MDL-68829 user: Update participants SQL for Oracle/MSSQL compatibility
[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 WHERE clauses.
269         if (!empty($additionalwhere)) {
270             $wheres[] = $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         $isfrontpage = ($this->context->instanceid == SITEID);
318         $prefix = 'eu_';
319         $filteruid = "{$prefix}u.id";
320         $sql = '';
321         $joins = [];
322         $wheres = [];
323         $params = [];
324         // It is possible some statements must always be included (in addition to any filtering).
325         $forcedprefix = "f{$prefix}";
326         $forceduid = "{$forcedprefix}u.id";
327         $forcedsql = '';
328         $forcedjoins = [];
329         $forcedwhere = "{$forcedprefix}u.deleted = 0";
331         if (!$isfrontpage) {
332             // Prepare any enrolment method filtering.
333             [
334                 'joins' => $methodjoins,
335                 'where' => $wheres[],
336                 'params' => $methodparams,
337             ] = $this->get_enrol_method_sql($filteruid);
339             // Prepare any status filtering.
340             [
341                 'joins' => $statusjoins,
342                 'where' => $statuswhere,
343                 'params' => $statusparams,
344                 'forcestatus' => $forcestatus,
345             ] = $this->get_status_sql($filteruid, $forceduid, $forcedprefix);
347             if ($forcestatus) {
348                 // Force filtering by active participants if user does not have capability to view suspended.
349                 $forcedjoins = array_merge($forcedjoins, $statusjoins);
350                 $statusjoins = [];
351                 $forcedwhere .= " AND ({$statuswhere})";
352             } else {
353                 $wheres[] = $statuswhere;
354             }
356             $joins = array_merge($joins, $methodjoins, $statusjoins);
357             $params = array_merge($params, $methodparams, $statusparams);
358         }
360         // Prepare any groups filtering.
361         $groupids = [];
363         if ($this->filterset->has_filter('groups')) {
364             $groupids = $this->filterset->get_filter('groups')->get_filter_values();
365         }
367         if ($groupids) {
368             $groupjoin = groups_get_members_join($groupids, $filteruid, $this->context, $this->get_groups_jointype());
369             $joins[] = $groupjoin->joins;
370             $params = array_merge($params, $groupjoin->params);
371             if (!empty($groupjoin->wheres)) {
372                 $wheres[] = $groupjoin->wheres;
373             }
374         }
376         // Combine the relevant filters and prepare the query.
377         $joins = array_filter($joins);
378         if (!empty($joins)) {
379             $joinsql = implode("\n", $joins);
381             $sql = "SELECT DISTINCT {$prefix}u.id
382                                FROM {user} {$prefix}u
383                                     {$joinsql}
384                               WHERE {$prefix}u.deleted = 0";
385         }
387         $wheres = array_filter($wheres);
388         if (!empty($wheres)) {
389             if ($this->filterset->get_join_type() === $this->filterset::JOINTYPE_ALL) {
390                 $wheresql = '(' . implode(') AND (', $wheres) . ')';
391             } else {
392                 $wheresql = '(' . implode(') OR (', $wheres) . ')';
393             }
395             $sql .= " AND ({$wheresql})";
396         }
398         // Prepare any SQL that must be applied.
399         if (!empty($forcedjoins)) {
400             $forcedjoinsql = implode("\n", $forcedjoins);
401             $forcedsql = "SELECT DISTINCT {$forcedprefix}u.id
402                                      FROM {user} {$forcedprefix}u
403                                           {$forcedjoinsql}
404                                     WHERE {$forcedwhere}";
405         }
407         return [
408             'sql' => $sql,
409             'forcedsql' => $forcedsql,
410             'params' => $params,
411         ];
412     }
414     /**
415      * Prepare the enrolment methods filter SQL content.
416      *
417      * @param string $useridcolumn User ID column used in the calling query, e.g. u.id
418      * @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => []].
419      */
420     protected function get_enrol_method_sql($useridcolumn): array {
421         global $DB;
423         $prefix = 'ejm_';
424         $joins  = [];
425         $where = '';
426         $params = [];
427         $enrolids = [];
429         if ($this->filterset->has_filter('enrolments')) {
430             $enrolids = $this->filterset->get_filter('enrolments')->get_filter_values();
431         }
433         if (!empty($enrolids)) {
434             $jointype = $this->filterset->get_filter('enrolments')->get_join_type();
436             // Handle 'All' join type.
437             if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL ||
438                     $jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_NONE) {
439                 $allwheres = [];
441                 foreach ($enrolids as $i => $enrolid) {
442                     $thisprefix = "{$prefix}{$i}";
443                     list($enrolidsql, $enrolidparam) = $DB->get_in_or_equal($enrolid, SQL_PARAMS_NAMED, $thisprefix);
445                     $joins[] = "LEFT JOIN {enrol} {$thisprefix}e
446                                        ON ({$thisprefix}e.id {$enrolidsql}
447                                       AND {$thisprefix}e.courseid = :{$thisprefix}courseid)";
448                     $joins[] = "LEFT JOIN {user_enrolments} {$thisprefix}ue
449                                        ON {$thisprefix}ue.userid = {$useridcolumn}
450                                       AND {$thisprefix}ue.enrolid = {$thisprefix}e.id";
452                     if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL) {
453                         $allwheres[] = "{$thisprefix}ue.id IS NOT NULL";
454                     } else {
455                         // Ensure participants do not match any of the filtered methods when joining by 'None'.
456                         $allwheres[] = "{$thisprefix}ue.id IS NULL";
457                     }
459                     $params["{$thisprefix}courseid"] = $this->course->id;
460                     $params = array_merge($params, $enrolidparam);
461                 }
463                 if (!empty($allwheres)) {
464                     $where = implode(' AND ', $allwheres);
465                 }
466             } else {
467                 // Handle the 'Any'join type.
469                 list($enrolidssql, $enrolidsparams) = $DB->get_in_or_equal($enrolids, SQL_PARAMS_NAMED, $prefix);
471                 $joins[] = "LEFT JOIN {enrol} {$prefix}e
472                                    ON ({$prefix}e.id {$enrolidssql}
473                                   AND {$prefix}e.courseid = :{$prefix}courseid)";
474                 $joins[] = "LEFT JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}
475                                                               AND {$prefix}ue.enrolid = {$prefix}e.id";
476                 $where = "{$prefix}ue.id IS NOT NULL";
478                 $params["{$prefix}courseid"] = $this->course->id;
479                 $params = array_merge($params, $enrolidsparams);
480             }
481         }
483         return [
484             'joins' => $joins,
485             'where' => $where,
486             'params' => $params,
487         ];
488     }
490     /**
491      * Prepare the status filter SQL content.
492      * Note: Users who cannot view suspended users will always have their results filtered to only show active participants.
493      *
494      * @param string $filteruidcolumn User ID column used in the calling query, e.g. eu_u.id
495      * @param string $forceduidcolumn User ID column used in any forced query, e.g. feu_u.id
496      * @param string $forcedprefix The prefix to use if forced filtering is required
497      * @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => [], 'forcestatus' => true]
498      */
499     protected function get_status_sql($filteruidcolumn, $forceduidcolumn, $forcedprefix): array {
500         $prefix = $forcedprefix;
501         $useridcolumn = $forceduidcolumn;
502         $joins  = [];
503         $where = '';
504         $params = [];
505         $forcestatus = true;
507         // By default we filter to show users with active status only.
508         $statusids = [ENROL_USER_ACTIVE];
509         $statusjointype = $this->filterset::JOINTYPE_DEFAULT;
511         // Allow optional status filtering if the user has relevant capabilities.
512         if (has_capability('moodle/course:enrolreview', $this->context) &&
513                 (has_capability('moodle/course:viewsuspendedusers', $this->context))) {
514             $forcestatus = false;
515             $prefix = 'ejs_';
516             $useridcolumn = $filteruidcolumn;
518             // Default to no filtering if capabilities allow for it.
519             $statusids = [];
521             if ($this->filterset->has_filter('status')) {
522                 $statusjointype = $this->filterset->get_filter('status')->get_join_type();
523                 $statusfiltervalues = $this->filterset->get_filter('status')->get_filter_values();
525                 // If values are set for the status filter, use them.
526                 if (!empty($statusfiltervalues)) {
527                     $statusids = $statusfiltervalues;
528                 }
529             }
530         }
532         if (!empty($statusids)) {
533             $enroljoin = 'JOIN {enrol} %1$se ON %1$se.id = %1$sue.enrolid
534                                                   AND %1$se.courseid = :%1$scourseid';
536             $whereactive = '(%1$sue.status = :%2$sactive
537                           AND %1$se.status = :%2$senabled
538                       AND %1$sue.timestart < :%2$snow1
539                        AND (%1$sue.timeend = 0
540                          OR %1$sue.timeend > :%2$snow2))';
542             $wheresuspended = '(%1$sue.status = :%2$ssuspended
543                              OR %1$se.status != :%2$senabled
544                          OR %1$sue.timestart >= :%2$snow1
545                            OR (%1$sue.timeend > 0
546                           AND %1$sue.timeend <= :%2$snow2))';
548             // Round 'now' time to help DB caching.
549             $now = round(time(), -2);
551             switch ($statusjointype) {
552                 case $this->filterset::JOINTYPE_ALL:
553                     $joinwheres = [];
555                     foreach ($statusids as $i => $statusid) {
556                         $joinprefix = "{$prefix}{$i}";
557                         $joins[] = "JOIN {user_enrolments} {$joinprefix}ue ON {$joinprefix}ue.userid = {$useridcolumn}";
559                         if ($statusid === ENROL_USER_ACTIVE) {
560                             // Conditions to be met if user filtering by active.
561                             $joinwheres[] = sprintf($whereactive, $joinprefix, $joinprefix);
563                             $activeparams = [
564                                 "{$joinprefix}active" => ENROL_USER_ACTIVE,
565                                 "{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,
566                                 "{$joinprefix}now1"   => $now,
567                                 "{$joinprefix}now2"   => $now,
568                                 "{$joinprefix}courseid"   => $this->course->id,
569                             ];
571                             $params = array_merge($params, $activeparams);
572                         } else {
573                             // Conditions to be met if filtering by suspended (currently the only other status).
574                             $joinwheres[] = sprintf($wheresuspended, $joinprefix, $joinprefix);
576                             $suspendedparams = [
577                                 "{$joinprefix}suspended" => ENROL_USER_SUSPENDED,
578                                 "{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,
579                                 "{$joinprefix}now1"   => $now,
580                                 "{$joinprefix}now2"   => $now,
581                                 "{$joinprefix}courseid"   => $this->course->id,
582                             ];
584                             $params = array_merge($params, $suspendedparams);
585                         }
587                         $joins[] = sprintf($enroljoin, $joinprefix);
588                     }
590                     $where = implode(' AND ', $joinwheres);
591                     break;
593                 case $this->filterset::JOINTYPE_NONE:
594                     // Should always be enrolled, just not in any of the filtered statuses.
595                     $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";
596                     $joins[] = sprintf($enroljoin, $prefix);
597                     $joinwheres = [];
598                     $params["{$prefix}courseid"] = $this->course->id;
600                     foreach ($statusids as $i => $statusid) {
601                         $paramprefix = "{$prefix}{$i}";
603                         if ($statusid === ENROL_USER_ACTIVE) {
604                             // Conditions to be met if user filtering by active.
605                             $joinwheres[] = sprintf("NOT {$whereactive}", $prefix, $paramprefix);
607                             $activeparams = [
608                                 "{$paramprefix}active" => ENROL_USER_ACTIVE,
609                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
610                                 "{$paramprefix}now1"   => $now,
611                                 "{$paramprefix}now2"   => $now,
612                             ];
614                             $params = array_merge($params, $activeparams);
615                         } else {
616                             // Conditions to be met if filtering by suspended (currently the only other status).
617                             $joinwheres[] = sprintf("NOT {$wheresuspended}", $prefix, $paramprefix);
619                             $suspendedparams = [
620                                 "{$paramprefix}suspended" => ENROL_USER_SUSPENDED,
621                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
622                                 "{$paramprefix}now1"   => $now,
623                                 "{$paramprefix}now2"   => $now,
624                             ];
626                             $params = array_merge($params, $suspendedparams);
627                         }
628                     }
630                     $where = '(' . implode(' AND ', $joinwheres) . ')';
631                     break;
633                 default:
634                     // Handle the 'Any' join type.
636                     $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";
637                     $joins[] = sprintf($enroljoin, $prefix);
638                     $joinwheres = [];
639                     $params["{$prefix}courseid"] = $this->course->id;
641                     foreach ($statusids as $i => $statusid) {
642                         $paramprefix = "{$prefix}{$i}";
644                         if ($statusid === ENROL_USER_ACTIVE) {
645                             // Conditions to be met if user filtering by active.
646                             $joinwheres[] = sprintf($whereactive, $prefix, $paramprefix);
648                             $activeparams = [
649                                 "{$paramprefix}active" => ENROL_USER_ACTIVE,
650                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
651                                 "{$paramprefix}now1"   => $now,
652                                 "{$paramprefix}now2"   => $now,
653                             ];
655                             $params = array_merge($params, $activeparams);
656                         } else {
657                             // Conditions to be met if filtering by suspended (currently the only other status).
658                             $joinwheres[] = sprintf($wheresuspended, $prefix, $paramprefix);
660                             $suspendedparams = [
661                                 "{$paramprefix}suspended" => ENROL_USER_SUSPENDED,
662                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
663                                 "{$paramprefix}now1"   => $now,
664                                 "{$paramprefix}now2"   => $now,
665                             ];
667                             $params = array_merge($params, $suspendedparams);
668                         }
669                     }
671                     $where = '(' . implode(' OR ', $joinwheres) . ')';
672                     break;
673             }
674         }
676         return [
677             'joins' => $joins,
678             'where' => $where,
679             'params' => $params,
680             'forcestatus' => $forcestatus,
681         ];
682     }
684     /**
685      * Fetch the groups filter's grouplib jointype, based on its filterset jointype.
686      * This mapping is to ensure compatibility between the two, should their values ever differ.
687      *
688      * @return int
689      */
690     protected function get_groups_jointype(): int {
691         $groupsfilter = $this->filterset->get_filter('groups');
693         switch ($groupsfilter->get_join_type()) {
694             case $groupsfilter::JOINTYPE_NONE:
695                 $groupsjoin = GROUPS_JOIN_NONE;
696                 break;
697             case $groupsfilter::JOINTYPE_ALL:
698                 $groupsjoin = GROUPS_JOIN_ALL;
699                 break;
700             default:
701                 // Default to ANY jointype.
702                 $groupsjoin = GROUPS_JOIN_ANY;
703                 break;
704         }
706         return $groupsjoin;
707     }
709     /**
710      * Prepare SQL where clause and associated parameters for any roles filtering being performed.
711      *
712      * @return array SQL query data in the format ['where' => '', 'params' => []].
713      */
714     protected function get_roles_sql(): array {
715         global $DB;
717         $where = '';
718         $params = [];
720         // Limit list to users with some role only.
721         if ($this->filterset->has_filter('roles')) {
722             $rolesfilter = $this->filterset->get_filter('roles');
724             $roleids = $rolesfilter->get_filter_values();
725             $jointype = $rolesfilter->get_join_type();
727             // Determine how to match values in the query.
728             $matchinsql = 'IN';
729             switch ($jointype) {
730                 case $rolesfilter::JOINTYPE_ALL:
731                     $wherejoin = ' AND ';
732                     break;
733                 case $rolesfilter::JOINTYPE_NONE:
734                     $wherejoin = ' AND NOT ';
735                     $matchinsql = 'NOT IN';
736                     break;
737                 default:
738                     // Default to 'Any' jointype.
739                     $wherejoin = ' OR ';
740                     break;
741             }
743             // We want to query both the current context and parent contexts.
744             $rolecontextids = $this->context->get_parent_context_ids(true);
746             // Get users without any role, if needed.
747             if (($withoutkey = array_search(-1, $roleids)) !== false) {
748                 list($relatedctxsql1, $norolectxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
750                 if ($jointype === $rolesfilter::JOINTYPE_NONE) {
751                     $where .= "(u.id IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
752                 } else {
753                     $where .= "(u.id NOT IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
754                 }
756                 $params = array_merge($params, $norolectxparams);
758                 if ($withoutkey !== false) {
759                     unset($roleids[$withoutkey]);
760                 }
762                 // Join if any roles will be included.
763                 if (!empty($roleids)) {
764                     // The NOT case is replaced with AND to prevent a double negative.
765                     $where .= $jointype === $rolesfilter::JOINTYPE_NONE ? ' AND ' : $wherejoin;
766                 }
767             }
769             // Get users with specified roles, if needed.
770             if (!empty($roleids)) {
771                 // All case - need one WHERE per filtered role.
772                 if ($rolesfilter::JOINTYPE_ALL === $jointype) {
773                     $numroles = count($roleids);
774                     $rolecount = 1;
776                     foreach ($roleids as $roleid) {
777                         list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
778                         list($roleidssql, $roleidparams) = $DB->get_in_or_equal($roleid, SQL_PARAMS_NAMED, 'roleids');
780                         $where .= "(u.id IN (
781                                      SELECT userid
782                                        FROM {role_assignments}
783                                       WHERE roleid {$roleidssql}
784                                         AND contextid {$relatedctxsql})
785                                    )";
787                         if ($rolecount < $numroles) {
788                             $where .= $wherejoin;
789                             $rolecount++;
790                         }
792                         $params = array_merge($params, $roleidparams, $relctxparams);
793                     }
795                 } else {
796                     // Any / None cases - need one WHERE to cover all filtered roles.
797                     list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
798                     list($roleidssql, $roleidsparams) = $DB->get_in_or_equal($roleids, SQL_PARAMS_NAMED, 'roleids');
800                     $where .= "(u.id {$matchinsql} (
801                                  SELECT userid
802                                    FROM {role_assignments}
803                                   WHERE roleid {$roleidssql}
804                                     AND contextid {$relatedctxsql})
805                                )";
807                     $params = array_merge($params, $roleidsparams, $relctxparams);
808                 }
809             }
810         }
812         return [
813             'where' => $where,
814             'params' => $params,
815         ];
816     }
818     /**
819      * Prepare SQL where clause and associated parameters for any keyword searches being performed.
820      *
821      * @return array SQL query data in the format ['where' => '', 'params' => []].
822      */
823     protected function get_keywords_search_sql(): array {
824         global $CFG, $DB, $USER;
826         $keywords = [];
827         $where = '';
828         $params = [];
829         $keywordsfilter = $this->filterset->get_filter('keywords');
830         $jointype = $keywordsfilter->get_join_type();
831         $notjoin = false;
833         // Determine how to match values in the query.
834         switch ($jointype) {
835             case $keywordsfilter::JOINTYPE_ALL:
836                 $wherejoin = ' AND ';
837                 break;
838             case $keywordsfilter::JOINTYPE_NONE:
839                 $wherejoin = ' AND NOT ';
840                 $notjoin = true;
841                 break;
842             default:
843                 // Default to 'Any' jointype.
844                 $wherejoin = ' OR ';
845                 break;
846         }
848         if ($this->filterset->has_filter('keywords')) {
849             $keywords = $keywordsfilter->get_filter_values();
850         }
852         foreach ($keywords as $index => $keyword) {
853             $searchkey1 = 'search' . $index . '1';
854             $searchkey2 = 'search' . $index . '2';
855             $searchkey3 = 'search' . $index . '3';
856             $searchkey4 = 'search' . $index . '4';
857             $searchkey5 = 'search' . $index . '5';
858             $searchkey6 = 'search' . $index . '6';
859             $searchkey7 = 'search' . $index . '7';
861             $conditions = [];
862             // Search by fullname.
863             $fullname = $DB->sql_fullname('u.firstname', 'u.lastname');
864             $conditions[] = $DB->sql_like($fullname, ':' . $searchkey1, false, false);
866             // Search by email.
867             $email = $DB->sql_like('email', ':' . $searchkey2, false, false);
869             if ($notjoin) {
870                 $email = "(email IS NOT NULL AND {$email})";
871             }
873             if (!in_array('email', $this->userfields)) {
874                 $maildisplay = 'maildisplay' . $index;
875                 $userid1 = 'userid' . $index . '1';
876                 // Prevent users who hide their email address from being found by others
877                 // who aren't allowed to see hidden email addresses.
878                 $email = "(". $email ." AND (" .
879                         "u.maildisplay <> :$maildisplay " .
880                         "OR u.id = :$userid1". // Users can always find themselves.
881                         "))";
882                 $params[$maildisplay] = core_user::MAILDISPLAY_HIDE;
883                 $params[$userid1] = $USER->id;
884             }
886             $conditions[] = $email;
888             // Search by idnumber.
889             $idnumber = $DB->sql_like('idnumber', ':' . $searchkey3, false, false);
891             if ($notjoin) {
892                 $idnumber = "(idnumber IS NOT NULL AND  {$idnumber})";
893             }
895             if (!in_array('idnumber', $this->userfields)) {
896                 $userid2 = 'userid' . $index . '2';
897                 // Users who aren't allowed to see idnumbers should at most find themselves
898                 // when searching for an idnumber.
899                 $idnumber = "(". $idnumber . " AND u.id = :$userid2)";
900                 $params[$userid2] = $USER->id;
901             }
903             $conditions[] = $idnumber;
905             if (!empty($CFG->showuseridentity)) {
906                 // Search all user identify fields.
907                 $extrasearchfields = explode(',', $CFG->showuseridentity);
908                 foreach ($extrasearchfields as $extrasearchfield) {
909                     if (in_array($extrasearchfield, ['email', 'idnumber', 'country'])) {
910                         // Already covered above. Search by country not supported.
911                         continue;
912                     }
913                     $param = $searchkey3 . $extrasearchfield;
914                     $condition = $DB->sql_like($extrasearchfield, ':' . $param, false, false);
915                     $params[$param] = "%$keyword%";
917                     if ($notjoin) {
918                         $condition = "($extrasearchfield IS NOT NULL AND {$condition})";
919                     }
921                     if (!in_array($extrasearchfield, $this->userfields)) {
922                         // User cannot see this field, but allow match if their own account.
923                         $userid3 = 'userid' . $index . '3' . $extrasearchfield;
924                         $condition = "(". $condition . " AND u.id = :$userid3)";
925                         $params[$userid3] = $USER->id;
926                     }
927                     $conditions[] = $condition;
928                 }
929             }
931             // Search by middlename.
932             $middlename = $DB->sql_like('middlename', ':' . $searchkey4, false, false);
934             if ($notjoin) {
935                 $middlename = "(middlename IS NOT NULL AND {$middlename})";
936             }
938             $conditions[] = $middlename;
940             // Search by alternatename.
941             $alternatename = $DB->sql_like('alternatename', ':' . $searchkey5, false, false);
943             if ($notjoin) {
944                 $alternatename = "(alternatename IS NOT NULL AND {$alternatename})";
945             }
947             $conditions[] = $alternatename;
949             // Search by firstnamephonetic.
950             $firstnamephonetic = $DB->sql_like('firstnamephonetic', ':' . $searchkey6, false, false);
952             if ($notjoin) {
953                 $firstnamephonetic = "(firstnamephonetic IS NOT NULL AND {$firstnamephonetic})";
954             }
956             $conditions[] = $firstnamephonetic;
958             // Search by lastnamephonetic.
959             $lastnamephonetic = $DB->sql_like('lastnamephonetic', ':' . $searchkey7, false, false);
961             if ($notjoin) {
962                 $lastnamephonetic = "(lastnamephonetic IS NOT NULL AND {$lastnamephonetic})";
963             }
965             $conditions[] = $lastnamephonetic;
967             if (!empty($where)) {
968                 $where .= $wherejoin;
969             } else if ($jointype === $keywordsfilter::JOINTYPE_NONE) {
970                 // Join type 'None' requires the WHERE to begin with NOT.
971                 $where .= ' NOT ';
972             }
974             $where .= "(". implode(" OR ", $conditions) .") ";
975             $params[$searchkey1] = "%$keyword%";
976             $params[$searchkey2] = "%$keyword%";
977             $params[$searchkey3] = "%$keyword%";
978             $params[$searchkey4] = "%$keyword%";
979             $params[$searchkey5] = "%$keyword%";
980             $params[$searchkey6] = "%$keyword%";
981             $params[$searchkey7] = "%$keyword%";
982         }
984         return [
985             'where' => $where,
986             'params' => $params,
987         ];
988     }