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

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

-- =============================================
-- Author:  (C) Vitacard System 2019
-- =============================================

CREATE PROCEDURE [rds].[sp_DnIdent]
 @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]
  , CAST(CAST(CAST(GETDATE() as float) as INT) as datetime) as [ReceivedDate]
  , GETDATE() as [ReceivedTime]
INTO #ZGLV
FROM @xml.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('./NPOLIS[1]', 'varchar(20)') AS [NPOLIS]
 ,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('./SMO[1]', 'int') 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('./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('./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)

ALTER TABLE #ZL
ADD PATIENT varchar(36);

update t1
set t1.PATIENT =  (
  select top 1 PatientID from [RDS].T_PATIENT t2
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR and t2.PolisNumber = t1.NPOLIS )   
 )
from #ZL t1
where t1.PATIENT is null 
--step 2
update t1
set t1.PATIENT =  (
  select top 1 PatientID from [RDS].T_PATIENT t2
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Birthday = t1.DR)   
 )
from #ZL t1
where t1.PATIENT is null 
--step 3
update t1
set t1.PATIENT =  (
  select top 1 PatientID from [RDS].T_PATIENT t2
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.PolisNumber = t1.NPOLIS)   
 )
from #ZL t1
where t1.PATIENT is null 
--step 4
update t1
set t1.PATIENT =  (
  select top 1 PatientID from [RDS].T_PATIENT t2
  where (t2.LastName = t1.FAM and t2.Birthday = t1.DR and t2.PolisNumber = t1.NPOLIS)   
 )
from #ZL t1
where t1.PATIENT is null

--SELECT zl.ID_PAC, dn.* FROM #ZL zl
--join #DN dn on dn.ZLID = zl.ID_PAC

--SELECT zl.* FROM #PRESENCES pres
--join #ZL zl on pres.ID_PAC = zl.ID_PAC

ALTER TABLE #ZL
ADD ErrorString varchar(max) NOT NULL DEFAULT('Верные данные: '  + char(13) + char(10))

Update p
set p.ErrorString = 'Ошибка идентификации'
from #ZL p
where p.PATIENT is null

Update  p
set p.ErrorString = ErrorString + 'Пациент умер. Дата смерти: '+convert(varchar(10), patient.DateDeath, 120) + char(13) + char(10)
from #ZL p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where patient.DateDeath is not null 

Update  p
set p.ErrorString = ErrorString + 'Фамилия: ' + patient.LastName + char(13) + char(10)
from #ZL p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where p.FAM <> patient.LastName

Update  p
set p.ErrorString = ErrorString + 'Имя: ' + patient.FirstName + char(13) + char(10)
from #ZL p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where p.IM <> patient.FirstName

Update  p
set p.ErrorString = ErrorString + 'Отчество: ' + patient.FatherName + char(13) + char(10)
from #ZL p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where p.OT <> patient.FatherName and p.OT is not null

Update  p
set p.ErrorString =  ErrorString + 'Дата рождения: ' + convert(varchar(10), patient.Birthday, 120) + char(13) + char(10)
from #ZL p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where p.[DR] <> patient.Birthday

Update  p
set p.ErrorString = ErrorString + 'СНИЛС: ' + patient.SNILS + char(13) + char(10)
from #ZL p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where p.[SNILS] <> patient.SNILS  and p.[SNILS] is not null

Update  p
set p.ErrorString = ErrorString + 'Тип полиса: ' + cast (patient.PolicyForm as varchar(2))  + char(13) + char(10)
from #ZL p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where p.[VPOLIS] <> patient.PolicyForm and p.[VPOLIS] is not null

Update  p
set p.ErrorString = ErrorString + 'Серия полиса: ' + patient.PolisSerial + char(13) + char(10)
from #ZL p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where p.SPOLIS <> patient.PolisSerial and p.SPOLIS is not null

Update  p
set p.ErrorString = ErrorString + 'Номер полиса: ' + patient.PolisNumber + char(13) + char(10)
from #ZL p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where p.NPOLIS <> patient.PolisNumber

Update p
set p.ErrorString = 
(select case
	when dn.SNILS_MR is not null then 'СНИЛС врача: ' + patient.SNILS_D --+ char(13) + char(10)
	else 'Не указан СНИЛС врача.'
end)
from #ZL p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
join #DN dn on dn.ZLID = p.ID_PAC
where dn.SNILS_MR <> patient.SNILS_D

select
		((select REPLACE([FILENAME], 'IDN', 'DN') from #ZGLV)),
		'<?xml version="1.0" encoding="Windows-1251"?>
		' + 
		(select
				--ZGLV
				(select 
						    '1.0' AS 'VERSION'
							,convert(varchar(10), GETDATE(), 120) AS 'DATA'
							,REPLACE([FILENAME], 'IDN', 'DN') AS 'FILENAME'
							,[MCOD]	AS 'MCOD'
					FROM #ZGLV 
					FOR XML PATH ('ZGLV'),TYPE	
				)
				,( 
					SELECT 
						p.[ID_PAC]  as 'ID_PAC'
						,patient.LastName  as 'FAM'
						,patient.FirstName  as 'IM'
						,patient.FatherName  as 'OT'
						,case when p.[PATIENT] is null then null else convert(varchar(10), patient.Birthday , 120) end as 'DR'
						,patient.[ENP] as 'ENP'
						,patient.PolicyForm  as 'VPOLIS'
						,patient.PolisSerial  as 'SPOLIS'
						,patient.PolisNumber  as 'NPOLIS'
						,patient.F002Smo as 'SMO'
						,patient.SNILS  as 'SNILS'
						,(
							SELECT cont.CONTACT as 'CONTACT'
							, cont.TYPE as 'TYPE'
							FROM #CONTACTS cont where cont.ID_PAC = p.ID_PAC
							FOR XML PATH ('CONTACTS'), TYPE 
						 ) 
						,
						 (
							SELECT dn.DATE_BEGIN as 'DATE_BEGIN'
							, dn.DATE_OUT as 'DATE_OUT'
							, dn.DS as 'DS'
							, dn.MO as 'MO'
							, dn.STATUS as 'STATUS'
							, patient.SNILS_D as 'SNILS_MR'
							, dn.SPEC as 'SPEC'
							,(
								SELECT DISTINCT pres.DATE_PLAN as 'DATE_PLAN'
								, pres.PLACE as 'PLACE'
								, pres.MO_P as 'MO_P'
								FROM #PRESENCES pres where pres.ID_PAC = p.ID_PAC
								FOR XML PATH ('PRESENCES'), TYPE 
							 ) 
							FROM #DN dn where dn.ZLID = p.ID_PAC
							FOR XML PATH ('DN'), TYPE 
						 )
						,
						case when p.ErrorString = ('Верные данные: '  + char(13) + char(10)) then null else  p.ErrorString end as 'ERROR'
					FROM #ZL p
						left join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
					FOR
					XML PATH('ZL'),
					TYPE
		)
		FOR XML PATH (''), ROOT('DN_LIST')
		)

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