kupshop/admin/ordersOfSuppliers.php

<?php

$main_class = 'OrdersOfSuppliers';

class OrdersOfSuppliers extends Window
{
    protected $tableName = 'orders_of_suppliers';

    public function __construct()
    {
        if (getVal('print')) {
            $this->setTemplate('window/ordersOfSuppliersPrint.tpl');
        }
    }

    public function get_vars()
    {
        $vars = parent::get_vars();
        $acn = $this->getAction();

        if ($acn == 'add') {
            $vars['suppliers'] = sqlFetchAll($this->selectSQL('suppliers', []), 'id');
        }

        return $vars;
    }

    public function handleUpdate()
    {
        $data = $this->getData();
        $items = getVal('items', $data, []);

        if (!$this->getID()) {
            $this->setID($data['id_supplier']);
        }

        $items = array_reverse($items, true);

        foreach ($items as $id => $item) {
            $item['id'] = intval($id);

            if (!empty($item['delete']) || !$id) {
                if ($id > 0) {
                    $this->handleDeleteValue($item);
                }
                continue;
            }

            if (empty($item['id_product'])) {
                continue;
            }

            if ($id < 0) {
                $this->handleAddValue($item);
            } else {
                $this->handleUpdateValue($item);
            }
        }

        return true;
    }

    public function handleDeleteValue($item)
    {
        $this->deleteSQL('orders_of_suppliers', ['id' => $item['id']]);
    }

    public function handleUpdateValue($item)
    {
        $this->updateSQL('orders_of_suppliers', ['pieces' => $item['pieces']], ['id' => $item['id']]);
    }

    public function handleAddValue($item)
    {
        $ID = $this->getID();

        $this->prepareNull($item['id_variation']);

        $where = ['id_supplier' => $ID,
            'id_product' => $item['id_product'],
            'id_variation' => $item['id_variation'], ];

        $data = [
            'pieces' => $item['pieces'],
            'date_create' => date('Y-m-d H:i:s'),
        ];

        $items = sqlFetch($this->selectSQL('orders_of_suppliers', $where, ['id', 'pieces']));
        if (!empty($items['id'])) {
            $data['pieces'] += $items['pieces'];
            $this->updateSQL('orders_of_suppliers', ['pieces' => $data['pieces']], ['id' => $items['id']]);
        } else {
            $this->insertSQL('orders_of_suppliers', array_merge($data, $where));
        }
    }

    public function handleDelete()
    {
        require_once 'stockIn.php';

        $ID = $this->getID();

        $this->insertSQL('stock_in', [
            'id_index' => 'future',
            'date_created' => date('Y-m-d H:i:s'),
            'id_supplier' => $ID,
            'date_expiration' => '',
            'date_issued' => '',
        ]);
        $IDStockIn = sqlInsertId();

        $stockIn = new StockIn();
        $stockIn->setID($IDStockIn);

        $priceBuyQuery = $this->getBuyPriceQuery();

        $SQL = sqlQuery("SELECT oos.*, {$priceBuyQuery} AS price, oos.pieces AS quantity
               FROM orders_of_suppliers oos
               LEFT JOIN products AS p ON p.id=oos.id_product
               LEFT JOIN products_variations AS pv ON pv.id=oos.id_variation
               WHERE id_supplier=:id_supplier
               ORDER BY p.title DESC, pv.title DESC", ['id_supplier' => $ID]);
        foreach ($SQL as $item) {
            $item['name'] = Product::getName($item['id_product']);
            $stockIn->handleAddValue($item);
        }

        $this->deleteSQL('orders_of_suppliers', ['id_supplier' => $ID]);

        writeDownActivity(sprintf(translate('activityDeleted'), $ID));

        $this->redirect(['s' => 'stockIn.php', 'ID' => $IDStockIn]);
    }

    public function handleClear()
    {
        $ID = $this->getID();

        $this->deleteSQL('orders_of_suppliers', ['id_supplier' => $ID]);
        redirect("launch.php?s=ordersOfSuppliers.php&acn=edit&ID={$ID}");
    }

    public function getObject()
    {
        $ID = $this->getID();
        $supplier = sqlFetchAssoc(sqlQuery('SELECT * FROM '.getTableName('suppliers').' WHERE id=:ID', ['ID' => $ID]));

        $priceBuyQuery = $this->getBuyPriceQuery();

        $SQL2 = sqlQuery('SELECT oos.id, COALESCE(pos.code, pv.code, p.code) AS code, COALESCE(pos.ean, pv.ean, p.ean) AS ean, 
                p.title AS name, p.id AS id_product, COALESCE(pv.price, p.price) AS price, oos.pieces, pv.title AS variation, 
                pv.id AS id_variation, '.$priceBuyQuery.' AS price_buy
                FROM orders_of_suppliers AS oos
                LEFT JOIN products_of_suppliers AS pos ON pos.id_supplier=:ID AND pos.id_product=oos.id_product AND (pos.id_variation=oos.id_variation OR (pos.id_variation IS NULL AND oos.id_variation IS NULL))
                LEFT JOIN products AS p ON p.id=oos.id_product
                LEFT JOIN products_variations AS pv ON pv.id=oos.id_variation
                WHERE oos.id_supplier=:ID 
                ORDER BY p.title, pv.title', ['ID' => $ID]);
        $items = [];
        $price = 0;
        foreach ($SQL2 as $row) {
            $price += $row['pieces_price'] = ($row['price_buy'] > 0 ? $row['price_buy'] : $row['price']) * $row['pieces'];
            $items[$row['id']] = $row;
        }

        return ['id' => $ID,
            'items' => $items,
            'supplier' => $supplier,
            'price' => $price,
        ];
    }

    protected function getBuyPriceQuery()
    {
        $sqlPrice = [];

        if (findModule(\Modules::PRODUCTS_SUPPLIERS)) {
            $sqlPrice[] = 'SELECT pos.price_buy
             FROM products_of_suppliers pos
             WHERE pos.id_supplier=oos.id_supplier 
               AND oos.id_product=pos.id_product 
               AND ((oos.id_variation IS NULL AND pos.id_variation IS NULL) OR oos.id_variation=pos.id_variation) 
               AND pos.price_buy > 0';
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_BUY)) {
            $sqlPrice[] = 'SELECT COALESCE(pv.price_buy, p.price_buy)
             FROM products p
             LEFT JOIN products_variations pv ON p.id = pv.id_product
             WHERE oos.id_product=p.id 
               AND ((oos.id_variation IS NULL AND pv.id IS NULL) OR oos.id_variation=pv.id) 
               AND COALESCE(pv.price_buy, p.price_buy) > 0
             LIMIT 1';
        }

        $sqlPrice[] = 'SELECT sii.price
             FROM stock_in_items sii
             LEFT JOIN stock_in si ON si.id = sii.id_stock_in
             WHERE si.id_supplier=oos.id_supplier AND si.id_index="invoice" 
               AND oos.id_product=sii.id_product 
               AND ((oos.id_variation IS NULL AND sii.id_variation IS NULL) OR oos.id_variation=sii.id_variation) 
               AND sii.price > 0
             ORDER BY si.date_issued DESC
             LIMIT 1';

        return 'COALESCE(('.join('),(', $sqlPrice).'))';
    }
}