SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_IdentificationPm]') and xtype = N'P')
drop procedure [dbo].[sp_IdentificationPm]
GO
-- =============================================
-- Create date:		2019/10/28
-- Description:     Идентификация ПМ
-- =============================================
CREATE  PROCEDURE [dbo].[sp_IdentificationPm] 
 @x xml
 ,@PackageName varchar(max) = null
 ,@FileName varchar(max) = null

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]', 'varchar(6)') AS [MCOD]
  , CAST(CAST(CAST(GETDATE() as float) as INT) as datetime) as [ReceivedDate]
  , GETDATE() as [ReceivedTime]
INTO #ZGLV
FROM @x.nodes('IDENTIFICATION_PM/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('./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('./NPOLIS[1]', 'varchar(20)') AS [NPOLIS]
 ,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]
 ,T.c.value('./INSURED[1]', 'varchar(36)') AS [INSURED]
 ,T.c.value('./InsuredPolicyRecordID[1]', 'varchar(36)') AS [InsuredPolicyRecordID] -- заглушка
 ,T.c.value('../ZGLV[1]/DATA[1]', 'datetime') AS [DATA]
INTO #ZL
FROM @x.nodes('/IDENTIFICATION_PM/ZL') T(c)   


	--step 0.
	update t1
	set t1.[InsuredPolicyRecordID] = (
	  select top 1 InsuredPolicyRecordID 
	  from [ISZL].T_INSURED_POLICY_RECORD t2
		join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Patronymic = t1.OT and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or p.Number = t1.ENP) and t2.DateStop is null)
	  order by t2.DateBegin desc     
	 )
	from #ZL t1
	where t1.[InsuredPolicyRecordID] is null

	--step 1. без отчества
	update t1
	set t1.[InsuredPolicyRecordID] = (
	  select top 1 InsuredPolicyRecordID 
	  from [ISZL].T_INSURED_POLICY_RECORD t2
		join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM  and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or p.Number = t1.ENP) and t2.DateStop is null)
	  order by t2.DateBegin desc
	  )
	from #ZL t1
	where t1.[InsuredPolicyRecordID] is null

	--step 2. без отчества и ДР
	update t1
	set t1.[InsuredPolicyRecordID] = (
	  select top 1 InsuredPolicyRecordID 
	  from [ISZL].T_INSURED_POLICY_RECORD t2
		join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and (p.Number = t1.NPOLIS or p.Number = t1.ENP) and t2.DateStop is null)
	  order by t2.DateBegin desc   
	  )
	from #ZL t1
	where t1.[InsuredPolicyRecordID] is null

	--step 3. без отчества и Фамилии
	update t1
	set t1.[InsuredPolicyRecordID] = (
	  select top 1 InsuredPolicyRecordID 
	  from [ISZL].T_INSURED_POLICY_RECORD t2
		join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	  where (t2.FirstName = t1.IM and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or p.Number = t1.ENP) and t2.DateStop is null)
	  order by t2.DateBegin desc    
	 )
	from #ZL t1
	where t1.[InsuredPolicyRecordID] is null

		--step 3. без имени
	update t1
	set t1.[InsuredPolicyRecordID] = (
	  select top 1 InsuredPolicyRecordID 
	  from [ISZL].T_INSURED_POLICY_RECORD t2
		join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	  where (t2.LastName = t1.FAM and t2.Patronymic = t1.OT and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or p.Number = t1.ENP) and t2.DateStop is null)
	  order by t2.DateBegin desc    
	 )
	from #ZL t1
	where t1.[InsuredPolicyRecordID] is null


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

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

Update p
set p.ErrorString = ErrorString + 'Пациент умер. Дата смерти: '+convert(varchar(10), pat.DateDeath, 120) + char(13) + char(10)
from #ZL p
left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID = p.InsuredPolicyRecordID
left join [ISZL].T_INSURED pat on pat.InsuredID = record.Insured
where pat.DateDeath is not null 

Update p
set p.ErrorString = ErrorString + 'Фамилия: ' + pat.LastName + char(13) + char(10)
from #ZL p
left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID = p.InsuredPolicyRecordID
left join [ISZL].T_INSURED pat on pat.InsuredID = record.Insured
where p.FAM <> pat.LastName

Update p
set p.ErrorString = ErrorString + 'Имя: ' + pat.FirstName + char(13) + char(10)
from #ZL p
left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID = p.InsuredPolicyRecordID
left join [ISZL].T_INSURED pat on pat.InsuredID = record.Insured
where p.IM <> pat.FirstName

Update p
set p.ErrorString = ErrorString + 'Отчество: ' + pat.Patronymic + char(13) + char(10)
from #ZL p
left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID = p.InsuredPolicyRecordID
left join [ISZL].T_INSURED pat on pat.InsuredID = record.Insured
where p.OT <> pat.Patronymic and p.OT is not null

