DROP PROCEDURE IF EXISTS sp_f017billtypesload;
DROP FUNCTION IF EXISTS sp_f017billtypesload;

CREATE PROCEDURE public.sp_f017billtypesload("Xml" bytea, "FileName" character varying)
LANGUAGE 'plpgsql'
AS $BODY$

	declare T xml;
	declare tempStrXml text;
	
	BEGIN
		
		select convert_from("Xml", 'WIN1251') into tempStrXml;
		
		select replace(tempStrXml, 'encoding="windows-1251"','')::xml into T; 
		
		DROP TABLE IF EXISTS temp_f017;
		
		CREATE TEMP TABLE temp_f017 as 	
		SELECT 
			tf_okato,
			orgType::int, 
			billcod,
			bill_namp,
			bill_namk,
			CASE WHEN  DATEBEG  = '' THEN NULL ELSE DATEBEG::TIMESTAMP END, 
			CASE WHEN  DATEEND  = '' THEN NULL ELSE DATEEND::TIMESTAMP END 
		FROM
		XMLTABLE ('/packet/zap' PASSING T 
		COLUMNS 
			tf_okato text path './tf_okato' null, 
			orgType text path './orgType' null, 
			billcod text path './billcod' null,
			bill_namp text path './bill_namp' null,
			bill_namk text path './bill_namk' null,
			DATEBEG text path './datebeg' null, 
			DATEEND text path './dateend' null );
		
		update IES.T_F017_BILL_TYPES t2
			set  bill_namp=t1.bill_namp
				,bill_namk=t1.bill_namk
				,DATEEND=t1.DATEEND
		from temp_f017 t1
		where t1.tf_okato=t2.TF_OKATO and t1.orgType = t2.orgType and t1.billcod = t2.billcod and t1.DATEBEG = t2.DATEBEG;
		
		insert into IES.T_F017_BILL_TYPES(F017BillTypesID,TF_OKATO,orgType,billcod,bill_namp,bill_namk,DATEBEG,DATEEND)
			select gen_random_uuid(),t1.tf_okato,t1.orgType,t1.billcod, t1.bill_namp, t1.bill_namk, t1.DATEBEG,t1.DATEEND
		from temp_f017 t1
		left join IES.T_F017_BILL_TYPES t2 on (t1.tf_okato=t2.TF_OKATO and t2.orgType = t1.orgType and t2.billcod = t1.billcod and t1.DATEBEG = t2.DATEBEG)
		where t2.F017BillTypesID is null;
		
		DROP TABLE IF EXISTS temp_f017;
		
	End
$BODY$;
