---

/*на основе данных в НПФ360, но корректность расчетов бизнес не проверял */
/*with operation(year,value)as (
	--credit
SELECT date_part('year',o.effective_date), t.value
FROM ourpension.contract c
	join ourpension.sharer sh on c.id = sh.contract_id
	join back_office.account ca on ca.owner_sharer_id = sh.id
	join back_office.transaction t on ca.id = t.credit_account_id 
	join back_office.operation o on t.operation_id = o.id
	join back_office.account_type cat on cat.id = ca.account_type_id 
where sh.id = :sharer_id
	and case when c.service_type = 'NPO' then cat.mnemonics in ('ИПС_ФЛ')
			when c.service_type = 'PDS' then cat.mnemonics in ('ПДС СВ')
			else false end
	and o.approved is not null --Авторизована
	and o.deleted is null --Не удалена
	and o.revoked is null --Не отозвана
union all
--debit
SELECT date_part('year',o.effective_date), -t.value
FROM ourpension.contract c
	join ourpension.sharer sh on c.id = sh.contract_id
	join back_office.account da on da.owner_sharer_id = sh.id
	join back_office.transaction t on da.id = t.debit_account_id
	join back_office.operation o on t.operation_id = o.id
	join back_office.account_type dat on dat.id = da.account_type_id 
where sh.id = :sharer_id
	and case when c.service_type = 'NPO' then dat.mnemonics in ('ИПС_ФЛ')
			when c.service_type = 'PDS' then dat.mnemonics in ('ПДС СВ')
			else false end
	and o.approved is not null --Авторизована
	and o.deleted is null --Не удалена
	and o.revoked is null --Не отозвана
	),
sum_by_year	(year, value) as (
SELECT cast (o.year as integer), sum(o.value) as value FROM operation o 
	group by o.year)*/
	
/*На основе данных из 1СВС, задача 63495*/	
with sum_by_year	(year, value) as (
select date_part('year', t.period_date) y,
       sum(t.amount) op_y_total
  from ourpension.contract c
	join ourpension.sharer sh on c.id = sh.contract_id
  join mgr.ft_dwh_get__pension_account_operation(cast ('{"p_sPensionAccountId":"' || sh.sharer_id_1c || '", "p_sServiceType":"' || c.service_type || '"}' as jsonb)) t on true
  where sh.id = :sharer_id
  and case when c.service_type = 'NPO' then t.operation_type_name in ('Ч/з банк', 'От работодателя')
			when c.service_type = 'PDS' then t.operation_type_name = 'Сберегательные взносы'
			else false end
group by y
order by y)

select m_nv.year, 'NONE' as type, coalesce(sum_by_year.value,0) as sum_income, null as sum_tax_deduction, null as date, m_nv.value as max_tax_deduction
from ourpension.max_year_tax_deduction m_nv
left join sum_by_year on sum_by_year.year = m_nv.year and sum_by_year.value >=0
where m_nv.year >= (
	select cast (date_part('year', c.date) as integer ) 
	from ourpension.contract c 
	join ourpension.sharer sh on c.id = sh.contract_id and sh.id = :sharer_id)
	order by m_nv.year