kupshop/admin/stockInOrder.php

<?php

use Query\Operator;

$main_class = 'StockInOrder';

class StockInOrder extends Frame
{
    protected $template = 'window/stockInOrder.tpl';

    public function handleOrdersOfSupplierAdd()
    {
        $data = getVal('data');
        $ErrStr = '';
        if (!empty($data['pieces'])) {
            require_once 'ordersOfSuppliers.php';
            $orders = new OrdersOfSuppliers();
            $orders->setID($data['id_supplier']);
            $orders->handleAddValue($data);
            $ErrStr = '&OkStr='.urlencode('Přidáno do seznamu.');
        }
        redirect("launch.php?s=stockInOrder.php&acn=edit&IDp={$data['id_product']}&IDv={$data['id_variation']}".$ErrStr);
    }

    public function handleMovePieces()
    {
        $data = getVal('data');
        $ErrStr = '';
        sqlStartTransaction();
        foreach ($data['move_pieces'] as $key => $pieces) {
            if (!empty($pieces['pieces'])) {
                $product = new Product($key);
                $product->storeIn($pieces['id_variation'], '-'.$pieces['pieces']);

                $product = new Product($data['id_product']);
                $product->storeIn($data['id_variation'], $pieces['pieces']);
                if (empty($ErrStr)) {
                    $ErrStr = '&OkStr='.urlencode('Kusy přesunuty.');
                }
            }
        }
        sqlFinishTransaction();
        redirect("launch.php?s=stockInOrder.php&acn=edit&IDp={$data['id_product']}&IDv={$data['id_variation']}".$ErrStr);
    }

    public function get_vars()
    {
        $vars = parent::get_vars();
        $pageVars = getVal('body', $vars);

        $IDp = getVal('IDp');
        $IDv = getVal('IDv');

        $pageVars['id_product'] = $IDp;
        $pageVars['id_variation'] = $IDv;

        if (!$IDp) {
            throw new InvalidArgumentException('Specify product ID');
        }

        $posQuery = sqlQueryBuilder()
            ->select('pos.in_store, pos.code, s.order_url, s.name, s.id')
            ->from('products_of_suppliers', 'pos')
            ->leftJoin('pos', 'suppliers', 's', 'pos.id_supplier = s.id')
            ->leftJoin('pos', 'products', 'p', 'pos.id_product = p.id')
            ->leftJoin('p', 'vats', 'v', 'v.id = p.vat');

        if (findModule(\Modules::STOCK_IN)) {
            $subQuery = sqlQueryBuilder()
                ->select('sii.price as buy_price')
                ->from('stock_in', 'si')
                ->leftJoin('si', 'stock_in_items', 'sii', 'si.id = sii.id_stock_in')
                ->where('sii.id_product = pos.id_product')
                ->andWhere(Operator::equalsToOrNullable('sii.id_variation', 'pos.id_variation'))
                ->andWhere('si.id_supplier = s.id')
                ->andWhere(Operator::inStringArray(['invoice'], 'si.id_index'))
                ->orderBy('sii.id', 'DESC')
                ->setMaxResults(1);

            $posQuery->addSelect("({$subQuery->getSQL()}) as buy_price");
        }

        $posQuery->addSelect('pos.price_buy, v.vat');

        if (findModule('orders_of_suppliers')) {
            $posQuery->addSelect('oos.pieces')
                ->leftJoin('pos', 'orders_of_suppliers', 'oos', 'pos.id_supplier=oos.id_supplier AND oos.id_product=pos.id_product AND '.Operator::equalsToOrNullable('oos.id_variation', 'pos.id_variation'));
        }

        $posQuery->andWhere(Operator::equals(['pos.id_product' => $IDp]));
        if ($IDv > 0) {
            $posQuery->andWhere(Operator::equals(['pos.id_variation' => $IDv]));
        }
        $posQuery->addOrderBy('s.id');

        if (findModule(\Modules::STOCK_IN)) {
            $posQuery->addParameters(
                $subQuery->getParameters(),
                $subQuery->getParameterTypes()
            );
        }

        $inSuppliers = $posQuery->execute();

        $pageVars['suppliers'] = [];
        foreach ($inSuppliers as $row) {
            $pageVars['suppliers'][] = $row;
        }

        // do ordering
        usort($pageVars['suppliers'], function ($a, $b) {
            if ($a['buy_price'] === null) {
                return 1;
            } elseif ($b['buy_price'] === null) {
                return -1;
            }

            return $a['buy_price'] <=> $b['buy_price'];
        });
        foreach ($pageVars['suppliers'] as $key => $supplier) {
            if ($supplier['in_store'] == 0) {
                unset($pageVars['suppliers'][$key]);
                $pageVars['suppliers'][] = $supplier;
            }
        }

        if (findModule('orders_of_suppliers')) {
            foreach ($pageVars['suppliers'] as &$supplier) {
                $qbInSuppliers = sqlQueryBuilder()->select('SUM(sii.quantity) AS quantity, si.id AS id_stockin')
                    ->from('stock_in_items', 'sii')
                    ->leftJoin('sii', 'stock_in', 'si', 'sii.id_stock_in = si.id')
                    ->where(Operator::equals([
                        'si.id_index' => 'future',
                        'sii.id_product<=>:id_product',
                        'sii.id_variation<=>:id_variation',
                    ]))
                    ->setParameter('id_supplier', $supplier['id'])
                    ->setParameter('id_product', $IDp)
                    ->setParameter('id_variation', $IDv);
                $supplier = array_merge($supplier, $qbInSuppliers->execute()->fetchAllAssociative());

                $SQL = sqlQuery('SELECT COALESCE(pv.in_store ,p.in_store) AS in_store, pos.id_product, pos.id_variation, p.title AS product_title, pv.title AS variation_title
                           FROM '.getTableName('products_of_suppliers').' AS pos
                           LEFT JOIN '.getTableName('products').' AS p ON p.id = pos.id_product
                           LEFT JOIN '.getTableName('products_variations').' AS pv ON pv.id = pos.id_variation
                           WHERE pos.id_supplier=:id_supplier AND pos.code=:code AND pos.id_product!=:id_product', [
                    'id_supplier' => $supplier['id'],
                    'code' => $supplier['code'],
                    'id_product' => $IDp,
                ]);

                foreach ($SQL as $product) {
                    $supplier['same_code'][] = $product;
                }
            }
        }

        $vars['body'] = $pageVars;

        return $vars;
    }
}