File: /lib/moodlelib.php

/**
 * If the current user is to be shown extra user fields when listing or
 * selecting users, returns a string suitable for including in an SQL select
 * clause to retrieve those fields.
 *
 * @param context $context Context
 * @param string $alias Alias of user table, e.g. 'u' (default none)
 * @param string $prefix Prefix for field names using AS, e.g. 'u_' (default none)
 * @param array $already Array of fields that we're going to include anyway so don't list them (default none)
 * @return string Partial SQL select clause, beginning with comma, for example ',u.idnumber,u.department' unless it is blank
 */
function get_extra_user_fields_sql($context, $alias='', $prefix='', $already = array()) {
    $fields = get_extra_user_fields($context, $already);
    $result = '';
    // Add punctuation for alias.
    if ($alias !== '') {
        $alias .= '.';
    }
    foreach ($fields as $field) {
        if (strstr($field, "profile_field_")) {
           $result .= ', (SELECT data FROM {user_info_field} xuif LEFT JOIN {user_info_data} xuid ON xuif.id=xuid.fieldid WHERE xuif.shortname=\''.substr($field, 14).'\' AND xuid.userid='.$alias.'id)';
            if ($prefix) {
                $result .= ' AS ' . $prefix . $field;
            } else {
                $result .= ' AS '.$field;
            }
        } else {
            $result .= ', ' . $alias . $field;
            if ($prefix) {
                $result .= ' AS ' . $prefix . $field;
            }
        }
    }
    return $result;
}


=======================================================================================

File: /enrol/locallib.php

/**
     * Gets all of the users enrolled in this course.
     *
     * If a filter was specified this will be the users who were enrolled
     * in this course by means of that instance. If role or search filters were
     * specified then these will also be applied.
     *
     * @global moodle_database $DB
     * @param string $sort
     * @param string $direction ASC or DESC
     * @param int $page First page should be 0
     * @param int $perpage Defaults to 25
     * @return array
     */
    public function get_users($sort, $direction='ASC', $page=0, $perpage=25) {
        global $DB;
        if ($direction !== 'ASC') {
            $direction = 'DESC';
        }
        $key = md5("$sort-$direction-$page-$perpage");
        if (!array_key_exists($key, $this->users)) {
            list($instancessql, $params, $filter) = $this->get_instance_sql();
            list($filtersql, $moreparams) = $this->get_filter_sql();
            $params += $moreparams;
            $extrafields = get_extra_user_fields($this->get_context());
            $extrafields[] = 'lastaccess';
            //$ufields = user_picture::fields('u', $extrafields);
            $ufields = user_picture::fields('u');
            $sql = "SELECT DISTINCT $ufields ".get_extra_user_fields_sql($this->get_context(), 'u', '', $ufields).", COALESCE(ul.timeaccess, 0) AS lastcourseaccess
                      FROM {user} u
                      JOIN {user_enrolments} ue ON (ue.userid = u.id  AND ue.enrolid $instancessql)
                      JOIN {enrol} e ON (e.id = ue.enrolid)
                 LEFT JOIN {user_lastaccess} ul ON (ul.courseid = e.courseid AND ul.userid = u.id)
                 LEFT JOIN {groups_members} gm ON u.id = gm.userid AND gm.groupid IN (
                               SELECT g.id
                                 FROM {groups} g
                                WHERE g.courseid = e.courseid
                           )
                     WHERE $filtersql
                  ORDER BY $sort $direction";
            $this->users[$key] = $DB->get_records_sql($sql, $params, $page*$perpage, $perpage);
        }
        return $this->users[$key];
    }


    /**
     * Helper method used by {@link get_potential_users()} and {@link search_other_users()}.
     *
     * @param string $search the search term, if any.
     * @param bool $searchanywhere Can the search term be anywhere, or must it be at the start.
     * @return array with three elements:
     *     string list of fields to SELECT,
     *     string contents of SQL WHERE clause,
     *     array query params. Note that the SQL snippets use named parameters.
     */
    protected function get_basic_search_conditions($search, $searchanywhere) {
        global $DB, $CFG;

        // Add some additional sensible conditions
        $tests = array("u.id <> :guestid", 'u.deleted = 0', 'u.confirmed = 1');
        $params = array('guestid' => $CFG->siteguest);
        if (!empty($search)) {
            $conditions = get_extra_user_fields($this->get_context());
            $conditions[] = 'u.firstname';
            $conditions[] = 'u.lastname';
            $conditions[] = $DB->sql_fullname('u.firstname', 'u.lastname');
            if ($searchanywhere) {
                $searchparam = '%' . $search . '%';
            } else {
                $searchparam = $search . '%';
            }
            $i = 0;
            foreach ($conditions as $key => $condition) {
                $conditions[$key] = $DB->sql_like($condition, ":con{$i}00", false);
                $params["con{$i}00"] = $searchparam;
                $i++;
            }
            $tests[] = '(' . implode(' OR ', $conditions) . ')';
        }
        $wherecondition = implode(' AND ', $tests);

        $extrafields = get_extra_user_fields($this->get_context(), array('username', 'lastaccess'));
        $extrafields[] = 'username';
        $extrafields[] = 'lastaccess';
        //$ufields = user_picture::fields('u', $extrafields);
        $ufields = user_picture::fields('u');
        
        return array($ufields, $params, $wherecondition);
    }

    /**
     * Gets an array of the users that can be enrolled in this course.
     *
     * @global moodle_database $DB
     * @param int $enrolid
     * @param string $search
     * @param bool $searchanywhere
     * @param int $page Defaults to 0
     * @param int $perpage Defaults to 25
     * @param int $addedenrollment Defaults to 0
     * @return array Array(totalusers => int, users => array)
     */
    public function get_potential_users($enrolid, $search='', $searchanywhere=false, $page=0, $perpage=25, $addedenrollment=0) {
        global $DB;

        list($ufields, $params, $wherecondition) = $this->get_basic_search_conditions($search, $searchanywhere);

        $fields      = 'SELECT '.$ufields." ".get_extra_user_fields_sql($this->get_context(), 'u', '', $ufields);
        $countfields = 'SELECT COUNT(1)';
        $sql = " FROM {user} u
            LEFT JOIN {user_enrolments} ue ON (ue.userid = u.id AND ue.enrolid = :enrolid)
                WHERE $wherecondition
                      AND ue.id IS NULL";
        $params['enrolid'] = $enrolid;

        return $this->execute_search_queries($search, $fields, $countfields, $sql, $params, $page, $perpage, $addedenrollment);
    }

===========================================================================

File: /lib/datalib.php

/**
 * 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 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) {
        $select .= " AND $extraselect";
        $params = $params + (array)$extraparams;
    }

    if ($sort) {
        $sort = " ORDER BY $sort $dir";
    }

    // If a context is specified, get extra user fields that the current user
    // is supposed to see.
    $extrafields = '';
    if ($extracontext) {
        $extrafields = get_extra_user_fields_sql($extracontext, 'u', '',
                array('id', 'username', 'email', 'firstname', 'lastname', 'city', 'country',
                'lastaccess', 'confirmed', 'mnethostid'));
    }
    $namefields = get_all_user_name_fields(true);
    $extrafields = "$extrafields, $namefields";

    // warning: will return UNCONFIRMED USERS
    return $DB->get_records_sql("SELECT id, username, email, city, country, lastaccess, confirmed, mnethostid, suspended $extrafields
                                   FROM {user} u
                                  WHERE $select
                                  $sort", $params, $page, $recordsperpage);

}