ConfirmationEmailTemplate
2 months ago
ImportExport
1 month ago
RestApi
1 month ago
Statistics
2 months ago
BulkActionController.php
3 months ago
BulkActionException.php
3 months ago
BulkConfirmationEmailResender.php
3 months ago
ConfirmationEmailCustomizer.php
3 months ago
ConfirmationEmailMailer.php
3 months ago
ConfirmationEmailResolver.php
3 months ago
EngagementDataBackfiller.php
3 months ago
InactiveSubscribersController.php
1 month ago
LinkTokens.php
3 months ago
NewSubscriberNotificationMailer.php
3 months ago
RequiredCustomFieldValidator.php
3 months ago
SegmentsCountRecalculator.php
1 month ago
Source.php
3 months ago
SubscriberActions.php
3 months ago
SubscriberCustomFieldRepository.php
3 years ago
SubscriberIPsRepository.php
2 years ago
SubscriberLimitNotificationEvaluator.php
3 months ago
SubscriberLimitNotificationMailer.php
3 months ago
SubscriberLimitNotificationScheduler.php
3 months ago
SubscriberListingRepository.php
1 month ago
SubscriberPersonalDataEraser.php
3 months ago
SubscriberSaveController.php
3 months ago
SubscriberSegmentRepository.php
1 month ago
SubscriberSubscribeController.php
1 month ago
SubscriberTagRepository.php
4 years ago
SubscribersCountsController.php
1 month ago
SubscribersEmailCountsController.php
1 month ago
SubscribersRepository.php
1 month ago
index.php
3 years ago
InactiveSubscribersController.php
192 lines
| 1 | <?php declare(strict_types = 1); |
| 2 | |
| 3 | namespace MailPoet\Subscribers; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use MailPoet\Entities\ScheduledTaskEntity; |
| 9 | use MailPoet\Entities\ScheduledTaskSubscriberEntity; |
| 10 | use MailPoet\Entities\SendingQueueEntity; |
| 11 | use MailPoet\Entities\SubscriberEntity; |
| 12 | use MailPoetVendor\Carbon\Carbon; |
| 13 | use MailPoetVendor\Doctrine\DBAL\ArrayParameterType; |
| 14 | use MailPoetVendor\Doctrine\DBAL\ParameterType; |
| 15 | use MailPoetVendor\Doctrine\ORM\EntityManager; |
| 16 | |
| 17 | class InactiveSubscribersController { |
| 18 | |
| 19 | const UNOPENED_EMAILS_THRESHOLD = 3; |
| 20 | const LIFETIME_EMAILS_THRESHOLD = 10; |
| 21 | |
| 22 | private $processedTaskIdsTableCreated = false; |
| 23 | |
| 24 | /** @var EntityManager */ |
| 25 | private $entityManager; |
| 26 | |
| 27 | public function __construct( |
| 28 | EntityManager $entityManager |
| 29 | ) { |
| 30 | $this->entityManager = $entityManager; |
| 31 | } |
| 32 | |
| 33 | public function markInactiveSubscribers(int $daysToInactive, int $startId, int $endId, ?int $unopenedEmails = self::UNOPENED_EMAILS_THRESHOLD) { |
| 34 | $thresholdDate = $this->getThresholdDate($daysToInactive); |
| 35 | return $this->deactivateSubscribers($thresholdDate, $startId, $endId, $unopenedEmails); |
| 36 | } |
| 37 | |
| 38 | public function markActiveSubscribers(int $daysToInactive, int $batchSize): int { |
| 39 | $thresholdDate = $this->getThresholdDate($daysToInactive); |
| 40 | return $this->activateSubscribers($thresholdDate, $batchSize); |
| 41 | } |
| 42 | |
| 43 | public function reactivateInactiveSubscribers(): void { |
| 44 | $subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 45 | $reactivateAllInactiveQuery = " |
| 46 | UPDATE {$subscribersTable} SET status = :statusSubscribed WHERE status = :statusInactive |
| 47 | "; |
| 48 | $this->entityManager->getConnection()->executeQuery($reactivateAllInactiveQuery, [ |
| 49 | 'statusSubscribed' => SubscriberEntity::STATUS_SUBSCRIBED, |
| 50 | 'statusInactive' => SubscriberEntity::STATUS_INACTIVE, |
| 51 | ]); |
| 52 | } |
| 53 | |
| 54 | private function getThresholdDate(int $daysToInactive): Carbon { |
| 55 | $now = new Carbon(); |
| 56 | return $now->subDays($daysToInactive); |
| 57 | } |
| 58 | |
| 59 | /** |
| 60 | * @return int |
| 61 | */ |
| 62 | private function deactivateSubscribers(Carbon $thresholdDate, int $startId, int $endId, ?int $unopenedEmails = self::UNOPENED_EMAILS_THRESHOLD) { |
| 63 | $subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 64 | $scheduledTasksTable = $this->entityManager->getClassMetadata(ScheduledTaskEntity::class)->getTableName(); |
| 65 | $scheduledTaskSubscribersTable = $this->entityManager->getClassMetadata(ScheduledTaskSubscriberEntity::class)->getTableName(); |
| 66 | $sendingQueuesTable = $this->entityManager->getClassMetadata(SendingQueueEntity::class)->getTableName(); |
| 67 | $connection = $this->entityManager->getConnection(); |
| 68 | |
| 69 | $thresholdDateIso = $thresholdDate->toDateTimeString(); |
| 70 | $dayAgo = new Carbon(); |
| 71 | $dayAgoIso = $dayAgo->subDay()->toDateTimeString(); |
| 72 | |
| 73 | // Temporary table with processed tasks from threshold date up to yesterday |
| 74 | $processedTaskIdsTable = 'inactive_task_ids'; |
| 75 | if (!$this->processedTaskIdsTableCreated) { |
| 76 | $processedTaskIdsTableSql = " |
| 77 | CREATE TEMPORARY TABLE IF NOT EXISTS {$processedTaskIdsTable} |
| 78 | (INDEX task_id_ids (id), PRIMARY KEY (`id`)) |
| 79 | SELECT DISTINCT task_id as id FROM {$sendingQueuesTable} as sq |
| 80 | JOIN {$scheduledTasksTable} as st ON sq.task_id = st.id |
| 81 | WHERE st.processed_at > :thresholdDate |
| 82 | AND st.processed_at < :dayAgo |
| 83 | "; |
| 84 | $connection->executeQuery($processedTaskIdsTableSql, [ |
| 85 | 'thresholdDate' => $thresholdDateIso, |
| 86 | 'dayAgo' => $dayAgoIso, |
| 87 | ]); |
| 88 | $this->processedTaskIdsTableCreated = true; |
| 89 | } |
| 90 | |
| 91 | // Select subscribers who received at least a number of emails after threshold date and subscribed before that |
| 92 | $lifetimeEmailsThreshold = self::LIFETIME_EMAILS_THRESHOLD; |
| 93 | $inactiveSubscriberIdsTmpTable = 'inactive_subscriber_ids'; |
| 94 | $connection->executeQuery( |
| 95 | " |
| 96 | CREATE TEMPORARY TABLE IF NOT EXISTS {$inactiveSubscriberIdsTmpTable} |
| 97 | (UNIQUE subscriber_id (id), PRIMARY KEY (`id`)) |
| 98 | SELECT s.id FROM {$subscribersTable} as s |
| 99 | JOIN {$scheduledTaskSubscribersTable} as sts USE INDEX (subscriber_id) ON s.id = sts.subscriber_id |
| 100 | JOIN {$processedTaskIdsTable} task_ids ON task_ids.id = sts.task_id |
| 101 | WHERE s.last_subscribed_at < :thresholdDate |
| 102 | AND s.status = :status |
| 103 | AND s.id >= :startId |
| 104 | AND s.id <= :endId |
| 105 | AND s.email_count >= {$lifetimeEmailsThreshold} |
| 106 | GROUP BY s.id |
| 107 | HAVING count(s.id) >= :unopenedEmailsThreshold |
| 108 | ", |
| 109 | [ |
| 110 | 'thresholdDate' => $thresholdDateIso, |
| 111 | 'status' => SubscriberEntity::STATUS_SUBSCRIBED, |
| 112 | 'startId' => $startId, |
| 113 | 'endId' => $endId, |
| 114 | 'unopenedEmailsThreshold' => $unopenedEmails, |
| 115 | ] |
| 116 | ); |
| 117 | |
| 118 | $result = $connection->executeQuery(" |
| 119 | SELECT isi.id FROM {$inactiveSubscriberIdsTmpTable} isi |
| 120 | LEFT OUTER JOIN {$subscribersTable} as s ON isi.id = s.id AND GREATEST( |
| 121 | COALESCE(s.last_engagement_at, '0'), |
| 122 | COALESCE(s.last_subscribed_at, '0'), |
| 123 | COALESCE(s.created_at, '0') |
| 124 | ) > :thresholdDate |
| 125 | WHERE s.id IS NULL |
| 126 | ", [ |
| 127 | 'thresholdDate' => $thresholdDateIso, |
| 128 | ]); |
| 129 | $idsToDeactivate = $result->fetchAllAssociative(); |
| 130 | |
| 131 | $connection->executeQuery("DROP TABLE {$inactiveSubscriberIdsTmpTable}"); |
| 132 | |
| 133 | $idsToDeactivate = $this->extractIds($idsToDeactivate); |
| 134 | if (!count($idsToDeactivate)) { |
| 135 | return 0; |
| 136 | } |
| 137 | $connection->executeQuery("UPDATE {$subscribersTable} SET status = :statusInactive WHERE id IN (:idsToDeactivate)", [ |
| 138 | 'statusInactive' => SubscriberEntity::STATUS_INACTIVE, |
| 139 | 'idsToDeactivate' => $idsToDeactivate, |
| 140 | ], ['idsToDeactivate' => ArrayParameterType::INTEGER]); |
| 141 | return count($idsToDeactivate); |
| 142 | } |
| 143 | |
| 144 | private function activateSubscribers(Carbon $thresholdDate, int $batchSize): int { |
| 145 | $subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 146 | $connection = $this->entityManager->getConnection(); |
| 147 | |
| 148 | $idsToActivate = $connection->executeQuery(" |
| 149 | SELECT s.id |
| 150 | FROM {$subscribersTable} s |
| 151 | LEFT OUTER JOIN {$subscribersTable} s2 ON s.id = s2.id AND GREATEST( |
| 152 | COALESCE(s2.last_engagement_at, '0'), |
| 153 | COALESCE(s2.last_subscribed_at, '0'), |
| 154 | COALESCE(s2.created_at, '0') |
| 155 | ) > :thresholdDate |
| 156 | WHERE s.last_subscribed_at < :thresholdDate |
| 157 | AND s.status = :statusInactive |
| 158 | AND s2.id IS NOT NULL |
| 159 | GROUP BY s.id |
| 160 | LIMIT :batchSize |
| 161 | ", [ |
| 162 | 'thresholdDate' => $thresholdDate, |
| 163 | 'statusInactive' => SubscriberEntity::STATUS_INACTIVE, |
| 164 | 'batchSize' => $batchSize, |
| 165 | ], ['batchSize' => ParameterType::INTEGER])->fetchAllAssociative(); |
| 166 | |
| 167 | $idsToActivate = $this->extractIds($idsToActivate); |
| 168 | if (!count($idsToActivate)) { |
| 169 | return 0; |
| 170 | } |
| 171 | $connection->executeQuery("UPDATE {$subscribersTable} SET status = :statusSubscribed WHERE id IN (:idsToActivate)", [ |
| 172 | 'statusSubscribed' => SubscriberEntity::STATUS_SUBSCRIBED, |
| 173 | 'idsToActivate' => $idsToActivate, |
| 174 | ], ['idsToActivate' => ArrayParameterType::INTEGER]); |
| 175 | return count($idsToActivate); |
| 176 | } |
| 177 | |
| 178 | /** |
| 179 | * @param array<array<string, mixed>> $rows |
| 180 | * @return int[] |
| 181 | */ |
| 182 | private function extractIds(array $rows): array { |
| 183 | $ids = []; |
| 184 | foreach ($rows as $row) { |
| 185 | if (isset($row['id']) && is_numeric($row['id'])) { |
| 186 | $ids[] = (int)$row['id']; |
| 187 | } |
| 188 | } |
| 189 | return $ids; |
| 190 | } |
| 191 | } |
| 192 |