Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 4.1.x will end 13 November 2023 (12 months).
  • Bug fixes for security issues in 4.1.x will end 10 November 2025 (36 months).
  • PHP version: minimum PHP 7.4.0 Note: minimum PHP version has increased since Moodle 4.0. PHP 8.0.x is supported too.

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401]

   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 core_user\fields;
  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 = fields::get_identity_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              '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);
 107  
 108          $sql = "{$outerselect}
 109                            FROM ({$innerselect}
 110                                            FROM {$innerjoins}
 111                                   {$innerwhere}
 112                                 ) {$subqueryalias}
 113                   {$outerjoins}
 114                   {$outerwhere}
 115                         {$sort}";
 116  
 117          return $DB->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
 118      }
 119  
 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;
 129  
 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);
 139  
 140          $sql = "SELECT COUNT(u.id)
 141                    FROM ({$innerselect}
 142                                    FROM {$innerjoins}
 143                           {$innerwhere}
 144                         ) {$subqueryalias}
 145           {$outerjoins}
 146           {$outerwhere}";
 147  
 148          return $DB->count_records_sql($sql, $params);
 149      }
 150  
 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          global $CFG;
 160  
 161          $isfrontpage = ($this->course->id == SITEID);
 162          $accesssince = 0;
 163          // Whether to match on users who HAVE accessed since the given time (ie false is 'inactive for more than x').
 164          $matchaccesssince = false;
 165  
 166          // The alias for the subquery that fetches all distinct course users.
 167          $usersubqueryalias = 'targetusers';
 168          // The alias for {user} within the distinct user subquery.
 169          $inneruseralias = 'udistinct';
 170          // Inner query that selects distinct users in a course who are not deleted.
 171          // Note: This ensures the outer (filtering) query joins on distinct users, avoiding the need for GROUP BY.
 172          $innerselect = "SELECT DISTINCT {$inneruseralias}.id";
 173          $innerjoins = ["{user} {$inneruseralias}"];
 174          $innerwhere = "WHERE {$inneruseralias}.deleted = 0 AND {$inneruseralias}.id <> :siteguest";
 175          $params = ['siteguest' => $CFG->siteguest];
 176  
 177          $outerjoins = ["JOIN {user} u ON u.id = {$usersubqueryalias}.id"];
 178          $wheres = [];
 179  
 180          if ($this->filterset->has_filter('accesssince')) {
 181              $accesssince = $this->filterset->get_filter('accesssince')->current();
 182  
 183              // Last access filtering only supports matching or not matching, not any/all/none.
 184              $jointypenone = $this->filterset->get_filter('accesssince')::JOINTYPE_NONE;
 185              if ($this->filterset->get_filter('accesssince')->get_join_type() === $jointypenone) {
 186                  $matchaccesssince = true;
 187              }
 188          }
 189  
 190          [
 191              // SQL that forms part of the filter.
 192              'sql' => $esql,
 193              // SQL for enrolment filtering that must always be applied (eg due to capability restrictions).
 194              'forcedsql' => $esqlforced,
 195              'params' => $eparams,
 196          ] = $this->get_enrolled_sql();
 197          $params = array_merge($params, $eparams);
 198  
 199          // Get the fields for all contexts because there is a special case later where it allows
 200          // matches of fields you can't access if they are on your own account.
 201          $userfields = fields::for_identity(null)->with_userpic();
 202          ['selects' => $userfieldssql, 'joins' => $userfieldsjoin, 'params' => $userfieldsparams, 'mappings' => $mappings] =
 203                  (array)$userfields->get_sql('u', true);
 204          if ($userfieldsjoin) {
 205              $outerjoins[] = $userfieldsjoin;
 206              $params = array_merge($params, $userfieldsparams);
 207          }
 208  
 209          // Include any compulsory enrolment SQL (eg capability related filtering that must be applied).
 210          if (!empty($esqlforced)) {
 211              $outerjoins[] = "JOIN ({$esqlforced}) fef ON fef.id = u.id";
 212          }
 213  
 214          // Include any enrolment related filtering.
 215          if (!empty($esql)) {
 216              $outerjoins[] = "LEFT JOIN ({$esql}) ef ON ef.id = u.id";
 217              $wheres[] = 'ef.id IS NOT NULL';
 218          }
 219  
 220          if ($isfrontpage) {
 221              $outerselect = "SELECT u.lastaccess $userfieldssql";
 222              if ($accesssince) {
 223                  $wheres[] = user_get_user_lastaccess_sql($accesssince, 'u', $matchaccesssince);
 224              }
 225          } else {
 226              $outerselect = "SELECT COALESCE(ul.timeaccess, 0) AS lastaccess $userfieldssql";
 227              // Not everybody has accessed the course yet.
 228              $outerjoins[] = 'LEFT JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = :courseid2)';
 229              $params['courseid2'] = $this->course->id;
 230              if ($accesssince) {
 231                  $wheres[] = user_get_course_lastaccess_sql($accesssince, 'ul', $matchaccesssince);
 232              }
 233  
 234              // Make sure we only ever fetch users in the course (regardless of enrolment filters).
 235              $innerjoins[] = "JOIN {user_enrolments} ue ON ue.userid = {$inneruseralias}.id";
 236              $innerjoins[] = 'JOIN {enrol} e ON e.id = ue.enrolid
 237                                        AND e.courseid = :courseid1';
 238              $params['courseid1'] = $this->course->id;
 239          }
 240  
 241          // Performance hacks - we preload user contexts together with accounts.
 242          $ccselect = ', ' . context_helper::get_preload_record_columns_sql('ctx');
 243          $ccjoin = 'LEFT JOIN {context} ctx ON (ctx.instanceid = u.id AND ctx.contextlevel = :contextlevel)';
 244          $params['contextlevel'] = CONTEXT_USER;
 245          $outerselect .= $ccselect;
 246          $outerjoins[] = $ccjoin;
 247  
 248          // Apply any role filtering.
 249          if ($this->filterset->has_filter('roles')) {
 250              [
 251                  'where' => $roleswhere,
 252                  'params' => $rolesparams,
 253              ] = $this->get_roles_sql();
 254  
 255              if (!empty($roleswhere)) {
 256                  $wheres[] = "({$roleswhere})";
 257              }
 258  
 259              if (!empty($rolesparams)) {
 260                  $params = array_merge($params, $rolesparams);
 261              }
 262          }
 263  
 264          // Apply any country filtering.
 265          if ($this->filterset->has_filter('country')) {
 266              [
 267                  'where' => $countrywhere,
 268                  'params' => $countryparams,
 269              ] = $this->get_country_sql();
 270  
 271              if (!empty($countrywhere)) {
 272                  $wheres[] = "($countrywhere)";
 273              }
 274  
 275              if (!empty($countryparams)) {
 276                  $params = array_merge($params, $countryparams);
 277              }
 278          }
 279  
 280          // Apply any keyword text searches.
 281          if ($this->filterset->has_filter('keywords')) {
 282              [
 283                  'where' => $keywordswhere,
 284                  'params' => $keywordsparams,
 285              ] = $this->get_keywords_search_sql($mappings);
 286  
 287              if (!empty($keywordswhere)) {
 288                  $wheres[] = "({$keywordswhere})";
 289              }
 290  
 291              if (!empty($keywordsparams)) {
 292                  $params = array_merge($params, $keywordsparams);
 293              }
 294          }
 295  
 296          // Add any supplied additional forced WHERE clauses.
 297          if (!empty($additionalwhere)) {
 298              $innerwhere .= " AND ({$additionalwhere})";
 299              $params = array_merge($params, $additionalparams);
 300          }
 301  
 302          // Prepare final values.
 303          $outerjoinsstring = implode("\n", $outerjoins);
 304          $innerjoinsstring = implode("\n", $innerjoins);
 305          if ($wheres) {
 306              switch ($this->filterset->get_join_type()) {
 307                  case $this->filterset::JOINTYPE_ALL:
 308                      $wherenot = '';
 309                      $wheresjoin = ' AND ';
 310                      break;
 311                  case $this->filterset::JOINTYPE_NONE:
 312                      $wherenot = ' NOT ';
 313                      $wheresjoin = ' AND NOT ';
 314  
 315                      // Some of the $where conditions may begin with `NOT` which results in `AND NOT NOT ...`.
 316                      // To prevent this from breaking on Oracle the inner WHERE clause is wrapped in brackets, making it
 317                      // `AND NOT (NOT ...)` which is valid in all DBs.
 318                      $wheres = array_map(function($where) {
 319                          return "({$where})";
 320                      }, $wheres);
 321  
 322                      break;
 323                  default:
 324                      // Default to 'Any' jointype.
 325                      $wherenot = '';
 326                      $wheresjoin = ' OR ';
 327                      break;
 328              }
 329  
 330              $outerwhere = 'WHERE ' . $wherenot . implode($wheresjoin, $wheres);
 331          } else {
 332              $outerwhere = '';
 333          }
 334  
 335          return [
 336              'subqueryalias' => $usersubqueryalias,
 337              'outerselect' => $outerselect,
 338              'innerselect' => $innerselect,
 339              'outerjoins' => $outerjoinsstring,
 340              'innerjoins' => $innerjoinsstring,
 341              'outerwhere' => $outerwhere,
 342              'innerwhere' => $innerwhere,
 343              'params' => $params,
 344          ];
 345      }
 346  
 347      /**
 348       * Prepare SQL and associated parameters for users enrolled in the course.
 349       *
 350       * @return array SQL query data in the format ['sql' => '', 'forcedsql' => '', 'params' => []].
 351       */
 352      protected function get_enrolled_sql(): array {
 353          global $USER;
 354  
 355          $isfrontpage = ($this->context->instanceid == SITEID);
 356          $prefix = 'eu_';
 357          $filteruid = "{$prefix}u.id";
 358          $sql = '';
 359          $joins = [];
 360          $wheres = [];
 361          $params = [];
 362          // It is possible some statements must always be included (in addition to any filtering).
 363          $forcedprefix = "f{$prefix}";
 364          $forceduid = "{$forcedprefix}u.id";
 365          $forcedsql = '';
 366          $forcedjoins = [];
 367          $forcedwhere = "{$forcedprefix}u.deleted = 0";
 368  
 369          if (!$isfrontpage) {
 370              // Prepare any enrolment method filtering.
 371              [
 372                  'joins' => $methodjoins,
 373                  'where' => $wheres[],
 374                  'params' => $methodparams,
 375              ] = $this->get_enrol_method_sql($filteruid);
 376  
 377              // Prepare any status filtering.
 378              [
 379                  'joins' => $statusjoins,
 380                  'where' => $statuswhere,
 381                  'params' => $statusparams,
 382                  'forcestatus' => $forcestatus,
 383              ] = $this->get_status_sql($filteruid, $forceduid, $forcedprefix);
 384  
 385              if ($forcestatus) {
 386                  // Force filtering by active participants if user does not have capability to view suspended.
 387                  $forcedjoins = array_merge($forcedjoins, $statusjoins);
 388                  $statusjoins = [];
 389                  $forcedwhere .= " AND ({$statuswhere})";
 390              } else {
 391                  $wheres[] = $statuswhere;
 392              }
 393  
 394              $joins = array_merge($joins, $methodjoins, $statusjoins);
 395              $params = array_merge($params, $methodparams, $statusparams);
 396          }
 397  
 398          $groupids = [];
 399  
 400          if ($this->filterset->has_filter('groups')) {
 401              $groupids = $this->filterset->get_filter('groups')->get_filter_values();
 402          }
 403  
 404          // Force additional groups filtering if required due to lack of capabilities.
 405          // Note: This means results will always be limited to allowed groups, even if the user applies their own groups filtering.
 406          $canaccessallgroups = has_capability('moodle/site:accessallgroups', $this->context);
 407          $forcegroups = ($this->course->groupmode == SEPARATEGROUPS && !$canaccessallgroups);
 408  
 409          if ($forcegroups) {
 410              $allowedgroupids = array_keys(groups_get_all_groups($this->course->id, $USER->id));
 411  
 412              // Users not in any group in a course with separate groups mode should not be able to access the participants filter.
 413              if (empty($allowedgroupids)) {
 414                  // The UI does not support this, so it should not be reachable unless someone is trying to bypass the restriction.
 415                  throw new \coding_exception('User must be part of a group to filter by participants.');
 416              }
 417  
 418              $forceduid = "{$forcedprefix}u.id";
 419              $forcedjointype = $this->get_groups_jointype(\core_table\local\filter\filter::JOINTYPE_ANY);
 420              $forcedgroupjoin = groups_get_members_join($allowedgroupids, $forceduid, $this->context, $forcedjointype);
 421  
 422              $forcedjoins[] = $forcedgroupjoin->joins;
 423              $forcedwhere .= " AND ({$forcedgroupjoin->wheres})";
 424  
 425              $params = array_merge($params, $forcedgroupjoin->params);
 426  
 427              // Remove any filtered groups the user does not have access to.
 428              $groupids = array_intersect($allowedgroupids, $groupids);
 429          }
 430  
 431          // Prepare any user defined groups filtering.
 432          if ($groupids) {
 433              $groupjoin = groups_get_members_join($groupids, $filteruid, $this->context, $this->get_groups_jointype());
 434  
 435              $joins[] = $groupjoin->joins;
 436              $params = array_merge($params, $groupjoin->params);
 437              if (!empty($groupjoin->wheres)) {
 438                  $wheres[] = $groupjoin->wheres;
 439              }
 440          }
 441  
 442          // Combine the relevant filters and prepare the query.
 443          $joins = array_filter($joins);
 444          if (!empty($joins)) {
 445              $joinsql = implode("\n", $joins);
 446  
 447              $sql = "SELECT DISTINCT {$prefix}u.id
 448                                 FROM {user} {$prefix}u
 449                                      {$joinsql}
 450                                WHERE {$prefix}u.deleted = 0";
 451          }
 452  
 453          $wheres = array_filter($wheres);
 454          if (!empty($wheres)) {
 455              if ($this->filterset->get_join_type() === $this->filterset::JOINTYPE_ALL) {
 456                  $wheresql = '(' . implode(') AND (', $wheres) . ')';
 457              } else {
 458                  $wheresql = '(' . implode(') OR (', $wheres) . ')';
 459              }
 460  
 461              $sql .= " AND ({$wheresql})";
 462          }
 463  
 464          // Prepare any SQL that must be applied.
 465          if (!empty($forcedjoins)) {
 466              $forcedjoinsql = implode("\n", $forcedjoins);
 467              $forcedsql = "SELECT DISTINCT {$forcedprefix}u.id
 468                                       FROM {user} {$forcedprefix}u
 469                                            {$forcedjoinsql}
 470                                      WHERE {$forcedwhere}";
 471          }
 472  
 473          return [
 474              'sql' => $sql,
 475              'forcedsql' => $forcedsql,
 476              'params' => $params,
 477          ];
 478      }
 479  
 480      /**
 481       * Prepare the enrolment methods filter SQL content.
 482       *
 483       * @param string $useridcolumn User ID column used in the calling query, e.g. u.id
 484       * @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => []].
 485       */
 486      protected function get_enrol_method_sql($useridcolumn): array {
 487          global $DB;
 488  
 489          $prefix = 'ejm_';
 490          $joins  = [];
 491          $where = '';
 492          $params = [];
 493          $enrolids = [];
 494  
 495          if ($this->filterset->has_filter('enrolments')) {
 496              $enrolids = $this->filterset->get_filter('enrolments')->get_filter_values();
 497          }
 498  
 499          if (!empty($enrolids)) {
 500              $jointype = $this->filterset->get_filter('enrolments')->get_join_type();
 501  
 502              // Handle 'All' join type.
 503              if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL ||
 504                      $jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_NONE) {
 505                  $allwheres = [];
 506  
 507                  foreach ($enrolids as $i => $enrolid) {
 508                      $thisprefix = "{$prefix}{$i}";
 509                      list($enrolidsql, $enrolidparam) = $DB->get_in_or_equal($enrolid, SQL_PARAMS_NAMED, $thisprefix);
 510  
 511                      $joins[] = "LEFT JOIN {enrol} {$thisprefix}e
 512                                         ON ({$thisprefix}e.id {$enrolidsql}
 513                                        AND {$thisprefix}e.courseid = :{$thisprefix}courseid)";
 514                      $joins[] = "LEFT JOIN {user_enrolments} {$thisprefix}ue
 515                                         ON {$thisprefix}ue.userid = {$useridcolumn}
 516                                        AND {$thisprefix}ue.enrolid = {$thisprefix}e.id";
 517  
 518                      if ($jointype === $this->filterset->get_filter('enrolments')::JOINTYPE_ALL) {
 519                          $allwheres[] = "{$thisprefix}ue.id IS NOT NULL";
 520                      } else {
 521                          // Ensure participants do not match any of the filtered methods when joining by 'None'.
 522                          $allwheres[] = "{$thisprefix}ue.id IS NULL";
 523                      }
 524  
 525                      $params["{$thisprefix}courseid"] = $this->course->id;
 526                      $params = array_merge($params, $enrolidparam);
 527                  }
 528  
 529                  if (!empty($allwheres)) {
 530                      $where = implode(' AND ', $allwheres);
 531                  }
 532              } else {
 533                  // Handle the 'Any'join type.
 534  
 535                  list($enrolidssql, $enrolidsparams) = $DB->get_in_or_equal($enrolids, SQL_PARAMS_NAMED, $prefix);
 536  
 537                  $joins[] = "LEFT JOIN {enrol} {$prefix}e
 538                                     ON ({$prefix}e.id {$enrolidssql}
 539                                    AND {$prefix}e.courseid = :{$prefix}courseid)";
 540                  $joins[] = "LEFT JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}
 541                                                                AND {$prefix}ue.enrolid = {$prefix}e.id";
 542                  $where = "{$prefix}ue.id IS NOT NULL";
 543  
 544                  $params["{$prefix}courseid"] = $this->course->id;
 545                  $params = array_merge($params, $enrolidsparams);
 546              }
 547          }
 548  
 549          return [
 550              'joins' => $joins,
 551              'where' => $where,
 552              'params' => $params,
 553          ];
 554      }
 555  
 556      /**
 557       * Prepare the status filter SQL content.
 558       * Note: Users who cannot view suspended users will always have their results filtered to only show active participants.
 559       *
 560       * @param string $filteruidcolumn User ID column used in the calling query, e.g. eu_u.id
 561       * @param string $forceduidcolumn User ID column used in any forced query, e.g. feu_u.id
 562       * @param string $forcedprefix The prefix to use if forced filtering is required
 563       * @return array SQL query data in the format ['joins' => [], 'where' => '', 'params' => [], 'forcestatus' => true]
 564       */
 565      protected function get_status_sql($filteruidcolumn, $forceduidcolumn, $forcedprefix): array {
 566          $prefix = $forcedprefix;
 567          $useridcolumn = $forceduidcolumn;
 568          $joins  = [];
 569          $where = '';
 570          $params = [];
 571          $forcestatus = true;
 572  
 573          // By default we filter to show users with active status only.
 574          $statusids = [ENROL_USER_ACTIVE];
 575          $statusjointype = $this->filterset::JOINTYPE_DEFAULT;
 576  
 577          // Allow optional status filtering if the user has relevant capabilities.
 578          if (has_capability('moodle/course:enrolreview', $this->context) &&
 579                  (has_capability('moodle/course:viewsuspendedusers', $this->context))) {
 580              $forcestatus = false;
 581              $prefix = 'ejs_';
 582              $useridcolumn = $filteruidcolumn;
 583  
 584              // Default to no filtering if capabilities allow for it.
 585              $statusids = [];
 586  
 587              if ($this->filterset->has_filter('status')) {
 588                  $statusjointype = $this->filterset->get_filter('status')->get_join_type();
 589                  $statusfiltervalues = $this->filterset->get_filter('status')->get_filter_values();
 590  
 591                  // If values are set for the status filter, use them.
 592                  if (!empty($statusfiltervalues)) {
 593                      $statusids = $statusfiltervalues;
 594                  }
 595              }
 596          }
 597  
 598          if (!empty($statusids)) {
 599              $enroljoin = 'JOIN {enrol} %1$se ON %1$se.id = %1$sue.enrolid
 600                                                    AND %1$se.courseid = :%1$scourseid';
 601  
 602              $whereactive = '(%1$sue.status = :%2$sactive
 603                            AND %1$se.status = :%2$senabled
 604                        AND %1$sue.timestart < :%2$snow1
 605                         AND (%1$sue.timeend = 0
 606                           OR %1$sue.timeend > :%2$snow2))';
 607  
 608              $wheresuspended = '(%1$sue.status = :%2$ssuspended
 609                               OR %1$se.status != :%2$senabled
 610                           OR %1$sue.timestart >= :%2$snow1
 611                             OR (%1$sue.timeend > 0
 612                            AND %1$sue.timeend <= :%2$snow2))';
 613  
 614              // Round 'now' time to help DB caching.
 615              $now = round(time(), -2);
 616  
 617              switch ($statusjointype) {
 618                  case $this->filterset::JOINTYPE_ALL:
 619                      $joinwheres = [];
 620  
 621                      foreach ($statusids as $i => $statusid) {
 622                          $joinprefix = "{$prefix}{$i}";
 623                          $joins[] = "JOIN {user_enrolments} {$joinprefix}ue ON {$joinprefix}ue.userid = {$useridcolumn}";
 624  
 625                          if ($statusid === ENROL_USER_ACTIVE) {
 626                              // Conditions to be met if user filtering by active.
 627                              $joinwheres[] = sprintf($whereactive, $joinprefix, $joinprefix);
 628  
 629                              $activeparams = [
 630                                  "{$joinprefix}active" => ENROL_USER_ACTIVE,
 631                                  "{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,
 632                                  "{$joinprefix}now1"   => $now,
 633                                  "{$joinprefix}now2"   => $now,
 634                                  "{$joinprefix}courseid"   => $this->course->id,
 635                              ];
 636  
 637                              $params = array_merge($params, $activeparams);
 638                          } else {
 639                              // Conditions to be met if filtering by suspended (currently the only other status).
 640                              $joinwheres[] = sprintf($wheresuspended, $joinprefix, $joinprefix);
 641  
 642                              $suspendedparams = [
 643                                  "{$joinprefix}suspended" => ENROL_USER_SUSPENDED,
 644                                  "{$joinprefix}enabled" => ENROL_INSTANCE_ENABLED,
 645                                  "{$joinprefix}now1"   => $now,
 646                                  "{$joinprefix}now2"   => $now,
 647                                  "{$joinprefix}courseid"   => $this->course->id,
 648                              ];
 649  
 650                              $params = array_merge($params, $suspendedparams);
 651                          }
 652  
 653                          $joins[] = sprintf($enroljoin, $joinprefix);
 654                      }
 655  
 656                      $where = implode(' AND ', $joinwheres);
 657                      break;
 658  
 659                  case $this->filterset::JOINTYPE_NONE:
 660                      // Should always be enrolled, just not in any of the filtered statuses.
 661                      $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";
 662                      $joins[] = sprintf($enroljoin, $prefix);
 663                      $joinwheres = [];
 664                      $params["{$prefix}courseid"] = $this->course->id;
 665  
 666                      foreach ($statusids as $i => $statusid) {
 667                          $paramprefix = "{$prefix}{$i}";
 668  
 669                          if ($statusid === ENROL_USER_ACTIVE) {
 670                              // Conditions to be met if user filtering by active.
 671                              $joinwheres[] = sprintf("NOT {$whereactive}", $prefix, $paramprefix);
 672  
 673                              $activeparams = [
 674                                  "{$paramprefix}active" => ENROL_USER_ACTIVE,
 675                                  "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
 676                                  "{$paramprefix}now1"   => $now,
 677                                  "{$paramprefix}now2"   => $now,
 678                              ];
 679  
 680                              $params = array_merge($params, $activeparams);
 681                          } else {
 682                              // Conditions to be met if filtering by suspended (currently the only other status).
 683                              $joinwheres[] = sprintf("NOT {$wheresuspended}", $prefix, $paramprefix);
 684  
 685                              $suspendedparams = [
 686                                  "{$paramprefix}suspended" => ENROL_USER_SUSPENDED,
 687                                  "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
 688                                  "{$paramprefix}now1"   => $now,
 689                                  "{$paramprefix}now2"   => $now,
 690                              ];
 691  
 692                              $params = array_merge($params, $suspendedparams);
 693                          }
 694                      }
 695  
 696                      $where = '(' . implode(' AND ', $joinwheres) . ')';
 697                      break;
 698  
 699                  default:
 700                      // Handle the 'Any' join type.
 701  
 702                      $joins[] = "JOIN {user_enrolments} {$prefix}ue ON {$prefix}ue.userid = {$useridcolumn}";
 703                      $joins[] = sprintf($enroljoin, $prefix);
 704                      $joinwheres = [];
 705                      $params["{$prefix}courseid"] = $this->course->id;
 706  
 707                      foreach ($statusids as $i => $statusid) {
 708                          $paramprefix = "{$prefix}{$i}";
 709  
 710                          if ($statusid === ENROL_USER_ACTIVE) {
 711                              // Conditions to be met if user filtering by active.
 712                              $joinwheres[] = sprintf($whereactive, $prefix, $paramprefix);
 713  
 714                              $activeparams = [
 715                                  "{$paramprefix}active" => ENROL_USER_ACTIVE,
 716                                  "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
 717                                  "{$paramprefix}now1"   => $now,
 718                                  "{$paramprefix}now2"   => $now,
 719                              ];
 720  
 721                              $params = array_merge($params, $activeparams);
 722                          } else {
 723                              // Conditions to be met if filtering by suspended (currently the only other status).
 724                              $joinwheres[] = sprintf($wheresuspended, $prefix, $paramprefix);
 725  
 726                              $suspendedparams = [
 727                                  "{$paramprefix}suspended" => ENROL_USER_SUSPENDED,
 728                                  "{$paramprefix}enabled" => ENROL_INSTANCE_ENABLED,
 729                                  "{$paramprefix}now1"   => $now,
 730                                  "{$paramprefix}now2"   => $now,
 731                              ];
 732  
 733                              $params = array_merge($params, $suspendedparams);
 734                          }
 735                      }
 736  
 737                      $where = '(' . implode(' OR ', $joinwheres) . ')';
 738                      break;
 739              }
 740          }
 741  
 742          return [
 743              'joins' => $joins,
 744              'where' => $where,
 745              'params' => $params,
 746              'forcestatus' => $forcestatus,
 747          ];
 748      }
 749  
 750      /**
 751       * Fetch the groups filter's grouplib jointype, based on its filterset jointype.
 752       * This mapping is to ensure compatibility between the two, should their values ever differ.
 753       *
 754       * @param int|null $forcedjointype If set, specifies the join type to fetch mapping for (used when applying forced filtering).
 755       *                            If null, then user defined filter join type is used.
 756       * @return int
 757       */
 758      protected function get_groups_jointype(?int $forcedjointype = null): int {
 759  
 760          // If applying forced groups filter and no manual groups filtering is applied, add an empty filter so we can map the join.
 761          if (!is_null($forcedjointype) && !$this->filterset->has_filter('groups')) {
 762              $this->filterset->add_filter(new \core_table\local\filter\integer_filter('groups'));
 763          }
 764  
 765          $groupsfilter = $this->filterset->get_filter('groups');
 766  
 767          if (is_null($forcedjointype)) {
 768              // Fetch join type mapping for a user supplied groups filtering.
 769              $filterjointype = $groupsfilter->get_join_type();
 770          } else {
 771              // Fetch join type mapping for forced groups filtering.
 772              $filterjointype = $forcedjointype;
 773          }
 774  
 775          switch ($filterjointype) {
 776              case $groupsfilter::JOINTYPE_NONE:
 777                  $groupsjoin = GROUPS_JOIN_NONE;
 778                  break;
 779              case $groupsfilter::JOINTYPE_ALL:
 780                  $groupsjoin = GROUPS_JOIN_ALL;
 781                  break;
 782              default:
 783                  // Default to ANY jointype.
 784                  $groupsjoin = GROUPS_JOIN_ANY;
 785                  break;
 786          }
 787  
 788          return $groupsjoin;
 789      }
 790  
 791      /**
 792       * Prepare SQL where clause and associated parameters for any roles filtering being performed.
 793       *
 794       * @return array SQL query data in the format ['where' => '', 'params' => []].
 795       */
 796      protected function get_roles_sql(): array {
 797          global $DB;
 798  
 799          $where = '';
 800          $params = [];
 801  
 802          // Limit list to users with some role only.
 803          if ($this->filterset->has_filter('roles')) {
 804              $rolesfilter = $this->filterset->get_filter('roles');
 805  
 806              $roleids = $rolesfilter->get_filter_values();
 807              $jointype = $rolesfilter->get_join_type();
 808  
 809              // Determine how to match values in the query.
 810              $matchinsql = 'IN';
 811              switch ($jointype) {
 812                  case $rolesfilter::JOINTYPE_ALL:
 813                      $wherejoin = ' AND ';
 814                      break;
 815                  case $rolesfilter::JOINTYPE_NONE:
 816                      $wherejoin = ' AND NOT ';
 817                      $matchinsql = 'NOT IN';
 818                      break;
 819                  default:
 820                      // Default to 'Any' jointype.
 821                      $wherejoin = ' OR ';
 822                      break;
 823              }
 824  
 825              // We want to query both the current context and parent contexts.
 826              $rolecontextids = $this->context->get_parent_context_ids(true);
 827  
 828              // Get users without any role, if needed.
 829              if (($withoutkey = array_search(-1, $roleids)) !== false) {
 830                  list($relatedctxsql1, $norolectxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
 831  
 832                  if ($jointype === $rolesfilter::JOINTYPE_NONE) {
 833                      $where .= "(u.id IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
 834                  } else {
 835                      $where .= "(u.id NOT IN (SELECT userid FROM {role_assignments} WHERE contextid {$relatedctxsql1}))";
 836                  }
 837  
 838                  $params = array_merge($params, $norolectxparams);
 839  
 840                  if ($withoutkey !== false) {
 841                      unset($roleids[$withoutkey]);
 842                  }
 843  
 844                  // Join if any roles will be included.
 845                  if (!empty($roleids)) {
 846                      // The NOT case is replaced with AND to prevent a double negative.
 847                      $where .= $jointype === $rolesfilter::JOINTYPE_NONE ? ' AND ' : $wherejoin;
 848                  }
 849              }
 850  
 851              // Get users with specified roles, if needed.
 852              if (!empty($roleids)) {
 853                  // All case - need one WHERE per filtered role.
 854                  if ($rolesfilter::JOINTYPE_ALL === $jointype) {
 855                      $numroles = count($roleids);
 856                      $rolecount = 1;
 857  
 858                      foreach ($roleids as $roleid) {
 859                          list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
 860                          list($roleidssql, $roleidparams) = $DB->get_in_or_equal($roleid, SQL_PARAMS_NAMED, 'roleids');
 861  
 862                          $where .= "(u.id IN (
 863                                       SELECT userid
 864                                         FROM {role_assignments}
 865                                        WHERE roleid {$roleidssql}
 866                                          AND contextid {$relatedctxsql})
 867                                     )";
 868  
 869                          if ($rolecount < $numroles) {
 870                              $where .= $wherejoin;
 871                              $rolecount++;
 872                          }
 873  
 874                          $params = array_merge($params, $roleidparams, $relctxparams);
 875                      }
 876  
 877                  } else {
 878                      // Any / None cases - need one WHERE to cover all filtered roles.
 879                      list($relatedctxsql, $relctxparams) = $DB->get_in_or_equal($rolecontextids, SQL_PARAMS_NAMED, 'relatedctx');
 880                      list($roleidssql, $roleidsparams) = $DB->get_in_or_equal($roleids, SQL_PARAMS_NAMED, 'roleids');
 881  
 882                      $where .= "(u.id {$matchinsql} (
 883                                   SELECT userid
 884                                     FROM {role_assignments}
 885                                    WHERE roleid {$roleidssql}
 886                                      AND contextid {$relatedctxsql})
 887                                 )";
 888  
 889                      $params = array_merge($params, $roleidsparams, $relctxparams);
 890                  }
 891              }
 892          }
 893  
 894          return [
 895              'where' => $where,
 896              'params' => $params,
 897          ];
 898      }
 899  
 900      /**
 901       * Prepare SQL where clause and associated parameters for country filtering
 902       *
 903       * @return array SQL query data in the format ['where' => '', 'params' => []].
 904       */
 905      protected function get_country_sql(): array {
 906          global $DB;
 907  
 908          $where = '';
 909          $params = [];
 910  
 911          $countryfilter = $this->filterset->get_filter('country');
 912          if ($countrycodes = $countryfilter->get_filter_values()) {
 913              // If filter type is "None", then we negate the comparison.
 914              [$countrywhere, $params] = $DB->get_in_or_equal($countrycodes, SQL_PARAMS_NAMED, 'country',
 915                  $countryfilter->get_join_type() !== $countryfilter::JOINTYPE_NONE);
 916  
 917              $where = "(u.country {$countrywhere})";
 918          }
 919  
 920          return [
 921              'where' => $where,
 922              'params' => $params,
 923          ];
 924      }
 925  
 926      /**
 927       * Prepare SQL where clause and associated parameters for any keyword searches being performed.
 928       *
 929       * @param array $mappings Array of field mappings (fieldname => SQL code for the value)
 930       * @return array SQL query data in the format ['where' => '', 'params' => []].
 931       */
 932      protected function get_keywords_search_sql(array $mappings): array {
 933          global $CFG, $DB, $USER;
 934  
 935          $keywords = [];
 936          $where = '';
 937          $params = [];
 938          $keywordsfilter = $this->filterset->get_filter('keywords');
 939          $jointype = $keywordsfilter->get_join_type();
 940          // None join types in both filter row and filterset require additional 'not null' handling for accurate keywords matches.
 941          $notjoin = false;
 942  
 943          // Determine how to match values in the query.
 944          switch ($jointype) {
 945              case $keywordsfilter::JOINTYPE_ALL:
 946                  $wherejoin = ' AND ';
 947                  break;
 948              case $keywordsfilter::JOINTYPE_NONE:
 949                  $wherejoin = ' AND NOT ';
 950                  $notjoin = true;
 951                  break;
 952              default:
 953                  // Default to 'Any' jointype.
 954                  $wherejoin = ' OR ';
 955                  break;
 956          }
 957  
 958          // Handle filterset None join type.
 959          if ($this->filterset->get_join_type() === $this->filterset::JOINTYPE_NONE) {
 960              $notjoin = true;
 961          }
 962  
 963          if ($this->filterset->has_filter('keywords')) {
 964              $keywords = $keywordsfilter->get_filter_values();
 965          }
 966  
 967          $canviewfullnames = has_capability('moodle/site:viewfullnames', $this->context);
 968  
 969          foreach ($keywords as $index => $keyword) {
 970              $searchkey1 = 'search' . $index . '1';
 971              $searchkey2 = 'search' . $index . '2';
 972              $searchkey3 = 'search' . $index . '3';
 973              $searchkey4 = 'search' . $index . '4';
 974              $searchkey5 = 'search' . $index . '5';
 975              $searchkey6 = 'search' . $index . '6';
 976              $searchkey7 = 'search' . $index . '7';
 977  
 978              $conditions = [];
 979  
 980              // Search by fullname.
 981              [$fullname, $fullnameparams] = fields::get_sql_fullname('u', $canviewfullnames);
 982              $conditions[] = $DB->sql_like($fullname, ':' . $searchkey1, false, false);
 983              $params = array_merge($params, $fullnameparams);
 984  
 985              // Search by email.
 986              $email = $DB->sql_like('email', ':' . $searchkey2, false, false);
 987  
 988              if ($notjoin) {
 989                  $email = "(email IS NOT NULL AND {$email})";
 990              }
 991  
 992              if (!in_array('email', $this->userfields)) {
 993                  $maildisplay = 'maildisplay' . $index;
 994                  $userid1 = 'userid' . $index . '1';
 995                  // Prevent users who hide their email address from being found by others
 996                  // who aren't allowed to see hidden email addresses.
 997                  $email = "(". $email ." AND (" .
 998                          "u.maildisplay <> :$maildisplay " .
 999                          "OR u.id = :$userid1". // Users can always find themselves.
1000                          "))";
1001                  $params[$maildisplay] = core_user::MAILDISPLAY_HIDE;
1002                  $params[$userid1] = $USER->id;
1003              }
1004  
1005              $conditions[] = $email;
1006  
1007              // Search by idnumber.
1008              $idnumber = $DB->sql_like('idnumber', ':' . $searchkey3, false, false);
1009  
1010              if ($notjoin) {
1011                  $idnumber = "(idnumber IS NOT NULL AND  {$idnumber})";
1012              }
1013  
1014              if (!in_array('idnumber', $this->userfields)) {
1015                  $userid2 = 'userid' . $index . '2';
1016                  // Users who aren't allowed to see idnumbers should at most find themselves
1017                  // when searching for an idnumber.
1018                  $idnumber = "(". $idnumber . " AND u.id = :$userid2)";
1019                  $params[$userid2] = $USER->id;
1020              }
1021  
1022              $conditions[] = $idnumber;
1023  
1024              // Search all user identify fields.
1025              $extrasearchfields = fields::get_identity_fields(null);
1026              foreach ($extrasearchfields as $fieldindex => $extrasearchfield) {
1027                  if (in_array($extrasearchfield, ['email', 'idnumber', 'country'])) {
1028                      // Already covered above.
1029                      continue;
1030                  }
1031                  // The param must be short (max 32 characters) so don't include field name.
1032                  $param = $searchkey3 . '_ident' . $fieldindex;
1033                  $fieldsql = $mappings[$extrasearchfield];
1034                  $condition = $DB->sql_like($fieldsql, ':' . $param, false, false);
1035                  $params[$param] = "%$keyword%";
1036  
1037                  if ($notjoin) {
1038                      $condition = "($fieldsql IS NOT NULL AND {$condition})";
1039                  }
1040  
1041                  if (!in_array($extrasearchfield, $this->userfields)) {
1042                      // User cannot see this field, but allow match if their own account.
1043                      $userid3 = 'userid' . $index . '3_ident' . $fieldindex;
1044                      $condition = "(". $condition . " AND u.id = :$userid3)";
1045                      $params[$userid3] = $USER->id;
1046                  }
1047                  $conditions[] = $condition;
1048              }
1049  
1050              // Search by middlename.
1051              $middlename = $DB->sql_like('middlename', ':' . $searchkey4, false, false);
1052  
1053              if ($notjoin) {
1054                  $middlename = "(middlename IS NOT NULL AND {$middlename})";
1055              }
1056  
1057              $conditions[] = $middlename;
1058  
1059              // Search by alternatename.
1060              $alternatename = $DB->sql_like('alternatename', ':' . $searchkey5, false, false);
1061  
1062              if ($notjoin) {
1063                  $alternatename = "(alternatename IS NOT NULL AND {$alternatename})";
1064              }
1065  
1066              $conditions[] = $alternatename;
1067  
1068              // Search by firstnamephonetic.
1069              $firstnamephonetic = $DB->sql_like('firstnamephonetic', ':' . $searchkey6, false, false);
1070  
1071              if ($notjoin) {
1072                  $firstnamephonetic = "(firstnamephonetic IS NOT NULL AND {$firstnamephonetic})";
1073              }
1074  
1075              $conditions[] = $firstnamephonetic;
1076  
1077              // Search by lastnamephonetic.
1078              $lastnamephonetic = $DB->sql_like('lastnamephonetic', ':' . $searchkey7, false, false);
1079  
1080              if ($notjoin) {
1081                  $lastnamephonetic = "(lastnamephonetic IS NOT NULL AND {$lastnamephonetic})";
1082              }
1083  
1084              $conditions[] = $lastnamephonetic;
1085  
1086              if (!empty($where)) {
1087                  $where .= $wherejoin;
1088              } else if ($jointype === $keywordsfilter::JOINTYPE_NONE) {
1089                  // Join type 'None' requires the WHERE to begin with NOT.
1090                  $where .= ' NOT ';
1091              }
1092  
1093              $where .= "(". implode(" OR ", $conditions) .") ";
1094              $params[$searchkey1] = "%$keyword%";
1095              $params[$searchkey2] = "%$keyword%";
1096              $params[$searchkey3] = "%$keyword%";
1097              $params[$searchkey4] = "%$keyword%";
1098              $params[$searchkey5] = "%$keyword%";
1099              $params[$searchkey6] = "%$keyword%";
1100              $params[$searchkey7] = "%$keyword%";
1101          }
1102  
1103          return [
1104              'where' => $where,
1105              'params' => $params,
1106          ];
1107      }
1108  }