Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
  • Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.

Differences Between: [Versions 39 and 311] [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 and 403]

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