df28d6c5 |
1 | <?PHP // $Id$ |
2 | |
3 | /// FUNCTIONS FOR DATABASE HANDLING //////////////////////////////// |
4 | |
5 | function execute_sql($command, $feedback=true) { |
6 | /// Completely general function - it just runs some SQL and reports success. |
7 | |
8 | global $db; |
9 | |
10 | $result = $db->Execute("$command"); |
11 | |
12 | if ($result) { |
13 | if ($feedback) { |
14 | echo "<P><FONT COLOR=green><B>".get_string("success")."</B></FONT></P>"; |
15 | } |
16 | return true; |
17 | } else { |
18 | if ($feedback) { |
19 | echo "<P><FONT COLOR=red><B>".get_string("error")."</B></FONT></P>"; |
20 | } |
21 | return false; |
22 | } |
23 | } |
24 | |
2b051f1c |
25 | function modify_database($sqlfile="", $sqlstring="") { |
26 | /// Assumes that the input text (file or string consists of |
27 | /// a number of SQL statements ENDING WITH SEMICOLONS. The |
28 | /// semicolons MUST be the last character in a line. |
df28d6c5 |
29 | /// Lines that are blank or that start with "#" are ignored. |
30 | /// Only tested with mysql dump files (mysqldump -p -d moodle) |
31 | |
32 | global $CFG; |
33 | |
2b051f1c |
34 | $success = true; // Let's be optimistic :-) |
35 | |
36 | if (!empty($sqlfile)) { |
37 | if (!is_readable($sqlfile)) { |
38 | $success = false; |
39 | echo "<P>Tried to modify database, but \"$sqlfile\" doesn't exist!</P>"; |
40 | return $success; |
41 | } else { |
42 | $lines = file($sqlfile); |
43 | } |
44 | } else { |
45 | $lines[] = $sqlstring; |
46 | } |
47 | |
48 | $command = ""; |
49 | |
50 | foreach ($lines as $line) { |
51 | $line = rtrim($line); |
52 | $length = strlen($line); |
53 | |
54 | if ($length and $line[0] <> "#") { |
55 | if (substr($line, $length-1, 1) == ";") { |
56 | $line = substr($line, 0, $length-1); // strip ; |
57 | $command .= $line; |
58 | $command = str_replace("prefix_", $CFG->prefix, $command); // Table prefixes |
59 | if (! execute_sql($command)) { |
60 | $success = false; |
df28d6c5 |
61 | } |
2b051f1c |
62 | $command = ""; |
63 | } else { |
64 | $command .= $line; |
df28d6c5 |
65 | } |
66 | } |
df28d6c5 |
67 | } |
68 | |
69 | return $success; |
2b051f1c |
70 | |
df28d6c5 |
71 | } |
72 | |
a3fb1c45 |
73 | /// FUNCTIONS TO MODIFY TABLES //////////////////////////////////////////// |
74 | |
92230499 |
75 | function table_column($table, $oldfield, $field, $type="integer", $size="10", |
76 | $signed="unsigned", $default="0", $null="not null", $after="") { |
a3fb1c45 |
77 | /// Add a new field to a table, or modify an existing one (if oldfield is defined). |
8a230a7d |
78 | global $CFG, $db; |
a3fb1c45 |
79 | |
80 | switch (strtolower($CFG->dbtype)) { |
81 | |
82 | case "mysql": |
83 | case "mysqlt": |
84 | |
85 | switch (strtolower($type)) { |
c2cb4545 |
86 | case "text": |
87 | $type = "TEXT"; |
4e56c82d |
88 | $signed = ""; |
c2cb4545 |
89 | break; |
a3fb1c45 |
90 | case "integer": |
92230499 |
91 | $type = "INTEGER($size)"; |
a3fb1c45 |
92 | break; |
92230499 |
93 | case "varchar": |
94 | $type = "VARCHAR($size)"; |
4e56c82d |
95 | $signed = ""; |
a3fb1c45 |
96 | break; |
97 | } |
98 | |
99 | if (!empty($oldfield)) { |
100 | $operation = "CHANGE $oldfield $field"; |
101 | } else { |
102 | $operation = "ADD $field"; |
103 | } |
104 | |
105 | $default = "DEFAULT '$default'"; |
106 | |
107 | if (!empty($after)) { |
31ce4b53 |
108 | $after = "AFTER `$after`"; |
a3fb1c45 |
109 | } |
110 | |
111 | execute_sql("ALTER TABLE {$CFG->prefix}$table $operation $type $signed $default $null $after"); |
112 | break; |
113 | |
5a4d292b |
114 | case "postgres7": // From Petri Asikainen |
8a230a7d |
115 | //Check db-version |
116 | $dbinfo = $db->ServerInfo(); |
117 | $dbver = substr($dbinfo[version],0,3); |
118 | |
119 | $field = "$field"; |
5a4d292b |
120 | //to prevent conflicts with reserved words |
5a4d292b |
121 | $oldfield = "\"$oldfield\""; |
122 | |
123 | switch (strtolower($type)) { |
124 | case "integer": |
125 | if ($size <= 2) { |
126 | $type = "INT2"; |
127 | } |
128 | if ($size <= 4) { |
129 | $type = "INT"; |
130 | } |
131 | if ($size > 4) { |
132 | $type = "INT8"; |
133 | } |
134 | break; |
135 | case "varchar": |
136 | $type = "VARCHAR($size)"; |
137 | break; |
138 | } |
139 | |
8a230a7d |
140 | $default = "'$default'"; |
5a4d292b |
141 | |
142 | //After is not implemented in postgesql |
143 | //if (!empty($after)) { |
144 | // $after = "AFTER '$after'"; |
145 | //} |
146 | |
cefc7e81 |
147 | if ($oldfield != "\"\"") { |
5a4d292b |
148 | execute_sql("ALTER TABLE {$CFG->prefix}$table RENAME COLUMN $oldfield TO $field"); |
149 | } else { |
150 | execute_sql("ALTER TABLE {$CFG->prefix}$table ADD COLUMN $field $type"); |
151 | } |
152 | |
8a230a7d |
153 | if ($dbver >= "7.3") { |
154 | // modifying 'not null' is posible before 7.3 |
155 | //update default values to table |
156 | if ($null == "NOT NULL") { |
157 | execute_sql("UPDATE {$CFG->prefix}$table SET $field=$default where $field IS NULL"); |
158 | execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $null"); |
159 | } else { |
160 | execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field DROP NOT NULL"); |
161 | } |
5a4d292b |
162 | } |
8a230a7d |
163 | |
164 | execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET DEFAULT $default"); |
5a4d292b |
165 | |
166 | break; |
a3fb1c45 |
167 | |
168 | default: |
169 | switch (strtolower($type)) { |
a3fb1c45 |
170 | case "integer": |
a3fb1c45 |
171 | $type = "INTEGER"; |
172 | break; |
92230499 |
173 | case "varchar": |
174 | $type = "VARCHAR"; |
175 | break; |
a3fb1c45 |
176 | } |
177 | |
178 | $default = "DEFAULT '$default'"; |
179 | |
180 | if (!empty($after)) { |
31ce4b53 |
181 | $after = "AFTER $after"; |
a3fb1c45 |
182 | } |
183 | |
184 | if (!empty($oldfield)) { |
185 | execute_sql("ALTER TABLE {$CFG->prefix}$table RENAME COLUMN $oldfield $field"); |
186 | } else { |
187 | execute_sql("ALTER TABLE {$CFG->prefix}$table ADD COLUMN $field $type"); |
188 | } |
189 | |
190 | execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $null"); |
191 | execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $default"); |
192 | break; |
193 | |
194 | } |
195 | } |
196 | |
197 | |
198 | |
199 | /// GENERIC FUNCTIONS TO CHECK AND COUNT RECORDS //////////////////////////////////////// |
df28d6c5 |
200 | |
5c63e0c4 |
201 | function record_exists($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") { |
df28d6c5 |
202 | /// Returns true or false depending on whether the specified record exists |
203 | |
204 | global $CFG; |
205 | |
5c63e0c4 |
206 | if ($field1) { |
207 | $select = "WHERE $field1 = '$value1'"; |
9fa49e22 |
208 | if ($field2) { |
df28d6c5 |
209 | $select .= " AND $field2 = '$value2'"; |
9fa49e22 |
210 | if ($field3) { |
df28d6c5 |
211 | $select .= " AND $field3 = '$value3'"; |
212 | } |
213 | } |
5c63e0c4 |
214 | } else { |
215 | $select = ""; |
df28d6c5 |
216 | } |
217 | |
218 | return record_exists_sql("SELECT * FROM $CFG->prefix$table $select LIMIT 1"); |
219 | } |
220 | |
221 | |
222 | function record_exists_sql($sql) { |
223 | /// Returns true or false depending on whether the specified record exists |
224 | /// The sql statement is provided as a string. |
225 | |
226 | global $db; |
227 | |
228 | $rs = $db->Execute($sql); |
e53b0823 |
229 | if (empty($rs)) return false; |
df28d6c5 |
230 | |
231 | if ( $rs->RecordCount() ) { |
232 | return true; |
233 | } else { |
234 | return false; |
235 | } |
236 | } |
237 | |
238 | |
5c63e0c4 |
239 | function count_records($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") { |
df28d6c5 |
240 | /// Get all the records and count them |
241 | |
242 | global $CFG; |
243 | |
5c63e0c4 |
244 | if ($field1) { |
245 | $select = "WHERE $field1 = '$value1'"; |
9fa49e22 |
246 | if ($field2) { |
df28d6c5 |
247 | $select .= " AND $field2 = '$value2'"; |
9fa49e22 |
248 | if ($field3) { |
df28d6c5 |
249 | $select .= " AND $field3 = '$value3'"; |
250 | } |
251 | } |
5c63e0c4 |
252 | } else { |
253 | $select = ""; |
df28d6c5 |
254 | } |
255 | |
256 | return count_records_sql("SELECT COUNT(*) FROM $CFG->prefix$table $select"); |
257 | } |
258 | |
9fa49e22 |
259 | function count_records_select($table, $select="") { |
260 | /// Get all the records and count them |
261 | |
262 | global $CFG; |
263 | |
d26d7ed0 |
264 | if ($select) { |
265 | $select = "WHERE $select"; |
266 | } |
267 | |
9fa49e22 |
268 | return count_records_sql("SELECT COUNT(*) FROM $CFG->prefix$table $select"); |
269 | } |
270 | |
271 | |
df28d6c5 |
272 | function count_records_sql($sql) { |
273 | /// Get all the records and count them |
274 | /// The sql statement is provided as a string. |
275 | |
276 | global $db; |
277 | |
278 | $rs = $db->Execute("$sql"); |
e53b0823 |
279 | if (empty($rs)) return 0; |
df28d6c5 |
280 | |
281 | return $rs->fields[0]; |
282 | } |
283 | |
a3fb1c45 |
284 | |
285 | |
286 | |
287 | /// GENERIC FUNCTIONS TO GET, INSERT, OR UPDATE DATA /////////////////////////////////// |
288 | |
5c63e0c4 |
289 | function get_record($table, $field1, $value1, $field2="", $value2="", $field3="", $value3="") { |
df28d6c5 |
290 | /// Get a single record as an object |
291 | |
292 | global $CFG; |
293 | |
5c63e0c4 |
294 | $select = "WHERE $field1 = '$value1'"; |
df28d6c5 |
295 | |
9fa49e22 |
296 | if ($field2) { |
df28d6c5 |
297 | $select .= " AND $field2 = '$value2'"; |
9fa49e22 |
298 | if ($field3) { |
df28d6c5 |
299 | $select .= " AND $field3 = '$value3'"; |
300 | } |
301 | } |
302 | |
303 | return get_record_sql("SELECT * FROM $CFG->prefix$table $select"); |
304 | } |
305 | |
306 | function get_record_sql($sql) { |
307 | /// Get a single record as an object |
308 | /// The sql statement is provided as a string. |
4d7a3735 |
309 | /// A LIMIT is normally added to only look for 1 record |
df28d6c5 |
310 | |
4d7a3735 |
311 | global $db, $CFG; |
df28d6c5 |
312 | |
4d7a3735 |
313 | if ($CFG->debug > 7) { // Debugging mode - don't use limit |
314 | $limit = ""; |
315 | } else { |
316 | $limit = " LIMIT 1"; // Workaround - limit to one record |
317 | } |
318 | |
7618a8eb |
319 | if (!$rs = $db->Execute("$sql$limit")) { |
320 | if ($CFG->debug > 7) { // Debugging mode - print checks |
321 | $db->debug=true; |
322 | $db->Execute("$sql$limit"); |
323 | $db->debug=false; |
324 | } |
325 | return false; |
326 | } |
4d7a3735 |
327 | |
7618a8eb |
328 | if (!$recordcount = $rs->RecordCount()) { |
329 | return false; // Found no records |
4d7a3735 |
330 | } |
df28d6c5 |
331 | |
7618a8eb |
332 | if ($recordcount == 1) { // Found one record |
df28d6c5 |
333 | return (object)$rs->fields; |
4d7a3735 |
334 | |
7618a8eb |
335 | } else { // Error: found more than one record |
336 | notify("Error: Turn off debugging to hide this error."); |
337 | notify("$sql$limit"); |
4d7a3735 |
338 | if ($records = $rs->GetAssoc(true)) { |
7618a8eb |
339 | notify("Found more than one record in get_record_sql !"); |
4d7a3735 |
340 | print_object($records); |
4d7a3735 |
341 | } else { |
7618a8eb |
342 | notify("Very strange error in get_record_sql !"); |
343 | print_object($rs); |
4d7a3735 |
344 | } |
7618a8eb |
345 | print_continue("$CFG->wwwroot/admin/config.php"); |
df28d6c5 |
346 | } |
347 | } |
348 | |
18496c59 |
349 | function get_record_select($table, $select="", $fields="*") { |
350 | /// Gets one record from a table, as an object |
351 | /// "select" is a fragment of SQL to define the selection criteria |
352 | |
353 | global $CFG; |
354 | |
355 | if ($select) { |
356 | $select = "WHERE $select"; |
357 | } |
358 | |
359 | return get_record_sql("SELECT $fields FROM $CFG->prefix$table $select"); |
360 | } |
361 | |
362 | |
0eeac484 |
363 | function get_records($table, $field="", $value="", $sort="", $fields="*", $limitfrom="", $limitnum="") { |
df28d6c5 |
364 | /// Get a number of records as an array of objects |
365 | /// Can optionally be sorted eg "time ASC" or "time DESC" |
366 | /// If "fields" is specified, only those fields are returned |
367 | /// The "key" is the first column returned, eg usually "id" |
0eeac484 |
368 | /// limitfrom and limitnum must both be specified or not at all |
df28d6c5 |
369 | |
370 | global $CFG; |
371 | |
9fa49e22 |
372 | if ($field) { |
df28d6c5 |
373 | $select = "WHERE $field = '$value'"; |
5c63e0c4 |
374 | } else { |
375 | $select = ""; |
df28d6c5 |
376 | } |
5c63e0c4 |
377 | |
74a0363f |
378 | if ($limitfrom !== "") { |
0eeac484 |
379 | switch ($CFG->dbtype) { |
380 | case "mysql": |
381 | $limit = "LIMIT $limitfrom,$limitnum"; |
382 | break; |
383 | case "postgres7": |
384 | $limit = "LIMIT $limitnum OFFSET $limitfrom"; |
385 | break; |
386 | default: |
387 | $limit = "LIMIT $limitnum,$limitfrom"; |
388 | } |
389 | } else { |
390 | $limit = ""; |
391 | } |
392 | |
df28d6c5 |
393 | if ($sort) { |
5c63e0c4 |
394 | $sort = "ORDER BY $sort"; |
df28d6c5 |
395 | } |
396 | |
0eeac484 |
397 | return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort $limit"); |
df28d6c5 |
398 | } |
399 | |
9fa49e22 |
400 | function get_records_select($table, $select="", $sort="", $fields="*") { |
401 | /// Get a number of records as an array of objects |
402 | /// Can optionally be sorted eg "time ASC" or "time DESC" |
403 | /// "select" is a fragment of SQL to define the selection criteria |
404 | /// The "key" is the first column returned, eg usually "id" |
405 | |
406 | global $CFG; |
407 | |
d26d7ed0 |
408 | if ($select) { |
409 | $select = "WHERE $select"; |
5c63e0c4 |
410 | } |
411 | |
412 | if ($sort) { |
413 | $sort = "ORDER BY $sort"; |
d26d7ed0 |
414 | } |
415 | |
5c63e0c4 |
416 | return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort"); |
9fa49e22 |
417 | } |
418 | |
df28d6c5 |
419 | |
420 | function get_records_list($table, $field="", $values="", $sort="", $fields="*") { |
9fa49e22 |
421 | /// Get a number of records as an array of objects |
422 | /// Differs from get_records() in that the values variable |
423 | /// can be a comma-separated list of values eg "4,5,6,10" |
424 | /// Can optionally be sorted eg "time ASC" or "time DESC" |
425 | /// The "key" is the first column returned, eg usually "id" |
df28d6c5 |
426 | |
427 | global $CFG; |
428 | |
9fa49e22 |
429 | if ($field) { |
df28d6c5 |
430 | $select = "WHERE $field in ($values)"; |
5c63e0c4 |
431 | } else { |
432 | $select = ""; |
df28d6c5 |
433 | } |
5c63e0c4 |
434 | |
df28d6c5 |
435 | if ($sort) { |
5c63e0c4 |
436 | $sort = "ORDER BY $sort"; |
df28d6c5 |
437 | } |
438 | |
5c63e0c4 |
439 | return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort"); |
df28d6c5 |
440 | } |
441 | |
442 | |
9fa49e22 |
443 | |
df28d6c5 |
444 | function get_records_sql($sql) { |
9fa49e22 |
445 | /// Get a number of records as an array of objects |
446 | /// The "key" is the first column returned, eg usually "id" |
447 | /// The sql statement is provided as a string. |
df28d6c5 |
448 | |
449 | global $db; |
450 | |
451 | $rs = $db->Execute("$sql"); |
e53b0823 |
452 | if (empty($rs)) return false; |
df28d6c5 |
453 | |
454 | if ( $rs->RecordCount() > 0 ) { |
455 | if ($records = $rs->GetAssoc(true)) { |
456 | foreach ($records as $key => $record) { |
457 | $objects[$key] = (object) $record; |
458 | } |
459 | return $objects; |
460 | } else { |
461 | return false; |
462 | } |
463 | } else { |
464 | return false; |
465 | } |
466 | } |
467 | |
9fa49e22 |
468 | function get_records_menu($table, $field="", $value="", $sort="", $fields="*") { |
469 | /// Get a number of records as an array of objects |
470 | /// Can optionally be sorted eg "time ASC" or "time DESC" |
471 | /// If "fields" is specified, only those fields are returned |
472 | /// The "key" is the first column returned, eg usually "id" |
473 | |
474 | global $CFG; |
475 | |
476 | if ($field) { |
477 | $select = "WHERE $field = '$value'"; |
5c63e0c4 |
478 | } else { |
479 | $select = ""; |
9fa49e22 |
480 | } |
5c63e0c4 |
481 | |
9fa49e22 |
482 | if ($sort) { |
5c63e0c4 |
483 | $sort = "ORDER BY $sort"; |
9fa49e22 |
484 | } |
485 | |
5c63e0c4 |
486 | return get_records_sql_menu("SELECT $fields FROM $CFG->prefix$table $select $sort"); |
9fa49e22 |
487 | } |
488 | |
489 | function get_records_select_menu($table, $select="", $sort="", $fields="*") { |
490 | /// Get a number of records as an array of objects |
491 | /// Can optionally be sorted eg "time ASC" or "time DESC" |
492 | /// "select" is a fragment of SQL to define the selection criteria |
493 | /// Returns associative array of first two fields |
494 | |
495 | global $CFG; |
496 | |
d26d7ed0 |
497 | if ($select) { |
498 | $select = "WHERE $select"; |
499 | } |
500 | |
5c63e0c4 |
501 | if ($sort) { |
502 | $sort = "ORDER BY $sort"; |
503 | } |
504 | |
505 | return get_records_sql_menu("SELECT $fields FROM $CFG->prefix$table $select $sort"); |
9fa49e22 |
506 | } |
507 | |
508 | |
df28d6c5 |
509 | function get_records_sql_menu($sql) { |
9fa49e22 |
510 | /// Given an SQL select, this function returns an associative |
511 | /// array of the first two columns. This is most useful in |
512 | /// combination with the choose_from_menu function to create |
513 | /// a form menu. |
df28d6c5 |
514 | |
515 | global $db; |
516 | |
517 | $rs = $db->Execute("$sql"); |
e53b0823 |
518 | if (empty($rs)) return false; |
df28d6c5 |
519 | |
520 | if ( $rs->RecordCount() > 0 ) { |
521 | while (!$rs->EOF) { |
522 | $menu[$rs->fields[0]] = $rs->fields[1]; |
523 | $rs->MoveNext(); |
524 | } |
525 | return $menu; |
526 | |
527 | } else { |
528 | return false; |
529 | } |
530 | } |
531 | |
ec2a28a6 |
532 | function get_field($table, $return, $field1, $value1, $field2="", $value2="", $field3="", $value3="") { |
df28d6c5 |
533 | /// Get a single field from a database record |
534 | |
535 | global $db, $CFG; |
536 | |
ec2a28a6 |
537 | $select = "WHERE $field1 = '$value1'"; |
538 | |
539 | if ($field2) { |
540 | $select .= " AND $field2 = '$value2'"; |
541 | if ($field3) { |
542 | $select .= " AND $field3 = '$value3'"; |
543 | } |
544 | } |
545 | |
546 | $rs = $db->Execute("SELECT $return FROM $CFG->prefix$table $select"); |
e53b0823 |
547 | if (empty($rs)) return false; |
df28d6c5 |
548 | |
549 | if ( $rs->RecordCount() == 1 ) { |
550 | return $rs->fields["$return"]; |
551 | } else { |
552 | return false; |
553 | } |
554 | } |
555 | |
ec2a28a6 |
556 | function set_field($table, $newfield, $newvalue, $field1, $value1, $field2="", $value2="", $field3="", $value3="") { |
df28d6c5 |
557 | /// Set a single field in a database record |
558 | |
559 | global $db, $CFG; |
560 | |
ec2a28a6 |
561 | $select = "WHERE $field1 = '$value1'"; |
562 | |
563 | if ($field2) { |
564 | $select .= " AND $field2 = '$value2'"; |
565 | if ($field3) { |
566 | $select .= " AND $field3 = '$value3'"; |
567 | } |
568 | } |
569 | |
570 | return $db->Execute("UPDATE $CFG->prefix$table SET $newfield = '$newvalue' $select"); |
df28d6c5 |
571 | } |
572 | |
573 | |
5c63e0c4 |
574 | function delete_records($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") { |
df28d6c5 |
575 | /// Delete one or more records from a table |
576 | |
577 | global $db, $CFG; |
578 | |
5c63e0c4 |
579 | if ($field1) { |
580 | $select = "WHERE $field1 = '$value1'"; |
9fa49e22 |
581 | if ($field2) { |
df28d6c5 |
582 | $select .= " AND $field2 = '$value2'"; |
9fa49e22 |
583 | if ($field3) { |
df28d6c5 |
584 | $select .= " AND $field3 = '$value3'"; |
585 | } |
586 | } |
5c63e0c4 |
587 | } else { |
588 | $select = ""; |
df28d6c5 |
589 | } |
590 | |
591 | return $db->Execute("DELETE FROM $CFG->prefix$table $select"); |
592 | } |
593 | |
30f89d68 |
594 | function delete_records_select($table, $select="") { |
595 | /// Delete one or more records from a table |
596 | /// "select" is a fragment of SQL to define the selection criteria |
597 | |
598 | global $CFG, $db; |
599 | |
600 | if ($select) { |
601 | $select = "WHERE $select"; |
602 | } |
603 | |
604 | return $db->Execute("DELETE FROM $CFG->prefix$table $select"); |
605 | } |
606 | |
df28d6c5 |
607 | |
608 | function insert_record($table, $dataobject, $returnid=true) { |
609 | /// Insert a record into a table and return the "id" field if required |
610 | /// If the return ID isn't required, then this just reports success as true/false. |
611 | /// $dataobject is an object containing needed data |
612 | |
613 | global $db, $CFG; |
614 | |
615 | // Determine all the fields needed |
616 | if (! $columns = $db->MetaColumns("$CFG->prefix$table")) { |
617 | return false; |
618 | } |
df28d6c5 |
619 | $data = (array)$dataobject; |
620 | |
b3fa6684 |
621 | // Pull out data from the dataobject that matches the fields in the table. |
622 | // If fields are missing or empty, then try to set the defaults explicitly |
623 | // because some databases (eg PostgreSQL) don't always set them properly |
df28d6c5 |
624 | foreach ($columns as $column) { |
9655b654 |
625 | if(isset($column->primary_key) and $column->primary_key == 1) { |
626 | $pkey = $column->name; // take column name of primary key |
627 | } |
b3fa6684 |
628 | if ($column->name <> "id") { |
629 | if (isset($data[$column->name])) { |
50913937 |
630 | if ((string)$data[$column->name] == "" and !empty($column->has_default) and !empty($column->default_value)) { |
b3fa6684 |
631 | $ddd[$column->name] = $column->default_value; |
632 | } else { |
dc8791f3 |
633 | $ddd[$column->name] = $data[$column->name]; |
b3fa6684 |
634 | } |
635 | } else { |
92230499 |
636 | if (!empty($column->has_default) and !empty($column->default_value)) { |
b3fa6684 |
637 | $ddd[$column->name] = $column->default_value; |
638 | } |
639 | } |
df28d6c5 |
640 | } |
641 | } |
642 | |
b3fa6684 |
643 | |
df28d6c5 |
644 | // Construct SQL queries |
645 | if (! $numddd = count($ddd)) { |
646 | return false; |
647 | } |
648 | |
649 | $count = 0; |
650 | $inscolumns = ""; |
651 | $insvalues = ""; |
652 | $select = ""; |
653 | |
654 | foreach ($ddd as $key => $value) { |
3c72e2f9 |
655 | if (!is_null($value)){ |
656 | if ($select) { |
657 | $inscolumns .= ", "; |
658 | $insvalues .= ", "; |
659 | $select .= " AND "; |
660 | } |
661 | $inscolumns .= "$key"; |
662 | $insvalues .= "'$value'"; |
663 | $select .= "$key = '$value'"; |
df28d6c5 |
664 | } |
665 | } |
666 | |
667 | if (! $rs = $db->Execute("INSERT INTO $CFG->prefix$table ($inscolumns) VALUES ($insvalues)")) { |
668 | return false; |
669 | } |
670 | |
671 | if ($returnid) { |
64929926 |
672 | if ($CFG->dbtype == "mysql" ) { |
99988d1a |
673 | return $db->Insert_ID(); // ADOdb has stored the ID for us, but it isn't reliable |
674 | } |
1523be78 |
675 | |
9655b654 |
676 | if ($CFG->dbtype == "postgres7" and isset($pkey)){ |
677 | $oid = $db->Insert_ID(); |
28ba0d55 |
678 | if ($rs = $db->Execute("SELECT $pkey FROM $CFG->prefix$table WHERE oid = $oid")) { |
9655b654 |
679 | if ($rs->RecordCount() == 1) { |
680 | return $rs->fields[0]; |
681 | } else { |
682 | return false; |
683 | } |
684 | } |
685 | } |
1523be78 |
686 | // Try to pull the record out again to find the id. This is the most cross-platform method. |
df28d6c5 |
687 | if ($rs = $db->Execute("SELECT id FROM $CFG->prefix$table WHERE $select")) { |
1523be78 |
688 | if ($rs->RecordCount() == 1) { |
689 | return $rs->fields[0]; |
690 | } |
df28d6c5 |
691 | } |
1523be78 |
692 | |
693 | return false; |
694 | |
df28d6c5 |
695 | } else { |
696 | return true; |
697 | } |
698 | } |
699 | |
700 | |
701 | function update_record($table, $dataobject) { |
702 | /// Update a record in a table |
703 | /// $dataobject is an object containing needed data |
ebc3bd2b |
704 | /// Relies on $dataobject having a variable "id" to |
705 | /// specify the record to update |
df28d6c5 |
706 | |
707 | global $db, $CFG; |
708 | |
709 | if (! isset($dataobject->id) ) { |
710 | return false; |
711 | } |
712 | |
713 | // Determine all the fields in the table |
714 | if (!$columns = $db->MetaColumns("$CFG->prefix$table")) { |
715 | return false; |
716 | } |
717 | $data = (array)$dataobject; |
718 | |
719 | // Pull out data matching these fields |
720 | foreach ($columns as $column) { |
92230499 |
721 | if ($column->name <> "id" and isset($data[$column->name]) ) { |
df28d6c5 |
722 | $ddd[$column->name] = $data[$column->name]; |
723 | } |
724 | } |
725 | |
726 | // Construct SQL queries |
727 | $numddd = count($ddd); |
728 | $count = 0; |
729 | $update = ""; |
730 | |
731 | foreach ($ddd as $key => $value) { |
732 | $count++; |
733 | $update .= "$key = '$value'"; |
734 | if ($count < $numddd) { |
735 | $update .= ", "; |
736 | } |
737 | } |
738 | |
739 | if ($rs = $db->Execute("UPDATE $CFG->prefix$table SET $update WHERE id = '$dataobject->id'")) { |
740 | return true; |
741 | } else { |
742 | return false; |
743 | } |
744 | } |
745 | |
746 | |
df28d6c5 |
747 | |
748 | |
749 | /// USER DATABASE //////////////////////////////////////////////// |
750 | |
751 | function get_user_info_from_db($field, $value) { |
752 | /// Get a complete user record, which includes all the info |
753 | /// in the user record, as well as membership information |
754 | /// Suitable for setting as $USER session cookie. |
755 | |
18496c59 |
756 | if (!$field or !$value) { |
df28d6c5 |
757 | return false; |
df28d6c5 |
758 | } |
759 | |
18496c59 |
760 | if (! $user = get_record_select("user", "$field = '$value' AND deleted <> '1'")) { |
761 | return false; |
762 | } |
df28d6c5 |
763 | |
18496c59 |
764 | // Add membership information |
df28d6c5 |
765 | |
18496c59 |
766 | if ($site = get_site()) { // Everyone is always a member of the top course |
767 | $user->student[$site->id] = true; |
768 | } |
df28d6c5 |
769 | |
f53af941 |
770 | if ($students = get_records("user_students", "userid", $user->id)) { |
771 | foreach ($students as $student) { |
a6d82c3f |
772 | if (get_field("course", "visible", "id", $student->course)) { |
773 | $user->student[$student->course] = true; |
774 | $user->zoom[$student->course] = $student->zoom; |
775 | } |
f53af941 |
776 | } |
18496c59 |
777 | } |
df28d6c5 |
778 | |
f53af941 |
779 | if ($teachers = get_records("user_teachers", "userid", $user->id)) { |
780 | foreach ($teachers as $teacher) { |
781 | $user->teacher[$teacher->course] = true; |
782 | } |
18496c59 |
783 | } |
df28d6c5 |
784 | |
f53af941 |
785 | if ($admins = get_records("user_admins", "userid", $user->id)) { |
786 | foreach ($admins as $admin) { |
787 | $user->admin = true; |
788 | break; |
789 | } |
df28d6c5 |
790 | } |
18496c59 |
791 | |
b86fc0e2 |
792 | if ($displays = get_records("course_display", "userid", $user->id)) { |
793 | foreach ($displays as $display) { |
794 | $user->display[$display->course] = $display->display; |
795 | } |
796 | } |
797 | |
18496c59 |
798 | return $user; |
df28d6c5 |
799 | } |
800 | |
801 | function update_user_in_db() { |
802 | /// Updates user record to record their last access |
803 | |
804 | global $db, $USER, $REMOTE_ADDR, $CFG; |
805 | |
806 | if (!isset($USER->id)) |
807 | return false; |
808 | |
809 | $timenow = time(); |
810 | if ($db->Execute("UPDATE {$CFG->prefix}user SET lastIP='$REMOTE_ADDR', lastaccess='$timenow' |
811 | WHERE id = '$USER->id' ")) { |
812 | return true; |
813 | } else { |
814 | return false; |
815 | } |
816 | } |
817 | |
818 | |
819 | function adminlogin($username, $md5password) { |
820 | /// Does this username and password specify a valid admin user? |
821 | |
822 | global $CFG; |
823 | |
9fa49e22 |
824 | return record_exists_sql("SELECT u.id |
825 | FROM {$CFG->prefix}user u, |
826 | {$CFG->prefix}user_admins a |
ebc3bd2b |
827 | WHERE u.id = a.userid |
df28d6c5 |
828 | AND u.username = '$username' |
829 | AND u.password = '$md5password'"); |
830 | } |
831 | |
832 | |
833 | function get_site () { |
834 | /// Returns $course object of the top-level site. |
835 | |
836 | if ( $course = get_record("course", "category", 0)) { |
837 | return $course; |
838 | } else { |
839 | return false; |
840 | } |
841 | } |
842 | |
d2b6ba70 |
843 | function get_courses($categoryid="all", $sort="sortorder ASC", $fields="*") { |
c2cb4545 |
844 | /// Returns list of courses, for whole site, or category |
9fa49e22 |
845 | |
d2b6ba70 |
846 | if ($categoryid == "all") { |
847 | $courses = get_records("course", "", "", $sort, $fields); |
848 | } else { |
849 | $courses = get_records("course", "category", "$categoryid", $sort, $fields); |
a6d82c3f |
850 | } |
851 | |
852 | if ($courses) { /// Remove unavailable courses from the list |
853 | foreach ($courses as $key => $course) { |
854 | if (!$course->visible) { |
855 | if (!isteacher($course->id)) { |
856 | unset($courses[$key]); |
857 | } |
858 | } |
859 | } |
9fa49e22 |
860 | } |
a6d82c3f |
861 | return $courses; |
9fa49e22 |
862 | } |
863 | |
d2b6ba70 |
864 | |
c2cb4545 |
865 | function get_my_courses($userid, $sort="c.fullname ASC") { |
866 | global $CFG; |
867 | |
868 | return get_records_sql("SELECT c.* |
869 | FROM {$CFG->prefix}course c, |
870 | {$CFG->prefix}user_students s, |
871 | {$CFG->prefix}user_teachers t |
872 | WHERE (s.userid = '$userid' AND s.course = c.id) |
873 | OR (t.userid = '$userid' AND t.course = c.id) |
874 | GROUP BY c.id |
875 | ORDER BY $sort"); |
876 | } |
877 | |
df4f0670 |
878 | function get_courses_search($search, $sort="fullname ASC", $page=0, $recordsperpage=50) { |
879 | /// Returns a list of courses that match a search |
880 | |
881 | global $CFG; |
882 | |
883 | switch ($CFG->dbtype) { |
884 | case "mysql": |
885 | $limit = "LIMIT $page,$recordsperpage"; |
886 | break; |
887 | case "postgres7": |
888 | $limit = "LIMIT $recordsperpage OFFSET ".($page * $recordsperpage); |
889 | break; |
890 | default: |
891 | $limit = "LIMIT $recordsperpage,$page"; |
892 | } |
893 | |
894 | //to allow caseinsensitive search for postgesql |
895 | if ($CFG->dbtype == "postgres7") { |
896 | $LIKE = "ILIKE"; |
897 | } else { |
898 | $LIKE = "LIKE"; |
899 | } |
900 | |
901 | $fullnamesearch = ""; |
902 | $summarysearch = ""; |
903 | |
904 | $searchterms = explode(" ", $search); // Search for words independently |
905 | |
906 | foreach ($searchterms as $searchterm) { |
907 | if ($fullnamesearch) { |
908 | $fullnamesearch .= " AND "; |
909 | } |
910 | $fullnamesearch .= " fullname $LIKE '%$searchterm%' "; |
911 | |
912 | if ($summarysearch) { |
913 | $summarysearch .= " AND "; |
914 | } |
915 | $summarysearch .= " summary $LIKE '%$searchterm%' "; |
916 | } |
917 | |
918 | |
919 | $courses = get_records_sql("SELECT * |
920 | FROM {$CFG->prefix}course |
921 | WHERE ($fullnamesearch OR $summarysearch) |
922 | ORDER BY $sort $limit"); |
923 | |
924 | if ($courses) { /// Remove unavailable courses from the list |
925 | foreach ($courses as $key => $course) { |
926 | if (!$course->visible) { |
927 | if (!isteacher($course->id)) { |
928 | unset($courses[$key]); |
929 | } |
930 | } |
931 | } |
932 | } |
933 | |
934 | return $courses; |
935 | } |
936 | |
c2cb4545 |
937 | |
938 | function get_categories($parent="none", $sort="sortorder ASC") { |
d2b6ba70 |
939 | /// Returns a sorted list of categories |
940 | |
c2cb4545 |
941 | if ($parent == "none") { |
942 | $categories = get_records("course_categories", "", "", $sort); |
943 | } else { |
944 | $categories = get_records("course_categories", "parent", $parent, $sort); |
945 | } |
946 | if ($categories) { /// Remove unavailable categories from the list |
947 | $admin = isadmin(); |
948 | foreach ($categories as $key => $category) { |
949 | if (!$category->visible) { |
950 | if (!$admin) { |
951 | unset($categories[$key]); |
952 | } |
953 | } |
954 | } |
955 | } |
956 | return $categories; |
9fa49e22 |
957 | } |
958 | |
d2b6ba70 |
959 | |
df4f0670 |
960 | function fix_course_sortorder($categoryid, $sort="sortorder ASC") { |
d2b6ba70 |
961 | /// Given a category object, this function makes sure the courseorder |
7e1a999c |
962 | /// variable reflects the real world. |
963 | |
df4f0670 |
964 | if (!$courses = get_records("course", "category", "$categoryid", "$sort", "id, sortorder")) { |
d2b6ba70 |
965 | return true; |
7e1a999c |
966 | } |
967 | |
d2b6ba70 |
968 | $count = 0; |
969 | $modified = false; |
7e1a999c |
970 | |
d2b6ba70 |
971 | foreach ($courses as $course) { |
972 | if ($course->sortorder != $count) { |
973 | set_field("course", "sortorder", $count, "id", $course->id); |
974 | $modified = true; |
7e1a999c |
975 | } |
d2b6ba70 |
976 | $count++; |
7e1a999c |
977 | } |
d2b6ba70 |
978 | |
979 | if ($modified) { |
980 | set_field("course_categories", "timemodified", time(), "id", $categoryid); |
7e1a999c |
981 | } |
d2b6ba70 |
982 | |
7e1a999c |
983 | return true; |
984 | } |
985 | |
9fa49e22 |
986 | |
987 | function get_guest() { |
988 | return get_user_info_from_db("username", "guest"); |
989 | } |
990 | |
991 | |
df28d6c5 |
992 | function get_admin () { |
993 | /// Returns $user object of the main admin user |
994 | |
995 | global $CFG; |
996 | |
997 | if ( $admins = get_admins() ) { |
998 | foreach ($admins as $admin) { |
999 | return $admin; // ie the first one |
1000 | } |
1001 | } else { |
1002 | return false; |
1003 | } |
1004 | } |
1005 | |
1006 | function get_admins() { |
1007 | /// Returns list of all admins |
1008 | |
1009 | global $CFG; |
1010 | |
ebc3bd2b |
1011 | return get_records_sql("SELECT u.* |
1012 | FROM {$CFG->prefix}user u, |
1013 | {$CFG->prefix}user_admins a |
1014 | WHERE a.userid = u.id |
1015 | ORDER BY u.id ASC"); |
df28d6c5 |
1016 | } |
1017 | |
1924074c |
1018 | function get_creators() { |
1019 | /// Returns list of all admins |
1020 | |
1021 | global $CFG; |
1022 | |
1023 | return get_records_sql("SELECT u.* |
1024 | FROM {$CFG->prefix}user u, |
1025 | {$CFG->prefix}user_coursecreators a |
1026 | WHERE a.userid = u.id |
1027 | ORDER BY u.id ASC"); |
1028 | } |
df28d6c5 |
1029 | |
1030 | function get_teacher($courseid) { |
1031 | /// Returns $user object of the main teacher for a course |
1032 | |
1033 | global $CFG; |
1034 | |
1035 | if ( $teachers = get_course_teachers($courseid, "t.authority ASC")) { |
1036 | foreach ($teachers as $teacher) { |
1037 | if ($teacher->authority) { |
1038 | return $teacher; // the highest authority teacher |
1039 | } |
1040 | } |
1041 | } else { |
1042 | return false; |
1043 | } |
1044 | } |
1045 | |
2d0b30a0 |
1046 | function get_course_students($courseid, $sort="u.lastaccess DESC") { |
df28d6c5 |
1047 | /// Returns list of all students in this course |
adaf3928 |
1048 | /// if courseid = 0 then return ALL students in all courses |
df28d6c5 |
1049 | |
1050 | global $CFG; |
1051 | |
688d06f4 |
1052 | return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat, |
9d6f5d42 |
1053 | u.email, u.city, u.country, u.lastaccess, u.lastlogin, u.picture |
1054 | FROM {$CFG->prefix}user u, |
1055 | {$CFG->prefix}user_students s |
2d0b30a0 |
1056 | WHERE s.course = '$courseid' AND s.userid = u.id AND u.deleted = '0' |
df28d6c5 |
1057 | ORDER BY $sort"); |
1058 | } |
1059 | |
2d0b30a0 |
1060 | function get_course_teachers($courseid, $sort="t.authority ASC") { |
df28d6c5 |
1061 | /// Returns list of all teachers in this course |
adaf3928 |
1062 | /// if courseid = 0 then return ALL teachers in all courses |
df28d6c5 |
1063 | |
1064 | global $CFG; |
1065 | |
688d06f4 |
1066 | return get_records_sql("SELECT u.*,t.authority,t.role |
1067 | FROM {$CFG->prefix}user u, |
1068 | {$CFG->prefix}user_teachers t |
2d0b30a0 |
1069 | WHERE t.course = '$courseid' AND t.userid = u.id AND u.deleted = '0' |
df28d6c5 |
1070 | ORDER BY $sort"); |
1071 | } |
1072 | |
1073 | function get_course_users($courseid, $sort="u.lastaccess DESC") { |
353d0338 |
1074 | /// Returns all the users of a course: students and teachers |
1075 | /// If the "course" is actually the site, then return all site users. |
1076 | |
1077 | $site = get_site(); |
1078 | |
1079 | if ($courseid == $site->id) { |
1080 | return get_site_users($sort); |
1081 | } |
1082 | |
1083 | /// Using this method because the single SQL just would not always work! |
df28d6c5 |
1084 | |
1085 | $teachers = get_course_teachers($courseid, $sort); |
1086 | $students = get_course_students($courseid, $sort); |
1087 | |
1088 | if ($teachers and $students) { |
1089 | return array_merge($teachers, $students); |
1090 | } else if ($teachers) { |
1091 | return $teachers; |
1092 | } else { |
1093 | return $students; |
1094 | } |
1095 | |
353d0338 |
1096 | /// Why wouldn't this work? |
1097 | /// return get_records_sql("SELECT u.* FROM user u, user_students s, user_teachers t |
1098 | /// WHERE (s.course = '$courseid' AND s.userid = u.id) OR |
1099 | /// (t.course = '$courseid' AND t.userid = u.id) |
1100 | /// ORDER BY $sort"); |
df28d6c5 |
1101 | } |
1102 | |
2d0b30a0 |
1103 | function get_site_users($sort="u.lastaccess DESC") { |
1104 | /// Returns a list of all active users who are enrolled |
1105 | /// or teaching in courses on this server |
1106 | |
353d0338 |
1107 | global $CFG, $db; |
1108 | |
1109 | //$db->debug = true; |
2d0b30a0 |
1110 | |
688d06f4 |
1111 | return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat, |
bbf9b162 |
1112 | u.email, u.city, u.country, u.lastaccess, u.lastlogin, u.picture |
1113 | FROM {$CFG->prefix}user u, |
1114 | {$CFG->prefix}user_students s, |
353d0338 |
1115 | {$CFG->prefix}user_teachers t, |
1116 | {$CFG->prefix}user_coursecreators c, |
1117 | {$CFG->prefix}user_admins a |
bbf9b162 |
1118 | WHERE s.userid = u.id |
1119 | OR t.userid = u.id |
353d0338 |
1120 | OR a.userid = u.id |
1121 | OR c.userid = u.id |
1122 | GROUP BY u.id |
1123 | ORDER BY $sort "); |
2d0b30a0 |
1124 | } |
1125 | |
9fa49e22 |
1126 | |
5a741655 |
1127 | function get_users($get=true, $search="", $confirmed=false, $exceptions="", $sort="firstname ASC") { |
1128 | /// Returns a subset of users, |
1129 | /// $get - if false then only a count of the records is returned |
1130 | /// $search is a simple string to search for |
1131 | /// $confirmed is a switch to allow/disallow unconfirmed users |
1132 | /// $exceptions is a list of IDs to ignore, eg 2,4,5,8,9,10 |
1133 | /// $sort is a sorting criteria to use |
e384fb7b |
1134 | |
1135 | if ($search) { |
1136 | $search = " AND (firstname LIKE '%$search%' |
1137 | OR lastname LIKE '%$search%' |
1138 | OR email LIKE '%$search%') "; |
1139 | } |
1140 | |
5a741655 |
1141 | if ($confirmed) { |
1142 | $confirmed = " AND confirmed = '1' "; |
1143 | } |
1144 | |
1145 | if ($exceptions) { |
1146 | $exceptions = " AND id NOT IN ($exceptions) "; |
1147 | } |
1148 | |
1149 | if ($sort and $get) { |
1150 | $sort = " ORDER BY $sort "; |
1151 | } else { |
1152 | $sort = ""; |
1153 | } |
1154 | |
1155 | if ($get) { |
1156 | return get_records_select("user", "username <> 'guest' AND deleted = 0 $search $confirmed $exceptions $sort"); |
1157 | } else { |
1158 | return count_records_select("user", "username <> 'guest' AND deleted = 0 $search $confirmed $exceptions $sort"); |
1159 | } |
9fa49e22 |
1160 | } |
1161 | |
5a741655 |
1162 | |
c750592a |
1163 | function get_users_listing($sort, $dir="ASC", $page=1, $recordsperpage=20, $search="") { |
9fa49e22 |
1164 | global $CFG; |
31fefa63 |
1165 | |
c2a96d6b |
1166 | switch ($CFG->dbtype) { |
1167 | case "mysql": |
1168 | $limit = "LIMIT $page,$recordsperpage"; |
1169 | break; |
1170 | case "postgres7": |
a918234e |
1171 | $limit = "LIMIT $recordsperpage OFFSET ".($page); |
c2a96d6b |
1172 | break; |
1173 | default: |
1174 | $limit = "LIMIT $recordsperpage,$page"; |
31fefa63 |
1175 | } |
c2a96d6b |
1176 | |
c750592a |
1177 | if ($search) { |
1178 | $search = " AND (firstname LIKE '%$search%' |
1179 | OR lastname LIKE '%$search%' |
1180 | OR email LIKE '%$search%') "; |
1181 | } |
1182 | |
9fa49e22 |
1183 | return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess |
1184 | FROM {$CFG->prefix}user |
1185 | WHERE username <> 'guest' |
c750592a |
1186 | AND deleted <> '1' $search |
31fefa63 |
1187 | ORDER BY $sort $dir $limit"); |
9fa49e22 |
1188 | |
1189 | } |
1190 | |
1191 | function get_users_confirmed() { |
1192 | global $CFG; |
1193 | return get_records_sql("SELECT * |
1194 | FROM {$CFG->prefix}user |
1195 | WHERE confirmed = 1 |
1196 | AND deleted = 0 |
1197 | AND username <> 'guest' |
1198 | AND username <> 'changeme'"); |
1199 | } |
1200 | |
1201 | |
99988d1a |
1202 | function get_users_unconfirmed($cutofftime=2000000000) { |
9fa49e22 |
1203 | global $CFG; |
1204 | return get_records_sql("SELECT * |
1205 | FROM {$CFG->prefix}user |
1206 | WHERE confirmed = 0 |
1207 | AND firstaccess > 0 |
1208 | AND firstaccess < '$cutofftime'"); |
1209 | } |
1210 | |
1211 | |
1212 | function get_users_longtimenosee($cutofftime) { |
1213 | global $CFG; |
31fefa63 |
1214 | |
1215 | $db->debug = true; |
9fa49e22 |
1216 | return get_records_sql("SELECT u.* |
1217 | FROM {$CFG->prefix}user u, |
1218 | {$CFG->prefix}user_students s |
31fefa63 |
1219 | WHERE u.lastaccess > '0' |
1220 | AND u.lastaccess < '$cutofftime' |
97485d07 |
1221 | AND u.id = s.userid |
1222 | GROUP BY u.id"); |
9fa49e22 |
1223 | } |
1224 | |
1225 | |
df28d6c5 |
1226 | /// MODULE FUNCTIONS ///////////////////////////////////////////////// |
1227 | |
9fa49e22 |
1228 | function get_course_mods($courseid) { |
1229 | /// Just gets a raw list of all modules in a course |
1230 | global $CFG; |
1231 | |
7acaa63d |
1232 | return get_records_sql("SELECT cm.*, m.name as modname |
1233 | FROM {$CFG->prefix}modules m, |
1234 | {$CFG->prefix}course_modules cm |
9fa49e22 |
1235 | WHERE cm.course = '$courseid' |
1236 | AND cm.deleted = '0' |
1237 | AND cm.module = m.id "); |
1238 | } |
1239 | |
df28d6c5 |
1240 | function get_coursemodule_from_instance($modulename, $instance, $courseid) { |
1241 | /// Given an instance of a module, finds the coursemodule description |
1242 | |
1243 | global $CFG; |
1244 | |
1245 | return get_record_sql("SELECT cm.*, m.name |
7acaa63d |
1246 | FROM {$CFG->prefix}course_modules cm, |
1247 | {$CFG->prefix}modules md, |
1248 | {$CFG->prefix}$modulename m |
df28d6c5 |
1249 | WHERE cm.course = '$courseid' AND |
1250 | cm.deleted = '0' AND |
1251 | cm.instance = m.id AND |
1252 | md.name = '$modulename' AND |
1253 | md.id = cm.module AND |
1254 | m.id = '$instance'"); |
1255 | |
1256 | } |
1257 | |
cccb016a |
1258 | function get_all_instances_in_course($modulename, $course) { |
df28d6c5 |
1259 | /// Returns an array of all the active instances of a particular |
cccb016a |
1260 | /// module in a given course, sorted in the order they are defined |
1261 | /// in the course. Returns false on any errors. |
1262 | /// $course is a course object, this depends on an accurate $course->modinfo |
df28d6c5 |
1263 | |
1264 | global $CFG; |
1265 | |
cccb016a |
1266 | if (!$modinfo = unserialize($course->modinfo)) { |
1267 | return array(); |
1acfbce5 |
1268 | } |
1269 | |
cccb016a |
1270 | if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible |
7acaa63d |
1271 | FROM {$CFG->prefix}course_modules cm, |
1272 | {$CFG->prefix}course_sections cw, |
1273 | {$CFG->prefix}modules md, |
1274 | {$CFG->prefix}$modulename m |
cccb016a |
1275 | WHERE cm.course = '$course->id' AND |
df28d6c5 |
1276 | cm.instance = m.id AND |
1277 | cm.deleted = '0' AND |
1278 | cm.section = cw.id AND |
1279 | md.name = '$modulename' AND |
cccb016a |
1280 | md.id = cm.module")) { |
1281 | return array(); |
1282 | } |
1283 | |
1284 | // Hide non-visible instances from students |
1285 | if (isteacher($course->id)) { |
1286 | $invisible = -1; |
1287 | } else { |
1288 | $invisible = 0; |
1289 | } |
1290 | |
1291 | foreach ($modinfo as $mod) { |
1292 | if ($mod->mod == $modulename and $mod->visible > $invisible) { |
1293 | $outputarray[] = $rawmods[$mod->cm]; |
1294 | } |
1295 | } |
1296 | |
1297 | return $outputarray; |
df28d6c5 |
1298 | |
1299 | } |
1300 | |
9fa49e22 |
1301 | |
580f2fbc |
1302 | function instance_is_visible($moduletype, $module) { |
1303 | /// Given a valid module object with info about the id and course, |
1304 | /// and the module's type (eg "forum") returns whether the object |
1305 | /// is visible or not |
1306 | |
1307 | global $CFG; |
1308 | |
86e6076b |
1309 | if ($records = get_records_sql("SELECT cm.instance, cm.visible |
580f2fbc |
1310 | FROM {$CFG->prefix}course_modules cm, |
580f2fbc |
1311 | {$CFG->prefix}modules m |
1312 | WHERE cm.course = '$module->course' AND |
1313 | cm.module = m.id AND |
1314 | m.name = '$moduletype' AND |
86e6076b |
1315 | cm.instance = '$module->id'")) { |
580f2fbc |
1316 | |
1317 | foreach ($records as $record) { // there should only be one - use the first one |
1318 | return $record->visible; |
1319 | } |
1320 | } |
1321 | |
1322 | return true; // visible by default! |
1323 | } |
1324 | |
a3fb1c45 |
1325 | |
1326 | |
1327 | |
9fa49e22 |
1328 | /// LOG FUNCTIONS ///////////////////////////////////////////////////// |
1329 | |
1330 | |
1331 | function add_to_log($course, $module, $action, $url="", $info="") { |
1332 | /// Add an entry to the log table. These are "action" focussed rather |
1333 | /// than web server hits, and provide a way to easily reconstruct what |
1334 | /// any particular student has been doing. |
1335 | /// |
1336 | /// course = the course id |
1337 | /// module = forum, journal, resource, course, user etc |
1338 | /// action = view, edit, post (often but not always the same as the file.php) |
1339 | /// url = the file and parameters used to see the results of the action |
1340 | /// info = additional description information |
1341 | |
31fefa63 |
1342 | global $db, $CFG, $USER, $REMOTE_ADDR; |
9fa49e22 |
1343 | |
1344 | if (isset($USER->realuser)) { // Don't log |
1345 | return; |
1346 | } |
1347 | |
61e96406 |
1348 | $userid = empty($USER->id) ? "" : $USER->id; |
1349 | |
9fa49e22 |
1350 | $timenow = time(); |
1351 | $info = addslashes($info); |
1352 | |
31fefa63 |
1353 | $result = $db->Execute("INSERT INTO {$CFG->prefix}log (time, |
ebc3bd2b |
1354 | userid, |
1355 | course, |
1356 | ip, |
1357 | module, |
1358 | action, |
1359 | url, |
1360 | info) |
1361 | VALUES ('$timenow', |
61e96406 |
1362 | '$userid', |
ebc3bd2b |
1363 | '$course', |
1364 | '$REMOTE_ADDR', |
1365 | '$module', |
1366 | '$action', |
1367 | '$url', |
1368 | '$info')"); |
1369 | |
ce78926d |
1370 | if (!$result and ($CFG->debug > 7)) { |
9fa49e22 |
1371 | echo "<P>Error: Could not insert a new entry to the Moodle log</P>"; // Don't throw an error |
1372 | } |
1373 | } |
1374 | |
1375 | |
1376 | function get_logs($select, $order) { |
1377 | global $CFG; |
1378 | |
1379 | return get_records_sql("SELECT l.*, u.firstname, u.lastname, u.picture |
1380 | FROM {$CFG->prefix}log l, |
1381 | {$CFG->prefix}user u |
1382 | $select $order"); |
1383 | } |
1384 | |
1385 | function get_logs_usercourse($userid, $courseid, $coursestart) { |
1386 | global $CFG; |
1387 | |
da0c90c3 |
1388 | if ($courseid) { |
1389 | $courseselect = " AND course = '$courseid' "; |
1390 | } |
1391 | |
9fa49e22 |
1392 | return get_records_sql("SELECT floor((`time` - $coursestart)/86400) as day, count(*) as num |
1393 | FROM {$CFG->prefix}log |
ebc3bd2b |
1394 | WHERE userid = '$userid' |
da0c90c3 |
1395 | AND `time` > '$coursestart' $courseselect |
9fa49e22 |
1396 | GROUP BY day "); |
1397 | } |
1398 | |
1399 | function get_logs_userday($userid, $courseid, $daystart) { |
1400 | global $CFG; |
1401 | |
7e4a6488 |
1402 | if ($courseid) { |
1403 | $courseselect = " AND course = '$courseid' "; |
1404 | } |
1405 | |
9fa49e22 |
1406 | return get_records_sql("SELECT floor((`time` - $daystart)/3600) as hour, count(*) as num |
1407 | FROM {$CFG->prefix}log |
ebc3bd2b |
1408 | WHERE userid = '$userid' |
7e4a6488 |
1409 | AND `time` > '$daystart' $courseselect |
9fa49e22 |
1410 | GROUP BY hour "); |
1411 | } |
1412 | |
a3fb1c45 |
1413 | /// GENERAL HELPFUL THINGS /////////////////////////////////// |
1414 | |
1415 | function print_object($object) { |
1416 | /// Mostly just for debugging |
1417 | |
2b051f1c |
1418 | echo "<PRE>"; |
1419 | print_r($object); |
1420 | echo "</PRE>"; |
a3fb1c45 |
1421 | } |
1422 | |
1423 | |
9fa49e22 |
1424 | |
9d5b689c |
1425 | // vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140: |
df28d6c5 |
1426 | ?> |