if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_MotionInHospitalLoad_karelia]') and xtype = N'P')
drop procedure [dbo].[sp_MotionInHospitalLoad_karelia]
GO
create procedure dbo.sp_MotionInHospitalLoad_karelia @x xml
as
begin
SELECT 
	 T.c.value('./DATA[1]', 'date') AS [DATA]
	,T.c.value('./TYPE[1]', 'varchar(6)') AS [TYPE]
	,T.c.value('./ID[1]', 'varchar(36)') AS [ID]
INTO #ZGLV
FROM @x.nodes('MOTION_IN_HOSPITAL//ZGLV') T(c)

SELECT 
  T.c.value('../ZGLV[1]/ID[1]','varchar(36)') as [ID]
 ,T.c.value('./NOM_NAP[1]', 'varchar(16)') AS [NOM_NAP]
 ,T.c.value('./DTA_NAP[1]', 'datetime') AS [DTA_NAP]
 ,T.c.value('./FRM_MP[1]', 'int') AS [FRM_MP]
 ,T.c.value('./MCOD_STC[1]', 'varchar(6)') AS [MCOD_STC]
 ,T.c.value('./MPODR_STC[1]','varchar(2)') AS [MPODR_STC]
 ,T.c.value('./DTA_FKT[1]', 'datetime') AS [DTA_FKT]
 ,T.c.value('./DTA_END[1]', 'datetime') AS [DTA_END]
 ,T.c.value('./SMO_CODE[1]', 'varchar(5)') AS [SMO_CODE]
 ,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('./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('./USL_OK[1]', 'int') AS [USL_OK]
 ,T.c.value('./KOD_PFO[1]', 'int') AS [KOD_PFO]
 ,T.c.value('./KOD_PFK[1]', 'int') AS [KOD_PFK]
 ,T.c.value('./NHISTORY[1]', 'varchar(50)') AS [NHISTORY]
 ,T.c.value('./insured[1]', 'varchar(36)') AS [insured]
 ,T.c.value('./hospId[1]', 'varchar(36)') AS [hospId]
 
 ,T.c.value('./branchid[1]', 'varchar(36)') as [branchid]--
 ,T.c.value('./profilbedid[1]', 'varchar(36)') as [profilbedid]--
INTO #ZAP
FROM @x.nodes('MOTION_IN_HOSPITAL//ZAP') T(c)

create table #Errors
(
	CODE int,
	COMMENT varchar(250)
)
	
	-- Подбор по ЕНП скорее всего не будет работать тк есть дубли T_INSURED
	--update z
	--set z.Insured = i.InsuredID
	--from #ZAP z
	--cross apply(
	--	select top 1 i.InsuredID
	--	from ISZL.T_INSURED i
	--	where i.ENP is not null
	--			and
	--		i.ENP = z.NPOLIS
	--	order by i.InsuredID
	--) i
	--where z.[VPOLIS] = 3 and z.NPOLIS is not null and z.Insured is null

	-- Подбор по ФИО + дата рождения + номер полиса
	update z
	set z.Insured = i.InsuredID
	from #ZAP z
		cross apply
		(
			select top 1 ipr.Insured InsuredID
			from ISZL.T_INSURED_POLICY_RECORD ipr
				join ISZL.T_POLICY p on p.PolicyID = ipr.[Policy]
			where ipr.FIO is not null
					and
				ipr.FIO = REPLACE(z.FAM + CHAR(160) + z.IM + isnull(CHAR(160) + z.OT, CHAR(160)), 'ё', 'е')
					and
				ipr.Birthday is not null
					and
				ipr.Birthday = z.DR
					and
				p.Number is not null
					and
				p.Number = z.NPOLIS
				--	and
				--ipr.DateStop is null
			order by DateBegin desc
		) i
	where z.FAM is not null and z.IM is not null and z.DR is not null and z.NPOLIS is not null and z.Insured is null

	-- Подбор по ФИО + номер полиса
	update z
	set z.Insured = i.InsuredID
	from #ZAP z
		cross apply
		(
			select top 1 ipr.Insured InsuredID
			from ISZL.T_INSURED_POLICY_RECORD ipr
				join ISZL.T_POLICY p on p.PolicyID = ipr.[Policy]
			where ipr.FIO is not null
					and
				ipr.FIO = REPLACE(z.FAM + CHAR(160) + z.IM + isnull(CHAR(160) + z.OT, CHAR(160)), 'ё', 'е')
					and
				p.Number is not null
					and
				p.Number = z.NPOLIS
				--	and
				--ipr.DateStop is null
			order by DateBegin desc
		) i
	where z.FAM is not null and z.IM is not null and z.NPOLIS is not null and z.Insured is null

	-- Подбор по ФИО + дата рождения
	update z
	set z.Insured = i.InsuredID
	from #ZAP z
		cross apply
		(
			select top 1 ipr.Insured InsuredID
			from ISZL.T_INSURED_POLICY_RECORD ipr
			where ipr.FIO is not null
					and
				ipr.FIO = REPLACE(z.FAM + CHAR(160) + z.IM + isnull(CHAR(160) + z.OT, CHAR(160)), 'ё', 'е')
					and
				ipr.Birthday is not null
					and
				ipr.Birthday = z.DR
				--	and
				--ipr.DateStop is null
			order by DateBegin desc
		) i
	where z.FAM is not null and z.IM is not null and z.DR is not null and z.Insured is null

--Ищем пациентов
--step 0 (фамилия, имя, отчество, дата рождения)
--update t1
--set t1.[insured] =  (
--	select ins.InsuredID from ISZL.T_INSURED ins
--	where ins.LastName = t1.FAM and ins.FirstName = t1.IM and ins.Patronymic = t1.OT and ins.Birthday = t1.DR
-- )
--from #ZAP t1
--where t1.[insured] is null 

--step 1 (фамилия, имя, дата рождения)
--update t1
--set t1.[insured] =  (
--	select ins.InsuredID from ISZL.T_INSURED ins
--	where ins.LastName = t1.FAM and ins.FirstName = t1.IM and ins.Birthday = t1.DR
-- )
--from #ZAP t1
--where t1.[insured] is null


--update t1
--set t1.[insured] =  (
--	select top 1 ipr.Insured from ISZL.T_INSURED_POLICY_RECORD ipr
--	join ISZL.T_INSURED ins on ins.InsuredID = ipr.Insured
--	where ins.LastName = t1.FAM and ins.FirstName = t1.IM and (ins.ENP = t1.NPOLIS)
-- )
--from #ZAP t1
--where t1.[insured] is null and t1.VPOLIS = 3

--step 2 (фамилия, имя, номер+серия полиса)
--update t1
--set t1.[insured] =  (
--	select top 1 ipr.Insured from ISZL.T_INSURED_POLICY_RECORD ipr
--	join ISZL.T_INSURED ins on ins.InsuredID = ipr.Insured
--	join [ISZL].T_POLICY p on p.PolicyID = ipr.Policy
--	where ins.LastName = t1.FAM and ins.FirstName = t1.IM and p.Number = t1.NPOLIS and isnull(p.Series,'') = isnull(t1.SPOLIS,'')
-- )
--from #ZAP t1
--where t1.[insured] is null

--update t1
--set t1.[insured] =  (
--	select top 1 ipr.Insured from ISZL.T_INSURED_POLICY_RECORD ipr
--	join ISZL.T_INSURED ins on ins.InsuredID = ipr.Insured
--	join [ISZL].T_POLICY p on p.PolicyID = ipr.Policy
--	where ins.LastName = t1.FAM and ins.Birthday = t1.DR and p.Number = t1.NPOLIS and isnull(p.Series,'') = isnull(t1.SPOLIS,'')
-- )
--from #ZAP t1
--where t1.[insured] is null


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

--не в каждой госпитализации есть дата и номер направления, поэтому убрала
--и привела дату к datetime, потому что не проходила как дата
update t1
set t1.hospId = (
	select hosp.HospitalizationID from ISZL.T_HOSPITALIZATION hosp
	where hosp.Insured = t1.insured
	and hosp.HOSPITALIZATION_DATE = cast(t1.DTA_FKT as datetime)
	and hosp.MED_CARD_NUMBER = t1.NHISTORY
	and hosp.MO = t1.MCOD_STC
)
from #ZAP t1
where t1.insured is not null

insert into #Errors
select 1, 'Не найдена соответствующая госпитализация'
from #ZAP zap
where zap.hospId is null and @type = 'Insert'

insert into #Errors
select 2, 'Застрахованный не найден ни по ФИО и ДР, ни по номеру и серии полиса'
from #ZAP zap
where zap.insured is null  and @type <> 'Delete'

insert into #Errors
select 3, 'Застрахованный умер'
from #ZAP zap
join ISZL.T_INSURED ins on ins.InsuredID = zap.[insured]
where ins.DateDeath is not null and zap.DTA_FKT >ins.DateDeath  and @type <> 'Delete'

insert into #Errors
select 4, 'Найдено больше одного подразделения с кодом: ' + cast(zap.MPODR_STC as varchar) + '. Для МО:' + cast(zap.MCOD_STC as varchar)
from #ZAP zap
where 
(select COUNT(*) from ISZL.T_BRANCH br 
where br.BR_CODE = zap.MPODR_STC and br.F003Mo = zap.MCOD_STC) > 1 and @type <> 'Delete'

update zap
set zap.[branchid] = (select br.BranchID from ISZL.T_BRANCH br 
where br.BR_CODE = zap.MPODR_STC and br.F003Mo = zap.MCOD_STC) 
from #ZAP zap 
where  @type <> 'Delete'

insert into #Errors
select 5, 'Не найдено подразделения с кодом: ' + cast(zap.MPODR_STC as varchar) + '. Для МО:' + cast(zap.MCOD_STC as varchar)
from #ZAP zap
where zap.[branchid] is null and @type <> 'Delete'

if (@type = 'Insert')
begin
	--если нет ошибок, то вставка
	insert into ISZL.T_MOTION_IN_HOSPITAL
	(
		 [MotionInHospitalID]
		,[HOSPITALIZATION_DATE]
		,[MED_CARD_NUMBER]
		,[MO]
		,[DIVISION]
		,[V020_STRUCTURE_BED]
		,[DATE_IN]
		,[Hospitalization]
		,[BRANCH]
		,[DATE_OUT]
		,[USL_OK]
		,[smo_code]
		,[vpolis]
		,[spolis]
		,[npolis]
	)
	select
		 zap.ID
		,zap.DTA_FKT
		,zap.NHISTORY
		,zap.MCOD_STC
		,zap.KOD_PFO
		,(select top 1 bed.IDK_PR from ISZL.T_V020_STRUCTURE_BED bed where bed.StructureBed = zap.KOD_PFK)
		,zap.DTA_FKT
		,zap.hospId
		,zap.MPODR_STC
		,zap.DTA_END
		,zap.USL_OK
		,zap.SMO_CODE
		,zap.VPOLIS
		,zap.SPOLIS
		,zap.NPOLIS
	from #ZAP zap
	where (select COUNT(*) from #Errors) = 0
end

if (@type = 'Update')
begin
	--проверка
	declare @exists uniqueidentifier
	set @exists = (
	select 
	hosp.MotionInHospitalID 
	from ISZL.T_MOTION_IN_HOSPITAL hosp
	join #ZAP zap on zap.ID = hosp.MotionInHospitalID )

	if(@exists is null)
	begin
		insert into #Errors
		select 7, 'Не найдены соответствующие сведения о выбытии пациента'
		from #ZAP zap
	end
	else
	begin
		update t1
		set  t1.HOSPITALIZATION_DATE = zap.DTA_FKT
			,t1.MED_CARD_NUMBER = zap.NHISTORY
			,t1.MO = zap.MCOD_STC
			,t1.DIVISION = zap.KOD_PFO
			,t1.V020_STRUCTURE_BED = (select top 1 bed.IDK_PR from ISZL.T_V020_STRUCTURE_BED bed where bed.StructureBed = zap.KOD_PFK)
			,t1.DATE_IN = zap.DTA_FKT
			,t1.BRANCH = zap.MPODR_STC
			,t1.DATE_OUT = zap.DTA_END
			,t1.USL_OK = zap.USL_OK
			,t1.smo_code = zap.SMO_CODE
			,t1.vpolis = zap.VPOLIS
			,t1.spolis = zap.SPOLIS
			,t1.npolis = zap.NPOLIS
		from ISZL.T_MOTION_IN_HOSPITAL t1
		join #ZAP zap on t1.MotionInHospitalID = zap.ID
		where (select count(*) from #Errors) = 0
	end
end

if (@type = 'Delete')
begin
	declare @existsdel uniqueidentifier
	set @existsdel = (
	select 
	hosp.MotionInHospitalID 
	from ISZL.T_MOTION_IN_HOSPITAL hosp
	join #ZAP zap on zap.ID = hosp.MotionInHospitalID )

	if(@existsdel is null)
	begin
		insert into #Errors
		select 8, 'Не найдены соответствующие сведения о выбытии пациента'
		from #ZAP zap
	end
	else
	begin
		delete t1
		from ISZL.T_MOTION_IN_HOSPITAL t1
		join #ZAP zap on zap.ID = t1.MotionInHospitalID
		where (select count(*) from #Errors) = 0
	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 #ZGLV
DROP TABLE #ZAP
DROP TABLE #Errors
end
