Proyectos de Subversion Moodle

<?php

// This file is part of Moodle - http://moodle.org/

//

// Moodle is free software: you can redistribute it and/or modify

// it under the terms of the GNU General Public License as published by

// the Free Software Foundation, either version 3 of the License, or

// (at your option) any later version.

//

// Moodle is distributed in the hope that it will be useful,

// but WITHOUT ANY WARRANTY; without even the implied warranty of

// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

// GNU General Public License for more details.

//

// You should have received a copy of the GNU General Public License

// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

/**

 * Library of functions for database manipulation.

 *

 * Other main libraries:

 * - weblib.php - functions that produce web output

 * - moodlelib.php - general-purpose Moodle functions

 *

 * @package    core

 * @copyright  1999 onwards Martin Dougiamas  {@link http://moodle.com}

 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later

 */

defined('MOODLE_INTERNAL') || die();

/**

 * The maximum courses in a category

 * MAX_COURSES_IN_CATEGORY * MAX_COURSE_CATEGORIES must not be more than max integer!

 */

define('MAX_COURSES_IN_CATEGORY', 10000);

/**

  * The maximum number of course categories

  * MAX_COURSES_IN_CATEGORY * MAX_COURSE_CATEGORIES must not be more than max integer!

  */

define('MAX_COURSE_CATEGORIES', 10000);

/**

 * Number of seconds to wait before updating lastaccess information in DB.

 *

 * We allow overwrites from config.php, useful to ensure coherence in performance

 * tests results.

 *

 * Note: For web service requests in the external_tokens field, we use a different constant

 * webservice::TOKEN_LASTACCESS_UPDATE_SECS.

 */

if (!defined('LASTACCESS_UPDATE_SECS')) {

    define('LASTACCESS_UPDATE_SECS', 60);

}

/**

 * The constant value when we use the search option.

 */

define('USER_SEARCH_STARTS_WITH', 0);

define('USER_SEARCH_CONTAINS', 1);

define('USER_SEARCH_EXACT_MATCH', 2);

/**

 * Returns $user object of the main admin user

 *

 * @static stdClass $mainadmin

 * @return stdClass|false {user} record from DB, false if not found

 */

function get_admin() {

    global $CFG, $DB;

    static $mainadmin = null;

    static $prevadmins = null;

    if (empty($CFG->siteadmins)) {

        // Should not happen on an ordinary site.

        // It does however happen during unit tests.

        return false;

    }

    if (isset($mainadmin) and $prevadmins === $CFG->siteadmins) {

        return clone($mainadmin);

    }

    $mainadmin = null;

    foreach (explode(',', $CFG->siteadmins) as $id) {

        if ($user = $DB->get_record('user', array('id'=>$id, 'deleted'=>0))) {

            $mainadmin = $user;

            break;

        }

    }

    if ($mainadmin) {

        $prevadmins = $CFG->siteadmins;

        return clone($mainadmin);

    } else {

        // this should not happen

        return false;

    }

}

/**

 * Returns list of all admins, using 1 DB query

 *

 * @return array

 */

function get_admins() {

    global $DB, $CFG;

    if (empty($CFG->siteadmins)) {  // Should not happen on an ordinary site

        return array();

    }

    $sql = "SELECT u.*

              FROM {user} u

             WHERE u.deleted = 0 AND u.id IN ($CFG->siteadmins)";

    // We want the same order as in $CFG->siteadmins.

    $records = $DB->get_records_sql($sql);

    $admins = array();

    foreach (explode(',', $CFG->siteadmins) as $id) {

        $id = (int)$id;

        if (!isset($records[$id])) {

            // User does not exist, this should not happen.

            continue;

        }

        $admins[$records[$id]->id] = $records[$id];

    }

    return $admins;

}

/**

 * Search through course users

 *

 * If $coursid specifies the site course then this function searches

 * through all undeleted and confirmed users

 *

 * @global object

 * @uses SITEID

 * @uses SQL_PARAMS_NAMED

 * @uses CONTEXT_COURSE

 * @param int $courseid The course in question.

 * @param int $groupid The group in question.

 * @param string $searchtext The string to search for

 * @param string $sort A field to sort by

 * @param array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10

 * @return array

 */

