ILIAS  Release_4_4_x_branch Revision 61816
 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  // Notice: Using replace instead of insert looks strange, because we actually know whether the selected data exists or not
225  // But we occasionally found some duplicate key errors although the data set should not exist when the following code is reached
226  $ilDB->replace(
227  self::$userTable,
228  array(
229  'room_id' => array('integer', $this->roomId),
230  'user_id' => array('integer', $user->getUserId())
231  ),
232  array(
233  'userdata' => array('text', json_encode($userdata)),
234  'connected' => array('integer', time()),
235  )
236  );
237 
238  return true;
239  }
240 
241  return false;
242  }
243 
253  public function getConnectedUsers()
254  {
255  global $ilDB;
256 
257  $query = 'SELECT userdata FROM ' . self::$userTable . ' WHERE room_id = %s';
258  $types = array('integer');
259  $values = array($this->roomId);
260  $rset = $ilDB->queryF( $query, $types, $values );
261  $users = array();
262 
263  while( $row = $ilDB->fetchAssoc( $rset ) )
264  {
265  $users[] = json_decode( $row['userdata'] );
266  }
267 
268  return $users;
269  }
270 
277  public function disconnectUser($user_id)
278  {
279  $this->disconnectUsers( array($user_id) );
280  }
281 
291  public function disconnectUsers(array $userIds)
292  {
293  global $ilDB;
294 
295  $query = 'SELECT * FROM ' . self::$userTable . ' WHERE room_id = %s AND ' .
296  $ilDB->in( 'user_id', $userIds, false, 'integer' );
297 
298  $types = array('integer');
299  $values = array($this->roomId);
300  $rset = $ilDB->queryF( $query, $types, $values );
301 
302  if( $row = $ilDB->fetchAssoc( $rset ) )
303  {
304  $query = 'SELECT proom_id FROM ' . self::$privateRoomsTable . ' WHERE parent_id = %s';
305  $rset_prooms = $ilDB->queryF($query, array('integer'), array($this->roomId));
306 
307  $prooms = array();
308 
309  while($row_prooms = $ilDB->fetchAssoc($rset_prooms)) {
310  $prooms[] = $row_prooms['proom_id'];
311  }
312 
313  if (true || $this->getSetting( 'enable_history' )) {
314  $query = 'UPDATE ' . self::$privateSessionsTable . ' SET disconnected = %s WHERE ' . $ilDB->in('user_id', $userIds, false, 'integer') . ' AND ' . $ilDB->in('proom_id', $prooms, false, 'integer');
315  $ilDB->manipulateF($query, array('integer'), array(time()));
316  }
317  else {
318  $query = 'DELETE FROM ' . self::$privateSessionsTable . ' WHERE ' . $ilDB->in('user_id', $userIds, false, 'integer') . ' AND ' . $ilDB->in('proom_id', $prooms, false, 'integer');
319  $ilDB->manipulate($query);
320  }
321 
322  $query = 'DELETE FROM ' . self::$userTable . ' WHERE room_id = %s AND ' .
323  $ilDB->in( 'user_id', $userIds, false, 'integer' );
324 
325  $types = array('integer');
326  $values = array($this->roomId);
327  $ilDB->manipulateF( $query, $types, $values );
328 
329  do
330  {
331  if ($this->getSetting( 'enable_history' )) {
332  $ilDB->insert(
333  self::$sessionTable,
334  array(
335  'room_id' => array('integer', $this->roomId),
336  'user_id' => array('integer', $row['user_id']),
337  'userdata' => array('text', $row['userdata']),
338  'connected' => array('integer', $row['connected']),
339  'disconnected' => array('integer', time()),
340  )
341  );
342  }
343  }
344  while( $row = $ilDB->fetchAssoc( $rset ) );
345  }
346 
347  }
348 
349  private function phpTypeToMDBType($type) {
350  switch($type) {
351  case 'string':
352  return 'text';
353  default:
354  return $type;
355  }
356 
357  }
358 
365  public function saveSettings(array $settings)
366  {
367  global $ilDB;
368 
369  $localSettings = array();
370 
371  foreach( $this->availableSettings as $setting => $type )
372  {
373  if( isset( $settings[$setting] ) ) {
374  if ($type == 'boolean') {
375  $settings[$setting] = (boolean)$settings[$setting];
376  }
377  $localSettings[$setting] = array($this->phpTypeToMDBType($type), $settings[$setting]);
378  }
379  }
380 
381  if (!$localSettings['room_type'][1]) {
382  $localSettings['room_type'][1] = 'repository';
383  }
384 
385  if( $this->roomId )
386  {
387  $ilDB->update(
388  self::$settingsTable,
389  $localSettings,
390  array( 'room_id' => array('integer', $this->roomId) )
391  );
392  }
393  else
394  {
395  $this->roomId = $ilDB->nextId( self::$settingsTable );
396 
397  $localSettings['room_id'] = array(
398  $this->availableSettings['room_id'], $this->roomId
399  );
400 
401  $ilDB->insert( self::$settingsTable, $localSettings );
402  }
403  }
404 
410  public function getSettings()
411  {
412  return $this->settings;
413  }
414 
422  public static function byObjectId($object_id)
423  {
424  global $ilDB;
425  $query = 'SELECT * FROM ' . self::$settingsTable . ' WHERE object_id = %s';
426  $types = array('integer');
427  $values = array($object_id);
428  $rset = $ilDB->queryF( $query, $types, $values );
429 
430  if( $row = $ilDB->fetchAssoc( $rset ) )
431  {
432  $room = new self();
433  $room->initialize( $row );
434  return $room;
435  }
436  }
437 
445  public static function byRoomId($room_id, $initObject = false)
446  {
447  global $ilDB;
448 
449  $query = 'SELECT * FROM ' . self::$settingsTable . ' WHERE room_id = %s';
450 
451  $types = array('integer');
452  $values = array($room_id);
453 
454  $rset = $ilDB->queryF( $query, $types, $values );
455 
456  if( $row = $ilDB->fetchAssoc( $rset ) )
457  {
458  $room = new self();
459  $room->initialize( $row );
460 
461  if ($initObject) {
462  $room->object = ilObjectFactory::getInstanceByObjId($row['object_id']);
463  }
464 
465  return $room;
466  }
467  }
468 
475  public function initialize(array $rowdata)
476  {
477  $this->roomId = $rowdata['room_id'];
478 
479  foreach( $this->availableSettings as $setting => $type )
480  {
481  if( isset($rowdata[$setting]) )
482  {
483  settype($rowdata[$setting], $this->availableSettings[$setting]);
484  $this->setSetting( $setting, $rowdata[$setting] );
485  }
486  }
487  }
488 
494  public function getRoomId()
495  {
496  return $this->roomId;
497  }
498 
507  public function isSubscribed($chat_userid)
508  {
509  global $ilDB;
510 
511  $query = 'SELECT count(user_id) as cnt FROM ' . self::$userTable .
512  ' WHERE room_id = %s AND user_id = %s';
513 
514  $types = array('integer', 'integer');
515  $values = array($this->roomId, $chat_userid);
516  $rset = $ilDB->queryF( $query, $types, $values );
517 
518  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
519  return true;
520 
521  return false;
522  }
523 
524  public function isAllowedToEnterPrivateRoom($chat_userid, $proom_id) {
525  //echo call_user_func_array('sprintf', array_merge(array($query), $values));
526  global $ilDB;
527 
528  $query = 'SELECT count(user_id) cnt FROM ' . self::$privateRoomsAccessTable .
529  ' WHERE proom_id = %s AND user_id = %s';
530 
531  $types = array('integer', 'integer');
532  $values = array($proom_id, $chat_userid);
533  $rset = $ilDB->queryF( $query, $types, $values );
534 
535  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
536  return true;
537 
538  $query = 'SELECT count(*) cnt FROM ' . self::$privateRoomsTable .
539  ' WHERE proom_id = %s AND owner = %s';
540 
541  $types = array('integer', 'integer');
542  $values = array($proom_id, $chat_userid);
543  $rset = $ilDB->queryF( $query, $types, $values );
544 
545  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
546  return true;
547 
548  return false;
549  }
550 
556  public function disconnectAllUsersFromAllRooms()
557  {
558  global $ilDB;
559 
560  $ilDB->manipulate( 'DELETE FROM ' . self::$userTable );
561  $ilDB->manipulate( 'UPDATE ' . self::$privateRoomsTable . ' SET closed = ' . $ilDB->quote( time() ,'integer') . ' WHERE closed = 0 OR closed IS NULL');
562  $ilDB->manipulate( 'UPDATE ' . self::$privateSessionsTable . ' SET disconnected = ' . $ilDB->quote( time() ,'integer') . ' WHERE disconnected = 0 OR disconnected IS NULL');
566  }
567 
578  public function getHistory(ilDateTime $from = null, ilDateTime $to = null, $restricted_session_userid = null, $proom_id = 0)
579  {
580  global $ilDB, $ilUser;
581 
582  $join = '';
583 
584  if ($proom_id) {
585  $join .= ' INNER JOIN ' . self::$privateSessionsTable .
586  ' pSessionTable ON pSessionTable.user_id = ' .
587  $ilDB->quote( $restricted_session_userid, 'integer' ) .
588  ' AND historyTable.sub_room = pSessionTable.proom_id AND timestamp >= pSessionTable.connected AND timestamp <= pSessionTable.disconnected ';
589  }
590 
591  $query = 'SELECT historyTable.* FROM ' . self::$historyTable . ' historyTable ' .
592  $join . ' WHERE historyTable.room_id = ' . $this->getRoomId();
593 
594  $filter = array();
595 
596  if( $from != null )
597  {
598  $filter[] = 'timestamp >= ' . $ilDB->quote( $from->getUnixTime(), 'integer' );
599  }
600 
601  if( $to != null )
602  {
603  $filter[] = 'timestamp <= ' . $ilDB->quote( $to->getUnixTime(), 'integer' );
604  }
605 
606  if( $filter )
607  $query .= ' AND ' . join( ' AND ', $filter );
608  $query .= ' ORDER BY timestamp ASC';
609 
610  $rset = $ilDB->query( $query );
611  $result = array();
612 
613  while( $row = $ilDB->fetchAssoc( $rset ) )
614  {
615  $row['message'] = json_decode( $row['message'] );
616  $row['message']->timestamp = $row['timestamp'];
617  if ($row['message']->public !== null && !$row['message']->public && !in_array($ilUser->getId(), explode(',', $row['recipients']))) {
618  continue;
619  }
620 
621  $result[] = $row;
622  }
623  return $result;
624  }
625 
626  public function getPrivateRoomSessions(ilDateTime $from = null, ilDateTime $to = null, $user_id = 0, $room_id=0 ) {
627  global $ilDB;
628 
629  $query = 'SELECT proom_id, title FROM ' . self::$privateRoomsTable . ' WHERE proom_id IN (
630  SELECT proom_id FROM '.self::$privateSessionsTable.' WHERE connected >= %s AND disconnected <= %s AND user_id = %s
631 
632  ) AND parent_id = %s';
633 
634  $rset = $ilDB->queryF($query, array('integer','integer','integer','integer'), array($from->getUnixTime(), $to->getUnixTime(), $user_id, $room_id));
635  $result = array();
636  while( $row = $ilDB->fetchAssoc( $rset ) )
637  {
638  $result[] = $row;
639  }
640  return $result;
641  }
642 
651  public function saveFileUploadToDb($user_id, $filename, $type)
652  {
653  global $ilDB;
654 
655  $upload_id = $ilDB->nextId( self::$uploadTable );
656 
657  $ilDB->insert(
658  self::$uploadTable,
659  array(
660  'upload_id' => array('integer', $upload_id),
661  'room_id' => array('integer', $this->roomId),
662  'user_id' => array('integer', $user_id),
663  'filename' => array('text', $filename),
664  'filetype' => array('text', $type),
665  'timestamp' => array('integer', time())
666  )
667  );
668  }
669 
677  public function banUser($user_id, $comment = '')
678  {
679  global $ilDB;
680 
681  $ilDB->insert(
682  self::$banTable,
683  array(
684  'room_id' => array('integer', $this->roomId),
685  'user_id' => array('integer', $user_id),
686  'timestamp' => array('integer', time()),
687  'remark' => array('text', $comment),
688  )
689  );
690  }
691 
700  public function unbanUser($user_id)
701  {
702  global $ilDB;
703 
704  if( !is_array( $user_id ) )
705  $user_id = array($user_id);
706 
707  $query = 'DELETE FROM ' . self::$banTable . ' WHERE room_id = %s AND ' .
708  $ilDB->in( 'user_id', $user_id, false, 'integer' );
709 
710  $types = array('integer');
711  $values = array($this->getRoomId());
712 
713  return $ilDB->manipulateF( $query, $types, $values );
714  }
715 
724  public function isUserBanned($user_id)
725  {
726  global $ilDB;
727 
728  $query = 'SELECT count(user_id) cnt FROM ' . self::$banTable .
729  ' WHERE user_id = %s AND room_id = %s';
730 
731  $types = array('integer', 'integer');
732  $values = array($user_id, $this->getRoomId());
733 
734  $rset = $ilDB->queryF( $query, $types, $values );
735 
736  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] )
737  return true;
738 
739  return false;
740  }
741 
749  public function getBannedUsers()
750  {
751  global $ilDB;
752 
753  $query = 'SELECT * FROM ' . self::$banTable . ' WHERE room_id = %s ';
754  $types = array('integer');
755  $values = array($this->getRoomId());
756  $rset = $ilDB->queryF( $query, $types, $values );
757  $result = array();
758 
759  if( $rset )
760  {
761  while( $row = $ilDB->fetchAssoc( $rset ) )
762  {
763  if( $row['user_id'] > 0 )
764  {
765  $user = new ilObjUser( $row['user_id'] );
766  $userdata = array(
767  'user_id' => $user->getId(),
768  'firstname' => $user->getFirstname(),
769  'lastname' => $user->getLastname(),
770  'login' => $user->getLogin(),
771  'remark' => $row['remark']
772  );
773 
774  $result[] = $userdata;
775  }
776  else
777  {
778  //@todo anonymous user
779  }
780  }
781  }
782 
783  return $result;
784  }
785 
796  public function getLastSession(ilChatroomUser $user)
797  {
798  global $ilDB;
799 
800  $query = 'SELECT * FROM ' . self::$sessionTable . ' WHERE user_id = ' .
801  $ilDB->quote( $user->getUserId(), 'integer' ) .
802  ' ORDER BY connected DESC';
803 
804  $ilDB->setLimit( 1 );
805  $rset = $ilDB->query( $query );
806 
807  if( $row = $ilDB->fetchAssoc( $rset ) )
808  {
809  return $row;
810  }
811  }
812 
823  public function getSessions(ilChatroomUser $user)
824  {
825  global $ilDB;
826 
827  $query = 'SELECT * FROM ' . self::$sessionTable
828  . ' WHERE room_id = '.
829  $ilDB->quote( $this->getRoomId(), 'integer' ) .
830  ' ORDER BY connected DESC';
831 
832  $rset = $ilDB->query( $query );
833 
834  $result = array();
835 
836  while( $row = $ilDB->fetchAssoc( $rset ) )
837  {
838  $result[] = $row;
839  }
840 
841  return $result;
842  }
843 
844  public function addPrivateRoom($title, ilChatroomUser $owner, $settings)
845  {
846  global $ilDB;
847 
848  $nextId = $ilDB->nextId('chatroom_prooms');
849 
850  $ilDB->insert(
851  self::$privateRoomsTable,
852  array(
853  'proom_id' => array('integer', $nextId),
854  'parent_id' => array('integer', $this->roomId),
855  'title' => array('text', $title),
856  'owner' => array('integer', $owner->getUserId()),
857  'created' => array('integer', time()),
858  'is_public' => array('integer', $settings['public']),
859  )
860  );
861 
862  return $nextId;
863  }
864 
865  public function closePrivateRoom($id)
866  {
867  global $ilDB;
868 
869  $ilDB->manipulateF(
870  'UPDATE ' . self::$privateRoomsTable . ' SET closed = %s WHERE proom_id = %s',
871  array('integer', 'integer'),
872  array(time(), $id)
873  );
874  }
875 
876  public function isOwnerOfPrivateRoom($user_id, $proom_id) {
877  global $ilDB;
878 
879  $query = 'SELECT proom_id FROM ' . self::$privateRoomsTable . ' WHERE proom_id = %s AND owner = %s';
880  $types = array('integer', 'integer');
881  $values = array($proom_id, $user_id);
882 
883  $rset = $ilDB->queryF($query, $types, $values);
884 
885  if ($rset && $ilDB->fetchAssoc($rset)) {
886  return true;
887  }
888  return false;
889  }
890 
895  public function inviteUserToPrivateRoom($user_id, $proom_id)
896  {
900  global $ilDB;
901 
902  $query = 'DELETE FROM ' . self::$privateRoomsAccessTable . ' WHERE user_id = %s AND proom_id = %s';
903  $types = array('integer', 'integer');
904  $values = array($user_id, $proom_id);
905 
906  $ilDB->manipulateF($query, $types, $values);
907 
908  $ilDB->insert(self::$privateRoomsAccessTable, array(
909  'user_id' => array('integer', $user_id),
910  'proom_id' => array('integer', $proom_id)
911  ));
912  }
913 
920  public function getChatURL($gui, $scope_id = 0)
921  {
922  include_once 'Services/Link/classes/class.ilLink.php';
923 
924  $url = '';
925 
926  if(is_object($gui))
927  {
928  if($scope_id)
929  {
930  $url = ilLink::_getStaticLink($gui->object->getRefId(), $gui->object->getType(), true, '_'.$scope_id);
931  }
932  else
933  {
934  $url = ilLink::_getStaticLink($gui->object->getRefId(), $gui->object->getType());
935  }
936  }
937 
938  return $url;
939  }
940 
949  public function sendInvitationNotification($gui, $sender, $recipient_id, $subScope = 0, $invitationLink = '')
950  {
954  global $lng;
955 
956  if($gui && !$invitationLink)
957  {
958  $invitationLink = $this->getChatURL($gui, $subScope);
959  }
960 
961  if($recipient_id > 0 && !in_array(ANONYMOUS_USER_ID, array($recipient_id)))
962  {
963  if(is_numeric($sender) && $sender > 0)
964  {
965  $sender_id = $sender;
969  $usr = ilObjectFactory::getInstanceByObjId($sender);
970  $public_name = $usr->getPublicName();
971  }
972  else if($sender instanceof ilChatroomUser)
973  {
974  if($sender->getUserId() > 0)
975  {
976  $sender_id = $sender->getUserId();
977  }
978  else
979  {
980  $sender_id = ANONYMOUS_USER_ID;
981  }
982  $public_name = $sender->getUsername();
983  }
984  else
985  {
986  throw new InvalidArgumentException('$sender must be an instance of ilChatroomUser or an id of an ilObjUser instance');
987  }
988 
989  $lng->loadLanguageModule('mail');
990 
991  $recipient = ilObjectFactory::getInstanceByObjId($recipient_id);
992  $bodyParams = array(
993  'link' => $invitationLink,
994  'inviter_name' => $public_name,
995  'room_name' => $this->getTitle(),
996  'salutation' => $lng->txt('mail_salutation_' . $recipient->getGender()) . ' ' . $recipient->getFullname()
997  );
998 
999  if($subScope)
1000  {
1001  $bodyParams['room_name'] .= ' - ' . self::lookupPrivateRoomTitle($subScope);
1002  }
1003 
1004  require_once 'Services/Notifications/classes/class.ilNotificationConfig.php';
1005  $notification = new ilNotificationConfig('chat_invitation');
1006  $notification->setTitleVar('chat_invitation', $bodyParams, 'chatroom');
1007  $notification->setShortDescriptionVar('chat_invitation_short', $bodyParams, 'chatroom');
1008  $notification->setLongDescriptionVar('chat_invitation_long', $bodyParams, 'chatroom');
1009  $notification->setAutoDisable(false);
1010  $notification->setLink($invitationLink);
1011  $notification->setIconPath('templates/default/images/icon_chtr_s.png');
1012  $notification->setValidForSeconds(0);
1013 
1014  $notification->setHandlerParam('mail.sender', $sender_id);
1015 
1016  $notification->notifyByUsers(array($recipient_id));
1017  }
1018  }
1019 
1020  public function inviteUserToPrivateRoomByLogin($login, $proom_id) {
1021  global $ilDB;
1022  $user_id = ilObjUser::_lookupId($login);
1023  $this->inviteUserToPrivateRoom($user_id, $proom_id);
1024  }
1025 
1026  public static function lookupPrivateRoomTitle($proom_id) {
1027  global $ilDB;
1028 
1029  $query = 'SELECT title FROM ' . self::$privateRoomsTable . ' WHERE proom_id = %s';
1030  $types = array('integer');
1031  $values = array($proom_id);
1032 
1033  $rset = $ilDB->queryF($query, $types, $values);
1034 
1035  if ($row = $ilDB->fetchAssoc($rset)) {
1036  return $row['title'];
1037  }
1038 
1039  return 'unkown';
1040  }
1041 
1042  public function getActivePrivateRooms($userid)
1043  {
1044  global $ilDB;
1045 
1046  $query = '
1047  SELECT roomtable.title, roomtable.proom_id, accesstable.user_id id, roomtable.owner owner FROM ' . self::$privateRoomsTable . ' roomtable
1048  LEFT JOIN '.self::$privateRoomsAccessTable.' accesstable ON roomtable.proom_id = accesstable.proom_id AND accesstable.user_id = %s
1049  WHERE parent_id = %s AND (closed = 0 OR closed IS NULL) AND (accesstable.user_id IS NOT NULL OR roomtable.owner = %s)';
1050  $types = array('integer', 'integer', 'integer');
1051  $values = array($userid, $this->roomId, $userid);
1052  $rset = $ilDB->queryF( $query, $types, $values );
1053  $rooms = array();
1054 
1055  while( $row = $ilDB->fetchAssoc( $rset ) )
1056  {
1057  $row['active_users'] = $this->listUsersInPrivateRoom($row['id']);
1058  $row['owner'] = $row['owner'];
1059  $rooms[$row['proom_id']] = $row;
1060  }
1061 
1062  return $rooms;
1063  }
1064 
1065  public function listUsersInPrivateRoom($private_room_id) {
1066  global $ilDB;
1067 
1068  $query = 'SELECT user_id FROM ' . self::$privateSessionsTable . ' WHERE proom_id = %s AND disconnected = 0 OR disconnected IS NULL';
1069  $types = array('integer');
1070  $values = array($private_room_id);
1071  $rset = $ilDB->queryF( $query, $types, $values );
1072 
1073  $users = array();
1074 
1075  while ($row = $ilDB->fetchAssoc($rset)) {
1076  $users[] = $row['user_id'];
1077  }
1078 
1079  return $users;
1080  }
1081 
1082  public function userIsInPrivateRoom($room_id, $user_id)
1083  {
1084  global $ilDB;
1085 
1086  $query = 'SELECT proom_id id FROM ' . self::$privateSessionsTable . ' WHERE user_id = %s AND proom_id = %s AND disconnected = 0 OR disconnected IS NULL';
1087  $types = array('integer', 'integer');
1088  $values = array($user_id, $room_id);
1089  $rset = $ilDB->queryF( $query, $types, $values );
1090  if ($ilDB->fetchAssoc($rset))
1091  return true;
1092  return false;
1093  }
1094 
1095  public function subscribeUserToPrivateRoom($room_id, $user_id)
1096  {
1097  global $ilDB;
1098 
1099  if (!$this->userIsInPrivateRoom($room_id, $user_id)) {
1100  $ilDB->insert(
1101  self::$privateSessionsTable,
1102  array(
1103  'proom_id' => array('integer', $room_id),
1104  'user_id' => array('integer', $user_id),
1105  'connected' => array('integer', time()),
1106  'disconnected' => array('integer', 0),
1107  )
1108  );
1109  }
1110  }
1111 
1118  public function unsubscribeUserFromPrivateRoom($room_id, $user_id)
1119  {
1120  global $ilDB;
1121 
1122  $ilDB->update(
1123  self::$privateSessionsTable,
1124  array(
1125  'disconnected' => array('integer', time())
1126  ),
1127  array(
1128  'proom_id' => array('integer', $room_id),
1129  'user_id' => array('integer', $user_id),
1130  )
1131  );
1132  }
1133 
1134  public function countActiveUsers() {
1135  global $ilDB;
1136 
1137  $query = 'SELECT count(user_id) as cnt FROM ' . self::$userTable .
1138  ' WHERE room_id = %s';
1139 
1140  $types = array('integer');
1141  $values = array($this->roomId);
1142  $rset = $ilDB->queryF( $query, $types, $values );
1143 
1144  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
1145  return $row['cnt'];
1146 
1147  return 0;
1148  }
1149 
1150  public function getUniquePrivateRoomTitle($title) {
1151  global $ilDB;
1152 
1153  $query = 'SELECT title FROM ' . self::$privateRoomsTable . ' WHERE parent_id = %s and closed = 0';
1154  $rset = $ilDB->queryF($query, array('integer'), array($this->roomId));
1155 
1156  $titles = array();
1157 
1158  while($row = $ilDB->fetchAssoc($rset)) {
1159  $titles[] = $row['title'];
1160  }
1161 
1162  $suffix = '';
1163  $i = 0;
1164  do {
1165  if(!in_array($title . $suffix, $titles)) {
1166  $title .= $suffix;
1167  break;
1168  }
1169 
1170  ++$i;
1171 
1172  $suffix = ' (' . $i . ')';
1173  } while(true);
1174 
1175  return $title;
1176  }
1177 
1178  public static function findDeletablePrivateRooms() {
1179  global $ilDB;
1180 
1181  $query = '
1182  SELECT private_rooms.proom_id id, MIN(disconnected) min_disconnected, MAX(disconnected) max_disconnected
1183  FROM ' . self::$privateSessionsTable . ' private_sessions
1184  INNER JOIN '.self::$privateRoomsTable.' private_rooms
1185  ON private_sessions.proom_id = private_rooms.proom_id
1186  WHERE closed = 0
1187  GROUP BY private_rooms.proom_id
1188  HAVING MIN(disconnected) > 0 AND MAX(disconnected) < %s';
1189  $rset = $ilDB->queryF(
1190  $query,
1191  array('integer'),
1192  array(time() + 60 * 5)
1193  );
1194 
1195  $rooms = array();
1196 
1197  while ($row = $ilDB->fetchAssoc($rset)) {
1198  $rooms[$row['id']] = $row['id'];
1199  }
1200 
1201  $query = 'SELECT DISTINCT proom_id, room_id, object_id FROM ' . self::$privateRoomsTable
1202  . ' INNER JOIN ' . self::$settingsTable . ' ON parent_id = room_id '
1203  . ' WHERE ' . $ilDB->in('proom_id', $rooms, false, 'integer');
1204 
1205  $rset = $ilDB->query($query);
1206  $rooms = array();
1207  while($row = $ilDB->fetchAssoc($rset)) {
1208  $rooms[] = array(
1209  'proom_id' => $row['proom_id'],
1210  'room_id' => $row['room_id'],
1211  'object_id' => $row['object_id']
1212  );
1213  }
1214 
1215  return $rooms;
1216  }
1217 
1226  public function getAllRooms($user_id)
1227  {
1231  global $ilDB;
1232 
1233  $query = "
1234  SELECT room_id, od.title
1235  FROM object_data od
1236 
1237  INNER JOIN " . self::$settingsTable . "
1238  ON object_id = od.obj_id
1239 
1240  INNER JOIN " . self::$privateRoomsTable . " prt
1241  ON prt.owner = %s
1242 
1243  WHERE od.type = 'chtr'
1244  ";
1245 
1246  $types = array('integer');
1247  $values = array($user_id);
1248 
1249  $res = $ilDB->queryF($query, $types, $values);
1250 
1251  $rooms = array();
1252 
1253  while($row = $ilDB->fetchAssoc($res))
1254  {
1255  $room_id = $row['room_id'];
1256  $rooms[$room_id] = $row['title'];
1257  }
1258 
1259  return $rooms;
1260  }
1261 
1262 
1263 
1264  public function getPrivateSubRooms($parent_room, $user_id)
1265  {
1266  global $ilDB;
1267 
1268  $query = "
1269  SELECT proom_id, parent_id
1270  FROM chatroom_prooms
1271  WHERE parent_id = %s
1272  AND owner = %s
1273  AND closed = 0
1274  ";
1275 
1276  $types = array( 'integer', 'integer' );
1277  $values = array( $parent_room, $user_id );
1278 
1279  $res = $ilDB->queryF( $query, $types, $values );
1280 
1281  $priv_rooms = array();
1282 
1283  while( $row = $ilDB->fetchAssoc( $res ) )
1284  {
1285  $proom_id = $row['proom_id'];
1286  $priv_rooms[$proom_id] = $row['parent_id'];
1287  }
1288 
1289  return $priv_rooms;
1290  }
1291 
1299  public function getRefIdByRoomId($room_id)
1300  {
1301  global $ilDB;
1302 
1303  $query = "
1304  SELECT objr.ref_id
1305  FROM object_reference objr
1306 
1307  INNER JOIN chatroom_settings cs
1308  ON cs.object_id = objr.obj_id
1309 
1310  INNER JOIN object_data od
1311  ON od.obj_id = cs.object_id
1312 
1313  WHERE cs.room_id = %s
1314  ";
1315 
1316  $types = array( 'integer' );
1317  $values = array( $room_id );
1318 
1319  $res = $ilDB->queryF( $query, $types, $values );
1320 
1321  $row = $ilDB->fetchAssoc( $res );
1322 
1323  return $row['ref_id'];
1324  }
1325 
1326 public function getLastMessages($number, $chatuser = null) {
1327  global $ilDB;
1328 
1329  // There is currently no way to check if a message is private or not
1330  // by sql. So we fetch twice as much as we need and hope that there
1331  // are not more than $number private messages.
1332  $ilDB->setLimit($number * 2);
1333  $rset = $ilDB->queryF('SELECT * FROM ' . self::$historyTable . ' WHERE room_id = %s AND sub_room = 0 ORDER BY timestamp DESC', array('integer'), array($this->roomId));
1334 
1335  $result_count = 0;
1336  $results = array();
1337  while(($row = $ilDB->fetchAssoc($rset)) && $result_count < $number) {
1338  $tmp = json_decode($row['message']);
1339  if ($chatuser !== null && $tmp->public == 0 && $tmp->recipients) {
1340  if (in_array($chatuser->getUserId(), explode(',',$tmp->recipients))) {
1341  $results[] = $tmp;
1342  ++$result_count;
1343  }
1344  }
1345  else if ($tmp->public == 1) {
1346  $results[] = $tmp;
1347  ++$result_count;
1348  }
1349 
1350  }
1351  return $results;
1352  }
1353 
1354  public function getLastMessagesForChatViewer($number, $chatuser = null)
1355  {
1359  global $ilDB;
1360 
1361  $ilDB->setLimit($number);
1362  $rset = $ilDB->query(
1363  'SELECT *
1364  FROM ' . self::$historyTable . '
1365  WHERE room_id = '.$ilDB->quote($this->roomId, 'integer').'
1366  AND sub_room = 0
1367  AND (
1368  (' . $ilDB->like('message', 'text', '%"type":"message"%') . ' AND ' . $ilDB->like('message', 'text', '%"public":1%') . ' AND ' . $ilDB->like('message', 'text', '%"recipients":null%') . ')
1369  OR
1370  ' . $ilDB->like('message', 'text', '%"type":"%connected"%') . ')
1371  ORDER BY timestamp DESC'
1372  );
1373 
1374  $results = array();
1375  while(($row = $ilDB->fetchAssoc($rset)))
1376  {
1377  $tmp = json_decode($row['message']);
1378  if($tmp->type != 'message' && $row['timestamp'] && !is_numeric($tmp->timestamp))
1379  {
1380  $tmp->timestamp = $row['timestamp'] * 1000;
1381  }
1382  $results[] = $tmp;
1383  }
1384  return $results;
1385  }
1386 
1387  public function clearMessages($sub_room) {
1388  global $ilDB;
1389 
1390  $ilDB->queryF(
1391  'DELETE FROM ' . self::$historyTable . ' WHERE room_id = %s AND sub_room = %s',
1392  array('integer', 'integer'),
1393  array($this->roomId, (int)$sub_room)
1394  );
1395 
1396  if ($sub_room) {
1397  $ilDB->queryF(
1398  'DELETE FROM ' . self::$sessionTable . ' WHERE proom_id = %s AND disconnected < %s',
1399  array('integer', 'integer'),
1400  array($sub_room, time())
1401  );
1402  }
1403  else {
1404  $ilDB->queryF(
1405  'DELETE FROM ' . self::$sessionTable . ' WHERE room_id = %s AND disconnected < %s',
1406  array('integer', 'integer'),
1407  array($this->roomId, time())
1408  );
1409  }
1410  }
1411 
1412  public static function getUntrashedChatReferences()
1413  {
1417  global $ilDB;
1418 
1419  // Check for parent because of an invalid parent node for the old public chat (thx @ jposselt ;-)).
1420  // We cannot find this old public chat and clean this automatically
1421  $query = '
1422  SELECT od.obj_id, od.title, ore.ref_id, od.type, odp.title parent_title
1423  FROM object_data od
1424  INNER JOIN object_reference ore ON ore.obj_id = od.obj_id
1425  INNER JOIN tree t ON t.child = ore.ref_id
1426  INNER JOIN tree p ON p.child = t.parent
1427  INNER JOIN object_reference orep ON orep.ref_id = p.child
1428  INNER JOIN object_data odp ON odp.obj_id = orep.obj_id
1429  INNER JOIN object_reference pre ON pre.ref_id = t.parent
1430  INNER JOIN object_data pod ON pod.obj_id = pre.obj_id
1431  WHERE od.type = %s AND t.tree > 0 AND ore.deleted IS NULL
1432  ORDER BY od.title
1433  ';
1434  $res = $ilDB->queryF($query, array('text'), array('chtr'));
1435 
1436  $chats = array();
1437  while($row = $ilDB->fetchAssoc($res))
1438  {
1439  $chats[] = $row;
1440  }
1441 
1442  return $chats;
1443  }
1444 }
1445 
1446 ?>