with params as(
    select
        cast($1 as int8) as p_Lpu_id,
        $2 as p_LpuFilial_id,
        cast($3 as int8) as p_LpuBuilding_id,
        cast($4 as int8) as p_LpuUnit_id,
        cast($5 as int8) as p_LpuSection_id,
        cast($6 as int8) as p_LpuRegionType_id,
        cast($7 as int8) as p_LpuRegion_id,
        cast($8 as int8) as p_MedStaffFact_id,
        cast($9 as timestamp) as p_begdate,
        cast($10 as timestamp) as p_enddate
),

getLpu as(
    select L.*
    from params
        left join lateral(
            select * 
            from rpt.GetLpu_succession_pg(p_Lpu_id, p_begdate, p_enddate)
            
            union all select
                -1 as Lpu_id,
                null,
                null,
                'Без прикрепления' as Lpu_Nick,
                null,
                null,
                null,
                null,
                null
        )L on true
),

IDiag as (
    select D.Diag_id 
    from Diag as D 
    where D.Diag_Code = 'I20.0' 
        or left(D.Diag_Code, 3) in ('I21','I22', 'I63', 'I60','I61','I62', 'I64', 'G45','G46')
),

evns as(
    select eps.Person_id
    from v_EvnPS as eps
        inner join v_EvnSection as es on es.EvnSection_pid = eps.EvnPS_id
            and es.EvnSection_Index = es.EvnSection_Count - 1
            and coalesce(es.EvnSection_IsPriem, 1) = 1
            and coalesce(es.LeaveType_id, -1) != 3
        inner join IDiag as D on D.Diag_id = es.Diag_id
        left join params on true
    where eps.EvnPS_disDate between p_begdate and p_enddate 
    
    union all select epl.Person_id
    from v_EvnPL as epl
        inner join v_EvnVizitPL as EvnVPL on EvnVPL.EvnVizitPL_pid = epl.EvnPL_id
        inner join IDiag as D on D.Diag_id = EvnVPL.Diag_id
        left join params on true
    where epl.EvnPL_disDate between p_begdate and p_enddate
        and epl.EvnPL_isFinish = 2
        and EvnVPL.EvnVizitPL_Index = EvnVPL.EvnVizitPL_count - 1

    union all select EU.Person_id
    from v_EvnUsluga as eu
        inner join v_UslugaComplex as UC on UC.UslugaComplex_id = eu.UslugaComplex_id
        left join params on true
    where eu.EvnClass_id in (21,22,23,24,29,37,43,47,52,62,160,100,75,76,74,197,213)
        and eu.EvnUsluga_setDate between p_begdate and p_enddate
        and UC.UslugaComplex_Code in('A16.12.004','A16.12.004.001','A16.12.004.002',
            'A16.12.004.003','A16.12.004.004','A16.12.004.005','A16.12.004.006',
            'A16.12.004.007','A16.12.004.008','A16.12.004.009','A16.12.026',
            'A16.12.026.012','A16.12.028')
),

