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