From a74077e0ad661ab239cc51f0ead553ede8732c8c Mon Sep 17 00:00:00 2001 From: Kathy Lussier Date: Mon, 31 Jul 2017 13:43:38 -0400 Subject: [PATCH] LP#1688099: Popularity parameter for the number of OUs that own a title Creates a new badge that calculates a score based on a count of libraries that own a copy of a title. The badge is based on libraries that are the copy's circulation library. The intent of the badge is to measure the breadth of ownership within a particular consortium with the hope that it doesn't generate as many 'false hits' that are seen with the similar Total Copy Counts badge. Signed-off-by: Kathy Lussier --- .../Pg/upgrade/xxxx.data.org-unit-count-badge.sql | 37 ++++++++++++++++++++++ 1 file changed, 37 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/xxxx.data.org-unit-count-badge.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.data.org-unit-count-badge.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.data.org-unit-count-badge.sql new file mode 100644 index 0000000000..16250c7fa8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.data.org-unit-count-badge.sql @@ -0,0 +1,37 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION rating.org_unit_count(badge_id INT) + RETURNS TABLE (record BIGINT, 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(DISTINCT cp.circ_lib)::NUMERIC + FROM asset.copy cp + JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy) + 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 (id, name, description, func, require_percentile) VALUES + (17,'Circulation Library Count', 'Count of distinct org units that are a circulation library for a copy on the record', 'rating.org_unit_count', TRUE); + +COMMIT; + -- 2.11.0