SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DirectDispancerRDS12]') and xtype = N'P')
DROP PROCEDURE  [dbo].[sp_DirectDispancerRDS12]
GO

-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2017-09-21
-- Description: Формировать список взрослого населения (пациентов),  направляемых на текущюю диспансеризацию и профилакт.мед осмотр (ОПВ) (для Костромы)
-- =============================================

CREATE  PROCEDURE [dbo].[sp_DirectDispancerRDS12]
	@GuidPackage uniqueidentifier
,	@Year int  -- Год формирования списка диспансеризации
,	@LpuMain  varchar(6) -- ЛПУ
,	@Branch  varchar(8) -- Филиал
AS
BEGIN
	SET NOCOUNT ON;

declare
	@Date date
,	@LpuID uniqueidentifier

set @Date = CONVERT (date, GETDATE())

--  Считываем способ фильтрации пациентов на диспансеризацию - по ЛПУ или по филиалам
select  @LpuID = lm.LpuID
from  AKUZ.T_LPU_MAIN lm 
	  join [AKUZ].T_LPU_SETTINGS ls on ls.LpuMain = lm.LpuID
where lm.LpuCode = @LpuMain;

--=================================  Предварительный список на диспансеризацию ==================
	
BEGIN
	;with cte as
	(
		select row_number() over (partition by rg.Patient order by rg.RegistrationDate DESC) as rn, rg.Patient, rg.[Status], rg.RegistrationDate, rg.LpuMain, rg.[State]
		FROM AKUZ.T_REGISTRATION rg 
		LEFT JOIN AKUZ.T_REGISTRATION_CANCEL rc ON rc.Registration = rg.RegistrationID
		WHERE rg.RegistrationDate <= @Date
		AND (rc.RegistrationCancelID IS NULL OR NOT rc.[Status] = 2) 
		AND rg.AttachmentStatus > 0
		AND rg.[Profile] = 1  -- Профиль прикрепления  Терапия
		and rg.[Status] = 1
		and rg.LpuMain = @LpuID
	)
	select
		cte.Patient
	,	cte.[Status]
	,	cte.RegistrationDate
	,	cte.LpuMain 
	,	st.StateNumber
	,	dv.Code 
	into #reg
	from cte
	left join [AKUZ].T_STATE st on st.StateID = cte.[State]
	left join [AKUZ].T_DIVISION dv on dv.DivisionID = st.Division
	where cte.rn = 1
	and dv.Code not in ('99', '999')
	and st.StateNumber is not null;
	
	select ac.AmbulanceCardID, ac.Patient 
	into #ac
	from akuz.T_AMBULANCE_CARD ac
	where ac.CardLpu = @LpuID
	and not exists(select * from akuz.T_DEATH_INFO di where di.AmbulanceCard = ac.AmbulanceCardID);

	create clustered index ix_#ac_patient on #ac(patient) 

	;with cte
	as 
	(
		select 
			pol.PolicyID
		,	pol.ValidThrough
		,	pol.Number
		,	pol.Serial 
		,	pol.PolicyForm
		,	pol.InsuranceCompany
		,	pol.Patient
		,	row_number() over (partition by pol.Patient order by pol.issuedate desc, isnull(pol.validthrough, dateadd(year, 100, pol.issuedate)) desc) as rn
		from AKUZ.T_POLICY pol 
		inner join
			#ac ac
			on ac.Patient = pol.Patient
		where pol.type_ in (536) and @Date >= pol.issuedate
		and pol.Number <> ''
	)
	select 
	cte.PolicyID
	,	cte.ValidThrough
	,	cte.Number
	,	cte.Serial 
	,	cte.PolicyForm
	,	cte.Patient
	,	ins.SmoCod
	into #pol
	from cte
	inner join AKUZ.T_INSURANCE_COMPANY ic on ic.OrganizationID = cte.InsuranceCompany
	inner join AKUZ.T_INSURER ins on ins.InsurerID = ic.Insurer
	where cte.rn = 1
	and ins.SmoCod is not null;

	create index ix_#reg_patient on #reg(patient) include (statenumber, code);
	create index ix_#pol_patient on #pol(patient) include (PolicyForm, Serial, Number);

	SELECT  
		NEWID()							 as RdsDispPatientID
	,	p.PatientID						 as PatientIDDispDirect  -- IDPatient
	,	cast(p.PatientID as varchar(36)) as ID_PAC -- Код записи о пациенте
	,	LEFT(p.LastName,40)				 as FAM
	,	LEFT(p.FirstName,40)			 as IM
	,	LEFT(p.FatherName,40)			 as OT
	,	p.Birthday						 as DR  -- Дата рождения пациента
	,	pol.PolicyForm					 as VPOLIS  -- Тип документа, подтверждающего факт страхования по ОМС
	,	LEFT(pol.Serial,30)				 as SPOLIS -- Серия документа, подтверждающего факт страхования по ОМС
	,	LEFT(pol.Number,32)				 as NPOLIS --Номер документа, подтверждающего факт страхования по ОМС
	,	LEFT(t24.Alias,2)				 as DOCTYPE -- Тип документа, удостоверяющего личность пациента или представителя
	,	LEFT(p.DocSeries,20)			 as DOCSER --Серия документа, удостоверяющего личность пациента или представителя
	,	CASE LTRIM(RTRIM(p.DocNumber)) WHEN  ''  THEN null  ELSE LEFT(LTRIM(RTRIM(p.DocNumber)),20) END as DOCNUM	-- Номер документа, удостоверяющего личность пациента или представителя
	,	CASE p.SNILS WHEN  ''  THEN null  ELSE REPLACE(REPLACE(p.SNILS,'-',''),' ','') END as SNILS	 
	,	LEFT(STUFF((SELECT ';' + Number + case when PhoneType = 1 then 'дом.' when PhoneType = 2 then 'раб.'when PhoneType = 3 then 'моб.' when PhoneType = 4 then 'email' when PhoneType = 5 then 'прочее' else '' end   
		FROM  AKUZ.T_PHONE_NUMBER pn1 where pn1.Patient =   p.PatientID FOR XML PATH ('')), 1, 1, ''),128) as TEL   --  Номер телефона(ов) пациента (только для отслеживания изменений при отсылке в РДС)
	,	LEFT(p.PlaceOfBirth,255)		 as MR		--   Место рождения
	,	@Year							 as [YEAR]  --   Год прохождения диспансеризации
	,	@GuidPackage					 as RdsDispPackage -- AKUZ.RDS_DISP_PACKAGE  Учет направлений на диспансеризацию
	,	DATEPART(m,p.Birthday)			 as MonthDISP    --  месяц прохождения диспансеризации
	,	sex.Code						 as W  --   Пол
	,	0								 as [TYPE]   --  0-добавить
	,	CASE WHEN  (@Year -DATEPART(YYYY,p.Birthday)) >= 21 and ((@Year - DATEPART(YYYY,p.Birthday)) % 3 = 0) and	((@Year -DATEPART(YYYY,p.Birthday)) <= 96) 
			 THEN 0  -- ДВ1
			 when	not exists
					(
						select * from [AKUZ].T_SCHET_ZAP sz  
						inner join 
							[AKUZ].T_SCHET_PACK spck 
							on spck .SchetPackID = sz.SchetPack 
							and spck .PackNamePrefix in ('DO','DP')
						inner join 
							[AKUZ].T_SCHET s 
							on s.SchetID = sz.Schet 
							and s.YEAR in (@Year, @Year-1) 
						where sz.AmbulanceCard = ac.AmbulanceCardID 
					)
			 then 2  -- ОПВ     
		END								 as [STAGE]					   --  Этап Диспансеризации 
	,	ac.AmbulanceCardID				 as AmbulanceCard  -- Амбулаторная карта
	,	LEFT(@Branch,255)				 as BranchCode  -- код филиала
	,	reg.StateNumber					 as UCH     -- Участок прикрепления
	,	reg.Code						 as PODR    -- Подразделение МО
	into #PATIENT
	FROM #ac ac 
	inner JOIN AKUZ.T_PATIENT p ON p.PatientID = ac.Patient
	join VCLib.T_ENUMS t24 ON t24.EnumID = p.DocType
	join VCLib.T_ENUMS sex ON sex.EnumID = p.Sex
	LEFT JOIN AKUZ.T_SNILS t2 ON t2.Snils = p.SNILS
	LEFT JOIN VCLib.T_ENUMS t3 ON t3.EnumID = p.Sex
	inner join
		#pol pol
		on pol.Patient = p.PatientID
	inner join
		#reg reg
		on reg.Patient = p.PatientID
	WHERE @Year - YEAR(p.Birthday) >= 18;
	--or not exists
	--(
	--	select * --sz.AmbulanceCard, spck.PackNamePrefix, s.YEAR 
	--	from [AKUZ].T_SCHET_ZAP sz  
	--	left join [AKUZ].T_SCHET_PACK spck on spck .SchetPackID = sz.SchetPack and  spck .PackNamePrefix in ('DO','DP')
	--	left join [AKUZ].T_SCHET s on s.SchetID = sz.Schet and s.YEAR in (@Year, @Year-1) 
	--	where  sz.AmbulanceCard = ac.AmbulanceCardID 
	--);

	delete from #PATIENT where [STAGE] is null;

	create index ix_#patient_id_pac on #patient(id_pac);

	----===============================   Находим НОВЫХ     ===================================================
	----                                  Новый - такой, что  НЕ СУЩЕСТВУЕТ пациента(ID) в RDS_DISP_PATIENT за текущий год в напрвлениях на диспансеризацию,  
	----									и при этом  его тип  - Добавить или Редактировать, то
	----									добавить  из #PATIENT в T_RDS_DISP_PATIENT с признаком Добавить
	insert into AKUZ.T_RDS_DISP_PATIENT(RdsDispPatientID, PatientIDDispDirect, ID_PAC, FAM, IM, OT, DR, VPOLIS, SPOLIS, NPOLIS, DOCTYPE, DOCSER
	, DOCNUM, SNILS, TEL, MR, [YEAR], RdsDispPackage, MonthDISP, W, [TYPE], STAGE, AmbulanceCard, BranchCode, UCH, PODR, V016DispType)
	select 
		NEWID() as RdsDispPatientID
	,	p.PatientIDDispDirect
	,	p.ID_PAC 
	,	p.FAM
	,	p.IM
	,	p.OT
	,	p.DR
	,	p.VPOLIS
	,	p.SPOLIS
	,	p.NPOLIS
	,	p.DOCTYPE
	,	p.DOCSER
	,	p.DOCNUM
	,	p.SNILS
	,	p.TEL
	,	p.MR
	,	p.[YEAR]
	,	@GuidPackage as RdsDispPackage  -- ID Нового пакета
	,	p.MonthDISP
	,	p.W
	,	0 as [TYPE]  -- Добавленные
	,	p.STAGE
	,	p.AmbulanceCard
	,	p.BranchCode 
	,	p.UCH
	,	p.PODR 
	,	CASE WHEN  STAGE = 0  
			 THEN 'ДВ1'
			 ELSE 'ОПВ'
		END 
	from #PATIENT p 
	WHERE NOT EXISTS
	(
		select * from AKUZ.T_RDS_DISP_PATIENT pt
		inner join 
			AKUZ.T_RDS_DISP_PACKAGE pk 
			on pk.YearDisp = @Year  
			and pk.RdsdispPackageID = pt.RdsDispPackage 
			and pk.CODE_MO = @LpuMain  
		where pt.[YEAR] =  @Year 
		and pt.ID_PAC = p.ID_PAC 
		and pt.[TYPE] in (0, 1)
	);
	
