DROP PROCEDURE IF EXISTS sp_f037licmoload;
DROP FUNCTION IF EXISTS sp_f037licmoload;

CREATE PROCEDURE  public.sp_f037licmoload(
	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_f037;

CREATE TEMP TABLE temp_f037 as
SELECT XMLTABLE.* 
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, 
N_DOC text path './N_DOC' null, 
DATEBEG timestamp path './DATEBEG' null, 
DATEEND timestamp path './DATEEND' null );

	DROP TABLE IF EXISTS temp_f037_2;

--что бы руками не создавать таблицу
	create temp table  temp_f037_2 AS 
	select t1.* , gen_random_uuid() as F031ErmoID,gen_random_uuid() as F032TrmoID from temp_f037 t1 where 1=0;
		
	with cte_f031 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
	)
	, cte_f032 AS
	(
		select 
		distinct on (f032.UIDMO) f032.UIDMO, f032.F032TrmoID 
		from ies.T_F032_TRMO f032 
		where  f032.Actual = true 
		order by f032.UIDMO,f032.D_BEGIN desc
	)
	
	INSERT INTO temp_f037_2 
	select t1.* , f031.F031ErmoID,f032.F032TrmoID
	from temp_f037 t1
	join cte_f031 f031 on t1.IDMO = f031.IDMO 
	join cte_f032 f032 on t1.UIDMO = f032.UIDMO;


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

	-- обновляем актуальность у записей которые есть в Фед. справочнике
	update IES.T_F037_LicMO t1 
		set Actual = true 
	where exists(select 1 from temp_f037_2 t2 where  t1.IDMO = t2.F031ErmoID and t1.UIDMO = t2.F032TrmoID and t1.N_DOC = t2.N_DOC and t1.DATEBEG = t2.DATEBEG)
	and t1.Actual = false;
  
	UPDATE  IES.T_F037_LicMO t1 
	SET 
		   OID_MO  = t2.OID_MO
		  ,MCOD    = t2.MCOD
		  ,DATEEND = t2.DATEEND
	FROM temp_f037_2 t2
		where
		t1.IDMO = t2.F031ErmoID and t1.UIDMO = t2.F032TrmoID and t1.N_DOC = t2.N_DOC and t1.DATEBEG = t2.DATEBEG and t1.Actual=true;

	INSERT INTO IES.T_F037_LicMO 
				(F037LicMOID, IDMO, OID_MO, MCOD, UIDMO, N_DOC, DATEBEG, DATEEND, Actual)
		select
				gen_random_uuid(), t1.F031ErmoID, t1.OID_MO, t1.MCOD, t1.F032TrmoID, t1.N_DOC, t1.DATEBEG, t1.DATEEND,true
	FROM temp_f037_2 t1
	left join  IES.T_F037_LicMO  t2 on t2.IDMO = t1.F031ErmoID and t2.UIDMO = t1.F032TrmoID  and t1.N_DOC = t2.N_DOC and t1.DATEBEG = t2.DATEBEG and t2.Actual=true
	where t2.F037LicMOID is null;


	DROP TABLE IF EXISTS temp_f037;
	DROP TABLE IF EXISTS temp_f037_2;

return;
End
$BODY$;

