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
  29 CREATE TABLE money.billing (
  30         id              BIGSERIAL                       PRIMARY KEY,
  31         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  32         billing_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  33         voided          BOOL                            NOT NULL DEFAULT FALSE,
  34         voider          INT,
  35         void_time       TIMESTAMP WITH TIME ZONE,
  36         amount          NUMERIC(6,2)                    NOT NULL,
  37         billing_type    TEXT                            NOT NULL,
  38         note            TEXT
  39 );
  40 CREATE INDEX m_b_xact_idx ON money.billing (xact);
  41 CREATE INDEX m_b_time_idx ON money.billing (billing_ts);
  42
  43 CREATE TABLE money.payment (
  44         id              BIGSERIAL                       PRIMARY KEY,
  45         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  46         payment_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  47         voided          BOOL                            NOT NULL DEFAULT FALSE,
  48         amount          NUMERIC(6,2)                    NOT NULL,
  49         note            TEXT
  50 );
  51 CREATE INDEX m_p_xact_idx ON money.payment (xact);
  52 CREATE INDEX m_p_time_idx ON money.payment (payment_ts);
  53
  54 CREATE OR REPLACE VIEW money.payment_view AS
  55         SELECT  p.*,c.relname AS payment_type
  56           FROM  money.payment p
  57                 JOIN pg_class c ON (p.tableoid = c.oid);
  58
  59 CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS
  60         SELECT  xact,
  61                 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,billing_type
  68           ORDER BY MAX(billing_ts);
  69
  70 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
  71         SELECT  xact,
  72                 LAST(billing_type) AS last_billing_type,
  73                 LAST(note) AS last_billing_note,
  74                 MAX(billing_ts) AS last_billing_ts,
  75                 SUM(COALESCE(amount,0)) AS total_owed
  76           FROM  money.billing
  77           WHERE voided IS FALSE
  78           GROUP BY xact
  79           ORDER BY MAX(billing_ts);
  80
  81 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
  82         SELECT  xact,
  83                 LAST(payment_type) AS last_payment_type,
  84                 LAST(note) AS last_payment_note,
  85                 MAX(payment_ts) as last_payment_ts,
  86                 SUM(COALESCE(amount,0)) AS total_paid
  87           FROM  money.payment_view
  88           WHERE voided IS FALSE
  89           GROUP BY xact
  90           ORDER BY MAX(payment_ts);
  91
  92 CREATE OR REPLACE VIEW money.transaction_billing_with_void_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(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_owed
  98           FROM  money.billing
  99           GROUP BY xact
 100           ORDER BY MAX(billing_ts);
 101
 102 CREATE OR REPLACE VIEW money.transaction_payment_with_void_summary AS
 103         SELECT  xact,
 104                 LAST(payment_type) AS last_payment_type,
 105                 LAST(note) AS last_payment_note,
 106                 MAX(payment_ts) as last_payment_ts,
 107                 SUM(CASE WHEN voided THEN 0 ELSE COALESCE(amount,0) END) AS total_paid
 108           FROM  money.payment_view
 109           GROUP BY xact
 110           ORDER BY MAX(payment_ts);
 111
 112 CREATE OR REPLACE VIEW money.open_transaction_billing_type_summary AS
 113         SELECT  xact,
 114                 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,billing_type
 121           ORDER BY MAX(billing_ts);
 122
 123 CREATE OR REPLACE VIEW money.open_transaction_billing_summary AS
 124         SELECT  xact,
 125                 LAST(billing_type) AS last_billing_type,
 126                 LAST(note) AS last_billing_note,
 127                 MAX(billing_ts) AS last_billing_ts,
 128                 SUM(COALESCE(amount,0)) AS total_owed
 129           FROM  money.billing
 130           WHERE voided IS FALSE
 131           GROUP BY xact
 132           ORDER BY MAX(billing_ts);
 133
 134 CREATE OR REPLACE VIEW money.open_transaction_payment_summary AS
 135         SELECT  xact,
 136                 LAST(payment_type) AS last_payment_type,
 137                 LAST(note) AS last_payment_note,
 138                 MAX(payment_ts) as last_payment_ts,
 139                 SUM(COALESCE(amount,0)) AS total_paid
 140           FROM  money.payment_view
 141           WHERE voided IS FALSE
 142           GROUP BY xact
 143           ORDER BY MAX(payment_ts);
 144
 145 CREATE OR REPLACE VIEW money.billable_xact_with_void_summary AS
 146         SELECT  xact.id AS id,
 147                 xact.usr AS usr,
 148                 xact.xact_start AS xact_start,
 149                 xact.xact_finish AS xact_finish,
 150                 credit.total_paid,
 151                 credit.last_payment_ts,
 152                 credit.last_payment_note,
 153                 credit.last_payment_type,
 154                 debit.total_owed,
 155                 debit.last_billing_ts,
 156                 debit.last_billing_note,
 157                 debit.last_billing_type,
 158                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 159                 p.relname AS xact_type
 160           FROM  money.billable_xact xact
 161                 JOIN pg_class p ON (xact.tableoid = p.oid)
 162                 LEFT JOIN money.transaction_billing_with_void_summary debit ON (xact.id = debit.xact)
 163                 LEFT JOIN money.transaction_payment_with_void_summary credit ON (xact.id = credit.xact);
 164
 165 CREATE OR REPLACE VIEW money.billable_xact_summary AS
 166         SELECT  xact.id AS id,
 167                 xact.usr AS usr,
 168                 xact.xact_start AS xact_start,
 169                 xact.xact_finish AS xact_finish,
 170                 credit.total_paid,
 171                 credit.last_payment_ts,
 172                 credit.last_payment_note,
 173                 credit.last_payment_type,
 174                 debit.total_owed,
 175                 debit.last_billing_ts,
 176                 debit.last_billing_note,
 177                 debit.last_billing_type,
 178                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
 179                 p.relname AS xact_type
 180           FROM  money.billable_xact xact
 181                 JOIN pg_class p ON (xact.tableoid = p.oid)
 182                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
 183                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact);
 184
 185 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
 186         SELECT  xact.id AS id,
 187                 xact.usr AS usr,
 188                 xact.xact_start AS xact_start,
 189                 xact.xact_finish AS xact_finish,
 190                 SUM(credit.amount) AS total_paid,
 191                 MAX(credit.payment_ts) AS last_payment_ts,
 192                 LAST(credit.note) AS last_payment_note,
 193                 LAST(credit.payment_type) AS last_payment_type,
 194                 SUM(debit.amount) AS total_owed,
 195                 MAX(debit.billing_ts) AS last_billing_ts,
 196                 LAST(debit.note) AS last_billing_note,
 197                 LAST(debit.billing_type) AS last_billing_type,
 198                 COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
 199                 p.relname AS xact_type
 200           FROM  money.billable_xact xact
 201                 JOIN pg_class p ON (xact.tableoid = p.oid)
 202                 LEFT JOIN money.billing debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
 203                 LEFT JOIN money.payment_view credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
 204           WHERE xact.xact_finish IS NULL
 205           GROUP BY 1,2,3,4,14
 206           ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
 207
 208
 209 CREATE OR REPLACE VIEW money.open_usr_summary AS
 210         SELECT  usr,
 211                 SUM(total_paid) AS total_paid,
 212                 SUM(total_owed) AS total_owed,
 213                 SUM(balance_owed) AS balance_owed
 214           FROM money.open_billable_xact_summary
 215           GROUP BY 1;
 216
 217 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
 218         SELECT  usr,
 219                 SUM(total_paid) AS total_paid,
 220                 SUM(total_owed) AS total_owed,
 221                 SUM(balance_owed) AS balance_owed
 222           FROM  money.open_billable_xact_summary
 223           WHERE xact_type = 'circulation'
 224           GROUP BY 1;
 225
 226 CREATE OR REPLACE VIEW money.usr_summary AS
 227         SELECT  usr,
 228                 SUM(total_paid) AS total_paid,
 229                 SUM(total_owed) AS total_owed,
 230                 SUM(balance_owed) AS balance_owed
 231           FROM money.billable_xact_summary
 232           GROUP BY 1;
 233
 234 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
 235         SELECT  usr,
 236                 SUM(total_paid) AS total_paid,
 237                 SUM(total_owed) AS total_owed,
 238                 SUM(balance_owed) AS balance_owed
 239           FROM  money.billable_xact_summary
 240           WHERE xact_type = 'circulation'
 241           GROUP BY 1;
 242
 243 CREATE TABLE money.bnm_payment (
 244         amount_collected        NUMERIC(6,2)    NOT NULL,
 245         accepting_usr           INT             NOT NULL
 246 ) INHERITS (money.payment);
 247
 248 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
 249 CREATE INDEX money_forgive_id_idx ON money.forgive_payment (id);
 250 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
 251 CREATE INDEX money_forgive_payment_payment_ts_idx ON money.forgive_payment (payment_ts);
 252 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
 253
 254 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
 255 CREATE INDEX money_work_id_idx ON money.work_payment (id);
 256 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
 257 CREATE INDEX money_work_payment_payment_ts_idx ON money.work_payment (payment_ts);
 258 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
 259
 260 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
 261 CREATE INDEX money_credit_id_idx ON money.credit_payment (id);
 262 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
 263 CREATE INDEX money_credit_payment_payment_ts_idx ON money.credit_payment (payment_ts);
 264 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
 265
 266 CREATE TABLE money.bnm_desk_payment (
 267         cash_drawer     INT     REFERENCES actor.workstation (id)
 268 ) INHERITS (money.bnm_payment);
 269
 270 CREATE OR REPLACE VIEW money.desk_payment_view AS
 271         SELECT  p.*,c.relname AS payment_type
 272           FROM  money.bnm_desk_payment p
 273                 JOIN pg_class c ON (p.tableoid = c.oid);
 274
 275 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
 276 CREATE INDEX money_cash_id_idx ON money.cash_payment (id);
 277 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
 278 CREATE INDEX money_cash_payment_ts_idx ON money.cash_payment (payment_ts);
 279 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
 280 CREATE INDEX money_cash_payment_cash_drawer_idx ON money.cash_payment (cash_drawer);
 281
 282 CREATE TABLE money.check_payment (
 283         check_number    TEXT    NOT NULL
 284 ) INHERITS (money.bnm_desk_payment);
 285 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
 286 CREATE INDEX money_check_id_idx ON money.check_payment (id);
 287 CREATE INDEX money_check_payment_ts_idx ON money.check_payment (payment_ts);
 288 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
 289 CREATE INDEX money_check_payment_cash_drawer_idx ON money.check_payment (cash_drawer);
 290
 291 CREATE TABLE money.credit_card_payment (
 292         cc_type         TEXT    NOT NULL,
 293         cc_number       TEXT    NOT NULL,
 294         expire_month    INT     NOT NULL,
 295         expire_year     INT     NOT NULL,
 296         approval_code   TEXT    NOT NULL
 297 ) INHERITS (money.bnm_desk_payment);
 298 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
 299 CREATE INDEX money_credit_card_id_idx ON money.credit_card_payment (id);
 300 CREATE INDEX money_credit_card_payment_ts_idx ON money.credit_card_payment (payment_ts);
 301 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
 302 CREATE INDEX money_credit_card_payment_cash_drawer_idx ON money.credit_card_payment (cash_drawer);
 303
 304 CREATE OR REPLACE VIEW money.cashdrawer_payment_view AS
 305         SELECT  ou.id AS org_unit,
 306                 ws.id AS cashdrawer,
 307                 t.payment_type AS payment_type,
 308                 p.payment_ts AS payment_ts,
 309                 p.amount AS amount,
 310                 p.voided AS voided,
 311                 p.note AS note
 312           FROM  actor.org_unit ou
 313                 JOIN actor.workstation ws ON (ou.id = ws.owning_lib)
 314                 LEFT JOIN money.bnm_desk_payment p ON (ws.id = p.cash_drawer)
 315                 LEFT JOIN money.payment_view t ON (p.id = t.id);
 316
 317
 318 COMMIT;
 319