adding "open only" views
authormiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Thu, 17 Nov 2005 16:54:32 +0000 (16:54 +0000)
committermiker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Thu, 17 Nov 2005 16:54:32 +0000 (16:54 +0000)
git-svn-id: svn://svn.open-ils.org/ILS/trunk@2058 dcc99617-32d9-48b4-a31d-7c20da2025e4

Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/money.pm
Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm
Open-ILS/src/perlmods/OpenILS/Utils/Fieldmapper.pm
Open-ILS/src/sql/Pg/080.schema.money.sql

index c49a67b..6661bac 100644 (file)
@@ -14,6 +14,13 @@ __PACKAGE__->columns(Essential => qw/xact_start usr/);
 __PACKAGE__->columns(Others => qw/xact_finish/);
 #-------------------------------------------------------------------------------
 
 __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');
 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__->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');
 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__->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');
 package money::billable_transaction_summary;
 use base qw/money/;
 __PACKAGE__->table('money_billable_transaction_summary');
index 49068c7..7a0886e 100644 (file)
        money::forgive_payment->sequence( 'money.payment_id_seq' );
 
        #---------------------------------------------------------------------
        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::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::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;
        
        money::user_circulation_summary->table( 'money.usr_circulation_summary' );
        package money::user_circulation_summary;
        
        money::user_circulation_summary->table( 'money.usr_circulation_summary' );
index ac08467..86e9702 100644 (file)
@@ -144,12 +144,18 @@ sub _init {
                'Fieldmapper::money::billing'                   => { hint => 'mb'  },
                'Fieldmapper::money::billable_transaction'      => { hint => 'mbt'  },
 
                '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::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::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 },
 
                'Fieldmapper::money::billable_transaction_summary'      => { hint       => 'mbts',
                                                                             readonly   => 1 },
 
index 2337168..14cab10 100644 (file)
@@ -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);
 
          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,
        SELECT  xact,
                billing_type AS last_billing_type,
                LAST(note) AS last_billing_note,
@@ -49,15 +49,59 @@ CREATE OR REPLACE VIEW money.transaction_billing_summary AS
          GROUP BY xact,billing_type
          ORDER BY MAX(billing_ts);
 
          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,
 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
                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
          ORDER BY MAX(payment_ts);
 
 CREATE OR REPLACE VIEW money.billable_xact_summary AS
@@ -78,9 +122,46 @@ CREATE OR REPLACE VIEW money.billable_xact_summary AS
          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)
          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,
+               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)
          WHERE xact.xact_finish IS NULL;
 
                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,
 CREATE OR REPLACE VIEW money.usr_summary AS
        SELECT  usr,
                SUM(total_paid) AS total_paid,