1<?php
2/* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */
3
4/**
5* User query class. Put any complex that queries for a set of users into
6* this class and keep ilObjUser "small".
7*
8* @author Alex Killing <alex.killing@gmx.de>
9* @version $Id$
10* @ingroup ServicesUser
11*/
12class ilUserQuery
13{
14    const DEFAULT_ORDER_FIELD = 'login';
15
16    /**
17     * @var \ilLogger | null
18     */
19    private $logger = null;
20
21    private $order_field = self::DEFAULT_ORDER_FIELD;
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;
38    private $authentication_method = '';
39
40    /**
41     * @var array
42     */
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
59    /**
60     * Constructor
61     */
62    public function __construct()
63    {
64        global $DIC;
65
66        $this->logger = $DIC->logger()->usr();
67    }
68
69    /**
70     * Set udf filter
71     *
72     * @param array $a_val udf filter array
73     */
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
88    /**
89     * Get udf filter
90     *
91     * @return array udf filter array
92     */
93    public function getUdfFilter()
94    {
95        return $this->udf_filter;
96    }
97
98    /**
99     * Set order field (column in usr_data)
100     * Default order is 'login'
101     * @param string
102     */
103    public function setOrderField($a_order)
104    {
105        $this->order_field = $a_order;
106    }
107
108    /**
109     * Set order direction
110     * 'asc' or 'desc'
111     * Default is 'asc'
112     * @param string $a_dir
113     */
114    public function setOrderDirection($a_dir)
115    {
116        $this->order_dir = $a_dir;
117    }
118
119    /**
120     * Set offset
121     * @param int $a_offset
122     */
123    public function setOffset($a_offset)
124    {
125        $this->offset = $a_offset;
126    }
127
128    /**
129     * Set result limit
130     * Default is 50
131     * @param int $a_limit
132     */
133    public function setLimit($a_limit)
134    {
135        $this->limit = $a_limit;
136    }
137
138    /**
139     * Text (like) filter in login, firstname, lastname or email
140     * @param string filter
141     */
142    public function setTextFilter($a_filter)
143    {
144        $this->text_filter = $a_filter;
145    }
146
147    /**
148     * Set activation filter
149     * 'active' or 'inactive' or empty
150     * @param string $a_activation
151     */
152    public function setActionFilter($a_activation)
153    {
154        $this->activation = $a_activation;
155    }
156
157    /**
158     * Set last login filter
159     * @param ilDateTime $dt
160     */
161    public function setLastLogin(ilDateTime $dt = null)
162    {
163        $this->last_login = $dt;
164    }
165
166    /**
167     * Enable limited access filter
168     * @param bool
169     */
170    public function setLimitedAccessFilter($a_status)
171    {
172        $this->limited_access = $a_status;
173    }
174
175    /**
176     * Enable no course filter
177     * @param bool $a_no_course
178     */
179    public function setNoCourseFilter($a_no_course)
180    {
181        $this->no_courses = $a_no_course;
182    }
183
184    /**
185     * Enable no group filter
186     * @param bool $a_no_group
187     */
188    public function setNoGroupFilter($a_no_group)
189    {
190        $this->no_groups = $a_no_group;
191    }
192
193    /**
194     * Set course / group filter
195     * object_id of course or group
196     * @param int $a_cg_id
197     */
198    public function setCourseGroupFilter($a_cg_id)
199    {
200        $this->crs_grp = $a_cg_id;
201    }
202
203    /**
204     * Set role filter
205     * obj_id of role
206     * @param int $a_role_id
207     */
208    public function setRoleFilter($a_role_id)
209    {
210        $this->role = $a_role_id;
211    }
212
213    /**
214     * Set user folder filter
215     * reference id of user folder or category (local user administration)
216     * @param int $a_fold_id
217     */
218    public function setUserFolder($a_fold_id)
219    {
220        $this->user_folder = $a_fold_id;
221    }
222
223    /**
224     * Set additional fields (columns in usr_data or 'online_time')
225     * @param array $additional_fields
226     */
227    public function setAdditionalFields($a_add)
228    {
229        $this->additional_fields = (array) $a_add;
230    }
231
232    /**
233     * Array with user ids to query against
234     * @param array $a_filter
235     */
236    public function setUserFilter($a_filter)
237    {
238        $this->users = $a_filter;
239    }
240
241    /**
242     * set first letter lastname filter
243     * @param string $a_fll
244     */
245    public function setFirstLetterLastname($a_fll)
246    {
247        $this->first_letter = $a_fll;
248    }
249
250    /**
251     * set filter for user that are limited but has access
252     *
253     * @param $a_access
254     */
255    public function setAccessFilter($a_access)
256    {
257        $this->has_access = (bool) $a_access;
258    }
259
260    /**
261     * Set authentication filter
262     * 'default', 'local' or 'lti'
263     * @param string $a_authentication
264     */
265    public function setAuthenticationFilter($a_authentication)
266    {
267        $this->authentication_method = $a_authentication;
268    }
269
270    /**
271     * Query usr_data
272     * @return array ('cnt', 'set')
273     */
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
577    /**
578     * Get data for user administration list.
579     * @deprecated
580     */
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}
619