kupshop/bundles/External/ZNZBundle/Util/ZNZPriceLevelsGenerator.php

<?php

declare(strict_types=1);

namespace External\ZNZBundle\Util;

use External\ZNZBundle\Email\PriceLevelUpgradeEmail;
use KupShop\KupShopBundle\Context\ContextManager;
use KupShop\KupShopBundle\Context\LanguageContext;
use Query\Operator;
use Query\QueryBuilder;

class ZNZPriceLevelsGenerator
{
    public function __construct(
        private ContextManager $contextManager,
        private PriceLevelUpgradeEmail $priceLevelUpgradeEmail,
        private readonly ZNZConfiguration $configuration,
    ) {
    }

    public function generate(?int $userId = null): void
    {
        // otocim si poradi, aby podminky s vyssi prioritou byly na zacatku
        $priceLevels = $this->getPriceLevelsConfig();

        $affectedUsers = [];
        foreach ($priceLevels as $priority => $data) {
            if (empty($data['type'])) {
                continue;
            }

            $method = 'type'.ucfirst($data['type']);
            if (!method_exists($this, $method)) {
                continue;
            }

            $config = $this->{$method}($data);
            if (empty($config)) {
                continue;
            }

            $subQb = $this->getBaseOrdersQueryBuilder()
                ->andWhere($config['spec']);

            // chci vytahnout pouze skupiny, ktere maji nastaveny id_pricelist, protoze ve chvili kdy ma skupina
            // svuj pricelist, tak uz nemuze aplikovat cenovou hladinu
            $subUserGroupRelations = sqlQueryBuilder()
                ->select('ugr.*')
                ->from('users_groups_relations', 'ugr')
                ->join('ugr', 'users_groups', 'ug', 'ugr.id_group = ug.id AND ug.id_pricelist IS NOT NULL');

            $qb = sqlQueryBuilder()
                ->select("u.id, {$config['id_price_level']} as id_price_level")
                ->from('users', 'u')
                ->leftJoinSubQuery('u', $subQb, 'o', 'u.id = o.id_user')
                ->leftJoin('u', 'users_dealer_price_level', 'udpl', 'udpl.id_user = u.id')
                ->leftJoinSubQuery('u', $subUserGroupRelations, 'ugr', 'u.id = ugr.id_user')
                ->leftJoin('ugr', 'users_groups', 'ug', 'ugr.id_group = ug.id')
                ->andWhere($config['condition'])
                ->andWhere(Operator::isNull('u.id_pricelist'))
                ->andWhere(Operator::isNull('ug.id_pricelist'))
                ->groupBy('u.id')
                ->sendToMaster();

            if ($userId) {
                $qb->andWhere(Operator::equals(['u.id' => $userId]));
            }

            // vyradim si zakazniky, ktere uz maji prirazenou cenovou hladinu s vyssi prioritou
            if ($previousPriceLevels = $this->getPreviousPriceLevels($priority)) {
                $qb->andWhere(
                    Operator::orX(
                        Operator::not(
                            Operator::inIntArray($previousPriceLevels, 'udpl.id_price_level')
                        ),
                        'udpl.id_price_level IS NULL'
                    )
                );
            }

            // nactu si uzivatele, kterym bude nastavena jina cenova hladina
            $affectedUsers = array_replace($affectedUsers, $this->getAffectedUsersFromQueryBuilder(clone $qb, $config));

            // nastavim cenove hladiny
            sqlGetConnection()->transactional(function () use ($qb, $config, $userId) {
                $deleteSpec = [Operator::equals(['id_price_level' => $config['id_price_level']])];
                if ($userId) {
                    $deleteSpec[] = Operator::equals(['id_user' => $userId]);
                }

                // smazu prirazeni price levelu
                sqlQueryBuilder()
                    ->delete('users_dealer_price_level')
                    ->where(Operator::andX($deleteSpec))
                    ->execute();

                // vytvorim prirazeni price levelu
                sqlQuery("REPLACE INTO users_dealer_price_level (id_user, id_price_level)
                    {$qb->getSQL()}", $qb->getParameters(), $qb->getParameterTypes());
            });
        }

        $this->sendNotificationsToAffectedUsers($affectedUsers);
    }

    private function typeUserRegistered(array $config): array
    {
        if (empty($config['priceLevelId'])) {
            return [];
        }

        return [
            'id_price_level' => (int) $config['priceLevelId'],
            'spec' => function (QueryBuilder $qb) {
                $qb->addSelect('SUM(total_price) as total_price');
            },
            'order_message' => $config['order_message'] ?? null,
            'condition' => 'o.total_price >= 0 OR o.total_price IS NULL',
        ];
    }

    private function typeUserRegisteredFirstPurchase(array $config): array
    {
        if (empty($config['priceLevelId'])) {
            return [];
        }

        return [
            'id_price_level' => (int) $config['priceLevelId'],
            'spec' => function (QueryBuilder $qb) {
                $qb->addSelect('COUNT(id) as total_orders_count');

                return Operator::andX(
                    Operator::inIntArray(getStatuses('handled'), 'status'),
                    Operator::equals(['status_storno' => 0])
                );
            },
            'order_message' => $config['order_message'] ?? null,
            'condition' => 'o.total_orders_count > 0',
        ];
    }

    private function typeUserPointsCountReached(array $config): array
    {
        if (empty($config['requiredPoints']) || empty($config['priceLevelId'])) {
            return [];
        }

        $pointValue = \Settings::getDefault()->loadValue('znz')['bonusProgram']['pointValue'] ?? null;
        if (empty($pointValue)) {
            return [];
        }

        $value = (int) $config['requiredPoints'] * (int) $pointValue;

        return [
            'id_price_level' => (int) $config['priceLevelId'],
            'spec' => function (QueryBuilder $qb) {
                $qb->addSelect('SUM(total_price * currency_rate) as total_price');

                return 'date_created >= DATE_SUB(NOW(), INTERVAL 365 DAY)';
            },
            'condition' => 'o.total_price >= '.$value,
            'order_message' => $config['order_message'] ?? null,
        ];
    }

    private function sendNotificationsToAffectedUsers(array $affectedUsers): void
    {
        if (empty($affectedUsers)) {
            return;
        }

        // pokud to bude moc velkej objem, tak maily neposilam.. radsi :)
        if (count($affectedUsers) > 500) {
            return;
        }

        foreach ($affectedUsers as $affectedUser) {
            $orderMessage = clone $this->priceLevelUpgradeEmail;
            $orderMessage->addPlaceholder('CENOVA_HLADINA', fn () => $affectedUser['priceLevelName'], 'Název cenové hladiny');

            $email = $this->contextManager->activateContexts([LanguageContext::class => $affectedUser['language']], fn () => $orderMessage->getEmail());
            $email['to'] = $affectedUser['userEmail'];

            $orderMessage->sendEmail($email);
        }
    }

    private function getAffectedUsersFromQueryBuilder(QueryBuilder $qb, array $config): array
    {
        $qb->addSelect('u.email, u.id_language, udpl.id_price_level old_id_price_level, pl.name price_level_name')
            ->leftJoin('u', 'users_dealer_price_level', 'udpl', 'udpl.id_user = u.id')
            ->join('u', 'price_levels', 'pl', 'pl.id = :priceLevelId')
            ->setParameter('priceLevelId', $config['id_price_level']);

        $affectedUsers = [];

        foreach ($qb->execute() as $item) {
            if ($item['old_id_price_level'] == $item['id_price_level']) {
                continue;
            }

            $affectedUsers[$item['id']] = [
                'userId' => $item['id'],
                'userEmail' => $item['email'],
                'priceLevelId' => $item['id_price_level'],
                'priceLevelName' => $item['price_level_name'],
                'orderMessage' => !empty($config['order_message']) ? $config['order_message'] : null,
                'language' => $item['id_language'] ?: $this->configuration->getMainWebsiteLanguage(),
            ];
        }

        return $affectedUsers;
    }

    // vratim ID cenovych hladin, ktere maji vyssi prioritu, nez ta aktualni
    private function getPreviousPriceLevels(int $priceLevelIndex): array
    {
        $previous = [];
        foreach ($this->getPriceLevelsConfig() as $index => $item) {
            if (!empty($item['priceLevelId']) && $index > $priceLevelIndex) {
                $previous[] = (int) $item['priceLevelId'];
            }
        }

        return $previous;
    }

    private function getPriceLevelIndexByPriceLevelId(int $priceLevelId): ?int
    {
        foreach ($this->getPriceLevelsConfig() as $index => $item) {
            if ($item['priceLevelId'] == $priceLevelId) {
                return $index;
            }
        }

        return null;
    }

    private function getPriceLevelsConfig(): array
    {
        $dbcfg = \Settings::getDefault();

        $priceLevelsConfig = $dbcfg->loadValue('znz')['bonusProgram']['priceLevel'] ?? [];

        // seradim si to podle klicu, ktery znaci priority (od nejvyssi po nejnizsi)
        krsort($priceLevelsConfig);

        return $priceLevelsConfig;
    }

    private function getBaseOrdersQueryBuilder(): QueryBuilder
    {
        return sqlQueryBuilder()
            ->select('id_user')
            ->from('orders')
            ->andWhere(Operator::equals(['status_storno' => 0]))
            ->andWhere('id_user IS NOT NULL')
            ->groupBy('id_user');
    }
}