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
SubscribersEmailCountsController.php
132 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\SubscriberEntity; |
| 11 | use MailPoetVendor\Carbon\Carbon; |
| 12 | use MailPoetVendor\Doctrine\DBAL\ParameterType; |
| 13 | use MailPoetVendor\Doctrine\ORM\EntityManager; |
| 14 | |
| 15 | class SubscribersEmailCountsController { |
| 16 | /** @var EntityManager */ |
| 17 | private $entityManager; |
| 18 | |
| 19 | /** @var string */ |
| 20 | private $subscribersTable; |
| 21 | |
| 22 | /** @var string */ |
| 23 | private $scheduledTasksTable; |
| 24 | |
| 25 | public function __construct( |
| 26 | EntityManager $entityManager |
| 27 | ) { |
| 28 | $this->entityManager = $entityManager; |
| 29 | $this->subscribersTable = $this->entityManager->getClassMetadata(SubscriberEntity::class)->getTableName(); |
| 30 | $this->scheduledTasksTable = $this->entityManager->getClassMetadata(ScheduledTaskEntity::class)->getTableName(); |
| 31 | } |
| 32 | |
| 33 | public function updateSubscribersEmailCounts(?\DateTimeInterface $dateLastProcessed, int $startId, int $endId, ?\DateTimeInterface $now = null): int { |
| 34 | $scheduledTaskSubscribersTable = $this->entityManager->getClassMetadata(ScheduledTaskSubscriberEntity::class)->getTableName(); |
| 35 | |
| 36 | $connection = $this->entityManager->getConnection(); |
| 37 | |
| 38 | // $now is the run's frozen cutoff reference; the caller passes it so all windows of a run |
| 39 | // (including resumes) share the same dayAgo and match the stored baseline exactly. |
| 40 | $dayAgoIso = Carbon::createFromTimestamp(($now ?? new Carbon())->getTimestamp())->subDay()->toDateTimeString(); |
| 41 | |
| 42 | $countSubscribersToUpdate = $this->countSubscribersInRange($startId, $endId); |
| 43 | if (!$countSubscribersToUpdate) { |
| 44 | return 0; |
| 45 | } |
| 46 | |
| 47 | $queryParams = [ |
| 48 | 'startId' => $startId, |
| 49 | 'endId' => $endId, |
| 50 | 'dayAgo' => $dayAgoIso, |
| 51 | ]; |
| 52 | if ($dateLastProcessed) { |
| 53 | $carbonDateLastProcessed = Carbon::createFromTimestamp($dateLastProcessed->getTimestamp()); |
| 54 | $dateFromIso = ($carbonDateLastProcessed->subDay())->toDateTimeString(); |
| 55 | $queryParams['dateFrom'] = $dateFromIso; |
| 56 | } |
| 57 | // If $dateLastProcessed provided, increment value, otherwise count all and reset value |
| 58 | $initUpdateValue = $dateLastProcessed ? 's.email_count' : ''; |
| 59 | $dateLastProcessedSql = $dateLastProcessed ? ' AND st.processed_at >= :dateFrom' : ''; |
| 60 | |
| 61 | $connection->executeStatement( |
| 62 | " |
| 63 | UPDATE {$this->subscribersTable} as s |
| 64 | JOIN ( |
| 65 | -- STRAIGHT_JOIN pins the join order to subscribers -> sts -> scheduled_tasks so each |
| 66 | -- batch only scans its own subscribers' rows. Without it the optimizer may lead with |
| 67 | -- scheduled_tasks (type='sending' looks selective but isn't) and re-scan the whole |
| 68 | -- sending history on every batch -- observed on a particular site with heavy history. |
| 69 | SELECT STRAIGHT_JOIN s.id, COUNT(st.id) as email_count |
| 70 | FROM {$this->subscribersTable} as s |
| 71 | JOIN {$scheduledTaskSubscribersTable} as sts ON s.id = sts.subscriber_id |
| 72 | JOIN {$this->scheduledTasksTable} as st ON st.id = sts.task_id |
| 73 | WHERE s.id >= :startId |
| 74 | AND s.id <= :endId |
| 75 | AND st.type = 'sending' |
| 76 | AND st.processed_at IS NOT NULL |
| 77 | AND st.processed_at < :dayAgo |
| 78 | {$dateLastProcessedSql} |
| 79 | GROUP BY s.id |
| 80 | ) counts ON counts.id = s.id |
| 81 | SET s.email_count = {$initUpdateValue} + IFNULL(counts.email_count, 0) |
| 82 | ", |
| 83 | $queryParams |
| 84 | ); |
| 85 | |
| 86 | return $countSubscribersToUpdate; |
| 87 | } |
| 88 | |
| 89 | public function hasNewSendingTasksSince(\DateTimeInterface $dateLastProcessed, ?\DateTimeInterface $now = null): bool { |
| 90 | $carbonDateLastProcessed = Carbon::createFromTimestamp($dateLastProcessed->getTimestamp()); |
| 91 | $dateFromIso = ($carbonDateLastProcessed->subDay())->toDateTimeString(); |
| 92 | $queryParams['dateFrom'] = $dateFromIso; |
| 93 | $dayAgoIso = Carbon::createFromTimestamp(($now ?? new Carbon())->getTimestamp())->subDay()->toDateTimeString(); |
| 94 | $queryParams['dayAgo'] = $dayAgoIso; |
| 95 | |
| 96 | $result = $this->entityManager->getConnection()->executeQuery( |
| 97 | " |
| 98 | SELECT count(id) FROM {$this->scheduledTasksTable} |
| 99 | WHERE type = 'sending' |
| 100 | AND processed_at IS NOT NULL |
| 101 | AND processed_at < :dayAgo |
| 102 | AND processed_at >= :dateFrom |
| 103 | ", |
| 104 | $queryParams |
| 105 | )->fetchNumeric(); |
| 106 | |
| 107 | /** @var int[] $result - it's required for PHPStan */ |
| 108 | return is_array($result) && isset($result[0]) && ((int)$result[0] > 0); |
| 109 | } |
| 110 | |
| 111 | private function countSubscribersInRange(int $startId, int $endId): int { |
| 112 | $result = $this->entityManager->getConnection()->executeQuery( |
| 113 | " |
| 114 | SELECT COUNT(s.id) FROM {$this->subscribersTable} as s |
| 115 | WHERE s.id >= :startId |
| 116 | AND s.id <= :endId |
| 117 | ", |
| 118 | [ |
| 119 | 'startId' => $startId, |
| 120 | 'endId' => $endId, |
| 121 | ], |
| 122 | [ |
| 123 | 'startId' => ParameterType::INTEGER, |
| 124 | 'endId' => ParameterType::INTEGER, |
| 125 | ] |
| 126 | )->fetchNumeric(); |
| 127 | |
| 128 | /** @var int[] $result - it's required for PHPStan */ |
| 129 | return is_array($result) && isset($result[0]) ? intval($result[0]) : 0; |
| 130 | } |
| 131 | } |
| 132 |