updating view to be more direct/less obtuse
[Evergreen.git] / Open-ILS / src / sql / Pg / 080.schema.money.sql
1 DROP SCHEMA money CASCADE;
2
3 BEGIN;
4
5 CREATE SCHEMA money;
6
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
13 );
14 CREATE UNIQUE INDEX m_c_t_usr_collector_location_once_idx ON money.collections_tracker (usr, collector, location);
15
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
21 );
22 CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr);
23
24 CREATE TABLE money.grocery ( -- Catchall table for local billing
25         billing_location        INT     NOT NULL, -- library creating transaction
26         note                    TEXT
27 ) INHERITS (money.billable_xact);
28 ALTER TABLE money.grocery ADD PRIMARY KEY (id);
29
30 CREATE TABLE money.billing (
31         id              BIGSERIAL                       PRIMARY KEY,
32         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
33         billing_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
34         voided          BOOL                            NOT NULL DEFAULT FALSE,
35         voider          INT,
36         void_time       TIMESTAMP WITH TIME ZONE,
37         amount          NUMERIC(6,2)                    NOT NULL,
38         billing_type    TEXT                            NOT NULL,
39         note            TEXT
40 );
41 CREATE INDEX m_b_xact_idx ON money.billing (xact);
42 CREATE INDEX m_b_time_idx ON money.billing (billing_ts);
43
44 CREATE TABLE money.payment (
45         id              BIGSERIAL                       PRIMARY KEY,
46         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
47         payment_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
48         voided          BOOL                            NOT NULL DEFAULT FALSE,
49         amount          NUMERIC(6,2)                    NOT NULL,
50         note            TEXT
51 );
52 CREATE INDEX m_p_xact_idx ON money.payment (xact);
53 CREATE INDEX m_p_time_idx ON money.payment (payment_ts);
54
55 CREATE OR REPLACE VIEW money.payment_view AS
56         SELECT  p.*,c.relname AS payment_type
57           FROM  money.payment p
58                 JOIN pg_class c ON (p.tableoid = c.oid);
59
60 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
61         SELECT  xact,
62                 billing_type AS last_billing_type,
63                 LAST(note) AS last_billing_note,
64                 MAX(billing_ts) AS last_billing_ts,
65                 SUM(COALESCE(amount,0)) AS total_owed
66           FROM  money.billing
67           WHERE voided IS FALSE
68           GROUP BY xact,billing_type
69           ORDER BY MAX(billing_ts);
70
71 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
72         SELECT  xact,
73                 LAST(billing_type) AS last_billing_type,
74                 LAST(note) AS last_billing_note,
75                 MAX(billing_ts) AS last_billing_ts,
76                 SUM(COALESCE(amount,0)) AS total_owed
77           FROM  money.billing
78           WHERE voided IS FALSE
79           GROUP BY xact
80           ORDER BY MAX(billing_ts);
81
82 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
83         SELECT  xact,
84                 LAST(payment_type) AS last_payment_type,
85                 LAST(note) AS last_payment_note,
86                 MAX(payment_ts) as last_payment_ts,
87                 SUM(COALESCE(amount,0)) AS total_paid
88           FROM  money.payment_view
89           WHERE voided IS FALSE
90           GROUP BY xact
91           ORDER BY MAX(payment_ts);
92
93 CREATE OR REPLACE VIEW money.transaction_billing_with_void_summary AS
94         SELECT  xact,
95                 LAST(billing_type) AS last_billing_type,
96                 LAST(note) AS last_billing_note,
97                 MAX(billing_ts) AS last_billing_ts,
98                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_owed
99           FROM  money.billing
100           GROUP BY xact
101           ORDER BY MAX(billing_ts);
102
103 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
104         SELECT  xact,
105                 LAST(payment_type) AS last_payment_type,
106                 LAST(note) AS last_payment_note,
107                 MAX(payment_ts) as last_payment_ts,
108                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_paid
109           FROM  money.payment_view
110           GROUP BY xact
111           ORDER BY MAX(payment_ts);
112
113 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
114         SELECT  xact,
115                 billing_type AS last_billing_type,
116                 LAST(note) AS last_billing_note,
117                 MAX(billing_ts) AS last_billing_ts,
118                 SUM(COALESCE(amount,0)) AS total_owed
119           FROM  money.billing
120           WHERE voided IS FALSE
121           GROUP BY xact,billing_type
122           ORDER BY MAX(billing_ts);
123
124 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
125         SELECT  xact,
126                 LAST(billing_type) AS last_billing_type,
127                 LAST(note) AS last_billing_note,
128                 MAX(billing_ts) AS last_billing_ts,
129                 SUM(COALESCE(amount,0)) AS total_owed
130           FROM  money.billing
131           WHERE voided IS FALSE
132           GROUP BY xact
133           ORDER BY MAX(billing_ts);
134
135 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
136         SELECT  xact,
137                 LAST(payment_type) AS last_payment_type,
138                 LAST(note) AS last_payment_note,
139                 MAX(payment_ts) as last_payment_ts,
140                 SUM(COALESCE(amount,0)) AS total_paid
141           FROM  money.payment_view
142           WHERE voided IS FALSE
143           GROUP BY xact
144           ORDER BY MAX(payment_ts);
145
146 /* Replacing with the one below.
147 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
148         SELECT  xact.id AS id,
149                 xact.usr AS usr,
150                 xact.xact_start AS xact_start,
151                 xact.xact_finish AS xact_finish,
152                 credit.total_paid,
153                 credit.last_payment_ts,
154                 credit.last_payment_note,
155                 credit.last_payment_type,
156                 debit.total_owed,
157                 debit.last_billing_ts,
158                 debit.last_billing_note,
159                 debit.last_billing_type,
160                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
161                 p.relname AS xact_type
162           FROM  money.billable_xact xact
163                 JOIN pg_class p ON (xact.tableoid = p.oid)
164                 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
165                 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
166 */
167
168 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
169         SELECT  xact.id AS id,
170                 xact.usr AS usr,
171                 xact.xact_start AS xact_start,
172                 xact.xact_finish AS xact_finish,
173                 SUM(credit.amount) AS total_paid,
174                 MAX(credit.payment_ts) AS last_payment_ts,
175                 LAST(credit.note) AS last_payment_note,
176                 LAST(credit.payment_type) AS last_payment_type,
177                 SUM(debit.amount) AS total_owed,
178                 MAX(debit.billing_ts) AS last_billing_ts,
179                 LAST(debit.note) AS last_billing_note,
180                 LAST(debit.billing_type) AS last_billing_type,
181                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
182                 p.relname AS xact_type
183           FROM  money.billable_xact xact
184                 JOIN pg_class p ON (xact.tableoid = p.oid)
185                 LEFT JOIN money.billing debit ON (xact.id = debit.xact)
186                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact)
187           GROUP BY 1,2,3,4,14
188           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
189
190 /* Replacing with the version below
191 CREATE OR REPLACE VIEW money.billable_xact_summary AS
192         SELECT  xact.id AS id,
193                 xact.usr AS usr,
194                 xact.xact_start AS xact_start,
195                 xact.xact_finish AS xact_finish,
196                 credit.total_paid,
197                 credit.last_payment_ts,
198                 credit.last_payment_note,
199                 credit.last_payment_type,
200                 debit.total_owed,
201                 debit.last_billing_ts,
202                 debit.last_billing_note,
203                 debit.last_billing_type,
204                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
205                 p.relname AS xact_type
206           FROM  money.billable_xact xact
207                 JOIN pg_class p ON (xact.tableoid = p.oid)
208                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
209                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
210 */
211
212 CREATE OR REPLACE VIEW money.billable_xact_summary AS
213         SELECT  xact.id AS id,
214                 xact.usr AS usr,
215                 xact.xact_start AS xact_start,
216                 xact.xact_finish AS xact_finish,
217                 SUM(credit.amount) AS total_paid,
218                 MAX(credit.payment_ts) AS last_payment_ts,
219                 LAST(credit.note) AS last_payment_note,
220                 LAST(credit.payment_type) AS last_payment_type,
221                 SUM(debit.amount) AS total_owed,
222                 MAX(debit.billing_ts) AS last_billing_ts,
223                 LAST(debit.note) AS last_billing_note,
224                 LAST(debit.billing_type) AS last_billing_type,
225                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
226                 p.relname AS xact_type
227           FROM  money.billable_xact xact
228                 JOIN pg_class p ON (xact.tableoid = p.oid)
229                 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
230                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
231           GROUP BY 1,2,3,4,14
232           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
233
234 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
235         SELECT  xact.id AS id,
236                 xact.usr AS usr,
237                 xact.xact_start AS xact_start,
238                 xact.xact_finish AS xact_finish,
239                 SUM(credit.amount) AS total_paid,
240                 MAX(credit.payment_ts) AS last_payment_ts,
241                 LAST(credit.note) AS last_payment_note,
242                 LAST(credit.payment_type) AS last_payment_type,
243                 SUM(debit.amount) AS total_owed,
244                 MAX(debit.billing_ts) AS last_billing_ts,
245                 LAST(debit.note) AS last_billing_note,
246                 LAST(debit.billing_type) AS last_billing_type,
247                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
248                 p.relname AS xact_type
249           FROM  money.billable_xact xact
250                 JOIN pg_class p ON (xact.tableoid = p.oid)
251                 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
252                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
253           WHERE xact.xact_finish IS NULL
254           GROUP BY 1,2,3,4,14
255           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
256
257
258 CREATE OR REPLACE VIEW money.open_usr_summary AS
259         SELECT  usr,
260                 SUM(total_paid) AS total_paid,
261                 SUM(total_owed) AS total_owed, 
262                 SUM(balance_owed) AS balance_owed
263           FROM money.open_billable_xact_summary
264           GROUP BY 1;
265
266 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
267         SELECT  usr,
268                 SUM(total_paid) AS total_paid,
269                 SUM(total_owed) AS total_owed, 
270                 SUM(balance_owed) AS balance_owed
271           FROM  money.open_billable_xact_summary
272           WHERE xact_type = 'circulation'
273           GROUP BY 1;
274
275 CREATE OR REPLACE VIEW money.usr_summary AS
276         SELECT  usr,
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
281           GROUP BY 1;
282
283 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
284         SELECT  usr,
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'
290           GROUP BY 1;
291
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);
297
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);
304
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);
311
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);
318
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);
323
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);
328
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);
333
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);
341
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);
351
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);
365
366 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
367         SELECT  ou.id AS org_unit,
368                 ws.id AS cashdrawer,
369                 t.payment_type AS payment_type,
370                 p.payment_ts AS payment_ts,
371                 p.amount AS amount,
372                 p.voided AS voided,
373                 p.note AS note
374           FROM  actor.org_unit ou
375                 JOIN actor.workstation ws ON (ou.id = ws.owning_lib)
376                 LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer)
377                 LEFT JOIN money.payment_view t ON (p.id = t.id);
378
379
380 COMMIT;
381