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