---

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 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;