
                
            with mv as (
                select
                localtimestamp as dt
            )
            
                
                SELECT 
                -- TOP SL, FIXDATE 18.05.2023, JIRA PROMEDWEB-148004
        
                 ESEC.EvnSection_id as "EvnSection_id"
                ,ESEC.EvnSection_pid as "EvnSection_pid"
                ,EPS.Person_id as "Person_id"
                ,EPS.PersonEvn_id as "PersonEvn_id"
                ,EPS.Server_id as "Server_id"
                ,EPS.PrehospWaifRefuseCause_id as "PrehospWaifRefuseCause_id"
                ,RTRIM(coalesce(EPS.EvnPS_NumCard, '')) as "EvnPS_NumCard"
                ,(
                    select
                        PersonAmbulatCard_Num
                    from v_PersonAmbulatCard PAC
                        left join v_PersonAmbulatCardLocat PACL on PAC.PersonAmbulatCard_id = PACL.PersonAmbulatCard_id
                        left join v_AmbulatCardLpuBuilding ACLB on ACLB.PersonAmbulatCard_id = PAC.PersonAmbulatCard_id
                    where
                        PAC.Person_id = EPS.Person_id
                        and PAC.Lpu_id = '2717'
                        and ESEC.EvnSection_setDate between
                            coalesce(ACLB.AmbulatCardLpuBuilding_begDate, ESEC.EvnSection_setDate)
                            and coalesce(ACLB.AmbulatCardLpuBuilding_endDate, ESEC.EvnSection_setDate)
                        order by
                            PACL.PersonAmbulatCardLocat_begDate desc,
                            PAC.PersonAmbulatCard_id desc
                    limit 1
                ) as "PersonAmbulatCard_Num"
                ,coalesce(LS.LpuSection_Name, '') as "LpuSection_Name"
                ,coalesce(LSW.LpuSectionWard_Name, '') as "LpuSectionWard_Name"
                ,coalesce(Dtmp.Diag_FullName, '') as "Diag_Name"
                ,to_char(ESEC.EvnSection_setDate, 'DD.MM.YYYY') as "EvnSection_setDate"
                ,to_char(ESEC.EvnSection_disDate, 'DD.MM.YYYY') as "EvnSection_disDate"
                ,coalesce(LT.LeaveType_Name, '') as "LeaveType_Name"
                ,PT.PayType_Name as "PayType_Name"
                ,RTRIM(coalesce(MP.Person_Fio, '')) as "MedPersonal_Fio"
                ,case when ESEC.EvnSection_disDate is not null
                    then
                        case
                            when LUT.LpuUnitType_Code = 2 and date_part('day', ESEC.EvnSection_disDate - ESEC.EvnSection_setDate) + 1 > 1
                            then date_part('day', ESEC.EvnSection_disDate - ESEC.EvnSection_setDate)
                            else date_part('day', ESEC.EvnSection_disDate - ESEC.EvnSection_setDate) + 1
                        end - coalesce(ESEC.EvnSection_Absence, 0)
                    else null
                  end as "EvnSection_KoikoDni"
                 ,MES.Mes_Code as "Mes_Code"
                 ,
                case when pls.Polis_endDate is not null and pls.Polis_endDate <= (select dt from mv)
then 'orange'
else case when ps.PersonCloseCause_id = 2 and ps.Person_closeDT is not null
        then 'red'
        else case when ps.Server_pid = 0
                then 'true'
                else 'false'
        end
