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