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 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
 147         SELECT  xact.id AS id,
 148                 xact.usr AS usr,
 149                 xact.xact_start AS xact_start,
 150                 xact.xact_finish AS xact_finish,
 151                 credit.total_paid,
 152                 credit.last_payment_ts,
 153                 credit.last_payment_note,
 154                 credit.last_payment_type,
 155                 debit.total_owed,
 156                 debit.last_billing_ts,
 157                 debit.last_billing_note,
 158                 debit.last_billing_type,
 159                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 160                 p.relname AS xact_type
 161           FROM  money.billable_xact xact
 162                 JOIN pg_class p ON (xact.tableoid = p.oid)
 163                 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
 164                 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
 165
 166 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 167         SELECT  xact.id AS id,
 168                 xact.usr AS usr,
 169                 xact.xact_start AS xact_start,
 170                 xact.xact_finish AS xact_finish,
 171                 credit.total_paid,
 172                 credit.last_payment_ts,
 173                 credit.last_payment_note,
 174                 credit.last_payment_type,
 175                 debit.total_owed,
 176                 debit.last_billing_ts,
 177                 debit.last_billing_note,
 178                 debit.last_billing_type,
 179                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 180                 p.relname AS xact_type
 181           FROM  money.billable_xact xact
 182                 JOIN pg_class p ON (xact.tableoid = p.oid)
 183                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 184                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
 185
 186 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
 187         SELECT  xact.id AS id,
 188                 xact.usr AS usr,
 189                 xact.xact_start AS xact_start,
 190                 xact.xact_finish AS xact_finish,
 191                 SUM(credit.amount) AS total_paid,
 192                 MAX(credit.payment_ts) AS last_payment_ts,
 193                 LAST(credit.note) AS last_payment_note,
 194                 LAST(credit.payment_type) AS last_payment_type,
 195                 SUM(debit.amount) AS total_owed,
 196                 MAX(debit.billing_ts) AS last_billing_ts,
 197                 LAST(debit.note) AS last_billing_note,
 198                 LAST(debit.billing_type) AS last_billing_type,
 199                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
 200                 p.relname AS xact_type
 201           FROM  money.billable_xact xact
 202                 JOIN pg_class p ON (xact.tableoid = p.oid)
 203                 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
 204                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
 205           WHERE xact.xact_finish IS NULL
 206           GROUP BY 1,2,3,4,14
 207           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
 208
 209
 210 CREATE OR REPLACE VIEW money.open_usr_summary AS
 211         SELECT  usr,
 212                 SUM(total_paid) AS total_paid,
 213                 SUM(total_owed) AS total_owed,
 214                 SUM(balance_owed) AS balance_owed
 215           FROM money.open_billable_xact_summary
 216           GROUP BY 1;
 217
 218 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
 219         SELECT  usr,
 220                 SUM(total_paid) AS total_paid,
 221                 SUM(total_owed) AS total_owed,
 222                 SUM(balance_owed) AS balance_owed
 223           FROM  money.open_billable_xact_summary
 224           WHERE xact_type = 'circulation'
 225           GROUP BY 1;
 226
 227 CREATE OR REPLACE VIEW money.usr_summary AS
 228         SELECT  usr,
 229                 SUM(total_paid) AS total_paid,
 230                 SUM(total_owed) AS total_owed,
 231                 SUM(balance_owed) AS balance_owed
 232           FROM money.billable_xact_summary
 233           GROUP BY 1;
 234
 235 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
 236         SELECT  usr,
 237                 SUM(total_paid) AS total_paid,
 238                 SUM(total_owed) AS total_owed,
 239                 SUM(balance_owed) AS balance_owed
 240           FROM  money.billable_xact_summary
 241           WHERE xact_type = 'circulation'
 242           GROUP BY 1;
 243
 244 CREATE TABLE money.bnm_payment (
 245         amount_collected        NUMERIC(6,2)    NOT NULL,
 246         accepting_usr           INT             NOT NULL
 247 ) INHERITS (money.payment);
 248 ALTER TABLE money.bnm_payment ADD PRIMARY KEY (id);
 249
 250 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
 251 ALTER TABLE money.forgive_payment ADD PRIMARY KEY (id);
 252 CREATE INDEX money_forgive_id_idx ON money.forgive_payment (id);
 253 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
 254 CREATE INDEX money_forgive_payment_payment_ts_idx ON money.forgive_payment (payment_ts);
 255 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
 256
 257 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
 258 ALTER TABLE money.work_payment ADD PRIMARY KEY (id);
 259 CREATE INDEX money_work_id_idx ON money.work_payment (id);
 260 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
 261 CREATE INDEX money_work_payment_payment_ts_idx ON money.work_payment (payment_ts);
 262 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
 263
 264 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
 265 ALTER TABLE money.credit_payment ADD PRIMARY KEY (id);
 266 CREATE INDEX money_credit_id_idx ON money.credit_payment (id);
 267 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
 268 CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment (payment_ts);
 269 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
 270
 271 CREATE TABLE money.bnm_desk_payment (
 272         cash_drawer     INT     REFERENCES actor.workstation (id)
 273 ) INHERITS (money.bnm_payment);
 274 ALTER TABLE money.bnm_desk_payment ADD PRIMARY KEY (id);
 275
 276 CREATE OR REPLACE VIEW money.desk_payment_view AS
 277         SELECT  p.*,c.relname AS payment_type
 278           FROM  money.bnm_desk_payment p
 279                 JOIN pg_class c ON (p.tableoid = c.oid);
 280
 281 CREATE OR REPLACE VIEW money.bnm_payment_view AS
 282         SELECT  p.*,c.relname AS payment_type
 283           FROM  money.bnm_payment p
 284                 JOIN pg_class c ON (p.tableoid = c.oid);
 285
 286 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
 287 ALTER TABLE money.cash_payment ADD PRIMARY KEY (id);
 288 CREATE INDEX money_cash_id_idx ON money.cash_payment (id);
 289 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
 290 CREATE INDEX money_cash_payment_ts_idx ON money.cash_payment (payment_ts);
 291 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
 292 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
 293
 294 CREATE TABLE money.check_payment (
 295         check_number    TEXT    NOT NULL
 296 ) INHERITS (money.bnm_desk_payment);
 297 ALTER TABLE money.check_payment ADD PRIMARY KEY (id);
 298 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
 299 CREATE INDEX money_check_id_idx ON money.check_payment (id);
 300 CREATE INDEX money_check_payment_ts_idx ON money.check_payment (payment_ts);
 301 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
 302 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
 303
 304 CREATE TABLE money.credit_card_payment (
 305         cc_type         TEXT    NOT NULL,
 306         cc_number       TEXT    NOT NULL,
 307         expire_month    INT     NOT NULL,
 308         expire_year     INT     NOT NULL,
 309         approval_code   TEXT    NOT NULL
 310 ) INHERITS (money.bnm_desk_payment);
 311 ALTER TABLE money.credit_card_payment ADD PRIMARY KEY (id);
 312 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
 313 CREATE INDEX money_credit_card_id_idx ON money.credit_card_payment (id);
 314 CREATE INDEX money_credit_card_payment_ts_idx ON money.credit_card_payment (payment_ts);
 315 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
 316 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);
 317
 318 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
 319         SELECT  ou.id AS org_unit,
 320                 ws.id AS cashdrawer,
 321                 t.payment_type AS payment_type,
 322                 p.payment_ts AS payment_ts,
 323                 p.amount AS amount,
 324                 p.voided AS voided,
 325                 p.note AS note
 326           FROM  actor.org_unit ou
 327                 JOIN actor.workstation ws ON (ou.id = ws.owning_lib)
 328                 LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer)
 329                 LEFT JOIN money.payment_view t ON (p.id = t.id);
 330
 331
 332 COMMIT;
 333