From 3d762bcfbc5fec1f5bd82301cffb734bba824b76 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Mon, 19 Oct 2015 20:41:12 +0000 Subject: [PATCH] new reporting source for hold/copy ratios This patch adds a new reporting source, "Hold/Copy Ratio per Bib and Pickup Library (and Descendants)", that, for each bib that has a hold request on it (or any of its components) calculates the following: - active holds at each OU (including the OU's descendants) - holdable copies at each OU (and its descendants) - the ratio of the above two counts - counts and ratio across the entire consoritum Signed-off-by: Galen Charlton --- Open-ILS/examples/fm_IDL.xml | 56 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 56 insertions(+) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 37aab837d2..e5f7a7824c 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -10544,6 +10544,62 @@ 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 *, + 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 + 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) + WHERE + ahr.cancel_time IS NULL + AND ahr.fulfillment_time IS NULL + GROUP BY bib_record + )y + USING (id) + + + + + + + + + + + + + + + + + + + + + + SELECT -- 2.11.0