ilUserQuery Class Reference

User query class. More...

Collaboration diagram for ilUserQuery:

Public Member Functions
	__construct ()
	Constructor. More...
	setOrderField ($a_order)
	Set order field (column in usr_data) Default order is 'login'. More...
	setOrderDirection ($a_dir)
	Set order direction 'asc' or 'desc' Default is 'asc'. More...
	setOffset ($a_offset)
	Set offset. More...
	setLimit ($a_limit)
	Set result limit Default is 50. More...
	setTextFilter ($a_filter)
	Text (like) filter in login, firstname, lastname or email. More...
	setActionFilter ($a_activation)
	Set activation filter 'active' or 'inactive' or empty. More...
	setLastLogin (ilDateTime $dt=NULL)
	Set last login filter. More...
	setLimitedAccessFilter ($a_status)
	Enable limited access filter. More...
	setNoCourseFilter ($a_no_course)
	Enable no course filter. More...
	setNoGroupFilter ($a_no_group)
	Enable no group filter. More...
	setCourseGroupFilter ($a_cg_id)
	Set course / group filter object_id of course or group. More...
	setRoleFilter ($a_role_id)
	Set role filter obj_id of role. More...
	setUserFolder ($a_fold_id)
	Set user folder filter reference id of user folder or category (local user administration) More...
	setAdditionalFields ($a_add)
	Set additional fields (columns in usr_data or 'online_time') More...
	setUserFilter ($a_filter)
	Array with user ids to query against. More...
	setFirstLetterLastname ($a_fll)
	set first letter lastname filter More...
	query ()
	Query usr_data. More...

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, $a_user_folder_filter=null, $a_additional_fields='', $a_user_filter=null, $a_first_letter="")
	Get data for user administration list. More...

Private Attributes
	$order_field = 'login'
	$order_dir = 'asc'
	$offset = 0
	$limit = 50
	$text_filter = ''
	$activation = ''
	$last_login = NULL
	$limited_access = false
	$no_courses = false
	$no_groups = false
	$crs_grp = 0
	$role = 0
	$user_folder = 0
	$additional_fields = array()
	$users = array()
	$first_letter = ''
	$default_fields

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.

Constructor & Destructor Documentation

__construct()

ilUserQuery::__construct

(

)

Constructor.

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

        {
                ;
        }

Member Function Documentation

getUserListData()

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,   $a_user_folder_filter = null,   $a_additional_fields = '',   $a_user_filter = null,   $a_first_letter = ""  )

static

Get data for user administration list.

Deprecated:

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

References $query.

Referenced by ilUserLPTableGUI\getItems(), ilAssignedUsersTableGUI\getItems(), ilGroupParticipantsTableGUI\parse(), ilCourseParticipantsTableGUI\parse(), ilRepositoryUserResultTableGUI\parseUserIds(), ilSubscriberTableGUI\readSubscriberData(), and ilWaitingListTableGUI\readUserData().

        {
        
                $query = new ilUserQuery();
                $query->setOrderField($a_order_field);
                $query->setOrderDirection($a_order_dir);
                $query->setOffset($a_offset);
                $query->setLimit($a_limit);
                $query->setTextFilter($a_string_filter);
                $query->setActionFilter($a_activation_filter);
                $query->setLastLogin($a_last_login_filter);
                $query->setLimitedAccessFilter($a_limited_access_filter);
                $query->setNoCourseFilter($a_no_courses_filter);
                $query->setCourseGroupFilter($a_course_group_filter);
                $query->setRoleFilter($a_role_filter);
                $query->setUserFolder($a_user_folder_filter);
                $query->setAdditionalFields($a_additional_fields);
                $query->setUserFilter($a_user_filter);
                $query->setFirstLetterLastname($a_first_letter);
                return $query->query();
        }

Here is the caller graph for this function:

query()

ilUserQuery::query

(

)

Query usr_data.

Returns

array ('cnt', 'set')

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

