From 5ed2320b3f78a4f537f00a384ddb6873e48b6dfa Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 14 Feb 2017 15:59:53 -0500 Subject: [PATCH] LP#1664715: Hold ratio template ignores some copies The relatively new report template "Hold/Copy Ratio per Bib and Pickup Library (and Descendants)" only includes copies that are at locations that are actually pickup libraries of holds. For instance, if you have two branches (A and B) in a system, each with 10 copies attached to a bib, but a hold only at branch A, the ratio calculated at the system level sees 10 copies rather than twenty. This has the effect of inflating the hold side of the hold/copy ratio. The SQL definition is adjusted here to count copies across the entire branch of the org tree per bib, rather than through a join that restricts to those branches that are pickup libraries for a relevant hold. Upgrade note: Report templates using the Hold/Copy Ratio at Pickup Library and Descendants data source will need to be re-created to benefit from this bug fix. Signed-off-by: Mike Rylander Signed-off-by: Josh Stompro Signed-off-by: Kathy Lussier --- Open-ILS/examples/fm_IDL.xml | 40 ++++++++++++++++++------------------ 1 file changed, 20 insertions(+), 20 deletions(-) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index fca38b56b2..f84734a7df 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -10756,27 +10756,27 @@ SELECT usr, WITH counts_at_ou AS ( - SELECT bib_record, pickup_lib, count(DISTINCT ahr.id) AS holds_at_pickup_library, - COALESCE(count(DISTINCT ac.id),0) as copy_count_at_pickup_library - FROM action.hold_request ahr - JOIN reporter.hold_request_record rhrr USING (id) - LEFT JOIN action.hold_copy_map ahcm ON (ahr.id = ahcm.hold) - LEFT JOIN asset.copy ac ON (ahcm.target_copy = ac.id AND ahr.pickup_lib = ac.circ_lib) - WHERE ahr.cancel_time IS NULL - AND ahr.fulfillment_time IS NULL - GROUP BY bib_record, pickup_lib + SELECT rhrr.bib_record AS id, + aou.id AS pickup_lib_or_desc, + COUNT(DISTINCT ahr.id) AS holds_at_or_below, + COALESCE(COUNT(DISTINCT ac.id),0) AS copy_count_at_or_below + FROM actor.org_unit aou + JOIN actor.org_unit_type aout ON (aou.ou_type = aout.id), + action.hold_request ahr + JOIN reporter.hold_request_record rhrr USING (id) + LEFT JOIN action.hold_copy_map ahcm ON (ahr.id = ahcm.hold) + LEFT JOIN asset.copy ac ON (ahcm.target_copy = ac.id) + WHERE ahr.cancel_time IS NULL AND ahr.fulfillment_time IS NULL + AND ac.circ_lib IN (SELECT id FROM actor.org_unit_descendants(aou.id)) + AND (actor.org_unit_ancestor_at_depth(ahr.pickup_lib,aout.depth)).id = (actor.org_unit_ancestor_at_depth(ac.circ_lib,aout.depth)).id + GROUP BY 1, 2 ) - SELECT *, - CASE WHEN copy_count_at_or_below = 0 THEN 'Infinity'::FLOAT ELSE holds_at_or_below::FLOAT/copy_count_at_or_below END AS hold_copy_ratio_at_or_below_ou, - CASE WHEN copy_count_everywhere = 0 THEN 'Infinity'::FLOAT ELSE holds_everywhere::FLOAT/copy_count_everywhere END AS everywhere_ratio - FROM - (SELECT bib_record AS id, aou.id AS pickup_lib_or_desc, SUM(holds_at_pickup_library) AS holds_at_or_below, SUM(copy_count_at_pickup_library) AS copy_count_at_or_below - FROM actor.org_unit aou - JOIN counts_at_ou cao ON (cao.pickup_lib IN (SELECT id FROM actor.org_unit_descendants(aou.id))) - GROUP BY bib_record, pickup_lib_or_desc - )x - JOIN - (SELECT bib_record AS id, count(DISTINCT ahr.id) AS holds_everywhere, COALESCE(count(DISTINCT target_copy),0) as copy_count_everywhere + SELECT x.id, x.pickup_lib_or_desc, x.holds_at_or_below, x.copy_count_at_or_below, + y.holds_everywhere, y.copy_count_everywhere, + CASE WHEN copy_count_at_or_below = 0 THEN 'Infinity'::FLOAT ELSE x.holds_at_or_below::FLOAT/x.copy_count_at_or_below END AS hold_copy_ratio_at_or_below_ou, + CASE WHEN copy_count_everywhere = 0 THEN 'Infinity'::FLOAT ELSE y.holds_everywhere::FLOAT/y.copy_count_everywhere END AS everywhere_ratio + FROM counts_at_ou x + JOIN (SELECT bib_record AS id, count(DISTINCT ahr.id) AS holds_everywhere, COALESCE(count(DISTINCT target_copy),0) as copy_count_everywhere FROM action.hold_request ahr JOIN reporter.hold_request_record rhrr USING (id) -- 2.43.2