ILIAS  Release_4_3_x_branch Revision 61807
 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  $ut_join = "";
29  if (is_array($a_additional_fields))
30  {
31  foreach ($a_additional_fields as $f)
32  {
33  if (!in_array($f, $fields))
34  {
35  if($f == "online_time")
36  {
37  $fields[] = "ut_online.online_time";
38  $ut_join = " LEFT JOIN ut_online ON usr_data.usr_id = ut_online.usr_id";
39  }
40  else
41  {
42  $fields[] = $f;
43  }
44  }
45  }
46  }
47 
48  // count query
49  $count_query = "SELECT count(usr_id) cnt".
50  " FROM usr_data";
51 
52  $sql_fields = array();
53  foreach($fields as $idx => $field)
54  {
55  if(!stristr($field, "."))
56  {
57  $sql_fields[] = "usr_data.".$field;
58  }
59  else
60  {
61  $sql_fields[] = $field;
62  }
63  }
64 
65  // basic query
66  $query = "SELECT ".implode($sql_fields, ",").
67  " FROM usr_data".
68  $ut_join;
69 
70  // filter
71  $query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
72 
73  // User filter
74  if($a_user_filter and is_array(($a_user_filter)))
75  {
76  $query .= ' AND '.$ilDB->in('usr_data.usr_id',$a_user_filter,false,'integer');
77  }
78 
79  $count_query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
80  $where = " AND";
81 
82  if ($a_first_letter != "")
83  {
84  $add = $where." (".$ilDB->upper($ilDB->substr("usr_data.lastname", 1, 1))." = ".$ilDB->upper($ilDB->quote($a_first_letter, "text")).") ";
85  $query.= $add;
86  $count_query.= $add;
87  $where = " AND";
88  }
89 
90  if ($a_string_filter != "") // email, name, login
91  {
92  $add = $where." (".$ilDB->like("usr_data.login", "text", "%".$a_string_filter."%")." ".
93  "OR ".$ilDB->like("usr_data.firstname", "text", "%".$a_string_filter."%")." ".
94  "OR ".$ilDB->like("usr_data.lastname", "text", "%".$a_string_filter."%")." ".
95  "OR ".$ilDB->like("usr_data.email", "text", "%".$a_string_filter."%").") ";
96  $query.= $add;
97  $count_query.= $add;
98  $where = " AND";
99  }
100  if ($a_activation_filter != "") // activation
101  {
102  if ($a_activation_filter == "inactive")
103  {
104  $add = $where." usr_data.active = ".$ilDB->quote(0, "integer")." ";
105  }
106  else
107  {
108  $add = $where." usr_data.active = ".$ilDB->quote(1, "integer")." ";
109  }
110  $query.= $add;
111  $count_query.= $add;
112  $where = " AND";
113  }
114 
115  if (is_object($a_last_login_filter)) // last login
116  {
117  if ($a_last_login_filter->get(IL_CAL_UNIX) < time())
118  {
119  $add = $where." usr_data.last_login < ".
120  $ilDB->quote($a_last_login_filter->get(IL_CAL_DATETIME), "timestamp");
121  $query.= $add;
122  $count_query.= $add;
123  $where = " AND";
124  }
125  }
126  if ($a_limited_access_filter) // limited access
127  {
128  $add = $where." usr_data.time_limit_unlimited= ".$ilDB->quote(0, "integer");
129  $query.= $add;
130  $count_query.= $add;
131  $where = " AND";
132  }
133  if ($a_no_courses_filter) // no courses assigned
134  {
135  $add = $where." usr_data.usr_id NOT IN (".
136  "SELECT DISTINCT ud.usr_id ".
137  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
138  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
139  "WHERE od.title LIKE 'il_crs_%')";
140  $query.= $add;
141  $count_query.= $add;
142  $where = " AND";
143  }
144  if ($a_course_group_filter > 0) // members of course/group
145  {
146  $cgtype = ilObject::_lookupType($a_course_group_filter, true);
147  $add = $where." usr_data.usr_id IN (".
148  "SELECT DISTINCT ud.usr_id ".
149  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
150  "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
151  "WHERE od.title = ".$ilDB->quote("il_".$cgtype."_member_".$a_course_group_filter, "text").")";
152  $query.= $add;
153  $count_query.= $add;
154  $where = " AND";
155  }
156  if ($a_role_filter > 0) // global role
157  {
158  $add = $where." usr_data.usr_id IN (".
159  "SELECT DISTINCT ud.usr_id ".
160  "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
161  "WHERE rbac_ua.rol_id = ".$ilDB->quote($a_role_filter, "integer").")";
162  $query.= $add;
163  $count_query.= $add;
164  $where = " AND";
165  }
166 
167  if(!is_null($a_user_folder_filter))
168  {
169  $add = $where." ".$ilDB->in('usr_data.time_limit_owner',$a_user_folder_filter,false,'integer');
170  $query.= $add;
171  $count_query.= $add;
172  $where = " AND";
173  }
174 
175  // order by
176  switch($a_order_field)
177  {
178  case "access_until":
179  if ($a_order_dir == "desc")
180  {
181  $query.= " ORDER BY usr_data.active DESC, usr_data.time_limit_unlimited DESC, usr_data.time_limit_until DESC";
182  }
183  else
184  {
185  $query.= " ORDER BY usr_data.active ASC, usr_data.time_limit_unlimited ASC, usr_data.time_limit_until ASC";
186  }
187  break;
188 
189  case "online_time":
190  if ($a_order_dir == "desc")
191  {
192  $query.= " ORDER BY ut_online.online_time DESC";
193  }
194  else
195  {
196  $query.= " ORDER BY ut_online.online_time ASC";
197  }
198  break;
199 
200  default:
201  if (!in_array($a_order_field, $fields))
202  {
203  $a_order_field = "login";
204  }
205  if ($a_order_dir != "asc" && $a_order_dir != "desc")
206  {
207  $a_order_dir = "asc";
208  }
209  $query .= " ORDER BY usr_data.".$a_order_field." ".strtoupper($a_order_dir);
210  break;
211  }
212 
213  // count query
214  $set = $ilDB->query($count_query);
215  $cnt = 0;
216  if ($rec = $ilDB->fetchAssoc($set))
217  {
218  $cnt = $rec["cnt"];
219  }
220 
221  $offset = (int) $a_offset;
222  $limit = (int) $a_limit;
223 
224  // #9866: validate offset against rowcount
225  if($offset >= $cnt)
226  {
227  $offset = 0;
228  }
229 
230  $ilDB->setLimit($limit, $offset);
231 
232  // set query
233  $set = $ilDB->query($query);
234  $result = array();
235  while($rec = $ilDB->fetchAssoc($set))
236  {
237  $result[] = $rec;
238  }
239  return array("cnt" => $cnt, "set" => $result);
240  }
241 }