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