d_group as(
    select distinct
        evns.Person_id,
        l.Lpu_id,
        l.Lpu_Nick,
        l.Lpu_endDate,
        LR.LpuRegion_id,
        LR.LpuRegion_Name
    from(
        select distinct evns.Person_id
        from evns
    )evns
        left join params on true
        left join lateral(
            select 
                PC.LpuRegion_id,
                PC.Lpu_id
            from v_PersonCard_all as pc
            where PC.LpuAttachType_id = 1
            and pc.person_id=evns.Person_id
                and PC.PersonCard_begDate <= p_enddate 
                and coalesce(PC.PersonCard_endDate,date'infinity') >= p_enddate
            limit 1
        )pc on true
        
        left join getLpu as l on l.Lpu_id = coalesce(pc.Lpu_id,-1)
        left join LpuRegion as LR on pc.LpuRegion_id = LR.LpuRegion_id
        left join v_MedStaffRegion as msr on LR.LpuRegion_id = msr.LpuRegion_id
            and msr.MedStaffRegion_endDate is null
            and msr.MedStaffRegion_isMain = 2
        left join v_MedStaffFact as msf on msr.MedStaffFact_id = msf.MedStaffFact_id
        left join LpuSection as LS on LR.LpuSection_id = LS.LpuSection_id
        left join LpuUnit as LU on LU.LpuUnit_id = LS.LpuUnit_id 
        left join LpuBuilding as LB on LU.LpuBuilding_id = LB.LpuBuilding_id
    where (p_MedStaffFact_id is null or msf.MedPersonal_id = p_MedStaffFact_id)
        and (p_LpuRegionType_id is null or lr.LpuRegionType_id = p_LpuRegionType_id)
        and (p_LpuRegion_id is null or PC.LpuRegion_id = p_LpuRegion_id)
        and (p_LpuSection_id is null or ls.LpuSection_id = p_LpuSection_id)
        and (p_LpuUnit_id is null or lu.LpuUnit_id = p_LpuUnit_id)
        and (p_LpuBuilding_id is null or lb.LpuBuilding_id = p_LpuBuilding_id)
        and (LB.LpuFilial_id in(
            select id::int8 as id from unnest(string_to_array(p_LpuFilial_id, ',')) as id)
            or coalesce(rtrim(p_LpuFilial_id), '') = '')
)

