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.billing (
  16         id              BIGSERIAL                       PRIMARY KEY,
  17         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  18         billing_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  19         voided          BOOL                            NOT NULL DEFAULT FALSE,
  20         amount          NUMERIC(6,2)                    NOT NULL,
  21         billing_type    TEXT                            NOT NULL,
  22         note            TEXT
  23 );
  24 CREATE INDEX m_b_xact_idx ON money.billing (xact);
  25
  26 CREATE TABLE money.payment (
  27         id              BIGSERIAL                       PRIMARY KEY,
  28         xact            BIGINT                          NOT NULL, -- money.billable_xact.id
  29         payment_ts      TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT NOW(),
  30         voided          BOOL                            NOT NULL DEFAULT FALSE,
  31         amount          NUMERIC(6,2)                    NOT NULL,
  32         note            TEXT
  33 );
  34 CREATE INDEX m_p_xact_idx ON money.payment (xact);
  35
  36 CREATE OR REPLACE VIEW money.payment_view AS
  37         SELECT  p.*,c.relname AS payment_type
  38           FROM  money.payment p
  39                 JOIN pg_class c ON (p.tableoid = c.oid);
  40
  41 CREATE OR REPLACE VIEW money.transaction_billing_summary AS
  42         SELECT  xact,
  43                 billing_type AS last_billing_type,
  44                 LAST(note) AS last_billing_note,
  45                 MAX(billing_ts) AS last_billing_ts,
  46                 SUM(COALESCE(amount,0)) AS total_owed
  47           FROM  money.billing
  48           WHERE voided IS FALSE
  49           GROUP BY xact,billing_type
  50           ORDER BY MAX(billing_ts);
  51
  52 CREATE OR REPLACE VIEW money.transaction_payment_summary AS
  53         SELECT  xact,
  54                 payment_type AS last_payment_type,
  55                 LAST(note) AS last_payment_note,
  56                 MAX(payment_ts) as last_payment_ts,
  57                 SUM(COALESCE(amount,0)) AS total_paid
  58           FROM  money.payment_view
  59           WHERE voided IS FALSE
  60           GROUP BY xact,payment_type
  61           ORDER BY MAX(payment_ts);
  62
  63 CREATE OR REPLACE VIEW money.billable_xact_summary AS
  64         SELECT  xact.id AS id,
  65                 xact.usr AS usr,
  66                 xact.xact_start AS xact_start,
  67                 xact.xact_finish AS xact_finish,
  68                 credit.total_paid,
  69                 credit.last_payment_ts,
  70                 credit.last_payment_note,
  71                 credit.last_payment_type,
  72                 debit.total_owed,
  73                 debit.last_billing_ts,
  74                 debit.last_billing_note,
  75                 debit.last_billing_type,
  76                 COALESCE(debit.total_owed,0) - COALESCE(credit.total_paid,0) AS balance_owed,
  77                 p.relname AS xact_type
  78           FROM  money.billable_xact xact
  79                 JOIN pg_class p ON (xact.tableoid = p.oid)
  80                 LEFT JOIN money.transaction_billing_summary debit ON (xact.id = debit.xact)
  81                 LEFT JOIN money.transaction_payment_summary credit ON (xact.id = credit.xact)
  82           WHERE xact.xact_finish IS NULL;
  83
  84 CREATE OR REPLACE VIEW money.usr_summary AS
  85         SELECT  usr,
  86                 SUM(total_paid) AS total_paid,
  87                 SUM(total_owed) AS total_owed,
  88                 SUM(balance_owed) AS balance_owed
  89           FROM money.billable_xact_summary
  90           GROUP BY 1;
  91
  92 CREATE OR REPLACE VIEW money.usr_circulation_summary AS
  93         SELECT  usr,
  94                 SUM(total_paid) AS total_paid,
  95                 SUM(total_owed) AS total_owed,
  96                 SUM(balance_owed) AS balance_owed
  97           FROM  money.billable_xact_summary
  98           WHERE xact_type = 'circulation'
  99           GROUP BY 1;
 100
 101 CREATE TABLE money.bnm_payment (
 102         amount_collected        NUMERIC(6,2)    NOT NULL,
 103         accepting_usr           INT             NOT NULL
 104 ) INHERITS (money.payment);
 105
 106 CREATE TABLE money.forgive_payment () INHERITS (money.bnm_payment);
 107 CREATE INDEX money_forgive_payment_xact_idx ON money.forgive_payment (xact);
 108 CREATE INDEX money_forgive_payment_accepting_usr_idx ON money.forgive_payment (accepting_usr);
 109
 110 CREATE TABLE money.work_payment () INHERITS (money.bnm_payment);
 111 CREATE INDEX money_work_payment_xact_idx ON money.work_payment (xact);
 112 CREATE INDEX money_work_payment_accepting_usr_idx ON money.work_payment (accepting_usr);
 113
 114 CREATE TABLE money.credit_payment () INHERITS (money.bnm_payment);
 115 CREATE INDEX money_credit_payment_xact_idx ON money.credit_payment (xact);
 116 CREATE INDEX money_credit_payment_accepting_usr_idx ON money.credit_payment (accepting_usr);
 117
 118 CREATE TABLE money.bnm_desk_payment (
 119         cash_drawer     TEXT    NOT NULL
 120 ) INHERITS (money.bnm_payment);
 121
 122 CREATE TABLE money.cash_payment () INHERITS (money.bnm_desk_payment);
 123 CREATE INDEX money_cash_payment_xact_idx ON money.cash_payment (xact);
 124 CREATE INDEX money_cash_payment_accepting_usr_idx ON money.cash_payment (accepting_usr);
 125
 126 CREATE TABLE money.check_payment (
 127         check_number    TEXT    NOT NULL
 128 ) INHERITS (money.bnm_desk_payment);
 129 CREATE INDEX money_check_payment_xact_idx ON money.check_payment (xact);
 130 CREATE INDEX money_check_payment_accepting_usr_idx ON money.check_payment (accepting_usr);
 131
 132 CREATE TABLE money.credit_card_payment (
 133         cc_type         TEXT    NOT NULL,
 134         cc_number       TEXT    NOT NULL,
 135         expire_month    INT     NOT NULL,
 136         expire_year     INT     NOT NULL,
 137         approval_code   TEXT    NOT NULL
 138 ) INHERITS (money.bnm_desk_payment);
 139 CREATE INDEX money_credit_card_payment_xact_idx ON money.credit_card_payment (xact);
 140 CREATE INDEX money_credit_card_payment_accepting_usr_idx ON money.credit_card_payment (accepting_usr);
 141
 142
 143 COMMIT;
 144