
                
                
            with list as (
                with curDT as (select dbo.tzGetDate() as curDate)
                
            select distinct
                PC.LpuAttachType_id as LpuAttachType_id,
                ES.EvnSection_id as EvnSection_id,
                EPS.EvnPS_id as EvnPS_id,
                EPS.Person_id as Person_id,
                EPS.PersonEvn_id as PersonEvn_id,
                EPS.Server_id as Server_id,
                ED.Lpu_did as Lpu_did,
                ED.MedPersonal_id as MedPersonal_did, -- Направивший врач
                -- MSR.MedPersonal_id as MedPersonal_aid, -- Участковый врач
                ZMPD.MedPersonal_id as MedPersonal_zdid, -- Заведующий отделением направившего врача
                EPS.PrehospType_id as PrehospType_id,
                --Поступил - косяк должна быть дата поступления в отделение!
                --COALESCE(to_char(EPS.EvnPS_setDate, 'DD.MM.YYYY'), '') || ' ' || COALESCE(EPS.EvnPS_setTime, '') as "EvnPS_setDateTime",
                COALESCE(
                    to_char(ES.EvnSection_setDate, 'DD.MM.YYYY') ||  ' ' || COALESCE(ES.EvnSection_setTime::varchar, ''),
                    to_char(EPS.EvnPS_setDate, 'DD.MM.YYYY') ||  ' ' || COALESCE(EPS.EvnPS_setTime::varchar, '')
                ) as EvnPS_setDateTime,
                COALESCE(ES.EvnSection_setDate, EPS.EvnPS_setDate) as EvnPS_setDateSort,
                COALESCE(ES.EvnSection_setTime, EPS.EvnPS_setTime) as EvnPS_setTimeSort,

                RTRIM(COALESCE(PC.Person_Surname, '')) || ' ' || RTRIM(COALESCE(PC.Person_Firname, '')) || ' ' || RTRIM(COALESCE(PC.Person_Secname, '')) as Person_Fio,
                to_char(PC.Person_Birthday, 'DD.MM.YYYY') as Person_Birthday,
                dbo.Age2(PC.Person_BirthDay, (select curDate from curDT)) as Person_Age,
                --ЛПУ, куда госпитализирован - Lpu_id
                EPS.Lpu_id as Lpu_id,
                RTRIM(COALESCE(Lpu.Lpu_Nick, '')) as Lpu_Name,
                RTRIM(COALESCE(LpuDir.Lpu_Nick, '')) as LpuDir_Name,
                -- Отделение (показывать последнее активное отделение)
                RTRIM(COALESCE(lss.LpuSection_Name,ls.LpuSection_Name)) as LpuSections_Name,
                --Тип госпитализации
                RTRIM(COALESCE(PHT.PrehospType_Name, '')) as PrehospType_Name,
                -- Количество дней с момента госпитализации
                DATEDIFF('DAY', ES.EvnSection_setDT, dbo.tzGetDate()) as DaysDiff,

                COALESCE(ES.EvnSection_Count, 0) as EvnSection_Count,

                -- Кем направлен (Если направлен ЛПУ, то выводить LPU_Nick; если другое отделение, то название отделения)
                case
                    when EPS.Lpu_did = '13101856' then RTRIM(MPED.Person_Fin)
                    when EPS.Lpu_did is not null then RTRIM(COALESCE((SELECT Lpu_Nick FROM v_Lpu  WHERE Lpu_id = EPS.Lpu_did), ''))
                    when EPS.Lpu_id != '13101856' and EPS.PrehospDirect_id = 1 then RTRIM(COALESCE((SELECT Lpu_Nick FROM v_Lpu  WHERE Lpu_id = EPS.Lpu_id), ''))
                    when EPS.LpuSection_did is not null then RTRIM(COALESCE((SELECT LpuSection_Name FROM LpuSection  WHERE LpuSection_id = EPS.LpuSection_did), ''))
                    else RTRIM(COALESCE(PHD.PrehospDirect_Name, ''))
                end as PrehospDirect_Name,
                --Кем доставлен
                RTRIM(COALESCE(PHA.PrehospArrive_Name, '')) as PrehospArrive_Name,
                --Направление
                EPS.EvnDirection_id as EvnDirection_id,
                ED.EvnDirection_Num as EvnDirection_Num,
                null as ActualCost,
                null as PlannedCost,
                --окончательный Основной диагноз МКБ+наименование
                d.Diag_FullName as Diag_Name,
                d.Diag_id as Diag_id,
                --Дата(время) выписки (перевода, смерти)
                COALESCE(to_char(EPS.EvnPS_disDate, 'DD.MM.YYYY'), '') || ' ' || COALESCE(EPS.EvnPS_disTime::varchar, '') as EvnPS_disDateTime,
                --Исход госпитализации
                RTRIM(COALESCE(LT.LeaveType_Name, '')) as LeaveType_Name,
                --Результат госпитализации
                --
                -- RTRIM(COALESCE(RD.ResultDesease_Name, '')) as ResultDesease_Name,
                -- ЛПУ прикрепления на дату поступления
                RTRIM(COALESCE(LpuAtt.Lpu_Nick, '')) as LpuAtt_Name,
                -- Участок на дату поступления
                RTRIM(COALESCE(LR.LpuRegion_Name, '')) as LpuRegion_Name,
                case when COALESCE(EvnPS_IsNeglectedCase, 1) = 2 then 'true' else 'false' end as EvnPS_IsNeglectedCase,
                case when COALESCE(EPS.EvnPS_IsPrehospAcceptRefuse, 1) = 2 then 'true' else 'false' end as EvnPS_IsPrehospAcceptRefuse_Name,
                EPS.EvnPS_IsPrehospAcceptRefuse as EvnPS_IsPrehospAcceptRefuse,
                COALESCE(ES.EvnSection_setDT, EPS.EvnPS_setDT) as orderByDT
                -- end select
            FROM
                v_EvnSection ES
                inner join v_PersonCard_all PC  on ES.Person_id = PC.Person_id
                --inner join v_EvnPS EPS  on EPS.EvnPS_id = ES.EvnSection_pid and EPS.Lpu_id = ES.Lpu_id
                INNER JOIN LATERAL (
                    Select EPS.*
                    from v_EvnPS EPS
                    where EPS.EvnPS_id = ES.EvnSection_pid and EPS.Lpu_id = ES.Lpu_id
                    limit 1
                ) EPS ON true
                 --для фильтра по участкам

                left join v_LpuSection LS  on LS.LpuSection_id = EPS.LpuSection_pid --для получения имени приемного отделения
                left join v_PrehospDirect PHD  on PHD.PrehospDirect_id = EPS.PrehospDirect_id --Кем направлен
                left join v_PrehospType PHT  on PHT.PrehospType_id = EPS.PrehospType_id--Тип госпитализации
                left join v_PrehospArrive PHA  on PHA.PrehospArrive_id = EPS.PrehospArrive_id --Кем доставлен
                left join v_Lpu Lpu  on Lpu.Lpu_id = EPS.Lpu_id --ЛПУ куда госпитализирован
                left join v_Lpu LpuAtt  on LpuAtt.Lpu_id = PC.Lpu_id --лпу прикрепления на дату поступления
                left join v_LpuRegion LR  on LR.LpuRegion_id = PC.LpuRegion_id --участок прикрепления на дату поступления
                left join v_LeaveType LT  on LT.LeaveType_id = EPS.LeaveType_id --Исход госпитализации
                left join v_EvnDirection_all ED  on ED.EvnDirection_id = EPS.EvnDirection_id
                left join v_Lpu LpuDir  on LpuDir.Lpu_id = ED.Lpu_did --ЛПУ направления
                left join v_MedPersonal MPED  on MPED.MedPersonal_id = ED.MedPersonal_id and MPED.Lpu_id = ED.Lpu_sid
                left join v_Diag dp  on EPS.Diag_pid = dp.Diag_id
                LEFT JOIN LATERAL (
                    select MSF.MedPersonal_id
                    from dbo.v_MedStaffFact MSF
                        inner join persis.Post P  on P.id = MSF.Post_id
                    where MSF.LpuSection_id = ED.LpuSection_id
                        and (COALESCE(MSF.WorkData_begDate, (select curDate from curDT)) <= (select curDate from curDT))
                        and (COALESCE(MSF.WorkData_endDate, (select curDate from curDT)+interval '1 day') > (select curDate from curDT))
                        and P.code = 6
                    limit 1
                ) ZMPD ON true
                --Последнее активное отделение
                left join v_LpuSection lss  on lss.LpuSection_id = ES.LpuSection_id
                left join v_Diag d  on COALESCE(ES.Diag_id,EPS.Diag_pid) = d.Diag_id
                --данные для расчета предполагаемой стоимости лечения в отделении в котором пациент сейчас находится
                --LpuSectionTariffMes_Tariff последнего активного отделения

            where
                PC.Lpu_id = '13101856' -- фильтр по МО не может быть нуловым
                and PC.LpuAttachType_id in (1,2,3,4)
            and (PC.LpuAttachType_id != 4 or PC.Lpu_id = ES.Lpu_id)
                and COALESCE(ES.LpuSection_id, EPS.LpuSection_pid) is not null
                and PC.PersonCard_begDate <= ES.EvnSection_setDate
                and (PC.PersonCard_endDate is null or PC.PersonCard_endDate > ES.EvnSection_setDate)
                and ES.EvnSection_Index = (ES.EvnSection_Count - 1)
                 and coalesce(d.Diag_Code, '') not in('B18.0','B18.1','B17.1','B18.2','Z11.3') and coalesce(d.Diag_Code, '') not between 'B20.0' and 'B24' and coalesce(d.Diag_Code, '') not between 'A50.0' and 'A64' and coalesce(d.Diag_Code, '') not between 'B16.0' and 'B16.9' and coalesce(d.Diag_Code, '') not between 'F00.0' and 'F99' and coalesce(d.Diag_Code, '') not between 'A15.0' and 'A19.9' and coalesce(EPS.Lpu_id, 0) not in(13101862,13101870)
                 AND es.EvnSection_setDate >= '2022-05-09' AND es.EvnSection_setDate <= '2022-05-15'
        
            )
            
                SELECT 
                t1.LpuAttachType_id as "LpuAttachType_id",
                t1.EvnSection_id as "EvnSection_id",
                t1.EvnPS_id as "EvnPS_id",
                t1.Person_id as "Person_id",
                t1.PersonEvn_id as "PersonEvn_id",
                t1.Server_id as "Server_id",
                t1.Lpu_did as "Lpu_did",
                t1.MedPersonal_did as "MedPersonal_did",
                t1.MedPersonal_zdid as "MedPersonal_zdid",
                t1.PrehospType_id as "PrehospType_id",
                t1.EvnPS_setDateTime as "EvnPS_setDateTime",
                t1.EvnPS_setDateSort as "EvnPS_setDateSort",
                t1.EvnPS_setTimeSort as "EvnPS_setTimeSort",
                t1.Person_Fio as "Person_Fio",
                t1.Person_Birthday as "Person_Birthday",
                t1.Person_Age as "Person_Age",
                t1.Lpu_id as "Lpu_id",
                t1.Lpu_Name as "Lpu_Name",
                t1.LpuDir_Name as "LpuDir_Name",
                t1.LpuSections_Name as "LpuSections_Name",
                t1.PrehospType_Name as "PrehospType_Name",
                t1.DaysDiff as "DaysDiff",
                t1.EvnSection_Count as "EvnSection_Count",
                t1.PrehospDirect_Name as "PrehospDirect_Name",
                t1.PrehospArrive_Name as "PrehospArrive_Name",
                t1.EvnDirection_id as "EvnDirection_id",
                t1.EvnDirection_Num as "EvnDirection_Num",
                t1.ActualCost as "ActualCost",
                t1.PlannedCost as "PlannedCost",
                t1.Diag_Name as "Diag_Name",
                t1.Diag_id as "Diag_id",
                t1.EvnPS_disDateTime as "EvnPS_disDateTime",
                t1.LeaveType_Name as "LeaveType_Name",
                t1.LpuAtt_Name as "LpuAtt_Name",
                t1.LpuRegion_Name as "LpuRegion_Name",
                t1.EvnPS_IsNeglectedCase as "EvnPS_IsNeglectedCase",
                t1.EvnPS_IsPrehospAcceptRefuse_Name as "EvnPS_IsPrehospAcceptRefuse_Name",
                t1.EvnPS_IsPrehospAcceptRefuse as "EvnPS_IsPrehospAcceptRefuse",
                t1.orderByDT as "orderByDT"
                
                FROM
                
                list t1
                 WHERE 
                t1.LpuAttachType_id = (
                    select t2.LpuAttachType_id
                    from list t2 where t1.EvnSection_id = t2.EvnSection_id
                    order by LpuAttachType_id
                    limit 1
                )
                
                
                        ORDER BY
                            
                t1.orderByDT desc
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
