DROP PROCEDURE IF EXISTS sp_f038addrmpload;
DROP FUNCTION IF EXISTS sp_f038addrmpload;

CREATE PROCEDURE public.sp_f038addrmpload(
	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_f038;

	CREATE TEMP TABLE temp_f038 as
	SELECT
			IDADDRESS,
			UIDMO,
			UIDSPMO,
			N_DOC,
			ADDR,
			ADDR_GAR,
			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 
	IDADDRESS text path './IDADDRESS' null, 
	UIDMO text path './UIDMO' null, 
	UIDSPMO text path './UIDSPMO' null, 
	N_DOC text path './N_DOC' null,
	ADDR text path './ADDR' null, 
	ADDR_GAR text path './ADDR_GAR' null, 
	DATEBEG text path './DATEBEG' null, 
	DATEEND text path './DATEEND' null );

	CREATE  INDEX IF NOT EXISTS id_IDADDRESS
	ON temp_f038
		(IDADDRESS);	
	CREATE  INDEX IF NOT EXISTS id_IDSPMO
	ON temp_f038
		(UIDSPMO);	
	CREATE  INDEX IF NOT EXISTS id_UIDMO
	ON temp_f038
		(UIDMO);

	DROP TABLE IF EXISTS f032_limited;
	--оптимизация
	CREATE TEMP TABLE f032_limited as
	SELECT DISTINCT ON (LEFT(f032.UIDMO, 11))
        f032.F032TrmoID, LEFT(f032.UIDMO, 11) AS UIDMO_prefix
    FROM ies.t_f032_trmo f032
    WHERE f032.Actual = true
    ORDER BY LEFT(f032.UIDMO, 11), f032.D_BEGIN DESC;

-- обновляем актуальность у записей которых уже нет в Фед. справочнике
	update  ies.t_f038_addrmp t1
		set Actual = false 
	where not exists(select 1 from temp_f038 t2 
						JOIN f032_limited f032 ON t2.UIDMO = f032.UIDMO_prefix
						where t1.IDADDRESS = t2.IDADDRESS and f032.F032TrmoID = t1.UIDMO and t2.UIDSPMO = t1.UIDSPMO and t1.DATEBEG = t2.DATEBEG )
	and t1.Actual = true;


	UPDATE ies.t_f038_addrmp t1
	SET 
		 ADDR = t2.ADDR
		,ADDR_GAR = t2.ADDR_GAR
		,N_DOC = t2.N_DOC
		,DATEEND = t2.DATEEND
	FROM  temp_f038 t2 
	JOIN f032_limited f032 ON t2.UIDMO = f032.UIDMO_prefix
	where  t1.IDADDRESS = t2.IDADDRESS and  t2.UIDSPMO = t1.UIDSPMO and t1.UIDMO = f032.F032TrmoID and t1.DATEBEG = t2.DATEBEG and t1.Actual = true;

	INSERT INTO ies.t_f038_addrmp
			(F038AddrmpID, IDADDRESS, UIDMO, UIDSPMO, N_DOC, ADDR, ADDR_GAR, DATEBEG, DATEEND,Actual,idspmo)
	select
			gen_random_uuid(), t1.IDADDRESS, f032.F032TrmoID, t1.UIDSPMO, t1.N_DOC, t1.ADDR, t1.ADDR_GAR, t1.DATEBEG, t1.DATEEND,true,''
	FROM temp_f038 t1
	JOIN f032_limited f032 ON t1.UIDMO = f032.UIDMO_prefix
	left join ies.t_f038_addrmp t2 on t1.IDADDRESS = t2.IDADDRESS and t2.UIDSPMO = t1.UIDSPMO and t2.UIDMO = f032.F032TrmoID  and t1.DATEBEG = t2.DATEBEG and t2.Actual =true 
	where t2.F038AddrmpID is null;

	DROP TABLE IF EXISTS temp_f038;
	DROP TABLE IF EXISTS f032_limited;
-- select * from IES.T_F038_ADDRMP limit 3
return;
End
$BODY$;

--select * from ies.T_F038_ADDRMP limit 200 154985
