From 495e38c6da7b39891ab5ef94268c221209bebde9 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Tue, 11 Feb 2020 13:18:26 -0500 Subject: [PATCH] beginnings of aged billing summary --- Open-ILS/examples/fm_IDL.xml | 44 ++++++++++++++++++++++ Open-ILS/src/sql/Pg/080.schema.money.sql | 33 ++++++++++++++++ Open-ILS/src/sql/Pg/090.schema.action.sql | 9 +++++ .../XXXX.schema.money_aged_billing_summary.sql | 40 ++++++++++++++++++++ 4 files changed, 126 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.money_aged_billing_summary.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 75b52a7597..906aa8fff8 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -8449,6 +8449,50 @@ SELECT usr, --> + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index 7d30797b1e..04da84fa09 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -704,5 +704,38 @@ CREATE OR REPLACE VIEW money.all_billings AS UNION ALL SELECT * FROM money.aged_billing; +-- Create table to hold aged materialized billable xact summary data +CREATE TABLE money.aged_billing_summary ( + id BIGINT, + xact_start TIMESTAMP WITH TIME ZONE, + xact_finish TIMESTAMP WITH TIME ZONE, + total_paid NUMERIC, + last_payment_ts TIMESTAMP WITH TIME ZONE, + last_payment_note TEXT, + last_payment_type TEXT, + total_owed NUMERIC, + last_billing_ts TIMESTAMP WITH TIME ZONE, + last_billing_note TEXT, + last_billing_type TEXT, + balance_owed NUMERIC, + xact_type TEXT +); + +ALTER TABLE money.aged_billing_summary ADD PRIMARY KEY (id); + +CREATE INDEX money_aged_billing_summary_usr_idx ON money.aged_billing_summary (usr); +CREATE INDEX money_aged_billing_summary_xact_start_idx ON money.aged_billing_summary (xact_start); + +CREATE OR REPLACE VIEW money.all_billing_summary AS + SELECT id, xact_start, xact_finish, total_paid, last_payment_ts + last_payment_note, last_payment_type, total_owed, last_billing_ts, + last_billing_note, last_billing_type, balance_owed, xact_type + FROM money.billable_xact_summary + UNION ALL + SELECT id, xact_start, xact_finish, total_paid, last_payment_ts + last_payment_note, last_payment_type, total_owed, last_billing_ts, + last_billing_note, last_billing_type, balance_owed, xact_type + FROM money.aged_billing_summary; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 3fb3b4a782..3be3216b6f 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -352,6 +352,15 @@ BEGIN auto_renewal, auto_renewal_remaining FROM action.all_circulation WHERE id = OLD.id; + -- Archive a copy of materialized_xact_billing_summary (minus usr) + INSERT INTO money.aged_billing_summary + (id, xact_start, xact_finish, total_paid, last_payment_ts, last_payment_note, last_payment_type, + total_owed, last_billing_ts, last_billing_note, last_billing_type, balance_owed, xact_type) + SELECT + id, xact_start, xact_finish, total_paid, last_payment_ts, last_payment_note, last_payment_type, + total_owed, last_billing_ts, last_billing_note, last_billing_type, balance_owed, xact_type + FROM money.materialized_billable_xact_summary WHERE id = OLD.id; + -- Migrate billings and payments to aged tables INSERT INTO money.aged_billing diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.money_aged_billing_summary.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.money_aged_billing_summary.sql new file mode 100644 index 0000000000..fd05d96295 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.money_aged_billing_summary.sql @@ -0,0 +1,40 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +-- Create table to hold aged materialized billable xact summary data +CREATE TABLE money.aged_billing_summary ( + id BIGINT, + xact_start TIMESTAMP WITH TIME ZONE, + xact_finish TIMESTAMP WITH TIME ZONE, + total_paid NUMERIC, + last_payment_ts TIMESTAMP WITH TIME ZONE, + last_payment_note TEXT, + last_payment_type TEXT, + total_owed NUMERIC, + last_billing_ts TIMESTAMP WITH TIME ZONE, + last_billing_note TEXT, + last_billing_type TEXT, + balance_owed NUMERIC, + xact_type TEXT +); + +ALTER TABLE money.aged_billing_summary ADD PRIMARY KEY (id); + +CREATE INDEX money_aged_billing_summary_usr_idx ON money.aged_billing_summary (usr); +CREATE INDEX money_aged_billing_summary_xact_start_idx ON money.aged_billing_summary (xact_start); + +CREATE OR REPLACE VIEW money.all_billing_summary AS + SELECT id, xact_start, xact_finish, total_paid, last_payment_ts + last_payment_note, last_payment_type, total_owed, last_billing_ts, + last_billing_note, last_billing_type, balance_owed, xact_type + FROM money.billable_xact_summary + UNION ALL + SELECT id, xact_start, xact_finish, total_paid, last_payment_ts + last_payment_note, last_payment_type, total_owed, last_billing_ts, + last_billing_note, last_billing_type, balance_owed, xact_type + FROM money.aged_billing_summary; + +-- populate the new table with aged billing/payment summary data + +COMMIT; -- 2.11.0