---

#set ($sharer = $db.find("
    select sh.id as sharer_id
    from ourpension.sharer sh
    where sh.individual_id = :p_individual_id
"))

#set ($data = $db.find("
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.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
)
select coalesce(sum_by_year.value, 0) as sum_income, 
       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 = :p_year
"))