
                
            with mv as (
                select
                localtimestamp as dt
            )
            
                
                SELECT 
                -- TOP SL, FIXDATE 18.05.2023, JIRA PROMEDWEB-148004
        
            case
                when
                    efr.evnfuncrequest_id != null or
                    ucat.uslugacomplexattributetype_sysnick = 'func' or
                    ep.prescriptiontype_id = 12
                then 1
                else 0
                end as "isFunc",
            case when EUP.Lpu_id = '140101000000010' then 'edit' else 'view' end as "accessType",
            doc.EvnXml_id as "EvnXml_id",
            doc.EvnXml_IsSigned as "EvnXml_IsSigned",
            EUP.EvnUslugaPar_id as "EvnUslugaPar_id",
            EUP.EvnUslugaPar_pid as "EvnUslugaPar_pid",
            EUP.Person_id as "Person_id",
            EUP.PersonEvn_id as "PersonEvn_id",
            EUP.Server_id as "Server_id",
            to_char(coalesce(EUP.EvnUslugaPar_disDate, EUP.EvnUslugaPar_setDate), 'DD.MM.YYYY') as "EvnUslugaPar_setDate",
            coalesce(EUP.EvnUslugaPar_IsSigned, 1) as "EvnUslugaPar_IsSigned",
            RTRIM(LS.LpuSection_Name) as "LpuSection_Name",
            RTRIM(MP.Person_Fio) as "MedPersonal_Fio",
            lpu.Lpu_Name as "Lpu_Name",
            CASE WHEN EUP.UslugaComplex_id IS NULL THEN RTRIM(Usluga.Usluga_Code) ELSE RTRIM(UslugaComplex.UslugaComplex_Code) END as "Usluga_Code",
            CASE WHEN EUP.UslugaComplex_id IS NULL THEN RTRIM(Usluga.Usluga_Name) ELSE RTRIM(UslugaComplex.UslugaComplex_Name) END as "Usluga_Name",
            RTRIM(PT.PayType_Name) as "PayType_Name",
            coalesce(EUP.EvnUslugaPar_Kolvo, 0) as "EvnUslugaPar_Kolvo",
            coalesce(PostMed.PostMed_Name, '') as "PostMed_Name",
            to_char(ecp.EvnCostPrint_setDT, 'DD.MM.YYYY') as "EvnCostPrint_setDT",
            case when ecp.EvnCostPrint_IsNoPrint = 2 then 'Отказ от справки' when ecp.EvnCostPrint_IsNoPrint = 1 then 'Справка выдана' else '' end as "EvnCostPrint_IsNoPrintText",
            coalesce(O2.Org_Nick, L.Lpu_Nick, Org.Org_Nick, L2.Lpu_Nick,  L3.Lpu_Nick) as "Referral_Org_Nick", -- направившая МО
            lpusec.LpuSection_Name as "LSD"
        
                ,RTRIM(PS.Person_SurName) as "Person_Surname"
                ,RTRIM(PS.Person_FirName) as "Person_Firname"
                ,RTRIM(PS.Person_SecName) as "Person_Secname"
                ,to_char(PS.Person_Birthday, 'DD.MM.YYYY') as "Person_Birthday"
                ,dbo.Age2(PS.Person_BirthDay, (select dt from mv))  as "Person_Age"
                ,to_char(PS.Person_deadDT, 'DD.MM.YYYY') as "Person_deadDT"
            
            
                FROM
                
         v_EvnUslugaPar EUP  inner join dbo.v_PersonState PS on EUP.Person_id = PS.Person_id
            left join v_LpuSection lpusec on lpusec.LpuSection_id = EUP.LpuSection_did
            left join v_EvnCostPrint ecp on ecp.Evn_id = EUP.EvnUslugaPar_id
            left join lateral(
                select Person_Fio
                from v_MedPersonal
                where MedPersonal_id = EUP.MedPersonal_id
                limit 1
            ) MP on true
            left join Usluga on Usluga.Usluga_id = EUP.Usluga_id
            left join v_UslugaComplex UslugaComplex on UslugaComplex.UslugaComplex_id = EUP.UslugaComplex_id
            left join v_EvnUslugaPar EvnParent on EvnParent.EvnUslugaPar_id = EUP.EvnUslugaPar_pid
            left join v_PayType PT on PT.PayType_id = EUP.PayType_id
            left join v_MedStaffFact MedStaffFact on MedStaffFact.MedStaffFact_id = EUP.MedStaffFact_id
            left join v_LpuSection LS on LS.LpuSection_id = coalesce(EUP.LpuSection_uid, MedStaffFact.LpuSection_id)
            left join v_Lpu lpu on lpu.Lpu_id = LS.Lpu_id
            left join v_PostMed PostMed on PostMed.PostMed_id = MedStaffFact.Post_id
            left join v_EvnDirection_all ED on EUP.EvnDirection_id = ED.EvnDirection_id
            left join v_Lpu LD on LD.Lpu_id = ED.Lpu_sid
            left join v_Org Org on Org.Org_id = ED.Org_sid
            left join v_Lpu L ON L.Lpu_id = ED.Lpu_sid -- NGS
            left join v_Lpu L2 ON L2.Lpu_id = EUP.Lpu_did -- NGS
            left join v_Lpu L3 on L3.Org_id = EUP.Org_uid
            left join v_Org O2 on O2.Org_id = EUP.Org_did
        
                left join lateral(
                    select EvnXml.EvnXml_id, EvnXml.EvnXml_IsSigned
                    from v_EvnXml  EvnXml
                    where Evn_id = EUP.EvnUslugaPar_id
                    order by EvnXml_insDT desc
                    limit 1
                ) doc on true
            
                    left join v_evnprescr ep on ep.evnprescr_id = eup.evnprescr_id
                    left join v_evnfuncrequest efr on efr.evnfuncrequest_pid = eup.evndirection_id
                    left join lateral(
                        select ucat.uslugacomplexattributetype_sysnick
                        from v_uslugacomplexattribute uca
                            left join v_uslugacomplexattributetype ucat
                                on ucat.uslugacomplexattributetype_id = uca.uslugacomplexattributetype_id
                         where uca.uslugacomplex_id = eup.uslugacomplex_id
                            and ucat.uslugacomplexattributetype_sysnick = 'func'
                    ) as ucat on true
                
                         WHERE 
                        (1 = 1) and (
                EUP.Lpu_id = '140101000000010'
                OR EUP.Lpu_uid = '140101000000010'
            ) and EUP.EvnUslugaPar_IsInReg = 2 and eup.EvnUslugaPar_setDate is not null
                  AND CAST((CASE
                                WHEN EUP.EvnUslugaPar_disDT IS NOT NULL THEN EUP.EvnUslugaPar_disDT
                                ELSE EUP.EvnUslugaPar_setDT END) AS DATE)
                    >= CAST('2022-01-01' AS DATE)
             
                  AND CAST((CASE
                            WHEN EUP.EvnUslugaPar_disDT IS NOT NULL THEN EUP.EvnUslugaPar_disDT
                            ELSE EUP.EvnUslugaPar_setDT END) AS DATE)
                    <= CAST('2022-12-31' AS DATE)
             and EUP.PayType_id = '140101000000001' and UslugaComplex.UslugaCategory_id = '140101000000001' and EvnParent.EvnUslugaPar_id is null
                        
                
                        ORDER BY
                            
                        EUP.EvnUslugaPar_id
                        
                OFFSET 0 ROWS
                FETCH NEXT 100000 ROWS ONLY
                
