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

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

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

CREATE PROCEDURE [rds].[sp_DispIdent]
 @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('./YEAR[1]', 'int') AS [YEAR]
  ,T.c.value('./MONTH[1]', 'int') AS [MONTH]
  ,T.c.value('./CODE_MO[1]', 'int') AS [CODE_MO]
  ,T.c.value('./FILENAME[1]', 'varchar(26)') AS [FILENAME]
  , CAST(CAST(CAST(GETDATE() as float) as INT) as datetime) as [ReceivedDate]
  , GETDATE() as [ReceivedTime]
INTO #ZGLV
FROM @xml.nodes('/PERS_LIST/ZGLV') T(c)   

 SELECT 
 NewId() as [ItemID]
 ,T.c.value('./ID_PAC[1]', 'varchar(36)') AS [ID_PAC]
 ,T.c.value('./SMO[1]', 'int') AS [SMO]
 ,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('./W[1]', 'int') AS [W]
 ,T.c.value('./DR[1]', 'datetime') AS [DR]
 ,T.c.value('./ENP[1]', 'varchar(16)') AS [ENP]
 ,T.c.value('./DOCTYPE[1]', 'varchar(2)') AS [DOCTYPE]
 ,T.c.value('./DOCSER[1]', 'varchar(10)') AS [DOCSER]
 ,T.c.value('./DOCNUM[1]', 'varchar(20)') AS [DOCNUM]
 ,T.c.value('./SNILS[1]', 'varchar(14)') AS [SNILS]
 ,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('../ZGLV[1]/CODE_MO[1]', 'int') AS [CODE_MO]
INTO #PERS
FROM @xml.nodes('/PERS_LIST/PERS') T(c)   

ALTER TABLE #PERS
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 ) 
  order by t2.DocType
 )
from #PERS 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)   
  order by t2.DocType
 )
from #PERS 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)   
  order by t2.DocType
 )
from #PERS 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)   
  order by t2.DocType
 )
from #PERS t1
where t1.PATIENT is null 


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

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


Update  p
set p.ErrorString = ErrorString + 'Пациент умер. Дата смерти: '+convert(varchar(10), patient.DateDeath, 120) + char(13) + char(10)
from #PERS 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 #PERS 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 #PERS 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 #PERS 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 + 'Пол: ' + case when patient.Sex = 10301 then 'мужской' else 'женский' end + char(13) + char(10)
from #PERS p
	join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
	left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = p.[W]
where eSex.EnumID <> patient.Sex

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

Update  p
set p.ErrorString = ErrorString + 'Серия документа: ' + patient.DocSeries + char(13) + char(10)
from #PERS p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
left join [VCLib].T_ENUMS doctype on doctype.EnumTypeID = 105
						and 
						doctype.EnumID = patient.[DocType]
where (p.[DOCTYPE] is null and REPLACE(p.[DOCSER], ' ', '' ) <> REPLACE(patient.DocSeries, ' ', '' ) and p.[DOCSER] is not null) or (p.[DOCTYPE] = doctype.[Alias] and REPLACE(p.[DOCSER], ' ', '' ) <> REPLACE(patient.DocSeries, ' ', '' )  and p.[DOCSER] is not null)

Update  p
set p.ErrorString = ErrorString + 'Номер документа: ' + patient.DocNumber + char(13) + char(10)
from #PERS p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
left join [VCLib].T_ENUMS doctype on doctype.EnumTypeID = 105
						and 
						doctype.EnumID = patient.[DocType]
where (p.[DOCTYPE] is null and p.[DOCNUM] <> patient.DocNumber and p.[DOCNUM] is not null) or (p.[DOCTYPE] = doctype.[Alias] and p.[DOCNUM] <> patient.DocNumber and p.[DOCNUM] is not null)

Update  p
set p.ErrorString = ErrorString + 'СНИЛС: ' + patient.SNILS + char(13) + char(10)
from #PERS p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where REPLACE(REPLACE(p.[SNILS], ' ', '' ), '-', '' ) <> REPLACE(REPLACE(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 #PERS 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 #PERS p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where REPLACE(p.SPOLIS, ' ', '' ) <> REPLACE(patient.PolisSerial, ' ', '' ) and p.SPOLIS is not null and p.[VPOLIS] <> 3

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

Update  p
set p.ErrorString = ErrorString + 'Номер полиса(ЕНП): ' + patient.ENP --+ char(13) + char(10)
from #PERS p
join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
where p.NPOLIS <> patient.ENP and p.[VPOLIS] = 3




select
		((select REPLACE([FILENAME], 'IDD', 'DD') from #ZGLV)),
		'<?xml version="1.0" encoding="Windows-1251"?>
		' + 
		(select
				--ZGLV
				(select 
						    '2.1' AS 'VERSION'
							,convert(varchar(10), GETDATE(), 120) AS 'DATA'
							,[YEAR]		AS 'YEAR'
							,[MONTH]	AS 'MONTH'
							,[CODE_MO]	AS 'CODE_MO'
							,REPLACE([FILENAME], 'IDD', 'DD')	AS 'FILENAME'
					FROM #ZGLV 
					FOR XML PATH ('ZGLV'),TYPE	
				)
				,( 
					SELECT 
						p.[ID_PAC]  as 'ID_PAC'
						,patient.F002Smo as 'SMO'
						, convert(varchar(10), patient.Birthday , 120) as 'DR'
						,patient.[ENP] as 'ENP'
						,convert(varchar(10), GETDATE(), 120) AS 'DATA_CHECK'
						,case when p.ErrorString = ('Верные данные: '  + char(13) + char(10)) then null else  p.ErrorString end as 'ERROR'
					FROM #PERS p
						left join [RDS].T_PATIENT patient on p.PATIENT = patient.PatientID
						left join [VCLib].T_ENUMS doctype on doctype.EnumTypeID = 105
							and 
							doctype.EnumID = patient.[DOCTYPE]
						left join [VCLib].T_ENUMS eSex on eSex.[EnumID] = patient.Sex
					FOR
					XML PATH('PERS'),
					TYPE
		)
		FOR XML PATH (''), ROOT('PERS_LIST')
		)
 
DROP TABLE #PERS
DROP TABLE #ZGLV
END