---

WITH tessa (path) AS (
SELECT value FROM tools.npf360_settings WHERE "name" = 'TESSA_API'
)
SELECT
case when td.canceled is null then '<span class="glyphicon glyphicon-ok" style="color: green; align-items: center; justify-content: center;"></span>'
    when td.canceled is not null then '<span class="glyphicon glyphicon-remove" style="color: red; align-items: center; justify-content: center;"></span>'
    else'не определено>'
end as actual,
to_char(td.version, 'DD.MM.YYYY') as created_at,
to_char(td.date, 'DD.MM.YYYY') as certificate_date,
td.number as document_number,
case
when contract.entity_id is null
    then '<a target="_blank" href="view?viewCode=Client_list&id=' || contract.individual_id || '">' || apdi.last_name || ' ' || apdi.first_name || coalesce(' ' || apdi.middle_name, '') || '</a>'
else '<a target="_blank" href="view?viewCode=NCIuIMAW&id=' || contract.entity_id || '">' || e.short_name || '</a>'
end as client_full_name,
case sat.service_type
when 'NPO' then 'НПО'
when 'OPS' then 'ОПС'
when'PDS' then 'ПДС'
end as contract_activity_type,
contract.number as contract_number,
'<a target="_blank" href="view?viewCode=7HPZX8vF&id='||contract.id||'">'||contract.number||'</a>' as contract_number_link,
sh.number as account_number,
CASE
    WHEN EXISTS (
        SELECT 1
        FROM report.fns_reference_request_journal fj
        WHERE fj.contract_number = contract.number
         AND fj.response_received = true
    ) THEN 'ФНС'
    ELSE info.name
END as receive_method,
a.last_name || ' ' || a.first_name || ' ' || a.middle_name as created_by_username,
'<a target="_blank" href="view?viewCode=application_rd_npo_pds&id='||atc.id||'">'||atc.number||'</a>' as application_id,
td.comment as comment_text,
atc.number as atc_number,
atc.id as atc_id,
to_char(contract.date, 'DD.MM.YYYY') as contract_date,
ind.id as i_id,
'<a target="_blank" href="' || tessa.path || 'card/'|| td.tessa_card_id ||'" target="_blank">' || td.tessa_card_id || '</a>' as tessa_card_id,
apdi.last_name || ' ' || apdi.first_name || coalesce(' ' || apdi.middle_name, '') as full_fl_name,
to_char(td.accepted, 'DD.MM.YYYY HH24:MI:SS') as accepted,
to_char(td.canceled, 'DD.MM.YYYY HH24:MI:SS') as canceled,
td.id as id,
dev.get_history_link2('gYRJIbh0','ourpension.year_tax_deduction',td.id,'История изменений') as history
FROM ourpension.year_tax_deduction td
LEFT JOIN ourpension.application_termination_contract atc ON atc.id = td.application_termination_contract_id
LEFT JOIN ourpension.application_personal_data_individual apdi ON apdi.id = atc.application_personal_data_individual_id
LEFT JOIN ourpension.application_termination_contract_data atcd ON atcd.application_termination_contract_id = atc.id
LEFT JOIN ourpension.individual ind ON ind.id = apdi.individual_id
LEFT JOIN ourpension.sharer sh ON sh.id = atcd.sharer_id
LEFT JOIN ourpension.contract contract ON contract.id = sh.contract_id
LEFT JOIN ourpension.service_app_type sat ON atc.service_app_type_code = sat.code
LEFT JOIN ourpension.agent a ON td.agent_id = a.id
LEFT JOIN ourpension.information_source info ON info.id = atc.information_source_id
left join ourpension.entity e on e.id = contract.entity_id
CROSS JOIN tessa
WHERE td.type != 'NONE'
and not exists (
    select 1
    from ourpension.year_tax_deduction td2
    left join ourpension.application_termination_contract atc2
        on atc2.id = td2.application_termination_contract_id
    left join ourpension.application_termination_contract_data atcd2
        on atcd2.application_termination_contract_id = atc2.id
    left join ourpension.sharer sh2
        on sh2.id = atcd2.sharer_id
    left join ourpension.contract contract2
        on contract2.id = sh2.contract_id
    where td2.type != 'NONE'
     and contract2.number = contract.number
     and (
         td2.year > td.year
         or (
             td2.year = td.year
             and td2.id > td.id
         )
     )
)