
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DBO].[sp_CreateDispDirectList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [DBO].[sp_CreateDispDirectList]
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[RDS].[sp_CreateDispDirectList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [RDS].[sp_CreateDispDirectList]
GO
-- =============================================
-- Description: Создать список по диспансеризации за текущий year(getdate()) год
-- =============================================
CREATE PROCEDURE [RDS].[sp_CreateDispDirectList]
(
    @cnt INTEGER OUTPUT
)
as
begin
	delete info from [RDS].T_DISP_DIRECT_INFO info
	join [RDS].T_DISP_DIRECT dd on dd.[DispDirectID] = info.[DispDirect]
	where dd.[YEAR] = year(getdate())

	delete inter from [RDS].T_DISP_DIRECT_INTERVIEW inter
	join [RDS].T_DISP_DIRECT dd on dd.[DispDirectID] = inter.[DispDirect]
	where dd.[YEAR] = year(getdate())

	delete stat from [RDS].T_DISP_DIRECT_STATUS stat
	join [RDS].T_DISP_DIRECT dd on dd.[DispDirectID] = stat.[DispDirect]
	where dd.[YEAR] = year(getdate())

	delete from [RDS].T_DISP_DIRECT
	where [YEAR] = year(getdate())

	set @cnt = (select count(*) from [RDS].T_DISP_DIRECT)
	--select @cnt
	 INSERT INTO [RDS].T_DISP_DIRECT
	 (
		 [DispDirectID]
		 ,[ID_PAC]
		 ,[FAM]
		 ,[IM]
		 ,[OT]
		 ,[W]
		 ,[DR]
		 ,[VPOLIS]
		 ,[SPOLIS]
		 ,[NPOLIS]
		 ,[DOCTYPE]
		 ,[DOCSER]
		 ,[DOCNUM]
		 --,[SNILS]
		 ,[COMENTP]
		 --,[TEL]
		 ,[MR]
		 ,[YEAR]
		 --,[Quarter]
		 --,[Stage]
		 --,[Patient]
		 ,[CODE_SMO]
		 ,[CODE_MO]
		 ,[SNILS_P]
		 ,[SNILS_D]
	 )
	 select 
	 newid()
	 , p.PatientID
	 , p.LastName
	 , p.FirstName
	 , p.FatherName
	 , p.Sex
	 , p.Birthday
	 , p.PolicyForm
	 , p.PolisSerial
	 , p.PolisNumber
	 , p.DocType
	 , p.DocSeries
	 , p.DocNumber
	 --, p.SNILS
	 , null
	 --, p.Phone
	 , null
	 , year(getdate())--[YEAR]
	 --, DATEPART(QUARTER, p.Birthday)--[Quarter]
	 --, 0
	 --, p.PatientID 
	 , p.F002Smo
	 , p.LpuMain
	 , p.SNILS
	 , p.SNILS_D
	 from [RDS].T_PATIENT p
	 where (year(getdate()) - year(p.Birthday)) % 3 = 0 and (year(getdate()) - year(p.Birthday))>=21 and (year(getdate()) - year(p.Birthday))<=99

	 select @cnt = count(*)-@cnt from [RDS].T_DISP_DIRECT--Возвращаем сколько добавили записей
end