---

WITH report_data as (
SELECT
	row_number() over(order by oad.id) as rn,
	im.id as individual_id,
  im.insurance_number as snils,
	c.id as contract_id,
  c.number as contract_number,
  c.date as contract_date,
	f.name_in_pfr,
  concat(im.last_name, ' ', im.first_name, ' ', im.middle_name) as fio_m,
  concat(ip.last_name, ' ', ip.first_name, ' ', ip.middle_name) as fio_p,
  oad.number as number,
  oad.date as date,
  coalesce(oadp.payment_value,0) as payment_value,
  coalesce(b.balance,0) as balance,
  coalesce(bm.balance,0) as balance_rs,
  coalesce(dr.ops,0)+coalesce(dr.ops_id,0) as rops,
  concat(a.last_name, ' ', substr(a.first_name,1,1), '. ', substr(a.middle_name,1,1)||'.') as fio_a 
FROM back_office.ops_assignee_decision oad 
join ourpension.ops_assignee_payment oap
  on oap.id = oad.ops_assignee_payment_id
join ourpension.sharer sh 
  on sh.id = oap.sharer_id
join ourpension.contract c 
  on c.id = sh.contract_id
left join ourpension.fund f
  on f.id = c.fund_id
join ourpension.individual im
  on im.id = sh.individual_id 
join ourpension.individual ip
  on ip.id = oap.assignee_id
join back_office.ops_assignee_decision_payment oadp
  on oadp.ops_assignee_decision_id = oad.id
join ourpension.agent a 
  on a.id = oad.agent_id
left join report.dia_rops dr
       on dr.dia_register_id = :dia_register_id
			and dr.insurance_number = im.insurance_number
left join lateral (
	   select a.owner_sharer_id , sum(b.total_credit)- sum(b.total_debit) as balance
     from  back_office."account" a 
     join back_office.balance b 
       on b.account_id = a.id
     where current_date between b.start_date and b.end_date 
       and a.owner_sharer_id = sh.id
     group by a.owner_sharer_id
) b on true
left join lateral (
	   select a.owner_sharer_id , sum(b.total_credit)- sum(b.total_debit) as balance
     from  back_office."account" a
	   join back_office.account_type act
       on act.id = a.account_type_id
     join back_office.account_type_group g
       on g.id = act.account_type_group_id
     join back_office.balance b 
       on b.account_id = a.id
     where current_date between b.start_date and b.end_date
	     and g.mnemonics = 'Р(С)'
       and a.owner_sharer_id = sh.id
     group by a.owner_sharer_id
) bm on true
WHERE :isShow
	and oad.accepted is not null
)
SELECT
  r.rn as rn,
  concat('<a target="_blank" href="view?viewCode=Client_list&id=', r.individual_id, '">', r.snils, '</a>') as snils,
  concat('<a target="_blank" href="view?viewCode=7HPZX8vF&id=', r.contract_id, '">', r.contract_number, '</a>') as contract_number,
  to_char(r.contract_date, 'DD.MM.YYYY') as contract_date,
  r.name_in_pfr as name_in_pfr,
  r.fio_m as fio_m,
  r.fio_p as fio_p,
  r.number as number,
  to_char(r.date, 'DD.MM.YYYY') as date,
  trim(to_char(coalesce(r.payment_value, 0), '99 999 999 999 999 990D99')) as payment_value,
  trim(to_char(coalesce(r.payment_value, 0), '99 999 999 999 999 990D99')) as payment_val,
  trim(to_char(coalesce(r.balance, 0), '99 999 999 999 999 990D99')) as balance,
  trim(to_char(coalesce(r.balance_rs, 0), '99 999 999 999 999 990D99')) as balance_rs,
  trim(to_char(coalesce(r.rops,0), '99 999 999 999 999 990D99')) as rops,
  trim(to_char(r.balance+r.balance_rs+r.rops - r.payment_value, '99 999 999 999 999 990D99')) as total,
  r.fio_a as fio_a 
FROM report_data r