lib/datalib.php

   1 <?PHP // $Id$
   2
   3 /// FUNCTIONS FOR DATABASE HANDLING  ////////////////////////////////
   4 /**
   5 * execute a given sql command string
   6 *
   7 * Completely general function - it just runs some SQL and reports success.
   8 *
   9 * @param        type description
  10 */
  11 function execute_sql($command, $feedback=true) {
  12 /// Completely general function - it just runs some SQL and reports success.
  13
  14     global $db;
  15
  16     $result = $db->Execute("$command");
  17
  18     if ($result) {
  19         if ($feedback) {
  20             echo "<P><FONT COLOR=green><B>".get_string("success")."</B></FONT></P>";
  21         }
  22         return true;
  23     } else {
  24         if ($feedback) {
  25             echo "<P><FONT COLOR=red><B>".get_string("error")."</B></FONT></P>";
  26         }
  27         return false;
  28     }
  29 }
  30 /**
  31 * Run an arbitrary sequence of semicolon-delimited SQL commands
  32 *
  33 * Assumes that the input text (file or string consists of
  34 * a number of SQL statements ENDING WITH SEMICOLONS.  The
  35 * semicolons MUST be the last character in a line.
  36 * Lines that are blank or that start with "#" are ignored.
  37 * Only tested with mysql dump files (mysqldump -p -d moodle)
  38 *
  39 * @param        type description
  40 */
  41
  42 function modify_database($sqlfile="", $sqlstring="") {
  43
  44     global $CFG;
  45
  46     $success = true;  // Let's be optimistic :-)
  47
  48     if (!empty($sqlfile)) {
  49         if (!is_readable($sqlfile)) {
  50             $success = false;
  51             echo "<P>Tried to modify database, but \"$sqlfile\" doesn't exist!</P>";
  52             return $success;
  53         } else {
  54             $lines = file($sqlfile);
  55         }
  56     } else {
  57         $lines[] = $sqlstring;
  58     }
  59
  60     $command = "";
  61
  62     foreach ($lines as $line) {
  63         $line = rtrim($line);
  64         $length = strlen($line);
  65
  66         if ($length and $line[0] <> "#") {
  67             if (substr($line, $length-1, 1) == ";") {
  68                 $line = substr($line, 0, $length-1);   // strip ;
  69                 $command .= $line;
  70                 $command = str_replace("prefix_", $CFG->prefix, $command); // Table prefixes
  71                 if (! execute_sql($command)) {
  72                     $success = false;
  73                 }
  74                 $command = "";
  75             } else {
  76                 $command .= $line;
  77             }
  78         }
  79     }
  80
  81     return $success;
  82
  83 }
  84
  85 /// FUNCTIONS TO MODIFY TABLES ////////////////////////////////////////////
  86
  87 /**
  88 * Add a new field to a table, or modify an existing one (if oldfield is defined).
  89 *
  90 * Add a new field to a table, or modify an existing one (if oldfield is defined).
  91 *
  92 * @param        type description
  93 */
  94
  95 function table_column($table, $oldfield, $field, $type="integer", $size="10",
  96                       $signed="unsigned", $default="0", $null="not null", $after="") {
  97     global $CFG, $db;
  98
  99     switch (strtolower($CFG->dbtype)) {
 100
 101         case "mysql":
 102         case "mysqlt":
 103
 104             switch (strtolower($type)) {
 105                 case "text":
 106                     $type = "TEXT";
 107                     $signed = "";
 108                     break;
 109                 case "integer":
 110                     $type = "INTEGER($size)";
 111                     break;
 112                 case "varchar":
 113                     $type = "VARCHAR($size)";
 114                     $signed = "";
 115                     break;
 116             }
 117
 118             if (!empty($oldfield)) {
 119                 $operation = "CHANGE $oldfield $field";
 120             } else {
 121                 $operation = "ADD $field";
 122             }
 123
 124             $default = "DEFAULT '$default'";
 125
 126             if (!empty($after)) {
 127                 $after = "AFTER `$after`";
 128             }
 129
 130             return execute_sql("ALTER TABLE {$CFG->prefix}$table $operation $type $signed $default $null $after");
 131             break;
 132
 133         case "postgres7":        // From Petri Asikainen
 134             //Check db-version
 135             $dbinfo = $db->ServerInfo();
 136             $dbver = substr($dbinfo['version'],0,3);
 137
 138             //to prevent conflicts with reserved words
 139             $field = "\"$field\"";
 140             $oldfield = "\"$oldfield\"";
 141
 142             switch (strtolower($type)) {
 143                 case "integer":
 144                     if ($size <= 2) {
 145                         $type = "INT2";
 146                     }
 147                     if ($size <= 4) {
 148                         $type = "INT";
 149                     }
 150                     if  ($size > 4) {
 151                         $type = "INT8";
 152                     }
 153                     break;
 154                 case "varchar":
 155                     $type = "VARCHAR($size)";
 156                     break;
 157             }
 158
 159             $default = "'$default'";
 160
 161             //After is not implemented in postgesql
 162             //if (!empty($after)) {
 163             //    $after = "AFTER '$after'";
 164             //}
 165
 166             if ($oldfield != "\"\"") {
 167                 if ($field != $oldfield) {
 168                     execute_sql("ALTER TABLE {$CFG->prefix}$table RENAME COLUMN $oldfield TO $field");
 169                 }
 170             } else {
 171                 execute_sql("ALTER TABLE {$CFG->prefix}$table ADD COLUMN $field $type");
 172                 execute_sql("UPDATE {$CFG->prefix}$table SET $field=$default");
 173             }
 174
 175             if ($dbver >= "7.3") {
 176                 // modifying 'not null' is posible before 7.3
 177                 //update default values to table
 178                 if ($null == "NOT NULL") {
 179                     execute_sql("UPDATE {$CFG->prefix}$table SET $field=$default where $field IS NULL");
 180                     execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $null");
 181                 } else {
 182                     execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field DROP NOT NULL");
 183                 }
 184             }
 185
 186             return execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET DEFAULT $default");
 187
 188             break;
 189
 190         default:
 191             switch (strtolower($type)) {
 192                 case "integer":
 193                     $type = "INTEGER";
 194                     break;
 195                 case "varchar":
 196                     $type = "VARCHAR";
 197                     break;
 198             }
 199
 200             $default = "DEFAULT '$default'";
 201
 202             if (!empty($after)) {
 203                 $after = "AFTER $after";
 204             }
 205
 206             if (!empty($oldfield)) {
 207                 execute_sql("ALTER TABLE {$CFG->prefix}$table RENAME COLUMN $oldfield $field");
 208             } else {
 209                 execute_sql("ALTER TABLE {$CFG->prefix}$table ADD COLUMN $field $type");
 210             }
 211
 212             execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $null");
 213             return execute_sql("ALTER TABLE {$CFG->prefix}$table ALTER COLUMN $field SET $default");
 214             break;
 215
 216     }
 217 }
 218
 219
 220
 221 /// GENERIC FUNCTIONS TO CHECK AND COUNT RECORDS ////////////////////////////////////////
 222
 223 /**
 224 * Returns true or false depending on whether the specified record exists
 225 *
 226 * Returns true or false depending on whether the specified record exists
 227 *
 228 * @param        type description
 229 */
 230 function record_exists($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") {
 231
 232     global $CFG;
 233
 234     if ($field1) {
 235         $select = "WHERE $field1 = '$value1'";
 236         if ($field2) {
 237             $select .= " AND $field2 = '$value2'";
 238             if ($field3) {
 239                 $select .= " AND $field3 = '$value3'";
 240             }
 241         }
 242     } else {
 243         $select = "";
 244     }
 245
 246     return record_exists_sql("SELECT * FROM $CFG->prefix$table $select LIMIT 1");
 247 }
 248
 249
 250 /**
 251 * Returns true or false depending on whether the specified record exists
 252 *
 253 * The sql statement is provided as a string.
 254 *
 255 * @param        type description
 256 */
 257 function record_exists_sql($sql) {
 258
 259     global $db;
 260
 261     $rs = $db->Execute($sql);
 262     if (empty($rs)) return false;
 263
 264     if ( $rs->RecordCount() ) {
 265         return true;
 266     } else {
 267         return false;
 268     }
 269 }
 270
 271
 272 /**
 273 * Get all the records and count them
 274 *
 275 * Get all the records and count them
 276 *
 277 * @param        type description
 278 */
 279 function count_records($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") {
 280
 281     global $CFG;
 282
 283     if ($field1) {
 284         $select = "WHERE $field1 = '$value1'";
 285         if ($field2) {
 286             $select .= " AND $field2 = '$value2'";
 287             if ($field3) {
 288                 $select .= " AND $field3 = '$value3'";
 289             }
 290         }
 291     } else {
 292         $select = "";
 293     }
 294
 295     return count_records_sql("SELECT COUNT(*) FROM $CFG->prefix$table $select");
 296 }
 297
 298 /**
 299 * Get all the records and count them
 300 *
 301 * Get all the records and count them
 302 *
 303 * @param        type description
 304 *
 305 */
 306 function count_records_select($table, $select="") {
 307
 308     global $CFG;
 309
 310     if ($select) {
 311         $select = "WHERE $select";
 312     }
 313
 314     return count_records_sql("SELECT COUNT(*) FROM $CFG->prefix$table $select");
 315 }
 316
 317
 318 /**
 319 * Get all the records and count them
 320 *
 321 * The sql statement is provided as a string.
 322 *
 323 * @param        type description
 324 */
 325 function count_records_sql($sql) {
 326
 327     global $db;
 328
 329     $rs = $db->Execute("$sql");
 330     if (empty($rs)) return 0;
 331
 332     return $rs->fields[0];
 333 }
 334
 335
 336
 337
 338 /// GENERIC FUNCTIONS TO GET, INSERT, OR UPDATE DATA  ///////////////////////////////////
 339
 340 /**
 341 * Get a single record as an object
 342 *
 343 * Get a single record as an object
 344 *
 345 * @param        string  $table the name of the table to select from
 346 * @param        string  $field1 the name of the field for the first criteria
 347 * @param        string  $value1 the value of the field for the first criteria
 348 * @param        string  $field2 the name of the field for the second criteria
 349 * @param        string  $value2 the value of the field for the second criteria
 350 * @param        string  $field3 the name of the field for the third criteria
 351 * @param        string  $value3 the value of the field for the third criteria
 352 * @return       object(fieldset)        a fieldset object containing the first record selected
 353 */
 354 function get_record($table, $field1, $value1, $field2="", $value2="", $field3="", $value3="") {
 355
 356     global $CFG;
 357
 358     $select = "WHERE $field1 = '$value1'";
 359
 360     if ($field2) {
 361         $select .= " AND $field2 = '$value2'";
 362         if ($field3) {
 363             $select .= " AND $field3 = '$value3'";
 364         }
 365     }
 366
 367     return get_record_sql("SELECT * FROM $CFG->prefix$table $select");
 368 }
 369
 370 /**
 371 * Get a single record as an object
 372 *
 373 * The sql statement is provided as a string.
 374 * A LIMIT is normally added to only look for 1 record
 375 *
 376 * @param        type description
 377 */
 378 function get_record_sql($sql) {
 379
 380     global $db, $CFG;
 381
 382     if ($CFG->debug > 7) {    // Debugging mode - don't use limit
 383        $limit = "";
 384     } else {
 385        $limit = " LIMIT 1";    // Workaround - limit to one record
 386     }
 387
 388     if (!$rs = $db->Execute("$sql$limit")) {
 389         if ($CFG->debug > 7) {    // Debugging mode - print checks
 390             $db->debug=true;
 391             $db->Execute("$sql$limit");
 392             $db->debug=false;
 393         }
 394         return false;
 395     }
 396
 397     if (!$recordcount = $rs->RecordCount()) {
 398         return false;                 // Found no records
 399     }
 400
 401     if ($recordcount == 1) {          // Found one record
 402         return (object)$rs->fields;
 403
 404     } else {                          // Error: found more than one record
 405         notify("Error:  Turn off debugging to hide this error.");
 406         notify("$sql$limit");
 407         if ($records = $rs->GetAssoc(true)) {
 408             notify("Found more than one record in get_record_sql !");
 409             print_object($records);
 410         } else {
 411             notify("Very strange error in get_record_sql !");
 412             print_object($rs);
 413         }
 414         print_continue("$CFG->wwwroot/admin/config.php");
 415     }
 416 }
 417
 418 /**
 419 * Gets one record from a table, as an object
 420 *
 421 * "select" is a fragment of SQL to define the selection criteria
 422 *
 423 * @param        type description
 424 */
 425 function get_record_select($table, $select="", $fields="*") {
 426
 427     global $CFG;
 428
 429     if ($select) {
 430         $select = "WHERE $select";
 431     }
 432
 433     return get_record_sql("SELECT $fields FROM $CFG->prefix$table $select");
 434 }
 435
 436
 437 /**
 438 * Get a number of records as an array of objects
 439 *
 440 * Can optionally be sorted eg "time ASC" or "time DESC"
 441 * If "fields" is specified, only those fields are returned
 442 * The "key" is the first column returned, eg usually "id"
 443 * limitfrom and limitnum must both be specified or not at all
 444 *
 445 * @param        type description
 446 */
 447 function get_records($table, $field="", $value="", $sort="", $fields="*", $limitfrom="", $limitnum="") {
 448
 449     global $CFG;
 450
 451     if ($field) {
 452         $select = "WHERE $field = '$value'";
 453     } else {
 454         $select = "";
 455     }
 456
 457     if ($limitfrom !== "") {
 458         switch ($CFG->dbtype) {
 459             case "mysql":
 460                  $limit = "LIMIT $limitfrom,$limitnum";
 461                  break;
 462             case "postgres7":
 463                  $limit = "LIMIT $limitnum OFFSET $limitfrom";
 464                  break;
 465             default:
 466                  $limit = "LIMIT $limitnum,$limitfrom";
 467         }
 468     } else {
 469         $limit = "";
 470     }
 471
 472     if ($sort) {
 473         $sort = "ORDER BY $sort";
 474     }
 475
 476     return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort $limit");
 477 }
 478
 479 /**
 480 * Get a number of records as an array of objects
 481 *
 482 * Can optionally be sorted eg "time ASC" or "time DESC"
 483 * "select" is a fragment of SQL to define the selection criteria
 484 * The "key" is the first column returned, eg usually "id"
 485 * limitfrom and limitnum must both be specified or not at all
 486 *
 487 * @param        type description
 488 */
 489 function get_records_select($table, $select="", $sort="", $fields="*", $limitfrom="", $limitnum="") {
 490
 491     global $CFG;
 492
 493     if ($select) {
 494         $select = "WHERE $select";
 495     }
 496
 497     if ($limitfrom !== "") {
 498         switch ($CFG->dbtype) {
 499             case "mysql":
 500                  $limit = "LIMIT $limitfrom,$limitnum";
 501                  break;
 502             case "postgres7":
 503                  $limit = "LIMIT $limitnum OFFSET $limitfrom";
 504                  break;
 505             default:
 506                  $limit = "LIMIT $limitnum,$limitfrom";
 507         }
 508     } else {
 509         $limit = "";
 510     }
 511
 512     if ($sort) {
 513         $sort = "ORDER BY $sort";
 514     }
 515
 516     return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort $limit");
 517 }
 518
 519
 520 /**
 521 * Get a number of records as an array of objects
 522 *
 523 * Differs from get_records() in that the values variable
 524 * can be a comma-separated list of values eg  "4,5,6,10"
 525 * Can optionally be sorted eg "time ASC" or "time DESC"
 526 * The "key" is the first column returned, eg usually "id"
 527 *
 528 * @param        type description
 529 */
 530 function get_records_list($table, $field="", $values="", $sort="", $fields="*") {
 531
 532     global $CFG;
 533
 534     if ($field) {
 535         $select = "WHERE $field in ($values)";
 536     } else {
 537         $select = "";
 538     }
 539
 540     if ($sort) {
 541         $sort = "ORDER BY $sort";
 542     }
 543
 544     return get_records_sql("SELECT $fields FROM $CFG->prefix$table $select $sort");
 545 }
 546
 547
 548
 549 /**
 550 * Get a number of records as an array of objects
 551 *
 552 * The "key" is the first column returned, eg usually "id"
 553 * The sql statement is provided as a string.
 554 *
 555 * @param        type description
 556 */
 557 function get_records_sql($sql) {
 558
 559     global $db;
 560
 561     $rs = $db->Execute("$sql");
 562     if (empty($rs)) return false;
 563
 564     if ( $rs->RecordCount() > 0 ) {
 565         if ($records = $rs->GetAssoc(true)) {
 566             foreach ($records as $key => $record) {
 567                 $objects[$key] = (object) $record;
 568             }
 569             return $objects;
 570         } else {
 571             return false;
 572         }
 573     } else {
 574         return false;
 575     }
 576 }
 577
 578 /**
 579 * Get a number of records as an array of objects
 580 *
 581 * Can optionally be sorted eg "time ASC" or "time DESC"
 582 * If "fields" is specified, only those fields are returned
 583 * The "key" is the first column returned, eg usually "id"
 584 *
 585 * @param        type description
 586 */
 587 function get_records_menu($table, $field="", $value="", $sort="", $fields="*") {
 588
 589     global $CFG;
 590
 591     if ($field) {
 592         $select = "WHERE $field = '$value'";
 593     } else {
 594         $select = "";
 595     }
 596
 597     if ($sort) {
 598         $sort = "ORDER BY $sort";
 599     }
 600
 601     return get_records_sql_menu("SELECT $fields FROM $CFG->prefix$table $select $sort");
 602 }
 603
 604 /**
 605 * Get a number of records as an array of objects
 606 *
 607 * Can optionally be sorted eg "time ASC" or "time DESC"
 608 * "select" is a fragment of SQL to define the selection criteria
 609 * Returns associative array of first two fields
 610 *
 611 * @param        type description
 612 */
 613 function get_records_select_menu($table, $select="", $sort="", $fields="*") {
 614
 615     global $CFG;
 616
 617     if ($select) {
 618         $select = "WHERE $select";
 619     }
 620
 621     if ($sort) {
 622         $sort = "ORDER BY $sort";
 623     }
 624
 625     return get_records_sql_menu("SELECT $fields FROM $CFG->prefix$table $select $sort");
 626 }
 627
 628
 629 /**
 630 * Given an SQL select, this function returns an associative
 631 *
 632 * array of the first two columns.  This is most useful in
 633 * combination with the choose_from_menu function to create
 634 * a form menu.
 635 *
 636 * @param        type description
 637 */
 638 function get_records_sql_menu($sql) {
 639
 640     global $db;
 641
 642     $rs = $db->Execute("$sql");
 643     if (empty($rs)) return false;
 644
 645     if ( $rs->RecordCount() > 0 ) {
 646         while (!$rs->EOF) {
 647             $menu[$rs->fields[0]] = $rs->fields[1];
 648             $rs->MoveNext();
 649         }
 650         return $menu;
 651
 652     } else {
 653         return false;
 654     }
 655 }
 656
 657 /**
 658 * Get a single field from a database record
 659 *
 660 * longdesc
 661 *
 662 * @param        type description
 663 */
 664 function get_field($table, $return, $field1, $value1, $field2="", $value2="", $field3="", $value3="") {
 665
 666     global $db, $CFG;
 667
 668     $select = "WHERE $field1 = '$value1'";
 669
 670     if ($field2) {
 671         $select .= " AND $field2 = '$value2'";
 672         if ($field3) {
 673             $select .= " AND $field3 = '$value3'";
 674         }
 675     }
 676
 677     $rs = $db->Execute("SELECT $return FROM $CFG->prefix$table $select");
 678     if (empty($rs)) return false;
 679
 680     if ( $rs->RecordCount() == 1 ) {
 681         return $rs->fields["$return"];
 682     } else {
 683         return false;
 684     }
 685 }
 686
 687 /**
 688 * Set a single field in a database record
 689 *
 690 * longdesc
 691 *
 692 * @param        type description
 693 */
 694 function set_field($table, $newfield, $newvalue, $field1, $value1, $field2="", $value2="", $field3="", $value3="") {
 695
 696     global $db, $CFG;
 697
 698     $select = "WHERE $field1 = '$value1'";
 699
 700     if ($field2) {
 701         $select .= " AND $field2 = '$value2'";
 702         if ($field3) {
 703             $select .= " AND $field3 = '$value3'";
 704         }
 705     }
 706
 707     return $db->Execute("UPDATE $CFG->prefix$table SET $newfield = '$newvalue' $select");
 708 }
 709
 710
 711 /**
 712 * Delete one or more records from a table
 713 *
 714 * Delete one or more records from a table
 715 *
 716 * @param        type description
 717 */
 718 function delete_records($table, $field1="", $value1="", $field2="", $value2="", $field3="", $value3="") {
 719
 720     global $db, $CFG;
 721
 722     if ($field1) {
 723         $select = "WHERE $field1 = '$value1'";
 724         if ($field2) {
 725             $select .= " AND $field2 = '$value2'";
 726             if ($field3) {
 727                 $select .= " AND $field3 = '$value3'";
 728             }
 729         }
 730     } else {
 731         $select = "";
 732     }
 733
 734     return $db->Execute("DELETE FROM $CFG->prefix$table $select");
 735 }
 736
 737 /**
 738 * Delete one or more records from a table
 739 *
 740 * "select" is a fragment of SQL to define the selection criteria
 741 *
 742 * @param        type description
 743 */
 744 function delete_records_select($table, $select="") {
 745
 746     global $CFG, $db;
 747
 748     if ($select) {
 749         $select = "WHERE $select";
 750     }
 751
 752     return $db->Execute("DELETE FROM $CFG->prefix$table $select");
 753 }
 754
 755
 756 /**
 757 * Insert a record into a table and return the "id" field if required
 758 *
 759 * If the return ID isn't required, then this just reports success as true/false.
 760 * $dataobject is an object containing needed data
 761 *
 762 * @param        type description
 763 */
 764 function insert_record($table, $dataobject, $returnid=true, $primarykey='id') {
 765
 766     global $db, $CFG;
 767
 768 /// Execute a dummy query to get an empty recordset
 769     if (!$rs = $db->Execute("SELECT * FROM $CFG->prefix$table WHERE $primarykey ='-1'")) {
 770         return false;
 771     }
 772
 773 /// Get the correct SQL from adoDB
 774     if (!$insertSQL = $db->GetInsertSQL($rs, (array)$dataobject, true)) {
 775         return false;
 776     }
 777
 778 /// Run the SQL statement
 779     if (!$rs = $db->Execute($insertSQL)) {
 780         return false;
 781     }
 782
 783 /// If a return ID is not needed then just return true now
 784     if (!$returnid) {
 785         return true;
 786     }
 787
 788 /// Find the return ID of the newly inserted record
 789     switch ($CFG->dbtype) {
 790         case "postgres7":             // Just loves to be special
 791             $oid = $db->Insert_ID();
 792             if ($rs = $db->Execute("SELECT $primarykey FROM $CFG->prefix$table WHERE oid = $oid")) {
 793                 if ($rs->RecordCount() == 1) {
 794                     return (integer) $rs->fields[0];
 795                 }
 796             }
 797             return false;
 798
 799         default:
 800             return $db->Insert_ID();  // Should work on most databases, but not all!
 801     }
 802 }
 803
 804
 805 /**
 806 * Update a record in a table
 807 *
 808 * $dataobject is an object containing needed data
 809 * Relies on $dataobject having a variable "id" to
 810 * specify the record to update
 811 *
 812 * @param        type description
 813 */
 814 function update_record($table, $dataobject) {
 815
 816     global $db, $CFG;
 817
 818     if (! isset($dataobject->id) ) {
 819         return false;
 820     }
 821
 822     // Determine all the fields in the table
 823     if (!$columns = $db->MetaColumns("$CFG->prefix$table")) {
 824         return false;
 825     }
 826     $data = (array)$dataobject;
 827
 828     // Pull out data matching these fields
 829     foreach ($columns as $column) {
 830         if ($column->name <> "id" and isset($data[$column->name]) ) {
 831             $ddd[$column->name] = $data[$column->name];
 832         }
 833     }
 834
 835     // Construct SQL queries
 836     $numddd = count($ddd);
 837     $count = 0;
 838     $update = "";
 839
 840     foreach ($ddd as $key => $value) {
 841         $count++;
 842         $update .= "$key = '$value'";
 843         if ($count < $numddd) {
 844             $update .= ", ";
 845         }
 846     }
 847
 848     if ($rs = $db->Execute("UPDATE $CFG->prefix$table SET $update WHERE id = '$dataobject->id'")) {
 849         return true;
 850     } else {
 851         return false;
 852     }
 853 }
 854
 855
 856
 857
 858 /// USER DATABASE ////////////////////////////////////////////////
 859
 860 /**
 861 * Get a complete user record, which includes all the info
 862 *
 863 * in the user record, as well as membership information
 864 * Suitable for setting as $USER session cookie.
 865 *
 866 * @param        type description
 867 */
 868 function get_user_info_from_db($field, $value) {
 869
 870     if (!$field or !$value) {
 871         return false;
 872     }
 873
 874     if (! $user = get_record_select("user", "$field = '$value' AND deleted <> '1'")) {
 875         return false;
 876     }
 877
 878     // Add membership information
 879
 880     if ($site = get_site()) { // Everyone is always a member of the top course
 881         $user->student[$site->id] = true;
 882     }
 883
 884     if ($students = get_records("user_students", "userid", $user->id)) {
 885         foreach ($students as $student) {
 886             if (get_field("course", "visible", "id", $student->course)) {
 887                 $user->student[$student->course] = true;
 888                 $user->zoom[$student->course] = $student->zoom;
 889             }
 890         }
 891     }
 892
 893     if ($teachers = get_records("user_teachers", "userid", $user->id)) {
 894         foreach ($teachers as $teacher) {
 895             $user->teacher[$teacher->course] = true;
 896             if ($teacher->editall) {
 897                 $user->teacheredit[$teacher->course] = true;
 898             }
 899         }
 900     }
 901
 902     if ($admins = get_records("user_admins", "userid", $user->id)) {
 903         $user->admin = true;
 904     }
 905
 906     if ($displays = get_records("course_display", "userid", $user->id)) {
 907         foreach ($displays as $display) {
 908             $user->display[$display->course] = $display->display;
 909         }
 910     }
 911
 912     if ($groups = get_records("groups_members", "userid", $user->id)) {
 913         foreach ($groups as $groupmember) {
 914             $courseid = get_field("groups", "courseid", "id", $groupmember->groupid);
 915             $user->groupmember[$courseid] = $groupmember->groupid;
 916         }
 917     }
 918
 919     if ($preferences = get_records('user_preferences', 'userid', $user->id)) {
 920         foreach ($preferences as $preference) {
 921             $user->preference[$preference->name] = $preference->value;
 922         }
 923     }
 924
 925     return $user;
 926 }
 927
 928 /**
 929 * Updates user record to record their last access
 930 *
 931 * longdesc
 932 *
 933 */
 934 function update_user_in_db() {
 935
 936    global $db, $USER, $REMOTE_ADDR, $CFG;
 937
 938    if (!isset($USER->id))
 939        return false;
 940
 941    $timenow = time();
 942    if ($db->Execute("UPDATE {$CFG->prefix}user SET lastIP='$REMOTE_ADDR', lastaccess='$timenow'
 943                      WHERE id = '$USER->id' ")) {
 944        return true;
 945    } else {
 946        return false;
 947    }
 948 }
 949
 950
 951 /**
 952 * Does this username and password specify a valid admin user?
 953 *
 954 * longdesc
 955 *
 956 * @param        type description
 957 */
 958 function adminlogin($username, $md5password) {
 959
 960     global $CFG;
 961
 962     return record_exists_sql("SELECT u.id
 963                                 FROM {$CFG->prefix}user u,
 964                                      {$CFG->prefix}user_admins a
 965                               WHERE u.id = a.userid
 966                                 AND u.username = '$username'
 967                                 AND u.password = '$md5password'");
 968 }
 969
 970
 971 /**
 972 * Get the guest user information from the database
 973 *
 974 * longdesc
 975 *
 976 * @param        type description
 977 */
 978 function get_guest() {
 979     return get_user_info_from_db("username", "guest");
 980 }
 981
 982
 983 /**
 984 * Returns $user object of the main admin user
 985 *
 986 * longdesc
 987 *
 988 * @param        type description
 989 */
 990 function get_admin () {
 991
 992     global $CFG;
 993
 994     if ( $admins = get_admins() ) {
 995         foreach ($admins as $admin) {
 996             return $admin;   // ie the first one
 997         }
 998     } else {
 999         return false;
1000     }
1001 }
1002
1003 /**
1004 * Returns list of all admins
1005 *
1006 * longdesc
1007 *
1008 * @param        type description
1009 */
1010 function get_admins() {
1011
1012     global $CFG;
1013
1014     return get_records_sql("SELECT u.*
1015                               FROM {$CFG->prefix}user u,
1016                                    {$CFG->prefix}user_admins a
1017                              WHERE a.userid = u.id
1018                              ORDER BY u.id ASC");
1019 }
1020
1021 /**
1022 * Returns list of all creators
1023 *
1024 * longdesc
1025 *
1026 * @param        type description
1027 */
1028 function get_creators() {
1029
1030     global $CFG;
1031
1032     return get_records_sql("SELECT u.*
1033                               FROM {$CFG->prefix}user u,
1034                                    {$CFG->prefix}user_coursecreators a
1035                              WHERE a.userid = u.id
1036                              ORDER BY u.id ASC");
1037 }
1038
1039 /**
1040 * Returns $user object of the main teacher for a course
1041 *
1042 * longdesc
1043 *
1044 * @param        type description
1045 */
1046 function get_teacher($courseid) {
1047
1048     global $CFG;
1049
1050     if ( $teachers = get_course_teachers($courseid, "t.authority ASC")) {
1051         foreach ($teachers as $teacher) {
1052             if ($teacher->authority) {
1053                 return $teacher;   // the highest authority teacher
1054             }
1055         }
1056     } else {
1057         return false;
1058     }
1059 }
1060
1061 /**
1062 * Searches logs to find all enrolments since a certain date
1063 *
1064 * used to print recent activity
1065 *
1066 * @param        type description
1067 */
1068 function get_recent_enrolments($courseid, $timestart) {
1069
1070     global $CFG;
1071
1072     return get_records_sql("SELECT u.id, u.firstname, u.lastname
1073                             FROM {$CFG->prefix}user u,
1074                                  {$CFG->prefix}user_students s,
1075                                  {$CFG->prefix}log l
1076                             WHERE l.time > '$timestart'
1077                               AND l.course = '$courseid'
1078                               AND l.module = 'course'
1079                               AND l.action = 'enrol'
1080                               AND l.info = u.id
1081                               AND u.id = s.userid
1082                               AND s.course = '$courseid'
1083                               GROUP BY l.info
1084                               ORDER BY l.time ASC");
1085 }
1086
1087 /**
1088 * Returns list of all students in this course
1089 *
1090 * @param        type description
1091 */
1092 function get_course_students($courseid, $sort="s.timeaccess", $dir="", $page=0, $recordsperpage=99999,
1093                              $firstinitial="", $lastinitial="", $group=NULL) {
1094
1095     global $CFG;
1096
1097     switch ($CFG->dbtype) {
1098         case "mysql":
1099              $limit = "LIMIT $page,$recordsperpage";
1100              $LIKE = "LIKE";
1101              break;
1102         case "postgres7":
1103              $limit = "LIMIT $recordsperpage OFFSET ".($page);
1104              $LIKE = "ILIKE";
1105              break;
1106         default:
1107              $limit = "LIMIT $recordsperpage,$page";
1108              $LIKE = "ILIKE";
1109     }
1110
1111     $groupmembers = '';
1112     $select = "s.course = '$courseid' AND s.userid = u.id AND u.deleted = '0' ";
1113
1114     if ($firstinitial) {
1115         $select .= " AND u.firstname $LIKE '$firstinitial%' ";
1116     }
1117
1118     if ($lastinitial) {
1119         $select .= " AND u.lastname $LIKE '$lastinitial%' ";
1120     }
1121
1122     if ($group === 0) {   /// Need something here to get all students not in a group
1123         return array();
1124
1125     } else if ($group !== NULL) {
1126         $groupmembers = ", {$CFG->prefix}groups_members gm ";
1127         $select .= " AND u.id = gm.userid AND gm.groupid = '$group'";
1128     }
1129
1130     if ($sort) {
1131         $sort = " ORDER BY $sort ";
1132     }
1133
1134     return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,
1135                             u.email, u.city, u.country, u.lastlogin, u.picture, u.department, u.institution,
1136                             u.emailstop, u.lang, u.timezone, s.timeaccess as lastaccess
1137                             FROM {$CFG->prefix}user u,
1138                                  {$CFG->prefix}user_students s $groupmembers
1139                             WHERE $select $sort $dir $limit");
1140 }
1141
1142 /**
1143 * Counts the students in a given course, or a subset of them
1144 *
1145 * @param        type description
1146 */
1147 function count_course_students($course, $search="", $firstinitial="", $lastinitial="", $group=NULL) {
1148
1149     global $CFG;
1150
1151     switch ($CFG->dbtype) {
1152         case "mysql":
1153              $LIKE = "LIKE";
1154              break;
1155         default:
1156              $LIKE = "ILIKE";
1157     }
1158
1159     $groupmembers = "";
1160     $select = "s.course = '$course->id' AND s.userid = u.id AND u.deleted = '0'";
1161
1162     if ($search) {
1163         $select .= " AND u.firstname $LIKE '%$search%' OR u.lastname $LIKE '%$search%'";
1164     }
1165     if ($firstinitial) {
1166         $select .= " AND u.firstname $LIKE '$firstinitial%'";
1167     }
1168     if ($lastinitial) {
1169         $select .= " AND u.lastname $LIKE '$lastinitial%'";
1170     }
1171
1172     if ($group === 0) {   /// Need something here to get all students not in a group
1173         return 0;
1174
1175     } else if ($group !== NULL) {
1176         $groupmembers = ", {$CFG->prefix}groups_members gm ";
1177         $select .= " AND u.id = gm.userid AND gm.groupid = '$group'";
1178     }
1179
1180     return count_records_sql("SELECT COUNT(*) FROM {$CFG->prefix}user u,
1181                                                    {$CFG->prefix}user_students s $groupmembers
1182                                                    WHERE $select");
1183
1184 }
1185
1186
1187 /**
1188 * Returns list of all teachers in this course
1189 *
1190 * @param        type description
1191 */
1192 function get_course_teachers($courseid, $sort="t.authority ASC") {
1193
1194     global $CFG;
1195
1196     return get_records_sql("SELECT u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,
1197                                    u.email, u.city, u.country, u.lastlogin, u.picture, u.lang, u.timezone,
1198                                    u.emailstop, t.authority,t.role,t.editall,t.timeaccess as lastaccess
1199                             FROM {$CFG->prefix}user u,
1200                                  {$CFG->prefix}user_teachers t
1201                             WHERE t.course = '$courseid' AND t.userid = u.id AND u.deleted = '0'
1202                             ORDER BY $sort");
1203 }
1204
1205 /**
1206 * Returns all the users of a course: students and teachers
1207 *
1208 * If the "course" is actually the site, then return all site users.
1209 *
1210 * @param        type description
1211 */
1212 function get_course_users($courseid, $sort="timeaccess DESC") {
1213
1214     $site = get_site();
1215
1216     if ($courseid == $site->id) {
1217         return get_site_users($sort);
1218     }
1219
1220     /// Using this method because the single SQL just would not always work!
1221
1222     $users = array();
1223
1224     $teachers = get_course_teachers($courseid, $sort);
1225     $students = get_course_students($courseid, $sort);
1226
1227     if ($teachers and $students) {
1228         foreach ($students as $student) {
1229             $users[$student->id] = $student;
1230         }
1231         foreach ($teachers as $teacher) {
1232             $users[$teacher->id] = $teacher;
1233         }
1234         return $users;
1235
1236     } else if ($teachers) {
1237         return $teachers;
1238
1239     } else if ($students) {
1240         return $students;
1241     }
1242
1243     return $users;
1244
1245     /// This doesn't work ... why not?
1246     ///    return get_records_sql("SELECT u.* FROM user u, user_students s, user_teachers t
1247     ///                            WHERE (s.course = '$courseid' AND s.userid = u.id) OR
1248     ///                                  (t.course = '$courseid' AND t.userid = u.id)
1249     ///                            ORDER BY $sort");
1250 }
1251
1252
1253 /**
1254 * Returns a list of all active users who are enrolled
1255 *
1256 * or teaching in courses on this server
1257 *
1258 * @param        type description
1259 */
1260 function get_site_users($sort="u.lastaccess DESC", $select="") {
1261
1262     global $CFG, $db;
1263
1264
1265     if ($select) {
1266         $selectinfo = $select;
1267     } else {
1268         $selectinfo = "u.id, u.username, u.firstname, u.lastname, u.maildisplay, u.mailformat,".
1269                       "u.email, u.emailstop, u.city, u.country, u.lastaccess, u.lastlogin, u.picture, u.lang, u.timezone";
1270     }
1271
1272
1273     if (!$students = get_records_sql("SELECT $selectinfo from {$CFG->prefix}user u, {$CFG->prefix}user_students s
1274                                       WHERE s.userid = u.id GROUP BY u.id ORDER BY $sort")) {
1275         $students = array();
1276     }
1277     if (!$teachers = get_records_sql("SELECT $selectinfo from {$CFG->prefix}user u, {$CFG->prefix}user_teachers t
1278                                       WHERE t.userid = u.id GROUP BY u.id ORDER BY $sort")) {
1279         $teachers = array();
1280     }
1281     if (!$admins = get_records_sql("SELECT $selectinfo from {$CFG->prefix}user u, {$CFG->prefix}user_admins a
1282                                       WHERE a.userid = u.id GROUP BY u.id ORDER BY $sort")) {
1283         $admins = array();
1284     }
1285     $users = array_merge($teachers, $students);
1286     $users = array_merge($users, $admins);
1287     return $users;
1288 }
1289
1290
1291 /**
1292 * Returns a subset of users
1293 *
1294 * longdesc
1295 *
1296 * @param        bookean $get    if false then only a count of the records is returned
1297 * @param        string  $search a simple string to search for
1298 * @param        boolean $confirmed      a switch to allow/disallow unconfirmed users
1299 * @param        array(int)      $exceptions     a list of IDs to ignore, eg 2,4,5,8,9,10
1300 * @param        string  $sort   a SQL snippet for the sorting criteria to use
1301 */
1302 function get_users($get=true, $search="", $confirmed=false, $exceptions="", $sort="firstname ASC") {
1303
1304     global $CFG;
1305
1306     switch ($CFG->dbtype) {
1307         case "mysql":
1308              $fullname = " CONCAT(firstname,\" \",lastname) ";
1309              $LIKE = "LIKE";
1310              break;
1311         case "postgres7":
1312              $fullname = " firstname||\" \"||lastname ";
1313              $LIKE = "ILIKE";
1314              break;
1315         default:
1316              $fullname = " firstname||\" \"||lastname ";
1317              $LIKE = "ILIKE";
1318     }
1319
1320     if ($search) {
1321         $search = " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
1322     }
1323
1324     if ($confirmed) {
1325         $confirmed = " AND confirmed = '1' ";
1326     }
1327
1328     if ($exceptions) {
1329         $exceptions = " AND id NOT IN ($exceptions) ";
1330     }
1331
1332     if ($sort and $get) {
1333         $sort = " ORDER BY $sort ";
1334     } else {
1335         $sort = "";
1336     }
1337
1338     if ($get) {
1339         return get_records_select("user", "username <> 'guest' AND deleted = 0 $search $confirmed $exceptions $sort");
1340     } else {
1341         return count_records_select("user", "username <> 'guest' AND deleted = 0 $search $confirmed $exceptions $sort");
1342     }
1343 }
1344
1345
1346 /**
1347 * shortdesc
1348 *
1349 * longdesc
1350 *
1351 * @param        type description
1352 */
1353 function get_users_listing($sort, $dir="ASC", $page=1, $recordsperpage=20, $search="") {
1354     global $CFG;
1355
1356     switch ($CFG->dbtype) {
1357         case "mysql":
1358              $limit = "LIMIT $page,$recordsperpage";
1359              $fullname = " CONCAT(firstname,\" \",lastname) ";
1360              $LIKE = "LIKE";
1361              break;
1362         case "postgres7":
1363              $limit = "LIMIT $recordsperpage OFFSET ".($page);
1364              $fullname = " firstname||\" \"||lastname ";
1365              $LIKE = "ILIKE";
1366              break;
1367         default:
1368              $limit = "LIMIT $recordsperpage,$page";
1369              $fullname = " firstname||\" \"||lastname ";
1370              $LIKE = "LIKE";
1371     }
1372
1373     if ($search) {
1374         $search = " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
1375     }
1376
1377     return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess
1378                               FROM {$CFG->prefix}user
1379                              WHERE username <> 'guest'
1380                                AND deleted <> 1 and confirmed = 1 $search
1381                           ORDER BY $sort $dir $limit");
1382
1383 }
1384
1385 /**
1386 * shortdesc
1387 *
1388 * longdesc
1389 *
1390 * @param        type description
1391 */
1392 function get_users_confirmed() {
1393     global $CFG;
1394     return get_records_sql("SELECT *
1395                               FROM {$CFG->prefix}user
1396                              WHERE confirmed = 1
1397                                AND deleted = 0
1398                                AND username <> 'guest'
1399                                AND username <> 'changeme'");
1400 }
1401
1402
1403 /**
1404 * shortdesc
1405 *
1406 * longdesc
1407 *
1408 * @param        type description
1409 */
1410 function get_users_unconfirmed($cutofftime=2000000000) {
1411     global $CFG;
1412     return get_records_sql("SELECT *
1413                              FROM {$CFG->prefix}user
1414                             WHERE confirmed = 0
1415                               AND firstaccess > 0
1416                               AND firstaccess < '$cutofftime'");
1417 }
1418
1419
1420 /**
1421 * shortdesc
1422 *
1423 * longdesc
1424 *
1425 * @param        type description
1426 */
1427 function get_users_longtimenosee($cutofftime) {
1428     global $CFG;
1429     return get_records_sql("SELECT DISTINCT *
1430                               FROM {$CFG->prefix}user_students
1431                              WHERE timeaccess > '0'
1432                                AND timeaccess < '$cutofftime' ");
1433 }
1434
1435 /**
1436 * Returns an array of group objects that the user is a member of
1437 * in the given course.  If userid isn't specified, then return a
1438 * list of all groups in the course.
1439 *
1440 * @param        type description
1441 */
1442 function get_groups($courseid, $userid=0) {
1443     global $CFG;
1444
1445     if ($userid) {
1446         $dbselect = ", {$CFG->prefix}groups_members m";
1447         $userselect = "AND m.groupid = g.id AND m.userid = '$userid'";
1448     } else {
1449         $dbselect = '';
1450         $userselect = '';
1451     }
1452
1453     return get_records_sql("SELECT DISTINCT g.*
1454                               FROM {$CFG->prefix}groups g $dbselect
1455                              WHERE g.courseid = '$courseid' $userselect ");
1456 }
1457
1458
1459 /**
1460 * Returns an array of user objects
1461 *
1462 * @param        type description
1463 */
1464 function get_users_in_group($groupid, $sort="u.lastaccess DESC") {
1465     global $CFG;
1466     return get_records_sql("SELECT DISTINCT u.*
1467                               FROM {$CFG->prefix}user u,
1468                                    {$CFG->prefix}groups_members m
1469                              WHERE m.groupid = '$groupid'
1470                                AND m.userid = u.id
1471                           ORDER BY $sort");
1472 }
1473
1474 /**
1475 * An efficient way of finding all the users who aren't in groups yet
1476 *
1477 * @param        type description
1478 */
1479 function get_users_not_in_group($courseid) {
1480     global $CFG;
1481
1482     return array();     /// XXX TO BE DONE
1483 }
1484
1485 /**
1486 * Returns the user's group in a particular course
1487 *
1488 * @param        type description
1489 */
1490 function user_group($courseid, $userid) {
1491     global $CFG;
1492
1493     return get_record_sql("SELECT g.*
1494                              FROM {$CFG->prefix}groups g,
1495                                   {$CFG->prefix}groups_members m
1496                              WHERE g.courseid = '$courseid'
1497                                AND g.id = m.groupid
1498                                AND m.userid = '$userid'");
1499 }
1500
1501
1502
1503
1504 /// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
1505
1506
1507 /**
1508 * Returns $course object of the top-level site.
1509 *
1510 * Returns $course object of the top-level site.
1511 *
1512 * @param        type description
1513 */
1514 function get_site () {
1515
1516     if ( $course = get_record("course", "category", 0)) {
1517         return $course;
1518     } else {
1519         return false;
1520     }
1521 }
1522
1523
1524 /**
1525 * Returns list of courses, for whole site, or category
1526 *
1527 * Returns list of courses, for whole site, or category
1528 *
1529 * @param        type description
1530 */
1531 function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {
1532
1533     global $USER, $CFG;
1534
1535     $categoryselect = "";
1536     if ($categoryid != "all") {
1537         $categoryselect = "c.category = '$categoryid'";
1538     }
1539
1540     $teachertable = "";
1541     $visiblecourses = "";
1542     if (!empty($USER)) {  // May need to check they are a teacher
1543         if (!iscreator()) {
1544             $visiblecourses = "AND ((c.visible > 0) OR (t.userid = '$USER->id' AND t.course = c.id))";
1545             $teachertable = ", {$CFG->prefix}user_teachers t";
1546         }
1547     } else {
1548         $visiblecourses = "AND c.visible > 0";
1549     }
1550
1551     if ($categoryselect or $visiblecourses) {
1552         $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
1553     } else {
1554         $selectsql = "{$CFG->prefix}course c $teachertable";
1555     }
1556
1557
1558     return get_records_sql("SELECT DISTINCT $fields FROM $selectsql ORDER BY $sort");
1559 }
1560
1561
1562 /**
1563 * Returns list of courses, for whole site, or category
1564 *
1565 * Similar to get_courses, but allows paging
1566 *
1567 * @param        type description
1568 */
1569 function get_courses_page($categoryid="all", $sort="c.sortorder ASC", $fields="c.*",
1570                           &$totalcount, $limitfrom="", $limitnum="") {
1571
1572     global $USER, $CFG;
1573
1574     $categoryselect = "";
1575     if ($categoryid != "all") {
1576         $categoryselect = "c.category = '$categoryid'";
1577     }
1578
1579     $teachertable = "";
1580     $visiblecourses = "";
1581     if (!empty($USER)) {  // May need to check they are a teacher
1582         if (!iscreator()) {
1583             $visiblecourses = "AND ((c.visible > 0) OR (t.userid = '$USER->id' AND t.course = c.id))";
1584             $teachertable = ", {$CFG->prefix}user_teachers t";
1585         }
1586     } else {
1587         $visiblecourses = "AND c.visible > 0";
1588     }
1589
1590     if ($limitfrom !== "") {
1591         switch ($CFG->dbtype) {
1592             case "mysql":
1593                  $limit = "LIMIT $limitfrom,$limitnum";
1594                  break;
1595             case "postgres7":
1596                  $limit = "LIMIT $limitnum OFFSET $limitfrom";
1597                  break;
1598             default:
1599                  $limit = "LIMIT $limitnum,$limitfrom";
1600         }
1601     } else {
1602         $limit = "";
1603     }
1604
1605     $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
1606
1607     $totalcount = count_records_sql("SELECT COUNT(DISTINCT c.id) FROM $selectsql");
1608
1609     return get_records_sql("SELECT DISTINCT $fields FROM $selectsql ORDER BY $sort $limit");
1610 }
1611
1612
1613 /**
1614 * shortdesc
1615 *
1616 * longdesc
1617 *
1618 * @param        type description
1619 */
1620 function get_my_courses($userid, $sort="visible DESC,fullname ASC") {
1621
1622     global $CFG;
1623
1624     $course = array();
1625
1626     if ($students = get_records("user_students", "userid", $userid, "", "id, course")) {
1627         foreach ($students as $student) {
1628             $course[$student->course] = $student->course;
1629         }
1630     }
1631     if ($teachers = get_records("user_teachers", "userid", $userid, "", "id, course")) {
1632         foreach ($teachers as $teacher) {
1633             $course[$teacher->course] = $teacher->course;
1634         }
1635     }
1636     if (empty($course)) {
1637         return $course;
1638     }
1639
1640     $courseids = implode(',', $course);
1641
1642     return get_records_list("course", "id", $courseids, $sort);
1643
1644 //  The following is correct but VERY slow with large datasets
1645 //
1646 //    return get_records_sql("SELECT c.*
1647 //                              FROM {$CFG->prefix}course c,
1648 //                                   {$CFG->prefix}user_students s,
1649 //                                   {$CFG->prefix}user_teachers t
1650 //                             WHERE (s.userid = '$userid' AND s.course = c.id)
1651 //                                OR (t.userid = '$userid' AND t.course = c.id)
1652 //                             GROUP BY c.id
1653 //                             ORDER BY $sort");
1654 }
1655
1656
1657 /**
1658 * Returns a list of courses that match a search
1659 *
1660 * Returns a list of courses that match a search
1661 *
1662 * @param        type description
1663 */
1664 function get_courses_search($searchterms, $sort="fullname ASC", $page=0, $recordsperpage=50, &$totalcount) {
1665
1666     global $CFG;
1667
1668     switch ($CFG->dbtype) {
1669         case "mysql":
1670              $limit = "LIMIT $page,$recordsperpage";
1671              break;
1672         case "postgres7":
1673              $limit = "LIMIT $recordsperpage OFFSET ".($page * $recordsperpage);
1674              break;
1675         default:
1676              $limit = "LIMIT $recordsperpage,$page";
1677     }
1678
1679     //to allow case-insensitive search for postgesql
1680     if ($CFG->dbtype == "postgres7") {
1681         $LIKE = "ILIKE";
1682         $NOTLIKE = "NOT ILIKE";   // case-insensitive
1683         $REGEXP = "~*";
1684         $NOTREGEXP = "!~*";
1685     } else {
1686         $LIKE = "LIKE";
1687         $NOTLIKE = "NOT LIKE";
1688         $REGEXP = "REGEXP";
1689         $NOTREGEXP = "NOT REGEXP";
1690     }
1691
1692     $fullnamesearch = "";
1693     $summarysearch = "";
1694
1695     foreach ($searchterms as $searchterm) {
1696         if ($fullnamesearch) {
1697             $fullnamesearch .= " AND ";
1698         }
1699         if ($summarysearch) {
1700             $summarysearch .= " AND ";
1701         }
1702
1703         if (substr($searchterm,0,1) == "+") {
1704             $searchterm = substr($searchterm,1);
1705             $summarysearch .= " summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1706             $fullnamesearch .= " fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1707         } else if (substr($searchterm,0,1) == "-") {
1708             $searchterm = substr($searchterm,1);
1709             $summarysearch .= " summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1710             $fullnamesearch .= " fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
1711         } else {
1712             $summarysearch .= " summary $LIKE '%$searchterm%' ";
1713             $fullnamesearch .= " fullname $LIKE '%$searchterm%' ";
1714         }
1715
1716     }
1717
1718     $selectsql = "{$CFG->prefix}course WHERE ($fullnamesearch OR $summarysearch) AND category > '0'";
1719
1720     $totalcount = count_records_sql("SELECT COUNT(*) FROM $selectsql");
1721
1722     $courses = get_records_sql("SELECT * FROM $selectsql ORDER BY $sort $limit");
1723
1724     if ($courses) {  /// Remove unavailable courses from the list
1725         foreach ($courses as $key => $course) {
1726             if (!$course->visible) {
1727                 if (!isteacher($course->id)) {
1728                     unset($courses[$key]);
1729                     $totalcount--;
1730                 }
1731             }
1732         }
1733     }
1734
1735     return $courses;
1736 }
1737
1738
1739 /**
1740 * Returns a sorted list of categories
1741 *
1742 * Returns a sorted list of categories
1743 *
1744 * @param        type description
1745 */
1746 function get_categories($parent="none", $sort="sortorder ASC") {
1747
1748     if ($parent == "none") {
1749         $categories = get_records("course_categories", "", "", $sort);
1750     } else {
1751         $categories = get_records("course_categories", "parent", $parent, $sort);
1752     }
1753     if ($categories) {  /// Remove unavailable categories from the list
1754         $creator = iscreator();
1755         foreach ($categories as $key => $category) {
1756             if (!$category->visible) {
1757                 if (!$creator) {
1758                     unset($categories[$key]);
1759                 }
1760             }
1761         }
1762     }
1763     return $categories;
1764 }
1765
1766
1767 /**
1768 * reconcile $courseorder with a category object
1769 *
1770 * Given a category object, this function makes sure the courseorder
1771 * variable reflects the real world.
1772 *
1773 * @param        type description
1774 */
1775 function fix_course_sortorder($categoryid, $sort="sortorder ASC") {
1776
1777     if (!$courses = get_records("course", "category", "$categoryid", "$sort", "id, sortorder")) {
1778         set_field("course_categories", "coursecount", 0, "id", $categoryid);
1779         return true;
1780     }
1781
1782     $count = 0;
1783     $modified = false;
1784
1785     foreach ($courses as $course) {
1786         if ($course->sortorder != $count) {
1787             set_field("course", "sortorder", $count, "id", $course->id);
1788             $modified = true;
1789         }
1790         $count++;
1791     }
1792
1793     if ($modified) {
1794         set_field("course_categories", "timemodified", time(), "id", $categoryid);
1795     }
1796     set_field("course_categories", "coursecount", $count, "id", $categoryid);
1797
1798     return true;
1799 }
1800
1801 /**
1802 * This function creates a default separated/connected scale
1803 *
1804 * This function creates a default separated/connected scale
1805 * so there's something in the database.  The locations of
1806 * strings and files is a bit odd, but this is because we
1807 * need to maintain backward compatibility with many different
1808 * existing language translations and older sites.
1809 *
1810 * @param        type description
1811 */
1812 function make_default_scale() {
1813
1814     global $CFG;
1815
1816     $defaultscale = NULL;
1817     $defaultscale->courseid = 0;
1818     $defaultscale->userid = 0;
1819     $defaultscale->name  = get_string("separateandconnected");
1820     $defaultscale->scale = get_string("postrating1", "forum").",".
1821                            get_string("postrating2", "forum").",".
1822                            get_string("postrating3", "forum");
1823     $defaultscale->timemodified = time();
1824
1825     /// Read in the big description from the file.  Note this is not
1826     /// HTML (despite the file extension) but Moodle format text.
1827     $parentlang = get_string("parentlang");
1828     if (is_readable("$CFG->dirroot/lang/$CFG->lang/help/forum/ratings.html")) {
1829         $file = file("$CFG->dirroot/lang/$CFG->lang/help/forum/ratings.html");
1830     } else if ($parentlang and is_readable("$CFG->dirroot/lang/$parentlang/help/forum/ratings.html")) {
1831         $file = file("$CFG->dirroot/lang/$parentlang/help/forum/ratings.html");
1832     } else if (is_readable("$CFG->dirroot/lang/en/help/forum/ratings.html")) {
1833         $file = file("$CFG->dirroot/lang/en/help/forum/ratings.html");
1834     } else {
1835         $file = "";
1836     }
1837
1838     $defaultscale->description = addslashes(implode("", $file));
1839
1840     if ($defaultscale->id = insert_record("scale", $defaultscale)) {
1841         execute_sql("UPDATE {$CFG->prefix}forum SET scale = '$defaultscale->id'", false);
1842     }
1843 }
1844
1845 /**
1846 * Returns a menu of all available scales from the site as well as the given course
1847 *
1848 * Returns a menu of all available scales from the site as well as the given course
1849 *
1850 * @param        type description
1851 */
1852 function get_scales_menu($courseid=0) {
1853
1854     global $CFG;
1855
1856     $sql = "SELECT id, name FROM {$CFG->prefix}scale
1857              WHERE courseid = '0' or courseid = '$courseid'
1858           ORDER BY courseid ASC, name ASC";
1859
1860     if ($scales = get_records_sql_menu("$sql")) {
1861         return $scales;
1862     }
1863
1864     make_default_scale();
1865
1866     return get_records_sql_menu("$sql");
1867 }
1868
1869 /// MODULE FUNCTIONS /////////////////////////////////////////////////
1870
1871 /**
1872 * Just gets a raw list of all modules in a course
1873 *
1874 * Just gets a raw list of all modules in a course
1875 *
1876 * @param        type description
1877 */
1878 function get_course_mods($courseid) {
1879     global $CFG;
1880
1881     return get_records_sql("SELECT cm.*, m.name as modname
1882                             FROM {$CFG->prefix}modules m,
1883                                  {$CFG->prefix}course_modules cm
1884                             WHERE cm.course = '$courseid'
1885                             AND cm.deleted = '0'
1886                             AND cm.module = m.id ");
1887 }
1888
1889 /**
1890 * Given an instance of a module, finds the coursemodule description
1891 *
1892 * Given an instance of a module, finds the coursemodule description
1893 *
1894 * @param        type description
1895 */
1896 function get_coursemodule_from_instance($modulename, $instance, $courseid) {
1897
1898     global $CFG;
1899
1900     return get_record_sql("SELECT cm.*, m.name
1901                            FROM {$CFG->prefix}course_modules cm,
1902                                 {$CFG->prefix}modules md,
1903                                 {$CFG->prefix}$modulename m
1904                            WHERE cm.course = '$courseid' AND
1905                                  cm.deleted = '0' AND
1906                                  cm.instance = m.id AND
1907                                  md.name = '$modulename' AND
1908                                  md.id = cm.module AND
1909                                  m.id = '$instance'");
1910
1911 }
1912
1913 /**
1914 * Returns an array of all the active instances of a particular module in a given course, sorted in the order they are defined
1915 *
1916 * Returns an array of all the active instances of a particular
1917 * module in a given course, sorted in the order they are defined
1918 * in the course.   Returns false on any errors.
1919 *
1920 * @param        string  $modulename     the name of the module to get instances for
1921 * @param                object(course)  $course this depends on an accurate $course->modinfo
1922 */
1923 function get_all_instances_in_course($modulename, $course) {
1924
1925     global $CFG;
1926
1927     if (!$modinfo = unserialize($course->modinfo)) {
1928         return array();
1929     }
1930
1931     if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode
1932                             FROM {$CFG->prefix}course_modules cm,
1933                                  {$CFG->prefix}course_sections cw,
1934                                  {$CFG->prefix}modules md,
1935                                  {$CFG->prefix}$modulename m
1936                             WHERE cm.course = '$course->id' AND
1937                                   cm.instance = m.id AND
1938                                   cm.deleted = '0' AND
1939                                   cm.section = cw.id AND
1940                                   md.name = '$modulename' AND
1941                                   md.id = cm.module")) {
1942         return array();
1943     }
1944
1945     // Hide non-visible instances from students
1946     if (isteacher($course->id)) {
1947         $invisible = -1;
1948     } else {
1949         $invisible = 0;
1950     }
1951
1952     foreach ($modinfo as $mod) {
1953         if ($mod->mod == $modulename and $mod->visible > $invisible) {
1954             $instance = $rawmods[$mod->cm];
1955             if (!empty($mod->extra)) {
1956                 $instance->extra = $mod->extra;
1957             }
1958             $outputarray[] = $instance;
1959         }
1960     }
1961
1962     return $outputarray;
1963
1964 }
1965
1966
1967 /**
1968 * determine whether a module instance is visible within a course
1969 *
1970 * Given a valid module object with info about the id and course,
1971 * and the module's type (eg "forum") returns whether the object
1972 * is visible or not
1973 *
1974 * @param        type description
1975 */
1976 function instance_is_visible($moduletype, $module) {
1977
1978     global $CFG;
1979
1980     if ($records = get_records_sql("SELECT cm.instance, cm.visible
1981                                     FROM {$CFG->prefix}course_modules cm,
1982                                          {$CFG->prefix}modules m
1983                                    WHERE cm.course = '$module->course' AND
1984                                          cm.module = m.id AND
1985                                          m.name = '$moduletype' AND
1986                                          cm.instance = '$module->id'")) {
1987
1988         foreach ($records as $record) { // there should only be one - use the first one
1989             return $record->visible;
1990         }
1991     }
1992
1993     return true;  // visible by default!
1994 }
1995
1996
1997
1998
1999 /// LOG FUNCTIONS /////////////////////////////////////////////////////
2000
2001
2002 /**
2003 * Add an entry to the log table.
2004 *
2005 * Add an entry to the log table.  These are "action" focussed rather
2006 * than web server hits, and provide a way to easily reconstruct what
2007 * any particular student has been doing.
2008 *
2009 * @param        int         $course  the course id
2010 * @param        string  $module  the module name - e.g. forum, journal, resource, course, user etc
2011 * @param        string  $action  view, edit, post (often but not always the same as the file.php)
2012 * @param        string  $url     the file and parameters used to see the results of the action
2013 * @param        string  $info    additional description information
2014 * @param        string  $cm          the course_module->id if there is one
2015 * @param        string  $user    if log regards $user other than $USER
2016 */
2017 function add_to_log($courseid, $module, $action, $url="", $info="", $cm=0, $user=0) {
2018
2019     global $db, $CFG, $USER, $REMOTE_ADDR;
2020
2021     if ($user) {
2022         $userid = $user;
2023     } else {
2024         if (isset($USER->realuser)) {  // Don't log
2025             return;
2026         }
2027         $userid = empty($USER->id) ? "" : $USER->id;
2028     }
2029
2030     $timenow = time();
2031     $info = addslashes($info);
2032
2033     $result = $db->Execute("INSERT INTO {$CFG->prefix}log (time, userid, course, ip, module, cmid, action, url, info)
2034         VALUES ('$timenow', '$userid', '$courseid', '$REMOTE_ADDR', '$module', '$cm', '$action', '$url', '$info')");
2035
2036     if (!$result and ($CFG->debug > 7)) {
2037         echo "<P>Error: Could not insert a new entry to the Moodle log</P>";  // Don't throw an error
2038     }
2039     if (!$user) {
2040         if (isstudent($courseid)) {
2041             $db->Execute("UPDATE {$CFG->prefix}user_students SET timeaccess = '$timenow' ".
2042                          "WHERE course = '$courseid' AND userid = '$userid'");
2043         }
2044
2045         if (isteacher($courseid, false, false)) {
2046             $db->Execute("UPDATE {$CFG->prefix}user_teachers SET timeaccess = '$timenow' ".
2047                          "WHERE course = '$courseid' AND userid = '$userid'");
2048         }
2049     }
2050 }
2051
2052
2053 /**
2054 * select all log records based on SQL criteria
2055 *
2056 * select all log records based on SQL criteria
2057 *
2058 * @param        string  $select SQL select criteria
2059 * @param        string  $order  SQL order by clause to sort the records returned
2060 */
2061 function get_logs($select, $order="l.time DESC", $limitfrom="", $limitnum="", &$totalcount) {
2062     global $CFG;
2063
2064     if ($limitfrom !== "") {
2065         switch ($CFG->dbtype) {
2066             case "mysql":
2067                  $limit = "LIMIT $limitfrom,$limitnum";
2068                  break;
2069             case "postgres7":
2070                  $limit = "LIMIT $limitnum OFFSET $limitfrom";
2071                  break;
2072             default:
2073                  $limit = "LIMIT $limitnum,$limitfrom";
2074         }
2075     } else {
2076         $limit = "";
2077     }
2078
2079     if ($order) {
2080         $order = "ORDER BY $order";
2081     }
2082
2083     $selectsql = "{$CFG->prefix}log l, {$CFG->prefix}user u WHERE $select";
2084
2085     $totalcount = count_records_sql("SELECT COUNT(*) FROM $selectsql");
2086
2087     return get_records_sql("SELECT l.*, u.firstname, u.lastname, u.picture
2088                                 FROM $selectsql $order $limit");
2089 }
2090
2091
2092 /**
2093 * select all log records for a given course and user
2094 *
2095 * select all log records for a given course and user
2096 *
2097 * @param        type description
2098 */
2099 function get_logs_usercourse($userid, $courseid, $coursestart) {
2100     global $CFG;
2101
2102     if ($courseid) {
2103        $courseselect = " AND course = '$courseid' ";
2104     }
2105
2106     return get_records_sql("SELECT floor((`time` - $coursestart)/86400) as day, count(*) as num
2107                             FROM {$CFG->prefix}log
2108                            WHERE userid = '$userid'
2109                              AND `time` > '$coursestart' $courseselect
2110                         GROUP BY day ");
2111 }
2112
2113 /**
2114 * select all log records for a given course, user, and day
2115 *
2116 * select all log records for a given course, user, and day
2117 *
2118 * @param        type description
2119 */
2120 function get_logs_userday($userid, $courseid, $daystart) {
2121     global $CFG;
2122
2123     if ($courseid) {
2124        $courseselect = " AND course = '$courseid' ";
2125     }
2126
2127     return get_records_sql("SELECT floor((`time` - $daystart)/3600) as hour, count(*) as num
2128                             FROM {$CFG->prefix}log
2129                            WHERE userid = '$userid'
2130                              AND `time` > '$daystart' $courseselect
2131                         GROUP BY hour ");
2132 }
2133
2134 /// GENERAL HELPFUL THINGS  ///////////////////////////////////
2135
2136 /**
2137 * dump a given object's information in a PRE block
2138 *
2139 * dump a given object's information in a PRE block
2140 * Mostly just for debugging
2141 *
2142 * @param        type description
2143 */
2144 function print_object($object) {
2145
2146     echo "<PRE>";
2147     print_r($object);
2148     echo "</PRE>";
2149 }
2150
2151
2152
2153 // vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140:
2154 ?>