DROP PROCEDURE IF EXISTS sp_o002okatoload;
DROP FUNCTION IF EXISTS sp_o002okatoload; 

CREATE PROCEDURE public.sp_o002okatoload ("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_o002;
	CREATE TEMP TABLE temp_o002 as
	SELECT 
		TER, 
		KOD1, 
		KOD2, 
		KOD3, 
		RAZDEL, 
		NAME1, 
		NOMAKT, 
		STATUS, 
		CASE WHEN  DATEUTV  = '' THEN NULL ELSE DATEUTV::TIMESTAMP END,
		CASE WHEN  DATEVVED  = '' THEN NULL ELSE DATEVVED::TIMESTAMP END, 
		NOMDESCR, 
		CENTRUM 
	FROM
	XMLTABLE ('/packet/zap' PASSING T 
	COLUMNS 
		TER text path './TER' null, 
		KOD1 text path './KOD1' null, 
		KOD2 text path './KOD2' null, 
		KOD3 text path './KOD3' null, 
		RAZDEL text path './RAZDEL' null, 
		NAME1 text path './NAME1' null, 
		NOMAKT text path './NOMAKT' null, 
		STATUS text path './STATUS' null, 
		DATEUTV text path './DATEUTV' null, 
		DATEVVED text path './DATEVVED' null, 
		NOMDESCR text path './NOMDESCR' null, 
		CENTRUM text path './CENTRUM' null );

	update IES.T_O002_OKATO t2
		set  NAME1 = t1.NAME1
			,CENTRUM = t1.CENTRUM
			,NOMDESCR = t1.NOMDESCR
			,NOMAKT = RIGHT('000'||cast(cast(cast(t1.NOMAKT as decimal(15,6)) as int) as varchar(3)),3)
			,STATUS = t1.STATUS
			,DATA_UPD = t1.DATEUTV
			,ACTUAL = true
		from temp_o002 t1
		where (t1.TER || t1.KOD1 || t1.KOD2 || t1.KOD3 || t1.RAZDEL = t2.TER || t2.KOD1 || t2.KOD2 || t2.KOD3 || t2.RAZDEL) ;

	insert into IES.T_O002_OKATO(OkatoID, TER, KOD1, KOD2, KOD3, RAZDEL, NAME1, CENTRUM, NOMDESCR, NOMAKT, STATUS, DATA_UPD, CODE, ACTUAL)
		select gen_random_uuid(), t1.TER, t1.KOD1, t1.KOD2, t1.KOD3, t1.RAZDEL, t1.NAME1, t1.CENTRUM, t1.NOMDESCR,ca.NOMAKT, t1.STATUS, t1.DATEUTV, t1.TER || t1.KOD1 || t1.KOD2 || t1.KOD3,true
		from temp_o002 t1
		left join IES.T_O002_OKATO t2 on (t1.TER || t1.KOD1 || t1.KOD2 || t1.KOD3 || t1.RAZDEL = t2.TER || t2.KOD1 || t2.KOD2 || t2.KOD3 || t2.RAZDEL)
		left join lateral(select RIGHT('000'||cast(cast(cast(t1.NOMAKT as decimal(15,6)) as int) as varchar(3)),3) as NOMAKT) ca on true
		where t2.OkatoID is null;

		Update  IES.T_O002_OKATO t2
				set ACTUAL = false
		where not exists(select from temp_o002 t1 where t1.TER || t1.KOD1 || t1.KOD2 || t1.KOD3 || t1.RAZDEL = t2.TER || t2.KOD1 || t2.KOD2 || t2.KOD3 || t2.RAZDEL);
		
	drop table if exists temp_o002;

	return;
END
$BODY$;
