SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_Prof_V1_KARELIA]') and xtype = N'P')
drop procedure [dbo].[sp_Prof_V1_KARELIA]
GO
CREATE procedure [dbo].[sp_Prof_V1_KARELIA] @x xml
AS 
BEGIN 
SELECT 
   NewId() as [ItemID]
  ,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('./TYPE[1]', 'varchar(6)') AS [TYPE]
  ,T.c.value('./CODE_MO[1]', 'varchar(6)') AS [CODE_MO]
  ,T.c.value('./ID_MO[1]', 'varchar(30)') AS [ID_MO]
  ,T.c.value('./SMO[1]', 'varchar(5)') AS [SMO]
  ,T.c.value('./ZAP[1]', 'int') AS [ZAP]
  ,GETDATE()  AS [CurrentDate] 
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('./ID[1]', 'uniqueidentifier') AS [ID]
 ,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('./NPOLIS[1]', 'varchar(30)') AS [NPOLIS]
 ,T.c.value('./SPOLIS[1]', 'varchar(30)') AS [SPOLIS]
 ,T.c.value('./DISP[1]', 'varchar(3)') AS [DISP]
 ,T.c.value('./DISP_MONTH[1]', 'int') AS [DISP_MONTH]
 ,T.c.value('./PHONE1[1]', 'varchar(11)') AS [PHONE1]
 ,T.c.value('./PHONE2[1]', 'varchar(11)') AS [PHONE2]
 ,T.c.value('./PR[1]', 'int') AS [PR]
 ,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]/YEAR[1]', 'int') AS [YEAR]     
,T.c.value('./ZGLV[1]/CODE_MO[1]', 'varchar(6)') AS [CODE_MO]  
,T.c.value('./ZGLV[1]/SMO[1]', 'varchar(5)') AS [SMO]  
INTO #PERS
FROM @x.nodes('/PERS_LIST/PERS') T(c)   

--Ищем полис застрахованного
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 

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 

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 

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 

--Ищем актуальный полис застрахованного 
update t1
set t1.[ActualInsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	--join [ISZL].T_F008_OMS_TYPE f008 on f008.IDDOC = p.OmsType
  where t2.Insured = t3.Insured and t2.DateBegin <= t1.[DATA] and (not(t2.DateStop is not null and t2.DateStop <= t1.[DATA]))
  order by t2.DateBegin, p.OmsType
 )
from #PERS t1
join [ISZL].T_INSURED_POLICY_RECORD t3 on t3.[InsuredPolicyRecordID] = t1.[InsuredPolicyRecordID]

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

-- Проверка заполнения обязательных полей (Ошибка при обработке пакета с иденификатором ___ Обратитесь в тех. Поддержку).
insert into #Errors
select 1, 'Ошибка при обработке пакета с иденификатором ('+t1.[ID_PAC]+ '). Обратитесь в тех. поддержку.'
from #PERS t1
where t1.[ID_PAC] is null and t1.[ID] is null
and  t1.[FAM] is null and  t1.[IM] is null and  t1.[W] is null and  t1.[DR] is null
and  t1.[VPOLIS] is null and  t1.[NPOLIS] is null and  t1.[DISP] is null and  t1.[PR] is null

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

--Застрахованный умер
insert into #Errors
select 3, 'Застрахованный умер. ID_PAC('+cast(t1.ID_PAC as varchar)+') NPOLIS('+cast(t1.NPOLIS as varchar)+')'
from #PERS t1
join [ISZL].T_INSURED_POLICY_RECORD t2 on t2.[InsuredPolicyRecordID] = t1.[ActualInsuredPolicyRecordID]
join [ISZL].T_INSURED t3 on t3.InsuredID = t2.Insured
where t3.DateDeath is not null --and t1.>t3.DateDeath

--Проверка по Федеральному справочнику V016 
insert into #Errors
select 4, 'Значение атрибута DISP ('+t1.DISP+') не содержится в справочнике V016. ID_PAC('+cast(t1.ID_PAC as varchar)+') NPOLIS('+cast(t1.NPOLIS as varchar)+')'
from #PERS t1
left join [ISZL].T_V016_DISPT v016 on v016.IDDT = t1.DISP and v016.DATEEND is null
where v016.V016ID is null

-- Проверка поля. 
insert into  #Errors (CODE, COMMENT)
select 5, 'Пол это обязательное атрибут!'
from #PERS p
where p.[W] is null

