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.billable_xact (
   8         id              BIGSERIAL                       PRIMARY KEY,
   9         usr             INT                             NOT NULL, -- actor.usr.id
  10         xact_start      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  11         xact_finish     TIMESTAMP WITH TIME ZONE
  12 );
  13 CREATE INDEX m_b_x_open_xacts_idx ON money.billable_xact (usr) WHERE xact_finish IS NULL;
  14
  15 CREATE TABLE money.grocery ( -- Catchall table for local billing
  16         billing_location        INT     NOT NULL, -- library creating transaction
  17         note                    TEXT
  18 ) INHERITS (money.billable_xact);
  19
  20 CREATE TABLE money.billing (
  21         id              BIGSERIAL                       PRIMARY KEY,
  22         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  23         billing_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  24         voided          BOOL                            NOT NULL DEFAULT FALSE,
  25         voider          INT,
  26         void_time       TIMESTAMP WITH TIME ZONE,
  27         amount          NUMERIC(6,2)                    NOT NULL,
  28         billing_type    TEXT                            NOT NULL,
  29         note            TEXT
  30 );
  31 CREATE INDEX m_b_xact_idx ON money.billing (xact);
  32
  33 CREATE TABLE money.payment (
  34         id              BIGSERIAL                       PRIMARY KEY,
  35         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  36         payment_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  37         voided          BOOL                            NOT NULL DEFAULT FALSE,
  38         amount          NUMERIC(6,2)                    NOT NULL,
  39         note            TEXT
  40 );
  41 CREATE INDEX m_p_xact_idx ON money.payment (xact);
  42
  43 CREATE OR REPLACE VIEW money.payment_view AS
  44         SELECT  p.*,c.relname AS payment_type
  45           FROM  money.payment p
  46                 JOIN pg_class c ON (p.tableoid = c.oid);
  47
  48 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
  49         SELECT  xact,
  50                 billing_type AS last_billing_type,
  51                 LAST(note) AS last_billing_note,
  52                 MAX(billing_ts) AS last_billing_ts,
  53                 SUM(COALESCE(amount,0)) AS total_owed
  54           FROM  money.billing
  55           WHERE voided IS FALSE
  56           GROUP BY xact,billing_type
  57           ORDER BY MAX(billing_ts);
  58
  59 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
  60         SELECT  xact,
  61                 LAST(billing_type) AS last_billing_type,
  62                 LAST(note) AS last_billing_note,
  63                 MAX(billing_ts) AS last_billing_ts,
  64                 SUM(COALESCE(amount,0)) AS total_owed
  65           FROM  money.billing
  66           WHERE voided IS FALSE
  67           GROUP BY xact
  68           ORDER BY MAX(billing_ts);
  69
  70 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
  71         SELECT  xact,
  72                 LAST(payment_type) AS last_payment_type,
  73                 LAST(note) AS last_payment_note,
  74                 MAX(payment_ts) as last_payment_ts,
  75                 SUM(COALESCE(amount,0)) AS total_paid
  76           FROM  money.payment_view
  77           WHERE voided IS FALSE
  78           GROUP BY xact
  79           ORDER BY MAX(payment_ts);
  80
  81 CREATE OR REPLACE VIEW money.transaction_billing_with_void_summary AS
  82         SELECT  xact,
  83                 LAST(billing_type) AS last_billing_type,
  84                 LAST(note) AS last_billing_note,
  85                 MAX(billing_ts) AS last_billing_ts,
  86                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_owed
  87           FROM  money.billing
  88           GROUP BY xact
  89           ORDER BY MAX(billing_ts);
  90
  91 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
  92         SELECT  xact,
  93                 LAST(payment_type) AS last_payment_type,
  94                 LAST(note) AS last_payment_note,
  95                 MAX(payment_ts) as last_payment_ts,
  96                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_paid
  97           FROM  money.payment_view
  98           GROUP BY xact
  99           ORDER BY MAX(payment_ts);
 100
 101 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
 102         SELECT  xact,
 103                 billing_type AS last_billing_type,
 104                 LAST(note) AS last_billing_note,
 105                 MAX(billing_ts) AS last_billing_ts,
 106                 SUM(COALESCE(amount,0)) AS total_owed
 107           FROM  money.billing
 108           WHERE voided IS FALSE
 109           GROUP BY xact,billing_type
 110           ORDER BY MAX(billing_ts);
 111
 112 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
 113         SELECT  xact,
 114                 LAST(billing_type) AS last_billing_type,
 115                 LAST(note) AS last_billing_note,
 116                 MAX(billing_ts) AS last_billing_ts,
 117                 SUM(COALESCE(amount,0)) AS total_owed
 118           FROM  money.billing
 119           WHERE voided IS FALSE
 120           GROUP BY xact
 121           ORDER BY MAX(billing_ts);
 122
 123 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
 124         SELECT  xact,
 125                 LAST(payment_type) AS last_payment_type,
 126                 LAST(note) AS last_payment_note,
 127                 MAX(payment_ts) as last_payment_ts,
 128                 SUM(COALESCE(amount,0)) AS total_paid
 129           FROM  money.payment_view
 130           WHERE voided IS FALSE
 131           GROUP BY xact
 132           ORDER BY MAX(payment_ts);
 133
 134 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
 135         SELECT  xact.id AS id,
 136                 xact.usr AS usr,
 137                 xact.xact_start AS xact_start,
 138                 xact.xact_finish AS xact_finish,
 139                 credit.total_paid,
 140                 credit.last_payment_ts,
 141                 credit.last_payment_note,
 142                 credit.last_payment_type,
 143                 debit.total_owed,
 144                 debit.last_billing_ts,
 145                 debit.last_billing_note,
 146                 debit.last_billing_type,
 147                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 148                 p.relname AS xact_type
 149           FROM  money.billable_xact xact
 150                 JOIN pg_class p ON (xact.tableoid = p.oid)
 151                 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
 152                 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
 153
 154 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 155         SELECT  xact.id AS id,
 156                 xact.usr AS usr,
 157                 xact.xact_start AS xact_start,
 158                 xact.xact_finish AS xact_finish,
 159                 credit.total_paid,
 160                 credit.last_payment_ts,
 161                 credit.last_payment_note,
 162                 credit.last_payment_type,
 163                 debit.total_owed,
 164                 debit.last_billing_ts,
 165                 debit.last_billing_note,
 166                 debit.last_billing_type,
 167                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 168                 p.relname AS xact_type
 169           FROM  money.billable_xact xact
 170                 JOIN pg_class p ON (xact.tableoid = p.oid)
 171                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 172                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
 173
 174 CREATE OR REPLACE VIEW money.open_billable_xact_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                 credit.total_paid,
 180                 credit.last_payment_ts,
 181                 credit.last_payment_note,
 182                 credit.last_payment_type,
 183                 debit.total_owed,
 184                 debit.last_billing_ts,
 185                 debit.last_billing_note,
 186                 debit.last_billing_type,
 187                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,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.transaction_billing_summary debit ON (xact.id = debit.xact)
 192                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact)
 193           WHERE xact.xact_finish IS NULL;
 194
 195 CREATE OR REPLACE VIEW money.open_usr_summary AS
 196         SELECT  usr,
 197                 SUM(total_paid) AS total_paid,
 198                 SUM(total_owed) AS total_owed,
 199                 SUM(balance_owed) AS balance_owed
 200           FROM money.open_billable_xact_summary
 201           GROUP BY 1;
 202
 203 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
 204         SELECT  usr,
 205                 SUM(total_paid) AS total_paid,
 206                 SUM(total_owed) AS total_owed,
 207                 SUM(balance_owed) AS balance_owed
 208           FROM  money.open_billable_xact_summary
 209           WHERE xact_type = 'circulation'
 210           GROUP BY 1;
 211
 212 CREATE OR REPLACE VIEW money.usr_summary AS
 213         SELECT  usr,
 214                 SUM(total_paid) AS total_paid,
 215                 SUM(total_owed) AS total_owed,
 216                 SUM(balance_owed) AS balance_owed
 217           FROM money.billable_xact_summary
 218           GROUP BY 1;
 219
 220 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
 221         SELECT  usr,
 222                 SUM(total_paid) AS total_paid,
 223                 SUM(total_owed) AS total_owed,
 224                 SUM(balance_owed) AS balance_owed
 225           FROM  money.billable_xact_summary
 226           WHERE xact_type = 'circulation'
 227           GROUP BY 1;
 228
 229 CREATE TABLE money.bnm_payment (
 230         amount_collected        NUMERIC(6,2)    NOT NULL,
 231         accepting_usr           INT             NOT NULL
 232 ) INHERITS (money.payment);
 233
 234 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
 235 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
 236 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
 237
 238 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
 239 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
 240 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
 241
 242 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
 243 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
 244 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
 245
 246 CREATE TABLE money.bnm_desk_payment (
 247         cash_drawer     INT     REFERENCES actor.workstation (id)
 248 ) INHERITS (money.bnm_payment);
 249
 250 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
 251 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
 252 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
 253 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
 254
 255 CREATE TABLE money.check_payment (
 256         check_number    TEXT    NOT NULL
 257 ) INHERITS (money.bnm_desk_payment);
 258 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
 259 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
 260 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
 261
 262 CREATE TABLE money.credit_card_payment (
 263         cc_type         TEXT    NOT NULL,
 264         cc_number       TEXT    NOT NULL,
 265         expire_month    INT     NOT NULL,
 266         expire_year     INT     NOT NULL,
 267         approval_code   TEXT    NOT NULL
 268 ) INHERITS (money.bnm_desk_payment);
 269 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
 270 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
 271 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);
 272
 273
 274 COMMIT;
 275