Two new functions for randomizing arrays
[moodle.git] / lib / datalib.php
CommitLineData
df28d6c5 1<?PHP // $Id$
2
3/// FUNCTIONS FOR DATABASE HANDLING ////////////////////////////////
4
5function 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
25function modify_database($sqlfile) {
26/// Assumes that the input text file consists of a number
27/// of SQL statements ENDING WITH SEMICOLONS. The semicolons
28/// MUST be the last character in a line.
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
34 if (file_exists($sqlfile)) {
35 $success = true;
36 $lines = file($sqlfile);
37 $command = "";
38
39 while ( list($i, $line) = each($lines) ) {
40 $line = chop($line);
41 $length = strlen($line);
42
43 if ($length && substr($line, 0, 1) <> "#") {
44 if (substr($line, $length-1, 1) == ";") {
45 $line = substr($line, 0, $length-1); // strip ;
46 $command .= $line;
47 $command = str_replace("prefix_", $CFG->prefix, $command); // Table prefixes
48 if (! execute_sql($command)) {
49 $success = false;
50 }
51 $command = "";
52 } else {
53 $command .= $line;
54 }
55 }
56 }
57
58 } else {
59 $success = false;
60 echo "<P>Tried to modify database, but \"$sqlfile\" doesn't exist!</P>";
61 }
62
63 return $success;
64}
65
a3fb1c45 66/// FUNCTIONS TO MODIFY TABLES ////////////////////////////////////////////
67
92230499 68function table_column($table, $oldfield, $field, $type="integer", $size="10",
69 $signed="unsigned", $default="0", $null="not null", $after="") {
a3fb1c45 70/// Add a new field to a table, or modify an existing one (if oldfield is defined).
71 global $CFG;
72
73 switch (strtolower($CFG->dbtype)) {
74
75 case "mysql":
76 case "mysqlt":
77
78 switch (strtolower($type)) {
a3fb1c45 79 case "integer":
92230499 80 $type = "INTEGER($size)";
a3fb1c45 81 break;
92230499 82 case "varchar":
83 $type = "VARCHAR($size)";
a3fb1c45 84 break;
85 }
86
87 if (!empty($oldfield)) {
88 $operation = "CHANGE $oldfield $field";
89 } else {
90 $operation = "ADD $field";
91 }
92
93 $default = "DEFAULT '$default'";
94
95 if (!empty($after)) {
96 $after = "AFTER '$after'";
97 }
98
99 execute_sql("ALTER TABLE {$CFG->prefix}$table $operation $type $signed $default $null $after");
100 break;
101
5a4d292b 102 case "postgres7": // From Petri Asikainen
a3fb1c45 103
5a4d292b 104 //to prevent conflicts with reserved words
105 $field = "\"$field\"";
106 $oldfield = "\"$oldfield\"";
107
108 switch (strtolower($type)) {
109 case "integer":
110 if ($size <= 2) {
111 $type = "INT2";
112 }
113 if ($size <= 4) {
114 $type = "INT";
115 }
116 if ($size > 4) {
117 $type = "INT8";
118 }
119 break;
120 case "varchar":
121 $type = "VARCHAR($size)";
122 break;
123 }
124
125 $default = "DEFAULT '$default'";
126
127 //After is not implemented in postgesql
128 //if (!empty($after)) {
129 // $after = "AFTER '$after'";
130 //}
131
132 if (!empty($oldfield)) {
133 execute_sql("ALTER TABLE {$CFG->prefix}$table RENAME COLUMN $oldfield TO $field");
134 } else {
135 execute_sql("ALTER TABLE {$CFG->prefix}$table ADD COLUMN $field $type");
136 }
137
138 /* SETTING OF COLUMN TO NULL/NOT NULL
139 IS NOT POSIBLE BEFORE POSTGRESQL 7.3
140 THIS COMMENTED OUT UNTIL I FIGuRE OUT HOW GET POSTGESQL VERSION FROM ADODB
141
142 //update default values to table
143 if ($null == "NOT NULL") {
144 execute_sql("UPDATE {$CFG->prefix}$table SET $field=$default where $field=NULL");
145 execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $null");
146 } else {
147 execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field DROP NOT NULL"
148 }
149 */
150
151 execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $default");
152
153 break;
a3fb1c45 154
155 default:
156 switch (strtolower($type)) {
a3fb1c45 157 case "integer":
a3fb1c45 158 $type = "INTEGER";
159 break;
92230499 160 case "varchar":
161 $type = "VARCHAR";
162 break;
a3fb1c45 163 }
164
165 $default = "DEFAULT '$default'";
166
167 if (!empty($after)) {
168 $after = "AFTER '$after'";
169 }
170
171 if (!empty($oldfield)) {
172 execute_sql("ALTER TABLE {$CFG->prefix}$table RENAME COLUMN $oldfield $field");
173 } else {
174 execute_sql("ALTER TABLE {$CFG->prefix}$table ADD COLUMN $field $type");
175 }
176
177 execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $null");
178 execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $default");
179 break;
180
181 }
182}
183
184
185
186/// GENERIC FUNCTIONS TO CHECK AND COUNT RECORDS ////////////////////////////////////////
df28d6c5 187
5c63e0c4 188function record_exists($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") {
df28d6c5 189/// Returns true or false depending on whether the specified record exists
190
191 global $CFG;
192
5c63e0c4 193 if ($field1) {
194 $select = "WHERE $field1 = '$value1'";
9fa49e22 195 if ($field2) {
df28d6c5 196 $select .= " AND $field2 = '$value2'";
9fa49e22 197 if ($field3) {
df28d6c5 198 $select .= " AND $field3 = '$value3'";
199 }
200 }
5c63e0c4 201 } else {
202 $select = "";
df28d6c5 203 }
204
205 return record_exists_sql("SELECT * FROM $CFG->prefix$table $select LIMIT 1");
206}
207
208
209function record_exists_sql($sql) {
210/// Returns true or false depending on whether the specified record exists
211/// The sql statement is provided as a string.
212
213 global $db;
214
215 $rs = $db->Execute($sql);
e53b0823 216 if (empty($rs)) return false;
df28d6c5 217
218 if ( $rs->RecordCount() ) {
219 return true;
220 } else {
221 return false;
222 }
223}
224
225
5c63e0c4 226function count_records($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") {
df28d6c5 227/// Get all the records and count them
228
229 global $CFG;
230
5c63e0c4 231 if ($field1) {
232 $select = "WHERE $field1 = '$value1'";
9fa49e22 233 if ($field2) {
df28d6c5 234 $select .= " AND $field2 = '$value2'";
9fa49e22 235 if ($field3) {
df28d6c5 236 $select .= " AND $field3 = '$value3'";
237 }
238 }
5c63e0c4 239 } else {
240 $select = "";
df28d6c5 241 }
242
243 return count_records_sql("SELECT COUNT(*) FROM $CFG->prefix$table $select");
244}
245
9fa49e22 246function count_records_select($table, $select="") {
247/// Get all the records and count them
248
249 global $CFG;
250
d26d7ed0 251 if ($select) {
252 $select = "WHERE $select";
253 }
254
9fa49e22 255 return count_records_sql("SELECT COUNT(*) FROM $CFG->prefix$table $select");
256}
257
258
df28d6c5 259function count_records_sql($sql) {
260/// Get all the records and count them
261/// The sql statement is provided as a string.
262
263 global $db;
264
265 $rs = $db->Execute("$sql");
e53b0823 266 if (empty($rs)) return 0;
df28d6c5 267
268 return $rs->fields[0];
269}
270
a3fb1c45 271
272
273
274/// GENERIC FUNCTIONS TO GET, INSERT, OR UPDATE DATA ///////////////////////////////////
275
5c63e0c4 276function get_record($table, $field1, $value1, $field2="", $value2="", $field3="", $value3="") {
df28d6c5 277/// Get a single record as an object
278
279 global $CFG;
280
5c63e0c4 281 $select = "WHERE $field1 = '$value1'";
df28d6c5 282
9fa49e22 283 if ($field2) {
df28d6c5 284 $select .= " AND $field2 = '$value2'";
9fa49e22 285 if ($field3) {
df28d6c5 286 $select .= " AND $field3 = '$value3'";
287 }
288 }
289
290 return get_record_sql("SELECT * FROM $CFG->prefix$table $select");
291}
292
293function get_record_sql($sql) {
294/// Get a single record as an object
295/// The sql statement is provided as a string.
296
297 global $db;
298
299 $rs = $db->Execute("$sql");
e53b0823 300 if (empty($rs)) return false;
df28d6c5 301
302 if ( $rs->RecordCount() == 1 ) {
303 return (object)$rs->fields;
304 } else {
305 return false;
306 }
307}
308
18496c59 309function get_record_select($table, $select="", $fields="*") {
310/// Gets one record from a table, as an object
311/// "select" is a fragment of SQL to define the selection criteria
312
313 global $CFG;
314
315 if ($select) {
316 $select = "WHERE $select";
317 }
318
319 return get_record_sql("SELECT $fields FROM $CFG->prefix$table $select");
320}
321
322
df28d6c5 323function get_records($table, $field="", $value="", $sort="", $fields="*") {
324/// Get a number of records as an array of objects
325/// Can optionally be sorted eg "time ASC" or "time DESC"
326/// If "fields" is specified, only those fields are returned
327/// The "key" is the first column returned, eg usually "id"
328
329 global $CFG;
330
9fa49e22 331 if ($field) {
df28d6c5 332 $select = "WHERE $field = '$value'";
5c63e0c4 333 } else {
334 $select = "";
df28d6c5 335 }
5c63e0c4 336
df28d6c5 337 if ($sort) {
5c63e0c4 338 $sort = "ORDER BY $sort";
df28d6c5 339 }
340
5c63e0c4 341 return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort");
df28d6c5 342}
343
9fa49e22 344function get_records_select($table, $select="", $sort="", $fields="*") {
345/// Get a number of records as an array of objects
346/// Can optionally be sorted eg "time ASC" or "time DESC"
347/// "select" is a fragment of SQL to define the selection criteria
348/// The "key" is the first column returned, eg usually "id"
349
350 global $CFG;
351
d26d7ed0 352 if ($select) {
353 $select = "WHERE $select";
5c63e0c4 354 }
355
356 if ($sort) {
357 $sort = "ORDER BY $sort";
d26d7ed0 358 }
359
5c63e0c4 360 return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort");
9fa49e22 361}
362
df28d6c5 363
364function get_records_list($table, $field="", $values="", $sort="", $fields="*") {
9fa49e22 365/// Get a number of records as an array of objects
366/// Differs from get_records() in that the values variable
367/// can be a comma-separated list of values eg "4,5,6,10"
368/// Can optionally be sorted eg "time ASC" or "time DESC"
369/// The "key" is the first column returned, eg usually "id"
df28d6c5 370
371 global $CFG;
372
9fa49e22 373 if ($field) {
df28d6c5 374 $select = "WHERE $field in ($values)";
5c63e0c4 375 } else {
376 $select = "";
df28d6c5 377 }
5c63e0c4 378
df28d6c5 379 if ($sort) {
5c63e0c4 380 $sort = "ORDER BY $sort";
df28d6c5 381 }
382
5c63e0c4 383 return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort");
df28d6c5 384}
385
386
9fa49e22 387
df28d6c5 388function get_records_sql($sql) {
9fa49e22 389/// Get a number of records as an array of objects
390/// The "key" is the first column returned, eg usually "id"
391/// The sql statement is provided as a string.
df28d6c5 392
393 global $db;
394
395 $rs = $db->Execute("$sql");
e53b0823 396 if (empty($rs)) return false;
df28d6c5 397
398 if ( $rs->RecordCount() > 0 ) {
399 if ($records = $rs->GetAssoc(true)) {
400 foreach ($records as $key => $record) {
401 $objects[$key] = (object) $record;
402 }
403 return $objects;
404 } else {
405 return false;
406 }
407 } else {
408 return false;
409 }
410}
411
9fa49e22 412function get_records_menu($table, $field="", $value="", $sort="", $fields="*") {
413/// Get a number of records as an array of objects
414/// Can optionally be sorted eg "time ASC" or "time DESC"
415/// If "fields" is specified, only those fields are returned
416/// The "key" is the first column returned, eg usually "id"
417
418 global $CFG;
419
420 if ($field) {
421 $select = "WHERE $field = '$value'";
5c63e0c4 422 } else {
423 $select = "";
9fa49e22 424 }
5c63e0c4 425
9fa49e22 426 if ($sort) {
5c63e0c4 427 $sort = "ORDER BY $sort";
9fa49e22 428 }
429
5c63e0c4 430 return get_records_sql_menu("SELECT $fields FROM $CFG->prefix$table $select $sort");
9fa49e22 431}
432
433function get_records_select_menu($table, $select="", $sort="", $fields="*") {
434/// Get a number of records as an array of objects
435/// Can optionally be sorted eg "time ASC" or "time DESC"
436/// "select" is a fragment of SQL to define the selection criteria
437/// Returns associative array of first two fields
438
439 global $CFG;
440
d26d7ed0 441 if ($select) {
442 $select = "WHERE $select";
443 }
444
5c63e0c4 445 if ($sort) {
446 $sort = "ORDER BY $sort";
447 }
448
449 return get_records_sql_menu("SELECT $fields FROM $CFG->prefix$table $select $sort");
9fa49e22 450}
451
452
df28d6c5 453function get_records_sql_menu($sql) {
9fa49e22 454/// Given an SQL select, this function returns an associative
455/// array of the first two columns. This is most useful in
456/// combination with the choose_from_menu function to create
457/// a form menu.
df28d6c5 458
459 global $db;
460
461 $rs = $db->Execute("$sql");
e53b0823 462 if (empty($rs)) return false;
df28d6c5 463
464 if ( $rs->RecordCount() > 0 ) {
465 while (!$rs->EOF) {
466 $menu[$rs->fields[0]] = $rs->fields[1];
467 $rs->MoveNext();
468 }
469 return $menu;
470
471 } else {
472 return false;
473 }
474}
475
476function get_field($table, $return, $field, $value) {
477/// Get a single field from a database record
478
479 global $db, $CFG;
480
481 $rs = $db->Execute("SELECT $return FROM $CFG->prefix$table WHERE $field = '$value'");
e53b0823 482 if (empty($rs)) return false;
df28d6c5 483
484 if ( $rs->RecordCount() == 1 ) {
485 return $rs->fields["$return"];
486 } else {
487 return false;
488 }
489}
490
491function set_field($table, $newfield, $newvalue, $field, $value) {
492/// Set a single field in a database record
493
494 global $db, $CFG;
495
496 return $db->Execute("UPDATE $CFG->prefix$table SET $newfield = '$newvalue' WHERE $field = '$value'");
497}
498
499
5c63e0c4 500function delete_records($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") {
df28d6c5 501/// Delete one or more records from a table
502
503 global $db, $CFG;
504
5c63e0c4 505 if ($field1) {
506 $select = "WHERE $field1 = '$value1'";
9fa49e22 507 if ($field2) {
df28d6c5 508 $select .= " AND $field2 = '$value2'";
9fa49e22 509 if ($field3) {
df28d6c5 510 $select .= " AND $field3 = '$value3'";
511 }
512 }
5c63e0c4 513 } else {
514 $select = "";
df28d6c5 515 }
516
517 return $db->Execute("DELETE FROM $CFG->prefix$table $select");
518}
519
520
521function insert_record($table, $dataobject, $returnid=true) {
522/// Insert a record into a table and return the "id" field if required
523/// If the return ID isn't required, then this just reports success as true/false.
524/// $dataobject is an object containing needed data
525
526 global $db, $CFG;
527
528 // Determine all the fields needed
529 if (! $columns = $db->MetaColumns("$CFG->prefix$table")) {
530 return false;
531 }
532
533 $data = (array)$dataobject;
534
b3fa6684 535 // Pull out data from the dataobject that matches the fields in the table.
536 // If fields are missing or empty, then try to set the defaults explicitly
537 // because some databases (eg PostgreSQL) don't always set them properly
df28d6c5 538 foreach ($columns as $column) {
b3fa6684 539 if ($column->name <> "id") {
540 if (isset($data[$column->name])) {
92230499 541 if ($data[$column->name] == "" and !empty($column->has_default) and !empty($column->default_value)) {
b3fa6684 542 $ddd[$column->name] = $column->default_value;
543 } else {
dc8791f3 544 $ddd[$column->name] = $data[$column->name];
b3fa6684 545 }
546 } else {
92230499 547 if (!empty($column->has_default) and !empty($column->default_value)) {
b3fa6684 548 $ddd[$column->name] = $column->default_value;
549 }
550 }
df28d6c5 551 }
552 }
553
b3fa6684 554
df28d6c5 555 // Construct SQL queries
556 if (! $numddd = count($ddd)) {
557 return false;
558 }
559
560 $count = 0;
561 $inscolumns = "";
562 $insvalues = "";
563 $select = "";
564
565 foreach ($ddd as $key => $value) {
3c72e2f9 566 if (!is_null($value)){
567 if ($select) {
568 $inscolumns .= ", ";
569 $insvalues .= ", ";
570 $select .= " AND ";
571 }
572 $inscolumns .= "$key";
573 $insvalues .= "'$value'";
574 $select .= "$key = '$value'";
df28d6c5 575 }
576 }
577
578 if (! $rs = $db->Execute("INSERT INTO $CFG->prefix$table ($inscolumns) VALUES ($insvalues)")) {
579 return false;
580 }
581
582 if ($returnid) {
99988d1a 583 if ($CFG->dbtype == "mysql") {
584 return $db->Insert_ID(); // ADOdb has stored the ID for us, but it isn't reliable
585 }
1523be78 586
587 // Try to pull the record out again to find the id. This is the most cross-platform method.
df28d6c5 588 if ($rs = $db->Execute("SELECT id FROM $CFG->prefix$table WHERE $select")) {
1523be78 589 if ($rs->RecordCount() == 1) {
590 return $rs->fields[0];
591 }
df28d6c5 592 }
1523be78 593
594 return false;
595
df28d6c5 596 } else {
597 return true;
598 }
599}
600
601
602function update_record($table, $dataobject) {
603/// Update a record in a table
604/// $dataobject is an object containing needed data
ebc3bd2b 605/// Relies on $dataobject having a variable "id" to
606/// specify the record to update
df28d6c5 607
608 global $db, $CFG;
609
610 if (! isset($dataobject->id) ) {
611 return false;
612 }
613
614 // Determine all the fields in the table
615 if (!$columns = $db->MetaColumns("$CFG->prefix$table")) {
616 return false;
617 }
618 $data = (array)$dataobject;
619
620 // Pull out data matching these fields
621 foreach ($columns as $column) {
92230499 622 if ($column->name <> "id" and isset($data[$column->name]) ) {
df28d6c5 623 $ddd[$column->name] = $data[$column->name];
624 }
625 }
626
627 // Construct SQL queries
628 $numddd = count($ddd);
629 $count = 0;
630 $update = "";
631
632 foreach ($ddd as $key => $value) {
633 $count++;
634 $update .= "$key = '$value'";
635 if ($count < $numddd) {
636 $update .= ", ";
637 }
638 }
639
640 if ($rs = $db->Execute("UPDATE $CFG->prefix$table SET $update WHERE id = '$dataobject->id'")) {
641 return true;
642 } else {
643 return false;
644 }
645}
646
647
df28d6c5 648
649
650/// USER DATABASE ////////////////////////////////////////////////
651
652function get_user_info_from_db($field, $value) {
653/// Get a complete user record, which includes all the info
654/// in the user record, as well as membership information
655/// Suitable for setting as $USER session cookie.
656
18496c59 657 if (!$field or !$value) {
df28d6c5 658 return false;
df28d6c5 659 }
660
18496c59 661 if (! $user = get_record_select("user", "$field = '$value' AND deleted <> '1'")) {
662 return false;
663 }
df28d6c5 664
18496c59 665 // Add membership information
df28d6c5 666
18496c59 667 if ($site = get_site()) { // Everyone is always a member of the top course
668 $user->student[$site->id] = true;
669 }
df28d6c5 670
f53af941 671 if ($students = get_records("user_students", "userid", $user->id)) {
672 foreach ($students as $student) {
673 $user->student[$student->course] = true;
674 }
18496c59 675 }
df28d6c5 676
f53af941 677 if ($teachers = get_records("user_teachers", "userid", $user->id)) {
678 foreach ($teachers as $teacher) {
679 $user->teacher[$teacher->course] = true;
680 }
18496c59 681 }
df28d6c5 682
f53af941 683 if ($admins = get_records("user_admins", "userid", $user->id)) {
684 foreach ($admins as $admin) {
685 $user->admin = true;
686 break;
687 }
df28d6c5 688 }
18496c59 689
690 return $user;
df28d6c5 691}
692
693function update_user_in_db() {
694/// Updates user record to record their last access
695
696 global $db, $USER, $REMOTE_ADDR, $CFG;
697
698 if (!isset($USER->id))
699 return false;
700
701 $timenow = time();
702 if ($db->Execute("UPDATE {$CFG->prefix}user SET lastIP='$REMOTE_ADDR', lastaccess='$timenow'
703 WHERE id = '$USER->id' ")) {
704 return true;
705 } else {
706 return false;
707 }
708}
709
710
711function adminlogin($username, $md5password) {
712/// Does this username and password specify a valid admin user?
713
714 global $CFG;
715
9fa49e22 716 return record_exists_sql("SELECT u.id
717 FROM {$CFG->prefix}user u,
718 {$CFG->prefix}user_admins a
ebc3bd2b 719 WHERE u.id = a.userid
df28d6c5 720 AND u.username = '$username'
721 AND u.password = '$md5password'");
722}
723
724
725function get_site () {
726/// Returns $course object of the top-level site.
727
728 if ( $course = get_record("course", "category", 0)) {
729 return $course;
730 } else {
731 return false;
732 }
733}
734
9fa49e22 735
736function get_courses($category=0, $sort="fullname ASC") {
737/// Returns list of courses
738
739 if ($category > 0) { // Return all courses in one category
740 return get_records("course", "category", $category, $sort);
741
742 } else if ($category < 0) { // Return all courses, even the site
743 return get_records("course", "", "", $sort);
744
745 } else { // Return all courses, except site
746 return get_records_select("course", "category > 0", $sort);
747 }
748}
749
750function get_categories() {
751 return get_records("course_categories", "", "", "name");
752}
753
754
755function get_guest() {
756 return get_user_info_from_db("username", "guest");
757}
758
759
df28d6c5 760function get_admin () {
761/// Returns $user object of the main admin user
762
763 global $CFG;
764
765 if ( $admins = get_admins() ) {
766 foreach ($admins as $admin) {
767 return $admin; // ie the first one
768 }
769 } else {
770 return false;
771 }
772}
773
774function get_admins() {
775/// Returns list of all admins
776
777 global $CFG;
778
ebc3bd2b 779 return get_records_sql("SELECT u.*
780 FROM {$CFG->prefix}user u,
781 {$CFG->prefix}user_admins a
782 WHERE a.userid = u.id
783 ORDER BY u.id ASC");
df28d6c5 784}
785
786
787function get_teacher($courseid) {
788/// Returns $user object of the main teacher for a course
789
790 global $CFG;
791
792 if ( $teachers = get_course_teachers($courseid, "t.authority ASC")) {
793 foreach ($teachers as $teacher) {
794 if ($teacher->authority) {
795 return $teacher; // the highest authority teacher
796 }
797 }
798 } else {
799 return false;
800 }
801}
802
803function get_course_students($courseid, $sort="u.lastaccess DESC") {
804/// Returns list of all students in this course
805
806 global $CFG;
807
808 return get_records_sql("SELECT u.* FROM {$CFG->prefix}user u, {$CFG->prefix}user_students s
ebc3bd2b 809 WHERE s.course = '$courseid' AND s.userid = u.id AND u.deleted = '0'
df28d6c5 810 ORDER BY $sort");
811}
812
813function get_course_teachers($courseid, $sort="t.authority ASC") {
814/// Returns list of all teachers in this course
815
816 global $CFG;
817
818 return get_records_sql("SELECT u.*,t.authority,t.role FROM {$CFG->prefix}user u, {$CFG->prefix}user_teachers t
ebc3bd2b 819 WHERE t.course = '$courseid' AND t.userid = u.id AND u.deleted = '0'
df28d6c5 820 ORDER BY $sort");
821}
822
823function get_course_users($courseid, $sort="u.lastaccess DESC") {
824/// Using this method because the direct SQL just would not always work!
825
826 $teachers = get_course_teachers($courseid, $sort);
827 $students = get_course_students($courseid, $sort);
828
829 if ($teachers and $students) {
830 return array_merge($teachers, $students);
831 } else if ($teachers) {
832 return $teachers;
833 } else {
834 return $students;
835 }
836
837/// return get_records_sql("SELECT u.* FROM user u, user_students s, user_teachers t
ebc3bd2b 838/// WHERE (s.course = '$courseid' AND s.userid = u.id) OR
839/// (t.course = '$courseid' AND t.userid = u.id)
df28d6c5 840/// ORDER BY $sort");
841}
842
843
9fa49e22 844function get_users_search($search, $sort="u.firstname ASC") {
845 global $CFG;
846
847 return get_records_sql("SELECT * from {$CFG->prefix}user
848 WHERE confirmed = 1
849 AND deleted = 0
850 AND (firstname LIKE '%$search%' OR
851 lastname LIKE '%$search%' OR
852 email LIKE '%$search%')
853 AND username <> 'guest'
854 AND username <> 'changeme'");
855}
856
857
858function get_users_count() {
31fefa63 859 return count_records_select("user", "username <> 'guest' AND deleted <> 1");
9fa49e22 860}
861
862function get_users_listing($sort, $dir="ASC", $page=1, $recordsperpage=20) {
863 global $CFG;
31fefa63 864
c2a96d6b 865 switch ($CFG->dbtype) {
866 case "mysql":
867 $limit = "LIMIT $page,$recordsperpage";
868 break;
869 case "postgres7":
870 $limit = "LIMIT $recordsperpage OFFSET ".($page * $recordsperpage);
871 break;
872 default:
873 $limit = "LIMIT $recordsperpage,$page";
31fefa63 874 }
c2a96d6b 875
9fa49e22 876 return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess
877 FROM {$CFG->prefix}user
878 WHERE username <> 'guest'
879 AND deleted <> '1'
31fefa63 880 ORDER BY $sort $dir $limit");
9fa49e22 881
882}
883
884function get_users_confirmed() {
885 global $CFG;
886 return get_records_sql("SELECT *
887 FROM {$CFG->prefix}user
888 WHERE confirmed = 1
889 AND deleted = 0
890 AND username <> 'guest'
891 AND username <> 'changeme'");
892}
893
894
99988d1a 895function get_users_unconfirmed($cutofftime=2000000000) {
9fa49e22 896 global $CFG;
897 return get_records_sql("SELECT *
898 FROM {$CFG->prefix}user
899 WHERE confirmed = 0
900 AND firstaccess > 0
901 AND firstaccess < '$cutofftime'");
902}
903
904
905function get_users_longtimenosee($cutofftime) {
906 global $CFG;
31fefa63 907
908 $db->debug = true;
9fa49e22 909 return get_records_sql("SELECT u.*
910 FROM {$CFG->prefix}user u,
911 {$CFG->prefix}user_students s
31fefa63 912 WHERE u.lastaccess > '0'
913 AND u.lastaccess < '$cutofftime'
97485d07 914 AND u.id = s.userid
915 GROUP BY u.id");
9fa49e22 916}
917
918
df28d6c5 919
920/// MODULE FUNCTIONS /////////////////////////////////////////////////
921
9fa49e22 922function get_course_mods($courseid) {
923/// Just gets a raw list of all modules in a course
924 global $CFG;
925
926 return get_records_sql("SELECT cm.*, m.name as modname
927 FROM {$CFG->prefix}modules m, {$CFG->prefix}course_modules cm
928 WHERE cm.course = '$courseid'
929 AND cm.deleted = '0'
930 AND cm.module = m.id ");
931}
932
df28d6c5 933function get_coursemodule_from_instance($modulename, $instance, $courseid) {
934/// Given an instance of a module, finds the coursemodule description
935
936 global $CFG;
937
938 return get_record_sql("SELECT cm.*, m.name
939 FROM {$CFG->prefix}course_modules cm, {$CFG->prefix}modules md, {$CFG->prefix}$modulename m
940 WHERE cm.course = '$courseid' AND
941 cm.deleted = '0' AND
942 cm.instance = m.id AND
943 md.name = '$modulename' AND
944 md.id = cm.module AND
945 m.id = '$instance'");
946
947}
948
949function get_all_instances_in_course($modulename, $courseid, $sort="cw.section") {
950/// Returns an array of all the active instances of a particular
951/// module in a given course. Returns false on any errors.
952
953 global $CFG;
954
955 return get_records_sql("SELECT m.*,cw.section,cm.id as coursemodule
956 FROM {$CFG->prefix}course_modules cm, {$CFG->prefix}course_sections cw,
957 {$CFG->prefix}modules md, {$CFG->prefix}$modulename m
958 WHERE cm.course = '$courseid' AND
959 cm.instance = m.id AND
960 cm.deleted = '0' AND
961 cm.section = cw.id AND
962 md.name = '$modulename' AND
963 md.id = cm.module
964 ORDER BY $sort");
965
966}
967
9fa49e22 968
a3fb1c45 969
970
971
9fa49e22 972/// LOG FUNCTIONS /////////////////////////////////////////////////////
973
974
975function add_to_log($course, $module, $action, $url="", $info="") {
976/// Add an entry to the log table. These are "action" focussed rather
977/// than web server hits, and provide a way to easily reconstruct what
978/// any particular student has been doing.
979///
980/// course = the course id
981/// module = forum, journal, resource, course, user etc
982/// action = view, edit, post (often but not always the same as the file.php)
983/// url = the file and parameters used to see the results of the action
984/// info = additional description information
985
31fefa63 986 global $db, $CFG, $USER, $REMOTE_ADDR;
9fa49e22 987
988 if (isset($USER->realuser)) { // Don't log
989 return;
990 }
991
61e96406 992 $userid = empty($USER->id) ? "" : $USER->id;
993
9fa49e22 994 $timenow = time();
995 $info = addslashes($info);
996
31fefa63 997 $result = $db->Execute("INSERT INTO {$CFG->prefix}log (time,
ebc3bd2b 998 userid,
999 course,
1000 ip,
1001 module,
1002 action,
1003 url,
1004 info)
1005 VALUES ('$timenow',
61e96406 1006 '$userid',
ebc3bd2b 1007 '$course',
1008 '$REMOTE_ADDR',
1009 '$module',
1010 '$action',
1011 '$url',
1012 '$info')");
1013
ce78926d 1014 if (!$result and ($CFG->debug > 7)) {
9fa49e22 1015 echo "<P>Error: Could not insert a new entry to the Moodle log</P>"; // Don't throw an error
1016 }
1017}
1018
1019
1020function get_logs($select, $order) {
1021 global $CFG;
1022
1023 return get_records_sql("SELECT l.*, u.firstname, u.lastname, u.picture
1024 FROM {$CFG->prefix}log l,
1025 {$CFG->prefix}user u
1026 $select $order");
1027}
1028
1029function get_logs_usercourse($userid, $courseid, $coursestart) {
1030 global $CFG;
1031
1032 return get_records_sql("SELECT floor((`time` - $coursestart)/86400) as day, count(*) as num
1033 FROM {$CFG->prefix}log
ebc3bd2b 1034 WHERE userid = '$userid'
9fa49e22 1035 AND course = '$courseid'
1036 AND `time` > '$coursestart'
1037 GROUP BY day ");
1038}
1039
1040function get_logs_userday($userid, $courseid, $daystart) {
1041 global $CFG;
1042
1043 return get_records_sql("SELECT floor((`time` - $daystart)/3600) as hour, count(*) as num
1044 FROM {$CFG->prefix}log
ebc3bd2b 1045 WHERE userid = '$userid'
9fa49e22 1046 AND course = '$courseid'
1047 AND `time` > '$daystart'
1048 GROUP BY hour ");
1049}
1050
a3fb1c45 1051/// GENERAL HELPFUL THINGS ///////////////////////////////////
1052
1053function print_object($object) {
1054/// Mostly just for debugging
1055
1056 $array = (array)$object;
1057 foreach ($array as $key => $item) {
1058 echo "$key -> $item <BR>";
1059 }
1060}
1061
1062
9fa49e22 1063
df28d6c5 1064?>