/**
  * Return array of users whose progress is tracked in this course.
  *
  * Optionally supply a search's where clause, group id, sorting, paging.
  *
  * @param string $where Where clause sql, referring to 'u.' fields (optional)
  * @param array $whereparams Where clause params (optional)
  * @param int $groupid Group ID to restrict to (optional)
  * @param string $sort Order by clause (optional)
  * @param int $limitfrom Result start (optional)
  * @param int $limitnum Result max size (optional)
  * @param context $extracontext If set, includes extra user information fields
  *   as appropriate to display for current user in this context
  * @return array Array of user objects with standard user fields
  */
 public function get_tracked_users($where = '', $whereparams = array(), $groupid = 0, $sort = '', $limitfrom = '', $limitnum = '', context $extracontext = null)
 {
     global $DB;
     list($enrolledsql, $params) = get_enrolled_sql(context_course::instance($this->course->id), 'moodle/course:isincompletionreports', $groupid, true);
     $sql = 'SELECT u.id, u.firstname, u.lastname, u.idnumber';
     if ($extracontext) {
         $sql .= get_extra_user_fields_sql($extracontext, 'u', '', array('idnumber'));
     }
     $sql .= ' FROM (' . $enrolledsql . ') eu JOIN {user} u ON u.id = eu.id';
     if ($where) {
         $sql .= " AND {$where}";
         $params = array_merge($params, $whereparams);
     }
     if ($sort) {
         $sql .= " ORDER BY {$sort}";
     }
     return $DB->get_records_sql($sql, $params, $limitfrom, $limitnum);
 }

