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
15 CREATE TABLE money.billable_xact (
16 id BIGSERIAL PRIMARY KEY,
17 usr INT NOT NULL, -- actor.usr.id
18 xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
19 xact_finish TIMESTAMP WITH TIME ZONE
21 CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr) WHERE xact_finish IS NULL;
23 CREATE TABLE money.grocery ( -- Catchall table for local billing
24 billing_location INT NOT NULL, -- library creating transaction
26 ) INHERITS (money.billable_xact);
28 CREATE TABLE money.billing (
29 id BIGSERIAL PRIMARY KEY,
30 xact BIGINT NOT NULL, -- money.billable_xact.id
31 billing_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
32 voided BOOL NOT NULL DEFAULT FALSE,
34 void_time TIMESTAMP WITH TIME ZONE,
35 amount NUMERIC(6,2) NOT NULL,
36 billing_type TEXT NOT NULL,
39 CREATE INDEX m_b_xact_idx ON money.billing (xact);
41 CREATE TABLE money.payment (
42 id BIGSERIAL PRIMARY KEY,
43 xact BIGINT NOT NULL, -- money.billable_xact.id
44 payment_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
45 voided BOOL NOT NULL DEFAULT FALSE,
46 amount NUMERIC(6,2) NOT NULL,
49 CREATE INDEX m_p_xact_idx ON money.payment (xact);
51 CREATE OR REPLACE VIEW money.payment_view AS
52 SELECT p.*,c.relname AS payment_type
54 JOIN pg_class c ON (p.tableoid = c.oid);
56 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
58 billing_type AS last_billing_type,
59 LAST(note) AS last_billing_note,
60 MAX(billing_ts) AS last_billing_ts,
61 SUM(COALESCE(amount,0)) AS total_owed
64 GROUP BY xact,billing_type
65 ORDER BY MAX(billing_ts);
67 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
69 LAST(billing_type) AS last_billing_type,
70 LAST(note) AS last_billing_note,
71 MAX(billing_ts) AS last_billing_ts,
72 SUM(COALESCE(amount,0)) AS total_owed
76 ORDER BY MAX(billing_ts);
78 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
80 LAST(payment_type) AS last_payment_type,
81 LAST(note) AS last_payment_note,
82 MAX(payment_ts) as last_payment_ts,
83 SUM(COALESCE(amount,0)) AS total_paid
84 FROM money.payment_view
87 ORDER BY MAX(payment_ts);
89 CREATE OR REPLACE VIEW money.transaction_billing_with_void_summary AS
91 LAST(billing_type) AS last_billing_type,
92 LAST(note) AS last_billing_note,
93 MAX(billing_ts) AS last_billing_ts,
94 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_owed
97 ORDER BY MAX(billing_ts);
99 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
101 LAST(payment_type) AS last_payment_type,
102 LAST(note) AS last_payment_note,
103 MAX(payment_ts) as last_payment_ts,
104 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_paid
105 FROM money.payment_view
107 ORDER BY MAX(payment_ts);
109 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
111 billing_type AS last_billing_type,
112 LAST(note) AS last_billing_note,
113 MAX(billing_ts) AS last_billing_ts,
114 SUM(COALESCE(amount,0)) AS total_owed
116 WHERE voided IS FALSE
117 GROUP BY xact,billing_type
118 ORDER BY MAX(billing_ts);
120 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
122 LAST(billing_type) AS last_billing_type,
123 LAST(note) AS last_billing_note,
124 MAX(billing_ts) AS last_billing_ts,
125 SUM(COALESCE(amount,0)) AS total_owed
127 WHERE voided IS FALSE
129 ORDER BY MAX(billing_ts);
131 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
133 LAST(payment_type) AS last_payment_type,
134 LAST(note) AS last_payment_note,
135 MAX(payment_ts) as last_payment_ts,
136 SUM(COALESCE(amount,0)) AS total_paid
137 FROM money.payment_view
138 WHERE voided IS FALSE
140 ORDER BY MAX(payment_ts);
142 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
143 SELECT xact.id AS id,
145 xact.xact_start AS xact_start,
146 xact.xact_finish AS xact_finish,
148 credit.last_payment_ts,
149 credit.last_payment_note,
150 credit.last_payment_type,
152 debit.last_billing_ts,
153 debit.last_billing_note,
154 debit.last_billing_type,
155 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
156 p.relname AS xact_type
157 FROM money.billable_xact xact
158 JOIN pg_class p ON (xact.tableoid = p.oid)
159 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
160 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
162 CREATE OR REPLACE VIEW money.billable_xact_summary AS
163 SELECT xact.id AS id,
165 xact.xact_start AS xact_start,
166 xact.xact_finish AS xact_finish,
168 credit.last_payment_ts,
169 credit.last_payment_note,
170 credit.last_payment_type,
172 debit.last_billing_ts,
173 debit.last_billing_note,
174 debit.last_billing_type,
175 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
176 p.relname AS xact_type
177 FROM money.billable_xact xact
178 JOIN pg_class p ON (xact.tableoid = p.oid)
179 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
180 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
182 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
183 SELECT xact.id AS id,
185 xact.xact_start AS xact_start,
186 xact.xact_finish AS xact_finish,
188 credit.last_payment_ts,
189 credit.last_payment_note,
190 credit.last_payment_type,
192 debit.last_billing_ts,
193 debit.last_billing_note,
194 debit.last_billing_type,
195 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
196 p.relname AS xact_type
197 FROM money.billable_xact xact
198 JOIN pg_class p ON (xact.tableoid = p.oid)
199 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
200 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact)
201 WHERE xact.xact_finish IS NULL;
203 CREATE OR REPLACE VIEW money.open_usr_summary AS
205 SUM(total_paid) AS total_paid,
206 SUM(total_owed) AS total_owed,
207 SUM(balance_owed) AS balance_owed
208 FROM money.open_billable_xact_summary
211 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
213 SUM(total_paid) AS total_paid,
214 SUM(total_owed) AS total_owed,
215 SUM(balance_owed) AS balance_owed
216 FROM money.open_billable_xact_summary
217 WHERE xact_type = 'circulation'
220 CREATE OR REPLACE VIEW money.usr_summary AS
222 SUM(total_paid) AS total_paid,
223 SUM(total_owed) AS total_owed,
224 SUM(balance_owed) AS balance_owed
225 FROM money.billable_xact_summary
228 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
230 SUM(total_paid) AS total_paid,
231 SUM(total_owed) AS total_owed,
232 SUM(balance_owed) AS balance_owed
233 FROM money.billable_xact_summary
234 WHERE xact_type = 'circulation'
237 CREATE TABLE money.bnm_payment (
238 amount_collected NUMERIC(6,2) NOT NULL,
239 accepting_usr INT NOT NULL
240 ) INHERITS (money.payment);
242 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
243 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
244 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
246 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
247 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
248 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
250 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
251 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
252 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
254 CREATE TABLE money.bnm_desk_payment (
255 cash_drawer INT REFERENCES actor.workstation (id)
256 ) INHERITS (money.bnm_payment);
258 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
259 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
260 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
261 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
263 CREATE TABLE money.check_payment (
264 check_number TEXT NOT NULL
265 ) INHERITS (money.bnm_desk_payment);
266 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
267 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
268 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
270 CREATE TABLE money.credit_card_payment (
271 cc_type TEXT NOT NULL,
272 cc_number TEXT NOT NULL,
273 expire_month INT NOT NULL,
274 expire_year INT NOT NULL,
275 approval_code TEXT NOT NULL
276 ) INHERITS (money.bnm_desk_payment);
277 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
278 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
279 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);