ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
Public Member Functions | Static Public Member Functions | Private Attributes
ilUserQuery Class Reference
Services/User

User query class. More...

+ Collaboration diagram for ilUserQuery:

Public Member Functions

 __construct ()
 Constructor. More...
 
 setOrderField ($a_order)
 Set order field (column in usr_data) Default order is 'login'. More...
 
 setOrderDirection ($a_dir)
 Set order direction 'asc' or 'desc' Default is 'asc'. More...
 
 setOffset ($a_offset)
 Set offset. More...
 
 setLimit ($a_limit)
 Set result limit Default is 50. More...
 
 setTextFilter ($a_filter)
 Text (like) filter in login, firstname, lastname or email. More...
 
 setActionFilter ($a_activation)
 Set activation filter 'active' or 'inactive' or empty. More...
 
 setLastLogin (ilDateTime $dt=NULL)
 Set last login filter. More...
 
 setLimitedAccessFilter ($a_status)
 Enable limited access filter. More...
 
 setNoCourseFilter ($a_no_course)
 Enable no course filter. More...
 
 setNoGroupFilter ($a_no_group)
 Enable no group filter. More...
 
 setCourseGroupFilter ($a_cg_id)
 Set course / group filter object_id of course or group. More...
 
 setRoleFilter ($a_role_id)
 Set role filter obj_id of role. More...
 
 setUserFolder ($a_fold_id)
 Set user folder filter reference id of user folder or category (local user administration) More...
 
 setAdditionalFields ($a_add)
 Set additional fields (columns in usr_data or 'online_time') More...
 
 setUserFilter ($a_filter)
 Array with user ids to query against. More...
 
 setFirstLetterLastname ($a_fll)
 set first letter lastname filter More...
 
 setAccessFilter ($a_access)
 set filter for user that are limited but has access More...
 
 query ()
 Query usr_data. More...
 

Static Public Member Functions

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. More...
 

Private Attributes

 $order_field = 'login'
 
 $order_dir = 'asc'
 
 $offset = 0
 
 $limit = 50
 
 $text_filter = ''
 
 $activation = ''
 
 $last_login = NULL
 
 $limited_access = false
 
 $no_courses = false
 
 $no_groups = false
 
 $crs_grp = 0
 
 $role = 0
 
 $user_folder = 0
 
 $additional_fields = array()
 
 $users = array()
 
 $first_letter = ''
 
 $has_access = false
 
 $default_fields
 

Detailed Description

User query class.

Put any complex that queries for a set of users into this class and keep ilObjUser "small".

Author
Alex Killing alex..nosp@m.kill.nosp@m.ing@g.nosp@m.mx.d.nosp@m.e
Version
$Id$

Definition at line 12 of file class.ilUserQuery.php.

Constructor & Destructor Documentation

◆ __construct()

ilUserQuery::__construct ( )

Constructor.

Definition at line 48 of file class.ilUserQuery.php.

49  {
50  ;
51  }

Member Function Documentation

◆ getUserListData()

static ilUserQuery::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 = "" 
)
static

Get data for user administration list.

Deprecated:

Definition at line 507 of file class.ilUserQuery.php.

References $query.

Referenced by ilUserLPTableGUI\getItems(), ilAssignedUsersTableGUI\getItems(), ilGroupParticipantsTableGUI\parse(), ilCourseParticipantsTableGUI\parse(), ilSubscriberTableGUI\readSubscriberData(), and ilWaitingListTableGUI\readUserData().

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  }
ilUserQuery
User query class.
Definition: class.ilUserQuery.php:12
+ Here is the caller graph for this function:

◆ query()

ilUserQuery::query ( )

Query usr_data.

Returns
array ('cnt', 'set')

Definition at line 219 of file class.ilUserQuery.php.

