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         amount          NUMERIC(6,2)                    NOT NULL,
  26         billing_type    TEXT                            NOT NULL,
  27         note            TEXT
  28 );
  29 CREATE INDEX m_b_xact_idx ON money.billing (xact);
  30
  31 CREATE TABLE money.payment (
  32         id              BIGSERIAL                       PRIMARY KEY,
  33         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  34         payment_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  35         voided          BOOL                            NOT NULL DEFAULT FALSE,
  36         amount          NUMERIC(6,2)                    NOT NULL,
  37         note            TEXT
  38 );
  39 CREATE INDEX m_p_xact_idx ON money.payment (xact);
  40
  41 CREATE OR REPLACE VIEW money.payment_view AS
  42         SELECT  p.*,c.relname AS payment_type
  43           FROM  money.payment p
  44                 JOIN pg_class c ON (p.tableoid = c.oid);
  45
  46 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
  47         SELECT  xact,
  48                 billing_type AS last_billing_type,
  49                 LAST(note) AS last_billing_note,
  50                 MAX(billing_ts) AS last_billing_ts,
  51                 SUM(COALESCE(amount,0)) AS total_owed
  52           FROM  money.billing
  53           WHERE voided IS FALSE
  54           GROUP BY xact,billing_type
  55           ORDER BY MAX(billing_ts);
  56
  57 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
  58         SELECT  xact,
  59                 LAST(billing_type) AS last_billing_type,
  60                 LAST(note) AS last_billing_note,
  61                 MAX(billing_ts) AS last_billing_ts,
  62                 SUM(COALESCE(amount,0)) AS total_owed
  63           FROM  money.billing
  64           WHERE voided IS FALSE
  65           GROUP BY xact
  66           ORDER BY MAX(billing_ts);
  67
  68 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
  69         SELECT  xact,
  70                 LAST(payment_type) AS last_payment_type,
  71                 LAST(note) AS last_payment_note,
  72                 MAX(payment_ts) as last_payment_ts,
  73                 SUM(COALESCE(amount,0)) AS total_paid
  74           FROM  money.payment_view
  75           WHERE voided IS FALSE
  76           GROUP BY xact
  77           ORDER BY MAX(payment_ts);
  78
  79 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
  80         SELECT  xact,
  81                 billing_type AS last_billing_type,
  82                 LAST(note) AS last_billing_note,
  83                 MAX(billing_ts) AS last_billing_ts,
  84                 SUM(COALESCE(amount,0)) AS total_owed
  85           FROM  money.billing
  86           WHERE voided IS FALSE
  87           GROUP BY xact,billing_type
  88           ORDER BY MAX(billing_ts);
  89
  90 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
  91         SELECT  xact,
  92                 LAST(billing_type) AS last_billing_type,
  93                 LAST(note) AS last_billing_note,
  94                 MAX(billing_ts) AS last_billing_ts,
  95                 SUM(COALESCE(amount,0)) AS total_owed
  96           FROM  money.billing
  97           WHERE voided IS FALSE
  98           GROUP BY xact
  99           ORDER BY MAX(billing_ts);
 100
 101 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
 102         SELECT  xact,
 103                 LAST(payment_type) AS last_payment_type,
 104                 LAST(note) AS last_payment_note,
 105                 MAX(payment_ts) as last_payment_ts,
 106                 SUM(COALESCE(amount,0)) AS total_paid
 107           FROM  money.payment_view
 108           WHERE voided IS FALSE
 109           GROUP BY xact
 110           ORDER BY MAX(payment_ts);
 111
 112 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 113         SELECT  xact.id AS id,
 114                 xact.usr AS usr,
 115                 xact.xact_start AS xact_start,
 116                 xact.xact_finish AS xact_finish,
 117                 credit.total_paid,
 118                 credit.last_payment_ts,
 119                 credit.last_payment_note,
 120                 credit.last_payment_type,
 121                 debit.total_owed,
 122                 debit.last_billing_ts,
 123                 debit.last_billing_note,
 124                 debit.last_billing_type,
 125                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 126                 p.relname AS xact_type
 127           FROM  money.billable_xact xact
 128                 JOIN pg_class p ON (xact.tableoid = p.oid)
 129                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 130                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
 131
 132 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
 133         SELECT  xact.id AS id,
 134                 xact.usr AS usr,
 135                 xact.xact_start AS xact_start,
 136                 xact.xact_finish AS xact_finish,
 137                 credit.total_paid,
 138                 credit.last_payment_ts,
 139                 credit.last_payment_note,
 140                 credit.last_payment_type,
 141                 debit.total_owed,
 142                 debit.last_billing_ts,
 143                 debit.last_billing_note,
 144                 debit.last_billing_type,
 145                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 146                 p.relname AS xact_type
 147           FROM  money.billable_xact xact
 148                 JOIN pg_class p ON (xact.tableoid = p.oid)
 149                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 150                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact)
 151           WHERE xact.xact_finish IS NULL;
 152
 153 CREATE OR REPLACE VIEW money.open_usr_summary AS
 154         SELECT  usr,
 155                 SUM(total_paid) AS total_paid,
 156                 SUM(total_owed) AS total_owed,
 157                 SUM(balance_owed) AS balance_owed
 158           FROM money.open_billable_xact_summary
 159           GROUP BY 1;
 160
 161 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
 162         SELECT  usr,
 163                 SUM(total_paid) AS total_paid,
 164                 SUM(total_owed) AS total_owed,
 165                 SUM(balance_owed) AS balance_owed
 166           FROM  money.open_billable_xact_summary
 167           WHERE xact_type = 'circulation'
 168           GROUP BY 1;
 169
 170 CREATE OR REPLACE VIEW money.usr_summary AS
 171         SELECT  usr,
 172                 SUM(total_paid) AS total_paid,
 173                 SUM(total_owed) AS total_owed,
 174                 SUM(balance_owed) AS balance_owed
 175           FROM money.billable_xact_summary
 176           GROUP BY 1;
 177
 178 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
 179         SELECT  usr,
 180                 SUM(total_paid) AS total_paid,
 181                 SUM(total_owed) AS total_owed,
 182                 SUM(balance_owed) AS balance_owed
 183           FROM  money.billable_xact_summary
 184           WHERE xact_type = 'circulation'
 185           GROUP BY 1;
 186
 187 CREATE TABLE money.bnm_payment (
 188         amount_collected        NUMERIC(6,2)    NOT NULL,
 189         accepting_usr           INT             NOT NULL
 190 ) INHERITS (money.payment);
 191
 192 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
 193 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
 194 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
 195
 196 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
 197 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
 198 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
 199
 200 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
 201 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
 202 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
 203
 204 CREATE TABLE money.bnm_desk_payment (
 205         cash_drawer     TEXT    NOT NULL
 206 ) INHERITS (money.bnm_payment);
 207
 208 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
 209 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
 210 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
 211
 212 CREATE TABLE money.check_payment (
 213         check_number    TEXT    NOT NULL
 214 ) INHERITS (money.bnm_desk_payment);
 215 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
 216 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
 217
 218 CREATE TABLE money.credit_card_payment (
 219         cc_type         TEXT    NOT NULL,
 220         cc_number       TEXT    NOT NULL,
 221         expire_month    INT     NOT NULL,
 222         expire_year     INT     NOT NULL,
 223         approval_code   TEXT    NOT NULL
 224 ) INHERITS (money.bnm_desk_payment);
 225 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
 226 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
 227
 228
 229 COMMIT;
 230