ILIAS  release_6 Revision v6.24-5-g0c8bfefb3b8
class.ilUserQuery.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */
3
13{
14 const DEFAULT_ORDER_FIELD = 'login';
15
19 private $logger = null;
20
22 private $order_dir = 'asc';
23 private $offset = 0;
24 private $limit = 50;
25 private $text_filter = '';
26 private $activation = '';
27 private $last_login = null;
28 private $limited_access = false;
29 private $no_courses = false;
30 private $no_groups = false;
31 private $crs_grp = 0;
32 private $role = 0;
33 private $user_folder = 0;
34 private $additional_fields = array();
35 private $users = array();
36 private $first_letter = '';
37 private $has_access = false;
39
43 protected $udf_filter = array();
44
45 private $default_fields = array(
46 "usr_id",
47 "login",
48 "firstname",
49 "lastname",
50 "email",
51 "second_email",
52 "time_limit_until",
53 "time_limit_unlimited",
54 "time_limit_owner",
55 "last_login",
56 "active"
57 );
58
62 public function __construct()
63 {
64 global $DIC;
65
66 $this->logger = $DIC->logger()->usr();
67 }
68
74 public function setUdfFilter($a_val)
75 {
76 $valid_udfs = [];
77
78 $definitions = \ilUserDefinedFields::_getInstance()->getDefinitions();
79 foreach ((array) $a_val as $udf_name => $udf_value) {
80 list($udf_string, $udf_id) = explode('_', $udf_name);
81 if (array_key_exists((int) $udf_id, $definitions)) {
82 $valid_udfs[$udf_name] = $udf_value;
83 }
84 }
85 $this->udf_filter = $valid_udfs;
86 }
87
93 public function getUdfFilter()
94 {
95 return $this->udf_filter;
96 }
97
103 public function setOrderField($a_order)
104 {
105 $this->order_field = $a_order;
106 }
107
114 public function setOrderDirection($a_dir)
115 {
116 $this->order_dir = $a_dir;
117 }
118
123 public function setOffset($a_offset)
124 {
125 $this->offset = $a_offset;
126 }
127
133 public function setLimit($a_limit)
134 {
135 $this->limit = $a_limit;
136 }
137
142 public function setTextFilter($a_filter)
143 {
144 $this->text_filter = $a_filter;
145 }
146
152 public function setActionFilter($a_activation)
153 {
154 $this->activation = $a_activation;
155 }
156
161 public function setLastLogin(ilDateTime $dt = null)
162 {
163 $this->last_login = $dt;
164 }
165
170 public function setLimitedAccessFilter($a_status)
171 {
172 $this->limited_access = $a_status;
173 }
174
179 public function setNoCourseFilter($a_no_course)
180 {
181 $this->no_courses = $a_no_course;
182 }
183
188 public function setNoGroupFilter($a_no_group)
189 {
190 $this->no_groups = $a_no_group;
191 }
192
198 public function setCourseGroupFilter($a_cg_id)
199 {
200 $this->crs_grp = $a_cg_id;
201 }
202
208 public function setRoleFilter($a_role_id)
209 {
210 $this->role = $a_role_id;
211 }
212
218 public function setUserFolder($a_fold_id)
219 {
220 $this->user_folder = $a_fold_id;
221 }
222
227 public function setAdditionalFields($a_add)
228 {
229 $this->additional_fields = (array) $a_add;
230 }
231
236 public function setUserFilter($a_filter)
237 {
238 $this->users = $a_filter;
239 }
240
245 public function setFirstLetterLastname($a_fll)
246 {
247 $this->first_letter = $a_fll;
248 }
249
255 public function setAccessFilter($a_access)
256 {
257 $this->has_access = (bool) $a_access;
258 }
259
265 public function setAuthenticationFilter($a_authentication)
266 {
267 $this->authentication_method = $a_authentication;
268 }
269
274 public function query()
275 {
276 global $DIC;
277
278 $ilDB = $DIC['ilDB'];
279
280
281 $udf_fields = array();
282
283 $join = "";
284
285 if (is_array($this->additional_fields)) {
286 foreach ($this->additional_fields as $f) {
287 if (!in_array($f, $this->default_fields)) {
288 if ($f == "online_time") {
289 $this->default_fields[] = "ut_online.online_time";
290 $join = " LEFT JOIN ut_online ON (usr_data.usr_id = ut_online.usr_id) ";
291 } elseif (substr($f, 0, 4) == "udf_") {
292 $udf_fields[] = (int) substr($f, 4);
293 } else {
294 $this->default_fields[] = $f;
295 }
296 }
297 }
298 }
299
300 // if udf fields are involved we need the definitions
301 $udf_def = array();
302 if (count($udf_fields) > 0) {
303 include_once './Services/User/classes/class.ilUserDefinedFields.php';
304 $udf_def = ilUserDefinedFields::_getInstance()->getDefinitions();
305 }
306
307 // join udf table
308 foreach ($udf_fields as $id) {
309 $udf_table = ($udf_def[$id]["field_type"] != UDF_TYPE_WYSIWYG)
310 ? "udf_text"
311 : "udf_clob";
312 $join .= " LEFT JOIN " . $udf_table . " ud_" . $id . " ON (ud_" . $id . ".field_id=" . $ilDB->quote($id) . " AND ud_" . $id . ".usr_id = usr_data.usr_id) ";
313 }
314
315 // count query
316 $count_query = "SELECT count(usr_data.usr_id) cnt" .
317 " FROM usr_data";
318
319 $all_multi_fields = array("interests_general", "interests_help_offered", "interests_help_looking");
320 $multi_fields = array();
321
322 $sql_fields = array();
323 foreach ($this->default_fields as $idx => $field) {
324 if (!$field) {
325 continue;
326 }
327
328 if (in_array($field, $all_multi_fields)) {
329 $multi_fields[] = $field;
330 } elseif (!stristr($field, ".")) {
331 $sql_fields[] = "usr_data." . $field;
332 } else {
333 $sql_fields[] = $field;
334 }
335 }
336
337 // udf fields
338 foreach ($udf_fields as $id) {
339 $sql_fields[] = "ud_" . $id . ".value udf_" . $id;
340 }
341
342 // basic query
343 $query = "SELECT " . implode(",", $sql_fields) .
344 " FROM usr_data" .
345 $join;
346
347 $count_query = $count_query . " " .
348 $join;
349
350 // filter
351 $query .= " WHERE usr_data.usr_id <> " . $ilDB->quote(ANONYMOUS_USER_ID, "integer");
352
353 // User filter
354 $count_query .= " WHERE 1 = 1 ";
355 $count_user_filter = "usr_data.usr_id != " . $ilDB->quote(ANONYMOUS_USER_ID, "integer");
356 if ($this->users and is_array(($this->users))) {
357 $query .= ' AND ' . $ilDB->in('usr_data.usr_id', $this->users, false, 'integer');
358 $count_user_filter = $ilDB->in('usr_data.usr_id', $this->users, false, 'integer');
359 }
360
361 $count_query .= " AND " . $count_user_filter . " ";
362 $where = " AND";
363
364 if ($this->first_letter != "") {
365 $add = $where . " (" . $ilDB->upper($ilDB->substr("usr_data.lastname", 1, 1)) . " = " . $ilDB->upper($ilDB->quote($this->first_letter, "text")) . ") ";
366 $query .= $add;
367 $count_query .= $add;
368 $where = " AND";
369 }
370
371 if ($this->text_filter != "") { // email, name, login
372 $add = $where . " (" . $ilDB->like("usr_data.login", "text", "%" . $this->text_filter . "%") . " " .
373 "OR " . $ilDB->like("usr_data.firstname", "text", "%" . $this->text_filter . "%") . " " .
374 "OR " . $ilDB->like("usr_data.lastname", "text", "%" . $this->text_filter . "%") . " " .
375 "OR " . $ilDB->like("usr_data.second_email", "text", "%" . $this->text_filter . "%") . " " .
376 "OR " . $ilDB->like("usr_data.email", "text", "%" . $this->text_filter . "%") . ") ";
377 $query .= $add;
378 $count_query .= $add;
379 $where = " AND";
380 }
381
382 if ($this->activation != "") { // activation
383 if ($this->activation == "inactive") {
384 $add = $where . " usr_data.active = " . $ilDB->quote(0, "integer") . " ";
385 } else {
386 $add = $where . " usr_data.active = " . $ilDB->quote(1, "integer") . " ";
387 }
388 $query .= $add;
389 $count_query .= $add;
390 $where = " AND";
391 }
392
393 if ($this->last_login instanceof ilDateTime) { // last login
394 if (ilDateTime::_before($this->last_login, new ilDateTime(time(), IL_CAL_UNIX), IL_CAL_DAY)) {
395 $add = $where . " usr_data.last_login < " .
396 $ilDB->quote($this->last_login->get(IL_CAL_DATETIME), "timestamp");
397 $query .= $add;
398 $count_query .= $add;
399 $where = " AND";
400 }
401 }
402 if ($this->limited_access) { // limited access
403 $add = $where . " usr_data.time_limit_unlimited= " . $ilDB->quote(0, "integer");
404 $query .= $add;
405 $count_query .= $add;
406 $where = " AND";
407 }
408
409 // udf filter
410 foreach ($this->getUdfFilter() as $k => $f) {
411 if ($f != "") {
412 $udf_id = explode("_", $k)[1];
413 if ($udf_def[$udf_id]["field_type"] == UDF_TYPE_TEXT) {
414 $add = $where . " " . $ilDB->like("ud_" . $udf_id . ".value", "text", "%" . $f . "%");
415 } else {
416 $add = $where . " ud_" . $udf_id . ".value = " . $ilDB->quote($f, "text");
417 }
418 $query .= $add;
419 $count_query .= $add;
420 $where = " AND";
421 }
422 }
423
424 if ($this->has_access) { //user is limited but has access
425 $unlimited = "time_limit_unlimited = " . $ilDB->quote(1, 'integer');
426 $from = "time_limit_from < " . $ilDB->quote(time(), 'integer');
427 $until = "time_limit_until > " . $ilDB->quote(time(), 'integer');
428
429 $add = $where . ' (' . $unlimited . ' OR (' . $from . ' AND ' . $until . '))';
430 $query .= $add;
431 $count_query .= $add;
432 $where = " AND";
433 }
434 if ($this->no_courses) { // no courses assigned
435 $add = $where . " usr_data.usr_id NOT IN (" .
436 "SELECT DISTINCT ud.usr_id " .
437 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) " .
438 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) " .
439 "WHERE od.title LIKE 'il_crs_%')";
440 $query .= $add;
441 $count_query .= $add;
442 $where = " AND";
443 }
444 if ($this->no_groups) { // no groups assigned
445 $add = $where . " usr_data.usr_id NOT IN (" .
446 "SELECT DISTINCT ud.usr_id " .
447 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) " .
448 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) " .
449 "WHERE od.title LIKE 'il_grp_%')";
450 $query .= $add;
451 $count_query .= $add;
452 $where = " AND";
453 }
454 if ($this->crs_grp > 0) { // members of course/group
455 $cgtype = ilObject::_lookupType($this->crs_grp, true);
456 $add = $where . " usr_data.usr_id IN (" .
457 "SELECT DISTINCT ud.usr_id " .
458 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) " .
459 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) " .
460 "WHERE od.title = " . $ilDB->quote("il_" . $cgtype . "_member_" . $this->crs_grp, "text") . ")";
461 $query .= $add;
462 $count_query .= $add;
463 $where = " AND";
464 }
465 if ($this->role > 0) { // global role
466 $add = $where . " usr_data.usr_id IN (" .
467 "SELECT DISTINCT ud.usr_id " .
468 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) " .
469 "WHERE rbac_ua.rol_id = " . $ilDB->quote($this->role, "integer") . ")";
470 $query .= $add;
471 $count_query .= $add;
472 $where = " AND";
473 }
474
475 if ($this->user_folder) {
476 $add = $where . " " . $ilDB->in('usr_data.time_limit_owner', $this->user_folder, false, 'integer');
477 $query .= $add;
478 $count_query .= $add;
479 $where = " AND";
480 }
481
482 if ($this->authentication_method != "") { // authentication
483 $add = $where . " usr_data.auth_mode = " . $ilDB->quote($this->authentication_method, "text") . " ";
484 $query .= $add;
485 $count_query .= $add;
486 $where = " AND";
487 }
488
489 // order by
490 switch ($this->order_field) {
491 case "access_until":
492 if ($this->order_dir == "desc") {
493 $query .= " ORDER BY usr_data.active DESC, usr_data.time_limit_unlimited DESC, usr_data.time_limit_until DESC";
494 } else {
495 $query .= " ORDER BY usr_data.active ASC, usr_data.time_limit_unlimited ASC, usr_data.time_limit_until ASC";
496 }
497 break;
498
499 case "online_time":
500 if ($this->order_dir == "desc") {
501 $query .= " ORDER BY ut_online.online_time DESC";
502 } else {
503 $query .= " ORDER BY ut_online.online_time ASC";
504 }
505 break;
506
507 default:
508 if ($this->order_dir != "asc" && $this->order_dir != "desc") {
509 $this->order_dir = "asc";
510 }
511 if (substr($this->order_field, 0, 4) == "udf_") {
512 // #25311 check if order field is in field list
513 if (is_array($this->getUdfFilter()) && array_key_exists($this->order_field, $this->getUdfFilter())) {
514 $query .= " ORDER BY ud_" . ((int) substr($this->order_field, 4)) . ".value " . strtoupper($this->order_dir);
515 } else {
516 $query .= ' ORDER BY ' . self::DEFAULT_ORDER_FIELD . ' ' . strtoupper($this->order_dir);
517 }
518 } else {
519 if (!in_array($this->order_field, $this->default_fields)) {
520 $this->order_field = "login";
521 }
522 $query .= " ORDER BY usr_data." . $this->order_field . " " . strtoupper($this->order_dir);
523 }
524 break;
525 }
526
527 // count query
528 $set = $ilDB->query($count_query);
529 $cnt = 0;
530 if ($rec = $ilDB->fetchAssoc($set)) {
531 $cnt = $rec["cnt"];
532 }
533
534 $offset = (int) $this->offset;
535 $limit = (int) $this->limit;
536
537 // #9866: validate offset against rowcount
538 if ($offset >= $cnt) {
539 $offset = 0;
540 }
541
542 $ilDB->setLimit($limit, $offset);
543
544 if (sizeof($multi_fields)) {
545 $usr_ids = array();
546 }
547
548 // set query
549 $set = $ilDB->query($query);
550 $result = array();
551
552 while ($rec = $ilDB->fetchAssoc($set)) {
553 $result[] = $rec;
554 if (sizeof($multi_fields)) {
555 $usr_ids[] = $rec["usr_id"];
556 }
557 }
558
559 // add multi-field-values to user-data
560 if (sizeof($multi_fields) && sizeof($usr_ids)) {
561 $usr_multi = array();
562 $set = $ilDB->query("SELECT * FROM usr_data_multi" .
563 " WHERE " . $ilDB->in("usr_id", $usr_ids, "", "integer"));
564 while ($row = $ilDB->fetchAssoc($set)) {
565 $usr_multi[$row["usr_id"]][$row["field_id"]][] = $row["value"];
566 }
567 foreach ($result as $idx => $item) {
568 if (isset($usr_multi[$item["usr_id"]])) {
569 $result[$idx] = array_merge($item, $usr_multi[$item["usr_id"]]);
570 }
571 }
572 }
573 return array("cnt" => $cnt, "set" => $result);
574 }
575
576
581 public static function getUserListData(
582 $a_order_field,
583 $a_order_dir,
584 $a_offset,
585 $a_limit,
586 $a_string_filter = "",
587 $a_activation_filter = "",
588 $a_last_login_filter = null,
589 $a_limited_access_filter = false,
590 $a_no_courses_filter = false,
591 $a_course_group_filter = 0,
592 $a_role_filter = 0,
593 $a_user_folder_filter = null,
594 $a_additional_fields = '',
595 $a_user_filter = null,
596 $a_first_letter = "",
597 $a_authentication_filter = null
598 ) {
599 $query = new ilUserQuery();
600 $query->setOrderField($a_order_field);
601 $query->setOrderDirection($a_order_dir);
602 $query->setOffset($a_offset);
603 $query->setLimit($a_limit);
604 $query->setTextFilter($a_string_filter);
605 $query->setActionFilter($a_activation_filter);
606 $query->setLastLogin($a_last_login_filter);
607 $query->setLimitedAccessFilter($a_limited_access_filter);
608 $query->setNoCourseFilter($a_no_courses_filter);
609 $query->setCourseGroupFilter($a_course_group_filter);
610 $query->setRoleFilter($a_role_filter);
611 $query->setUserFolder($a_user_folder_filter);
612 $query->setAdditionalFields($a_additional_fields);
613 $query->setUserFilter($a_user_filter);
614 $query->setFirstLetterLastname($a_first_letter);
615 $query->setAuthenticationFilter($a_authentication_filter);
616 return $query->query();
617 }
618}
$result
An exception for terminatinating execution or to throw for unit testing.
const IL_CAL_UNIX
const IL_CAL_DATETIME
const IL_CAL_DAY
const UDF_TYPE_WYSIWYG
const UDF_TYPE_TEXT
@classDescription Date and time handling
static _before(ilDateTime $start, ilDateTime $end, $a_compare_field='', $a_tz='')
compare two dates and check start is before end This method does not consider tz offsets.
static _lookupType($a_id, $a_reference=false)
lookup object type
static _getInstance()
Get instance.
User query class.
setUserFilter($a_filter)
Array with user ids to query against.
getUdfFilter()
Get udf filter.
setLimit($a_limit)
Set result limit Default is 50.
setAdditionalFields($a_add)
Set additional fields (columns in usr_data or 'online_time')
setUserFolder($a_fold_id)
Set user folder filter reference id of user folder or category (local user administration)
setUdfFilter($a_val)
Set udf filter.
const DEFAULT_ORDER_FIELD
__construct()
Constructor.
setNoGroupFilter($a_no_group)
Enable no group filter.
setLastLogin(ilDateTime $dt=null)
Set last login filter.
setActionFilter($a_activation)
Set activation filter 'active' or 'inactive' or empty.
setOrderField($a_order)
Set order field (column in usr_data) Default order is 'login'.
setLimitedAccessFilter($a_status)
Enable limited access filter.
static getUserListData( $a_order_field, $a_order_dir, $a_offset, $a_limit, $a_string_filter="", $a_activation_filter="", $a_last_login_filter=null, $a_limited_access_filter=false, $a_no_courses_filter=false, $a_course_group_filter=0, $a_role_filter=0, $a_user_folder_filter=null, $a_additional_fields='', $a_user_filter=null, $a_first_letter="", $a_authentication_filter=null)
Get data for user administration list.
setOrderDirection($a_dir)
Set order direction 'asc' or 'desc' Default is 'asc'.
query()
Query usr_data.
setRoleFilter($a_role_id)
Set role filter obj_id of role.
setAuthenticationFilter($a_authentication)
Set authentication filter 'default', 'local' or 'lti'.
setNoCourseFilter($a_no_course)
Enable no course filter.
setOffset($a_offset)
Set offset.
setAccessFilter($a_access)
set filter for user that are limited but has access
setTextFilter($a_filter)
Text (like) filter in login, firstname, lastname or email.
setFirstLetterLastname($a_fll)
set first letter lastname filter
setCourseGroupFilter($a_cg_id)
Set course / group filter object_id of course or group.
$query
global $ilDB
$DIC
Definition: xapitoken.php:46