PHP get_related_contexts_string函数代码示例

 /**
  * Initialise the iterator
  * @return boolean success
  */
 function init()
 {
     global $CFG;
     $this->close();
     grade_regrade_final_grades($this->course->id);
     $course_item = grade_item::fetch_course_item($this->course->id);
     if ($course_item->needsupdate) {
         // can not calculate all final grades - sorry
         return false;
     }
     if (strpos($CFG->gradebookroles, ',') !== false) {
         $gradebookroles = " = {$CFG->gradebookroles}";
     } else {
         $gradebookroles = " IN ({$CFG->gradebookroles})";
     }
     $relatedcontexts = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $this->course->id));
     if ($this->groupid) {
         $groupsql = "INNER JOIN {$CFG->prefix}groups_members gm ON gm.userid = u.id";
         $groupwheresql = "AND gm.groupid = {$this->groupid}";
     } else {
         $groupsql = "";
         $groupwheresql = "";
     }
     $users_sql = "SELECT u.*\n                        FROM {$CFG->prefix}user u\n                             INNER JOIN {$CFG->prefix}role_assignments ra ON u.id = ra.userid\n                             {$groupsql}\n                       WHERE ra.roleid {$gradebookroles}\n                             AND ra.contextid {$relatedcontexts}\n                             {$groupwheresql}\n                    ORDER BY u.id ASC";
     $this->users_rs = get_recordset_sql($users_sql);
     if (!empty($this->grade_items)) {
         $itemids = array_keys($this->grade_items);
         $itemids = implode(',', $itemids);
         $grades_sql = "SELECT g.*\n                             FROM {$CFG->prefix}grade_grades g\n                                  INNER JOIN {$CFG->prefix}user u ON g.userid = u.id\n                                  INNER JOIN {$CFG->prefix}role_assignments ra ON u.id = ra.userid\n                                  {$groupsql}\n                            WHERE ra.roleid {$gradebookroles}\n                                  AND ra.contextid {$relatedcontexts}\n                                  AND g.itemid IN ({$itemids})\n                                  {$groupwheresql}\n                         ORDER BY g.userid ASC, g.itemid ASC";
         $this->grades_rs = get_recordset_sql($grades_sql);
     }
     return true;
 }

 /**
  * Initialise the iterator
  * @return boolean success
  */
 public function init()
 {
     global $CFG, $DB;
     $this->close();
     grade_regrade_final_grades($this->course->id);
     $course_item = grade_item::fetch_course_item($this->course->id);
     if ($course_item->needsupdate) {
         // can not calculate all final grades - sorry
         return false;
     }
     $coursecontext = get_context_instance(CONTEXT_COURSE, $this->course->id);
     $relatedcontexts = get_related_contexts_string($coursecontext);
     list($gradebookroles_sql, $params) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles), SQL_PARAMS_NAMED, 'grbr');
     //limit to users with an active enrolment
     list($enrolledsql, $enrolledparams) = get_enrolled_sql($coursecontext);
     $params = array_merge($params, $enrolledparams);
     if ($this->groupid) {
         $groupsql = "INNER JOIN {groups_members} gm ON gm.userid = u.id";
         $groupwheresql = "AND gm.groupid = :groupid";
         // $params contents: gradebookroles
         $params['groupid'] = $this->groupid;
     } else {
         $groupsql = "";
         $groupwheresql = "";
     }
     if (empty($this->sortfield1)) {
         // we must do some sorting even if not specified
         $ofields = ", u.id AS usrt";
         $order = "usrt ASC";
     } else {
         $ofields = ", u.{$this->sortfield1} AS usrt1";
         $order = "usrt1 {$this->sortorder1}";
         if (!empty($this->sortfield2)) {
             $ofields .= ", u.{$this->sortfield2} AS usrt2";
             $order .= ", usrt2 {$this->sortorder2}";
         }
         if ($this->sortfield1 != 'id' and $this->sortfield2 != 'id') {
             // user order MUST be the same in both queries,
             // must include the only unique user->id if not already present
             $ofields .= ", u.id AS usrt";
             $order .= ", usrt ASC";
         }
     }
     // $params contents: gradebookroles and groupid (for $groupwheresql)
     $users_sql = "SELECT u.* {$ofields}\n                        FROM {user} u\n                        JOIN ({$enrolledsql}) je ON je.id = u.id\n                             {$groupsql}\n                        JOIN (\n                                  SELECT DISTINCT ra.userid\n                                    FROM {role_assignments} ra\n                                   WHERE ra.roleid {$gradebookroles_sql}\n                                     AND ra.contextid {$relatedcontexts}\n                             ) rainner ON rainner.userid = u.id\n                         WHERE u.deleted = 0\n                             {$groupwheresql}\n                    ORDER BY {$order}";
     $this->users_rs = $DB->get_recordset_sql($users_sql, $params);
     if (!empty($this->grade_items)) {
         $itemids = array_keys($this->grade_items);
         list($itemidsql, $grades_params) = $DB->get_in_or_equal($itemids, SQL_PARAMS_NAMED, 'items');
         $params = array_merge($params, $grades_params);
         // $params contents: gradebookroles, enrolledparams, groupid (for $groupwheresql) and itemids
         $grades_sql = "SELECT g.* {$ofields}\n                             FROM {grade_grades} g\n                             JOIN {user} u ON g.userid = u.id\n                             JOIN ({$enrolledsql}) je ON je.id = u.id\n                                  {$groupsql}\n                             JOIN (\n                                      SELECT DISTINCT ra.userid\n                                        FROM {role_assignments} ra\n                                       WHERE ra.roleid {$gradebookroles_sql}\n                                         AND ra.contextid {$relatedcontexts}\n                                  ) rainner ON rainner.userid = u.id\n                              WHERE u.deleted = 0\n                              AND g.itemid {$itemidsql}\n                              {$groupwheresql}\n                         ORDER BY {$order}, g.itemid ASC";
         $this->grades_rs = $DB->get_recordset_sql($grades_sql, $params);
     } else {
         $this->grades_rs = false;
     }
     return true;
 }

 /**
  * Initialise the iterator
  * @return boolean success
  */
 function init()
 {
     global $CFG;
     $this->close();
     grade_regrade_final_grades($this->course->id);
     $course_item = grade_item::fetch_course_item($this->course->id);
     if ($course_item->needsupdate) {
         // can not calculate all final grades - sorry
         return false;
     }
     if (strpos($CFG->gradebookroles, ',') === false) {
         $gradebookroles = " = {$CFG->gradebookroles}";
     } else {
         $gradebookroles = " IN ({$CFG->gradebookroles})";
     }
     $relatedcontexts = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $this->course->id));
     if ($this->groupid) {
         $groupsql = "INNER JOIN {$CFG->prefix}groups_members gm ON gm.userid = u.id";
         $groupwheresql = "AND gm.groupid = {$this->groupid}";
     } else {
         $groupsql = "";
         $groupwheresql = "";
     }
     if (empty($this->sortfield1)) {
         // we must do some sorting even if not specified
         $ofields = ", u.id AS usrt";
         $order = "usrt ASC";
     } else {
         $ofields = ", u.{$this->sortfield1} AS usrt1";
         $order = "usrt1 {$this->sortorder1}";
         if (!empty($this->sortfield2)) {
             $ofields .= ", u.{$this->sortfield2} AS usrt2";
             $order .= ", usrt2 {$this->sortorder2}";
         }
         if ($this->sortfield1 != 'id' and $this->sortfield2 != 'id') {
             // user order MUST be the same in both queries, must include the only unique user->id if not already present
             $ofields .= ", u.id AS usrt";
             $order .= ", usrt ASC";
         }
     }
     $users_sql = "SELECT u.* {$ofields}\n                        FROM {$CFG->prefix}user u\n                             INNER JOIN {$CFG->prefix}role_assignments ra ON u.id = ra.userid\n                             {$groupsql}\n                       WHERE ra.roleid {$gradebookroles}\n                             AND ra.contextid {$relatedcontexts}\n                             {$groupwheresql}\n                    ORDER BY {$order}";
     $this->users_rs = get_recordset_sql($users_sql);
     if (!empty($this->grade_items)) {
         $itemids = array_keys($this->grade_items);
         $itemids = implode(',', $itemids);
         $grades_sql = "SELECT g.* {$ofields}\n                             FROM {$CFG->prefix}grade_grades g\n                                  INNER JOIN {$CFG->prefix}user u ON g.userid = u.id\n                                  INNER JOIN {$CFG->prefix}role_assignments ra ON u.id = ra.userid\n                                  {$groupsql}\n                            WHERE ra.roleid {$gradebookroles}\n                                  AND ra.contextid {$relatedcontexts}\n                                  AND g.itemid IN ({$itemids})\n                                  {$groupwheresql}\n                         ORDER BY {$order}, g.itemid ASC";
         $this->grades_rs = get_recordset_sql($grades_sql);
     } else {
         $this->grades_rs = false;
     }
     return true;
 }

 public function find_users($search)
 {
     global $DB;
     $context = context_course::instance($this->courseid);
     list($wherecondition, $params) = $this->search_sql($search, 'u');
     list($enrolledsql, $enrolledparams) = get_enrolled_sql($context, '', $this->groupid, true);
     $params = array_merge($params, $enrolledparams);
     $params['courseid'] = $this->courseid;
     $fields = 'SELECT r.id AS roleid, ' . 'r.shortname AS roleshortname, ' . 'r.name AS rolename, ' . 'u.id AS userid, ' . $this->required_fields_sql('u');
     $countfields = 'SELECT COUNT(1)';
     $sql = ' FROM {user} u JOIN (' . $enrolledsql . ') e ON e.id = u.id' . ' LEFT JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.contextid ' . get_related_contexts_string($context) . ')' . ' LEFT JOIN {role} r ON r.id = ra.roleid' . ' WHERE ' . $wherecondition;
     $order = ' ORDER BY r.sortorder, u.lastname ASC, u.firstname ASC';
     if (!$this->is_validating()) {
         $count = $DB->count_records_sql($countfields . $sql, $params);
         if ($count > 100) {
             return $this->too_many_results($search, $count);
         }
     }
     $rs = $DB->get_recordset_sql($fields . $sql . $order, $params);
     $roles = groups_calculate_role_people($rs, $context);
     return $this->convert_array_format($roles, $search);
 }

 public function find_users($search)
 {
     global $DB;
     // Get list of allowed roles.
     $context = get_context_instance(CONTEXT_COURSE, $this->courseid);
     if ($validroleids = groups_get_possible_roles($context)) {
         list($roleids, $roleparams) = $DB->get_in_or_equal($validroleids, SQL_PARAMS_NAMED, 'r');
     } else {
         $roleids = " = -1";
         $roleparams = array();
     }
     // Get the search condition.
     list($searchcondition, $searchparams) = $this->search_sql($search, 'u');
     // Build the SQL
     list($enrolsql, $enrolparams) = get_enrolled_sql($context);
     $fields = "SELECT r.id AS roleid, r.shortname AS roleshortname, r.name AS rolename, u.id AS userid,\n                          " . $this->required_fields_sql('u') . ",\n                          (SELECT count(igm.groupid)\n                             FROM {groups_members} igm\n                             JOIN {groups} ig ON igm.groupid = ig.id\n                            WHERE igm.userid = u.id AND ig.courseid = :courseid) AS numgroups";
     $sql = "   FROM {user} u\n                   JOIN ({$enrolsql}) e ON e.id = u.id\n              LEFT JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.contextid " . get_related_contexts_string($context) . " AND ra.roleid {$roleids})\n              LEFT JOIN {role} r ON r.id = ra.roleid\n                  WHERE u.deleted = 0\n                        AND u.id NOT IN (SELECT userid\n                                          FROM {groups_members}\n                                         WHERE groupid = :groupid)\n                        AND {$searchcondition}";
     $orderby = "ORDER BY u.lastname, u.firstname";
     $params = array_merge($searchparams, $roleparams, $enrolparams);
     $params['courseid'] = $this->courseid;
     $params['groupid'] = $this->groupid;
     if (!$this->is_validating()) {
         $potentialmemberscount = $DB->count_records_sql("SELECT COUNT(DISTINCT u.id) {$sql}", $params);
         if ($potentialmemberscount > group_non_members_selector::MAX_USERS_PER_PAGE) {
             return $this->too_many_results($search, $potentialmemberscount);
         }
     }
     $rs = $DB->get_recordset_sql("{$fields} {$sql} {$orderby}", $params);
     $roles = groups_calculate_role_people($rs, $context);
     //don't hold onto user IDs if we're doing validation
     if (empty($this->validatinguserids)) {
         if ($roles) {
             foreach ($roles as $k => $v) {
                 if ($v) {
                     foreach ($v->users as $uid => $userobject) {
                         $this->potentialmembersids[] = $uid;
                     }
                 }
             }
         }
     }
     return $this->convert_array_format($roles, $search);
 }

