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

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

  IF OBJECT_ID('dbo.T_F033_SPMO_BACKUP','U') IS NULL
		begin
			SELECT * INTO dbo.T_F033_SPMO_BACKUP FROM ies.T_F033_SPMO 
		end

  SELECT 
   T.c.value('./UIDSPMO[1]', 'VARCHAR(17)') AS UIDSPMO
  ,T.c.value('./IDSPMO[1]', 'VARCHAR(17)') AS IDSPMO
  ,T.c.value('./NAM_SPMO[1]', 'VARCHAR(2000)') AS NAM_SPMO
  ,T.c.value('./NAM_SK_SPMO[1]', 'VARCHAR(2000)') AS NAM_SK_SPMO
  ,T.c.value('./PHONE[1]', 'VARCHAR(255)') AS PHONE
  ,T.c.value('./OSP[1]', 'VARCHAR(1)') AS OSP
  ,T.c.value('./VID_SPMO[1]', 'VARCHAR(2000)') AS VID_SPMO
  ,T.c.value('./OID_SPMO[1]', 'VARCHAR(50)') AS OID_SPMO  
  ,case when T.c.value('./DATEBEG[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEBEG[1]', 'varchar(20)'),103) end AS DATEBEG
  ,case when T.c.value('./DATEEND[1]', 'varchar(20)')='' then null else CONVERT(datetime,T.c.value('./DATEEND[1]', 'varchar(20)'),103) end AS DATEEND
  into #temp_f033
  FROM   @Xml.nodes('/packet/zap') T(c)

--проставим UIDSPMO хоть как то для записей у которых его нет
update t1 set t1.UIDSPMO = ca.UIDSPMO
from  ies.[T_F033_SPMO]  t1 
cross apply (select top 1 * from  #temp_f033  t2 where t2.IDSPMO = t1.IDSPMO order by t2.DATEBEG desc) ca
where (t1.uidspmo = '' or t1.uidspmo is null) 

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

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


UPDATE t1 
SET 
		[IDSPMO] = t2.IDSPMO
      ,[NAM_SPMO] = t2.NAM_SPMO
      ,[NAM_SK_SPMO] = t2.NAM_SK_SPMO
      ,[PHONE] = t2.PHONE
      ,[OSP] = t2.OSP
      ,[VID_SPMO] = t2.VID_SPMO
      ,[OID_SPMO] = t2.OID_SPMO
      ,[DATEEND] = t2.DATEEND
FROM [IES].[T_F033_SPMO] t1
join #temp_f033 t2 on t2.UIDSPMO = t1.UIDSPMO and t2.DATEBEG = t1.DATEBEG and t1.Actual = 1 

INSERT INTO [IES].[T_F033_SPMO]
			(F033SpmoID,UIDSPMO, IDSPMO, NAM_SPMO, NAM_SK_SPMO, PHONE, OSP, VID_SPMO, OID_SPMO, DATEBEG, DATEEND,Actual)
	select
			NEWID(), t1.UIDSPMO ,t1.IDSPMO, t1.NAM_SPMO, t1.NAM_SK_SPMO, t1.PHONE, t1.OSP, t1.VID_SPMO, t1.OID_SPMO, t1.DATEBEG, t1.DATEEND,1
FROM #temp_f033 t1
left join [IES].[T_F033_SPMO] t2 on t2.UIDSPMO = t1.UIDSPMO and t2.DATEBEG = t1.DATEBEG and t2.Actual = 1
where t2.F033SpmoID is null 

drop table #temp_f033
--select * from [IES].[T_F033_SPMO]
END
