Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 4.2.x will end 22 April 2024 (12 months).
- Bug fixes for security issues in 4.2.x will end 7 October 2024 (18 months).
- PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.1.x is supported too.
/lib/ -> datalib.php (source)
Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402] [Versions 402 and 403]
1 <?php 2 // This file is part of Moodle - http://moodle.org/ 3 // 4 // Moodle is free software: you can redistribute it and/or modify 5 // it under the terms of the GNU General Public License as published by 6 // the Free Software Foundation, either version 3 of the License, or 7 // (at your option) any later version. 8 // 9 // Moodle is distributed in the hope that it will be useful, 10 // but WITHOUT ANY WARRANTY; without even the implied warranty of 11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 // GNU General Public License for more details. 13 // 14 // You should have received a copy of the GNU General Public License 15 // along with Moodle. If not, see <http://www.gnu.org/licenses/>. 16 17 /** 18 * Library of functions for database manipulation. 19 * 20 * Other main libraries: 21 * - weblib.php - functions that produce web output 22 * - moodlelib.php - general-purpose Moodle functions 23 * 24 * @package core 25 * @copyright 1999 onwards Martin Dougiamas {@link http://moodle.com} 26 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 27 */ 28 29 defined('MOODLE_INTERNAL') || die(); 30 31 /** 32 * The maximum courses in a category 33 * MAX_COURSES_IN_CATEGORY * MAX_COURSE_CATEGORIES must not be more than max integer! 34 */ 35 define('MAX_COURSES_IN_CATEGORY', 10000); 36 37 /** 38 * The maximum number of course categories 39 * MAX_COURSES_IN_CATEGORY * MAX_COURSE_CATEGORIES must not be more than max integer! 40 */ 41 define('MAX_COURSE_CATEGORIES', 10000); 42 43 /** 44 * Number of seconds to wait before updating lastaccess information in DB. 45 * 46 * We allow overwrites from config.php, useful to ensure coherence in performance 47 * tests results. 48 * 49 * Note: For web service requests in the external_tokens field, we use a different constant 50 * webservice::TOKEN_LASTACCESS_UPDATE_SECS. 51 */ 52 if (!defined('LASTACCESS_UPDATE_SECS')) { 53 define('LASTACCESS_UPDATE_SECS', 60); 54 } 55 56 /** 57 * Returns $user object of the main admin user 58 * 59 * @static stdClass $mainadmin 60 * @return stdClass {@link $USER} record from DB, false if not found 61 */ 62 function get_admin() { 63 global $CFG, $DB; 64 65 static $mainadmin = null; 66 static $prevadmins = null; 67 68 if (empty($CFG->siteadmins)) { 69 // Should not happen on an ordinary site. 70 // It does however happen during unit tests. 71 return false; 72 } 73 74 if (isset($mainadmin) and $prevadmins === $CFG->siteadmins) { 75 return clone($mainadmin); 76 } 77 78 $mainadmin = null; 79 80 foreach (explode(',', $CFG->siteadmins) as $id) { 81 if ($user = $DB->get_record('user', array('id'=>$id, 'deleted'=>0))) { 82 $mainadmin = $user; 83 break; 84 } 85 } 86 87 if ($mainadmin) { 88 $prevadmins = $CFG->siteadmins; 89 return clone($mainadmin); 90 } else { 91 // this should not happen 92 return false; 93 } 94 } 95 96 /** 97 * Returns list of all admins, using 1 DB query 98 * 99 * @return array 100 */ 101 function get_admins() { 102 global $DB, $CFG; 103 104 if (empty($CFG->siteadmins)) { // Should not happen on an ordinary site 105 return array(); 106 } 107 108 $sql = "SELECT u.* 109 FROM {user} u 110 WHERE u.deleted = 0 AND u.id IN ($CFG->siteadmins)"; 111 112 // We want the same order as in $CFG->siteadmins. 113 $records = $DB->get_records_sql($sql); 114 $admins = array(); 115 foreach (explode(',', $CFG->siteadmins) as $id) { 116 $id = (int)$id; 117 if (!isset($records[$id])) { 118 // User does not exist, this should not happen. 119 continue; 120 } 121 $admins[$records[$id]->id] = $records[$id]; 122 } 123 124 return $admins; 125 } 126 127 /** 128 * Search through course users 129 * 130 * If $coursid specifies the site course then this function searches 131 * through all undeleted and confirmed users 132 * 133 * @global object 134 * @uses SITEID 135 * @uses SQL_PARAMS_NAMED 136 * @uses CONTEXT_COURSE 137 * @param int $courseid The course in question. 138 * @param int $groupid The group in question. 139 * @param string $searchtext The string to search for 140 * @param string $sort A field to sort by 141 * @param array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10 142 * @return array 143 */ 144 function search_users($courseid, $groupid, $searchtext, $sort='', array $exceptions=null) { 145 global $DB; 146 147 $fullname = $DB->sql_fullname('u.firstname', 'u.lastname'); 148 149 if (!empty($exceptions)) { 150 list($exceptions, $params) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex', false); 151 $except = "AND u.id $exceptions"; 152 } else { 153 $except = ""; 154 $params = array(); 155 } 156 157 if (!empty($sort)) { 158 $order = "ORDER BY $sort"; 159 } else { 160 $order = ""; 161 } 162 163 $select = "u.deleted = 0 AND u.confirmed = 1 AND (".$DB->sql_like($fullname, ':search1', false)." OR ".$DB->sql_like('u.email', ':search2', false).")"; 164 $params['search1'] = "%$searchtext%"; 165 $params['search2'] = "%$searchtext%"; 166 167 if (!$courseid or $courseid == SITEID) { 168 $sql = "SELECT u.id, u.firstname, u.lastname, u.email 169 FROM {user} u 170 WHERE $select 171 $except 172 $order"; 173 return $DB->get_records_sql($sql, $params); 174 175 } else { 176 if ($groupid) { 177 $sql = "SELECT u.id, u.firstname, u.lastname, u.email 178 FROM {user} u 179 JOIN {groups_members} gm ON gm.userid = u.id 180 WHERE $select AND gm.groupid = :groupid 181 $except 182 $order"; 183 $params['groupid'] = $groupid; 184 return $DB->get_records_sql($sql, $params); 185 186 } else { 187 $context = context_course::instance($courseid); 188 189 // We want to query both the current context and parent contexts. 190 list($relatedctxsql, $relatedctxparams) = $DB->get_in_or_equal($context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relatedctx'); 191 192 $sql = "SELECT u.id, u.firstname, u.lastname, u.email 193 FROM {user} u 194 JOIN {role_assignments} ra ON ra.userid = u.id 195 WHERE $select AND ra.contextid $relatedctxsql 196 $except 197 $order"; 198 $params = array_merge($params, $relatedctxparams); 199 return $DB->get_records_sql($sql, $params); 200 } 201 } 202 } 203 204 /** 205 * Returns SQL used to search through user table to find users (in a query 206 * which may also join and apply other conditions). 207 * 208 * You can combine this SQL with an existing query by adding 'AND $sql' to the 209 * WHERE clause of your query (where $sql is the first element in the array 210 * returned by this function), and merging in the $params array to the parameters 211 * of your query (where $params is the second element). Your query should use 212 * named parameters such as :param, rather than the question mark style. 213 * 214 * There are examples of basic usage in the unit test for this function. 215 * 216 * @param string $search the text to search for (empty string = find all) 217 * @param string $u the table alias for the user table in the query being 218 * built. May be ''. 219 * @param bool $searchanywhere If true (default), searches in the middle of 220 * names, otherwise only searches at start 221 * @param array $extrafields Array of extra user fields to include in search, must be prefixed with table alias if they are not in 222 * the user table. 223 * @param array $exclude Array of user ids to exclude (empty = don't exclude) 224 * @param array $includeonly If specified, only returns users that have ids 225 * incldued in this array (empty = don't restrict) 226 * @return array an array with two elements, a fragment of SQL to go in the 227 * where clause the query, and an associative array containing any required 228 * parameters (using named placeholders). 229 */ 230 function users_search_sql(string $search, string $u = 'u', bool $searchanywhere = true, array $extrafields = [], 231 array $exclude = null, array $includeonly = null): array { 232 global $DB, $CFG; 233 $params = array(); 234 $tests = array(); 235 236 if ($u) { 237 $u .= '.'; 238 } 239 240 // If we have a $search string, put a field LIKE '$search%' condition on each field. 241 if ($search) { 242 $conditions = array( 243 $DB->sql_fullname($u . 'firstname', $u . 'lastname'), 244 $conditions[] = $u . 'lastname' 245 ); 246 foreach ($extrafields as $field) { 247 // Add the table alias for the user table if the field doesn't already have an alias. 248 $conditions[] = strpos($field, '.') !== false ? $field : $u . $field; 249 } 250 if ($searchanywhere) { 251 $searchparam = '%' . $search . '%'; 252 } else { 253 $searchparam = $search . '%'; 254 } 255 $i = 0; 256 foreach ($conditions as $key => $condition) { 257 $conditions[$key] = $DB->sql_like($condition, ":con{$i}00", false, false); 258 $params["con{$i}00"] = $searchparam; 259 $i++; 260 } 261 $tests[] = '(' . implode(' OR ', $conditions) . ')'; 262 } 263 264 // Add some additional sensible conditions. 265 $tests[] = $u . "id <> :guestid"; 266 $params['guestid'] = $CFG->siteguest; 267 $tests[] = $u . 'deleted = 0'; 268 $tests[] = $u . 'confirmed = 1'; 269 270 // If we are being asked to exclude any users, do that. 271 if (!empty($exclude)) { 272 list($usertest, $userparams) = $DB->get_in_or_equal($exclude, SQL_PARAMS_NAMED, 'ex', false); 273 $tests[] = $u . 'id ' . $usertest; 274 $params = array_merge($params, $userparams); 275 } 276 277 // If we are validating a set list of userids, add an id IN (...) test. 278 if (!empty($includeonly)) { 279 list($usertest, $userparams) = $DB->get_in_or_equal($includeonly, SQL_PARAMS_NAMED, 'val'); 280 $tests[] = $u . 'id ' . $usertest; 281 $params = array_merge($params, $userparams); 282 } 283 284 // In case there are no tests, add one result (this makes it easier to combine 285 // this with an existing query as you can always add AND $sql). 286 if (empty($tests)) { 287 $tests[] = '1 = 1'; 288 } 289 290 // Combing the conditions and return. 291 return array(implode(' AND ', $tests), $params); 292 } 293 294 295 /** 296 * This function generates the standard ORDER BY clause for use when generating 297 * lists of users. If you don't have a reason to use a different order, then 298 * you should use this method to generate the order when displaying lists of users. 299 * 300 * If the optional $search parameter is passed, then exact matches to the search 301 * will be sorted first. For example, suppose you have two users 'Al Zebra' and 302 * 'Alan Aardvark'. The default sort is Alan, then Al. If, however, you search for 303 * 'Al', then Al will be listed first. (With two users, this is not a big deal, 304 * but with thousands of users, it is essential.) 305 * 306 * The list of fields scanned for exact matches are: 307 * - firstname 308 * - lastname 309 * - $DB->sql_fullname 310 * - those returned by \core_user\fields::get_identity_fields or those included in $customfieldmappings 311 * 312 * If named parameters are used (which is the default, and highly recommended), 313 * then the parameter names are like :usersortexactN, where N is an int. 314 * 315 * The simplest possible example use is: 316 * list($sort, $params) = users_order_by_sql(); 317 * $sql = 'SELECT * FROM {users} ORDER BY ' . $sort; 318 * 319 * A more complex example, showing that this sort can be combined with other sorts: 320 * list($sort, $sortparams) = users_order_by_sql('u'); 321 * $sql = "SELECT g.id AS groupid, gg.groupingid, u.id AS userid, u.firstname, u.lastname, u.idnumber, u.username 322 * FROM {groups} g 323 * LEFT JOIN {groupings_groups} gg ON g.id = gg.groupid 324 * LEFT JOIN {groups_members} gm ON g.id = gm.groupid 325 * LEFT JOIN {user} u ON gm.userid = u.id 326 * WHERE g.courseid = :courseid $groupwhere $groupingwhere 327 * ORDER BY g.name, $sort"; 328 * $params += $sortparams; 329 * 330 * An example showing the use of $search: 331 * list($sort, $sortparams) = users_order_by_sql('u', $search, $this->get_context()); 332 * $order = ' ORDER BY ' . $sort; 333 * $params += $sortparams; 334 * $availableusers = $DB->get_records_sql($fields . $sql . $order, $params, $page*$perpage, $perpage); 335 * 336 * @param string $usertablealias (optional) any table prefix for the {users} table. E.g. 'u'. 337 * @param string $search (optional) a current search string. If given, 338 * any exact matches to this string will be sorted first. 339 * @param context|null $context the context we are in. Used by \core_user\fields::get_identity_fields. 340 * Defaults to $PAGE->context. 341 * @param array $customfieldmappings associative array of mappings for custom fields returned by \core_user\fields::get_sql. 342 * @return array with two elements: 343 * string SQL fragment to use in the ORDER BY clause. For example, "firstname, lastname". 344 * array of parameters used in the SQL fragment. If $search is not given, this is guaranteed to be an empty array. 345 */ 346 function users_order_by_sql(string $usertablealias = '', string $search = null, context $context = null, 347 array $customfieldmappings = []) { 348 global $DB, $PAGE; 349 350 if ($usertablealias) { 351 $tableprefix = $usertablealias . '.'; 352 } else { 353 $tableprefix = ''; 354 } 355 356 $sort = "{$tableprefix}lastname, {$tableprefix}firstname, {$tableprefix}id"; 357 $params = array(); 358 359 if (!$search) { 360 return array($sort, $params); 361 } 362 363 if (!$context) { 364 $context = $PAGE->context; 365 } 366 367 $exactconditions = array(); 368 $paramkey = 'usersortexact1'; 369 370 $exactconditions[] = $DB->sql_fullname($tableprefix . 'firstname', $tableprefix . 'lastname') . 371 ' = :' . $paramkey; 372 $params[$paramkey] = $search; 373 $paramkey++; 374 375 if ($customfieldmappings) { 376 $fieldstocheck = array_merge([$tableprefix . 'firstname', $tableprefix . 'lastname'], array_values($customfieldmappings)); 377 } else { 378 $fieldstocheck = array_merge(['firstname', 'lastname'], \core_user\fields::get_identity_fields($context, false)); 379 $fieldstocheck = array_map(function($field) use ($tableprefix) { 380 return $tableprefix . $field; 381 }, $fieldstocheck); 382 } 383 384 foreach ($fieldstocheck as $key => $field) { 385 $exactconditions[] = 'LOWER(' . $field . ') = LOWER(:' . $paramkey . ')'; 386 $params[$paramkey] = $search; 387 $paramkey++; 388 } 389 390 $sort = 'CASE WHEN ' . implode(' OR ', $exactconditions) . 391 ' THEN 0 ELSE 1 END, ' . $sort; 392 393 return array($sort, $params); 394 } 395 396 /** 397 * Returns a subset of users 398 * 399 * @global object 400 * @uses DEBUG_DEVELOPER 401 * @uses SQL_PARAMS_NAMED 402 * @param bool $get If false then only a count of the records is returned 403 * @param string $search A simple string to search for 404 * @param bool $confirmed A switch to allow/disallow unconfirmed users 405 * @param array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10 406 * @param string $sort A SQL snippet for the sorting criteria to use 407 * @param string $firstinitial Users whose first name starts with $firstinitial 408 * @param string $lastinitial Users whose last name starts with $lastinitial 409 * @param string $page The page or records to return 410 * @param string $recordsperpage The number of records to return per page 411 * @param string $fields A comma separated list of fields to be returned from the chosen table. 412 * @return array|int|bool {@link $USER} records unless get is false in which case the integer count of the records found is returned. 413 * False is returned if an error is encountered. 414 */ 415 function get_users($get=true, $search='', $confirmed=false, array $exceptions=null, $sort='firstname ASC', 416 $firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*', $extraselect='', array $extraparams=null) { 417 global $DB, $CFG; 418 419 if ($get && !$recordsperpage) { 420 debugging('Call to get_users with $get = true no $recordsperpage limit. ' . 421 'On large installations, this will probably cause an out of memory error. ' . 422 'Please think again and change your code so that it does not try to ' . 423 'load so much data into memory.', DEBUG_DEVELOPER); 424 } 425 426 $fullname = $DB->sql_fullname(); 427 428 $select = " id <> :guestid AND deleted = 0"; 429 $params = array('guestid'=>$CFG->siteguest); 430 431 if (!empty($search)){ 432 $search = trim($search); 433 $select .= " AND (".$DB->sql_like($fullname, ':search1', false)." OR ".$DB->sql_like('email', ':search2', false)." OR username = :search3)"; 434 $params['search1'] = "%$search%"; 435 $params['search2'] = "%$search%"; 436 $params['search3'] = "$search"; 437 } 438 439 if ($confirmed) { 440 $select .= " AND confirmed = 1"; 441 } 442 443 if ($exceptions) { 444 list($exceptions, $eparams) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex', false); 445 $params = $params + $eparams; 446 $select .= " AND id $exceptions"; 447 } 448 449 if ($firstinitial) { 450 $select .= " AND ".$DB->sql_like('firstname', ':fni', false, false); 451 $params['fni'] = "$firstinitial%"; 452 } 453 if ($lastinitial) { 454 $select .= " AND ".$DB->sql_like('lastname', ':lni', false, false); 455 $params['lni'] = "$lastinitial%"; 456 } 457 458 if ($extraselect) { 459 $select .= " AND $extraselect"; 460 $params = $params + (array)$extraparams; 461 } 462 463 if ($get) { 464 return $DB->get_records_select('user', $select, $params, $sort, $fields, $page, $recordsperpage); 465 } else { 466 return $DB->count_records_select('user', $select, $params); 467 } 468 } 469 470 471 /** 472 * Return filtered (if provided) list of users in site, except guest and deleted users. 473 * 474 * @param string $sort An SQL field to sort by 475 * @param string $dir The sort direction ASC|DESC 476 * @param int $page The page or records to return 477 * @param int $recordsperpage The number of records to return per page 478 * @param string $search A simple string to search for 479 * @param string $firstinitial Users whose first name starts with $firstinitial 480 * @param string $lastinitial Users whose last name starts with $lastinitial 481 * @param string $extraselect An additional SQL select statement to append to the query 482 * @param array $extraparams Additional parameters to use for the above $extraselect 483 * @param stdClass $extracontext If specified, will include user 'extra fields' 484 * as appropriate for current user and given context 485 * @return array Array of {@link $USER} records 486 */ 487 function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=0, 488 $search='', $firstinitial='', $lastinitial='', $extraselect='', 489 array $extraparams=null, $extracontext = null) { 490 global $DB, $CFG; 491 492 $fullname = $DB->sql_fullname(); 493 494 $select = "deleted <> 1 AND u.id <> :guestid"; 495 $params = array('guestid' => $CFG->siteguest); 496 497 if (!empty($search)) { 498 $search = trim($search); 499 $select .= " AND (". $DB->sql_like($fullname, ':search1', false, false). 500 " OR ". $DB->sql_like('email', ':search2', false, false). 501 " OR username = :search3)"; 502 $params['search1'] = "%$search%"; 503 $params['search2'] = "%$search%"; 504 $params['search3'] = "$search"; 505 } 506 507 if ($firstinitial) { 508 $select .= " AND ". $DB->sql_like('firstname', ':fni', false, false); 509 $params['fni'] = "$firstinitial%"; 510 } 511 if ($lastinitial) { 512 $select .= " AND ". $DB->sql_like('lastname', ':lni', false, false); 513 $params['lni'] = "$lastinitial%"; 514 } 515 516 if ($extraselect) { 517 // The extra WHERE clause may refer to the 'id' column which can now be ambiguous because we 518 // changed the query to include joins, so replace any 'id' that is on its own (no alias) 519 // with 'u.id'. 520 $extraselect = preg_replace('~([ =]|^)id([ =]|$)~', '$1u.id$2', $extraselect); 521 $select .= " AND $extraselect"; 522 $params = $params + (array)$extraparams; 523 } 524 525 // If a context is specified, get extra user fields that the current user 526 // is supposed to see, otherwise just get the name fields. 527 $userfields = \core_user\fields::for_name(); 528 if ($extracontext) { 529 $userfields->with_identity($extracontext, true); 530 } 531 532 $userfields->excluding('id'); 533 $userfields->including('username', 'email', 'city', 'country', 'lastaccess', 'confirmed', 'mnethostid', 'suspended'); 534 ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams, 'mappings' => $mappings] = 535 (array)$userfields->get_sql('u', true); 536 537 if ($sort) { 538 $orderbymap = $mappings; 539 $orderbymap['default'] = 'lastaccess'; 540 $sort = get_safe_orderby($orderbymap, $sort, $dir); 541 } 542 543 // warning: will return UNCONFIRMED USERS 544 return $DB->get_records_sql("SELECT u.id $selects 545 FROM {user} u 546 $joins 547 WHERE $select 548 $sort", array_merge($params, $joinparams), $page, $recordsperpage); 549 550 } 551 552 553 /** 554 * Full list of users that have confirmed their accounts. 555 * 556 * @global object 557 * @return array of unconfirmed users 558 */ 559 function get_users_confirmed() { 560 global $DB, $CFG; 561 return $DB->get_records_sql("SELECT * 562 FROM {user} 563 WHERE confirmed = 1 AND deleted = 0 AND id <> ?", array($CFG->siteguest)); 564 } 565 566 567 /// OTHER SITE AND COURSE FUNCTIONS ///////////////////////////////////////////// 568 569 570 /** 571 * Returns $course object of the top-level site. 572 * 573 * @return object A {@link $COURSE} object for the site, exception if not found 574 */ 575 function get_site() { 576 global $SITE, $DB; 577 578 if (!empty($SITE->id)) { // We already have a global to use, so return that 579 return $SITE; 580 } 581 582 if ($course = $DB->get_record('course', array('category'=>0))) { 583 return $course; 584 } else { 585 // course table exists, but the site is not there, 586 // unfortunately there is no automatic way to recover 587 throw new moodle_exception('nosite', 'error'); 588 } 589 } 590 591 /** 592 * Gets a course object from database. If the course id corresponds to an 593 * already-loaded $COURSE or $SITE object, then the loaded object will be used, 594 * saving a database query. 595 * 596 * If it reuses an existing object, by default the object will be cloned. This 597 * means you can modify the object safely without affecting other code. 598 * 599 * @param int $courseid Course id 600 * @param bool $clone If true (default), makes a clone of the record 601 * @return stdClass A course object 602 * @throws dml_exception If not found in database 603 */ 604 function get_course($courseid, $clone = true) { 605 global $DB, $COURSE, $SITE; 606 if (!empty($COURSE->id) && $COURSE->id == $courseid) { 607 return $clone ? clone($COURSE) : $COURSE; 608 } else if (!empty($SITE->id) && $SITE->id == $courseid) { 609 return $clone ? clone($SITE) : $SITE; 610 } else { 611 return $DB->get_record('course', array('id' => $courseid), '*', MUST_EXIST); 612 } 613 } 614 615 /** 616 * Returns list of courses, for whole site, or category 617 * 618 * Returns list of courses, for whole site, or category 619 * Important: Using c.* for fields is extremely expensive because 620 * we are using distinct. You almost _NEVER_ need all the fields 621 * in such a large SELECT 622 * 623 * Consider using core_course_category::get_courses() 624 * or core_course_category::search_courses() instead since they use caching. 625 * 626 * @global object 627 * @global object 628 * @global object 629 * @uses CONTEXT_COURSE 630 * @param string|int $categoryid Either a category id or 'all' for everything 631 * @param string $sort A field and direction to sort by 632 * @param string $fields The additional fields to return (note that "id, category, visible" are always present) 633 * @return array Array of courses 634 */ 635 function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") { 636 637 global $USER, $CFG, $DB; 638 639 $params = array(); 640 641 if ($categoryid !== "all" && is_numeric($categoryid)) { 642 $categoryselect = "WHERE c.category = :catid"; 643 $params['catid'] = $categoryid; 644 } else { 645 $categoryselect = ""; 646 } 647 648 if (empty($sort)) { 649 $sortstatement = ""; 650 } else { 651 $sortstatement = "ORDER BY $sort"; 652 } 653 654 $visiblecourses = array(); 655 656 $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx'); 657 $ccjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel)"; 658 $params['contextlevel'] = CONTEXT_COURSE; 659 660 // The fields "id, category, visible" are required in the subsequent loop and must always be present. 661 if ($fields !== 'c.*') { 662 $fieldarray = array_merge( 663 // Split fields on comma + zero or more whitespace, merge with required fields. 664 preg_split('/,\s*/', $fields), [ 665 'c.id', 666 'c.category', 667 'c.visible', 668 ] 669 ); 670 $fields = implode(',', array_unique($fieldarray)); 671 } 672 673 $sql = "SELECT $fields $ccselect 674 FROM {course} c 675 $ccjoin 676 $categoryselect 677 $sortstatement"; 678 679 // pull out all course matching the cat 680 if ($courses = $DB->get_records_sql($sql, $params)) { 681 682 // loop throught them 683 foreach ($courses as $course) { 684 context_helper::preload_from_record($course); 685 if (core_course_category::can_view_course_info($course)) { 686 $visiblecourses [$course->id] = $course; 687 } 688 } 689 } 690 return $visiblecourses; 691 } 692 693 /** 694 * A list of courses that match a search 695 * 696 * @global object 697 * @global object 698 * @param array $searchterms An array of search criteria 699 * @param string $sort A field and direction to sort by 700 * @param int $page The page number to get 701 * @param int $recordsperpage The number of records per page 702 * @param int $totalcount Passed in by reference. 703 * @param array $requiredcapabilities Extra list of capabilities used to filter courses 704 * @param array $searchcond additional search conditions, for example ['c.enablecompletion = :p1'] 705 * @param array $params named parameters for additional search conditions, for example ['p1' => 1] 706 * @return stdClass[] {@link $COURSE} records 707 */ 708 function get_courses_search($searchterms, $sort, $page, $recordsperpage, &$totalcount, 709 $requiredcapabilities = array(), $searchcond = [], $params = []) { 710 global $CFG, $DB; 711 712 if ($DB->sql_regex_supported()) { 713 $REGEXP = $DB->sql_regex(true); 714 $NOTREGEXP = $DB->sql_regex(false); 715 } 716 717 $i = 0; 718 719 // Thanks Oracle for your non-ansi concat and type limits in coalesce. MDL-29912 720 if ($DB->get_dbfamily() == 'oracle') { 721 $concat = "(c.summary|| ' ' || c.fullname || ' ' || c.idnumber || ' ' || c.shortname)"; 722 } else { 723 $concat = $DB->sql_concat("COALESCE(c.summary, '')", "' '", 'c.fullname', "' '", 'c.idnumber', "' '", 'c.shortname'); 724 } 725 726 foreach ($searchterms as $searchterm) { 727 $i++; 728 729 $NOT = false; /// Initially we aren't going to perform NOT LIKE searches, only MSSQL and Oracle 730 /// will use it to simulate the "-" operator with LIKE clause 731 732 /// Under Oracle and MSSQL, trim the + and - operators and perform 733 /// simpler LIKE (or NOT LIKE) queries 734 if (!$DB->sql_regex_supported()) { 735 if (substr($searchterm, 0, 1) == '-') { 736 $NOT = true; 737 } 738 $searchterm = trim($searchterm, '+-'); 739 } 740 741 // TODO: +- may not work for non latin languages 742 743 if (substr($searchterm,0,1) == '+') { 744 $searchterm = trim($searchterm, '+-'); 745 $searchterm = preg_quote($searchterm, '|'); 746 $searchcond[] = "$concat $REGEXP :ss$i"; 747 $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)"; 748 749 } else if ((substr($searchterm,0,1) == "-") && (core_text::strlen($searchterm) > 1)) { 750 $searchterm = trim($searchterm, '+-'); 751 $searchterm = preg_quote($searchterm, '|'); 752 $searchcond[] = "$concat $NOTREGEXP :ss$i"; 753 $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)"; 754 755 } else { 756 $searchcond[] = $DB->sql_like($concat,":ss$i", false, true, $NOT); 757 $params['ss'.$i] = "%$searchterm%"; 758 } 759 } 760 761 if (empty($searchcond)) { 762 $searchcond = array('1 = 1'); 763 } 764 765 $searchcond = implode(" AND ", $searchcond); 766 767 $courses = array(); 768 $c = 0; // counts how many visible courses we've seen 769 770 // Tiki pagination 771 $limitfrom = $page * $recordsperpage; 772 $limitto = $limitfrom + $recordsperpage; 773 774 $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx'); 775 $ccjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel)"; 776 $params['contextlevel'] = CONTEXT_COURSE; 777 778 $sql = "SELECT c.* $ccselect 779 FROM {course} c 780 $ccjoin 781 WHERE $searchcond AND c.id <> ".SITEID." 782 ORDER BY $sort"; 783 784 $mycourses = enrol_get_my_courses(); 785 $rs = $DB->get_recordset_sql($sql, $params); 786 foreach($rs as $course) { 787 // Preload contexts only for hidden courses or courses we need to return. 788 context_helper::preload_from_record($course); 789 $coursecontext = context_course::instance($course->id); 790 if (!array_key_exists($course->id, $mycourses) && !core_course_category::can_view_course_info($course)) { 791 continue; 792 } 793 if (!empty($requiredcapabilities)) { 794 if (!has_all_capabilities($requiredcapabilities, $coursecontext)) { 795 continue; 796 } 797 } 798 // Don't exit this loop till the end 799 // we need to count all the visible courses 800 // to update $totalcount 801 if ($c >= $limitfrom && $c < $limitto) { 802 $courses[$course->id] = $course; 803 } 804 $c++; 805 } 806 $rs->close(); 807 808 // our caller expects 2 bits of data - our return 809 // array, and an updated $totalcount 810 $totalcount = $c; 811 return $courses; 812 } 813 814 /** 815 * Fixes course category and course sortorder, also verifies category and course parents and paths. 816 * (circular references are not fixed) 817 * 818 * @global object 819 * @global object 820 * @uses MAX_COURSE_CATEGORIES 821 * @uses SITEID 822 * @uses CONTEXT_COURSE 823 * @return void 824 */ 825 function fix_course_sortorder() { 826 global $DB, $SITE; 827 828 //WARNING: this is PHP5 only code! 829 830 // if there are any changes made to courses or categories we will trigger 831 // the cache events to purge all cached courses/categories data 832 $cacheevents = array(); 833 834 if ($unsorted = $DB->get_records('course_categories', array('sortorder'=>0))) { 835 //move all categories that are not sorted yet to the end 836 $DB->set_field('course_categories', 'sortorder', 837 get_max_courses_in_category() * MAX_COURSE_CATEGORIES, array('sortorder' => 0)); 838 $cacheevents['changesincoursecat'] = true; 839 } 840 841 $allcats = $DB->get_records('course_categories', null, 'sortorder, id', 'id, sortorder, parent, depth, path'); 842 $topcats = array(); 843 $brokencats = array(); 844 foreach ($allcats as $cat) { 845 $sortorder = (int)$cat->sortorder; 846 if (!$cat->parent) { 847 while(isset($topcats[$sortorder])) { 848 $sortorder++; 849 } 850 $topcats[$sortorder] = $cat; 851 continue; 852 } 853 if (!isset($allcats[$cat->parent])) { 854 $brokencats[] = $cat; 855 continue; 856 } 857 if (!isset($allcats[$cat->parent]->children)) { 858 $allcats[$cat->parent]->children = array(); 859 } 860 while(isset($allcats[$cat->parent]->children[$sortorder])) { 861 $sortorder++; 862 } 863 $allcats[$cat->parent]->children[$sortorder] = $cat; 864 } 865 unset($allcats); 866 867 // add broken cats to category tree 868 if ($brokencats) { 869 $defaultcat = reset($topcats); 870 foreach ($brokencats as $cat) { 871 $topcats[] = $cat; 872 } 873 } 874 875 // now walk recursively the tree and fix any problems found 876 $sortorder = 0; 877 $fixcontexts = array(); 878 if (_fix_course_cats($topcats, $sortorder, 0, 0, '', $fixcontexts)) { 879 $cacheevents['changesincoursecat'] = true; 880 } 881 882 // detect if there are "multiple" frontpage courses and fix them if needed 883 $frontcourses = $DB->get_records('course', array('category'=>0), 'id'); 884 if (count($frontcourses) > 1) { 885 if (isset($frontcourses[SITEID])) { 886 $frontcourse = $frontcourses[SITEID]; 887 unset($frontcourses[SITEID]); 888 } else { 889 $frontcourse = array_shift($frontcourses); 890 } 891 $defaultcat = reset($topcats); 892 foreach ($frontcourses as $course) { 893 $DB->set_field('course', 'category', $defaultcat->id, array('id'=>$course->id)); 894 $context = context_course::instance($course->id); 895 $fixcontexts[$context->id] = $context; 896 $cacheevents['changesincourse'] = true; 897 } 898 unset($frontcourses); 899 } else { 900 $frontcourse = reset($frontcourses); 901 } 902 903 // now fix the paths and depths in context table if needed 904 if ($fixcontexts) { 905 foreach ($fixcontexts as $fixcontext) { 906 $fixcontext->reset_paths(false); 907 } 908 context_helper::build_all_paths(false); 909 unset($fixcontexts); 910 $cacheevents['changesincourse'] = true; 911 $cacheevents['changesincoursecat'] = true; 912 } 913 914 // release memory 915 unset($topcats); 916 unset($brokencats); 917 unset($fixcontexts); 918 919 // fix frontpage course sortorder 920 if ($frontcourse->sortorder != 1) { 921 $DB->set_field('course', 'sortorder', 1, array('id'=>$frontcourse->id)); 922 $cacheevents['changesincourse'] = true; 923 } 924 925 // now fix the course counts in category records if needed 926 $sql = "SELECT cc.id, cc.coursecount, COUNT(c.id) AS newcount 927 FROM {course_categories} cc 928 LEFT JOIN {course} c ON c.category = cc.id 929 GROUP BY cc.id, cc.coursecount 930 HAVING cc.coursecount <> COUNT(c.id)"; 931 932 if ($updatecounts = $DB->get_records_sql($sql)) { 933 // categories with more courses than MAX_COURSES_IN_CATEGORY 934 $categories = array(); 935 foreach ($updatecounts as $cat) { 936 $cat->coursecount = $cat->newcount; 937 if ($cat->coursecount >= get_max_courses_in_category()) { 938 $categories[] = $cat->id; 939 } 940 unset($cat->newcount); 941 $DB->update_record_raw('course_categories', $cat, true); 942 } 943 if (!empty($categories)) { 944 $str = implode(', ', $categories); 945 debugging("The number of courses (category id: $str) has reached max number of courses " . 946 "in a category (" . get_max_courses_in_category() . "). It will cause a sorting performance issue. " . 947 "Please set higher value for \$CFG->maxcoursesincategory in config.php. " . 948 "Please also make sure \$CFG->maxcoursesincategory * MAX_COURSE_CATEGORIES less than max integer. " . 949 "See tracker issues: MDL-25669 and MDL-69573", DEBUG_DEVELOPER); 950 } 951 $cacheevents['changesincoursecat'] = true; 952 } 953 954 // now make sure that sortorders in course table are withing the category sortorder ranges 955 $sql = "SELECT DISTINCT cc.id, cc.sortorder 956 FROM {course_categories} cc 957 JOIN {course} c ON c.category = cc.id 958 WHERE c.sortorder < cc.sortorder OR c.sortorder > cc.sortorder + " . get_max_courses_in_category(); 959 960 if ($fixcategories = $DB->get_records_sql($sql)) { 961 //fix the course sortorder ranges 962 foreach ($fixcategories as $cat) { 963 $sql = "UPDATE {course} 964 SET sortorder = ".$DB->sql_modulo('sortorder', get_max_courses_in_category())." + ? 965 WHERE category = ?"; 966 $DB->execute($sql, array($cat->sortorder, $cat->id)); 967 } 968 $cacheevents['changesincoursecat'] = true; 969 } 970 unset($fixcategories); 971 972 // categories having courses with sortorder duplicates or having gaps in sortorder 973 $sql = "SELECT DISTINCT c1.category AS id , cc.sortorder 974 FROM {course} c1 975 JOIN {course} c2 ON c1.sortorder = c2.sortorder 976 JOIN {course_categories} cc ON (c1.category = cc.id) 977 WHERE c1.id <> c2.id"; 978 $fixcategories = $DB->get_records_sql($sql); 979 980 $sql = "SELECT cc.id, cc.sortorder, cc.coursecount, MAX(c.sortorder) AS maxsort, MIN(c.sortorder) AS minsort 981 FROM {course_categories} cc 982 JOIN {course} c ON c.category = cc.id 983 GROUP BY cc.id, cc.sortorder, cc.coursecount 984 HAVING (MAX(c.sortorder) <> cc.sortorder + cc.coursecount) OR (MIN(c.sortorder) <> cc.sortorder + 1)"; 985 $gapcategories = $DB->get_records_sql($sql); 986 987 foreach ($gapcategories as $cat) { 988 if (isset($fixcategories[$cat->id])) { 989 // duplicates detected already 990 991 } else if ($cat->minsort == $cat->sortorder and $cat->maxsort == $cat->sortorder + $cat->coursecount - 1) { 992 // easy - new course inserted with sortorder 0, the rest is ok 993 $sql = "UPDATE {course} 994 SET sortorder = sortorder + 1 995 WHERE category = ?"; 996 $DB->execute($sql, array($cat->id)); 997 998 } else { 999 // it needs full resorting 1000 $fixcategories[$cat->id] = $cat; 1001 } 1002 $cacheevents['changesincourse'] = true; 1003 } 1004 unset($gapcategories); 1005 1006 // fix course sortorders in problematic categories only 1007 foreach ($fixcategories as $cat) { 1008 $i = 1; 1009 $courses = $DB->get_records('course', array('category'=>$cat->id), 'sortorder ASC, id DESC', 'id, sortorder'); 1010 foreach ($courses as $course) { 1011 if ($course->sortorder != $cat->sortorder + $i) { 1012 $course->sortorder = $cat->sortorder + $i; 1013 $DB->update_record_raw('course', $course, true); 1014 $cacheevents['changesincourse'] = true; 1015 } 1016 $i++; 1017 } 1018 } 1019 1020 // advise all caches that need to be rebuilt 1021 foreach (array_keys($cacheevents) as $event) { 1022 cache_helper::purge_by_event($event); 1023 } 1024 } 1025 1026 /** 1027 * Internal recursive category verification function, do not use directly! 1028 * 1029 * @todo Document the arguments of this function better 1030 * 1031 * @global object 1032 * @uses CONTEXT_COURSECAT 1033 * @param array $children 1034 * @param int $sortorder 1035 * @param string $parent 1036 * @param int $depth 1037 * @param string $path 1038 * @param array $fixcontexts 1039 * @return bool if changes were made 1040 */ 1041 function _fix_course_cats($children, &$sortorder, $parent, $depth, $path, &$fixcontexts) { 1042 global $DB; 1043 1044 $depth++; 1045 $changesmade = false; 1046 1047 foreach ($children as $cat) { 1048 $sortorder = $sortorder + get_max_courses_in_category(); 1049 $update = false; 1050 if ($parent != $cat->parent or $depth != $cat->depth or $path.'/'.$cat->id != $cat->path) { 1051 $cat->parent = $parent; 1052 $cat->depth = $depth; 1053 $cat->path = $path.'/'.$cat->id; 1054 $update = true; 1055 1056 // make sure context caches are rebuild and dirty contexts marked 1057 $context = context_coursecat::instance($cat->id); 1058 $fixcontexts[$context->id] = $context; 1059 } 1060 if ($cat->sortorder != $sortorder) { 1061 $cat->sortorder = $sortorder; 1062 $update = true; 1063 } 1064 if ($update) { 1065 $DB->update_record('course_categories', $cat, true); 1066 $changesmade = true; 1067 } 1068 if (isset($cat->children)) { 1069 if (_fix_course_cats($cat->children, $sortorder, $cat->id, $cat->depth, $cat->path, $fixcontexts)) { 1070 $changesmade = true; 1071 } 1072 } 1073 } 1074 return $changesmade; 1075 } 1076 1077 /** 1078 * List of remote courses that a user has access to via MNET. 1079 * Works only on the IDP 1080 * 1081 * @global object 1082 * @global object 1083 * @param int @userid The user id to get remote courses for 1084 * @return array Array of {@link $COURSE} of course objects 1085 */ 1086 function get_my_remotecourses($userid=0) { 1087 global $DB, $USER; 1088 1089 if (empty($userid)) { 1090 $userid = $USER->id; 1091 } 1092 1093 // we can not use SELECT DISTINCT + text field (summary) because of MS SQL and Oracle, subselect used therefore 1094 $sql = "SELECT c.id, c.remoteid, c.shortname, c.fullname, 1095 c.hostid, c.summary, c.summaryformat, c.categoryname AS cat_name, 1096 h.name AS hostname 1097 FROM {mnetservice_enrol_courses} c 1098 JOIN (SELECT DISTINCT hostid, remotecourseid 1099 FROM {mnetservice_enrol_enrolments} 1100 WHERE userid = ? 1101 ) e ON (e.hostid = c.hostid AND e.remotecourseid = c.remoteid) 1102 JOIN {mnet_host} h ON h.id = c.hostid"; 1103 1104 return $DB->get_records_sql($sql, array($userid)); 1105 } 1106 1107 /** 1108 * List of remote hosts that a user has access to via MNET. 1109 * Works on the SP 1110 * 1111 * @global object 1112 * @global object 1113 * @return array|bool Array of host objects or false 1114 */ 1115 function get_my_remotehosts() { 1116 global $CFG, $USER; 1117 1118 if ($USER->mnethostid == $CFG->mnet_localhost_id) { 1119 return false; // Return nothing on the IDP 1120 } 1121 if (!empty($USER->mnet_foreign_host_array) && is_array($USER->mnet_foreign_host_array)) { 1122 return $USER->mnet_foreign_host_array; 1123 } 1124 return false; 1125 } 1126 1127 1128 /** 1129 * Returns a menu of all available scales from the site as well as the given course 1130 * 1131 * @global object 1132 * @param int $courseid The id of the course as found in the 'course' table. 1133 * @return array 1134 */ 1135 function get_scales_menu($courseid=0) { 1136 global $DB; 1137 1138 $sql = "SELECT id, name, courseid 1139 FROM {scale} 1140 WHERE courseid = 0 or courseid = ? 1141 ORDER BY courseid ASC, name ASC"; 1142 $params = array($courseid); 1143 $scales = array(); 1144 $results = $DB->get_records_sql($sql, $params); 1145 foreach ($results as $index => $record) { 1146 $context = empty($record->courseid) ? context_system::instance() : context_course::instance($record->courseid); 1147 $scales[$index] = format_string($record->name, false, ["context" => $context]); 1148 } 1149 // Format: [id => 'scale name']. 1150 return $scales; 1151 } 1152 1153 /** 1154 * Increment standard revision field. 1155 * 1156 * The revision are based on current time and are incrementing. 1157 * There is a protection for runaway revisions, it may not go further than 1158 * one hour into future. 1159 * 1160 * The field has to be XMLDB_TYPE_INTEGER with size 10. 1161 * 1162 * @param string $table 1163 * @param string $field name of the field containing revision 1164 * @param string $select use empty string when updating all records 1165 * @param array $params optional select parameters 1166 */ 1167 function increment_revision_number($table, $field, $select, array $params = null) { 1168 global $DB; 1169 1170 $now = time(); 1171 $sql = "UPDATE {{$table}} 1172 SET $field = (CASE 1173 WHEN $field IS NULL THEN $now 1174 WHEN $field < $now THEN $now 1175 WHEN $field > $now + 3600 THEN $now 1176 ELSE $field + 1 END)"; 1177 if ($select) { 1178 $sql = $sql . " WHERE $select"; 1179 } 1180 $DB->execute($sql, $params); 1181 } 1182 1183 1184 /// MODULE FUNCTIONS ///////////////////////////////////////////////// 1185 1186 /** 1187 * Just gets a raw list of all modules in a course 1188 * 1189 * @global object 1190 * @param int $courseid The id of the course as found in the 'course' table. 1191 * @return array 1192 */ 1193 function get_course_mods($courseid) { 1194 global $DB; 1195 1196 if (empty($courseid)) { 1197 return false; // avoid warnings 1198 } 1199 1200 return $DB->get_records_sql("SELECT cm.*, m.name as modname 1201 FROM {modules} m, {course_modules} cm 1202 WHERE cm.course = ? AND cm.module = m.id AND m.visible = 1", 1203 array($courseid)); // no disabled mods 1204 } 1205 1206 1207 /** 1208 * Given an id of a course module, finds the coursemodule description 1209 * 1210 * Please note that this function performs 1-2 DB queries. When possible use cached 1211 * course modinfo. For example get_fast_modinfo($courseorid)->get_cm($cmid) 1212 * See also {@link cm_info::get_course_module_record()} 1213 * 1214 * @global object 1215 * @param string $modulename name of module type, eg. resource, assignment,... (optional, slower and less safe if not specified) 1216 * @param int $cmid course module id (id in course_modules table) 1217 * @param int $courseid optional course id for extra validation 1218 * @param bool $sectionnum include relative section number (0,1,2 ...) 1219 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1220 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1221 * MUST_EXIST means throw exception if no record or multiple records found 1222 * @return stdClass 1223 */ 1224 function get_coursemodule_from_id($modulename, $cmid, $courseid=0, $sectionnum=false, $strictness=IGNORE_MISSING) { 1225 global $DB; 1226 1227 $params = array('cmid'=>$cmid); 1228 1229 if (!$modulename) { 1230 if (!$modulename = $DB->get_field_sql("SELECT md.name 1231 FROM {modules} md 1232 JOIN {course_modules} cm ON cm.module = md.id 1233 WHERE cm.id = :cmid", $params, $strictness)) { 1234 return false; 1235 } 1236 } else { 1237 if (!core_component::is_valid_plugin_name('mod', $modulename)) { 1238 throw new coding_exception('Invalid modulename parameter'); 1239 } 1240 } 1241 1242 $params['modulename'] = $modulename; 1243 1244 $courseselect = ""; 1245 $sectionfield = ""; 1246 $sectionjoin = ""; 1247 1248 if ($courseid) { 1249 $courseselect = "AND cm.course = :courseid"; 1250 $params['courseid'] = $courseid; 1251 } 1252 1253 if ($sectionnum) { 1254 $sectionfield = ", cw.section AS sectionnum"; 1255 $sectionjoin = "LEFT JOIN {course_sections} cw ON cw.id = cm.section"; 1256 } 1257 1258 $sql = "SELECT cm.*, m.name, md.name AS modname $sectionfield 1259 FROM {course_modules} cm 1260 JOIN {modules} md ON md.id = cm.module 1261 JOIN {".$modulename."} m ON m.id = cm.instance 1262 $sectionjoin 1263 WHERE cm.id = :cmid AND md.name = :modulename 1264 $courseselect"; 1265 1266 return $DB->get_record_sql($sql, $params, $strictness); 1267 } 1268 1269 /** 1270 * Given an instance number of a module, finds the coursemodule description 1271 * 1272 * Please note that this function performs DB query. When possible use cached course 1273 * modinfo. For example get_fast_modinfo($courseorid)->instances[$modulename][$instance] 1274 * See also {@link cm_info::get_course_module_record()} 1275 * 1276 * @global object 1277 * @param string $modulename name of module type, eg. resource, assignment,... 1278 * @param int $instance module instance number (id in resource, assignment etc. table) 1279 * @param int $courseid optional course id for extra validation 1280 * @param bool $sectionnum include relative section number (0,1,2 ...) 1281 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1282 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1283 * MUST_EXIST means throw exception if no record or multiple records found 1284 * @return stdClass 1285 */ 1286 function get_coursemodule_from_instance($modulename, $instance, $courseid=0, $sectionnum=false, $strictness=IGNORE_MISSING) { 1287 global $DB; 1288 1289 if (!core_component::is_valid_plugin_name('mod', $modulename)) { 1290 throw new coding_exception('Invalid modulename parameter'); 1291 } 1292 1293 $params = array('instance'=>$instance, 'modulename'=>$modulename); 1294 1295 $courseselect = ""; 1296 $sectionfield = ""; 1297 $sectionjoin = ""; 1298 1299 if ($courseid) { 1300 $courseselect = "AND cm.course = :courseid"; 1301 $params['courseid'] = $courseid; 1302 } 1303 1304 if ($sectionnum) { 1305 $sectionfield = ", cw.section AS sectionnum"; 1306 $sectionjoin = "LEFT JOIN {course_sections} cw ON cw.id = cm.section"; 1307 } 1308 1309 $sql = "SELECT cm.*, m.name, md.name AS modname $sectionfield 1310 FROM {course_modules} cm 1311 JOIN {modules} md ON md.id = cm.module 1312 JOIN {".$modulename."} m ON m.id = cm.instance 1313 $sectionjoin 1314 WHERE m.id = :instance AND md.name = :modulename 1315 $courseselect"; 1316 1317 return $DB->get_record_sql($sql, $params, $strictness); 1318 } 1319 1320 /** 1321 * Returns all course modules of given activity in course 1322 * 1323 * @param string $modulename The module name (forum, quiz, etc.) 1324 * @param int $courseid The course id to get modules for 1325 * @param string $extrafields extra fields starting with m. 1326 * @return array Array of results 1327 */ 1328 function get_coursemodules_in_course($modulename, $courseid, $extrafields='') { 1329 global $DB; 1330 1331 if (!core_component::is_valid_plugin_name('mod', $modulename)) { 1332 throw new coding_exception('Invalid modulename parameter'); 1333 } 1334 1335 if (!empty($extrafields)) { 1336 $extrafields = ", $extrafields"; 1337 } 1338 $params = array(); 1339 $params['courseid'] = $courseid; 1340 $params['modulename'] = $modulename; 1341 1342 1343 return $DB->get_records_sql("SELECT cm.*, m.name, md.name as modname $extrafields 1344 FROM {course_modules} cm, {modules} md, {".$modulename."} m 1345 WHERE cm.course = :courseid AND 1346 cm.instance = m.id AND 1347 md.name = :modulename AND 1348 md.id = cm.module", $params); 1349 } 1350 1351 /** 1352 * Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined 1353 * 1354 * Returns an array of all the active instances of a particular 1355 * module in given courses, sorted in the order they are defined 1356 * in the course. Returns an empty array on any errors. 1357 * 1358 * The returned objects includle the columns cw.section, cm.visible, 1359 * cm.groupmode, cm.groupingid and cm.lang and are indexed by cm.id. 1360 * 1361 * @global object 1362 * @global object 1363 * @param string $modulename The name of the module to get instances for 1364 * @param array $courses an array of course objects. 1365 * @param int $userid 1366 * @param int $includeinvisible 1367 * @return array of module instance objects, including some extra fields from the course_modules 1368 * and course_sections tables, or an empty array if an error occurred. 1369 */ 1370 function get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false) { 1371 global $CFG, $DB; 1372 1373 if (!core_component::is_valid_plugin_name('mod', $modulename)) { 1374 throw new coding_exception('Invalid modulename parameter'); 1375 } 1376 1377 $outputarray = array(); 1378 1379 if (empty($courses) || !is_array($courses) || count($courses) == 0) { 1380 return $outputarray; 1381 } 1382 1383 list($coursessql, $params) = $DB->get_in_or_equal(array_keys($courses), SQL_PARAMS_NAMED, 'c0'); 1384 $params['modulename'] = $modulename; 1385 1386 if (!$rawmods = $DB->get_records_sql("SELECT cm.id AS coursemodule, m.*, cw.section, cm.visible AS visible, 1387 cm.groupmode, cm.groupingid, cm.lang 1388 FROM {course_modules} cm, {course_sections} cw, {modules} md, 1389 {".$modulename."} m 1390 WHERE cm.course $coursessql AND 1391 cm.instance = m.id AND 1392 cm.section = cw.id AND 1393 md.name = :modulename AND 1394 md.id = cm.module", $params)) { 1395 return $outputarray; 1396 } 1397 1398 foreach ($courses as $course) { 1399 $modinfo = get_fast_modinfo($course, $userid); 1400 1401 if (empty($modinfo->instances[$modulename])) { 1402 continue; 1403 } 1404 1405 foreach ($modinfo->instances[$modulename] as $cm) { 1406 if (!$includeinvisible and !$cm->uservisible) { 1407 continue; 1408 } 1409 if (!isset($rawmods[$cm->id])) { 1410 continue; 1411 } 1412 $instance = $rawmods[$cm->id]; 1413 if (!empty($cm->extra)) { 1414 $instance->extra = $cm->extra; 1415 } 1416 $outputarray[] = $instance; 1417 } 1418 } 1419 1420 return $outputarray; 1421 } 1422 1423 /** 1424 * Returns an array of all the active instances of a particular module in a given course, 1425 * sorted in the order they are defined. 1426 * 1427 * Returns an array of all the active instances of a particular 1428 * module in a given course, sorted in the order they are defined 1429 * in the course. Returns an empty array on any errors. 1430 * 1431 * The returned objects includle the columns cw.section, cm.visible, 1432 * cm.groupmode, and cm.groupingid, and are indexed by cm.id. 1433 * 1434 * Simply calls {@link all_instances_in_courses()} with a single provided course 1435 * 1436 * @param string $modulename The name of the module to get instances for 1437 * @param object $course The course obect. 1438 * @return array of module instance objects, including some extra fields from the course_modules 1439 * and course_sections tables, or an empty array if an error occurred. 1440 * @param int $userid 1441 * @param int $includeinvisible 1442 */ 1443 function get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false) { 1444 return get_all_instances_in_courses($modulename, array($course->id => $course), $userid, $includeinvisible); 1445 } 1446 1447 1448 /** 1449 * Determine whether a module instance is visible within a course 1450 * 1451 * Given a valid module object with info about the id and course, 1452 * and the module's type (eg "forum") returns whether the object 1453 * is visible or not according to the 'eye' icon only. 1454 * 1455 * NOTE: This does NOT take into account visibility to a particular user. 1456 * To get visibility access for a specific user, use get_fast_modinfo, get a 1457 * cm_info object from this, and check the ->uservisible property; or use 1458 * the \core_availability\info_module::is_user_visible() static function. 1459 * 1460 * @global object 1461 1462 * @param $moduletype Name of the module eg 'forum' 1463 * @param $module Object which is the instance of the module 1464 * @return bool Success 1465 */ 1466 function instance_is_visible($moduletype, $module) { 1467 global $DB; 1468 1469 if (!empty($module->id)) { 1470 $params = array('courseid'=>$module->course, 'moduletype'=>$moduletype, 'moduleid'=>$module->id); 1471 if ($records = $DB->get_records_sql("SELECT cm.instance, cm.visible, cm.groupingid, cm.id, cm.course 1472 FROM {course_modules} cm, {modules} m 1473 WHERE cm.course = :courseid AND 1474 cm.module = m.id AND 1475 m.name = :moduletype AND 1476 cm.instance = :moduleid", $params)) { 1477 1478 foreach ($records as $record) { // there should only be one - use the first one 1479 return $record->visible; 1480 } 1481 } 1482 } 1483 return true; // visible by default! 1484 } 1485 1486 1487 /// LOG FUNCTIONS ///////////////////////////////////////////////////// 1488 1489 /** 1490 * Get instance of log manager. 1491 * 1492 * @param bool $forcereload 1493 * @return \core\log\manager 1494 */ 1495 function get_log_manager($forcereload = false) { 1496 /** @var \core\log\manager $singleton */ 1497 static $singleton = null; 1498 1499 if ($forcereload and isset($singleton)) { 1500 $singleton->dispose(); 1501 $singleton = null; 1502 } 1503 1504 if (isset($singleton)) { 1505 return $singleton; 1506 } 1507 1508 $classname = '\tool_log\log\manager'; 1509 if (defined('LOG_MANAGER_CLASS')) { 1510 $classname = LOG_MANAGER_CLASS; 1511 } 1512 1513 if (!class_exists($classname)) { 1514 if (!empty($classname)) { 1515 debugging("Cannot find log manager class '$classname'.", DEBUG_DEVELOPER); 1516 } 1517 $classname = '\core\log\dummy_manager'; 1518 } 1519 1520 $singleton = new $classname(); 1521 return $singleton; 1522 } 1523 1524 /** 1525 * Add an entry to the config log table. 1526 * 1527 * These are "action" focussed rather than web server hits, 1528 * and provide a way to easily reconstruct changes to Moodle configuration. 1529 * 1530 * @package core 1531 * @category log 1532 * @global moodle_database $DB 1533 * @global stdClass $USER 1534 * @param string $name The name of the configuration change action 1535 For example 'filter_active' when activating or deactivating a filter 1536 * @param string $oldvalue The config setting's previous value 1537 * @param string $value The config setting's new value 1538 * @param string $plugin Plugin name, for example a filter name when changing filter configuration 1539 * @return void 1540 */ 1541 function add_to_config_log($name, $oldvalue, $value, $plugin) { 1542 global $USER, $DB; 1543 1544 $log = new stdClass(); 1545 // Use 0 as user id during install. 1546 $log->userid = during_initial_install() ? 0 : $USER->id; 1547 $log->timemodified = time(); 1548 $log->name = $name; 1549 $log->oldvalue = $oldvalue; 1550 $log->value = $value; 1551 $log->plugin = $plugin; 1552 1553 $id = $DB->insert_record('config_log', $log); 1554 1555 $event = core\event\config_log_created::create(array( 1556 'objectid' => $id, 1557 'userid' => $log->userid, 1558 'context' => \context_system::instance(), 1559 'other' => array( 1560 'name' => $log->name, 1561 'oldvalue' => $log->oldvalue, 1562 'value' => $log->value, 1563 'plugin' => $log->plugin 1564 ) 1565 )); 1566 $event->trigger(); 1567 } 1568 1569 /** 1570 * Store user last access times - called when use enters a course or site 1571 * 1572 * @package core 1573 * @category log 1574 * @global stdClass $USER 1575 * @global stdClass $CFG 1576 * @global moodle_database $DB 1577 * @uses LASTACCESS_UPDATE_SECS 1578 * @uses SITEID 1579 * @param int $courseid empty courseid means site 1580 * @return void 1581 */ 1582 function user_accesstime_log($courseid=0) { 1583 global $USER, $CFG, $DB; 1584 1585 if (!isloggedin() or \core\session\manager::is_loggedinas()) { 1586 // no access tracking 1587 return; 1588 } 1589 1590 if (isguestuser()) { 1591 // Do not update guest access times/ips for performance. 1592 return; 1593 } 1594 1595 if (defined('USER_KEY_LOGIN') && USER_KEY_LOGIN === true) { 1596 // Do not update user login time when using user key login. 1597 return; 1598 } 1599 1600 if (empty($courseid)) { 1601 $courseid = SITEID; 1602 } 1603 1604 $timenow = time(); 1605 1606 /// Store site lastaccess time for the current user 1607 if ($timenow - $USER->lastaccess > LASTACCESS_UPDATE_SECS) { 1608 /// Update $USER->lastaccess for next checks 1609 $USER->lastaccess = $timenow; 1610 1611 $last = new stdClass(); 1612 $last->id = $USER->id; 1613 $last->lastip = getremoteaddr(); 1614 $last->lastaccess = $timenow; 1615 1616 $DB->update_record_raw('user', $last); 1617 } 1618 1619 if ($courseid == SITEID) { 1620 /// no user_lastaccess for frontpage 1621 return; 1622 } 1623 1624 /// Store course lastaccess times for the current user 1625 if (empty($USER->currentcourseaccess[$courseid]) or ($timenow - $USER->currentcourseaccess[$courseid] > LASTACCESS_UPDATE_SECS)) { 1626 1627 $lastaccess = $DB->get_field('user_lastaccess', 'timeaccess', array('userid'=>$USER->id, 'courseid'=>$courseid)); 1628 1629 if ($lastaccess === false) { 1630 // Update course lastaccess for next checks 1631 $USER->currentcourseaccess[$courseid] = $timenow; 1632 1633 $last = new stdClass(); 1634 $last->userid = $USER->id; 1635 $last->courseid = $courseid; 1636 $last->timeaccess = $timenow; 1637 try { 1638 $DB->insert_record_raw('user_lastaccess', $last, false); 1639 } catch (dml_write_exception $e) { 1640 // During a race condition we can fail to find the data, then it appears. 1641 // If we still can't find it, rethrow the exception. 1642 $lastaccess = $DB->get_field('user_lastaccess', 'timeaccess', array('userid' => $USER->id, 1643 'courseid' => $courseid)); 1644 if ($lastaccess === false) { 1645 throw $e; 1646 } 1647 // If we did find it, the race condition was true and another thread has inserted the time for us. 1648 // We can just continue without having to do anything. 1649 } 1650 1651 } else if ($timenow - $lastaccess < LASTACCESS_UPDATE_SECS) { 1652 // no need to update now, it was updated recently in concurrent login ;-) 1653 1654 } else { 1655 // Update course lastaccess for next checks 1656 $USER->currentcourseaccess[$courseid] = $timenow; 1657 1658 $DB->set_field('user_lastaccess', 'timeaccess', $timenow, array('userid'=>$USER->id, 'courseid'=>$courseid)); 1659 } 1660 } 1661 } 1662 1663 /// GENERAL HELPFUL THINGS /////////////////////////////////// 1664 1665 /** 1666 * Dumps a given object's information for debugging purposes 1667 * 1668 * When used in a CLI script, the object's information is written to the standard 1669 * error output stream. When used in a web script, the object is dumped to a 1670 * pre-formatted block with the "notifytiny" CSS class. 1671 * 1672 * @param mixed $object The data to be printed 1673 * @return void output is echo'd 1674 */ 1675 function print_object($object) { 1676 1677 // we may need a lot of memory here 1678 raise_memory_limit(MEMORY_EXTRA); 1679 1680 if (CLI_SCRIPT) { 1681 fwrite(STDERR, print_r($object, true)); 1682 fwrite(STDERR, PHP_EOL); 1683 } else if (AJAX_SCRIPT) { 1684 foreach (explode("\n", print_r($object, true)) as $line) { 1685 error_log($line); 1686 } 1687 } else { 1688 echo html_writer::tag('pre', s(print_r($object, true)), array('class' => 'notifytiny')); 1689 } 1690 } 1691 1692 /** 1693 * This function is the official hook inside XMLDB stuff to delegate its debug to one 1694 * external function. 1695 * 1696 * Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before 1697 * using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-) 1698 * 1699 * @uses DEBUG_DEVELOPER 1700 * @param string $message string contains the error message 1701 * @param object $object object XMLDB object that fired the debug 1702 */ 1703 function xmldb_debug($message, $object) { 1704 1705 debugging($message, DEBUG_DEVELOPER); 1706 } 1707 1708 /** 1709 * @global object 1710 * @uses CONTEXT_COURSECAT 1711 * @return boolean Whether the user can create courses in any category in the system. 1712 */ 1713 function user_can_create_courses() { 1714 global $DB; 1715 $catsrs = $DB->get_recordset('course_categories'); 1716 foreach ($catsrs as $cat) { 1717 if (has_capability('moodle/course:create', context_coursecat::instance($cat->id))) { 1718 $catsrs->close(); 1719 return true; 1720 } 1721 } 1722 $catsrs->close(); 1723 return false; 1724 } 1725 1726 /** 1727 * This method can update the values in mulitple database rows for a colum with 1728 * a unique index, without violating that constraint. 1729 * 1730 * Suppose we have a table with a unique index on (otherid, sortorder), and 1731 * for a particular value of otherid, we want to change all the sort orders. 1732 * You have to do this carefully or you will violate the unique index at some time. 1733 * This method takes care of the details for you. 1734 * 1735 * Note that, it is the responsibility of the caller to make sure that the 1736 * requested rename is legal. For example, if you ask for [1 => 2, 2 => 2] 1737 * then you will get a unique key violation error from the database. 1738 * 1739 * @param string $table The database table to modify. 1740 * @param string $field the field that contains the values we are going to change. 1741 * @param array $newvalues oldvalue => newvalue how to change the values. 1742 * E.g. [1 => 4, 2 => 1, 3 => 3, 4 => 2]. 1743 * @param array $otherconditions array fieldname => requestedvalue extra WHERE clause 1744 * conditions to restrict which rows are affected. E.g. array('otherid' => 123). 1745 * @param int $unusedvalue (defaults to -1) a value that is never used in $ordercol. 1746 */ 1747 function update_field_with_unique_index($table, $field, array $newvalues, 1748 array $otherconditions, $unusedvalue = -1) { 1749 global $DB; 1750 $safechanges = decompose_update_into_safe_changes($newvalues, $unusedvalue); 1751 1752 $transaction = $DB->start_delegated_transaction(); 1753 foreach ($safechanges as $change) { 1754 list($from, $to) = $change; 1755 $otherconditions[$field] = $from; 1756 $DB->set_field($table, $field, $to, $otherconditions); 1757 } 1758 $transaction->allow_commit(); 1759 } 1760 1761 /** 1762 * Helper used by {@link update_field_with_unique_index()}. Given a desired 1763 * set of changes, break them down into single udpates that can be done one at 1764 * a time without breaking any unique index constraints. 1765 * 1766 * Suppose the input is array(1 => 2, 2 => 1) and -1. Then the output will be 1767 * array (array(1, -1), array(2, 1), array(-1, 2)). This function solves this 1768 * problem in the general case, not just for simple swaps. The unit tests give 1769 * more examples. 1770 * 1771 * Note that, it is the responsibility of the caller to make sure that the 1772 * requested rename is legal. For example, if you ask for something impossible 1773 * like array(1 => 2, 2 => 2) then the results are undefined. (You will probably 1774 * get a unique key violation error from the database later.) 1775 * 1776 * @param array $newvalues The desired re-ordering. 1777 * E.g. array(1 => 4, 2 => 1, 3 => 3, 4 => 2). 1778 * @param int $unusedvalue A value that is not currently used. 1779 * @return array A safe way to perform the re-order. An array of two-element 1780 * arrays array($from, $to). 1781 * E.g. array(array(1, -1), array(2, 1), array(4, 2), array(-1, 4)). 1782 */ 1783 function decompose_update_into_safe_changes(array $newvalues, $unusedvalue) { 1784 $nontrivialmap = array(); 1785 foreach ($newvalues as $from => $to) { 1786 if ($from == $unusedvalue || $to == $unusedvalue) { 1787 throw new \coding_exception('Supposedly unused value ' . $unusedvalue . ' is actually used!'); 1788 } 1789 if ($from != $to) { 1790 $nontrivialmap[$from] = $to; 1791 } 1792 } 1793 1794 if (empty($nontrivialmap)) { 1795 return array(); 1796 } 1797 1798 // First we deal with all renames that are not part of cycles. 1799 // This bit is O(n^2) and it ought to be possible to do better, 1800 // but it does not seem worth the effort. 1801 $safechanges = array(); 1802 $nontrivialmapchanged = true; 1803 while ($nontrivialmapchanged) { 1804 $nontrivialmapchanged = false; 1805 1806 foreach ($nontrivialmap as $from => $to) { 1807 if (array_key_exists($to, $nontrivialmap)) { 1808 continue; // Cannot currenly do this rename. 1809 } 1810 // Is safe to do this rename now. 1811 $safechanges[] = array($from, $to); 1812 unset($nontrivialmap[$from]); 1813 $nontrivialmapchanged = true; 1814 } 1815 } 1816 1817 // Are we done? 1818 if (empty($nontrivialmap)) { 1819 return $safechanges; 1820 } 1821 1822 // Now what is left in $nontrivialmap must be a permutation, 1823 // which must be a combination of disjoint cycles. We need to break them. 1824 while (!empty($nontrivialmap)) { 1825 // Extract the first cycle. 1826 reset($nontrivialmap); 1827 $current = $cyclestart = key($nontrivialmap); 1828 $cycle = array(); 1829 do { 1830 $cycle[] = $current; 1831 $next = $nontrivialmap[$current]; 1832 unset($nontrivialmap[$current]); 1833 $current = $next; 1834 } while ($current != $cyclestart); 1835 1836 // Now convert it to a sequence of safe renames by using a temp. 1837 $safechanges[] = array($cyclestart, $unusedvalue); 1838 $cycle[0] = $unusedvalue; 1839 $to = $cyclestart; 1840 while ($from = array_pop($cycle)) { 1841 $safechanges[] = array($from, $to); 1842 $to = $from; 1843 } 1844 } 1845 1846 return $safechanges; 1847 } 1848 1849 /** 1850 * Return maximum number of courses in a category 1851 * 1852 * @uses MAX_COURSES_IN_CATEGORY 1853 * @return int number of courses 1854 */ 1855 function get_max_courses_in_category() { 1856 global $CFG; 1857 // Use default MAX_COURSES_IN_CATEGORY if $CFG->maxcoursesincategory is not set or invalid. 1858 if (!isset($CFG->maxcoursesincategory) || clean_param($CFG->maxcoursesincategory, PARAM_INT) == 0) { 1859 return MAX_COURSES_IN_CATEGORY; 1860 } else { 1861 return $CFG->maxcoursesincategory; 1862 } 1863 } 1864 1865 /** 1866 * Prepare a safe ORDER BY statement from user interactable requests. 1867 * 1868 * This allows safe user specified sorting (ORDER BY), by abstracting the SQL from the value being requested by the user. 1869 * A standard string (and optional direction) can be specified, which will be mapped to a predefined allow list of SQL ordering. 1870 * The mapping can optionally include a 'default', which will be used if the key provided is invalid. 1871 * 1872 * Example usage: 1873 * -If $orderbymap = [ 1874 * 'courseid' => 'c.id', 1875 * 'somecustomvalue'=> 'c.startdate, c.shortname', 1876 * 'default' => 'c.fullname', 1877 * ] 1878 * -A value from the map array's keys can be passed in by a user interaction (eg web service) along with an optional direction. 1879 * -get_safe_orderby($orderbymap, 'courseid', 'DESC') would return: ORDER BY c.id DESC 1880 * -get_safe_orderby($orderbymap, 'somecustomvalue') would return: ORDER BY c.startdate, c.shortname 1881 * -get_safe_orderby($orderbymap, 'invalidblah', 'DESC') would return: ORDER BY c.fullname DESC 1882 * -If no default key was specified in $orderbymap, the invalidblah example above would return empty string. 1883 * 1884 * @param array $orderbymap An array in the format [keystring => sqlstring]. A default fallback can be set with the key 'default'. 1885 * @param string $orderbykey A string to be mapped to a key in $orderbymap. 1886 * @param string $direction Optional ORDER BY direction (ASC/DESC, case insensitive). 1887 * @param bool $useprefix Whether ORDER BY is prefixed to the output (true by default). This should not be modified in most cases. 1888 * It is included to enable get_safe_orderby_multiple() to use this function multiple times. 1889 * @return string The ORDER BY statement, or empty string if $orderbykey is invalid and no default is mapped. 1890 */ 1891 function get_safe_orderby(array $orderbymap, string $orderbykey, string $direction = '', bool $useprefix = true): string { 1892 $orderby = $useprefix ? ' ORDER BY ' : ''; 1893 $output = ''; 1894 1895 // Only include an order direction if ASC/DESC is explicitly specified (case insensitive). 1896 $direction = strtoupper($direction); 1897 if (!in_array($direction, ['ASC', 'DESC'], true)) { 1898 $direction = ''; 1899 } else { 1900 $direction = " {$direction}"; 1901 } 1902 1903 // Prepare the statement if the key maps to a defined sort parameter. 1904 if (isset($orderbymap[$orderbykey])) { 1905 $output = "{$orderby}{$orderbymap[$orderbykey]}{$direction}"; 1906 } else if (array_key_exists('default', $orderbymap)) { 1907 // Fall back to use the default if one is specified. 1908 $output = "{$orderby}{$orderbymap['default']}{$direction}"; 1909 } 1910 1911 return $output; 1912 } 1913 1914 /** 1915 * Prepare a safe ORDER BY statement from user interactable requests using multiple values. 1916 * 1917 * This allows safe user specified sorting (ORDER BY) similar to get_safe_orderby(), but supports multiple keys and directions. 1918 * This is useful in cases where combinations of columns are needed and/or each item requires a specified direction (ASC/DESC). 1919 * The mapping can optionally include a 'default', which will be used if the key provided is invalid. 1920 * 1921 * Example usage: 1922 * -If $orderbymap = [ 1923 * 'courseid' => 'c.id', 1924 * 'fullname'=> 'c.fullname', 1925 * 'default' => 'c.startdate', 1926 * ] 1927 * -An array of values from the map's keys can be passed in by a user interaction (eg web service), with optional directions. 1928 * -get_safe_orderby($orderbymap, ['courseid', 'fullname'], ['DESC', 'ASC']) would return: ORDER BY c.id DESC, c.fullname ASC 1929 * -get_safe_orderby($orderbymap, ['courseid', 'invalidblah'], ['aaa', 'DESC']) would return: ORDER BY c.id, c.startdate DESC 1930 * -If no default key was specified in $orderbymap, the invalidblah example above would return: ORDER BY c.id 1931 * 1932 * @param array $orderbymap An array in the format [keystring => sqlstring]. A default fallback can be set with the key 'default'. 1933 * @param array $orderbykeys An array of strings to be mapped to keys in $orderbymap. 1934 * @param array $directions Optional array of ORDER BY direction (ASC/DESC, case insensitive). 1935 * The array keys should match array keys in $orderbykeys. 1936 * @return string The ORDER BY statement, or empty string if $orderbykeys contains no valid items and no default is mapped. 1937 */ 1938 function get_safe_orderby_multiple(array $orderbymap, array $orderbykeys, array $directions = []): string { 1939 $output = ''; 1940 1941 // Check each key for a valid mapping and add to the ORDER BY statement (invalid entries will be empty strings). 1942 foreach ($orderbykeys as $index => $orderbykey) { 1943 $direction = $directions[$index] ?? ''; 1944 $safeorderby = get_safe_orderby($orderbymap, $orderbykey, $direction, false); 1945 1946 if (!empty($safeorderby)) { 1947 $output .= ", {$safeorderby}"; 1948 } 1949 } 1950 1951 // Prefix with ORDER BY if any valid ordering is specified (and remove comma from the start). 1952 if (!empty($output)) { 1953 $output = ' ORDER BY' . ltrim($output, ','); 1954 } 1955 1956 return $output; 1957 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
