mod/glossary/sql.php

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