<?php

use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
use KupShop\AdminBundle\Admin\ProductsFilter;
use KupShop\AdminBundle\AdminList\BaseList;
use KupShop\AdminBundle\AdminList\FiltersStorage;
use KupShop\AdminBundle\Util\ActivityLog;
use KupShop\AdminBundle\Util\AdminSectionTree;
use KupShop\CatalogBundle\Util\ProductsFilterSpecs;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use KupShop\KupShopBundle\Util\StringUtil;
use Query\Operator as Op;
use Query\QueryBuilder;

require_once $cfg['Path']['shared_version'].'/web/lang/lang.cs.php';

class ProductsMassModificationList extends BaseList
{
    use FiltersStorage;

    // Compatible with product/variation/both
    public const TYPE_PRODUCT_ONLY = 1;
    public const TYPE_VARIATION_ONLY = 2;
    public const TYPE_BOTH = 3;
    public const TYPE_VARIATION_OR_PRODUCT = 7;

    protected $params;
    protected $template = 'list/products.massModification.tpl';

    protected $pageDivide = 50;

    protected $figure;

    protected $editItems;
    protected $changedProducts;

    public $suppliers;
    public $productsRelatedTypes;
    public $VAT_TYPES;

    protected $showExport = false;

    /** @var AdminSectionTree */
    protected $adminSectionTree;

    public function __construct()
    {
        $this->adminSectionTree = ServiceContainer::getService(AdminSectionTree::class);
    }

