daily pastebin goal
46%
SHARE
TWEET

Untitled

a guest Mar 22nd, 2018 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function GetGoodsByNewBaseQuery($query, $oby = "   ", $lim = '', $where = "", $depot, $storages = array(), $filter = '', $withCount = false, $withGroups = false, $withBrands = false, $filterDeposit = false)
  2. {
  3.     if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items, _deposits')) < 0) {
  4.         $this->errors($r);
  5.     }
  6.  
  7.     $storagesIdsArr = array();
  8.     foreach ($storages as $value) {
  9.         $storagesIdsArr[] = $value['id'];
  10.     }
  11.     $storagesIdsInStr = implode(', ', $storagesIdsArr);
  12.  
  13.     $query = "CREATE TEMPORARY TABLE _items (INDEX(id)) ENGINE MEMORY " . $query;
  14.     if (($r = $this->get_mysql_change($query)) < 0) {
  15.         $this->errors($r);
  16.     }
  17.  
  18.     $user_id = (int)GetUserID();
  19.  
  20.     $q = "CREATE TEMPORARY TABLE _deposits ENGINE MEMORY SELECT itn.id as item_id, cs.id as storage_id,
  21.            COALESCE(cds.count, 0) as count,
  22.            cds.price as price,
  23.            COALESCE(us.count, 0) as user_count,
  24.            COALESCE(fs.count, 0) as free_count,
  25.            COALESCE(osz.count, 0) as osz_count,
  26.            COALESCE(ss.count, 0) as stock_count,
  27.            (GREATEST(COALESCE(fs.count, 0), COALESCE(osz.count, 0)) + COALESCE(ss.count, 0)) as total_free_count,
  28.            COALESCE(cms.markup, cm.markup, 1) as markup,
  29.            COALESCE(cms.days, cm.days, 0) as days,
  30.            COALESCE(cms.days_other, cm.days_other, \"\") as days_other,
  31.            CAST(COALESCE(cms.days_comment, cm.days_comment, \"\") as CHAR(300)) as days_comment,
  32.            COALESCE(cms.days_process, cm.days_process, \"\") as days_process,
  33.            DATE_ADD(NOW(), INTERVAL COALESCE(cms.days, cm.days, 0) DAY) as ship_date
  34.        FROM _items itn
  35.        JOIN catalog_storage cs ON cs.public = 1
  36.        LEFT JOIN catalog_deposit_storage cds ON itn.id = cds.item_id AND cds.storage_id = cs.id
  37.        LEFT JOIN catalog_deposit_free_storage fs ON itn.id = fs.item_id AND cs.id = fs.storage_id
  38.        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}
  39.        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}
  40.        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
  41.        LEFT JOIN catalog_markup cm ON cm.fid_out = cs.depot_id AND cm.fid_in = {$depot}
  42.        LEFT JOIN catalog_markup_storage cms ON cs.id = cms.storage_id AND cm.fid_in = {$depot}
  43.    ORDER BY cm.days ASC, cms.days ASC, markup ASC";
  44.     if (($r = $this->get_mysql_change($q)) < 0) {
  45.         $this->errors($r);
  46.     }
  47.  
  48.         // CREATE TEMPORARY TABLE _items_depots AS
  49.     $q = "
  50.        SELECT I.*,
  51.        dt.price_value AS Price,
  52.        dt.price_min as price_min,
  53.         IFNULL(SUM(D.count),0) AS deposit,
  54.         dt.max_show,
  55.         dt.package_count,
  56.         IFNULL(dt.request_count,0) as req_count,
  57.         dt.request_gmtdate as req_date,
  58.         SUM(IFNULL(ds.count,0)) as reserve_sum
  59.        FROM _items AS I
  60.    ";
  61.     if ($filterDeposit == 2) {
  62.         $q .= "JOIN catalog_deposit_storage AS D ON D.item_id = I.id and D.storage_id in (". $storagesIdsInStr .")";
  63.     } else {
  64.         $q .= "LEFT JOIN catalog_deposit_storage AS D ON D.item_id = I.id and D.storage_id in (". $storagesIdsInStr .")";
  65.     }
  66.     if ($filterDeposit == 1) {
  67.         $q .= "JOIN (SELECT
  68.            item_id,
  69.            SUM(CASE WHEN storage_id in (". $storagesIdsInStr .") THEN `count` ELSE 0 END)
  70.             + SUM(CASE WHEN storage_id in (". $storagesIdsInStr .") THEN `user_count` ELSE 0 END)
  71.             + SUM(CASE WHEN storage_id not in (". $storagesIdsInStr .") THEN `total_free_count` ELSE 0 END) as depositall
  72.            from _deposits
  73.            GROUP BY item_id
  74.            HAVING depositall > 0) dall ON I.id = dall.item_id
  75.        ";
  76.     }
  77.     $q .= "JOIN catalog_deposit AS dt ON I.id = dt.item_id AND dt.depot_id = {$depot}
  78.           LEFT JOIN catalog_deposit_stock ds ON I.id = ds.item_id AND dt.depot_id = ds.depot_id
  79.        GROUP BY I.id
  80.    ";
  81.  
  82.     $q_items_depots = $q;
  83.     // if (($r = $this->get_mysql_change($q)) < 0) {
  84.     //     $this->errors($r);
  85.     // }
  86.  
  87.     if ($user_id) {
  88.             // CREATE TEMPORARY TABLE _items_final as
  89.         $q = "
  90.            SELECT
  91.            c.*,
  92.            (CASE WHEN usi.item_id IS NULL
  93.                THEN 0
  94.                ELSE 1
  95.            END) as iswish,
  96.  
  97.            GREATEST(
  98.                COALESCE(
  99.                    Fix.fix_price,
  100.                    LEAST(
  101.                        c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100,
  102.                        COALESCE(FixSale.fix_price, c.Price)
  103.                    )
  104.                ) * ((100 - IFNULL(U.discount,0)) / 100),
  105.                (IF(U.is_min_price_disabled = 1, 0, c.price_min))
  106.            ) as discountprice,
  107.  
  108.            -- GREATEST(
  109.            --     (CASE
  110.            --         WHEN (Fix.fix_price IS NOT NULL) THEN
  111.            --             Fix.fix_price
  112.            --         WHEN (FixSale.fix_price IS NOT NULL) THEN
  113.            --             LEAST(
  114.            --                 c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100,
  115.            --                 FixSale.fix_price
  116.            --             )
  117.            --         ELSE
  118.            --             c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100
  119.            --     END) * ((100 - IFNULL(U.discount,0)) / 100),
  120.  
  121.            --     (CASE
  122.            --         WHEN (U.is_min_price_disabled = 1)
  123.            --             THEN 0
  124.            --         ELSE
  125.            --             c.price_min
  126.  
  127.            --     END)
  128.            -- ) as discountprice,
  129.  
  130.            -- GREATEST(
  131.            --   MIN(
  132.            --     (CASE
  133.            --       WHEN (FixSale.fix_price IS NOT NULL) THEN
  134.            --         FixSale.fix_price
  135.            --       WHEN (Sale.value IS NOT NULL) THEN
  136.            --         c.Price * (100 - IFNULL(Sale.value,0)) / 100
  137.            --       WHEN (Fix.fix_price IS NOT NULL) THEN
  138.            --         Fix.fix_price
  139.            --       ELSE
  140.            --         c.Price * (100 - (IFNULL(Grp.value,0)+IFNULL(Itm.value,0)) - IFNULL(U.discount,0)) / 100
  141.            --     END)
  142.            --   ),
  143.            --   (CASE
  144.            --     WHEN (U.is_min_price_disabled = 1)
  145.            --       THEN 0
  146.            --     ELSE
  147.            --       c.price_min
  148.            --   END)
  149.            -- ) as discountprice,
  150.  
  151.            (CASE
  152.              WHEN
  153.                (FixSale.fix_price IS NOT NULL) OR
  154.                (Sale.value IS NOT NULL)
  155.              THEN
  156.                1
  157.              ELSE
  158.                0
  159.              END
  160.            ) as is_sale,
  161.  
  162.            (CASE
  163.              WHEN
  164.                Brkn.id IS NOT NULL
  165.              THEN
  166.                1
  167.              ELSE
  168.                0
  169.              END
  170.            ) as is_broken,
  171.  
  172.            (CASE
  173.              WHEN
  174.                Brkn.id IS NOT NULL
  175.              THEN
  176.                GROUP_CONCAT(DISTINCT CONCAT_WS('|', Brkn.count, Brkn.discount) ORDER BY Brkn.discount DESC SEPARATOR '||')
  177.              ELSE
  178.                NULL
  179.              END
  180.            ) as brokens
  181.  
  182.            FROM ($q_items_depots) c
  183.              LEFT JOIN users As U ON U.id = {$user_id}
  184.              LEFT JOIN users_saveditems usi ON U.id = usi.user_id AND c.id = usi.item_id
  185.              LEFT JOIN billing_discount AS Fix ON Fix.item_id = c.id AND Fix.user_id = U.id AND Fix.fix_price != '0'
  186.              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
  187.              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
  188.              LEFT JOIN billing_discount AS Grp ON Grp.id_group = c.id_group AND Grp.user_id = U.id AND Grp.fix_price = '0'
  189.              LEFT JOIN billing_discount AS Itm ON Itm.item_id = c.id AND Itm.user_id = U.id AND Itm.fix_price = '0'
  190.              LEFT JOIN catalog_broken AS Brkn ON U.depot_id = Brkn.depot_id AND Brkn.item_id = c.id
  191.        ";
  192.         $q .= "
  193.            GROUP BY c.id
  194.        ";
  195.         if ($filter != '' || $where != '') {
  196.             $q .= ' HAVING ' . $filter . " ";
  197.             if ($filter != '' && $where != '') {
  198.                 $q .= " AND ";
  199.             }
  200.             $q .= $where . " ";
  201.         }
  202.     } else {
  203.         $q = "SELECT c.*, c.Price as discountprice FROM ($q_items_depots) c";
  204.     }
  205.     if (!empty($oby)) {
  206.         $oby = " ORDER BY " . $oby;
  207.     } else {
  208.         $oby = " ";
  209.     }
  210.     // if (($goods = $this->get_mysql_fetch_assoc($q . $oby . $lim)) < 0) {
  211.     if (($goods = $this->get_mysql_fetch_assoc($q  . $oby . $lim)) < 0) {
  212.         $this->errors($goods);
  213.     }
  214.     $lim_count = false;
  215.     if (!empty($lim)) {
  216.         if (($goods_for_count = $this->get_mysql_fetch_assoc($q)) < 0) {
  217.             $this->errors($goods_for_count);
  218.         }
  219.         $lim_count = count($goods_for_count);
  220.         unset($goods_for_count);
  221.     }
  222.  
  223.     $resultGoodsIds = array();
  224.     if (is_array($goods)) {
  225.         foreach ($goods as $value) {
  226.             $resultGoodsIds[] = $value['id'];
  227.         }
  228.         if (($datatmp = $this->get_mysql_fetch_assoc("SELECT * FROM _deposits WHERE item_id IN (" . implode(', ', $resultGoodsIds) . ")")) < 0) {
  229.             $this->errors($datatmp);
  230.         }
  231.  
  232.         $deposit_array = array();
  233.         $hasLocalDeposit = array();
  234.         if (is_array($datatmp)) {
  235.             foreach ($datatmp as $row) {
  236.                 if (!isset($deposit_array[$row['item_id']])) {
  237.                     $deposit_array[$row['item_id']] = array();
  238.                 }
  239.                 $deposit_array[$row['item_id']][$row['storage_id']] = array(
  240.                         'count' => (int)$row['count'] + (int)$row['user_count'],
  241.                         'price' => (float)$row['price'],
  242.                         'free_count' => (int)$row['total_free_count'],
  243.                         'markup' => (float)number_format($row['markup'], 2),
  244.                         'days' => (int)$row['days'],
  245.                         'days_other' => (string)$row['days_other'],
  246.                         'days_comment' => (string)$row['days_comment'],
  247.                         'ship_date' => (string)$row['ship_date']
  248.                 );
  249.  
  250.                 if (in_array($row['storage_id'], $storagesIdsArr) && ((int)$row['count'] + (int)$row['user_count'] > 0)) {
  251.                     $hasLocalDeposit[$row['item_id']] = true;
  252.                 }
  253.             }
  254.         }
  255.         unset($datatmp);
  256.  
  257.         $q_item_info = "
  258.            SELECT
  259.                cinfo.id as id,
  260.                cinfo.picture_files,
  261.                cinfo.pair_id,
  262.                cinfo.header,
  263.                cinfo.content,
  264.                cinfo.sale20,
  265.                cinfo.article_code,
  266.                cinfo.article_exact,
  267.                cinfo.clone_key,
  268.                cinfo.variation_code,
  269.                binfo.header brand,
  270.                binfo.exclusive_sort brand_exclusive_sort
  271.            FROM _items i
  272.            JOIN catalog_item cinfo on i.id = cinfo.id
  273.            LEFT JOIN catalog_brand binfo ON cinfo.brand_id = binfo.id
  274.        ";
  275.         if (($infotmp = $this->get_mysql_fetch_assoc($q_item_info)) < 0) {
  276.             $this->errors($infotmp);
  277.         }
  278.         $items_info = array();
  279.         foreach ($infotmp as $irow) {
  280.             $items_info[$irow['id']] = $irow;
  281.         }
  282.         unset($infotmp);
  283.  
  284.         foreach ($goods as &$value) {
  285.             $value['deposit_json'] = json_encode($deposit_array[$value['id']]);
  286.             $value['has_local_deposit'] = isset($hasLocalDeposit[$value['id']]);
  287.             if (isset($items_info[$value['id']])) {
  288.                 $value = array_merge($value, $items_info[$value['id']]);
  289.             }
  290.         }
  291.         unset($deposit_array);
  292.         unset($items_info);
  293.     }
  294.  
  295.  
  296.     if ($withCount || $withGroups || $withBrands) {
  297.         if (!count($goods)) {
  298.             return array(
  299.                 'goods' => null,
  300.                 'count' => 0,
  301.                 'groups' => null,
  302.                 'brands' => null
  303.             );
  304.         }
  305.         $result = array();
  306.         $result['goods'] = $goods;
  307.         if ($withCount) {
  308.             if (!empty($lim)) {
  309.                 if ($lim_count) {
  310.                     $count = $lim_count;
  311.                 } else {
  312.                     $count = $this->get_mysql_fetch_assoc("SELECT COUNT(*) as count FROM _items i");
  313.                     $count = $count[0]['count'];
  314.                 }
  315.             } else {
  316.                 $count = $this->get_mysql_fetch_assoc("SELECT COUNT(*) as count FROM _items i WHERE id IN (" . implode(', ', $resultGoodsIds) . ")");
  317.                 $count = $count[0]['count'];
  318.                 // $count = count($result['goods']);
  319.             }
  320.             $result['count'] = $count;
  321.         }
  322.         if ($withGroups) {
  323.             $q = "SELECT
  324.                DISTINCT
  325.                cc.header,
  326.                cc.id
  327.                FROM _items i
  328.                LEFT JOIN catalog_category cc ON i.category_id=cc.id
  329.                WHERE
  330.                i.id IN (" . implode(', ', $resultGoodsIds) . ") AND
  331.                cc.header !=''
  332.            ";
  333.             $result['groups'] = $this->get_mysql_fetch_assoc($q);;
  334.         }
  335.         if ($withBrands) {
  336.             $q = "SELECT
  337.                DISTINCT
  338.                cb.header,
  339.                cb.id
  340.                FROM _items i
  341.                LEFT JOIN catalog_brand cb ON i.brand_id=cb.id
  342.                WHERE
  343.                i.id IN (" . implode(', ', $resultGoodsIds) . ") AND
  344.                cb.header !=''
  345.            ";
  346.             $result['brands'] = $this->get_mysql_fetch_assoc($q);;
  347.         }
  348.  
  349.         return $result;
  350.     }
  351.  
  352.     return $goods;
  353. }
  354.  
  355. function GetGoodsByBaseQuery($query, $oby = "   ", $lim = '', $where = "", $depot, $storages = array(), $filter = '', $withCount = false, $withGroups = false, $withBrands = false, $filterDeposit = false)
  356. {
  357.  
  358.     // if (($r = $this->get_mysql_change('SET SESSION group_concat_max_len = 5000000')) < 0) {
  359.     //     $this->errors($r);
  360.     // }
  361.     // $tm = microtime(true);
  362.     // echo 'func start:';
  363.     // print_r(memory_get_usage(true));
  364.     // echo ', time: ';
  365.     // var_dump(microtime() - $tm);
  366.     // echo('<br>');
  367.     if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items')) < 0) {
  368.         $this->errors($r);
  369.     }
  370.     if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items_storages')) < 0) {
  371.         $this->errors($r);
  372.     }
  373.     if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _deposits')) < 0) {
  374.         $this->errors($r);
  375.     }
  376.     // if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _catalog_deposit_storage')) < 0) {
  377.     //     $this->errors($r);
  378.     // }
  379.     if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items_depots')) < 0) {
  380.         $this->errors($r);
  381.     }
  382.     if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items_almost_final')) < 0) {
  383.         $this->errors($r);
  384.     }
  385.     if (($r = $this->get_mysql_change('DROP TEMPORARY TABLE IF EXISTS _items_final')) < 0) {
  386.         $this->errors($r);
  387.     }
  388.     // if (($r = $this->get_mysql_change('DROP TABLE IF EXISTS _items, _items_storages, _deposits, _items_depots, _items_almost_final, _items_final')) < 0) {
  389.     //     $this->errors($r);
  390.     // }
  391.  
  392.     $storagesIdsArr = array();
  393.     foreach ($storages as $value) {
  394.         $storagesIdsArr[] = $value['id'];
  395.     }
  396.     $storagesIdsInStr = implode(', ', $storagesIdsArr);
  397.  
  398.     // var_dump(microtime(true) - $tm);
  399.     $query = "CREATE TEMPORARY TABLE _items (INDEX(id)) " . $query;
  400.     if (($r = $this->get_mysql_change($query)) < 0) {
  401.         $this->errors($r);
  402.     }
  403.  
  404.  
  405.     $query = "CREATE TEMPORARY TABLE _items_storages SELECT I.*, cs.id as strgid, cs.depot_id as strgdepotid FROM _items I, catalog_storage cs
  406.        where cs.public = 1
  407.    ";
  408.     if (($r = $this->get_mysql_change($query)) < 0) {
  409.         $this->errors($r);
  410.     }
  411.  
  412.     // var_dump(microtime(true) - $tm);
  413.     // $q = "
  414.     //     CREATE TEMPORARY TABLE _catalog_deposit_storage (INDEX(item_id))
  415.     //     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,
  416.     //         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
  417.     //       RIGHT JOIN _items_storages itn ON itn.id = cds.item_id AND cds.storage_id = itn.strgid
  418.     //       LEFT JOIN catalog_deposit_free_storage fs ON itn.id = fs.item_id AND itn.strgid = fs.storage_id
  419.     //           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}
  420.     //           LEFT JOIN catalog_markup cm ON cm.fid_out = itn.strgdepotid AND cm.fid_in = {$depot}
  421.     // ";
  422.     // // $q = "
  423.     // //     CREATE TEMPORARY TABLE _catalog_deposit_storage (INDEX(item_id))
  424.     // //     SELECT cds1.* FROM catalog_deposit_storage cds1
  425.     // //     JOIN  (SELECT cds.item_id, cds.storage_id, MAX(cds.date) as maxdate FROM catalog_deposit_storage cds
  426.     // //       JOIN _items I ON I.id = cds.item_id
  427.     // //       WHERE cds.storage_id IN ({$storagesIdsInStr})
  428.     // //       GROUP BY cds.item_id, cds.storage_id) cds2
  429.     // //       ON cds1.item_id = cds2.item_id AND cds1.storage_id = cds2.storage_id AND cds1.date = cds2.maxdate
  430.     // // ";
  431.     // if (($r = $this->get_mysql_change($q)) < 0) {
  432.     //     $this->errors($r);
  433.     // }
  434.  
  435.     $user_id = (int)GetUserID();
  436.     $q = "CREATE TEMPORARY TABLE _deposits SELECT itn.id as item_id, itn.strgid as storage_id,
  437.            ifnull(cds.count, 0) as count,
  438.            cds.price as price,
  439.            ifnull(us.count, 0) as user_count,
  440.            ifnull(fs.count, 0) as free_count,
  441.            ifnull(osz.count, 0) as osz_count,
  442.            ifnull(ss.count, 0) as stock_count,
  443.            (GREATEST(ifnull(fs.count, 0), ifnull(osz.count, 0)) + ifnull(ss.count, 0)) as total_free_count,
  444.            IFNULL(cms.markup, IFNULL(cm.markup, 1)) as markup,
  445.            IFNULL(cms.days, IFNULL(cm.days, 0)) as days,
  446.            IFNULL(cms.days_other, IFNULL(cm.days_other, \"\")) as days_other,
  447.            IFNULL(cms.days_comment, IFNULL(cm.days_comment, \"\")) as days_comment,
  448.            IFNULL(cms.days_process, IFNULL(cm.days_process, \"\")) as days_process,
  449.            DATE_ADD(NOW(), INTERVAL IFNULL(cms.days, IFNULL(cm.days, 0)) DAY) as ship_date
  450.        FROM catalog_deposit_storage cds
  451.        RIGHT JOIN _items_storages itn ON itn.id = cds.item_id AND cds.storage_id = itn.strgid
  452.        LEFT JOIN catalog_deposit_free_storage fs ON itn.id = fs.item_id AND itn.strgid = fs.storage_id
  453.        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}
  454.        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}
  455.        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
  456.        LEFT JOIN catalog_markup cm ON cm.fid_out = itn.strgdepotid AND cm.fid_in = {$depot}
  457.        LEFT JOIN catalog_markup_storage cms ON itn.strgid = cms.storage_id AND cm.fid_in = {$depot}
  458.    ORDER BY cm.days ASC, cms.days ASC, markup ASC";
  459.     if (($r = $this->get_mysql_change($q)) < 0) {
  460.         $this->errors($r);
  461.     }
  462.     // echo 'before select _deposits:';
  463.     // print_r(memory_get_usage(true));
  464.     // echo ', time: ';
  465.     // var_dump(microtime() - $tm);
  466.     // echo('<br>');
  467.     // if (($datatmp = $this->get_mysql_fetch_assoc("SELECT * FROM _deposits")) < 0) {
  468.     //     $this->errors($r);
  469.     // }
  470.     // echo 'after select _deposits:';
  471.     // print_r(memory_get_usage(true));
  472.     // echo ', time: ';
  473.     // var_dump(microtime() - $tm);
  474.     // echo('<br>');
  475.     // $deposit_array = array();
  476.     // if (is_array($datatmp)) {
  477.     //     foreach ($datatmp as $row) {
  478.     //         if (!isset($deposit_array[$row['item_id']])) {
  479.     //             $deposit_array[$row['item_id']] = array();
  480.     //         }
  481.     //         $deposit_array[$row['item_id']][$row['storage_id']] = array(
  482.     //                 'count' => (int)$row['count'] + (int)$row['user_count'],
  483.     //                 'price' => (float)$row['price'],
  484.     //                 'free_count' => (int)$row['total_free_count'],
  485.     //                 'markup' => (float)number_format($row['markup'], 2),
  486.     //                 'days' => (int)$row['days'],
  487.     //                 'days_other' => (string)$row['days_other'],
  488.     //                 'days_comment' => (string)$row['days_comment'],
  489.     //                 'ship_date' => (string)$row['ship_date']
  490.     //         );
  491.     //     }
  492.     // }
  493.     // echo 'after rearray:';
  494.     // print_r(memory_get_usage(true));
  495.     // echo ', time: ';
  496.     // var_dump(microtime() - $tm);
  497.     // echo('<br>');
  498.     // if (($rcount = $this->get_mysql_fetch_assoc("SELECT count(*) as rcount FROM _deposits")) < 0) {
  499.     //     $this->errors($rcount);
  500.     // }
  501.     // $rcount = (int)$rcount[0]['rcount'];
  502.     // $rcountoffset = 0;
  503.     // $rcountstep = 10000;
  504.     // $deposit_array = array();
  505.     // while ($rcountoffset < $rcount) {
  506.     //     if (($datatmp = $this->get_mysql_fetch_assoc("SELECT * FROM _deposits LIMIT {$rcountoffset}, {$rcountstep}")) < 0) {
  507.     //         $this->errors($datatmp);
  508.     //     }
  509.     //     if (is_array($datatmp)) {
  510.     //         foreach ($datatmp as $row) {
  511.     //             if (!isset($deposit_array[$row['item_id']])) {
  512.     //                 $deposit_array[$row['item_id']] = array();
  513.     //             }
  514.     //             $deposit_array[$row['item_id']][$row['storage_id']] = array(
  515.     //                     'count' => (int)$row['count'] + (int)$row['user_count'],
  516.     //                     'price' => (float)$row['price'],
  517.     //                     'free_count' => (int)$row['total_free_count'],
  518.     //                     'markup' => (float)number_format($row['markup'], 2),
  519.     //                     'days' => (int)$row['days'],
  520.     //                     'days_other' => (string)$row['days_other'],
  521.     //                     'days_comment' => (string)$row['days_comment'],
  522.     //                     'ship_date' => (string)$row['ship_date']
  523.     //             );
  524.     //         }
  525.     //     }
  526.     //     $rcountoffset += $rcountstep;
  527.     // }
  528.  
  529.     // echo '<pre>';
  530.     // print_r($deposit_array);
  531.     // echo '</pre>';
  532.  
  533.     // $q = "ALTER TABLE _catalog_deposit_storage ADD INDEX `item_id` (`item_id`)";
  534.     // if (($r = $this->get_mysql_change($q)) < 0) {
  535.     //     $this->errors($r);
  536.     // }
  537.  
  538.     // var_dump(microtime(true) - $tm);
  539.     //    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,
  540.     // $q = "
  541.     //     CREATE TEMPORARY TABLE _items_depots AS
  542.     //     SELECT I.*,
  543.     //     (datediff(dt.request_gmtdate,NOW()) > 30 OR dt.request_gmtdate IS NULL) as show_other_depot,
  544.     //     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,
  545.     // ";
  546.     $q = "
  547.        CREATE TEMPORARY TABLE _items_depots AS
  548.        SELECT I.*,
  549.        (datediff(dt.request_gmtdate,NOW()) > 30 OR dt.request_gmtdate IS NULL) as show_other_depot,
  550.    ";
  551.     // $tmpcase = "CASE WHEN (D.storage_id = " . implode(" or D.storage_id = ", $storagesIdsArr) . ") THEN D.count END";
  552.     // foreach ($storagesIdsArr as $value) {
  553.     //     $q .= "IFNULL(SUM(CASE WHEN (D.storage_id = {$value}) THEN D.count END),0) AS depot_storage_{$value},";
  554.     // }
  555.     $q .= "
  556.        dt.price_value AS Price,
  557.        dt.price_min as price_min,
  558.         IFNULL(SUM(D.count),0) AS deposit,
  559.         dt.max_show,
  560.         dt.package_count,
  561.         IFNULL(dt.request_count,0) as req_count,
  562.         dt.request_gmtdate as req_date,
  563.         SUM(IFNULL(ds.count,0)) as reserve_sum
  564.        FROM _items AS I
  565.    ";
  566.     if ($filterDeposit == 2) {
  567.         $q .= "JOIN catalog_deposit_storage AS D ON D.item_id = I.id and D.storage_id in (". implode(', ', $storagesIdsArr) .")";
  568.     } else {
  569.         $q .= "LEFT JOIN catalog_deposit_storage AS D ON D.item_id = I.id and D.storage_id in (". implode(', ', $storagesIdsArr) .")";
  570.     }
  571.     if ($filterDeposit == 1) {
  572.         $q .= "JOIN (SELECT
  573.            item_id,
  574.            SUM(CASE WHEN storage_id in (". implode(', ', $storagesIdsArr) .") THEN `count` ELSE 0 END)
  575.             + SUM(CASE WHEN storage_id in (". implode(', ', $storagesIdsArr) .") THEN `user_count` ELSE 0 END)
  576.             + SUM(CASE WHEN storage_id not in (". implode(', ', $storagesIdsArr) .") THEN `total_free_count` ELSE 0 END) as depositall
  577.            from _deposits
  578.            GROUP BY item_id
  579.            HAVING depositall > 0) dall ON I.id = dall.item_id
  580.        ";
  581.     }
  582.     $q .= "JOIN catalog_deposit AS dt ON I.id = dt.item_id AND dt.depot_id = {$depot}
  583.           LEFT JOIN catalog_deposit_stock ds ON I.id = ds.item_id AND dt.depot_id = ds.depot_id
  584.        GROUP BY I.id,
  585.         I.header,
  586.         I.id_group,
  587.         I.content,
  588.         I.article_code,
  589.         I.variation_code,
  590.         I.public
  591.    ";
  592.     if (($r = $this->get_mysql_change($q)) < 0) {
  593.         $this->errors($r);
  594.     }
  595.  
  596.     // var_dump(microtime(true) - $tm);
  597.     $q = "
  598.        CREATE TEMPORARY TABLE _items_almost_final as
  599.        SELECT I.*,
  600.            (CASE
  601.                WHEN (cm.fid_in IS NOT NULL) THEN
  602.                    SUM(IFNULL(cd.count_other,0))+IFNULL(I.req_count,0)+IFNULL(I.reserve_sum,0)
  603.                ELSE
  604.                    IFNULL(I.req_count,0)
  605.            END) AS show_wagon,
  606.            (CASE
  607.                WHEN (cm.fid_in IS NOT NULL) THEN
  608.                    SUM(IFNULL(cd.count_other,0))
  609.                ELSE
  610.                    0
  611.            END) AS sum_count_other
  612.  
  613.            FROM _items_depots I
  614.            LEFT JOIN catalog_deposit cd ON I.id = cd.item_id AND cd.depot_id <> {$depot}
  615.            LEFT JOIN catalog_markup cm ON {$depot} = cm.fid_in
  616.            GROUP BY I.id
  617.    ";
  618.     if (($r = $this->get_mysql_change($q)) < 0) {
  619.         $this->errors($r);
  620.     }
  621.     if ($userId = GetUserID()) {
  622.         $q = "
  623.            CREATE TEMPORARY TABLE _items_final as
  624.            SELECT
  625.            c.*,
  626.            (CASE WHEN usi.item_id IS NULL
  627.                THEN 0
  628.                ELSE 1
  629.            END) as iswish,
  630.  
  631.            GREATEST(
  632.                COALESCE(
  633.                    Fix.fix_price,
  634.                    LEAST(
  635.                        c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100,
  636.                        COALESCE(FixSale.fix_price, c.Price)
  637.                    )
  638.                ) * ((100 - IFNULL(U.discount,0)) / 100),
  639.                (IF(U.is_min_price_disabled = 1, 0, c.price_min))
  640.            ) as discountprice,
  641.  
  642.            -- GREATEST(
  643.            --     (CASE
  644.            --         WHEN (Fix.fix_price IS NOT NULL) THEN
  645.            --             Fix.fix_price
  646.            --         WHEN (FixSale.fix_price IS NOT NULL) THEN
  647.            --             LEAST(
  648.            --                 c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100,
  649.            --                 FixSale.fix_price
  650.            --             )
  651.            --         ELSE
  652.            --             c.Price * (100 - GREATEST(IFNULL(Sale.value,0), IFNULL(Grp.value,0)+IFNULL(Itm.value,0))) / 100
  653.            --     END) * ((100 - IFNULL(U.discount,0)) / 100),
  654.  
  655.            --     (CASE
  656.            --         WHEN (U.is_min_price_disabled = 1)
  657.            --             THEN 0
  658.            --         ELSE
  659.            --             c.price_min
  660.  
  661.            --     END)
  662.            -- ) as discountprice,
  663.  
  664.            -- GREATEST(
  665.            --   MIN(
  666.            --     (CASE
  667.            --       WHEN (FixSale.fix_price IS NOT NULL) THEN
  668.            --         FixSale.fix_price
  669.            --       WHEN (Sale.value IS NOT NULL) THEN
  670.            --         c.Price * (100 - IFNULL(Sale.value,0)) / 100
  671.            --       WHEN (Fix.fix_price IS NOT NULL) THEN
  672.            --         Fix.fix_price
  673.            --       ELSE
  674.            --         c.Price * (100 - (IFNULL(Grp.value,0)+IFNULL(Itm.value,0)) - IFNULL(U.discount,0)) / 100
  675.            --     END)
  676.            --   ),
  677.            --   (CASE
  678.            --     WHEN (U.is_min_price_disabled = 1)
  679.            --       THEN 0
  680.            --     ELSE
  681.            --       c.price_min
  682.            --   END)
  683.            -- ) as discountprice,
  684.  
  685.            (CASE
  686.              WHEN
  687.                (FixSale.fix_price IS NOT NULL) OR
  688.                (Sale.value IS NOT NULL)
  689.              THEN
  690.                1
  691.              ELSE
  692.                0
  693.              END
  694.            ) as is_sale,
  695.  
  696.            (CASE
  697.              WHEN
  698.                Brkn.id IS NOT NULL
  699.              THEN
  700.                1
  701.              ELSE
  702.                0
  703.              END
  704.            ) as is_broken,
  705.  
  706.            (CASE
  707.              WHEN
  708.                Brkn.id IS NOT NULL
  709.              THEN
  710.                GROUP_CONCAT(DISTINCT CONCAT_WS('|', Brkn.count, Brkn.discount) ORDER BY Brkn.discount DESC SEPARATOR '||')
  711.              ELSE
  712.                NULL
  713.              END
  714.            ) as brokens
  715.  
  716.            FROM _items_almost_final AS c
  717.              LEFT JOIN users As U ON U.id = {$userId}
  718.              LEFT JOIN users_saveditems usi ON U.id = usi.user_id AND c.id = usi.item_id
  719.              LEFT JOIN billing_discount AS Fix ON Fix.item_id = c.id AND Fix.user_id = U.id AND Fix.fix_price != '0'
  720.              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
  721.              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
  722.              LEFT JOIN billing_discount AS Grp ON Grp.id_group = c.id_group AND Grp.user_id = U.id AND Grp.fix_price = '0'
  723.              LEFT JOIN billing_discount AS Itm ON Itm.item_id = c.id AND Itm.user_id = U.id AND Itm.fix_price = '0'
  724.              LEFT JOIN catalog_broken AS Brkn ON U.depot_id = Brkn.depot_id AND Brkn.item_id = c.id
  725.  
  726.            GROUP BY c.id
  727.        ";
  728.         if (($r = $this->get_mysql_change($q)) < 0) {
  729.             $this->errors($r);
  730.         }
  731.  
  732.         $q = "SELECT * FROM _items_final c ";
  733.         if ($filter != '' || $where != '') {
  734.             $q .= ' WHERE ' . $filter . " ";
  735.             if ($filter != '' && $where != '') {
  736.                 $q .= " AND ";
  737.             }
  738.             $q .= $where . " ";
  739.         }
  740.         $lastTable = '_items_final';
  741.     } else {
  742.         $q = "SELECT *, Price as discountprice FROM _items_almost_final c ";
  743.         if ($where != '') {
  744.             $q .= ' WHERE ' . $where . " ";
  745.         }
  746.         $lastTable = '_items_almost_final';
  747.     }
  748.     // var_dump(microtime(true) - $tm);
  749.  
  750.     // var_dump(microtime(true) - $tm);
  751.     $goods=array();
  752.  
  753.     if (!empty($oby)) {
  754.         $oby = "ORDER BY " . $oby;
  755.     } else {
  756.         $oby = " ";
  757.     }
  758.     if (($goods = $this->get_mysql_fetch_assoc($q . $oby . $lim)) < 0) {
  759.         $this->errors($goods);
  760.     } else {
  761.         if (array_search((int)$depot, $this->blockOrderToDepots) !== false) {
  762.             foreach ($goods as &$value) {
  763.                 $value['show_wagon'] = 0;
  764.             }
  765.         }
  766.         if (is_array($goods)) {
  767.             $resultGoodsIds = array();
  768.             foreach ($goods as $value) {
  769.                 $resultGoodsIds[] = $value['id'];
  770.             }
  771.             if (($datatmp = $this->get_mysql_fetch_assoc("SELECT * FROM _deposits WHERE item_id IN (" . implode(', ', $resultGoodsIds) . ")")) < 0) {
  772.                 $this->errors($r);
  773.             }
  774.             $deposit_array = array();
  775.             $hasLocalDeposit = array();
  776.             if (is_array($datatmp)) {
  777.                 foreach ($datatmp as $row) {
  778.                     if (!isset($deposit_array[$row['item_id']])) {
  779.                         $deposit_array[$row['item_id']] = array();
  780.                     }
  781.                     $deposit_array[$row['item_id']][$row['storage_id']] = array(
  782.                             'count' => (int)$row['count'] + (int)$row['user_count'],
  783.                             'price' => (float)$row['price'],
  784.                             'free_count' => (int)$row['total_free_count'],
  785.                             'markup' => (float)number_format($row['markup'], 2),
  786.                             'days' => (int)$row['days'],
  787.                             'days_other' => (string)$row['days_other'],
  788.                             'days_comment' => (string)$row['days_comment'],
  789.                             'ship_date' => (string)$row['ship_date']
  790.                     );
  791.  
  792.                     if (in_array($row['storage_id'], $storagesIdsArr) && ((int)$row['count'] + (int)$row['user_count'] > 0)) {
  793.                         $hasLocalDeposit[$row['item_id']] = true;
  794.                     }
  795.                 }
  796.             }
  797.             // echo 'before unset:';
  798.             // print_r(memory_get_usage(true));
  799.             // echo ', time: ';
  800.             // var_dump(microtime() - $tm);
  801.             // echo('<br>');
  802.             unset($datatmp);
  803.             foreach ($goods as &$value) {
  804.                 $value['deposit_json'] = json_encode($deposit_array[$value['id']]);
  805.                 $value['has_local_deposit'] = isset($hasLocalDeposit[$value['id']]);
  806.             }
  807.             unset($deposit_array);
  808.         }
  809.         if ($withCount || $withGroups || $withBrands) {
  810.             $result = array();
  811.             $result['goods'] = $goods;
  812.             if ($withCount) {
  813.                 $count = $this->get_mysql_fetch_assoc("SELECT COUNT(*) as count FROM (" . $q . ") as items");
  814.                 $count = $count[0]['count'];
  815.                 $result['count'] = $count;
  816.             }
  817.             if ($withGroups) {
  818.                 $q = "SELECT
  819.                    DISTINCT
  820.                    cc.header,
  821.                    cc.id
  822.                    FROM {$lastTable} c
  823.                    LEFT JOIN catalog_category cc ON c.category_id=cc.id
  824.                    WHERE
  825.                    cc.header !=''
  826.                ";
  827.                 $result['groups'] = $this->get_mysql_fetch_assoc($q);;
  828.             }
  829.             if ($withBrands) {
  830.                 $q = "SELECT
  831.                    DISTINCT
  832.                    cb.header,
  833.                    cb.id
  834.                    FROM {$lastTable} c
  835.                    LEFT JOIN catalog_brand cb ON c.brand_id=cb.id
  836.                    WHERE
  837.                    cb.header !=''
  838.                ";
  839.                 $result['brands'] = $this->get_mysql_fetch_assoc($q);;
  840.             }
  841.             // echo 'after all:';
  842.             // print_r(memory_get_usage(true));
  843.             // echo ', time: ';
  844.             // var_dump(microtime() - $tm);
  845.             // echo('<br>');
  846.             return $result;
  847.         }
  848.  
  849.         return $goods;
  850.     }
  851. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top