IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DispInfoLoad]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DispInfoLoad]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_DispInfoLoad]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_DispInfoLoad]
GO
-- =============================================
-- Author:  (C) Vitacard System 2011
--              Шатков В.А.
-- Create date: 2011-12-02
-- Description: Загрузка счет-реестра ОМС персональные данные, Севастополь
-- =============================================

CREATE PROCEDURE [rds].[sp_DispInfoLoad]
  @FileID uniqueidentifier
 ,@xml xml
 ,@Worker uniqueidentifier 
AS 
BEGIN 
 
 
SELECT 
	@FileID AS [FileID]
  ,T.c.value('./VERSION[1]', 'varchar(5)') AS [VERSION]
  ,T.c.value('./DATA[1]', 'datetime') AS [DATA]
  ,T.c.value('./FILENAME[1]', 'varchar(26)') AS [FILENAME]
   ,T.c.value('./SMO[1]', 'int') AS [SMO]
  , CAST(CAST(CAST(GETDATE() as float) as INT) as datetime) as [ReceivedDate]
  , GETDATE() as [ReceivedTime]
INTO #ZGLV
FROM @xml.nodes('/INFO_LIST/ZGLV') T(c)   

 SELECT 
 NewId() as [ItemID]
  ,T.c.value('../ZGLV[1]/SMO[1]', 'int') AS [SMO]
 ,T.c.value('./CODE_MO[1]', 'int') AS [MO]
 ,T.c.value('./YEAR[1]', 'int') AS [YEAR]
 ,T.c.value('./FAM[1]', 'varchar(40)') AS [FAM]
 ,T.c.value('./IM[1]', 'varchar(40)') AS [IM]
 ,T.c.value('./OT[1]', 'varchar(40)') AS [OT]
 ,T.c.value('./DR[1]', 'datetime') AS [DR]
 ,T.c.value('./ENP[1]', 'varchar(16)') AS [ENP]
 ,T.c.value('./S_POL[1]', 'varchar(10)') AS [S_POL]
 ,T.c.value('./N_POL[1]', 'varchar(20)') AS [N_POL]
 ,T.c.value('./IDDT[1]', 'varchar(3)') AS [IDDT]
 ,T.c.value('./F_INF[1]', 'int') AS [F_INF]
 ,T.c.value('./D_INF[1]', 'datetime') AS [D_INF]
 ,T.c.value('./N_INF[1]', 'varchar(50)') AS [N_INF]
 ,T.c.value('./TIP_INFO[1]', 'int') AS [TIP_INFO]
 ,T.c.value('./RESULT[1]', 'varchar(50)') AS [RESULT]
INTO #ZAP
FROM @xml.nodes('/INFO_LIST/INFO') T(c)   


--------

 update DispFileInfo
 set DispFileInfo.[VERSION] = zglv.[VERSION]
 , DispFileInfo.[ReceivedDate] = zglv.[ReceivedDate], DispFileInfo.ReceivedTime = zglv.[ReceivedTime], DispFileInfo.[Worker] = @Worker
 from [RDS].T_DISP_DIRECT_INFO_FILE DispFileInfo
 join #ZGLV zglv on zglv.[FileID]=DispFileInfo.[FileID]
 where DispFileInfo.[FileID] = @FileID
------------------



 INSERT INTO [RDS].T_DISP_DIRECT_INFO
 (
      [DispDirectInfoID]
      ,[Date]
      ,[DispDirect]
      ,[DispDirectInfoType]
      ,[DispDirectInfoFile]
	  ,[Addr]
	  ,[CODE_MO]
	  ,[CODE_SMO]
	  ,V016Dispt
	  ,[Result]
	  ,[ENP]
	  ,[TIP_INFO]

 )
 select zap.[ItemID]
	,zap.[D_INF]
	,null--
	,zap.[F_INF]
	,@FileID
	,zap.[N_INF]
	,zap.[MO]
	,zap.[SMO]
	,v016.V016ID
	,zap.[RESULT]
	,zap.[ENP]
	,zap.[TIP_INFO]
 from #ZAP zap
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = zap.[IDDT] and v016.DATEEND is null
-----------------

--step 0
update t1
set t1.[DispDirect] =  (
  select top 1 [DispDirectID] from [RDS].T_DISP_DIRECT t2
	left join [RDS].T_DISP_DIRECT_FACT ddf on ddf.[DispDirect] = t2.[DispDirectID]
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = zap.[IDDT] and v016.DATEEND is null
  where (t2.ENP = zap.ENP and t2.V016Dispt = v016.V016ID and t2.[YEAR] = zap.[YEAR] 
	and ddf.[DispDirectFactID] is null)   
 )
from [RDS].T_DISP_DIRECT_INFO t1
join #ZAP zap on zap.[ItemID] = t1.[DispDirectInfoID]

