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
  29 CREATE TABLE money.billing (
  30         id              BIGSERIAL                       PRIMARY KEY,
  31         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  32         billing_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  33         voided          BOOL                            NOT NULL DEFAULT FALSE,
  34         voider          INT,
  35         void_time       TIMESTAMP WITH TIME ZONE,
  36         amount          NUMERIC(6,2)                    NOT NULL,
  37         billing_type    TEXT                            NOT NULL,
  38         note            TEXT
  39 );
  40 CREATE INDEX m_b_xact_idx ON money.billing (xact);
  41 CREATE INDEX m_b_time_idx ON money.billing (billing_ts);
  42
  43 CREATE TABLE money.payment (
  44         id              BIGSERIAL                       PRIMARY KEY,
  45         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  46         payment_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  47         voided          BOOL                            NOT NULL DEFAULT FALSE,
  48         amount          NUMERIC(6,2)                    NOT NULL,
  49         note            TEXT
  50 );
  51 CREATE INDEX m_p_xact_idx ON money.payment (xact);
  52 CREATE INDEX m_p_time_idx ON money.payment (payment_ts);
  53
  54 CREATE OR REPLACE VIEW money.payment_view AS
  55         SELECT  p.*,c.relname AS payment_type
  56           FROM  money.payment p
  57                 JOIN pg_class c ON (p.tableoid = c.oid);
  58
  59 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
  60         SELECT  xact,
  61                 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,billing_type
  68           ORDER BY MAX(billing_ts);
  69
  70 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
  71         SELECT  xact,
  72                 LAST(billing_type) AS last_billing_type,
  73                 LAST(note) AS last_billing_note,
  74                 MAX(billing_ts) AS last_billing_ts,
  75                 SUM(COALESCE(amount,0)) AS total_owed
  76           FROM  money.billing
  77           WHERE voided IS FALSE
  78           GROUP BY xact
  79           ORDER BY MAX(billing_ts);
  80
  81 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
  82         SELECT  xact,
  83                 LAST(payment_type) AS last_payment_type,
  84                 LAST(note) AS last_payment_note,
  85                 MAX(payment_ts) as last_payment_ts,
  86                 SUM(COALESCE(amount,0)) AS total_paid
  87           FROM  money.payment_view
  88           WHERE voided IS FALSE
  89           GROUP BY xact
  90           ORDER BY MAX(payment_ts);
  91
  92 CREATE OR REPLACE VIEW money.transaction_billing_with_void_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(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_owed
  98           FROM  money.billing
  99           GROUP BY xact
 100           ORDER BY MAX(billing_ts);
 101
 102 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
 103         SELECT  xact,
 104                 LAST(payment_type) AS last_payment_type,
 105                 LAST(note) AS last_payment_note,
 106                 MAX(payment_ts) as last_payment_ts,
 107                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_paid
 108           FROM  money.payment_view
 109           GROUP BY xact
 110           ORDER BY MAX(payment_ts);
 111
 112 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
 113         SELECT  xact,
 114                 billing_type AS last_billing_type,
 115                 LAST(note) AS last_billing_note,
 116                 MAX(billing_ts) AS last_billing_ts,
 117                 SUM(COALESCE(amount,0)) AS total_owed
 118           FROM  money.billing
 119           WHERE voided IS FALSE
 120           GROUP BY xact,billing_type
 121           ORDER BY MAX(billing_ts);
 122
 123 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
 124         SELECT  xact,
 125                 LAST(billing_type) AS last_billing_type,
 126                 LAST(note) AS last_billing_note,
 127                 MAX(billing_ts) AS last_billing_ts,
 128                 SUM(COALESCE(amount,0)) AS total_owed
 129           FROM  money.billing
 130           WHERE voided IS FALSE
 131           GROUP BY xact
 132           ORDER BY MAX(billing_ts);
 133
 134 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
 135         SELECT  xact,
 136                 LAST(payment_type) AS last_payment_type,
 137                 LAST(note) AS last_payment_note,
 138                 MAX(payment_ts) as last_payment_ts,
 139                 SUM(COALESCE(amount,0)) AS total_paid
 140           FROM  money.payment_view
 141           WHERE voided IS FALSE
 142           GROUP BY xact
 143           ORDER BY MAX(payment_ts);
 144
 145 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
 146         SELECT  xact.id AS id,
 147                 xact.usr AS usr,
 148                 xact.xact_start AS xact_start,
 149                 xact.xact_finish AS xact_finish,
 150                 credit.total_paid,
 151                 credit.last_payment_ts,
 152                 credit.last_payment_note,
 153                 credit.last_payment_type,
 154                 debit.total_owed,
 155                 debit.last_billing_ts,
 156                 debit.last_billing_note,
 157                 debit.last_billing_type,
 158                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 159                 p.relname AS xact_type
 160           FROM  money.billable_xact xact
 161                 JOIN pg_class p ON (xact.tableoid = p.oid)
 162                 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
 163                 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
 164
 165 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 166         SELECT  xact.id AS id,
 167                 xact.usr AS usr,
 168                 xact.xact_start AS xact_start,
 169                 xact.xact_finish AS xact_finish,
 170                 credit.total_paid,
 171                 credit.last_payment_ts,
 172                 credit.last_payment_note,
 173                 credit.last_payment_type,
 174                 debit.total_owed,
 175                 debit.last_billing_ts,
 176                 debit.last_billing_note,
 177                 debit.last_billing_type,
 178                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 179                 p.relname AS xact_type
 180           FROM  money.billable_xact xact
 181                 JOIN pg_class p ON (xact.tableoid = p.oid)
 182                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 183                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
 184
 185 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
 186         SELECT  xact.id AS id,
 187                 xact.usr AS usr,
 188                 xact.xact_start AS xact_start,
 189                 xact.xact_finish AS xact_finish,
 190                 credit.total_paid,
 191                 credit.last_payment_ts,
 192                 credit.last_payment_note,
 193                 credit.last_payment_type,
 194                 debit.total_owed,
 195                 debit.last_billing_ts,
 196                 debit.last_billing_note,
 197                 debit.last_billing_type,
 198                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 199                 p.relname AS xact_type
 200           FROM  money.billable_xact xact
 201                 JOIN pg_class p ON (xact.tableoid = p.oid)
 202                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 203                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact)
 204           WHERE xact.xact_finish IS NULL;
 205
 206 CREATE OR REPLACE VIEW money.open_usr_summary AS
 207         SELECT  usr,
 208                 SUM(total_paid) AS total_paid,
 209                 SUM(total_owed) AS total_owed,
 210                 SUM(balance_owed) AS balance_owed
 211           FROM money.open_billable_xact_summary
 212           GROUP BY 1;
 213
 214 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
 215         SELECT  usr,
 216                 SUM(total_paid) AS total_paid,
 217                 SUM(total_owed) AS total_owed,
 218                 SUM(balance_owed) AS balance_owed
 219           FROM  money.open_billable_xact_summary
 220           WHERE xact_type = 'circulation'
 221           GROUP BY 1;
 222
 223 CREATE OR REPLACE VIEW money.usr_summary AS
 224         SELECT  usr,
 225                 SUM(total_paid) AS total_paid,
 226                 SUM(total_owed) AS total_owed,
 227                 SUM(balance_owed) AS balance_owed
 228           FROM money.billable_xact_summary
 229           GROUP BY 1;
 230
 231 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
 232         SELECT  usr,
 233                 SUM(total_paid) AS total_paid,
 234                 SUM(total_owed) AS total_owed,
 235                 SUM(balance_owed) AS balance_owed
 236           FROM  money.billable_xact_summary
 237           WHERE xact_type = 'circulation'
 238           GROUP BY 1;
 239
 240 CREATE TABLE money.bnm_payment (
 241         amount_collected        NUMERIC(6,2)    NOT NULL,
 242         accepting_usr           INT             NOT NULL
 243 ) INHERITS (money.payment);
 244
 245 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
 246 CREATE INDEX money_forgive_id_idx ON money.work_payment (id);
 247 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
 248 CREATE INDEX money_forgive_payment_payment_ts_idx ON money.forgive_payment (payment_ts);
 249 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
 250
 251 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
 252 CREATE INDEX money_work_id_idx ON money.work_payment (id);
 253 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
 254 CREATE INDEX money_work_payment_payment_ts_idx ON money.work_payment (payment_ts);
 255 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
 256
 257 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
 258 CREATE INDEX money_credit_id_idx ON money.credit_payment (id);
 259 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
 260 CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment (payment_ts);
 261 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
 262
 263 CREATE TABLE money.bnm_desk_payment (
 264         cash_drawer     INT     REFERENCES actor.workstation (id)
 265 ) INHERITS (money.bnm_payment);
 266
 267 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
 268 CREATE INDEX money_cash_id_idx ON money.cash_payment (id);
 269 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
 270 CREATE INDEX money_cash_payment_ts_idx ON money.cash_payment (payment_ts);
 271 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
 272 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
 273
 274 CREATE TABLE money.check_payment (
 275         check_number    TEXT    NOT NULL
 276 ) INHERITS (money.bnm_desk_payment);
 277 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
 278 CREATE INDEX money_check_id_idx ON money.check_payment (id);
 279 CREATE INDEX money_check_payment_ts_idx ON money.check_payment (payment_ts);
 280 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
 281 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
 282
 283 CREATE TABLE money.credit_card_payment (
 284         cc_type         TEXT    NOT NULL,
 285         cc_number       TEXT    NOT NULL,
 286         expire_month    INT     NOT NULL,
 287         expire_year     INT     NOT NULL,
 288         approval_code   TEXT    NOT NULL
 289 ) INHERITS (money.bnm_desk_payment);
 290 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
 291 CREATE INDEX money_credit_card_id_idx ON money.credit_card_payment (id);
 292 CREATE INDEX money_credit_card_payment_ts_idx ON money.credit_card_payment (payment_ts);
 293 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
 294 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);
 295
 296 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
 297         SELECT  ou.id AS org_unit,
 298                 ws.id AS cashdrawer,
 299                 t.payment_type AS payment_type,
 300                 p.payment_ts AS payment_ts,
 301                 p.amount AS amount,
 302                 p.voided AS voided,
 303                 p.note AS note
 304           FROM  actor.org_unit ou
 305                 JOIN actor.workstation ws ON (ou.id = ws.owning_lib)
 306                 LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer)
 307                 LEFT JOIN money.payment_view t ON (p.id = t.id);
 308
 309
 310 COMMIT;
 311