---

with deposit(sharer_id, effective_date, value, document) as (

-- Взносы и инвест.доход
select
    s.id,
    t.effective_date,
    t.value,
    case
     when cat.mnemonics = 'ИПС' then ot.short_name || coalesce(' ПП №' || io.number, '')
     else ot.short_name
    end
from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join back_office.operation_type ot on ot.id = o.operation_type_id
    join back_office.account ca on ca.id = t.credit_account_id
    join back_office.account_type cat on cat.id = ca.account_type_id
    join back_office.account_type_group catg on catg.id = cat.account_type_group_id
     and catg.mnemonics = 'Н'
    join ourpension.sharer s on ca.owner_sharer_id = s.id
     and s.id = $P{sharer_id}
    join ourpension.individual i on i.id = s.individual_id
    left join back_office.incoming_order_operation ioo on o.id = ioo.operation_id
    left join back_office.incoming_order io on io.id = ioo.incoming_order_id
where o.approved is not null
    and o.deleted is null
    and t.effective_date <= $P{d2}
union all
-- Перераспределение: списание без кредитового счета
select
    s.id,
    t.effective_date,
    -t.value,
    ot.short_name
from back_office.transaction t
    join back_office.operation o
     on o.id = t.operation_id
    join back_office.operation_type ot
     on ot.id = o.operation_type_id
     and ot.code = 'REDISTRIBUTION'
    join back_office.account da
     on da.id = t.debit_account_id
    join ourpension.sharer s
     on s.id = da.owner_sharer_id
     and s.id = $P{sharer_id}
where o.approved is not null
    and o.deleted is null
    and t.credit_account_id is null
    and t.effective_date <= $P{d2}
--Взносы и компенсации на Р(С) и Р(П)
union all
select
    s.id,
    t.effective_date,
    t.value,
    ot.short_name
from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join back_office.operation_type ot on ot.id = o.operation_type_id
     and ot.code in ('DEPOSIT_ENROLLMENT', 'COMPENSATION_FROM_OWN_FUNDS')
    join back_office.account ca on ca.id = t.credit_account_id
    join back_office.account_type cat on cat.id = ca.account_type_id
     and cat.mnemonics in ('Р(П) ИПС', 'Р(П) ИПС ИД', 'Р(С) ИПС', 'Р(С) ИПС ИД')
    join ourpension.sharer s on ca.owner_sharer_id = s.id
     and s.id = $P{sharer_id}
    join ourpension.individual i on i.id = s.individual_id
    left join back_office.incoming_order_operation ioo on o.id = ioo.operation_id
    left join back_office.incoming_order io on io.id = ioo.incoming_order_id
where o.approved is not null
    and o.deleted is null
    and t.effective_date <= $P{d2}

-- Перераспределение на другие СПС и ИПС
union all
select
    s.id,
    t.effective_date,
- t.value,
    ot.short_name
from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join back_office.operation_type ot on ot.id = o.operation_type_id
    join back_office.account ca on ca.id = t.credit_account_id
    join back_office.account_type cat on cat.id = ca.account_type_id
and cat.mnemonics in ('ИПС', 'СПС', 'СПС И', 'ИПС ИД', 'СПС ИД', 'СПС И ИД', 'Р С' )
    join back_office.account da on da.id = t.debit_account_id
    join back_office.account_type dat on dat.id = da.account_type_id
    join ourpension.sharer s on s.id = da.owner_sharer_id
     and s.id = $P{sharer_id}
where o.approved is not null
    and o.deleted is null
    and t.effective_date <= $P{d2}
union all
-- Инвест.доход на резервные счета
select
    s.id,
    t.effective_date,
    t.value,
    ot.short_name
from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join back_office.operation_type ot on ot.id = o.operation_type_id
    join back_office.account ca on ca.id = t.credit_account_id
    join back_office.account_type cat on cat.id = ca.account_type_id
    join back_office.account_type_group catg on catg.id = cat.account_type_group_id
     and catg.mnemonics in ('Р(С)', 'Р(П)')
    join ourpension.sharer s on ca.owner_sharer_id = s.id
     and s.id = $P{sharer_id}
    join ourpension.individual i on i.id = s.individual_id
where o.approved is not null
    and o.deleted is null
    and ot.code = 'PENSION_INVESTMENT_REVENUE_ENROLLMENT'
    and t.effective_date <= $P{d2}

union all
-- Перевод из страхового резерва в выплатной
select
    s.id,
    t.effective_date,
    t.value,
    ot.short_name
from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join back_office.operation_type ot on ot.id = o.operation_type_id
    join back_office.account ca on ca.id = t.credit_account_id
    join back_office.account_type cat on cat.id = ca.account_type_id
    join back_office.account_type_group catg on catg.id = cat.account_type_group_id
     and catg.mnemonics in ('Р(П)', 'Р(С)')
    join back_office.account da on da.id = t.debit_account_id
    join back_office.account_type dat on dat.id = da.account_type_id
     and dat.mnemonics in ('Р С', 'Р С ИД', 'СПС', 'СПС И', 'СПС ИД', 'СПС И ИД')
    join ourpension.sharer s on s.id = ca.owner_sharer_id
     and s.id = $P{sharer_id}
where o.approved is not null
    and o.deleted is null
    and t.effective_date <= $P{d2}

union all
    -- Возвраты
select
    s.id,
    t.effective_date,
    t.value,
    ot.short_name
from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join back_office.operation_type ot on ot.id = o.operation_type_id
    join back_office.account ca on ca.id = t.credit_account_id
    join back_office.account_type cat on cat.id = ca.account_type_id
    join back_office.account_type_group catg on catg.id = cat.account_type_group_id
    join ourpension.sharer s on ca.owner_sharer_id = s.id
     and s.id = $P{sharer_id}
    join ourpension.individual i on i.id = s.individual_id
where o.approved is not null
    and o.deleted is null
    and o.operation_type_id in (16, 61, 60)
    and o.effective_date <= $P{d2}

union all
    -- МГД
select
    s.id,
    t.effective_date,
    t.value,
    'Разнесение МГД'
from back_office.transaction t
    join back_office.operation o on t.operation_id = o.id
    join back_office.operation_type ot on ot.id = o.operation_type_id
    join back_office.account ca on ca.id = t.credit_account_id
    join back_office.account_type cat on cat.id = ca.account_type_id
     and cat.mnemonics in ('НВ ВС ИПС','НВ КС ИПС','НВ ИПС')
    join ourpension.sharer s on s.id = ca.owner_sharer_id
     and s.id = $P{sharer_id}
where o.approved is not null
    and o.deleted is null
    and t.debit_account_id is null
and t.effective_date <= $P{d2}

), deposit_lines(sharer_id, effective_date, value, document, num) as (

select
    t.*,
    row_number() over (partition by sharer_id order by effective_date)
from (
    select
     sharer_id,
     effective_date,
     sum(value) as value,
     document
    from deposit
    group by sharer_id, effective_date, document) t

), payment (sharer_id, effective_date, value, document) as (

select
    s.id,
    t.effective_date,
    t.value,
    case cat.mnemonics
        when 'В НДФЛ' then 'Удержание НДФЛ' || coalesce(' ПП №'|| pr.tax_order_number, '')
        else ot.short_name || coalesce(' ПП №'|| pr.payment_order_number, '')
    end
from back_office.transaction t
    join back_office.account da on da.id = t.debit_account_id
    join back_office.account ca on ca.id = t.credit_account_id
    join back_office.account_type cat on cat.id = ca.account_type_id
    join back_office.account_type_group catg on cat.account_type_group_id = catg.id
     and catg.mnemonics = 'В'
    join back_office.operation o on o.id = t.operation_id
    join back_office.operation_type ot on ot.id = o.operation_type_id
    join ourpension.sharer s on da.owner_sharer_id = s.id
     and s.id = $P{sharer_id}
    left join back_office.payment_register pr on o.payment_register_id = pr.id
where o.approved is not null
    and o.deleted is null
    and t.effective_date <= $P{d2}

), payment_lines(sharer_id, effective_date, value, document, num) as (

select
    sharer_id, effective_date, sum(value), document,
    row_number() over (partition by sharer_id order by effective_date)
from payment
group by sharer_id, effective_date, document

), lines(sharer_id, num) as (

select
    s.id,
    generate_series(1, greatest(max(d.num), max(p.num)))
from ourpension.sharer s
    left join deposit_lines d on s.id = d.sharer_id
    left join payment_lines p on s.id = p.sharer_id
where s.id = $P{sharer_id}
group by s.id

), report(num, deposit_num, deposit_effective_date, deposit_value, deposit_document,
payment_num, payment_effective_date, payment_value, payment_document) as (

select
    l.num,
    d.num,
    d.effective_date,
    d.value,
    d.document,
    p.num,
    p.effective_date,
    p.value,
    p.document
from ourpension.sharer s
    join lines l on l.sharer_id = s.id
    left join deposit_lines d on l.sharer_id = d.sharer_id and l.num = d.num
    left join payment_lines p on l.sharer_id = p.sharer_id and l.num = p.num
where s.id = $P{sharer_id}

) select * from report order by num;