From daa68601684d8e783be2f06d6d9cbc09f65edc47 Mon Sep 17 00:00:00 2001
From: Jason Boyer <JBoyer@EquinoxInitiative.org>
Date: Mon, 19 Apr 2021 09:37:27 -0400
Subject: [PATCH] LP1925028: Make Hold/Copy Ratio Reports Consistent, Add a By
 Home Library Version

Use ahcm for all hold/copy ratio reporting sources so the counts are more
consistent, ignore metarecord hold copies (by default) and the copies from
frozen or captured holds.

Also a Hold/Copy Ratio per Bib and Home Library source is added.

Sponsored-by: Westchester Library System

Signed-off-by: Jason Boyer <JBoyer@equinoxinitiative.org>
Signed-off-by: Rogan Hamby <rogan.hamby@gmail.com>
Signed-off-by: Chris Sharp <csharp@georgialibraries.org>
---
 Open-ILS/examples/fm_IDL.xml | 266 ++++++++++++++++++++++++++++++++-----------
 1 file changed, 202 insertions(+), 64 deletions(-)

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 227619a60f..e03ebbbf61 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -12440,52 +12440,37 @@ SELECT  usr,
 
 	<class id="rhcrpb" controller="open-ils.cstore open-ils.pcrud open-ils.reporter-store" oils_obj:fieldmapper="reporter::hold_copy_ratio_per_bib" oils_persist:readonly="true" reporter:core="true" reporter:label="Hold/Copy Ratio per Bib">
 		<oils_persist:source_definition><![CDATA[
-
-            -- -- If we uncomment the RIGHT JOIN against biblio.record_entry, then we'll get a row for every non-deleted bib, whether it has active holds or not.
-            -- -- If we expect to use pcrud to query against specific bibs, we probably want to do this.  However, if we're using this to populate a report, we
-            -- -- may not.
-            -- SELECT
-            --     bre.id AS bib_id,
-            --     COALESCE( z.copy_count, 0 ) AS copy_count,
-            --     COALESCE( z.hold_count, 0 ) AS hold_count,
-            --     COALESCE( z.copy_hold_ratio, 0 ) AS hold_copy_ratio
-            -- FROM (
                 SELECT
-                    y.bre AS id,
-                    COALESCE( x.copy_count, 0 ) AS copy_count,
-                    y.hold_count AS hold_count,
-                    (y.hold_count::REAL / (CASE WHEN x.copy_count = 0 OR x.copy_count IS NULL THEN 0.1 ELSE x.copy_count::REAL END)) AS hold_copy_ratio
-                FROM (
-                        SELECT
-                            (SELECT bib_record FROM reporter.hold_request_record r WHERE r.id = h.id LIMIT 1) AS bre,
-                            COUNT(*) AS hold_count
-                        FROM action.hold_request h
+                    x.id,
+                    COALESCE( y.copy_count, 0 ) AS copy_count,
+                    x.hold_count AS hold_count,
+                    CASE WHEN y.copy_count = 0 THEN 'Infinity'::FLOAT ELSE x.hold_count::FLOAT/y.copy_count::FLOAT END AS hold_copy_ratio
+                FROM
+                    (SELECT bib_record as id, count(DISTINCT ahr.id) AS hold_count
+                        FROM
+                            action.hold_request ahr
+                            JOIN reporter.hold_request_record rhrr USING (id)
                         WHERE
-                            cancel_time IS NULL
-                            AND fulfillment_time IS NULL
-                            -- AND NOT frozen  -- a frozen hold is still a desired hold, eh?
-                        GROUP BY 1
-                    )y LEFT JOIN (
-                        SELECT 
-                            (SELECT id
-                                FROM biblio.record_entry 
-                                WHERE id = (SELECT record FROM asset.call_number WHERE id = call_number and deleted is false)
-                            ) AS bre, 
-                            COUNT(*) AS copy_count
-                        FROM asset.copy
-                            JOIN asset.copy_location loc ON (copy.location = loc.id AND loc.holdable)
-                        WHERE copy.holdable 
-                            AND NOT copy.deleted 
-                            AND copy.status IN ( SELECT id FROM config.copy_status WHERE holdable ) 
-                        GROUP BY 1
-                    )x ON x.bre = y.bre
-                -- )z RIGHT JOIN (
-                --     SELECT id
-                --     FROM biblio.record_entry
-                --     WHERE NOT deleted
-                -- )bre ON (z.bib_id = bre.id)
-                
-
+                            ahr.cancel_time IS NULL
+                            AND ahr.fulfillment_time IS NULL
+                        GROUP BY bib_record
+                    )x
+                    JOIN
+                    (SELECT bib_record as id, COALESCE(count(DISTINCT target_copy),0) as copy_count
+                        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
+                            AND ahr.capture_time IS NULL
+                            AND ahr.frozen IS FALSE
+                            -- Comment out the next line to count copies included from other bibs by metarecord holds
+                            AND ahr.hold_type != 'M'
+                           GROUP BY bib_record
+                    )y
+                    USING (id)
 		]]></oils_persist:source_definition>
 		<fields oils_persist:primary="id" oils_persist:sequence="biblio.record_entry">
 			<field reporter:label="Record ID" name="id" reporter:datatype="id"/>
