class.ilChatroom.php

Go to the documentation of this file.

<?php
/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
 
class ilChatroom
{
        private static $settingsTable = 'chatroom_settings';
        private static $historyTable = 'chatroom_history';
        private static $userTable = 'chatroom_users';
        private static $sessionTable = 'chatroom_sessions';
        private static $banTable = 'chatroom_bans';
        private static $privateRoomsTable = 'chatroom_prooms';
        private static $privateSessionsTable = 'chatroom_psessions';
        private static $uploadTable = 'chatroom_uploads';
        private static $privateRoomsAccessTable = 'chatroom_proomaccess';
        private $settings = array();
        private $availableSettings = array(
                'object_id'              => 'integer',
                'allow_anonymous'        => 'boolean',
                'allow_custom_usernames' => 'boolean',
                'enable_history'         => 'boolean',
                'restrict_history'       => 'boolean',
                'autogen_usernames'      => 'string',
                'room_type'              => 'string',
                'allow_private_rooms'    => 'integer',
                'display_past_msgs'      => 'integer',
                'private_rooms_enabled'  => 'boolean'
        );
        private $roomId;
 
        private $object;
 
        public static function checkUserPermissions($permissions, $ref_id, $send_info = true)
        {
                global $rbacsystem, $lng;
 
                if(!is_array($permissions))
                {
                        $permissions = array($permissions);
                }
 
                foreach($permissions as $permission)
                {
                        if(!$rbacsystem->checkAccess($permission, $ref_id))
                        {
                                if($send_info)
                                {
                                        ilUtil::sendFailure($lng->txt("permission_denied"), true);
                                }
                                return false;
                        }
                }
 
                return true;
        }
 
        public static function checkPermissionsOfUser($usr_id, $permissions, $ref_id)
        {
                global $rbacsystem, $lng;
 
                if(!is_array($permissions))
                {
                        $permissions = array($permissions);
                }
 
                foreach($permissions as $permission)
                {
                        if(!$rbacsystem->checkAccessOfUser($usr_id, $permission, $ref_id))
                        {
                                return false;
                        }
                }
 
                return true;
        }
 
        public static function byObjectId($object_id)
        {
                global $ilDB;
                $query  = 'SELECT * FROM ' . self::$settingsTable . ' WHERE object_id = %s';
                $types  = array('integer');
                $values = array($object_id);
                $rset   = $ilDB->queryF($query, $types, $values);
 
                if($row = $ilDB->fetchAssoc($rset))
                {
                        $room = new self();
                        $room->initialize($row);
                        return $room;
                }
        }
 
        public function initialize(array $rowdata)
        {
                $this->roomId = $rowdata['room_id'];
 
                foreach($this->availableSettings as $setting => $type)
                {
                        if(isset($rowdata[$setting]))
                        {
                                settype($rowdata[$setting], $this->availableSettings[$setting]);
                                $this->setSetting($setting, $rowdata[$setting]);
                        }
                }
        }
 
        public function setSetting($name, $value)
        {
                $this->settings[$name] = $value;
        }
 
        public static function byRoomId($room_id, $initObject = false)
        {
                global $ilDB;
 
                $query = 'SELECT * FROM ' . self::$settingsTable . ' WHERE room_id = %s';
 
                $types  = array('integer');
                $values = array($room_id);
 
                $rset = $ilDB->queryF($query, $types, $values);
 
                if($row = $ilDB->fetchAssoc($rset))
                {
                        $room = new self();
                        $room->initialize($row);
 
                        if($initObject)
                        {
                                $room->object = ilObjectFactory::getInstanceByObjId($row['object_id']);
                        }
 
                        return $room;
                }
        }
 
        public static function disconnectAllUsersFromAllRooms()
        {
                global $ilDB;
 
                $ilDB->manipulate('DELETE FROM ' . self::$userTable);
                $ilDB->manipulate('UPDATE ' . self::$privateRoomsTable . ' SET closed = ' . $ilDB->quote(time(), 'integer') . ' WHERE closed = 0 OR closed IS NULL');
                $ilDB->manipulate('UPDATE ' . self::$privateSessionsTable . ' SET disconnected = ' . $ilDB->quote(time(), 'integer') . ' WHERE disconnected = 0');
        }
 
