Query usr_data.
220 {
222
223 $ut_join = "";
224 if (is_array($this->additional_fields))
225 {
226 foreach ($this->additional_fields as $f)
227 {
228 if (!in_array($f, $this->default_fields))
229 {
230 if($f == "online_time")
231 {
232 $this->default_fields[] = "ut_online.online_time";
233 $ut_join = " LEFT JOIN ut_online ON usr_data.usr_id = ut_online.usr_id";
234 }
235 else
236 {
237 $this->default_fields[] = $f;
238 }
239 }
240 }
241 }
242
243 $count_query = "SELECT count(usr_id) cnt".
244 " FROM usr_data";
245
246 $all_multi_fields = array("interests_general", "interests_help_offered", "interests_help_looking");
247 $multi_fields = array();
248
249 $sql_fields = array();
250 foreach($this->default_fields as $idx => $field)
251 {
252 if(!$field)
253 {
254 continue;
255 }
256
257 if(in_array($field, $all_multi_fields))
258 {
259 $multi_fields[] = $field;
260 }
261 else if(!stristr($field, "."))
262 {
263 $sql_fields[] = "usr_data.".$field;
264 }
265 else
266 {
267 $sql_fields[] = $field;
268 }
269 }
270
271
272 $query =
"SELECT ".implode($sql_fields,
",").
273 " FROM usr_data".
274 $ut_join;
275
276
277 $query.=
" WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID,
"integer");
278
279
280 if($this->users and is_array(($this->users)))
281 {
282 $query .=
' AND '.$ilDB->in(
'usr_data.usr_id',$this->users,
false,
'integer');
283 }
284
285 $count_query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
286 $where = " AND";
287
288 if ($this->first_letter != "")
289 {
290 $add = $where.
" (".
$ilDB->upper(
$ilDB->substr(
"usr_data.lastname", 1, 1)).
" = ".
$ilDB->upper(
$ilDB->quote($this->first_letter,
"text")).
") ";
292 $count_query.= $add;
293 $where = " AND";
294 }
295
296 if ($this->text_filter != "")
297 {
298 $add = $where.
" (".
$ilDB->like(
"usr_data.login",
"text",
"%".$this->text_filter.
"%").
" ".
299 "OR ".$ilDB->like("usr_data.firstname", "text", "%".$this->text_filter."%")." ".
300 "OR ".$ilDB->like("usr_data.lastname", "text", "%".$this->text_filter."%")." ".
301 "OR ".$ilDB->like("usr_data.email", "text", "%".$this->text_filter."%").") ";
303 $count_query.= $add;
304 $where = " AND";
305 }
306
307 if ($this->activation != "")
308 {
309 if ($this->activation == "inactive")
310 {
311 $add = $where.
" usr_data.active = ".
$ilDB->quote(0,
"integer").
" ";
312 }
313 else
314 {
315 $add = $where.
" usr_data.active = ".
$ilDB->quote(1,
"integer").
" ";
316 }
318 $count_query.= $add;
319 $where = " AND";
320 }
321
323 {
325 {
326 $add = $where." usr_data.last_login < ".
329 $count_query.= $add;
330 $where = " AND";
331 }
332 }
333 if ($this->limited_access)
334 {
335 $add = $where.
" usr_data.time_limit_unlimited= ".
$ilDB->quote(0,
"integer");
337 $count_query.= $add;
338 $where = " AND";
339 }
340
341 if($this->has_access)
342 {
343 $unlimited =
"time_limit_unlimited = ".
$ilDB->quote(1,
'integer');
344 $from =
"time_limit_from < ".
$ilDB->quote(time(),
'integer');
345 $until =
"time_limit_until > ".
$ilDB->quote(time(),
'integer');
346
347 $add = $where.' (' .$unlimited.' OR ('.$from.' AND ' .$until.'))';
349 $count_query.= $add;
350 $where = " AND";
351 }
352 if ($this->no_courses)
353 {
354 $add = $where." usr_data.usr_id NOT 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 LIKE 'il_crs_%')";
360 $count_query.= $add;
361 $where = " AND";
362 }
363 if ($this->no_groups)
364 {
365 $add = $where." usr_data.usr_id NOT IN (".
366 "SELECT DISTINCT ud.usr_id ".
367 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
368 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
369 "WHERE od.title LIKE 'il_grp_%')";
371 $count_query.= $add;
372 $where = " AND";
373 }
374 if ($this->crs_grp > 0)
375 {
377 $add = $where." usr_data.usr_id IN (".
378 "SELECT DISTINCT ud.usr_id ".
379 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
380 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
381 "WHERE od.title = ".$ilDB->quote("il_".$cgtype."_member_".$this->crs_grp, "text").")";
383 $count_query.= $add;
384 $where = " AND";
385 }
386 if ($this->role > 0)
387 {
388 $add = $where." usr_data.usr_id IN (".
389 "SELECT DISTINCT ud.usr_id ".
390 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
391 "WHERE rbac_ua.rol_id = ".$ilDB->quote($this->role, "integer").")";
393 $count_query.= $add;
394 $where = " AND";
395 }
396
397 if($this->user_folder)
398 {
399 $add = $where.
" ".
$ilDB->in(
'usr_data.time_limit_owner',$this->user_folder,
false,
'integer');
401 $count_query.= $add;
402 $where = " AND";
403 }
404
405
406 switch($this->order_field)
407 {
408 case "access_until":
409 if ($this->order_dir == "desc")
410 {
411 $query.=
" ORDER BY usr_data.active DESC, usr_data.time_limit_unlimited DESC, usr_data.time_limit_until DESC";
412 }
413 else
414 {
415 $query.=
" ORDER BY usr_data.active ASC, usr_data.time_limit_unlimited ASC, usr_data.time_limit_until ASC";
416 }
417 break;
418
419 case "online_time":
420 if ($this->order_dir == "desc")
421 {
422 $query.=
" ORDER BY ut_online.online_time DESC";
423 }
424 else
425 {
426 $query.=
" ORDER BY ut_online.online_time ASC";
427 }
428 break;
429
430 default:
431 if (!in_array($this->order_field, $this->default_fields))
432 {
433 $this->order_field = "login";
434 }
435 if ($this->order_dir != "asc" && $this->order_dir != "desc")
436 {
437 $this->order_dir = "asc";
438 }
439 $query .=
" ORDER BY usr_data.".$this->order_field.
" ".strtoupper($this->order_dir);
440 break;
441 }
442
443
444 $set =
$ilDB->query($count_query);
445 $cnt = 0;
446 if ($rec =
$ilDB->fetchAssoc($set))
447 {
448 $cnt = $rec["cnt"];
449 }
450
452 $limit = (int) $this->limit;
453
454
456 {
458 }
459
461
462 if(sizeof($multi_fields))
463 {
464 $usr_ids = array();
465 }
466
467
470 while($rec =
$ilDB->fetchAssoc($set))
471 {
473
474 if(sizeof($multi_fields))
475 {
476 $usr_ids[] = $rec["usr_id"];
477 }
478 }
479
480
481 if(sizeof($multi_fields) && sizeof($usr_ids))
482 {
483 $usr_multi = array();
484 $set =
$ilDB->query(
"SELECT * FROM usr_data_multi".
485 " WHERE ".
$ilDB->in(
"usr_id", $usr_ids,
"",
"integer"));
487 {
488 $usr_multi[
$row[
"usr_id"]][
$row[
"field_id"]][] =
$row[
"value"];
489 }
490 foreach(
$result as $idx => $item)
491 {
492 if(isset($usr_multi[$item["usr_id"]]))
493 {
494 $result[$idx] = array_merge($item, $usr_multi[$item[
"usr_id"]]);
495 }
496 }
497 }
498
499 return array(
"cnt" => $cnt,
"set" =>
$result);
500 }
@classDescription Date and time handling
static _before(ilDateTime $start, ilDateTime $end, $a_compare_field='', $a_tz='')
compare two dates and check start is before end This method does not consider tz offsets.
static _lookupType($a_id, $a_reference=false)
lookup object type