ILIAS  Release_4_1_x_branch Revision 61804
 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 {
17  public static function getUserListData($a_order_field, $a_order_dir, $a_offset, $a_limit,
18  $a_string_filter = "", $a_activation_filter = "", $a_last_login_filter = null,
19  $a_limited_access_filter = false, $a_no_courses_filter = false,
20  $a_course_group_filter = 0, $a_role_filter = 0, $a_user_folder_filter = null,
21  $a_additional_fields = '', $a_user_filter = null, $a_first_letter = "")
22  {
23  global $ilDB, $rbacreview;
24 
25  $fields = array("usr_id", "login", "firstname", "lastname", "email",
26  "time_limit_until", "time_limit_unlimited", "time_limit_owner", "last_login", "active");
27 
28  if (is_array($a_additional_fields))
29  {
30  foreach ($a_additional_fields as $f)
31  {
32  if (!in_array($f, $fields))
33  {
34  $fields[] = $f;
35  }
36  }
37  }
38 
39  // count query
40  $count_query = "SELECT count(usr_id) cnt".
41  " FROM usr_data";
42 
43  // basic query
44  $query = "SELECT ".implode($fields, ",").
45  " FROM usr_data";
46 
47  // filter
48  $query.= " WHERE usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
49 
50  // User filter
51  if($a_user_filter and is_array(($a_user_filter)))
52  {
53  $query .= ' AND '.$ilDB->in('usr_id',$a_user_filter,false,'integer');
54  }
55 
56  $count_query.= " WHERE usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
57  $where = " AND";
58 
59  if ($a_first_letter != "")
60  {
61  $add = $where." (".$ilDB->upper($ilDB->substr("usr_data.lastname", 1, 1))." = ".$ilDB->upper($ilDB->quote($a_first_letter, "text")).") ";
62  $query.= $add;
63  $count_query.= $add;
64  $where = " AND";
65  }
66 
67  if ($a_string_filter != "") // email, name, login
68  {
69  $add = $where." (".$ilDB->like("usr_data.login", "text", "%".$a_string_filter."%")." ".
70  "OR ".$ilDB->like("usr_data.firstname", "text", "%".$a_string_filter."%")." ".
71  "OR ".$ilDB->like("usr_data.lastname", "text", "%".$a_string_filter."%")." ".
72  "OR ".$ilDB->like("usr_data.email", "text", "%".$a_string_filter."%").") ";
73  $query.= $add;
74  $count_query.= $add;
75  $where = " AND";
76  }
77  if ($a_activation_filter != "") // activation
78  {
79  if ($a_activation_filter == "inactive")
80  {
81  $add = $where." usr_data.active = ".$ilDB->quote(0, "integer")." ";
82  }
83  else
84  {
85  $add = $where." usr_data.active = ".$ilDB->quote(1, "integer")." ";
86  }
87  $query.= $add;
88  $count_query.= $add;
89  $where = " AND";
90  }
91 
92  if (is_object($a_last_login_filter)) // last login
93  {
94  if ($a_last_login_filter->get(IL_CAL_UNIX) < time())
95  {
96  $add = $where." last_login < ".
97  $ilDB->quote($a_last_login_filter->get(IL_CAL_DATETIME), "timestamp");
98  $query.= $add;
99  $count_query.= $add;
100  $where = " AND";
101  }
102  }
103  if ($a_limited_access_filter) // limited access
104  {
105  $add = $where." time_limit_unlimited= ".$ilDB->quote(0, "integer");
106  $query.= $add;
107  $count_query.= $add;
108  $where = " AND";
109  }
110  if ($a_no_courses_filter) // no courses assigned
111  {
112  $add = $where." usr_id NOT IN (".
113  "SELECT DISTINCT ud.usr_id ".
114  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
115  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
116  "WHERE od.title LIKE 'il_crs_%')";
117  $query.= $add;
118  $count_query.= $add;
119  $where = " AND";
120  }
121  if ($a_course_group_filter > 0) // members of course/group
122  {
123  $cgtype = ilObject::_lookupType($a_course_group_filter, true);
124  $add = $where." usr_id IN (".
125  "SELECT DISTINCT ud.usr_id ".
126  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
127  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
128  "WHERE od.title = ".$ilDB->quote("il_".$cgtype."_member_".$a_course_group_filter, "text").")";
129  $query.= $add;
130  $count_query.= $add;
131  $where = " AND";
132  }
133  if ($a_role_filter > 0) // global role
134  {
135  $add = $where." usr_id IN (".
136  "SELECT DISTINCT ud.usr_id ".
137  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
138  "WHERE rbac_ua.rol_id = ".$ilDB->quote($a_role_filter, "integer").")";
139  $query.= $add;
140  $count_query.= $add;
141  $where = " AND";
142  }
143 
144  if(!is_null($a_user_folder_filter))
145  {
146  $add = $where." ".$ilDB->in('time_limit_owner',$a_user_folder_filter,false,'integer');
147  $query.= $add;
148  $count_query.= $add;
149  $where = " AND";
150  }
151 
152  // order by
153  if ($a_order_field != "access_until")
154  {
155  if (!in_array($a_order_field, $fields))
156  {
157  $a_order_field = "login";
158  }
159  if ($a_order_dir != "asc" && $a_order_dir != "desc")
160  {
161  $a_order_dir = "asc";
162  }
163  $query.= " ORDER BY ".$a_order_field." ".strtoupper($a_order_dir);
164  }
165  else
166  {
167  if ($a_order_dir == "desc")
168  {
169  $query.= " ORDER BY active DESC, time_limit_unlimited DESC, time_limit_until DESC";
170  }
171  else
172  {
173  $query.= " ORDER BY active ASC, time_limit_unlimited ASC, time_limit_until ASC";
174  }
175  }
176 
177  // count query
178  $set = $ilDB->query($count_query);
179  $cnt = 0;
180  if ($rec = $ilDB->fetchAssoc($set))
181  {
182  $cnt = $rec["cnt"];
183  }
184 
185  $offset = (int) $a_offset;
186  $limit = (int) $a_limit;
187  $ilDB->setLimit($limit, $offset);
188 
189  // set query
190  $set = $ilDB->query($query);
191  $result = array();
192  while($rec = $ilDB->fetchAssoc($set))
193  {
194  $result[] = $rec;
195  }
196  return array("cnt" => $cnt, "set" => $result);
197  }
198 }