<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"/>
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)
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">
</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"/>
projects / evergreen-equinox.git / commitdiff