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