/**
 * This function returns an array of grades that were included in the import,
 * but wherer the user does not currenly have a graded role on the course. These gradse
 * are still stored in the database, but will not be visible in the gradebook unless
 * this user subsequently enrols on the course in a graded roles.
 *
 * The returned objects have fields user firstname, lastname and useridnumber, and gradeidnumber.
 *
 * @param integer $importcode import batch identifier
 * @param integer $courseid the course we are importing to.
 * @return mixed and array of user objects, or false if none.
 */
function get_unenrolled_users_in_import($importcode, $courseid)
{
    global $CFG, $DB;
    $relatedctxcondition = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $courseid));
    list($usql, $params) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles));
    $sql = "SELECT giv.id, u.firstname, u.lastname, u.idnumber AS useridnumber,\n                COALESCE(gi.idnumber, gin.itemname) AS gradeidnumber\n            FROM\n                {grade_import_values} giv\n                JOIN {user} u ON giv.userid = u.id\n                LEFT JOIN {grade_items} gi ON gi.id = giv.itemid\n                LEFT JOIN {grade_import_newitem} gin ON gin.id = giv.newgradeitem\n                LEFT JOIN {role_assignments} ra ON (giv.userid = ra.userid AND\n                    ra.roleid {$usql} AND\n                    ra.contextid {$relatedctxcondition})\n                WHERE giv.importcode = ?\n                    AND ra.id IS NULL\n                ORDER BY gradeidnumber, u.lastname, u.firstname";
    $params[] = $importcode;
    return $DB->get_records_sql($sql, $params);
}

    public function find_users($search) {
        global $DB;

        // Get list of allowed roles.
        $context = context_course::instance($this->courseid);
        if ($validroleids = groups_get_possible_roles($context)) {
            list($roleids, $roleparams) = $DB->get_in_or_equal($validroleids, SQL_PARAMS_NAMED, 'r');
        } else {
            $roleids = " = -1";
            $roleparams = array();
        }

        // Get the search condition.
        list($searchcondition, $searchparams) = $this->search_sql($search, 'u');

        // Build the SQL
        list($enrolsql, $enrolparams) = get_enrolled_sql($context);
        $fields = "SELECT r.id AS roleid, u.id AS userid,
                          " . $this->required_fields_sql('u') . ",
                          (SELECT count(igm.groupid)
                             FROM {groups_members} igm
                             JOIN {groups} ig ON igm.groupid = ig.id
                            WHERE igm.userid = u.id AND ig.courseid = :courseid) AS numgroups";
        $sql = "   FROM {user} u
                   JOIN ($enrolsql) e ON e.id = u.id
              LEFT JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.contextid " . get_related_contexts_string($context) . " AND ra.roleid $roleids)
              LEFT JOIN {role} r ON r.id = ra.roleid
              LEFT JOIN {groups_members} gm ON (gm.userid = u.id AND gm.groupid = :groupid)
                  WHERE u.deleted = 0
                        AND gm.id IS NULL
                        AND $searchcondition";

        list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext);
        $orderby = ' ORDER BY ' . $sort;

        $params = array_merge($searchparams, $roleparams, $enrolparams);
        $params['courseid'] = $this->courseid;
        $params['groupid']  = $this->groupid;

        if (!$this->is_validating()) {
            $potentialmemberscount = $DB->count_records_sql("SELECT COUNT(DISTINCT u.id) $sql", $params);
            if ($potentialmemberscount > group_non_members_selector::MAX_USERS_PER_PAGE) {
                return $this->too_many_results($search, $potentialmemberscount);
            }
        }

        $rs = $DB->get_recordset_sql("$fields $sql $orderby", array_merge($params, $sortparams));
        $roles =  groups_calculate_role_people($rs, $context);

        //don't hold onto user IDs if we're doing validation
        if (empty($this->validatinguserids) ) {
            if($roles) {
                foreach($roles as $k=>$v) {
                    if($v) {
                        foreach($v->users as $uid=>$userobject) {
                            $this->potentialmembersids[] = $uid;
                        }
                    }
                }
            }
        }

        return $this->convert_array_format($roles, $search);
    }

