
                
                
                SELECT 
            
            msf.MedStaffFact_id as "MedStaffFact_id",
            hv.MedSpecOms_id as "MedSpecOms_id",
            cast(mso.MedSpecOms_Code as varchar)  || '. ' || mso.MedSpecOms_Name as "MedSpecOms_Name",
            hv.HomeVisit_id as "HomeVisit_id",
            hv.Person_id as "Person_id",
            hv.HomeVisit_Num as "HomeVisit_Num",
            to_char(hv.HomeVisit_setDT, 'DD.MM.YYYY') as "HomeVisit_setDate",
            p.PersonEvn_id as "PersonEvn_id",
            p.Server_id as "Server_id",
            p.Person_Surname as "Person_Surname",
            p.Person_Firname as "Person_Firname",
            p.Person_Secname as "Person_Secname",
            to_char(p.Person_BirthDay, 'DD.MM.YYYY') as "Person_Birthday",
            rtrim(hv.Address_Address) as "Address_Address",
            mp.Person_FIO as "MedPersonal_FIO",
            coalesce(lr.LpuRegion_id, lrc.LpuRegion_id) as "LpuRegion_id",
            coalesce(lrc.LpuRegion_Name, '')||' '||coalesce(lrc.LpuRegionType_Name, '') as "LpuRegion_Name",
            lb.LpuBuilding_id as "LpuBuilding_id",
            lb.LpuBuilding_Name as "LpuBuilding_Name",
            case
                when hv.HomeVisitStatus_id is null then '0. Требует подтверждения'
                when hv.HomeVisitStatus_id = 1 and hvsrc.HomeVisitSource_Code = 10  and hv.CmpCallCard_id is not null then '1.	Актив из СМП'
                else cast((1 + hvs.HomeVisitStatus_Code::integer) as varchar)||'. '||hvs.HomeVisitStatus_Name
            end as "HomeVisitStatus_Nameg",
            case
                when hv.HomeVisitStatus_id is null then 'Требует подтверждения'
                when hv.HomeVisitStatus_id = 1 and hvsrc.HomeVisitSource_Code = 10  and hv.CmpCallCard_id is not null then 'Актив из СМП'
                else hvs.HomeVisitStatus_Name
            end as "HomeVisitStatus_Name",
            age2(p.Person_Birthday, getdate()) as "Person_Age",
            hv.HomeVisit_Phone as "HomeVisit_Phone",
            hv.HomeVisitStatus_id as "HomeVisitStatus_id",
            hv.HomeVisitCallType_id as "HomeVisitCallType_id",
            hvwc.HomeVisitWhoCall_Name as "HomeVisitWhoCall_Name",
            hvct.HomeVisitCallType_Name as "HomeVisitCallType_Name",
            HomeVisit_Symptoms as "HomeVisit_Symptoms",
            HomeVisit_Comment as "HomeVisit_Comment",
            HomeVisit_LpuComment as "HomeVisit_LpuComment",
            coalesce(hv.CallProfType_id, 1) as "CallProfType_id",
            coalesce(cpt.CallProfType_Name, 'Терапевтический/педиатрический') as "CallProfType_Name",
            to_char(hv.HomeVisit_setDT, 'HH24:MI') as "HomeVisit_setTime",
            hv.Lpu_id as "Lpu_id",
            hv.HomeVisitSource_id as "HomeVisitSource_id",
            l.Lpu_Nick as "Lpu_Nick",
            ccc.CmpCallCard_id as "CmpCallCard_id",
            case when hvsrc.HomeVisitSource_Code <> 5 then ccc.CmpCallCard_Ngod end as "CmpCallCard_Ngod",
            case when ccc.CmpCallCard_id is not null and hvsrc.HomeVisitSource_Code <> 5
                then to_char(htsh.HomeVisitStatusHist_setDT, 'DD.MM.YYYY')
                else null
            end as "HomeVisitStatusHist_setDT",
            case when lat.Lpu_Nick is null
                then ''
                else coalesce(lat.Lpu_Nick, '')||'/'||coalesce(pc.LpuRegion_Name, '')||coalesce(pc.LpuRegionType_Name, '')
            end as "LpuRegionAttach",
            rtrim(coalesce(ltrim(rtrim(msf.MedPersonal_TabCode)||' '), '')||coalesce(ltrim(rtrim(mp.Person_FIO)||' '), '')||coalesce(ltrim(rtrim('['||ltrim(rtrim(msfls.LpuSection_Code))||'. '||ltrim(rtrim(msfls.LpuSection_Name))||']')||' '), '')||coalesce(ltrim(rtrim(post.name)), '')) as "MedStaff_Comp",
            hv.HomeVisit_isQuarantine as "HomeVisit_isQuarantine",
            case
                when (evpl.EvnVizitPL_id is not null)
                    and (d.Diag_Code = 'J06.9')
                    and (ed.EvnDirection_id is null or dt.DirType_Code != 9) then 1
                else 0
            end as "HomeVisit_needRecord",
            case
                when (evpl.EvnVizitPL_id is not null)
                and (ed.EvnDirection_id is not null and dt.DirType_Code = 9) then 1
                else 0
            end as "hasEvnDirectionIntoLab",
            evpl.EvnVizitPL_id as "EvnVizitPL_id",
            evpl.EvnVizitPL_pid as "EvnVizitPL_pid",
            ed.EvnDirection_id as "EvnDirection_id"
        
            
                FROM
                
            
            v_HomeVisit hv
            --left join v_EvnVizitPL evpl on evpl.HomeVisit_id = hv.HomeVisit_id
            left join lateral (
                select * from v_EvnVizitPL evpl
                where evpl.HomeVisit_id = hv.HomeVisit_id
                ORDER BY evpl.EvnVizitPL_id DESC
                limit 1
            ) as evpl on true
            left join v_Diag d on d.Diag_id = evpl.Diag_id
            -- переделал, так как одно посещение может быть связано с несколькими направлениями (были дубли)
            -- left join v_EvnDirection ed on ed.EvnDirection_pid = evpl.EvnVizitPL_id
            left join lateral (
                select * from v_EvnDirection ed
                where ed.EvnDirection_pid = evpl.EvnVizitPL_id
                order by ed.EvnDirection_id desc
                limit 1
            ) as ed on true
            left join v_DirType dt on dt.DirType_id = ed.DirType_id
             left join v_Lpu l on hv.Lpu_id = l.Lpu_id 
            left join v_PersonState p on hv.Person_id = p.Person_id
            left join lateral (
                select MedStaffFact_id, MedPersonal_TabCode, Post_id, LpuSection_id, MedPersonal_id, LpuBuilding_id
                from v_MedStaffFact
                where MedStaffFact_id = hv.MedStaffFact_id
                limit 1
            ) as msf on true
            left join persis.Post post on post.id = msf.Post_id
            left join v_LpuSection msfls on msfls.LpuSection_id = msf.LpuSection_id
            left join lateral (
                select MedPersonal_id, Person_FIO
                from v_MedPersonal
                where MedPersonal_id = coalesce(msf.MedPersonal_id, hv.MedPersonal_id)
                  and Lpu_id = hv.Lpu_id
                limit 1
            ) as mp on true
            left join lateral (
                select LpuRegion_Name, LpuRegionType_Name, Lpu_id
                from v_PersonCard_all
                where Person_id = hv.Person_id
                  and LpuAttachType_id = 1
                  and hv.HomeVisit_insdt >= PersonCard_begDate
                  and (hv.HomeVisit_insdt <= PersonCard_endDate or PersonCard_endDate IS NULL)
                limit 1
            ) as pc on true
            left join v_Lpu lat on lat.Lpu_id = pc.Lpu_id
            left join v_LpuRegion lr on lr.LpuRegion_id = hv.LpuRegion_id and lr.Lpu_id = l.Lpu_id
            left join v_LpuRegion lrc on lrc.LpuRegion_id = hv.LpuRegion_cid
            left join v_LpuSection ls on ls.LpuSection_id = lrc.LpuSection_id
            left join v_LpuBuilding lb on lb.LpuBuilding_id = coalesce(msf.LpuBuilding_id, ls.LpuBuilding_id)
            left join v_HomeVisitStatus hvs on hv.HomeVisitStatus_id = hvs.HomeVisitStatus_id
            left join v_HomeVisitWhoCall hvwc on hv.HomeVisitWhoCall_id = hvwc.HomeVisitWhoCall_id
            left join v_HomeVisitCallType hvct on hv.HomeVisitCallType_id = hvct.HomeVisitCallType_id
            left join v_CmpCallCard ccc on ccc.CmpCallCard_id = hv.CmpCallCard_id
            left join v_CallProfType cpt on cpt.CallProfType_id = hv.CallProfType_id
            left join v_HomeVisitSource hvsrc on hvsrc.HomeVisitSource_id = hv.HomeVisitSource_id
            left join v_MedSpecOms mso on mso.MedSpecOms_id = hv.MedSpecOms_id
            left join lateral (
                select htsh.HomeVisitStatusHist_setDT
                from v_HomeVisitStatusHist htsh
                where htsh.HomeVisit_id = hv.HomeVisit_id
                  and HomeVisitStatus_id = 1
                limit 1
            ) as htsh on true
        
             WHERE 
            (1=1) and cast(hv.HomeVisit_setDT as date) >= cast('2022-01-01' as date) and cast(hv.HomeVisit_setDT as date) <= cast('2022-02-22' as date) and hv.Lpu_id = '7' and p.Person_SurName ilike ('г'||'%') and p.Person_FirName ilike ('влад'||'%') and p.Person_SecName ilike ('влад'||'%')
            
                
                        ORDER BY
                            
            
            hv.HomeVisitStatus_id,
            hv.HomeVisit_id
        
            
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
