---

WITH tessa (path) AS (
  SELECT value 
  FROM tools.npf360_settings 
  WHERE "name" = 'TESSA_API'
),
fj_last AS (
  SELECT DISTINCT ON (contract_number)
         contract_number,
         model_file_to_verification_id,
         smevRegisterId
  FROM report.fns_reference_request_journal
  ORDER BY contract_number, request_date DESC
),
result AS (
  SELECT
	td.canceled as actual,
  --  case 
      --when contract.entity_id is null then 
        '<a target="_blank" href="view?viewCode=Client_list&id=' || contract.individual_id || '">' 
        || ind.last_name || ' ' || ind.first_name || coalesce(' ' || ind.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 uat.activity_eng_type
      when 'NPO' then 'НПО'
      when 'OPS' then 'ОПС'
      when 'PDS' then 'ПДС'
    end as contract_activity_type, */
		uat.activity_rus_type 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,
    '<a target="_blank" href="view?viewCode=Pzbmp8Pg&id=' || sh.id || '">' || sh.number || '</a>' as account_number_link,
sh.number as sh_number,
	sh.id as sh_id,

    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,

'<a target="_blank" href="/ourpension/view?viewCode=7K6FKG8e&id=' || fj.smevRegisterId || '">' || sr.number || '</a>' as request_number_link,
	td.tessa_card_id as tessa_id_pure,
	contract.id as contract_id

  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 LATERAL (
    SELECT *
    FROM ourpension.application_termination_contract_data
    WHERE application_termination_contract_id = atc.id
    ORDER BY id DESC   
    LIMIT 1
) atcd ON TRUE

  LEFT JOIN ourpension.individual ind 
    ON ind.id = coalesce(td.individual_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.uv_activity_type uat 
			 on uat.activity_eng_type = contract.service_type 
			and uat.client_type = contract.client_type 
  --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


LEFT JOIN fj_last fj ON fj.contract_number = contract.number


	 LEFT JOIN dev.model_file_to_verification mfc 
        ON mfc.id = fj.model_file_to_verification_id
    LEFT JOIN report.smev_register sr 
        ON sr.id = mfc.register_id
  CROSS JOIN tessa

  WHERE td.type != 'NONE'
  
    ORDER BY 
    td.canceled IS NOT NULL,
    td.version DESC
)
SELECT
  case 
      when r.actual is null then '<span class="glyphicon glyphicon-ok" style="color: green; align-items: center; justify-content: center;"></span>'
      when r.actual is not null then '<span class="glyphicon glyphicon-remove" style="color: red;  align-items: center; justify-content: center;"></span>' 
      else 'не определено>'
    end as actual,
  r.client_full_name as client_full_name,
  r.document_number as document_number,
  r.year as year,
  r.created_at as created_at,
  r.certificate_date as certificate_date,
  r.contract_activity_type as contract_activity_type,
  r.contract_number as contract_number,
  r.contract_number_link as contract_number_link,
  r.contract_date as contract_date,
  r.account_number_link as account_number,
  r.created_by_username as created_by_username,
  r.receive_method as receive_method,
  r.application_id as application_id,
  r.comment_text as comment_text,
  r.atc_number as atc_number,
  r.atc_id as atc_id,
  r.i_id as i_id,
  r.tessa_card_id as tessa_card_id,
  r.request_number_link as request_number_link,
  r.full_fl_name as full_fl_name,
  r.sh_number as sh_number,
  r.sh_id as sh_id,
  r.accepted as accepted,
  r.canceled as canceled,
  r.id as id,
  r.tessa_id_pure as tessa_id_pure,
  r.contract_id as contract_id 
FROM result r






SELECT x.*
FROM ourpension.year_tax_deduction x
join ourpension.application_termination_contract at on x.application_termination_contract_id = at.id
join ourpension.sharer s on at.sharer_id = s.id
join ourpension.contract c on s.contract_id = c.id
where c."service_type" = N'PDS'
and x."type" <> 'NONE'