/**
 * Search through course users
 *
 * If $coursid specifies the site course then this function searches
 * through all undeleted and confirmed users
 * @param int $courseid The course in question.
 * @param string $searchtext the text to search for
 * @param string $sort the column name to order by
 * @param string $exceptions comma separated list of user IDs to exclude
 * @return array  An array of {@link $USER} records.
 */
function message_search_users($courseid, $searchtext, $sort='', $exceptions='') {
    global $CFG, $USER, $DB;

    $fullname = $DB->sql_fullname();

    if (!empty($exceptions)) {
        $except = ' AND u.id NOT IN ('. $exceptions .') ';
    } else {
        $except = '';
    }

    if (!empty($sort)) {
        $order = ' ORDER BY '. $sort;
    } else {
        $order = '';
    }

    $ufields = user_picture::fields('u');
    if (!$courseid or $courseid == SITEID) {
        $params = array($USER->id, "%$searchtext%");
        return $DB->get_records_sql("SELECT $ufields, mc.id as contactlistid, mc.blocked
                                       FROM {user} u
                                       LEFT JOIN {message_contacts} mc
                                            ON mc.contactid = u.id AND mc.userid = ?
                                      WHERE u.deleted = '0' AND u.confirmed = '1'
                                            AND (".$DB->sql_like($fullname, '?', false).")
                                            $except
                                     $order", $params);
    } else {
//TODO: add enabled enrolment join here (skodak)
        $context = get_context_instance(CONTEXT_COURSE, $courseid);
        $contextlists = get_related_contexts_string($context);

        // everyone who has a role assignment in this course or higher
        $params = array($USER->id, "%$searchtext%");
        $users = $DB->get_records_sql("SELECT $ufields, mc.id as contactlistid, mc.blocked
                                         FROM {user} u
                                         JOIN {role_assignments} ra ON ra.userid = u.id
                                         LEFT JOIN {message_contacts} mc
                                              ON mc.contactid = u.id AND mc.userid = ?
                                        WHERE u.deleted = '0' AND u.confirmed = '1'
                                              AND ra.contextid $contextlists
                                              AND (".$DB->sql_like($fullname, '?', false).")
                                              $except
                                       $order", $params);

        return $users;
    }
}

//.........这里部分代码省略.........
                     $headers[] = get_string('feedback', 'game');
                 }
                 $colnum = 0;
                 foreach ($headers as $item) {
                     $myxls->write(0, $colnum, $item, $formatbc);
                     $colnum++;
                 }
                 $rownum = 1;
             } else {
                 if ($download == 'CSV') {
                     $filename .= ".txt";
                     header("Content-Type: application/download\n");
                     header("Content-Disposition: attachment; filename=\"{$filename}\"");
                     header("Expires: 0");
                     header("Cache-Control: must-revalidate,post-check=0,pre-check=0");
                     header("Pragma: public");
                     $headers = get_string('fullname') . "\t" . get_string('startedon', 'game') . "\t" . get_string('timecompleted', 'game') . "\t" . get_string('attemptduration', 'game');
                     if ($game->grade) {
                         $headers .= "\t" . get_string('grade', 'game') . "/" . $game->grade;
                     }
                     if ($detailedmarks) {
                         foreach ($questionids as $id) {
                             $headers .= "\t#" . $questions[$id]->number;
                         }
                     }
                     if ($hasfeedback) {
                         $headers .= "\t" . get_string('feedback', 'game');
                     }
                     echo $headers . " \n";
                 }
             }
         }
     }
     $contextlists = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $course->id));
     // Construct the SQL.
     $select = 'SELECT qa.id,' . sql_concat('u.id', '\'#\'', $db->IfNull('qa.attempt', '0')) . ' AS uniqueid, ' . 'qa.id as attemptuniqueid, qa.id AS attempt, u.id AS userid, u.firstname, u.lastname, u.picture, ' . 'qa.score, qa.timefinish, qa.timestart, qa.timefinish - qa.timestart AS duration ';
     if ($course->id != SITEID) {
         // This is too complicated, so just do it for each of the four cases.
         if (!empty($currentgroup) && empty($noattempts)) {
             // We want a particular group and we only want to see students WITH attempts.
             // So join on groups_members and do an inner join on attempts.
             $from = 'FROM {user} u JOIN {role_assignments} ra ON ra.userid = u.id ' . groups_members_join_sql() . 'JOIN {game_attempts} qa ON u.id = qa.userid AND qa.gameid = ' . $game->id;
             $where = ' WHERE ra.contextid ' . $contextlists . ' AND ' . groups_members_where_sql($currentgroup) . ' AND qa.preview = 0';
         } else {
             if (!empty($currentgroup) && !empty($noattempts)) {
                 // We want a particular group and we want to do something funky with attempts.
                 // So join on groups_members and left join on attempts...
                 $from = 'FROM {user} u JOIN {role_assignments} ra ON ra.userid = u.id ' . groups_members_join_sql() . 'LEFT JOIN {game_attempts} qa ON u.id = qa.userid AND qa.gameid = ' . $game->id;
                 $where = ' WHERE ra.contextid ' . $contextlists . ' AND ' . groups_members_where_sql($currentgroup);
                 if ($noattempts == 1) {
                     // Noattempts = 1 means only no attempts, so make the left join ask.
                     // For only records where the right is null (no attempts).
                     $where .= ' AND qa.userid IS NULL';
                     // Show ONLY no attempts.
                 } else {
                     // We are including attempts, so exclude previews.
                     $where .= ' AND qa.preview = 0';
                 }
             } else {
                 if (empty($currentgroup)) {
                     // We don't care about group, and we to do something funky with attempts.
                     // So do a left join on attempts.
                     $from = 'FROM {user} u JOIN {role_assignments} ra ON ra.userid = u.id ' . ' LEFT JOIN {game_attempts} qa ON u.id = qa.userid AND qa.gameid = ' . $game->id;
                     $where = " WHERE ra.contextid {$contextlists}";
                     if (empty($noattempts)) {
                         // Show ONLY students with attempts.

/**
 * Searches logs to find all enrolments since a certain date
 *
 * used to print recent activity
 *
 * @param int $courseid The course in question.
 * @param int $timestart The date to check forward of
 * @return object|false  {@link $USER} records or false if error.
 */
function get_recent_enrolments($courseid, $timestart)
{
    global $DB;
    debugging('get_recent_enrolments() is deprecated as it returned inaccurate results.', DEBUG_DEVELOPER);
    $context = context_course::instance($courseid);
    $sql = "SELECT u.id, u.firstname, u.lastname, MAX(l.time)\n              FROM {user} u, {role_assignments} ra, {log} l\n             WHERE l.time > ?\n                   AND l.course = ?\n                   AND l.module = 'course'\n                   AND l.action = 'enrol'\n                   AND " . $DB->sql_cast_char2int('l.info') . " = u.id\n                   AND u.id = ra.userid\n                   AND ra.contextid " . get_related_contexts_string($context) . "\n          GROUP BY u.id, u.firstname, u.lastname\n          ORDER BY MAX(l.time) ASC";
    $params = array($timestart, $courseid);
    return $DB->get_records_sql($sql, $params);
}

    /**
     * Builds and return the row of averages for the right part of the grader report.
     * @param array $rows Whether to return only group averages or all averages.
     * @param bool $grouponly Whether to return only group averages or all averages.
     * @return array Array of rows for the right part of the report
     */
    public function get_right_avg_row($rows=array(), $grouponly=false) {
        global $CFG, $USER, $DB, $OUTPUT;

        if (!$this->canviewhidden) {
            // totals might be affected by hiding, if user can not see hidden grades the aggregations might be altered
            // better not show them at all if user can not see all hidden grades
            return $rows;
        }

        $showaverages = $this->get_pref('showaverages');
        $showaveragesgroup = $this->currentgroup && $showaverages;

        $averagesdisplaytype   = $this->get_pref('averagesdisplaytype');
        $averagesdecimalpoints = $this->get_pref('averagesdecimalpoints');
        $meanselection         = $this->get_pref('meanselection');
        $shownumberofgrades    = $this->get_pref('shownumberofgrades');

        $avghtml = '';
        $avgcssclass = 'avg';

        if ($grouponly) {
            $straverage = get_string('groupavg', 'grades');
            $showaverages = $this->currentgroup && $this->get_pref('showaverages');
            $groupsql = $this->groupsql;
            $groupwheresql = $this->groupwheresql;
            $groupwheresqlparams = $this->groupwheresql_params;
            $avgcssclass = 'groupavg';
        } else {
            $straverage = get_string('overallaverage', 'grades');
            $showaverages = $this->get_pref('showaverages');
            $groupsql = "";
            $groupwheresql = "";
            $groupwheresqlparams = array();
        }

        if ($shownumberofgrades) {
            $straverage .= ' (' . get_string('submissions', 'grades') . ') ';
        }

        $totalcount = $this->get_numusers($grouponly);

        //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);

        if ($showaverages) {
            $params = array_merge(array('courseid'=>$this->courseid), $gradebookrolesparams, $enrolledparams, $groupwheresqlparams);

            // find sums of all grade items in course
            $SQL = "SELECT g.itemid, SUM(g.finalgrade) AS sum
                      FROM {grade_items} gi
                      JOIN {grade_grades} g
                           ON g.itemid = gi.id
                      JOIN {user} u
                           ON u.id = g.userid
                      JOIN ($enrolledsql) je
                           ON je.id = u.id
                      JOIN {role_assignments} ra
                           ON ra.userid = u.id
                      $groupsql
                     WHERE gi.courseid = :courseid
                           AND ra.roleid $gradebookrolessql
                           AND ra.contextid ".get_related_contexts_string($this->context)."
                           AND g.finalgrade IS NOT NULL
                           $groupwheresql
                  GROUP BY g.itemid";
            $sumarray = array();
            if ($sums = $DB->get_records_sql($SQL, $params)) {
                foreach ($sums as $itemid => $csum) {
                    $sumarray[$itemid] = $csum->sum;
                }
            }

            // MDL-10875 Empty grades must be evaluated as grademin, NOT always 0
            // This query returns a count of ungraded grades (NULL finalgrade OR no matching record in grade_grades table)
            $SQL = "SELECT gi.id, COUNT(u.id) AS count
                      FROM {grade_items} gi
                      CROSS JOIN {user} u
                      JOIN ($enrolledsql) je
                           ON je.id = u.id
                      JOIN {role_assignments} ra
                           ON ra.userid = u.id
                      LEFT OUTER JOIN {grade_grades} g
                           ON (g.itemid = gi.id AND g.userid = u.id AND g.finalgrade IS NOT NULL)
                      $groupsql
                     WHERE gi.courseid = :courseid
                           AND ra.roleid $gradebookrolessql
                           AND ra.contextid ".get_related_contexts_string($this->context)."
                           AND g.id IS NULL
                           $groupwheresql
                  GROUP BY gi.id";

//.........这里部分代码省略.........

/**
 * This function returns an array of grades that were included in the import,
 * but wherer the user does not currenly have a graded role on the course. These gradse 
 * are still stored in the database, but will not be visible in the gradebook unless
 * this user subsequently enrols on the course in a graded roles.
 *
 * The returned objects have fields user firstname, lastname and useridnumber, and gradeidnumber.
 *
 * @param integer $importcode import batch identifier
 * @param integer $courseid the course we are importing to.
 * @return mixed and array of user objects, or false if none.
 */
function get_unenrolled_users_in_import($importcode, $courseid)
{
    global $CFG;
    $relatedctxcondition = get_related_contexts_string(get_context_instance(CONTEXT_COURSE, $courseid));
    $sql = "SELECT giv.id, u.firstname, u.lastname, u.idnumber AS useridnumber, \n                COALESCE(gi.idnumber, gin.itemname) AS gradeidnumber\n            FROM\n                {$CFG->prefix}grade_import_values giv\n                JOIN {$CFG->prefix}user u ON giv.userid = u.id\n                LEFT JOIN {$CFG->prefix}grade_items gi ON gi.id = giv.itemid\n                LEFT JOIN {$CFG->prefix}grade_import_newitem gin ON gin.id = giv.newgradeitem\n                LEFT JOIN {$CFG->prefix}role_assignments ra ON (giv.userid = ra.userid AND\n                    ra.roleid IN ({$CFG->gradebookroles}) AND\n                    ra.contextid {$relatedctxcondition})\n                WHERE giv.importcode = {$importcode}\n                    AND ra.id IS NULL\n                ORDER BY gradeidnumber, u.lastname, u.firstname";
    return get_records_sql($sql);
}

//.........这里部分代码省略.........
 * @param $sort - the sort order
 * @param $limitfrom - number of records to skip (offset)
 * @param $limitnum - number of records to fetch
 * @param $groups - single group or array of groups - group(s) user is in
 * @param $exceptions - list of users to exclude
 */
function internalmail_get_users_by_capability($context, $capability, $fields = '', $sort = 'u.firstname', $limitfrom = '', $limitnum = '', $groups = '', $exceptions = '', $doanything = true, $search = '', $firstinitial = '', $lastinitial = '')
{
    global $CFG, $USER, $COURSE;
    /// Sorting out groups
    if ($groups !== '') {
        $groupjoin = 'INNER JOIN ' . $CFG->prefix . 'groups_members gm ON gm.userid = ra.userid';
        if (is_array($groups)) {
            $groupsql = 'AND gm.groupid IN (' . implode(',', $groups) . ')';
        } else {
            if ($groups == 0) {
                if (!has_capability('block/email_list:viewallgroups', $context) && $COURSE->groupmode == 1) {
                    $groupids = groups_get_groups_for_user($USER->id, $COURSE->id);
                    $groupsql = 'AND gm.groupid IN (' . implode(',', $groupids) . ')';
                } else {
                    $groupsql = '';
                }
            } else {
                $groupsql = 'AND gm.groupid = ' . $groups;
            }
        }
    } else {
        $groupjoin = '';
        $groupsql = '';
    }
    /// Sorting out exceptions
    $exceptionsql = $exceptions ? "AND u.id NOT IN ({$exceptions})" : '';
    /// Set up default fields
    if (empty($fields)) {
        $fields = 'u.*, ul.timeaccess as lastaccess, ra.hidden';
    }
    /// Set up default sort
    if (empty($sort)) {
        $sortby = 'ul.timeaccess';
    }
    $sortby = $sort ? " ORDER BY {$sort} " : '';
    /// If context is a course, then construct sql for ul
    if ($context->contextlevel == CONTEXT_COURSE) {
        $courseid = $context->instanceid;
        $coursesql = "AND (ul.courseid = {$courseid} OR ul.courseid IS NULL)";
    } else {
        $coursesql = '';
    }
    $LIKE = sql_ilike();
    $fullname = sql_fullname();
    $search_sql = '';
    if (!empty($search)) {
        $search = trim($search);
        $search_sql .= " AND ({$fullname} {$LIKE} '%{$search}%' OR email {$LIKE} '%{$search}%' OR username {$LIKE} '%{$search}%' OR idnumber {$LIKE} '%{$search}%') ";
    }
    if ($firstinitial) {
        $search_sql .= ' AND firstname ' . $LIKE . ' \'' . $firstinitial . '%\'';
    }
    if ($lastinitial) {
        $search_sql .= ' AND lastname ' . $LIKE . ' \'' . $lastinitial . '%\'';
    }
    /// Sorting out roles with this capability set
    if ($possibleroles = get_roles_with_capability($capability, CAP_ALLOW, $context)) {
        if (!$doanything) {
            if (!($sitecontext = get_context_instance(CONTEXT_SYSTEM))) {
                return false;
                // Something is seriously wrong
            }
            $doanythingroles = get_roles_with_capability('moodle/site:doanything', CAP_ALLOW, $sitecontext);
        }
        $validroleids = array();
        foreach ($possibleroles as $possiblerole) {
            if (!$doanything) {
                if (isset($doanythingroles[$possiblerole->id])) {
                    // We don't want these included
                    continue;
                }
            }
            if ($caps = role_context_capabilities($possiblerole->id, $context, $capability)) {
                // resolved list
                if (isset($caps[$capability]) && $caps[$capability] > 0) {
                    // resolved capability > 0
                    $validroleids[] = $possiblerole->id;
                }
            }
        }
        if (empty($validroleids)) {
            return false;
        }
        $roleids = '(' . implode(',', $validroleids) . ')';
    } else {
        return false;
        // No need to continue, since no roles have this capability set
    }
    /// Construct the main SQL
    $select = " SELECT {$fields}";
    $from = " FROM {$CFG->prefix}user u\r\n                INNER JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id\r\n                INNER JOIN {$CFG->prefix}role r ON r.id = ra.roleid\r\n                LEFT OUTER JOIN {$CFG->prefix}user_lastaccess ul ON ul.userid = u.id\r\n                {$groupjoin}";
    $where = " WHERE ra.contextid " . get_related_contexts_string($context) . "\r\n                  AND u.deleted = 0\r\n                  AND ra.roleid in {$roleids}\r\n                      {$exceptionsql}\r\n                      {$coursesql}\r\n                      {$groupsql}\r\n                      {$search_sql}";
    return get_records_sql($select . $from . $where . $sortby, $limitfrom, $limitnum);
}

 /**
  * Initialise the iterator
  *
  * @return boolean success
  */
 public function init()
 {
     global $CFG, $DB;
     $this->close();
     grade_regrade_final_grades($this->course->id);
     $course_item = grade_item::fetch_course_item($this->course->id);
     if ($course_item->needsupdate) {
         // can not calculate all final grades - sorry
         return false;
     }
     $coursecontext = context_course::instance($this->course->id);
     $relatedcontexts = get_related_contexts_string($coursecontext);
     list($gradebookroles_sql, $params) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles), SQL_PARAMS_NAMED, 'grbr');
     list($enrolledsql, $enrolledparams) = get_enrolled_sql($coursecontext, '', 0, $this->onlyactive);
     $params = array_merge($params, $enrolledparams);
     if ($this->groupid) {
         $groupsql = "INNER JOIN {groups_members} gm ON gm.userid = u.id";
         $groupwheresql = "AND gm.groupid = :groupid";
         // $params contents: gradebookroles
         $params['groupid'] = $this->groupid;
     } else {
         $groupsql = "";
         $groupwheresql = "";
     }
     if (empty($this->sortfield1)) {
         // we must do some sorting even if not specified
         $ofields = ", u.id AS usrt";
         $order = "usrt ASC";
     } else {
         $ofields = ", u.{$this->sortfield1} AS usrt1";
         $order = "usrt1 {$this->sortorder1}";
         if (!empty($this->sortfield2)) {
             $ofields .= ", u.{$this->sortfield2} AS usrt2";
             $order .= ", usrt2 {$this->sortorder2}";
         }
         if ($this->sortfield1 != 'id' and $this->sortfield2 != 'id') {
             // user order MUST be the same in both queries,
             // must include the only unique user->id if not already present
             $ofields .= ", u.id AS usrt";
             $order .= ", usrt ASC";
         }
     }
     $userfields = 'u.*';
     $customfieldssql = '';
     if ($this->allowusercustomfields && !empty($CFG->grade_export_customprofilefields)) {
         $customfieldscount = 0;
         $customfieldsarray = grade_helper::get_user_profile_fields($this->course->id, $this->allowusercustomfields);
         foreach ($customfieldsarray as $field) {
             if (!empty($field->customid)) {
                 $customfieldssql .= "\n                            LEFT JOIN (SELECT * FROM {user_info_data}\n                                WHERE fieldid = :cf{$customfieldscount}) cf{$customfieldscount}\n                            ON u.id = cf{$customfieldscount}.userid";
                 $userfields .= ", cf{$customfieldscount}.data AS 'customfield_{$field->shortname}'";
                 $params['cf' . $customfieldscount] = $field->customid;
                 $customfieldscount++;
             }
         }
     }
     // $params contents: gradebookroles and groupid (for $groupwheresql)
     $users_sql = "SELECT {$userfields} {$ofields}\n                        FROM {user} u\n                        JOIN ({$enrolledsql}) je ON je.id = u.id\n                             {$groupsql} {$customfieldssql}\n                        JOIN (\n                                  SELECT DISTINCT ra.userid\n                                    FROM {role_assignments} ra\n                                   WHERE ra.roleid {$gradebookroles_sql}\n                                     AND ra.contextid {$relatedcontexts}\n                             ) rainner ON rainner.userid = u.id\n                         WHERE u.deleted = 0\n                             {$groupwheresql}\n                    ORDER BY {$order}";
     $this->users_rs = $DB->get_recordset_sql($users_sql, $params);
     if (!empty($this->grade_items)) {
         $itemids = array_keys($this->grade_items);
         list($itemidsql, $grades_params) = $DB->get_in_or_equal($itemids, SQL_PARAMS_NAMED, 'items');
         $params = array_merge($params, $grades_params);
         // $params contents: gradebookroles, enrolledparams, groupid (for $groupwheresql) and itemids
         $grades_sql = "SELECT g.* {$ofields}\n                             FROM {grade_grades} g\n                             JOIN {user} u ON g.userid = u.id\n                             JOIN ({$enrolledsql}) je ON je.id = u.id\n                                  {$groupsql}\n                             JOIN (\n                                      SELECT DISTINCT ra.userid\n                                        FROM {role_assignments} ra\n                                       WHERE ra.roleid {$gradebookroles_sql}\n                                         AND ra.contextid {$relatedcontexts}\n                                  ) rainner ON rainner.userid = u.id\n                              WHERE u.deleted = 0\n                              AND g.itemid {$itemidsql}\n                              {$groupwheresql}\n                         ORDER BY {$order}, g.itemid ASC";
         $this->grades_rs = $DB->get_recordset_sql($grades_sql, $params);
     } else {
         $this->grades_rs = false;
     }
     return true;
 }

/**
 * This function is used to print roles column in user profile page.
 * It is using the CFG->profileroles to limit the list to only interesting roles.
 * (The permission tab has full details of user role assignments.)
 *
 * @param int $userid
 * @param int $courseid
 * @return string
 */
function get_user_roles_in_course($userid, $courseid)
{
    global $CFG, $DB, $USER;
    if (empty($CFG->profileroles)) {
        return '';
    }
    if ($courseid == SITEID) {
        $context = get_context_instance(CONTEXT_SYSTEM);
    } else {
        $context = get_context_instance(CONTEXT_COURSE, $courseid);
    }
    if (empty($CFG->profileroles)) {
        return array();
    }
    $allowed = explode(',', $CFG->profileroles);
    list($rallowed, $params) = $DB->get_in_or_equal($allowed, SQL_PARAMS_NAMED);
    $contextlist = get_related_contexts_string($context);
    $sql = "SELECT DISTINCT r.id, r.name, r.shortname, r.sortorder\n              FROM {role_assignments} ra, {role} r\n             WHERE r.id = ra.roleid\n                   AND ra.contextid {$contextlist}\n                   AND r.id {$rallowed}\n                   AND ra.userid = :userid\n          ORDER BY r.sortorder ASC";
    $params['userid'] = $userid;
    $rolestring = '';
    if ($roles = $DB->get_records_sql($sql, $params)) {
        foreach ($roles as $userrole) {
            $rolenames[$userrole->id] = $userrole->name;
        }
        $rolenames = role_fix_names($rolenames, $context);
        // Substitute aliases
        foreach ($rolenames as $roleid => $rolename) {
            $rolenames[$roleid] = '<a href="' . $CFG->wwwroot . '/user/index.php?contextid=' . $context->id . '&amp;roleid=' . $roleid . '">' . $rolename . '</a>';
        }
        $rolestring = implode(',', $rolenames);
    }
    return $rolestring;
}