kupshop/bundles/KupShop/StoresBundle/Utils/StoreWarehouseRecalculate.php

<?php

namespace KupShop\StoresBundle\Utils;

use KupShop\ProductsBatchesBundle\Util\ProductsBatchesUtil;
use Query\Operator;
use Query\QueryBuilder;
use Symfony\Contracts\Service\Attribute\Required;

class StoreWarehouseRecalculate
{
    /**
     * @var ProductsBatchesUtil
     */
    private $productsBatchesUtil;

    #[Required]
    public StoresInStore $stores;

    public function fixStoreWarehouseErrors($mainStoreOnly = false, $deleteStoresItems = false)
    {
        $stores = sqlQueryBuilder()
            ->select('id, id_base_position, JSON_EXTRACT(data, "$.warehouse_locations") as locations, type, id_delivery')
            ->from('stores')
            ->andWhere(Operator::equals(['type' => StoresInStore::TYPE_STORE]))
            ->execute()
            ->fetchAllAssociativeIndexed();

        // Update all stores
        foreach ($stores as $storeId => $store) {
            if (!$mainStoreOnly) {
                if (empty($store['id_base_position']) || empty($store['locations']) || $store['type'] == StoresInStore::TYPE_EXTERNAL_STORE) {
                    continue;
                }

                $this->updateStore($storeId, function (QueryBuilder $qb) use ($store) {
                    $qb->orWhere(Operator::equals(['wpos.id' => $store['id_base_position']]))
                        ->orWhere(Operator::inStringArray(json_decode($store['locations']), 'wpos.id_location'))
                        ->addGroupBy('wp.id_product, wp.id_variation');
                }, $deleteStoresItems);
            }
        }

        // Update main store
        $storeId = $this->stores->getMainStoreId();
        $this->updateStore($storeId, function (QueryBuilder $qb) use ($stores) {
            foreach ($stores as $store) {
                if (empty($store['id_base_position']) || empty($store['locations']) || $store['type'] == StoresInStore::TYPE_EXTERNAL_STORE) {
                    continue;
                }

                $qb->andWhere(Operator::not(Operator::equals(['wpos.id' => $store['id_base_position']])))
                    ->andWhere(Operator::not(Operator::inStringArray(json_decode($store['locations']), 'wpos.id_location')));
            }

            $qb->addGroupBy('wp.id_product, wp.id_variation');
        }, $deleteStoresItems);
    }

