ILIAS  Release_4_0_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
Public Member Functions | Static Public Member Functions
ilUserQuery Class Reference
Services/User

User query class. More...

+ Collaboration diagram for ilUserQuery:

Public Member Functions

 old ()

Static Public Member Functions

static getUserListData ($a_order_field, $a_order_dir, $a_offset, $a_limit, $a_string_filter="", $a_activation_filter="", $a_last_login_filter=null, $a_limited_access_filter=false, $a_no_courses_filter=false, $a_course_group_filter=0, $a_role_filter=0)
 Get data for user administration list.

Detailed Description

User query class.

Put any complex that queries for a set of users into this class and keep ilObjUser "small".

Author
Alex Killing alex..nosp@m.kill.nosp@m.ing@g.nosp@m.mx.d.nosp@m.e
Version
$Id$

Definition at line 12 of file class.ilUserQuery.php.

Member Function Documentation

static ilUserQuery::getUserListData (   $a_order_field,
  $a_order_dir,
  $a_offset,
  $a_limit,
  $a_string_filter = "",
  $a_activation_filter = "",
  $a_last_login_filter = null,
  $a_limited_access_filter = false,
  $a_no_courses_filter = false,
  $a_course_group_filter = 0,
  $a_role_filter = 0 
)
static

Get data for user administration list.

Definition at line 17 of file class.ilUserQuery.php.

References $ilDB, $query, $result, ilObject\_lookupType(), IL_CAL_DATETIME, and IL_CAL_UNIX.

Referenced by ilAssignedUsersTableGUI\getItems(), and ilUserTableGUI\getItems().

{
global $ilDB, $rbacreview;
$fields = array("usr_id", "login", "firstname", "lastname", "email",
"time_limit_until", "time_limit_unlimited", "last_login", "active");
// count query
$count_query = "SELECT count(usr_id) cnt".
" FROM usr_data";
// basic query
$query = "SELECT ".implode($fields, ",").
" FROM usr_data";
// filter
$query.= " WHERE usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
$count_query.= " WHERE usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
$where = " AND";
if ($a_string_filter != "") // email, name, login
{
$add = $where." (".$ilDB->like("usr_data.login", "text", "%".$a_string_filter."%")." ".
"OR ".$ilDB->like("usr_data.firstname", "text", "%".$a_string_filter."%")." ".
"OR ".$ilDB->like("usr_data.lastname", "text", "%".$a_string_filter."%")." ".
"OR ".$ilDB->like("usr_data.email", "text", "%".$a_string_filter."%").") ";
$query.= $add;
$count_query.= $add;
$where = " AND";
}
if ($a_activation_filter != "") // activation
{
if ($a_activation_filter == "inactive")
{
$add = $where." usr_data.active = ".$ilDB->quote(0, "integer")." ";
}
else
{
$add = $where." usr_data.active = ".$ilDB->quote(1, "integer")." ";
}
$query.= $add;
$count_query.= $add;
$where = " AND";
}
if (is_object($a_last_login_filter)) // last login
{
if ($a_last_login_filter->get(IL_CAL_UNIX) < time())
{
$add = $where." last_login < ".
$ilDB->quote($a_last_login_filter->get(IL_CAL_DATETIME), "timestamp");
$query.= $add;
$count_query.= $add;
$where = " AND";
}
}
if ($a_limited_access_filter) // limited access
{
$add = $where." time_limit_unlimited= ".$ilDB->quote(0, "integer");
$query.= $add;
$count_query.= $add;
$where = " AND";
}
if ($a_no_courses_filter) // no courses assigned
{
$add = $where." usr_id NOT IN (".
"SELECT DISTINCT ud.usr_id ".
"FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
"JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
"WHERE od.title LIKE 'il_crs_%')";
$query.= $add;
$count_query.= $add;
$where = " AND";
}
if ($a_course_group_filter > 0) // members of course/group
{
$cgtype = ilObject::_lookupType($a_course_group_filter, true);
$add = $where." usr_id IN (".
"SELECT DISTINCT ud.usr_id ".
"FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
"JOIN object_data od ON (rbac_ua.rol_id = od.obj_id) ".
"WHERE od.title = ".$ilDB->quote("il_".$cgtype."_member_".$a_course_group_filter, "text").")";
$query.= $add;
$count_query.= $add;
$where = " AND";
}
if ($a_role_filter > 0) // global role
{
$add = $where." usr_id IN (".
"SELECT DISTINCT ud.usr_id ".
"FROM usr_data ud join rbac_ua ON (ud.usr_id = rbac_ua.usr_id) ".
"WHERE rbac_ua.rol_id = ".$ilDB->quote($a_role_filter, "integer").")";
$query.= $add;
$count_query.= $add;
$where = " AND";
}
// order by
if ($a_order_field != "access_until")
{
if (!in_array($a_order_field, $fields))
{
$a_order_field = "login";
}
if ($a_order_dir != "asc" && $a_order_dir != "desc")
{
$a_order_dir = "asc";
}
$query.= " ORDER BY ".$a_order_field." ".strtoupper($a_order_dir);
}
else
{
if ($a_order_dir == "desc")
{
$query.= " ORDER BY active DESC, time_limit_unlimited DESC, time_limit_until DESC";
}
else
{
$query.= " ORDER BY active ASC, time_limit_unlimited ASC, time_limit_until ASC";
}
}
// count query
$set = $ilDB->query($count_query);
$cnt = 0;
if ($rec = $ilDB->fetchAssoc($set))
{
$cnt = $rec["cnt"];
}
$offset = (int) $a_offset;
$limit = (int) $a_limit;
$ilDB->setLimit($limit, $offset);
// set query
$set = $ilDB->query($query);
$result = array();
while($rec = $ilDB->fetchAssoc($set))
{
$result[] = $rec;
}
return array("cnt" => $cnt, "set" => $result);
}

