From 14ac2aa12dff2fe6b6fc57290a8af5a79aa0130c Mon Sep 17 00:00:00 2001
From: scottmk <scottmk@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Thu, 22 Oct 2009 18:55:14 +0000
Subject: [PATCH] Create new table acq.fund_allocation_percent, to store the
percentages by which future funding credits will be allocated to funds.
Create a trigger to ensure that the percentages never add up to more than
100 for any given funding source.
Populate the new table from acq.fund_allocation.
In acq.fund_allocation: convert any percentages to amounts, and nullify the
percentages.
M Open-ILS/src/sql/Pg/002.schema.config.sql
A Open-ILS/src/sql/Pg/upgrade/0049.schema.acq_funding_allocation_percent.sql
M Open-ILS/examples/fm_IDL.xml
git-svn-id: svn://svn.open-ils.org/ILS/trunk@14566 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
Open-ILS/examples/fm_IDL.xml | 33 +++
Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +-
.../0049.schema.acq_funding_allocation_percent.sql | 251 +++++++++++++++++++++
3 files changed, 285 insertions(+), 1 deletion(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/0049.schema.acq_funding_allocation_percent.sql
diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index fef9047159..4cffee95dd 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -1690,6 +1690,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
<field reporter:label="Billable Transactions" name="billable_transactions" oils_persist:virtual="true" reporter:datatype="link"/>
<field reporter:label="Checkins" name="checkins" oils_persist:virtual="true" reporter:datatype="link"/>
<field reporter:label="Circulations Performed as Staff" name="performed_circulations" oils_persist:virtual="true" reporter:datatype="link"/>
+ <field reporter:label="Fund Allocation Percentages" name="fund_alloc_pcts" oils_persist:virtual="true" reporter:datatype="link"/>
</fields>
<links>
<link field="demographic" reltype="might_have" key="id" map="" class="rud"/>
@@ -1717,8 +1718,10 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
<link reporter:label="Check-ins Performed as Staff" field="checkins" reltype="has_many" key="checkin_staff" map="" class="circ"/>
<link field="cards" reltype="has_many" key="usr" map="" class="ac"/>
<link reporter:label="Circulations Performed as Staff" field="performed_circulations" reltype="has_many" key="circ_staff" map="" class="circ"/>
+ <link field="fund_alloc_pcts" reltype="has_many" key="allocator" map="" class="acqfap"/>
</links>
</class>
+
<class id="coust" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="config::org_unit_setting_type" oils_persist:tablename="config.org_unit_setting_type" reporter:label="Organizational Unit Setting Type">
<fields oils_persist:primary="name">
<field name="name" reporter:datatype="text"/>
@@ -2778,6 +2781,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
<field reporter:label="Addresses" name="addresses" oils_persist:virtual="true" reporter:datatype="link"/>
<field reporter:label="Checkins" name="checkins" oils_persist:virtual="true" reporter:datatype="link"/>
<field reporter:label="Workstations" name="workstations" oils_persist:virtual="true" reporter:datatype="link"/>
+ <field reporter:label="Fund Allocation Percentages" name="fund_alloc_pcts" oils_persist:virtual="true" reporter:datatype="link"/>
</fields>
<links>
<link field="billing_address" reltype="has_a" key="id" map="" class="aoa"/>
@@ -2796,6 +2800,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
<link field="workstations" reltype="has_many" key="owning_lib" map="" class="aws"/>
<link field="distribution_formulas" reltype="has_many" key="owner" map="" class="acqdf"/>
<link field="distribution_formula_entries" reltype="has_many" key="owning_lib" map="" class="acqdfe"/>
+ <link field="fund_alloc_pcts" reltype="has_many" key="org" map="" class="acqfap"/>
</links>
<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
<actions>
@@ -4078,12 +4083,14 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
<field name="summary" oils_persist:virtual="true"/>
<field reporter:label="Allocations" name="allocations" oils_persist:virtual="true" reporter:datatype="link"/>
<field reporter:label="Credits" name="credits" oils_persist:virtual="true" reporter:datatype="link"/>
+ <field reporter:label="Fund Allocation Percentages" name="fund_alloc_pcts" oils_persist:virtual="true" reporter:datatype="link"/>
</fields>
<links>
<link field="currency_type" reltype="has_a" key="code" map="" class="acqct"/>
<link field="owner" reltype="has_a" key="id" map="" class="aou"/>
<link field="allocations" reltype="has_many" map="" key="funding_source" class="acqfa"/>
<link field="credits" reltype="has_many" key="funding_source" map="" class="acqfscred"/>
+ <link field="fund_alloc_pcts" reltype="has_many" key="funding_source" map="" class="acqfap"/>
</links>
<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
<actions>
@@ -4262,6 +4269,32 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
</links>
</class>
+ <class id="acqfap" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="acq::fund_allocation_percent" oils_persist:tablename="acq.fund_allocation_percent" reporter:label="Fund Allocation Percent">
+ <fields oils_persist:primary="id" oils_persist:sequence="acq.fund_allocation_percent_id_seq">
+ <field reporter:label="Allocation ID" name="id" reporter:datatype="id" />
+ <field reporter:label="Funding Source" name="funding_source" reporter:datatype="link" />
+ <field reporter:label="Org Unit" name="org" reporter:datatype="org_unit" />
+ <field reporter:label="" name="fund_code" reporter:datatype="text" />
+ <field reporter:label="Percent" name="percent" reporter:datatype="float" />
+ <field reporter:label="Allocating User" name="allocator" reporter:datatype="link" />
+ <field reporter:label="Note" name="note" reporter:datatype="text" />
+ <field reporter:label="Create Time" name="create_time" reporter:datatype="timestamp" />
+ </fields>
+ <links>
+ <link field="allocator" reltype="has_a" key="id" map="" class="au"/>
+ <link field="org" reltype="has_a" key="id" map="" class="aou"/>
+ <link field="funding_source" reltype="has_a" key="id" map="" class="acqfs"/>
+ </links>
+ <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+ <actions>
+ <create permission="ADMIN_ACQ_FUND_ALLOCATION_PERCENT" global_required="true"/>
+ <retrieve permission="VIEW_ACQ_FUND_ALLOCATION_PERCENT" global_required="true"/>
+ <update permission="ADMIN_ACQ_FUND_ALLOCATION_PERCENT" global_required="true"/>
+ <delete permission="ADMIN_ACQ_FUND_ALLOCATION_PERCENT" global_required="true"/>
+ </actions>
+ </permacrud>
+ </class>
+
<class id="acqpl" controller="open-ils.cstore open-ils.reporter-store" oils_obj:fieldmapper="acq::picklist" oils_persist:tablename="acq.picklist" reporter:label="Pick List">
<fields oils_persist:primary="id" oils_persist:sequence="acq.picklist_id_seq">
<field reporter:label="Picklist ID" name="id" reporter:datatype="id" />
diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index a89398ca14..accc463ca4 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 ('0048'); -- berick
+INSERT INTO config.upgrade_log (version) VALUES ('0049'); -- Scott McKellar
CREATE TABLE config.bib_source (
diff --git a/Open-ILS/src/sql/Pg/upgrade/0049.schema.acq_funding_allocation_percent.sql b/Open-ILS/src/sql/Pg/upgrade/0049.schema.acq_funding_allocation_percent.sql
new file mode 100644
index 0000000000..8cbba7131f
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0049.schema.acq_funding_allocation_percent.sql
@@ -0,0 +1,251 @@
+BEGIN;
+
+-- Create new table acq.fund_allocation_percent
+-- Populate it from acq.fund_allocation
+-- Convert all percentages to amounts in acq.fund_allocation
+
+INSERT INTO config.upgrade_log (version) VALUES ('0049'); -- Scott McKellar
+
+CREATE TABLE acq.fund_allocation_percent
+(
+ id SERIAL PRIMARY KEY,
+ funding_source INT NOT NULL REFERENCES acq.funding_source
+ DEFERRABLE INITIALLY DEFERRED,
+ org INT NOT NULL REFERENCES actor.org_unit
+ DEFERRABLE INITIALLY DEFERRED,
+ fund_code TEXT,
+ percent NUMERIC NOT NULL,
+ allocator INTEGER NOT NULL REFERENCES actor.usr
+ DEFERRABLE_INITIALLY DEFERRED,
+ note TEXT,
+ create_time TIMESTAMPTZ NOT NULL DEFAULT now(),
+ CONSTRAINT logical_key UNIQUE( funding_source, org, fund_code ),
+ CONSTRAINT percentage_range CHECK( percent >= 0 AND percent <= 100 )
+);
+
+-- Trigger function to validate combination of org_unit and fund_code
+
+CREATE OR REPLACE FUNCTION acq.fund_alloc_percent_val()
+RETURNS TRIGGER AS $$
+--
+DECLARE
+--
+dummy int := 0;
+--
+BEGIN
+ SELECT
+ 1
+ INTO
+ dummy
+ FROM
+ acq.fund
+ WHERE
+ org = NEW.org
+ AND code = NEW.fund_code
+ LIMIT 1;
+ --
+ IF dummy = 1 then
+ RETURN NEW;
+ ELSE
+ RAISE EXCEPTION 'No fund exists for org % and code %', NEW.org, NEW.fund_code;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER acq_fund_alloc_percent_val_trig
+ BEFORE INSERT OR UPDATE ON acq.fund_allocation_percent
+ FOR EACH ROW EXECUTE PROCEDURE acq.fund_alloc_percent_val();
+
+-- To do: trigger to verify that percentages don't add up to more than 100
+
+CREATE OR REPLACE FUNCTION acq.fap_limit_100()
+RETURNS TRIGGER AS $$
+DECLARE
+--
+total_percent numeric;
+--
+BEGIN
+ SELECT
+ sum( percent )
+ INTO
+ total_percent
+ FROM
+ acq.fund_allocation_percent AS fap
+ WHERE
+ fap.funding_source = NEW.funding_source;
+ --
+ IF total_percent > 100 THEN
+ RAISE EXCEPTION 'Total percentages exceed 100 for funding_source %',
+ NEW.funding_source;
+ ELSE
+ RETURN NEW;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER acqfap_limit_100_trig
+ AFTER INSERT OR UPDATE ON acq.fund_allocation_percent
+ FOR EACH ROW EXECUTE PROCEDURE acq.fap_limit_100();
+
+-- Populate new table from acq.fund_allocation
+
+INSERT INTO acq.fund_allocation_percent
+(
+ funding_source,
+ org,
+ fund_code,
+ percent,
+ allocator,
+ note,
+ create_time
+)
+ SELECT
+ fa.funding_source,
+ fund.org,
+ fund.code,
+ fa.percent,
+ fa.allocator,
+ fa.note,
+ fa.create_time
+ FROM
+ acq.fund_allocation AS fa
+ INNER JOIN acq.fund AS fund
+ ON ( fa.fund = fund.id )
+ WHERE
+ fa.percent is not null
+ ORDER BY
+ fund.org,
+
+-- Temporary function to convert percentages to amounts in acq.fund_allocation
+
+-- Algorithm to apply to each funding source:
+
+-- 1. Add up the credits.
+-- 2. Add up the percentages.
+-- 3. Multiply the sum of the percentages timies the sum of the credits. Drop any
+-- fractional cents from the result. This is the total amount to be allocated.
+-- 4. For each allocation: multiply the percentage by the total allocation. Drop any
+-- fractional cents to get a preliminary amount.
+-- 5. Add up the preliminary amounts for all the allocations.
+-- 6. Subtract the results of step 5 from the result of step 3. The difference is the
+-- number of residual cents (resulting from having dropped fractional cents) that
+-- must be distributed across the funds in order to make the total of the amounts
+-- match the total allocation.
+-- 7. Make a second pass through the allocations, in decreasing order of the fractional
+-- cents that were dropped from their amounts in step 4. Add one cent to the amount
+-- for each successive fund, until all the residual cents have been exhausted.
+
+-- Result: the sum of the individual allocations now equals the total to be allocated,
+-- to the penny. The individual amounts match the percentages as closely as possible,
+-- given the constraint that the total must match.
+
+CREATE OR REPLACE FUNCTION acq.apply_percents()
+RETURNS VOID AS $$
+declare
+--
+tot RECORD;
+fund RECORD;
+tot_cents INTEGER;
+src INTEGER;
+id INTEGER[];
+curr_id INTEGER;
+pennies NUMERIC[];
+curr_amount NUMERIC;
+i INTEGER;
+total_of_floors INTEGER;
+total_percent NUMERIC;
+total_allocation INTEGER;
+residue INTEGER;
+--
+begin
+ RAISE NOTICE 'Applying percents';
+ FOR tot IN
+ SELECT
+ fsrc.funding_source,
+ sum( fsrc.amount ) AS total
+ FROM
+ acq.funding_source_credit AS fsrc
+ WHERE fsrc.funding_source IN
+ ( SELECT DISTINCT fa.funding_source
+ FROM acq.fund_allocation AS fa
+ WHERE fa.percent IS NOT NULL )
+ GROUP BY
+ fsrc.funding_source
+ LOOP
+ tot_cents = floor( tot.total * 100 );
+ src = tot.funding_source;
+ RAISE NOTICE 'Funding source % total %',
+ src, tot_cents;
+ i := 0;
+ total_of_floors := 0;
+ total_percent := 0;
+ --
+ FOR fund in
+ SELECT
+ fa.id,
+ fa.percent,
+ floor( fa.percent * tot_cents / 100 ) as floor_pennies
+ FROM
+ acq.fund_allocation AS fa
+ WHERE
+ fa.funding_source = src
+ AND fa.percent IS NOT NULL
+ ORDER BY
+ mod( fa.percent * tot_cents / 100, 1 ),
+ fa.fund,
+ fa.id
+ LOOP
+ RAISE NOTICE ' %: %',
+ fund.id,
+ fund.floor_pennies;
+ i := i + 1;
+ id[i] = fund.id;
+ pennies[i] = fund.floor_pennies;
+ total_percent := total_percent + fund.percent;
+ total_of_floors := total_of_floors + pennies[i];
+ END LOOP;
+ total_allocation := floor( total_percent * tot_cents /100 );
+ RAISE NOTICE 'Total before distributing residue: %', total_of_floors;
+ residue := total_allocation - total_of_floors;
+ RAISE NOTICE 'Residue: %', residue;
+ --
+ -- Post the calculated amounts, revising as needed to
+ -- distribute the rounding error
+ --
+ WHILE i > 0 LOOP
+ IF residue > 0 THEN
+ pennies[i] = pennies[i] + 1;
+ residue := residue - 1;
+ END IF;
+ --
+ -- Post amount
+ --
+ curr_id := id[i];
+ curr_amount := trunc( pennies[i] / 100, 2 );
+ --
+ UPDATE
+ acq.fund_allocation AS fa
+ SET
+ amount = curr_amount,
+ percent = NULL
+ WHERE
+ fa.id = curr_id;
+ --
+ RAISE NOTICE ' ID % and amount %',
+ curr_id,
+ curr_amount;
+ i = i - 1;
+ END LOOP;
+ END LOOP;
+end;
+$$ LANGUAGE 'plpgsql';
+
+-- Run the temporary function
+
+select * from acq.apply_percents();
+
+-- Drop the temporary function now that we're done with it
+
+drop function acq.apply_percents();
+
+COMMIT;
--
2.11.0