__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__->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__->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');
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;
money::user_circulation_summary->table( 'money.usr_circulation_summary' );
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,
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
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;
+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,
projects / Evergreen.git / commitdiff