---

with agg_p as (
select si.id, sum(abs(rd.sum_dt_pu - rd.sum_ct_pu)) as s_pu, sum(abs(rd.sum_dt_bu - rd.sum_ct_bu)) as s_bu
from ourpension.spr_indicator_bu si
join ourpension.spr_indicator_bu si1
on si.id = si1.is_aggregate
join ourpension.spr_accounts_indicator_bu sa
    on sa.spr_indicator_bu_id = si1.id
join back_office.report_conformity_pu_bu_detail rd
    on rd.report_conformity_pu_bu_id = $P{id}
    and rd."account" = sa.account
group by si.id
),
agg_sum as (
select si.id, sum(abs(rd.sum_dt_pu - rd.sum_ct_pu)) as s_pu, sum(abs(rd.sum_dt_bu - rd.sum_ct_bu)) as s_bu
from ourpension.spr_indicator_bu si
join ourpension.spr_accounts_indicator_bu sa
    on sa.spr_indicator_bu_id = si.id
join back_office.report_conformity_pu_bu_detail rd
    on rd.report_conformity_pu_bu_id = $P{id}
    and rd."account" = sa.account
join back_office.report_conformity_pu_bu r
    on r.id = rd.report_conformity_pu_bu_id
and r."service_type" = any(si."service_type")
group by si.id
),
all_sum as (
select 0 as id, 1 as step, sum(s_pu) as s_pu, sum(s_bu) as s_bu
from agg_sum
),
agg_all as (
select id, s_pu, s_bu
from agg_p
union all
select a.id, a.s_pu, a.s_bu
from agg_sum a
left join agg_p b
     on a.id = b.id
where b.id is null
),
si_agg as (
select si.id, 1 as step, string_agg(sa.account,'+') as accounts
from ourpension.spr_indicator_bu si
join ourpension.spr_accounts_indicator_bu sa
    on sa.spr_indicator_bu_id = si.id
join back_office.report_conformity_pu_bu r
    on r.id = $P{id}
and r."service_type" = any(si."service_type")
group by si.id
)
select si.id, sia.step, si.name, sia.accounts, a.s_pu, a.s_bu, coalesce(a.s_pu,0) - coalesce(a.s_bu,0) as razn
from ourpension.spr_indicator_bu si
join si_agg sia
on si.id = sia.id
left join agg_all a
     on si.id = a.id
union all
select id, step, '', '', s_pu, s_bu, coalesce(s_pu,0) - coalesce(s_bu, 0)
from all_sum
union all
select distinct si.is_aggregate, 2, 'в т.ч.', '', 0, 0, 0
from ourpension.spr_indicator_bu si
join back_office.report_conformity_pu_bu r
on r.id = $P{id}
and r."service_type" = any(si."service_type")
where si.is_aggregate is not null and 'OPS' = any(si."service_type")
order by id, step