/**
 * 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, '', '', array('id', 'username', 'email', 'firstname', 'lastname', 'city', 'country', 'lastaccess', 'confirmed', 'mnethostid'));
    }
    // warning: will return UNCONFIRMED USERS
    return $DB->get_records_sql("SELECT id, username, email, firstname, lastname, city, country,\n                                        lastaccess, confirmed, mnethostid, suspended {$extrafields}\n                                   FROM {user}\n                                  WHERE {$select}\n                                  {$sort}", $params, $page, $recordsperpage);
}

    /**
     * @method get_users_listing
     * @todo to get user list of school based on condition  
     * @param string $sort fieldname
     * @param string $dir specify the order to sort
     * @param int $page page number
     * @param int $recordsperpage records perpage
     * @param string $extraselect extra condition to select user
     * @param array $extraparams
     * @return array of objects , list of users
     */
    function get_users_listing($sort = 'lastaccess', $dir = 'ASC', $page = 0, $recordsperpage = 0, $extraselect = '', array $extraparams = null, $extracontext = null) {
        global $DB, $CFG;
        $extraselect;

        $select = "deleted <> 1 AND id <> :guestid";  //$select = "deleted=0";
        $params = array('guestid' => $CFG->siteguest);

        if ($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.
        $extrafields = '';
        if ($extracontext) {
            $extrafields = get_extra_user_fields_sql($extracontext, '', '', array('id', 'username', 'email', 'firstname', 'lastname', 'city', 'country',
                'lastaccess', 'confirmed', 'mnethostid'));
        }
        /*
         * ###Bugreport#183-Filters
         * @author Naveen Kumar<[email protected]>
         * (Resolved) Added $select parameters for conditions 
         */
        // warning: will return UNCONFIRMED USERS
        //  print_object($params);
        $users = $DB->get_records_sql("SELECT *
                                       FROM {user}
                                       WHERE $select", $params);

        $hierarchy = new hierarchy();
        $schoollist = $hierarchy->get_assignedschools();
        $schoollist = $hierarchy->get_school_parent($schoollist, $selected = array(), $inctop = false, $all = false);
        if (is_siteadmin()) {
            $schoollist = $hierarchy->get_school_items();
        }
        $schoolidin = implode(',', array_keys($schoollist));
        if ($users && $schoollist) {
            $useridin = implode(',', array_keys($users));
            return $DB->get_records_sql("select user.* from (SELECT u.id, u.username, u.email, u.firstname, u.lastname, u.city, u.country,
                                            u.lastaccess, u.confirmed, u.mnethostid, u.suspended FROM {user} u
                                        JOIN {local_users} lu ON lu.userid = u.id
                                        JOIN {local_school_permissions} sp ON sp.userid = u.id
                                        WHERE u.id in ($useridin) AND sp.schoolid IN ($schoolidin)
                                        UNION
                                        SELECT u.id, u.username, u.email, u.firstname, u.lastname, u.city, u.country,
                                            u.lastaccess, u.confirmed, u.mnethostid, u.suspended FROM {user} u
                                        JOIN {local_users} lu ON lu.userid = u.id
                                        JOIN {local_userdata} ud ON ud.userid = u.id
                                        WHERE u.id in ($useridin) AND ud.schoolid IN ($schoolidin)) user GROUP BY user.id ORDER BY user.$sort $dir LIMIT $page, $recordsperpage ");
        }
    }

                TABLE_VAR_SHOW    => 'sshow',
                TABLE_VAR_IFIRST  => 'sifirst',
                TABLE_VAR_ILAST   => 'silast',
                TABLE_VAR_PAGE    => 'spage'
                ));
    $table->setup();

    // we are looking for all users with this role assigned in this context or higher
    $contextlist = get_related_contexts_string($context);

    list($esql, $params) = get_enrolled_sql($context, NULL, $currentgroup, true);
    $joins = array("FROM {user} u");
    $wheres = array();

    $extrasql = get_extra_user_fields_sql($context, 'u', '', array(
            'id', 'username', 'firstname', 'lastname', 'email', 'city', 'country',
            'picture', 'lang', 'timezone', 'maildisplay', 'imagealt', 'lastaccess'));

    if ($isfrontpage) {
        $select = "SELECT u.id, u.username, u.firstname, u.lastname,
                          u.email, u.city, u.country, u.picture,
                          u.lang, u.timezone, u.maildisplay, u.imagealt,
                          u.lastaccess$extrasql";
        $joins[] = "JOIN ($esql) e ON e.id = u.id"; // everybody on the frontpage usually
        if ($accesssince) {
            $wheres[] = get_user_lastaccess_sql($accesssince);
        }

    } else {
        $select = "SELECT u.id, u.username, u.firstname, u.lastname,
                          u.email, u.city, u.country, u.picture,

//.........这里部分代码省略.........
                $columns[] = 'picture';
                $headers[] = '';
            }
            $columns[] = 'fullname';
            $headers[] = get_string('name');

            $extrafields = get_extra_user_fields($coursecontext);
            foreach ($extrafields as $field) {
                $columns[] = $field;
                $headers[] = get_user_field_name($field);
            }
            $columns[] = 'attempt';
            $headers[] = get_string('attempt', 'scorm');
            $columns[] = 'start';
            $headers[] = get_string('started', 'scorm');
            $columns[] = 'finish';
            $headers[] = get_string('last', 'scorm');
            $columns[] = 'score';
            $headers[] = get_string('score', 'scorm');
            $scoes = $DB->get_records('scorm_scoes', array("scorm"=>$scorm->id), 'id');
            foreach ($scoes as $sco) {
                if ($sco->launch != '') {
                    $columns[] = 'scograde'.$sco->id;
                    $headers[] = format_string($sco->title,'',$formattextoptions);
                }
            }

            $params = array();
            list($usql, $params) = $DB->get_in_or_equal($allowedlist, SQL_PARAMS_NAMED);
                                    // Construct the SQL
            $select = 'SELECT DISTINCT '.$DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)').' AS uniqueid, ';
            $select .= 'st.scormid AS scormid, st.attempt AS attempt, ' .
                    'u.id AS userid, u.idnumber, u.firstname, u.lastname, u.picture, u.imagealt, u.email'.
                    get_extra_user_fields_sql($coursecontext, 'u', '', array('idnumber')) . ' ';

            // This part is the same for all cases - join users and scorm_scoes_track tables
            $from = 'FROM {user} u ';
            $from .= 'LEFT JOIN {scorm_scoes_track} st ON st.userid = u.id AND st.scormid = '.$scorm->id;
            switch ($attemptsmode) {
                case SCORM_REPORT_ATTEMPTS_STUDENTS_WITH:
                    // Show only students with attempts
                    $where = ' WHERE u.id ' .$usql. ' AND st.userid IS NOT NULL';
                    break;
                case SCORM_REPORT_ATTEMPTS_STUDENTS_WITH_NO:
                    // Show only students without attempts
                    $where = ' WHERE u.id ' .$usql. ' AND st.userid IS NULL';
                    break;
                case SCORM_REPORT_ATTEMPTS_ALL_STUDENTS:
                    // Show all students with or without attempts
                    $where = ' WHERE u.id ' .$usql. ' AND (st.userid IS NOT NULL OR st.userid IS NULL)';
                    break;
            }

            $countsql = 'SELECT COUNT(DISTINCT('.$DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)').')) AS nbresults, ';
            $countsql .= 'COUNT(DISTINCT('.$DB->sql_concat('u.id', '\'#\'', 'st.attempt').')) AS nbattempts, ';
            $countsql .= 'COUNT(DISTINCT(u.id)) AS nbusers ';
            $countsql .= $from.$where;
            $attempts = $DB->get_records_sql($select.$from.$where, $params);
            $questioncount = get_scorm_question_count($scorm->id);
            for($id = 0; $id < $questioncount; $id++) {
                if ($displayoptions['qtext']) {
                    $columns[] = 'question' . $id;
                    $headers[] = get_string('questionx', 'scormreport_interactions', $id);
                }
                if ($displayoptions['resp']) {
                    $columns[] = 'response' . $id;

            TABLE_VAR_SORT    => 'ssort',
            TABLE_VAR_HIDE    => 'shide',
            TABLE_VAR_SHOW    => 'sshow',
            TABLE_VAR_IFIRST  => 'sifirst',
            TABLE_VAR_ILAST   => 'silast',
            TABLE_VAR_PAGE    => 'spage'
            ));
