---

SELECT
  sh.id as id,
  '<a target="_blank" href="view?viewCode=Client_list&id=' || sh.individual_id||'">'|| 
  	rtrim(i.last_name || ' ' || i.first_name || ' ' || coalesce(i.middle_name, '')) ||'</a>' as fio,
  to_char(i.birth_date, 'dd.MM.yyyy') as birthDate,
  coalesce(uat.activity_rus_type, 'ОПС') as serviceTypeRus,
  '<a target="_blank" href="view?viewCode=Pzbmp8Pg&id=' || sh.id|| '"target="_blank">' || sh.number || '</a>' as number,
  to_char(sh.open_date, 'dd.MM.yyyy') as date,
  s_types.label as sharer_type,
  to_char(sh.close_date, 'dd.MM.yyyy') as closeDate,
  to_char(di.death_date, 'dd.MM.yyyy') as deathDate,
  pp.name as programName,
  trim(to_char(coalesce(b1s.curr_state, 0), '99 999 999 999 999 990D99')) as sharerCurrState,
  trim(to_char(coalesce(b.curr_state, 0), '99 999 999 999 999 990D99')) as sharerCurrState360,
  (select state from utils.get_sharer_state_on_date(sh.id)) as state,
  (select name from ourpension.sharer_statuses where code = ourpension.current_sharer_status(sh.id) limit 1) as stateNpf360 
FROM ourpension.sharer sh
JOIN ourpension.contract c ON c.id = sh.contract_id and c.id = :contractId
JOIN ourpension.individual i ON sh.individual_id = i.id
				 
LEFT JOIN ourpension.sharer_params s_params ON s_params.sharer_id = sh.id
LEFT JOIN ourpension.voc_sharer_types s_types ON s_types.code = s_params.sharer_type
left join ourpension.pension_program pp on pp.id = sh.pension_program_id
left join lateral 
			(select individual_id, death_date
				from(
					select a.individual_id, a.death_date,
					    row_number() over (partition by a.individual_id order by a.version desc) num
					  from  ourpension.application_death_info a
					where a.individual_id = i.id
						and a.accepted is not null
					    and a.canceled is null
					    and a.rejected is null
					    ) d where num = 1
			) di on true
left join lateral(
  select
		sum(b.total_credit - b.total_debit) curr_state
	from back_office.account a
		join back_office.balance b on a.id = b.account_id and coalesce(:effectiveDate, current_date) between b.start_date and b.end_date
	where a.owner_sharer_id = sh.id
) b on 1 = 1
left join lateral(
	select id, sum(balanceN) balanceN, sum(balance) curr_state from (
		select
		shb.id as id,
		sum(case when atg.mnemonics = 'Н' then io.val_num end) as balanceN,
		sum(io.val_num) as balance
		from mgr.imp_obj io
		join ourpension.sharer shb on shb.id = sh.id and io.npf360_id = shb.contract_id
		join back_office.account_type at on io.code = at.mnemonics
		join back_office.account_type_group atg on at.account_type_group_id = atg.id
		and atg.mnemonics in ('Н', 'Р(С)', 'Р(П)', 'Р(Е)', 'Р ПП', 'Р Н')
		where io.service_type = 'OPS' and io.add_dt <= coalesce(:effectiveDate, current_date)
		group by shb.id
		
		union all 
		
		select
		shb.id as id,
		sum(io.val_num) as balanceN,
		sum(io.val_num) as balance
		from mgr.imp_obj io
		join ourpension.sharer shb on shb.id = sh.id and io.npf360_id = shb.id
		where io.service_type <> 'OPS'
		group by shb.id
	)
	group by id
) b1s on true
----, utils.get_sharer_state_on_date(s.id) state
--join utils.get_sharer_state_ops_on_date(null, null, null) state on  s.id = state.sharer_id

left join ourpension.uv_activity_type uat on c.client_type = uat.client_type and c.service_type = uat.activity_eng_type
where 
1=1

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

#if ($filterServiceTypeRus && !$filterServiceTypeRus.isEmpty())
	and uat.activity_rus_type ilike trim(:filterServiceTypeRus) || '%'
#end

#if ($filterBirthDate && !$filterBirthDate.isEmpty())
	and i.birth_date =:filterBirthDate
#end

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

#if ($filterOpenDate && !$filterOpenDate.isEmpty())
	and sh.open_date = :filterOpenDate
#end

#if ($filterCloseDate && !$filterCloseDate.isEmpty())
	and sh.close_date = :filterOpenDate
#end	

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