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); }
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); }
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(); } // We want to query both the current context and parent contexts. list($relatedctxsql, $relatedctxparams) = $DB->get_in_or_equal($context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relatedctx'); // 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,\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 {$relatedctxsql} AND ra.roleid {$roleids})\n LEFT JOIN {role} r ON r.id = ra.roleid\n LEFT JOIN {groups_members} gm ON (gm.userid = u.id AND gm.groupid = :groupid)\n WHERE u.deleted = 0\n AND gm.id IS NULL\n AND {$searchcondition}"; list($sort, $sortparams) = users_order_by_sql('u', $search, $this->accesscontext); $orderby = ' ORDER BY ' . $sort; $params = array_merge($searchparams, $roleparams, $enrolparams, $relatedctxparams); $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 > $this->maxusersperpage) { 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); }
public function find_users($search) { // Get list of allowed roles. $context = get_context_instance(CONTEXT_COURSE, $this->courseid); if (!($validroleids = groups_get_possible_roles($context))) { return array(); } $roleids = 'IN (' . implode(',', $validroleids) . ')'; // Get the search condition. $searchcondition = $this->search_sql($search, 'u'); //TODO penny check the use of groupid here. // Build the SQL $fields = "SELECT r.id AS roleid, r.shortname AS roleshortname, r.name AS rolename, u.id AS userid, " . $this->required_fields_sql('u') . ", (SELECT count(igm.groupid) FROM {groups_members} igm JOIN {groups} ig ON\n igm.groupid = ig.id WHERE igm.userid = u.id AND ig.courseid = {$this->courseid}\n ) AS numgroups\n "; $sql = "\n FROM {user} u\n JOIN {role_assignments} ra ON ra.userid = u.id\n JOIN {role} r ON r.id = ra.roleid\n WHERE ra.contextid " . get_related_contexts_string($context) . "\n AND u.deleted = 0\n AND ra.roleid {$roleids}\n AND u.id NOT IN (SELECT userid\n FROM {groups_members}\n WHERE groupid = {$this->groupid})\n AND {$searchcondition}"; $orderby = " ORDER BY u.lastname, u.firstname"; if (!$this->is_validating()) { $potentialmemberscount = count_records_sql('SELECT count(DISTINCT u.id) ' . $sql); if ($potentialmemberscount > group_non_members_selector::MAX_USERS_PER_PAGE) { return $this->too_many_results($search, $potentialmemberscount); } } $rs = get_recordset_sql($fields . $sql . $orderby); $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))) { return array(); } list($roleids, $roleparams) = $DB->get_in_or_equal($validroleids); // Get the search condition. list($searchcondition, $searchparams) = $this->search_sql($search, 'u'); // Build the SQL $fields = "SELECT r.id AS roleid, r.shortname AS roleshortname, r.name AS rolename, 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 = ?) AS numgroups '; $sql = " FROM {user} u\n JOIN {role_assignments} ra ON ra.userid = u.id\n JOIN {role} r ON r.id = ra.roleid\n WHERE ra.contextid " . get_related_contexts_string($context) . "\n AND u.deleted = 0\n AND ra.roleid {$roleids}\n AND u.id NOT IN (SELECT userid\n FROM {groups_members}\n WHERE groupid = ?)\n AND {$searchcondition}"; $orderby = " ORDER BY u.lastname, u.firstname"; $params = array_merge($roleparams, array($this->groupid), $searchparams); 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); } } array_unshift($params, $this->courseid); $rs = $DB->get_recordset_sql($fields . $sql . $orderby, $params); $roles = groups_calculate_role_people($rs, $context); return $this->convert_array_format($roles, $search); }
/** * Gets the users for a course who are not in a specified group, and returns * them in an array organised by role. For the array format, see * groups_get_members_by_role. * @param int $groupid The id of the group * @param string searchtext similar to searchtext in role assign, search * @return array An array of role id or '*' => information about that role * including a list of users */ function groups_get_users_not_in_group_by_role($courseid, $groupid, $searchtext = '', $sort = 'u.lastname ASC') { global $CFG; $context = get_context_instance(CONTEXT_COURSE, $courseid); if ($searchtext !== '') { // Search for a subset of remaining users $LIKE = sql_ilike(); $FULLNAME = sql_fullname(); $wheresearch = " AND u.id IN (SELECT id FROM {$CFG->prefix}user WHERE {$FULLNAME} {$LIKE} '%{$searchtext}%' OR email {$LIKE} '%{$searchtext}%' )"; } else { $wheresearch = ''; } /// Get list of allowed roles if (!($validroleids = groups_get_possible_roles($context))) { return; } $roleids = '(' . implode(',', $validroleids) . ')'; /// Construct the main SQL $select = " SELECT r.id AS roleid,r.shortname AS roleshortname,r.name AS rolename,\n u.id AS userid, u.firstname, u.lastname"; $from = " FROM {$CFG->prefix}user u\n INNER JOIN {$CFG->prefix}role_assignments ra ON ra.userid = u.id\n INNER JOIN {$CFG->prefix}role r ON r.id = ra.roleid"; $where = " WHERE ra.contextid " . get_related_contexts_string($context) . "\n AND u.deleted = 0\n AND ra.roleid in {$roleids}\n AND u.id NOT IN (SELECT userid\n FROM {$CFG->prefix}groups_members\n WHERE groupid = {$groupid})\n {$wheresearch}"; $orderby = " ORDER BY {$sort}"; return groups_calculate_role_people(get_recordset_sql($select . $from . $where . $orderby), $context); }