Track
1 month ago
GATracking.php
7 months ago
StatisticsBouncesRepository.php
3 years ago
StatisticsClicksRepository.php
2 years ago
StatisticsFormsRepository.php
3 years ago
StatisticsNewslettersRepository.php
1 year ago
StatisticsOpensRepository.php
3 months ago
StatisticsUnsubscribesRepository.php
3 months ago
StatisticsWooCommercePurchasesRepository.php
2 years ago
UnsubscribeReasonTracker.php
3 months ago
UserAgentsRepository.php
1 year ago
index.php
3 years ago
StatisticsOpensRepository.php
112 lines
| 1 | <?php declare(strict_types = 1); |
| 2 | |
| 3 | namespace MailPoet\Statistics; |
| 4 | |
| 5 | if (!defined('ABSPATH')) exit; |
| 6 | |
| 7 | |
| 8 | use MailPoet\Doctrine\Repository; |
| 9 | use MailPoet\Entities\SegmentEntity; |
| 10 | use MailPoet\Entities\StatisticsOpenEntity; |
| 11 | use MailPoet\Entities\SubscriberEntity; |
| 12 | use MailPoet\Subscribers\Statistics\SubscriberStatisticsRepository; |
| 13 | use MailPoetVendor\Doctrine\ORM\EntityManager; |
| 14 | use MailPoetVendor\Doctrine\ORM\QueryBuilder; |
| 15 | |
| 16 | /** |
| 17 | * @extends Repository<StatisticsOpenEntity> |
| 18 | */ |
| 19 | class StatisticsOpensRepository extends Repository { |
| 20 | /** @var SubscriberStatisticsRepository */ |
| 21 | private $subscriberStatisticsRepository; |
| 22 | |
| 23 | public function __construct( |
| 24 | EntityManager $entityManager, |
| 25 | SubscriberStatisticsRepository $subscriberStatisticsRepository |
| 26 | ) { |
| 27 | parent::__construct($entityManager); |
| 28 | $this->entityManager = $entityManager; |
| 29 | $this->subscriberStatisticsRepository = $subscriberStatisticsRepository; |
| 30 | } |
| 31 | |
| 32 | protected function getEntityClassName(): string { |
| 33 | return StatisticsOpenEntity::class; |
| 34 | } |
| 35 | |
| 36 | public function recalculateSubscriberScore(SubscriberEntity $subscriber): void { |
| 37 | $subscriber->setEngagementScoreUpdatedAt(new \DateTimeImmutable()); |
| 38 | $yearAgo = new \DateTimeImmutable('-1 year'); |
| 39 | $newslettersSentCount = $this->subscriberStatisticsRepository->getTotalSentCount($subscriber, $yearAgo); |
| 40 | if ($newslettersSentCount < SubscriberStatisticsRepository::MIN_SENT_EMAILS_FOR_ENGAGEMENT_SCORE) { |
| 41 | $subscriber->setEngagementScore(null); |
| 42 | $this->entityManager->flush(); |
| 43 | return; |
| 44 | } |
| 45 | $opensCount = $this->subscriberStatisticsRepository->getStatisticsOpenCount($subscriber, $yearAgo); |
| 46 | $score = ($opensCount / $newslettersSentCount) * 100; |
| 47 | $subscriber->setEngagementScore($score); |
| 48 | $this->entityManager->flush(); |
| 49 | } |
| 50 | |
| 51 | public function resetSubscribersScoreCalculation() { |
| 52 | $this->entityManager->createQueryBuilder()->update(SubscriberEntity::class, 's') |
| 53 | ->set('s.engagementScoreUpdatedAt', ':updatedAt') |
| 54 | ->setParameter('updatedAt', null) |
| 55 | ->getQuery()->execute(); |
| 56 | } |
| 57 | |
| 58 | public function recalculateSegmentScore(SegmentEntity $segment): void { |
| 59 | $segment->setAverageEngagementScoreUpdatedAt(new \DateTimeImmutable()); |
| 60 | $avgScore = $this |
| 61 | ->entityManager |
| 62 | ->createQueryBuilder() |
| 63 | ->select('avg(subscriber.engagementScore)') |
| 64 | ->from(SubscriberEntity::class, 'subscriber') |
| 65 | ->join('subscriber.subscriberSegments', 'subscriberSegments') |
| 66 | ->where('subscriberSegments.segment = :segment') |
| 67 | ->andWhere('subscriber.status = :subscribed') |
| 68 | ->andWhere('subscriber.deletedAt IS NULL') |
| 69 | ->andWhere('subscriberSegments.status = :subscribed') |
| 70 | ->setParameter('segment', $segment) |
| 71 | ->setParameter('subscribed', SubscriberEntity::STATUS_SUBSCRIBED) |
| 72 | ->getQuery() |
| 73 | ->getSingleScalarResult(); |
| 74 | $segment->setAverageEngagementScore($avgScore === null ? $avgScore : (float)$avgScore); |
| 75 | $this->entityManager->flush(); |
| 76 | } |
| 77 | |
| 78 | public function resetSegmentsScoreCalculation(): void { |
| 79 | $this->entityManager->createQueryBuilder()->update(SegmentEntity::class, 's') |
| 80 | ->set('s.averageEngagementScoreUpdatedAt', ':updatedAt') |
| 81 | ->setParameter('updatedAt', null) |
| 82 | ->getQuery()->execute(); |
| 83 | } |
| 84 | |
| 85 | public function getAllForSubscriber(SubscriberEntity $subscriber): QueryBuilder { |
| 86 | return $this->entityManager->createQueryBuilder() |
| 87 | ->select('opens.id id, queue.newsletterRenderedSubject, opens.createdAt, userAgent.userAgent') |
| 88 | ->from(StatisticsOpenEntity::class, 'opens') |
| 89 | ->join('opens.queue', 'queue') |
| 90 | ->leftJoin('opens.userAgent', 'userAgent') |
| 91 | ->where('opens.subscriber = :subscriber') |
| 92 | ->orderBy('queue.newsletterRenderedSubject') |
| 93 | ->setParameter('subscriber', $subscriber->getId()); |
| 94 | } |
| 95 | |
| 96 | /** @param int[] $ids */ |
| 97 | public function deleteByNewsletterIds(array $ids): void { |
| 98 | $this->entityManager->createQueryBuilder() |
| 99 | ->delete(StatisticsOpenEntity::class, 's') |
| 100 | ->where('s.newsletter IN (:ids)') |
| 101 | ->setParameter('ids', $ids) |
| 102 | ->getQuery() |
| 103 | ->execute(); |
| 104 | |
| 105 | // delete was done via DQL, make sure the entities are also detached from the entity manager |
| 106 | $this->detachAll(function (StatisticsOpenEntity $entity) use ($ids) { |
| 107 | $newsletter = $entity->getNewsletter(); |
| 108 | return $newsletter && in_array($newsletter->getId(), $ids, true); |
| 109 | }); |
| 110 | } |
| 111 | } |
| 112 |