ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
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 private $order_field = 'login';
15 private $order_dir = 'asc';
16 private $offset = 0;
17 private $limit = 50;
18 private $text_filter = '';
19 private $activation = '';
20 private $last_login = NULL;
21 private $limited_access = false;
22 private $no_courses = false;
23 private $no_groups = false;
24 private $crs_grp = 0;
25 private $role = 0;
26 private $user_folder = 0;
27 private $additional_fields = array();
28 private $users = array();
29 private $first_letter = '';
30 private $has_access = false;
31
32 private $default_fields = array(
33 "usr_id",
34 "login",
35 "firstname",
36 "lastname",
37 "email",
38 "time_limit_until",
39 "time_limit_unlimited",
40 "time_limit_owner",
41 "last_login",
42 "active"
43 );
44
48 public function __construct()
49 {
50 ;
51 }
52
58 public function setOrderField($a_order)
59 {
60 $this->order_field = $a_order;
61 }
62
69 public function setOrderDirection($a_dir)
70 {
71 $this->order_dir = $a_dir;
72 }
73
78 public function setOffset($a_offset)
79 {
80 $this->offset = $a_offset;
81 }
82
88 public function setLimit($a_limit)
89 {
90 $this->limit = $a_limit;
91 }
92
97 public function setTextFilter($a_filter)
98 {
99 $this->text_filter = $a_filter;
100 }
101
107 public function setActionFilter($a_activation)
108 {
109 $this->activation = $a_activation;
110 }
111
116 public function setLastLogin(ilDateTime $dt = NULL)
117 {
118 $this->last_login = $dt;
119 }
120
125 public function setLimitedAccessFilter($a_status)
126 {
127 $this->limited_access = $a_status;
128 }
129
134 public function setNoCourseFilter($a_no_course)
135 {
136 $this->no_courses = $a_no_course;
137 }
138
143 public function setNoGroupFilter($a_no_group)
144 {
145 $this->no_groups = $a_no_group;
146 }
147
153 public function setCourseGroupFilter($a_cg_id)
154 {
155 $this->crs_grp = $a_cg_id;
156 }
157
163 public function setRoleFilter($a_role_id)
164 {
165 $this->role = $a_role_id;
166 }
167
173 public function setUserFolder($a_fold_id)
174 {
175 $this->user_folder = $a_fold_id;
176 }
177
182 public function setAdditionalFields($a_add)
183 {
184 $this->additional_fields = (array) $a_add;
185 }
186
191 public function setUserFilter($a_filter)
192 {
193 $this->users = $a_filter;
194 }
195
200 public function setFirstLetterLastname($a_fll)
201 {
202 $this->first_letter = $a_fll;
203 }
204
210 public function setAccessFilter($a_access)
211 {
212 $this->has_access = (bool) $a_access;
213 }
214
219 public function query()
220 {
221 global $ilDB;
222
223 $ut_join = "";
224 if (is_array($this->additional_fields))
225 {
226 foreach ($this->additional_fields as $f)
227 {
228 if (!in_array($f, $this->default_fields))
229 {
230 if($f == "online_time")
231 {
232 $this->default_fields[] = "ut_online.online_time";
233 $ut_join = " LEFT JOIN ut_online ON usr_data.usr_id = ut_online.usr_id";
234 }
235 else
236 {
237 $this->default_fields[] = $f;
238 }
239 }
240 }
241 }
242 // count query
243 $count_query = "SELECT count(usr_id) cnt".
244 " FROM usr_data";
245
246 $all_multi_fields = array("interests_general", "interests_help_offered", "interests_help_looking");
247 $multi_fields = array();
248
249 $sql_fields = array();
250 foreach($this->default_fields as $idx => $field)
251 {
252 if(!$field)
253 {
254 continue;
255 }
256
257 if(in_array($field, $all_multi_fields))
258 {
259 $multi_fields[] = $field;
260 }
261 else if(!stristr($field, "."))
262 {
263 $sql_fields[] = "usr_data.".$field;
264 }
265 else
266 {
267 $sql_fields[] = $field;
268 }
269 }
270
271 // basic query
272 $query = "SELECT ".implode($sql_fields, ",").
273 " FROM usr_data".
274 $ut_join;
275
276 // filter
277 $query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
278
279 // User filter
280 if($this->users and is_array(($this->users)))
281 {
282 $query .= ' AND '.$ilDB->in('usr_data.usr_id',$this->users,false,'integer');
283 }
284
285 $count_query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
286 $where = " AND";
287
288 if ($this->first_letter != "")
289 {
290 $add = $where." (".$ilDB->upper($ilDB->substr("usr_data.lastname", 1, 1))." = ".$ilDB->upper($ilDB->quote($this->first_letter, "text")).") ";
291 $query.= $add;
292 $count_query.= $add;
293 $where = " AND";
294 }
295
296 if ($this->text_filter != "") // email, name, login
297 {
298 $add = $where." (".$ilDB->like("usr_data.login", "text", "%".$this->text_filter."%")." ".
299 "OR ".$ilDB->like("usr_data.firstname", "text", "%".$this->text_filter."%")." ".
300 "OR ".$ilDB->like("usr_data.lastname", "text", "%".$this->text_filter."%")." ".
301 "OR ".$ilDB->like("usr_data.email", "text", "%".$this->text_filter."%").") ";
302 $query.= $add;
303 $count_query.= $add;
304 $where = " AND";
305 }
306
307 if ($this->activation != "") // activation
308 {
309 if ($this->activation == "inactive")
310 {
311 $add = $where." usr_data.active = ".$ilDB->quote(0, "integer")." ";
312 }
313 else
314 {
315 $add = $where." usr_data.active = ".$ilDB->quote(1, "integer")." ";
316 }
317 $query.= $add;
318 $count_query.= $add;
319 $where = " AND";
320 }
321
322 if($this->last_login instanceof ilDateTime) // last login
323 {
324 if(ilDateTime::_before($this->last_login, new ilDateTime(time(),IL_CAL_UNIX),IL_CAL_DAY))
325 {
326 $add = $where." usr_data.last_login < ".
327 $ilDB->quote($this->last_login->get(IL_CAL_DATETIME), "timestamp");
328 $query.= $add;
329 $count_query.= $add;
330 $where = " AND";
331 }
332 }
333 if ($this->limited_access) // limited access
334 {
335 $add = $where." usr_data.time_limit_unlimited= ".$ilDB->quote(0, "integer");
336 $query.= $add;
337 $count_query.= $add;
338 $where = " AND";
339 }
340
341 if($this->has_access) //user is limited but has access
342 {
343 $unlimited = "time_limit_unlimited = ". $ilDB->quote(1, 'integer');
344 $from = "time_limit_from < ". $ilDB->quote(time(), 'integer');
345 $until = "time_limit_until > ". $ilDB->quote(time(), 'integer');
346
347 $add = $where.' (' .$unlimited.' OR ('.$from.' AND ' .$until.'))';
348 $query.= $add;
349 $count_query.= $add;
350 $where = " AND";
351 }
352 if ($this->no_courses) // no courses assigned
353 {
354 $add = $where." usr_data.usr_id NOT IN (".
355 "SELECT DISTINCT ud.usr_id ".
356 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
357 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
358 "WHERE od.title LIKE 'il_crs_%')";
359 $query.= $add;
360 $count_query.= $add;
361 $where = " AND";
362 }
363 if ($this->no_groups) // no groups assigned
364 {
365 $add = $where." usr_data.usr_id NOT IN (".
366 "SELECT DISTINCT ud.usr_id ".
367 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
368 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
369 "WHERE od.title LIKE 'il_grp_%')";
370 $query.= $add;
371 $count_query.= $add;
372 $where = " AND";
373 }
374 if ($this->crs_grp > 0) // members of course/group
375 {
376 $cgtype = ilObject::_lookupType($this->crs_grp, true);
377 $add = $where." usr_data.usr_id IN (".
378 "SELECT DISTINCT ud.usr_id ".
379 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
380 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
381 "WHERE od.title = ".$ilDB->quote("il_".$cgtype."_member_".$this->crs_grp, "text").")";
382 $query.= $add;
383 $count_query.= $add;
384 $where = " AND";
385 }
386 if ($this->role > 0) // global role
387 {
388 $add = $where." usr_data.usr_id IN (".
389 "SELECT DISTINCT ud.usr_id ".
390 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
391 "WHERE rbac_ua.rol_id = ".$ilDB->quote($this->role, "integer").")";
392 $query.= $add;
393 $count_query.= $add;
394 $where = " AND";
395 }
396
397 if($this->user_folder)
398 {
399 $add = $where." ".$ilDB->in('usr_data.time_limit_owner',$this->user_folder,false,'integer');
400 $query.= $add;
401 $count_query.= $add;
402 $where = " AND";
403 }
404
405 // order by
406 switch($this->order_field)
407 {
408 case "access_until":
409 if ($this->order_dir == "desc")
410 {
411 $query.= " ORDER BY usr_data.active DESC, usr_data.time_limit_unlimited DESC, usr_data.time_limit_until DESC";
412 }
413 else
414 {
415 $query.= " ORDER BY usr_data.active ASC, usr_data.time_limit_unlimited ASC, usr_data.time_limit_until ASC";
416 }
417 break;
418
419 case "online_time":
420 if ($this->order_dir == "desc")
421 {
422 $query.= " ORDER BY ut_online.online_time DESC";
423 }
424 else
425 {
426 $query.= " ORDER BY ut_online.online_time ASC";
427 }
428 break;
429
430 default:
431 if (!in_array($this->order_field, $this->default_fields))
432 {
433 $this->order_field = "login";
434 }
435 if ($this->order_dir != "asc" && $this->order_dir != "desc")
436 {
437 $this->order_dir = "asc";
438 }
439 $query .= " ORDER BY usr_data.".$this->order_field." ".strtoupper($this->order_dir);
440 break;
441 }
442
443 // count query
444 $set = $ilDB->query($count_query);
445 $cnt = 0;
446 if ($rec = $ilDB->fetchAssoc($set))
447 {
448 $cnt = $rec["cnt"];
449 }
450
451 $offset = (int) $this->offset;
452 $limit = (int) $this->limit;
453
454 // #9866: validate offset against rowcount
455 if($offset >= $cnt)
456 {
457 $offset = 0;
458 }
459
460 $ilDB->setLimit($limit, $offset);
461
462 if(sizeof($multi_fields))
463 {
464 $usr_ids = array();
465 }
466
467 // set query
468 $set = $ilDB->query($query);
469 $result = array();
470 while($rec = $ilDB->fetchAssoc($set))
471 {
472 $result[] = $rec;
473
474 if(sizeof($multi_fields))
475 {
476 $usr_ids[] = $rec["usr_id"];
477 }
478 }
479
480 // add multi-field-values to user-data
481 if(sizeof($multi_fields) && sizeof($usr_ids))
482 {
483 $usr_multi = array();
484 $set = $ilDB->query("SELECT * FROM usr_data_multi".
485 " WHERE ".$ilDB->in("usr_id", $usr_ids, "", "integer"));
486 while($row = $ilDB->fetchAssoc($set))
487 {
488 $usr_multi[$row["usr_id"]][$row["field_id"]][] = $row["value"];
489 }
490 foreach($result as $idx => $item)
491 {
492 if(isset($usr_multi[$item["usr_id"]]))
493 {
494 $result[$idx] = array_merge($item, $usr_multi[$item["usr_id"]]);
495 }
496 }
497 }
498
499 return array("cnt" => $cnt, "set" => $result);
500 }
501
502
507 public static function getUserListData($a_order_field, $a_order_dir, $a_offset, $a_limit,
508 $a_string_filter = "", $a_activation_filter = "", $a_last_login_filter = null,
509 $a_limited_access_filter = false, $a_no_courses_filter = false,
510 $a_course_group_filter = 0, $a_role_filter = 0, $a_user_folder_filter = null,
511 $a_additional_fields = '', $a_user_filter = null, $a_first_letter = "")
512 {
513
514 $query = new ilUserQuery();
515 $query->setOrderField($a_order_field);
516 $query->setOrderDirection($a_order_dir);
517 $query->setOffset($a_offset);
518 $query->setLimit($a_limit);
519 $query->setTextFilter($a_string_filter);
520 $query->setActionFilter($a_activation_filter);
521 $query->setLastLogin($a_last_login_filter);
522 $query->setLimitedAccessFilter($a_limited_access_filter);
523 $query->setNoCourseFilter($a_no_courses_filter);
524 $query->setCourseGroupFilter($a_course_group_filter);
525 $query->setRoleFilter($a_role_filter);
526 $query->setUserFolder($a_user_folder_filter);
527 $query->setAdditionalFields($a_additional_fields);
528 $query->setUserFilter($a_user_filter);
529 $query->setFirstLetterLastname($a_first_letter);
530 return $query->query();
531 }
532}
$result
const IL_CAL_UNIX
const IL_CAL_DATETIME
const IL_CAL_DAY
@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
User query class.
setUserFilter($a_filter)
Array with user ids to query against.
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)
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="")
Get data for user administration list.
__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.
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.
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.
global $ilDB