        public static function findDeletablePrivateRooms()
        {
                global $ilDB;
 
                $query = '
                        SELECT private_rooms.proom_id id, MIN(disconnected) min_disconnected, MAX(disconnected) max_disconnected
                        FROM ' . self::$privateSessionsTable . ' private_sessions
                        INNER JOIN ' . self::$privateRoomsTable . ' private_rooms
                                ON private_sessions.proom_id = private_rooms.proom_id
                        WHERE closed = 0
                        GROUP BY private_rooms.proom_id
                        HAVING MIN(disconnected) > 0 AND MAX(disconnected) < %s';
                $rset  = $ilDB->queryF(
                        $query,
                        array('integer'),
                        array(time() + 60 * 5)
                );
 
                $rooms = array();
 
                while($row = $ilDB->fetchAssoc($rset))
                {
                        $rooms[$row['id']] = $row['id'];
                }
 
                $query = 'SELECT DISTINCT proom_id, room_id, object_id FROM ' . self::$privateRoomsTable
                        . ' INNER JOIN ' . self::$settingsTable . ' ON parent_id = room_id '
                        . ' WHERE ' . $ilDB->in('proom_id', $rooms, false, 'integer');
 
                $rset  = $ilDB->query($query);
                $rooms = array();
                while($row = $ilDB->fetchAssoc($rset))
                {
                        $rooms[] = array(
                                'proom_id'  => $row['proom_id'],
                                'room_id'   => $row['room_id'],
                                'object_id' => $row['object_id']
                        );
                }
 
                return $rooms;
        }
 
        public static function getUntrashedChatReferences($filter = array())
        {
                global $ilDB;
 
                // Check for parent because of an invalid parent node for the old public chat (thx @ jposselt ;-)).
                // We cannot find this old public chat and clean this automatically
                $query = '
                        SELECT od.obj_id, od.title, ore.ref_id, od.type, odp.title parent_title
                        FROM object_data od
                        INNER JOIN object_reference ore ON ore.obj_id = od.obj_id
                        INNER JOIN tree t ON t.child = ore.ref_id
                        INNER JOIN tree p ON p.child = t.parent
                        INNER JOIN object_reference orep ON orep.ref_id = p.child
                        INNER JOIN object_data odp ON odp.obj_id = orep.obj_id
                        INNER JOIN object_reference pre ON pre.ref_id = t.parent
                        INNER JOIN object_data pod ON pod.obj_id = pre.obj_id
                ';
 
                if(isset($filter['last_activity']))
                {
                        $threshold = $ilDB->quote($filter['last_activity'], 'integer');
                        $query .= "
                                INNER JOIN chatroom_settings ON chatroom_settings.object_id = od.obj_id
                                INNER JOIN chatroom_history ON chatroom_history.room_id = chatroom_settings.room_id AND chatroom_history.timestamp > $threshold
                        ";
                }
 
                $query .= '
                        WHERE od.type = %s AND t.tree > 0 AND ore.deleted IS NULL
                        GROUP BY od.obj_id, od.title, ore.ref_id, od.type, odp.title
                        ORDER BY od.title
                ';
                $res = $ilDB->queryF($query, array('text'), array('chtr'));
 
                $chats = array();
                while($row = $ilDB->fetchAssoc($res))
                {
                        $chats[] = $row;
                }
 
                return $chats;
        }
 
        public function getDescription()
        {
                if(!$this->object)
                {
                        $this->object = ilObjectFactory::getInstanceByObjId($this->getSetting('object_id'));
                }
 
                return $this->object->getDescription();
        }
 
        public function getSetting($name)
        {
                return $this->settings[$name];
        }
 
        public function save()
        {
                $this->saveSettings($this->settings);
        }
 
        public function saveSettings(array $settings)
        {
                global $ilDB;
 
                $localSettings = array();
 
                foreach($this->availableSettings as $setting => $type)
                {
                        if(isset($settings[$setting]))
                        {
                                if($type == 'boolean')
                                {
                                        $settings[$setting] = (boolean)$settings[$setting];
                                }
                                $localSettings[$setting] = array($this->phpTypeToMDBType($type), $settings[$setting]);
                        }
                }
 
                if(!$localSettings['room_type'][1])
                {
                        $localSettings['room_type'][1] = 'repository';
                }
 
                if($this->roomId)
                {
                        $ilDB->update(
                                self::$settingsTable,
                                $localSettings,
                                array('room_id' => array('integer', $this->roomId))
                        );
                }
                else
                {
                        $this->roomId = $ilDB->nextId(self::$settingsTable);
 
                        $localSettings['room_id'] = array(
                                $this->availableSettings['room_id'], $this->roomId
                        );
 
                        $ilDB->insert(self::$settingsTable, $localSettings);
                }
        }
 
        private function phpTypeToMDBType($type)
        {
                switch($type)
                {
                        case 'string':
                                return 'text';
                        default:
                                return $type;
                }
 
        }
 
        public function addHistoryEntry($message, $recipient = null, $publicMessage = true)
        {
                global $ilDB;
 
                $subRoom = 0;
                if(is_array($message))
                {
                        $subRoom = (int)$message['sub'];
                }
                else if(is_object($message))
                {
                        $subRoom = (int)$message->sub;
                }
 
                $id = $ilDB->nextId(self::$historyTable);
                $ilDB->insert(
                        self::$historyTable,
                        array(
                                'hist_id'   => array('integer', $id),
                                'room_id'   => array('integer', $this->roomId),
                                'sub_room'  => array('integer', $subRoom),
                                'message'   => array('text', json_encode($message)),
                                'timestamp' => array('integer', time()),
                        )
                );
        }
 
