DROP FUNCTION if exists sp_SchetSearch_PM_DN_facts(uuid);
CREATE OR REPLACE FUNCTION sp_SchetSearch_PM_DN_facts
(
    "FileID" uuid
)
RETURNS void 
LANGUAGE plpgsql
AS $function$
declare
    _ts timestamp;
    fileid uuid;
begin
    fileid := "FileID";
    _ts := now()::timestamp;
    
    Drop Table If Exists TmpDN;
    Create Temp Table TmpDN
    As
    select
        gen_random_uuid() as DnFactID
    ,    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
    ,    _ts as CreationDate
    ,    ss.SchetSluchID as SchetSluchID
    ,    sz.Insured as Insured
    ,    null::uuid as DnItem
    from ISZL.T_SCHET_SLUCH ss
    left join 
        ISZL.T_SCHET_SLUCH_ACCOMPLISHED ssa 
        on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
    inner join
        ISZL.T_SCHET_ZAP sz
        on sz.SchetZapID = ss.SchetZap
    inner 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        
    
    update TmpDN dn
    set DnItem = 
    (
        select 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
        limit 1
    )
    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 d.DnFactID, d.DnItem, d.DATE_1, d.DATE_2, d.V009Result, d.DS_MkbID, d.MO, d.CreationDate, d.SchetSluchID, d.Insured
    from TmpDN d;

-------------------------------------------------------------------------------------------------------------------
    
    Drop Table If Exists TmpPM;
    Create Temp Table TmpPM
    As
    select
        gen_random_uuid() 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
    ,    sz.DR
    ,    coalesce(sz.ENP, ins.ENP) as ENP
    ,    ssa.MO as CODE_MO
    ,    coalesce(ss.ISHOD, ssa.ISHOD) as ISHOD
    ,    s.NSCHET as NSCHET
    ,    s.YEAR as YEAR
    ,    s.MONTH as MONTH
    ,    v016.V016ID as V016Dispt
    ,    null::uuid as DispDirectFactFile
    ,    null::varchar(4000) 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
    ,    _ts as CreationDate
    ,    ss.SchetSluchID as SchetSluchID
    ,    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
    ,    null::uuid as DispDirect
  , ssa.P_OTK::boolean
  , ss.NHISTORY
  , ss.DSONK::boolean as DS_ONK
  , sz.SNILS
  , null::boolean as DS1_PR
    from ISZL.T_SCHET_SLUCH ss
    left join 
        ISZL.T_SCHET_SLUCH_ACCOMPLISHED ssa
        on ssa.SchetSluchAccomplishedID = ss.SchetSluchAccomplished
    inner join
        ISZL.T_SCHET_ZAP sz 
        on sz.SchetZapID = ss.SchetZap
    left join
        iszl.T_INSURED ins 
        on ins.InsuredID = sz.Insured
    inner join
        ISZL.T_SCHET s
        on s.SchetID = sz.Schet
    inner 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
    
    Drop Table If Exists TmpNAZ;
    Create Temp Table TmpNAZ
    As
    select 
        naz.DestinationType as DestinationType
    ,    pm.DispDirectFactID as DispDirectFactID
    from ISZL.T_SCHET_SLUCH_DEST naz
    inner join
        TmpPM pm
        on pm.SchetSluchID = naz.OmsSchetSluch;
    
    insert into TmpNAZ(DestinationType, DispDirectFactID)
    select naz.NAZR, pm.DispDirectFactID
    from ISZL.T_SCHET_SLUCH_NAZ naz
    inner join
        TmpPM pm
        on pm.SchetSluchID = naz.SchetSluch;

    update TmpPM pm
    set DispDirect = 
    (
        select 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 = extract(year from pm.DateBegin)
        and ddf.DispDirectFactID is null
        and dd.type_ = (select e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT' limit 1)
    and not exists (select 1 from iszl.T_DISP_DIRECT_FACT t100 where t100.DispDirect = dd.DispDirectID)
        limit 1
    )
    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 pm.DispDirectFactID, pm.DateBegin, pm.DateEnd, pm.FAM, pm.IM, pm.OT, pm.RSLT_D, pm.DispDirect, pm.DR
    , pm.ENP, pm.CODE_MO, pm.ISHOD, pm.NSCHET, pm.YEAR, pm.MONTH, pm.V016Dispt, null, null, pm.PR_D_N
    , pm.DS1, pm.Insured, pm.CreationDate, pm.SchetSluchID
  , VPOLIS, SPOLIS, NPOLIS, SNILS, P_OTK, NHISTORY, DS1_PR, DS_ONK
    from TmpPM pm
  where not exists
      (
        select *
        from ISZL.T_DISP_DIRECT_FACT f
        where f.DispDirect = pm.DispDirect
      )
        and
      not exists
      (
        select *
        from TmpPM pm1
        where pm1.DispDirectFactID < pm.DispDirectFactID
                and
            pm1.DispDirect = pm.DispDirect
      );

  update ISZL.T_DISP_DIRECT_FACT f
  set 
    DateBegin = pm.DateBegin, 
    DateEnd = pm.DateEnd, 
    FAM = pm.FAM, 
    IM = pm.IM, 
    OT = pm.OT, 
    RSLT_D = pm.RSLT_D, 
    DR = pm.DR,
    ENP = pm.ENP,
    CODE_MO = pm.CODE_MO,
    ISHOD = pm.ISHOD,
    NSCHET = pm.NSCHET,
    YEAR = pm.YEAR,
    MONTH = pm.MONTH,
    V016Dispt = pm.V016Dispt,
    PR_D_N = pm.PR_D_N,
    DS1 = pm.DS1,
    Insured = pm.Insured,
    CreationDate = pm.CreationDate,
    SchetSluch = pm.SchetSluchID,
    VPOLIS = pm.VPOLIS,
    SPOLIS = pm.SPOLIS,
    NPOLIS = pm.NPOLIS,
    SNILS = pm.SNILS,
    P_OTK = pm.P_OTK,
    NHISTORY = pm.NHISTORY,
    DS1_PR = pm.DS1_PR,
    DS_ONK = pm.DS_ONK
  from TmpPM pm
  where pm.DispDirect = f.DispDirect;

    ---------------------------------------------
    ---Создаём автоматом направления на 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)
    select gen_random_uuid(), p.ID_PAC, p.FAM, p.IM, p.OT, p.Sex, p.DR, p.ENP, p.VPOLIS, p.SPOLIS, p.NPOLIS, p.SNILS_P
    , case when extract(month from p.DateEnd) = 12 then p.YEAR + 1 else p.YEAR end
    , v016.V016ID
    , case when extract(month from p.DateEnd) = 12 then 1 else extract(month from p.DateEnd) end
      , orgsmo.OrganizationID, orgmo.OrganizationID, p.Insured, p.InsuredPolicyRecordID
    , now()::timestamp, p.SchetSluchID
    from TmpPM 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 p.RSLT_D in (6, 11, 12, 13, 14, 15, 17, 18, 19);
    ---------------------------------------------
    ---------------------------------------------
    ---------------------------------------------

    insert into ISZL.T_DISP_DIRECT_FACT_DESTINATION( DispDirectFactDestinationID, DestinationType, DispDirectFact)
    select gen_random_uuid(), n.DestinationType, n.DispDirectFactID
    from TmpNAZ n;

    drop table if exists TmpNAZ;
    drop table if exists TmpPM;
    drop table if exists TmpDN;
end;
$function$;