    public function getEditItems()
    {
        global $cfg;

        if (isset($this->editItems)) {
            return $this->editItems;
        }

        $editItems = [
            'figureCode' => ['name' => 'Kód', 'field' => 'code', 'input' => ['code'], 'size' => '170', 'group' => 'product'],
            'figureTitle' => ['name' => 'Název', 'field' => 'title', 'input' => ['title'], 'size' => '400', 'group' => 'product'],
            'figureEAN' => ['name' => 'EAN', 'field' => 'ean', 'input' => ['ean'], 'size' => '150', 'group' => 'product', 'type' => self::TYPE_VARIATION_OR_PRODUCT],
            'figurePrice' => ['name' => 'Cena bez DPH', 'field' => 'price', 'input' => ['price'], 'raw_field' => 'COALESCE(@.price, p.price)', 'size' => '160', 'group' => 'prices'],
            'figurePriceWithVat' => ['name' => 'Cena s DPH', 'field' => 'price', 'input' => ['priceWithVat'], 'raw_field' => 'COALESCE(@.price, p.price)', 'size' => '160', 'group' => 'prices'],
            'figurePriceFinal' => ['name' => 'Cena po slevě', 'field' => 'price_final', 'raw_field' => 'COALESCE(@.price, p.price) * (1 - p.discount/100)', 'input' => ['priceFinal'], 'size' => '160', 'group' => 'prices'],
            'figureVat' => ['name' => 'DPH', 'field' => 'vat', 'input' => ['vat'], 'size' => '160', 'group' => 'prices'],
            'figureDiscount' => ['name' => 'Sleva', 'field' => 'discount', 'input' => ['discount'], 'size' => '110', 'group' => 'prices'],
            'figurePriceCommon' => ['name' => 'Škrtlá cena', 'field' => 'price_common', 'input' => ['priceCommon'], 'size' => '120', 'group' => 'prices', 'type' => self::TYPE_BOTH],
            'figureGuarantee' => ['name' => 'Záruka', 'field' => 'guarantee', 'input' => ['guarantee'], 'size' => '70', 'group' => 'other'],
            'figureInStore' => ['name' => 'Skladem', 'field' => 'in_store', 'input' => ['inStore'], 'raw_field' => 'COALESCE(@.in_store, p.in_store)', 'size' => '90', 'group' => 'store', 'type' => self::TYPE_VARIATION_OR_PRODUCT],
            'figurePiecesSold' => ['name' => 'Prodaných', 'field' => 'pieces_sold', 'input' => ['piecesSold'], 'size' => '90', 'group' => 'store'],
            'figureCampaign' => ['name' => 'Kampaň', 'field' => 'campaign', 'input' => ['campaign'], 'size' => '460', 'group' => 'market'],
            'figureFigure' => ['name' => 'Zobrazovat', 'field' => 'figure', 'input' => ['figure'], 'size' => '100', 'group' => 'product'],
            'figureDeliveryTime' => ['name' => 'Dostupnost', 'field' => 'delivery_time', 'input' => ['deliveryTime'], 'size' => '400', 'group' => 'store'],
            'figureShowInFeed' => ['name' => 'V porovnávačích', 'field' => 'show_in_feed', 'input' => ['show_in_feed'], 'size' => '100', 'group' => 'market'],
            'figureShowInSearch' => ['name' => 'Ve vyhledávání', 'field' => 'show_in_search', 'input' => ['show_in_search'], 'size' => '100', 'group' => 'product'],
            'figureMaxCPC' => ['name' => 'Cena za proklik', 'field' => 'max_cpc', 'input' => ['max_cpc'], 'size' => '140', 'group' => 'market'],
            'figurePosition' => ['name' => 'Pořadí produktu', 'field' => 'position', 'input' => ['position'], 'size' => '140', 'group' => 'market'],
            'figureCategory' => ['name' => 'Sekce', 'input' => ['sections'], 'size' => '360', 'group' => 'product'],
            'figureProducer' => ['name' => 'Výrobce', 'field' => 'producer', 'input' => ['producer'], 'size' => '160', 'group' => 'product'],
            'figureDelete' => ['name' => 'Smazat položku', 'input' => ['delete'], 'size' => '100', 'group' => 'other'],
            'figureShortDescr' => ['name' => 'Anotace', 'field' => 'short_descr', 'input' => ['shortDescr'], 'size' => '400', 'group' => 'text'],
            'figureLongDescr' => ['name' => 'Popis zboží', 'field' => 'long_descr', 'input' => ['longDescr'], 'size' => '700', 'group' => 'text'],
            'figureParamsDescr' => ['name' => 'Rozšiřující popis', 'field' => 'parameters', 'input' => ['paramsDescr'], 'size' => '700', 'group' => 'text'],
            'figureDateAdded' => ['name' => 'Datum vytvoření', 'field' => 'date_added', 'input' => ['dateAdded'], 'size' => '200', 'group' => 'other'],
            'figurePhotos' => ['name' => 'Obrázky', 'input' => ['photos'], 'size' => '600', 'group' => 'product'],
            'figureMetaTitle' => ['name' => 'SEO Titulek', 'field' => 'meta_title', 'raw_field' => 'COALESCE(p.meta_title, p.title)', 'input' => ['metaTitle'], 'size' => '400', 'group' => 'text'],
            'figureMetaDescription' => ['name' => 'SEO Popisek', 'field' => 'meta_description', 'input' => ['metaDescription'], 'size' => '400', 'group' => 'text'],
            'figurePrintLabels' => ['name' => 'Tisk štítků',  'input' => ['printLabels'], 'size' => '100', 'group' => 'store', 'type' => self::TYPE_VARIATION_OR_PRODUCT],
            'figureDimensions' => ['name' => 'Rozměry (ŠxVxH)', 'field' => 'depth', 'raw_field' => '@.width AS width, @.height AS height, @.depth', 'input' => ['width', 'height', 'depth'], 'size' => '400', 'group' => 'other', 'type' => static::TYPE_BOTH],
        ];

        if (findModule(Modules::OSS_VATS)) {
            $editItems['figureOssVats'] = ['name' => 'OSS kategorie', 'field' => 'id_cn', 'input' => ['id_cn'], 'group' => 'prices', 'type' => self::TYPE_PRODUCT_ONLY];
        }

        if (findModule(\Modules::PARAMETER_GROUPS)) {
            $editItems['figureParamGroups'] = ['name' => 'Skupiny parametrů', 'field' => 'id_parameter_group', 'input' => ['paramGroups'], 'size' => '100', 'group' => 'product'];
        }

        if (findModule(\Modules::PRODUCTS_CHARGES)) {
            $editItems['productsCharges'] = ['name' => 'Příplatky', 'input' => ['products_charges'], 'size' => '400', 'group' => 'prices'];
        }

        if (findModule(Modules::QUANTITY_DISCOUNT)) {
            $editItems['figureQuantityDiscount'] = ['name' => 'Množstevní sleva', 'input' => ['quantity_discount'], 'size' => '300', 'group' => 'prices'];
        }

        if (findModule(Modules::ARTICLES)) {
            $editItems['articlesProduct'] = ['name' => 'Články k produktu', 'input' => ['articles_product'], 'size' => '300', 'group' => 'other'];
        }

        $editVariantKeys = ['figureEAN', 'figurePrice', 'figurePriceWithVat', 'figurePriceFinal', 'figureInStore', 'figureDeliveryTime', 'figureDelete', 'figureFigure', 'figurePrintLabels'];

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_SHOW_MAX)) {
            $editItems['figureShowMax'] = ['name' => 'Zobrazit max. skladem', 'field' => 'in_store_show_max', 'input' => ['in_store_show_max'], 'size' => '100', 'group' => 'store'];
            $editVariantKeys[] = 'figureShowMax';
        }
        if (!empty($cfg['Modules']['products']['note'])) {
            $editItems['figureNote'] = ['name' => 'Poznámka', 'field' => 'note', 'input' => ['note'], 'size' => '300', 'group' => 'text'];
            $editVariantKeys[] = 'figureNote';
        }
        if (!empty($cfg['Modules']['products_variations']['variationCode'])) {
            $editVariantKeys[] = 'figureCode';
        }
        if (findModule('missing_products')) {
            $editItems['figureInStoreMin'] = ['name' => 'Minimální sklad', 'field' => 'in_store_min', 'input' => ['in_store_min'], 'size' => '100', 'group' => 'store'];
            $editVariantKeys[] = 'figureInStoreMin';
        }
        if (findModule('products_suppliers') || findModule('suppliers') || findModule('stock_in')) {
            $editItems['figureCodeOfSuppliers'] = ['name' => 'Produkt dodavatele', 'field' => 'code_of_supplier', 'raw_field' => 'pos.code', 'input' => ['codeOfSuppliers'], 'size' => '600', 'group' => 'store', 'type' => self::TYPE_VARIATION_OR_PRODUCT];
            $editVariantKeys[] = 'figureCodeOfSuppliers';
        }

        if (findModule(Modules::PRODUCTS_SERIAL_NUMBERS)) {
            $editItems['figureSerialNumberRequire'] = ['name' => 'Požadovat seriové číslo', 'field' => 'serial_number_require', 'input' => ['serial_number_require'], 'size' => '100', 'group' => 'store', 'type' => self::TYPE_PRODUCT_ONLY];
        }

        if (findModule('templates')) {
            $editItems['figureTemplates'] = ['name' => 'Šablony', 'input' => ['templates'], 'size' => '400', 'group' => 'other'];
        }

        if (findModule('products_collections')) {
            $editItems['figureCollections'] = ['name' => 'Kolekce', 'input' => ['collections'], 'size' => '400', 'group' => 'other'];
        }

        if (findModule('products_related')) {
            $editItems['figureRelated'] = ['name' => 'Související zboží', 'input' => ['products-related'], 'size' => '360', 'group' => 'other'];
        }

        if (findModule('attachments')) {
            $editItems['figureAttachments'] = ['name' => 'Přílohy ke zboží', 'input' => ['attachments'], 'size' => '700', 'group' => 'other'];
        }

        if (findModule('links')) {
            $editItems['productsLinks'] = ['name' => 'Odkazy ke zboží', 'input' => ['links'], 'size' => '700', 'group' => 'other'];
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_WEIGHT)) {
            $editItems['figureWeight'] = ['name' => 'Hmotnost', 'field' => 'weight', 'input' => ['weight'], 'size' => 160, 'group' => 'other'];
            $editVariantKeys[] = 'figureWeight';
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_RECYCLING_FEE)) {
            $editItems['figureRecyclingFee'] = ['name' => 'Recyklační  příspěvek', 'field' => 'recycling_fee', 'raw_field' => "JSON_VALUE(p.data, '$.recycling_fee')", 'input' => ['data', 'recycling_fee'], 'size' => 160, 'group' => 'other', 'validate' => function (&$value) { $this->preparePrice($value); }];
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_STEP)) {
            $editItems['figureStep'] = ['name' => 'Velikost balení', 'field' => 'step', 'raw_field' => "JSON_VALUE(p.data, '$.step')", 'input' => ['data', 'step'], 'size' => 160, 'group' => 'store', 'validate' => function (&$value) { $this->preparePrice($value); }];
        }

        if (findModule(\Modules::BONUS_PROGRAM)) {
            $editItems['figureBonusPoints'] = ['name' => 'Body', 'field' => 'bonus_points', 'input' => ['bonusPoints'], 'size' => 100, 'group' => 'prices'];
            $editVariantKeys[] = 'figureBonusPoints';
        }

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_BUY)) {
            $editItems['figurePriceBuy'] = ['name' => 'Nákupní cena', 'field' => 'price_buy', 'input' => ['priceBuy'], 'size' => 100, 'group' => 'prices', 'type' => self::TYPE_BOTH];
        }

        if (findModule(Modules::PRODUCTS, Modules::SUB_UNITS)) {
            $editItems['figureProductUnits'] = ['name' => 'Množstevní jednotka', 'field' => 'unit', 'input' => ['unit'], 'size' => '90',
                'group' => 'store'];

            $editItems['figureMeasureUnit'] = ['name' => 'Měrná jednotka', 'field' => 'measure_unit', 'input' => ['measure_unit'], 'size' => '90',
                'group' => 'other'];

            $editItems['figureMeasureQuantity'] = ['name' => 'V balení', 'field' => 'measure_quantity', 'input' => ['measure_quantity'],
                'size' => '90', 'group' => 'other', 'type' => self::TYPE_BOTH];
        }

        if (findModule(Modules::STOCK_IN)) {
            $editItems['figureStockInDate'] = ['name' => 'Datum naskladnění', 'field' => 'date_stock_in', 'input' => ['stockInDate'], 'size' => '200', 'group' => 'store'];
        }

        if (findModule(Modules::PRODUCT_SETS)) {
            $editItems['figureProductSets'] = ['name' => 'Sety', 'input' => ['sets'], 'size' => '400', 'group' => 'other'];
        }

        if (findModule(Modules::PRODUCT_GIFTS)) {
            $editItems['figureProductGifts'] = ['name' => 'Dárky', 'input' => ['gifts'], 'size' => '400', 'group' => 'other'];
        }

        if (findModule(Modules::LABELS, Modules::SUB_PRODUCT_LABELS)) {
            $editItems['figureProductLabels'] = ['name' => 'Štítky', 'input' => ['labels'], 'size' => '400', 'group' => 'market'];
        }

        // Add parameters
        $parameters = $this->getParams();
        foreach ($this->getParameterFields(0) as $id_parameter => $field) {
            $parameter = $parameters[$id_parameter];
            $fieldName = "figureParameter{$id_parameter}";
            $editItems[$fieldName] = ['name' => "[Par] {$parameter['name']}",
                'raw_field' => "value{$id_parameter}",
                'input' => [$fieldName],
                'size' => 300,
                'group' => null,
                'parameter' => $id_parameter,
            ];
            $this->figure[$fieldName] = 1;
        }

        // Translate
        global $txt_str;
        translate('massModification');
        foreach ($editItems as $name => &$item) {
            if (isset($txt_str['productsMassModification'][$name])) {
                $item['name'] = $txt_str['productsMassModification'][$name];
            }
        }
        unset($item);

        $figure = $this->getFigure();
        $figureFeeds = $figure['feeds'] ?? [];
        if ($figureFeeds) {
            foreach ($this->getFeeds() as $feed) {
                if (array_search($feed['id'], $figureFeeds) === false) {
                    continue;
                }

                foreach ($this->getFeedFields() as $feedField => $feedItem) {
                    if (empty($figure["feed{$feedField}"])) {
                        continue;
                    }

                    $figureName = "figureFeed_{$feed['id']}_{$feedField}";
                    $editItems[$figureName] = [
                        'name' => "{$feed['name']} | {$feedItem['title']}",
                        'field' => null,
                        'input' => [$figureName],
                        'size' => $feedItem['size'],
                        'feed' => $feed,
                        'item' => $feedField,
                        'group' => null,
                    ];

                    $this->figure[$figureName] = true;
                }
            }
        }

        $figureStores = $figure['stores'] ?? [];
        if (findModule(Modules::STORES) && $figureStores) {
            foreach ($this->getStores() as $store) {
                if (array_search($store['id'], $figureStores) === false) {
                    continue;
                }

                foreach ($this->getStoresFields() as $storeField => $storeItem) {
                    if (empty($figure["store{$storeField}"])) {
                        continue;
                    }

                    $figureName = "storeItems{$store['id']}_{$storeField}";
                    $editItems[$figureName] = [
                        'name' => "{$store['name']} | {$storeItem['title']}",
                        'input' => [$figureName.'['.$store['id'].']'],
                        'store' => $store,
                        'size' => $storeItem['size'],
                        'item' => $storeField,
                        'group' => null,
                        'field' => null,
                        'type' => self::TYPE_VARIATION_OR_PRODUCT,
                    ];
                    $editVariantKeys[] = $figureName;

                    $this->figure[$figureName] = true;
                }
            }
        }

        // PriceLists
        $figurePriceLists = $figure['pricelists'] ?? [];
        if (findModule(Modules::PRICELISTS) && $figurePriceLists) {
            foreach ($this->getPriceLists() as $priceList) {
                if (array_search($priceList['id'], $figurePriceLists) === false) {
                    continue;
                }

                foreach ($this->getPriceListFields() as $priceListField => $priceListItem) {
                    if (empty($figure["priceList{$priceListField}"])) {
                        continue;
                    }

                    $figureName = "figurePriceList_{$priceList['id']}_{$priceListField}";
                    $editItems[$figureName] = [
                        'name' => "Ceník {$priceList['name']} | {$priceListItem['title']}",
                        'field' => 'pricelist_field',
                        'raw_field' => "COALESCE(prlv.{$priceListItem['sort']}, prlp.{$priceListItem['sort']})",
                        'spec' => function (QueryBuilder $qb) use ($priceList) {
                            if ($qb->getQueryPart('from')[0]['alias'] == 'p') {
                                $qb->joinVariationsOnProducts();
                                $qb->andWhere(\KupShop\PricelistBundle\Query\Product::applyPricelist($priceList['id']));
                            } else {
                                $qb->andWhere(\KupShop\PricelistBundle\Query\Product::applyPricelistOnVariation($priceList['id']));
                            }
                        },
                        'input' => [$figureName],
                        'size' => $priceListItem['size'],
                        'priceList' => $priceList,
                        'item' => $priceListField,
                        'group' => null,
                        'type' => $priceListItem['type'],
                    ];

                    $this->figure[$figureName] = true;
                }
            }
        }

        // Add fields editable in variations
        foreach ($editItems as $name => $item) {
            if (isset($item['type']) && $item['type'] & self::TYPE_VARIATION_ONLY) {
                $editVariantKeys[] = $name;
            }
        }

        $this->editItems = [
            'editItems' => $editItems,
            'editVariantKeys' => $editVariantKeys,
        ];

        return $this->editItems;
    }

    public function getFigureItems()
    {
        $figureItems = [
            'product' => [],
            'prices' => [],
            'text' => [],
            'store' => [],
            'market' => [],
            'other' => [],
        ];

        foreach ($this->editItems['editItems'] as $key => $item) {
            $item['key_name'] = $key;
            if (isset($item['group'])) {
                $figureItems[$item['group']][] = $item;
            } elseif (array_key_exists('group', $item)) {
                continue;
            } else {
                $figureItems['other'][] = $item;
            }
        }

        return ['items' => $figureItems];
    }

    public function getFigure()
    {
        if (isset($this->figure)) {
            return $this->figure;
        }

        $figure = [];
        $prep = getVal('prepare');
        if (!empty($prep)) {
            $figure = getVal('figure');
        } elseif (getVal('submitSettings')) {
            $figure = getVal('figure');
            setcookie('mass_modification_figure', serialize($figure), time() + 3600 * 24 * 30);
        } else {
            if (!empty($_COOKIE['mass_modification_figure'])) {
                $figure = unserialize($_COOKIE['mass_modification_figure']);
            }
        }

        $this->figure = $figure;

        return $this->figure;
    }

    public function &getFind()
    {
        if (isset($this->find)) {
            return $this->find;
        }

        $find = getVal('find');

        if (!empty($find)) {
            $_SESSION['mass_modification_find'] = $_POST['find'];
        } elseif (!empty($_SESSION['mass_modification_find'])) {
            $find = $_SESSION['mass_modification_find'];
        }

        $this->find = $find;

        return $this->find;
    }

    public function &getFilter()
    {
        if (isset($this->filter)) {
            return $this->filter;
        }

        $filter = getVal('filter');

        $type = 'productsMassModification';
        if (!empty($filter)) {
            $filter = ProductsFilter::cleanFilter($filter);
            $_SESSION[$type.'_filter'] = $filter;
        } elseif (!empty($_SESSION[$type.'_filter'])) {
            $filter = $_SESSION[$type.'_filter'];
        }

        $this->filter = $filter;

        return $this->filter;
    }

    public function generateTableDef()
    {
        $editItems = $this->getEditItems()['editItems'];
        $figure = $this->getFigure();

        $fields = [
            'Produkt' => [
                'field' => 'title',
                'size' => 200,
            ],
        ];
        foreach ($editItems as $name => $item) {
            if (empty($figure[$name])) {
                continue;
            }

            /*if (empty($item['field']))
               continue;*/

            $fields[$item['name']] = [
                'field' => getVal('field', $item),
                'size' => getVal('size', $item, 100),
                'parameter' => getVal('parameter', $item),
                'itemName' => $name,
            ];

            $raw_field = getVal('raw_field', $item);
            if ($raw_field) {
                $fields[$item['name']]['raw_field'] = $raw_field;
            }
        }

        $this->tableDef = [
            'id' => 'id',
            'fields' => $fields,
        ];
    }

    public function get_vars()
    {
        global $cfg;

        $qb = sqlQueryBuilder();

        $this->generateTableDef();

        $vars = parent::get_vars();

        // DPH
        $SQL = $qb->select('id', 'descr', 'vat')
                  ->from(getTableName('vats'))
                  ->orderBy('vat', 'ASC')
                  ->execute();

        $this->VAT_TYPES = [];

        foreach ($SQL as $row) {
            $this->VAT_TYPES[] = [
                'id' => $row['id'],
                'descr' => $row['descr'],
            ];
        }

        $CHARGES = [0 => '-bez příplatku-'];
        if (findModule(Modules::PRODUCTS_CHARGES)) {
            // Příplatky
            $qb->resetQueryParts();
            $SQL = $qb->select('*')
                ->addSelect('IF(admin_title != "", admin_title, title) as title')
                ->from('charges')
                ->andWhere("type = 'product'")
                ->orderBy('id', 'ASC')
                ->execute();
            $CHARGES += sqlFetchAll($SQL, ['id' => 'title']);
        }

        // Dodavatele
        $this->suppliers = [0 => 'Vyberte dodavatele'];
        if (findModule('products_suppliers') || findModule('suppliers') || findModule('stock_in')) {
            $qb->resetQueryParts();
            $SQL = $qb->select('id', 'name')
                     ->from(getTableName('suppliers'))
                     ->orderBy('name', 'ASC')
                     ->execute();

            foreach ($SQL as $supplier) {
                $this->suppliers[$supplier['id']] = $supplier['name'];
            }
        }

        if (findModule(Modules::PRODUCTS_RELATED, Modules::SUB_TYPES)) {
            $this->productsRelatedTypes = sqlFetchAll(sqlQueryBuilder()->select('*')->from('products_related_types')->execute(), ['id' => 'name']);
        }

        // Product flags
        $flags = ['' => '['.translate('ignore').']'];
        $flags = array_merge($flags, array_map(function ($flag) {
            return $flag['plural'];
        }, $cfg['Products']['Flags']));

        $producers = [
            '0' => '['.translate('allProd').']',
            '-1' => '['.translate('noProd').']',
        ] + $this->getProducers();

        // nabidka poctu polozek na stranku
        $figureDivide = [
            10 => 10,
            50 => 50,
            100 => 100,
            200 => 200,
            500 => 500,
            1000 => 1000,
            2000 => 2000,
            5000 => 5000,
            10000 => 10000,
        ];

        $find = $this->getFind();
        $filter = $this->getFilter();

        return array_merge($vars, $this->getEditItems(), [
            'CHARGES' => $CHARGES,
            'suppliers' => $this->suppliers,
            'productsRelatedTypes' => $this->productsRelatedTypes,
            'params' => $this->getParams(),
            'VAT_TYPES' => $this->VAT_TYPES,
            'flags' => $flags,
            'producers' => $producers,
            'figureDivide' => $figureDivide,
            'find' => $find,
            'filter' => $filter,
            'figure' => $this->getFigure(),
            'figureParameters' => $this->getFigureParameters(),
            'categories' => $this->adminSectionTree->getCategories(),
            'figureItems' => $this->getFigureItems(),
        ]);
    }

    public function getSQLOrdering(&$var, &$orderParam)
    {
        parent::getSQLOrdering($var, $orderParam);

        $figure = $this->getFigure();

        $editItems = $this->getEditItems();

        $column = $orderParam['sort'] ?? null;

        $fields = $this->getTableDef()['fields'];
        if ($column !== null) {
            $item = $fields[$column];
            $itemKey = $item['itemName'] ?? null;
            $field = $editItems['editItems'][$itemKey] ?? null;
        }

        if (!empty($figure['Variants']) && !empty($field)) {
            if (array_search($itemKey, $editItems['editVariantKeys'])) {
                if (!empty($field['raw_field'])) {
                    $var['orderField'] = str_replace('@', 'pv', $field['raw_field']);
                } else {
                    $var['orderField'] = 'pv.'.$var['orderField'];
                }
            }
        } elseif (!empty($field['raw_field'])) {
            $var['orderField'] = str_replace('@', 'p', $field['raw_field']);
        }
    }

    public function applyOrder(Query\QueryBuilder $qb, &$vars)
    {
        $qb = parent::applyOrder($qb, $vars);

        $figure = $this->getFigure();
        if (!empty($figure['Variants'])) {
            $qb->addOrderBy('pvcv.sort');
        }

        return $qb;
    }

    public function getQuery()
    {
        global $cfg;

        $qb = $this->getBaseQuery();
        $qb->from(getTableName('products'), 'p')
            ->where('p.id > 0');

        // -------------------------------------------------

        $editItems = $this->getEditItems()['editItems'];

        $figure = $this->getFigure();
        foreach ($editItems as $key => $item) {
            if (!empty($item['type']) && $item['type'] == self::TYPE_VARIATION_ONLY) {
                continue;
            }

            if (isset($figure[$key]) && isset($item['field'])) {
                if (empty($item['raw_field'])) {
                    $qb->addSelect('p.'.$item['field']);
                } else {
                    $qb->addSelect(str_replace('@', 'p', $item['raw_field']).' as '.$item['field']);
                }
            }

            if (isset($figure[$key]) && isset($item['spec'])) {
                $qb->addSelect($item['spec']);
            }
        }

        $divide = getVal('divide', $figure, $this->pageDivide);

        if (!empty($figure['Variants'])) {
            $qb->addSelect('pv.id as pv_id')
                ->joinVariationsOnProducts()
                ->leftJoin('pv', getTableName('products_variations_combination'), 'pvc', 'pvc.id_variation=pv.id')
                ->leftJoin('pvc', getTableName('products_variations_choices_values'), 'pvcv', 'pvc.id_value=pvcv.id');
        }

        if (!empty($figure['figureCodeOfSuppliers'])) {
            $qb->joinVariationsOnProducts()
                ->joinProductsOfSuppliers();
        }

        $find = $this->getFind();
        $filter = $this->getFilter();
        // ###########
        if (!empty($find['Code'])) {
            if (strpos($find['Code'], ',') === false) {
                // Search 'fulltext'
                $qb->andWhere('(p.code LIKE :code) OR (p.ean LIKE :code)')->setParameter('code', '%'.$find['Code'].'%');
                if (!empty($figure['Variants'])) {
                    if (findModule('products_variations', 'variationCode')) {
                        $qb->orWhere('(pv.code LIKE :code)')->setParameter('code', '%'.$find['Code'].'%');
                    }
                    $qb->orWhere('(pv.ean LIKE :code)')->setParameter('code', '%'.$find['Code'].'%');
                }
            } else {
                // Search in set of codes/EANs
                // ////////////////////////////
                $codes = array_map(function ($x) {
                    return trim($x);
                }, explode(',', $find['Code']));
                $eans = array_filter($codes, function ($x) {return is_numeric($x); });
                $qb->andWhere(Op::orX(Op::inStringArray($codes, 'p.code'), Op::inStringArray($eans, 'p.ean')));
                if (!empty($figure['Variants'])) {
                    if (findModule('products_variations', 'variationCode')) {
                        $qb->orWhere(Op::inStringArray($codes, 'pv.code'));
                    }
                    $qb->orWhere(Op::inStringArray($eans, 'pv.ean'));
                }
                // ////////////////////////////
            }
        }
        if (!empty($find['CodeEmpty'])) {
            $query = 'code IS NULL';
            if (!empty($figure['Variants']) && findModule('products_variations', 'variationCode')) {
                $qb->andWhere('(p.'.$query.' OR pv.'.$query.')');
            } else {
                $qb->andWhere('p.'.$query.'');
            }
        }
        // ###########
        if (!empty($find['Title'])) {
            $search = get_search_query($find['Title'], [['field' => 'p.title', 'match' => 'both']]);
            $qb->andWhere($search['where']);

            foreach ($search['data'] as $title => $data) {
                $qb->setParameter($title, $data);
            }
        }
        // ###########
        if (!empty($find['Note'])) {
            if (!empty($figure['Variants'])) {
                $qb->andWhere('(p.note LIKE :find_note OR pv.note LIKE :find_note)')->setParameter('find_note', '%'.$find['Note'].'%');
            } else {
                $qb->andWhere('p.note LIKE :find_note')->setParameter('find_note', '%'.$find['Note'].'%');
            }
        }
        // ###########
        if (!empty($find['NoteEmpty'])) {
            $query = 'note IS NULL';
            if (!empty($figure['Variants'])) {
                $qb->andWhere('(p.'.$query.' AND pv.'.$query.')');
            } else {
                $qb->andWhere('p.'.$query.'');
            }
        }
        // ###########
        if (!empty($find['Description'])) {
            $qb->andWhere('(p.short_descr LIKE :find_description) OR (p.long_descr LIKE :find_description) OR (p.parameters LIKE :find_description)')
            ->setParameter('find_description', '%'.$find['Description'].'%');
        }

        if (!empty($find['ParametersEmpty']) && !empty($filter['categories'])) {
            $qb->leftJoin('pf_ps', getTableName('parameters_sections'), 'pas', 'pf_ps.id_section=pas.id_section')
                ->leftJoin('pas', getTableName('parameters_products'), 'pap', 'p.id=pap.id_product AND pas.id_parameter=pap.id_parameter')
                ->andWhere('pap.id_product IS NULL');
        }

        if (!empty($figure['Variants'])) {
            $qb->groupBy('p.id, pv.id');
        } else {
            $qb->groupBy('p.id');
        }

        if (getVal('visibleProductsWithHiddenProducer')) {
            $qb->joinProducersOnProducts()
            ->andWhere('p.figure = "Y" AND pr.active="N"');
        }

        $filterSpecs = null;
        $productsFilterSpecs = ServiceContainer::getService(ProductsFilterSpecs::class);
        $filter = $this->getFilter();
        if ($filter) {
            $filterSpecs = $productsFilterSpecs->getSpecs($filter);
            if ($filterSpecs) {
                $qb->andWhere($filterSpecs);
            }
        }

        // Add parameters
        $parameters = $this->getParams();
        foreach ($this->getFigureParameters() as $id_parameter) {
            $parameter = $parameters[$id_parameter];
            $alias = "pp{$id_parameter}";
            $type = $parameter->getValueTypeField();
            $field = "{$alias}.{$type}";
            $qb->leftJoin('p', 'parameters_products', $alias, "{$alias}.id_product = p.id AND {$alias}.id_parameter = {$id_parameter}")
                ->addSelect("{$field} value{$id_parameter}");

            if (!empty($find['ParametersEmpty'])) {
                $qb->andWhere(Op::isNull($field));
            }
        }

        return [
            'qb' => $qb,
            'divide' => $divide,
        ];
    }

    /**
     * @return Parameter[]
     */
    public function getParams()
    {
        // Parameters
        if (is_null($this->params)) {
            $params = [];

            if (findModule('products_parameters')) {
                $params = Parameter::get();
            }

            $this->params = $params;
        }

        return $this->params;
    }

    public function getFigureParameters()
    {
        if (isset($this->figureParameters)) {
            return array_slice($this->figureParameters, 0, 50);
        }

        $figure = $this->getFigure();

        if (isset($figure['ParametersInCategory'])) {
            $figureParameters = [];

            if (!empty($this->getFilter()['categories'])) {
                if (!empty($this->getFilter()['subcategories']) && $this->getFilter()['subcategories'] == 'Y') {
                    $categories = $this->getFilter()['categories'];
                    foreach ($categories as $category) {
                        $descendantCategories = getDescendantCategories($category);
                        if (!empty($descendantCategories)) {
                            $categories = array_unique(array_merge($categories, $descendantCategories));
                        }
                    }
                } else {
                    $categories = $this->getFilter()['categories'];
                }

                $params = sqlQueryBuilder()->select('DISTINCT ps.id_parameter')
                   ->from('parameters_sections', 'ps')
                   ->join('ps', 'parameters', 'p', 'ps.id_parameter=p.id')
                   ->where(Op::inIntArray($categories, 'ps.id_section'))
                   ->orderBy('weight')->addOrderBy('p.position')->execute();

                $figureParameters = sqlFetchAll($params, ['id_parameter' => 'id_parameter']);
            }

            $this->figureParameters = $figureParameters;
        } else {
            $figureParameters = getVal('params', $figure, []);
            $params = $this->getParams();
            $figureParameters = array_filter($figureParameters, function ($p) use ($params) { return array_key_exists($p, $params); });
            $this->figureParameters = $figureParameters;
        }

        return array_slice($this->figureParameters, 0, 50);
    }

    public function handle()
    {
        $this->activateFilters();

        $qb = sqlQueryBuilder();

        // TODO: Zbavit se tohodle. Je to nebezpecny, jakákoli lokální proměnná dokáže rozesrat (přepsat) vstupní data a je průser.
        // TODO: Ideálně nahradit všechny $title[$thisID] za $data['title'][$thisID] a bude pokoj
        extract($_REQUEST, EXTR_SKIP | EXTR_REFS);

        $editItems = $this->getEditItems();
        $editVariantKeys = $editItems['editVariantKeys'];
        $editItems = $editItems['editItems'];

        if (isset($submitSaveAndNext)) {
            $submitSave = true;
            $_GET['page'] = getVal('page', null, 0) + 1;
        }

        // --------------------------------------
        if (isset($submitSave)) {
            $count = 0;
            $ID = getVal('ID', null, []);
            $edit = getVal('edit');

            foreach ($ID as $thisID) {
                $IDdb = intval($thisID);
                $query = '';
                $table = getTableName('products');

                $sql_values = [];
                // Filter out not selected values
                foreach ($editItems as $key => $item) {
                    if (empty($edit[$key][$thisID])) {
                        foreach ($item['input'] as $input) {
                            if ($input == 'data') {
                                continue;
                            }
                            unset($_REQUEST[$input][$thisID]);
                        }
                    }
                }

                // Check for variants
                if (substr($thisID, 0, 1) == 'v') {
                    $IDdb = intval(substr($thisID, 1));
                    $table = getTableName('products_variations');

                    // Filter out values not for variants
                    foreach ($editItems as $key => $item) {
                        if (array_search($key, $editVariantKeys) === false) {
                            foreach ($item['input'] as $input) {
                                unset($_REQUEST[$input][$thisID]);
                            }
                        }
                    }
                }
                if (isset($delete[$thisID])) {
                    if (!empty($delete[$thisID])) {
                        $query = "DELETE FROM {$table} WHERE id=".$IDdb.' ';
                        $SQL = sqlQuery($query, '@');
                        if (!$SQL) {
                            echo '<br>Chyba při editaci záznamu!';
                        }
                        $count += sqlAffectedRows($SQL);
                        continue;
                    }
                }
                if (isset($code[$thisID])) {
                    if (empty($code[$thisID])) {
                        $query .= ', code=NULL ';
                    } else {
                        $query .= ", code='".sqlFormatInput(trim($code[$thisID]))."'";
                    }
                }
                if (isset($title[$thisID])) {
                    if (empty($title[$thisID])) {
                        $query .= ', title=NULL ';
                    } else {
                        $query .= ", title='".sqlFormatInput(trim($title[$thisID]))."'";
                    }
                }
                if (isset($note[$thisID])) {
                    if (empty($note[$thisID])) {
                        $query .= ', note=NULL ';
                    } else {
                        $query .= ", note='".sqlFormatInput(trim($note[$thisID]))."'";
                    }
                }
                if (isset($ean[$thisID])) {
                    if (empty($ean[$thisID])) {
                        $query .= ', ean=NULL ';
                    } else {
                        $query .= ", ean='".sqlFormatInput(trim($ean[$thisID]))."'";
                    }
                }
                if (isset($id_cn[$thisID])) {
                    if (empty($id_cn[$thisID])) {
                        $query .= ', id_cn=NULL ';
                    } else {
                        $query .= ', id_cn='.sqlFormatInput($id_cn[$thisID]);
                    }
                }
                if (isset($priceFinal[$thisID]) && (!isset($discount[$thisID])) && isset($priceFinalPrice[$thisID]) && (isset($vat[$thisID]) || isset($priceFinalVat[$thisID]))) {
                    $priceDiscounted = toDecimal($priceFinal[$thisID]);
                    $value = isset($vat[$thisID]) ? $vat[$thisID] : $priceFinalVat[$thisID];
                    $vatValue = getVat($value);
                    $priceVat = toDecimal($priceFinalPrice[$thisID])->addVat($vatValue);
                    $finalDiscount = 0;
                    if (!$priceVat->isZero()) {
                        $finalDiscount = DecimalConstants::hundred()->mul(DecimalConstants::one()->sub($priceDiscounted->div($priceVat)));
                        // 100*(1 - (discount_price/price));
                    }

                    $query .= ', discount=:dicsount';
                    $sql_values['dicsount'] = $finalDiscount;
                } elseif (isset($priceWithVat[$thisID]) && (isset($vat[$thisID]) || isset($priceWithVatVat[$thisID]))) {
                    $priceWithVat[$thisID] = str_replace(',', '.', $priceWithVat[$thisID]);
                    if (!is_numeric($priceWithVat[$thisID])) {
                        $priceWithVat[$thisID] = 'NULL';
                    } else {
                        $priceWithVat[$thisID] = doubleval($priceWithVat[$thisID]);
                        $value = isset($vat[$thisID]) ? $vat[$thisID] : $priceWithVatVat[$thisID];
                        $vatValue = getVat($value);

                        $priceWithVat[$thisID] = $priceWithVat[$thisID] / (1 + ($vatValue / 100));
                        $priceWithVat[$thisID] = number_format($priceWithVat[$thisID], 3, '.', '');
                        $priceWithVat[$thisID] = "'{$priceWithVat[$thisID]}'";
                    }

                    $query .= ", price={$priceWithVat[$thisID]}";
                } elseif (isset($price[$thisID])) {
                    $price[$thisID] = str_replace(',', '.', $price[$thisID]);
                    if (!is_numeric($price[$thisID])) {
                        $price[$thisID] = 'NULL';
                    } else {
                        $price[$thisID] = doubleval($price[$thisID]);
                        $price[$thisID] = "'{$price[$thisID]}'";
                    }
                    $query .= ", price={$price[$thisID]}";
                }
                if (isset($priceCommon[$thisID])) {
                    $priceCommon[$thisID] = doubleval(str_replace(',', '.', $priceCommon[$thisID]));
                    if (!is_numeric($priceCommon[$thisID])) {
                        $priceCommon[$thisID] = 0;
                    }
                    $query .= ", price_common='".$priceCommon[$thisID]."'";
                }
                if (isset($discount[$thisID])) {
                    $discount[$thisID] = doubleval(str_replace(',', '.', $discount[$thisID]));
                    if (!is_numeric($discount[$thisID])) {
                        $discount[$thisID] = 0;
                    }
                    $query .= ", discount='".$discount[$thisID]."'";
                }
                if (isset($vat[$thisID])) {
                    $vat[$thisID] = intval($vat[$thisID]);
                    $query .= ', vat='.$vat[$thisID].'';
                }
                if (isset($guarantee[$thisID])) {
                    $guarantee[$thisID] = intval($guarantee[$thisID]);
                    $query .= ', guarantee='.$guarantee[$thisID].'';
                }
                if (isset($inStore[$thisID])) {
                    if (findRight('PROD_STOCK')) {
                        if (!is_numeric($inStore[$thisID]) || $inStore[$thisID] < -1) {
                            $inStore[$thisID] = -1;
                        } else {
                            $inStore[$thisID] = intval($inStore[$thisID]);
                        }
                        $query .= ', in_store='.$inStore[$thisID].'';
                    }
                }
                if (isset($piecesSold[$thisID])) {
                    if (!is_numeric($piecesSold[$thisID])) {
                        $piecesSold[$thisID] = 0;
                    } else {
                        $piecesSold[$thisID] = intval($piecesSold[$thisID]);
                    }
                    $query .= ', pieces_sold='.$piecesSold[$thisID].'';
                }
                if (!empty($_REQUEST['edit']['figureCampaign'][$thisID])) {
                    if (isset($campaign)) {
                        $flags = getVal($thisID, $campaign, []);
                    } else {
                        $flags = [];
                    }

                    $query .= ", campaign='".join(',', array_keys($flags))."'";
                }
                if (!empty($_REQUEST['edit']['figureFigure'][$thisID])) {
                    // $figure[$thisID] = (!empty($figure[$thisID])) ? "Y" : "N";
                    $query .= ", figure='".$figures[$thisID]."'";
                }

                if (!empty($_REQUEST['edit']['figureCharge'][$thisID])) {
                    $charge_id[$thisID] = intval($charge_id[$thisID]);
                    $charge_included[$thisID] = (!empty($charge_included[$thisID])) ? 'Y' : 'N';
                    if (empty($charge_id[$thisID])) {
                        $charge_included[$thisID] = 'N';
                    }

                    $query .= (!empty($charge_id[$thisID])) ? ', charge_id='.intval($charge_id[$thisID]).'' : ', charge_id=NULL ';
                    $query .= ", charge_included='".$charge_included[$thisID]."'";
                }
                if (isset($deliveryTime[$thisID])) {
                    $deliveryTime[$thisID] = intval($deliveryTime[$thisID]);
                    if ($deliveryTime[$thisID] == '1') {
                        $deliveryTime[$thisID] = intval($deliveryTimeDays[$thisID]);
                    }
                    $query .= ", delivery_time='".$deliveryTime[$thisID]."'";
                }
                if (isset($in_store_show_max[$thisID])) {
                    if (empty($in_store_show_max[$thisID])) {
                        $query .= ', in_store_show_max=NULL ';
                    } else {
                        $query .= ", in_store_show_max='".intval($in_store_show_max[$thisID])."'";
                    }
                }
                if (isset($in_store_min[$thisID])) {
                    if (empty($in_store_min[$thisID])) {
                        $query .= ', in_store_min=NULL ';
                    } else {
                        $query .= ", in_store_min='".intval($in_store_min[$thisID])."'";
                    }
                }
                if (isset($max_cpc[$thisID])) {
                    if (empty($max_cpc[$thisID])) {
                        $query .= ', max_cpc=0 ';
                    } else {
                        $query .= ", max_cpc='".floatval($max_cpc[$thisID])."'";
                    }
                }
                if (!empty($_REQUEST['edit']['figureShowInFeed'][$thisID])) {
                    if (empty($show_in_feed[$thisID])) {
                        $query .= ", show_in_feed='N' ";
                    } else {
                        $query .= ", show_in_feed='Y'";
                    }
                }

                if (!empty($_REQUEST['edit']['figureSerialNumberRequire'][$thisID])) {
                    if (empty($serial_number_require[$thisID])) {
                        $query .= ", serial_number_require='N' ";
                    } else {
                        $query .= ", serial_number_require='Y'";
                    }
                }

                if (!empty($_REQUEST['edit']['figureShowInSearch'][$thisID])) {
                    if (empty($show_in_search[$thisID])) {
                        $query .= ", show_in_search='N' ";
                    } else {
                        $query .= ", show_in_search='Y'";
                    }
                }
                if (isset($position[$thisID])) {
                    if (empty($position[$thisID]) && $position[$thisID] != '0') {
                        $query .= ', position=NULL ';
                    } else {
                        $query .= ", position='".intval($position[$thisID])."'";
                    }
                }
                if (isset($producer[$thisID])) {
                    $producer[$thisID] = intval($producer[$thisID]);
                    $query .= ', producer='.$producer[$thisID].'';
                }

                if (isset($shortDescr[$thisID])) {
                    $query .= ', short_descr=:short_descr';
                    $sql_values['short_descr'] = trim($shortDescr[$thisID]);
                }

                if (isset($longDescr[$thisID])) {
                    $query .= ', long_descr=:long_descr';
                    $sql_values['long_descr'] = trim($longDescr[$thisID]);
                }

                if (isset($paramsDescr[$thisID])) {
                    $query .= ', parameters=:parameters';
                    $sql_values['parameters'] = trim($paramsDescr[$thisID]);
                }

                if (isset($metaTitle[$thisID])) {
                    $query .= ', meta_title=:meta_title';

                    $_title = $this->getQueryBuilder()->select('title')->from('products')->where('id =:id')->setParameter('id', $thisID)->execute()->fetchColumn();
                    if (($_title && $_title == $metaTitle[$thisID]) || empty($metaTitle[$thisID]) || (isset($title[$thisID]) && $metaTitle[$thisID] == $title[$thisID])) {
                        $sql_values['meta_title'] = null;
                    } else {
                        $sql_values['meta_title'] = trim($metaTitle[$thisID]);
                    }
                }

                if (isset($metaDescription[$thisID])) {
                    $query .= ', meta_description=:meta_description';
                    $sql_values['meta_description'] = trim($metaDescription[$thisID]);
                }

                if (isset($metaKeywords[$thisID])) {
                    $query .= ', meta_keywords=:meta_keywords';
                    $sql_values['meta_keywords'] = trim($metaKeywords[$thisID]);
                }

                if (isset($weight[$thisID])) {
                    $query .= ', weight=:weight';
                    $sql_values['weight'] = $this->preparePrice($weight[$thisID]);
                }

                // dimensions
                foreach (['width', 'height', 'depth'] as $tmpDimension) {
                    if (isset(${$tmpDimension}[$thisID])) {
                        $query .= ", {$tmpDimension}=:{$tmpDimension}";
                        $sql_values[$tmpDimension] = $this->preparePrice(${$tmpDimension}[$thisID]);
                    }
                }

                if (isset($priceBuy[$thisID])) {
                    $query .= ', price_buy=:priceBuy';
                    $sql_values['priceBuy'] = $this->preparePrice($priceBuy[$thisID]);
                }

                if (isset($bonusPoints[$thisID])) {
                    $query .= ', bonus_points=:bonus_points';
                    $sql_values['bonus_points'] = $this->preparePrice($bonusPoints[$thisID]);
                }

                if (isset($margin[$thisID])) {
                    $query .= ', price='.(isset($sql_values['priceBuy']) ? ':priceBuy' : 'price_buy').' * :margin';
                    $sql_values['margin'] = 1. + $this->preparePrice($margin[$thisID]) / 100;
                }

                if (isset($product_unit[$thisID])) {
                    $query .= ', unit = :product_unit';
                    $sql_values['product_unit'] = $product_unit[$thisID];
                }

                if (isset($measure_quantity[$thisID])) {
                    $query .= ', measure_quantity = :measure_quantity';
                    $sql_values['measure_quantity'] = $measure_quantity[$thisID];
                }

                if (isset($measure_unit[$thisID])) {
                    $query .= ', measure_unit = :measure_unit';
                    $sql_values['measure_unit'] = $measure_unit[$thisID];
                }

                if (isset(getVal('paramGroups')[$thisID])) {
                    $query .= ', id_parameter_group = :idParameterGroup ';
                    $sql_values['idParameterGroup'] = getVal('paramGroups')[$thisID];
                }

                if (isset($dateAdded[$thisID])) {
                    $d = DateTime::createFromFormat('d.m.Y H:i:s', $dateAdded[$thisID]);
                    if ($d) {
                        $query .= ', date_added= :dateAdded ';
                        $sql_values['dateAdded'] = $d->format('Y-m-d H:i:s');
                    }
                }

                if (isset($stockInDate[$thisID])) {
                    $dStockIn = DateTime::createFromFormat('d.m.Y H:i:s', $stockInDate[$thisID]);
                    if ($dStockIn) {
                        $query .= ', date_stock_in= :stockInDate ';
                        $sql_values['stockInDate'] = $dStockIn->format('Y-m-d H:i:s');
                    }
                }

                if (isset($data[$thisID])) {
                    if ($errors = $this->validateCustomData($data[$thisID])) {
                        $this->addError("Chyba při editaci produktu {$IDdb}: ".implode('; ', $errors));
                    } else {
                        $query .= ', data= JSON_MERGE_PATCH(COALESCE(data, "{}"), :data) ';
                        $sql_values['data'] = json_encode($data[$thisID]);
                    }
                }

                $this->updateProduct($thisID);

                if ($query) {
                    $query = substr($query, 2);
                    $query = "UPDATE {$table} SET {$query} WHERE id={$IDdb} ";

                    try {
                        $SQL = sqlQuery($query, $sql_values);
                        $count += sqlAffectedRows($SQL);
                    } catch (Doctrine\DBAL\DBALException $e) {
                        $error = $this->translateException($e);
                        $this->addError("Chyba při editaci produktu {$IDdb}: {$error}");
                    }
                }

                // Code of suppliers
                if (isset($codeOfSuppliers[$thisID])) {
                    foreach ($codeOfSuppliers[$thisID] as $IDpos => $value) {
                        $qb->resetQueryParts();
                        $value['code'] = trim($value['code']);
                        $value['ean'] = trim($value['ean']);
                        $value['in_store'] = trim($value['in_store']);

                        if (!empty($value['delete'])) {
                            $qb->delete('products_of_suppliers')->andWhere(Op::equals(['id' => $IDpos]));
                        } elseif ($IDpos > 0) {
                            $qb->update('products_of_suppliers');
                            $qb->directValues([
                                'code' => !empty($value['code']) ? $value['code'] : null,
                                'ean' => !empty($value['ean']) ? $value['ean'] : null,
                                'in_store' => !empty($value['in_store']) ? $value['in_store'] : 0,
                            ])->andWhere(Op::equals(['id' => $IDpos]));
                        } elseif (!empty($value['supplier'])) {
                            $prod_allow_empty_supplier_code = ((Settings::getDefault()['prod_allow_empty_supplier_code'] ?? null) == 'Y');
                            if (!$prod_allow_empty_supplier_code
                                && empty($value['code']) && empty($value['ean'])
                                && empty($value['in_store'])) {
                                continue;
                            }
                            if (str_starts_with($thisID, 'v')) {
                                $idp = sqlQueryBuilder()
                                    ->select('id_product')
                                    ->from('products_variations')
                                    ->where(Op::equals(['id' => $IDdb]))
                                    ->execute()->fetchColumn();

                                $qb->insert('products_of_suppliers')
                                    ->directValues(
                                        [
                                            'id_variation' => $IDdb,
                                            'id_product' => $idp,
                                            'id_supplier' => $value['supplier'],
                                            'code' => !empty($value['code']) ? $value['code'] : null,
                                            'in_store' => !empty($value['in_store']) ? $value['in_store'] : 0,
                                            'ean' => !empty($value['ean']) ? $value['ean'] : null,
                                        ]
                                    );
                            } else {
                                $qb->insert('products_of_suppliers')
                                    ->directValues([
                                        'id_variation' => null,
                                        'id_product' => $IDdb,
                                        'id_supplier' => $value['supplier'],
                                        'code' => !empty($value['code']) ? $value['code'] : null,
                                        'in_store' => !empty($value['in_store']) ? $value['in_store'] : 0,
                                        'ean' => !empty($value['ean']) ? $value['ean'] : null,
                                    ]);
                            }
                        } else {
                            continue;
                        }

                        try {
                            $SQL = $qb->execute();

                            $count += $SQL;

                            // $count += $qb->execute()->rowCount();
                        } catch (Doctrine\DBAL\DBALException $e) {
                            $error = $this->translateException($e);
                            $this->addError("Chyba při editaci kódu dodavatele u produktu {$IDdb}: {$error}");
                        }
                    }
                }

                // Parameters configurations
                if (findModule(Modules::PRODUCTS_PARAMETERS, Modules::SUB_CONFIGURATIONS) && isset($parameters_configurations[$thisID])) {
                    foreach ($parameters_configurations[$thisID] as $paramID => $data) {
                        if (($data['enabled'] ?? false) == 'Y') {
                            ParameterConfiguration::insertValue($paramID, $thisID);
                        } else {
                            (new ParameterConfiguration())->deleteValue($paramID, $thisID);
                        }
                    }
                }

                // Parameters
                if (isset($parameters[$thisID])) {
                    $params = $this->getParams();
                    $params = Parameter::getValues($params, $thisID);

                    foreach ($parameters[$thisID] as $IDpar => $values) {
                        foreach ($values as $index => $value) {
                            $value['value'] = trim($value['value']);
                            // jestli neni prazdnou hodnotu
                            if (isset($value['value']) && $value['value'] != '') {
                                /*if ($parameter['type'] == "float")
                               {
                                  $value = str_replace(',', '.', $value);
                                  $value = preg_replace("/([,\\.]+|\\.0|,0)$/", '', $value);
                               }*/
                                // $query = queryCreate(
                                $query_values = [
                                    'id_product' => $IDdb,
                                    'id_parameter' => $IDpar,
                                    'value' => $value['value'],
                                    // 'unit' => $parametersUnit[$IDpar][$index],
                                    // 'weight' => $parametersWeight[$IDpar][$index]
                                ];

                                if (findModule(Modules::PRODUCTS_PARAMETERS, Modules::SUB_CONFIGURATIONS)) {
                                    $query_values['configuration_price'] = $value['price'];
                                }

                                if (isset($value['unit'])) {
                                    $query_values['unit'] = $value['unit'];
                                }

                                if ($params[$IDpar]['value_type'] == 'float') {
                                    $this->preparePrice($query_values['value']);
                                }

                                if (!empty($value['delete'])) {
                                    $query_values['delete'] = 'on';
                                }

                                $count += $params[$IDpar]->setValue($query_values, $index);
                            }
                        }
                    }
                }

                // Sections
                if (isset($sections[$thisID])) {
                    foreach ($sections[$thisID] as $originalSectionId => $value) {
                        $id_section = $value['id_section'];

                        if ($id_section <= 0) {
                            continue;
                        }

                        if (!empty($value['delete'])) {
                            if ($id_section > 0) {
                                $count += $this->deleteSQL('products_in_sections', ['id_section' => $id_section, 'id_product' => $IDdb]);
                            }
                            continue;
                        }

                        try {
                            if ($originalSectionId > 0) {
                                $count += $this->updateSQL('products_in_sections', ['id_section' => $id_section], ['id_product' => $IDdb, 'id_section' => $originalSectionId]);
                            } else {
                                $count += $this->insertSQL('products_in_sections', ['id_product' => $IDdb, 'id_section' => $id_section]);
                            }
                        } catch (UniqueConstraintViolationException $e) {
                        }
                    }
                }
                //                if ($thisID > 0) {
                //                    die(var_dump($sections[$thisID]));
                //                }
                // Templates
                if (isset($templates[$thisID])) {
                    foreach ($templates[$thisID] as $index => $value) {
                        $id_template = $value['id_template'];

                        if ($id_template <= 0) {
                            continue;
                        }

                        if (!empty($value['delete'])) {
                            if ($id_template > 0) {
                                $count += $this->deleteSQL('templates_products', ['id_template' => $id_template, 'id_product' => $IDdb]);
                            }
                            continue;
                        }

                        try {
                            $count += $this->insertSQL('templates_products', ['id_product' => $IDdb, 'id_template' => $id_template]);
                        } catch (Doctrine\DBAL\Exception\UniqueConstraintViolationException $e) {
                        }
                    }
                }

                // Collections
                if (isset($collections[$thisID])) {
                    $this->deleteSQL('products_collections', ['id_product' => $IDdb]);
                    foreach ($collections[$thisID] as $index => $value) {
                        $id_product_related = $value['id_product_related'];
                        if ($id_product_related <= 0) {
                            continue;
                        }
                        if (empty($value['delete'])) {
                            try {
                                $count += $this->insertSQL('products_collections', ['id_product' => $IDdb, 'id_product_related' => $id_product_related]);
                            } catch (Doctrine\DBAL\Exception\UniqueConstraintViolationException $e) {
                            }
                        }
                    }
                }

                // Product sets
                if (isset($sets[$thisID])) {
                    $this->deleteSQL('products_sets', ['id_product' => $IDdb]);
                    foreach ($sets[$thisID] as $index => $value) {
                        $id_product_set = $value['id_product_set'];
                        if ($id_product_set <= 0) {
                            continue;
                        }
                        if (empty($value['delete'])) {
                            try {
                                $count += $this->insertSQL('products_sets', ['id_product' => $IDdb, 'id_product_set' => $id_product_set]);
                            } catch (Doctrine\DBAL\Exception\UniqueConstraintViolationException $e) {
                            }
                        }
                    }
                }

                // Product gifts
                if (isset($gifts[$thisID])) {
                    $this->deleteSQL('products_gifts', ['id_product' => $IDdb]);
                    foreach ($gifts[$thisID] as $index => $value) {
                        $id_product_gift = $value['id_product_gift'];
                        if ($id_product_gift <= 0) {
                            continue;
                        }
                        if (empty($value['delete'])) {
                            try {
                                $count += $this->insertSQL('products_gifts', ['id_product' => $IDdb, 'id_product_gift' => $id_product_gift]);
                            } catch (Doctrine\DBAL\Exception\UniqueConstraintViolationException $e) {
                            }
                        }
                    }
                }

                if (isset($labels[$thisID])) {
                    $this->deleteSQL('product_labels_relation', ['id_product' => $IDdb]);

                    $insertQb = sqlQueryBuilder()->insert('product_labels_relation');
                    $skipInsert = true;
                    foreach ($labels[$thisID] as $value) {
                        if (empty($value['delete']) && ($value['id_label'] > 0)) {
                            $insertQb->multiDirectValues(['id_product' => $IDdb, 'id_label' => $value['id_label']]);
                            $skipInsert = false;
                        }
                    }
                    if (!$skipInsert) {
                        $insertQb->execute();
                    }
                }

                // Photos
                if (isset($edit['figurePhotos'][$thisID])) {
                    sqlQuery('DELETE FROM '.getTableName('photos_products_relation').' WHERE id_product=:id_product AND id_variation IS NULL', ['id_product' => $thisID]);
                    if (isset($photos)) {
                        // filter duplicated images
                        $photos[$thisID] = array_unique($photos[$thisID], SORT_REGULAR);
                        foreach ($photos[$thisID] as $index => $photo) {
                            if ($photo['id_photo'] <= 0 || empty($photo['id_photo'])) {
                                continue;
                            }

                            if (empty($photo['show_in_lead'])) {
                                $showInLead = ($index == 0) ? 'Y' : 'N';
                            } else {
                                $showInLead = $photo['show_in_lead'];
                            }

                            try {
                                $count += $this->insertSQL('photos_products_relation',
                                    ['id_product' => $thisID, 'id_photo' => $photo['id_photo'], 'position' => $index, 'show_in_lead' => $showInLead]);
                            } catch (Doctrine\DBAL\Exception\UniqueConstraintViolationException $e) {
                            }
                        }
                        Photos::checkLeadPhoto('photos_products_relation', 'id_product', $thisID);
                    }
                }
                // Products Related
                if (isset($products_related[$thisID])) {
                    foreach ($products_related[$thisID] as $index => $value) {
                        if ($value['id_rel_product'] <= 0) {
                            continue;
                        }

                        $value['id_top_product'] = $IDdb;
                        $row = $this->filterFields($value, ['id_top_product', 'id_rel_product', 'type']);

                        if (!empty($value['delete'])) {
                            $count += $this->deleteSQL('products_related', $row);
                            continue;
                        }

                        try {
                            $count += $this->insertSQL('products_related', $row);
                        } catch (Doctrine\DBAL\Exception\UniqueConstraintViolationException $e) {
                        }
                    }
                }
                // product articles
                if (isset($articles_product[$thisID])) {
                    $this->deleteSQL('products_in_articles', ['id_product' => $thisID]);
                    if (isset($articles_product)) {
                        foreach ($articles_product[$thisID] as $article) {
                            if (empty($article['id'])) {
                                continue;
                            }
                            if (empty($article['delete'])) {
                                try {
                                    $count += $this->insertSQL(
                                        'products_in_articles',
                                        ['id_article' => $article['id'], 'id_product' => $thisID]
                                    );
                                } catch (UniqueConstraintViolationException $e) {
                                }
                            }
                        }
                    }
                }

                // Products attachments
                if (isset($edit['figureAttachments'][$thisID])) {
                    $this->deleteSQL('attachments', ['id_product' => $thisID]);
                    if (isset($attachments)) {
                        foreach ($attachments[$thisID] as $att) {
                            if (empty($att['link'])) {
                                continue;
                            }

                            if (isset($att['title']) && empty($att['title']) && !empty($att['link'])) {
                                $att['title'] = basename($att['link']);
                            }

                            if (empty($att['delete'])) {
                                $insertData = [
                                    'link' => $att['link'],
                                    'title' => $att['title'],
                                    'id_product' => $thisID,
                                ];

                                if (findModule('attachments', 'attachments_types')) {
                                    $insertData['type'] = $att['type'] ?? null;
                                }

                                try {
                                    $count += $this->insertSQL('attachments', $insertData);
                                } catch (UniqueConstraintViolationException $e) {
                                }
                            }
                        }
                    }
                }

                // product charges
                if (isset($edit['productsCharges'][$thisID])) {
                    $this->deleteSQL('products_charges', ['id_product' => $thisID]);

                    if (isset($products_charges)) {
                        foreach ($products_charges[$thisID] as $charge) {
                            if (empty($charge['id'])) {
                                continue;
                            }
                            if (empty($charge['delete'])) {
                                try {
                                    $count += $this->insertSQL(
                                        'products_charges',
                                        ['id_charge' => $charge['id'], 'id_product' => $thisID]
                                    );
                                } catch (UniqueConstraintViolationException $e) {
                                }
                            }
                        }
                    }
                }

                // Product links
                if (isset($edit['productsLinks'][$thisID])) {
                    $this->deleteSQL('links', ['id_product' => $thisID]);

                    if (isset($links)) {
                        foreach ($links[$thisID] as $att) {
                            if (empty($att['link'])) {
                                continue;
                            }

                            if (isset($att['title']) && empty($att['title']) && !empty($att['link'])) {
                                $att['title'] = basename($att['link']);
                            }
                            if (empty($att['delete'])) {
                                try {
                                    $count += $this->insertSQL('links',
                                        [
                                            'link' => $att['link'],
                                            'title' => $att['title'],
                                            'id_product' => $thisID,
                                            'type' => isset($att['type']) ? $att['type'] : null,
                                        ]
                                    );
                                } catch (UniqueConstraintViolationException $e) {
                                }
                            }
                        }
                    }
                }

                if (isset($quantity_discount[$thisID])) {
                    $this->deleteSQL('products_quantity_discounts', ['id_product' => $thisID]);
                    foreach ($quantity_discount[$thisID] as $index => $discount) {
                        if (empty($discount['pieces']) && empty($discount['discount'])) {
                            continue;
                        }

                        try {
                            if (empty($discount['delete'])) {
                                $count += $this->insertSQL('products_quantity_discounts', ['id_product' => $thisID, 'pieces' => $discount['pieces'], 'discount' => $discount['discount']]);
                            }
                        } catch (UniqueConstraintViolationException $e) {
                        }
                    }
                }

                if (isset($feed[$thisID])) {
                    foreach ($feed[$thisID] as $id_feed => $values) {
                        foreach ($values as &$value) {
                            if (strlen($value) == 0) {
                                $value = null;
                            }
                        }
                        $where = ['id_feed' => $id_feed, 'id_product' => $thisID];
                        try {
                            $count += $this->insertSQL('feed_products', array_merge($where, $values));
                        } catch (UniqueConstraintViolationException $e) {
                            $count += $this->updateSQL('feed_products', $values, $where);
                        }
                    }
                    if (findModule(Modules::FEED_GIFTS)) {
                        sqlQuery('DELETE FROM feed_products WHERE id_section IS NULL AND active IS NULL AND title IS NULL AND cpc IS NULL AND gift_text IS NULL');
                    } else {
                        sqlQuery('DELETE FROM feed_products WHERE id_section IS NULL AND active IS NULL AND title IS NULL AND cpc IS NULL');
                    }
                }

                if (isset($store[$thisID])) {
                    foreach ($store[$thisID] as $id_store => $values) {
                        $values = array_map(function ($val) {
                            return strlen($val) ? $val : null;
                        }, $values);

                        $where = ['id_store' => $id_store, 'id_product' => $thisID, 'id_variation' => null];

                        if (StringUtil::startsWith($thisID, 'v')) {
                            $id_variation = trim($thisID, 'v');
                            $id_product = $this->selectSQL('products_variations', ['id' => $id_variation], ['id_product'])->fetchColumn();
                            $where['id_product'] = $id_product;
                            $where['id_variation'] = $id_variation;
                        }

                        if ($this->selectSQL('stores_items', $where)->fetch()) {
                            $count += $this->updateSQL('stores_items', $values, $where);
                        } else {
                            $count += $this->insertSQL('stores_items', array_merge($where, $values));
                        }
                    }
                }

                // handle PriceList
                if (isset($priceList[$thisID])) {
                    foreach ($priceList[$thisID] as $id_price_list => $values) {
                        $id_variation = null;
                        $id_product = $thisID;
                        if (StringUtil::startsWith($thisID, 'v')) {
                            $id_variation = trim($thisID, 'v');
                            $id_product = $this->selectSQL('products_variations', ['id' => $id_variation], ['id_product'])->fetchColumn();
                        }

                        $_vat = $this->selectSQL('products', ['id' => $id_product], ['vat'])->fetchColumn();
                        $_vat = getVat($_vat);

                        if (isset($values['price_with_vat'])) {
                            $values['price'] = empty($values['price_with_vat']) ? '' : toDecimal($values['price_with_vat'])->removeVat($_vat);
                        }

                        $update = [];

                        if (isset($values['price'])) {
                            $update['price'] = empty($values['price']) ? null : $this->preparePrice($values['price']);
                        }

                        if (isset($values['discount'])) {
                            $update['discount'] = empty($values['discount']) ? null : $values['discount'];
                        }

                        if ($update) {
                            $exists = $this->selectSQL('pricelists_products', [
                                'id_pricelist' => $id_price_list,
                                'id_product' => $id_product,
                                'id_variation' => $id_variation,
                            ])->fetch();
                            if ($exists) {
                                $this->updateSQL(
                                    'pricelists_products',
                                    $update,
                                    [
                                        'id_pricelist' => $id_price_list,
                                        'id_product' => $id_product,
                                        'id_variation' => $id_variation,
                                    ]
                                );
                            } else {
                                $this->insertSQL(
                                    'pricelists_products',
                                    array_merge([
                                        'id_pricelist' => $id_price_list,
                                        'id_product' => $id_product,
                                        'id_variation' => $id_variation,
                                    ], $update)
                                );
                            }
                        }
                    }
                }
            }

            $this->addChangedProducts($count);

            $figure = $this->getFigure();
            if (!empty($figure['Variants'])) {
                Variations::recalcInStore();
            }

            if ($this->changedProducts > 0) {
                addActivityLog(ActivityLog::SEVERITY_NOTICE, ActivityLog::TYPE_CHANGE, sprintf(translate('modified_log'), $this->changedProducts));
            }
            $this->returnOK(sprintf(translate('modified'), $this->changedProducts));
        }
    }

    private $configurationsCache = [];

    public function productHasParametersConfigurations($productId, $parameterId)
    {
        if (!$this->configurationsCache) {
            $figureParameters = $this->getFigureParameters();
            $configurations = sqlQueryBuilder()->select('id_product, id_parameter')
               ->from('parameters_configurations')
               ->andWhere(Op::inIntArray(array_values($figureParameters), 'id_parameter'))
               ->execute();

            foreach ($configurations as $configuration) {
                $this->configurationsCache[$configuration['id_product']][$configuration['id_parameter']] = true;
            }
        }

        if (isset($this->configurationsCache[$productId][$parameterId])) {
            return true;
        }

        return false;
    }

    public function printEditField($name, $row, $IDpr)
    {
        $item = getVal($name, $this->getEditItems()['editItems']); ?>
       <td data-type="enabler" data-column="<?php echo $item['input'][0]; ?>">
        <?php
           $type = getVal('type', $item);
        $product = new Product(intval($IDpr));
        if ($IDpr !== 0) {
            if ($type == self::TYPE_VARIATION_OR_PRODUCT) {
                if (substr($IDpr, 0, 1) != 'v' && $product->hasVariations()) {
                    echo '</td><td></td>';

                    return false;
                }
            } elseif ($type == self::TYPE_VARIATION_ONLY && substr($IDpr, 0, 1) != 'v') {
                echo '</td><td></td>';

                return false;
            }
        }

        if ($IDpr === 0) {
            if (!empty($item['field'])) {
                $row[$item['field']] = '';
            } ?>
              <input class="enabler massEnabler" type="checkbox" tabindex="-1" />
           <?php
        } else { ?>
            <?php if ((!empty($item['read_only']) && $item['read_only'] == false) || empty($item['read_only'])) { ?>
              <input class="enabler" type="checkbox" tabindex="-1" name="edit[<?php echo $name; ?>][<?php echo $IDpr; ?>]" value='1'/>
            <?php } ?>
           <?php } ?>
       </td>
    <td data-type="field" data-column="<?php echo $item['input'][0]; ?>">
        <?php

        if (isset($item['parameter'])) {
            static $parameterFields = null;
            static $parameterFieldsId = null;

            if ($parameterFieldsId !== $IDpr) {
                $parameterFields = $this->getParameterFields($IDpr);
            }

            // Parameters
            $rows = $parameterFields[$item['parameter']];
            $parameters = [];
            foreach ($rows as $key => $row) {
                $IDp = $row['id_parameter'];
                $params = $this->getParams();
                if (!array_key_exists($IDp, $params)) {
                    continue;
                }

                $param = clone $params[$IDp];

                if ($IDpr !== 0 && isset($row["value_{$params[$row['id_parameter']]->value_type}"])) {
                    $value = $row["value_{$params[$row['id_parameter']]->value_type}"];
                } else {
                    $value = $row['value'];
                }

                $param['value_id'] = empty($row['id']) ? 0 : $row['id'];

                $param['value'] = $value;
                $param['row'] = $row;

                if ($param['value_type'] == 'list') {
                    $values = $params[$row['id_parameter']]->fetchListValues();
                    $param_values = [];

                    foreach ($values as $para) {
                        $param_values[$para['id']] = $para['value'];
                    }

                    $values = ['' => '&nbsp;'] + $param_values;

                    $param['values'] = $values;
                }

                $parameters[] = $param;
            }

            return $parameters;
        }

        switch ($name) {
            case 'figurePriceWithVat':
                if (is_numeric($row['price'])) {
                    $price = calcPrice($row['price'], getVat($row['vat'], 0))->printFloatValue(2);
                } else {
                    $price = '';
                }
                // cena bez DPH
                echo '<input type="text" class="input" name="priceWithVat['.$IDpr.']" size="15" value="'.$price.'" onkeypress="checkInputData(\'float\')" onchange="update(this,\''.$IDpr.'\')" />';
                echo '<input type="hidden" name="priceWithVatVat['.$IDpr.']" value="'.getVal('vat', $row).'" />';
                break;

            case 'figureVat':
                // DPH list
                echo '<select class="selecter" name="vat['.$IDpr.']" onchange="update(this,\''.$IDpr.'\')">';
                for ($x = 0; $x < count($this->VAT_TYPES); $x++) {
                    echo '<option value="'.$this->VAT_TYPES[$x]['id'].'"'.checkSelect($this->VAT_TYPES[$x]['id'], $row['vat']).'>';
                    echo htmlspecialchars($this->VAT_TYPES[$x]['descr']);
                    echo "</option>\n";
                }
                echo '</select>';
                break;

            case 'figureCampaign':
                global $cfg;
                echo '&nbsp;';
                $campaigns = explodeFlags($row['campaign']);
                // kampan
                foreach ($cfg['Products']['Flags'] as $flag => $name) {
                    $hidden = $cfg['Products']['Flags'][$flag]['hidden'] ?? false;
                    $noneditable = $cfg['Products']['Flags'][$flag]['noneditable'] ?? false;
                    if (!$hidden && !$noneditable) {
                        ?>
                   <div style='float: left; margin-right:10px;'>
                      <label title="<?php echo $name['plural']; ?>">
                         <input style="margin-right:5px;" type="checkbox" name="campaign[<?php echo $IDpr; ?>][<?php echo $flag; ?>]" value="ON" <?php if (isset($campaigns[$flag])) {
                             echo 'checked="checked"';
                         } ?> onchange="update(this,'<?php echo $IDpr; ?>')"/><?php echo $name['plural']; ?>
                      </label>
                   </div>
                <?php
                    } elseif ($noneditable) {
                        ?>
                       <div style='float: left; margin-right:10px;'>
                          <label title="<?php echo $name['plural']; ?>">
                             <input style="margin-right:5px;" type="checkbox" disabled="disabled" <?php if (isset($campaigns[$flag])) {
                                 echo 'checked="checked"';
                             } ?> /><?php echo $name['plural']; ?>
                              <?php if (isset($campaigns[$flag])) {
                                  ?>
                                 <input type="hidden"  name="campaign[<?php echo $IDpr; ?>][<?php echo $flag; ?>]" value="ON">
                              <?php
                              } ?>
                          </label>
                       </div>
                    <?php
                    } elseif (isSuperuser()) {
                        ?>
                       <div style='float: left; margin-right:10px;'>
                          <label title="<?php echo $name['plural']; ?>">
                             <input style="margin-right:5px;" type="checkbox" disabled="disabled" <?php if (isset($campaigns[$flag])) {
                                 echo 'checked="checked"';
                             } ?> /><?php echo $name['plural']; ?> <span class="glyphicon glyphicon-flash" style="color:#AAB2BD;"></span>
                              <?php if (isset($campaigns[$flag])) {
                                  ?>
                                 <input type="hidden"  name="campaign[<?php echo $IDpr; ?>][<?php echo $flag; ?>]" value="ON">
                              <?php
                              } ?>
                          </label>
                       </div>
                    <?php
                    } elseif (isset($campaigns[$flag])) {
                        ?>
                       <input type="hidden"  name="campaign[<?php echo $IDpr; ?>][<?php echo $flag; ?>]" value="ON">
                    <?php
                    }
                }
                break;

            case 'figureDeliveryTime':
                $deliveryTimeDays = '';

                if ($IDpr === 0) {
                    $row['delivery_time'] = -99;
                }

                if ($row['delivery_time'] > 0) {
                    $deliveryTimeDays = $row['delivery_time'];
                    $row['delivery_time'] = 1;
                }
                $input = "<input type='text' maxlength='2' size='2' name='deliveryTimeDays[{$IDpr}]' value='{$deliveryTimeDays}' class='form-control input-sm small' onchange='update(this,\"{$IDpr}\")'>";

                $isDeliveryTimeConfig = isset($GLOBALS['cfg']['Products']['DeliveryTimeConfig']);

                foreach ($GLOBALS['cfg']['Products'][$isDeliveryTimeConfig ? 'DeliveryTimeConfig' : 'DeliveryTime'] as $key => $value) { ?>
                   <div class="pull-left" style="margin-right:10px;">
                      <input type="radio" id="dT<?php echo $IDpr; ?>_<?php echo $key; ?>" value="<?php echo $key; ?>" name="deliveryTime[<?php echo $IDpr; ?>]" onchange="update(this,'<?php echo $IDpr; ?>')" <?php echo checkRadio($key, $row['delivery_time']); ?>>
                      <label for="dT<?php echo $IDpr; ?>_<?php echo $key; ?>">
                          <?php echo sprintf($isDeliveryTimeConfig ? $value['name'] : $value, $input); ?>
                      </label>
                   </div>
                <?php }
                break;

            case 'figureCodeOfSuppliers':
                if (substr($IDpr, 0, 1) == 'v') {
                    $where = " id_variation='".intval(substr($IDpr, 1))."'";
                } else {
                    $where = "id_product='{$IDpr}' AND id_variation IS NULL";
                }

                $SQL = sqlQuery('SELECT id, id_supplier, code, ean, in_store
                     FROM '.getTableName('products_of_suppliers')."
                     WHERE {$where}
                     ORDER BY id_supplier ASC ");

                if (sqlNumRows($SQL) <= 0) {
                    $SQL = [['id' => '0', 'name' => 'Vyberte dodavatele']];
                }

                return $SQL;
                break;

            case 'figureDelete':
                // Smazat
                echo '<div align="center">';
                echo '<input type="checkbox" id="delete'.$IDpr.'" name="delete['.$IDpr.']" onchange="update(this,\''.$IDpr.'\')" value="Y" />';
                echo '<label for="delete'.$IDpr.'">&nbsp;Ano</label>';
                echo '</div>';
                break;

            case 'figureShortDescr':
                echo '<input type="text" class="input" name="shortDescr['.$IDpr.']" size="45" value="'.htmlspecialchars($row['short_descr']).'" onchange="update(this,\''.$IDpr.'\')" />';
                break;

            case 'figureCategory':
                $sections = $IDpr === 0 ? false : sqlFetchAll(sqlQuery('SELECT ps.id_section, s.name
                  FROM '.getTableName('products_in_sections').' ps
                  LEFT JOIN '.getTableName('sections').' s ON s.id=ps.id_section
                  WHERE id_product=:id_product', ['id_product' => $IDpr]));
                if (!$sections) {
                    $sections = [['id_section' => '', 'position' => 0, 'name' => '-- Vyhledejte sekci --']];
                }

                return $sections;

            case 'figureTemplates':
                $SQL = sqlQuery('SELECT CONCAT_WS(" - ", tc.name, t.name) name, t.id
                     FROM templates_products tp
                     LEFT JOIN templates t ON t.id=tp.id_template
                     LEFT JOIN templates_categories tc ON tc.id=t.id_category
                     WHERE tp.id_product=:id_product ORDER BY t.id', ['id_product' => $IDpr]);
                if (sqlNumRows($SQL) <= 0) {
                    $SQL = [['id' => '', 'name' => '-- Vyhledejte šablonu --']];
                }

                return $SQL;

            case 'figureQuantityDiscount':
                $SQL = sqlQuery('SELECT id, pieces, discount FROM products_quantity_discounts
                    WHERE id_product = :id_product ORDER BY pieces ASC', ['id_product' => $IDpr]);
                if (sqlNumRows($SQL) <= 0) {
                    $SQL = [['id' => '', 'pieces' => '', 'discount' => '']];
                }

                return $SQL;

            case 'figureCollections':
                $product->fetchCollections(true);
                if (!$product->collections['own']) {
                    $product->collections['own'] = [['id_product_related' => '', 'position' => 0]];
                }

                return $product->collections;

            case 'figureProductSets':
                $product->fetchSets(true);

                if (empty($product->sets)) {
                    $product->sets['own'] = [['id_product_set' => '', 'position' => 0]];
                }

                return $product->sets;

            case 'figureProductGifts':
                $product->fetchGifts(true);

                if (empty($product->gifts)) {
                    $product->gifts['own'] = [['id_product_gift' => '', 'position' => 0]];
                }

                return $product->gifts;

            case 'figureProductLabels':
                $product->fetchLabels('all');

                if (empty($product->labels)) {
                    $product->labels['own'] = [['id_label' => '', 'position' => 0]];
                }

                return $product->labels;

            case 'figurePhotos':
                $photos = [];
                if (!empty($product->id)) {
                    $qb = sqlQueryBuilder()
                        ->select('id, descr, source, image_2 AS image_big, ppr.position, ppr.show_in_lead')
                        ->from('photos', 'p')
                        ->from('photos_products_relation', 'ppr')
                        ->where('ppr.id_photo = p.id AND ppr.id_variation IS NULL')
                        ->andWhere(\Query\Operator::equals([
                            'ppr.id_product' => $product->id,
                            'ppr.active' => 'Y',
                        ]))
                        ->orderBy('ppr.position');

                    foreach ($qb->execute() as $row) {
                        $photo = getImage($row['id'], $row['image_big'], $row['source'], 4, $row['descr']);
                        $photo['position'] = $row['position'];
                        $photo['show_in_lead'] = $row['show_in_lead'];
                        $photos[] = $photo;
                    }
                }

                return $photos;

            case 'figureRelated':
                $product->fetchProductsRelated();
                if (!$product->products_related) {
                    $product->products_related = [['id_rel_products' => '', 'position' => 0]];
                }

                return $product->products_related;

            case 'figureAttachments':
                $product->fetchAttachments();

                return $product->attachments;

            case 'articlesProduct':
                $articles = [];
                if (!empty($product->id)) {
                    $articles = sqlQueryBuilder()->select('a.*')
                        ->from('articles', 'a')
                        ->join('a', 'products_in_articles', 'pia', 'a.id = pia.id_article')
                        ->where(\Query\Operator::equals(['pia.id_product' => $product->id]))
                        ->execute()->fetchAll();
                }
                if (sizeof($articles) <= 0) {
                    $articles = [['id' => '', 'title' => 'Vyhledejte článek']];
                }

                return $articles;

            case 'productsCharges':
                $charges = [];
                if (!empty($product->id)) {
                    $charges = sqlQueryBuilder()->select('c.*')
                        ->addSelect('IF(c.admin_title != "", c.admin_title, c.title) as title')
                        ->from('charges', 'c')
                        ->join('c', 'products_charges', 'pc', 'c.id = pc.id_charge')
                        ->where(\Query\Operator::equals(['pc.id_product' => $product->id]))
                        ->execute()->fetchAll();
                }

                return $charges;

            case 'productsLinks':
                $product->fetchLinks();

                return $product->links;

            default:
                return true;
        }

        echo '</td>';

        return false;
    }

    public function getParameterFields($IDpr)
    {
        // Handle parameters
        $figureParameters = $this->getFigureParameters();

        if (empty($figureParameters)) {
            return [];
        }

        $paramValues = [];

        if ($IDpr) {
            $values = sqlQueryBuilder()->select('*')->from('parameters_products')
                ->where(Op::equals(['id_product' => $IDpr]))
                ->andWhere(Op::inIntArray(array_values($figureParameters), 'id_parameter'))->execute();

            foreach ($values as $value) {
                $paramValues[$value['id_parameter']][] = $value;
            }
        }

        foreach ($figureParameters as $IDp) {
            if (!isset($paramValues[$IDp])) {
                $paramValues[$IDp] = [['id_parameter' => $IDp, 'unit' => null, 'value' => null]];
            }
        }

        ksort($paramValues, SORT_NUMERIC);

        return $paramValues;
    }

    public function getVariations($id_variation)
    {
        $qb = $this->getBaseQuery();
        $qb->from('products_variations', 'pv')
            ->leftJoin('pv', 'products', 'p', 'p.id=pv.id_product');

        $figure = $this->getFigure();
        if (!empty($figure['figureCodeOfSuppliers'])) {
            $qb->leftJoin('p', 'products_of_suppliers', 'pos', 'pos.id_product=p.id');
        }

        $qb->select('pv.id, pv.title, p.vat, p.discount, pv.price as pv_price');

        $editItems = $this->getEditItems();

        foreach ($editItems['editVariantKeys'] as $key) {
            if (isset($figure[$key]) && isset($editItems['editItems'][$key]['field'])) {
                $item = $editItems['editItems'][$key];
                if (empty($item['raw_field'])) {
                    $qb->addSelect('pv.'.$item['field']);
                } else {
                    $qb->addSelect(str_replace('@', 'pv', $item['raw_field']).' as '.$item['field']);
                }
            }

            if (isset($figure[$key]) && isset($editItems['editItems'][$key]['spec'])) {
                $qb->addSelect($editItems['editItems'][$key]['spec']);
            }
        }

        $SQLv = $qb->andWhere(Op::equals(['pv.id' => $id_variation]))
            ->groupBy('pv.id')
            ->execute();

        return $SQLv;
    }

    /**
     * @return QueryBuilder|mixed
     */
    protected function getBaseQuery()
    {
        $qb = sqlQueryBuilder()
            ->select('p.id', 'p.title', 'p.vat', 'p.discount', 'p.price');

        return $qb;
    }

    public function getProductUnits()
    {
        static $units;

        if ($units) {
            return $units;
        }

        if (findModule(Modules::PRODUCTS, Modules::SUB_UNITS)) {
            $units = sqlFetchAll(sqlQueryBuilder()->select('*')->from('products_units')->execute(), ['id' => 'short_name']);
        } else {
            $units = [];
        }

        return $units;
    }

    /**
     * @return array
     */
    public function getProducers()
    {
        if (isset($this->producers)) {
            return $this->producers;
        }

        // Producers
        $SQL = sqlQuery('SELECT id, name
                              FROM producers
                              ORDER BY name ASC');
        $producers = [];
        foreach ($SQL as $producer) {
            $producers[$producer['id']] = $producer['name'];
        }
        $this->producers = $producers;

        return $this->producers;
    }

    public function getProducerName($id)
    {
        if (!$id) {
            return 'Vyberte výrobce';
        }

        return $this->getProducers()[$id];
    }

    public function getOssCategories()
    {
        if (isset($this->ossCategories)) {
            return $this->ossCategories;
        }

        $this->ossCategories = sqlFetchAll(sqlQueryBuilder()->select('ovc.cnkey, ovc.description')
            ->from('kupshop_shared.oss_vats_categories', 'ovc')
            ->innerJoin('ovc', 'products', 'p', 'ovc.cnkey = p.id_cn')
            ->execute(), 'cnkey');

        return $this->ossCategories;
    }

    public function getOssCategoryName($id)
    {
        if (!$id) {
            return '';
        }

        return $this->getOssCategories()[$id]['description'] ?? '';
    }

    public function getSectionInfo($id)
    {
        if (!$id) {
            return ['path' => 'Vyberte sekci', 'name' => ''];
        }

        if (isset($this->sections[$id])) {
            return $this->sections[$id];
        }

        $category = $this->adminSectionTree->getCategoryById($id);

        $path = [$category['title']];

        $parent = $category;
        while ($parent = $parent['parent']) {
            $path[] = $parent['title'];
        }

        $this->sections[$id] = [
            'id_section' => $id,
            'path' => join(' / ', array_reverse($path)),
            'name' => $category['title'],
        ];

        return $this->sections[$id];
    }

    protected function addChangedProducts($count)
    {
        $this->changedProducts += $count;
    }

    protected function getData()
    {
        return $_REQUEST;
    }

    protected function validateCustomData(&$data)
    {
        $errors = [];

        $customDataFields = $this->getCustomDataFields();
        if (!$customDataFields) {
            $errors[] = 'There are no CustomDataFields defined!';
        }

        foreach ($customDataFields as $key => $item) {
            if (!array_key_exists($key, $data)) {
                continue;
            }
            if (($validate = ($item['validate'] ?? null)) && is_callable($validate)) {
                $value = $data[$key];
                try {
                    $validate($value);
                } catch (Exception $exception) {
                    $value = null;
                    $errors[] = $exception->getMessage();
                }
                $data[$key] = $value;
            }
        }

        return $errors;
    }

    protected function getCustomDataFields()
    {
        if (isset($this->customDataFields)) {
            return $this->customDataFields;
        }

        $customDataFields = [];

        $editItems = $this->getEditItems()['editItems'];
        foreach ($editItems as $editItem) {
            $input0 = ($editItem['input'][0] ?? null);
            if (($input0 == 'data') && ($input1 = ($editItem['input'][1] ?? null))) {
                $customDataFields[$input1] = $editItem;
            }
        }

        return $this->customDataFields = $customDataFields;
    }

    protected function updateProduct($thisID)
    {
        $figure = $this->getFigure();
        if (!empty($figure['Variants']) && !empty($figure['figureDeliveryTime'])) {
            $prod = new Product($thisID);
            $prod->updateDeliveryTime();
        }
    }

    // PriceLists
    public function getPriceLists()
    {
        if (isset($this->priceLists)) {
            return $this->priceLists;
        }

        return $this->priceLists = sqlFetchAll($this->selectSQL('pricelists', []), 'id');
    }

    public function getPriceListFields()
    {
        return [
            'discount' => [
                'title' => 'Sleva',
                'size' => 120,
                'type' => self::TYPE_BOTH,
                'sort' => 'discount',
            ],
            'price' => [
                'title' => 'Cena bez DPH',
                'size' => 120,
                'type' => self::TYPE_BOTH,
                'sort' => 'price',
            ],
            'price_with_vat' => [
                'title' => 'Cena s DPH',
                'size' => 120,
                'type' => self::TYPE_BOTH,
                'sort' => 'price',
            ],
        ];
    }

    private $priceListData = [];

    public function getPriceListData($id_price_list, $id_product)
    {
        $id_variation = null;
        if (StringUtil::startsWith($id_product, 'v')) {
            $id_variation = trim($id_product, 'v');
            $id_product = $this->selectSQL('products_variations', ['id' => $id_variation], ['id_product'])->fetchColumn();
        }

        if (isset($this->priceListData[$id_price_list][$id_product])) {
            if ($id_variation) {
                return $this->priceListData[$id_price_list][$id_product]['variations'][$id_variation] ?? [];
            }

            return $this->priceListData[$id_price_list][$id_product] ?? [];
        }

        $data = sqlQueryBuilder()->select('pp.*, v.vat')
         ->from('pricelists_products', 'pp')
         ->leftJoin('pp', 'products', 'p', 'p.id = pp.id_product')
         ->leftJoin('p', 'vats', 'v', 'v.id = p.vat')
         ->where(\Query\Operator::equals([
             'pp.id_pricelist' => $id_price_list,
             'pp.id_product' => $id_product,
         ]))
         ->execute()->fetchAll();

        foreach ($data as $item) {
            $price = toDecimal($item['price']);
            if ($item['id_variation']) {
                $this->priceListData[$id_price_list][$item['id_product']]['variations'][$item['id_variation']] = [
                    'price' => $item['price'],
                    'price_with_vat' => $item['price'] ? calcPrice($price, $item['vat']) : null,
                    'discount' => $item['discount'],
                ];
            } else {
                $this->priceListData[$id_price_list][$item['id_product']]['price'] = $item['price'];
                $this->priceListData[$id_price_list][$item['id_product']]['price_with_vat'] = $item['price'] ? calcPrice($price, $item['vat']) : null;
                $this->priceListData[$id_price_list][$item['id_product']]['discount'] = $item['discount'];
            }
        }

        if ($id_variation) {
            return $this->priceListData[$id_price_list][$id_product]['variations']['id_variation'] ?? [];
        }

        return $this->priceListData[$id_price_list][$id_product] ?? [];
    }

    // Feeds
    public function getFeeds()
    {
        if (isset($this->feeds)) {
            return $this->feeds;
        }

        return $this->feeds = sqlFetchAll($this->selectSQL('feeds', []), 'id');
    }

    public function getFeedFields()
    {
        $feedFields = [
            'id_section' => [
                'title' => 'Sekce',
                'size' => 200,
            ],
            'active' => [
                'title' => 'Aktivní',
                'size' => 120,
            ],
            'title' => [
                'title' => 'Název',
                'size' => 300,
            ],
            'cpc' => [
                'title' => 'Cena za proklik',
                'size' => 70,
            ],
        ];
        if (findModule(Modules::FEED_GIFTS)) {
            $feedFields['gift_text'] = [
                'title' => 'Popis dárku',
                'size' => 300,
            ];
        }

        return $feedFields;
    }

    private $feedData;

    public function getFeedData($id_feed, $id_product)
    {
        static $feedUtils;
        if (!isset($feedUtils)) {
            $feedUtils = ServiceContainer::getService(\KupShop\FeedsBundle\Utils\FeedUtils::class);
        }

        if (array_key_exists($id_product, $this->feedData[$id_feed] ?? [])) {
            return $this->feedData[$id_feed][$id_product];
        }

        $qb = sqlQueryBuilder()->select('fp.*')->from('feed_products', 'fp')
            ->where(Op::equals(['id_feed' => $id_feed, 'id_product' => $id_product]));

        $feed = $this->getFeeds()[$id_feed];
        $mappingType = null;
        if ($feedUtils->filterThirdPartySectionType($feed['type'])) {
            $mappingType = $feed['type'];
        } elseif (!empty($feed['section_mapping'])) {
            $mappingType = $feed['section_mapping'];
        }

        if (isset($mappingType)) {
            $qb->leftJoin('fp', "kupshop_shared.feed_{$mappingType}", 'fs', 'fp.id_section = fs.id')
                ->addSelect('fs.name as section_name');
        }

        return $qb->execute()->fetch();
    }

    // Stores
    public function getStores()
    {
        if (isset($this->stores)) {
            return $this->stores;
        }
        $storesInStore = ServiceContainer::getService(\KupShop\StoresBundle\Utils\StoresInStore::class);

        return $this->stores = $storesInStore->getStores();
    }

    public function getStoresFields()
    {
        $storesFields = [
            'min_quantity' => [
                'title' => 'Min. množství',
                'size' => 200,
            ],
            'quantity' => [
                'title' => 'Kusů skladem',
                'size' => 200,
            ],
        ];

        return $storesFields;
    }

    private $storesData;

    public function getStoresData($id_store, $id_product)
    {
        if (empty($id_store) || empty($id_product)) {
            return [];
        }

        if (array_key_exists($id_product, $this->storesData[$id_product] ?? [])) {
            return $this->storesData[$id_product][$id_store];
        }

        $where = ['id_store' => $id_store, 'id_variation' => null];

        if (StringUtil::startsWith($id_product, 'v')) {
            $id_variation = trim($id_product, 'v');
            $where['id_variation'] = $id_variation;
        } else {
            $where['id_product'] = $id_product;
        }

        $qb = sqlQueryBuilder()->select('*')->from('stores_items')
            ->where(Op::equalsNullable($where));

        $this->storesData[$id_product][$id_store] = $qb->execute()->fetch();

        return $this->storesData[$id_product][$id_store];
    }
}
