Extend Reporter - Purchase Alerts

-- for /openils/var/web/reports/fm_IDL.xml & /openils/conf

        <class id="erpa" controller="openils.cstore" oils_obj:fieldmapper="extend_reporter::purchase_alerts" oils_persist:tablename="extend_reporter.purchase_alerts" reporter:core="true" reporter:label="Purchase Alerts">
                <fields>
                        <field reporter:label="Bib Record" name="bib_id" reporter:datatype="link"/>
                        <field reporter:label="Library System" name="library_id" reporter:datatype="link"/>
                        <field reporter:label="Library" name="library" reporter:datatype="text"/>
                        <field reporter:label="Title" name="title" reporter:datatype="text"/>
                        <field reporter:label="Author" name="author" reporter:datatype="text"/>
                        <field reporter:label="ISBNs" name="isbn" reporter:datatype="text"/>
                        <field reporter:label="Publisher" name="publisher" reporter:datatype="text"/>
                        <field reporter:label="Copies Currently Owned" name="copies" reporter:datatype="int"/>
                        <field reporter:label="Holds on Bib Record" name="holds_count" reporter:datatype="int"/>
                        <field reporter:label="Holds Count Per Copy" name="holds_count_per_copy" reporter:datatype="int"/>
                </fields>
                <links>
                        <link field="bib_id" reltype="has_a" key="id" map="" class="bre"/>
                        <link field="library_id" reltype="has_a" key="id" map="" class="aou"/>
                </links>
        </class>

drop view if exists extend_reporter.purchase_alerts;

create view extend_reporter.purchase_alerts as
SELECT bd.bib_id, parent.id AS library_id, parent.shortname AS library, bd.title, bd.author, bd.isbn, bd.publisher,
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, rmsr.publisher,
        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, 6
        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, 6, 7
;

select * from extend_reporter.purchase_alerts where library = 'YCL' order by 3,4;


/*

future development notes

decided to not include metarecord holds because they are too small a percetage and potentially too confusing to users but they could be linked through metabib.metarecord.id = action.hold_request.target where hold_type = 'M'

if we wanted material types we could do something like

join metabib.record_attr_vector_list vl on vl.source = bd.bib_id
join metabib.full_attr_id_map atmap on atmap.id = any(vl.vlist)

to access the item_type and item_form but without mapping it the search_type and icon_type might actually be more useful to users without doing additional work

*/