        public function connectUser(ilChatroomUser $user)
        {
                global $ilDB;
 
                $userdata = array(
                        'login' => $user->getUsername(),
                        'id'    => $user->getUserId()
                );
 
                $query  = 'SELECT user_id FROM ' . self::$userTable . ' WHERE room_id = %s AND user_id = %s';
                $types  = array('integer', 'integer');
                $values = array($this->roomId, $user->getUserId());
 
                if(!$ilDB->fetchAssoc($ilDB->queryF($query, $types, $values)))
                {
                        // Notice: Using replace instead of insert looks strange, because we actually know whether the selected data exists or not
                        // But we occasionally found some duplicate key errors although the data set should not exist when the following code is reached
                        $ilDB->replace(
                                self::$userTable,
                                array(
                                        'room_id' => array('integer', $this->roomId),
                                        'user_id' => array('integer', $user->getUserId())
                                ),
                                array(
                                        'userdata'  => array('text', json_encode($userdata)),
                                        'connected' => array('integer', time()),
                                )
                        );
 
                        return true;
                }
 
                return false;
        }
 
        public function getConnectedUsers()
        {
                global $ilDB;
 
                $query  = 'SELECT userdata FROM ' . self::$userTable . ' WHERE room_id = %s';
                $types  = array('integer');
                $values = array($this->roomId);
                $rset   = $ilDB->queryF($query, $types, $values);
                $users  = array();
 
                while($row = $ilDB->fetchAssoc($rset))
                {
                        $users[] = json_decode($row['userdata']);
                }
 
                return $users;
        }
 
        public function disconnectUser($user_id)
        {
                $this->disconnectUsers(array($user_id));
        }
 
        public function disconnectUsers(array $userIds)
        {
                global $ilDB;
 
                $query = 'SELECT * FROM ' . self::$userTable . ' WHERE room_id = %s AND ' .
                        $ilDB->in('user_id', $userIds, false, 'integer');
 
                $types  = array('integer');
                $values = array($this->roomId);
                $rset   = $ilDB->queryF($query, $types, $values);
 
                if($row = $ilDB->fetchAssoc($rset))
                {
                        $query       = 'SELECT proom_id FROM ' . self::$privateRoomsTable . ' WHERE parent_id = %s';
                        $rset_prooms = $ilDB->queryF($query, array('integer'), array($this->roomId));
 
                        $prooms = array();
 
                        while($row_prooms = $ilDB->fetchAssoc($rset_prooms))
                        {
                                $prooms[] = $row_prooms['proom_id'];
                        }
 
                        if(true || $this->getSetting('enable_history'))
                        {
                                $query = 'UPDATE ' . self::$privateSessionsTable . ' SET disconnected = %s WHERE ' . $ilDB->in('user_id', $userIds, false, 'integer') . ' AND ' . $ilDB->in('proom_id', $prooms, false, 'integer');
                                $ilDB->manipulateF($query, array('integer'), array(time()));
                        }
                        else
                        {
                                $query = 'DELETE FROM ' . self::$privateSessionsTable . ' WHERE ' . $ilDB->in('user_id', $userIds, false, 'integer') . ' AND ' . $ilDB->in('proom_id', $prooms, false, 'integer');
                                $ilDB->manipulate($query);
                        }
 
                        $query = 'DELETE FROM ' . self::$userTable . ' WHERE room_id = %s AND ' .
                                $ilDB->in('user_id', $userIds, false, 'integer');
 
                        $types  = array('integer');
                        $values = array($this->roomId);
                        $ilDB->manipulateF($query, $types, $values);
 
                        do
                        {
                                if($this->getSetting('enable_history'))
                                {
                                        $id = $ilDB->nextId(self::$sessionTable);
                                        $ilDB->insert(
                                                self::$sessionTable,
                                                array(
                                                        'sess_id'      => array('integer', $id),
                                                        'room_id'      => array('integer', $this->roomId),
                                                        'user_id'      => array('integer', $row['user_id']),
                                                        'userdata'     => array('text', $row['userdata']),
                                                        'connected'    => array('integer', $row['connected']),
                                                        'disconnected' => array('integer', time())
                                                )
                                        );
                                }
                        }
                        while($row = $ilDB->fetchAssoc($rset));
                }
 
        }
 
        public function getSettings()
        {
                return $this->settings;
        }
 
        public function isSubscribed($chat_userid)
        {
                global $ilDB;
 
                $query = 'SELECT count(user_id) as cnt FROM ' . self::$userTable .
                        ' WHERE room_id = %s AND user_id = %s';
 
                $types  = array('integer', 'integer');
                $values = array($this->roomId, $chat_userid);
                $rset   = $ilDB->queryF($query, $types, $values);
 
                if($rset && ($row = $ilDB->fetchAssoc($rset)) && $row['cnt'] == 1)
                        return true;
 
                return false;
        }
 
