Ir a la última revisión | Autoría | Comparar con el anterior | Ultima modificación | Ver Log |
<?php// This file is part of Moodle - http://moodle.org///// Moodle is free software: you can redistribute it and/or modify// it under the terms of the GNU General Public License as published by// the Free Software Foundation, either version 3 of the License, or// (at your option) any later version.//// Moodle is distributed in the hope that it will be useful,// but WITHOUT ANY WARRANTY; without even the implied warranty of// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the// GNU General Public License for more details.//// You should have received a copy of the GNU General Public License// along with Moodle. If not, see <http://www.gnu.org/licenses/>./*** Class used to fetch participants based on a filterset.** @package core_user* @copyright 2020 Michael Hawkins <michaelh@moodle.com>* @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later*/namespace core_user\table;use context;use context_helper;use core_table\local\filter\filterset;use core_user;use moodle_recordset;use stdClass;use core_user\fields;defined('MOODLE_INTERNAL') || die;require_once($CFG->dirroot . '/user/lib.php');/*** Class used to fetch participants based on a filterset.** @package core_user* @copyright 2020 Michael Hawkins <michaelh@moodle.com>* @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later*/class participants_search {/*** @var filterset $filterset The filterset describing which participants to include in the search.*/protected $filterset;/*** @var stdClass $course The course being searched.*/protected $course;/*** @var context_course $context The course context being searched.*/protected $context;/*** @var string[] $userfields Names of any extra user fields to be shown when listing users.*/protected $userfields;/*** Class constructor.** @param stdClass $course The course being searched.* @param context $context The context of the search.* @param filterset $filterset The filterset used to filter the participants in a course.*/public function __construct(stdClass $course, context $context, filterset $filterset) {$this->course = $course;$this->context = $context;$this->filterset = $filterset;$this->userfields = fields::get_identity_fields($this->context);}/*** Fetch participants matching the filterset.** @param string $additionalwhere Any additional SQL to add to where.* @param array $additionalparams The additional params used by $additionalwhere.* @param string $sort Optional SQL sort.* @param int $limitfrom Return a subset of records, starting at this point (optional).* @param int $limitnum Return a subset comprising this many records (optional, required if $limitfrom is set).* @return moodle_recordset*/public function get_participants(string $additionalwhere = '', array $additionalparams = [], string $sort = '',int $limitfrom = 0, int $limitnum = 0): moodle_recordset {global $DB;['subqueryalias' => $subqueryalias,'outerselect' => $outerselect,'innerselect' => $innerselect,'outerjoins' => $outerjoins,'innerjoins' => $innerjoins,'outerwhere' => $outerwhere,'innerwhere' => $innerwhere,'params' => $params,] = $this->get_participants_sql($additionalwhere, $additionalparams);$sql = "{$outerselect}FROM ({$innerselect}FROM {$innerjoins}{$innerwhere}) {$subqueryalias}{$outerjoins}{$outerwhere}{$sort}";return $DB->get_recordset_sql($sql, $params, $limitfrom, $limitnum);}/*** Returns the total number of participants for a given course.** @param string $additionalwhere Any additional SQL to add to where.* @param array $additionalparams The additional params used by $additionalwhere.* @return int*/public function get_total_participants_count(string $additionalwhere = '', array $additionalparams = []): int {global $DB;['subqueryalias' => $subqueryalias,'innerselect' => $innerselect,'outerjoins' => $outerjoins,'innerjoins' => $innerjoins,'outerwhere' => $outerwhere,'innerwhere' => $innerwhere,'params' => $params,] = $this->get_participants_sql($additionalwhere, $additionalparams);$sql = "SELECT COUNT(u.id)FROM ({$innerselect}FROM {$innerjoins}{$innerwhere}) {$subqueryalias}{$outerjoins}{$outerwhere}";return $DB->count_records_sql($sql, $params);}/*** Generate the SQL used to fetch filtered data for the participants table.** @param string $additionalwhere Any additional SQL to add to where* @param array $additionalparams The additional params* @return array*/protected function get_participants_sql(string $additionalwhere, array $additionalparams): array {global $CFG;$isfrontpage = ($this->course->id == SITEID);$accesssince = 0;// Whether to match on users who HAVE accessed since the given time (ie false is 'inactive for more than x').$matchaccesssince = false;// The alias for the subquery that fetches all distinct course users.$usersubqueryalias = 'targetusers';// The alias for {user} within the distinct user subquery.$inneruseralias = 'udistinct';// Inner query that selects distinct users in a course who are not deleted.// Note: This ensures the outer (filtering) query joins on distinct users, avoiding the need for GROUP BY.$innerselect = "SELECT DISTINCT {$inneruseralias}.id";$innerjoins = ["{user} {$inneruseralias}"];$innerwhere = "WHERE {$inneruseralias}.deleted = 0 AND {$inneruseralias}.id <> :siteguest";$params = ['siteguest' => $CFG->siteguest];$outerjoins = ["JOIN {user} u ON u.id = {$usersubqueryalias}.id"];$wheres = [];if ($this->filterset->has_filter('accesssince')) {$accesssince = $this->filterset->get_filter('accesssince')->current();// Last access filtering only supports matching or not matching, not any/all/none.$jointypenone = $this->filterset->get_filter('accesssince')::JOINTYPE_NONE;if ($this->filterset->get_filter('accesssince')->get_join_type() === $jointypenone) {$matchaccesssince = true;}}[// SQL that forms part of the filter.'sql' => $esql,// SQL for enrolment filtering that must always be applied (eg due to capability restrictions).'forcedsql' => $esqlforced,'params' => $eparams,] = $this->get_enrolled_sql();$params = array_merge($params, $eparams);// Get the fields for all contexts because there is a special case later where it allows// matches of fields you can't access if they are on your own account.$userfields = fields::for_identity(null)->with_userpic();['selects' => $userfieldssql, 'joins' => $userfieldsjoin, 'params' => $userfieldsparams, 'mappings' => $mappings] =(array)$userfields->get_sql('u', true);if ($userfieldsjoin) {$outerjoins[] = $userfieldsjoin;$params = array_merge($params, $userfieldsparams);}// Include any compulsory enrolment SQL (eg capability related filtering that must be applied).if (!empty($esqlforced)) {$outerjoins[] = "JOIN ({$esqlforced}) fef ON fef.id = u.id";}// Include any enrolment related filtering.if (!empty($esql)) {$outerjoins[] = "LEFT JOIN ({$esql}) ef ON ef.id = u.id";$wheres[] = 'ef.id IS NOT NULL';}if ($isfrontpage) {$outerselect = "SELECT u.lastaccess $userfieldssql";if ($accesssince) {$wheres[] = user_get_user_lastaccess_sql($accesssince, 'u', $matchaccesssince);}} else {$outerselect = "SELECT COALESCE(ul.timeaccess, 0) AS lastaccess $userfieldssql";// Not everybody has accessed the course yet.$outerjoins[] = 'LEFT JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = :courseid2)';$params['courseid2'] = $this->course->id;if ($accesssince) {$wheres[] = user_get_course_lastaccess_sql($accesssince, 'ul', $matchaccesssince);}// Make sure we only ever fetch users in the course (regardless of enrolment filters).$innerjoins[] = "JOIN {user_enrolments} ue ON ue.userid = {$inneruseralias}.id";$innerjoins[] = 'JOIN {enrol} e ON e.id = ue.enrolidAND e.courseid = :courseid1';$params['courseid1'] = $this->course->id;}// Performance hacks - we preload user contexts together with accounts.$ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');$ccjoin = 'LEFT JOIN {context} ctx ON (ctx.instanceid = u.id AND ctx.contextlevel = :contextlevel)';$params['contextlevel'] = CONTEXT_USER;$outerselect .= $ccselect;$outerjoins[] = $ccjoin;// Apply any role filtering.if ($this->filterset->has_filter('roles')) {['where' => $roleswhere,'params' => $rolesparams,] = $this->get_roles_sql();if (!empty($roleswhere)) {$wheres[] = "({$roleswhere})";}if (!empty($rolesparams)) {$params = array_merge($params, $rolesparams);}}// Apply any country filtering.if ($this->filterset->has_filter('country')) {['where' => $countrywhere,'params' => $countryparams,] = $this->get_country_sql();if (!empty($countrywhere)) {$wheres[] = "($countrywhere)";}if (!empty($countryparams)) {$params = array_merge($params, $countryparams);}}// Apply any keyword text searches.if ($this->filterset->has_filter('keywords')) {['where' => $keywordswhere,'params' => $keywordsparams,] = $this->get_keywords_search_sql($mappings);if (!empty($keywordswhere)) {$wheres[] = "({$keywordswhere})";}if (!empty($keywordsparams)) {$params = array_merge($params, $keywordsparams);}}// Add any supplied additional forced WHERE clauses.if (!empty($additionalwhere)) {$innerwhere .= " AND ({$additionalwhere})";$params = array_merge($params, $additionalparams);}// Prepare final values.$outerjoinsstring = implode("\n", $outerjoins);$innerjoinsstring = implode("\n", $innerjoins);if ($wheres) {switch ($this->filterset->get_join_type()) {case $this->filterset::JOINTYPE_ALL:$wherenot = '';$wheresjoin = ' AND ';break;case $this->filterset::JOINTYPE_NONE:$wherenot = ' NOT ';$wheresjoin = ' AND NOT ';// Some of the $where conditions may begin with `NOT` which results in `AND NOT NOT ...`.// To prevent this from breaking on Oracle the inner WHERE clause is wrapped in brackets, making it// `AND NOT (NOT ...)` which is valid in all DBs.$wheres = array_map(function($where) {return "({$where})";}, $wheres);break;default:// Default to 'Any' jointype.$wherenot = '';$wheresjoin = ' OR ';break;}$outerwhere = 'WHERE ' . $wherenot . implode($wheresjoin, $wheres);} else {$outerwhere = '';}return ['subqueryalias' => $usersubqueryalias,'outerselect' => $outerselect,'innerselect' => $innerselect,'outerjoins' => $outerjoinsstring,'innerjoins' => $innerjoinsstring,'outerwhere' => $outerwhere,'innerwhere' => $innerwhere,'params' => $params,];}/*** Prepare SQL and associated parameters for users enrolled in the course.** @return array SQL query data in the format ['sql' => '', 'forcedsql' => '', 'params' => []].*/protected function get_enrolled_sql(): array {global $USER;$isfrontpage = ($this->context->instanceid == SITEID);$prefix = 'eu_';$filteruid = "{$prefix}u.id";$sql = '';$joins = [];$wheres = [];$params = [];// It is possible some statements must always be included (in addition to any filtering).$forcedprefix = "f{$prefix}";$forceduid = "{$forcedprefix}u.id";$forcedsql = '';$forcedjoins = [];$forcedwhere = "{$forcedprefix}u.deleted = 0";if (!$isfrontpage) {// Prepare any enrolment method filtering.['joins' => $methodjoins,'where' => $wheres[],'params' => $methodparams,] = $this->get_enrol_method_sql($filteruid);// Prepare any status filtering.['joins' => $statusjoins,'where' => $statuswhere,'params' => $statusparams,'forcestatus' => $forcestatus,] = $this->get_status_sql($filteruid, $forceduid, $forcedprefix);if ($forcestatus) {// Force filtering by active participants if user does not have capability to view suspended.$forcedjoins = array_merge($forcedjoins, $statusjoins);$statusjoins = [];$forcedwhere .= " AND ({$statuswhere})";} else {$wheres[] = $statuswhere;}$joins = array_merge($joins, $methodjoins, $statusjoins);$params = array_merge($params, $methodparams, $statusparams);}$groupids = [];if ($this->filterset->has_filter('groups')) {$groupids = $this->filterset->get_filter('groups')->get_filter_values();}// Force additional groups filtering if required due to lack of capabilities.// Note: This means results will always be limited to allowed groups, even if the user applies their own groups filtering.$canaccessallgroups = has_capability('moodle/site:accessallgroups', $this->context);$forcegroups = ($this->course->groupmode == SEPARATEGROUPS && !$canaccessallgroups);if ($forcegroups) {$allowedgroupids = array_keys(groups_get_all_groups($this->course->id, $USER->id));// Users not in any group in a course with separate groups mode should not be able to access the participants filter.if (empty($allowedgroupids)) {// The UI does not support this, so it should not be reachable unless someone is trying to bypass the restriction.throw new \coding_exception('User must be part of a group to filter by participants.');}$forceduid = "{$forcedprefix}u.id";$forcedjointype = $this->get_groups_jointype(\core_table\local\filter\filter::JOINTYPE_ANY);$forcedgroupjoin = groups_get_members_join($allowedgroupids, $forceduid, $this->context, $forcedjointype);$forcedjoins[] = $forcedgroupjoin->joins;$forcedwhere .= " AND ({$forcedgroupjoin->wheres})";$params = array_merge($params, $forcedgroupjoin->params);// Remove any filtered groups the user does not have access to.$groupids = array_intersect($allowedgroupids, $groupids);}// Prepare any user defined groups filtering.if ($groupids) {$groupjoin = groups_get_members_join($groupids, $filteruid, $this->context, $this->get_groups_jointype());$joins[] = $groupjoin->joins;$params = array_merge($params, $groupjoin->params);if (!empty($groupjoin->wheres)) {$wheres[] = $groupjoin->wheres;}}// Combine the relevant filters and prepare the query.$joins = array_filter($joins);if (!empty($joins)) {$joinsql = implode("\n", $joins);$sql = "SELECT DISTINCT {$prefix}u.idFROM {user} {$prefix}u{$joinsql}WHERE {$prefix}u.deleted = 0";}$wheres = array_filter($wheres);if (!empty($wheres)) {if ($this->filterset->get_join_type() === $this->filterset::JOINTYPE_ALL) {$wheresql = '(' . implode(') AND (', $wheres) . ')';} else {$wheresql = '(' . implode(') OR (', $wheres) . ')';}$sql .= " AND ({$wheresql})";}// Prepare any SQL that must be applied.if (!empty($forcedjoins)) {$forcedjoinsql = implode("\n", $forcedjoins);$forcedsql = "SELECT DISTINCT {$forcedprefix}u.idFROM {user} {$forcedprefix}u{$forcedjoinsql}WHERE {$forcedwhere}";}return ['sql' => $sql,'forcedsql' => $forcedsql,'params' => $params,];}/*** Prepare the enrolment methods filter SQL content.** @param string $useridcolumn User ID column used in the calling query, e.g. u.id* @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => []].*/protected function get_enrol_method_sql($useridcolumn): array {global $DB;$prefix = 'ejm_';$joins = [];$where = '';$params = [];$enrolids = [];if ($this->filterset->has_filter('enrolments')) {$enrolids = $this->filterset->get_filter('enrolments')->get_filter_values();}if (!empty($enrolids)) {$jointype = $this->filterset->get_filter('enrolments')->get_join_type();// Handle 'All' join type.if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL ||$jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_NONE) {$allwheres = [];foreach ($enrolids as $i => $enrolid) {$thisprefix = "{$prefix}{$i}";list($enrolidsql, $enrolidparam) = $DB->get_in_or_equal($enrolid, SQL_PARAMS_NAMED, $thisprefix);$joins[] = "LEFT JOIN {enrol} {$thisprefix}eON ({$thisprefix}e.id {$enrolidsql}AND {$thisprefix}e.courseid = :{$thisprefix}courseid)";$joins[] = "LEFT JOIN {user_enrolments} {$thisprefix}ueON {$thisprefix}ue.userid = {$useridcolumn}AND {$thisprefix}ue.enrolid = {$thisprefix}e.id";if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL) {$allwheres[] = "{$thisprefix}ue.id IS NOT NULL";} else {// Ensure participants do not match any of the filtered methods when joining by 'None'.$allwheres[] = "{$thisprefix}ue.id IS NULL";}$params["{$thisprefix}courseid"] = $this->course->id;$params = array_merge($params, $enrolidparam);}if (!empty($allwheres)) {$where = implode(' AND ', $allwheres);}} else {// Handle the 'Any'join type.list($enrolidssql, $enrolidsparams) = $DB->get_in_or_equal($enrolids, SQL_PARAMS_NAMED, $prefix);$joins[] = "LEFT JOIN {enrol} {$prefix}eON ({$prefix}e.id {$enrolidssql}AND {$prefix}e.courseid = :{$prefix}courseid)";$joins[] = "LEFT JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}AND {$prefix}ue.enrolid = {$prefix}e.id";$where = "{$prefix}ue.id IS NOT NULL";$params["{$prefix}courseid"] = $this->course->id;$params = array_merge($params, $enrolidsparams);}}return ['joins' => $joins,'where' => $where,'params' => $params,];}/*** Prepare the status filter SQL content.* Note: Users who cannot view suspended users will always have their results filtered to only show active participants.** @param string $filteruidcolumn User ID column used in the calling query, e.g. eu_u.id* @param string $forceduidcolumn User ID column used in any forced query, e.g. feu_u.id* @param string $forcedprefix The prefix to use if forced filtering is required* @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => [], 'forcestatus' => true]*/protected function get_status_sql($filteruidcolumn, $forceduidcolumn, $forcedprefix): array {$prefix = $forcedprefix;$useridcolumn = $forceduidcolumn;$joins = [];$where = '';$params = [];$forcestatus = true;// By default we filter to show users with active status only.$statusids = [ENROL_USER_ACTIVE];$statusjointype = $this->filterset::JOINTYPE_DEFAULT;// Allow optional status filtering if the user has relevant capabilities.if (has_capability('moodle/course:enrolreview', $this->context) &&(has_capability('moodle/course:viewsuspendedusers', $this->context))) {$forcestatus = false;$prefix = 'ejs_';$useridcolumn = $filteruidcolumn;// Default to no filtering if capabilities allow for it.$statusids = [];if ($this->filterset->has_filter('status')) {$statusjointype = $this->filterset->get_filter('status')->get_join_type();$statusfiltervalues = $this->filterset->get_filter('status')->get_filter_values();// If values are set for the status filter, use them.if (!empty($statusfiltervalues)) {$statusids = $statusfiltervalues;}}}if (!empty($statusids)) {$enroljoin = 'JOIN {enrol} %1$se ON %1$se.id = %1$sue.enrolidAND %1$se.courseid = :%1$scourseid';$whereactive = '(%1$sue.status = :%2$sactiveAND %1$se.status = :%2$senabledAND %1$sue.timestart < :%2$snow1AND (%1$sue.timeend = 0OR %1$sue.timeend > :%2$snow2))';$wheresuspended = '(%1$sue.status = :%2$ssuspendedOR %1$se.status != :%2$senabledOR %1$sue.timestart >= :%2$snow1OR (%1$sue.timeend > 0AND %1$sue.timeend <= :%2$snow2))';// Round 'now' time to help DB caching.$now = round(time(), -2);switch ($statusjointype) {case $this->filterset::JOINTYPE_ALL:$joinwheres = [];foreach ($statusids as $i => $statusid) {$joinprefix = "{$prefix}{$i}";$joins[] = "JOIN {user_enrolments} {$joinprefix}ue ON {$joinprefix}ue.userid = {$useridcolumn}";if ($statusid === ENROL_USER_ACTIVE) {// Conditions to be met if user filtering by active.$joinwheres[] = sprintf($whereactive, $joinprefix, $joinprefix);$activeparams = ["{$joinprefix}active" => ENROL_USER_ACTIVE,"{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,"{$joinprefix}now1" => $now,"{$joinprefix}now2" => $now,"{$joinprefix}courseid" => $this->course->id,];$params = array_merge($params, $activeparams);} else {// Conditions to be met if filtering by suspended (currently the only other status).$joinwheres[] = sprintf($wheresuspended, $joinprefix, $joinprefix);$suspendedparams = ["{$joinprefix}suspended" => ENROL_USER_SUSPENDED,"{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,"{$joinprefix}now1" => $now,"{$joinprefix}now2" => $now,"{$joinprefix}courseid" => $this->course->id,];$params = array_merge($params, $suspendedparams);}$joins[] = sprintf($enroljoin, $joinprefix);}$where = implode(' AND ', $joinwheres);break;case $this->filterset::JOINTYPE_NONE:// Should always be enrolled, just not in any of the filtered statuses.$joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";$joins[] = sprintf($enroljoin, $prefix);$joinwheres = [];$params["{$prefix}courseid"] = $this->course->id;foreach ($statusids as $i => $statusid) {$paramprefix = "{$prefix}{$i}";if ($statusid === ENROL_USER_ACTIVE) {// Conditions to be met if user filtering by active.$joinwheres[] = sprintf("NOT {$whereactive}", $prefix, $paramprefix);$activeparams = ["{$paramprefix}active" => ENROL_USER_ACTIVE,"{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,"{$paramprefix}now1" => $now,"{$paramprefix}now2" => $now,];$params = array_merge($params, $activeparams);} else {// Conditions to be met if filtering by suspended (currently the only other status).$joinwheres[] = sprintf("NOT {$wheresuspended}", $prefix, $paramprefix);$suspendedparams = ["{$paramprefix}suspended" => ENROL_USER_SUSPENDED,"{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,"{$paramprefix}now1" => $now,"{$paramprefix}now2" => $now,];$params = array_merge($params, $suspendedparams);}}$where = '(' . implode(' AND ', $joinwheres) . ')';break;default:// Handle the 'Any' join type.$joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";$joins[] = sprintf($enroljoin, $prefix);$joinwheres = [];$params["{$prefix}courseid"] = $this->course->id;foreach ($statusids as $i => $statusid) {$paramprefix = "{$prefix}{$i}";if ($statusid === ENROL_USER_ACTIVE) {// Conditions to be met if user filtering by active.$joinwheres[] = sprintf($whereactive, $prefix, $paramprefix);$activeparams = ["{$paramprefix}active" => ENROL_USER_ACTIVE,"{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,"{$paramprefix}now1" => $now,"{$paramprefix}now2" => $now,];$params = array_merge($params, $activeparams);} else {// Conditions to be met if filtering by suspended (currently the only other status).$joinwheres[] = sprintf($wheresuspended, $prefix, $paramprefix);$suspendedparams = ["{$paramprefix}suspended" => ENROL_USER_SUSPENDED,"{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,"{$paramprefix}now1" => $now,"{$paramprefix}now2" => $now,];$params = array_merge($params, $suspendedparams);}}$where = '(' . implode(' OR ', $joinwheres) . ')';break;}}return ['joins' => $joins,'where' => $where,'params' => $params,'forcestatus' => $forcestatus,];}/*** Fetch the groups filter's grouplib jointype, based on its filterset jointype.* This mapping is to ensure compatibility between the two, should their values ever differ.** @param int|null $forcedjointype If set, specifies the join type to fetch mapping for (used when applying forced filtering).* If null, then user defined filter join type is used.* @return int*/protected function get_groups_jointype(?int $forcedjointype = null): int {// If applying forced groups filter and no manual groups filtering is applied, add an empty filter so we can map the join.if (!is_null($forcedjointype) && !$this->filterset->has_filter('groups')) {$this->filterset->add_filter(new \core_table\local\filter\integer_filter('groups'));}$groupsfilter = $this->filterset->get_filter('groups');if (is_null($forcedjointype)) {// Fetch join type mapping for a user supplied groups filtering.$filterjointype = $groupsfilter->get_join_type();} else {// Fetch join type mapping for forced groups filtering.$filterjointype = $forcedjointype;}switch ($filterjointype) {case $groupsfilter::JOINTYPE_NONE:$groupsjoin = GROUPS_JOIN_NONE;break;case $groupsfilter::JOINTYPE_ALL:$groupsjoin = GROUPS_JOIN_ALL;break;default:// Default to ANY jointype.$groupsjoin = GROUPS_JOIN_ANY;break;}return $groupsjoin;}/*** Prepare SQL where clause and associated parameters for any roles filtering being performed.** @return array SQL query data in the format ['where' => '', 'params' => []].*/protected function get_roles_sql(): array {global $DB;$where = '';$params = [];// Limit list to users with some role only.if ($this->filterset->has_filter('roles')) {$rolesfilter = $this->filterset->get_filter('roles');$roleids = $rolesfilter->get_filter_values();$jointype = $rolesfilter->get_join_type();// Determine how to match values in the query.$matchinsql = 'IN';switch ($jointype) {case $rolesfilter::JOINTYPE_ALL:$wherejoin = ' AND ';break;case $rolesfilter::JOINTYPE_NONE:$wherejoin = ' AND NOT ';$matchinsql = 'NOT IN';break;default:// Default to 'Any' jointype.$wherejoin = ' OR ';break;}// We want to query both the current context and parent contexts.$rolecontextids = $this->context->get_parent_context_ids(true);// Get users without any role, if needed.if (($withoutkey = array_search(-1, $roleids)) !== false) {list($relatedctxsql1, $norolectxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');if ($jointype === $rolesfilter::JOINTYPE_NONE) {$where .= "(u.id IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";} else {$where .= "(u.id NOT IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";}$params = array_merge($params, $norolectxparams);if ($withoutkey !== false) {unset($roleids[$withoutkey]);}// Join if any roles will be included.if (!empty($roleids)) {// The NOT case is replaced with AND to prevent a double negative.$where .= $jointype === $rolesfilter::JOINTYPE_NONE ? ' AND ' : $wherejoin;}}// Get users with specified roles, if needed.if (!empty($roleids)) {// All case - need one WHERE per filtered role.if ($rolesfilter::JOINTYPE_ALL === $jointype) {$numroles = count($roleids);$rolecount = 1;foreach ($roleids as $roleid) {list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');list($roleidssql, $roleidparams) = $DB->get_in_or_equal($roleid, SQL_PARAMS_NAMED, 'roleids');$where .= "(u.id IN (SELECT useridFROM {role_assignments}WHERE roleid {$roleidssql}AND contextid {$relatedctxsql}))";if ($rolecount < $numroles) {$where .= $wherejoin;$rolecount++;}$params = array_merge($params, $roleidparams, $relctxparams);}} else {// Any / None cases - need one WHERE to cover all filtered roles.list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');list($roleidssql, $roleidsparams) = $DB->get_in_or_equal($roleids, SQL_PARAMS_NAMED, 'roleids');$where .= "(u.id {$matchinsql} (SELECT useridFROM {role_assignments}WHERE roleid {$roleidssql}AND contextid {$relatedctxsql}))";$params = array_merge($params, $roleidsparams, $relctxparams);}}}return ['where' => $where,'params' => $params,];}/*** Prepare SQL where clause and associated parameters for country filtering** @return array SQL query data in the format ['where' => '', 'params' => []].*/protected function get_country_sql(): array {global $DB;$where = '';$params = [];$countryfilter = $this->filterset->get_filter('country');if ($countrycodes = $countryfilter->get_filter_values()) {// If filter type is "None", then we negate the comparison.[$countrywhere, $params] = $DB->get_in_or_equal($countrycodes, SQL_PARAMS_NAMED, 'country',$countryfilter->get_join_type() !== $countryfilter::JOINTYPE_NONE);$where = "(u.country {$countrywhere})";}return ['where' => $where,'params' => $params,];}/*** Prepare SQL where clause and associated parameters for any keyword searches being performed.** @param array $mappings Array of field mappings (fieldname => SQL code for the value)* @return array SQL query data in the format ['where' => '', 'params' => []].*/protected function get_keywords_search_sql(array $mappings): array {global $CFG, $DB, $USER;$keywords = [];$where = '';$params = [];$keywordsfilter = $this->filterset->get_filter('keywords');$jointype = $keywordsfilter->get_join_type();// None join types in both filter row and filterset require additional 'not null' handling for accurate keywords matches.$notjoin = false;// Determine how to match values in the query.switch ($jointype) {case $keywordsfilter::JOINTYPE_ALL:$wherejoin = ' AND ';break;case $keywordsfilter::JOINTYPE_NONE:$wherejoin = ' AND NOT ';$notjoin = true;break;default:// Default to 'Any' jointype.$wherejoin = ' OR ';break;}// Handle filterset None join type.if ($this->filterset->get_join_type() === $this->filterset::JOINTYPE_NONE) {$notjoin = true;}if ($this->filterset->has_filter('keywords')) {$keywords = $keywordsfilter->get_filter_values();}$canviewfullnames = has_capability('moodle/site:viewfullnames', $this->context);foreach ($keywords as $index => $keyword) {$searchkey1 = 'search' . $index . '1';$searchkey2 = 'search' . $index . '2';$searchkey3 = 'search' . $index . '3';$searchkey4 = 'search' . $index . '4';$searchkey5 = 'search' . $index . '5';$searchkey6 = 'search' . $index . '6';$searchkey7 = 'search' . $index . '7';$conditions = [];// Search by fullname.[$fullname, $fullnameparams] = fields::get_sql_fullname('u', $canviewfullnames);$conditions[] = $DB->sql_like($fullname, ':' . $searchkey1, false, false);$params = array_merge($params, $fullnameparams);// Search by email.$email = $DB->sql_like('email', ':' . $searchkey2, false, false);if ($notjoin) {$email = "(email IS NOT NULL AND {$email})";}if (!in_array('email', $this->userfields)) {$maildisplay = 'maildisplay' . $index;$userid1 = 'userid' . $index . '1';// Prevent users who hide their email address from being found by others// who aren't allowed to see hidden email addresses.$email = "(". $email ." AND (" ."u.maildisplay <> :$maildisplay " ."OR u.id = :$userid1". // Users can always find themselves."))";$params[$maildisplay] = core_user::MAILDISPLAY_HIDE;$params[$userid1] = $USER->id;}$conditions[] = $email;// Search by idnumber.$idnumber = $DB->sql_like('idnumber', ':' . $searchkey3, false, false);if ($notjoin) {$idnumber = "(idnumber IS NOT NULL AND {$idnumber})";}if (!in_array('idnumber', $this->userfields)) {$userid2 = 'userid' . $index . '2';// Users who aren't allowed to see idnumbers should at most find themselves// when searching for an idnumber.$idnumber = "(". $idnumber . " AND u.id = :$userid2)";$params[$userid2] = $USER->id;}$conditions[] = $idnumber;// Search all user identify fields.$extrasearchfields = fields::get_identity_fields(null);foreach ($extrasearchfields as $fieldindex => $extrasearchfield) {if (in_array($extrasearchfield, ['email', 'idnumber', 'country'])) {// Already covered above.continue;}// The param must be short (max 32 characters) so don't include field name.$param = $searchkey3 . '_ident' . $fieldindex;$fieldsql = $mappings[$extrasearchfield];$condition = $DB->sql_like($fieldsql, ':' . $param, false, false);$params[$param] = "%$keyword%";if ($notjoin) {$condition = "($fieldsql IS NOT NULL AND {$condition})";}if (!in_array($extrasearchfield, $this->userfields)) {// User cannot see this field, but allow match if their own account.$userid3 = 'userid' . $index . '3_ident' . $fieldindex;$condition = "(". $condition . " AND u.id = :$userid3)";$params[$userid3] = $USER->id;}$conditions[] = $condition;}// Search by middlename.$middlename = $DB->sql_like('middlename', ':' . $searchkey4, false, false);if ($notjoin) {$middlename = "(middlename IS NOT NULL AND {$middlename})";}$conditions[] = $middlename;// Search by alternatename.$alternatename = $DB->sql_like('alternatename', ':' . $searchkey5, false, false);if ($notjoin) {$alternatename = "(alternatename IS NOT NULL AND {$alternatename})";}$conditions[] = $alternatename;// Search by firstnamephonetic.$firstnamephonetic = $DB->sql_like('firstnamephonetic', ':' . $searchkey6, false, false);if ($notjoin) {$firstnamephonetic = "(firstnamephonetic IS NOT NULL AND {$firstnamephonetic})";}$conditions[] = $firstnamephonetic;// Search by lastnamephonetic.$lastnamephonetic = $DB->sql_like('lastnamephonetic', ':' . $searchkey7, false, false);if ($notjoin) {$lastnamephonetic = "(lastnamephonetic IS NOT NULL AND {$lastnamephonetic})";}$conditions[] = $lastnamephonetic;if (!empty($where)) {$where .= $wherejoin;} else if ($jointype === $keywordsfilter::JOINTYPE_NONE) {// Join type 'None' requires the WHERE to begin with NOT.$where .= ' NOT ';}$where .= "(". implode(" OR ", $conditions) .") ";$params[$searchkey1] = "%$keyword%";$params[$searchkey2] = "%$keyword%";$params[$searchkey3] = "%$keyword%";$params[$searchkey4] = "%$keyword%";$params[$searchkey5] = "%$keyword%";$params[$searchkey6] = "%$keyword%";$params[$searchkey7] = "%$keyword%";}return ['where' => $where,'params' => $params,];}}