https://pastein.ru/t/x9a
Загрузка данных
CREATE OR REPLACE PROCEDURE utils.p_report_rops(IN p_username character varying)
LANGUAGE plpgsql
AS $procedure$
declare
respjson jsonb;
rec_resp record;
d_count int :=0;
c_count int :=0;
currtime text :='';
p_report_date date;
begin
select report_date into p_report_date from report.dia_param;
raise notice 'report_date %', p_report_date;
raise notice 'НАЧАЛО!!! %', clock_timestamp();
-- Вставляем шапку
insert into report.dia_register(id, number, date, register_type_code,
version, username, report_date)
select
nextval('report.dia_register_id_sequence'),
(select
replace(text_prefix_number,'year', cast (date_part('year',now()) as varchar (10) ))||cast ((serial_number+1) as varchar (10))
from dev.unique_number where code = 'dia_rops'),
now(),
'ROPS',
now(),
p_username,
p.report_date
from report.dia_param p;
--времянка для шареров трансфертов
drop table if exists t_sharer_transfer;
create temp table t_sharer_transfer as
select
d.sharer_id transfer_sharer_id
from back_office.ops_transfer_register tr
join back_office.ops_transfer_details d on tr.id = d.ops_transfer_register_id
left join report.dia_param p on true
join back_office.operation o on tr.id = o.ops_transfer_register_id and o.deleted is null and o.approved is not null
and o.effective_date <= p_report_date;
raise notice 'сделали времянку со списком шареров-трансов! %', clock_timestamp();
drop table if exists t_death_info;
-- времянка для списка мертвецов
create temp table t_death_info as
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) as num
from ourpension.application_death_info a
where a.accepted is not null
and a.canceled is null
and a.rejected is null) r where r.num = 1;
create unique index t_individual_death_idx on t_death_info(individual_id);
raise notice 'сделали времянку со списком мертвецов %', clock_timestamp();
--из with во временные таблицы!
drop table if exists t_balance_d;
create temp table t_balance_d as --balance_d(sharer_id, balance, ops, ops_id, dps_g, dps_g_id, dps_l, dps_l_id, dps_mk, dps_mk_id)
select
da.owner_sharer_id as sharer_id,
sum(t.value) balance,
sum(case when dat.mnemonics in ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС') then t.value else 0 end) ops,
sum(case when dat.mnemonics in ('ОПС ИД','ОПС ФР', 'Р ПП ОПС ИД') then t.value else 0 end) ops_id,
sum(case when dat.mnemonics in ('ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г') then t.value else 0 end) dps_g,
sum(case when dat.mnemonics in ('ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД') then t.value else 0 end) dps_g_id,
sum(case when dat.mnemonics in ('ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л' ) then t.value else 0 end) dps_l,
sum(case when dat.mnemonics in ('ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД') then t.value else 0 end) dps_l_id,
sum(case when dat.mnemonics in ('ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК') then t.value else 0 end) dps_mk,
sum(case when dat.mnemonics in ('ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД') then t.value else 0 end) dps_mk_id
from back_office.transaction t
join back_office.account ca on t.credit_account_id = ca.id
join back_office.account_type cat on cat.id = ca.account_type_id
and cat.mnemonics in ('Р ОПС', 'Р ОПС ИД')
join back_office.account da on t.debit_account_id = da.id
join back_office.account_type dat on dat.id = da.account_type_id
-- join back_office.account_type_group datg on datg.id = dat.account_type_group_id --TEE 060826
-- and datg.mnemonics <>'Р ПП'
join back_office.operation o on o.id = t.operation_id
-- join back_office.operation_type ot on ot.id = o.operation_type_id --TEE 060826
--and ot.code = '' -- добавить тип операции перевода в Р ОПС по решению
join ourpension.sharer s on s.id = da.owner_sharer_id
join ourpension.individual i on i.id = s.individual_id
join t_death_info di on di.individual_id = i.id
where dat.mnemonics in ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС',
'ОПС ИД','ОПС ФР', 'Р ПП ОПС ИД',
'ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г',
'ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД',
'ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л',
'ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД',
'ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК',
'ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД')
group by da.owner_sharer_id;
raise notice 'посчитали t_balance_d %', clock_timestamp();
drop table if exists t_balance_c;
create temp table t_balance_c as --sharer_id, balance, ops, ops_id, dps_g, dps_g_id, dps_l, dps_l_id, dps_mk, dps_mk_id)
select
ca.owner_sharer_id sharer_id,
sum(t.value) balance,
sum(case when cat.mnemonics in ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС') then t.value else 0 end) ops,
sum(case when cat.mnemonics in ('ОПС ИД','ОПС ФР', 'Р ПП ОПС ИД') then t.value else 0 end) ops_id,
sum(case when cat.mnemonics in ('ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г') then t.value else 0 end) dps_g,
sum(case when cat.mnemonics in ('ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД') then t.value else 0 end) dps_g_id,
sum(case when cat.mnemonics in ('ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л' ) then t.value else 0 end) dps_l,
sum(case when cat.mnemonics in ('ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД') then t.value else 0 end) dps_l_id,
sum(case when cat.mnemonics in ('ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК') then t.value else 0 end) dps_mk,
sum(case when cat.mnemonics in ('ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД') then t.value else 0 end) dps_mk_id
from back_office.transaction t
join back_office.account ca on t.credit_account_id = ca.id
join back_office.account_type cat on cat.id = ca.account_type_id
join back_office.account da on t.debit_account_id = da.id
join back_office.account_type dat on dat.id = da.account_type_id
and dat.mnemonics in ('Р ОПС', 'Р ОПС ИД')
-- join back_office.account_type_group datg on datg.id = dat.account_type_group_id --TEE 060826
join back_office.operation o on o.id = t.operation_id
-- join back_office.operation_type ot on ot.id = o.operation_type_id --TEE 060826
--and ot.code = '' -- добавить тип операции перевода в Р ОПС по решению
join ourpension.sharer s on s.id = ca.owner_sharer_id
join ourpension.individual i on i.id = s.individual_id
join t_death_info di on di.individual_id = i.id
where cat.mnemonics in ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС',
'ОПС ИД','ОПС ФР', 'Р ПП ОПС ИД',
'ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г',
'ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД',
'ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л',
'ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД',
'ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК',
'ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД')
group by ca.owner_sharer_id;
raise notice 'посчитали t_balance_с %', clock_timestamp();
select count(*) into d_count from t_balance_d;
select count(*) into c_count from t_balance_c;
raise notice 'в t_balance_с %', c_count::text ;
raise notice 'в t_balance_d %', d_count::text ;
drop table if exists t_balance_all;
create temp table t_balance_all as
select
s.id sharer_id,
(coalesce(bd.balance,0) - coalesce(bc.balance,0)) as balance,
(coalesce(bd.ops,0) - coalesce(bc.ops,0)) as ops,
(coalesce(bd.ops_id,0) - coalesce(bc.ops_id,0)) as ops_id,
(coalesce(bd.dps_g,0) - coalesce(bc.dps_g,0)) as dps_g,
(coalesce(bd.dps_g_id,0) - coalesce(bc.dps_g_id,0)) as dps_g_id,
(coalesce(bd.dps_l,0) - coalesce(bc.dps_l,0)) as dps_l,
(coalesce(bd.dps_l_id,0) - coalesce(bc.dps_l_id,0)) as dps_l_id,
(coalesce(bd.dps_mk,0) - coalesce(bc.dps_mk,0)) as dps_mk,
(coalesce(bd.dps_mk_id,0) - coalesce(bc.dps_mk_id,0)) as dps_mk_id
from ourpension.sharer s
join ourpension.individual i on i.id = s.individual_id
join t_death_info di on di.individual_id = i.id
left join t_balance_c bc on bc.sharer_id = s.id
left join t_balance_d bd on bd.sharer_id = s.id;
raise notice 'посчитали t_balance_all! %', clock_timestamp();
select count(*) into c_count from t_balance_all;
raise notice 'в t_balance_all %', c_count::text ;
---------------
drop table if exists t_buffer;
create temp table t_buffer as
select
nextval('report.dia_rops_id_sequence') id,
currval('report.dia_register_id_sequence') dia_register_id,
i.insurance_number insurance_number,
s.number sharer_number,
di.death_date death_date,
coalesce(b.ops,0) ops,
coalesce(b.ops_id,0) ops_id,
coalesce(b.dps_l,0) dps_l,
coalesce(b.dps_l_id,0) dps_l_id,
coalesce(b.dps_g,0) dps_g,
coalesce(b.dps_g_id,0) dps_g_id,
coalesce(b.dps_mk,0) dps_mk,
coalesce(b.dps_mk_id,0) dps_mk_id,
coalesce(b.balance,0) total
from ourpension.sharer s
join ourpension.individual i on s.individual_id = i.id
join t_balance_all b on b.sharer_id = s.id
join t_death_info di on di.individual_id = i.id
left join report.dia_param p on true
left join t_sharer_transfer tst on s.id = tst.transfer_sharer_id
where di.death_date <= p.report_date
and b.balance<>0
and transfer_sharer_id is null;
--and not exists(
-- select
-- 1
-- from back_office.ops_transfer_register tr
-- join back_office.ops_transfer_details d on tr.id = d.ops_transfer_register_id
-- join back_office.operation o on tr.id = o.ops_transfer_register_id and o.deleted is null and o.approved is not null
-- and o.effective_date <= p.report_date
-- where d.sharer_id = s.id
--);
raise notice 'посчитали t_buffer! %', clock_timestamp();
select count(*) into c_count from t_buffer;
raise notice 'в t_buffer %', c_count::text ;
insert into report.dia_rops(
id,
dia_register_id,
insurance_number,
sharer_number,
death_date,
ops,
ops_id,
dps_l,
dps_l_id,
dps_g,
dps_g_id,
dps_mk,
dps_mk_id,
total
)
select
id,
dia_register_id,
insurance_number,
sharer_number,
death_date,
ops,
ops_id,
dps_l,
dps_l_id,
dps_g,
dps_g_id,
dps_mk,
dps_mk_id,
total
from t_buffer;
raise notice 'сохранили в dia_rops %', clock_timestamp();
update dev.unique_number
set serial_number = serial_number+1
where code = 'dia_rops';
raise notice 'обновили dev.unique_number %', clock_timestamp();
/*
with balance_d(sharer_id, balance, ops, ops_id, dps_g, dps_g_id, dps_l, dps_l_id, dps_mk, dps_mk_id) as (
select
da.owner_sharer_id,
sum(t.value),
sum(case when dat.mnemonics in ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС') then t.value else 0 end),
sum(case when dat.mnemonics in ('ОПС ИД','ОПС ФР', 'Р ПП ОПС ИД') then t.value else 0 end),
sum(case when dat.mnemonics in ('ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г') then t.value else 0 end),
sum(case when dat.mnemonics in ('ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД') then t.value else 0 end),
sum(case when dat.mnemonics in ('ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л' ) then t.value else 0 end),
sum(case when dat.mnemonics in ('ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД') then t.value else 0 end),
sum(case when dat.mnemonics in ('ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК') then t.value else 0 end),
sum(case when dat.mnemonics in ('ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД') then t.value else 0 end)
from back_office.transaction t
join back_office.account ca on t.credit_account_id = ca.id
join back_office.account_type cat on cat.id = ca.account_type_id
and cat.mnemonics in ('Р ОПС', 'Р ОПС ИД')
join back_office.account da on t.debit_account_id = da.id
join back_office.account_type dat on dat.id = da.account_type_id
join back_office.account_type_group datg on datg.id = dat.account_type_group_id
-- and datg.mnemonics <>'Р ПП'
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id
--and ot.code = '' -- добавить тип операции перевода в Р ОПС по решению
join ourpension.sharer s on s.id = da.owner_sharer_id
join ourpension.individual i on i.id = s.individual_id
join t_death_info di on di.individual_id = i.id
group by da.owner_sharer_id
), balance_c(sharer_id, balance, ops, ops_id, dps_g, dps_g_id, dps_l, dps_l_id, dps_mk, dps_mk_id) as (
select
ca.owner_sharer_id,
sum(t.value),
sum(case when cat.mnemonics in ('ОПС', 'ОПС ГВ', 'ОПС КП', 'Р ПП ОПС') then t.value else 0 end),
sum(case when cat.mnemonics in ('ОПС ИД','ОПС ФР', 'Р ПП ОПС ИД') then t.value else 0 end),
sum(case when cat.mnemonics in ('ДПС Г', 'ДПС Г ГВ', 'ДПС Г КП', 'Р ПП ДПС Г') then t.value else 0 end),
sum(case when cat.mnemonics in ('ДПС Г ИД', 'ДПС Г ФР', 'Р ПП ДПС Г ИД') then t.value else 0 end),
sum(case when cat.mnemonics in ('ДПС Л', 'ДПС Л ГВ', 'ДПС Л КП', 'Р ПП ДПС Л' ) then t.value else 0 end),
sum(case when cat.mnemonics in ('ДПС Л ИД', 'ДПС Л ФР', 'Р ПП ДПС Л ИД') then t.value else 0 end),
sum(case when cat.mnemonics in ('ДПС МК', 'ДПС МК ГВ', 'ДПС МК КП', 'Р ПП ДПС МК') then t.value else 0 end),
sum(case when cat.mnemonics in ('ДПС МК ИД', 'ДПС МК ФР', 'Р ПП ДПС МК ИД') then t.value else 0 end)
from back_office.transaction t
join back_office.account ca on t.credit_account_id = ca.id
join back_office.account_type cat on cat.id = ca.account_type_id
join back_office.account da on t.debit_account_id = da.id
join back_office.account_type dat on dat.id = da.account_type_id
and dat.mnemonics in ('Р ОПС', 'Р ОПС ИД')
join back_office.account_type_group datg on datg.id = dat.account_type_group_id
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id
--and ot.code = '' -- добавить тип операции перевода в Р ОПС по решению
join ourpension.sharer s on s.id = ca.owner_sharer_id
join ourpension.individual i on i.id = s.individual_id
join t_death_info di on di.individual_id = i.id
group by ca.owner_sharer_id
),balance(sharer_id, balance, ops, ops_id, dps_g, dps_g_id, dps_l, dps_l_id, dps_mk, dps_mk_id) as (
select
s.id,
(coalesce(bd.balance,0) - coalesce(bc.balance,0)) as balance,
(coalesce(bd.ops,0) - coalesce(bc.ops,0)) as balance,
(coalesce(bd.ops_id,0) - coalesce(bc.ops_id,0)) as balance,
(coalesce(bd.dps_g,0) - coalesce(bc.dps_g,0)) as balance,
(coalesce(bd.dps_g_id,0) - coalesce(bc.dps_g_id,0)) as balance,
(coalesce(bd.dps_l,0) - coalesce(bc.dps_l,0)) as balance,
(coalesce(bd.dps_l_id,0) - coalesce(bc.dps_l_id,0)) as balance,
(coalesce(bd.dps_mk,0) - coalesce(bc.dps_mk,0)) as balance,
(coalesce(bd.dps_mk_id,0) - coalesce(bc.dps_mk_id,0)) as balance
from ourpension.sharer s
left join balance_c bc on bc.sharer_id = s.id
left join balance_d bd on bd.sharer_id = s.id
)
-- insert into report.dia_rops(
--
-- id,
-- dia_register_id,
-- insurance_number,
-- sharer_number,
-- death_date,
-- ops,
-- ops_id,
-- dps_l,
-- dps_l_id,
-- dps_g,
-- dps_g_id,
-- dps_mk,
-- dps_mk_id,
-- total
--
-- )
insert into t_dia_rops(
id,
dia_register_id,
insurance_number,
sharer_number,
death_date,
ops,
ops_id,
dps_l,
dps_l_id,
dps_g,
dps_g_id,
dps_mk,
dps_mk_id,
total
)
select
nextval('report.dia_rops_id_sequence'),
currval('report.dia_register_id_sequence'),
i.insurance_number,
s.number,
di.death_date,
coalesce(b.ops,0),
coalesce(b.ops_id,0),
coalesce(b.dps_l,0),
coalesce(b.dps_l_id,0),
coalesce(b.dps_g,0),
coalesce(b.dps_g_id,0),
coalesce(b.dps_mk,0),
coalesce(b.dps_mk_id,0),
coalesce(b.balance,0)
from ourpension.sharer s
join ourpension.individual i on s.individual_id = i.id
join balance b on b.sharer_id = s.id
join t_death_info di on di.individual_id = i.id
left join report.dia_param p on true
where di.death_date <= p.report_date
and b.balance<>0
and not exists(
select
1
from back_office.ops_transfer_register tr
join back_office.ops_transfer_details d on tr.id = d.ops_transfer_register_id
join back_office.operation o on tr.id = o.ops_transfer_register_id and o.deleted is null and o.approved is not null
and o.effective_date <= p.report_date
where d.sharer_id = s.id
);
raise notice 'Закончили со вставкой в табл t_dia_rops';
update dev.unique_number
set serial_number = serial_number+1
where code = 'dia_rops';
*/
end;
$procedure$
