---

WITH cte_s_act as
(
    SELECT 
        csh.contract_id,
        MIN(csh.status_date) AS status_date
    FROM ourpension.contract_statuses_history csh
    WHERE csh.code IN ( 'CA0', 'HA0', 'MA0' )
    GROUP BY csh.contract_id
)
SELECT
  ct.id as id,
  ct.id as contractId,
  '<a target="_blank" href="view?viewCode=7HPZX8vF&id=' || ct.id|| '"target="_blank">' || ct.number || '</a>' as number,
  to_char(ct.date, 'DD.MM.YYYY') as date,
  case 
  when p.lifetime_pension='true' 
   then '<span data-toggle="tooltip" title="' || spl.scheme_number || '" class="label label-success">' || spl.scheme_number || '</span>'
   else '<span data-toggle="tooltip" title="' || spl.scheme_number || '" class="label label-primary">' || spl.scheme_number || '</span>'
end as schemeNumber,
  '<span data-toggle="tooltip" title="' || coalesce(uat.activity_name, 'ОПС') || '" class="label label-success">' || coalesce(uat.activity_rus_type, 'ОПС') || '</span>' as clientType,
  case 
  when ct.entity_id is null
    then '<a target="_blank" href="view?viewCode=Client_list&id=' || ct.individual_id || '">' || i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, '') || '</a>'
  else '<a target="_blank" href="view?viewCode=NCIuIMAW&id=' || ct.entity_id || '">' || e.short_name || '</a>'
end as investor,
  '<span data-toggle="tooltip" title="' || to_char(
  case when t.termination_date is not null or ct.close_date is not null
  then coalesce(t.termination_date, ct.close_date)
  else ct.date
  end, 'DD.MM.YYYY') || '" class="label label-primary">' || cs.name || '</span>' as contractLifecycle,
  coalesce(a.last_name,'') || ' ' || left(coalesce(a.first_name, ''), 1) || '.' || coalesce(left(a.middle_name, 1) || '.', '') as agent,
  case
 when ct.service_type = 'PDS' then 'ДДС'
 when ct.service_type = 'NPO' and ct.individual_id is not null then 'ИПП'
 when ct.service_type = 'NPO' and ct.entity_id is not null then 'КПП'
 else ''
end as contractType,
  pp3."name" as program,
  pp2."name" as product,
  ct.number as contractNumber,
  cr.name as closeReasone,
  to_char(t.termination_date, 'DD.MM.YYYY') as terminationDate,
  tr.name as terminationReason,
  to_char(ct.create_date, 'DD.MM.YYYY') as createDate,
  to_char(coalesce(ct.close_date,t.termination_date,ct.date), 'dd.MM.yyyy') as state_date,
  pp3."name" || coalesce('(' || pp2."name" || ')','') as programProduct,
  cs.name as state,
  ct.client_type as clientTypeId,
  coalesce(uat.activity_rus_type, 'ОПС') as serviceType,
  sc.name as saleChannelName,
  fe.name as fund_name,
  i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, '') as investor_fio,
  death_info.id as death_info_id,
  ct.service_type as service_type,
  fsg.name_in_pfr as fund_signed,
  fsr.name_in_pfr as fund_resource 
FROM ourpension.contract ct
inner join ourpension.current_contract_status(ct.id) ccs
        on true
inner join ourpension.contract_statuses cs 
        on cs.code = ccs.c_code
inner join ourpension.agent a 
        on a.id = ct.agent_id
	




left join ourpension.uv_activity_type uat 
			 on uat.activity_eng_type = ct.service_type 
			and uat.client_type = ct.client_type 
left join lateral (
		 select contract_id, termination_reason_id, termination_date
 		 from (                 
           select tc.contract_id,
                  t.contract_termination_reason_id as termination_reason_id,
                  t.date as termination_date,
                  row_number() over (partition by tc.contract_id order by t.date, t.version desc) num
           from ourpension.termination_contract tc
           join ourpension.termination t 
						 on tc.termination_id = t.id
	         where tc.contract_id = ct.id
					) tt where tt.num = 1
        ) t on true
left join ourpension.entity e 
			 on e.id = ct.entity_id
left join ourpension.individual i 
			 on i.id = ct.individual_id
left join ourpension.pension_scheme p 
			 on p.id = ct.scheme_id
left join ourpension.contract_close_reason cr 
			 on cr.id = ct.contract_close_reason_id
left join ourpension.contract_termination_reason tr 
			 on  tr.id = t.termination_reason_id
left join ourpension.information_source sc 
			 on sc.id = ct.sale_channel_id
left join ourpension.fund_branch fb 
			 on fb.id = a.fund_branch_id 
left join ourpension.fund f 
			 on f.id = fb.fund_id
left join ourpension.entity fe 
			 on fe.id = f.entity_id 
left join ourpension.contract_params cpp 
			 on cpp.contract_id = ct.id 
left join prod_line.pens_product pp3 
			 on pp3.id = ct.product_id
left join prod_line.table_param_pl_pens_program tpp 
			 on tpp.pens_program_id = pp3.id
left join prod_line.pens_product_param_pl pppp 
			 on pppp.id = tpp.pens_product_param_pl_id
left join prod_line.pens_product pp2 
			on pp2.id = pppp.pens_product_id
