---

WITH bbb(id) as ( select 1)
--balance(sharer_id, balance, ops, dps_g, dps_l, dps_mk, dps_r) as (
--
--  select
--    s.id,
--    sum(coalesce(b.total_credit - b.total_debit, 0)),
--    sum(case when at.mnemonics in ('ОПС', 'ОПС ГВ', 'ОПС ИД', 'ОПС КП', 'ОПС ФР', 'ГВ', 'КП') 		then coalesce(b.total_credit - b.total_debit, 0) else 0 end),
--    sum(case when at.mnemonics in ('ДПС Г', 'ДПС Г ГВ', 'ДПС Г ИД', 'ДПС Г КП', 'ДПС Г ФР') 			then coalesce(b.total_credit - b.total_debit, 0) else 0 end),
--    sum(case when at.mnemonics in ('ДПС Л', 'ДПС Л ГВ', 'ДПС Л ИД', 'ДПС Л КП', 'ДПС Л ФР')	 			then coalesce(b.total_credit - b.total_debit, 0) else 0 end),
--    sum(case when at.mnemonics in ('ДПС МК', 'ДПС МК ГВ', 'ДПС МК ИД', 'ДПС МК КП', 'ДПС МК ФР') 	then coalesce(b.total_credit - b.total_debit, 0) else 0 end),
--    sum(case when at.mnemonics in ('ДПС Р', 'ДПС Р ГВ', 'ДПС Р ИД', 'ДПС Р КП', 'ДПС Р ФР') 			then coalesce(b.total_credit - b.total_debit, 0) else 0 end)
--  from back_office.balance b
--	  join back_office.account a on b.account_id = a.id
--			join back_office.account_type at on a.account_type_id = at.id	 
--				join back_office.account_type_group atg on at.account_type_group_id = atg.id and atg.mnemonics = 'Н'
--			join ourpension.sharer s on a.owner_sharer_id = s.id
--				join ourpension.contract c on c.id = s.contract_id and c.service_type = 'OPS'
--	where current_date between b.start_date and b.end_date
--  group by s.id
--	
--)
SELECT
  case when s.row_id is not null then '<span class="glyphicon glyphicon-ok" aria-hidden="true"></span>' end as selected,
  sh.id as id,
  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,
  case when i.sex = 'M' 
  then 'М'
  else 'Ж'
end as sex,
  to_char(i.birth_date, 'DD.MM.YYYY') as birthDate,
  c.number as contractNumber,
  to_char(c.date, 'DD.MM.YYYY') as contractDate,
  sh.number as sharerNumber,
  trim(to_char(coalesce(bw.ops, 0), '99 999 999 999 999 990D99')) as ops,
  trim(to_char(coalesce(bw.dps_mk, 0), '99 999 999 999 999 990D99')) as dpsMK,
  trim(to_char(coalesce(bw.dps_l, 0), '99 999 999 999 999 990D99')) as dps_l,
  trim(to_char(coalesce(bw.dps_g, 0), '99 999 999 999 999 990D99')) as dps_g,
  trim(to_char(coalesce(bw.dps_r, 0), '99 999 999 999 999 990D99')) as dps_r,
  f.fund_id as privious_insurer_fund_id,
  fn.name_in_pfr as proper_insurer_name,
  trim(to_char(coalesce(bw.balance, 0), '99 999 999 999 999 990D99')) as balance,
  '<a target="_blank" href="view?viewCode=7FujB1cL&filterCourtDecisionId='|| cd.id ||'"target="_blank">' || f.privious_insurer_source || '</a>' as privious_insurer_source,
  cd.court_decision_number as court_decision_number,
  to_char(cd.court_decision_date, 'dd.MM.yyyy') as court_decision_date,
  to_char(cd.court_effective_date, 'DD.MM.YYYY') as court_effective_date,
  to_char(cd.planned_transfer_date, 'DD.MM.YYYY') as planned_transfer_date 
FROM ourpension.sharer sh
join ourpension.contract c on sh.contract_id = c.id
	join ourpension.individual i on i.id = sh.individual_id
  --left join balance bw on bw.sharer_id = sh.id
	
		left join lateral (
  select
    s.id sharer_id,
    sum(coalesce(b.total_credit - b.total_debit, 0)) balance,
    sum(case when at.mnemonics in ('ОПС', 'ОПС ГВ', 'ОПС ИД', 'ОПС КП', 'ОПС ФР', 'ГВ', 'КП') 		then coalesce(b.total_credit - b.total_debit, 0) else 0 end) ops,
    sum(case when at.mnemonics in ('ДПС Г', 'ДПС Г ГВ', 'ДПС Г ИД', 'ДПС Г КП', 'ДПС Г ФР') 			then coalesce(b.total_credit - b.total_debit, 0) else 0 end) dps_g,
    sum(case when at.mnemonics in ('ДПС Л', 'ДПС Л ГВ', 'ДПС Л ИД', 'ДПС Л КП', 'ДПС Л ФР')	 			then coalesce(b.total_credit - b.total_debit, 0) else 0 end) dps_l,
    sum(case when at.mnemonics in ('ДПС МК', 'ДПС МК ГВ', 'ДПС МК ИД', 'ДПС МК КП', 'ДПС МК ФР') 	then coalesce(b.total_credit - b.total_debit, 0) else 0 end) dps_mk,
    sum(case when at.mnemonics in ('ДПС Р', 'ДПС Р ГВ', 'ДПС Р ИД', 'ДПС Р КП', 'ДПС Р ФР') 			then coalesce(b.total_credit - b.total_debit, 0) else 0 end) dps_r
  from back_office.balance b
	  join back_office.account a on b.account_id = a.id
			join back_office.account_type at on a.account_type_id = at.id	 
				join back_office.account_type_group atg on at.account_type_group_id = atg.id and atg.mnemonics = 'Н'
			join ourpension.sharer s on a.owner_sharer_id = s.id
				join ourpension.contract c on c.id = s.contract_id and c.service_type = 'OPS'
	where current_date between b.start_date and b.end_date
	and s.id = sh.id
  group by s.id
	
) bw on true

	left join tools.selection s on sh.id = s.row_id
		and s.username = :username
		and s.code = :query_table_code
join ourpension.court_decision_data cdd on cdd.contract_id = c.id
  join ourpension.court_decision cd on cd.id = cdd.court_decision_id 
  join ourpension.fund fn on fn.id = cd.proper_insurer_id
	
	left join lateral ourpension.ops_get_contract_previous_insurer(c.id) f on true