ILIAS  Release_4_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-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3 
12 class ilUserQuery
13 {
17  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)
21  {
22  global $ilDB, $rbacreview;
23 
24  $fields = array("usr_id", "login", "firstname", "lastname", "email",
25  "time_limit_until", "time_limit_unlimited", "last_login", "active");
26 
27  // count query
28  $count_query = "SELECT count(usr_id) cnt".
29  " FROM usr_data";
30 
31  // basic query
32  $query = "SELECT ".implode($fields, ",").
33  " FROM usr_data";
34 
35  // filter
36  $query.= " WHERE usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
37  $count_query.= " WHERE usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
38  $where = " AND";
39 
40  if ($a_string_filter != "") // email, name, login
41  {
42  $add = $where." (".$ilDB->like("usr_data.login", "text", "%".$a_string_filter."%")." ".
43  "OR ".$ilDB->like("usr_data.firstname", "text", "%".$a_string_filter."%")." ".
44  "OR ".$ilDB->like("usr_data.lastname", "text", "%".$a_string_filter."%")." ".
45  "OR ".$ilDB->like("usr_data.email", "text", "%".$a_string_filter."%").") ";
46  $query.= $add;
47  $count_query.= $add;
48  $where = " AND";
49  }
50  if ($a_activation_filter != "") // activation
51  {
52  if ($a_activation_filter == "inactive")
53  {
54  $add = $where." usr_data.active = ".$ilDB->quote(0, "integer")." ";
55  }
56  else
57  {
58  $add = $where." usr_data.active = ".$ilDB->quote(1, "integer")." ";
59  }
60  $query.= $add;
61  $count_query.= $add;
62  $where = " AND";
63  }
64 
65  if (is_object($a_last_login_filter)) // last login
66  {
67  if ($a_last_login_filter->get(IL_CAL_UNIX) < time())
68  {
69  $add = $where." last_login < ".
70  $ilDB->quote($a_last_login_filter->get(IL_CAL_DATETIME), "timestamp");
71  $query.= $add;
72  $count_query.= $add;
73  $where = " AND";
74  }
75  }
76  if ($a_limited_access_filter) // limited access
77  {
78  $add = $where." time_limit_unlimited= ".$ilDB->quote(0, "integer");
79  $query.= $add;
80  $count_query.= $add;
81  $where = " AND";
82  }
83  if ($a_no_courses_filter) // no courses assigned
84  {
85  $add = $where." usr_id NOT IN (".
86  "SELECT DISTINCT ud.usr_id ".
87  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
88  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
89  "WHERE od.title LIKE 'il_crs_%')";
90  $query.= $add;
91  $count_query.= $add;
92  $where = " AND";
93  }
94  if ($a_course_group_filter > 0) // members of course/group
95  {
96  $cgtype = ilObject::_lookupType($a_course_group_filter, true);
97  $add = $where." usr_id IN (".
98  "SELECT DISTINCT ud.usr_id ".
99  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
100  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
101  "WHERE od.title = ".$ilDB->quote("il_".$cgtype."_member_".$a_course_group_filter, "text").")";
102  $query.= $add;
103  $count_query.= $add;
104  $where = " AND";
105  }
106  if ($a_role_filter > 0) // global role
107  {
108  $add = $where." usr_id IN (".
109  "SELECT DISTINCT ud.usr_id ".
110  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
111  "WHERE rbac_ua.rol_id = ".$ilDB->quote($a_role_filter, "integer").")";
112  $query.= $add;
113  $count_query.= $add;
114  $where = " AND";
115  }
116 
117  // order by
118  if ($a_order_field != "access_until")
119  {
120  if (!in_array($a_order_field, $fields))
121  {
122  $a_order_field = "login";
123  }
124  if ($a_order_dir != "asc" && $a_order_dir != "desc")
125  {
126  $a_order_dir = "asc";
127  }
128  $query.= " ORDER BY ".$a_order_field." ".strtoupper($a_order_dir);
129  }
130  else
131  {
132  if ($a_order_dir == "desc")
133  {
134  $query.= " ORDER BY active DESC, time_limit_unlimited DESC, time_limit_until DESC";
135  }
136  else
137  {
138  $query.= " ORDER BY active ASC, time_limit_unlimited ASC, time_limit_until ASC";
139  }
140  }
141 
142  // count query
143  $set = $ilDB->query($count_query);
144  $cnt = 0;
145  if ($rec = $ilDB->fetchAssoc($set))
146  {
147  $cnt = $rec["cnt"];
148  }
149 
150  $offset = (int) $a_offset;
151  $limit = (int) $a_limit;
152  $ilDB->setLimit($limit, $offset);
153 
154  // set query
155  $set = $ilDB->query($query);
156  $result = array();
157  while($rec = $ilDB->fetchAssoc($set))
158  {
159  $result[] = $rec;
160  }
161  return array("cnt" => $cnt, "set" => $result);
162  }
163 
164  function old()
165  {
166  $result_arr = array();
167  $types = array();
168  $values = array();
169 
170  if ($a_fields !== NULL and is_array($a_fields))
171  {
172  if (count($a_fields) == 0)
173  {
174  $select = "*";
175  }
176  else
177  {
178  if (($usr_id_field = array_search("usr_id",$a_fields)) !== false)
179  unset($a_fields[$usr_id_field]);
180 
181  $select = implode(",",$a_fields).",usr_data.usr_id";
182  // online time
183  if(in_array('online_time',$a_fields))
184  {
185  $select .= ",ut_online.online_time ";
186  }
187  }
188 
189  $q = "SELECT ".$select." FROM usr_data ";
190 
191  // Add online_time if desired
192  // Need left join here to show users that never logged in
193  if(in_array('online_time',$a_fields))
194  {
195  $q .= "LEFT JOIN ut_online ON usr_data.usr_id = ut_online.usr_id ";
196  }
197 
198  switch ($active)
199  {
200  case 0:
201  case 1:
202  $q .= "WHERE active = ".$ilDB->quote($active, "integer");
203  break;
204  case 2:
205  $q .= "WHERE time_limit_unlimited= ".$ilDB->quote(0, "integer");;
206  break;
207  case 3:
208  $qtemp = $q . ", rbac_ua, object_data WHERE rbac_ua.rol_id = object_data.obj_id AND ".
209  $ilDB->like("object_data.title", "text", "%crs%")." AND usr_data.usr_id = rbac_ua.usr_id";
210  $r = $ilDB->query($qtemp);
211  $course_users = array();
212  while ($row = $ilDB->fetchAssoc($r))
213  {
214  array_push($course_users, $row["usr_id"]);
215  }
216  if (count($course_users))
217  {
218  $q .= " WHERE ".$ilDB->in("usr_data.usr_id", $course_users, true, "integer")." ";
219  }
220  else
221  {
222  return $result_arr;
223  }
224  break;
225  case 4:
226  $date = strftime("%Y-%m-%d %H:%I:%S", mktime(0, 0, 0, $_SESSION["user_filter_data"]["m"], $_SESSION["user_filter_data"]["d"], $_SESSION["user_filter_data"]["y"]));
227  $q.= " AND last_login < ".$ilDB->quote($date, "timestamp");
228  break;
229  case 5:
230  $ref_id = $_SESSION["user_filter_data"];
231  if ($ref_id)
232  {
233  $q .= " LEFT JOIN crs_members ON usr_data.usr_id = crs_members.usr_id ".
234  "WHERE crs_members.obj_id = (SELECT obj_id FROM object_reference ".
235  "WHERE ref_id = ".$ilDB->quote($ref_id, "integer").") ";
236  }
237  break;
238  case 6:
239  global $rbacreview;
240  $ref_id = $_SESSION["user_filter_data"];
241  if ($ref_id)
242  {
243  $rolf = $rbacreview->getRoleFolderOfObject($ref_id);
244  $local_roles = $rbacreview->getRolesOfRoleFolder($rolf["ref_id"],false);
245  if (is_array($local_roles) && count($local_roles))
246  {
247  $q.= " LEFT JOIN rbac_ua ON usr_data.usr_id = rbac_ua.usr_id WHERE ".
248  $ilDB->in("rbac_ua.rol_id", $local_roles, false, "integer")." ";
249  }
250  }
251  break;
252  case 7:
253  $rol_id = $_SESSION["user_filter_data"];
254  if ($rol_id)
255  {
256  $q .= " LEFT JOIN rbac_ua ON usr_data.usr_id = rbac_ua.usr_id WHERE rbac_ua.rol_id = ".
257  $ilDB->quote($rol_id, "integer");
258  }
259  break;
260  }
261 
262  $r = $ilDB->query($q);
263 
264  while ($row = $ilDB->fetchAssoc($r))
265  {
266  $result_arr[] = $row;
267  }
268  }
269 
270  return $result_arr;
271  }
272 
273 }