with params as(
	select
		cast($1 as int8) as p_lpu_id,
		$2 as p_lpufiliallist,
		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_lpuunittype_id,
		cast($7 as int8) as p_paytype,
		cast($8 as timestamp) as p_begdate,
		cast($9 as time) as p_begtime,
		cast($10 as timestamp) as p_enddate,
		cast($11 as time) as p_endtime
),

month_quarter as(
    select distinct
        rownum,
        id,
        val
    from(values
        (1,1, 'Январь'),
        (2,2, 'Февраль'),
        (3,3, 'Март'),
        (4,13, '1 квартал'),
        (5,4, 'Апрель'),
        (6,5, 'Май'),
        (7,6, 'Июнь'),
        (8,14, '2 квартал'),
        (9,7, 'Июль'),
        (10,8, 'Август'),
        (11,9, 'Сентябрь'),
        (12,15, '3 квартал'),
        (13,10, 'Октябрь'),
        (14,11, 'Ноябрь'),
        (15,12, 'Декабрь'),
        (16,16, '4 квартал'),
        (17,17, 'Итого за год')
    ) as tableST (rownum,id,val)
),

LpuP as(
    select
		L.Lpu_id,
		params.*
    from params
		left join lateral(
			select * 
			from rpt.GetLpu_succession_pg(p_Lpu_id, p_begDate, dateadd('day', 1, p_endDate))L
		)L on true		
),

LpSection as(
    select 
        LpuP.*,
        LS.LpuSection_id,
        LS.LpuSection_Code,
        LS.LpuSection_Name,
        LU.LpuUnitType_SysNick,
        ls.LpuSection_setDate,
        ls. LpuSection_disDate,
        LpuSectionAge_id
    from LpuP 
        inner join v_LpuSection as LS on LS.Lpu_id = LpuP.Lpu_id
        inner join v_LpuUnit as LU on LU.LpuUnit_id = LS.LpuUnit_id
        inner join v_LpuBuilding as LB on LB.LpuBuilding_id = LU.LpuBuilding_id
    where true
        and LS.LpuSectionProfile_SysNick = 'profil'
        and (LS.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
        and (LS.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
        and (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
        and LU.LpuUnitType_SysNick like '%stac%' 
        and (LU.LpuUnitType_id = p_LpuUnitType_id or p_LpuUnitType_id is null)
        and (LB.LpuFilial_id in(
            select id::int8 as id from unnest(string_to_array(p_LpuFilialList, ',')) as id)
            or coalesce(rtrim(p_LpuFilialList), '') = '')
)

select
    case
        when zz.month_id is null
            then 15
        else zz.month_id end as month_id,
    zz.lev,
    to_char(zz.EvnSection_setDate, 'dd.mm.yyyy') as "EvnSection_setDate1",
    case when zz.lev = 15 then 'Итого ' else zz.mes end as mes,
    case
        when zz.lev = 3
            then 'Итого по дате  '||to_char(zz.EvnSection_setDate, 'dd.mm.yyyy')
        else to_char(zz.EvnSection_setDate, 'dd.mm.yyyy')
        end "EvnSection_setDate",
    zz.lpu_name,
    zz.Diag_Code as "Diag_Code",
    zz.v5,
    zz.v6,
    zz.v7,
    zz.v8,
    zz.v9,
    zz.v10,
    zz.v11,
    zz.v12
from(
    select
        z.month_id,
        mm.val as mes,
        z.EvnSection_setDate,
        z.lpu_name,
        z.Diag_Code,
        z.lev,
        z.v5,
        z.v6,
        z.v7,
        z.v8,
        z.v9,
        z.v10,
        z.v11,
        z.v12
    from(
        select 
            month(EvnSection_setDT) as month_id,
            EvnSection_setDate,
            lpu.lpu_name,
            d.Diag_Code,
            grouping(
                month(EvnSection_setDT),
                EvnSection_setDate,
                lpu.lpu_name,
                d.Diag_Code) as lev,
            count(distinct ES.EvnSection_id) as v5,
            count(distinct
                case
                    when coalesce(es.DiagSetPhase_id,eps.DiagSetPhase_pid) in (1,4)
                        then ES.EvnSection_id end) as v6,
            count(distinct
                case
                    when coalesce(es.DiagSetPhase_id,eps.DiagSetPhase_pid) = 2
                        then ES.EvnSection_id end) as v7,
            count(distinct
                case
                    when coalesce(es.DiagSetPhase_id,eps.DiagSetPhase_pid) = 3
                        then ES.EvnSection_id end) as v8,
            count(distinct
                case
                    when coalesce(es.DiagSetPhase_id,eps.DiagSetPhase_pid) = 5
                        then ES.EvnSection_id end) as v9,
            count(distinct
                case
                    when coalesce(es.DiagSetPhase_id,eps.DiagSetPhase_pid) = 7
                        then ES.EvnSection_id end) as v10,
            count(distinct
                case
                    when coalesce(es.DiagSetPhase_id,eps.DiagSetPhase_pid) = 6
                        then ES.EvnSection_id end) as v11,
            count(distinct
                case
                    when es.DiagSetPhase_id is null and  eps.DiagSetPhase_pid is null
                        then ES.EvnSection_id end) as v12
        from LpSection as LS  
            inner join v_EvnSection as ES on ES.LpuSection_id = LS.LpuSection_id
                and EvnSection_setDT between p_begdate + p_begtime and p_enddate + p_endtime
            inner join v_lpu as lpu  on lpu.lpu_id = ls.lpu_id
            inner join v_EvnPS as EPS  on EPS.EvnPS_id = ES.EvnSection_pid
            inner join v_Diag as D  on D.Diag_id = coalesce(es.Diag_id,eps.Diag_pid)
                and (D.Diag_Code between 'J12.0' and 'J18.9'
                    or D.Diag_Code in ('B34.2', 'B33.8', 'Z03.8', 'Z22.8', 'Z20.8','U07.1','U07.2')) 
            left join v_DiagSetPhase as sp on sp.DiagSetPhase_id = eps.DiagSetPhase_pid
            left join v_DiagSetPhase as spes on spes.DiagSetPhase_id = es.DiagSetPhase_id
        where coalesce(eps.evnps_iscont,1) = 1
            and    ES.EvnSection_Index = (
                select min(ES.EvnSection_Index)
                from v_EvnSection as ES 
                where  ES.EvnSection_pid = EPS.EvnPS_id
                and coalesce(ES.EvnSection_IsPriem,1) = 1)
        group by rollup(
            month(EvnSection_setDT),
            EvnSection_setDate,
            lpu.lpu_name,
            d.Diag_Code)
    )z
        left join month_quarter as mm on mm.id = z.month_id
    where z.v5 > 0 or z.v6 > 0 or z.v7 > 0 or z.v8 > 0 or z.v9 > 0 or z.v10 > 0 or z.v11 > 0 or z.v12 > 0
)zz
where zz.lev <> 7 and zz.lev <> 1
order by
    zz.month_id,
    zz.EvnSection_setDate,
    zz.lev,
    zz.lpu_name,
    zz.Diag_Code
