if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InformingDn]') and xtype = N'P')
drop procedure [dbo].[sp_InformingDn]
GO
-- =============================================
-- Author:        Georgiy Dubovitskiy
-- Create date: 2019/10/28
-- Description:    Информирование диспансерного наблюдения
-- =============================================
CREATE PROCEDURE [dbo].[sp_InformingDn]
	@FileID varchar(36) = null,
	@Worker varchar(36) = null,
	@x xml
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(50)') AS [FILENAME]
  ,T.c.value('./SMO[1]', 'varchar(5)') AS [SMO]
  , CAST(CAST(CAST(GETDATE() as float) as INT) as datetime) as [ReceivedDate]
  , GETDATE() as [ReceivedTime]
INTO #ZGLV
FROM @x.nodes('/INFORMING_DN/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('./DS[1]', 'varchar(10)') AS [DS]
 ,T.c.value('./ENP[1]', 'varchar(16)') AS [ENP]
 ,T.c.value('./VPOLIS[1]', 'int') AS [VPOLIS]
 ,T.c.value('./SPOLIS[1]', 'varchar(10)') AS [SPOLIS]
 ,T.c.value('./NPOLIS[1]', 'varchar(20)') AS [NPOLIS]
 ,T.c.value('./DATA_INF[1]', 'datetime') AS [DATA_INF]
 ,T.c.value('./F_INF[1]', 'int') AS [F_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('./INSURED[1]', 'varchar(36)') as [INSURED] -- заглушка
  ,T.c.value('./ActualInsuredPolicyRecordID[1]', 'varchar(36)') as [ActualInsuredPolicyRecordID] -- заглушка
 ,T.c.value('../ZGLV[1]/SMO[1]', 'varchar(5)') as [SMO] -- заглушка
 ,T.c.value('./DuplicateCount[1]', 'int') AS [DuplicateCount] -- заглушка
  ,T.c.value('./Mkb[1]', 'varchar(36)') AS [Mkb] -- заглушка
INTO #ZL
FROM @x.nodes('/INFORMING_DN/ZL') T(c)

select
	T.c.value('../ENP[1]', 'varchar(16)') AS [ENP]
	,T.c.value('./DS[1]', 'varchar(10)') AS [DN_DS]
	,T.c.value('./MkbId[1]', 'varchar(36)') AS [MkbId]
INTO #DNDS
FROM @x.nodes('/INFORMING_DN/ZL/DNDS') T(c)

--step 0
update t1
set t1.INSURED = (
  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
  where (t2.ENP = t1.ENP)   
 )
from #ZL t1
where t1.INSURED is null

--step 1
update t1
set t1.INSURED = (
  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
  join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID =
  (select top 1 ipr.InsuredPolicyRecordID from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured order by ipr.DateBegin desc)
  join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR and iszlpolicy.Number = t1.NPOLIS )   
 )
from #ZL t1
where t1.INSURED is null
 
--step 2
update t1
set t1.INSURED =  (
  select top 1 InsuredID from [ISZL].T_INSURED t2
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR)   
 )
from #ZL t1
where t1.INSURED is null 

--step 3
update t1
set t1.INSURED =  (
  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
  join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID =
  (select top 1 ipr.InsuredPolicyRecordID from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured order by ipr.DateBegin desc)
  join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and iszlpolicy.Number = t1.NPOLIS)   
 )
from #ZL t1
where t1.INSURED is null 

--step 4
update t1
set t1.INSURED =  (
  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
  join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID =
  (select top 1 ipr.InsuredPolicyRecordID from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured  order by ipr.DateBegin desc)
  join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
  where (t2.LastName = t1.FAM and t2.Birthday = t1.DR and iszlpolicy.Number = t1.NPOLIS)   
 )
from #ZL t1
where t1.INSURED is null

update zl
set zl.DuplicateCount = (select count(*)
	from #ZL z
	join iszl.T_DN_INFO di on
		di.Insured = cast(z.INSURED as uniqueidentifier) and
		di.Smo = z.SMO and
		di.INFO_TYPE = z.TIP_INFO and
		cast(cast(cast(di.INFO_DATE as float) as int) as datetime) = cast(cast(cast(z.DATA_INF as float) as int) as datetime)
	where z.INSURED is not null and di.Insured is not null)
from #ZL zl
where zl.INSURED is not null

update t1
set t1.Mkb = (
select top 1 t.MkbID from ISZL.T_MKB t where t.MCBCode = t1.DS)
from #ZL t1

update t1
set t1.MkbId = (
select top 1 t.MkbID from ISZL.T_MKB t where t.MCBCode = t1.DN_DS)
from #DNDS t1

create table #Errors
(
	CODE int,
	COMMENT varchar(250)
)

