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