/**
  * 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;
 }

 /**
  * Get list of course participants.
  *
  * @param int $courseid
  * @param text $withcapability
  * @param int $groupid
  * @param bool $onlyactive
  * @return array of course participants
  */
 public static function get_enrolled_users($courseid, $withcapability = null, $groupid = null, $onlyactive = false)
 {
     global $DB, $CFG, $USER;
     // Do basic automatic PARAM checks on incoming data, using params description
     // If any problems are found then exceptions are thrown with helpful error messages
     $params = self::validate_parameters(self::get_enrolled_users_parameters(), array('courseid' => $courseid, 'withcapability' => $withcapability, 'groupid' => $groupid, 'onlyactive' => $onlyactive));
     $coursecontext = get_context_instance(CONTEXT_COURSE, $params['courseid']);
     if ($courseid == SITEID) {
         $context = get_context_instance(CONTEXT_SYSTEM);
     } else {
         $context = $coursecontext;
     }
     try {
         self::validate_context($context);
     } catch (Exception $e) {
         $exceptionparam = new stdClass();
         $exceptionparam->message = $e->getMessage();
         $exceptionparam->courseid = $params['courseid'];
         throw new moodle_exception(get_string('errorcoursecontextnotvalid', 'webservice', $exceptionparam));
     }
     if ($courseid == SITEID) {
         require_capability('moodle/site:viewparticipants', $context);
     } else {
         require_capability('moodle/course:viewparticipants', $context);
     }
     if ($withcapability) {
         require_capability('moodle/role:review', $coursecontext);
     }
     if ($groupid && groups_is_member($groupid)) {
         require_capability('moodle/site:accessallgroups', $coursecontext);
     }
     if ($onlyactive) {
         require_capability('moodle/course:enrolreview', $coursecontext);
     }
     list($sqlparams, $params) = get_enrolled_sql($coursecontext, $withcapability, $groupid, $onlyactive);
     $sql = "SELECT ue.userid, e.courseid, u.firstname, u.lastname, u.username, c.id as usercontextid\n                  FROM {user_enrolments} ue\n                  JOIN {enrol} e ON (e.id = ue.enrolid)\n                  JOIN {user} u ON (ue.userid = u.id)\n                  JOIN {context} c ON (u.id = c.instanceid AND contextlevel = " . CONTEXT_USER . ")\n                  WHERE e.courseid = :courseid AND ue.userid IN ({$sqlparams})\n                  GROUP BY ue.userid, e.courseid, u.firstname, u.lastname, u.username, c.id";
     $params['courseid'] = $courseid;
     $enrolledusers = $DB->get_records_sql($sql, $params);
     $result = array();
     $isadmin = is_siteadmin($USER);
     $canviewfullnames = has_capability('moodle/site:viewfullnames', $context);
     foreach ($enrolledusers as $enrolleduser) {
         $profilimgurl = moodle_url::make_pluginfile_url($enrolleduser->usercontextid, 'user', 'icon', NULL, '/', 'f1');
         $profilimgurlsmall = moodle_url::make_pluginfile_url($enrolleduser->usercontextid, 'user', 'icon', NULL, '/', 'f2');
         $resultuser = array('courseid' => $enrolleduser->courseid, 'userid' => $enrolleduser->userid, 'fullname' => fullname($enrolleduser), 'profileimgurl' => $profilimgurl->out(false), 'profileimgurlsmall' => $profilimgurlsmall->out(false));
         // check if we can return username
         if ($isadmin) {
             $resultuser['username'] = $enrolleduser->username;
         }
         // check if we can return first and last name
         if ($isadmin or $canviewfullnames) {
             $resultuser['firstname'] = $enrolleduser->firstname;
             $resultuser['lastname'] = $enrolleduser->lastname;
         }
         $result[] = $resultuser;
     }
     return $result;
 }

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

        $mailmaxusers = (isset($CFG->maxusersperpage) ? $CFG->maxusersperpage : $this->maxusersperpage);

        $context = context_course::instance($this->courseid);

        $mailsamerole = has_capability('local/mail:mailsamerole', $context);
        $userroleids = local_mail_get_user_roleids($USER->id, $context);

        list($wherecondition, $params) = $this->search_sql($search, 'u');
        list($enrolledsql, $enrolledparams) = get_enrolled_sql($context, '', $this->groupid, true);
        list($parentsql, $parentparams) = $DB->get_in_or_equal($context->get_parent_context_ids(true), SQL_PARAMS_NAMED);

        $params = array_merge($params, $enrolledparams, $parentparams);
        $params['courseid'] = $this->courseid;

        if (!$mailsamerole) {
            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);
            $wherecondition .= " AND u.id IN (SELECT userid FROM {role_assignments} WHERE roleid $samerolesql AND contextid $relctxsql)";
            $params = array_merge($params, $sameroleparams, $reldctxparams);
        }

        $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 '
            . $parentsql . ')'
            . ' 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 > $mailmaxusers) {
                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);
    }

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

 /**
  * Get list of course participants.
  *
  * @param int $courseid
  * @param text $withcapability
  * @param int $groupid
  * @param bool $onlyactive
  * @return array of course participants
  */
 public static function get_enrolled_users($courseid, $withcapability, $groupid, $onlyactive)
 {
     global $DB;
     // Do basic automatic PARAM checks on incoming data, using params description
     // If any problems are found then exceptions are thrown with helpful error messages
     $params = self::validate_parameters(self::get_enrolled_users_parameters(), array('courseid' => $courseid, 'withcapability' => $withcapability, 'groupid' => $groupid, 'onlyactive' => $onlyactive));
     $coursecontext = get_context_instance(CONTEXT_COURSE, $params['courseid']);
     if ($courseid == SITEID) {
         $context = get_context_instance(CONTEXT_SYSTEM);
     } else {
         $context = $coursecontext;
     }
     try {
         self::validate_context($context);
     } catch (Exception $e) {
         $exceptionparam = new stdClass();
         $exceptionparam->message = $e->getMessage();
         $exceptionparam->courseid = $params['courseid'];
         throw new moodle_exception(get_string('errorcoursecontextnotvalid', 'webservice', $exceptionparam));
     }
     if ($courseid == SITEID) {
         require_capability('moodle/site:viewparticipants', $context);
     } else {
         require_capability('moodle/course:viewparticipants', $context);
     }
     if ($withcapability) {
         require_capability('moodle/role:review', $coursecontext);
     }
     if ($groupid) {
         if (groups_is_member($groupid)) {
             require_capability('moodle/site:accessallgroups', $coursecontext);
         }
     }
     if ($onlyactive) {
         require_capability('moodle/course:enrolreview', $coursecontext);
     }
     list($sql, $params) = get_enrolled_sql($coursecontext, $withcapability, $groupid, $onlyactive);
     $sql = "SELECT DISTINCT ue.userid, e.courseid\n                  FROM {user_enrolments} ue\n                  JOIN {enrol} e ON (e.id = ue.enrolid)\n                 WHERE e.courseid = :courseid AND ue.userid IN ({$sql})";
     $params['courseid'] = $courseid;
     $enrolledusers = $DB->get_records_sql($sql, $params);
     $result = array();
     foreach ($enrolledusers as $enrolleduser) {
         $result[] = array('courseid' => $enrolleduser->courseid, 'userid' => $enrolleduser->userid);
     }
     return $result;
 }

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

 /**
  * Handles searching for user in a particular course in the message area.
  *
  * @param int $userid The user id doing the searching
  * @param int $courseid The id of the course we are searching in
  * @param string $search The string the user is searching
  * @param int $limitfrom
  * @param int $limitnum
  * @return array
  */
 public static function search_users_in_course($userid, $courseid, $search, $limitfrom = 0, $limitnum = 0)
 {
     global $DB;
     // Get all the users in the course.
     list($esql, $params) = get_enrolled_sql(\context_course::instance($courseid), '', 0, true);
     $sql = "SELECT u.*, mc.blocked\n                  FROM {user} u\n                  JOIN ({$esql}) je\n                    ON je.id = u.id\n             LEFT JOIN {message_contacts} mc\n                    ON (mc.contactid = u.id AND mc.userid = :userid)\n                 WHERE u.deleted = 0";
     // Add more conditions.
     $fullname = $DB->sql_fullname();
     $sql .= " AND u.id != :userid2\n                  AND " . $DB->sql_like($fullname, ':search', false) . "\n             ORDER BY " . $DB->sql_fullname();
     $params = array_merge(array('userid' => $userid, 'userid2' => $userid, 'search' => '%' . $search . '%'), $params);
     // Convert all the user records into contacts.
     $contacts = array();
     if ($users = $DB->get_records_sql($sql, $params, $limitfrom, $limitnum)) {
         foreach ($users as $user) {
             $contacts[] = helper::create_contact($user);
         }
     }
     return $contacts;
 }

