· 6 years ago · Sep 16, 2019, 02:36 PM
1CREATE TEMPORARY TABLE IF NOT EXISTS temp_tt ENGINE=MEMORY AS (
2 SELECT
3 ag.goods_id AS id,
4 ag.prop_template_id AS proptplID,
5 ag.prop_goods_id AS prop_goodid,
6
7 MAX(hts.soft_id) as spID,
8 ag.brandid AS brandid,
9 NULL AS bcid,
10 ag.barcode AS barcode,
11 ag.categoryid AS catid,
12 ag.is_warr_disc AS is_warr_disc,
13 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 ,
84 IF(ag.has_replacement > 1, 1, ag.has_replacement)
85 , ag.id_str as new_code
86 , ag.fake_part_no, ag.url
87
88 FROM goods_models_avail_1 ag
89
90 JOIN goods_remains_2 rem ON rem.goods_id = ag.goods_id AND rem.barcode_id = ag.barcodeID
91
92
93
94
95 JOIN goods_prices_2 gp ON (gp.goods_id=ag.goods_id AND gp.city_id=18413 AND gp.barcode_id=ag.barcodeID)
96
97
98 JOIN goods_hard_to_soft_2_2 hts on hts.hard_id = ag.categoryID
99 JOIN goods_soft_price_2 sp ON hts.soft_id=sp.id AND sp.site_setting_id=5
100
101
102
103
104 WHERE
105 (TRUE
106 AND (0
107 OR
108 ( ( rem.novator_remains > rem.novator_reserve) OR
109 ( rem.mitino_remains > rem.mitino_reserve) OR
110 ( rem.novokuz_remains > rem.novokuz_reserve) OR
111 ( rem.polej_remains > rem.polej_reserve) OR
112 ( rem.kondr_remains > rem.kondr_reserve)
113 )
114 OR
115 (( rem.novator_reserve_date > NOW() AND rem.novator_reserve > 0) OR
116 ( rem.mitino_reserve_date > NOW() AND rem.mitino_reserve > 0) OR
117 ( rem.novokuz_reserve_date > NOW() AND rem.novokuz_reserve > 0) OR
118 ( rem.polej_reserve_date > NOW() AND rem.polej_reserve > 0) OR
119 ( rem.kondr_reserve_date > NOW() AND rem.kondr_reserve > 0)
120 )
121 OR rem.msk_arrival_date IS NOT NULL
122 OR rem.supp_days >= 0))
123 AND fake_part_no = '0'
124 AND MATCH(ag.name, ag.id_str, ag.part_no) AGAINST ('+блок* +питания*' IN BOOLEAN MODE )
125
126 GROUP BY ag.goods_id , ag.brand_id , ag.model_id , ag.fake_part_no
127 ORDER BY ag.model_id
128 LIMIT 0, 3000)