---

with operations (id, ef_year, cred_mnemonics, deb_mnemonics, value,operation_type_id) as (
	select
	o.id,
    extract(year from o.effective_date),
    cat.mnemonics as cred_mnemonics,
    dat.mnemonics as deb_mnemonics,
    t.value,
    o.operation_type_id
  from back_office.operation o
    join back_office.transaction t on t.operation_id = o.id
    left join back_office.account da on da.id = t.debit_account_id
    left join back_office.account ca  on ca.id = t.credit_account_id
    left join back_office.account_type dat on dat.id = da.account_type_id
    left join back_office.account_type cat on cat.id = ca.account_type_id
  where o.effective_date between cast($P{bDate} as date)  and cast($P{eDate} as date)
		and da.owner_sharer_id = $P{sharerId}
		and da.owner_sharer_id <> coalesce(ca.owner_sharer_id, 0)
	
union all

  select
	o.id,
    extract(year from o.effective_date),
    cat.mnemonics,
    case when dat.mnemonics is null and ot.code = 'PDS_RETURN_NDFL_REDEMPTION' 
         then cat.mnemonics
         else dat.mnemonics end,
    case when ot.code = 'PDS_RETURN_NDFL_REDEMPTION' then -t.value else t.value end,
    o.operation_type_id
  from back_office.operation o
  join back_office.operation_type ot 
    on ot.id = o.operation_type_id
    join back_office.transaction t on t.operation_id = o.id
	join back_office.account ca  on ca.id = t.credit_account_id
    left join back_office.account da on da.id = t.debit_account_id
    left join back_office.account_type dat on dat.id = da.account_type_id
    left join back_office.account_type cat on cat.id = ca.account_type_id
  where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
		and ca.owner_sharer_id = $P{sharerId}

	union all

  -- Возврат ОПС
	select
	o.id,
    extract(year from o.effective_date),
    cat.mnemonics,
    dat.mnemonics,
    t.value,
    o.operation_type_id
	from back_office.operation o
	  join back_office.ops_payment_return r on r.id = o.ops_payment_return_id
	  join back_office.ops_payment_register_decision rd on rd.id = r.ops_payment_register_decision_id
	  join back_office.ops_payment_decision d on d.id = rd.ops_payment_decision_id
	  join ourpension.ops_pension_assignment_application a on a.id = d.ops_pension_assignment_application_id	
	
    join back_office.transaction t on t.operation_id = o.id
    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 dat on dat.id = da.account_type_id
    join back_office.account_type cat on cat.id = ca.account_type_id
    
  where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
		and ca.owner_individual_id = rd.recipient_individual_id
		and a.sharer_id = $P{sharerId}
		
	union all
	
	  -- Возврат НПО
	select
	o.id,
    extract(year from o.effective_date),
    cat.mnemonics,
    dat.mnemonics,
    t.value,
    o.operation_type_id
	from back_office.operation o
	  join back_office.payment_return pr on pr.id = o.payment_return_id
	  join back_office.payment p on p.id = pr.payment_id
	  join back_office.payment_register r on p.payment_register_id = r.id
	
    join back_office.transaction t on t.operation_id = o.id
    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 dat on dat.id = da.account_type_id
    join back_office.account_type cat on cat.id = ca.account_type_id
	
  where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
		and ca.owner_individual_id = p.recipient_individual_id
		and p.sharer_id = $P{sharerId}

	union all

  -- Повторная выплата возврата
	select
	o.id,
    extract(year from o.effective_date),
    cat.mnemonics,
    dat.mnemonics,
    t.value,
    o.operation_type_id
	from back_office.operation o
	  join back_office.ops_payment_register_decision rd on rd.ops_payment_register_id = o.ops_payment_register_id
      join back_office.ops_payment_return r on r.id = rd.ops_payment_return_id
	  join back_office.ops_payment_decision d on d.id = rd.ops_payment_decision_id
	  join ourpension.ops_pension_assignment_application a on a.id = d.ops_pension_assignment_application_id
      join back_office.transaction t on t.operation_id = o.id
      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 dat on dat.id = da.account_type_id
      join back_office.account_type cat on cat.id = ca.account_type_id
  where o.effective_date between cast($P{bDate} as date) and cast($P{eDate} as date)
		and ca.owner_individual_id = rd.recipient_individual_id
		and a.sharer_id = $P{sharerId}
)
select
    cast(p.ef_year as varchar(4)) as year_name,
    count(*) as count_oper,
    sum(p.sum_value) as sum_value,
    sum(n.sum_ndfl) as sum_ndfl
from (
    select
        o.id,
        o.ef_year,
        sum(o.value) as sum_value
    from operations o
    join back_office.operation_type ot
        on ot.id = o.operation_type_id
    where o.deb_mnemonics like '%СВ%'
      and coalesce(o.cred_mnemonics, '') not like '%НДФЛ%'
      and ot.code in (
          'PAYMENT_TERMINATED',
          'PDS_REDEMPTION_PAYMENT',
          'PDS_REDEMPTION_PAYMENT_TO_NPF',
          'PDS_REDEMPTION_LIFELONG_PAYMENT',
          'PDS_REDEMPTION_REDEMPTION_LOSS',
          'PDS_ADDITIONAL_PAYMENT_REDEMPTION',
          'PDS_REP_PAYMENT_TERMINATED',
          'SECOND_PAYMENT_TERMINATED',
          'PDS_RETURN_NDFL_REDEMPTION'
      )
    group by o.id, o.ef_year
) p
left join (
    select
        id,
        sum(value) as sum_ndfl
    from operations
    where cred_mnemonics like '%НДФЛ%'
    group by id
) n on n.id = p.id
group by p.ef_year
order by p.ef_year