if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DnInfoListLoad_karelia]') and xtype = N'P')
drop procedure [dbo].[sp_DnInfoListLoad_karelia]
GO
CREATE procedure [dbo].[sp_DnInfoListLoad_karelia] 
 @x xml
 ,@FileID uniqueidentifier = null
 ,@Worker uniqueidentifier = null
AS 
BEGIN 
--загрузка только через дирректории!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT 
   NewId() as [ItemID]
  ,T.c.value('./VERSION[1]', 'varchar(5)') AS [VERSION]
  ,T.c.value('./DATA[1]', 'date') AS [DATA]
  ,T.c.value('./FILENAME[1]', 'varchar(26)') AS [FILENAME]
  ,T.c.value('./SMO[1]', 'varchar(5)') AS [SMO]
  ,T.c.value('./ZAP[1]', 'int') AS [ZAP]
INTO #ZGLV
FROM @x.nodes('/PERS_LIST/ZGLV') T(c)   

 SELECT 
 NewId() as [ItemID]
 ,T.c.value('./ID_PAC[1]', 'varchar(36)') AS [ID_PAC]
 ,T.c.value('../ZGLV[1]/ID[1]', 'varchar(36)') AS [ZGLVID]
 ,T.c.value('./FAM[1]', 'varchar(30)') AS [FAM]
 ,T.c.value('./IM[1]', 'varchar(30)') AS [IM]
 ,T.c.value('./OT[1]', 'varchar(30)') AS [OT]
 ,T.c.value('./W[1]', 'int') AS [W]
 ,T.c.value('./DR[1]', 'datetime') AS [DR]
 ,T.c.value('./VPOLIS[1]', 'int') AS [VPOLIS]
 ,T.c.value('./SPOLIS[1]', 'varchar(30)') AS [SPOLIS]
 ,T.c.value('./NPOLIS[1]', 'varchar(30)') AS [NPOLIS]
 ,T.c.value('./PHONE1[1]', 'varchar(30)') AS [PHONE1]
 ,T.c.value('./PHONE2[1]', 'varchar(30)') AS [PHONE2]
 ,T.c.value('./InsuredPolicyRecordID[1]', 'varchar(36)') AS [InsuredPolicyRecordID] -- заглушка
 ,T.c.value('./ActualInsuredPolicyRecordID[1]', 'varchar(36)') AS [ActualInsuredPolicyRecordID] -- заглушка
 ,T.c.value('../ZGLV[1]/DATA[1]', 'datetime') AS [DATA]
 ,T.c.value('../ZGLV[1]/SMO[1]', 'varchar(5)') AS [SMO]
INTO #PERS
FROM @x.nodes('/PERS_LIST/PERS') T(c)

 SELECT 
 NewId() as [ItemID]
 ,T.c.value('../../NPOLIS[1]', 'varchar(30)') AS [NPOLIS]
 ,T.c.value('./INFO_FORM[1]', 'int') AS [INFO_FORM]
 ,T.c.value('./INFO_DATE[1]', 'datetime') AS [INFO_DATE]
 ,T.c.value('./INFO_TYPE[1]', 'int') AS [INFO_TYPE]
 ,T.c.value('./DS[1]', 'varchar(5)') AS [DS] -- новое поле
 ,T.c.value('../../../ZGLV[1]/SMO[1]', 'varchar(5)') AS [SMO]
INTO #INFO_NOTE
FROM @x.nodes('/PERS_LIST/PERS/INFO_LIST/INFO_NOTE') T(c)

--Ищем полис застрахованного

--0. Ищем полис по номеру полиса. Не важно актуальный он или нет
--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]
	join ISZL.T_INSURED ins on ins.InsuredID = t2.Insured
  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 ins.ENP = t1.NPOLIS))   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.VPOLIS = 3

--0. Ищем полис по номеру полиса. Не важно актуальный он или нет
--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 )   
 )
from #PERS 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]
	join ISZL.T_INSURED ins on ins.InsuredID = t2.Insured
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM  and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or ins.ENP = t1.NPOLIS)) 
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.VPOLIS = 3

--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)   
 )
from #PERS 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]
	join ISZL.T_INSURED ins on ins.InsuredID = t2.Insured
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and (p.Number = t1.NPOLIS or ins.ENP = t1.NPOLIS))
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.VPOLIS = 3

--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 )   
 )
from #PERS 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]
	join ISZL.T_INSURED ins on ins.InsuredID = t2.Insured
  where (t2.FirstName = t1.IM and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or ins.ENP = t1.NPOLIS))
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.VPOLIS = 3

--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)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null 

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

insert into #Errors
select 7,  'Застрахованный не найден ни по ФИО и ДР, ни по номеру и серии полиса. ID_PAC ('+isnull(ID_PAC,'')+'). NPOLIS ('+ NPOLIS+')'
from #PERS t1
where t1.[InsuredPolicyRecordID] is null 

if ((select count(*) from #Errors) > 0)
begin
	select cast(0 as bit),
	'<?xml version="1.0" encoding="utf-8"?>
	' + (
	select 
		CODE AS 'CODE',
		COMMENT AS 'COMMENT'
	FROM #Errors
	FOR XML PATH('ERR'),
	ROOT('ROOT')
	)
end
else
begin

--------------------
	--Сheck File Exist--
	--------------------
	declare @ExistFile uniqueidentifier
	set @ExistFile = (
				select top 1 ddif.[FileID] 
				from [ISZL].T_DN_INFO_FILE ddif 
				where ddif.[FILENAME] = (select  z.[FILENAME] from #ZGLV z)
			)

	if @ExistFile is not null
	begin 
		exec [dbo].sp_DispDnInfoDel @ExistFile
	end

	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_INFO_FILE') 
	from #ZGLV 

	insert into [ISZL].T_DN_INFO_FILE ([FileID],[VERSION],ReceivedDate,ReceivedTime,[DATA],[FILENAME])
	select @FileId, [VERSION],GetDate(),GetDate(),[DATA],[FILENAME]
	from #ZGLV 
	
	insert into ISZL.T_DN_INFO
		(
			 [DnInfoID]
			,[DnInfoForm]
			,[INFO_DATE]
			,[INFO_TYPE]
			,[DnInfoFile]
			,[SMO]
			,[Insured]
		)
		select
			 infnote.ItemID
			,infnote.INFO_FORM
			,infnote.INFO_DATE
			,infnote.INFO_TYPE
			,@FileId
			,infnote.SMO
			,ipr.Insured
		from #INFO_NOTE infnote
		join #PERS pers on pers.NPOLIS = infnote.NPOLIS
		join ISZL.T_INSURED_POLICY_RECORD ipr on ipr.InsuredPolicyRecordID = pers.InsuredPolicyRecordID

		-- добавляем диагнозы в коллекцию
		insert into ISZL.[T_DN_INFO_DIAGNOSIS]
		([DnInfoDiagnosisID],
		[Mkb],
		[DnInfo])
		select
		NewId(),
		(select top 1 m.MkbID from ISZL.T_MKB m where m.MCBCode = note.DS),
		note.ItemID
		from #INFO_NOTE note
end

DROP TABLE #Errors
DROP TABLE #INFO_NOTE
DROP TABLE #PERS
DROP TABLE #ZGLV
END
