· 7 years ago · Feb 20, 2019, 11:42 AM
1DELIMITER $$
2CREATE DEFINER=`root`@`%` PROCEDURE `product_validate_input`(IN jss json)
3proc_label:begin
4
5 declare eat_code nvarchar(100) COLLATE utf8_unicode_ci;
6 declare template_exists int;
7
8
9 declare final_json mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
10
11 declare js_product_name mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
12 declare js_trade_name mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
13 declare js_manifacturer_country mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
14 declare js_manufacturer_name mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
15 declare js_brand_name_product_offer mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
16 declare js_Offer_number_TRU_supplier mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
17 declare js_extra_features_name0 mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
18 declare js_extra_features_qualityValueSet0 mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
19 declare js_extra_features_name1 mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
20 declare js_extra_features_qualityValueSet1 mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
21 declare js_delivery_places mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
22 declare js_documents_name mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
23 declare js_documents_department mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
24 declare js_documents_number mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
25 declare js_documents_filename mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
26 declare js_documents_range mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
27 declare js_documents_dop_name mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
28 declare js_documents_dop_filename mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
29
30 declare violations mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci;
31
32 SET @enabled = true;
33 #set final_json = '';
34 #call debug_msg(@enabled, (select concat('json is:', js)) );
35 #if (js = '') then
36 #select 'ПуÑÑ‚Ð°Ñ Ñтрока';
37 #LEAVE proc_label;
38 #end if;
39
40 DROP TEMPORARY TABLE IF EXISTS jsonDat;
41
42 CREATE TEMPORARY TABLE jsonDat
43 AS (
44 SELECT
45 CAST(json_unquote(json_extract(jss, '$."Product_name"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as product_name,
46 CAST(json_unquote(json_extract(jss, '$."trade_name"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as trade_name,
47 CAST(json_unquote(json_extract(jss, '$."manufacturer_country"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as manifacturer_country,
48 CAST(json_unquote(json_extract(jss, '$."manufacturer_name"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as manufacturer_name,
49 CAST(json_unquote(json_extract(jss, '$."brand_name_product_offer"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as brand_name_product_offer,
50 CAST(json_unquote(json_extract(jss, '$."Offer_number_TRU_supplier"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as Offer_number_TRU_supplier,
51 CAST(json_unquote(json_extract(json_unquote(json_extract(jss, '$."extra_features"."<all_channels>"."<all_locales>"')),'$[0]."name"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as extra_features_name0,
52 CAST(json_unquote(json_extract(json_unquote(json_extract(jss, '$."extra_features"."<all_channels>"."<all_locales>"')),'$[0]."qualityValueSet"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as extra_features_qualityValueSet0,
53 CAST(json_unquote(json_extract(json_unquote(json_extract(jss, '$."extra_features"."<all_channels>"."<all_locales>"')),'$[1]."name"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as extra_features_name1,
54 CAST(json_unquote(json_extract(json_unquote(json_extract(jss, '$."extra_features"."<all_channels>"."<all_locales>"')),'$[1]."qualityValueSet"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as extra_features_qualityValueSet1,
55 json_unquote(json_extract(jss, '$."delivery_places"."<all_channels>"."<all_locales>"')) as delivery_places,
56 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(jss, '$."documents"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."name"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_name,
57 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(jss, '$."documents"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."department"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_department,
58 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(jss, '$."documents"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."number"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_number,
59 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(jss, '$."documents"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."fileRef"')),'$."fileName"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_filename,
60 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(jss, '$."documents"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."range"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_range,
61 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(jss, '$."documents_dop"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."name"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_dop_name,
62 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(jss, '$."documents_dop"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."fileRef"')),'$."fileName"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_dop_filename
63 );
64
65 set final_json = '';
66 #реализовать проверку на наличие товара Ñ ÐºÐ¾Ð´Ð¾Ð¼ клаÑÑификатора
67 #выход из процедуры, еÑли грузим шаблон
68 if ( select json_unquote(json_extract(jss, '$."is_ticker"."<all_channels>"."<all_locales>"' )) = 'true')
69 then
70 set final_json = '[{"code": "is-template"}]';
71 select final_json;
72 leave proc_label;
73 end if;
74
75 set eat_code = ( select IFNULL(json_unquote(json_extract(jss, '$."code"."<all_channels>"."<all_locales>"')), '' ) );
76
77
78
79 select count(*) into template_exists from pim_catalog_product
80 where json_unquote(json_extract(raw_values, '$."code"."<all_channels>"."<all_locales>"')) = eat_code
81 and json_unquote(json_extract(raw_values, '$."is_ticker"."<all_channels>"."<all_locales>"')) = 'true';
82
83 if ( eat_code = '' )
84 then
85 set final_json = CONCAT( IF ( final_json = '', '', CONCAT(final_json,',') ), '{"code": "code-empty"}');
86 elseif (template_exists = 0)
87 then
88 set final_json = CONCAT( IF ( final_json = '', '', CONCAT(final_json,',') ), '{"code": "not-in-classifier"}');
89 end if;
90
91 #проверка на цену > 0
92 if ( (select IFNULL(json_unquote(json_extract(jss, '$."price"."<all_channels>"."<all_locales>"')), 0) <= 0) OR (select json_unquote(json_extract(jss, '$."price"."<all_channels>"."<all_locales>"')) = ''))
93 then
94 set final_json = CONCAT( IF ( final_json = '', '', CONCAT(final_json,',') ), '{"code": "price-empty"}');
95 end if;
96
97 #будем проверÑÑ‚ÑŒ вÑе колонки на вхождение в Ñтоп-лиÑÑ‚ Ñловарь и Ñразу результат запихивать в Ñвою переменную
98
99
100 select group_concat(js separator ',') into js_product_name from
101 (select
102 CONCAT( '{"code":"stop-word",
103 "stopWord": { "id": ',k.id,',
104 "text": "',k.name,'"},
105 "attribute": {"id" : "-5",
106 "code":"product_name",
107 "label":"Полное наименование"}
108 }' ) as js
109
110 from (select lower(product_name) as w from jsonDat) as t
111 inner join kt_stop_word as k
112 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
113 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
114 where k.isActive = 1 ) as g;
115
116 #call debug_msg(@enabled, (select concat(js_product_name, ' AFTER INSERT') from jsonDat) );
117
118 select group_concat(js separator ',') into js_trade_name from
119 (select
120 CONCAT( '{"code":"stop-word",
121 "stopWord": { "id": ',k.id,',
122 "text": "',k.name,'"},
123 "attribute": {"id" : "-5",
124 "code":"trade_name",
125 "label":"Торговое наименование"}
126 }' ) as js
127
128 from (select lower(trade_name) as w from jsonDat) as t
129 inner join kt_stop_word as k
130 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
131 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
132 where k.isActive = 1 ) as g;
133
134 select group_concat(js separator ',') into js_manifacturer_country from
135 (select
136 CONCAT( '{"code":"stop-word",
137 "stopWord": { "id": ',k.id,',
138 "text": "',k.name,'"},
139 "attribute": {"id" : "-5",
140 "code":"manifacturer_country",
141 "label":"Ðаименование меÑта проиÑÑ…Ð¾Ð¶Ð´ÐµÐ½Ð¸Ñ Ñ‚Ð¾Ð²Ð°Ñ€Ð°"}
142 }' ) as js
143
144 from (select lower(manifacturer_country) as w from jsonDat) as t
145 inner join kt_stop_word as k
146 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
147 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
148 where k.isActive = 1 ) as g;
149
150 select group_concat(js separator ',') into js_manufacturer_name from
151 (select
152 CONCAT( '{"code":"stop-word",
153 "stopWord": { "id": ',k.id,',
154 "text": "',k.name,'"},
155 "attribute": {"id" : "-5",
156 "code":"manufacturer_name",
157 "label":"Ð˜Ð½Ñ„Ð¾Ñ€Ð¼Ð°Ñ†Ð¸Ñ Ð¾ производителе товара"}
158 }' ) as js
159
160 from (select lower(manufacturer_name) as w from jsonDat) as t
161 inner join kt_stop_word as k
162 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
163 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
164 where k.isActive = 1 ) as g;
165
166
167 select group_concat(js separator ',') into js_brand_name_product_offer from
168 (select
169 CONCAT( '{"code":"stop-word",
170 "stopWord": { "id": ',k.id,',
171 "text": "',k.name,'"},
172 "attribute": {"id" : "-5",
173 "code":"brand_name_product_offer",
174 "label":"Фирменное наименование"}
175 }' ) as js
176
177 from (select lower(brand_name_product_offer) as w from jsonDat) as t
178 inner join kt_stop_word as k
179 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
180 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
181 where k.isActive = 1 ) as g;
182
183
184 select group_concat(js separator ',') into js_Offer_number_TRU_supplier from
185 (select
186 CONCAT( '{"code":"stop-word",
187 "stopWord": { "id": ',k.id,',
188 "text": "',k.name,'"},
189 "attribute": {"id" : "-5",
190 "code":"Offer_number_TRU_supplier",
191 "label":"Ðомер ÐŸÑ€ÐµÐ´Ð»Ð¾Ð¶ÐµÐ½Ð¸Ñ Ð¢Ð Ð£ (ПоÑтавщика)"}
192 }' ) as js
193
194 from (select lower(Offer_number_TRU_supplier) as w from jsonDat) as t
195 inner join kt_stop_word as k
196 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
197 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
198 where k.isActive = 1 ) as g;
199
200
201 select group_concat(js separator ',') into js_extra_features_name0 from
202 (select
203 CONCAT( '{"code":"stop-word",
204 "stopWord": { "id": ',k.id,',
205 "text": "',k.name,'"},
206 "attribute": {"id" : "-5",
207 "code":"extra_features_name_block_1",
208 "label":"Ðаименование характериÑтики"}
209 }' ) as js
210
211 from (select lower(extra_features_name0) as w from jsonDat) as t
212 inner join kt_stop_word as k
213 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
214 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
215 where k.isActive = 1 ) as g;
216
217
218 select group_concat(js separator ',') into js_extra_features_qualityValueSet0 from
219 (select
220 CONCAT( '{"code":"stop-word",
221 "stopWord": { "id": ',k.id,',
222 "text": "',k.name,'"},
223 "attribute": {"id" : "-5",
224 "code":"extra_features_qualityValueSet_block_1",
225 "label":"Значение характериÑтики"}
226 }' ) as js
227
228 from (select lower(extra_features_qualityValueSet0) as w from jsonDat) as t
229 inner join kt_stop_word as k
230 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
231 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
232 where k.isActive = 1 ) as g;
233
234
235 select group_concat(js separator ',') into js_extra_features_name1 from
236 (select
237 CONCAT( '{"code":"stop-word",
238 "stopWord": { "id": ',k.id,',
239 "text": "',k.name,'"},
240 "attribute": {"id" : "-5",
241 "code":"extra_features_name_block_2",
242 "label":"Ðаименование характериÑтики"}
243 }' ) as js
244
245 from (select lower(extra_features_name1) as w from jsonDat) as t
246 inner join kt_stop_word as k
247 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
248 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
249 where k.isActive = 1 ) as g;
250
251
252 select group_concat(js separator ',') into js_extra_features_qualityValueSet1 from
253 (select
254 CONCAT( '{"code":"stop-word",
255 "stopWord": { "id": ',k.id,',
256 "text": "',k.name,'"},
257 "attribute": {"id" : "-5",
258 "code":"extra_features_qualityValueSet_block_2",
259 "label":"Значение характериÑтики"}
260 }' ) as js
261
262 from (select lower(extra_features_qualityValueSet1) as w from jsonDat) as t
263 inner join kt_stop_word as k
264 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
265 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
266 where k.isActive = 1 ) as g;
267
268
269
270
271 select group_concat(js separator ',') into js_delivery_places from
272 (select
273 CONCAT( '{"code":"stop-word",
274 "stopWord": { "id": ',k.id,',
275 "text": "',k.name,'"},
276 "attribute": {"id" : "-5",
277 "code":"delivery_places",
278 "label":"Значение характериÑтики"}
279 }' ) as js
280
281 from (select lower(delivery_places) as w from jsonDat) as t
282 inner join kt_stop_word as k
283 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
284 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
285 where k.isActive = 1 ) as g;
286
287
288 select group_concat(js separator ',') into js_documents_name from
289 (select
290 CONCAT( '{"code":"stop-word",
291 "stopWord": { "id": ',k.id,',
292 "text": "',k.name,'"},
293 "attribute": {"id" : "-5",
294 "code":"documents_name",
295 "label":"Ðаименование документа"}
296 }' ) as js
297
298 from (select lower(documents_name) as w from jsonDat) as t
299 inner join kt_stop_word as k
300 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
301 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
302 where k.isActive = 1 ) as g;
303
304
305 select group_concat(js separator ',') into js_documents_department from
306 (select
307 CONCAT( '{"code":"stop-word",
308 "stopWord": { "id": ',k.id,',
309 "text": "',k.name,'"},
310 "attribute": {"id" : "-5",
311 "code":"documents_department",
312 "label":"Ðаименование органа, выдавшего документ"}
313 }' ) as js
314
315 from (select lower(documents_department) as w from jsonDat) as t
316 inner join kt_stop_word as k
317 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
318 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
319 where k.isActive = 1 ) as g;
320
321
322 select group_concat(js separator ',') into js_documents_number from
323 (select
324 CONCAT( '{"code":"stop-word",
325 "stopWord": { "id": ',k.id,',
326 "text": "',k.name,'"},
327 "attribute": {"id" : "-5",
328 "code":"documents_number",
329 "label":"Ðомер документа"}
330 }' ) as js
331
332 from (select lower(documents_number) as w from jsonDat) as t
333 inner join kt_stop_word as k
334 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
335 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
336 where k.isActive = 1 ) as g;
337
338
339 select group_concat(js separator ',') into js_documents_filename from
340 (select
341 CONCAT( '{"code":"stop-word",
342 "stopWord": { "id": ',k.id,',
343 "text": "',k.name,'"},
344 "attribute": {"id" : "-5",
345 "code":"documents_filename",
346 "label":"Файл (название файла)"}
347 }' ) as js
348
349 from (select lower(documents_filename) as w from jsonDat) as t
350 inner join kt_stop_word as k
351 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
352 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
353 where k.isActive = 1 ) as g;
354
355
356 select group_concat(js separator ',') into js_documents_range from
357 (select
358 CONCAT( '{"code":"stop-word",
359 "stopWord": { "id": ',k.id,',
360 "text": "',k.name,'"},
361 "attribute": {"id" : "-5",
362 "code":"documents_range",
363 "label":"Срок дейÑÑ‚Ð²Ð¸Ñ Ð´Ð¾ÐºÑƒÐ¼ÐµÐ½Ñ‚Ð°"}
364 }' ) as js
365
366 from (select lower(documents_range) as w from jsonDat) as t
367 inner join kt_stop_word as k
368 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
369 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
370 where k.isActive = 1 ) as g;
371
372
373 select group_concat(js separator ',') into js_documents_dop_name from
374 (select
375 CONCAT( '{"code":"stop-word",
376 "stopWord": { "id": ',k.id,',
377 "text": "',k.name,'"},
378 "attribute": {"id" : "-5",
379 "code":"documents_dop_name",
380 "label":"Ðаименование документа (атрибуты)"}
381 }' ) as js
382
383 from (select lower(documents_dop_name) as w from jsonDat) as t
384 inner join kt_stop_word as k
385 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
386 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
387 where k.isActive = 1 ) as g;
388
389
390 select group_concat(js separator ',') into js_documents_dop_filename from
391 (select
392 CONCAT( '{"code":"stop-word",
393 "stopWord": { "id": ',k.id,',
394 "text": "',k.name,'"},
395 "attribute": {"id" : "-5",
396 "code":"documents_dop_filename",
397 "label":"Файл (название файла) (атрибуты)"}
398 }' ) as js
399
400 from (select lower(documents_dop_filename) as w from jsonDat) as t
401 inner join kt_stop_word as k
402 on (t.w = lower_name or instr(t.w, BINARY k.lower_name) > 0 ) AND
403 t.w NOT REGEXP CONCAT('[[:alpha:]](',k.lower_name,')$|^(',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[[:alpha:]]|[[:alpha:]](',k.lower_name,')[^[:alpha:]]|[^[:alpha:]](',k.lower_name,')[[:alpha:]]')
404 where k.isActive = 1 ) as g;
405
406
407 #не проверÑем на нулл, тк concat_ws автоматом убирает нуллы + разделители
408 #но Ñ‚.к. была пуÑÑ‚Ð°Ñ Ñтрока в final_json, то заменим ее на нулл
409 if final_json = ''
410 then
411 set final_json = null;
412 end if;
413
414 set violations = CONCAT( '[',
415 CONCAT_WS(',',
416 final_json,
417 js_product_name,
418 js_trade_name,
419 js_manifacturer_country,
420 js_manufacturer_name,
421 js_brand_name_product_offer,
422 js_Offer_number_TRU_supplier,
423 js_extra_features_name0,
424 js_extra_features_qualityValueSet0,
425 js_extra_features_name1,
426 js_extra_features_qualityValueSet1,
427 js_delivery_places,
428 js_documents_name,
429 js_documents_department,
430 js_documents_number,
431 js_documents_filename,
432 js_documents_range,
433 js_documents_dop_name,
434 js_documents_dop_filename
435 ), ']');
436
437
438 CREATE TABLE IF NOT EXISTS test_validation (
439 dat timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
440 js json DEFAULT NULL,
441 violations json DEFAULT NULL
442 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
443
444 insert into test_validation(js, violations)
445 select jss, violations;
446
447 DROP TEMPORARY TABLE IF EXISTS jsonDat;
448
449 select violations;
450
451
452end$$
453DELIMITER ;