$table->setup();

list($esql, $params) = get_enrolled_sql($context, null, $currentgroup, true);
$joins = array("FROM {user} u");
$wheres = array();

$mainuserfields = user_picture::fields('u', array('username', 'email', 'city', 'country', 'lang', 'timezone', 'maildisplay'));
$alreadyretrievedfields = explode(',', $mainuserfields);
$extrasql = get_extra_user_fields_sql($context, 'u', '', $alreadyretrievedfields);

if ($isfrontpage) {
    $select = "SELECT $mainuserfields, u.lastaccess$extrasql";
    $joins[] = "JOIN ($esql) e ON e.id = u.id"; // Everybody on the frontpage usually.
    if ($accesssince) {
        $wheres[] = get_user_lastaccess_sql($accesssince);
    }

} else {
    $select = "SELECT $mainuserfields, COALESCE(ul.timeaccess, 0) AS lastaccess$extrasql";
    $joins[] = "JOIN ($esql) e ON e.id = u.id"; // Course enrolled users only.
    $joins[] = "LEFT JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = :courseid)"; // Not everybody accessed course yet.
    $params['courseid'] = $course->id;
    if ($accesssince) {
        $wheres[] = get_course_lastaccess_sql($accesssince);

//.........这里部分代码省略.........
             $columns[] = 'checkbox';
             $headers[] = null;
         }
         if (!$download && $CFG->grade_report_showuserimage) {
             $columns[] = 'picture';
             $headers[] = '';
         }
         $columns[] = 'fullname';
         $headers[] = get_string('name');
         $extrafields = get_extra_user_fields($coursecontext);
         foreach ($extrafields as $field) {
             $columns[] = $field;
             $headers[] = get_user_field_name($field);
         }
         $columns[] = 'attempt';
         $headers[] = get_string('attempt', 'scorm');
         $columns[] = 'start';
         $headers[] = get_string('started', 'scorm');
         $columns[] = 'finish';
         $headers[] = get_string('last', 'scorm');
         $columns[] = 'score';
         $headers[] = get_string('score', 'scorm');
         $scoes = $DB->get_records('scorm_scoes', array("scorm" => $scorm->id), 'sortorder, id');
         foreach ($scoes as $sco) {
             if ($sco->launch != '') {
                 $columns[] = 'scograde' . $sco->id;
                 $headers[] = format_string($sco->title, '', $formattextoptions);
             }
         }
         $params = array();
         list($usql, $params) = $DB->get_in_or_equal($allowedlist, SQL_PARAMS_NAMED);
         // Construct the SQL.
         $select = 'SELECT DISTINCT ' . $DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)') . ' AS uniqueid, ';
         $select .= 'st.scormid AS scormid, st.attempt AS attempt, ' . \user_picture::fields('u', array('idnumber'), 'userid') . get_extra_user_fields_sql($coursecontext, 'u', '', array('email', 'idnumber')) . ' ';
         // This part is the same for all cases - join users and scorm_scoes_track tables.
         $from = 'FROM {user} u ';
         $from .= 'LEFT JOIN {scorm_scoes_track} st ON st.userid = u.id AND st.scormid = ' . $scorm->id;
         switch ($attemptsmode) {
             case SCORM_REPORT_ATTEMPTS_STUDENTS_WITH:
                 // Show only students with attempts.
                 $where = ' WHERE u.id ' . $usql . ' AND st.userid IS NOT NULL';
                 break;
             case SCORM_REPORT_ATTEMPTS_STUDENTS_WITH_NO:
                 // Show only students without attempts.
                 $where = ' WHERE u.id ' . $usql . ' AND st.userid IS NULL';
                 break;
             case SCORM_REPORT_ATTEMPTS_ALL_STUDENTS:
                 // Show all students with or without attempts.
                 $where = ' WHERE u.id ' . $usql . ' AND (st.userid IS NOT NULL OR st.userid IS NULL)';
                 break;
         }
         $countsql = 'SELECT COUNT(DISTINCT(' . $DB->sql_concat('u.id', '\'#\'', 'COALESCE(st.attempt, 0)') . ')) AS nbresults, ';
         $countsql .= 'COUNT(DISTINCT(' . $DB->sql_concat('u.id', '\'#\'', 'st.attempt') . ')) AS nbattempts, ';
         $countsql .= 'COUNT(DISTINCT(u.id)) AS nbusers ';
         $countsql .= $from . $where;
         $nbmaincolumns = count($columns);
         // Get number of main columns used.
         $objectives = get_scorm_objectives($scorm->id);
         $nosort = array();
         foreach ($objectives as $scoid => $sco) {
             foreach ($sco as $id => $objectivename) {
                 $colid = $scoid . 'objectivestatus' . $id;
                 $columns[] = $colid;
                 $nosort[] = $colid;
                 if (!$displayoptions['objectivescore']) {
                     // Display the objective name only.

 /**
  * pulls out the userids of the users to be display, and sorts them
  */
 public function load_users()
 {
     global $CFG, $DB;
     //limit to users with a gradeable role
     list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $this->gradebookroles), SQL_PARAMS_NAMED, 'grbr0');
     //limit to users with an active enrollment
     list($enrolledsql, $enrolledparams) = get_enrolled_sql($this->context);
     //fields we need from the user table
     $userfields = user_picture::fields('u');
     $userfields .= get_extra_user_fields_sql($this->context);
     $sortjoin = $sort = $params = null;
     //if the user has clicked one of the sort asc/desc arrows
     if (is_numeric($this->sortitemid)) {
         $params = array_merge(array('gitemid' => $this->sortitemid), $gradebookrolesparams, $this->groupwheresql_params, $enrolledparams);
         $sortjoin = "LEFT JOIN {grade_grades} g ON g.userid = u.id AND g.itemid = {$this->sortitemid}";
         $sort = "g.finalgrade {$this->sortorder}";
     } else {
         $sortjoin = '';
         switch ($this->sortitemid) {
             case 'lastname':
                 $sort = "u.lastname {$this->sortorder}, u.firstname {$this->sortorder}";
                 break;
             case 'firstname':
                 $sort = "u.firstname {$this->sortorder}, u.lastname {$this->sortorder}";
                 break;
             case 'idnumber':
             default:
                 $sort = "u.idnumber {$this->sortorder}";
                 break;
         }
         $params = array_merge($gradebookrolesparams, $this->groupwheresql_params, $enrolledparams);
     }
     $sql = "SELECT {$userfields}\n                  FROM {user} u\n                  JOIN ({$enrolledsql}) je ON je.id = u.id\n                       {$this->groupsql}\n                       {$sortjoin}\n                  JOIN (\n                           SELECT DISTINCT ra.userid\n                             FROM {role_assignments} ra\n                            WHERE ra.roleid IN ({$this->gradebookroles})\n                              AND ra.contextid " . get_related_contexts_string($this->context) . "\n                       ) rainner ON rainner.userid = u.id\n                   AND u.deleted = 0\n                   {$this->groupwheresql}\n              ORDER BY {$sort}";
     $this->users = $DB->get_records_sql($sql, $params, $this->get_pref('studentsperpage') * $this->page, $this->get_pref('studentsperpage'));
     if (empty($this->users)) {
         $this->userselect = '';
         $this->users = array();
         $this->userselect_params = array();
     } else {
         list($usql, $uparams) = $DB->get_in_or_equal(array_keys($this->users), SQL_PARAMS_NAMED, 'usid0');
         $this->userselect = "AND g.userid {$usql}";
         $this->userselect_params = $uparams;
         //add a flag to each user indicating whether their enrolment is active
         $sql = "SELECT ue.userid\n                      FROM {user_enrolments} ue\n                      JOIN {enrol} e ON e.id = ue.enrolid\n                     WHERE ue.userid {$usql}\n                           AND ue.status = :uestatus\n                           AND e.status = :estatus\n                           AND e.courseid = :courseid\n                  GROUP BY ue.userid";
         $coursecontext = get_course_context($this->context);
         $params = array_merge($uparams, array('estatus' => ENROL_INSTANCE_ENABLED, 'uestatus' => ENROL_USER_ACTIVE, 'courseid' => $coursecontext->instanceid));
         $useractiveenrolments = $DB->get_records_sql($sql, $params);
         foreach ($this->users as $user) {
             $this->users[$user->id]->suspendedenrolment = !array_key_exists($user->id, $useractiveenrolments);
         }
     }
     return $this->users;
 }

    public function test_get_extra_user_fields_sql() {
        global $CFG, $USER, $DB;

        $olduser = $USER;
        $USER = $DB->get_record('user', array('id'=>2)); //admin

        $oldshowuseridentity = $CFG->showuseridentity;
        $context = context_system::instance();

        // No fields
        $CFG->showuseridentity = '';
        $this->assertEquals('', get_extra_user_fields_sql($context));

        // One field
        $CFG->showuseridentity = 'frog';
        $this->assertEquals(', frog', get_extra_user_fields_sql($context));

        // Two fields with table prefix
        $CFG->showuseridentity = 'frog,zombie';
        $this->assertEquals(', u1.frog, u1.zombie', get_extra_user_fields_sql($context, 'u1'));

        // Two fields with field prefix
        $CFG->showuseridentity = 'frog,zombie';
        $this->assertEquals(', frog AS u_frog, zombie AS u_zombie',
            get_extra_user_fields_sql($context, '', 'u_'));

        // One field excluded
        $CFG->showuseridentity = 'frog';
        $this->assertEquals('', get_extra_user_fields_sql($context, '', '', array('frog')));

        // Two fields, one excluded, table+field prefix
        $CFG->showuseridentity = 'frog,zombie';
        $this->assertEquals(', u1.zombie AS u_zombie',
            get_extra_user_fields_sql($context, 'u1', 'u_', array('frog')));

        // As long as this test passes, the value will be set back. This is only
        // in-memory anyhow
        $CFG->showuseridentity = $oldshowuseridentity;
        $USER = $olduser;
    }

 /**
  * Return array of users whose progress is tracked in this course
  *
  * Optionally supply a search's where caluse, group id, sorting, paging
  *
  * @param string $where Where clause sql (optional)
  * @param array $where_params Where clause params (optional)
  * @param integer $groupid Group ID to restrict to (optional)
  * @param string $sort Order by clause (optional)
  * @param integer $limitfrom Result start (optional)
  * @param integer $limitnum Result max size (optional)
  * @param context $extracontext If set, includes extra user information fields
  *   as appropriate to display for current user in this context
  * @return array
  */
 public function get_tracked_users($where = '', $where_params = array(), $groupid = 0, $sort = '', $limitfrom = '', $limitnum = '', context $extracontext = null)
 {
     global $DB;
     $tracked = $this->generate_tracked_user_sql($groupid);
     $params = $tracked->data;
     $sql = "\n            SELECT\n                u.id,\n                u.firstname,\n                u.lastname,\n                u.idnumber\n        ";
     if ($extracontext) {
         $sql .= get_extra_user_fields_sql($extracontext, 'u', '', array('idnumber'));
     }
     $sql .= $tracked->sql;
     if ($where) {
         $sql .= " AND {$where}";
         $params = array_merge($params, $where_params);
     }
     if ($sort) {
         $sql .= " ORDER BY {$sort}";
     }
     $users = $DB->get_records_sql($sql, $params, $limitfrom, $limitnum);
     return $users ? $users : array();
     // In case it returns false
 }

 /**
  * get information about particular users with their registrations/queues
  *
  * @param int $groupingid optional get only this grouping
  * @param int $groupid optional get only this group
  * @param int|array $userids optional get only this user(s)
  * @param stdClass[] $orderby array how data should be sorted (column as key and ASC/DESC as value)
  * @return stdClass[] array of objects records from DB with all necessary data
  */
 public function get_user_data($groupingid = 0, $groupid = 0, $userids = 0, $orderby = array())
 {
     global $DB, $PAGE, $OUTPUT;
     // After which table-fields can we sort?
     $sortable = array('firstname', 'lastname', 'idnumber', 'email');
     $return = new stdClass();
     // Indexed by agrpid!
     $agrps = $this->get_active_groups(false, false, 0, $groupid, $groupingid, false);
     $agrpids = array_keys($agrps);
     if (!empty($agrpids)) {
         list($agrpsql, $agrpparams) = $DB->get_in_or_equal($agrpids);
     } else {
         $agrpsql = '';
         $agrpparams = array();
         echo $OUTPUT->box($OUTPUT->notification(get_string('no_groups_to_display', 'grouptool'), 'notifyproblem'), 'generalbox centered');
     }
     if (!empty($userids)) {
         if (!is_array($userids)) {
             $userids = array($userids);
         }
         list($usersql, $userparams) = $DB->get_in_or_equal($userids);
     } else {
         $usersql = ' LIKE *';
         $userparams = array();
     }
     $extrauserfields = get_extra_user_fields_sql($this->context, 'u');
     $mainuserfields = user_picture::fields('u', array('idnumber', 'email'));
     $orderbystring = "";
     if (!empty($orderby)) {
         foreach ($orderby as $field => $direction) {
             if (in_array($field, $sortable)) {
                 if ($orderbystring != "") {
                     $orderbystring .= ", ";
                 } else {
                     $orderbystring .= " ORDER BY";
                 }
                 $orderbystring .= " " . $field . " " . (!empty($direction) && $direction == 'ASC' ? 'ASC' : 'DESC');
             } else {
                 unset($orderby[$field]);
             }
         }
     }
     $sql = "SELECT {$mainuserfields} {$extrauserfields} " . "FROM {user} u " . "WHERE u.id " . $usersql . $orderbystring;
     $params = array_merge($userparams);
     $data = $DB->get_records_sql($sql, $params);
     // Add reg and queue data...
     if (!empty($agrpsql)) {
         foreach ($data as $idx => &$cur) {
             $sql = "SELECT agrps.id\n                          FROM {grouptool_registered} regs\n                     LEFT JOIN {grouptool_agrps}      agrps ON regs.agrpid = agrps.id\n                     LEFT JOIN {groups}               grps  ON agrps.groupid = grps.id\n                         WHERE regs.modified_by >= 0\n                               AND regs.userid = ?\n                               AND regs.agrpid " . $agrpsql;
             $params = array_merge(array($cur->id), $agrpparams);
             $cur->regs = $DB->get_fieldset_sql($sql, $params);
             $sql = "SELECT agrps.id\n                          FROM {grouptool_queued} queued\n                     LEFT JOIN {grouptool_agrps}  agrps ON queued.agrpid = agrps.id\n                     LEFT JOIN {groups}           grps  ON agrps.groupid = grps.id\n                         WHERE queued.userid = ?\n                               AND queued.agrpid " . $agrpsql;
             $params = array_merge(array($cur->id), $agrpparams);
             $cur->queued = $DB->get_fieldset_sql($sql, $params);
         }
     }
     return $data;
 }

    /**
     * Contruct all the parts of the main database query.
     * @param object $quiz the quiz settings.
     * @param string $qmsubselect SQL fragment from {@link quiz_report_qm_filter_select()}.
     * @param bool $qmfilter whether to show all, or only the final grade attempt.
     * @param int $attemptsmode which attempts to show.
     *      One of the QUIZ_REPORT_ATTEMPTS_... constants.
     * @param array $reportstudents list if userids of users to include in the report.
     * @return array with 4 elements ($fields, $from, $where, $params) that can be used to
     *      build the actual database query.
     */
    protected function base_sql($quiz, $qmsubselect, $qmfilter, $attemptsmode, $reportstudents) {
        global $DB;

        $fields = $DB->sql_concat('u.id', "'#'", 'COALESCE(quiza.attempt, 0)') . ' AS uniqueid,';

        if ($qmsubselect) {
            $fields .= "\n(CASE WHEN $qmsubselect THEN 1 ELSE 0 END) AS gradedattempt,";
        }

        $extrafields = get_extra_user_fields_sql($this->context, 'u', '',
                array('id', 'idnumber', 'firstname', 'lastname', 'picture',
                'imagealt', 'institution', 'department', 'email'));
        $fields .= '
                quiza.uniqueid AS usageid,
                quiza.id AS attempt,
                u.id AS userid,
                u.idnumber,
                u.firstname,
                u.lastname,
                u.picture,
                u.imagealt,
                u.institution,
                u.department,
                u.email' . $extrafields . ',
                quiza.sumgrades,
                quiza.timefinish,
                quiza.timestart,
                CASE WHEN quiza.timefinish = 0 THEN null
                     WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart
                     ELSE 0 END AS duration';
            // To explain that last bit, in MySQL, qa.timestart and qa.timefinish
            // are unsigned. Since MySQL 5.5.5, when they introduced strict mode,
            // subtracting a larger unsigned int from a smaller one gave an error.
            // Therefore, we avoid doing that. timefinish can be non-zero and less
            // than timestart when you have two load-balanced servers with very
            // badly synchronised clocks, and a student does a really quick attempt.';

        // This part is the same for all cases - join users and quiz_attempts tables
        $from = "\n{user} u";
        $from .= "\nLEFT JOIN {quiz_attempts} quiza ON
                                    quiza.userid = u.id AND quiza.quiz = :quizid";
        $params = array('quizid' => $quiz->id);

        if ($qmsubselect && $qmfilter) {
            $from .= " AND $qmsubselect";
        }
        switch ($attemptsmode) {
            case QUIZ_REPORT_ATTEMPTS_ALL:
                // Show all attempts, including students who are no longer in the course
                $where = 'quiza.id IS NOT NULL AND quiza.preview = 0';
                break;
            case QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH:
                // Show only students with attempts
                list($usql, $uparams) = $DB->get_in_or_equal(
                        $reportstudents, SQL_PARAMS_NAMED, 'u');
                $params += $uparams;
                $where = "u.id $usql AND quiza.preview = 0 AND quiza.id IS NOT NULL";
                break;
            case QUIZ_REPORT_ATTEMPTS_STUDENTS_WITH_NO:
                // Show only students without attempts
                list($usql, $uparams) = $DB->get_in_or_equal(
                        $reportstudents, SQL_PARAMS_NAMED, 'u');
                $params += $uparams;
                $where = "u.id $usql AND quiza.id IS NULL";
                break;
            case QUIZ_REPORT_ATTEMPTS_ALL_STUDENTS:
                // Show all students with or without attempts
                list($usql, $uparams) = $DB->get_in_or_equal(
                        $reportstudents, SQL_PARAMS_NAMED, 'u');
                $params += $uparams;
                $where = "u.id $usql AND (quiza.preview = 0 OR quiza.preview IS NULL)";
                break;
        }

        return array($fields, $from, $where, $params);
    }

function local_mail_getsqlrecipients($courseid, $search, $groupid, $roleid, $recipients = false) {
    global $CFG, $USER, $DB;

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

    $mailsamerole = has_capability('local/mail:mailsamerole', $context);

    list($esql, $params) = get_enrolled_sql($context, null, $groupid, true);
    $joins = array("FROM {user} u");
    $wheres = array();

    $mainuserfields = user_picture::fields('u', array('username', 'email', 'city', 'country', 'lang', 'timezone', 'maildisplay'));

    $extrasql = get_extra_user_fields_sql($context, 'u', '', array(
            'id', 'firstname', 'lastname'));
    $select = "SELECT $mainuserfields$extrasql";
    $joins[] = "JOIN ($esql) e ON e.id = u.id";

    // Performance hacks - we preload user contexts together with accounts.
    $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');
    $ccjoin = "LEFT JOIN {context} ctx ON (ctx.instanceid = u.id AND ctx.contextlevel = :contextlevel)";
    $params['contextlevel'] = CONTEXT_USER;
    $select .= $ccselect;
    $joins[] = $ccjoin;

    if (!$mailsamerole) {
        $userroleids = local_mail_get_user_roleids($USER->id, $context);
        list($relctxsql, $reldctxparams) = $DB->get_in_or_equal($context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relctx');
        list($samerolesql, $sameroleparams) = $DB->get_in_or_equal($userroleids, SQL_PARAMS_NAMED, 'samerole' , false);
        $wheres[] = "u.id IN (SELECT userid FROM {role_assignments} WHERE roleid $samerolesql AND contextid $relctxsql)";
        $params = array_merge($params, array('roleid' => $roleid), $sameroleparams, $reldctxparams);
    }

    if ($roleid) {
        // 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');
        $wheres[] = "u.id IN (SELECT userid FROM {role_assignments} WHERE roleid = :roleid AND contextid $relatedctxsql)";
        $params = array_merge($params, array('roleid' => $roleid), $relatedctxparams);
    }

    $from = implode("\n", $joins);

    if (!empty($search)) {
        $fullname = $DB->sql_fullname('u.firstname', 'u.lastname');
        $wheres[] = "(". $DB->sql_like($fullname, ':search1', false, false) .") ";
        $params['search1'] = "%$search%";
    }

    $from = implode("\n", $joins);

    $wheres[] = 'u.id <> :guestid AND u.deleted = 0 AND u.confirmed = 1 AND u.id <> :userid';
    $params['userid'] = $USER->id;
    $params['guestid'] = $CFG->siteguest;

    if ($recipients) {
        $wheres[] = 'u.id IN ('.preg_replace('/^,|,$/', '', $recipients).')';
    }

    $where = "WHERE " . implode(" AND ", $wheres);

    $sort = 'ORDER BY u.lastname ASC, u.firstname ASC';

    return array($select, $from, $where, $sort, $params);
}

function get_users_listing_subadmin($sort = 'lastaccess', $dir = 'ASC', $page = 0, $recordsperpage = 0, $search = '', $firstinitial = '', $lastinitial = '', $extraselect = '', array $extraparams = null, $extracontext = null, $join = '')
{
    global $DB;
    $fullname = $DB->sql_fullname();
    $select = "u.deleted <> 1";
    $params = array();
    if (!empty($search)) {
        $search = trim($search);
        $select .= " AND (" . $DB->sql_like($fullname, ':search1', false, false) . " OR " . $DB->sql_like('u.email', ':search2', false, false) . " OR u.username = :search3)";
        $params['search1'] = "%{$search}%";
        $params['search2'] = "%{$search}%";
        $params['search3'] = "{$search}";
    }
    if ($firstinitial) {
        $select .= " AND " . $DB->sql_like('u.firstname', ':fni', false, false);
        $params['fni'] = "{$firstinitial}%";
    }
    if ($lastinitial) {
        $select .= " AND " . $DB->sql_like('u.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, '', '', array('id', 'username', 'email', 'firstname', 'lastname', 'city', 'country', 'lastaccess', 'confirmed', 'mnethostid'));
    }
    // warning: will return UNCONFIRMED USERS
    return $DB->get_records_sql("SELECT u.*\n                                   FROM {user} u\n\t\t\t\t\t\t\t\t   {$join}\n                                  WHERE {$select}\n                                  {$sort}", $params, $page, $recordsperpage);
}

 public function test_get_extra_user_fields_sql()
 {
     global $CFG, $USER, $DB;
     $this->resetAfterTest();
     $this->setAdminUser();
     $context = context_system::instance();
     // No fields.
     $CFG->showuseridentity = '';
     $this->assertSame('', get_extra_user_fields_sql($context));
     // One field.
     $CFG->showuseridentity = 'frog';
     $this->assertSame(', frog', get_extra_user_fields_sql($context));
     // Two fields with table prefix.
     $CFG->showuseridentity = 'frog,zombie';
     $this->assertSame(', u1.frog, u1.zombie', get_extra_user_fields_sql($context, 'u1'));
     // Two fields with field prefix.
     $CFG->showuseridentity = 'frog,zombie';
     $this->assertSame(', frog AS u_frog, zombie AS u_zombie', get_extra_user_fields_sql($context, '', 'u_'));
     // One field excluded.
     $CFG->showuseridentity = 'frog';
     $this->assertSame('', get_extra_user_fields_sql($context, '', '', array('frog')));
     // Two fields, one excluded, table+field prefix.
     $CFG->showuseridentity = 'frog,zombie';
     $this->assertEquals(', u1.zombie AS u_zombie', get_extra_user_fields_sql($context, 'u1', 'u_', array('frog')));
 }

    /**
     * Contruct all the parts of the main database query.
     * @param array $reportstudents list if userids of users to include in the report.
     * @return array with 4 elements ($fields, $from, $where, $params) that can be used to
     *      build the actual database query.
     */
    public function base_sql($reportstudents) {
        global $DB;

        $fields = $DB->sql_concat('u.id', "'#'", 'COALESCE(quiza.attempt, 0)') . ' AS uniqueid,';

        if ($this->qmsubselect) {
            $fields .= "\n(CASE WHEN $this->qmsubselect THEN 1 ELSE 0 END) AS gradedattempt,";
        }

        $extrafields = get_extra_user_fields_sql($this->context, 'u', '',
                array('id', 'idnumber', 'firstname', 'lastname', 'picture',
                'imagealt', 'institution', 'department', 'email'));
        $fields .= '
                quiza.uniqueid AS usageid,
                quiza.id AS attempt,
                u.id AS userid,
                u.idnumber,
                u.firstname,
                u.lastname,
                u.picture,
                u.imagealt,
                u.institution,
                u.department,
                u.email' . $extrafields . ',
                quiza.state,
                quiza.sumgrades,
                quiza.timefinish,
                quiza.timestart,
                CASE WHEN quiza.timefinish = 0 THEN null
                     WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart
                     ELSE 0 END AS duration';
            // To explain that last bit, timefinish can be non-zero and less
            // than timestart when you have two load-balanced servers with very
            // badly synchronised clocks, and a student does a really quick attempt.

        // This part is the same for all cases. Join the users and quiz_attempts tables.
        $from = "\n{user} u";
        $from .= "\nLEFT JOIN {quiz_attempts} quiza ON
                                    quiza.userid = u.id AND quiza.quiz = :quizid";
        $params = array('quizid' => $this->quiz->id);