DROP PROCEDURE IF EXISTS sp_f015federaldistrictload;
DROP FUNCTION IF EXISTS sp_f015federaldistrictload;

CREATE PROCEDURE public.sp_f015federaldistrictload("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_f015;
		
		CREATE TEMP TABLE temp_f015 as 		
		SELECT 
			KOD_OK::int,
			OKRNAME, 
			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 
			KOD_OK text path './KOD_OK' null, 
			OKRNAME text path './OKRNAME' null,
			DATEBEG text path './DATEBEG' null, 
			DATEEND text path './DATEEND' null );
		
		update IES.T_F015_FEDERAL_DISTRICT t2
			set  OKRNAME=t1.OKRNAME
				,DATEEND=t1.DATEEND
		from temp_f015 t1
		where t1.KOD_OK=t2.KOD_OK and t1.DATEBEG = t2.DATEBEG;
		
		insert into IES.T_F015_FEDERAL_DISTRICT(KOD_OK,OKRNAME,DATEBEG,DATEEND)
			select t1.KOD_OK, t1.OKRNAME, t1.DATEBEG, t1.DATEEND
		from temp_f015 t1
		where not exists(select 1 from IES.T_F015_FEDERAL_DISTRICT t2 where (t1.KOD_OK = t2.KOD_OK and t1.DATEBEG = t2.DATEBEG));
		
		DROP TABLE IF EXISTS temp_f015;
		
	End
	
$BODY$;

