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