Open-ILS/src/sql/Pg/upgrade/0049.schema.acq_funding_allocation_percent.sql

   1 BEGIN;
   2
   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
   6
   7 INSERT INTO config.upgrade_log (version) VALUES ('0049'); -- Scott McKellar
   8
   9 CREATE TABLE acq.fund_allocation_percent
  10 (
  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,
  16     fund_code            TEXT,
  17     percent              NUMERIC           NOT NULL,
  18     allocator            INTEGER           NOT NULL REFERENCES actor.usr
  19                                                DEFERRABLE INITIALLY DEFERRED,
  20     note                 TEXT,
  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 )
  24 );
  25
  26 -- Trigger function to validate combination of org_unit and fund_code
  27
  28 CREATE OR REPLACE FUNCTION acq.fund_alloc_percent_val()
  29 RETURNS TRIGGER AS $$
  30 --
  31 DECLARE
  32 --
  33 dummy int := 0;
  34 --
  35 BEGIN
  36     SELECT
  37         1
  38     INTO
  39         dummy
  40     FROM
  41         acq.fund
  42     WHERE
  43         org = NEW.org
  44         AND code = NEW.fund_code
  45         LIMIT 1;
  46     --
  47     IF dummy = 1 then
  48         RETURN NEW;
  49     ELSE
  50         RAISE EXCEPTION 'No fund exists for org % and code %', NEW.org, NEW.fund_code;
  51     END IF;
  52 END;
  53 $$ LANGUAGE plpgsql;
  54
  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();
  58
  59 -- To do: trigger to verify that percentages don't add up to more than 100
  60
  61 CREATE OR REPLACE FUNCTION acq.fap_limit_100()
  62 RETURNS TRIGGER AS $$
  63 DECLARE
  64 --
  65 total_percent numeric;
  66 --
  67 BEGIN
  68     SELECT
  69         sum( percent )
  70     INTO
  71         total_percent
  72     FROM
  73         acq.fund_allocation_percent AS fap
  74     WHERE
  75         fap.funding_source = NEW.funding_source;
  76     --
  77     IF total_percent > 100 THEN
  78         RAISE EXCEPTION 'Total percentages exceed 100 for funding_source %',
  79             NEW.funding_source;
  80     ELSE
  81         RETURN NEW;
  82     END IF;
  83 END;
  84 $$ LANGUAGE plpgsql;
  85
  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();
  89
  90 -- Populate new table from acq.fund_allocation
  91
  92 INSERT INTO acq.fund_allocation_percent
  93 (
  94     funding_source,
  95     org,
  96     fund_code,
  97     percent,
  98     allocator,
  99     note,
 100     create_time
 101 )
 102     SELECT
 103         fa.funding_source,
 104         fund.org,
 105         fund.code,
 106         fa.percent,
 107         fa.allocator,
 108         fa.note,
 109         fa.create_time
 110     FROM
 111         acq.fund_allocation AS fa
 112             INNER JOIN acq.fund AS fund
 113                 ON ( fa.fund = fund.id )
 114     WHERE
 115         fa.percent is not null
 116     ORDER BY
 117         fund.org;
 118
 119 -- Temporary function to convert percentages to amounts in acq.fund_allocation
 120
 121 -- Algorithm to apply to each funding source:
 122
 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.
 137
 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.
 141
 142 CREATE OR REPLACE FUNCTION acq.apply_percents()
 143 RETURNS VOID AS $$
 144 declare
 145 --
 146 tot              RECORD;
 147 fund             RECORD;
 148 tot_cents        INTEGER;
 149 src              INTEGER;
 150 id               INTEGER[];
 151 curr_id          INTEGER;
 152 pennies          NUMERIC[];
 153 curr_amount      NUMERIC;
 154 i                INTEGER;
 155 total_of_floors  INTEGER;
 156 total_percent    NUMERIC;
 157 total_allocation INTEGER;
 158 residue          INTEGER;
 159 --
 160 begin
 161         RAISE NOTICE 'Applying percents';
 162         FOR tot IN
 163                 SELECT
 164                         fsrc.funding_source,
 165                         sum( fsrc.amount ) AS total
 166                 FROM
 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 )
 172                 GROUP BY
 173                         fsrc.funding_source
 174         LOOP
 175                 tot_cents = floor( tot.total * 100 );
 176                 src = tot.funding_source;
 177                 RAISE NOTICE 'Funding source % total %',
 178                         src, tot_cents;
 179                 i := 0;
 180                 total_of_floors := 0;
 181                 total_percent := 0;
 182                 --
 183                 FOR fund in
 184                         SELECT
 185                                 fa.id,
 186                                 fa.percent,
 187                                 floor( fa.percent * tot_cents / 100 ) as floor_pennies
 188                         FROM
 189                                 acq.fund_allocation AS fa
 190                         WHERE
 191                                 fa.funding_source = src
 192                                 AND fa.percent IS NOT NULL
 193                         ORDER BY
 194                                 mod( fa.percent * tot_cents / 100, 1 ),
 195                                 fa.fund,
 196                                 fa.id
 197                 LOOP
 198                         RAISE NOTICE '   %: %',
 199                                 fund.id,
 200                                 fund.floor_pennies;
 201                         i := i + 1;
 202                         id[i] = fund.id;
 203                         pennies[i] = fund.floor_pennies;
 204                         total_percent := total_percent + fund.percent;
 205                         total_of_floors := total_of_floors + pennies[i];
 206                 END LOOP;
 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;
 211                 --
 212                 -- Post the calculated amounts, revising as needed to
 213                 -- distribute the rounding error
 214                 --
 215                 WHILE i > 0 LOOP
 216                         IF residue > 0 THEN
 217                                 pennies[i] = pennies[i] + 1;
 218                                 residue := residue - 1;
 219                         END IF;
 220                         --
 221                         -- Post amount
 222                         --
 223                         curr_id     := id[i];
 224                         curr_amount := trunc( pennies[i] / 100, 2 );
 225                         --
 226                         UPDATE
 227                                 acq.fund_allocation AS fa
 228                         SET
 229                                 amount = curr_amount,
 230                                 percent = NULL
 231                         WHERE
 232                                 fa.id = curr_id;
 233                         --
 234                         RAISE NOTICE '   ID % and amount %',
 235                                 curr_id,
 236                                 curr_amount;
 237                         i = i - 1;
 238                 END LOOP;
 239         END LOOP;
 240 end;
 241 $$ LANGUAGE 'plpgsql';
 242
 243 -- Run the temporary function
 244
 245 select * from acq.apply_percents();
 246
 247 -- Drop the temporary function now that we're done with it
 248
 249 drop function acq.apply_percents();
 250
 251 COMMIT;