From 1ffb4265df7d1dab1b2c297c494cd3beb170f2ae Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 18 Jul 2005 16:51:22 +0000 Subject: [PATCH] adding user_circulation_summary; adding xact_type to billable_transaction_summary git-svn-id: svn://svn.open-ils.org/ILS/trunk@1260 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../perlmods/OpenILS/Application/Storage/CDBI/money.pm | 9 ++++++++- .../perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm | 5 +++++ Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm | 3 +++ Open-ILS/src/sql/Postgres/080.schema.money.sql | 15 +++++++++++++-- 4 files changed, 29 insertions(+), 3 deletions(-) diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm index 52b69e8956..2fc3eaaf7c 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm @@ -21,13 +21,20 @@ __PACKAGE__->columns(Primary => 'usr'); __PACKAGE__->columns(Essential => qw/total_paid total_owed balance_owed/); #------------------------------------------------------------------------------- +package money::user_circulation_summary; +use base qw/money/; +__PACKAGE__->table('money_user_summary'); +__PACKAGE__->columns(Primary => 'usr'); +__PACKAGE__->columns(Essential => qw/total_paid total_owed balance_owed/); +#------------------------------------------------------------------------------- + package money::billable_transaction_summary; use base qw/money/; __PACKAGE__->table('money_billable_transaction_summary'); __PACKAGE__->columns(Primary => 'id'); __PACKAGE__->columns(Essential => qw/xact_start usr xact_finish total_paid last_payment_ts total_owed last_billing_ts - balance_owed/); + balance_owed xact_type/); #------------------------------------------------------------------------------- package money::billing; diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm index db1885e0e5..30aee17360 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm @@ -64,6 +64,11 @@ money::user_summary->table( 'money.usr_summary' ); #--------------------------------------------------------------------- + package money::user_circulation_summary; + + money::user_summary->table( 'money.usr_circulation_summary' ); + + #--------------------------------------------------------------------- package action::circulation; action::circulation->table( 'action.circulation' ); diff --git a/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm b/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm index 91a088253e..bf171ef719 100644 --- a/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm +++ b/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm @@ -138,6 +138,9 @@ sub _init { 'Fieldmapper::money::user_summary' => { hint => 'mus', readonly => 1 }, + 'Fieldmapper::money::user_circulation_summary' => { hint => 'mucs', + readonly => 1 }, + 'Fieldmapper::money::billable_transaction_summary' => { hint => 'mbts', readonly => 1 }, diff --git a/Open-ILS/src/sql/Postgres/080.schema.money.sql b/Open-ILS/src/sql/Postgres/080.schema.money.sql index df10cf4ec3..bff25d42ed 100644 --- a/Open-ILS/src/sql/Postgres/080.schema.money.sql +++ b/Open-ILS/src/sql/Postgres/080.schema.money.sql @@ -41,12 +41,14 @@ CREATE OR REPLACE VIEW money.billable_xact_summary AS MAX(credit.payment_ts) AS last_payment_ts, SUM(COALESCE(debit.amount,0)) AS total_owed, MAX(debit.billing_ts) AS last_billing_ts, - SUM(COALESCE(debit.amount,0) - COALESCE(credit.amount,0)) AS balance_owed + SUM(COALESCE(debit.amount,0) - COALESCE(credit.amount,0)) AS balance_owed, + p.relname AS xact_type FROM money.billable_xact xact + JOIN pg_class p ON (xact.tableoid = p.oid) LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE) LEFT JOIN money.payment credit ON (xact.id = credit.xact AND credit.voided IS FALSE) WHERE xact.xact_finish IS NULL - GROUP BY 1,2,3,4; + GROUP BY 1,2,3,4,10; CREATE OR REPLACE VIEW money.usr_summary AS SELECT usr, @@ -56,6 +58,15 @@ CREATE OR REPLACE VIEW money.usr_summary AS FROM money.billable_xact_summary GROUP BY 1; +CREATE OR REPLACE VIEW money.usr_circulation_summary AS + SELECT usr, + SUM(total_paid) AS total_paid, + SUM(total_owed) AS total_owed, + SUM(balance_owed) AS balance_owed + FROM money.billable_xact_summary + WHERE xact_type = 'circulation' + GROUP BY 1; + CREATE TABLE money.bnm_payment ( amount_collected NUMERIC(6,2) NOT NULL, accepting_usr INT NOT NULL -- 2.11.0