· 6 years ago · Jul 08, 2019, 05:24 PM
1BEGIN
2
3DECLARE v_offers_q INT;
4DECLARE v_path VARCHAR(255) DEFAULT '1/20150316/14265389276019.jpg';
5DECLARE v_id_items_genders INT;
6DECLARE v_id_items_seasons INT;
7DECLARE v_id_items_suppliers INT;
8DECLARE v_action INT DEFAULT 0;
9
10SET p_max_amount = IF(p_max_amount = 0, 999999, p_max_amount);
11
12IF p_id_stores = 41 THEN
13
14 IF p_action = 'related' THEN
15
16 IF p_id_items_2 <> 0 THEN
17
18 SELECT
19 t1.id_items_genders,
20 t3.id_items_taxonomies_terms,
21 t2.id_items_taxonomies_terms
22 INTO
23 v_id_items_genders,
24 v_id_items_seasons,
25 v_id_items_suppliers
26 FROM t_items_2 t1
27 # SUPPLIER
28 INNER JOIN t_items_2_rels_taxonomies_terms t2 ON t2.id_items_2 = t1.id_items_2 AND t2.id_items_taxonomies = 1
29 # SEASON
30 LEFT JOIN t_items_2_rels_taxonomies_terms t3 ON t3.id_items_2 = t1.id_items_2 AND t3.id_items_taxonomies = 6
31 WHERE t1.id_items_2 = p_id_items_2;
32
33 END IF;
34
35 SELECT DISTINCT
36 t1.id_items_2,
37 t1.alias,
38 t1.path,
39 t1.type,
40 t1.gender,
41 t1.brand,
42 t1.model,
43 t1.color,
44 t1.price,
45 t1.original_price,
46 t1.discount_percentage,
47 t1.label
48 FROM t_items_temps t1
49 # SUPPLIER
50 INNER JOIN t_items_2_rels_taxonomies_terms t2 ON t2.id_items_2 = t1.id_items_2 AND t2.id_items_taxonomies = 1
51 # SEASON
52 INNER JOIN t_items_2_rels_taxonomies_terms t3 ON t3.id_items_2 = t1.id_items_2 AND t3.id_items_taxonomies = 6
53 WHERE t1.id_items_2 <> p_id_items_2
54 AND t1.id_items_genders = v_id_items_genders
55 AND t2.id_items_taxonomies_terms = v_id_items_suppliers
56 AND t3.id_items_taxonomies_terms = v_id_items_seasons
57 AND t1.price > 0
58 AND t1.path <> v_path
59 ORDER BY t1.id_items_2
60 LIMIT p_limit;
61
62 ELSEIF p_action = 'navigations' THEN
63
64 SET v_action = 1;
65 SET @prepare = CONCAT("
66 CREATE TEMPORARY TABLE IF NOT EXISTS tt_offers_new AS
67 SELECT
68 t2.id_items_2,
69 t2.id_items_1,
70 t2.alias,
71 t2.path,
72 t2.type,
73 t2.gender,
74 t2.brand,
75 t2.model,
76 t2.color,
77 t2.price,
78 t2.original_price,
79 t2.discount_percentage
80 FROM t_items_offers_navigations t1
81 INNER JOIN t_items_temps t2 ON t1.id_items_2_recommended = t2.id_items_2
82 WHERE
83 t1.id_items_2_evaluated = ", p_id_items_2,"
84 AND t2.price BETWEEN ", p_min_amount, " AND ", p_max_amount,
85 IF(p_size <> '', CONCAT(" AND t2.size = ", p_size), " "), "
86 AND path <> '", v_path, "'", IF(p_data_item <> '', CONCAT(" AND t1.id_items_2_recommended NOT IN (", p_data_item, ") "), " "), "
87 GROUP BY t1.id_items_2_recommended
88 ORDER BY t1.quantity DESC
89 LIMIT ", p_limit);
90 PREPARE stmt FROM @prepare;
91 EXECUTE stmt;
92 DEALLOCATE PREPARE stmt;
93
94 ELSE
95
96 SET v_action = 1;
97
98 CREATE TEMPORARY TABLE IF NOT EXISTS tt_offers_new AS
99 SELECT
100 t2.id_items_2,
101 t2.id_items_1,
102 t2.alias,
103 t2.path,
104 t2.type,
105 t2.gender,
106 t2.brand,
107 t2.model,
108 t2.color,
109 t2.price,
110 t2.original_price,
111 t2.discount_percentage
112 FROM t_items_offers t1
113 INNER JOIN t_items_temps t2 ON t1.id_items_2_recommended = t2.id_items_2
114 WHERE
115 t1.id_items_2 = p_id_items_2
116 AND t2.price BETWEEN p_min_amount AND p_max_amount
117 AND IF(p_size <> '', t2.size = p_size, 1) AND path <> v_path
118 GROUP BY t1.id_items_2_recommended
119 ORDER BY t1.quantity DESC
120 LIMIT p_limit;
121
122 END IF;
123
124 IF v_action = 1 THEN
125
126 SELECT
127 COUNT(1)
128 INTO
129 v_offers_q
130 FROM tt_offers_new;
131
132 SET p_limit = p_limit - v_offers_q;
133
134 IF p_limit > 0 THEN
135
136 SELECT
137 *
138 FROM tt_offers_new
139
140 UNION ALL
141
142 SELECT
143 *
144 FROM (
145 SELECT DISTINCT
146 id_items_2,
147 0 id_items_1,
148 alias,
149 path,
150 type,
151 gender,
152 brand,
153 model,
154 color,
155 price,
156 original_price,
157 discount_percentage
158 FROM t_items_temps
159 WHERE
160 price BETWEEN p_min_amount AND p_max_amount
161 AND path <> v_path
162 ORDER BY timestamp_active DESC
163 LIMIT p_limit
164 ) v;
165
166 ELSE
167
168 SELECT
169 *
170 FROM tt_offers_new;
171
172 END IF;
173
174 DROP TEMPORARY TABLE IF EXISTS tt_offers_new;
175
176 END IF;
177
178ELSE
179
180 SELECT
181 tt2.id_items_2,
182 tt1.id_items_1,
183 tt6.alias,
184 tt7.path,
185 tt8.description type,
186 tt9.description gender,
187 tt11.description brand,
188 tt2.model,
189 tt13.description color,
190 tt5.price,
191 tt5.real_price original_price,
192 IF(tt5.real_price > tt5.price, CEILING((1 - tt5.price / tt5.real_price) * 100), 0) discount_percentage
193 FROM (
194 SELECT
195 t2.id_items_1,
196 t1.id_items_2,
197 t1.id_items_2_recommended,
198 SUM(t3.stock) stock,
199 t1.quantity
200 FROM t_items_offers t1
201 INNER JOIN t_items_1 t2 ON t1.id_items_2_recommended = t2.id_items_2
202 INNER JOIN t_items_stocks t3 ON t2.id_items_1 = t3.id_items_1 AND t3.id_stores = p_id_stores
203 # SIZE
204 LEFT JOIN t_items_1_rels_taxonomies_terms t4 ON t4.id_items_1 = t2.id_items_1 AND t4.id_items_taxonomies = 4
205 LEFT JOIN t_items_taxonomies_terms t5 ON t5.id_items_taxonomies_terms = t4.id_items_taxonomies_terms
206 WHERE t1.id_items_2 = p_id_items_2
207 AND IF(p_size <> '', t5.description = p_size, 1)
208 AND t1.is_equivalent = 1
209 GROUP BY t1.id_items_2_recommended
210 HAVING stock > 0
211 ) tt1
212 INNER JOIN t_items_2 tt2 ON tt1.id_items_2_recommended = tt2.id_items_2
213 INNER JOIN t_items_prices_v2 tt5 ON tt2.id_items_2 = tt5.id_items_2 AND tt5.id_stores = p_id_stores
214 LEFT JOIN t_aliases tt6 ON tt6.module = 'producto' AND tt6.params = CONCAT('id=', tt2.id_items_2)
215 LEFT JOIN t_items_images tt7 ON tt7.id_items_2 = tt2.id_items_2 AND tt7.width = 160 AND tt7.height = 240 AND tt7.weight = 1
216 LEFT JOIN t_items_types tt8 ON tt8.id_items_types = tt2.id_items_types
217 LEFT JOIN t_items_genders tt9 ON tt9.id_items_genders = tt2.id_items_genders
218 # BRAND
219 INNER JOIN t_items_2_rels_taxonomies_terms tt10 ON tt10.id_items_2 = tt2.id_items_2 AND tt10.id_items_taxonomies = 2
220 INNER JOIN t_items_taxonomies_terms tt11 ON tt11.id_items_taxonomies_terms = tt10.id_items_taxonomies_terms
221 # COLOR
222 LEFT JOIN t_items_2_rels_taxonomies_terms tt12 ON tt12.id_items_2 = tt2.id_items_2 AND tt12.id_items_taxonomies = 5
223 LEFT JOIN t_items_taxonomies_terms tt13 ON tt13.id_items_taxonomies_terms = tt12.id_items_taxonomies_terms
224 WHERE tt5.price BETWEEN p_min_amount AND p_max_amount
225 GROUP BY tt2.id_items_2
226 ORDER BY tt1.quantity DESC
227 LIMIT p_limit;
228
229END IF;
230
231/*
232SELECT DISTINCT
233id_items_2,
2340 id_items_1,
235alias,
236path,
237type,
238gender,
239brand,
240model,
241color,
242price,
243original_price,
244discount_percentage
245FROM t_items_temps
246WHERE price BETWEEN p_min_amount AND p_max_amount
247AND path <> v_path
248ORDER BY timestamp_active DESC
249LIMIT p_limit;
250*/
251END