@@ -12509,7 +12494,7 @@ SELECT  usr,
                 CASE WHEN copy_count_at_pickup_library = 0 THEN 'Infinity'::FLOAT ELSE holds_at_pickup_library::FLOAT/copy_count_at_pickup_library END AS pickup_library_ratio,
                 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, pickup_lib, count(DISTINCT ahr.id) AS holds_at_pickup_library, COALESCE(count(DISTINCT ac.id),0) as copy_count_at_pickup_library
+                (SELECT bib_record as id, pickup_lib, count(DISTINCT ahr.id) AS holds_at_pickup_library
                     FROM
                         action.hold_request ahr
                         JOIN reporter.hold_request_record rhrr USING (id)
@@ -12521,16 +12506,48 @@ SELECT  usr,
                     GROUP BY bib_record, pickup_lib
                 )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 bib_record as id, pickup_lib, 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
+                        AND ahr.capture_time IS NULL
+                        AND ahr.frozen IS FALSE
+                        -- Comment out the next line to count copies included from other bibs by metarecord holds
+                        AND ahr.hold_type != 'M'
+                    GROUP BY bib_record, pickup_lib
                 )y
+                USING(id,pickup_lib)
+                JOIN
+                (SELECT bib_record as id, count(DISTINCT ahr.id) AS holds_everywhere
+                    FROM
+                        action.hold_request ahr
+                        JOIN reporter.hold_request_record rhrr USING (id)
+                    WHERE
+                        ahr.cancel_time IS NULL
+                        AND ahr.fulfillment_time IS NULL
+                    GROUP BY bib_record
+                )z
+                USING (id)
+                JOIN
+                (SELECT bib_record as id, 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
+                        AND ahr.capture_time IS NULL
+                        AND ahr.frozen IS FALSE
+                        -- Comment out the next line to count copies included from other bibs by metarecord holds
+                        AND ahr.hold_type != 'M'
+                        GROUP BY bib_record
+                )u
                 USING (id)
 		]]></oils_persist:source_definition>
 		<fields oils_persist:primary="id" oils_persist:sequence="biblio.record_entry">
@@ -12554,40 +12571,161 @@ SELECT  usr,
 		</permacrud>
 	</class>
 
