refined the logging some
[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 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
147         SELECT  xact.id AS id,
148                 xact.usr AS usr,
149                 xact.xact_start AS xact_start,
150                 xact.xact_finish AS xact_finish,
151                 credit.total_paid,
152                 credit.last_payment_ts,
153                 credit.last_payment_note,
154                 credit.last_payment_type,
155                 debit.total_owed,
156                 debit.last_billing_ts,
157                 debit.last_billing_note,
158                 debit.last_billing_type,
159                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
160                 p.relname AS xact_type
161           FROM  money.billable_xact xact
162                 JOIN pg_class p ON (xact.tableoid = p.oid)
163                 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
164                 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
165
166 CREATE OR REPLACE VIEW money.billable_xact_summary AS
167         SELECT  xact.id AS id,
168                 xact.usr AS usr,
169                 xact.xact_start AS xact_start,
170                 xact.xact_finish AS xact_finish,
171                 credit.total_paid,
172                 credit.last_payment_ts,
173                 credit.last_payment_note,
174                 credit.last_payment_type,
175                 debit.total_owed,
176                 debit.last_billing_ts,
177                 debit.last_billing_note,
178                 debit.last_billing_type,
179                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
180                 p.relname AS xact_type
181           FROM  money.billable_xact xact
182                 JOIN pg_class p ON (xact.tableoid = p.oid)
183                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
184                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
185
186 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
187         SELECT  xact.id AS id,
188                 xact.usr AS usr,
189                 xact.xact_start AS xact_start,
190                 xact.xact_finish AS xact_finish,
191                 SUM(credit.amount) AS total_paid,
192                 MAX(credit.payment_ts) AS last_payment_ts,
193                 LAST(credit.note) AS last_payment_note,
194                 LAST(credit.payment_type) AS last_payment_type,
195                 SUM(debit.amount) AS total_owed,
196                 MAX(debit.billing_ts) AS last_billing_ts,
197                 LAST(debit.note) AS last_billing_note,
198                 LAST(debit.billing_type) AS last_billing_type,
199                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
200                 p.relname AS xact_type
201           FROM  money.billable_xact xact
202                 JOIN pg_class p ON (xact.tableoid = p.oid)
203                 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
204                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
205           WHERE xact.xact_finish IS NULL
206           GROUP BY 1,2,3,4,14
207           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
208
209
210 CREATE OR REPLACE VIEW money.open_usr_summary AS
211         SELECT  usr,
212                 SUM(total_paid) AS total_paid,
213                 SUM(total_owed) AS total_owed, 
214                 SUM(balance_owed) AS balance_owed
215           FROM money.open_billable_xact_summary
216           GROUP BY 1;
217
218 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
219         SELECT  usr,
220                 SUM(total_paid) AS total_paid,
221                 SUM(total_owed) AS total_owed, 
222                 SUM(balance_owed) AS balance_owed
223           FROM  money.open_billable_xact_summary
224           WHERE xact_type = 'circulation'
225           GROUP BY 1;
226
227 CREATE OR REPLACE VIEW money.usr_summary AS
228         SELECT  usr,
229                 SUM(total_paid) AS total_paid,
230                 SUM(total_owed) AS total_owed, 
231                 SUM(balance_owed) AS balance_owed
232           FROM money.billable_xact_summary
233           GROUP BY 1;
234
235 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
236         SELECT  usr,
237                 SUM(total_paid) AS total_paid,
238                 SUM(total_owed) AS total_owed, 
239                 SUM(balance_owed) AS balance_owed
240           FROM  money.billable_xact_summary
241           WHERE xact_type = 'circulation'
242           GROUP BY 1;
243
244 CREATE TABLE money.bnm_payment (
245         amount_collected        NUMERIC(6,2)    NOT NULL,
246         accepting_usr           INT             NOT NULL
247 ) INHERITS (money.payment);
248 ALTER TABLE money.bnm_payment ADD PRIMARY KEY (id);
249
250 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
251 ALTER TABLE money.forgive_payment ADD PRIMARY KEY (id);
252 CREATE INDEX money_forgive_id_idx ON money.forgive_payment (id);
253 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
254 CREATE INDEX money_forgive_payment_payment_ts_idx ON money.forgive_payment (payment_ts);
255 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
256
257 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
258 ALTER TABLE money.work_payment ADD PRIMARY KEY (id);
259 CREATE INDEX money_work_id_idx ON money.work_payment (id);
260 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
261 CREATE INDEX money_work_payment_payment_ts_idx ON money.work_payment (payment_ts);
262 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
263
264 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
265 ALTER TABLE money.credit_payment ADD PRIMARY KEY (id);
266 CREATE INDEX money_credit_id_idx ON money.credit_payment (id);
267 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
268 CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment (payment_ts);
269 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
270
271 CREATE TABLE money.bnm_desk_payment (
272         cash_drawer     INT     REFERENCES actor.workstation (id)
273 ) INHERITS (money.bnm_payment);
274 ALTER TABLE money.bnm_desk_payment ADD PRIMARY KEY (id);
275
276 CREATE OR REPLACE VIEW money.desk_payment_view AS
277         SELECT  p.*,c.relname AS payment_type
278           FROM  money.bnm_desk_payment p
279                 JOIN pg_class c ON (p.tableoid = c.oid);
280
281 CREATE OR REPLACE VIEW money.bnm_payment_view AS
282         SELECT  p.*,c.relname AS payment_type
283           FROM  money.bnm_payment p
284                 JOIN pg_class c ON (p.tableoid = c.oid);
285
286 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
287 ALTER TABLE money.cash_payment ADD PRIMARY KEY (id);
288 CREATE INDEX money_cash_id_idx ON money.cash_payment (id);
289 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
290 CREATE INDEX money_cash_payment_ts_idx ON money.cash_payment (payment_ts);
291 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
292 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
293
294 CREATE TABLE money.check_payment (
295         check_number    TEXT    NOT NULL
296 ) INHERITS (money.bnm_desk_payment);
297 ALTER TABLE money.check_payment ADD PRIMARY KEY (id);
298 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
299 CREATE INDEX money_check_id_idx ON money.check_payment (id);
300 CREATE INDEX money_check_payment_ts_idx ON money.check_payment (payment_ts);
301 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
302 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
303
304 CREATE TABLE money.credit_card_payment (
305         cc_type         TEXT    NOT NULL,
306         cc_number       TEXT    NOT NULL,
307         expire_month    INT     NOT NULL,
308         expire_year     INT     NOT NULL,
309         approval_code   TEXT    NOT NULL
310 ) INHERITS (money.bnm_desk_payment);
311 ALTER TABLE money.credit_card_payment ADD PRIMARY KEY (id);
312 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
313 CREATE INDEX money_credit_card_id_idx ON money.credit_card_payment (id);
314 CREATE INDEX money_credit_card_payment_ts_idx ON money.credit_card_payment (payment_ts);
315 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
316 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);
317
318 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
319         SELECT  ou.id AS org_unit,
320                 ws.id AS cashdrawer,
321                 t.payment_type AS payment_type,
322                 p.payment_ts AS payment_ts,
323                 p.amount AS amount,
324                 p.voided AS voided,
325                 p.note AS note
326           FROM  actor.org_unit ou
327                 JOIN actor.workstation ws ON (ou.id = ws.owning_lib)
328                 LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer)
329                 LEFT JOIN money.payment_view t ON (p.id = t.id);
330
331
332 COMMIT;
333