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

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

-- =============================================
-- Description: Загрузка фактов прохождения диспансеризации
-- =============================================
CREATE PROCEDURE [rds].[sp_DispDirectFactLoad]
  @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('./YEAR[1]', 'int') AS [YEAR]
  ,T.c.value('./MONTH[1]', 'int') AS [MONTH]
  ,T.c.value('./FILENAME[1]', 'varchar(26)') AS [FILENAME]
  ,T.c.value('./IDDT[1]', 'varchar(3)') AS [IDDT]
  , CAST(CAST(CAST(GETDATE() as float) as INT) as datetime) as [ReceivedDate]
  , GETDATE() as [ReceivedTime]
INTO #ZGLV
FROM @xml.nodes('/FACT_LIST/ZGLV') T(c)   

 SELECT 
 NewId() as [ItemID]
 ,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('./CODE_MO[1]', 'varchar(6)') AS [CODE_MO]
 ,T.c.value('./NSCHET[1]', 'varchar(15)') AS [NSCHET]
 ,T.c.value('./DATE_B[1]', 'datetime') AS [DATE_B]
 ,T.c.value('./DATE_E[1]', 'datetime') AS [DATE_E]
 ,T.c.value('./ISHOD[1]', 'int') AS [ISHOD]
 ,T.c.value('./RESULT[1]', 'int') AS [RESULT]
 ,T.c.value('../ZGLV[1]/YEAR[1]', 'int') AS [YEAR]
 ,T.c.value('../ZGLV[1]/MONTH[1]', 'int') AS [MONTH]
 ,T.c.value('../ZGLV[1]/IDDT[1]', 'varchar(3)') AS [IDDT]
 ,T.c.value('./PR_D_N[1]', 'int') AS [PR_D_N]

INTO #FACT
FROM @xml.nodes('/FACT_LIST/FACT') T(c) 

 SELECT 
 NewId() as [ItemID]
 ,T.c.value('../ENP[1]', 'varchar(16)') AS [ENP]
 ,T.c.value('../NSCHET[1]', 'varchar(15)') AS [NSCHET]
 ,T.c.value('./VID_CODE[1]', 'int') AS [VID_CODE]

INTO #DESTINATION
FROM @xml.nodes('/FACT_LIST/FACT/DESTINATION') T(c) 

ALTER TABLE #FACT
ADD DD_ID varchar(36);

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

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

--step 2
update t1
set t1.DD_ID =  (
  select top 1 DispDirectID from [RDS].T_DISP_DIRECT t2
	left join  [RDS].T_DISP_DIRECT_FACT t3 on t3.DispDirect = t2.DispDirectID
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = t1.[IDDT] and v016.DATEEND is null
  where (t2.FAM = t1.FAM and t2.IM = t1.IM and t2.DR = t1.DR
   and t2.[YEAR] = t1.[YEAR] and t2.V016Dispt = v016.V016ID and t3.DispDirectFactID is null)   
 )
from #FACT t1
where t1.DD_ID is null

--step 3
update t1
set t1.DD_ID =  (
  select top 1 DispDirectID from [RDS].T_DISP_DIRECT t2
	left join  [RDS].T_DISP_DIRECT_FACT t3 on t3.DispDirect = t2.DispDirectID
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = t1.[IDDT] and v016.DATEEND is null
  where (t2.FAM = t1.FAM and t2.OT = t1.OT and t2.DR = t1.DR
   and t2.[YEAR] = t1.[YEAR] and t2.V016Dispt = v016.V016ID and t3.DispDirectFactID is null)   
 )
from #FACT t1
where t1.DD_ID is null

--step 4
update t1
set t1.DD_ID =  (
  select top 1 DispDirectID from [RDS].T_DISP_DIRECT t2
	left join  [RDS].T_DISP_DIRECT_FACT t3 on t3.DispDirect = t2.DispDirectID
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = t1.[IDDT] and v016.DATEEND is null
  where (t2.IM = t1.IM and t2.OT = t1.OT and t2.DR = t1.DR
   and t2.[YEAR] = t1.[YEAR] and t2.V016Dispt = v016.V016ID and t3.DispDirectFactID is null)   
 )
from #FACT t1
where t1.DD_ID is null

