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