function GetGoodsByNewBaseQuery($query, $oby = "   ", $lim = '', $where = "", $depot, $storages = array(), $filter = '', $withCount = false, $withGroups = false, $withBrands = false, $filterDeposit = false)
{
    if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items, _deposits')) < 0) {
        $this->errors($r);
    }

    $storagesIdsArr = array();
    foreach ($storages as $value) {
        $storagesIdsArr[] = $value['id'];
    }
    $storagesIdsInStr = implode(', ', $storagesIdsArr);

    $query = "CREATE TEMPORARY TABLE _items (INDEX(id)) ENGINE MEMORY " . $query;
    if (($r = $this->get_mysql_change($query)) < 0) {
        $this->errors($r);
    }

    $user_id = (int)GetUserID();

    $q = "CREATE TEMPORARY TABLE _deposits ENGINE MEMORY SELECT itn.id as item_id, cs.id as storage_id,
            COALESCE(cds.count, 0) as count,
            cds.price as price,
            COALESCE(us.count, 0) as user_count,
            COALESCE(fs.count, 0) as free_count,
            COALESCE(osz.count, 0) as osz_count,
            COALESCE(ss.count, 0) as stock_count,
            (GREATEST(COALESCE(fs.count, 0), COALESCE(osz.count, 0)) + COALESCE(ss.count, 0)) as total_free_count,
            COALESCE(cms.markup, cm.markup, 1) as markup,
            COALESCE(cms.days, cm.days, 0) as days,
            COALESCE(cms.days_other, cm.days_other, \"\") as days_other,
            CAST(COALESCE(cms.days_comment, cm.days_comment, \"\") as CHAR(300)) as days_comment,
            COALESCE(cms.days_process, cm.days_process, \"\") as days_process,
            DATE_ADD(NOW(), INTERVAL COALESCE(cms.days, cm.days, 0) DAY) as ship_date
        FROM _items itn
        JOIN catalog_storage cs ON cs.public = 1
        LEFT JOIN catalog_deposit_storage cds ON itn.id = cds.item_id AND cds.storage_id = cs.id
        LEFT JOIN catalog_deposit_free_storage fs ON itn.id = fs.item_id AND cs.id = fs.storage_id
        LEFT JOIN catalog_deposit_stock_storage ss ON itn.id = ss.item_id AND cs.id = ss.stock_storage_id AND ss.depot_id = {$depot}
        LEFT JOIN catalog_deposit_osz_storage osz ON itn.id = osz.item_id AND cs.id = osz.stock_storage_id AND osz.depot_id = {$depot}
        LEFT JOIN catalog_deposit_user_storage us ON {$user_id} = us.user_id AND itn.id = us.item_id AND cs.id = us.storage_id
        LEFT JOIN catalog_markup cm ON cm.fid_out = cs.depot_id AND cm.fid_in = {$depot}
        LEFT JOIN catalog_markup_storage cms ON cs.id = cms.storage_id AND cm.fid_in = {$depot}
    ORDER BY cm.days ASC, cms.days ASC, markup ASC";
    if (($r = $this->get_mysql_change($q)) < 0) {
        $this->errors($r);
    }

        // CREATE TEMPORARY TABLE _items_depots AS
    $q = "
        SELECT I.*,
        dt.price_value AS Price,
        dt.price_min as price_min,
         IFNULL(SUM(D.count),0) AS deposit,
         dt.max_show,
         dt.package_count,
         IFNULL(dt.request_count,0) as req_count,
         dt.request_gmtdate as req_date,
         SUM(IFNULL(ds.count,0)) as reserve_sum
        FROM _items AS I
    ";
    if ($filterDeposit == 2) {
        $q .= "JOIN catalog_deposit_storage AS D ON D.item_id = I.id and D.storage_id in (". $storagesIdsInStr .")";
    } else {
        $q .= "LEFT JOIN catalog_deposit_storage AS D ON D.item_id = I.id and D.storage_id in (". $storagesIdsInStr .")";
    }
    if ($filterDeposit == 1) {
        $q .= "JOIN (SELECT
            item_id,
            SUM(CASE WHEN storage_id in (". $storagesIdsInStr .") THEN `count` ELSE 0 END)
             + SUM(CASE WHEN storage_id in (". $storagesIdsInStr .") THEN `user_count` ELSE 0 END)
             + SUM(CASE WHEN storage_id not in (". $storagesIdsInStr .") THEN `total_free_count` ELSE 0 END) as depositall
            from _deposits
            GROUP BY item_id
            HAVING depositall > 0) dall ON I.id = dall.item_id
        ";
    }
    $q .= "JOIN catalog_deposit AS dt ON I.id = dt.item_id AND dt.depot_id = {$depot}
           LEFT JOIN catalog_deposit_stock ds ON I.id = ds.item_id AND dt.depot_id = ds.depot_id
        GROUP BY I.id
    ";

    $q_items_depots = $q;
    // if (($r = $this->get_mysql_change($q)) < 0) {
    //     $this->errors($r);
    // }

    if ($user_id) {
            // CREATE TEMPORARY TABLE _items_final as
        $q = "
            SELECT
            c.*,
            (CASE WHEN usi.item_id IS NULL
                THEN 0
                ELSE 1
            END) as iswish,

            GREATEST(
                COALESCE(
                    Fix.fix_price,
                    LEAST(
                        c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100,
                        COALESCE(FixSale.fix_price, c.Price)
                    )
                ) * ((100 - IFNULL(U.discount,0)) / 100),
                (IF(U.is_min_price_disabled = 1, 0, c.price_min))
            ) as discountprice,

            -- GREATEST(
            --     (CASE
            --         WHEN (Fix.fix_price IS NOT NULL) THEN
            --             Fix.fix_price
            --         WHEN (FixSale.fix_price IS NOT NULL) THEN
            --             LEAST(
            --                 c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100,
            --                 FixSale.fix_price
            --             )
            --         ELSE
            --             c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100
            --     END) * ((100 - IFNULL(U.discount,0)) / 100),

            --     (CASE
            --         WHEN (U.is_min_price_disabled = 1)
            --             THEN 0
            --         ELSE
            --             c.price_min

            --     END)
            -- ) as discountprice,

            -- GREATEST(
            --   MIN(
            --     (CASE
            --       WHEN (FixSale.fix_price IS NOT NULL) THEN
            --         FixSale.fix_price
            --       WHEN (Sale.value IS NOT NULL) THEN
            --         c.Price * (100 - IFNULL(Sale.value,0)) / 100
            --       WHEN (Fix.fix_price IS NOT NULL) THEN
            --         Fix.fix_price
            --       ELSE
            --         c.Price * (100 - (IFNULL(Grp.value,0)+IFNULL(Itm.value,0)) - IFNULL(U.discount,0)) / 100
            --     END)
            --   ),
            --   (CASE
            --     WHEN (U.is_min_price_disabled = 1)
            --       THEN 0
            --     ELSE
            --       c.price_min
            --   END)
            -- ) as discountprice,

            (CASE
              WHEN
                (FixSale.fix_price IS NOT NULL) OR
                (Sale.value IS NOT NULL)
              THEN
                1
              ELSE
                0
              END
            ) as is_sale,

            (CASE
              WHEN
                Brkn.id IS NOT NULL
              THEN
                1
              ELSE
                0
              END
            ) as is_broken,

            (CASE
              WHEN
                Brkn.id IS NOT NULL
              THEN
                GROUP_CONCAT(DISTINCT CONCAT_WS('|', Brkn.count, Brkn.discount) ORDER BY Brkn.discount DESC SEPARATOR '||')
              ELSE
                NULL
              END
            ) as brokens

            FROM ($q_items_depots) c
              LEFT JOIN users As U ON U.id = {$user_id}
              LEFT JOIN users_saveditems usi ON U.id = usi.user_id AND c.id = usi.item_id
              LEFT JOIN billing_discount AS Fix ON Fix.item_id = c.id AND Fix.user_id = U.id AND Fix.fix_price != '0'
              LEFT JOIN billing_discount AS FixSale ON FixSale.item_id = c.id AND FixSale.user_id = 0 AND FixSale.fix_price != '0' AND U.depot_id = FixSale.depot_id AND c.deposit >= FixSale.minimal
              LEFT JOIN billing_discount AS Sale ON Sale.item_id = c.id AND Sale.user_id = 0 AND U.depot_id = Sale.depot_id AND c.deposit >= Sale.minimal
              LEFT JOIN billing_discount AS Grp ON Grp.id_group = c.id_group AND Grp.user_id = U.id AND Grp.fix_price = '0'
              LEFT JOIN billing_discount AS Itm ON Itm.item_id = c.id AND Itm.user_id = U.id AND Itm.fix_price = '0'
              LEFT JOIN catalog_broken AS Brkn ON U.depot_id = Brkn.depot_id AND Brkn.item_id = c.id
        ";
        $q .= "
            GROUP BY c.id
        ";
        if ($filter != '' || $where != '') {
            $q .= ' HAVING ' . $filter . " ";
            if ($filter != '' && $where != '') {
                $q .= " AND ";
            }
            $q .= $where . " ";
        }
    } else {
        $q = "SELECT c.*, c.Price as discountprice FROM ($q_items_depots) c";
    }
    if (!empty($oby)) {
        $oby = " ORDER BY " . $oby;
    } else {
        $oby = " ";
    }
    // if (($goods = $this->get_mysql_fetch_assoc($q . $oby . $lim)) < 0) {
    if (($goods = $this->get_mysql_fetch_assoc($q  . $oby . $lim)) < 0) {
        $this->errors($goods);
    }
    $lim_count = false;
    if (!empty($lim)) {
        if (($goods_for_count = $this->get_mysql_fetch_assoc($q)) < 0) {
            $this->errors($goods_for_count);
        }
        $lim_count = count($goods_for_count);
        unset($goods_for_count);
    }

    $resultGoodsIds = array();
    if (is_array($goods)) {
        foreach ($goods as $value) {
            $resultGoodsIds[] = $value['id'];
        }
        if (($datatmp = $this->get_mysql_fetch_assoc("SELECT * FROM _deposits WHERE item_id IN (" . implode(', ', $resultGoodsIds) . ")")) < 0) {
            $this->errors($datatmp);
        }

        $deposit_array = array();
        $hasLocalDeposit = array();
        if (is_array($datatmp)) {
            foreach ($datatmp as $row) {
                if (!isset($deposit_array[$row['item_id']])) {
                    $deposit_array[$row['item_id']] = array();
                }
                $deposit_array[$row['item_id']][$row['storage_id']] = array(
                        'count' => (int)$row['count'] + (int)$row['user_count'],
                        'price' => (float)$row['price'],
                        'free_count' => (int)$row['total_free_count'],
                        'markup' => (float)number_format($row['markup'], 2),
                        'days' => (int)$row['days'],
                        'days_other' => (string)$row['days_other'],
                        'days_comment' => (string)$row['days_comment'],
                        'ship_date' => (string)$row['ship_date']
                );

                if (in_array($row['storage_id'], $storagesIdsArr) && ((int)$row['count'] + (int)$row['user_count'] > 0)) {
                    $hasLocalDeposit[$row['item_id']] = true;
                }
            }
        }
        unset($datatmp);

        $q_item_info = "
            SELECT
                cinfo.id as id,
                cinfo.picture_files,
                cinfo.pair_id,
                cinfo.header,
                cinfo.content,
                cinfo.sale20,
                cinfo.article_code,
                cinfo.article_exact,
                cinfo.clone_key,
                cinfo.variation_code,
                binfo.header brand,
                binfo.exclusive_sort brand_exclusive_sort
            FROM _items i
            JOIN catalog_item cinfo on i.id = cinfo.id
            LEFT JOIN catalog_brand binfo ON cinfo.brand_id = binfo.id
        ";
        if (($infotmp = $this->get_mysql_fetch_assoc($q_item_info)) < 0) {
            $this->errors($infotmp);
        }
        $items_info = array();
        foreach ($infotmp as $irow) {
            $items_info[$irow['id']] = $irow;
        }
        unset($infotmp);

        foreach ($goods as &$value) {
            $value['deposit_json'] = json_encode($deposit_array[$value['id']]);
            $value['has_local_deposit'] = isset($hasLocalDeposit[$value['id']]);
            if (isset($items_info[$value['id']])) {
                $value = array_merge($value, $items_info[$value['id']]);
            }
        }
        unset($deposit_array);
        unset($items_info);
    }


    if ($withCount || $withGroups || $withBrands) {
        if (!count($goods)) {
            return array(
                'goods' => null,
                'count' => 0,
                'groups' => null,
                'brands' => null
            );
        }
        $result = array();
        $result['goods'] = $goods;
        if ($withCount) {
            if (!empty($lim)) {
                if ($lim_count) {
                    $count = $lim_count;
                } else {
                    $count = $this->get_mysql_fetch_assoc("SELECT COUNT(*) as count FROM _items i");
                    $count = $count[0]['count'];
                }
            } else {
                $count = $this->get_mysql_fetch_assoc("SELECT COUNT(*) as count FROM _items i WHERE id IN (" . implode(', ', $resultGoodsIds) . ")");
                $count = $count[0]['count'];
                // $count = count($result['goods']);
            }
            $result['count'] = $count;
        }
        if ($withGroups) {
            $q = "SELECT
                DISTINCT
                cc.header,
                cc.id
                FROM _items i
                LEFT JOIN catalog_category cc ON i.category_id=cc.id
                WHERE
                i.id IN (" . implode(', ', $resultGoodsIds) . ") AND
                cc.header !=''
            ";
            $result['groups'] = $this->get_mysql_fetch_assoc($q);;
        }
        if ($withBrands) {
            $q = "SELECT
                DISTINCT
                cb.header,
                cb.id
                FROM _items i
                LEFT JOIN catalog_brand cb ON i.brand_id=cb.id
                WHERE
                i.id IN (" . implode(', ', $resultGoodsIds) . ") AND
                cb.header !=''
            ";
            $result['brands'] = $this->get_mysql_fetch_assoc($q);;
        }

        return $result;
    }

    return $goods;
}

function GetGoodsByBaseQuery($query, $oby = "   ", $lim = '', $where = "", $depot, $storages = array(), $filter = '', $withCount = false, $withGroups = false, $withBrands = false, $filterDeposit = false)
{

    // if (($r = $this->get_mysql_change('SET SESSION group_concat_max_len = 5000000')) < 0) {
    //     $this->errors($r);
    // }
    // $tm = microtime(true);
    // echo 'func start:';
    // print_r(memory_get_usage(true));
    // echo ', time: ';
    // var_dump(microtime() - $tm);
    // echo('<br>');
    if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items')) < 0) {
        $this->errors($r);
    }
    if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items_storages')) < 0) {
        $this->errors($r);
    }
    if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _deposits')) < 0) {
        $this->errors($r);
    }
    // if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _catalog_deposit_storage')) < 0) {
    //     $this->errors($r);
    // }
    if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items_depots')) < 0) {
        $this->errors($r);
    }
    if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items_almost_final')) < 0) {
        $this->errors($r);
    }
    if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items_final')) < 0) {
        $this->errors($r);
    }
    // if (($r = $this->get_mysql_change('DROP TABLE IF EXISTS _items, _items_storages, _deposits, _items_depots, _items_almost_final, _items_final')) < 0) {
    //     $this->errors($r);
    // }

    $storagesIdsArr = array();
    foreach ($storages as $value) {
        $storagesIdsArr[] = $value['id'];
    }
    $storagesIdsInStr = implode(', ', $storagesIdsArr);

    // var_dump(microtime(true) - $tm);
    $query = "CREATE TEMPORARY TABLE _items (INDEX(id)) " . $query;
    if (($r = $this->get_mysql_change($query)) < 0) {
        $this->errors($r);
    }


    $query = "CREATE TEMPORARY TABLE _items_storages SELECT I.*, cs.id as strgid, cs.depot_id as strgdepotid FROM _items I, catalog_storage cs
        where cs.public = 1
    ";
    if (($r = $this->get_mysql_change($query)) < 0) {
        $this->errors($r);
    }

    // var_dump(microtime(true) - $tm);
    // $q = "
    //     CREATE TEMPORARY TABLE _catalog_deposit_storage (INDEX(item_id))
    //     SELECT itn.id as item_id, itn.strgid as storage_id, ifnull(cds.count, 0) as count, ifnull(fs.count, 0) as free_count, ifnull(ss.count, 0) as stock_count, (ifnull(fs.count, 0) + ifnull(ss.count, 0)) as total_free_count,
    //         IFNULL(cm.markup, 1) as markup, IFNULL(cm.days, 0) as days, IFNULL(cm.days_other, \"\") as days_other, IFNULL(cm.days_comment, \"\") as days_comment, IFNULL(cm.days_process, \"\") as days_process, DATE_ADD(NOW(), INTERVAL IFNULL(cm.days, 0) DAY) as ship_date FROM catalog_deposit_storage cds
    //       RIGHT JOIN _items_storages itn ON itn.id = cds.item_id AND cds.storage_id = itn.strgid
    //       LEFT JOIN catalog_deposit_free_storage fs ON itn.id = fs.item_id AND itn.strgid = fs.storage_id
    //           LEFT JOIN catalog_deposit_stock_storage ss ON itn.id = ss.item_id AND itn.strgid = ss.stock_storage_id AND ss.depot_id = {$depot}
    //           LEFT JOIN catalog_markup cm ON cm.fid_out = itn.strgdepotid AND cm.fid_in = {$depot}
    // ";
    // // $q = "
    // //     CREATE TEMPORARY TABLE _catalog_deposit_storage (INDEX(item_id))
    // //     SELECT cds1.* FROM catalog_deposit_storage cds1
    // //     JOIN  (SELECT cds.item_id, cds.storage_id, MAX(cds.date) as maxdate FROM catalog_deposit_storage cds
    // //       JOIN _items I ON I.id = cds.item_id
    // //       WHERE cds.storage_id IN ({$storagesIdsInStr})
    // //       GROUP BY cds.item_id, cds.storage_id) cds2
    // //       ON cds1.item_id = cds2.item_id AND cds1.storage_id = cds2.storage_id AND cds1.date = cds2.maxdate
    // // ";
    // if (($r = $this->get_mysql_change($q)) < 0) {
    //     $this->errors($r);
    // }

    $user_id = (int)GetUserID();
    $q = "CREATE TEMPORARY TABLE _deposits SELECT itn.id as item_id, itn.strgid as storage_id,
            ifnull(cds.count, 0) as count,
            cds.price as price,
            ifnull(us.count, 0) as user_count,
            ifnull(fs.count, 0) as free_count,
            ifnull(osz.count, 0) as osz_count,
            ifnull(ss.count, 0) as stock_count,
            (GREATEST(ifnull(fs.count, 0), ifnull(osz.count, 0)) + ifnull(ss.count, 0)) as total_free_count,
            IFNULL(cms.markup, IFNULL(cm.markup, 1)) as markup,
            IFNULL(cms.days, IFNULL(cm.days, 0)) as days,
            IFNULL(cms.days_other, IFNULL(cm.days_other, \"\")) as days_other,
            IFNULL(cms.days_comment, IFNULL(cm.days_comment, \"\")) as days_comment,
            IFNULL(cms.days_process, IFNULL(cm.days_process, \"\")) as days_process,
            DATE_ADD(NOW(), INTERVAL IFNULL(cms.days, IFNULL(cm.days, 0)) DAY) as ship_date
        FROM catalog_deposit_storage cds
        RIGHT JOIN _items_storages itn ON itn.id = cds.item_id AND cds.storage_id = itn.strgid
        LEFT JOIN catalog_deposit_free_storage fs ON itn.id = fs.item_id AND itn.strgid = fs.storage_id
        LEFT JOIN catalog_deposit_stock_storage ss ON itn.id = ss.item_id AND itn.strgid = ss.stock_storage_id AND ss.depot_id = {$depot}
        LEFT JOIN catalog_deposit_osz_storage osz ON itn.id = osz.item_id AND itn.strgid = osz.stock_storage_id AND osz.depot_id = {$depot}
        LEFT JOIN catalog_deposit_user_storage us ON {$user_id} = us.user_id AND itn.id = us.item_id AND itn.strgid = us.storage_id
        LEFT JOIN catalog_markup cm ON cm.fid_out = itn.strgdepotid AND cm.fid_in = {$depot}
        LEFT JOIN catalog_markup_storage cms ON itn.strgid = cms.storage_id AND cm.fid_in = {$depot}
    ORDER BY cm.days ASC, cms.days ASC, markup ASC";
    if (($r = $this->get_mysql_change($q)) < 0) {
        $this->errors($r);
    }
    // echo 'before select _deposits:';
    // print_r(memory_get_usage(true));
    // echo ', time: ';
    // var_dump(microtime() - $tm);
    // echo('<br>');
    // if (($datatmp = $this->get_mysql_fetch_assoc("SELECT * FROM _deposits")) < 0) {
    //     $this->errors($r);
    // }
    // echo 'after select _deposits:';
    // print_r(memory_get_usage(true));
    // echo ', time: ';
    // var_dump(microtime() - $tm);
    // echo('<br>');
    // $deposit_array = array();
    // if (is_array($datatmp)) {
    //     foreach ($datatmp as $row) {
    //         if (!isset($deposit_array[$row['item_id']])) {
    //             $deposit_array[$row['item_id']] = array();
    //         }
    //         $deposit_array[$row['item_id']][$row['storage_id']] = array(
    //                 'count' => (int)$row['count'] + (int)$row['user_count'],
    //                 'price' => (float)$row['price'],
    //                 'free_count' => (int)$row['total_free_count'],
    //                 'markup' => (float)number_format($row['markup'], 2),
    //                 'days' => (int)$row['days'],
    //                 'days_other' => (string)$row['days_other'],
    //                 'days_comment' => (string)$row['days_comment'],
    //                 'ship_date' => (string)$row['ship_date']
    //         );
    //     }
    // }
    // echo 'after rearray:';
    // print_r(memory_get_usage(true));
    // echo ', time: ';
    // var_dump(microtime() - $tm);
    // echo('<br>');
    // if (($rcount = $this->get_mysql_fetch_assoc("SELECT count(*) as rcount FROM _deposits")) < 0) {
    //     $this->errors($rcount);
    // }
    // $rcount = (int)$rcount[0]['rcount'];
    // $rcountoffset = 0;
    // $rcountstep = 10000;
    // $deposit_array = array();
    // while ($rcountoffset < $rcount) {
    //     if (($datatmp = $this->get_mysql_fetch_assoc("SELECT * FROM _deposits LIMIT {$rcountoffset}, {$rcountstep}")) < 0) {
    //         $this->errors($datatmp);
    //     }
    //     if (is_array($datatmp)) {
    //         foreach ($datatmp as $row) {
    //             if (!isset($deposit_array[$row['item_id']])) {
    //                 $deposit_array[$row['item_id']] = array();
    //             }
    //             $deposit_array[$row['item_id']][$row['storage_id']] = array(
    //                     'count' => (int)$row['count'] + (int)$row['user_count'],
    //                     'price' => (float)$row['price'],
    //                     'free_count' => (int)$row['total_free_count'],
    //                     'markup' => (float)number_format($row['markup'], 2),
    //                     'days' => (int)$row['days'],
    //                     'days_other' => (string)$row['days_other'],
    //                     'days_comment' => (string)$row['days_comment'],
    //                     'ship_date' => (string)$row['ship_date']
    //             );
    //         }
    //     }
    //     $rcountoffset += $rcountstep;
    // }

    // echo '<pre>';
    // print_r($deposit_array);
    // echo '</pre>';

    // $q = "ALTER TABLE _catalog_deposit_storage ADD INDEX `item_id` (`item_id`)";
    // if (($r = $this->get_mysql_change($q)) < 0) {
    //     $this->errors($r);
    // }

    // var_dump(microtime(true) - $tm);
    //    CONCAT(\"{\", IFNULL(GROUP_CONCAT(\"\\\"\", D.storage_id, \"\\\": { \\\"count\\\": \", D.count, \", \\\"free_count\\\": \", (IF ((datediff(dt.request_gmtdate,NOW()) > 30 OR dt.request_gmtdate IS NULL), D.total_free_count, 0)), \", \\\"markup\\\": \", D.markup, \", \\\"days\\\": \", D.days, \", \\\"ship_date\\\": \\\"\", D.ship_date, \"\\\" }\" SEPARATOR ', '), \"\"), \"}\") AS deposit_json,
    // $q = "
    //     CREATE TEMPORARY TABLE _items_depots AS
    //     SELECT I.*,
    //     (datediff(dt.request_gmtdate,NOW()) > 30 OR dt.request_gmtdate IS NULL) as show_other_depot,
    //     CONCAT(\"{\", IFNULL(GROUP_CONCAT(\"\\\"\", D.storage_id, \"\\\": { \\\"count\\\": \", D.count, \", \\\"free_count\\\": \", D.total_free_count, \", \\\"markup\\\": \", D.markup, \", \\\"days\\\": \", D.days, \", \\\"days_other\\\": \\\"\", D.days_other, \"\\\", \\\"days_comment\\\": \\\"\", D.days_comment, \"\\\", \\\"ship_date\\\": \\\"\", D.ship_date, \"\\\" }\"  ORDER BY D.days ASC SEPARATOR ', '), \"\"), \"}\") AS deposit_json,
    // ";
    $q = "
        CREATE TEMPORARY TABLE _items_depots AS
        SELECT I.*,
        (datediff(dt.request_gmtdate,NOW()) > 30 OR dt.request_gmtdate IS NULL) as show_other_depot,
    ";
    // $tmpcase = "CASE WHEN (D.storage_id = " . implode(" or D.storage_id = ", $storagesIdsArr) . ") THEN D.count END";
    // foreach ($storagesIdsArr as $value) {
    //     $q .= "IFNULL(SUM(CASE WHEN (D.storage_id = {$value}) THEN D.count END),0) AS depot_storage_{$value},";
    // }
    $q .= "
        dt.price_value AS Price,
        dt.price_min as price_min,
         IFNULL(SUM(D.count),0) AS deposit,
         dt.max_show,
         dt.package_count,
         IFNULL(dt.request_count,0) as req_count,
         dt.request_gmtdate as req_date,
         SUM(IFNULL(ds.count,0)) as reserve_sum
        FROM _items AS I
    ";
    if ($filterDeposit == 2) {
        $q .= "JOIN catalog_deposit_storage AS D ON D.item_id = I.id and D.storage_id in (". implode(', ', $storagesIdsArr) .")";
    } else {
        $q .= "LEFT JOIN catalog_deposit_storage AS D ON D.item_id = I.id and D.storage_id in (". implode(', ', $storagesIdsArr) .")";
    }
    if ($filterDeposit == 1) {
        $q .= "JOIN (SELECT
            item_id,
            SUM(CASE WHEN storage_id in (". implode(', ', $storagesIdsArr) .") THEN `count` ELSE 0 END)
             + SUM(CASE WHEN storage_id in (". implode(', ', $storagesIdsArr) .") THEN `user_count` ELSE 0 END)
             + SUM(CASE WHEN storage_id not in (". implode(', ', $storagesIdsArr) .") THEN `total_free_count` ELSE 0 END) as depositall
            from _deposits
            GROUP BY item_id
            HAVING depositall > 0) dall ON I.id = dall.item_id
        ";
    }
    $q .= "JOIN catalog_deposit AS dt ON I.id = dt.item_id AND dt.depot_id = {$depot}
           LEFT JOIN catalog_deposit_stock ds ON I.id = ds.item_id AND dt.depot_id = ds.depot_id
        GROUP BY I.id,
         I.header,
         I.id_group,
         I.content,
         I.article_code,
         I.variation_code,
         I.public
    ";
    if (($r = $this->get_mysql_change($q)) < 0) {
        $this->errors($r);
    }

    // var_dump(microtime(true) - $tm);
    $q = "
        CREATE TEMPORARY TABLE _items_almost_final as
        SELECT I.*,
            (CASE
                WHEN (cm.fid_in IS NOT NULL) THEN
                    SUM(IFNULL(cd.count_other,0))+IFNULL(I.req_count,0)+IFNULL(I.reserve_sum,0)
                ELSE
                    IFNULL(I.req_count,0)
            END) AS show_wagon,
            (CASE
                WHEN (cm.fid_in IS NOT NULL) THEN
                    SUM(IFNULL(cd.count_other,0))
                ELSE
                    0
            END) AS sum_count_other

            FROM _items_depots I
            LEFT JOIN catalog_deposit cd ON I.id = cd.item_id AND cd.depot_id <> {$depot}
            LEFT JOIN catalog_markup cm ON {$depot} = cm.fid_in
            GROUP BY I.id
    ";
    if (($r = $this->get_mysql_change($q)) < 0) {
        $this->errors($r);
    }
    if ($userId = GetUserID()) {
        $q = "
            CREATE TEMPORARY TABLE _items_final as
            SELECT
            c.*,
            (CASE WHEN usi.item_id IS NULL
                THEN 0
                ELSE 1
            END) as iswish,

            GREATEST(
                COALESCE(
                    Fix.fix_price,
                    LEAST(
                        c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100,
                        COALESCE(FixSale.fix_price, c.Price)
                    )
                ) * ((100 - IFNULL(U.discount,0)) / 100),
                (IF(U.is_min_price_disabled = 1, 0, c.price_min))
            ) as discountprice,

            -- GREATEST(
            --     (CASE
            --         WHEN (Fix.fix_price IS NOT NULL) THEN
            --             Fix.fix_price
            --         WHEN (FixSale.fix_price IS NOT NULL) THEN
            --             LEAST(
            --                 c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100,
            --                 FixSale.fix_price
            --             )
            --         ELSE
            --             c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100
            --     END) * ((100 - IFNULL(U.discount,0)) / 100),

            --     (CASE
            --         WHEN (U.is_min_price_disabled = 1)
            --             THEN 0
            --         ELSE
            --             c.price_min

            --     END)
            -- ) as discountprice,

            -- GREATEST(
            --   MIN(
            --     (CASE
            --       WHEN (FixSale.fix_price IS NOT NULL) THEN
            --         FixSale.fix_price
            --       WHEN (Sale.value IS NOT NULL) THEN
            --         c.Price * (100 - IFNULL(Sale.value,0)) / 100
            --       WHEN (Fix.fix_price IS NOT NULL) THEN
            --         Fix.fix_price
            --       ELSE
            --         c.Price * (100 - (IFNULL(Grp.value,0)+IFNULL(Itm.value,0)) - IFNULL(U.discount,0)) / 100
            --     END)
            --   ),
            --   (CASE
            --     WHEN (U.is_min_price_disabled = 1)
            --       THEN 0
            --     ELSE
            --       c.price_min
            --   END)
            -- ) as discountprice,

            (CASE
              WHEN
                (FixSale.fix_price IS NOT NULL) OR
                (Sale.value IS NOT NULL)
              THEN
                1
              ELSE
                0
              END
            ) as is_sale,

            (CASE
              WHEN
                Brkn.id IS NOT NULL
              THEN
                1
              ELSE
                0
              END
            ) as is_broken,

            (CASE
              WHEN
                Brkn.id IS NOT NULL
              THEN
                GROUP_CONCAT(DISTINCT CONCAT_WS('|', Brkn.count, Brkn.discount) ORDER BY Brkn.discount DESC SEPARATOR '||')
              ELSE
                NULL
              END
            ) as brokens

            FROM _items_almost_final AS c
              LEFT JOIN users As U ON U.id = {$userId}
              LEFT JOIN users_saveditems usi ON U.id = usi.user_id AND c.id = usi.item_id
              LEFT JOIN billing_discount AS Fix ON Fix.item_id = c.id AND Fix.user_id = U.id AND Fix.fix_price != '0'
              LEFT JOIN billing_discount AS FixSale ON FixSale.item_id = c.id AND FixSale.user_id = 0 AND FixSale.fix_price != '0' AND U.depot_id = FixSale.depot_id AND c.deposit >= FixSale.minimal
              LEFT JOIN billing_discount AS Sale ON Sale.item_id = c.id AND Sale.user_id = 0 AND U.depot_id = Sale.depot_id AND c.deposit >= Sale.minimal
              LEFT JOIN billing_discount AS Grp ON Grp.id_group = c.id_group AND Grp.user_id = U.id AND Grp.fix_price = '0'
              LEFT JOIN billing_discount AS Itm ON Itm.item_id = c.id AND Itm.user_id = U.id AND Itm.fix_price = '0'
              LEFT JOIN catalog_broken AS Brkn ON U.depot_id = Brkn.depot_id AND Brkn.item_id = c.id

            GROUP BY c.id
        ";
        if (($r = $this->get_mysql_change($q)) < 0) {
            $this->errors($r);
        }

        $q = "SELECT * FROM _items_final c ";
        if ($filter != '' || $where != '') {
            $q .= ' WHERE ' . $filter . " ";
            if ($filter != '' && $where != '') {
                $q .= " AND ";
            }
            $q .= $where . " ";
        }
        $lastTable = '_items_final';
    } else {
        $q = "SELECT *, Price as discountprice FROM _items_almost_final c ";
        if ($where != '') {
            $q .= ' WHERE ' . $where . " ";
        }
        $lastTable = '_items_almost_final';
    }
    // var_dump(microtime(true) - $tm);

    // var_dump(microtime(true) - $tm);
    $goods=array();

    if (!empty($oby)) {
        $oby = "ORDER BY " . $oby;
    } else {
        $oby = " ";
    }
    if (($goods = $this->get_mysql_fetch_assoc($q . $oby . $lim)) < 0) {
        $this->errors($goods);
    } else {
        if (array_search((int)$depot, $this->blockOrderToDepots) !== false) {
            foreach ($goods as &$value) {
                $value['show_wagon'] = 0;
            }
        }
        if (is_array($goods)) {
            $resultGoodsIds = array();
            foreach ($goods as $value) {
                $resultGoodsIds[] = $value['id'];
            }
            if (($datatmp = $this->get_mysql_fetch_assoc("SELECT * FROM _deposits WHERE item_id IN (" . implode(', ', $resultGoodsIds) . ")")) < 0) {
                $this->errors($r);
            }
            $deposit_array = array();
            $hasLocalDeposit = array();
            if (is_array($datatmp)) {
                foreach ($datatmp as $row) {
                    if (!isset($deposit_array[$row['item_id']])) {
                        $deposit_array[$row['item_id']] = array();
                    }
                    $deposit_array[$row['item_id']][$row['storage_id']] = array(
                            'count' => (int)$row['count'] + (int)$row['user_count'],
                            'price' => (float)$row['price'],
                            'free_count' => (int)$row['total_free_count'],
                            'markup' => (float)number_format($row['markup'], 2),
                            'days' => (int)$row['days'],
                            'days_other' => (string)$row['days_other'],
                            'days_comment' => (string)$row['days_comment'],
                            'ship_date' => (string)$row['ship_date']
                    );

                    if (in_array($row['storage_id'], $storagesIdsArr) && ((int)$row['count'] + (int)$row['user_count'] > 0)) {
                        $hasLocalDeposit[$row['item_id']] = true;
                    }
                }
            }
            // echo 'before unset:';
            // print_r(memory_get_usage(true));
            // echo ', time: ';
            // var_dump(microtime() - $tm);
            // echo('<br>');
            unset($datatmp);
            foreach ($goods as &$value) {
                $value['deposit_json'] = json_encode($deposit_array[$value['id']]);
                $value['has_local_deposit'] = isset($hasLocalDeposit[$value['id']]);
            }
            unset($deposit_array);
        }
        if ($withCount || $withGroups || $withBrands) {
            $result = array();
            $result['goods'] = $goods;
            if ($withCount) {
                $count = $this->get_mysql_fetch_assoc("SELECT COUNT(*) as count FROM (" . $q . ") as items");
                $count = $count[0]['count'];
                $result['count'] = $count;
            }
            if ($withGroups) {
                $q = "SELECT
                    DISTINCT
                    cc.header,
                    cc.id
                    FROM {$lastTable} c
                    LEFT JOIN catalog_category cc ON c.category_id=cc.id
                    WHERE
                    cc.header !=''
                ";
                $result['groups'] = $this->get_mysql_fetch_assoc($q);;
            }
            if ($withBrands) {
                $q = "SELECT
                    DISTINCT
                    cb.header,
                    cb.id
                    FROM {$lastTable} c
                    LEFT JOIN catalog_brand cb ON c.brand_id=cb.id
                    WHERE
                    cb.header !=''
                ";
                $result['brands'] = $this->get_mysql_fetch_assoc($q);;
            }
            // echo 'after all:';
            // print_r(memory_get_usage(true));
            // echo ', time: ';
            // var_dump(microtime() - $tm);
            // echo('<br>');
            return $result;
        }

        return $goods;
    }
}