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);
214 CREATE OR REPLACE VIEW money.billable_xact_summary AS
215 SELECT xact.id AS id,
217 xact.xact_start AS xact_start,
218 xact.xact_finish AS xact_finish,
219 SUM(credit.amount) AS total_paid,
220 MAX(credit.payment_ts) AS last_payment_ts,
221 LAST(credit.note) AS last_payment_note,
222 LAST(credit.payment_type) AS last_payment_type,
223 SUM(debit.amount) AS total_owed,
224 MAX(debit.billing_ts) AS last_billing_ts,
225 LAST(debit.note) AS last_billing_note,
226 LAST(debit.billing_type) AS last_billing_type,
227 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
228 p.relname AS xact_type
229 FROM money.billable_xact xact
230 JOIN pg_class p ON (xact.tableoid = p.oid)
231 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
232 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
234 ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
236 CREATE OR REPLACE VIEW money.usr_summary AS
238 SUM(total_paid) AS total_paid,
239 SUM(total_owed) AS total_owed,
240 SUM(balance_owed) AS balance_owed
241 FROM money.billable_xact_summary
244 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
246 SUM(total_paid) AS total_paid,
247 SUM(total_owed) AS total_owed,
248 SUM(balance_owed) AS balance_owed
249 FROM money.billable_xact_summary
250 WHERE xact_type = 'circulation'
253 CREATE TABLE money.bnm_payment (
254 amount_collected NUMERIC(6,2) NOT NULL,
255 accepting_usr INT NOT NULL
256 ) INHERITS (money.payment);
257 ALTER TABLE money.bnm_payment ADD PRIMARY KEY (id);
259 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
260 ALTER TABLE money.forgive_payment ADD PRIMARY KEY (id);
261 CREATE INDEX money_forgive_id_idx ON money.forgive_payment (id);
262 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
263 CREATE INDEX money_forgive_payment_payment_ts_idx ON money.forgive_payment (payment_ts);
264 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
266 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
267 ALTER TABLE money.work_payment ADD PRIMARY KEY (id);
268 CREATE INDEX money_work_id_idx ON money.work_payment (id);
269 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
270 CREATE INDEX money_work_payment_payment_ts_idx ON money.work_payment (payment_ts);
271 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
273 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
274 ALTER TABLE money.credit_payment ADD PRIMARY KEY (id);
275 CREATE INDEX money_credit_id_idx ON money.credit_payment (id);
276 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
277 CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment (payment_ts);
278 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
280 CREATE TABLE money.bnm_desk_payment (
281 cash_drawer INT REFERENCES actor.workstation (id)
282 ) INHERITS (money.bnm_payment);
283 ALTER TABLE money.bnm_desk_payment ADD PRIMARY KEY (id);
285 CREATE OR REPLACE VIEW money.desk_payment_view AS
286 SELECT p.*,c.relname AS payment_type
287 FROM money.bnm_desk_payment p
288 JOIN pg_class c ON (p.tableoid = c.oid);
290 CREATE OR REPLACE VIEW money.bnm_payment_view AS
291 SELECT p.*,c.relname AS payment_type
292 FROM money.bnm_payment p
293 JOIN pg_class c ON (p.tableoid = c.oid);
295 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
296 ALTER TABLE money.cash_payment ADD PRIMARY KEY (id);
297 CREATE INDEX money_cash_id_idx ON money.cash_payment (id);
298 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
299 CREATE INDEX money_cash_payment_ts_idx ON money.cash_payment (payment_ts);
300 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
301 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
303 CREATE TABLE money.check_payment (
304 check_number TEXT NOT NULL
305 ) INHERITS (money.bnm_desk_payment);
306 ALTER TABLE money.check_payment ADD PRIMARY KEY (id);
307 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
308 CREATE INDEX money_check_id_idx ON money.check_payment (id);
309 CREATE INDEX money_check_payment_ts_idx ON money.check_payment (payment_ts);
310 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
311 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
313 CREATE TABLE money.credit_card_payment (
314 cc_type TEXT NOT NULL,
315 cc_number TEXT NOT NULL,
316 expire_month INT NOT NULL,
317 expire_year INT NOT NULL,
318 approval_code TEXT NOT NULL
319 ) INHERITS (money.bnm_desk_payment);
320 ALTER TABLE money.credit_card_payment ADD PRIMARY KEY (id);
321 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
322 CREATE INDEX money_credit_card_id_idx ON money.credit_card_payment (id);
323 CREATE INDEX money_credit_card_payment_ts_idx ON money.credit_card_payment (payment_ts);
324 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
325 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);
327 CREATE OR REPLACE VIEW money.non_drawer_payment_view AS
328 SELECT p.*, c.relname AS payment_type
329 FROM money.bnm_payment p
330 JOIN pg_class c ON p.tableoid = c.oid
331 WHERE c.relname NOT IN ('cash_payment','check_payment','credit_card_payment');
333 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
334 SELECT ou.id AS org_unit,
336 t.payment_type AS payment_type,
337 p.payment_ts AS payment_ts,
341 FROM actor.org_unit ou
342 JOIN actor.workstation ws ON (ou.id = ws.owning_lib)
343 LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer)
344 LEFT JOIN money.payment_view t ON (p.id = t.id);