-	<class id="rhcrpbapd" controller="open-ils.cstore open-ils.pcrud open-ils.reporter-store" oils_obj:fieldmapper="reporter::hold_copy_ratio_per_bib_and_pickup_desc" oils_persist:readonly="true" reporter:label="Hold/Copy Ratio per Bib and Pickup Library (and Descendants) ">
+	<class id="rhcrpbah" controller="open-ils.cstore open-ils.pcrud open-ils.reporter-store" oils_obj:fieldmapper="reporter::hold_copy_ratio_per_bib_and_home" oils_persist:readonly="true" reporter:label="Hold/Copy Ratio per Bib and Home Library">
 		<oils_persist:source_definition><![CDATA[
-            WITH counts_at_ou AS (
-                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),
+            SELECT *,
+                CASE WHEN copy_count_at_home_library = 0 THEN 'Infinity'::FLOAT ELSE holds_at_home_library::FLOAT/copy_count_at_home_library END AS home_library_ratio,
+                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, home_ou, count(DISTINCT ahr.id) AS holds_at_home_library
+                    FROM
+                        action.hold_request ahr
+                        JOIN actor.usr au ON (ahr.usr = au.id)
+                        JOIN reporter.hold_request_record rhrr ON (rhrr.id = ahr.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 au.home_ou = ac.circ_lib)
+                    WHERE
+                        ahr.cancel_time IS NULL
+                        AND ahr.fulfillment_time IS NULL
+                    GROUP BY bib_record, home_ou
+                )x
+                JOIN
+                (SELECT bib_record as id, home_ou, COALESCE(count(DISTINCT ac.id),0) as copy_count_at_home_library
+                    FROM
+                        action.hold_request ahr
+                        JOIN actor.usr au ON (ahr.usr = au.id)
+                        JOIN reporter.hold_request_record rhrr ON (rhrr.id = ahr.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 au.home_ou = ac.circ_lib)
+                    WHERE
+                        ahr.cancel_time IS NULL
+                        AND ahr.fulfillment_time IS NULL
+                        AND ahr.capture_time IS NULL
+                        AND ahr.frozen IS FALSE
+                        -- Comment out the next line to count copies included from other bibs by metarecord holds
+                        AND ahr.hold_type != 'M'
+                    GROUP BY bib_record, home_ou
+                )y
+                USING (id,home_ou)
+                JOIN
+                (SELECT bib_record as id, count(DISTINCT ahr.id) AS holds_everywhere
+                    FROM
+                        action.hold_request ahr
+                        JOIN reporter.hold_request_record rhrr USING (id)
+                    WHERE
+                        ahr.cancel_time IS NULL
+                        AND ahr.fulfillment_time IS NULL
+                    GROUP BY bib_record
+                )z
+                USING (id)
+                JOIN
+                (SELECT bib_record as id, 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)
-                        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
+                    WHERE
+                        ahr.cancel_time IS NULL
+                        AND ahr.fulfillment_time IS NULL
+                        AND ahr.capture_time IS NULL
+                        AND ahr.frozen IS FALSE
+                        -- Comment out the next line to count copies included from other bibs by metarecord holds
+                        AND ahr.hold_type != 'M'
+                    GROUP BY bib_record
+                )u
+                USING (id)
+	]]></oils_persist:source_definition>
+	<fields oils_persist:primary="id" oils_persist:sequence="biblio.record_entry">
+		<field reporter:label="Record ID" name="id" reporter:datatype="link"/>
+		<field reporter:label="User Home Library" name="home_ou" reporter:datatype="org_unit"/>
+		<field reporter:label="Active Holds at Home Library" name="holds_at_home_library" reporter:datatype="int"/>
+		<field reporter:label="Holdable Copy Count at Home Library" name="copy_count_at_home_library" reporter:datatype="int"/>
+		<field reporter:label="Active Holds Everywhere" name="holds_everywhere" reporter:datatype="int"/>
+		<field reporter:label="Holdable Copy Count Everywhere" name="copy_count_everywhere" reporter:datatype="int"/>
+		<field reporter:label="Hold/Copy Ratio at Home Library" name="home_library_ratio" reporter:datatype="float"/>
+		<field reporter:label="Hold/Copy Ratio Everywhere" name="everywhere_ratio" reporter:datatype="float"/>
+	</fields>
+	<links>
+		<link field="id" reltype="has_a" key="id" map="" class="bre"/>
+		<link field="home_ou" reltype="has_a" key="id" map="" class="aou"/>
+	</links>
+	<permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+		<actions>
+			<retrieve/>
+		</actions>
+	</permacrud>
+</class>
+
+	<class id="rhcrpbapd" controller="open-ils.cstore open-ils.pcrud open-ils.reporter-store" oils_obj:fieldmapper="reporter::hold_copy_ratio_per_bib_and_pickup_desc" oils_persist:readonly="true" reporter:label="Hold/Copy Ratio per Bib and Pickup Library (and Descendants) ">
+		<oils_persist:source_definition><![CDATA[
+                WITH counts_at_ou AS (
+                SELECT a.id, a.pickup_lib_or_desc, a.holds_at_or_below, b.copy_count_at_or_below
+                FROM
+                    (SELECT rhrr.bib_record AS id,
+                            aou.id AS pickup_lib_or_desc,
+                            COUNT(DISTINCT ahr.id) AS holds_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
+                ) a
+                JOIN
+                    (SELECT rhrr.bib_record AS id,
+                            aou.id AS pickup_lib_or_desc,
+                            COALESCE(COUNT(DISTINCT ahcm.target_copy),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 ahr.capture_time IS NULL AND NOT ahr.frozen
+                            -- Comment out the next line to count copies included from other bibs by metarecord holds
+                            AND ahr.hold_type != 'M'
+                            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
+                ) b
+                USING (id,pickup_lib_or_desc)
             )
             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,
+                   y.holds_everywhere, z.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
+                CASE WHEN copy_count_everywhere = 0 THEN 'Infinity'::FLOAT ELSE y.holds_everywhere::FLOAT/z.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
+                JOIN
+                (SELECT bib_record as id, count(DISTINCT ahr.id) AS holds_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)
+                JOIN
+                (SELECT bib_record as id, 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
+                        AND ahr.capture_time IS NULL
+                        AND ahr.frozen IS FALSE
+                        -- Comment out the next line to count copies included from other bibs by metarecord holds
+                        AND ahr.hold_type != 'M'
+                        GROUP BY bib_record
+                )z
+                USING (id)
 		]]></oils_persist:source_definition>
 		<fields oils_persist:primary="id" oils_persist:sequence="biblio.record_entry">
 			<field reporter:label="Record ID" name="id" reporter:datatype="link"/>
-- 
2.11.0