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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[rds].[sp_DnInsert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [rds].[sp_DnInsert]
GO
-- =============================================
-- Author:  (C) Vitacard System 2019
-- =============================================
CREATE PROCEDURE [rds].[sp_DnInsert]
    @FileID uniqueidentifier, 
	@xml xml 
AS
BEGIN  
SELECT 
	 T.c.value('./VERSION[1]', 'varchar(5)') AS [VERSION]
	,T.c.value('./DATA[1]', 'datetime') AS [DATA]
	,T.c.value('./FILENAME[1]', 'varchar(24)') AS [FILENAME]
	,T.c.value('./MCOD[1]', 'int') AS [MCOD]
INTO #ZGLV
FROM @xml.nodes('/DN_LIST/ZGLV') T(c) 

SELECT 
 NewId() as [ItemID]
 ,T.c.value('../FileID[1]', 'uniqueidentifier') as [ZGLV]
 ,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('./SMO[1]', 'varchar(5)') AS [SMO]
 ,T.c.value('./SNILS[1]', 'varchar(14)') AS [SNILS]
INTO #ZL
FROM @xml.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 @xml.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 [DISP_INIT_ID]
 ,T.c.value('./DATE_BEGIN[1]', 'date') as [DATE_BEGIN]
 ,T.c.value('./DATE_OUT[1]', 'date') as [DATE_OUT]
 ,T.c.value('./DS[1]', 'varchar(10)') as [DS]
 ,T.c.value('./MO[1]', 'int') as [MO]
 ,T.c.value('./STATUS[1]', 'int') as [STATUS]
 ,T.c.value('./SNILS_MR[1]', 'varchar(14)') as [SNILS_MR]
 ,T.c.value('./SPEC[1]', 'int') as [SPEC]
INTO #DN
FROM @xml.nodes('/DN_LIST/ZL/DN') T(c)

SELECT
 NewId() as [PresenceId]
 ,T.c.value('../../ID_PAC[1]', 'uniqueidentifier') as [ID_PAC]
 ,T.c.value('../DN_ID[1]', 'varchar(36)') as [DISP_INIT_ID]
 ,T.c.value('./DATE_PLAN[1]', 'date') as [DATE_PLAN]
 ,T.c.value('./PLACE[1]', 'int') as [PLACE]
 ,T.c.value('./MO_P[1]', 'int') as [MO_P]
INTO #PRESENCES
FROM @xml.nodes('/DN_LIST/ZL/DN/PRESENCES') T(c)

update item
set item.ID_PAC = zl.ID_PAC
from [RDS].T_PATIENT item
join #ZL zl on item.ENP = zl.ENP


--все distinct пациенты insured(zl)
select distinct
	 zl.ID_PAC
	,zl.FAM
	,zl.IM
	,zl.OT
	,zl.DR
	,zl.ENP
	,zl.VPOLIS
	,zl.SPOLIS
	,zl.NPOLIS
	,zl.SMO
	,zl.SNILS
into #TEMP_T_DN_ITEM
from #ZL zl

alter table #TEMP_T_DN_ITEM
add 
	id uniqueidentifier null,
	Patient uniqueidentifier

update tempitem
set tempitem.id = newid(), tempitem.Patient = patient.PatientID
from #TEMP_T_DN_ITEM tempitem
join RDS.T_PATIENT patient on patient.ID_PAC = tempitem.ID_PAC

select
	 dn.DNID
	,dn.DATE_BEGIN
	,dn.DATE_OUT
	,dn.DISP_INIT_ID
	,dn.STATUS
	,dn.DS
	,dn.SNILS_MR
	,dn.SPEC
	,dnitem.Patient
	,dnitem.ID_PAC
	,dn.DISP_INIT_ID
into #TEMPNOTDISTINCTDN
from #DN dn
join #TEMP_T_DN_ITEM dnitem on dn.ZLID = dnitem.ID_PAC
where dnitem.Patient is not null

--проставляем DATE_OUT, если пришел с таким же DN_ID и он не null.
update dnitem
set dnitem.DATE_OUT = dn.DATE_OUT
from [RDS].T_DN_ITEM dnitem
join #TEMPNOTDISTINCTDN dn on dn.DISP_INIT_ID = dnitem.DN_ID
where dn.DATE_OUT is not null and dnitem.DATE_OUT is null
 
--добавляем в существующего пациента чайлд-элемент dn, у которого не совпадает дата впервые установленного диагноза и мкб код
insert into [RDS].T_DN_ITEM
(
	 [DnItemID]
	,[DATE_BEGIN]
	,[DATE_OUT]
	,[DN_ID]
	,[DS_DISP]
	,[SNILS_VR]
	,[Patient]
	,[ID_PAC]
	,[DnFile]
)
select
	 tempnotdist.DNID
	,tempnotdist.DATE_BEGIN
	,tempnotdist.DATE_OUT
	,tempnotdist.DN_ID
	,mkb.MkbID
	,LEFT(tempnotdist.SNILS_MR,11)
	,tempnotdist.Patient
	,tempnotdist.ID_PAC
	,@FileID
from #TEMPNOTDISTINCTDN tempnotdist
join RDS.T_MKB mkb on mkb.MCBCode = tempnotdist.DS and mkb.EndCode is null
where not exists(select * from RDS.T_DN_ITEM dnitem
				join RDS.T_MKB mkb1 on dnitem.DS_DISP = mkb.MkbID and mkb1.MCBCode = mkb.MCBCode
--dodelat potomy chto date_begin может не приходить
				where dnitem.ID_PAC = tempnotdist.ID_PAC and dnitem.DATE_BEGIN = tempnotdist.DATE_BEGIN
				)
--добавляем presences в существующего dn-a в существующем пациенте, у которого не совпадают dat_plan и place
insert into [RDS].T_DN_ITEM_PLAN
(
	 [DnItemPlanID]
	,[DATE_PLAN]
	,[PLACE]
	,[MO_P]
	,[DnItem]
)
select
	 pres.PresenceId
	,pres.DATE_PLAN
	,pres.PLACE
	,lpumain.MCOD
	,dn.DNID 
from #PRESENCES pres
join #TEMPNOTDISTINCTDN dn on dn.DISP_INIT_ID = pres.DISP_INIT_ID
left join RDS.T_LPU_MAIN lpumain on lpumain.MCOD = pres.MO_P
where not exists(select * from RDS.T_DN_ITEM item
				 join RDS.T_DN_ITEM_PLAN itemplan on itemplan.DnItem = item.DnItemID
				 where item.ID_PAC = dn.ID_PAC and itemplan.DATE_PLAN = pres.DATE_PLAN and itemplan.PLACE = pres.PLACE)


--добавление новых контактов, различающихся по контакту и его типу к пациенту, который существует в базе
insert into [RDS].T_PATIENT_CONTACT
(
	 [PatientContactID]
	,[Contact]
	,[PatientContactType]
	,[Patient]
)
select newid(), cont.CONTACT, (select Code from RDS.T_PATIENT_CONTACT_TYPE where Code = cont.TYPE), tempdnlistitem.Patient
from #CONTACTS cont
join #TEMP_T_DN_ITEM tempdnlistitem on tempdnlistitem.ID_PAC = cont.ID_PAC
where not exists (select * from [RDS].T_RDS_PATIENT pat
				  join [RDS].T_PATIENT_CONTACT patcont on pat.InsuredID = patcont.Patient and patcont.Contact = cont.CONTACT
				  join [RDS].T_PATIENT_CONTACT_TYPE conttype on conttype.Code = patcont.PatientContactType
				  where pat.ID_PAC = tempdnlistitem.ID_PAC)

DROP TABLE #ZGLV
DROP TABLE #ZL
DROP TABLE #CONTACTS
DROP TABLE #DN
DROP TABLE #PRESENCES
DROP TABLE #TEMP_T_DN_ITEM
DROP TABLE #TEMPNOTDISTINCTDN
END