public function find_users($search)
 {
     global $DB;
     list($wherecondition, $params) = $this->search_sql($search, '');
     $fields = 'SELECT ' . $this->required_fields_sql('');
     $countfields = 'SELECT COUNT(1)';
     $sql = " FROM {user}\n                WHERE {$wherecondition}\n                      AND id NOT IN (\n                         SELECT r.userid\n                           FROM {role_assignments} r\n                          WHERE r.contextid = :contextid\n                                AND r.roleid = :roleid)";
     list($sort, $sortparams) = users_order_by_sql('', $search, $this->accesscontext);
     $order = ' ORDER BY ' . $sort;
     $params['contextid'] = $this->context->id;
     $params['roleid'] = $this->roleid;
     if (!$this->is_validating()) {
         $potentialmemberscount = $DB->count_records_sql($countfields . $sql, $params);
         if ($potentialmemberscount > $this->maxusersperpage) {
             return $this->too_many_results($search, $potentialmemberscount);
         }
     }
     $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams));
     if (empty($availableusers)) {
         return array();
     }
     if ($search) {
         $groupname = get_string('potusersmatching', 'core_role', $search);
     } else {
         $groupname = get_string('potusers', 'core_role');
     }
     return array($groupname => $availableusers);
 }

 /**
  * Find allowed or not allowed users of a service (depend of $this->displayallowedusers)
  * @global object $DB
  * @param <type> $search
  * @return array
  */
 public function find_users($search)
 {
     global $DB;
     //by default wherecondition retrieves all users except the deleted, not
     //confirmed and guest
     list($wherecondition, $params) = $this->search_sql($search, 'u');
     $params['serviceid'] = $this->serviceid;
     $fields = 'SELECT ' . $this->required_fields_sql('u');
     $countfields = 'SELECT COUNT(1)';
     if ($this->displayallowedusers) {
         ///the following SQL retrieve all users that are allowed to the serviceid
         $sql = " FROM {user} u, {external_services_users} esu\n                 WHERE {$wherecondition}\n                       AND u.deleted = 0\n                       AND esu.userid = u.id\n                       AND esu.externalserviceid = :serviceid";
     } else {
         ///the following SQL retrieve all users that are not allowed to the serviceid
         $sql = " FROM {user} u WHERE {$wherecondition} AND u.deleted = 0\n                 AND NOT EXISTS (SELECT esu.userid FROM {external_services_users} esu\n                                                  WHERE esu.externalserviceid = :serviceid\n                                                        AND esu.userid = u.id)";
     }
     list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext);
     $order = ' ORDER BY ' . $sort;
     if (!$this->is_validating()) {
         $potentialmemberscount = $DB->count_records_sql($countfields . $sql, $params);
         if ($potentialmemberscount > service_user_selector::MAX_USERS_PER_PAGE) {
             return $this->too_many_results($search, $potentialmemberscount);
         }
     }
     $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams));
     if (empty($availableusers)) {
         return array();
     }
     if ($search) {
         $groupname = $this->displayallowedusers ? get_string('serviceusersmatching', 'webservice', $search) : get_string('potusersmatching', 'webservice', $search);
     } else {
         $groupname = $this->displayallowedusers ? get_string('serviceusers', 'webservice') : get_string('potusers', 'webservice');
     }
     return array($groupname => $availableusers);
 }

 public function find_users($search)
 {
     global $DB;
     list($wherecondition, $params) = $this->search_sql($search, 'u');
     list($ctxcondition, $ctxparams) = $DB->get_in_or_equal($this->context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'ctx');
     $params = array_merge($params, $ctxparams);
     $params['roleid'] = $this->roleid;
     list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext);
     $params = array_merge($params, $sortparams);
     $sql = "SELECT ra.id AS raid," . $this->required_fields_sql('u') . ",ra.contextid,ra.component\n                  FROM {role_assignments} ra\n                  JOIN {user} u ON u.id = ra.userid\n                  JOIN {context} ctx ON ra.contextid = ctx.id\n                 WHERE {$wherecondition}\n                       AND ctx.id {$ctxcondition}\n                       AND ra.roleid = :roleid\n              ORDER BY ctx.depth DESC, ra.component, {$sort}";
     $contextusers = $DB->get_records_sql($sql, $params);
     // No users at all.
     if (empty($contextusers)) {
         return array();
     }
     // We have users. Out put them in groups by context depth.
     // To help the loop below, tack a dummy user on the end of the results
     // array, to trigger output of the last group.
     $dummyuser = new stdClass();
     $dummyuser->contextid = 0;
     $dummyuser->id = 0;
     $dummyuser->component = '';
     $contextusers[] = $dummyuser;
     $results = array();
     // The results array we are building up.
     $doneusers = array();
     // Ensures we only list each user at most once.
     $currentcontextid = $this->context->id;
     $currentgroup = array();
     foreach ($contextusers as $user) {
         if (isset($doneusers[$user->id])) {
             continue;
         }
         $doneusers[$user->id] = 1;
         if ($user->contextid != $currentcontextid) {
             // We have got to the end of the previous group. Add it to the results array.
             if ($currentcontextid == $this->context->id) {
                 $groupname = $this->this_con_group_name($search, count($currentgroup));
             } else {
                 $groupname = $this->parent_con_group_name($search, $currentcontextid);
             }
             $results[$groupname] = $currentgroup;
             // Get ready for the next group.
             $currentcontextid = $user->contextid;
             $currentgroup = array();
         }
         // Add this user to the group we are building up.
         unset($user->contextid);
         if ($currentcontextid != $this->context->id) {
             $user->disabled = true;
         }
         if ($user->component !== '') {
             // Bad luck, you can tweak only manual role assignments.
             $user->disabled = true;
         }
         unset($user->component);
         $currentgroup[$user->id] = $user;
     }
     return $results;
 }

 public function find_users($search)
 {
     global $CFG, $DB;
     list($wherecondition, $params) = $this->search_sql($search, '');
     $fields = 'SELECT ' . $this->required_fields_sql('');
     $countfields = 'SELECT COUNT(1)';
     $sql = " FROM {user}\n                WHERE {$wherecondition} AND mnethostid = :localmnet";
     // It could be dangerous to make remote users admins and also this could lead to other problems.
     $params['localmnet'] = $CFG->mnet_localhost_id;
     list($sort, $sortparams) = users_order_by_sql('', $search, $this->accesscontext);
     $order = ' ORDER BY ' . $sort;
     // Check to see if there are too many to show sensibly.
     if (!$this->is_validating()) {
         $potentialcount = $DB->count_records_sql($countfields . $sql, $params);
         if ($potentialcount > $this->maxusersperpage) {
             return $this->too_many_results($search, $potentialcount);
         }
     }
     $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams));
     if (empty($availableusers)) {
         return array();
     }
     if ($search) {
         $groupname = get_string('potusersmatching', 'core_role', $search);
     } else {
         $groupname = get_string('potusers', 'core_role');
     }
     return array($groupname => $availableusers);
 }

 /**
  * Finds all subscribed users
  *
  * @param string $search
  * @return array
  */
 public function find_users($search)
 {
     global $DB;
     list($wherecondition, $params) = $this->search_sql($search, 'u');
     $params['forumid'] = $this->forumid;
     // only active enrolled or everybody on the frontpage
     list($esql, $eparams) = get_enrolled_sql($this->context, '', $this->currentgroup, true);
     $fields = $this->required_fields_sql('u');
     list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext);
     $params = array_merge($params, $eparams, $sortparams);
     $subscribers = $DB->get_records_sql("SELECT {$fields}\n                                               FROM {user} u\n                                               JOIN ({$esql}) je ON je.id = u.id\n                                               JOIN {forum_subscriptions} s ON s.userid = u.id\n                                              WHERE {$wherecondition} AND s.forum = :forumid\n                                           ORDER BY {$sort}", $params);
     return array(get_string("existingsubscribers", 'forum') => $subscribers);
 }

 /**
  * Finds all subscribed users
  *
  * @param string $search
  * @return array
  */
 public function find_users($search)
 {
     global $DB;
     list($wherecondition, $params) = $this->search_sql($search, 'u');
     $params['twfid'] = $this->twfid;
     // only active enrolled or everybody on the frontpage
     list($esql, $eparams) = get_enrolled_sql($this->context, '', $this->currentgroup, true);
     $fields = $this->required_fields_sql('u');
     list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext);
     $params = array_merge($params, $eparams, $sortparams);
     $subscribers = $DB->get_records_sql("SELECT {$fields}\n                                               FROM {user} u\n                                               JOIN ({$esql}) je ON je.id = u.id\n                                               JOIN {twf_subscriptions} s ON s.userid = u.id\n                                              WHERE {$wherecondition} AND s.twf = :twfid\n                                           ORDER BY {$sort}", $params);
     $cm = get_coursemodule_from_instance('twf', $this->twfid);
     $modinfo = get_fast_modinfo($cm->course);
     $info = new \core_availability\info_module($modinfo->get_cm($cm->id));
     $subscribers = $info->filter_user_list($subscribers);
     return array(get_string("existingsubscribers", 'twf') => $subscribers);
 }

    public function test_users_order_by_sql_search_with_extra_fields_and_prefix() {
        global $CFG, $DB;
        $CFG->showuseridentity = 'email,idnumber';
        $this->setAdminUser();
        $this->resetAfterTest(true);

        list($sort, $params) = users_order_by_sql('u', 'search', context_system::instance());
        $this->assert_same_sql('CASE WHEN
                    ' . $DB->sql_fullname('u.firstname', 'u.lastname') . ' = :usersortexact1 OR
                    LOWER(u.firstname) = LOWER(:usersortexact2) OR
                    LOWER(u.lastname) = LOWER(:usersortexact3) OR
                    LOWER(u.email) = LOWER(:usersortexact4) OR
                    LOWER(u.idnumber) = LOWER(:usersortexact5)
                THEN 0 ELSE 1 END, u.lastname, u.firstname, u.id', $sort);
        $this->assertEquals(array('usersortexact1' => 'search', 'usersortexact2' => 'search',
                'usersortexact3' => 'search', 'usersortexact4' => 'search', 'usersortexact5' => 'search'), $params);
    }

 public function find_users($search)
 {
     global $DB, $CFG;
     list($wherecondition, $params) = $this->search_sql($search, '');
     $fields = 'SELECT ' . $this->required_fields_sql('');
     if ($wherecondition) {
         $wherecondition = "{$wherecondition} AND id IN ({$CFG->siteadmins})";
     } else {
         $wherecondition = "id IN ({$CFG->siteadmins})";
     }
     $sql = " FROM {user}\n                WHERE {$wherecondition}";
     list($sort, $sortparams) = users_order_by_sql('', $search, $this->accesscontext);
     $params = array_merge($params, $sortparams);
     $order = ' ORDER BY ' . $sort;
     $availableusers = $DB->get_records_sql($fields . $sql . $order, $params);
     if (empty($availableusers)) {
         return array();
     }
     $mainadmin = array();
     $mainadminuser = get_admin();
     if ($mainadminuser && isset($availableusers[$mainadminuser->id])) {
         $mainadmin = array($mainadminuser->id => $availableusers[$mainadminuser->id]);
         unset($availableusers[$mainadminuser->id]);
     }
     $result = array();
     if ($mainadmin) {
         $result[get_string('mainadmin', 'core_role')] = $mainadmin;
     }
     if ($availableusers) {
         if ($search) {
             $groupname = get_string('extusersmatching', 'core_role', $search);
         } else {
             $groupname = get_string('extusers', 'core_role');
         }
         $result[$groupname] = $availableusers;
     }
     return $result;
 }

 public function find_users($search)
 {
     global $DB;
     list($enrolsql, $eparams) = get_enrolled_sql($this->context);
     // Now we have to go to the database.
     list($wherecondition, $params) = $this->search_sql($search, 'u');
     $params = array_merge($params, $eparams);
     if ($wherecondition) {
         $wherecondition = ' AND ' . $wherecondition;
     }
     $fields = 'SELECT ' . $this->required_fields_sql('u');
     $countfields = 'SELECT COUNT(u.id)';
     $sql = " FROM ({$enrolsql}) enrolled_users_view\n                   JOIN {user} u ON u.id = enrolled_users_view.id\n              LEFT JOIN {role_assignments} ra ON (ra.userid = enrolled_users_view.id AND\n                                            ra.roleid = :roleid AND ra.contextid = :contextid)\n                  WHERE ra.id IS NULL\n                        {$wherecondition}";
     $params['contextid'] = $this->context->id;
     $params['roleid'] = $this->roleid;
     list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext);
     $order = ' ORDER BY ' . $sort;
     // Check to see if there are too many to show sensibly.
     if (!$this->is_validating()) {
         $potentialmemberscount = $DB->count_records_sql($countfields . $sql, $params);
         if ($potentialmemberscount > $this->maxusersperpage) {
             return $this->too_many_results($search, $potentialmemberscount);
         }
     }
     // If not, show them.
     $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams));
     if (empty($availableusers)) {
         return array();
     }
     if ($search) {
         $groupname = get_string('potusersmatching', 'core_role', $search);
     } else {
         $groupname = get_string('potusers', 'core_role');
     }
     return array($groupname => $availableusers);
 }

 /**
  * Set the marking allocation for multiple users
  *
  * @return void
  */
 protected function process_set_batch_marking_allocation()
 {
     global $CFG, $DB;
     // Include batch marking allocation form.
     require_once $CFG->dirroot . '/mod/assign/batchsetallocatedmarkerform.php';
     $formparams = array('userscount' => 0, 'usershtml' => '');
     list($sort, $params) = users_order_by_sql();
     $markers = get_users_by_capability($this->get_context(), 'mod/assign:grade', '', $sort);
     $markerlist = array();
     foreach ($markers as $marker) {
         $markerlist[$marker->id] = fullname($marker);
     }
     $formparams['markers'] = $markerlist;
     $mform = new mod_assign_batch_set_allocatedmarker_form(null, $formparams);
     if ($mform->is_cancelled()) {
         return true;
     }
     if ($formdata = $mform->get_data()) {
         $useridlist = explode(',', $formdata->selectedusers);
         $marker = $DB->get_record('user', array('id' => $formdata->allocatedmarker), '*', MUST_EXIST);
         foreach ($useridlist as $userid) {
             $flags = $this->get_user_flags($userid, true);
             if ($flags->workflowstate == ASSIGN_MARKING_WORKFLOW_STATE_READYFORREVIEW || $flags->workflowstate == ASSIGN_MARKING_WORKFLOW_STATE_INREVIEW || $flags->workflowstate == ASSIGN_MARKING_WORKFLOW_STATE_READYFORRELEASE || $flags->workflowstate == ASSIGN_MARKING_WORKFLOW_STATE_RELEASED) {
                 continue;
                 // Allocated marker can only be changed in certain workflow states.
             }
             $flags->allocatedmarker = $marker->id;
             if ($this->update_user_flags($flags)) {
                 $user = $DB->get_record('user', array('id' => $userid), '*', MUST_EXIST);
                 \mod_assign\event\marker_updated::create_from_marker($this, $user, $marker)->trigger();
             }
         }
     }
 }

 foreach ($pages as $key => $page) {
     if ($page->qtype != LESSON_PAGE_ESSAY) {
         unset($pages[$key]);
     }
 }
 if (count($pages) > 0) {
     // Get only the attempts that are in response to essay questions
     list($usql, $parameters) = $DB->get_in_or_equal(array_keys($pages), SQL_PARAMS_NAMED);
     // If group selected, only get group members attempts.
     list($esql, $params) = get_enrolled_sql($context, '', $currentgroup, true);
     $parameters = array_merge($params, $parameters);
     $sql = "SELECT a.*\n                        FROM {lesson_attempts} a\n                        JOIN ({$esql}) ue ON a.userid = ue.id\n                        WHERE pageid {$usql}";
     if ($essayattempts = $DB->get_records_sql($sql, $parameters)) {
         $ufields = user_picture::fields('u');
         // Get all the users who have taken this lesson.
         list($sort, $sortparams) = users_order_by_sql('u');
         $params['lessonid'] = $lesson->id;
         $sql = "SELECT DISTINCT {$ufields}\n                        FROM {user} u\n                        JOIN {lesson_attempts} a ON u.id = a.userid\n                        JOIN ({$esql}) ue ON ue.id = a.userid\n                        WHERE a.lessonid = :lessonid\n                        ORDER BY {$sort}";
         if (!($users = $DB->get_records_sql($sql, $params))) {
             $mode = 'none';
             // not displaying anything
             if (!empty($currentgroup)) {
                 $groupname = groups_get_group_name($currentgroup);
                 $lesson->add_message(get_string('noonehasansweredgroup', 'lesson', $groupname));
             } else {
                 $lesson->add_message(get_string('noonehasanswered', 'lesson'));
             }
         }
     } else {
         $mode = 'none';
         // not displaying anything

 /**
  * Prepares data object with all workshop grades to be rendered
  *
  * @param int $userid the user we are preparing the report for
  * @param int $groupid if non-zero, prepare the report for the given group only
  * @param int $page the current page (for the pagination)
  * @param int $perpage participants per page (for the pagination)
  * @param string $sortby lastname|firstname|submissiontitle|submissiongrade|gradinggrade
  * @param string $sorthow ASC|DESC
  * @return stdclass data for the renderer
  */
 public function prepare_grading_report_data($userid, $groupid, $page, $perpage, $sortby, $sorthow)
 {
     global $DB;
     $canviewall = has_capability('mod/workshop:viewallassessments', $this->context, $userid);
     $isparticipant = $this->is_participant($userid);
     if (!$canviewall and !$isparticipant) {
         // who the hell is this?
         return array();
     }
     if (!in_array($sortby, array('lastname', 'firstname', 'submissiontitle', 'submissiongrade', 'gradinggrade'))) {
         $sortby = 'lastname';
     }
     if (!($sorthow === 'ASC' or $sorthow === 'DESC')) {
         $sorthow = 'ASC';
     }
     // get the list of user ids to be displayed
     if ($canviewall) {
         $participants = $this->get_participants(false, $groupid);
     } else {
         // this is an ordinary workshop participant (aka student) - display the report just for him/her
         $participants = array($userid => (object) array('id' => $userid));
     }
     // we will need to know the number of all records later for the pagination purposes
     $numofparticipants = count($participants);
     if ($numofparticipants > 0) {
         // load all fields which can be used for sorting and paginate the records
         list($participantids, $params) = $DB->get_in_or_equal(array_keys($participants), SQL_PARAMS_NAMED);
         $params['workshopid1'] = $this->id;
         $params['workshopid2'] = $this->id;
         $sqlsort = array();
         $sqlsortfields = array($sortby => $sorthow) + array('lastname' => 'ASC', 'firstname' => 'ASC', 'u.id' => 'ASC');
         foreach ($sqlsortfields as $sqlsortfieldname => $sqlsortfieldhow) {
             $sqlsort[] = $sqlsortfieldname . ' ' . $sqlsortfieldhow;
         }
         $sqlsort = implode(',', $sqlsort);
         $picturefields = user_picture::fields('u', array(), 'userid');
         $sql = "SELECT {$picturefields}, s.title AS submissiontitle, s.grade AS submissiongrade, ag.gradinggrade\n                      FROM {user} u\n                 LEFT JOIN {workshop_submissions} s ON (s.authorid = u.id AND s.workshopid = :workshopid1 AND s.example = 0)\n                 LEFT JOIN {workshop_aggregations} ag ON (ag.userid = u.id AND ag.workshopid = :workshopid2)\n                     WHERE u.id {$participantids}\n                  ORDER BY {$sqlsort}";
         $participants = $DB->get_records_sql($sql, $params, $page * $perpage, $perpage);
     } else {
         $participants = array();
     }
     // this will hold the information needed to display user names and pictures
     $userinfo = array();
     // get the user details for all participants to display
     $additionalnames = get_all_user_name_fields();
     foreach ($participants as $participant) {
         if (!isset($userinfo[$participant->userid])) {
             $userinfo[$participant->userid] = new stdclass();
             $userinfo[$participant->userid]->id = $participant->userid;
             $userinfo[$participant->userid]->picture = $participant->picture;
             $userinfo[$participant->userid]->imagealt = $participant->imagealt;
             $userinfo[$participant->userid]->email = $participant->email;
             foreach ($additionalnames as $addname) {
                 $userinfo[$participant->userid]->{$addname} = $participant->{$addname};
             }
         }
     }
     // load the submissions details
     $submissions = $this->get_submissions(array_keys($participants));
     // get the user details for all moderators (teachers) that have overridden a submission grade
     foreach ($submissions as $submission) {
         if (!isset($userinfo[$submission->gradeoverby])) {
             $userinfo[$submission->gradeoverby] = new stdclass();
             $userinfo[$submission->gradeoverby]->id = $submission->gradeoverby;
             $userinfo[$submission->gradeoverby]->picture = $submission->overpicture;
             $userinfo[$submission->gradeoverby]->imagealt = $submission->overimagealt;
             $userinfo[$submission->gradeoverby]->email = $submission->overemail;
             foreach ($additionalnames as $addname) {
                 $temp = 'over' . $addname;
                 $userinfo[$submission->gradeoverby]->{$addname} = $submission->{$temp};
             }
         }
     }
     // get the user details for all reviewers of the displayed participants
     $reviewers = array();
     if ($submissions) {
         list($submissionids, $params) = $DB->get_in_or_equal(array_keys($submissions), SQL_PARAMS_NAMED);
         list($sort, $sortparams) = users_order_by_sql('r');
         $picturefields = user_picture::fields('r', array(), 'reviewerid');
         $sql = "SELECT a.id AS assessmentid, a.submissionid, a.grade, a.gradinggrade, a.gradinggradeover, a.weight,\n                           {$picturefields}, s.id AS submissionid, s.authorid\n                      FROM {workshop_assessments} a\n                      JOIN {user} r ON (a.reviewerid = r.id)\n                      JOIN {workshop_submissions} s ON (a.submissionid = s.id AND s.example = 0)\n                     WHERE a.submissionid {$submissionids}\n                  ORDER BY a.weight DESC, {$sort}";
         $reviewers = $DB->get_records_sql($sql, array_merge($params, $sortparams));
         foreach ($reviewers as $reviewer) {
             if (!isset($userinfo[$reviewer->reviewerid])) {
                 $userinfo[$reviewer->reviewerid] = new stdclass();
                 $userinfo[$reviewer->reviewerid]->id = $reviewer->reviewerid;
                 $userinfo[$reviewer->reviewerid]->picture = $reviewer->picture;
                 $userinfo[$reviewer->reviewerid]->imagealt = $reviewer->imagealt;
                 $userinfo[$reviewer->reviewerid]->email = $reviewer->email;
                 foreach ($additionalnames as $addname) {
//.........这里部分代码省略.........

 /**
  * Given list of DB records from table course populates each record with list of users with course contact roles
  *
  * This function fills the courses with raw information as {@link get_role_users()} would do.
  * See also {@link course_in_list::get_course_contacts()} for more readable return
  *
  * $courses[$i]->managers = array(
  *   $roleassignmentid => $roleuser,
  *   ...
  * );
  *
  * where $roleuser is an stdClass with the following properties:
  *
  * $roleuser->raid - role assignment id
  * $roleuser->id - user id
  * $roleuser->username
  * $roleuser->firstname
  * $roleuser->lastname
  * $roleuser->rolecoursealias
  * $roleuser->rolename
  * $roleuser->sortorder - role sortorder
  * $roleuser->roleid
  * $roleuser->roleshortname
  *
  * @todo MDL-38596 minimize number of queries to preload contacts for the list of courses
  *
  * @param array $courses
  */
 public static function preload_course_contacts(&$courses)
 {
     global $CFG, $DB;
     if (empty($courses) || empty($CFG->coursecontact)) {
         return;
     }
     $managerroles = explode(',', $CFG->coursecontact);
     $cache = cache::make('core', 'coursecontacts');
     $cacheddata = $cache->get_many(array_merge(array('basic'), array_keys($courses)));
     // Check if cache was set for the current course contacts and it is not yet expired.
     if (empty($cacheddata['basic']) || $cacheddata['basic']['roles'] !== $CFG->coursecontact || $cacheddata['basic']['lastreset'] < time() - self::CACHE_COURSE_CONTACTS_TTL) {
         // Reset cache.
         $cache->purge();
         $cache->set('basic', array('roles' => $CFG->coursecontact, 'lastreset' => time()));
         $cacheddata = $cache->get_many(array_merge(array('basic'), array_keys($courses)));
     }
     $courseids = array();
     foreach (array_keys($courses) as $id) {
         if ($cacheddata[$id] !== false) {
             $courses[$id]->managers = $cacheddata[$id];
         } else {
             $courseids[] = $id;
         }
     }
     // Array $courseids now stores list of ids of courses for which we still need to retrieve contacts.
     if (empty($courseids)) {
         return;
     }
     // First build the array of all context ids of the courses and their categories.
     $allcontexts = array();
     foreach ($courseids as $id) {
         $context = context_course::instance($id);
         $courses[$id]->managers = array();
         foreach (preg_split('|/|', $context->path, 0, PREG_SPLIT_NO_EMPTY) as $ctxid) {
             if (!isset($allcontexts[$ctxid])) {
                 $allcontexts[$ctxid] = array();
             }
             $allcontexts[$ctxid][] = $id;
         }
     }
     // Fetch list of all users with course contact roles in any of the courses contexts or parent contexts.
     list($sql1, $params1) = $DB->get_in_or_equal(array_keys($allcontexts), SQL_PARAMS_NAMED, 'ctxid');
     list($sql2, $params2) = $DB->get_in_or_equal($managerroles, SQL_PARAMS_NAMED, 'rid');
     list($sort, $sortparams) = users_order_by_sql('u');
     $notdeleted = array('notdeleted' => 0);
     $allnames = get_all_user_name_fields(true, 'u');
     $sql = "SELECT ra.contextid, ra.id AS raid,\n                       r.id AS roleid, r.name AS rolename, r.shortname AS roleshortname,\n                       rn.name AS rolecoursealias, u.id, u.username, {$allnames}\n                  FROM {role_assignments} ra\n                  JOIN {user} u ON ra.userid = u.id\n                  JOIN {role} r ON ra.roleid = r.id\n             LEFT JOIN {role_names} rn ON (rn.contextid = ra.contextid AND rn.roleid = r.id)\n                WHERE  ra.contextid " . $sql1 . " AND ra.roleid " . $sql2 . " AND u.deleted = :notdeleted\n             ORDER BY r.sortorder, {$sort}";
     $rs = $DB->get_recordset_sql($sql, $params1 + $params2 + $notdeleted + $sortparams);
     $checkenrolments = array();
     foreach ($rs as $ra) {
         foreach ($allcontexts[$ra->contextid] as $id) {
             $courses[$id]->managers[$ra->raid] = $ra;
             if (!isset($checkenrolments[$id])) {
                 $checkenrolments[$id] = array();
             }
             $checkenrolments[$id][] = $ra->id;
         }
     }
     $rs->close();
     // Remove from course contacts users who are not enrolled in the course.
     $enrolleduserids = self::ensure_users_enrolled($checkenrolments);
     foreach ($checkenrolments as $id => $userids) {
         if (empty($enrolleduserids[$id])) {
             $courses[$id]->managers = array();
         } else {
             if ($notenrolled = array_diff($userids, $enrolleduserids[$id])) {
                 foreach ($courses[$id]->managers as $raid => $ra) {
                     if (in_array($ra->id, $notenrolled)) {
                         unset($courses[$id]->managers[$raid]);
                     }
                 }
             }
//.........这里部分代码省略.........

 /**
  * Get the list of potential subscribers to a forum.
  *
  * @param context_module $context the forum context.
  * @param integer $groupid the id of a group, or 0 for all groups.
  * @param string $fields the list of fields to return for each user. As for get_users_by_capability.
  * @param string $sort sort order. As for get_users_by_capability.
  * @return array list of users.
  */
 public static function get_potential_subscribers($context, $groupid, $fields, $sort = '')
 {
     global $DB;
     // Only active enrolled users or everybody on the frontpage.
     list($esql, $params) = get_enrolled_sql($context, 'mod/forum:allowforcesubscribe', $groupid, true);
     if (!$sort) {
         list($sort, $sortparams) = users_order_by_sql('u');
         $params = array_merge($params, $sortparams);
     }
     $sql = "SELECT {$fields}\n                FROM {user} u\n                JOIN ({$esql}) je ON je.id = u.id\n            ORDER BY {$sort}";
     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);
    }

function referentiel_get_accompagnements_teacher($referentiel_instance_id, $course_id, $ref_teacher)
{
    // retourne la liste des id des accompagnes
    //
    global $DB;
    if (!empty($referentiel_instance_id) && !empty($course_id) && !empty($ref_teacher)) {
        $params = array("refid" => "{$referentiel_instance_id}", "courseid" => "{$course_id}", "teacherid" => "{$ref_teacher}");
        list($sort, $sortparams) = users_order_by_sql('u');
        $params += $sortparams;
        $sql = "SELECT ra.userid FROM {referentiel_accompagnement} AS ra, {user} AS u\n WHERE ra.ref_instance=:refid\n \tAND ra.courseid=:courseid AND u.id=ra.userid AND ra.teacherid=:teacherid\n ORDER BY {$sort} ";
        return $DB->get_records_sql($sql, $params);
    }
    return NULL;
}

 /**
  * Load all of the uses who have the capability into choice array
  *
  * @return bool Always returns true
  */
 function load_choices()
 {
     if (is_array($this->choices)) {
         return true;
     }
     list($sort, $sortparams) = users_order_by_sql('u');
     if (!empty($sortparams)) {
         throw new coding_exception('users_order_by_sql returned some query parameters. ' . 'This is unexpected, and a problem because there is no way to pass these ' . 'parameters to get_users_by_capability. See MDL-34657.');
     }
     $userfields = 'u.id, u.username, ' . get_all_user_name_fields(true, 'u');
     $users = get_users_by_capability(context_system::instance(), $this->capability, $userfields, $sort);
     $this->choices = array('$@NONE@$' => get_string('nobody'), '$@ALL@$' => get_string('everyonewhocan', 'admin', get_capability_string($this->capability)));
     if ($this->includeadmins) {
         $admins = get_admins();
         foreach ($admins as $user) {
             $this->choices[$user->id] = fullname($user);
         }
     }
     if (is_array($users)) {
         foreach ($users as $user) {
             $this->choices[$user->id] = fullname($user);
         }
     }
     return true;
 }

 /**
  * Find our users who could be enrolled into the remote course
  *
  * Our users must have 'moodle/site:mnetlogintoremote' capability assigned.
  * Remote users, guests, deleted and not confirmed users are not returned.
  *
  * @param string $search
  * @return array
  */
 public function find_users($search)
 {
     global $CFG, $DB;
     $systemcontext = context_system::instance();
     $userids = get_users_by_capability($systemcontext, 'moodle/site:mnetlogintoremote', 'u.id');
     if (empty($userids)) {
         return array();
     }
     list($usql, $uparams) = $DB->get_in_or_equal(array_keys($userids), SQL_PARAMS_NAMED, 'uid');
     list($wherecondition, $params) = $this->search_sql($search, 'u');
     $params = array_merge($params, $uparams);
     $params['hostid'] = $this->hostid;
     $params['remotecourseid'] = $this->remotecourseid;
     $params['mnetlocalhostid'] = $CFG->mnet_localhost_id;
     $fields = "SELECT " . $this->required_fields_sql("u");
     $countfields = "SELECT COUNT(1)";
     $sql = "          FROM {user} u\n                         WHERE {$wherecondition}\n                               AND u.mnethostid = :mnetlocalhostid\n                               AND u.id {$usql}\n                               AND u.id NOT IN (SELECT e.userid\n                                                  FROM {mnetservice_enrol_enrolments} e\n                                                 WHERE (e.hostid = :hostid AND e.remotecourseid = :remotecourseid))";
     list($sort, $sortparams) = users_order_by_sql('u');
     $order = "    ORDER BY {$sort}";
     if (!$this->is_validating()) {
         $potentialmemberscount = $DB->count_records_sql($countfields . $sql, $params);
         if ($potentialmemberscount > 100) {
             return $this->too_many_results($search, $potentialmemberscount);
         }
     }
     $availableusers = $DB->get_records_sql($fields . $sql . $order, array_merge($params, $sortparams));
     if (empty($availableusers)) {
         return array();
     }
     if ($search) {
         $groupname = get_string('enrolcandidatesmatching', 'enrol', $search);
     } else {
         $group