ILIAS  Release_4_2_x_branch Revision 61807
 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  {
208  global $ilDB;
209 
210  $userdata = array(
211  'login' => $user->getUsername(),
212  'id' => $user->getUserId()
213  );
214 
215  $query = 'SELECT user_id FROM ' . self::$userTable .
216  ' WHERE room_id = %s AND user_id = %s';
217 
218  $types = array('integer', 'integer');
219  $values = array($this->roomId, $user->getUserId());
220 
221  if( !$ilDB->fetchAssoc( $ilDB->queryF( $query, $types, $values ) ) )
222  {
223  $ilDB->insert(
224  self::$userTable,
225  array(
226  'room_id' => array('integer', $this->roomId),
227  'user_id' => array('integer', $user->getUserId()),
228  'userdata' => array('text', json_encode( $userdata )),
229  'connected' => array('integer', time()),
230  )
231  );
232 
233  return true;
234  }
235 
236  return false;
237  }
238 
248  public function getConnectedUsers()
249  {
250  global $ilDB;
251 
252  $query = 'SELECT userdata FROM ' . self::$userTable . ' WHERE room_id = %s';
253  $types = array('integer');
254  $values = array($this->roomId);
255  $rset = $ilDB->queryF( $query, $types, $values );
256  $users = array();
257 
258  while( $row = $ilDB->fetchAssoc( $rset ) )
259  {
260  $users[] = json_decode( $row['userdata'] );
261  }
262 
263  return $users;
264  }
265 
272  public function disconnectUser(ilObjUser $user)
273  {
274  $this->disconnectUsers( array($user->getId()) );
275  }
276 
286  public function disconnectUsers(array $userIds)
287  {
288  global $ilDB;
289 
290  $query = 'SELECT * FROM ' . self::$userTable . ' WHERE room_id = %s AND ' .
291  $ilDB->in( 'user_id', $userIds, false, 'integer' );
292 
293  $types = array('integer');
294  $values = array($this->roomId);
295  $rset = $ilDB->queryF( $query, $types, $values );
296 
297  if( $row = $ilDB->fetchAssoc( $rset ) )
298  {
299  $query = 'SELECT proom_id FROM ' . self::$privateRoomsTable . ' WHERE parent_id = %s';
300  $rset_prooms = $ilDB->queryF($query, array('integer'), array($this->roomId));
301 
302  $prooms = array();
303 
304  while($row_prooms = $ilDB->fetchAssoc($rset_prooms)) {
305  $prooms[] = $row_prooms['proom_id'];
306  }
307 
308  if (true || $this->getSetting( 'enable_history' )) {
309  $query = 'UPDATE ' . self::$privateSessionsTable . ' SET disconnected = %s WHERE ' . $ilDB->in('user_id', $userIds, false, 'integer') . ' AND ' . $ilDB->in('proom_id', $prooms, false, 'integer');
310  $ilDB->manipulateF($query, array('integer'), array(time()));
311  }
312  else {
313  $query = 'DELETE FROM ' . self::$privateSessionsTable . ' WHERE ' . $ilDB->in('user_id', $userIds, false, 'integer') . ' AND ' . $ilDB->in('proom_id', $prooms, false, 'integer');
314  $ilDB->manipulate($query);
315  }
316 
317  $query = 'DELETE FROM ' . self::$userTable . ' WHERE room_id = %s AND ' .
318  $ilDB->in( 'user_id', $userIds, false, 'integer' );
319 
320  $types = array('integer');
321  $values = array($this->roomId);
322  $ilDB->manipulateF( $query, $types, $values );
323 
324  do
325  {
326  if ($this->getSetting( 'enable_history' )) {
327  $ilDB->insert(
328  self::$sessionTable,
329  array(
330  'room_id' => array('integer', $this->roomId),
331  'user_id' => array('integer', $row['user_id']),
332  'userdata' => array('text', $row['userdata']),
333  'connected' => array('integer', $row['connected']),
334  'disconnected' => array('integer', time()),
335  )
336  );
337  }
338  }
339  while( $row = $ilDB->fetchAssoc( $rset ) );
340  }
341 
342  }
343 
344  private function phpTypeToMDBType($type) {
345  switch($type) {
346  case 'string':
347  return 'text';
348  default:
349  return $type;
350  }
351 
352  }
353 
360  public function saveSettings(array $settings)
361  {
362  global $ilDB;
363 
364  $localSettings = array();
365 
366  foreach( $this->availableSettings as $setting => $type )
367  {
368  if( isset( $settings[$setting] ) ) {
369  if ($type == 'boolean') {
370  $settings[$setting] = (boolean)$settings[$setting];
371  }
372  $localSettings[$setting] = array($this->phpTypeToMDBType($type), $settings[$setting]);
373  }
374  }
375 
376  if (!$localSettings['room_type'][1]) {
377  $localSettings['room_type'][1] = 'repository';
378  }
379 
380  if( $this->roomId )
381  {
382  $ilDB->update(
383  self::$settingsTable,
384  $localSettings,
385  array( 'room_id' => array('integer', $this->roomId) )
386  );
387  }
388  else
389  {
390  $this->roomId = $ilDB->nextId( self::$settingsTable );
391 
392  $localSettings['room_id'] = array(
393  $this->availableSettings['room_id'], $this->roomId
394  );
395 
396  $ilDB->insert( self::$settingsTable, $localSettings );
397  }
398  }
399 
405  public function getSettings()
406  {
407  return $this->settings;
408  }
409 
417  public static function byObjectId($object_id)
418  {
419  global $ilDB;
420  $query = 'SELECT * FROM ' . self::$settingsTable . ' WHERE object_id = %s';
421  $types = array('integer');
422  $values = array($object_id);
423  $rset = $ilDB->queryF( $query, $types, $values );
424 
425  if( $row = $ilDB->fetchAssoc( $rset ) )
426  {
427  $room = new self();
428  $room->initialize( $row );
429  return $room;
430  }
431  }
432 
440  public static function byRoomId($room_id, $initObject = false)
441  {
442  global $ilDB;
443 
444  $query = 'SELECT * FROM ' . self::$settingsTable . ' WHERE room_id = %s';
445 
446  $types = array('integer');
447  $values = array($room_id);
448 
449  $rset = $ilDB->queryF( $query, $types, $values );
450 
451  if( $row = $ilDB->fetchAssoc( $rset ) )
452  {
453  $room = new self();
454  $room->initialize( $row );
455 
456  if ($initObject) {
457  $room->object = ilObjectFactory::getInstanceByObjId($row['object_id']);
458  }
459 
460  return $room;
461  }
462  }
463 
470  public function initialize(array $rowdata)
471  {
472  $this->roomId = $rowdata['room_id'];
473 
474  foreach( $this->availableSettings as $setting => $type )
475  {
476  if( isset($rowdata[$setting]) )
477  {
478  settype($rowdata[$setting], $this->availableSettings[$setting]);
479  $this->setSetting( $setting, $rowdata[$setting] );
480  }
481  }
482  }
483 
489  public function getRoomId()
490  {
491  return $this->roomId;
492  }
493 
502  public function isSubscribed($chat_userid)
503  {
504  global $ilDB;
505 
506  $query = 'SELECT count(user_id) as cnt FROM ' . self::$userTable .
507  ' WHERE room_id = %s AND user_id = %s';
508 
509  $types = array('integer', 'integer');
510  $values = array($this->roomId, $chat_userid);
511  $rset = $ilDB->queryF( $query, $types, $values );
512 
513  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
514  return true;
515 
516  return false;
517  }
518 
519  public function isAllowedToEnterPrivateRoom($chat_userid, $proom_id) {
520  //echo call_user_func_array('sprintf', array_merge(array($query), $values));
521  global $ilDB;
522 
523  $query = 'SELECT count(user_id) cnt FROM ' . self::$privateRoomsAccessTable .
524  ' WHERE proom_id = %s AND user_id = %s';
525 
526  $types = array('integer', 'integer');
527  $values = array($proom_id, $chat_userid);
528  $rset = $ilDB->queryF( $query, $types, $values );
529 
530  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
531  return true;
532 
533  $query = 'SELECT count(*) cnt FROM ' . self::$privateRoomsTable .
534  ' WHERE proom_id = %s AND owner = %s';
535 
536  $types = array('integer', 'integer');
537  $values = array($proom_id, $chat_userid);
538  $rset = $ilDB->queryF( $query, $types, $values );
539 
540  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] == 1 )
541  return true;
542 
543  return false;
544  }
545 
551  public function disconnectAllUsersFromAllRooms()
552  {
553  global $ilDB;
554 
555  $ilDB->manipulate( 'DELETE FROM ' . self::$userTable );
556  $ilDB->manipulate( 'UPDATE ' . self::$privateRoomsTable . ' SET closed = ' . $ilDB->quote( time() ,'integer') . ' WHERE closed = 0 OR closed IS NULL');
557  $ilDB->manipulate( 'UPDATE ' . self::$privateSessionsTable . ' SET disconnected = ' . $ilDB->quote( time() ,'integer') . ' WHERE disconnected = 0 OR disconnected IS NULL');
561  }
562 
573  public function getHistory(ilDateTime $from = null, ilDateTime $to = null, $restricted_session_userid = null, $proom_id = 0)
574  {
575  global $ilDB, $ilUser;
576 
577  $join = '';
578 
579  if ($proom_id) {
580  $join .= ' INNER JOIN ' . self::$privateSessionsTable .
581  ' pSessionTable ON pSessionTable.user_id = ' .
582  $ilDB->quote( $restricted_session_userid, 'integer' ) .
583  ' AND historyTable.sub_room = pSessionTable.proom_id AND timestamp >= pSessionTable.connected AND timestamp <= pSessionTable.disconnected ';
584  }
585 
586  $query = 'SELECT historyTable.* FROM ' . self::$historyTable . ' historyTable ' .
587  $join . ' WHERE historyTable.room_id = ' . $this->getRoomId();
588 
589  $filter = array();
590 
591  if( $from != null )
592  {
593  $filter[] = 'timestamp >= ' . $ilDB->quote( $from->getUnixTime(), 'integer' );
594  }
595 
596  if( $to != null )
597  {
598  $filter[] = 'timestamp <= ' . $ilDB->quote( $to->getUnixTime(), 'integer' );
599  }
600 
601  if( $filter )
602  $query .= ' AND ' . join( ' AND ', $filter );
603  $query .= ' ORDER BY timestamp ASC';
604 
605  $rset = $ilDB->query( $query );
606  $result = array();
607 
608  while( $row = $ilDB->fetchAssoc( $rset ) )
609  {
610  $row['message'] = json_decode( $row['message'] );
611  $row['message']->timestamp = $row['timestamp'];
612  if ($row['message']->public !== null && !$row['message']->public && !in_array($ilUser->getId(), explode(',', $row['recipients']))) {
613  continue;
614  }
615 
616  $result[] = $row;
617  }
618  return $result;
619  }
620 
621  public function getPrivateRoomSessions(ilDateTime $from = null, ilDateTime $to = null, $user_id = 0, $room_id=0 ) {
622  global $ilDB;
623 
624  $query = 'SELECT proom_id, title FROM ' . self::$privateRoomsTable . ' WHERE proom_id IN (
625  SELECT proom_id FROM '.self::$privateSessionsTable.' WHERE connected >= %s AND disconnected <= %s AND user_id = %s
626 
627  ) AND parent_id = %s';
628 
629  $rset = $ilDB->queryF($query, array('integer','integer','integer','integer'), array($from->getUnixTime(), $to->getUnixTime(), $user_id, $room_id));
630  $result = array();
631  while( $row = $ilDB->fetchAssoc( $rset ) )
632  {
633  $result[] = $row;
634  }
635  return $result;
636  }
637 
646  public function saveFileUploadToDb($user_id, $filename, $type)
647  {
648  global $ilDB;
649 
650  $upload_id = $ilDB->nextId( self::$uploadTable );
651 
652  $ilDB->insert(
653  self::$uploadTable,
654  array(
655  'upload_id' => array('integer', $upload_id),
656  'room_id' => array('integer', $this->roomId),
657  'user_id' => array('integer', $user_id),
658  'filename' => array('text', $filename),
659  'filetype' => array('text', $type),
660  'timestamp' => array('integer', time())
661  )
662  );
663  }
664 
672  public function banUser($user_id, $comment = '')
673  {
674  global $ilDB;
675 
676  $ilDB->insert(
677  self::$banTable,
678  array(
679  'room_id' => array('integer', $this->roomId),
680  'user_id' => array('integer', $user_id),
681  'timestamp' => array('integer', time()),
682  'remark' => array('text', $comment),
683  )
684  );
685  }
686 
695  public function unbanUser($user_id)
696  {
697  global $ilDB;
698 
699  if( !is_array( $user_id ) )
700  $user_id = array($user_id);
701 
702  $query = 'DELETE FROM ' . self::$banTable . ' WHERE room_id = %s AND ' .
703  $ilDB->in( 'user_id', $user_id, false, 'integer' );
704 
705  $types = array('integer');
706  $values = array($this->getRoomId());
707 
708  return $ilDB->manipulateF( $query, $types, $values );
709  }
710 
719  public function isUserBanned($user_id)
720  {
721  global $ilDB;
722 
723  $query = 'SELECT count(user_id) cnt FROM ' . self::$banTable .
724  ' WHERE user_id = %s AND room_id = %s';
725 
726  $types = array('integer', 'integer');
727  $values = array($user_id, $this->getRoomId());
728 
729  $rset = $ilDB->queryF( $query, $types, $values );
730 
731  if( $rset && ($row = $ilDB->fetchAssoc( $rset )) && $row['cnt'] )
732  return true;
733 
734  return false;
735  }
736 
744  public function getBannedUsers()
745  {
746  global $ilDB;
747 
748  $query = 'SELECT * FROM ' . self::$banTable . ' WHERE room_id = %s ';
749  $types = array('integer');
750  $values = array($this->getRoomId());
751  $rset = $ilDB->queryF( $query, $types, $values );
752  $result = array();
753 
754  if( $rset )
755  {
756  while( $row = $ilDB->fetchAssoc( $rset ) )
757  {
758  if( $row['user_id'] > 0 )
759  {
760  $user = new ilObjUser( $row['user_id'] );
761  $userdata = array(
762  'user_id' => $user->getId(),
763  'firstname' => $user->getFirstname(),
764  'lastname' => $user->getLastname(),
765  'login' => $user->getLogin(),
766  'remark' => $row['remark']
767  );
768 
769  $result[] = $userdata;
770  }
771  else
772  {
773  //@todo anonymous user
774  }
775  }
776  }
777 
778  return $result;
779  }
780 
791  public function getLastSession(ilChatroomUser $user)
792  {
793  global $ilDB;
794 
795  $query = 'SELECT * FROM ' . self::$sessionTable . ' WHERE user_id = ' .
796  $ilDB->quote( $user->getUserId(), 'integer' ) .
797  ' ORDER BY connected DESC';
798 
799  $ilDB->setLimit( 1 );
800  $rset = $ilDB->query( $query );
801 
802  if( $row = $ilDB->fetchAssoc( $rset ) )
803  {
804  return $row;
805  }
806  }
807 
818  public function getSessions(ilChatroomUser $user)
819  {
820  global $ilDB;
821 
822  $query = 'SELECT * FROM ' . self::$sessionTable . ' WHERE user_id = ' .
823  $ilDB->quote( $user->getUserId(), 'integer' ) . ' AND room_id = '.
824  $ilDB->quote( $this->getRoomId(), 'integer' ) .
825  ' ORDER BY connected DESC';
826 
827  $rset = $ilDB->query( $query );
828 
829  $result = array();
830 
831  while( $row = $ilDB->fetchAssoc( $rset ) )
832  {
833  $result[] = $row;
834  }
835 
836  return $result;
837  }
838 
839  public function addPrivateRoom($title, ilChatroomUser $owner, $settings)
840  {
841  global $ilDB;
842 
843  $nextId = $ilDB->nextId('chatroom_prooms');
844 
845  $ilDB->insert(
846  self::$privateRoomsTable,
847  array(
848  'proom_id' => array('integer', $nextId),
849  'parent_id' => array('integer', $this->roomId),
850  'title' => array('text', $title),
851  'owner' => array('integer', $owner->getUserId()),
852  'created' => array('integer', time()),
853  'is_public' => array('integer', $settings['public']),
854  )
855  );
856 
857  return $nextId;
858  }
859 
860  public function closePrivateRoom($id)
861  {
862  global $ilDB;
863 
864  $ilDB->manipulateF(
865  'UPDATE ' . self::$privateRoomsTable . ' SET closed = %s WHERE proom_id = %s',
866  array('integer', 'integer'),
867  array(time(), $id)
868  );
869  }
870 
871  public function isOwnerOfPrivateRoom($user_id, $proom_id) {
872  global $ilDB;
873 
874  $query = 'SELECT proom_id FROM ' . self::$privateRoomsTable . ' WHERE proom_id = %s AND owner = %s';
875  $types = array('integer', 'integer');
876  $values = array($proom_id, $user_id);
877 
878  $rset = $ilDB->queryF($query, $types, $values);
879 
880  if ($rset && $ilDB->fetchAssoc($rset)) {
881  return true;
882  }
883  return false;
884  }
885 
886  public function inviteUserToPrivateRoom($user_id, $proom_id) {
887  global $ilDB;
888 
889  $query = 'DELETE FROM ' . self::$privateRoomsAccessTable . ' WHERE user_id = %s AND proom_id = %s';
890  $types = array('integer', 'integer');
891  $values = array($user_id, $proom_id);
892 
893  $ilDB->manipulateF($query, $types, $values);
894 
895  $ilDB->insert(self::$privateRoomsAccessTable, array(
896  'user_id' => array('integer', $user_id),
897  'proom_id' => array('integer', $proom_id)
898  ));
899 /*
900  $ilDB->insert(self::$privateSessionsTable, array(
901  'user_id' => array('integer', $user_id),
902  'proom_id' => array('integer', $proom_id),
903  'connected' => array('integer', time()),
904  'disconnected' => array('integer', 0),
905  ));
906  */
907  }
908 
916  public function getChatURL($gui, $scope_id = 0)
917  {
918  include_once '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  {
1217  global $ilDB;
1218 
1219  $query = "
1220  SELECT room_id, od.title
1221  FROM object_data od
1222 
1223  INNER JOIN " . self::$settingsTable . "
1224  ON object_id = od.obj_id
1225 
1226  INNER JOIN " . self::$privateRoomsTable . " prt
1227  ON prt.owner = %s
1228 
1229  WHERE od.type = 'chtr'
1230  ";
1231 
1232  $types = array( 'integer' );
1233  $values = array( $user_id );
1234 
1235  $res = $ilDB->queryF( $query, $types, $values );
1236 
1237  $rooms = array();
1238 
1239  while( $row = $ilDB->fetchAssoc( $res ) )
1240  {
1241  $room_id = $row['room_id'];
1242  $rooms[$room_id] = $row['title'];
1243  }
1244 
1245  return $rooms;
1246  }
1247 
1248 
1249 
1250  public function getPrivateSubRooms($parent_room, $user_id)
1251  {
1252  global $ilDB;
1253 
1254  $query = "
1255  SELECT proom_id, parent_id
1256  FROM chatroom_prooms
1257  WHERE parent_id = %s
1258  AND owner = %s
1259  AND closed = 0
1260  ";
1261 
1262  $types = array( 'integer', 'integer' );
1263  $values = array( $parent_room, $user_id );
1264 
1265  $res = $ilDB->queryF( $query, $types, $values );
1266 
1267  $priv_rooms = array();
1268 
1269  while( $row = $ilDB->fetchAssoc( $res ) )
1270  {
1271  $proom_id = $row['proom_id'];
1272  $priv_rooms[$proom_id] = $row['parent_id'];
1273  }
1274 
1275  return $priv_rooms;
1276  }
1277 
1285  public function getRefIdByRoomId($room_id)
1286  {
1287  global $ilDB;
1288 
1289  $query = "
1290  SELECT objr.ref_id
1291  FROM object_reference objr
1292 
1293  INNER JOIN chatroom_settings cs
1294  ON cs.object_id = objr.obj_id
1295 
1296  INNER JOIN object_data od
1297  ON od.obj_id = cs.object_id
1298 
1299  WHERE cs.room_id = %s
1300  ";
1301 
1302  $types = array( 'integer' );
1303  $values = array( $room_id );
1304 
1305  $res = $ilDB->queryF( $query, $types, $values );
1306 
1307  $row = $ilDB->fetchAssoc( $res );
1308 
1309  return $row['ref_id'];
1310  }
1311 
1312  public function getLastMessages($number, $chatuser = null) {
1313  global $ilDB;
1314 
1315  // There is currently no way to check if a message is private or not
1316  // by sql. So we fetch twice as much as we need and hope that there
1317  // are not more than $number private messages.
1318  $ilDB->setLimit($number * 2);
1319  $rset = $ilDB->queryF('SELECT * FROM ' . self::$historyTable . ' WHERE room_id = %s AND sub_room = 0 ORDER BY timestamp DESC', array('integer'), array($this->roomId));
1320 
1321  $result_count = 0;
1322  $results = array();
1323  while(($row = $ilDB->fetchAssoc($rset)) && $result_count < $number) {
1324  $tmp = json_decode($row['message']);
1325  if ($chatuser !== null && $tmp->public == 0 && $tmp->recipients) {
1326  if (in_array($chatuser->getUserId(), explode(',',$tmp->recipients))) {
1327  $results[] = $tmp;
1328  ++$result_count;
1329  }
1330  }
1331  else if ($tmp->public == 1) {
1332  $results[] = $tmp;
1333  ++$result_count;
1334  }
1335 
1336  }
1337  return $results;
1338  }
1339 
1340  public function getLastMessagesForChatViewer($number, $chatuser = null)
1341  {
1345  global $ilDB;
1346 
1347  $ilDB->setLimit($number);
1348  $rset = $ilDB->query(
1349  'SELECT *
1350  FROM ' . self::$historyTable . '
1351  WHERE room_id = '.$ilDB->quote($this->roomId, 'integer').'
1352  AND sub_room = 0
1353  AND (
1354  (' . $ilDB->like('message', 'text', '%"type":"message"%') . ' AND ' . $ilDB->like('message', 'text', '%"public":1%') . ' AND ' . $ilDB->like('message', 'text', '%"recipients":null%') . ')
1355  OR
1356  ' . $ilDB->like('message', 'text', '%"type":"%connected"%') . ')
1357  ORDER BY timestamp DESC'
1358  );
1359 
1360  $results = array();
1361  while(($row = $ilDB->fetchAssoc($rset)))
1362  {
1363  $tmp = json_decode($row['message']);
1364  $results[] = $tmp;
1365  }
1366  return $results;
1367  }
1368 
1369  public function clearMessages($sub_room) {
1370  global $ilDB;
1371 
1372  $ilDB->queryF(
1373  'DELETE FROM ' . self::$historyTable . ' WHERE room_id = %s AND sub_room = %s',
1374  array('integer', 'integer'),
1375  array($this->roomId, (int)$sub_room)
1376  );
1377 
1378  if ($sub_room) {
1379  $ilDB->queryF(
1380  'DELETE FROM ' . self::$sessionTable . ' WHERE proom_id = %s AND disconnected < %s',
1381  array('integer', 'integer'),
1382  array($sub_room, time())
1383  );
1384  }
1385  else {
1386  $ilDB->queryF(
1387  'DELETE FROM ' . self::$sessionTable . ' WHERE room_id = %s AND disconnected < %s',
1388  array('integer', 'integer'),
1389  array($this->roomId, time())
1390  );
1391  }
1392  }
1393 
1394  public static function getUntrashedChatReferences()
1395  {
1399  global $ilDB;
1400 
1401  // Check for parent because of an invalid parent node for the old public chat (thx @ jposselt ;-)).
1402  // We cannot find this old public chat and clean this automatically
1403  $query = '
1404  SELECT od.obj_id, od.title, ore.ref_id, od.type
1405  FROM object_data od
1406  INNER JOIN object_reference ore ON ore.obj_id = od.obj_id
1407  INNER JOIN tree t ON t.child = ore.ref_id
1408  INNER JOIN object_reference pre ON pre.ref_id = t.parent
1409  INNER JOIN object_data pod ON pod.obj_id = pre.obj_id
1410  WHERE od.type = %s AND t.tree > 0 AND ore.deleted IS NULL
1411  ORDER BY od.title
1412  ';
1413  $res = $ilDB->queryF($query, array('text'), array('chtr'));
1414 
1415  $chats = array();
1416  while($row = $ilDB->fetchAssoc($res))
1417  {
1418  $chats[] = $row;
1419  }
1420 
1421  return $chats;
1422  }
1423 }
1424 
1425 ?>