/home/edulekha/studygroup.edulekha.com/ow_plugins/mailbox/bol/conversation_dao.php
<?php
/**
* This software is intended for use with Oxwall Free Community Software http://www.oxwall.org/ and is
* licensed under The BSD license.
* ---
* Copyright (c) 2011, Oxwall Foundation
* All rights reserved.
* Redistribution and use in source and binary forms, with or without modification, are permitted provided that the
* following conditions are met:
*
* - Redistributions of source code must retain the above copyright notice, this list of conditions and
* the following disclaimer.
*
* - Redistributions in binary form must reproduce the above copyright notice, this list of conditions and
* the following disclaimer in the documentation and/or other materials provided with the distribution.
*
* - Neither the name of the Oxwall Foundation nor the names of its contributors may be used to endorse or promote products
* derived from this software without specific prior written permission.
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES,
* INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
* INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
* AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
/**
* Data Access Object for `mailbox_conversation` table.
*
* @author Podyachev Evgeny <joker.OW2@gmail.com>
* @package ow_plugin.mailbox.bol
* @since 1.0
*/
class MAILBOX_BOL_ConversationDao extends OW_BaseDao
{
const READ_NONE = 0;
const READ_INITIATOR = 1;
const READ_INTERLOCUTOR = 2;
const READ_ALL = 3;
const DELETED_NONE = 0;
const DELETED_INITIATOR = 1;
const DELETED_INTERLOCUTOR = 2;
const DELETED_ALL = 3;
const VIEW_NONE = 0;
const VIEW_INITIATOR = 1;
const VIEW_INTERLOCUTOR = 2;
const VIEW_ALL = 3;
const CACHE_LIFE_TIME = 86400;
const CACHE_TAG_USER_CONVERSATION_COUNT = 'mailbox_conversation_count_user_id_';
const CACHE_TAG_USER_NEW_CONVERSATION_COUNT = 'mailbox_new_conversation_count_user_id_';
const CHAT_CONVERSATION_SUBJECT = 'mailbox_chat_conversation';
const WINK_CONVERSATION_SUBJECT = 'Wink';
/**
* Class constructor
*
*/
protected function __construct()
{
parent::__construct();
}
/**
* Class instance
*
* @var MAILBOX_BOL_ConversationDao
*/
private static $classInstance;
/**
* Returns class instance
*
* @return MAILBOX_BOL_ConversationDao
*/
public static function getInstance()
{
if ( self::$classInstance === null )
{
self::$classInstance = new self();
}
return self::$classInstance;
}
/**
* @see MAILBOX_BOL_ConversationDao::getDtoClassName()
*
*/
public function getDtoClassName()
{
return 'MAILBOX_BOL_Conversation';
}
/**
* @see MAILBOX_BOL_ConversationDao::getTableName()
*
*/
public function getTableName()
{
return OW_DB_PREFIX . 'mailbox_conversation';
}
/**
* Returns conversations list by $userId
*
* @param int $userId
* @param int $first
* @param int $count
* @return array
*/
public function getConversationListByUserId( $userId, $first, $count )
{
$sql = " SELECT `conv`.* FROM `" . $this->getTableName() . "` AS `conv`
WHERE `conv`.`initiatorId` = :userId LIMIT :start, :count";
return $this->dbo->queryForList($sql, array('userId' => $userId, 'start' => $first, 'count' => $count));
}
public function getAffectedRows()
{
return $this->dbo->getAffectedRows();
}
public function findConversationList( $initiatorId, $interlocutorId )
{
$ex = new OW_Example();
$ex->andFieldEqual('initiatorId', $initiatorId);
$ex->andFieldEqual('interlocutorId', $interlocutorId);
$ex->setOrder('id');
return $this->findListByExample($ex);
}
/**
*
* @param array $userId
* @return array
*/
public function getNewConversationListForConsoleNotificationMailer( $userIdList )
{
if ( empty($userIdList) )
{
return array();
}
$userList = $this->dbo->mergeInClause($userIdList);
$sql = " SELECT `mess`.`id` as messageId, `mess`.*, `conv`.* FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
ON (`last_m`.`conversationId` = `conv`.`id`)
INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `mess`
ON ( `last_m`.`initiatorMessageId` = `mess`.id AND ( `last_m`.`initiatorMessageId` > `last_m`.interlocutorMessageId )
OR `last_m`.`interlocutorMessageId` = `mess`.id AND ( `last_m`.`initiatorMessageId` < `last_m`.interlocutorMessageId ) )
WHERE `conv`.`notificationSent` = 0 AND ( ( `conv`.`initiatorId` IN ( $userList ) AND `last_m`.`interlocutorMessageId` > 0 AND `conv`.`deleted` != " . self::DELETED_INITIATOR . " AND NOT `conv`.`read` & " . (self::READ_INITIATOR) . " AND NOT `conv`.`viewed` & " . (self::VIEW_INITIATOR) . " )
OR ( `conv`.`interlocutorId` IN ( $userList ) AND `conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " AND NOT `conv`.`read` & " . (self::READ_INTERLOCUTOR) . " AND NOT `conv`.`viewed` & " . (self::VIEW_INTERLOCUTOR) . " ) )
";
$conversationList = $this->dbo->queryForList($sql);
$resultList = array();
foreach ( $conversationList as $conversation )
{
$userId = $conversation['recipientId'];
if ($conversation['wasAuthorized'] == 0)
{
$actionName = 'read_message';
if ($conversation['subject'] == 'mailbox_chat_conversation')
{
$actionName = 'read_chat_message';
}
if (!OW::getAuthorization()->isUserAuthorized($userId, 'mailbox', $actionName))
{
$status = BOL_AuthorizationService::getInstance()->getActionStatus('mailbox', $actionName);
$conversation['text'] = $status['msg'];
}
}
else
{
if ($conversation['isSystem'] == 1)
{
$eventParams = json_decode($conversation['text'], true);
$eventParams['params']['messageId'] = $conversation['messageId'];
$eventParams['params']['getPreview'] = true;
$event = new OW_Event($eventParams['entityType'].'.'.$eventParams['eventName'], $eventParams['params']);
OW::getEventManager()->trigger($event);
$data = $event->getData();
if (!empty($data))
{
$conversation['text'] = $data;
}
else
{
$conversation['text'] = OW::getLanguage()->text('mailbox', 'can_not_display_entitytype_message', array('entityType'=>$eventParams['entityType']));
}
}
}
$resultList[$userId][] = $conversation;
}
return $resultList;
}
public function getMailsConversationIdList($userId, $first, $count)
{
$sql = " SELECT `conv`.`id`, MAX(mess.timeStamp) as `messageTimestamp` FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
ON ( `last_m`.`conversationId` = `conv`.`id` )
INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `mess`
ON ( `conv`.`id` = `mess`.conversationId )
WHERE `conv`.`subject` <> '".self::CHAT_CONVERSATION_SUBJECT."' AND ( ( `conv`.`initiatorId` = :user AND `conv`.`deleted` != " . self::DELETED_INITIATOR . " AND ( `mess`.`id` = `last_m`.`initiatorMessageId` OR `mess`.`id` = `last_m`.`interlocutorMessageId`) )
OR ( `conv`.`interlocutorId` = :user AND `conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " AND ( `mess`.`id` = `last_m`.`initiatorMessageId` OR `mess`.`id` = `last_m`.`interlocutorMessageId`) ) )
GROUP BY `conv`.`id`
ORDER BY `messageTimestamp` DESC
LIMIT :first, :count ";
return $this->dbo->queryForList($sql, array('user' => $userId, 'first' => $first, 'count' => $count));
}
public function getChatsConversationIdList($userId, $first, $count)
{
$sql = " SELECT `conv`.`id`, MAX(mess.timeStamp) as `messageTimestamp` FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
ON ( `last_m`.`conversationId` = `conv`.`id` )
INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `mess`
ON ( `conv`.`id` = `mess`.conversationId )
WHERE `conv`.`subject` = '".self::CHAT_CONVERSATION_SUBJECT."' AND (( `conv`.`initiatorId` = :user AND `conv`.`deleted` != " . self::DELETED_INITIATOR . " )
OR ( `conv`.`interlocutorId` = :user AND `conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " ))
GROUP BY `conv`.`id`
ORDER BY `messageTimestamp` DESC
LIMIT :first, :count ";
return $this->dbo->queryForList($sql, array('user' => $userId, 'first' => $first, 'count' => $count));
}
public function getConversationItem( $convId )
{
$sql = " SELECT `conv`.`id`, `conv`.`initiatorId`, `conv`.`interlocutorId`, `conv`.`subject`, `conv`.`read`, `conv`.`viewed`, `mess`.`id` as 'lastMessageId', `mess`.`text`, `mess`.`recipientRead`, `mess`.`timeStamp`, `mess`.`isSystem`, `mess`.`senderId` as `lastMessageSenderId`, `mess`.`recipientId` as `lastMessageRecipientId`, `mess`.`wasAuthorized` as `lastMessageWasAuthorized`, `last_m`.`initiatorMessageId`, `last_m`.`interlocutorMessageId` FROM `" . $this->getTableName() . "` AS `conv`
LEFT JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
ON ( `last_m`.`conversationId` = `conv`.`id` )
LEFT JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `mess`
ON ( `last_m`.`initiatorMessageId` = `mess`.id OR `last_m`.`interlocutorMessageId` = `mess`.id )
WHERE `conv`.`id` = :convId
ORDER BY `mess`.`timeStamp` DESC, `mess`.`id` DESC
LIMIT 1";
return $this->dbo->queryForRow($sql, array('convId' => $convId));
}
public function findChatConversationIdWithUserById($userId, $opponentId)
{
$sql = "SELECT `id` FROM `".$this->getTableName()."` WHERE `subject`=:subject AND ( `initiatorId`=:userId AND `interlocutorId`=:opponentId OR `initiatorId`=:opponentId AND `interlocutorId`=:userId )";
return (int)$this->dbo->queryForColumn($sql, array('subject'=>self::CHAT_CONVERSATION_SUBJECT, 'userId'=>$userId, 'opponentId'=>$opponentId));
}
public function findChatConversationIdWithUserByIdList($userId, $userIdList)
{
$userIdListString = $this->dbo->mergeInClause($userIdList);
$sql = "SELECT `id`, IF (`initiatorId` IN ( {$userIdListString} ), initiatorId, interlocutorId) AS opponentId FROM `".$this->getTableName()."` WHERE `subject`=:subject AND ( `initiatorId`=:userId AND `interlocutorId` IN ( {$userIdListString} ) OR `initiatorId` IN ( {$userIdListString} ) AND `interlocutorId`=:userId )";
return $this->dbo->queryForList($sql, array('subject'=>self::CHAT_CONVERSATION_SUBJECT, 'userId'=>$userId));
}
public function findWinkConversationIdWithUserById($userId, $opponentId)
{
$sql = "SELECT `id` FROM `".$this->getTableName()."` WHERE `subject`=:subject AND ( `initiatorId`=:userId AND `interlocutorId`=:opponentId OR `initiatorId`=:opponentId AND `interlocutorId`=:userId )";
return (int)$this->dbo->queryForColumn($sql, array('subject'=>self::WINK_CONVERSATION_SUBJECT, 'userId'=>$userId, 'opponentId'=>$opponentId));
}
public function findConversationListByUserId($userId, $activeModes)
{
if (in_array('chat', $activeModes) && in_array('mail', $activeModes))
{
$condition = "1 ";
}
else
{
$condition = "1 ";
if (in_array('chat', $activeModes))
{
$condition .= "AND `conv`.`subject` = '".MAILBOX_BOL_ConversationService::CHAT_CONVERSATION_SUBJECT."' ";
}
if (in_array('mail', $activeModes))
{
$condition .= "AND `conv`.`subject` <> '".MAILBOX_BOL_ConversationService::CHAT_CONVERSATION_SUBJECT."' ";
}
}
$sql = " SELECT `conv`.`id`, MAX(mess.timeStamp) as `messageTimestamp` FROM `" . $this->getTableName() . "` AS `conv`
LEFT JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
ON ( `last_m`.`conversationId` = `conv`.`id` )
LEFT JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `mess`
ON ( `conv`.`id` = `mess`.conversationId )
WHERE {$condition} AND (( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `mess`.`timeStamp`>`conv`.`initiatorDeletedTimestamp` ) )
OR ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `mess`.`timeStamp`>`conv`.`interlocutorDeletedTimestamp` ) )) AND `last_m`.`id` IS NOT NULL
GROUP BY `conv`.`id`
ORDER BY `messageTimestamp` DESC";
return $this->dbo->queryForColumnList($sql, array('user' => $userId));
}
public function findConversationItemListByUserId($userId, $activeModes, $from = 0, $count = 50, $convId = null)
{
if (in_array('chat', $activeModes) && in_array('mail', $activeModes))
{
$condition = "1 ";
}
else
{
$condition = "1 ";
if (in_array('chat', $activeModes))
{
$condition .= "AND `conv`.`subject` = '".MAILBOX_BOL_ConversationService::CHAT_CONVERSATION_SUBJECT."' ";
}
if (in_array('mail', $activeModes))
{
$condition .= "AND `conv`.`subject` <> '".MAILBOX_BOL_ConversationService::CHAT_CONVERSATION_SUBJECT."' ";
}
}
if ( $convId )
{
$condition .= "AND `conv`.`id` = '".(int) $convId."' ";
}
//
// $sql = " SELECT `conv`.`id`,
// `conv`.`initiatorId`,
// `conv`.`interlocutorId`,
// `conv`.`subject`,
// `conv`.`read`,
// `conv`.`viewed`,
//
// `last_m`.`initiatorMessageId`,
// `initiatorMessage`.`id` as initiatorLastMessageId,
// `initiatorMessage`.`text` as initiatorText,
// `initiatorMessage`.`recipientRead` as initiatorRecipientRead,
// `initiatorMessage`.`isSystem` as initiatorMessageIsSystem,
// `initiatorMessage`.`senderId` as `initiatorMessageSenderId`,
// `initiatorMessage`.`recipientId` as `initiatorMessageRecipientId`,
// `initiatorMessage`.`wasAuthorized` as `initiatorMessageWasAuthorized`,
// `initiatorMessage`.`timeStamp` as `initiatorMessageTimestamp`,
//
// `last_m`.`interlocutorMessageId`,
// `interlocutorMessage`.`id` as interlocutorLastMessageId,
// `interlocutorMessage`.`text` as interlocutorText,
// `interlocutorMessage`.`recipientRead` as interlocutorRecipientRead,
// `interlocutorMessage`.`isSystem` as interlocutorMessageIsSystem,
// `interlocutorMessage`.`senderId` as `interlocutorMessageSenderId`,
// `interlocutorMessage`.`recipientId` as `interlocutorMessageRecipientId`,
// `interlocutorMessage`.`wasAuthorized` as `interlocutorMessageWasAuthorized`,
// `interlocutorMessage`.`timeStamp` as `interlocutorMessageTimestamp`
//
// FROM `" . $this->getTableName() . "` AS `conv`
//
// LEFT JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
// ON ( `last_m`.`conversationId` = `conv`.`id` )
//
// LEFT JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `interlocutorMessage`
// ON ( `conv`.`id` = `interlocutorMessage`.conversationId AND `last_m`.`interlocutorMessageId` = `interlocutorMessage`.`id` )
//
// LEFT JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `initiatorMessage`
// ON ( `conv`.`id` = `initiatorMessage`.conversationId AND `last_m`.`initiatorMessageId` = `initiatorMessage`.`id` )
//
// WHERE {$condition} AND (( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `initiatorMessage`.`timeStamp`>`conv`.`initiatorDeletedTimestamp` ) )
// OR ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `interlocutorMessage`.`timeStamp`>`conv`.`interlocutorDeletedTimestamp` ) )) AND `last_m`.`id` IS NOT NULL
//
// GROUP BY `conv`.`id`
//
// ORDER BY GREATEST( COALESCE(`initiatorMessage`.`timeStamp`, 0), COALESCE(`interlocutorMessage`.`timeStamp`, 0) ) DESC
//
// LIMIT :from, :count";
$sql = " SELECT `conv`.`id`,
`conv`.`initiatorId`,
`conv`.`interlocutorId`,
`conv`.`subject`,
`conv`.`read`,
`conv`.`viewed`,
`initiatorMessage`.`id` as initiatorLastMessageId,
`initiatorMessage`.`text` as initiatorText,
`initiatorMessage`.`recipientRead` as initiatorRecipientRead,
`initiatorMessage`.`isSystem` as initiatorMessageIsSystem,
`initiatorMessage`.`senderId` as `initiatorMessageSenderId`,
`initiatorMessage`.`recipientId` as `initiatorMessageRecipientId`,
`initiatorMessage`.`wasAuthorized` as `initiatorMessageWasAuthorized`,
`initiatorMessage`.`timeStamp` as `initiatorMessageTimestamp`
FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `initiatorMessage`
ON ( `conv`.`lastMessageId` = `initiatorMessage`.`id` )
WHERE {$condition} AND (( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `initiatorMessage`.`timeStamp`>`conv`.`initiatorDeletedTimestamp` ) )
OR ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `initiatorMessage`.`timeStamp`>`conv`.`interlocutorDeletedTimestamp` ) ))
GROUP BY `conv`.`id`
ORDER BY `conv`.`lastMessageTimestamp` DESC
LIMIT :from, :count";
return $this->dbo->queryForList($sql, array('user' => $userId, 'from'=>$from, 'count'=>$count));
}
public function countConversationListByUserId($userId, $activeModes)
{
if (in_array('chat', $activeModes) && in_array('mail', $activeModes))
{
$condition = "1 ";
}
else
{
$condition = "1 ";
if (in_array('chat', $activeModes))
{
$condition .= "AND `conv`.`subject` = '".MAILBOX_BOL_ConversationService::CHAT_CONVERSATION_SUBJECT."' ";
}
if (in_array('mail', $activeModes))
{
$condition .= "AND `conv`.`subject` <> '".MAILBOX_BOL_ConversationService::CHAT_CONVERSATION_SUBJECT."' ";
}
}
$sql = "SELECT COUNT(*) FROM ( SELECT `conv`.`id` as `count` FROM `" . $this->getTableName() . "` AS `conv`
LEFT JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
ON ( `last_m`.`conversationId` = `conv`.`id` )
LEFT JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `mess`
ON ( `conv`.`id` = `mess`.conversationId )
WHERE {$condition} AND (( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `mess`.`timeStamp`>`conv`.`initiatorDeletedTimestamp` ) )
OR ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `mess`.`timeStamp`>`conv`.`interlocutorDeletedTimestamp` ) )) AND `last_m`.`id` IS NOT NULL
GROUP BY `conv`.`id`) AS `cnt`";
return $this->dbo->queryForColumn($sql, array('user' => $userId));
}
public function getViewedConversationCountForConsole( $userId, $convList )
{
$condition = " 1 ";
if ( !empty( $convList ) )
{
$condition .= " AND `conv`.`id` IN (". $this->dbo->mergeInClause($convList) .") ";
}
$sql = " SELECT COUNT(`conv`.`id`) FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
ON `last_m`.`conversationId` = `conv`.`id`
AND ( ( `conv`.`initiatorId` = :user AND `last_m`.`interlocutorMessageId` > 0 AND `conv`.`deleted` != " . self::DELETED_INITIATOR . " AND NOT `conv`.`read` & " . (self::READ_INITIATOR) . " AND `conv`.`viewed` & " . (self::VIEW_INITIATOR) . " )
OR ( `conv`.`interlocutorId` = :user AND `conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " AND NOT `conv`.`read` & " . (self::READ_INTERLOCUTOR) . " AND `conv`.`viewed` & " . (self::VIEW_INTERLOCUTOR) . " ) )";
return (int) $this->dbo->queryForColumn($sql, array('user' => $userId));
}
public function getNewConversationCountForConsole( $userId, $convList )
{
$condition = " 1 ";
if ( !empty( $convList ) )
{
$condition .= " AND `conv`.`id` IN (". $this->dbo->mergeInClause($convList) .") ";
}
$sql = " SELECT COUNT(`conv`.`id`) FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
ON `last_m`.`conversationId` = `conv`.`id`
AND ( ( `conv`.`initiatorId` = :user AND `last_m`.`interlocutorMessageId` > 0 AND `conv`.`deleted` != " . self::DELETED_INITIATOR . " AND NOT `conv`.`read` & " . (self::READ_INITIATOR) . " AND NOT `conv`.`viewed` & " . (self::VIEW_INITIATOR) . " )
OR ( `conv`.`interlocutorId` = :user AND `conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " AND NOT `conv`.`read` & " . (self::READ_INTERLOCUTOR) . " AND NOT `conv`.`viewed` & " . (self::VIEW_INTERLOCUTOR) . " ) )
WHERE ".$condition;
return (int) $this->dbo->queryForColumn($sql, array('user' => $userId));
}
public function getConsoleConversationList( $activeModes, $userId, $first, $count, $lastPingTime = null, $ignoreList = array() )
{
if (in_array('chat', $activeModes) && in_array('mail', $activeModes))
{
$condition = "1 ";
}
else
{
$condition = "1 ";
if (in_array('chat', $activeModes))
{
$condition .= "AND `conv`.`subject` = '".MAILBOX_BOL_ConversationService::CHAT_CONVERSATION_SUBJECT."' ";
}
if (in_array('mail', $activeModes))
{
$condition .= "AND `conv`.`subject` <> '".MAILBOX_BOL_ConversationService::CHAT_CONVERSATION_SUBJECT."' ";
}
}
$ignore = "";
if ( !empty( $ignoreList ) )
{
$ignore = " AND `conv`.id NOT IN (". $this->dbo->mergeInClause($ignoreList) .") ";
}
$sql = " SELECT `last_m`.*, `mess`.*, `conv`.* FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
ON ( `last_m`.`conversationId` = `conv`.`id` )
INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `mess`
ON ( `conv`.`id` = `mess`.conversationId )
WHERE {$condition} AND ( ( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `conv`.`lastMessageTimestamp` > `conv`.`initiatorDeletedTimestamp`) AND `mess`.`id` = `last_m`.`interlocutorMessageId` )
OR ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `conv`.`lastMessageTimestamp` > `conv`.`interlocutorDeletedTimestamp`) AND `mess`.`id` = `last_m`.`initiatorMessageId` ) ) $ignore
ORDER BY if( ((`conv`.`initiatorId` = :user AND `conv`.`viewed` & " . (self::VIEW_INITIATOR) . ") OR (`conv`.`interlocutorId` = :user AND `conv`.`viewed` & " . (self::VIEW_INTERLOCUTOR) . ")), 1, 0 ), `mess`.`timeStamp` DESC
LIMIT :first, :count ";
return $this->dbo->queryForList( $sql, array('user' => $userId, 'first' => $first, 'count' => $count, 'lastPingTime' => isset($lastPingTime) ? $lastPingTime : time() ) );
}
public function getMarkedUnreadConversationList( $userId, $ignoreList = array(), $activeModeList = array())
{
$mailModeEnabled = (in_array('mail', $activeModeList)) ? true : false;
$chatModeEnabled = (in_array('chat', $activeModeList)) ? true : false;
$ignore = "";
if ( !empty( $ignoreList ) )
{
$ignore = " AND `conv`.id NOT IN (". $this->dbo->mergeInClause($ignoreList) .") ";
}
if ( !$mailModeEnabled || !$chatModeEnabled )
{
if ( !$chatModeEnabled )
{
$ignore .= " AND `conv`.subject <> '" . MAILBOX_BOL_ConversationService::CHAT_CONVERSATION_SUBJECT . "' ";
}
else
{
$ignore .= " AND `conv`.subject = '" . MAILBOX_BOL_ConversationService::CHAT_CONVERSATION_SUBJECT . "' ";
}
}
$ignore .= ' AND `conv`.`lastMessageId` != 0';
$sql = " SELECT `conv`.`id` FROM `" . $this->getTableName() . "` AS `conv`
WHERE (( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `conv`.`lastMessageTimestamp` > `conv`.`initiatorDeletedTimestamp`) ) OR ( `conv`.`interlocutorId` = :user AND ( `conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `conv`.`lastMessageTimestamp` > `conv`.`interlocutorDeletedTimestamp`) ))
AND ( `conv`.`read` <> ".self::READ_ALL."
AND (( `conv`.`initiatorId` = :user AND NOT `conv`.`read` & ". self::READ_INITIATOR .") OR ( `conv`.`interlocutorId` = :user AND NOT `conv`.`read` & ". self::READ_INTERLOCUTOR ." ))
)
$ignore
";
return $this->dbo->queryForColumnList( $sql, array('user' => $userId) );
}
public function getInboxConversationList( $userId, $first, $count )
{
$sql = " SELECT `conv`.*, `last_m`.*, `mess`.* FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
ON ( `last_m`.`conversationId` = `conv`.`id` )
INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `mess`
ON ( `conv`.`id` = `mess`.conversationId )
WHERE ( `conv`.`initiatorId` = :user AND `conv`.`deleted` != " . self::DELETED_INITIATOR . " AND `mess`.`id` = `last_m`.`interlocutorMessageId` )
OR ( `conv`.`interlocutorId` = :user AND `conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " AND `mess`.`id` = `last_m`.`initiatorMessageId` )
ORDER BY `mess`.`timeStamp` DESC
LIMIT :first, :count ";
return $this->dbo->queryForList($sql, array('user' => $userId, 'first' => $first, 'count' => $count));
}
public function findConversationByKeyword( $kw, $limit = null, $from = 0 )
{
$questionName = OW::getConfig()->getValue('base', 'display_name_question');
$questionDataTable = BOL_QuestionDataDao::getInstance()->getTableName();
$userId = OW::getUser()->getId();
$limitStr = $limit === null ? '' : 'LIMIT '. intval($from) .', ' . intval($limit);
//TODO get by username
if ($questionName == 'username')
{
$join = "INNER JOIN `".BOL_UserDao::getInstance()->getTableName()."` AS us ON ( `us`.`id` = IF (`conv`.`initiatorId`=:user, `conv`.`interlocutorId`, `conv`.`initiatorId`) )";
$where = " `us`.`username` LIKE :kw ";
}
else
{
$join = "INNER JOIN `".$questionDataTable."` AS qd ON ( `qd`.`userId` = IF (`conv`.`initiatorId`=:user, `conv`.`interlocutorId`, `conv`.`initiatorId`) )";
$where = " `qd`.`questionName`=:name AND `qd`.`textValue` LIKE :kw ";
}
$query = " SELECT `conv`.`id`,
`conv`.`initiatorId`,
`conv`.`interlocutorId`,
`conv`.`subject`,
`conv`.`read`,
`conv`.`viewed`,
`conv`.`lastMessageTimestamp`,
`message`.`id` as lastMessageId,
`message`.`text` as lastMessageText,
`message`.`recipientRead` as lastMessageRecipientRead,
`message`.`isSystem` as lastMessageIsSystem,
`message`.`senderId` as lastMessageSenderId,
`message`.`recipientId` as lastMessageRecipientId,
`message`.`wasAuthorized` as lastMessageWasAuthorized
FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `message`
ON ( `conv`.`lastMessageId` = `message`.`id` )
{$join}
WHERE ( {$where} ) AND (( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `message`.`timeStamp`>`conv`.`initiatorDeletedTimestamp` ) )
OR ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `message`.`timeStamp`>`conv`.`interlocutorDeletedTimestamp` ) ))
UNION
SELECT `conv`.`id`,
`conv`.`initiatorId`,
`conv`.`interlocutorId`,
`conv`.`subject`,
`conv`.`read`,
`conv`.`viewed`,
`conv`.`lastMessageTimestamp`,
`message`.`id` as lastMessageId,
`message`.`text` as lastMessageText,
`message`.`recipientRead` as lastMessageRecipientRead,
`message`.`isSystem` as lastMessageIsSystem,
`message`.`senderId` as lastMessageSenderId,
`message`.`recipientId` as lastMessageRecipientId,
`message`.`wasAuthorized` as lastMessageWasAuthorized
FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `message`
ON ( `conv`.`lastMessageId` = `message`.`id` )
WHERE conv.subject LIKE :kw AND ( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `message`.`timeStamp`>`conv`.`initiatorDeletedTimestamp` ) )
UNION
SELECT `conv`.`id`,
`conv`.`initiatorId`,
`conv`.`interlocutorId`,
`conv`.`subject`,
`conv`.`read`,
`conv`.`viewed`,
`conv`.`lastMessageTimestamp`,
`message`.`id` as lastMessageId,
`message`.`text` as lastMessageText,
`message`.`recipientRead` as lastMessageRecipientRead,
`message`.`isSystem` as lastMessageIsSystem,
`message`.`senderId` as lastMessageSenderId,
`message`.`recipientId` as lastMessageRecipientId,
`message`.`wasAuthorized` as lastMessageWasAuthorized
FROM `" . $this->getTableName() . "` AS `conv`
INNER JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `message`
ON ( `conv`.`lastMessageId` = `message`.`id` )
WHERE conv.subject LIKE :kw AND ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `message`.`timeStamp`>`conv`.`interlocutorDeletedTimestamp` ))
GROUP BY 1
ORDER BY 7 DESC
".$limitStr;
//print_r($query);
// $query = " SELECT `conv`.`id`,
// `conv`.`initiatorId`,
// `conv`.`interlocutorId`,
// `conv`.`subject`,
// `conv`.`read`,
// `conv`.`viewed`,
//
// `last_m`.`initiatorMessageId`,
// `initiatorMessage`.`id` as initiatorLastMessageId,
// `initiatorMessage`.`text` as initiatorText,
// `initiatorMessage`.`recipientRead` as initiatorRecipientRead,
// `initiatorMessage`.`isSystem` as initiatorMessageIsSystem,
// `initiatorMessage`.`senderId` as `initiatorMessageSenderId`,
// `initiatorMessage`.`recipientId` as `initiatorMessageRecipientId`,
// `initiatorMessage`.`wasAuthorized` as `initiatorMessageWasAuthorized`,
// `initiatorMessage`.`timeStamp` as `initiatorMessageTimestamp`,
//
// `last_m`.`interlocutorMessageId`,
// `interlocutorMessage`.`id` as interlocutorLastMessageId,
// `interlocutorMessage`.`text` as interlocutorText,
// `interlocutorMessage`.`recipientRead` as interlocutorRecipientRead,
// `interlocutorMessage`.`isSystem` as interlocutorMessageIsSystem,
// `interlocutorMessage`.`senderId` as `interlocutorMessageSenderId`,
// `interlocutorMessage`.`recipientId` as `interlocutorMessageRecipientId`,
// `interlocutorMessage`.`wasAuthorized` as `interlocutorMessageWasAuthorized`,
// `interlocutorMessage`.`timeStamp` as `interlocutorMessageTimestamp`
//
// FROM `" . $this->getTableName() . "` AS `conv`
//
// INNER JOIN `" . MAILBOX_BOL_LastMessageDao::getInstance()->getTableName() . "` AS `last_m`
// ON ( `last_m`.`conversationId` = `conv`.`id` )
//
// LEFT JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `interlocutorMessage`
// ON ( `conv`.`id` = `interlocutorMessage`.conversationId AND `last_m`.`interlocutorMessageId` = `interlocutorMessage`.`id` )
//
// LEFT JOIN `" . MAILBOX_BOL_MessageDao::getInstance()->getTableName() . "` AS `initiatorMessage`
// ON ( `conv`.`id` = `initiatorMessage`.conversationId AND `last_m`.`initiatorMessageId` = `initiatorMessage`.`id` )
//
// {$join}
//
// WHERE ( {$where} ) AND (( `conv`.`initiatorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INITIATOR . " OR `initiatorMessage`.`timeStamp`>`conv`.`initiatorDeletedTimestamp` ) )
// OR ( `conv`.`interlocutorId` = :user AND (`conv`.`deleted` != " . self::DELETED_INTERLOCUTOR . " OR `interlocutorMessage`.`timeStamp`>`conv`.`interlocutorDeletedTimestamp` ) )) AND `last_m`.`id` IS NOT NULL
//
// GROUP BY `conv`.`id`
//
// ORDER BY GREATEST( COALESCE(`initiatorMessage`.`timeStamp`, 0), COALESCE(`interlocutorMessage`.`timeStamp`, 0) ) DESC
//
// ".$limitStr;
$conversationItemList = OW::getDbo()->queryForList($query, array(
'kw' => '%' . $kw . '%',
'user' => $userId,
'name' => $questionName
));
foreach($conversationItemList as $i => $conversation)
{
$conversationItemList[$i]['timeStamp'] = (int)$conversation['lastMessageTimestamp'];
$conversationItemList[$i]['lastMessageSenderId'] = $conversation['lastMessageSenderId'];
$conversationItemList[$i]['isSystem'] = $conversation['lastMessageIsSystem'];
$conversationItemList[$i]['text'] = $conversation['lastMessageText'];
$conversationItemList[$i]['lastMessageId'] = $conversation['lastMessageId'];
$conversationItemList[$i]['recipientRead'] = $conversation['lastMessageRecipientRead'];
$conversationItemList[$i]['lastMessageRecipientId'] = $conversation['lastMessageRecipientId'];
$conversationItemList[$i]['lastMessageWasAuthorized'] = $conversation['lastMessageWasAuthorized'];
}
// foreach($conversationItemList as $i => $conversation)
// {
// if ((int)$conversation['initiatorMessageTimestamp'] > (int)$conversation['interlocutorMessageTimestamp'])
// {
// $conversationItemList[$i]['timeStamp'] = (int)$conversation['initiatorMessageTimestamp'];
// $conversationItemList[$i]['lastMessageSenderId'] = $conversation['initiatorMessageSenderId'];
// $conversationItemList[$i]['isSystem'] = $conversation['initiatorMessageIsSystem'];
// $conversationItemList[$i]['text'] = $conversation['initiatorText'];
//
// $conversationItemList[$i]['lastMessageId'] = $conversation['initiatorLastMessageId'];
// $conversationItemList[$i]['recipientRead'] = $conversation['initiatorRecipientRead'];
// $conversationItemList[$i]['lastMessageRecipientId'] = $conversation['initiatorMessageRecipientId'];
// $conversationItemList[$i]['lastMessageWasAuthorized'] = $conversation['initiatorMessageWasAuthorized'];
// }
// else
// {
// $conversationItemList[$i]['timeStamp'] = (int)$conversation['interlocutorMessageTimestamp'];
// $conversationItemList[$i]['lastMessageSenderId'] = $conversation['interlocutorMessageSenderId'];
// $conversationItemList[$i]['isSystem'] = $conversation['interlocutorMessageIsSystem'];
// $conversationItemList[$i]['text'] = $conversation['interlocutorText'];
//
// $conversationItemList[$i]['lastMessageId'] = $conversation['interlocutorLastMessageId'];
// $conversationItemList[$i]['recipientRead'] = $conversation['interlocutorRecipientRead'];
// $conversationItemList[$i]['lastMessageRecipientId'] = $conversation['interlocutorMessageRecipientId'];
// $conversationItemList[$i]['lastMessageWasAuthorized'] = $conversation['interlocutorMessageWasAuthorized'];
// }
// }
return $conversationItemList;
}
/**
* @param $userId
* @return MAILBOX_BOL_Conversation
*/
public function findUserLastConversation($userId)
{
$example = new OW_Example();
$example->andFieldEqual('initiatorId', $userId);
$example->setOrder('createStamp DESC');
$example->setLimitClause(0,1);
return $this->findObjectByExample($example);
}
}