---

with signers as (
    select
        a.id,
        concat_ws(
            ' ', a.last_name, a.first_name, a.middle_name
        ) as fio,
        (
            select string_agg(
                distinct p.position, '; ' order by p.position
            )
            from ourpension.agent_position p
            where p.agent_id = a.id
        ) as position
    from ourpension.agent a
    where a.id = any(
        cast(
            string_to_array($P{signAgentIdPU}, ',')
            as integer[]
        )
    )
    or a.id = any(
        cast(
            string_to_array($P{signAgentIdBU}, ',')
            as integer[]
        )
    )
),
sign_pu as (
    select
        string_agg(fio, chr(10) order by id) as fio,
        string_agg(
            coalesce(position, ''), chr(10) order by id
        ) as position
    from signers
    where id = any(
        cast(
            string_to_array($P{signAgentIdPU}, ',')
            as integer[]
        )
    )
),
sign_bu as (
    select
        string_agg(fio, chr(10) order by id) as fio,
        string_agg(
            coalesce(position, ''), chr(10) order by id
        ) as position
    from signers
    where id = any(
        cast(
            string_to_array($P{signAgentIdBU}, ',')
            as integer[]
        )
    )
)
select
    r.form_date,
    to_char(
        to_date(r.m_y_report, 'MM.YYYY')
            + interval '1 month - 1 day',
        'DD.MM.YYYY'
    ) as endDate,
    pu.fio as sign_pu_fio,
    pu.position as sign_pu_position,
    bu.fio as sign_bu_fio,
    bu.position as sign_bu_position
from back_office.report_conformity_pu_bu r
cross join sign_pu pu
cross join sign_bu bu
where r.id = $P{id}