· 6 years ago · Jul 19, 2019, 08:22 AM
1 Command: Query
2 Time: 1573
3 State: Sending data
4 Info: CREATE TEMPORARY TABLE IF NOT EXISTS temp_tt ENGINE=MEMORY AS (
5 SELECT
6 ag.id AS id,
7 MAX(ag.prop_template_id) AS proptplID,
8 MAX(ag.prop_goods_id) AS prop_goodid,
9 sp.ID AS spID,
10
11 MAX(ag.brandid) AS brandid,
12 NULL AS bcid,
13 ag.barcode AS barcode,
14 MAX(ag.categoryid) AS catid,
15 MAX(ag.is_warr_disc) AS is_warr_disc,
16 MAX(ag.original_goodid) AS original_goodid,
17
18 IF (
19
20 (( rem.novator_remains > rem.novator_reserve) OR
21 ( rem.mitino_remains > rem.mitino_reserve) OR
22 ( rem.novokuz_remains > rem.novokuz_reserve) OR
23 ( rem.polej_remains > rem.polej_reserve) OR
24 ( rem.kondr_remains > rem.kondr_reserve)
25 ), 1,
26 IF (
27 rem.supp_quantity > 0, 7,
28 IF (
29 ( rem.msk_arrival_date IS NOT NULL ), 3,
30 IF (
31 (
32 ( rem.kondr_availability NOT BETWEEN 1 AND 6 AND
33 rem.kondr_reserve > 0 AND rem.kondr_reserve_date > NOW()
34 ) OR
35 ( rem.novator_availability NOT BETWEEN 1 AND 6 AND
36 rem.novator_reserve > 0 AND rem.novator_reserve_date > NOW()
37 ) OR
38 ( rem.mitino_availability NOT BETWEEN 1 AND 6 AND
39 rem.mitino_reserve > 0 AND rem.mitino_reserve_date > NOW()
40 )
41 ), 4, 0
42 )
43 )
44 )
45 ) AS main_status,IF (( rem.novator_availability BETWEEN 1 AND 6 ) , 1, 0) as novator_status,
46 IF (( rem.mitino_availability BETWEEN 1 AND 6 ) , 1, 0) as mitino_status,
47 IF (( rem.novokuz_availability BETWEEN 1 AND 6 ) , 1, 0) as novokuz_status,
48 IF (( rem.polej_availability BETWEEN 1 AND 6 ) , 1, 0) as polej_status,
49 IF (( rem.kondr_availability BETWEEN 1 AND 6 ) , 1, 0) as kondr_status,
50 (rem.novator_remains-rem.novator_reserve) AS novator_remains,
51 (rem.mitino_remains-rem.mitino_reserve) AS mitino_remains,
52 (rem.novokuz_remains-rem.novokuz_reserve) AS novokuz_remains,
53 (rem.polej_remains-rem.polej_reserve) AS polej_remains,
54 (rem.kondr_remains-rem.kondr_reserve) AS main_remains,
55 rem.novator_suborder AS novator_suborder,
56 rem.mitino_suborder AS mitino_suborder,
57 rem.novokuz_suborder AS novokuz_suborder,
58 rem.polej_suborder AS polej_suborder,
59 rem.kondr_suborder AS kondr_suborder,
60 rem.novator_reserve_date AS novator_reserve_date,
61 rem.mitino_reserve_date AS mitino_reserve_date,
62 rem.novokuz_reserve_date AS novokuz_reserve_date,
63 rem.polej_reserve_date AS polej_reserve_date,
64 rem.kondr_reserve_date AS main_reserve_date,
65
66 rem.msk_arrival_date,
67 rem.supp_days,
68 gp.retail_price as retail_price,
69 gp.bonus_price,
70 gp.small_whole_sale_price,
71 gp.medium_whole_sale_price,
72 CASE WHEN gp.backup_retail_price_date >= DATE_SUB(NOW(), INTERVAL 10 DAY)
73 THEN gp.prev_retail_price ELSE NULL END AS prev_retail_price,
74 ag.name,
75 gp.backup_retail_price_date AS prev_changes,
76 ag.part_no,
77 ag.buytext,
78 ag.title,
79 ag.foto_goodID,
80 ag.sm_width,
81 ag.sm_height,
82 IFNULL(rem.new_speed, 0) as new_speed
83 , CASE WHEN
84 (gp.retail_price BETWEEN 101 AND 46800)
85 THEN 1
86 ELSE 0 END AS pricefilter
87 , CASE WHEN ag.brandid IN ('3488') THEN 1 ELSE 0 END AS brandfilter
88 , 1 AS availfilter
89 , MAX( CASE WHEN gtp.prop_id = 17228 AND
90 gtp.propvalue_id IN (87654) THEN 1 ELSE 0 END) as sf17228,
91 IF(ag.has_replacement > 1, 1, ag.has_replacement)
92 , ag.id as new_code
93
94
95 FROM goods_avail_2 ag
96 JOIN goods_remains rem ON rem.goods_id = ag.id AND rem.barcode_id = ag.barcodeID
97 JOIN
98 (SELECT
99 hts.soft_id AS ID,
100 hts.hard_id AS classID,
101 '' AS path,
102 sp.selection_name selection_name
103 FROM goods_hard_to_soft_2_2 hts
104 JOIN goods_soft_price sp ON
105 hts.soft_id = sp.id AND
106 sp.site_setting_id = 5
107 WHERE hts.soft_id = 22093
108 UNION SELECT
109 sp.ID AS ID,
110 hts.hard_id AS classID,
111 sp.path AS path,
112 sp.selection_name
113 FROM goods_soft_price sp
114 JOIN goods_soft_price_tree spt ON
115 sp.ID = spt.child_id AND
116 spt.parent_id = 22093
117 JOIN goods_hard_to_soft_2_2 hts ON
118 hts.soft_id = sp.id AND
119 hts.soft_id <> 22093
120 WHERE spt.parent_id = 22093) sp ON sp.classID = ag.categoryID
121
122
123 JOIN goods_props_to_templates ptt ON ptt.template_id = ag.prop_template_id
124 JOIN goods_props_2 p ON p.id = ptt.prop_id
125 JOIN goods_to_props_values_2 gtp ON gtp.goods_id=ag.prop_goods_id AND gtp.prop_id = ptt.prop_id
126 JOIN goods_props_values pv ON pv.id = gtp.propvalue_id
127
128 JOIN goods_prices gp ON (gp.goods_id=ag.id AND gp.city_id=18413 AND gp.barcode_id=ag.barcodeID)
129
130
131
132
133
134
135
136
137 WHERE
138 (TRUE
139 AND (0
140 OR
141 (( rem.novator_remains > rem.novator_reserve) OR
142 ( rem.mitino_remains > rem.mitino_reserve) OR
143 ( rem.novokuz_remains > rem.novokuz_reserve) OR
144 ( rem.polej_remains > rem.polej_reserve) OR
145 ( rem.kondr_remains > rem.kondr_reserve)
146 )
147 OR
148 (( rem.novator_reserve_date > NOW() AND rem.novator_reserve > 0) OR
149 ( rem.mitino_reserve_date > NOW() AND rem.mitino_reserve > 0) OR
150 ( rem.novokuz_reserve_date > NOW() AND rem.novokuz_reserve > 0) OR
151 ( rem.polej_reserve_date > NOW() AND rem.polej_reserve > 0) OR
152 ( rem.kondr_reserve_date > NOW() AND rem.kondr_reserve > 0)
153 )
154 OR rem.msk_arrival_date IS NOT NULL
155 OR rem.supp_days >= 0))
156
157
158
159 GROUP BY ag.ID, ag.barcodeID
160 LIMIT 0, 8000)