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