DROP PROCEDURE IF EXISTS sp_f036rukmoload;
DROP FUNCTION IF EXISTS sp_f036rukmoload;

CREATE PROCEDURE public.sp_f036rukmoload(
	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_f036;

	CREATE TEMP TABLE temp_f036 as
	SELECT 
		IDMO,
		OID_MO,
		MCOD,
		UIDMO,
		FAM_RUK,
		IM_RUK,
		OT_RUK,
		TYPE_RUK,
		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, 
			OID_MO text path './OID_MO' null, 
			MCOD text path './MCOD' null, 
			UIDMO text path './UIDMO' null, 
			FAM_RUK text path './FAM_RUK' null, 
			IM_RUK text path './IM_RUK' null, 
			OT_RUK text path './OT_RUK' null, 
			TYPE_RUK text path './TYPE_RUK' 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;


	DROP TABLE IF EXISTS  temp_f036_2; 
	create temp table  temp_f036_2 AS 
	select t1.*, f031.F031ErmoID 
  	FROM temp_f036 t1
  	join f031_filtered  f031 on t1.IDMO = f031.IDMO ;


		-- обновляем актуальность у записей которых уже нет в Фед. справочнике
	update  IES.T_F036_RukMO t1
		set Actual = false 
	where not exists(select 1 from temp_f036_2 t2 where  t1.IDMO = t2.F031ErmoID and t1.FAM_RUK = t2.FAM_RUK  
															and t1.IM_RUK = t2.IM_RUK and t1.TYPE_RUK = t2.TYPE_RUK 
															and t2.DATEBEG = t1.DATEBEG)
	and t1.Actual = true;
	
		-- обновляем актуальность у записей которые есть в Фед. справочнике
	update IES.T_F036_RukMO t1 
		set Actual = true 
	where exists(select 1 from temp_f036_2 t2 where  t1.IDMO = t2.F031ErmoID and t1.FAM_RUK = t2.FAM_RUK  
															and t1.IM_RUK = t2.IM_RUK and t1.TYPE_RUK = t2.TYPE_RUK 
															and t2.DATEBEG = t1.DATEBEG)
	and t1.Actual = false;
	

	UPDATE IES.T_F036_RukMO t1 
	SET 
		   OT_RUK  = t2.OT_RUK
		  ,OID_MO  = t2.OID_MO
		  ,MCOD    = t2.MCOD
		  ,UIDMO   = t2.UIDMO
		  ,DATEEND = t2.DATEEND
	FROM temp_f036_2 t2 
	where t1.IDMO = t2.F031ErmoID and t1.FAM_RUK = t2.FAM_RUK  and t1.IM_RUK = t2.IM_RUK and t1.TYPE_RUK = t2.TYPE_RUK and t2.DATEBEG = t1.DATEBEG and t1.Actual = true;


	INSERT INTO IES.T_F036_RukMO
				(F036RukMOID, IDMO, OID_MO, FAM_RUK, IM_RUK, OT_RUK, TYPE_RUK, DATEBEG, DATEEND,MCOD,UIDMO ,Actual)
		select
				gen_random_uuid(), t1.F031ErmoID, t1.OID_MO, t1.FAM_RUK, t1.IM_RUK, t1.OT_RUK, t1.TYPE_RUK, t1.DATEBEG, t1.DATEEND, t1.MCOD, t1.UIDMO ,true
	FROM temp_f036_2 t1
	left join IES.T_F036_RukMO t2 on t2.IDMO = t1.F031ErmoID  and t1.FAM_RUK = t2.FAM_RUK  and t1.IM_RUK = t2.IM_RUK and t1.TYPE_RUK = t2.TYPE_RUK and t1.DATEBEG = t2.DATEBEG and t2.Actual = true
	where t2.F036RukMOID is null;

	DROP TABLE IF EXISTS temp_f036;
	DROP TABLE IF EXISTS  f031_filtered; 
	DROP TABLE IF EXISTS temp_f036_2;
--select * from IES.T_F036_RukMO where actual = 1
	return;
END
$BODY$;