insert into #Errors
select 0, 'Код мкб ('+ ISNULL(mkb.DS, '_ПУСТО_') +') не найден в справочнике МКБ. NPOLIS (' + ISNULL(mkb.NPOLIS, '_ПУСТО_') + ')'
from #ZL mkb
where mkb.Mkb is null and mkb.DS is not null

insert into #Errors
select 0, 'Код мкб ('+ ISNULL(mkb.DN_DS, '_ПУСТО_') +') не найден в справочнике МКБ. NPOLIS (' + ISNULL(mkb.ENP, '_ПУСТО_') + ')'
from #DNDS mkb
where mkb.MkbId is null and mkb.DN_DS is not null

insert into #Errors
(
	 CODE
	,[COMMENT]
)
select
	1
	,'Застрахованный не найден ни по ФИО и ДР, ни по номеру и серии полиса. NPOLIS('+ ISNULL(p.NPOLIS, '_ПУСТО_') +')'
from #ZL p
where p.INSURED is null

insert into #Errors
select 2, 'Уже существует информирование с указанными параметрами. NPOLIS(' + ISNULL(zl.NPOLIS, '_ПУСТО_') + ')'
from #ZL zl
where zl.DuplicateCount > 0

if((select COUNT(*) from #Errors) > 0)
begin
	select cast(0 as bit),
	'<?xml version="1.0" encoding="utf-8"?>
	' + (
	select 
		ISNULL(CODE, -1) AS 'CODE',
		ISNULL(COMMENT, '_ПУСТО_') AS 'COMMENT'
	FROM #Errors
	FOR XML PATH('ERR'),
	ROOT('ROOT')
	)
end

if ((select FileId from #ZGLV) is not null)
	begin
			------------------
			--Сheck File Exist--
			------------------
			declare @InfoFile uniqueidentifier
			set @InfoFile = (
						select top 1 [FileID] 
						from [ISZL].T_DN_INFO_FILE  
						where [FILENAME] = (select  z.[FILENAME] from #ZGLV z)
					)

			if @InfoFile is not null
			begin 
				exec [dbo].sp_DispDnInfoDel @InfoFile
			end

			insert into VCLib.T_FILE ([FileID],[Name],[CreateDateTime],[FileNameForm],[SaveFileLocation],[UseOle],[type_])
			select FileId, [FILENAME],GetDate(), 1, 1, 0, (select top 1 EntityID from VCLib.T_ENTITIES where EntName = 'DN_INFO_FILE') 
			from #ZGLV 

			insert into [ISZL].T_DN_INFO_FILE ([FileID],[VERSION],CODE_MO,ID,ReceivedDate,ReceivedTime,[DATA],[FILENAME])
			select FileId,[VERSION],null,null,ReceivedDate,ReceivedTime,[DATA],[FILENAME]
			from #ZGLV 
		end
	else
		begin
			DELETE FROM [ISZL].T_DN_INFO
			WHERE [FileName] = (select [FILENAME] from #ZGLV)
		end	

insert into ISZL.T_DN_INFO
		(
			 [DnInfoID]
			,[DnInfoForm]
			,[INFO_DATE]
			,[INFO_TYPE]
			,[Addr]
			,[SMO]
			,[Insured]
			,[FileName]
		)
		SELECT 
		  t1.ItemID, 
		  t1.F_INF, 
		  t1.DATA_INF, 
		  t1.TIP_INFO, 
		  t1.N_INF, 
		  t1.SMO, 
		  t1.INSURED, 
		  (SELECT TOP 1 [FILENAME] FROM #ZGLV)
		from #ZL t1
		WHERE 
		  t1.INSURED IS NOT NULL
		  AND t1.DuplicateCount <= 0

	-- добавляем в коллекцию с новой загрузкой диагнозов
	if(select count(*) from #DNDS) > 0
		begin
			insert into ISZL.[T_DN_INFO_DIAGNOSIS]
				(
					[DnInfoDiagnosisID],
					[Mkb],
					[DnInfo]
				)
			select
				NewId(),
				dn.MkbId,
				zl.ItemID
			from #DNDS dn
			join #ZL zl on dn.ENP = zl.ENP
			where zl.INSURED IS NOT NULL
		  AND zl.DuplicateCount <= 0
		  and dn.MkbId is not null
		end

	-- добавляем в коллекцию по старой загрузке (с учетом того, чтоы не дублировать записи)
	insert into ISZL.[T_DN_INFO_DIAGNOSIS]
				(
					[DnInfoDiagnosisID],
					[Mkb],
					[DnInfo]
				)
			select
				NewId(),
				zl.mkb,
				zl.ItemID
			from #ZL zl
			where zl.INSURED IS NOT NULL
			AND zl.DuplicateCount <= 0
			and not exists (select DnInfo from ISZL.[T_DN_INFO_DIAGNOSIS] where DnInfo = zl.ItemID)
			and zl.Mkb is not null

DROP TABLE #ZL
DROP TABLE #ZGLV
DROP TABLE #Errors
DROP TABLE #DNDS
END