-- Проверка соответствия введенных кодировок для пола. 
insert into  #Errors (CODE, COMMENT)
select 6, 'Неверный код пола застрахованного лица. p.W ('+cast(p.[W] As varchar)+')'
from #PERS p
where p.W not in (Select IDPOL
from ISZL.T_V005_SEX)

-- Проверка соответствия введенных кодировок для типов полиса. 
insert into  #Errors (CODE, COMMENT)
select 7, 'Неверный тип полиса. p.[VPOLIS] ('+cast(p.[VPOLIS] As varchar)+')'
from #PERS p
where p.[VPOLIS] not in (select IDDOC
	from ISZL.T_F008_OMS_TYPE)

-- Проверка допустимых значений поля "Месяц отчетного периода проведения Профилактических мероприятий"
insert into  #Errors (CODE, COMMENT)
select 8, 'Номер месяца отчетного периода указан неверно. p.[DISP_MONTH] ('+cast(p.[DISP_MONTH] as varchar)+')'
from #PERS p
where p.[DISP_MONTH] not BETWEEN 1 AND 12;

-- проверка на дубликат Фамилия+Имя+Отчество (при наличии)+Дата рождения+Тип профилактического мероприятия+Год прохождения профилактического мероприятия.
insert into #Errors (CODE, COMMENT)
select 9, 'Уже существует направление на проф. мероприятие с указанными параметрами. NPOLIS(' + p.NPOLIS + ')'+' ФИО '+p.FAM+' '+p.IM +' '+p.OT+ ' '+cast(p.DR as varchar)+ ' '+ cast(p.[YEAR] as varchar)
from #PERS p
join iszl.T_F003 mo on mo.MCOD = p.CODE_MO
join iszl.T_DISP_DIRECT dd
on (dd.FAM = p.FAM
	and dd.IM = p.IM
	and dd.OT = p.OT
	and dd.DR = p.DR
	and dd.[YEAR] = p.[YEAR])
