DROP PROCEDURE IF EXISTS sp_f031ermoload;
DROP FUNCTION IF EXISTS sp_f031ermoload;

CREATE PROCEDURE public.sp_f031ermoload(
	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_f031;

	CREATE TEMP TABLE temp_f031 as
	SELECT 
		idmo, nam_mop, nam_mok, 
		inn, kpp, ogrn, oid_mo, okopf, 
		okfs, addr_j, addr_j_gar, oktmo, email, phone, fax,
		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, 
			NAM_MOP text path './NAM_MOP' null, 
			NAM_MOK text path './NAM_MOK' null, 
			INN text path './INN' null, 
			KPP text path './KPP' null, 
			OGRN text path './OGRN' null, 
			OID_MO text path './OID_MO' null, 
			OKOPF text path './OKOPF' null, 
			OKFS text path './OKFS' null,
			ADDR_J text path './ADDR_J' null, 
			ADDR_J_GAR text path './ADDR_J_GAR' null, 
			OKTMO text path './OKTMO' null, 
			EMAIL text path './EMAIL' null, 
			PHONE text path './PHONE' null,
			FAX text path './FAX' null,
			DATEBEG text path './DATEBEG' null, 
			DATEEND text path './DATEEND' null 
		);


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

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


	UPDATE IES.T_F031_ERMO t1 
	SET NAM_MOP = t2.NAM_MOP
	    ,NAM_MOK = t2.NAM_MOK
	    ,INN = t2.INN
	    ,KPP = t2.KPP
	    ,OGRN = t2.OGRN
	    ,OID_MO = t2.OID_MO
        ,OKOPF = t2.OKOPF
        ,OKFS = t2.OKFS
        ,ADDR_J = t2.ADDR_J
        ,ADDR_J_GAR = t2.ADDR_J_GAR
        ,OKTMO = t2.OKTMO
        ,EMAIL = t2.EMAIL
        ,PHONE = t2.PHONE
        ,FAX = t2.FAX
        ,DATEEND = t2.DATEEND
	FROM temp_f031 t2 
	WHERE t2.IDMO = t1.IDMO and t2.DATEBEG = t1.DATEBEG and t1.Actual = true;

	INSERT INTO IES.T_F031_ERMO
    (
		F031ErmoID, IDMO, NAM_MOP, NAM_MOK, INN, KPP, 
		OGRN, OID_MO, OKOPF, OKFS, ADDR_J, ADDR_J_GAR, 
		OKTMO, EMAIL, PHONE, FAX, DATEBEG, DATEEND,Actual
	)
	select
		gen_random_uuid(), t1.IDMO, t1.NAM_MOP, t1.NAM_MOK, t1.INN, t1.KPP, 
		t1.OGRN, t1.OID_MO, t1.OKOPF, t1.OKFS, t1.ADDR_J, t1.ADDR_J_GAR, 
		t1.OKTMO, t1.EMAIL, t1.PHONE, t1.FAX, t1.DATEBEG, t1.DATEEND , true
	FROM temp_f031 t1
	left join IES.T_F031_ERMO t2 on t2.IDMO = t1.IDMO and t2.DATEBEG = t1.DATEBEG  and t2.Actual = true
	where t2.F031ErmoID is null;
	
	DROP TABLE IF EXISTS temp_f031;
	--select * from IES.T_F031_ERMO where actual = true
	return;
END
$BODY$;

