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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

	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)


	;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]
			   ,t1.UIDMO --[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]					= 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 t1.UIDMO = t2.UIDMO and t1.D_BEGIN = t2.D_BEGIN
	

	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])
			  
		select 
				NEWID()
			   ,t1.[UIDMO]
	           ,t1.[IDMO]
	           ,t1.[MCOD]
	           ,t1.[OKTMO_P]
	           ,t1.[SUBJ]
	           ,t1.[D_BEGIN]
	           ,t1.[D_BEGIN_OMS]
	           ,t1.[D_END]
	           ,t1.[NAME_E]
	           ,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]
	    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  t1.UIDMO = t2.UIDMO and t1.D_BEGIN = t2.D_BEGIN)
	
END