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