// set default mod permissions
$group = new UserGroup(5);
if ($group->groupID) {
- $sql = "REPLACE INTO wcf" . WCF_N . "_user_group_option_value
- (groupID, optionID, optionValue)
- SELECT 5, optionID, 1
- FROM wcf" . WCF_N . "_user_group_option
- WHERE optionName LIKE 'mod.conversation.%'";
+ $sql = "REPLACE INTO wcf" . WCF_N . "_user_group_option_value
+ (groupID, optionID, optionValue)
+ SELECT 5, optionID, 1
+ FROM wcf" . WCF_N . "_user_group_option
+ WHERE optionName LIKE 'mod.conversation.%'";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute();
}
$group = new UserGroup(6);
if ($group->groupID) {
- $sql = "REPLACE INTO wcf" . WCF_N . "_user_group_option_value
- (groupID, optionID, optionValue)
- SELECT 6, optionID, 1
- FROM wcf" . WCF_N . "_user_group_option
- WHERE optionName LIKE 'mod.conversation.%'";
+ $sql = "REPLACE INTO wcf" . WCF_N . "_user_group_option_value
+ (groupID, optionID, optionValue)
+ SELECT 6, optionID, 1
+ FROM wcf" . WCF_N . "_user_group_option
+ WHERE optionName LIKE 'mod.conversation.%'";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute();
}
$userID = WCF::getUser()->userID;
}
- $sql = "SELECT *
- FROM wcf" . WCF_N . "_conversation_to_user
- WHERE participantID = ?
- AND conversationID = ?";
+ $sql = "SELECT *
+ FROM wcf" . WCF_N . "_conversation_to_user
+ WHERE participantID = ?
+ AND conversationID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([$userID, $this->conversationID]);
$row = $statement->fetchArray();
*/
public static function getUserConversation($conversationID, $userID)
{
- $sql = "SELECT conversation_to_user.*, conversation.*
- FROM wcf" . WCF_N . "_conversation conversation
- LEFT JOIN wcf" . WCF_N . "_conversation_to_user conversation_to_user
- ON (conversation_to_user.participantID = ? AND conversation_to_user.conversationID = conversation.conversationID)
- WHERE conversation.conversationID = ?";
+ $sql = "SELECT conversation_to_user.*, conversation.*
+ FROM wcf" . WCF_N . "_conversation conversation
+ LEFT JOIN wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ ON (
+ conversation_to_user.participantID = ?
+ AND conversation_to_user.conversationID = conversation.conversationID
+ )
+ WHERE conversation.conversationID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([$userID, $conversationID]);
$row = $statement->fetchArray();
{
$conditionBuilder = new PreparedStatementConditionBuilder();
$conditionBuilder->add('conversation.conversationID IN (?)', [$conversationIDs]);
- $sql = "SELECT conversation_to_user.*, conversation.*
- FROM wcf" . WCF_N . "_conversation conversation
- LEFT JOIN wcf" . WCF_N . "_conversation_to_user conversation_to_user
- ON (conversation_to_user.participantID = " . $userID . " AND conversation_to_user.conversationID = conversation.conversationID)
- " . $conditionBuilder;
+ $sql = "SELECT conversation_to_user.*, conversation.*
+ FROM wcf" . WCF_N . "_conversation conversation
+ LEFT JOIN wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ ON (
+ conversation_to_user.participantID = " . $userID . "
+ AND conversation_to_user.conversationID = conversation.conversationID
+ )
+ " . $conditionBuilder;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditionBuilder->getParameters());
$conversations = [];
$this->canAddUnrestricted = false;
if ($this->isActiveParticipant()) {
$sql = "SELECT joinedAt
- FROM wcf" . WCF_N . "_conversation_to_user
- WHERE conversationID = ?
- AND participantID = ?";
+ FROM wcf" . WCF_N . "_conversation_to_user
+ WHERE conversationID = ?
+ AND participantID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([
$this->conversationID,
$conditions->add("(hideConversation <> ? AND leftAt = ?)", [self::STATE_LEFT, 0]);
}
- $sql = "SELECT participantID
- FROM wcf" . WCF_N . "_conversation_to_user
- " . $conditions;
+ $sql = "SELECT participantID
+ FROM wcf" . WCF_N . "_conversation_to_user
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
$conditions->add("conversation_to_user.leftByOwnChoice = ?", [1]);
}
- $sql = "SELECT user_table.username
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
- LEFT JOIN wcf" . WCF_N . "_user user_table
- ON (user_table.userID = conversation_to_user.participantID)
- " . $conditions;
+ $sql = "SELECT user_table.username
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ LEFT JOIN wcf" . WCF_N . "_user user_table
+ ON (user_table.userID = conversation_to_user.participantID)
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
if ($this->userID) {
// check if author has left the conversation
- $sql = "SELECT hideConversation
- FROM wcf" . WCF_N . "_conversation_to_user
- WHERE conversationID = ?
- AND participantID = ?";
+ $sql = "SELECT hideConversation
+ FROM wcf" . WCF_N . "_conversation_to_user
+ WHERE conversationID = ?
+ AND participantID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([$this->conversationID, $this->userID]);
$row = $statement->fetchArray();
{
if ($this->isActiveParticipant === null) {
$sql = "SELECT leftAt
- FROM wcf" . WCF_N . "_conversation_to_user
- WHERE conversationID = ?
- AND participantID = ?";
+ FROM wcf" . WCF_N . "_conversation_to_user
+ WHERE conversationID = ?
+ AND participantID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([
$this->conversationID,
$conditions->add("conversationID IN (?)", [$conversationIDs]);
$conditions->add("userID = ?", [$userID]);
- $sql = "SELECT conversationID
- FROM wcf" . WCF_N . "_conversation
- " . $conditions;
+ $sql = "SELECT conversationID
+ FROM wcf" . WCF_N . "_conversation
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
while ($row = $statement->fetchArray()) {
$conditions->add("participantID = ?", [$userID]);
$conditions->add("hideConversation <> ?", [self::STATE_LEFT]);
- $sql = "SELECT conversationID
- FROM wcf" . WCF_N . "_conversation_to_user
- " . $conditions;
+ $sql = "SELECT conversationID
+ FROM wcf" . WCF_N . "_conversation_to_user
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
while ($row = $statement->fetchArray()) {
$conditionBuilder = new PreparedStatementConditionBuilder();
$conditionBuilder->add('groupID IN (?)', [$validGroupIDs]);
$sql = "SELECT DISTINCT userID
- FROM wcf" . WCF_N . "_user_to_group
- " . $conditionBuilder;
+ FROM wcf" . WCF_N . "_user_to_group
+ " . $conditionBuilder;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditionBuilder->getParameters());
while ($userID = $statement->fetchColumn()) {
UserStorageHandler::getInstance()->reset($conversation->getParticipantIDs(), 'conversationCount');
// mark conversation as read for the author
- $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
- SET lastVisitTime = ?
- WHERE participantID = ?
- AND conversationID = ?";
+ $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
+ SET lastVisitTime = ?
+ WHERE participantID = ?
+ AND conversationID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([$data['time'], $data['userID'], $conversation->conversationID]);
} else {
$conditions = new PreparedStatementConditionBuilder();
$conditions->add("conversationID IN (?)", [$this->objectIDs]);
$sql = "SELECT DISTINCT participantID
- FROM wcf" . WCF_N . "_conversation_to_user
- " . $conditions;
+ FROM wcf" . WCF_N . "_conversation_to_user
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
}
$conversationIDs = [];
- $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
- SET lastVisitTime = ?
- WHERE participantID = ?
- AND conversationID = ?";
+ $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
+ SET lastVisitTime = ?
+ WHERE participantID = ?
+ AND conversationID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
WCF::getDB()->beginTransaction();
foreach ($this->getObjects() as $conversation) {
$conditionBuilder->add('conversation.conversationID IN (?)', [$conversationIDs]);
$conditionBuilder->add('conversation.time <= ?', [$this->parameters['visitTime']]);
- $sql = "SELECT conversation.conversationID
- FROM wcf" . WCF_N . "_conversation conversation,
- wcf" . WCF_N . "_user_notification notification
- " . $conditionBuilder;
+ $sql = "SELECT conversation.conversationID
+ FROM wcf" . WCF_N . "_conversation conversation,
+ wcf" . WCF_N . "_user_notification notification
+ " . $conditionBuilder;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditionBuilder->getParameters());
$notificationObjectIDs = $statement->fetchAll(\PDO::FETCH_COLUMN);
$conditionBuilder->add('conversation_message.conversationID IN (?)', [$conversationIDs]);
$conditionBuilder->add('conversation_message.time <= ?', [$this->parameters['visitTime']]);
- $sql = "SELECT conversation_message.messageID
- FROM wcf" . WCF_N . "_conversation_message conversation_message,
- wcf" . WCF_N . "_user_notification notification
- " . $conditionBuilder;
+ $sql = "SELECT conversation_message.messageID
+ FROM wcf" . WCF_N . "_conversation_message conversation_message,
+ wcf" . WCF_N . "_user_notification notification
+ " . $conditionBuilder;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditionBuilder->getParameters());
$notificationObjectIDs = $statement->fetchAll(\PDO::FETCH_COLUMN);
*/
public function markAllAsRead()
{
- $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
- SET lastVisitTime = ?
- WHERE participantID = ?";
+ $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
+ SET lastVisitTime = ?
+ WHERE participantID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([
TIME_NOW,
public function getLeaveForm()
{
// get hidden state from first conversation (all others have the same state)
- $sql = "SELECT hideConversation
- FROM wcf" . WCF_N . "_conversation_to_user
- WHERE conversationID = ?
- AND participantID = ?";
+ $sql = "SELECT hideConversation
+ FROM wcf" . WCF_N . "_conversation_to_user
+ WHERE conversationID = ?
+ AND participantID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([
\current($this->objectIDs),
*/
public function hideConversation()
{
- $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
- SET hideConversation = ?
- WHERE conversationID = ?
- AND participantID = ?";
+ $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
+ SET hideConversation = ?
+ WHERE conversationID = ?
+ AND participantID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
WCF::getDB()->beginTransaction();
$conditionBuilder = new PreparedStatementConditionBuilder();
$conditionBuilder->add('conversation.conversationID IN (?)', [$this->objectIDs]);
$conditionBuilder->add('conversation_to_user.conversationID IS NULL');
- $sql = "SELECT DISTINCT conversation.conversationID
- FROM wcf" . WCF_N . "_conversation conversation
- LEFT JOIN wcf" . WCF_N . "_conversation_to_user conversation_to_user
- ON ( conversation_to_user.conversationID = conversation.conversationID
- AND conversation_to_user.hideConversation <> " . Conversation::STATE_LEFT . "
- AND conversation_to_user.participantID IS NOT NULL)
- " . $conditionBuilder;
+ $sql = "SELECT DISTINCT conversation.conversationID
+ FROM wcf" . WCF_N . "_conversation conversation
+ LEFT JOIN wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ ON (
+ conversation_to_user.conversationID = conversation.conversationID
+ AND conversation_to_user.hideConversation <> " . Conversation::STATE_LEFT . "
+ AND conversation_to_user.participantID IS NOT NULL
+ )
+ " . $conditionBuilder;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditionBuilder->getParameters());
$conversationIDs = $statement->fetchAll(\PDO::FETCH_COLUMN);
*/
public function getMixedConversationList()
{
- $sqlSelect = ' , (SELECT participantID FROM wcf' . WCF_N . '_conversation_to_user WHERE conversationID = conversation.conversationID AND participantID <> conversation.userID AND isInvisible = 0 ORDER BY username, participantID LIMIT 1) AS otherParticipantID
- , (SELECT username FROM wcf' . WCF_N . '_conversation_to_user WHERE conversationID = conversation.conversationID AND participantID <> conversation.userID AND isInvisible = 0 ORDER BY username, participantID LIMIT 1) AS otherParticipant';
+ $sqlSelect = ' , (
+ SELECT participantID
+ FROM wcf' . WCF_N . '_conversation_to_user
+ WHERE conversationID = conversation.conversationID
+ AND participantID <> conversation.userID
+ AND isInvisible = 0
+ ORDER BY username, participantID
+ LIMIT 1
+ ) AS otherParticipantID
+ , (
+ SELECT username
+ FROM wcf' . WCF_N . '_conversation_to_user
+ WHERE conversationID = conversation.conversationID
+ AND participantID <> conversation.userID
+ AND isInvisible = 0
+ ORDER BY username, participantID
+ LIMIT 1
+ ) AS otherParticipant';
$unreadConversationList = new UserConversationList(WCF::getUser()->userID);
$unreadConversationList->sqlSelects .= $sqlSelect;
// collect number of messages for each conversation
$conditionBuilder = new PreparedStatementConditionBuilder();
$conditionBuilder->add('conversation_message.conversationID IN (?)', [$this->objectIDs]);
- $sql = "SELECT conversationID, COUNT(messageID) AS messages, SUM(attachments) AS attachments
- FROM wcf" . WCF_N . "_conversation_message conversation_message
- " . $conditionBuilder . "
- GROUP BY conversationID";
+ $sql = "SELECT conversationID, COUNT(messageID) AS messages, SUM(attachments) AS attachments
+ FROM wcf" . WCF_N . "_conversation_message conversation_message
+ " . $conditionBuilder . "
+ GROUP BY conversationID";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditionBuilder->getParameters());
*/
public function resetParticipants()
{
- $sql = "DELETE FROM wcf" . WCF_N . "_conversation_to_user
- WHERE conversationID = ?
- AND participantID <> ?";
+ $sql = "DELETE FROM wcf" . WCF_N . "_conversation_to_user
+ WHERE conversationID = ?
+ AND participantID <> ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([$this->conversationID, $this->userID]);
}
$conditions = new PreparedStatementConditionBuilder();
$conditions->add("userID IN (?)", [\array_merge($participantIDs, $invisibleParticipantIDs)]);
- $sql = "SELECT userID, username
- FROM wcf" . WCF_N . "_user
- " . $conditions;
+ $sql = "SELECT userID, username
+ FROM wcf" . WCF_N . "_user
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
while ($row = $statement->fetchArray()) {
if (!empty($participantIDs)) {
WCF::getDB()->beginTransaction();
- $sql = "INSERT INTO wcf" . WCF_N . "_conversation_to_user
- (conversationID, participantID, username, isInvisible, joinedAt)
- VALUES (?, ?, ?, ?, ?)
- ON DUPLICATE KEY
- UPDATE hideConversation = 0, leftAt = 0, leftByOwnChoice = 1";
+ $sql = "INSERT INTO wcf" . WCF_N . "_conversation_to_user
+ (conversationID, participantID, username, isInvisible, joinedAt)
+ VALUES (?, ?, ?, ?, ?)
+ ON DUPLICATE KEY
+ UPDATE hideConversation = 0,
+ leftAt = 0,
+ leftByOwnChoice = 1";
$statement = WCF::getDB()->prepareStatement($sql);
foreach ($participantIDs as $userID) {
if (!empty($invisibleParticipantIDs)) {
WCF::getDB()->beginTransaction();
- $sql = "INSERT INTO wcf" . WCF_N . "_conversation_to_user
- (conversationID, participantID, username, isInvisible)
- VALUES (?, ?, ?, ?)";
+ $sql = "INSERT INTO wcf" . WCF_N . "_conversation_to_user
+ (conversationID, participantID, username, isInvisible)
+ VALUES (?, ?, ?, ?)";
$statement = WCF::getDB()->prepareStatement($sql);
foreach ($invisibleParticipantIDs as $userID) {
*/
public function updateParticipantCount()
{
- $sql = "UPDATE wcf" . WCF_N . "_conversation conversation
- SET participants = (
- SELECT COUNT(*) AS count
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
- WHERE conversation_to_user.conversationID = conversation.conversationID
- AND conversation_to_user.hideConversation <> ?
- AND conversation_to_user.participantID <> ?
- AND conversation_to_user.isInvisible = ?
- )
- WHERE conversation.conversationID = ?";
+ $sql = "UPDATE wcf" . WCF_N . "_conversation conversation
+ SET participants = (
+ SELECT COUNT(*) AS count
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ WHERE conversation_to_user.conversationID = conversation.conversationID
+ AND conversation_to_user.hideConversation <> ?
+ AND conversation_to_user.participantID <> ?
+ AND conversation_to_user.isInvisible = ?
+ )
+ WHERE conversation.conversationID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([
Conversation::STATE_LEFT,
*/
public function updateParticipantSummary()
{
- $sql = "SELECT participantID AS userID, hideConversation, username
- FROM wcf" . WCF_N . "_conversation_to_user
- WHERE conversationID = ?
- AND participantID <> ?
- AND isInvisible = 0
- ORDER BY username";
+ $sql = "SELECT participantID AS userID, hideConversation, username
+ FROM wcf" . WCF_N . "_conversation_to_user
+ WHERE conversationID = ?
+ AND participantID <> ?
+ AND isInvisible = 0
+ ORDER BY username";
$statement = WCF::getDB()->prepareStatement($sql, 5);
$statement->execute([$this->conversationID, $this->userID]);
public function removeParticipant($userID)
{
$sql = "SELECT joinedAt
- FROM wcf" . WCF_N . "_conversation_to_user
- WHERE conversationID = ?
- AND participantID = ?";
+ FROM wcf" . WCF_N . "_conversation_to_user
+ WHERE conversationID = ?
+ AND participantID = ?";
$statement = WCF::getDB()->prepareStatement($sql, 1);
$statement->execute([$this->conversationID, $userID]);
$joinedAt = $statement->fetchSingleColumn();
$sql = "SELECT messageID
- FROM wcf" . WCF_N . "_conversation_message
- WHERE conversationID = ?
- AND time >= ?
- AND time <= ?";
+ FROM wcf" . WCF_N . "_conversation_message
+ WHERE conversationID = ?
+ AND time >= ?
+ AND time <= ?";
$statement = WCF::getDB()->prepareStatement($sql, 1);
$statement->execute([
$this->conversationID,
]);
$lastMessageID = $statement->fetchSingleColumn();
- $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
- SET leftAt = ?,
- lastMessageID = ?,
- leftByOwnChoice = ?
- WHERE conversationID = ?
- AND participantID = ?";
+ $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
+ SET leftAt = ?,
+ lastMessageID = ?,
+ leftByOwnChoice = ?
+ WHERE conversationID = ?
+ AND participantID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([
TIME_NOW,
*/
public function updateFirstMessage()
{
- $sql = "SELECT messageID
- FROM wcf" . WCF_N . "_conversation_message
- WHERE conversationID = ?
- ORDER BY time ASC";
+ $sql = "SELECT messageID
+ FROM wcf" . WCF_N . "_conversation_message
+ WHERE conversationID = ?
+ ORDER BY time ASC";
$statement = WCF::getDB()->prepareStatement($sql, 1);
$statement->execute([
$this->conversationID,
*/
public function updateLastMessage()
{
- $sql = "SELECT time, userID, username
- FROM wcf" . WCF_N . "_conversation_message
- WHERE conversationID = ?
- ORDER BY time DESC";
+ $sql = "SELECT time, userID, username
+ FROM wcf" . WCF_N . "_conversation_message
+ WHERE conversationID = ?
+ ORDER BY time DESC";
$statement = WCF::getDB()->prepareStatement($sql, 1);
$statement->execute([
$this->conversationID,
*/
public function countObjects()
{
- $sql = "SELECT COUNT(*) AS count
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
- " . $this->sqlConditionJoins . "
- " . $this->getConditionBuilder()->__toString();
+ $sql = "SELECT COUNT(*) AS count
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ " . $this->sqlConditionJoins . "
+ " . $this->getConditionBuilder()->__toString();
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($this->getConditionBuilder()->getParameters());
$row = $statement->fetchArray();
public function readObjectIDs()
{
$this->objectIDs = [];
- $sql = "SELECT conversation_to_user.participantID AS objectID
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
- " . $this->sqlConditionJoins . "
- " . $this->getConditionBuilder()->__toString() . "
- " . (!empty($this->sqlOrderBy) ? "ORDER BY " . $this->sqlOrderBy : '');
+ $sql = "SELECT conversation_to_user.participantID AS objectID
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ " . $this->sqlConditionJoins . "
+ " . $this->getConditionBuilder()->__toString() . "
+ " . (!empty($this->sqlOrderBy) ? "ORDER BY " . $this->sqlOrderBy : '');
$statement = WCF::getDB()->prepareStatement($sql, $this->sqlLimit, $this->sqlOffset);
$statement->execute($this->getConditionBuilder()->getParameters());
$this->objectIDs = $statement->fetchAll(\PDO::FETCH_COLUMN);
parent::readObjects();
// check for deleted users
- $sql = "SELECT username
- FROM wcf" . WCF_N . "_conversation_to_user
- WHERE conversationID = ?
- AND participantID IS NULL";
+ $sql = "SELECT username
+ FROM wcf" . WCF_N . "_conversation_to_user
+ WHERE conversationID = ?
+ AND participantID IS NULL";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([$this->conversationID]);
$i = 0;
*/
public function readObjectIDs()
{
- $sql = "SELECT conversation_to_user.conversationID AS objectID
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
- " . $this->sqlConditionJoins . "
- " . $this->getConditionBuilder() . "
- " . (!empty($this->sqlOrderBy) ? "ORDER BY " . $this->sqlOrderBy : '');
+ $sql = "SELECT conversation_to_user.conversationID AS objectID
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ " . $this->sqlConditionJoins . "
+ " . $this->getConditionBuilder() . "
+ " . (!empty($this->sqlOrderBy) ? "ORDER BY " . $this->sqlOrderBy : '');
$statement = WCF::getDB()->prepareStatement($sql, $this->sqlLimit, $this->sqlOffset);
$statement->execute($this->getConditionBuilder()->getParameters());
$this->objectIDs = $statement->fetchAll(\PDO::FETCH_COLUMN);
// filter by label id
if ($labelID) {
$this->getConditionBuilder()->add("conversation.conversationID IN (
- SELECT conversationID
- FROM wcf" . WCF_N . "_conversation_label_to_object
- WHERE labelID = ?
- )", [$labelID]);
+ SELECT conversationID
+ FROM wcf" . WCF_N . "_conversation_label_to_object
+ WHERE labelID = ?
+ )", [$labelID]);
}
// own posts
return parent::countObjects();
}
- $sql = "SELECT COUNT(*) AS count
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
- " . $this->sqlConditionJoins . "
- " . $this->getConditionBuilder()->__toString();
+ $sql = "SELECT COUNT(*) AS count
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ " . $this->sqlConditionJoins . "
+ " . $this->getConditionBuilder()->__toString();
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($this->getConditionBuilder()->getParameters());
$row = $statement->fetchArray();
return;
}
- $sql = "SELECT conversation_to_user.conversationID AS objectID
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
- " . $this->sqlConditionJoins . "
- " . $this->getConditionBuilder()->__toString() . "
- " . (!empty($this->sqlOrderBy) ? "ORDER BY " . $this->sqlOrderBy : '');
+ $sql = "SELECT conversation_to_user.conversationID AS objectID
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ " . $this->sqlConditionJoins . "
+ " . $this->getConditionBuilder()->__toString() . "
+ " . (!empty($this->sqlOrderBy) ? "ORDER BY " . $this->sqlOrderBy : '');
$statement = WCF::getDB()->prepareStatement($sql, $this->sqlLimit, $this->sqlOffset);
$statement->execute($this->getConditionBuilder()->getParameters());
$this->objectIDs = $statement->fetchAll(\PDO::FETCH_COLUMN);
$conditions = new PreparedStatementConditionBuilder();
$conditions->add("messageID IN (?)", [$messageIDs]);
$sql = "SELECT messageID, userID, username, time
- FROM wcf" . WCF_N . "_conversation_message
- " . $conditions;
+ FROM wcf" . WCF_N . "_conversation_message
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
$messageData = [];
$conditions->add("conversationID IN (?)", [\array_keys($this->objects)]);
$conditions->add("labelID IN (?)", [\array_keys($labels)]);
- $sql = "SELECT labelID, conversationID
- FROM wcf" . WCF_N . "_conversation_label_to_object
- " . $conditions;
+ $sql = "SELECT labelID, conversationID
+ FROM wcf" . WCF_N . "_conversation_label_to_object
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
$data = [];
$conditions->add("conversationID = ?", [$conversation->conversationID]);
$conditions->add("labelID IN (?)", [\array_keys($labels)]);
- $sql = "SELECT labelID
- FROM wcf" . WCF_N . "_conversation_label_to_object
- " . $conditions;
+ $sql = "SELECT labelID
+ FROM wcf" . WCF_N . "_conversation_label_to_object
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
while ($row = $statement->fetchArray()) {
$conditions->add("conversationID = ?", [$conversationID]);
$conditions->add("labelID IN (?)", [$labelIDs]);
- $sql = "SELECT labelID
- FROM wcf" . WCF_N . "_conversation_label_to_object
- " . $conditions;
+ $sql = "SELECT labelID
+ FROM wcf" . WCF_N . "_conversation_label_to_object
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
$assignedLabels = $statement->fetchAll(\PDO::FETCH_COLUMN);
$conditions->add("conversationID IN (?)", [$this->parameters['conversationIDs']]);
$conditions->add("labelID IN (?)", [$labelIDs]);
- $sql = "DELETE FROM wcf" . WCF_N . "_conversation_label_to_object
- " . $conditions;
+ $sql = "DELETE FROM wcf" . WCF_N . "_conversation_label_to_object
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
// assign label ids
if (!empty($this->parameters['labelIDs'])) {
- $sql = "INSERT INTO wcf" . WCF_N . "_conversation_label_to_object
- (labelID, conversationID)
- VALUES (?, ?)";
+ $sql = "INSERT INTO wcf" . WCF_N . "_conversation_label_to_object
+ (labelID, conversationID)
+ VALUES (?, ?)";
$statement = WCF::getDB()->prepareStatement($sql);
WCF::getDB()->beginTransaction();
$userConversation = Conversation::getUserConversation($conversation->conversationID, $message->userID);
if ($userConversation !== null && $userConversation->isInvisible) {
// make invisible participant visible
- $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
- SET isInvisible = 0
- WHERE participantID = ?
- AND conversationID = ?";
+ $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
+ SET isInvisible = 0
+ WHERE participantID = ?
+ AND conversationID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([$message->userID, $conversation->conversationID]);
}
// reset visibility if it was hidden but not left
- $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
- SET hideConversation = ?
- WHERE conversationID = ?
- AND hideConversation = ?";
+ $sql = "UPDATE wcf" . WCF_N . "_conversation_to_user
+ SET hideConversation = ?
+ WHERE conversationID = ?
+ AND hideConversation = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([
Conversation::STATE_DEFAULT,
{
/** @var Conversation $conversation */
- $sql = "SELECT COUNT(*) AS count
- FROM wcf" . WCF_N . "_conversation_message
- WHERE conversationID = ?";
+ $sql = "SELECT COUNT(*) AS count
+ FROM wcf" . WCF_N . "_conversation_message
+ WHERE conversationID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([$conversation->conversationID]);
$count = $statement->fetchArray();
*/
public function readObjects()
{
- $sql = "SELECT modification_log.*
- FROM wcf" . WCF_N . "_modification_log modification_log
- " . $this->getConditionBuilder() . "
- " . (!empty($this->sqlOrderBy) ? "ORDER BY " . $this->sqlOrderBy : '');
+ $sql = "SELECT modification_log.*
+ FROM wcf" . WCF_N . "_modification_log modification_log
+ " . $this->getConditionBuilder() . "
+ " . (!empty($this->sqlOrderBy) ? "ORDER BY " . $this->sqlOrderBy : '');
$statement = WCF::getDB()->prepareStatement($sql, $this->sqlLimit, $this->sqlOffset);
$statement->execute($this->getConditionBuilder()->getParameters());
$this->objects = $statement->fetchObjects(($this->objectClassName ?: $this->className));
$conditions = new PreparedStatementConditionBuilder();
$conditions->add('username IN (?)', [$this->participants]);
$sql = "SELECT userID
- FROM wcf" . WCF_N . "_user
- " . $conditions;
+ FROM wcf" . WCF_N . "_user
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
$userIDs = [];
//
// See https://github.com/WoltLab/com.woltlab.wcf.conversation/issues/131
$this->objectList->getConditionBuilder()->add('
- (
- (
- conversation.userID = ?
- AND
- conversation.conversationID IN (
- SELECT conversationID
- FROM wcf' . WCF_N . '_conversation_to_user
- WHERE participantID IN (?)
- GROUP BY conversationID
- HAVING COUNT(conversationID) = ?
- )
- )
- OR
- (
- conversation.userID <> ?
- AND
- conversation.conversationID IN (
- SELECT conversationID
- FROM wcf' . WCF_N . '_conversation_to_user
- WHERE participantID IN (?)
- AND isInvisible = ?
- GROUP BY conversationID
- HAVING COUNT(conversationID) = ?
- )
- )
- )', [
+ (
+ (
+ conversation.userID = ?
+ AND conversation.conversationID IN (
+ SELECT conversationID
+ FROM wcf' . WCF_N . '_conversation_to_user
+ WHERE participantID IN (?)
+ GROUP BY conversationID
+ HAVING COUNT(conversationID) = ?
+ )
+ )
+ OR
+ (
+ conversation.userID <> ?
+ AND conversation.conversationID IN (
+ SELECT conversationID
+ FROM wcf' . WCF_N . '_conversation_to_user
+ WHERE participantID IN (?)
+ AND isInvisible = ?
+ GROUP BY conversationID
+ HAVING COUNT(conversationID) = ?
+ )
+ )
+ )', [
// Parameters for the first condition.
WCF::getUser()->userID,
$userIDs,
if ($count > 1) {
$this->objectList->seek($count - 1);
if ($this->objectList->current()->time < $this->conversation->lastPostTime) {
- $sql = "SELECT time
- FROM wcf" . WCF_N . "_conversation_message
- WHERE conversationID = ?
- AND time > ?
- ORDER BY time";
+ $sql = "SELECT time
+ FROM wcf" . WCF_N . "_conversation_message
+ WHERE conversationID = ?
+ AND time > ?
+ ORDER BY time";
$statement = WCF::getDB()->prepareStatement($sql, 1);
$statement->execute([$this->conversationID, $this->objectList->current()->time]);
$endTime = $statement->fetchSingleColumn() - 1;
$conditionBuilder = clone $this->objectList->getConditionBuilder();
$conditionBuilder->add('time ' . ($this->sortOrder == 'ASC' ? '<=' : '>=') . ' ?', [$this->message->time]);
- $sql = "SELECT COUNT(*) AS messages
- FROM wcf" . WCF_N . "_conversation_message conversation_message
- " . $conditionBuilder;
+ $sql = "SELECT COUNT(*) AS messages
+ FROM wcf" . WCF_N . "_conversation_message conversation_message
+ " . $conditionBuilder;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditionBuilder->getParameters());
$row = $statement->fetchArray();
*/
protected function goToLastPost()
{
- $sql = "SELECT conversation_message.messageID
- FROM wcf" . WCF_N . "_conversation_message conversation_message
- " . $this->objectList->getConditionBuilder() . "
- ORDER BY time " . ($this->sortOrder == 'ASC' ? 'DESC' : 'ASC');
+ $sql = "SELECT conversation_message.messageID
+ FROM wcf" . WCF_N . "_conversation_message conversation_message
+ " . $this->objectList->getConditionBuilder() . "
+ ORDER BY time " . ($this->sortOrder == 'ASC' ? 'DESC' : 'ASC');
$statement = WCF::getDB()->prepareStatement($sql, 1);
$statement->execute($this->objectList->getConditionBuilder()->getParameters());
$row = $statement->fetchArray();
$conditionBuilder = clone $this->objectList->getConditionBuilder();
$conditionBuilder->add('time > ?', [$this->conversation->lastVisitTime]);
- $sql = "SELECT conversation_message.messageID
- FROM wcf" . WCF_N . "_conversation_message conversation_message
- " . $conditionBuilder . "
- ORDER BY time ASC";
+ $sql = "SELECT conversation_message.messageID
+ FROM wcf" . WCF_N . "_conversation_message conversation_message
+ " . $conditionBuilder . "
+ ORDER BY time ASC";
$statement = WCF::getDB()->prepareStatement($sql, 1);
$statement->execute($conditionBuilder->getParameters());
$row = $statement->fetchArray();
switch ($action->actionName) {
case 'assignLabel':
// check if user has labels
- $sql = "SELECT COUNT(*) AS count
- FROM wcf" . WCF_N . "_conversation_label
- WHERE userID = ?";
+ $sql = "SELECT COUNT(*) AS count
+ FROM wcf" . WCF_N . "_conversation_label
+ WHERE userID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([WCF::getUser()->userID]);
$row = $statement->fetchArray();
$conditions->add("conversationID IN (?)", [$conversationIDs]);
$conditions->add("participantID = ?", [WCF::getUser()->userID]);
- $sql = "SELECT conversationID
- FROM wcf" . WCF_N . "_conversation_to_user
- " . $conditions;
+ $sql = "SELECT conversationID
+ FROM wcf" . WCF_N . "_conversation_to_user
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
while ($row = $statement->fetchArray()) {
$conditions->add("participantID = ?", [WCF::getUser()->userID]);
$conditions->add("hideConversation <> ?", [1]);
- $sql = "SELECT conversationID
- FROM wcf" . WCF_N . "_conversation_to_user
- " . $conditions;
+ $sql = "SELECT conversationID
+ FROM wcf" . WCF_N . "_conversation_to_user
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
$conditions->add("conversationID IN (?)", [\array_keys($this->conversations)]);
$conditions->add("participantID = ?", [WCF::getUser()->userID]);
- $sql = "SELECT conversationID, lastVisitTime
- FROM wcf" . WCF_N . "_conversation_to_user
- " . $conditions;
+ $sql = "SELECT conversationID, lastVisitTime
+ FROM wcf" . WCF_N . "_conversation_to_user
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
$lastVisitTime = [];
$conditions->add("participantID = ?", [WCF::getUser()->userID]);
$conditions->add("hideConversation <> ?", [0]);
- $sql = "SELECT conversationID
- FROM wcf" . WCF_N . "_conversation_to_user
- " . $conditions;
+ $sql = "SELECT conversationID
+ FROM wcf" . WCF_N . "_conversation_to_user
+ " . $conditions;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditions->getParameters());
$conditionBuilder->add('conversation_to_user.lastVisitTime < conversation.lastPostTime');
$conditionBuilder->add('conversation_to_user.leftAt = 0');
- $sql = "SELECT COUNT(*) AS count
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user,
- wcf" . WCF_N . "_conversation conversation
- " . $conditionBuilder;
+ $sql = "SELECT COUNT(*) AS count
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user,
+ wcf" . WCF_N . "_conversation conversation
+ " . $conditionBuilder;
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute($conditionBuilder->getParameters());
$row = $statement->fetchArray();
$conditionBuilder2->add('conversation.userID = ?', [$userID]);
$conditionBuilder2->add('conversation.isDraft = 1');
- $sql = "SELECT (SELECT COUNT(*)
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
- " . $conditionBuilder1->__toString() . ")
- +
- (SELECT COUNT(*)
- FROM wcf" . WCF_N . "_conversation conversation
- " . $conditionBuilder2->__toString() . ") AS count";
+ $sql = "SELECT (
+ SELECT COUNT(*)
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ " . $conditionBuilder1->__toString() . "
+ ) + (
+ SELECT COUNT(*)
+ FROM wcf" . WCF_N . "_conversation conversation
+ " . $conditionBuilder2->__toString() . "
+ ) AS count";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute(\array_merge(
$conditionBuilder1->getParameters(),
}
$data['participantID'] = ImportHandler::getInstance()->getNewID('com.woltlab.wcf.user', $data['participantID']);
- $sql = "INSERT INTO wcf" . WCF_N . "_conversation_to_user
- (conversationID, participantID, username, hideConversation, isInvisible, lastVisitTime)
- VALUES (?, ?, ?, ?, ?, ?)
- ON DUPLICATE KEY UPDATE hideConversation = IF(hideConversation > 0 AND hideConversation = VALUES(hideConversation),hideConversation,0),
- isInvisible = IF(isInvisible AND VALUES(isInvisible),1,0),
- lastVisitTime = GREATEST(lastVisitTime,VALUES(lastVisitTime))";
+ $sql = "INSERT INTO wcf" . WCF_N . "_conversation_to_user
+ (conversationID, participantID, username, hideConversation, isInvisible, lastVisitTime)
+ VALUES (?, ?, ?, ?, ?, ?)
+ ON DUPLICATE KEY UPDATE hideConversation = IF(hideConversation > 0 AND hideConversation = VALUES(hideConversation),hideConversation,0),
+ isInvisible = IF(isInvisible AND VALUES(isInvisible),1,0),
+ lastVisitTime = GREATEST(lastVisitTime,VALUES(lastVisitTime))";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute([
$data['conversationID'],
// save labels
if ($data['participantID'] && !empty($additionalData['labelIDs'])) {
- $sql = "INSERT IGNORE INTO wcf" . WCF_N . "_conversation_label_to_object
- (labelID, conversationID)
- VALUES (?, ?)";
+ $sql = "INSERT IGNORE INTO wcf" . WCF_N . "_conversation_label_to_object
+ (labelID, conversationID)
+ VALUES (?, ?)";
$statement = WCF::getDB()->prepareStatement($sql);
foreach ($additionalData['labelIDs'] as $labelID) {
$labelID = ImportHandler::getInstance()->getNewID('com.woltlab.wcf.conversation.label', $labelID);
*/
public function getJoins()
{
- return "JOIN wcf" . WCF_N . "_conversation_to_user conversation_to_user ON (conversation_to_user.participantID = " . WCF::getUser()->userID . " AND conversation_to_user.conversationID = " . $this->getTableName() . ".conversationID)
- LEFT JOIN wcf" . WCF_N . "_conversation conversation ON (conversation.conversationID = " . $this->getTableName() . ".conversationID)";
+ return " JOIN wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ ON (
+ conversation_to_user.participantID = " . WCF::getUser()->userID . "
+ AND conversation_to_user.conversationID = " . $this->getTableName() . ".conversationID
+ )
+ LEFT JOIN wcf" . WCF_N . "_conversation conversation
+ ON (conversation.conversationID = " . $this->getTableName() . ".conversationID)";
}
/**
{
if ($this->count === null) {
$this->count = 0;
- $sql = "SELECT MAX(messageID) AS messageID
- FROM wcf" . WCF_N . "_conversation_message";
+ $sql = "SELECT MAX(messageID) AS messageID
+ FROM wcf" . WCF_N . "_conversation_message";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute();
$row = $statement->fetchArray();
// prepare statements
$attachmentObjectType = ObjectTypeCache::getInstance()
->getObjectTypeByName('com.woltlab.wcf.attachment.objectType', 'com.woltlab.wcf.conversation.message');
- $sql = "SELECT COUNT(*) AS attachments
- FROM wcf" . WCF_N . "_attachment
- WHERE objectTypeID = ?
- AND objectID = ?";
+ $sql = "SELECT COUNT(*) AS attachments
+ FROM wcf" . WCF_N . "_attachment
+ WHERE objectTypeID = ?
+ AND objectID = ?";
$attachmentStatement = WCF::getDB()->prepareStatement($sql);
// retrieve permissions
}
$sql = "UPDATE wcf" . WCF_N . "_conversation_message
- SET attachments = ?,
- message = ?,
- enableHtml = ?,
- hasEmbeddedObjects = ?
- WHERE messageID = ?";
+ SET attachments = ?,
+ message = ?,
+ enableHtml = ?,
+ hasEmbeddedObjects = ?
+ WHERE messageID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
WCF::getDB()->beginTransaction();
{
if ($this->count === null) {
$this->count = 0;
- $sql = "SELECT MAX(conversationID) AS conversationID
- FROM wcf" . WCF_N . "_conversation";
+ $sql = "SELECT MAX(conversationID) AS conversationID
+ FROM wcf" . WCF_N . "_conversation";
$statement = WCF::getDB()->prepareStatement($sql);
$statement->execute();
$row = $statement->fetchArray();
parent::execute();
// prepare statements
- $sql = "SELECT messageID, time, userID, username
- FROM wcf" . WCF_N . "_conversation_message
- WHERE conversationID = ?
- ORDER BY time";
+ $sql = "SELECT messageID, time, userID, username
+ FROM wcf" . WCF_N . "_conversation_message
+ WHERE conversationID = ?
+ ORDER BY time";
$firstMessageStatement = WCF::getDB()->prepareStatement($sql, 1);
- $sql = "SELECT time, userID, username
- FROM wcf" . WCF_N . "_conversation_message
- WHERE conversationID = ?
- ORDER BY time DESC";
+ $sql = "SELECT time, userID, username
+ FROM wcf" . WCF_N . "_conversation_message
+ WHERE conversationID = ?
+ ORDER BY time DESC";
$lastMessageStatement = WCF::getDB()->prepareStatement($sql, 1);
- $sql = "SELECT COUNT(*) AS messages,
- SUM(attachments) AS attachments
- FROM wcf" . WCF_N . "_conversation_message
- WHERE conversationID = ?";
+ $sql = "SELECT COUNT(*) AS messages,
+ SUM(attachments) AS attachments
+ FROM wcf" . WCF_N . "_conversation_message
+ WHERE conversationID = ?";
$statsStatement = WCF::getDB()->prepareStatement($sql);
- $sql = "SELECT COUNT(*) AS participants
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
- WHERE conversation_to_user.conversationID = ?
- AND conversation_to_user.hideConversation <> ?
- AND conversation_to_user.participantID <> ?
- AND conversation_to_user.isInvisible = ?";
+ $sql = "SELECT COUNT(*) AS participants
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ WHERE conversation_to_user.conversationID = ?
+ AND conversation_to_user.hideConversation <> ?
+ AND conversation_to_user.participantID <> ?
+ AND conversation_to_user.isInvisible = ?";
$participantCounterStatement = WCF::getDB()->prepareStatement($sql);
- $sql = "SELECT conversation_to_user.participantID AS userID, conversation_to_user.hideConversation, user_table.username
- FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
- LEFT JOIN wcf" . WCF_N . "_user user_table
- ON (user_table.userID = conversation_to_user.participantID)
- WHERE conversation_to_user.conversationID = ?
- AND conversation_to_user.participantID <> ?
- AND conversation_to_user.isInvisible = ?
- ORDER BY user_table.username";
+ $sql = "SELECT conversation_to_user.participantID AS userID, conversation_to_user.hideConversation, user_table.username
+ FROM wcf" . WCF_N . "_conversation_to_user conversation_to_user
+ LEFT JOIN wcf" . WCF_N . "_user user_table
+ ON (user_table.userID = conversation_to_user.participantID)
+ WHERE conversation_to_user.conversationID = ?
+ AND conversation_to_user.participantID <> ?
+ AND conversation_to_user.isInvisible = ?
+ ORDER BY user_table.username";
$participantStatement = WCF::getDB()->prepareStatement($sql, 5);
- $sql = "SELECT COUNT(*) AS participants
- FROM wcf" . WCF_N . "_conversation_to_user
- WHERE conversationID = ?
- AND hideConversation <> ?
- AND participantID IS NOT NULL";
+ $sql = "SELECT COUNT(*) AS participants
+ FROM wcf" . WCF_N . "_conversation_to_user
+ WHERE conversationID = ?
+ AND hideConversation <> ?
+ AND participantID IS NOT NULL";
$existingParticipantStatement = WCF::getDB()->prepareStatement($sql);
$obsoleteConversations = [];
}
$sql = "UPDATE wcf" . WCF_N . "_conversation
- SET firstMessageID = ?,
- lastPostTime = ?,
- lastPosterID = ?,
- lastPoster = ?,
- userID = ?,
- username = ?,
- replies = ?,
- attachments = ?,
- participants = ?,
- participantSummary = ?
- WHERE conversationID = ?";
+ SET firstMessageID = ?,
+ lastPostTime = ?,
+ lastPosterID = ?,
+ lastPoster = ?,
+ userID = ?,
+ username = ?,
+ replies = ?,
+ attachments = ?,
+ participants = ?,
+ participantSummary = ?
+ WHERE conversationID = ?";
$statement = WCF::getDB()->prepareStatement($sql);
WCF::getDB()->beginTransaction();