--------
 update DispFactFile
 set DispFactFile.[VERSION] = zglv.[VERSION], DispFactFile.[FILENAME] = zglv.[FILENAME]
 ,DispFactFile.[YEAR]=zglv.[YEAR]
 ,DispFactFile.[MONTH]=zglv.[MONTH] 
 ,DispFactFile.[V016Dispt] = v016.V016ID
 ,DispFactFile.[ReceivedDate] = zglv.[ReceivedDate], DispFactFile.[ReceivedTime] = zglv.[ReceivedTime], DispFactFile.[Worker] = @Worker
 from [RDS].T_DISP_DIRECT_FACT_FILE DispFactFile
 join #ZGLV zglv on zglv.FileId=DispFactFile.[FileID]
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = zglv.[IDDT] and v016.DATEEND is null
 where DispFactFile.[FileID] = @FileID
 --------
 
 INSERT INTO [RDS].T_DISP_DIRECT_FACT
 (
      [DispDirectFactID]
      ,[DateBegin]
      ,[DateEnd]
      ,[FAM]
      ,[IM]
      ,[OT]
      ,[RSLT_D]
      ,[DispDirect]
      ,[DR]
      ,[ENP]
      ,[CODE_MO]
      ,[ISHOD]
      ,[NSCHET]
      ,[YEAR]
      ,[MONTH]
      ,[V016Dispt]
      ,[DispDirectFactFile]
	  ,[PR_D_N]

 )
 select 
 f.[ItemID]
 ,f.[DATE_B]
 ,f.[DATE_E]
 ,f.[FAM]
 ,f.[IM]
 ,f.[OT]
 ,f.[RESULT]
 ,f.DD_ID
 ,f.[DR]
 ,f.[ENP]
 ,f.[CODE_MO]
 ,f.[ISHOD]
 ,f.[NSCHET]
 ,f.[YEAR] 
 ,f.[MONTH]
 ,v016.V016ID
 ,@FileID
 ,f.[PR_D_N]

 from #FACT f
	left join [RDS].T_V016_DISPT v016 on v016.IDDT = f.[IDDT] and v016.DATEEND is null



 INSERT INTO [RDS].T_DISP_DIRECT_FACT_DESTINATION
 (
      [DispDirectFactDestinationID]
      ,[DestinationType]
      ,[DispDirectFact]
 )
 select 
	newID()
	,d.VID_CODE
	,f.[ItemID]
 from #DESTINATION d
 join #FACT f on f.ENP = d.ENP and f.[NSCHET] = d.[NSCHET]

 update ddi
 set ddi.Result = 'Диспансеризация пройдена с '+convert(varchar(10), f.[DATE_B], 120) + ' по ' + convert(varchar(10), ISNULL(f.[DATE_E], GETDATE()), 120)
 from [RDS].T_DISP_DIRECT_INFO ddi
 join [RDS].T_DISP_DIRECT dd on dd.[DispDirectID] = ddi.[DispDirect]
 join #FACT f on f.DD_ID = dd.[DispDirectID]




 
 INSERT INTO [RDS].T_DISP_DIRECT
 (
      [DispDirectID]
      ,[DispDirectFactFile]
      ,[FAM]
      ,[IM]
      ,[OT]
      ,[W]
      ,[DR]
	  ,[ENP]
      ,[VPOLIS]
      ,[SPOLIS]
      ,[NPOLIS]
      ,[DOCTYPE]
      ,[DOCSER]
      ,[DOCNUM]
	  ,[SNILS_P]
      ,[COMENTP]
      ,[MR]
      ,[YEAR]
      ,[V016Dispt]
      ,[MONTH_Q]
      ,[CODE_SMO]
      ,[CODE_MO]
      ,[SNILS_D]
	  ,[PODR]
	  ,[UCH]
      --,[DispDirect]
 )
 select 
	newid(), @FileID
		
		, dd.[FAM],dd.[IM],dd.[OT], dd.[W] ,dd.[DR], dd.[ENP], dd.[VPOLIS]
				,dd.[SPOLIS],dd.[NPOLIS], dd.[DOCTYPE] ,dd.[DOCSER] ,dd.[DOCNUM] ,dd.[SNILS_P]
				,dd.[COMENTP] 
				,dd.[MR] 
				,case when month(t1.[DATE_E]) = 12 then dd.[YEAR]+1 else dd.[YEAR] end--ask
				--,p.[PATIENT]
				,v016.V016ID ,case when month(t1.[DATE_E]) = 12 then 1 else month(t1.[DATE_E]) end--ask
  				,dd.[CODE_SMO] ,dd.[CODE_MO], dd.[SNILS_D]
				,dd.[PODR]
				,dd.[UCH]
 from #FACT t1
 join RDS.T_DISP_DIRECT dd on dd.[DispDirectID] = t1.DD_ID
 left join [RDS].T_V016_DISPT v016 on v016.IDDT = 'ДВ2' and v016.DATEEND is null
where (t1.[RESULT] = 6 or t1.[RESULT] = 11 or t1.[RESULT] = 12 or t1.[RESULT] = 13 or t1.[RESULT] = 14 or t1.[RESULT] = 15)


DROP TABLE #ZGLV
DROP TABLE #FACT
DROP TABLE #DESTINATION
END



