*/
class StatisticsWooCommercePurchasesRepository extends Repository {
/** @var Helper */
private $wooCommerceHelper;
public function __construct(
EntityManager $entityManager,
Helper $wooCommerceHelper
) {
parent::__construct($entityManager);
$this->wooCommerceHelper = $wooCommerceHelper;
}
protected function getEntityClassName() {
return StatisticsWooCommercePurchaseEntity::class;
}
public function createOrUpdateByClickDataAndOrder(StatisticsClickEntity $click, \WC_Order $order) {
// search by subscriber and newsletter IDs (instead of click itself) to avoid duplicities
// when a new click from the subscriber appeared since last tracking for given newsletter
// (this will keep the originally tracked click - likely the click that led to the order)
$statistics = $this->findOneBy([
'orderId' => $order->get_id(),
'subscriber' => $click->getSubscriber(),
'newsletter' => $click->getNewsletter(),
]);
if (!$statistics instanceof StatisticsWooCommercePurchaseEntity) {
$newsletter = $click->getNewsletter();
$queue = $click->getQueue();
if ((!$newsletter instanceof NewsletterEntity) || (!$queue instanceof SendingQueueEntity)) return;
$statistics = new StatisticsWooCommercePurchaseEntity(
$newsletter,
$queue,
$click,
$order->get_id(),
$order->get_currency(),
(float)$order->get_remaining_refund_amount(),
$order->get_status()
);
$this->persist($statistics);
} else {
$statistics->setOrderCurrency($order->get_currency());
$statistics->setOrderPriceTotal((float)$order->get_remaining_refund_amount());
$statistics->setStatus($order->get_status());
}
$statistics->setSubscriber($click->getSubscriber());
$this->flush();
}
public function getRevenuesByCampaigns(string $currency): array {
$revenueStatus = $this->wooCommerceHelper->getPurchaseStates();
$revenueStatsTable = $this->entityManager->getClassMetadata(StatisticsWooCommercePurchaseEntity::class)->getTableName();
$newsletterTable = $this->entityManager->getClassMetadata(NewsletterEntity::class)->getTableName();
// The "SELECT MIN(click_id)..." sub-query is used to count each purchase only once.
// In the data we track a purchase to multiple newsletters if clicks from multiple newsletters occurred.
/** @var array $data */
$data = $this->entityManager->getConnection()->executeQuery('
SELECT
SUM(swp.order_price_total) AS revenue,
COALESCE(n.parent_id, swp.newsletter_id) AS campaign_id,
(
CASE
WHEN n.type IS NULL THEN \'unknown\'
WHEN n.type = :notification_history_type THEN :notification_type
ELSE n.type
END
) AS campaign_type,
COUNT(order_id) as orders_count
FROM ' . $revenueStatsTable . ' swp
LEFT JOIN ' . $newsletterTable . ' n ON
n.id = swp.newsletter_id
WHERE
swp.order_currency = :currency
AND swp.status IN (:revenue_status)
AND swp.click_id IN (SELECT MIN(click_id) FROM ' . $revenueStatsTable . ' ss GROUP BY order_id)
GROUP BY campaign_id, n.type;
', [
'notification_history_type' => NewsletterEntity::TYPE_NOTIFICATION_HISTORY,
'notification_type' => NewsletterEntity::TYPE_NOTIFICATION,
'currency' => $currency,
'revenue_status' => $revenueStatus,
], [
'notification_history_type' => ParameterType::STRING,
'notification_type' => ParameterType::STRING,
'currency' => ParameterType::STRING,
'revenue_status' => ArrayParameterType::STRING,
])->fetchAllAssociative();
$data = array_map(function($row) {
$row['revenue'] = round(floatval($row['revenue']), 2);
$row['orders_count'] = intval($row['orders_count']);
return $row;
}, $data);
return $data;
}
/** @param int[] $ids */
public function removeNewsletterDataByNewsletterIds(array $ids): void {
$this->entityManager->createQueryBuilder()
->update(StatisticsWooCommercePurchaseEntity::class, 'swp')
->set('swp.newsletter', ':newsletter')
->where('swp.newsletter IN (:ids)')
->setParameter('newsletter', null)
->setParameter('ids', $ids)
->getQuery()
->execute();
// update was done via DQL, make sure the entities are also refreshed in the entity manager
$this->refreshAll(function (StatisticsWooCommercePurchaseEntity $entity) use ($ids) {
$newsletter = $entity->getNewsletter();
return $newsletter && in_array($newsletter->getId(), $ids, true);
});
}
}