+ Here is the call graph for this function:

+ Here is the caller graph for this function:

ilUserQuery::old ( )

Definition at line 164 of file class.ilUserQuery.php.

References $_SESSION, $ilDB, $q, $ref_id, and $row.

{
$result_arr = array();
$types = array();
$values = array();
if ($a_fields !== NULL and is_array($a_fields))
{
if (count($a_fields) == 0)
{
$select = "*";
}
else
{
if (($usr_id_field = array_search("usr_id",$a_fields)) !== false)
unset($a_fields[$usr_id_field]);
$select = implode(",",$a_fields).",usr_data.usr_id";
// online time
if(in_array('online_time',$a_fields))
{
$select .= ",ut_online.online_time ";
}
}
$q = "SELECT ".$select." FROM usr_data ";
// Add online_time if desired
// Need left join here to show users that never logged in
if(in_array('online_time',$a_fields))
{
$q .= "LEFT JOIN ut_online ON usr_data.usr_id = ut_online.usr_id ";
}
switch ($active)
{
case 0:
case 1:
$q .= "WHERE active = ".$ilDB->quote($active, "integer");
break;
case 2:
$q .= "WHERE time_limit_unlimited= ".$ilDB->quote(0, "integer");;
break;
case 3:
$qtemp = $q . ", rbac_ua, object_data WHERE rbac_ua.rol_id = object_data.obj_id AND ".
$ilDB->like("object_data.title", "text", "%crs%")." AND usr_data.usr_id = rbac_ua.usr_id";
$r = $ilDB->query($qtemp);
$course_users = array();
while ($row = $ilDB->fetchAssoc($r))
{
array_push($course_users, $row["usr_id"]);
}
if (count($course_users))
{
$q .= " WHERE ".$ilDB->in("usr_data.usr_id", $course_users, true, "integer")." ";
}
else
{
return $result_arr;
}
break;
case 4:
$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"]));
$q.= " AND last_login < ".$ilDB->quote($date, "timestamp");
break;
case 5:
$ref_id = $_SESSION["user_filter_data"];
if ($ref_id)
{
$q .= " LEFT JOIN crs_members ON usr_data.usr_id = crs_members.usr_id ".
"WHERE crs_members.obj_id = (SELECT obj_id FROM object_reference ".
"WHERE ref_id = ".$ilDB->quote($ref_id, "integer").") ";
}
break;
case 6:
global $rbacreview;
$ref_id = $_SESSION["user_filter_data"];
if ($ref_id)
{
$rolf = $rbacreview->getRoleFolderOfObject($ref_id);
$local_roles = $rbacreview->getRolesOfRoleFolder($rolf["ref_id"],false);
if (is_array($local_roles) && count($local_roles))
{
$q.= " LEFT JOIN rbac_ua ON usr_data.usr_id = rbac_ua.usr_id WHERE ".
$ilDB->in("rbac_ua.rol_id", $local_roles, false, "integer")." ";
}
}
break;
case 7:
$rol_id = $_SESSION["user_filter_data"];
if ($rol_id)
{
$q .= " LEFT JOIN rbac_ua ON usr_data.usr_id = rbac_ua.usr_id WHERE rbac_ua.rol_id = ".
$ilDB->quote($rol_id, "integer");
}
break;
}
$r = $ilDB->query($q);
while ($row = $ilDB->fetchAssoc($r))
{
$result_arr[] = $row;
}
}
return $result_arr;
}
The documentation for this class was generated from the following file: