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%') ";
 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                 return array_slice($USER->mycourses[$doanything], 0, $limit, true);
 623             } else {
 624                 return $USER->mycourses[$doanything];
 625             }
 626         } else {
 627             // now, this is the first call, i.e. no cache, and we are using defaults, with a limit supplied,
 628             // we need to store the limit somewhere, retrieve all, cache properly and then slice the array
 629             // to return the proper number of entries. This is so that we don't keep missing calls like limit 20,20,20
 630             if ($limit) {
 631                 $reallimit = $limit;
 632                 $limit = 0;
 633             }
 634         }
 635     }
 636
 637     $mycourses = array();
 638
 639     // Fix fields to refer to the course table c
 640     $fields=preg_replace('/([a-z0-9*]+)/','c.$1',$fields);
 641
 642     // Attempt to filter the list of courses in order to reduce the number
 643     // of queries in the next part.
 644
 645     // Check root permissions
 646     $sitecontext = get_context_instance(CONTEXT_SYSTEM, SITEID);
 647
 648     // Guest's do not have any courses
 649     if (has_capability('moodle/legacy:guest',$sitecontext,$userid,false)) {
 650         return(array());
 651     }
 652
 653     // we can optimise some things for true admins
 654     $candoanything = false;
 655     if ($doanything && has_capability('moodle/site:doanything',$sitecontext,$userid,true)) {
 656         $candoanything = true;
 657     }
 658
 659     if ($candoanything || has_capability('moodle/course:view',$sitecontext,$userid,$doanything)) {
 660         // User can view all courses, although there might be exceptions
 661         // which we will filter later.
 662         $rs = get_recordset('course c', '', '', $sort, $fields);
 663     } else {
 664         // The only other context level above courses that applies to moodle/course:view
 665         // is category. So we consider:
 666         // 1. All courses in which the user is assigned a role
 667         // 2. All courses in categories in which the user is assigned a role
 668         // 2BIS. All courses in subcategories in which the user gets assignment because he is assigned in one of its ascendant categories
 669         // 3. All courses which have overrides for moodle/course:view
 670         // Remember that this is just a filter. We check each individual course later.
 671         // However for a typical student on a large system this can reduce the
 672         // number of courses considered from around 2,000 to around 2, with corresponding
 673         // reduction in the number of queries needed.
 674         $rs=get_recordset_sql("
 675             SELECT $fields
 676             FROM {$CFG->prefix}course c, (
 677                 SELECT
 678                     c.id
 679                 FROM
 680                     {$CFG->prefix}role_assignments ra
 681                     INNER JOIN {$CFG->prefix}context x ON x.id         = ra.contextid
 682                     INNER JOIN {$CFG->prefix}course c  ON x.instanceid = c.id
 683                 WHERE
 684                     ra.userid      = $userid AND
 685                     x.contextlevel = 50
 686                 UNION
 687                 SELECT
 688                     c.id
 689                 FROM
 690                     {$CFG->prefix}role_assignments ra
 691                     INNER JOIN {$CFG->prefix}context x ON x.id = ra.contextid
 692                     INNER JOIN {$CFG->prefix}course_categories a ON a.path LIKE ".sql_concat("'%/'", 'x.instanceid', "'/%'")." OR x.instanceid = a.id
 693                     INNER JOIN {$CFG->prefix}course c ON c.category = a.id
 694                 WHERE
 695                     ra.userid = $userid AND
 696                     x.contextlevel = 40
 697                 UNION
 698                 SELECT
 699                     c.id
 700                 FROM
 701                     {$CFG->prefix}role_capabilities ca
 702                     INNER JOIN {$CFG->prefix}context x ON x.id = ca.contextid
 703                     INNER JOIN {$CFG->prefix}course c  ON c.id = x.instanceid
 704                 WHERE
 705                     ca.capability  = 'moodle/course:view' AND
 706                     ca.contextid  != {$sitecontext->id} AND
 707                     x.contextlevel = 50
 708             ) cids
 709             WHERE c.id = cids.id
 710             ORDER BY $sort"
 711         );
 712     }
 713
 714     if ($rs && $rs->RecordCount() > 0) {
 715         while ($course = rs_fetch_next_record($rs)) {
 716             if ($course->id != SITEID) {
 717
 718                 if ($candoanything) { // no need for further checks...
 719                     $mycourses[$course->id] = $course;
 720                     continue;
 721                 }
 722
 723                 // users with moodle/course:view are considered course participants
 724                 // the course needs to be visible, or user must have moodle/course:viewhiddencourses
 725                 // capability set to view hidden courses
 726                 $context = get_context_instance(CONTEXT_COURSE, $course->id);
 727                 if ( has_capability('moodle/course:view', $context, $userid, $doanything) &&
 728                     !has_capability('moodle/legacy:guest', $context, $userid, false) &&
 729                     ($course->visible || has_capability('moodle/course:viewhiddencourses', $context, $userid))) {
 730                     $mycourses[$course->id] = $course;
 731
 732                     // Only return a limited number of courses if limit is set
 733                     if($limit>0) {
 734                         $limit--;
 735                         if($limit==0) {
 736                             break;
 737                         }
 738                     }
 739                 }
 740             }
 741         }
 742     }
 743
 744     // Cache if using default params...
 745     if (!empty($USER->id) && ($USER->id == $userid) && $usingdefaults && $limit == 0) {
 746         $USER->mycourses[$doanything] = $mycourses;
 747     }
 748
 749     if (!empty($mycourses) && $reallimit) {
 750         return array_slice($mycourses, 0, $reallimit, true);
 751     } else {
 752         return $mycourses;
 753     }
 754 }
 755
 756
 757 /**
 758  * A list of courses that match a search
 759  *
 760  * @uses $CFG
 761  * @param array $searchterms ?
 762  * @param string $sort ?
 763  * @param int $page ?
 764  * @param int $recordsperpage ?
 765  * @param int $totalcount Passed in by reference. ?
 766  * @return object {@link $COURSE} records
 767  */
 768 function get_courses_search($searchterms, $sort='fullname ASC', $page=0, $recordsperpage=50, &$totalcount) {
 769
 770     global $CFG;
 771
 772     //to allow case-insensitive search for postgesql
 773     if ($CFG->dbfamily == 'postgres') {
 774         $LIKE = 'ILIKE';
 775         $NOTLIKE = 'NOT ILIKE';   // case-insensitive
 776         $REGEXP = '~*';
 777         $NOTREGEXP = '!~*';
 778     } else {
 779         $LIKE = 'LIKE';
 780         $NOTLIKE = 'NOT LIKE';
 781         $REGEXP = 'REGEXP';
 782         $NOTREGEXP = 'NOT REGEXP';
 783     }
 784
 785     $fullnamesearch = '';
 786     $summarysearch = '';
 787
 788     foreach ($searchterms as $searchterm) {
 789
 790     /// Under Oracle and MSSQL, trim the + and - operators and perform
 791     /// simpler LIKE search
 792         if ($CFG->dbfamily == 'oracle' || $CFG->dbfamily == 'mssql') {
 793             $searchterm = trim($searchterm, '+-');
 794         }
 795
 796         if ($fullnamesearch) {
 797             $fullnamesearch .= ' AND ';
 798         }
 799         if ($summarysearch) {
 800             $summarysearch .= ' AND ';
 801         }
 802
 803         if (substr($searchterm,0,1) == '+') {
 804             $searchterm = substr($searchterm,1);
 805             $summarysearch .= " summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
 806             $fullnamesearch .= " fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
 807         } else if (substr($searchterm,0,1) == "-") {
 808             $searchterm = substr($searchterm,1);
 809             $summarysearch .= " summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
 810             $fullnamesearch .= " fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' ";
 811         } else {
 812             $summarysearch .= ' summary '. $LIKE .' \'%'. $searchterm .'%\' ';
 813             $fullnamesearch .= ' fullname '. $LIKE .' \'%'. $searchterm .'%\' ';
 814         }
 815
 816     }
 817
 818     $selectsql = $CFG->prefix .'course WHERE ('. $fullnamesearch .' OR '. $summarysearch .') AND category > \'0\'';
 819
 820     $totalcount = count_records_sql('SELECT COUNT(*) FROM '. $selectsql);
 821
 822     $courses = get_records_sql('SELECT * FROM '. $selectsql .' ORDER BY '. $sort, $page, $recordsperpage);
 823
 824     if ($courses) {  /// Remove unavailable courses from the list
 825         foreach ($courses as $key => $course) {
 826             if (!$course->visible) {
 827                 if (!has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id))) {
 828                     unset($courses[$key]);
 829                     $totalcount--;
 830                 }
 831             }
 832         }
 833     }
 834
 835     return $courses;
 836 }
 837
 838
 839 /**
 840  * Returns a sorted list of categories
 841  *
 842  * @param string $parent The parent category if any
 843  * @param string $sort the sortorder
 844  * @return array of categories
 845  */
 846 function get_categories($parent='none', $sort='sortorder ASC') {
 847
 848     if ($parent === 'none') {
 849         $categories = get_records('course_categories', '', '', $sort);
 850     } else {
 851         $categories = get_records('course_categories', 'parent', $parent, $sort);
 852     }
 853     if ($categories) {  /// Remove unavailable categories from the list
 854         foreach ($categories as $key => $category) {
 855             if (!$category->visible) {
 856                 if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_COURSECAT, $category->id))) {
 857                     unset($categories[$key]);
 858                 }
 859             }
 860         }
 861     }
 862     return $categories;
 863 }
 864
 865
 866 /**
 867  * Returns an array of category ids of all the subcategories for a given
 868  * category.
 869  * @param $catid - The id of the category whose subcategories we want to find.
 870  * @return array of category ids.
 871  */
 872 function get_all_subcategories($catid) {
 873
 874     $subcats = array();
 875
 876     if ($categories = get_records('course_categories', 'parent', $catid)) {
 877         foreach ($categories as $cat) {
 878             array_push($subcats, $cat->id);
 879             $subcats = array_merge($subcats, get_all_subcategories($cat->id));
 880         }
 881     }
 882     return $subcats;
 883 }
 884
 885
 886 /**
 887 * This recursive function makes sure that the courseorder is consecutive
 888 *
 889 * @param    type description
 890 *
 891 * $n is the starting point, offered only for compatilibity -- will be ignored!
 892 * $safe (bool) prevents it from assuming category-sortorder is unique, used to upgrade
 893 *       safely from 1.4 to 1.5
 894 */
 895 function fix_course_sortorder($categoryid=0, $n=0, $safe=0, $depth=0, $path='') {
 896
 897     global $CFG;
 898
 899     $count = 0;
 900
 901     $catgap    = 1000; // "standard" category gap
 902     $tolerance = 200;  // how "close" categories can get
 903
 904     if ($categoryid > 0){
 905         // update depth and path
 906         $cat   = get_record('course_categories', 'id', $categoryid);
 907         if ($cat->parent == 0) {
 908             $depth = 0;
 909             $path  = '';
 910         } else if ($depth == 0 ) { // doesn't make sense; get from DB
 911             // this is only called if the $depth parameter looks dodgy
 912             $parent = get_record('course_categories', 'id', $cat->parent);
 913             $path  = $parent->path;
 914             $depth = $parent->depth;
 915         }
 916         $path  = $path . '/' . $categoryid;
 917         $depth = $depth + 1;
 918
 919         set_field('course_categories', 'path',  addslashes($path),  'id', $categoryid);
 920         set_field('course_categories', 'depth', $depth, 'id', $categoryid);
 921     }
 922
 923     // get some basic info about courses in the category
 924     $info = get_record_sql('SELECT MIN(sortorder) AS min,
 925                                    MAX(sortorder) AS max,
 926                                    COUNT(sortorder)  AS count
 927                             FROM ' . $CFG->prefix . 'course
 928                             WHERE category=' . $categoryid);
 929     if (is_object($info)) { // no courses?
 930         $max   = $info->max;
 931         $count = $info->count;
 932         $min   = $info->min;
 933         unset($info);
 934     }
 935
 936     if ($categoryid > 0 && $n==0) { // only passed category so don't shift it
 937         $n = $min;
 938     }
 939
 940     // $hasgap flag indicates whether there's a gap in the sequence
 941     $hasgap    = false;
 942     if ($max-$min+1 != $count) {
 943         $hasgap = true;
 944     }
 945
 946     // $mustshift indicates whether the sequence must be shifted to
 947     // meet its range
 948     $mustshift = false;
 949     if ($min < $n+$tolerance || $min > $n+$tolerance+$catgap ) {
 950         $mustshift = true;
 951     }
 952
 953     // actually sort only if there are courses,
 954     // and we meet one ofthe triggers:
 955     //  - safe flag
 956     //  - they are not in a continuos block
 957     //  - they are too close to the 'bottom'
 958     if ($count && ( $safe || $hasgap || $mustshift ) ) {
 959         // special, optimized case where all we need is to shift
 960         if ( $mustshift && !$safe && !$hasgap) {
 961             $shift = $n + $catgap - $min;
 962             // UPDATE course SET sortorder=sortorder+$shift
 963             execute_sql("UPDATE {$CFG->prefix}course
 964                          SET sortorder=sortorder+$shift
 965                          WHERE category=$categoryid", 0);
 966             $n = $n + $catgap + $count;
 967
 968         } else { // do it slowly
 969             $n = $n + $catgap;
 970             // if the new sequence overlaps the current sequence, lack of transactions
 971             // will stop us -- shift things aside for a moment...
 972             if ($safe || ($n >= $min && $n+$count+1 < $min && $CFG->dbfamily==='mysql')) {
 973                 $shift = $max + $n + 1000;
 974                 execute_sql("UPDATE {$CFG->prefix}course
 975                          SET sortorder=sortorder+$shift
 976                          WHERE category=$categoryid", 0);
 977             }
 978
 979             $courses = get_courses($categoryid, 'c.sortorder ASC', 'c.id,c.sortorder');
 980             begin_sql();
 981             foreach ($courses as $course) {
 982                 if ($course->sortorder != $n ) { // save db traffic
 983                     set_field('course', 'sortorder', $n, 'id', $course->id);
 984                 }
 985                 $n++;
 986             }
 987             commit_sql();
 988         }
 989     }
 990     set_field('course_categories', 'coursecount', $count, 'id', $categoryid);
 991
 992     // $n could need updating
 993     $max = get_field_sql("SELECT MAX(sortorder) from {$CFG->prefix}course WHERE category=$categoryid");
 994     if ($max > $n) {
 995         $n = $max;
 996     }
 997
 998     if ($categories = get_categories($categoryid)) {
 999         foreach ($categories as $category) {
1000             $n = fix_course_sortorder($category->id, $n, $safe, $depth, $path);
1001         }
1002     }
1003
1004     return $n+1;
1005 }
1006
1007 /**
1008  * List of remote courses that a user has access to via MNET.
1009  * Works only on the IDP
1010  *
1011  * @uses $CFG, $USER
1012  * @return array {@link $COURSE} of course objects
1013  */
1014 function get_my_remotecourses($userid=0) {
1015     global $CFG, $USER;
1016
1017     if (empty($userid)) {
1018         $userid = $USER->id;
1019     }
1020
1021     $sql = "SELECT c.remoteid, c.shortname, c.fullname,
1022                    c.hostid, c.summary, c.cat_name,
1023                    h.name AS hostname
1024             FROM   {$CFG->prefix}mnet_enrol_course c
1025             JOIN   {$CFG->prefix}mnet_enrol_assignments a ON c.id=a.courseid
1026             JOIN   {$CFG->prefix}mnet_host h        ON c.hostid=h.id
1027             WHERE  a.userid={$userid}";
1028
1029     return get_records_sql($sql);
1030 }
1031
1032 /**
1033  * List of remote hosts that a user has access to via MNET.
1034  * Works on the SP
1035  *
1036  * @uses $CFG, $USER
1037  * @return array of host objects
1038  */
1039 function get_my_remotehosts() {
1040     global $CFG, $USER;
1041
1042     if ($USER->mnethostid == $CFG->mnet_localhost_id) {
1043         return false; // Return nothing on the IDP
1044     }
1045     if (!empty($USER->mnet_foreign_host_array) && is_array($USER->mnet_foreign_host_array)) {
1046         return $USER->mnet_foreign_host_array;
1047     }
1048     return false;
1049 }
1050
1051 /**
1052  * This function creates a default separated/connected scale
1053  *
1054  * This function creates a default separated/connected scale
1055  * so there's something in the database.  The locations of
1056  * strings and files is a bit odd, but this is because we
1057  * need to maintain backward compatibility with many different
1058  * existing language translations and older sites.
1059  *
1060  * @uses $CFG
1061  */
1062 function make_default_scale() {
1063
1064     global $CFG;
1065
1066     $defaultscale = NULL;
1067     $defaultscale->courseid = 0;
1068     $defaultscale->userid = 0;
1069     $defaultscale->name  = get_string('separateandconnected');
1070     $defaultscale->scale = get_string('postrating1', 'forum').','.
1071                            get_string('postrating2', 'forum').','.
1072                            get_string('postrating3', 'forum');
1073     $defaultscale->timemodified = time();
1074
1075     /// Read in the big description from the file.  Note this is not
1076     /// HTML (despite the file extension) but Moodle format text.
1077     $parentlang = get_string('parentlang');
1078     if (is_readable($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
1079         $file = file($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
1080     } else if (is_readable($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
1081         $file = file($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
1082     } else if ($parentlang and is_readable($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
1083         $file = file($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
1084     } else if ($parentlang and is_readable($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
1085         $file = file($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
1086     } else if (is_readable($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html')) {
1087         $file = file($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html');
1088     } else {
1089         $file = '';
1090     }
1091
1092     $defaultscale->description = addslashes(implode('', $file));
1093
1094     if ($defaultscale->id = insert_record('scale', $defaultscale)) {
1095         execute_sql('UPDATE '. $CFG->prefix .'forum SET scale = \''. $defaultscale->id .'\'', false);
1096     }
1097 }
1098
1099
1100 /**
1101  * Returns a menu of all available scales from the site as well as the given course
1102  *
1103  * @uses $CFG
1104  * @param int $courseid The id of the course as found in the 'course' table.
1105  * @return object
1106  */
1107 function get_scales_menu($courseid=0) {
1108
1109     global $CFG;
1110
1111     $sql = "SELECT id, name FROM {$CFG->prefix}scale
1112              WHERE courseid = '0' or courseid = '$courseid'
1113           ORDER BY courseid ASC, name ASC";
1114
1115     if ($scales = get_records_sql_menu($sql)) {
1116         return $scales;
1117     }
1118
1119     make_default_scale();
1120
1121     return get_records_sql_menu($sql);
1122 }
1123
1124
1125
1126 /**
1127  * Given a set of timezone records, put them in the database,  replacing what is there
1128  *
1129  * @uses $CFG
1130  * @param array $timezones An array of timezone records
1131  */
1132 function update_timezone_records($timezones) {
1133 /// Given a set of timezone records, put them in the database
1134
1135     global $CFG;
1136
1137 /// Clear out all the old stuff
1138     execute_sql('TRUNCATE TABLE '.$CFG->prefix.'timezone', false);
1139
1140 /// Insert all the new stuff
1141     foreach ($timezones as $timezone) {
1142         insert_record('timezone', $timezone);
1143     }
1144 }
1145
1146
1147 /// MODULE FUNCTIONS /////////////////////////////////////////////////
1148
1149 /**
1150  * Just gets a raw list of all modules in a course
1151  *
1152  * @uses $CFG
1153  * @param int $courseid The id of the course as found in the 'course' table.
1154  * @return object
1155  */
1156 function get_course_mods($courseid) {
1157     global $CFG;
1158
1159     if (empty($courseid)) {
1160         return false; // avoid warnings
1161     }
1162
1163     return get_records_sql("SELECT cm.*, m.name as modname
1164                             FROM {$CFG->prefix}modules m,
1165                                  {$CFG->prefix}course_modules cm
1166                             WHERE cm.course = '$courseid'
1167                             AND cm.module = m.id ");
1168 }
1169
1170
1171 /**
1172  * Given an id of a course module, finds the coursemodule description
1173  *
1174  * @param string $modulename name of module type, eg. resource, assignment,...
1175  * @param int $cmid course module id (id in course_modules table)
1176  * @param int $courseid optional course id for extra validation
1177  * @return object course module instance with instance and module name
1178  */
1179 function get_coursemodule_from_id($modulename, $cmid, $courseid=0) {
1180
1181     global $CFG;
1182
1183     $courseselect = ($courseid) ? "cm.course = '$courseid' AND " : '';
1184
1185     return get_record_sql("SELECT cm.*, m.name, md.name as modname
1186                            FROM {$CFG->prefix}course_modules cm,
1187                                 {$CFG->prefix}modules md,
1188                                 {$CFG->prefix}$modulename m
1189                            WHERE $courseselect
1190                                  cm.id = '$cmid' AND
1191                                  cm.instance = m.id AND
1192                                  md.name = '$modulename' AND
1193                                  md.id = cm.module");
1194 }
1195
1196 /**
1197  * Given an instance number of a module, finds the coursemodule description
1198  *
1199  * @param string $modulename name of module type, eg. resource, assignment,...
1200  * @param int $instance module instance number (id in resource, assignment etc. table)
1201  * @param int $courseid optional course id for extra validation
1202  * @return object course module instance with instance and module name
1203  */
1204 function get_coursemodule_from_instance($modulename, $instance, $courseid=0) {
1205
1206     global $CFG;
1207
1208     $courseselect = ($courseid) ? "cm.course = '$courseid' AND " : '';
1209
1210     return get_record_sql("SELECT cm.*, m.name, md.name as modname
1211                            FROM {$CFG->prefix}course_modules cm,
1212                                 {$CFG->prefix}modules md,
1213                                 {$CFG->prefix}$modulename m
1214                            WHERE $courseselect
1215                                  cm.instance = m.id AND
1216                                  md.name = '$modulename' AND
1217                                  md.id = cm.module AND
1218                                  m.id = '$instance'");
1219
1220 }
1221
1222 /**
1223  * Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined
1224  *
1225  * Returns an array of all the active instances of a particular
1226  * module in given courses, sorted in the order they are defined
1227  * in the course.   Returns false on any errors.
1228  *
1229  * @uses $CFG
1230  * @param string  $modulename The name of the module to get instances for
1231  * @param array  $courses This depends on an accurate $course->modinfo
1232  * @return array of instances
1233  */
1234 function get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false) {
1235     global $CFG;
1236     if (empty($courses) || !is_array($courses) || count($courses) == 0) {
1237         return array();
1238     }
1239     if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode, cm.course
1240                             FROM {$CFG->prefix}course_modules cm,
1241                                  {$CFG->prefix}course_sections cw,
1242                                  {$CFG->prefix}modules md,
1243                                  {$CFG->prefix}$modulename m
1244                             WHERE cm.course IN (".implode(',',array_keys($courses)).") AND
1245                                   cm.instance = m.id AND
1246                                   cm.section = cw.id AND
1247                                   md.name = '$modulename' AND
1248                                   md.id = cm.module")) {
1249         return array();
1250     }
1251
1252     $outputarray = array();
1253
1254     foreach ($courses as $course) {
1255         if ($includeinvisible) {
1256             $invisible = -1;
1257         } else if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id), $userid)) {
1258             // Usually hide non-visible instances from students
1259             $invisible = -1;
1260         } else {
1261             $invisible = 0;
1262         }
1263
1264    /// Casting $course->modinfo to string prevents one notice when the field is null
1265         if (!$modinfo = unserialize((string)$course->modinfo)) {
1266             continue;
1267         }
1268         foreach ($modinfo as $mod) {
1269             if ($mod->mod == $modulename and $mod->visible > $invisible) {
1270                 $instance = $rawmods[$mod->cm];
1271                 if (!empty($mod->extra)) {
1272                     $instance->extra = $mod->extra;
1273                 }
1274                 $outputarray[] = $instance;
1275             }
1276         }
1277     }
1278
1279     return $outputarray;
1280
1281 }
1282
1283 /**
1284  * Returns an array of all the active instances of a particular module in a given course, sorted in the order they are defined
1285  *
1286  * Returns an array of all the active instances of a particular
1287  * module in a given course, sorted in the order they are defined
1288  * in the course.   Returns false on any errors.
1289  *
1290  * @uses $CFG
1291  * @param string  $modulename The name of the module to get instances for
1292  * @param object(course)  $course This depends on an accurate $course->modinfo
1293  */
1294 function get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false) {
1295
1296     global $CFG;
1297
1298     if (empty($course->modinfo)) {
1299         return array();
1300     }
1301
1302     if (!$modinfo = unserialize((string)$course->modinfo)) {
1303         return array();
1304     }
1305
1306     if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode
1307                             FROM {$CFG->prefix}course_modules cm,
1308                                  {$CFG->prefix}course_sections cw,
1309                                  {$CFG->prefix}modules md,
1310                                  {$CFG->prefix}$modulename m
1311                             WHERE cm.course = '$course->id' AND
1312                                   cm.instance = m.id AND
1313                                   cm.section = cw.id AND
1314                                   md.name = '$modulename' AND
1315                                   md.id = cm.module")) {
1316         return array();
1317     }
1318
1319     if ($includeinvisible) {
1320         $invisible = -1;
1321     } else if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id), $userid)) {
1322         // Usually hide non-visible instances from students
1323         $invisible = -1;
1324     } else {
1325         $invisible = 0;
1326     }
1327
1328     $outputarray = array();
1329
1330     foreach ($modinfo as $mod) {
1331         if ($mod->mod == $modulename and $mod->visible > $invisible) {
1332             $instance = $rawmods[$mod->cm];
1333             if (!empty($mod->extra)) {
1334                 $instance->extra = $mod->extra;
1335             }
1336             $outputarray[] = $instance;
1337         }
1338     }
1339
1340     return $outputarray;
1341
1342 }
1343
1344
1345 /**
1346  * Determine whether a module instance is visible within a course
1347  *
1348  * Given a valid module object with info about the id and course,
1349  * and the module's type (eg "forum") returns whether the object
1350  * is visible or not
1351  *
1352  * @uses $CFG
1353  * @param $moduletype Name of the module eg 'forum'
1354  * @param $module Object which is the instance of the module
1355  * @return bool
1356  */
1357 function instance_is_visible($moduletype, $module) {
1358
1359     global $CFG;
1360
1361     if (!empty($module->id)) {
1362         if ($records = get_records_sql("SELECT cm.instance, cm.visible
1363                                         FROM {$CFG->prefix}course_modules cm,
1364                                              {$CFG->prefix}modules m
1365                                        WHERE cm.course = '$module->course' AND
1366                                              cm.module = m.id AND
1367                                              m.name = '$moduletype' AND
1368                                              cm.instance = '$module->id'")) {
1369
1370             foreach ($records as $record) { // there should only be one - use the first one
1371                 return $record->visible;
1372             }
1373         }
1374     }
1375     return true;  // visible by default!
1376 }
1377
1378
1379
1380
1381 /// LOG FUNCTIONS /////////////////////////////////////////////////////
1382
1383
1384 /**
1385  * Add an entry to the log table.
1386  *
1387  * Add an entry to the log table.  These are "action" focussed rather
1388  * than web server hits, and provide a way to easily reconstruct what
1389  * any particular student has been doing.
1390  *
1391  * @uses $CFG
1392  * @uses $USER
1393  * @uses $db
1394  * @uses $REMOTE_ADDR
1395  * @uses SITEID
1396  * @param    int     $courseid  The course id
1397  * @param    string  $module  The module name - e.g. forum, journal, resource, course, user etc
1398  * @param    string  $action  'view', 'update', 'add' or 'delete', possibly followed by another word to clarify.
1399  * @param    string  $url     The file and parameters used to see the results of the action
1400  * @param    string  $info    Additional description information
1401  * @param    string  $cm      The course_module->id if there is one
1402  * @param    string  $user    If log regards $user other than $USER
1403  */
1404 function add_to_log($courseid, $module, $action, $url='', $info='', $cm=0, $user=0) {
1405     // Note that this function intentionally does not follow the normal Moodle DB access idioms.
1406     // This is for a good reason: it is the most frequently used DB update function,
1407     // so it has been optimised for speed.
1408     global $db, $CFG, $USER;
1409
1410     if ($cm === '' || is_null($cm)) { // postgres won't translate empty string to its default
1411         $cm = 0;
1412     }
1413
1414     if ($user) {
1415         $userid = $user;
1416     } else {
1417         if (!empty($USER->realuser)) {  // Don't log
1418             return;
1419         }
1420         $userid = empty($USER->id) ? '0' : $USER->id;
1421     }
1422
1423     $REMOTE_ADDR = getremoteaddr();
1424
1425     $timenow = time();
1426     $info = addslashes($info);
1427     if (!empty($url)) { // could break doing html_entity_decode on an empty var.
1428         $url = html_entity_decode($url); // for php < 4.3.0 this is defined in moodlelib.php
1429     }
1430
1431     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; $PERF->logwrites++;};
1432
1433     if ($CFG->type = 'oci8po') {
1434         if (empty($info)) {
1435             $info = ' ';
1436         }
1437     }
1438
1439     $result = $db->Execute('INSERT INTO '. $CFG->prefix .'log (time, userid, course, ip, module, cmid, action, url, info)
1440         VALUES (' . "'$timenow', '$userid', '$courseid', '$REMOTE_ADDR', '$module', '$cm', '$action', '$url', '$info')");
1441
1442     if (!$result and debugging()) {
1443         echo '<p>Error: Could not insert a new entry to the Moodle log</p>';  // Don't throw an error
1444     }
1445
1446 /// Store lastaccess times for the current user, do not use in cron and other commandline scripts
1447
1448     if (!empty($USER->id) && ($userid == $USER->id) && !defined('FULLME')) {
1449         $db->Execute('UPDATE '. $CFG->prefix .'user
1450                          SET lastip=\''. $REMOTE_ADDR .'\', lastaccess=\''. $timenow .'\'
1451                        WHERE id = \''. $userid .'\' ');
1452         if ($courseid != SITEID && !empty($courseid)) {
1453             if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++;};
1454
1455             if ($record = get_record('user_lastaccess', 'userid', $userid, 'courseid', $courseid)) {
1456                 $record->timeaccess = $timenow;
1457                 return update_record('user_lastaccess', $record);
1458             } else {
1459                 $record = new object;
1460                 $record->userid = $userid;
1461                 $record->courseid = $courseid;
1462                 $record->timeaccess = $timenow;
1463                 return insert_record('user_lastaccess', $record);
1464             }
1465         }
1466     }
1467 }
1468
1469
1470 /**
1471  * Select all log records based on SQL criteria
1472  *
1473  * @uses $CFG
1474  * @param string $select SQL select criteria
1475  * @param string $order SQL order by clause to sort the records returned
1476  * @param string $limitfrom ?
1477  * @param int $limitnum ?
1478  * @param int $totalcount Passed in by reference.
1479  * @return object
1480  * @todo Finish documenting this function
1481  */
1482 function get_logs($select, $order='l.time DESC', $limitfrom='', $limitnum='', &$totalcount) {
1483     global $CFG;
1484
1485     if ($order) {
1486         $order = 'ORDER BY '. $order;
1487     }
1488
1489     $selectsql = $CFG->prefix .'log l LEFT JOIN '. $CFG->prefix .'user u ON l.userid = u.id '. ((strlen($select) > 0) ? 'WHERE '. $select : '');
1490     $countsql = $CFG->prefix.'log l '.((strlen($select) > 0) ? ' WHERE '. $select : '');
1491
1492     $totalcount = count_records_sql("SELECT COUNT(*) FROM $countsql");
1493
1494     return get_records_sql('SELECT l.*, u.firstname, u.lastname, u.picture
1495                                 FROM '. $selectsql .' '. $order, $limitfrom, $limitnum) ;
1496 }
1497
1498
1499 /**
1500  * Select all log records for a given course and user
1501  *
1502  * @uses $CFG
1503  * @uses DAYSECS
1504  * @param int $userid The id of the user as found in the 'user' table.
1505  * @param int $courseid The id of the course as found in the 'course' table.
1506  * @param string $coursestart ?
1507  * @todo Finish documenting this function
1508  */
1509 function get_logs_usercourse($userid, $courseid, $coursestart) {
1510     global $CFG;
1511
1512     if ($courseid) {
1513         $courseselect = ' AND course = \''. $courseid .'\' ';
1514     } else {
1515         $courseselect = '';
1516     }
1517
1518     return get_records_sql("SELECT floor((time - $coursestart)/". DAYSECS .") as day, count(*) as num
1519                             FROM {$CFG->prefix}log
1520                            WHERE userid = '$userid'
1521                              AND time > '$coursestart' $courseselect
1522                         GROUP BY day ");
1523 }
1524
1525 /**
1526  * Select all log records for a given course, user, and day
1527  *
1528  * @uses $CFG
1529  * @uses HOURSECS
1530  * @param int $userid The id of the user as found in the 'user' table.
1531  * @param int $courseid The id of the course as found in the 'course' table.
1532  * @param string $daystart ?
1533  * @return object
1534  * @todo Finish documenting this function
1535  */
1536 function get_logs_userday($userid, $courseid, $daystart) {
1537     global $CFG;
1538
1539     if ($courseid) {
1540         $courseselect = ' AND course = \''. $courseid .'\' ';
1541     } else {
1542         $courseselect = '';
1543     }
1544
1545     return get_records_sql("SELECT floor((time - $daystart)/". HOURSECS .") as hour, count(*) as num
1546                             FROM {$CFG->prefix}log
1547                            WHERE userid = '$userid'
1548                              AND time > '$daystart' $courseselect
1549                         GROUP BY hour ");
1550 }
1551
1552 /**
1553  * Returns an object with counts of failed login attempts
1554  *
1555  * Returns information about failed login attempts.  If the current user is
1556  * an admin, then two numbers are returned:  the number of attempts and the
1557  * number of accounts.  For non-admins, only the attempts on the given user
1558  * are shown.
1559  *
1560  * @param string $mode Either 'admin', 'teacher' or 'everybody'
1561  * @param string $username The username we are searching for
1562  * @param string $lastlogin The date from which we are searching
1563  * @return int
1564  */
1565 function count_login_failures($mode, $username, $lastlogin) {
1566
1567     $select = 'module=\'login\' AND action=\'error\' AND time > '. $lastlogin;
1568
1569     if (has_capability('moodle/site:config', get_context_instance(CONTEXT_SYSTEM, SITEID))) {    // Return information about all accounts
1570         if ($count->attempts = count_records_select('log', $select)) {
1571             $count->accounts = count_records_select('log', $select, 'COUNT(DISTINCT info)');
1572             return $count;
1573         }
1574     } else if ($mode == 'everybody' or ($mode == 'teacher' and isteacherinanycourse())) {
1575         if ($count->attempts = count_records_select('log', $select .' AND info = \''. $username .'\'')) {
1576             return $count;
1577         }
1578     }
1579     return NULL;
1580 }
1581
1582
1583 /// GENERAL HELPFUL THINGS  ///////////////////////////////////
1584
1585 /**
1586  * Dump a given object's information in a PRE block.
1587  *
1588  * Mostly just used for debugging.
1589  *
1590  * @param mixed $object The data to be printed
1591  */
1592 function print_object($object) {
1593     echo '<pre class="notifytiny">' . htmlspecialchars(print_r($object,true)) . '</pre>';
1594 }
1595
1596 function course_parent_visible($course = null) {
1597     global $CFG;
1598
1599     if (empty($course)) {
1600         return true;
1601     }
1602     if (!empty($CFG->allowvisiblecoursesinhiddencategories)) {
1603         return true;
1604     }
1605     return category_parent_visible($course->category);
1606 }
1607
1608 function category_parent_visible($parent = 0) {
1609
1610     static $visible;
1611
1612     if (!$parent) {
1613         return true;
1614     }
1615
1616     if (empty($visible)) {
1617         $visible = array(); // initialize
1618     }
1619
1620     if (array_key_exists($parent,$visible)) {
1621         return $visible[$parent];
1622     }
1623
1624     $category = get_record('course_categories', 'id', $parent);
1625     $list = explode('/', preg_replace('/^\/(.*)$/', '$1', $category->path));
1626     $list[] = $parent;
1627     $parents = get_records_list('course_categories', 'id', implode(',', $list), 'depth DESC');
1628     $v = true;
1629     foreach ($parents as $p) {
1630         if (!$p->visible) {
1631             $v = false;
1632         }
1633     }
1634     $visible[$parent] = $v; // now cache it
1635     return $v;
1636 }
1637
1638 /**
1639  * This function is the official hook inside XMLDB stuff to delegate its debug to one
1640  * external function.
1641  *
1642  * Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before
1643  * using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-)
1644  *
1645  * @param $message string contains the error message
1646  * @param $object object XMLDB object that fired the debug
1647  */
1648 function xmldb_debug($message, $object) {
1649
1650     debugging($message, DEBUG_DEVELOPER);
1651 }
1652
1653 /**
1654  * Get the lists of courses the current user has $cap capability in
1655  * I am not sure if this is needed, it loops through all courses so
1656  * could cause performance problems.
1657  * If it's not used, we can use a faster function to detect
1658  * capability in restorelib.php
1659  * @param string $cap
1660  * @return array
1661  */
1662 function get_capability_courses($cap) {
1663     global $USER;
1664
1665     $mycourses = array();
1666     if ($courses = get_records('course')) {
1667         foreach ($courses as $course) {
1668             if (has_capability($cap, get_context_instance(CONTEXT_COURSE, $course->id))) {
1669                 $mycourses[] = $course->id;
1670             }
1671         }
1672     }
1673
1674     return $mycourses;
1675 }
1676
1677 /**
1678  * true or false function to see if user can create any courses at all
1679  * @return bool
1680  */
1681 function user_can_create_courses() {
1682     global $USER;
1683     // if user has course creation capability at any site or course cat, then return true;
1684
1685     if (has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
1686         return true;
1687     } else {
1688         return (bool) count(get_creatable_categories());
1689     }
1690
1691 }
1692
1693 /**
1694  * get the list of categories the current user can create courses in
1695  * @return array
1696  */
1697 function get_creatable_categories() {
1698
1699     $creatablecats = array();
1700     if ($cats = get_records('course_categories')) {
1701         foreach ($cats as $cat) {
1702             if (has_capability('moodle/course:create', get_context_instance(CONTEXT_COURSECAT, $cat->id))) {
1703                 $creatablecats[$cat->id] = $cat->name;
1704             }
1705         }
1706     }
1707     return $creatablecats;
1708 }
1709
1710 // vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140:
1711 ?>