Extend Reporter - Weeding

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

        <class id="erw" controller="openils.cstore" oils_obj:fieldmapper="extend_reporter::weeding" oils_persist:tablename="extend_reporter.weeding" reporter:core="true" reporter:label="Weeding">
                <fields>
                        <field reporter:label="Number of Circs" name="num_of_circs" reporter:datatype="int"/>
                        <field reporter:label="Barcode" name="barcode" reporter:datatype="text"/>
                        <field reporter:label="Shelving Location" name="shelving_location" reporter:datatype="text"/>
                        <field reporter:label="Call Number" name="call_number" reporter:datatype="text"/>
                        <field reporter:label="Title" name="title" reporter:datatype="text"/>
                        <field reporter:label="Library" name="library" reporter:datatype="text"/>
                        <field reporter:label="Last Circulation" name="last_circulation" reporter:datatype="timestamp"/>
                        <field reporter:label="Item Create Date" name="create_date" reporter:datatype="timestamp"/>
                </fields>
        </class>


drop table if exists extend_reporter.weeding;

create table extend_reporter.weeding as
SELECT COUNT(acirc.id) as "num_of_circs", ac.barcode AS "barcode", acl.NAME AS "shelving_location",
acn.label AS "call_number", msr.title, child.shortname as library, MAX(acirc.xact_start) AS "last_circulation", ac.create_date
FROM asset.COPY ac
JOIN asset.copy_location acl ON ac.LOCATION = acl.id
LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
JOIN asset.call_number acn ON acn.id = ac.call_number
JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
JOIN actor.org_unit child ON child.id = ac.circ_lib
WHERE acirc.xact_start IS NULL
AND date(ac.create_date) <= NOW() - INTERVAL '3 years'
AND ac.deleted = FALSE
AND ac.status = 0
AND acl.circulate = TRUE
GROUP BY 2, 3, 4, 5, 6, 8
UNION ALL
SELECT COUNT(acirc.id) as "num_of_circs", ac.barcode AS "barcode", acl.NAME AS "shelving_location",
acn.label AS "call_number", msr.title, child.shortname as library, MAX(acirc.xact_start) AS "last_circulation", ac.create_date
FROM asset.COPY ac
JOIN asset.copy_location acl ON ac.LOCATION = acl.id
LEFT JOIN ACTION.circulation acirc ON acirc.target_copy = ac.id
JOIN asset.call_number acn ON acn.id = ac.call_number
JOIN reporter.materialized_simple_record msr ON msr.id = acn.record
JOIN actor.org_unit child ON child.id = ac.circ_lib
WHERE date(ac.create_date) <= NOW() - INTERVAL '3 years'
AND ac.deleted = FALSE
AND ac.status = 0
AND acl.circulate = TRUE
GROUP BY 2, 3, 4, 5, 6, 8
HAVING MAX(acirc.xact_start) < NOW() - INTERVAL '3 years'
;

select * from extend_reporter.weeding where library ilike 'YCL-RH' and shelving_location = 'Adult Fiction' and
date(create_date) < now() - interval '5 years' and last_circulation is null;