function search_users($courseid, $groupid, $searchtext, $sort='', array $exceptions=null) {

    global $DB;

    $fullname  = $DB->sql_fullname('u.firstname', 'u.lastname');

    if (!empty($exceptions)) {

        list($exceptions, $params) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex', false);

        $except = "AND u.id $exceptions";

    } else {

        $except = "";

        $params = array();

    }

    if (!empty($sort)) {

        $order = "ORDER BY $sort";

    } else {

        $order = "";

    }

    $select = "u.deleted = 0 AND u.confirmed = 1 AND (".$DB->sql_like($fullname, ':search1', false)." OR ".$DB->sql_like('u.email', ':search2', false).")";

    $params['search1'] = "%$searchtext%";

    $params['search2'] = "%$searchtext%";

    if (!$courseid or $courseid == SITEID) {

        $sql = "SELECT u.id, u.firstname, u.lastname, u.email

                  FROM {user} u

                 WHERE $select

                       $except

                $order";

        return $DB->get_records_sql($sql, $params);

    } else {

        if ($groupid) {

            $sql = "SELECT u.id, u.firstname, u.lastname, u.email

                      FROM {user} u

                      JOIN {groups_members} gm ON gm.userid = u.id

                     WHERE $select AND gm.groupid = :groupid

                           $except

                     $order";

            $params['groupid'] = $groupid;

            return $DB->get_records_sql($sql, $params);

        } else {

            $context = context_course::instance($courseid);

            // We want to query both the current context and parent contexts.

            list($relatedctxsql, $relatedctxparams) = $DB->get_in_or_equal($context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relatedctx');

            $sql = "SELECT u.id, u.firstname, u.lastname, u.email

                      FROM {user} u

                      JOIN {role_assignments} ra ON ra.userid = u.id

                     WHERE $select AND ra.contextid $relatedctxsql

                           $except

                    $order";

            $params = array_merge($params, $relatedctxparams);

            return $DB->get_records_sql($sql, $params);

        }

    }

}

/**

 * Returns SQL used to search through user table to find users (in a query

 * which may also join and apply other conditions).

 *

 * You can combine this SQL with an existing query by adding 'AND $sql' to the

 * WHERE clause of your query (where $sql is the first element in the array

 * returned by this function), and merging in the $params array to the parameters

 * of your query (where $params is the second element). Your query should use

 * named parameters such as :param, rather than the question mark style.

 *

 * There are examples of basic usage in the unit test for this function.

 *

 * @param string $search the text to search for (empty string = find all)

 * @param string $u the table alias for the user table in the query being

 *     built. May be ''.

 * @param int $searchtype If 0(default): searches at start, 1: searches in the middle of names

 *      2: search exact match.

 * @param array $extrafields Array of extra user fields to include in search, must be prefixed with table alias if they are not in

 *     the user table.

 * @param array $exclude Array of user ids to exclude (empty = don't exclude)

 * @param array $includeonly If specified, only returns users that have ids

 *     incldued in this array (empty = don't restrict)

 * @return array an array with two elements, a fragment of SQL to go in the

 *     where clause the query, and an associative array containing any required

 *     parameters (using named placeholders).

 */

function users_search_sql(string $search, string $u = 'u', int $searchtype = USER_SEARCH_STARTS_WITH, array $extrafields = [],

        array $exclude = null, array $includeonly = null): array {

    global $DB, $CFG;

    $params = array();

    $tests = array();

    if ($u) {

        $u .= '.';

    }

    if ($search) {

        $conditions = array(

            $DB->sql_fullname($u . 'firstname', $u . 'lastname'),

            $conditions[] = $u . 'lastname'

        );

        foreach ($extrafields as $field) {

            // Add the table alias for the user table if the field doesn't already have an alias.

            $conditions[] = strpos($field, '.') !== false ? $field : $u . $field;

        }

        switch ($searchtype) {

            case USER_SEARCH_STARTS_WITH:

                // Put a field LIKE 'search%' condition on each field.

                $searchparam = $search . '%';

                break;

            case USER_SEARCH_CONTAINS:

                // Put a field LIKE '$search%' condition on each field.

                $searchparam = '%' . $search . '%';

                break;

            case USER_SEARCH_EXACT_MATCH:

                // Match exact the $search string.

                $searchparam = $search;

                break;

        }

        $i = 0;

        foreach ($conditions as $key => $condition) {

            $conditions[$key] = $DB->sql_like($condition, ":con{$i}00", false, false);

            if ($searchtype === USER_SEARCH_EXACT_MATCH) {

                $conditions[$key] = "$condition = :con{$i}00";

            }

            $params["con{$i}00"] = $searchparam;

            $i++;

        }

        $tests[] = '(' . implode(' OR ', $conditions) . ')';

    }

    // Add some additional sensible conditions.

    $tests[] = $u . "id <> :guestid";

    $params['guestid'] = $CFG->siteguest;

    $tests[] = $u . 'deleted = 0';

    $tests[] = $u . 'confirmed = 1';

    // If we are being asked to exclude any users, do that.

    if (!empty($exclude)) {

        list($usertest, $userparams) = $DB->get_in_or_equal($exclude, SQL_PARAMS_NAMED, 'ex', false);

        $tests[] = $u . 'id ' . $usertest;

        $params = array_merge($params, $userparams);

    }

    // If we are validating a set list of userids, add an id IN (...) test.

    if (!empty($includeonly)) {

        list($usertest, $userparams) = $DB->get_in_or_equal($includeonly, SQL_PARAMS_NAMED, 'val');

        $tests[] = $u . 'id ' . $usertest;

        $params = array_merge($params, $userparams);

    }

    // In case there are no tests, add one result (this makes it easier to combine

    // this with an existing query as you can always add AND $sql).

    if (empty($tests)) {

        $tests[] = '1 = 1';

    }

    // Combing the conditions and return.

    return array(implode(' AND ', $tests), $params);

}

/**

 * This function generates the standard ORDER BY clause for use when generating

 * lists of users. If you don't have a reason to use a different order, then

 * you should use this method to generate the order when displaying lists of users.

 *

 * If the optional $search parameter is passed, then exact matches to the search

 * will be sorted first. For example, suppose you have two users 'Al Zebra' and

 * 'Alan Aardvark'. The default sort is Alan, then Al. If, however, you search for

 * 'Al', then Al will be listed first. (With two users, this is not a big deal,

 * but with thousands of users, it is essential.)

 *

 * The list of fields scanned for exact matches are:

 *  - firstname

 *  - lastname

 *  - $DB->sql_fullname

 *  - those returned by \core_user\fields::get_identity_fields or those included in $customfieldmappings

 *

 * If named parameters are used (which is the default, and highly recommended),

 * then the parameter names are like :usersortexactN, where N is an int.

 *

 * The simplest possible example use is:

 * list($sort, $params) = users_order_by_sql();

 * $sql = 'SELECT * FROM {users} ORDER BY ' . $sort;

 *

 * A more complex example, showing that this sort can be combined with other sorts:

 * list($sort, $sortparams) = users_order_by_sql('u');

 * $sql = "SELECT g.id AS groupid, gg.groupingid, u.id AS userid, u.firstname, u.lastname, u.idnumber, u.username

 *           FROM {groups} g

 *      LEFT JOIN {groupings_groups} gg ON g.id = gg.groupid

 *      LEFT JOIN {groups_members} gm ON g.id = gm.groupid

 *      LEFT JOIN {user} u ON gm.userid = u.id

 *          WHERE g.courseid = :courseid $groupwhere $groupingwhere

 *       ORDER BY g.name, $sort";

 * $params += $sortparams;

 *

 * An example showing the use of $search:

 * list($sort, $sortparams) = users_order_by_sql('u', $search, $this->get_context());

 * $order = ' ORDER BY ' . $sort;

 * $params += $sortparams;

 * $availableusers = $DB->get_records_sql($fields . $sql . $order, $params, $page*$perpage, $perpage);

 *

 * @param string $usertablealias (optional) any table prefix for the {users} table. E.g. 'u'.

 * @param string $search (optional) a current search string. If given,

 *      any exact matches to this string will be sorted first.

 * @param context|null $context the context we are in. Used by \core_user\fields::get_identity_fields.

 *      Defaults to $PAGE->context.

 * @param array $customfieldmappings associative array of mappings for custom fields returned by \core_user\fields::get_sql.

 * @return array with two elements:

 *      string SQL fragment to use in the ORDER BY clause. For example, "firstname, lastname".

 *      array of parameters used in the SQL fragment. If $search is not given, this is guaranteed to be an empty array.

 */

function users_order_by_sql(string $usertablealias = '', string $search = null, context $context = null,

        array $customfieldmappings = []) {

    global $DB, $PAGE;

    if ($usertablealias) {

        $tableprefix = $usertablealias . '.';

    } else {

        $tableprefix = '';

    }

    $sort = "{$tableprefix}lastname, {$tableprefix}firstname, {$tableprefix}id";

    $params = array();

    if (!$search) {

        return array($sort, $params);

    }

    if (!$context) {

        $context = $PAGE->context;

    }

    $exactconditions = array();

    $paramkey = 'usersortexact1';

    $exactconditions[] = $DB->sql_fullname($tableprefix . 'firstname', $tableprefix  . 'lastname') .

            ' = :' . $paramkey;

    $params[$paramkey] = $search;

    $paramkey++;

    if ($customfieldmappings) {

        $fieldstocheck = array_merge([$tableprefix . 'firstname', $tableprefix . 'lastname'], array_values($customfieldmappings));

    } else {

        $fieldstocheck = array_merge(['firstname', 'lastname'], \core_user\fields::get_identity_fields($context, false));

        $fieldstocheck = array_map(function($field) use ($tableprefix) {

            return $tableprefix . $field;

        }, $fieldstocheck);

    }

    foreach ($fieldstocheck as $key => $field) {

        $exactconditions[] = 'LOWER(' . $field . ') = LOWER(:' . $paramkey . ')';

        $params[$paramkey] = $search;

        $paramkey++;

    }

    $sort = 'CASE WHEN ' . implode(' OR ', $exactconditions) .

            ' THEN 0 ELSE 1 END, ' . $sort;

    return array($sort, $params);

}

/**

 * Returns a subset of users

 *

 * @global object

 * @uses DEBUG_DEVELOPER

 * @uses SQL_PARAMS_NAMED

 * @param bool $get If false then only a count of the records is returned

 * @param string $search A simple string to search for

 * @param bool $confirmed A switch to allow/disallow unconfirmed users

 * @param array $exceptions A list of IDs to ignore, eg 2,4,5,8,9,10

 * @param string $sort A SQL snippet for the sorting criteria to use

 * @param string $firstinitial Users whose first name starts with $firstinitial

 * @param string $lastinitial Users whose last name starts with $lastinitial

 * @param string $page The page or records to return

 * @param string $recordsperpage The number of records to return per page

 * @param string $fields A comma separated list of fields to be returned from the chosen table.

 * @return array|int|bool  {@link $USER} records unless get is false in which case the integer count of the records found is returned.

 *                        False is returned if an error is encountered.

 */

function get_users($get=true, $search='', $confirmed=false, array $exceptions=null, $sort='firstname ASC',

                   $firstinitial='', $lastinitial='', $page='', $recordsperpage='', $fields='*', $extraselect='', array $extraparams=null) {

    global $DB, $CFG;

    if ($get && !$recordsperpage) {

        debugging('Call to get_users with $get = true no $recordsperpage limit. ' .

                'On large installations, this will probably cause an out of memory error. ' .

                'Please think again and change your code so that it does not try to ' .

                'load so much data into memory.', DEBUG_DEVELOPER);

    }

    $fullname  = $DB->sql_fullname();

    $select = " id <> :guestid AND deleted = 0";

    $params = array('guestid'=>$CFG->siteguest);

    if (!empty($search)){

        $search = trim($search);

        $select .= " AND (".$DB->sql_like($fullname, ':search1', false)." OR ".$DB->sql_like('email', ':search2', false)." OR username = :search3)";

        $params['search1'] = "%$search%";

        $params['search2'] = "%$search%";

        $params['search3'] = "$search";

    }

    if ($confirmed) {

        $select .= " AND confirmed = 1";

    }

    if ($exceptions) {

        list($exceptions, $eparams) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'ex', false);

        $params = $params + $eparams;

        $select .= " AND id $exceptions";

    }

    if ($firstinitial) {

        $select .= " AND ".$DB->sql_like('firstname', ':fni', false, false);

        $params['fni'] = "$firstinitial%";

    }

    if ($lastinitial) {

        $select .= " AND ".$DB->sql_like('lastname', ':lni', false, false);

        $params['lni'] = "$lastinitial%";

    }

    if ($extraselect) {

        $select .= " AND $extraselect";

        $params = $params + (array)$extraparams;

    }

    if ($get) {

        return $DB->get_records_select('user', $select, $params, $sort, $fields, $page, $recordsperpage);

    } else {

        return $DB->count_records_select('user', $select, $params);

    }

}