References $ilDB, $limit, $offset, $query, $result, $row, ilDateTime\_before(), ilObject\_lookupType(), IL_CAL_DATETIME, IL_CAL_DAY, and IL_CAL_UNIX.

        {
                global $ilDB;
                
                $ut_join = "";
                if (is_array($this->additional_fields))
                {
                        foreach ($this->additional_fields as $f)
                        {
                                if (!in_array($f, $this->default_fields))
                                {
                                        if($f == "online_time")
                                        {
                                                $this->default_fields[] = "ut_online.online_time";                                              
                                                $ut_join = " LEFT JOIN ut_online ON usr_data.usr_id = ut_online.usr_id";
                                        }
                                        else
                                        {
                                                $this->default_fields[] = $f;
                                        }
                                }
                        }
                }               
                // count query
                $count_query = "SELECT count(usr_id) cnt".
                        " FROM usr_data";
                
                $all_multi_fields = array("interests_general", "interests_help_offered", "interests_help_looking");
                $multi_fields = array();
                
                $sql_fields = array();
                foreach($this->default_fields as $idx => $field)
                {
                        if(!$field)
                        {
                                continue;
                        }
                        
                        if(in_array($field, $all_multi_fields))
                        {
                                $multi_fields[] = $field;
                        }                       
                        else if(!stristr($field, "."))
                        {
                                $sql_fields[] = "usr_data.".$field;
                        }
                        else
                        {
                                $sql_fields[] = $field;
                        }
                }
                
                // basic query
                $query = "SELECT ".implode($sql_fields, ",").
                        " FROM usr_data".
                        $ut_join;
                        
                // filter
                $query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");

                // User filter
                if($this->users and is_array(($this->users)))
                {
                        $query .= ' AND '.$ilDB->in('usr_data.usr_id',$this->users,false,'integer');
                }

                $count_query.= " WHERE usr_data.usr_id <> ".$ilDB->quote(ANONYMOUS_USER_ID, "integer");
                $where = " AND";

                if ($this->first_letter != "")
                {
                        $add = $where." (".$ilDB->upper($ilDB->substr("usr_data.lastname", 1, 1))." = ".$ilDB->upper($ilDB->quote($this->first_letter, "text")).") ";
                        $query.= $add;
                        $count_query.= $add;
                        $where = " AND";
                }
                
                if ($this->text_filter != "")           // email, name, login
                {
                        $add = $where." (".$ilDB->like("usr_data.login", "text", "%".$this->text_filter."%")." ".
                                "OR ".$ilDB->like("usr_data.firstname", "text", "%".$this->text_filter."%")." ".
                                "OR ".$ilDB->like("usr_data.lastname", "text", "%".$this->text_filter."%")." ".
                                "OR ".$ilDB->like("usr_data.email", "text", "%".$this->text_filter."%").") ";
                        $query.= $add;
                        $count_query.= $add;
                        $where = " AND";
                }
                
                if ($this->activation != "")            // activation
                {
                        if ($this->activation == "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($this->last_login instanceof ilDateTime)     // last login
                {
                        if(ilDateTime::_before($this->last_login, new ilDateTime(time(),IL_CAL_UNIX),IL_CAL_DAY))
                        {
                                $add = $where." usr_data.last_login < ".
                                        $ilDB->quote($this->last_login->get(IL_CAL_DATETIME), "timestamp");
                                $query.= $add;
                                $count_query.= $add;
                                $where = " AND";
                        }
                }
                if ($this->limited_access)              // limited access
                {
                        $add = $where." usr_data.time_limit_unlimited= ".$ilDB->quote(0, "integer");
                        $query.= $add;
                        $count_query.= $add;
                        $where = " AND";
                }
                if ($this->no_courses)          // no courses assigned
                {
                        $add = $where." usr_data.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 ($this->no_groups)           // no groups assigned
                {
                        $add = $where." usr_data.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_grp_%')";
                        $query.= $add;
                        $count_query.= $add;
                        $where = " AND";
                }
                if ($this->crs_grp > 0)         // members of course/group
                {
                        $cgtype = ilObject::_lookupType($this->crs_grp, true);
                        $add = $where." usr_data.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_".$this->crs_grp, "text").")";
                        $query.= $add;
                        $count_query.= $add;
                        $where = " AND";
                }
                if ($this->role > 0)            // global role
                {
                        $add = $where." usr_data.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($this->role, "integer").")";
                        $query.= $add;
                        $count_query.= $add;
                        $where = " AND";
                }
                
                if($this->user_folder)
                {
                        $add = $where." ".$ilDB->in('usr_data.time_limit_owner',$this->user_folder,false,'integer');
                        $query.= $add;
                        $count_query.= $add;
                        $where = " AND";
                }

                // order by
                switch($this->order_field)
                {
                        case  "access_until":
                                if ($this->order_dir == "desc")
                                {
                                        $query.= " ORDER BY usr_data.active DESC, usr_data.time_limit_unlimited DESC, usr_data.time_limit_until DESC";
                                }
                                else
                                {
                                        $query.= " ORDER BY usr_data.active ASC, usr_data.time_limit_unlimited ASC, usr_data.time_limit_until ASC";
                                }       
                                break;
                                
                        case "online_time":
                                if ($this->order_dir == "desc")
                                {
                                        $query.= " ORDER BY ut_online.online_time DESC";
                                }
                                else
                                {
                                        $query.= " ORDER BY ut_online.online_time ASC";
                                }       
                                break;
                                
                        default:
                                if (!in_array($this->order_field, $this->default_fields))
                                {
                                        $this->order_field = "login";
                                }
                                if ($this->order_dir != "asc" && $this->order_dir != "desc")
                                {
                                        $this->order_dir = "asc";
                                }
                                $query .= " ORDER BY usr_data.".$this->order_field." ".strtoupper($this->order_dir);
                                break;
                }
                
                // count query
                $set = $ilDB->query($count_query);
                $cnt = 0;
                if ($rec = $ilDB->fetchAssoc($set))
                {
                        $cnt = $rec["cnt"];
                }
                
                $offset = (int) $this->offset;
                $limit = (int) $this->limit;
                
                // #9866: validate offset against rowcount
                if($offset >= $cnt)
                {
                        $offset = 0;
                }
                
                $ilDB->setLimit($limit, $offset);
                
                if(sizeof($multi_fields))
                {
                        $usr_ids = array();
                }
                
                // set query
                $set = $ilDB->query($query);
                $result = array();
                while($rec = $ilDB->fetchAssoc($set))
                {
                        $result[] = $rec;
                        
                        if(sizeof($multi_fields))
                        {
                                $usr_ids[] = $rec["usr_id"];
                        }
                }
                
                // add multi-field-values to user-data
                if(sizeof($multi_fields) && sizeof($usr_ids))
                {
                        $usr_multi = array();
                        $set = $ilDB->query("SELECT * FROM usr_data_multi".
                                " WHERE ".$ilDB->in("usr_id", $usr_ids, "", "integer"));
                        while($row = $ilDB->fetchAssoc($set))
                        {
                                $usr_multi[$row["usr_id"]][$row["field_id"]][] = $row["value"];
                        }                       
                        foreach($result as $idx => $item)
                        {
                                if(isset($usr_multi[$item["usr_id"]]))
                                {
                                        $result[$idx] = array_merge($item, $usr_multi[$item["usr_id"]]);                                
                                }
                        }
                }
                
                return array("cnt" => $cnt, "set" => $result);          
        }

Here is the call graph for this function:

setActionFilter()

ilUserQuery::setActionFilter

(

$a_activation

)

Set activation filter 'active' or 'inactive' or empty.

Parameters

string

$a_activation

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

        {
                $this->activation = $a_activation;
        }

setAdditionalFields()

ilUserQuery::setAdditionalFields

(

$a_add

)

Set additional fields (columns in usr_data or 'online_time')

Parameters

array

$additional_fields

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

        {
                $this->additional_fields = (array) $a_add;
        }

setCourseGroupFilter()

ilUserQuery::setCourseGroupFilter

(

$a_cg_id

)

Set course / group filter object_id of course or group.

Parameters

int

$a_cg_id

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

        {
                $this->crs_grp = $a_cg_id;
        }

setFirstLetterLastname()

ilUserQuery::setFirstLetterLastname

(

$a_fll

)

set first letter lastname filter

Parameters

string

$a_fll

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

        {
                $this->first_letter = $a_fll;
        }

setLastLogin()

ilUserQuery::setLastLogin

(

ilDateTime

$dt = NULL

)

Set last login filter.

Parameters

ilDateTime

$dt

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

        {
                $this->last_login = $dt;
        }

setLimit()

ilUserQuery::setLimit

(

$a_limit

)

Set result limit Default is 50.

Parameters

int

$a_limit

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

        {
                $this->limit = $a_limit;
        }

setLimitedAccessFilter()

ilUserQuery::setLimitedAccessFilter

(

$a_status

)

Enable limited access filter.

Parameters

bool

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

        {
                $this->limited_access = $a_status;
        }

setNoCourseFilter()

ilUserQuery::setNoCourseFilter

(

$a_no_course

)

Enable no course filter.

Parameters

bool

$a_no_course

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

        {
                $this->no_courses = $a_no_course;
        }

setNoGroupFilter()

ilUserQuery::setNoGroupFilter

(

$a_no_group

)

Enable no group filter.

Parameters

bool

$a_no_group

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

        {
                $this->no_groups = $a_no_group;
        }

setOffset()

ilUserQuery::setOffset

(

$a_offset

)

Set offset.

Parameters

int

$a_offset

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

        {
                $this->offset = $a_offset;
        }

setOrderDirection()

ilUserQuery::setOrderDirection

(

$a_dir

)

Set order direction 'asc' or 'desc' Default is 'asc'.

Parameters

string

$a_dir

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

        {
                $this->order_dir = $a_dir;
        }

setOrderField()

ilUserQuery::setOrderField

(

$a_order

)

Set order field (column in usr_data) Default order is 'login'.

Parameters

string

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

        {
                $this->order_field = $a_order;
        }

setRoleFilter()

ilUserQuery::setRoleFilter

(

$a_role_id

)

Set role filter obj_id of role.

Parameters

int

$a_role_id

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

        {
                $this->role = $a_role_id;
        }

setTextFilter()

ilUserQuery::setTextFilter

(

$a_filter

)

Text (like) filter in login, firstname, lastname or email.

Parameters

string

filter

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

        {
                $this->text_filter = $a_filter;
        }

setUserFilter()

ilUserQuery::setUserFilter

(

$a_filter

)

Array with user ids to query against.

Parameters

array

$a_filter

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

        {
                $this->users = $a_filter;
        }

setUserFolder()

ilUserQuery::setUserFolder

(

$a_fold_id

)

Set user folder filter reference id of user folder or category (local user administration)

Parameters

type

$a_fold_id

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

        {
                $this->user_folder = $a_fold_id;
        }

Field Documentation

$activation

ilUserQuery::$activation = ''

private

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

$additional_fields

ilUserQuery::$additional_fields = array()

private

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

$crs_grp

ilUserQuery::$crs_grp = 0

private

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

$default_fields

ilUserQuery::$default_fields

private

Initial value:

= array(
                "usr_id", 
                "login", 
                "firstname", 
                "lastname", 
                "email",
                "time_limit_until", 
                "time_limit_unlimited", 
                "time_limit_owner", 
                "last_login", 
                "active"
        )

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

$first_letter

ilUserQuery::$first_letter = ''

private

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

$last_login

ilUserQuery::$last_login = NULL

private

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

$limit

ilUserQuery::$limit = 50

private

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

Referenced by query().

$limited_access

ilUserQuery::$limited_access = false

private

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

$no_courses

ilUserQuery::$no_courses = false

private

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

$no_groups

ilUserQuery::$no_groups = false

private

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

$offset

ilUserQuery::$offset = 0

private

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

Referenced by query().

$order_dir

ilUserQuery::$order_dir = 'asc'

private

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

$order_field

ilUserQuery::$order_field = 'login'

private

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

$role

ilUserQuery::$role = 0

private

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

$text_filter

ilUserQuery::$text_filter = ''

private

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

$user_folder

ilUserQuery::$user_folder = 0

private

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

$users

ilUserQuery::$users = array()

private

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

---

The documentation for this class was generated from the following file:

• Services/User/classes/class.ilUserQuery.php