Track
4 days ago
GATracking.php
6 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 days ago
StatisticsUnsubscribesRepository.php
2 months ago
StatisticsWooCommercePurchasesRepository.php
1 year ago
UnsubscribeReasonTracker.php
2 months ago
UserAgentsRepository.php
1 year ago
index.php
3 years ago
StatisticsClicksRepository.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\NewsletterEntity; |
| 10 | use MailPoet\Entities\NewsletterLinkEntity; |
| 11 | use MailPoet\Entities\SendingQueueEntity; |
| 12 | use MailPoet\Entities\StatisticsClickEntity; |
| 13 | use MailPoet\Entities\SubscriberEntity; |
| 14 | use MailPoet\Entities\UserAgentEntity; |
| 15 | use MailPoetVendor\Doctrine\ORM\QueryBuilder; |
| 16 | |
| 17 | /** |
| 18 | * @extends Repository<StatisticsClickEntity> |
| 19 | */ |
| 20 | class StatisticsClicksRepository extends Repository { |
| 21 | protected function getEntityClassName(): string { |
| 22 | return StatisticsClickEntity::class; |
| 23 | } |
| 24 | |
| 25 | public function createOrUpdateClickCount( |
| 26 | NewsletterLinkEntity $link, |
| 27 | SubscriberEntity $subscriber, |
| 28 | NewsletterEntity $newsletter, |
| 29 | SendingQueueEntity $queue, |
| 30 | ?UserAgentEntity $userAgent |
| 31 | ): StatisticsClickEntity { |
| 32 | $statistics = $this->findOneBy([ |
| 33 | 'link' => $link, |
| 34 | 'newsletter' => $newsletter, |
| 35 | 'subscriber' => $subscriber, |
| 36 | 'queue' => $queue, |
| 37 | ]); |
| 38 | if (!$statistics instanceof StatisticsClickEntity) { |
| 39 | $statistics = new StatisticsClickEntity($newsletter, $queue, $subscriber, $link, 1); |
| 40 | if ($userAgent) { |
| 41 | $statistics->setUserAgent($userAgent); |
| 42 | $statistics->setUserAgentType($userAgent->getUserAgentType()); |
| 43 | } |
| 44 | $this->persist($statistics); |
| 45 | } else { |
| 46 | $statistics->setCount($statistics->getCount() + 1); |
| 47 | } |
| 48 | return $statistics; |
| 49 | } |
| 50 | |
| 51 | public function getAllForSubscriber(SubscriberEntity $subscriber): QueryBuilder { |
| 52 | return $this->entityManager->createQueryBuilder() |
| 53 | ->select('clicks.id id, queue.newsletterRenderedSubject, clicks.createdAt, link.url, userAgent.userAgent') |
| 54 | ->from(StatisticsClickEntity::class, 'clicks') |
| 55 | ->join('clicks.queue', 'queue') |
| 56 | ->join('clicks.link', 'link') |
| 57 | ->leftJoin('clicks.userAgent', 'userAgent') |
| 58 | ->where('clicks.subscriber = :subscriber') |
| 59 | ->orderBy('link.url') |
| 60 | ->setParameter('subscriber', $subscriber->getId()); |
| 61 | } |
| 62 | |
| 63 | /** |
| 64 | * @param SubscriberEntity $subscriber |
| 65 | * @param \DateTimeInterface $from |
| 66 | * @param \DateTimeInterface $to |
| 67 | * @return StatisticsClickEntity[] |
| 68 | */ |
| 69 | public function findLatestPerNewsletterBySubscriber(SubscriberEntity $subscriber, \DateTimeInterface $from, \DateTimeInterface $to): array { |
| 70 | // subquery to find latest click IDs for each newsletter |
| 71 | $latestClickIdsPerNewsletterQuery = $this->entityManager->createQueryBuilder() |
| 72 | ->select('MAX(clicks.id)') |
| 73 | ->from(StatisticsClickEntity::class, 'clicks') |
| 74 | ->where('clicks.subscriber = :subscriber') |
| 75 | ->andWhere('clicks.updatedAt > :from') |
| 76 | ->andWhere('clicks.updatedAt < :to') |
| 77 | ->groupBy('clicks.newsletter'); |
| 78 | |
| 79 | $expr = $this->entityManager->getExpressionBuilder(); |
| 80 | return $this->entityManager->createQueryBuilder() |
| 81 | ->select('c') |
| 82 | ->from(StatisticsClickEntity::class, 'c') |
| 83 | ->where( |
| 84 | $expr->in( |
| 85 | 'c.id', |
| 86 | $latestClickIdsPerNewsletterQuery->getDQL() |
| 87 | ) |
| 88 | ) |
| 89 | ->setParameter('subscriber', $subscriber) |
| 90 | ->setParameter('from', $from->format('Y-m-d H:i:s')) |
| 91 | ->setParameter('to', $to->format('Y-m-d H:i:s')) |
| 92 | ->getQuery() |
| 93 | ->getResult(); |
| 94 | } |
| 95 | |
| 96 | /** @param int[] $ids */ |
| 97 | public function deleteByNewsletterIds(array $ids): void { |
| 98 | $this->entityManager->createQueryBuilder() |
| 99 | ->delete(StatisticsClickEntity::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 (StatisticsClickEntity $entity) use ($ids) { |
| 107 | $newsletter = $entity->getNewsletter(); |
| 108 | return $newsletter && in_array($newsletter->getId(), $ids, true); |
| 109 | }); |
| 110 | } |
| 111 | } |
| 112 |