SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SchetSearch_PM_DN_facts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SchetSearch_PM_DN_facts]
GO
-- Author:      Vitacore (с)
-- Create date: 
-- Description:  поиск фактов пм и дн в загруженном счете
-- =============================================
CREATE  PROCEDURE [dbo].[sp_SchetSearch_PM_DN_facts]
    @FileID uniqueidentifier
AS
BEGIN
	select 
	  newid() as DnFactID,
      --null as DnItem,
      ss.DATE_1 as DATE_1,
      ss.DATE_2 as DATE_2,
      ss.RSLT as V009Result,
      ss.Mkb as DS_MkbID,
      ssa.MO as MO,
      getDate() as CreationDate,
	  ss.SchetSluchID as SchetSluchID,
	  sz.Insured as Insured
	into #DN
	from ISZL.T_SCHET_SLUCH ss
	left join ISZL.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
	join ISZL.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
	join ISZL.T_SCHET s on s.SchetID = sz.Schet
	where s.SchetFile = @FileID and 
		(ss.DN is not null and ss.P_CEL = '1.3')--1.3	Диспансерное наблюдение	01.01.2018

	alter table #DN
	add DnItem uniqueidentifier

	update dn
	set dn.DnItem = d.DnItemId
	from #DN dn
	cross apply(
		select top 1 di.DnItemId 
		from ISZL.T_DN_ITEM di
		where di.Insured = dn.Insured 
			and di.DS_DISP = dn.DS_MkbID 
			and di.MO_IN = dn.MO
	) d
	where dn.Insured is not null	

	insert into ISZL.T_DN_FACT
	([DnFactID],[DnItem],[DATE_1],[DATE_2],[V009Result],[DS],[MO],[CreationDate],SchetSluch, Insured)
	select [DnFactID],[DnItem],[DATE_1],[DATE_2],[V009Result],[DS_MkbID],[MO],[CreationDate],SchetSluchID, Insured
	from #DN

	update t0
	set t0.IsSubjectDN = case when ( EXISTS (SELECT *
										FROM ISZL.T_SCHET_SLUCH t4 
											left join iszl.T_MKB mkb1 on mkb1.MCBCode = t4.DS1
											left join iszl.t_DN_PERIODICITY dnp1 on dnp1.Mkb = mkb1.MkbID and dnp1.Periodicity is not null
											left join iszl.T_MKB mkb2 on mkb2.MCBCode = t4.DS2
											left join iszl.t_DN_PERIODICITY dnp2 on dnp2.Mkb = mkb2.MkbID and dnp2.Periodicity is not null
										WHERE t3.SchetSluchAccomplishedID=t4.SchetSluchAccomplished 
													AND (dnp1.ID is not null OR dnp2.ID is not null) 
													AND  NOT EXISTS (SELECT *
													FROM ISZL.T_DN_ITEM t62 
														LEFT JOIN ISZL.T_MKB t63 ON t63.MkbID = t62.DS_DISP
													WHERE (t63.MCBCode=t4.DS1 OR t63.MCBCode=t4.DS2) AND t62.Insured=t0.InsuredID)
										))
			then 1 else 0 end
	FROM ISZL.T_INSURED t0 
	join ISZL.T_SCHET_ZAP sz on sz.Insured = t0.InsuredID
	JOIN ISZL.T_SCHET_SLUCH_ACCOMPLISHED t3 ON t3.SchetZap = sz.SchetZapID
	join ISZL.T_SCHET s on s.SchetID = sz.Schet
	WHERE s.SchetFile = @FileID AND t0.DateDeath IS NULL