select
    row_number() over(
        order by 
            case when dd.Lpu_id = -1 then 2 else 1 end,
            concat(
                dd.Lpu_Nick,
                to_char(dd.Lpu_endDate,' (закрыта dd.mm.yyyy)')), 
            concat_ws(' ',
                PS_.Person_SurName,
                PS_.Person_FirName,
                PS_.Person_SecName)
            ) as "rNum",
    dd.Lpu_id as "Lpu_id",
    case
        when dd.Lpu_id = -1 then 'Без прикрепления'
        else concat(
                dd.Lpu_Nick,
                to_char(dd.Lpu_endDate,' (закрыта dd.mm.yyyy)'))
        end as "Lpu_Name",
    to_char(PS_.Person_BirthDay,'dd.mm.yyyy') as "Person_BirthDay",
    dd.LpuRegion_Name as "LpuRegion_Name",
    concat_ws(' ',
        PS_.Person_SurName,
        PS_.Person_FirName,
        PS_.Person_SecName) as "Pers_FIO",
    dd.Person_id as "Person_id",

    case 
        when D_V5_1.EV_DT is not null and D_V5_2.EV_DT is not null
            then
                case
                    when D_V5_1.EV_DT > D_V5_2.EV_DT 
                        then 
                            	concat_ws(chr(10),
                                to_char(D_V5_1.EV_DT,'dd.mm.yyyy'),
                                D_V5_1.Code,
                                D_V5_1.Lpu_Nick)
                    else 
                        	concat_ws(chr(10),
                            to_char(D_V5_2.EV_DT,'dd.mm.yyyy'),
                            D_V5_2.Code,
                            D_V5_2.Lpu_Nick)
                    end 
        when D_V5_1.EV_DT is not null 
            then    concat_ws(chr(10),
                    to_char(D_V5_1.EV_DT,'dd.mm.yyyy'),
                    D_V5_1.Code,
                    D_V5_1.Lpu_Nick)
        when D_V5_2.EV_DT is not null 
            then 	concat_ws(chr(10),
	                to_char(D_V5_2.EV_DT,'dd.mm.yyyy'),
	                D_V5_2.Code,
	                D_V5_2.Lpu_Nick)
         
         else   'НЕТ'
        end as "Val5",
    case 
        when D_V6_1.EV_DT is not null and D_V6_2.EV_DT is not null
            then
                case
                    when D_V6_1.EV_DT > D_V6_2.EV_DT 
                        then    concat_ws(chr(10),
                                to_char(D_V6_1.EV_DT,'dd.mm.yyyy'),
                                D_V6_1.Code,
                                D_V6_1.Lpu_Nick)
                    else 
                         	concat_ws(chr(10),
                            to_char(D_V6_2.EV_DT,'dd.mm.yyyy'),
                            D_V6_2.Code,
                            D_V6_2.Lpu_Nick)
                    end 
        when D_V6_1.EV_DT is not null 
            then    concat_ws(chr(10),
                    to_char(D_V6_1.EV_DT,'dd.mm.yyyy'),
                    D_V6_1.Code,
                    D_V6_1.Lpu_Nick)
         when D_V6_2.EV_DT is not null 
         	then 	concat_ws(chr(10),
	                to_char(D_V6_2.EV_DT,'dd.mm.yyyy'),
	                D_V6_2.Code,
	                D_V6_2.Lpu_Nick)
                
           else  'НЕТ'
        end as "Val6",
    case 
        when D_V7_1.EV_DT is not null and D_V7_2.EV_DT is not null
            then
                case
                    when D_V7_1.EV_DT > D_V7_2.EV_DT 
                        then    concat_ws(chr(10),
                                to_char(D_V7_1.EV_DT,'dd.mm.yyyy'),
                                D_V7_1.Code,
                                D_V7_1.Lpu_Nick)
                    else    concat_ws(chr(10),
                            to_char(D_V7_2.EV_DT,'dd.mm.yyyy'),
                            D_V7_2.Code,
                            D_V7_2.Lpu_Nick)
                    end 
        when D_V7_1.EV_DT is not null 
            then 
                concat_ws(chr(10),
                    to_char(D_V7_1.EV_DT,'dd.mm.yyyy'),
                    D_V7_1.Code,
                    D_V7_1.Lpu_Nick)
                    
        when D_V7_2.EV_DT is not null 
            then 
            	concat_ws(chr(10),
                to_char(D_V7_2.EV_DT,'dd.mm.yyyy'),
                D_V7_2.Code,
                D_V7_2.Lpu_Nick)
                
        else 'НЕТ'  end as "Val7",
    case 
        when D_V8_1.EV_DT is not null and D_V8_2.EV_DT is not null
            then
                case
                    when D_V8_1.EV_DT > D_V8_2.EV_DT 
                        then 
                            concat_ws(chr(10),
                                to_char(D_V8_1.EV_DT,'dd.mm.yyyy'),
                                D_V8_1.Code,
                                D_V8_1.Lpu_Nick)
                    else 
                        concat_ws(chr(10),
                            to_char(D_V8_2.EV_DT,'dd.mm.yyyy'),
                            D_V8_2.Code,
                            D_V8_2.Lpu_Nick)
                      
                    end 
        when D_V8_1.EV_DT is not null 
            then 
                	concat_ws(chr(10),
                    to_char(D_V8_1.EV_DT,'dd.mm.yyyy'),
                    D_V8_1.Code,
                    D_V8_1.Lpu_Nick)
        when D_V8_2.EV_DT is not null  
        	then
	            	concat_ws(chr(10),
	                to_char(D_V8_2.EV_DT,'dd.mm.yyyy'),
	                D_V8_2.Code,
	                D_V8_2.Lpu_Nick)
        else 'НЕТ' 
        end as "Val8",
    case 
        when D_V9_1.EV_DT is not null and D_V9_2.EV_DT is not null
            then
                case
                    when D_V9_1.EV_DT > D_V9_2.EV_DT 
                        then 
                            concat_ws(chr(10),
                                to_char(D_V9_1.EV_DT,'dd.mm.yyyy'),
                                D_V9_1.Code,
                                D_V9_1.Lpu_Nick)
                    else 
                        concat_ws(chr(10),
                            to_char(D_V9_2.EV_DT,'dd.mm.yyyy'),
                            D_V9_2.Code,
                            D_V9_2.Lpu_Nick)
                    end 
        when D_V9_1.EV_DT is not null 
            then    concat_ws(chr(10),
                    to_char(D_V9_1.EV_DT,'dd.mm.yyyy'),
                    D_V9_1.Code,
                    D_V9_1.Lpu_Nick)
                    
         when D_V9_2.EV_DT is not null 
            then
            concat_ws(chr(10),
                to_char(D_V9_2.EV_DT,'dd.mm.yyyy'),
                D_V9_2.Code,
                D_V9_2.Lpu_Nick)
         else   'НЕТ'
        end as "Val9",
    case 
        when (D_V5_1.EV_DT is not null or D_V5_2.EV_DT is not null) and D_V10_11.EV_DT is not null
            then 
                concat_ws(chr(10),
                    to_char(D_V10_11.EV_DT,'dd.mm.yyyy'),
                    D_V10_11.Code,
                    D_V10_11.Lpu_Nick)
                
        else 'НЕТ'
        end as "Val10",
 case 
        when D_V5_1.EV_DT is null and D_V5_2.EV_DT is null and D_V10_11.EV_DT is not null 
            then 
                concat_ws(chr(10),
                    to_char(D_V10_11.EV_DT,'dd.mm.yyyy'),
                    D_V10_11.Code,
                    D_V10_11.Lpu_Nick)
        else 'НЕТ'
        end as "Val11"
