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