Symfony Profiler

<?php
declare(strict_types=1);
namespace Slivki\Dao\OfferCode;
use DateTimeImmutable;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Query\QueryBuilder;
use League\Period\Period;
use Slivki\Dto\Statistic\Conversion\ConversionByOfferDto;
use Slivki\Entity\Visit;
use Slivki\Enum\OfferCode\PurchaseCountPeriod;
use Slivki\Factory\Statistic\Conversion\ConversionByOfferDtoFactory;
use function sprintf;
use function array_map;
use function array_column;
final class PurchaseCountWithCorrectionDao implements PurchaseCountWithCorrectionDaoInterface
{
private const COUNT_CODES_FROM_SAME_SUBSCRIPTION = 0;
private Connection $connection;
private ConversionByOfferDtoFactory $conversionByOfferDtoFactory;
private CodePoolDaoInterface $codePoolDao;
public function __construct(
Connection $connection,
ConversionByOfferDtoFactory $conversionByOfferDtoFactory,
CodePoolDaoInterface $codePoolDao
) {
$this->connection = $connection;
$this->conversionByOfferDtoFactory = $conversionByOfferDtoFactory;
$this->codePoolDao = $codePoolDao;
}
public function findByPeriod(Period $period): array
{
$purchasesByPeriod = $this->getByPeriodQueryBuilder($period)
->execute()
->fetchAllAssociative();
return array_column($purchasesByPeriod, 'codes_count', 'offer_id');
}
public function findTotal(): array
{
$queryBuilder = $this->connection->createQueryBuilder();
$purchases = $queryBuilder
->addSelect([
'offer_codes.offer_id',
sprintf(
'sum (case %s %s %s end) as codes_count',
'when offer_codes.subscription_period_start_at is null then offer_codes.codes_count',
'when offer_codes.codes_count > :codesFromSameSubscription then :codesFromSameSubscription',
'else offer_codes.codes_count',
)
])
->from(
sprintf('(%s)', $this->getCodesCountQueryBuilder()->getSQL()),
'offer_codes'
)
->addGroupBy('offer_codes.offer_id')
->setParameter('codesFromSameSubscription', self::COUNT_CODES_FROM_SAME_SUBSCRIPTION)
->execute()
->fetchAllAssociative();
return array_column($purchases, 'codes_count', 'offer_id');
}
public function findTotalByOfferId(int $offerId): int
{
$queryBuilder = $this->connection->createQueryBuilder();
$expr = $queryBuilder->expr();
$purchases = $queryBuilder
->addSelect([
sprintf(
'sum (case %s %s %s end) as codes_count',
'when offer_codes.subscription_period_start_at is null then offer_codes.codes_count',
'when offer_codes.codes_count > :codesFromSameSubscription then :codesFromSameSubscription',
'else offer_codes.codes_count',
)
])
->from(
sprintf('(%s)', $this->getCodesCountQueryBuilder()->getSQL()),
'offer_codes'
)
->andWhere($expr->eq('offer_codes.offer_id', ':offerId'))
->andWhere($expr->eq('offer_codes.code_pool_id', ':codePoolId'))
->setParameters([
'codesFromSameSubscription' => self::COUNT_CODES_FROM_SAME_SUBSCRIPTION,
'offerId' => $offerId,
'codePoolId' => $this->codePoolDao->findLastCodePoolIdByOfferId($offerId),
])
->execute()
->fetchOne();
return false !== $purchases ? (int) $purchases : 0;
}
public function findLastDayByOfferId(int $offerId): int
{
$queryBuilder = $this->connection->createQueryBuilder();
$expr = $queryBuilder->expr();
$period = new Period(
new DateTimeImmutable(sprintf('- %d days', PurchaseCountPeriod::LAST_DAY)),
new DateTimeImmutable(),
Period::INCLUDE_ALL,
);
$purchasesByPeriod = $this->getByPeriodQueryBuilder($period)
->andWhere($expr->eq('codes_by_period.offer_id', ':offerId'))
->setParameters([
'offerId' => $offerId,
'dateFrom' => $period->getStartDate()->format('Y-m-d H:i:s'),
'dateTo' => $period->getEndDate()->format('Y-m-d H:i:s'),
'codesFromSameSubscription' => self::COUNT_CODES_FROM_SAME_SUBSCRIPTION,
])
->execute()
->fetchAssociative();
return (int) ($purchasesByPeriod['codes_count'] ?? 0);
}
public function findLastMonth(): array
{
return $this->findByPeriod(
new Period(
new DateTimeImmutable(sprintf('- %d days', PurchaseCountPeriod::LAST_MONTH)),
new DateTimeImmutable(),
Period::INCLUDE_ALL,
)
);
}
public function findLastWeek(): array
{
return $this->findByPeriod(
new Period(
new DateTimeImmutable(sprintf('- %d days', PurchaseCountPeriod::LAST_WEEK)),
new DateTimeImmutable(),
Period::INCLUDE_ALL,
)
);
}
public function findLastDay(): array
{
return $this->findByPeriod(
new Period(
new DateTimeImmutable(sprintf('- %d days', PurchaseCountPeriod::LAST_DAY)),
new DateTimeImmutable(),
Period::INCLUDE_ALL,
)
);
}
public function findConversionByManagerIdAndPeriod(int $managerId, Period $period): array
{
$conversionByManagerIdAndPeriodQueryBuilder = $this->getOfferConversionQueryBuilder($period);
$expr = $conversionByManagerIdAndPeriodQueryBuilder->expr();
$conversionByManagerIdAndPeriod = $conversionByManagerIdAndPeriodQueryBuilder
->andWhere($expr->eq('offer.manager_id', ':managerId'))
->setParameter('managerId', $managerId)
->execute()
->fetchAllAssociative();
return array_map(
fn (array $conversionByOffer): ConversionByOfferDto =>
$this->conversionByOfferDtoFactory->create($conversionByOffer),
$conversionByManagerIdAndPeriod,
);
}
public function findConversionByPeriod(Period $period): array
{
$conversionByPeriod = $this->getOfferConversionQueryBuilder($period)
->execute()
->fetchAllAssociative();
return array_map(
fn (array $conversionByOffer): ConversionByOfferDto =>
$this->conversionByOfferDtoFactory->create($conversionByOffer),
$conversionByPeriod,
);
}
private function getOfferConversionQueryBuilder(Period $period): QueryBuilder
{
$queryBuilder = $this->connection->createQueryBuilder();
$expr = $queryBuilder->expr();
return $queryBuilder
->addSelect([
'offer.id as offer_id',
'offer.manager_id as manager_id',
'coalesce(manager.manager_name, manager.email) as manager_name',
'offer.title as offer_name',
'coalesce(purchases.codes_count, 0) as codes_count',
'count(*) as visit_count',
])
->from('offer')
->innerJoin(
'offer',
'customer',
'manager',
$expr->eq('manager.id', 'offer.manager_id')
)
->leftJoin(
'offer',
sprintf('(%s)', $this->getByPeriodQueryBuilder($period)->getSQL()),
'purchases',
$expr->eq('purchases.offer_id', 'offer.id')
)
->leftJoin(
'offer',
'visit',
'visit',
$expr->and(
$expr->eq('offer.id', 'visit.entity_id'),
$expr->eq('visit.entity_type_id', ':offerVisitType')
)
)
->andWhere('visit.created_on between :dateFrom and :dateTo')
->andWhere($expr->or(
':dateFrom between offer.active_since and offer.active_till',
':dateTo between offer.active_since and offer.active_till',
))
->setParameters([
'offerVisitType' => Visit::TYPE_OFFER,
'dateFrom' => $period->getStartDate()->format('Y-m-d H:i:s'),
'dateTo' => $period->getEndDate()->format('Y-m-d H:i:s'),
'codesFromSameSubscription' => self::COUNT_CODES_FROM_SAME_SUBSCRIPTION,
])
->addGroupBy([
'offer.id',
'offer.manager_id',
'coalesce(manager.manager_name, manager.email)',
'offer.title',
'coalesce(purchases.codes_count, 0)',
]);
}
private function getByPeriodQueryBuilder(Period $period): QueryBuilder
{
$queryBuilder = $this->connection->createQueryBuilder();
return $queryBuilder
->addSelect([
'codes_by_period.offer_id',
sprintf(
'sum (case %s %s %s %s %s end) as codes_count',
'when codes_by_period.subscription_period_start_at is null then codes_by_period.codes_count',
'when codes_by_period.previous_period_codes_count > :codesFromSameSubscription then 0',
'when (codes_by_period.previous_period_codes_count + codes_by_period.codes_count) <= :codesFromSameSubscription then codes_by_period.codes_count',
'when (codes_by_period.codes_count - codes_by_period.previous_period_codes_count) > :codesFromSameSubscription then (:codesFromSameSubscription - codes_by_period.previous_period_codes_count)',
'else codes_by_period.codes_count - codes_by_period.previous_period_codes_count',
)
])
->from(
sprintf('(%s)', $this->getPurchasesWithPreviousSubscriptionQueryBuilder()->getSQL()),
'codes_by_period'
)
->setParameters([
'dateFrom' => $period->getStartDate()->format('Y-m-d H:i:s'),
'dateTo' => $period->getEndDate()->format('Y-m-d H:i:s'),
'codesFromSameSubscription' => self::COUNT_CODES_FROM_SAME_SUBSCRIPTION,
])
->addGroupBy('codes_by_period.offer_id');
}
private function getPurchasesWithPreviousSubscriptionQueryBuilder(): QueryBuilder
{
$queryBuilder = $this->connection->createQueryBuilder();
$expr = $queryBuilder->expr();
return $queryBuilder
->addSelect([
'offer_order.offer_id',
'offer_order.user_id',
'offer_order.subscription_period_start_at',
'coalesce(previous_count.count_by_subscription, 0) as previous_period_codes_count',
'count(*) as codes_count',
])
->from('offer_code')
->innerJoin(
'offer_code',
'offer_order',
'offer_order',
$expr->eq('offer_code.offer_order_id', 'offer_order.id')
)
->leftJoin(
'offer_code',
sprintf('(%s)', $this->getPreviousSubscriptionPurchasesQueryBuilder()->getSQL()),
'previous_count',
$expr->and(
$expr->eq('previous_count.offer_id', 'offer_order.offer_id'),
$expr->eq('previous_count.user_id', 'offer_order.user_id'),
$expr->eq('previous_count.subscription_period_start_at', 'offer_order.subscription_period_start_at'),
)
)
->andWhere('offer_order.created_on between :dateFrom and :dateTo')
->addGroupBy([
'offer_order.offer_id',
'offer_order.user_id',
'offer_order.subscription_period_start_at',
'coalesce(previous_count.count_by_subscription, 0)',
]);
}
private function getPreviousSubscriptionPurchasesQueryBuilder(): QueryBuilder
{
$queryBuilder = $this->connection->createQueryBuilder();
$expr = $queryBuilder->expr();
return $queryBuilder
->addSelect([
'offer_order.offer_id',
'offer_order.user_id',
'offer_order.subscription_period_start_at',
'count(*) as count_by_subscription',
])
->from('offer_code')
->innerJoin(
'offer_code',
'offer_order',
'offer_order',
$expr->eq('offer_code.offer_order_id', 'offer_order.id')
)
->andWhere($expr->lt('offer_order.created_on', ':dateFrom'))
->andWhere(
$expr->in(
'(offer_order.user_id, subscription_period_start_at)',
$this->getDistinctSubscriptionPurchasesQueryBuilder()->getSQL()
)
)
->addGroupBy([
'offer_order.offer_id',
'offer_order.user_id',
'offer_order.subscription_period_start_at',
]);
}
private function getDistinctSubscriptionPurchasesQueryBuilder(): QueryBuilder
{
$queryBuilder = $this->connection->createQueryBuilder();
$expr = $queryBuilder->expr();
return $queryBuilder
->addSelect([
'offer_order.user_id',
'offer_order.subscription_period_start_at',
])
->from('offer_code')
->innerJoin(
'offer_code',
'offer_order',
'offer_order',
$expr->eq('offer_code.offer_order_id', 'offer_order.id')
)
->andWhere('offer_order.created_on between :dateFrom and :dateTo')
->andWhere($expr->isNotNull('offer_order.subscription_period_start_at'))
->distinct();
}
private function getCodesCountQueryBuilder(): QueryBuilder
{
$queryBuilder = $this->connection->createQueryBuilder();
$expr = $queryBuilder->expr();
return $queryBuilder
->addSelect([
'offer_order.offer_id',
'offer_order.user_id',
'offer_order.subscription_period_start_at',
'offer_code.offer_code_pool_id as code_pool_id',
'count(*) as codes_count',
])
->from('offer_code')
->innerJoin(
'offer_code',
'offer_order',
'offer_order',
$expr->eq('offer_code.offer_order_id', 'offer_order.id')
)
->addGroupBy([
'offer_order.offer_id',
'offer_order.user_id',
'offer_order.subscription_period_start_at',
'offer_code.offer_code_pool_id',
]);
}
}

src/Dao/OfferCode/PurchaseCountWithCorrectionDao.php line 123