1 DROP SCHEMA money CASCADE;
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
13 CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr) WHERE xact_finish IS NULL;
15 CREATE TABLE money.grocery ( -- Catchall table for local billing
16 billing_location INT NOT NULL, -- library creating transaction
18 ) INHERITS (money.billable_xact);
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 amount NUMERIC(6,2) NOT NULL,
26 billing_type TEXT NOT NULL,
29 CREATE INDEX m_b_xact_idx ON money.billing (xact);
31 CREATE TABLE money.payment (
32 id BIGSERIAL PRIMARY KEY,
33 xact BIGINT NOT NULL, -- money.billable_xact.id
34 payment_ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
35 voided BOOL NOT NULL DEFAULT FALSE,
36 amount NUMERIC(6,2) NOT NULL,
39 CREATE INDEX m_p_xact_idx ON money.payment (xact);
41 CREATE OR REPLACE VIEW money.payment_view AS
42 SELECT p.*,c.relname AS payment_type
44 JOIN pg_class c ON (p.tableoid = c.oid);
46 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
48 billing_type AS last_billing_type,
49 LAST(note) AS last_billing_note,
50 MAX(billing_ts) AS last_billing_ts,
51 SUM(COALESCE(amount,0)) AS total_owed
54 GROUP BY xact,billing_type
55 ORDER BY MAX(billing_ts);
57 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
59 LAST(billing_type) AS last_billing_type,
60 LAST(note) AS last_billing_note,
61 MAX(billing_ts) AS last_billing_ts,
62 SUM(COALESCE(amount,0)) AS total_owed
66 ORDER BY MAX(billing_ts);
68 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
70 LAST(payment_type) AS last_payment_type,
71 LAST(note) AS last_payment_note,
72 MAX(payment_ts) as last_payment_ts,
73 SUM(COALESCE(amount,0)) AS total_paid
74 FROM money.payment_view
77 ORDER BY MAX(payment_ts);
79 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
81 billing_type AS last_billing_type,
82 LAST(note) AS last_billing_note,
83 MAX(billing_ts) AS last_billing_ts,
84 SUM(COALESCE(amount,0)) AS total_owed
87 GROUP BY xact,billing_type
88 ORDER BY MAX(billing_ts);
90 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
92 LAST(billing_type) AS last_billing_type,
93 LAST(note) AS last_billing_note,
94 MAX(billing_ts) AS last_billing_ts,
95 SUM(COALESCE(amount,0)) AS total_owed
99 ORDER BY MAX(billing_ts);
101 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
103 LAST(payment_type) AS last_payment_type,
104 LAST(note) AS last_payment_note,
105 MAX(payment_ts) as last_payment_ts,
106 SUM(COALESCE(amount,0)) AS total_paid
107 FROM money.payment_view
108 WHERE voided IS FALSE
110 ORDER BY MAX(payment_ts);
112 CREATE OR REPLACE VIEW money.billable_xact_summary AS
113 SELECT xact.id AS id,
115 xact.xact_start AS xact_start,
116 xact.xact_finish AS xact_finish,
118 credit.last_payment_ts,
119 credit.last_payment_note,
120 credit.last_payment_type,
122 debit.last_billing_ts,
123 debit.last_billing_note,
124 debit.last_billing_type,
125 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
126 p.relname AS xact_type
127 FROM money.billable_xact xact
128 JOIN pg_class p ON (xact.tableoid = p.oid)
129 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
130 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
132 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
133 SELECT xact.id AS id,
135 xact.xact_start AS xact_start,
136 xact.xact_finish AS xact_finish,
138 credit.last_payment_ts,
139 credit.last_payment_note,
140 credit.last_payment_type,
142 debit.last_billing_ts,
143 debit.last_billing_note,
144 debit.last_billing_type,
145 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
146 p.relname AS xact_type
147 FROM money.billable_xact xact
148 JOIN pg_class p ON (xact.tableoid = p.oid)
149 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
150 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact)
151 WHERE xact.xact_finish IS NULL;
153 CREATE OR REPLACE VIEW money.open_usr_summary AS
155 SUM(total_paid) AS total_paid,
156 SUM(total_owed) AS total_owed,
157 SUM(balance_owed) AS balance_owed
158 FROM money.open_billable_xact_summary
161 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
163 SUM(total_paid) AS total_paid,
164 SUM(total_owed) AS total_owed,
165 SUM(balance_owed) AS balance_owed
166 FROM money.open_billable_xact_summary
167 WHERE xact_type = 'circulation'
170 CREATE OR REPLACE VIEW money.usr_summary AS
172 SUM(total_paid) AS total_paid,
173 SUM(total_owed) AS total_owed,
174 SUM(balance_owed) AS balance_owed
175 FROM money.billable_xact_summary
178 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
180 SUM(total_paid) AS total_paid,
181 SUM(total_owed) AS total_owed,
182 SUM(balance_owed) AS balance_owed
183 FROM money.billable_xact_summary
184 WHERE xact_type = 'circulation'
187 CREATE TABLE money.bnm_payment (
188 amount_collected NUMERIC(6,2) NOT NULL,
189 accepting_usr INT NOT NULL
190 ) INHERITS (money.payment);
192 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
193 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
194 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
196 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
197 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
198 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
200 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
201 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
202 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
204 CREATE TABLE money.bnm_desk_payment (
205 cash_drawer TEXT NOT NULL
206 ) INHERITS (money.bnm_payment);
208 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
209 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
210 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
212 CREATE TABLE money.check_payment (
213 check_number TEXT NOT NULL
214 ) INHERITS (money.bnm_desk_payment);
215 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
216 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
218 CREATE TABLE money.credit_card_payment (
219 cc_type TEXT NOT NULL,
220 cc_number TEXT NOT NULL,
221 expire_month INT NOT NULL,
222 expire_year INT NOT NULL,
223 approval_code TEXT NOT NULL
224 ) INHERITS (money.bnm_desk_payment);
225 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
226 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);