adding "voider" and "void_time" fields to billing
[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.billable_xact (
8         id              BIGSERIAL                       PRIMARY KEY,
9         usr             INT                             NOT NULL, -- actor.usr.id
10         xact_start      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
11         xact_finish     TIMESTAMP WITH TIME ZONE
12 );
13 CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr) WHERE xact_finish IS NULL;
14
15 CREATE TABLE money.grocery ( -- Catchall table for local billing
16         billing_location        INT     NOT NULL, -- library creating transaction
17         note                    TEXT
18 ) INHERITS (money.billable_xact);
19
20 CREATE TABLE money.billing (
21         id              BIGSERIAL                       PRIMARY KEY,
22         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
23         billing_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
24         voided          BOOL                            NOT NULL DEFAULT FALSE,
25         voider          INT,
26         void_time       TIMESTAMP WITH TIME ZONE,
27         amount          NUMERIC(6,2)                    NOT NULL,
28         billing_type    TEXT                            NOT NULL,
29         note            TEXT
30 );
31 CREATE INDEX m_b_xact_idx ON money.billing (xact);
32
33 CREATE TABLE money.payment (
34         id              BIGSERIAL                       PRIMARY KEY,
35         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
36         payment_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
37         voided          BOOL                            NOT NULL DEFAULT FALSE,
38         amount          NUMERIC(6,2)                    NOT NULL,
39         note            TEXT
40 );
41 CREATE INDEX m_p_xact_idx ON money.payment (xact);
42
43 CREATE OR REPLACE VIEW money.payment_view AS
44         SELECT  p.*,c.relname AS payment_type
45           FROM  money.payment p
46                 JOIN pg_class c ON (p.tableoid = c.oid);
47
48 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
49         SELECT  xact,
50                 billing_type AS last_billing_type,
51                 LAST(note) AS last_billing_note,
52                 MAX(billing_ts) AS last_billing_ts,
53                 SUM(COALESCE(amount,0)) AS total_owed
54           FROM  money.billing
55           WHERE voided IS FALSE
56           GROUP BY xact,billing_type
57           ORDER BY MAX(billing_ts);
58
59 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
60         SELECT  xact,
61                 LAST(billing_type) AS last_billing_type,
62                 LAST(note) AS last_billing_note,
63                 MAX(billing_ts) AS last_billing_ts,
64                 SUM(COALESCE(amount,0)) AS total_owed
65           FROM  money.billing
66           WHERE voided IS FALSE
67           GROUP BY xact
68           ORDER BY MAX(billing_ts);
69
70 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
71         SELECT  xact,
72                 LAST(payment_type) AS last_payment_type,
73                 LAST(note) AS last_payment_note,
74                 MAX(payment_ts) as last_payment_ts,
75                 SUM(COALESCE(amount,0)) AS total_paid
76           FROM  money.payment_view
77           WHERE voided IS FALSE
78           GROUP BY xact
79           ORDER BY MAX(payment_ts);
80
81 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
82         SELECT  xact,
83                 billing_type AS last_billing_type,
84                 LAST(note) AS last_billing_note,
85                 MAX(billing_ts) AS last_billing_ts,
86                 SUM(COALESCE(amount,0)) AS total_owed
87           FROM  money.billing
88           WHERE voided IS FALSE
89           GROUP BY xact,billing_type
90           ORDER BY MAX(billing_ts);
91
92 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
93         SELECT  xact,
94                 LAST(billing_type) AS last_billing_type,
95                 LAST(note) AS last_billing_note,
96                 MAX(billing_ts) AS last_billing_ts,
97                 SUM(COALESCE(amount,0)) AS total_owed
98           FROM  money.billing
99           WHERE voided IS FALSE
100           GROUP BY xact
101           ORDER BY MAX(billing_ts);
102
103 CREATE OR REPLACE VIEW money.open_transaction_payment_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(COALESCE(amount,0)) AS total_paid
109           FROM  money.payment_view
110           WHERE voided IS FALSE
111           GROUP BY xact
112           ORDER BY MAX(payment_ts);
113
114 CREATE OR REPLACE VIEW money.billable_xact_summary AS
115         SELECT  xact.id AS id,
116                 xact.usr AS usr,
117                 xact.xact_start AS xact_start,
118                 xact.xact_finish AS xact_finish,
119                 credit.total_paid,
120                 credit.last_payment_ts,
121                 credit.last_payment_note,
122                 credit.last_payment_type,
123                 debit.total_owed,
124                 debit.last_billing_ts,
125                 debit.last_billing_note,
126                 debit.last_billing_type,
127                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
128                 p.relname AS xact_type
129           FROM  money.billable_xact xact
130                 JOIN pg_class p ON (xact.tableoid = p.oid)
131                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
132                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
133
134 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
135         SELECT  xact.id AS id,
136                 xact.usr AS usr,
137                 xact.xact_start AS xact_start,
138                 xact.xact_finish AS xact_finish,
139                 credit.total_paid,
140                 credit.last_payment_ts,
141                 credit.last_payment_note,
142                 credit.last_payment_type,
143                 debit.total_owed,
144                 debit.last_billing_ts,
145                 debit.last_billing_note,
146                 debit.last_billing_type,
147                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
148                 p.relname AS xact_type
149           FROM  money.billable_xact xact
150                 JOIN pg_class p ON (xact.tableoid = p.oid)
151                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
152                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact)
153           WHERE xact.xact_finish IS NULL;
154
155 CREATE OR REPLACE VIEW money.open_usr_summary AS
156         SELECT  usr,
157                 SUM(total_paid) AS total_paid,
158                 SUM(total_owed) AS total_owed, 
159                 SUM(balance_owed) AS balance_owed
160           FROM money.open_billable_xact_summary
161           GROUP BY 1;
162
163 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
164         SELECT  usr,
165                 SUM(total_paid) AS total_paid,
166                 SUM(total_owed) AS total_owed, 
167                 SUM(balance_owed) AS balance_owed
168           FROM  money.open_billable_xact_summary
169           WHERE xact_type = 'circulation'
170           GROUP BY 1;
171
172 CREATE OR REPLACE VIEW money.usr_summary AS
173         SELECT  usr,
174                 SUM(total_paid) AS total_paid,
175                 SUM(total_owed) AS total_owed, 
176                 SUM(balance_owed) AS balance_owed
177           FROM money.billable_xact_summary
178           GROUP BY 1;
179
180 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
181         SELECT  usr,
182                 SUM(total_paid) AS total_paid,
183                 SUM(total_owed) AS total_owed, 
184                 SUM(balance_owed) AS balance_owed
185           FROM  money.billable_xact_summary
186           WHERE xact_type = 'circulation'
187           GROUP BY 1;
188
189 CREATE TABLE money.bnm_payment (
190         amount_collected        NUMERIC(6,2)    NOT NULL,
191         accepting_usr           INT             NOT NULL
192 ) INHERITS (money.payment);
193
194 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
195 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
196 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
197
198 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
199 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
200 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
201
202 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
203 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
204 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
205
206 CREATE TABLE money.bnm_desk_payment (
207         cash_drawer     TEXT    NOT NULL
208 ) INHERITS (money.bnm_payment);
209
210 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
211 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
212 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
213
214 CREATE TABLE money.check_payment (
215         check_number    TEXT    NOT NULL
216 ) INHERITS (money.bnm_desk_payment);
217 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
218 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
219
220 CREATE TABLE money.credit_card_payment (
221         cc_type         TEXT    NOT NULL,
222         cc_number       TEXT    NOT NULL,
223         expire_month    INT     NOT NULL,
224         expire_year     INT     NOT NULL,
225         approval_code   TEXT    NOT NULL
226 ) INHERITS (money.bnm_desk_payment);
227 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
228 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
229
230
231 COMMIT;
232