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