er('subscriber', $subscriber); } public function getWooCommerceRevenue(SubscriberEntity $subscriber, ?Carbon $startTime = null): ?WooCommerceRevenue { if (!$this->wcHelper->isWooCommerceActive()) { return null; } $revenueStatus = $this->wcHelper->getPurchaseStates(); $currency = $this->wcHelper->getWoocommerceCurrency(); $queryBuilder = $this->entityManager->createQueryBuilder() ->select('stats.orderPriceTotal') ->from(StatisticsWooCommercePurchaseEntity::class, 'stats') ->where('stats.subscriber = :subscriber') ->andWhere('stats.orderCurrency = :currency') ->setParameter('subscriber', $subscriber) ->setParameter('currency', $currency) ->andWhere('stats.status IN (:revenue_status)') ->setParameter('subscriber', $subscriber) ->setParameter('currency', $currency) ->setParameter('revenue_status', $revenueStatus) ->groupBy('stats.orderId, stats.orderPriceTotal'); if ($startTime) { $queryBuilder ->andWhere('stats.createdAt >= :dateTime') ->setParameter('dateTime', $startTime); } $purchases = $queryBuilder->getQuery() ->getResult(); $sum = array_sum(array_column($purchases, 'orderPriceTotal')); return new WooCommerceRevenue( $currency, (float)$sum, count($purchases), $this->wcHelper ); } private function applyDateConstraint(QueryBuilder $queryBuilder, Carbon $startTime): QueryBuilder { $queryBuilder->join(StatisticsNewsletterEntity::class, 'sent_stats', 'WITH', 'stats.newsletter = sent_stats.newsletter AND stats.subscriber = sent_stats.subscriber AND sent_stats.sentAt >= :dateTime') ->setParameter('dateTime', $startTime); return $queryBuilder; } }