From 9ca0ff3a09abdb95a4c22ac64803260fb510470e Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Tue, 5 May 2020 11:01:24 -0400 Subject: [PATCH] populate aged_billing_summary table with data Signed-off-by: Chris Sharp --- .../XXXX.schema.money_aged_billing_summary.sql | 38 ++++++++++++++++++++++ 1 file changed, 38 insertions(+) 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 index fd05d96295..eeff058c79 100644 --- 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 @@ -35,6 +35,44 @@ CREATE OR REPLACE VIEW money.all_billing_summary AS last_billing_note, last_billing_type, balance_owed, xact_type FROM money.aged_billing_summary; +\qecho + -- populate the new table with aged billing/payment summary data +INSERT INTO money.aged_billing_summary + SELECT xact.id, + xact.xact_start, + xact.xact_finish, + COALESCE(credit.amount, 0.0::numeric) AS total_paid, + credit.payment_ts AS last_payment_ts, + credit.note AS last_payment_note, + credit.payment_type AS last_payment_type, + COALESCE(debit.amount, 0.0::numeric) AS total_owed, + debit.billing_ts AS last_billing_ts, + debit.note AS last_billing_note, + debit.billing_type AS last_billing_type, + COALESCE(debit.amount, 0.0::numeric) - COALESCE(credit.amount, 0.0::numeric) AS balance_owed, + 'circulation' AS xact_type + FROM action.aged_circulation xact + LEFT JOIN ( + SELECT aged_billing.xact, + sum(aged_billing.amount) AS amount, + max(aged_billing.billing_ts) AS billing_ts, + last(aged_billing.note) AS note, + last(aged_billing.billing_type) AS billing_type + FROM money.aged_billing + WHERE aged_billing.voided IS FALSE + GROUP BY aged_billing.xact + ) debit ON xact.id = debit.xact + LEFT JOIN ( + SELECT aged_payment.xact, + sum(aged_payment.amount) AS amount, + max(aged_payment.payment_ts) AS payment_ts, + last(aged_payment.note) AS note, + last(aged_payment.payment_type) AS payment_type + FROM money.aged_payment + WHERE aged_payment.voided IS FALSE + GROUP BY aged_payment.xact + ) credit ON xact.id = credit.xact + ORDER BY debit.billing_ts, credit.payment_ts; COMMIT; -- 2.11.0