/** * Rebuilds the conversation data of the relevant conversations. */ public function rebuild() { if (empty($this->objects)) { $this->readObjects(); } // collect number of messages for each conversation $conditionBuilder = new PreparedStatementConditionBuilder(); $conditionBuilder->add('conversation_message.conversationID IN (?)', array($this->objectIDs)); $sql = "SELECT\t\tconversationID, COUNT(messageID) AS messages, SUM(attachments) AS attachments\n\t\t\tFROM\t\twcf" . WCF_N . "_conversation_message conversation_message\n\t\t\t" . $conditionBuilder . "\n\t\t\tGROUP BY\tconversationID"; $statement = WCF::getDB()->prepareStatement($sql); $statement->execute($conditionBuilder->getParameters()); $objectIDs = array(); while ($row = $statement->fetchArray()) { if (!$row['messages']) { continue; } $objectIDs[] = $row['conversationID']; $conversationEditor = new ConversationEditor(new Conversation(null, array('conversationID' => $row['conversationID']))); $conversationEditor->update(array('attachments' => $row['attachments'], 'replies' => $row['messages'] - 1)); $conversationEditor->updateFirstMessage(); $conversationEditor->updateLastMessage(); } // delete conversations without messages $deleteConversationIDs = array_diff($this->objectIDs, $objectIDs); if (!empty($deleteConversationIDs)) { $conversationAction = new ConversationAction($deleteConversationIDs, 'delete'); $conversationAction->executeAction(); } }
/** * @see \wcf\system\worker\IWorker::execute() */ public function execute() { $this->objectList->getConditionBuilder()->add('conversation.conversationID BETWEEN ? AND ?', array($this->limit * $this->loopCount + 1, $this->limit * $this->loopCount + $this->limit)); parent::execute(); // prepare statements $sql = "SELECT\t\tmessageID, time, userID, username\n\t\t\tFROM\t\twcf" . WCF_N . "_conversation_message\n\t\t\tWHERE\t\tconversationID = ?\n\t\t\tORDER BY\ttime"; $firstMessageStatement = WCF::getDB()->prepareStatement($sql, 1); $sql = "SELECT\t\ttime, userID, username\n\t\t\tFROM\t\twcf" . WCF_N . "_conversation_message\n\t\t\tWHERE\t\tconversationID = ?\n\t\t\tORDER BY\ttime DESC"; $lastMessageStatement = WCF::getDB()->prepareStatement($sql, 1); $sql = "SELECT\tCOUNT(*) AS messages,\n\t\t\t\tSUM(attachments) AS attachments\n\t\t\tFROM\twcf" . WCF_N . "_conversation_message\n\t\t\tWHERE\tconversationID = ?"; $statsStatement = WCF::getDB()->prepareStatement($sql); $sql = "SELECT\tCOUNT(*) AS participants\n\t\t\tFROM\twcf" . WCF_N . "_conversation_to_user conversation_to_user\n\t\t\tWHERE\tconversation_to_user.conversationID = ?\n\t\t\t\tAND conversation_to_user.hideConversation <> ?\n\t\t\t\tAND conversation_to_user.participantID <> ?\n\t\t\t\tAND conversation_to_user.isInvisible = ?"; $participantCounterStatement = WCF::getDB()->prepareStatement($sql); $sql = "SELECT\t\tconversation_to_user.participantID AS userID, conversation_to_user.hideConversation, user_table.username\n\t\t\tFROM\t\twcf" . WCF_N . "_conversation_to_user conversation_to_user\n\t\t\tLEFT JOIN\twcf" . WCF_N . "_user user_table\n\t\t\tON\t\t(user_table.userID = conversation_to_user.participantID)\n\t\t\tWHERE\t\tconversation_to_user.conversationID = ?\n\t\t\t\t\tAND conversation_to_user.participantID <> ?\n\t\t\t\t\tAND conversation_to_user.isInvisible = ?\n\t\t\tORDER BY\tuser_table.username"; $participantStatement = WCF::getDB()->prepareStatement($sql, 5); $sql = "SELECT\tCOUNT(*) AS participants\n\t\t\tFROM\twcf" . WCF_N . "_conversation_to_user\n\t\t\tWHERE\tconversationID = ?\n\t\t\t\tAND participantID IS NOT NULL"; $existingParticipantStatement = WCF::getDB()->prepareStatement($sql, 5); $obsoleteConversations = array(); foreach ($this->objectList as $conversation) { $editor = new ConversationEditor($conversation); // check for obsolete conversations $obsolete = false; if ($conversation->isDraft) { if (!$conversation->userID) { $obsolete = true; } } else { $existingParticipantStatement->execute(array($conversation->conversationID)); $row = $existingParticipantStatement->fetchSingleRow(); if (!$row['participants']) { $obsolete = true; } } if ($obsolete) { $obsoleteConversations[] = $editor; continue; } // update data $data = array(); // get first post $firstMessageStatement->execute(array($conversation->conversationID)); if (($row = $firstMessageStatement->fetchSingleRow()) !== false) { $data['firstMessageID'] = $row['messageID']; $data['lastPostTime'] = $data['time'] = $row['time']; $data['userID'] = $row['userID']; $data['username'] = $row['username']; } // get last post $lastMessageStatement->execute(array($conversation->conversationID)); if (($row = $lastMessageStatement->fetchSingleRow()) !== false) { $data['lastPostTime'] = $row['time']; $data['lastPosterID'] = $row['userID']; $data['lastPoster'] = $row['username']; } // get stats $statsStatement->execute(array($conversation->conversationID)); $row = $statsStatement->fetchSingleRow(); $data['replies'] = $row['messages'] ? $row['messages'] - 1 : 0; $data['attachments'] = $row['attachments'] ?: 0; // get number of participants $participantCounterStatement->execute(array($conversation->conversationID, Conversation::STATE_LEFT, $conversation->userID, 0)); $row = $participantCounterStatement->fetchSingleRow(); $data['participants'] = $row['participants']; // get participant summary $participantStatement->execute(array($conversation->conversationID, $conversation->userID, 0)); $users = array(); while ($row = $participantStatement->fetchArray()) { $users[] = $row; } $data['participantSummary'] = serialize($users); $editor->update($data); } // delete obsolete conversations if (!empty($obsoleteConversations)) { $action = new ConversationAction($obsoleteConversations, 'delete'); $action->executeAction(); } }