---

WITH tessa (path) AS (
  SELECT value 
  FROM tools.npf360_settings 
  WHERE "name" = 'TESSA_API'
),
result 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,

    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,

    td.number as document_number,
    td.year as year,
    to_char(td.version, 'DD.MM.YYYY') as created_at,
    to_char(td.date, 'DD.MM.YYYY') as certificate_date,

    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,

    to_char(contract.date, 'DD.MM.YYYY') as contract_date,
    sh.number as account_number,

    a.last_name || ' ' || a.first_name || ' ' || a.middle_name as created_by_username,

    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 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,
    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" != "")
    and td.version < to_date(:created_at_to, 'YYYY-MM-DD') + interval '1 day'
  #end

  ORDER BY 
    td.canceled IS NOT NULL,
    td.version DESC
)
SELECT *
FROM result