---

WITH pension_value (ops_payment_decision_id, pension_value) as (
	select dl.ops_payment_decision_id, dl.pension_value
		from back_office.ops_payment_decision_lifetime dl
	union all 
	select dla.ops_payment_decision_id, dla.pension_value
		from back_office.ops_payment_decision_lifetime_all dla
	union all 
	select ft.ops_payment_decision_id, ft.pension_value
		from back_office.ops_payment_decision_fixed_term ft
	union all 
	select o.ops_payment_decision_id, o.payment_value pension_value
		from back_office.ops_payment_decision_onetime o 
)
SELECT
  '<input name="selected" type="checkbox" value="' || CAST(t.id as varchar) || '" ' || case when s.row_id is not NULL then 'checked' else '' end || ' />' as selected,
  t.id as id,
  case
when t.decision_type_code in ('УСТН', 'ОТКЗ') and t.parent_ops_payment_decision_id is not null then true
else false
end as revaluation,
  sh.id as sharerId,
  i.id as individualId,
  trim(to_char(ROUND(v.k_value, 2), '99 999 999 999 999 990D99')) as kk,
  t.number as number,
  to_char(t.date, 'dd.MM.yyyy') as date,
  to_char(t.calc_date, 'dd.MM.yyyy') as calcDate,
  to_char(t.accepted, 'dd.MM.yyyy') as acceptedDate,
  i.insurance_number as insuranceNumber,
  '<a target="_blank" href="view?viewCode=Client_list&id='||i.id||'">'||rtrim(i.last_name || ' ' || i.first_name || ' ' || coalesce(i.middle_name, ''))||'</a>' as client,
  sh.number as sharerNumber,
  '<a target="_blank" href="view?viewCode=7HPZX8vF&id='||c.id||'"target="_blank">'||c.number||'</a>' as contractNumber,
  '<a href="/ourpension/view?viewCode=Pzbmp8Pg&id=' || sh.id|| '" target="_blank">' || sh.number || '</a>' as sharerN,
  dpt.group_code as decisionPaymentTypeCd,
  case
when t.decision_type_code in ('УСТН', 'ОТКЗ') and t.parent_ops_payment_decision_id is not null then dpt.group_code||' (Переоценка)'
else dpt.group_code
end as decisionPaymentTypeCdVisible,
  trim(to_char(coalesce(pv.pension_value, pens_v.payment_val), '99 999 999 999 999 990D99')) as pensionValue,
  case 
  when t.rejected is not null
    then '<span data-toggle="tooltip" title="' || to_char(t.rejected, 'DD.MM.YYYY') || '" class="label label-danger">Удалено</span><br>'
  when t.canceled is not null then 
    case 
      when exists(select 1 from back_office.ops_payment_decision where parent_ops_payment_decision_id = t.id and decision_type_code in ('УСТН', 'ОТКЗ'))
        then '<span data-toggle="tooltip" title="' || to_char(t.canceled, 'DD.MM.YYYY') || '" class="label label-default">Отменено</span>'
        else '<span data-toggle="tooltip" title="' || to_char(t.canceled, 'DD.MM.YYYY') || '" class="label label-default">Аннулирован</span>'
    end
  when t.accepted is not null
    then '<span data-toggle="tooltip" title="' || to_char(t.accepted, 'DD.MM.YYYY') || '" class="label label-primary">Принято</span><br>'
  when t.canceled is null and t.accepted is null and t.rejected is null 
    then '<span data-toggle="tooltip" title=" " class="label label-default">Сформировано</span><br>'
  when t.completed is not null and t.rejected is null and t.canceled is null
    then '<span data-toggle="tooltip" title=" " class="label label-primary">Исполнено</span><br>'
else ''
end as Lifecycle,
  t.decision_type_code as decisionTypeCode,
  COALESCE(rrt.short_name, s_reason.name, '') as refusalReason,
  '<a target="_blank" href="view?viewCode=PxdvYjrk&id='||pa.id||'"target="_blank">'||pa.number||'</a>' as appNumber,
  to_char(pa.appeal_date, 'dd.MM.yyyy') as appealDate,
  to_char(coalesce(t.pension_date, pa.pension_date), 'DD.MM.YYYY') as pensionDate,
  regexp_replace(dpt.short_name, 'ЕВО|ЕВДМ', 'ЕВ', 'g') as decisionPaymentType,
  a.last_name || ' ' ||substr(a.first_name,1,1) ||'.'||coalesce(substr(a.middle_name,1,1)||'.', '') as agent,
  '<a target="_blank" href="view?viewCode=VyebKYjt&id='||pc.id||'"target="_blank">'||pc.number||'</a>' as pfrCertificateNumber,
  case
  when t.parent_ops_payment_decision_id is not null then '<a target="_blank" href="view?viewCode=LB4xxv3W&id='||rd.id||'"target="_blank">'||rd.number||'</a>'
  else
  '<a target="_blank" href="view?viewCode=LB4xxv3W&id='||coalesce(st.id, rt.id )||'"target="_blank">'||coalesce(st.number, rt.number)||'</a>'
  end as relatedDecision,
  i.id as id_client,
  '<a target="_blank" href="view?viewCode=jHU2emkA&id='||t.ops_valuation_id||'"target="_blank">'||to_char(v.valuation_date, 'dd.MM.yyyy')||'</a>' as opsValuation,
  to_char(i.birth_date, 'DD.MM.YYYY') as birthDate,
  sign.last_name || ' ' ||substr(sign.first_name,1,1) ||'.'||coalesce(substr(sign.middle_name,1,1)||'.', '') as sign_fio,
  t.sign_agent_id as signAgentId,
  c.number as contractNumberText,
  rtrim(i.last_name || ' ' || i.first_name || ' ' || coalesce(i.middle_name, '')) as clientFIO,
  t.decision_payment_type_code as decisionPaymentTypeCode,
  t.comment as comment,
  s_reason.name as suspenseReason,
  to_char(date_trunc('month', ps.suspension_date), 'dd.MM.yyyy') as suspensionDate,
  to_char(date_trunc('month', pr.renewal_date), 'dd.MM.yyyy') as renewalDate,
  t.ops_fixing_id as fixingId,
  /*dt.decision_type*/ dpt.name as decisionTypeName,
  pt.short_name as appPaymentType,
  pa.ops_payment_type_code as appPaymentTypeCode,
  dt.name as decisionType,
  coalesce( dpt.group_code, pt.pfr_code) as decisionPaymentTypeGroupCode,
  pt.pfr_code as appPaymentTypePFRCode,
  case 
  	when t.expiration is null and t.decision_type_code = 'ПРИОСТ' then  to_char(pa.accepted + INTERVAL '3 months', 'dd.MM.yyyy')
  	else to_char(t.expiration, 'dd.MM.yyyy')
  end as expiration,
  exists ( 
  select 1 
  from back_office.ops_payment_decision_refusal_reason o 
  where o.ops_payment_decision_id = t.id 
    and o.refusal_reason ilike '%не ранее чем через пять лет со дня предыдущего обращения%' 
  limit 1 
) as ev_less_5_years,
  ourpension.get_ops_decisions_tessa_card_links(t.id, 'НП') as card_links,
  to_char(pa.registration, 'dd.MM.yyyy') as registrationDate 
