https://pastein.ru/t/Fgg
Загрузка данных
1)
SELECT
ct.id as id,
ct.id as contractId,
'<a target="_blank" href="view?viewCode=7HPZX8vF&id=' || ct.id|| '"target="_blank">' || ct.number || '</a>' as number,
to_char(ct.date, 'DD.MM.YYYY') as date,
case
when p.lifetime_pension='true'
then '<span data-toggle="tooltip" title="' || spl.scheme_number || '" class="label label-success">' || spl.scheme_number || '</span>'
else '<span data-toggle="tooltip" title="' || spl.scheme_number || '" class="label label-primary">' || spl.scheme_number || '</span>'
end as schemeNumber,
'<span data-toggle="tooltip" title="' || coalesce(uat.activity_name, 'ОПС') || '" class="label label-success">' || coalesce(uat.activity_rus_type, 'ОПС') || '</span>' as clientType,
case
when ct.entity_id is null
then '<a target="_blank" href="view?viewCode=Client_list&id=' || ct.individual_id || '">' || i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, '') || '</a>'
else '<a target="_blank" href="view?viewCode=NCIuIMAW&id=' || ct.entity_id || '">' || e.short_name || '</a>'
end as investor,
'<span data-toggle="tooltip" title="' || to_char(
case when t.termination_date is not null or ct.close_date is not null
then coalesce(t.termination_date, ct.close_date)
else ct.date
end, 'DD.MM.YYYY') || '" class="label label-primary">' || cs.name || '</span>' as contractLifecycle,
coalesce(a.last_name,'') || ' ' || left(coalesce(a.first_name, ''), 1) || '.' || coalesce(left(a.middle_name, 1) || '.', '') as agent,
case
when ct.service_type = 'PDS' then 'ДДС'
when ct.service_type = 'NPO' and ct.individual_id is not null then 'ИПП'
when ct.service_type = 'NPO' and ct.entity_id is not null then 'КПП'
else ''
end as contractType,
pp3."name" as program,
pp2."name" as product,
ct.number as contractNumber,
cr.name as closeReasone,
to_char(termination_history.termination_date, 'DD.MM.YYYY') as terminationDate,
tr.name as terminationReason,
to_char(ct.create_date, 'DD.MM.YYYY') as createDate,
to_char(coalesce(ct.close_date,t.termination_date,ct.date), 'dd.MM.yyyy') as state_date,
pp3."name" || coalesce('(' || pp2."name" || ')','') as programProduct,
cs.name as state,
ct.client_type as clientTypeId,
coalesce(uat.activity_rus_type, 'ОПС') as serviceType,
sc.name as saleChannelName,
fe.name as fund_name,
i.last_name || ' ' || i.first_name || coalesce(' ' || i.middle_name, '') as investor_fio,
death_info.id as death_info_id,
ct.service_type as service_type,
fsg.name_in_pfr as fund_signed,
fsr.name_in_pfr as fund_resource
FROM ourpension.contract as ct
inner join ourpension.current_contract_status(ct.id) as ccs
on true
inner join ourpension.contract_statuses as cs
on cs.code = ccs.c_code
inner join ourpension.agent as a
on a.id = ct.agent_id
-- ============================================================================
-- Классификация.
-- Логика оставлена как была: берём последнюю запись.
-- ============================================================================
-- ============================================================================
-- Номер участника.
-- ============================================================================
-- ============================================================================
-- Дата действия.
--
-- Раньше ради этого вычислялся min(status_date) по ВСЕЙ истории.
--
-- Теперь для конкретного договора просто берём самую раннюю дату
-- CA0 / HA0 / MA0.
--
-- Это та же бизнес-логика:
-- min(status_date)
-- но без глобального group by на ~13.7 млн строк.
-- ============================================================================
left join ourpension.uv_activity_type as uat
on uat.activity_eng_type = ct.service_type
and uat.client_type = ct.client_type
-- ============================================================================
-- Последнее/первое прекращение ровно по исходной сортировке:
-- order by t.date, t.version desc.
--
-- В исходнике row_number partition by contract_id был внутри запроса,
-- где contract_id уже фиксирован. Поэтому partition/window там ничего
-- полезного не давал.
-- ============================================================================
LEFT JOIN LATERAL
(
SELECT
tc.contract_id,
trm.contract_termination_reason_id AS termination_reason_id,
trm.date AS termination_date
FROM ourpension.termination_contract AS tc
JOIN ourpension.termination AS trm
ON trm.id = tc.termination_id
JOIN ourpension.contract_termination_reason AS reason
ON reason.id = trm.contract_termination_reason_id
WHERE tc.contract_id = ct.id
ORDER BY
trm.date DESC NULLS LAST,
trm.version DESC NULLS LAST,
trm.id DESC
LIMIT 1
) AS t
ON TRUE
left join ourpension.entity as e
on e.id = ct.entity_id
left join lateral
(
select
max(h.status_date) as termination_date
from ourpension.contract_statuses_history as h
where h.contract_id = ct.id
and h.code in ('DA0', 'IA0', 'NA0')
) as termination_history
on true
left join ourpension.individual as i
on i.id = ct.individual_id
left join ourpension.pension_scheme as p
on p.id = ct.scheme_id
left join ourpension.contract_close_reason as cr
on cr.id = ct.contract_close_reason_id
left join ourpension.contract_termination_reason as tr
on tr.id = t.termination_reason_id
left join ourpension.information_source as sc
on sc.id = ct.sale_channel_id
left join ourpension.fund_branch as fb
on fb.id = a.fund_branch_id
left join ourpension.fund as f
on f.id = fb.fund_id
left join ourpension.entity as fe
on fe.id = f.entity_id
left join ourpension.contract_params as cpp
on cpp.contract_id = ct.id
left join prod_line.pens_product as pp3
on pp3.id = ct.product_id
left join prod_line.table_param_pl_pens_program as tpp
on tpp.pens_program_id = pp3.id
left join prod_line.pens_product_param_pl as pppp
on pppp.id = tpp.pens_product_param_pl_id
left join prod_line.pens_product as pp2
on pp2.id = pppp.pens_product_id
left join ourpension.fund as fsg
on fsg.id = ct.sign_fund_id
left join ourpension.fund as fsr
on fsr.id = ct.fund_id
-- ============================================================================
-- Информация о смерти.
-- Здесь бизнес-логику не менял.
-- Для PDS эта lateral вообще не исполняется — planner это уже показал.
-- ============================================================================
left join lateral
(
select
adi.id,
adi."number",
adi.death_date
from ourpension.application_death_info as adi
join ourpension.individual as di
on di.id = adi.individual_id
join ourpension.agent as da
on da.id = adi.agent_id
join ourpension.death_info_certificate as dc
on dc.code = adi.death_info_certificate_code
join ourpension.contract_statuses_history as history
on history.contract_id = ct.id
where adi.accepted is not null
and adi.canceled is null
and adi.rejected is null
and adi.individual_id = ct.individual_id
and history.code = 'CA0'
and adi.death_date <= history.status_date
and ct.service_type = 'OPS'
) as death_info
on true
-- ============================================================================
-- Актуальный продукт договора.
-- Логика оставлена без изменений.
-- ============================================================================
left join lateral
(
select
products.id,
products.pens_scheme_accum_stage_id as scheme_id,
ps.brief as scheme_number
from ourpension.contract_product_history as history
inner join prod_line.pens_product as products
on products.id = history.pens_product_id
left join prod_line.pens_scheme as ps
on ps.id = products.pens_scheme_accum_stage_id
where history.contract_id = ct.id
and current_timestamp between
history.begin_date
and coalesce(history.end_date, 'infinity')
limit 1
) as spl
on true
WHERE false
-- ============================================================================
-- ВАЖНО.
-- Было:
-- coalesce(ct.service_type, 'OPS') = :filterServiceType
-- Для PDS это заставило planner идти по contract_pkey и отфильтровать
-- ~13.55 млн строк.
-- Ниже та же семантика:
-- NULL считается OPS, как и раньше.
-- Но обычный service_type теперь виден planner напрямую.
-- ============================================================================
-- ============================================================================
-- Фильтр по самой ранней дате действия.
-- ============================================================================
2)
WITH ids as MATERIALIZED(
select
i.id
from ourpension.individual i
where i.id = COALESCE(:individual_id, -1)
union
select
dd.individual_id as id
from ourpension.individual_doubles d
inner join ourpension.individual_doubles_details dd on d.id = dd.doubles_id
where d.id = COALESCE(:fDoublesId, -1)
),
b (id, balance) as MATERIALIZED(
select
a.owner_sharer_id,
sum(b.total_credit - b.total_debit)
from back_office.account a
join ourpension.sharer sh on sh.id = a.owner_sharer_id
join ids on sh.individual_id = ids.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
join back_office.balance b on a.id = b.account_id
and coalesce(:effectiveDate, current_date) between b.start_date and b.end_date
group by a.owner_sharer_id
),
br1s (id, balance) as MATERIALIZED(
select
sh.id,
sum(io.val_num)
from mgr.imp_obj io
join ids on true
join ourpension.sharer sh on sh.individual_id = ids.id
join back_office.account_type at on io.code = at.mnemonics and io.npf360_id = sh.contract_id
join back_office.account_type_group atg on at.account_type_group_id = atg.id
group by sh.id
),
first_deposit(contract_id, sharer_id, effective_date) AS MATERIALIZED(
SELECT
sh.contract_id,
sh.id,
COALESCE(
sh.open_date,
(
select MIN(t.effective_date)
from back_office.account a
join back_office.account_type at
on at.id = a.account_type_id
join back_office.account_type_group atg
on atg.id = at.account_type_group_id
and atg.mnemonics = 'Н'
join back_office.transaction t
on t.credit_account_id = a.id
where a.owner_sharer_id = sh.id
and a.owner_contract_id = sh.contract_id
)
)
from ids
join ourpension.sharer sh
on sh.individual_id = ids.id
), termination(contract_id, termination_reason_id, termination_date, num) as MATERIALIZED(
select
tc.contract_id,
t.contract_termination_reason_id,
t.date,
row_number() over (partition by tc.contract_id order by t.date, t.version desc)
from
ids
inner join ourpension.contract c on ids.id = c.individual_id
inner join ourpension.termination_contract tc on c.id = tc.contract_id
inner join ourpension.termination t on tc.termination_id = t.id
), s1(id) as MATERIALIZED(
select
s.id as id
from
ids
inner join ourpension.sharer s on ids.id = s.individual_id
union
select
s.id as id
from
ids
inner join ourpension.contract c on ids.id = c.individual_id
inner join ourpension.sharer s on c.id = s.contract_id
), s (sharer_id, sharer_individual_id, contract_individual_id, contract_id, contract_number, contract_date, sharer_number, sharer_date,
sharer_close_date, closed_reason_id, close_reason, scheme_name, scheme_number, lifetime_pension, lifetime_pension_text, periodicity_payout,
periodicity_payout_type, client_type, service_type, payment, pension_program_id, curr_state, curr_state_n,
contract_state_date, contract_state, contract_state_rank, sharer_name, curr_state_1s, curr_state_n_1s, fund_name, acc_type, comment,
termination_date, fund_signed
) as (
select
sh.id,
sh.individual_id,
c.individual_id,
c.id,
c.number,
c.date,
sh.number,
sh.open_date,
sh.close_date,
sh.closed_reason_id,
cr.name,
p.name,
p.number,
p.lifetime_pension,
case p.lifetime_pension
when 'true' then 'Пожизненная'
when 'false' then 'Срочная'
end,
pd.name,
pd.id,
c.client_type,
c.service_type,
0,
sh.pension_program_id,
coalesce(b.balance, 0),
coalesce(b.balance, 0),
cs1s.status_dt,
case
when cs1s.service_type = 'OPS' then
case
when cs1s.contract_status in ('Заключен', 'Ожидание подтверждения ПФР', 'Ожидание поступления взносов')
then 'Заключен'
when cs1s.contract_status in ('Накопительный период', 'Выплатной период')
then 'Действующий'
when cs1s.contract_status in ('Подготовка к закрытию', 'Закрыт', 'Передан в РОПС')
then 'Прекращен'
when cs1s.contract_status in ('Приостановлен по отказу из ПФР', 'Отказ фонда')
then 'Аннулирован'
else
cs1s.contract_status
end
else
cs1s.contract_status
end,
case
when cs1s.contract_status in ('Приостановлен по отказу из ПФР', 'Отказ фонда') /*coalesce(sh.close_date, c.close_date) is not null */then 4
when cs1s.contract_status in ('Подготовка к закрытию', 'Закрыт', 'Передан в РОПС', 'Прекращен')/*t.termination_date is not null*/ then 3
when cs1s.contract_status in ('Накопительный период', 'Выплатной период', 'Действующий') /*coalesce(sh.open_date, cf.effective_date) is not null*/ then 2
when cs1s.contract_status in ('Заключен', 'Ожидание подтверждения ПФР', 'Ожидание поступления взносов', 'Оформлен')/*c.date is not null*/ then 1
else 0
end,
case
when i.id is not null then concat_ws(' ', i.last_name, i.first_name, i.middle_name)
when e.id is not null then coalesce(e.short_name, e.name)
else ''
end,
0,
coalesce(br1s.balance, 0),
f.name_short,
case
when c.service_type = 'OPS' then
'ОПС'
when c.service_type = 'PDS' then
'СДС'
when c.service_type = 'NPO' and c.entity_id is null then
'ИПС'
else 'КИПС/СПС'
end ,
sh.comment,
t.termination_date,
fsg.name_in_pfr AS fund_signed
FROM s1
join ourpension.sharer sh on s1.id = sh.id
join ourpension.contract c on c.id = sh.contract_id
left join ourpension.individual i on c.individual_id = i.id
left join ourpension.entity e on c.entity_id = e.id
left join ourpension.fund f on c.fund_id = f.id
LEFT JOIN ourpension.fund fsg ON fsg.id = c.sign_fund_id
left join ourpension.contract_close_reason cr on cr.id = sh.closed_reason_id
left join ourpension.pension_scheme p on c.scheme_id = p.id
left join ourpension.periodicity pd on pd.id = c.periodicity_payout_id
left join b on b.id = sh.id
left join br1s on br1s.id = sh.id
left join termination t on t.contract_id = c.id and t.num = 1
left join first_deposit cf on cf.contract_id = c.id
left join mgr.v_io_contract_status cs1s on cs1s.contract_id = c.id -- добавлено для получения статуса договора из 1С Новиков А.В. 29.05.2025
), payment_suspense (sharer_id, suspension_date, renewal_date) as MATERIALIZED(
select
t.sharer_id,
t.suspension_date,
t.renewal_date
from back_office.payment_suspense t
where t.suspension_date = ( select max(t2.suspension_date) from back_office.payment_suspense t2 where t2.sharer_id = t.sharer_id )
), payment_resumption (sharer_id, resumption_date) as (
select
r.sharer_id,
r.resumption_date
from back_office.resumption r
where r.resumption_date = ( select max(r2.resumption_date) from back_office.resumption r2 where r.sharer_id = r2.sharer_id )
), 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
), assignee(sharer_id, assignee_count) as MATERIALIZED(
select
s.id,
count(*)
from
ids
inner join ourpension.sharer s on ids.id = s.individual_id
where exists(
select
from ourpension.application_assignee_distribution a
where a.sharer_id = s.id and a.accepted is not null and a.canceled is null
)
group by s.id
)
, uorls as ( -- уведомления об объединении СНИЛС
select urd.id, uc."number",
row_number() over (partition by uc."number" order by u.id desc) as rn
from report.uorls_contract uc
join report.uorls u on
uc.uorls_id = u.id
join report.uorls_register_document urd on
u.uorls_register_document_id = urd.id
)
SELECT
COALESCE( (select name from ourpension.sharer_statuses where code =(select current_sharer_status from ourpension.current_sharer_status(s.sharer_id)) limit 1), '' ) as sharerStatusRaw,
s.sharer_id as id,
s.comment as comment,
to_char(s.contract_date, 'DD.MM.YYYY') as contractDate,
'<a target="_blank" href="view?viewCode=7HPZX8vF&id='||s.contract_id||'">'||s.contract_number||'</a>' as contractNumber,
(select name from ourpension.contract_statuses where code = (select c_code from ourpension.current_contract_status(s.contract_id) limit 1)) as contract_status,
to_char(s.termination_date, 'DD.MM.YYYY') as termination_date,
'<a target="_blank" href="view?viewCode=Pzbmp8Pg&id='||s.sharer_id||'">'||s.sharer_number||'</a>' as sharerNumber,
case
when s.service_type = 'PDS' then 'ПДС'
when s.client_type = 0 and s.service_type = 'NPO' then
'<span data-toggle="tooltip" title="НПО индивидуальный" class="label label-success">НПО</span>'
when s.service_type = 'NPO' then
'<span data-toggle="tooltip" title="НПО корпоративный" class="label label-primary">НПО</span>'
else '<span data-toggle="tooltip" title="ОПС" class="label label-primary">ОПС</span>'
end as clientType,
s_types.label as sharer_type,
to_char(s.sharer_date, 'DD.MM.YYYY') as sharerDate,
to_char(coalesce(s.curr_state_n_1s, s.curr_state_1s, 0), '99 999 999 999 999 990D99') as currState,
case when s.lifetime_pension='true' then
'<span data-toggle="tooltip" title="' ||s.scheme_name|| '" class="label label-success">'||s.scheme_number||'</span>'
else
'<span data-toggle="tooltip" title="' ||s.scheme_name|| '" class="label label-primary">'||s.scheme_number||'</span>'
end as schemeNumber,
to_char(coalesce(s.curr_state, 0), '99 999 999 999 999 990D99') as currState360,
case when s.sharer_date is not null
then '<span data-toggle="tooltip" title="' || to_char(s.sharer_date, 'DD.MM.YYYY') || '" class="label label-success">Зарегистрирован</span><br>'
else ''
end ||
--надо присоединить дату первого взноса
--'<span data-toggle="tooltip" title="дата первого взноса" class="label label-primary">Действующий</span>' ||
case when s. sharer_close_date is not null
then '<span data-toggle="tooltip" title="' || to_char(s. sharer_close_date, 'DD.MM.YYYY') || '" class="label label-default">Закрыт</span>'
else ''
end as lifecycle,
COALESCE( '<span with-history-status>' || (select name from ourpension.sharer_statuses where code =(select current_sharer_status from ourpension.current_sharer_status(s.sharer_id)) limit 1) || '</span>', '' ) as sharerStatus,
sh_state.state as state,
case s.periodicity_payout_type
when 1 then
'<span data-toggle="tooltip" title="' ||s.periodicity_payout|| '" class="label label-success">M</span>'
when 2 then
'<span data-toggle="tooltip" title="' ||s.periodicity_payout|| '" class="label label-primary">К</span>'
when 3 then
'<span data-toggle="tooltip" title="' ||s.periodicity_payout|| '" class="label label-primary">П</span>'
when 4 then
'<span data-toggle="tooltip" title="' ||s.periodicity_payout|| '" class="label label-primary">Г</span>'
end as periodicityPayout,
nsps.status as condition_state,
to_char(s.sharer_close_date, 'DD.MM.YYYY') as sharerCloseDate,
'<span data-toggle="tooltip" title="'||pp.name||'" class="label label-success">'||pp.number||'</span>' as pensionProgram,
trim(to_char(s.payment, '99 999 999 999 999 990D99')) as payment,
s.contract_id as contractId,
case
when a.assignee_count is not null then '<span class="glyphicon glyphicon-ok" aria-hidden="true"></span>'
else ''
end as haveAssignee,
'<span data-toggle="tooltip" title="' || to_char(s.contract_state_date, 'DD.MM.YYYY') || '" class="label label-primary">' || s.contract_state || '</span>' as contractLifecycle,
to_char(p.suspension_date, 'DD.MM.YYYY') as suspensionDate,
to_char(r.resumption_date, 'DD.MM.YYYY') as resumptionDate,
to_char(p.renewal_date, 'DD.MM.YYYY') as renewalDate,
s.service_type as serviceType,
s.sharer_id as sharerId,
s.client_type as clientTypeId,
coalesce(s.curr_state_n_1s, s.curr_state_1s, 0) as balance,
coalesce(s.curr_state, 0) as balance360,
s.close_reason as closeReason,
to_char(cr.restore_date, 'DD.MM.YYYY') as regainRestoreDate,
to_char(cr.close_date, 'DD.MM.YYYY') as regainCloseDate,
s.sharer_name as sharer_name,
s.fund_name as fund_name,
s.acc_type as acc_type,
s.contract_number as contract_number,
s.client_type as client_type,
s.sharer_id as sharer_id,
s.fund_signed as fund_signed
FROM s
left join ourpension.pension_program pp on pp.id = s.pension_program_id
left join payment_suspense p on p.sharer_id = s.sharer_id
left join payment_resumption r on r.sharer_id = s.sharer_id
left join closure_regain cr on cr.sharer_id = s.sharer_id and cr.num = 1
left join assignee a on a.sharer_id = s.sharer_id
--join utils.get_sharer_state_ops_on_date(null, null, null) state on s.sharer_id = state.sharer_id
--join utils.get_sharer_state_on_date(s.sharer_id) state on true
left join ourpension.f_get_sharer_state(coalesce(s.sharer_id, -1), NULL, false) sh_state on true
left join utils.get_sharer_state_ops_on_date(coalesce(s.sharer_id, -1), null, null) state on true
left join back_office.ops_decision_type dt on dt.code = state.decision_type_code
left join lateral back_office.fn_npo_sharer_payment_status(s.sharer_id) nsps on TRUE
left join uorls uo on s.contract_number = uo."number" and uo.rn = 1
LEFT JOIN ourpension.sharer_params s_params ON s_params.sharer_id = s.sharer_id
LEFT JOIN ourpension.voc_sharer_types s_types ON s_types.code = s_params.sharer_type