/**

 * Return filtered (if provided) list of users in site, except guest and deleted users.

 *

 * @param string $sort An SQL field to sort by

 * @param string $dir The sort direction ASC|DESC

 * @param int $page The page or records to return

 * @param int $recordsperpage The number of records to return per page

 * @param string $search A simple string to search for

 * @param string $firstinitial Users whose first name starts with $firstinitial

 * @param string $lastinitial Users whose last name starts with $lastinitial

 * @param string $extraselect An additional SQL select statement to append to the query

 * @param array $extraparams Additional parameters to use for the above $extraselect

 * @param stdClass $extracontext If specified, will include user 'extra fields'

 *   as appropriate for current user and given context

 * @return array Array of {@link $USER} records

 */

function get_users_listing($sort='lastaccess', $dir='ASC', $page=0, $recordsperpage=0,

                           $search='', $firstinitial='', $lastinitial='', $extraselect='',

                           array $extraparams=null, $extracontext = null) {

    global $DB, $CFG;

    $fullname  = $DB->sql_fullname();

    $select = "deleted <> 1 AND u.id <> :guestid";

    $params = array('guestid' => $CFG->siteguest);

    if (!empty($search)) {

        $search = trim($search);

        $select .= " AND (". $DB->sql_like($fullname, ':search1', false, false).

                   " OR ". $DB->sql_like('email', ':search2', false, false).

                   " OR username = :search3)";

        $params['search1'] = "%$search%";

        $params['search2'] = "%$search%";

        $params['search3'] = "$search";

    }

    if ($firstinitial) {

        $select .= " AND ". $DB->sql_like('firstname', ':fni', false, false);

        $params['fni'] = "$firstinitial%";

    }

    if ($lastinitial) {

        $select .= " AND ". $DB->sql_like('lastname', ':lni', false, false);

        $params['lni'] = "$lastinitial%";

    }

    if ($extraselect) {

        // The extra WHERE clause may refer to the 'id' column which can now be ambiguous because we

        // changed the query to include joins, so replace any 'id' that is on its own (no alias)

        // with 'u.id'.

        $extraselect = preg_replace('~([ =]|^)id([ =]|$)~', '$1u.id$2', $extraselect);

        $select .= " AND $extraselect";

        $params = $params + (array)$extraparams;

    }

    // If a context is specified, get extra user fields that the current user

    // is supposed to see, otherwise just get the name fields.

    $userfields = \core_user\fields::for_name();

    if ($extracontext) {

        $userfields->with_identity($extracontext, true);

    }

    $userfields->excluding('id');

    $userfields->including('username', 'email', 'city', 'country', 'lastaccess', 'confirmed', 'mnethostid', 'suspended');

    ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams, 'mappings' => $mappings] =

            (array)$userfields->get_sql('u', true);

    if ($sort) {

        $orderbymap = $mappings;

        $orderbymap['default'] = 'lastaccess';

        $sort = get_safe_orderby($orderbymap, $sort, $dir);

    }

    // warning: will return UNCONFIRMED USERS

    return $DB->get_records_sql("SELECT u.id $selects

                                   FROM {user} u

                                        $joins

                                  WHERE $select

                                  $sort", array_merge($params, $joinparams), $page, $recordsperpage);

}

