---

#set ($sharer = $db.find("
    select sh.id as sharer_id,
           sh.sharer_id_1c,
           c.service_type
    from ourpension.application_termination_contract atc
    join ourpension.sharer sh on sh.id = atc.sharer_id
    join ourpension.contract c on c.id = sh.contract_id
    where atc.id = :p_application_termination_contract_id
"))
#set ($sharerId = $sharer.sharer_id)
#set ($pYear = $p_year)
#set ($params = $db.params())
$params.add("p_sharer_id", $sharerId)
$params.add("p_year_val", $pYear)
#set ($p1 = '{"p_sPensionAccountId":"')
#set ($p2 = $sharer.sharer_id_1c)
#set ($p3 = '", "p_sServiceType":"')
#set ($p4 = $sharer.service_type)
#set ($p5 = '"}')
$params.add("p_json", "${p1}${p2}${p3}${p4}${p5}")
#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_json as jsonb)
    ) t on true
    where sh.id = :p_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_val
", $params))



#set ($rez1 = $db.eval("SELECT ourpension.f_merge_year_tax_deduction(
    CAST(NULLIF(CAST(:p_application_termination_contract_id AS text), '') AS integer),
    CAST(CASE WHEN CAST(:p_date AS text) = '9999-99-99' OR CAST(:p_date AS text) = '' THEN NULL
         ELSE CAST(:p_date AS text) END AS date),
    CAST(NULLIF(CAST(:p_individual_id AS text), '') AS integer),
    CAST(NULLIF(CAST($data.max_tax_deduction AS text), '') AS integer),
    CAST(NULLIF(CAST($data.sum_income AS text), '') AS numeric),
    CAST(CASE WHEN CAST(:p_sum_tax_deduction AS text) = '-1' OR CAST(:p_sum_tax_deduction AS text) = '' THEN NULL
         ELSE CAST(:p_sum_tax_deduction AS text) END AS numeric),
    CAST(:p_type AS text),
    CAST(NULLIF(CAST(:p_year AS text), '') AS integer),
    CAST(:username AS text),
    CAST(NULLIF(CAST(:user_id AS text), '') AS integer),
    CAST(NULLIF(CAST(:p_batch_id AS text), '') AS integer)
)"))