DROP PROCEDURE IF EXISTS sp_f035umoload;
DROP FUNCTION IF EXISTS sp_f035umoload;

CREATE PROCEDURE  public.sp_f035umoload(
	IN "Xml" bytea,
	IN "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_f035;

	CREATE TEMP TABLE temp_f035 as
	SELECT 
		IDMO,
		MCOD,
		UIDMO,
		OID_MO,
		NAM_UMOP,
		NAM_UMOK,
		INN_U,
		KPP_U,
		NAM_PRO_U,
		OKTMO_U,
		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 
			IDMO text path './IDMO' null, 
			MCOD text path './MCOD' null,
			UIDMO text path './UIDMO' null, 
			OID_MO text path './OID_MO' null, 
			NAM_UMOP text path './NAM_UMOP' null, 
			NAM_UMOK text path './NAM_UMOK' null, 
			INN_U text path './INN_U' null, 
			KPP_U text path './KPP_U' null, 
			NAM_PRO_U text path './NAM_PPO_U' null, 
			OKTMO_U text path './OKTMO_U' null, 
			DATEBEG text path './DATEBEG' null, 
			DATEEND text path './DATEEND' null 
		);

	DROP TABLE IF EXISTS  f031_filtered; 
	create temp table  f031_filtered AS 
	select 
		distinct on (f031.IDMO) f031.IDMO, f031.F031ErmoID 
		from ies.T_F031_ERMO f031 
		where  f031.Actual = true 
		order by f031.IDMO,f031.DATEBEG desc;


  			-- обновляем актуальность у записей которых уже нет в Фед. справочнике
	update  IES.T_F035_UMO t1 
		set Actual = false 
	where not exists(select 1 from temp_f035 t2 
					join f031_filtered f031 on  t2.IDMO = f031.IDMO 
					join  ies.T_F003_MO f003 on t2.MCOD = f003.MCOD
					where f031.F031ErmoID = t1.IDMO and f003.MCOD = t1.MCOD and t1.INN_U = t2.INN_U and t1.KPP_U = t2.KPP_U and t2.DATEBEG = t1.DATEBEG)
	and t1.Actual = true;
	
				-- обновляем актуальность у записей которые есть в Фед. справочнике
	update  IES.T_F035_UMO t1 
		set Actual = true 
	where  exists(select 1 from temp_f035 t2 
					join f031_filtered f031 on  t2.IDMO = f031.IDMO 
					join  ies.T_F003_MO f003 on t2.MCOD = f003.MCOD
					where f031.F031ErmoID = t1.IDMO and f003.MCOD = t1.MCOD and t1.INN_U = t2.INN_U and t1.KPP_U = t2.KPP_U and t2.DATEBEG = t1.DATEBEG)
	and t1.Actual = false;

	
	UPDATE IES.T_F035_UMO t1
	SET 
	       NAM_UMOP = t2.NAM_UMOP
		  ,OID_MO = t2.OID_MO
	      ,NAM_UMOK = t2.NAM_UMOK
	      ,NAM_PRO_U = t2.NAM_PRO_U
	      ,OKTMO_U = t2.OKTMO_U
	      ,DATEEND = t2.DATEEND
		  ,UIDMO = t2.UIDMO
	FROM  temp_f035 t2 
	join f031_filtered f031 on  t2.IDMO = f031.IDMO 
	join ies.T_F003_MO f003 on t2.MCOD = f003.MCOD
	where 
		t1.INN_U = t2.INN_U and t1.KPP_U = t2.KPP_U and t2.DATEBEG = t1.DATEBEG and t1.Actual = true
		and t1.IDMO = f031.F031ErmoID and t1.mcod = f003.MCOD;

	INSERT INTO IES.T_F035_UMO
			(F035UmoID, IDMO, MCOD, OID_MO, NAM_UMOP, NAM_UMOK, INN_U, KPP_U, NAM_PRO_U, OKTMO_U, DATEBEG, DATEEND,Actual, UIDMO)
	select
			gen_random_uuid(), f031.F031ErmoID, t1.MCOD, t1.OID_MO, t1.NAM_UMOP, t1.NAM_UMOK, t1.INN_U, t1.KPP_U, t1.NAM_PRO_U, t1.OKTMO_U, t1.DATEBEG, t1.DATEEND, true, t1.UIDMO
	FROM temp_f035 t1
	join f031_filtered f031 on  t1.IDMO = f031.IDMO 
	join ies.T_F003_MO f003 on t1.MCOD = f003.MCOD
	left join IES.T_F035_UMO t2 on t2.IDMO = f031.F031ErmoID and  t2.mcod = f003.MCOD and t1.INN_U = t2.INN_U and t1.KPP_U = t2.KPP_U and t2.DATEBEG = t1.DATEBEG and t2.Actual = true
	where t2.F035UmoID is null;

	--select * from IES.T_F035_UMO
	DROP TABLE IF EXISTS temp_f035;
	DROP TABLE IF EXISTS  f031_filtered; 
	return;
END
$BODY$;
