· 6 years ago · Mar 20, 2019, 03:36 PM
1CREATE TEMPORARY TABLE IF NOT EXISTS temp_tt ENGINE=MEMORY AS (
2 SELECT
3 ag.goods_id AS id,
4 MAX(ag.prop_template_id) AS proptplID,
5 MAX(ag.prop_goods_id) AS prop_goodid,
6 sp.ID AS spID,
7
8 MAX(ag.brandid) AS brandid,
9 NULL AS bcid,
10 ag.barcode AS barcode,
11 MAX(ag.categoryid) AS catid,
12 MAX(ag.is_warr_disc) AS is_warr_disc,
13 MAX(ag.original_goodid) AS original_goodid,
14
15 IF (
16
17 (( rem.kondr_remains > rem.kondr_reserve) OR
18 ( rem.udel_remains > rem.udel_reserve) OR
19 ( rem.spblig_remains > rem.spblig_reserve)
20 ), 1,
21 IF (
22 rem.supp_quantity > 0, 7,
23 IF (
24 ( rem.msk_arrival_date IS NOT NULL ), 3,
25 IF (
26 (
27 ( rem.kondr_availability NOT BETWEEN 1 AND 6 AND
28 rem.kondr_reserve > 0 AND rem.kondr_reserve_date > NOW()
29 ) OR
30 ( rem.novator_availability NOT BETWEEN 1 AND 6 AND
31 rem.novator_reserve > 0 AND rem.novator_reserve_date > NOW()
32 ) OR
33 ( rem.mitino_availability NOT BETWEEN 1 AND 6 AND
34 rem.mitino_reserve > 0 AND rem.mitino_reserve_date > NOW()
35 )
36 ), 4, 0
37 )
38 )
39 )
40 ) AS main_status,
41
42 IF (( rem.novator_availability BETWEEN 1 AND 6 ) , 1, 0) as novator_status,
43 IF (( rem.mitino_availability BETWEEN 1 AND 6 ) , 1, 0) as mitino_status,
44 IF (( rem.spblig_availability BETWEEN 1 AND 6 ) , 1, 0) as spblig_status,
45 IF (( rem.ekat_availability BETWEEN 1 AND 6 ) , 1, 0) as ekat_status,
46 IF (( rem.novokuz_availability BETWEEN 1 AND 6 ) , 1, 0) as novokuz_status,
47 IF (( rem.jelez_availability BETWEEN 1 AND 6 ) , 1, 0) as jelez_status,
48 IF (( rem.comm_availability BETWEEN 1 AND 6 ) , 1, 0) as comm_status,
49 IF (( rem.vrn_availability BETWEEN 1 AND 6 ) , 1, 0) as vrn_status,
50 IF (( rem.rnd_availability BETWEEN 1 AND 6 ) , 1, 0) as rnd_status,
51 IF (( rem.yaro_availability BETWEEN 1 AND 6 ) , 1, 0) as yaro_status,
52 IF (( rem.vlad_availability BETWEEN 1 AND 6 ) , 1, 0) as vlad_status,
53 IF (( rem.samara_availability BETWEEN 1 AND 6 ) , 1, 0) as samara_status,
54 IF (( rem.udel_availability BETWEEN 1 AND 6 ) , 1, 0) as udel_status,
55 IF (( rem.novgorod_availability BETWEEN 1 AND 6 ) , 1, 0) as novgorod_status,
56 IF (( rem.kazan_availability BETWEEN 1 AND 6 ) , 1, 0) as kazan_status,
57 IF (( rem.saratov_availability BETWEEN 1 AND 6 ) , 1, 0) as saratov_status,
58 IF (( rem.stavr_availability BETWEEN 1 AND 6 ) , 1, 0) as stavr_status,
59 IF (( rem.nsk_availability BETWEEN 1 AND 6 ) , 1, 0) as nsk_status,
60
61 (rem.novator_remains-rem.novator_reserve) AS nahimov_remains,
62 (rem.kondr_remains-rem.kondr_reserve) AS main_remains,
63 (rem.kondr_remains-rem.kondr_reserve) AS kondr_remains,
64 (rem.mitino_remains-rem.mitino_reserve) AS mitino_remains,
65 (rem.spblig_remains-rem.spblig_reserve) AS spblig_remains,
66 (rem.ekat_remains-rem.ekat_reserve) AS ekat_remains,
67 (rem.novokuz_remains-rem.novokuz_reserve) AS novokuz_remains,
68 (rem.jelez_remains-rem.jelez_reserve) AS jelez_remains,
69 (rem.comm_remains-rem.comm_reserve) AS comm_remains,
70 (rem.vrn_remains-rem.vrn_reserve) AS vrn_remains,
71 (rem.rnd_remains-rem.rnd_reserve) AS rnd_remains,
72 (rem.yaro_remains-rem.yaro_reserve) AS yaro_remains,
73 (rem.vlad_remains-rem.vlad_reserve) AS vlad_remains,
74 (rem.samara_remains-rem.samara_reserve) AS samara_remains,
75 (rem.udel_remains-rem.udel_reserve) AS udel_remains,
76 (rem.novgorod_remains-rem.novgorod_reserve) AS novgorod_remains,
77 (rem.kazan_remains-rem.kazan_reserve) AS kazan_remains,
78 (rem.saratov_remains-rem.saratov_reserve) AS saratov_remains,
79 (rem.stavr_remains-rem.kazan_reserve) AS stavr_remains,
80 (rem.nsk_remains-rem.nsk_reserve) AS nsk_remains,
81
82 rem.novator_suborder AS novator_suborder,
83 rem.kondr_suborder AS kondr_suborder,
84 rem.mitino_suborder AS mitino_suborder,
85 rem.spblig_suborder AS spblig_suborder,
86 rem.ekat_suborder AS ekat_suborder,
87 rem.novokuz_suborder AS novokuz_suborder,
88 rem.jelez_suborder AS jelez_suborder,
89 rem.comm_suborder AS comm_suborder,
90 rem.vrn_suborder AS vrn_suborder,
91 rem.rnd_suborder AS rnd_suborder,
92 rem.yaro_suborder AS yaro_suborder,
93 rem.vlad_suborder AS vlad_suborder,
94 rem.samara_suborder AS samara_suborder,
95 rem.udel_suborder AS udel_suborder,
96 rem.novgorod_suborder AS novgorod_suborder,
97 rem.kazan_suborder AS kazan_suborder,
98 rem.saratov_suborder AS saratov_suborder,
99 rem.stavr_suborder AS stavr_suborder,
100 rem.nsk_suborder AS nsk_suborder,
101
102 rem.kondr_reserve_date AS main_reserve_date,
103 rem.novator_reserve_date AS novator_reserve_date,
104 rem.mitino_reserve_date AS mitino_reserve_date,
105 rem.spblig_reserve_date AS spblig_reserve_date,
106 rem.ekat_reserve_date AS ekat_reserve_date,
107 rem.novokuz_reserve_date AS novokuz_reserve_date,
108 rem.jelez_reserve_date AS jelez_reserve_date,
109 rem.comm_reserve_date AS comm_reserve_date,
110 rem.vrn_reserve_date AS vrn_reserve_date,
111 rem.rnd_reserve_date AS rnd_reserve_date,
112 rem.yaro_reserve_date AS yaro_reserve_date,
113 rem.vlad_reserve_date AS vlad_reserve_date,
114 rem.samara_reserve_date AS samara_reserve_date,
115 rem.udel_reserve_date AS udel_reserve_date,
116 rem.novgorod_reserve_date AS novgorod_reserve_date,
117 rem.kazan_reserve_date AS kazan_reserve_date,
118 rem.saratov_reserve_date AS saratov_reserve_date,
119 rem.stavr_reserve_date AS stavr_reserve_date,
120 rem.nsk_reserve_date AS nsk_reserve_date,
121 rem.msk_arrival_date,
122
123 rem.supp_days,
124 gp.retail_price as retail_price,
125 gp.bonus_price,
126 gp.small_whole_sale_price,
127 gp.medium_whole_sale_price,
128 CASE WHEN gp.backup_retail_price_date >= DATE_SUB(NOW(), INTERVAL 10 DAY)
129 THEN gp.prev_retail_price ELSE NULL END AS prev_retail_price,
130 ag.name,
131 gp.backup_retail_price_date AS prev_changes,
132 ag.part_no,
133 ag.buytext,
134 ag.title,
135 ag.foto_goodID,
136 ag.sm_width,
137 ag.sm_height,
138 IFNULL(rem.new_speed, 0) as new_speed
139 , 1 AS pricefilter
140 , 1 AS brandfilter
141 , 1 AS availfilter
142 , MAX( CASE WHEN gtp.prop_id = 8469 AND
143 gtp.propvalue_id IN (71046) THEN 1 ELSE 0 END) as sf8469,
144 IF(ag.has_replacement > 1, 1, ag.has_replacement)
145 , ag.id_str as new_code
146 , ag.fake_part_no, ag.url
147
148 FROM goods_models_avail ag
149 JOIN goods_remains rem ON rem.goods_id = ag.goods_id AND rem.barcode_id = ag.barcodeID
150 JOIN
151 (SELECT
152 hts.soft_id AS ID,
153 hts.hard_id AS classID,
154 '' AS path,
155 sp.selection_name selection_name
156 FROM goods_hard_to_soft hts
157 JOIN goods_soft_price sp ON
158 hts.soft_id = sp.id AND
159 sp.site_setting_id = 5
160 WHERE hts.soft_id = 21714
161 UNION SELECT
162 sp.ID AS ID,
163 hts.hard_id AS classID,
164 sp.path AS path,
165 sp.selection_name
166 FROM goods_soft_price sp
167 JOIN goods_soft_price_tree_2 spt ON
168 sp.ID = spt.child_id AND
169 spt.parent_id = 21714
170 JOIN goods_hard_to_soft hts ON
171 hts.soft_id = sp.id AND
172 hts.soft_id <> 21714
173 WHERE spt.parent_id = 21714) sp ON sp.classID = ag.categoryID
174
175
176 JOIN goods_props_to_templates_2 ptt ON ptt.template_id = ag.prop_template_id
177 JOIN goods_props_2 p ON p.id = ptt.prop_id
178 JOIN goods_to_props_values gtp ON gtp.goods_id=ag.prop_goods_id AND gtp.prop_id = ptt.prop_id
179 JOIN goods_props_values_2 pv ON pv.id = gtp.propvalue_id
180
181 JOIN goods_prices_2 gp ON (gp.goods_id=ag.goods_id AND gp.city_id=18413 AND gp.barcode_id=ag.barcodeID)
182 WHERE
183 (TRUE
184 AND (0
185 OR
186 (( rem.kondr_remains > rem.kondr_reserve) OR
187 ( rem.udel_remains > rem.udel_reserve) OR
188 ( rem.spblig_remains > rem.spblig_reserve)
189 )
190 OR
191 (( rem.kondr_reserve_date > NOW() AND rem.kondr_reserve > 0) OR
192 ( rem.udel_reserve_date > NOW() AND rem.udel_reserve > 0) OR
193 ( rem.spblig_reserve_date > NOW() AND rem.spblig_reserve > 0)
194 )
195 OR rem.msk_arrival_date IS NOT NULL
196 OR rem.supp_days >= 0))
197 AND fake_part_no = '0'
198 AND MATCH(ag.name, ag.id_str, ag.part_no) AGAINST ('+asus*' IN BOOLEAN MODE )
199
200
201 GROUP BY ag.goods_id , ag.brand_id , ag.model_id , ag.fake_part_no
202 ORDER BY ag.model_id
203 LIMIT 0, 3000)