kupshop/bundles/External/LeaflyBundle/Util/BonusProgramUtil.php

<?php

declare(strict_types=1);

namespace External\LeaflyBundle\Util;

use KupShop\KupShopBundle\Context\CurrencyContext;
use KupShop\KupShopBundle\Util\Contexts;
use Query\Operator;

class BonusProgramUtil
{
    public const BONUS_PROGRAM_COURSE = 25;
    protected $defaultPriceLevel;

    protected $userGroup;

    protected $pricelevels;

    public function __construct()
    {
        $this->defaultPriceLevel = 12;

        $this->userGroup = 3;

        $this->pricelevels =
            [
                12 => ['actual_id' => 12, 'next_id' => 8, 'from' => 0, 'to' => 200000],
                8 => ['actual_id' => 8, 'next_id' => 9, 'from' => 200001, 'to' => 300000],
                9 => ['actual_id' => 9, 'next_id' => 10, 'from' => 300001, 'to' => 400000],
                10 => ['actual_id' => 10, 'next_id' => 11, 'from' => 400001, 'to' => 500000],
                11 => ['actual_id' => 11, 'next_id' => null, 'from' => 500001, 'to' => null],
            ];
    }

    public function updateUsersPricelevel(): void
    {
        $qb = sqlQueryBuilder()->select('o.id_user, COALESCE(SUM(oi.total_price),0) as total_price,u.get_news, u.custom_data ')
            ->from('orders', 'o')
            ->leftJoin('o', 'users', 'u', 'u.id = o.id_user')
            ->leftJoin('o', 'order_items', 'oi', 'oi.id_order=o.id')
            ->innerJoin('o', 'users_groups_relations', 'ugr', 'o.id_user=ugr.id_user')
            ->andWhere(Operator::isNotNull('o.id_user'))
            ->andWhere(Operator::equals(['status_storno' => 0]))
            ->andWhere(Operator::equals(['ugr.id_group' => $this->userGroup]))
            ->andWhere('YEAR(o.date_created) = YEAR(CURDATE())')
            ->groupBy('o.id_user');

        $usersWithBonus = [];
        foreach ($qb->execute() as $user) {
            $pricelevel = $this->defaultPriceLevel;

            foreach ($this->pricelevels as $pricelevelId => $priceLevel) {
                if ($user['total_price'] >= $priceLevel['from']) {
                    $pricelevel = $pricelevelId;
                }
            }

            $newCustomData = array_merge(json_decode($user['custom_data'] ?? '', true) ?? [], ['sum_orders_price' => $user['total_price']]);
            sqlQueryBuilder()->update('users')->where(\Query\Operator::equals(['id' => $user['id_user']]))
                ->directValues(['custom_data' => json_encode($newCustomData)])
                ->execute();

            $idPriceLevel = sqlQueryBuilder()
                ->select('id_price_level')
                ->from('users_dealer_price_level')
                ->where(
                    Operator::equals(['id_user' => $user['id_user']])
                )->execute()->fetchOne();

            if (!$idPriceLevel || array_key_exists($idPriceLevel, $this->pricelevels)) {
                sqlQueryBuilder()->delete('users_dealer_price_level')->where(Operator::equals(['id_user' => $user['id_user']]))->execute();
                if ($pricelevel) {
                    $usersWithBonus[] = $user['id_user'];
                    sqlQueryBuilder()->insert('users_dealer_price_level')
                        ->directValues(
                            ['id_user' => $user['id_user'],
                                'id_price_level' => $pricelevel]
                        )->execute();
                }
            }
        }

        $usersWithBonusOld = sqlQueryBuilder()->select('id_user')->from('users_dealer_price_level')->where(
            Operator::inIntArray(array_keys($this->pricelevels), 'id_price_level')
        )->execute()->fetchFirstColumn();

        $diff = array_diff($usersWithBonusOld, $usersWithBonus);
        sqlQueryBuilder()
            ->delete('users_dealer_price_level')
            ->where(Operator::inIntArray($diff, 'id_user'))
            ->andWhere(Operator::not(Operator::equals(['id_price_level' => $this->defaultPriceLevel])))
            ->execute();

        $this->setDefaultDealerPriceLevel();
    }

    private function setDefaultDealerPriceLevel()
    {
        $qb = sqlQueryBuilder()->select('u.id, custom_data')
            ->from('users', 'u')
            ->innerJoin('u', 'users_groups_relations', 'ugr', 'u.id = ugr.id_user')
            ->leftJoin('u', 'users_dealer_price_level', 'udp', 'u.id = udp.id_user')
            ->andWhere(Operator::equals(['ugr.id_group' => $this->userGroup]))
            ->andWhere(Operator::isNull('udp.id_price_level'))
            ->groupBy('u.id');

        foreach ($qb->execute() as $user) {
            sqlQuery('INSERT IGNORE INTO users_dealer_price_level (id_user, id_price_level) VALUES (:id_user, :id_price_level)',
                ['id_user' => $user['id'], 'id_price_level' => $this->defaultPriceLevel]);

            $newCustomData = array_merge(json_decode($user['custom_data'] ?? '', true) ?? [], ['sum_orders_price' => 0]);
            sqlQueryBuilder()->update('users')->where(\Query\Operator::equals(['id' => $user['id']]))
                ->directValues(['custom_data' => json_encode($newCustomData)])
                ->execute();
        }
    }

    public function GetUserBonusProgramInfo($user): array
    {
        $return = [
            'turnover' => $user->getCustomData()['sum_orders_price'] ?? 0,
            'price_level' => $user->getPriceLevel(),
            'fulfill' => true,
        ];

        if (!isset($this->pricelevels[$user['idUserPriceLevel']])) {
            return ['actual_id' => null,
                'actual_name' => 'Neznámá slevová akce',
                'next_id' => null,
                'from' => 0,
                'to' => 0,
                'fulfill' => false,
                'price_level' => $user->getPriceLevel()];
        }

        $currencyContext = Contexts::get(CurrencyContext::class);
        $priceLevels = $this->pricelevels[$user['idUserPriceLevel']];

        if ($currencyContext->getActiveId() == 'EUR') {
            $priceLevels['from'] = ceil($priceLevels['from'] / self::BONUS_PROGRAM_COURSE);
            $priceLevels['to'] = ceil($priceLevels['to'] / self::BONUS_PROGRAM_COURSE);
        }

        return array_merge($return, $priceLevels, $this->getPriceLevelName($user['idUserPriceLevel']));
    }

    private function getPriceLevelName($idPricelevel)
    {
        return sqlQueryBuilder()->select('name',
            'discount')->from('price_levels')->where(Operator::equals(['id' => $idPricelevel]))->execute()->fetch() ?: [];
    }
}