Query usr_data.
266 {
268
269
270 $udf_fields = array();
271
272 $join = "";
273
274 if (is_array($this->additional_fields)) {
275 foreach ($this->additional_fields as $f) {
276 if (!in_array($f, $this->default_fields)) {
277 if ($f == "online_time") {
278 $this->default_fields[] = "ut_online.online_time";
279 $join = " LEFT JOIN ut_online ON (usr_data.usr_id = ut_online.usr_id) ";
280 } elseif (substr($f, 0, 4) == "udf_") {
281 $udf_fields[] = (int) substr($f, 4);
282 } else {
283 $this->default_fields[] = $f;
284 }
285 }
286 }
287 }
288
289
290 $udf_def = array();
291 if (count($udf_fields) > 0) {
292 include_once './Services/User/classes/class.ilUserDefinedFields.php';
294 }
295
296
297 foreach ($udf_fields as
$id) {
299 ? "udf_text"
300 : "udf_clob";
301 $join.=
" LEFT JOIN " . $udf_table .
" ud_" .
$id .
" ON (ud_" .
$id .
".field_id=" .
$ilDB->quote(
$id) .
" AND ud_" .
$id .
".usr_id = usr_data.usr_id) ";
302 }
303
304
305 $count_query = "SELECT count(usr_data.usr_id) cnt" .
306 " FROM usr_data";
307
308 $all_multi_fields = array("interests_general", "interests_help_offered", "interests_help_looking");
309 $multi_fields = array();
310
311 $sql_fields = array();
312 foreach ($this->default_fields as $idx => $field) {
313 if (!$field) {
314 continue;
315 }
316
317 if (in_array($field, $all_multi_fields)) {
318 $multi_fields[] = $field;
319 } elseif (!stristr($field, ".")) {
320 $sql_fields[] = "usr_data." . $field;
321 } else {
322 $sql_fields[] = $field;
323 }
324 }
325
326
327 foreach ($udf_fields as
$id) {
328 $sql_fields[] =
"ud_" .
$id .
".value udf_" .
$id;
329 }
330
331
332 $query =
"SELECT " . implode($sql_fields,
",") .
333 " FROM usr_data" .
334 $join;
335
336 $count_query = $count_query . " " .
337 $join;
338
339
340 $query.=
" WHERE usr_data.usr_id <> " .
$ilDB->quote(ANONYMOUS_USER_ID,
"integer");
341
342
343 $count_query.= " WHERE 1 = 1 ";
344 $count_user_filter =
"usr_data.usr_id != " .
$ilDB->quote(ANONYMOUS_USER_ID,
"integer");
345 if ($this->users and is_array(($this->users))) {
346 $query .=
' AND ' .
$ilDB->in(
'usr_data.usr_id', $this->users,
false,
'integer');
347 $count_user_filter =
$ilDB->in(
'usr_data.usr_id', $this->users,
false,
'integer');
348 }
349
350 $count_query.= " AND " . $count_user_filter . " ";
351 $where = " AND";
352
353 if ($this->first_letter != "") {
354 $add = $where .
" (" .
$ilDB->upper(
$ilDB->substr(
"usr_data.lastname", 1, 1)) .
" = " .
$ilDB->upper(
$ilDB->quote($this->first_letter,
"text")) .
") ";
356 $count_query.= $add;
357 $where = " AND";
358 }
359
360 if ($this->text_filter != "") {
361 $add = $where .
" (" .
$ilDB->like(
"usr_data.login",
"text",
"%" . $this->text_filter .
"%") .
" " .
362 "OR " .
$ilDB->like(
"usr_data.firstname",
"text",
"%" . $this->text_filter .
"%") .
" " .
363 "OR " .
$ilDB->like(
"usr_data.lastname",
"text",
"%" . $this->text_filter .
"%") .
" " .
364 "OR " .
$ilDB->like(
"usr_data.second_email",
"text",
"%" . $this->text_filter .
"%") .
" " .
365 "OR " .
$ilDB->like(
"usr_data.email",
"text",
"%" . $this->text_filter .
"%") .
") ";
367 $count_query.= $add;
368 $where = " AND";
369 }
370
371 if ($this->activation != "") {
372 if ($this->activation == "inactive") {
373 $add = $where .
" usr_data.active = " .
$ilDB->quote(0,
"integer") .
" ";
374 } else {
375 $add = $where .
" usr_data.active = " .
$ilDB->quote(1,
"integer") .
" ";
376 }
378 $count_query.= $add;
379 $where = " AND";
380 }
381
382 if ($this->last_login instanceof
ilDateTime) {
384 $add = $where . " usr_data.last_login < " .
387 $count_query.= $add;
388 $where = " AND";
389 }
390 }
391 if ($this->limited_access) {
392 $add = $where .
" usr_data.time_limit_unlimited= " .
$ilDB->quote(0,
"integer");
394 $count_query.= $add;
395 $where = " AND";
396 }
397
398
400 if ($f != "") {
401 $udf_id = explode("_", $k)[1];
403 $add = $where .
" " .
$ilDB->like(
"ud_" . $udf_id .
".value",
"text",
"%" . $f .
"%");
404 } else {
405 $add = $where .
" ud_" . $udf_id .
".value = " .
$ilDB->quote($f,
"text");
406 }
408 $count_query.= $add;
409 $where = " AND";
410 }
411 }
412
413 if ($this->has_access) {
414 $unlimited =
"time_limit_unlimited = " .
$ilDB->quote(1,
'integer');
415 $from =
"time_limit_from < " .
$ilDB->quote(time(),
'integer');
416 $until =
"time_limit_until > " .
$ilDB->quote(time(),
'integer');
417
418 $add = $where .
' (' . $unlimited .
' OR (' .
$from .
' AND ' . $until .
'))';
420 $count_query.= $add;
421 $where = " AND";
422 }
423 if ($this->no_courses) {
424 $add = $where . " usr_data.usr_id NOT IN (" .
425 "SELECT DISTINCT ud.usr_id " .
426 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) " .
427 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) " .
428 "WHERE od.title LIKE 'il_crs_%')";
430 $count_query.= $add;
431 $where = " AND";
432 }
433 if ($this->no_groups) {
434 $add = $where . " usr_data.usr_id NOT IN (" .
435 "SELECT DISTINCT ud.usr_id " .
436 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) " .
437 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) " .
438 "WHERE od.title LIKE 'il_grp_%')";
440 $count_query.= $add;
441 $where = " AND";
442 }
443 if ($this->crs_grp > 0) {
445 $add = $where . " usr_data.usr_id IN (" .
446 "SELECT DISTINCT ud.usr_id " .
447 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) " .
448 "JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) " .
449 "WHERE od.title = " .
$ilDB->quote(
"il_" . $cgtype .
"_member_" . $this->crs_grp,
"text") .
")";
451 $count_query.= $add;
452 $where = " AND";
453 }
454 if ($this->role > 0) {
455 $add = $where . " usr_data.usr_id IN (" .
456 "SELECT DISTINCT ud.usr_id " .
457 "FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) " .
458 "WHERE rbac_ua.rol_id = " .
$ilDB->quote($this->role,
"integer") .
")";
460 $count_query.= $add;
461 $where = " AND";
462 }
463
464 if ($this->user_folder) {
465 $add = $where .
" " .
$ilDB->in(
'usr_data.time_limit_owner', $this->user_folder,
false,
'integer');
467 $count_query.= $add;
468 $where = " AND";
469 }
470
471 if ($this->authentication_method != "") {
472 $add = $where .
" usr_data.auth_mode = " .
$ilDB->quote($this->authentication_method,
"text") .
" ";
474 $count_query.= $add;
475 $where = " AND";
476 }
477
478
479 switch ($this->order_field) {
480 case "access_until":
481 if ($this->order_dir == "desc") {
482 $query.=
" ORDER BY usr_data.active DESC, usr_data.time_limit_unlimited DESC, usr_data.time_limit_until DESC";
483 } else {
484 $query.=
" ORDER BY usr_data.active ASC, usr_data.time_limit_unlimited ASC, usr_data.time_limit_until ASC";
485 }
486 break;
487
488 case "online_time":
489 if ($this->order_dir == "desc") {
490 $query.=
" ORDER BY ut_online.online_time DESC";
491 } else {
492 $query.=
" ORDER BY ut_online.online_time ASC";
493 }
494 break;
495
496 default:
497 if ($this->order_dir != "asc" && $this->order_dir != "desc") {
498 $this->order_dir = "asc";
499 }
500 if (substr($this->order_field, 0, 4) == "udf_") {
501
503 $query .=
" ORDER BY ud_" . ((int) substr($this->order_field, 4)) .
".value " . strtoupper($this->order_dir);
504 } else {
505 $query .=
' ORDER BY ' . self::DEFAULT_ORDER_FIELD .
' ' . strtoupper($this->order_dir);
506 }
507 } else {
508 if (!in_array($this->order_field, $this->default_fields)) {
509 $this->order_field = "login";
510 }
511 $query .=
" ORDER BY usr_data." . $this->order_field .
" " . strtoupper($this->order_dir);
512 }
513 break;
514 }
515
516
517 $set =
$ilDB->query($count_query);
518 $cnt = 0;
519 if ($rec =
$ilDB->fetchAssoc($set)) {
520 $cnt = $rec["cnt"];
521 }
522
524 $limit = (int) $this->limit;
525
526
529 }
530
532
533 if (sizeof($multi_fields)) {
534 $usr_ids = array();
535 }
536
537
540 while ($rec =
$ilDB->fetchAssoc($set)) {
542
543 if (sizeof($multi_fields)) {
544 $usr_ids[] = $rec["usr_id"];
545 }
546 }
547
548
549 if (sizeof($multi_fields) && sizeof($usr_ids)) {
550 $usr_multi = array();
551 $set =
$ilDB->query(
"SELECT * FROM usr_data_multi" .
552 " WHERE " .
$ilDB->in(
"usr_id", $usr_ids,
"",
"integer"));
554 $usr_multi[
$row[
"usr_id"]][
$row[
"field_id"]][] =
$row[
"value"];
555 }
556 foreach (
$result as $idx => $item) {
557 if (isset($usr_multi[$item["usr_id"]])) {
558 $result[$idx] = array_merge($item, $usr_multi[$item[
"usr_id"]]);
559 }
560 }
561 }
562
563 return array(
"cnt" => $cnt,
"set" =>
$result);
564 }
@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
static _getInstance()
Get instance.
getUdfFilter()
Get udf filter.
if(!array_key_exists('StateId', $_REQUEST)) $id