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,res.pickup_lib) 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)
40 LEFT JOIN booking.reservation res ON (groc.id = xact.id)
44 sum(billing.amount) AS amount,
45 max(billing.billing_ts) AS billing_ts,
46 last(billing.note) AS note,
47 last(billing.billing_type) AS billing_type
49 WHERE billing.voided IS FALSE
50 GROUP BY billing.xact, billing.voided
51 ) debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
53 SELECT payment_view.xact,
55 sum(payment_view.amount) AS amount,
56 max(payment_view.payment_ts) AS payment_ts,
57 last(payment_view.note) AS note,
58 last(payment_view.payment_type) AS payment_type
59 FROM money.payment_view
60 WHERE payment_view.voided IS FALSE
61 GROUP BY payment_view.xact, payment_view.voided
62 ) credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
63 WHERE xact.xact_finish IS NULL
65 ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
68 CREATE OR REPLACE VIEW money.open_usr_summary AS
70 SUM(total_paid) AS total_paid,
71 SUM(total_owed) AS total_owed,
72 SUM(balance_owed) AS balance_owed
73 FROM money.open_billable_xact_summary
76 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
78 SUM(total_paid) AS total_paid,
79 SUM(total_owed) AS total_owed,
80 SUM(balance_owed) AS balance_owed
81 FROM money.open_billable_xact_summary
82 WHERE xact_type = 'circulation'
85 -- Not a view, but it's cross-schema..
86 CREATE TABLE config.idl_field_doc (
87 id BIGSERIAL PRIMARY KEY,
88 fm_class TEXT NOT NULL,
90 owner INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
93 CREATE UNIQUE INDEX idl_field_doc_identity ON config.idl_field_doc (fm_class,field,owner);