Merge branch 'MDL-81073' of https://github.com/paulholden/moodle
[moodle.git] / lib / datalib.php
blobeac742b6ae27b332c5b4dc2e99cbbd831ee497e2
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.
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/>.
17 /**
18 * Library of functions for database manipulation.
20 * Other main libraries:
21 * - weblib.php - functions that produce web output
22 * - moodlelib.php - general-purpose Moodle functions
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
29 defined('MOODLE_INTERNAL') || die();
31 /**
32 * The maximum courses in a category
33 * MAX_COURSES_IN_CATEGORY * MAX_COURSE_CATEGORIES must not be more than max integer!
35 define('MAX_COURSES_IN_CATEGORY', 10000);
37 /**
38 * The maximum number of course categories
39 * MAX_COURSES_IN_CATEGORY * MAX_COURSE_CATEGORIES must not be more than max integer!
41 define('MAX_COURSE_CATEGORIES', 10000);
43 /**
44 * Number of seconds to wait before updating lastaccess information in DB.
46 * We allow overwrites from config.php, useful to ensure coherence in performance
47 * tests results.
49 * Note: For web service requests in the external_tokens field, we use a different constant
50 * webservice::TOKEN_LASTACCESS_UPDATE_SECS.
52 if (!defined('LASTACCESS_UPDATE_SECS')) {
53 define('LASTACCESS_UPDATE_SECS', 60);
55 /**
56 * The constant value when we use the search option.
58 define('USER_SEARCH_STARTS_WITH', 0);
59 define('USER_SEARCH_CONTAINS', 1);
60 define('USER_SEARCH_EXACT_MATCH', 2);
62 /**
63 * Returns $user object of the main admin user
65 * @static stdClass $mainadmin
66 * @return stdClass {@link $USER} record from DB, false if not found
68 function get_admin() {
69 global $CFG, $DB;
71 static $mainadmin = null;
72 static $prevadmins = null;
74 if (empty($CFG->siteadmins)) {
75 // Should not happen on an ordinary site.
76 // It does however happen during unit tests.
77 return false;
80 if (isset($mainadmin) and $prevadmins === $CFG->siteadmins) {
81 return clone($mainadmin);
84 $mainadmin = null;
86 foreach (explode(',', $CFG->siteadmins) as $id) {
87 if ($user = $DB->get_record('user', array('id'=>$id, 'deleted'=>0))) {
88 $mainadmin = $user;
89 break;
93 if ($mainadmin) {
94 $prevadmins = $CFG->siteadmins;
95 return clone($mainadmin);
96 } else {
97 // this should not happen
98 return false;
103 * Returns list of all admins, using 1 DB query
105 * @return array
107 function get_admins() {
108 global $DB, $CFG;
110 if (empty($CFG->siteadmins)) { // Should not happen on an ordinary site
111 return array();
114 $sql = "SELECT u.*
115 FROM {user} u
116 WHERE u.deleted = 0 AND u.id IN ($CFG->siteadmins)";
118 // We want the same order as in $CFG->siteadmins.
119 $records = $DB->get_records_sql($sql);
120 $admins = array();
121 foreach (explode(',', $CFG->siteadmins) as $id) {
122 $id = (int)$id;
123 if (!isset($records[$id])) {
124 // User does not exist, this should not happen.
125 continue;
127 $admins[$records[$id]->id] = $records[$id];
130 return $admins;
134 * Search through course users
136 * If $coursid specifies the site course then this function searches
137 * through all undeleted and confirmed users
139 * @global object
140 * @uses SITEID
141 * @uses SQL_PARAMS_NAMED
142 * @uses CONTEXT_COURSE
143 * @param int $courseid The course in question.
144 * @param int $groupid The group in question.
145 * @param string $searchtext The string to search for
146 * @param string $sort A field to sort by
147 * @param array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
148 * @return array
150 function search_users($courseid, $groupid, $searchtext, $sort='', array $exceptions=null) {
151 global $DB;
153 $fullname = $DB->sql_fullname('u.firstname', 'u.lastname');
155 if (!empty($exceptions)) {
156 list($exceptions, $params) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex', false);
157 $except = "AND u.id $exceptions";
158 } else {
159 $except = "";
160 $params = array();
163 if (!empty($sort)) {
164 $order = "ORDER BY $sort";
165 } else {
166 $order = "";
169 $select = "u.deleted = 0 AND u.confirmed = 1 AND (".$DB->sql_like($fullname, ':search1', false)." OR ".$DB->sql_like('u.email', ':search2', false).")";
170 $params['search1'] = "%$searchtext%";
171 $params['search2'] = "%$searchtext%";
173 if (!$courseid or $courseid == SITEID) {
174 $sql = "SELECT u.id, u.firstname, u.lastname, u.email
175 FROM {user} u
176 WHERE $select
177 $except
178 $order";
179 return $DB->get_records_sql($sql, $params);
181 } else {
182 if ($groupid) {
183 $sql = "SELECT u.id, u.firstname, u.lastname, u.email
184 FROM {user} u
185 JOIN {groups_members} gm ON gm.userid = u.id
186 WHERE $select AND gm.groupid = :groupid
187 $except
188 $order";
189 $params['groupid'] = $groupid;
190 return $DB->get_records_sql($sql, $params);
192 } else {
193 $context = context_course::instance($courseid);
195 // We want to query both the current context and parent contexts.
196 list($relatedctxsql, $relatedctxparams) = $DB->get_in_or_equal($context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relatedctx');
198 $sql = "SELECT u.id, u.firstname, u.lastname, u.email
199 FROM {user} u
200 JOIN {role_assignments} ra ON ra.userid = u.id
201 WHERE $select AND ra.contextid $relatedctxsql
202 $except
203 $order";
204 $params = array_merge($params, $relatedctxparams);
205 return $DB->get_records_sql($sql, $params);
211 * Returns SQL used to search through user table to find users (in a query
212 * which may also join and apply other conditions).
214 * You can combine this SQL with an existing query by adding 'AND $sql' to the
215 * WHERE clause of your query (where $sql is the first element in the array
216 * returned by this function), and merging in the $params array to the parameters
217 * of your query (where $params is the second element). Your query should use
218 * named parameters such as :param, rather than the question mark style.
220 * There are examples of basic usage in the unit test for this function.
222 * @param string $search the text to search for (empty string = find all)
223 * @param string $u the table alias for the user table in the query being
224 * built. May be ''.
225 * @param int $searchtype If 0(default): searches at start, 1: searches in the middle of names
226 * 2: search exact match.
227 * @param array $extrafields Array of extra user fields to include in search, must be prefixed with table alias if they are not in
228 * the user table.
229 * @param array $exclude Array of user ids to exclude (empty = don't exclude)
230 * @param array $includeonly If specified, only returns users that have ids
231 * incldued in this array (empty = don't restrict)
232 * @return array an array with two elements, a fragment of SQL to go in the
233 * where clause the query, and an associative array containing any required
234 * parameters (using named placeholders).
236 function users_search_sql(string $search, string $u = 'u', int $searchtype = USER_SEARCH_STARTS_WITH, array $extrafields = [],
237 array $exclude = null, array $includeonly = null): array {
238 global $DB, $CFG;
239 $params = array();
240 $tests = array();
242 if ($u) {
243 $u .= '.';
246 if ($search) {
247 $conditions = array(
248 $DB->sql_fullname($u . 'firstname', $u . 'lastname'),
249 $conditions[] = $u . 'lastname'
251 foreach ($extrafields as $field) {
252 // Add the table alias for the user table if the field doesn't already have an alias.
253 $conditions[] = strpos($field, '.') !== false ? $field : $u . $field;
255 switch ($searchtype) {
256 case USER_SEARCH_STARTS_WITH:
257 // Put a field LIKE 'search%' condition on each field.
258 $searchparam = $search . '%';
259 break;
260 case USER_SEARCH_CONTAINS:
261 // Put a field LIKE '$search%' condition on each field.
262 $searchparam = '%' . $search . '%';
263 break;
264 case USER_SEARCH_EXACT_MATCH:
265 // Match exact the $search string.
266 $searchparam = $search;
267 break;
269 $i = 0;
270 foreach ($conditions as $key => $condition) {
271 $conditions[$key] = $DB->sql_like($condition, ":con{$i}00", false, false);
272 if ($searchtype === USER_SEARCH_EXACT_MATCH) {
273 $conditions[$key] = "$condition = :con{$i}00";
275 $params["con{$i}00"] = $searchparam;
276 $i++;
278 $tests[] = '(' . implode(' OR ', $conditions) . ')';
281 // Add some additional sensible conditions.
282 $tests[] = $u . "id <> :guestid";
283 $params['guestid'] = $CFG->siteguest;
284 $tests[] = $u . 'deleted = 0';
285 $tests[] = $u . 'confirmed = 1';
287 // If we are being asked to exclude any users, do that.
288 if (!empty($exclude)) {
289 list($usertest, $userparams) = $DB->get_in_or_equal($exclude, SQL_PARAMS_NAMED, 'ex', false);
290 $tests[] = $u . 'id ' . $usertest;
291 $params = array_merge($params, $userparams);
294 // If we are validating a set list of userids, add an id IN (...) test.
295 if (!empty($includeonly)) {
296 list($usertest, $userparams) = $DB->get_in_or_equal($includeonly, SQL_PARAMS_NAMED, 'val');
297 $tests[] = $u . 'id ' . $usertest;
298 $params = array_merge($params, $userparams);
301 // In case there are no tests, add one result (this makes it easier to combine
302 // this with an existing query as you can always add AND $sql).
303 if (empty($tests)) {
304 $tests[] = '1 = 1';
307 // Combing the conditions and return.
308 return array(implode(' AND ', $tests), $params);
313 * This function generates the standard ORDER BY clause for use when generating
314 * lists of users. If you don't have a reason to use a different order, then
315 * you should use this method to generate the order when displaying lists of users.
317 * If the optional $search parameter is passed, then exact matches to the search
318 * will be sorted first. For example, suppose you have two users 'Al Zebra' and
319 * 'Alan Aardvark'. The default sort is Alan, then Al. If, however, you search for
320 * 'Al', then Al will be listed first. (With two users, this is not a big deal,
321 * but with thousands of users, it is essential.)
323 * The list of fields scanned for exact matches are:
324 * - firstname
325 * - lastname
326 * - $DB->sql_fullname
327 * - those returned by \core_user\fields::get_identity_fields or those included in $customfieldmappings
329 * If named parameters are used (which is the default, and highly recommended),
330 * then the parameter names are like :usersortexactN, where N is an int.
332 * The simplest possible example use is:
333 * list($sort, $params) = users_order_by_sql();
334 * $sql = 'SELECT * FROM {users} ORDER BY ' . $sort;
336 * A more complex example, showing that this sort can be combined with other sorts:
337 * list($sort, $sortparams) = users_order_by_sql('u');
338 * $sql = "SELECT g.id AS groupid, gg.groupingid, u.id AS userid, u.firstname, u.lastname, u.idnumber, u.username
339 * FROM {groups} g
340 * LEFT JOIN {groupings_groups} gg ON g.id = gg.groupid
341 * LEFT JOIN {groups_members} gm ON g.id = gm.groupid
342 * LEFT JOIN {user} u ON gm.userid = u.id
343 * WHERE g.courseid = :courseid $groupwhere $groupingwhere
344 * ORDER BY g.name, $sort";
345 * $params += $sortparams;
347 * An example showing the use of $search:
348 * list($sort, $sortparams) = users_order_by_sql('u', $search, $this->get_context());
349 * $order = ' ORDER BY ' . $sort;
350 * $params += $sortparams;
351 * $availableusers = $DB->get_records_sql($fields . $sql . $order, $params, $page*$perpage, $perpage);
353 * @param string $usertablealias (optional) any table prefix for the {users} table. E.g. 'u'.
354 * @param string $search (optional) a current search string. If given,
355 * any exact matches to this string will be sorted first.
356 * @param context|null $context the context we are in. Used by \core_user\fields::get_identity_fields.
357 * Defaults to $PAGE->context.
358 * @param array $customfieldmappings associative array of mappings for custom fields returned by \core_user\fields::get_sql.
359 * @return array with two elements:
360 * string SQL fragment to use in the ORDER BY clause. For example, "firstname, lastname".
361 * array of parameters used in the SQL fragment. If $search is not given, this is guaranteed to be an empty array.
363 function users_order_by_sql(string $usertablealias = '', string $search = null, context $context = null,
364 array $customfieldmappings = []) {
365 global $DB, $PAGE;
367 if ($usertablealias) {
368 $tableprefix = $usertablealias . '.';
369 } else {
370 $tableprefix = '';
373 $sort = "{$tableprefix}lastname, {$tableprefix}firstname, {$tableprefix}id";
374 $params = array();
376 if (!$search) {
377 return array($sort, $params);
380 if (!$context) {
381 $context = $PAGE->context;
384 $exactconditions = array();
385 $paramkey = 'usersortexact1';
387 $exactconditions[] = $DB->sql_fullname($tableprefix . 'firstname', $tableprefix . 'lastname') .
388 ' = :' . $paramkey;
389 $params[$paramkey] = $search;
390 $paramkey++;
392 if ($customfieldmappings) {
393 $fieldstocheck = array_merge([$tableprefix . 'firstname', $tableprefix . 'lastname'], array_values($customfieldmappings));
394 } else {
395 $fieldstocheck = array_merge(['firstname', 'lastname'], \core_user\fields::get_identity_fields($context, false));
396 $fieldstocheck = array_map(function($field) use ($tableprefix) {
397 return $tableprefix . $field;
398 }, $fieldstocheck);
401 foreach ($fieldstocheck as $key => $field) {
402 $exactconditions[] = 'LOWER(' . $field . ') = LOWER(:' . $paramkey . ')';
403 $params[$paramkey] = $search;
404 $paramkey++;
407 $sort = 'CASE WHEN ' . implode(' OR ', $exactconditions) .
408 ' THEN 0 ELSE 1 END, ' . $sort;
410 return array($sort, $params);
414 * Returns a subset of users
416 * @global object
417 * @uses DEBUG_DEVELOPER
418 * @uses SQL_PARAMS_NAMED
419 * @param bool $get If false then only a count of the records is returned
420 * @param string $search A simple string to search for
421 * @param bool $confirmed A switch to allow/disallow unconfirmed users
422 * @param array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10
423 * @param string $sort A SQL snippet for the sorting criteria to use
424 * @param string $firstinitial Users whose first name starts with $firstinitial
425 * @param string $lastinitial Users whose last name starts with $lastinitial
426 * @param string $page The page or records to return
427 * @param string $recordsperpage The number of records to return per page
428 * @param string $fields A comma separated list of fields to be returned from the chosen table.
429 * @return array|int|bool {@link $USER} records unless get is false in which case the integer count of the records found is returned.
430 * False is returned if an error is encountered.
432 function get_users($get=true, $search='', $confirmed=false, array $exceptions=null, $sort='firstname ASC',
433 $firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*', $extraselect='', array $extraparams=null) {
434 global $DB, $CFG;
436 if ($get && !$recordsperpage) {
437 debugging('Call to get_users with $get = true no $recordsperpage limit. ' .
438 'On large installations, this will probably cause an out of memory error. ' .
439 'Please think again and change your code so that it does not try to ' .
440 'load so much data into memory.', DEBUG_DEVELOPER);
443 $fullname = $DB->sql_fullname();
445 $select = " id <> :guestid AND deleted = 0";
446 $params = array('guestid'=>$CFG->siteguest);
448 if (!empty($search)){
449 $search = trim($search);
450 $select .= " AND (".$DB->sql_like($fullname, ':search1', false)." OR ".$DB->sql_like('email', ':search2', false)." OR username = :search3)";
451 $params['search1'] = "%$search%";
452 $params['search2'] = "%$search%";
453 $params['search3'] = "$search";
456 if ($confirmed) {
457 $select .= " AND confirmed = 1";
460 if ($exceptions) {
461 list($exceptions, $eparams) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex', false);
462 $params = $params + $eparams;
463 $select .= " AND id $exceptions";
466 if ($firstinitial) {
467 $select .= " AND ".$DB->sql_like('firstname', ':fni', false, false);
468 $params['fni'] = "$firstinitial%";
470 if ($lastinitial) {
471 $select .= " AND ".$DB->sql_like('lastname', ':lni', false, false);
472 $params['lni'] = "$lastinitial%";
475 if ($extraselect) {
476 $select .= " AND $extraselect";
477 $params = $params + (array)$extraparams;
480 if ($get) {
481 return $DB->get_records_select('user', $select, $params, $sort, $fields, $page, $recordsperpage);
482 } else {
483 return $DB->count_records_select('user', $select, $params);
489 * Return filtered (if provided) list of users in site, except guest and deleted users.
491 * @param string $sort An SQL field to sort by
492 * @param string $dir The sort direction ASC|DESC
493 * @param int $page The page or records to return
494 * @param int $recordsperpage The number of records to return per page
495 * @param string $search A simple string to search for
496 * @param string $firstinitial Users whose first name starts with $firstinitial
497 * @param string $lastinitial Users whose last name starts with $lastinitial
498 * @param string $extraselect An additional SQL select statement to append to the query
499 * @param array $extraparams Additional parameters to use for the above $extraselect
500 * @param stdClass $extracontext If specified, will include user 'extra fields'
501 * as appropriate for current user and given context
502 * @return array Array of {@link $USER} records
504 function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=0,
505 $search='', $firstinitial='', $lastinitial='', $extraselect='',
506 array $extraparams=null, $extracontext = null) {
507 global $DB, $CFG;
509 $fullname = $DB->sql_fullname();
511 $select = "deleted <> 1 AND u.id <> :guestid";
512 $params = array('guestid' => $CFG->siteguest);
514 if (!empty($search)) {
515 $search = trim($search);
516 $select .= " AND (". $DB->sql_like($fullname, ':search1', false, false).
517 " OR ". $DB->sql_like('email', ':search2', false, false).
518 " OR username = :search3)";
519 $params['search1'] = "%$search%";
520 $params['search2'] = "%$search%";
521 $params['search3'] = "$search";
524 if ($firstinitial) {
525 $select .= " AND ". $DB->sql_like('firstname', ':fni', false, false);
526 $params['fni'] = "$firstinitial%";
528 if ($lastinitial) {
529 $select .= " AND ". $DB->sql_like('lastname', ':lni', false, false);
530 $params['lni'] = "$lastinitial%";
533 if ($extraselect) {
534 // The extra WHERE clause may refer to the 'id' column which can now be ambiguous because we
535 // changed the query to include joins, so replace any 'id' that is on its own (no alias)
536 // with 'u.id'.
537 $extraselect = preg_replace('~([ =]|^)id([ =]|$)~', '$1u.id$2', $extraselect);
538 $select .= " AND $extraselect";
539 $params = $params + (array)$extraparams;
542 // If a context is specified, get extra user fields that the current user
543 // is supposed to see, otherwise just get the name fields.
544 $userfields = \core_user\fields::for_name();
545 if ($extracontext) {
546 $userfields->with_identity($extracontext, true);
549 $userfields->excluding('id');
550 $userfields->including('username', 'email', 'city', 'country', 'lastaccess', 'confirmed', 'mnethostid', 'suspended');
551 ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams, 'mappings' => $mappings] =
552 (array)$userfields->get_sql('u', true);
554 if ($sort) {
555 $orderbymap = $mappings;
556 $orderbymap['default'] = 'lastaccess';
557 $sort = get_safe_orderby($orderbymap, $sort, $dir);
560 // warning: will return UNCONFIRMED USERS
561 return $DB->get_records_sql("SELECT u.id $selects
562 FROM {user} u
563 $joins
564 WHERE $select
565 $sort", array_merge($params, $joinparams), $page, $recordsperpage);
571 * Full list of users that have confirmed their accounts.
573 * @global object
574 * @return array of unconfirmed users
576 function get_users_confirmed() {
577 global $DB, $CFG;
578 return $DB->get_records_sql("SELECT *
579 FROM {user}
580 WHERE confirmed = 1 AND deleted = 0 AND id <> ?", array($CFG->siteguest));
584 /// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////
588 * Returns $course object of the top-level site.
590 * @return object A {@link $COURSE} object for the site, exception if not found
592 function get_site() {
593 global $SITE, $DB;
595 if (!empty($SITE->id)) { // We already have a global to use, so return that
596 return $SITE;
599 if ($course = $DB->get_record('course', array('category'=>0))) {
600 return $course;
601 } else {
602 // course table exists, but the site is not there,
603 // unfortunately there is no automatic way to recover
604 throw new moodle_exception('nosite', 'error');
609 * Gets a course object from database. If the course id corresponds to an
610 * already-loaded $COURSE or $SITE object, then the loaded object will be used,
611 * saving a database query.
613 * If it reuses an existing object, by default the object will be cloned. This
614 * means you can modify the object safely without affecting other code.
616 * @param int $courseid Course id
617 * @param bool $clone If true (default), makes a clone of the record
618 * @return stdClass A course object
619 * @throws dml_exception If not found in database
621 function get_course($courseid, $clone = true) {
622 global $DB, $COURSE, $SITE;
623 if (!empty($COURSE->id) && $COURSE->id == $courseid) {
624 return $clone ? clone($COURSE) : $COURSE;
625 } else if (!empty($SITE->id) && $SITE->id == $courseid) {
626 return $clone ? clone($SITE) : $SITE;
627 } else {
628 return $DB->get_record('course', array('id' => $courseid), '*', MUST_EXIST);
633 * Returns list of courses, for whole site, or category
635 * Returns list of courses, for whole site, or category
636 * Important: Using c.* for fields is extremely expensive because
637 * we are using distinct. You almost _NEVER_ need all the fields
638 * in such a large SELECT
640 * Consider using core_course_category::get_courses()
641 * or core_course_category::search_courses() instead since they use caching.
643 * @global object
644 * @global object
645 * @global object
646 * @uses CONTEXT_COURSE
647 * @param string|int $categoryid Either a category id or 'all' for everything
648 * @param string $sort A field and direction to sort by
649 * @param string $fields The additional fields to return (note that "id, category, visible" are always present)
650 * @return array Array of courses
652 function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {
654 global $USER, $CFG, $DB;
656 $params = array();
658 if ($categoryid !== "all" && is_numeric($categoryid)) {
659 $categoryselect = "WHERE c.category = :catid";
660 $params['catid'] = $categoryid;
661 } else {
662 $categoryselect = "";
665 if (empty($sort)) {
666 $sortstatement = "";
667 } else {
668 $sortstatement = "ORDER BY $sort";
671 $visiblecourses = array();
673 $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');
674 $ccjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel)";
675 $params['contextlevel'] = CONTEXT_COURSE;
677 // The fields "id, category, visible" are required in the subsequent loop and must always be present.
678 if ($fields !== 'c.*') {
679 $fieldarray = array_merge(
680 // Split fields on comma + zero or more whitespace, merge with required fields.
681 preg_split('/,\s*/', $fields), [
682 'c.id',
683 'c.category',
684 'c.visible',
687 $fields = implode(',', array_unique($fieldarray));
690 $sql = "SELECT $fields $ccselect
691 FROM {course} c
692 $ccjoin
693 $categoryselect
694 $sortstatement";
696 // pull out all course matching the cat
697 if ($courses = $DB->get_records_sql($sql, $params)) {
699 // loop throught them
700 foreach ($courses as $course) {
701 context_helper::preload_from_record($course);
702 if (core_course_category::can_view_course_info($course)) {
703 $visiblecourses [$course->id] = $course;
707 return $visiblecourses;
711 * A list of courses that match a search
713 * @global object
714 * @global object
715 * @param array $searchterms An array of search criteria
716 * @param string $sort A field and direction to sort by
717 * @param int $page The page number to get
718 * @param int $recordsperpage The number of records per page
719 * @param int $totalcount Passed in by reference.
720 * @param array $requiredcapabilities Extra list of capabilities used to filter courses
721 * @param array $searchcond additional search conditions, for example ['c.enablecompletion = :p1']
722 * @param array $params named parameters for additional search conditions, for example ['p1' => 1]
723 * @return stdClass[] {@link $COURSE} records
725 function get_courses_search($searchterms, $sort, $page, $recordsperpage, &$totalcount,
726 $requiredcapabilities = array(), $searchcond = [], $params = []) {
727 global $CFG, $DB;
729 if ($DB->sql_regex_supported()) {
730 $REGEXP = $DB->sql_regex(true);
731 $NOTREGEXP = $DB->sql_regex(false);
734 $i = 0;
736 // Thanks Oracle for your non-ansi concat and type limits in coalesce. MDL-29912
737 if ($DB->get_dbfamily() == 'oracle') {
738 $concat = "(c.summary|| ' ' || c.fullname || ' ' || c.idnumber || ' ' || c.shortname)";
739 } else {
740 $concat = $DB->sql_concat("COALESCE(c.summary, '')", "' '", 'c.fullname', "' '", 'c.idnumber', "' '", 'c.shortname');
743 foreach ($searchterms as $searchterm) {
744 $i++;
746 $NOT = false; /// Initially we aren't going to perform NOT LIKE searches, only MSSQL and Oracle
747 /// will use it to simulate the "-" operator with LIKE clause
749 /// Under Oracle and MSSQL, trim the + and - operators and perform
750 /// simpler LIKE (or NOT LIKE) queries
751 if (!$DB->sql_regex_supported()) {
752 if (substr($searchterm, 0, 1) == '-') {
753 $NOT = true;
755 $searchterm = trim($searchterm, '+-');
758 // TODO: +- may not work for non latin languages
760 if (substr($searchterm,0,1) == '+') {
761 $searchterm = trim($searchterm, '+-');
762 $searchterm = preg_quote($searchterm, '|');
763 $searchcond[] = "$concat $REGEXP :ss$i";
764 $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";
766 } else if ((substr($searchterm,0,1) == "-") && (core_text::strlen($searchterm) > 1)) {
767 $searchterm = trim($searchterm, '+-');
768 $searchterm = preg_quote($searchterm, '|');
769 $searchcond[] = "$concat $NOTREGEXP :ss$i";
770 $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";
772 } else {
773 $searchcond[] = $DB->sql_like($concat,":ss$i", false, true, $NOT);
774 $params['ss'.$i] = "%$searchterm%";
778 if (empty($searchcond)) {
779 $searchcond = array('1 = 1');
782 $searchcond = implode(" AND ", $searchcond);
784 $courses = array();
785 $c = 0; // counts how many visible courses we've seen
787 // Tiki pagination
788 $limitfrom = $page * $recordsperpage;
789 $limitto = $limitfrom + $recordsperpage;
791 $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');
792 $ccjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel)";
793 $params['contextlevel'] = CONTEXT_COURSE;
795 $sql = "SELECT c.* $ccselect
796 FROM {course} c
797 $ccjoin
798 WHERE $searchcond AND c.id <> ".SITEID."
799 ORDER BY $sort";
801 $mycourses = enrol_get_my_courses();
802 $rs = $DB->get_recordset_sql($sql, $params);
803 foreach($rs as $course) {
804 // Preload contexts only for hidden courses or courses we need to return.
805 context_helper::preload_from_record($course);
806 $coursecontext = context_course::instance($course->id);
807 if (!array_key_exists($course->id, $mycourses) && !core_course_category::can_view_course_info($course)) {
808 continue;
810 if (!empty($requiredcapabilities)) {
811 if (!has_all_capabilities($requiredcapabilities, $coursecontext)) {
812 continue;
815 // Don't exit this loop till the end
816 // we need to count all the visible courses
817 // to update $totalcount
818 if ($c >= $limitfrom && $c < $limitto) {
819 $courses[$course->id] = $course;
821 $c++;
823 $rs->close();
825 // our caller expects 2 bits of data - our return
826 // array, and an updated $totalcount
827 $totalcount = $c;
828 return $courses;
832 * Fixes course category and course sortorder, also verifies category and course parents and paths.
833 * (circular references are not fixed)
835 * @global object
836 * @global object
837 * @uses MAX_COURSE_CATEGORIES
838 * @uses SITEID
839 * @uses CONTEXT_COURSE
840 * @return void
842 function fix_course_sortorder() {
843 global $DB, $SITE;
845 //WARNING: this is PHP5 only code!
847 // if there are any changes made to courses or categories we will trigger
848 // the cache events to purge all cached courses/categories data
849 $cacheevents = array();
851 if ($unsorted = $DB->get_records('course_categories', array('sortorder'=>0))) {
852 //move all categories that are not sorted yet to the end
853 $DB->set_field('course_categories', 'sortorder',
854 get_max_courses_in_category() * MAX_COURSE_CATEGORIES, array('sortorder' => 0));
855 $cacheevents['changesincoursecat'] = true;
858 $allcats = $DB->get_records('course_categories', null, 'sortorder, id', 'id, sortorder, parent, depth, path');
859 $topcats = array();
860 $brokencats = array();
861 foreach ($allcats as $cat) {
862 $sortorder = (int)$cat->sortorder;
863 if (!$cat->parent) {
864 while(isset($topcats[$sortorder])) {
865 $sortorder++;
867 $topcats[$sortorder] = $cat;
868 continue;
870 if (!isset($allcats[$cat->parent])) {
871 $brokencats[] = $cat;
872 continue;
874 if (!isset($allcats[$cat->parent]->children)) {
875 $allcats[$cat->parent]->children = array();
877 while(isset($allcats[$cat->parent]->children[$sortorder])) {
878 $sortorder++;
880 $allcats[$cat->parent]->children[$sortorder] = $cat;
882 unset($allcats);
884 // add broken cats to category tree
885 if ($brokencats) {
886 $defaultcat = reset($topcats);
887 foreach ($brokencats as $cat) {
888 $topcats[] = $cat;
892 // now walk recursively the tree and fix any problems found
893 $sortorder = 0;
894 $fixcontexts = array();
895 if (_fix_course_cats($topcats, $sortorder, 0, 0, '', $fixcontexts)) {
896 $cacheevents['changesincoursecat'] = true;
899 // detect if there are "multiple" frontpage courses and fix them if needed
900 $frontcourses = $DB->get_records('course', array('category'=>0), 'id');
901 if (count($frontcourses) > 1) {
902 if (isset($frontcourses[SITEID])) {
903 $frontcourse = $frontcourses[SITEID];
904 unset($frontcourses[SITEID]);
905 } else {
906 $frontcourse = array_shift($frontcourses);
908 $defaultcat = reset($topcats);
909 foreach ($frontcourses as $course) {
910 $DB->set_field('course', 'category', $defaultcat->id, array('id'=>$course->id));
911 $context = context_course::instance($course->id);
912 $fixcontexts[$context->id] = $context;
913 $cacheevents['changesincourse'] = true;
915 unset($frontcourses);
916 } else {
917 $frontcourse = reset($frontcourses);
920 // now fix the paths and depths in context table if needed
921 if ($fixcontexts) {
922 foreach ($fixcontexts as $fixcontext) {
923 $fixcontext->reset_paths(false);
925 context_helper::build_all_paths(false);
926 unset($fixcontexts);
927 $cacheevents['changesincourse'] = true;
928 $cacheevents['changesincoursecat'] = true;
931 // release memory
932 unset($topcats);
933 unset($brokencats);
934 unset($fixcontexts);
936 // fix frontpage course sortorder
937 if ($frontcourse->sortorder != 1) {
938 $DB->set_field('course', 'sortorder', 1, array('id'=>$frontcourse->id));
939 $cacheevents['changesincourse'] = true;
942 // now fix the course counts in category records if needed
943 $sql = "SELECT cc.id, cc.coursecount, COUNT(c.id) AS newcount
944 FROM {course_categories} cc
945 LEFT JOIN {course} c ON c.category = cc.id
946 GROUP BY cc.id, cc.coursecount
947 HAVING cc.coursecount <> COUNT(c.id)";
949 if ($updatecounts = $DB->get_records_sql($sql)) {
950 // categories with more courses than MAX_COURSES_IN_CATEGORY
951 $categories = array();
952 foreach ($updatecounts as $cat) {
953 $cat->coursecount = $cat->newcount;
954 if ($cat->coursecount >= get_max_courses_in_category()) {
955 $categories[] = $cat->id;
957 unset($cat->newcount);
958 $DB->update_record_raw('course_categories', $cat, true);
960 if (!empty($categories)) {
961 $str = implode(', ', $categories);
962 debugging("The number of courses (category id: $str) has reached max number of courses " .
963 "in a category (" . get_max_courses_in_category() . "). It will cause a sorting performance issue. " .
964 "Please set higher value for \$CFG->maxcoursesincategory in config.php. " .
965 "Please also make sure \$CFG->maxcoursesincategory * MAX_COURSE_CATEGORIES less than max integer. " .
966 "See tracker issues: MDL-25669 and MDL-69573", DEBUG_DEVELOPER);
968 $cacheevents['changesincoursecat'] = true;
971 // now make sure that sortorders in course table are withing the category sortorder ranges
972 $sql = "SELECT DISTINCT cc.id, cc.sortorder
973 FROM {course_categories} cc
974 JOIN {course} c ON c.category = cc.id
975 WHERE c.sortorder < cc.sortorder OR c.sortorder > cc.sortorder + " . get_max_courses_in_category();
977 if ($fixcategories = $DB->get_records_sql($sql)) {
978 //fix the course sortorder ranges
979 foreach ($fixcategories as $cat) {
980 $sql = "UPDATE {course}
981 SET sortorder = ".$DB->sql_modulo('sortorder', get_max_courses_in_category())." + ?
982 WHERE category = ?";
983 $DB->execute($sql, array($cat->sortorder, $cat->id));
985 $cacheevents['changesincoursecat'] = true;
987 unset($fixcategories);
989 // categories having courses with sortorder duplicates or having gaps in sortorder
990 $sql = "SELECT DISTINCT c1.category AS id , cc.sortorder
991 FROM {course} c1
992 JOIN {course} c2 ON c1.sortorder = c2.sortorder
993 JOIN {course_categories} cc ON (c1.category = cc.id)
994 WHERE c1.id <> c2.id";
995 $fixcategories = $DB->get_records_sql($sql);
997 $sql = "SELECT cc.id, cc.sortorder, cc.coursecount, MAX(c.sortorder) AS maxsort, MIN(c.sortorder) AS minsort
998 FROM {course_categories} cc
999 JOIN {course} c ON c.category = cc.id
1000 GROUP BY cc.id, cc.sortorder, cc.coursecount
1001 HAVING (MAX(c.sortorder) <> cc.sortorder + cc.coursecount) OR (MIN(c.sortorder) <> cc.sortorder + 1)";
1002 $gapcategories = $DB->get_records_sql($sql);
1004 foreach ($gapcategories as $cat) {
1005 if (isset($fixcategories[$cat->id])) {
1006 // duplicates detected already
1008 } else if ($cat->minsort == $cat->sortorder and $cat->maxsort == $cat->sortorder + $cat->coursecount - 1) {
1009 // easy - new course inserted with sortorder 0, the rest is ok
1010 $sql = "UPDATE {course}
1011 SET sortorder = sortorder + 1
1012 WHERE category = ?";
1013 $DB->execute($sql, array($cat->id));
1015 } else {
1016 // it needs full resorting
1017 $fixcategories[$cat->id] = $cat;
1019 $cacheevents['changesincourse'] = true;
1021 unset($gapcategories);
1023 // fix course sortorders in problematic categories only
1024 foreach ($fixcategories as $cat) {
1025 $i = 1;
1026 $courses = $DB->get_records('course', array('category'=>$cat->id), 'sortorder ASC, id DESC', 'id, sortorder');
1027 foreach ($courses as $course) {
1028 if ($course->sortorder != $cat->sortorder + $i) {
1029 $course->sortorder = $cat->sortorder + $i;
1030 $DB->update_record_raw('course', $course, true);
1031 $cacheevents['changesincourse'] = true;
1033 $i++;
1037 // advise all caches that need to be rebuilt
1038 foreach (array_keys($cacheevents) as $event) {
1039 cache_helper::purge_by_event($event);
1044 * Internal recursive category verification function, do not use directly!
1046 * @todo Document the arguments of this function better
1048 * @global object
1049 * @uses CONTEXT_COURSECAT
1050 * @param array $children
1051 * @param int $sortorder
1052 * @param string $parent
1053 * @param int $depth
1054 * @param string $path
1055 * @param array $fixcontexts
1056 * @return bool if changes were made
1058 function _fix_course_cats($children, &$sortorder, $parent, $depth, $path, &$fixcontexts) {
1059 global $DB;
1061 $depth++;
1062 $changesmade = false;
1064 foreach ($children as $cat) {
1065 $sortorder = $sortorder + get_max_courses_in_category();
1066 $update = false;
1067 if ($parent != $cat->parent or $depth != $cat->depth or $path.'/'.$cat->id != $cat->path) {
1068 $cat->parent = $parent;
1069 $cat->depth = $depth;
1070 $cat->path = $path.'/'.$cat->id;
1071 $update = true;
1073 // make sure context caches are rebuild and dirty contexts marked
1074 $context = context_coursecat::instance($cat->id);
1075 $fixcontexts[$context->id] = $context;
1077 if ($cat->sortorder != $sortorder) {
1078 $cat->sortorder = $sortorder;
1079 $update = true;
1081 if ($update) {
1082 $DB->update_record('course_categories', $cat, true);
1083 $changesmade = true;
1085 if (isset($cat->children)) {
1086 if (_fix_course_cats($cat->children, $sortorder, $cat->id, $cat->depth, $cat->path, $fixcontexts)) {
1087 $changesmade = true;
1091 return $changesmade;
1095 * List of remote courses that a user has access to via MNET.
1096 * Works only on the IDP
1098 * @global object
1099 * @global object
1100 * @param int @userid The user id to get remote courses for
1101 * @return array Array of {@link $COURSE} of course objects
1103 function get_my_remotecourses($userid=0) {
1104 global $DB, $USER;
1106 if (empty($userid)) {
1107 $userid = $USER->id;
1110 // we can not use SELECT DISTINCT + text field (summary) because of MS SQL and Oracle, subselect used therefore
1111 $sql = "SELECT c.id, c.remoteid, c.shortname, c.fullname,
1112 c.hostid, c.summary, c.summaryformat, c.categoryname AS cat_name,
1113 h.name AS hostname
1114 FROM {mnetservice_enrol_courses} c
1115 JOIN (SELECT DISTINCT hostid, remotecourseid
1116 FROM {mnetservice_enrol_enrolments}
1117 WHERE userid = ?
1118 ) e ON (e.hostid = c.hostid AND e.remotecourseid = c.remoteid)
1119 JOIN {mnet_host} h ON h.id = c.hostid";
1121 return $DB->get_records_sql($sql, array($userid));
1125 * List of remote hosts that a user has access to via MNET.
1126 * Works on the SP
1128 * @global object
1129 * @global object
1130 * @return array|bool Array of host objects or false
1132 function get_my_remotehosts() {
1133 global $CFG, $USER;
1135 if ($USER->mnethostid == $CFG->mnet_localhost_id) {
1136 return false; // Return nothing on the IDP
1138 if (!empty($USER->mnet_foreign_host_array) && is_array($USER->mnet_foreign_host_array)) {
1139 return $USER->mnet_foreign_host_array;
1141 return false;
1146 * Returns a menu of all available scales from the site as well as the given course
1148 * @global object
1149 * @param int $courseid The id of the course as found in the 'course' table.
1150 * @return array
1152 function get_scales_menu($courseid=0) {
1153 global $DB;
1155 $sql = "SELECT id, name, courseid
1156 FROM {scale}
1157 WHERE courseid = 0 or courseid = ?
1158 ORDER BY courseid ASC, name ASC";
1159 $params = array($courseid);
1160 $scales = array();
1161 $results = $DB->get_records_sql($sql, $params);
1162 foreach ($results as $index => $record) {
1163 $context = empty($record->courseid) ? context_system::instance() : context_course::instance($record->courseid);
1164 $scales[$index] = format_string($record->name, false, ["context" => $context]);
1166 // Format: [id => 'scale name'].
1167 return $scales;
1171 * Increment standard revision field.
1173 * The revision are based on current time and are incrementing.
1174 * There is a protection for runaway revisions, it may not go further than
1175 * one hour into future.
1177 * The field has to be XMLDB_TYPE_INTEGER with size 10.
1179 * @param string $table
1180 * @param string $field name of the field containing revision
1181 * @param string $select use empty string when updating all records
1182 * @param array $params optional select parameters
1184 function increment_revision_number($table, $field, $select, array $params = null) {
1185 global $DB;
1187 $now = time();
1188 $sql = "UPDATE {{$table}}
1189 SET $field = (CASE
1190 WHEN $field IS NULL THEN $now
1191 WHEN $field < $now THEN $now
1192 WHEN $field > $now + 3600 THEN $now
1193 ELSE $field + 1 END)";
1194 if ($select) {
1195 $sql = $sql . " WHERE $select";
1197 $DB->execute($sql, $params);
1201 /// MODULE FUNCTIONS /////////////////////////////////////////////////
1204 * Just gets a raw list of all modules in a course
1206 * @global object
1207 * @param int $courseid The id of the course as found in the 'course' table.
1208 * @return array
1210 function get_course_mods($courseid) {
1211 global $DB;
1213 if (empty($courseid)) {
1214 return false; // avoid warnings
1217 return $DB->get_records_sql("SELECT cm.*, m.name as modname
1218 FROM {modules} m, {course_modules} cm
1219 WHERE cm.course = ? AND cm.module = m.id AND m.visible = 1",
1220 array($courseid)); // no disabled mods
1225 * Given an id of a course module, finds the coursemodule description
1227 * Please note that this function performs 1-2 DB queries. When possible use cached
1228 * course modinfo. For example get_fast_modinfo($courseorid)->get_cm($cmid)
1229 * See also {@link cm_info::get_course_module_record()}
1231 * @global object
1232 * @param string $modulename name of module type, eg. resource, assignment,... (optional, slower and less safe if not specified)
1233 * @param int $cmid course module id (id in course_modules table)
1234 * @param int $courseid optional course id for extra validation
1235 * @param bool $sectionnum include relative section number (0,1,2 ...)
1236 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1237 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1238 * MUST_EXIST means throw exception if no record or multiple records found
1239 * @return stdClass
1241 function get_coursemodule_from_id($modulename, $cmid, $courseid=0, $sectionnum=false, $strictness=IGNORE_MISSING) {
1242 global $DB;
1244 $params = array('cmid'=>$cmid);
1246 if (!$modulename) {
1247 if (!$modulename = $DB->get_field_sql("SELECT md.name
1248 FROM {modules} md
1249 JOIN {course_modules} cm ON cm.module = md.id
1250 WHERE cm.id = :cmid", $params, $strictness)) {
1251 return false;
1253 } else {
1254 if (!core_component::is_valid_plugin_name('mod', $modulename)) {
1255 throw new coding_exception('Invalid modulename parameter');
1259 $params['modulename'] = $modulename;
1261 $courseselect = "";
1262 $sectionfield = "";
1263 $sectionjoin = "";
1265 if ($courseid) {
1266 $courseselect = "AND cm.course = :courseid";
1267 $params['courseid'] = $courseid;
1270 if ($sectionnum) {
1271 $sectionfield = ", cw.section AS sectionnum";
1272 $sectionjoin = "LEFT JOIN {course_sections} cw ON cw.id = cm.section";
1275 $sql = "SELECT cm.*, m.name, md.name AS modname $sectionfield
1276 FROM {course_modules} cm
1277 JOIN {modules} md ON md.id = cm.module
1278 JOIN {".$modulename."} m ON m.id = cm.instance
1279 $sectionjoin
1280 WHERE cm.id = :cmid AND md.name = :modulename
1281 $courseselect";
1283 return $DB->get_record_sql($sql, $params, $strictness);
1287 * Given an instance number of a module, finds the coursemodule description
1289 * Please note that this function performs DB query. When possible use cached course
1290 * modinfo. For example get_fast_modinfo($courseorid)->instances[$modulename][$instance]
1291 * See also {@link cm_info::get_course_module_record()}
1293 * @global object
1294 * @param string $modulename name of module type, eg. resource, assignment,...
1295 * @param int $instance module instance number (id in resource, assignment etc. table)
1296 * @param int $courseid optional course id for extra validation
1297 * @param bool $sectionnum include relative section number (0,1,2 ...)
1298 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1299 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1300 * MUST_EXIST means throw exception if no record or multiple records found
1301 * @return stdClass
1303 function get_coursemodule_from_instance($modulename, $instance, $courseid=0, $sectionnum=false, $strictness=IGNORE_MISSING) {
1304 global $DB;
1306 if (!core_component::is_valid_plugin_name('mod', $modulename)) {
1307 throw new coding_exception('Invalid modulename parameter');
1310 $params = array('instance'=>$instance, 'modulename'=>$modulename);
1312 $courseselect = "";
1313 $sectionfield = "";
1314 $sectionjoin = "";
1316 if ($courseid) {
1317 $courseselect = "AND cm.course = :courseid";
1318 $params['courseid'] = $courseid;
1321 if ($sectionnum) {
1322 $sectionfield = ", cw.section AS sectionnum";
1323 $sectionjoin = "LEFT JOIN {course_sections} cw ON cw.id = cm.section";
1326 $sql = "SELECT cm.*, m.name, md.name AS modname $sectionfield
1327 FROM {course_modules} cm
1328 JOIN {modules} md ON md.id = cm.module
1329 JOIN {".$modulename."} m ON m.id = cm.instance
1330 $sectionjoin
1331 WHERE m.id = :instance AND md.name = :modulename
1332 $courseselect";
1334 return $DB->get_record_sql($sql, $params, $strictness);
1338 * Returns all course modules of given activity in course
1340 * @param string $modulename The module name (forum, quiz, etc.)
1341 * @param int $courseid The course id to get modules for
1342 * @param string $extrafields extra fields starting with m.
1343 * @return array Array of results
1345 function get_coursemodules_in_course($modulename, $courseid, $extrafields='') {
1346 global $DB;
1348 if (!core_component::is_valid_plugin_name('mod', $modulename)) {
1349 throw new coding_exception('Invalid modulename parameter');
1352 if (!empty($extrafields)) {
1353 $extrafields = ", $extrafields";
1355 $params = array();
1356 $params['courseid'] = $courseid;
1357 $params['modulename'] = $modulename;
1360 return $DB->get_records_sql("SELECT cm.*, m.name, md.name as modname $extrafields
1361 FROM {course_modules} cm, {modules} md, {".$modulename."} m
1362 WHERE cm.course = :courseid AND
1363 cm.instance = m.id AND
1364 md.name = :modulename AND
1365 md.id = cm.module", $params);
1369 * Returns an array of all the active instances of a particular module in given courses, sorted in the order they are defined
1371 * Returns an array of all the active instances of a particular
1372 * module in given courses, sorted in the order they are defined
1373 * in the course. Returns an empty array on any errors.
1375 * The returned objects includle the columns cw.section, cm.visible,
1376 * cm.groupmode, cm.groupingid and cm.lang and are indexed by cm.id.
1378 * @global object
1379 * @global object
1380 * @param string $modulename The name of the module to get instances for
1381 * @param array $courses an array of course objects.
1382 * @param int $userid
1383 * @param int $includeinvisible
1384 * @return array of module instance objects, including some extra fields from the course_modules
1385 * and course_sections tables, or an empty array if an error occurred.
1387 function get_all_instances_in_courses($modulename, $courses, $userid=NULL, $includeinvisible=false) {
1388 global $CFG, $DB;
1390 if (!core_component::is_valid_plugin_name('mod', $modulename)) {
1391 throw new coding_exception('Invalid modulename parameter');
1394 $outputarray = array();
1396 if (empty($courses) || !is_array($courses) || count($courses) == 0) {
1397 return $outputarray;
1400 list($coursessql, $params) = $DB->get_in_or_equal(array_keys($courses), SQL_PARAMS_NAMED, 'c0');
1401 $params['modulename'] = $modulename;
1403 if (!$rawmods = $DB->get_records_sql("SELECT cm.id AS coursemodule, m.*, cw.section, cm.visible AS visible,
1404 cm.groupmode, cm.groupingid, cm.lang
1405 FROM {course_modules} cm, {course_sections} cw, {modules} md,
1406 {".$modulename."} m
1407 WHERE cm.course $coursessql AND
1408 cm.instance = m.id AND
1409 cm.section = cw.id AND
1410 md.name = :modulename AND
1411 md.id = cm.module", $params)) {
1412 return $outputarray;
1415 foreach ($courses as $course) {
1416 $modinfo = get_fast_modinfo($course, $userid);
1418 if (empty($modinfo->instances[$modulename])) {
1419 continue;
1422 foreach ($modinfo->instances[$modulename] as $cm) {
1423 if (!$includeinvisible and !$cm->uservisible) {
1424 continue;
1426 if (!isset($rawmods[$cm->id])) {
1427 continue;
1429 $instance = $rawmods[$cm->id];
1430 if (!empty($cm->extra)) {
1431 $instance->extra = $cm->extra;
1433 $outputarray[] = $instance;
1437 return $outputarray;
1441 * Returns an array of all the active instances of a particular module in a given course,
1442 * sorted in the order they are defined.
1444 * Returns an array of all the active instances of a particular
1445 * module in a given course, sorted in the order they are defined
1446 * in the course. Returns an empty array on any errors.
1448 * The returned objects includle the columns cw.section, cm.visible,
1449 * cm.groupmode, and cm.groupingid, and are indexed by cm.id.
1451 * Simply calls {@link all_instances_in_courses()} with a single provided course
1453 * @param string $modulename The name of the module to get instances for
1454 * @param object $course The course obect.
1455 * @return array of module instance objects, including some extra fields from the course_modules
1456 * and course_sections tables, or an empty array if an error occurred.
1457 * @param int $userid
1458 * @param int $includeinvisible
1460 function get_all_instances_in_course($modulename, $course, $userid=NULL, $includeinvisible=false) {
1461 return get_all_instances_in_courses($modulename, array($course->id => $course), $userid, $includeinvisible);
1466 * Determine whether a module instance is visible within a course
1468 * Given a valid module object with info about the id and course,
1469 * and the module's type (eg "forum") returns whether the object
1470 * is visible or not according to the 'eye' icon only.
1472 * NOTE: This does NOT take into account visibility to a particular user.
1473 * To get visibility access for a specific user, use get_fast_modinfo, get a
1474 * cm_info object from this, and check the ->uservisible property; or use
1475 * the \core_availability\info_module::is_user_visible() static function.
1477 * @global object
1479 * @param $moduletype Name of the module eg 'forum'
1480 * @param $module Object which is the instance of the module
1481 * @return bool Success
1483 function instance_is_visible($moduletype, $module) {
1484 global $DB;
1486 if (!empty($module->id)) {
1487 $params = array('courseid'=>$module->course, 'moduletype'=>$moduletype, 'moduleid'=>$module->id);
1488 if ($records = $DB->get_records_sql("SELECT cm.instance, cm.visible, cm.groupingid, cm.id, cm.course
1489 FROM {course_modules} cm, {modules} m
1490 WHERE cm.course = :courseid AND
1491 cm.module = m.id AND
1492 m.name = :moduletype AND
1493 cm.instance = :moduleid", $params)) {
1495 foreach ($records as $record) { // there should only be one - use the first one
1496 return $record->visible;
1500 return true; // visible by default!
1504 /// LOG FUNCTIONS /////////////////////////////////////////////////////
1507 * Get instance of log manager.
1509 * @param bool $forcereload
1510 * @return \core\log\manager
1512 function get_log_manager($forcereload = false) {
1513 /** @var \core\log\manager $singleton */
1514 static $singleton = null;
1516 if ($forcereload and isset($singleton)) {
1517 $singleton->dispose();
1518 $singleton = null;
1521 if (isset($singleton)) {
1522 return $singleton;
1525 $classname = '\tool_log\log\manager';
1526 if (defined('LOG_MANAGER_CLASS')) {
1527 $classname = LOG_MANAGER_CLASS;
1530 if (!class_exists($classname)) {
1531 if (!empty($classname)) {
1532 debugging("Cannot find log manager class '$classname'.", DEBUG_DEVELOPER);
1534 $classname = '\core\log\dummy_manager';
1537 $singleton = new $classname();
1538 return $singleton;
1542 * Add an entry to the config log table.
1544 * These are "action" focussed rather than web server hits,
1545 * and provide a way to easily reconstruct changes to Moodle configuration.
1547 * @package core
1548 * @category log
1549 * @global moodle_database $DB
1550 * @global stdClass $USER
1551 * @param string $name The name of the configuration change action
1552 For example 'filter_active' when activating or deactivating a filter
1553 * @param string $oldvalue The config setting's previous value
1554 * @param string $value The config setting's new value
1555 * @param string $plugin Plugin name, for example a filter name when changing filter configuration
1556 * @return void
1558 function add_to_config_log($name, $oldvalue, $value, $plugin) {
1559 global $USER, $DB;
1561 $log = new stdClass();
1562 // Use 0 as user id during install.
1563 $log->userid = during_initial_install() ? 0 : $USER->id;
1564 $log->timemodified = time();
1565 $log->name = $name;
1566 $log->oldvalue = $oldvalue;
1567 $log->value = $value;
1568 $log->plugin = $plugin;
1570 $id = $DB->insert_record('config_log', $log);
1572 $event = core\event\config_log_created::create(array(
1573 'objectid' => $id,
1574 'userid' => $log->userid,
1575 'context' => \context_system::instance(),
1576 'other' => array(
1577 'name' => $log->name,
1578 'oldvalue' => $log->oldvalue,
1579 'value' => $log->value,
1580 'plugin' => $log->plugin
1583 $event->trigger();
1587 * Store user last access times - called when use enters a course or site
1589 * @package core
1590 * @category log
1591 * @global stdClass $USER
1592 * @global stdClass $CFG
1593 * @global moodle_database $DB
1594 * @uses LASTACCESS_UPDATE_SECS
1595 * @uses SITEID
1596 * @param int $courseid empty courseid means site
1597 * @return void
1599 function user_accesstime_log($courseid=0) {
1600 global $USER, $CFG, $DB;
1602 if (!isloggedin() or \core\session\manager::is_loggedinas()) {
1603 // no access tracking
1604 return;
1607 if (isguestuser()) {
1608 // Do not update guest access times/ips for performance.
1609 return;
1612 if (defined('USER_KEY_LOGIN') && USER_KEY_LOGIN === true) {
1613 // Do not update user login time when using user key login.
1614 return;
1617 if (empty($courseid)) {
1618 $courseid = SITEID;
1621 $timenow = time();
1623 /// Store site lastaccess time for the current user
1624 if ($timenow - $USER->lastaccess > LASTACCESS_UPDATE_SECS) {
1625 /// Update $USER->lastaccess for next checks
1626 $USER->lastaccess = $timenow;
1628 $last = new stdClass();
1629 $last->id = $USER->id;
1630 $last->lastip = getremoteaddr();
1631 $last->lastaccess = $timenow;
1633 $DB->update_record_raw('user', $last);
1636 if ($courseid == SITEID) {
1637 /// no user_lastaccess for frontpage
1638 return;
1641 /// Store course lastaccess times for the current user
1642 if (empty($USER->currentcourseaccess[$courseid]) or ($timenow - $USER->currentcourseaccess[$courseid] > LASTACCESS_UPDATE_SECS)) {
1644 $lastaccess = $DB->get_field('user_lastaccess', 'timeaccess', array('userid'=>$USER->id, 'courseid'=>$courseid));
1646 if ($lastaccess === false) {
1647 // Update course lastaccess for next checks
1648 $USER->currentcourseaccess[$courseid] = $timenow;
1650 $last = new stdClass();
1651 $last->userid = $USER->id;
1652 $last->courseid = $courseid;
1653 $last->timeaccess = $timenow;
1654 try {
1655 $DB->insert_record_raw('user_lastaccess', $last, false);
1656 } catch (dml_write_exception $e) {
1657 // During a race condition we can fail to find the data, then it appears.
1658 // If we still can't find it, rethrow the exception.
1659 $lastaccess = $DB->get_field('user_lastaccess', 'timeaccess', array('userid' => $USER->id,
1660 'courseid' => $courseid));
1661 if ($lastaccess === false) {
1662 throw $e;
1664 // If we did find it, the race condition was true and another thread has inserted the time for us.
1665 // We can just continue without having to do anything.
1668 } else if ($timenow - $lastaccess < LASTACCESS_UPDATE_SECS) {
1669 // no need to update now, it was updated recently in concurrent login ;-)
1671 } else {
1672 // Update course lastaccess for next checks
1673 $USER->currentcourseaccess[$courseid] = $timenow;
1675 $DB->set_field('user_lastaccess', 'timeaccess', $timenow, array('userid'=>$USER->id, 'courseid'=>$courseid));
1680 /// GENERAL HELPFUL THINGS ///////////////////////////////////
1683 * Dumps a given object's information for debugging purposes. (You can actually use this function
1684 * to print any type of value such as arrays or simple strings, not just objects.)
1686 * When used in a web script, the object is dumped in a fancy-formatted div.
1688 * When used in a CLI script, the object's information is written to the standard error output
1689 * stream.
1691 * When used in an AJAX script, the object's information is dumped to the server error log.
1693 * In text mode, private fields are shown with * and protected with +.
1695 * In web view, formatting is done with Bootstrap classes. You can hover over some items to see
1696 * more information, such as value types or access controls, or full field names if the names get
1697 * cut off.
1699 * By default, this will recurse to child objects, except where that would result in infinite
1700 * recursion. To change that, set $expandclasses to an empty array (= do not recurse) or to a list
1701 * of the class names that you would like to expand. You can also set values in this array to a
1702 * regular expression beginning with / if you want to match a range of classes.
1704 * @param mixed $item Object, array, or other item to display
1705 * @param string[] $expandclasses Optional list of class patterns to recurse to
1706 * @param bool $textonly If true, outputs text-only (automatically set for CLI and AJAX)
1707 * @param bool $return For internal use - if true, returns value instead of echoing it
1708 * @param int $depth For internal use - depth of recursion within print_object call
1709 * @param \stdClass[] $done For internal use - array listing already-printed objects
1710 * @return string HTML code (or text if CLI) to display, if $return is true, otherwise empty string
1712 function print_object($item, array $expandclasses = ['/./'], bool $textonly = false, bool $return = false,
1713 int $depth = 0, array $done = []): string {
1714 // We may need a lot of memory here.
1715 raise_memory_limit(MEMORY_EXTRA);
1717 // Set text (instead of HTML) mode if in CLI or AJAX script.
1718 if (CLI_SCRIPT || AJAX_SCRIPT) {
1719 $textonly = true;
1723 * Gets styling for types of variable.
1725 * @param mixed $item Arbitrary PHP variable (simple primitive type) to display
1726 * @return string Bootstrap class for styling the display
1728 $gettypestyle = function($item): string {
1729 switch (gettype($item)) {
1730 case 'NULL':
1731 case 'boolean':
1732 return 'font-italic';
1733 case 'integer':
1734 case 'double':
1735 return 'text-primary';
1736 case 'string' :
1737 return 'text-success';
1738 default:
1739 return '';
1744 * Formats and escapes the text for the contents of a variable.
1746 * @param mixed $item Arbitrary PHP variable (simple primitive type) to display
1747 * @return string Contents as text
1749 $getobjectstr = function($item) use($textonly): string {
1750 if (is_null($item)) {
1751 return 'null';
1753 $objectstr = (string)$item;
1754 if (is_string($item)) {
1755 // Quotes around strings.
1756 $objectstr = "'$objectstr'";
1757 } else if (is_bool($item)) {
1758 // Show true or false for bools.
1759 $objectstr = $item ? 'true' : 'false';
1760 } else if (is_float($item)) {
1761 // Add 'f' for floats.
1762 $objectstr = $item . 'f';
1764 if ($textonly) {
1765 return $objectstr;
1766 } else {
1767 return s($objectstr);
1771 if ($textonly) {
1772 $out = '';
1773 } else {
1774 $notype = false;
1775 $cssclass = $gettypestyle($item);
1776 if (is_object($item) || is_array($item)) {
1777 // For object and array, don't show the title on hover - it makes no sense because
1778 // they're big, plus we already show the word 'array' or the object type.
1779 $notype = true;
1780 // Add a fancy box, with alternating colour, around the object and non-empty array.
1781 if (is_object($item) || count($item) > 0) {
1782 if (($depth & 1) === 0) {
1783 $cssclass .= ' bg-white rounded p-2';
1784 } else {
1785 $cssclass .= ' bg-light rounded p-2';
1789 if ($depth === 0) {
1790 // The top-level object being printed has print-object class in case anyone wants to
1791 // do extra styling.
1792 $cssclass .= ' print-object';
1794 $attributes = [];
1795 if (!$notype) {
1796 // We show the item type on hover. Note there is no need to include the actual value
1797 // in the title attribute here, because the full text will be displayed anyway with
1798 // wrapping if needed..
1799 $attributes['title'] = gettype($item);
1801 $out = html_writer::start_div($cssclass, $attributes);
1804 // Depending on the level of nesting, we allocate a slightly different proportion (ranging
1805 // from 2/12 to 5/12) of the available width for the key names.
1806 $bsdepth = floor(min(6, $depth) / 2);
1807 $bootstrapdt = 'col-sm-' . ($bsdepth + 2);
1808 $bootstrapdd = 'col-sm-' . (12 - ($bsdepth + 2));
1810 // This main code handles objects and arrays.
1811 if (is_array($item) || is_object($item)) {
1812 if (is_object($item)) {
1813 // Object header: class name.
1814 if ($textonly) {
1815 $out .= '[' . get_class($item) . ']';
1816 } else {
1817 // Objects display the class name as a badge. Content goes within a <dl>.
1818 $badge = html_writer::span(get_class($item), 'badge bg-primary text-white');
1819 $out .= html_writer::tag('h5', $badge);
1820 $out .= html_writer::start_tag('dl', ['class' => 'row']);
1821 $dl = true;
1823 // Record that we have output this object already (to prevent circular refs).
1824 $done[] = $item;
1825 $object = true;
1826 // Cast to array so we can loop through all properties.
1827 $item = (array)$item;
1828 } else {
1829 // Array header: 'array' and a count.
1830 $arrayinfo = 'array (' . count($item) . ')';
1831 if ($textonly) {
1832 $out .= $arrayinfo;
1833 } else {
1834 // Arrays show the same as objects but the badge is grey.
1835 $badge = html_writer::span($arrayinfo, 'badge bg-secondary text-dark');
1836 // Decide if there will be a <dl> tag - only if there is some content.
1837 $dl = count($item) > 0;
1838 $attributes = [];
1839 if (!$dl) {
1840 // When there is no content inside the array, don't show bottom margin on heading.
1841 $attributes['class'] = 'mb-0';
1843 $out .= html_writer::tag('h5', $badge, $attributes);
1844 if ($dl) {
1845 $out .= html_writer::start_tag('dl', ['class' => 'row']);
1848 $object = false;
1851 // Properties.
1852 foreach ($item as $key => $value) {
1853 // Detect private and protected variables.
1854 $matches = [];
1855 $stringkey = (string)$key;
1856 if (preg_match('~^\x00(.*)\x00(.*)$~', $stringkey, $matches)) {
1857 $shortkey = $matches[2];
1858 $access = $matches[1] == '*' ? 'protected' : 'private';
1859 } else {
1860 $shortkey = $stringkey;
1861 $access = 'public';
1863 if ($textonly) {
1864 switch ($access) {
1865 case 'protected' :
1866 $shortkey = '+' . $shortkey;
1867 break;
1868 case 'private' :
1869 $shortkey = '*' . $shortkey;
1870 break;
1872 $out .= PHP_EOL . ' ' . $shortkey . ' = ';
1873 } else {
1874 switch ($access) {
1875 case 'protected':
1876 // Protected is in normal font.
1877 $bootstrapstyle = ' font-weight-normal';
1878 break;
1879 case 'private':
1880 // Private is italic.
1881 $bootstrapstyle = ' font-weight-normal font-italic';
1882 break;
1883 default:
1884 // Public is bold, same for array keys.
1885 $bootstrapstyle = '';
1886 break;
1888 $attributes = ['class' => $bootstrapdt . ' text-truncate' . $bootstrapstyle];
1889 if ($object) {
1890 // For an object property, the title is the full text of the key (in case it
1891 // gets cut off) and the access modifier.
1892 $attributes['title'] = s($shortkey) . ' (' . $access . ')';
1893 $objectstr = s($shortkey);
1894 } else {
1895 // For an array key, the title is the full text of the key (in case it gets
1896 // cut off) and the type of the key. Array keys can't have an access modifier.
1897 $attributes['title'] = s($shortkey) . ' (' . gettype($key) . ')';
1898 // Array keys are styled according to the normal styling for that type.
1899 $typestyle = $gettypestyle($key);
1900 if ($typestyle) {
1901 $attributes['class'] .= ' ' . $typestyle;
1903 // Array keys also use a special object string e.g. 'true' for bool, quoted.
1904 $objectstr = $getobjectstr($key);
1906 $out .= html_writer::tag('dt', $objectstr, $attributes);
1908 // Consider how to display the value for this key.
1909 $extraclass = '';
1910 switch (gettype($value)) {
1911 case 'object' :
1912 $objclass = get_class($value);
1914 // See if we printed it further up the tree in which case
1915 // it will definitely not be printed (infinite recursion).
1916 if (in_array($value, $done)) {
1917 if ($textonly) {
1918 $display = '[circular reference: ' . $objclass . ']';
1919 } else {
1920 $display = '[circular reference: ' . s($objclass) . ']';
1921 $extraclass = ' text-danger';
1923 break;
1926 // Recurse only to specified types.
1927 $recurse = false;
1928 foreach ($expandclasses as $pattern) {
1929 if (substr($pattern, 0, 1) === '/') {
1930 // Allow regular expressions beginning with a / symbol.
1931 if (preg_match($pattern, $objclass)) {
1932 $recurse = true;
1933 break;
1935 } else {
1936 // Other strings must be exact match.
1937 if ($objclass === $pattern) {
1938 $recurse = true;
1939 break;
1944 if ($recurse) {
1945 // Recursively display the object.
1946 $display = print_object($value, $expandclasses, $textonly, true, $depth + 1, $done);
1947 if ($textonly) {
1948 // Indent by adding spaces after each LF.
1949 $display = str_replace(PHP_EOL, PHP_EOL . ' ', $display);
1951 } else {
1952 // Do not display the object, just a marker in square breackets.
1953 if ($textonly) {
1954 $display = '[object: ' . $objclass . ']';
1955 } else {
1956 $display = '[object: ' . s($objclass) . ']';
1959 break;
1961 case 'array' :
1962 // Recursively display the array.
1963 $display = print_object($value, $expandclasses, $textonly, true, $depth + 1, $done);
1964 if ($textonly) {
1965 // Indent by adding spaces after each LF.
1966 $display = str_replace(PHP_EOL, PHP_EOL . ' ', $display);
1968 break;
1970 default:
1971 // Plain value - recurse to display.
1972 $display = print_object($value, [], $textonly, true, $depth + 1);
1973 break;
1975 if ($textonly) {
1976 $out .= $display;
1977 } else {
1978 $out .= html_writer::tag('dd', $display, ['class' => $bootstrapdd . $extraclass]);
1981 if (!$textonly && $dl) {
1982 $out .= html_writer::end_tag('dl');
1984 } else {
1985 // For things which are not objects or arrays, just convert to string for display.
1986 $out .= $getobjectstr($item);
1989 if (!$textonly) {
1990 $out .= html_writer::end_div();
1993 // Display or return result.
1994 if ($return) {
1995 return $out;
1996 } else {
1997 if (CLI_SCRIPT) {
1998 fwrite(STDERR, $out);
1999 fwrite(STDERR, PHP_EOL);
2000 } else if (AJAX_SCRIPT) {
2001 foreach (explode(PHP_EOL, $out) as $line) {
2002 error_log($line);
2004 } else {
2005 if ($textonly) {
2006 $out = html_writer::tag('pre', s($out));
2008 echo $out . "\n";
2010 return '';
2015 * This function is the official hook inside XMLDB stuff to delegate its debug to one
2016 * external function.
2018 * Any script can avoid calls to this function by defining XMLDB_SKIP_DEBUG_HOOK before
2019 * using XMLDB classes. Obviously, also, if this function doesn't exist, it isn't invoked ;-)
2021 * @uses DEBUG_DEVELOPER
2022 * @param string $message string contains the error message
2023 * @param object $object object XMLDB object that fired the debug
2025 function xmldb_debug($message, $object) {
2027 debugging($message, DEBUG_DEVELOPER);
2031 * @global object
2032 * @uses CONTEXT_COURSECAT
2033 * @return boolean Whether the user can create courses in any category in the system.
2035 function user_can_create_courses() {
2036 global $DB;
2037 $catsrs = $DB->get_recordset('course_categories');
2038 foreach ($catsrs as $cat) {
2039 if (has_capability('moodle/course:create', context_coursecat::instance($cat->id))) {
2040 $catsrs->close();
2041 return true;
2044 $catsrs->close();
2045 return false;
2049 * This method can update the values in mulitple database rows for a colum with
2050 * a unique index, without violating that constraint.
2052 * Suppose we have a table with a unique index on (otherid, sortorder), and
2053 * for a particular value of otherid, we want to change all the sort orders.
2054 * You have to do this carefully or you will violate the unique index at some time.
2055 * This method takes care of the details for you.
2057 * Note that, it is the responsibility of the caller to make sure that the
2058 * requested rename is legal. For example, if you ask for [1 => 2, 2 => 2]
2059 * then you will get a unique key violation error from the database.
2061 * @param string $table The database table to modify.
2062 * @param string $field the field that contains the values we are going to change.
2063 * @param array $newvalues oldvalue => newvalue how to change the values.
2064 * E.g. [1 => 4, 2 => 1, 3 => 3, 4 => 2].
2065 * @param array $otherconditions array fieldname => requestedvalue extra WHERE clause
2066 * conditions to restrict which rows are affected. E.g. array('otherid' => 123).
2067 * @param int $unusedvalue (defaults to -1) a value that is never used in $ordercol.
2069 function update_field_with_unique_index($table, $field, array $newvalues,
2070 array $otherconditions, $unusedvalue = -1) {
2071 global $DB;
2072 $safechanges = decompose_update_into_safe_changes($newvalues, $unusedvalue);
2074 $transaction = $DB->start_delegated_transaction();
2075 foreach ($safechanges as $change) {
2076 list($from, $to) = $change;
2077 $otherconditions[$field] = $from;
2078 $DB->set_field($table, $field, $to, $otherconditions);
2080 $transaction->allow_commit();
2084 * Helper used by {@link update_field_with_unique_index()}. Given a desired
2085 * set of changes, break them down into single udpates that can be done one at
2086 * a time without breaking any unique index constraints.
2088 * Suppose the input is array(1 => 2, 2 => 1) and -1. Then the output will be
2089 * array (array(1, -1), array(2, 1), array(-1, 2)). This function solves this
2090 * problem in the general case, not just for simple swaps. The unit tests give
2091 * more examples.
2093 * Note that, it is the responsibility of the caller to make sure that the
2094 * requested rename is legal. For example, if you ask for something impossible
2095 * like array(1 => 2, 2 => 2) then the results are undefined. (You will probably
2096 * get a unique key violation error from the database later.)
2098 * @param array $newvalues The desired re-ordering.
2099 * E.g. array(1 => 4, 2 => 1, 3 => 3, 4 => 2).
2100 * @param int $unusedvalue A value that is not currently used.
2101 * @return array A safe way to perform the re-order. An array of two-element
2102 * arrays array($from, $to).
2103 * E.g. array(array(1, -1), array(2, 1), array(4, 2), array(-1, 4)).
2105 function decompose_update_into_safe_changes(array $newvalues, $unusedvalue) {
2106 $nontrivialmap = array();
2107 foreach ($newvalues as $from => $to) {
2108 if ($from == $unusedvalue || $to == $unusedvalue) {
2109 throw new \coding_exception('Supposedly unused value ' . $unusedvalue . ' is actually used!');
2111 if ($from != $to) {
2112 $nontrivialmap[$from] = $to;
2116 if (empty($nontrivialmap)) {
2117 return array();
2120 // First we deal with all renames that are not part of cycles.
2121 // This bit is O(n^2) and it ought to be possible to do better,
2122 // but it does not seem worth the effort.
2123 $safechanges = array();
2124 $nontrivialmapchanged = true;
2125 while ($nontrivialmapchanged) {
2126 $nontrivialmapchanged = false;
2128 foreach ($nontrivialmap as $from => $to) {
2129 if (array_key_exists($to, $nontrivialmap)) {
2130 continue; // Cannot currenly do this rename.
2132 // Is safe to do this rename now.
2133 $safechanges[] = array($from, $to);
2134 unset($nontrivialmap[$from]);
2135 $nontrivialmapchanged = true;
2139 // Are we done?
2140 if (empty($nontrivialmap)) {
2141 return $safechanges;
2144 // Now what is left in $nontrivialmap must be a permutation,
2145 // which must be a combination of disjoint cycles. We need to break them.
2146 while (!empty($nontrivialmap)) {
2147 // Extract the first cycle.
2148 reset($nontrivialmap);
2149 $current = $cyclestart = key($nontrivialmap);
2150 $cycle = array();
2151 do {
2152 $cycle[] = $current;
2153 $next = $nontrivialmap[$current];
2154 unset($nontrivialmap[$current]);
2155 $current = $next;
2156 } while ($current != $cyclestart);
2158 // Now convert it to a sequence of safe renames by using a temp.
2159 $safechanges[] = array($cyclestart, $unusedvalue);
2160 $cycle[0] = $unusedvalue;
2161 $to = $cyclestart;
2162 while ($from = array_pop($cycle)) {
2163 $safechanges[] = array($from, $to);
2164 $to = $from;
2168 return $safechanges;
2172 * Return maximum number of courses in a category
2174 * @uses MAX_COURSES_IN_CATEGORY
2175 * @return int number of courses
2177 function get_max_courses_in_category() {
2178 global $CFG;
2179 // Use default MAX_COURSES_IN_CATEGORY if $CFG->maxcoursesincategory is not set or invalid.
2180 if (!isset($CFG->maxcoursesincategory) || clean_param($CFG->maxcoursesincategory, PARAM_INT) == 0) {
2181 return MAX_COURSES_IN_CATEGORY;
2182 } else {
2183 return $CFG->maxcoursesincategory;
2188 * Prepare a safe ORDER BY statement from user interactable requests.
2190 * This allows safe user specified sorting (ORDER BY), by abstracting the SQL from the value being requested by the user.
2191 * A standard string (and optional direction) can be specified, which will be mapped to a predefined allow list of SQL ordering.
2192 * The mapping can optionally include a 'default', which will be used if the key provided is invalid.
2194 * Example usage:
2195 * -If $orderbymap = [
2196 * 'courseid' => 'c.id',
2197 * 'somecustomvalue'=> 'c.startdate, c.shortname',
2198 * 'default' => 'c.fullname',
2200 * -A value from the map array's keys can be passed in by a user interaction (eg web service) along with an optional direction.
2201 * -get_safe_orderby($orderbymap, 'courseid', 'DESC') would return: ORDER BY c.id DESC
2202 * -get_safe_orderby($orderbymap, 'somecustomvalue') would return: ORDER BY c.startdate, c.shortname
2203 * -get_safe_orderby($orderbymap, 'invalidblah', 'DESC') would return: ORDER BY c.fullname DESC
2204 * -If no default key was specified in $orderbymap, the invalidblah example above would return empty string.
2206 * @param array $orderbymap An array in the format [keystring => sqlstring]. A default fallback can be set with the key 'default'.
2207 * @param string $orderbykey A string to be mapped to a key in $orderbymap.
2208 * @param string $direction Optional ORDER BY direction (ASC/DESC, case insensitive).
2209 * @param bool $useprefix Whether ORDER BY is prefixed to the output (true by default). This should not be modified in most cases.
2210 * It is included to enable get_safe_orderby_multiple() to use this function multiple times.
2211 * @return string The ORDER BY statement, or empty string if $orderbykey is invalid and no default is mapped.
2213 function get_safe_orderby(array $orderbymap, string $orderbykey, string $direction = '', bool $useprefix = true): string {
2214 $orderby = $useprefix ? ' ORDER BY ' : '';
2215 $output = '';
2217 // Only include an order direction if ASC/DESC is explicitly specified (case insensitive).
2218 $direction = strtoupper($direction);
2219 if (!in_array($direction, ['ASC', 'DESC'], true)) {
2220 $direction = '';
2221 } else {
2222 $direction = " {$direction}";
2225 // Prepare the statement if the key maps to a defined sort parameter.
2226 if (isset($orderbymap[$orderbykey])) {
2227 $output = "{$orderby}{$orderbymap[$orderbykey]}{$direction}";
2228 } else if (array_key_exists('default', $orderbymap)) {
2229 // Fall back to use the default if one is specified.
2230 $output = "{$orderby}{$orderbymap['default']}{$direction}";
2233 return $output;
2237 * Prepare a safe ORDER BY statement from user interactable requests using multiple values.
2239 * This allows safe user specified sorting (ORDER BY) similar to get_safe_orderby(), but supports multiple keys and directions.
2240 * This is useful in cases where combinations of columns are needed and/or each item requires a specified direction (ASC/DESC).
2241 * The mapping can optionally include a 'default', which will be used if the key provided is invalid.
2243 * Example usage:
2244 * -If $orderbymap = [
2245 * 'courseid' => 'c.id',
2246 * 'fullname'=> 'c.fullname',
2247 * 'default' => 'c.startdate',
2249 * -An array of values from the map's keys can be passed in by a user interaction (eg web service), with optional directions.
2250 * -get_safe_orderby($orderbymap, ['courseid', 'fullname'], ['DESC', 'ASC']) would return: ORDER BY c.id DESC, c.fullname ASC
2251 * -get_safe_orderby($orderbymap, ['courseid', 'invalidblah'], ['aaa', 'DESC']) would return: ORDER BY c.id, c.startdate DESC
2252 * -If no default key was specified in $orderbymap, the invalidblah example above would return: ORDER BY c.id
2254 * @param array $orderbymap An array in the format [keystring => sqlstring]. A default fallback can be set with the key 'default'.
2255 * @param array $orderbykeys An array of strings to be mapped to keys in $orderbymap.
2256 * @param array $directions Optional array of ORDER BY direction (ASC/DESC, case insensitive).
2257 * The array keys should match array keys in $orderbykeys.
2258 * @return string The ORDER BY statement, or empty string if $orderbykeys contains no valid items and no default is mapped.
2260 function get_safe_orderby_multiple(array $orderbymap, array $orderbykeys, array $directions = []): string {
2261 $output = '';
2263 // Check each key for a valid mapping and add to the ORDER BY statement (invalid entries will be empty strings).
2264 foreach ($orderbykeys as $index => $orderbykey) {
2265 $direction = $directions[$index] ?? '';
2266 $safeorderby = get_safe_orderby($orderbymap, $orderbykey, $direction, false);
2268 if (!empty($safeorderby)) {
2269 $output .= ", {$safeorderby}";
2273 // Prefix with ORDER BY if any valid ordering is specified (and remove comma from the start).
2274 if (!empty($output)) {
2275 $output = ' ORDER BY' . ltrim($output, ',');
2278 return $output;