ILIAS  release_4-3 Revision
 All Data Structures Namespaces Files Functions Variables Groups Pages
class.ilChatroom.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3 
4 
13 class ilChatroom
14 {
15 
16  private $settings = array();
17  private static $settingsTable = 'chatroom_settings';
18  private static $historyTable = 'chatroom_history';
19  private static $userTable = 'chatroom_users';
20  private static $sessionTable = 'chatroom_sessions';
21  private static $banTable = 'chatroom_bans';
22  private static $privateRoomsTable = 'chatroom_prooms';
23  private static $privateSessionsTable = 'chatroom_psessions';
24  private static $uploadTable = 'chatroom_uploads';
25  private static $privateRoomsAccessTable = 'chatroom_proomaccess';
26 
34  private $availableSettings = array(
35  'object_id' => 'integer',
36  'allow_anonymous' => 'boolean',
37  'allow_custom_usernames' => 'boolean',
38  'enable_history' => 'boolean',
39  'restrict_history' => 'boolean',
40  'autogen_usernames' => 'string',
41  'room_type' => 'string',
42  'allow_private_rooms' => 'integer',
43  'display_past_msgs' => 'integer',
44  'private_rooms_enabled' => 'boolean'
45  );
46  private $roomId;
47 
48  private $object;
49 
50  public function getTitle()
51  {
52  if( !$this->object )
53  {
54  $this->object = ilObjectFactory::getInstanceByObjId($this->getSetting('object_id'));
55  }
56 
57  return $this->object->getTitle();
58  }
59 
67  public static function checkUserPermissions($permissions, $ref_id, $send_info = true)
68  {
69  global $rbacsystem, $lng;
70 
71  if( !is_array($permissions) )
72  {
73  $permissions = array( $permissions );
74  }
75 
76  foreach( $permissions as $permission )
77  {
78  if( !$rbacsystem->checkAccess( $permission, $ref_id ) )
79  {
80  if ($send_info) {
81  ilUtil::sendFailure( $lng->txt("permission_denied"), true );
82  }
83  return false;
84  }
85  }
86 
87  return true;
88  }
89 
101  public static function checkPermissionsOfUser($usr_id, $permissions, $ref_id)
102  {
103  global $rbacsystem, $lng;
104 
105  if( !is_array($permissions) )
106  {
107  $permissions = array( $permissions );
108  }
109 
110  foreach( $permissions as $permission )
111  {
112  if( !$rbacsystem->checkAccessOfUser($usr_id, $permission, $ref_id ) )
113  {
114  return false;
115  }
116  }
117 
118  return true;
119  }
120 
121  public function getDescription()
122  {
123  if (!$this->object)
124  {
125  $this->object = ilObjectFactory::getInstanceByObjId($this->getSetting('object_id'));
126  }
127 
128  return $this->object->getDescription();
129  }
130 
137  public function getSetting($name)
138  {
139  return $this->settings[$name];
140  }
141 
148  public function setSetting($name, $value)
149  {
150  $this->settings[$name] = $value;
151  }
152 
156  public function save()
157  {
158  $this->saveSettings( $this->settings );
159  }
160 
171  public function addHistoryEntry($message, $recipient = null, $publicMessage = true)
172  {
173  global $ilDB;
174 
175  $subRoom = 0;
176  if (is_array($message)) {
177  $subRoom = (int) $message['sub'];
178  }
179  else if (is_object($message)) {
180  $subRoom = (int) $message->sub;
181  }
182 
183  $ilDB->insert(
184  self::$historyTable,
185  array(
186  'room_id' => array('integer', $this->roomId),
187  'sub_room' => array('integer', $subRoom),
188  'message' => array('text', json_encode($message)),
189  'timestamp' => array('integer', time()),
190  )
191  );
192  }
193 
206  public function connectUser(ilChatroomUser $user)
207  {
211  global $ilDB;
212 
213  $userdata = array(
214  'login' => $user->getUsername(),
215  'id' => $user->getUserId()
216  );
217 
218  $query = 'SELECT user_id FROM ' . self::$userTable . ' WHERE room_id = %s AND user_id = %s';
219  $types = array('integer', 'integer');
220  $values = array($this->roomId, $user->getUserId());
221 
222  if(!$ilDB->fetchAssoc($ilDB->queryF($query, $types, $values)))
223  {
224  $ilDB->insert(
225  self::$userTable,
226  array(
227  'room_id' => array('integer', $this->roomId),
228  'user_id' => array('integer', $user->getUserId()),
229  'userdata' => array('text', json_encode($userdata)),
230  'connected' => array('integer', time()),
231  )
232  );
233 
234  return true;
235  }
236 
237  return false;
238  }
239 
249  public function getConnectedUsers()
250  {
251  global $ilDB;
252 
253  $query = 'SELECT userdata FROM ' . self::$userTable . ' WHERE room_id = %s';
254  $types = array('integer');
255  $values = array($this->roomId);
256  $rset = $ilDB->queryF( $query, $types, $values );
257  $users = array();
258 
259  while( $row = $ilDB->fetchAssoc( $rset ) )
260  {
261  $users[] = json_decode( $row['userdata'] );
262  }
263 
264  return $users;
265  }
266 
273  public function disconnectUser($user_id)
274  {
275  $this->disconnectUsers( array($user_id) );
276  }
277 
287  public function disconnectUsers(array $userIds)
288  {
289  global $ilDB;
290 
291  $query = 'SELECT * FROM ' . self::$userTable . ' WHERE room_id = %s AND ' .
292  $ilDB->in( 'user_id', $userIds, false, 'integer' );
293 
294  $types = array('integer');
295  $values = array($this->roomId);
296  $rset = $ilDB->queryF( $query, $types, $values );
297 
298  if( $row = $ilDB->fetchAssoc( $rset ) )
299  {
300  $query = 'SELECT proom_id FROM ' . self::$privateRoomsTable . ' WHERE parent_id = %s';
301  $rset_prooms = $ilDB->queryF($query, array('integer'), array($this->roomId));
302 
303  $prooms = array();
304 
305  while($row_prooms = $ilDB->fetchAssoc($rset_prooms)) {
306  $prooms[] = $row_prooms['proom_id'];
307  }
308 
309  if (true || $this->getSetting( 'enable_history' )) {
310  $query = 'UPDATE ' . self::$privateSessionsTable . ' SET disconnected = %s WHERE ' . $ilDB->in('user_id', $userIds, false, 'integer') . ' AND ' . $ilDB->in('proom_id', $prooms, false, 'integer');
311  $ilDB->manipulateF($query, array('integer'), array(time()));
312  }
313  else {
314  $query = 'DELETE FROM ' . self::$privateSessionsTable . ' WHERE ' . $ilDB->in('user_id', $userIds, false, 'integer') . ' AND ' . $ilDB->in('proom_id', $prooms, false, 'integer');
315  $ilDB->manipulate($query);
316  }
317 
318  $query = 'DELETE FROM ' . self::$userTable . ' WHERE room_id = %s AND ' .
319  $ilDB->in( 'user_id', $userIds, false, 'integer' );
320 
321  $types = array('integer');
322  $values = array($this->roomId);
323  $ilDB->manipulateF( $query, $types, $values );
324 
325  do
326  {
327  if ($this->getSetting( 'enable_history' )) {
328  $ilDB->insert(
329  self::$sessionTable,
330  array(
331  'room_id' => array('integer', $this->roomId),
332  'user_id' => array('integer', $row['user_id']),
333  'userdata' => array('text', $row['userdata']),
334  'connected' => array('integer', $row['connected']),
335  'disconnected' => array('integer', time()),
336  )
337  );
338  }
339  }
340  while( $row = $ilDB->fetchAssoc( $rset ) );
341  }
342 
343  }
344 
345  private function phpTypeToMDBType($type) {
346  switch($type) {
347  case 'string':
348  return 'text';
349  default:
350  return $type;
351  }
352 
353  }
354 
361  public function saveSettings(array $settings)
362  {
363  global $ilDB;
364 
365  $localSettings = array();
366 
367  foreach( $this->availableSettings as $setting => $type )
368  {
369  if( isset( $settings[$setting] ) ) {
370  if ($type == 'boolean') {
371  $settings[$setting] = (boolean)$settings[$setting];
372  }
373  $localSettings[$setting] = array($this->phpTypeToMDBType($type), $settings[$setting]);
374  }
375  }
376 
377  if (!$localSettings['room_type'][1]) {
378  $localSettings['room_type'][1] = 'repository';
379  }
380 
381  if( $this->roomId )
382  {
383  $ilDB->update(
384  self::$settingsTable,
385  $localSettings,
386  array( 'room_id' => array('integer', $this->roomId) )
387  );
388  }
389  else
390  {
391  $this->roomId = $ilDB->nextId( self::$settingsTable );
392 
393  $localSettings['room_id'] = array(
394  $this->availableSettings['room_id'], $this->roomId
395  );
396 
397  $ilDB->insert( self::$settingsTable, $localSettings );
398  }
399  }
400 
406  public function getSettings()
407  {
408  return $this->settings;
409  }
410 
418  public static function byObjectId($object_id)
419  {
420  global $ilDB;
421  $query = 'SELECT * FROM ' . self::$settingsTable . ' WHERE object_id = %s';
422  $types = array('integer');
423  $values = array($object_id);
424  $rset = $ilDB->queryF( $query, $types, $values );
425 
426  if( $row = $ilDB->fetchAssoc( $rset ) )
427  {
428  $room = new self();
429  $room->initialize( $row );
430  return $room;
431  }
432  }
433 
441  public static function byRoomId($room_id, $initObject = false)
442  {
443  global $ilDB;
444 
445  $query = 'SELECT * FROM ' . self::$settingsTable . ' WHERE room_id = %s';
446 
447  $types = array('integer');
448  $values = array($room_id);
449 
450  $rset = $ilDB->queryF( $query, $types, $values );
451 
452  if( $row = $ilDB->fetchAssoc( $rset ) )
453  {
454  $room = new self();
455  $room->initialize( $row );
456 
457  if ($initObject) {
458  $room->object = ilObjectFactory::getInstanceByObjId($row['object_id']);
459  }
460 
461  return $room;
462  }
463  }
464 
471  public function initialize(array $rowdata)
472  {
473  $this->roomId = $rowdata['room_id'];
474 
475  foreach( $this->availableSettings as $setting => $type )
476  {
477  if( isset($rowdata[$setting]) )
478  {
479  settype($rowdata[$setting], $this->availableSettings[$setting]);
480  $this->setSetting( $setting, $rowdata[$setting] );
481  }
482  }
483  }
484 
490  public function getRoomId()
491  {
492  return $this->roomId;
493  }
494 
503  public function isSubscribed($chat_userid)
504  {
505  global $ilDB;
506 
507  $query = 'SELECT count(user_id) as cnt FROM ' . self::$userTable .
508  ' WHERE room_id = %s AND user_id = %s';
509 
510  $types = array('integer', 'integer');
511  $values = array($this->roomId, $chat_userid);
512  $rset = $ilDB->queryF( $query, $types, $values );
513 
514  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
515  return true;
516 
517  return false;
518  }
519 
520  public function isAllowedToEnterPrivateRoom($chat_userid, $proom_id) {
521  //echo call_user_func_array('sprintf', array_merge(array($query), $values));
522  global $ilDB;
523 
524  $query = 'SELECT count(user_id) cnt FROM ' . self::$privateRoomsAccessTable .
525  ' WHERE proom_id = %s AND user_id = %s';
526 
527  $types = array('integer', 'integer');
528  $values = array($proom_id, $chat_userid);
529  $rset = $ilDB->queryF( $query, $types, $values );
530 
531  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
532  return true;
533 
534  $query = 'SELECT count(*) cnt FROM ' . self::$privateRoomsTable .
535  ' WHERE proom_id = %s AND owner = %s';
536 
537  $types = array('integer', 'integer');
538  $values = array($proom_id, $chat_userid);
539  $rset = $ilDB->queryF( $query, $types, $values );
540 
541  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
542  return true;
543 
544  return false;
545  }
546 
552  public function disconnectAllUsersFromAllRooms()
553  {
554  global $ilDB;
555 
556  $ilDB->manipulate( 'DELETE FROM ' . self::$userTable );
557  $ilDB->manipulate( 'UPDATE ' . self::$privateRoomsTable . ' SET closed = ' . $ilDB->quote( time() ,'integer') . ' WHERE closed = 0 OR closed IS NULL');
558  $ilDB->manipulate( 'UPDATE ' . self::$privateSessionsTable . ' SET disconnected = ' . $ilDB->quote( time() ,'integer') . ' WHERE disconnected = 0 OR disconnected IS NULL');
562  }
563 
574  public function getHistory(ilDateTime $from = null, ilDateTime $to = null, $restricted_session_userid = null, $proom_id = 0)
575  {
576  global $ilDB, $ilUser;
577 
578  $join = '';
579 
580  if ($proom_id) {
581  $join .= ' INNER JOIN ' . self::$privateSessionsTable .
582  ' pSessionTable ON pSessionTable.user_id = ' .
583  $ilDB->quote( $restricted_session_userid, 'integer' ) .
584  ' AND historyTable.sub_room = pSessionTable.proom_id AND timestamp >= pSessionTable.connected AND timestamp <= pSessionTable.disconnected ';
585  }
586 
587  $query = 'SELECT historyTable.* FROM ' . self::$historyTable . ' historyTable ' .
588  $join . ' WHERE historyTable.room_id = ' . $this->getRoomId();
589 
590  $filter = array();
591 
592  if( $from != null )
593  {
594  $filter[] = 'timestamp >= ' . $ilDB->quote( $from->getUnixTime(), 'integer' );
595  }
596 
597  if( $to != null )
598  {
599  $filter[] = 'timestamp <= ' . $ilDB->quote( $to->getUnixTime(), 'integer' );
600  }
601 
602  if( $filter )
603  $query .= ' AND ' . join( ' AND ', $filter );
604  $query .= ' ORDER BY timestamp ASC';
605 
606  $rset = $ilDB->query( $query );
607  $result = array();
608 
609  while( $row = $ilDB->fetchAssoc( $rset ) )
610  {
611  $row['message'] = json_decode( $row['message'] );
612  $row['message']->timestamp = $row['timestamp'];
613  if ($row['message']->public !== null && !$row['message']->public && !in_array($ilUser->getId(), explode(',', $row['recipients']))) {
614  continue;
615  }
616 
617  $result[] = $row;
618  }
619  return $result;
620  }
621 
622  public function getPrivateRoomSessions(ilDateTime $from = null, ilDateTime $to = null, $user_id = 0, $room_id=0 ) {
623  global $ilDB;
624 
625  $query = 'SELECT proom_id, title FROM ' . self::$privateRoomsTable . ' WHERE proom_id IN (
626  SELECT proom_id FROM '.self::$privateSessionsTable.' WHERE connected >= %s AND disconnected <= %s AND user_id = %s
627 
628  ) AND parent_id = %s';
629 
630  $rset = $ilDB->queryF($query, array('integer','integer','integer','integer'), array($from->getUnixTime(), $to->getUnixTime(), $user_id, $room_id));
631  $result = array();
632  while( $row = $ilDB->fetchAssoc( $rset ) )
633  {
634  $result[] = $row;
635  }
636  return $result;
637  }
638 
647  public function saveFileUploadToDb($user_id, $filename, $type)
648  {
649  global $ilDB;
650 
651  $upload_id = $ilDB->nextId( self::$uploadTable );
652 
653  $ilDB->insert(
654  self::$uploadTable,
655  array(
656  'upload_id' => array('integer', $upload_id),
657  'room_id' => array('integer', $this->roomId),
658  'user_id' => array('integer', $user_id),
659  'filename' => array('text', $filename),
660  'filetype' => array('text', $type),
661  'timestamp' => array('integer', time())
662  )
663  );
664  }
665 
673  public function banUser($user_id, $comment = '')
674  {
675  global $ilDB;
676 
677  $ilDB->insert(
678  self::$banTable,
679  array(
680  'room_id' => array('integer', $this->roomId),
681  'user_id' => array('integer', $user_id),
682  'timestamp' => array('integer', time()),
683  'remark' => array('text', $comment),
684  )
685  );
686  }
687 
696  public function unbanUser($user_id)
697  {
698  global $ilDB;
699 
700  if( !is_array( $user_id ) )
701  $user_id = array($user_id);
702 
703  $query = 'DELETE FROM ' . self::$banTable . ' WHERE room_id = %s AND ' .
704  $ilDB->in( 'user_id', $user_id, false, 'integer' );
705 
706  $types = array('integer');
707  $values = array($this->getRoomId());
708 
709  return $ilDB->manipulateF( $query, $types, $values );
710  }
711 
720  public function isUserBanned($user_id)
721  {
722  global $ilDB;
723 
724  $query = 'SELECT count(user_id) cnt FROM ' . self::$banTable .
725  ' WHERE user_id = %s AND room_id = %s';
726 
727  $types = array('integer', 'integer');
728  $values = array($user_id, $this->getRoomId());
729 
730  $rset = $ilDB->queryF( $query, $types, $values );
731 
732  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] )
733  return true;
734 
735  return false;
736  }
737 
745  public function getBannedUsers()
746  {
747  global $ilDB;
748 
749  $query = 'SELECT * FROM ' . self::$banTable . ' WHERE room_id = %s ';
750  $types = array('integer');
751  $values = array($this->getRoomId());
752  $rset = $ilDB->queryF( $query, $types, $values );
753  $result = array();
754 
755  if( $rset )
756  {
757  while( $row = $ilDB->fetchAssoc( $rset ) )
758  {
759  if( $row['user_id'] > 0 )
760  {
761  $user = new ilObjUser( $row['user_id'] );
762  $userdata = array(
763  'user_id' => $user->getId(),
764  'firstname' => $user->getFirstname(),
765  'lastname' => $user->getLastname(),
766  'login' => $user->getLogin(),
767  'remark' => $row['remark']
768  );
769 
770  $result[] = $userdata;
771  }
772  else
773  {
774  //@todo anonymous user
775  }
776  }
777  }
778 
779  return $result;
780  }
781 
792  public function getLastSession(ilChatroomUser $user)
793  {
794  global $ilDB;
795 
796  $query = 'SELECT * FROM ' . self::$sessionTable . ' WHERE user_id = ' .
797  $ilDB->quote( $user->getUserId(), 'integer' ) .
798  ' ORDER BY connected DESC';
799 
800  $ilDB->setLimit( 1 );
801  $rset = $ilDB->query( $query );
802 
803  if( $row = $ilDB->fetchAssoc( $rset ) )
804  {
805  return $row;
806  }
807  }
808 
819  public function getSessions(ilChatroomUser $user)
820  {
821  global $ilDB;
822 
823  $query = 'SELECT * FROM ' . self::$sessionTable
824  . ' WHERE room_id = '.
825  $ilDB->quote( $this->getRoomId(), 'integer' ) .
826  ' ORDER BY connected DESC';
827 
828  $rset = $ilDB->query( $query );
829 
830  $result = array();
831 
832  while( $row = $ilDB->fetchAssoc( $rset ) )
833  {
834  $result[] = $row;
835  }
836 
837  return $result;
838  }
839 
840  public function addPrivateRoom($title, ilChatroomUser $owner, $settings)
841  {
842  global $ilDB;
843 
844  $nextId = $ilDB->nextId('chatroom_prooms');
845 
846  $ilDB->insert(
847  self::$privateRoomsTable,
848  array(
849  'proom_id' => array('integer', $nextId),
850  'parent_id' => array('integer', $this->roomId),
851  'title' => array('text', $title),
852  'owner' => array('integer', $owner->getUserId()),
853  'created' => array('integer', time()),
854  'is_public' => array('integer', $settings['public']),
855  )
856  );
857 
858  return $nextId;
859  }
860 
861  public function closePrivateRoom($id)
862  {
863  global $ilDB;
864 
865  $ilDB->manipulateF(
866  'UPDATE ' . self::$privateRoomsTable . ' SET closed = %s WHERE proom_id = %s',
867  array('integer', 'integer'),
868  array(time(), $id)
869  );
870  }
871 
872  public function isOwnerOfPrivateRoom($user_id, $proom_id) {
873  global $ilDB;
874 
875  $query = 'SELECT proom_id FROM ' . self::$privateRoomsTable . ' WHERE proom_id = %s AND owner = %s';
876  $types = array('integer', 'integer');
877  $values = array($proom_id, $user_id);
878 
879  $rset = $ilDB->queryF($query, $types, $values);
880 
881  if ($rset && $ilDB->fetchAssoc($rset)) {
882  return true;
883  }
884  return false;
885  }
886 
891  public function inviteUserToPrivateRoom($user_id, $proom_id)
892  {
896  global $ilDB;
897 
898  $query = 'DELETE FROM ' . self::$privateRoomsAccessTable . ' WHERE user_id = %s AND proom_id = %s';
899  $types = array('integer', 'integer');
900  $values = array($user_id, $proom_id);
901 
902  $ilDB->manipulateF($query, $types, $values);
903 
904  $ilDB->insert(self::$privateRoomsAccessTable, array(
905  'user_id' => array('integer', $user_id),
906  'proom_id' => array('integer', $proom_id)
907  ));
908  }
909 
916  public function getChatURL($gui, $scope_id = 0)
917  {
918  include_once 'Services/Link/classes/class.ilLink.php';
919 
920  $url = '';
921 
922  if(is_object($gui))
923  {
924  if($scope_id)
925  {
926  $url = ilLink::_getStaticLink($gui->object->getRefId(), $gui->object->getType(), true, '_'.$scope_id);
927  }
928  else
929  {
930  $url = ilLink::_getStaticLink($gui->object->getRefId(), $gui->object->getType());
931  }
932  }
933 
934  return $url;
935  }
936 
945  public function sendInvitationNotification($gui, $sender, $recipient_id, $subScope = 0, $invitationLink = '')
946  {
950  global $lng;
951 
952  if($gui && !$invitationLink)
953  {
954  $invitationLink = $this->getChatURL($gui, $subScope);
955  }
956 
957  if($recipient_id > 0 && !in_array(ANONYMOUS_USER_ID, array($recipient_id)))
958  {
959  if(is_numeric($sender) && $sender > 0)
960  {
961  $sender_id = $sender;
965  $usr = ilObjectFactory::getInstanceByObjId($sender);
966  $public_name = $usr->getPublicName();
967  }
968  else if($sender instanceof ilChatroomUser)
969  {
970  if($sender->getUserId() > 0)
971  {
972  $sender_id = $sender->getUserId();
973  }
974  else
975  {
976  $sender_id = ANONYMOUS_USER_ID;
977  }
978  $public_name = $sender->getUsername();
979  }
980  else
981  {
982  throw new InvalidArgumentException('$sender must be an instance of ilChatroomUser or an id of an ilObjUser instance');
983  }
984 
985  $lng->loadLanguageModule('mail');
986 
987  $recipient = ilObjectFactory::getInstanceByObjId($recipient_id);
988  $bodyParams = array(
989  'link' => $invitationLink,
990  'inviter_name' => $public_name,
991  'room_name' => $this->getTitle(),
992  'salutation' => $lng->txt('mail_salutation_' . $recipient->getGender()) . ' ' . $recipient->getFullname()
993  );
994 
995  if($subScope)
996  {
997  $bodyParams['room_name'] .= ' - ' . self::lookupPrivateRoomTitle($subScope);
998  }
999 
1000  require_once 'Services/Notifications/classes/class.ilNotificationConfig.php';
1001  $notification = new ilNotificationConfig('chat_invitation');
1002  $notification->setTitleVar('chat_invitation', $bodyParams, 'chatroom');
1003  $notification->setShortDescriptionVar('chat_invitation_short', $bodyParams, 'chatroom');
1004  $notification->setLongDescriptionVar('chat_invitation_long', $bodyParams, 'chatroom');
1005  $notification->setAutoDisable(false);
1006  $notification->setLink($invitationLink);
1007  $notification->setIconPath('templates/default/images/icon_chtr_s.png');
1008  $notification->setValidForSeconds(0);
1009 
1010  $notification->setHandlerParam('mail.sender', $sender_id);
1011 
1012  $notification->notifyByUsers(array($recipient_id));
1013  }
1014  }
1015 
1016  public function inviteUserToPrivateRoomByLogin($login, $proom_id) {
1017  global $ilDB;
1018  $user_id = ilObjUser::_lookupId($login);
1019  $this->inviteUserToPrivateRoom($user_id, $proom_id);
1020  }
1021 
1022  public static function lookupPrivateRoomTitle($proom_id) {
1023  global $ilDB;
1024 
1025  $query = 'SELECT title FROM ' . self::$privateRoomsTable . ' WHERE proom_id = %s';
1026  $types = array('integer');
1027  $values = array($proom_id);
1028 
1029  $rset = $ilDB->queryF($query, $types, $values);
1030 
1031  if ($row = $ilDB->fetchAssoc($rset)) {
1032  return $row['title'];
1033  }
1034 
1035  return 'unkown';
1036  }
1037 
1038  public function getActivePrivateRooms($userid)
1039  {
1040  global $ilDB;
1041 
1042  $query = '
1043  SELECT roomtable.title, roomtable.proom_id, accesstable.user_id id, roomtable.owner owner FROM ' . self::$privateRoomsTable . ' roomtable
1044  LEFT JOIN '.self::$privateRoomsAccessTable.' accesstable ON roomtable.proom_id = accesstable.proom_id AND accesstable.user_id = %s
1045  WHERE parent_id = %s AND (closed = 0 OR closed IS NULL) AND (accesstable.user_id IS NOT NULL OR roomtable.owner = %s)';
1046  $types = array('integer', 'integer', 'integer');
1047  $values = array($userid, $this->roomId, $userid);
1048  $rset = $ilDB->queryF( $query, $types, $values );
1049  $rooms = array();
1050 
1051  while( $row = $ilDB->fetchAssoc( $rset ) )
1052  {
1053  $row['active_users'] = $this->listUsersInPrivateRoom($row['id']);
1054  $row['owner'] = $row['owner'];
1055  $rooms[$row['proom_id']] = $row;
1056  }
1057 
1058  return $rooms;
1059  }
1060 
1061  public function listUsersInPrivateRoom($private_room_id) {
1062  global $ilDB;
1063 
1064  $query = 'SELECT user_id FROM ' . self::$privateSessionsTable . ' WHERE proom_id = %s AND disconnected = 0 OR disconnected IS NULL';
1065  $types = array('integer');
1066  $values = array($private_room_id);
1067  $rset = $ilDB->queryF( $query, $types, $values );
1068 
1069  $users = array();
1070 
1071  while ($row = $ilDB->fetchAssoc($rset)) {
1072  $users[] = $row['user_id'];
1073  }
1074 
1075  return $users;
1076  }
1077 
1078  public function userIsInPrivateRoom($room_id, $user_id)
1079  {
1080  global $ilDB;
1081 
1082  $query = 'SELECT proom_id id FROM ' . self::$privateSessionsTable . ' WHERE user_id = %s AND proom_id = %s AND disconnected = 0 OR disconnected IS NULL';
1083  $types = array('integer', 'integer');
1084  $values = array($user_id, $room_id);
1085  $rset = $ilDB->queryF( $query, $types, $values );
1086  if ($ilDB->fetchAssoc($rset))
1087  return true;
1088  return false;
1089  }
1090 
1091  public function subscribeUserToPrivateRoom($room_id, $user_id)
1092  {
1093  global $ilDB;
1094 
1095  if (!$this->userIsInPrivateRoom($room_id, $user_id)) {
1096  $ilDB->insert(
1097  self::$privateSessionsTable,
1098  array(
1099  'proom_id' => array('integer', $room_id),
1100  'user_id' => array('integer', $user_id),
1101  'connected' => array('integer', time()),
1102  'disconnected' => array('integer', 0),
1103  )
1104  );
1105  }
1106  }
1107 
1114  public function unsubscribeUserFromPrivateRoom($room_id, $user_id)
1115  {
1116  global $ilDB;
1117 
1118  $ilDB->update(
1119  self::$privateSessionsTable,
1120  array(
1121  'disconnected' => array('integer', time())
1122  ),
1123  array(
1124  'proom_id' => array('integer', $room_id),
1125  'user_id' => array('integer', $user_id),
1126  )
1127  );
1128  }
1129 
1130  public function countActiveUsers() {
1131  global $ilDB;
1132 
1133  $query = 'SELECT count(user_id) as cnt FROM ' . self::$userTable .
1134  ' WHERE room_id = %s';
1135 
1136  $types = array('integer');
1137  $values = array($this->roomId);
1138  $rset = $ilDB->queryF( $query, $types, $values );
1139 
1140  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
1141  return $row['cnt'];
1142 
1143  return 0;
1144  }
1145 
1146  public function getUniquePrivateRoomTitle($title) {
1147  global $ilDB;
1148 
1149  $query = 'SELECT title FROM ' . self::$privateRoomsTable . ' WHERE parent_id = %s and closed = 0';
1150  $rset = $ilDB->queryF($query, array('integer'), array($this->roomId));
1151 
1152  $titles = array();
1153 
1154  while($row = $ilDB->fetchAssoc($rset)) {
1155  $titles[] = $row['title'];
1156  }
1157 
1158  $suffix = '';
1159  $i = 0;
1160  do {
1161  if(!in_array($title . $suffix, $titles)) {
1162  $title .= $suffix;
1163  break;
1164  }
1165 
1166  ++$i;
1167 
1168  $suffix = ' (' . $i . ')';
1169  } while(true);
1170 
1171  return $title;
1172  }
1173 
1174  public static function findDeletablePrivateRooms() {
1175  global $ilDB;
1176 
1177  $query = 'SELECT private_rooms.proom_id id, MIN(disconnected) min_disconnected, MAX(disconnected) max_disconnected FROM ' . self::$privateSessionsTable . ' private_sessions INNER JOIN '.self::$privateRoomsTable.' private_rooms ON private_sessions.proom_id = private_rooms.proom_id WHERE closed = 0 GROUP BY private_rooms.proom_id HAVING min_disconnected > 0 AND max_disconnected < %s';
1178  $rset = $ilDB->queryF(
1179  $query,
1180  array('integer'),
1181  array(time() + 60 * 5)
1182  );
1183 
1184  $rooms = array();
1185 
1186  while ($row = $ilDB->fetchAssoc($rset)) {
1187  $rooms[] = $row['id'];
1188  }
1189 
1190  $query = 'SELECT DISTINCT proom_id, room_id, object_id FROM ' . self::$privateRoomsTable
1191  . ' INNER JOIN ' . self::$settingsTable . ' ON parent_id = room_id '
1192  . ' WHERE ' . $ilDB->in('proom_id', $rooms, false, 'integer');
1193 
1194  $rset = $ilDB->query($query);
1195  $rooms = array();
1196  while($row = $ilDB->fetchAssoc($rset)) {
1197  $rooms[] = array(
1198  'proom_id' => $row['proom_id'],
1199  'room_id' => $row['room_id'],
1200  'object_id' => $row['object_id']
1201  );
1202  }
1203 
1204  return $rooms;
1205  }
1206 
1215  public function getAllRooms($user_id)
1216  {
1220  global $ilDB;
1221 
1222  $query = "
1223  SELECT room_id, od.title
1224  FROM object_data od
1225 
1226  INNER JOIN " . self::$settingsTable . "
1227  ON object_id = od.obj_id
1228 
1229  INNER JOIN " . self::$privateRoomsTable . " prt
1230  ON prt.owner = %s
1231 
1232  WHERE od.type = 'chtr'
1233  ";
1234 
1235  $types = array('integer');
1236  $values = array($user_id);
1237 
1238  $res = $ilDB->queryF($query, $types, $values);
1239 
1240  $rooms = array();
1241 
1242  while($row = $ilDB->fetchAssoc($res))
1243  {
1244  $room_id = $row['room_id'];
1245  $rooms[$room_id] = $row['title'];
1246  }
1247 
1248  return $rooms;
1249  }
1250 
1251 
1252 
1253  public function getPrivateSubRooms($parent_room, $user_id)
1254  {
1255  global $ilDB;
1256 
1257  $query = "
1258  SELECT proom_id, parent_id
1259  FROM chatroom_prooms
1260  WHERE parent_id = %s
1261  AND owner = %s
1262  AND closed = 0
1263  ";
1264 
1265  $types = array( 'integer', 'integer' );
1266  $values = array( $parent_room, $user_id );
1267 
1268  $res = $ilDB->queryF( $query, $types, $values );
1269 
1270  $priv_rooms = array();
1271 
1272  while( $row = $ilDB->fetchAssoc( $res ) )
1273  {
1274  $proom_id = $row['proom_id'];
1275  $priv_rooms[$proom_id] = $row['parent_id'];
1276  }
1277 
1278  return $priv_rooms;
1279  }
1280 
1288  public function getRefIdByRoomId($room_id)
1289  {
1290  global $ilDB;
1291 
1292  $query = "
1293  SELECT objr.ref_id
1294  FROM object_reference objr
1295 
1296  INNER JOIN chatroom_settings cs
1297  ON cs.object_id = objr.obj_id
1298 
1299  INNER JOIN object_data od
1300  ON od.obj_id = cs.object_id
1301 
1302  WHERE cs.room_id = %s
1303  ";
1304 
1305  $types = array( 'integer' );
1306  $values = array( $room_id );
1307 
1308  $res = $ilDB->queryF( $query, $types, $values );
1309 
1310  $row = $ilDB->fetchAssoc( $res );
1311 
1312  return $row['ref_id'];
1313  }
1314 
1315 public function getLastMessages($number, $chatuser = null) {
1316  global $ilDB;
1317 
1318  // There is currently no way to check if a message is private or not
1319  // by sql. So we fetch twice as much as we need and hope that there
1320  // are not more than $number private messages.
1321  $ilDB->setLimit($number * 2);
1322  $rset = $ilDB->queryF('SELECT * FROM ' . self::$historyTable . ' WHERE room_id = %s AND sub_room = 0 ORDER BY timestamp DESC', array('integer'), array($this->roomId));
1323 
1324  $result_count = 0;
1325  $results = array();
1326  while(($row = $ilDB->fetchAssoc($rset)) && $result_count < $number) {
1327  $tmp = json_decode($row['message']);
1328  if ($chatuser !== null && $tmp->public == 0 && $tmp->recipients) {
1329  if (in_array($chatuser->getUserId(), explode(',',$tmp->recipients))) {
1330  $results[] = $tmp;
1331  ++$result_count;
1332  }
1333  }
1334  else if ($tmp->public == 1) {
1335  $results[] = $tmp;
1336  ++$result_count;
1337  }
1338 
1339  }
1340  return $results;
1341  }
1342 
1343  public function getLastMessagesForChatViewer($number, $chatuser = null)
1344  {
1348  global $ilDB;
1349 
1350  $ilDB->setLimit($number);
1351  $rset = $ilDB->query(
1352  'SELECT *
1353  FROM ' . self::$historyTable . '
1354  WHERE room_id = '.$ilDB->quote($this->roomId, 'integer').'
1355  AND sub_room = 0
1356  AND (
1357  (' . $ilDB->like('message', 'text', '%"type":"message"%') . ' AND ' . $ilDB->like('message', 'text', '%"public":1%') . ' AND ' . $ilDB->like('message', 'text', '%"recipients":null%') . ')
1358  OR
1359  ' . $ilDB->like('message', 'text', '%"type":"%connected"%') . ')
1360  ORDER BY timestamp DESC'
1361  );
1362 
1363  $results = array();
1364  while(($row = $ilDB->fetchAssoc($rset)))
1365  {
1366  $tmp = json_decode($row['message']);
1367  if($tmp->type != 'message' && $row['timestamp'] && !is_numeric($tmp->timestamp))
1368  {
1369  $tmp->timestamp = $row['timestamp'] * 1000;
1370  }
1371  $results[] = $tmp;
1372  }
1373  return $results;
1374  }
1375 
1376  public function clearMessages($sub_room) {
1377  global $ilDB;
1378 
1379  $ilDB->queryF(
1380  'DELETE FROM ' . self::$historyTable . ' WHERE room_id = %s AND sub_room = %s',
1381  array('integer', 'integer'),
1382  array($this->roomId, (int)$sub_room)
1383  );
1384 
1385  if ($sub_room) {
1386  $ilDB->queryF(
1387  'DELETE FROM ' . self::$sessionTable . ' WHERE proom_id = %s AND disconnected < %s',
1388  array('integer', 'integer'),
1389  array($sub_room, time())
1390  );
1391  }
1392  else {
1393  $ilDB->queryF(
1394  'DELETE FROM ' . self::$sessionTable . ' WHERE room_id = %s AND disconnected < %s',
1395  array('integer', 'integer'),
1396  array($this->roomId, time())
1397  );
1398  }
1399  }
1400 
1401  public static function getUntrashedChatReferences()
1402  {
1406  global $ilDB;
1407 
1408  // Check for parent because of an invalid parent node for the old public chat (thx @ jposselt ;-)).
1409  // We cannot find this old public chat and clean this automatically
1410  $query = '
1411  SELECT od.obj_id, od.title, ore.ref_id, od.type
1412  FROM object_data od
1413  INNER JOIN object_reference ore ON ore.obj_id = od.obj_id
1414  INNER JOIN tree t ON t.child = ore.ref_id
1415  INNER JOIN object_reference pre ON pre.ref_id = t.parent
1416  INNER JOIN object_data pod ON pod.obj_id = pre.obj_id
1417  WHERE od.type = %s AND t.tree > 0 AND ore.deleted IS NULL
1418  ORDER BY od.title
1419  ';
1420  $res = $ilDB->queryF($query, array('text'), array('chtr'));
1421 
1422  $chats = array();
1423  while($row = $ilDB->fetchAssoc($res))
1424  {
1425  $chats[] = $row;
1426  }
1427 
1428  return $chats;
1429  }
1430 }
1431 
1432 ?>