kupshop/bundles/KupShop/AdminBundle/Util/ExpiringProductUtil.php

<?php

declare(strict_types=1);

namespace KupShop\AdminBundle\Util;

use Doctrine\DBAL\ParameterType;
use KupShop\KupShopBundle\Query\JsonOperator;
use KupShop\KupShopBundle\Util\EANValidator;
use KupShop\OrderDiscountBundle\Util\DiscountUtil;
use Query\Operator;

class ExpiringProductUtil
{
    /**
     * @var ProductUtils
     */
    protected $productUtils;

    protected ?DiscountUtil $discountUtil;

    /**
     * @required
     */
    public function setProductUtils(ProductUtils $productUtils): void
    {
        $this->productUtils = $productUtils;
    }

    /**
     * @required
     */
    public function setDiscountUtil(?DiscountUtil $discountUtil): void
    {
        $this->discountUtil = $discountUtil;
    }

    public function createFromPieces(int $idProduct, ?int $idVariation, int $pieces, $prependText = '')
    {
        return sqlGetConnection()->transactional(function () use ($idProduct, $idVariation, $pieces, $prependText) {
            $productAlreadyExists = $this->expiringProductVariationAlreadyExists($idProduct, $idVariation);

            $newIdVariation = null;

            if ($productAlreadyExists) {
                $newIdProduct = $productAlreadyExists['id_product'];
                $newIdVariation = $productAlreadyExists['id_variation'];
            } elseif (is_null($idVariation) || !($newIdProduct = $this->expiringProductAlreadyExists($idProduct))) {
                $newIdProduct = $this->duplicateProduct($idProduct, (bool) $idVariation);
                $this->modifyProductExpiringTitle($newIdProduct, $prependText);
            }

            if ($idVariation && !$newIdVariation) {
                $newIdVariation = $this->duplicateVariation($idVariation, $newIdProduct);
            }

            $this->setProductVisible($newIdProduct, $newIdVariation);

            $this->updateInStore($idProduct, $idVariation, -$pieces);
            $this->updateInStore($newIdProduct, $newIdVariation, $pieces);

            $this->clearPriceForDiscount($newIdProduct, $newIdVariation);

            return ['oldIdProduct' => $idProduct,
                'oldIdVariation' => $idVariation,
                'idProduct' => $newIdProduct,
                'idVariation' => $newIdVariation,
                'movedPieces' => $pieces];
        });
    }

    protected function updateInStore($idProduct, $idVariation, $movePieces): void
    {
        if ($idVariation) {
            sqlQueryBuilder()
                ->update('products_variations')
                ->set('in_store', 'in_store + :amount')
                ->where(Operator::equals(['id' => $idVariation]))
                ->setParameter('amount', $movePieces)
                ->execute();
        }

        sqlQueryBuilder()
            ->update('products')
            ->set('in_store', 'in_store + :amount')
            ->where(Operator::equals(['id' => $idProduct]))
            ->setParameter('amount', $movePieces)
            ->execute();

        $newProduct = new \Product($idProduct);
        $newProduct->updateInStore();
    }

    protected function duplicateVariation($idVariation, $newIdProduct)
    {
        $updateValues = [];

        $newIdVariation = \Variations::duplicateVariation($idVariation, $newIdProduct);
        $updateValues['ean'] = EANValidator::generateEan(['id_variation' => $newIdVariation]);
        $updateValues['in_store'] = 0;

        sqlQueryBuilder()->update('products_variations')
            ->directValues($updateValues)
            ->set('data', "JSON_INSERT(COALESCE(data, JSON_OBJECT()), '$.expiringBatchVariation', :originalVariation)")
            ->where(Operator::equals(['id' => $newIdVariation]))
            ->setParameter('originalVariation', $idVariation, ParameterType::INTEGER)->execute();

        return $newIdVariation;
    }

    protected function setProductVisible($idProduct, $idVariation)
    {
        sqlQueryBuilder()->update('products')
            ->directValues(['figure' => 'Y'])->where(Operator::equals(['id' => $idProduct]))->execute();

        if ($idVariation) {
            sqlQueryBuilder()->update('products_variations')
                ->directValues(['figure' => 'Y'])
                ->where(Operator::equals(['id' => $idVariation]))->execute();
        }
    }

    public function hideSoldExpiringProduct($idProduct = null)
    {
        $qb1 = sqlQueryBuilder()->update('products_variations', 'pv')
            ->join('pv', 'products', 'p', 'p.id = pv.id_product')
            ->set('pv.figure', "'N'")
            ->andWhere("JSON_CONTAINS_PATH(p.data, 'one', '$.expiringBatchProduct')")
            ->andWhere('pv.in_store <= 0');

        $qb2 = sqlQueryBuilder()->update('products')
            ->set('figure', "'N'")
            ->andWhere("JSON_CONTAINS_PATH(data, 'one', '$.expiringBatchProduct')")
            ->andWhere('in_store <= 0');

        if ($idProduct) {
            $qb1->andWhere(Operator::equals(['pv.id_product' => $idProduct]));
            $qb2->andWhere(Operator::equals(['id' => $idProduct]));
        }

        $qb1->execute();
        $qb2->execute();
    }

