kupshop/admin/inventoryItems.php

<?php

$main_class = 'InventoryItems';

class InventoryItems extends Frame
{
    use DatabaseCommunication;

    protected $template = 'window/inventoryItems.tpl';

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

        $ID = $this->getID();

        $SQL = sqlQuery('SELECT s.*
						FROM '.getTableName('inventory')." s
						WHERE s.id='".$ID."' ");
        if (sqlNumRows($SQL) == 1) {
            $pageVars['inventory'] = sqlFetchArray($SQL);
        }
        $fields = 'si.id, si.id_product, si.id_variation, si.quantity, p.title as product_title, pv.title variation_title,
			(COALESCE(pv.in_store, p.in_store)+(
				SELECT COALESCE(SUM(pieces), 0)
				FROM '.getTableName('order_items').' oi
				LEFT JOIN '.getTableName('orders').' o ON oi.id_order = o.id
				WHERE o.status IN ('.join(',', getStatuses('notpacked')).') AND ((oi.id_variation = si.id_variation) OR (si.id_variation IS NULL AND oi.id_product = si.id_product))
			)) as web_in_store,
			(
				SELECT COALESCE(SUM(quantity), 0)
				FROM '.getTableName('inventory_items').' ii
				WHERE (ii.id_variation = si.id_variation) OR (si.id_variation IS NULL AND ii.id_product = si.id_product)
			) as in_store, COALESCE(pv.ean, p.ean) as ean';

        if (findModule('products_variations', 'variationCode')) {
            $fields .= ', COALESCE(pv.code, p.code) as code';
        } else {
            $fields .= ', p.code as code';
        }

        if (findModule('products', 'weight')) {
            if (findModule('products_variations')) {
                $fields .= ', COALESCE(pv.weight, p.weight) as weight';
            } else {
                $fields .= ', p.weight as weight';
            }
        }

        if (findModule('products', 'note')) {
            $fields .= ', COALESCE(pv.note, p.note) as note';
        }

        $SQL = sqlQuery("SELECT {$fields}
			      FROM ".getTableName('inventory_items').' si
			      LEFT JOIN '.getTableName('products').' p ON p.id=si.id_product
			      LEFT JOIN '.getTableName('products_variations')." pv ON pv.id=si.id_variation
			      WHERE si.id_inventory='".$ID."' ORDER BY si.id DESC");

        $items = [];
        $total_price_vat = 0;
        foreach ($SQL as $key => $row) {
            // TODO
            // $pageVars['total_price_vat'] += ($row["price"]*$row["quantity"])*(1+$row["vat"]/100);

            $query = 'SELECT i.id, i.name, ii.quantity
				FROM '.getTableName('inventory_items').' ii
				LEFT JOIN '.getTableName('inventory')." i ON i.id=ii.id_inventory
				WHERE ii.id_product={$row['id_product']} AND i.id!={$ID} ";
            if (!empty($row['id_variation'])) {
                $query .= " AND ii.id_variation = {$row['id_variation']}";
            }

            $row['inventory'] = sqlFetchAll(sqlQuery($query));

            if ($row['web_in_store'] > $row['in_store']) {
                $row['color'] = '#FDD7DB';
            }// "#9b313b";
            elseif ($row['web_in_store'] < $row['in_store']) {
                $row['color'] = '#fceecd';
            }// "#9a7a2c";
            else {
                $row['color'] = '#E5FCCD';
            }// "#699b31";

            $items[++$key] = $row;
        }

        $pageVars['items'] = $items;
        $pageVars['total_price_vat'] = $total_price_vat;

        $vars['body']['data'] = $pageVars;

        return $vars;
    }

    public function handle()
    {
        $data = getVal('data');

        $items = getVal('items', $data, []);
        foreach ($items as $id => $item) {
            $link['id'] = intval($item['item_id']);

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

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

                continue;
            }
            $item['code'] = (empty($item['code']) ? null : $item['code']);
            $item['weight'] = $this->preparePrice($item['weight']);
            $item['ean'] = (empty($item['ean']) ? null : $item['ean']);
            $item['id_variation'] = (empty($item['id_variation']) ? null : $item['id_variation']);

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

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

        $SQL = sqlQuery('INSERT INTO '.getTableName('inventory_items').'
		SET id_inventory=:id_inventory, id_product=:id_product, id_variation=:id_variation,
		quantity=:quantity ON DUPLICATE KEY UPDATE quantity=quantity+:quantity',
            ['id_inventory' => $id_inventory,
                'id_product' => $item['id_product'],
                'id_variation' => $item['id_variation'],
                'quantity' => $item['quantity'],
            ]);

        // if(!empty($item['code']) || !empty($item['ean']))
        // {
        $fields = ['ean' => $item['ean']];

        // If variations has code, update directly
        if (findModule('products_variations', 'variationCode')) {
            $fields['code'] = $item['code'];
        } elseif (!empty($item['id_variation'])) {
            // If variations does not have code, update product code
            $this->updateSQL('products', ['code' => $item['code']], ['id' => $item['id_product']]);
        }

        if (findModule('products', 'weight')) {
            $fields['weight'] = $this->preparePrice($item['weight']);
        }

        if (!empty($item['id_variation'])) {
            $this->updateSQL('products_variations', $fields, ['id' => $item['id_variation']]);
        } else {
            $this->updateSQL('products', $fields, ['id' => $item['id_product']]);
        }
        // }
    }

    public function handleDeleteValue($item)
    {
        sqlStartTransaction();

        sqlQuery('DELETE FROM '.getTableName('inventory_items')."
	      WHERE id='{$item['item_id']}' ");

        sqlFinishTransaction();
    }

    public function handleUpdateValue($item)
    {
        sqlStartTransaction();

        sqlQuery('UPDATE '.getTableName('inventory_items')."
	      SET quantity='".$item['quantity']."'
	      WHERE id='{$item['item_id']}' ");

        // if(!empty($item['code']) || !empty($item['ean']))
        // {
        $fields = ['ean' => $item['ean']];

        // If variations has code, update directly
        if (findModule('products_variations', 'variationCode')) {
            $fields['code'] = $item['code'];
        } elseif (!empty($item['id_variation'])) {
            // If variations does not have code, update product code
            $this->updateSQL('products', ['code' => $item['code']], ['id' => $item['id_product']]);
        }

        if (findModule('products', 'weight')) {
            $fields['weight'] = $this->preparePrice($item['weight']);
        }

        if (!empty($item['id_variation'])) {
            $this->updateSQL('products_variations', $fields, ['id' => $item['id_variation']]);
        } else {
            $this->updateSQL('products', $fields, ['id' => $item['id_product']]);
        }
        // }

        sqlFinishTransaction();
    }
}