end
                end as "Person_IsBDZ",
            
                 CASE WHEN ESEC.EvnSection_id = EPS.EvnSection_lastid THEN 1 ELSE 0 END as "EvnSection_isLast"
                 ,coalesce(ksgkpg.Mes_Code, '')||' '||coalesce(ksgkpg.Mes_Name, '') as "EvnSection_KSG"
                 ,kpg.Mes_Code as "EvnSection_KPG"
                ,case when ESEC.EvnSection_IsAdultEscort = 2 then 'Да' else 'Нет' end as "EvnSection_IsAdultEscort"
                ,coalesce(ksgkpg.Mes_Code, '')||' '||coalesce(ksgkpg.Mes_Name, '') as "EvnSection_KSGKPG"
                ,qa.isQualityAnketa as "isQualityAnketa"
            
                ,RTRIM(PS.Person_SurName) as "Person_Surname"
                ,RTRIM(PS.Person_FirName) as "Person_Firname"
                ,RTRIM(PS.Person_SecName) as "Person_Secname"
                ,case
                    when (PS.Person_SurName is null and PS.Person_FirName is null and PS.Person_SecName is null) then 'Неизвестный'
                    else
                        coalesce(upper(left(PS.Person_SurName, 1)) || lower(overlay(PS.Person_SurName placing '' from 1 for 1)),'') ||' '||
                        coalesce(upper(left(PS.Person_FirName, 1)) || lower(overlay(PS.Person_FirName placing '' from 1 for 1)),'') ||' '||
                        coalesce(upper(left(PS.Person_SecName, 1)) || lower(overlay(PS.Person_SecName placing '' from 1 for 1)),'')
                end
                as "Person_Fio"
                ,PS.Sex_id as "Sex_id"
                ,to_char(PS.Person_Birthday, 'DD.MM.YYYY') as "Person_Birthday"
                ,
            case
                
                when datediff('day', cast(PS.Person_Birthday as date), localtimestamp) <= 31
                    then '' || datediff('day', cast(PS.Person_Birthday as date), localtimestamp) ||
                    case
                        when
                            datediff('day', cast(PS.Person_Birthday as date), localtimestamp) >= 2
                            and datediff('day', cast(PS.Person_Birthday as date), localtimestamp) <= 4
                            or
                            datediff('day', cast(PS.Person_Birthday as date), localtimestamp) >= 22
                            and datediff('day', cast(PS.Person_Birthday as date), localtimestamp) + 1 <= 24
                        then ' дня'
                        when datediff('day', cast(PS.Person_Birthday as date), localtimestamp) in (1, 21, 31)
                        then ' день'
                        else ' дней'
                    end
                when datediff('day', cast(PS.Person_Birthday as date), localtimestamp) + 1 > 31 and dbo.Age_newborn(cast(PS.Person_Birthday as date), localtimestamp) < 12
                    then '' || dbo.Age_newborn(cast(PS.Person_Birthday as date), localtimestamp)::varchar || ' мес.'

                when dbo.Age_newborn(cast(PS.Person_Birthday as date), localtimestamp) >= 12 and dbo.Age_newborn(cast(PS.Person_Birthday as date), localtimestamp) < 36
                    then '' || dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp)::varchar ||
                        case
                            when dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp) = 2 then ' года '
                            else ' год '
                        end ||
                        case
                            when (dbo.Age_newborn(cast(PS.Person_Birthday as date), localtimestamp) - (dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp) * 12)) < 1 then ''
                            else (dbo.Age_newborn(cast(PS.Person_Birthday as date), localtimestamp) - (dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp) * 12))::varchar || ' мес.'
                        end

                when dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp) >= 3
                    then '' || dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp)::varchar ||
                        case
                    when
                        right(dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp)::varchar,2)::integer >= 11
                        and right(dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp)::varchar,2)::integer <= 14
                        or right(dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp)::varchar,1)::integer >= 5
                        and right(dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp)::varchar,1)::integer <= 9
                        or right(dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp)::varchar,1)::integer = 0
                    then ' лет'
                    when
                        right(dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp)::varchar,1)::integer >= 2
                        and right(dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp)::varchar,1)::integer <= 4
                    then ' года'
                    when right(dbo.Age2(cast(PS.Person_Birthday as date), localtimestamp)::varchar,1)::integer = 1
                    then ' год'
                end
                    else '' || dbo.Age_newborn(cast(PS.Person_Birthday as date), localtimestamp)::varchar || ' мес.'
            end
            as "Person_age"
        
                ,to_char(PS.Person_DeadDT, 'DD.MM.YYYY') as "Person_deadDT"
                ,case when SES1.ServiceEvnStatus_SysNick in ('sendegis','loadegis') then 'true' else 'false' end as "fedservice_iemk"
            
                FROM
                
         dbo.v_PersonState PS  inner join v_EvnPS EPS on EPS.Person_id = PS.Person_id
                            and EPS.Lpu_id = '2717'
            inner join v_EvnSection as ESEC on ESEC.EvnSection_pid = EPS.EvnPS_id and ESEC.Lpu_id = '2717'
                and COALESCE(ESEC.EvnSection_IsPriem, 1) = 1
            inner join LpuSection LS on LS.LpuSection_id = ESEC.LpuSection_id
            inner join v_LpuUnit LU on LU.LpuUnit_id = LS.LpuUnit_id
            inner join LpuUnitType LUT on LUT.LpuUnitType_id = LU.LpuUnitType_id
            left join v_EvnCostPrint ecp on ecp.Evn_id = EPS.EvnPS_id
            left join v_Diag Dtmp on Dtmp.Diag_id = ESEC.Diag_id
            left join LpuSectionWard LSW on LSW.LpuSectionWard_id = ESEC.LpuSectionWard_id
            left join v_Polis pls on pls.Polis_id = ps.Polis_id
            left join PayType as PT on PT.PayType_id = ESEC.PayType_id
            left join lateral(
                select Person_Fio
                from v_MedPersonal
                where MedPersonal_id = ESEC.MedPersonal_id
                limit 1
            ) as MP on true
            left join v_MesOld as MES on MES.Mes_id = ESEC.Mes_id
            left join LeaveType LT on LT.LeaveType_id = ESEC.LeaveType_id
            left join v_MesTariff spmt on ESEC.MesTariff_id = spmt.MesTariff_id
            left join v_MesOld as sksg on sksg.Mes_id = ESEC.Mes_sid
            left join v_MesOld as ksg on ksg.Mes_id = case
                when spmt.Mes_id in (ESEC.Mes_sid, ESEC.Mes_tid) then spmt.Mes_id
                else coalesce(ESEC.Mes_sid, ESEC.Mes_tid)
            end
            left join v_MesOld as ksgkpg on spmt.Mes_id = ksgkpg.Mes_id
            left join v_MesOld as kpg on kpg.Mes_id = ESEC.Mes_kid
            left join lateral (
                select count(QualityReview_id) as isQualityAnketa
                from v_QualityReview
                where Evn_id = EPS.EvnPS_id limit 1
            ) qa on true
        
                left join lateral(
                    select ESS.EvnSection_id, ESSDiag.Diag_Code
                    from
                        v_EvnSection ESS
                        inner join v_Diag ESSDiag on ESSDiag.Diag_id = ESS.Diag_id
                    where ESS.EvnSection_pid = EPS.EvnPS_id
                        and ESS.EvnSection_id = EPS.EvnSection_lastid
                        and ESS.Lpu_id = '2717'
                    limit 1
                ) adf on ((adf.Diag_Code in('R75','Z20.6','Z83.0','Z04.0','Z09.3','Z13.3','Z50.2','Z50.3','Z55.3','Z55.4','Z60.0','Z61.0','Z61.2','Z61.3','Z61.7','Z62.6','Z64.3','Z63.0','Z72.6','Z81.1','Z81.3','Z81.8')) or (adf.Diag_Code between 'B20.0' and 'B24') or (adf.Diag_Code between 'F00.0' and 'F09') or (adf.Diag_Code between 'F20.0' and 'F99') or (adf.Diag_Code between 'F10.0' and 'F19.9') or (adf.Diag_Code between 'A50.0' and 'A64') or (adf.Diag_Code between 'Z62.0' and 'Z62.4') or (adf.Diag_Code between 'Z63.2' and 'Z63.5') or (adf.Diag_Code between 'Z72.0' and 'Z72.2') or (adf.Diag_Code between 'Z73.0' and 'Z73.1'))
            
            left join lateral(
                select
                    PersonEvn_id,
                    Server_id
                from v_PersonState
                where Person_id = PS.Person_id
                limit 1
            ) as CPS on true
        
            left join lateral(
                select ServiceEvnStatus_id
                from v_ServiceEvnHist
                where Evn_id = EPS.EvnPS_id
                  and ServiceEvnList_id = 1
                order by ServiceEvnHist_id desc
                limit 1
            ) as SEH1 on true
            left join v_ServiceEvnStatus SES1 on SES1.ServiceEvnStatus_id = SEH1.ServiceEvnStatus_id
        
                         WHERE 
                        (1 = 1) and adf.EvnSection_id is null
                        and PS.PersonEvn_id = coalesce(ESEC.PersonEvn_id, CPS.PersonEvn_id)
                        and PS.Server_id = coalesce(ESEC.Server_id, CPS.Server_id)
                    
                        and exists(
                            select 1
                            from v_Person_all PStmp
                            where PStmp.Person_id = PS.Person_id
                             and lower(PStmp.Person_SurName) like lower('МИТРОФАНОВА')||'%'
                    )  and coalesce(Dtmp.Diag_Code, '') not in('R75','Z20.6','Z83.0','Z04.0','Z09.3','Z13.3','Z50.2','Z50.3','Z55.3','Z55.4','Z60.0','Z61.0','Z61.2','Z61.3','Z61.7','Z62.6','Z64.3','Z63.0','Z72.6','Z81.1','Z81.3','Z81.8') and coalesce(Dtmp.Diag_Code, '') not between 'B20.0' and 'B24' and coalesce(Dtmp.Diag_Code, '') not between 'F00.0' and 'F09' and coalesce(Dtmp.Diag_Code, '') not between 'F20.0' and 'F99' and coalesce(Dtmp.Diag_Code, '') not between 'F10.0' and 'F19.9' and coalesce(Dtmp.Diag_Code, '') not between 'A50.0' and 'A64' and coalesce(Dtmp.Diag_Code, '') not between 'Z62.0' and 'Z62.4' and coalesce(Dtmp.Diag_Code, '') not between 'Z63.2' and 'Z63.5' and coalesce(Dtmp.Diag_Code, '') not between 'Z72.0' and 'Z72.2' and coalesce(Dtmp.Diag_Code, '') not between 'Z73.0' and 'Z73.1'
                        
                
                        ORDER BY
                            
                            EPS.EvnPS_id
                            
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