    protected function duplicateProduct(int $idProduct, bool $hasVariation = false): int
    {
        $newIdProduct = $this->productUtils->duplicateProduct($idProduct);
        $this->productUtils->copyProductData($idProduct, $newIdProduct, [
            '_all_' => true,
            'pricelist' => 'products',
            'photos' => 'products',
            'variations' => false,
            'products_related' => true,
            'products_collections' => false,
        ]);

        $oldProductData = sqlQueryBuilder()->select('date_added')
            ->from('products')
            ->where(Operator::equals(['id' => $idProduct]))
            ->execute()->fetchAssociative();

        $values = [
            'show_in_feed' => 'N',
            'date_added' => $oldProductData['date_added'],
            'in_store' => 0,
            'discount' => 0,
        ];

        if (!$hasVariation) {
            $values['ean'] = EANValidator::generateEan(['id_product' => $newIdProduct]);
        }

        sqlQueryBuilder()->update('products')
            ->directValues($values)
            ->set('data', "JSON_INSERT(COALESCE(data, JSON_OBJECT()), '$.expiringBatchProduct', :originalProduct)")
            ->where(Operator::equals([
                'id' => $newIdProduct,
            ]))
            ->setParameter('originalProduct', $idProduct, ParameterType::INTEGER)
            ->execute();

        $this->insertProductsRelated($newIdProduct, $idProduct, 1);
        $this->insertProductsRelated($idProduct, $newIdProduct, 1);

        if (findModule(\Modules::PRODUCTS_COLLECTIONS)) {
            $this->insertProductsCollections($idProduct, $newIdProduct);
        }

        return $newIdProduct;
    }

    protected function insertProductsRelated($idTop, $idRel, $type)
    {
        $values = [
            'id_top_product' => $idTop,
            'id_rel_product' => $idRel,
            'position' => 0,
        ];

        if (findModule(\Modules::PRODUCTS_RELATED, \Modules::SUB_TYPES)) {
            $values['type'] = $type;
        }

        sqlQueryBuilder()->insert('products_related')
            ->directValues($values)->onDuplicateKeyUpdate([])->execute();
    }

    protected function insertProductsCollections($idTop, $idRel)
    {
        sqlQueryBuilder()->insert('products_collections')
            ->directValues([
                'id_product' => $idTop,
                'id_product_related' => $idRel,
            ])->onDuplicateKeyUpdate([])->execute();
    }

    public function clearPriceForDiscount($idProduct, $idVariation)
    {
        if (findModule(\Modules::PRICE_HISTORY)) {
            sqlQueryBuilder()->delete('price_history')
                ->where(Operator::equalsNullable(['id_product' => $idProduct, 'id_variation' => $idVariation]))
                ->execute();
        }
    }

    public function expiringProductVariationAlreadyExists($idProduct, $idVariation = false)
    {
        return sqlQueryBuilder()
            ->select('p.id id_product, pv.id id_variation, COALESCE(pv.in_store, p.in_store) in_store')
            ->from('products', 'p')
            ->leftJoin('p', 'products_variations', 'pv', 'pv.id_product = p.id')
            ->andWhere(Operator::equalsNullable([
                JsonOperator::extract('p.data', '$.expiringBatchProduct') => $idProduct,
            ] +
                (($idVariation === false) ? [] : [JsonOperator::extract('pv.data', '$.expiringBatchVariation') => $idVariation])
            ))
            ->execute()->fetchAssociative();
    }

    public function expiringProductAlreadyExists($idProduct)
    {
        return $this->expiringProductVariationAlreadyExists($idProduct)['id_product'] ?? null;
    }

    public function addDiscountToProduct($oldIdProduct, $oldIdVariation, $idProduct, $idVariation, $discount)
    {
        if (is_null($idVariation)) {
            sqlQueryBuilder()->update('products')
                ->directValues(['discount' => $discount])
                ->where(Operator::equals(['id' => $idProduct]))
                ->execute();

            return;
        }

        $oldPrice = sqlQueryBuilder()->select('COALESCE(pv.price, p.price) price, p.vat')->from('products', 'p')
            ->leftJoin('p', 'products_variations', 'pv', 'pv.id_product = p.id')
            ->where(Operator::equalsNullable(['p.id' => $oldIdProduct, 'pv.id' => $oldIdVariation]))
            ->execute()->fetchAssociative();

        $vatId = $oldPrice['vat'];
        $vat = getVat($vatId);
        $oldPrice = \Decimal::fromString($oldPrice['price']);
        $discountPrice = $this->discountUtil->calculateDiscountPrice($oldPrice, ['unit' => 'perc', 'discount' => $discount]);

        $updateValues = [
            'price' => $oldPrice->sub($discountPrice),
        ];

        if (findModule(\Modules::PRICE_HISTORY)) {
            $updateValues['price_for_discount'] = $oldPrice;
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_COMMON)) {
            $updateValues['price_common'] = $oldPrice->addVat($vat);
        }

        sqlQueryBuilder()->update('products_variations')
            ->directValues($updateValues)
            ->where(Operator::equals(['id' => $idVariation]))
            ->execute();
    }

    public function modifyProductExpiringTitle($idProduct, $text)
    {
        sqlQueryBuilder()->update('products')
            ->set('title', "CONCAT(:text,' ', title)")
            ->where(Operator::equals([
                'id' => $idProduct,
            ]))
            ->setParameter('text', $text)
            ->execute();
    }
}