---

WITH sub AS (
 SELECT DISTINCT
   contract.number as contract_number,
   sh.number as sh_number,
   i.last_name as last_name,
   i.first_name as first_name,
   i.middle_name as middle_name,
   to_char(i.birth_date, 'DD.MM.YYYY') as birth_date,
   i.insurance_number as insurance_number,
   i.certificate_series as certificate_series,
   i.certificate_number as certificate_number,
   case 
     when i.sex = 'F' then 'Ж'
     else 'М'
   end as sex,
   i.id as id 
 FROM ourpension.application_termination_contract atc 
 left join ourpension.application_termination_contract_data atcd 
   on atcd.application_termination_contract_id = atc.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.individual i 
   on sh.individual_id = i.id
 left join ourpension.uv_activity_type uat 
   on uat.activity_eng_type = contract.service_type 
   and uat.client_type = contract.client_type
 WHERE atc.id is not null 
   and (uat.activity_rus_type = 'ПДС' or uat.activity_rus_type = 'НПО')
   and 1=1
)
SELECT
 contract_number,
 sh_number,
 last_name,
 first_name,
 middle_name,
 birth_date,
 insurance_number,
 certificate_series,
 certificate_number,
 sex,
 id
FROM sub
WHERE 1=1
#if  
  (("$!fl_first_name" == "") 
   && ("$!fl_last_name" == "")
   && ("$!fl_middle_name" == "")
   && ("$!cert_number" == "")
   && ("$!cert_series" == "")
   && ("$!insurance_number_fl" == "")
   && ("$!fl_contract_number" == "")
   && ("$!fl_birth_date" == "")
   && ("$!fl_sh_number" == ""))
 and 1 = 0
#end

#if ("$!fl_first_name" != "")
   and first_name ILIKE '%' || :fl_first_name || '%'
#end

#if ("$!fl_last_name" != "")
   and last_name ILIKE '%' || :fl_last_name || '%'
#end

#if ("$!fl_middle_name" != "")
   and middle_name ILIKE '%' || :fl_middle_name || '%'
#end

#if ("$!cert_number" != "")
   and certificate_number ILIKE '%' || :cert_number || '%'
#end

#if ("$!cert_series" != "")
   and certificate_series ILIKE '%' || :cert_series || '%'
#end

#if ("$!insurance_number_fl" != "")
   and insurance_number ILIKE '%' || :insurance_number_fl || '%'
#end

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

#if ("$!fl_birth_date" != "")
   and birth_date = :fl_birth_date
#end 

#if ("$!fl_sh_number" != "")
   and sh_number ILIKE '%' || :fl_sh_number || '%'
#end