Open-ILS/src/sql/Pg/080.schema.money.sql

   1 /*
   2  * Copyright (C) 2004-2008  Georgia Public Library Service
   3  * Copyright (C) 2007-2008  Equinox Software, Inc.
   4  * Mike Rylander <miker@esilibrary.com>
   5  *
   6  * This program is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU General Public License
   8  * as published by the Free Software Foundation; either version 2
   9  * of the License, or (at your option) any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  14  * GNU General Public License for more details.
  15  *
  16  */
  17
  18 DROP SCHEMA money CASCADE;
  19
  20 BEGIN;
  21
  22 CREATE SCHEMA money;
  23
  24 CREATE TABLE money.collections_tracker (
  25         id              BIGSERIAL                       PRIMARY KEY,
  26         usr             INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, -- actor.usr.id
  27         collector       INT                             NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
  28         location        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  29         enter_time      TIMESTAMP WITH TIME ZONE
  30 );
  31 CREATE UNIQUE INDEX m_c_t_usr_collector_location_once_idx ON money.collections_tracker (usr, collector, location);
  32
  33 CREATE TABLE money.billable_xact (
  34         id              BIGSERIAL                       PRIMARY KEY,
  35         usr             INT                             NOT NULL, -- actor.usr.id
  36         xact_start      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  37         xact_finish     TIMESTAMP WITH TIME ZONE
  38 );
  39 CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr);
  40
  41 CREATE TABLE money.grocery ( -- Catchall table for local billing
  42         billing_location        INT     NOT NULL, -- library creating transaction
  43         note                    TEXT
  44 ) INHERITS (money.billable_xact);
  45 ALTER TABLE money.grocery ADD PRIMARY KEY (id);
  46 CREATE INDEX circ_open_date_idx ON "money".grocery (xact_start) WHERE xact_finish IS NULL;
  47 CREATE INDEX m_g_usr_idx ON "money".grocery (usr);
  48
  49 CREATE TABLE money.billing (
  50         id              BIGSERIAL                       PRIMARY KEY,
  51         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  52         billing_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  53         voided          BOOL                            NOT NULL DEFAULT FALSE,
  54         voider          INT,
  55         void_time       TIMESTAMP WITH TIME ZONE,
  56         amount          NUMERIC(6,2)                    NOT NULL,
  57         billing_type    TEXT                            NOT NULL,
  58         note            TEXT
  59 );
  60 CREATE INDEX m_b_xact_idx ON money.billing (xact);
  61 CREATE INDEX m_b_time_idx ON money.billing (billing_ts);
  62
  63 CREATE TABLE money.payment (
  64         id              BIGSERIAL                       PRIMARY KEY,
  65         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  66         payment_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  67         voided          BOOL                            NOT NULL DEFAULT FALSE,
  68         amount          NUMERIC(6,2)                    NOT NULL,
  69         note            TEXT
  70 );
  71 CREATE INDEX m_p_xact_idx ON money.payment (xact);
  72 CREATE INDEX m_p_time_idx ON money.payment (payment_ts);
  73
  74 CREATE OR REPLACE VIEW money.payment_view AS
  75         SELECT  p.*,c.relname AS payment_type
  76           FROM  money.payment p
  77                 JOIN pg_class c ON (p.tableoid = c.oid);
  78
  79 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
  80         SELECT  xact,
  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
  85           FROM  money.billing
  86           WHERE voided IS FALSE
  87           GROUP BY xact,billing_type
  88           ORDER BY MAX(billing_ts);
  89
  90 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
  91         SELECT  xact,
  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
  96           FROM  money.billing
  97           WHERE voided IS FALSE
  98           GROUP BY xact
  99           ORDER BY MAX(billing_ts);
 100
 101 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
 102         SELECT  xact,
 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
 109           GROUP BY xact
 110           ORDER BY MAX(payment_ts);
 111
 112 CREATE OR REPLACE VIEW money.transaction_billing_with_void_summary AS
 113         SELECT  xact,
 114                 LAST(billing_type) AS last_billing_type,
 115                 LAST(note) AS last_billing_note,
 116                 MAX(billing_ts) AS last_billing_ts,
 117                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_owed
 118           FROM  money.billing
 119           GROUP BY xact
 120           ORDER BY MAX(billing_ts);
 121
 122 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
 123         SELECT  xact,
 124                 LAST(payment_type) AS last_payment_type,
 125                 LAST(note) AS last_payment_note,
 126                 MAX(payment_ts) as last_payment_ts,
 127                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_paid
 128           FROM  money.payment_view
 129           GROUP BY xact
 130           ORDER BY MAX(payment_ts);
 131
 132 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
 133         SELECT  xact,
 134                 billing_type AS last_billing_type,
 135                 LAST(note) AS last_billing_note,
 136                 MAX(billing_ts) AS last_billing_ts,
 137                 SUM(COALESCE(amount,0)) AS total_owed
 138           FROM  money.billing
 139           WHERE voided IS FALSE
 140           GROUP BY xact,billing_type
 141           ORDER BY MAX(billing_ts);
 142
 143 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
 144         SELECT  xact,
 145                 LAST(billing_type) AS last_billing_type,
 146                 LAST(note) AS last_billing_note,
 147                 MAX(billing_ts) AS last_billing_ts,
 148                 SUM(COALESCE(amount,0)) AS total_owed
 149           FROM  money.billing
 150           WHERE voided IS FALSE
 151           GROUP BY xact
 152           ORDER BY MAX(billing_ts);
 153
 154 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
 155         SELECT  xact,
 156                 LAST(payment_type) AS last_payment_type,
 157                 LAST(note) AS last_payment_note,
 158                 MAX(payment_ts) as last_payment_ts,
 159                 SUM(COALESCE(amount,0)) AS total_paid
 160           FROM  money.payment_view
 161           WHERE voided IS FALSE
 162           GROUP BY xact
 163           ORDER BY MAX(payment_ts);
 164
 165 /* Replacing with the one below.
 166 CREATE OR REPLACE VIEW money.billable_xact_with_void_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_with_void_summary debit ON (xact.id = debit.xact)
 184                 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
 185 */
 186
 187 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
 188         SELECT  xact.id AS id,
 189                 xact.usr AS usr,
 190                 xact.xact_start AS xact_start,
 191                 xact.xact_finish AS xact_finish,
 192                 SUM(credit.amount) AS total_paid,
 193                 MAX(credit.payment_ts) AS last_payment_ts,
 194                 LAST(credit.note) AS last_payment_note,
 195                 LAST(credit.payment_type) AS last_payment_type,
 196                 SUM(debit.amount) AS total_owed,
 197                 MAX(debit.billing_ts) AS last_billing_ts,
 198                 LAST(debit.note) AS last_billing_note,
 199                 LAST(debit.billing_type) AS last_billing_type,
 200                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
 201                 p.relname AS xact_type
 202           FROM  money.billable_xact xact
 203                 JOIN pg_class p ON (xact.tableoid = p.oid)
 204                 LEFT JOIN money.billing debit ON (xact.id = debit.xact)
 205                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact)
 206           GROUP BY 1,2,3,4,14
 207           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
 208
 209 /* Replacing with the version below
 210 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 211         SELECT  xact.id AS id,
 212                 xact.usr AS usr,
 213                 xact.xact_start AS xact_start,
 214                 xact.xact_finish AS xact_finish,
 215                 credit.total_paid,
 216                 credit.last_payment_ts,
 217                 credit.last_payment_note,
 218                 credit.last_payment_type,
 219                 debit.total_owed,
 220                 debit.last_billing_ts,
 221                 debit.last_billing_note,
 222                 debit.last_billing_type,
 223                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 224                 p.relname AS xact_type
 225           FROM  money.billable_xact xact
 226                 JOIN pg_class p ON (xact.tableoid = p.oid)
 227                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 228                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
 229
 230 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 231         SELECT  xact.id AS id,
 232                 xact.usr AS usr,
 233                 xact.xact_start AS xact_start,
 234                 xact.xact_finish AS xact_finish,
 235                 SUM(credit.amount) AS total_paid,
 236                 MAX(credit.payment_ts) AS last_payment_ts,
 237                 LAST(credit.note) AS last_payment_note,
 238                 LAST(credit.payment_type) AS last_payment_type,
 239                 SUM(debit.amount) AS total_owed,
 240                 MAX(debit.billing_ts) AS last_billing_ts,
 241                 LAST(debit.note) AS last_billing_note,
 242                 LAST(debit.billing_type) AS last_billing_type,
 243                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
 244                 p.relname AS xact_type
 245           FROM  money.billable_xact xact
 246                 JOIN pg_class p ON (xact.tableoid = p.oid)
 247                 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
 248                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
 249           GROUP BY 1,2,3,4,14
 250           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
 251 */
 252
 253 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 254         SELECT  xact.id,
 255                 xact.usr,
 256                 xact.xact_start,
 257                 xact.xact_finish,
 258                 credit.amount AS total_paid,
 259                 credit.payment_ts AS last_payment_ts,
 260                 credit.note AS last_payment_note,
 261                 credit.payment_type AS last_payment_type,
 262                 debit.amount AS total_owed,
 263                 debit.billing_ts AS last_billing_ts,
 264                 debit.note AS last_billing_note,
 265                 debit.billing_type AS last_billing_type,
 266                 COALESCE(debit.amount, 0::numeric) - COALESCE(credit.amount, 0::numeric) AS balance_owed,
 267                 p.relname AS xact_type
 268           FROM  money.billable_xact xact
 269                 JOIN pg_class p ON xact.tableoid = p.oid
 270                 LEFT JOIN (
 271                         SELECT  billing.xact,
 272                                 sum(billing.amount) AS amount,
 273                                 max(billing.billing_ts) AS billing_ts,
 274                                 last(billing.note) AS note,
 275                                 last(billing.billing_type) AS billing_type
 276                           FROM  money.billing
 277                           WHERE billing.voided IS FALSE
 278                           GROUP BY billing.xact
 279                         ) debit ON xact.id = debit.xact
 280                 LEFT JOIN (
 281                         SELECT  payment_view.xact,
 282                                 sum(payment_view.amount) AS amount,
 283                                 max(payment_view.payment_ts) AS payment_ts,
 284                                 last(payment_view.note) AS note,
 285                                 last(payment_view.payment_type) AS payment_type
 286                           FROM  money.payment_view
 287                           WHERE payment_view.voided IS FALSE
 288                           GROUP BY payment_view.xact
 289                         ) credit ON xact.id = credit.xact
 290           ORDER BY debit.billing_ts, credit.payment_ts;
 291
 292 CREATE OR REPLACE VIEW money.usr_summary AS
 293         SELECT  usr,
 294                 SUM(total_paid) AS total_paid,
 295                 SUM(total_owed) AS total_owed,
 296                 SUM(balance_owed) AS balance_owed
 297           FROM money.billable_xact_summary
 298           GROUP BY 1;
 299
 300 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
 301         SELECT  usr,
 302                 SUM(total_paid) AS total_paid,
 303                 SUM(total_owed) AS total_owed,
 304                 SUM(balance_owed) AS balance_owed
 305           FROM  money.billable_xact_summary
 306           WHERE xact_type = 'circulation'
 307           GROUP BY 1;
 308
 309 CREATE TABLE money.bnm_payment (
 310         amount_collected        NUMERIC(6,2)    NOT NULL,
 311         accepting_usr           INT             NOT NULL
 312 ) INHERITS (money.payment);
 313 ALTER TABLE money.bnm_payment ADD PRIMARY KEY (id);
 314
 315 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
 316 ALTER TABLE money.forgive_payment ADD PRIMARY KEY (id);
 317 CREATE INDEX money_forgive_id_idx ON money.forgive_payment (id);
 318 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
 319 CREATE INDEX money_forgive_payment_payment_ts_idx ON money.forgive_payment (payment_ts);
 320 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
 321
 322 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
 323 ALTER TABLE money.work_payment ADD PRIMARY KEY (id);
 324 CREATE INDEX money_work_id_idx ON money.work_payment (id);
 325 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
 326 CREATE INDEX money_work_payment_payment_ts_idx ON money.work_payment (payment_ts);
 327 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
 328
 329 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
 330 ALTER TABLE money.credit_payment ADD PRIMARY KEY (id);
 331 CREATE INDEX money_credit_id_idx ON money.credit_payment (id);
 332 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
 333 CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment (payment_ts);
 334 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
 335
 336 CREATE TABLE money.goods_payment () INHERITS (money.bnm_payment);
 337 ALTER TABLE money.goods_payment ADD PRIMARY KEY (id);
 338 CREATE INDEX money_goods_id_idx ON money.goods_payment (id);
 339 CREATE INDEX money_goods_payment_xact_idx ON money.goods_payment (xact);
 340 CREATE INDEX money_goods_payment_payment_ts_idx ON money.goods_payment (payment_ts);
 341 CREATE INDEX money_goods_payment_accepting_usr_idx ON money.goods_payment (accepting_usr);
 342
 343 CREATE TABLE money.bnm_desk_payment (
 344         cash_drawer     INT     REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED
 345 ) INHERITS (money.bnm_payment);
 346 ALTER TABLE money.bnm_desk_payment ADD PRIMARY KEY (id);
 347
 348 CREATE OR REPLACE VIEW money.desk_payment_view AS
 349         SELECT  p.*,c.relname AS payment_type
 350           FROM  money.bnm_desk_payment p
 351                 JOIN pg_class c ON (p.tableoid = c.oid);
 352
 353 CREATE OR REPLACE VIEW money.bnm_payment_view AS
 354         SELECT  p.*,c.relname AS payment_type
 355           FROM  money.bnm_payment p
 356                 JOIN pg_class c ON (p.tableoid = c.oid);
 357
 358 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
 359 ALTER TABLE money.cash_payment ADD PRIMARY KEY (id);
 360 CREATE INDEX money_cash_id_idx ON money.cash_payment (id);
 361 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
 362 CREATE INDEX money_cash_payment_ts_idx ON money.cash_payment (payment_ts);
 363 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
 364 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
 365
 366 CREATE TABLE money.check_payment (
 367         check_number    TEXT    NOT NULL
 368 ) INHERITS (money.bnm_desk_payment);
 369 ALTER TABLE money.check_payment ADD PRIMARY KEY (id);
 370 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
 371 CREATE INDEX money_check_id_idx ON money.check_payment (id);
 372 CREATE INDEX money_check_payment_ts_idx ON money.check_payment (payment_ts);
 373 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
 374 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
 375
 376 CREATE TABLE money.credit_card_payment (
 377         cc_type         TEXT    NOT NULL,
 378         cc_number       TEXT    NOT NULL,
 379         expire_month    INT     NOT NULL,
 380         expire_year     INT     NOT NULL,
 381         approval_code   TEXT    NOT NULL
 382 ) INHERITS (money.bnm_desk_payment);
 383 ALTER TABLE money.credit_card_payment ADD PRIMARY KEY (id);
 384 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
 385 CREATE INDEX money_credit_card_id_idx ON money.credit_card_payment (id);
 386 CREATE INDEX money_credit_card_payment_ts_idx ON money.credit_card_payment (payment_ts);
 387 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
 388 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);
 389
 390 CREATE OR REPLACE VIEW money.non_drawer_payment_view AS
 391         SELECT  p.*, c.relname AS payment_type
 392           FROM  money.bnm_payment p
 393                         JOIN pg_class c ON p.tableoid = c.oid
 394           WHERE c.relname NOT IN ('cash_payment','check_payment','credit_card_payment');
 395
 396 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
 397         SELECT  ou.id AS org_unit,
 398                 ws.id AS cashdrawer,
 399                 t.payment_type AS payment_type,
 400                 p.payment_ts AS payment_ts,
 401                 p.amount AS amount,
 402                 p.voided AS voided,
 403                 p.note AS note
 404           FROM  actor.org_unit ou
 405                 JOIN actor.workstation ws ON (ou.id = ws.owning_lib)
 406                 LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer)
 407                 LEFT JOIN money.payment_view t ON (p.id = t.id);
 408
 409
 410 COMMIT;
 411