From 0a7220e09a8455a78de87df14c71c3e5bc2f166a Mon Sep 17 00:00:00 2001 From: scottmk Date: Thu, 25 Feb 2010 20:37:21 +0000 Subject: [PATCH] Added five new views to the acq schema: all_fund_allocation_total all_fund_encumbrance_total all_fund_spent_total all_fund_spent_balance all_fund_combined_balance These views are similar to the correspondingly named views without the "all_" prefix (i.e. acq.fund_allocation_total, etc) except that the return a row for every fund in acq.fund, even if the fund is not represented in acq.fund_debit or acq.fund_allocation. M Open-ILS/src/sql/Pg/200.schema.acq.sql M Open-ILS/src/sql/Pg/002.schema.config.sql A Open-ILS/src/sql/Pg/upgrade/0173.schema.acq.all-fund-views.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@15642 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 50 ++++++++++ Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/200.schema.acq.sql | 89 ++++++++++++++++++ .../0173.schema.acq.all-fund-views.sql | 94 +++++++++++++++++++ 4 files changed, 234 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0173.schema.acq.all-fund-views.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index eafb921873..8a378e56cc 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4931,6 +4931,56 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index da35c0fd5e..f4fee655ff 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 ('0172'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0173'); -- Scott McKellar CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index e06cdf1620..630d128102 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -2339,6 +2339,95 @@ CREATE OR REPLACE VIEW acq.fund_spent_balance AS FROM acq.fund_allocation_total c LEFT JOIN acq.fund_spent_total d USING (fund); +-- For each fund: the total allocation from all sources, in the +-- currency of the fund (or 0 if there are no allocations) + +CREATE VIEW acq.all_fund_allocation_total AS +SELECT + f.id AS fund, + COALESCE( SUM( a.amount * acq.exchange_ratio( + s.currency_type, f.currency_type))::numeric(100,2), 0 ) + AS amount +FROM + acq.fund f + LEFT JOIN acq.fund_allocation a + ON a.fund = f.id + LEFT JOIN acq.funding_source s + ON a.funding_source = s.id +GROUP BY + f.id; + +-- For every fund: the total encumbrances (or 0 if none), +-- in the currency of the fund. + +CREATE VIEW acq.all_fund_encumbrance_total AS +SELECT + f.id AS fund, + COALESCE( encumb.amount, 0 ) AS amount +FROM + acq.fund AS f + LEFT JOIN ( + SELECT + fund, + sum( amount ) AS amount + FROM + acq.fund_debit + WHERE + encumbrance + GROUP BY fund + ) AS encumb + ON f.id = encumb.fund; + +-- For every fund: the total spent (or 0 if none), +-- in the currency of the fund. + +CREATE VIEW acq.all_fund_spent_total AS +SELECT + f.id AS fund, + COALESCE( spent.amount, 0 ) AS amount +FROM + acq.fund AS f + LEFT JOIN ( + SELECT + fund, + sum( amount ) AS amount + FROM + acq.fund_debit + WHERE + NOT encumbrance + GROUP BY fund + ) AS spent + ON f.id = spent.fund; + +-- For each fund: the amount not yet spent, in the currency +-- of the fund. May include encumbrances. + +CREATE VIEW acq.all_fund_spent_balance AS +SELECT + c.fund, + c.amount - d.amount AS amount +FROM acq.all_fund_allocation_total c + LEFT JOIN acq.all_fund_spent_total d USING (fund); + +-- For each fund: the amount neither spent nor encumbered, +-- in the currency of the fund + +CREATE VIEW acq.all_fund_combined_balance AS +SELECT + a.fund, + a.amount - COALESCE( c.amount, 0 ) AS amount +FROM + acq.all_fund_allocation_total a + LEFT OUTER JOIN ( + SELECT + fund, + SUM( amount ) AS amount + FROM + acq.fund_debit + GROUP BY + fund + ) AS c USING ( fund ); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0173.schema.acq.all-fund-views.sql b/Open-ILS/src/sql/Pg/upgrade/0173.schema.acq.all-fund-views.sql new file mode 100644 index 0000000000..d14a204429 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0173.schema.acq.all-fund-views.sql @@ -0,0 +1,94 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0173'); -- Scott McKellar + +-- For each fund: the total allocation from all sources, in the +-- currency of the fund (or 0 if there are no allocations) + +CREATE VIEW acq.all_fund_allocation_total AS +SELECT + f.id AS fund, + COALESCE( SUM( a.amount * acq.exchange_ratio( + s.currency_type, f.currency_type))::numeric(100,2), 0 ) + AS amount +FROM + acq.fund f + LEFT JOIN acq.fund_allocation a + ON a.fund = f.id + LEFT JOIN acq.funding_source s + ON a.funding_source = s.id +GROUP BY + f.id; + +-- For every fund: the total encumbrances (or 0 if none), +-- in the currency of the fund. + +CREATE VIEW acq.all_fund_encumbrance_total AS +SELECT + f.id AS fund, + COALESCE( encumb.amount, 0 ) AS amount +FROM + acq.fund AS f + LEFT JOIN ( + SELECT + fund, + sum( amount ) AS amount + FROM + acq.fund_debit + WHERE + encumbrance + GROUP BY fund + ) AS encumb + ON f.id = encumb.fund; + +-- For every fund: the total spent (or 0 if none), +-- in the currency of the fund. + +CREATE VIEW acq.all_fund_spent_total AS +SELECT + f.id AS fund, + COALESCE( spent.amount, 0 ) AS amount +FROM + acq.fund AS f + LEFT JOIN ( + SELECT + fund, + sum( amount ) AS amount + FROM + acq.fund_debit + WHERE + NOT encumbrance + GROUP BY fund + ) AS spent + ON f.id = spent.fund; + +-- For each fund: the amount not yet spent, in the currency +-- of the fund. May include encumbrances. + +CREATE VIEW acq.all_fund_spent_balance AS +SELECT + c.fund, + c.amount - d.amount AS amount +FROM acq.all_fund_allocation_total c + LEFT JOIN acq.all_fund_spent_total d USING (fund); + +-- For each fund: the amount neither spent nor encumbered, +-- in the currency of the fund + +CREATE VIEW acq.all_fund_combined_balance AS +SELECT + a.fund, + a.amount - COALESCE( c.amount, 0 ) AS amount +FROM + acq.all_fund_allocation_total a + LEFT OUTER JOIN ( + SELECT + fund, + SUM( amount ) AS amount + FROM + acq.fund_debit + GROUP BY + fund + ) AS c USING ( fund ); + +COMMIT; -- 2.43.2