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