Temporary scales icon
[moodle.git] / lib / datalib.php
CommitLineData
df28d6c5 1<?PHP // $Id$
2
3/// FUNCTIONS FOR DATABASE HANDLING ////////////////////////////////
4
5function execute_sql($command, $feedback=true) {
6/// Completely general function - it just runs some SQL and reports success.
7
8 global $db;
9
10 $result = $db->Execute("$command");
11
12 if ($result) {
13 if ($feedback) {
14 echo "<P><FONT COLOR=green><B>".get_string("success")."</B></FONT></P>";
15 }
16 return true;
17 } else {
18 if ($feedback) {
19 echo "<P><FONT COLOR=red><B>".get_string("error")."</B></FONT></P>";
20 }
21 return false;
22 }
23}
24
2b051f1c 25function modify_database($sqlfile="", $sqlstring="") {
26/// Assumes that the input text (file or string consists of
27/// a number of SQL statements ENDING WITH SEMICOLONS. The
28/// semicolons MUST be the last character in a line.
df28d6c5 29/// Lines that are blank or that start with "#" are ignored.
30/// Only tested with mysql dump files (mysqldump -p -d moodle)
31
32 global $CFG;
33
2b051f1c 34 $success = true; // Let's be optimistic :-)
35
36 if (!empty($sqlfile)) {
37 if (!is_readable($sqlfile)) {
38 $success = false;
39 echo "<P>Tried to modify database, but \"$sqlfile\" doesn't exist!</P>";
40 return $success;
41 } else {
42 $lines = file($sqlfile);
43 }
44 } else {
45 $lines[] = $sqlstring;
46 }
47
48 $command = "";
49
50 foreach ($lines as $line) {
51 $line = rtrim($line);
52 $length = strlen($line);
53
54 if ($length and $line[0] <> "#") {
55 if (substr($line, $length-1, 1) == ";") {
56 $line = substr($line, 0, $length-1); // strip ;
57 $command .= $line;
58 $command = str_replace("prefix_", $CFG->prefix, $command); // Table prefixes
59 if (! execute_sql($command)) {
60 $success = false;
df28d6c5 61 }
2b051f1c 62 $command = "";
63 } else {
64 $command .= $line;
df28d6c5 65 }
66 }
df28d6c5 67 }
68
69 return $success;
2b051f1c 70
df28d6c5 71}
72
a3fb1c45 73/// FUNCTIONS TO MODIFY TABLES ////////////////////////////////////////////
74
92230499 75function table_column($table, $oldfield, $field, $type="integer", $size="10",
76 $signed="unsigned", $default="0", $null="not null", $after="") {
a3fb1c45 77/// Add a new field to a table, or modify an existing one (if oldfield is defined).
8a230a7d 78 global $CFG, $db;
a3fb1c45 79
80 switch (strtolower($CFG->dbtype)) {
81
82 case "mysql":
83 case "mysqlt":
84
85 switch (strtolower($type)) {
c2cb4545 86 case "text":
87 $type = "TEXT";
4e56c82d 88 $signed = "";
c2cb4545 89 break;
a3fb1c45 90 case "integer":
92230499 91 $type = "INTEGER($size)";
a3fb1c45 92 break;
92230499 93 case "varchar":
94 $type = "VARCHAR($size)";
4e56c82d 95 $signed = "";
a3fb1c45 96 break;
97 }
98
99 if (!empty($oldfield)) {
100 $operation = "CHANGE $oldfield $field";
101 } else {
102 $operation = "ADD $field";
103 }
104
105 $default = "DEFAULT '$default'";
106
107 if (!empty($after)) {
31ce4b53 108 $after = "AFTER `$after`";
a3fb1c45 109 }
110
111 execute_sql("ALTER TABLE {$CFG->prefix}$table $operation $type $signed $default $null $after");
112 break;
113
5a4d292b 114 case "postgres7": // From Petri Asikainen
8a230a7d 115 //Check db-version
116 $dbinfo = $db->ServerInfo();
117 $dbver = substr($dbinfo[version],0,3);
118
119 $field = "$field";
5a4d292b 120 //to prevent conflicts with reserved words
5a4d292b 121 $oldfield = "\"$oldfield\"";
122
123 switch (strtolower($type)) {
124 case "integer":
125 if ($size <= 2) {
126 $type = "INT2";
127 }
128 if ($size <= 4) {
129 $type = "INT";
130 }
131 if ($size > 4) {
132 $type = "INT8";
133 }
134 break;
135 case "varchar":
136 $type = "VARCHAR($size)";
137 break;
138 }
139
8a230a7d 140 $default = "'$default'";
5a4d292b 141
142 //After is not implemented in postgesql
143 //if (!empty($after)) {
144 // $after = "AFTER '$after'";
145 //}
146
cefc7e81 147 if ($oldfield != "\"\"") {
5a4d292b 148 execute_sql("ALTER TABLE {$CFG->prefix}$table RENAME COLUMN $oldfield TO $field");
149 } else {
150 execute_sql("ALTER TABLE {$CFG->prefix}$table ADD COLUMN $field $type");
151 }
152
8a230a7d 153 if ($dbver >= "7.3") {
154 // modifying 'not null' is posible before 7.3
155 //update default values to table
156 if ($null == "NOT NULL") {
157 execute_sql("UPDATE {$CFG->prefix}$table SET $field=$default where $field IS NULL");
158 execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $null");
159 } else {
160 execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field DROP NOT NULL");
161 }
5a4d292b 162 }
8a230a7d 163
164 execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET DEFAULT $default");
5a4d292b 165
166 break;
a3fb1c45 167
168 default:
169 switch (strtolower($type)) {
a3fb1c45 170 case "integer":
a3fb1c45 171 $type = "INTEGER";
172 break;
92230499 173 case "varchar":
174 $type = "VARCHAR";
175 break;
a3fb1c45 176 }
177
178 $default = "DEFAULT '$default'";
179
180 if (!empty($after)) {
31ce4b53 181 $after = "AFTER $after";
a3fb1c45 182 }
183
184 if (!empty($oldfield)) {
185 execute_sql("ALTER TABLE {$CFG->prefix}$table RENAME COLUMN $oldfield $field");
186 } else {
187 execute_sql("ALTER TABLE {$CFG->prefix}$table ADD COLUMN $field $type");
188 }
189
190 execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $null");
191 execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $default");
192 break;
193
194 }
195}
196
197
198
199/// GENERIC FUNCTIONS TO CHECK AND COUNT RECORDS ////////////////////////////////////////
df28d6c5 200
5c63e0c4 201function record_exists($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") {
df28d6c5 202/// Returns true or false depending on whether the specified record exists
203
204 global $CFG;
205
5c63e0c4 206 if ($field1) {
207 $select = "WHERE $field1 = '$value1'";
9fa49e22 208 if ($field2) {
df28d6c5 209 $select .= " AND $field2 = '$value2'";
9fa49e22 210 if ($field3) {
df28d6c5 211 $select .= " AND $field3 = '$value3'";
212 }
213 }
5c63e0c4 214 } else {
215 $select = "";
df28d6c5 216 }
217
218 return record_exists_sql("SELECT * FROM $CFG->prefix$table $select LIMIT 1");
219}
220
221
222function record_exists_sql($sql) {
223/// Returns true or false depending on whether the specified record exists
224/// The sql statement is provided as a string.
225
226 global $db;
227
228 $rs = $db->Execute($sql);
e53b0823 229 if (empty($rs)) return false;
df28d6c5 230
231 if ( $rs->RecordCount() ) {
232 return true;
233 } else {
234 return false;
235 }
236}
237
238
5c63e0c4 239function count_records($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") {
df28d6c5 240/// Get all the records and count them
241
242 global $CFG;
243
5c63e0c4 244 if ($field1) {
245 $select = "WHERE $field1 = '$value1'";
9fa49e22 246 if ($field2) {
df28d6c5 247 $select .= " AND $field2 = '$value2'";
9fa49e22 248 if ($field3) {
df28d6c5 249 $select .= " AND $field3 = '$value3'";
250 }
251 }
5c63e0c4 252 } else {
253 $select = "";
df28d6c5 254 }
255
256 return count_records_sql("SELECT COUNT(*) FROM $CFG->prefix$table $select");
257}
258
9fa49e22 259function count_records_select($table, $select="") {
260/// Get all the records and count them
261
262 global $CFG;
263
d26d7ed0 264 if ($select) {
265 $select = "WHERE $select";
266 }
267
9fa49e22 268 return count_records_sql("SELECT COUNT(*) FROM $CFG->prefix$table $select");
269}
270
271
df28d6c5 272function count_records_sql($sql) {
273/// Get all the records and count them
274/// The sql statement is provided as a string.
275
276 global $db;
277
278 $rs = $db->Execute("$sql");
e53b0823 279 if (empty($rs)) return 0;
df28d6c5 280
281 return $rs->fields[0];
282}
283
a3fb1c45 284
285
286
287/// GENERIC FUNCTIONS TO GET, INSERT, OR UPDATE DATA ///////////////////////////////////
288
5c63e0c4 289function get_record($table, $field1, $value1, $field2="", $value2="", $field3="", $value3="") {
df28d6c5 290/// Get a single record as an object
291
292 global $CFG;
293
5c63e0c4 294 $select = "WHERE $field1 = '$value1'";
df28d6c5 295
9fa49e22 296 if ($field2) {
df28d6c5 297 $select .= " AND $field2 = '$value2'";
9fa49e22 298 if ($field3) {
df28d6c5 299 $select .= " AND $field3 = '$value3'";
300 }
301 }
302
303 return get_record_sql("SELECT * FROM $CFG->prefix$table $select");
304}
305
306function get_record_sql($sql) {
307/// Get a single record as an object
308/// The sql statement is provided as a string.
4d7a3735 309/// A LIMIT is normally added to only look for 1 record
df28d6c5 310
4d7a3735 311 global $db, $CFG;
df28d6c5 312
4d7a3735 313 if ($CFG->debug > 7) { // Debugging mode - don't use limit
314 $limit = "";
315 } else {
316 $limit = " LIMIT 1"; // Workaround - limit to one record
317 }
318
7618a8eb 319 if (!$rs = $db->Execute("$sql$limit")) {
320 if ($CFG->debug > 7) { // Debugging mode - print checks
321 $db->debug=true;
322 $db->Execute("$sql$limit");
323 $db->debug=false;
324 }
325 return false;
326 }
4d7a3735 327
7618a8eb 328 if (!$recordcount = $rs->RecordCount()) {
329 return false; // Found no records
4d7a3735 330 }
df28d6c5 331
7618a8eb 332 if ($recordcount == 1) { // Found one record
df28d6c5 333 return (object)$rs->fields;
4d7a3735 334
7618a8eb 335 } else { // Error: found more than one record
336 notify("Error: Turn off debugging to hide this error.");
337 notify("$sql$limit");
4d7a3735 338 if ($records = $rs->GetAssoc(true)) {
7618a8eb 339 notify("Found more than one record in get_record_sql !");
4d7a3735 340 print_object($records);
4d7a3735 341 } else {
7618a8eb 342 notify("Very strange error in get_record_sql !");
343 print_object($rs);
4d7a3735 344 }
7618a8eb 345 print_continue("$CFG->wwwroot/admin/config.php");
df28d6c5 346 }
347}
348
18496c59 349function get_record_select($table, $select="", $fields="*") {
350/// Gets one record from a table, as an object
351/// "select" is a fragment of SQL to define the selection criteria
352
353 global $CFG;
354
355 if ($select) {
356 $select = "WHERE $select";
357 }
358
359 return get_record_sql("SELECT $fields FROM $CFG->prefix$table $select");
360}
361
362
0eeac484 363function get_records($table, $field="", $value="", $sort="", $fields="*", $limitfrom="", $limitnum="") {
df28d6c5 364/// Get a number of records as an array of objects
365/// Can optionally be sorted eg "time ASC" or "time DESC"
366/// If "fields" is specified, only those fields are returned
367/// The "key" is the first column returned, eg usually "id"
0eeac484 368/// limitfrom and limitnum must both be specified or not at all
df28d6c5 369
370 global $CFG;
371
9fa49e22 372 if ($field) {
df28d6c5 373 $select = "WHERE $field = '$value'";
5c63e0c4 374 } else {
375 $select = "";
df28d6c5 376 }
5c63e0c4 377
74a0363f 378 if ($limitfrom !== "") {
0eeac484 379 switch ($CFG->dbtype) {
380 case "mysql":
381 $limit = "LIMIT $limitfrom,$limitnum";
382 break;
383 case "postgres7":
384 $limit = "LIMIT $limitnum OFFSET $limitfrom";
385 break;
386 default:
387 $limit = "LIMIT $limitnum,$limitfrom";
388 }
389 } else {
390 $limit = "";
391 }
392
df28d6c5 393 if ($sort) {
5c63e0c4 394 $sort = "ORDER BY $sort";
df28d6c5 395 }
396
0eeac484 397 return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort $limit");
df28d6c5 398}
399
9fa49e22 400function get_records_select($table, $select="", $sort="", $fields="*") {
401/// Get a number of records as an array of objects
402/// Can optionally be sorted eg "time ASC" or "time DESC"
403/// "select" is a fragment of SQL to define the selection criteria
404/// The "key" is the first column returned, eg usually "id"
405
406 global $CFG;
407
d26d7ed0 408 if ($select) {
409 $select = "WHERE $select";
5c63e0c4 410 }
411
412 if ($sort) {
413 $sort = "ORDER BY $sort";
d26d7ed0 414 }
415
5c63e0c4 416 return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort");
9fa49e22 417}
418
df28d6c5 419
420function get_records_list($table, $field="", $values="", $sort="", $fields="*") {
9fa49e22 421/// Get a number of records as an array of objects
422/// Differs from get_records() in that the values variable
423/// can be a comma-separated list of values eg "4,5,6,10"
424/// Can optionally be sorted eg "time ASC" or "time DESC"
425/// The "key" is the first column returned, eg usually "id"
df28d6c5 426
427 global $CFG;
428
9fa49e22 429 if ($field) {
df28d6c5 430 $select = "WHERE $field in ($values)";
5c63e0c4 431 } else {
432 $select = "";
df28d6c5 433 }
5c63e0c4 434
df28d6c5 435 if ($sort) {
5c63e0c4 436 $sort = "ORDER BY $sort";
df28d6c5 437 }
438
5c63e0c4 439 return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort");
df28d6c5 440}
441
442
9fa49e22 443
df28d6c5 444function get_records_sql($sql) {
9fa49e22 445/// Get a number of records as an array of objects
446/// The "key" is the first column returned, eg usually "id"
447/// The sql statement is provided as a string.
df28d6c5 448
449 global $db;
450
451 $rs = $db->Execute("$sql");
e53b0823 452 if (empty($rs)) return false;
df28d6c5 453
454 if ( $rs->RecordCount() > 0 ) {
455 if ($records = $rs->GetAssoc(true)) {
456 foreach ($records as $key => $record) {
457 $objects[$key] = (object) $record;
458 }
459 return $objects;
460 } else {
461 return false;
462 }
463 } else {
464 return false;
465 }
466}
467
9fa49e22 468function get_records_menu($table, $field="", $value="", $sort="", $fields="*") {
469/// Get a number of records as an array of objects
470/// Can optionally be sorted eg "time ASC" or "time DESC"
471/// If "fields" is specified, only those fields are returned
472/// The "key" is the first column returned, eg usually "id"
473
474 global $CFG;
475
476 if ($field) {
477 $select = "WHERE $field = '$value'";
5c63e0c4 478 } else {
479 $select = "";
9fa49e22 480 }
5c63e0c4 481
9fa49e22 482 if ($sort) {
5c63e0c4 483 $sort = "ORDER BY $sort";
9fa49e22 484 }
485
5c63e0c4 486 return get_records_sql_menu("SELECT $fields FROM $CFG->prefix$table $select $sort");
9fa49e22 487}
488
489function get_records_select_menu($table, $select="", $sort="", $fields="*") {
490/// Get a number of records as an array of objects
491/// Can optionally be sorted eg "time ASC" or "time DESC"
492/// "select" is a fragment of SQL to define the selection criteria
493/// Returns associative array of first two fields
494
495 global $CFG;
496
d26d7ed0 497 if ($select) {
498 $select = "WHERE $select";
499 }
500
5c63e0c4 501 if ($sort) {
502 $sort = "ORDER BY $sort";
503 }
504
505 return get_records_sql_menu("SELECT $fields FROM $CFG->prefix$table $select $sort");
9fa49e22 506}
507
508
df28d6c5 509function get_records_sql_menu($sql) {
9fa49e22 510/// Given an SQL select, this function returns an associative
511/// array of the first two columns. This is most useful in
512/// combination with the choose_from_menu function to create
513/// a form menu.
df28d6c5 514
515 global $db;
516
517 $rs = $db->Execute("$sql");
e53b0823 518 if (empty($rs)) return false;
df28d6c5 519
520 if ( $rs->RecordCount() > 0 ) {
521 while (!$rs->EOF) {
522 $menu[$rs->fields[0]] = $rs->fields[1];
523 $rs->MoveNext();
524 }
525 return $menu;
526
527 } else {
528 return false;
529 }
530}
531
ec2a28a6 532function get_field($table, $return, $field1, $value1, $field2="", $value2="", $field3="", $value3="") {
df28d6c5 533/// Get a single field from a database record
534
535 global $db, $CFG;
536
ec2a28a6 537 $select = "WHERE $field1 = '$value1'";
538
539 if ($field2) {
540 $select .= " AND $field2 = '$value2'";
541 if ($field3) {
542 $select .= " AND $field3 = '$value3'";
543 }
544 }
545
546 $rs = $db->Execute("SELECT $return FROM $CFG->prefix$table $select");
e53b0823 547 if (empty($rs)) return false;
df28d6c5 548
549 if ( $rs->RecordCount() == 1 ) {
550 return $rs->fields["$return"];
551 } else {
552 return false;
553 }
554}
555
ec2a28a6 556function set_field($table, $newfield, $newvalue, $field1, $value1, $field2="", $value2="", $field3="", $value3="") {
df28d6c5 557/// Set a single field in a database record
558
559 global $db, $CFG;
560
ec2a28a6 561 $select = "WHERE $field1 = '$value1'";
562
563 if ($field2) {
564 $select .= " AND $field2 = '$value2'";
565 if ($field3) {
566 $select .= " AND $field3 = '$value3'";
567 }
568 }
569
570 return $db->Execute("UPDATE $CFG->prefix$table SET $newfield = '$newvalue' $select");
df28d6c5 571}
572
573
5c63e0c4 574function delete_records($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") {
df28d6c5 575/// Delete one or more records from a table
576
577 global $db, $CFG;
578
5c63e0c4 579 if ($field1) {
580 $select = "WHERE $field1 = '$value1'";
9fa49e22 581 if ($field2) {
df28d6c5 582 $select .= " AND $field2 = '$value2'";
9fa49e22 583 if ($field3) {
df28d6c5 584 $select .= " AND $field3 = '$value3'";
585 }
586 }
5c63e0c4 587 } else {
588 $select = "";
df28d6c5 589 }
590
591 return $db->Execute("DELETE FROM $CFG->prefix$table $select");
592}
593
30f89d68 594function delete_records_select($table, $select="") {
595/// Delete one or more records from a table
596/// "select" is a fragment of SQL to define the selection criteria
597
598 global $CFG, $db;
599
600 if ($select) {
601 $select = "WHERE $select";
602 }
603
604 return $db->Execute("DELETE FROM $CFG->prefix$table $select");
605}
606
df28d6c5 607
608function insert_record($table, $dataobject, $returnid=true) {
609/// Insert a record into a table and return the "id" field if required
610/// If the return ID isn't required, then this just reports success as true/false.
611/// $dataobject is an object containing needed data
612
613 global $db, $CFG;
614
615 // Determine all the fields needed
616 if (! $columns = $db->MetaColumns("$CFG->prefix$table")) {
617 return false;
618 }
df28d6c5 619 $data = (array)$dataobject;
620
b3fa6684 621 // Pull out data from the dataobject that matches the fields in the table.
622 // If fields are missing or empty, then try to set the defaults explicitly
623 // because some databases (eg PostgreSQL) don't always set them properly
df28d6c5 624 foreach ($columns as $column) {
9655b654 625 if(isset($column->primary_key) and $column->primary_key == 1) {
626 $pkey = $column->name; // take column name of primary key
627 }
b3fa6684 628 if ($column->name <> "id") {
629 if (isset($data[$column->name])) {
50913937 630 if ((string)$data[$column->name] == "" and !empty($column->has_default) and !empty($column->default_value)) {
b3fa6684 631 $ddd[$column->name] = $column->default_value;
632 } else {
dc8791f3 633 $ddd[$column->name] = $data[$column->name];
b3fa6684 634 }
635 } else {
92230499 636 if (!empty($column->has_default) and !empty($column->default_value)) {
b3fa6684 637 $ddd[$column->name] = $column->default_value;
638 }
639 }
df28d6c5 640 }
641 }
642
b3fa6684 643
df28d6c5 644 // Construct SQL queries
645 if (! $numddd = count($ddd)) {
646 return false;
647 }
648
649 $count = 0;
650 $inscolumns = "";
651 $insvalues = "";
652 $select = "";
653
654 foreach ($ddd as $key => $value) {
3c72e2f9 655 if (!is_null($value)){
656 if ($select) {
657 $inscolumns .= ", ";
658 $insvalues .= ", ";
659 $select .= " AND ";
660 }
661 $inscolumns .= "$key";
662 $insvalues .= "'$value'";
663 $select .= "$key = '$value'";
df28d6c5 664 }
665 }
666
667 if (! $rs = $db->Execute("INSERT INTO $CFG->prefix$table ($inscolumns) VALUES ($insvalues)")) {
668 return false;
669 }
670
671 if ($returnid) {
64929926 672 if ($CFG->dbtype == "mysql" ) {
99988d1a 673 return $db->Insert_ID(); // ADOdb has stored the ID for us, but it isn't reliable
674 }
1523be78 675
9655b654 676 if ($CFG->dbtype == "postgres7" and isset($pkey)){
677 $oid = $db->Insert_ID();
28ba0d55 678 if ($rs = $db->Execute("SELECT $pkey FROM $CFG->prefix$table WHERE oid = $oid")) {
9655b654 679 if ($rs->RecordCount() == 1) {
680 return $rs->fields[0];
681 } else {
682 return false;
683 }
684 }
685 }
1523be78 686 // Try to pull the record out again to find the id. This is the most cross-platform method.
df28d6c5 687 if ($rs = $db->Execute("SELECT id FROM $CFG->prefix$table WHERE $select")) {
1523be78 688 if ($rs->RecordCount() == 1) {
689 return $rs->fields[0];
690 }
df28d6c5 691 }
1523be78 692
693 return false;
694
df28d6c5 695 } else {
696 return true;
697 }
698}
699
700
701function update_record($table, $dataobject) {
702/// Update a record in a table
703/// $dataobject is an object containing needed data
ebc3bd2b 704/// Relies on $dataobject having a variable "id" to
705/// specify the record to update
df28d6c5 706
707 global $db, $CFG;
708
709 if (! isset($dataobject->id) ) {
710 return false;
711 }
712
713 // Determine all the fields in the table
714 if (!$columns = $db->MetaColumns("$CFG->prefix$table")) {
715 return false;
716 }
717 $data = (array)$dataobject;
718
719 // Pull out data matching these fields
720 foreach ($columns as $column) {
92230499 721 if ($column->name <> "id" and isset($data[$column->name]) ) {
df28d6c5 722 $ddd[$column->name] = $data[$column->name];
723 }
724 }
725
726 // Construct SQL queries
727 $numddd = count($ddd);
728 $count = 0;
729 $update = "";
730
731 foreach ($ddd as $key => $value) {
732 $count++;
733 $update .= "$key = '$value'";
734 if ($count < $numddd) {
735 $update .= ", ";
736 }
737 }
738
739 if ($rs = $db->Execute("UPDATE $CFG->prefix$table SET $update WHERE id = '$dataobject->id'")) {
740 return true;
741 } else {
742 return false;
743 }
744}
745
746
df28d6c5 747
748
749/// USER DATABASE ////////////////////////////////////////////////
750
751function get_user_info_from_db($field, $value) {
752/// Get a complete user record, which includes all the info
753/// in the user record, as well as membership information
754/// Suitable for setting as $USER session cookie.
755
18496c59 756 if (!$field or !$value) {
df28d6c5 757 return false;
df28d6c5 758 }
759
18496c59 760 if (! $user = get_record_select("user", "$field = '$value' AND deleted <> '1'")) {
761 return false;
762 }
df28d6c5 763
18496c59 764 // Add membership information
df28d6c5 765
18496c59 766 if ($site = get_site()) { // Everyone is always a member of the top course
767 $user->student[$site->id] = true;
768 }
df28d6c5 769
f53af941 770 if ($students = get_records("user_students", "userid", $user->id)) {
771 foreach ($students as $student) {
a6d82c3f 772 if (get_field("course", "visible", "id", $student->course)) {
773 $user->student[$student->course] = true;
774 $user->zoom[$student->course] = $student->zoom;
775 }
f53af941 776 }
18496c59 777 }
df28d6c5 778
f53af941 779 if ($teachers = get_records("user_teachers", "userid", $user->id)) {
780 foreach ($teachers as $teacher) {
781 $user->teacher[$teacher->course] = true;
782 }
18496c59 783 }
df28d6c5 784
f53af941 785 if ($admins = get_records("user_admins", "userid", $user->id)) {
786 foreach ($admins as $admin) {
787 $user->admin = true;
788 break;
789 }
df28d6c5 790 }
18496c59 791
b86fc0e2 792 if ($displays = get_records("course_display", "userid", $user->id)) {
793 foreach ($displays as $display) {
794 $user->display[$display->course] = $display->display;
795 }
796 }
797
18496c59 798 return $user;
df28d6c5 799}
800
801function update_user_in_db() {
802/// Updates user record to record their last access
803
804 global $db, $USER, $REMOTE_ADDR, $CFG;
805
806 if (!isset($USER->id))
807 return false;
808
809 $timenow = time();
810 if ($db->Execute("UPDATE {$CFG->prefix}user SET lastIP='$REMOTE_ADDR', lastaccess='$timenow'
811 WHERE id = '$USER->id' ")) {
812 return true;
813 } else {
814 return false;
815 }
816}
817
818
819function adminlogin($username, $md5password) {
820/// Does this username and password specify a valid admin user?
821
822 global $CFG;
823
9fa49e22 824 return record_exists_sql("SELECT u.id
825 FROM {$CFG->prefix}user u,
826 {$CFG->prefix}user_admins a
ebc3bd2b 827 WHERE u.id = a.userid
df28d6c5 828 AND u.username = '$username'
829 AND u.password = '$md5password'");
830}
831
832
833function get_site () {
834/// Returns $course object of the top-level site.
835
836 if ( $course = get_record("course", "category", 0)) {
837 return $course;
838 } else {
839 return false;
840 }
841}
842
d2b6ba70 843function get_courses($categoryid="all", $sort="sortorder ASC", $fields="*") {
c2cb4545 844/// Returns list of courses, for whole site, or category
9fa49e22 845
d2b6ba70 846 if ($categoryid == "all") {
847 $courses = get_records("course", "", "", $sort, $fields);
848 } else {
849 $courses = get_records("course", "category", "$categoryid", $sort, $fields);
a6d82c3f 850 }
851
852 if ($courses) { /// Remove unavailable courses from the list
853 foreach ($courses as $key => $course) {
854 if (!$course->visible) {
855 if (!isteacher($course->id)) {
856 unset($courses[$key]);
857 }
858 }
859 }
9fa49e22 860 }
a6d82c3f 861 return $courses;
9fa49e22 862}
863
d2b6ba70 864
c2cb4545 865function get_my_courses($userid, $sort="c.fullname ASC") {
866 global $CFG;
867
868 return get_records_sql("SELECT c.*
869 FROM {$CFG->prefix}course c,
870 {$CFG->prefix}user_students s,
871 {$CFG->prefix}user_teachers t
872 WHERE (s.userid = '$userid' AND s.course = c.id)
873 OR (t.userid = '$userid' AND t.course = c.id)
874 GROUP BY c.id
875 ORDER BY $sort");
876}
877
df4f0670 878function get_courses_search($search, $sort="fullname ASC", $page=0, $recordsperpage=50) {
879/// Returns a list of courses that match a search
880
881 global $CFG;
882
883 switch ($CFG->dbtype) {
884 case "mysql":
885 $limit = "LIMIT $page,$recordsperpage";
886 break;
887 case "postgres7":
888 $limit = "LIMIT $recordsperpage OFFSET ".($page * $recordsperpage);
889 break;
890 default:
891 $limit = "LIMIT $recordsperpage,$page";
892 }
893
894 //to allow caseinsensitive search for postgesql
895 if ($CFG->dbtype == "postgres7") {
896 $LIKE = "ILIKE";
897 } else {
898 $LIKE = "LIKE";
899 }
900
901 $fullnamesearch = "";
902 $summarysearch = "";
903
904 $searchterms = explode(" ", $search); // Search for words independently
905
906 foreach ($searchterms as $searchterm) {
907 if ($fullnamesearch) {
908 $fullnamesearch .= " AND ";
909 }
910 $fullnamesearch .= " fullname $LIKE '%$searchterm%' ";
911
912 if ($summarysearch) {
913 $summarysearch .= " AND ";
914 }
915 $summarysearch .= " summary $LIKE '%$searchterm%' ";
916 }
917
918
919 $courses = get_records_sql("SELECT *
920 FROM {$CFG->prefix}course
921 WHERE ($fullnamesearch OR $summarysearch)
922 ORDER BY $sort $limit");
923
924 if ($courses) { /// Remove unavailable courses from the list
925 foreach ($courses as $key => $course) {
926 if (!$course->visible) {
927 if (!isteacher($course->id)) {
928 unset($courses[$key]);
929 }
930 }
931 }
932 }
933
934 return $courses;
935}
936
c2cb4545 937
938function get_categories($parent="none", $sort="sortorder ASC") {
d2b6ba70 939/// Returns a sorted list of categories
940
c2cb4545 941 if ($parent == "none") {
942 $categories = get_records("course_categories", "", "", $sort);
943 } else {
944 $categories = get_records("course_categories", "parent", $parent, $sort);
945 }
946 if ($categories) { /// Remove unavailable categories from the list
947 $admin = isadmin();
948 foreach ($categories as $key => $category) {
949 if (!$category->visible) {
950 if (!$admin) {
951 unset($categories[$key]);
952 }
953 }
954 }
955 }
956 return $categories;
9fa49e22 957}
958
d2b6ba70 959
df4f0670 960function fix_course_sortorder($categoryid, $sort="sortorder ASC") {
d2b6ba70 961/// Given a category object, this function makes sure the courseorder
7e1a999c 962/// variable reflects the real world.
963
df4f0670 964 if (!$courses = get_records("course", "category", "$categoryid", "$sort", "id, sortorder")) {
d2b6ba70 965 return true;
7e1a999c 966 }
967
d2b6ba70 968 $count = 0;
969 $modified = false;
7e1a999c 970
d2b6ba70 971 foreach ($courses as $course) {
972 if ($course->sortorder != $count) {
973 set_field("course", "sortorder", $count, "id", $course->id);
974 $modified = true;
7e1a999c 975 }
d2b6ba70 976 $count++;
7e1a999c 977 }
d2b6ba70 978
979 if ($modified) {
980 set_field("course_categories", "timemodified", time(), "id", $categoryid);
7e1a999c 981 }
d2b6ba70 982
7e1a999c 983 return true;
984}
985
9fa49e22 986
987function get_guest() {
988 return get_user_info_from_db("username", "guest");
989}
990
991
df28d6c5 992function get_admin () {
993/// Returns $user object of the main admin user
994
995 global $CFG;
996
997 if ( $admins = get_admins() ) {
998 foreach ($admins as $admin) {
999 return $admin; // ie the first one
1000 }
1001 } else {
1002 return false;
1003 }
1004}
1005
1006function get_admins() {
1007/// Returns list of all admins
1008
1009 global $CFG;
1010
ebc3bd2b 1011 return get_records_sql("SELECT u.*
1012 FROM {$CFG->prefix}user u,
1013 {$CFG->prefix}user_admins a
1014 WHERE a.userid = u.id
1015 ORDER BY u.id ASC");
df28d6c5 1016}
1017
1924074c 1018function get_creators() {
1019/// Returns list of all admins
1020
1021 global $CFG;
1022
1023 return get_records_sql("SELECT u.*
1024 FROM {$CFG->prefix}user u,
1025 {$CFG->prefix}user_coursecreators a
1026 WHERE a.userid = u.id
1027 ORDER BY u.id ASC");
1028}
df28d6c5 1029
1030function get_teacher($courseid) {
1031/// Returns $user object of the main teacher for a course
1032
1033 global $CFG;
1034
1035 if ( $teachers = get_course_teachers($courseid, "t.authority ASC")) {
1036 foreach ($teachers as $teacher) {
1037 if ($teacher->authority) {
1038 return $teacher; // the highest authority teacher
1039 }
1040 }
1041 } else {
1042 return false;
1043 }
1044}
1045
2d0b30a0 1046function get_course_students($courseid, $sort="u.lastaccess DESC") {
df28d6c5 1047/// Returns list of all students in this course
adaf3928 1048/// if courseid = 0 then return ALL students in all courses
df28d6c5 1049
1050 global $CFG;
1051
688d06f4 1052 return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,
9d6f5d42 1053 u.email, u.city, u.country, u.lastaccess, u.lastlogin, u.picture
1054 FROM {$CFG->prefix}user u,
1055 {$CFG->prefix}user_students s
2d0b30a0 1056 WHERE s.course = '$courseid' AND s.userid = u.id AND u.deleted = '0'
df28d6c5 1057 ORDER BY $sort");
1058}
1059
2d0b30a0 1060function get_course_teachers($courseid, $sort="t.authority ASC") {
df28d6c5 1061/// Returns list of all teachers in this course
adaf3928 1062/// if courseid = 0 then return ALL teachers in all courses
df28d6c5 1063
1064 global $CFG;
1065
688d06f4 1066 return get_records_sql("SELECT u.*,t.authority,t.role
1067 FROM {$CFG->prefix}user u,
1068 {$CFG->prefix}user_teachers t
2d0b30a0 1069 WHERE t.course = '$courseid' AND t.userid = u.id AND u.deleted = '0'
df28d6c5 1070 ORDER BY $sort");
1071}
1072
1073function get_course_users($courseid, $sort="u.lastaccess DESC") {
353d0338 1074/// Returns all the users of a course: students and teachers
1075/// If the "course" is actually the site, then return all site users.
1076
1077 $site = get_site();
1078
1079 if ($courseid == $site->id) {
1080 return get_site_users($sort);
1081 }
1082
1083 /// Using this method because the single SQL just would not always work!
df28d6c5 1084
1085 $teachers = get_course_teachers($courseid, $sort);
1086 $students = get_course_students($courseid, $sort);
1087
1088 if ($teachers and $students) {
1089 return array_merge($teachers, $students);
1090 } else if ($teachers) {
1091 return $teachers;
1092 } else {
1093 return $students;
1094 }
1095
353d0338 1096 /// Why wouldn't this work?
1097 /// return get_records_sql("SELECT u.* FROM user u, user_students s, user_teachers t
1098 /// WHERE (s.course = '$courseid' AND s.userid = u.id) OR
1099 /// (t.course = '$courseid' AND t.userid = u.id)
1100 /// ORDER BY $sort");
df28d6c5 1101}
1102
2d0b30a0 1103function get_site_users($sort="u.lastaccess DESC") {
1104/// Returns a list of all active users who are enrolled
1105/// or teaching in courses on this server
1106
353d0338 1107 global $CFG, $db;
1108
1109 //$db->debug = true;
2d0b30a0 1110
688d06f4 1111 return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,
bbf9b162 1112 u.email, u.city, u.country, u.lastaccess, u.lastlogin, u.picture
1113 FROM {$CFG->prefix}user u,
1114 {$CFG->prefix}user_students s,
353d0338 1115 {$CFG->prefix}user_teachers t,
1116 {$CFG->prefix}user_coursecreators c,
1117 {$CFG->prefix}user_admins a
bbf9b162 1118 WHERE s.userid = u.id
1119 OR t.userid = u.id
353d0338 1120 OR a.userid = u.id
1121 OR c.userid = u.id
1122 GROUP BY u.id
1123 ORDER BY $sort ");
2d0b30a0 1124}
1125
9fa49e22 1126
5a741655 1127function get_users($get=true, $search="", $confirmed=false, $exceptions="", $sort="firstname ASC") {
1128/// Returns a subset of users,
1129/// $get - if false then only a count of the records is returned
1130/// $search is a simple string to search for
1131/// $confirmed is a switch to allow/disallow unconfirmed users
1132/// $exceptions is a list of IDs to ignore, eg 2,4,5,8,9,10
1133/// $sort is a sorting criteria to use
e384fb7b 1134
1135 if ($search) {
1136 $search = " AND (firstname LIKE '%$search%'
1137 OR lastname LIKE '%$search%'
1138 OR email LIKE '%$search%') ";
1139 }
1140
5a741655 1141 if ($confirmed) {
1142 $confirmed = " AND confirmed = '1' ";
1143 }
1144
1145 if ($exceptions) {
1146 $exceptions = " AND id NOT IN ($exceptions) ";
1147 }
1148
1149 if ($sort and $get) {
1150 $sort = " ORDER BY $sort ";
1151 } else {
1152 $sort = "";
1153 }
1154
1155 if ($get) {
1156 return get_records_select("user", "username <> 'guest' AND deleted = 0 $search $confirmed $exceptions $sort");
1157 } else {
1158 return count_records_select("user", "username <> 'guest' AND deleted = 0 $search $confirmed $exceptions $sort");
1159 }
9fa49e22 1160}
1161
5a741655 1162
c750592a 1163function get_users_listing($sort, $dir="ASC", $page=1, $recordsperpage=20, $search="") {
9fa49e22 1164 global $CFG;
31fefa63 1165
c2a96d6b 1166 switch ($CFG->dbtype) {
1167 case "mysql":
1168 $limit = "LIMIT $page,$recordsperpage";
1169 break;
1170 case "postgres7":
a918234e 1171 $limit = "LIMIT $recordsperpage OFFSET ".($page);
c2a96d6b 1172 break;
1173 default:
1174 $limit = "LIMIT $recordsperpage,$page";
31fefa63 1175 }
c2a96d6b 1176
c750592a 1177 if ($search) {
1178 $search = " AND (firstname LIKE '%$search%'
1179 OR lastname LIKE '%$search%'
1180 OR email LIKE '%$search%') ";
1181 }
1182
9fa49e22 1183 return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess
1184 FROM {$CFG->prefix}user
1185 WHERE username <> 'guest'
c750592a 1186 AND deleted <> '1' $search
31fefa63 1187 ORDER BY $sort $dir $limit");
9fa49e22 1188
1189}
1190
1191function get_users_confirmed() {
1192 global $CFG;
1193 return get_records_sql("SELECT *
1194 FROM {$CFG->prefix}user
1195 WHERE confirmed = 1
1196 AND deleted = 0
1197 AND username <> 'guest'
1198 AND username <> 'changeme'");
1199}
1200
1201
99988d1a 1202function get_users_unconfirmed($cutofftime=2000000000) {
9fa49e22 1203 global $CFG;
1204 return get_records_sql("SELECT *
1205 FROM {$CFG->prefix}user
1206 WHERE confirmed = 0
1207 AND firstaccess > 0
1208 AND firstaccess < '$cutofftime'");
1209}
1210
1211
1212function get_users_longtimenosee($cutofftime) {
1213 global $CFG;
31fefa63 1214
1215 $db->debug = true;
9fa49e22 1216 return get_records_sql("SELECT u.*
1217 FROM {$CFG->prefix}user u,
1218 {$CFG->prefix}user_students s
31fefa63 1219 WHERE u.lastaccess > '0'
1220 AND u.lastaccess < '$cutofftime'
97485d07 1221 AND u.id = s.userid
1222 GROUP BY u.id");
9fa49e22 1223}
1224
1225
df28d6c5 1226/// MODULE FUNCTIONS /////////////////////////////////////////////////
1227
9fa49e22 1228function get_course_mods($courseid) {
1229/// Just gets a raw list of all modules in a course
1230 global $CFG;
1231
7acaa63d 1232 return get_records_sql("SELECT cm.*, m.name as modname
1233 FROM {$CFG->prefix}modules m,
1234 {$CFG->prefix}course_modules cm
9fa49e22 1235 WHERE cm.course = '$courseid'
1236 AND cm.deleted = '0'
1237 AND cm.module = m.id ");
1238}
1239
df28d6c5 1240function get_coursemodule_from_instance($modulename, $instance, $courseid) {
1241/// Given an instance of a module, finds the coursemodule description
1242
1243 global $CFG;
1244
1245 return get_record_sql("SELECT cm.*, m.name
7acaa63d 1246 FROM {$CFG->prefix}course_modules cm,
1247 {$CFG->prefix}modules md,
1248 {$CFG->prefix}$modulename m
df28d6c5 1249 WHERE cm.course = '$courseid' AND
1250 cm.deleted = '0' AND
1251 cm.instance = m.id AND
1252 md.name = '$modulename' AND
1253 md.id = cm.module AND
1254 m.id = '$instance'");
1255
1256}
1257
cccb016a 1258function get_all_instances_in_course($modulename, $course) {
df28d6c5 1259/// Returns an array of all the active instances of a particular
cccb016a 1260/// module in a given course, sorted in the order they are defined
1261/// in the course. Returns false on any errors.
1262/// $course is a course object, this depends on an accurate $course->modinfo
df28d6c5 1263
1264 global $CFG;
1265
cccb016a 1266 if (!$modinfo = unserialize($course->modinfo)) {
1267 return array();
1acfbce5 1268 }
1269
cccb016a 1270 if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible
7acaa63d 1271 FROM {$CFG->prefix}course_modules cm,
1272 {$CFG->prefix}course_sections cw,
1273 {$CFG->prefix}modules md,
1274 {$CFG->prefix}$modulename m
cccb016a 1275 WHERE cm.course = '$course->id' AND
df28d6c5 1276 cm.instance = m.id AND
1277 cm.deleted = '0' AND
1278 cm.section = cw.id AND
1279 md.name = '$modulename' AND
cccb016a 1280 md.id = cm.module")) {
1281 return array();
1282 }
1283
1284 // Hide non-visible instances from students
1285 if (isteacher($course->id)) {
1286 $invisible = -1;
1287 } else {
1288 $invisible = 0;
1289 }
1290
1291 foreach ($modinfo as $mod) {
1292 if ($mod->mod == $modulename and $mod->visible > $invisible) {
1293 $outputarray[] = $rawmods[$mod->cm];
1294 }
1295 }
1296
1297 return $outputarray;
df28d6c5 1298
1299}
1300
9fa49e22 1301
580f2fbc 1302function instance_is_visible($moduletype, $module) {
1303/// Given a valid module object with info about the id and course,
1304/// and the module's type (eg "forum") returns whether the object
1305/// is visible or not
1306
1307 global $CFG;
1308
86e6076b 1309 if ($records = get_records_sql("SELECT cm.instance, cm.visible
580f2fbc 1310 FROM {$CFG->prefix}course_modules cm,
580f2fbc 1311 {$CFG->prefix}modules m
1312 WHERE cm.course = '$module->course' AND
1313 cm.module = m.id AND
1314 m.name = '$moduletype' AND
86e6076b 1315 cm.instance = '$module->id'")) {
580f2fbc 1316
1317 foreach ($records as $record) { // there should only be one - use the first one
1318 return $record->visible;
1319 }
1320 }
1321
1322 return true; // visible by default!
1323}
1324
a3fb1c45 1325
1326
1327
9fa49e22 1328/// LOG FUNCTIONS /////////////////////////////////////////////////////
1329
1330
1331function add_to_log($course, $module, $action, $url="", $info="") {
1332/// Add an entry to the log table. These are "action" focussed rather
1333/// than web server hits, and provide a way to easily reconstruct what
1334/// any particular student has been doing.
1335///
1336/// course = the course id
1337/// module = forum, journal, resource, course, user etc
1338/// action = view, edit, post (often but not always the same as the file.php)
1339/// url = the file and parameters used to see the results of the action
1340/// info = additional description information
1341
31fefa63 1342 global $db, $CFG, $USER, $REMOTE_ADDR;
9fa49e22 1343
1344 if (isset($USER->realuser)) { // Don't log
1345 return;
1346 }
1347
61e96406 1348 $userid = empty($USER->id) ? "" : $USER->id;
1349
9fa49e22 1350 $timenow = time();
1351 $info = addslashes($info);
1352
31fefa63 1353 $result = $db->Execute("INSERT INTO {$CFG->prefix}log (time,
ebc3bd2b 1354 userid,
1355 course,
1356 ip,
1357 module,
1358 action,
1359 url,
1360 info)
1361 VALUES ('$timenow',
61e96406 1362 '$userid',
ebc3bd2b 1363 '$course',
1364 '$REMOTE_ADDR',
1365 '$module',
1366 '$action',
1367 '$url',
1368 '$info')");
1369
ce78926d 1370 if (!$result and ($CFG->debug > 7)) {
9fa49e22 1371 echo "<P>Error: Could not insert a new entry to the Moodle log</P>"; // Don't throw an error
1372 }
1373}
1374
1375
1376function get_logs($select, $order) {
1377 global $CFG;
1378
1379 return get_records_sql("SELECT l.*, u.firstname, u.lastname, u.picture
1380 FROM {$CFG->prefix}log l,
1381 {$CFG->prefix}user u
1382 $select $order");
1383}
1384
1385function get_logs_usercourse($userid, $courseid, $coursestart) {
1386 global $CFG;
1387
da0c90c3 1388 if ($courseid) {
1389 $courseselect = " AND course = '$courseid' ";
1390 }
1391
9fa49e22 1392 return get_records_sql("SELECT floor((`time` - $coursestart)/86400) as day, count(*) as num
1393 FROM {$CFG->prefix}log
ebc3bd2b 1394 WHERE userid = '$userid'
da0c90c3 1395 AND `time` > '$coursestart' $courseselect
9fa49e22 1396 GROUP BY day ");
1397}
1398
1399function get_logs_userday($userid, $courseid, $daystart) {
1400 global $CFG;
1401
7e4a6488 1402 if ($courseid) {
1403 $courseselect = " AND course = '$courseid' ";
1404 }
1405
9fa49e22 1406 return get_records_sql("SELECT floor((`time` - $daystart)/3600) as hour, count(*) as num
1407 FROM {$CFG->prefix}log
ebc3bd2b 1408 WHERE userid = '$userid'
7e4a6488 1409 AND `time` > '$daystart' $courseselect
9fa49e22 1410 GROUP BY hour ");
1411}
1412
a3fb1c45 1413/// GENERAL HELPFUL THINGS ///////////////////////////////////
1414
1415function print_object($object) {
1416/// Mostly just for debugging
1417
2b051f1c 1418 echo "<PRE>";
1419 print_r($object);
1420 echo "</PRE>";
a3fb1c45 1421}
1422
1423
9fa49e22 1424
9d5b689c 1425// vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140:
df28d6c5 1426?>