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