"MDL-14120, fix print_error"
[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
6 * @version $Id$
7 * @copyright 2003
8 **/
9
10/// Creating the SQL statements
11
1c144d0e 12/// Initialise some variables
13 $sqlorderby = '';
54eca8f2 14 $sqlsortkey = NULL;
afda6b56 15 $textlib = textlib_get_instance();
1c144d0e 16
572889f1 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
4f3e59c3 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
c9128b63 38
4f3e59c3 39 $fullpivot = 1;
4f3e59c3 40
41 $userid = '';
86a1ba04 42 if ( !empty($USER->id) ) {
4f3e59c3 43 $userid = "OR ge.userid = $USER->id";
44 }
45 switch ($tab) {
46 case GLOSSARY_CATEGORY_VIEW:
47 if ($hook == GLOSSARY_SHOW_ALL_CATEGORIES ) {
48
e24d96ba 49 $sqlselect = "SELECT gec.id AS cid, ge.*, gec.entryid, gc.name AS glossarypivot";
4f3e59c3 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
a359c29b 57 $sqlorderby = ' ORDER BY gc.name, ge.concept';
4f3e59c3 58
59 } elseif ($hook == GLOSSARY_SHOW_NOT_CATEGORISED ) {
60
61 $printpivot = 0;
a91960b3 62 $sqlselect = "SELECT ge.*, concept AS glossarypivot";
539f698d 63 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge LEFT JOIN {$CFG->prefix}glossary_entries_categories gec
64 ON ge.id = gec.entryid";
4f3e59c3 65 $sqlwhere = "WHERE (glossaryid = '$glossary->id' OR sourceglossaryid = '$glossary->id') AND
539f698d 66 (ge.approved != 0 $userid) AND gec.entryid IS NULL";
4f3e59c3 67
68
69 $sqlorderby = ' ORDER BY concept';
70
71 } else {
72
73 $printpivot = 0;
a91960b3 74 $sqlselect = "SELECT ge.*, ce.entryid, c.name AS glossarypivot";
4f3e59c3 75 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge, {$CFG->prefix}glossary_entries_categories ce, {$CFG->prefix}glossary_categories c";
4db75a5c 76 $sqlwhere = "WHERE ge.id = ce.entryid AND ce.categoryid = '$hook' AND
4f3e59c3 77 ce.categoryid = c.id AND ge.approved != 0 AND
4db75a5c 78 (ge.glossaryid = '$glossary->id' OR ge.sourceglossaryid = '$glossary->id') AND
4f3e59c3 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 = '';
2cb3ab13 88
572889f1 89 if ( $sqlsortkey == 'firstname' ) {
2cb3ab13 90 $usernamefield = sql_fullname('u.firstname' , 'u.lastname');
91 } else {
92 $usernamefield = sql_fullname('u.lastname' , 'u.firstname');
4f3e59c3 93 }
810944af 94 $where = "AND " . sql_substr() . "(upper($usernamefield),1," . $textlib->strlen($hook) . ") = '" . $textlib->strtoupper($hook) . "'";
2cb3ab13 95
4f3e59c3 96 if ( $hook == 'ALL' ) {
97 $where = '';
98 }
99
8451166c 100 $sqlselect = "SELECT ge.*, $usernamefield AS glossarypivot, 1 AS userispivot ";
4f3e59c3 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
4db75a5c 105 (ge.glossaryid = '$glossary->id' OR ge.sourceglossaryid = '$glossary->id')";
db87439a 106 $sqlorderby = "ORDER BY $usernamefield $sqlsortorder, ge.concept";
4f3e59c3 107 break;
108 case GLOSSARY_APPROVAL_VIEW:
109 $fullpivot = 0;
110 $printpivot = 0;
111
112 $where = '';
113 if ($hook != 'ALL' and $hook != 'SPECIAL') {
810944af 114 $where = 'AND ' . sql_substr() . '(upper(concept),1,' . $textlib->strlen($hook) . ') = \'' . $textlib->strtoupper($hook) . '\'';
4f3e59c3 115 }
116
a91960b3 117 $sqlselect = "SELECT ge.*, ge.concept AS glossarypivot";
4f3e59c3 118 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge";
4db75a5c 119 $sqlwhere = "WHERE (ge.glossaryid = '$glossary->id' OR ge.sourceglossaryid = '$glossary->id') AND
4f3e59c3 120 ge.approved = 0 $where";
121
db87439a 122 if ( $sqlsortkey ) {
123 $sqlorderby = "ORDER BY $sqlsortkey $sqlsortorder";
4f3e59c3 124 } else {
125 $sqlorderby = "ORDER BY ge.concept";
126 }
127 break;
128 case GLOSSARY_DATE_VIEW:
c356bb40 129 $printpivot = 0;
4f3e59c3 130 case GLOSSARY_STANDARD_VIEW:
131 default:
a91960b3 132 $sqlselect = "SELECT ge.*, ge.concept AS glossarypivot";
4f3e59c3 133 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge";
134
135 $where = '';
136 $fullpivot = 0;
a8f4522d 137 $LIKE = sql_ilike();
138 $NOTLIKE = 'NOT ' . $LIKE;
4f3e59c3 139
140 switch ( $mode ) {
141 case 'search':
74a27855 142
143 /// Some differences in syntax for PostgreSQL
a4bad45c 144 if ($CFG->dbfamily == "postgres") {
74a27855 145 $REGEXP = "~*";
146 $NOTREGEXP = "!~*";
147 } else {
74a27855 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) {
0fd4f407 159
74a27855 160 if ($conceptsearch) {
161 $conceptsearch .= " AND ";
162 }
163 if ($aliassearch) {
164 $aliassearch .= " AND ";
165 }
166 if ($definitionsearch) {
167 $definitionsearch .= " AND ";
168 }
6f4bdb9c 169
170 /// Under Oracle and MSSQL, trim the + and - operators and perform
171 /// simpler LIKE search
a4bad45c 172 if ($CFG->dbfamily == 'oracle' || $CFG->dbfamily == 'mssql') {
6f4bdb9c 173 $searchterm = trim($searchterm, '+-');
174 }
175
74a27855 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
69baafde 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
74a27855 199 WHERE (ge.glossaryid = '$glossary->id' OR
69baafde 200 ge.sourceglossaryid = '$glossary->id') AND
201 (ge.approved != 0 $userid) AND
202 ge.id = al.entryid AND
74a27855 203 $aliassearch");
204 //Process aliases id
69baafde 205 if ($recaliases) {
206 foreach ($recaliases as $recalias) {
207 $listaliases[] = $recalias->entryid;
208 }
209 $idaliases = implode (',',$listaliases);
210 }
74a27855 211
212 //Add seach conditions in concepts and, if needed, in definitions
4f3e59c3 213 $printpivot = 0;
74a27855 214 $where = "AND (( $conceptsearch) ";
215
216 //Include aliases id if found
69baafde 217 if (!empty($idaliases)) {
74a27855 218 $where .= " OR ge.id IN ($idaliases) ";
69baafde 219 }
74a27855 220
221 //Include search in definitions if requested
4f3e59c3 222 if ( $fullsearch ) {
74a27855 223 $where .= " OR ($definitionsearch) )";
4f3e59c3 224 } else {
225 $where .= ")";
226 }
74a27855 227
4f3e59c3 228 break;
229
230 case 'term':
231 $printpivot = 0;
5cca2496 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 ";
4f3e59c3 235 break;
236
237 case 'entry':
238 $printpivot = 0;
1c144d0e 239 $where = "AND ge.id = '$hook'";
4f3e59c3 240 break;
241
242 case 'letter':
243 if ($hook != 'ALL' and $hook != 'SPECIAL') {
810944af 244 $where = 'AND ' . sql_substr() . '(upper(concept),1,' . $textlib->strlen($hook) . ') = \'' . $textlib->strtoupper($hook) . '\'';
4f3e59c3 245 }
539f698d 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 }
810944af 256 $where = 'AND ' . sql_substr() . '(upper(concept),1,1) NOT IN (' . $textlib->strtoupper($sqlalphabet) . ')';
539f698d 257 }
4f3e59c3 258 break;
259 }
260
4db75a5c 261 $sqlwhere = "WHERE (ge.glossaryid = '$glossary->id' or ge.sourceglossaryid = '$glossary->id') AND
4f3e59c3 262 (ge.approved != 0 $userid)
263 $where";
264 switch ( $tab ) {
265 case GLOSSARY_DATE_VIEW:
db87439a 266 $sqlorderby = "ORDER BY $sqlsortkey $sqlsortorder";
4f3e59c3 267 break;
268
269 case GLOSSARY_STANDARD_VIEW:
270 $sqlorderby = "ORDER BY ge.concept";
271 default:
272 break;
273 }
274 break;
5cca2496 275 }
276 $count = count_records_sql("select count(*) $sqlfrom $sqlwhere");
465e3ae3 277
0217757b 278 $limitfrom = $offset;
279 $limitnum = 0;
a5e48d96 280
4f3e59c3 281 if ( $offset >= 0 ) {
0217757b 282 $limitnum = $entriesbypage;
4f3e59c3 283 }
8451166c 284
0217757b 285 $allentries = get_records_sql("$sqlselect $sqlfrom $sqlwhere $sqlorderby", $limitfrom, $limitnum);
286?>