References $ilDB, $limit, $offset, $query, $result, $row, ilDateTime\_before(), ilObject\_lookupType(), IL_CAL_DATETIME, IL_CAL_DAY, and IL_CAL_UNIX.

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  }
IL_CAL_DATETIME
const IL_CAL_DATETIME
Definition: class.ilDateTime.php:9
$result
$result
Definition: CleanUpTest.php:407
ilDateTime\_before
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.
Definition: class.ilDateTime.php:156
IL_CAL_UNIX
const IL_CAL_UNIX
Definition: class.ilDateTime.php:11
IL_CAL_DAY
const IL_CAL_DAY
Definition: class.ilDateTime.php:20
ilDateTime
Date and time handling
Definition: class.ilDateTime.php:32
ilObject\_lookupType
static _lookupType($a_id, $a_reference=false)
lookup object type
Definition: class.ilObject.php:1196
$ilDB
global $ilDB
Definition: storeScorm2004.php:16
+ Here is the call graph for this function:

◆ setAccessFilter()

ilUserQuery::setAccessFilter (   $a_access)

set filter for user that are limited but has access

Parameters
$a_access

Definition at line 210 of file class.ilUserQuery.php.

211  {
212  $this->has_access = (bool) $a_access;
213  }

◆ setActionFilter()

ilUserQuery::setActionFilter (   $a_activation)

Set activation filter 'active' or 'inactive' or empty.

Parameters
string$a_activation

Definition at line 107 of file class.ilUserQuery.php.

108  {
109  $this->activation = $a_activation;
110  }

◆ setAdditionalFields()

ilUserQuery::setAdditionalFields (   $a_add)

Set additional fields (columns in usr_data or 'online_time')

Parameters
array$additional_fields

Definition at line 182 of file class.ilUserQuery.php.

183  {
184  $this->additional_fields = (array) $a_add;
185  }

◆ setCourseGroupFilter()

ilUserQuery::setCourseGroupFilter (   $a_cg_id)

Set course / group filter object_id of course or group.

Parameters
int$a_cg_id

Definition at line 153 of file class.ilUserQuery.php.

154  {
155  $this->crs_grp = $a_cg_id;
156  }

◆ setFirstLetterLastname()

ilUserQuery::setFirstLetterLastname (   $a_fll)

set first letter lastname filter

Parameters
string$a_fll

Definition at line 200 of file class.ilUserQuery.php.

201  {
202  $this->first_letter = $a_fll;
203  }

◆ setLastLogin()

ilUserQuery::setLastLogin ( ilDateTime  $dt = NULL)

Set last login filter.

Parameters
ilDateTime$dt

Definition at line 116 of file class.ilUserQuery.php.

117  {
118  $this->last_login = $dt;
119  }

◆ setLimit()

ilUserQuery::setLimit (   $a_limit)

Set result limit Default is 50.

Parameters
int$a_limit

Definition at line 88 of file class.ilUserQuery.php.

89  {
90  $this->limit = $a_limit;
91  }

◆ setLimitedAccessFilter()

ilUserQuery::setLimitedAccessFilter (   $a_status)

Enable limited access filter.

Parameters
bool

Definition at line 125 of file class.ilUserQuery.php.

126  {
127  $this->limited_access = $a_status;
128  }

◆ setNoCourseFilter()

ilUserQuery::setNoCourseFilter (   $a_no_course)

Enable no course filter.

Parameters
bool$a_no_course

Definition at line 134 of file class.ilUserQuery.php.

135  {
136  $this->no_courses = $a_no_course;
137  }

◆ setNoGroupFilter()

ilUserQuery::setNoGroupFilter (   $a_no_group)

Enable no group filter.

Parameters
bool$a_no_group

Definition at line 143 of file class.ilUserQuery.php.

144  {
145  $this->no_groups = $a_no_group;
146  }

◆ setOffset()

ilUserQuery::setOffset (   $a_offset)

Set offset.

Parameters
int$a_offset

Definition at line 78 of file class.ilUserQuery.php.

79  {
80  $this->offset = $a_offset;
81  }

◆ setOrderDirection()

ilUserQuery::setOrderDirection (   $a_dir)

Set order direction 'asc' or 'desc' Default is 'asc'.

Parameters
string$a_dir

Definition at line 69 of file class.ilUserQuery.php.