--==================================  Находим УДАЛЕННЫХ   ======================
--									Удаленный - такой пациент в указанном ЛПУ из RDS_DISP_PATIENT, которого нет в #PATIENT и если
--									в последней записи его направления на диспансеризацию  его тип - Добавить или Редактировать, то
--									добавить из #PATIENT в T_RDS_DISP_PATIENT с признаком Удалить
	;with DelPatient
	as
	(
		select pt.RdsDispPatientID, row_number() over (partition by pt.id_pac order by pk.TimeCreate desc) as rn
		from AKUZ.T_RDS_DISP_PATIENT pt 
		inner join 
			AKUZ.T_RDS_DISP_PACKAGE pk 
			on pk.YearDisp = @Year 
			and pk.RdsdispPackageID = pt.RdsDispPackage
			and pk.CODE_MO =  @LpuMain  
		WHERE NOT EXISTS
		(
			select* from #PATIENT p where p.ID_PAC = pt.ID_PAC
		)
		and pt.[TYPE] in (0, 1)
	)
	insert into AKUZ.T_RDS_DISP_PATIENT(RdsDispPatientID, PatientIDDispDirect, ID_PAC, FAM, IM, OT, DR, VPOLIS, SPOLIS, NPOLIS, DOCTYPE, DOCSER, DOCNUM, SNILS,TEL, MR
	, [YEAR], RdsDispPackage, MonthDISP, W, [TYPE], STAGE, AmbulanceCard, BranchCode, UCH, PODR, V016DispType)
	select	
		NEWID() as RdsDispPatientID
	,	pt.PatientIDDispDirect
	,	pt.ID_PAC
	,	pt.FAM
	,	pt.IM
	,	pt.OT
	,	pt.DR
	,	pt.VPOLIS 
	,	pt.SPOLIS 
	,	pt.NPOLIS
	,	pt.DOCTYPE
	,	pt.DOCSER
	,	pt.DOCNUM
	,	pt.SNILS
	,	pt.TEL
	,	pt.MR
	,	pt.[YEAR]
	,	@GuidPackage as RdsDispPackage -- ID Нового пакета
	,	pt.MonthDISP
	,	pt.W
	,	2 as [TYPE]  --Удаление
	,	pt.STAGE
	,	pt.AmbulanceCard
	,	pt.BranchCode
	,	pt.UCH
	,	pt.PODR
	,	CASE WHEN  STAGE = 0  
			THEN 'ДВ1'
			ELSE 'ОПВ'
		END 

	from AKUZ.T_RDS_DISP_PATIENT pt 
	inner join 
		DelPatient Del
		on Del.RdsDispPatientID = pt.RdsDispPatientID
		and Del.rn = 1;

