· 6 years ago · Jul 12, 2019, 12:44 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.novator_remains > rem.novator_reserve) OR
18 ( rem.mitino_remains > rem.mitino_reserve) OR
19 ( rem.novokuz_remains > rem.novokuz_reserve) OR
20 ( rem.polej_remains > rem.polej_reserve) OR
21 ( rem.kondr_remains > rem.kondr_reserve)
22 ), 1,
23 IF (
24 rem.supp_quantity > 0, 7,
25 IF (
26 ( rem.msk_arrival_date IS NOT NULL ), 3,
27 IF (
28 (
29 ( rem.kondr_availability NOT BETWEEN 1 AND 6 AND
30 rem.kondr_reserve > 0 AND rem.kondr_reserve_date > NOW()
31 ) OR
32 ( rem.novator_availability NOT BETWEEN 1 AND 6 AND
33 rem.novator_reserve > 0 AND rem.novator_reserve_date > NOW()
34 ) OR
35 ( rem.mitino_availability NOT BETWEEN 1 AND 6 AND
36 rem.mitino_reserve > 0 AND rem.mitino_reserve_date > NOW()
37 )
38 ), 4, 0
39 )
40 )
41 )
42 ) AS main_status,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.novokuz_availability BETWEEN 1 AND 6 ) , 1, 0) as novokuz_status,
45 IF (( rem.polej_availability BETWEEN 1 AND 6 ) , 1, 0) as polej_status,
46 IF (( rem.kondr_availability BETWEEN 1 AND 6 ) , 1, 0) as kondr_status,
47 (rem.novator_remains-rem.novator_reserve) AS novator_remains,
48 (rem.mitino_remains-rem.mitino_reserve) AS mitino_remains,
49 (rem.novokuz_remains-rem.novokuz_reserve) AS novokuz_remains,
50 (rem.polej_remains-rem.polej_reserve) AS polej_remains,
51 (rem.kondr_remains-rem.kondr_reserve) AS main_remains,
52 rem.novator_suborder AS novator_suborder,
53 rem.mitino_suborder AS mitino_suborder,
54 rem.novokuz_suborder AS novokuz_suborder,
55 rem.polej_suborder AS polej_suborder,
56 rem.kondr_suborder AS kondr_suborder,
57 rem.novator_reserve_date AS novator_reserve_date,
58 rem.mitino_reserve_date AS mitino_reserve_date,
59 rem.novokuz_reserve_date AS novokuz_reserve_date,
60 rem.polej_reserve_date AS polej_reserve_date,
61 rem.kondr_reserve_date AS main_reserve_date,
62
63 rem.msk_arrival_date,
64 rem.supp_days,
65 gp.retail_price as retail_price,
66 gp.bonus_price,
67 gp.small_whole_sale_price,
68 gp.medium_whole_sale_price,
69 CASE WHEN gp.backup_retail_price_date >= DATE_SUB(NOW(), INTERVAL 10 DAY)
70 THEN gp.prev_retail_price ELSE NULL END AS prev_retail_price,
71 ag.name,
72 gp.backup_retail_price_date AS prev_changes,
73 ag.part_no,
74 ag.buytext,
75 ag.title,
76 ag.foto_goodID,
77 ag.sm_width,
78 ag.sm_height,
79 IFNULL(rem.new_speed, 0) as new_speed
80 , 1 AS pricefilter
81 , 1 AS brandfilter
82 , 1 AS availfilter
83 , MAX( CASE WHEN gtp.prop_id = 23968 AND
84 gtp.propvalue_id IN (160597) THEN 1 ELSE 0 END) as sf23968,
85 IF(ag.has_replacement > 1, 1, ag.has_replacement)
86 , ag.id_str as new_code
87 , ag.fake_part_no, ag.url
88
89 FROM goods_models_avail_1 ag
90 JOIN goods_remains rem ON rem.goods_id = ag.goods_id AND rem.barcode_id = ag.barcodeID
91 JOIN
92 (SELECT
93 hts.soft_id AS ID,
94 hts.hard_id AS classID,
95 '' AS path,
96 sp.selection_name selection_name
97 FROM goods_hard_to_soft_2 hts
98 JOIN goods_soft_price sp ON
99 hts.soft_id = sp.id AND
100 sp.site_setting_id = 5
101 WHERE hts.soft_id = 21729
102 UNION SELECT
103 sp.ID AS ID,
104 hts.hard_id AS classID,
105 sp.path AS path,
106 sp.selection_name
107 FROM goods_soft_price sp
108 JOIN goods_soft_price_tree_2 spt ON
109 sp.ID = spt.child_id AND
110 spt.parent_id = 21729
111 JOIN goods_hard_to_soft_2 hts ON
112 hts.soft_id = sp.id AND
113 hts.soft_id <> 21729
114 WHERE spt.parent_id = 21729) sp ON sp.classID = ag.categoryID
115
116
117 JOIN goods_props_to_templates ptt ON ptt.template_id = ag.prop_template_id
118 JOIN goods_props_2 p ON p.id = ptt.prop_id
119 JOIN goods_to_props_values_2 gtp ON gtp.goods_id=ag.prop_goods_id AND gtp.prop_id = ptt.prop_id
120 JOIN goods_props_values pv ON pv.id = gtp.propvalue_id
121
122 JOIN goods_prices gp ON (gp.goods_id=ag.goods_id AND gp.city_id=18413 AND gp.barcode_id=ag.barcodeID)
123
124
125
126
127
128
129
130
131 WHERE
132 (TRUE
133 AND (0
134 OR
135 (( rem.novator_remains > rem.novator_reserve) OR
136 ( rem.mitino_remains > rem.mitino_reserve) OR
137 ( rem.novokuz_remains > rem.novokuz_reserve) OR
138 ( rem.polej_remains > rem.polej_reserve) OR
139 ( rem.kondr_remains > rem.kondr_reserve)
140 )
141 OR
142 (( rem.novator_reserve_date > NOW() AND rem.novator_reserve > 0) OR
143 ( rem.mitino_reserve_date > NOW() AND rem.mitino_reserve > 0) OR
144 ( rem.novokuz_reserve_date > NOW() AND rem.novokuz_reserve > 0) OR
145 ( rem.polej_reserve_date > NOW() AND rem.polej_reserve > 0) OR
146 ( rem.kondr_reserve_date > NOW() AND rem.kondr_reserve > 0)
147 )
148 OR rem.msk_arrival_date IS NOT NULL
149 OR rem.supp_days >= 0))
150 AND fake_part_no = '0'
151 AND MATCH(ag.name, ag.id_str, ag.part_no) AGAINST ('+Sony* +разъем* +питания*' IN BOOLEAN MODE )
152
153
154 GROUP BY ag.goods_id , ag.brand_id , ag.model_id , ag.fake_part_no
155 ORDER BY ag.model_id
156 LIMIT 0, 3000)