7cf1c7bd |
1 | <?php |
2 | |
3 | /** |
4 | * Library of functions for database manipulation. |
5 | * |
6 | * Library of functions for database manipulation. |
7 | * Other main libraries: |
8 | * - weblib.php - functions that produce web output |
9 | * - moodlelib.php - general-purpose Moodle functions |
10 | * @author Martin Dougiamas |
11 | * @version $Id$ |
12 | * @license http://opensource.org/licenses/gpl-license.php GNU Public License |
13 | * @package moodlecore |
14 | */ |
15 | |
df28d6c5 |
16 | |
7d8c2ec3 |
17 | /// GLOBAL CONSTANTS ///////////////////////////////////////////////////////// |
7cf1c7bd |
18 | |
7d8c2ec3 |
19 | if ($SITE = get_site()) { |
7cf1c7bd |
20 | /** |
21 | * If $SITE global from get_site() function exists then set it to that id, otherwise set to 1. |
22 | */ |
7d8c2ec3 |
23 | define('SITEID', $SITE->id); |
24 | } else { |
7cf1c7bd |
25 | /** |
26 | * @ignore |
27 | */ |
7d8c2ec3 |
28 | define('SITEID', 1); |
29 | } |
222ac91b |
30 | |
df28d6c5 |
31 | /// FUNCTIONS FOR DATABASE HANDLING //////////////////////////////// |
222ac91b |
32 | |
18a97fd8 |
33 | /** |
7cf1c7bd |
34 | * Execute a given sql command string |
35 | * |
36 | * Completely general function - it just runs some SQL and reports success. |
37 | * |
38 | * @param string $command the sql string to be executed |
39 | * @param boolean $feedback should the output of this command be returned? Default is true. |
40 | * @return string |
41 | */ |
df28d6c5 |
42 | function execute_sql($command, $feedback=true) { |
43 | /// Completely general function - it just runs some SQL and reports success. |
44 | |
45 | global $db; |
8f0cd6ef |
46 | |
d4419d55 |
47 | $result = $db->Execute($command); |
df28d6c5 |
48 | |
49 | if ($result) { |
50 | if ($feedback) { |
d4419d55 |
51 | echo '<p><font color="green"><b>'. get_string('success') .'</b></font></p>'; |
df28d6c5 |
52 | } |
53 | return true; |
54 | } else { |
55 | if ($feedback) { |
d4419d55 |
56 | echo '<p><font color="red"><b>'. get_string('error') .'</b></font></p>'; |
df28d6c5 |
57 | } |
58 | return false; |
59 | } |
60 | } |
61 | |
7cf1c7bd |
62 | /** |
63 | * Run an arbitrary sequence of semicolon-delimited SQL commands |
64 | * |
65 | * Assumes that the input text (file or string) consists of |
66 | * a number of SQL statements ENDING WITH SEMICOLONS. The |
67 | * semicolons MUST be the last character in a line. |
68 | * Lines that are blank or that start with "#" are ignored. |
69 | * Only tested with mysql dump files (mysqldump -p -d moodle) |
70 | * |
71 | * @param string $sqlfile The path where a file with sql commands can be found on the server. |
72 | * @param string $sqlstring If no path is supplied then a string with semicolon delimited sql |
73 | * commands can be supplied in this argument. |
74 | */ |
d4419d55 |
75 | function modify_database($sqlfile='', $sqlstring='') { |
df28d6c5 |
76 | |
77 | global $CFG; |
78 | |
8f0cd6ef |
79 | $success = true; // Let's be optimistic |
2b051f1c |
80 | |
81 | if (!empty($sqlfile)) { |
82 | if (!is_readable($sqlfile)) { |
83 | $success = false; |
d4419d55 |
84 | echo '<p>Tried to modify database, but "'. $sqlfile .'" doesn\'t exist!</p>'; |
2b051f1c |
85 | return $success; |
86 | } else { |
87 | $lines = file($sqlfile); |
88 | } |
89 | } else { |
90 | $lines[] = $sqlstring; |
91 | } |
92 | |
d4419d55 |
93 | $command = ''; |
2b051f1c |
94 | |
95 | foreach ($lines as $line) { |
96 | $line = rtrim($line); |
97 | $length = strlen($line); |
98 | |
d4419d55 |
99 | if ($length and $line[0] <> '#') { |
100 | if (substr($line, $length-1, 1) == ';') { |
2b051f1c |
101 | $line = substr($line, 0, $length-1); // strip ; |
102 | $command .= $line; |
d4419d55 |
103 | $command = str_replace('prefix_', $CFG->prefix, $command); // Table prefixes |
2b051f1c |
104 | if (! execute_sql($command)) { |
105 | $success = false; |
df28d6c5 |
106 | } |
d4419d55 |
107 | $command = ''; |
2b051f1c |
108 | } else { |
109 | $command .= $line; |
df28d6c5 |
110 | } |
111 | } |
df28d6c5 |
112 | } |
113 | |
114 | return $success; |
2b051f1c |
115 | |
df28d6c5 |
116 | } |
117 | |
a3fb1c45 |
118 | /// FUNCTIONS TO MODIFY TABLES //////////////////////////////////////////// |
119 | |
18a97fd8 |
120 | /** |
121 | * Add a new field to a table, or modify an existing one (if oldfield is defined). |
8f0cd6ef |
122 | * |
18a97fd8 |
123 | * Add a new field to a table, or modify an existing one (if oldfield is defined). |
124 | * |
6bc502cc |
125 | * @param type description |
7cf1c7bd |
126 | * @todo Finish documenting this function |
18a97fd8 |
127 | */ |
128 | |
d4419d55 |
129 | function table_column($table, $oldfield, $field, $type='integer', $size='10', |
130 | $signed='unsigned', $default='0', $null='not null', $after='') { |
8a230a7d |
131 | global $CFG, $db; |
a3fb1c45 |
132 | |
133 | switch (strtolower($CFG->dbtype)) { |
134 | |
d4419d55 |
135 | case 'mysql': |
136 | case 'mysqlt': |
a3fb1c45 |
137 | |
138 | switch (strtolower($type)) { |
d4419d55 |
139 | case 'text': |
140 | $type = 'TEXT'; |
141 | $signed = ''; |
c2cb4545 |
142 | break; |
d4419d55 |
143 | case 'integer': |
144 | $type = 'INTEGER('. $size .')'; |
a3fb1c45 |
145 | break; |
d4419d55 |
146 | case 'varchar': |
147 | $type = 'VARCHAR('. $size .')'; |
148 | $signed = ''; |
a3fb1c45 |
149 | break; |
150 | } |
151 | |
152 | if (!empty($oldfield)) { |
d4419d55 |
153 | $operation = 'CHANGE '. $oldfield .' '. $field; |
a3fb1c45 |
154 | } else { |
d4419d55 |
155 | $operation = 'ADD '. $field; |
a3fb1c45 |
156 | } |
157 | |
d4419d55 |
158 | $default = 'DEFAULT \''. $default .'\''; |
a3fb1c45 |
159 | |
160 | if (!empty($after)) { |
d4419d55 |
161 | $after = 'AFTER `'. $after .'`'; |
a3fb1c45 |
162 | } |
163 | |
d4419d55 |
164 | return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' '. $operation .' '. $type .' '. $signed .' '. $default .' '. $null .' '. $after); |
a3fb1c45 |
165 | break; |
166 | |
d4419d55 |
167 | case 'postgres7': // From Petri Asikainen |
8a230a7d |
168 | //Check db-version |
169 | $dbinfo = $db->ServerInfo(); |
29622339 |
170 | $dbver = substr($dbinfo['version'],0,3); |
8f0cd6ef |
171 | |
5a4d292b |
172 | //to prevent conflicts with reserved words |
d4419d55 |
173 | $realfield = '"'. $field .'"'; |
174 | $field = '"'. $field .'_alter_column_tmp"'; |
175 | $oldfield = '"'. $oldfield .'"'; |
5a4d292b |
176 | |
177 | switch (strtolower($type)) { |
d4419d55 |
178 | case 'integer': |
09d4c9a9 |
179 | if ($size <= 4) { |
d4419d55 |
180 | $type = 'INT2'; |
8f0cd6ef |
181 | } |
09d4c9a9 |
182 | if ($size <= 10) { |
d4419d55 |
183 | $type = 'INT'; |
5a4d292b |
184 | } |
09d4c9a9 |
185 | if ($size > 10) { |
d4419d55 |
186 | $type = 'INT8'; |
5a4d292b |
187 | } |
188 | break; |
d4419d55 |
189 | case 'varchar': |
190 | $type = 'VARCHAR('. $size .')'; |
5a4d292b |
191 | break; |
192 | } |
193 | |
d4419d55 |
194 | $default = '\''. $default .'\''; |
5a4d292b |
195 | |
196 | //After is not implemented in postgesql |
197 | //if (!empty($after)) { |
198 | // $after = "AFTER '$after'"; |
199 | //} |
200 | |
d05024a7 |
201 | //Use transactions |
d4419d55 |
202 | execute_sql('BEGIN'); |
d05024a7 |
203 | |
204 | //Allways use temporaly column |
d4419d55 |
205 | execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type); |
d05024a7 |
206 | //Add default values |
d4419d55 |
207 | execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default); |
8f0cd6ef |
208 | |
5a4d292b |
209 | |
d4419d55 |
210 | if ($dbver >= '7.3') { |
8a230a7d |
211 | // modifying 'not null' is posible before 7.3 |
212 | //update default values to table |
d4419d55 |
213 | if ($null == 'NOT NULL') { |
214 | execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .'='. $default .' WHERE '. $field .' IS NULL'); |
215 | execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null); |
8a230a7d |
216 | } else { |
d4419d55 |
217 | execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' DROP NOT NULL'); |
8a230a7d |
218 | } |
5a4d292b |
219 | } |
8a230a7d |
220 | |
d4419d55 |
221 | execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET DEFAULT '. $default); |
8f0cd6ef |
222 | |
d4419d55 |
223 | if ( $oldfield != '""' ) { |
224 | execute_sql('UPDATE '. $CFG->prefix . $table .' SET '. $field .' = '. $oldfield); |
225 | execute_sql('ALTER TABLE '. $CFG->prefix . $table .' DROP COLUMN '. $oldfield); |
d05024a7 |
226 | } |
5a4d292b |
227 | |
7cf1c7bd |
228 | execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $field .' TO '. $realfield); |
8f0cd6ef |
229 | |
d4419d55 |
230 | return execute_sql('COMMIT'); |
5a4d292b |
231 | break; |
a3fb1c45 |
232 | |
233 | default: |
234 | switch (strtolower($type)) { |
d4419d55 |
235 | case 'integer': |
236 | $type = 'INTEGER'; |
a3fb1c45 |
237 | break; |
d4419d55 |
238 | case 'varchar': |
239 | $type = 'VARCHAR'; |
92230499 |
240 | break; |
a3fb1c45 |
241 | } |
242 | |
d4419d55 |
243 | $default = 'DEFAULT \''. $default .'\''; |
a3fb1c45 |
244 | |
245 | if (!empty($after)) { |
d4419d55 |
246 | $after = 'AFTER '. $after; |
a3fb1c45 |
247 | } |
248 | |
249 | if (!empty($oldfield)) { |
d4419d55 |
250 | execute_sql('ALTER TABLE '. $CFG->prefix . $table .' RENAME COLUMN '. $oldfield .' '. $field); |
a3fb1c45 |
251 | } else { |
d4419d55 |
252 | execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ADD COLUMN '. $field .' '. $type); |
a3fb1c45 |
253 | } |
254 | |
d4419d55 |
255 | execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $null); |
256 | return execute_sql('ALTER TABLE '. $CFG->prefix . $table .' ALTER COLUMN '. $field .' SET '. $default); |
a3fb1c45 |
257 | break; |
258 | |
259 | } |
260 | } |
261 | |
262 | |
263 | |
264 | /// GENERIC FUNCTIONS TO CHECK AND COUNT RECORDS //////////////////////////////////////// |
df28d6c5 |
265 | |
18a97fd8 |
266 | /** |
267 | * Returns true or false depending on whether the specified record exists |
8f0cd6ef |
268 | * |
18a97fd8 |
269 | * Returns true or false depending on whether the specified record exists |
270 | * |
6bc502cc |
271 | * @param type description |
7cf1c7bd |
272 | * @todo Finish documenting this function |
18a97fd8 |
273 | */ |
d4419d55 |
274 | function record_exists($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') { |
df28d6c5 |
275 | |
276 | global $CFG; |
277 | |
5c63e0c4 |
278 | if ($field1) { |
d4419d55 |
279 | $select = 'WHERE '. $field1 .' = \''. $value1 .'\''; |
9fa49e22 |
280 | if ($field2) { |
d4419d55 |
281 | $select .= ' AND '. $field2 .' = \''. $value2 .'\''; |
9fa49e22 |
282 | if ($field3) { |
d4419d55 |
283 | $select .= ' AND '. $field3 .' = \''. $value3 .'\''; |
df28d6c5 |
284 | } |
285 | } |
5c63e0c4 |
286 | } else { |
d4419d55 |
287 | $select = ''; |
df28d6c5 |
288 | } |
289 | |
d4419d55 |
290 | return record_exists_sql('SELECT * FROM '. $CFG->prefix . $table .' '. $select .' LIMIT 1'); |
df28d6c5 |
291 | } |
292 | |
293 | |
18a97fd8 |
294 | /** |
295 | * Returns true or false depending on whether the specified record exists |
8f0cd6ef |
296 | * |
18a97fd8 |
297 | * The sql statement is provided as a string. |
298 | * |
6bc502cc |
299 | * @param type description |
7cf1c7bd |
300 | * @todo Finish documenting this function |
18a97fd8 |
301 | */ |
df28d6c5 |
302 | function record_exists_sql($sql) { |
df28d6c5 |
303 | |
06b1db82 |
304 | global $CFG, $db; |
df28d6c5 |
305 | |
9c7fee6c |
306 | if (!$rs = $db->Execute($sql)) { |
2eef791f |
307 | if (isset($CFG->debug) and $CFG->debug > 7) { |
d4419d55 |
308 | notify($db->ErrorMsg().'<br /><br />'.$sql); |
9c7fee6c |
309 | } |
310 | return false; |
311 | } |
df28d6c5 |
312 | |
313 | if ( $rs->RecordCount() ) { |
314 | return true; |
315 | } else { |
316 | return false; |
317 | } |
318 | } |
319 | |
320 | |
18a97fd8 |
321 | /** |
322 | * Get all the records and count them |
8f0cd6ef |
323 | * |
18a97fd8 |
324 | * Get all the records and count them |
325 | * |
6bc502cc |
326 | * @param type description |
7cf1c7bd |
327 | * @todo Finish documenting this function |
18a97fd8 |
328 | */ |
d4419d55 |
329 | function count_records($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') { |
df28d6c5 |
330 | |
331 | global $CFG; |
332 | |
5c63e0c4 |
333 | if ($field1) { |
d4419d55 |
334 | $select = 'WHERE '. $field1 .' = \''. $value1 .'\''; |
9fa49e22 |
335 | if ($field2) { |
d4419d55 |
336 | $select .= ' AND '. $field2 .' = \''. $value2 .'\''; |
9fa49e22 |
337 | if ($field3) { |
d4419d55 |
338 | $select .= ' AND '. $field3 .' = \''. $value3 .'\''; |
df28d6c5 |
339 | } |
340 | } |
5c63e0c4 |
341 | } else { |
d4419d55 |
342 | $select = ''; |
df28d6c5 |
343 | } |
344 | |
d4419d55 |
345 | return count_records_sql('SELECT COUNT(*) FROM '. $CFG->prefix . $table .' '. $select); |
df28d6c5 |
346 | } |
347 | |
18a97fd8 |
348 | /** |
349 | * Get all the records and count them |
8f0cd6ef |
350 | * |
18a97fd8 |
351 | * Get all the records and count them |
352 | * |
6bc502cc |
353 | * @param type description |
7cf1c7bd |
354 | * @todo Finish documenting this function |
18a97fd8 |
355 | * |
356 | */ |
d4419d55 |
357 | function count_records_select($table, $select='', $countitem='COUNT(*)') { |
9fa49e22 |
358 | |
359 | global $CFG; |
360 | |
d26d7ed0 |
361 | if ($select) { |
d4419d55 |
362 | $select = 'WHERE '.$select; |
d26d7ed0 |
363 | } |
364 | |
d4419d55 |
365 | return count_records_sql('SELECT '. $countitem .' FROM '. $CFG->prefix . $table .' '. $select); |
9fa49e22 |
366 | } |
367 | |
368 | |
18a97fd8 |
369 | /** |
370 | * Get all the records and count them |
8f0cd6ef |
371 | * |
18a97fd8 |
372 | * The sql statement is provided as a string. |
373 | * |
6bc502cc |
374 | * @param type description |
7cf1c7bd |
375 | * @todo Finish documenting this function |
18a97fd8 |
376 | */ |
df28d6c5 |
377 | function count_records_sql($sql) { |
df28d6c5 |
378 | |
06b1db82 |
379 | global $CFG, $db; |
df28d6c5 |
380 | |
d4419d55 |
381 | $rs = $db->Execute($sql); |
9c7fee6c |
382 | if (!$rs) { |
2eef791f |
383 | if (isset($CFG->debug) and $CFG->debug > 7) { |
d4419d55 |
384 | notify($db->ErrorMsg() .'<br /><br />'. $sql); |
9c7fee6c |
385 | } |
386 | return 0; |
387 | } |
df28d6c5 |
388 | |
389 | return $rs->fields[0]; |
390 | } |
391 | |
a3fb1c45 |
392 | |
393 | |
394 | |
395 | /// GENERIC FUNCTIONS TO GET, INSERT, OR UPDATE DATA /////////////////////////////////// |
396 | |
18a97fd8 |
397 | /** |
398 | * Get a single record as an object |
8f0cd6ef |
399 | * |
18a97fd8 |
400 | * Get a single record as an object |
401 | * |
6bc502cc |
402 | * @param string $table the name of the table to select from |
403 | * @param string $field1 the name of the field for the first criteria |
404 | * @param string $value1 the value of the field for the first criteria |
405 | * @param string $field2 the name of the field for the second criteria |
406 | * @param string $value2 the value of the field for the second criteria |
407 | * @param string $field3 the name of the field for the third criteria |
408 | * @param string $value3 the value of the field for the third criteria |
409 | * @return object(fieldset) a fieldset object containing the first record selected |
18a97fd8 |
410 | */ |
d4419d55 |
411 | function get_record($table, $field1, $value1, $field2='', $value2='', $field3='', $value3='') { |
8f0cd6ef |
412 | |
7427070a |
413 | global $CFG ; |
df28d6c5 |
414 | |
d4419d55 |
415 | $select = 'WHERE '. $field1 .' = \''. $value1 .'\''; |
df28d6c5 |
416 | |
9fa49e22 |
417 | if ($field2) { |
d4419d55 |
418 | $select .= ' AND '. $field2 .' = \''. $value2 .'\''; |
9fa49e22 |
419 | if ($field3) { |
d4419d55 |
420 | $select .= ' AND '. $field3 .' = \''. $value3 .'\''; |
df28d6c5 |
421 | } |
422 | } |
423 | |
d4419d55 |
424 | return get_record_sql('SELECT * FROM '. $CFG->prefix . $table .' '. $select); |
df28d6c5 |
425 | } |
426 | |
18a97fd8 |
427 | /** |
428 | * Get a single record as an object |
8f0cd6ef |
429 | * |
18a97fd8 |
430 | * The sql statement is provided as a string. |
431 | * A LIMIT is normally added to only look for 1 record |
432 | * |
6bc502cc |
433 | * @param type description |
7cf1c7bd |
434 | * @todo Finish documenting this function |
18a97fd8 |
435 | */ |
df28d6c5 |
436 | function get_record_sql($sql) { |
df28d6c5 |
437 | |
4d7a3735 |
438 | global $db, $CFG; |
df28d6c5 |
439 | |
2eef791f |
440 | if (isset($CFG->debug) and $CFG->debug > 7) { // Debugging mode - don't use limit |
d4419d55 |
441 | $limit = ''; |
4d7a3735 |
442 | } else { |
d4419d55 |
443 | $limit = ' LIMIT 1'; // Workaround - limit to one record |
4d7a3735 |
444 | } |
445 | |
d4419d55 |
446 | if (!$rs = $db->Execute($sql . $limit)) { |
2eef791f |
447 | if (isset($CFG->debug) and $CFG->debug > 7) { // Debugging mode - print checks |
d4419d55 |
448 | notify( $db->ErrorMsg() . '<br /><br />'. $sql . $limit ); |
7618a8eb |
449 | } |
450 | return false; |
451 | } |
4d7a3735 |
452 | |
7618a8eb |
453 | if (!$recordcount = $rs->RecordCount()) { |
454 | return false; // Found no records |
4d7a3735 |
455 | } |
df28d6c5 |
456 | |
7618a8eb |
457 | if ($recordcount == 1) { // Found one record |
df28d6c5 |
458 | return (object)$rs->fields; |
4d7a3735 |
459 | |
7618a8eb |
460 | } else { // Error: found more than one record |
d4419d55 |
461 | notify('Error: Turn off debugging to hide this error.'); |
462 | notify($sql . $limit); |
4d7a3735 |
463 | if ($records = $rs->GetAssoc(true)) { |
d4419d55 |
464 | notify('Found more than one record in get_record_sql !'); |
4d7a3735 |
465 | print_object($records); |
4d7a3735 |
466 | } else { |
d4419d55 |
467 | notify('Very strange error in get_record_sql !'); |
7618a8eb |
468 | print_object($rs); |
4d7a3735 |
469 | } |
d4419d55 |
470 | print_continue($CFG->wwwroot .'/admin/config.php'); |
df28d6c5 |
471 | } |
472 | } |
473 | |
18a97fd8 |
474 | /** |
475 | * Gets one record from a table, as an object |
8f0cd6ef |
476 | * |
18a97fd8 |
477 | * "select" is a fragment of SQL to define the selection criteria |
478 | * |
6bc502cc |
479 | * @param type description |
7cf1c7bd |
480 | * @todo Finish documenting this function |
18a97fd8 |
481 | */ |
d4419d55 |
482 | function get_record_select($table, $select='', $fields='*') { |
18496c59 |
483 | |
484 | global $CFG; |
485 | |
486 | if ($select) { |
d4419d55 |
487 | $select = 'WHERE '. $select; |
18496c59 |
488 | } |
489 | |
d4419d55 |
490 | return get_record_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select); |
18496c59 |
491 | } |
492 | |
493 | |
18a97fd8 |
494 | /** |
495 | * Get a number of records as an array of objects |
8f0cd6ef |
496 | * |
18a97fd8 |
497 | * Can optionally be sorted eg "time ASC" or "time DESC" |
498 | * If "fields" is specified, only those fields are returned |
499 | * The "key" is the first column returned, eg usually "id" |
500 | * limitfrom and limitnum must both be specified or not at all |
501 | * |
6bc502cc |
502 | * @param type description |
7cf1c7bd |
503 | * @todo Finish documenting this function |
18a97fd8 |
504 | */ |
d4419d55 |
505 | function get_records($table, $field='', $value='', $sort='', $fields='*', $limitfrom='', $limitnum='') { |
df28d6c5 |
506 | |
507 | global $CFG; |
508 | |
9fa49e22 |
509 | if ($field) { |
d4419d55 |
510 | $select = 'WHERE '. $field .' = \''. $value .'\''; |
5c63e0c4 |
511 | } else { |
d4419d55 |
512 | $select = ''; |
df28d6c5 |
513 | } |
5c63e0c4 |
514 | |
d4419d55 |
515 | if ($limitfrom !== '') { |
0eeac484 |
516 | switch ($CFG->dbtype) { |
d4419d55 |
517 | case 'mysql': |
518 | $limit = 'LIMIT '. $limitfrom .','. $limitnum; |
0eeac484 |
519 | break; |
d4419d55 |
520 | case 'postgres7': |
521 | $limit = 'LIMIT '. $limitnum .' OFFSET '. $limitfrom; |
0eeac484 |
522 | break; |
8f0cd6ef |
523 | default: |
d4419d55 |
524 | $limit = 'LIMIT '. $limitnum .','. $limitfrom; |
0eeac484 |
525 | } |
526 | } else { |
d4419d55 |
527 | $limit = ''; |
0eeac484 |
528 | } |
529 | |
df28d6c5 |
530 | if ($sort) { |
d4419d55 |
531 | $sort = 'ORDER BY '. $sort; |
df28d6c5 |
532 | } |
533 | |
d4419d55 |
534 | return get_records_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select .' '. $sort .' '. $limit); |
df28d6c5 |
535 | } |
536 | |
18a97fd8 |
537 | /** |
538 | * Get a number of records as an array of objects |
8f0cd6ef |
539 | * |
18a97fd8 |
540 | * Can optionally be sorted eg "time ASC" or "time DESC" |
541 | * "select" is a fragment of SQL to define the selection criteria |
542 | * The "key" is the first column returned, eg usually "id" |
4f91b296 |
543 | * limitfrom and limitnum must both be specified or not at all |
18a97fd8 |
544 | * |
6bc502cc |
545 | * @param type description |
7cf1c7bd |
546 | * @todo Finish documenting this function |
18a97fd8 |
547 | */ |
d4419d55 |
548 | function get_records_select($table, $select='', $sort='', $fields='*', $limitfrom='', $limitnum='') { |
9fa49e22 |
549 | |
550 | global $CFG; |
551 | |
d26d7ed0 |
552 | if ($select) { |
d4419d55 |
553 | $select = 'WHERE '. $select; |
5c63e0c4 |
554 | } |
555 | |
d4419d55 |
556 | if ($limitfrom !== '') { |
4f91b296 |
557 | switch ($CFG->dbtype) { |
d4419d55 |
558 | case 'mysql': |
559 | $limit = 'LIMIT '. $limitfrom .','. $limitnum; |
4f91b296 |
560 | break; |
d4419d55 |
561 | case 'postgres7': |
562 | $limit = 'LIMIT '. $limitnum .' OFFSET '. $limitfrom; |
4f91b296 |
563 | break; |
8f0cd6ef |
564 | default: |
d4419d55 |
565 | $limit = 'LIMIT '. $limitnum .','. $limitfrom; |
4f91b296 |
566 | } |
567 | } else { |
d4419d55 |
568 | $limit = ''; |
4f91b296 |
569 | } |
570 | |
5c63e0c4 |
571 | if ($sort) { |
d4419d55 |
572 | $sort = 'ORDER BY '. $sort; |
d26d7ed0 |
573 | } |
574 | |
d4419d55 |
575 | return get_records_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select .' '. $sort .' '. $limit); |
9fa49e22 |
576 | } |
577 | |
df28d6c5 |
578 | |
18a97fd8 |
579 | /** |
580 | * Get a number of records as an array of objects |
8f0cd6ef |
581 | * |
582 | * Differs from get_records() in that the values variable |
18a97fd8 |
583 | * can be a comma-separated list of values eg "4,5,6,10" |
584 | * Can optionally be sorted eg "time ASC" or "time DESC" |
585 | * The "key" is the first column returned, eg usually "id" |
586 | * |
6bc502cc |
587 | * @param type description |
7cf1c7bd |
588 | * @todo Finish documenting this function |
18a97fd8 |
589 | */ |
d4419d55 |
590 | function get_records_list($table, $field='', $values='', $sort='', $fields='*') { |
df28d6c5 |
591 | |
592 | global $CFG; |
593 | |
9fa49e22 |
594 | if ($field) { |
d4419d55 |
595 | $select = 'WHERE '. $field .' IN ( '. $values .')'; |
5c63e0c4 |
596 | } else { |
d4419d55 |
597 | $select = ''; |
df28d6c5 |
598 | } |
5c63e0c4 |
599 | |
df28d6c5 |
600 | if ($sort) { |
d4419d55 |
601 | $sort = 'ORDER BY '. $sort; |
df28d6c5 |
602 | } |
603 | |
d4419d55 |
604 | return get_records_sql('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select .' '. $sort); |
df28d6c5 |
605 | } |
606 | |
607 | |
9fa49e22 |
608 | |
18a97fd8 |
609 | /** |
610 | * Get a number of records as an array of objects |
8f0cd6ef |
611 | * |
18a97fd8 |
612 | * The "key" is the first column returned, eg usually "id" |
613 | * The sql statement is provided as a string. |
614 | * |
6bc502cc |
615 | * @param type description |
7cf1c7bd |
616 | * @todo Finish documenting this function |
18a97fd8 |
617 | */ |
df28d6c5 |
618 | function get_records_sql($sql) { |
df28d6c5 |
619 | |
9c7fee6c |
620 | global $CFG,$db; |
df28d6c5 |
621 | |
9c7fee6c |
622 | if (!$rs = $db->Execute($sql)) { |
2eef791f |
623 | if (isset($CFG->debug) and $CFG->debug > 7) { |
d4419d55 |
624 | notify($db->ErrorMsg() .'<br /><br />'. $sql); |
9c7fee6c |
625 | } |
626 | return false; |
627 | } |
8f0cd6ef |
628 | |
df28d6c5 |
629 | if ( $rs->RecordCount() > 0 ) { |
630 | if ($records = $rs->GetAssoc(true)) { |
631 | foreach ($records as $key => $record) { |
632 | $objects[$key] = (object) $record; |
633 | } |
634 | return $objects; |
635 | } else { |
636 | return false; |
637 | } |
638 | } else { |
639 | return false; |
640 | } |
641 | } |
642 | |
18a97fd8 |
643 | /** |
644 | * Get a number of records as an array of objects |
8f0cd6ef |
645 | * |
18a97fd8 |
646 | * Can optionally be sorted eg "time ASC" or "time DESC" |
647 | * If "fields" is specified, only those fields are returned |
648 | * The "key" is the first column returned, eg usually "id" |
8f0cd6ef |
649 | * |
6bc502cc |
650 | * @param type description |
7cf1c7bd |
651 | * @todo Finish documenting this function |
18a97fd8 |
652 | */ |
d4419d55 |
653 | function get_records_menu($table, $field='', $value='', $sort='', $fields='*') { |
9fa49e22 |
654 | |
655 | global $CFG; |
656 | |
657 | if ($field) { |
d4419d55 |
658 | $select = 'WHERE '. $field .' = \''. $value .'\''; |
5c63e0c4 |
659 | } else { |
d4419d55 |
660 | $select = ''; |
9fa49e22 |
661 | } |
5c63e0c4 |
662 | |
9fa49e22 |
663 | if ($sort) { |
d4419d55 |
664 | $sort = 'ORDER BY '. $sort; |
9fa49e22 |
665 | } |
666 | |
d4419d55 |
667 | return get_records_sql_menu('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select .' '. $sort); |
9fa49e22 |
668 | } |
669 | |
18a97fd8 |
670 | /** |
ae01b52a |
671 | * Get a number of records as an array of values |
672 | * |
18a97fd8 |
673 | * Can optionally be sorted eg "time ASC" or "time DESC" |
674 | * "select" is a fragment of SQL to define the selection criteria |
675 | * Returns associative array of first two fields |
8f0cd6ef |
676 | * |
6bc502cc |
677 | * @param type description |
7cf1c7bd |
678 | * @todo Finish documenting this function |
18a97fd8 |
679 | */ |
d4419d55 |
680 | function get_records_select_menu($table, $select='', $sort='', $fields='*') { |
9fa49e22 |
681 | |
682 | global $CFG; |
683 | |
d26d7ed0 |
684 | if ($select) { |
d4419d55 |
685 | $select = 'WHERE '. $select; |
d26d7ed0 |
686 | } |
687 | |
5c63e0c4 |
688 | if ($sort) { |
d4419d55 |
689 | $sort = 'ORDER BY '. $sort; |
5c63e0c4 |
690 | } |
691 | |
d4419d55 |
692 | return get_records_sql_menu('SELECT '. $fields .' FROM '. $CFG->prefix . $table .' '. $select .' '. $sort); |
9fa49e22 |
693 | } |
694 | |
695 | |
18a97fd8 |
696 | /** |
8f0cd6ef |
697 | * Given an SQL select, this function returns an associative |
698 | * |
699 | * array of the first two columns. This is most useful in |
700 | * combination with the choose_from_menu function to create |
18a97fd8 |
701 | * a form menu. |
702 | * |
6bc502cc |
703 | * @param type description |
7cf1c7bd |
704 | * @todo Finish documenting this function |
18a97fd8 |
705 | */ |
df28d6c5 |
706 | function get_records_sql_menu($sql) { |
df28d6c5 |
707 | |
06b1db82 |
708 | global $CFG, $db; |
df28d6c5 |
709 | |
9c7fee6c |
710 | if (!$rs = $db->Execute($sql)) { |
2eef791f |
711 | if (isset($CFG->debug) and $CFG->debug > 7) { |
d4419d55 |
712 | notify($db->ErrorMsg() .'<br /><br />'. $sql); |
9c7fee6c |
713 | } |
714 | return false; |
715 | } |
df28d6c5 |
716 | |
717 | if ( $rs->RecordCount() > 0 ) { |
718 | while (!$rs->EOF) { |
719 | $menu[$rs->fields[0]] = $rs->fields[1]; |
720 | $rs->MoveNext(); |
721 | } |
722 | return $menu; |
8f0cd6ef |
723 | |
df28d6c5 |
724 | } else { |
725 | return false; |
726 | } |
727 | } |
728 | |
18a97fd8 |
729 | /** |
730 | * Get a single field from a database record |
8f0cd6ef |
731 | * |
18a97fd8 |
732 | * longdesc |
733 | * |
6bc502cc |
734 | * @param type description |
7cf1c7bd |
735 | * @todo Finish documenting this function |
18a97fd8 |
736 | */ |
d4419d55 |
737 | function get_field($table, $return, $field1, $value1, $field2='', $value2='', $field3='', $value3='') { |
df28d6c5 |
738 | |
739 | global $db, $CFG; |
740 | |
d4419d55 |
741 | $select = 'WHERE '. $field1 .' = \''. $value1 .'\''; |
ec2a28a6 |
742 | |
743 | if ($field2) { |
d4419d55 |
744 | $select .= ' AND '. $field2 .' = \''. $value2 .'\''; |
ec2a28a6 |
745 | if ($field3) { |
d4419d55 |
746 | $select .= ' AND '. $field3 .' = \''. $value3 .'\''; |
ec2a28a6 |
747 | } |
748 | } |
749 | |
d4419d55 |
750 | $rs = $db->Execute('SELECT '. $return .' FROM '. $CFG->prefix . $table .' '. $select); |
9c7fee6c |
751 | if (!$rs) { |
2eef791f |
752 | if (isset($CFG->debug) and $CFG->debug > 7) { |
d4419d55 |
753 | notify($db->ErrorMsg() .'<br /><br />SELECT '. $return .' FROM '. $CFG->prefix . $table .' '. $select); |
9c7fee6c |
754 | } |
755 | return false; |
756 | } |
df28d6c5 |
757 | |
758 | if ( $rs->RecordCount() == 1 ) { |
d4419d55 |
759 | return $rs->fields[$return]; |
df28d6c5 |
760 | } else { |
761 | return false; |
762 | } |
763 | } |
764 | |
b4bac9b6 |
765 | |
766 | /** |
767 | * Get a single field from a database record |
8f0cd6ef |
768 | * |
b4bac9b6 |
769 | * longdesc |
770 | * |
771 | * @param type description |
7cf1c7bd |
772 | * @todo Finish documenting this function |
b4bac9b6 |
773 | */ |
774 | function get_field_sql($sql) { |
775 | |
776 | global $db, $CFG; |
777 | |
778 | $rs = $db->Execute($sql); |
779 | if (!$rs) { |
780 | if (isset($CFG->debug) and $CFG->debug > 7) { |
d4419d55 |
781 | notify($db->ErrorMsg() .'<br /><br />'. $sql); |
b4bac9b6 |
782 | } |
783 | return false; |
784 | } |
785 | |
786 | if ( $rs->RecordCount() == 1 ) { |
787 | return $rs->fields[0]; |
788 | } else { |
789 | return false; |
790 | } |
791 | } |
792 | |
18a97fd8 |
793 | /** |
794 | * Set a single field in a database record |
8f0cd6ef |
795 | * |
18a97fd8 |
796 | * longdesc |
797 | * |
6bc502cc |
798 | * @param type description |
7cf1c7bd |
799 | * @todo Finish documenting this function |
18a97fd8 |
800 | */ |
d4419d55 |
801 | function set_field($table, $newfield, $newvalue, $field1, $value1, $field2='', $value2='', $field3='', $value3='') { |
df28d6c5 |
802 | |
803 | global $db, $CFG; |
804 | |
d4419d55 |
805 | $select = 'WHERE '. $field1 .' = \''. $value1 .'\''; |
ec2a28a6 |
806 | |
807 | if ($field2) { |
d4419d55 |
808 | $select .= ' AND '. $field2 .' = \''. $value2 .'\''; |
ec2a28a6 |
809 | if ($field3) { |
d4419d55 |
810 | $select .= ' AND '. $field3 .' = \''. $value3 .'\''; |
ec2a28a6 |
811 | } |
812 | } |
813 | |
d4419d55 |
814 | return $db->Execute('UPDATE '. $CFG->prefix . $table .' SET '. $newfield .' = \''. $newvalue .'\' '. $select); |
df28d6c5 |
815 | } |
816 | |
817 | |
18a97fd8 |
818 | /** |
819 | * Delete one or more records from a table |
8f0cd6ef |
820 | * |
18a97fd8 |
821 | * Delete one or more records from a table |
822 | * |
6bc502cc |
823 | * @param type description |
7cf1c7bd |
824 | * @todo Finish documenting this function |
18a97fd8 |
825 | */ |
d4419d55 |
826 | function delete_records($table, $field1='', $value1='', $field2='', $value2='', $field3='', $value3='') { |
df28d6c5 |
827 | |
828 | global $db, $CFG; |
829 | |
5c63e0c4 |
830 | if ($field1) { |
d4419d55 |
831 | $select = 'WHERE '. $field1 .' = \''. $value1 .'\''; |
9fa49e22 |
832 | if ($field2) { |
d4419d55 |
833 | $select .= ' AND '. $field2 .' = \''. $value2 .'\''; |
9fa49e22 |
834 | if ($field3) { |
d4419d55 |
835 | $select .= ' AND '. $field3 .' = \''. $value3 .'\''; |
df28d6c5 |
836 | } |
837 | } |
5c63e0c4 |
838 | } else { |
d4419d55 |
839 | $select = ''; |
df28d6c5 |
840 | } |
841 | |
d4419d55 |
842 | return $db->Execute('DELETE FROM '. $CFG->prefix . $table .' '. $select); |
df28d6c5 |
843 | } |
844 | |
18a97fd8 |
845 | /** |
846 | * Delete one or more records from a table |
8f0cd6ef |
847 | * |
18a97fd8 |
848 | * "select" is a fragment of SQL to define the selection criteria |
849 | * |
6bc502cc |
850 | * @param type description |
7cf1c7bd |
851 | * @todo Finish documenting this function |
18a97fd8 |
852 | */ |
d4419d55 |
853 | function delete_records_select($table, $select='') { |
30f89d68 |
854 | |
855 | global $CFG, $db; |
856 | |
857 | if ($select) { |
d4419d55 |
858 | $select = 'WHERE '.$select; |
30f89d68 |
859 | } |
860 | |
d4419d55 |
861 | return $db->Execute('DELETE FROM '. $CFG->prefix . $table .' '. $select); |
30f89d68 |
862 | } |
863 | |
df28d6c5 |
864 | |
18a97fd8 |
865 | /** |
866 | * Insert a record into a table and return the "id" field if required |
8f0cd6ef |
867 | * |
18a97fd8 |
868 | * If the return ID isn't required, then this just reports success as true/false. |
869 | * $dataobject is an object containing needed data |
870 | * |
6bc502cc |
871 | * @param type description |
7cf1c7bd |
872 | * @todo Finish documenting this function |
18a97fd8 |
873 | */ |
fcf9c450 |
874 | function insert_record($table, $dataobject, $returnid=true, $primarykey='id') { |
8f0cd6ef |
875 | |
0892f7bd |
876 | global $db, $CFG; |
877 | |
d4469f2a |
878 | /// Execute a dummy query to get an empty recordset |
d4419d55 |
879 | if (!$rs = $db->Execute('SELECT * FROM '. $CFG->prefix . $table .' WHERE '. $primarykey .' = \'-1\'')) { |
d4469f2a |
880 | return false; |
881 | } |
8f0cd6ef |
882 | |
d4469f2a |
883 | /// Get the correct SQL from adoDB |
884 | if (!$insertSQL = $db->GetInsertSQL($rs, (array)$dataobject, true)) { |
885 | return false; |
886 | } |
8f0cd6ef |
887 | |
d4469f2a |
888 | /// Run the SQL statement |
889 | if (!$rs = $db->Execute($insertSQL)) { |
2eef791f |
890 | if (isset($CFG->debug) and $CFG->debug > 7) { |
d4419d55 |
891 | notify($db->ErrorMsg() .'<br /><br />'.$insertSQL); |
9c7fee6c |
892 | } |
0892f7bd |
893 | return false; |
894 | } |
8f0cd6ef |
895 | |
d4469f2a |
896 | /// If a return ID is not needed then just return true now |
8f0cd6ef |
897 | if (!$returnid) { |
0892f7bd |
898 | return true; |
899 | } |
900 | |
d4469f2a |
901 | /// Find the return ID of the newly inserted record |
0892f7bd |
902 | switch ($CFG->dbtype) { |
d4419d55 |
903 | case 'postgres7': // Just loves to be special |
0892f7bd |
904 | $oid = $db->Insert_ID(); |
d4419d55 |
905 | if ($rs = $db->Execute('SELECT '. $primarykey .' FROM '. $CFG->prefix . $table .' WHERE oid = '. $oid)) { |
0892f7bd |
906 | if ($rs->RecordCount() == 1) { |
fcf9c450 |
907 | return (integer) $rs->fields[0]; |
0892f7bd |
908 | } |
909 | } |
910 | return false; |
911 | |
8f0cd6ef |
912 | default: |
d4469f2a |
913 | return $db->Insert_ID(); // Should work on most databases, but not all! |
0892f7bd |
914 | } |
915 | } |
916 | |
917 | |
18a97fd8 |
918 | /** |
919 | * Update a record in a table |
8f0cd6ef |
920 | * |
18a97fd8 |
921 | * $dataobject is an object containing needed data |
8f0cd6ef |
922 | * Relies on $dataobject having a variable "id" to |
18a97fd8 |
923 | * specify the record to update |
924 | * |
6bc502cc |
925 | * @param type description |
7cf1c7bd |
926 | * @todo Finish documenting this function |
18a97fd8 |
927 | */ |
df28d6c5 |
928 | function update_record($table, $dataobject) { |
df28d6c5 |
929 | |
930 | global $db, $CFG; |
931 | |
932 | if (! isset($dataobject->id) ) { |
933 | return false; |
934 | } |
935 | |
936 | // Determine all the fields in the table |
d4419d55 |
937 | if (!$columns = $db->MetaColumns($CFG->prefix . $table)) { |
df28d6c5 |
938 | return false; |
939 | } |
940 | $data = (array)$dataobject; |
941 | |
942 | // Pull out data matching these fields |
943 | foreach ($columns as $column) { |
d4419d55 |
944 | if ($column->name <> 'id' and isset($data[$column->name]) ) { |
df28d6c5 |
945 | $ddd[$column->name] = $data[$column->name]; |
946 | } |
947 | } |
948 | |
949 | // Construct SQL queries |
950 | $numddd = count($ddd); |
951 | $count = 0; |
d4419d55 |
952 | $update = ''; |
df28d6c5 |
953 | |
954 | foreach ($ddd as $key => $value) { |
955 | $count++; |
d4419d55 |
956 | $update .= $key .' = \''. $value .'\''; |
df28d6c5 |
957 | if ($count < $numddd) { |
d4419d55 |
958 | $update .= ', '; |
df28d6c5 |
959 | } |
960 | } |
961 | |
d4419d55 |
962 | if ($rs = $db->Execute('UPDATE '. $CFG->prefix . $table .' SET '. $update .' WHERE id = \''. $dataobject->id .'\'')) { |
df28d6c5 |
963 | return true; |
964 | } else { |
2eef791f |
965 | if (isset($CFG->debug) and $CFG->debug > 7) { |
d4419d55 |
966 | notify($db->ErrorMsg() .'<br /><br />UPDATE '. $CFG->prefix . $table .' SET '. $update .' WHERE id = \''. $dataobject->id .'\''); |
9c7fee6c |
967 | } |
df28d6c5 |
968 | return false; |
969 | } |
970 | } |
971 | |
972 | |
df28d6c5 |
973 | |
974 | |
975 | /// USER DATABASE //////////////////////////////////////////////// |
976 | |
18a97fd8 |
977 | /** |
8f0cd6ef |
978 | * Get a complete user record, which includes all the info |
979 | * |
18a97fd8 |
980 | * in the user record, as well as membership information |
981 | * Suitable for setting as $USER session cookie. |
982 | * |
6bc502cc |
983 | * @param type description |
7cf1c7bd |
984 | * @todo Finish documenting this function |
18a97fd8 |
985 | */ |
df28d6c5 |
986 | function get_user_info_from_db($field, $value) { |
df28d6c5 |
987 | |
ca60b03b |
988 | global $CFG; |
989 | |
18496c59 |
990 | if (!$field or !$value) { |
df28d6c5 |
991 | return false; |
df28d6c5 |
992 | } |
993 | |
ca60b03b |
994 | /// Get all the basic user data |
995 | |
d4419d55 |
996 | if (! $user = get_record_select('user', $field .' = \''. $value .'\' AND deleted <> \'1\'')) { |
18496c59 |
997 | return false; |
998 | } |
df28d6c5 |
999 | |
ca60b03b |
1000 | /// Add membership information |
1001 | |
d4419d55 |
1002 | if ($admins = get_records('user_admins', 'userid', $user->id)) { |
ca60b03b |
1003 | $user->admin = true; |
1004 | } |
df28d6c5 |
1005 | |
222ac91b |
1006 | $user->student[SITEID] = isstudent(SITEID, $user->id); |
df28d6c5 |
1007 | |
ca60b03b |
1008 | /// Determine enrolments based on current enrolment module |
df28d6c5 |
1009 | |
d4419d55 |
1010 | require_once($CFG->dirroot .'/enrol/'. $CFG->enrol .'/enrol.php'); |
ca60b03b |
1011 | $enrol = new enrolment_plugin(); |
1012 | $enrol->get_student_courses($user); |
1013 | $enrol->get_teacher_courses($user); |
df28d6c5 |
1014 | |
ca60b03b |
1015 | /// Get various settings and preferences |
18496c59 |
1016 | |
d4419d55 |
1017 | if ($displays = get_records('course_display', 'userid', $user->id)) { |
b86fc0e2 |
1018 | foreach ($displays as $display) { |
1019 | $user->display[$display->course] = $display->display; |
1020 | } |
1021 | } |
1022 | |
ca60b03b |
1023 | if ($preferences = get_records('user_preferences', 'userid', $user->id)) { |
1024 | foreach ($preferences as $preference) { |
1025 | $user->preference[$preference->name] = $preference->value; |
1026 | } |
1027 | } |
1028 | |
d4419d55 |
1029 | if ($groups = get_records('groups_members', 'userid', $user->id)) { |
3facf28f |
1030 | foreach ($groups as $groupmember) { |
d4419d55 |
1031 | $courseid = get_field('groups', 'courseid', 'id', $groupmember->groupid); |
3facf28f |
1032 | $user->groupmember[$courseid] = $groupmember->groupid; |
f374fb10 |
1033 | } |
1034 | } |
1035 | |
70812e39 |
1036 | |
18496c59 |
1037 | return $user; |
df28d6c5 |
1038 | } |
1039 | |
df28d6c5 |
1040 | |
18a97fd8 |
1041 | /** |
1042 | * Does this username and password specify a valid admin user? |
8f0cd6ef |
1043 | * |
18a97fd8 |
1044 | * longdesc |
1045 | * |
6bc502cc |
1046 | * @param type description |
7cf1c7bd |
1047 | * @todo Finish documenting this function |
18a97fd8 |
1048 | */ |
df28d6c5 |
1049 | function adminlogin($username, $md5password) { |
df28d6c5 |
1050 | |
1051 | global $CFG; |
1052 | |
8f0cd6ef |
1053 | return record_exists_sql("SELECT u.id |
1054 | FROM {$CFG->prefix}user u, |
1055 | {$CFG->prefix}user_admins a |
1056 | WHERE u.id = a.userid |
1057 | AND u.username = '$username' |
df28d6c5 |
1058 | AND u.password = '$md5password'"); |
1059 | } |
1060 | |
1061 | |
18a97fd8 |
1062 | /** |
1063 | * Get the guest user information from the database |
8f0cd6ef |
1064 | * |
18a97fd8 |
1065 | * longdesc |
1066 | * |
6bc502cc |
1067 | * @param type description |
7cf1c7bd |
1068 | * @todo Finish documenting this function |
18a97fd8 |
1069 | */ |
9fa49e22 |
1070 | function get_guest() { |
d4419d55 |
1071 | return get_user_info_from_db('username', 'guest'); |
9fa49e22 |
1072 | } |
1073 | |
1074 | |
18a97fd8 |
1075 | /** |
1076 | * Returns $user object of the main admin user |
8f0cd6ef |
1077 | * |
18a97fd8 |
1078 | * longdesc |
1079 | * |
6bc502cc |
1080 | * @param type description |
7cf1c7bd |
1081 | * @todo Finish documenting this function |
18a97fd8 |
1082 | */ |
df28d6c5 |
1083 | function get_admin () { |
df28d6c5 |
1084 | |
1085 | global $CFG; |
1086 | |
1087 | if ( $admins = get_admins() ) { |
1088 | foreach ($admins as $admin) { |
8f0cd6ef |
1089 | return $admin; // ie the first one |
df28d6c5 |
1090 | } |
1091 | } else { |
1092 | return false; |
1093 | } |
1094 | } |
1095 | |
18a97fd8 |
1096 | /** |
1097 | * Returns list of all admins |
8f0cd6ef |
1098 | * |
18a97fd8 |
1099 | * longdesc |
1100 | * |
6bc502cc |
1101 | * @param type description |
7cf1c7bd |
1102 | * @todo Finish documenting this function |
18a97fd8 |
1103 | */ |
df28d6c5 |
1104 | function get_admins() { |
df28d6c5 |
1105 | |
1106 | global $CFG; |
1107 | |
8f0cd6ef |
1108 | return get_records_sql("SELECT u.*, a.id as adminid |
1109 | FROM {$CFG->prefix}user u, |
ebc3bd2b |
1110 | {$CFG->prefix}user_admins a |
1111 | WHERE a.userid = u.id |
427fdf66 |
1112 | ORDER BY a.id ASC"); |
df28d6c5 |
1113 | } |
1114 | |
18a97fd8 |
1115 | /** |
1116 | * Returns list of all creators |
8f0cd6ef |
1117 | * |
18a97fd8 |
1118 | * longdesc |
1119 | * |
6bc502cc |
1120 | * @param type description |
7cf1c7bd |
1121 | * @todo Finish documenting this function |
18a97fd8 |
1122 | */ |
1924074c |
1123 | function get_creators() { |
1924074c |
1124 | |
1125 | global $CFG; |
1126 | |
1127 | return get_records_sql("SELECT u.* |
1128 | FROM {$CFG->prefix}user u, |
1129 | {$CFG->prefix}user_coursecreators a |
1130 | WHERE a.userid = u.id |
1131 | ORDER BY u.id ASC"); |
1132 | } |
df28d6c5 |
1133 | |
18a97fd8 |
1134 | /** |
1135 | * Returns $user object of the main teacher for a course |
8f0cd6ef |
1136 | * |
18a97fd8 |
1137 | * longdesc |
1138 | * |
6bc502cc |
1139 | * @param type description |
7cf1c7bd |
1140 | * @todo Finish documenting this function |
18a97fd8 |
1141 | */ |
df28d6c5 |
1142 | function get_teacher($courseid) { |
df28d6c5 |
1143 | |
1144 | global $CFG; |
1145 | |
d4419d55 |
1146 | if ( $teachers = get_course_teachers($courseid, 't.authority ASC')) { |
df28d6c5 |
1147 | foreach ($teachers as $teacher) { |
1148 | if ($teacher->authority) { |
1149 | return $teacher; // the highest authority teacher |
1150 | } |
1151 | } |
1152 | } else { |
1153 | return false; |
1154 | } |
1155 | } |
1156 | |
18a97fd8 |
1157 | /** |
1158 | * Searches logs to find all enrolments since a certain date |
8f0cd6ef |
1159 | * |
18a97fd8 |
1160 | * used to print recent activity |
1161 | * |
6bc502cc |
1162 | * @param type description |
7cf1c7bd |
1163 | * @todo Finish documenting this function |
18a97fd8 |
1164 | */ |
0a08581d |
1165 | function get_recent_enrolments($courseid, $timestart) { |
0a08581d |
1166 | |
1167 | global $CFG; |
1168 | |
fbf5081c |
1169 | return get_records_sql("SELECT DISTINCT u.id, u.firstname, u.lastname, l.time |
0a08581d |
1170 | FROM {$CFG->prefix}user u, |
1171 | {$CFG->prefix}user_students s, |
1172 | {$CFG->prefix}log l |
8f0cd6ef |
1173 | WHERE l.time > '$timestart' |
0a08581d |
1174 | AND l.course = '$courseid' |
8f0cd6ef |
1175 | AND l.module = 'course' |
0a08581d |
1176 | AND l.action = 'enrol' |
1177 | AND l.info = u.id |
1178 | AND u.id = s.userid |
64cbb55c |
1179 | AND s.course = '$courseid' |
0a08581d |
1180 | ORDER BY l.time ASC"); |
1181 | } |
1182 | |
18a97fd8 |
1183 | /** |
2cc72e84 |
1184 | * Returns array of userinfo of all students in this course |
1185 | * or on this site if courseid is id of site |
8f0cd6ef |
1186 | * |
6bc502cc |
1187 | * @param type description |
7cf1c7bd |
1188 | * @todo Finish documenting this function |
18a97fd8 |
1189 | */ |
d4419d55 |
1190 | function get_course_students($courseid, $sort='s.timeaccess', $dir='', $page=0, $recordsperpage=99999, |
1191 | $firstinitial='', $lastinitial='', $group=NULL, $search='', $fields='', $exceptions='') { |
df28d6c5 |
1192 | |
1193 | global $CFG; |
8f0cd6ef |
1194 | |
1195 | if ($courseid == SITEID and $CFG->allusersaresitestudents) { |
2700d113 |
1196 | // return users with confirmed, undeleted accounts who are not site teachers |
1197 | // the following is a mess because of different conventions in the different user functions |
1198 | $sort = str_replace('s.timeaccess', 'lastaccess', $sort); // site users can't be sorted by timeaccess |
1199 | $sort = str_replace('timeaccess', 'lastaccess', $sort); // site users can't be sorted by timeaccess |
1200 | $sort = str_replace('u.', '', $sort); // the get_user function doesn't use the u. prefix to fields |
8f0cd6ef |
1201 | $fields = str_replace('u.', '', $fields); |
2700d113 |
1202 | if ($sort) { |
d4419d55 |
1203 | $sort = $sort .' '. $dir; |
2700d113 |
1204 | } |
1205 | // Now we have to make sure site teachers are excluded |
1206 | if ($teachers = get_records('user_teachers', 'course', SITEID)) { |
1207 | foreach ($teachers as $teacher) { |
d4419d55 |
1208 | $exceptions .= ','. $teacher->userid; |
2700d113 |
1209 | } |
1210 | $exceptions = ltrim($exceptions, ','); |
1211 | } |
8f0cd6ef |
1212 | return get_users(true, $search, true, $exceptions, $sort, $firstinitial, $lastinitial, |
2700d113 |
1213 | $page, $recordsperpage, $fields ? $fields : '*'); |
1214 | } |
df28d6c5 |
1215 | |
4969ad74 |
1216 | switch ($CFG->dbtype) { |
d4419d55 |
1217 | case 'mysql': |
1218 | $fullname = ' CONCAT(firstname," ",lastname) '; |
1219 | $limit = 'LIMIT '. $page .','. $recordsperpage; |
1220 | $LIKE = 'LIKE'; |
4969ad74 |
1221 | break; |
d4419d55 |
1222 | case 'postgres7': |
73fc8c1a |
1223 | $fullname = " firstname||' '||lastname "; |
d4419d55 |
1224 | $limit = 'LIMIT '. $recordsperpage .' OFFSET '.($page); |
1225 | $LIKE = 'ILIKE'; |
4969ad74 |
1226 | break; |
8f0cd6ef |
1227 | default: |
d4419d55 |
1228 | $fullname = ' firstname||" "||lastname '; |
1229 | $limit = 'LIMIT '. $recordsperpage .','. $page; |
1230 | $LIKE = 'ILIKE'; |
a328425d |
1231 | } |
1232 | |
0720313b |
1233 | $groupmembers = ''; |
f950af3c |
1234 | |
1235 | // make sure it works on the site course |
d4419d55 |
1236 | $select = 's.course = \''. $courseid .'\' AND '; |
222ac91b |
1237 | if ($courseid == SITEID) { |
f950af3c |
1238 | $select = ''; |
1239 | } |
1240 | |
d4419d55 |
1241 | $select .= 's.userid = u.id AND u.deleted = \'0\' '; |
73fc8c1a |
1242 | |
1284a926 |
1243 | if (!$fields) { |
1244 | $fields = 'u.id, u.confirmed, u.username, u.firstname, u.lastname, '. |
1245 | 'u.maildisplay, u.mailformat, u.maildigest, u.email, u.city, '. |
1246 | 'u.country, u.picture, u.idnumber, u.department, u.institution, '. |
1247 | 'u.emailstop, u.lang, u.timezone, s.timeaccess as lastaccess'; |
1248 | } |
1249 | |
73fc8c1a |
1250 | if ($search) { |
d4419d55 |
1251 | $search = ' AND ('. $fullname .' '. $LIKE .'\'%'. $search .'%\' OR email '. $LIKE .'\'%'. $search .'%\') '; |
73fc8c1a |
1252 | } |
a328425d |
1253 | |
1254 | if ($firstinitial) { |
d4419d55 |
1255 | $select .= ' AND u.firstname '. $LIKE .'\''. $firstinitial .'%\' '; |
a328425d |
1256 | } |
1257 | |
1258 | if ($lastinitial) { |
d4419d55 |
1259 | $select .= ' AND u.lastname '. $LIKE .'\''. $lastinitial .'%\' '; |
4969ad74 |
1260 | } |
1261 | |
3d35e6b7 |
1262 | if ($group === 0) { /// Need something here to get all students not in a group |
1263 | return array(); |
1264 | |
1265 | } else if ($group !== NULL) { |
d4419d55 |
1266 | $groupmembers = ', '. $CFG->prefix .'groups_members gm '; |
1267 | $select .= ' AND u.id = gm.userid AND gm.groupid = \''. $group .'\''; |
0720313b |
1268 | } |
8f0cd6ef |
1269 | |
900df8b6 |
1270 | if (!empty($exceptions)) { |
d4419d55 |
1271 | $select .= ' AND u.id NOT IN ('. $exceptions .')'; |
900df8b6 |
1272 | } |
0720313b |
1273 | |
8e4c9ef7 |
1274 | if ($sort) { |
d4419d55 |
1275 | $sort = ' ORDER BY '. $sort .' '; |
8e4c9ef7 |
1276 | } |
1277 | |
2700d113 |
1278 | $students = get_records_sql("SELECT $fields |
488acd1b |
1279 | FROM {$CFG->prefix}user u, |
1280 | {$CFG->prefix}user_students s |
1281 | $groupmembers |
73fc8c1a |
1282 | WHERE $select $search $sort $dir $limit"); |
2700d113 |
1283 | |
1284 | if ($courseid != SITEID) { |
1285 | return $students; |
1286 | } |
8f0cd6ef |
1287 | |
2700d113 |
1288 | // We are here because we need the students for the site. |
1289 | // These also include teachers on real courses minus those on the site |
1290 | if ($teachers = get_records('user_teachers', 'course', SITEID)) { |
1291 | foreach ($teachers as $teacher) { |
d4419d55 |
1292 | $exceptions .= ','. $teacher->userid; |
2700d113 |
1293 | } |
1294 | $exceptions = ltrim($exceptions, ','); |
d4419d55 |
1295 | $select .= ' AND u.id NOT IN ('. $exceptions .')'; |
2700d113 |
1296 | } |
1297 | if (!$teachers = get_records_sql("SELECT $fields |
1298 | FROM {$CFG->prefix}user u, |
1299 | {$CFG->prefix}user_teachers s |
1300 | $groupmembers |
1301 | WHERE $select $search $sort $dir $limit")) { |
1302 | return $students; |
1303 | } |
1304 | if (!$students) { |
1305 | return $teachers; |
1306 | } |
1307 | return $teachers + $students; |
df28d6c5 |
1308 | } |
1309 | |
a328425d |
1310 | /** |
2cc72e84 |
1311 | * Counts the students in a given course (or site), or a subset of them |
8f0cd6ef |
1312 | * |
6bc502cc |
1313 | * @param type description |
7cf1c7bd |
1314 | * @todo Finish documenting this function |
a328425d |
1315 | */ |
d4419d55 |
1316 | function count_course_students($course, $search='', $firstinitial='', $lastinitial='', $group=NULL, $exceptions='') { |
a328425d |
1317 | |
2700d113 |
1318 | if ($students = get_course_students($course->id, '', '', 0, 999999, $firstinitial, $lastinitial, $group, $search, '', $exceptions)) { |
1319 | return count($students); |
a328425d |
1320 | } |
2700d113 |
1321 | return 0; |
a328425d |
1322 | } |
1323 | |
1324 | |
18a97fd8 |
1325 | /** |
8f0cd6ef |
1326 | * Returns list of all teachers in this course |
2cc72e84 |
1327 | * (also works for site) |
8f0cd6ef |
1328 | * |
6bc502cc |
1329 | * @param type description |
7cf1c7bd |
1330 | * @todo Finish documenting this function |
18a97fd8 |
1331 | */ |
d4419d55 |
1332 | function get_course_teachers($courseid, $sort='t.authority ASC', $exceptions='') { |
df28d6c5 |
1333 | |
1334 | global $CFG; |
1335 | |
900df8b6 |
1336 | if (!empty($exceptions)) { |
d4419d55 |
1337 | $except = ' AND u.id NOT IN ('. $exceptions .') '; |
900df8b6 |
1338 | } else { |
1339 | $except = ''; |
1340 | } |
1341 | |
8d241374 |
1342 | return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat, u.maildigest, |
8f0cd6ef |
1343 | u.email, u.city, u.country, u.lastlogin, u.picture, u.lang, u.timezone, |
86fb5ed5 |
1344 | u.emailstop, t.authority,t.role,t.editall,t.timeaccess as lastaccess |
8f0cd6ef |
1345 | FROM {$CFG->prefix}user u, |
688d06f4 |
1346 | {$CFG->prefix}user_teachers t |
8f0cd6ef |
1347 | WHERE t.course = '$courseid' AND t.userid = u.id |
2700d113 |
1348 | AND u.deleted = '0' AND u.confirmed = '1' $except |
df28d6c5 |
1349 | ORDER BY $sort"); |
1350 | } |
1351 | |
18a97fd8 |
1352 | /** |
1353 | * Returns all the users of a course: students and teachers |
18a97fd8 |
1354 | * |
6bc502cc |
1355 | * @param type description |
7cf1c7bd |
1356 | * @todo Finish documenting this function |
18a97fd8 |
1357 | */ |
d4419d55 |
1358 | function get_course_users($courseid, $sort='timeaccess DESC', $exceptions='', $fields='') { |
353d0338 |
1359 | |
900df8b6 |
1360 | /// Using this method because the single SQL is too inefficient |
1361 | // Note that this has the effect that teachers and students are |
1362 | // sorted individually. Returns first all teachers, then all students |
0720313b |
1363 | |
900df8b6 |
1364 | if (!$teachers = get_course_teachers($courseid, $sort, $exceptions)) { |
1365 | $teachers = array(); |
1366 | } |
65ee9c16 |
1367 | if (!$students = get_course_students($courseid, $sort, '', 0, 99999, '', '', NULL, '', $fields, $exceptions)) { |
900df8b6 |
1368 | $students = array(); |
1369 | } |
df28d6c5 |
1370 | |
900df8b6 |
1371 | return $teachers + $students; |
0720313b |
1372 | |
900df8b6 |
1373 | } |
0720313b |
1374 | |
df28d6c5 |
1375 | |
900df8b6 |
1376 | /** |
1377 | * Search through course users |
2700d113 |
1378 | * If used for the site course searches through all undeleted, confirmed users |
900df8b6 |
1379 | * |
1380 | * @param type description |
7cf1c7bd |
1381 | * @todo Finish documenting this function |
900df8b6 |
1382 | */ |
1383 | function search_users($courseid, $groupid, $searchtext, $sort='', $exceptions='') { |
1384 | global $CFG; |
0720313b |
1385 | |
900df8b6 |
1386 | switch ($CFG->dbtype) { |
d4419d55 |
1387 | case 'mysql': |
1388 | $fullname = ' CONCAT(u.firstname," ",u.lastname) '; |
1389 | $LIKE = 'LIKE'; |
900df8b6 |
1390 | break; |
d4419d55 |
1391 | case 'postgres7': |
900df8b6 |
1392 | $fullname = " u.firstname||' '||u.lastname "; |
d4419d55 |
1393 | $LIKE = 'ILIKE'; |
900df8b6 |
1394 | break; |
8f0cd6ef |
1395 | default: |
d4419d55 |
1396 | $fullname = ' u.firstname||" "||u.lastname '; |
1397 | $LIKE = 'ILIKE'; |
900df8b6 |
1398 | } |
8f0cd6ef |
1399 | |
900df8b6 |
1400 | if (!empty($exceptions)) { |
d4419d55 |
1401 | $except = ' AND u.id NOT IN ('. $exceptions .') '; |
900df8b6 |
1402 | } else { |
1403 | $except = ''; |
1404 | } |
2700d113 |
1405 | |
900df8b6 |
1406 | if (!empty($sort)) { |
d4419d55 |
1407 | $order = ' ORDER BY '. $sort; |
900df8b6 |
1408 | } else { |
1409 | $order = ''; |
1410 | } |
8f0cd6ef |
1411 | |
d4419d55 |
1412 | $select = 'u.deleted = \'0\' AND u.confirmed = \'1\''; |
2700d113 |
1413 | |
222ac91b |
1414 | if (!$courseid or $courseid == SITEID) { |
2700d113 |
1415 | return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email |
1416 | FROM {$CFG->prefix}user u |
1417 | WHERE $select |
900df8b6 |
1418 | AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%') |
2700d113 |
1419 | $except $order"); |
8f0cd6ef |
1420 | } else { |
2700d113 |
1421 | |
900df8b6 |
1422 | if ($groupid) { |
1423 | return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email |
8f0cd6ef |
1424 | FROM {$CFG->prefix}user u, |
900df8b6 |
1425 | {$CFG->prefix}groups_members g |
2700d113 |
1426 | WHERE $select AND g.groupid = '$groupid' AND g.userid = u.id |
900df8b6 |
1427 | AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%') |
1428 | $except $order"); |
1429 | } else { |
1430 | if (!$teachers = get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email |
8f0cd6ef |
1431 | FROM {$CFG->prefix}user u, |
900df8b6 |
1432 | {$CFG->prefix}user_teachers s |
2700d113 |
1433 | WHERE $select AND s.course = '$courseid' AND s.userid = u.id |
900df8b6 |
1434 | AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%') |
1435 | $except $order")) { |
1436 | $teachers = array(); |
1437 | } |
1438 | if (!$students = get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email |
8f0cd6ef |
1439 | FROM {$CFG->prefix}user u, |
900df8b6 |
1440 | {$CFG->prefix}user_students s |
2700d113 |
1441 | WHERE $select AND s.course = '$courseid' AND s.userid = u.id |
900df8b6 |
1442 | AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%') |
1443 | $except $order")) { |
1444 | $students = array(); |
1445 | } |
1446 | return $teachers + $students; |
1447 | } |
1448 | } |
df28d6c5 |
1449 | } |
1450 | |
2700d113 |
1451 | |
18a97fd8 |
1452 | /** |
2700d113 |
1453 | * Returns a list of all site users |
1454 | * Obsolete, just calls get_course_users(SITEID) |
18a97fd8 |
1455 | * |
6bc502cc |
1456 | * @param type description |
7cf1c7bd |
1457 | * @todo Finish documenting this function |
18a97fd8 |
1458 | */ |
d4419d55 |
1459 | function get_site_users($sort='u.lastaccess DESC', $fields='*', $exceptions='') { |
2d0b30a0 |
1460 | |
65ee9c16 |
1461 | return get_course_users(SITEID, $sort, $exceptions, $fields); |
2d0b30a0 |
1462 | } |
1463 | |
9fa49e22 |
1464 | |
18a97fd8 |
1465 | /** |
8f0cd6ef |
1466 | * Returns a subset of users |
1467 | * |
18a97fd8 |
1468 | * longdesc |
1469 | * |
7cf1c7bd |
1470 | * @param boolean $get if false then only a count of the records is returned |
6bc502cc |
1471 | * @param string $search a simple string to search for |
1472 | * @param boolean $confirmed a switch to allow/disallow unconfirmed users |
1473 | * @param array(int) $exceptions a list of IDs to ignore, eg 2,4,5,8,9,10 |
1474 | * @param string $sort a SQL snippet for the sorting criteria to use |
1284a926 |
1475 | * @param string $firstinitial |
1476 | * @param string $lastinitial |
1477 | * @param string $page |
1478 | * @param string $recordsperpage |
1479 | * @param string $fields a comma separated list of fields |
18a97fd8 |
1480 | */ |
d4419d55 |
1481 | function get_users($get=true, $search='', $confirmed=false, $exceptions='', $sort='firstname ASC', |
1482 | $firstinitial='', $lastinitial='', $page=0, $recordsperpage=99999, $fields='*') { |
18a97fd8 |
1483 | |
1484 | global $CFG; |
1485 | |
1486 | switch ($CFG->dbtype) { |
d4419d55 |
1487 | case 'mysql': |
1488 | $limit = 'LIMIT '. $page .','. $recordsperpage; |
1489 | $fullname = ' CONCAT(firstname," ",lastname) '; |
1490 | $LIKE = 'LIKE'; |
489b2919 |
1491 | break; |
d4419d55 |
1492 | case 'postgres7': |
1493 | $limit = 'LIMIT '. $recordsperpage .' OFFSET '.($page); |
3a954ad4 |
1494 | $fullname = " firstname||' '||lastname "; |
d4419d55 |
1495 | $LIKE = 'ILIKE'; |
18a97fd8 |
1496 | break; |
8f0cd6ef |
1497 | default: |
d4419d55 |
1498 | $limit = 'LIMIT '. $recordsperpage .','. $page; |
1499 | $fullname = ' firstname||" "||lastname '; |
1500 | $LIKE = 'ILIKE'; |
18a97fd8 |
1501 | } |
e384fb7b |
1502 | |
d4419d55 |
1503 | $select = 'username <> \'guest\' AND deleted = 0'; |
488acd1b |
1504 | |
e384fb7b |
1505 | if ($search) { |
488acd1b |
1506 | $select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') "; |
e384fb7b |
1507 | } |
1508 | |
5a741655 |
1509 | if ($confirmed) { |
d4419d55 |
1510 | $select .= ' AND confirmed = \'1\' '; |
5a741655 |
1511 | } |
1512 | |
1513 | if ($exceptions) { |
d4419d55 |
1514 | $select .= ' AND id NOT IN ('. $exceptions .') '; |
5a741655 |
1515 | } |
1516 | |
488acd1b |
1517 | if ($firstinitial) { |
d4419d55 |
1518 | $select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\''; |
8f0cd6ef |
1519 | } |
488acd1b |
1520 | if ($lastinitial) { |
d4419d55 |
1521 | $select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\''; |
8f0cd6ef |
1522 | } |
488acd1b |
1523 | |
5a741655 |
1524 | if ($sort and $get) { |
d4419d55 |
1525 | $sort = ' ORDER BY '. $sort .' '; |
5a741655 |
1526 | } else { |
d4419d55 |
1527 | $sort = ''; |
5a741655 |
1528 | } |
1529 | |
1530 | if ($get) { |
d4419d55 |
1531 | return get_records_select('user', $select .' '. $sort .' '. $limit, '', $fields); |
5a741655 |
1532 | } else { |
d4419d55 |
1533 | return count_records_select('user', $select .' '. $sort .' '. $limit); |
5a741655 |
1534 | } |
9fa49e22 |
1535 | } |
1536 | |
5a741655 |
1537 | |
18a97fd8 |
1538 | /** |
1539 | * shortdesc |
8f0cd6ef |
1540 | * |
18a97fd8 |
1541 | * longdesc |
1542 | * |
6bc502cc |
1543 | * @param type description |
7cf1c7bd |
1544 | * @todo Finish documenting this function |
18a97fd8 |
1545 | */ |
d4419d55 |
1546 | function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=99999, |
7cf1c7bd |
1547 | $search='', $firstinitial='', $lastinitial='') { |
488acd1b |
1548 | |
9fa49e22 |
1549 | global $CFG; |
31fefa63 |
1550 | |
c2a96d6b |
1551 | switch ($CFG->dbtype) { |
d4419d55 |
1552 | case 'mysql': |
1553 | $limit = 'LIMIT '. $page .','. $recordsperpage; |
1554 | $fullname = ' CONCAT(firstname," ",lastname) '; |
1555 | $LIKE = 'LIKE'; |
c2a96d6b |
1556 | break; |
d4419d55 |
1557 | case 'postgres7': |
1558 | $limit = 'LIMIT '. $recordsperpage .' OFFSET '.($page); |
1559 | $fullname = ' firstname||\' \'||lastname '; |
1560 | $LIKE = 'ILIKE'; |
c2a96d6b |
1561 | break; |
8f0cd6ef |
1562 | default: |
d4419d55 |
1563 | $limit = 'LIMIT '. $recordsperpage .','. $page; |
1564 | $fullname = ' firstname||\' \'||lastname '; |
1565 | $LIKE = 'LIKE'; |
31fefa63 |
1566 | } |
c2a96d6b |
1567 | |
488acd1b |
1568 | $select = 'deleted <> 1'; |
1569 | |
c750592a |
1570 | if ($search) { |
488acd1b |
1571 | $select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') "; |
1572 | } |
1573 | |
1574 | if ($firstinitial) { |
d4419d55 |
1575 | $select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\' '; |
488acd1b |
1576 | } |
1577 | |
1578 | if ($lastinitial) { |
d4419d55 |
1579 | $select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\' '; |
c750592a |
1580 | } |
1581 | |
488acd1b |
1582 | if ($sort) { |
d4419d55 |
1583 | $sort = ' ORDER BY '. $sort .' '. $dir; |
488acd1b |
1584 | } |
1585 | |
1586 | /// warning: will return UNCONFIRMED USERS |
ea5d48ee |
1587 | return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess, confirmed |
8f0cd6ef |
1588 | FROM {$CFG->prefix}user |
488acd1b |
1589 | WHERE $select $sort $limit "); |
9fa49e22 |
1590 | |
1591 | } |
1592 | |
488acd1b |
1593 | |
18a97fd8 |
1594 | /** |
1595 | * shortdesc |
8f0cd6ef |
1596 | * |
18a97fd8 |
1597 | * longdesc |
1598 | * |
6bc502cc |
1599 | * @param type description |
7cf1c7bd |
1600 | * @todo Finish documenting this function |
18a97fd8 |
1601 | */ |
9fa49e22 |
1602 | function get_users_confirmed() { |
1603 | global $CFG; |
8f0cd6ef |
1604 | return get_records_sql("SELECT * |
1605 | FROM {$CFG->prefix}user |
1606 | WHERE confirmed = 1 |
9fa49e22 |
1607 | AND deleted = 0 |
8f0cd6ef |
1608 | AND username <> 'guest' |
9fa49e22 |
1609 | AND username <> 'changeme'"); |
1610 | } |
1611 | |
1612 | |
18a97fd8 |
1613 | /** |
1614 | * shortdesc |
8f0cd6ef |
1615 | * |
18a97fd8 |
1616 | * longdesc |
1617 | * |
6bc502cc |
1618 | * @param type description |
7cf1c7bd |
1619 | * @todo Finish documenting this function |
18a97fd8 |
1620 | */ |
99988d1a |
1621 | function get_users_unconfirmed($cutofftime=2000000000) { |
9fa49e22 |
1622 | global $CFG; |
8f0cd6ef |
1623 | return get_records_sql("SELECT * |
1624 | FROM {$CFG->prefix}user |
9fa49e22 |
1625 | WHERE confirmed = 0 |
8f0cd6ef |
1626 | AND firstaccess > 0 |
9fa49e22 |
1627 | AND firstaccess < '$cutofftime'"); |
1628 | } |
1629 | |
1630 | |
18a97fd8 |
1631 | /** |
1632 | * shortdesc |
8f0cd6ef |
1633 | * |
18a97fd8 |
1634 | * longdesc |
1635 | * |
6bc502cc |
1636 | * @param type description |
7cf1c7bd |
1637 | * @todo Finish documenting this function |
18a97fd8 |
1638 | */ |
9fa49e22 |
1639 | function get_users_longtimenosee($cutofftime) { |
1640 | global $CFG; |
937ae59c |
1641 | return get_records_sql("SELECT DISTINCT * |
1642 | FROM {$CFG->prefix}user_students |
8f0cd6ef |
1643 | WHERE timeaccess > '0' |
937ae59c |
1644 | AND timeaccess < '$cutofftime' "); |
9fa49e22 |
1645 | } |
1646 | |
f374fb10 |
1647 | /** |
1648 | * Returns an array of group objects that the user is a member of |
8f0cd6ef |
1649 | * in the given course. If userid isn't specified, then return a |
f374fb10 |
1650 | * list of all groups in the course. |
8f0cd6ef |
1651 | * |
6bc502cc |
1652 | * @param type description |
7cf1c7bd |
1653 | * @todo Finish documenting this function |
f374fb10 |
1654 | */ |
1655 | function get_groups($courseid, $userid=0) { |
1656 | global $CFG; |
1657 | |
1658 | if ($userid) { |
d4419d55 |
1659 | $dbselect = ', '. $CFG->prefix .'groups_members m'; |
1660 | $userselect = 'AND m.groupid = g.id AND m.userid = \''. $userid .'\''; |
2d439c9d |
1661 | } else { |
1662 | $dbselect = ''; |
1663 | $userselect = ''; |
f374fb10 |
1664 | } |
1665 | |
1666 | return get_records_sql("SELECT DISTINCT g.* |
2d439c9d |
1667 | FROM {$CFG->prefix}groups g $dbselect |
f374fb10 |
1668 | WHERE g.courseid = '$courseid' $userselect "); |
1669 | } |
1670 | |
1671 | |
1672 | /** |
1673 | * Returns an array of user objects |
8f0cd6ef |
1674 | * |
6bc502cc |
1675 | * @param type description |
7cf1c7bd |
1676 | * @todo Finish documenting this function |
f374fb10 |
1677 | */ |
d4419d55 |
1678 | function get_group_users($groupid, $sort='u.lastaccess DESC', $exceptions='') { |
f374fb10 |
1679 | global $CFG; |
900df8b6 |
1680 | if (!empty($exceptions)) { |
d4419d55 |
1681 | $except = ' AND u.id NOT IN ('. $exceptions .') '; |
900df8b6 |
1682 | } else { |
1683 | $except = ''; |
1684 | } |
f374fb10 |
1685 | return get_records_sql("SELECT DISTINCT u.* |
1686 | FROM {$CFG->prefix}user u, |
8f0cd6ef |
1687 | {$CFG->prefix}groups_members m |
f374fb10 |
1688 | WHERE m.groupid = '$groupid' |
900df8b6 |
1689 | AND m.userid = u.id $except |
2c4263c4 |
1690 | ORDER BY $sort"); |
f374fb10 |
1691 | } |
1692 | |
1693 | /** |
1694 | * An efficient way of finding all the users who aren't in groups yet |
8f0cd6ef |
1695 | * |
6bc502cc |
1696 | * @param type description |
7cf1c7bd |
1697 | * @todo Finish documenting this function |
f374fb10 |
1698 | */ |
1699 | function get_users_not_in_group($courseid) { |
1700 | global $CFG; |
1701 | |
1702 | return array(); /// XXX TO BE DONE |
1703 | } |
1704 | |
60b025d1 |
1705 | |
1706 | /** |
1707 | * Returns an array of user objects |
8f0cd6ef |
1708 | * |
6bc502cc |
1709 | * @param type description |
7cf1c7bd |
1710 | * @todo Finish documenting this function |
60b025d1 |
1711 | */ |
d4419d55 |
1712 | function get_group_students($groupid, $sort='u.lastaccess DESC') { |
60b025d1 |
1713 | global $CFG; |
1714 | return get_records_sql("SELECT DISTINCT u.* |
1715 | FROM {$CFG->prefix}user u, |
1716 | {$CFG->prefix}groups_members m, |
1717 | {$CFG->prefix}groups g, |
1718 | {$CFG->prefix}user_students s |
1719 | WHERE m.groupid = '$groupid' |
8f0cd6ef |
1720 | AND m.userid = u.id |
1721 | AND m.groupid = g.id |
60b025d1 |
1722 | AND g.courseid = s.course |
1723 | AND s.userid = u.id |
1724 | ORDER BY $sort"); |
1725 | } |
1726 | |
1727 | |
f374fb10 |
1728 | /** |
1729 | * Returns the user's group in a particular course |
8f0cd6ef |
1730 | * |
6bc502cc |
1731 | * @param type description |
7cf1c7bd |
1732 | * @todo Finish documenting this function |
f374fb10 |
1733 | */ |
1734 | function user_group($courseid, $userid) { |
1735 | global $CFG; |
1736 | |
1737 | return get_record_sql("SELECT g.* |
0da33e07 |
1738 | FROM {$CFG->prefix}groups g, |
1739 | {$CFG->prefix}groups_members m |
f374fb10 |
1740 | WHERE g.courseid = '$courseid' |
1741 | AND g.id = m.groupid |
1742 | AND m.userid = '$userid'"); |
1743 | } |
1744 | |
1745 | |
9fa49e22 |
1746 | |
02ebf404 |
1747 | |
1748 | /// OTHER SITE AND COURSE FUNCTIONS ///////////////////////////////////////////// |
1749 | |
1750 | |
18a97fd8 |
1751 | /** |
1752 | * Returns $course object of the top-level site. |
8f0cd6ef |
1753 | * |
18a97fd8 |
1754 | * Returns $course object of the top-level site. |
1755 | * |
6bc502cc |
1756 | * @param type description |
7cf1c7bd |
1757 | * @todo Finish documenting this function |
18a97fd8 |
1758 | */ |
02ebf404 |
1759 | function get_site () { |
02ebf404 |
1760 | |
d4419d55 |
1761 | if ( $course = get_record('course', 'category', 0)) { |
02ebf404 |
1762 | return $course; |
1763 | } else { |
1764 | return false; |
1765 | } |
1766 | } |
1767 | |
1768 | |
18a97fd8 |
1769 | /** |
1770 | * Returns list of courses, for whole site, or category |
8f0cd6ef |
1771 | * |
18a97fd8 |
1772 | * Returns list of courses, for whole site, or category |
1773 | * |
6bc502cc |
1774 | * @param type description |
7cf1c7bd |
1775 | * @todo Finish documenting this function |
18a97fd8 |
1776 | */ |
d4419d55 |
1777 | function get_courses($categoryid='all', $sort='c.sortorder ASC', $fields='c.*') { |
c7fe5c6f |
1778 | /// This function deliberately uses PHP to do the checking at the end, |
1779 | /// because MySQL has been known to really bog down when trying to do a JOIN |
1780 | /// on the 'course' and 'user_teachers' table at the same time. |
02ebf404 |
1781 | |
8ef9cb56 |
1782 | global $USER, $CFG; |
1783 | |
c7fe5c6f |
1784 | $select = array(); |
1785 | |
d4419d55 |
1786 | if ($categoryid != 'all') { |
c7fe5c6f |
1787 | $select[] = "c.category = '$categoryid'"; |
02ebf404 |
1788 | } |
1789 | |
c7fe5c6f |
1790 | /// Admins can always see invisible courses |
1791 | /// Creators can always see invisible courses |
1792 | /// Teachers can only see their own invisible courses <- needs detailed checking |
1793 | /// Students can't see invisible courses at all |
1794 | /// Guests can't see invisible courses at all |
1795 | |
d4419d55 |
1796 | $visiblecourses = ''; |
c7fe5c6f |
1797 | $showallinvisible = iscreator(); // includes admins |
1798 | $hideallinvisible = empty($USER->id) || (!isteacher()); |
1799 | |
1800 | if ($hideallinvisible) { |
1801 | $select[] = 'c.visible > 0'; |
8ef9cb56 |
1802 | } |
1803 | |
c7fe5c6f |
1804 | if ($select) { |
1805 | $selectsql = "{$CFG->prefix}course c WHERE ".implode(' AND ', $select); |
14f32609 |
1806 | } else { |
c7fe5c6f |
1807 | $selectsql = "{$CFG->prefix}course c "; |
14f32609 |
1808 | } |
1809 | |
c7fe5c6f |
1810 | $courses = get_records_sql("SELECT DISTINCT $fields FROM $selectsql ORDER BY $sort"); |
1811 | |
1812 | if ($courses and (!$hideallinvisible) and (!$showallinvisible)) { // For ordinary users we need to check visibility |
1813 | foreach ($courses as $key => $course) { |
1814 | if ($course->visible == 0) { // Invisible course, let's check if we are a teacher |
1815 | if (!isteacher($course->id)) { // We shouldn't see this |
1816 | unset($courses[$key]); |
1817 | } |
1818 | } |
1819 | } |
1820 | } |
8130b77b |
1821 | |
c7fe5c6f |
1822 | return $courses; |
8130b77b |
1823 | } |
1824 | |
1825 | |
18a97fd8 |
1826 | /** |
1827 | * Returns list of courses, for whole site, or category |
8f0cd6ef |
1828 | * |
18a97fd8 |
1829 | * Similar to get_courses, but allows paging |
1830 | * |
6bc502cc |
1831 | * @param type description |
18a97fd8 |
1832 | */ |
d4419d55 |
1833 | function get_courses_page($categoryid='all', $sort='c.sortorder ASC', $fields='c.*', |
1834 | &$totalcount, $limitfrom='', $limitnum='') { |
8130b77b |
1835 | |
c7fe5c6f |
1836 | /// This function deliberately uses PHP to do the checking at the end, |
1837 | /// because MySQL has been known to really bog down when trying to do a JOIN |
1838 | /// on the 'course' and 'user_teachers' table at the same time. |
1839 | |
8130b77b |
1840 | global $USER, $CFG; |
1841 | |
c7fe5c6f |
1842 | $select = array(); |
1843 | |
d4419d55 |
1844 | if ($categoryid != 'all') { |
c7fe5c6f |
1845 | $select[] = "c.category = '$categoryid'"; |
8130b77b |
1846 | } |
1847 | |
c7fe5c6f |
1848 | /// Admins can always see invisible courses |
1849 | /// Creators can always see invisible courses |
1850 | /// Teachers can only see their own invisible courses <- needs detailed checking |
1851 | /// Students can't see invisible courses at all |
1852 | /// Guests can't see invisible courses at all |
1853 | |
d4419d55 |
1854 | $visiblecourses = ''; |
c7fe5c6f |
1855 | $showallinvisible = iscreator(); // includes admins |
1856 | $hideallinvisible = empty($USER->id) || (!isteacher()); |
1857 | |
1858 | if ($hideallinvisible) { |
1859 | $select[] = 'c.visible > 0'; |
1860 | } |
1861 | |
1862 | if ($select) { |
1863 | $selectsql = "{$CFG->prefix}course c WHERE ".implode(' AND ', $select); |
8130b77b |
1864 | } else { |
c7fe5c6f |
1865 | $selectsql = "{$CFG->prefix}course c "; |
8130b77b |
1866 | } |
1867 | |
c7fe5c6f |
1868 | $courses = get_records_sql("SELECT DISTINCT $fields FROM $selectsql ORDER BY $sort"); |
1869 | |
1870 | if ($courses and (!$hideallinvisible) and (!$showallinvisible)) { // For ordinary users we need to check visibility |
1871 | foreach ($courses as $key => $course) { |
1872 | if ($course->visible == 0) { // Invisible course, let's check if we are a teacher |
1873 | if (!isteacher($course->id)) { // We shouldn't see this |
1874 | unset($courses[$key]); |
1875 | } |
1876 | } |
02ebf404 |
1877 | } |
1878 | } |
8ef9cb56 |
1879 | |
c7fe5c6f |
1880 | $totalcount = count($courses); |
8ef9cb56 |
1881 | |
c7fe5c6f |
1882 | if ($courses and ($limitfrom or $limitnum)) { |
1883 | $courses = array_slice($courses, (int)$limitfrom, (int)$limitnum); |
1884 | } |
8ef9cb56 |
1885 | |
c7fe5c6f |
1886 | return $courses; |
02ebf404 |
1887 | } |
1888 | |
1889 | |
18a97fd8 |
1890 | /** |
1891 | * shortdesc |
8f0cd6ef |
1892 | * |
18a97fd8 |
1893 | * longdesc |
1894 | * |
6bc502cc |
1895 | * @param type description |
7cf1c7bd |
1896 | * @todo Finish documenting this function |
18a97fd8 |
1897 | */ |
d4419d55 |
1898 | function get_my_courses($userid, $sort='visible DESC,sortorder ASC') { |
2f3499b7 |
1899 | |
02ebf404 |
1900 | global $CFG; |
1901 | |
2f3499b7 |
1902 | $course = array(); |
1903 | |
d4419d55 |
1904 | if ($students = get_records('user_students', 'userid', $userid, '', 'id, course')) { |
2f3499b7 |
1905 | foreach ($students as $student) { |
1906 | $course[$student->course] = $student->course; |
1907 | } |
1908 | } |
d4419d55 |
1909 | if ($teachers = get_records('user_teachers', 'userid', $userid, '', 'id, course')) { |
2f3499b7 |
1910 | foreach ($teachers as $teacher) { |
1911 | $course[$teacher->course] = $teacher->course; |
1912 | } |
1913 | } |
1914 | if (empty($course)) { |
1915 | return $course; |
1916 | } |
1917 | |
1918 | $courseids = implode(',', $course); |
1919 | |
d4419d55 |
1920 | return get_records_list('course', 'id', $courseids, $sort); |
2f3499b7 |
1921 | |
1922 | // The following is correct but VERY slow with large datasets |
1923 | // |
8f0cd6ef |
1924 | // return get_records_sql("SELECT c.* |
1925 | // FROM {$CFG->prefix}course c, |
1926 | // {$CFG->prefix}user_students s, |
1927 | // {$CFG->prefix}user_teachers t |
2f3499b7 |
1928 | // WHERE (s.userid = '$userid' AND s.course = c.id) |
1929 | // OR (t.userid = '$userid' AND t.course = c.id) |
8f0cd6ef |
1930 | // GROUP BY c.id |
2f3499b7 |
1931 | // ORDER BY $sort"); |
02ebf404 |
1932 | } |
1933 | |
1934 | |
18a97fd8 |
1935 | /** |
1936 | * Returns a list of courses that match a search |
8f0cd6ef |
1937 | * |
18a97fd8 |
1938 | * Returns a list of courses that match a search |
1939 | * |
6bc502cc |
1940 | * @param type description |
7cf1c7bd |
1941 | * @todo Finish documenting this function |
18a97fd8 |
1942 | */ |
d4419d55 |
1943 | function get_courses_search($searchterms, $sort='fullname ASC', $page=0, $recordsperpage=50, &$totalcount) { |
02ebf404 |
1944 | |
1945 | global $CFG; |
1946 | |
1947 | switch ($CFG->dbtype) { |
d4419d55 |
1948 | case 'mysql': |
1949 | $limit = 'LIMIT '. $page .','. $recordsperpage; |
02ebf404 |
1950 | break; |
d4419d55 |
1951 | case 'postgres7': |
1952 | $limit = 'LIMIT '. $recordsperpage .' OFFSET '.($page * $recordsperpage); |
02ebf404 |
1953 | break; |
8f0cd6ef |
1954 | default: |
d4419d55 |
1955 | $limit = 'LIMIT '. $recordsperpage .','. $page; |
02ebf404 |
1956 | } |
1957 | |
18a97fd8 |
1958 | //to allow case-insensitive search for postgesql |
d4419d55 |
1959 | if ($CFG->dbtype == 'postgres7') { |
1960 | $LIKE = 'ILIKE'; |
1961 | $NOTLIKE = 'NOT ILIKE'; // case-insensitive |
1962 | $REGEXP = '~*'; |
1963 | $NOTREGEXP = '!~*'; |
02ebf404 |
1964 | } else { |
d4419d55 |
1965 | $LIKE = 'LIKE'; |
1966 | $NOTLIKE = 'NOT LIKE'; |
1967 | $REGEXP = 'REGEXP'; |
1968 | $NOTREGEXP = 'NOT REGEXP'; |
02ebf404 |
1969 | } |
1970 | |
d4419d55 |
1971 | $fullnamesearch = ''; |
1972 | $summarysearch = ''; |
02ebf404 |
1973 | |
02ebf404 |
1974 | foreach ($searchterms as $searchterm) { |
1975 | if ($fullnamesearch) { |
d4419d55 |
1976 | $fullnamesearch .= ' AND '; |
02ebf404 |
1977 | } |
02ebf404 |
1978 | if ($summarysearch) { |
d4419d55 |
1979 | $summarysearch .= ' AND '; |
02ebf404 |
1980 | } |
a8b56716 |
1981 | |
d4419d55 |
1982 | if (substr($searchterm,0,1) == '+') { |
a8b56716 |
1983 | $searchterm = substr($searchterm,1); |
1984 | $summarysearch .= " summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' "; |
1985 | $fullnamesearch .= " fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' "; |
1986 | } else if (substr($searchterm,0,1) == "-") { |
1987 | $searchterm = substr($searchterm,1); |
1988 | $summarysearch .= " summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' "; |
1989 | $fullnamesearch .= " fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' "; |
1990 | } else { |
d4419d55 |
1991 | $summarysearch .= ' summary '. $LIKE .'\'%'. $searchterm .'%\' '; |
1992 | $fullnamesearch .= ' fullname '. $LIKE .'\'%'. $searchterm .'%\' '; |
a8b56716 |
1993 | } |
1994 | |
02ebf404 |
1995 | } |
1996 | |
d4419d55 |
1997 | $selectsql = $CFG->prefix .'course WHERE ('. $fullnamesearch .' OR '. $summarysearch .') AND category > \'0\''; |
a8b56716 |
1998 | |
d4419d55 |
1999 | $totalcount = count_records_sql('SELECT COUNT(*) FROM '. $selectsql); |
02ebf404 |
2000 | |
d4419d55 |
2001 | $courses = get_records_sql('SELECT * FROM '. $selectsql .' ORDER BY '. $sort .' '. $limit); |
02ebf404 |
2002 | |
2003 | if ($courses) { /// Remove unavailable courses from the list |
2004 | foreach ($courses as $key => $course) { |
2005 | if (!$course->visible) { |
2006 | if (!isteacher($course->id)) { |
2007 | unset($courses[$key]); |
a8b56716 |
2008 | $totalcount--; |
02ebf404 |
2009 | } |
2010 | } |
2011 | } |
2012 | } |
2013 | |
2014 | return $courses; |
2015 | } |
2016 | |
2017 | |
18a97fd8 |
2018 | /** |
2019 | * Returns a sorted list of categories |
8f0cd6ef |
2020 | * |
18a97fd8 |
2021 | * Returns a sorted list of categories |
2022 | * |
6bc502cc |
2023 | * @param type description |
7cf1c7bd |
2024 | * @todo Finish documenting this function |
18a97fd8 |
2025 | */ |
d4419d55 |
2026 | function get_categories($parent='none', $sort='sortorder ASC') { |
02ebf404 |
2027 | |
d4419d55 |
2028 | if ($parent == 'none') { |
2029 | $categories = get_records('course_categories', '', '', $sort); |
02ebf404 |
2030 | } else { |
d4419d55 |
2031 | $categories = get_records('course_categories', 'parent', $parent, $sort); |
02ebf404 |
2032 | } |
2033 | if ($categories) { /// Remove unavailable categories from the list |
3af6e1db |
2034 | $creator = iscreator(); |
02ebf404 |
2035 | foreach ($categories as $key => $category) { |
2036 | if (!$category->visible) { |
3af6e1db |
2037 | if (!$creator) { |
02ebf404 |
2038 | unset($categories[$key]); |
2039 | } |
2040 | } |
2041 | } |
2042 | } |
2043 | return $categories; |
2044 | } |
2045 | |
2046 | |
18a97fd8 |
2047 | /** |
6bc502cc |
2048 | * This recursive function makes sure that the courseorder is consecutive |
18a97fd8 |
2049 | * |
6bc502cc |
2050 | * @param type description |
7cf1c7bd |
2051 | * @todo Finish documenting this function |
18a97fd8 |
2052 | */ |
6bc502cc |
2053 | function fix_course_sortorder($categoryid=0, $n=0) { |
8f0cd6ef |
2054 | |
02ebf404 |
2055 | $count = 0; |
6bc502cc |
2056 | if ($courses = get_courses($categoryid)) { |
2057 | foreach ($courses as $course) { |
2058 | set_field('course', 'sortorder', $n, 'id', $course->id); |
2059 | $n++; |
2060 | $count++; |
02ebf404 |
2061 | } |
02ebf404 |
2062 | } |
d4419d55 |
2063 | set_field('course_categories', 'coursecount', $count, 'id', $categoryid); |
8f0cd6ef |
2064 | |
6bc502cc |
2065 | if ($categories = get_categories($categoryid)) { |
2066 | foreach ($categories as $category) { |
2067 | $n = fix_course_sortorder($category->id, $n); |
2068 | } |
2069 | } |
8f0cd6ef |
2070 | |
6bc502cc |
2071 | return $n; |
02ebf404 |
2072 | } |
2073 | |
18a97fd8 |
2074 | /** |
2075 | * This function creates a default separated/connected scale |
8f0cd6ef |
2076 | * |
18a97fd8 |
2077 | * This function creates a default separated/connected scale |
8f0cd6ef |
2078 | * so there's something in the database. The locations of |
2079 | * strings and files is a bit odd, but this is because we |
18a97fd8 |
2080 | * need to maintain backward compatibility with many different |
2081 | * existing language translations and older sites. |
2082 | * |
6bc502cc |
2083 | * @param type description |
7cf1c7bd |
2084 | * @todo Finish documenting this function |
18a97fd8 |
2085 | */ |
02ebf404 |
2086 | function make_default_scale() { |
02ebf404 |
2087 | |
2088 | global $CFG; |
2089 | |
2090 | $defaultscale = NULL; |
2091 | $defaultscale->courseid = 0; |
2092 | $defaultscale->userid = 0; |
d4419d55 |
2093 | $defaultscale->name = get_string('separateandconnected'); |
2094 | $defaultscale->scale = get_string('postrating1', 'forum').','. |
2095 | get_string('postrating2', 'forum').','. |
2096 | get_string('postrating3', 'forum'); |
02ebf404 |
2097 | $defaultscale->timemodified = time(); |
2098 | |
8f0cd6ef |
2099 | /// Read in the big description from the file. Note this is not |
02ebf404 |
2100 | /// HTML (despite the file extension) but Moodle format text. |
d4419d55 |
2101 | $parentlang = get_string('parentlang'); |
2102 | if (is_readable($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) { |
2103 | $file = file($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html'); |
2104 | } else if ($parentlang and is_readable($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) { |
2105 | $file = file($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html'); |
2106 | } else if (is_readable($CFG->dirroot .'/lang/en/help/forum/ratings.html')) { |
2107 | $file = file($CFG->dirroot .'/lang/en/help/forum/ratings.html'); |
02ebf404 |
2108 | } else { |
d4419d55 |
2109 | $file = ''; |
02ebf404 |
2110 | } |
2111 | |
d4419d55 |
2112 | $defaultscale->description = addslashes(implode('', $file)); |
02ebf404 |
2113 | |
d4419d55 |
2114 | if ($defaultscale->id = insert_record('scale', $defaultscale)) { |
2115 | execute_sql('UPDATE '. $CFG->prefix .'forum SET scale = \''. $defaultscale->id .'\'', false); |
02ebf404 |
2116 | } |
2117 | } |
2118 | |
18a97fd8 |
2119 | /** |
2120 | * Returns a menu of all available scales from the site as well as the given course |
8f0cd6ef |
2121 | * |
18a97fd8 |
2122 | * Returns a menu of all available scales from the site as well as the given course |
2123 | * |
6bc502cc |
2124 | * @param type description |
7cf1c7bd |
2125 | * @todo Finish documenting this function |
18a97fd8 |
2126 | */ |
02ebf404 |
2127 | function get_scales_menu($courseid=0) { |
02ebf404 |
2128 | |
2129 | global $CFG; |
8f0cd6ef |
2130 | |
2131 | $sql = "SELECT id, name FROM {$CFG->prefix}scale |
2132 | WHERE courseid = '0' or courseid = '$courseid' |
02ebf404 |
2133 | ORDER BY courseid ASC, name ASC"; |
2134 | |
d4419d55 |
2135 | if ($scales = get_records_sql_menu($sql)) { |
02ebf404 |
2136 | return $scales; |
2137 | } |
2138 | |
2139 | make_default_scale(); |
2140 | |
d4419d55 |
2141 | return get_records_sql_menu($sql); |
02ebf404 |
2142 | } |
2143 | |
df28d6c5 |
2144 | /// MODULE FUNCTIONS ///////////////////////////////////////////////// |
2145 | |
18a97fd8 |
2146 | /** |
2147 | * Just gets a raw list of all modules in a course |
8f0cd6ef |
2148 | * |
18a97fd8 |
2149 | * Just gets a raw list of all modules in a course |
2150 | * |
6bc502cc |
2151 | * @param type description |
7cf1c7bd |
2152 | * @todo Finish documenting this function |
18a97fd8 |
2153 | */ |
9fa49e22 |
2154 | function get_course_mods($courseid) { |
9fa49e22 |
2155 | global $CFG; |
2156 | |
7acaa63d |
2157 | return get_records_sql("SELECT cm.*, m.name as modname |
8f0cd6ef |
2158 | FROM {$CFG->prefix}modules m, |
7acaa63d |
2159 | {$CFG->prefix}course_modules cm |
8f0cd6ef |
2160 | WHERE cm.course = '$courseid' |
9fa49e22 |
2161 | AND cm.deleted = '0' |
2162 | AND cm.module = m.id "); |
2163 | } |
2164 | |
18a97fd8 |
2165 | /** |
2166 | * Given an instance of a module, finds the coursemodule description |
8f0cd6ef |
2167 | * |
18a97fd8 |
2168 | * Given an instance of a module, finds the coursemodule description |
2169 | * |
6bc502cc |
2170 | * @param type description |
7cf1c7bd |
2171 | * @todo Finish documenting this function |
18a97fd8 |
2172 | */ |
df28d6c5 |
2173 | function get_coursemodule_from_instance($modulename, $instance, $courseid) { |
df28d6c5 |
2174 | |
2175 | global $CFG; |
2176 | |
2177 | return get_record_sql("SELECT cm.*, m.name |
8f0cd6ef |
2178 | FROM {$CFG->prefix}course_modules cm, |
2179 | {$CFG->prefix}modules md, |
2180 | {$CFG->prefix}$modulename m |
2181 | WHERE cm.course = '$courseid' AND |
df28d6c5 |
2182 | cm.deleted = '0' AND |
8f0cd6ef |
2183 | cm.instance = m.id AND |
2184 | md.name = '$modulename' AND |
df28d6c5 |
2185 | md.id = cm.module AND |
2186 | m.id = '$instance'"); |
2187 | |
2188 | } |
2189 | |
18a97fd8 |
2190 | /** |
2191 | * Returns an array of all the active instances of a particular module in a given course, sorted in the order they are defined |
8f0cd6ef |
2192 | * |
18a97fd8 |
2193 | * Returns an array of all the active instances of a particular |
2194 | * module in a given course, sorted in the order they are defined |
2195 | * in the course. Returns false on any errors. |
2196 | * |
6bc502cc |
2197 | * @param string $modulename the name of the module to get instances for |
2198 | * @param object(course) $course this depends on an accurate $course->modinfo |
18a97fd8 |
2199 | */ |
cccb016a |
2200 | function get_all_instances_in_course($modulename, $course) { |
df28d6c5 |
2201 | |
2202 | global $CFG; |
2203 | |
cccb016a |
2204 | if (!$modinfo = unserialize($course->modinfo)) { |
2205 | return array(); |
1acfbce5 |
2206 | } |
2207 | |
404afe6b |
2208 | if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode |
8f0cd6ef |
2209 | FROM {$CFG->prefix}course_modules cm, |
2210 | {$CFG->prefix}course_sections cw, |
2211 | {$CFG->prefix}modules md, |
2212 | {$CFG->prefix}$modulename m |
2213 | WHERE cm.course = '$course->id' AND |
2214 | cm.instance = m.id AND |
df28d6c5 |
2215 | cm.deleted = '0' AND |
8f0cd6ef |
2216 | cm.section = cw.id AND |
2217 | md.name = '$modulename' AND |
cccb016a |
2218 | md.id = cm.module")) { |
2219 | return array(); |
2220 | } |
2221 | |
2222 | // Hide non-visible instances from students |
2223 | if (isteacher($course->id)) { |
2224 | $invisible = -1; |
2225 | } else { |
2226 | $invisible = 0; |
2227 | } |
2228 | |
2229 | foreach ($modinfo as $mod) { |
2230 | if ($mod->mod == $modulename and $mod->visible > $invisible) { |
7f12f9cd |
2231 | $instance = $rawmods[$mod->cm]; |
2232 | if (!empty($mod->extra)) { |
2233 | $instance->extra = $mod->extra; |
2234 | } |
2235 | $outputarray[] = $instance; |
cccb016a |
2236 | } |
2237 | } |
2238 | |
2239 | return $outputarray; |
df28d6c5 |
2240 | |
2241 | } |
2242 | |
9fa49e22 |
2243 | |
18a97fd8 |
2244 | /** |
2245 | * determine whether a module instance is visible within a course |
8f0cd6ef |
2246 | * |
2247 | * Given a valid module object with info about the id and course, |
2248 | * and the module's type (eg "forum") returns whether the object |
18a97fd8 |
2249 | * is visible or not |
2250 | * |
6bc502cc |
2251 | * @param type description |
7cf1c7bd |
2252 | * @todo Finish documenting this function |
18a97fd8 |
2253 | */ |
580f2fbc |
2254 | function instance_is_visible($moduletype, $module) { |
580f2fbc |
2255 | |
2256 | global $CFG; |
2257 | |
86e6076b |
2258 | if ($records = get_records_sql("SELECT cm.instance, cm.visible |
580f2fbc |
2259 | FROM {$CFG->prefix}course_modules cm, |
580f2fbc |
2260 | {$CFG->prefix}modules m |
2261 | WHERE cm.course = '$module->course' AND |
8f0cd6ef |
2262 | cm.module = m.id AND |
2263 | m.name = '$moduletype' AND |
2264 | cm.instance = '$module->id'")) { |
580f2fbc |
2265 | |
2266 | foreach ($records as $record) { // there should only be one - use the first one |
2267 | return $record->visible; |
2268 | } |
2269 | } |
2270 | |
2271 | return true; // visible by default! |
2272 | } |
2273 | |
a3fb1c45 |
2274 | |
2275 | |
2276 | |
9fa49e22 |
2277 | /// LOG FUNCTIONS ///////////////////////////////////////////////////// |
2278 | |
2279 | |
18a97fd8 |
2280 | /** |
2281 | * Add an entry to the log table. |
8f0cd6ef |
2282 | * |
18a97fd8 |
2283 | * Add an entry to the log table. These are "action" focussed rather |
8f0cd6ef |
2284 | * than web server hits, and provide a way to easily reconstruct what |
18a97fd8 |
2285 | * any particular student has been doing. |
2286 | * |
6bc502cc |
2287 | * @param int $course the course id |
2288 | * @param string $module the module name - e.g. forum, journal, resource, course, user etc |
2289 | * @param string $action view, edit, post (often but not always the same as the file.php) |
2290 | * @param string $url the file and parameters used to see the results of the action |
8f0cd6ef |
2291 | * @param string $info additional description information |
6bc502cc |
2292 | * @param string $cm the course_module->id if there is one |
2293 | * @param string $user if log regards $user other than $USER |
18a97fd8 |
2294 | */ |
d4419d55 |
2295 | function add_to_log($courseid, $module, $action, $url='', $info='', $cm=0, $user=0) { |
9fa49e22 |
2296 | |
31fefa63 |
2297 | global $db, $CFG, $USER, $REMOTE_ADDR; |
9fa49e22 |
2298 | |
3d94772d |
2299 | if ($user) { |
2300 | $userid = $user; |
2301 | } else { |
2302 | if (isset($USER->realuser)) { // Don't log |
2303 | return; |
2304 | } |
d4419d55 |
2305 | $userid = empty($USER->id) ? '0' : $USER->id; |
9fa49e22 |
2306 | } |
2307 | |
2308 | $timenow = time(); |
2309 | $info = addslashes($info); |
8f0cd6ef |
2310 | $url = html_entity_decode($url); // for php < 4.3.0 this is defined in moodlelib.php |
9fa49e22 |
2311 | |
d4419d55 |
2312 | $result = $db->Execute('INSERT INTO '. $CFG->prefix .'log (time, userid, course, ip, module, cmid, action, url, info) |
2313 | VALUES (' . "'$timenow', '$userid', '$courseid', '$REMOTE_ADDR', '$module', '$cm', '$action', '$url', '$info')"); |
ebc3bd2b |
2314 | |
ce78926d |
2315 | if (!$result and ($CFG->debug > 7)) { |
d4419d55 |
2316 | echo '<p>Error: Could not insert a new entry to the Moodle log</p>'; // Don't throw an error |
8f0cd6ef |
2317 | } |
540995b8 |
2318 | if (!$user and isset($USER->id)) { |
222ac91b |
2319 | if ($courseid == SITEID) { |
d4419d55 |
2320 | $db->Execute('UPDATE '. $CFG->prefix .'user SET lastIP=\''. $REMOTE_ADDR .'\', lastaccess=\''. $timenow .'\' |
2321 | WHERE id = \''. $USER->id .'\' '); |
540995b8 |
2322 | } else if (isstudent($courseid)) { |
d4419d55 |
2323 | $db->Execute('UPDATE '. $CFG->prefix .'user_students SET timeaccess = \''. $timenow .'\' '. |
2324 | 'WHERE course = \''. $courseid .'\' AND userid = \''. $userid .'\''); |
540995b8 |
2325 | } else if (isteacher($courseid, false, false)) { |
d4419d55 |
2326 | $db->Execute('UPDATE '. $CFG->prefix .'user_teachers SET timeaccess = \''. $timenow .'\' '. |
2327 | 'WHERE course = \''. $courseid .'\' AND userid = \''. $userid .'\''); |
3d94772d |
2328 | } |
8f0cd6ef |
2329 | } |
9fa49e22 |
2330 | } |
2331 | |
2332 | |
18a97fd8 |
2333 | /** |
2334 | * select all log records based on SQL criteria |
8f0cd6ef |
2335 | * |
18a97fd8 |
2336 | * select all log records based on SQL criteria |
8f0cd6ef |
2337 | * |
6bc502cc |
2338 | * @param string $select SQL select criteria |
2339 | * @param string $order SQL order by clause to sort the records returned |
18a97fd8 |
2340 | */ |
d4419d55 |
2341 | function get_logs($select, $order='l.time DESC', $limitfrom='', $limitnum='', &$totalcount) { |
9fa49e22 |
2342 | global $CFG; |
2343 | |
d4419d55 |
2344 | if ($limitfrom !== '') { |
519d369f |
2345 | switch ($CFG->dbtype) { |
d4419d55 |
2346 | case 'mysql': |
2347 | $limit = 'LIMIT '. $limitfrom .','. $limitnum; |
519d369f |
2348 | break; |
d4419d55 |
2349 | case 'postgres7': |
2350 | $limit = 'LIMIT '. $limitnum .' OFFSET '. $limitfrom; |
519d369f |
2351 | break; |
8f0cd6ef |
2352 | default: |
d4419d55 |
2353 | $limit = 'LIMIT '. $limitnum .','. $limitfrom; |
519d369f |
2354 | } |
2355 | } else { |
d4419d55 |
2356 | $limit = ''; |
519d369f |
2357 | } |
2358 | |
2359 | if ($order) { |
d4419d55 |
2360 | $order = 'ORDER BY '. $order; |
519d369f |
2361 | } |
2362 | |
b4bac9b6 |
2363 | $selectsql = "{$CFG->prefix}log l LEFT JOIN {$CFG->prefix}user u ON l.userid = u.id ".((strlen($select) > 0) ? "WHERE $select" : ""); |
d4419d55 |
2364 | $totalcount = count_records_sql('SELECT COUNT(*) FROM '. $selectsql); |
519d369f |
2365 | |
d4419d55 |
2366 | return get_records_sql('SELECT l.*, u.firstname, u.lastname, u.picture |
2367 | FROM '. $selectsql .' '. $order .' '. $limit); |
9fa49e22 |
2368 | } |
2369 | |
519d369f |
2370 | |
18a97fd8 |
2371 | /** |
2372 | * select all log records for a given course and user |
8f0cd6ef |
2373 | * |
18a97fd8 |
2374 | * select all log records for a given course and user |
2375 | * |
6bc502cc |
2376 | * @param type description |
7cf1c7bd |
2377 | * @todo Finish documenting this function |
18a97fd8 |
2378 | */ |
9fa49e22 |
2379 | function get_logs_usercourse($userid, $courseid, $coursestart) { |
2380 | global $CFG; |
2381 | |
da0c90c3 |
2382 | if ($courseid) { |
d4419d55 |
2383 | $courseselect = ' AND course = \''. $courseid .'\' '; |
2700d113 |
2384 | } else { |
2385 | $courseselect = ''; |
da0c90c3 |
2386 | } |
2387 | |
8f0cd6ef |
2388 | return get_records_sql("SELECT floor((`time` - $coursestart)/86400) as day, count(*) as num |
2389 | FROM {$CFG->prefix}log |
2390 | WHERE userid = '$userid' |
da0c90c3 |
2391 | AND `time` > '$coursestart' $courseselect |
9fa49e22 |
2392 | GROUP BY day "); |
2393 | } |
2394 | |
18a97fd8 |
2395 | /** |
2396 | * select all log records for a given course, user, and day |
8f0cd6ef |
2397 | * |
18a97fd8 |
2398 | * select all log records for a given course, user, and day |
2399 | * |
6bc502cc |
2400 | * @param type description |
7cf1c7bd |
2401 | * @todo Finish documenting this function |
18a97fd8 |
2402 | */ |
9fa49e22 |
2403 | function get_logs_userday($userid, $courseid, $daystart) { |
2404 | global $CFG; |
2405 | |
7e4a6488 |
2406 | if ($courseid) { |
d4419d55 |
2407 | $courseselect = ' AND course = \''. $courseid .'\' '; |
2700d113 |
2408 | } else { |
2409 | $courseselect = ''; |
7e4a6488 |
2410 | } |
2411 | |
9fa49e22 |
2412 | return get_records_sql("SELECT floor((`time` - $daystart)/3600) as hour, count(*) as num |
2413 | FROM {$CFG->prefix}log |
8f0cd6ef |
2414 | WHERE userid = '$userid' |
7e4a6488 |
2415 | AND `time` > '$daystart' $courseselect |
9fa49e22 |
2416 | GROUP BY hour "); |
2417 | } |
2418 | |
b4bac9b6 |
2419 | /** |
2420 | * Returns an object with counts of failed login attempts |
2421 | * |
8f0cd6ef |
2422 | * Returns information about failed login attempts. If the current user is |
2423 | * an admin, then two numbers are returned: the number of attempts and the |
b4bac9b6 |
2424 | * number of accounts. For non-admins, only the attempts on the given user |
2425 | * are shown. |
2426 | * |
2427 | * @param mode - admin, teacher or everybody |
2428 | * @param username - the username we are searching for |
2429 | * @param lastlogin - the date from which we are searching |
2430 | */ |
2431 | |
2432 | function count_login_failures($mode, $username, $lastlogin) { |
2433 | |
d4419d55 |
2434 | $select = 'module=\'login\' AND action=\'error\' AND time > '. $lastlogin; |
b4bac9b6 |
2435 | |
2436 | if (isadmin()) { // Return information about all accounts |
2437 | if ($count->attempts = count_records_select('log', $select)) { |
2438 | $count->accounts = count_records_select('log', $select, 'COUNT(DISTINCT info)'); |
2439 | return $count; |
2440 | } |
2441 | } else if ($mode == 'everybody' or ($mode == 'teacher' and isteacher())) { |
d4419d55 |
2442 | if ($count->attempts = count_records_select('log', $select .' AND info = \''. $username .'\'')) { |
b4bac9b6 |
2443 | return $count; |
2444 | } |
2445 | } |
2446 | return NULL; |
2447 | } |
2448 | |
2449 | |
a3fb1c45 |
2450 | /// GENERAL HELPFUL THINGS /////////////////////////////////// |
2451 | |
18a97fd8 |
2452 | /** |
2453 | * dump a given object's information in a PRE block |
8f0cd6ef |
2454 | * |
18a97fd8 |
2455 | * dump a given object's information in a PRE block |
2456 | * Mostly just for debugging |
2457 | * |
6bc502cc |
2458 | * @param type description |
7cf1c7bd |
2459 | * @todo Finish documenting this function |
18a97fd8 |
2460 | */ |
a3fb1c45 |
2461 | function print_object($object) { |
a3fb1c45 |
2462 | |
d4419d55 |
2463 | echo '<pre>'; |
2b051f1c |
2464 | print_r($object); |
d4419d55 |
2465 | echo '</pre>'; |
a3fb1c45 |
2466 | } |
2467 | |
2468 | |
9fa49e22 |
2469 | |
9d5b689c |
2470 | // vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140: |
df28d6c5 |
2471 | ?> |