DROP PROCEDURE IF EXISTS sp_f032trmoload;
DROP FUNCTION IF EXISTS sp_f032trmoload;

CREATE PROCEDURE public.sp_f032trmoload(
	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_f032;

CREATE TEMP TABLE temp_f032 as
SELECT XMLTABLE.* 
FROM 
XMLTABLE ('/packet/zap' PASSING  T 
COLUMNS 
UIDMO text path './UIDMO' null, 
IDMO text path './IDMO' null, 
MCOD text path './MCOD' null, 
OKTMO_P text path './OKTMO_P' null, 
SUBJ text path './SUBJ' null, 
D_BEGIN timestamp path './D_BEGIN' null, 
D_BEGIN_OMS timestamp path './D_BEGIN_OMS' null,
D_END text path './D_END' null, --текст потому что выдает ошибку : не верный тип данных (пусто нельзя)
NAME_E text path './NAME_E' null, 
OSP text path './OSP' null, 
VID_MO text path './VID_MO' null, 
OID_MO text path './OID_MO' null, 
OID_SPMO text path './OID_SPMO' 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, 
JURADDRESS_INDEX text path './JURADDRESS_INDEX' null, 
JURADDRESS_ADDRESS text path './JURADDRESS_ADDRESS' null, 
GAR_ADDRESS text path './GAR_ADDRESS' null, 
OKFS text path './OKFS' null, 
VEDPRI text path './VEDPRI' null, 
PHONE text path './PHONE' null, 
EMAIL text path './EMAIL' null,
FAX text path './FAX' null,
DATEBEG timestamp path './DATEBEG' null, 
DATEEND timestamp path './DATEEND' null );

update temp_f032 t1 set D_END = null where t1.D_END = ''; --иначе выйдет ошибка при приведении к типу 

-- IF exists (select 1 from temp_f032 where RIGHT(UIDMO,6) <>'000000') then
		
-- 			RAISE EXCEPTION 'Изменилась длинна UIDMO!';
-- 			RETURN ;
-- 		END IF;	

		--для контроля длинны поля
IF exists (select 1 from temp_f032 where length(UIDMO) > 11) then
	
		RAISE EXCEPTION 'Изменилась длинна UIDMO!';
		RETURN ;
	END IF;		
		
-- обновляем актуальность у записей которых уже нет в Фед. справочнике
update IES.T_F032_TRMO t1
	set Actual = false 
	--select *  
where not exists(select 1 from temp_f032 t2 where left(t1.UIDMO,11) = left(t2.UIDMO,11) and t1.D_BEGIN = t2.D_BEGIN)
and t1.Actual = true;

-- обновляем актуальность у записей которые есть в Фед. справочнике
update IES.T_F032_TRMO t1
	set Actual = true 
	--select *  
where  exists(select 1 from temp_f032 t2 where left(t1.UIDMO,11) = left(t2.UIDMO,11) and t1.D_BEGIN = t2.D_BEGIN)
and t1.Actual = false;


	with cte as 
	(
	 select  t1.*, ROW_NUMBER() over (partition by t1.mcod order by t1.D_BEGIN desc) as rn
	 from temp_f032 t1 
	 left join ies.T_F003_MO t2 on t1.MCOD = t2.MCOD 
	 where t2.MCOD is null
	)

	insert into IES.T_F003_MO
			   (TF_OKATO
			   ,MCOD
			   ,NAM_MOP
			   ,NAM_MOK
			   ,FAM_RUK
			   ,IM_RUK
			   ,OT_RUK
			   ,D_BEGIN
			   ,D_END
			   ,NAME_E
			   ,D_EDIT
			   ,IsActive
			   ,Holidays
			   )

	select 
			   left(t1.OKTMO_P,5)  as TF_OKATO
			   ,t1.MCOD
			   ,left(t1.UIDMO,11) --NAM_MOP
			   ,t1.IDMO--NAM_MOK
			   ,'' as FAM_RUK
			   ,'' as IM_RUK
			   ,'' as OT_RUK
			   ,t1.D_BEGIN
			   ,t1.D_END::timestamp
			   ,t1.NAME_E
			   ,t1.DATEBEG
			   ,false as IsActive
			   ,64 as Holidays

	from cte t1 
     where rn = 1;

	UPDATE IES.T_F032_TRMO t2 
		set  SUBJ					= t1.SUBJ
			,NAME_E				= t1.NAME_E
			,DATEBEG				= t1.DATEBEG --        
			,DATEEND				= t1.DATEEND
			,D_BEGIN_OMS			= t1.D_BEGIN_OMS
			,D_END					= t1.D_END::timestamp
			,MCOD					= t1.MCOD
			,OKTMO_P				= t1.OKTMO_P		 
			,osp					= case when t1.OSP = 'true' then 1   when t1.OSP = 'false' then 0 else  t1.OSP::int end  --t1.osp
			,vid_mo				= t1.vid_mo
			,oid_mo				= t1.oid_mo
			,oid_spmo				= t1.oid_spmo
			,nam_mop				= t1.nam_mop
			,nam_mok				= t1.nam_mok
			,inn					= t1.inn
			,kpp					= t1.kpp
			,ogrn					= t1.ogrn
			,juraddress_index		= t1.juraddress_index
			,juraddress_address	= t1.juraddress_address
			,gar_address			= t1.gar_address
			,okfs					= t1.okfs
			,vedpri				= t1.vedpri
			,phone					= t1.phone
			,fax					= t1.fax
			,email					= t1.email
			--select *
		from temp_f032 t1
		  where left(t1.UIDMO,11) = left(t2.UIDMO,11) and t1.D_BEGIN = t2.D_BEGIN 
		and t2.Actual = true;

	INSERT INTO IES.T_F032_TRMO
	           (F032TrmoID
			   ,UIDMO
	           ,IDMO
	           ,MCOD
	           ,OKTMO_P
	           ,SUBJ
	           ,D_BEGIN
	           ,D_BEGIN_OMS
	           ,D_END
	           ,NAME_E
	           ,osp
	           ,vid_mo
	           ,oid_mo
	           ,oid_spmo
	           ,nam_mop
	           ,nam_mok
	           ,inn
	           ,kpp
	           ,ogrn
	           ,juraddress_index
	           ,juraddress_address
	           ,gar_address
	           ,okfs
	           ,vedpri
	           ,phone
	           ,fax
	           ,email
	           ,DATEBEG
	           ,DATEEND
			   ,Actual
			   ) 
		select 
				gen_random_uuid ()
			   ,left(t1.UIDMO,11)
	           ,t1.IDMO
	           ,t1.MCOD
	           ,t1.OKTMO_P
	           ,t1.SUBJ
	           ,t1.D_BEGIN
	           ,t1.D_BEGIN_OMS
	           ,t1.D_END::timestamp
	           ,t1.NAME_E
	           ,case when t1.OSP = 'true' then 1   when t1.OSP = 'false' then 0 else  t1.OSP::int end  --t1.osp
	           ,t1.vid_mo
	           ,t1.oid_mo
	           ,t1.oid_spmo
	           ,t1.nam_mop
	           ,t1.nam_mok
	           ,t1.inn
	           ,t1.kpp
	           ,t1.ogrn
	           ,t1.juraddress_index
	           ,t1.juraddress_address
	           ,t1.gar_address
	           ,t1.okfs
	           ,t1.vedpri
	           ,t1.phone
	           ,t1.fax
	           ,t1.email
	           ,t1.DATEBEG
	           ,t1.DATEEND
			   ,true
			   
	    from temp_f032 t1
		join ies.T_F003_MO f003 on (t1.MCOD = f003.MCOD)
		where not exists(select 1 from IES.T_F032_TRMO t2 where  left(t1.UIDMO,11) = left(t2.UIDMO,11) and t1.D_BEGIN = t2.D_BEGIN and t2.Actual = true);
		
DROP TABLE IF EXISTS temp_f032;

return;
End
$BODY$;
