· 7 years ago · Feb 04, 2019, 04:18 PM
1drop procedure product_validate_form_json;
2
3DELIMITER $$
4CREATE DEFINER=`root`@`%` PROCEDURE `product_validate_form_json`(IN price nvarchar(20), IN classifierId int, IN prodId int )
5begin
6
7 declare final_json mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci default '';
8 declare done int default false;
9
10 #ÑÑ‚Ð°Ñ‚ÑƒÑ Ð²Ð°Ð»Ð¸Ð´Ð°Ñ†Ð¸Ð¸
11 declare stat int;
12
13 declare word_id int;
14 declare word_name NVARCHAR(150) COLLATE utf8_unicode_ci;
15
16 declare cur1 cursor for
17 select name from kt_stop_word where isActive = 1;
18
19 declare continue handler for not found set done = TRUE;
20
21 set final_json = '';
22 set stat = 0;
23 SET @enabled = true;
24 call debug_msg(@enabled, (select concat('prod ID:', prodId)) );
25
26 if ifnull(price,0) <=0
27 then
28 set final_json = '{"code": "price-empty"}';
29 end if;
30
31
32 #Ð²Ð°Ð»Ð¸Ð´Ð°Ñ†Ð¸Ñ Ð½Ð° Ñтоп-Ñлова
33
34 #DROP TEMPORARY TABLE jsonDat;
35
36 CREATE TEMPORARY TABLE IF NOT EXISTS jsonDat ENGINE = MyISAM
37 AS (
38 SELECT
39 id,
40 family_id,
41 product_model_id,
42 family_variant_id,
43 is_enabled,
44 identifier,
45 raw_values,
46 created,
47 updated,
48 valid,
49 CAST(json_unquote(json_extract(raw_values, '$."Product_name"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as product_name,
50 CAST(json_unquote(json_extract(raw_values, '$."trade_name"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as trade_name,
51 CAST(json_unquote(json_extract(raw_values, '$."manifacturer_country"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as manifacturer_country,
52 CAST(json_unquote(json_extract(raw_values, '$."manufacturer_name"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as manufacturer_name,
53 CAST(json_unquote(json_extract(raw_values, '$."brand_name_product_offer"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as brand_name_product_offer,
54 CAST(json_unquote(json_extract(raw_values, '$."Offer_number_TRU_supplier"."<all_channels>"."<all_locales>"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as Offer_number_TRU_supplier,
55 CAST(json_unquote(json_extract(json_unquote(json_extract(raw_values, '$."extra_features"."<all_channels>"."<all_locales>"')),'$."name"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as extra_features_name,
56 CAST(json_unquote(json_extract(json_unquote(json_extract(raw_values, '$."extra_features"."<all_channels>"."<all_locales>"')),'$."qualityValueSet"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as extra_features_qualityValueSet,
57 json_unquote(json_extract(raw_values, '$."delivery_places"."<all_channels>"."<all_locales>"')) as delivery_places,
58 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(raw_values, '$."documents"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."name"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_name,
59 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(raw_values, '$."documents"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."department"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_department,
60 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(raw_values, '$."documents"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."number"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_number,
61 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(raw_values, '$."documents"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."fileRef"')),'$."fileName"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_filename,
62 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(raw_values, '$."documents"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."range"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_range,
63 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(raw_values, '$."documents_dop"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."name"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_dop_name,
64 CAST(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(json_unquote(json_extract(raw_values, '$."documents_dop"."<all_channels>"."<all_locales>"')),'$[0]')),'$[0]."fileRef"')),'$."fileName"')) as char(300) CHARSET utf8) collate utf8_unicode_ci as documents_dop_filename
65 FROM pim_catalog_product
66 where id = prodId
67 );
68
69
70 call debug_msg(@enabled, (select concat(id, ' AFTER INSERT') from jsonDat) );
71
72 open cur1;
73
74 read_loop: LOOP
75 fetch cur1 into word_name;
76 if done then
77 leave read_loop;
78 end if;
79
80 ###Ñ€Ð°ÐºÐ¾Ð²Ð°Ñ Ð»Ð¾Ð³Ð¸ÐºÐ° внутри курÑора
81 ###тут заполнÑетÑÑ Ð¸Ñ‚Ð¾Ð³Ð¾Ð²Ð°Ñ Ð¿ÐµÑ€ÐµÐ¼ÐµÐ½Ð½Ð°Ñ final_json
82
83 end loop;
84
85 close cur1;
86
87
88
89 insert into pim_catalog_product_temporary
90 (Id,
91 family_id,
92 product_model_id,
93 family_variant_id,
94 is_enabled,
95 identifier,
96 raw_values,
97 created,
98 updated,
99 valid, violations)
100 select Id,
101 family_id,
102 product_model_id,
103 family_variant_id,
104 is_enabled,
105 identifier,
106 raw_values,
107 created,
108 updated,
109 valid,
110 concat('[',final_json,']') as violations
111 from jsonDat;
112 ##здеÑÑŒ тоже Ñтрок нет
113 call debug_msg(@enabled, (select concat(id,' FINAL') from jsonDat) );
114 end if;
115
116
117
118end$$
119DELIMITER ;