Open-ILS/src/sql/Pg/version-upgrade/2.6.2-2.6.3-upgrade-db.sql

   1 --Upgrade Script for 2.6.2 to 2.6.3
   2 \set eg_version '''2.6.3'''
   3 BEGIN;
   4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.6.3', :eg_version);
   5
   6 SELECT evergreen.upgrade_deps_block_check('0887', :eg_version);
   7
   8 DROP FUNCTION IF EXISTS vandelay.marc21_extract_fixed_field_list( text, text );
   9
  10 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field_list( marc TEXT, ff TEXT, use_default BOOL DEFAULT FALSE ) RETURNS TEXT[] AS $func$
  11 DECLARE
  12     rtype       TEXT;
  13     ff_pos      RECORD;
  14     tag_data    RECORD;
  15     val         TEXT;
  16     collection  TEXT[] := '{}'::TEXT[];
  17 BEGIN
  18     rtype := (vandelay.marc21_record_type( marc )).code;
  19     FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
  20         IF ff_pos.tag = 'ldr' THEN
  21             val := oils_xpath_string('//*[local-name()="leader"]', marc);
  22             IF val IS NOT NULL THEN
  23                 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
  24                 collection := collection || val;
  25             END IF;
  26         ELSE
  27             FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
  28                 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
  29                 collection := collection || val;
  30             END LOOP;
  31         END IF;
  32         CONTINUE WHEN NOT use_default;
  33         CONTINUE WHEN ARRAY_UPPER(collection, 1) > 0;
  34         val := REPEAT( ff_pos.default_val, ff_pos.length );
  35         collection := collection || val;
  36     END LOOP;
  37
  38     RETURN collection;
  39 END;
  40 $func$ LANGUAGE PLPGSQL;
  41
  42 DROP FUNCTION IF EXISTS vandelay.marc21_extract_fixed_field( text, text );
  43
  44 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT, use_default BOOL DEFAULT FALSE ) RETURNS TEXT AS $func$
  45 DECLARE
  46     rtype       TEXT;
  47     ff_pos      RECORD;
  48     tag_data    RECORD;
  49     val         TEXT;
  50 BEGIN
  51     rtype := (vandelay.marc21_record_type( marc )).code;
  52     FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
  53         IF ff_pos.tag = 'ldr' THEN
  54             val := oils_xpath_string('//*[local-name()="leader"]', marc);
  55             IF val IS NOT NULL THEN
  56                 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
  57                 RETURN val;
  58             END IF;
  59         ELSE
  60             FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
  61                 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
  62                 RETURN val;
  63             END LOOP;
  64         END IF;
  65         CONTINUE WHEN NOT use_default;
  66         val := REPEAT( ff_pos.default_val, ff_pos.length );
  67         RETURN val;
  68     END LOOP;
  69
  70     RETURN NULL;
  71 END;
  72 $func$ LANGUAGE PLPGSQL;
  73
  74 DROP FUNCTION IF EXISTS vandelay.marc21_extract_all_fixed_fields( text );
  75
  76 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT, use_default BOOL DEFAULT FALSE ) RETURNS SETOF biblio.record_ff_map AS $func$
  77 DECLARE
  78     tag_data    TEXT;
  79     rtype       TEXT;
  80     ff_pos      RECORD;
  81     output      biblio.record_ff_map%ROWTYPE;
  82 BEGIN
  83     rtype := (vandelay.marc21_record_type( marc )).code;
  84
  85     FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
  86         output.ff_name  := ff_pos.fixed_field;
  87         output.ff_value := NULL;
  88
  89         IF ff_pos.tag = 'ldr' THEN
  90             output.ff_value := oils_xpath_string('//*[local-name()="leader"]', marc);
  91             IF output.ff_value IS NOT NULL THEN
  92                 output.ff_value := SUBSTRING( output.ff_value, ff_pos.start_pos + 1, ff_pos.length );
  93                 RETURN NEXT output;
  94                 output.ff_value := NULL;
  95             END IF;
  96         ELSE
  97             FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
  98                 output.ff_value := SUBSTRING( tag_data, ff_pos.start_pos + 1, ff_pos.length );
  99                 CONTINUE WHEN output.ff_value IS NULL AND NOT use_default;
 100                 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
 101                 RETURN NEXT output;
 102                 output.ff_value := NULL;
 103             END LOOP;
 104         END IF;
 105
 106     END LOOP;
 107
 108     RETURN;
 109 END;
 110 $func$ LANGUAGE PLPGSQL;
 111
 112 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field_list( rid BIGINT, ff TEXT ) RETURNS TEXT[] AS $func$
 113     SELECT * FROM vandelay.marc21_extract_fixed_field_list( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2, TRUE );
 114 $func$ LANGUAGE SQL;
 115
 116 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$
 117     SELECT * FROM vandelay.marc21_extract_fixed_field( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2, TRUE );
 118 $func$ LANGUAGE SQL;
 119
 120 CREATE OR REPLACE FUNCTION biblio.marc21_extract_all_fixed_fields( rid BIGINT ) RETURNS SETOF biblio.record_ff_map AS $func$
 121     SELECT $1 AS record, ff_name, ff_value FROM vandelay.marc21_extract_all_fixed_fields( (SELECT marc FROM biblio.record_entry WHERE id = $1), TRUE );
 122 $func$ LANGUAGE SQL;
 123
 124
 125 SELECT evergreen.upgrade_deps_block_check('0890', :eg_version);
 126
 127 CREATE OR REPLACE FUNCTION acq.transfer_fund(
 128         old_fund   IN INT,
 129         old_amount IN NUMERIC,     -- in currency of old fund
 130         new_fund   IN INT,
 131         new_amount IN NUMERIC,     -- in currency of new fund
 132         user_id    IN INT,
 133         xfer_note  IN TEXT         -- to be recorded in acq.fund_transfer
 134         -- ,funding_source_in IN INT  -- if user wants to specify a funding source (see notes)
 135 ) RETURNS VOID AS $$
 136 /* -------------------------------------------------------------------------------
 137
 138 Function to transfer money from one fund to another.
 139
 140 A transfer is represented as a pair of entries in acq.fund_allocation, with a
 141 negative amount for the old (losing) fund and a positive amount for the new
 142 (gaining) fund.  In some cases there may be more than one such pair of entries
 143 in order to pull the money from different funding sources, or more specifically
 144 from different funding source credits.  For each such pair there is also an
 145 entry in acq.fund_transfer.
 146
 147 Since funding_source is a non-nullable column in acq.fund_allocation, we must
 148 choose a funding source for the transferred money to come from.  This choice
 149 must meet two constraints, so far as possible:
 150
 151 1. The amount transferred from a given funding source must not exceed the
 152 amount allocated to the old fund by the funding source.  To that end we
 153 compare the amount being transferred to the amount allocated.
 154
 155 2. We shouldn't transfer money that has already been spent or encumbered, as
 156 defined by the funding attribution process.  We attribute expenses to the
 157 oldest funding source credits first.  In order to avoid transferring that
 158 attributed money, we reverse the priority, transferring from the newest funding
 159 source credits first.  There can be no guarantee that this approach will
 160 avoid overcommitting a fund, but no other approach can do any better.
 161
 162 In this context the age of a funding source credit is defined by the
 163 deadline_date for credits with deadline_dates, and by the effective_date for
 164 credits without deadline_dates, with the proviso that credits with deadline_dates
 165 are all considered "older" than those without.
 166
 167 ----------
 168
 169 In the signature for this function, there is one last parameter commented out,
 170 named "funding_source_in".  Correspondingly, the WHERE clause for the query
 171 driving the main loop has an OR clause commented out, which references the
 172 funding_source_in parameter.
 173
 174 If these lines are uncommented, this function will allow the user optionally to
 175 restrict a fund transfer to a specified funding source.  If the source
 176 parameter is left NULL, then there will be no such restriction.
 177
 178 ------------------------------------------------------------------------------- */
 179 DECLARE
 180         same_currency      BOOLEAN;
 181         currency_ratio     NUMERIC;
 182         old_fund_currency  TEXT;
 183         old_remaining      NUMERIC;  -- in currency of old fund
 184         new_fund_currency  TEXT;
 185         new_fund_active    BOOLEAN;
 186         new_remaining      NUMERIC;  -- in currency of new fund
 187         curr_old_amt       NUMERIC;  -- in currency of old fund
 188         curr_new_amt       NUMERIC;  -- in currency of new fund
 189         source_addition    NUMERIC;  -- in currency of funding source
 190         source_deduction   NUMERIC;  -- in currency of funding source
 191         orig_allocated_amt NUMERIC;  -- in currency of funding source
 192         allocated_amt      NUMERIC;  -- in currency of fund
 193         source             RECORD;
 194 BEGIN
 195         --
 196         -- Sanity checks
 197         --
 198         IF old_fund IS NULL THEN
 199                 RAISE EXCEPTION 'acq.transfer_fund: old fund id is NULL';
 200         END IF;
 201         --
 202         IF old_amount IS NULL THEN
 203                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer is NULL';
 204         END IF;
 205         --
 206         -- The new fund and its amount must be both NULL or both not NULL.
 207         --
 208         IF new_fund IS NOT NULL AND new_amount IS NULL THEN
 209                 RAISE EXCEPTION 'acq.transfer_fund: amount to transfer to receiving fund is NULL';
 210         END IF;
 211         --
 212         IF new_fund IS NULL AND new_amount IS NOT NULL THEN
 213                 RAISE EXCEPTION 'acq.transfer_fund: receiving fund is NULL, its amount is not NULL';
 214         END IF;
 215         --
 216         IF user_id IS NULL THEN
 217                 RAISE EXCEPTION 'acq.transfer_fund: user id is NULL';
 218         END IF;
 219         --
 220         -- Initialize the amounts to be transferred, each denominated
 221         -- in the currency of its respective fund.  They will be
 222         -- reduced on each iteration of the loop.
 223         --
 224         old_remaining := old_amount;
 225         new_remaining := new_amount;
 226         --
 227         -- RAISE NOTICE 'Transferring % in fund % to % in fund %',
 228         --      old_amount, old_fund, new_amount, new_fund;
 229         --
 230         -- Get the currency types of the old and new funds.
 231         --
 232         SELECT
 233                 currency_type
 234         INTO
 235                 old_fund_currency
 236         FROM
 237                 acq.fund
 238         WHERE
 239                 id = old_fund;
 240         --
 241         IF old_fund_currency IS NULL THEN
 242                 RAISE EXCEPTION 'acq.transfer_fund: old fund id % is not defined', old_fund;
 243         END IF;
 244         --
 245         IF new_fund IS NOT NULL THEN
 246                 SELECT
 247                         currency_type,
 248                         active
 249                 INTO
 250                         new_fund_currency,
 251                         new_fund_active
 252                 FROM
 253                         acq.fund
 254                 WHERE
 255                         id = new_fund;
 256                 --
 257                 IF new_fund_currency IS NULL THEN
 258                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is not defined', new_fund;
 259                 ELSIF NOT new_fund_active THEN
 260                         --
 261                         -- No point in putting money into a fund from whence you can't spend it
 262                         --
 263                         RAISE EXCEPTION 'acq.transfer_fund: new fund id % is inactive', new_fund;
 264                 END IF;
 265                 --
 266                 IF new_amount = old_amount THEN
 267                         same_currency := true;
 268                         currency_ratio := 1;
 269                 ELSE
 270                         --
 271                         -- We'll have to translate currency between funds.  We presume that
 272                         -- the calling code has already applied an appropriate exchange rate,
 273                         -- so we'll apply the same conversion to each sub-transfer.
 274                         --
 275                         same_currency := false;
 276                         currency_ratio := new_amount / old_amount;
 277                 END IF;
 278         END IF;
 279         --
 280         -- Identify the funding source(s) from which we want to transfer the money.
 281         -- The principle is that we want to transfer the newest money first, because
 282         -- we spend the oldest money first.  The priority for spending is defined
 283         -- by a sort of the view acq.ordered_funding_source_credit.
 284         --
 285         FOR source in
 286                 SELECT
 287                         ofsc.id,
 288                         ofsc.funding_source,
 289                         ofsc.amount,
 290                         ofsc.amount * acq.exchange_ratio( fs.currency_type, old_fund_currency )
 291                                 AS converted_amt,
 292                         fs.currency_type
 293                 FROM
 294                         acq.ordered_funding_source_credit AS ofsc,
 295                         acq.funding_source fs
 296                 WHERE
 297                         ofsc.funding_source = fs.id
 298                         and ofsc.funding_source IN
 299                         (
 300                                 SELECT funding_source
 301                                 FROM acq.fund_allocation
 302                                 WHERE fund = old_fund
 303                         )
 304                         -- and
 305                         -- (
 306                         --      ofsc.funding_source = funding_source_in
 307                         --      OR funding_source_in IS NULL
 308                         -- )
 309                 ORDER BY
 310                         ofsc.sort_priority desc,
 311                         ofsc.sort_date desc,
 312                         ofsc.id desc
 313         LOOP
 314                 --
 315                 -- Determine how much money the old fund got from this funding source,
 316                 -- denominated in the currency types of the source and of the fund.
 317                 -- This result may reflect transfers from previous iterations.
 318                 --
 319                 SELECT
 320                         COALESCE( sum( amount ), 0 ),
 321                         COALESCE( sum( amount )
 322                                 * acq.exchange_ratio( source.currency_type, old_fund_currency ), 0 )
 323                 INTO
 324                         orig_allocated_amt,     -- in currency of the source
 325                         allocated_amt           -- in currency of the old fund
 326                 FROM
 327                         acq.fund_allocation
 328                 WHERE
 329                         fund = old_fund
 330                         and funding_source = source.funding_source;
 331                 --
 332                 -- Determine how much to transfer from this credit, in the currency
 333                 -- of the fund.   Begin with the amount remaining to be attributed:
 334                 --
 335                 curr_old_amt := old_remaining;
 336                 --
 337                 -- Can't attribute more than was allocated from the fund:
 338                 --
 339                 IF curr_old_amt > allocated_amt THEN
 340                         curr_old_amt := allocated_amt;
 341                 END IF;
 342                 --
 343                 -- Can't attribute more than the amount of the current credit:
 344                 --
 345                 IF curr_old_amt > source.converted_amt THEN
 346                         curr_old_amt := source.converted_amt;
 347                 END IF;
 348                 --
 349                 curr_old_amt := trunc( curr_old_amt, 2 );
 350                 --
 351                 old_remaining := old_remaining - curr_old_amt;
 352                 --
 353                 -- Determine the amount to be deducted, if any,
 354                 -- from the old allocation.
 355                 --
 356                 IF old_remaining > 0 THEN
 357                         --
 358                         -- In this case we're using the whole allocation, so use that
 359                         -- amount directly instead of applying a currency translation
 360                         -- and thereby inviting round-off errors.
 361                         --
 362                         source_deduction := - curr_old_amt;
 363                 ELSE
 364                         source_deduction := trunc(
 365                                 ( - curr_old_amt ) *
 366                                         acq.exchange_ratio( old_fund_currency, source.currency_type ),
 367                                 2 );
 368                 END IF;
 369                 --
 370                 IF source_deduction <> 0 THEN
 371                         --
 372                         -- Insert negative allocation for old fund in fund_allocation,
 373                         -- converted into the currency of the funding source
 374                         --
 375                         INSERT INTO acq.fund_allocation (
 376                                 funding_source,
 377                                 fund,
 378                                 amount,
 379                                 allocator,
 380                                 note
 381                         ) VALUES (
 382                                 source.funding_source,
 383                                 old_fund,
 384                                 source_deduction,
 385                                 user_id,
 386                                 'Transfer to fund ' || new_fund
 387                         );
 388                 END IF;
 389                 --
 390                 IF new_fund IS NOT NULL THEN
 391                         --
 392                         -- Determine how much to add to the new fund, in
 393                         -- its currency, and how much remains to be added:
 394                         --
 395                         IF same_currency THEN
 396                                 curr_new_amt := curr_old_amt;
 397                         ELSE
 398                                 IF old_remaining = 0 THEN
 399                                         --
 400                                         -- This is the last iteration, so nothing should be left
 401                                         --
 402                                         curr_new_amt := new_remaining;
 403                                         new_remaining := 0;
 404                                 ELSE
 405                                         curr_new_amt := trunc( curr_old_amt * currency_ratio, 2 );
 406                                         new_remaining := new_remaining - curr_new_amt;
 407                                 END IF;
 408                         END IF;
 409                         --
 410                         -- Determine how much to add, if any,
 411                         -- to the new fund's allocation.
 412                         --
 413                         IF old_remaining > 0 THEN
 414                                 --
 415                                 -- In this case we're using the whole allocation, so use that amount
 416                                 -- amount directly instead of applying a currency translation and
 417                                 -- thereby inviting round-off errors.
 418                                 --
 419                                 source_addition := curr_new_amt;
 420                         ELSIF source.currency_type = old_fund_currency THEN
 421                                 --
 422                                 -- In this case we don't need a round trip currency translation,
 423                                 -- thereby inviting round-off errors:
 424                                 --
 425                                 source_addition := curr_old_amt;
 426                         ELSE
 427                                 source_addition := trunc(
 428                                         curr_new_amt *
 429                                                 acq.exchange_ratio( new_fund_currency, source.currency_type ),
 430                                         2 );
 431                         END IF;
 432                         --
 433                         IF source_addition <> 0 THEN
 434                                 --
 435                                 -- Insert positive allocation for new fund in fund_allocation,
 436                                 -- converted to the currency of the founding source
 437                                 --
 438                                 INSERT INTO acq.fund_allocation (
 439                                         funding_source,
 440                                         fund,
 441                                         amount,
 442                                         allocator,
 443                                         note
 444                                 ) VALUES (
 445                                         source.funding_source,
 446                                         new_fund,
 447                                         source_addition,
 448                                         user_id,
 449                                         'Transfer from fund ' || old_fund
 450                                 );
 451                         END IF;
 452                 END IF;
 453                 --
 454                 IF trunc( curr_old_amt, 2 ) <> 0
 455                 OR trunc( curr_new_amt, 2 ) <> 0 THEN
 456                         --
 457                         -- Insert row in fund_transfer, using amounts in the currency of the funds
 458                         --
 459                         INSERT INTO acq.fund_transfer (
 460                                 src_fund,
 461                                 src_amount,
 462                                 dest_fund,
 463                                 dest_amount,
 464                                 transfer_user,
 465                                 note,
 466                                 funding_source_credit
 467                         ) VALUES (
 468                                 old_fund,
 469                                 trunc( curr_old_amt, 2 ),
 470                                 new_fund,
 471                                 trunc( curr_new_amt, 2 ),
 472                                 user_id,
 473                                 xfer_note,
 474                                 source.id
 475                         );
 476                 END IF;
 477                 --
 478                 if old_remaining <= 0 THEN
 479                         EXIT;                   -- Nothing more to be transferred
 480                 END IF;
 481         END LOOP;
 482 END;
 483 $$ LANGUAGE plpgsql;
 484
 485
 486 SELECT evergreen.upgrade_deps_block_check('0891', :eg_version);
 487
 488 UPDATE permission.perm_list
 489 SET description = 'Allows a user to process and verify URLs'
 490 WHERE code = 'URL_VERIFY';
 491
 492 COMMIT;