70  {
71  $this->order_dir = $a_dir;
72  }

◆ setOrderField()

ilUserQuery::setOrderField (   $a_order)

Set order field (column in usr_data) Default order is 'login'.

Parameters
string

Definition at line 58 of file class.ilUserQuery.php.

59  {
60  $this->order_field = $a_order;
61  }

◆ setRoleFilter()

ilUserQuery::setRoleFilter (   $a_role_id)

Set role filter obj_id of role.

Parameters
int$a_role_id

Definition at line 163 of file class.ilUserQuery.php.

164  {
165  $this->role = $a_role_id;
166  }

◆ setTextFilter()

ilUserQuery::setTextFilter (   $a_filter)

Text (like) filter in login, firstname, lastname or email.

Parameters
stringfilter

Definition at line 97 of file class.ilUserQuery.php.

98  {
99  $this->text_filter = $a_filter;
100  }

◆ setUserFilter()

ilUserQuery::setUserFilter (   $a_filter)

Array with user ids to query against.

Parameters
array$a_filter

Definition at line 191 of file class.ilUserQuery.php.

192  {
193  $this->users = $a_filter;
194  }

◆ setUserFolder()

ilUserQuery::setUserFolder (   $a_fold_id)

Set user folder filter reference id of user folder or category (local user administration)

Parameters
type$a_fold_id

Definition at line 173 of file class.ilUserQuery.php.

174  {
175  $this->user_folder = $a_fold_id;
176  }

Field Documentation

◆ $activation

ilUserQuery::$activation = ''
private

Definition at line 19 of file class.ilUserQuery.php.

◆ $additional_fields

ilUserQuery::$additional_fields = array()
private

Definition at line 27 of file class.ilUserQuery.php.

◆ $crs_grp

ilUserQuery::$crs_grp = 0
private

Definition at line 24 of file class.ilUserQuery.php.

◆ $default_fields

ilUserQuery::$default_fields
private
Initial value:
= array(
"usr_id",
"login",
"firstname",
"lastname",
"email",
"time_limit_until",
"time_limit_unlimited",
"time_limit_owner",
"last_login",
"active"
)

Definition at line 32 of file class.ilUserQuery.php.

◆ $first_letter

ilUserQuery::$first_letter = ''
private

Definition at line 29 of file class.ilUserQuery.php.

◆ $has_access

ilUserQuery::$has_access = false
private

Definition at line 30 of file class.ilUserQuery.php.

◆ $last_login

ilUserQuery::$last_login = NULL
private

Definition at line 20 of file class.ilUserQuery.php.

◆ $limit

ilUserQuery::$limit = 50
private

Definition at line 17 of file class.ilUserQuery.php.

Referenced by query().

◆ $limited_access

ilUserQuery::$limited_access = false
private

Definition at line 21 of file class.ilUserQuery.php.

◆ $no_courses

ilUserQuery::$no_courses = false
private

Definition at line 22 of file class.ilUserQuery.php.

◆ $no_groups

ilUserQuery::$no_groups = false
private

Definition at line 23 of file class.ilUserQuery.php.

◆ $offset

ilUserQuery::$offset = 0
private

Definition at line 16 of file class.ilUserQuery.php.

Referenced by query().

◆ $order_dir

ilUserQuery::$order_dir = 'asc'
private

Definition at line 15 of file class.ilUserQuery.php.

◆ $order_field

ilUserQuery::$order_field = 'login'
private

Definition at line 14 of file class.ilUserQuery.php.

◆ $role

ilUserQuery::$role = 0
private

Definition at line 25 of file class.ilUserQuery.php.

◆ $text_filter

ilUserQuery::$text_filter = ''
private

Definition at line 18 of file class.ilUserQuery.php.

◆ $user_folder

ilUserQuery::$user_folder = 0
private

Definition at line 26 of file class.ilUserQuery.php.

◆ $users

ilUserQuery::$users = array()
private

Definition at line 28 of file class.ilUserQuery.php.

The documentation for this class was generated from the following file: