kupshop/bundles/KupShop/POSBundle/Util/PosAppStats.php

<?php

namespace KupShop\POSBundle\Util;

use KupShop\GraphQLBundle\ApiShared\Types\DateInterval;
use KupShop\KupShopBundle\Context\CountryContext;
use KupShop\KupShopBundle\Util\Contexts;
use Query\Operator;

class PosAppStats
{
    private PosEntity $posEntity;
    private string $startDatetime;
    private string $endDatetime;

    private \Decimal $actualCashInPos;
    private \Decimal $nonHandledOrdersCount;
    private \Decimal $nonHandledOrdersPriceSum;
    private \Decimal $discounts;
    private \Decimal $sales;
    private \Decimal $rounding;
    private \Decimal $paidOfTheInvoiceCash;
    private \Decimal $paidOfTheInvoiceCard;
    private array $stats = [];

    public function fetchStats(): void
    {
        $nonHandledOrdersState = $this->loadNonHandledOrders();
        $this->nonHandledOrdersCount = \Decimal::fromInteger($nonHandledOrdersState['non_handled_orders_count'] ?? 0);
        $this->nonHandledOrdersPriceSum = \Decimal::fromString($nonHandledOrdersState['non_handled_orders_price_sum'] ?? '0');

        $this->loadActualCashInPos();
        $this->loadStatsPerVat();
        $this->loadAdditionalStats();
        $this->loadDiscounts();
        $this->loadSales();
        $this->loadRounding();

        $this->loadPaidOfTheInvoice();
    }

    private function loadStatsPerVat(): void
    {
        $vatsQb = sqlQueryBuilder()
            ->select('v.id, v.descr, v.vat, JSON_VALUE(v.data, \'$.level\') level')->from('vats', 'v')
            ->orderBy('(v.is_default = "Y")', 'DESC')
            ->addOrderBy('v.vat', 'DESC');

        if (findModule(\Modules::OSS_VATS)) {
            $vatsQb->andWhere('v.id_country = :idCountry')
                ->setParameter('idCountry', Contexts::get(CountryContext::class)->getActiveId());
        }

        foreach ($vatsQb->execute()->fetchAllAssociative() as $vat) {
            $this->stats[\Payment::METHOD_CASH][$vat['vat']] = $this->getSumByVat(\Payment::METHOD_CASH, $vat['vat']);
            $this->stats[\Payment::METHOD_CARD][$vat['vat']] = $this->getSumByVat(\Payment::METHOD_CARD, $vat['vat']);
            $this->stats[\Payment::METHOD_INVOICE][$vat['vat']] = $this->getSumByVat(\Payment::METHOD_INVOICE, $vat['vat']);
        }
    }

    private function loadAdditionalStats(): void
    {
        $this->stats[\Payment::METHOD_CASH_INSERTION] = $this->getSum(\Payment::METHOD_CASH_INSERTION);
        $this->stats[\Payment::METHOD_CASH_SELECTION] = $this->getSum(\Payment::METHOD_CASH_SELECTION);
        $this->stats[\Payment::METHOD_COMPENSATION] = $this->getSum(\Payment::METHOD_COMPENSATION);
    }

    private function loadActualCashInPos(): void
    {
        $qb = sqlQueryBuilder()
            ->select('SUM(price) as sum')
            ->from('order_payments', 'op')
            ->leftJoin('op', 'pos_payments_relation', 'ppr', 'op.id = ppr.id_payment')
            ->andWhere(
                Operator::orX(
                    Operator::equals(['method' => \Payment::METHOD_CASH]),
                    Operator::equals(['method' => \Payment::METHOD_CASH_SELECTION]),
                    Operator::equals(['method' => \Payment::METHOD_CASH_INSERTION]),
                    Operator::equals(['method' => \Payment::METHOD_COMPENSATION]),
                    findModule(\Modules::SKEET) ? Operator::equals(['method' => \Payment::METHOD_EET_INVOICE_CASH]) : null
                )
            )
            ->andWhere(Operator::equals(['op.status' => \Payment::STATUS_FINISHED]))
            ->andWhere('ppr.id_pos = :idPos')
            ->setParameter('idPos', $this->posEntity->getId());

        if ($this->endDatetime) {
            $qb->andWhere('op.date < :endDatetime')->setParameter('endDatetime', $this->endDatetime);
        }

        $this->actualCashInPos = toDecimal($qb->execute()->fetchOne());
    }

    private function loadDiscounts(): void
    {
        $qb = sqlQueryBuilder()
            ->select('SUM(JSON_EXTRACT(oi.note, "$.custom_discount.discount_price_with_vat"))')
            ->from('order_items', 'oi')
            ->leftJoin('oi', 'order_payments', 'op', 'oi.id_order = op.id_order')
            ->leftJoin('op', 'pos_payments_relation', 'ppr', 'op.id = ppr.id_payment')
            ->groupBy('oi.id');

        $qb = $this->applyBasics($qb)
            ->andWhere('JSON_EXTRACT(oi.note, "$.custom_discount.discount_price_with_vat") IS NOT NULL');

        $this->discounts = toDecimal(array_sum($qb->execute()->fetchFirstColumn()));
    }

    private function loadSales(): void
    {
        $qb = sqlQueryBuilder()
            ->select('SUM(price)')
            ->from('order_payments', 'op')
            ->leftJoin('op', 'pos_payments_relation', 'ppr', 'op.id = ppr.id_payment')
            ->andWhere(
                Operator::orX(
                    Operator::equals(['method' => \Payment::METHOD_CASH]),
                    Operator::equals(['method' => \Payment::METHOD_CARD]),
                    Operator::equals(['method' => \Payment::METHOD_INVOICE]),
                )
            )
            ->andWhere(Operator::equals(['op.status' => \Payment::STATUS_FINISHED]));
        $qb = $this->applyBasics($qb);

        $this->sales = toDecimal($qb->execute()->fetchColumn());
    }

    private function loadRounding(): void
    {
        $payment = sqlQueryBuilder()
            ->select('SUM(price)')
            ->from('order_payments', 'op')
            ->leftJoin('op', 'pos_payments_relation', 'ppr', 'op.id = ppr.id_payment')
            ->andWhere(
                Operator::orX(
                    Operator::equals(['method' => \Payment::METHOD_CASH]),
                    Operator::equals(['method' => \Payment::METHOD_CARD]),
                    Operator::equals(['method' => \Payment::METHOD_INVOICE]),
                )
            )
            ->andWhere(Operator::equals(['op.status' => \Payment::STATUS_FINISHED]));
        $payment = $this->applyBasics($payment)->execute()->fetchOne();

        $orderPayment = sqlQueryBuilder()
            ->select('total_price')
            ->from('order_payments', 'op')
            ->leftJoin('op', 'orders', 'o', 'o.id = op.id_order')
            ->leftJoin('op', 'pos_payments_relation', 'ppr', 'op.id = ppr.id_payment')
            ->andWhere(
                Operator::orX(
                    Operator::equals(['method' => \Payment::METHOD_CASH]),
                    Operator::equals(['method' => \Payment::METHOD_CARD]),
                    Operator::equals(['method' => \Payment::METHOD_INVOICE]),
                )
            )
            ->andWhere(Operator::equals(['op.status' => \Payment::STATUS_FINISHED]))
            ->groupBy('id_order');
        $orderPayment = $this->applyBasics($orderPayment)->execute()->fetchFirstColumn();

        $this->rounding = toDecimal(abs($payment - array_sum($orderPayment)));
    }

    private function getSumByVat($method, $vat): \Decimal
    {
        $prices = sqlQueryBuilder()
            ->select('op.price')
            ->from('pos_payments_relation', 'ppr')
            ->leftJoin('ppr', 'order_payments', 'op', 'op.id = ppr.id_payment')
            ->leftJoin('op', 'orders', 'o', 'op.id_order = o.id')
            ->leftJoin('o', 'order_items', 'oi', 'o.id = oi.id_order')
            ->andWhere(Operator::equals(['op.method' => $method]))
            ->andWhere(Operator::equals(['oi.tax' => $vat]))
            ->andWhere(Operator::equals(['op.status' => \Payment::STATUS_FINISHED]))
            ->groupBy('id_payment');

        $prices = $this->applyBasics($prices)
            ->execute()
            ->fetchFirstColumn();

        return toDecimal(array_sum($prices));
    }

    private function getSum($method): \Decimal
    {
        $qb = sqlQueryBuilder()
            ->select('IFNULL(SUM(price), 0) as sum')
            ->from('pos_payments_relation', 'ppr')
            ->leftJoin('ppr', 'order_payments', 'op', 'op.id = ppr.id_payment')
            ->andWhere(Operator::equals(['op.method' => $method]))
            ->andWhere(Operator::equals(['op.status' => \Payment::STATUS_FINISHED]));
        $qb = $this->applyBasics($qb);

        return toDecimal($qb->execute()->fetchColumn());
    }

