
if exists ( select * from sys.procedures p where p.name = 'sp_F032TRMOLoad' ) DROP PROCEDURE [dbo].sp_F032TRMOLoad
GO

CREATE PROCEDURE [dbo].[sp_F032TRMOLoad]
  @Xml xml
 ,@FileName varchar(50)
AS 
BEGIN

--на всякий 
  IF OBJECT_ID('dbo.T_F032_TRMO_BACKUP','U') IS NULL
		begin
			SELECT * INTO dbo.T_F032_TRMO_BACKUP FROM ies.T_F032_TRMO
		end

	SELECT 
		 T.c.value('./UIDMO[1]', 'VARCHAR(17)') AS UIDMO
		,T.c.value('./IDMO[1]', 'VARCHAR(17)') AS IDMO
		,T.c.value('./MCOD[1]', 'VARCHAR(6)') AS MCOD
		,T.c.value('./OKTMO_P[1]', 'VARCHAR(11)') AS OKTMO_P
		,T.c.value('./SUBJ[1]', 'VARCHAR(150)') AS SUBJ
		,CASE WHEN T.c.value('./D_BEGIN[1]', 'VARCHAR(20)')='' THEN NULL ELSE CONVERT(DATETIME,T.c.value('./D_BEGIN[1]', 'VARCHAR(20)'),104) END AS D_BEGIN
		,CASE WHEN T.c.value('./D_BEGIN_OMS[1]', 'VARCHAR(20)')='' THEN NULL ELSE CONVERT(DATETIME,T.c.value('./D_BEGIN_OMS[1]', 'VARCHAR(20)'),104) END AS D_BEGIN_OMS
		,CASE WHEN T.c.value('./D_END[1]', 'VARCHAR(20)')='' THEN NULL ELSE CONVERT(DATETIME,T.c.value('./D_END[1]', 'VARCHAR(20)'),104) END AS D_END
		,T.c.value('./NAME_E[1]', 'VARCHAR(1)') AS NAME_E
		,T.c.value('./OSP[1]', 'VARCHAR(6)') AS OSP
		,T.c.value('./VID_MO[1]', 'VARCHAR(500)') AS VID_MO
		,T.c.value('./OID_MO[1]', 'VARCHAR(255)') AS OID_MO
		,T.c.value('./OID_SPMO[1]', 'VARCHAR(255)') AS OID_SPMO
		,T.c.value('./NAM_MOP[1]', 'VARCHAR(500)') AS NAM_MOP
		,T.c.value('./NAM_MOK[1]', 'VARCHAR(255)') AS NAM_MOK
		,T.c.value('./INN[1]', 'VARCHAR(50)') AS INN
		,T.c.value('./KPP[1]', 'VARCHAR(50)') AS KPP
		,T.c.value('./OGRN[1]', 'VARCHAR(50)') AS OGRN
		,T.c.value('./JURADDRESS_INDEX[1]', 'VARCHAR(50)') AS JURADDRESS_INDEX
		,T.c.value('./JURADDRESS_ADDRESS[1]', 'VARCHAR(500)') AS JURADDRESS_ADDRESS
		,T.c.value('./GAR_ADDRESS[1]', 'UNIQUEIDENTIFIER') AS GAR_ADDRESS
		,T.c.value('./OKFS[1]', 'VARCHAR(50)') AS OKFS
		,T.c.value('./VEDPRI[1]', 'VARCHAR(50)') AS VEDPRI
		,T.c.value('./PHONE[1]', 'VARCHAR(100)') AS PHONE
		,T.c.value('./FAX[1]', 'VARCHAR(100)') AS FAX
		,T.c.value('./EMAIL[1]', 'VARCHAR(100)') AS EMAIL
		,CASE WHEN T.c.value('./DATEBEG[1]', 'VARCHAR(20)')='' THEN NULL ELSE CONVERT(DATETIME,T.c.value('./DATEBEG[1]', 'VARCHAR(20)'),104) END AS DATEBEG
		,CASE WHEN T.c.value('./DATEEND[1]', 'VARCHAR(20)')='' THEN NULL ELSE CONVERT(DATETIME,T.c.value('./DATEEND[1]', 'VARCHAR(20)'),104) END AS DATEEND
	into #F032
	from @xml.nodes('/packet/zap') T(c)

	--20240523 поменяли обратно на 11 символов, поэтому сделал другую проверку
	--IF exists (select 1 from #F032 where RIGHT(UIDMO,6) <>'000000') 
	--	BEGIN
	--		RAISERROR('Изменилась длинна UIDMO!',16,1)
	--		RETURN 
	--	END	

		--для контроля длинны поля
	IF exists (select 1 from #F032 where len(UIDMO) > 11) 
		BEGIN
			RAISERROR('Изменилась длинна UIDMO!',16,1)
			RETURN 
		END	

		-- обновляем актуальность у записей которых уже нет в Фед. справочнике
update t1 
	set Actual = 0 
from [IES].[T_F032_TRMO] t1
where not exists(select 1 from #F032 t2 where left(t1.UIDMO,11) = left(t2.UIDMO,11) and t1.D_BEGIN = t2.D_BEGIN)
and t1.Actual = 1

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

	;with cte as 
	(
	 select  t1.*, ROW_NUMBER() over (partition by t1.mcod order by t1.D_BEGIN desc) as rn
	 from #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]
			   ,t1.[NAME_E]
			   ,t1.[DATEBEG]
			   ,0 as [IsActive]
			   ,64 as [Holidays]

	from cte t1 
     where rn = 1


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

	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 
				NEWID()
			   ,left(t1.UIDMO,11)
	           ,t1.[IDMO]
	           ,t1.[MCOD]
	           ,t1.[OKTMO_P]
	           ,t1.[SUBJ]
	           ,t1.[D_BEGIN]
	           ,t1.[D_BEGIN_OMS]
	           ,t1.[D_END]
	           ,t1.[NAME_E]
	           ,case when t1.OSP = 'true' then 1   when t1.OSP = 'false' then 0 else t1.OSP 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]
			   ,1
	    from #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 = 1)
	
END
