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
StatisticsWooCommercePurchasesRepository.php
137 lines
| 1 | <?php // phpcs:ignore SlevomatCodingStandard.TypeHints.DeclareStrictTypes.DeclareStrictTypesMissing |
| 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\SendingQueueEntity; |
| 11 | use MailPoet\Entities\StatisticsClickEntity; |
| 12 | use MailPoet\Entities\StatisticsWooCommercePurchaseEntity; |
| 13 | use MailPoet\WooCommerce\Helper; |
| 14 | use MailPoetVendor\Doctrine\DBAL\ArrayParameterType; |
| 15 | use MailPoetVendor\Doctrine\DBAL\ParameterType; |
| 16 | use MailPoetVendor\Doctrine\ORM\EntityManager; |
| 17 | |
| 18 | /** |
| 19 | * @extends Repository<StatisticsWooCommercePurchaseEntity> |
| 20 | */ |
| 21 | class StatisticsWooCommercePurchasesRepository extends Repository { |
| 22 | |
| 23 | /** @var Helper */ |
| 24 | private $wooCommerceHelper; |
| 25 | |
| 26 | public function __construct( |
| 27 | EntityManager $entityManager, |
| 28 | Helper $wooCommerceHelper |
| 29 | ) { |
| 30 | parent::__construct($entityManager); |
| 31 | $this->wooCommerceHelper = $wooCommerceHelper; |
| 32 | } |
| 33 | |
| 34 | protected function getEntityClassName() { |
| 35 | return StatisticsWooCommercePurchaseEntity::class; |
| 36 | } |
| 37 | |
| 38 | public function createOrUpdateByClickDataAndOrder(StatisticsClickEntity $click, \WC_Order $order) { |
| 39 | // search by subscriber and newsletter IDs (instead of click itself) to avoid duplicities |
| 40 | // when a new click from the subscriber appeared since last tracking for given newsletter |
| 41 | // (this will keep the originally tracked click - likely the click that led to the order) |
| 42 | $statistics = $this->findOneBy([ |
| 43 | 'orderId' => $order->get_id(), |
| 44 | 'subscriber' => $click->getSubscriber(), |
| 45 | 'newsletter' => $click->getNewsletter(), |
| 46 | ]); |
| 47 | |
| 48 | if (!$statistics instanceof StatisticsWooCommercePurchaseEntity) { |
| 49 | $newsletter = $click->getNewsletter(); |
| 50 | $queue = $click->getQueue(); |
| 51 | if ((!$newsletter instanceof NewsletterEntity) || (!$queue instanceof SendingQueueEntity)) return; |
| 52 | $statistics = new StatisticsWooCommercePurchaseEntity( |
| 53 | $newsletter, |
| 54 | $queue, |
| 55 | $click, |
| 56 | $order->get_id(), |
| 57 | $order->get_currency(), |
| 58 | (float)$order->get_remaining_refund_amount(), |
| 59 | $order->get_status() |
| 60 | ); |
| 61 | $this->persist($statistics); |
| 62 | } else { |
| 63 | $statistics->setOrderCurrency($order->get_currency()); |
| 64 | $statistics->setOrderPriceTotal((float)$order->get_remaining_refund_amount()); |
| 65 | $statistics->setStatus($order->get_status()); |
| 66 | } |
| 67 | $statistics->setSubscriber($click->getSubscriber()); |
| 68 | $this->flush(); |
| 69 | } |
| 70 | |
| 71 | public function getRevenuesByCampaigns(string $currency): array { |
| 72 | $revenueStatus = $this->wooCommerceHelper->getPurchaseStates(); |
| 73 | $revenueStatsTable = $this->entityManager->getClassMetadata(StatisticsWooCommercePurchaseEntity::class)->getTableName(); |
| 74 | $newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName(); |
| 75 | |
| 76 | // The "SELECT MIN(click_id)..." sub-query is used to count each purchase only once. |
| 77 | // In the data we track a purchase to multiple newsletters if clicks from multiple newsletters occurred. |
| 78 | /** @var array<int, array{revenue: float|int, campaign_id:int, orders_count:int}> $data */ |
| 79 | $data = $this->entityManager->getConnection()->executeQuery(' |
| 80 | SELECT |
| 81 | SUM(swp.order_price_total) AS revenue, |
| 82 | COALESCE(n.parent_id, swp.newsletter_id) AS campaign_id, |
| 83 | ( |
| 84 | CASE |
| 85 | WHEN n.type IS NULL THEN \'unknown\' |
| 86 | WHEN n.type = :notification_history_type THEN :notification_type |
| 87 | ELSE n.type |
| 88 | END |
| 89 | ) AS campaign_type, |
| 90 | COUNT(order_id) as orders_count |
| 91 | FROM ' . $revenueStatsTable . ' swp |
| 92 | LEFT JOIN ' . $newsletterTable . ' n ON |
| 93 | n.id = swp.newsletter_id |
| 94 | WHERE |
| 95 | swp.order_currency = :currency |
| 96 | AND swp.status IN (:revenue_status) |
| 97 | AND swp.click_id IN (SELECT MIN(click_id) FROM ' . $revenueStatsTable . ' ss GROUP BY order_id) |
| 98 | GROUP BY campaign_id, n.type; |
| 99 | ', [ |
| 100 | 'notification_history_type' => NewsletterEntity::TYPE_NOTIFICATION_HISTORY, |
| 101 | 'notification_type' => NewsletterEntity::TYPE_NOTIFICATION, |
| 102 | 'currency' => $currency, |
| 103 | 'revenue_status' => $revenueStatus, |
| 104 | ], [ |
| 105 | 'notification_history_type' => ParameterType::STRING, |
| 106 | 'notification_type' => ParameterType::STRING, |
| 107 | 'currency' => ParameterType::STRING, |
| 108 | 'revenue_status' => ArrayParameterType::STRING, |
| 109 | ])->fetchAllAssociative(); |
| 110 | |
| 111 | $data = array_map(function($row) { |
| 112 | $row['revenue'] = round(floatval($row['revenue']), 2); |
| 113 | $row['orders_count'] = intval($row['orders_count']); |
| 114 | return $row; |
| 115 | }, $data); |
| 116 | return $data; |
| 117 | } |
| 118 | |
| 119 | /** @param int[] $ids */ |
| 120 | public function removeNewsletterDataByNewsletterIds(array $ids): void { |
| 121 | $this->entityManager->createQueryBuilder() |
| 122 | ->update(StatisticsWooCommercePurchaseEntity::class, 'swp') |
| 123 | ->set('swp.newsletter', ':newsletter') |
| 124 | ->where('swp.newsletter IN (:ids)') |
| 125 | ->setParameter('newsletter', null) |
| 126 | ->setParameter('ids', $ids) |
| 127 | ->getQuery() |
| 128 | ->execute(); |
| 129 | |
| 130 | // update was done via DQL, make sure the entities are also refreshed in the entity manager |
| 131 | $this->refreshAll(function (StatisticsWooCommercePurchaseEntity $entity) use ($ids) { |
| 132 | $newsletter = $entity->getNewsletter(); |
| 133 | return $newsletter && in_array($newsletter->getId(), $ids, true); |
| 134 | }); |
| 135 | } |
| 136 | } |
| 137 |