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