Export
1 week ago
NewsletterStatistics.php
3 years ago
NewsletterStatisticsRepository.php
2 weeks ago
WooCommerceRevenue.php
3 years ago
index.php
3 years ago
NewsletterStatisticsRepository.php
342 lines
| 1 | <?php // phpcs:ignore SlevomatCodingStandard.TypeHints.DeclareStrictTypes.DeclareStrictTypesMissing |
| 2 | |
| 3 | namespace MailPoet\Newsletter\Statistics; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use MailPoet\Doctrine\Repository; |
| 9 | use MailPoet\Entities\NewsletterEntity; |
| 10 | use MailPoet\Entities\ScheduledTaskEntity; |
| 11 | use MailPoet\Entities\SendingQueueEntity; |
| 12 | use MailPoet\Entities\StatisticsBounceEntity; |
| 13 | use MailPoet\Entities\StatisticsClickEntity; |
| 14 | use MailPoet\Entities\StatisticsNewsletterEntity; |
| 15 | use MailPoet\Entities\StatisticsOpenEntity; |
| 16 | use MailPoet\Entities\StatisticsUnsubscribeEntity; |
| 17 | use MailPoet\Entities\StatisticsWooCommercePurchaseEntity; |
| 18 | use MailPoet\Entities\SubscriberEntity; |
| 19 | use MailPoet\Entities\UserAgentEntity; |
| 20 | use MailPoet\Settings\TrackingConfig; |
| 21 | use MailPoet\WooCommerce\Helper as WCHelper; |
| 22 | use MailPoet\WooCommerce\OrderAttributionRevenueReader; |
| 23 | use MailPoetVendor\Doctrine\ORM\EntityManager; |
| 24 | use MailPoetVendor\Doctrine\ORM\Query\Expr\Join; |
| 25 | use MailPoetVendor\Doctrine\ORM\QueryBuilder; |
| 26 | use MailPoetVendor\Doctrine\ORM\UnexpectedResultException; |
| 27 | |
| 28 | /** |
| 29 | * @extends Repository<NewsletterEntity> |
| 30 | */ |
| 31 | class NewsletterStatisticsRepository extends Repository { |
| 32 | |
| 33 | /** @var WCHelper */ |
| 34 | private $wcHelper; |
| 35 | |
| 36 | /** @var TrackingConfig */ |
| 37 | private $trackingConfig; |
| 38 | |
| 39 | /** @var OrderAttributionRevenueReader */ |
| 40 | private $orderAttributionRevenueReader; |
| 41 | |
| 42 | public function __construct( |
| 43 | EntityManager $entityManager, |
| 44 | WCHelper $wcHelper, |
| 45 | TrackingConfig $trackingConfig, |
| 46 | OrderAttributionRevenueReader $orderAttributionRevenueReader |
| 47 | ) { |
| 48 | parent::__construct($entityManager); |
| 49 | $this->wcHelper = $wcHelper; |
| 50 | $this->trackingConfig = $trackingConfig; |
| 51 | $this->orderAttributionRevenueReader = $orderAttributionRevenueReader; |
| 52 | } |
| 53 | |
| 54 | protected function getEntityClassName() { |
| 55 | return NewsletterEntity::class; |
| 56 | } |
| 57 | |
| 58 | public function getStatistics(NewsletterEntity $newsletter): NewsletterStatistics { |
| 59 | $stats = new NewsletterStatistics( |
| 60 | $this->getStatisticsClickCount($newsletter), |
| 61 | $this->getStatisticsOpenCount($newsletter), |
| 62 | $this->getStatisticsUnsubscribeCount($newsletter), |
| 63 | $this->getStatisticsBounceCount($newsletter), |
| 64 | $this->getTotalSentCount($newsletter), |
| 65 | $this->getWooCommerceRevenue($newsletter) |
| 66 | ); |
| 67 | $stats->setMachineOpenCount($this->getStatisticsMachineOpenCount($newsletter)); |
| 68 | return $stats; |
| 69 | } |
| 70 | |
| 71 | /** |
| 72 | * @param NewsletterEntity[] $newsletters |
| 73 | * @return NewsletterStatistics[] |
| 74 | */ |
| 75 | public function getBatchStatistics( |
| 76 | array $newsletters, |
| 77 | ?\DateTimeImmutable $from = null, |
| 78 | ?\DateTimeImmutable $to = null, |
| 79 | array $include = [ |
| 80 | 'totals', |
| 81 | StatisticsClickEntity::class, |
| 82 | StatisticsOpenEntity::class, |
| 83 | StatisticsUnsubscribeEntity::class, |
| 84 | StatisticsBounceEntity::class, |
| 85 | WooCommerceRevenue::class, |
| 86 | ] |
| 87 | ): array { |
| 88 | |
| 89 | $totalSentCounts = in_array('totals', $include, true) ? $this->getTotalSentCounts($newsletters, $from, $to) : []; |
| 90 | $clickCounts = in_array(StatisticsClickEntity::class, $include, true) ? $this->getStatisticCounts(StatisticsClickEntity::class, $newsletters, $from, $to) : []; |
| 91 | $openCounts = in_array(StatisticsOpenEntity::class, $include, true) ? $this->getStatisticCounts(StatisticsOpenEntity::class, $newsletters, $from, $to) : []; |
| 92 | $unsubscribeCounts = in_array(StatisticsUnsubscribeEntity::class, $include, true) ? $this->getStatisticCounts(StatisticsUnsubscribeEntity::class, $newsletters, $from, $to) : []; |
| 93 | $bounceCounts = in_array(StatisticsBounceEntity::class, $include, true) ? $this->getStatisticCounts(StatisticsBounceEntity::class, $newsletters, $from, $to) : []; |
| 94 | $wooCommerceRevenues = in_array(WooCommerceRevenue::class, $include, true) ? $this->getWooCommerceRevenues($newsletters, $from, $to) : []; |
| 95 | |
| 96 | $statistics = []; |
| 97 | foreach ($newsletters as $newsletter) { |
| 98 | $id = $newsletter->getId(); |
| 99 | $statistics[$id] = new NewsletterStatistics( |
| 100 | $clickCounts[$id] ?? 0, |
| 101 | $openCounts[$id] ?? 0, |
| 102 | $unsubscribeCounts[$id] ?? 0, |
| 103 | $bounceCounts[$id] ?? 0, |
| 104 | $totalSentCounts[$id] ?? 0, |
| 105 | $wooCommerceRevenues[$id] ?? null |
| 106 | ); |
| 107 | } |
| 108 | return $statistics; |
| 109 | } |
| 110 | |
| 111 | public function getTotalSentCount(NewsletterEntity $newsletter): int { |
| 112 | $counts = $this->getTotalSentCounts([$newsletter]); |
| 113 | return $counts[$newsletter->getId()] ?? 0; |
| 114 | } |
| 115 | |
| 116 | public function getStatisticsClickCount(NewsletterEntity $newsletter): int { |
| 117 | $counts = $this->getStatisticCounts(StatisticsClickEntity::class, [$newsletter]); |
| 118 | return $counts[$newsletter->getId()] ?? 0; |
| 119 | } |
| 120 | |
| 121 | public function getStatisticsOpenCount(NewsletterEntity $newsletter): int { |
| 122 | $counts = $this->getStatisticCounts(StatisticsOpenEntity::class, [$newsletter]); |
| 123 | return $counts[$newsletter->getId()] ?? 0; |
| 124 | } |
| 125 | |
| 126 | public function getStatisticsMachineOpenCount(NewsletterEntity $newsletter): int { |
| 127 | $qb = $this->getStatisticsQuery(StatisticsOpenEntity::class, [$newsletter]); |
| 128 | $result = $qb->andWhere('(stats.userAgentType = :userAgentType)') |
| 129 | ->setParameter('userAgentType', UserAgentEntity::USER_AGENT_TYPE_MACHINE) |
| 130 | ->getQuery() |
| 131 | ->getOneOrNullResult(); |
| 132 | |
| 133 | if (empty($result)) return 0; |
| 134 | return $result['cnt'] ?? 0; |
| 135 | } |
| 136 | |
| 137 | /** |
| 138 | * @param SubscriberEntity $subscriber |
| 139 | * @param int|null $limit |
| 140 | * @param int|null $offset |
| 141 | * @return list<array{newsletter_id: mixed, newsletter_rendered_subject: string|null, opened_at: \DateTimeInterface|null, sent_at: \DateTimeInterface}> |
| 142 | */ |
| 143 | public function getAllForSubscriber( |
| 144 | SubscriberEntity $subscriber, |
| 145 | ?int $limit = null, |
| 146 | ?int $offset = null |
| 147 | ): array { |
| 148 | return $this->entityManager->createQueryBuilder() |
| 149 | ->select('IDENTITY(statistics.newsletter) AS newsletter_id') |
| 150 | ->addSelect('opens.createdAt AS opened_at') |
| 151 | ->addSelect('queue.newsletterRenderedSubject AS newsletter_rendered_subject') |
| 152 | ->addSelect('statistics.sentAt AS sent_at') |
| 153 | ->from(StatisticsNewsletterEntity::class, 'statistics') |
| 154 | ->join(SendingQueueEntity::class, 'queue', Join::WITH, 'statistics.queue = queue') |
| 155 | ->leftJoin( |
| 156 | StatisticsOpenEntity::class, |
| 157 | 'opens', |
| 158 | Join::WITH, |
| 159 | 'statistics.newsletter = opens.newsletter AND statistics.subscriber = opens.subscriber' |
| 160 | ) |
| 161 | ->where('statistics.subscriber = :subscriber') |
| 162 | ->setParameter('subscriber', $subscriber) |
| 163 | ->addOrderBy('newsletter_id') |
| 164 | ->setMaxResults($limit) |
| 165 | ->setFirstResult($offset) |
| 166 | ->getQuery() |
| 167 | ->getResult(); |
| 168 | } |
| 169 | |
| 170 | public function getStatisticsUnsubscribeCount(NewsletterEntity $newsletter): int { |
| 171 | $counts = $this->getStatisticCounts(StatisticsUnsubscribeEntity::class, [$newsletter]); |
| 172 | return $counts[$newsletter->getId()] ?? 0; |
| 173 | } |
| 174 | |
| 175 | public function getStatisticsBounceCount(NewsletterEntity $newsletter): int { |
| 176 | $counts = $this->getStatisticCounts(StatisticsBounceEntity::class, [$newsletter]); |
| 177 | return $counts[$newsletter->getId()] ?? 0; |
| 178 | } |
| 179 | |
| 180 | public function getWooCommerceRevenue(NewsletterEntity $newsletter) { |
| 181 | $revenues = $this->getWooCommerceRevenues([$newsletter]); |
| 182 | return $revenues[$newsletter->getId()] ?? null; |
| 183 | } |
| 184 | |
| 185 | /** |
| 186 | * @param NewsletterEntity $newsletter |
| 187 | * @return int |
| 188 | */ |
| 189 | public function getChildrenCount(NewsletterEntity $newsletter) { |
| 190 | try { |
| 191 | return (int)$this->entityManager |
| 192 | ->createQueryBuilder() |
| 193 | ->select('COUNT(n.id) as cnt') |
| 194 | ->from(NewsletterEntity::class, 'n') |
| 195 | ->where('n.parent = :newsletter') |
| 196 | ->setParameter('newsletter', $newsletter) |
| 197 | ->getQuery() |
| 198 | ->getSingleScalarResult(); |
| 199 | } catch (UnexpectedResultException $e) { |
| 200 | return 0; |
| 201 | } |
| 202 | } |
| 203 | |
| 204 | private function getTotalSentCounts(array $newsletters, ?\DateTimeImmutable $from = null, ?\DateTimeImmutable $to = null): array { |
| 205 | $query = $this->doctrineRepository |
| 206 | ->createQueryBuilder('n') |
| 207 | ->select('n.id, SUM(q.countProcessed) AS cnt') |
| 208 | ->join('n.queues', 'q') |
| 209 | ->join('q.task', 't') |
| 210 | ->where('t.status = :status') |
| 211 | ->setParameter('status', ScheduledTaskEntity::STATUS_COMPLETED) |
| 212 | ->andWhere('q.newsletter IN (:newsletters)') |
| 213 | ->setParameter('newsletters', $newsletters) |
| 214 | ->groupBy('n.id'); |
| 215 | |
| 216 | if ($from && $to) { |
| 217 | $query->andWhere('q.createdAt BETWEEN :from AND :to') |
| 218 | ->setParameter('from', $from) |
| 219 | ->setParameter('to', $to); |
| 220 | } elseif ($from && $to === null) { |
| 221 | $query->andWhere('q.createdAt >= :from') |
| 222 | ->setParameter('from', $from); |
| 223 | } elseif ($from === null && $to) { |
| 224 | $query->andWhere('q.createdAt <= :to') |
| 225 | ->setParameter('to', $to); |
| 226 | } |
| 227 | |
| 228 | $results = $query->getQuery() |
| 229 | ->getResult(); |
| 230 | |
| 231 | $counts = []; |
| 232 | foreach ($results ?: [] as $result) { |
| 233 | $counts[(int)$result['id']] = (int)$result['cnt']; |
| 234 | } |
| 235 | return $counts; |
| 236 | } |
| 237 | |
| 238 | private function getStatisticCounts(string $statisticsEntityName, array $newsletters, ?\DateTimeImmutable $from = null, ?\DateTimeImmutable $to = null): array { |
| 239 | $qb = $this->getStatisticsQuery($statisticsEntityName, $newsletters); |
| 240 | if ( |
| 241 | $statisticsEntityName === StatisticsClickEntity::class |
| 242 | || ($statisticsEntityName === StatisticsOpenEntity::class && $this->trackingConfig->areOpensSeparated()) |
| 243 | ) { |
| 244 | $qb->andWhere('(stats.userAgentType = :userAgentType) OR (stats.userAgentType IS NULL)') |
| 245 | ->setParameter('userAgentType', UserAgentEntity::USER_AGENT_TYPE_HUMAN); |
| 246 | } |
| 247 | if ($from && $to) { |
| 248 | $qb->andWhere('stats.createdAt BETWEEN :from AND :to') |
| 249 | ->setParameter('from', $from) |
| 250 | ->setParameter('to', $to); |
| 251 | } elseif ($from && $to === null) { |
| 252 | $qb->andWhere('stats.createdAt >= :from') |
| 253 | ->setParameter('from', $from); |
| 254 | } elseif ($from === null && $to) { |
| 255 | $qb->andWhere('stats.createdAt <= :to') |
| 256 | ->setParameter('to', $to); |
| 257 | } |
| 258 | |
| 259 | $results = $qb |
| 260 | ->getQuery() |
| 261 | ->getResult(); |
| 262 | |
| 263 | $counts = []; |
| 264 | foreach ($results ?: [] as $result) { |
| 265 | $counts[(int)$result['id']] = (int)$result['cnt']; |
| 266 | } |
| 267 | return $counts; |
| 268 | } |
| 269 | |
| 270 | private function getStatisticsQuery(string $statisticsEntityName, array $newsletters): QueryBuilder { |
| 271 | return $this->entityManager->createQueryBuilder() |
| 272 | ->select('IDENTITY(stats.newsletter) AS id, COUNT(DISTINCT stats.subscriber) as cnt') |
| 273 | ->from($statisticsEntityName, 'stats') |
| 274 | ->where('stats.newsletter IN (:newsletters)') |
| 275 | ->groupBy('stats.newsletter') |
| 276 | ->setParameter('newsletters', $newsletters); |
| 277 | } |
| 278 | |
| 279 | private function getWooCommerceRevenues(array $newsletters, ?\DateTimeImmutable $from = null, ?\DateTimeImmutable $to = null) { |
| 280 | if (!$this->wcHelper->isWooCommerceActive()) { |
| 281 | return null; |
| 282 | } |
| 283 | |
| 284 | $newsletterIds = array_map(function(NewsletterEntity $newsletter): int { |
| 285 | return (int)$newsletter->getId(); |
| 286 | }, $newsletters); |
| 287 | $revenueStatus = $this->wcHelper->getPurchaseStates(); |
| 288 | $currency = $this->wcHelper->getWoocommerceCurrency(); |
| 289 | $wooBackedRevenues = $this->orderAttributionRevenueReader->getNewsletterRevenues($newsletterIds, $from, $to); |
| 290 | if (is_array($wooBackedRevenues)) { |
| 291 | $revenues = []; |
| 292 | foreach ($wooBackedRevenues as $newsletterId => $result) { |
| 293 | $revenues[(int)$newsletterId] = new WooCommerceRevenue( |
| 294 | $currency, |
| 295 | (float)$result['total'], |
| 296 | (int)$result['count'], |
| 297 | $this->wcHelper |
| 298 | ); |
| 299 | } |
| 300 | return $revenues; |
| 301 | } |
| 302 | |
| 303 | $query = $this->entityManager |
| 304 | ->createQueryBuilder() |
| 305 | ->select('IDENTITY(stats.newsletter) AS id, SUM(stats.orderPriceTotal) AS total, COUNT(stats.id) AS cnt') |
| 306 | ->from(StatisticsWooCommercePurchaseEntity::class, 'stats') |
| 307 | ->where('stats.newsletter IN (:newsletters)') |
| 308 | ->andWhere('stats.orderCurrency = :currency') |
| 309 | ->andWhere('stats.status IN (:revenue_status)') |
| 310 | ->setParameter('newsletters', $newsletters) |
| 311 | ->setParameter('currency', $currency) |
| 312 | ->setParameter('revenue_status', $revenueStatus) |
| 313 | ->groupBy('stats.newsletter'); |
| 314 | |
| 315 | if ($from && $to) { |
| 316 | $query->andWhere('stats.createdAt BETWEEN :from AND :to') |
| 317 | ->setParameter('from', $from) |
| 318 | ->setParameter('to', $to); |
| 319 | } elseif ($from && $to === null) { |
| 320 | $query->andWhere('stats.createdAt >= :from') |
| 321 | ->setParameter('from', $from); |
| 322 | } elseif ($from === null && $to) { |
| 323 | $query->andWhere('stats.createdAt <= :to') |
| 324 | ->setParameter('to', $to); |
| 325 | } |
| 326 | |
| 327 | $results = $query->getQuery() |
| 328 | ->getResult(); |
| 329 | |
| 330 | $revenues = []; |
| 331 | foreach ($results ?: [] as $result) { |
| 332 | $revenues[(int)$result['id']] = new WooCommerceRevenue( |
| 333 | $currency, |
| 334 | (float)$result['total'], |
| 335 | (int)$result['cnt'], |
| 336 | $this->wcHelper |
| 337 | ); |
| 338 | } |
| 339 | return $revenues; |
| 340 | } |
| 341 | } |
| 342 |