1<?php
2/* Copyright (c) 1998-2012 ILIAS open source, Extended GPL, see docs/LICENSE */
3
4/**
5 * Class ilChatroomInstaller
6 * @author  Jan Posselt <jposselt@databay.de>
7 * @version $Id$
8 * @ingroup ModulesChatroom
9 */
10class ilChatroomInstaller
11{
12    /**
13     * Creates tables needed for chat and calls registerObject and
14     * registerAdminObject methods.
15     * @global ilDBInterface $ilDB
16     */
17    public static function install()
18    {
19        /**
20         * @var $ilDB ilDBInterface
21         */
22        global $DIC;
23
24        $ilDB = $DIC->database();
25
26        if (!$ilDB->tableExists('chatroom_settings')) {
27            $fields = array(
28                'room_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
29                'object_id' => array('type' => 'integer', 'length' => 4, 'notnull' => false, 'default' => 0),
30                'room_type' => array('type' => 'text', 'length' => 20, 'notnull' => true),
31                'allow_anonymous' => array('type' => 'integer', 'length' => 1, 'notnull' => false, 'default' => 0),
32                'allow_custom_usernames' => array('type' => 'integer', 'length' => 1, 'notnull' => false, 'default' => 0),
33                'enable_history' => array('type' => 'integer', 'length' => 1, 'notnull' => false, 'default' => 0),
34                'restrict_history' => array('type' => 'integer', 'length' => 1, 'notnull' => false, 'default' => 0),
35                'autogen_usernames' => array('type' => 'text', 'length' => 50, 'notnull' => false, 'default' => 'Anonymous #'),
36                'allow_private_rooms' => array('type' => 'integer', 'length' => 1, 'notnull' => false, 'default' => 0),
37            );
38
39            $ilDB->createTable('chatroom_settings', $fields);
40            $ilDB->addPrimaryKey('chatroom_settings', array('room_id'));
41            $ilDB->createSequence('chatroom_settings');
42        }
43
44        if (!$ilDB->tableExists('chatroom_users')) {
45            $fields = array(
46                'room_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
47                'user_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
48                'userdata' => array('type' => 'text', 'length' => 4000, 'notnull' => true),
49                'connected' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
50            );
51            $ilDB->createTable('chatroom_users', $fields);
52            $ilDB->addPrimaryKey('chatroom_users', array('room_id', 'user_id'));
53        }
54
55        if (!$ilDB->tableExists('chatroom_sessions')) {
56            $fields = array(
57                'room_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
58                'user_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
59                'userdata' => array('type' => 'text', 'length' => 4000, 'notnull' => true),
60                'connected' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
61                'disconnected' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
62            );
63            $ilDB->createTable('chatroom_sessions', $fields);
64        }
65
66        if (!$ilDB->tableExists('chatroom_history')) {
67            $fields = array(
68                'room_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
69                'message' => array('type' => 'text', 'length' => 4000, 'notnull' => true),
70                'timestamp' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
71            );
72            $ilDB->createTable('chatroom_history', $fields);
73        }
74
75        if (!$ilDB->tableExists('chatroom_bans')) {
76            $fields = array(
77                'room_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
78                'user_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
79                'timestamp' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
80                'remark' => array('type' => 'text', 'length' => 1000, 'notnull' => false),
81            );
82            $ilDB->createTable('chatroom_bans', $fields);
83        }
84
85        if (!$ilDB->tableExists('chatroom_admconfig')) {
86            $fields = array(
87                'instance_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
88                'server_settings' => array('type' => 'text', 'length' => 2000, 'notnull' => true),
89                'default_config' => array('type' => 'integer', 'length' => 1, 'notnull' => true, 'default' => 0),
90            );
91            $ilDB->createTable('chatroom_admconfig', $fields);
92            $ilDB->addPrimaryKey('chatroom_admconfig', array('instance_id'));
93            $ilDB->createSequence('chatroom_admconfig');
94        }
95
96        if (!$ilDB->tableExists('chatroom_prooms')) {
97            $fields = array(
98                'proom_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
99                'parent_id' => array('type' => 'text', 'length' => 2000, 'notnull' => true),
100                'title' => array('type' => 'text', 'length' => 200, 'notnull' => true, 'default' => 0),
101                'owner' => array('type' => 'integer', 'length' => 4, 'notnull' => true, 'default' => 0),
102                'created' => array('type' => 'integer', 'length' => 4, 'notnull' => true, 'default' => 0),
103                'closed' => array('type' => 'integer', 'length' => 4, 'notnull' => false, 'default' => 0),
104            );
105            $ilDB->createTable('chatroom_prooms', $fields);
106            $ilDB->addPrimaryKey('chatroom_prooms', array('proom_id'));
107            $ilDB->createSequence('chatroom_prooms');
108        }
109
110        if (!$ilDB->tableExists('chatroom_psessions')) {
111            $fields = array(
112                'proom_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
113                'user_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
114                'connected' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
115                'disconnected' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
116            );
117            $ilDB->createTable('chatroom_psessions', $fields);
118        }
119
120        if (!$ilDB->tableExists('chatroom_uploads')) {
121            $fields = array(
122                'upload_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
123                'room_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
124                'user_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
125                'filename' => array('type' => 'text', 'length' => 200, 'notnull' => true),
126                'filetype' => array('type' => 'text', 'length' => 200, 'notnull' => true),
127                'timestamp' => array('type' => 'integer', 'length' => 4, 'notnull' => true)
128            );
129            $ilDB->createTable('chatroom_uploads', $fields);
130            $ilDB->addPrimaryKey('chatroom_uploads', array('upload_id'));
131            $ilDB->createSequence('chatroom_uploads');
132        }
133
134        if (!$ilDB->tableColumnExists('chatroom_prooms', 'is_public')) {
135            $ilDB->addTableColumn('chatroom_prooms', 'is_public', array('type' => 'integer', 'default' => 1, 'length' => 1));
136        }
137
138        if (!$ilDB->tableExists('chatroom_psessions')) {
139            $fields = array(
140                'proom_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
141                'user_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
142                'connected' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
143                'disconnected' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
144            );
145            $ilDB->createTable('chatroom_psessions', $fields);
146        }
147
148        if (!$ilDB->tableExists('chatroom_proomaccess')) {
149            $fields = array(
150                'proom_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
151                'user_id' => array('type' => 'integer', 'length' => 4, 'notnull' => true),
152            );
153            $ilDB->createTable('chatroom_proomaccess', $fields);
154        }
155
156        if (!$ilDB->tableColumnExists('chatroom_admconfig', 'client_settings')) {
157            $ilDB->addTableColumn(
158                "chatroom_admconfig",
159                "client_settings",
160                array(
161                    "type" => "text",
162                    "length" => 1000,
163                    "notnull" => true
164                )
165            );
166        }
167
168        if (!$ilDB->tableExists('chatroom_smilies')) {
169            $fields = array(
170                'smiley_id' => array(
171                    'type' => 'integer',
172                    'length' => 4,
173                ),
174                'smiley_keywords' => array(
175                    'type' => 'text',
176                    'length' => 100,
177                ),
178                'smiley_path' => array(
179                    'type' => 'text',
180                    'length' => 200,
181                )
182            );
183
184            $ilDB->createTable('chatroom_smilies', $fields);
185            $ilDB->addPrimaryKey('chatroom_smilies', array('smiley_id'));
186            $ilDB->createSequence('chatroom_smilies');
187        }
188
189        self::registerObject();
190        self::registerAdminObject();
191        self::removeOldChatEntries();
192        self::convertChatObjects();
193
194        $notificationSettings = new ilSetting('notifications');
195        $notificationSettings->set('enable_osd', true);
196    }
197
198    /**
199     * Registers chat object by inserting it into object_data.
200     * @global ilDBInterface $ilDB
201     */
202    public static function registerObject()
203    {
204        global $DIC;
205
206        /**@var $ilDB ilDBInterface */
207        $ilDB = $DIC->database();
208
209        $typ_id = null;
210
211        $query = 'SELECT obj_id FROM object_data ' .
212            'WHERE type = ' . $ilDB->quote('typ', 'text') . ' ' .
213            'AND title = ' . $ilDB->quote('chtr', 'text');
214        if (!($object_definition_row = $ilDB->fetchAssoc($ilDB->query($query)))) {
215            $typ_id = $ilDB->nextId('object_data');
216            $ilDB->insert(
217                'object_data',
218                array(
219                    'obj_id' => array('integer', $typ_id),
220                    'type' => array('text', 'typ'),
221                    'title' => array('text', 'chtr'),
222                    'description' => array('text', 'Chatroom Object'),
223                    'owner' => array('integer', -1),
224                    'create_date' => array('timestamp', date('Y-m-d H:i:s')),
225                    'last_update' => array('timestamp', date('Y-m-d H:i:s'))
226                )
227            );
228
229            // REGISTER RBAC OPERATIONS FOR OBJECT TYPE
230            // 1: edit_permissions, 2: visible, 3: read, 4:write
231            foreach (array(1, 2, 3, 4) as $ops_id) {
232                $query = "INSERT INTO rbac_ta (typ_id, ops_id) VALUES ( " .
233                    $ilDB->quote($typ_id, 'integer') . "," . $ilDB->quote($ops_id, 'integer') .
234                    ")";
235                $ilDB->manipulate($query);
236            }
237        }
238
239        if ($moderatePermissionId = self::getModeratorPermissionId()) {
240            if (!$typ_id) {
241                $typ_id = $object_definition_row['obj_id'];
242            }
243
244            if ($typ_id) {
245                $ilDB->manipulateF(
246                    'DELETE FROM rbac_ta WHERE typ_id = %s AND ops_id = %s',
247                    array('integer', 'integer'),
248                    array($typ_id, $moderatePermissionId)
249                );
250
251                $ilDB->insert(
252                    'rbac_ta',
253                    array(
254                        'typ_id' => array('integer', $typ_id),
255                        'ops_id' => array('integer', $moderatePermissionId),
256                    )
257                );
258            }
259        }
260    }
261
262    private static function getModeratorPermissionId()
263    {
264        global $DIC;
265
266        /**@var $ilDB ilDBInterface */
267        $ilDB = $DIC->database();
268
269        $rset = $ilDB->queryF(
270            'SELECT ops_id FROM rbac_operations WHERE operation = %s',
271            array('text'),
272            array('moderate')
273        );
274        if ($row = $ilDB->fetchAssoc($rset)) {
275            return $row['ops_id'];
276        }
277        return 0;
278    }
279
280    /**
281     * Registgers admin chat object by inserting it into object_data.
282     * @global ilDBInterface $ilDB
283     */
284    public static function registerAdminObject()
285    {
286        global $DIC;
287
288        /**@var $ilDB ilDBInterface */
289        $ilDB = $DIC->database();
290
291        $query = 'SELECT * FROM object_data WHERE type = ' . $ilDB->quote('chta', 'text');
292        if (!$ilDB->fetchAssoc($ilDB->query($query))) {
293            $obj_id = $ilDB->nextId('object_data');
294            $ilDB->insert(
295                'object_data',
296                array(
297                    'obj_id' => array('integer', $obj_id),
298                    'type' => array('text', 'chta'),
299                    'title' => array('text', 'Chatroom Admin'),
300                    'description' => array('text', 'Chatroom General Settings'),
301                    'owner' => array('integer', -1),
302                    'create_date' => array('timestamp', date('Y-m-d H:i:s')),
303                    'last_update' => array('timestamp', date('Y-m-d H:i:s'))
304                )
305            );
306
307            $ref_id = $ilDB->nextId('object_reference');
308            $query = "INSERT INTO object_reference (ref_id, obj_id) VALUES(" . $ilDB->quote($ref_id, 'integer') . ", " . $ilDB->quote($obj_id, 'integer') . ")";
309            $ilDB->manipulate($query);
310
311            $tree = new ilTree(ROOT_FOLDER_ID);
312            $tree->insertNode($ref_id, SYSTEM_FOLDER_ID);
313        }
314    }
315
316    public static function removeOldChatEntries()
317    {
318        global $DIC;
319
320        /**@var $ilDB ilDBInterface */
321        $ilDB = $DIC->database();
322
323        $res = $ilDB->queryF(
324            'SELECT object_data.obj_id, ref_id, lft, rgt
325			FROM object_data
326            INNER JOIN object_reference ON object_reference.obj_id = object_data.obj_id
327			INNER JOIN tree ON child = ref_id
328            WHERE type = %s',
329            array('text'),
330            array('chac')
331        );
332
333        $data = $ilDB->fetchAssoc($res);
334        if ($data) {
335            $res = $ilDB->queryF('SELECT * FROM tree
336								  INNER JOIN object_reference ON ref_id = child
337								  INNER JOIN object_data ON object_data.obj_id = object_reference.obj_id
338								  WHERE lft BETWEEN %s AND %s', array('integer', 'integer'), array($data['lft'], $data['rgt']));
339            while ($row = $ilDB->fetchAssoc($res)) {
340                $ilDB->manipulate(
341                    'DELETE
342					FROM object_data
343					WHERE obj_id = ' . $ilDB->quote($row['obj_id'], 'integer')
344                );
345
346                $ilDB->manipulate(
347                    'DELETE
348					FROM object_reference
349					WHERE ref_id = ' . $ilDB->quote($row['ref_id'], 'integer')
350                );
351
352                $ilDB->manipulate(
353                    'DELETE
354					FROM tree
355					WHERE child = ' . $ilDB->quote($row['ref_id'], 'integer')
356                );
357            }
358        }
359
360        $ilDB->manipulateF('DELETE FROM object_data WHERE type = %s AND title = %s', array('text', 'text'), array('typ', 'chat'));
361        $ilDB->manipulateF('DELETE FROM object_data WHERE type = %s AND title = %s', array('text', 'text'), array('typ', 'chac'));
362    }
363
364    /**
365     * Converts old 'chat' objects to 'chtr' objects.
366     */
367    public static function convertChatObjects()
368    {
369        global $DIC;
370
371        /**@var $ilDB ilDBInterface */
372        $ilDB = $DIC->database();
373
374        $res = $ilDB->queryF(
375            "SELECT		obj_id
376			FROM		object_data
377			WHERE		type = %s",
378            array('text'),
379            array('chat')
380        );
381
382        $obj_ids = array();
383
384        while ($row = $ilDB->fetchAssoc($res)) {
385            $obj_ids[] = $row['obj_id'];
386        }
387
388        $ilDB->manipulateF(
389            "UPDATE		object_data
390			SET		type = %s
391			WHERE		type = %s",
392            array('text', 'text'),
393            array('chtr', 'chat')
394        );
395
396        self::setChatroomSettings($obj_ids);
397    }
398
399    /**
400     * Sets autogen_usernames default option for chatrooms
401     * @param array $obj_ids
402     */
403    public static function setChatroomSettings($obj_ids)
404    {
405        if (is_array($obj_ids)) {
406            foreach ($obj_ids as $obj_id) {
407                $room = new ilChatroom();
408                $room->saveSettings(array(
409                    'object_id' => $obj_id,
410                    'autogen_usernames' => 'Autogen #',
411                    'room_type' => 'repository'
412                ));
413            }
414        }
415    }
416
417    public static function createDefaultPublicRoom($force = false)
418    {
419        global $DIC;
420
421        /**@var $ilDB ilDBInterface */
422        $ilDB = $DIC->database();
423
424        if ($force) {
425            $query = 'DELETE FROM chatroom_settings WHERE room_type = ' . $ilDB->quote('default', 'text');
426            $ilDB->manipulate($query);
427            $create = true;
428        } else {
429            $query = 'SELECT * FROM chatroom_settings WHERE room_type = ' . $ilDB->quote('default', 'text');
430            $rset = $ilDB->query($query);
431            $create = !$ilDB->fetchAssoc($rset);
432        }
433        if ($create) {
434            $query = "
435				SELECT object_data.obj_id, object_reference.ref_id
436				FROM object_data
437				INNER JOIN object_reference ON object_reference.obj_id = object_data.obj_id
438				WHERE type = " . $ilDB->quote('chta', 'text');
439            $rset = $ilDB->query($query);
440            $row = $ilDB->fetchAssoc($rset);
441            $chatfolder_ref_id = $row['ref_id'];
442
443            require_once 'Modules/Chatroom/classes/class.ilObjChatroom.php';
444            $newObj = new ilObjChatroom();
445
446            $newObj->setType('chtr');
447            $newObj->setTitle('Public Chat');
448            $newObj->setDescription('');
449            $newObj->create(); // true for upload
450            $newObj->createReference();
451            $newObj->putInTree($chatfolder_ref_id);
452            $newObj->setPermissions($chatfolder_ref_id);
453
454            $obj_id = $newObj->getId();
455            $ref_id = $newObj->getRefId();
456
457            $id = $ilDB->nextId('chatroom_settings');
458            $ilDB->insert(
459                'chatroom_settings',
460                array(
461                    'room_id' => array('integer', $id),
462                    'object_id' => array('integer', $obj_id),
463                    'room_type' => array('text', 'default'),
464                    'allow_anonymous' => array('integer', 0),
465                    'allow_custom_usernames' => array('integer', 0),
466                    'enable_history' => array('integer', 0),
467                    'restrict_history' => array('integer', 0),
468                    'autogen_usernames' => array('text', 'Anonymous #'),
469                    'allow_private_rooms' => array('integer', 1),
470                )
471            );
472
473            $settings = new ilSetting('chatroom');
474            $settings->set('public_room_ref', $ref_id);
475        }
476    }
477
478    public static function createMissinRoomSettingsForConvertedObjects()
479    {
480        global $DIC;
481
482        /**@var $ilDB ilDBInterface */
483        $ilDB = $DIC->database();
484
485        $res = $ilDB->queryF(
486            "SELECT obj_id FROM object_data
487				LEFT JOIN chatroom_settings ON object_id = obj_id
488			WHERE type = %s
489				AND room_id IS NULL",
490            array('text'),
491            array('chtr')
492        );
493
494        $roomsToFix = array();
495        while ($row = $ilDB->fetchAssoc($res)) {
496            $roomsToFix[] = $row['obj_id'];
497        }
498
499        self::setChatroomSettings($roomsToFix);
500    }
501
502    /**
503     * @param int $ref_id
504     */
505    public static function ensureCorrectPublicChatroomTreeLocation($ref_id)
506    {
507        global $DIC;
508        /** @var $tree      ilTree */
509        $tree = $DIC->repositoryTree();
510        /** @var ilDBInterface $ilDB */
511        $ilDB = $DIC->database();
512        /** @var ilRbacAdmin $rbacadmin */
513        $rbacadmin = $DIC->rbac()->admin();
514
515        $ilDB->setLimit(1);
516        $query = "
517			SELECT object_data.obj_id, object_reference.ref_id
518			FROM object_data
519			INNER JOIN object_reference ON object_reference.obj_id = object_data.obj_id
520			WHERE type = " . $ilDB->quote('chta', 'text');
521        $rset = $ilDB->query($query);
522        $row = $ilDB->fetchAssoc($rset);
523        $chatfolder_ref_id = $row['ref_id'];
524        $pid = $tree->getParentId($ref_id);
525
526        if (
527            $chatfolder_ref_id &&
528            $pid != $chatfolder_ref_id &&
529            !$tree->isDeleted($chatfolder_ref_id)
530        ) {
531            $tree->moveTree($ref_id, $chatfolder_ref_id);
532            $rbacadmin->adjustMovedObjectPermissions($ref_id, $pid);
533            include_once('./Services/Conditions/classes/class.ilConditionHandler.php');
534            ilConditionHandler::_adjustMovedObjectConditions($ref_id);
535        }
536    }
537}
538