https://pastein.ru/t/azJ
Загрузка данных
WITH /* --task 45797
(id, balanceN, balance) as (
select
a.owner_sharer_id,
sum(case when atg.mnemonics = 'Н' then b.total_credit - b.total_debit end) as balanceN,
sum(b.total_credit - b.total_debit)
from back_office.account a
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 in ('Н', 'Р(С)', 'Р(П)', 'Р(Е)', 'Р ПП', 'Р Н')
join back_office.balance b on a.id = b.account_id
and coalesce(:fEffectiveDate, current_date) between b.start_date and b.end_date
group by a.owner_sharer_id
),
*/ --end task 45797
/* br (id, balance) as (
select
a.owner_sharer_id,
sum(b.total_credit - b.total_debit)
from back_office.account a
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 in ('Р(С)', 'Р(П)', 'Р(Е)', 'Р ПП', 'Р Н')
join back_office.balance b on a.id = b.account_id
and coalesce(:fEffectiveDate, current_date) between b.start_date and b.end_date
group by a.owner_sharer_id
),*/ /*first_deposit(sharer_id, effective_date) as (
select
a.owner_sharer_id,
min(t.effective_date)
from back_office.transaction t
join back_office.account a on t.credit_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 in ('Н')
group by
a.owner_sharer_id
),*/
employees_list_member(employees_list_member_id, employees_list_id, sharer_id, periodicity_code, retiree, program_id, scheme_id, num) as (
select
elm.id,
el.id,
elm.sharer_id,
elm.periodicity_code,
elm.retiree,
el.program_id,
el.scheme_id,
row_number() over (partition by elm.sharer_id order by el.version desc)
from ourpension.employees_list_member elm
join ourpension.employees_list el on el.id = elm.employees_list_id
where el.accepted is not null
and el.canceled is null
and el.rejected is null
),
-- , closure_regain (sharer_closure_regain_id, sharer_id, restore_date, close_date, num) as (
-- select
-- t.id,
-- t.sharer_id,
-- t.restore_date,
-- t.close_date,
-- row_number() over (partition by t.sharer_id order by t.restore_date desc)
-- from ourpension.sharer_closure_regain t
--
-- )
cte_sharer_info as
(
SELECT
s.id,
c.id as contract_id,
c.number as contract_number,
c.date as contract_date,
c.client_type,
c.service_type,
i.insurance_number,
c.entity_id,
c.individual_id AS contract_individual,
s.number as sharer_number,
s.individual_id,
i.last_name,
i.first_name,
i.middle_name,
ourpension.current_sharer_status(s.id) as status_code,
s.open_date as sharer_date,
trim(to_char(coalesce(b1s.balance, 0) /*+ coalesce(br.balance, 0)*/, '99 999 999 999 999 990D99')) as balance,
trim(to_char(coalesce(b.balance, 0) /*+ coalesce(br.balance, 0)*/, '99 999 999 999 999 990D99')) as balance360,
trim(to_char(coalesce(b1s.balanceN, 0) /*+ coalesce(br.balance, 0)*/, '99 999 999 999 999 990D99'))
/* ||
case when security.user_has_any_roles(:user_id, string_to_array('ROLE_ADMIN',',')) then
' | ' ||
case when coalesce(b.balanceN, 0) < 999999999 then
lpad( trim(to_char(coalesce(b.balanceN, 0) /*+ coalesce(br.balance, 0)*/, '99 999 999 999 999 990D99')) , 15, chr(160))
else
trim(to_char(coalesce(b.balanceN, 0) /*+ coalesce(br.balance, 0)*/, '99 999 999 999 999 990D99'))
end
else ''
end*/
/*
trim(to_char(coalesce(b1s.balanceN, 0), '99 999 999 999 999 990D99')) || ' | ' || trim(to_char(coalesce(b.balanceN, 0), '99 999 999 999 999 990D99'))
*/ as balanceN,
to_char(coalesce(b.balanceN, 0) /*+ coalesce(br.balance, 0)*/, '99 999 999 999 999 990D99') as balanceN360,
s.close_date as sharer_close_date,
cr.name as close_reason,
clr.restore_date as regain_restore_date,
clr.close_date as regain_close_date,
i.certificate_series,
i.certificate_number,
aa.address_text as actual_address,
ar.address_text as registration_address,
ps.number as scheme_number,
c.pension_rule_id,
m.program_id,
acc.id as account_id
FROM ourpension.sharer s
join ourpension.contract c on s.contract_id = c.id
join ourpension.individual i on i.id = s.individual_id
left join ourpension.individual ii on ii.id = c.individual_id
left join ourpension.entity e on e.id = c.entity_id
left join lateral (
select *
from back_office.account ac
where ac.owner_sharer_id = s.id
order by id desc limit 1
) acc on true
-- left join b on b.id = s.id -- -- task 45797
--left join br on br.id = s.id
--left join first_deposit f on f.sharer_id = s.id
left join ourpension.contract_close_reason cr on cr.id=s.closed_reason_id
left join employees_list_member m on m.sharer_id = s.id and m.num = 1
left join ourpension.address ar on i.registration_address_id = ar.id
left join ourpension.address aa on i.actual_address_id = aa.id
left join ourpension.pension_scheme ps on ps.id = s.scheme_id
--left join closure_regain clr on clr.sharer_id = s.id and clr.num = 1
left join lateral (
select t.id sharer_closure_regain_id,
t.sharer_id sharer_id,
t.restore_date restore_date,
t.close_date close_date,
row_number() over (partition by t.sharer_id order by t.restore_date desc) num
from ourpension.sharer_closure_regain t
where t.sharer_id = s.id
) clr on true and clr.num = 1
-- task 45797
left join lateral (
select
a.owner_sharer_id as id,
sum(case when atg.mnemonics = 'Н' then b.total_credit - b.total_debit end) as balanceN,
sum(b.total_credit - b.total_debit) as balance
from back_office.account a
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 in ('Н', 'Р(С)', 'Р(П)', 'Р(Е)', 'Р ПП', 'Р Н')
join back_office.balance b on a.id = b.account_id
and coalesce(:fEffectiveDate, current_date) between b.start_date and b.end_date -- заменить null на :fEffectiveDate,
where a.owner_sharer_id = s.id
group by a.owner_sharer_id
) b on true
--end task 45797
--start task 63978
left join lateral(
select id, sum(balanceN) balanceN, sum(balance) balance from (
select
sh.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 sh on sh.id = s.id and io.npf360_id = sh.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'
group by sh.id
union all
select
sh.id as id,
sum(io.val_num) as balanceN,
sum(io.val_num) as balance
from mgr.imp_obj io
join ourpension.sharer sh on sh.id = s.id and io.npf360_id = sh.contract_id
where io.service_type <> 'OPS'
group by sh.id
)
group by id
) b1s on true
--end task 63978
--start task 40805
--left join utils.get_sharer_state_ops_on_date(null, null, null) state on s.id = state.sharer_id and c.service_type = 'OPS'
--end task 40805
WHERE
false
)
SELECT
csi.id as id,
csi.id as contractId,
'<a target="_blank" href="view?viewCode=7HPZX8vF&id='||csi.contract_id||'">'||csi.contract_number||'</a>' as contractNumber,
to_char(csi.contract_date, 'DD.MM.YYYY') as contractDate,
case
when csi.client_type = 0 and csi.service_type = 'NPO' then
'<span data-toggle="tooltip" title="НПО индивидуальный" class="label label-success">НПО</span>'
when csi.service_type = 'NPO' then
'<span data-toggle="tooltip" title="НПО корпоративный" class="label label-primary">НПО</span>'
when csi.service_type = 'PDS' then
'<span data-toggle="tooltip" title="ПДС" class="label label-success">ПДС</span>'
else '<span data-toggle="tooltip" title="ОПС" class="label label-primary">ОПС</span>'
end as clientType,
csi.insurance_number as insurance_number,
case when (csi.entity_id is null)
then /*'<a target="_blank" href="view?viewCode=Client_list&id='||ii.id||'">'||rtrim(ii.last_name || ' ' || ii.first_name || ' ' || coalesce(ii.middle_name, ''))||'</a>'*/ (select concat_ws(' ', trim(ii.last_name),trim(ii.first_name),trim(ii.middle_name)) from ourpension.individual ii where ii.id = csi.individual_id)
else /*'<a target="_blank" href="view?viewCode=NCIuIMAW&id='||e.id||'">'||e.short_name||'</a>'*/ (select e.short_name from ourpension.entity e where e.id = csi.entity_id)
end as investor,
csi.sharer_number as sharerNumber,
'<a target="_blank" href="view?viewCode=Client_list&id='||csi.individual_id||'">'||rtrim(csi.last_name || ' ' || csi.first_name || ' ' || coalesce(csi.middle_name, ''))||'</a>' as sharer,
ss.name as state,
to_char(csi.sharer_date, 'DD.MM.YYYY') as sharerDate,
csi.balance as balance,
csi.balance360 as balance360,
csi.balanceN as balanceN,
csi.balanceN360 as balanceN360,
to_char(csi.sharer_close_date, 'DD.MM.YYYY') as closeDate,
csi.individual_id as individual_id,
csi.close_reason as closeReasone,
to_char(csi.regain_restore_date, 'DD.MM.YYYY') as regainRestoreDate,
to_char(csi.regain_close_date, 'DD.MM.YYYY') as regainCloseDate,
csi.certificate_series || csi.certificate_number as certificate,
csi.actual_address as actualAddress,
csi.registration_address as registrationAddress,
csi.service_type as serviceType,
csi.scheme_number as scheme_number,
(select rmn.brief from ourpension.pension_rule pr
join prod_line.regulatory_model_npf rmn on rmn.id = pr.regulatory_model_npf_id
where pr.id = csi.pension_rule_id
) as regulatory_model_name,
csi.contract_individual as contract_individual
FROM cte_sharer_info csi
INNER JOIN ourpension.sharer_statuses ss
ON ss.code = csi.status_code
WHERE true