/**

 * Full list of users that have confirmed their accounts.

 *

 * @global object

 * @return array of unconfirmed users

 */

function get_users_confirmed() {

    global $DB, $CFG;

    return $DB->get_records_sql("SELECT *

                                   FROM {user}

                                  WHERE confirmed = 1 AND deleted = 0 AND id <> ?", array($CFG->siteguest));

}

/// OTHER SITE AND COURSE FUNCTIONS /////////////////////////////////////////////

/**

 * Returns $course object of the top-level site.

 *

 * @return object A {@link $COURSE} object for the site, exception if not found

 */

function get_site() {

    global $SITE, $DB;

    if (!empty($SITE->id)) {   // We already have a global to use, so return that

        return $SITE;

    }

    if ($course = $DB->get_record('course', array('category'=>0))) {

        return $course;

    } else {

        // course table exists, but the site is not there,

        // unfortunately there is no automatic way to recover

        throw new moodle_exception('nosite', 'error');

    }

}

/**

 * Gets a course object from database. If the course id corresponds to an

 * already-loaded $COURSE or $SITE object, then the loaded object will be used,

 * saving a database query.

 *

 * If it reuses an existing object, by default the object will be cloned. This

 * means you can modify the object safely without affecting other code.

 *

 * @param int $courseid Course id

 * @param bool $clone If true (default), makes a clone of the record

 * @return stdClass A course object

 * @throws dml_exception If not found in database

 */

function get_course($courseid, $clone = true) {

    global $DB, $COURSE, $SITE;

    if (!empty($COURSE->id) && $COURSE->id == $courseid) {

        return $clone ? clone($COURSE) : $COURSE;

    } else if (!empty($SITE->id) && $SITE->id == $courseid) {

        return $clone ? clone($SITE) : $SITE;

    } else {

        return $DB->get_record('course', array('id' => $courseid), '*', MUST_EXIST);

    }

}

/**

 * Returns list of courses, for whole site, or category

 *

 * Returns list of courses, for whole site, or category

 * Important: Using c.* for fields is extremely expensive because

 *            we are using distinct. You almost _NEVER_ need all the fields

 *            in such a large SELECT

 *

 * Consider using core_course_category::get_courses()

 * or core_course_category::search_courses() instead since they use caching.

 *

 * @global object

 * @global object

 * @global object

 * @uses CONTEXT_COURSE

 * @param string|int $categoryid Either a category id or 'all' for everything

 * @param string $sort A field and direction to sort by

 * @param string $fields The additional fields to return (note that "id, category, visible" are always present)

 * @return array Array of courses

 */

function get_courses($categoryid="all", $sort="c.sortorder ASC", $fields="c.*") {

    global $USER, $CFG, $DB;

    $params = array();

    if ($categoryid !== "all" && is_numeric($categoryid)) {

        $categoryselect = "WHERE c.category = :catid";

        $params['catid'] = $categoryid;

    } else {

        $categoryselect = "";

    }

    if (empty($sort)) {

        $sortstatement = "";

    } else {

        $sortstatement = "ORDER BY $sort";

    }

    $visiblecourses = array();

    $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');

    $ccjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel)";

    $params['contextlevel'] = CONTEXT_COURSE;

    // The fields "id, category, visible" are required in the subsequent loop and must always be present.

    if ($fields !== 'c.*') {

        $fieldarray = array_merge(

            // Split fields on comma + zero or more whitespace, merge with required fields.

            preg_split('/,\s*/', $fields), [

                'c.id',

                'c.category',

                'c.visible',

            ]

        );

        $fields = implode(',', array_unique($fieldarray));

    }

    $sql = "SELECT $fields $ccselect

              FROM {course} c

           $ccjoin

              $categoryselect

              $sortstatement";

    // pull out all course matching the cat

    if ($courses = $DB->get_records_sql($sql, $params)) {

        // loop throught them

        foreach ($courses as $course) {

            context_helper::preload_from_record($course);

            if (core_course_category::can_view_course_info($course)) {

                $visiblecourses [$course->id] = $course;

            }

        }

    }

    return $visiblecourses;

}

