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.
18 DROP SCHEMA money CASCADE;
24 CREATE TABLE money.collections_tracker (
25 id BIGSERIAL PRIMARY KEY,
26 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, -- actor.usr.id
27 collector INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
28 location INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
29 enter_time TIMESTAMP WITH TIME ZONE
31 CREATE UNIQUE INDEX m_c_t_usr_collector_location_once_idx ON money.collections_tracker (usr, collector, location);
33 CREATE TABLE money.billable_xact (
34 id BIGSERIAL PRIMARY KEY,
35 usr INT NOT NULL, -- actor.usr.id
36 xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
37 xact_finish TIMESTAMP WITH TIME ZONE
39 CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr);
41 CREATE TABLE money.grocery ( -- Catchall table for local billing
42 billing_location INT NOT NULL, -- library creating transaction
44 ) INHERITS (money.billable_xact);
45 ALTER TABLE money.grocery ADD PRIMARY KEY (id);
46 CREATE INDEX circ_open_date_idx ON "money".grocery (xact_start) WHERE xact_finish IS NULL;
47 CREATE INDEX m_g_usr_idx ON "money".grocery (usr);
49 CREATE TABLE money.billing (
50 id BIGSERIAL PRIMARY KEY,
51 xact BIGINT NOT NULL, -- money.billable_xact.id
52 billing_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
53 voided BOOL NOT NULL DEFAULT FALSE,
55 void_time TIMESTAMP WITH TIME ZONE,
56 amount NUMERIC(6,2) NOT NULL,
57 billing_type TEXT NOT NULL,
60 CREATE INDEX m_b_xact_idx ON money.billing (xact);
61 CREATE INDEX m_b_time_idx ON money.billing (billing_ts);
63 CREATE TABLE money.payment (
64 id BIGSERIAL PRIMARY KEY,
65 xact BIGINT NOT NULL, -- money.billable_xact.id
66 payment_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
67 voided BOOL NOT NULL DEFAULT FALSE,
68 amount NUMERIC(6,2) NOT NULL,
71 CREATE INDEX m_p_xact_idx ON money.payment (xact);
72 CREATE INDEX m_p_time_idx ON money.payment (payment_ts);
74 CREATE OR REPLACE VIEW money.payment_view AS
75 SELECT p.*,c.relname AS payment_type
77 JOIN pg_class c ON (p.tableoid = c.oid);
79 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
81 billing_type AS last_billing_type,
82 LAST(note) AS last_billing_note,
83 MAX(billing_ts) AS last_billing_ts,
84 SUM(COALESCE(amount,0)) AS total_owed
87 GROUP BY xact,billing_type
88 ORDER BY MAX(billing_ts);
90 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
92 LAST(billing_type) AS last_billing_type,
93 LAST(note) AS last_billing_note,
94 MAX(billing_ts) AS last_billing_ts,
95 SUM(COALESCE(amount,0)) AS total_owed
99 ORDER BY MAX(billing_ts);
101 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
103 LAST(payment_type) AS last_payment_type,
104 LAST(note) AS last_payment_note,
105 MAX(payment_ts) as last_payment_ts,
106 SUM(COALESCE(amount,0)) AS total_paid
107 FROM money.payment_view
108 WHERE voided IS FALSE
110 ORDER BY MAX(payment_ts);
112 CREATE OR REPLACE VIEW money.transaction_billing_with_void_summary AS
114 LAST(billing_type) AS last_billing_type,
115 LAST(note) AS last_billing_note,
116 MAX(billing_ts) AS last_billing_ts,
117 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_owed
120 ORDER BY MAX(billing_ts);
122 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
124 LAST(payment_type) AS last_payment_type,
125 LAST(note) AS last_payment_note,
126 MAX(payment_ts) as last_payment_ts,
127 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_paid
128 FROM money.payment_view
130 ORDER BY MAX(payment_ts);
132 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
134 billing_type AS last_billing_type,
135 LAST(note) AS last_billing_note,
136 MAX(billing_ts) AS last_billing_ts,
137 SUM(COALESCE(amount,0)) AS total_owed
139 WHERE voided IS FALSE
140 GROUP BY xact,billing_type
141 ORDER BY MAX(billing_ts);
143 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
145 LAST(billing_type) AS last_billing_type,
146 LAST(note) AS last_billing_note,
147 MAX(billing_ts) AS last_billing_ts,
148 SUM(COALESCE(amount,0)) AS total_owed
150 WHERE voided IS FALSE
152 ORDER BY MAX(billing_ts);
154 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
156 LAST(payment_type) AS last_payment_type,
157 LAST(note) AS last_payment_note,
158 MAX(payment_ts) as last_payment_ts,
159 SUM(COALESCE(amount,0)) AS total_paid
160 FROM money.payment_view
161 WHERE voided IS FALSE
163 ORDER BY MAX(payment_ts);
165 /* Replacing with the one below.
166 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
167 SELECT xact.id AS id,
169 xact.xact_start AS xact_start,
170 xact.xact_finish AS xact_finish,
172 credit.last_payment_ts,
173 credit.last_payment_note,
174 credit.last_payment_type,
176 debit.last_billing_ts,
177 debit.last_billing_note,
178 debit.last_billing_type,
179 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
180 p.relname AS xact_type
181 FROM money.billable_xact xact
182 JOIN pg_class p ON (xact.tableoid = p.oid)
183 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
184 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
187 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
188 SELECT xact.id AS id,
190 xact.xact_start AS xact_start,
191 xact.xact_finish AS xact_finish,
192 SUM(credit.amount) AS total_paid,
193 MAX(credit.payment_ts) AS last_payment_ts,
194 LAST(credit.note) AS last_payment_note,
195 LAST(credit.payment_type) AS last_payment_type,
196 SUM(debit.amount) AS total_owed,
197 MAX(debit.billing_ts) AS last_billing_ts,
198 LAST(debit.note) AS last_billing_note,
199 LAST(debit.billing_type) AS last_billing_type,
200 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
201 p.relname AS xact_type
202 FROM money.billable_xact xact
203 JOIN pg_class p ON (xact.tableoid = p.oid)
204 LEFT JOIN money.billing debit ON (xact.id = debit.xact)
205 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact)
207 ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
209 /* Replacing with the version below
210 CREATE OR REPLACE VIEW money.billable_xact_summary AS
211 SELECT xact.id AS id,
213 xact.xact_start AS xact_start,
214 xact.xact_finish AS xact_finish,
216 credit.last_payment_ts,
217 credit.last_payment_note,
218 credit.last_payment_type,
220 debit.last_billing_ts,
221 debit.last_billing_note,
222 debit.last_billing_type,
223 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
224 p.relname AS xact_type
225 FROM money.billable_xact xact
226 JOIN pg_class p ON (xact.tableoid = p.oid)
227 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
228 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
230 CREATE OR REPLACE VIEW money.billable_xact_summary AS
231 SELECT xact.id AS id,
233 xact.xact_start AS xact_start,
234 xact.xact_finish AS xact_finish,
235 SUM(credit.amount) AS total_paid,
236 MAX(credit.payment_ts) AS last_payment_ts,
237 LAST(credit.note) AS last_payment_note,
238 LAST(credit.payment_type) AS last_payment_type,
239 SUM(debit.amount) AS total_owed,
240 MAX(debit.billing_ts) AS last_billing_ts,
241 LAST(debit.note) AS last_billing_note,
242 LAST(debit.billing_type) AS last_billing_type,
243 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
244 p.relname AS xact_type
245 FROM money.billable_xact xact
246 JOIN pg_class p ON (xact.tableoid = p.oid)
247 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
248 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
250 ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
253 CREATE OR REPLACE VIEW money.billable_xact_summary AS
258 credit.amount AS total_paid,
259 credit.payment_ts AS last_payment_ts,
260 credit.note AS last_payment_note,
261 credit.payment_type AS last_payment_type,
262 debit.amount AS total_owed,
263 debit.billing_ts AS last_billing_ts,
264 debit.note AS last_billing_note,
265 debit.billing_type AS last_billing_type,
266 COALESCE(debit.amount, 0::numeric) - COALESCE(credit.amount, 0::numeric) AS balance_owed,
267 p.relname AS xact_type
268 FROM money.billable_xact xact
269 JOIN pg_class p ON xact.tableoid = p.oid
272 sum(billing.amount) AS amount,
273 max(billing.billing_ts) AS billing_ts,
274 last(billing.note) AS note,
275 last(billing.billing_type) AS billing_type
277 WHERE billing.voided IS FALSE
278 GROUP BY billing.xact
279 ) debit ON xact.id = debit.xact
281 SELECT payment_view.xact,
282 sum(payment_view.amount) AS amount,
283 max(payment_view.payment_ts) AS payment_ts,
284 last(payment_view.note) AS note,
285 last(payment_view.payment_type) AS payment_type
286 FROM money.payment_view
287 WHERE payment_view.voided IS FALSE
288 GROUP BY payment_view.xact
289 ) credit ON xact.id = credit.xact
290 ORDER BY debit.billing_ts, credit.payment_ts;
292 CREATE OR REPLACE VIEW money.usr_summary AS
294 SUM(total_paid) AS total_paid,
295 SUM(total_owed) AS total_owed,
296 SUM(balance_owed) AS balance_owed
297 FROM money.billable_xact_summary
300 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
302 SUM(total_paid) AS total_paid,
303 SUM(total_owed) AS total_owed,
304 SUM(balance_owed) AS balance_owed
305 FROM money.billable_xact_summary
306 WHERE xact_type = 'circulation'
309 CREATE TABLE money.bnm_payment (
310 amount_collected NUMERIC(6,2) NOT NULL,
311 accepting_usr INT NOT NULL
312 ) INHERITS (money.payment);
313 ALTER TABLE money.bnm_payment ADD PRIMARY KEY (id);
315 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
316 ALTER TABLE money.forgive_payment ADD PRIMARY KEY (id);
317 CREATE INDEX money_forgive_id_idx ON money.forgive_payment (id);
318 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
319 CREATE INDEX money_forgive_payment_payment_ts_idx ON money.forgive_payment (payment_ts);
320 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
322 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
323 ALTER TABLE money.work_payment ADD PRIMARY KEY (id);
324 CREATE INDEX money_work_id_idx ON money.work_payment (id);
325 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
326 CREATE INDEX money_work_payment_payment_ts_idx ON money.work_payment (payment_ts);
327 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
329 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
330 ALTER TABLE money.credit_payment ADD PRIMARY KEY (id);
331 CREATE INDEX money_credit_id_idx ON money.credit_payment (id);
332 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
333 CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment (payment_ts);
334 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
336 CREATE TABLE money.goods_payment () INHERITS (money.bnm_payment);
337 ALTER TABLE money.goods_payment ADD PRIMARY KEY (id);
338 CREATE INDEX money_goods_id_idx ON money.goods_payment (id);
339 CREATE INDEX money_goods_payment_xact_idx ON money.goods_payment (xact);
340 CREATE INDEX money_goods_payment_payment_ts_idx ON money.goods_payment (payment_ts);
341 CREATE INDEX money_goods_payment_accepting_usr_idx ON money.goods_payment (accepting_usr);
343 CREATE TABLE money.bnm_desk_payment (
344 cash_drawer INT REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED
345 ) INHERITS (money.bnm_payment);
346 ALTER TABLE money.bnm_desk_payment ADD PRIMARY KEY (id);
348 CREATE OR REPLACE VIEW money.desk_payment_view AS
349 SELECT p.*,c.relname AS payment_type
350 FROM money.bnm_desk_payment p
351 JOIN pg_class c ON (p.tableoid = c.oid);
353 CREATE OR REPLACE VIEW money.bnm_payment_view AS
354 SELECT p.*,c.relname AS payment_type
355 FROM money.bnm_payment p
356 JOIN pg_class c ON (p.tableoid = c.oid);
358 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
359 ALTER TABLE money.cash_payment ADD PRIMARY KEY (id);
360 CREATE INDEX money_cash_id_idx ON money.cash_payment (id);
361 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
362 CREATE INDEX money_cash_payment_ts_idx ON money.cash_payment (payment_ts);
363 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
364 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
366 CREATE TABLE money.check_payment (
367 check_number TEXT NOT NULL
368 ) INHERITS (money.bnm_desk_payment);
369 ALTER TABLE money.check_payment ADD PRIMARY KEY (id);
370 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
371 CREATE INDEX money_check_id_idx ON money.check_payment (id);
372 CREATE INDEX money_check_payment_ts_idx ON money.check_payment (payment_ts);
373 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
374 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
376 CREATE TABLE money.credit_card_payment (
377 cc_type TEXT NOT NULL,
378 cc_number TEXT NOT NULL,
379 expire_month INT NOT NULL,
380 expire_year INT NOT NULL,
381 approval_code TEXT NOT NULL
382 ) INHERITS (money.bnm_desk_payment);
383 ALTER TABLE money.credit_card_payment ADD PRIMARY KEY (id);
384 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
385 CREATE INDEX money_credit_card_id_idx ON money.credit_card_payment (id);
386 CREATE INDEX money_credit_card_payment_ts_idx ON money.credit_card_payment (payment_ts);
387 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
388 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);
390 CREATE OR REPLACE VIEW money.non_drawer_payment_view AS
391 SELECT p.*, c.relname AS payment_type
392 FROM money.bnm_payment p
393 JOIN pg_class c ON p.tableoid = c.oid
394 WHERE c.relname NOT IN ('cash_payment','check_payment','credit_card_payment');
396 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
397 SELECT ou.id AS org_unit,
399 t.payment_type AS payment_type,
400 p.payment_ts AS payment_ts,
404 FROM actor.org_unit ou
405 JOIN actor.workstation ws ON (ou.id = ws.owning_lib)
406 LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer)
407 LEFT JOIN money.payment_view t ON (p.id = t.id);