lib/datalib.php

   1 <?php // $Id$
   2
   3 /**
   4  * Library of functions for database manipulation.
   5  *
   6  * Other main libraries:
   7  * - weblib.php - functions that produce web output
   8  * - moodlelib.php - general-purpose Moodle functions
   9  * @author Martin Dougiamas and many others
  10  * @version $Id$
  11  * @license http://www.gnu.org/copyleft/gpl.html GNU Public License
  12  * @package moodlecore
  13  */
  14
  15
  16 /**
  17  * Escape all dangerous characters in a data record
  18  *
  19  * $dataobject is an object containing needed data
  20  * Run over each field exectuting addslashes() function
  21  * to escape SQL unfriendly characters (e.g. quotes)
  22  * Handy when writing back data read from the database
  23  *
  24  * @param $dataobject Object containing the database record
  25  * @return object Same object with neccessary characters escaped
  26  */
  27 function addslashes_object( $dataobject ) {
  28     $a = get_object_vars( $dataobject);
  29     foreach ($a as $key=>$value) {
  30       $a[$key] = addslashes( $value );
  31     }
  32     return (object)$a;
  33 }
  34
  35 /// USER DATABASE ////////////////////////////////////////////////
  36
  37 /**
  38  * Returns $user object of the main admin user
  39  * primary admin = admin with lowest role_assignment id among admins
  40  * @uses $CFG
  41  * @return object(admin) An associative array representing the admin user.
  42  */
  43 function get_admin () {
  44
  45     global $CFG;
  46
  47     if ( $admins = get_admins() ) {
  48         foreach ($admins as $admin) {
  49             return $admin;   // ie the first one
  50         }
  51     } else {
  52         return false;
  53     }
  54 }
  55
  56 /**
  57  * Returns list of all admins
  58  *
  59  * @uses $CFG
  60  * @return object
  61  */
  62 function get_admins() {
  63
  64     global $CFG;
  65
  66     $context = get_context_instance(CONTEXT_SYSTEM, SITEID);
  67
  68     return get_users_by_capability($context, 'moodle/site:doanything', 'u.*, ra.id as adminid', 'ra.id ASC'); // only need first one
  69
  70 }
  71
  72
  73 function get_courses_in_metacourse($metacourseid) {
  74     global $CFG;
  75
  76     $sql = "SELECT c.id,c.shortname,c.fullname FROM {$CFG->prefix}course c, {$CFG->prefix}course_meta mc WHERE mc.parent_course = $metacourseid
  77         AND mc.child_course = c.id ORDER BY c.shortname";
  78
  79     return get_records_sql($sql);
  80 }
  81
  82 function get_courses_notin_metacourse($metacourseid,$count=false) {
  83
  84     global $CFG;
  85
  86     if ($count) {
  87         $sql  = "SELECT COUNT(c.id)";
  88     } else {
  89         $sql = "SELECT c.id,c.shortname,c.fullname";
  90     }
  91
  92     $alreadycourses = get_courses_in_metacourse($metacourseid);
  93
  94     $sql .= " FROM {$CFG->prefix}course c WHERE ".((!empty($alreadycourses)) ? "c.id NOT IN (".implode(',',array_keys($alreadycourses)).")
  95     AND " : "")." c.id !=$metacourseid and c.id != ".SITEID." and c.metacourse != 1 ".((empty($count)) ? " ORDER BY c.shortname" : "");
  96
  97     return get_records_sql($sql);
  98 }
  99
 100 function count_courses_notin_metacourse($metacourseid) {
 101     global $CFG;
 102
 103     $alreadycourses = get_courses_in_metacourse($metacourseid);
 104
 105     $sql = "SELECT COUNT(c.id) AS notin FROM {$CFG->prefix}course c
 106              WHERE ".((!empty($alreadycourses)) ? "c.id NOT IN (".implode(',',array_keys($alreadycourses)).")
 107               AND " : "")." c.id !=$metacourseid and c.id != ".SITEID." and c.metacourse != 1";
 108
 109     if (!$count = get_record_sql($sql)) {
 110         return 0;
 111     }
 112
 113     return $count->notin;
 114 }
 115
 116 /**
 117  * Search through course users
 118  *
 119  * If $coursid specifies the site course then this function searches
 120  * through all undeleted and confirmed users
 121  *
 122  * @uses $CFG
 123  * @uses SITEID
 124  * @param int $courseid The course in question.
 125  * @param int $groupid The group in question.
 126  * @param string $searchtext ?
 127  * @param string $sort ?
 128  * @param string $exceptions ?
 129  * @return object
 130  */
 131 function search_users($courseid, $groupid, $searchtext, $sort='', $exceptions='') {
 132     global $CFG;
 133
 134     $LIKE      = sql_ilike();
 135     $fullname  = sql_fullname('u.firstname', 'u.lastname');
 136
 137     if (!empty($exceptions)) {
 138         $except = ' AND u.id NOT IN ('. $exceptions .') ';
 139     } else {
 140         $except = '';
 141     }
 142
 143     if (!empty($sort)) {
 144         $order = ' ORDER BY '. $sort;
 145     } else {
 146         $order = '';
 147     }
 148
 149     $select = 'u.deleted = \'0\' AND u.confirmed = \'1\'';
 150
 151     if (!$courseid or $courseid == SITEID) {
 152         return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
 153                       FROM {$CFG->prefix}user u
 154                       WHERE $select
 155                           AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
 156                           $except $order");
 157     } else {
 158
 159         if ($groupid) {
 160 //TODO:check. Remove group DB dependencies.
 161             return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
 162                           FROM {$CFG->prefix}user u,
 163                                {$CFG->prefix}groups_members gm
 164                           WHERE $select AND gm.groupid = '$groupid' AND gm.userid = u.id
 165                               AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
 166                               $except $order");
 167         } else {
 168             $context = get_context_instance(CONTEXT_COURSE, $courseid);
 169             $contextlists = get_related_contexts_string($context);
 170             $users = get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email
 171                           FROM {$CFG->prefix}user u,
 172                                {$CFG->prefix}role_assignments ra
 173                           WHERE $select AND ra.contextid $contextlists AND ra.userid = u.id
 174                               AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%')
 175                               $except $order");
 176         }
 177         return $users;
 178     }
 179 }
 180
 181
 182 /**
 183  * Returns a list of all site users
 184  * Obsolete, just calls get_course_users(SITEID)
 185  *
 186  * @uses SITEID
 187  * @deprecated Use {@link get_course_users()} instead.
 188  * @param string $fields A comma separated list of fields to be returned from the chosen table.
 189  * @return object|false  {@link $USER} records or false if error.
 190  */
 191 function get_site_users($sort='u.lastaccess DESC', $fields='*', $exceptions='') {
 192
 193     return get_course_users(SITEID, $sort, $exceptions, $fields);
 194 }
 195
 196
 197 /**
 198  * Returns a subset of users
 199  *
 200  * @uses $CFG
 201  * @param bool $get If false then only a count of the records is returned
 202  * @param string $search A simple string to search for
 203  * @param bool $confirmed A switch to allow/disallow unconfirmed users
 204  * @param array(int) $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
 205  * @param string $sort A SQL snippet for the sorting criteria to use
 206  * @param string $firstinitial ?
 207  * @param string $lastinitial ?
 208  * @param string $page ?
 209  * @param string $recordsperpage ?
 210  * @param string $fields A comma separated list of fields to be returned from the chosen table.
 211  * @return object|false|int  {@link $USER} records unless get is false in which case the integer count of the records found is returned. False is returned if an error is encountered.
 212  */
 213 function get_users($get=true, $search='', $confirmed=false, $exceptions='', $sort='firstname ASC',
 214                    $firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*') {
 215
 216     global $CFG;
 217
 218     if ($get && !$recordsperpage) {
 219         debugging('Call to get_users with $get = true no $recordsperpage limit. ' .
 220                 'On large installations, this will probably cause an out of memory error. ' .
 221                 'Please think again and change your code so that it does not try to ' .
 222                 'load so much data into memory.', DEBUG_DEVELOPER);
 223     }
 224
 225     $LIKE      = sql_ilike();
 226     $fullname  = sql_fullname();
 227
 228     $select = 'username <> \'guest\' AND deleted = 0';
 229
 230     if (!empty($search)){
 231         $search = trim($search);
 232         $select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') ";
 233     }
 234
 235     if ($confirmed) {
 236         $select .= ' AND confirmed = \'1\' ';
 237     }
 238
 239     if ($exceptions) {
 240         $select .= ' AND id NOT IN ('. $exceptions .') ';
 241     }
 242
 243     if ($firstinitial) {
 244         $select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\'';
 245     }
 246     if ($lastinitial) {
 247         $select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\'';
 248     }
 249
 250     if ($get) {
 251         return get_records_select('user', $select, $sort, $fields, $page, $recordsperpage);
 252     } else {
 253         return count_records_select('user', $select);
 254     }
 255 }
 256
 257
 258 /**
 259  * shortdesc (optional)
 260  *
 261  * longdesc
 262  *
 263  * @uses $CFG
 264  * @param string $sort ?
 265  * @param string $dir ?
 266  * @param int $categoryid ?
 267  * @param int $categoryid ?
 268  * @param string $search ?
 269  * @param string $firstinitial ?
 270  * @param string $lastinitial ?
 271  * @returnobject {@link $USER} records
 272  * @todo Finish documenting this function
 273  */
 274
 275 function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=0,
 276                            $search='', $firstinitial='', $lastinitial='', $remotewhere='') {
 277
 278     global $CFG;
 279
 280     $LIKE      = sql_ilike();
 281     $fullname  = sql_fullname();
 282
 283     $select = "deleted <> '1'";
 284
 285     if (!empty($search)) {
 286         $search = trim($search);
 287         $select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%' OR username='$search') ";
 288     }
 289
 290     if ($firstinitial) {
 291         $select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\' ';
 292     }
 293
 294     if ($lastinitial) {
 295         $select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\' ';
 296     }
 297
 298     $select .= $remotewhere;
 299
 300     if ($sort) {
 301         $sort = ' ORDER BY '. $sort .' '. $dir;
 302     }
 303
 304 /// warning: will return UNCONFIRMED USERS
 305     return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess, confirmed, mnethostid
 306                               FROM {$CFG->prefix}user
 307                              WHERE $select $sort", $page, $recordsperpage);
 308
 309 }
 310
 311
 312 /**
 313  * Full list of users that have confirmed their accounts.
 314  *
 315  * @uses $CFG
 316  * @return object
 317  */
 318 function get_users_confirmed() {
 319     global $CFG;
 320     return get_records_sql("SELECT *
 321                               FROM {$CFG->prefix}user
 322                              WHERE confirmed = 1
 323                                AND deleted = 0
 324                                AND username <> 'guest'");
 325 }
 326
 327
 328 /**
 329  * Full list of users that have not yet confirmed their accounts.
 330  *
 331  * @uses $CFG
 332  * @param string $cutofftime ?
 333  * @return object  {@link $USER} records
 334  */
 335 function get_users_unconfirmed($cutofftime=2000000000) {
 336     global $CFG;
 337     return get_records_sql("SELECT *
 338                              FROM {$CFG->prefix}user
 339                             WHERE confirmed = 0
 340                               AND firstaccess > 0
 341                               AND firstaccess < $cutofftime");
 342 }
 343
 344 /**
 345  * All users that we have not seen for a really long time (ie dead accounts)
 346  *
 347  * @uses $CFG
 348  * @param string $cutofftime ?
 349  * @return object  {@link $USER} records
 350  */
 351 function get_users_longtimenosee($cutofftime) {
 352     global $CFG;
 353     return get_records_sql("SELECT userid as id, courseid
 354                               FROM {$CFG->prefix}user_lastaccess
 355                              WHERE courseid != ".SITEID."
 356                                AND timeaccess > 0
 357                                AND timeaccess < $cutofftime ");
 358 }
 359
 360 /**
 361  * Full list of bogus accounts that are probably not ever going to be used
 362  *
 363  * @uses $CFG
 364  * @param string $cutofftime ?
 365  * @return object  {@link $USER} records
 366  */
 367
 368 function get_users_not_fully_set_up($cutofftime=2000000000) {
 369     global $CFG;
 370     return get_records_sql("SELECT *
 371                              FROM {$CFG->prefix}user
 372                             WHERE confirmed = 1
 373                              AND lastaccess > 0
 374                              AND lastaccess < $cutofftime
 375                              AND deleted = 0
 376                              AND (lastname = '' OR firstname = '' OR email = '')");
 377 }
 378
 379 /// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
 380
 381
 382 /**
 383  * Returns $course object of the top-level site.
 384  *
 385  * @return course  A {@link $COURSE} object for the site
 386  */
 387 function get_site() {
 388
 389     global $SITE;
 390
 391     if (!empty($SITE->id)) {   // We already have a global to use, so return that
 392         return $SITE;
 393     }
 394
 395     if ($course = get_record('course', 'category', 0)) {
 396         return $course;
 397     } else {
 398         return false;
 399     }
 400 }
 401
 402 /**
 403  * Returns list of courses, for whole site, or category
 404  *
 405  * Returns list of courses, for whole site, or category
 406  * Important: Using c.* for fields is extremely expensive because
 407  *            we are using distinct. You almost _NEVER_ need all the fields
 408  *            in such a large SELECT
 409  *
 410  * @param    type description
 411  *
 412  */
 413 function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {
 414
 415     global $USER, $CFG;
 416
 417     if ($categoryid != "all" && is_numeric($categoryid)) {
 418         $categoryselect = "WHERE c.category = '$categoryid'";
 419     } else {
 420         $categoryselect = "";
 421     }
 422
 423     if (empty($sort)) {
 424         $sortstatement = "";
 425     } else {
 426         $sortstatement = "ORDER BY $sort";
 427     }
 428
 429     $visiblecourses = array();
 430
 431     // pull out all course matching the cat
 432     if ($courses = get_records_sql("SELECT $fields
 433                                 FROM {$CFG->prefix}course c
 434                                 $categoryselect
 435                                 $sortstatement")) {
 436
 437         // loop throught them
 438         foreach ($courses as $course) {
 439
 440             if (isset($course->visible) && $course->visible <= 0) {
 441                 // for hidden courses, require visibility check
 442                 if (has_capability('moodle/course:viewhiddencourses',
 443                         get_context_instance(CONTEXT_COURSE, $course->id))) {
 444                     $visiblecourses [] = $course;
 445                 }
 446             } else {
 447                 $visiblecourses [] = $course;
 448             }
 449         }
 450     }
 451     return $visiblecourses;
 452
 453 /*
 454     $teachertable = "";
 455     $visiblecourses = "";
 456     $sqland = "";
 457     if (!empty($categoryselect)) {
 458         $sqland = "AND ";
 459     }
 460     if (!empty($USER->id)) {  // May need to check they are a teacher
 461         if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
 462             $visiblecourses = "$sqland ((c.visible > 0) OR t.userid = '$USER->id')";
 463             $teachertable = "LEFT JOIN {$CFG->prefix}user_teachers t ON t.course = c.id";
 464         }
 465     } else {
 466         $visiblecourses = "$sqland c.visible > 0";
 467     }
 468
 469     if ($categoryselect or $visiblecourses) {
 470         $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
 471     } else {
 472         $selectsql = "{$CFG->prefix}course c $teachertable";
 473     }
 474
 475     $extrafield = str_replace('ASC','',$sort);
 476     $extrafield = str_replace('DESC','',$extrafield);
 477     $extrafield = trim($extrafield);
 478     if (!empty($extrafield)) {
 479         $extrafield = ','.$extrafield;
 480     }
 481     return get_records_sql("SELECT ".((!empty($teachertable)) ? " DISTINCT " : "")." $fields $extrafield FROM $selectsql ".((!empty($sort)) ? "ORDER BY $sort" : ""));
 482     */
 483 }
 484
 485
 486 /**
 487  * Returns list of courses, for whole site, or category
 488  *
 489  * Similar to get_courses, but allows paging
 490  * Important: Using c.* for fields is extremely expensive because
 491  *            we are using distinct. You almost _NEVER_ need all the fields
 492  *            in such a large SELECT
 493  *
 494  * @param    type description
 495  *
 496  */
 497 function get_courses_page($categoryid="all", $sort="c.sortorder ASC", $fields="c.*",
 498                           &$totalcount, $limitfrom="", $limitnum="") {
 499
 500     global $USER, $CFG;
 501
 502     $categoryselect = "";
 503     if ($categoryid != "all" && is_numeric($categoryid)) {
 504         $categoryselect = "WHERE c.category = '$categoryid'";
 505     } else {
 506         $categoryselect = "";
 507     }
 508
 509     // pull out all course matching the cat
 510     $visiblecourses = array();
 511     if (!($courses = get_records_sql("SELECT $fields
 512                                 FROM {$CFG->prefix}course c
 513                                 $categoryselect
 514                                 ORDER BY $sort"))) {
 515         return $visiblecourses;
 516     }
 517     $totalcount = 0;
 518
 519     if (!$limitnum) {
 520         $limitnum = count($courses);
 521     }
 522
 523     if (!$limitfrom) {
 524         $limitfrom = 0;
 525     }
 526
 527     // iteration will have to be done inside loop to keep track of the limitfrom and limitnum
 528     foreach ($courses as $course) {
 529         if ($course->visible <= 0) {
 530             // for hidden courses, require visibility check
 531             if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id))) {
 532                 $totalcount++;
 533                 if ($totalcount > $limitfrom && count($visiblecourses) < $limitnum) {
 534                     $visiblecourses [] = $course;
 535                 }
 536             }
 537         } else {
 538             $totalcount++;
 539             if ($totalcount > $limitfrom && count($visiblecourses) < $limitnum) {
 540                 $visiblecourses [] = $course;
 541             }
 542         }
 543     }
 544
 545     return $visiblecourses;
 546
 547 /**
 548
 549     $categoryselect = "";
 550     if ($categoryid != "all" && is_numeric($categoryid)) {
 551         $categoryselect = "c.category = '$categoryid'";
 552     }
 553
 554     $teachertable = "";
 555     $visiblecourses = "";
 556     $sqland = "";
 557     if (!empty($categoryselect)) {
 558         $sqland = "AND ";
 559     }
 560     if (!empty($USER) and !empty($USER->id)) {  // May need to check they are a teacher
 561         if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
 562             $visiblecourses = "$sqland ((c.visible > 0) OR t.userid = '$USER->id')";
 563             $teachertable = "LEFT JOIN {$CFG->prefix}user_teachers t ON t.course=c.id";
 564         }
 565     } else {
 566         $visiblecourses = "$sqland c.visible > 0";
 567     }
 568
 569     if ($limitfrom !== "") {
 570         $limit = sql_paging_limit($limitfrom, $limitnum);
 571     } else {
 572         $limit = "";
 573     }
 574
 575     $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses";
 576
 577     $totalcount = count_records_sql("SELECT COUNT(DISTINCT c.id) FROM $selectsql");
 578
 579     return get_records_sql("SELECT $fields FROM $selectsql ".((!empty($sort)) ? "ORDER BY $sort" : "")." $limit");
 580     */
 581 }
 582
 583
 584 /**
 585  * List of courses that a user has access to view. Note that for admins,
 586  * this usually includes every course on the system.
 587  *
 588  * @uses $CFG
 589  * @param int $userid The user of interest
 590  * @param string $sort the sortorder in the course table
 591  * @param string $fields  the fields to return
 592  * @param bool $doanything True if using the doanything flag
 593  * @param int $limit Maximum number of records to return, or 0 for unlimited
 594  * @return array {@link $COURSE} of course objects
 595  */
 596 function get_my_courses($userid, $sort=NULL, $fields=NULL, $doanything=false,$limit=0) {
 597
 598     global $CFG, $USER;
 599
 600     // Default parameters
 601     $d_sort   = 'visible DESC,sortorder ASC';
 602     $d_fields = 'id, category, sortorder, shortname, fullname, idnumber, newsitems, teacher, teachers, student, students, guest, startdate, visible, cost, enrol, summary, groupmode, groupmodeforce';
 603
 604     $usingdefaults = true;
 605     if (is_null($sort)   || $sort === $d_sort) {
 606         $sort = $d_sort;
 607     } else {
 608         $usingdefaults = false;
 609     }
 610     if (is_null($fields) || $fields === $d_fields) {
 611         $fields = $d_fields;
 612     } else {
 613         $usingdefaults = false;
 614     }
 615
 616     $reallimit = 0; // this is only set if we are using a limit on the first call
 617
 618     // If using default params, we may have it cached...
 619     if (!empty($USER->id) && ($USER->id == $userid) && $usingdefaults) {
 620         if (!empty($USER->mycourses[$doanything])) {
 621             if ($limit && $limit < count($USER->mycourses[$doanything])) {
 622                 //silence warnings in PHP 4.x - the last param was added in PHP 5.0.2
 623                 return @array_slice($USER->mycourses[$doanything], 0, $limit, true);
 624             } else {
 625                 return $USER->mycourses[$doanything];
 626             }
 627         } else {
 628             // now, this is the first call, i.e. no cache, and we are using defaults, with a limit supplied,
 629             // we need to store the limit somewhere, retrieve all, cache properly and then slice the array
 630             // to return the proper number of entries. This is so that we don't keep missing calls like limit 20,20,20
 631             if ($limit) {
 632                 $reallimit = $limit;
 633                 $limit = 0;
 634             }
 635         }
 636     }
 637
 638     $mycourses = array();
 639
 640     // Fix fields to refer to the course table c
 641     $fields=preg_replace('/([a-z0-9*]+)/','c.$1',$fields);
 642
 643     // Attempt to filter the list of courses in order to reduce the number
 644     // of queries in the next part.
 645
 646     // Check root permissions
 647     $sitecontext = get_context_instance(CONTEXT_SYSTEM, SITEID);
 648
 649     // Guest's do not have any courses
 650     if (has_capability('moodle/legacy:guest',$sitecontext,$userid,false)) {
 651         return(array());
 652     }
 653
 654     // we can optimise some things for true admins
 655     $candoanything = false;
 656     if ($doanything && has_capability('moodle/site:doanything',$sitecontext,$userid,true)) {
 657         $candoanything = true;
 658     }
 659
 660     if ($candoanything || has_capability('moodle/course:view',$sitecontext,$userid,$doanything)) {
 661         // User can view all courses, although there might be exceptions
 662         // which we will filter later.
 663         $rs = get_recordset('course c', '', '', $sort, $fields);
 664     } else {
 665         // The only other context level above courses that applies to moodle/course:view
 666         // is category. So we consider:
 667         // 1. All courses in which the user is assigned a role
 668         // 2. All courses in categories in which the user is assigned a role
 669         // 2BIS. All courses in subcategories in which the user gets assignment because he is assigned in one of its ascendant categories
 670         // 3. All courses which have overrides for moodle/course:view
 671         // Remember that this is just a filter. We check each individual course later.
 672         // However for a typical student on a large system this can reduce the
 673         // number of courses considered from around 2,000 to around 2, with corresponding
 674         // reduction in the number of queries needed.
 675         $rs=get_recordset_sql("
 676             SELECT $fields
 677             FROM {$CFG->prefix}course c, (
 678                 SELECT
 679                     c.id
 680                 FROM
 681                     {$CFG->prefix}role_assignments ra
 682                     INNER JOIN {$CFG->prefix}context x ON x.id         = ra.contextid
 683                     INNER JOIN {$CFG->prefix}course c  ON x.instanceid = c.id
 684                 WHERE
 685                     ra.userid      = $userid AND
 686                     x.contextlevel = 50
 687                 UNION
 688                 SELECT
 689                     c.id
 690                 FROM
 691                     {$CFG->prefix}role_assignments ra
 692                     INNER JOIN {$CFG->prefix}context x ON x.id = ra.contextid
 693                     INNER JOIN {$CFG->prefix}course_categories a ON a.path LIKE ".sql_concat("'%/'", 'x.instanceid', "'/%'")." OR x.instanceid = a.id
 694                     INNER JOIN {$CFG->prefix}course c ON c.category = a.id
 695                 WHERE
 696                     ra.userid = $userid AND
 697                     x.contextlevel = 40
 698                 UNION
 699                 SELECT
 700                     c.id
 701                 FROM
 702                     {$CFG->prefix}role_capabilities ca
 703                     INNER JOIN {$CFG->prefix}context x ON x.id = ca.contextid
 704                     INNER JOIN {$CFG->prefix}course c  ON c.id = x.instanceid
 705                 WHERE
 706                     ca.capability  = 'moodle/course:view' AND
 707                     ca.contextid  != {$sitecontext->id} AND
 708                     x.contextlevel = 50
 709             ) cids
 710             WHERE c.id = cids.id
 711             ORDER BY $sort"
 712         );
 713     }
 714
 715     if ($rs && $rs->RecordCount() > 0) {
 716         while ($course = rs_fetch_next_record($rs)) {
 717             if ($course->id != SITEID) {
 718
 719                 if ($candoanything) { // no need for further checks...
 720                     $mycourses[$course->id] = $course;
 721                     continue;
 722                 }
 723
 724                 // users with moodle/course:view are considered course participants
 725                 // the course needs to be visible, or user must have moodle/course:viewhiddencourses
 726                 // capability set to view hidden courses
 727                 $context = get_context_instance(CONTEXT_COURSE, $course->id);
 728                 if ( has_capability('moodle/course:view', $context, $userid, $doanything) &&
 729                     !has_capability('moodle/legacy:guest', $context, $userid, false) &&
 730                     ($course->visible || has_capability('moodle/course:viewhiddencourses', $context, $userid))) {
 731                     $mycourses[$course->id] = $course;
 732
 733                     // Only return a limited number of courses if limit is set
 734                     if($limit>0) {
 735                         $limit--;
 736                         if($limit==0) {
 737                             break;
 738                         }
 739                     }
 740                 }
 741             }
 742         }
 743     }
 744
 745     // Cache if using default params...
 746     if (!empty($USER->id) && ($USER->id == $userid) && $usingdefaults && $limit == 0) {
 747         $USER->mycourses[$doanything] = $mycourses;
 748     }
 749
 750     if (!empty($mycourses) && $reallimit) {
 751         return array_slice($mycourses, 0, $reallimit, true);
 752     } else {
 753         return $mycourses;
 754     }
 755 }
 756
 757
 758 /**
 759  * A list of courses that match a search
 760  *
 761  * @uses $CFG
 762  * @param array $searchterms ?
 763  * @param string $sort ?
 764  * @param int $page ?
 765  * @param int $recordsperpage ?
 766  * @param int $totalcount Passed in by reference. ?
 767  * @return object {@link $COURSE} records
 768  */
 769 function get_courses_search($searchterms, $sort='fullname ASC', $page=0, $recordsperpage=50, &$totalcount) {
 770
 771     global $CFG;
 772
 773     //to allow case-insensitive search for postgesql
 774     if ($CFG->dbfamily == 'postgres') {
 775         $LIKE = 'ILIKE';
 776         $NOTLIKE = 'NOT ILIKE';   // case-insensitive
 777         $REGEXP = '~*';
 778         $NOTREGEXP = '!~*';
 779     } else {
 780         $LIKE = 'LIKE';
 781         $NOTLIKE = 'NOT LIKE';
 782         $REGEXP = 'REGEXP';
 783         $NOTREGEXP = 'NOT REGEXP';
 784     }
 785
 786     $fullnamesearch = '';
 787     $summarysearch = '';
 788
 789     foreach ($searchterms as $searchterm) {
 790
 791     /// Under Oracle and MSSQL, trim the + and - operators and perform
 792     /// simpler LIKE search
 793         if ($CFG->dbfamily == 'oracle' || $CFG->dbfamily == 'mssql') {
 794             $searchterm = trim($searchterm, '+-');
 795         }
 796
 797         if ($fullnamesearch) {
 798             $fullnamesearch .= ' AND ';
 799         }
 800         if ($summarysearch) {
 801             $summarysearch .= ' AND ';
 802         }
 803
 804         if (substr($searchterm,0,1) == '+') {
 805             $searchterm = substr($searchterm,1);
 806             $summarysearch .= " summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
 807             $fullnamesearch .= " fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
 808         } else if (substr($searchterm,0,1) == "-") {
 809             $searchterm = substr($searchterm,1);
 810             $summarysearch .= " summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
 811             $fullnamesearch .= " fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
 812         } else {
 813             $summarysearch .= ' summary '. $LIKE .' \'%'. $searchterm .'%\' ';
 814             $fullnamesearch .= ' fullname '. $LIKE .' \'%'. $searchterm .'%\' ';
 815         }
 816
 817     }
 818
 819     $selectsql = $CFG->prefix .'course WHERE ('. $fullnamesearch .' OR '. $summarysearch .') AND category > \'0\'';
 820
 821     $totalcount = count_records_sql('SELECT COUNT(*) FROM '. $selectsql);
 822
 823     $courses = get_records_sql('SELECT * FROM '. $selectsql .' ORDER BY '. $sort, $page, $recordsperpage);
 824
 825     if ($courses) {  /// Remove unavailable courses from the list
 826         foreach ($courses as $key => $course) {
 827             if (!$course->visible) {
 828                 if (!has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id))) {
 829                     unset($courses[$key]);
 830                     $totalcount--;
 831                 }
 832             }
 833         }
 834     }
 835
 836     return $courses;
 837 }
 838
 839
 840 /**
 841  * Returns a sorted list of categories
 842  *
 843  * @param string $parent The parent category if any
 844  * @param string $sort the sortorder
 845  * @return array of categories
 846  */
 847 function get_categories($parent='none', $sort='sortorder ASC') {
 848
 849     if ($parent === 'none') {
 850         $categories = get_records('course_categories', '', '', $sort);
 851     } else {
 852         $categories = get_records('course_categories', 'parent', $parent, $sort);
 853     }
 854     if ($categories) {  /// Remove unavailable categories from the list
 855         foreach ($categories as $key => $category) {
 856             if (!$category->visible) {
 857                 if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_COURSECAT, $category->id))) {
 858                     unset($categories[$key]);
 859                 }
 860             }
 861         }
 862     }
 863     return $categories;
 864 }
 865
 866
 867 /**
 868  * Returns an array of category ids of all the subcategories for a given
 869  * category.
 870  * @param $catid - The id of the category whose subcategories we want to find.
 871  * @return array of category ids.
 872  */
 873 function get_all_subcategories($catid) {
 874
 875     $subcats = array();
 876
 877     if ($categories = get_records('course_categories', 'parent', $catid)) {
 878         foreach ($categories as $cat) {
 879             array_push($subcats, $cat->id);
 880             $subcats = array_merge($subcats, get_all_subcategories($cat->id));
 881         }
 882     }
 883     return $subcats;
 884 }
 885
 886
 887 /**
 888 * This recursive function makes sure that the courseorder is consecutive
 889 *
 890 * @param    type description
 891 *
 892 * $n is the starting point, offered only for compatilibity -- will be ignored!
 893 * $safe (bool) prevents it from assuming category-sortorder is unique, used to upgrade
 894 *       safely from 1.4 to 1.5
 895 */
 896 function fix_course_sortorder($categoryid=0, $n=0, $safe=0, $depth=0, $path='') {
 897
 898     global $CFG;
 899
 900     $count = 0;
 901
 902     $catgap    = 1000; // "standard" category gap
 903     $tolerance = 200;  // how "close" categories can get
 904
 905     if ($categoryid > 0){
 906         // update depth and path
 907         $cat   = get_record('course_categories', 'id', $categoryid);
 908         if ($cat->parent == 0) {
 909             $depth = 0;
 910             $path  = '';
 911         } else if ($depth == 0 ) { // doesn't make sense; get from DB
 912             // this is only called if the $depth parameter looks dodgy
 913             $parent = get_record('course_categories', 'id', $cat->parent);
 914             $path  = $parent->path;
 915             $depth = $parent->depth;
 916         }
 917         $path  = $path . '/' . $categoryid;
 918         $depth = $depth + 1;
 919
 920         set_field('course_categories', 'path',  addslashes($path),  'id', $categoryid);
 921         set_field('course_categories', 'depth', $depth, 'id', $categoryid);
 922     }
 923
 924     // get some basic info about courses in the category
 925     $info = get_record_sql('SELECT MIN(sortorder) AS min,
 926                                    MAX(sortorder) AS max,
 927                                    COUNT(sortorder)  AS count
 928                             FROM ' . $CFG->prefix . 'course
 929                             WHERE category=' . $categoryid);
 930     if (is_object($info)) { // no courses?
 931         $max   = $info->max;
 932         $count = $info->count;
 933         $min   = $info->min;
 934         unset($info);
 935     }
 936
 937     if ($categoryid > 0 && $n==0) { // only passed category so don't shift it
 938         $n = $min;
 939     }
 940
 941     // $hasgap flag indicates whether there's a gap in the sequence
 942     $hasgap    = false;
 943     if ($max-$min+1 != $count) {
 944         $hasgap = true;
 945     }
 946
 947     // $mustshift indicates whether the sequence must be shifted to
 948     // meet its range
 949     $mustshift = false;
 950     if ($min < $n+$tolerance || $min > $n+$tolerance+$catgap ) {
 951         $mustshift = true;
 952     }
 953
 954     // actually sort only if there are courses,
 955     // and we meet one ofthe triggers:
 956     //  - safe flag
 957     //  - they are not in a continuos block
 958     //  - they are too close to the 'bottom'
 959     if ($count && ( $safe || $hasgap || $mustshift ) ) {
 960         // special, optimized case where all we need is to shift
 961         if ( $mustshift && !$safe && !$hasgap) {
 962             $shift = $n + $catgap - $min;
 963             if ($shift < $count) {
 964                 $shift = $count + $catgap;
 965             }
 966             // UPDATE course SET sortorder=sortorder+$shift
 967             execute_sql("UPDATE {$CFG->prefix}course
 968                          SET sortorder=sortorder+$shift
 969                          WHERE category=$categoryid", 0);
 970             $n = $n + $catgap + $count;
 971
 972         } else { // do it slowly
 973             $n = $n + $catgap;
 974             // if the new sequence overlaps the current sequence, lack of transactions
 975             // will stop us -- shift things aside for a moment...
 976             if ($safe || ($n >= $min && $n+$count+1 < $min && $CFG->dbfamily==='mysql')) {
 977                 $shift = $max + $n + 1000;
 978                 execute_sql("UPDATE {$CFG->prefix}course
 979                          SET sortorder=sortorder+$shift
 980                          WHERE category=$categoryid", 0);
 981             }
 982
 983             $courses = get_courses($categoryid, 'c.sortorder ASC', 'c.id,c.sortorder');
 984             begin_sql();
 985             $tx = true; // transaction sanity
 986             foreach ($courses as $course) {
 987                 if ($tx && $course->sortorder != $n ) { // save db traffic
 988                     $tx = $tx && set_field('course', 'sortorder', $n,
 989                                            'id', $course->id);
 990                 }
 991                 $n++;
 992             }
 993             if ($tx) {
 994                 commit_sql();
 995             } else {
 996                 rollback_sql();
 997                 if (!$safe) {
 998                     // if we failed when called with !safe, try
 999                     // to recover calling self with safe=true
1000                     return fix_course_sortorder($categoryid, $n, true, $depth, $path);
1001                 }
1002             }
1003         }
1004     }
1005     set_field('course_categories', 'coursecount', $count, 'id', $categoryid);
1006
1007     // $n could need updating
1008     $max = get_field_sql("SELECT MAX(sortorder) from {$CFG->prefix}course WHERE category=$categoryid");
1009     if ($max > $n) {
1010         $n = $max;
1011     }
1012
1013     if ($categories = get_categories($categoryid)) {
1014         foreach ($categories as $category) {
1015             $n = fix_course_sortorder($category->id, $n, $safe, $depth, $path);
1016         }
1017     }
1018
1019     return $n+1;
1020 }
1021
1022 /**
1023  * Ensure all courses have a valid course category
1024  * useful if a category has been removed manually
1025  **/
1026 function fix_coursecategory_orphans() {
1027
1028     global $CFG;
1029
1030     // Note: the handling of sortorder here is arguably
1031     // open to race conditions. Hard to fix here, unlikely
1032     // to hit anyone in production.
1033
1034     $sql = "SELECT c.id, c.category, c.shortname
1035             FROM {$CFG->prefix}course c
1036             LEFT OUTER JOIN {$CFG->prefix}course_categories cc ON c.category=cc.id
1037             WHERE cc.id IS NULL AND c.id != " . SITEID;
1038
1039     $rs = get_recordset_sql($sql);
1040
1041     if ($rs->RecordCount()){ // we have some orphans
1042
1043         // the "default" category is the lowest numbered...
1044         $default   = get_field_sql("SELECT MIN(id)
1045                                     FROM {$CFG->prefix}course_categories");
1046         $sortorder = get_field_sql("SELECT MAX(sortorder)
1047                                     FROM {$CFG->prefix}course
1048                                     WHERE category=$default");
1049
1050
1051         begin_sql();
1052         $tx = true;
1053         while ($tx && $course = rs_fetch_next_record($rs)) {
1054             $tx = $tx && set_field('course', 'category',  $default,     'id', $course->id);
1055             $tx = $tx && set_field('course', 'sortorder', ++$sortorder, 'id', $course->id);
1056         }
1057         if ($tx) {
1058             commit_sql();
1059         } else {
1060             rollback_sql();
1061         }
1062     }
1063 }
1064
1065 /**
1066  * List of remote courses that a user has access to via MNET.
1067  * Works only on the IDP
1068  *
1069  * @uses $CFG, $USER
1070  * @return array {@link $COURSE} of course objects
1071  */
1072 function get_my_remotecourses($userid=0) {
1073     global $CFG, $USER;
1074
1075     if (empty($userid)) {
1076         $userid = $USER->id;
1077     }
1078
1079     $sql = "SELECT c.remoteid, c.shortname, c.fullname,
1080                    c.hostid, c.summary, c.cat_name,
1081                    h.name AS hostname
1082             FROM   {$CFG->prefix}mnet_enrol_course c
1083             JOIN   {$CFG->prefix}mnet_enrol_assignments a ON c.id=a.courseid
1084             JOIN   {$CFG->prefix}mnet_host h        ON c.hostid=h.id
1085             WHERE  a.userid={$userid}";
1086
1087     return get_records_sql($sql);
1088 }
1089
1090 /**
1091  * List of remote hosts that a user has access to via MNET.
1092  * Works on the SP
1093  *
1094  * @uses $CFG, $USER
1095  * @return array of host objects
1096  */
1097 function get_my_remotehosts() {
1098     global $CFG, $USER;
1099
1100     if ($USER->mnethostid == $CFG->mnet_localhost_id) {
1101         return false; // Return nothing on the IDP
1102     }
1103     if (!empty($USER->mnet_foreign_host_array) && is_array($USER->mnet_foreign_host_array)) {
1104         return $USER->mnet_foreign_host_array;
1105     }
1106     return false;
1107 }
1108
1109 /**
1110  * This function creates a default separated/connected scale
1111  *
1112  * This function creates a default separated/connected scale
1113  * so there's something in the database.  The locations of
1114  * strings and files is a bit odd, but this is because we
1115  * need to maintain backward compatibility with many different
1116  * existing language translations and older sites.
1117  *
1118  * @uses $CFG
1119  */
1120 function make_default_scale() {
1121
1122     global $CFG;
1123
1124     $defaultscale = NULL;
1125     $defaultscale->courseid = 0;
1126     $defaultscale->userid = 0;
1127     $defaultscale->name  = get_string('separateandconnected');
1128     $defaultscale->scale = get_string('postrating1', 'forum').','.
1129                            get_string('postrating2', 'forum').','.
1130                            get_string('postrating3', 'forum');
1131     $defaultscale->timemodified = time();
1132
1133     /// Read in the big description from the file.  Note this is not
1134     /// HTML (despite the file extension) but Moodle format text.
1135     $parentlang = get_string('parentlang');
1136     if (is_readable($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
1137         $file = file($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
1138     } else if (is_readable($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
1139         $file = file($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
1140     } else if ($parentlang and is_readable($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
1141         $file = file($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
1142     } else if ($parentlang and is_readable($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
1143         $file = file($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
1144     } else if (is_readable($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html')) {
1145         $file = file($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html');
1146     } else {
1147         $file = '';
1148     }
1149
1150     $defaultscale->description = addslashes(implode('', $file));
1151
1152     if ($defaultscale->id = insert_record('scale', $defaultscale)) {
1153         execute_sql('UPDATE '. $CFG->prefix .'forum SET scale = \''. $defaultscale->id .'\'', false);
1154     }
1155 }
1156
1157
1158 /**
1159  * Returns a menu of all available scales from the site as well as the given course
1160  *
1161  * @uses $CFG
1162  * @param int $courseid The id of the course as found in the 'course' table.
1163  * @return object
1164  */
1165 function get_scales_menu($courseid=0) {
1166
1167     global $CFG;
1168
1169     $sql = "SELECT id, name FROM {$CFG->prefix}scale
1170              WHERE courseid = '0' or courseid = '$courseid'
1171           ORDER BY courseid ASC, name ASC";
1172
1173     if ($scales = get_records_sql_menu($sql)) {
1174         return $scales;
1175     }
1176
1177     make_default_scale();
1178
1179     return get_records_sql_menu($sql);
1180 }
1181
1182
1183
1184 /**
1185  * Given a set of timezone records, put them in the database,  replacing what is there
1186  *
1187  * @uses $CFG
1188  * @param array $timezones An array of timezone records
1189  */
1190 function update_timezone_records($timezones) {
1191 /// Given a set of timezone records, put them in the database
1192
1193     global $CFG;
1194
1195 /// Clear out all the old stuff
1196     execute_sql('TRUNCATE TABLE '.$CFG->prefix.'timezone', false);
1197
1198 /// Insert all the new stuff
1199     foreach ($timezones as $timezone) {
1200         insert_record('timezone', $timezone);
1201     }
1202 }
1203
1204
1205 /// MODULE FUNCTIONS /////////////////////////////////////////////////
1206
1207 /**
1208  * Just gets a raw list of all modules in a course
1209  *
1210  * @uses $CFG
1211  * @param int $courseid The id of the course as found in the 'course' table.
1212  * @return object
1213  */
1214 function get_course_mods($courseid) {
1215     global $CFG;
1216
1217     if (empty($courseid)) {
1218         return false; // avoid warnings
1219     }
1220
1221     return get_records_sql("SELECT cm.*, m.name as modname
1222                             FROM {$CFG->prefix}modules m,
1223                                  {$CFG->prefix}course_modules cm
1224                             WHERE cm.course = '$courseid'
1225                             AND cm.module = m.id ");
1226 }
1227
1228
1229 /**
1230  * Given an id of a course module, finds the coursemodule description
1231  *
1232  * @param string $modulename name of module type, eg. resource, assignment,...
1233  * @param int $cmid course module id (id in course_modules table)
1234  * @param int $courseid optional course id for extra validation
1235  * @return object course module instance with instance and module name
1236  */
1237 function get_coursemodule_from_id($modulename, $cmid, $courseid=0) {
1238
1239     global $CFG;
1240
1241     $courseselect = ($courseid) ? "cm.course = '$courseid' AND " : '';
1242
1243     return get_record_sql("SELECT cm.*, m.name, md.name as modname
1244                            FROM {$CFG->prefix}course_modules cm,
1245                                 {$CFG->prefix}modules md,
1246                                 {$CFG->prefix}$modulename m
1247                            WHERE $courseselect
1248                                  cm.id = '$cmid' AND
1249                                  cm.instance = m.id AND
1250                                  md.name = '$modulename' AND
1251                                  md.id = cm.module");
1252 }
1253
1254 /**
1255  * Given an instance number of a module, finds the coursemodule description
1256  *
1257  * @param string $modulename name of module type, eg. resource, assignment,...
1258  * @param int $instance module instance number (id in resource, assignment etc. table)
1259  * @param int $courseid optional course id for extra validation
1260  * @return object course module instance with instance and module name
1261  */
1262 function get_coursemodule_from_instance($modulename, $instance, $courseid=0) {
1263
1264     global $CFG;
1265
1266     $courseselect = ($courseid) ? "cm.course = '$courseid' AND " : '';
1267
1268     return get_record_sql("SELECT cm.*, m.name, md.name as modname
1269                            FROM {$CFG->prefix}course_modules cm,
1270                                 {$CFG->prefix}modules md,
1271                                 {$CFG->prefix}$modulename m
1272                            WHERE $courseselect
1273                                  cm.instance = m.id AND
1274                                  md.name = '$modulename' AND
1275                                  md.id = cm.module AND
1276                                  m.id = '$instance'");
1277
1278 }
1279
1280 /**
1281  * Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined
1282  *
1283  * Returns an array of all the active instances of a particular
1284  * module in given courses, sorted in the order they are defined
1285  * in the course.   Returns false on any errors.
1286  *
1287  * @uses $CFG
1288  * @param string  $modulename The name of the module to get instances for
1289  * @param array  $courses This depends on an accurate $course->modinfo
1290  * @return array of instances
1291  */
1292 function get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false) {
1293     global $CFG;
1294     if (empty($courses) || !is_array($courses) || count($courses) == 0) {
1295         return array();
1296     }
1297     if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode, cm.course
1298                             FROM {$CFG->prefix}course_modules cm,
1299                                  {$CFG->prefix}course_sections cw,
1300                                  {$CFG->prefix}modules md,
1301                                  {$CFG->prefix}$modulename m
1302                             WHERE cm.course IN (".implode(',',array_keys($courses)).") AND
1303                                   cm.instance = m.id AND
1304                                   cm.section = cw.id AND
1305                                   md.name = '$modulename' AND
1306                                   md.id = cm.module")) {
1307         return array();
1308     }
1309
1310     $outputarray = array();
1311
1312     foreach ($courses as $course) {
1313         if ($includeinvisible) {
1314             $invisible = -1;
1315         } else if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id), $userid)) {
1316             // Usually hide non-visible instances from students
1317             $invisible = -1;
1318         } else {
1319             $invisible = 0;
1320         }
1321
1322    /// Casting $course->modinfo to string prevents one notice when the field is null
1323         if (!$modinfo = unserialize((string)$course->modinfo)) {
1324             continue;
1325         }
1326         foreach ($modinfo as $mod) {
1327             if ($mod->mod == $modulename and $mod->visible > $invisible) {
1328                 $instance = $rawmods[$mod->cm];
1329                 if (!empty($mod->extra)) {
1330                     $instance->extra = $mod->extra;
1331                 }
1332                 $outputarray[] = $instance;
1333             }
1334         }
1335     }
1336
1337     return $outputarray;
1338
1339 }
1340
1341 /**
1342  * Returns an array of all the active instances of a particular module in a given course, sorted in the order they are defined
1343  *
1344  * Returns an array of all the active instances of a particular
1345  * module in a given course, sorted in the order they are defined
1346  * in the course.   Returns false on any errors.
1347  *
1348  * @uses $CFG
1349  * @param string  $modulename The name of the module to get instances for
1350  * @param object(course)  $course This depends on an accurate $course->modinfo
1351  */
1352 function get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false) {
1353
1354     global $CFG;
1355
1356     if (empty($course->modinfo)) {
1357         return array();
1358     }
1359
1360     if (!$modinfo = unserialize((string)$course->modinfo)) {
1361         return array();
1362     }
1363
1364     if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode,cm.groupingid
1365                             FROM {$CFG->prefix}course_modules cm,
1366                                  {$CFG->prefix}course_sections cw,
1367                                  {$CFG->prefix}modules md,
1368                                  {$CFG->prefix}$modulename m
1369                             WHERE cm.course = '$course->id' AND
1370                                   cm.instance = m.id AND
1371                                   cm.section = cw.id AND
1372                                   md.name = '$modulename' AND
1373                                   md.id = cm.module")) {
1374         return array();
1375     }
1376
1377     if ($includeinvisible) {
1378         $invisible = -1;
1379     } else if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id), $userid)) {
1380         // Usually hide non-visible instances from students
1381         $invisible = -1;
1382     } else {
1383         $invisible = 0;
1384     }
1385
1386     $outputarray = array();
1387
1388     foreach ($modinfo as $mod) {
1389         $mod->id = $mod->cm;
1390         $mod->course = $course->id;
1391         if (!groups_course_module_visible($mod)) {
1392             continue;
1393         }
1394         if ($mod->mod == $modulename and $mod->visible > $invisible) {
1395             $instance = $rawmods[$mod->cm];
1396             if (!empty($mod->extra)) {
1397                 $instance->extra = $mod->extra;
1398             }
1399             $outputarray[] = $instance;
1400         }
1401     }
1402
1403     return $outputarray;
1404
1405 }
1406
1407
1408 /**
1409  * Determine whether a module instance is visible within a course
1410  *
1411  * Given a valid module object with info about the id and course,
1412  * and the module's type (eg "forum") returns whether the object
1413  * is visible or not
1414  *
1415  * @uses $CFG
1416  * @param $moduletype Name of the module eg 'forum'
1417  * @param $module Object which is the instance of the module
1418  * @return bool
1419  */
1420 function instance_is_visible($moduletype, $module) {
1421
1422     global $CFG;
1423
1424     if (!empty($module->id)) {
1425         if ($records = get_records_sql("SELECT cm.instance, cm.visible, cm.groupingid, cm.id, cm.groupmembersonly, cm.course
1426                                         FROM {$CFG->prefix}course_modules cm,
1427                                              {$CFG->prefix}modules m
1428                                        WHERE cm.course = '$module->course' AND
1429                                              cm.module = m.id AND
1430                                              m.name = '$moduletype' AND
1431                                              cm.instance = '$module->id'")) {
1432
1433             foreach ($records as $record) { // there should only be one - use the first one
1434                 return $record->visible && groups_course_module_visible($record);
1435             }
1436         }
1437     }
1438     return true;  // visible by default!
1439 }
1440
1441
1442
1443
1444 /// LOG FUNCTIONS /////////////////////////////////////////////////////
1445
1446
1447 /**
1448  * Add an entry to the log table.
1449  *
1450  * Add an entry to the log table.  These are "action" focussed rather
1451  * than web server hits, and provide a way to easily reconstruct what
1452  * any particular student has been doing.
1453  *
1454  * @uses $CFG
1455  * @uses $USER
1456  * @uses $db
1457  * @uses $REMOTE_ADDR
1458  * @uses SITEID
1459  * @param    int     $courseid  The course id
1460  * @param    string  $module  The module name - e.g. forum, journal, resource, course, user etc
1461  * @param    string  $action  'view', 'update', 'add' or 'delete', possibly followed by another word to clarify.
1462  * @param    string  $url     The file and parameters used to see the results of the action
1463  * @param    string  $info    Additional description information
1464  * @param    string  $cm      The course_module->id if there is one
1465  * @param    string  $user    If log regards $user other than $USER
1466  */
1467 function add_to_log($courseid, $module, $action, $url='', $info='', $cm=0, $user=0) {
1468     // Note that this function intentionally does not follow the normal Moodle DB access idioms.
1469     // This is for a good reason: it is the most frequently used DB update function,
1470     // so it has been optimised for speed.
1471     global $db, $CFG, $USER;
1472
1473     if ($cm === '' || is_null($cm)) { // postgres won't translate empty string to its default
1474         $cm = 0;
1475     }
1476
1477     if ($user) {
1478         $userid = $user;
1479     } else {
1480         if (!empty($USER->realuser)) {  // Don't log
1481             return;
1482         }
1483         $userid = empty($USER->id) ? '0' : $USER->id;
1484     }
1485
1486     $REMOTE_ADDR = getremoteaddr();
1487
1488     $timenow = time();
1489     $info = addslashes($info);
1490     if (!empty($url)) { // could break doing html_entity_decode on an empty var.
1491         $url = html_entity_decode($url); // for php < 4.3.0 this is defined in moodlelib.php
1492     }
1493
1494     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; $PERF->logwrites++;};
1495
1496     if ($CFG->type = 'oci8po') {
1497         if (empty($info)) {
1498             $info = ' ';
1499         }
1500     }
1501
1502     $result = $db->Execute('INSERT INTO '. $CFG->prefix .'log (time, userid, course, ip, module, cmid, action, url, info)
1503         VALUES (' . "'$timenow', '$userid', '$courseid', '$REMOTE_ADDR', '$module', '$cm', '$action', '$url', '$info')");
1504
1505     if (!$result and debugging()) {
1506         echo '<p>Error: Could not insert a new entry to the Moodle log</p>';  // Don't throw an error
1507     }
1508
1509 /// Store lastaccess times for the current user, do not use in cron and other commandline scripts
1510
1511     if (!empty($USER->id) && ($userid == $USER->id) && !defined('FULLME')) {
1512         $db->Execute('UPDATE '. $CFG->prefix .'user
1513                          SET lastip=\''. $REMOTE_ADDR .'\', lastaccess=\''. $timenow .'\'
1514                        WHERE id = \''. $userid .'\' ');
1515         if ($courseid != SITEID && !empty($courseid)) {
1516             if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++;};
1517
1518             if ($record = get_record('user_lastaccess', 'userid', $userid, 'courseid', $courseid)) {
1519                 $record->timeaccess = $timenow;
1520                 return update_record('user_lastaccess', $record);
1521             } else {
1522                 $record = new object;
1523                 $record->userid = $userid;
1524                 $record->courseid = $courseid;
1525                 $record->timeaccess = $timenow;
1526                 return insert_record('user_lastaccess', $record);
1527             }
1528         }
1529     }
1530 }
1531
1532
1533 /**
1534  * Select all log records based on SQL criteria
1535  *
1536  * @uses $CFG
1537  * @param string $select SQL select criteria
1538  * @param string $order SQL order by clause to sort the records returned
1539  * @param string $limitfrom ?
1540  * @param int $limitnum ?
1541  * @param int $totalcount Passed in by reference.
1542  * @return object
1543  * @todo Finish documenting this function
1544  */
1545 function get_logs($select, $order='l.time DESC', $limitfrom='', $limitnum='', &$totalcount) {
1546     global $CFG;
1547
1548     if ($order) {
1549         $order = 'ORDER BY '. $order;
1550     }
1551
1552     $selectsql = $CFG->prefix .'log l LEFT JOIN '. $CFG->prefix .'user u ON l.userid = u.id '. ((strlen($select) > 0) ? 'WHERE '. $select : '');
1553     $countsql = $CFG->prefix.'log l '.((strlen($select) > 0) ? ' WHERE '. $select : '');
1554
1555     $totalcount = count_records_sql("SELECT COUNT(*) FROM $countsql");
1556
1557     return get_records_sql('SELECT l.*, u.firstname, u.lastname, u.picture
1558                                 FROM '. $selectsql .' '. $order, $limitfrom, $limitnum) ;
1559 }
1560
1561
1562 /**
1563  * Select all log records for a given course and user
1564  *
1565  * @uses $CFG
1566  * @uses DAYSECS
1567  * @param int $userid The id of the user as found in the 'user' table.
1568  * @param int $courseid The id of the course as found in the 'course' table.
1569  * @param string $coursestart ?
1570  * @todo Finish documenting this function
1571  */
1572 function get_logs_usercourse($userid, $courseid, $coursestart) {
1573     global $CFG;
1574
1575     if ($courseid) {
1576         $courseselect = ' AND course = \''. $courseid .'\' ';
1577     } else {
1578         $courseselect = '';
1579     }
1580
1581     return get_records_sql("SELECT floor((time - $coursestart)/". DAYSECS .") as day, count(*) as num
1582                             FROM {$CFG->prefix}log
1583                            WHERE userid = '$userid'
1584                              AND time > '$coursestart' $courseselect
1585                         GROUP BY day ");
1586 }
1587
1588 /**
1589  * Select all log records for a given course, user, and day
1590  *
1591  * @uses $CFG
1592  * @uses HOURSECS
1593  * @param int $userid The id of the user as found in the 'user' table.
1594  * @param int $courseid The id of the course as found in the 'course' table.
1595  * @param string $daystart ?
1596  * @return object
1597  * @todo Finish documenting this function
1598  */
1599 function get_logs_userday($userid, $courseid, $daystart) {
1600     global $CFG;
1601
1602     if ($courseid) {
1603         $courseselect = ' AND course = \''. $courseid .'\' ';
1604     } else {
1605         $courseselect = '';
1606     }
1607
1608     return get_records_sql("SELECT floor((time - $daystart)/". HOURSECS .") as hour, count(*) as num
1609                             FROM {$CFG->prefix}log
1610                            WHERE userid = '$userid'
1611                              AND time > '$daystart' $courseselect
1612                         GROUP BY hour ");
1613 }
1614
1615 /**
1616  * Returns an object with counts of failed login attempts
1617  *
1618  * Returns information about failed login attempts.  If the current user is
1619  * an admin, then two numbers are returned:  the number of attempts and the
1620  * number of accounts.  For non-admins, only the attempts on the given user
1621  * are shown.
1622  *
1623  * @param string $mode Either 'admin', 'teacher' or 'everybody'
1624  * @param string $username The username we are searching for
1625  * @param string $lastlogin The date from which we are searching
1626  * @return int
1627  */
1628 function count_login_failures($mode, $username, $lastlogin) {
1629
1630     $select = 'module=\'login\' AND action=\'error\' AND time > '. $lastlogin;
1631
1632     if (has_capability('moodle/site:config', get_context_instance(CONTEXT_SYSTEM, SITEID))) {    // Return information about all accounts
1633         if ($count->attempts = count_records_select('log', $select)) {
1634             $count->accounts = count_records_select('log', $select, 'COUNT(DISTINCT info)');
1635             return $count;
1636         }
1637     } else if ($mode == 'everybody' or ($mode == 'teacher' and isteacherinanycourse())) {
1638         if ($count->attempts = count_records_select('log', $select .' AND info = \''. $username .'\'')) {
1639             return $count;
1640         }
1641     }
1642     return NULL;
1643 }
1644
1645
1646 /// GENERAL HELPFUL THINGS  ///////////////////////////////////
1647
1648 /**
1649  * Dump a given object's information in a PRE block.
1650  *
1651  * Mostly just used for debugging.
1652  *
1653  * @param mixed $object The data to be printed
1654  */
1655 function print_object($object) {
1656     echo '<pre class="notifytiny">' . htmlspecialchars(print_r($object,true)) . '</pre>';
1657 }
1658
1659 function course_parent_visible($course = null) {
1660     global $CFG;
1661
1662     if (empty($course)) {
1663         return true;
1664     }
1665     if (!empty($CFG->allowvisiblecoursesinhiddencategories)) {
1666         return true;
1667     }
1668     return category_parent_visible($course->category);
1669 }
1670
1671 function category_parent_visible($parent = 0) {
1672
1673     static $visible;
1674
1675     if (!$parent) {
1676         return true;
1677     }
1678
1679     if (empty($visible)) {
1680         $visible = array(); // initialize
1681     }
1682
1683     if (array_key_exists($parent,$visible)) {
1684         return $visible[$parent];
1685     }
1686
1687     $category = get_record('course_categories', 'id', $parent);
1688     $list = explode('/', preg_replace('/^\/(.*)$/', '$1', $category->path));
1689     $list[] = $parent;
1690     $parents = get_records_list('course_categories', 'id', implode(',', $list), 'depth DESC');
1691     $v = true;
1692     foreach ($parents as $p) {
1693         if (!$p->visible) {
1694             $v = false;
1695         }
1696     }
1697     $visible[$parent] = $v; // now cache it
1698     return $v;
1699 }
1700
1701 /**
1702  * This function is the official hook inside XMLDB stuff to delegate its debug to one
1703  * external function.
1704  *
1705  * Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before
1706  * using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-)
1707  *
1708  * @param $message string contains the error message
1709  * @param $object object XMLDB object that fired the debug
1710  */
1711 function xmldb_debug($message, $object) {
1712
1713     debugging($message, DEBUG_DEVELOPER);
1714 }
1715
1716 /**
1717  * Get the lists of courses the current user has $cap capability in
1718  * I am not sure if this is needed, it loops through all courses so
1719  * could cause performance problems.
1720  * If it's not used, we can use a faster function to detect
1721  * capability in restorelib.php
1722  * @param string $cap
1723  * @return array
1724  */
1725 function get_capability_courses($cap) {
1726     global $USER;
1727
1728     $mycourses = array();
1729     if ($courses = get_records('course')) {
1730         foreach ($courses as $course) {
1731             if (has_capability($cap, get_context_instance(CONTEXT_COURSE, $course->id))) {
1732                 $mycourses[] = $course->id;
1733             }
1734         }
1735     }
1736
1737     return $mycourses;
1738 }
1739
1740 /**
1741  * true or false function to see if user can create any courses at all
1742  * @return bool
1743  */
1744 function user_can_create_courses() {
1745     global $USER;
1746     // if user has course creation capability at any site or course cat, then return true;
1747
1748     if (has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
1749         return true;
1750     } else {
1751         return (bool) count(get_creatable_categories());
1752     }
1753
1754 }
1755
1756 /**
1757  * get the list of categories the current user can create courses in
1758  * @return array
1759  */
1760 function get_creatable_categories() {
1761
1762     $creatablecats = array();
1763     if ($cats = get_records('course_categories')) {
1764         foreach ($cats as $cat) {
1765             if (has_capability('moodle/course:create', get_context_instance(CONTEXT_COURSECAT, $cat->id))) {
1766                 $creatablecats[$cat->id] = $cat->name;
1767             }
1768         }
1769     }
1770     return $creatablecats;
1771 }
1772
1773 // vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140:
1774 ?>