33122ccc3698e76733de8da3f9f72c6ed141978d
[moodle.git] / user / classes / table / participants_search.php
1 <?php
2 // This file is part of Moodle - http://moodle.org/
3 //
4 // Moodle is free software: you can redistribute it and/or modify
5 // it under the terms of the GNU General Public License as published by
6 // the Free Software Foundation, either version 3 of the License, or
7 // (at your option) any later version.
8 //
9 // Moodle is distributed in the hope that it will be useful,
10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
12 // GNU General Public License for more details.
13 //
14 // You should have received a copy of the GNU General Public License
15 // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
17 /**
18  * Class used to fetch participants based on a filterset.
19  *
20  * @package    core_user
21  * @copyright  2020 Michael Hawkins <michaelh@moodle.com>
22  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
23  */
25 namespace core_user\table;
27 use context;
28 use context_helper;
29 use core_table\local\filter\filterset;
30 use core_user;
31 use moodle_recordset;
32 use stdClass;
33 use user_picture;
35 defined('MOODLE_INTERNAL') || die;
37 require_once($CFG->dirroot . '/user/lib.php');
39 /**
40  * Class used to fetch participants based on a filterset.
41  *
42  * @package    core_user
43  * @copyright  2020 Michael Hawkins <michaelh@moodle.com>
44  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
45  */
46 class participants_search {
48     /**
49      * @var filterset $filterset The filterset describing which participants to include in the search.
50      */
51     protected $filterset;
53     /**
54      * @var stdClass $course The course being searched.
55      */
56     protected $course;
58     /**
59      * @var context_course $context The course context being searched.
60      */
61     protected $context;
63     /**
64      * @var string[] $userfields Names of any extra user fields to be shown when listing users.
65      */
66     protected $userfields;
68     /**
69      * Class constructor.
70      *
71      * @param stdClass $course The course being searched.
72      * @param context $context The context of the search.
73      * @param filterset $filterset The filterset used to filter the participants in a course.
74      */
75     public function __construct(stdClass $course, context $context, filterset $filterset) {
76         $this->course = $course;
77         $this->context = $context;
78         $this->filterset = $filterset;
80         $this->userfields = get_extra_user_fields($this->context);
81     }
83     /**
84      * Fetch participants matching the filterset.
85      *
86      * @param string $additionalwhere Any additional SQL to add to where.
87      * @param array $additionalparams The additional params used by $additionalwhere.
88      * @param string $sort Optional SQL sort.
89      * @param int $limitfrom Return a subset of records, starting at this point (optional).
90      * @param int $limitnum Return a subset comprising this many records (optional, required if $limitfrom is set).
91      * @return moodle_recordset
92      */
93     public function get_participants(string $additionalwhere = '', array $additionalparams = [], string $sort = '',
94             int $limitfrom = 0, int $limitnum = 0): moodle_recordset {
95         global $DB;
97         [
98             'subqueryalias' => $subqueryalias,
99             'outerselect' => $outerselect,
100             'innerselect' => $innerselect,
101             'outerjoins' => $outerjoins,
102             'innerjoins' => $innerjoins,
103             'outerwhere' => $outerwhere,
104             'innerwhere' => $innerwhere,
105             'params' => $params,
106         ] = $this->get_participants_sql($additionalwhere, $additionalparams);
108         $sql = "{$outerselect}
109                           FROM ({$innerselect}
110                                           FROM {$innerjoins}
111                                  {$innerwhere}
112                                ) {$subqueryalias}
113                  {$outerjoins}
114                  {$outerwhere}
115                        {$sort}";
117         return $DB->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
118     }
120     /**
121      * Returns the total number of participants for a given course.
122      *
123      * @param string $additionalwhere Any additional SQL to add to where.
124      * @param array $additionalparams The additional params used by $additionalwhere.
125      * @return int
126      */
127     public function get_total_participants_count(string $additionalwhere = '', array $additionalparams = []): int {
128         global $DB;
130         [
131             'subqueryalias' => $subqueryalias,
132             'innerselect' => $innerselect,
133             'outerjoins' => $outerjoins,
134             'innerjoins' => $innerjoins,
135             'outerwhere' => $outerwhere,
136             'innerwhere' => $innerwhere,
137             'params' => $params,
138         ] = $this->get_participants_sql($additionalwhere, $additionalparams);
140         $sql = "SELECT COUNT(u.id)
141                   FROM ({$innerselect}
142                                   FROM {$innerjoins}
143                          {$innerwhere}
144                        ) {$subqueryalias}
145          {$outerjoins}
146          {$outerwhere}";
148         return $DB->count_records_sql($sql, $params);
149     }
151     /**
152      * Generate the SQL used to fetch filtered data for the participants table.
153      *
154      * @param string $additionalwhere Any additional SQL to add to where
155      * @param array $additionalparams The additional params
156      * @return array
157      */
158     protected function get_participants_sql(string $additionalwhere, array $additionalparams): array {
159         $isfrontpage = ($this->course->id == SITEID);
160         $accesssince = 0;
161         // Whether to match on users who HAVE accessed since the given time (ie false is 'inactive for more than x').
162         $matchaccesssince = false;
164         // The alias for the subquery that fetches all distinct course users.
165         $usersubqueryalias = 'targetusers';
166         // The alias for {user} within the distinct user subquery.
167         $inneruseralias = 'udistinct';
168         // Inner query that selects distinct users in a course who are not deleted.
169         // Note: This ensures the outer (filtering) query joins on distinct users, avoiding the need for GROUP BY.
170         $innerselect = "SELECT DISTINCT {$inneruseralias}.id";
171         $innerjoins = ["{user} {$inneruseralias}"];
172         $innerwhere = "WHERE {$inneruseralias}.deleted = 0";
174         $outerjoins = ["JOIN {user} u ON u.id = {$usersubqueryalias}.id"];
175         $wheres = [];
177         if ($this->filterset->has_filter('accesssince')) {
178             $accesssince = $this->filterset->get_filter('accesssince')->current();
180             // Last access filtering only supports matching or not matching, not any/all/none.
181             $jointypenone = $this->filterset->get_filter('accesssince')::JOINTYPE_NONE;
182             if ($this->filterset->get_filter('accesssince')->get_join_type() === $jointypenone) {
183                 $matchaccesssince = true;
184             }
185         }
187         [
188             // SQL that forms part of the filter.
189             'sql' => $esql,
190             // SQL for enrolment filtering that must always be applied (eg due to capability restrictions).
191             'forcedsql' => $esqlforced,
192             'params' => $params,
193         ] = $this->get_enrolled_sql();
195         $userfieldssql = user_picture::fields('u', $this->userfields);
197         // Include any compulsory enrolment SQL (eg capability related filtering that must be applied).
198         if (!empty($esqlforced)) {
199             $outerjoins[] = "JOIN ({$esqlforced}) fef ON fef.id = u.id";
200         }
202         // Include any enrolment related filtering.
203         if (!empty($esql)) {
204             $outerjoins[] = "LEFT JOIN ({$esql}) ef ON ef.id = u.id";
205             $wheres[] = 'ef.id IS NOT NULL';
206         }
208         if ($isfrontpage) {
209             $outerselect = "SELECT {$userfieldssql}, u.lastaccess";
210             if ($accesssince) {
211                 $wheres[] = user_get_user_lastaccess_sql($accesssince, 'u', $matchaccesssince);
212             }
213         } else {
214             $outerselect = "SELECT {$userfieldssql}, COALESCE(ul.timeaccess, 0) AS lastaccess";
215             // Not everybody has accessed the course yet.
216             $outerjoins[] = 'LEFT JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = :courseid2)';
217             $params['courseid2'] = $this->course->id;
218             if ($accesssince) {
219                 $wheres[] = user_get_course_lastaccess_sql($accesssince, 'ul', $matchaccesssince);
220             }
222             // Make sure we only ever fetch users in the course (regardless of enrolment filters).
223             $innerjoins[] = "JOIN {user_enrolments} ue ON ue.userid = {$inneruseralias}.id";
224             $innerjoins[] = 'JOIN {enrol} e ON e.id = ue.enrolid
225                                       AND e.courseid = :courseid1';
226             $params['courseid1'] = $this->course->id;
227         }
229         // Performance hacks - we preload user contexts together with accounts.
230         $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');
231         $ccjoin = 'LEFT JOIN {context} ctx ON (ctx.instanceid = u.id AND ctx.contextlevel = :contextlevel)';
232         $params['contextlevel'] = CONTEXT_USER;
233         $outerselect .= $ccselect;
234         $outerjoins[] = $ccjoin;
236         // Apply any role filtering.
237         if ($this->filterset->has_filter('roles')) {
238             [
239                 'where' => $roleswhere,
240                 'params' => $rolesparams,
241             ] = $this->get_roles_sql();
243             if (!empty($roleswhere)) {
244                 $wheres[] = "({$roleswhere})";
245             }
247             if (!empty($rolesparams)) {
248                 $params = array_merge($params, $rolesparams);
249             }
250         }
252         // Apply any keyword text searches.
253         if ($this->filterset->has_filter('keywords')) {
254             [
255                 'where' => $keywordswhere,
256                 'params' => $keywordsparams,
257             ] = $this->get_keywords_search_sql();
259             if (!empty($keywordswhere)) {
260                 $wheres[] = $keywordswhere;
261             }
263             if (!empty($keywordsparams)) {
264                 $params = array_merge($params, $keywordsparams);
265             }
266         }
268         // Add any supplied additional forced WHERE clauses.
269         if (!empty($additionalwhere)) {
270             $innerwhere .= " AND ({$additionalwhere})";
271             $params = array_merge($params, $additionalparams);
272         }
274         // Prepare final values.
275         $outerjoinsstring = implode("\n", $outerjoins);
276         $innerjoinsstring = implode("\n", $innerjoins);
277         if ($wheres) {
278             switch ($this->filterset->get_join_type()) {
279                 case $this->filterset::JOINTYPE_ALL:
280                     $wherenot = '';
281                     $wheresjoin = ' AND ';
282                     break;
283                 case $this->filterset::JOINTYPE_NONE:
284                     $wherenot = ' NOT ';
285                     $wheresjoin = ' AND NOT ';
286                     break;
287                 default:
288                     // Default to 'Any' jointype.
289                     $wherenot = '';
290                     $wheresjoin = ' OR ';
291                     break;
292             }
294             $outerwhere = 'WHERE ' . $wherenot . implode($wheresjoin, $wheres);
295         } else {
296             $outerwhere = '';
297         }
299         return [
300             'subqueryalias' => $usersubqueryalias,
301             'outerselect' => $outerselect,
302             'innerselect' => $innerselect,
303             'outerjoins' => $outerjoinsstring,
304             'innerjoins' => $innerjoinsstring,
305             'outerwhere' => $outerwhere,
306             'innerwhere' => $innerwhere,
307             'params' => $params,
308         ];
309     }
311     /**
312      * Prepare SQL and associated parameters for users enrolled in the course.
313      *
314      * @return array SQL query data in the format ['sql' => '', 'forcedsql' => '', 'params' => []].
315      */
316     protected function get_enrolled_sql(): array {
317         global $USER;
319         $isfrontpage = ($this->context->instanceid == SITEID);
320         $prefix = 'eu_';
321         $filteruid = "{$prefix}u.id";
322         $sql = '';
323         $joins = [];
324         $wheres = [];
325         $params = [];
326         // It is possible some statements must always be included (in addition to any filtering).
327         $forcedprefix = "f{$prefix}";
328         $forceduid = "{$forcedprefix}u.id";
329         $forcedsql = '';
330         $forcedjoins = [];
331         $forcedwhere = "{$forcedprefix}u.deleted = 0";
333         if (!$isfrontpage) {
334             // Prepare any enrolment method filtering.
335             [
336                 'joins' => $methodjoins,
337                 'where' => $wheres[],
338                 'params' => $methodparams,
339             ] = $this->get_enrol_method_sql($filteruid);
341             // Prepare any status filtering.
342             [
343                 'joins' => $statusjoins,
344                 'where' => $statuswhere,
345                 'params' => $statusparams,
346                 'forcestatus' => $forcestatus,
347             ] = $this->get_status_sql($filteruid, $forceduid, $forcedprefix);
349             if ($forcestatus) {
350                 // Force filtering by active participants if user does not have capability to view suspended.
351                 $forcedjoins = array_merge($forcedjoins, $statusjoins);
352                 $statusjoins = [];
353                 $forcedwhere .= " AND ({$statuswhere})";
354             } else {
355                 $wheres[] = $statuswhere;
356             }
358             $joins = array_merge($joins, $methodjoins, $statusjoins);
359             $params = array_merge($params, $methodparams, $statusparams);
360         }
362         $groupids = [];
364         if ($this->filterset->has_filter('groups')) {
365             $groupids = $this->filterset->get_filter('groups')->get_filter_values();
366         }
368         // Force additional groups filtering if required due to lack of capabilities.
369         // Note: This means results will always be limited to allowed groups, even if the user applies their own groups filtering.
370         $canaccessallgroups = has_capability('moodle/site:accessallgroups', $this->context);
371         $forcegroups = ($this->course->groupmode == SEPARATEGROUPS && !$canaccessallgroups);
373         if ($forcegroups) {
374             $allowedgroupids = array_keys(groups_get_all_groups($this->course->id, $USER->id));
376             // Users not in any group in a course with separate groups mode should not be able to access the participants filter.
377             if (empty($allowedgroupids)) {
378                 // The UI does not support this, so it should not be reachable unless someone is trying to bypass the restriction.
379                 throw new \coding_exception('User must be part of a group to filter by participants.');
380             }
382             $forceduid = "{$forcedprefix}u.id";
383             $forcedjointype = $this->get_groups_jointype(\core_table\local\filter\filter::JOINTYPE_ANY);
384             $forcedgroupjoin = groups_get_members_join($allowedgroupids, $forceduid, $this->context, $forcedjointype);
386             $forcedjoins[] = $forcedgroupjoin->joins;
387             $forcedwhere .= "AND ({$forcedgroupjoin->wheres})";
389             $params = array_merge($params, $forcedgroupjoin->params);
391             // Remove any filtered groups the user does not have access to.
392             $groupids = array_intersect($allowedgroupids, $groupids);
393         }
395         // Prepare any user defined groups filtering.
396         if ($groupids) {
397             $groupjoin = groups_get_members_join($groupids, $filteruid, $this->context, $this->get_groups_jointype());
399             $joins[] = $groupjoin->joins;
400             $params = array_merge($params, $groupjoin->params);
401             if (!empty($groupjoin->wheres)) {
402                 $wheres[] = $groupjoin->wheres;
403             }
404         }
406         // Combine the relevant filters and prepare the query.
407         $joins = array_filter($joins);
408         if (!empty($joins)) {
409             $joinsql = implode("\n", $joins);
411             $sql = "SELECT DISTINCT {$prefix}u.id
412                                FROM {user} {$prefix}u
413                                     {$joinsql}
414                               WHERE {$prefix}u.deleted = 0";
415         }
417         $wheres = array_filter($wheres);
418         if (!empty($wheres)) {
419             if ($this->filterset->get_join_type() === $this->filterset::JOINTYPE_ALL) {
420                 $wheresql = '(' . implode(') AND (', $wheres) . ')';
421             } else {
422                 $wheresql = '(' . implode(') OR (', $wheres) . ')';
423             }
425             $sql .= " AND ({$wheresql})";
426         }
428         // Prepare any SQL that must be applied.
429         if (!empty($forcedjoins)) {
430             $forcedjoinsql = implode("\n", $forcedjoins);
431             $forcedsql = "SELECT DISTINCT {$forcedprefix}u.id
432                                      FROM {user} {$forcedprefix}u
433                                           {$forcedjoinsql}
434                                     WHERE {$forcedwhere}";
435         }
437         return [
438             'sql' => $sql,
439             'forcedsql' => $forcedsql,
440             'params' => $params,
441         ];
442     }
444     /**
445      * Prepare the enrolment methods filter SQL content.
446      *
447      * @param string $useridcolumn User ID column used in the calling query, e.g. u.id
448      * @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => []].
449      */
450     protected function get_enrol_method_sql($useridcolumn): array {
451         global $DB;
453         $prefix = 'ejm_';
454         $joins  = [];
455         $where = '';
456         $params = [];
457         $enrolids = [];
459         if ($this->filterset->has_filter('enrolments')) {
460             $enrolids = $this->filterset->get_filter('enrolments')->get_filter_values();
461         }
463         if (!empty($enrolids)) {
464             $jointype = $this->filterset->get_filter('enrolments')->get_join_type();
466             // Handle 'All' join type.
467             if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL ||
468                     $jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_NONE) {
469                 $allwheres = [];
471                 foreach ($enrolids as $i => $enrolid) {
472                     $thisprefix = "{$prefix}{$i}";
473                     list($enrolidsql, $enrolidparam) = $DB->get_in_or_equal($enrolid, SQL_PARAMS_NAMED, $thisprefix);
475                     $joins[] = "LEFT JOIN {enrol} {$thisprefix}e
476                                        ON ({$thisprefix}e.id {$enrolidsql}
477                                       AND {$thisprefix}e.courseid = :{$thisprefix}courseid)";
478                     $joins[] = "LEFT JOIN {user_enrolments} {$thisprefix}ue
479                                        ON {$thisprefix}ue.userid = {$useridcolumn}
480                                       AND {$thisprefix}ue.enrolid = {$thisprefix}e.id";
482                     if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL) {
483                         $allwheres[] = "{$thisprefix}ue.id IS NOT NULL";
484                     } else {
485                         // Ensure participants do not match any of the filtered methods when joining by 'None'.
486                         $allwheres[] = "{$thisprefix}ue.id IS NULL";
487                     }
489                     $params["{$thisprefix}courseid"] = $this->course->id;
490                     $params = array_merge($params, $enrolidparam);
491                 }
493                 if (!empty($allwheres)) {
494                     $where = implode(' AND ', $allwheres);
495                 }
496             } else {
497                 // Handle the 'Any'join type.
499                 list($enrolidssql, $enrolidsparams) = $DB->get_in_or_equal($enrolids, SQL_PARAMS_NAMED, $prefix);
501                 $joins[] = "LEFT JOIN {enrol} {$prefix}e
502                                    ON ({$prefix}e.id {$enrolidssql}
503                                   AND {$prefix}e.courseid = :{$prefix}courseid)";
504                 $joins[] = "LEFT JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}
505                                                               AND {$prefix}ue.enrolid = {$prefix}e.id";
506                 $where = "{$prefix}ue.id IS NOT NULL";
508                 $params["{$prefix}courseid"] = $this->course->id;
509                 $params = array_merge($params, $enrolidsparams);
510             }
511         }
513         return [
514             'joins' => $joins,
515             'where' => $where,
516             'params' => $params,
517         ];
518     }
520     /**
521      * Prepare the status filter SQL content.
522      * Note: Users who cannot view suspended users will always have their results filtered to only show active participants.
523      *
524      * @param string $filteruidcolumn User ID column used in the calling query, e.g. eu_u.id
525      * @param string $forceduidcolumn User ID column used in any forced query, e.g. feu_u.id
526      * @param string $forcedprefix The prefix to use if forced filtering is required
527      * @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => [], 'forcestatus' => true]
528      */
529     protected function get_status_sql($filteruidcolumn, $forceduidcolumn, $forcedprefix): array {
530         $prefix = $forcedprefix;
531         $useridcolumn = $forceduidcolumn;
532         $joins  = [];
533         $where = '';
534         $params = [];
535         $forcestatus = true;
537         // By default we filter to show users with active status only.
538         $statusids = [ENROL_USER_ACTIVE];
539         $statusjointype = $this->filterset::JOINTYPE_DEFAULT;
541         // Allow optional status filtering if the user has relevant capabilities.
542         if (has_capability('moodle/course:enrolreview', $this->context) &&
543                 (has_capability('moodle/course:viewsuspendedusers', $this->context))) {
544             $forcestatus = false;
545             $prefix = 'ejs_';
546             $useridcolumn = $filteruidcolumn;
548             // Default to no filtering if capabilities allow for it.
549             $statusids = [];
551             if ($this->filterset->has_filter('status')) {
552                 $statusjointype = $this->filterset->get_filter('status')->get_join_type();
553                 $statusfiltervalues = $this->filterset->get_filter('status')->get_filter_values();
555                 // If values are set for the status filter, use them.
556                 if (!empty($statusfiltervalues)) {
557                     $statusids = $statusfiltervalues;
558                 }
559             }
560         }
562         if (!empty($statusids)) {
563             $enroljoin = 'JOIN {enrol} %1$se ON %1$se.id = %1$sue.enrolid
564                                                   AND %1$se.courseid = :%1$scourseid';
566             $whereactive = '(%1$sue.status = :%2$sactive
567                           AND %1$se.status = :%2$senabled
568                       AND %1$sue.timestart < :%2$snow1
569                        AND (%1$sue.timeend = 0
570                          OR %1$sue.timeend > :%2$snow2))';
572             $wheresuspended = '(%1$sue.status = :%2$ssuspended
573                              OR %1$se.status != :%2$senabled
574                          OR %1$sue.timestart >= :%2$snow1
575                            OR (%1$sue.timeend > 0
576                           AND %1$sue.timeend <= :%2$snow2))';
578             // Round 'now' time to help DB caching.
579             $now = round(time(), -2);
581             switch ($statusjointype) {
582                 case $this->filterset::JOINTYPE_ALL:
583                     $joinwheres = [];
585                     foreach ($statusids as $i => $statusid) {
586                         $joinprefix = "{$prefix}{$i}";
587                         $joins[] = "JOIN {user_enrolments} {$joinprefix}ue ON {$joinprefix}ue.userid = {$useridcolumn}";
589                         if ($statusid === ENROL_USER_ACTIVE) {
590                             // Conditions to be met if user filtering by active.
591                             $joinwheres[] = sprintf($whereactive, $joinprefix, $joinprefix);
593                             $activeparams = [
594                                 "{$joinprefix}active" => ENROL_USER_ACTIVE,
595                                 "{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,
596                                 "{$joinprefix}now1"   => $now,
597                                 "{$joinprefix}now2"   => $now,
598                                 "{$joinprefix}courseid"   => $this->course->id,
599                             ];
601                             $params = array_merge($params, $activeparams);
602                         } else {
603                             // Conditions to be met if filtering by suspended (currently the only other status).
604                             $joinwheres[] = sprintf($wheresuspended, $joinprefix, $joinprefix);
606                             $suspendedparams = [
607                                 "{$joinprefix}suspended" => ENROL_USER_SUSPENDED,
608                                 "{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,
609                                 "{$joinprefix}now1"   => $now,
610                                 "{$joinprefix}now2"   => $now,
611                                 "{$joinprefix}courseid"   => $this->course->id,
612                             ];
614                             $params = array_merge($params, $suspendedparams);
615                         }
617                         $joins[] = sprintf($enroljoin, $joinprefix);
618                     }
620                     $where = implode(' AND ', $joinwheres);
621                     break;
623                 case $this->filterset::JOINTYPE_NONE:
624                     // Should always be enrolled, just not in any of the filtered statuses.
625                     $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";
626                     $joins[] = sprintf($enroljoin, $prefix);
627                     $joinwheres = [];
628                     $params["{$prefix}courseid"] = $this->course->id;
630                     foreach ($statusids as $i => $statusid) {
631                         $paramprefix = "{$prefix}{$i}";
633                         if ($statusid === ENROL_USER_ACTIVE) {
634                             // Conditions to be met if user filtering by active.
635                             $joinwheres[] = sprintf("NOT {$whereactive}", $prefix, $paramprefix);
637                             $activeparams = [
638                                 "{$paramprefix}active" => ENROL_USER_ACTIVE,
639                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
640                                 "{$paramprefix}now1"   => $now,
641                                 "{$paramprefix}now2"   => $now,
642                             ];
644                             $params = array_merge($params, $activeparams);
645                         } else {
646                             // Conditions to be met if filtering by suspended (currently the only other status).
647                             $joinwheres[] = sprintf("NOT {$wheresuspended}", $prefix, $paramprefix);
649                             $suspendedparams = [
650                                 "{$paramprefix}suspended" => ENROL_USER_SUSPENDED,
651                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
652                                 "{$paramprefix}now1"   => $now,
653                                 "{$paramprefix}now2"   => $now,
654                             ];
656                             $params = array_merge($params, $suspendedparams);
657                         }
658                     }
660                     $where = '(' . implode(' AND ', $joinwheres) . ')';
661                     break;
663                 default:
664                     // Handle the 'Any' join type.
666                     $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";
667                     $joins[] = sprintf($enroljoin, $prefix);
668                     $joinwheres = [];
669                     $params["{$prefix}courseid"] = $this->course->id;
671                     foreach ($statusids as $i => $statusid) {
672                         $paramprefix = "{$prefix}{$i}";
674                         if ($statusid === ENROL_USER_ACTIVE) {
675                             // Conditions to be met if user filtering by active.
676                             $joinwheres[] = sprintf($whereactive, $prefix, $paramprefix);
678                             $activeparams = [
679                                 "{$paramprefix}active" => ENROL_USER_ACTIVE,
680                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
681                                 "{$paramprefix}now1"   => $now,
682                                 "{$paramprefix}now2"   => $now,
683                             ];
685                             $params = array_merge($params, $activeparams);
686                         } else {
687                             // Conditions to be met if filtering by suspended (currently the only other status).
688                             $joinwheres[] = sprintf($wheresuspended, $prefix, $paramprefix);
690                             $suspendedparams = [
691                                 "{$paramprefix}suspended" => ENROL_USER_SUSPENDED,
692                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
693                                 "{$paramprefix}now1"   => $now,
694                                 "{$paramprefix}now2"   => $now,
695                             ];
697                             $params = array_merge($params, $suspendedparams);
698                         }
699                     }
701                     $where = '(' . implode(' OR ', $joinwheres) . ')';
702                     break;
703             }
704         }
706         return [
707             'joins' => $joins,
708             'where' => $where,
709             'params' => $params,
710             'forcestatus' => $forcestatus,
711         ];
712     }
714     /**
715      * Fetch the groups filter's grouplib jointype, based on its filterset jointype.
716      * This mapping is to ensure compatibility between the two, should their values ever differ.
717      *
718      * @param int|null $forcedjointype If set, specifies the join type to fetch mapping for (used when applying forced filtering).
719      *                            If null, then user defined filter join type is used.
720      * @return int
721      */
722     protected function get_groups_jointype(?int $forcedjointype = null): int {
724         // If applying forced groups filter and no manual groups filtering is applied, add an empty filter so we can map the join.
725         if (!is_null($forcedjointype) && !$this->filterset->has_filter('groups')) {
726             $this->filterset->add_filter(new \core_table\local\filter\integer_filter('groups'));
727         }
729         $groupsfilter = $this->filterset->get_filter('groups');
731         if (is_null($forcedjointype)) {
732             // Fetch join type mapping for a user supplied groups filtering.
733             $filterjointype = $groupsfilter->get_join_type();
734         } else {
735             // Fetch join type mapping for forced groups filtering.
736             $filterjointype = $forcedjointype;
737         }
739         switch ($filterjointype) {
740             case $groupsfilter::JOINTYPE_NONE:
741                 $groupsjoin = GROUPS_JOIN_NONE;
742                 break;
743             case $groupsfilter::JOINTYPE_ALL:
744                 $groupsjoin = GROUPS_JOIN_ALL;
745                 break;
746             default:
747                 // Default to ANY jointype.
748                 $groupsjoin = GROUPS_JOIN_ANY;
749                 break;
750         }
752         return $groupsjoin;
753     }
755     /**
756      * Prepare SQL where clause and associated parameters for any roles filtering being performed.
757      *
758      * @return array SQL query data in the format ['where' => '', 'params' => []].
759      */
760     protected function get_roles_sql(): array {
761         global $DB;
763         $where = '';
764         $params = [];
766         // Limit list to users with some role only.
767         if ($this->filterset->has_filter('roles')) {
768             $rolesfilter = $this->filterset->get_filter('roles');
770             $roleids = $rolesfilter->get_filter_values();
771             $jointype = $rolesfilter->get_join_type();
773             // Determine how to match values in the query.
774             $matchinsql = 'IN';
775             switch ($jointype) {
776                 case $rolesfilter::JOINTYPE_ALL:
777                     $wherejoin = ' AND ';
778                     break;
779                 case $rolesfilter::JOINTYPE_NONE:
780                     $wherejoin = ' AND NOT ';
781                     $matchinsql = 'NOT IN';
782                     break;
783                 default:
784                     // Default to 'Any' jointype.
785                     $wherejoin = ' OR ';
786                     break;
787             }
789             // We want to query both the current context and parent contexts.
790             $rolecontextids = $this->context->get_parent_context_ids(true);
792             // Get users without any role, if needed.
793             if (($withoutkey = array_search(-1, $roleids)) !== false) {
794                 list($relatedctxsql1, $norolectxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
796                 if ($jointype === $rolesfilter::JOINTYPE_NONE) {
797                     $where .= "(u.id IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
798                 } else {
799                     $where .= "(u.id NOT IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
800                 }
802                 $params = array_merge($params, $norolectxparams);
804                 if ($withoutkey !== false) {
805                     unset($roleids[$withoutkey]);
806                 }
808                 // Join if any roles will be included.
809                 if (!empty($roleids)) {
810                     // The NOT case is replaced with AND to prevent a double negative.
811                     $where .= $jointype === $rolesfilter::JOINTYPE_NONE ? ' AND ' : $wherejoin;
812                 }
813             }
815             // Get users with specified roles, if needed.
816             if (!empty($roleids)) {
817                 // All case - need one WHERE per filtered role.
818                 if ($rolesfilter::JOINTYPE_ALL === $jointype) {
819                     $numroles = count($roleids);
820                     $rolecount = 1;
822                     foreach ($roleids as $roleid) {
823                         list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
824                         list($roleidssql, $roleidparams) = $DB->get_in_or_equal($roleid, SQL_PARAMS_NAMED, 'roleids');
826                         $where .= "(u.id IN (
827                                      SELECT userid
828                                        FROM {role_assignments}
829                                       WHERE roleid {$roleidssql}
830                                         AND contextid {$relatedctxsql})
831                                    )";
833                         if ($rolecount < $numroles) {
834                             $where .= $wherejoin;
835                             $rolecount++;
836                         }
838                         $params = array_merge($params, $roleidparams, $relctxparams);
839                     }
841                 } else {
842                     // Any / None cases - need one WHERE to cover all filtered roles.
843                     list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
844                     list($roleidssql, $roleidsparams) = $DB->get_in_or_equal($roleids, SQL_PARAMS_NAMED, 'roleids');
846                     $where .= "(u.id {$matchinsql} (
847                                  SELECT userid
848                                    FROM {role_assignments}
849                                   WHERE roleid {$roleidssql}
850                                     AND contextid {$relatedctxsql})
851                                )";
853                     $params = array_merge($params, $roleidsparams, $relctxparams);
854                 }
855             }
856         }
858         return [
859             'where' => $where,
860             'params' => $params,
861         ];
862     }
864     /**
865      * Prepare SQL where clause and associated parameters for any keyword searches being performed.
866      *
867      * @return array SQL query data in the format ['where' => '', 'params' => []].
868      */
869     protected function get_keywords_search_sql(): array {
870         global $CFG, $DB, $USER;
872         $keywords = [];
873         $where = '';
874         $params = [];
875         $keywordsfilter = $this->filterset->get_filter('keywords');
876         $jointype = $keywordsfilter->get_join_type();
877         // None join types in both filter row and filterset require additional 'not null' handling for accurate keywords matches.
878         $notjoin = false;
880         // Determine how to match values in the query.
881         switch ($jointype) {
882             case $keywordsfilter::JOINTYPE_ALL:
883                 $wherejoin = ' AND ';
884                 break;
885             case $keywordsfilter::JOINTYPE_NONE:
886                 $wherejoin = ' AND NOT ';
887                 $notjoin = true;
888                 break;
889             default:
890                 // Default to 'Any' jointype.
891                 $wherejoin = ' OR ';
892                 break;
893         }
895         // Handle filterset None join type.
896         if ($this->filterset->get_join_type() === $this->filterset::JOINTYPE_NONE) {
897             $notjoin = true;
898         }
900         if ($this->filterset->has_filter('keywords')) {
901             $keywords = $keywordsfilter->get_filter_values();
902         }
904         foreach ($keywords as $index => $keyword) {
905             $searchkey1 = 'search' . $index . '1';
906             $searchkey2 = 'search' . $index . '2';
907             $searchkey3 = 'search' . $index . '3';
908             $searchkey4 = 'search' . $index . '4';
909             $searchkey5 = 'search' . $index . '5';
910             $searchkey6 = 'search' . $index . '6';
911             $searchkey7 = 'search' . $index . '7';
913             $conditions = [];
914             // Search by fullname.
915             $fullname = $DB->sql_fullname('u.firstname', 'u.lastname');
916             $conditions[] = $DB->sql_like($fullname, ':' . $searchkey1, false, false);
918             // Search by email.
919             $email = $DB->sql_like('email', ':' . $searchkey2, false, false);
921             if ($notjoin) {
922                 $email = "(email IS NOT NULL AND {$email})";
923             }
925             if (!in_array('email', $this->userfields)) {
926                 $maildisplay = 'maildisplay' . $index;
927                 $userid1 = 'userid' . $index . '1';
928                 // Prevent users who hide their email address from being found by others
929                 // who aren't allowed to see hidden email addresses.
930                 $email = "(". $email ." AND (" .
931                         "u.maildisplay <> :$maildisplay " .
932                         "OR u.id = :$userid1". // Users can always find themselves.
933                         "))";
934                 $params[$maildisplay] = core_user::MAILDISPLAY_HIDE;
935                 $params[$userid1] = $USER->id;
936             }
938             $conditions[] = $email;
940             // Search by idnumber.
941             $idnumber = $DB->sql_like('idnumber', ':' . $searchkey3, false, false);
943             if ($notjoin) {
944                 $idnumber = "(idnumber IS NOT NULL AND  {$idnumber})";
945             }
947             if (!in_array('idnumber', $this->userfields)) {
948                 $userid2 = 'userid' . $index . '2';
949                 // Users who aren't allowed to see idnumbers should at most find themselves
950                 // when searching for an idnumber.
951                 $idnumber = "(". $idnumber . " AND u.id = :$userid2)";
952                 $params[$userid2] = $USER->id;
953             }
955             $conditions[] = $idnumber;
957             if (!empty($CFG->showuseridentity)) {
958                 // Search all user identify fields.
959                 $extrasearchfields = explode(',', $CFG->showuseridentity);
960                 foreach ($extrasearchfields as $extrasearchfield) {
961                     if (in_array($extrasearchfield, ['email', 'idnumber', 'country'])) {
962                         // Already covered above. Search by country not supported.
963                         continue;
964                     }
965                     $param = $searchkey3 . $extrasearchfield;
966                     $condition = $DB->sql_like($extrasearchfield, ':' . $param, false, false);
967                     $params[$param] = "%$keyword%";
969                     if ($notjoin) {
970                         $condition = "($extrasearchfield IS NOT NULL AND {$condition})";
971                     }
973                     if (!in_array($extrasearchfield, $this->userfields)) {
974                         // User cannot see this field, but allow match if their own account.
975                         $userid3 = 'userid' . $index . '3' . $extrasearchfield;
976                         $condition = "(". $condition . " AND u.id = :$userid3)";
977                         $params[$userid3] = $USER->id;
978                     }
979                     $conditions[] = $condition;
980                 }
981             }
983             // Search by middlename.
984             $middlename = $DB->sql_like('middlename', ':' . $searchkey4, false, false);
986             if ($notjoin) {
987                 $middlename = "(middlename IS NOT NULL AND {$middlename})";
988             }
990             $conditions[] = $middlename;
992             // Search by alternatename.
993             $alternatename = $DB->sql_like('alternatename', ':' . $searchkey5, false, false);
995             if ($notjoin) {
996                 $alternatename = "(alternatename IS NOT NULL AND {$alternatename})";
997             }
999             $conditions[] = $alternatename;
1001             // Search by firstnamephonetic.
1002             $firstnamephonetic = $DB->sql_like('firstnamephonetic', ':' . $searchkey6, false, false);
1004             if ($notjoin) {
1005                 $firstnamephonetic = "(firstnamephonetic IS NOT NULL AND {$firstnamephonetic})";
1006             }
1008             $conditions[] = $firstnamephonetic;
1010             // Search by lastnamephonetic.
1011             $lastnamephonetic = $DB->sql_like('lastnamephonetic', ':' . $searchkey7, false, false);
1013             if ($notjoin) {
1014                 $lastnamephonetic = "(lastnamephonetic IS NOT NULL AND {$lastnamephonetic})";
1015             }
1017             $conditions[] = $lastnamephonetic;
1019             if (!empty($where)) {
1020                 $where .= $wherejoin;
1021             } else if ($jointype === $keywordsfilter::JOINTYPE_NONE) {
1022                 // Join type 'None' requires the WHERE to begin with NOT.
1023                 $where .= ' NOT ';
1024             }
1026             $where .= "(". implode(" OR ", $conditions) .") ";
1027             $params[$searchkey1] = "%$keyword%";
1028             $params[$searchkey2] = "%$keyword%";
1029             $params[$searchkey3] = "%$keyword%";
1030             $params[$searchkey4] = "%$keyword%";
1031             $params[$searchkey5] = "%$keyword%";
1032             $params[$searchkey6] = "%$keyword%";
1033             $params[$searchkey7] = "%$keyword%";
1034         }
1036         return [
1037             'where' => $where,
1038             'params' => $params,
1039         ];
1040     }