FROM back_office.ops_payment_decision t	
	join ourpension.ops_pension_assignment_application pa on pa.id = t.ops_pension_assignment_application_id /*Заявления*/
	left join ourpension.pfr_certificate pc on pc.id = t.pfr_certificate_id /*Справка ПФР*/
	join ourpension.sharer sh on sh.id = pa.sharer_id /*Счета*/
	join ourpension.contract c on c.id = sh.contract_id /*Договоры*/
	join ourpension.individual i on i.id = c.individual_id /*ЗЛ*/
	left join ourpension.agent a on a.id = t.agent_id /*Сотрудкник*/
	join ourpension.ops_payment_type pt on pt.code = pa.ops_payment_type_code
	left join ourpension.decision_payment_type dpt on dpt.code = case 
																															when t.decision_payment_type_code = '00' then 
																																case
																																	when pa.ops_payment_type_code = ('01') then -- EV
																																		'01'
																																	when pa.ops_payment_type_code = ('05') then -- SPV
																																		'02'
																																	when pa.ops_payment_type_code = ('02') then -- NP
																																		'03'
																																	when pa.ops_payment_type_code = ('04') then -- NP
																																		'04'
																																end
																																
 
																															else  
																																t.decision_payment_type_code 
																														 end
	left join back_office.ops_decision_type dt on dt.code = t.decision_type_code
	left join back_office.ops_payment_decision_suspension ps on ps.ops_payment_suspense_decision_id = t.id
  left join back_office.ops_payment_decision_renewal pr on pr.ops_payment_renewal_decision_id = t.id
	left join back_office.ops_suspense_reason sr on sr.code = ps.ops_suspense_reason_code
	left join back_office.ops_payment_decision rd on rd.id = t.parent_ops_payment_decision_id
	
	left join back_office.ops_payment_decision st on st.id = ps.ops_payment_decision_id
	left join back_office.ops_payment_decision rt on rt.id = pr.ops_payment_decision_id
	
	left join back_office.ops_valuation v on v.id = t.ops_valuation_id
	left join pension_value pv on t.id = pv.ops_payment_decision_id
	left join ourpension.agent sign on sign.id = t.sign_agent_id
	/*left join reason rr on rr.ops_payment_decision_id = t.id*/
	left join back_office.ops_decision_refusal_reason_type rrt on t.ops_decision_refusal_reason_type_id = rrt.id
	left join ourpension.service_application sa on pa.id = sa.record_id
	
	left join back_office.ops_payment_decision_renewal opdr on opdr.ops_payment_renewal_decision_id = t.id
	left join back_office.ops_payment_decision_suspension opds on opds.ops_payment_suspense_decision_id = t.id
	left join back_office.suspense_reason s_reason on s_reason.id = opdr.ops_suspense_reason or s_reason.id = opds.ops_suspense_reason
	left join lateral(select payment_val from back_office.fn_gen_pension_value_for_decn_suspend(t.id) where t.decision_type_code in ('ПРОС', 'ВОЗБ', 'ПРЕК', 'ВОСТ')) pens_v on TRUE
	left join tools.selection s on s.row_id = t.id and s.username = :username and s.code = 'rfGg5LSz'
	
	/*из WITH
	pension_value (ops_payment_decision_id, pension_value) as (
select dl.ops_payment_decision_id, dl.pension_value
  from back_office.ops_payment_decision_lifetime dl
union all 
select dla.ops_payment_decision_id, dla.pension_value
  from back_office.ops_payment_decision_lifetime_all dla
union all 
select ft.ops_payment_decision_id, ft.pension_value
  from back_office.ops_payment_decision_fixed_term ft
union all 
select o.ops_payment_decision_id, o.payment_value pension_value
  from back_office.ops_payment_decision_onetime o ))*/
/*,reason as(
	SELECT ops_payment_decision_id ,ARRAY_AGG(refusal_reason) as refusal_reason
	FROM back_office.ops_payment_decision_refusal_reason
	GROUP BY  ops_payment_decision_id
)*/