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 t2
    set t2.IsSubjectDN = 0
    FROM ISZL.T_SCHET_SLUCH t0 
        LEFT JOIN ISZL.T_SCHET_ZAP t1 ON t1.SchetZapID = t0.SchetZap
        LEFT JOIN ISZL.T_INSURED t2 ON t2.InsuredID = t1.Insured
        LEFT JOIN ISZL.T_SCHET s on s.SchetID = t1.Schet
    WHERE
        (
            t2.IsSubjectDN is not null and t2.IsSubjectDN = 1
                and
            (NOT t0.DS1 IS NULL OR NOT t0.DS2 IS NULL)
                and 
            s.SchetFile = @FileID
        )

-------------------------------------------------------------------------------------------------------------------
    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
