mod/glossary/sql.php

   1 <?php
   2
   3 /**
   4  * SQL.PHP
   5  *    This file is include from view.php and print.php
   6  * @copyright 2003
   7  **/
   8
   9 /// Creating the SQL statements
  10
  11 /// Initialise some variables
  12     $sqlorderby = '';
  13     $sqlsortkey = NULL;
  14
  15     // For cases needing inner view
  16     $sqlwrapheader = '';
  17     $sqlwrapfooter = '';
  18
  19 /// Calculate the SQL sortkey to be used by the SQL statements later
  20     switch ( $sortkey ) {
  21         case "CREATION":
  22             $sqlsortkey = "timecreated";
  23             break;
  24         case "UPDATE":
  25             $sqlsortkey = "timemodified";
  26             break;
  27         case "FIRSTNAME":
  28             $sqlsortkey = "firstname";
  29             break;
  30         case "LASTNAME":
  31             $sqlsortkey = "lastname";
  32             break;
  33     }
  34     $sqlsortorder = $sortorder;
  35
  36 /// Pivot is the field that set the break by groups (category, initial, author name, etc)
  37
  38 /// fullpivot indicate if the whole pivot should be compared agasint the db or just the first letter
  39 /// printpivot indicate if the pivot should be printed or not
  40
  41     $fullpivot = 1;
  42     $params = array('gid1'=>$glossary->id, 'gid2'=>$glossary->id, 'myid'=>$USER->id, 'hook'=>$hook);
  43
  44     $userid = '';
  45     if ( isloggedin() ) {
  46         $userid = "OR ge.userid = :myid";
  47     }
  48     switch ($tab) {
  49     case GLOSSARY_CATEGORY_VIEW:
  50         if ($hook == GLOSSARY_SHOW_ALL_CATEGORIES  ) {
  51
  52             $sqlselect = "SELECT gec.id AS cid, ge.*, gec.entryid, gc.name AS glossarypivot";
  53             $sqlfrom   = "FROM {glossary_entries} ge,
  54                                {glossary_entries_categories} gec,
  55                                {glossary_categories} gc";
  56             $sqlwhere  = "WHERE (ge.glossaryid = :gid1 OR ge.sourceglossaryid = :gid2) AND
  57                           ge.id = gec.entryid AND gc.id = gec.categoryid AND
  58                           (ge.approved <> 0 $userid)";
  59
  60             $sqlorderby = ' ORDER BY gc.name, ge.concept';
  61
  62         } elseif ($hook == GLOSSARY_SHOW_NOT_CATEGORISED ) {
  63
  64             $printpivot = 0;
  65             $sqlselect = "SELECT ge.*, concept AS glossarypivot";
  66             $sqlfrom   = "FROM {glossary_entries} ge LEFT JOIN {glossary_entries_categories} gec
  67                                ON ge.id = gec.entryid";
  68             $sqlwhere  = "WHERE (glossaryid = :gid1 OR sourceglossaryid = :gid2) AND
  69                           (ge.approved <> 0 $userid) AND gec.entryid IS NULL";
  70
  71
  72             $sqlorderby = ' ORDER BY concept';
  73
  74         } else {
  75
  76             $printpivot = 0;
  77             $sqlselect  = "SELECT ge.*, ce.entryid, c.name AS glossarypivot";
  78             $sqlfrom    = "FROM {glossary_entries} ge, {glossary_entries_categories} ce, {glossary_categories} c";
  79             $sqlwhere   = "WHERE ge.id = ce.entryid AND ce.categoryid = :hook AND
  80                                  ce.categoryid = c.id AND ge.approved != 0 AND
  81                                  (ge.glossaryid = :gid1 OR ge.sourceglossaryid = :gid2) AND
  82                           (ge.approved <> 0 $userid)";
  83
  84             $sqlorderby = ' ORDER BY c.name, ge.concept';
  85
  86         }
  87     break;
  88     case GLOSSARY_AUTHOR_VIEW:
  89
  90         $where = '';
  91         $params['hookup'] = core_text::strtoupper($hook);
  92
  93         if ( $sqlsortkey == 'firstname' ) {
  94             $usernamefield = $DB->sql_fullname('u.firstname' , 'u.lastname');
  95         } else {
  96             $usernamefield = $DB->sql_fullname('u.lastname' , 'u.firstname');
  97         }
  98         if ($hook != 'ALL' && ($hookstrlen = core_text::strlen($hook))) {
  99             $where = "AND " . $DB->sql_substr("upper($usernamefield)", 1, core_text::strlen($hook)) . " = :hookup";
 100         }
 101
 102         $sqlselect  = "SELECT ge.*, $usernamefield AS glossarypivot, 1 AS userispivot ";
 103         $sqlfrom    = "FROM {glossary_entries} ge, {user} u";
 104         $sqlwhere   = "WHERE ge.userid = u.id  AND
 105                              (ge.approved <> 0 $userid)
 106                              $where AND
 107                              (ge.glossaryid = :gid1 OR ge.sourceglossaryid = :gid2)";
 108         $sqlorderby = "ORDER BY $usernamefield $sqlsortorder, ge.concept";
 109     break;
 110     case GLOSSARY_APPROVAL_VIEW:
 111         $fullpivot = 0;
 112         $printpivot = 0;
 113
 114         $where = '';
 115         $params['hookup'] = core_text::strtoupper($hook);
 116
 117         if ($hook != 'ALL' and $hook != 'SPECIAL' && ($hookstrlen = core_text::strlen($hook))) {
 118             $where = "AND " . $DB->sql_substr("upper(concept)", 1, $hookstrlen) . " = :hookup";
 119         }
 120
 121         $sqlselect  = "SELECT ge.*, ge.concept AS glossarypivot";
 122         $sqlfrom    = "FROM {glossary_entries} ge";
 123         $sqlwhere   = "WHERE (ge.glossaryid = :gid1 OR ge.sourceglossaryid = :gid2) AND
 124                              ge.approved = 0 $where";
 125
 126         if ( $sqlsortkey ) {
 127             $sqlorderby = "ORDER BY $sqlsortkey $sqlsortorder";
 128         } else {
 129             $sqlorderby = "ORDER BY ge.concept";
 130         }
 131     break;
 132     case GLOSSARY_DATE_VIEW:
 133         $printpivot = 0;
 134     case GLOSSARY_STANDARD_VIEW:
 135     default:
 136         $sqlselect  = "SELECT ge.*, ge.concept AS glossarypivot";
 137         $sqlfrom    = "FROM {glossary_entries} ge";
 138
 139         $where = '';
 140         $fullpivot = 0;
 141
 142         switch ( $mode ) {
 143         case 'search':
 144
 145             if ($DB->sql_regex_supported()) {
 146                 $REGEXP    = $DB->sql_regex(true);
 147                 $NOTREGEXP = $DB->sql_regex(false);
 148             }
 149
 150             $searchcond = array();
 151             $alcond     = array();
 152             //$params     = array();
 153             $i = 0;
 154
 155             $searchterms = explode(" ",$hook);
 156
 157             foreach ($searchterms as $searchterm) {
 158                 $i++;
 159
 160                 $NOT = false; /// Initially we aren't going to perform NOT LIKE searches, only MSSQL and Oracle
 161                            /// will use it to simulate the "-" operator with LIKE clause
 162
 163                 if (empty($fullsearch)) {
 164                     // With fullsearch disabled, look only within concepts and aliases.
 165                     $concat = $DB->sql_concat('ge.concept', "' '", "COALESCE(al.alias, :emptychar".$i.")");
 166                 } else {
 167                     // With fullsearch enabled, look also within definitions.
 168                     $concat = $DB->sql_concat('ge.concept', "' '", 'ge.definition', "' '", "COALESCE(al.alias, :emptychar".$i.")");
 169                 }
 170                 $params['emptychar'.$i] = '';
 171
 172                 /// Under Oracle and MSSQL, trim the + and - operators and perform
 173                 /// simpler LIKE (or NOT LIKE) queries
 174                 if (!$DB->sql_regex_supported()) {
 175                     if (substr($searchterm, 0, 1) == '-') {
 176                         $NOT = true;
 177                     }
 178                     $searchterm = trim($searchterm, '+-');
 179                 }
 180
 181                 if (substr($searchterm,0,1) == '+') {
 182                     $searchterm = trim($searchterm, '+-');
 183                     if (core_text::strlen($searchterm) < 2) {
 184                         continue;
 185                     }
 186                     $searchterm = preg_quote($searchterm, '|');
 187                     $searchcond[] = "$concat $REGEXP :ss$i";
 188                     $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";
 189
 190                 } else if (substr($searchterm,0,1) == "-") {
 191                     $searchterm = trim($searchterm, '+-');
 192                     if (core_text::strlen($searchterm) < 2) {
 193                         continue;
 194                     }
 195                     $searchterm = preg_quote($searchterm, '|');
 196                     $searchcond[] = "$concat $NOTREGEXP :ss$i";
 197                     $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";
 198
 199                 } else {
 200                     if (core_text::strlen($searchterm) < 2) {
 201                         continue;
 202                     }
 203                     $searchcond[] = $DB->sql_like($concat, ":ss$i", false, true, $NOT);
 204                     $params['ss'.$i] = "%$searchterm%";
 205                 }
 206             }
 207
 208             if (empty($searchcond)) {
 209                 $where = "AND 1=2 "; // no search result
 210
 211             } else {
 212                 $searchcond = implode(" AND ", $searchcond);
 213
 214                 // Need one inner view here to avoid distinct + text
 215                 $sqlwrapheader = 'SELECT ge.*, ge.concept AS glossarypivot
 216                                     FROM {glossary_entries} ge
 217                                     JOIN ( ';
 218                 $sqlwrapfooter = ' ) gei ON (ge.id = gei.id)';
 219
 220                 $sqlselect  = "SELECT DISTINCT ge.id";
 221                 $sqlfrom    = "FROM {glossary_entries} ge
 222                                LEFT JOIN {glossary_alias} al ON al.entryid = ge.id";
 223                 $where      = "AND ($searchcond)";
 224             }
 225
 226         break;
 227
 228         case 'term':
 229             $params['hook2'] = $hook;
 230             $printpivot = 0;
 231             $sqlfrom .= " LEFT JOIN {glossary_alias} ga on ge.id = ga.entryid";
 232             $where = "AND (ge.concept = :hook OR ga.alias = :hook2) ";
 233         break;
 234
 235         case 'entry':
 236             $printpivot = 0;
 237             $where = "AND ge.id = :hook";
 238         break;
 239
 240         case 'letter':
 241             if ($hook != 'ALL' and $hook != 'SPECIAL' and ($hookstrlen = core_text::strlen($hook))) {
 242                 $params['hookup'] = core_text::strtoupper($hook);
 243                 $where = "AND " . $DB->sql_substr("upper(concept)", 1, $hookstrlen) . " = :hookup";
 244             }
 245             if ($hook == 'SPECIAL') {
 246                 //Create appropiate IN contents
 247                 $alphabet = explode(",", get_string('alphabet', 'langconfig'));
 248                 list($nia, $aparams) = $DB->get_in_or_equal($alphabet, SQL_PARAMS_NAMED, $start='a', false);
 249                 $params = array_merge($params, $aparams);
 250                 $where = "AND " . $DB->sql_substr("upper(concept)", 1, 1) . " $nia";
 251             }
 252         break;
 253         }
 254
 255         $sqlwhere   = "WHERE (ge.glossaryid = :gid1 or ge.sourceglossaryid = :gid2) AND
 256                              (ge.approved <> 0 $userid)
 257                               $where";
 258         switch ( $tab ) {
 259         case GLOSSARY_DATE_VIEW:
 260             $sqlorderby = "ORDER BY $sqlsortkey $sqlsortorder";
 261         break;
 262
 263         case GLOSSARY_STANDARD_VIEW:
 264             $sqlorderby = "ORDER BY ge.concept";
 265         default:
 266         break;
 267         }
 268     break;
 269     }
 270
 271     $count = 0;
 272     if ($tab == GLOSSARY_CATEGORY_VIEW && $hook == GLOSSARY_SHOW_ALL_CATEGORIES) {
 273         $count = $DB->count_records_sql("SELECT COUNT(ge.id) $sqlfrom $sqlwhere", $params);
 274     } else {
 275         $count = $DB->count_records_sql("SELECT COUNT(DISTINCT(ge.id)) $sqlfrom $sqlwhere", $params);
 276     }
 277
 278     $limitfrom = $offset;
 279     $limitnum = 0;
 280
 281     if ( $offset >= 0 ) {
 282         $limitnum = $entriesbypage;
 283     }
 284
 285     $query = "$sqlwrapheader $sqlselect $sqlfrom $sqlwhere $sqlwrapfooter $sqlorderby";
 286     $allentries = $DB->get_records_sql($query, $params, $limitfrom, $limitnum);