1<?php
2/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3
4/**
5 * Class ilChatroom
6 * @author  Jan Posselt <jposselt@databay.de>
7 * @version $Id$
8 * @ingroup ModulesChatroom
9 */
10class ilChatroom
11{
12    private static $settingsTable = 'chatroom_settings';
13    private static $historyTable = 'chatroom_history';
14    private static $userTable = 'chatroom_users';
15    private static $sessionTable = 'chatroom_sessions';
16    private static $banTable = 'chatroom_bans';
17    private static $privateRoomsTable = 'chatroom_prooms';
18    private static $privateSessionsTable = 'chatroom_psessions';
19    private static $uploadTable = 'chatroom_uploads';
20    private static $privateRoomsAccessTable = 'chatroom_proomaccess';
21    private $settings = array();
22    /**
23     * Each value of this array describes a setting with the internal type.
24     * The type must be a type wich can be set by the function settype
25     * @see http://php.net/manual/de/function.settype.php
26     * @var array string => string
27     */
28    private $availableSettings = array(
29        'object_id' => 'integer',
30        'online_status' => 'integer',
31        'allow_anonymous' => 'boolean',
32        'allow_custom_usernames' => 'boolean',
33        'enable_history' => 'boolean',
34        'restrict_history' => 'boolean',
35        'autogen_usernames' => 'string',
36        'room_type' => 'string',
37        'allow_private_rooms' => 'integer',
38        'display_past_msgs' => 'integer',
39        'private_rooms_enabled' => 'boolean'
40    );
41    private $roomId;
42
43    private $object;
44
45    /**
46     * Checks user permissions by given array and ref_id.
47     * @param string|array  $permissions
48     * @param integer       $ref_id
49     * @param bool          $send_info
50     * @return bool
51     */
52    public static function checkUserPermissions($permissions, $ref_id, $send_info = true)
53    {
54        global $DIC;
55
56        if (!is_array($permissions)) {
57            $permissions = array($permissions);
58        }
59
60        $hasPermissions = self::checkPermissions($DIC->user()->getId(), $ref_id, $permissions);
61        if (!$hasPermissions && $send_info) {
62            ilUtil::sendFailure($DIC->language()->txt('permission_denied'), true);
63
64            return false;
65        }
66
67        return $hasPermissions;
68    }
69
70    /**
71     * Checks user permissions in question for a given user id in relation
72     * to a given ref_id.
73     * @param integer       $usr_id
74     * @param array|string  $permissions
75     * @param integer       $ref_id
76     * @return bool
77     */
78    public static function checkPermissionsOfUser($usr_id, $permissions, $ref_id)
79    {
80        if (!is_array($permissions)) {
81            $permissions = array($permissions);
82        }
83
84        return self::checkPermissions($usr_id, $ref_id, $permissions);
85    }
86
87    /**
88     * @param int   $usrId
89     * @param int   $refId
90     * @param array $permissions
91     * @return bool
92     */
93    protected static function checkPermissions($usrId, $refId, array $permissions)
94    {
95        global $DIC;
96
97        require_once 'Modules/Chatroom/classes/class.ilObjChatroom.php';
98        $pub_ref_id = ilObjChatroom::_getPublicRefId();
99
100        foreach ($permissions as $permission) {
101            if ($pub_ref_id == $refId) {
102                $hasAccess = $DIC->rbac()->system()->checkAccessOfUser($usrId, $permission, $refId);
103                if ($hasAccess) {
104                    $hasWritePermission = $DIC->rbac()->system()->checkAccessOfUser($usrId, 'write', $refId);
105                    if ($hasWritePermission) {
106                        continue;
107                    }
108
109                    $visible = null;
110                    $a_obj_id = ilObject::_lookupObjId($refId);
111                    $active = ilObjChatroomAccess::isActivated($refId, $a_obj_id, $visible);
112
113                    switch ($permission) {
114                        case 'visible':
115                            if (!$active) {
116                                $GLOBALS['DIC']->access()->addInfoItem(IL_NO_OBJECT_ACCESS, $GLOBALS['DIC']->language()->txt('offline'));
117                            }
118
119                            if (!$active && !$visible) {
120                                return false;
121                            }
122                            break;
123
124                        case 'read':
125                            if (!$active) {
126                                $GLOBALS['DIC']->access()->addInfoItem(IL_NO_OBJECT_ACCESS, $GLOBALS['DIC']->language()->txt('offline'));
127                                return false;
128                            }
129                            break;
130                    }
131                }
132            } else {
133                $hasAccess = $DIC->access()->checkAccessOfUser($usrId, $permission, '', $refId);
134            }
135
136            if (!$hasAccess) {
137                return false;
138            }
139        }
140
141        return true;
142    }
143
144    /**
145     * Returns ilChatroom object by given $object_id.
146     * @param integer        $object_id
147     * @return ilChatroom
148     */
149    public static function byObjectId($object_id)
150    {
151        global $DIC;
152
153        $query = 'SELECT * FROM ' . self::$settingsTable . ' WHERE object_id = %s';
154        $types = array('integer');
155        $values = array($object_id);
156        $rset = $DIC->database()->queryF($query, $types, $values);
157
158        if ($row = $DIC->database()->fetchAssoc($rset)) {
159            $room = new self();
160            $room->initialize($row);
161            return $room;
162        }
163    }
164
165    /**
166     * Sets $this->roomId by given array $rowdata and calls setSetting method
167     * foreach available setting in $this->availableSettings.
168     * @param array $rowdata
169     */
170    public function initialize(array $rowdata)
171    {
172        $this->roomId = $rowdata['room_id'];
173
174        foreach ($this->availableSettings as $setting => $type) {
175            if (isset($rowdata[$setting])) {
176                settype($rowdata[$setting], $this->availableSettings[$setting]);
177                $this->setSetting($setting, $rowdata[$setting]);
178            }
179        }
180    }
181
182    /**
183     * Sets given name and value as setting into $this->settings array.
184     * @param string $name
185     * @param mixed  $value
186     */
187    public function setSetting($name, $value)
188    {
189        $this->settings[$name] = $value;
190    }
191
192    /**
193     * Returns ilChatroom by given $room_id
194     * @param integer        $room_id
195     * @return ilChatroom
196     */
197    public static function byRoomId($room_id, $initObject = false)
198    {
199        global $DIC;
200
201        $query = 'SELECT * FROM ' . self::$settingsTable . ' WHERE room_id = %s';
202
203        $types = array('integer');
204        $values = array($room_id);
205
206        $rset = $DIC->database()->queryF($query, $types, $values);
207
208        if ($row = $DIC->database()->fetchAssoc($rset)) {
209            $room = new self();
210            $room->initialize($row);
211
212            if ($initObject) {
213                $room->object = ilObjectFactory::getInstanceByObjId($row['object_id']);
214            }
215
216            return $room;
217        }
218    }
219
220    /**
221     * Deletes all entrys from userTable.
222     */
223    public static function disconnectAllUsersFromAllRooms()
224    {
225        global $DIC;
226
227        $DIC->database()->manipulate('DELETE FROM ' . self::$userTable);
228        $DIC->database()->manipulate('UPDATE ' . self::$privateRoomsTable . ' SET closed = ' . $DIC->database()->quote(time(), 'integer') . ' WHERE closed = 0 OR closed IS NULL');
229        $DIC->database()->manipulate('UPDATE ' . self::$privateSessionsTable . ' SET disconnected = ' . $DIC->database()->quote(time(), 'integer') . ' WHERE disconnected = 0');
230        /**
231         * @todo nicht nur löschen, auch in Session Tabelle nachpflegen
232         */
233    }
234
235    public static function findDeletablePrivateRooms()
236    {
237        global $DIC;
238
239        $query = '
240			SELECT private_rooms.proom_id id, MIN(disconnected) min_disconnected, MAX(disconnected) max_disconnected
241			FROM ' . self::$privateSessionsTable . ' private_sessions
242			INNER JOIN ' . self::$privateRoomsTable . ' private_rooms
243				ON private_sessions.proom_id = private_rooms.proom_id
244			WHERE closed = 0
245			GROUP BY private_rooms.proom_id
246			HAVING MIN(disconnected) > 0 AND MAX(disconnected) < %s';
247        $rset = $DIC->database()->queryF(
248            $query,
249            array('integer'),
250            array(time() + 60 * 5)
251        );
252
253        $rooms = array();
254
255        while ($row = $DIC->database()->fetchAssoc($rset)) {
256            $rooms[$row['id']] = $row['id'];
257        }
258
259        $query = 'SELECT DISTINCT proom_id, room_id, object_id FROM ' . self::$privateRoomsTable
260            . ' INNER JOIN ' . self::$settingsTable . ' ON parent_id = room_id '
261            . ' WHERE ' . $DIC->database()->in('proom_id', $rooms, false, 'integer');
262
263        $rset = $DIC->database()->query($query);
264        $rooms = array();
265        while ($row = $DIC->database()->fetchAssoc($rset)) {
266            $rooms[] = array(
267                'proom_id' => $row['proom_id'],
268                'room_id' => $row['room_id'],
269                'object_id' => $row['object_id']
270            );
271        }
272
273        return $rooms;
274    }
275
276    public static function getUntrashedChatReferences($filter = array())
277    {
278        global $DIC;
279
280        // Check for parent because of an invalid parent node for the old public chat (thx @ jposselt ;-)).
281        // We cannot find this old public chat and clean this automatically
282        $query = '
283			SELECT od.obj_id, od.title, ore.ref_id, od.type, odp.title parent_title
284			FROM object_data od
285			INNER JOIN object_reference ore ON ore.obj_id = od.obj_id
286			INNER JOIN tree t ON t.child = ore.ref_id
287			INNER JOIN tree p ON p.child = t.parent
288			INNER JOIN object_reference orep ON orep.ref_id = p.child
289			INNER JOIN object_data odp ON odp.obj_id = orep.obj_id
290			INNER JOIN object_reference pre ON pre.ref_id = t.parent
291			INNER JOIN object_data pod ON pod.obj_id = pre.obj_id
292		';
293
294        if (isset($filter['last_activity'])) {
295            $threshold = $DIC->database()->quote($filter['last_activity'], 'integer');
296            $query .= "
297				INNER JOIN chatroom_settings ON chatroom_settings.object_id = od.obj_id
298				INNER JOIN chatroom_history ON chatroom_history.room_id = chatroom_settings.room_id AND chatroom_history.timestamp > $threshold
299			";
300        }
301
302        $query .= '
303			WHERE od.type = %s AND t.tree > 0 AND ore.deleted IS NULL
304			GROUP BY od.obj_id, od.title, ore.ref_id, od.type, odp.title
305			ORDER BY od.title
306		';
307        $res = $DIC->database()->queryF($query, array('text'), array('chtr'));
308
309        $chats = array();
310        while ($row = $DIC->database()->fetchAssoc($res)) {
311            $chats[] = $row;
312        }
313
314        return $chats;
315    }
316
317    public function getDescription()
318    {
319        if (!$this->object) {
320            $this->object = ilObjectFactory::getInstanceByObjId($this->getSetting('object_id'));
321        }
322
323        return $this->object->getDescription();
324    }
325
326    /**
327     * Returns setting from $this->settings array by given name.
328     * @param string $name
329     * @return mixed
330     */
331    public function getSetting($name)
332    {
333        return $this->settings[$name];
334    }
335
336    /**
337     * Saves settings using $this->settings
338     */
339    public function save()
340    {
341        $this->saveSettings($this->settings);
342    }
343
344    /**
345     * Saves settings into settingsTable using given settings array.
346     * @param array          $settings
347     */
348    public function saveSettings(array $settings)
349    {
350        global $DIC;
351
352        $localSettings = array();
353
354        foreach ($this->availableSettings as $setting => $type) {
355            if (isset($settings[$setting])) {
356                if ($type == 'boolean') {
357                    $settings[$setting] = (boolean) $settings[$setting];
358                }
359                $localSettings[$setting] = array($this->phpTypeToMDBType($type), $settings[$setting]);
360            }
361        }
362
363        if (!$localSettings['room_type'][1]) {
364            $localSettings['room_type'][1] = 'repository';
365        }
366
367        if ($this->roomId) {
368            $DIC->database()->update(
369                self::$settingsTable,
370                $localSettings,
371                array('room_id' => array('integer', $this->roomId))
372            );
373        } else {
374            $this->roomId = $DIC->database()->nextId(self::$settingsTable);
375
376            $localSettings['room_id'] = array(
377                $this->availableSettings['room_id'], $this->roomId
378            );
379
380            $DIC->database()->insert(self::$settingsTable, $localSettings);
381        }
382    }
383
384    private function phpTypeToMDBType($type)
385    {
386        switch ($type) {
387            case 'string':
388                return 'text';
389            default:
390                return $type;
391        }
392    }
393
394    /**
395     * Inserts entry into historyTable.
396     * @todo $recipient, $publicMessage speichern
397     * @param string         $message
398     * @param string         $recipient
399     * @param boolean        $publicMessage
400     */
401    public function addHistoryEntry($message, $recipient = null, $publicMessage = true)
402    {
403        global $DIC;
404
405        $subRoom = 0;
406        $timestamp = 0;
407        if (is_array($message)) {
408            $subRoom = (int) $message['sub'];
409            $timestamp = (int) $message['timestamp'];
410        } elseif (is_object($message)) {
411            $subRoom = (int) $message->sub;
412            $timestamp = (int) $message->timestamp;
413        }
414
415        $id = $DIC->database()->nextId(self::$historyTable);
416        $DIC->database()->insert(
417            self::$historyTable,
418            array(
419                'hist_id' => array('integer', $id),
420                'room_id' => array('integer', $this->roomId),
421                'sub_room' => array('integer', $subRoom),
422                'message' => array('text', json_encode($message)),
423                'timestamp' => array('integer', ($timestamp > 0 ? $timestamp : time())),
424            )
425        );
426    }
427
428    /**
429     * Connects user by inserting userdata into userTable.
430     * Checks if user is already connected by using the given $user object
431     * for selecting the userId from userTable. If no entry is found, matching
432     * userId and roomId, the userdata is inserted into the userTable to
433     * connect the user.
434     * @param ilChatroomUser $user
435     * @return boolean
436     */
437    public function connectUser(ilChatroomUser $user)
438    {
439        global $DIC;
440
441        $userdata = array(
442            'login' => $user->getUsername(),
443            'id' => $user->getUserId()
444        );
445
446        $query = 'SELECT user_id FROM ' . self::$userTable . ' WHERE room_id = %s AND user_id = %s';
447        $types = array('integer', 'integer');
448        $values = array($this->roomId, $user->getUserId());
449
450        if (!$DIC->database()->fetchAssoc($DIC->database()->queryF($query, $types, $values))) {
451            // Notice: Using replace instead of insert looks strange, because we actually know whether the selected data exists or not
452            // But we occasionally found some duplicate key errors although the data set should not exist when the following code is reached
453            $DIC->database()->replace(
454                self::$userTable,
455                array(
456                    'room_id' => array('integer', $this->roomId),
457                    'user_id' => array('integer', $user->getUserId())
458                ),
459                array(
460                    'userdata' => array('text', json_encode($userdata)),
461                    'connected' => array('integer', time()),
462                )
463            );
464
465            return true;
466        }
467
468        return false;
469    }
470
471    /**
472     * Returns an array of connected users.
473     * Returns an array of user objects containing all users having an entry
474     * in userTable, matching the roomId.
475     * @param bool $only_data
476     * @return array
477     */
478    public function getConnectedUsers($only_data = true)
479    {
480        global $DIC;
481
482        $query = 'SELECT ' . ($only_data ? 'userdata' : '*') . ' FROM ' . self::$userTable . ' WHERE room_id = %s';
483        $types = array('integer');
484        $values = array($this->roomId);
485        $rset = $DIC->database()->queryF($query, $types, $values);
486        $users = array();
487
488        while ($row = $DIC->database()->fetchAssoc($rset)) {
489            $users[] = $only_data ? json_decode($row['userdata']) : $row;
490        }
491
492        return $users;
493    }
494
495    /**
496     * Creates userId array by given $user object and calls disconnectUsers
497     * method.
498     * @param int $user_id
499     */
500    public function disconnectUser($user_id)
501    {
502        $this->disconnectUsers(array($user_id));
503    }
504
505    /**
506     * Disconnects users by deleting userdata from userTable using given userId array.
507     * Deletes entrys from userTable, matching roomId and userId if existing and
508     * inserts userdata and disconnection time into sessionTable.
509     * @param array          $userIds
510     */
511    public function disconnectUsers(array $userIds)
512    {
513        global $DIC;
514
515        $query = 'SELECT * FROM ' . self::$userTable . ' WHERE room_id = %s AND ' .
516            $DIC->database()->in('user_id', $userIds, false, 'integer');
517
518        $types = array('integer');
519        $values = array($this->roomId);
520        $rset = $DIC->database()->queryF($query, $types, $values);
521
522        if ($row = $DIC->database()->fetchAssoc($rset)) {
523            $query = 'SELECT proom_id FROM ' . self::$privateRoomsTable . ' WHERE parent_id = %s';
524            $rset_prooms = $DIC->database()->queryF($query, array('integer'), array($this->roomId));
525
526            $prooms = array();
527
528            while ($row_prooms = $DIC->database()->fetchAssoc($rset_prooms)) {
529                $prooms[] = $row_prooms['proom_id'];
530            }
531
532            if (true || $this->getSetting('enable_history')) {
533                $query = 'UPDATE ' . self::$privateSessionsTable . ' SET disconnected = %s WHERE ' . $DIC->database()->in('user_id', $userIds, false, 'integer') . ' AND ' . $DIC->database()->in('proom_id', $prooms, false, 'integer');
534                $DIC->database()->manipulateF($query, array('integer'), array(time()));
535            } else {
536                $query = 'DELETE FROM ' . self::$privateSessionsTable . ' WHERE ' . $DIC->database()->in('user_id', $userIds, false, 'integer') . ' AND ' . $DIC->database()->in('proom_id', $prooms, false, 'integer');
537                $DIC->database()->manipulate($query);
538            }
539
540            $query = 'DELETE FROM ' . self::$userTable . ' WHERE room_id = %s AND ' .
541                $DIC->database()->in('user_id', $userIds, false, 'integer');
542
543            $types = array('integer');
544            $values = array($this->roomId);
545            $DIC->database()->manipulateF($query, $types, $values);
546
547            do {
548                if ($this->getSetting('enable_history')) {
549                    $id = $DIC->database()->nextId(self::$sessionTable);
550                    $DIC->database()->insert(
551                        self::$sessionTable,
552                        array(
553                            'sess_id' => array('integer', $id),
554                            'room_id' => array('integer', $this->roomId),
555                            'user_id' => array('integer', $row['user_id']),
556                            'userdata' => array('text', $row['userdata']),
557                            'connected' => array('integer', $row['connected']),
558                            'disconnected' => array('integer', time())
559                        )
560                    );
561                }
562            } while ($row = $DIC->database()->fetchAssoc($rset));
563        }
564    }
565
566    /**
567     * Returns $this->settings array.
568     * @return array
569     */
570    public function getSettings()
571    {
572        return $this->settings;
573    }
574
575    /**
576     * Returns true if entry exists in userTable matching given $chat_userid
577     * and $this->roomId.
578     * @param integer        $chat_userid
579     * @return boolean
580     */
581    public function isSubscribed($chat_userid)
582    {
583        global $DIC;
584
585        $query = 'SELECT count(user_id) as cnt FROM ' . self::$userTable .
586            ' WHERE room_id = %s AND user_id = %s';
587
588        $types = array('integer', 'integer');
589        $values = array($this->roomId, $chat_userid);
590        $rset = $DIC->database()->queryF($query, $types, $values);
591
592        if ($rset && ($row = $DIC->database()->fetchAssoc($rset)) && $row['cnt'] == 1) {
593            return true;
594        }
595
596        return false;
597    }
598
599    public function isAllowedToEnterPrivateRoom($chat_userid, $proom_id)
600    {
601        global $DIC;
602
603        $query = 'SELECT count(user_id) cnt FROM ' . self::$privateRoomsAccessTable .
604            ' WHERE proom_id = %s AND user_id = %s';
605
606        $types = array('integer', 'integer');
607        $values = array($proom_id, $chat_userid);
608        $rset = $DIC->database()->queryF($query, $types, $values);
609
610        if ($rset && ($row = $DIC->database()->fetchAssoc($rset)) && $row['cnt'] == 1) {
611            return true;
612        }
613
614        $query = 'SELECT count(*) cnt FROM ' . self::$privateRoomsTable .
615            ' WHERE proom_id = %s AND owner = %s';
616
617        $types = array('integer', 'integer');
618        $values = array($proom_id, $chat_userid);
619        $rset = $DIC->database()->queryF($query, $types, $values);
620
621        if ($rset && ($row = $DIC->database()->fetchAssoc($rset)) && $row['cnt'] == 1) {
622            return true;
623        }
624
625        return false;
626    }
627
628    /**
629     * Returns array containing history data selected from historyTable by given
630     * ilDateTime, $restricted_session_userid and matching roomId.
631     * @param ilDateTime     $from
632     * @param ilDateTime     $to
633     * @param integer        $restricted_session_userid
634     * @param bool           $respect_target
635     * @return array
636     */
637    public function getHistory(ilDateTime $from = null, ilDateTime $to = null, $restricted_session_userid = null, $proom_id = 0, $respect_target = true)
638    {
639        global $DIC;
640
641        $join = '';
642
643        if ($proom_id) {
644            $join .=
645                'INNER JOIN ' . self::$privateSessionsTable . ' pSessionTable ' .
646                'ON pSessionTable.user_id = ' . $DIC->database()->quote($restricted_session_userid, 'integer') . ' ' .
647                'AND pSessionTable.proom_id = historyTable.sub_room ' .
648                'AND timestamp >= pSessionTable.connected ' .
649                'AND timestamp <= pSessionTable.disconnected ';
650        }
651
652        $query =
653            'SELECT historyTable.* ' .
654            'FROM ' . self::$historyTable . ' historyTable ' . $join . ' ' .
655            'WHERE historyTable.room_id = ' . $this->getRoomId();
656
657        if ($proom_id !== null) {
658            $query .= ' AND historyTable.sub_room = ' . $DIC->database()->quote($proom_id, 'integer');
659        }
660
661        $filter = array();
662
663        if ($from != null) {
664            $filter[] = 'timestamp >= ' . $DIC->database()->quote($from->getUnixTime(), 'integer');
665        }
666
667        if ($to != null) {
668            $filter[] = 'timestamp <= ' . $DIC->database()->quote($to->getUnixTime(), 'integer');
669        }
670
671        if ($filter) {
672            $query .= ' AND ' . join(' AND ', $filter);
673        }
674        $query .= ' ORDER BY timestamp ASC';
675
676        $rset = $DIC->database()->query($query);
677        $result = array();
678
679        while ($row = $DIC->database()->fetchAssoc($rset)) {
680            $message = json_decode($row['message']);
681            if ($message === null) {
682                $message = json_decode('{}');
683            }
684
685            $row['message'] = $message;
686            $row['message']->timestamp = $row['timestamp'];
687            if (
688                $respect_target &&
689                $row['message']->target !== null &&
690                !$row['message']->target->public &&
691                !in_array($DIC->user()->getId(), explode(',', $row['recipients']))
692            ) {
693                continue;
694            }
695
696            $result[] = $row;
697        }
698        return $result;
699    }
700
701    /**
702     * Returns roomID from $this->roomId
703     * @return integer
704     */
705    public function getRoomId()
706    {
707        return $this->roomId;
708    }
709
710    public function getPrivateRoomSessions(ilDateTime $from = null, ilDateTime $to = null, $user_id = 0, $room_id = 0)
711    {
712        global $DIC;
713
714        $query = 'SELECT proom_id, title FROM ' . self::$privateRoomsTable . ' WHERE proom_id IN (
715			SELECT proom_id FROM ' . self::$privateSessionsTable . ' WHERE connected >= %s AND disconnected <= %s AND user_id = %s
716
717		) AND parent_id = %s';
718
719        $rset = $DIC->database()->queryF($query, array('integer', 'integer', 'integer', 'integer'), array($from->getUnixTime(), $to->getUnixTime(), $user_id, $room_id));
720        $result = array();
721        while ($row = $DIC->database()->fetchAssoc($rset)) {
722            $result[] = $row;
723        }
724        return $result;
725    }
726
727    /**
728     * Saves information about file uploads in DB.
729     * @param integer        $user_id
730     * @param string         $filename
731     * @param string         $type
732     */
733    public function saveFileUploadToDb($user_id, $filename, $type)
734    {
735        global $DIC;
736
737        $upload_id = $DIC->database()->nextId(self::$uploadTable);
738
739        $DIC->database()->insert(
740            self::$uploadTable,
741            array(
742                'upload_id' => array('integer', $upload_id),
743                'room_id' => array('integer', $this->roomId),
744                'user_id' => array('integer', $user_id),
745                'filename' => array('text', $filename),
746                'filetype' => array('text', $type),
747                'timestamp' => array('integer', time())
748            )
749        );
750    }
751
752    /**
753     * Inserts user into banTable, using given $user_id
754     * @param integer        $user_id
755     * @param integer        $actor_id
756     * @param string         $comment
757     */
758    public function banUser($user_id, $actor_id, $comment = '')
759    {
760        global $DIC;
761
762        $DIC->database()->replace(
763            self::$banTable,
764            array(
765                'room_id' => array('integer', $this->roomId),
766                'user_id' => array('integer', $user_id)
767            ),
768            array(
769                'actor_id' => array('integer', $actor_id),
770                'timestamp' => array('integer', time()),
771                'remark' => array('text', $comment)
772            )
773        );
774    }
775
776    /**
777     * Deletes entry from banTable matching roomId and given $user_id and
778     * returns true if sucessful.
779     * @param mixed          $user_id
780     * @return boolean
781     */
782    public function unbanUser($user_id)
783    {
784        global $DIC;
785
786        if (!is_array($user_id)) {
787            $user_id = array($user_id);
788        }
789
790        $query = 'DELETE FROM ' . self::$banTable . ' WHERE room_id = %s AND ' . $DIC->database()->in('user_id', $user_id, false, 'integer');
791
792        $types = array('integer');
793        $values = array($this->getRoomId());
794
795        return $DIC->database()->manipulateF($query, $types, $values);
796    }
797
798    /**
799     * Returns true if there's an entry in banTable matching roomId and given
800     * $user_id
801     * @param integer        $user_id
802     * @return boolean
803     */
804    public function isUserBanned($user_id)
805    {
806        global $DIC;
807
808        $query = 'SELECT COUNT(user_id) cnt FROM ' . self::$banTable . ' WHERE user_id = %s AND room_id = %s';
809
810        $types = array('integer', 'integer');
811        $values = array($user_id, $this->getRoomId());
812
813        $rset = $DIC->database()->queryF($query, $types, $values);
814
815        if ($rset && ($row = $DIC->database()->fetchAssoc($rset)) && $row['cnt']) {
816            return true;
817        }
818
819        return false;
820    }
821
822    /**
823     * Returns an multidimensional array containing userdata from users
824     * having an entry in banTable with matching roomId.
825     * @return array
826     */
827    public function getBannedUsers()
828    {
829        global $DIC;
830
831        $query = 'SELECT chb.* FROM ' . self::$banTable . ' chb INNER JOIN usr_data ud ON chb.user_id = ud.usr_id WHERE chb.room_id = %s ';
832        $types = array('integer');
833        $values = array($this->getRoomId());
834        $rset = $DIC->database()->queryF($query, $types, $values);
835        $result = array();
836
837        if ($rset) {
838            while ($row = $DIC->database()->fetchAssoc($rset)) {
839                if ($row['user_id'] > 0) {
840                    $user = new ilObjUser($row['user_id']);
841                    $userdata = array(
842                        'user_id' => $user->getId(),
843                        'firstname' => $user->getFirstname(),
844                        'lastname' => $user->getLastname(),
845                        'login' => $user->getLogin(),
846                        'timestamp' => $row['timestamp'],
847                        'actor_id' => $row['actor_id'],
848                        'remark' => $row['remark']
849                    );
850
851                    $result[] = $userdata;
852                }
853            }
854        }
855
856        return $result;
857    }
858
859    /**
860     * Returns last session from user.
861     * Returns row from sessionTable where user_id matches userId from given
862     * $user object.
863     * @param ilChatroomUser $user
864     * @return array
865     */
866    public function getLastSession(ilChatroomUser $user)
867    {
868        global $DIC;
869
870        $query = 'SELECT * FROM ' . self::$sessionTable . ' WHERE user_id = ' .
871            $DIC->database()->quote($user->getUserId(), 'integer') .
872            ' ORDER BY connected DESC';
873
874        $DIC->database()->setLimit(1);
875        $rset = $DIC->database()->query($query);
876
877        if ($row = $DIC->database()->fetchAssoc($rset)) {
878            return $row;
879        }
880    }
881
882    /**
883     * Returns all session from user
884     * Returns all from sessionTable where user_id matches userId from given
885     * $user object.
886     * @param ilChatroomUser $user
887     * @return array
888     */
889    public function getSessions(ilChatroomUser $user)
890    {
891        global $DIC;
892
893        $query = 'SELECT * FROM ' . self::$sessionTable
894            . ' WHERE room_id = ' .
895            $DIC->database()->quote($this->getRoomId(), 'integer') .
896            ' ORDER BY connected DESC';
897
898        $rset = $DIC->database()->query($query);
899
900        $result = array();
901
902        while ($row = $DIC->database()->fetchAssoc($rset)) {
903            $result[] = $row;
904        }
905
906        return $result;
907    }
908
909    public function addPrivateRoom($title, ilChatroomUser $owner, $settings)
910    {
911        global $DIC;
912
913        $nextId = $DIC->database()->nextId(self::$privateRoomsTable);
914        $DIC->database()->insert(
915            self::$privateRoomsTable,
916            array(
917                'proom_id' => array('integer', $nextId),
918                'parent_id' => array('integer', $this->roomId),
919                'title' => array('text', $title),
920                'owner' => array('integer', $owner->getUserId()),
921                'closed' => array('integer', (isset($settings['closed']) ? $settings['closed'] : 0)),
922                'created' => array('integer', (isset($settings['created']) ? $settings['created'] : time())),
923                'is_public' => array('integer', $settings['public']),
924            )
925        );
926
927        return $nextId;
928    }
929
930    public function closePrivateRoom($id)
931    {
932        global $DIC;
933
934        $DIC->database()->manipulateF(
935            'UPDATE ' . self::$privateRoomsTable . ' SET closed = %s WHERE proom_id = %s',
936            array('integer', 'integer'),
937            array(time(), $id)
938        );
939    }
940
941    public function isOwnerOfPrivateRoom($user_id, $proom_id)
942    {
943        global $DIC;
944
945        $query = 'SELECT proom_id FROM ' . self::$privateRoomsTable . ' WHERE proom_id = %s AND owner = %s';
946        $types = array('integer', 'integer');
947        $values = array($proom_id, $user_id);
948
949        $rset = $DIC->database()->queryF($query, $types, $values);
950
951        if ($rset && $DIC->database()->fetchAssoc($rset)) {
952            return true;
953        }
954        return false;
955    }
956
957    /**
958     * @param        $gui
959     * @param mixed  $sender (can be an instance of ilChatroomUser or an user id of an ilObjUser instance
960     * @param int    $recipient_id
961     * @param int    $subScope
962     * @param string $invitationLink
963     * @throws InvalidArgumentException
964     */
965    public function sendInvitationNotification($gui, $sender, $recipient_id, $subScope = 0, $invitationLink = '')
966    {
967        global $DIC;
968
969        if ($gui && !$invitationLink) {
970            $invitationLink = $this->getChatURL($gui, $subScope);
971        }
972
973        if ($recipient_id > 0 && !in_array(ANONYMOUS_USER_ID, array($recipient_id))) {
974            if (is_numeric($sender) && $sender > 0) {
975                $sender_id = $sender;
976                /**
977                 * @var $usr ilObjUser
978                 */
979                $usr = ilObjectFactory::getInstanceByObjId($sender);
980                $public_name = $usr->getPublicName();
981            } elseif ($sender instanceof ilChatroomUser) {
982                if ($sender->getUserId() > 0) {
983                    $sender_id = $sender->getUserId();
984                } else {
985                    $sender_id = ANONYMOUS_USER_ID;
986                }
987                $public_name = $sender->getUsername();
988            } else {
989                throw new InvalidArgumentException('$sender must be an instance of ilChatroomUser or an id of an ilObjUser instance');
990            }
991
992            $userLang = ilLanguageFactory::_getLanguageOfUser($recipient_id);
993            $userLang->loadLanguageModule('mail');
994            require_once 'Services/Mail/classes/class.ilMail.php';
995            $bodyParams = array(
996                'link' => $invitationLink,
997                'inviter_name' => $public_name,
998                'room_name' => $this->getTitle(),
999                'salutation' => ilMail::getSalutation($recipient_id, $userLang)
1000            );
1001
1002            if ($subScope) {
1003                $bodyParams['room_name'] .= ' - ' . self::lookupPrivateRoomTitle($subScope);
1004            }
1005
1006            require_once 'Services/Notifications/classes/class.ilNotificationConfig.php';
1007            $notification = new ilNotificationConfig('chat_invitation');
1008            $notification->setTitleVar('chat_invitation', $bodyParams, 'chatroom');
1009            $notification->setShortDescriptionVar('chat_invitation_short', $bodyParams, 'chatroom');
1010            $notification->setLongDescriptionVar('chat_invitation_long', $bodyParams, 'chatroom');
1011            $notification->setAutoDisable(false);
1012            $notification->setLink($invitationLink);
1013            $notification->setIconPath('templates/default/images/icon_chtr.svg');
1014            $notification->setValidForSeconds(ilNotificationConfig::TTL_LONG);
1015            $notification->setVisibleForSeconds(ilNotificationConfig::DEFAULT_TTS);
1016
1017            $notification->setHandlerParam('mail.sender', $sender_id);
1018
1019            $notification->notifyByUsers(array($recipient_id));
1020        }
1021    }
1022
1023    /**
1024     * @param         $gui
1025     * @param integer $scope_id
1026     * @return string
1027     */
1028    public function getChatURL($gui, $scope_id = 0)
1029    {
1030        include_once 'Services/Link/classes/class.ilLink.php';
1031
1032        $url = '';
1033
1034        if (is_object($gui)) {
1035            if ($scope_id) {
1036                $url = ilLink::_getStaticLink($gui->object->getRefId(), $gui->object->getType(), true, '_' . $scope_id);
1037            } else {
1038                $url = ilLink::_getStaticLink($gui->object->getRefId(), $gui->object->getType());
1039            }
1040        }
1041
1042        return $url;
1043    }
1044
1045    public function getTitle()
1046    {
1047        if (!$this->object) {
1048            $this->object = ilObjectFactory::getInstanceByObjId($this->getSetting('object_id'));
1049        }
1050
1051        return $this->object->getTitle();
1052    }
1053
1054    public static function lookupPrivateRoomTitle($proom_id)
1055    {
1056        global $DIC;
1057
1058        $query = 'SELECT title FROM ' . self::$privateRoomsTable . ' WHERE proom_id = %s';
1059        $types = array('integer');
1060        $values = array($proom_id);
1061
1062        $rset = $DIC->database()->queryF($query, $types, $values);
1063
1064        if ($row = $DIC->database()->fetchAssoc($rset)) {
1065            return $row['title'];
1066        }
1067
1068        return 'unkown';
1069    }
1070
1071    public function inviteUserToPrivateRoomByLogin($login, $proom_id)
1072    {
1073        $user_id = ilObjUser::_lookupId($login);
1074        $this->inviteUserToPrivateRoom($user_id, $proom_id);
1075    }
1076
1077    /**
1078     * @param int $user_id
1079     * @param int $proom_id
1080     */
1081    public function inviteUserToPrivateRoom($user_id, $proom_id)
1082    {
1083        global $DIC;
1084
1085        $DIC->database()->replace(self::$privateRoomsAccessTable, array(
1086            'user_id' => array('integer', $user_id),
1087            'proom_id' => array('integer', $proom_id)
1088        ), array());
1089    }
1090
1091    public function getActivePrivateRooms($userid)
1092    {
1093        global $DIC;
1094
1095        $query = '
1096			SELECT roomtable.title, roomtable.proom_id, accesstable.user_id id, roomtable.owner rowner
1097			FROM ' . self::$privateRoomsTable . ' roomtable
1098			LEFT JOIN ' . self::$privateRoomsAccessTable . ' accesstable
1099			ON roomtable.proom_id = accesstable.proom_id
1100			AND accesstable.user_id = %s
1101			WHERE parent_id = %s
1102			AND (closed = 0 OR closed IS NULL)
1103			AND (accesstable.user_id IS NOT NULL OR roomtable.owner = %s)';
1104        $types = array('integer', 'integer', 'integer');
1105        $values = array($userid, $this->roomId, $userid);
1106        $rset = $DIC->database()->queryF($query, $types, $values);
1107        $rooms = array();
1108
1109        while ($row = $DIC->database()->fetchAssoc($rset)) {
1110            $row['active_users'] = $this->listUsersInPrivateRoom($row['id']);
1111            $row['owner'] = $row['rowner'];
1112            $rooms[$row['proom_id']] = $row;
1113        }
1114
1115        return $rooms;
1116    }
1117
1118    public function listUsersInPrivateRoom($private_room_id)
1119    {
1120        global $DIC;
1121
1122        $query = '
1123			SELECT chatroom_users.user_id FROM ' . self::$privateSessionsTable . '
1124			INNER JOIN chatroom_users ON chatroom_users.user_id = ' . self::$privateSessionsTable . '.user_id WHERE proom_id = %s AND disconnected = 0
1125		';
1126        $types = array('integer');
1127        $values = array($private_room_id);
1128        $rset = $DIC->database()->queryF($query, $types, $values);
1129
1130        $users = array();
1131
1132        while ($row = $DIC->database()->fetchAssoc($rset)) {
1133            $users[$row['user_id']] = $row['user_id'];
1134        }
1135
1136        return array_values($users);
1137    }
1138
1139    public function subscribeUserToPrivateRoom($room_id, $user_id)
1140    {
1141        global $DIC;
1142
1143        if (!$this->userIsInPrivateRoom($room_id, $user_id)) {
1144            $id = $DIC->database()->nextId(self::$privateSessionsTable);
1145            $DIC->database()->insert(
1146                self::$privateSessionsTable,
1147                array(
1148                    'psess_id' => array('integer', $id),
1149                    'proom_id' => array('integer', $room_id),
1150                    'user_id' => array('integer', $user_id),
1151                    'connected' => array('integer', time()),
1152                    'disconnected' => array('integer', 0),
1153                )
1154            );
1155        }
1156    }
1157
1158    public function userIsInPrivateRoom($room_id, $user_id)
1159    {
1160        global $DIC;
1161
1162        $query = 'SELECT proom_id id FROM ' . self::$privateSessionsTable . ' WHERE user_id = %s AND proom_id = %s AND disconnected = 0';
1163        $types = array('integer', 'integer');
1164        $values = array($user_id, $room_id);
1165        $rset = $DIC->database()->queryF($query, $types, $values);
1166        if ($DIC->database()->fetchAssoc($rset)) {
1167            return true;
1168        }
1169        return false;
1170    }
1171
1172    /**
1173     * @param integer        $room_id
1174     * @param integer        $user_id
1175     */
1176    public function unsubscribeUserFromPrivateRoom($room_id, $user_id)
1177    {
1178        global $DIC;
1179
1180        $DIC->database()->update(
1181            self::$privateSessionsTable,
1182            array(
1183                'disconnected' => array('integer', time())
1184            ),
1185            array(
1186                'proom_id' => array('integer', $room_id),
1187                'user_id' => array('integer', $user_id)
1188            )
1189        );
1190    }
1191
1192    public function countActiveUsers()
1193    {
1194        global $DIC;
1195
1196        $query = 'SELECT count(user_id) as cnt FROM ' . self::$userTable .
1197            ' WHERE room_id = %s';
1198
1199        $types = array('integer');
1200        $values = array($this->roomId);
1201        $rset = $DIC->database()->queryF($query, $types, $values);
1202
1203        if ($rset && ($row = $DIC->database()->fetchAssoc($rset)) && $row['cnt'] == 1) {
1204            return $row['cnt'];
1205        }
1206
1207        return 0;
1208    }
1209
1210    public function getPrivateRooms()
1211    {
1212        global $DIC;
1213
1214        $query = 'SELECT * FROM ' . self::$privateRoomsTable . ' WHERE parent_id = %s';
1215        $rset = $DIC->database()->queryF($query, array('integer'), array($this->roomId));
1216
1217        $rooms = array();
1218
1219        while ($row = $DIC->database()->fetchAssoc($rset)) {
1220            $rooms[] = $row;
1221        }
1222
1223        return $rooms;
1224    }
1225
1226    /**
1227     * @param int $subRoomId
1228     * @return int[]
1229     */
1230    public function getPrivilegedUsersForPrivateRoom($subRoomId)
1231    {
1232        global $DIC;
1233
1234        $query = 'SELECT user_id FROM ' . self::$privateRoomsAccessTable . ' WHERE proom_id = %s';
1235        $rset = $DIC->database()->queryF($query, array('integer'), array($subRoomId));
1236
1237        $userIds = array();
1238
1239        while ($row = $DIC->database()->fetchAssoc($rset)) {
1240            $userIds[] = $row['user_id'];
1241        }
1242
1243        return $userIds;
1244    }
1245
1246    public function getUniquePrivateRoomTitle($title)
1247    {
1248        global $DIC;
1249
1250        $query = 'SELECT title FROM ' . self::$privateRoomsTable . ' WHERE parent_id = %s and closed = 0';
1251        $rset = $DIC->database()->queryF($query, array('integer'), array($this->roomId));
1252
1253        $titles = array();
1254
1255        while ($row = $DIC->database()->fetchAssoc($rset)) {
1256            $titles[] = $row['title'];
1257        }
1258
1259        $suffix = '';
1260        $i = 0;
1261        do {
1262            if (!in_array($title . $suffix, $titles)) {
1263                $title .= $suffix;
1264                break;
1265            }
1266
1267            ++$i;
1268
1269            $suffix = ' (' . $i . ')';
1270        } while (true);
1271
1272        return $title;
1273    }
1274
1275    /**
1276     * Fetches and returns a Array<Integer, String> of all accessible repository object chats in the main tree
1277     * @param integer $user_id
1278     * @return string[]
1279     */
1280    public function getAccessibleRoomIdByTitleMap($user_id)
1281    {
1282        global $DIC;
1283
1284        $query = "
1285			SELECT room_id, od.title, objr.ref_id
1286			FROM object_data od
1287			INNER JOIN  " . self::$settingsTable . "
1288				ON object_id = od.obj_id
1289			INNER JOIN object_reference objr
1290				ON objr.obj_id = od.obj_id
1291				AND objr.deleted IS NULL
1292			INNER JOIN tree
1293				ON tree.child = objr.ref_id
1294				AND tree.tree = %s
1295			WHERE od.type = %s
1296       ";
1297
1298        $types = array('integer', 'text');
1299        $values = array(1, 'chtr');
1300
1301        $res = $DIC->database()->queryF($query, $types, $values);
1302
1303        $rooms = [];
1304
1305        while ($row = $DIC->database()->fetchAssoc($res)) {
1306            if (ilChatroom::checkPermissionsOfUser($user_id, 'read', $row['ref_id'])) {
1307                $rooms[$row['room_id']] = $row['title'];
1308            }
1309        }
1310
1311        return $rooms;
1312    }
1313
1314    public function getPrivateSubRooms($parent_room, $user_id)
1315    {
1316        global $DIC;
1317
1318        $query = "
1319       SELECT      proom_id, parent_id
1320       FROM        " . self::$privateRoomsTable . "
1321       WHERE       parent_id = %s
1322       AND     owner = %s
1323       AND     closed = 0
1324       ";
1325
1326        $types = array('integer', 'integer');
1327        $values = array($parent_room, $user_id);
1328
1329        $res = $DIC->database()->queryF($query, $types, $values);
1330
1331        $priv_rooms = array();
1332
1333        while ($row = $DIC->database()->fetchAssoc($res)) {
1334            $proom_id = $row['proom_id'];
1335            $priv_rooms[$proom_id] = $row['parent_id'];
1336        }
1337
1338        return $priv_rooms;
1339    }
1340
1341    /**
1342     * Returns ref_id of given room_id
1343     * @param integer        $room_id
1344     * @return integer
1345     */
1346    public function getRefIdByRoomId($room_id)
1347    {
1348        global $DIC;
1349
1350        $query = "
1351       SELECT      objr.ref_id
1352       FROM        object_reference    objr
1353
1354       INNER JOIN  chatroom_settings   cs
1355           ON      cs.object_id = objr.obj_id
1356
1357       INNER JOIN  object_data     od
1358           ON      od.obj_id = cs.object_id
1359
1360       WHERE       cs.room_id = %s
1361       ";
1362
1363        $types = array('integer');
1364        $values = array($room_id);
1365
1366        $res = $DIC->database()->queryF($query, $types, $values);
1367
1368        $row = $DIC->database()->fetchAssoc($res);
1369
1370        return $row['ref_id'];
1371    }
1372
1373    public function getLastMessagesForChatViewer($number, $chatuser = null)
1374    {
1375        return $this->getLastMessages($number, $chatuser);
1376    }
1377
1378    public function getLastMessages($number, $chatuser = null)
1379    {
1380        global $DIC;
1381
1382        // There is currently no way to check if a message is private or not
1383        // by sql. So we fetch twice as much as we need and hope that there
1384        // are not more than $number private messages.
1385        $DIC->database()->setLimit($number);
1386        $rset = $DIC->database()->query(
1387            'SELECT *
1388			FROM ' . self::$historyTable . '
1389			WHERE room_id = ' . $DIC->database()->quote($this->roomId, 'integer') . '
1390			AND sub_room = 0
1391			AND (
1392				(' . $DIC->database()->like('message', 'text', '%"type":"message"%') . ' AND NOT ' . $DIC->database()->like('message', 'text', '%"public":0%') . ')
1393		  		OR ' . $DIC->database()->like('message', 'text', '%"target":{%"id":"' . $chatuser->getUserId() . '"%') . '
1394				OR ' . $DIC->database()->like('message', 'text', '%"from":{"id":' . $chatuser->getUserId() . '%') . '
1395			)
1396			ORDER BY timestamp DESC'
1397        );
1398
1399        $result_count = 0;
1400        $results = array();
1401        while (($row = $DIC->database()->fetchAssoc($rset)) && $result_count < $number) {
1402            $tmp = json_decode($row['message']);
1403            if ($chatuser !== null && $tmp->target != null && $tmp->target->public == 0) {
1404                if ($chatuser->getUserId() == $tmp->target->id || $chatuser->getUserId() == $tmp->from->id) {
1405                    $results[] = $tmp;
1406                    ++$result_count;
1407                }
1408            } else {
1409                $results[] = $tmp;
1410                ++$result_count;
1411            }
1412        }
1413
1414        $rset = $DIC->database()->query(
1415            'SELECT *
1416			FROM ' . self::$historyTable . '
1417			WHERE room_id = ' . $DIC->database()->quote($this->roomId, 'integer') . '
1418			AND sub_room = 0
1419			AND ' . $DIC->database()->like('message', 'text', '%"type":"notice"%') . '
1420			AND timestamp <= ' . $DIC->database()->quote($results[0]->timestamp, 'integer') . ' AND timestamp >= ' . $DIC->database()->quote($results[$result_count - 1]->timestamp, 'integer') . '
1421
1422			ORDER BY timestamp DESC'
1423        );
1424
1425        while (($row = $DIC->database()->fetchAssoc($rset))) {
1426            $tmp = json_decode($row['message']);
1427            $results[] = $tmp;
1428        }
1429
1430        \usort($results, function ($a, $b) {
1431            $a_timestamp = strlen($a->timestamp) == 13 ? substr($a->timestamp, 0, -3) : $a->timestamp;
1432            $b_timestamp = strlen($b->timestamp) == 13 ? substr($b->timestamp, 0, -3) : $b->timestamp;
1433
1434            return $b_timestamp - $a_timestamp;
1435        });
1436
1437        return $results;
1438    }
1439
1440    public function clearMessages($sub_room)
1441    {
1442        global $DIC;
1443
1444        $DIC->database()->queryF(
1445            'DELETE FROM ' . self::$historyTable . ' WHERE room_id = %s AND sub_room = %s',
1446            array('integer', 'integer'),
1447            array($this->roomId, (int) $sub_room)
1448        );
1449
1450        if ($sub_room) {
1451            $DIC->database()->queryF(
1452                'DELETE FROM ' . self::$privateSessionsTable . ' WHERE proom_id = %s AND disconnected < %s',
1453                array('integer', 'integer'),
1454                array($sub_room, time())
1455            );
1456        } else {
1457            $DIC->database()->queryF(
1458                'DELETE FROM ' . self::$sessionTable . ' WHERE room_id = %s AND disconnected < %s',
1459                array('integer', 'integer'),
1460                array($this->roomId, time())
1461            );
1462        }
1463    }
1464}
1465