3 -- Create new table acq.fund_allocation_percent
4 -- Populate it from acq.fund_allocation
5 -- Convert all percentages to amounts in acq.fund_allocation
7 INSERT INTO config.upgrade_log (version) VALUES ('0049'); -- Scott McKellar
9 CREATE TABLE acq.fund_allocation_percent
11 id SERIAL PRIMARY KEY,
12 funding_source INT NOT NULL REFERENCES acq.funding_source
13 DEFERRABLE INITIALLY DEFERRED,
14 org INT NOT NULL REFERENCES actor.org_unit
15 DEFERRABLE INITIALLY DEFERRED,
17 percent NUMERIC NOT NULL,
18 allocator INTEGER NOT NULL REFERENCES actor.usr
19 DEFERRABLE INITIALLY DEFERRED,
21 create_time TIMESTAMPTZ NOT NULL DEFAULT now(),
22 CONSTRAINT logical_key UNIQUE( funding_source, org, fund_code ),
23 CONSTRAINT percentage_range CHECK( percent >= 0 AND percent <= 100 )
26 -- Trigger function to validate combination of org_unit and fund_code
28 CREATE OR REPLACE FUNCTION acq.fund_alloc_percent_val()
44 AND code = NEW.fund_code
50 RAISE EXCEPTION 'No fund exists for org % and code %', NEW.org, NEW.fund_code;
55 CREATE TRIGGER acq_fund_alloc_percent_val_trig
56 BEFORE INSERT OR UPDATE ON acq.fund_allocation_percent
57 FOR EACH ROW EXECUTE PROCEDURE acq.fund_alloc_percent_val();
59 -- To do: trigger to verify that percentages don't add up to more than 100
61 CREATE OR REPLACE FUNCTION acq.fap_limit_100()
65 total_percent numeric;
73 acq.fund_allocation_percent AS fap
75 fap.funding_source = NEW.funding_source;
77 IF total_percent > 100 THEN
78 RAISE EXCEPTION 'Total percentages exceed 100 for funding_source %',
86 CREATE TRIGGER acqfap_limit_100_trig
87 AFTER INSERT OR UPDATE ON acq.fund_allocation_percent
88 FOR EACH ROW EXECUTE PROCEDURE acq.fap_limit_100();
90 -- Populate new table from acq.fund_allocation
92 INSERT INTO acq.fund_allocation_percent
111 acq.fund_allocation AS fa
112 INNER JOIN acq.fund AS fund
113 ON ( fa.fund = fund.id )
115 fa.percent is not null
119 -- Temporary function to convert percentages to amounts in acq.fund_allocation
121 -- Algorithm to apply to each funding source:
123 -- 1. Add up the credits.
124 -- 2. Add up the percentages.
125 -- 3. Multiply the sum of the percentages timies the sum of the credits. Drop any
126 -- fractional cents from the result. This is the total amount to be allocated.
127 -- 4. For each allocation: multiply the percentage by the total allocation. Drop any
128 -- fractional cents to get a preliminary amount.
129 -- 5. Add up the preliminary amounts for all the allocations.
130 -- 6. Subtract the results of step 5 from the result of step 3. The difference is the
131 -- number of residual cents (resulting from having dropped fractional cents) that
132 -- must be distributed across the funds in order to make the total of the amounts
133 -- match the total allocation.
134 -- 7. Make a second pass through the allocations, in decreasing order of the fractional
135 -- cents that were dropped from their amounts in step 4. Add one cent to the amount
136 -- for each successive fund, until all the residual cents have been exhausted.
138 -- Result: the sum of the individual allocations now equals the total to be allocated,
139 -- to the penny. The individual amounts match the percentages as closely as possible,
140 -- given the constraint that the total must match.
142 CREATE OR REPLACE FUNCTION acq.apply_percents()
155 total_of_floors INTEGER;
156 total_percent NUMERIC;
157 total_allocation INTEGER;
161 RAISE NOTICE 'Applying percents';
165 sum( fsrc.amount ) AS total
167 acq.funding_source_credit AS fsrc
168 WHERE fsrc.funding_source IN
169 ( SELECT DISTINCT fa.funding_source
170 FROM acq.fund_allocation AS fa
171 WHERE fa.percent IS NOT NULL )
175 tot_cents = floor( tot.total * 100 );
176 src = tot.funding_source;
177 RAISE NOTICE 'Funding source % total %',
180 total_of_floors := 0;
187 floor( fa.percent * tot_cents / 100 ) as floor_pennies
189 acq.fund_allocation AS fa
191 fa.funding_source = src
192 AND fa.percent IS NOT NULL
194 mod( fa.percent * tot_cents / 100, 1 ),
198 RAISE NOTICE ' %: %',
203 pennies[i] = fund.floor_pennies;
204 total_percent := total_percent + fund.percent;
205 total_of_floors := total_of_floors + pennies[i];
207 total_allocation := floor( total_percent * tot_cents /100 );
208 RAISE NOTICE 'Total before distributing residue: %', total_of_floors;
209 residue := total_allocation - total_of_floors;
210 RAISE NOTICE 'Residue: %', residue;
212 -- Post the calculated amounts, revising as needed to
213 -- distribute the rounding error
217 pennies[i] = pennies[i] + 1;
218 residue := residue - 1;
224 curr_amount := trunc( pennies[i] / 100, 2 );
227 acq.fund_allocation AS fa
229 amount = curr_amount,
234 RAISE NOTICE ' ID % and amount %',
241 $$ LANGUAGE 'plpgsql';
243 -- Run the temporary function
245 select * from acq.apply_percents();
247 -- Drop the temporary function now that we're done with it
249 drop function acq.apply_percents();