· 9 years ago · Nov 25, 2016, 05:14 AM
1<?php
2
3function GetGoodsByBaseQuery($query, $oby = " ", $lim = '', $where = "", $depot, $storages = array(), $filter = '', $withCount = false, $withGroups = false, $withBrands = false, $filterDeposit = false)
4{
5 if (($r = $this->get_mysql_change('DROP TABLE IF EXISTS _items')) < 0) {
6 $this->errors($r);
7 }
8 if (($r = $this->get_mysql_change('DROP TABLE IF EXISTS _items_storages')) < 0) {
9 $this->errors($r);
10 }
11 if (($r = $this->get_mysql_change('DROP TABLE IF EXISTS _deposits')) < 0) {
12 $this->errors($r);
13 }
14 if (($r = $this->get_mysql_change('DROP TABLE IF EXISTS _items_depots')) < 0) {
15 $this->errors($r);
16 }
17 if (($r = $this->get_mysql_change('DROP TABLE IF EXISTS _items_almost_final')) < 0) {
18 $this->errors($r);
19 }
20 if (($r = $this->get_mysql_change('DROP TABLE IF EXISTS _items_final')) < 0) {
21 $this->errors($r);
22 }
23
24 $storagesIdsArr = array();
25 foreach ($storages as $value) {
26 $storagesIdsArr[] = $value['id'];
27 }
28 $storagesIdsInStr = implode(', ', $storagesIdsArr);
29
30 $query = "CREATE TEMPORARY TABLE _items (INDEX(id))" . $query;
31 if (($r = $this->get_mysql_change($query)) < 0) {
32 $this->errors($r);
33 }
34
35
36 $query = "CREATE TEMPORARY TABLE _items_storages SELECT I.*, cs.id as strgid, cs.depot_id as strgdepotid FROM _items I, catalog_storage cs
37 where cs.public = 1
38 ";
39 if (($r = $this->get_mysql_change($query)) < 0) {
40 $this->errors($r);
41 }
42
43 $user_id = (int)GetUserID();
44 $q = "CREATE TEMPORARY TABLE _deposits SELECT itn.id as item_id, itn.strgid as storage_id,
45 ifnull(cds.count, 0) as count,
46 cds.price as price,
47 ifnull(us.count, 0) as user_count,
48 ifnull(fs.count, 0) as free_count,
49 ifnull(osz.count, 0) as osz_count,
50 ifnull(ss.count, 0) as stock_count,
51 (GREATEST(ifnull(fs.count, 0), ifnull(osz.count, 0)) + ifnull(ss.count, 0)) as total_free_count,
52 IFNULL(cms.markup, IFNULL(cm.markup, 1)) as markup,
53 IFNULL(cms.days, IFNULL(cm.days, 0)) as days,
54 IFNULL(cms.days_other, IFNULL(cm.days_other, \"\")) as days_other,
55 IFNULL(cms.days_comment, IFNULL(cm.days_comment, \"\")) as days_comment,
56 IFNULL(cms.days_process, IFNULL(cm.days_process, \"\")) as days_process,
57 DATE_ADD(NOW(), INTERVAL IFNULL(cms.days, IFNULL(cm.days, 0)) DAY) as ship_date
58 FROM catalog_deposit_storage cds
59 RIGHT JOIN _items_storages itn ON itn.id = cds.item_id AND cds.storage_id = itn.strgid
60 LEFT JOIN catalog_deposit_free_storage fs ON itn.id = fs.item_id AND itn.strgid = fs.storage_id
61 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}
62 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}
63 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
64 LEFT JOIN catalog_markup cm ON cm.fid_out = itn.strgdepotid AND cm.fid_in = {$depot}
65 LEFT JOIN catalog_markup_storage cms ON itn.strgid = cms.storage_id AND cm.fid_in = {$depot}
66 ORDER BY cm.days ASC, cms.days ASC";
67 if (($r = $this->get_mysql_change($q)) < 0) {
68 $this->errors($r);
69 }
70 $q = "
71 CREATE TEMPORARY TABLE _items_depots AS
72 SELECT I.*,
73 (datediff(dt.request_gmtdate,NOW()) > 30 OR dt.request_gmtdate IS NULL) as show_other_depot,
74 ";
75 // $tmpcase = "CASE WHEN (D.storage_id = " . implode(" or D.storage_id = ", $storagesIdsArr) . ") THEN D.count END";
76 // foreach ($storagesIdsArr as $value) {
77 // $q .= "IFNULL(SUM(CASE WHEN (D.storage_id = {$value}) THEN D.count END),0) AS depot_storage_{$value},";
78 // }
79 $q .= "
80 dt.price_value AS Price,
81 dt.price_min as price_min,
82 IFNULL(SUM(D.count),0) AS deposit,
83 dt.max_show,
84 dt.package_count,
85 IFNULL(dt.request_count,0) as req_count,
86 dt.request_gmtdate as req_date,
87 SUM(IFNULL(ds.count,0)) as reserve_sum
88 FROM _items AS I
89 ";
90 if ($filterDeposit == 2) {
91 $q .= "JOIN catalog_deposit_storage AS D ON D.item_id = I.id and D.storage_id in (". implode(', ', $storagesIdsArr) .")";
92 } else {
93 $q .= "LEFT JOIN catalog_deposit_storage AS D ON D.item_id = I.id and D.storage_id in (". implode(', ', $storagesIdsArr) .")";
94 }
95 if ($filterDeposit == 1) {
96 $q .= "JOIN (SELECT
97 item_id,
98 SUM(CASE WHEN storage_id in (". implode(', ', $storagesIdsArr) .") THEN `count` ELSE 0 END)
99 + SUM(CASE WHEN storage_id in (". implode(', ', $storagesIdsArr) .") THEN `user_count` ELSE 0 END)
100 + SUM(CASE WHEN storage_id not in (". implode(', ', $storagesIdsArr) .") THEN `total_free_count` ELSE 0 END) as depositall
101 from _deposits
102 GROUP BY item_id
103 HAVING depositall > 0) dall ON I.id = dall.item_id
104 ";
105 }
106 $q .= "JOIN catalog_deposit AS dt ON I.id = dt.item_id AND dt.depot_id = {$depot}
107 LEFT JOIN catalog_deposit_stock ds ON I.id = ds.item_id AND dt.depot_id = ds.depot_id
108 GROUP BY I.id,
109 I.header,
110 I.id_group,
111 I.content,
112 I.article_code,
113 I.variation_code,
114 I.public
115 ";
116 if (($r = $this->get_mysql_change($q)) < 0) {
117 $this->errors($r);
118 }
119
120 // var_dump(microtime(true) - $tm);
121 $q = "
122 CREATE TEMPORARY TABLE _items_almost_final as
123 SELECT I.*,
124 (CASE
125 WHEN (cm.fid_in IS NOT NULL) THEN
126 SUM(IFNULL(cd.count_other,0))+IFNULL(I.req_count,0)+IFNULL(I.reserve_sum,0)
127 ELSE
128 IFNULL(I.req_count,0)
129 END) AS show_wagon,
130 (CASE
131 WHEN (cm.fid_in IS NOT NULL) THEN
132 SUM(IFNULL(cd.count_other,0))
133 ELSE
134 0
135 END) AS sum_count_other
136
137 FROM _items_depots I
138 LEFT JOIN catalog_deposit cd ON I.id = cd.item_id AND cd.depot_id <> {$depot}
139 LEFT JOIN catalog_markup cm ON {$depot} = cm.fid_in
140 GROUP BY I.id
141 ";
142 if (($r = $this->get_mysql_change($q)) < 0) {
143 $this->errors($r);
144 }
145 if ($userId = GetUserID()) {
146 $q = "
147 CREATE TEMPORARY TABLE _items_final as
148 SELECT
149 c.*,
150 (CASE WHEN usi.item_id IS NULL
151 THEN 0
152 ELSE 1
153 END) as iswish,
154 GREATEST(MIN(
155 (CASE
156 WHEN (FixSale.fix_price IS NOT NULL) THEN
157 FixSale.fix_price
158 WHEN (Sale.value IS NOT NULL) THEN
159 c.Price * (100 - IFNULL(Sale.value,0)) / 100
160 WHEN (Fix.fix_price IS NOT NULL) THEN
161 Fix.fix_price
162 ELSE
163 c.Price * (100 - (IFNULL(Grp.value,0)+IFNULL(Itm.value,0)) - IFNULL(U.discount,0)) / 100
164 END)
165 ), c.price_min) as discountprice,
166
167 (CASE
168 WHEN
169 (FixSale.fix_price IS NOT NULL) OR
170 (Sale.value IS NOT NULL)
171 THEN
172 1
173 ELSE
174 0
175 END
176 ) as is_sale,
177
178 (CASE
179 WHEN
180 Brkn.id IS NOT NULL
181 THEN
182 1
183 ELSE
184 0
185 END
186 ) as is_broken,
187
188 (CASE
189 WHEN
190 Brkn.id IS NOT NULL
191 THEN
192 GROUP_CONCAT(DISTINCT CONCAT_WS('|', Brkn.count, Brkn.discount) ORDER BY Brkn.discount DESC SEPARATOR '||')
193 ELSE
194 NULL
195 END
196 ) as brokens
197
198 FROM _items_almost_final AS c
199 LEFT JOIN users As U ON U.id = {$userId}
200 LEFT JOIN users_saveditems usi ON U.id = usi.user_id AND c.id = usi.item_id
201 LEFT JOIN billing_discount AS Fix ON Fix.item_id = c.id AND Fix.user_id = U.id AND Fix.fix_price != '0'
202 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
203 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
204 LEFT JOIN billing_discount AS Grp ON Grp.id_group = c.id_group AND Grp.user_id = U.id AND Grp.fix_price = '0'
205 LEFT JOIN billing_discount AS Itm ON Itm.item_id = c.id AND Itm.user_id = U.id AND Itm.fix_price = '0'
206 LEFT JOIN catalog_broken AS Brkn ON U.depot_id = Brkn.depot_id AND Brkn.item_id = c.id
207
208 GROUP BY c.id
209 ";
210 if (($r = $this->get_mysql_change($q)) < 0) {
211 $this->errors($r);
212 }
213
214 $q = "SELECT * FROM _items_final c ";
215 if ($filter != '' || $where != '') {
216 $q .= ' WHERE ' . $filter . " ";
217 if ($filter != '' && $where != '') {
218 $q .= " AND ";
219 }
220 $q .= $where . " ";
221 }
222 $lastTable = '_items_final';
223 } else {
224 $q = "SELECT *, Price as discountprice FROM _items_almost_final c ";
225 if ($where != '') {
226 $q .= ' WHERE ' . $where . " ";
227 }
228 $lastTable = '_items_almost_final';
229 }
230
231 $goods=array();
232
233 if (!empty($oby)) {
234 $oby = "ORDER BY " . $oby;
235 } else {
236 $oby = " ";
237 }
238 if (($goods = $this->get_mysql_fetch_assoc($q . $oby . $lim)) < 0) {
239 $this->errors($goods);
240 } else {
241 if (array_search((int)$depot, $this->blockOrderToDepots) !== false) {
242 foreach ($goods as &$value) {
243 $value['show_wagon'] = 0;
244 }
245 }
246 if (is_array($goods)) {
247 $resultGoodsIds = array();
248 foreach ($goods as $value) {
249 $resultGoodsIds[] = $value['id'];
250 }
251 if (($datatmp = $this->get_mysql_fetch_assoc("SELECT * FROM _deposits WHERE item_id IN (" . implode(', ', $resultGoodsIds) . ")")) < 0) {
252 $this->errors($r);
253 }
254 $deposit_array = array();
255 $hasLocalDeposit = array();
256 if (is_array($datatmp)) {
257 foreach ($datatmp as $row) {
258 if (!isset($deposit_array[$row['item_id']])) {
259 $deposit_array[$row['item_id']] = array();
260 }
261 $deposit_array[$row['item_id']][$row['storage_id']] = array(
262 'count' => (int)$row['count'] + (int)$row['user_count'],
263 'price' => (float)$row['price'],
264 'free_count' => (int)$row['total_free_count'],
265 'markup' => (float)number_format($row['markup'], 2),
266 'days' => (int)$row['days'],
267 'days_other' => (string)$row['days_other'],
268 'days_comment' => (string)$row['days_comment'],
269 'ship_date' => (string)$row['ship_date']
270 );
271
272 if (in_array($row['storage_id'], $storagesIdsArr) && ((int)$row['count'] + (int)$row['user_count'] > 0)) {
273 $hasLocalDeposit[$row['item_id']] = true;
274 }
275 }
276 }
277 unset($datatmp);
278 foreach ($goods as &$value) {
279 $value['deposit_json'] = json_encode($deposit_array[$value['id']]);
280 $value['has_local_deposit'] = isset($hasLocalDeposit[$value['id']]);
281 }
282 unset($deposit_array);
283 }
284 if ($withCount || $withGroups || $withBrands) {
285 $result = array();
286 $result['goods'] = $goods;
287 if ($withCount) {
288 $count = $this->get_mysql_fetch_assoc("SELECT COUNT(*) as count FROM (" . $q . ") as items");
289 $count = $count[0]['count'];
290 $result['count'] = $count;
291 }
292 if ($withGroups) {
293 $q = "SELECT
294 DISTINCT
295 cc.header,
296 cc.id
297 FROM {$lastTable} c
298 LEFT JOIN catalog_category cc ON c.category_id=cc.id
299 WHERE
300 cc.header !=''
301 ";
302 $result['groups'] = $this->get_mysql_fetch_assoc($q);;
303 }
304 if ($withBrands) {
305 $q = "SELECT
306 DISTINCT
307 cb.header,
308 cb.id
309 FROM {$lastTable} c
310 LEFT JOIN catalog_brand cb ON c.brand_id=cb.id
311 WHERE
312 cb.header !=''
313 ";
314 $result['brands'] = $this->get_mysql_fetch_assoc($q);;
315 }
316 return $result;
317 }
318
319 return $goods;
320 }
321}