function getUserList($currentgroup = '', $courseId, $contextlevel, $context, $limitUsers = 15)
{
    global $USER, $CFG, $DB, $OUTPUT;
    $groupmembers = "";
    $groupselect = "";
    $params = array();
    //Add this to the SQL to show only group users
    if ($currentgroup !== NULL) {
        $groupmembers = ", {groups_members} gm";
        $groupselect = "AND u.id = gm.userid AND gm.groupid = :currentgroup";
        $params['currentgroup'] = $currentgroup;
    }
    $userfields = user_picture::fields('u', array('username'));
    if ($courseId == SITEID or $contextlevel < CONTEXT_COURSE) {
        // Site-level
        //Only show if is admin
        if (!checkIfUserIsAdmin()) {
            return '';
        }
        $sql = "SELECT {$userfields}, ul.lastip, MAX(ul.timemodified) AS lastaccess\n                      FROM {user} u {$groupmembers} ,{sessions} ul\n                     WHERE u.id = ul.userid AND u.deleted = 0\n                     {$groupselect}\n                  GROUP BY {$userfields}\n                  ORDER BY ul.timemodified DESC ";
    } else {
        // Course level - show only enrolled users for now
        //Only show if is teacher or admin
        if (!checkIfUserIsTeacher($courseId)) {
            return '';
        }
        list($esqljoin, $eparams) = get_enrolled_sql($context);
        $params = array_merge($params, $eparams);
        $sql = "SELECT {$userfields}, ul.lastip, MAX(ul.timemodified) AS lastaccess\n                      FROM {sessions} ul {$groupmembers}, {user} u\n                      JOIN ({$esqljoin}) euj ON euj.id = u.id\n                     WHERE u.id = ul.userid\n                           AND u.deleted = 0\n                           {$groupselect}\n                  GROUP BY {$userfields}\n                  ORDER BY lastaccess DESC";
        $params['courseid'] = $courseId;
    }
    if ($users = $DB->get_records_sql($sql, $params, 0, $limitUsers)) {
        // We'll just take the most recent 50 maximum
        foreach ($users as $user) {
            $users[$user->id]->fullname = fullname($user);
        }
    } else {
        $users = array();
    }
    return $users;
}

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

