F8fWswSU

1DELIMITER $$
2CREATE DEFINER=`root`@`%` PROCEDURE `product_validate_form_json`(IN price nvarchar(20), IN classifierId int, IN prodId int )
3begin
4
5	declare final_json mediumtext CHARACTER SET utf8  COLLATE utf8_unicode_ci default '';
6	declare done int default false;
7   
8	#статус валидации
9	declare stat int;
10        
11	declare word_id int;
12	declare word_name NVARCHAR(150) COLLATE utf8_unicode_ci;
13    
14    declare cur1 cursor for
15	select name from kt_stop_word where isActive = 1;
16        
17	declare continue handler for not found set done = TRUE;
18        
19	set final_json = '';
20	set stat = 0;
21	SET @enabled = true;
22    call debug_msg(@enabled, (select concat('prod ID:', prodId)) );     
23        
24    if ifnull(price,0) <=0
25    then 
26		set final_json = '{"code": "price-empty"}';
27    end if;
28    
29       
30     #валидация на стоп-слова
31     		            
32		CREATE TEMPORARY TABLE IF NOT EXISTS jsonDat 
33                 AS (
34		SELECT 
35        id,
36        family_id, 
37		product_model_id, 
38		family_variant_id,
39		is_enabled,
40		identifier,
41		raw_values,
42		created,
43		updated,
44		valid,
45		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,
46		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,
47		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,
48		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,
49		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,
50		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,
51		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,
52		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,
53		json_unquote(json_extract(raw_values, '$."delivery_places"."<all_channels>"."<all_locales>"')) as delivery_places,
54		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,
55		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,
56		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,
57		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,
58		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,
59		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,
60		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
61        FROM pim_catalog_product        
62        where id = prodId
63                 );
64                 
65		
66        call debug_msg(@enabled, (select concat(id, ' AFTER INSERT') from jsonDat) );   
67		#вот тут уже возвращаетсчя NULL 
68		
69		#...бля-бла-бла...
70		
71		end$$
72		delimiter ;