Lost Item Report

DROP TABLE IF EXISTS rogan.lost_items;
DROP TABLE IF EXISTS rogan.bought_items;
DROP TABLE IF EXISTS rogan.bought_summary;
DROP TABLE IF EXISTS rogan.lost_summary;

CREATE TABLE rogan.lost_items AS
SELECT ac.barcode, aou.shortname AS branch, parent.shortname AS SYSTEM, acn.label AS "call number",
acl.NAME AS LOCATION, ccs.NAME AS status, ac.circ_modifier,
ssr.author, ssr.title, ssr.pubdate AS "publication date", ac.COST AS "item price",
date(ac.edit_date) AS "last edit date"
FROM asset.COPY ac
JOIN config.copy_status ccs ON ccs.id = ac.status
JOIN actor.org_unit aou ON aou.id = ac.circ_lib
JOIN asset.call_number acn ON acn.id = ac.call_number
JOIN reporter.super_simple_record ssr ON ssr.id = acn.record
JOIN asset.copy_location acl ON acl.id = ac.LOCATION
JOIN actor.org_unit parent ON parent.id = aou.parent_ou
WHERE ccs.id IN (SELECT id FROM config.copy_status WHERE NAME ILIKE '%lost%')
AND ac.deleted = FALSE
AND date(ac.edit_date) >= '2014-07-01' AND date(ac.edit_date) <= '2015-06-30'
;

CREATE TABLE rogan.bought_items AS
SELECT ac.barcode, aou.shortname AS branch, parent.shortname AS SYSTEM, acn.label AS "call number",
acl.NAME AS LOCATION, ccs.NAME AS status, ac.circ_modifier,
ssr.author, ssr.title, ssr.pubdate AS "publication date", ac.COST AS "item price",
date(ac.create_date) AS "create date"
FROM asset.COPY ac
JOIN config.copy_status ccs ON ccs.id = ac.status
JOIN actor.org_unit aou ON aou.id = ac.circ_lib
JOIN asset.call_number acn ON acn.id = ac.call_number
JOIN reporter.super_simple_record ssr ON ssr.id = acn.record
JOIN asset.copy_location acl ON acl.id = ac.LOCATION
JOIN actor.org_unit parent ON parent.id = aou.parent_ou
WHERE date(ac.create_date) >= '2014-07-01' AND date(ac.create_date) <= '2015-06-30'
;

/*
SELECT * FROM rogan.lost_items
ORDER BY SYSTEM, branch, LOCATION, "call number", author, title;
*/

CREATE TABLE rogan.lost_summary AS
SELECT a.SYSTEM, a.item_count AS "total lost items", b.circ_modifier, b.item_count AS "lost items",
ROUND((cast(b.item_count AS NUMERIC)/cast(a.item_count AS NUMERIC))*100,2) AS percent
FROM (SELECT COUNT(barcode) AS item_count, SYSTEM FROM rogan.lost_items GROUP BY 2) a
LEFT JOIN (SELECT COUNT(barcode) AS item_count, SYSTEM, circ_modifier FROM rogan.lost_items GROUP BY 2, 3) b ON b.SYSTEM = a.SYSTEM
-- WHERE circ_modifier = 'VIDEO'
ORDER BY 1, 3
;

select * from rogan.lost_summary;

CREATE TABLE rogan.bought_summary AS
SELECT a.SYSTEM, a.item_count AS "total acquired items", b.circ_modifier, b.item_count AS "bought items",
ROUND((cast(b.item_count AS NUMERIC)/cast(a.item_count AS NUMERIC))*100,2) AS percent
FROM (SELECT COUNT(barcode) AS item_count, SYSTEM FROM rogan.bought_items GROUP BY 2) a
LEFT JOIN (SELECT COUNT(barcode) AS item_count, SYSTEM, circ_modifier FROM rogan.bought_items GROUP BY 2, 3) b ON b.SYSTEM = a.SYSTEM
-- WHERE circ_modifier = 'VIDEO'
ORDER BY 1, 3
;

select * from rogan.bought_summary;

SELECT l.SYSTEM, l.circ_modifier, l."total lost items", l.percent AS "lost percent", l."lost items" AS "lost items of circ mod",
b."total acquired items", b."bought items" AS "acquired items of circ mod", b.percent AS "acquired percent"
FROM (SELECT * FROM rogan.lost_summary) l
JOIN (SELECT * FROM rogan.bought_summary) b ON b.SYSTEM = l.SYSTEM
WHERE b.circ_modifier = l.circ_modifier AND b.circ_modifier = 'AUDIO'
;

/*

Evergreen doesn't track bills per item but per the account. So, I know that a bill is for a lost item because
it is the billing type but I can't see which bill is for what lost item.  The closest I can do is I could list
lost items associated with an account that also owes lost item bills.  If anything has changed that item status
though it wouldn't sync up.

For this I'm also pulling the lost materials only.  If libraries charge a lost item processing fee I could also
include that.

I've included the barcodes so the accounts can be easily looked up that way but because patrons can have multiple
valid barcodes I've put them into an array because otherwise it will duplicate entries for them.

-- 'Lost Materials Processing Fee'

*/

select array_agg(acard.barcode) as barcode, au.first_given_name, au.family_name, aou.shortname as library,
sum(x.total_owed) as "total owed", sum(x.total_paid) as "total paid", sum(x.balance_owed) as "balance owed"
from money.billing b
join money.billable_xact_summary x on x.id = b.xact
join actor.usr au on au.id = x.usr
join actor.org_unit aou on aou.id = au.home_ou
join actor.card acard on acard.usr = au.id
where b.billing_type in ('Lost Materials') and aou.shortname ilike 'bcl%' and x.balance_owed > 0
and acard.active = true
group by au.id, au.first_given_name, au.family_name, aou.shortname
order by aou.shortname, au.family_name, au.first_given_name
;

/*

Now an overview of all libraries by last fiscal year but this time including

*/

select c.library, c.lost_items, b.total_owed, b.total_paid, b.balance_owed,
round(b.total_paid/c.lost_items,2) as "amount recovered per item"
from ( SELECT COUNT(barcode) AS lost_items, system as library FROM rogan.lost_items GROUP BY 2 ) c
join (
    select aou2.shortname as library,
    sum(x.total_owed) as total_owed, sum(x.total_paid) as total_paid, sum(x.balance_owed) as balance_owed
    from money.billing b
    join money.billable_xact_summary x on x.id = b.xact
    join actor.usr au on au.id = x.usr
    join actor.org_unit aou on aou.id = au.home_ou
    join actor.card acard on acard.usr = au.id
    join actor.org_unit aou2 on aou2.id = aou.parent_ou
    where b.billing_type in ('Lost Materials')and x.balance_owed > 0
    and acard.active = true and date(x.xact_start) >= '2014-07-01' and date(x.xact_start) <= '2015-06-30'
    group by aou2.shortname ) b on b.library = c.library
order by library asc
;