From 6b3661b8334606a4f690c71f97e28fe8d46e20f7 Mon Sep 17 00:00:00 2001
From: erickson <erickson@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Wed, 30 Dec 2009 14:40:36 +0000
Subject: [PATCH] updated some existing billing views to use the new (faster)
money.materialized_billable_xact_summary
git-svn-id: svn://svn.open-ils.org/ILS/trunk@15244 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +-
Open-ILS/src/sql/Pg/080.schema.money.sql | 19 +++--
Open-ILS/src/sql/Pg/500.view.cross-schema.sql | 80 ++++++----------------
.../Pg/upgrade/0128.schema.billing-matt-views.sql | 44 ++++++++++++
4 files changed, 79 insertions(+), 66 deletions(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/0128.schema.billing-matt-views.sql
diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 0ba3dc92c0..bd85991811 100644
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log (
install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
-INSERT INTO config.upgrade_log (version) VALUES ('0127'); -- miker
+INSERT INTO config.upgrade_log (version) VALUES ('0128'); -- berick
CREATE TABLE config.bib_source (
id SERIAL PRIMARY KEY,
diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql
index 007990bc53..46cfa0e5ae 100644
--- a/Open-ILS/src/sql/Pg/080.schema.money.sql
+++ b/Open-ILS/src/sql/Pg/080.schema.money.sql
@@ -254,6 +254,11 @@ CREATE OR REPLACE VIEW money.billable_xact_summary AS
CREATE TABLE money.materialized_billable_xact_summary AS
SELECT * FROM money.billable_xact_summary WHERE 1=0;
+-- TODO: Define money.materialized_billable_xact_summary w/ explicit columns and
+-- remove the definition above for money.billable_xact_summary.
+CREATE OR REPLACE VIEW money.billable_xact_summary AS
+ SELECT * FROM money.materialized_billable_xact_summary;
+
CREATE INDEX money_mat_summary_id_idx ON money.materialized_billable_xact_summary (id);
CREATE INDEX money_mat_summary_usr_idx ON money.materialized_billable_xact_summary (usr);
CREATE INDEX money_mat_summary_xact_start_idx ON money.materialized_billable_xact_summary (xact_start);
@@ -489,12 +494,14 @@ END;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE VIEW money.usr_summary AS
- SELECT usr,
- SUM(total_paid) AS total_paid,
- SUM(total_owed) AS total_owed,
- SUM(balance_owed) AS balance_owed
- FROM money.billable_xact_summary
- GROUP BY 1;
+ SELECT
+ usr,
+ sum(total_paid) AS total_paid,
+ sum(total_owed) AS total_owed,
+ sum(balance_owed) AS balance_owed
+ FROM money.materialized_billable_xact_summary
+ GROUP BY usr;
+
CREATE OR REPLACE VIEW money.usr_circulation_summary AS
SELECT usr,
diff --git a/Open-ILS/src/sql/Pg/500.view.cross-schema.sql b/Open-ILS/src/sql/Pg/500.view.cross-schema.sql
index b6df81eb23..f49defc818 100644
--- a/Open-ILS/src/sql/Pg/500.view.cross-schema.sql
+++ b/Open-ILS/src/sql/Pg/500.view.cross-schema.sql
@@ -17,52 +17,6 @@
BEGIN;
-CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
- SELECT xact.id AS id,
- xact.usr AS usr,
- COALESCE(circ.circ_lib,groc.billing_location,res.pickup_lib) AS billing_location,
- xact.xact_start AS xact_start,
- xact.xact_finish AS xact_finish,
- SUM(credit.amount) AS total_paid,
- MAX(credit.payment_ts) AS last_payment_ts,
- LAST(credit.note) AS last_payment_note,
- LAST(credit.payment_type) AS last_payment_type,
- SUM(debit.amount) AS total_owed,
- MAX(debit.billing_ts) AS last_billing_ts,
- LAST(debit.note) AS last_billing_note,
- LAST(debit.billing_type) AS last_billing_type,
- COALESCE(SUM(debit.amount),0) - COALESCE(SUM(credit.amount),0) AS balance_owed,
- p.relname AS xact_type
- FROM money.billable_xact xact
- JOIN pg_class p ON (xact.tableoid = p.oid)
- LEFT JOIN "action".circulation circ ON (circ.id = xact.id)
- LEFT JOIN money.grocery groc ON (groc.id = xact.id)
- LEFT JOIN booking.reservation res ON (groc.id = xact.id)
- LEFT JOIN (
- SELECT billing.xact,
- billing.voided,
- sum(billing.amount) AS amount,
- max(billing.billing_ts) AS billing_ts,
- last(billing.note) AS note,
- last(billing.billing_type) AS billing_type
- FROM money.billing
- WHERE billing.voided IS FALSE
- GROUP BY billing.xact, billing.voided
- ) debit ON (xact.id = debit.xact AND debit.voided IS FALSE)
- LEFT JOIN (
- SELECT payment_view.xact,
- payment_view.voided,
- sum(payment_view.amount) AS amount,
- max(payment_view.payment_ts) AS payment_ts,
- last(payment_view.note) AS note,
- last(payment_view.payment_type) AS payment_type
- FROM money.payment_view
- WHERE payment_view.voided IS FALSE
- GROUP BY payment_view.xact, payment_view.voided
- ) credit ON (xact.id = credit.xact AND credit.voided IS FALSE)
- WHERE xact.xact_finish IS NULL
- GROUP BY 1,2,3,4,5,15
- ORDER BY MAX(debit.billing_ts), MAX(credit.payment_ts);
CREATE OR REPLACE VIEW money.billable_xact_summary_location_view AS
SELECT m.*, COALESCE(c.circ_lib, g.billing_location, r.pickup_lib) AS billing_location
@@ -71,22 +25,30 @@ CREATE OR REPLACE VIEW money.billable_xact_summary_location_view AS
LEFT JOIN money.grocery g ON (g.id = m.id)
LEFT JOIN booking.reservation r ON (r.id = m.id);
+CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
+ SELECT * FROM money.billable_xact_summary_location_view
+ WHERE xact_finish IS NULL;
+
CREATE OR REPLACE VIEW money.open_usr_summary AS
- SELECT usr,
- SUM(total_paid) AS total_paid,
- SUM(total_owed) AS total_owed,
- SUM(balance_owed) AS balance_owed
- FROM money.open_billable_xact_summary
- GROUP BY 1;
+ SELECT
+ usr,
+ sum(total_paid) AS total_paid,
+ sum(total_owed) AS total_owed,
+ sum(balance_owed) AS balance_owed
+ FROM money.materialized_billable_xact_summary
+ WHERE xact_finish IS NULL
+ GROUP BY usr;
CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
- SELECT usr,
- SUM(total_paid) AS total_paid,
- SUM(total_owed) AS total_owed,
- SUM(balance_owed) AS balance_owed
- FROM money.open_billable_xact_summary
- WHERE xact_type = 'circulation'
- GROUP BY 1;
+ SELECT
+ usr,
+ SUM(total_paid) AS total_paid,
+ SUM(total_owed) AS total_owed,
+ SUM(balance_owed) AS balance_owed
+ FROM money.materialized_billable_xact_summary
+ WHERE xact_type = 'circulation' AND xact_finish IS NULL
+ GROUP BY usr;
+
-- Not a view, but it's cross-schema..
CREATE TABLE config.idl_field_doc (
diff --git a/Open-ILS/src/sql/Pg/upgrade/0128.schema.billing-matt-views.sql b/Open-ILS/src/sql/Pg/upgrade/0128.schema.billing-matt-views.sql
new file mode 100644
index 0000000000..5c07a2d860
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0128.schema.billing-matt-views.sql
@@ -0,0 +1,44 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0128');
+
+DROP VIEW money.open_usr_circulation_summary;
+DROP VIEW money.open_billable_xact_summary;
+
+CREATE OR REPLACE VIEW money.billable_xact_summary AS
+ SELECT * FROM money.materialized_billable_xact_summary;
+
+CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
+ SELECT * FROM money.billable_xact_summary_location_view
+ WHERE xact_finish IS NULL;
+
+CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
+ SELECT
+ usr,
+ SUM(total_paid) AS total_paid,
+ SUM(total_owed) AS total_owed,
+ SUM(balance_owed) AS balance_owed
+ FROM money.materialized_billable_xact_summary
+ WHERE xact_type = 'circulation' AND xact_finish IS NULL
+ GROUP BY usr;
+
+CREATE OR REPLACE VIEW money.usr_summary AS
+ SELECT
+ usr,
+ sum(total_paid) AS total_paid,
+ sum(total_owed) AS total_owed,
+ sum(balance_owed) AS balance_owed
+ FROM money.materialized_billable_xact_summary
+ GROUP BY usr;
+
+CREATE OR REPLACE VIEW money.open_usr_summary AS
+ SELECT
+ usr,
+ sum(total_paid) AS total_paid,
+ sum(total_owed) AS total_owed,
+ sum(balance_owed) AS balance_owed
+ FROM money.materialized_billable_xact_summary
+ WHERE xact_finish IS NULL
+ GROUP BY usr;
+
+COMMIT;
--
2.11.0