SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sendRegistr]') and xtype = N'P')
drop procedure [dbo].[sp_sendRegistr]
GO
-- =============================================
-- Author:		ЗАО Витакор
-- Create date: 2016.02.11
-- Description:	[dbo].[sp_sendRegistr] 
-- =============================================
CREATE PROCEDURE [dbo].[sp_sendRegistr] 
	@doc xml	-- xml
As
Begin
	SET NOCOUNT ON;

	If @doc Is NULL
	begin
		Select 1 ExitCode;
		Select 'Ошибка' Err;
		goto ENDING;
	end

	INSERT INTO [AKUZ].[T_ASULON_LOG]([AsulonLogID],[RequestTime],[MessageType],[RequestData])
    VALUES(NEWID(),GETDATE(),'sp_sendRegistr',cast(@doc as varchar(max)))
--    
--регистр 7 нозологий, registrType 3
--
    ;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://ws.hostco.ru/types' as s01, DEFAULT 'http://ws.hostco.ru/types')
	select
		  T.c.value('./UNRZ[1]' ,'varchar(16)') as UNRZ
		  ,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]' ,'varchar(1)') as W
		  ,T.c.value('./DR[1]' ,'varchar(10)') as DR
		  ,T.c.value('./DB_EDV[1]' ,'varchar(10)') as DB_EDV
		  ,T.c.value('./C_KAT1[1]' ,'varchar(3)') as C_KAT1
		  ,T.c.value('./DATE_RSB[1]' ,'varchar(10)') as DATE_RSB
		  ,T.c.value('../../registrType[1]' ,'int') as registrType
	into #fr
	from @doc.nodes('types:privilegedRegistr/federalRegistr/Privileged') T(c)
	where T.c.value('../../registrType[1]' ,'int') = 3

	update f
	set C_KAT1 = '0'+right(C_KAT1,2)
	from #fr f
	where SUBSTRING(f.C_KAT1 ,1 ,1) <> '0'

	--delete f
	--from #fr f
	--where registrType <> '3'

--обновляем существующие льготы 7 нозологий, введенные вручную
	update pb
	set PAC_ID = (select top 1 LTRIM(RTRIM(f.UNRZ)) from #fr f
								where p.LastName = f.FAM
									and p.FirstName = f.IM
									and p.FatherName = f.OT
									and p.Birthday = CAST(f.DR as datetime)
									and b.BenefitCode = f.C_KAT1)
		,BeginDate = (select top 1 f.DATE_RSB from #fr f
						where p.LastName = f.FAM
							and p.FirstName = f.IM
							and p.FatherName = f.OT
							and p.Birthday = CAST(f.DR as datetime)
							and b.BenefitCode = f.C_KAT1)
		,CreateType = 2
		,LPU_INF = null
	--select pb.*
	from [AKUZ].T_PATIENT_BENEFITS pb
	join [AKUZ].T_PATIENT p on p.PatientID = pb.Patient
	join [AKUZ].T_BENEFITS b on b.BenefitsID=pb.Benefits and b.BenefitViews=19006
	where pb.CreateType = 1
	and exists (select top 1 * from #fr f
					where p.LastName = f.FAM
						and p.FirstName = f.IM
						and p.FatherName = f.OT
						and p.Birthday = CAST(f.DR as datetime)
						and b.BenefitCode = f.C_KAT1
						and pb.BeginDate = f.DATE_RSB
						and pb.PAC_ID = f.UNRZ)

--вставка отсутствующих льгот
	insert into [AKUZ].T_PATIENT_BENEFITS (PatientBenefitsID
		  ,Patient
		  ,Benefits
		  ,BenefitDocument
		  ,BenefitDocumentNumber
		  ,BeginDate
		  ,EndDate
		  ,Mkb
		  ,Active
		  ,Deleted
		  ,CreateType
		  ,PAC_ID
		  ,LPU_INF
		  ,SOST_R
		  ,ReasonRegistration
		  ,ReasonDeregistration
		  ,BenefitDocumentSeries
		  ,BenefitDocType
		  ,RegDate)
	select NEWID()
		  ,(select top 1 p.PatientID from [AKUZ].T_PATIENT p
			where p.LastName = f.FAM
				and p.FirstName = f.IM
				and p.FatherName = f.OT
				and p.Birthday = CAST(f.DR as datetime))
		  ,(select top 1 b.BenefitsID from [AKUZ].T_BENEFITS b
			where b.BenefitViews = 19006
				and b.BenefitCode = f.C_KAT1)
		  ,null
		  ,null
		  ,f.DATE_RSB
		  ,null
		  ,null
		  ,1
		  ,0
		  ,2
		  ,f.UNRZ
		  ,null
		  ,null
		  ,null
		  ,null
		  ,null
		  ,null
		  ,null	
	from #fr f
	where not exists (select top 1 * from [AKUZ].T_PATIENT_BENEFITS pb
						join [AKUZ].T_BENEFITS b on b.BenefitsID = pb.Benefits and b.BenefitViews = 19006
						join [AKUZ].T_PATIENT p on p.PatientID = pb.Patient
						where p.LastName = f.FAM
							and p.FirstName = f.IM
							and p.FatherName = f.OT
							and p.Birthday = CAST(f.DR as datetime)
							and b.BenefitCode = f.C_KAT1
							and pb.BeginDate = f.DATE_RSB
							and pb.PAC_ID = f.UNRZ)
	and exists (select top 1 * from [AKUZ].T_PATIENT p
				where p.LastName = f.FAM
					and p.FirstName = f.IM
					and p.FatherName = f.OT
					and p.Birthday = CAST(f.DR as datetime))
	and exists (select top 1 * from [AKUZ].T_BENEFITS b
				where b.BenefitViews = 19006
					and b.BenefitCode = f.C_KAT1)

