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