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             // UPDATE course SET sortorder=sortorder+$shift
 964             execute_sql("UPDATE {$CFG->prefix}course
 965                          SET sortorder=sortorder+$shift
 966                          WHERE category=$categoryid", 0);
 967             $n = $n + $catgap + $count;
 968
 969         } else { // do it slowly
 970             $n = $n + $catgap;
 971             // if the new sequence overlaps the current sequence, lack of transactions
 972             // will stop us -- shift things aside for a moment...
 973             if ($safe || ($n >= $min && $n+$count+1 < $min && $CFG->dbfamily==='mysql')) {
 974                 $shift = $max + $n + 1000;
 975                 execute_sql("UPDATE {$CFG->prefix}course
 976                          SET sortorder=sortorder+$shift
 977                          WHERE category=$categoryid", 0);
 978             }
 979
 980             $courses = get_courses($categoryid, 'c.sortorder ASC', 'c.id,c.sortorder');
 981             begin_sql();
 982             foreach ($courses as $course) {
 983                 if ($course->sortorder != $n ) { // save db traffic
 984                     set_field('course', 'sortorder', $n, 'id', $course->id);
 985                 }
 986                 $n++;
 987             }
 988             commit_sql();
 989         }
 990     }
 991     set_field('course_categories', 'coursecount', $count, 'id', $categoryid);
 992
 993     // $n could need updating
 994     $max = get_field_sql("SELECT MAX(sortorder) from {$CFG->prefix}course WHERE category=$categoryid");
 995     if ($max > $n) {
 996         $n = $max;
 997     }
 998
 999     if ($categories = get_categories($categoryid)) {
1000         foreach ($categories as $category) {
1001             $n = fix_course_sortorder($category->id, $n, $safe, $depth, $path);
1002         }
1003     }
1004
1005     return $n+1;
1006 }
1007
1008 /**
1009  * List of remote courses that a user has access to via MNET.
1010  * Works only on the IDP
1011  *
1012  * @uses $CFG, $USER
1013  * @return array {@link $COURSE} of course objects
1014  */
1015 function get_my_remotecourses($userid=0) {
1016     global $CFG, $USER;
1017
1018     if (empty($userid)) {
1019         $userid = $USER->id;
1020     }
1021
1022     $sql = "SELECT c.remoteid, c.shortname, c.fullname,
1023                    c.hostid, c.summary, c.cat_name,
1024                    h.name AS hostname
1025             FROM   {$CFG->prefix}mnet_enrol_course c
1026             JOIN   {$CFG->prefix}mnet_enrol_assignments a ON c.id=a.courseid
1027             JOIN   {$CFG->prefix}mnet_host h        ON c.hostid=h.id
1028             WHERE  a.userid={$userid}";
1029
1030     return get_records_sql($sql);
1031 }
1032
1033 /**
1034  * List of remote hosts that a user has access to via MNET.
1035  * Works on the SP
1036  *
1037  * @uses $CFG, $USER
1038  * @return array of host objects
1039  */
1040 function get_my_remotehosts() {
1041     global $CFG, $USER;
1042
1043     if ($USER->mnethostid == $CFG->mnet_localhost_id) {
1044         return false; // Return nothing on the IDP
1045     }
1046     if (!empty($USER->mnet_foreign_host_array) && is_array($USER->mnet_foreign_host_array)) {
1047         return $USER->mnet_foreign_host_array;
1048     }
1049     return false;
1050 }
1051
1052 /**
1053  * This function creates a default separated/connected scale
1054  *
1055  * This function creates a default separated/connected scale
1056  * so there's something in the database.  The locations of
1057  * strings and files is a bit odd, but this is because we
1058  * need to maintain backward compatibility with many different
1059  * existing language translations and older sites.
1060  *
1061  * @uses $CFG
1062  */
1063 function make_default_scale() {
1064
1065     global $CFG;
1066
1067     $defaultscale = NULL;
1068     $defaultscale->courseid = 0;
1069     $defaultscale->userid = 0;
1070     $defaultscale->name  = get_string('separateandconnected');
1071     $defaultscale->scale = get_string('postrating1', 'forum').','.
1072                            get_string('postrating2', 'forum').','.
1073                            get_string('postrating3', 'forum');
1074     $defaultscale->timemodified = time();
1075
1076     /// Read in the big description from the file.  Note this is not
1077     /// HTML (despite the file extension) but Moodle format text.
1078     $parentlang = get_string('parentlang');
1079     if (is_readable($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
1080         $file = file($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
1081     } else if (is_readable($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) {
1082         $file = file($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html');
1083     } else if ($parentlang and is_readable($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
1084         $file = file($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
1085     } else if ($parentlang and is_readable($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) {
1086         $file = file($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html');
1087     } else if (is_readable($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html')) {
1088         $file = file($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html');
1089     } else {
1090         $file = '';
1091     }
1092
1093     $defaultscale->description = addslashes(implode('', $file));
1094
1095     if ($defaultscale->id = insert_record('scale', $defaultscale)) {
1096         execute_sql('UPDATE '. $CFG->prefix .'forum SET scale = \''. $defaultscale->id .'\'', false);
1097     }
1098 }
1099
1100
1101 /**
1102  * Returns a menu of all available scales from the site as well as the given course
1103  *
1104  * @uses $CFG
1105  * @param int $courseid The id of the course as found in the 'course' table.
1106  * @return object
1107  */
1108 function get_scales_menu($courseid=0) {
1109
1110     global $CFG;
1111
1112     $sql = "SELECT id, name FROM {$CFG->prefix}scale
1113              WHERE courseid = '0' or courseid = '$courseid'
1114           ORDER BY courseid ASC, name ASC";
1115
1116     if ($scales = get_records_sql_menu($sql)) {
1117         return $scales;
1118     }
1119
1120     make_default_scale();
1121
1122     return get_records_sql_menu($sql);
1123 }
1124
1125
1126
1127 /**
1128  * Given a set of timezone records, put them in the database,  replacing what is there
1129  *
1130  * @uses $CFG
1131  * @param array $timezones An array of timezone records
1132  */
1133 function update_timezone_records($timezones) {
1134 /// Given a set of timezone records, put them in the database
1135
1136     global $CFG;
1137
1138 /// Clear out all the old stuff
1139     execute_sql('TRUNCATE TABLE '.$CFG->prefix.'timezone', false);
1140
1141 /// Insert all the new stuff
1142     foreach ($timezones as $timezone) {
1143         insert_record('timezone', $timezone);
1144     }
1145 }
1146
1147
1148 /// MODULE FUNCTIONS /////////////////////////////////////////////////
1149
1150 /**
1151  * Just gets a raw list of all modules in a course
1152  *
1153  * @uses $CFG
1154  * @param int $courseid The id of the course as found in the 'course' table.
1155  * @return object
1156  */
1157 function get_course_mods($courseid) {
1158     global $CFG;
1159
1160     if (empty($courseid)) {
1161         return false; // avoid warnings
1162     }
1163
1164     return get_records_sql("SELECT cm.*, m.name as modname
1165                             FROM {$CFG->prefix}modules m,
1166                                  {$CFG->prefix}course_modules cm
1167                             WHERE cm.course = '$courseid'
1168                             AND cm.module = m.id ");
1169 }
1170
1171
1172 /**
1173  * Given an id of a course module, finds the coursemodule description
1174  *
1175  * @param string $modulename name of module type, eg. resource, assignment,...
1176  * @param int $cmid course module id (id in course_modules table)
1177  * @param int $courseid optional course id for extra validation
1178  * @return object course module instance with instance and module name
1179  */
1180 function get_coursemodule_from_id($modulename, $cmid, $courseid=0) {
1181
1182     global $CFG;
1183
1184     $courseselect = ($courseid) ? "cm.course = '$courseid' AND " : '';
1185
1186     return get_record_sql("SELECT cm.*, m.name, md.name as modname
1187                            FROM {$CFG->prefix}course_modules cm,
1188                                 {$CFG->prefix}modules md,
1189                                 {$CFG->prefix}$modulename m
1190                            WHERE $courseselect
1191                                  cm.id = '$cmid' AND
1192                                  cm.instance = m.id AND
1193                                  md.name = '$modulename' AND
1194                                  md.id = cm.module");
1195 }
1196
1197 /**
1198  * Given an instance number of a module, finds the coursemodule description
1199  *
1200  * @param string $modulename name of module type, eg. resource, assignment,...
1201  * @param int $instance module instance number (id in resource, assignment etc. table)
1202  * @param int $courseid optional course id for extra validation
1203  * @return object course module instance with instance and module name
1204  */
1205 function get_coursemodule_from_instance($modulename, $instance, $courseid=0) {
1206
1207     global $CFG;
1208
1209     $courseselect = ($courseid) ? "cm.course = '$courseid' AND " : '';
1210
1211     return get_record_sql("SELECT cm.*, m.name, md.name as modname
1212                            FROM {$CFG->prefix}course_modules cm,
1213                                 {$CFG->prefix}modules md,
1214                                 {$CFG->prefix}$modulename m
1215                            WHERE $courseselect
1216                                  cm.instance = m.id AND
1217                                  md.name = '$modulename' AND
1218                                  md.id = cm.module AND
1219                                  m.id = '$instance'");
1220
1221 }
1222
1223 /**
1224  * Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined
1225  *
1226  * Returns an array of all the active instances of a particular
1227  * module in given courses, sorted in the order they are defined
1228  * in the course.   Returns false on any errors.
1229  *
1230  * @uses $CFG
1231  * @param string  $modulename The name of the module to get instances for
1232  * @param array  $courses This depends on an accurate $course->modinfo
1233  * @return array of instances
1234  */
1235 function get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false) {
1236     global $CFG;
1237     if (empty($courses) || !is_array($courses) || count($courses) == 0) {
1238         return array();
1239     }
1240     if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode, cm.course
1241                             FROM {$CFG->prefix}course_modules cm,
1242                                  {$CFG->prefix}course_sections cw,
1243                                  {$CFG->prefix}modules md,
1244                                  {$CFG->prefix}$modulename m
1245                             WHERE cm.course IN (".implode(',',array_keys($courses)).") AND
1246                                   cm.instance = m.id AND
1247                                   cm.section = cw.id AND
1248                                   md.name = '$modulename' AND
1249                                   md.id = cm.module")) {
1250         return array();
1251     }
1252
1253     $outputarray = array();
1254
1255     foreach ($courses as $course) {
1256         if ($includeinvisible) {
1257             $invisible = -1;
1258         } else if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id), $userid)) {
1259             // Usually hide non-visible instances from students
1260             $invisible = -1;
1261         } else {
1262             $invisible = 0;
1263         }
1264
1265    /// Casting $course->modinfo to string prevents one notice when the field is null
1266         if (!$modinfo = unserialize((string)$course->modinfo)) {
1267             continue;
1268         }
1269         foreach ($modinfo as $mod) {
1270             if ($mod->mod == $modulename and $mod->visible > $invisible) {
1271                 $instance = $rawmods[$mod->cm];
1272                 if (!empty($mod->extra)) {
1273                     $instance->extra = $mod->extra;
1274                 }
1275                 $outputarray[] = $instance;
1276             }
1277         }
1278     }
1279
1280     return $outputarray;
1281
1282 }
1283
1284 /**
1285  * Returns an array of all the active instances of a particular module in a given course, sorted in the order they are defined
1286  *
1287  * Returns an array of all the active instances of a particular
1288  * module in a given course, sorted in the order they are defined
1289  * in the course.   Returns false on any errors.
1290  *
1291  * @uses $CFG
1292  * @param string  $modulename The name of the module to get instances for
1293  * @param object(course)  $course This depends on an accurate $course->modinfo
1294  */
1295 function get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false) {
1296
1297     global $CFG;
1298
1299     if (empty($course->modinfo)) {
1300         return array();
1301     }
1302
1303     if (!$modinfo = unserialize((string)$course->modinfo)) {
1304         return array();
1305     }
1306
1307     if (!$rawmods = get_records_sql("SELECT cm.id as coursemodule, m.*,cw.section,cm.visible as visible,cm.groupmode,cm.groupingid
1308                             FROM {$CFG->prefix}course_modules cm,
1309                                  {$CFG->prefix}course_sections cw,
1310                                  {$CFG->prefix}modules md,
1311                                  {$CFG->prefix}$modulename m
1312                             WHERE cm.course = '$course->id' AND
1313                                   cm.instance = m.id AND
1314                                   cm.section = cw.id AND
1315                                   md.name = '$modulename' AND
1316                                   md.id = cm.module")) {
1317         return array();
1318     }
1319
1320     if ($includeinvisible) {
1321         $invisible = -1;
1322     } else if (has_capability('moodle/course:viewhiddencourses', get_context_instance(CONTEXT_COURSE, $course->id), $userid)) {
1323         // Usually hide non-visible instances from students
1324         $invisible = -1;
1325     } else {
1326         $invisible = 0;
1327     }
1328
1329     $outputarray = array();
1330
1331     foreach ($modinfo as $mod) {
1332         $mod->id = $mod->cm;
1333         $mod->course = $course->id;
1334         if (!groups_course_module_visible($mod)) {
1335             continue;
1336         }
1337         if ($mod->mod == $modulename and $mod->visible > $invisible) {
1338             $instance = $rawmods[$mod->cm];
1339             if (!empty($mod->extra)) {
1340                 $instance->extra = $mod->extra;
1341             }
1342             $outputarray[] = $instance;
1343         }
1344     }
1345
1346     return $outputarray;
1347
1348 }
1349
1350
1351 /**
1352  * Determine whether a module instance is visible within a course
1353  *
1354  * Given a valid module object with info about the id and course,
1355  * and the module's type (eg "forum") returns whether the object
1356  * is visible or not
1357  *
1358  * @uses $CFG
1359  * @param $moduletype Name of the module eg 'forum'
1360  * @param $module Object which is the instance of the module
1361  * @return bool
1362  */
1363 function instance_is_visible($moduletype, $module) {
1364
1365     global $CFG;
1366
1367     if (!empty($module->id)) {
1368         if ($records = get_records_sql("SELECT cm.instance, cm.visible, cm.groupingid, cm.id, cm.groupmembersonly, cm.course
1369                                         FROM {$CFG->prefix}course_modules cm,
1370                                              {$CFG->prefix}modules m
1371                                        WHERE cm.course = '$module->course' AND
1372                                              cm.module = m.id AND
1373                                              m.name = '$moduletype' AND
1374                                              cm.instance = '$module->id'")) {
1375
1376             foreach ($records as $record) { // there should only be one - use the first one
1377                 return $record->visible && groups_course_module_visible($record);
1378             }
1379         }
1380     }
1381     return true;  // visible by default!
1382 }
1383
1384
1385
1386
1387 /// LOG FUNCTIONS /////////////////////////////////////////////////////
1388
1389
1390 /**
1391  * Add an entry to the log table.
1392  *
1393  * Add an entry to the log table.  These are "action" focussed rather
1394  * than web server hits, and provide a way to easily reconstruct what
1395  * any particular student has been doing.
1396  *
1397  * @uses $CFG
1398  * @uses $USER
1399  * @uses $db
1400  * @uses $REMOTE_ADDR
1401  * @uses SITEID
1402  * @param    int     $courseid  The course id
1403  * @param    string  $module  The module name - e.g. forum, journal, resource, course, user etc
1404  * @param    string  $action  'view', 'update', 'add' or 'delete', possibly followed by another word to clarify.
1405  * @param    string  $url     The file and parameters used to see the results of the action
1406  * @param    string  $info    Additional description information
1407  * @param    string  $cm      The course_module->id if there is one
1408  * @param    string  $user    If log regards $user other than $USER
1409  */
1410 function add_to_log($courseid, $module, $action, $url='', $info='', $cm=0, $user=0) {
1411     // Note that this function intentionally does not follow the normal Moodle DB access idioms.
1412     // This is for a good reason: it is the most frequently used DB update function,
1413     // so it has been optimised for speed.
1414     global $db, $CFG, $USER;
1415
1416     if ($cm === '' || is_null($cm)) { // postgres won't translate empty string to its default
1417         $cm = 0;
1418     }
1419
1420     if ($user) {
1421         $userid = $user;
1422     } else {
1423         if (!empty($USER->realuser)) {  // Don't log
1424             return;
1425         }
1426         $userid = empty($USER->id) ? '0' : $USER->id;
1427     }
1428
1429     $REMOTE_ADDR = getremoteaddr();
1430
1431     $timenow = time();
1432     $info = addslashes($info);
1433     if (!empty($url)) { // could break doing html_entity_decode on an empty var.
1434         $url = html_entity_decode($url); // for php < 4.3.0 this is defined in moodlelib.php
1435     }
1436
1437     if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; $PERF->logwrites++;};
1438
1439     if ($CFG->type = 'oci8po') {
1440         if (empty($info)) {
1441             $info = ' ';
1442         }
1443     }
1444
1445     $result = $db->Execute('INSERT INTO '. $CFG->prefix .'log (time, userid, course, ip, module, cmid, action, url, info)
1446         VALUES (' . "'$timenow', '$userid', '$courseid', '$REMOTE_ADDR', '$module', '$cm', '$action', '$url', '$info')");
1447
1448     if (!$result and debugging()) {
1449         echo '<p>Error: Could not insert a new entry to the Moodle log</p>';  // Don't throw an error
1450     }
1451
1452 /// Store lastaccess times for the current user, do not use in cron and other commandline scripts
1453
1454     if (!empty($USER->id) && ($userid == $USER->id) && !defined('FULLME')) {
1455         $db->Execute('UPDATE '. $CFG->prefix .'user
1456                          SET lastip=\''. $REMOTE_ADDR .'\', lastaccess=\''. $timenow .'\'
1457                        WHERE id = \''. $userid .'\' ');
1458         if ($courseid != SITEID && !empty($courseid)) {
1459             if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++;};
1460
1461             if ($record = get_record('user_lastaccess', 'userid', $userid, 'courseid', $courseid)) {
1462                 $record->timeaccess = $timenow;
1463                 return update_record('user_lastaccess', $record);
1464             } else {
1465                 $record = new object;
1466                 $record->userid = $userid;
1467                 $record->courseid = $courseid;
1468                 $record->timeaccess = $timenow;
1469                 return insert_record('user_lastaccess', $record);
1470             }
1471         }
1472     }
1473 }
1474
1475
1476 /**
1477  * Select all log records based on SQL criteria
1478  *
1479  * @uses $CFG
1480  * @param string $select SQL select criteria
1481  * @param string $order SQL order by clause to sort the records returned
1482  * @param string $limitfrom ?
1483  * @param int $limitnum ?
1484  * @param int $totalcount Passed in by reference.
1485  * @return object
1486  * @todo Finish documenting this function
1487  */
1488 function get_logs($select, $order='l.time DESC', $limitfrom='', $limitnum='', &$totalcount) {
1489     global $CFG;
1490
1491     if ($order) {
1492         $order = 'ORDER BY '. $order;
1493     }
1494
1495     $selectsql = $CFG->prefix .'log l LEFT JOIN '. $CFG->prefix .'user u ON l.userid = u.id '. ((strlen($select) > 0) ? 'WHERE '. $select : '');
1496     $countsql = $CFG->prefix.'log l '.((strlen($select) > 0) ? ' WHERE '. $select : '');
1497
1498     $totalcount = count_records_sql("SELECT COUNT(*) FROM $countsql");
1499
1500     return get_records_sql('SELECT l.*, u.firstname, u.lastname, u.picture
1501                                 FROM '. $selectsql .' '. $order, $limitfrom, $limitnum) ;
1502 }
1503
1504
1505 /**
1506  * Select all log records for a given course and user
1507  *
1508  * @uses $CFG
1509  * @uses DAYSECS
1510  * @param int $userid The id of the user as found in the 'user' table.
1511  * @param int $courseid The id of the course as found in the 'course' table.
1512  * @param string $coursestart ?
1513  * @todo Finish documenting this function
1514  */
1515 function get_logs_usercourse($userid, $courseid, $coursestart) {
1516     global $CFG;
1517
1518     if ($courseid) {
1519         $courseselect = ' AND course = \''. $courseid .'\' ';
1520     } else {
1521         $courseselect = '';
1522     }
1523
1524     return get_records_sql("SELECT floor((time - $coursestart)/". DAYSECS .") as day, count(*) as num
1525                             FROM {$CFG->prefix}log
1526                            WHERE userid = '$userid'
1527                              AND time > '$coursestart' $courseselect
1528                         GROUP BY day ");
1529 }
1530
1531 /**
1532  * Select all log records for a given course, user, and day
1533  *
1534  * @uses $CFG
1535  * @uses HOURSECS
1536  * @param int $userid The id of the user as found in the 'user' table.
1537  * @param int $courseid The id of the course as found in the 'course' table.
1538  * @param string $daystart ?
1539  * @return object
1540  * @todo Finish documenting this function
1541  */
1542 function get_logs_userday($userid, $courseid, $daystart) {
1543     global $CFG;
1544
1545     if ($courseid) {
1546         $courseselect = ' AND course = \''. $courseid .'\' ';
1547     } else {
1548         $courseselect = '';
1549     }
1550
1551     return get_records_sql("SELECT floor((time - $daystart)/". HOURSECS .") as hour, count(*) as num
1552                             FROM {$CFG->prefix}log
1553                            WHERE userid = '$userid'
1554                              AND time > '$daystart' $courseselect
1555                         GROUP BY hour ");
1556 }
1557
1558 /**
1559  * Returns an object with counts of failed login attempts
1560  *
1561  * Returns information about failed login attempts.  If the current user is
1562  * an admin, then two numbers are returned:  the number of attempts and the
1563  * number of accounts.  For non-admins, only the attempts on the given user
1564  * are shown.
1565  *
1566  * @param string $mode Either 'admin', 'teacher' or 'everybody'
1567  * @param string $username The username we are searching for
1568  * @param string $lastlogin The date from which we are searching
1569  * @return int
1570  */
1571 function count_login_failures($mode, $username, $lastlogin) {
1572
1573     $select = 'module=\'login\' AND action=\'error\' AND time > '. $lastlogin;
1574
1575     if (has_capability('moodle/site:config', get_context_instance(CONTEXT_SYSTEM, SITEID))) {    // Return information about all accounts
1576         if ($count->attempts = count_records_select('log', $select)) {
1577             $count->accounts = count_records_select('log', $select, 'COUNT(DISTINCT info)');
1578             return $count;
1579         }
1580     } else if ($mode == 'everybody' or ($mode == 'teacher' and isteacherinanycourse())) {
1581         if ($count->attempts = count_records_select('log', $select .' AND info = \''. $username .'\'')) {
1582             return $count;
1583         }
1584     }
1585     return NULL;
1586 }
1587
1588
1589 /// GENERAL HELPFUL THINGS  ///////////////////////////////////
1590
1591 /**
1592  * Dump a given object's information in a PRE block.
1593  *
1594  * Mostly just used for debugging.
1595  *
1596  * @param mixed $object The data to be printed
1597  */
1598 function print_object($object) {
1599     echo '<pre class="notifytiny">' . htmlspecialchars(print_r($object,true)) . '</pre>';
1600 }
1601
1602 function course_parent_visible($course = null) {
1603     global $CFG;
1604
1605     if (empty($course)) {
1606         return true;
1607     }
1608     if (!empty($CFG->allowvisiblecoursesinhiddencategories)) {
1609         return true;
1610     }
1611     return category_parent_visible($course->category);
1612 }
1613
1614 function category_parent_visible($parent = 0) {
1615
1616     static $visible;
1617
1618     if (!$parent) {
1619         return true;
1620     }
1621
1622     if (empty($visible)) {
1623         $visible = array(); // initialize
1624     }
1625
1626     if (array_key_exists($parent,$visible)) {
1627         return $visible[$parent];
1628     }
1629
1630     $category = get_record('course_categories', 'id', $parent);
1631     $list = explode('/', preg_replace('/^\/(.*)$/', '$1', $category->path));
1632     $list[] = $parent;
1633     $parents = get_records_list('course_categories', 'id', implode(',', $list), 'depth DESC');
1634     $v = true;
1635     foreach ($parents as $p) {
1636         if (!$p->visible) {
1637             $v = false;
1638         }
1639     }
1640     $visible[$parent] = $v; // now cache it
1641     return $v;
1642 }
1643
1644 /**
1645  * This function is the official hook inside XMLDB stuff to delegate its debug to one
1646  * external function.
1647  *
1648  * Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before
1649  * using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-)
1650  *
1651  * @param $message string contains the error message
1652  * @param $object object XMLDB object that fired the debug
1653  */
1654 function xmldb_debug($message, $object) {
1655
1656     debugging($message, DEBUG_DEVELOPER);
1657 }
1658
1659 /**
1660  * Get the lists of courses the current user has $cap capability in
1661  * I am not sure if this is needed, it loops through all courses so
1662  * could cause performance problems.
1663  * If it's not used, we can use a faster function to detect
1664  * capability in restorelib.php
1665  * @param string $cap
1666  * @return array
1667  */
1668 function get_capability_courses($cap) {
1669     global $USER;
1670
1671     $mycourses = array();
1672     if ($courses = get_records('course')) {
1673         foreach ($courses as $course) {
1674             if (has_capability($cap, get_context_instance(CONTEXT_COURSE, $course->id))) {
1675                 $mycourses[] = $course->id;
1676             }
1677         }
1678     }
1679
1680     return $mycourses;
1681 }
1682
1683 /**
1684  * true or false function to see if user can create any courses at all
1685  * @return bool
1686  */
1687 function user_can_create_courses() {
1688     global $USER;
1689     // if user has course creation capability at any site or course cat, then return true;
1690
1691     if (has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM, SITEID))) {
1692         return true;
1693     } else {
1694         return (bool) count(get_creatable_categories());
1695     }
1696
1697 }
1698
1699 /**
1700  * get the list of categories the current user can create courses in
1701  * @return array
1702  */
1703 function get_creatable_categories() {
1704
1705     $creatablecats = array();
1706     if ($cats = get_records('course_categories')) {
1707         foreach ($cats as $cat) {
1708             if (has_capability('moodle/course:create', get_context_instance(CONTEXT_COURSECAT, $cat->id))) {
1709                 $creatablecats[$cat->id] = $cat->name;
1710             }
1711         }
1712     }
1713     return $creatablecats;
1714 }
1715
1716 // vim:autoindent:expandtab:shiftwidth=4:tabstop=4:tw=140:
1717 ?>