-------------------------------------------------------------------------------------------------------------------
	select newid() as DispDirectFactID,
		ss.DATE_1 as DateBegin,
		ss.DATE_2 as DateEnd,
		sz.FAM as FAM,
		sz.IM as IM,
		sz.OT as OT,
		ssa.MO as LpuMain,
		ssa.RSLT_D as RSLT_D,
		--null as DispDirect,
		sz.DR,
		isnull(sz.ENP,ins.ENP) as ENP,
		ssa.MO as CODE_MO,
		isnull(ss.ISHOD,ssa.ISHOD) as ISHOD,
		s.NSCHET as NSCHET,
		s.YEAR as YEAR,
		s.MONTH as MONTH,
		v016.V016ID as V016Dispt,
		null as DispDirectFactFile,
		null as Comments,
		case when ss.DN = 1 or ss.DN = 2 then 1 else 0 end as PR_D_N,
		ss.DS1 as DS1,
		sz.Insured as Insured,
		getDate() as CreationDate,
		ss.SchetSluchID as SchetSluchID,
		--for fact
		sz.ID_PAC as ID_PAC,
		sz.W as Sex,
		sz.VPOLIS,
		sz.SPOLIS,
		sz.NPOLIS,
		sz.SNILS as SNILS_P,
		sz.F002smo as SMOCOD,
		sz.InsuredPolicyRecord as InsuredPolicyRecordID,
		ssa.P_OTK,
		ss.NHISTORY,
		ss.DSONK as DS_ONK,
		sz.SNILS,
		null as DS1_PR
	into #PM
	from ISZL.T_SCHET_SLUCH ss
	left join ISZL.T_SCHET_SLUCH_ACCOMPLISHED ssa on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
	join ISZL.T_SCHET_ZAP sz on sz.SchetZapID = ss.SchetZap
	left join iszl.T_INSURED ins on ins.InsuredID = sz.Insured
	join ISZL.T_SCHET s on s.SchetID = sz.Schet
	join ISZL.T_V016_DISPT v016 on v016.IDDT = s.DISP
	where s.SchetFile = @FileID  
		--and (ss.DN is not null or ss.P_CEL = '2.2')--1.3	Диспансерное наблюдение	01.01.2018
		
	alter table #PM
	add DispDirect uniqueidentifier

	select naz.DestinationType as DestinationType,
		pm.DispDirectFactID as DispDirectFactID
	into #NAZ
	from ISZL.T_SCHET_SLUCH_DEST naz
	join #PM pm on pm.SchetSluchID = naz.OmsSchetSluch
	
	insert into #NAZ
	(DestinationType,DispDirectFactID)
	select naz.NAZR,
			pm.DispDirectFactID
	from ISZL.T_SCHET_SLUCH_NAZ naz
	join #PM pm on pm.SchetSluchID = naz.SchetSluch

	update pm
	set pm.DispDirect = d.DispDirectID
	from #PM pm
	cross apply(
		select top 1 dd.DispDirectID 
		from ISZL.T_DISP_DIRECT dd 
		left join ISZL.T_DISP_DIRECT_FACT ddf on ddf.DispDirect = dd.DispDirectID
		where dd.Insured = pm.Insured 
			and dd.V016Dispt = pm.V016Dispt
			and dd.YEAR = YEAR(pm.DateBegin)
			and ddf.DispDirectFactID is null
			and dd.type_ = (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT')
			and not exists (select 1 from iszl.T_DISP_DIRECT_FACT t100 where t100.DispDirect = dd.DispDirectID)
	) d
	where pm.Insured is not null	

	insert into ISZL.T_DISP_DIRECT_FACT
	([DispDirectFactID],[DateBegin],[DateEnd],[FAM],[IM],[OT],[RSLT_D],[DispDirect],[DR]
		,[ENP],[CODE_MO],[ISHOD],[NSCHET],[YEAR],[MONTH],[V016Dispt],[DispDirectFactFile],[Comments],[PR_D_N]
		,[DS1],[Insured],[CreationDate], SchetSluch
		,VPOLIS, SPOLIS, NPOLIS, SNILS, P_OTK, NHISTORY, DS1_PR, DS_ONK
		)
	select [DispDirectFactID],[DateBegin],[DateEnd],[FAM],[IM],[OT],[RSLT_D],[DispDirect],[DR]
		,[ENP],[CODE_MO],[ISHOD],[NSCHET],[YEAR],[MONTH],[V016Dispt],null,null,[PR_D_N]
		,[DS1],[Insured],[CreationDate],SchetSluchID
		,VPOLIS, SPOLIS, NPOLIS, SNILS, P_OTK, NHISTORY, DS1_PR, DS_ONK
	from #PM

	---------------------------------------------
	---Создаём автоматом направления на 2 этап--- 
	---------------------------------------------
	INSERT INTO iszl.T_DISP_DIRECT
	(
		DispDirectID, ID_PAC,FAM,IM,OT,Sex,DR,ENP
		,VPOLIS,SPOLIS,NPOLIS
		,SNILS_P,[YEAR],V016Dispt
		,MONTH_Q
		,Smo,Mo
		,Insured,InsuredPolicyRecord
		,CreationDate
		,SchetSluch
		--,DOCSER
		--,DOCNUM
		--,COMENTP
		--,MR
		--,F011DocumentType
		--,DispDirect
		--,PODR
		--,UCH
		--,DispDirectFactFile
		--,ID
		--,SNILS_D
		--,Selection
	)
	select newid(), p.[ID_PAC],FAM,IM,OT,Sex,DR,ENP
			,VPOLIS,SPOLIS,NPOLIS
			,p.SNILS_P, case when month(p.[DateEnd]) = 12 then p.[YEAR]+1 else p.[YEAR] end ,v016.V016ID
			,case when month(p.[DateEnd]) = 12 then 1 else month(p.[DateEnd]) end
  			,orgsmo.OrganizationID, orgmo.OrganizationID
			,p.Insured
			,p.[InsuredPolicyRecordID]
			,getdate()
			,SchetSluchID
	from #PM p
		left join [ISZL].T_ORGANIZATION orgsmo on orgsmo.Code = p.SMOCOD
		left join [ISZL].T_ORGANIZATION orgmo on orgmo.Code = p.[CODE_MO]
		left join [ISZL].T_V016_DISPT v016 on v016.IDDT = 'ДВ2' and v016.DATEEND is null
	where [RSLT_D] = 6 or [RSLT_D] = 11 or
				[RSLT_D] = 12 or [RSLT_D] = 13 or
				[RSLT_D] = 14 or [RSLT_D] = 15 or
				[RSLT_D] = 17 or [RSLT_D] = 18 or [RSLT_D] = 19
	---------------------------------------------
	---------------------------------------------
	---------------------------------------------

	insert into ISZL.T_DISP_DIRECT_FACT_DESTINATION
	(DispDirectFactDestinationID,DestinationType,DispDirectFact)
	select newid(),DestinationType,DispDirectFactID
	from #NAZ

drop table #NAZ
drop table #PM
drop table #DN
END
