Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 3.10.x will end 8 November 2021 (12 months).
- Bug fixes for security issues in 3.10.x will end 9 May 2022 (18 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.
/user/classes/table/ -> participants_search.php (source)
Differences Between: [Versions 310 and 311] [Versions 310 and 400] [Versions 310 and 401] [Versions 310 and 402] [Versions 310 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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