--================================  Находим ИЗМЕНЕННЫХ   ===========================
--									Выбираем из RDS_DISP_PATIENT для указанного ЛПУ последнее направление на диспансеризацию. 
--									Если его тип - Добавить или Редактировать, то 
--									добавить из #PATIENT в T_RDS_DISP_PATIENT с признаком Редактировать
	;with UpdPatient
	as
	(
		select pt.RdsDispPatientID, row_number() over (partition by pt.ID_PAC order by pk.TimeCreate desc) as rn
		from AKUZ.T_RDS_DISP_PATIENT pt 
		inner join 
			AKUZ.T_RDS_DISP_PACKAGE pk 
			on pk.YearDisp = @Year 
			and pk.RdsdispPackageID = pt.RdsDispPackage
			and pk.CODE_MO =  @LpuMain  
		inner join
			#PATIENT p 
			on p.ID_PAC = pt.ID_PAC
		where pt.[TYPE] in (0,1)		
	)
	insert into AKUZ.T_RDS_DISP_PATIENT(RdsDispPatientID, PatientIDDispDirect, ID_PAC, FAM, IM, OT, DR, VPOLIS, SPOLIS, NPOLIS, DOCTYPE, DOCSER, DOCNUM, SNILS, TEL, MR
	, [YEAR], RdsDispPackage, MonthDISP, W, [TYPE], STAGE, AmbulanceCard, BranchCode, UCH, PODR, V016DispType)
	select 
		NEWID() as RdsDispPatientID
	,	pt.PatientIDDispDirect
	,	pt.ID_PAC
	,	pt.FAM
	,	pt.IM
	,	pt.OT
	,	pt.DR
	,	pt.VPOLIS
	,	pt.SPOLIS
	,	pt.NPOLIS
	,	pt.DOCTYPE
	,	pt.DOCSER
	,	pt.DOCNUM
	,	pt.SNILS
	,	pt.TEL
	,	pt.MR
	,	pt.[YEAR]
	,	@GuidPackage as RdsDispPackage -- ID Нового пакета
	,	pt.MonthDISP
	,	pt.W
	,	1 as [TYPE]  --Редактирование
	,	pt.STAGE
	,	pt.AmbulanceCard
	,	pt.BranchCode
	,	pt.UCH
	,	pt.PODR
	,	CASE WHEN  pt.STAGE = 0  
			THEN 'ДВ1'
			ELSE 'ОПВ'
		END 

	from AKUZ.T_RDS_DISP_PATIENT pt 
	inner join
		UpdPatient upd
		on upd.RdsDispPatientID = pt.RdsDispPatientID
	inner join
		#PATIENT p 
		on p.ID_PAC = pt.ID_PAC
	where pt.[TYPE] in (0,1)
	and
	(
		pt.YEAR <> p.YEAR
		or pt.FAM <> p.FAM
		or pt.IM <> p.IM 	
		or pt.OT <> p.OT 		
		or pt.DR <> p.DR			
		or pt.SPOLIS <> p.SPOLIS
		or pt.NPOLIS <> p.NPOLIS 	
		or pt.DOCTYPE <> p.DOCTYPE 	
		or pt.DOCSER <> p.DOCSER 				
		or pt.DOCNUM <> p.DOCNUM 				
		or pt.SNILS <> p.SNILS 				
		or pt.TEL <> p.TEL		
		or pt.MR <> p.MR				 
		or pt.W <> p.W			
		or pt.MonthDISP <> p.MonthDISP
		or pt.AmbulanceCard <> p.AmbulanceCard	
		or pt.UCH <> p.UCH
		or pt.PODR <> p.PODR
	);

	DROP TABLE #PATIENT;
	DROP TABLE #pol;
	DROP TABLE #reg;
END

-- Out
select Count(*) from AKUZ.T_RDS_DISP_PATIENT dp WHERE  dp.RdsDispPackage = @GuidPackage;		-- Принадлежат своему пакету  
END
