ILIAS  Release_4_4_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
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 
12 class ilUserQuery
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 
31  private $default_fields = array(
32  "usr_id",
33  "login",
34  "firstname",
35  "lastname",
36  "email",
37  "time_limit_until",
38  "time_limit_unlimited",
39  "time_limit_owner",
40  "last_login",
41  "active"
42  );
43 
47  public function __construct()
48  {
49  ;
50  }
51 
57  public function setOrderField($a_order)
58  {
59  $this->order_field = $a_order;
60  }
61 
68  public function setOrderDirection($a_dir)
69  {
70  $this->order_dir = $a_dir;
71  }
72 
77  public function setOffset($a_offset)
78  {
79  $this->offset = $a_offset;
80  }
81 
87  public function setLimit($a_limit)
88  {
89  $this->limit = $a_limit;
90  }
91 
96  public function setTextFilter($a_filter)
97  {
98  $this->text_filter = $a_filter;
99  }
100 
106  public function setActionFilter($a_activation)
107  {
108  $this->activation = $a_activation;
109  }
110 
115  public function setLastLogin(ilDateTime $dt = NULL)
116  {
117  $this->last_login = $dt;
118  }
119 
124  public function setLimitedAccessFilter($a_status)
125  {
126  $this->limited_access = $a_status;
127  }
128 
133  public function setNoCourseFilter($a_no_course)
134  {
135  $this->no_courses = $a_no_course;
136  }
137 
142  public function setNoGroupFilter($a_no_group)
143  {
144  $this->no_groups = $a_no_group;
145  }
146 
152  public function setCourseGroupFilter($a_cg_id)
153  {
154  $this->crs_grp = $a_cg_id;
155  }
156 
162  public function setRoleFilter($a_role_id)
163  {
164  $this->role = $a_role_id;
165  }
166 
172  public function setUserFolder($a_fold_id)
173  {
174  $this->user_folder = $a_fold_id;
175  }
176 
181  public function setAdditionalFields($a_add)
182  {
183  $this->additional_fields = (array) $a_add;
184  }
185 
190  public function setUserFilter($a_filter)
191  {
192  $this->users = $a_filter;
193  }
194 
199  public function setFirstLetterLastname($a_fll)
200  {
201  $this->first_letter = $a_fll;
202  }
203 
208  public function query()
209  {
210  global $ilDB;
211 
212  $ut_join = "";
213  if (is_array($this->additional_fields))
214  {
215  foreach ($this->additional_fields as $f)
216  {
217  if (!in_array($f, $this->default_fields))
218  {
219  if($f == "online_time")
220  {
221  $this->default_fields[] = "ut_online.online_time";
222  $ut_join = " LEFT JOIN ut_online ON usr_data.usr_id = ut_online.usr_id";
223  }
224  else
225  {
226  $this->default_fields[] = $f;
227  }
228  }
229  }
230  }
231  // count query
232  $count_query = "SELECT count(usr_id) cnt".
233  " FROM usr_data";
234 
235  $sql_fields = array();
236  foreach($this->default_fields as $idx => $field)
237  {
238  if(!$field)
239  {
240  continue;
241  }
242 
243  if(!stristr($field, "."))
244  {
245  $sql_fields[] = "usr_data.".$field;
246  }
247  else
248  {
249  $sql_fields[] = $field;
250  }
251  }
252 
253  // basic query
254  $query = "SELECT ".implode($sql_fields, ",").
255  " FROM usr_data".
256  $ut_join;
257 
258  // filter
259  $query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
260 
261  // User filter
262  if($this->users and is_array(($this->users)))
263  {
264  $query .= ' AND '.$ilDB->in('usr_data.usr_id',$this->users,false,'integer');
265  }
266 
267  $count_query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
268  $where = " AND";
269 
270  if ($this->first_letter != "")
271  {
272  $add = $where." (".$ilDB->upper($ilDB->substr("usr_data.lastname", 1, 1))." = ".$ilDB->upper($ilDB->quote($this->first_letter, "text")).") ";
273  $query.= $add;
274  $count_query.= $add;
275  $where = " AND";
276  }
277 
278  if ($this->text_filter != "") // email, name, login
279  {
280  $add = $where." (".$ilDB->like("usr_data.login", "text", "%".$this->text_filter."%")." ".
281  "OR ".$ilDB->like("usr_data.firstname", "text", "%".$this->text_filter."%")." ".
282  "OR ".$ilDB->like("usr_data.lastname", "text", "%".$this->text_filter."%")." ".
283  "OR ".$ilDB->like("usr_data.email", "text", "%".$this->text_filter."%").") ";
284  $query.= $add;
285  $count_query.= $add;
286  $where = " AND";
287  }
288 
289  if ($this->activation != "") // activation
290  {
291  if ($this->activation == "inactive")
292  {
293  $add = $where." usr_data.active = ".$ilDB->quote(0, "integer")." ";
294  }
295  else
296  {
297  $add = $where." usr_data.active = ".$ilDB->quote(1, "integer")." ";
298  }
299  $query.= $add;
300  $count_query.= $add;
301  $where = " AND";
302  }
303 
304  if($this->last_login instanceof ilDateTime) // last login
305  {
306  if(ilDateTime::_before($this->last_login, new ilDateTime(time(),IL_CAL_UNIX),IL_CAL_DAY))
307  {
308  $add = $where." usr_data.last_login < ".
309  $ilDB->quote($this->last_login->get(IL_CAL_DATETIME), "timestamp");
310  $query.= $add;
311  $count_query.= $add;
312  $where = " AND";
313  }
314  }
315  if ($this->limited_access) // limited access
316  {
317  $add = $where." usr_data.time_limit_unlimited= ".$ilDB->quote(0, "integer");
318  $query.= $add;
319  $count_query.= $add;
320  $where = " AND";
321  }
322  if ($this->no_courses) // no courses assigned
323  {
324  $add = $where." usr_data.usr_id NOT IN (".
325  "SELECT DISTINCT ud.usr_id ".
326  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
327  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
328  "WHERE od.title LIKE 'il_crs_%')";
329  $query.= $add;
330  $count_query.= $add;
331  $where = " AND";
332  }
333  if ($this->no_groups) // no groups assigned
334  {
335  $add = $where." usr_data.usr_id NOT IN (".
336  "SELECT DISTINCT ud.usr_id ".
337  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
338  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
339  "WHERE od.title LIKE 'il_grp_%')";
340  $query.= $add;
341  $count_query.= $add;
342  $where = " AND";
343  }
344  if ($this->crs_grp > 0) // members of course/group
345  {
346  $cgtype = ilObject::_lookupType($this->crs_grp, true);
347  $add = $where." usr_data.usr_id IN (".
348  "SELECT DISTINCT ud.usr_id ".
349  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
350  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
351  "WHERE od.title = ".$ilDB->quote("il_".$cgtype."_member_".$this->crs_grp, "text").")";
352  $query.= $add;
353  $count_query.= $add;
354  $where = " AND";
355  }
356  if ($this->role > 0) // global role
357  {
358  $add = $where." usr_data.usr_id IN (".
359  "SELECT DISTINCT ud.usr_id ".
360  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
361  "WHERE rbac_ua.rol_id = ".$ilDB->quote($this->role, "integer").")";
362  $query.= $add;
363  $count_query.= $add;
364  $where = " AND";
365  }
366 
367  if($this->user_folder)
368  {
369  $add = $where." ".$ilDB->in('usr_data.time_limit_owner',$this->user_folder,false,'integer');
370  $query.= $add;
371  $count_query.= $add;
372  $where = " AND";
373  }
374 
375  // order by
376  switch($this->order_field)
377  {
378  case "access_until":
379  if ($this->order_dir == "desc")
380  {
381  $query.= " ORDER BY usr_data.active DESC, usr_data.time_limit_unlimited DESC, usr_data.time_limit_until DESC";
382  }
383  else
384  {
385  $query.= " ORDER BY usr_data.active ASC, usr_data.time_limit_unlimited ASC, usr_data.time_limit_until ASC";
386  }
387  break;
388 
389  case "online_time":
390  if ($this->order_dir == "desc")
391  {
392  $query.= " ORDER BY ut_online.online_time DESC";
393  }
394  else
395  {
396  $query.= " ORDER BY ut_online.online_time ASC";
397  }
398  break;
399 
400  default:
401  if (!in_array($this->order_field, $this->default_fields))
402  {
403  $this->order_field = "login";
404  }
405  if ($this->order_dir != "asc" && $this->order_dir != "desc")
406  {
407  $this->order_dir = "asc";
408  }
409  $query .= " ORDER BY usr_data.".$this->order_field." ".strtoupper($this->order_dir);
410  break;
411  }
412 
413  // count query
414  $set = $ilDB->query($count_query);
415  $cnt = 0;
416  if ($rec = $ilDB->fetchAssoc($set))
417  {
418  $cnt = $rec["cnt"];
419  }
420 
421  $offset = (int) $this->offset;
422  $limit = (int) $this->limit;
423 
424  // #9866: validate offset against rowcount
425  if($offset >= $cnt)
426  {
427  $offset = 0;
428  }
429 
430  $ilDB->setLimit($limit, $offset);
431 
432  // set query
433  $set = $ilDB->query($query);
434  $result = array();
435  while($rec = $ilDB->fetchAssoc($set))
436  {
437  $result[] = $rec;
438  }
439  return array("cnt" => $cnt, "set" => $result);
440 
441  }
442 
443 
448  public static function getUserListData($a_order_field, $a_order_dir, $a_offset, $a_limit,
449  $a_string_filter = "", $a_activation_filter = "", $a_last_login_filter = null,
450  $a_limited_access_filter = false, $a_no_courses_filter = false,
451  $a_course_group_filter = 0, $a_role_filter = 0, $a_user_folder_filter = null,
452  $a_additional_fields = '', $a_user_filter = null, $a_first_letter = "")
453  {
454 
455  $query = new ilUserQuery();
456  $query->setOrderField($a_order_field);
457  $query->setOrderDirection($a_order_dir);
458  $query->setOffset($a_offset);
459  $query->setLimit($a_limit);
460  $query->setTextFilter($a_string_filter);
461  $query->setActionFilter($a_activation_filter);
462  $query->setLastLogin($a_last_login_filter);
463  $query->setLimitedAccessFilter($a_limited_access_filter);
464  $query->setNoCourseFilter($a_no_courses_filter);
465  $query->setCourseGroupFilter($a_course_group_filter);
466  $query->setRoleFilter($a_role_filter);
467  $query->setUserFolder($a_user_folder_filter);
468  $query->setAdditionalFields($a_additional_fields);
469  $query->setUserFilter($a_user_filter);
470  $query->setFirstLetterLastname($a_first_letter);
471  return $query->query();
472  }
473 }