files/lib/system/worker/ConversationRebuildDataWorker.class.php

   1 <?php
   2
   3 namespace wcf\system\worker;
   4
   5 use wcf\data\conversation\Conversation;
   6 use wcf\data\conversation\ConversationAction;
   7 use wcf\data\conversation\ConversationEditor;
   8 use wcf\data\conversation\ConversationList;
   9 use wcf\system\WCF;
  10
  11 /**
  12  * Worker implementation for updating conversations.
  13  *
  14  * @author  Marcel Werk
  15  * @copyright   2001-2019 WoltLab GmbH
  16  * @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
  17  *
  18  * @method  ConversationList    getObjectList()
  19  */
  20 class ConversationRebuildDataWorker extends AbstractRebuildDataWorker
  21 {
  22     /**
  23      * @inheritDoc
  24      */
  25     protected $limit = 100;
  26
  27     /**
  28      * @inheritDoc
  29      */
  30     public function countObjects()
  31     {
  32         if ($this->count === null) {
  33             $this->count = 0;
  34             $sql = "SELECT  MAX(conversationID) AS conversationID
  35                     FROM    wcf1_conversation";
  36             $statement = WCF::getDB()->prepare($sql);
  37             $statement->execute();
  38             $row = $statement->fetchArray();
  39             if ($row !== false) {
  40                 $this->count = $row['conversationID'];
  41             }
  42         }
  43     }
  44
  45     /**
  46      * @inheritDoc
  47      */
  48     protected function initObjectList()
  49     {
  50         $this->objectList = new ConversationList();
  51         $this->objectList->sqlOrderBy = 'conversation.conversationID';
  52     }
  53
  54     /**
  55      * @inheritDoc
  56      */
  57     public function execute()
  58     {
  59         $this->objectList->getConditionBuilder()->add(
  60             'conversation.conversationID BETWEEN ? AND ?',
  61             [$this->limit * $this->loopCount + 1, $this->limit * $this->loopCount + $this->limit]
  62         );
  63
  64         parent::execute();
  65
  66         // prepare statements
  67         $sql = "SELECT      messageID, time, userID, username
  68                 FROM        wcf1_conversation_message
  69                 WHERE       conversationID = ?
  70                 ORDER BY    time";
  71         $firstMessageStatement = WCF::getDB()->prepare($sql, 1);
  72         $sql = "SELECT      time, userID, username
  73                 FROM        wcf1_conversation_message
  74                 WHERE       conversationID = ?
  75                 ORDER BY    time DESC";
  76         $lastMessageStatement = WCF::getDB()->prepare($sql, 1);
  77         $sql = "SELECT  COUNT(*) AS messages,
  78                         SUM(attachments) AS attachments
  79                 FROM    wcf1_conversation_message
  80                 WHERE   conversationID = ?";
  81         $statsStatement = WCF::getDB()->prepare($sql);
  82         $sql = "SELECT  COUNT(*) AS participants
  83                 FROM    wcf1_conversation_to_user conversation_to_user
  84                 WHERE   conversation_to_user.conversationID = ?
  85                     AND conversation_to_user.hideConversation <> ?
  86                     AND conversation_to_user.participantID <> ?
  87                     AND conversation_to_user.isInvisible = ?";
  88         $participantCounterStatement = WCF::getDB()->prepare($sql);
  89         $sql = "SELECT      conversation_to_user.participantID AS userID, conversation_to_user.hideConversation, user_table.username
  90                 FROM        wcf1_conversation_to_user conversation_to_user
  91                 LEFT JOIN   wcf1_user user_table
  92                 ON          user_table.userID = conversation_to_user.participantID
  93                 WHERE       conversation_to_user.conversationID = ?
  94                         AND conversation_to_user.participantID <> ?
  95                         AND conversation_to_user.isInvisible = ?
  96                 ORDER BY    user_table.username";
  97         $participantStatement = WCF::getDB()->prepare($sql, 5);
  98
  99         $sql = "SELECT  COUNT(*) AS participants
 100                 FROM    wcf1_conversation_to_user
 101                 WHERE   conversationID = ?
 102                     AND hideConversation <> ?
 103                     AND participantID IS NOT NULL";
 104         $existingParticipantStatement = WCF::getDB()->prepare($sql);
 105
 106         $obsoleteConversations = [];
 107         $updateData = [];
 108         /** @var Conversation $conversation */
 109         foreach ($this->objectList as $conversation) {
 110             // get stats
 111             $statsStatement->execute([$conversation->conversationID]);
 112             $row = $statsStatement->fetchSingleRow();
 113
 114             // update data
 115             $data = [
 116                 'attachments' => $row['attachments'] ?: 0,
 117                 'firstMessageID' => $conversation->firstMessageID,
 118                 'lastPostTime' => $conversation->lastPostTime,
 119                 'lastPosterID' => $conversation->lastPosterID,
 120                 'lastPoster' => $conversation->lastPoster,
 121                 'replies' => $row['messages'] ? $row['messages'] - 1 : 0,
 122                 'userID' => $conversation->userID,
 123                 'username' => $conversation->username,
 124             ];
 125
 126             // check for obsolete conversations
 127             $obsolete = $row['messages'] == 0;
 128             if (!$obsolete) {
 129                 if ($conversation->isDraft) {
 130                     if (!$conversation->userID) {
 131                         $obsolete = true;
 132                     }
 133                 } else {
 134                     $existingParticipantStatement->execute([$conversation->conversationID, Conversation::STATE_LEFT]);
 135                     $row = $existingParticipantStatement->fetchSingleRow();
 136                     if (!$row['participants']) {
 137                         $obsolete = true;
 138                     }
 139                 }
 140             }
 141
 142             if ($obsolete) {
 143                 $obsoleteConversations[] = new ConversationEditor($conversation);
 144                 continue;
 145             }
 146
 147             // get first post
 148             $firstMessageStatement->execute([$conversation->conversationID]);
 149             if (($row = $firstMessageStatement->fetchSingleRow()) !== false) {
 150                 $data['firstMessageID'] = $row['messageID'];
 151                 $data['lastPostTime'] = $data['time'] = $row['time'];
 152                 $data['userID'] = $row['userID'];
 153                 $data['username'] = $row['username'];
 154             }
 155
 156             // get last post
 157             $lastMessageStatement->execute([$conversation->conversationID]);
 158             if (($row = $lastMessageStatement->fetchSingleRow()) !== false) {
 159                 $data['lastPostTime'] = $row['time'];
 160                 $data['lastPosterID'] = $row['userID'];
 161                 $data['lastPoster'] = $row['username'];
 162             }
 163
 164             // get number of participants
 165             $participantCounterStatement->execute([
 166                 $conversation->conversationID,
 167                 Conversation::STATE_LEFT,
 168                 $conversation->userID,
 169                 0,
 170             ]);
 171             $data['participants'] = $participantCounterStatement->fetchSingleColumn();
 172
 173             // get participant summary
 174             $participantStatement->execute([$conversation->conversationID, $conversation->userID, 0]);
 175             $users = [];
 176             while ($row = $participantStatement->fetchArray()) {
 177                 $users[] = $row;
 178             }
 179             $data['participantSummary'] = \serialize($users);
 180
 181             $updateData[$conversation->conversationID] = $data;
 182         }
 183
 184         $sql = "UPDATE  wcf1_conversation
 185                 SET     firstMessageID = ?,
 186                         lastPostTime = ?,
 187                         lastPosterID = ?,
 188                         lastPoster = ?,
 189                         userID = ?,
 190                         username = ?,
 191                         replies = ?,
 192                         attachments = ?,
 193                         participants = ?,
 194                         participantSummary = ?
 195                 WHERE   conversationID = ?";
 196         $statement = WCF::getDB()->prepare($sql);
 197
 198         WCF::getDB()->beginTransaction();
 199         foreach ($updateData as $conversationID => $data) {
 200             $statement->execute([
 201                 $data['firstMessageID'],
 202                 $data['lastPostTime'],
 203                 $data['lastPosterID'],
 204                 $data['lastPoster'],
 205                 $data['userID'],
 206                 $data['username'],
 207                 $data['replies'],
 208                 $data['attachments'],
 209                 $data['participants'],
 210                 $data['participantSummary'],
 211                 $conversationID,
 212             ]);
 213         }
 214         WCF::getDB()->commitTransaction();
 215
 216         // delete obsolete conversations
 217         if (!empty($obsoleteConversations)) {
 218             $action = new ConversationAction($obsoleteConversations, 'delete');
 219             $action->executeAction();
 220         }
 221     }
 222 }