LEFT JOIN ourpension.fund fsg ON fsg.id = ct.sign_fund_id    -- Заключивший фонд
LEFT JOIN ourpension.fund fsr ON fsr.id = ct.fund_id          -- Фонд-источник
LEFT JOIN LATERAL (

	SELECT t.id, t."number", t.death_date 
	FROM ourpension.application_death_info t
	JOIN ourpension.individual i on i.id = t.individual_id
	JOIN ourpension.agent a on a.id = t.agent_id
	JOIN ourpension.death_info_certificate dc on dc.code = t.death_info_certificate_code
	JOIN ourpension.contract_statuses_history history ON history.contract_id = ct.id
	WHERE t.accepted IS NOT NULL
		AND t.canceled IS NULL
		AND t.rejected IS NULL	
		AND t.individual_id = ct.individual_id
		AND history.code = 'CA0'
		AND t.death_date <= history.status_date
		AND ct."service_type" = 'OPS'
																						 
) death_info ON true
LEFT JOIN LATERAL 
(   SELECT 
        products.id, 
        products.pens_scheme_accum_stage_id as scheme_id,
        ps.brief as scheme_number
    FROM ourpension.contract_product_history history
        INNER JOIN prod_line.pens_product products 
        ON products.id = history.pens_product_id
        LEFT JOIN prod_line.pens_scheme ps
        ON ps.id = products.pens_scheme_accum_stage_id
    WHERE history.contract_id = ct.id
        AND CURRENT_TIMESTAMP BETWEEN 
            history.begin_date AND COALESCE( history.end_date, 'infinity' )
    LIMIT 1
) spl
ON true
WHERE 
#if ($filterShowList)
  true
#else
  false
#end

#if ( $id != -1 ) 
	and ct.id = :id
#end

#if ($filterNumber && !$filterNumber.isEmpty())
	and ct.number ilike replace(replace(trim(:filterNumber), '\', '\\'), '/', '\/') || '%'
#end

#if ($filterPensionScheme && !$filterPensionScheme.isEmpty())
	and p.number ilike trim(:filterPensionScheme) || '%'
#end

#if ($filterStartDateContract && !$filterStartDateContract.isEmpty())
	and ct.date >= :filterStartDateContract
#end

#if ($filterEndDateContract && !$filterEndDateContract.isEmpty())
	and ct.date <= :filterEndDateContract
#end

#if ($filterInsuranceNumber && !$filterInsuranceNumber.isEmpty())
	and i.insurance_number like trim(:filterInsuranceNumber) || '%'
#end

#if ($filterInvestorLastName && !$filterInvestorLastName.isEmpty())
	and i.last_name ilike trim(:filterInvestorLastName) || '%'
#end

#if ($filterInvestorShortName && !$filterInvestorShortName.isEmpty())
	and e.short_name ilike '%' || trim(:filterInvestorShortName) || '%'
#end

#if ($filterInvestorFirstName && !$filterInvestorFirstName.isEmpty())
	and i.first_name ilike trim(:filterInvestorFirstName) || '%'
#end

#if ($filterInvestorMiddleName && !$filterInvestorMiddleName.isEmpty())
	and i.middle_name ilike trim(:filterInvestorMiddleName) || '%'
#end

#if ($filterInn && !$filterInn.isEmpty())
	and (i.inn like trim(:filterInn) || '%' or e.inn like trim(:filterInn) || '%')
#end

#if ($filterServiceType && !$filterServiceType.isEmpty())
	and (coalesce(ct.service_type, 'OPS') = :filterServiceType)
	###and (coalesce(uat.activity_rus_type, 'ОПС') = :filterServiceType)
#end

#if ($filterInvestorFullName && !$filterInvestorFullName.isEmpty())
	and concat(i.last_name,' ',i.first_name, ' ',i.middle_name) ilike '%' || trim(:filterInvestorFullName) || '%'
#end    

#if ($filterStatus && !$filterStatus.isEmpty())
	and cs.name ilike trim(:filterStatus) || '%'
#end

#if ($filterProgram && !$filterProgram.isEmpty())
	and pp3.name ilike trim(:filterProgram) || '%'
#end

#if ($filterProduct && !$filterProduct.isEmpty())
	and pp2.name ilike trim(:filterProduct) || '%'
#end


#if ($filterContractType && !$filterContractType.isEmpty())
  /*and ( :filterStatus = 4 and t.termination_date is not null and sh.close_date is null and ct.close_date is null --прекращен
			or :filterStatus = 3 and (sh.close_date is not null or ct.close_date is not null) --закрыт
			or :filterStatus = 2 and t.termination_date is null and sh.open_date is not null and sh.close_date is null and ct.close_date is null --действующий
			or :filterStatus = 1 and sh.open_date is null	
			 and t.termination_date is null and sh.close_date is null and ct.close_date is null
			 --Заключен
		 )*/
		and ( :filterContractType = 3 and ct.service_type = 'PDS' --ДДС
				 or :filterContractType = 2 and ct.service_type = 'NPO' and ct.entity_id is not null --КПП
				 or :filterContractType = 1 and ct.service_type = 'NPO' and ct.individual_id is not null --ИПП 
				 or :filterContractType = 0 and	ct.service_type = 'OPS' 
		 ) 
#end
																			
#if ($filterNonstandardContract && !$filterNonstandardReason.isEmpty())
	and ct.nonstandard_reason_id = :filterNonstandardReason
#end