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

<?php

declare(strict_types=1);

namespace KupShop\WarehouseBundle\Util;

use KupShop\KupShopBundle\Query\JsonOperator;
use KupShop\WarehouseBundle\Entity\StoreItem;
use Query\Operator;

class ExpiringProductUtil extends \KupShop\AdminBundle\Util\ExpiringProductUtil
{
    /**
     * @var StoreItemWorker
     */
    protected $storeItemWorker;

    /**
     * @required
     */
    public function setStoreItemWorker(StoreItemWorker $storeItemWorker): void
    {
        $this->storeItemWorker = $storeItemWorker;
    }

    public function createFromPosition(int $idProduct, ?int $idVariation, ?int $idPosition, $prependText = '')
    {
        return sqlGetConnection()->transactional(function () use ($idProduct, $idVariation, $idPosition, $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);

            $movedPieces = $this->movePiecesToExpiringProduct($idProduct,
                $idVariation,
                false,
                $newIdProduct,
                $newIdVariation,
                false,
                $idPosition);

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

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

    public function createFromBatch(int $idProduct, int $idBatch, ?int $idPosition, $prependText = ''): array
    {
        return sqlGetConnection()->transactional(function () use ($idProduct, $idBatch, $idPosition, $prependText) {
            $batch = sqlQueryBuilder()->select('*')
                ->from('products_batches', 'pb')
                ->where(Operator::equals(['id' => $idBatch]))
                ->execute()->fetchAssociative();

            $idVariation = $batch['id_variation'];

            $newIdVariation = null;

            $DMTAlreadyExists = $this->expiringProductWithDMTAlreadyExists($idProduct, $idVariation, $batch['date_expiry']);

            if ($DMTAlreadyExists) {
                $newIdProduct = $DMTAlreadyExists['id_product'];
                $newIdVariation = $DMTAlreadyExists['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);

            $newIdBatch = $this->duplicateBatch($batch, $newIdProduct, $newIdVariation);

            $movedPieces = $this->movePiecesToExpiringProduct($idProduct,
                $idVariation,
                $idBatch,
                $newIdProduct,
                $newIdVariation,
                $newIdBatch,
                $idPosition);

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

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

    protected function movePiecesToExpiringProduct(
        $idProduct,
        $idVariation,
        $idBatch,
        $newIdProduct,
        $newIdVariation,
        $newIdBatch,
        $idPosition = null,
    ) {
        $movePieces = $this->getAvailablePiecesToMoveWarehouse($idProduct, $idVariation, $idBatch, $idPosition);

        $availablePositions = sqlQueryBuilder()->select('wpos.id, wpos.code, wp.pieces')->from('warehouse_products', 'wp')
            ->innerJoin('wp', 'warehouse_positions', 'wpos', 'wp.id_position = wpos.id')
            ->innerJoin('wpos', 'warehouse_locations', 'wl', 'wpos.id_location = wl.id')
            ->andWhere(Operator::orX("wl.code != 'BOX'", Operator::equals(['wpos.id' => $idPosition])))
            ->andWhere('wp.pieces > 0')
            ->andWhere(Operator::equalsNullable(['id_product' => $idProduct, 'id_variation' => $idVariation]));

        if ($idPosition) {
            $availablePositions->andWhere(Operator::equals(['wp.id_position' => $idPosition]));
        }

        if ($idBatch !== false) {
            $availablePositions->andWhere(Operator::equals(['id_product_batch' => $idBatch]));
        }

        $availablePositions = $availablePositions->orderBy('wp.pieces', 'DESC')
            ->execute()->fetchAllAssociative();

        $storeItem = new StoreItem(['id_product' => $idProduct, 'id_variation' => $idVariation, 'id_product_batch' => $idBatch ?: null]);
        $storeItemNew = new StoreItem(['id_product' => $newIdProduct,
            'id_variation' => $newIdVariation,
            'id_product_batch' => $newIdBatch ?: null]);

        $movePiecesLeft = $movePieces;
        $movedLog = [];
        foreach ($availablePositions as $position) {
            if ($movePiecesLeft <= 0) {
                break;
            }
            $movePiecesPosition = (int) min($movePiecesLeft, $position['pieces']);

            $this->storeItemWorker->updateOnPosition($storeItem, (int) $position['id'], -$movePiecesPosition);
            $this->storeItemWorker->createStoreItem($storeItemNew, $movePiecesPosition, (int) $position['id']);

            $movedLog['pozice '.$position['code']] = 'Kusů: '.$movePiecesPosition;

            $movePiecesLeft -= $movePiecesPosition;
        }

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

        return $movePieces;
    }

    public function getAvailablePiecesToMoveWarehouse($idProduct, $idVariation, $idBatch, $idPosition = null)
    {
        $whereParameters = [
            'id_product' => $idProduct,
            'id_variation' => $idVariation,
        ];
        if ($idBatch !== false) {
            $whereParameters['id_product_batch'] = $idBatch;
        }
        if ($idPosition) {
            $whereParameters['wp.id_position'] = $idPosition;
        }

        $warehousePieces = sqlQueryBuilder()->select('SUM(pieces)')->from('warehouse_products', 'wp')
            ->innerJoin('wp', 'warehouse_positions', 'wpos', 'wp.id_position = wpos.id')
            ->innerJoin('wpos', 'warehouse_locations', 'wl', 'wpos.id_location = wl.id')
            ->where('')
            ->andWhere(Operator::orX("wl.code != 'BOX'", Operator::equals(['wpos.id' => $idPosition])))
            ->andWhere(Operator::equalsNullable($whereParameters))->execute()->fetchOne();

        $webPieces = sqlQueryBuilder()->select('COALESCE(pv.in_store, p.in_store) in_store')->from('products', 'p')
            ->leftJoin('p', 'products_variations', 'pv', 'pv.id_product = p.id')
            ->where(Operator::equalsNullable([
                'p.id' => $idProduct,
                'pv.id' => $idVariation,
            ]))->execute()->fetchOne();

        return max(min($warehousePieces, $webPieces), 0);
    }

    protected function duplicateBatch(array $batch, int $newIdProduct, ?int $newIdVariation)
    {
        sqlQuery('INSERT IGNORE INTO products_batches (id_product, id_variation, code, date_expiry)
                SELECT :id_product_to, :id_variation_to, code, date_expiry
                FROM products_batches
                WHERE id=:id_batch_from',
            ['id_batch_from' => $batch['id'], 'id_product_to' => $newIdProduct, 'id_variation_to' => $newIdVariation]);

        return sqlQueryBuilder()->select('id')->from('products_batches')
            ->where(Operator::equalsNullable([
                'id_product' => $newIdProduct,
                'id_variation' => $newIdVariation,
                'code' => $batch['code'],
                'date_expiry' => $batch['date_expiry'],
            ]))->execute()->fetchOne();
    }

    public function expiringProductWithDMTAlreadyExists($idProduct, $idVariation, $dateExpiry)
    {
        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')
            ->leftJoin('pv', 'products_batches', 'pb', 'p.id = pb.id_product AND pv.id <=> pb.id_variation')
            ->andWhere(Operator::equalsNullable([
                JsonOperator::extract('p.data', '$.expiringBatchProduct') => $idProduct,
                JsonOperator::extract('pv.data', '$.expiringBatchVariation') => $idVariation,
                'pb.date_expiry' => $dateExpiry]))
            ->execute()->fetchAssociative();
    }

    public function productHasEnabledBatches($idProduct)
    {
        if (!findModule(\Modules::PRODUCTS_BATCHES)) {
            return false;
        }

        return sqlQueryBuilder()->select('1')
            ->from('products')->where(Operator::equals([
                'id' => $idProduct,
                'batch_number_require' => 'Y',
            ]))
            ->execute()->fetchOne();
    }
}