    public function updateStore($id_store, callable $whereSpec, $deleteStoresItems = false)
    {
        // NOTE Spouští se pouze pro interní sklad, kde se nemají aktualizovat šarže (proto jsou šarže vymazány)

        if ($deleteStoresItems) {
            sqlQueryBuilder()
                ->delete('stores_items', 'si')
                ->leftJoin('si', 'stores', 's', 's.id = si.id_store')
                ->andWhere(
                    Operator::andX(
                        Operator::orX(
                            Operator::equals(['s.type' => StoresInStore::TYPE_STORE]),
                            Operator::equals(['s.type' => StoresInStore::TYPE_MAIN_STORE])
                        ),
                        Operator::equals(['s.id' => $id_store])
                    )
                )
                ->execute();
        }

        // Vytáhne stav skladu z konkrétních warehouse pozic
        $warehouseQuantity = sqlQueryBuilder()->select('wp.id_product', 'wp.id_variation', 'SUM(wp.pieces) quantity')
            ->from('warehouse_products', 'wp')
            ->leftJoin('wp', 'warehouse_positions', 'wpos', 'wp.id_position = wpos.id')
            ->andWhere($whereSpec);

        $qb = sqlQueryBuilder()->select('p.id AS id_product, pv.id AS id_variation, 
                p.title AS product_title, pv.title AS variation_title,  
                IFNULL(wq.quantity, 0) AS warehouse_qty, 
                si.quantity AS store_qty,
                :id_store as id_store')
            ->fromProducts()
            ->joinVariationsOnProducts();

        if (findModule(\Modules::PRODUCTS_BATCHES)) {
            $qb->addSelect('wq.id_product_batch');
            $warehouseQuantity->addSelect('wp.id_product_batch')
               ->groupBy('wp.id_product', 'wp.id_variation');
        }

        $qb->leftJoinSubQuery('p', $warehouseQuantity, 'wq', 'wq.id_product = p.id AND (wq.id_variation = pv.id OR (wq.id_variation IS NULL AND pv.id IS NULL))')
            ->leftJoin('p', 'stores_items', 'si', 'si.id_product = p.id AND (si.id_variation = pv.id OR (si.id_variation IS NULL AND pv.id IS NULL)) AND si.id_store = :id_store')
            ->setParameter('id_store', $id_store);

        if ($id_store) {
            // Odkomentováno, původně byl zde koment, že nefunguje, ale zdá se že funguje (nebo jsem nenašel use-case na kterém ne)
            $ordersQuantity = sqlQueryBuilder()->select('oi.id_product', 'oi.id_variation', 'SUM(pieces) quantity')
                ->from('order_items', 'oi')
                ->leftJoin('oi', 'orders', 'o', 'oi.id_order = o.id')
                ->join('oi', 'order_stores', 'os', 'os.id_order = o.id AND os.id_store = :id_store')
                ->setParameter('id_store', $id_store)
                ->leftJoin('oi', 'warehouse_orders', 'wo', 'oi.id_order = wo.id_order')
                 ->where(Operator::andX(
                     Operator::inIntArray(array_merge(getStatuses('notpacked'), $cfg['Order']['Status']['warehouse_remaining'] ?? []), 'o.status'),
                     'wo.id_position IS NOT NULL OR wo.id IS NULL'
                 ))
                ->andWhere(\Query\Order::activeOnly())
                ->andWhere(Operator::not(\Query\Order::byItemType('return')))
                ->groupBy('oi.id_product', 'oi.id_variation');

            $qb->addSelect('IFNULL(oq.quantity, 0) AS orders_qty')
                ->leftJoinSubQuery('p', $ordersQuantity, 'oq', 'oq.id_product = p.id AND (oq.id_variation = pv.id OR (oq.id_variation IS NULL AND pv.id IS NULL))')
                ->where('IFNULL(wq.quantity, 0) - IFNULL(oq.quantity, 0) != IFNULL(si.quantity, 0)');
        } else {
            $qb->where('IFNULL(wq.quantity, 0) != IFNULL(si.quantity, 0)');
        }

        $storeProductsErrors = $qb->execute()->fetchAllAssociative();

        foreach ($storeProductsErrors as $error) {
            $error['quantity'] = $error['warehouse_qty'] - ($error['orders_qty'] ?? 0);
            if (is_null($error['store_qty'])) {
                sqlQuery('INSERT INTO stores_items 
                      SET id_store=:id_store, id_product=:id_product, id_variation=:id_variation,
                      quantity=:quantity ON DUPLICATE KEY UPDATE quantity=:quantity', $error);
            } else {
                sqlQueryBuilder()->update('stores_items')
                    ->set('quantity', $error['quantity'])
                    ->where(Operator::equals(['id_store' => $error['id_store'], 'id_product' => $error['id_product']]))
                    ->andWhere(Operator::equalsNullable(['id_variation' => $error['id_variation']]))
                    ->execute();
            }

            if (findModule(\Modules::PRODUCTS_BATCHES) && ($error['id_product_batch'] ?? false)) {
                $this->productsBatchesUtil->setBatchesToCustomData('stores_items', [
                    'id_store' => $error['id_store'],
                    'id_product' => $error['id_product'],
                    'id_variation' => $error['id_variation'],
                ], [$error['id_product_batch'] => $error['quantity']]);
            }
        }
    }

    /**
     * @required
     */
    public function setProductsBatchesUtil(?ProductsBatchesUtil $productsBatchesUtil): void
    {
        $this->productsBatchesUtil = $productsBatchesUtil;
    }
}