From da240e76b8a16cf7e079ab09b1fd8571a4d68818 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 18 Aug 2016 12:58:25 -0400 Subject: [PATCH] LP#1613730: Add a "Copy Count" rating function for badges Signed-off-by: Mike Rylander Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/220.schema.rating.sql | 32 ++++++++++++++++- .../Pg/upgrade/XXXX.data.copy-count-badge.sql | 34 +++++++++++++++++++ 2 files changed, 65 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.data.copy-count-badge.sql diff --git a/Open-ILS/src/sql/Pg/220.schema.rating.sql b/Open-ILS/src/sql/Pg/220.schema.rating.sql index 460fc36fdd..3494a7b6de 100644 --- a/Open-ILS/src/sql/Pg/220.schema.rating.sql +++ b/Open-ILS/src/sql/Pg/220.schema.rating.sql @@ -77,7 +77,9 @@ INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_im (12,'On-line Bib has attributes','rating.generic_fixed_rating_by_uri',FALSE,FALSE,FALSE), (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE), (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE), - (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE); + (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE), + (16,'Copy Count','rating.copy_count',FALSE,FALSE,TRUE); + CREATE TABLE rating.badge ( id SERIAL PRIMARY KEY, @@ -901,5 +903,33 @@ BEGIN END; $f$ LANGUAGE PLPGSQL STRICT; +CREATE OR REPLACE FUNCTION rating.copy_count(badge_id INT) + RETURNS TABLE (record INT, value NUMERIC) AS $f$ +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + ANALYZE precalc_copy_filter_bib_list; + + RETURN QUERY + SELECT f.id::INT AS bib, + COUNT(f.copy)::NUMERIC + FROM precalc_copy_filter_bib_list f + JOIN asset.copy cp ON (f.copy = cp.id) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1; + +END; +$f$ LANGUAGE PLPGSQL STRICT; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.data.copy-count-badge.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.copy-count-badge.sql new file mode 100644 index 0000000000..84d36da040 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.data.copy-count-badge.sql @@ -0,0 +1,34 @@ +BEGIN; + +CREATE OR REPLACE FUNCTION rating.copy_count(badge_id INT) + RETURNS TABLE (record INT, value NUMERIC) AS $f$ +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + ANALYZE precalc_copy_filter_bib_list; + + RETURN QUERY + SELECT f.id::INT AS bib, + COUNT(f.copy)::NUMERIC + FROM precalc_copy_filter_bib_list f + JOIN asset.copy cp ON (f.copy = cp.id) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1; + +END; +$f$ LANGUAGE PLPGSQL STRICT; + +INSERT INTO rating.popularity_parameter (name, func, require_percentile) VALUES ('Copy Count', 'rating.copy_count', TRUE); + +COMMIT; + -- 2.43.2