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