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);
}
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);
}
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);
}
/**
* Lists users in a group based on their role on the course.
* Returns false if there's an error or there are no users in the group.
* Otherwise returns an array of role ID => role data, where role data includes:
* (role) $id, $shortname, $name
* $users: array of objects for each user which include the specified fields
* Users who do not have a role are stored in the returned array with key '-'
* and pseudo-role details (including a name, 'No role'). Users with multiple
* roles, same deal with key '*' and name 'Multiple roles'. You can find out
* which roles each has by looking in the $roles array of the user object.
* @param int $groupid
* @param int $courseid Course ID (should match the group's course)
* @param string $fields List of fields from user table prefixed with u, default 'u.*'
* @param string $sort SQL ORDER BY clause, default 'u.lastname ASC'
* @param string $extrawheretest extra SQL conditions ANDed with the existing where clause.
* @return array Complex array as described above
*/
function groups_get_members_by_role($groupid, $courseid, $fields = 'u.*', $sort = 'u.lastname ASC', $extrawheretest = '')
{
global $CFG;
// Retrieve information about all users and their roles on the course or
// parent ('related') contexts
$context = get_context_instance(CONTEXT_COURSE, $courseid);
if ($extrawheretest) {
$extrawheretest = ' AND ' . $extrawheretest;
}
$rs = get_recordset_sql($crap = "SELECT r.id AS roleid,r.shortname AS roleshortname,r.name AS rolename,\n u.id AS userid,{$fields}\n FROM {$CFG->prefix}groups_members gm\n INNER JOIN {$CFG->prefix}user u ON u.id = gm.userid\n INNER JOIN {$CFG->prefix}role_assignments ra \n ON ra.userid = u.id \n INNER JOIN {$CFG->prefix}role r ON r.id = ra.roleid\n WHERE gm.groupid='{$groupid}'\n AND ra.contextid " . get_related_contexts_string($context) . $extrawheretest . "\n ORDER BY r.sortorder,{$sort}");
return groups_calculate_role_people($rs, $context);
}
/**
* Lists users in a group based on their role on the course.
* Returns false if there's an error or there are no users in the group.
* Otherwise returns an array of role ID => role data, where role data includes:
* (role) $id, $shortname, $name
* $users: array of objects for each user which include the specified fields
* Users who do not have a role are stored in the returned array with key '-'
* and pseudo-role details (including a name, 'No role'). Users with multiple
* roles, same deal with key '*' and name 'Multiple roles'. You can find out
* which roles each has by looking in the $roles array of the user object.
*
* @param int $groupid
* @param int $courseid Course ID (should match the group's course)
* @param string $fields List of fields from user table prefixed with u, default 'u.*'
* @param string $sort SQL ORDER BY clause, default (when null passed) is what comes from users_order_by_sql.
* @param string $extrawheretest extra SQL conditions ANDed with the existing where clause.
* @param array $whereorsortparams any parameters required by $extrawheretest (named parameters).
* @return array Complex array as described above
*/
function groups_get_members_by_role($groupid, $courseid, $fields = 'u.*', $sort = null, $extrawheretest = '', $whereorsortparams = array())
{
global $CFG, $DB;
// Retrieve information about all users and their roles on the course or
// parent ('related') contexts
$context = context_course::instance($courseid);
if ($extrawheretest) {
$extrawheretest = ' AND ' . $extrawheretest;
}
if (is_null($sort)) {
list($sort, $sortparams) = users_order_by_sql('u');
$whereorsortparams = array_merge($whereorsortparams, $sortparams);
}
$sql = "SELECT r.id AS roleid, u.id AS userid, {$fields}\n FROM {groups_members} gm\n JOIN {user} u ON u.id = gm.userid\n LEFT JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.contextid " . get_related_contexts_string($context) . ")\n LEFT JOIN {role} r ON r.id = ra.roleid\n WHERE gm.groupid=:mgroupid\n " . $extrawheretest . "\n ORDER BY r.sortorder, {$sort}";
$whereorsortparams['mgroupid'] = $groupid;
$rs = $DB->get_recordset_sql($sql, $whereorsortparams);
return groups_calculate_role_people($rs, $context);
}
/**
* Lists users in a group based on their role on the course.
* Returns false if there's an error or there are no users in the group.
* Otherwise returns an array of role ID => role data, where role data includes:
* (role) $id, $shortname, $name
* $users: array of objects for each user which include the specified fields
* Users who do not have a role are stored in the returned array with key '-'
* and pseudo-role details (including a name, 'No role'). Users with multiple
* roles, same deal with key '*' and name 'Multiple roles'. You can find out
* which roles each has by looking in the $roles array of the user object.
*
* @param int $groupid
* @param int $courseid Course ID (should match the group's course)
* @param string $fields List of fields from user table prefixed with u, default 'u.*'
* @param string $sort SQL ORDER BY clause, default (when null passed) is what comes from users_order_by_sql.
* @param string $extrawheretest extra SQL conditions ANDed with the existing where clause.
* @param array $whereorsortparams any parameters required by $extrawheretest (named parameters).
* @return array Complex array as described above
*/
function groups_get_members_by_role($groupid, $courseid, $fields = 'u.*', $sort = null, $extrawheretest = '', $whereorsortparams = array())
{
global $DB;
// Retrieve information about all users and their roles on the course or
// parent ('related') contexts
$context = context_course::instance($courseid);
// We are looking for all users with this role assigned in this context or higher.
list($relatedctxsql, $relatedctxparams) = $DB->get_in_or_equal($context->get_parent_context_ids(true), SQL_PARAMS_NAMED, 'relatedctx');
if ($extrawheretest) {
$extrawheretest = ' AND ' . $extrawheretest;
}
if (is_null($sort)) {
list($sort, $sortparams) = users_order_by_sql('u');
$whereorsortparams = array_merge($whereorsortparams, $sortparams);
}
$sql = "SELECT r.id AS roleid, u.id AS userid, {$fields}\n FROM {groups_members} gm\n JOIN {user} u ON u.id = gm.userid\n LEFT JOIN {role_assignments} ra ON (ra.userid = u.id AND ra.contextid {$relatedctxsql})\n LEFT JOIN {role} r ON r.id = ra.roleid\n WHERE gm.groupid=:mgroupid\n " . $extrawheretest . "\n ORDER BY r.sortorder, {$sort}";
$whereorsortparams = array_merge($whereorsortparams, $relatedctxparams, array('mgroupid' => $groupid));
$rs = $DB->get_recordset_sql($sql, $whereorsortparams);
return groups_calculate_role_people($rs, $context);
}
/**
* Lists users in a group based on their role on the course.
* Returns false if there's an error or there are no users in the group.
* Otherwise returns an array of role ID => role data, where role data includes:
* (role) $id, $shortname, $name
* $users: array of objects for each user which include the specified fields
* Users who do not have a role are stored in the returned array with key '-'
* and pseudo-role details (including a name, 'No role'). Users with multiple
* roles, same deal with key '*' and name 'Multiple roles'. You can find out
* which roles each has by looking in the $roles array of the user object.
*
* @param int $groupid
* @param int $courseid Course ID (should match the group's course)
* @param string $fields List of fields from user table prefixed with u, default 'u.*'
* @param string $sort SQL ORDER BY clause, default 'u.lastname ASC'
* @param string $extrawheretest extra SQL conditions ANDed with the existing where clause.
* @param array $whereparams any parameters required by $extrawheretest (named parameters).
* @return array Complex array as described above
*/
function groups_get_members_by_role($groupid, $courseid, $fields='u.*',
$sort='u.lastname ASC', $extrawheretest='', $whereparams=array()) {
global $CFG, $DB;
// Retrieve information about all users and their roles on the course or
// parent ('related') contexts
$context = get_context_instance(CONTEXT_COURSE, $courseid);
if ($extrawheretest) {
$extrawheretest = ' AND ' . $extrawheretest;
}
$sql = "SELECT r.id AS roleid, r.shortname AS roleshortname, r.name AS rolename,
u.id AS userid, $fields
FROM {groups_members} gm
JOIN {user} u ON u.id = gm.userid
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 gm.groupid=:mgroupid
".$extrawheretest."
ORDER BY r.sortorder, $sort";
$whereparams['mgroupid'] = $groupid;
$rs = $DB->get_recordset_sql($sql, $whereparams);
return groups_calculate_role_people($rs, $context);
}
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);
}
