Open-ILS/src/sql/Pg/220.schema.rating.sql

   1 DROP SCHEMA IF EXISTS rating CASCADE;
   2
   3 BEGIN;
   4
   5 -- Create these so that the queries in the UDFs will validate
   6 CREATE TEMP TABLE precalc_filter_bib_list (
   7     id  BIGINT
   8 ) ON COMMIT DROP;
   9
  10 CREATE TEMP TABLE precalc_bib_filter_bib_list (
  11     id  BIGINT
  12 ) ON COMMIT DROP;
  13
  14 CREATE TEMP TABLE precalc_src_filter_bib_list (
  15     id  BIGINT
  16 ) ON COMMIT DROP;
  17
  18 CREATE TEMP TABLE precalc_copy_filter_bib_list (
  19     id  BIGINT,
  20     copy  BIGINT
  21 ) ON COMMIT DROP;
  22
  23 CREATE TEMP TABLE precalc_circ_mod_filter_bib_list (
  24     id  BIGINT,
  25     copy  BIGINT
  26 ) ON COMMIT DROP;
  27
  28 CREATE TEMP TABLE precalc_location_filter_bib_list (
  29     id  BIGINT,
  30     copy  BIGINT
  31 ) ON COMMIT DROP;
  32
  33 CREATE TEMP TABLE precalc_attr_filter_bib_list (
  34     id  BIGINT
  35 ) ON COMMIT DROP;
  36
  37 CREATE TEMP TABLE precalc_bibs_by_copy_list (
  38     id  BIGINT
  39 ) ON COMMIT DROP;
  40
  41 CREATE TEMP TABLE precalc_bibs_by_uri_list (
  42     id  BIGINT
  43 ) ON COMMIT DROP;
  44
  45 CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list (
  46     id  BIGINT
  47 ) ON COMMIT DROP;
  48
  49 CREATE TEMP TABLE precalc_bib_list (
  50     id  BIGINT
  51 ) ON COMMIT DROP;
  52
  53 CREATE SCHEMA rating;
  54
  55 CREATE TABLE rating.popularity_parameter (
  56     id          INT     PRIMARY KEY,
  57     name        TEXT    NOT NULL UNIQUE, -- i18n
  58     description TEXT,
  59     func        TEXT,
  60     require_horizon     BOOL    NOT NULL DEFAULT FALSE,
  61     require_importance  BOOL    NOT NULL DEFAULT FALSE,
  62     require_percentile  BOOL    NOT NULL DEFAULT FALSE
  63 );
  64
  65 INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_importance,require_percentile) VALUES
  66     (1,'Holds Filled Over Time','rating.holds_filled_over_time',TRUE,FALSE,TRUE),
  67     (2,'Holds Requested Over Time','rating.holds_placed_over_time',TRUE,FALSE,TRUE),
  68     (3,'Current Hold Count','rating.current_hold_count',FALSE,FALSE,TRUE),
  69     (4,'Circulations Over Time','rating.circs_over_time',TRUE,FALSE,TRUE),
  70     (5,'Current Circulation Count','rating.current_circ_count',FALSE,FALSE,TRUE),
  71     (6,'Out/Total Ratio','rating.checked_out_total_ratio',FALSE,FALSE,TRUE),
  72     (7,'Holds/Total Ratio','rating.holds_total_ratio',FALSE,FALSE,TRUE),
  73     (8,'Holds/Holdable Ratio','rating.holds_holdable_ratio',FALSE,FALSE,TRUE),
  74     (9,'Percent of Time Circulating','rating.percent_time_circulating',FALSE,FALSE,TRUE),
  75     (10,'Bibliographic Record Age (days, newer is better)','rating.bib_record_age',FALSE,FALSE,TRUE),
  76     (11,'Publication Age (days, newer is better)','rating.bib_pub_age',FALSE,FALSE,TRUE),
  77     (12,'On-line Bib has attributes','rating.generic_fixed_rating_by_uri',FALSE,FALSE,FALSE),
  78     (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE),
  79     (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE),
  80     (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE);
  81
  82 CREATE TABLE rating.badge (
  83     id                      SERIAL      PRIMARY KEY,
  84     name                    TEXT        NOT NULL,
  85     description             TEXT,
  86     scope                   INT         NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  87     weight                  INT         NOT NULL DEFAULT 1,
  88     horizon_age             INTERVAL,
  89     importance_age          INTERVAL,
  90     importance_interval     INTERVAL    NOT NULL DEFAULT '1 day',
  91     importance_scale        NUMERIC     CHECK (importance_scale IS NULL OR importance_scale > 0.0),
  92     recalc_interval         INTERVAL    NOT NULL DEFAULT '1 month',
  93     attr_filter             TEXT,
  94     src_filter              INT         REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  95     circ_mod_filter         TEXT        REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  96     loc_grp_filter          INT         REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  97     popularity_parameter    INT         NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  98     fixed_rating            INT         CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5),
  99     percentile              NUMERIC     CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)),
 100     discard                 INT         NOT NULL DEFAULT 0,
 101     last_calc               TIMESTAMPTZ,
 102     CONSTRAINT unique_name_scope UNIQUE (name,scope)
 103 );
 104
 105 CREATE TABLE rating.record_badge_score (
 106     id          BIGSERIAL   PRIMARY KEY,
 107     record      BIGINT      NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 108     badge       INT         NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
 109     score       INT         NOT NULL CHECK (score BETWEEN -5 AND 5),
 110     CONSTRAINT unique_record_badge UNIQUE (record,badge)
 111 );
 112 CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge);
 113 CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record);
 114
 115 CREATE OR REPLACE VIEW rating.badge_with_orgs AS
 116     WITH    org_scope AS (
 117                 SELECT  id,
 118                         array_agg(tree) AS orgs
 119                   FROM  (SELECT id,
 120                                 (actor.org_unit_descendants(id)).id AS tree
 121                           FROM  actor.org_unit
 122                         ) x
 123                   GROUP BY 1
 124             )
 125     SELECT  b.*,
 126             s.orgs
 127       FROM  rating.badge b
 128             JOIN org_scope s ON (b.scope = s.id);
 129
 130 CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT)
 131     RETURNS INT AS $f$
 132 DECLARE
 133     cnt     INT     := 0;
 134 BEGIN
 135
 136     SET LOCAL client_min_messages = error;
 137     DROP TABLE IF EXISTS precalc_src_filter_bib_list;
 138     IF src IS NOT NULL THEN
 139         CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
 140             SELECT id FROM biblio.record_entry
 141             WHERE source = src AND NOT deleted;
 142     ELSE
 143         CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS
 144             SELECT id FROM biblio.record_entry
 145             WHERE id > 0 AND NOT deleted;
 146     END IF;
 147
 148     SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list;
 149     RETURN cnt;
 150 END;
 151 $f$ LANGUAGE PLPGSQL;
 152
 153 CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT)
 154     RETURNS INT AS $f$
 155 DECLARE
 156     cnt     INT     := 0;
 157 BEGIN
 158
 159     SET LOCAL client_min_messages = error;
 160     DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list;
 161     IF cm IS NOT NULL THEN
 162         CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
 163             SELECT  cn.record AS id,
 164                     cp.id AS copy
 165               FROM  asset.call_number cn
 166                     JOIN asset.copy cp ON (cn.id = cp.call_number)
 167               WHERE cp.circ_modifier = cm
 168                     AND NOT cp.deleted;
 169     ELSE
 170         CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS
 171             SELECT  cn.record AS id,
 172                     cp.id AS copy
 173               FROM  asset.call_number cn
 174                     JOIN asset.copy cp ON (cn.id = cp.call_number)
 175               WHERE NOT cp.deleted;
 176     END IF;
 177
 178     SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list;
 179     RETURN cnt;
 180 END;
 181 $f$ LANGUAGE PLPGSQL;
 182
 183 CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT)
 184     RETURNS INT AS $f$
 185 DECLARE
 186     cnt     INT     := 0;
 187 BEGIN
 188
 189     SET LOCAL client_min_messages = error;
 190     DROP TABLE IF EXISTS precalc_location_filter_bib_list;
 191     IF loc IS NOT NULL THEN
 192         CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
 193             SELECT  cn.record AS id,
 194                     cp.id AS copy
 195               FROM  asset.call_number cn
 196                     JOIN asset.copy cp ON (cn.id = cp.call_number)
 197                     JOIN asset.copy_location_group_map lg ON (cp.location = lg.location)
 198               WHERE lg.lgroup = loc
 199                     AND NOT cp.deleted;
 200     ELSE
 201         CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS
 202             SELECT  cn.record AS id,
 203                     cp.id AS copy
 204               FROM  asset.call_number cn
 205                     JOIN asset.copy cp ON (cn.id = cp.call_number)
 206               WHERE NOT cp.deleted;
 207     END IF;
 208
 209     SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list;
 210     RETURN cnt;
 211 END;
 212 $f$ LANGUAGE PLPGSQL;
 213
 214 -- all or limited...
 215 CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT)
 216     RETURNS INT AS $f$
 217 DECLARE
 218     cnt     INT := 0;
 219     afilter TEXT;
 220 BEGIN
 221
 222     SET LOCAL client_min_messages = error;
 223     DROP TABLE IF EXISTS precalc_attr_filter_bib_list;
 224     IF attr_filter IS NOT NULL THEN
 225         afilter := metabib.compile_composite_attr(attr_filter);
 226         CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
 227             SELECT source AS id FROM metabib.record_attr_vector_list
 228             WHERE vlist @@ metabib.compile_composite_attr(attr_filter);
 229     ELSE
 230         CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS
 231             SELECT source AS id FROM metabib.record_attr_vector_list;
 232     END IF;
 233
 234     SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list;
 235     RETURN cnt;
 236 END;
 237 $f$ LANGUAGE PLPGSQL;
 238
 239 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT)
 240     RETURNS INT AS $f$
 241 DECLARE
 242     cnt         INT     := 0;
 243     badge_row   rating.badge_with_orgs%ROWTYPE;
 244     base        TEXT;
 245     whr         TEXT;
 246 BEGIN
 247
 248     SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
 249
 250     SET LOCAL client_min_messages = error;
 251     DROP TABLE IF EXISTS precalc_bibs_by_copy_list;
 252     CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS
 253         SELECT  DISTINCT cn.record AS id
 254           FROM  asset.call_number cn
 255                 JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted)
 256                 JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy)
 257           WHERE cn.owning_lib = ANY (badge_row.orgs)
 258                 AND NOT cn.deleted;
 259
 260     SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list;
 261     RETURN cnt;
 262 END;
 263 $f$ LANGUAGE PLPGSQL;
 264
 265 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT)
 266     RETURNS INT AS $f$
 267 DECLARE
 268     cnt         INT     := 0;
 269     badge_row   rating.badge_with_orgs%ROWTYPE;
 270 BEGIN
 271
 272     SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id;
 273
 274     SET LOCAL client_min_messages = error;
 275     DROP TABLE IF EXISTS precalc_bibs_by_uri_list;
 276     CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS
 277         SELECT  DISTINCT record AS id
 278           FROM  asset.call_number cn
 279                 JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id)
 280                 JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active)
 281           WHERE cn.owning_lib = ANY (badge_row.orgs)
 282                 AND cn.label = '##URI##'
 283                 AND NOT cn.deleted;
 284
 285     SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list;
 286     RETURN cnt;
 287 END;
 288 $f$ LANGUAGE PLPGSQL;
 289
 290 CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT)
 291     RETURNS INT AS $f$
 292 DECLARE
 293     cnt         INT     := 0;
 294 BEGIN
 295
 296     PERFORM rating.precalc_bibs_by_copy(badge_id);
 297     PERFORM rating.precalc_bibs_by_uri(badge_id);
 298
 299     SET LOCAL client_min_messages = error;
 300     DROP TABLE IF EXISTS precalc_bibs_by_copy_or_uri_list;
 301     CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS
 302         SELECT id FROM precalc_bibs_by_copy_list
 303             UNION
 304         SELECT id FROM precalc_bibs_by_uri_list;
 305
 306     SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_or_uri_list;
 307     RETURN cnt;
 308 END;
 309 $f$ LANGUAGE PLPGSQL;
 310
 311
 312 CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$
 313 DECLARE
 314     badge_row           rating.badge%ROWTYPE;
 315     param           rating.popularity_parameter%ROWTYPE;
 316 BEGIN
 317     SET LOCAL client_min_messages = error;
 318
 319     -- Find what we're doing
 320     SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id;
 321     SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter;
 322
 323     -- Calculate the filtered bib set, or all bibs if none
 324     PERFORM rating.precalc_attr_filter(badge_row.attr_filter);
 325     PERFORM rating.precalc_src_filter(badge_row.src_filter);
 326     PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter);
 327     PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter);
 328
 329     -- Bring the bib-level filter lists together
 330     DROP TABLE IF EXISTS precalc_bib_filter_bib_list;
 331     CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS
 332         SELECT id FROM precalc_attr_filter_bib_list
 333             INTERSECT
 334         SELECT id FROM precalc_src_filter_bib_list;
 335
 336     -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later.
 337     DROP TABLE IF EXISTS precalc_copy_filter_bib_list;
 338     CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS
 339         SELECT id, copy FROM precalc_circ_mod_filter_bib_list
 340             INTERSECT
 341         SELECT id, copy FROM precalc_location_filter_bib_list;
 342
 343     -- Bring the collapsed filter lists together
 344     DROP TABLE IF EXISTS precalc_filter_bib_list;
 345     CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS
 346         SELECT id FROM precalc_bib_filter_bib_list
 347             INTERSECT
 348         SELECT id FROM precalc_copy_filter_bib_list;
 349
 350     CREATE INDEX precalc_filter_bib_list_idx
 351         ON precalc_filter_bib_list (id);
 352
 353     IF setup_only THEN
 354         RETURN;
 355     END IF;
 356
 357     -- If it's a fixed-rating badge, just do it ...
 358     IF badge_row.fixed_rating IS NOT NULL THEN
 359         DELETE FROM rating.record_badge_score WHERE badge = badge_id;
 360         EXECUTE $e$
 361             INSERT INTO rating.record_badge_score (record, badge, score)
 362                 SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$
 363         USING badge_id, badge_row.fixed_rating;
 364
 365         UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
 366
 367         RETURN;
 368     END IF;
 369     -- else, calculate!
 370
 371     -- Make a session-local scratchpad for calculating scores
 372     CREATE TEMP TABLE record_score_scratchpad (
 373         bib     BIGINT,
 374         value   NUMERIC
 375     ) ON COMMIT DROP;
 376
 377     -- Gather raw values
 378     EXECUTE $e$
 379         INSERT INTO record_score_scratchpad (bib, value)
 380             SELECT * FROM $e$ || param.func || $e$($1)$e$
 381     USING badge_id;
 382
 383     IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN
 384         -- To speed up discard-common
 385         CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value);
 386         ANALYZE record_score_scratchpad;
 387     END IF;
 388
 389     IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail)
 390         DELETE FROM record_score_scratchpad WHERE value IN (
 391             SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard
 392         );
 393     END IF;
 394
 395     IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records
 396         DELETE FROM record_score_scratchpad WHERE value <= (
 397             SELECT value FROM (
 398                 SELECT  value,
 399                         CUME_DIST() OVER (ORDER BY value) AS p
 400                   FROM  record_score_scratchpad
 401             ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1
 402         );
 403     END IF;
 404
 405
 406     -- And, finally, push new data in
 407     DELETE FROM rating.record_badge_score WHERE badge = badge_id;
 408     INSERT INTO rating.record_badge_score (badge, record, score)
 409         SELECT  badge_id,
 410                 bib,
 411                 GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value
 412           FROM  record_score_scratchpad;
 413
 414     DROP TABLE record_score_scratchpad;
 415
 416     -- Now, finally-finally, mark the badge as recalculated
 417     UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id;
 418
 419     RETURN;
 420 END;
 421 $f$ LANGUAGE PLPGSQL;
 422
 423 CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT)
 424     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 425 DECLARE
 426     badge   rating.badge_with_orgs%ROWTYPE;
 427     iage    INT     := 1;
 428     iint    INT     := NULL;
 429     iscale  NUMERIC := NULL;
 430 BEGIN
 431
 432     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 433
 434     IF badge.horizon_age IS NULL THEN
 435         RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
 436             badge.name,
 437             badge.id;
 438     END IF;
 439
 440     PERFORM rating.precalc_bibs_by_copy(badge_id);
 441
 442     SET LOCAL client_min_messages = error;
 443     DROP TABLE IF EXISTS precalc_bib_list;
 444     CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
 445         SELECT id FROM precalc_filter_bib_list
 446             INTERSECT
 447         SELECT id FROM precalc_bibs_by_copy_list;
 448
 449     iint := EXTRACT(EPOCH FROM badge.importance_interval);
 450     IF badge.importance_age IS NOT NULL THEN
 451         iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
 452     END IF;
 453
 454     -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
 455     iscale := COALESCE(badge.importance_scale, 1.0);
 456
 457     RETURN QUERY
 458      SELECT bib,
 459             SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
 460       FROM (
 461          SELECT f.id AS bib,
 462                 (1 + EXTRACT(EPOCH FROM AGE(h.fulfillment_time)) / iint)::INT AS hage,
 463                 COUNT(h.id)::INT AS holds
 464           FROM  action.hold_request h
 465                 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
 466                 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
 467           WHERE h.fulfillment_time >= NOW() - badge.horizon_age
 468                 AND h.request_lib = ANY (badge.orgs)
 469           GROUP BY 1, 2
 470       ) x
 471       GROUP BY 1;
 472 END;
 473 $f$ LANGUAGE PLPGSQL STRICT;
 474
 475 CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT)
 476     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 477 DECLARE
 478     badge   rating.badge_with_orgs%ROWTYPE;
 479     iage    INT     := 1;
 480     iint    INT     := NULL;
 481     iscale  NUMERIC := NULL;
 482 BEGIN
 483
 484     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 485
 486     IF badge.horizon_age IS NULL THEN
 487         RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
 488             badge.name,
 489             badge.id;
 490     END IF;
 491
 492     PERFORM rating.precalc_bibs_by_copy(badge_id);
 493
 494     SET LOCAL client_min_messages = error;
 495     DROP TABLE IF EXISTS precalc_bib_list;
 496     CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
 497         SELECT id FROM precalc_filter_bib_list
 498             INTERSECT
 499         SELECT id FROM precalc_bibs_by_copy_list;
 500
 501     iint := EXTRACT(EPOCH FROM badge.importance_interval);
 502     IF badge.importance_age IS NOT NULL THEN
 503         iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
 504     END IF;
 505
 506     -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
 507     iscale := COALESCE(badge.importance_scale, 1.0);
 508
 509     RETURN QUERY
 510      SELECT bib,
 511             SUM( holds * GREATEST( iscale * (iage - hage), 1.0 ))
 512       FROM (
 513          SELECT f.id AS bib,
 514                 (1 + EXTRACT(EPOCH FROM AGE(h.request_time)) / iint)::INT AS hage,
 515                 COUNT(h.id)::INT AS holds
 516           FROM  action.hold_request h
 517                 JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
 518                 JOIN precalc_bib_list f ON (f.id = rhrr.bib_record)
 519           WHERE h.request_time >= NOW() - badge.horizon_age
 520                 AND h.request_lib = ANY (badge.orgs)
 521           GROUP BY 1, 2
 522       ) x
 523       GROUP BY 1;
 524 END;
 525 $f$ LANGUAGE PLPGSQL STRICT;
 526
 527 CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT)
 528     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 529 DECLARE
 530     badge   rating.badge_with_orgs%ROWTYPE;
 531 BEGIN
 532
 533     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 534
 535     PERFORM rating.precalc_bibs_by_copy(badge_id);
 536
 537     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 538         SELECT id FROM precalc_filter_bib_list
 539             INTERSECT
 540         SELECT id FROM precalc_bibs_by_copy_list
 541     );
 542
 543     ANALYZE precalc_copy_filter_bib_list;
 544
 545     RETURN QUERY
 546      SELECT rhrr.bib_record AS bib,
 547             COUNT(DISTINCT h.id)::NUMERIC AS holds
 548       FROM  action.hold_request h
 549             JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id)
 550             JOIN action.hold_copy_map m ON (m.hold = h.id)
 551             JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy)
 552       WHERE h.fulfillment_time IS NULL
 553             AND h.request_lib = ANY (badge.orgs)
 554       GROUP BY 1;
 555 END;
 556 $f$ LANGUAGE PLPGSQL STRICT;
 557
 558 CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT)
 559     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 560 DECLARE
 561     badge   rating.badge_with_orgs%ROWTYPE;
 562     iage    INT     := 1;
 563     iint    INT     := NULL;
 564     iscale  NUMERIC := NULL;
 565 BEGIN
 566
 567     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 568
 569     IF badge.horizon_age IS NULL THEN
 570         RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.',
 571             badge.name,
 572             badge.id;
 573     END IF;
 574
 575     PERFORM rating.precalc_bibs_by_copy(badge_id);
 576
 577     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 578         SELECT id FROM precalc_filter_bib_list
 579             INTERSECT
 580         SELECT id FROM precalc_bibs_by_copy_list
 581     );
 582
 583     ANALYZE precalc_copy_filter_bib_list;
 584
 585     iint := EXTRACT(EPOCH FROM badge.importance_interval);
 586     IF badge.importance_age IS NOT NULL THEN
 587         iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT;
 588     END IF;
 589
 590     -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE!
 591     iscale := COALESCE(badge.importance_scale, 1.0);
 592
 593     RETURN QUERY
 594      SELECT bib,
 595             SUM( circs * GREATEST( iscale * (iage - cage), 1.0 ))
 596       FROM (
 597          SELECT cn.record AS bib,
 598                 (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage,
 599                 COUNT(c.id)::INT AS circs
 600           FROM  action.circulation c
 601                 JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
 602                 JOIN asset.copy cp ON (cp.id = c.target_copy)
 603                 JOIN asset.call_number cn ON (cn.id = cp.call_number)
 604           WHERE c.xact_start >= NOW() - badge.horizon_age
 605                 AND cn.owning_lib = ANY (badge.orgs)
 606                 AND c.phone_renewal IS FALSE  -- we don't count renewals
 607                 AND c.desk_renewal IS FALSE
 608                 AND c.opac_renewal IS FALSE
 609           GROUP BY 1, 2
 610       ) x
 611       GROUP BY 1;
 612 END;
 613 $f$ LANGUAGE PLPGSQL STRICT;
 614
 615 CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT)
 616     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 617 DECLARE
 618     badge   rating.badge_with_orgs%ROWTYPE;
 619 BEGIN
 620
 621     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 622
 623     PERFORM rating.precalc_bibs_by_copy(badge_id);
 624
 625     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 626         SELECT id FROM precalc_filter_bib_list
 627             INTERSECT
 628         SELECT id FROM precalc_bibs_by_copy_list
 629     );
 630
 631     ANALYZE precalc_copy_filter_bib_list;
 632
 633     RETURN QUERY
 634      SELECT cn.record AS bib,
 635             COUNT(c.id)::NUMERIC AS circs
 636       FROM  action.circulation c
 637             JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy)
 638             JOIN asset.copy cp ON (cp.id = c.target_copy)
 639             JOIN asset.call_number cn ON (cn.id = cp.call_number)
 640       WHERE c.checkin_time IS NULL
 641             AND cn.owning_lib = ANY (badge.orgs)
 642       GROUP BY 1;
 643
 644 END;
 645 $f$ LANGUAGE PLPGSQL STRICT;
 646
 647 CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT)
 648     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 649 DECLARE
 650     badge   rating.badge_with_orgs%ROWTYPE;
 651 BEGIN
 652
 653     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 654
 655     PERFORM rating.precalc_bibs_by_copy(badge_id);
 656
 657     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 658         SELECT id FROM precalc_filter_bib_list
 659             INTERSECT
 660         SELECT id FROM precalc_bibs_by_copy_list
 661     );
 662
 663     ANALYZE precalc_copy_filter_bib_list;
 664
 665     RETURN QUERY
 666      SELECT bib,
 667             SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC
 668       FROM  (SELECT cn.record AS bib,
 669                     (cp.status = 1)::INT AS checked_out,
 670                     1 AS total
 671               FROM  asset.copy cp
 672                     JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
 673                     JOIN asset.call_number cn ON (cn.id = cp.call_number)
 674               WHERE cn.owning_lib = ANY (badge.orgs)
 675             ) x
 676       GROUP BY 1;
 677 END;
 678 $f$ LANGUAGE PLPGSQL STRICT;
 679
 680 CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT)
 681     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 682 DECLARE
 683     badge   rating.badge_with_orgs%ROWTYPE;
 684 BEGIN
 685
 686     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 687
 688     PERFORM rating.precalc_bibs_by_copy(badge_id);
 689
 690     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 691         SELECT id FROM precalc_filter_bib_list
 692             INTERSECT
 693         SELECT id FROM precalc_bibs_by_copy_list
 694     );
 695
 696     ANALYZE precalc_copy_filter_bib_list;
 697
 698     RETURN QUERY
 699      SELECT cn.record AS bib,
 700             COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
 701       FROM  asset.copy cp
 702             JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
 703             JOIN asset.call_number cn ON (cn.id = cp.call_number)
 704             JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
 705       WHERE cn.owning_lib = ANY (badge.orgs)
 706       GROUP BY 1;
 707 END;
 708 $f$ LANGUAGE PLPGSQL STRICT;
 709
 710 CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT)
 711     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 712 DECLARE
 713     badge   rating.badge_with_orgs%ROWTYPE;
 714 BEGIN
 715
 716     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 717
 718     PERFORM rating.precalc_bibs_by_copy(badge_id);
 719
 720     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 721         SELECT id FROM precalc_filter_bib_list
 722             INTERSECT
 723         SELECT id FROM precalc_bibs_by_copy_list
 724     );
 725
 726     ANALYZE precalc_copy_filter_bib_list;
 727
 728     RETURN QUERY
 729      SELECT cn.record AS bib,
 730             COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC
 731       FROM  asset.copy cp
 732             JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
 733             JOIN asset.copy_location cl ON (cl.id = cp.location)
 734             JOIN config.copy_status cs ON (cs.id = cp.status)
 735             JOIN asset.call_number cn ON (cn.id = cp.call_number)
 736             JOIN action.hold_copy_map m ON (m.target_copy = cp.id)
 737       WHERE cn.owning_lib = ANY (badge.orgs)
 738             AND cp.holdable IS TRUE
 739             AND cl.holdable IS TRUE
 740             AND cs.holdable IS TRUE
 741       GROUP BY 1;
 742 END;
 743 $f$ LANGUAGE PLPGSQL STRICT;
 744
 745 CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT)
 746     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 747 DECLARE
 748     badge   rating.badge_with_orgs%ROWTYPE;
 749 BEGIN
 750
 751     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 752
 753     PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
 754
 755     SET LOCAL client_min_messages = error;
 756     DROP TABLE IF EXISTS precalc_bib_list;
 757     CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
 758         SELECT id FROM precalc_filter_bib_list
 759             INTERSECT
 760         SELECT id FROM precalc_bibs_by_copy_or_uri_list;
 761
 762     RETURN QUERY
 763      SELECT b.id,
 764             1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0
 765       FROM  precalc_bib_list pop
 766             JOIN biblio.record_entry b ON (b.id = pop.id);
 767 END;
 768 $f$ LANGUAGE PLPGSQL STRICT;
 769
 770 CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT)
 771     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 772 DECLARE
 773     badge   rating.badge_with_orgs%ROWTYPE;
 774 BEGIN
 775
 776     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 777
 778     PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
 779
 780     SET LOCAL client_min_messages = error;
 781     DROP TABLE IF EXISTS precalc_bib_list;
 782     CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS
 783         SELECT id FROM precalc_filter_bib_list
 784             INTERSECT
 785         SELECT id FROM precalc_bibs_by_copy_or_uri_list;
 786
 787     RETURN QUERY
 788      SELECT pop.id AS bib,
 789             s.value::NUMERIC
 790       FROM  precalc_bib_list pop
 791             JOIN metabib.record_sorter s ON (
 792                 s.source = pop.id
 793                 AND s.attr = 'pubdate'
 794                 AND s.value ~ '^\d+$'
 795             )
 796       WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT;
 797 END;
 798 $f$ LANGUAGE PLPGSQL STRICT;
 799
 800 CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT)
 801     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 802 DECLARE
 803     badge   rating.badge_with_orgs%ROWTYPE;
 804 BEGIN
 805
 806     SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id;
 807
 808     PERFORM rating.precalc_bibs_by_copy(badge_id);
 809
 810     DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN (
 811         SELECT id FROM precalc_filter_bib_list
 812             INTERSECT
 813         SELECT id FROM precalc_bibs_by_copy_list
 814     );
 815
 816     ANALYZE precalc_copy_filter_bib_list;
 817
 818     RETURN QUERY
 819      SELECT bib,
 820             SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC
 821       FROM  (SELECT cn.record AS bib,
 822                     cp.id,
 823                     EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age,
 824                     SUM(  -- time copy spent circulating
 825                         EXTRACT(
 826                             EPOCH FROM
 827                             AGE(
 828                                 COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()),
 829                                 circ.xact_start
 830                             )
 831                         )
 832                     )::NUMERIC AS circ_time
 833               FROM  asset.copy cp
 834                     JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy)
 835                     JOIN asset.call_number cn ON (cn.id = cp.call_number)
 836                     LEFT JOIN action.all_circulation circ ON (
 837                         circ.target_copy = cp.id
 838                         AND stop_fines NOT IN (
 839                             'LOST',
 840                             'LONGOVERDUE',
 841                             'CLAIMSRETURNED',
 842                             'LONGOVERDUE'
 843                         )
 844                         AND NOT (
 845                             checkin_time IS NULL AND
 846                             stop_fines = 'MAXFINES'
 847                         )
 848                     )
 849               WHERE cn.owning_lib = ANY (badge.orgs)
 850                     AND cp.active_date IS NOT NULL
 851                     -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted
 852                     AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL)
 853               GROUP BY 1,2,3
 854             ) x
 855       GROUP BY 1;
 856 END;
 857 $f$ LANGUAGE PLPGSQL STRICT;
 858
 859 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT)
 860     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 861 BEGIN
 862     PERFORM rating.precalc_bibs_by_copy(badge_id);
 863     RETURN QUERY
 864         SELECT id, 1.0 FROM precalc_filter_bib_list
 865             INTERSECT
 866         SELECT id, 1.0 FROM precalc_bibs_by_copy_list;
 867 END;
 868 $f$ LANGUAGE PLPGSQL STRICT;
 869
 870 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT)
 871     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 872 BEGIN
 873     PERFORM rating.precalc_bibs_by_uri(badge_id);
 874     RETURN QUERY
 875         SELECT id, 1.0 FROM precalc_bib_filter_bib_list
 876             INTERSECT
 877         SELECT id, 1.0 FROM precalc_bibs_by_uri_list;
 878 END;
 879 $f$ LANGUAGE PLPGSQL STRICT;
 880
 881 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy_or_uri(badge_id INT)
 882     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 883 BEGIN
 884     PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id);
 885     RETURN QUERY
 886         (SELECT id, 1.0 FROM precalc_filter_bib_list
 887             INTERSECT
 888         SELECT id, 1.0 FROM precalc_bibs_by_copy_list)
 889             UNION
 890         (SELECT id, 1.0 FROM precalc_bib_filter_bib_list
 891             INTERSECT
 892         SELECT id, 1.0 FROM precalc_bibs_by_uri_list);
 893 END;
 894 $f$ LANGUAGE PLPGSQL STRICT;
 895
 896 CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT)
 897     RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$
 898 BEGIN
 899     RETURN QUERY
 900         SELECT id, 1.0 FROM precalc_bib_filter_bib_list;
 901 END;
 902 $f$ LANGUAGE PLPGSQL STRICT;
 903
 904 COMMIT;
 905