ConfirmationEmailTemplate
1 month ago
ImportExport
2 weeks ago
RestApi
3 days ago
Statistics
1 month ago
BulkActionController.php
3 days ago
BulkActionException.php
1 month 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
4 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
3 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
3 days ago
TrackingConsentController.php
4 days ago
index.php
3 years ago
EngagementDataBackfiller.php
211 lines
| 1 | <?php declare(strict_types = 1); |
| 2 | |
| 3 | namespace MailPoet\Subscribers; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use MailPoet\Entities\StatisticsClickEntity; |
| 9 | use MailPoet\Entities\StatisticsNewsletterEntity; |
| 10 | use MailPoet\Entities\StatisticsOpenEntity; |
| 11 | use MailPoet\Entities\SubscriberEntity; |
| 12 | use MailPoet\Segments\DynamicSegments\Filters\FilterHelper; |
| 13 | use MailPoet\Segments\DynamicSegments\Filters\WooFilterHelper; |
| 14 | use MailPoet\WooCommerce\Helper; |
| 15 | use MailPoetVendor\Carbon\Carbon; |
| 16 | use MailPoetVendor\Doctrine\DBAL\ArrayParameterType; |
| 17 | use MailPoetVendor\Doctrine\DBAL\Result; |
| 18 | use MailPoetVendor\Doctrine\ORM\EntityManager; |
| 19 | |
| 20 | class EngagementDataBackfiller { |
| 21 | /** @var EntityManager */ |
| 22 | private $entityManager; |
| 23 | |
| 24 | /** @var Helper */ |
| 25 | private $wcHelper; |
| 26 | |
| 27 | /** @var WooFilterHelper */ |
| 28 | private $wooFilterHelper; |
| 29 | |
| 30 | /** @var FilterHelper */ |
| 31 | private $filterHelper; |
| 32 | |
| 33 | /** @var int */ |
| 34 | private $lastProcessedSubscriberId = 0; |
| 35 | |
| 36 | public function __construct( |
| 37 | EntityManager $entityManager, |
| 38 | WooFilterHelper $wooFilterHelper, |
| 39 | FilterHelper $filterHelper, |
| 40 | Helper $wcHelper |
| 41 | ) { |
| 42 | $this->entityManager = $entityManager; |
| 43 | $this->wcHelper = $wcHelper; |
| 44 | $this->wooFilterHelper = $wooFilterHelper; |
| 45 | $this->filterHelper = $filterHelper; |
| 46 | } |
| 47 | |
| 48 | /** |
| 49 | * @return SubscriberEntity[] |
| 50 | */ |
| 51 | public function getBatch(int $lastProcessedId = 0, int $batchSize = 100): array { |
| 52 | $subscribers = $this->entityManager->createQueryBuilder() |
| 53 | ->select('PARTIAL s.{id, email, lastPurchaseAt, lastClickAt, lastOpenAt, lastSendingAt}') |
| 54 | ->from(SubscriberEntity::class, 's') |
| 55 | ->where('s.id > :lastProcessedId') |
| 56 | ->orderBy('s.id', 'ASC') |
| 57 | ->setMaxResults($batchSize) |
| 58 | ->setParameter('lastProcessedId', $lastProcessedId) |
| 59 | ->getQuery() |
| 60 | ->getResult(); |
| 61 | if (!is_array($subscribers)) { |
| 62 | return []; |
| 63 | } |
| 64 | /** @var SubscriberEntity[] $subscribers */ |
| 65 | return $subscribers; |
| 66 | } |
| 67 | |
| 68 | /** |
| 69 | * @param SubscriberEntity[] $subscribers |
| 70 | * |
| 71 | * @return void |
| 72 | */ |
| 73 | public function updateBatch(array $subscribers): void { |
| 74 | $subscriberIds = array_map(function (SubscriberEntity $subscriber) { |
| 75 | return $subscriber->getId(); |
| 76 | }, $subscribers); |
| 77 | |
| 78 | $clickData = $this->getClickDataForBatch($subscriberIds); |
| 79 | $openData = $this->getOpenDataForBatch($subscriberIds); |
| 80 | $sendingData = $this->getSendingDataForBatch($subscriberIds); |
| 81 | $purchaseData = $this->getPurchaseDataForBatch($subscriberIds); |
| 82 | |
| 83 | foreach ($subscribers as $subscriber) { |
| 84 | if ($subscriber->getLastPurchaseAt() === null && isset($purchaseData[$subscriber->getId()]['last_purchase_at'])) { |
| 85 | $purchaseDate = new Carbon($purchaseData[$subscriber->getId()]['last_purchase_at']); |
| 86 | $subscriber->setLastPurchaseAt($purchaseDate); |
| 87 | } |
| 88 | if ($subscriber->getLastOpenAt() === null && isset($openData[$subscriber->getId()]['last_open_at'])) { |
| 89 | $openDate = new Carbon($openData[$subscriber->getId()]['last_open_at']); |
| 90 | $subscriber->setLastOpenAt($openDate); |
| 91 | } |
| 92 | if ($subscriber->getLastClickAt() === null && isset($clickData[$subscriber->getId()]['last_click_at'])) { |
| 93 | $clickDate = new Carbon($clickData[$subscriber->getId()]['last_click_at']); |
| 94 | $subscriber->setLastClickAt($clickDate); |
| 95 | } |
| 96 | if ($subscriber->getLastSendingAt() === null && isset($sendingData[$subscriber->getId()]['last_sending_at'])) { |
| 97 | $sendingDate = new Carbon($sendingData[$subscriber->getId()]['last_sending_at']); |
| 98 | $subscriber->setLastSendingAt($sendingDate); |
| 99 | } |
| 100 | if (is_int($subscriber->getId())) { |
| 101 | $this->lastProcessedSubscriberId = $subscriber->getId(); |
| 102 | } |
| 103 | } |
| 104 | |
| 105 | $this->entityManager->flush(); |
| 106 | } |
| 107 | |
| 108 | public function getClickDataForBatch(array $subscriberIds): array { |
| 109 | $subscribersTable = $this->filterHelper->getSubscribersTable(); |
| 110 | $clicksTable = $this->filterHelper->getTableForEntity(StatisticsClickEntity::class); |
| 111 | |
| 112 | $query = $this |
| 113 | ->entityManager |
| 114 | ->getConnection() |
| 115 | ->createQueryBuilder() |
| 116 | ->select("$subscribersTable.id, MAX(clicks.created_at) as last_click_at") |
| 117 | ->from($subscribersTable) |
| 118 | ->innerJoin($subscribersTable, $clicksTable, 'clicks', "$subscribersTable.id = clicks.subscriber_id") |
| 119 | ->andWhere("$subscribersTable.id IN (:subscriberIds)") |
| 120 | ->setParameter('subscriberIds', $subscriberIds, ArrayParameterType::INTEGER) |
| 121 | ->groupBy("$subscribersTable.id"); |
| 122 | |
| 123 | $result = $query->execute(); |
| 124 | if ($result instanceof Result) { |
| 125 | return $result->fetchAllAssociativeIndexed(); |
| 126 | } |
| 127 | return []; |
| 128 | } |
| 129 | |
| 130 | public function getPurchaseDataForBatch(array $subscriberIds): array { |
| 131 | if (!$this->wcHelper->isWooCommerceActive()) { |
| 132 | return []; |
| 133 | } |
| 134 | |
| 135 | $subscribersTable = $this->filterHelper->getSubscribersTable(); |
| 136 | |
| 137 | $query = $this |
| 138 | ->entityManager |
| 139 | ->getConnection() |
| 140 | ->createQueryBuilder() |
| 141 | // The orderStats alias comes from wooFilterHelper->applyOrderStatusFilter, which calls wooFilterHelper->applyCustomerOrderJoin |
| 142 | ->select("$subscribersTable.id, MAX(orderStats.date_created) as last_purchase_at") |
| 143 | ->from($subscribersTable) |
| 144 | ->andWhere("$subscribersTable.id IN (:subscriberIds)") |
| 145 | ->setParameter('subscriberIds', $subscriberIds, ArrayParameterType::INTEGER); |
| 146 | $this->wooFilterHelper->applyOrderStatusFilter($query); |
| 147 | $query->groupBy("$subscribersTable.id"); |
| 148 | |
| 149 | $result = $query->execute(); |
| 150 | if ($result instanceof Result) { |
| 151 | return $result->fetchAllAssociativeIndexed(); |
| 152 | } |
| 153 | return []; |
| 154 | } |
| 155 | |
| 156 | public function getOpenDataForBatch(array $subscriberIds): array { |
| 157 | $subscribersTable = $this->filterHelper->getSubscribersTable(); |
| 158 | $opensTable = $this->filterHelper->getTableForEntity(StatisticsOpenEntity::class); |
| 159 | |
| 160 | $query = $this |
| 161 | ->entityManager |
| 162 | ->getConnection() |
| 163 | ->createQueryBuilder() |
| 164 | ->select("$subscribersTable.id, MAX(opens.created_at) as last_open_at") |
| 165 | ->from($subscribersTable) |
| 166 | ->innerJoin($subscribersTable, $opensTable, 'opens', "$subscribersTable.id = opens.subscriber_id") |
| 167 | ->andWhere("$subscribersTable.id IN (:subscriberIds)") |
| 168 | ->setParameter('subscriberIds', $subscriberIds, ArrayParameterType::INTEGER) |
| 169 | ->groupBy("$subscribersTable.id"); |
| 170 | |
| 171 | $result = $query->execute(); |
| 172 | if ($result instanceof Result) { |
| 173 | return $result->fetchAllAssociativeIndexed(); |
| 174 | } |
| 175 | return []; |
| 176 | } |
| 177 | |
| 178 | public function getSendingDataForBatch(array $subscriberIds): array { |
| 179 | $subscribersTable = $this->filterHelper->getSubscribersTable(); |
| 180 | $sendsTable = $this->filterHelper->getTableForEntity(StatisticsNewsletterEntity::class); |
| 181 | |
| 182 | $query = $this |
| 183 | ->entityManager |
| 184 | ->getConnection() |
| 185 | ->createQueryBuilder() |
| 186 | ->select("$subscribersTable.id, MAX(sends.sent_at) as last_sending_at") |
| 187 | ->from($subscribersTable) |
| 188 | ->innerJoin($subscribersTable, $sendsTable, 'sends', "$subscribersTable.id = sends.subscriber_id") |
| 189 | ->andWhere("$subscribersTable.id IN (:subscriberIds)") |
| 190 | ->setParameter('subscriberIds', $subscriberIds, ArrayParameterType::INTEGER) |
| 191 | ->groupBy("$subscribersTable.id"); |
| 192 | |
| 193 | $result = $query->execute(); |
| 194 | if ($result instanceof Result) { |
| 195 | return $result->fetchAllAssociativeIndexed(); |
| 196 | } |
| 197 | return []; |
| 198 | } |
| 199 | |
| 200 | /** |
| 201 | * @return int |
| 202 | */ |
| 203 | public function getLastProcessedSubscriberId(): int { |
| 204 | return $this->lastProcessedSubscriberId; |
| 205 | } |
| 206 | |
| 207 | public function setLastProcessedSubscriberId(int $id): void { |
| 208 | $this->lastProcessedSubscriberId = $id; |
| 209 | } |
| 210 | } |
| 211 |