From 6e346c59d1c431c45f4ad862f48b1fa4c4632d76 Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 5 Sep 2007 05:30:38 +0000 Subject: [PATCH] corrected money.billable_xact_summary view git-svn-id: svn://svn.open-ils.org/ILS/trunk@7755 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/080.schema.money.sql | 41 +++++++++++++++++++++++++++++++- 1 file changed, 40 insertions(+), 1 deletion(-) diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index 19b8994161..1e6f1e46dc 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -209,7 +209,6 @@ CREATE OR REPLACE VIEW money.billable_xact_summary AS JOIN pg_class p ON (xact.tableoid = p.oid) LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact) LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact); -*/ CREATE OR REPLACE VIEW money.billable_xact_summary AS SELECT xact.id AS id, @@ -232,6 +231,46 @@ CREATE OR REPLACE VIEW money.billable_xact_summary AS LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE) GROUP BY 1,2,3,4,14 ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts); +*/ + +CREATE OR REPLACE VIEW money.billable_xact_summary AS + SELECT xact.id, + xact.usr, + xact.xact_start, + xact.xact_finish, + credit.amount AS total_paid, + credit.payment_ts AS last_payment_ts, + credit.note AS last_payment_note, + credit.payment_type AS last_payment_type, + debit.amount 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::numeric) - COALESCE(credit.amount, 0::numeric) AS balance_owed, + p.relname AS xact_type + FROM money.billable_xact xact + JOIN pg_class p ON xact.tableoid = p.oid + LEFT JOIN ( + SELECT billing.xact, + sum(billing.amount) AS amount, + max(billing.billing_ts) AS billing_ts, + last(billing.note) AS note, + last(billing.billing_type) AS billing_type + FROM money.billing + WHERE billing.voided IS FALSE + GROUP BY billing.xact + ) debit ON xact.id = debit.xact + LEFT JOIN ( + SELECT payment_view.xact, + sum(payment_view.amount) AS amount, + max(payment_view.payment_ts) AS payment_ts, + last(payment_view.note) AS note, + last(payment_view.payment_type) AS payment_type + FROM money.payment_view + WHERE payment_view.voided IS FALSE + GROUP BY payment_view.xact + ) credit ON xact.id = credit.xact + ORDER BY debit.billing_ts, credit.payment_ts; CREATE OR REPLACE VIEW money.usr_summary AS SELECT usr, -- 2.11.0