2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
20 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
23 COALESCE(circ.circ_lib,groc.billing_location) AS billing_location,
24 xact.xact_start AS xact_start,
25 xact.xact_finish AS xact_finish,
26 SUM(credit.amount) AS total_paid,
27 MAX(credit.payment_ts) AS last_payment_ts,
28 LAST(credit.note) AS last_payment_note,
29 LAST(credit.payment_type) AS last_payment_type,
30 SUM(debit.amount) AS total_owed,
31 MAX(debit.billing_ts) AS last_billing_ts,
32 LAST(debit.note) AS last_billing_note,
33 LAST(debit.billing_type) AS last_billing_type,
34 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
35 p.relname AS xact_type
36 FROM money.billable_xact xact
37 JOIN pg_class p ON (xact.tableoid = p.oid)
38 LEFT JOIN "action".circulation circ ON (circ.id = xact.id)
39 LEFT JOIN money.grocery groc ON (groc.id = xact.id)
43 sum(billing.amount) AS amount,
44 max(billing.billing_ts) AS billing_ts,
45 last(billing.note) AS note,
46 last(billing.billing_type) AS billing_type
48 WHERE billing.voided IS FALSE
49 GROUP BY billing.xact, billing.voided
50 ) debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
52 SELECT payment_view.xact,
54 sum(payment_view.amount) AS amount,
55 max(payment_view.payment_ts) AS payment_ts,
56 last(payment_view.note) AS note,
57 last(payment_view.payment_type) AS payment_type
58 FROM money.payment_view
59 WHERE payment_view.voided IS FALSE
60 GROUP BY payment_view.xact, payment_view.voided
61 ) credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
62 WHERE xact.xact_finish IS NULL
64 ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
67 CREATE OR REPLACE VIEW money.open_usr_summary AS
69 SUM(total_paid) AS total_paid,
70 SUM(total_owed) AS total_owed,
71 SUM(balance_owed) AS balance_owed
72 FROM money.open_billable_xact_summary
75 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
77 SUM(total_paid) AS total_paid,
78 SUM(total_owed) AS total_owed,
79 SUM(balance_owed) AS balance_owed
80 FROM money.open_billable_xact_summary
81 WHERE xact_type = 'circulation'