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