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