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