Purchase Alert Report

-- originally based on scripts by Dan Scott

-- warning, one of the weaknesses of the count for the system script is that it does not count low branches
-- for example:
-- if YCL has high counts on Hurricane Sisters with a total of 15 copies at 6 branches and 58 holds
-- the report may only show 12 copies and 56 holds
-- this is because one branch has 1 copy and less than 4 holds and the second branch has 2 copies and less than 8 holds
-- also, it does not track metarecord holds at this time

-- system report

select bd.bib_id, parent.shortname as "org unit", bd.title, bd.author, bd.isbn, sum(bd.copies) as copies, sum(bd.holds_count) as "holds count",
round(sum(bd.holds_count)/sum(bd.copies)) AS "holds count per copy"
 from (
    SELECT all_holds.bib_id, aou.id as org, rmsr.title, rmsr.author, rmsr.isbn,
    COUNT(DISTINCT ac.id) AS "copies", COUNT(all_holds.bib_id)/COUNT(DISTINCT ac.id) AS "holds_count"
    FROM ((
    SELECT hold_request.target, hold_request.pickup_lib
    FROM ACTION.hold_request
    WHERE hold_request.hold_type = 'T'::TEXT
    AND hold_request.fulfillment_time IS NULL
    AND hold_request.cancel_time IS NULL
    UNION ALL
    SELECT bre.id, ahr.pickup_lib
    FROM ACTION.hold_request ahr
    JOIN asset.call_number acn ON ahr.target = acn.id
    JOIN biblio.record_entry bre ON acn.record = bre.id
    WHERE ahr.hold_type = 'V'::TEXT
    AND ahr.fulfillment_time IS NULL
    AND ahr.cancel_time IS NULL)
    UNION ALL
    SELECT bre.id, ahr.pickup_lib
    FROM ACTION.hold_request ahr
    JOIN asset.COPY ac ON ahr.target = ac.id
    JOIN asset.call_number acn ON ac.call_number = acn.id
    JOIN biblio.record_entry bre ON acn.record = bre.id
    WHERE ahr.hold_type = 'C'::TEXT
    AND ahr.fulfillment_time IS NULL
    AND ahr.cancel_time IS NULL)
    all_holds(bib_id, pickup_lib)
    JOIN reporter.materialized_simple_record rmsr ON rmsr.id = all_holds.bib_id
    JOIN actor.org_unit aou ON aou.id = all_holds.pickup_lib
    JOIN asset.call_number acn ON acn.record = all_holds.bib_id
    JOIN asset.COPY ac ON ac.call_number = acn.id
    WHERE ac.circ_lib = all_holds.pickup_lib
    GROUP BY 1, 2, 3, 4, 5
    HAVING COUNT(all_holds.bib_id)/COUNT(DISTINCT ac.id) > 3
) bd
join actor.org_unit child on child.id = bd.org
join actor.org_unit parent on parent.id = child.parent_ou
GROUP BY 1, 2, 3, 4, 5
ORDER BY 2 ASC, 5 DESC
;


SELECT all_holds.bib_id, aou.shortname AS "org unit", rmsr.title, rmsr.author, rmsr.isbn,
COUNT(DISTINCT ac.id) AS "copies", COUNT(all_holds.bib_id)/COUNT(DISTINCT ac.id) AS "holds count",
(COUNT(all_holds.bib_id)/COUNT(DISTINCT ac.id))/COUNT(DISTINCT ac.id) AS "holds count per copy"
FROM ((
SELECT hold_request.target, hold_request.pickup_lib
FROM ACTION.hold_request
WHERE hold_request.hold_type = 'T'::TEXT
AND hold_request.fulfillment_time IS NULL
AND hold_request.cancel_time IS NULL
UNION ALL
SELECT bre.id, ahr.pickup_lib
FROM ACTION.hold_request ahr
JOIN asset.call_number acn ON ahr.target = acn.id
JOIN biblio.record_entry bre ON acn.record = bre.id
WHERE ahr.hold_type = 'V'::TEXT
AND ahr.fulfillment_time IS NULL
AND ahr.cancel_time IS NULL)
UNION ALL
SELECT bre.id, ahr.pickup_lib
FROM ACTION.hold_request ahr
JOIN asset.COPY ac ON ahr.target = ac.id
JOIN asset.call_number acn ON ac.call_number = acn.id
JOIN biblio.record_entry bre ON acn.record = bre.id
WHERE ahr.hold_type = 'C'::TEXT
AND ahr.fulfillment_time IS NULL
AND ahr.cancel_time IS NULL)
all_holds(bib_id, pickup_lib)
JOIN reporter.materialized_simple_record rmsr ON rmsr.id = all_holds.bib_id
JOIN actor.org_unit aou ON aou.id = all_holds.pickup_lib
JOIN asset.call_number acn ON acn.record = all_holds.bib_id
JOIN asset.COPY ac ON ac.call_number = acn.id
WHERE ac.circ_lib = all_holds.pickup_lib
GROUP BY aou.shortname, all_holds.bib_id, rmsr.id, rmsr.title, rmsr.author, rmsr.isbn
HAVING COUNT(all_holds.bib_id)/COUNT(DISTINCT ac.id) > 3
ORDER BY aou.shortname, COUNT(all_holds.bib_id) DESC
;