LP#1549505: update baseline database schema
[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
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