    private function loadNonHandledOrders(): array
    {
        $qb = sqlQueryBuilder()
            ->select('COUNT(op.id) AS non_handled_orders_count, SUM(price) AS non_handled_orders_price_sum')
            ->from('order_payments', 'op')
            ->leftJoin('op', 'orders', 'o', 'op.id_order=o.id')
            ->leftJoin('op', 'pos_payments_relation', 'ppr', 'ppr.id_payment = op.id')
            ->andWhere('method = :cash OR method = :card OR method = :invoice')
            ->setParameters([
                'cash' => \Payment::METHOD_CASH,
                'card' => \Payment::METHOD_CARD,
                'invoice' => \Payment::METHOD_INVOICE,
            ])
            ->andWhere(Operator::equals(['op.status' => \Payment::STATUS_FINISHED]))
            ->andWhere(Operator::inIntArray(getStatuses('nothandled'), 'o.status'));
        $qb = $this->applyBasics($qb);

        return $qb->execute()->fetchAssociative();
    }

    private function loadPaidOfTheInvoice(): void
    {
        if (!findModule(\Modules::SKEET)) {
            $this->paidOfTheInvoiceCash = \DecimalConstants::zero();
            $this->paidOfTheInvoiceCard = \DecimalConstants::zero();

            return;
        }

        $this->paidOfTheInvoiceCash = $this->getPaidOfTheInvoice(\Payment::METHOD_EET_INVOICE_CASH);
        $this->paidOfTheInvoiceCard = $this->getPaidOfTheInvoice(\Payment::METHOD_EET_INVOICE_CARD);
    }

    public function setDatetimeInterval(?DateInterval $interval = null): void
    {
        $this->startDatetime = $interval ? $interval->getFrom() : (date('Y-m-d').' 0:00:00');
        $this->endDatetime = $interval ? $interval->getTo() : (date('Y-m-d').' 23:59:59');
    }

    private function applyBasics($qb)
    {
        return $qb->andWhere('op.date > :startDatetime')
            ->andWhere('op.date < :endDatetime')
            ->andWhere('ppr.id_pos = :idPos')
            ->setParameter('idPos', $this->posEntity->getId())
            ->setParameter('startDatetime', $this->startDatetime)
            ->setParameter('endDatetime', $this->endDatetime);
    }

    private function getPaidOfTheInvoice($method): \Decimal
    {
        $qb = sqlQueryBuilder()
            ->select('SUM(price)')
            ->from('order_payments', 'op')
            ->leftJoin('op', 'pos_payments_relation', 'ppr', 'op.id = ppr.id_payment')
            ->andWhere(Operator::equals(['method' => $method]))
            ->andWhere(Operator::equals(['op.status' => \Payment::STATUS_FINISHED]));

        return toDecimal($this->applyBasics($qb)->execute()->fetchColumn());
    }

    public function getStats(): array
    {
        return $this->stats ?? [];
    }

    public function getCashInsertion()
    {
        return $this->stats[\Payment::METHOD_CASH_INSERTION] ?? null;
    }

    public function getCashSelection()
    {
        return $this->stats[\Payment::METHOD_CASH_SELECTION] ?? null;
    }

    public function getCompensation()
    {
        return $this->stats[\Payment::METHOD_COMPENSATION] ?? null;
    }

    public function setPosEntity(PosEntity $posEntity): void
    {
        $this->posEntity = $posEntity;
    }

    public function getActualCashInPos(): \Decimal
    {
        return $this->actualCashInPos;
    }

    public function getNonHandledOrdersCount(): \Decimal
    {
        return $this->nonHandledOrdersCount;
    }

    public function getNonHandledOrdersPriceSum(): \Decimal
    {
        return $this->nonHandledOrdersPriceSum;
    }

    public function getDiscounts(): \Decimal
    {
        return $this->discounts;
    }

    public function getSales(): \Decimal
    {
        return $this->sales;
    }

    public function getRounding(): \Decimal
    {
        return $this->rounding;
    }

    public function getPaidOfTheInvoiceCash(): \Decimal
    {
        return $this->paidOfTheInvoiceCash;
    }

    public function getPaidOfTheInvoiceCard(): \Decimal
    {
        return $this->paidOfTheInvoiceCard;
    }
}