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 $settings = array();
        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 $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 function getTitle() 
        {
            if( !$this->object )
            {
                $this->object = ilObjectFactory::getInstanceByObjId($this->getSetting('object_id'));
            }

            return $this->object->getTitle();
        }

        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 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 setSetting($name, $value)
        {
                $this->settings[$name] = $value;
        }

        public function save()
        {
            $this->saveSettings( $this->settings );
        }

        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 ) );
                }

        }

        private function phpTypeToMDBType($type) {
                switch($type) {
                        case 'string':
                                return 'text';
                        default:
                                return $type;
                }

        }

        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 );
                }
        }

        public function getSettings()
        {
                return $this->settings;
        }

        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 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 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 getRoomId()
        {
                return $this->roomId;
        }

        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 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 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();

                $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 ) )
                {
                        $row['message'] = json_decode( $row['message'] );
                        $row['message']->timestamp = $row['timestamp'];
                        if ($row['message']->public !== null && !$row['message']->public && !in_array($ilUser->getId(), explode(',', $row['recipients']))) {
                            continue;
                        }
                        
                        $result[] = $row;
                }
                return $result;
        }

        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 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 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 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 inviteUserToPrivateRoomByLogin($login, $proom_id) {
                global $ilDB;
                $user_id = ilObjUser::_lookupId($login);
                $this->inviteUserToPrivateRoom($user_id, $proom_id);
        }

        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 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 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 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 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 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 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 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 * 2);
        $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->public == 0 && $tmp->recipients) {
                        if (in_array($chatuser->getUserId(), explode(',',$tmp->recipients))) {
                                $results[] = $tmp;
                                ++$result_count;
                        }
                }
                else if ($tmp->public == 1) {
                        $results[] = $tmp;
                        ++$result_count;
                }
                
        }
        return $results;
    }

        public function getLastMessagesForChatViewer($number, $chatuser = null)
        {
                global $ilDB;

                $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 ' . $ilDB->like('message', 'text', '%"public":1%') . ' AND ' . $ilDB->like('message', 'text', '%"recipients":null%') . ') 
                                        OR 
                                        ' . $ilDB->like('message', 'text', '%"type":"%connected"%') . ')
                        ORDER BY timestamp DESC'
                );

                $results = array();
                while(($row = $ilDB->fetchAssoc($rset)))
                {
                        $tmp       = json_decode($row['message']);
                        if($tmp->type != 'message' && $row['timestamp'] && !is_numeric($tmp->timestamp))
                        {
                                $tmp->timestamp = $row['timestamp'] * 1000;
                        }
                        $results[] = $tmp;
                }
                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())
                        );
                }
        }
        
        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;
        }
}

?>