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.transaction_billing_type_summary AS
  63         SELECT  xact,
  64                 billing_type AS last_billing_type,
  65                 LAST(note) AS last_billing_note,
  66                 MAX(billing_ts) AS last_billing_ts,
  67                 SUM(COALESCE(amount,0)) AS total_owed
  68           FROM  money.billing
  69           WHERE voided IS FALSE
  70           GROUP BY xact,billing_type
  71           ORDER BY MAX(billing_ts);
  72
  73 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
  74         SELECT  xact,
  75                 LAST(billing_type) AS last_billing_type,
  76                 LAST(note) AS last_billing_note,
  77                 MAX(billing_ts) AS last_billing_ts,
  78                 SUM(COALESCE(amount,0)) AS total_owed
  79           FROM  money.billing
  80           WHERE voided IS FALSE
  81           GROUP BY xact
  82           ORDER BY MAX(billing_ts);
  83
  84 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
  85         SELECT  xact,
  86                 LAST(payment_type) AS last_payment_type,
  87                 LAST(note) AS last_payment_note,
  88                 MAX(payment_ts) as last_payment_ts,
  89                 SUM(COALESCE(amount,0)) AS total_paid
  90           FROM  money.payment_view
  91           WHERE voided IS FALSE
  92           GROUP BY xact
  93           ORDER BY MAX(payment_ts);
  94
  95 CREATE OR REPLACE VIEW money.transaction_billing_with_void_summary AS
  96         SELECT  xact,
  97                 LAST(billing_type) AS last_billing_type,
  98                 LAST(note) AS last_billing_note,
  99                 MAX(billing_ts) AS last_billing_ts,
 100                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_owed
 101           FROM  money.billing
 102           GROUP BY xact
 103           ORDER BY MAX(billing_ts);
 104
 105 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
 106         SELECT  xact,
 107                 LAST(payment_type) AS last_payment_type,
 108                 LAST(note) AS last_payment_note,
 109                 MAX(payment_ts) as last_payment_ts,
 110                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_paid
 111           FROM  money.payment_view
 112           GROUP BY xact
 113           ORDER BY MAX(payment_ts);
 114
 115 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
 116         SELECT  xact,
 117                 billing_type AS last_billing_type,
 118                 LAST(note) AS last_billing_note,
 119                 MAX(billing_ts) AS last_billing_ts,
 120                 SUM(COALESCE(amount,0)) AS total_owed
 121           FROM  money.billing
 122           WHERE voided IS FALSE
 123           GROUP BY xact,billing_type
 124           ORDER BY MAX(billing_ts);
 125
 126 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
 127         SELECT  xact,
 128                 LAST(billing_type) AS last_billing_type,
 129                 LAST(note) AS last_billing_note,
 130                 MAX(billing_ts) AS last_billing_ts,
 131                 SUM(COALESCE(amount,0)) AS total_owed
 132           FROM  money.billing
 133           WHERE voided IS FALSE
 134           GROUP BY xact
 135           ORDER BY MAX(billing_ts);
 136
 137 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
 138         SELECT  xact,
 139                 LAST(payment_type) AS last_payment_type,
 140                 LAST(note) AS last_payment_note,
 141                 MAX(payment_ts) as last_payment_ts,
 142                 SUM(COALESCE(amount,0)) AS total_paid
 143           FROM  money.payment_view
 144           WHERE voided IS FALSE
 145           GROUP BY xact
 146           ORDER BY MAX(payment_ts);
 147
 148 /* Replacing with the one below.
 149 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
 150         SELECT  xact.id AS id,
 151                 xact.usr AS usr,
 152                 xact.xact_start AS xact_start,
 153                 xact.xact_finish AS xact_finish,
 154                 credit.total_paid,
 155                 credit.last_payment_ts,
 156                 credit.last_payment_note,
 157                 credit.last_payment_type,
 158                 debit.total_owed,
 159                 debit.last_billing_ts,
 160                 debit.last_billing_note,
 161                 debit.last_billing_type,
 162                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 163                 p.relname AS xact_type
 164           FROM  money.billable_xact xact
 165                 JOIN pg_class p ON (xact.tableoid = p.oid)
 166                 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
 167                 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
 168 */
 169
 170 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
 171         SELECT  xact.id AS id,
 172                 xact.usr AS usr,
 173                 xact.xact_start AS xact_start,
 174                 xact.xact_finish AS xact_finish,
 175                 SUM(credit.amount) AS total_paid,
 176                 MAX(credit.payment_ts) AS last_payment_ts,
 177                 LAST(credit.note) AS last_payment_note,
 178                 LAST(credit.payment_type) AS last_payment_type,
 179                 SUM(debit.amount) AS total_owed,
 180                 MAX(debit.billing_ts) AS last_billing_ts,
 181                 LAST(debit.note) AS last_billing_note,
 182                 LAST(debit.billing_type) AS last_billing_type,
 183                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
 184                 p.relname AS xact_type
 185           FROM  money.billable_xact xact
 186                 JOIN pg_class p ON (xact.tableoid = p.oid)
 187                 LEFT JOIN money.billing debit ON (xact.id = debit.xact)
 188                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact)
 189           GROUP BY 1,2,3,4,14
 190           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
 191
 192 /* Replacing with the version below
 193 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 194         SELECT  xact.id AS id,
 195                 xact.usr AS usr,
 196                 xact.xact_start AS xact_start,
 197                 xact.xact_finish AS xact_finish,
 198                 credit.total_paid,
 199                 credit.last_payment_ts,
 200                 credit.last_payment_note,
 201                 credit.last_payment_type,
 202                 debit.total_owed,
 203                 debit.last_billing_ts,
 204                 debit.last_billing_note,
 205                 debit.last_billing_type,
 206                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 207                 p.relname AS xact_type
 208           FROM  money.billable_xact xact
 209                 JOIN pg_class p ON (xact.tableoid = p.oid)
 210                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 211                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
 212 */
 213
 214 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 215         SELECT  xact.id AS id,
 216                 xact.usr AS usr,
 217                 xact.xact_start AS xact_start,
 218                 xact.xact_finish AS xact_finish,
 219                 SUM(credit.amount) AS total_paid,
 220                 MAX(credit.payment_ts) AS last_payment_ts,
 221                 LAST(credit.note) AS last_payment_note,
 222                 LAST(credit.payment_type) AS last_payment_type,
 223                 SUM(debit.amount) AS total_owed,
 224                 MAX(debit.billing_ts) AS last_billing_ts,
 225                 LAST(debit.note) AS last_billing_note,
 226                 LAST(debit.billing_type) AS last_billing_type,
 227                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
 228                 p.relname AS xact_type
 229           FROM  money.billable_xact xact
 230                 JOIN pg_class p ON (xact.tableoid = p.oid)
 231                 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
 232                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
 233           GROUP BY 1,2,3,4,14
 234           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
 235
 236 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
 237         SELECT  xact.id AS id,
 238                 xact.usr AS usr,
 239                 COALESCE(circ.circ_lib,groc.billing_location) AS billing_location,
 240                 xact.xact_start AS xact_start,
 241                 xact.xact_finish AS xact_finish,
 242                 SUM(credit.amount) AS total_paid,
 243                 MAX(credit.payment_ts) AS last_payment_ts,
 244                 LAST(credit.note) AS last_payment_note,
 245                 LAST(credit.payment_type) AS last_payment_type,
 246                 SUM(debit.amount) AS total_owed,
 247                 MAX(debit.billing_ts) AS last_billing_ts,
 248                 LAST(debit.note) AS last_billing_note,
 249                 LAST(debit.billing_type) AS last_billing_type,
 250                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
 251                 p.relname AS xact_type
 252           FROM  money.billable_xact xact
 253                 JOIN pg_class p ON (xact.tableoid = p.oid)
 254                 LEFT JOIN "action".circulation circ ON (circ.id = xact.id)
 255                 LEFT JOIN money.grocery groc ON (circ.id = xact.id)
 256                 LEFT JOIN (
 257                         SELECT  billing.xact,
 258                                 billing.voided,
 259                                 sum(billing.amount) AS amount,
 260                                 max(billing.billing_ts) AS billing_ts,
 261                                 last(billing.note) AS note,
 262                                 last(billing.billing_type) AS billing_type
 263                           FROM  money.billing
 264                           WHERE billing.voided IS FALSE
 265                           GROUP BY billing.xact, billing.voided
 266                 ) debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
 267                 LEFT JOIN (
 268                         SELECT  payment_view.xact,
 269                                 payment_view.voided,
 270                                 sum(payment_view.amount) AS amount,
 271                                 max(payment_view.payment_ts) AS payment_ts,
 272                                 last(payment_view.note) AS note,
 273                                 last(payment_view.payment_type) AS payment_type
 274                           FROM  money.payment_view
 275                           WHERE payment_view.voided IS FALSE
 276                           GROUP BY payment_view.xact, payment_view.voided
 277                 ) credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
 278           WHERE xact.xact_finish IS NULL
 279           GROUP BY 1,2,3,4,5,15
 280           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
 281
 282
 283 CREATE OR REPLACE VIEW money.open_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.open_billable_xact_summary
 289           GROUP BY 1;
 290
 291 CREATE OR REPLACE VIEW money.open_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.open_billable_xact_summary
 297           WHERE xact_type = 'circulation'
 298           GROUP BY 1;
 299
 300 CREATE OR REPLACE VIEW money.usr_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           GROUP BY 1;
 307
 308 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
 309         SELECT  usr,
 310                 SUM(total_paid) AS total_paid,
 311                 SUM(total_owed) AS total_owed,
 312                 SUM(balance_owed) AS balance_owed
 313           FROM  money.billable_xact_summary
 314           WHERE xact_type = 'circulation'
 315           GROUP BY 1;
 316
 317 CREATE TABLE money.bnm_payment (
 318         amount_collected        NUMERIC(6,2)    NOT NULL,
 319         accepting_usr           INT             NOT NULL
 320 ) INHERITS (money.payment);
 321 ALTER TABLE money.bnm_payment ADD PRIMARY KEY (id);
 322
 323 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
 324 ALTER TABLE money.forgive_payment ADD PRIMARY KEY (id);
 325 CREATE INDEX money_forgive_id_idx ON money.forgive_payment (id);
 326 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
 327 CREATE INDEX money_forgive_payment_payment_ts_idx ON money.forgive_payment (payment_ts);
 328 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
 329
 330 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
 331 ALTER TABLE money.work_payment ADD PRIMARY KEY (id);
 332 CREATE INDEX money_work_id_idx ON money.work_payment (id);
 333 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
 334 CREATE INDEX money_work_payment_payment_ts_idx ON money.work_payment (payment_ts);
 335 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
 336
 337 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
 338 ALTER TABLE money.credit_payment ADD PRIMARY KEY (id);
 339 CREATE INDEX money_credit_id_idx ON money.credit_payment (id);
 340 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
 341 CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment (payment_ts);
 342 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
 343
 344 CREATE TABLE money.bnm_desk_payment (
 345         cash_drawer     INT     REFERENCES actor.workstation (id)
 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