from d_group as dd
    left join params on true
    inner join v_PersonState as PS_ on dd.Person_id = PS_.Person_id
        and coalesce(date(PS_.Person_deadDT),date'infinity') >= p_enddate
    left join lateral(
        select
            D_.Diag_Code as Code,
            l.Lpu_Nick,
            EPS.EvnPS_disDT as EV_DT   
        from v_EvnSection as ES 
            inner join Diag as D_ on D_.Diag_id = ES.Diag_id
                and (D_.Diag_Code = 'I20.0' or left(D_.Diag_Code, 3) in ('I21','I22'))
                and coalesce(ES.EvnSection_IsPriem, 1) = 1
                and ES.EvnSection_Index = ES.EvnSection_count - 1
                and coalesce(ES.LeaveType_id, -1) != 3
            inner join v_EvnPS as EPS on EPS.EvnPS_id = ES.EvnSection_pid
                and EPS.EvnPS_disDT between p_begdate and DATEADD('d', 1, p_enddate)
            inner join v_Lpu as l on l.Lpu_id = ES.Lpu_id
                and coalesce(l.Lpu_IsTest,1) = 1
        where ES.Person_id = dd.Person_id
            and(ES.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EPS.EvnPS_disDT desc
        limit 1
    )D_V5_1 on true
    
    left join lateral(
        select 
            D_.Diag_Code as Code,
            l.Lpu_Nick,
            EPL.EvnPL_disDT as EV_DT
        from v_EvnVizitPL as EVPL 
            inner join Diag as D_ on D_.Diag_id = EVPL.Diag_id
                and (D_.Diag_Code = 'I20.0' or left(D_.Diag_Code, 3) in ('I21','I22'))
                and EVPL.EvnVizitPL_Index = EVPL.EvnVizitPL_count - 1
            inner join v_EvnPL as EPL on EPL.EvnPL_id = EVPL.EvnVizitPL_pid
                and EPL.EvnPL_IsFinish = 2
                and EPL.EvnPL_disDT between p_begdate and DATEADD('d', 1, p_enddate)
            inner join v_Lpu as l on l.Lpu_id = EVPL.Lpu_id
                and coalesce(l.Lpu_IsTest,1) = 1 
        where EVPL.Person_id = dd.Person_id
            and(EVPL.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EPL.EvnPL_disDT desc
        limit 1
    )D_V5_2 on true
    
    left join lateral(
        select 
            D_.Diag_Code as Code,
            l.Lpu_Nick,
            EPS.EvnPS_disDT as EV_DT   
        from v_EvnSection as ES 
            inner join Diag as D_ on D_.Diag_id = ES.Diag_id
                and left(D_.Diag_Code, 3) = 'I63'
                and coalesce(ES.EvnSection_IsPriem, 1) = 1
                and ES.EvnSection_Index = ES.EvnSection_count - 1
                and coalesce(ES.LeaveType_id, -1) != 3
            inner join v_EvnPS as EPS on EPS.EvnPS_id = ES.EvnSection_pid
                and EPS.EvnPS_disDT between p_begdate and DATEADD('d', 1, p_enddate)
            inner join v_Lpu as l on l.Lpu_id = ES.Lpu_id and coalesce(l.Lpu_IsTest,1) = 1
        where ES.Person_id = dd.Person_id
            and(ES.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EPS.EvnPS_disDT desc
        limit 1
    )D_V6_1 on true
    
    left join lateral(
        select 
            D_.Diag_Code as Code,
            l.Lpu_Nick,
            EPL.EvnPL_disDT as EV_DT
        from v_EvnVizitPL as EVPL 
            inner join Diag as D_ on D_.Diag_id = EVPL.Diag_id
                and left(D_.Diag_Code, 3) = 'I63'
                and EVPL.EvnVizitPL_Index = EVPL.EvnVizitPL_count - 1
            inner join v_EvnPL as EPL on EPL.EvnPL_id = EVPL.EvnVizitPL_pid
                and EPL.EvnPL_IsFinish = 2
                and EPL.EvnPL_disDT between p_begdate and DATEADD('d', 1, p_enddate)
            inner join v_Lpu as l on l.Lpu_id = EVPL.Lpu_id and coalesce(l.Lpu_IsTest,1) = 1 
        where EVPL.Person_id = dd.Person_id
            and(EVPL.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EPL.EvnPL_disDT desc
        limit 1
    )D_V6_2 on true
    
    left join lateral(
        select 
            D_.Diag_Code as Code,
            l.Lpu_Nick,
            EPS.EvnPS_disDT as EV_DT   
        from v_EvnSection as ES 
            inner join Diag as D_ on D_.Diag_id = ES.Diag_id
                and left(D_.Diag_Code, 3) in ('I60','I61','I62')
                and coalesce(ES.EvnSection_IsPriem, 1) = 1
                and ES.EvnSection_Index = ES.EvnSection_count - 1
                and coalesce(ES.LeaveType_id, -1) != 3
            inner join v_EvnPS as EPS on EPS.EvnPS_id = ES.EvnSection_pid
                and EPS.EvnPS_disDT between p_begdate and DATEADD('d', 1, p_enddate)
            inner join v_Lpu as l on l.Lpu_id = ES.Lpu_id and coalesce(l.Lpu_IsTest,1) = 1
        where ES.Person_id = dd.Person_id
            and(ES.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EPS.EvnPS_disDT desc
        limit 1
    )D_V7_1 on true
    
    left join lateral(
        select 
            D_.Diag_Code as Code,
            l.Lpu_Nick,
            EPL.EvnPL_disDT as EV_DT
        from v_EvnVizitPL as EVPL 
            inner join Diag as D_ on D_.Diag_id = EVPL.Diag_id
                and left(D_.Diag_Code, 3) in ('I60','I61','I62')
                and EVPL.EvnVizitPL_Index = EVPL.EvnVizitPL_count - 1
            inner join v_EvnPL as EPL on EPL.EvnPL_id = EVPL.EvnVizitPL_pid
                and EPL.EvnPL_IsFinish = 2
                and EPL.EvnPL_disDT between p_begdate and DATEADD('d', 1, p_enddate)
            inner join v_Lpu as l on l.Lpu_id = EVPL.Lpu_id and coalesce(l.Lpu_IsTest,1) = 1 
        where EVPL.Person_id = dd.Person_id
            and(EVPL.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EPL.EvnPL_disDT desc
        limit 1
    )D_V7_2 on true
    
    left join lateral(
        select 
            D_.Diag_Code as Code,
            l.Lpu_Nick,
            EPS.EvnPS_disDT as EV_DT   
        from v_EvnSection as ES 
            inner join Diag as D_ on D_.Diag_id = ES.Diag_id
                and left(D_.Diag_Code, 3) = 'I64'
                and coalesce(ES.EvnSection_IsPriem, 1) = 1
                and ES.EvnSection_Index = ES.EvnSection_count - 1
                and coalesce(ES.LeaveType_id, -1) != 3
            inner join v_EvnPS as EPS on EPS.EvnPS_id = ES.EvnSection_pid
                and EPS.EvnPS_disDT between p_begdate and DATEADD('d', 1, p_enddate)
            inner join v_Lpu as l on l.Lpu_id = ES.Lpu_id and coalesce(l.Lpu_IsTest,1) = 1
        where ES.Person_id = dd.Person_id
            and(ES.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EPS.EvnPS_disDT desc
        limit 1
    )D_V8_1 on true
    
    left join lateral(
        select 
            D_.Diag_Code as Code,
            l.Lpu_Nick,
            EPL.EvnPL_disDT as EV_DT
        from v_EvnVizitPL as EVPL 
            inner join Diag as D_ on D_.Diag_id = EVPL.Diag_id
                and left(D_.Diag_Code, 3) = 'I64'
                and EVPL.EvnVizitPL_Index = EVPL.EvnVizitPL_count - 1
            inner join v_EvnPL as EPL on EPL.EvnPL_id = EVPL.EvnVizitPL_pid
                and EPL.EvnPL_IsFinish = 2
                and EPL.EvnPL_disDT between p_begdate and DATEADD('d', 1, p_enddate)
            inner join v_Lpu as l on l.Lpu_id = EVPL.Lpu_id
                and coalesce(l.Lpu_IsTest,1) = 1 
        where EVPL.Person_id = dd.Person_id
            and(EVPL.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EPL.EvnPL_disDT desc
        limit 1
    )D_V8_2 on true

    left join lateral(
        select 
            D_.Diag_Code as Code,
            l.Lpu_Nick,
            EPS.EvnPS_disDT as EV_DT   
        from v_EvnSection as ES 
            inner join Diag as D_ on D_.Diag_id = ES.Diag_id
                and left(D_.Diag_Code, 3) in ('G45','G46')
                and coalesce(ES.EvnSection_IsPriem, 1) = 1
                and ES.EvnSection_Index = ES.EvnSection_count - 1
                and coalesce(ES.LeaveType_id, -1) != 3
            inner join v_EvnPS as EPS on EPS.EvnPS_id = ES.EvnSection_pid
                and EPS.EvnPS_disDT between p_begdate and DATEADD('d', 1, p_enddate)
            inner join v_Lpu as l on l.Lpu_id = ES.Lpu_id and coalesce(l.Lpu_IsTest,1) = 1
        where ES.Person_id = dd.Person_id
            and(ES.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EPS.EvnPS_disDT desc
        limit 1
    )D_V9_1 on true
    
    left join lateral(
        select 
            D_.Diag_Code as Code,
            l.Lpu_Nick,
            EPL.EvnPL_disDT as EV_DT
        from v_EvnVizitPL as EVPL 
            inner join Diag as D_ on D_.Diag_id = EVPL.Diag_id
                and left(D_.Diag_Code, 3) in ('G45','G46')
                and EVPL.EvnVizitPL_Index = EVPL.EvnVizitPL_count - 1
            inner join v_EvnPL as EPL on EPL.EvnPL_id = EVPL.EvnVizitPL_pid
                and EPL.EvnPL_disDT between p_begdate and DATEADD('d', 1, p_enddate)
            inner join v_Lpu as l on l.Lpu_id = EVPL.Lpu_id and coalesce(l.Lpu_IsTest,1) = 1 
        where EVPL.Person_id = dd.Person_id
            and(EVPL.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EPL.EvnPL_disDT desc
        limit 1
    )D_V9_2 on true
    
    left join lateral(
        select 
            UC.UslugaComplex_Code as Code,
            l.Lpu_Nick,
            EU.EvnUsluga_setDT as EV_DT
        from v_EvnUsluga as EU 
            inner join v_UslugaComplex as UC on EU.UslugaComplex_id = UC.UslugaComplex_id
                and UC.UslugaComplex_Code in (
                    'A16.12.004', 'A16.12.004.001', 'A16.12.004.002', 'A16.12.004.003',
                    'A16.12.004.004', 'A16.12.004.005', 'A16.12.004.006', 'A16.12.004.007',
                    'A16.12.004.008', 'A16.12.004.009', 'A16.12.026', 'A16.12.026.012', 
                    'A16.12.028')
            inner join v_Lpu as l on l.Lpu_id = coalesce(EU.Lpu_uid, EU.Lpu_id)
                and coalesce(l.Lpu_IsTest,1) = 1
        where EU.EvnUsluga_setDT between p_begdate and p_enddate
            and EU.Person_id = dd.Person_id
            and(EU.Lpu_id not in (100, 101, 13002677, 13002714, 150000))
        order by EU.EvnUsluga_setDT desc
        limit 1
    )D_V10_11 on true
