1 DROP SCHEMA money CASCADE;
7 CREATE TABLE money.collections_tracker (
8 id BIGSERIAL PRIMARY KEY,
9 usr INT NOT NULL REFERENCES actor.usr (id), -- actor.usr.id
10 collector INT NOT NULL REFERENCES actor.usr (id),
11 location INT NOT NULL REFERENCES actor.org_unit (id),
12 enter_time TIMESTAMP WITH TIME ZONE
14 CREATE UNIQUE INDEX m_c_t_usr_collector_location_once_idx ON money.collections_tracker (usr, collector, location);
16 CREATE TABLE money.billable_xact (
17 id BIGSERIAL PRIMARY KEY,
18 usr INT NOT NULL, -- actor.usr.id
19 xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
20 xact_finish TIMESTAMP WITH TIME ZONE
22 CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr);
24 CREATE TABLE money.grocery ( -- Catchall table for local billing
25 billing_location INT NOT NULL, -- library creating transaction
27 ) INHERITS (money.billable_xact);
28 ALTER TABLE money.grocery ADD PRIMARY KEY (id);
29 CREATE INDEX circ_open_date_idx ON "money".grocery (xact_start) WHERE xact_finish IS NULL;
30 CREATE INDEX m_g_usr_idx ON "money".grocery (usr);
32 CREATE TABLE money.billing (
33 id BIGSERIAL PRIMARY KEY,
34 xact BIGINT NOT NULL, -- money.billable_xact.id
35 billing_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
36 voided BOOL NOT NULL DEFAULT FALSE,
38 void_time TIMESTAMP WITH TIME ZONE,
39 amount NUMERIC(6,2) NOT NULL,
40 billing_type TEXT NOT NULL,
43 CREATE INDEX m_b_xact_idx ON money.billing (xact);
44 CREATE INDEX m_b_time_idx ON money.billing (billing_ts);
46 CREATE TABLE money.payment (
47 id BIGSERIAL PRIMARY KEY,
48 xact BIGINT NOT NULL, -- money.billable_xact.id
49 payment_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
50 voided BOOL NOT NULL DEFAULT FALSE,
51 amount NUMERIC(6,2) NOT NULL,
54 CREATE INDEX m_p_xact_idx ON money.payment (xact);
55 CREATE INDEX m_p_time_idx ON money.payment (payment_ts);
57 CREATE OR REPLACE VIEW money.payment_view AS
58 SELECT p.*,c.relname AS payment_type
60 JOIN pg_class c ON (p.tableoid = c.oid);
62 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
64 billing_type AS last_billing_type,
65 LAST(note) AS last_billing_note,
66 MAX(billing_ts) AS last_billing_ts,
67 SUM(COALESCE(amount,0)) AS total_owed
70 GROUP BY xact,billing_type
71 ORDER BY MAX(billing_ts);
73 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
75 LAST(billing_type) AS last_billing_type,
76 LAST(note) AS last_billing_note,
77 MAX(billing_ts) AS last_billing_ts,
78 SUM(COALESCE(amount,0)) AS total_owed
82 ORDER BY MAX(billing_ts);
84 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
86 LAST(payment_type) AS last_payment_type,
87 LAST(note) AS last_payment_note,
88 MAX(payment_ts) as last_payment_ts,
89 SUM(COALESCE(amount,0)) AS total_paid
90 FROM money.payment_view
93 ORDER BY MAX(payment_ts);
95 CREATE OR REPLACE VIEW money.transaction_billing_with_void_summary AS
97 LAST(billing_type) AS last_billing_type,
98 LAST(note) AS last_billing_note,
99 MAX(billing_ts) AS last_billing_ts,
100 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_owed
103 ORDER BY MAX(billing_ts);
105 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
107 LAST(payment_type) AS last_payment_type,
108 LAST(note) AS last_payment_note,
109 MAX(payment_ts) as last_payment_ts,
110 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_paid
111 FROM money.payment_view
113 ORDER BY MAX(payment_ts);
115 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
117 billing_type AS last_billing_type,
118 LAST(note) AS last_billing_note,
119 MAX(billing_ts) AS last_billing_ts,
120 SUM(COALESCE(amount,0)) AS total_owed
122 WHERE voided IS FALSE
123 GROUP BY xact,billing_type
124 ORDER BY MAX(billing_ts);
126 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
128 LAST(billing_type) AS last_billing_type,
129 LAST(note) AS last_billing_note,
130 MAX(billing_ts) AS last_billing_ts,
131 SUM(COALESCE(amount,0)) AS total_owed
133 WHERE voided IS FALSE
135 ORDER BY MAX(billing_ts);
137 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
139 LAST(payment_type) AS last_payment_type,
140 LAST(note) AS last_payment_note,
141 MAX(payment_ts) as last_payment_ts,
142 SUM(COALESCE(amount,0)) AS total_paid
143 FROM money.payment_view
144 WHERE voided IS FALSE
146 ORDER BY MAX(payment_ts);
148 /* Replacing with the one below.
149 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
150 SELECT xact.id AS id,
152 xact.xact_start AS xact_start,
153 xact.xact_finish AS xact_finish,
155 credit.last_payment_ts,
156 credit.last_payment_note,
157 credit.last_payment_type,
159 debit.last_billing_ts,
160 debit.last_billing_note,
161 debit.last_billing_type,
162 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
163 p.relname AS xact_type
164 FROM money.billable_xact xact
165 JOIN pg_class p ON (xact.tableoid = p.oid)
166 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
167 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
170 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
171 SELECT xact.id AS id,
173 xact.xact_start AS xact_start,
174 xact.xact_finish AS xact_finish,
175 SUM(credit.amount) AS total_paid,
176 MAX(credit.payment_ts) AS last_payment_ts,
177 LAST(credit.note) AS last_payment_note,
178 LAST(credit.payment_type) AS last_payment_type,
179 SUM(debit.amount) AS total_owed,
180 MAX(debit.billing_ts) AS last_billing_ts,
181 LAST(debit.note) AS last_billing_note,
182 LAST(debit.billing_type) AS last_billing_type,
183 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
184 p.relname AS xact_type
185 FROM money.billable_xact xact
186 JOIN pg_class p ON (xact.tableoid = p.oid)
187 LEFT JOIN money.billing debit ON (xact.id = debit.xact)
188 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact)
190 ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
192 /* Replacing with the version below
193 CREATE OR REPLACE VIEW money.billable_xact_summary AS
194 SELECT xact.id AS id,
196 xact.xact_start AS xact_start,
197 xact.xact_finish AS xact_finish,
199 credit.last_payment_ts,
200 credit.last_payment_note,
201 credit.last_payment_type,
203 debit.last_billing_ts,
204 debit.last_billing_note,
205 debit.last_billing_type,
206 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
207 p.relname AS xact_type
208 FROM money.billable_xact xact
209 JOIN pg_class p ON (xact.tableoid = p.oid)
210 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
211 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
213 CREATE OR REPLACE VIEW money.billable_xact_summary AS
214 SELECT xact.id AS id,
216 xact.xact_start AS xact_start,
217 xact.xact_finish AS xact_finish,
218 SUM(credit.amount) AS total_paid,
219 MAX(credit.payment_ts) AS last_payment_ts,
220 LAST(credit.note) AS last_payment_note,
221 LAST(credit.payment_type) AS last_payment_type,
222 SUM(debit.amount) AS total_owed,
223 MAX(debit.billing_ts) AS last_billing_ts,
224 LAST(debit.note) AS last_billing_note,
225 LAST(debit.billing_type) AS last_billing_type,
226 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
227 p.relname AS xact_type
228 FROM money.billable_xact xact
229 JOIN pg_class p ON (xact.tableoid = p.oid)
230 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
231 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
233 ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
236 CREATE OR REPLACE VIEW money.billable_xact_summary AS
241 credit.amount AS total_paid,
242 credit.payment_ts AS last_payment_ts,
243 credit.note AS last_payment_note,
244 credit.payment_type AS last_payment_type,
245 debit.amount AS total_owed,
246 debit.billing_ts AS last_billing_ts,
247 debit.note AS last_billing_note,
248 debit.billing_type AS last_billing_type,
249 COALESCE(debit.amount, 0::numeric) - COALESCE(credit.amount, 0::numeric) AS balance_owed,
250 p.relname AS xact_type
251 FROM money.billable_xact xact
252 JOIN pg_class p ON xact.tableoid = p.oid
255 sum(billing.amount) AS amount,
256 max(billing.billing_ts) AS billing_ts,
257 last(billing.note) AS note,
258 last(billing.billing_type) AS billing_type
260 WHERE billing.voided IS FALSE
261 GROUP BY billing.xact
262 ) debit ON xact.id = debit.xact
264 SELECT payment_view.xact,
265 sum(payment_view.amount) AS amount,
266 max(payment_view.payment_ts) AS payment_ts,
267 last(payment_view.note) AS note,
268 last(payment_view.payment_type) AS payment_type
269 FROM money.payment_view
270 WHERE payment_view.voided IS FALSE
271 GROUP BY payment_view.xact
272 ) credit ON xact.id = credit.xact
273 ORDER BY debit.billing_ts, credit.payment_ts;
275 CREATE OR REPLACE VIEW money.usr_summary AS
277 SUM(total_paid) AS total_paid,
278 SUM(total_owed) AS total_owed,
279 SUM(balance_owed) AS balance_owed
280 FROM money.billable_xact_summary
283 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
285 SUM(total_paid) AS total_paid,
286 SUM(total_owed) AS total_owed,
287 SUM(balance_owed) AS balance_owed
288 FROM money.billable_xact_summary
289 WHERE xact_type = 'circulation'
292 CREATE TABLE money.bnm_payment (
293 amount_collected NUMERIC(6,2) NOT NULL,
294 accepting_usr INT NOT NULL
295 ) INHERITS (money.payment);
296 ALTER TABLE money.bnm_payment ADD PRIMARY KEY (id);
298 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
299 ALTER TABLE money.forgive_payment ADD PRIMARY KEY (id);
300 CREATE INDEX money_forgive_id_idx ON money.forgive_payment (id);
301 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
302 CREATE INDEX money_forgive_payment_payment_ts_idx ON money.forgive_payment (payment_ts);
303 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
305 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
306 ALTER TABLE money.work_payment ADD PRIMARY KEY (id);
307 CREATE INDEX money_work_id_idx ON money.work_payment (id);
308 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
309 CREATE INDEX money_work_payment_payment_ts_idx ON money.work_payment (payment_ts);
310 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
312 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
313 ALTER TABLE money.credit_payment ADD PRIMARY KEY (id);
314 CREATE INDEX money_credit_id_idx ON money.credit_payment (id);
315 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
316 CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment (payment_ts);
317 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
319 CREATE TABLE money.bnm_desk_payment (
320 cash_drawer INT REFERENCES actor.workstation (id)
321 ) INHERITS (money.bnm_payment);
322 ALTER TABLE money.bnm_desk_payment ADD PRIMARY KEY (id);
324 CREATE OR REPLACE VIEW money.desk_payment_view AS
325 SELECT p.*,c.relname AS payment_type
326 FROM money.bnm_desk_payment p
327 JOIN pg_class c ON (p.tableoid = c.oid);
329 CREATE OR REPLACE VIEW money.bnm_payment_view AS
330 SELECT p.*,c.relname AS payment_type
331 FROM money.bnm_payment p
332 JOIN pg_class c ON (p.tableoid = c.oid);
334 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
335 ALTER TABLE money.cash_payment ADD PRIMARY KEY (id);
336 CREATE INDEX money_cash_id_idx ON money.cash_payment (id);
337 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
338 CREATE INDEX money_cash_payment_ts_idx ON money.cash_payment (payment_ts);
339 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
340 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
342 CREATE TABLE money.check_payment (
343 check_number TEXT NOT NULL
344 ) INHERITS (money.bnm_desk_payment);
345 ALTER TABLE money.check_payment ADD PRIMARY KEY (id);
346 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
347 CREATE INDEX money_check_id_idx ON money.check_payment (id);
348 CREATE INDEX money_check_payment_ts_idx ON money.check_payment (payment_ts);
349 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
350 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
352 CREATE TABLE money.credit_card_payment (
353 cc_type TEXT NOT NULL,
354 cc_number TEXT NOT NULL,
355 expire_month INT NOT NULL,
356 expire_year INT NOT NULL,
357 approval_code TEXT NOT NULL
358 ) INHERITS (money.bnm_desk_payment);
359 ALTER TABLE money.credit_card_payment ADD PRIMARY KEY (id);
360 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
361 CREATE INDEX money_credit_card_id_idx ON money.credit_card_payment (id);
362 CREATE INDEX money_credit_card_payment_ts_idx ON money.credit_card_payment (payment_ts);
363 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
364 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);
366 CREATE OR REPLACE VIEW money.non_drawer_payment_view AS
367 SELECT p.*, c.relname AS payment_type
368 FROM money.bnm_payment p
369 JOIN pg_class c ON p.tableoid = c.oid
370 WHERE c.relname NOT IN ('cash_payment','check_payment','credit_card_payment');
372 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
373 SELECT ou.id AS org_unit,
375 t.payment_type AS payment_type,
376 p.payment_ts AS payment_ts,
380 FROM actor.org_unit ou
381 JOIN actor.workstation ws ON (ou.id = ws.owning_lib)
382 LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer)
383 LEFT JOIN money.payment_view t ON (p.id = t.id);