--step 1
update t1
set t1.[DispDirect] =  (
  select top 1 [DispDirectID] from [RDS].T_DISP_DIRECT t2
	left join [RDS].T_DISP_DIRECT_FACT ddf on ddf.[DispDirect] = t2.[DispDirectID]
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = zap.[IDDT] and v016.DATEEND is null
  where (t2.FAM = zap.FAM and t2.IM = zap.IM and t2.OT = zap.OT and t2.DR = zap.DR and t2.V016Dispt = v016.V016ID  and t2.[YEAR] = zap.[YEAR] 
	and ddf.[DispDirectFactID] is null)     
 )
from [RDS].T_DISP_DIRECT_INFO t1
join #ZAP zap on zap.[ItemID] = t1.[DispDirectInfoID] 
where t1.[DispDirect] is null

--step 2
update t1
set t1.[DispDirect] =  (
  select top 1 [DispDirectID] from [RDS].T_DISP_DIRECT t2
	left join [RDS].T_DISP_DIRECT_FACT ddf on ddf.[DispDirect] = t2.[DispDirectID]
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = zap.[IDDT] and v016.DATEEND is null
  where (t2.FAM = zap.FAM and t2.IM = zap.IM and t2.OT = zap.OT and isnull(t2.DOCSER, '') +  t2.DOCNUM = ISNULL(zap.[S_POL],'') + zap.[N_POL] and t2.V016Dispt = v016.V016ID  and t2.[YEAR] = zap.[YEAR] 
	and ddf.[DispDirectFactID] is null)   
 )
from [RDS].T_DISP_DIRECT_INFO t1
join #ZAP zap on zap.[ItemID] = t1.[DispDirectInfoID]
where t1.[DispDirect] is null

--step 3
update t1
set t1.[DispDirect] =  (
  select top 1 [DispDirectID] from [RDS].T_DISP_DIRECT t2
	left join [RDS].T_DISP_DIRECT_FACT ddf on ddf.[DispDirect] = t2.[DispDirectID]
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = zap.[IDDT] and v016.DATEEND is null
  where (t2.FAM = zap.FAM and t2.IM = zap.IM and t2.DR = zap.DR and isnull(t2.DOCSER, '') +  t2.DOCNUM = ISNULL(zap.[S_POL],'') + zap.[N_POL] and t2.V016Dispt = v016.V016ID  and t2.[YEAR] = zap.[YEAR] 
	and ddf.[DispDirectFactID] is null)   
 )
from [RDS].T_DISP_DIRECT_INFO t1
join #ZAP zap on zap.[ItemID] = t1.[DispDirectInfoID]
where t1.[DispDirect] is null

--step 4
update t1
set t1.[DispDirect] =  (
  select top 1 [DispDirectID] from [RDS].T_DISP_DIRECT t2
	left join [RDS].T_DISP_DIRECT_FACT ddf on ddf.[DispDirect] = t2.[DispDirectID]
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = zap.[IDDT] and v016.DATEEND is null
  where (t2.FAM = zap.FAM and t2.OT = zap.OT and t2.DR = zap.DR and isnull(t2.DOCSER, '') +  t2.DOCNUM = ISNULL(zap.[S_POL],'') + zap.[N_POL] and t2.V016Dispt = v016.V016ID  and t2.[YEAR] = zap.[YEAR] 
	and ddf.[DispDirectFactID] is null)   
 )
from [RDS].T_DISP_DIRECT_INFO t1
join #ZAP zap on zap.[ItemID] = t1.[DispDirectInfoID]
where t1.[DispDirect] is null

--step 5
update t1
set t1.[DispDirect] =  (
  select top 1 [DispDirectID] from [RDS].T_DISP_DIRECT t2
	left join [RDS].T_DISP_DIRECT_FACT ddf on ddf.[DispDirect] = t2.[DispDirectID]
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = zap.[IDDT] and v016.DATEEND is null
  where (t2.IM = zap.IM and t2.OT = zap.OT and t2.DR = zap.DR and isnull(t2.DOCSER, '') +  t2.DOCNUM = ISNULL(zap.[S_POL],'') + zap.[N_POL] and t2.V016Dispt = v016.V016ID  and t2.[YEAR] = zap.[YEAR] 
	and ddf.[DispDirectFactID] is null)   
 )
from [RDS].T_DISP_DIRECT_INFO t1
join #ZAP zap on zap.[ItemID] = t1.[DispDirectInfoID]
where t1.[DispDirect] is null

--step 6
update t1
set t1.[DispDirect] =  (
  select top 1 [DispDirectID] from [RDS].T_DISP_DIRECT t2
	left join [RDS].T_DISP_DIRECT_FACT ddf on ddf.[DispDirect] = t2.[DispDirectID]
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = zap.[IDDT] and v016.DATEEND is null
  where (isnull(t2.DOCSER, '') +  t2.DOCNUM = ISNULL(zap.[S_POL],'') + zap.[N_POL] and t2.V016Dispt = v016.V016ID  and t2.[YEAR] = zap.[YEAR] 
	and ddf.[DispDirectFactID] is null)   
 )
from [RDS].T_DISP_DIRECT_INFO t1
join #ZAP zap on zap.[ItemID] = t1.[DispDirectInfoID]
where t1.[DispDirect] is null


DROP TABLE #ZAP
DROP TABLE #ZGLV
END