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