From 8c69ea5364a75a210499e33ffefeccf472c07693 Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 30 Apr 2008 04:50:12 +0000 Subject: [PATCH] adding some more billing report views git-svn-id: svn://svn.open-ils.org/ILS/trunk@9490 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 99 +++++++++++++++++++ .../src/sql/Pg/example.reporter-extension.sql | 77 +++++++++++++++ 2 files changed, 176 insertions(+) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index aa31d33b12..e63437ef88 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -3647,6 +3647,105 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/example.reporter-extension.sql b/Open-ILS/src/sql/Pg/example.reporter-extension.sql index c359d911be..00c79e4e6f 100644 --- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql +++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql @@ -224,6 +224,83 @@ SELECT t.value as title, LEFT JOIN asset.stat_cat_entry_copy_map sc2 ON (sc2.owning_copy = cp.id AND sc2.stat_cat = 2) LEFT JOIN asset.stat_cat_entry sce2 ON (sce2.id = sc2.stat_cat_entry); + +CREATE OR REPLACE VIEW money.open_circ_balance_by_owning_lib AS + SELECT circ.id, + cn.owning_lib, + bill.billing_type, + SUM(bill.amount) AS billed + FROM action.circulation circ + JOIN money.billing bill ON (circ.id = bill.xact) + JOIN asset.copy cp ON (circ.target_copy = cp.id) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + WHERE circ.xact_finish IS NULL + AND NOT bill.voided + GROUP BY 1,2,3 + ORDER BY 1,2,3; + +CREATE OR REPLACE VIEW money.open_balance_by_owning_lib AS + SELECT owning_lib, + ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, + SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance + FROM money.open_circ_balance_by_owning_lib x + GROUP BY 1; + + + + + +CREATE OR REPLACE VIEW money.open_circ_balance_by_circ_and_owning_lib AS + SELECT circ.id, + circ.circ_lib, + cn.owning_lib, + bill.billing_type, + SUM(bill.amount) AS billed + FROM action.circulation circ + JOIN money.billing bill ON (circ.id = bill.xact) + JOIN asset.copy cp ON (circ.target_copy = cp.id) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + WHERE circ.xact_finish IS NULL + AND NOT bill.voided + GROUP BY 1,2,3,4 + ORDER BY 1,2,3,4; + +CREATE OR REPLACE VIEW money.open_balance_by_circ_and_owning_lib AS + SELECT circ_lib, + owning_lib, + ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, + SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance + FROM money.open_circ_balance_by_circ_and_owning_lib x + GROUP BY 1,2; + + + + + +CREATE OR REPLACE VIEW money.open_circ_balance_by_usr_home_and_owning_lib AS + SELECT circ.id, + usr.home_ou, + cn.owning_lib, + bill.billing_type, + SUM(bill.amount) AS billed + FROM action.circulation circ + JOIN money.billing bill ON (circ.id = bill.xact) + JOIN asset.copy cp ON (circ.target_copy = cp.id) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + JOIN actor.usr usr ON (circ.usr = usr.id) + WHERE circ.xact_finish IS NULL + AND NOT bill.voided + GROUP BY 1,2,3,4 + ORDER BY 1,2,3,4; + +CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS + SELECT home_ou, + owning_lib, + ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, + SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance + FROM money.open_circ_balance_by_usr_home_and_owning_lib x + GROUP BY 1,2; + COMMIT; -- 2.43.2