--апдейты, чтобы не нарушить логику					
	update pb
	set EndDate = null
	--select *
	from [AKUZ].T_PATIENT_BENEFITS pb
	join [AKUZ].T_BENEFITS b on b.BenefitsID = pb.Benefits and b.BenefitViews=19006
	where CreateType = 2 and EndDate >= GETDATE() and EndDate is not null

	update pb
	set pb.BeginDate = DATEADD(YY ,-1 ,pb.EndDate)
	--select pb.begindate ,pb.enddate ,DATEADD(YY ,-1 ,pb.EndDate)
	from [AKUZ].T_PATIENT_BENEFITS pb
	join [AKUZ].T_BENEFITS b on b.BenefitsID=pb.Benefits and b.BenefitViews=19006
	where CreateType = 2 and BeginDate>=EndDate and EndDate is not null
	
	update pb
	set LPU_INF = null
	--select *
	from [AKUZ].T_PATIENT_BENEFITS pb
	join [AKUZ].T_BENEFITS b on b.BenefitsID = pb.Benefits and b.BenefitViews=19006
	where CreateType = 2 and LPU_INF is not null
	
	drop table #fr
--	
--регистр федеральных льготников, registrType 1
--
	DECLARE @ver int

   ;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://ws.hostco.ru/types' as s01, DEFAULT 'http://ws.hostco.ru/types')
	select
		  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]' ,'varchar(1)') as W
		  ,T.c.value('./DR[1]' ,'varchar(10)') as DR
		  ,T.c.value('./DB_EDV[1]' ,'varchar(10)') as DB_EDV
		  ,T.c.value('./DATE_RSB[1]' ,'varchar(10)') as DATE_RSB
		  ,SUBSTRING((T.c.value('./SNILS[1]' ,'char(14)')), 1, 3) + '-' + SUBSTRING((T.c.value('./SNILS[1]' ,'char(14)')), 4, 3) + '-' + SUBSTRING((T.c.value('./SNILS[1]' ,'char(14)')), 7, 3) + ' ' + SUBSTRING((T.c.value('./SNILS[1]' ,'char(14)')), 10, 2) as SNILS
		  ,T.c.value('./DATE_RSE[1]' ,'varchar(10)') as DATE_RSE
	into #fr1
	from @doc.nodes('types:privilegedRegistr/federalRegistr/Privileged') T(c)
	where T.c.value('../../registrType[1]' ,'int') = 1

   ;WITH XMLNAMESPACES ('http://ws.hostco.ru' as types, 'http://ws.hostco.ru/types' as s01, DEFAULT 'http://ws.hostco.ru/types')
	select
		  T.c.value('./C_KAT[1]' ,'varchar(3)') as C_KAT
		  ,T.c.value('./NAME_DL[1]' ,'varchar(255)') as NAME_DL
		  ,T.c.value('./SN_DL[1]' ,'varchar(255)') as SN_DL
		  ,T.c.value('./DATE_BL[1]' ,'varchar(10)') as DATE_BL
		  ,T.c.value('./DATE_EL[1]' ,'varchar(10)') as DATE_EL
		  ,T.c.value('../../DB_EDV[1]' ,'varchar(10)') as DB_EDV
		  ,T.c.value('../../DE_EDV[1]' ,'varchar(10)') as DE_EDV
		  ,SUBSTRING((T.c.value('../../SNILS[1]' ,'char(14)')), 1, 3) + '-' + SUBSTRING((T.c.value('../../SNILS[1]' ,'char(14)')), 4, 3) + '-' + SUBSTRING((T.c.value('../../SNILS[1]' ,'char(14)')), 7, 3) + ' ' + SUBSTRING((T.c.value('../../SNILS[1]' ,'char(14)')), 10, 2) as SNILS
	into #fr2
	from @doc.nodes('types:privilegedRegistr/federalRegistr/Privileged/PrivdCategories/Category') T(c)
	where T.c.value('../../../../registrType[1]' ,'int') = 1

	select @ver = max([Version]) from [AKUZ].T_FEDERAL_REGISTER_LOAD
	if @ver is null
	BEGIN
		SET @ver = 1
	END
	ELSE
	BEGIN
		SET @ver = @ver + 1
	END

	-- делаем insert в [AKUZ].T_FEDERAL_REGISTER_LOAD		
	INSERT INTO [AKUZ].[T_FEDERAL_REGISTER_LOAD]
		([FederalRegisterLoadID],[Users],[Version],[UpdateDate])
	select
		NEWID(),
		NULL,
		@ver,
		GETDATE()
	
	--новые снилсы у неисключенных из регистра
	INSERT INTO [AKUZ].[T_SNILS]
		([Snils])
	select p.Snils
	from #fr1 p
	where not exists 
	(
		select Snils
		from [AKUZ].T_SNILS s
		where s.Snils = p.Snils
	) and (p.date_rse is null or year(p.date_rse) = year(getdate()))
	group by p.SNILS		
	
	----delete
	--select *
	--from [AKUZ].T_FR_PATIENT

	insert into [AKUZ].T_FR_PATIENT (FrPatientID, Snils, LastName, FirstName, FatherName, Birthday, Sex)
	select NEWID()
		,r1.SNILS
		,rtrim(ltrim(r1.fam))
		,rtrim(ltrim(r1.im))
		,rtrim(ltrim(r1.ot))
		,cast(r1.dr as datetime)
		,CASE WHEN (UPPER(LTRIM(RTRIM(r1.W))) IN ('Ж')) THEN 1 ELSE 0 END
	from #fr1 r1
	where exists (select * from #fr2 r2
									where r1.SNILS=r2.SNILS
									and (r2.date_el is null or cast(r2.date_el as datetime) > GETDATE()))
									and not	exists (select 0 from [AKUZ].T_FR_PATIENT where Snils = r1.SNILS) 
									and (r1.date_rse is null or year(r1.date_rse) = year(getdate()))
	
	--update [AKUZ].T_FR_PATIENT
	--set	FirstName = rtrim(ltrim(r1.fam))
	--	,LastName = rtrim(ltrim(r1.im))
	--	,FatherName = rtrim(ltrim(r1.ot))
	--	,Birthday = cast(r1.dr as datetime)
	--	,Sex = CASE WHEN (UPPER(LTRIM(RTRIM(r1.W))) IN ('Ж')) THEN 1 ELSE 0 END
	--from #fr1 r1
	--where [AKUZ].T_FR_PATIENT.Snils = r1.Snils and exists (select * from #fr2 r2
	--								where r1.SNILS=r2.SNILS
	--								and (r2.date_el is null or cast(r2.date_el as datetime) > GETDATE())) 
	--								and (r1.date_rse is null or year(r1.date_rse) = year(getdate()))
	
	update pat
	set	pat.FirstName = rtrim(ltrim(r1.im))
		,pat.LastName = rtrim(ltrim(r1.fam))
		,pat.FatherName = rtrim(ltrim(r1.ot))
		,pat.Birthday = cast(r1.dr as datetime)
		,pat.Sex = CASE WHEN (UPPER(LTRIM(RTRIM(r1.W))) IN ('Ж')) THEN 1 ELSE 0 END
	from [AKUZ].T_FR_PATIENT pat
	join #fr1 r1 on(pat.Snils = r1.Snils)
	where exists (select * from #fr2 r2
									where r1.SNILS=r2.SNILS
									and (r2.date_el is null or cast(r2.date_el as datetime) > GETDATE())) 
									and (r1.date_rse is null or year(r1.date_rse) = year(getdate()))
	
	----delete
	--select *	
	--from [AKUZ].T_FR_DOCUMENT

	--в начале всё, кроме групп инвалидности, потом макс! действующую группу инвалидности
	update d
		set Name = ltrim(rtrim(r2.NAME_DL))
		, Number = ltrim(rtrim(r2.sn_dl))
	from [AKUZ].T_FR_DOCUMENT d
	join #fr2 r2 on r2.SNILS = d.Snils
	join #fr1 r1 on r1.SNILS = r2.SNILS and (r1.date_rse is null or year(r1.date_rse) = year(getdate()))
	join [AKUZ].T_BENEFITS b on b.BenefitCode = r2.c_kat and b.BenefitViews = 19001 
	where b.BenefitsID = d.Benefits and (r2.date_el is null or CAST(r2.date_el as datetime) > GETDATE()) and r2.c_kat not in (081, 082, 083)

	update d
		set Name = ltrim(rtrim(r2.NAME_DL))
		, Number = ltrim(rtrim(r2.sn_dl))
		, Benefits = b.BenefitsID
	from [AKUZ].T_FR_DOCUMENT d
	join #fr2 r2 on r2.SNILS = d.Snils
	join #fr1 r1 on r1.SNILS = r2.SNILS and (r1.date_rse is null or year(r1.date_rse) = year(getdate()))
	join [AKUZ].T_BENEFITS b on b.BenefitCode = r2.c_kat and b.BenefitViews = 19001
	where exists
		(
			select MAX(r21.c_kat) from #fr2 r21
			where r2.SNILS = r21.SNILS
				and (r21.date_el is null or CAST(r21.date_el as datetime) > GETDATE())
				and r21.c_kat in (081, 082, 083)
			group by r21.SNILS
			having r2.c_kat = MAX(r21.c_kat)
		) and 
		exists(select 0 from [AKUZ].T_BENEFITS bc 
						where bc.BenefitsID = d.Benefits 
						and bc.BenefitCode in (081, 082, 083))

	insert into [AKUZ].T_FR_DOCUMENT(FrDocumentID, Name, Number, Benefits, Snils)
	(
		select NEWID()
		,ltrim(rtrim(r2.name_dl))
		,ltrim(rtrim(r2.sn_dl))
		,b.BenefitsID
		,r2.SNILS		
	from #fr2 r2
	join [AKUZ].T_BENEFITS b on b.BenefitCode = r2.c_kat and b.BenefitViews = 19001
	join #fr1 r1 on r1.SNILS = r2.SNILS and (r1.date_rse is null or year(r1.date_rse) = year(getdate()))
	where (r2.date_el is null or CAST(r2.date_el as datetime) > GETDATE()) and r2.c_kat not in (081, 082, 083)
	and not exists
	(
		select * from [AKUZ].T_FR_DOCUMENT d
		where d.Benefits = b.BenefitsID and r2.SNILS = d.Snils
	)
	union all
	select NEWID()
		,ltrim(rtrim(r2.name_dl))
		,ltrim(rtrim(r2.sn_dl))
		,b.BenefitsID
		,r2.SNILS		
	from #fr2 r2
	join [AKUZ].T_BENEFITS b on b.BenefitCode = r2.c_kat and b.BenefitViews = 19001
	join #fr1 r1 on r1.SNILS = r2.SNILS and (r1.date_rse is null or year(r1.date_rse) = year(getdate()))
	where exists 
		(
			select MAX(r21.c_kat) from #fr2 r21
			where r2.SNILS = r21.SNILS
				and (r21.date_el is null or CAST(r21.date_el as datetime) > GETDATE())
				and r21.c_kat in (081, 082, 083)
			group by r21.SNILS
			having r2.c_kat = MAX(r21.c_kat)
		)
		and not exists
		(
			select * from [AKUZ].T_FR_DOCUMENT d
			where d.Benefits = b.BenefitsID and r2.SNILS = d.Snils
		)
	) 
	
	----delete
	--select *
	--from [AKUZ].T_FR_RECORD
	
	--период, в течение которого действуют все! льготы
	insert into [AKUZ].T_FR_RECORD (FrRecordID, Snils, Deleted, DateBegin, DateEnd, NSU, DateNsuBegin, DateNsuEnd, Nsus, [Version]
	)
	select distinct NEWID()
		,r2.SNILS
		,0
		,coalesce(max(r2.date_bl), cast('1900-01-01' as datetime))
		,min(r2.date_el) 
		,1
		,max(r2.DB_EDV)
		,min(r2.DE_EDV)
		,0
		--,@ver
		,case
          when Len(cast(@ver AS varchar(255))) = 3 then '0' + cast(@ver AS varchar(255))
          else @ver
         end
	from #fr2 r2
	join #fr1 r1 on r1.SNILS = r2.SNILS and (r1.date_rse is null or year(r1.date_rse) = year(getdate()))
	where (r2.date_el is null or CAST(r2.date_el as datetime) > GETDATE())
	group by r2.SNILS	
	having not exists
	(
		select * 
		from [AKUZ].T_FR_RECORD r 		
		where r.DateBegin = max(r2.date_bl) 
		and (r.DateEnd = min(r2.date_el) or r.DateEnd is null) 
		and r.DateNsuBegin = max(r2.DB_EDV) 
		and (r.DateNsuEnd = min(r2.DE_EDV) or r.DateNsuEnd is null)
		and r.Snils = r2.SNILS 
		and r.Version = (select MAX(rm.Version) from [AKUZ].T_FR_RECORD rm where rm.Snils = r2.SNILS)
	)
	
	drop table #fr1
	drop table #fr2

	-- результат
	Select 0 ExitCode;
	Select 'Ok' Msg

ENDING:
	return
End