projects / moodle.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
Some blank index.html files to prevent browsing
[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;
914 $user->zoom[$student->course] = $student->zoom;
915 }
f53af941 916 }
18496c59 917 }
df28d6c5 918
f53af941 919 if ($teachers = get_records("user_teachers", "userid", $user->id)) {
920 foreach ($teachers as $teacher) {
921 $user->teacher[$teacher->course] = true;
73047f2f 922 if ($teacher->editall) {
923 $user->teacheredit[$teacher->course] = true;
924 }
f53af941 925 }
18496c59 926 }
df28d6c5 927
f53af941 928 if ($admins = get_records("user_admins", "userid", $user->id)) {
70812e39 929 $user->admin = true;
df28d6c5 930 }
18496c59 931
b86fc0e2 932 if ($displays = get_records("course_display", "userid", $user->id)) {
933 foreach ($displays as $display) {
934 $user->display[$display->course] = $display->display;
935 }
936 }
937
0da33e07 938 if ($groups = get_records("groups_members", "userid", $user->id)) {
3facf28f 939 foreach ($groups as $groupmember) {
940 $courseid = get_field("groups", "courseid", "id", $groupmember->groupid);
941 $user->groupmember[$courseid] = $groupmember->groupid;
f374fb10 942 }
943 }
944
70812e39 945 if ($preferences = get_records('user_preferences', 'userid', $user->id)) {
946 foreach ($preferences as $preference) {
947 $user->preference[$preference->name] = $preference->value;
948 }
949 }
950
18496c59 951 return $user;
df28d6c5 952}
953
18a97fd8 954/**
955* Updates user record to record their last access
956*
957* longdesc
958*
959*/
df28d6c5 960function update_user_in_db() {
df28d6c5 961
962 global $db, $USER, $REMOTE_ADDR, $CFG;
963
964 if (!isset($USER->id))
965 return false;
966
967 $timenow = time();
968 if ($db->Execute("UPDATE {$CFG->prefix}user SET lastIP='$REMOTE_ADDR', lastaccess='$timenow'
969 WHERE id = '$USER->id' ")) {
970 return true;
971 } else {
972 return false;
973 }
974}
975
976
18a97fd8 977/**
978* Does this username and password specify a valid admin user?
979*
980* longdesc
981*
982* @param type description
983*/
df28d6c5 984function adminlogin($username, $md5password) {
df28d6c5 985
986 global $CFG;
987
9fa49e22 988 return record_exists_sql("SELECT u.id
989 FROM {$CFG->prefix}user u,
990 {$CFG->prefix}user_admins a
ebc3bd2b 991 WHERE u.id = a.userid
df28d6c5 992 AND u.username = '$username'
993 AND u.password = '$md5password'");
994}
995
996
18a97fd8 997/**
998* Get the guest user information from the database
999*
1000* longdesc
1001*
1002* @param type description
1003*/
9fa49e22 1004function get_guest() {
1005 return get_user_info_from_db("username", "guest");
1006}
1007
1008
18a97fd8 1009/**
1010* Returns $user object of the main admin user
1011*
1012* longdesc
1013*
1014* @param type description
1015*/
df28d6c5 1016function get_admin () {
df28d6c5 1017
1018 global $CFG;
1019
1020 if ( $admins = get_admins() ) {
1021 foreach ($admins as $admin) {
1022 return $admin; // ie the first one
1023 }
1024 } else {
1025 return false;
1026 }
1027}
1028
18a97fd8 1029/**
1030* Returns list of all admins
1031*
1032* longdesc
1033*
1034* @param type description
1035*/
df28d6c5 1036function get_admins() {
df28d6c5 1037
1038 global $CFG;
1039
ebc3bd2b 1040 return get_records_sql("SELECT u.*
1041 FROM {$CFG->prefix}user u,
1042 {$CFG->prefix}user_admins a
1043 WHERE a.userid = u.id
1044 ORDER BY u.id ASC");
df28d6c5 1045}
1046
18a97fd8 1047/**
1048* Returns list of all creators
1049*
1050* longdesc
1051*
1052* @param type description
1053*/
1924074c 1054function get_creators() {
1924074c 1055
1056 global $CFG;
1057
1058 return get_records_sql("SELECT u.*
1059 FROM {$CFG->prefix}user u,
1060 {$CFG->prefix}user_coursecreators a
1061 WHERE a.userid = u.id
1062 ORDER BY u.id ASC");
1063}
df28d6c5 1064
18a97fd8 1065/**
1066* Returns $user object of the main teacher for a course
1067*
1068* longdesc
1069*
1070* @param type description
1071*/
df28d6c5 1072function get_teacher($courseid) {
df28d6c5 1073
1074 global $CFG;
1075
1076 if ( $teachers = get_course_teachers($courseid, "t.authority ASC")) {
1077 foreach ($teachers as $teacher) {
1078 if ($teacher->authority) {
1079 return $teacher; // the highest authority teacher
1080 }
1081 }
1082 } else {
1083 return false;
1084 }
1085}
1086
18a97fd8 1087/**
1088* Searches logs to find all enrolments since a certain date
1089*
1090* used to print recent activity
1091*
1092* @param type description
1093*/
0a08581d 1094function get_recent_enrolments($courseid, $timestart) {
0a08581d 1095
1096 global $CFG;
1097
1098 return get_records_sql("SELECT u.id, u.firstname, u.lastname
1099 FROM {$CFG->prefix}user u,
1100 {$CFG->prefix}user_students s,
1101 {$CFG->prefix}log l
1102 WHERE l.time > '$timestart'
1103 AND l.course = '$courseid'
1104 AND l.module = 'course'
1105 AND l.action = 'enrol'
1106 AND l.info = u.id
1107 AND u.id = s.userid
64cbb55c 1108 AND s.course = '$courseid'
0a08581d 1109 GROUP BY l.info
1110 ORDER BY l.time ASC");
1111}
1112
18a97fd8 1113/**
1114* Returns list of all students in this course
1115*
18a97fd8 1116* @param type description
1117*/
a328425d 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 = '';
73fc8c1a 1141 $userstudents = '';
1142 $userstudentcolumns = '';
1143 $select = " u.deleted = '0' ";
1144
1145 if ($search) {
1146 $search = " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
1147 }
a328425d 1148
1149 if ($firstinitial) {
1150 $select .= " AND u.firstname $LIKE '$firstinitial%' ";
1151 }
1152
1153 if ($lastinitial) {
1154 $select .= " AND u.lastname $LIKE '$lastinitial%' ";
4969ad74 1155 }
1156
3d35e6b7 1157 if ($group === 0) { /// Need something here to get all students not in a group
1158 return array();
1159
1160 } else if ($group !== NULL) {
0720313b 1161 $groupmembers = ", {$CFG->prefix}groups_members gm ";
1162 $select .= " AND u.id = gm.userid AND gm.groupid = '$group'";
1163 }
1164
73fc8c1a 1165 if ($courseid != 0) {
1166 $userstudents = ", {$CFG->prefix}user_students s ";
1167 $select .= " AND s.course = '$courseid' AND s.userid = u.id";
1168 $userstudentcolumns = ", s.timeaccess as lastaccess";
1169 } else {
5a4aa257 1170 $userstudentcolumns = ", u.lastaccess as lastaccess";
73fc8c1a 1171 }
1172
1173
8e4c9ef7 1174 if ($sort) {
1175 $sort = " ORDER BY $sort ";
1176 }
1177
73fc8c1a 1178 return get_records_sql("SELECT u.id, u.confirmed, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,
1179 u.email, u.city, u.country, u.picture, u.department, u.institution,
1180 u.emailstop, u.lang, u.timezone $userstudentcolumns
1181 FROM {$CFG->prefix}user u $userstudents $groupmembers
1182 WHERE $select $search $sort $dir $limit");
df28d6c5 1183}
1184
a328425d 1185/**
1186* Counts the students in a given course, or a subset of them
1187*
1188* @param type description
1189*/
0720313b 1190function count_course_students($course, $search="", $firstinitial="", $lastinitial="", $group=NULL) {
a328425d 1191
1192 global $CFG;
1193
1194 switch ($CFG->dbtype) {
1195 case "mysql":
1196 $LIKE = "LIKE";
1197 break;
1198 default:
1199 $LIKE = "ILIKE";
1200 }
1201
73fc8c1a 1202
0720313b 1203 $groupmembers = "";
73fc8c1a 1204 $userstudents = "";
1205
1206 $select = " u.deleted = '0'";
a328425d 1207
1208 if ($search) {
1209 $select .= " AND u.firstname $LIKE '%$search%' OR u.lastname $LIKE '%$search%'";
1210 }
1211 if ($firstinitial) {
1212 $select .= " AND u.firstname $LIKE '$firstinitial%'";
1213 }
1214 if ($lastinitial) {
1215 $select .= " AND u.lastname $LIKE '$lastinitial%'";
1216 }
3d35e6b7 1217
1218 if ($group === 0) { /// Need something here to get all students not in a group
1219 return 0;
1220
1221 } else if ($group !== NULL) {
0720313b 1222 $groupmembers = ", {$CFG->prefix}groups_members gm ";
1223 $select .= " AND u.id = gm.userid AND gm.groupid = '$group'";
1224 }
a328425d 1225
73fc8c1a 1226 if ($course->id != 0) {
1227 $userstudents = ", {$CFG->prefix}user_students s ";
1228 $select .= " AND s.course = '$course->id' AND s.userid = u.id";
1229 }
a328425d 1230
73fc8c1a 1231 return count_records_sql("SELECT COUNT(*)
1232 FROM {$CFG->prefix}user u $userstudents $groupmembers
1233 WHERE $select");
a328425d 1234}
1235
1236
18a97fd8 1237/**
1238* Returns list of all teachers in this course
1239*
18a97fd8 1240* @param type description
1241*/
2d0b30a0 1242function get_course_teachers($courseid, $sort="t.authority ASC") {
df28d6c5 1243
1244 global $CFG;
1245
4d744a22 1246 return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,
2e39d2f8 1247 u.email, u.city, u.country, u.lastlogin, u.picture, u.lang, u.timezone,
86fb5ed5 1248 u.emailstop, t.authority,t.role,t.editall,t.timeaccess as lastaccess
688d06f4 1249 FROM {$CFG->prefix}user u,
1250 {$CFG->prefix}user_teachers t
2d0b30a0 1251 WHERE t.course = '$courseid' AND t.userid = u.id AND u.deleted = '0'
df28d6c5 1252 ORDER BY $sort");
1253}
1254
18a97fd8 1255/**
1256* Returns all the users of a course: students and teachers
1257*
1258* If the "course" is actually the site, then return all site users.
1259*
1260* @param type description
1261*/
4d744a22 1262function get_course_users($courseid, $sort="timeaccess DESC") {
353d0338 1263
1264 $site = get_site();
1265
1266 if ($courseid == $site->id) {
1267 return get_site_users($sort);
1268 }
1269
1270 /// Using this method because the single SQL just would not always work!
df28d6c5 1271
0720313b 1272 $users = array();
1273
df28d6c5 1274 $teachers = get_course_teachers($courseid, $sort);
1275 $students = get_course_students($courseid, $sort);
1276
1277 if ($teachers and $students) {
0720313b 1278 foreach ($students as $student) {
1279 $users[$student->id] = $student;
1280 }
1281 foreach ($teachers as $teacher) {
1282 $users[$teacher->id] = $teacher;
1283 }
1284 return $users;
1285
df28d6c5 1286 } else if ($teachers) {
1287 return $teachers;
0720313b 1288
1289 } else if ($students) {
df28d6c5 1290 return $students;
1291 }
1292
0720313b 1293 return $users;
1294
1295 /// This doesn't work ... why not?
353d0338 1296 /// return get_records_sql("SELECT u.* FROM user u, user_students s, user_teachers t
1297 /// WHERE (s.course = '$courseid' AND s.userid = u.id) OR
1298 /// (t.course = '$courseid' AND t.userid = u.id)
1299 /// ORDER BY $sort");
df28d6c5 1300}
1301
0720313b 1302
18a97fd8 1303/**
1304* Returns a list of all active users who are enrolled
1305*
1306* or teaching in courses on this server
1307*
1308* @param type description
1309*/
1a5ab449 1310function get_site_users($sort="u.lastaccess DESC", $select="") {
2d0b30a0 1311
13d7ed45 1312 global $CFG;
353d0338 1313
2d0b30a0 1314
1a5ab449 1315 if ($select) {
1316 $selectinfo = $select;
1317 } else {
1318 $selectinfo = "u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,".
86fb5ed5 1319 "u.email, u.emailstop, u.city, u.country, u.lastaccess, u.lastlogin, u.picture, u.lang, u.timezone";
1a5ab449 1320 }
1a5ab449 1321
4855286e 1322 if (!$users = get_records_sql("SELECT DISTINCT $selectinfo from {$CFG->prefix}user u, {$CFG->prefix}user_students s
1323 WHERE s.userid = u.id ORDER BY $sort")) {
13d7ed45 1324 $users = array();
1a5ab449 1325 }
13d7ed45 1326
4855286e 1327 if ($teachers = get_records_sql("SELECT DISTINCT $selectinfo from {$CFG->prefix}user u, {$CFG->prefix}user_teachers t
1328 WHERE t.userid = u.id ORDER BY $sort")) {
13d7ed45 1329 foreach ($teachers as $teacher) {
1330 $users[$teacher->id] = $teacher;
1331 }
1a5ab449 1332 }
13d7ed45 1333
4855286e 1334 if ($admins = get_records_sql("SELECT DISTINCT $selectinfo from {$CFG->prefix}user u, {$CFG->prefix}user_admins a
1335 WHERE a.userid = u.id ORDER BY $sort")) {
13d7ed45 1336 foreach ($admins as $admin) {
1337 $users[$admin->id] = $admin;
1338 }
1a5ab449 1339 }
13d7ed45 1340
1a5ab449 1341 return $users;
2d0b30a0 1342}
1343
9fa49e22 1344
18a97fd8 1345/**
1346* Returns a subset of users
1347*
1348* longdesc
1349*
1350* @param bookean $get if false then only a count of the records is returned
1351* @param string $search a simple string to search for
1352* @param boolean $confirmed a switch to allow/disallow unconfirmed users
1353* @param array(int) $exceptions a list of IDs to ignore, eg 2,4,5,8,9,10
1354* @param string $sort a SQL snippet for the sorting criteria to use
1355*/
5a741655 1356function get_users($get=true, $search="", $confirmed=false, $exceptions="", $sort="firstname ASC") {
18a97fd8 1357
1358 global $CFG;
1359
1360 switch ($CFG->dbtype) {
1361 case "mysql":
1362 $fullname = " CONCAT(firstname,\" \",lastname) ";
489b2919 1363 $LIKE = "LIKE";
1364 break;
1365 case "postgres7":
3a954ad4 1366 $fullname = " firstname||' '||lastname ";
489b2919 1367 $LIKE = "ILIKE";
18a97fd8 1368 break;
1369 default:
1370 $fullname = " firstname||\" \"||lastname ";
489b2919 1371 $LIKE = "ILIKE";
18a97fd8 1372 }
e384fb7b 1373
1374 if ($search) {
489b2919 1375 $search = " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
e384fb7b 1376 }
1377
5a741655 1378 if ($confirmed) {
1379 $confirmed = " AND confirmed = '1' ";
1380 }
1381
1382 if ($exceptions) {
1383 $exceptions = " AND id NOT IN ($exceptions) ";
1384 }
1385
1386 if ($sort and $get) {
1387 $sort = " ORDER BY $sort ";
1388 } else {
1389 $sort = "";
1390 }
1391
1392 if ($get) {
1393 return get_records_select("user", "username <> 'guest' AND deleted = 0 $search $confirmed $exceptions $sort");
1394 } else {
1395 return count_records_select("user", "username <> 'guest' AND deleted = 0 $search $confirmed $exceptions $sort");
1396 }
9fa49e22 1397}
1398
5a741655 1399
18a97fd8 1400/**
1401* shortdesc
1402*
1403* longdesc
1404*
1405* @param type description
1406*/
c750592a 1407function get_users_listing($sort, $dir="ASC", $page=1, $recordsperpage=20, $search="") {
9fa49e22 1408 global $CFG;
31fefa63 1409
c2a96d6b 1410 switch ($CFG->dbtype) {
1411 case "mysql":
1412 $limit = "LIMIT $page,$recordsperpage";
18a97fd8 1413 $fullname = " CONCAT(firstname,\" \",lastname) ";
489b2919 1414 $LIKE = "LIKE";
c2a96d6b 1415 break;
1416 case "postgres7":
a918234e 1417 $limit = "LIMIT $recordsperpage OFFSET ".($page);
3a954ad4 1418 $fullname = " firstname||' '||lastname ";
489b2919 1419 $LIKE = "ILIKE";
c2a96d6b 1420 break;
1421 default:
1422 $limit = "LIMIT $recordsperpage,$page";
18a97fd8 1423 $fullname = " firstname||\" \"||lastname ";
489b2919 1424 $LIKE = "LIKE";
31fefa63 1425 }
c2a96d6b 1426
c750592a 1427 if ($search) {
489b2919 1428 $search = " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
c750592a 1429 }
1430
ea5d48ee 1431/// warning: will return unconfirmed users
1432 return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess, confirmed
9fa49e22 1433 FROM {$CFG->prefix}user
1434 WHERE username <> 'guest'
ea5d48ee 1435 AND deleted <> 1 $search
31fefa63 1436 ORDER BY $sort $dir $limit");
9fa49e22 1437
1438}
1439
18a97fd8 1440/**
1441* shortdesc
1442*
1443* longdesc
1444*
1445* @param type description
1446*/
9fa49e22 1447function get_users_confirmed() {
1448 global $CFG;
1449 return get_records_sql("SELECT *
1450 FROM {$CFG->prefix}user
1451 WHERE confirmed = 1
1452 AND deleted = 0
1453 AND username <> 'guest'
1454 AND username <> 'changeme'");
1455}
1456
1457
18a97fd8 1458/**
1459* shortdesc
1460*
1461* longdesc
1462*
1463* @param type description
1464*/
99988d1a 1465function get_users_unconfirmed($cutofftime=2000000000) {
9fa49e22 1466 global $CFG;
1467 return get_records_sql("SELECT *
1468 FROM {$CFG->prefix}user
1469 WHERE confirmed = 0
1470 AND firstaccess > 0
1471 AND firstaccess < '$cutofftime'");
1472}
1473
1474
18a97fd8 1475/**
1476* shortdesc
1477*
1478* longdesc
1479*
1480* @param type description
1481*/
9fa49e22 1482function get_users_longtimenosee($cutofftime) {
1483 global $CFG;
937ae59c 1484 return get_records_sql("SELECT DISTINCT *
1485 FROM {$CFG->prefix}user_students
1486 WHERE timeaccess > '0'
1487 AND timeaccess < '$cutofftime' ");
9fa49e22 1488}
1489
f374fb10 1490/**
1491* Returns an array of group objects that the user is a member of
1492* in the given course. If userid isn't specified, then return a
1493* list of all groups in the course.
1494*
1495* @param type description
1496*/
1497function get_groups($courseid, $userid=0) {
1498 global $CFG;
1499
1500 if ($userid) {
2d439c9d 1501 $dbselect = ", {$CFG->prefix}groups_members m";
f374fb10 1502 $userselect = "AND m.groupid = g.id AND m.userid = '$userid'";
2d439c9d 1503 } else {
1504 $dbselect = '';
1505 $userselect = '';
f374fb10 1506 }
1507
1508 return get_records_sql("SELECT DISTINCT g.*
2d439c9d 1509 FROM {$CFG->prefix}groups g $dbselect
f374fb10 1510 WHERE g.courseid = '$courseid' $userselect ");
1511}
1512
1513
1514/**
1515* Returns an array of user objects
1516*
1517* @param type description
1518*/
60b025d1 1519function get_group_users($groupid, $sort="u.lastaccess DESC") {
f374fb10 1520 global $CFG;
1521 return get_records_sql("SELECT DISTINCT u.*
1522 FROM {$CFG->prefix}user u,
0da33e07 1523 {$CFG->prefix}groups_members m
f374fb10 1524 WHERE m.groupid = '$groupid'
75194fff 1525 AND m.userid = u.id
2c4263c4 1526 ORDER BY $sort");
f374fb10 1527}
1528
1529/**
1530* An efficient way of finding all the users who aren't in groups yet
1531*
1532* @param type description
1533*/
1534function get_users_not_in_group($courseid) {
1535 global $CFG;
1536
1537 return array(); /// XXX TO BE DONE
1538}
1539
60b025d1 1540
1541/**
1542* Returns an array of user objects
1543*
1544* @param type description
1545*/
1546function get_group_students($groupid, $sort="u.lastaccess DESC") {
1547 global $CFG;
1548 return get_records_sql("SELECT DISTINCT u.*
1549 FROM {$CFG->prefix}user u,
1550 {$CFG->prefix}groups_members m,
1551 {$CFG->prefix}groups g,
1552 {$CFG->prefix}user_students s
1553 WHERE m.groupid = '$groupid'
1554 AND m.userid = u.id
1555 AND m.groupid = g.id
1556 AND g.courseid = s.course
1557 AND s.userid = u.id
1558 ORDER BY $sort");
1559}
1560
1561
f374fb10 1562/**
1563* Returns the user's group in a particular course
1564*
1565* @param type description
1566*/
1567function user_group($courseid, $userid) {
1568 global $CFG;
1569
1570 return get_record_sql("SELECT g.*
0da33e07 1571 FROM {$CFG->prefix}groups g,
1572 {$CFG->prefix}groups_members m
f374fb10 1573 WHERE g.courseid = '$courseid'
1574 AND g.id = m.groupid
1575 AND m.userid = '$userid'");
1576}
1577
1578
9fa49e22 1579
02ebf404 1580
1581/// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
1582
1583
18a97fd8 1584/**
1585* Returns $course object of the top-level site.
1586*
1587* Returns $course object of the top-level site.
1588*
1589* @param type description
1590*/
02ebf404 1591function get_site () {
02ebf404 1592
1593 if ( $course = get_record("course", "category", 0)) {
1594 return $course;
1595 } else {
1596 return false;
1597 }
1598}
1599
1600
18a97fd8 1601/**
1602* Returns list of courses, for whole site, or category
1603*
1604* Returns list of courses, for whole site, or category
1605*
1606* @param type description
1607*/
8130b77b 1608function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {
02ebf404 1609
8ef9cb56 1610 global $USER, $CFG;
1611
1612 $categoryselect = "";
1613 if ($categoryid != "all") {
14f32609 1614 $categoryselect = "c.category = '$categoryid'";
02ebf404 1615 }
1616
8ef9cb56 1617 $teachertable = "";
f68e5995 1618 $visiblecourses = "";
8ef9cb56 1619 if (!empty($USER)) { // May need to check they are a teacher
3af6e1db 1620 if (!iscreator()) {
f68e5995 1621 $visiblecourses = "AND ((c.visible > 0) OR (t.userid = '$USER->id' AND t.course = c.id))";
8ef9cb56 1622 $teachertable = ", {$CFG->prefix}user_teachers t";
1623 }
f68e5995 1624 } else {
1625 $visiblecourses = "AND c.visible > 0";
8ef9cb56 1626 }
1627
14f32609 1628 if ($categoryselect or $visiblecourses) {
1629 $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
1630 } else {
1631 $selectsql = "{$CFG->prefix}course c $teachertable";
1632 }
1633
8130b77b 1634
0db8b2e2 1635 return get_records_sql("SELECT DISTINCT $fields FROM $selectsql ORDER BY $sort");
8130b77b 1636}
1637
1638
18a97fd8 1639/**
1640* Returns list of courses, for whole site, or category
1641*
1642* Similar to get_courses, but allows paging
1643*
1644* @param type description
1645*/
8130b77b 1646function get_courses_page($categoryid="all", $sort="c.sortorder ASC", $fields="c.*",
1647 &$totalcount, $limitfrom="", $limitnum="") {
8130b77b 1648
1649 global $USER, $CFG;
1650
1651 $categoryselect = "";
1652 if ($categoryid != "all") {
1653 $categoryselect = "c.category = '$categoryid'";
1654 }
1655
1656 $teachertable = "";
8130b77b 1657 $visiblecourses = "";
1658 if (!empty($USER)) { // May need to check they are a teacher
3af6e1db 1659 if (!iscreator()) {
8130b77b 1660 $visiblecourses = "AND ((c.visible > 0) OR (t.userid = '$USER->id' AND t.course = c.id))";
1661 $teachertable = ", {$CFG->prefix}user_teachers t";
8130b77b 1662 }
1663 } else {
1664 $visiblecourses = "AND c.visible > 0";
1665 }
1666
8ef9cb56 1667 if ($limitfrom !== "") {
1668 switch ($CFG->dbtype) {
1669 case "mysql":
1670 $limit = "LIMIT $limitfrom,$limitnum";
1671 break;
1672 case "postgres7":
1673 $limit = "LIMIT $limitnum OFFSET $limitfrom";
1674 break;
1675 default:
1676 $limit = "LIMIT $limitnum,$limitfrom";
02ebf404 1677 }
8ef9cb56 1678 } else {
1679 $limit = "";
02ebf404 1680 }
8ef9cb56 1681
53b67df5 1682 $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
8ef9cb56 1683
53b67df5 1684 $totalcount = count_records_sql("SELECT COUNT(DISTINCT c.id) FROM $selectsql");
8ef9cb56 1685
0db8b2e2 1686 return get_records_sql("SELECT DISTINCT $fields FROM $selectsql ORDER BY $sort $limit");
02ebf404 1687}
1688
1689
18a97fd8 1690/**
1691* shortdesc
1692*
1693* longdesc
1694*
1695* @param type description
1696*/
5b6a1dd3 1697function get_my_courses($userid, $sort="visible DESC,fullname ASC") {
2f3499b7 1698
02ebf404 1699 global $CFG;
1700
2f3499b7 1701 $course = array();
1702
1703 if ($students = get_records("user_students", "userid", $userid, "", "id, course")) {
1704 foreach ($students as $student) {
1705 $course[$student->course] = $student->course;
1706 }
1707 }
1708 if ($teachers = get_records("user_teachers", "userid", $userid, "", "id, course")) {
1709 foreach ($teachers as $teacher) {
1710 $course[$teacher->course] = $teacher->course;
1711 }
1712 }
1713 if (empty($course)) {
1714 return $course;
1715 }
1716
1717 $courseids = implode(',', $course);
1718
1719 return get_records_list("course", "id", $courseids, $sort);
1720
1721// The following is correct but VERY slow with large datasets
1722//
1723// return get_records_sql("SELECT c.*
1724// FROM {$CFG->prefix}course c,
1725// {$CFG->prefix}user_students s,
1726// {$CFG->prefix}user_teachers t
1727// WHERE (s.userid = '$userid' AND s.course = c.id)
1728// OR (t.userid = '$userid' AND t.course = c.id)
1729// GROUP BY c.id
1730// ORDER BY $sort");
02ebf404 1731}
1732
1733
18a97fd8 1734/**
1735* Returns a list of courses that match a search
1736*
1737* Returns a list of courses that match a search
1738*
1739* @param type description
1740*/
a8b56716 1741function get_courses_search($searchterms, $sort="fullname ASC", $page=0, $recordsperpage=50, &$totalcount) {
02ebf404 1742
1743 global $CFG;
1744
1745 switch ($CFG->dbtype) {
1746 case "mysql":
1747 $limit = "LIMIT $page,$recordsperpage";
1748 break;
1749 case "postgres7":
1750 $limit = "LIMIT $recordsperpage OFFSET ".($page * $recordsperpage);
1751 break;
1752 default:
1753 $limit = "LIMIT $recordsperpage,$page";
1754 }
1755
18a97fd8 1756 //to allow case-insensitive search for postgesql
02ebf404 1757 if ($CFG->dbtype == "postgres7") {
a8b56716 1758 $LIKE = "ILIKE";
1759 $NOTLIKE = "NOT ILIKE"; // case-insensitive
1760 $REGEXP = "~*";
1761 $NOTREGEXP = "!~*";
02ebf404 1762 } else {
a8b56716 1763 $LIKE = "LIKE";
1764 $NOTLIKE = "NOT LIKE";
1765 $REGEXP = "REGEXP";
1766 $NOTREGEXP = "NOT REGEXP";
02ebf404 1767 }
1768
1769 $fullnamesearch = "";
1770 $summarysearch = "";
1771
02ebf404 1772 foreach ($searchterms as $searchterm) {
1773 if ($fullnamesearch) {
1774 $fullnamesearch .= " AND ";
1775 }
02ebf404 1776 if ($summarysearch) {
1777 $summarysearch .= " AND ";
1778 }
a8b56716 1779
1780 if (substr($searchterm,0,1) == "+") {
1781 $searchterm = substr($searchterm,1);
1782 $summarysearch .= " summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1783 $fullnamesearch .= " fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1784 } else if (substr($searchterm,0,1) == "-") {
1785 $searchterm = substr($searchterm,1);
1786 $summarysearch .= " summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1787 $fullnamesearch .= " fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1788 } else {
1789 $summarysearch .= " summary $LIKE '%$searchterm%' ";
1790 $fullnamesearch .= " fullname $LIKE '%$searchterm%' ";
1791 }
1792
02ebf404 1793 }
1794
356fea4a 1795 $selectsql = "{$CFG->prefix}course WHERE ($fullnamesearch OR $summarysearch) AND category > '0'";
a8b56716 1796
1797 $totalcount = count_records_sql("SELECT COUNT(*) FROM $selectsql");
02ebf404 1798
a8b56716 1799 $courses = get_records_sql("SELECT * FROM $selectsql ORDER BY $sort $limit");
02ebf404 1800
1801 if ($courses) { /// Remove unavailable courses from the list
1802 foreach ($courses as $key => $course) {
1803 if (!$course->visible) {
1804 if (!isteacher($course->id)) {
1805 unset($courses[$key]);
a8b56716 1806 $totalcount--;
02ebf404 1807 }
1808 }
1809 }
1810 }
1811
1812 return $courses;
1813}
1814
1815
18a97fd8 1816/**
1817* Returns a sorted list of categories
1818*
1819* Returns a sorted list of categories
1820*
1821* @param type description
1822*/
02ebf404 1823function get_categories($parent="none", $sort="sortorder ASC") {
02ebf404 1824
1825 if ($parent == "none") {
1826 $categories = get_records("course_categories", "", "", $sort);
1827 } else {
1828 $categories = get_records("course_categories", "parent", $parent, $sort);
1829 }
1830 if ($categories) { /// Remove unavailable categories from the list
3af6e1db 1831 $creator = iscreator();
02ebf404 1832 foreach ($categories as $key => $category) {
1833 if (!$category->visible) {
3af6e1db 1834 if (!$creator) {
02ebf404 1835 unset($categories[$key]);
1836 }
1837 }
1838 }
1839 }
1840 return $categories;
1841}
1842
1843
18a97fd8 1844/**
1845* reconcile $courseorder with a category object
1846*
1847* Given a category object, this function makes sure the courseorder
1848* variable reflects the real world.
1849*
1850* @param type description
1851*/
02ebf404 1852function fix_course_sortorder($categoryid, $sort="sortorder ASC") {
02ebf404 1853
1854 if (!$courses = get_records("course", "category", "$categoryid", "$sort", "id, sortorder")) {
f1c1b17a 1855 set_field("course_categories", "coursecount", 0, "id", $categoryid);
02ebf404 1856 return true;
1857 }
1858
1859 $count = 0;
1860 $modified = false;
1861
1862 foreach ($courses as $course) {
1863 if ($course->sortorder != $count) {
1864 set_field("course", "sortorder", $count, "id", $course->id);
1865 $modified = true;
1866 }
1867 $count++;
1868 }
1869
1870 if ($modified) {
1871 set_field("course_categories", "timemodified", time(), "id", $categoryid);
1872 }
9936fe81 1873 set_field("course_categories", "coursecount", $count, "id", $categoryid);
02ebf404 1874
1875 return true;
1876}
1877
18a97fd8 1878/**
1879* This function creates a default separated/connected scale
1880*
1881* This function creates a default separated/connected scale
1882* so there's something in the database. The locations of
1883* strings and files is a bit odd, but this is because we
1884* need to maintain backward compatibility with many different
1885* existing language translations and older sites.
1886*
1887* @param type description
1888*/
02ebf404 1889function make_default_scale() {
02ebf404 1890
1891 global $CFG;
1892
1893 $defaultscale = NULL;
1894 $defaultscale->courseid = 0;
1895 $defaultscale->userid = 0;
1896 $defaultscale->name = get_string("separateandconnected");
1897 $defaultscale->scale = get_string("postrating1", "forum").",".
1898 get_string("postrating2", "forum").",".
1899 get_string("postrating3", "forum");
1900 $defaultscale->timemodified = time();
1901
1902 /// Read in the big description from the file. Note this is not
1903 /// HTML (despite the file extension) but Moodle format text.
1904 $parentlang = get_string("parentlang");
1905 if (is_readable("$CFG->dirroot/lang/$CFG->lang/help/forum/ratings.html")) {
1906 $file = file("$CFG->dirroot/lang/$CFG->lang/help/forum/ratings.html");
1907 } else if ($parentlang and is_readable("$CFG->dirroot/lang/$parentlang/help/forum/ratings.html")) {
1908 $file = file("$CFG->dirroot/lang/$parentlang/help/forum/ratings.html");
1909 } else if (is_readable("$CFG->dirroot/lang/en/help/forum/ratings.html")) {
1910 $file = file("$CFG->dirroot/lang/en/help/forum/ratings.html");
1911 } else {
1912 $file = "";
1913 }
1914
1915 $defaultscale->description = addslashes(implode("", $file));
1916
1917 if ($defaultscale->id = insert_record("scale", $defaultscale)) {
94d782eb 1918 execute_sql("UPDATE {$CFG->prefix}forum SET scale = '$defaultscale->id'", false);
02ebf404 1919 }
1920}
1921
18a97fd8 1922/**
1923* Returns a menu of all available scales from the site as well as the given course
1924*
1925* Returns a menu of all available scales from the site as well as the given course
1926*
1927* @param type description
1928*/
02ebf404 1929function get_scales_menu($courseid=0) {
02ebf404 1930
1931 global $CFG;
1932
1933 $sql = "SELECT id, name FROM {$CFG->prefix}scale
1934 WHERE courseid = '0' or courseid = '$courseid'
1935 ORDER BY courseid ASC, name ASC";
1936
1937 if ($scales = get_records_sql_menu("$sql")) {
1938 return $scales;
1939 }
1940
1941 make_default_scale();
1942
1943 return get_records_sql_menu("$sql");
1944}
1945
df28d6c5 1946/// MODULE FUNCTIONS /////////////////////////////////////////////////
1947
18a97fd8 1948/**
1949* Just gets a raw list of all modules in a course
1950*
1951* Just gets a raw list of all modules in a course
1952*
1953* @param type description
1954*/
9fa49e22 1955function get_course_mods($courseid) {
9fa49e22 1956 global $CFG;
1957
7acaa63d 1958 return get_records_sql("SELECT cm.*, m.name as modname
1959 FROM {$CFG->prefix}modules m,
1960 {$CFG->prefix}course_modules cm
9fa49e22 1961 WHERE cm.course = '$courseid'
1962 AND cm.deleted = '0'
1963 AND cm.module = m.id ");
1964}
1965
18a97fd8 1966/**
1967* Given an instance of a module, finds the coursemodule description
1968*
1969* Given an instance of a module, finds the coursemodule description
1970*
1971* @param type description
1972*/
df28d6c5 1973function get_coursemodule_from_instance($modulename, $instance, $courseid) {
df28d6c5 1974
1975 global $CFG;
1976
1977 return get_record_sql("SELECT cm.*, m.name
7acaa63d 1978 FROM {$CFG->prefix}course_modules cm,
1979 {$CFG->prefix}modules md,
1980 {$CFG->prefix}$modulename m
df28d6c5 1981 WHERE cm.course = '$courseid' AND
1982 cm.deleted = '0' AND
1983 cm.instance = m.id AND
1984 md.name = '$modulename' AND
1985 md.id = cm.module AND
1986 m.id = '$instance'");
1987
1988}
1989
18a97fd8 1990/**
1991* Returns an array of all the active instances of a particular module in a given course, sorted in the order they are defined
1992*
1993* Returns an array of all the active instances of a particular
1994* module in a given course, sorted in the order they are defined
1995* in the course. Returns false on any errors.
1996*
1997* @param string $modulename the name of the module to get instances for
1998* @param object(course) $course this depends on an accurate $course->modinfo
1999*/
cccb016a 2000function get_all_instances_in_course($modulename, $course) {
df28d6c5 2001
2002 global $CFG;
2003
cccb016a 2004 if (!$modinfo = unserialize($course->modinfo)) {
2005 return array();
1acfbce5 2006 }
2007
404afe6b 2008 if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode
7acaa63d 2009 FROM {$CFG->prefix}course_modules cm,
2010 {$CFG->prefix}course_sections cw,
2011 {$CFG->prefix}modules md,
2012 {$CFG->prefix}$modulename m
cccb016a 2013 WHERE cm.course = '$course->id' AND
df28d6c5 2014 cm.instance = m.id AND
2015 cm.deleted = '0' AND
2016 cm.section = cw.id AND
2017 md.name = '$modulename' AND
cccb016a 2018 md.id = cm.module")) {
2019 return array();
2020 }
2021
2022 // Hide non-visible instances from students
2023 if (isteacher($course->id)) {
2024 $invisible = -1;
2025 } else {
2026 $invisible = 0;
2027 }
2028
2029 foreach ($modinfo as $mod) {
2030 if ($mod->mod == $modulename and $mod->visible > $invisible) {
7f12f9cd 2031 $instance = $rawmods[$mod->cm];
2032 if (!empty($mod->extra)) {
2033 $instance->extra = $mod->extra;
2034 }
2035 $outputarray[] = $instance;
cccb016a 2036 }
2037 }
2038
2039 return $outputarray;
df28d6c5 2040
2041}
2042
9fa49e22 2043
18a97fd8 2044/**
2045* determine whether a module instance is visible within a course
2046*
2047* Given a valid module object with info about the id and course,
2048* and the module's type (eg "forum") returns whether the object
2049* is visible or not
2050*
2051* @param type description
2052*/
580f2fbc 2053function instance_is_visible($moduletype, $module) {
580f2fbc 2054
2055 global $CFG;
2056
86e6076b 2057 if ($records = get_records_sql("SELECT cm.instance, cm.visible
580f2fbc 2058 FROM {$CFG->prefix}course_modules cm,
580f2fbc 2059 {$CFG->prefix}modules m
2060 WHERE cm.course = '$module->course' AND
2061 cm.module = m.id AND
2062 m.name = '$moduletype' AND
86e6076b 2063 cm.instance = '$module->id'")) {
580f2fbc 2064
2065 foreach ($records as $record) { // there should only be one - use the first one
2066 return $record->visible;
2067 }
2068 }
2069
2070 return true; // visible by default!
2071}
2072
a3fb1c45 2073
2074
2075
9fa49e22 2076/// LOG FUNCTIONS /////////////////////////////////////////////////////
2077
2078
18a97fd8 2079/**
2080* Add an entry to the log table.
2081*
2082* Add an entry to the log table. These are "action" focussed rather
2083* than web server hits, and provide a way to easily reconstruct what
2084* any particular student has been doing.
2085*
69d79bc3 2086* @param int $course the course id
2087* @param string $module the module name - e.g. forum, journal, resource, course, user etc
2088* @param string $action view, edit, post (often but not always the same as the file.php)
2089* @param string $url the file and parameters used to see the results of the action
2090* @param string $info additional description information
2091* @param string $cm the course_module->id if there is one
2092* @param string $user if log regards $user other than $USER
18a97fd8 2093*/
69d79bc3 2094function add_to_log($courseid, $module, $action, $url="", $info="", $cm=0, $user=0) {
9fa49e22 2095
31fefa63 2096 global $db, $CFG, $USER, $REMOTE_ADDR;
9fa49e22 2097
3d94772d 2098 if ($user) {
2099 $userid = $user;
2100 } else {
2101 if (isset($USER->realuser)) { // Don't log
2102 return;
2103 }
2104 $userid = empty($USER->id) ? "" : $USER->id;
9fa49e22 2105 }
2106
2107 $timenow = time();
2108 $info = addslashes($info);
2109
69d79bc3 2110 $result = $db->Execute("INSERT INTO {$CFG->prefix}log (time, userid, course, ip, module, cmid, action, url, info)
2111 VALUES ('$timenow', '$userid', '$courseid', '$REMOTE_ADDR', '$module', '$cm', '$action', '$url', '$info')");
ebc3bd2b 2112
ce78926d 2113 if (!$result and ($CFG->debug > 7)) {
9fa49e22 2114 echo "<P>Error: Could not insert a new entry to the Moodle log</P>"; // Don't throw an error
2115 }
3d94772d 2116 if (!$user) {
2117 if (isstudent($courseid)) {
2118 $db->Execute("UPDATE {$CFG->prefix}user_students SET timeaccess = '$timenow' ".
2119 "WHERE course = '$courseid' AND userid = '$userid'");
2120 }
4d744a22 2121
3d94772d 2122 if (isteacher($courseid, false, false)) {
2123 $db->Execute("UPDATE {$CFG->prefix}user_teachers SET timeaccess = '$timenow' ".
2124 "WHERE course = '$courseid' AND userid = '$userid'");
2125 }
2126 }
9fa49e22 2127}
2128
2129
18a97fd8 2130/**
2131* select all log records based on SQL criteria
2132*
2133* select all log records based on SQL criteria
2134*
2135* @param string $select SQL select criteria
2136* @param string $order SQL order by clause to sort the records returned
2137*/
519d369f 2138function get_logs($select, $order="l.time DESC", $limitfrom="", $limitnum="", &$totalcount) {
9fa49e22 2139 global $CFG;
2140
519d369f 2141 if ($limitfrom !== "") {
2142 switch ($CFG->dbtype) {
2143 case "mysql":
2144 $limit = "LIMIT $limitfrom,$limitnum";
2145 break;
2146 case "postgres7":
2147 $limit = "LIMIT $limitnum OFFSET $limitfrom";
2148 break;
2149 default:
2150 $limit = "LIMIT $limitnum,$limitfrom";
2151 }
2152 } else {
2153 $limit = "";
2154 }
2155
2156 if ($order) {
2157 $order = "ORDER BY $order";
2158 }
2159
2160 $selectsql = "{$CFG->prefix}log l, {$CFG->prefix}user u WHERE $select";
2161
2162 $totalcount = count_records_sql("SELECT COUNT(*) FROM $selectsql");
2163
9fa49e22 2164 return get_records_sql("SELECT l.*, u.firstname, u.lastname, u.picture
519d369f 2165 FROM $selectsql $order $limit");
9fa49e22 2166}
2167
519d369f 2168
18a97fd8 2169/**
2170* select all log records for a given course and user
2171*
2172* select all log records for a given course and user
2173*
2174* @param type description
2175*/
9fa49e22 2176function get_logs_usercourse($userid, $courseid, $coursestart) {
2177 global $CFG;
2178
da0c90c3 2179 if ($courseid) {
2180 $courseselect = " AND course = '$courseid' ";
2181 }
2182
9fa49e22 2183 return get_records_sql("SELECT floor((`time` - $coursestart)/86400) as day, count(*) as num
2184 FROM {$CFG->prefix}log
ebc3bd2b 2185 WHERE userid = '$userid'
da0c90c3 2186 AND `time` > '$coursestart' $courseselect
9fa49e22 2187 GROUP BY day ");
2188}
2189
18a97fd8 2190/**
2191* select all log records for a given course, user, and day
2192*
2193* select all log records for a given course, user, and day
2194*
2195* @param type description
2196*/
9fa49e22 2197function get_logs_userday($userid, $courseid, $daystart) {
2198 global $CFG;
2199
7e4a6488 2200 if ($courseid) {
2201 $courseselect = " AND course = '$courseid' ";
2202 }
2203
9fa49e22 2204 return get_records_sql("SELECT floor((`time` - $daystart)/3600) as hour, count(*) as num
2205 FROM {$CFG->prefix}log
ebc3bd2b 2206 WHERE userid = '$userid'
7e4a6488 2207 AND `time` > '$daystart' $courseselect
9fa49e22 2208 GROUP BY hour ");
2209}
2210
a3fb1c45 2211/// GENERAL HELPFUL THINGS ///////////////////////////////////
2212
18a97fd8 2213/**
2214* dump a given object's information in a PRE block
2215*
2216* dump a given object's information in a PRE block
2217* Mostly just for debugging
2218*
2219* @param type description
2220*/
a3fb1c45 2221function print_object($object) {
a3fb1c45 2222
2b051f1c 2223 echo "<PRE>";
2224 print_r($object);
2225 echo "</PRE>";
a3fb1c45 2226}
2227
2228
9fa49e22 2229
9d5b689c 2230// vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140:
df28d6c5 2231?>
Moodle official production repository