Update p
set p.ErrorString =  ErrorString + 'Дата рождения: ' + convert(varchar(10), pat.Birthday, 120) + char(13) + char(10)
from #ZL p
left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID = p.InsuredPolicyRecordID
left join [ISZL].T_INSURED pat on pat.InsuredID = record.Insured
where p.[DR] <> pat.Birthday

Update p
set p.ErrorString = ErrorString + 'СНИЛС: ' + pat.SNILS + char(13) + char(10)
from #ZL p
left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID = p.InsuredPolicyRecordID
left join [ISZL].T_INSURED pat on pat.InsuredID = record.Insured
where REPLACE(REPLACE(p.[SNILS], '-', ''),' ', '') <> REPLACE(REPLACE(pat.SNILS, '-', ''),' ', '')  and p.[SNILS] is not null and pat.SNILS <> ''

Update p
set p.ErrorString = ErrorString + 'Тип полиса: ' + cast (iszlpolicy.OmsType as varchar(2))  + char(13) + char(10)
from #ZL p
left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID = p.InsuredPolicyRecordID
left join [ISZL].T_INSURED pat on pat.InsuredID = record.Insured
left join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
where p.[VPOLIS] <> iszlpolicy.OmsType and p.[VPOLIS] is not null

Update p
set p.ErrorString = ErrorString + 'Серия полиса: ' + iszlpolicy.Series + char(13) + char(10)
from #ZL p
left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID = p.InsuredPolicyRecordID
left join [ISZL].T_INSURED pat on pat.InsuredID = record.Insured
left join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
where p.SPOLIS <> iszlpolicy.Series and p.SPOLIS is not null

Update p
set p.ErrorString = ErrorString + 'Номер полиса: ' + iszlpolicy.Number + char(13) + char(10)
from #ZL p
left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID = p.InsuredPolicyRecordID
left join [ISZL].T_INSURED pat on pat.InsuredID = record.Insured
left join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.Policy
where p.NPOLIS <> iszlpolicy.Number

select
		 (select REPLACE([FILENAME], 'IDP', 'DP') from #ZGLV),
		'<?xml version="1.0" encoding="utf-8"?>
		' + 
		(select
				--ZGLV
				(select 
					'1.0' AS 'VERSION'
					,convert(varchar(10), GETDATE(), 120) AS 'DATA'
					,REPLACE([FILENAME], 'IDP', 'IRD') AS 'FILENAME'
					,[MCOD]	AS 'MCOD'
					FROM #ZGLV 
					FOR XML PATH ('ZGLV'),TYPE	
				)
				,( 
					SELECT 
						p.[ID_PAC]  as 'ID_PAC'
						,pat.[ENP] as 'ENP'
						,mo.Code as 'MO'
						,smo.Code as 'SMO'
						,case when p.ErrorString = ('Верные данные: '  + char(13) + char(10)) then null else  p.ErrorString end as 'ERROR'
					FROM #ZL p
					    left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID = p.InsuredPolicyRecordID
						left join [ISZL].T_INSURED pat on pat.InsuredID = record.Insured


					--	left join [ISZL].T_INSURED pat on p.INSURED = pat.InsuredID
					--	left join [ISZL].T_INSURED_POLICY_RECORD record on record.InsuredPolicyRecordID =
					--	(select top 1 ipr.InsuredPolicyRecordID from [ISZL].T_INSURED_POLICY_RECORD ipr where pat.InsuredID = ipr.Insured and ipr.DateStop is null order by ipr.DateBegin desc)
						left join [ISZL].T_POLICY iszlpolicy on iszlpolicy.PolicyID = record.[Policy]
						left join ISZL.T_ORGANIZATION smo on smo.OrganizationID = record.Smo
						
						left join [ISZL].T_MO_ATTACHMENT attachment on attachment.MoAttachmentID = 
						(select top 1 att.MoAttachmentID from [ISZL].T_MO_ATTACHMENT att where att.Insured = pat.InsuredID and att.Status = 1 
						and not exists(select * from [ISZL].T_MO_ATTACHMENT att1 where att1.Insured = pat.InsuredID and att.Status = 2 and CAST(att1.AttachDate as date) >= CAST(att.AttachDate as date)))
						left join ISZL.T_ORGANIZATION mo on mo.OrganizationID = attachment.Mo
					FOR
					XML PATH('ZL'),
					TYPE
		)
		FOR XML PATH (''), ROOT('IDENTIFICATION_RESULT_PM')
		)
		,(select [MCOD] FROM #ZGLV )

DROP TABLE #ZL
DROP TABLE #ZGLV
END
