
            with curDT as (select dbo.tzGetDate() as curDate)
            
            ,lastCVI as (
                select t.CVIRegistry_id from (
                    select cvireg.CVIRegistry_id,
                    row_number() over ( partition by register.Person_id order by Person_id, cvireg.CVIRegistry_setDT desc) as row
                    from dbo.v_CVIRegistry cvireg
                    inner join dbo.v_PersonRegister register on register.PersonRegister_id = cvireg.PersonRegister_id
                    where coalesce(cvireg.CVIRegistry_IsPneumonia, 1) = 1
                ) as t
                where t.row = 1
            )
        
            select
                'ps' as "RecType",
                reg.CVIRegistry_id as "id",
                reg.CVIRegistry_id as "CVIRegistry_id",
                ps.Person_id as "Person_id",
                ps.Person_SurName as "Person_SurName",
                ps.Person_FirName as "Person_FirName",
                ps.Person_SecName as "Person_SecName",
                ps.Person_BirthDay as "Person_BirthDay",
                dbo.Age(ps.Person_BirthDay, (select curDate from curDT)) as "Person_Age",
                reg.CVIRegistry_setDT as "begDT",
                reg.CVIRegistry_disDT as "endDT",
                eps_diag.Diag_id as "Diag_id",
                eps_diag.Diag_Code as "Diag_Code",
                (
                    select string_agg(sd.Diag_Code, ', ')
                    from v_EvnDiagPS
                    left join dbo.v_Diag sd on sd.Diag_id = v_EvnDiagPS.Diag_id
                    where v_EvnDiagPS.EvnDiagPS_rid = reg.Evn_id and v_EvnDiagPS.DiagSetClass_id <> 1
                ) as "SopDiag",
                'В стационаре' as "TreatmentPlace",
                eps_lpu.Lpu_Nick as "Lpu_Nick",
                case
                    when eps_erp.EvnReanimatPeriod_id is null then 'Легкая'
                    when (erc.ReanimConditionType_id = 7 or erc.ReanimConditionType_id is null) then 'Средняя'
                    else 'Тяжелая'
                end as "Heft",
                case
                    when eps_nutrition.EvnReanimatAction_id is not null then 'Зонд'
                    else 'Самостоятельно'
                end as "Nutrition",
                case
                    when eps_ventilation.EvnReanimatAction_setDate is not null then cast(datediff('hour', eps_ventilation.EvnReanimatAction_setDT, getdate()) as varchar)
                    else 'Нет'
                end as "Ventilation",
                eup.EvnDirection_id as "EvnDirection_id",
                ut.UslugaTest_ResultValue as "UslugaTest_ResultValue",
                eps_lt.LeaveType_Name as "Result",
                pr.PersonRegister_setDate as "setDT",
                pr.PersonRegister_disDate as "disDT"
            from dbo.v_CVIRegistry reg
            inner join dbo.PersonRegister pr on pr.PersonRegister_id = reg.PersonRegister_id
            inner join dbo.v_PersonState ps on ps.Person_id = pr.Person_id
            inner join dbo.v_EvnPS eps on eps.EvnPS_id = reg.Evn_id
            left join dbo.v_PersonCardState pc on pc.Person_id = ps.Person_id and pc.LpuAttachType_id = 1
            left join dbo.v_EvnSection section on section.EvnSection_pid = eps.EvnPS_id and  section.EvnSection_Index = section.EvnSection_Count - 1
            left join dbo.v_LeaveType eps_lt on eps_lt.LeaveType_id = section.LeaveType_id
            left join dbo.v_Diag eps_diag on eps_diag.Diag_id = section.Diag_id
            left join dbo.v_Lpu_all eps_lpu on eps_lpu.Lpu_id = section.Lpu_id
            left join dbo.v_EvnReanimatPeriod eps_erp on eps_erp.EvnReanimatPeriod_pid = section.EvnSection_id and eps_erp.EvnReanimatPeriod_disDT is null
            left join dbo.v_EvnReanimatCondition erc on erc.EvnReanimatCondition_pid = eps_erp.EvnReanimatPeriod_id and erc.EvnReanimatCondition_Index = erc.EvnReanimatCondition_Count - 1
            left join dbo.v_EvnReanimatAction eps_nutrition on eps_nutrition.EvnReanimatAction_pid = eps_erp.EvnReanimatPeriod_id and eps_nutrition.ReanimatActionType_id = 3 and eps_nutrition.EvnReanimatAction_disDT is null and eps_nutrition.NutritiousType_id <> 4
            left join dbo.v_EvnReanimatAction eps_ventilation on eps_ventilation.EvnReanimatAction_pid = eps_erp.EvnReanimatPeriod_id and eps_ventilation.ReanimatActionType_id = 1 and eps_ventilation.EvnReanimatAction_disDT is null
            left join lateral (
                select EUP.*
                from dbo.v_EvnUslugaPar EUP
                inner join dbo.v_UslugaComplex UC on UC.UslugaComplex_id = EUP.UslugaComplex_id
                where EUP.Person_id = ps.Person_id
                and UC.UslugaComplex_Code in ('A26.05.066.002', 'A26.08.027.001', 'A26.08.027.001.1', 'A26.08.027.001.2', 'A26.08.046.002', 'A26.09.044.002', 'A26.09.060.002', 'A26.08.027.004', 'A26.08.027.004.001', 'A26.08.027.004.002', 'A26.08.027.005', 'A26.08.027.006')
                order by EUP.EvnUslugaPar_setDT desc
                limit 1
            ) eup on true
            left join lateral (
                select utest.UslugaTest_ResultValue
                from dbo.v_UslugaTest utest
                inner join dbo.v_EvnLabSample els on els.EvnLabSample_id = utest.EvnLabSample_id
                where utest.UslugaTest_ResultValue is not null
                and els.EvnLabSample_id = eup.EvnLabSample_id
                order by utest.UslugaTest_setDT desc
                limit 1
            ) ut on true
            
            
            where (1=1) and not exists (
                    select * from dbo.v_DeathSvid ds
                    where Person_id = pr.Person_id
                        and coalesce(ds.DeathSvid_IsBad, 1) = 1
                        and ds.DeathSvid_IsActual = 2
                    limit 1
                ) and coalesce(reg.CVIRegistry_isPneumonia, 1) = 1 and eps_lt.LeaveType_SysNick in ('leave', 'dsleave', 'ksleave') and exists (select * from lastCVI where lastCVI.CVIRegistry_id = reg.CVIRegistry_id limit 1) and ps.Person_SurName ilike 'тест' || '%' and ps.Person_FirName ilike 'тест' || '%'
        
