ILIAS  Release_5_0_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  $all_multi_fields = array("interests_general", "interests_help_offered", "interests_help_looking");
236  $multi_fields = array();
237 
238  $sql_fields = array();
239  foreach($this->default_fields as $idx => $field)
240  {
241  if(!$field)
242  {
243  continue;
244  }
245 
246  if(in_array($field, $all_multi_fields))
247  {
248  $multi_fields[] = $field;
249  }
250  else if(!stristr($field, "."))
251  {
252  $sql_fields[] = "usr_data.".$field;
253  }
254  else
255  {
256  $sql_fields[] = $field;
257  }
258  }
259 
260  // basic query
261  $query = "SELECT ".implode($sql_fields, ",").
262  " FROM usr_data".
263  $ut_join;
264 
265  // filter
266  $query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
267 
268  // User filter
269  if($this->users and is_array(($this->users)))
270  {
271  $query .= ' AND '.$ilDB->in('usr_data.usr_id',$this->users,false,'integer');
272  }
273 
274  $count_query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
275  $where = " AND";
276 
277  if ($this->first_letter != "")
278  {
279  $add = $where." (".$ilDB->upper($ilDB->substr("usr_data.lastname", 1, 1))." = ".$ilDB->upper($ilDB->quote($this->first_letter, "text")).") ";
280  $query.= $add;
281  $count_query.= $add;
282  $where = " AND";
283  }
284 
285  if ($this->text_filter != "") // email, name, login
286  {
287  $add = $where." (".$ilDB->like("usr_data.login", "text", "%".$this->text_filter."%")." ".
288  "OR ".$ilDB->like("usr_data.firstname", "text", "%".$this->text_filter."%")." ".
289  "OR ".$ilDB->like("usr_data.lastname", "text", "%".$this->text_filter."%")." ".
290  "OR ".$ilDB->like("usr_data.email", "text", "%".$this->text_filter."%").") ";
291  $query.= $add;
292  $count_query.= $add;
293  $where = " AND";
294  }
295 
296  if ($this->activation != "") // activation
297  {
298  if ($this->activation == "inactive")
299  {
300  $add = $where." usr_data.active = ".$ilDB->quote(0, "integer")." ";
301  }
302  else
303  {
304  $add = $where." usr_data.active = ".$ilDB->quote(1, "integer")." ";
305  }
306  $query.= $add;
307  $count_query.= $add;
308  $where = " AND";
309  }
310 
311  if($this->last_login instanceof ilDateTime) // last login
312  {
313  if(ilDateTime::_before($this->last_login, new ilDateTime(time(),IL_CAL_UNIX),IL_CAL_DAY))
314  {
315  $add = $where." usr_data.last_login < ".
316  $ilDB->quote($this->last_login->get(IL_CAL_DATETIME), "timestamp");
317  $query.= $add;
318  $count_query.= $add;
319  $where = " AND";
320  }
321  }
322  if ($this->limited_access) // limited access
323  {
324  $add = $where." usr_data.time_limit_unlimited= ".$ilDB->quote(0, "integer");
325  $query.= $add;
326  $count_query.= $add;
327  $where = " AND";
328  }
329  if ($this->no_courses) // no courses assigned
330  {
331  $add = $where." usr_data.usr_id NOT IN (".
332  "SELECT DISTINCT ud.usr_id ".
333  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
334  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
335  "WHERE od.title LIKE 'il_crs_%')";
336  $query.= $add;
337  $count_query.= $add;
338  $where = " AND";
339  }
340  if ($this->no_groups) // no groups assigned
341  {
342  $add = $where." usr_data.usr_id NOT IN (".
343  "SELECT DISTINCT ud.usr_id ".
344  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
345  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
346  "WHERE od.title LIKE 'il_grp_%')";
347  $query.= $add;
348  $count_query.= $add;
349  $where = " AND";
350  }
351  if ($this->crs_grp > 0) // members of course/group
352  {
353  $cgtype = ilObject::_lookupType($this->crs_grp, true);
354  $add = $where." usr_data.usr_id 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 = ".$ilDB->quote("il_".$cgtype."_member_".$this->crs_grp, "text").")";
359  $query.= $add;
360  $count_query.= $add;
361  $where = " AND";
362  }
363  if ($this->role > 0) // global role
364  {
365  $add = $where." usr_data.usr_id IN (".
366  "SELECT DISTINCT ud.usr_id ".
367  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
368  "WHERE rbac_ua.rol_id = ".$ilDB->quote($this->role, "integer").")";
369  $query.= $add;
370  $count_query.= $add;
371  $where = " AND";
372  }
373 
374  if($this->user_folder)
375  {
376  $add = $where." ".$ilDB->in('usr_data.time_limit_owner',$this->user_folder,false,'integer');
377  $query.= $add;
378  $count_query.= $add;
379  $where = " AND";
380  }
381 
382  // order by
383  switch($this->order_field)
384  {
385  case "access_until":
386  if ($this->order_dir == "desc")
387  {
388  $query.= " ORDER BY usr_data.active DESC, usr_data.time_limit_unlimited DESC, usr_data.time_limit_until DESC";
389  }
390  else
391  {
392  $query.= " ORDER BY usr_data.active ASC, usr_data.time_limit_unlimited ASC, usr_data.time_limit_until ASC";
393  }
394  break;
395 
396  case "online_time":
397  if ($this->order_dir == "desc")
398  {
399  $query.= " ORDER BY ut_online.online_time DESC";
400  }
401  else
402  {
403  $query.= " ORDER BY ut_online.online_time ASC";
404  }
405  break;
406 
407  default:
408  if (!in_array($this->order_field, $this->default_fields))
409  {
410  $this->order_field = "login";
411  }
412  if ($this->order_dir != "asc" && $this->order_dir != "desc")
413  {
414  $this->order_dir = "asc";
415  }
416  $query .= " ORDER BY usr_data.".$this->order_field." ".strtoupper($this->order_dir);
417  break;
418  }
419 
420  // count query
421  $set = $ilDB->query($count_query);
422  $cnt = 0;
423  if ($rec = $ilDB->fetchAssoc($set))
424  {
425  $cnt = $rec["cnt"];
426  }
427 
428  $offset = (int) $this->offset;
429  $limit = (int) $this->limit;
430 
431  // #9866: validate offset against rowcount
432  if($offset >= $cnt)
433  {
434  $offset = 0;
435  }
436 
437  $ilDB->setLimit($limit, $offset);
438 
439  if(sizeof($multi_fields))
440  {
441  $usr_ids = array();
442  }
443 
444  // set query
445  $set = $ilDB->query($query);
446  $result = array();
447  while($rec = $ilDB->fetchAssoc($set))
448  {
449  $result[] = $rec;
450 
451  if(sizeof($multi_fields))
452  {
453  $usr_ids[] = $rec["usr_id"];
454  }
455  }
456 
457  // add multi-field-values to user-data
458  if(sizeof($multi_fields) && sizeof($usr_ids))
459  {
460  $usr_multi = array();
461  $set = $ilDB->query("SELECT * FROM usr_data_multi".
462  " WHERE ".$ilDB->in("usr_id", $usr_ids, "", "integer"));
463  while($row = $ilDB->fetchAssoc($set))
464  {
465  $usr_multi[$row["usr_id"]][$row["field_id"]][] = $row["value"];
466  }
467  foreach($result as $idx => $item)
468  {
469  if(isset($usr_multi[$item["usr_id"]]))
470  {
471  $result[$idx] = array_merge($item, $usr_multi[$item["usr_id"]]);
472  }
473  }
474  }
475 
476  return array("cnt" => $cnt, "set" => $result);
477  }
478 
479 
484  public static function getUserListData($a_order_field, $a_order_dir, $a_offset, $a_limit,
485  $a_string_filter = "", $a_activation_filter = "", $a_last_login_filter = null,
486  $a_limited_access_filter = false, $a_no_courses_filter = false,
487  $a_course_group_filter = 0, $a_role_filter = 0, $a_user_folder_filter = null,
488  $a_additional_fields = '', $a_user_filter = null, $a_first_letter = "")
489  {
490 
491  $query = new ilUserQuery();
492  $query->setOrderField($a_order_field);
493  $query->setOrderDirection($a_order_dir);
494  $query->setOffset($a_offset);
495  $query->setLimit($a_limit);
496  $query->setTextFilter($a_string_filter);
497  $query->setActionFilter($a_activation_filter);
498  $query->setLastLogin($a_last_login_filter);
499  $query->setLimitedAccessFilter($a_limited_access_filter);
500  $query->setNoCourseFilter($a_no_courses_filter);
501  $query->setCourseGroupFilter($a_course_group_filter);
502  $query->setRoleFilter($a_role_filter);
503  $query->setUserFolder($a_user_folder_filter);
504  $query->setAdditionalFields($a_additional_fields);
505  $query->setUserFilter($a_user_filter);
506  $query->setFirstLetterLastname($a_first_letter);
507  return $query->query();
508  }
509 }