where dd.type_ = (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT')

-- контроль на возраст и тип профилактического мероприятия. 
insert into  #Errors (CODE, COMMENT)
select 10, 'В возрасте '+ cast( datediff(year, p.DR, GETDATE()) as varchar)+' диспансеризации взрослого населения не подлежат.'
from #PERS p
where datediff(year, p.DR, GETDATE())<39 and datediff(year, DR, GETDATE()) not in (18, 21, 24, 27, 30, 33, 36)

--Проверка метки о приоритезации о прохождении ПМ является обязательным
insert into  #Errors (CODE, COMMENT)
select 10, 'Метки о приоритезации о прохождении ПМ  неверны p.PR ('+cast(p.[PR] As varchar)+').'
from #PERS p
where  p.PR>4 or p.PR<1


if ((select count(*) from #Errors) = 0)
begin
	--------
declare @filename varchar(250)
	set @filename = (select (FORMATMESSAGE('PROF_%s_%i_%s_%i_%s_%i',convert(varchar, z.[DATA], 12),z.[YEAR],z.[TYPE],z.[CODE_MO],z.[ID_MO],z.[SMO])) from #ZGLV z)

	--checkFileExist
	declare @fileid uniqueidentifier
	set @fileid = (
				select top 1 dd.[FileID] 
				from [ISZL].[T_DISP_DIRECT_FILE] dd 
				where dd.[FILENAME] = @filename
			)

	declare @type varchar(6)
	set @type = (select [TYPE] from #ZGLV)

if (@type='Insert') ---– добавление сведений, 
begin

	DELETE FROM [ISZL].T_DISP_DIRECT
	WHERE [ID] = (select [ID] from #PERS p)

		 INSERT INTO [ISZL].T_DISP_DIRECT
		 (
			   [DispDirectID]
			  ,[DispDirectFile]
			  ,[ID_PAC]
			  ,[ID]
			  ,[FAM]
			  ,[IM]
			  ,[OT]
			  ,[Sex]
			  ,[DR]
			  ,[ENP]
			  ,[VPOLIS]
			  ,[SPOLIS]
			  ,[NPOLIS]
			  ,[YEAR]
	          ,[PR]
			  ,[V016Dispt]
			  ,[MONTH_Q]
			  ,[Smo]--[CODE_SMO]
			  ,[Mo]--[CODE_MO]
			  ,[Insured]
			  ,[InsuredPolicyRecord]
			  ,[FileName]

		 )
		 select p.[ItemID], null
				, p.[ID_PAC]
				, p.[ID]
				,p.[FAM],p.[IM],p.[OT],p.[W] ,p.[DR], ins.[ENP], p.[VPOLIS]
				,p.[SPOLIS],p.[NPOLIS]
				,p.[YEAR] 
				,p.[PR]
				,v016.V016ID ,p.[DISP_MONTH]
  				,orgsmo.OrganizationID, orgmo.OrganizationID, t2.[Insured], t2.[InsuredPolicyRecordID],  @filename
		 from #PERS p
		 join [ISZL].T_INSURED_POLICY_RECORD t2 on t2.[InsuredPolicyRecordID] = p.[ActualInsuredPolicyRecordID]
		 join [ISZL].T_INSURED ins on ins.InsuredID = t2.Insured
		 join [ISZL].T_V016_DISPT v016 on v016.IDDT = p.DISP and v016.DATEEND is null
		 left join [ISZL].T_ORGANIZATION orgsmo on orgsmo.Code = p.SMO
		 left join [ISZL].T_ORGANIZATION orgmo on orgmo.Code = p.[CODE_MO]

		INSERT INTO [ISZL].T_INSURED_CONTACT
		(
			 InsuredContactID
			 ,Contact
			 ,ContactType
			 ,Insured
		)
		select
			newid()
			,p.[PHONE1]
			,null--PatientContactType
			,t2.Insured
		from #PERS p
		 join [ISZL].T_INSURED_POLICY_RECORD t2 on t2.[InsuredPolicyRecordID] = p.[ActualInsuredPolicyRecordID]
		where p.[PHONE1] is not null and ((select count(Contact) from [ISZL].T_INSURED_CONTACT where Contact = p.[PHONE1] ) = 0)

		INSERT INTO [ISZL].T_INSURED_CONTACT
		(
			  InsuredContactID
			 ,Contact
			 ,ContactType
			 ,Insured
		)
		select
			newid()
			,p.[PHONE2]
			,null--PatientContactType
			,t2.Insured
		from #PERS p
		 join [ISZL].T_INSURED_POLICY_RECORD t2 on t2.[InsuredPolicyRecordID] = p.[ActualInsuredPolicyRecordID]
		where p.[PHONE2] is not null and ((select count(Contact) from [ISZL].T_INSURED_CONTACT where Contact = p.[PHONE2] ) = 0)	
	end
	if (@type='Update')
	begin
		--обновляем данные где type = 1
		 update dd
		 set 
			 dd.[DispDirectFile] = @fileid 
			,dd.[V016Dispt] = v016.V016ID
			,dd.[MONTH_Q] = p.DISP_MONTH
		 from  [ISZL].T_DISP_DIRECT dd
			join #PERS p on dd.[ID_PAC] = p.[ID_PAC] and dd.[YEAR] = p.[YEAR] 
			left join [ISZL].T_V016_DISPT v016 on v016.IDDT = p.DISP and v016.DATEEND is null						
			left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = p.[W]
		   -- left join [ISZL].T_DISP_DIRECT_FACT fact on fact.DispDirect = dd.DispDirectID
		 where dd.[YEAR] = p.[YEAR] and dd.V016Dispt = v016.V016ID-- and fact is null
	 end
	if (@type='Delete')
	begin
		--Удаляем из [ISZL].T_DISP_DIRECT 
		delete dd
		from [ISZL].T_DISP_DIRECT dd
		join #PERS p on dd.[ID] = p.[ID] and dd.[YEAR] = p.[YEAR]
		left join [ISZL].T_V016_DISPT v016 on v016.IDDT = p.[DISP] and v016.DATEEND is null
		where dd.[DispDirectFile]=@fileid and dd.[V016Dispt] = v016.V016ID
	 end
end

select (case 
	   when (select COUNT(*) from #Errors) = 0 
	   then cast(1 as bit)
	   else case
			when (select COUNT(*) from #Errors) > 0
			then cast(0 as bit)
			end
	   end),
	   '<?xml version="1.0" encoding="Windows-1251"?>' 
		+ 
		(
			SELECT 
				CODE as 'CODE'
				,COMMENT as 'COMMENT'
			FROM #Errors 
			FOR XML PATH('ERR'),ROOT('ROOT')
		)
DROP TABLE #Errors
DROP TABLE #PERS
DROP TABLE #ZGLV
END

