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     unrecovered BOOL
  39 );
  40 CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr);
  41
  42 CREATE TABLE money.grocery ( -- Catchall table for local billing
  43         billing_location        INT     NOT NULL, -- library creating transaction
  44         note                    TEXT
  45 ) INHERITS (money.billable_xact);
  46 ALTER TABLE money.grocery ADD PRIMARY KEY (id);
  47 CREATE INDEX circ_open_date_idx ON "money".grocery (xact_start) WHERE xact_finish IS NULL;
  48 CREATE INDEX m_g_usr_idx ON "money".grocery (usr);
  49
  50 CREATE TABLE money.billing (
  51         id              BIGSERIAL                       PRIMARY KEY,
  52         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  53         billing_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  54         voided          BOOL                            NOT NULL DEFAULT FALSE,
  55         voider          INT,
  56         void_time       TIMESTAMP WITH TIME ZONE,
  57         amount          NUMERIC(6,2)                    NOT NULL,
  58         billing_type    TEXT                            NOT NULL,
  59         btype           INT                             NOT NULL REFERENCES config.billing_type (id) ON DELETE RESTRICT,
  60         note            TEXT
  61 );
  62 CREATE INDEX m_b_xact_idx ON money.billing (xact);
  63 CREATE INDEX m_b_time_idx ON money.billing (billing_ts);
  64
  65 CREATE TABLE money.payment (
  66         id              BIGSERIAL                       PRIMARY KEY,
  67         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  68         payment_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  69         voided          BOOL                            NOT NULL DEFAULT FALSE,
  70         amount          NUMERIC(6,2)                    NOT NULL,
  71         note            TEXT
  72 );
  73 CREATE INDEX m_p_xact_idx ON money.payment (xact);
  74 CREATE INDEX m_p_time_idx ON money.payment (payment_ts);
  75
  76 CREATE OR REPLACE VIEW money.payment_view AS
  77         SELECT  p.*,c.relname AS payment_type
  78           FROM  money.payment p
  79                 JOIN pg_class c ON (p.tableoid = c.oid);
  80
  81 CREATE OR REPLACE VIEW money.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.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.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.transaction_billing_with_void_summary AS
 115         SELECT  xact,
 116                 LAST(billing_type) AS last_billing_type,
 117                 LAST(note) AS last_billing_note,
 118                 MAX(billing_ts) AS last_billing_ts,
 119                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_owed
 120           FROM  money.billing
 121           GROUP BY xact
 122           ORDER BY MAX(billing_ts);
 123
 124 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
 125         SELECT  xact,
 126                 LAST(payment_type) AS last_payment_type,
 127                 LAST(note) AS last_payment_note,
 128                 MAX(payment_ts) as last_payment_ts,
 129                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_paid
 130           FROM  money.payment_view
 131           GROUP BY xact
 132           ORDER BY MAX(payment_ts);
 133
 134 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
 135         SELECT  xact,
 136                 billing_type AS last_billing_type,
 137                 LAST(note) AS last_billing_note,
 138                 MAX(billing_ts) AS last_billing_ts,
 139                 SUM(COALESCE(amount,0)) AS total_owed
 140           FROM  money.billing
 141           WHERE voided IS FALSE
 142           GROUP BY xact,billing_type
 143           ORDER BY MAX(billing_ts);
 144
 145 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
 146         SELECT  xact,
 147                 LAST(billing_type) AS last_billing_type,
 148                 LAST(note) AS last_billing_note,
 149                 MAX(billing_ts) AS last_billing_ts,
 150                 SUM(COALESCE(amount,0)) AS total_owed
 151           FROM  money.billing
 152           WHERE voided IS FALSE
 153           GROUP BY xact
 154           ORDER BY MAX(billing_ts);
 155
 156 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
 157         SELECT  xact,
 158                 LAST(payment_type) AS last_payment_type,
 159                 LAST(note) AS last_payment_note,
 160                 MAX(payment_ts) as last_payment_ts,
 161                 SUM(COALESCE(amount,0)) AS total_paid
 162           FROM  money.payment_view
 163           WHERE voided IS FALSE
 164           GROUP BY xact
 165           ORDER BY MAX(payment_ts);
 166
 167 /* Replacing with the one below.
 168 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
 169         SELECT  xact.id AS id,
 170                 xact.usr AS usr,
 171                 xact.xact_start AS xact_start,
 172                 xact.xact_finish AS xact_finish,
 173                 credit.total_paid,
 174                 credit.last_payment_ts,
 175                 credit.last_payment_note,
 176                 credit.last_payment_type,
 177                 debit.total_owed,
 178                 debit.last_billing_ts,
 179                 debit.last_billing_note,
 180                 debit.last_billing_type,
 181                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 182                 p.relname AS xact_type
 183           FROM  money.billable_xact xact
 184                 JOIN pg_class p ON (xact.tableoid = p.oid)
 185                 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
 186                 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
 187 */
 188
 189 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
 190         SELECT  xact.id AS id,
 191                 xact.usr AS usr,
 192                 xact.xact_start AS xact_start,
 193                 xact.xact_finish AS xact_finish,
 194                 SUM(credit.amount) AS total_paid,
 195                 MAX(credit.payment_ts) AS last_payment_ts,
 196                 LAST(credit.note) AS last_payment_note,
 197                 LAST(credit.payment_type) AS last_payment_type,
 198                 SUM(debit.amount) AS total_owed,
 199                 MAX(debit.billing_ts) AS last_billing_ts,
 200                 LAST(debit.note) AS last_billing_note,
 201                 LAST(debit.billing_type) AS last_billing_type,
 202                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
 203                 p.relname AS xact_type
 204           FROM  money.billable_xact xact
 205                 JOIN pg_class p ON (xact.tableoid = p.oid)
 206                 LEFT JOIN money.billing debit ON (xact.id = debit.xact)
 207                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact)
 208           GROUP BY 1,2,3,4,14
 209           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
 210
 211 /* Replacing with the version below
 212 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 213         SELECT  xact.id AS id,
 214                 xact.usr AS usr,
 215                 xact.xact_start AS xact_start,
 216                 xact.xact_finish AS xact_finish,
 217                 credit.total_paid,
 218                 credit.last_payment_ts,
 219                 credit.last_payment_note,
 220                 credit.last_payment_type,
 221                 debit.total_owed,
 222                 debit.last_billing_ts,
 223                 debit.last_billing_note,
 224                 debit.last_billing_type,
 225                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 226                 p.relname AS xact_type
 227           FROM  money.billable_xact xact
 228                 JOIN pg_class p ON (xact.tableoid = p.oid)
 229                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 230                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
 231
 232 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 233         SELECT  xact.id AS id,
 234                 xact.usr AS usr,
 235                 xact.xact_start AS xact_start,
 236                 xact.xact_finish AS xact_finish,
 237                 SUM(credit.amount) AS total_paid,
 238                 MAX(credit.payment_ts) AS last_payment_ts,
 239                 LAST(credit.note) AS last_payment_note,
 240                 LAST(credit.payment_type) AS last_payment_type,
 241                 SUM(debit.amount) AS total_owed,
 242                 MAX(debit.billing_ts) AS last_billing_ts,
 243                 LAST(debit.note) AS last_billing_note,
 244                 LAST(debit.billing_type) AS last_billing_type,
 245                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
 246                 p.relname AS xact_type
 247           FROM  money.billable_xact xact
 248                 JOIN pg_class p ON (xact.tableoid = p.oid)
 249                 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
 250                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
 251           GROUP BY 1,2,3,4,14
 252           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
 253 */
 254
 255 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 256         SELECT  xact.id,
 257                 xact.usr,
 258                 xact.xact_start,
 259                 xact.xact_finish,
 260                 credit.amount AS total_paid,
 261                 credit.payment_ts AS last_payment_ts,
 262                 credit.note AS last_payment_note,
 263                 credit.payment_type AS last_payment_type,
 264                 debit.amount AS total_owed,
 265                 debit.billing_ts AS last_billing_ts,
 266                 debit.note AS last_billing_note,
 267                 debit.billing_type AS last_billing_type,
 268                 COALESCE(debit.amount, 0::numeric) - COALESCE(credit.amount, 0::numeric) AS balance_owed,
 269                 p.relname AS xact_type
 270           FROM  money.billable_xact xact
 271                 JOIN pg_class p ON xact.tableoid = p.oid
 272                 LEFT JOIN (
 273                         SELECT  billing.xact,
 274                                 sum(billing.amount) AS amount,
 275                                 max(billing.billing_ts) AS billing_ts,
 276                                 last(billing.note) AS note,
 277                                 last(billing.billing_type) AS billing_type
 278                           FROM  money.billing
 279                           WHERE billing.voided IS FALSE
 280                           GROUP BY billing.xact
 281                         ) debit ON xact.id = debit.xact
 282                 LEFT JOIN (
 283                         SELECT  payment_view.xact,
 284                                 sum(payment_view.amount) AS amount,
 285                                 max(payment_view.payment_ts) AS payment_ts,
 286                                 last(payment_view.note) AS note,
 287                                 last(payment_view.payment_type) AS payment_type
 288                           FROM  money.payment_view
 289                           WHERE payment_view.voided IS FALSE
 290                           GROUP BY payment_view.xact
 291                         ) credit ON xact.id = credit.xact
 292           ORDER BY debit.billing_ts, credit.payment_ts;
 293
 294 CREATE OR REPLACE VIEW money.usr_summary AS
 295         SELECT  usr,
 296                 SUM(total_paid) AS total_paid,
 297                 SUM(total_owed) AS total_owed,
 298                 SUM(balance_owed) AS balance_owed
 299           FROM money.billable_xact_summary
 300           GROUP BY 1;
 301
 302 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
 303         SELECT  usr,
 304                 SUM(total_paid) AS total_paid,
 305                 SUM(total_owed) AS total_owed,
 306                 SUM(balance_owed) AS balance_owed
 307           FROM  money.billable_xact_summary
 308           WHERE xact_type = 'circulation'
 309           GROUP BY 1;
 310
 311 CREATE TABLE money.bnm_payment (
 312         amount_collected        NUMERIC(6,2)    NOT NULL,
 313         accepting_usr           INT             NOT NULL
 314 ) INHERITS (money.payment);
 315 ALTER TABLE money.bnm_payment ADD PRIMARY KEY (id);
 316
 317 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
 318 ALTER TABLE money.forgive_payment ADD PRIMARY KEY (id);
 319 CREATE INDEX money_forgive_id_idx ON money.forgive_payment (id);
 320 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
 321 CREATE INDEX money_forgive_payment_payment_ts_idx ON money.forgive_payment (payment_ts);
 322 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
 323
 324 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
 325 ALTER TABLE money.work_payment ADD PRIMARY KEY (id);
 326 CREATE INDEX money_work_id_idx ON money.work_payment (id);
 327 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
 328 CREATE INDEX money_work_payment_payment_ts_idx ON money.work_payment (payment_ts);
 329 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
 330
 331 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
 332 ALTER TABLE money.credit_payment ADD PRIMARY KEY (id);
 333 CREATE INDEX money_credit_id_idx ON money.credit_payment (id);
 334 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
 335 CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment (payment_ts);
 336 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
 337
 338 CREATE TABLE money.goods_payment () INHERITS (money.bnm_payment);
 339 ALTER TABLE money.goods_payment ADD PRIMARY KEY (id);
 340 CREATE INDEX money_goods_id_idx ON money.goods_payment (id);
 341 CREATE INDEX money_goods_payment_xact_idx ON money.goods_payment (xact);
 342 CREATE INDEX money_goods_payment_payment_ts_idx ON money.goods_payment (payment_ts);
 343 CREATE INDEX money_goods_payment_accepting_usr_idx ON money.goods_payment (accepting_usr);
 344
 345 CREATE TABLE money.bnm_desk_payment (
 346         cash_drawer     INT     REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED
 347 ) INHERITS (money.bnm_payment);
 348 ALTER TABLE money.bnm_desk_payment ADD PRIMARY KEY (id);
 349
 350 CREATE OR REPLACE VIEW money.desk_payment_view AS
 351         SELECT  p.*,c.relname AS payment_type
 352           FROM  money.bnm_desk_payment p
 353                 JOIN pg_class c ON (p.tableoid = c.oid);
 354
 355 CREATE OR REPLACE VIEW money.bnm_payment_view AS
 356         SELECT  p.*,c.relname AS payment_type
 357           FROM  money.bnm_payment p
 358                 JOIN pg_class c ON (p.tableoid = c.oid);
 359
 360 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
 361 ALTER TABLE money.cash_payment ADD PRIMARY KEY (id);
 362 CREATE INDEX money_cash_id_idx ON money.cash_payment (id);
 363 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
 364 CREATE INDEX money_cash_payment_ts_idx ON money.cash_payment (payment_ts);
 365 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
 366 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
 367
 368 CREATE TABLE money.check_payment (
 369         check_number    TEXT    NOT NULL
 370 ) INHERITS (money.bnm_desk_payment);
 371 ALTER TABLE money.check_payment ADD PRIMARY KEY (id);
 372 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
 373 CREATE INDEX money_check_id_idx ON money.check_payment (id);
 374 CREATE INDEX money_check_payment_ts_idx ON money.check_payment (payment_ts);
 375 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
 376 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
 377
 378 CREATE TABLE money.credit_card_payment (
 379         cc_type         TEXT,
 380         cc_number       TEXT,
 381         expire_month    INT,
 382         expire_year     INT,
 383         approval_code   TEXT
 384 ) INHERITS (money.bnm_desk_payment);
 385 ALTER TABLE money.credit_card_payment ADD PRIMARY KEY (id);
 386 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
 387 CREATE INDEX money_credit_card_id_idx ON money.credit_card_payment (id);
 388 CREATE INDEX money_credit_card_payment_ts_idx ON money.credit_card_payment (payment_ts);
 389 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
 390 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);
 391
 392 CREATE OR REPLACE VIEW money.non_drawer_payment_view AS
 393         SELECT  p.*, c.relname AS payment_type
 394           FROM  money.bnm_payment p
 395                         JOIN pg_class c ON p.tableoid = c.oid
 396           WHERE c.relname NOT IN ('cash_payment','check_payment','credit_card_payment');
 397
 398 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
 399         SELECT  ou.id AS org_unit,
 400                 ws.id AS cashdrawer,
 401                 t.payment_type AS payment_type,
 402                 p.payment_ts AS payment_ts,
 403                 p.amount AS amount,
 404                 p.voided AS voided,
 405                 p.note AS note
 406           FROM  actor.org_unit ou
 407                 JOIN actor.workstation ws ON (ou.id = ws.owning_lib)
 408                 LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer)
 409                 LEFT JOIN money.payment_view t ON (p.id = t.id);
 410
 411
 412 COMMIT;
 413