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