API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 20th, 2019
78
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 25.50 KB | None | 0 0
raw download clone embed print report
  1. DELIMITER $$
  2. CREATE DEFINER=`root`@`%` PROCEDURE `product_validate_input`(IN jss json)
  3. proc_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.        
  452. end$$
  453. DELIMITER ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!