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/>.
  16
  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  */
  24
  25 namespace core_user\table;
  26
  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;
  34
  35 defined('MOODLE_INTERNAL') || die;
  36
  37 require_once($CFG->dirroot . '/user/lib.php');
  38
  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 {
  47
  48     /**
  49      * @var filterset $filterset The filterset describing which participants to include in the search.
  50      */
  51     protected $filterset;
  52
  53     /**
  54      * @var stdClass $course The course being searched.
  55      */
  56     protected $course;
  57
  58     /**
  59      * @var context_course $context The course context being searched.
  60      */
  61     protected $context;
  62
  63     /**
  64      * @var string[] $userfields Names of any extra user fields to be shown when listing users.
  65      */
  66     protected $userfields;
  67
  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;
  79
  80         $this->userfields = get_extra_user_fields($this->context);
  81     }
  82
  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;
  96
  97         [
  98             'select' => $select,
  99             'from' => $from,
 100             'where' => $where,
 101             'params' => $params,
 102             'groupby' => $groupby,
 103         ] = $this->get_participants_sql($additionalwhere, $additionalparams);
 104
 105         return $DB->get_recordset_sql("{$select} {$from} {$where} {$groupby} {$sort}", $params, $limitfrom, $limitnum);
 106     }
 107
 108     /**
 109      * Returns the total number of participants for a given course.
 110      *
 111      * @param string $additionalwhere Any additional SQL to add to where.
 112      * @param array $additionalparams The additional params used by $additionalwhere.
 113      * @return int
 114      */
 115     public function get_total_participants_count(string $additionalwhere = '', array $additionalparams = []): int {
 116         global $DB;
 117
 118         [
 119             'from' => $from,
 120             'where' => $where,
 121             'params' => $params,
 122         ] = $this->get_participants_sql($additionalwhere, $additionalparams);
 123
 124         return $DB->count_records_sql("SELECT COUNT(DISTINCT(u.id)) {$from} {$where}", $params);
 125     }
 126
 127     /**
 128      * Generate the SQL used to fetch filtered data for the participants table.
 129      *
 130      * @param string $additionalwhere Any additional SQL to add to where
 131      * @param array $additionalparams The additional params
 132      * @return array
 133      */
 134     protected function get_participants_sql(string $additionalwhere, array $additionalparams): array {
 135         $isfrontpage = ($this->course->id == SITEID);
 136         $accesssince = 0;
 137         // Whether to match on users who HAVE accessed since the given time (ie false is 'inactive for more than x').
 138         $matchaccesssince = false;
 139
 140         if ($this->filterset->has_filter('accesssince')) {
 141             $accesssince = $this->filterset->get_filter('accesssince')->current();
 142
 143             // Last access filtering only supports matching or not matching, not any/all/none.
 144             $jointypenone = $this->filterset->get_filter('accesssince')::JOINTYPE_NONE;
 145             if ($this->filterset->get_filter('accesssince')->get_join_type() === $jointypenone) {
 146                 $matchaccesssince = true;
 147             }
 148         }
 149
 150         [
 151             // SQL that forms part of the filter.
 152             'sql' => $esql,
 153             // SQL for enrolment filtering that must always be applied (eg due to capability restrictions).
 154             'forcedsql' => $esqlforced,
 155             'params' => $params,
 156         ] = $this->get_enrolled_sql();
 157
 158         $joins = ['FROM {user} u'];
 159         $wheres = [];
 160         // Set where statement(s) that must always be included (outside of filter wheres).
 161         $forcedwhere = "u.deleted = 0";
 162
 163         $userfieldssql = user_picture::fields('u', $this->userfields);
 164
 165         // Include any compulsory enrolment SQL (eg capability related filtering that must be applied).
 166         if (!empty($esqlforced)) {
 167             $joins[] = "JOIN ({$esqlforced}) fef ON fef.id = u.id";
 168         }
 169
 170         // Include any enrolment related filtering.
 171         if (!empty($esql)) {
 172             $joins[] = "LEFT JOIN ({$esql}) ef ON ef.id = u.id";
 173             $wheres[] = 'ef.id IS NOT NULL';
 174         }
 175
 176         if ($isfrontpage) {
 177             $select = "SELECT {$userfieldssql}, u.lastaccess";
 178             if ($accesssince) {
 179                 $wheres[] = user_get_user_lastaccess_sql($accesssince, 'u', $matchaccesssince);
 180             }
 181             $groupby = ' GROUP BY u.id, u.lastaccess, ctx.id';
 182         } else {
 183             $select = "SELECT {$userfieldssql}, COALESCE(ul.timeaccess, 0) AS lastaccess";
 184             // Not everybody has accessed the course yet.
 185             $joins[] = 'LEFT JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = :courseid2)';
 186             $params['courseid2'] = $this->course->id;
 187             if ($accesssince) {
 188                 $wheres[] = user_get_course_lastaccess_sql($accesssince, 'ul', $matchaccesssince);
 189             }
 190
 191             // Make sure we only ever fetch users in the course (regardless of enrolment filters).
 192             $joins[] = 'JOIN {user_enrolments} ue ON ue.userid = u.id';
 193             $joins[] = 'JOIN {enrol} e ON e.id = ue.enrolid
 194                                       AND e.courseid = :courseid1';
 195             $params['courseid1'] = $this->course->id;
 196             $groupby = ' GROUP BY u.id, ul.timeaccess, ctx.id';
 197         }
 198
 199         // Performance hacks - we preload user contexts together with accounts.
 200         $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');
 201         $ccjoin = 'LEFT JOIN {context} ctx ON (ctx.instanceid = u.id AND ctx.contextlevel = :contextlevel)';
 202         $params['contextlevel'] = CONTEXT_USER;
 203         $select .= $ccselect;
 204         $joins[] = $ccjoin;
 205
 206         // Apply any role filtering.
 207         if ($this->filterset->has_filter('roles')) {
 208             [
 209                 'where' => $roleswhere,
 210                 'params' => $rolesparams,
 211             ] = $this->get_roles_sql();
 212
 213             if (!empty($roleswhere)) {
 214                 $wheres[] = "({$roleswhere})";
 215             }
 216
 217             if (!empty($rolesparams)) {
 218                 $params = array_merge($params, $rolesparams);
 219             }
 220         }
 221
 222         // Apply any keyword text searches.
 223         if ($this->filterset->has_filter('keywords')) {
 224             [
 225                 'where' => $keywordswhere,
 226                 'params' => $keywordsparams,
 227             ] = $this->get_keywords_search_sql();
 228
 229             if (!empty($keywordswhere)) {
 230                 $wheres[] = $keywordswhere;
 231             }
 232
 233             if (!empty($keywordsparams)) {
 234                 $params = array_merge($params, $keywordsparams);
 235             }
 236         }
 237
 238         // Add any supplied additional WHERE clauses.
 239         if (!empty($additionalwhere)) {
 240             $wheres[] = $additionalwhere;
 241             $params = array_merge($params, $additionalparams);
 242         }
 243
 244         // Prepare final values.
 245         $from = implode("\n", $joins);
 246         if ($wheres) {
 247             switch ($this->filterset->get_join_type()) {
 248                 case $this->filterset::JOINTYPE_ALL:
 249                     $firstjoin = ' AND ';
 250                     $wheresjoin = ' AND ';
 251                     break;
 252                 case $this->filterset::JOINTYPE_NONE:
 253                     $firstjoin = ' AND NOT ';
 254                     $wheresjoin = ' AND NOT ';
 255                     break;
 256                 default:
 257                     // Default to 'Any' jointype.
 258                     $firstjoin = ' AND ';
 259                     $wheresjoin = ' OR ';
 260                     break;
 261             }
 262
 263             $where = "WHERE ({$forcedwhere}) {$firstjoin}" . implode($wheresjoin, $wheres);
 264         } else {
 265             $where = '';
 266         }
 267
 268         return [
 269             'select' => $select,
 270             'from' => $from,
 271             'where' => $where,
 272             'params' => $params,
 273             'groupby' => $groupby,
 274         ];
 275     }
 276
 277     /**
 278      * Prepare SQL and associated parameters for users enrolled in the course.
 279      *
 280      * @return array SQL query data in the format ['sql' => '', 'forcedsql' => '', 'params' => []].
 281      */
 282     protected function get_enrolled_sql(): array {
 283         $isfrontpage = ($this->context->instanceid == SITEID);
 284         $prefix = 'eu_';
 285         $filteruid = "{$prefix}u.id";
 286         $sql = '';
 287         $joins = [];
 288         $wheres = [];
 289         $params = [];
 290         // It is possible some statements must always be included (in addition to any filtering).
 291         $forcedprefix = "f{$prefix}";
 292         $forceduid = "{$forcedprefix}u.id";
 293         $forcedsql = '';
 294         $forcedjoins = [];
 295         $forcedwhere = "{$forcedprefix}u.deleted = 0";
 296
 297         if (!$isfrontpage) {
 298             // Prepare any enrolment method filtering.
 299             [
 300                 'joins' => $methodjoins,
 301                 'where' => $wheres[],
 302                 'params' => $methodparams,
 303             ] = $this->get_enrol_method_sql($filteruid);
 304
 305             // Prepare any status filtering.
 306             [
 307                 'joins' => $statusjoins,
 308                 'where' => $statuswhere,
 309                 'params' => $statusparams,
 310                 'forcestatus' => $forcestatus,
 311             ] = $this->get_status_sql($filteruid, $forceduid, $forcedprefix);
 312
 313             if ($forcestatus) {
 314                 // Force filtering by active participants if user does not have capability to view suspended.
 315                 $forcedjoins = array_merge($forcedjoins, $statusjoins);
 316                 $statusjoins = [];
 317                 $forcedwhere .= " AND ({$statuswhere})";
 318             } else {
 319                 $wheres[] = $statuswhere;
 320             }
 321
 322             $joins = array_merge($joins, $methodjoins, $statusjoins);
 323             $params = array_merge($params, $methodparams, $statusparams);
 324         }
 325
 326         // Prepare any groups filtering.
 327         $groupids = [];
 328
 329         if ($this->filterset->has_filter('groups')) {
 330             $groupids = $this->filterset->get_filter('groups')->get_filter_values();
 331         }
 332
 333         if ($groupids) {
 334             $groupjoin = groups_get_members_join($groupids, $filteruid, $this->context, $this->get_groups_jointype());
 335             $joins[] = $groupjoin->joins;
 336             $params = array_merge($params, $groupjoin->params);
 337             if (!empty($groupjoin->wheres)) {
 338                 $wheres[] = $groupjoin->wheres;
 339             }
 340         }
 341
 342         // Combine the relevant filters and prepare the query.
 343         $joins = array_filter($joins);
 344         if (!empty($joins)) {
 345             $joinsql = implode("\n", $joins);
 346
 347             $sql = "SELECT DISTINCT {$prefix}u.id
 348                                FROM {user} {$prefix}u
 349                                     {$joinsql}
 350                               WHERE {$prefix}u.deleted = 0";
 351         }
 352
 353         $wheres = array_filter($wheres);
 354         if (!empty($wheres)) {
 355             if ($this->filterset->get_join_type() === $this->filterset::JOINTYPE_ALL) {
 356                 $wheresql = '(' . implode(') AND (', $wheres) . ')';
 357             } else {
 358                 $wheresql = '(' . implode(') OR (', $wheres) . ')';
 359             }
 360
 361             $sql .= " AND ({$wheresql})";
 362         }
 363
 364         // Prepare any SQL that must be applied.
 365         if (!empty($forcedjoins)) {
 366             $forcedjoinsql = implode("\n", $forcedjoins);
 367             $forcedsql = "SELECT DISTINCT {$forcedprefix}u.id
 368                                      FROM {user} {$forcedprefix}u
 369                                           {$forcedjoinsql}
 370                                     WHERE {$forcedwhere}";
 371         }
 372
 373         return [
 374             'sql' => $sql,
 375             'forcedsql' => $forcedsql,
 376             'params' => $params,
 377         ];
 378     }
 379
 380     /**
 381      * Prepare the enrolment methods filter SQL content.
 382      *
 383      * @param string $useridcolumn User ID column used in the calling query, e.g. u.id
 384      * @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => []].
 385      */
 386     protected function get_enrol_method_sql($useridcolumn): array {
 387         global $DB;
 388
 389         $prefix = 'ejm_';
 390         $joins  = [];
 391         $where = '';
 392         $params = [];
 393         $enrolids = [];
 394
 395         if ($this->filterset->has_filter('enrolments')) {
 396             $enrolids = $this->filterset->get_filter('enrolments')->get_filter_values();
 397         }
 398
 399         if (!empty($enrolids)) {
 400             $jointype = $this->filterset->get_filter('enrolments')->get_join_type();
 401
 402             // Handle 'All' join type.
 403             if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL ||
 404                     $jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_NONE) {
 405                 $allwheres = [];
 406
 407                 foreach ($enrolids as $i => $enrolid) {
 408                     $thisprefix = "{$prefix}{$i}";
 409                     list($enrolidsql, $enrolidparam) = $DB->get_in_or_equal($enrolid, SQL_PARAMS_NAMED, $thisprefix);
 410
 411                     $joins[] = "LEFT JOIN {enrol} {$thisprefix}e
 412                                        ON ({$thisprefix}e.id {$enrolidsql}
 413                                       AND {$thisprefix}e.courseid = :{$thisprefix}courseid)";
 414                     $joins[] = "LEFT JOIN {user_enrolments} {$thisprefix}ue
 415                                        ON {$thisprefix}ue.userid = {$useridcolumn}
 416                                       AND {$thisprefix}ue.enrolid = {$thisprefix}e.id";
 417
 418                     if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL) {
 419                         $allwheres[] = "{$thisprefix}ue.id IS NOT NULL";
 420                     } else {
 421                         // Ensure participants do not match any of the filtered methods when joining by 'None'.
 422                         $allwheres[] = "{$thisprefix}ue.id IS NULL";
 423                     }
 424
 425                     $params["{$thisprefix}courseid"] = $this->course->id;
 426                     $params = array_merge($params, $enrolidparam);
 427                 }
 428
 429                 if (!empty($allwheres)) {
 430                     $where = implode(' AND ', $allwheres);
 431                 }
 432             } else {
 433                 // Handle the 'Any'join type.
 434
 435                 list($enrolidssql, $enrolidsparams) = $DB->get_in_or_equal($enrolids, SQL_PARAMS_NAMED, $prefix);
 436
 437                 $joins[] = "LEFT JOIN {enrol} {$prefix}e
 438                                    ON ({$prefix}e.id {$enrolidssql}
 439                                   AND {$prefix}e.courseid = :{$prefix}courseid)";
 440                 $joins[] = "LEFT JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}
 441                                                               AND {$prefix}ue.enrolid = {$prefix}e.id";
 442                 $where = "{$prefix}ue.id IS NOT NULL";
 443
 444                 $params["{$prefix}courseid"] = $this->course->id;
 445                 $params = array_merge($params, $enrolidsparams);
 446             }
 447         }
 448
 449         return [
 450             'joins' => $joins,
 451             'where' => $where,
 452             'params' => $params,
 453         ];
 454     }
 455
 456     /**
 457      * Prepare the status filter SQL content.
 458      * Note: Users who cannot view suspended users will always have their results filtered to only show active participants.
 459      *
 460      * @param string $filteruidcolumn User ID column used in the calling query, e.g. eu_u.id
 461      * @param string $forceduidcolumn User ID column used in any forced query, e.g. feu_u.id
 462      * @param string $forcedprefix The prefix to use if forced filtering is required
 463      * @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => [], 'forcestatus' => true]
 464      */
 465     protected function get_status_sql($filteruidcolumn, $forceduidcolumn, $forcedprefix): array {
 466         $prefix = $forcedprefix;
 467         $useridcolumn = $forceduidcolumn;
 468         $joins  = [];
 469         $where = '';
 470         $params = [];
 471         $forcestatus = true;
 472
 473         // By default we filter to show users with active status only.
 474         $statusids = [ENROL_USER_ACTIVE];
 475         $statusjointype = $this->filterset::JOINTYPE_DEFAULT;
 476
 477         // Allow optional status filtering if the user has relevant capabilities.
 478         if (has_capability('moodle/course:enrolreview', $this->context) &&
 479                 (has_capability('moodle/course:viewsuspendedusers', $this->context))) {
 480             $forcestatus = false;
 481             $prefix = 'ejs_';
 482             $useridcolumn = $filteruidcolumn;
 483
 484             // Default to no filtering if capabilities allow for it.
 485             $statusids = [];
 486
 487             if ($this->filterset->has_filter('status')) {
 488                 $statusjointype = $this->filterset->get_filter('status')->get_join_type();
 489                 $statusfiltervalues = $this->filterset->get_filter('status')->get_filter_values();
 490
 491                 // If values are set for the status filter, use them.
 492                 if (!empty($statusfiltervalues)) {
 493                     $statusids = $statusfiltervalues;
 494                 }
 495             }
 496         }
 497
 498         if (!empty($statusids)) {
 499             $enroljoin = 'JOIN {enrol} %1$se ON %1$se.id = %1$sue.enrolid
 500                                                   AND %1$se.courseid = :%1$scourseid';
 501
 502             $whereactive = '(%1$sue.status = :%2$sactive
 503                           AND %1$se.status = :%2$senabled
 504                       AND %1$sue.timestart < :%2$snow1
 505                        AND (%1$sue.timeend = 0
 506                          OR %1$sue.timeend > :%2$snow2))';
 507
 508             $wheresuspended = '(%1$sue.status = :%2$ssuspended
 509                              OR %1$se.status != :%2$senabled
 510                          OR %1$sue.timestart >= :%2$snow1
 511                            OR (%1$sue.timeend > 0
 512                           AND %1$sue.timeend <= :%2$snow2))';
 513
 514             // Round 'now' time to help DB caching.
 515             $now = round(time(), -2);
 516
 517             switch ($statusjointype) {
 518                 case $this->filterset::JOINTYPE_ALL:
 519                     $joinwheres = [];
 520
 521                     foreach ($statusids as $i => $statusid) {
 522                         $joinprefix = "{$prefix}{$i}";
 523                         $joins[] = "JOIN {user_enrolments} {$joinprefix}ue ON {$joinprefix}ue.userid = {$useridcolumn}";
 524
 525                         if ($statusid === ENROL_USER_ACTIVE) {
 526                             // Conditions to be met if user filtering by active.
 527                             $joinwheres[] = sprintf($whereactive, $joinprefix, $joinprefix);
 528
 529                             $activeparams = [
 530                                 "{$joinprefix}active" => ENROL_USER_ACTIVE,
 531                                 "{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,
 532                                 "{$joinprefix}now1"   => $now,
 533                                 "{$joinprefix}now2"   => $now,
 534                                 "{$joinprefix}courseid"   => $this->course->id,
 535                             ];
 536
 537                             $params = array_merge($params, $activeparams);
 538                         } else {
 539                             // Conditions to be met if filtering by suspended (currently the only other status).
 540                             $joinwheres[] = sprintf($wheresuspended, $joinprefix, $joinprefix);
 541
 542                             $suspendedparams = [
 543                                 "{$joinprefix}suspended" => ENROL_USER_SUSPENDED,
 544                                 "{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,
 545                                 "{$joinprefix}now1"   => $now,
 546                                 "{$joinprefix}now2"   => $now,
 547                                 "{$joinprefix}courseid"   => $this->course->id,
 548                             ];
 549
 550                             $params = array_merge($params, $suspendedparams);
 551                         }
 552
 553                         $joins[] = sprintf($enroljoin, $joinprefix);
 554                     }
 555
 556                     $where = implode(' AND ', $joinwheres);
 557                     break;
 558
 559                 case $this->filterset::JOINTYPE_NONE:
 560                     // Should always be enrolled, just not in any of the filtered statuses.
 561                     $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";
 562                     $joins[] = sprintf($enroljoin, $prefix);
 563                     $joinwheres = [];
 564                     $params["{$prefix}courseid"] = $this->course->id;
 565
 566                     foreach ($statusids as $i => $statusid) {
 567                         $paramprefix = "{$prefix}{$i}";
 568
 569                         if ($statusid === ENROL_USER_ACTIVE) {
 570                             // Conditions to be met if user filtering by active.
 571                             $joinwheres[] = sprintf("NOT {$whereactive}", $prefix, $paramprefix);
 572
 573                             $activeparams = [
 574                                 "{$paramprefix}active" => ENROL_USER_ACTIVE,
 575                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
 576                                 "{$paramprefix}now1"   => $now,
 577                                 "{$paramprefix}now2"   => $now,
 578                             ];
 579
 580                             $params = array_merge($params, $activeparams);
 581                         } else {
 582                             // Conditions to be met if filtering by suspended (currently the only other status).
 583                             $joinwheres[] = sprintf("NOT {$wheresuspended}", $prefix, $paramprefix);
 584
 585                             $suspendedparams = [
 586                                 "{$paramprefix}suspended" => ENROL_USER_SUSPENDED,
 587                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
 588                                 "{$paramprefix}now1"   => $now,
 589                                 "{$paramprefix}now2"   => $now,
 590                             ];
 591
 592                             $params = array_merge($params, $suspendedparams);
 593                         }
 594                     }
 595
 596                     $where = '(' . implode(' AND ', $joinwheres) . ')';
 597                     break;
 598
 599                 default:
 600                     // Handle the 'Any' join type.
 601
 602                     $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";
 603                     $joins[] = sprintf($enroljoin, $prefix);
 604                     $joinwheres = [];
 605                     $params["{$prefix}courseid"] = $this->course->id;
 606
 607                     foreach ($statusids as $i => $statusid) {
 608                         $paramprefix = "{$prefix}{$i}";
 609
 610                         if ($statusid === ENROL_USER_ACTIVE) {
 611                             // Conditions to be met if user filtering by active.
 612                             $joinwheres[] = sprintf($whereactive, $prefix, $paramprefix);
 613
 614                             $activeparams = [
 615                                 "{$paramprefix}active" => ENROL_USER_ACTIVE,
 616                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
 617                                 "{$paramprefix}now1"   => $now,
 618                                 "{$paramprefix}now2"   => $now,
 619                             ];
 620
 621                             $params = array_merge($params, $activeparams);
 622                         } else {
 623                             // Conditions to be met if filtering by suspended (currently the only other status).
 624                             $joinwheres[] = sprintf($wheresuspended, $prefix, $paramprefix);
 625
 626                             $suspendedparams = [
 627                                 "{$paramprefix}suspended" => ENROL_USER_SUSPENDED,
 628                                 "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
 629                                 "{$paramprefix}now1"   => $now,
 630                                 "{$paramprefix}now2"   => $now,
 631                             ];
 632
 633                             $params = array_merge($params, $suspendedparams);
 634                         }
 635                     }
 636
 637                     $where = '(' . implode(' OR ', $joinwheres) . ')';
 638                     break;
 639             }
 640         }
 641
 642         return [
 643             'joins' => $joins,
 644             'where' => $where,
 645             'params' => $params,
 646             'forcestatus' => $forcestatus,
 647         ];
 648     }
 649
 650     /**
 651      * Fetch the groups filter's grouplib jointype, based on its filterset jointype.
 652      * This mapping is to ensure compatibility between the two, should their values ever differ.
 653      *
 654      * @return int
 655      */
 656     protected function get_groups_jointype(): int {
 657         $groupsfilter = $this->filterset->get_filter('groups');
 658
 659         switch ($groupsfilter->get_join_type()) {
 660             case $groupsfilter::JOINTYPE_NONE:
 661                 $groupsjoin = GROUPS_JOIN_NONE;
 662                 break;
 663             case $groupsfilter::JOINTYPE_ALL:
 664                 $groupsjoin = GROUPS_JOIN_ALL;
 665                 break;
 666             default:
 667                 // Default to ANY jointype.
 668                 $groupsjoin = GROUPS_JOIN_ANY;
 669                 break;
 670         }
 671
 672         return $groupsjoin;
 673     }
 674
 675     /**
 676      * Prepare SQL where clause and associated parameters for any roles filtering being performed.
 677      *
 678      * @return array SQL query data in the format ['where' => '', 'params' => []].
 679      */
 680     protected function get_roles_sql(): array {
 681         global $DB;
 682
 683         $where = '';
 684         $params = [];
 685
 686         // Limit list to users with some role only.
 687         if ($this->filterset->has_filter('roles')) {
 688             $rolesfilter = $this->filterset->get_filter('roles');
 689
 690             $roleids = $rolesfilter->get_filter_values();
 691             $jointype = $rolesfilter->get_join_type();
 692
 693             // Determine how to match values in the query.
 694             $matchinsql = 'IN';
 695             switch ($jointype) {
 696                 case $rolesfilter::JOINTYPE_ALL:
 697                     $wherejoin = ' AND ';
 698                     break;
 699                 case $rolesfilter::JOINTYPE_NONE:
 700                     $wherejoin = ' AND NOT ';
 701                     $matchinsql = 'NOT IN';
 702                     break;
 703                 default:
 704                     // Default to 'Any' jointype.
 705                     $wherejoin = ' OR ';
 706                     break;
 707             }
 708
 709             // We want to query both the current context and parent contexts.
 710             $rolecontextids = $this->context->get_parent_context_ids(true);
 711
 712             // Get users without any role, if needed.
 713             if (($withoutkey = array_search(-1, $roleids)) !== false) {
 714                 list($relatedctxsql1, $norolectxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
 715
 716                 if ($jointype === $rolesfilter::JOINTYPE_NONE) {
 717                     $where .= "(u.id IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
 718                 } else {
 719                     $where .= "(u.id NOT IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
 720                 }
 721
 722                 $params = array_merge($params, $norolectxparams);
 723
 724                 if ($withoutkey !== false) {
 725                     unset($roleids[$withoutkey]);
 726                 }
 727
 728                 // Join if any roles will be included.
 729                 if (!empty($roleids)) {
 730                     // The NOT case is replaced with AND to prevent a double negative.
 731                     $where .= $jointype === $rolesfilter::JOINTYPE_NONE ? ' AND ' : $wherejoin;
 732                 }
 733             }
 734
 735             // Get users with specified roles, if needed.
 736             if (!empty($roleids)) {
 737                 // All case - need one WHERE per filtered role.
 738                 if ($rolesfilter::JOINTYPE_ALL === $jointype) {
 739                     $numroles = count($roleids);
 740                     $rolecount = 1;
 741
 742                     foreach ($roleids as $roleid) {
 743                         list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
 744                         list($roleidssql, $roleidparams) = $DB->get_in_or_equal($roleid, SQL_PARAMS_NAMED, 'roleids');
 745
 746                         $where .= "(u.id IN (
 747                                      SELECT userid
 748                                        FROM {role_assignments}
 749                                       WHERE roleid {$roleidssql}
 750                                         AND contextid {$relatedctxsql})
 751                                    )";
 752
 753                         if ($rolecount < $numroles) {
 754                             $where .= $wherejoin;
 755                             $rolecount++;
 756                         }
 757
 758                         $params = array_merge($params, $roleidparams, $relctxparams);
 759                     }
 760
 761                 } else {
 762                     // Any / None cases - need one WHERE to cover all filtered roles.
 763                     list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
 764                     list($roleidssql, $roleidsparams) = $DB->get_in_or_equal($roleids, SQL_PARAMS_NAMED, 'roleids');
 765
 766                     $where .= "(u.id {$matchinsql} (
 767                                  SELECT userid
 768                                    FROM {role_assignments}
 769                                   WHERE roleid {$roleidssql}
 770                                     AND contextid {$relatedctxsql})
 771                                )";
 772
 773                     $params = array_merge($params, $roleidsparams, $relctxparams);
 774                 }
 775             }
 776         }
 777
 778         return [
 779             'where' => $where,
 780             'params' => $params,
 781         ];
 782     }
 783
 784     /**
 785      * Prepare SQL where clause and associated parameters for any keyword searches being performed.
 786      *
 787      * @return array SQL query data in the format ['where' => '', 'params' => []].
 788      */
 789     protected function get_keywords_search_sql(): array {
 790         global $CFG, $DB, $USER;
 791
 792         $keywords = [];
 793         $where = '';
 794         $params = [];
 795         $keywordsfilter = $this->filterset->get_filter('keywords');
 796         $jointype = $keywordsfilter->get_join_type();
 797         $notjoin = false;
 798
 799         // Determine how to match values in the query.
 800         switch ($jointype) {
 801             case $keywordsfilter::JOINTYPE_ALL:
 802                 $wherejoin = ' AND ';
 803                 break;
 804             case $keywordsfilter::JOINTYPE_NONE:
 805                 $wherejoin = ' AND NOT ';
 806                 $notjoin = true;
 807                 break;
 808             default:
 809                 // Default to 'Any' jointype.
 810                 $wherejoin = ' OR ';
 811                 break;
 812         }
 813
 814         if ($this->filterset->has_filter('keywords')) {
 815             $keywords = $keywordsfilter->get_filter_values();
 816         }
 817
 818         foreach ($keywords as $index => $keyword) {
 819             $searchkey1 = 'search' . $index . '1';
 820             $searchkey2 = 'search' . $index . '2';
 821             $searchkey3 = 'search' . $index . '3';
 822             $searchkey4 = 'search' . $index . '4';
 823             $searchkey5 = 'search' . $index . '5';
 824             $searchkey6 = 'search' . $index . '6';
 825             $searchkey7 = 'search' . $index . '7';
 826
 827             $conditions = [];
 828             // Search by fullname.
 829             $fullname = $DB->sql_fullname('u.firstname', 'u.lastname');
 830             $conditions[] = $DB->sql_like($fullname, ':' . $searchkey1, false, false);
 831
 832             // Search by email.
 833             $email = $DB->sql_like('email', ':' . $searchkey2, false, false);
 834
 835             if ($notjoin) {
 836                 $email = "(email IS NOT NULL AND {$email})";
 837             }
 838
 839             if (!in_array('email', $this->userfields)) {
 840                 $maildisplay = 'maildisplay' . $index;
 841                 $userid1 = 'userid' . $index . '1';
 842                 // Prevent users who hide their email address from being found by others
 843                 // who aren't allowed to see hidden email addresses.
 844                 $email = "(". $email ." AND (" .
 845                         "u.maildisplay <> :$maildisplay " .
 846                         "OR u.id = :$userid1". // Users can always find themselves.
 847                         "))";
 848                 $params[$maildisplay] = core_user::MAILDISPLAY_HIDE;
 849                 $params[$userid1] = $USER->id;
 850             }
 851
 852             $conditions[] = $email;
 853
 854             // Search by idnumber.
 855             $idnumber = $DB->sql_like('idnumber', ':' . $searchkey3, false, false);
 856
 857             if ($notjoin) {
 858                 $idnumber = "(idnumber IS NOT NULL AND  {$idnumber})";
 859             }
 860
 861             if (!in_array('idnumber', $this->userfields)) {
 862                 $userid2 = 'userid' . $index . '2';
 863                 // Users who aren't allowed to see idnumbers should at most find themselves
 864                 // when searching for an idnumber.
 865                 $idnumber = "(". $idnumber . " AND u.id = :$userid2)";
 866                 $params[$userid2] = $USER->id;
 867             }
 868
 869             $conditions[] = $idnumber;
 870
 871             if (!empty($CFG->showuseridentity)) {
 872                 // Search all user identify fields.
 873                 $extrasearchfields = explode(',', $CFG->showuseridentity);
 874                 foreach ($extrasearchfields as $extrasearchfield) {
 875                     if (in_array($extrasearchfield, ['email', 'idnumber', 'country'])) {
 876                         // Already covered above. Search by country not supported.
 877                         continue;
 878                     }
 879                     $param = $searchkey3 . $extrasearchfield;
 880                     $condition = $DB->sql_like($extrasearchfield, ':' . $param, false, false);
 881                     $params[$param] = "%$keyword%";
 882
 883                     if ($notjoin) {
 884                         $condition = "($extrasearchfield IS NOT NULL AND {$condition})";
 885                     }
 886
 887                     if (!in_array($extrasearchfield, $this->userfields)) {
 888                         // User cannot see this field, but allow match if their own account.
 889                         $userid3 = 'userid' . $index . '3' . $extrasearchfield;
 890                         $condition = "(". $condition . " AND u.id = :$userid3)";
 891                         $params[$userid3] = $USER->id;
 892                     }
 893                     $conditions[] = $condition;
 894                 }
 895             }
 896
 897             // Search by middlename.
 898             $middlename = $DB->sql_like('middlename', ':' . $searchkey4, false, false);
 899
 900             if ($notjoin) {
 901                 $middlename = "(middlename IS NOT NULL AND {$middlename})";
 902             }
 903
 904             $conditions[] = $middlename;
 905
 906             // Search by alternatename.
 907             $alternatename = $DB->sql_like('alternatename', ':' . $searchkey5, false, false);
 908
 909             if ($notjoin) {
 910                 $alternatename = "(alternatename IS NOT NULL AND {$alternatename})";
 911             }
 912
 913             $conditions[] = $alternatename;
 914
 915             // Search by firstnamephonetic.
 916             $firstnamephonetic = $DB->sql_like('firstnamephonetic', ':' . $searchkey6, false, false);
 917
 918             if ($notjoin) {
 919                 $firstnamephonetic = "(firstnamephonetic IS NOT NULL AND {$firstnamephonetic})";
 920             }
 921
 922             $conditions[] = $firstnamephonetic;
 923
 924             // Search by lastnamephonetic.
 925             $lastnamephonetic = $DB->sql_like('lastnamephonetic', ':' . $searchkey7, false, false);
 926
 927             if ($notjoin) {
 928                 $lastnamephonetic = "(lastnamephonetic IS NOT NULL AND {$lastnamephonetic})";
 929             }
 930
 931             $conditions[] = $lastnamephonetic;
 932
 933             if (!empty($where)) {
 934                 $where .= $wherejoin;
 935             } else if ($jointype === $keywordsfilter::JOINTYPE_NONE) {
 936                 // Join type 'None' requires the WHERE to begin with NOT.
 937                 $where .= ' NOT ';
 938             }
 939
 940             $where .= "(". implode(" OR ", $conditions) .") ";
 941             $params[$searchkey1] = "%$keyword%";
 942             $params[$searchkey2] = "%$keyword%";
 943             $params[$searchkey3] = "%$keyword%";
 944             $params[$searchkey4] = "%$keyword%";
 945             $params[$searchkey5] = "%$keyword%";
 946             $params[$searchkey6] = "%$keyword%";
 947             $params[$searchkey7] = "%$keyword%";
 948         }
 949
 950         return [
 951             'where' => $where,
 952             'params' => $params,
 953         ];
 954     }
 955 }