/**

 * A list of courses that match a search

 *

 * @global object

 * @global object

 * @param array $searchterms An array of search criteria

 * @param string $sort A field and direction to sort by

 * @param int $page The page number to get

 * @param int $recordsperpage The number of records per page

 * @param int $totalcount Passed in by reference.

 * @param array $requiredcapabilities Extra list of capabilities used to filter courses

 * @param array $searchcond additional search conditions, for example ['c.enablecompletion = :p1']

 * @param array $params named parameters for additional search conditions, for example ['p1' => 1]

 * @return stdClass[] {@link $COURSE} records

 */

function get_courses_search($searchterms, $sort, $page, $recordsperpage, &$totalcount,

                            $requiredcapabilities = array(), $searchcond = [], $params = []) {

    global $CFG, $DB;

    if ($DB->sql_regex_supported()) {

        $REGEXP    = $DB->sql_regex(true);

        $NOTREGEXP = $DB->sql_regex(false);

    }

    $i = 0;

    // Thanks Oracle for your non-ansi concat and type limits in coalesce. MDL-29912

    if ($DB->get_dbfamily() == 'oracle') {

        $concat = "(c.summary|| ' ' || c.fullname || ' ' || c.idnumber || ' ' || c.shortname)";

    } else {

        $concat = $DB->sql_concat("COALESCE(c.summary, '')", "' '", 'c.fullname', "' '", 'c.idnumber', "' '", 'c.shortname');

    }

    foreach ($searchterms as $searchterm) {

        $i++;

        $NOT = false; /// Initially we aren't going to perform NOT LIKE searches, only MSSQL and Oracle

                   /// will use it to simulate the "-" operator with LIKE clause

    /// Under Oracle and MSSQL, trim the + and - operators and perform

    /// simpler LIKE (or NOT LIKE) queries

        if (!$DB->sql_regex_supported()) {

            if (substr($searchterm, 0, 1) == '-') {

                $NOT = true;

            }

            $searchterm = trim($searchterm, '+-');

        }

        // TODO: +- may not work for non latin languages

        if (substr($searchterm,0,1) == '+') {

            $searchterm = trim($searchterm, '+-');

            $searchterm = preg_quote($searchterm, '|');

            $searchcond[] = "$concat $REGEXP :ss$i";

            $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";

        } else if ((substr($searchterm,0,1) == "-") && (core_text::strlen($searchterm) > 1)) {

            $searchterm = trim($searchterm, '+-');

            $searchterm = preg_quote($searchterm, '|');

            $searchcond[] = "$concat $NOTREGEXP :ss$i";

            $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";

        } else {

            $searchcond[] = $DB->sql_like($concat,":ss$i", false, true, $NOT);

            $params['ss'.$i] = "%$searchterm%";

        }

    }

    if (empty($searchcond)) {

        $searchcond = array('1 = 1');

    }

    $searchcond = implode(" AND ", $searchcond);

    $courses = array();

    $c = 0; // counts how many visible courses we've seen

    // Tiki pagination

    $limitfrom = $page * $recordsperpage;

    $limitto   = $limitfrom + $recordsperpage;

    $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');

    $ccjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = :contextlevel)";

    $params['contextlevel'] = CONTEXT_COURSE;

    $sql = "SELECT c.* $ccselect

              FROM {course} c

           $ccjoin

             WHERE $searchcond AND c.id <> ".SITEID."

          ORDER BY $sort";

    $mycourses = enrol_get_my_courses();

    $rs = $DB->get_recordset_sql($sql, $params);

    foreach($rs as $course) {

        // Preload contexts only for hidden courses or courses we need to return.

        context_helper::preload_from_record($course);

        $coursecontext = context_course::instance($course->id);

        if (!array_key_exists($course->id, $mycourses) && !core_course_category::can_view_course_info($course)) {

            continue;

        }

        if (!empty($requiredcapabilities)) {

            if (!has_all_capabilities($requiredcapabilities, $coursecontext)) {

                continue;

            }

        }

        // Don't exit this loop till the end

        // we need to count all the visible courses

        // to update $totalcount

        if ($c >= $limitfrom && $c < $limitto) {

            $courses[$course->id] = $course;

        }

        $c++;

    }

    $rs->close();

    // our caller expects 2 bits of data - our return

    // array, and an updated $totalcount

    $totalcount = $c;

    return $courses;

}