function forumngfeature_usage_show_mostreaders($params, $forum = null)
{
    global $DB, $PAGE;
    $cloneid = empty($params['clone']) ? 0 : $params['clone'];
    if ($forum == null) {
        if (empty($params['id'])) {
            throw new moodle_exception('Missing forum id param');
        }
        $forum = mod_forumng::get_from_cmid($params['id'], $cloneid);
    }
    $groupwhere = '';
    $groupparams = array();
    $groupid = 0;
    if (!empty($params['group']) && $params['group'] != mod_forumng::NO_GROUPS && $params['group'] != mod_forumng::ALL_GROUPS) {
        $groupwhere = 'AND (fd.groupid = :groupid OR fd.groupid IS NULL)';
        $groupid = $params['group'];
        $groupparams = array('groupid' => $groupid);
    }
    if (has_capability('mod/forumng:viewreadinfo', $forum->get_context())) {
        if (!$PAGE->has_set_url()) {
            // Set context when called via ajax.
            $PAGE->set_context($forum->get_context());
        }
        $renderer = $PAGE->get_renderer('forumngfeature_usage');
        // Only get enrolled users - speeds up query significantly on large forums.
        list($sql, $params) = get_enrolled_sql($forum->get_context(), '', $groupid, true);
        // View discussions read.
        $readers = $DB->get_recordset_sql("\n                SELECT COUNT(fr.userid) AS count, fr.discussionid\n                  FROM {forumng_discussions} fd\n            RIGHT JOIN (\n\t\t                SELECT discussionid, userid\n\t\t                  FROM (\n\t\t                        SELECT * FROM {forumng_read}\n\t\t                     UNION ALL\n\t\t                        SELECT frp.id, frp.userid, fp.discussionid, frp.time\n                                  FROM {forumng_posts} fp\n                            RIGHT JOIN {forumng_read_posts} frp ON fp.id = frp.postid\n                                 WHERE fp.deleted = 0 AND fp.oldversion = 0\n                        ) frp GROUP BY discussionid, userid\n                ) fr ON fr.discussionid = fd.id\n                 WHERE fd.forumngid = :courseid\n                   AND fd.deleted = 0\n                {$groupwhere}\n                   AND fr.userid IN({$sql})\n              GROUP BY fr.discussionid\n              ORDER BY count desc, fr.discussionid desc", array_merge(array('courseid' => $forum->get_id()), $groupparams, $params), 0, 5);
        $readerlist = array();
        foreach ($readers as $discuss) {
            $discussion = mod_forumng_discussion::get_from_id($discuss->discussionid, $cloneid);
            list($content, $user) = $renderer->render_usage_discussion_info($forum, $discussion);
            $readerlist[] = $renderer->render_usage_list_item($forum, $discuss->count, $user, $content);
        }
        return $renderer->render_usage_list($readerlist, 'mostreaders', false);
    }
}

 /**
  * Returns unread messages count and courses
  * @return array
  */
 public static function count_unread_and_courses_messages($userid = '0')
 {
     global $DB, $USER;
     //Parameter validation
     //REQUIRED
     $params = self::validate_parameters(self::count_unread_messages_parameters(), array('userid' => $userid));
     //Context validation
     //OPTIONAL but in most web service it should present
     $context = get_context_instance(CONTEXT_USER, $USER->id);
     self::validate_context($context);
     //Capability checking
     //OPTIONAL but in most web service it should present
     if (!has_capability('moodle/user:viewdetails', $context)) {
         throw new moodle_exception('cannotviewprofile');
     }
     $messagecount = $DB->count_records_select('message', 'useridto=' . (int) $userid);
     $courses = enrol_get_users_courses($params['userid'], true, 'id, shortname, fullname, idnumber, visible');
     $result = array('messagecount' => $messagecount, 'courses' => array());
     foreach ($courses as $course) {
         $context = context_course::instance($course->id, IGNORE_MISSING);
         try {
             self::validate_context($context);
         } catch (Exception $e) {
             // current user can not access this course, sorry we can not disclose who is enrolled in this course!
             continue;
         }
         if ($userid != $USER->id and !has_capability('moodle/course:viewparticipants', $context)) {
             // we need capability to view participants
             continue;
         }
         list($enrolledsqlselect, $enrolledparams) = get_enrolled_sql($context);
         $enrolledsql = "SELECT COUNT('x') FROM ({$enrolledsqlselect}) enrolleduserids";
         $enrolledusercount = $DB->count_records_sql($enrolledsql, $enrolledparams);
         $result['courses'][] = array('id' => $course->id, 'shortname' => $course->shortname, 'fullname' => $course->fullname, 'idnumber' => $course->idnumber, 'visible' => $course->visible, 'enrolledusercount' => $enrolledusercount);
     }
     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);
 }

    /**
     * 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);
        $params = array_merge($params, $eparams);

        $fields = $this->required_fields_sql('u');

        $subscribers = $DB->get_records_sql("SELECT $fields
                                               FROM {user} u
                                               JOIN ($esql) je ON je.id = u.id
                                               JOIN {forum_subscriptions} s ON s.userid = u.id
                                              WHERE $wherecondition AND s.forum = :forumid
                                           ORDER BY u.lastname ASC, u.firstname ASC", $params);

        return array(get_string("existingsubscribers", 'forum') => $subscribers);
    }

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

/**
 * This function returns an array of grades that were included in the import,
 * but where the user does not currently have a graded role on the course. These grades
 * 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;
    $coursecontext = context_course::instance($courseid);
    // We want to query both the current context and parent contexts.
    list($relatedctxsql, $relatedctxparams) = $DB->get_in_or_equal($coursecontext->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relatedctx');
    // Users with a gradeable role.
    list($gradebookrolessql, $gradebookrolesparams) = $DB->get_in_or_equal(explode(',', $CFG->gradebookroles), SQL_PARAMS_NAMED, 'grbr');
    // Enrolled users.
    $context = context_course::instance($courseid);
    list($enrolledsql, $enrolledparams) = get_enrolled_sql($context);
    list($sort, $sortparams) = users_order_by_sql('u');
    $sql = "SELECT giv.id, u.firstname, u.lastname, u.idnumber AS useridnumber,\n                   COALESCE(gi.idnumber, gin.itemname) AS gradeidnumber\n              FROM {grade_import_values} giv\n              JOIN {user} u\n                   ON giv.userid = u.id\n              LEFT JOIN {grade_items} gi\n                        ON gi.id = giv.itemid\n              LEFT JOIN {grade_import_newitem} gin\n                        ON gin.id = giv.newgradeitem\n              LEFT JOIN ({$enrolledsql}) je\n                        ON je.id = u.id\n              LEFT JOIN {role_assignments} ra\n                        ON (giv.userid = ra.userid AND ra.roleid {$gradebookrolessql} AND ra.contextid {$relatedctxsql})\n             WHERE giv.importcode = :importcode\n                   AND (ra.id IS NULL OR je.id IS NULL)\n          ORDER BY gradeidnumber, {$sort}";
    $params = array_merge($gradebookrolesparams, $enrolledparams, $sortparams, $relatedctxparams);
    $params['importcode'] = $importcode;
    return $DB->get_records_sql($sql, $params);
}

 /**
  * Load a count of submissions with a specified status.
  *
  * @param string $status The submission status - should match one of the constants
  * @return int number of matching submissions
  */
 public function count_submissions_with_status($status)
 {
     global $DB;
     $currentgroup = groups_get_activity_group($this->get_course_module(), true);
     list($esql, $params) = get_enrolled_sql($this->get_context(), 'mod/assign:submit', $currentgroup, true);
     $params['assignid'] = $this->get_instance()->id;
     $params['assignid2'] = $this->get_instance()->id;
     $params['submissionstatus'] = $status;
     if ($this->get_instance()->teamsubmission) {
         $groupsstr = '';
         if ($currentgroup != 0) {
             // If there is an active group we should only display the current group users groups.
             $participants = $this->list_participants($currentgroup, true);
             $groups = groups_get_all_groups($this->get_course()->id, array_keys($participants), $this->get_instance()->teamsubmissiongroupingid, 'DISTINCT g.id, g.name');
             list($groupssql, $groupsparams) = $DB->get_in_or_equal(array_keys($groups), SQL_PARAMS_NAMED);
             $groupsstr = 's.groupid ' . $groupssql . ' AND';
             $params = $params + $groupsparams;
         }
         $sql = 'SELECT COUNT(s.groupid)
                     FROM {assign_submission} s
                     WHERE
                         s.latest = 1 AND
                         s.assignment = :assignid AND
                         s.timemodified IS NOT NULL AND
                         s.userid = :groupuserid AND ' . $groupsstr . '
                         s.status = :submissionstatus';
         $params['groupuserid'] = 0;
     } else {
         $sql = 'SELECT COUNT(s.userid)
                     FROM {assign_submission} s
                     JOIN(' . $esql . ') e ON e.id = s.userid
                     WHERE
                         s.latest = 1 AND
                         s.assignment = :assignid AND
                         s.timemodified IS NOT NULL AND
                         s.status = :submissionstatus';
     }
     return $DB->count_records_sql($sql, $params);
 }

 /**
  * Returns SQL to fetch all enrolled users with the given capability in the current workshop
  *
  * The returned array consists of string $sql and the $params array. Note that the $sql can be
  * empty if a grouping is selected and it has no groups.
  *
  * The list is automatically restricted according to any availability restrictions
  * that apply to user lists (e.g. group, grouping restrictions).
  *
  * @param string $capability the name of the capability
  * @param bool $musthavesubmission ff true, return only users who have already submitted
  * @param int $groupid 0 means ignore groups, any other value limits the result by group id
  * @return array of (string)sql, (array)params
  */
 protected function get_users_with_capability_sql($capability, $musthavesubmission, $groupid)
 {
     global $CFG;
     /** @var int static counter used to generate unique parameter holders */
     static $inc = 0;
     $inc++;
     // If the caller requests all groups and we are using a selected grouping,
     // recursively call this function for each group in the grouping (this is
     // needed because get_enrolled_sql only supports a single group).
     if (empty($groupid) and $this->cm->groupingid) {
         $groupingid = $this->cm->groupingid;
         $groupinggroupids = array_keys(groups_get_all_groups($this->cm->course, 0, $this->cm->groupingid, 'g.id'));
         $sql = array();
         $params = array();
         foreach ($groupinggroupids as $groupinggroupid) {
             if ($groupinggroupid > 0) {
                 // just in case in order not to fall into the endless loop
                 list($gsql, $gparams) = $this->get_users_with_capability_sql($capability, $musthavesubmission, $groupinggroupid);
                 $sql[] = $gsql;
                 $params = array_merge($params, $gparams);
             }
         }
         $sql = implode(PHP_EOL . " UNION " . PHP_EOL, $sql);
         return array($sql, $params);
     }
     list($esql, $params) = get_enrolled_sql($this->context, $capability, $groupid, true);
     $userfields = user_picture::fields('u');
     $sql = "SELECT {$userfields}\n                  FROM {user} u\n                  JOIN ({$esql}) je ON (je.id = u.id AND u.deleted = 0) ";
     if ($musthavesubmission) {
         $sql .= " JOIN {workshop_submissions} ws ON (ws.authorid = u.id AND ws.example = 0 AND ws.workshopid = :workshopid{$inc}) ";
         $params['workshopid' . $inc] = $this->id;
     }
     // If the activity is restricted so that only certain users should appear
     // in user lists, integrate this into the same SQL.
     $info = new \core_availability\info_module($this->cm);
     list($listsql, $listparams) = $info->get_user_list_sql(false);
     if ($listsql) {
         $sql .= " JOIN ({$listsql}) restricted ON restricted.id = u.id ";
         $params = array_merge($params, $listparams);
     }
     return array($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