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