Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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
- */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement