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