· 8 years ago · Mar 22, 2018, 11:44 AM
1function 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
355function 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}