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