        public function isAllowedToEnterPrivateRoom($chat_userid, $proom_id)
        {
                //echo call_user_func_array('sprintf', array_merge(array($query), $values));
                global $ilDB;
 
                $query = 'SELECT count(user_id) cnt FROM ' . self::$privateRoomsAccessTable .
                        ' WHERE proom_id = %s AND user_id = %s';
 
                $types  = array('integer', 'integer');
                $values = array($proom_id, $chat_userid);
                $rset   = $ilDB->queryF($query, $types, $values);
 
                if($rset && ($row = $ilDB->fetchAssoc($rset)) && $row['cnt'] == 1)
                        return true;
 
                $query = 'SELECT count(*) cnt FROM ' . self::$privateRoomsTable .
                        ' WHERE proom_id = %s AND owner = %s';
 
                $types  = array('integer', 'integer');
                $values = array($proom_id, $chat_userid);
                $rset   = $ilDB->queryF($query, $types, $values);
 
                if($rset && ($row = $ilDB->fetchAssoc($rset)) && $row['cnt'] == 1)
                        return true;
 
                return false;
        }
 
        public function getHistory(ilDateTime $from = null, ilDateTime $to = null, $restricted_session_userid = null, $proom_id = 0)
        {
                global $ilDB, $ilUser;
 
                $join = '';
 
                if($proom_id)
                {
                        $join .=
                                'INNER JOIN ' . self::$privateSessionsTable . ' pSessionTable ' .
                                'ON pSessionTable.user_id = ' . $ilDB->quote($restricted_session_userid, 'integer') . ' ' .
                                'AND pSessionTable.proom_id = historyTable.sub_room ' .
                                'AND timestamp >= pSessionTable.connected ' .
                                'AND timestamp <= pSessionTable.disconnected ';
                }
 
                $query =
                        'SELECT historyTable.* ' .
                        'FROM ' . self::$historyTable . ' historyTable ' . $join . ' ' .
                        'WHERE historyTable.room_id = ' . $this->getRoomId();
 
                $query .= ' AND historyTable.sub_room = ' . $ilDB->quote($proom_id, 'integer');
 
                $filter = array();
 
                if($from != null)
                {
                        $filter[] = 'timestamp >= ' . $ilDB->quote($from->getUnixTime(), 'integer');
                }
 
                if($to != null)
                {
                        $filter[] = 'timestamp <= ' . $ilDB->quote($to->getUnixTime(), 'integer');
                }
 
                if($filter)
                        $query .= ' AND ' . join(' AND ', $filter);
                $query .= ' ORDER BY timestamp ASC';
 
                $rset   = $ilDB->query($query);
                $result = array();
 
                while($row = $ilDB->fetchAssoc($rset))
                {
                        $message = json_decode($row['message']);
                        if ($message === null) {
                                $message = json_decode('{}');
                        }
 
                        $row['message']            =  $message;
                        $row['message']->timestamp = $row['timestamp'];
                        if($row['message']->target !== null && !$row['message']->target->public && !in_array($ilUser->getId(), explode(',', $row['recipients'])))
                        {
                                continue;
                        }
 
                        $result[] = $row;
                }
                return $result;
        }
 
        public function getRoomId()
        {
                return $this->roomId;
        }
 
        public function getPrivateRoomSessions(ilDateTime $from = null, ilDateTime $to = null, $user_id = 0, $room_id = 0)
        {
                global $ilDB;
 
                $query = 'SELECT proom_id, title FROM ' . self::$privateRoomsTable . ' WHERE proom_id IN (
                        SELECT proom_id FROM ' . self::$privateSessionsTable . ' WHERE connected >= %s AND disconnected <= %s AND user_id = %s
 
                ) AND parent_id = %s';
 
