From 06d72691b321aaf7baa6c0b4295931f0d7dc6f6d Mon Sep 17 00:00:00 2001 From: miker Date: Thu, 17 Nov 2005 16:54:32 +0000 Subject: [PATCH] adding "open only" views git-svn-id: svn://svn.open-ils.org/ILS/trunk@2058 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../OpenILS/Application/Storage/CDBI/money.pm | 24 +++++ .../Application/Storage/Driver/Pg/dbi.pm | 15 ++++ .../src/perlmods/OpenILS/Utils/Fieldmapper.pm | 6 ++ Open-ILS/src/sql/Pg/080.schema.money.sql | 87 ++++++++++++++++++- 4 files changed, 129 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 c49a67ba35..6661bacd80 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm @@ -14,6 +14,13 @@ __PACKAGE__->columns(Essential => qw/xact_start usr/); __PACKAGE__->columns(Others => qw/xact_finish/); #------------------------------------------------------------------------------- +package money::open_user_summary; +use base qw/money/; +__PACKAGE__->table('money_open_user_summary'); +__PACKAGE__->columns(Primary => 'usr'); +__PACKAGE__->columns(Essential => qw/total_paid total_owed balance_owed/); +#------------------------------------------------------------------------------- + package money::user_summary; use base qw/money/; __PACKAGE__->table('money_user_summary'); @@ -21,6 +28,13 @@ __PACKAGE__->columns(Primary => 'usr'); __PACKAGE__->columns(Essential => qw/total_paid total_owed balance_owed/); #------------------------------------------------------------------------------- +package money::open_user_circulation_summary; +use base qw/money/; +__PACKAGE__->table('money_open_user_circulation_summary'); +__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_circulation_summary'); @@ -28,6 +42,16 @@ __PACKAGE__->columns(Primary => 'usr'); __PACKAGE__->columns(Essential => qw/total_paid total_owed balance_owed/); #------------------------------------------------------------------------------- +package money::open_billable_transaction_summary; +use base qw/money/; +__PACKAGE__->table('money_open_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 xact_type last_billing_note last_billing_type + last_payment_note last_payment_type/); +#------------------------------------------------------------------------------- + package money::billable_transaction_summary; use base qw/money/; __PACKAGE__->table('money_billable_transaction_summary'); 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 49068c7643..7a0886e769 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 @@ -52,16 +52,31 @@ money::forgive_payment->table( 'money.forgive_payment' ); money::forgive_payment->sequence( 'money.payment_id_seq' ); + #--------------------------------------------------------------------- + package money::open_billable_transaction_summary; + + money::open_billable_transaction_summary->table( 'money.open_billable_xact_summary' ); + #--------------------------------------------------------------------- package money::billable_transaction_summary; money::billable_transaction_summary->table( 'money.billable_xact_summary' ); + #--------------------------------------------------------------------- + package money::open_user_summary; + + money::open_user_summary->table( 'money.open_usr_summary' ); + #--------------------------------------------------------------------- package money::user_summary; money::user_summary->table( 'money.usr_summary' ); + #--------------------------------------------------------------------- + package money::open_user_circulation_summary; + + money::open_user_circulation_summary->table( 'money.open_usr_circulation_summary' ); + #--------------------------------------------------------------------- package money::user_circulation_summary; diff --git a/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm b/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm index ac08467dff..86e9702097 100644 --- a/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm +++ b/Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm @@ -144,12 +144,18 @@ sub _init { 'Fieldmapper::money::billing' => { hint => 'mb' }, 'Fieldmapper::money::billable_transaction' => { hint => 'mbt' }, + 'Fieldmapper::money::open_user_summary' => { hint => 'mous', + readonly => 1 }, 'Fieldmapper::money::user_summary' => { hint => 'mus', readonly => 1 }, + 'Fieldmapper::money::open_user_circulation_summary' => { hint => 'moucs', + readonly => 1 }, 'Fieldmapper::money::user_circulation_summary' => { hint => 'mucs', readonly => 1 }, + 'Fieldmapper::money::open_billable_transaction_summary' => { hint => 'mobts', + readonly => 1 }, 'Fieldmapper::money::billable_transaction_summary' => { hint => 'mbts', readonly => 1 }, diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index 23371684a0..14cab101cb 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -38,7 +38,7 @@ CREATE OR REPLACE VIEW money.payment_view AS FROM money.payment p JOIN pg_class c ON (p.tableoid = c.oid); -CREATE OR REPLACE VIEW money.transaction_billing_summary AS +CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS SELECT xact, billing_type AS last_billing_type, LAST(note) AS last_billing_note, @@ -49,18 +49,82 @@ CREATE OR REPLACE VIEW money.transaction_billing_summary AS GROUP BY xact,billing_type ORDER BY MAX(billing_ts); +CREATE OR REPLACE VIEW money.transaction_billing_summary AS + SELECT xact, + LAST(billing_type) AS last_billing_type, + LAST(note) AS last_billing_note, + MAX(billing_ts) AS last_billing_ts, + SUM(COALESCE(amount,0)) AS total_owed + FROM money.billing + WHERE voided IS FALSE + GROUP BY xact + ORDER BY MAX(billing_ts); + CREATE OR REPLACE VIEW money.transaction_payment_summary AS SELECT xact, - payment_type AS last_payment_type, + LAST(payment_type) AS last_payment_type, LAST(note) AS last_payment_note, MAX(payment_ts) as last_payment_ts, SUM(COALESCE(amount,0)) AS total_paid FROM money.payment_view WHERE voided IS FALSE - GROUP BY xact,payment_type + GROUP BY xact + ORDER BY MAX(payment_ts); + +CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS + SELECT xact, + billing_type AS last_billing_type, + LAST(note) AS last_billing_note, + MAX(billing_ts) AS last_billing_ts, + SUM(COALESCE(amount,0)) AS total_owed + FROM money.billing + WHERE voided IS FALSE + GROUP BY xact,billing_type + ORDER BY MAX(billing_ts); + +CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS + SELECT xact, + LAST(billing_type) AS last_billing_type, + LAST(note) AS last_billing_note, + MAX(billing_ts) AS last_billing_ts, + SUM(COALESCE(amount,0)) AS total_owed + FROM money.billing + WHERE voided IS FALSE + GROUP BY xact + ORDER BY MAX(billing_ts); + +CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS + SELECT xact, + LAST(payment_type) AS last_payment_type, + LAST(note) AS last_payment_note, + MAX(payment_ts) as last_payment_ts, + SUM(COALESCE(amount,0)) AS total_paid + FROM money.payment_view + WHERE voided IS FALSE + GROUP BY xact ORDER BY MAX(payment_ts); CREATE OR REPLACE VIEW money.billable_xact_summary AS + SELECT xact.id AS id, + xact.usr AS usr, + xact.xact_start AS xact_start, + xact.xact_finish AS xact_finish, + credit.total_paid, + credit.last_payment_ts, + credit.last_payment_note, + credit.last_payment_type, + debit.total_owed, + debit.last_billing_ts, + debit.last_billing_note, + debit.last_billing_type, + COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,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.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.open_billable_xact_summary AS SELECT xact.id AS id, xact.usr AS usr, xact.xact_start AS xact_start, @@ -81,6 +145,23 @@ CREATE OR REPLACE VIEW money.billable_xact_summary AS LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact) WHERE xact.xact_finish IS NULL; +CREATE OR REPLACE VIEW money.open_usr_summary AS + SELECT usr, + SUM(total_paid) AS total_paid, + SUM(total_owed) AS total_owed, + SUM(balance_owed) AS balance_owed + FROM money.open_billable_xact_summary + GROUP BY 1; + +CREATE OR REPLACE VIEW money.open_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.open_billable_xact_summary + WHERE xact_type = 'circulation' + GROUP BY 1; + CREATE OR REPLACE VIEW money.usr_summary AS SELECT usr, SUM(total_paid) AS total_paid, -- 2.43.2