
                select
                     E.EvnPL_id as "Evn_id"
                    ,E.EvnClass_id as "EvnClass_id"
                    ,E.Person_id as "Person_id"
                    ,E.PersonEvn_id as "PersonEvn_id"
                    ,E.Server_id as "Server_id"
                    ,PS.Person_SurName as "Person_SurName"
                    ,PS.Person_FirName as "Person_FirName"
                    ,PS.Person_SecName as "Person_SecName"
                    ,E.pmUser_insID as "pmUser_insID"
                    ,'ТАП' as "EvnClass_Name"
                    ,'' as "CertType_Code"
                    ,'' as "CertType_Name"
                    ,E.EvnPL_NumCard as "Evn_NumCard"
                    ,COALESCE(PS.Person_SurName, '') || ' ' || COALESCE(PS.Person_FirName, '') || ' ' || COALESCE(PS.Person_SecName, '') as "Person_FIO"
                    ,CASE WHEN PolT.PolisType_Code = 4 then '' ELSE COALESCE(RTRIM(Polis.Polis_Ser), '') END as "Polis_Ser"
                    ,CASE WHEN PolT.PolisType_Code = 4 then COALESCE(RTRIM(ps.Person_EdNum), '') ELSE COALESCE(RTRIM(Polis.Polis_Num), '') END as "Polis_Num"
                    ,COALESCE(RTRIM(PS.Person_Snils), '') as "Person_Snils"
                    ,to_char(PS.Person_BirthDay, 'DD.MM.YYYY') as "Person_BirthDay"
                    ,COALESCE(D.Diag_FullName, '') as "Diag_Name"
                    ,to_char(E.EvnPL_setDT, 'DD.MM.YYYY') as "Evn_setDate"
                    ,CASE WHEN E.EvnPL_IsFinish = 2 THEN to_char(E.EvnPL_disDT, 'DD.MM.YYYY') ELSE null END as "Evn_disDate"
                    ,RTRIM(LS.LpuSection_Name) as "LpuSection_Name"
                    ,RTRIM(MP.Person_Fio) as "MedPersonal_Fio"
                    ,'' as "PrehospType_Name"
                    ,'' as "EvnPS_HospCount"
                    ,'' as "LeaveType_Name"
                    ,PT.PayType_Name as "PayType_Name"
                    ,LB.LpuBuilding_Name as "LpuBuilding_Name"
                from
                    v_EvnPL E
                    inner join v_PersonState PS  on PS.Person_id = E.Person_id
                    left join lateral (
                    select
                        EvnVizitPL_id,
                        LpuSection_id,
                        MedPersonal_id,
                        PayType_id
                    from
                        dbo.v_EvnVizitPL evpl
                    where
                        evpl.EvnVizitPL_pid = E.EvnPL_id
                    order by
                        EvnVizitPL_setDT desc nulls last
                    limit 1
                ) evpl on true
                    left join v_LpuSection LS  on LS.LpuSection_id = EVPL.LpuSection_id
                    left join v_LpuUnit LU  on LS.LpuUnit_id = LU.LpuUnit_id
                    left join v_LpuBuilding LB  on LU.LpuBuilding_id = LB.LpuBuilding_id
                    left join v_Diag D  on D.Diag_id = E.Diag_id
                    left join v_PersonPolis Polis  on Polis.Polis_id = PS.Polis_id
                    left join v_PolisType PolT  on PolT.PolisType_id = Polis.PolisType_id
                    left join PayType PT  on PT.PayType_id = EVPL.PayType_id
                    LEFT JOIN LATERAL (
                        select Person_Fio
                        from v_MedPersonal
                        where MedPersonal_id = EVPL.MedPersonal_id
                        LIMIT 1
                    ) MP on true
                where
                    (1 = 1) and E.Lpu_id = '500801000000004'
                     and E.EvnPL_setDt >= CAST('2022-10-01' as date) and E.EvnPL_setDt <= CAST('2023-07-24' as date) + INTERVAL '1 day'
             union all 
                select
                     E.EvnPS_id as "Evn_id"
                    ,E.EvnClass_id as "EvnClass_id"
                    ,E.Person_id as "Person_id"
                    ,E.PersonEvn_id as "PersonEvn_id"
                    ,E.Server_id as "Server_id"
                    ,PS.Person_SurName as "Person_SurName"
                    ,PS.Person_FirName as "Person_FirName"
                    ,PS.Person_SecName as "Person_SecName"
                    ,E.pmUser_insID as "pmUser_insID"
                    ,'КВС' as "EvnClass_Name"
                    ,'' as "CertType_Code"
                    ,'' as "CertType_Name"
                    ,E.EvnPS_NumCard as "Evn_NumCard"
                    ,COALESCE(PS.Person_SurName, '') || ' ' || COALESCE(PS.Person_FirName, '') || ' ' || COALESCE(PS.Person_SecName, '') as "Person_FIO"
                    ,CASE WHEN PolT.PolisType_Code = 4 then '' ELSE COALESCE(RTRIM(Polis.Polis_Ser), '') END as "Polis_Ser"
                    ,CASE WHEN PolT.PolisType_Code = 4 then COALESCE(RTRIM(ps.Person_EdNum), '') ELSE COALESCE(RTRIM(Polis.Polis_Num), '') END as "Polis_Num"
                    ,COALESCE(RTRIM(PS.Person_Snils), '') as "Person_Snils"
                    ,to_char(PS.Person_BirthDay, 'DD.MM.YYYY') as "Person_BirthDay"
                    ,COALESCE(D.Diag_FullName, '') as "Diag_Name"
                    ,to_char(E.EvnPS_setDT, 'DD.MM.YYYY') as "Evn_setDate"
                    ,to_char(COALESCE(E.EvnPS_disDT, E.EvnPS_OutcomeDT), 'DD.MM.YYYY') as "Evn_disDate"
                    ,RTRIM(LS.LpuSection_Name) as "LpuSection_Name"
                    ,RTRIM(MP.Person_Fio) as "MedPersonal_Fio"
                    ,PT.PrehospType_Name as "PrehospType_Name"
                    ,COALESCE(CAST(E.EvnPS_HospCount as varchar), '') as "EvnPS_HospCount"
                    ,LT.LeaveType_Name as "LeaveType_Name"
                    ,PayT.PayType_Name as "PayType_Name"
                    ,LB.LpuBuilding_Name as "LpuBuilding_Name"
                from
                    v_EvnPS E
                    inner join  v_PersonState PS on PS.Person_id = E.Person_id
                    left join v_EvnSection ES on ES.EvnSection_id = E.EvnSection_lastid
                    left join v_LpuSection LS  on LS.LpuSection_id = ES.LpuSection_id
                    left join v_LpuUnit LU  on LS.LpuUnit_id = LU.LpuUnit_id
                    left join v_LpuBuilding LB  on LU.LpuBuilding_id = LB.LpuBuilding_id
                    left join v_Diag D  on D.Diag_id = E.Diag_id
                    left join v_PayType PayT  on PayT.PayType_id = COALESCE(ES.PayType_id, E.PayType_id)
                    left join v_PersonPolis Polis  on Polis.Polis_id = PS.Polis_id
                    left join v_PolisType PolT  on PolT.PolisType_id = Polis.PolisType_id
                    left join v_PrehospType PT  on PT.PrehospType_id = E.PrehospType_id
                    left join v_LeaveType LT  on LT.LeaveType_id = ES.LeaveType_id
                    LEFT JOIN LATERAL (
                        select Person_Fio
                        from v_MedPersonal
                        where MedPersonal_id = ES.MedPersonal_id
                        LIMIT 1
                    ) MP on true
                where
                    (1 = 1) and E.Lpu_id = '500801000000004'
                     and E.EvnPS_setDt >= CAST('2022-10-01' as date) and E.EvnPS_setDt <= CAST('2023-07-24' as date) + INTERVAL '1 day'
             union all 
                select
                     E.EvnPLDispCert_id as "Evn_id"
                    ,E.EvnClass_id as "EvnClass_id"
                    ,E.Person_id as "Person_id"
                    ,E.PersonEvn_id as "PersonEvn_id"
                    ,E.Server_id as "Server_id"
                    ,PS.Person_SurName as "Person_SurName"
                    ,PS.Person_FirName as "Person_FirName"
                    ,PS.Person_SecName as "Person_SecName"
                    ,E.pmUser_insID as "pmUser_insID"
                    ,'Справка' as "EvnClass_Name"
                    ,CT.CertType_Code as "CertType_Code"
                    ,CT.CertType_Name as "CertType_Name"
                    ,concat_ws(' ', E.EvnPLDispCert_Seria, E.EvnPLDispCert_Num) as "Evn_NumCard"
                    ,concat_ws(' ', PS.Person_SurName, PS.Person_FirName, PS.Person_SecName) as "Person_FIO"
                    ,CASE WHEN PolT.PolisType_Code = 4 then '' ELSE COALESCE(RTRIM(Polis.Polis_Ser), '') END as "Polis_Ser"
                    ,CASE WHEN PolT.PolisType_Code = 4 then COALESCE(RTRIM(ps.Person_EdNum), '') ELSE COALESCE(RTRIM(Polis.Polis_Num), '') END as "Polis_Num"
                    ,COALESCE(RTRIM(PS.Person_Snils), '') as "Person_Snils"
                    ,to_char(PS.Person_BirthDay, 'DD.MM.YYYY') as "Person_BirthDay"
                    ,'' as "Diag_Name"
                    ,to_char(minEvnUslugaSetDate.EvnUsluga_setDate, 'DD.MM.YYYY') as "Evn_setDate"
                    ,to_char(E.EvnPLDispCert_didDate, 'DD.MM.YYYY') as "Evn_disDate"
                    ,'' as "LpuSection_Name"
                    ,concat_ws(' ', msf.Person_Surname, msf.Person_Firname, msf.Person_Secname) as "MedPersonal_Fio"
                    ,'' as "PrehospType_Name"
                    ,'' as "EvnPS_HospCount"
                    ,'' as "LeaveType_Name"
                    ,PT.PayType_Name as "PayType_Name"
                    ,'' as "LpuBuilding_Name"
                from
                    v_EvnPLDispCert E
                    inner join lateral (
                        select
                            EU.EvnUsluga_setDate
                        from
                            v_DopDispInfoConsent DDIP
                            inner join lateral (
                                select EUDD.EvnUslugaDispDop_setDT as EvnUsluga_setDate
                                from v_EvnUslugaDispDop EUDD
                                where EUDD.DopDispInfoConsent_id = DDIP.DopDispInfoConsent_id
                                and EUDD.EvnUslugaDispDop_setDT is not null
                                union all
                                select EUP.EvnUslugaPar_setDT as EvnUsluga_setDate
                                from v_EvnDirection_all ED
                                inner join v_EvnUslugaPar EUP on EUP.EvnDirection_id = ED.EvnDirection_id
                                where ED.EvnDirection_pid = DDIP.EvnPLDisp_id
                                and ED.DopDispInfoConsent_id = DDIP.DopDispInfoConsent_id
                                and EUP.EvnUslugaPar_setDT is not null
                                limit 1
                            ) EU on true
                        where
                            DDIP.EvnPLDisp_id = E.EvnPLDispCert_id
                            and DDIP.DopDispInfoConsent_isAgree = 2
                            and (DDIP.DopDispInfoConsent_isEarlier is null or DDIP.DopDispInfoConsent_isEarlier = 1)
                        order by
                            EU.EvnUsluga_setDate
                        limit 1
                    ) minEvnUslugaSetDate on true
                    inner join v_CertType CT on CT.CertType_id = E.CertType_id
                    inner join v_PersonState PS  on PS.Person_id = E.Person_id
                    left join v_PersonPolis Polis on Polis.Polis_id = PS.Polis_id
                    left join v_PolisType PolT on PolT.PolisType_id = Polis.PolisType_id
                    left join PayType PT on PT.PayType_id = E.PayType_id
                    left join v_MedstaffFact msf ON msf.MedStaffFact_id = E.MedStaffFact_id
                where
                    (1 = 1) and E.Lpu_id = '500801000000004'
                     and CT.CertType_Code in ('8', '45', '51', '73') and minEvnUslugaSetDate.EvnUsluga_setDate >= cast('2022-10-01' as date) and cast(minEvnUslugaSetDate.EvnUsluga_setDate as date) <= cast('2023-07-24' as date)
            
            order by
                 "Evn_setDate"
                ,"Evn_disDate"
                ,"Person_SurName"
                ,"Person_FirName"
                ,"Person_SecName"
                ,"Person_BirthDay"
        
