3 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
6 COALESCE(circ.circ_lib,groc.billing_location) AS billing_location,
7 xact.xact_start AS xact_start,
8 xact.xact_finish AS xact_finish,
9 SUM(credit.amount) AS total_paid,
10 MAX(credit.payment_ts) AS last_payment_ts,
11 LAST(credit.note) AS last_payment_note,
12 LAST(credit.payment_type) AS last_payment_type,
13 SUM(debit.amount) AS total_owed,
14 MAX(debit.billing_ts) AS last_billing_ts,
15 LAST(debit.note) AS last_billing_note,
16 LAST(debit.billing_type) AS last_billing_type,
17 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
18 p.relname AS xact_type
19 FROM money.billable_xact xact
20 JOIN pg_class p ON (xact.tableoid = p.oid)
21 LEFT JOIN "action".circulation circ ON (circ.id = xact.id)
22 LEFT JOIN money.grocery groc ON (groc.id = xact.id)
26 sum(billing.amount) AS amount,
27 max(billing.billing_ts) AS billing_ts,
28 last(billing.note) AS note,
29 last(billing.billing_type) AS billing_type
31 WHERE billing.voided IS FALSE
32 GROUP BY billing.xact, billing.voided
33 ) debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
35 SELECT payment_view.xact,
37 sum(payment_view.amount) AS amount,
38 max(payment_view.payment_ts) AS payment_ts,
39 last(payment_view.note) AS note,
40 last(payment_view.payment_type) AS payment_type
41 FROM money.payment_view
42 WHERE payment_view.voided IS FALSE
43 GROUP BY payment_view.xact, payment_view.voided
44 ) credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
45 WHERE xact.xact_finish IS NULL
47 ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
50 CREATE OR REPLACE VIEW money.open_usr_summary AS
52 SUM(total_paid) AS total_paid,
53 SUM(total_owed) AS total_owed,
54 SUM(balance_owed) AS balance_owed
55 FROM money.open_billable_xact_summary
58 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
60 SUM(total_paid) AS total_paid,
61 SUM(total_owed) AS total_owed,
62 SUM(balance_owed) AS balance_owed
63 FROM money.open_billable_xact_summary
64 WHERE xact_type = 'circulation'