[Summary view] [Print] [Text view]

   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      $count = $DB->count_records_sql("SELECT COUNT(DISTINCT(ge.id)) $sqlfrom $sqlwhere", $params);
 271  
 272      $limitfrom = $offset;
 273      $limitnum = 0;
 274  
 275      if ( $offset >= 0 ) {
 276          $limitnum = $entriesbypage;
 277      }
 278  
 279      $query = "$sqlwrapheader $sqlselect $sqlfrom $sqlwhere $sqlwrapfooter $sqlorderby";
 280      $allentries = $DB->get_records_sql($query, $params, $limitfrom, $limitnum);