| [ Index ] |
PHP Cross Reference of Moodle 1.9.3 [Build 15-Oct-2008] |
[Summary view] [Print] [Text view]
1 <?php // $Id$ 2 /** 3 * Library of functions for database manipulation. 4 * 5 * Other main libraries: 6 * - weblib.php - functions that produce web output 7 * - moodlelib.php - general-purpose Moodle functions 8 * @author Martin Dougiamas and many others 9 * @license http://www.gnu.org/copyleft/gpl.html GNU Public License 10 * @package moodlecore 11 */ 12 13 /// Some constants 14 define('LASTACCESS_UPDATE_SECS', 60); /// Number of seconds to wait before 15 /// updating lastaccess information in DB. 16 17 /** 18 * Escape all dangerous characters in a data record 19 * 20 * $dataobject is an object containing needed data 21 * Run over each field exectuting addslashes() function 22 * to escape SQL unfriendly characters (e.g. quotes) 23 * Handy when writing back data read from the database 24 * 25 * @param $dataobject Object containing the database record 26 * @return object Same object with neccessary characters escaped 27 */ 28 function addslashes_object( $dataobject ) { 29 $a = get_object_vars( $dataobject); 30 foreach ($a as $key=>$value) { 31 $a[$key] = addslashes( $value ); 32 } 33 return (object)$a; 34 } 35 36 /// USER DATABASE //////////////////////////////////////////////// 37 38 /** 39 * Returns $user object of the main admin user 40 * primary admin = admin with lowest role_assignment id among admins 41 * @uses $CFG 42 * @return object(admin) An associative array representing the admin user. 43 */ 44 function get_admin () { 45 46 global $CFG; 47 static $myadmin; 48 49 if (isset($myadmin)) { 50 return $myadmin; 51 } 52 53 if ( $admins = get_admins() ) { 54 foreach ($admins as $admin) { 55 $myadmin = $admin; 56 return $admin; // ie the first one 57 } 58 } else { 59 return false; 60 } 61 } 62 63 /** 64 * Returns list of all admins, using 1 DB query. It depends on DB schema v1.7 65 * but does not depend on the v1.9 datastructures (context.path, etc). 66 * 67 * @uses $CFG 68 * @return object 69 */ 70 function get_admins() { 71 72 global $CFG; 73 74 $sql = "SELECT ra.userid, SUM(rc.permission) AS permission, MIN(ra.id) AS adminid 75 FROM " . $CFG->prefix . "role_capabilities rc 76 JOIN " . $CFG->prefix . "context ctx 77 ON ctx.id=rc.contextid 78 JOIN " . $CFG->prefix . "role_assignments ra 79 ON ra.roleid=rc.roleid AND ra.contextid=ctx.id 80 WHERE ctx.contextlevel=10 81 AND rc.capability IN ('moodle/site:config', 82 'moodle/legacy:admin', 83 'moodle/site:doanything') 84 GROUP BY ra.userid 85 HAVING SUM(rc.permission) > 0"; 86 87 $sql = "SELECT u.*, ra.adminid 88 FROM " . $CFG->prefix . "user u 89 JOIN ($sql) ra 90 ON u.id=ra.userid 91 ORDER BY ra.adminid ASC"; 92 93 return get_records_sql($sql); 94 } 95 96 97 function get_courses_in_metacourse($metacourseid) { 98 global $CFG; 99 100 $sql = "SELECT c.id,c.shortname,c.fullname FROM {$CFG->prefix}course c, {$CFG->prefix}course_meta mc WHERE mc.parent_course = $metacourseid 101 AND mc.child_course = c.id ORDER BY c.shortname"; 102 103 return get_records_sql($sql); 104 } 105 106 function get_courses_notin_metacourse($metacourseid,$count=false) { 107 108 global $CFG; 109 110 if ($count) { 111 $sql = "SELECT COUNT(c.id)"; 112 } else { 113 $sql = "SELECT c.id,c.shortname,c.fullname"; 114 } 115 116 $alreadycourses = get_courses_in_metacourse($metacourseid); 117 118 $sql .= " FROM {$CFG->prefix}course c WHERE ".((!empty($alreadycourses)) ? "c.id NOT IN (".implode(',',array_keys($alreadycourses)).") 119 AND " : "")." c.id !=$metacourseid and c.id != ".SITEID." and c.metacourse != 1 ".((empty($count)) ? " ORDER BY c.shortname" : ""); 120 121 return get_records_sql($sql); 122 } 123 124 function count_courses_notin_metacourse($metacourseid) { 125 global $CFG; 126 127 $alreadycourses = get_courses_in_metacourse($metacourseid); 128 129 $sql = "SELECT COUNT(c.id) AS notin FROM {$CFG->prefix}course c 130 WHERE ".((!empty($alreadycourses)) ? "c.id NOT IN (".implode(',',array_keys($alreadycourses)).") 131 AND " : "")." c.id !=$metacourseid and c.id != ".SITEID." and c.metacourse != 1"; 132 133 if (!$count = get_record_sql($sql)) { 134 return 0; 135 } 136 137 return $count->notin; 138 } 139 140 /** 141 * Search through course users 142 * 143 * If $coursid specifies the site course then this function searches 144 * through all undeleted and confirmed users 145 * 146 * @uses $CFG 147 * @uses SITEID 148 * @param int $courseid The course in question. 149 * @param int $groupid The group in question. 150 * @param string $searchtext ? 151 * @param string $sort ? 152 * @param string $exceptions ? 153 * @return object 154 */ 155 function search_users($courseid, $groupid, $searchtext, $sort='', $exceptions='') { 156 global $CFG; 157 158 $LIKE = sql_ilike(); 159 $fullname = sql_fullname('u.firstname', 'u.lastname'); 160 161 if (!empty($exceptions)) { 162 $except = ' AND u.id NOT IN ('. $exceptions .') '; 163 } else { 164 $except = ''; 165 } 166 167 if (!empty($sort)) { 168 $order = ' ORDER BY '. $sort; 169 } else { 170 $order = ''; 171 } 172 173 $select = 'u.deleted = \'0\' AND u.confirmed = \'1\''; 174 175 if (!$courseid or $courseid == SITEID) { 176 return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email 177 FROM {$CFG->prefix}user u 178 WHERE $select 179 AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%') 180 $except $order"); 181 } else { 182 183 if ($groupid) { 184 //TODO:check. Remove group DB dependencies. 185 return get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email 186 FROM {$CFG->prefix}user u, 187 {$CFG->prefix}groups_members gm 188 WHERE $select AND gm.groupid = '$groupid' AND gm.userid = u.id 189 AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%') 190 $except $order"); 191 } else { 192 $context = get_context_instance(CONTEXT_COURSE, $courseid); 193 $contextlists = get_related_contexts_string($context); 194 $users = get_records_sql("SELECT u.id, u.firstname, u.lastname, u.email 195 FROM {$CFG->prefix}user u, 196 {$CFG->prefix}role_assignments ra 197 WHERE $select AND ra.contextid $contextlists AND ra.userid = u.id 198 AND ($fullname $LIKE '%$searchtext%' OR u.email $LIKE '%$searchtext%') 199 $except $order"); 200 } 201 return $users; 202 } 203 } 204 205 206 /** 207 * Returns a list of all site users 208 * Obsolete, just calls get_course_users(SITEID) 209 * 210 * @uses SITEID 211 * @deprecated Use {@link get_course_users()} instead. 212 * @param string $fields A comma separated list of fields to be returned from the chosen table. 213 * @return object|false {@link $USER} records or false if error. 214 */ 215 function get_site_users($sort='u.lastaccess DESC', $fields='*', $exceptions='') { 216 217 return get_course_users(SITEID, $sort, $exceptions, $fields); 218 } 219 220 221 /** 222 * Returns a subset of users 223 * 224 * @uses $CFG 225 * @param bool $get If false then only a count of the records is returned 226 * @param string $search A simple string to search for 227 * @param bool $confirmed A switch to allow/disallow unconfirmed users 228 * @param array(int) $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10 229 * @param string $sort A SQL snippet for the sorting criteria to use 230 * @param string $firstinitial ? 231 * @param string $lastinitial ? 232 * @param string $page ? 233 * @param string $recordsperpage ? 234 * @param string $fields A comma separated list of fields to be returned from the chosen table. 235 * @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. 236 */ 237 function get_users($get=true, $search='', $confirmed=false, $exceptions='', $sort='firstname ASC', 238 $firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*', $extraselect='') { 239 240 global $CFG; 241 242 if ($get && !$recordsperpage) { 243 debugging('Call to get_users with $get = true no $recordsperpage limit. ' . 244 'On large installations, this will probably cause an out of memory error. ' . 245 'Please think again and change your code so that it does not try to ' . 246 'load so much data into memory.', DEBUG_DEVELOPER); 247 } 248 249 $LIKE = sql_ilike(); 250 $fullname = sql_fullname(); 251 252 $select = 'username <> \'guest\' AND deleted = 0'; 253 254 if (!empty($search)){ 255 $search = trim($search); 256 $select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%') "; 257 } 258 259 if ($confirmed) { 260 $select .= ' AND confirmed = \'1\' '; 261 } 262 263 if ($exceptions) { 264 $select .= ' AND id NOT IN ('. $exceptions .') '; 265 } 266 267 if ($firstinitial) { 268 $select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\''; 269 } 270 if ($lastinitial) { 271 $select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\''; 272 } 273 274 if ($extraselect) { 275 $select .= " AND $extraselect "; 276 } 277 278 if ($get) { 279 return get_records_select('user', $select, $sort, $fields, $page, $recordsperpage); 280 } else { 281 return count_records_select('user', $select); 282 } 283 } 284 285 286 /** 287 * shortdesc (optional) 288 * 289 * longdesc 290 * 291 * @uses $CFG 292 * @param string $sort ? 293 * @param string $dir ? 294 * @param int $categoryid ? 295 * @param int $categoryid ? 296 * @param string $search ? 297 * @param string $firstinitial ? 298 * @param string $lastinitial ? 299 * @returnobject {@link $USER} records 300 * @todo Finish documenting this function 301 */ 302 303 function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=0, 304 $search='', $firstinitial='', $lastinitial='', $extraselect='') { 305 306 global $CFG; 307 308 $LIKE = sql_ilike(); 309 $fullname = sql_fullname(); 310 311 $select = "deleted <> '1'"; 312 313 if (!empty($search)) { 314 $search = trim($search); 315 $select .= " AND ($fullname $LIKE '%$search%' OR email $LIKE '%$search%' OR username='$search') "; 316 } 317 318 if ($firstinitial) { 319 $select .= ' AND firstname '. $LIKE .' \''. $firstinitial .'%\' '; 320 } 321 322 if ($lastinitial) { 323 $select .= ' AND lastname '. $LIKE .' \''. $lastinitial .'%\' '; 324 } 325 326 if ($extraselect) { 327 $select .= " AND $extraselect "; 328 } 329 330 if ($sort) { 331 $sort = ' ORDER BY '. $sort .' '. $dir; 332 } 333 334 /// warning: will return UNCONFIRMED USERS 335 return get_records_sql("SELECT id, username, email, firstname, lastname, city, country, lastaccess, confirmed, mnethostid 336 FROM {$CFG->prefix}user 337 WHERE $select $sort", $page, $recordsperpage); 338 339 } 340 341 342 /** 343 * Full list of users that have confirmed their accounts. 344 * 345 * @uses $CFG 346 * @return object 347 */ 348 function get_users_confirmed() { 349 global $CFG; 350 return get_records_sql("SELECT * 351 FROM {$CFG->prefix}user 352 WHERE confirmed = 1 353 AND deleted = 0 354 AND username <> 'guest'"); 355 } 356 357 358 /// OTHER SITE AND COURSE FUNCTIONS ///////////////////////////////////////////// 359 360 361 /** 362 * Returns $course object of the top-level site. 363 * 364 * @return course A {@link $COURSE} object for the site 365 */ 366 function get_site() { 367 368 global $SITE; 369 370 if (!empty($SITE->id)) { // We already have a global to use, so return that 371 return $SITE; 372 } 373 374 if ($course = get_record('course', 'category', 0)) { 375 return $course; 376 } else { 377 return false; 378 } 379 } 380 381 /** 382 * Returns list of courses, for whole site, or category 383 * 384 * Returns list of courses, for whole site, or category 385 * Important: Using c.* for fields is extremely expensive because 386 * we are using distinct. You almost _NEVER_ need all the fields 387 * in such a large SELECT 388 * 389 * @param type description 390 * 391 */ 392 function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") { 393 394 global $USER, $CFG; 395 396 if ($categoryid != "all" && is_numeric($categoryid)) { 397 $categoryselect = "WHERE c.category = '$categoryid'"; 398 } else { 399 $categoryselect = ""; 400 } 401 402 if (empty($sort)) { 403 $sortstatement = ""; 404 } else { 405 $sortstatement = "ORDER BY $sort"; 406 } 407 408 $visiblecourses = array(); 409 410 // pull out all course matching the cat 411 if ($courses = get_records_sql("SELECT $fields, 412 ctx.id AS ctxid, ctx.path AS ctxpath, 413 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel 414 FROM {$CFG->prefix}course c 415 JOIN {$CFG->prefix}context ctx 416 ON (c.id = ctx.instanceid 417 AND ctx.contextlevel=".CONTEXT_COURSE.") 418 $categoryselect 419 $sortstatement")) { 420 421 // loop throught them 422 foreach ($courses as $course) { 423 $course = make_context_subobj($course); 424 if (isset($course->visible) && $course->visible <= 0) { 425 // for hidden courses, require visibility check 426 if (has_capability('moodle/course:viewhiddencourses', $course->context)) { 427 $visiblecourses [] = $course; 428 } 429 } else { 430 $visiblecourses [] = $course; 431 } 432 } 433 } 434 return $visiblecourses; 435 436 /* 437 $teachertable = ""; 438 $visiblecourses = ""; 439 $sqland = ""; 440 if (!empty($categoryselect)) { 441 $sqland = "AND "; 442 } 443 if (!empty($USER->id)) { // May need to check they are a teacher 444 if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM))) { 445 $visiblecourses = "$sqland ((c.visible > 0) OR t.userid = '$USER->id')"; 446 $teachertable = "LEFT JOIN {$CFG->prefix}user_teachers t ON t.course = c.id"; 447 } 448 } else { 449 $visiblecourses = "$sqland c.visible > 0"; 450 } 451 452 if ($categoryselect or $visiblecourses) { 453 $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses"; 454 } else { 455 $selectsql = "{$CFG->prefix}course c $teachertable"; 456 } 457 458 $extrafield = str_replace('ASC','',$sort); 459 $extrafield = str_replace('DESC','',$extrafield); 460 $extrafield = trim($extrafield); 461 if (!empty($extrafield)) { 462 $extrafield = ','.$extrafield; 463 } 464 return get_records_sql("SELECT ".((!empty($teachertable)) ? " DISTINCT " : "")." $fields $extrafield FROM $selectsql ".((!empty($sort)) ? "ORDER BY $sort" : "")); 465 */ 466 } 467 468 469 /** 470 * Returns list of courses, for whole site, or category 471 * 472 * Similar to get_courses, but allows paging 473 * Important: Using c.* for fields is extremely expensive because 474 * we are using distinct. You almost _NEVER_ need all the fields 475 * in such a large SELECT 476 * 477 * @param type description 478 * 479 */ 480 function get_courses_page($categoryid="all", $sort="c.sortorder ASC", $fields="c.*", 481 &$totalcount, $limitfrom="", $limitnum="") { 482 483 global $USER, $CFG; 484 485 $categoryselect = ""; 486 if ($categoryid != "all" && is_numeric($categoryid)) { 487 $categoryselect = "WHERE c.category = '$categoryid'"; 488 } else { 489 $categoryselect = ""; 490 } 491 492 // pull out all course matching the cat 493 $visiblecourses = array(); 494 if (!($rs = get_recordset_sql("SELECT $fields, 495 ctx.id AS ctxid, ctx.path AS ctxpath, 496 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel 497 FROM {$CFG->prefix}course c 498 JOIN {$CFG->prefix}context ctx 499 ON (c.id = ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.") 500 $categoryselect 501 ORDER BY $sort"))) { 502 return $visiblecourses; 503 } 504 $totalcount = 0; 505 506 if (!$limitfrom) { 507 $limitfrom = 0; 508 } 509 510 // iteration will have to be done inside loop to keep track of the limitfrom and limitnum 511 while ($course = rs_fetch_next_record($rs)) { 512 $course = make_context_subobj($course); 513 if ($course->visible <= 0) { 514 // for hidden courses, require visibility check 515 if (has_capability('moodle/course:viewhiddencourses', $course->context)) { 516 $totalcount++; 517 if ($totalcount > $limitfrom && (!$limitnum or count($visiblecourses) < $limitnum)) { 518 $visiblecourses [] = $course; 519 } 520 } 521 } else { 522 $totalcount++; 523 if ($totalcount > $limitfrom && (!$limitnum or count($visiblecourses) < $limitnum)) { 524 $visiblecourses [] = $course; 525 } 526 } 527 } 528 rs_close($rs); 529 return $visiblecourses; 530 531 /** 532 533 $categoryselect = ""; 534 if ($categoryid != "all" && is_numeric($categoryid)) { 535 $categoryselect = "c.category = '$categoryid'"; 536 } 537 538 $teachertable = ""; 539 $visiblecourses = ""; 540 $sqland = ""; 541 if (!empty($categoryselect)) { 542 $sqland = "AND "; 543 } 544 if (!empty($USER) and !empty($USER->id)) { // May need to check they are a teacher 545 if (!has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM))) { 546 $visiblecourses = "$sqland ((c.visible > 0) OR t.userid = '$USER->id')"; 547 $teachertable = "LEFT JOIN {$CFG->prefix}user_teachers t ON t.course=c.id"; 548 } 549 } else { 550 $visiblecourses = "$sqland c.visible > 0"; 551 } 552 553 if ($limitfrom !== "") { 554 $limit = sql_paging_limit($limitfrom, $limitnum); 555 } else { 556 $limit = ""; 557 } 558 559 $selectsql = "{$CFG->prefix}course c $teachertable WHERE $categoryselect $visiblecourses"; 560 561 $totalcount = count_records_sql("SELECT COUNT(DISTINCT c.id) FROM $selectsql"); 562 563 return get_records_sql("SELECT $fields FROM $selectsql ".((!empty($sort)) ? "ORDER BY $sort" : "")." $limit"); 564 */ 565 } 566 567 /* 568 * Retrieve course records with the course managers and other related records 569 * that we need for print_course(). This allows print_courses() to do its job 570 * in a constant number of DB queries, regardless of the number of courses, 571 * role assignments, etc. 572 * 573 * The returned array is indexed on c.id, and each course will have 574 * - $course->context - a context obj 575 * - $course->managers - array containing RA objects that include a $user obj 576 * with the minimal fields needed for fullname() 577 * 578 */ 579 function get_courses_wmanagers($categoryid=0, $sort="c.sortorder ASC", $fields=array()) { 580 /* 581 * The plan is to 582 * 583 * - Grab the courses JOINed w/context 584 * 585 * - Grab the interesting course-manager RAs 586 * JOINed with a base user obj and add them to each course 587 * 588 * So as to do all the work in 2 DB queries. The RA+user JOIN 589 * ends up being pretty expensive if it happens over _all_ 590 * courses on a large site. (Are we surprised!?) 591 * 592 * So this should _never_ get called with 'all' on a large site. 593 * 594 */ 595 global $USER, $CFG; 596 597 $allcats = false; // bool flag 598 if ($categoryid === 'all') { 599 $categoryclause = ''; 600 $allcats = true; 601 } elseif (is_numeric($categoryid)) { 602 $categoryclause = "c.category = $categoryid"; 603 } else { 604 debugging("Could not recognise categoryid = $categoryid"); 605 $categoryclause = ''; 606 } 607 608 $basefields = array('id', 'category', 'sortorder', 609 'shortname', 'fullname', 'idnumber', 610 'teacher', 'teachers', 'student', 'students', 611 'guest', 'startdate', 'visible', 612 'newsitems', 'cost', 'enrol', 613 'groupmode', 'groupmodeforce'); 614 615 if (!is_null($fields) && is_string($fields)) { 616 if (empty($fields)) { 617 $fields = $basefields; 618 } else { 619 // turn the fields from a string to an array that 620 // get_user_courses_bycap() will like... 621 $fields = explode(',',$fields); 622 $fields = array_map('trim', $fields); 623 $fields = array_unique(array_merge($basefields, $fields)); 624 } 625 } elseif (is_array($fields)) { 626 $fields = array_merge($basefields,$fields); 627 } 628 $coursefields = 'c.' .join(',c.', $fields); 629 630 if (empty($sort)) { 631 $sortstatement = ""; 632 } else { 633 $sortstatement = "ORDER BY $sort"; 634 } 635 636 $where = 'WHERE c.id != ' . SITEID; 637 if ($categoryclause !== ''){ 638 $where = "$where AND $categoryclause"; 639 } 640 641 // pull out all courses matching the cat 642 $sql = "SELECT $coursefields, 643 ctx.id AS ctxid, ctx.path AS ctxpath, 644 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel 645 FROM {$CFG->prefix}course c 646 JOIN {$CFG->prefix}context ctx 647 ON (c.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.") 648 $where 649 $sortstatement"; 650 651 $catpaths = array(); 652 $catpath = NULL; 653 if ($courses = get_records_sql($sql)) { 654 // loop on courses materialising 655 // the context, and prepping data to fetch the 656 // managers efficiently later... 657 foreach ($courses as $k => $course) { 658 $courses[$k] = make_context_subobj($courses[$k]); 659 $courses[$k]->managers = array(); 660 if ($allcats === false) { 661 // single cat, so take just the first one... 662 if ($catpath === NULL) { 663 $catpath = preg_replace(':/\d+$:', '',$courses[$k]->context->path); 664 } 665 } else { 666 // chop off the contextid of the course itself 667 // like dirname() does... 668 $catpaths[] = preg_replace(':/\d+$:', '',$courses[$k]->context->path); 669 } 670 } 671 } else { 672 return array(); // no courses! 673 } 674 675 $CFG->coursemanager = trim($CFG->coursemanager); 676 if (empty($CFG->coursemanager)) { 677 return $courses; 678 } 679 680 $managerroles = split(',', $CFG->coursemanager); 681 $catctxids = ''; 682 if (count($managerroles)) { 683 if ($allcats === true) { 684 $catpaths = array_unique($catpaths); 685 $ctxids = array(); 686 foreach ($catpaths as $cpath) { 687 $ctxids = array_merge($ctxids, explode('/',substr($cpath,1))); 688 } 689 $ctxids = array_unique($ctxids); 690 $catctxids = implode( ',' , $ctxids); 691 unset($catpaths); 692 unset($cpath); 693 } else { 694 // take the ctx path from the first course 695 // as all categories will be the same... 696 $catpath = substr($catpath,1); 697 $catpath = preg_replace(':/\d+$:','',$catpath); 698 $catctxids = str_replace('/',',',$catpath); 699 } 700 if ($categoryclause !== '') { 701 $categoryclause = "AND $categoryclause"; 702 } 703 /* 704 * Note: Here we use a LEFT OUTER JOIN that can 705 * "optionally" match to avoid passing a ton of context 706 * ids in an IN() clause. Perhaps a subselect is faster. 707 * 708 * In any case, this SQL is not-so-nice over large sets of 709 * courses with no $categoryclause. 710 * 711 */ 712 $sql = "SELECT ctx.path, ctx.instanceid, ctx.contextlevel, 713 ra.hidden, 714 r.id AS roleid, r.name as rolename, 715 u.id AS userid, u.firstname, u.lastname 716 FROM {$CFG->prefix}role_assignments ra 717 JOIN {$CFG->prefix}context ctx 718 ON ra.contextid = ctx.id 719 JOIN {$CFG->prefix}user u 720 ON ra.userid = u.id 721 JOIN {$CFG->prefix}role r 722 ON ra.roleid = r.id 723 LEFT OUTER JOIN {$CFG->prefix}course c 724 ON (ctx.instanceid=c.id AND ctx.contextlevel=".CONTEXT_COURSE.") 725 WHERE ( c.id IS NOT NULL"; 726 // under certain conditions, $catctxids is NULL 727 if($catctxids == NULL){ 728 $sql .= ") "; 729 }else{ 730 $sql .= " OR ra.contextid IN ($catctxids) )"; 731 } 732 733 $sql .= "AND ra.roleid IN ({$CFG->coursemanager}) 734 $categoryclause 735 ORDER BY r.sortorder ASC, ctx.contextlevel ASC, ra.sortorder ASC"; 736 $rs = get_recordset_sql($sql); 737 738 // This loop is fairly stupid as it stands - might get better 739 // results doing an initial pass clustering RAs by path. 740 while ($ra = rs_fetch_next_record($rs)) { 741 $user = new StdClass; 742 $user->id = $ra->userid; unset($ra->userid); 743 $user->firstname = $ra->firstname; unset($ra->firstname); 744 $user->lastname = $ra->lastname; unset($ra->lastname); 745 $ra->user = $user; 746 if ($ra->contextlevel == CONTEXT_SYSTEM) { 747 foreach ($courses as $k => $course) { 748 $courses[$k]->managers[] = $ra; 749 } 750 } elseif ($ra->contextlevel == CONTEXT_COURSECAT) { 751 if ($allcats === false) { 752 // It always applies 753 foreach ($courses as $k => $course) { 754 $courses[$k]->managers[] = $ra; 755 } 756 } else { 757 foreach ($courses as $k => $course) { 758 // Note that strpos() returns 0 as "matched at pos 0" 759 if (strpos($course->context->path, $ra->path.'/')===0) { 760 // Only add it to subpaths 761 $courses[$k]->managers[] = $ra; 762 } 763 } 764 } 765 } else { // course-level 766 if(!array_key_exists($ra->instanceid, $courses)) { 767 //this course is not in a list, probably a frontpage course 768 continue; 769 } 770 $courses[$ra->instanceid]->managers[] = $ra; 771 } 772 } 773 rs_close($rs); 774 } 775 776 return $courses; 777 } 778 779 /** 780 * Convenience function - lists courses that a user has access to view. 781 * 782 * For admins and others with access to "every" course in the system, we should 783 * try to get courses with explicit RAs. 784 * 785 * NOTE: this function is heavily geared towards the perspective of the user 786 * passed in $userid. So it will hide courses that the user cannot see 787 * (for any reason) even if called from cron or from another $USER's 788 * perspective. 789 * 790 * If you really want to know what courses are assigned to the user, 791 * without any hiding or scheming, call the lower-level 792 * get_user_courses_bycap(). 793 * 794 * 795 * Notes inherited from get_user_courses_bycap(): 796 * 797 * - $fields is an array of fieldnames to ADD 798 * so name the fields you really need, which will 799 * be added and uniq'd 800 * 801 * - the course records have $c->context which is a fully 802 * valid context object. Saves you a query per course! 803 * 804 * @uses $CFG,$USER 805 * @param int $userid The user of interest 806 * @param string $sort the sortorder in the course table 807 * @param array $fields - names of _additional_ fields to return (also accepts a string) 808 * @param bool $doanything True if using the doanything flag 809 * @param int $limit Maximum number of records to return, or 0 for unlimited 810 * @return array {@link $COURSE} of course objects 811 */ 812 function get_my_courses($userid, $sort='visible DESC,sortorder ASC', $fields=NULL, $doanything=false,$limit=0) { 813 814 global $CFG,$USER; 815 816 // Guest's do not have any courses 817 $sitecontext = get_context_instance(CONTEXT_SYSTEM); 818 if (has_capability('moodle/legacy:guest',$sitecontext,$userid,false)) { 819 return(array()); 820 } 821 822 $basefields = array('id', 'category', 'sortorder', 823 'shortname', 'fullname', 'idnumber', 824 'teacher', 'teachers', 'student', 'students', 825 'guest', 'startdate', 'visible', 826 'newsitems', 'cost', 'enrol', 827 'groupmode', 'groupmodeforce'); 828 829 if (!is_null($fields) && is_string($fields)) { 830 if (empty($fields)) { 831 $fields = $basefields; 832 } else { 833 // turn the fields from a string to an array that 834 // get_user_courses_bycap() will like... 835 $fields = explode(',',$fields); 836 $fields = array_map('trim', $fields); 837 $fields = array_unique(array_merge($basefields, $fields)); 838 } 839 } elseif (is_array($fields)) { 840 $fields = array_unique(array_merge($basefields, $fields)); 841 } else { 842 $fields = $basefields; 843 } 844 845 $orderby = ''; 846 $sort = trim($sort); 847 if (!empty($sort)) { 848 $rawsorts = explode(',', $sort); 849 $sorts = array(); 850 foreach ($rawsorts as $rawsort) { 851 $rawsort = trim($rawsort); 852 if (strpos($rawsort, 'c.') === 0) { 853 $rawsort = substr($rawsort, 2); 854 } 855 $sorts[] = trim($rawsort); 856 } 857 $sort = 'c.'.implode(',c.', $sorts); 858 $orderby = "ORDER BY $sort"; 859 } 860 861 // 862 // Logged-in user - Check cached courses 863 // 864 // NOTE! it's a _string_ because 865 // - it's all we'll ever use 866 // - it serialises much more compact than an array 867 // this a big concern here - cost of serialise 868 // and unserialise gets huge as the session grows 869 // 870 // If the courses are too many - it won't be set 871 // for large numbers of courses, caching in the session 872 // has marginal benefits (costs too much, not 873 // worthwhile...) and we may hit SQL parser limits 874 // because we use IN() 875 // 876 if ($userid === $USER->id) { 877 if (isset($USER->loginascontext) 878 && $USER->loginascontext->contextlevel == CONTEXT_COURSE) { 879 // list _only_ this course 880 // anything else is asking for trouble... 881 $courseids = $USER->loginascontext->instanceid; 882 } elseif (isset($USER->mycourses) 883 && is_string($USER->mycourses)) { 884 if ($USER->mycourses === '') { 885 // empty str means: user has no courses 886 // ... so do the easy thing... 887 return array(); 888 } else { 889 $courseids = $USER->mycourses; 890 } 891 } 892 if (isset($courseids)) { 893 // The data massaging here MUST be kept in sync with 894 // get_user_courses_bycap() so we return 895 // the same... 896 // (but here we don't need to check has_cap) 897 $coursefields = 'c.' .join(',c.', $fields); 898 $sql = "SELECT $coursefields, 899 ctx.id AS ctxid, ctx.path AS ctxpath, 900 ctx.depth as ctxdepth, ctx.contextlevel AS ctxlevel, 901 cc.path AS categorypath 902 FROM {$CFG->prefix}course c 903 JOIN {$CFG->prefix}course_categories cc 904 ON c.category=cc.id 905 JOIN {$CFG->prefix}context ctx 906 ON (c.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.") 907 WHERE c.id IN ($courseids) 908 $orderby"; 909 $rs = get_recordset_sql($sql); 910 $courses = array(); 911 $cc = 0; // keep count 912 while ($c = rs_fetch_next_record($rs)) { 913 // build the context obj 914 $c = make_context_subobj($c); 915 916 $courses[$c->id] = $c; 917 if ($limit > 0 && $cc++ > $limit) { 918 break; 919 } 920 } 921 rs_close($rs); 922 return $courses; 923 } 924 } 925 926 // Non-cached - get accessinfo 927 if ($userid === $USER->id && isset($USER->access)) { 928 $accessinfo = $USER->access; 929 } else { 930 $accessinfo = get_user_access_sitewide($userid); 931 } 932 933 934 $courses = get_user_courses_bycap($userid, 'moodle/course:view', $accessinfo, 935 $doanything, $sort, $fields, 936 $limit); 937 938 $cats = NULL; 939 // If we have to walk category visibility 940 // to eval course visibility, get the categories 941 if (empty($CFG->allowvisiblecoursesinhiddencategories)) { 942 $sql = "SELECT cc.id, cc.path, cc.visible, 943 ctx.id AS ctxid, ctx.path AS ctxpath, 944 ctx.depth as ctxdepth, ctx.contextlevel AS ctxlevel 945 FROM {$CFG->prefix}course_categories cc 946 JOIN {$CFG->prefix}context ctx ON (cc.id = ctx.instanceid) 947 WHERE ctx.contextlevel = ".CONTEXT_COURSECAT." 948 ORDER BY cc.id"; 949 $rs = get_recordset_sql($sql); 950 951 // Using a temporary array instead of $cats here, to avoid a "true" result when isnull($cats) further down 952 $categories = array(); 953 while ($course_cat = rs_fetch_next_record($rs)) { 954 // build the context obj 955 $course_cat = make_context_subobj($course_cat); 956 $categories[$course_cat->id] = $course_cat; 957 } 958 rs_close($rs); 959 960 if (!empty($categories)) { 961 $cats = $categories; 962 } 963 964 unset($course_cat); 965 } 966 // 967 // Strangely, get_my_courses() is expected to return the 968 // array keyed on id, which messes up the sorting 969 // So do that, and also cache the ids in the session if appropriate 970 // 971 $kcourses = array(); 972 $courses_count = count($courses); 973 $cacheids = NULL; 974 $vcatpaths = array(); 975 if ($userid === $USER->id && $courses_count < 500) { 976 $cacheids = array(); 977 } 978 for ($n=0; $n<$courses_count; $n++) { 979 980 // 981 // Check whether $USER (not $userid) can _actually_ see them 982 // Easy if $CFG->allowvisiblecoursesinhiddencategories 983 // is set, and we don't have to care about categories. 984 // Lots of work otherwise... (all in mem though!) 985 // 986 $cansee = false; 987 if (is_null($cats)) { // easy rules! 988 if ($courses[$n]->visible == true) { 989 $cansee = true; 990 } elseif (has_capability('moodle/course:viewhiddencourses', 991 $courses[$n]->context, $USER->id)) { 992 $cansee = true; 993 } 994 } else { 995 // 996 // Is the cat visible? 997 // we have to assume it _is_ visible 998 // so we can shortcut when we find a hidden one 999 // 1000 $viscat = true; 1001 $cpath = $courses[$n]->categorypath; 1002 if (isset($vcatpaths[$cpath])) { 1003 $viscat = $vcatpaths[$cpath]; 1004 } else { 1005 $cpath = substr($cpath,1); // kill leading slash 1006 $cpath = explode('/',$cpath); 1007 $ccct = count($cpath); 1008 for ($m=0;$m<$ccct;$m++) { 1009 $ccid = $cpath[$m]; 1010 if ($cats[$ccid]->visible==false) { 1011 $viscat = false; 1012 break; 1013 } 1014 } 1015 $vcatpaths[$courses[$n]->categorypath] = $viscat; 1016 } 1017 1018 // 1019 // Perhaps it's actually visible to $USER 1020 // check moodle/category:visibility 1021 // 1022 // The name isn't obvious, but the description says 1023 // "See hidden categories" so the user shall see... 1024 // But also check if the allowvisiblecoursesinhiddencategories setting is true, and check for course visibility 1025 if ($viscat === false) { 1026 $catctx = $cats[$courses[$n]->category]->context; 1027 if (has_capability('moodle/category:visibility', $catctx, $USER->id)) { 1028 $vcatpaths[$courses[$n]->categorypath] = true; 1029 $viscat = true; 1030 } elseif ($CFG->allowvisiblecoursesinhiddencategories && $courses[$n]->visible == true) { 1031 $viscat = true; 1032 } 1033 } 1034 1035 // 1036 // Decision matrix 1037 // 1038 if ($viscat === true) { 1039 if ($courses[$n]->visible == true) { 1040 $cansee = true; 1041 } elseif (has_capability('moodle/course:viewhiddencourses', 1042 $courses[$n]->context, $USER->id)) { 1043 $cansee = true; 1044 } 1045 } 1046 } 1047 if ($cansee === true) { 1048 $kcourses[$courses[$n]->id] = $courses[$n]; 1049 if (is_array($cacheids)) { 1050 $cacheids[] = $courses[$n]->id; 1051 } 1052 } 1053 } 1054 if (is_array($cacheids)) { 1055 // Only happens 1056 // - for the logged in user 1057 // - below the threshold (500) 1058 // empty string is _valid_ 1059 $USER->mycourses = join(',',$cacheids); 1060 } elseif ($userid === $USER->id && isset($USER->mycourses)) { 1061 // cheap sanity check 1062 unset($USER->mycourses); 1063 } 1064 1065 return $kcourses; 1066 } 1067 1068 /** 1069 * A list of courses that match a search 1070 * 1071 * @uses $CFG 1072 * @param array $searchterms ? 1073 * @param string $sort ? 1074 * @param int $page ? 1075 * @param int $recordsperpage ? 1076 * @param int $totalcount Passed in by reference. ? 1077 * @return object {@link $COURSE} records 1078 */ 1079 function get_courses_search($searchterms, $sort='fullname ASC', $page=0, $recordsperpage=50, &$totalcount) { 1080 1081 global $CFG; 1082 1083 //to allow case-insensitive search for postgesql 1084 if ($CFG->dbfamily == 'postgres') { 1085 $LIKE = 'ILIKE'; 1086 $NOTLIKE = 'NOT ILIKE'; // case-insensitive 1087 $REGEXP = '~*'; 1088 $NOTREGEXP = '!~*'; 1089 } else { 1090 $LIKE = 'LIKE'; 1091 $NOTLIKE = 'NOT LIKE'; 1092 $REGEXP = 'REGEXP'; 1093 $NOTREGEXP = 'NOT REGEXP'; 1094 } 1095 1096 $fullnamesearch = ''; 1097 $summarysearch = ''; 1098 1099 foreach ($searchterms as $searchterm) { 1100 1101 $NOT = ''; /// Initially we aren't going to perform NOT LIKE searches, only MSSQL and Oracle 1102 /// will use it to simulate the "-" operator with LIKE clause 1103 1104 /// Under Oracle and MSSQL, trim the + and - operators and perform 1105 /// simpler LIKE (or NOT LIKE) queries 1106 if ($CFG->dbfamily == 'oracle' || $CFG->dbfamily == 'mssql') { 1107 if (substr($searchterm, 0, 1) == '-') { 1108 $NOT = ' NOT '; 1109 } 1110 $searchterm = trim($searchterm, '+-'); 1111 } 1112 1113 if ($fullnamesearch) { 1114 $fullnamesearch .= ' AND '; 1115 } 1116 if ($summarysearch) { 1117 $summarysearch .= ' AND '; 1118 } 1119 1120 if (substr($searchterm,0,1) == '+') { 1121 $searchterm = substr($searchterm,1); 1122 $summarysearch .= " c.summary $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' "; 1123 $fullnamesearch .= " c.fullname $REGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' "; 1124 } else if (substr($searchterm,0,1) == "-") { 1125 $searchterm = substr($searchterm,1); 1126 $summarysearch .= " c.summary $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' "; 1127 $fullnamesearch .= " c.fullname $NOTREGEXP '(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)' "; 1128 } else { 1129 $summarysearch .= ' summary '. $NOT . $LIKE .' \'%'. $searchterm .'%\' '; 1130 $fullnamesearch .= ' fullname '. $NOT . $LIKE .' \'%'. $searchterm .'%\' '; 1131 } 1132 1133 } 1134 1135 $sql = "SELECT c.*, 1136 ctx.id AS ctxid, ctx.path AS ctxpath, 1137 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel 1138 FROM {$CFG->prefix}course c 1139 JOIN {$CFG->prefix}context ctx 1140 ON (c.id = ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSE.") 1141 WHERE (( $fullnamesearch ) OR ( $summarysearch )) 1142 AND category > 0 1143 ORDER BY " . $sort; 1144 1145 $courses = array(); 1146 1147 if ($rs = get_recordset_sql($sql)) { 1148 1149 1150 // Tiki pagination 1151 $limitfrom = $page * $recordsperpage; 1152 $limitto = $limitfrom + $recordsperpage; 1153 $c = 0; // counts how many visible courses we've seen 1154 1155 while ($course = rs_fetch_next_record($rs)) { 1156 $course = make_context_subobj($course); 1157 if ($course->visible || has_capability('moodle/course:viewhiddencourses', $course->context)) { 1158 // Don't exit this loop till the end 1159 // we need to count all the visible courses 1160 // to update $totalcount 1161 if ($c >= $limitfrom && $c < $limitto) { 1162 $courses[] = $course; 1163 } 1164 $c++; 1165 } 1166 } 1167 } 1168 1169 // our caller expects 2 bits of data - our return 1170 // array, and an updated $totalcount 1171 $totalcount = $c; 1172 return $courses; 1173 } 1174 1175 1176 /** 1177 * Returns a sorted list of categories. Each category object has a context 1178 * property that is a context object. 1179 * 1180 * When asking for $parent='none' it will return all the categories, regardless 1181 * of depth. Wheen asking for a specific parent, the default is to return 1182 * a "shallow" resultset. Pass false to $shallow and it will return all 1183 * the child categories as well. 1184 * 1185 * 1186 * @param string $parent The parent category if any 1187 * @param string $sort the sortorder 1188 * @param bool $shallow - set to false to get the children too 1189 * @return array of categories 1190 */ 1191 function get_categories($parent='none', $sort=NULL, $shallow=true) { 1192 global $CFG; 1193 1194 if ($sort === NULL) { 1195 $sort = 'ORDER BY cc.sortorder ASC'; 1196 } elseif ($sort ==='') { 1197 // leave it as empty 1198 } else { 1199 $sort = "ORDER BY $sort"; 1200 } 1201 1202 if ($parent === 'none') { 1203 $sql = "SELECT cc.*, 1204 ctx.id AS ctxid, ctx.path AS ctxpath, 1205 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel 1206 FROM {$CFG->prefix}course_categories cc 1207 JOIN {$CFG->prefix}context ctx 1208 ON cc.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSECAT." 1209 $sort"; 1210 } elseif ($shallow) { 1211 $parent = (int)$parent; 1212 $sql = "SELECT cc.*, 1213 ctx.id AS ctxid, ctx.path AS ctxpath, 1214 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel 1215 FROM {$CFG->prefix}course_categories cc 1216 JOIN {$CFG->prefix}context ctx 1217 ON cc.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSECAT." 1218 WHERE cc.parent=$parent 1219 $sort"; 1220 } else { 1221 $parent = (int)$parent; 1222 $sql = "SELECT cc.*, 1223 ctx.id AS ctxid, ctx.path AS ctxpath, 1224 ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel 1225 FROM {$CFG->prefix}course_categories cc 1226 JOIN {$CFG->prefix}context ctx 1227 ON cc.id=ctx.instanceid AND ctx.contextlevel=".CONTEXT_COURSECAT." 1228 JOIN {$CFG->prefix}course_categories ccp 1229 ON (cc.path LIKE ".sql_concat('ccp.path',"'%'").") 1230 WHERE ccp.id=$parent 1231 $sort"; 1232 } 1233 $categories = array(); 1234 1235 if( $rs = get_recordset_sql($sql) ){ 1236 while ($cat = rs_fetch_next_record($rs)) { 1237 $cat = make_context_subobj($cat); 1238 if ($cat->visible || has_capability('moodle/category:visibility',$cat->context)) { 1239 $categories[$cat->id] = $cat; 1240 } 1241 } 1242 } 1243 return $categories; 1244 } 1245 1246 1247 /** 1248 * Returns an array of category ids of all the subcategories for a given 1249 * category. 1250 * @param $catid - The id of the category whose subcategories we want to find. 1251 * @return array of category ids. 1252 */ 1253 function get_all_subcategories($catid) { 1254 1255 $subcats = array(); 1256 1257 if ($categories = get_records('course_categories', 'parent', $catid)) { 1258 foreach ($categories as $cat) { 1259 array_push($subcats, $cat->id); 1260 $subcats = array_merge($subcats, get_all_subcategories($cat->id)); 1261 } 1262 } 1263 return $subcats; 1264 } 1265 1266 1267 /** 1268 * This recursive function makes sure that the courseorder is consecutive 1269 * 1270 * @param type description 1271 * 1272 * $n is the starting point, offered only for compatilibity -- will be ignored! 1273 * $safe (bool) prevents it from assuming category-sortorder is unique, used to upgrade 1274 * safely from 1.4 to 1.5 1275 */ 1276 function fix_course_sortorder($categoryid=0, $n=0, $safe=0, $depth=0, $path='') { 1277 1278 global $CFG; 1279 1280 $count = 0; 1281 1282 $catgap = 1000; // "standard" category gap 1283 $tolerance = 200; // how "close" categories can get 1284 1285 if ($categoryid > 0){ 1286 // update depth and path 1287 $cat = get_record('course_categories', 'id', $categoryid); 1288 if ($cat->parent == 0) { 1289 $depth = 0; 1290 $path = ''; 1291 } else if ($depth == 0 ) { // doesn't make sense; get from DB 1292 // this is only called if the $depth parameter looks dodgy 1293 $parent = get_record('course_categories', 'id', $cat->parent); 1294 $path = $parent->path; 1295 $depth = $parent->depth; 1296 } 1297 $path = $path . '/' . $categoryid; 1298 $depth = $depth + 1; 1299 1300 if ($cat->path !== $path) { 1301 set_field('course_categories', 'path', addslashes($path), 'id', $categoryid); 1302 } 1303 if ($cat->depth != $depth) { 1304 set_field('course_categories', 'depth', $depth, 'id', $categoryid); 1305 } 1306 } 1307 1308 // get some basic info about courses in the category 1309 $info = get_record_sql('SELECT MIN(sortorder) AS min, 1310 MAX(sortorder) AS max, 1311 COUNT(sortorder) AS count 1312 FROM ' . $CFG->prefix . 'course 1313 WHERE category=' . $categoryid); 1314 if (is_object($info)) { // no courses? 1315 $max = $info->max; 1316 $count = $info->count; 1317 $min = $info->min; 1318 unset($info); 1319 } 1320 1321 if ($categoryid > 0 && $n==0) { // only passed category so don't shift it 1322 $n = $min; 1323 } 1324 1325 // $hasgap flag indicates whether there's a gap in the sequence 1326 $hasgap = false; 1327 if ($max-$min+1 != $count) { 1328 $hasgap = true; 1329 } 1330 1331 // $mustshift indicates whether the sequence must be shifted to 1332 // meet its range 1333 $mustshift = false; 1334 if ($min < $n+$tolerance || $min > $n+$tolerance+$catgap ) { 1335 $mustshift = true; 1336 } 1337 1338 // actually sort only if there are courses, 1339 // and we meet one ofthe triggers: 1340 // - safe flag 1341 // - they are not in a continuos block 1342 // - they are too close to the 'bottom' 1343 if ($count && ( $safe || $hasgap || $mustshift ) ) { 1344 // special, optimized case where all we need is to shift 1345 if ( $mustshift && !$safe && !$hasgap) { 1346 $shift = $n + $catgap - $min; 1347 if ($shift < $count) { 1348 $shift = $count + $catgap; 1349 } 1350 // UPDATE course SET sortorder=sortorder+$shift 1351 execute_sql("UPDATE {$CFG->prefix}course 1352 SET sortorder=sortorder+$shift 1353 WHERE category=$categoryid", 0); 1354 $n = $n + $catgap + $count; 1355 1356 } else { // do it slowly 1357 $n = $n + $catgap; 1358 // if the new sequence overlaps the current sequence, lack of transactions 1359 // will stop us -- shift things aside for a moment... 1360 if ($safe || ($n >= $min && $n+$count+1 < $min && $CFG->dbfamily==='mysql')) { 1361 $shift = $max + $n + 1000; 1362 execute_sql("UPDATE {$CFG->prefix}course 1363 SET sortorder=sortorder+$shift 1364 WHERE category=$categoryid", 0); 1365 } 1366 1367 $courses = get_courses($categoryid, 'c.sortorder ASC', 'c.id,c.sortorder'); 1368 begin_sql(); 1369 $tx = true; // transaction sanity 1370 foreach ($courses as $course) { 1371 if ($tx && $course->sortorder != $n ) { // save db traffic 1372 $tx = $tx && set_field('course', 'sortorder', $n, 1373 'id', $course->id); 1374 } 1375 $n++; 1376 } 1377 if ($tx) { 1378 commit_sql(); 1379 } else { 1380 rollback_sql(); 1381 if (!$safe) { 1382 // if we failed when called with !safe, try 1383 // to recover calling self with safe=true 1384 return fix_course_sortorder($categoryid, $n, true, $depth, $path); 1385 } 1386 } 1387 } 1388 } 1389 set_field('course_categories', 'coursecount', $count, 'id', $categoryid); 1390 1391 // $n could need updating 1392 $max = get_field_sql("SELECT MAX(sortorder) from {$CFG->prefix}course WHERE category=$categoryid"); 1393 if ($max > $n) { 1394 $n = $max; 1395 } 1396 1397 if ($categories = get_categories($categoryid)) { 1398 foreach ($categories as $category) { 1399 $n = fix_course_sortorder($category->id, $n, $safe, $depth, $path); 1400 } 1401 } 1402 1403 return $n+1; 1404 } 1405 1406 /** 1407 * Ensure all courses have a valid course category 1408 * useful if a category has been removed manually 1409 **/ 1410 function fix_coursecategory_orphans() { 1411 1412 global $CFG; 1413 1414 // Note: the handling of sortorder here is arguably 1415 // open to race conditions. Hard to fix here, unlikely 1416 // to hit anyone in production. 1417 1418 $sql = "SELECT c.id, c.category, c.shortname 1419 FROM {$CFG->prefix}course c 1420 LEFT OUTER JOIN {$CFG->prefix}course_categories cc ON c.category=cc.id 1421 WHERE cc.id IS NULL AND c.id != " . SITEID; 1422 1423 $rs = get_recordset_sql($sql); 1424 1425 if (!rs_EOF($rs)) { // we have some orphans 1426 1427 // the "default" category is the lowest numbered... 1428 $default = get_field_sql("SELECT MIN(id) 1429 FROM {$CFG->prefix}course_categories"); 1430 $sortorder = get_field_sql("SELECT MAX(sortorder) 1431 FROM {$CFG->prefix}course 1432 WHERE category=$default"); 1433 1434 1435 begin_sql(); 1436 $tx = true; 1437 while ($tx && $course = rs_fetch_next_record($rs)) { 1438 $tx = $tx && set_field('course', 'category', $default, 'id', $course->id); 1439 $tx = $tx && set_field('course', 'sortorder', ++$sortorder, 'id', $course->id); 1440 } 1441 if ($tx) { 1442 commit_sql(); 1443 } else { 1444 rollback_sql(); 1445 } 1446 } 1447 rs_close($rs); 1448 } 1449 1450 /** 1451 * List of remote courses that a user has access to via MNET. 1452 * Works only on the IDP 1453 * 1454 * @uses $CFG, $USER 1455 * @return array {@link $COURSE} of course objects 1456 */ 1457 function get_my_remotecourses($userid=0) { 1458 global $CFG, $USER; 1459 1460 if (empty($userid)) { 1461 $userid = $USER->id; 1462 } 1463 1464 $sql = "SELECT c.id, c.remoteid, c.shortname, c.fullname, 1465 c.hostid, c.summary, c.cat_name, 1466 h.name AS hostname 1467 FROM {$CFG->prefix}mnet_enrol_course c 1468 JOIN {$CFG->prefix}mnet_enrol_assignments a ON c.id=a.courseid 1469 JOIN {$CFG->prefix}mnet_host h ON c.hostid=h.id 1470 WHERE a.userid={$userid}"; 1471 1472 return get_records_sql($sql); 1473 } 1474 1475 /** 1476 * List of remote hosts that a user has access to via MNET. 1477 * Works on the SP 1478 * 1479 * @uses $CFG, $USER 1480 * @return array of host objects 1481 */ 1482 function get_my_remotehosts() { 1483 global $CFG, $USER; 1484 1485 if ($USER->mnethostid == $CFG->mnet_localhost_id) { 1486 return false; // Return nothing on the IDP 1487 } 1488 if (!empty($USER->mnet_foreign_host_array) && is_array($USER->mnet_foreign_host_array)) { 1489 return $USER->mnet_foreign_host_array; 1490 } 1491 return false; 1492 } 1493 1494 /** 1495 * This function creates a default separated/connected scale 1496 * 1497 * This function creates a default separated/connected scale 1498 * so there's something in the database. The locations of 1499 * strings and files is a bit odd, but this is because we 1500 * need to maintain backward compatibility with many different 1501 * existing language translations and older sites. 1502 * 1503 * @uses $CFG 1504 */ 1505 function make_default_scale() { 1506 1507 global $CFG; 1508 1509 $defaultscale = NULL; 1510 $defaultscale->courseid = 0; 1511 $defaultscale->userid = 0; 1512 $defaultscale->name = get_string('separateandconnected'); 1513 $defaultscale->scale = get_string('postrating1', 'forum').','. 1514 get_string('postrating2', 'forum').','. 1515 get_string('postrating3', 'forum'); 1516 $defaultscale->timemodified = time(); 1517 1518 /// Read in the big description from the file. Note this is not 1519 /// HTML (despite the file extension) but Moodle format text. 1520 $parentlang = get_string('parentlanguage'); 1521 if ($parentlang[0] == '[') { 1522 $parentlang = ''; 1523 } 1524 if (is_readable($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) { 1525 $file = file($CFG->dataroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html'); 1526 } else if (is_readable($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html')) { 1527 $file = file($CFG->dirroot .'/lang/'. $CFG->lang .'/help/forum/ratings.html'); 1528 } else if ($parentlang and is_readable($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) { 1529 $file = file($CFG->dataroot .'/lang/'. $parentlang .'/help/forum/ratings.html'); 1530 } else if ($parentlang and is_readable($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html')) { 1531 $file = file($CFG->dirroot .'/lang/'. $parentlang .'/help/forum/ratings.html'); 1532 } else if (is_readable($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html')) { 1533 $file = file($CFG->dirroot .'/lang/en_utf8/help/forum/ratings.html'); 1534 } else { 1535 $file = ''; 1536 } 1537 1538 $defaultscale->description = addslashes(implode('', $file)); 1539 1540 if ($defaultscale->id = insert_record('scale', $defaultscale)) { 1541 execute_sql('UPDATE '. $CFG->prefix .'forum SET scale = \''. $defaultscale->id .'\'', false); 1542 } 1543 } 1544 1545 1546 /** 1547 * Returns a menu of all available scales from the site as well as the given course 1548 * 1549 * @uses $CFG 1550 * @param int $courseid The id of the course as found in the 'course' table. 1551 * @return object 1552 */ 1553 function get_scales_menu($courseid=0) { 1554 1555 global $CFG; 1556 1557 $sql = "SELECT id, name FROM {$CFG->prefix}scale 1558 WHERE courseid = '0' or courseid = '$courseid' 1559 ORDER BY courseid ASC, name ASC"; 1560 1561 if ($scales = get_records_sql_menu($sql)) { 1562 return $scales; 1563 } 1564 1565 make_default_scale(); 1566 1567 return get_records_sql_menu($sql); 1568 } 1569 1570 1571 1572 /** 1573 * Given a set of timezone records, put them in the database, replacing what is there 1574 * 1575 * @uses $CFG 1576 * @param array $timezones An array of timezone records 1577 */ 1578 function update_timezone_records($timezones) { 1579 /// Given a set of timezone records, put them in the database 1580 1581 global $CFG; 1582 1583 /// Clear out all the old stuff 1584 execute_sql('TRUNCATE TABLE '.$CFG->prefix.'timezone', false); 1585 1586 /// Insert all the new stuff 1587 foreach ($timezones as $timezone) { 1588 if (is_array($timezone)) { 1589 $timezone = (object)$timezone; 1590 } 1591 insert_record('timezone', $timezone); 1592 } 1593 } 1594 1595 1596 /// MODULE FUNCTIONS ///////////////////////////////////////////////// 1597 1598 /** 1599 * Just gets a raw list of all modules in a course 1600 * 1601 * @uses $CFG 1602 * @param int $courseid The id of the course as found in the 'course' table. 1603 * @return object 1604 */ 1605 function get_course_mods($courseid) { 1606 global $CFG; 1607 1608 if (empty($courseid)) { 1609 return false; // avoid warnings 1610 } 1611 1612 return get_records_sql("SELECT cm.*, m.name as modname 1613 FROM {$CFG->prefix}modules m, 1614 {$CFG->prefix}course_modules cm 1615 WHERE cm.course = ".intval($courseid)." 1616 AND cm.module = m.id AND m.visible = 1"); // no disabled mods 1617 } 1618 1619 1620 /** 1621 * Given an id of a course module, finds the coursemodule description 1622 * 1623 * @param string $modulename name of module type, eg. resource, assignment,... 1624 * @param int $cmid course module id (id in course_modules table) 1625 * @param int $courseid optional course id for extra validation 1626 * @return object course module instance with instance and module name 1627 */ 1628 function get_coursemodule_from_id($modulename, $cmid, $courseid=0) { 1629 1630 global $CFG; 1631 1632 $courseselect = ($courseid) ? 'cm.course = '.intval($courseid).' AND ' : ''; 1633 1634 return get_record_sql("SELECT cm.*, m.name, md.name as modname 1635 FROM {$CFG->prefix}course_modules cm, 1636 {$CFG->prefix}modules md, 1637 {$CFG->prefix}$modulename m 1638 WHERE $courseselect 1639 cm.id = ".intval($cmid)." AND 1640 cm.instance = m.id AND 1641 md.name = '$modulename' AND 1642 md.id = cm.module"); 1643 } 1644 1645 /** 1646 * Given an instance number of a module, finds the coursemodule description 1647 * 1648 * @param string $modulename name of module type, eg. resource, assignment,... 1649 * @param int $instance module instance number (id in resource, assignment etc. table) 1650 * @param int $courseid optional course id for extra validation 1651 * @return object course module instance with instance and module name 1652 */ 1653 function get_coursemodule_from_instance($modulename, $instance, $courseid=0) { 1654 1655 global $CFG; 1656 1657 $courseselect = ($courseid) ? 'cm.course = '.intval($courseid).' AND ' : ''; 1658 1659 return get_record_sql("SELECT cm.*, m.name, md.name as modname 1660 FROM {$CFG->prefix}course_modules cm, 1661 {$CFG->prefix}modules md, 1662 {$CFG->prefix}$modulename m 1663 WHERE $courseselect 1664 cm.instance = m.id AND 1665 md.name = '$modulename' AND 1666 md.id = cm.module AND 1667 m.id = ".intval($instance)); 1668 1669 } 1670 1671 /** 1672 * Returns all course modules of given activity in course 1673 * @param string $modulename (forum, quiz, etc.) 1674 * @param int $courseid 1675 * @param string $extrafields extra fields starting with m. 1676 * @return array of cm objects, false if not found or error 1677 */ 1678 function get_coursemodules_in_course($modulename, $courseid, $extrafields='') { 1679 global $CFG; 1680 1681 if (!empty($extrafields)) { 1682 $extrafields = ", $extrafields"; 1683 } 1684 return get_records_sql("SELECT cm.*, m.name, md.name as modname $extrafields 1685 FROM {$CFG->prefix}course_modules cm, 1686 {$CFG->prefix}modules md, 1687 {$CFG->prefix}$modulename m 1688 WHERE cm.course = $courseid AND 1689 cm.instance = m.id AND 1690 md.name = '$modulename' AND 1691 md.id = cm.module"); 1692 } 1693 1694 /** 1695 * Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined 1696 * 1697 * Returns an array of all the active instances of a particular 1698 * module in given courses, sorted in the order they are defined 1699 * in the course. Returns an empty array on any errors. 1700 * 1701 * The returned objects includle the columns cw.section, cm.visible, 1702 * cm.groupmode and cm.groupingid, cm.groupmembersonly, and are indexed by cm.id. 1703 * 1704 * @param string $modulename The name of the module to get instances for 1705 * @param array $courses an array of course objects. 1706 * @return array of module instance objects, including some extra fields from the course_modules 1707 * and course_sections tables, or an empty array if an error occurred. 1708 */ 1709 function get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false) { 1710 global $CFG; 1711 1712 $outputarray = array(); 1713 1714 if (empty($courses) || !is_array($courses) || count($courses) == 0) { 1715 return $outputarray; 1716 } 1717 1718 if (!$rawmods = get_records_sql("SELECT cm.id AS coursemodule, m.*, cw.section, cm.visible AS visible, 1719 cm.groupmode, cm.groupingid, cm.groupmembersonly 1720 FROM {$CFG->prefix}course_modules cm, 1721 {$CFG->prefix}course_sections cw, 1722 {$CFG->prefix}modules md, 1723 {$CFG->prefix}$modulename m 1724 WHERE cm.course IN (".implode(',',array_keys($courses)).") AND 1725 cm.instance = m.id AND 1726 cm.section = cw.id AND 1727 md.name = '$modulename' AND 1728 md.id = cm.module")) { 1729 return $outputarray; 1730 } 1731 1732 require_once($CFG->dirroot.'/course/lib.php'); 1733 1734 foreach ($courses as $course) { 1735 $modinfo = get_fast_modinfo($course, $userid); 1736 1737 if (empty($modinfo->instances[$modulename])) { 1738 continue; 1739 } 1740 1741 foreach ($modinfo->instances[$modulename] as $cm) { 1742 if (!$includeinvisible and !$cm->uservisible) { 1743 continue; 1744 } 1745 if (!isset($rawmods[$cm->id])) { 1746 continue; 1747 } 1748 $instance = $rawmods[$cm->id]; 1749 if (!empty($cm->extra)) { 1750 $instance->extra = urlencode($cm->extra); // bc compatibility 1751 } 1752 $outputarray[] = $instance; 1753 } 1754 } 1755 1756 return $outputarray; 1757 } 1758 1759 /** 1760 * Returns an array of all the active instances of a particular module in a given course, 1761 * sorted in the order they are defined. 1762 * 1763 * Returns an array of all the active instances of a particular 1764 * module in a given course, sorted in the order they are defined 1765 * in the course. Returns an empty array on any errors. 1766 * 1767 * The returned objects includle the columns cw.section, cm.visible, 1768 * cm.groupmode and cm.groupingid, cm.groupmembersonly, and are indexed by cm.id. 1769 * 1770 * @param string $modulename The name of the module to get instances for 1771 * @param object $course The course obect. 1772 * @return array of module instance objects, including some extra fields from the course_modules 1773 * and course_sections tables, or an empty array if an error occurred. 1774 */ 1775 function get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false) { 1776 return get_all_instances_in_courses($modulename, array($course->id => $course), $userid, $includeinvisible); 1777 } 1778 1779 1780 /** 1781 * Determine whether a module instance is visible within a course 1782 * 1783 * Given a valid module object with info about the id and course, 1784 * and the module's type (eg "forum") returns whether the object 1785 * is visible or not, groupmembersonly visibility not tested 1786 * 1787 * @uses $CFG 1788 * @param $moduletype Name of the module eg 'forum' 1789 * @param $module Object which is the instance of the module 1790 * @return bool 1791 */ 1792 function instance_is_visible($moduletype, $module) { 1793 1794 global $CFG; 1795 1796 if (!empty($module->id)) { 1797 if ($records = get_records_sql("SELECT cm.instance, cm.visible, cm.groupingid, cm.id, cm.groupmembersonly, cm.course 1798 FROM {$CFG->prefix}course_modules cm, 1799 {$CFG->prefix}modules m 1800 WHERE cm.course = '$module->course' AND 1801 cm.module = m.id AND 1802 m.name = '$moduletype' AND 1803 cm.instance = '$module->id'")) { 1804 1805 foreach ($records as $record) { // there should only be one - use the first one 1806 return $record->visible; 1807 } 1808 } 1809 } 1810 return true; // visible by default! 1811 } 1812 1813 /** 1814 * Determine whether a course module is visible within a course, 1815 * this is different from instance_is_visible() - faster and visibility for user 1816 * 1817 * @param object $cm object 1818 * @param int $userid empty means current user 1819 * @return bool 1820 */ 1821 function coursemodule_visible_for_user($cm, $userid=0) { 1822 global $USER; 1823 1824 if (empty($cm->id)) { 1825 debugging("Incorrect course module parameter!", DEBUG_DEVELOPER); 1826 return false; 1827 } 1828 if (empty($userid)) { 1829 $userid = $USER->id; 1830 } 1831 if (!$cm->visible and !has_capability('moodle/course:viewhiddenactivities', get_context_instance(CONTEXT_MODULE, $cm->id), $userid)) { 1832 return false; 1833 } 1834 return groups_course_module_visible($cm, $userid); 1835 } 1836 1837 1838 1839 1840 /// LOG FUNCTIONS ///////////////////////////////////////////////////// 1841 1842 1843 /** 1844 * Add an entry to the log table. 1845 * 1846 * Add an entry to the log table. These are "action" focussed rather 1847 * than web server hits, and provide a way to easily reconstruct what 1848 * any particular student has been doing. 1849 * 1850 * @uses $CFG 1851 * @uses $USER 1852 * @uses $db 1853 * @uses $REMOTE_ADDR 1854 * @uses SITEID 1855 * @param int $courseid The course id 1856 * @param string $module The module name - e.g. forum, journal, resource, course, user etc 1857 * @param string $action 'view', 'update', 'add' or 'delete', possibly followed by another word to clarify. 1858 * @param string $url The file and parameters used to see the results of the action 1859 * @param string $info Additional description information 1860 * @param string $cm The course_module->id if there is one 1861 * @param string $user If log regards $user other than $USER 1862 */ 1863 function add_to_log($courseid, $module, $action, $url='', $info='', $cm=0, $user=0) { 1864 // Note that this function intentionally does not follow the normal Moodle DB access idioms. 1865 // This is for a good reason: it is the most frequently used DB update function, 1866 // so it has been optimised for speed. 1867 global $db, $CFG, $USER; 1868 1869 if ($cm === '' || is_null($cm)) { // postgres won't translate empty string to its default 1870 $cm = 0; 1871 } 1872 1873 if ($user) { 1874 $userid = $user; 1875 } else { 1876 if (!empty($USER->realuser)) { // Don't log 1877 return; 1878 } 1879 $userid = empty($USER->id) ? '0' : $USER->id; 1880 } 1881 1882 $REMOTE_ADDR = getremoteaddr(); 1883 1884 $timenow = time(); 1885 $info = addslashes($info); 1886 if (!empty($url)) { // could break doing html_entity_decode on an empty var. 1887 $url = html_entity_decode($url); // for php < 4.3.0 this is defined in moodlelib.php 1888 } 1889 1890 // Restrict length of log lines to the space actually available in the 1891 // database so that it doesn't cause a DB error. Log a warning so that 1892 // developers can avoid doing things which are likely to cause this on a 1893 // routine basis. 1894 $tl=textlib_get_instance(); 1895 if(!empty($info) && $tl->strlen($info)>255) { 1896 $info=$tl->substr($info,0,252).'...'; 1897 debugging('Warning: logged very long info',DEBUG_DEVELOPER); 1898 } 1899 // Note: Unlike $info, URL appears to be already slashed before this function 1900 // is called. Since database limits are for the data before slashes, we need 1901 // to remove them... 1902 $url=stripslashes($url); 1903 // If the 100 field size is changed, also need to alter print_log in course/lib.php 1904 if(!empty($url) && $tl->strlen($url)>100) { 1905 $url=$tl->substr($url,0,97).'...'; 1906 debugging('Warning: logged very long URL',DEBUG_DEVELOPER); 1907 } 1908 $url=addslashes($url); 1909 1910 if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; $PERF->logwrites++;}; 1911 1912 if ($CFG->type = 'oci8po') { 1913 if (empty($info)) { 1914 $info = ' '; 1915 } 1916 } 1917 $sql ='INSERT INTO '. $CFG->prefix .'log (time, userid, course, ip, module, cmid, action, url, info) 1918 VALUES (' . "'$timenow', '$userid', '$courseid', '$REMOTE_ADDR', '$module', '$cm', '$action', '$url', '$info')"; 1919 1920 $result = $db->Execute($sql); 1921 1922 // MDL-11893, alert $CFG->supportemail if insert into log failed 1923 if (!$result && $CFG->supportemail) { 1924 $site = get_site(); 1925 $subject = 'Insert into log failed at your moodle site '.$site->fullname; 1926 $message = "Insert into log table failed at ". date('l dS \of F Y h:i:s A') .".\n It is possible that your disk is full.\n\n"; 1927 $message .= "The failed SQL is:\n\n" . $sql; 1928 1929 // email_to_user is not usable because email_to_user tries to write to the logs table, 1930 // and this will get caught in an infinite loop, if disk is full 1931 if (empty($CFG->noemailever)) { 1932 $lasttime = get_config('admin', 'lastloginserterrormail'); 1933 if(empty($lasttime) || time() - $lasttime > 60*60*24) { // limit to 1 email per day 1934 mail($CFG->supportemail, $subject, $message); 1935 set_config('lastloginserterrormail', time(), 'admin'); 1936 } 1937 } 1938 } 1939 1940 if (!$result) { 1941 debugging('Error: Could not insert a new entry to the Moodle log', DEBUG_ALL); 1942 } 1943 1944 } 1945 1946 /** 1947 * Store user last access times - called when use enters a course or site 1948 * 1949 * Note: we use ADOdb code directly in this function to save some CPU 1950 * cycles here and there. They are simple operations not needing any 1951 * of the postprocessing performed by dmllib.php 1952 * 1953 * @param int $courseid, empty means site 1954 * @return void 1955 */ 1956 function user_accesstime_log($courseid=0) { 1957 1958 global $USER, $CFG, $PERF, $db; 1959 1960 if (!isloggedin() or !empty($USER->realuser)) { 1961 // no access tracking 1962 return; 1963 } 1964 1965 if (empty($courseid)) { 1966 $courseid = SITEID; 1967 } 1968 1969 $timenow = time(); 1970 1971 /// Store site lastaccess time for the current user 1972 if ($timenow - $USER->lastaccess > LASTACCESS_UPDATE_SECS) { 1973 /// Update $USER->lastaccess for next checks 1974 $USER->lastaccess = $timenow; 1975 if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++;}; 1976 1977 $remoteaddr = getremoteaddr(); 1978 if ($db->Execute("UPDATE {$CFG->prefix}user 1979 SET lastip = '$remoteaddr', lastaccess = $timenow 1980 WHERE id = $USER->id")) { 1981 } else { 1982 debugging('Error: Could not update global user lastaccess information'); // Don't throw an error 1983 } 1984 /// Remove this record from record cache since it will change 1985 if (!empty($CFG->rcache)) { 1986 rcache_unset('user', $USER->id); 1987 } 1988 } 1989 1990 if ($courseid == SITEID) { 1991 /// no user_lastaccess for frontpage 1992 return; 1993 } 1994 1995 /// Store course lastaccess times for the current user 1996 if (empty($USER->currentcourseaccess[$courseid]) or ($timenow - $USER->currentcourseaccess[$courseid] > LASTACCESS_UPDATE_SECS)) { 1997 if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; }; 1998 1999 $exists = false; // To detect if the user_lastaccess record exists or no 2000 if ($rs = $db->Execute("SELECT timeaccess 2001 FROM {$CFG->prefix}user_lastaccess 2002 WHERE userid = $USER->id AND courseid = $courseid")) { 2003 if (!$rs->EOF) { 2004 $exists = true; 2005 $lastaccess = reset($rs->fields); 2006 if ($timenow - $lastaccess < LASTACCESS_UPDATE_SECS) { 2007 /// no need to update now, it was updated recently in concurrent login ;-) 2008 $rs->Close(); 2009 return; 2010 } 2011 } 2012 $rs->Close(); 2013 } 2014 2015 /// Update course lastaccess for next checks 2016 $USER->currentcourseaccess[$courseid] = $timenow; 2017 if (defined('MDL_PERFDB')) { global $PERF ; $PERF->dbqueries++; }; 2018 2019 if ($exists) { // user_lastaccess record exists, update it 2020 if ($db->Execute("UPDATE {$CFG->prefix}user_lastaccess 2021 SET timeaccess = $timenow 2022 WHERE userid = $USER->id AND courseid = $courseid")) { 2023 } else { 2024 debugging('Error: Could not update course user lastacess information'); // Don't throw an error 2025 } 2026 2027 } else { // user lastaccess record doesn't exist, insert it 2028 if ($db->Execute("INSERT INTO {$CFG->prefix}user_lastaccess 2029 (userid, courseid, timeaccess) 2030 VALUES ($USER->id, $courseid, $timenow)")) { 2031 } else { 2032 debugging('Error: Could not insert course user lastaccess information'); // Don't throw an error 2033 } 2034 } 2035 } 2036 } 2037 2038 /** 2039 * Select all log records based on SQL criteria 2040 * 2041 * @uses $CFG 2042 * @param string $select SQL select criteria 2043 * @param string $order SQL order by clause to sort the records returned 2044 * @param string $limitfrom ? 2045 * @param int $limitnum ? 2046 * @param int $totalcount Passed in by reference. 2047 * @return object 2048 * @todo Finish documenting this function 2049 */ 2050 function get_logs($select, $order='l.time DESC', $limitfrom='', $limitnum='', &$totalcount) { 2051 global $CFG; 2052 2053 if ($order) { 2054 $order = 'ORDER BY '. $order; 2055 } 2056 2057 $selectsql = $CFG->prefix .'log l LEFT JOIN '. $CFG->prefix .'user u ON l.userid = u.id '. ((strlen($select) > 0) ? 'WHERE '. $select : ''); 2058 $countsql = $CFG->prefix.'log l '.((strlen($select) > 0) ? ' WHERE '. $select : ''); 2059 2060 $totalcount = count_records_sql("SELECT COUNT(*) FROM $countsql"); 2061 2062 return get_records_sql('SELECT l.*, u.firstname, u.lastname, u.picture 2063 FROM '. $selectsql .' '. $order, $limitfrom, $limitnum) ; 2064 } 2065 2066 2067 /** 2068 * Select all log records for a given course and user 2069 * 2070 * @uses $CFG 2071 * @uses DAYSECS 2072 * @param int $userid The id of the user as found in the 'user' table. 2073 * @param int $courseid The id of the course as found in the 'course' table. 2074 * @param string $coursestart ? 2075 * @todo Finish documenting this function 2076 */ 2077 function get_logs_usercourse($userid, $courseid, $coursestart) { 2078 global $CFG; 2079 2080 if ($courseid) { 2081 $courseselect = ' AND course = \''. $courseid .'\' '; 2082 } else { 2083 $courseselect = ''; 2084 } 2085 2086 return get_records_sql("SELECT floor((time - $coursestart)/". DAYSECS .") as day, count(*) as num 2087 FROM {$CFG->prefix}log 2088 WHERE userid = '$userid' 2089 AND time > '$coursestart' $courseselect 2090 GROUP BY floor((time - $coursestart)/". DAYSECS .") "); 2091 } 2092 2093 /** 2094 * Select all log records for a given course, user, and day 2095 * 2096 * @uses $CFG 2097 * @uses HOURSECS 2098 * @param int $userid The id of the user as found in the 'user' table. 2099 * @param int $courseid The id of the course as found in the 'course' table. 2100 * @param string $daystart ? 2101 * @return object 2102 * @todo Finish documenting this function 2103 */ 2104 function get_logs_userday($userid, $courseid, $daystart) { 2105 global $CFG; 2106 2107 if ($courseid) { 2108 $courseselect = ' AND course = \''. $courseid .'\' '; 2109 } else { 2110 $courseselect = ''; 2111 } 2112 2113 return get_records_sql("SELECT floor((time - $daystart)/". HOURSECS .") as hour, count(*) as num 2114 FROM {$CFG->prefix}log 2115 WHERE userid = '$userid' 2116 AND time > '$daystart' $courseselect 2117 GROUP BY floor((time - $daystart)/". HOURSECS .") "); 2118 } 2119 2120 /** 2121 * Returns an object with counts of failed login attempts 2122 * 2123 * Returns information about failed login attempts. If the current user is 2124 * an admin, then two numbers are returned: the number of attempts and the 2125 * number of accounts. For non-admins, only the attempts on the given user 2126 * are shown. 2127 * 2128 * @param string $mode Either 'admin', 'teacher' or 'everybody' 2129 * @param string $username The username we are searching for 2130 * @param string $lastlogin The date from which we are searching 2131 * @return int 2132 */ 2133 function count_login_failures($mode, $username, $lastlogin) { 2134 2135 $select = 'module=\'login\' AND action=\'error\' AND time > '. $lastlogin; 2136 2137 if (has_capability('moodle/site:config', get_context_instance(CONTEXT_SYSTEM))) { // Return information about all accounts 2138 if ($count->attempts = count_records_select('log', $select)) { 2139 $count->accounts = count_records_select('log', $select, 'COUNT(DISTINCT info)'); 2140 return $count; 2141 } 2142 } else if ($mode == 'everybody' or ($mode == 'teacher' and isteacherinanycourse())) { 2143 if ($count->attempts = count_records_select('log', $select .' AND info = \''. $username .'\'')) { 2144 return $count; 2145 } 2146 } 2147 return NULL; 2148 } 2149 2150 2151 /// GENERAL HELPFUL THINGS /////////////////////////////////// 2152 2153 /** 2154 * Dump a given object's information in a PRE block. 2155 * 2156 * Mostly just used for debugging. 2157 * 2158 * @param mixed $object The data to be printed 2159 */ 2160 function print_object($object) { 2161 echo '<pre class="notifytiny">' . htmlspecialchars(print_r($object,true)) . '</pre>'; 2162 } 2163 2164 /* 2165 * Check whether a course is visible through its parents 2166 * path. 2167 * 2168 * Notes: 2169 * 2170 * - All we need from the course is ->category. _However_ 2171 * if the course object has a categorypath property, 2172 * we'll save a dbquery 2173 * 2174 * - If we return false, you'll still need to check if 2175 * the user can has the 'moodle/category:visibility' 2176 * capability... 2177 * 2178 * - Will generate 2 DB calls. 2179 * 2180 * - It does have a small local cache, however... 2181 * 2182 * - Do NOT call this over many courses as it'll generate 2183 * DB traffic. Instead, see what get_my_courses() does. 2184 * 2185 * @param mixed $object A course object 2186 * @return bool 2187 */ 2188 function course_parent_visible($course = null) { 2189 global $CFG; 2190 //return true; 2191 static $mycache; 2192 2193 if (!is_object($course)) { 2194 return true; 2195 } 2196 if (!empty($CFG->allowvisiblecoursesinhiddencategories)) { 2197 return true; 2198 } 2199 2200 if (!isset($mycache)) { 2201 $mycache = array(); 2202 } else { 2203 // cast to force assoc array 2204 $k = (string)$course->category; 2205 if (isset($mycache[$k])) { 2206 return $mycache[$k]; 2207 } 2208 } 2209 2210 if (isset($course->categorypath)) { 2211 $path = $course->categorypath; 2212 } else { 2213 $path = get_field('course_categories', 'path', 2214 'id', $course->category); 2215 } 2216 $catids = substr($path,1); // strip leading slash 2217 $catids = str_replace('/',',',$catids); 2218 2219 $sql = "SELECT MIN(visible) 2220 FROM {$CFG->prefix}course_categories 2221 WHERE id IN ($catids)"; 2222 $vis = get_field_sql($sql); 2223 2224 // cast to force assoc array 2225 $k = (string)$course->category; 2226 $mycache[$k] = $vis; 2227 2228 return $vis; 2229 } 2230 2231 /** 2232 * This function is the official hook inside XMLDB stuff to delegate its debug to one 2233 * external function. 2234 * 2235 * Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before 2236 * using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-) 2237 * 2238 * @param $message string contains the error message 2239 * @param $object object XMLDB object that fired the debug 2240 */ 2241 function xmldb_debug($message, $object) { 2242 2243 debugging($message, DEBUG_DEVELOPER); 2244 } 2245 2246 /** 2247 * true or false function to see if user can create any courses at all 2248 * @return bool 2249 */ 2250 function user_can_create_courses() { 2251 global $USER; 2252 // if user has course creation capability at any site or course cat, then return true; 2253 2254 if (has_capability('moodle/course:create', get_context_instance(CONTEXT_SYSTEM))) { 2255 return true; 2256 } else { 2257 return (bool)