projects / moodle.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | blame (incremental) | history | HEAD
Some tweeks again
[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"
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)) {
3f1d9b22 768 $ddd[$column->name] = $column->default_value;
b3fa6684 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)) {
3f1d9b22 774 $ddd[$column->name] = $column->default_value;
b3fa6684 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";
3f1d9b22 799 $insvalues .= "'$value'";
3c72e2f9 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*/
a328425d 1115function get_course_students($courseid, $sort="s.timeaccess", $dir="", $page=0, $recordsperpage=99999,
1116 $firstinitial="", $lastinitial="") {
df28d6c5 1117
1118 global $CFG;
1119
4969ad74 1120 switch ($CFG->dbtype) {
1121 case "mysql":
1122 $limit = "LIMIT $page,$recordsperpage";
a328425d 1123 $LIKE = "LIKE";
4969ad74 1124 break;
1125 case "postgres7":
1126 $limit = "LIMIT $recordsperpage OFFSET ".($page);
a328425d 1127 $LIKE = "ILIKE";
4969ad74 1128 break;
1129 default:
1130 $limit = "LIMIT $recordsperpage,$page";
a328425d 1131 $LIKE = "ILIKE";
1132 }
1133
1134 $select = "s.course = '$courseid' AND s.userid = u.id AND u.deleted = '0' ";
1135
1136 if ($firstinitial) {
1137 $select .= " AND u.firstname $LIKE '$firstinitial%' ";
1138 }
1139
1140 if ($lastinitial) {
1141 $select .= " AND u.lastname $LIKE '$lastinitial%' ";
4969ad74 1142 }
1143
688d06f4 1144 return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,
4d744a22 1145 u.email, u.city, u.country, u.lastlogin, u.picture, s.timeaccess as lastaccess
9d6f5d42 1146 FROM {$CFG->prefix}user u,
1147 {$CFG->prefix}user_students s
a328425d 1148 WHERE $select
4969ad74 1149 ORDER BY $sort $dir $limit");
df28d6c5 1150}
1151
a328425d 1152/**
1153* Counts the students in a given course, or a subset of them
1154*
1155* @param type description
1156*/
1157function count_course_students($course, $search="", $firstinitial="", $lastinitial="") {
1158
1159 global $CFG;
1160
1161 switch ($CFG->dbtype) {
1162 case "mysql":
1163 $LIKE = "LIKE";
1164 break;
1165 default:
1166 $LIKE = "ILIKE";
1167 }
1168
1169 $select = "s.course = '$course->id' AND s.userid = u.id AND u.deleted = '0'";
1170
1171 if ($search) {
1172 $select .= " AND u.firstname $LIKE '%$search%' OR u.lastname $LIKE '%$search%'";
1173 }
1174 if ($firstinitial) {
1175 $select .= " AND u.firstname $LIKE '$firstinitial%'";
1176 }
1177 if ($lastinitial) {
1178 $select .= " AND u.lastname $LIKE '$lastinitial%'";
1179 }
1180
1181 return count_records_sql("SELECT COUNT(*) FROM {$CFG->prefix}user u,{$CFG->prefix}user_students s WHERE $select");
1182
1183}
1184
1185
18a97fd8 1186/**
1187* Returns list of all teachers in this course
1188*
18a97fd8 1189* @param type description
1190*/
2d0b30a0 1191function get_course_teachers($courseid, $sort="t.authority ASC") {
df28d6c5 1192
1193 global $CFG;
1194
4d744a22 1195 return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,
1196 u.email, u.city, u.country, u.lastlogin, u.picture,
1197 t.authority,t.role,t.editall,t.timeaccess as lastaccess
688d06f4 1198 FROM {$CFG->prefix}user u,
1199 {$CFG->prefix}user_teachers t
2d0b30a0 1200 WHERE t.course = '$courseid' AND t.userid = u.id AND u.deleted = '0'
df28d6c5 1201 ORDER BY $sort");
1202}
1203
18a97fd8 1204/**
1205* Returns all the users of a course: students and teachers
1206*
1207* If the "course" is actually the site, then return all site users.
1208*
1209* @param type description
1210*/
4d744a22 1211function get_course_users($courseid, $sort="timeaccess DESC") {
353d0338 1212
1213 $site = get_site();
1214
1215 if ($courseid == $site->id) {
1216 return get_site_users($sort);
1217 }
1218
1219 /// Using this method because the single SQL just would not always work!
df28d6c5 1220
1221 $teachers = get_course_teachers($courseid, $sort);
1222 $students = get_course_students($courseid, $sort);
1223
1224 if ($teachers and $students) {
1225 return array_merge($teachers, $students);
1226 } else if ($teachers) {
1227 return $teachers;
1228 } else {
1229 return $students;
1230 }
1231
353d0338 1232 /// Why wouldn't this work?
1233 /// return get_records_sql("SELECT u.* FROM user u, user_students s, user_teachers t
1234 /// WHERE (s.course = '$courseid' AND s.userid = u.id) OR
1235 /// (t.course = '$courseid' AND t.userid = u.id)
1236 /// ORDER BY $sort");
df28d6c5 1237}
1238
18a97fd8 1239/**
1240* Returns a list of all active users who are enrolled
1241*
1242* or teaching in courses on this server
1243*
1244* @param type description
1245*/
1a5ab449 1246function get_site_users($sort="u.lastaccess DESC", $select="") {
2d0b30a0 1247
353d0338 1248 global $CFG, $db;
1249
2d0b30a0 1250
1a5ab449 1251 if ($select) {
1252 $selectinfo = $select;
1253 } else {
1254 $selectinfo = "u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,".
1255 "u.email, u.city, u.country, u.lastaccess, u.lastlogin, u.picture";
1256 }
1257
1258
1259 if (!$students = get_records_sql("SELECT $selectinfo from {$CFG->prefix}user u, {$CFG->prefix}user_students s
1260 WHERE s.userid = u.id GROUP BY u.id ORDER BY $sort")) {
1261 $students = array();
1262 }
1263 if (!$teachers = get_records_sql("SELECT $selectinfo from {$CFG->prefix}user u, {$CFG->prefix}user_teachers t
1264 WHERE t.userid = u.id GROUP BY u.id ORDER BY $sort")) {
1265 $teachers = array();
1266 }
1267 if (!$admins = get_records_sql("SELECT $selectinfo from {$CFG->prefix}user u, {$CFG->prefix}user_admins a
1268 WHERE a.userid = u.id GROUP BY u.id ORDER BY $sort")) {
1269 $admins = array();
1270 }
1271 $users = array_merge($teachers, $students);
1272 $users = array_merge($users, $admins);
1273 return $users;
2d0b30a0 1274}
1275
9fa49e22 1276
18a97fd8 1277/**
1278* Returns a subset of users
1279*
1280* longdesc
1281*
1282* @param bookean $get if false then only a count of the records is returned
1283* @param string $search a simple string to search for
1284* @param boolean $confirmed a switch to allow/disallow unconfirmed users
1285* @param array(int) $exceptions a list of IDs to ignore, eg 2,4,5,8,9,10
1286* @param string $sort a SQL snippet for the sorting criteria to use
1287*/
5a741655 1288function get_users($get=true, $search="", $confirmed=false, $exceptions="", $sort="firstname ASC") {
18a97fd8 1289
1290 global $CFG;
1291
1292 switch ($CFG->dbtype) {
1293 case "mysql":
1294 $fullname = " CONCAT(firstname,\" \",lastname) ";
489b2919 1295 $LIKE = "LIKE";
1296 break;
1297 case "postgres7":
1298 $fullname = " firstname||\" \"||lastname ";
1299 $LIKE = "ILIKE";
18a97fd8 1300 break;
1301 default:
1302 $fullname = " firstname||\" \"||lastname ";
489b2919 1303 $LIKE = "ILIKE";
18a97fd8 1304 }
e384fb7b 1305
1306 if ($search) {
489b2919 1307 $search = " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
e384fb7b 1308 }
1309
5a741655 1310 if ($confirmed) {
1311 $confirmed = " AND confirmed = '1' ";
1312 }
1313
1314 if ($exceptions) {
1315 $exceptions = " AND id NOT IN ($exceptions) ";
1316 }
1317
1318 if ($sort and $get) {
1319 $sort = " ORDER BY $sort ";
1320 } else {
1321 $sort = "";
1322 }
1323
1324 if ($get) {
1325 return get_records_select("user", "username <> 'guest' AND deleted = 0 $search $confirmed $exceptions $sort");
1326 } else {
1327 return count_records_select("user", "username <> 'guest' AND deleted = 0 $search $confirmed $exceptions $sort");
1328 }
9fa49e22 1329}
1330
5a741655 1331
18a97fd8 1332/**
1333* shortdesc
1334*
1335* longdesc
1336*
1337* @param type description
1338*/
c750592a 1339function get_users_listing($sort, $dir="ASC", $page=1, $recordsperpage=20, $search="") {
9fa49e22 1340 global $CFG;
31fefa63 1341
c2a96d6b 1342 switch ($CFG->dbtype) {
1343 case "mysql":
1344 $limit = "LIMIT $page,$recordsperpage";
18a97fd8 1345 $fullname = " CONCAT(firstname,\" \",lastname) ";
489b2919 1346 $LIKE = "LIKE";
c2a96d6b 1347 break;
1348 case "postgres7":
a918234e 1349 $limit = "LIMIT $recordsperpage OFFSET ".($page);
18a97fd8 1350 $fullname = " firstname||\" \"||lastname ";
489b2919 1351 $LIKE = "ILIKE";
c2a96d6b 1352 break;
1353 default:
1354 $limit = "LIMIT $recordsperpage,$page";
18a97fd8 1355 $fullname = " firstname||\" \"||lastname ";
489b2919 1356 $LIKE = "LIKE";
31fefa63 1357 }
c2a96d6b 1358
c750592a 1359 if ($search) {
489b2919 1360 $search = " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
c750592a 1361 }
1362
9fa49e22 1363 return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess
1364 FROM {$CFG->prefix}user
1365 WHERE username <> 'guest'
160e8115 1366 AND deleted <> 1 and confirmed = 1 $search
31fefa63 1367 ORDER BY $sort $dir $limit");
9fa49e22 1368
1369}
1370
18a97fd8 1371/**
1372* shortdesc
1373*
1374* longdesc
1375*
1376* @param type description
1377*/
9fa49e22 1378function get_users_confirmed() {
1379 global $CFG;
1380 return get_records_sql("SELECT *
1381 FROM {$CFG->prefix}user
1382 WHERE confirmed = 1
1383 AND deleted = 0
1384 AND username <> 'guest'
1385 AND username <> 'changeme'");
1386}
1387
1388
18a97fd8 1389/**
1390* shortdesc
1391*
1392* longdesc
1393*
1394* @param type description
1395*/
99988d1a 1396function get_users_unconfirmed($cutofftime=2000000000) {
9fa49e22 1397 global $CFG;
1398 return get_records_sql("SELECT *
1399 FROM {$CFG->prefix}user
1400 WHERE confirmed = 0
1401 AND firstaccess > 0
1402 AND firstaccess < '$cutofftime'");
1403}
1404
1405
18a97fd8 1406/**
1407* shortdesc
1408*
1409* longdesc
1410*
1411* @param type description
1412*/
9fa49e22 1413function get_users_longtimenosee($cutofftime) {
1414 global $CFG;
937ae59c 1415 return get_records_sql("SELECT DISTINCT *
1416 FROM {$CFG->prefix}user_students
1417 WHERE timeaccess > '0'
1418 AND timeaccess < '$cutofftime' ");
9fa49e22 1419}
1420
1421
02ebf404 1422
1423/// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
1424
1425
18a97fd8 1426/**
1427* Returns $course object of the top-level site.
1428*
1429* Returns $course object of the top-level site.
1430*
1431* @param type description
1432*/
02ebf404 1433function get_site () {
02ebf404 1434
1435 if ( $course = get_record("course", "category", 0)) {
1436 return $course;
1437 } else {
1438 return false;
1439 }
1440}
1441
1442
18a97fd8 1443/**
1444* Returns list of courses, for whole site, or category
1445*
1446* Returns list of courses, for whole site, or category
1447*
1448* @param type description
1449*/
8130b77b 1450function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {
02ebf404 1451
8ef9cb56 1452 global $USER, $CFG;
1453
1454 $categoryselect = "";
1455 if ($categoryid != "all") {
7a7ac154 1456 $categoryselect = "WHERE c.category = '$categoryid'";
02ebf404 1457 }
1458
8ef9cb56 1459 $teachertable = "";
f68e5995 1460 $visiblecourses = "";
8ef9cb56 1461 if (!empty($USER)) { // May need to check they are a teacher
3af6e1db 1462 if (!iscreator()) {
f68e5995 1463 $visiblecourses = "AND ((c.visible > 0) OR (t.userid = '$USER->id' AND t.course = c.id))";
8ef9cb56 1464 $teachertable = ", {$CFG->prefix}user_teachers t";
1465 }
f68e5995 1466 } else {
1467 $visiblecourses = "AND c.visible > 0";
8ef9cb56 1468 }
1469
7a7ac154 1470 $selectsql = "{$CFG->prefix}course c $teachertable $categoryselect $visiblecourses";
8130b77b 1471
0db8b2e2 1472 return get_records_sql("SELECT DISTINCT $fields FROM $selectsql ORDER BY $sort");
8130b77b 1473}
1474
1475
18a97fd8 1476/**
1477* Returns list of courses, for whole site, or category
1478*
1479* Similar to get_courses, but allows paging
1480*
1481* @param type description
1482*/
8130b77b 1483function get_courses_page($categoryid="all", $sort="c.sortorder ASC", $fields="c.*",
1484 &$totalcount, $limitfrom="", $limitnum="") {
8130b77b 1485
1486 global $USER, $CFG;
1487
1488 $categoryselect = "";
1489 if ($categoryid != "all") {
1490 $categoryselect = "c.category = '$categoryid'";
1491 }
1492
1493 $teachertable = "";
8130b77b 1494 $visiblecourses = "";
1495 if (!empty($USER)) { // May need to check they are a teacher
3af6e1db 1496 if (!iscreator()) {
8130b77b 1497 $visiblecourses = "AND ((c.visible > 0) OR (t.userid = '$USER->id' AND t.course = c.id))";
1498 $teachertable = ", {$CFG->prefix}user_teachers t";
8130b77b 1499 }
1500 } else {
1501 $visiblecourses = "AND c.visible > 0";
1502 }
1503
8ef9cb56 1504 if ($limitfrom !== "") {
1505 switch ($CFG->dbtype) {
1506 case "mysql":
1507 $limit = "LIMIT $limitfrom,$limitnum";
1508 break;
1509 case "postgres7":
1510 $limit = "LIMIT $limitnum OFFSET $limitfrom";
1511 break;
1512 default:
1513 $limit = "LIMIT $limitnum,$limitfrom";
02ebf404 1514 }
8ef9cb56 1515 } else {
1516 $limit = "";
02ebf404 1517 }
8ef9cb56 1518
53b67df5 1519 $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
8ef9cb56 1520
53b67df5 1521 $totalcount = count_records_sql("SELECT COUNT(DISTINCT c.id) FROM $selectsql");
8ef9cb56 1522
0db8b2e2 1523 return get_records_sql("SELECT DISTINCT $fields FROM $selectsql ORDER BY $sort $limit");
02ebf404 1524}
1525
1526
18a97fd8 1527/**
1528* shortdesc
1529*
1530* longdesc
1531*
1532* @param type description
1533*/
5b6a1dd3 1534function get_my_courses($userid, $sort="visible DESC,fullname ASC") {
2f3499b7 1535
02ebf404 1536 global $CFG;
1537
2f3499b7 1538 $course = array();
1539
1540 if ($students = get_records("user_students", "userid", $userid, "", "id, course")) {
1541 foreach ($students as $student) {
1542 $course[$student->course] = $student->course;
1543 }
1544 }
1545 if ($teachers = get_records("user_teachers", "userid", $userid, "", "id, course")) {
1546 foreach ($teachers as $teacher) {
1547 $course[$teacher->course] = $teacher->course;
1548 }
1549 }
1550 if (empty($course)) {
1551 return $course;
1552 }
1553
1554 $courseids = implode(',', $course);
1555
1556 return get_records_list("course", "id", $courseids, $sort);
1557
1558// The following is correct but VERY slow with large datasets
1559//
1560// return get_records_sql("SELECT c.*
1561// FROM {$CFG->prefix}course c,
1562// {$CFG->prefix}user_students s,
1563// {$CFG->prefix}user_teachers t
1564// WHERE (s.userid = '$userid' AND s.course = c.id)
1565// OR (t.userid = '$userid' AND t.course = c.id)
1566// GROUP BY c.id
1567// ORDER BY $sort");
02ebf404 1568}
1569
1570
18a97fd8 1571/**
1572* Returns a list of courses that match a search
1573*
1574* Returns a list of courses that match a search
1575*
1576* @param type description
1577*/
a8b56716 1578function get_courses_search($searchterms, $sort="fullname ASC", $page=0, $recordsperpage=50, &$totalcount) {
02ebf404 1579
1580 global $CFG;
1581
1582 switch ($CFG->dbtype) {
1583 case "mysql":
1584 $limit = "LIMIT $page,$recordsperpage";
1585 break;
1586 case "postgres7":
1587 $limit = "LIMIT $recordsperpage OFFSET ".($page * $recordsperpage);
1588 break;
1589 default:
1590 $limit = "LIMIT $recordsperpage,$page";
1591 }
1592
18a97fd8 1593 //to allow case-insensitive search for postgesql
02ebf404 1594 if ($CFG->dbtype == "postgres7") {
a8b56716 1595 $LIKE = "ILIKE";
1596 $NOTLIKE = "NOT ILIKE"; // case-insensitive
1597 $REGEXP = "~*";
1598 $NOTREGEXP = "!~*";
02ebf404 1599 } else {
a8b56716 1600 $LIKE = "LIKE";
1601 $NOTLIKE = "NOT LIKE";
1602 $REGEXP = "REGEXP";
1603 $NOTREGEXP = "NOT REGEXP";
02ebf404 1604 }
1605
1606 $fullnamesearch = "";
1607 $summarysearch = "";
1608
02ebf404 1609 foreach ($searchterms as $searchterm) {
1610 if ($fullnamesearch) {
1611 $fullnamesearch .= " AND ";
1612 }
02ebf404 1613 if ($summarysearch) {
1614 $summarysearch .= " AND ";
1615 }
a8b56716 1616
1617 if (substr($searchterm,0,1) == "+") {
1618 $searchterm = substr($searchterm,1);
1619 $summarysearch .= " summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1620 $fullnamesearch .= " fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1621 } else if (substr($searchterm,0,1) == "-") {
1622 $searchterm = substr($searchterm,1);
1623 $summarysearch .= " summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1624 $fullnamesearch .= " fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1625 } else {
1626 $summarysearch .= " summary $LIKE '%$searchterm%' ";
1627 $fullnamesearch .= " fullname $LIKE '%$searchterm%' ";
1628 }
1629
02ebf404 1630 }
1631
356fea4a 1632 $selectsql = "{$CFG->prefix}course WHERE ($fullnamesearch OR $summarysearch) AND category > '0'";
a8b56716 1633
1634 $totalcount = count_records_sql("SELECT COUNT(*) FROM $selectsql");
02ebf404 1635
a8b56716 1636 $courses = get_records_sql("SELECT * FROM $selectsql ORDER BY $sort $limit");
02ebf404 1637
1638 if ($courses) { /// Remove unavailable courses from the list
1639 foreach ($courses as $key => $course) {
1640 if (!$course->visible) {
1641 if (!isteacher($course->id)) {
1642 unset($courses[$key]);
a8b56716 1643 $totalcount--;
02ebf404 1644 }
1645 }
1646 }
1647 }
1648
1649 return $courses;
1650}
1651
1652
18a97fd8 1653/**
1654* Returns a sorted list of categories
1655*
1656* Returns a sorted list of categories
1657*
1658* @param type description
1659*/
02ebf404 1660function get_categories($parent="none", $sort="sortorder ASC") {
02ebf404 1661
1662 if ($parent == "none") {
1663 $categories = get_records("course_categories", "", "", $sort);
1664 } else {
1665 $categories = get_records("course_categories", "parent", $parent, $sort);
1666 }
1667 if ($categories) { /// Remove unavailable categories from the list
3af6e1db 1668 $creator = iscreator();
02ebf404 1669 foreach ($categories as $key => $category) {
1670 if (!$category->visible) {
3af6e1db 1671 if (!$creator) {
02ebf404 1672 unset($categories[$key]);
1673 }
1674 }
1675 }
1676 }
1677 return $categories;
1678}
1679
1680
18a97fd8 1681/**
1682* reconcile $courseorder with a category object
1683*
1684* Given a category object, this function makes sure the courseorder
1685* variable reflects the real world.
1686*
1687* @param type description
1688*/
02ebf404 1689function fix_course_sortorder($categoryid, $sort="sortorder ASC") {
02ebf404 1690
1691 if (!$courses = get_records("course", "category", "$categoryid", "$sort", "id, sortorder")) {
f1c1b17a 1692 set_field("course_categories", "coursecount", 0, "id", $categoryid);
02ebf404 1693 return true;
1694 }
1695
1696 $count = 0;
1697 $modified = false;
1698
1699 foreach ($courses as $course) {
1700 if ($course->sortorder != $count) {
1701 set_field("course", "sortorder", $count, "id", $course->id);
1702 $modified = true;
1703 }
1704 $count++;
1705 }
1706
1707 if ($modified) {
1708 set_field("course_categories", "timemodified", time(), "id", $categoryid);
1709 }
9936fe81 1710 set_field("course_categories", "coursecount", $count, "id", $categoryid);
02ebf404 1711
1712 return true;
1713}
1714
18a97fd8 1715/**
1716* This function creates a default separated/connected scale
1717*
1718* This function creates a default separated/connected scale
1719* so there's something in the database. The locations of
1720* strings and files is a bit odd, but this is because we
1721* need to maintain backward compatibility with many different
1722* existing language translations and older sites.
1723*
1724* @param type description
1725*/
02ebf404 1726function make_default_scale() {
02ebf404 1727
1728 global $CFG;
1729
1730 $defaultscale = NULL;
1731 $defaultscale->courseid = 0;
1732 $defaultscale->userid = 0;
1733 $defaultscale->name = get_string("separateandconnected");
1734 $defaultscale->scale = get_string("postrating1", "forum").",".
1735 get_string("postrating2", "forum").",".
1736 get_string("postrating3", "forum");
1737 $defaultscale->timemodified = time();
1738
1739 /// Read in the big description from the file. Note this is not
1740 /// HTML (despite the file extension) but Moodle format text.
1741 $parentlang = get_string("parentlang");
1742 if (is_readable("$CFG->dirroot/lang/$CFG->lang/help/forum/ratings.html")) {
1743 $file = file("$CFG->dirroot/lang/$CFG->lang/help/forum/ratings.html");
1744 } else if ($parentlang and is_readable("$CFG->dirroot/lang/$parentlang/help/forum/ratings.html")) {
1745 $file = file("$CFG->dirroot/lang/$parentlang/help/forum/ratings.html");
1746 } else if (is_readable("$CFG->dirroot/lang/en/help/forum/ratings.html")) {
1747 $file = file("$CFG->dirroot/lang/en/help/forum/ratings.html");
1748 } else {
1749 $file = "";
1750 }
1751
1752 $defaultscale->description = addslashes(implode("", $file));
1753
1754 if ($defaultscale->id = insert_record("scale", $defaultscale)) {
94d782eb 1755 execute_sql("UPDATE {$CFG->prefix}forum SET scale = '$defaultscale->id'", false);
02ebf404 1756 }
1757}
1758
18a97fd8 1759/**
1760* Returns a menu of all available scales from the site as well as the given course
1761*
1762* Returns a menu of all available scales from the site as well as the given course
1763*
1764* @param type description
1765*/
02ebf404 1766function get_scales_menu($courseid=0) {
02ebf404 1767
1768 global $CFG;
1769
1770 $sql = "SELECT id, name FROM {$CFG->prefix}scale
1771 WHERE courseid = '0' or courseid = '$courseid'
1772 ORDER BY courseid ASC, name ASC";
1773
1774 if ($scales = get_records_sql_menu("$sql")) {
1775 return $scales;
1776 }
1777
1778 make_default_scale();
1779
1780 return get_records_sql_menu("$sql");
1781}
1782
df28d6c5 1783/// MODULE FUNCTIONS /////////////////////////////////////////////////
1784
18a97fd8 1785/**
1786* Just gets a raw list of all modules in a course
1787*
1788* Just gets a raw list of all modules in a course
1789*
1790* @param type description
1791*/
9fa49e22 1792function get_course_mods($courseid) {
9fa49e22 1793 global $CFG;
1794
7acaa63d 1795 return get_records_sql("SELECT cm.*, m.name as modname
1796 FROM {$CFG->prefix}modules m,
1797 {$CFG->prefix}course_modules cm
9fa49e22 1798 WHERE cm.course = '$courseid'
1799 AND cm.deleted = '0'
1800 AND cm.module = m.id ");
1801}
1802
18a97fd8 1803/**
1804* Given an instance of a module, finds the coursemodule description
1805*
1806* Given an instance of a module, finds the coursemodule description
1807*
1808* @param type description
1809*/
df28d6c5 1810function get_coursemodule_from_instance($modulename, $instance, $courseid) {
df28d6c5 1811
1812 global $CFG;
1813
1814 return get_record_sql("SELECT cm.*, m.name
7acaa63d 1815 FROM {$CFG->prefix}course_modules cm,
1816 {$CFG->prefix}modules md,
1817 {$CFG->prefix}$modulename m
df28d6c5 1818 WHERE cm.course = '$courseid' AND
1819 cm.deleted = '0' AND
1820 cm.instance = m.id AND
1821 md.name = '$modulename' AND
1822 md.id = cm.module AND
1823 m.id = '$instance'");
1824
1825}
1826
18a97fd8 1827/**
1828* Returns an array of all the active instances of a particular module in a given course, sorted in the order they are defined
1829*
1830* Returns an array of all the active instances of a particular
1831* module in a given course, sorted in the order they are defined
1832* in the course. Returns false on any errors.
1833*
1834* @param string $modulename the name of the module to get instances for
1835* @param object(course) $course this depends on an accurate $course->modinfo
1836*/
cccb016a 1837function get_all_instances_in_course($modulename, $course) {
df28d6c5 1838
1839 global $CFG;
1840
cccb016a 1841 if (!$modinfo = unserialize($course->modinfo)) {
1842 return array();
1acfbce5 1843 }
1844
cccb016a 1845 if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible
7acaa63d 1846 FROM {$CFG->prefix}course_modules cm,
1847 {$CFG->prefix}course_sections cw,
1848 {$CFG->prefix}modules md,
1849 {$CFG->prefix}$modulename m
cccb016a 1850 WHERE cm.course = '$course->id' AND
df28d6c5 1851 cm.instance = m.id AND
1852 cm.deleted = '0' AND
1853 cm.section = cw.id AND
1854 md.name = '$modulename' AND
cccb016a 1855 md.id = cm.module")) {
1856 return array();
1857 }
1858
1859 // Hide non-visible instances from students
1860 if (isteacher($course->id)) {
1861 $invisible = -1;
1862 } else {
1863 $invisible = 0;
1864 }
1865
1866 foreach ($modinfo as $mod) {
1867 if ($mod->mod == $modulename and $mod->visible > $invisible) {
7f12f9cd 1868 $instance = $rawmods[$mod->cm];
1869 if (!empty($mod->extra)) {
1870 $instance->extra = $mod->extra;
1871 }
1872 $outputarray[] = $instance;
cccb016a 1873 }
1874 }
1875
1876 return $outputarray;
df28d6c5 1877
1878}
1879
9fa49e22 1880
18a97fd8 1881/**
1882* determine whether a module instance is visible within a course
1883*
1884* Given a valid module object with info about the id and course,
1885* and the module's type (eg "forum") returns whether the object
1886* is visible or not
1887*
1888* @param type description
1889*/
580f2fbc 1890function instance_is_visible($moduletype, $module) {
580f2fbc 1891
1892 global $CFG;
1893
86e6076b 1894 if ($records = get_records_sql("SELECT cm.instance, cm.visible
580f2fbc 1895 FROM {$CFG->prefix}course_modules cm,
580f2fbc 1896 {$CFG->prefix}modules m
1897 WHERE cm.course = '$module->course' AND
1898 cm.module = m.id AND
1899 m.name = '$moduletype' AND
86e6076b 1900 cm.instance = '$module->id'")) {
580f2fbc 1901
1902 foreach ($records as $record) { // there should only be one - use the first one
1903 return $record->visible;
1904 }
1905 }
1906
1907 return true; // visible by default!
1908}
1909
a3fb1c45 1910
1911
1912
9fa49e22 1913/// LOG FUNCTIONS /////////////////////////////////////////////////////
1914
1915
18a97fd8 1916/**
1917* Add an entry to the log table.
1918*
1919* Add an entry to the log table. These are "action" focussed rather
1920* than web server hits, and provide a way to easily reconstruct what
1921* any particular student has been doing.
1922*
1923* @param int $course the course id
1924* @param string $module the module name - e.g. forum, journal, resource, course, user etc
1925* @param string $action view, edit, post (often but not always the same as the file.php)
1926* @param string $url the file and parameters used to see the results of the action
1927* @param string $info additional description information
1928*/
4d744a22 1929function add_to_log($courseid, $module, $action, $url="", $info="") {
9fa49e22 1930
31fefa63 1931 global $db, $CFG, $USER, $REMOTE_ADDR;
9fa49e22 1932
1933 if (isset($USER->realuser)) { // Don't log
1934 return;
1935 }
1936
61e96406 1937 $userid = empty($USER->id) ? "" : $USER->id;
1938
9fa49e22 1939 $timenow = time();
1940 $info = addslashes($info);
1941
31fefa63 1942 $result = $db->Execute("INSERT INTO {$CFG->prefix}log (time,
ebc3bd2b 1943 userid,
1944 course,
1945 ip,
1946 module,
1947 action,
1948 url,
1949 info)
1950 VALUES ('$timenow',
61e96406 1951 '$userid',
4d744a22 1952 '$courseid',
ebc3bd2b 1953 '$REMOTE_ADDR',
1954 '$module',
1955 '$action',
1956 '$url',
1957 '$info')");
1958
ce78926d 1959 if (!$result and ($CFG->debug > 7)) {
9fa49e22 1960 echo "<P>Error: Could not insert a new entry to the Moodle log</P>"; // Don't throw an error
1961 }
4d744a22 1962
1963 if (isstudent($courseid)) {
1964 $db->Execute("UPDATE {$CFG->prefix}user_students SET timeaccess = '$timenow' ".
1965 "WHERE course = '$courseid' AND userid = '$userid'");
1966 }
1967
1968 if (isteacher($courseid, false, false)) {
1969 $db->Execute("UPDATE {$CFG->prefix}user_teachers SET timeaccess = '$timenow' ".
1970 "WHERE course = '$courseid' AND userid = '$userid'");
1971 }
9fa49e22 1972}
1973
1974
18a97fd8 1975/**
1976* select all log records based on SQL criteria
1977*
1978* select all log records based on SQL criteria
1979*
1980* @param string $select SQL select criteria
1981* @param string $order SQL order by clause to sort the records returned
1982*/
519d369f 1983function get_logs($select, $order="l.time DESC", $limitfrom="", $limitnum="", &$totalcount) {
9fa49e22 1984 global $CFG;
1985
519d369f 1986 if ($limitfrom !== "") {
1987 switch ($CFG->dbtype) {
1988 case "mysql":
1989 $limit = "LIMIT $limitfrom,$limitnum";
1990 break;
1991 case "postgres7":
1992 $limit = "LIMIT $limitnum OFFSET $limitfrom";
1993 break;
1994 default:
1995 $limit = "LIMIT $limitnum,$limitfrom";
1996 }
1997 } else {
1998 $limit = "";
1999 }
2000
2001 if ($order) {
2002 $order = "ORDER BY $order";
2003 }
2004
2005 $selectsql = "{$CFG->prefix}log l, {$CFG->prefix}user u WHERE $select";
2006
2007 $totalcount = count_records_sql("SELECT COUNT(*) FROM $selectsql");
2008
9fa49e22 2009 return get_records_sql("SELECT l.*, u.firstname, u.lastname, u.picture
519d369f 2010 FROM $selectsql $order $limit");
9fa49e22 2011}
2012
519d369f 2013
18a97fd8 2014/**
2015* select all log records for a given course and user
2016*
2017* select all log records for a given course and user
2018*
2019* @param type description
2020*/
9fa49e22 2021function get_logs_usercourse($userid, $courseid, $coursestart) {
2022 global $CFG;
2023
da0c90c3 2024 if ($courseid) {
2025 $courseselect = " AND course = '$courseid' ";
2026 }
2027
9fa49e22 2028 return get_records_sql("SELECT floor((`time` - $coursestart)/86400) as day, count(*) as num
2029 FROM {$CFG->prefix}log
ebc3bd2b 2030 WHERE userid = '$userid'
da0c90c3 2031 AND `time` > '$coursestart' $courseselect
9fa49e22 2032 GROUP BY day ");
2033}
2034
18a97fd8 2035/**
2036* select all log records for a given course, user, and day
2037*
2038* select all log records for a given course, user, and day
2039*
2040* @param type description
2041*/
9fa49e22 2042function get_logs_userday($userid, $courseid, $daystart) {
2043 global $CFG;
2044
7e4a6488 2045 if ($courseid) {
2046 $courseselect = " AND course = '$courseid' ";
2047 }
2048
9fa49e22 2049 return get_records_sql("SELECT floor((`time` - $daystart)/3600) as hour, count(*) as num
2050 FROM {$CFG->prefix}log
ebc3bd2b 2051 WHERE userid = '$userid'
7e4a6488 2052 AND `time` > '$daystart' $courseselect
9fa49e22 2053 GROUP BY hour ");
2054}
2055
a3fb1c45 2056/// GENERAL HELPFUL THINGS ///////////////////////////////////
2057
18a97fd8 2058/**
2059* dump a given object's information in a PRE block
2060*
2061* dump a given object's information in a PRE block
2062* Mostly just for debugging
2063*
2064* @param type description
2065*/
a3fb1c45 2066function print_object($object) {
a3fb1c45 2067
2b051f1c 2068 echo "<PRE>";
2069 print_r($object);
2070 echo "</PRE>";
a3fb1c45 2071}
2072
2073
9fa49e22 2074
9d5b689c 2075// vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140:
df28d6c5 2076?>
Moodle official production repository