if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DnInsert]') and xtype = N'P')
drop procedure [dbo].[sp_DnInsert]
GO
-- Author:        Georgiy Dubovitskiy
-- Create date: 2019/10/28
-- Description:    Загрузка диспансерного наблюдения
-- =============================================
CREATE PROCEDURE [dbo].[sp_DnInsert] 
	@x xml
	,@FileID uniqueidentifier = null
	,@Worker uniqueidentifier = null
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('./MCOD[1]', 'int') AS [MCOD]
	,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 @x.nodes('/DN_LIST/ZGLV') T(c) 

SELECT 
 NewId() as [ItemID]
 ,T.c.value('./ID_PAC[1]', 'varchar(36)') AS [ID_PAC]
 ,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('./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('./SNILS[1]', 'varchar(14)') AS [SNILS]
 ,T.c.value('./InsuredId[1]','varchar(36)') as [InsuredId]
INTO #ZL
FROM @x.nodes('/DN_LIST/ZL') T(c)   

SELECT
 NewId() as [ContactID]
 ,T.c.value('../ID_PAC[1]', 'varchar(36)') as [ID_PAC]
 ,T.c.value('./CONTACT[1]', 'varchar(250)') as [CONTACT]
 ,T.c.value('./TYPE[1]', 'int') as [TYPE]
INTO #CONTACTS
FROM @x.nodes('DN_LIST/ZL/CONTACTS') T(c)

SELECT
  NEWID() as [DNID]
 ,T.c.value('../ID_PAC[1]', 'varchar(36)') as [ZLID]
 ,T.c.value('./DN_ID[1]', 'varchar(36)') as [DN_ID]
 ,T.c.value('./DATE_BEGIN[1]', 'date') as [DATE_BEGIN]
 ,T.c.value('./DATE_OUT[1]', 'date') as [DATE_OUT]
 ,T.c.value('./DN_PR[1]', 'int') as [DN_PR]
 ,T.c.value('./DATE_GR[1]', 'date') as [DATE_GR]
 ,T.c.value('./GROUP_DN[1]', 'int') as [GROUP_DN]
 ,T.c.value('./DS[1]', 'varchar(10)') as [DS]
 ,T.c.value('./MO[1]', 'varchar(6)') as [MO]
 ,T.c.value('./CODE_MR[1]', 'varchar(14)') as [SNILS_MR]
 ,T.c.value('./SPEC[1]', 'int') as [SPEC]
 ,T.c.value('./DATE_P[1]', 'date') as [DATE_P]
 ,T.c.value('./MkbId[1]', 'varchar(36)') AS [MkbId] -- заглушка
 ,T.c.value('./DuplicateId[1]', 'uniqueidentifier') AS [DuplicateId] -- заглушка
INTO #DN
FROM @x.nodes('/DN_LIST/ZL/DN') T(c)

update dn
set dn.MkbId = mkb.MkbID
from #DN dn
	join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null

SELECT
 NewId() as [PresenceIdGUID]
 ,T.c.value('../../ID_PAC[1]', 'varchar(36)') as [ID_PAC]
 ,T.c.value('../DN_ID[1]', 'varchar(36)') as [DN_ID]
 ,T.c.value('../CODE_MR[1]', 'varchar(14)') as [SNILS_MR]
 ,T.c.value('./PRESENCE_ID[1]', 'varchar(36)') as [PresenceId]
 ,T.c.value('./DATE_PLAN[1]', 'date') as [DATE_PLAN]
 ,T.c.value('./PLACE[1]', 'int') as [PLACE]
 ,T.c.value('./MO_P[1]', 'varchar(6)') as [MO_P]
 ,T.c.value('./PresenceDuplicateId[1]', 'uniqueidentifier') AS [PresenceDuplicateId] -- заглушка
INTO #PRESENCES
FROM @x.nodes('/DN_LIST/ZL/DN/PRESENCES') T(c)

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

insert into #Errors
select 0, 'Код мкб ('+ dn.DS +') не найден в справочнике МКБ (DN_ID - ' + DN_ID+')'
from #DN dn
where dn.MkbId is null

insert into #Errors
select 0, 'Код мкб ('+ dn.DS +') не принадлежит классификатору перечня заболеваний, при наличии которых проводится ДН. ID_PAC('+dn.ZLID+')'
from #DN dn
where dn.DS like 'U%'

insert into #Errors
select 7, 'Дата посещения (' + CONVERT(varchar(10), pres.DATE_PLAN, 104) + ') не может быть раньше, чем дата постановки на ДН (' + CONVERT(varchar(10), dn.DATE_BEGIN, 104) + '); DN_ID = ' + dn.DN_ID
from #PRESENCES pres
join #DN dn on dn.DN_ID = pres.DN_ID
where pres.DATE_PLAN < dn.DATE_BEGIN

-- Костыль для Костромы
if exists(select * from sys.all_columns c where c.name = 'xDn')
    exec sp_executesql N'insert into #Errors select 0, ''Врач, указанный как лечащий, не может вести ДН по его специальности. ID_PAC(''+dn.ZLID+'')'' from #DN dn join ISZL.T_V021_SPECIALITY s on s.Id = dn.SPEC where s.xDn = 0;'

insert into #Errors
select 0, 'Некорректная дата у явки с идентификатором (' + PresenceId + '). ID_PAC('+[ID_PAC]+')'
from #PRESENCES
where len(year(DATE_PLAN)) <> 4

--если отправляют файл, то нужно сделать идентификацию
if(@FileID is not null)
begin
	update t1
	set t1.InsuredId = (
	  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
	  join [ISZL].T_INSURED_POLICY_RECORD record on record.Insured =
	  (select top 1 Insured from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateEnd is null)
	  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.InsuredId is null
 
	--step 2 (ис ЗЛ фамилия, имя, отчество, др)
	update t1
	set t1.InsuredId =  (
	  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.InsuredId is null 

	--step 3 (фамилия, имя, номер полиса) из коллекции полисов
	update t1
	set t1.InsuredId =  (
	  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
	  join [ISZL].T_INSURED_POLICY_RECORD record on record.Insured =
	  (select top 1 Insured from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateEnd is null)
	  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.InsuredId is null 

	--step 4 (фамилия, др, номер полиса) из коллекции полисов
	update t1
	set t1.InsuredId =  (
	  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
	  join [ISZL].T_INSURED_POLICY_RECORD record on record.Insured =
	  (select top 1 Insured from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateEnd is null)
	  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.InsuredId is null

	--step 5 (ENP) из ЗЛ
	update t1
	set t1.InsuredId = (
		select top 1 t2.InsuredID from ISZL.T_INSURED t2
		where t2.ENP = t1.ENP
	)
	from #ZL t1
	where t1.InsuredId is null

	--step 6 (NPOLIS, ID_PAC) из коллекции полисов
	update t1
	set t1.InsuredId = (
		select top 1 t2.InsuredID from [ISZL].T_INSURED t2
		join [ISZL].T_INSURED_POLICY_RECORD record on record.Insured =
		(select top 1 Insured from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateEnd is null)
		join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
		where (iszlpolicy.Number = t1.NPOLIS and t2.ID_PAC = t1.ID_PAC)
	)
	from #ZL t1
	where t1.InsuredId is null

	--step 7 (ID_PAC) из ЗЛ
	update t1
	set t1.InsuredId = (
		select top 1 t2.InsuredID from ISZL.T_INSURED t2
		where t2.ID_PAC = t1.ID_PAC
	)
	from #ZL t1
	where t1.InsuredId is null

	insert into #Errors
	select 1, 'Застрахованный не найден в БД. NPOLIS('+ zl.NPOLIS+')'
	from #ZL zl
	where zl.InsuredId is null

	--там Застрахованый, Код МКБ-10, Дата явки
  update p
  set p.PresenceDuplicateId = (
				select top 1 pr.DnPresenceID  
				from [ISZL].T_DN_PRESENCE pr
					join ISZL.T_DN_ITEM di on di.DnItemID = pr.DnItem and di.Insured = zl.InsuredId
				where (di.DS_DISP = mkb.MkbID and pr.DATE_PLAN = p.DATE_PLAN)   
			)
	from #PRESENCES p
		join #DN dn on dn.DN_ID = p.DN_ID and dn.ZLID = p.ID_PAC 
		join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
		join #ZL zl on dn.ZLID = zl.ID_PAC

	update dn
	set dn.DuplicateId = (
		select top 1 di.DnItemID
			from #ZL z
			join #DN d on cast(z.ID_PAC as uniqueidentifier) = cast(dn.ZLID as uniqueidentifier)
			join iszl.T_DN_ITEM di on cast(z.InsuredId as uniqueidentifier) = di.Insured
			join iszl.T_MKB mkb on mkb.MCBCode = d.DS
			where di.Insured is not null and z.InsuredId is not null
				and z.ID_PAC = zl.ID_PAC
				and d.DNID = dn.DNID
				and d.MO = di.MO_IN
				and mkb.MkbID = di.DS_DISP
				and ((di.DATE_OUT is null and d.DATE_OUT is null)
					or (di.DATE_OUT is not null and d.DATE_OUT is not null
						and cast(cast(cast(di.DATE_OUT as float) as int) as datetime)
							= cast(d.DATE_OUT as datetime))))
	from #ZL zl
	left join #DN dn on zl.ID_PAC = dn.ZLID
	where zl.InsuredId is not null

	if((select COUNT(*) from #Errors) > 0)
	begin
		select cast(0 as bit),
		'<?xml version="1.0" encoding="Windows-1251"?>
		' + (
		select 
			CODE AS 'CODE',
			COMMENT AS 'COMMENT'
		FROM #Errors
		FOR XML PATH('ERR'),
		ROOT('ROOT')
		)
	end
	else
	begin
		--update ISZL.T_DN_FILE
		--set [VERSION] = zglv.[VERSION],
		--	[ReceivedDate] = getdate(),
		--	[ReceivedTime] = getdate(),
		--	[DATA] = zglv.[DATA],
		--	[MCOD] = zglv.[MCOD]
		--from #ZGLV zglv
		--where FileID = @FileID

		---file delete
		declare @DnFileId uniqueidentifier
		set @DnFileId = (
					select top 1 dd.[FileID] 
					from [ISZL].T_DN_FILE dd 
					where dd.[FILENAME] = (select  z.[FILENAME] from #ZGLV z)
				)

		if @DnFileId is not null
		begin 
			delete pr from ISZL.T_DN_PRESENCE pr
			join ISZL.T_DN_ITEM di on di.DnItemId = pr.DnItem
			where di.[DnFile] = @DnFileId
			
			delete from ISZL.T_DN_ITEM 
			where [DnFile] = @DnFileId
			
			delete from ISZL.T_DN_FILE 
			where FileId = @DnFileId
			
			delete from VClib.T_FILE 
			where FileId = @DnFileId
		end
		
		---create file
		insert into VCLib.T_FILE ([FileID],[Name],[CreateDateTime],[FileNameForm],[SaveFileLocation],[UseOle],[type_])
		select FileId, [FILENAME],GetDate(), 1, 1, 0, (select EntityID from VCLib.T_ENTITIES where EntName = 'DN_FILE') 
		from #ZGLV z

		insert into [ISZL].[T_DN_FILE] ([FileID],[VERSION],CODE_MO,ID,ReceivedDate,ReceivedTime,[DATA],[FILENAME])
		select FileId, [VERSION],[MCOD],null,ReceivedDate,ReceivedTime, [DATA], [FILENAME]
		from #ZGLV 

		insert into ISZL.T_DN_ITEM
		(
			 [DnItemID]
			,[DATE_IN]
			,[DATE_OUT]
			,[RESULT_OUT]
			,[DN_ID]
			,[DS_DISP]
			,[SNILS_VR]
			,[V021Speciality]
			,[Insured]
			,[ID_PAC]
			,[DnFile]
			,[MO_IN]
			,DATE_GR
			,GROUP_DN
			,DATE_P
		)
		select
			 dn.DNID
			,dn.DATE_BEGIN
			,dn.DATE_OUT
			,case dn.[DN_PR] when 4 then 1 when 6 then 8 else null end
			,dn.DN_ID
			,mkb.MkbID
			,dn.SNILS_MR
			,dn.SPEC
			,zl.InsuredId
			,dn.ZLID
			,@FileID
			,mo.MCOD
			,DATE_GR
			,GROUP_DN
			,DATE_P
		from #DN dn
		join #ZL zl on zl.ID_PAC = dn.ZLID
		join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
		left join ISZL.T_F003 mo on mo.MCOD = dn.MO
    where dn.DuplicateId is null

    update di
    set [RESULT_OUT] = case dn.[DN_PR] when 4 then 1 when 6 then 8 else null end,
        [DN_ID] = dn.DN_ID,
        [SNILS_VR] = dn.SNILS_MR,
        [V021Speciality] = dn.SPEC,
        [ID_PAC] = dn.ZLID,
        [DnFile] = @FileID,
        DATE_GR = dn.DATE_GR,
        GROUP_DN = dn.GROUP_DN,
        DATE_P = dn.DATE_P
		from #DN dn
    join ISZL.T_DN_ITEM di on di.DnItemID = dn.DuplicateId
		join #ZL zl on zl.ID_PAC = dn.ZLID
		join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
		left join ISZL.T_F003 mo on mo.MCOD = dn.MO
    where dn.DuplicateId is not null

		insert into ISZL.T_DN_PRESENCE
		(
			 [DnPresenceID]
			,[DATE_PLAN]
			,[PLACE]
			,[MO_P]
			,[DnItem]
		)
		select
			 pres.PresenceIdGUID
			,pres.DATE_PLAN
			,pres.PLACE
			,lpumain.MCOD
			,ISNULL(dn.DuplicateId, dn.DNID)
		from #PRESENCES pres
		join #DN dn on dn.DN_ID = pres.DN_ID
		left join ISZL.T_F003 lpumain on lpumain.MCOD = pres.MO_P
    where pres.PresenceDuplicateId is null

    update dp
    set [PLACE] = pres.PLACE,
        [MO_P] = lpumain.MCOD
		from #PRESENCES pres
    join ISZL.T_DN_PRESENCE dp on dp.DnPresenceID = pres.PresenceDuplicateId
		join #DN dn on dn.DN_ID = pres.DN_ID
		left join ISZL.T_F003 lpumain on lpumain.MCOD = pres.MO_P
    where pres.PresenceDuplicateId is not null

		insert into ISZL.T_INSURED_CONTACT
		(
			 [InsuredContactID]
			,[Insured]
			,[Contact]
			,[ContactType]
		)
		select
			 newid()
			,zl.InsuredId
			,cont.CONTACT
			,t1.ContactTypeID
		from #CONTACTS cont
		join #ZL zl on zl.ID_PAC = cont.ID_PAC
		join ISZL.T_CONTACT_TYPE t1 on t1.[Name] = cont.[TYPE]
		where not exists
		(select * 
		 from ISZL.T_INSURED_CONTACT insc
		 where insc.Contact = cont.CONTACT
		 and insc.Insured = zl.InsuredId
		 and insc.ContactType = t1.ContactTypeID
		)
	end
end
else
--прислали по раббиту
begin
	if ((select COUNT(*) from #Errors) = 0 )
	begin

		update zl
		set zl.InsuredId = ins.InsuredID
		from #ZL zl
		join ISZL.T_INSURED ins on ins.ENP = zl.ENP
		
		update t1
		set t1.InsuredId = (
		  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
		  join [ISZL].T_INSURED_POLICY_RECORD record on record.Insured =
		  (select top 1 Insured from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateEnd is null)
		  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.InsuredId is null
	 
		--step 2 (ис ЗЛ фамилия, имя, отчество, др)
		update t1
		set t1.InsuredId =  (
		  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.InsuredId is null 

		--step 3 (фамилия, имя, номер полиса) из коллекции полисов
		update t1
		set t1.InsuredId =  (
		  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
		  join [ISZL].T_INSURED_POLICY_RECORD record on record.Insured =
		  (select top 1 Insured from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateEnd is null)
		  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.InsuredId is null 

		--step 4 (фамилия, др, номер полиса) из коллекции полисов
		update t1
		set t1.InsuredId =  (
		  select top 1 t2.InsuredID from [ISZL].T_INSURED t2
		  join [ISZL].T_INSURED_POLICY_RECORD record on record.Insured =
		  (select top 1 Insured from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateEnd is null)
		  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.InsuredId is null


		--step 6 (NPOLIS, ID_PAC) из коллекции полисов
		update t1
		set t1.InsuredId = (
			select top 1 t2.InsuredID from [ISZL].T_INSURED t2
			join [ISZL].T_INSURED_POLICY_RECORD record on record.Insured =
			(select top 1 Insured from [ISZL].T_INSURED_POLICY_RECORD ipr where t2.InsuredID = ipr.Insured and ipr.DateEnd is null)
			join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
			where (iszlpolicy.Number = t1.NPOLIS and t2.ID_PAC = t1.ID_PAC)
		)
		from #ZL t1
		where t1.InsuredId is null

		--step 7 (ID_PAC) из ЗЛ
		update t1
		set t1.InsuredId = (
			select top 1 t2.InsuredID from ISZL.T_INSURED t2
			where t2.ID_PAC = t1.ID_PAC
		)
		from #ZL t1
		where t1.InsuredId is null


		--удаление дублей
		;with cte as
				(
					select t1.PresenceIdGUID,
					t1.DN_ID,
					t1.date_plan,
					t1.snils_mr,
					ROW_NUMBER() over
					(
						partition by t1.date_plan, t1.snils_mr, t1.dn_id
						order by cast(t1.DN_ID as varchar(36))
					) rn
					from #PRESENCES t1
				)
		delete from cte
		where cte.rn > 1

		--удаление дублей СЛУЧАЕВ
		;with cte as
		(
			select dn.DNID
			,dn.DATE_BEGIN
			,dn.DATE_OUT
			,dn.DN_ID
			,dn.SNILS_MR
			,dn.SPEC
			,zl.InsuredId
			,dn.ZLID
			,mo.MCOD
			--,@FileID
			,
			ROW_NUMBER() over
			(
				partition by zl.InsuredId, mkb.MkbID, mo.MCOD
				order by cast(dn.DN_ID as varchar(36))
			) rn
			from #DN dn
			join #ZL zl on dn.ZLID = zl.ID_PAC
			join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
			left join ISZL.T_F003 mo on mo.MCOD = dn.MO 
			)
		delete from #DN
		where DNID in
		(select DNID from cte
		where cte.rn > 1)

		--там Застрахованый, Код МКБ-10, Дата явки
    update p
    set p.PresenceDuplicateId = (
					select top 1 pr.DnPresenceID  
					from [ISZL].T_DN_PRESENCE pr
						join ISZL.T_DN_ITEM di on di.DnItemID = pr.DnItem and di.Insured = zl.InsuredId
					where (di.DS_DISP = mkb.MkbID and pr.DATE_PLAN = p.DATE_PLAN)   
				)
		from #PRESENCES p
			join #DN dn on dn.DN_ID = p.DN_ID and dn.ZLID = p.ID_PAC 
			join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
			join #ZL zl on dn.ZLID = zl.ID_PAC

		update dn
		set dn.DuplicateId = (
			select top 1 di.DnItemID
				from #ZL z
				join #DN d on cast(z.ID_PAC as uniqueidentifier) = cast(dn.ZLID as uniqueidentifier)
				join iszl.T_DN_ITEM di on cast(z.InsuredId as uniqueidentifier) = di.Insured
				join iszl.T_MKB mkb on mkb.MCBCode = d.DS
				where di.Insured is not null and z.InsuredId is not null
					and z.ID_PAC = zl.ID_PAC
					and d.DNID = dn.DNID
					and d.MO = di.MO_IN
					and mkb.MkbID = di.DS_DISP
					and ((di.DATE_OUT is null and d.DATE_OUT is null)
						or (di.DATE_OUT is not null and d.DATE_OUT is not null
							and cast(cast(cast(di.DATE_OUT as float) as int) as datetime)
								= cast(d.DATE_OUT as datetime))))
		from #ZL zl
		left join #DN dn on zl.ID_PAC = dn.ZLID
		where zl.InsuredId is not null

		if ((select COUNT(*) from #Errors) = 0 )
		begin
			--новый формат
			if((select count(*) from #PRESENCES pres where pres.PresenceId is null) = 0)
			begin
				--все старые dn
				select 
					t1.*,
					ISNULL(t1.DuplicateId, t2.DnItemID) DnItemID
				into 
				#OLDDN
				from #DN t1
				join #ZL t3 on t1.ZLID = t3.ID_PAC
				join ISZL.T_MKB mkb on mkb.MCBCode = t1.DS and mkb.EndCode is null
				join ISZL.T_DN_ITEM t2 on t2.Insured = t3.InsuredId and t2.DS_DISP = mkb.MkbID and t1.DN_ID = t2.DN_ID
			
				--все новые dn
				select
					t1.*,
					ISNULL(t1.DuplicateId, t1.DNID) DnItemID,
					t2.*
				into
				#NEWDN
				from #DN t1
				join #ZL t2 on t1.ZLID = t2.ID_PAC
				where not exists (select * from #OLDDN t3 where t1.DNID = t3.DNID)

				--все пресенсы с присланными старыми ДН
				select
					t1.DATE_PLAN,
					t1.ID_PAC,
					t1.MO_P,
					t1.PLACE,
					t1.PresenceId,
					t1.PresenceIdGUID,
					t1.DN_ID,
					t1.SNILS_MR,
					t2.DnItemID,
          t1.PresenceDuplicateId
				into #OLDDNPRESENCES
				from #PRESENCES t1
				join #OLDDN t2 on t1.DN_ID = t2.DN_ID
			
				--все новые ЯВКИ с присланными новыми ДН
				select
					t1.DATE_PLAN,
					t1.ID_PAC,
					t1.MO_P,
					t1.PLACE,
					t1.PresenceId,
					t1.PresenceIdGUID,
					t1.DN_ID,
					t1.SNILS_MR,
					t2.DnItemID,
          t1.PresenceDuplicateId
				into #NEWDNPRESENCES
				from #PRESENCES t1
				join #NEWDN t2 on t1.DN_ID = t2.DN_ID
			
				--обновление снилсов врачей, у которых совпадает заплан. дата и не совпадает снилс врача
				update t1
				set t1.SNILS_VR1 = pres.SNILS_MR
				--select t1.SNILS_VR1 as 'firstsnils', pres.SNILS_MR as 'secondsnils', t1.DATE_PLAN as 'firstdate', pres.DATE_PLAN as 'seconddate', t1.DnItem
				from ISZL.T_DN_PRESENCE t1
				join #OLDDNPRESENCES pres on /*(t1.PRESENCE_ID is not null and t1.PRESENCE_ID = pres.PresenceId or */t1.DnItem = pres.DnItemID and cast(t1.DATE_PLAN as datetime) = cast(pres.DATE_PLAN as datetime) and t1.SNILS_VR1 <> pres.SNILS_MR
			
				--вставка новых ЯВОК, в старых ДН
				insert into ISZL.T_DN_PRESENCE
				(
					 [DnPresenceID]
					,[DATE_PLAN]
					,[PLACE]
					,[MO_P]
					,[DnItem]
					,[SNILS_VR1]
					,[PRESENCE_ID]
				)
				select
					 pres.PresenceIdGUID
					,cast(pres.DATE_PLAN as datetime) as DATE_PLAN
					,pres.PLACE
					,mo.MCOD
					,pres.DnItemID
					,pres.SNILS_MR
					,pres.PresenceId
				from #OLDDNPRESENCES pres
				left join ISZL.T_F003 mo on pres.MO_P = mo.MCOD
				--не существуют такие, что совпадают заплан. дата и снилс врача, т.е. - новые
				where not exists (select * from ISZL.T_DN_PRESENCE t1 where /*(t1.PRESENCE_ID is not null and t1.PRESENCE_ID = pres.PresenceId or */t1.DnItem = pres.DnItemID and cast(t1.DATE_PLAN as datetime) = cast(pres.DATE_PLAN as datetime) and t1.SNILS_VR1 = pres.SNILS_MR)
            and pres.PresenceDuplicateId is null
                and
            not exists
            (
                select *
                from ISZL.T_DN_PRESENCE dp
                where dp.DnPresenceID = pres.PresenceIdGUID
            )
			
				--обновление presence
				update t1
				set t1.PRESENCE_ID = t2.PresenceId
				from ISZL.T_DN_PRESENCE t1
				join #OLDDNPRESENCES t2 on t1.DnItem = t2.DnItemID
				where t1.PRESENCE_ID is null
			
				--вставка новых ДН
				insert into ISZL.T_DN_ITEM
				(
					 [DnItemID]
					,[DATE_IN]
					,[DATE_OUT]
					,[RESULT_OUT]
					,[DN_ID]
					,[DS_DISP]
					,[SNILS_VR]
					,[V021Speciality]
					,[Insured]
					,[ID_PAC]
					--,[DnFile]
					,[MO_IN]
					--,[FileName]
					,DATE_GR
					,GROUP_DN
					,DATE_P
				)
				select
					 dn.DNID
					,dn.DATE_BEGIN
					,dn.DATE_OUT
					,case dn.[DN_PR] when 4 then 1 when 6 then 8 else null end
					,dn.DN_ID
					,mkb.MkbID
					,dn.SNILS_MR
					,dn.SPEC
					,zl.InsuredId
					,dn.ZLID
					--,@FileID
					,mo.MCOD
					,DATE_GR
					,GROUP_DN
					,DATE_P
					--,(select [FILENAME] from #ZGLV)
				from #NEWDN dn
				join #ZL zl on zl.ID_PAC = dn.ZLID
				join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
				left join ISZL.T_F003 mo on mo.MCOD = dn.MO
        where dn.DuplicateId is null

        update di
        set [RESULT_OUT] = case dn.[DN_PR] when 4 then 1 when 6 then 8 else null end,
            [DN_ID] = dn.DN_ID,
            [SNILS_VR] = dn.SNILS_MR,
            [V021Speciality] = dn.SPEC,
            [ID_PAC] = dn.ZLID,
            [DnFile] = @FileID,
            DATE_GR = dn.DATE_GR,
            GROUP_DN = dn.GROUP_DN,
            DATE_P = dn.DATE_P
        from #DN dn
        join ISZL.T_DN_ITEM di on di.DnItemID = dn.DuplicateId
        join #ZL zl on zl.ID_PAC = dn.ZLID
        join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
        left join ISZL.T_F003 mo on mo.MCOD = dn.MO
        where dn.DuplicateId is not null


				--вставка новых явок
				insert into ISZL.T_DN_PRESENCE
				(
					 [DnPresenceID]
					,[DATE_PLAN]
					,[PLACE]
					,[MO_P]
					,[DnItem]
					,[SNILS_VR1]
					,[PRESENCE_ID]
				)
				select
					 pres.PresenceIdGUID
					,pres.DATE_PLAN
					,pres.PLACE
					,lpumain.MCOD
					,pres.DnItemID
					,pres.SNILS_MR
					,pres.PresenceId
				from #NEWDNPRESENCES pres
				left join ISZL.T_F003 lpumain on lpumain.MCOD = pres.MO_P
        where pres.PresenceDuplicateId is null
                and
            not exists
            (
                select *
                from ISZL.T_DN_PRESENCE dp
                where dp.DnPresenceID = pres.PresenceIdGUID
            )
			
				drop table #OLDDNPRESENCES
				drop table #NEWDNPRESENCES
				drop table #OLDDN
				drop table #NEWDN
			end
			else
			begin
				declare @filexists uniqueidentifier
				set @filexists =
				(
					select top 1 dnfile.FileID 
					from ISZL.T_DN_FILE dnfile
					join VCLib.T_FILE f on dnfile.FileID = f.FileID
					where f.[Name] = (select zglv.[FILENAME] from #ZGLV zglv)
				)

				if(@filexists is not null)
				begin
					;with cte as
					(
						select dnitem.DnItemID as [DnItemID] 
						from ISZL.T_DN_ITEM dnitem
						where dnitem.DnFile = @filexists
					)
					delete pres
					from ISZL.T_DN_PRESENCE pres
					join cte on cte.DnItemID = pres.DnItem

					delete dnitem
					from ISZL.T_DN_ITEM dnitem
					where dnitem.DnFile = @filexists

					delete dnfile
					from ISZL.T_DN_FILE dnfile
					where dnfile.FileID = @filexists

					delete vcfile
					from VCLib.T_FILE vcfile
					where vcfile.FileID = @filexists
				end
			
				set @FileID = newid()
				insert into VCLib.T_FILE
				(
					 [FileID]
					,[Name]
					,[FileNameForm]
					,[SaveFileLocation]
					,[UseOle]
					,[type_]
				)
				select
					 @FileID
					,zglv.[FILENAME]
					,1
					,1
					,0
					,(select ent.EntityID from VCLib.T_ENTITIES ent where ent.EntName = 'DN_FILE')
				from #ZGLV zglv

				insert into ISZL.T_DN_FILE
				(
					 [FileID]
					,[FILENAME]
					,[ReceivedDate]
					,[ReceivedTime]
					,[DATA]
					,[CODE_MO]
				)
				select
					@FileID
					,zglv.[FILENAME]
					,getdate()
					,getdate()
					,zglv.[DATA]
					,zglv.MCOD
				from #ZGLV zglv

				insert into ISZL.T_DN_ITEM
				(
					 [DnItemID]
					,[DATE_IN]
					,[DATE_OUT]
					,[RESULT_OUT]
					,[DN_ID]
					,[DS_DISP]
					,[SNILS_VR]
					,[V021Speciality]
					,[Insured]
					,[ID_PAC]
					,[DnFile]
					,[MO_IN]
					,DATE_GR
					,GROUP_DN
					,DATE_P
				)
				select
					 dn.DNID
					,dn.DATE_BEGIN
					,dn.DATE_OUT
					,case dn.[DN_PR] when 4 then 1 when 6 then 8 else null end
					,dn.DN_ID
					,mkb.MkbID
					,dn.SNILS_MR
					,dn.SPEC
					,zl.InsuredId
					,dn.ZLID
					,@FileID
					,mo.MCOD
					,DATE_GR
					,GROUP_DN
					,DATE_P
				from #DN dn
				join #ZL zl on zl.ID_PAC = dn.ZLID
				join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
				left join ISZL.T_F003 mo on mo.MCOD = dn.MO
        where dn.DuplicateId is null

        update di
        set [RESULT_OUT] = case dn.[DN_PR] when 4 then 1 when 6 then 8 else null end,
            [DN_ID] = dn.DN_ID,
            [SNILS_VR] = dn.SNILS_MR,
            [V021Speciality] = dn.SPEC,
            [ID_PAC] = dn.ZLID,
            [DnFile] = @FileID,
            DATE_GR = dn.DATE_GR,
            GROUP_DN = dn.GROUP_DN,
            DATE_P = dn.DATE_P
        from #DN dn
        join ISZL.T_DN_ITEM di on di.DnItemID = dn.DuplicateId
        join #ZL zl on zl.ID_PAC = dn.ZLID
        join ISZL.T_MKB mkb on mkb.MCBCode = dn.DS and mkb.EndCode is null
        left join ISZL.T_F003 mo on mo.MCOD = dn.MO
        where dn.DuplicateId is not null


				insert into ISZL.T_DN_PRESENCE
				(
					 [DnPresenceID]
					,[DATE_PLAN]
					,[PLACE]
					,[MO_P]
					,[SNILS_VR1]
					,[DnItem]
				)
				select
					 pres.PresenceIdGUID
					,pres.DATE_PLAN
					,pres.PLACE
					,lpumain.MCOD
					,pres.SNILS_MR
					,ISNULL(dn.DuplicateId, dn.DNID)
				from #PRESENCES pres
				join #DN dn on dn.DN_ID = pres.DN_ID
				left join ISZL.T_F003 lpumain on lpumain.MCOD = pres.MO_P
        where pres.PresenceDuplicateId is null

        update dp
        set [PLACE] = pres.PLACE,
            [MO_P] = lpumain.MCOD
        from #PRESENCES pres
        join ISZL.T_DN_PRESENCE dp on dp.DnPresenceID = pres.PresenceDuplicateId
        join #DN dn on dn.DN_ID = pres.DN_ID
        left join ISZL.T_F003 lpumain on lpumain.MCOD = pres.MO_P
        where pres.PresenceDuplicateId is not null

				--добавляем только таких, которые отличаются по типу и контакту в ЗЛ
				insert into ISZL.T_INSURED_CONTACT
				(
					 [InsuredContactID]
					,[Insured]
					,[Contact]
					,[ContactType]
				)
				select
					 newid()
					,zl.InsuredId
					,cont.CONTACT
					,t1.ContactTypeID
				from #CONTACTS cont
				join #ZL zl on zl.ID_PAC = cont.ID_PAC
				join ISZL.T_CONTACT_TYPE t1 on t1.[Name] = cont.[TYPE]
				where not exists
				(select * 
				 from ISZL.T_INSURED_CONTACT insc
				 where insc.Contact = cont.CONTACT
				 and insc.Insured = zl.InsuredId
				 and insc.ContactType = t1.ContactTypeID
				)
			end
		end
	end
	select (case 
	   when (select COUNT(*) from #Errors) = 0 
	   then cast(1 as bit)
	   else case
			when (select COUNT(*) from #Errors) > 0
			then cast(0 as bit)
			end
	   end),
	   '<?xml version="1.0" encoding="Windows-1251"?>' 
		+ 
		(
			SELECT 
				CODE as 'CODE'
				,COMMENT as 'COMMENT'
			FROM #Errors 
			FOR XML PATH('ERR'),ROOT('ROOT')
		)
end



DROP TABLE #ZGLV
DROP TABLE #ZL
DROP TABLE #CONTACTS
DROP TABLE #DN
DROP TABLE #PRESENCES
DROP TABLE #Errors
END
