Session test to detect user switching, error counter is displayed in healthcenter...
[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 = '';
14
4f3e59c3 15/// Pivot is the field that set the break by groups (category, initial, author name, etc)
16
17/// fullpivot indicate if the whole pivot should be compared agasint the db or just the first letter
18/// printpivot indicate if the pivot should be printed or not
c9128b63 19 switch ($CFG->dbtype) {
20 case 'postgres7':
f950af3c 21 $as = 'as';
c9128b63 22 break;
23 case 'mysql':
f950af3c 24 $as = '';
c9128b63 25 break;
26 }
27
db87439a 28 switch ( $sortkey ) {
29 case "CREATION":
30 $sqlsortkey = "timecreated";
31 break;
32
33 case "UPDATE":
34 $sqlsortkey = "timemodified";
35 break;
36 }
ae078733 37 $sqlsortorder = $sortorder;
db87439a 38
4f3e59c3 39 $fullpivot = 1;
4f3e59c3 40
41 $userid = '';
f950af3c 42 if ( isset($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
f79bfd2d 49 $sqlselect = "SELECT gec.id, ge.*, gec.entryid, gc.name $as pivot";
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;
189bec88 62 $sqlselect = "SELECT ge.*, concept $as pivot";
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;
6e661303 74 $sqlselect = "SELECT ge.*, ce.entryid, c.name $as pivot";
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
16527801 87 if (!isset($sqlsortkey)) {
88 $sqlsortkey = NULL;
89 }
90
4f3e59c3 91 $where = '';
92 switch ($CFG->dbtype) {
93 case 'postgres7':
94 $usernametoshow = "u.firstname || ' ' || u.lastname";
db87439a 95 if ( $sqlsortkey == 'FIRSTNAME' ) {
4f3e59c3 96 $usernamefield = "u.firstname || ' ' || u.lastname";
97 } else {
98 $usernamefield = "u.lastname || ' ' || u.firstname";
99 }
67db6177 100 $where = "AND substr(upper($usernamefield),1," . strlen($hook) . ") = '" . strtoupper($hook) . "'";
4f3e59c3 101 break;
102 case 'mysql':
db87439a 103 if ( $sqlsortkey == 'FIRSTNAME' ) {
4f3e59c3 104 $usernamefield = "CONCAT(CONCAT(u.firstname,' '), u.lastname)";
105 } else {
106 $usernamefield = "CONCAT(CONCAT(u.lastname,' '), u.firstname)";
107 }
108 $where = "AND left(ucase($usernamefield)," . strlen($hook) . ") = '$hook'";
109 break;
110 }
111 if ( $hook == 'ALL' ) {
112 $where = '';
113 }
114
56e9c381 115 $sqlselect = "SELECT ge.id, $usernamefield $as pivot, u.id as uid, ge.*";
4f3e59c3 116 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge, {$CFG->prefix}user u";
117 $sqlwhere = "WHERE ge.userid = u.id AND
118 (ge.approved != 0 $userid)
119 $where AND
4db75a5c 120 (ge.glossaryid = '$glossary->id' OR ge.sourceglossaryid = '$glossary->id')";
db87439a 121 $sqlorderby = "ORDER BY $usernamefield $sqlsortorder, ge.concept";
4f3e59c3 122 break;
123 case GLOSSARY_APPROVAL_VIEW:
124 $fullpivot = 0;
125 $printpivot = 0;
126
43d2c514 127 if (!isset($sqlsortkey)) {
128 $sqlsortkey = NULL;
129 }
130
4f3e59c3 131 $where = '';
132 if ($hook != 'ALL' and $hook != 'SPECIAL') {
133 switch ($CFG->dbtype) {
134 case 'postgres7':
67db6177 135 $where = 'AND substr(upper(concept),1,' . strlen($hook) . ') = \'' . strtoupper($hook) . '\'';
4f3e59c3 136 break;
137 case 'mysql':
138 $where = 'AND left(ucase(concept),' . strlen($hook) . ") = '$hook'";
139 break;
140 }
141 }
142
189bec88 143 $sqlselect = "SELECT ge.*, ge.concept $as pivot";
4f3e59c3 144 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge";
4db75a5c 145 $sqlwhere = "WHERE (ge.glossaryid = '$glossary->id' OR ge.sourceglossaryid = '$glossary->id') AND
4f3e59c3 146 ge.approved = 0 $where";
147
db87439a 148 if ( $sqlsortkey ) {
149 $sqlorderby = "ORDER BY $sqlsortkey $sqlsortorder";
4f3e59c3 150 } else {
151 $sqlorderby = "ORDER BY ge.concept";
152 }
153 break;
154 case GLOSSARY_DATE_VIEW:
155 case GLOSSARY_STANDARD_VIEW:
156 default:
189bec88 157 $sqlselect = "SELECT ge.*, ge.concept $as pivot";
4f3e59c3 158 $sqlfrom = "FROM {$CFG->prefix}glossary_entries ge";
159
160 $where = '';
161 $fullpivot = 0;
162 if ($CFG->dbtype == "postgres7") {
163 $LIKE = "ILIKE"; // case-insensitive
164 } else {
165 $LIKE = "LIKE";
166 }
167
168 switch ( $mode ) {
169 case 'search':
69baafde 170 //First, look in aliases (bug 2242)
171 $idaliases = '';
172 $listaliases = array();
173 $recaliases = get_records_sql ("SELECT al.id, al.entryid
174 FROM {$CFG->prefix}glossary_alias al,
175 {$CFG->prefix}glossary_entries ge
176 WHERE (ge.glossaryid = '$glossary->id' OR
177 ge.sourceglossaryid = '$glossary->id') AND
178 (ge.approved != 0 $userid) AND
179 ge.id = al.entryid AND
180 al.alias $LIKE '%$hook%'");
181 if ($recaliases) {
182 foreach ($recaliases as $recalias) {
183 $listaliases[] = $recalias->entryid;
184 }
185 $idaliases = implode (',',$listaliases);
186 }
4f3e59c3 187 $printpivot = 0;
188 $where = "AND ( ge.concept $LIKE '%$hook%'";
69baafde 189 //Include aliases in resultset (if any)
190 if (!empty($idaliases)) {
191 $where .= " OR ge.id IN ($idaliases)";
192 }
4f3e59c3 193 if ( $fullsearch ) {
69baafde 194 $where .= " OR ge.definition $LIKE '%$hook%')";
4f3e59c3 195 } else {
196 $where .= ")";
197 }
198 break;
199
200 case 'term':
201 $printpivot = 0;
5cca2496 202 $sqlfrom .= " left join {$CFG->prefix}glossary_alias ga on ge.id = ga.entryid ";
203 $where = "AND (ge.concept = '$hook' OR ga.alias = '$hook' )
204 ";
4f3e59c3 205 break;
206
207 case 'entry':
208 $printpivot = 0;
1c144d0e 209 $where = "AND ge.id = '$hook'";
4f3e59c3 210 break;
211
212 case 'letter':
213 if ($hook != 'ALL' and $hook != 'SPECIAL') {
214 switch ($CFG->dbtype) {
215 case 'postgres7':
67db6177 216 $where = 'AND substr(upper(concept),1,' . strlen($hook) . ') = \'' . strtoupper($hook) . '\'';
4f3e59c3 217 break;
218 case 'mysql':
b6862360 219 $where = 'AND left(ucase(concept),' . strlen($hook) . ") = '" . strtoupper($hook) . "'";
4f3e59c3 220 break;
221 }
222 }
539f698d 223 if ($hook == 'SPECIAL') {
224 //Create appropiate IN contents
225 $alphabet = explode(",", get_string("alphabet"));
226 $sqlalphabet = '';
227 for ($i = 0; $i < count($alphabet); $i++) {
228 if ($i != 0) {
229 $sqlalphabet .= ',';
230 }
231 $sqlalphabet .= '\''.$alphabet[$i].'\'';
232 }
233 switch ($CFG->dbtype) {
234 case 'postgres7':
67db6177 235 $where = 'AND substr(upper(concept),1,1) NOT IN (' . strtoupper($sqlalphabet) . ')';
539f698d 236 break;
237 case 'mysql':
238 $where = 'AND left(ucase(concept),1) NOT IN (' . strtoupper($sqlalphabet) . ')';
239 break;
240 }
241 }
4f3e59c3 242 break;
243 }
244
4db75a5c 245 $sqlwhere = "WHERE (ge.glossaryid = '$glossary->id' or ge.sourceglossaryid = '$glossary->id') AND
4f3e59c3 246 (ge.approved != 0 $userid)
247 $where";
248 switch ( $tab ) {
249 case GLOSSARY_DATE_VIEW:
db87439a 250 $sqlorderby = "ORDER BY $sqlsortkey $sqlsortorder";
4f3e59c3 251 break;
252
253 case GLOSSARY_STANDARD_VIEW:
254 $sqlorderby = "ORDER BY ge.concept";
255 default:
256 break;
257 }
258 break;
5cca2496 259 }
260 $count = count_records_sql("select count(*) $sqlfrom $sqlwhere");
465e3ae3 261
4f3e59c3 262 $sqllimit = '';
a5e48d96 263
4f3e59c3 264 if ( $offset >= 0 ) {
f950af3c 265 switch ($CFG->dbtype) {
a5e48d96 266 case 'postgres7':
f950af3c 267 $sqllimit = " LIMIT $entriesbypage OFFSET $offset";
a5e48d96 268 break;
269 case 'mysql':
f950af3c 270 $sqllimit = " LIMIT $offset, $entriesbypage";
a5e48d96 271 break;
272 }
4f3e59c3 273 }
189bec88 274
5cca2496 275 $allentries = get_records_sql("$sqlselect $sqlfrom $sqlwhere $sqlorderby $sqllimit");
539f698d 276
465e3ae3 277?>