---

WITH latest_journal_ids AS (
    SELECT
        model_file_to_verification_id,
        MAX(id) AS max_id
    FROM report.fns_reference_request_journal
    WHERE model_file_to_verification_id IS NOT NULL
    GROUP BY model_file_to_verification_id
),
ranked AS (
    SELECT
	coalesce(fj.activity_type, contract.service_type) AS activity_type_code,
	contract.service_type as service_type_filter,
	fj.last_name,
	fj.first_name,
	fj.middle_name,
	fj.contract_number as contract_number_filter,
	contract.number as contract_number_snv,
        fj.smevRegisterId,
        fj.id AS id,
        sr.number AS request_number,
        fj.request_date AS request_date,
        fj.snils AS snils,
        fj.fio AS fj_fio,
       -- fj.contract_number AS contract_number,
	case coalesce(fj.activity_type, contract.service_type)
when 'NPO' then 'НПО'
when 'OPS' then 'ОПС'
when'PDS' then 'ПДС'
end as activity_type,
        --fj.activity_type AS activity_type,
        fj.is_sent_to_fns AS is_sent_to_fns,
        fj.response_received AS response_received,
        fj.certificate_link AS certificate_link,
        fj.model_file_to_verification_id AS model_file_to_verification_id,
        mfc.name AS file_name,
	coalesce(sr.agent_last_name, '') || 
coalesce(' ' || left(sr.agent_first_name, 1) || '.', '') || coalesce(' ' || left(sr.agent_middle_name, 1) || '.', '') as username,
	case 
  when contract.entity_id is null
    then '<a target="_blank" href="view?viewCode=Client_list&id=' || contract.individual_id || '">' || i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, '') || '</a>'
  else '<a target="_blank" href="view?viewCode=NCIuIMAW&id=' || contract.entity_id || '">' || e.short_name || '</a>'
end as fio,
	'<a target="_blank" href="view?viewCode=7HPZX8vF&id='||contract.id||'">'||fj.contract_number||'</a>' as contract_number
    FROM latest_journal_ids lj
    INNER JOIN report.fns_reference_request_journal fj
        ON fj.id = lj.max_id
    INNER JOIN dev.model_file_to_verification mfc
        ON mfc.id = fj.model_file_to_verification_id
    INNER JOIN report.smev_register sr
        ON sr.id = mfc.register_id
	left join ourpension.contract contract on contract.id = fj.contract_id
left join ourpension.individual i on i.id = contract.individual_id
left join ourpension.entity e 
			 on e.id = contract.entity_id
	 WHERE 1 = 1
        )
SELECT
  case when s.row_id is not null then '<span class="glyphicon glyphicon-ok" aria-hidden="true"></span>' end as selected,
  r.id as id,
  r.request_number as request_number,
  to_char(r.request_date, 'DD.MM.YYYY') as request_date,
  r.snils as snils,
  r.fj_fio as fj_fio,
  r.contract_number as contract_number,
  r.activity_type as activity_type,
  case when r.is_sent_to_fns then '<span class="glyphicon glyphicon-ok" style="color: green; align-items: center; justify-content: center;"></span>'
    when not r.is_sent_to_fns then  '<span class="glyphicon glyphicon-remove" style="color: red;  align-items: center; justify-content: center;"></span>' 
    else'не определено>'
end as is_sent_to_fns,
  case when r.response_received then '<span class="glyphicon glyphicon-ok" style="color: green; align-items: center; justify-content: center;"></span>'
    when not r.response_received then  '<span class="glyphicon glyphicon-remove" style="color: red;  align-items: center; justify-content: center;"></span>' 
    else'не определено>'
end as response_received,
  r.certificate_link as certificate_link,
  case when  r.model_file_to_verification_id is not null then
    '<a target="_blank" href="view?viewCode=9hUIiHYU&ids=' || r.model_file_to_verification_id ||'">	
    <span class="glyphicon glyphicon-book" aria-hidden="true"></span>' || ' ' || r.model_file_to_verification_id || '</a>'
else
    ' '
end as model_file_to_verification_id,
  '<a target="_blank" href="modelFileToVerification/modelFileToVerification/downloadSource?id='|| r.model_file_to_verification_id ||'" ||>	
<span class="glyphicon glyphicon-file" aria-hidden="true"></span>' || r.file_name || '</a>' as file_name,
  r.model_file_to_verification_id as model_file_id,
  r.smevRegisterId as smevRegisterId,
  r.is_sent_to_fns as is_sent_to_fns_p,
  r.username as username,
  r.fio as fio,
  '<a target="_blank" href="view?viewCode=n3mSokEw&contract_number=' || REPLACE(contract_number_snv, '\', '%5C')|| '">Открыть</a>' as link_snv 
FROM ranked r
left join tools.selection s on s.row_id = r.id 	and s.username = :username
		and s.code = :query_table_code
WHERE  
#if ($filterShowList || $id || $flk_id)
    true
#else
    false
#end

AND (r.service_type_filter = 'NPO' OR r.service_type_filter = 'PDS')
--AND r.request_number = :sr_number

#if ($startDate && !$startDate.isEmpty())
    AND (cast(:startDate as Date) IS NULL
         OR r.request_date >= cast(:startDate as Date))
#end

#if ($endDate && !$endDate.isEmpty())
    AND (cast(:endDate as Date) IS NULL
         OR r.request_date <= cast(:endDate as Date))
#end

#if ($Number && !$Number.isEmpty())
    AND r.request_number LIKE :Number
#end

#if ($filterInsuranceNumber && !$filterInsuranceNumber.isEmpty())
    AND r.snils = :filterInsuranceNumber
#end

#if ($filterLastName && !$filterLastName.isEmpty())
    AND r.last_name ILIKE '%' || :filterLastName || '%'
#end

#if ($filterFirstName && !$filterFirstName.isEmpty())
    AND r.first_name ILIKE '%' || :filterFirstName || '%'
#end

#if ($filterMiddleName && !$filterMiddleName.isEmpty())
    AND r.middle_name ILIKE '%' || :filterMiddleName || '%'
#end

#if ($filterContractNumber && !$filterContractNumber.isEmpty())
    AND r.contract_number_filter ILIKE '%' || :filterContractNumber || '%'
#end

AND (
    :filterActivityType IS NULL
    OR r.activity_type_code = :filterActivityType
)

#if ($filterResponseReceived && !$filterResponseReceived.isEmpty())
    AND COALESCE(r.response_received, false)
        = CAST(:filterResponseReceived AS boolean)
#end

#if ($filterIsSentToFns && !$filterIsSentToFns.isEmpty())
    AND COALESCE(r.is_sent_to_fns, false)
        = CAST(:filterIsSentToFns AS boolean)
#end