From 476f0a2275c37389e824fc4747def493935a4aa5 Mon Sep 17 00:00:00 2001
From: scottmk <scottmk@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Fri, 5 Feb 2010 04:25:05 +0000
Subject: [PATCH] 1. Add two new columns to actor.org_unit:
spend_warning_percent and spend_limit_percent. These define, by org unit,
spending levels that should trigger either a warning or a firm limit for a
fund.
2. Added corresponding columns to the auditor.actor_org_unit_history table.
3. In order to make the above work: raarranged way we create the
actor.org_unit table so that we define it with the fiscal_calendar
column and add the associated foreign key constraint later.
4. Add a fiscal_calendar column to the auditor.actor_org_unit_history
table (correcting an oversight on a previous commit).
5. Created two new functions to look up the default spending levels
for a given org unit. Where the spending level is undefined, the
functions look for a spending level defined for a parent org unit,
ultimately defaulting if necessary to a hard-coded value.
M Open-ILS/src/sql/Pg/005.schema.actors.sql
M Open-ILS/src/sql/Pg/999.functions.global.sql
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/0151.schema.acq.spending-limits.sql
M Open-ILS/examples/fm_IDL.xml
git-svn-id: svn://svn.open-ils.org/ILS/trunk@15451 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
Open-ILS/examples/fm_IDL.xml | 2 +
Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +-
Open-ILS/src/sql/Pg/005.schema.actors.sql | 7 +-
Open-ILS/src/sql/Pg/200.schema.acq.sql | 8 +-
Open-ILS/src/sql/Pg/999.functions.global.sql | 84 +++++++++++++++
.../Pg/upgrade/0151.schema.acq.spending-limits.sql | 113 +++++++++++++++++++++
6 files changed, 209 insertions(+), 7 deletions(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/0151.schema.acq.spending-limits.sql
diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 4252dd7fd1..f6fca23e8d 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -3154,6 +3154,8 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
<field reporter:label="Phone Number" name="phone" reporter:datatype="text"/>
<field reporter:label="OPAC Visible" name="opac_visible" reporter:datatype="bool"/>
<field reporter:label="Fiscal Calendar" name="fiscal_calendar" reporter:datatype="link"/>
+ <field reporter:label="Spend Warning Percent" name="spend_warning_percent" reporter:datatype="int"/>
+ <field reporter:label="Spend Limit Percent" name="spend_limit_percent" reporter:datatype="int"/>
<field reporter:label="Users" name="users" oils_persist:virtual="true" reporter:datatype="link"/>
<field reporter:label="Closed Dates" name="closed_dates" oils_persist:virtual="true" reporter:datatype="link"/>
<field reporter:label="Circulations" name="circulations" oils_persist:virtual="true" reporter:datatype="link"/>
diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 2a4647975c..fb1a3861f5 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 ('0150'); -- dbs
+INSERT INTO config.upgrade_log (version) VALUES ('0151'); -- Scott McKellar
CREATE TABLE config.bib_source (
id SERIAL PRIMARY KEY,
diff --git a/Open-ILS/src/sql/Pg/005.schema.actors.sql b/Open-ILS/src/sql/Pg/005.schema.actors.sql
index 81eb7a7b92..314e6a556e 100644
--- a/Open-ILS/src/sql/Pg/005.schema.actors.sql
+++ b/Open-ILS/src/sql/Pg/005.schema.actors.sql
@@ -339,7 +339,12 @@ CREATE TABLE actor.org_unit (
name TEXT NOT NULL UNIQUE,
email TEXT,
phone TEXT,
- opac_visible BOOL NOT NULL DEFAULT TRUE
+ opac_visible BOOL NOT NULL DEFAULT TRUE,
+ fiscal_calendar INT NOT NULL DEFAULT 1, -- foreign key constraint to be added later
+ spend_warning_percent INT CONSTRAINT spend_warning_percent_limit
+ CHECK( spend_warning_percent <= 100 ),
+ spend_limit_percent INT CONSTRAINT spend_limit_percent_limit
+ CHECK( spend_limit_percent <= 100 )
);
CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
CREATE INDEX actor_org_unit_ou_type_idx ON actor.org_unit (ou_type);
diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql
index 5501b9b476..8529540b64 100644
--- a/Open-ILS/src/sql/Pg/200.schema.acq.sql
+++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql
@@ -623,11 +623,9 @@ INSERT INTO acq.fiscal_calendar (
'Default'
);
-ALTER TABLE actor.org_unit
-ADD COLUMN fiscal_calendar INT NOT NULL
- REFERENCES acq.fiscal_calendar( id )
- DEFERRABLE INITIALLY DEFERRED
- DEFAULT 1;
+ALTER TABLE actor.org_unit ADD FOREIGN KEY
+ (fiscal_calendar) REFERENCES acq.fiscal_calendar( id )
+ DEFERRABLE INITIALLY DEFERRED;
CREATE TABLE acq.fiscal_year (
id SERIAL PRIMARY KEY,
diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql
index e13d0e5b74..7a9e731c92 100644
--- a/Open-ILS/src/sql/Pg/999.functions.global.sql
+++ b/Open-ILS/src/sql/Pg/999.functions.global.sql
@@ -1113,3 +1113,87 @@ CREATE TRIGGER fingerprint_tgr BEFORE INSERT OR UPDATE ON biblio.record_entry FO
CREATE TRIGGER aaa_indexing_ingest_or_delete AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.indexing_ingest_or_delete ();
CREATE TRIGGER bbb_simple_rec_trigger AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger ();
+CREATE OR REPLACE FUNCTION acq.default_spend_limit( org_unit_id IN INT )
+RETURNS INTEGER AS $$
+DECLARE
+ org INT;
+ key_id INT;
+ percent INT;
+ parent INT;
+BEGIN
+ org := org_unit_id;
+ WHILE percent IS NULL LOOP
+ SELECT
+ id,
+ spend_limit_percent,
+ parent_ou
+ INTO
+ key_id,
+ percent,
+ parent
+ FROM
+ actor.org_unit
+ WHERE
+ id = org;
+ --
+ IF key_id IS NULL THEN
+ RAISE EXCEPTION 'Org_unit id % is not valid', org_unit_id;
+ END IF;
+ --
+ IF parent IS NULL THEN
+ EXIT;
+ ELSE
+ org := parent;
+ END IF;
+ END LOOP;
+ --
+ IF percent IS NULL THEN
+ RETURN 0; -- Last-ditch default
+ ELSE
+ RETURN percent;
+ END IF;
+END;
+$$ LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION acq.default_warning_limit( org_unit_id IN INT )
+RETURNS INTEGER AS $$
+DECLARE
+ org INT;
+ key_id INT;
+ percent INT;
+ parent INT;
+BEGIN
+ org := org_unit_id;
+ WHILE percent IS NULL LOOP
+ SELECT
+ id,
+ spend_warning_percent,
+ parent_ou
+ INTO
+ key_id,
+ percent,
+ parent
+ FROM
+ actor.org_unit
+ WHERE
+ id = org;
+ --
+ IF key_id IS NULL THEN
+ RAISE EXCEPTION 'Org_unit id % is not valid', org_unit_id;
+ END IF;
+ --
+ IF parent IS NULL THEN
+ EXIT;
+ ELSE
+ org := parent;
+ END IF;
+ END LOOP;
+ --
+ IF percent IS NULL THEN
+ RETURN 10; -- Last-ditch default
+ ELSE
+ RETURN percent;
+ END IF;
+END;
+$$ LANGUAGE 'plpgsql';
+
diff --git a/Open-ILS/src/sql/Pg/upgrade/0151.schema.acq.spending-limits.sql b/Open-ILS/src/sql/Pg/upgrade/0151.schema.acq.spending-limits.sql
new file mode 100644
index 0000000000..b219c5b4e6
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0151.schema.acq.spending-limits.sql
@@ -0,0 +1,113 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0151'); -- Scott McKellar
+
+ALTER TABLE actor.org_unit
+ ADD COLUMN spend_warning_percent INT
+ CONSTRAINT spend_warning_percent_limit
+ CHECK( spend_warning_percent <= 100 );
+
+ALTER TABLE actor.org_unit
+ ADD COLUMN spend_limit_percent INT
+ CONSTRAINT spend_limit_percent_limit
+ CHECK( spend_limit_percent <= 100 );
+
+CREATE OR REPLACE FUNCTION acq.default_spend_limit( org_unit_id IN INT )
+RETURNS INTEGER AS $$
+DECLARE
+ org INT;
+ key_id INT;
+ percent INT;
+ parent INT;
+BEGIN
+ org := org_unit_id;
+ WHILE percent IS NULL LOOP
+ SELECT
+ id,
+ spend_limit_percent,
+ parent_ou
+ INTO
+ key_id,
+ percent,
+ parent
+ FROM
+ actor.org_unit
+ WHERE
+ id = org;
+ --
+ IF key_id IS NULL THEN
+ RAISE EXCEPTION 'Org_unit id % is not valid', org_unit_id;
+ END IF;
+ --
+ IF parent IS NULL THEN
+ EXIT;
+ ELSE
+ org := parent;
+ END IF;
+ END LOOP;
+ --
+ IF percent IS NULL THEN
+ RETURN 0; -- Last-ditch default
+ ELSE
+ RETURN percent;
+ END IF;
+END;
+$$ LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION acq.default_warning_limit( org_unit_id IN INT )
+RETURNS INTEGER AS $$
+DECLARE
+ org INT;
+ key_id INT;
+ percent INT;
+ parent INT;
+BEGIN
+ org := org_unit_id;
+ WHILE percent IS NULL LOOP
+ SELECT
+ id,
+ spend_warning_percent,
+ parent_ou
+ INTO
+ key_id,
+ percent,
+ parent
+ FROM
+ actor.org_unit
+ WHERE
+ id = org;
+ --
+ IF key_id IS NULL THEN
+ RAISE EXCEPTION 'Org_unit id % is not valid', org_unit_id;
+ END IF;
+ --
+ IF parent IS NULL THEN
+ EXIT;
+ ELSE
+ org := parent;
+ END IF;
+ END LOOP;
+ --
+ IF percent IS NULL THEN
+ RETURN 10; -- Last-ditch default
+ ELSE
+ RETURN percent;
+ END IF;
+END;
+$$ LANGUAGE 'plpgsql';
+
+COMMIT;
+
+-- If there is no auditor schema, the following ALTERs
+-- will fail, and that's okay. The first one will fail
+-- if the fiscal_calendar column is already present.
+
+ALTER TABLE auditor.actor_org_unit_history
+ ADD COLUMN fiscal_calendar INT;
+
+ALTER TABLE auditor.actor_org_unit_history
+ ADD COLUMN spend_warning_percent INT;
+
+ALTER TABLE auditor.actor_org_unit_history
+ ADD COLUMN spend_limit_percent INT;
+
--
2.11.0