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