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
