95 lines
3.2 KiB
PHP
95 lines
3.2 KiB
PHP
<?php declare(strict_types = 1);
|
|
|
|
namespace MailPoet\Statistics;
|
|
|
|
if (!defined('ABSPATH')) exit;
|
|
|
|
|
|
use MailPoet\Doctrine\Repository;
|
|
use MailPoet\Entities\NewsletterEntity;
|
|
use MailPoet\Entities\NewsletterLinkEntity;
|
|
use MailPoet\Entities\SendingQueueEntity;
|
|
use MailPoet\Entities\StatisticsClickEntity;
|
|
use MailPoet\Entities\SubscriberEntity;
|
|
use MailPoet\Entities\UserAgentEntity;
|
|
use MailPoetVendor\Doctrine\ORM\QueryBuilder;
|
|
|
|
/**
|
|
* @extends Repository<StatisticsClickEntity>
|
|
*/
|
|
class StatisticsClicksRepository extends Repository {
|
|
protected function getEntityClassName(): string {
|
|
return StatisticsClickEntity::class;
|
|
}
|
|
|
|
public function createOrUpdateClickCount(
|
|
NewsletterLinkEntity $link,
|
|
SubscriberEntity $subscriber,
|
|
NewsletterEntity $newsletter,
|
|
SendingQueueEntity $queue,
|
|
?UserAgentEntity $userAgent
|
|
): StatisticsClickEntity {
|
|
$statistics = $this->findOneBy([
|
|
'link' => $link,
|
|
'newsletter' => $newsletter,
|
|
'subscriber' => $subscriber,
|
|
'queue' => $queue,
|
|
]);
|
|
if (!$statistics instanceof StatisticsClickEntity) {
|
|
$statistics = new StatisticsClickEntity($newsletter, $queue, $subscriber, $link, 1);
|
|
if ($userAgent) {
|
|
$statistics->setUserAgent($userAgent);
|
|
$statistics->setUserAgentType($userAgent->getUserAgentType());
|
|
}
|
|
$this->persist($statistics);
|
|
} else {
|
|
$statistics->setCount($statistics->getCount() + 1);
|
|
}
|
|
return $statistics;
|
|
}
|
|
|
|
public function getAllForSubscriber(SubscriberEntity $subscriber): QueryBuilder {
|
|
return $this->entityManager->createQueryBuilder()
|
|
->select('clicks.id id, queue.newsletterRenderedSubject, clicks.createdAt, link.url, userAgent.userAgent')
|
|
->from(StatisticsClickEntity::class, 'clicks')
|
|
->join('clicks.queue', 'queue')
|
|
->join('clicks.link', 'link')
|
|
->leftJoin('clicks.userAgent', 'userAgent')
|
|
->where('clicks.subscriber = :subscriber')
|
|
->orderBy('link.url')
|
|
->setParameter('subscriber', $subscriber->getId());
|
|
}
|
|
|
|
/**
|
|
* @param SubscriberEntity $subscriber
|
|
* @param \DateTimeInterface $from
|
|
* @param \DateTimeInterface $to
|
|
* @return StatisticsClickEntity[]
|
|
*/
|
|
public function findLatestPerNewsletterBySubscriber(SubscriberEntity $subscriber, \DateTimeInterface $from, \DateTimeInterface $to): array {
|
|
// subquery to find latest click IDs for each newsletter
|
|
$latestClickIdsPerNewsletterQuery = $this->entityManager->createQueryBuilder()
|
|
->select('MAX(clicks.id)')
|
|
->from(StatisticsClickEntity::class, 'clicks')
|
|
->where('clicks.subscriber = :subscriber')
|
|
->andWhere('clicks.updatedAt > :from')
|
|
->andWhere('clicks.updatedAt < :to')
|
|
->groupBy('clicks.newsletter');
|
|
|
|
$expr = $this->entityManager->getExpressionBuilder();
|
|
return $this->entityManager->createQueryBuilder()
|
|
->select('c')
|
|
->from(StatisticsClickEntity::class, 'c')
|
|
->where(
|
|
$expr->in(
|
|
'c.id',
|
|
$latestClickIdsPerNewsletterQuery->getDQL()
|
|
)
|
|
)
|
|
->setParameter('subscriber', $subscriber)
|
|
->setParameter('from', $from->format('Y-m-d H:i:s'))
|
|
->setParameter('to', $to->format('Y-m-d H:i:s'))
|
|
->getQuery()
|
|
->getResult();
|
|
}
|
|
}
|