---

WITH sub AS (
 SELECT DISTINCT
   contract.number AS contract_number,
   sh.number AS sh_number,
   i.last_name,
   i.first_name,
   i.middle_name,
  i.birth_date,
   i.insurance_number,
   i.certificate_series,
   i.certificate_number,
   case 
     when i.sex = 'F' then 'Ж'
     else 'М'
   end AS sex,
   i.id,
	atc.number,
	atc.date
 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 = coalesce(atcd.sharer_id, atc.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 = 'НПО')
)
SELECT
  sub.contract_number as contract_number,
  sub.sh_number as sh_number,
  sub.last_name as last_name,
  sub.first_name as first_name,
  sub.middle_name as middle_name,
  to_char(sub.birth_date, 'DD.MM.YYYY') as birth_date,
  sub.insurance_number as insurance_number,
  sub.certificate_series as certificate_series,
  sub.certificate_number as certificate_number,
  sub.sex as sex,
  sub.id as id,
  sub.number as atc_number,
  to_char(sub.date, 'MM.DD.YYYY') as sub_date 
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 sub.first_name ILIKE '%' || :fl_first_name || '%'
#end

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

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

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

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

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

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

#if ("$!fl_birth_date" != "")
   and sub.birth_date = to_date(:fl_birth_date, 'DD.MM.YYYY')
#end 

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