1e6f1e46dcda674b05684bf69b0d531b1d5c713d
[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 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);
31
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,
37         voider          INT,
38         void_time       TIMESTAMP WITH TIME ZONE,
39         amount          NUMERIC(6,2)                    NOT NULL,
40         billing_type    TEXT                            NOT NULL,
41         note            TEXT
42 );
43 CREATE INDEX m_b_xact_idx ON money.billing (xact);
44 CREATE INDEX m_b_time_idx ON money.billing (billing_ts);
45
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,
52         note            TEXT
53 );
54 CREATE INDEX m_p_xact_idx ON money.payment (xact);
55 CREATE INDEX m_p_time_idx ON money.payment (payment_ts);
56
57 CREATE OR REPLACE VIEW money.payment_view AS
58         SELECT  p.*,c.relname AS payment_type
59           FROM  money.payment p
60                 JOIN pg_class c ON (p.tableoid = c.oid);
61
62 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
63         SELECT  xact,
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
68           FROM  money.billing
69           WHERE voided IS FALSE
70           GROUP BY xact,billing_type
71           ORDER BY MAX(billing_ts);
72
73 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
74         SELECT  xact,
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
79           FROM  money.billing
80           WHERE voided IS FALSE
81           GROUP BY xact
82           ORDER BY MAX(billing_ts);
83
84 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
85         SELECT  xact,
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
91           WHERE voided IS FALSE
92           GROUP BY xact
93           ORDER BY MAX(payment_ts);
94
95 CREATE OR REPLACE VIEW money.transaction_billing_with_void_summary AS
96         SELECT  xact,
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
101           FROM  money.billing
102           GROUP BY xact
103           ORDER BY MAX(billing_ts);
104
105 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
106         SELECT  xact,
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
112           GROUP BY xact
113           ORDER BY MAX(payment_ts);
114
115 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
116         SELECT  xact,
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
121           FROM  money.billing
122           WHERE voided IS FALSE
123           GROUP BY xact,billing_type
124           ORDER BY MAX(billing_ts);
125
126 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
127         SELECT  xact,
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
132           FROM  money.billing
133           WHERE voided IS FALSE
134           GROUP BY xact
135           ORDER BY MAX(billing_ts);
136
137 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
138         SELECT  xact,
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
145           GROUP BY xact
146           ORDER BY MAX(payment_ts);
147
148 /* Replacing with the one below.
149 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
150         SELECT  xact.id AS id,
151                 xact.usr AS usr,
152                 xact.xact_start AS xact_start,
153                 xact.xact_finish AS xact_finish,
154                 credit.total_paid,
155                 credit.last_payment_ts,
156                 credit.last_payment_note,
157                 credit.last_payment_type,
158                 debit.total_owed,
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);
168 */
169
170 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
171         SELECT  xact.id AS id,
172                 xact.usr AS usr,
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)
189           GROUP BY 1,2,3,4,14
190           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
191
192 /* Replacing with the version below
193 CREATE OR REPLACE VIEW money.billable_xact_summary AS
194         SELECT  xact.id AS id,
195                 xact.usr AS usr,
196                 xact.xact_start AS xact_start,
197                 xact.xact_finish AS xact_finish,
198                 credit.total_paid,
199                 credit.last_payment_ts,
200                 credit.last_payment_note,
201                 credit.last_payment_type,
202                 debit.total_owed,
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);
212
213 CREATE OR REPLACE VIEW money.billable_xact_summary AS
214         SELECT  xact.id AS id,
215                 xact.usr AS usr,
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)
232           GROUP BY 1,2,3,4,14
233           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
234 */
235
236 CREATE OR REPLACE VIEW money.billable_xact_summary AS
237         SELECT  xact.id,
238                 xact.usr,
239                 xact.xact_start,
240                 xact.xact_finish,
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
253                 LEFT JOIN (
254                         SELECT  billing.xact,
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
259                           FROM  money.billing
260                           WHERE billing.voided IS FALSE
261                           GROUP BY billing.xact
262                         ) debit ON xact.id = debit.xact
263                 LEFT JOIN (
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;
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.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');
371
372 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
373         SELECT  ou.id AS org_unit,
374                 ws.id AS cashdrawer,
375                 t.payment_type AS payment_type,
376                 p.payment_ts AS payment_ts,
377                 p.amount AS amount,
378                 p.voided AS voided,
379                 p.note AS note
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);
384
385
386 COMMIT;
387