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