Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 4.3.x will end 7 October 2024 (12 months).
- Bug fixes for security issues in 4.3.x will end 21 April 2025 (18 months).
- PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.2.x is supported too.
/user/classes/table/ -> participants_search.php (source)
Differences Between: [Versions 310 and 403] [Versions 311 and 403] [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 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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
