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.open_transaction_billing_type_summary AS
  82         SELECT  xact,
  83                 billing_type AS last_billing_type,
  84                 LAST(note) AS last_billing_note,
  85                 MAX(billing_ts) AS last_billing_ts,
  86                 SUM(COALESCE(amount,0)) AS total_owed
  87           FROM  money.billing
  88           WHERE voided IS FALSE
  89           GROUP BY xact,billing_type
  90           ORDER BY MAX(billing_ts);
  91
  92 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
  93         SELECT  xact,
  94                 LAST(billing_type) AS last_billing_type,
  95                 LAST(note) AS last_billing_note,
  96                 MAX(billing_ts) AS last_billing_ts,
  97                 SUM(COALESCE(amount,0)) AS total_owed
  98           FROM  money.billing
  99           WHERE voided IS FALSE
 100           GROUP BY xact
 101           ORDER BY MAX(billing_ts);
 102
 103 CREATE OR REPLACE VIEW money.open_transaction_payment_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(COALESCE(amount,0)) AS total_paid
 109           FROM  money.payment_view
 110           WHERE voided IS FALSE
 111           GROUP BY xact
 112           ORDER BY MAX(payment_ts);
 113
 114 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 115         SELECT  xact.id AS id,
 116                 xact.usr AS usr,
 117                 xact.xact_start AS xact_start,
 118                 xact.xact_finish AS xact_finish,
 119                 credit.total_paid,
 120                 credit.last_payment_ts,
 121                 credit.last_payment_note,
 122                 credit.last_payment_type,
 123                 debit.total_owed,
 124                 debit.last_billing_ts,
 125                 debit.last_billing_note,
 126                 debit.last_billing_type,
 127                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 128                 p.relname AS xact_type
 129           FROM  money.billable_xact xact
 130                 JOIN pg_class p ON (xact.tableoid = p.oid)
 131                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 132                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
 133
 134 CREATE OR REPLACE VIEW money.open_billable_xact_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_summary debit ON (xact.id = debit.xact)
 152                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact)
 153           WHERE xact.xact_finish IS NULL;
 154
 155 CREATE OR REPLACE VIEW money.open_usr_summary AS
 156         SELECT  usr,
 157                 SUM(total_paid) AS total_paid,
 158                 SUM(total_owed) AS total_owed,
 159                 SUM(balance_owed) AS balance_owed
 160           FROM money.open_billable_xact_summary
 161           GROUP BY 1;
 162
 163 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
 164         SELECT  usr,
 165                 SUM(total_paid) AS total_paid,
 166                 SUM(total_owed) AS total_owed,
 167                 SUM(balance_owed) AS balance_owed
 168           FROM  money.open_billable_xact_summary
 169           WHERE xact_type = 'circulation'
 170           GROUP BY 1;
 171
 172 CREATE OR REPLACE VIEW money.usr_summary AS
 173         SELECT  usr,
 174                 SUM(total_paid) AS total_paid,
 175                 SUM(total_owed) AS total_owed,
 176                 SUM(balance_owed) AS balance_owed
 177           FROM money.billable_xact_summary
 178           GROUP BY 1;
 179
 180 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
 181         SELECT  usr,
 182                 SUM(total_paid) AS total_paid,
 183                 SUM(total_owed) AS total_owed,
 184                 SUM(balance_owed) AS balance_owed
 185           FROM  money.billable_xact_summary
 186           WHERE xact_type = 'circulation'
 187           GROUP BY 1;
 188
 189 CREATE TABLE money.bnm_payment (
 190         amount_collected        NUMERIC(6,2)    NOT NULL,
 191         accepting_usr           INT             NOT NULL
 192 ) INHERITS (money.payment);
 193
 194 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
 195 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
 196 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
 197
 198 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
 199 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
 200 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
 201
 202 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
 203 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
 204 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
 205
 206 CREATE TABLE money.bnm_desk_payment (
 207         cash_drawer     TEXT    NOT NULL
 208 ) INHERITS (money.bnm_payment);
 209
 210 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
 211 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
 212 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
 213
 214 CREATE TABLE money.check_payment (
 215         check_number    TEXT    NOT NULL
 216 ) INHERITS (money.bnm_desk_payment);
 217 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
 218 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
 219
 220 CREATE TABLE money.credit_card_payment (
 221         cc_type         TEXT    NOT NULL,
 222         cc_number       TEXT    NOT NULL,
 223         expire_month    INT     NOT NULL,
 224         expire_year     INT     NOT NULL,
 225         approval_code   TEXT    NOT NULL
 226 ) INHERITS (money.bnm_desk_payment);
 227 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
 228 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
 229
 230
 231 COMMIT;
 232