                $rset   = $ilDB->queryF($query, array('integer', 'integer', 'integer', 'integer'), array($from->getUnixTime(), $to->getUnixTime(), $user_id, $room_id));
                $result = array();
                while($row = $ilDB->fetchAssoc($rset))
                {
                        $result[] = $row;
                }
                return $result;
        }
 
        public function saveFileUploadToDb($user_id, $filename, $type)
        {
                global $ilDB;
 
                $upload_id = $ilDB->nextId(self::$uploadTable);
 
                $ilDB->insert(
                        self::$uploadTable,
                        array(
                                'upload_id' => array('integer', $upload_id),
                                'room_id'   => array('integer', $this->roomId),
                                'user_id'   => array('integer', $user_id),
                                'filename'  => array('text', $filename),
                                'filetype'  => array('text', $type),
                                'timestamp' => array('integer', time())
                        )
                );
        }
 
        public function banUser($user_id, $comment = '')
        {
                global $ilDB;
 
                $ilDB->replace(
                        self::$banTable,
                        array(
                                'room_id' => array('integer', $this->roomId),
                                'user_id' => array('integer', $user_id)
                        ),
                        array(
                                'timestamp' => array('integer', time()),
                                'remark'    => array('text', $comment)
                        )
                );
        }
 
        public function unbanUser($user_id)
        {
                global $ilDB;
 
                if(!is_array($user_id))
                {
                        $user_id = array($user_id);
                }
 
                $query = 'DELETE FROM ' . self::$banTable . ' WHERE room_id = %s AND ' . $ilDB->in('user_id', $user_id, false, 'integer');
 
                $types  = array('integer');
                $values = array($this->getRoomId());
 
                return $ilDB->manipulateF($query, $types, $values);
        }
 
        public function isUserBanned($user_id)
        {
                global $ilDB;
 
                $query = 'SELECT COUNT(user_id) cnt FROM ' . self::$banTable . ' WHERE user_id = %s AND room_id = %s';
 
                $types  = array('integer', 'integer');
                $values = array($user_id, $this->getRoomId());
 
                $rset = $ilDB->queryF($query, $types, $values);
 
                if($rset && ($row = $ilDB->fetchAssoc($rset)) && $row['cnt'])
                {
                        return true;
                }
 
                return false;
        }
 
        public function getBannedUsers()
        {
                global $ilDB;
 
                $query  = 'SELECT chb.* FROM ' . self::$banTable . ' chb INNER JOIN usr_data ud ON chb.user_id = ud.usr_id WHERE chb.room_id = %s ';
                $types  = array('integer');
                $values = array($this->getRoomId());
                $rset   = $ilDB->queryF($query, $types, $values);
                $result = array();
 
                if($rset)
                {
                        while($row = $ilDB->fetchAssoc($rset))
                        {
                                if($row['user_id'] > 0)
                                {
                                        $user     = new ilObjUser($row['user_id']);
                                        $userdata = array(
                                                'user_id'   => $user->getId(),
                                                'firstname' => $user->getFirstname(),
                                                'lastname'  => $user->getLastname(),
                                                'login'     => $user->getLogin(),
                                                'remark'    => $row['remark']
                                        );
 
                                        $result[] = $userdata;
                                }
                                else
                                {
                                        //@todo anonymous user
                                }
                        }
                }
 
                return $result;
        }
 
        public function getLastSession(ilChatroomUser $user)
        {
                global $ilDB;
 
                $query = 'SELECT * FROM ' . self::$sessionTable . ' WHERE user_id = ' .
                        $ilDB->quote($user->getUserId(), 'integer') .
                        ' ORDER BY connected DESC';
 
                $ilDB->setLimit(1);
                $rset = $ilDB->query($query);
 
                if($row = $ilDB->fetchAssoc($rset))
                {
                        return $row;
                }
        }
 
        public function getSessions(ilChatroomUser $user)
        {
                global $ilDB;
 
                $query = 'SELECT * FROM ' . self::$sessionTable
                        . ' WHERE room_id = ' .
                        $ilDB->quote($this->getRoomId(), 'integer') .
                        ' ORDER BY connected DESC';
 
                $rset = $ilDB->query($query);
 
                $result = array();
 
                while($row = $ilDB->fetchAssoc($rset))
                {
                        $result[] = $row;
                }
 
                return $result;
        }
 
        public function addPrivateRoom($title, ilChatroomUser $owner, $settings)
        {
                global $ilDB;
 
                $nextId = $ilDB->nextId(self::$privateRoomsTable);
                $ilDB->insert(
                        self::$privateRoomsTable,
                        array(
                                'proom_id'  => array('integer', $nextId),
                                'parent_id' => array('integer', $this->roomId),
                                'title'     => array('text', $title),
                                'owner'     => array('integer', $owner->getUserId()),
                                'created'   => array('integer', time()),
                                'is_public' => array('integer', $settings['public']),
                        )
                );
 
                return $nextId;
        }
 
        public function closePrivateRoom($id)
        {
                global $ilDB;
 
                $ilDB->manipulateF(
                        'UPDATE ' . self::$privateRoomsTable . ' SET closed = %s WHERE proom_id = %s',
                        array('integer', 'integer'),
                        array(time(), $id)
                );
        }
 
        public function isOwnerOfPrivateRoom($user_id, $proom_id)
        {
                global $ilDB;
 
                $query  = 'SELECT proom_id FROM ' . self::$privateRoomsTable . ' WHERE proom_id = %s AND owner = %s';
                $types  = array('integer', 'integer');
                $values = array($proom_id, $user_id);
 
                $rset = $ilDB->queryF($query, $types, $values);
 
                if($rset && $ilDB->fetchAssoc($rset))
                {
                        return true;
                }
                return false;
        }
 
        public function sendInvitationNotification($gui, $sender, $recipient_id, $subScope = 0, $invitationLink = '')
        {
                global $lng;
 
                if($gui && !$invitationLink)
                {
                        $invitationLink = $this->getChatURL($gui, $subScope);
                }
 
                if($recipient_id > 0 && !in_array(ANONYMOUS_USER_ID, array($recipient_id)))
                {
                        if(is_numeric($sender) && $sender > 0)
                        {
                                $sender_id = $sender;
                                $usr         = ilObjectFactory::getInstanceByObjId($sender);
                                $public_name = $usr->getPublicName();
                        }
                        else if($sender instanceof ilChatroomUser)
                        {
                                if($sender->getUserId() > 0)
                                {
                                        $sender_id = $sender->getUserId();
                                }
                                else
                                {
                                        $sender_id = ANONYMOUS_USER_ID;
                                }
                                $public_name = $sender->getUsername();
                        }
                        else
                        {
                                throw new InvalidArgumentException('$sender must be an instance of ilChatroomUser or an id of an ilObjUser instance');
                        }
 
                        $lng->loadLanguageModule('mail');
 
                        $recipient  = ilObjectFactory::getInstanceByObjId($recipient_id);
                        $bodyParams = array(
                                'link'         => $invitationLink,
                                'inviter_name' => $public_name,
                                'room_name'    => $this->getTitle(),
                                'salutation'   => $lng->txt('mail_salutation_' . $recipient->getGender()) . ' ' . $recipient->getFullname()
                        );
 
                        if($subScope)
                        {
                                $bodyParams['room_name'] .= ' - ' . self::lookupPrivateRoomTitle($subScope);
                        }
 
                        require_once 'Services/Notifications/classes/class.ilNotificationConfig.php';
                        $notification = new ilNotificationConfig('chat_invitation');
                        $notification->setTitleVar('chat_invitation', $bodyParams, 'chatroom');
                        $notification->setShortDescriptionVar('chat_invitation_short', $bodyParams, 'chatroom');
                        $notification->setLongDescriptionVar('chat_invitation_long', $bodyParams, 'chatroom');
                        $notification->setAutoDisable(false);
                        $notification->setLink($invitationLink);
                        $notification->setIconPath('templates/default/images/icon_chtr.svg');
                        $notification->setValidForSeconds(ilNotificationConfig::TTL_LONG);
                        $notification->setVisibleForSeconds(ilNotificationConfig::DEFAULT_TTS);
 
                        $notification->setHandlerParam('mail.sender', $sender_id);
 
                        $notification->notifyByUsers(array($recipient_id));
                }
        }
 
        public function getChatURL($gui, $scope_id = 0)
        {
                include_once 'Services/Link/classes/class.ilLink.php';
 
                $url = '';
 
                if(is_object($gui))
                {
                        if($scope_id)
                        {
                                $url = ilLink::_getStaticLink($gui->object->getRefId(), $gui->object->getType(), true, '_' . $scope_id);
                        }
                        else
                        {
                                $url = ilLink::_getStaticLink($gui->object->getRefId(), $gui->object->getType());
                        }
                }
 
                return $url;
        }
 
        public function getTitle()
        {
                if(!$this->object)
                {
                        $this->object = ilObjectFactory::getInstanceByObjId($this->getSetting('object_id'));
                }
 
                return $this->object->getTitle();
        }
 
        public static function lookupPrivateRoomTitle($proom_id)
        {
                global $ilDB;
 
                $query  = 'SELECT title FROM ' . self::$privateRoomsTable . ' WHERE proom_id = %s';
                $types  = array('integer');
                $values = array($proom_id);
 
                $rset = $ilDB->queryF($query, $types, $values);
 
                if($row = $ilDB->fetchAssoc($rset))
                {
                        return $row['title'];
                }
 
                return 'unkown';
        }
 
        public function inviteUserToPrivateRoomByLogin($login, $proom_id)
        {
                global $ilDB;
                $user_id = ilObjUser::_lookupId($login);
                $this->inviteUserToPrivateRoom($user_id, $proom_id);
        }
 
        public function inviteUserToPrivateRoom($user_id, $proom_id)
        {
                global $ilDB;
 
                $query  = 'DELETE FROM ' . self::$privateRoomsAccessTable . ' WHERE user_id = %s AND proom_id = %s';
                $types  = array('integer', 'integer');
                $values = array($user_id, $proom_id);
 
                $ilDB->manipulateF($query, $types, $values);
 
                $ilDB->insert(self::$privateRoomsAccessTable, array(
                        'user_id'  => array('integer', $user_id),
                        'proom_id' => array('integer', $proom_id)
                ));
        }
 
        public function getActivePrivateRooms($userid)
        {
                global $ilDB;
 
                $query  = '
                        SELECT roomtable.title, roomtable.proom_id, accesstable.user_id id, roomtable.owner rowner
                        FROM ' . self::$privateRoomsTable . ' roomtable
                        LEFT JOIN ' . self::$privateRoomsAccessTable . ' accesstable
                        ON roomtable.proom_id = accesstable.proom_id
                        AND accesstable.user_id = %s
                        WHERE parent_id = %s
                        AND (closed = 0 OR closed IS NULL)
                        AND (accesstable.user_id IS NOT NULL OR roomtable.owner = %s)';
                $types  = array('integer', 'integer', 'integer');
                $values = array($userid, $this->roomId, $userid);
                $rset   = $ilDB->queryF($query, $types, $values);
                $rooms  = array();
 
                while($row = $ilDB->fetchAssoc($rset))
                {
                        $row['active_users']     = $this->listUsersInPrivateRoom($row['id']);
                        $row['owner']            = $row['rowner'];
                        $rooms[$row['proom_id']] = $row;
                }
 
                return $rooms;
        }
 
        public function listUsersInPrivateRoom($private_room_id)
        {
                global $ilDB;
 
                $query  = '
                        SELECT chatroom_users.user_id FROM ' . self::$privateSessionsTable . '
                        INNER JOIN chatroom_users ON chatroom_users.user_id = ' . self::$privateSessionsTable . '.user_id WHERE proom_id = %s AND disconnected = 0
                ';
                $types  = array('integer');
                $values = array($private_room_id);
                $rset   = $ilDB->queryF($query, $types, $values);
 
                $users = array();
 
                while($row = $ilDB->fetchAssoc($rset))
                {
                        $users[$row['user_id']] = $row['user_id'];
                }
 
                return array_values($users);
        }
 
        public function subscribeUserToPrivateRoom($room_id, $user_id)
        {
                global $ilDB;
 
                if(!$this->userIsInPrivateRoom($room_id, $user_id))
                {
                        $id = $ilDB->nextId(self::$privateSessionsTable);
                        $ilDB->insert(
                                self::$privateSessionsTable,
                                array(
                                        'psess_id'     => array('integer', $id),
                                        'proom_id'     => array('integer', $room_id),
                                        'user_id'      => array('integer', $user_id),
                                        'connected'    => array('integer', time()),
                                        'disconnected' => array('integer', 0),
                                )
                        );
                }
        }
 
        public function userIsInPrivateRoom($room_id, $user_id)
        {
                global $ilDB;
 
                $query  = 'SELECT proom_id id FROM ' . self::$privateSessionsTable . ' WHERE user_id = %s AND proom_id = %s AND disconnected = 0';
                $types  = array('integer', 'integer');
                $values = array($user_id, $room_id);
                $rset   = $ilDB->queryF($query, $types, $values);
                if($ilDB->fetchAssoc($rset))
                        return true;
                return false;
        }
 
        public function unsubscribeUserFromPrivateRoom($room_id, $user_id)
        {
                global $ilDB;
 
                $ilDB->update(
                        self::$privateSessionsTable,
                        array(
                                'disconnected' => array('integer', time())
                        ),
                        array(
                                'proom_id' => array('integer', $room_id),
                                'user_id'  => array('integer', $user_id)
                        )
                );
        }
 
        public function countActiveUsers()
        {
                global $ilDB;
 
                $query = 'SELECT count(user_id) as cnt FROM ' . self::$userTable .
                        ' WHERE room_id = %s';
 
                $types  = array('integer');
                $values = array($this->roomId);
                $rset   = $ilDB->queryF($query, $types, $values);
 
                if($rset && ($row = $ilDB->fetchAssoc($rset)) && $row['cnt'] == 1)
                        return $row['cnt'];
 
                return 0;
        }
 
        public function getUniquePrivateRoomTitle($title)
        {
                global $ilDB;
 
                $query = 'SELECT title FROM ' . self::$privateRoomsTable . ' WHERE parent_id = %s and closed = 0';
                $rset  = $ilDB->queryF($query, array('integer'), array($this->roomId));
 
                $titles = array();
 
                while($row = $ilDB->fetchAssoc($rset))
                {
                        $titles[] = $row['title'];
                }
 
                $suffix = '';
                $i      = 0;
                do
                {
                        if(!in_array($title . $suffix, $titles))
                        {
                                $title .= $suffix;
                                break;
                        }
 
                        ++$i;
 
                        $suffix = ' (' . $i . ')';
                }
                while(true);
 
                return $title;
        }
 
        public function getAllRooms($user_id)
        {
                global $ilDB;
 
                $query = "
       SELECT      room_id, od.title
       FROM        object_data od
 
       INNER JOIN  " . self::$settingsTable . "
           ON      object_id = od.obj_id
 
       INNER JOIN  " . self::$privateRoomsTable . " prt
           ON      prt.owner = %s
 
       WHERE       od.type = 'chtr'
       ";
 
                $types  = array('integer');
                $values = array($user_id);
 
                $res = $ilDB->queryF($query, $types, $values);
 
                $rooms = array();
 
                while($row = $ilDB->fetchAssoc($res))
                {
                        $room_id         = $row['room_id'];
                        $rooms[$room_id] = $row['title'];
                }
 
                return $rooms;
        }
 
        public function getPrivateSubRooms($parent_room, $user_id)
        {
                global $ilDB;
 
                $query = "
       SELECT      proom_id, parent_id
       FROM        chatroom_prooms
       WHERE       parent_id = %s
       AND     owner = %s
       AND     closed = 0
       ";
 
                $types  = array('integer', 'integer');
                $values = array($parent_room, $user_id);
 
                $res = $ilDB->queryF($query, $types, $values);
 
                $priv_rooms = array();
 
                while($row = $ilDB->fetchAssoc($res))
                {
                        $proom_id              = $row['proom_id'];
                        $priv_rooms[$proom_id] = $row['parent_id'];
                }
 
                return $priv_rooms;
        }
 
        public function getRefIdByRoomId($room_id)
        {
                global $ilDB;
 
                $query = "
       SELECT      objr.ref_id
       FROM        object_reference    objr
 
       INNER JOIN  chatroom_settings   cs
           ON      cs.object_id = objr.obj_id
 
       INNER JOIN  object_data     od
           ON      od.obj_id = cs.object_id
 
       WHERE       cs.room_id = %s
       ";
 
                $types  = array('integer');
                $values = array($room_id);
 
                $res = $ilDB->queryF($query, $types, $values);
 
                $row = $ilDB->fetchAssoc($res);
 
                return $row['ref_id'];
        }
 
        public function getLastMessagesForChatViewer($number, $chatuser = null)
        {
                return $this->getLastMessages($number, $chatuser);
        }
 
        public function getLastMessages($number, $chatuser = null)
        {
                global $ilDB;
 
                // There is currently no way to check if a message is private or not
                // by sql. So we fetch twice as much as we need and hope that there
                // are not more than $number private messages.
                $ilDB->setLimit($number);
                $rset = $ilDB->query(
                        'SELECT *
                        FROM ' . self::$historyTable . '
                        WHERE room_id = ' . $ilDB->quote($this->roomId, 'integer') . '
                        AND sub_room = 0
                        AND (
                                (' . $ilDB->like('message', 'text', '%"type":"message"%') . ' AND NOT ' . $ilDB->like('message', 'text', '%"public":0%') . ')
                                OR ' . $ilDB->like('message', 'text', '%"target":{%"id":"' . $chatuser->getUserId() . '"%') . '
                                OR ' . $ilDB->like('message', 'text', '%"from":{"id":' . $chatuser->getUserId() . '%') . '
                        )
                        ORDER BY timestamp DESC'
                );
                /*$rset = $ilDB->queryF('SELECT * FROM ' . self::$historyTable . ' WHERE room_id = %s AND sub_room = 0 ORDER BY timestamp DESC', array('integer'), array($this->roomId));*/
 
                $result_count = 0;
                $results      = array();
                while(($row = $ilDB->fetchAssoc($rset)) && $result_count < $number)
                {
                        $tmp = json_decode($row['message']);
                        if($chatuser !== null && $tmp->target != null && $tmp->target->public == 0)
                        {
                                if($chatuser->getUserId() == $tmp->target->id || $chatuser->getUserId() == $tmp->from->id)
                                {
                                        $results[] = $tmp;
                                        ++$result_count;
                                }
                        }
                        else
                        {
                                $results[] = $tmp;
                                ++$result_count;
                        }
                }
 
                $rset = $ilDB->query(
                        'SELECT *
                        FROM ' . self::$historyTable . '
                        WHERE room_id = ' . $ilDB->quote($this->roomId, 'integer') . '
                        AND sub_room = 0
                        AND ' . $ilDB->like('message', 'text', '%"type":"notice"%') . '
                        AND timestamp <= ' . $ilDB->quote($results[0]->timestamp, 'integer') . ' AND timestamp >= ' . $ilDB->quote($results[$result_count - 1]->timestamp, 'integer') . '
 
                        ORDER BY timestamp DESC'
                );
 
                while(($row = $ilDB->fetchAssoc($rset)))
                {
                        $tmp       = json_decode($row['message']);
                        $results[] = $tmp;
                }
 
                \usort($results, function ($a, $b)
                {
                        $a_timestamp = strlen($a->timestamp) == 13 ? substr($a->timestamp, 0, -3) : $a->timestamp;
                        $b_timestamp = strlen($b->timestamp) == 13 ? substr($b->timestamp, 0, -3) : $b->timestamp;
 
                        return $b_timestamp - $a_timestamp;
                });
 
                return $results;
        }
 
        public function clearMessages($sub_room)
        {
                global $ilDB;
 
                $ilDB->queryF(
                        'DELETE FROM ' . self::$historyTable . ' WHERE room_id = %s AND sub_room = %s',
                        array('integer', 'integer'),
                        array($this->roomId, (int)$sub_room)
                );
 
                if($sub_room)
                {
                        $ilDB->queryF(
                                'DELETE FROM ' . self::$privateSessionsTable . ' WHERE proom_id = %s AND disconnected < %s',
                                array('integer', 'integer'),
                                array($sub_room, time())
                        );
                }
                else
                {
                        $ilDB->queryF(
                                'DELETE FROM ' . self::$sessionTable . ' WHERE room_id = %s AND disconnected < %s',
                                array('integer', 'integer'),
                                array($this->roomId, time())
                        );
                }
        }
}