DROP PROCEDURE IF EXISTS sp_f016themeload;
DROP FUNCTION IF EXISTS sp_f016themeload;

CREATE PROCEDURE public.sp_f016themeload("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_f016;
		
		CREATE TEMP TABLE temp_f016 as 		
		SELECT 
			KOD_T,
			TNAME,
			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_T text path './KOD_T' null, 
			TNAME text path './TNAME' null, 
			DATEBEG text path './DATEBEG' null, 
			DATEEND text path './DATEEND' null );
		
		update IES.T_F016_THEME t2
			set  Name = t1.TNAME
				,DATEEND = t1.DATEEND
		from temp_f016 t1
		where t1.KOD_T=t2.Code and t1.DATEBEG = t2.DateBegin;
		
		insert into IES.T_F016_THEME(F016ThemeID,Code,Name,DateBegin,DATEEND)
			select gen_random_uuid(), t1.KOD_T, t1.TNAME, t1.DATEBEG, t1.DATEEND
		from temp_f016 t1
		left join IES.T_F016_THEME t2 on (t1.KOD_T=t2.Code and t1.DATEBEG = t2.DateBegin)
		where t2.F016ThemeID is null;
		
		DROP TABLE IF EXISTS temp_f016;
		
	End
$BODY$;
