lib/datalib.php

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