/**

 * Fixes course category and course sortorder, also verifies category and course parents and paths.

 * (circular references are not fixed)

 *

 * @global object

 * @global object

 * @uses MAX_COURSE_CATEGORIES

 * @uses SITEID

 * @uses CONTEXT_COURSE

 * @return void

 */

function fix_course_sortorder() {

    global $DB, $SITE;

    //WARNING: this is PHP5 only code!

    // if there are any changes made to courses or categories we will trigger

    // the cache events to purge all cached courses/categories data

    $cacheevents = array();

    if ($unsorted = $DB->get_records('course_categories', array('sortorder'=>0))) {

        //move all categories that are not sorted yet to the end

        $DB->set_field('course_categories', 'sortorder',

            get_max_courses_in_category() * MAX_COURSE_CATEGORIES, array('sortorder' => 0));

        $cacheevents['changesincoursecat'] = true;

    }

    $allcats = $DB->get_records('course_categories', null, 'sortorder, id', 'id, sortorder, parent, depth, path');

    $topcats    = array();

    $brokencats = array();

    foreach ($allcats as $cat) {

        $sortorder = (int)$cat->sortorder;

        if (!$cat->parent) {

            while(isset($topcats[$sortorder])) {

                $sortorder++;

            }

            $topcats[$sortorder] = $cat;

            continue;

        }

        if (!isset($allcats[$cat->parent])) {

            $brokencats[] = $cat;

            continue;

        }

        if (!isset($allcats[$cat->parent]->children)) {

            $allcats[$cat->parent]->children = array();

        }

        while(isset($allcats[$cat->parent]->children[$sortorder])) {

            $sortorder++;

        }

        $allcats[$cat->parent]->children[$sortorder] = $cat;

    }

    unset($allcats);

    // add broken cats to category tree

    if ($brokencats) {

        $defaultcat = reset($topcats);

        foreach ($brokencats as $cat) {

            $topcats[] = $cat;

        }

    }

    // now walk recursively the tree and fix any problems found

    $sortorder = 0;

    $fixcontexts = array();

    if (_fix_course_cats($topcats, $sortorder, 0, 0, '', $fixcontexts)) {

        $cacheevents['changesincoursecat'] = true;

    }

    // detect if there are "multiple" frontpage courses and fix them if needed

    $frontcourses = $DB->get_records('course', array('category'=>0), 'id');

    if (count($frontcourses) > 1) {

        if (isset($frontcourses[SITEID])) {

            $frontcourse = $frontcourses[SITEID];

            unset($frontcourses[SITEID]);

        } else {

            $frontcourse = array_shift($frontcourses);

        }

        $defaultcat = reset($topcats);

        foreach ($frontcourses as $course) {

            $DB->set_field('course', 'category', $defaultcat->id, array('id'=>$course->id));

            $context = context_course::instance($course->id);

            $fixcontexts[$context->id] = $context;

            $cacheevents['changesincourse'] = true;

        }

        unset($frontcourses);

    } else {

        $frontcourse = reset($frontcourses);

    }

    // now fix the paths and depths in context table if needed

    if ($fixcontexts) {

        foreach ($fixcontexts as $fixcontext) {

            $fixcontext->reset_paths(false);

        }

        context_helper::build_all_paths(false);

        unset($fixcontexts);

        $cacheevents['changesincourse'] = true;

        $cacheevents['changesincoursecat'] = true;

    }

    // release memory

    unset($topcats);

    unset($brokencats);

    unset($fixcontexts);

    // fix frontpage course sortorder

    if ($frontcourse->sortorder != 1) {

        $DB->set_field('course', 'sortorder', 1, array('id'=>$frontcourse->id));

        $cacheevents['changesincourse'] = true;

    }

    // now fix the course counts in category records if needed

    $sql = "SELECT cc.id, cc.coursecount, COUNT(c.id) AS newcount

              FROM {course_categories} cc

              LEFT JOIN {course} c ON c.category = cc.id

          GROUP BY cc.id, cc.coursecount

            HAVING cc.coursecount <> COUNT(c.id)";

    if ($updatecounts = $DB->get_records_sql($sql)) {

        // categories with more courses than MAX_COURSES_IN_CATEGORY

        $categories = array();

        foreach ($updatecounts as $cat) {

            $cat->coursecount = $cat->newcount;

            if ($cat->coursecount >= get_max_courses_in_category()) {

                $categories[] = $cat->id;

            }

            unset($cat->newcount);

            $DB->update_record_raw('course_categories', $cat, true);

        }

        if (!empty($categories)) {

            $str = implode(', ', $categories);

            debugging("The number of courses (category id: $str) has reached max number of courses " .

                "in a category (" . get_max_courses_in_category() . "). It will cause a sorting performance issue. " .

                "Please set higher value for \$CFG->maxcoursesincategory in config.php. " .

                "Please also make sure \$CFG->maxcoursesincategory * MAX_COURSE_CATEGORIES less than max integer. " .

                "See tracker issues: MDL-25669 and MDL-69573", DEBUG_DEVELOPER);

        }

        $cacheevents['changesincoursecat'] = true;

    }

    // now make sure that sortorders in course table are withing the category sortorder ranges

    $sql = "SELECT DISTINCT cc.id, cc.sortorder

              FROM {course_categories} cc

              JOIN {course} c ON c.category = cc.id

             WHERE c.sortorder < cc.sortorder OR c.sortorder > cc.sortorder + " . get_max_courses_in_category();

    if ($fixcategories = $DB->get_records_sql($sql)) {

        //fix the course sortorder ranges

        foreach ($fixcategories as $cat) {

            $sql = "UPDATE {course}

                       SET sortorder = ".$DB->sql_modulo('sortorder', get_max_courses_in_category())." + ?

                     WHERE category = ?";

            $DB->execute($sql, array($cat->sortorder, $cat->id));

        }

        $cacheevents['changesincoursecat'] = true;

    }

    unset($fixcategories);

    // categories having courses with sortorder duplicates or having gaps in sortorder

    $sql = "SELECT DISTINCT c1.category AS id , cc.sortorder

              FROM {course} c1

              JOIN {course} c2 ON c1.sortorder = c2.sortorder

              JOIN {course_categories} cc ON (c1.category = cc.id)

             WHERE c1.id <> c2.id";

    $fixcategories = $DB->get_records_sql($sql);

    $sql = "SELECT cc.id, cc.sortorder, cc.coursecount, MAX(c.sortorder) AS maxsort, MIN(c.sortorder) AS minsort

              FROM {course_categories} cc

              JOIN {course} c ON c.category = cc.id

          GROUP BY cc.id, cc.sortorder, cc.coursecount

            HAVING (MAX(c.sortorder) <>  cc.sortorder + cc.coursecount) OR (MIN(c.sortorder) <>  cc.sortorder + 1)";

    $gapcategories = $DB->get_records_sql($sql);