---

WITH tessa (path) AS (
    SELECT value FROM tools.npf360_settings WHERE "name" = 'TESSA_API'
),
data AS (
    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 1=1
    #if ( ("$!contract_number" == "")
        && ("$!client_full_name" == "")
        && ("$!contract_activity_type" == "")
        && ("$!account_number" == "")
        && ("$!created_at_from" == "")
        && ("$!created_at_to" == "") )
       and 1 = 0
    #end

    #if ("$!contract_number" != "")
        and contract.number ILIKE '%' || replace(:contract_number, chr(92), chr(92) || chr(92)) || '%' ESCAPE chr(92)
    #end

    #if ("$!client_full_name" != "")
        and apdi.last_name || ' ' || apdi.first_name || ' ' || apdi.middle_name ILIKE '%' || :client_full_name || '%'
    #end

    #if ("$!contract_activity_type" != "")
        and sat.service_type = cast(:contract_activity_type as ourpension.service_type)
    #end

    #if ("$!account_number" != "")
        and sh.number ILIKE '%' || :account_number || '%'
    #end

    #if ("$!created_at_from" != "")
        and td.version >= to_date(:created_at_from, 'YYYY-MM-DD')
    #end

    #if ("$!created_at_to" != "")
        -- +1 день, чтобы дата "по" была включительно
        and td.version < to_date(:created_at_to, 'YYYY-MM-DD') + interval '1 day'
    #end

    GROUP BY
        td.canceled,
        td.version,
        td.date,
        td.number,
        contract.entity_id,
        contract.individual_id,
        contract.id,
        contract.number,
        contract.date,
        apdi.last_name,
        apdi.first_name,
        apdi.middle_name,
        e.short_name,
        sat.service_type,
        sh.number,
        info.name,
        a.last_name,
        a.first_name,
        a.middle_name,
        atc.id,
        atc.number,
        td.comment,
        ind.id,
        tessa.path,
        td.tessa_card_id,
        td.accepted,
        td.id
)
SELECT * FROM data