Disable age hold protection when item has sat on the shelf for a certain amount of time.

allow-larl-holds.sql

\pset format html
\echo <h1>Allow LARL holds on NWRL age protected items</h1>

\echo <h2>NWRL Items that could fill LARL holds</h2>

begin;

-- Create temp table to store NWRL copies that are canidates for removing age hold protection.
create temporary table nwrl_new
on commit drop
as
-- NWRL age protected items that have sat on the shelf for 21 days

with active_larl_holds as ( -- CTE with list of all LARL holds by title
select count(rhrr.id) hold_count,bib_record from reporter.hold_request_record rhrr
    join action.hold_request ahr on ahr.id=rhrr.id
    join actor.usr au on au.id=ahr.usr
    LEFT JOIN actor.usr_standing_penalty ausp
        ON ( au.id = ausp.usr AND ( ausp.stop_date IS NULL OR ausp.stop_date > NOW() ) )
    LEFT JOIN config.standing_penalty csp
        ON ( csp.id = ausp.standing_penalty AND csp.block_list LIKE '%CAPTURE%' )

where
  ahr.capture_time is null
  and ahr.cancel_time is null
  and ahr.fulfillment_time is null
  and not ahr.frozen
  and ahr.pickup_lib in (SELECT id FROM actor.org_unit_descendants(101)) -- LARL Org Unit id, change for your organization
  AND csp.id IS NULL
group by rhrr.bib_record
order by count(rhrr.id)
)

select
  aou.shortname, rssr.id bib_id, alh.hold_count LARL_Hold_Count, acp.barcode, acp.circ_modifier
  ,rssr.title, rssr.author, acp.active_date, acp.status_changed_time
  , age(now(),acp.active_date) "time since active", age(now(),acp.status_changed_time) "Time on shelf"
  , acp.id copy_id, acp.age_protect, acp.floating
  , cps.*
  --, acp.*
from asset.copy acp
join asset.call_number acn on acn.id=acp.call_number
join actor.org_unit aou on aou.id=acp.circ_lib
join reporter.super_simple_record rssr on rssr.id=acn.record
join active_larl_holds alh on alh.bib_record=acn.record
left outer join reporter.hold_request_record rhrr on rhrr.bib_record=acn.record
left outer join action.hold_request ahr on ahr.id=rhrr.id
--left outer join (select

left outer join (
select
  x.record
  , sum(case when (x.circ_lib in (select id from actor.org_unit_descendants(101))) then 1 else 0 end) as "LARL Copies"
  , sum(case when (x.circ_lib in (select id from actor.org_unit_descendants(101)) and x.location='Express') then 1 else 0 end) as "LARL Express Copies"
  , sum(case when (x.circ_lib in (select id from actor.org_unit_descendants(127))) then 1 else 0 end) as "NWRL Copies"
  , string_agg(x.shortname||' - '||x.copies, ', ' order by x.copies desc) as "Summary"
  , string_agg( (case when x.circ_lib in (select id from actor.org_unit_descendants(101)) then (x.shortname||' - '||x.copies) end) , ', ' order by x.copies desc) as "LARL Summary"
  , string_agg( (case when x.circ_lib in (select id from actor.org_unit_descendants(101)) and x.location='Express' then (x.shortname||' - '||x.copies) end) , ', ' order by x.copies desc) as "LARL Express Summary"
  , string_agg( (case when x.circ_lib in (select id from actor.org_unit_descendants(127)) then (x.shortname||' - '||x.copies) end) , ', ' order by x.copies desc) as "NWRL Summary"

from
(select x.*, aou.shortname
from
(select acn.record, acl.name as location
 ,coalesce( -- if item is checked out/lost then use the checkout lib
  (select ac.circ_lib
        from action.circulation ac
        where
           acp.id=ac.target_copy
                and ac.xact_finish is null
                and ac.checkin_scan_time is null
                and ac.circ_lib in (select id from actor.org_unit_descendants(101)) -- LARL org unit
        order by ac.xact_start desc
        limit 1
 ), acp.circ_lib ) circ_lib
 , count(acp.id) copies
from asset.copy acp
join asset.call_number acn on acn.id=acp.call_number
join asset.copy_location acl on acl.id=acp.location
where
not acp.deleted
and acp.status in (0,1,3,6,7,8,103)

group by 1,2,3) x
join actor.org_unit aou on aou.id=x.circ_lib
) x
group by 1
) cps on cps.record=rssr.id

where
acp.age_protect is not null
and acp.status=0
and acp.status_changed_time <= now()-'14 days'::interval -- On shelf for at least 14 days
and ahr.fulfillment_time is null
and ahr.cancel_time is null
and not ahr.frozen
and ahr.capture_time is null
and acp.active_date <= now()-'28 days'::interval -- At least 28 days from being active
and (ahr.request_time < now()-'1 week'::interval -- holds have to be at least 1 week old or there can be no LARL copies
     or cps."LARL Copies" = 0)
-- Available LARL copies

-- Any NWRL holds

group by 1,2,3,4,5,6,7,8,9,12,13,14,15,16,17,18,19,20,21,22
order by 2,1
;

UPDATE
asset.copy acp
set age_protect=null
from nwrl_new nn
where
acp.id=nn.copy_id
and nn.shortname='THIEF_RIVER_FALLS' -- Limit to one NWRL location for testing - remove this for production

returning acp.barcode
;

-- Change the previous check time for the oldest holds that match the available NWRL items, to try and force them
--  to get retarged faster
--list of holds that match the number of NWRL items available
with holds as (select id hold_id, request_time, target from (
select *, row_number() over (partition by bib_id order by ahr.request_time) as rownum
from action.hold_request ahr
join (select bib_id, count(bib_id)
 from nwrl_new ltnw
 group by 1 )ltnw on ltnw.bib_id=ahr.target
where
ahr.hold_type='T'
and not ahr.frozen
and ahr.capture_time is null
and ahr.fulfillment_time is null
and ahr.cancel_time is null
and ahr.pickup_lib in (select id from actor.org_unit_descendants(101)) -- Limit to LARL pickup locations
and ahr.current_copy is null
) a
where rownum <= count
order by target
)
-- trigger hold targeting immediately
 -- set prev_check_time to 24 hours ago
update action.hold_request ahr
set prev_check_time=now()-'1 day'::interval
from holds h
where
h.hold_id=ahr.id
returning ahr.id, ahr.request_time, ahr.target, ahr.pickup_lib, ahr.usr
;

\echo <h2>NWRL new items that can/are filling LARL holds.</h2>
-- NWRL new items that are filling LARL holds

select aou.shortname, ccs.name, acp.barcode, acp.circ_modifier, acp.active_date, rssr.title, rssr.author

from asset.copy acp
join asset.copy_location acl on acl.id=acp.location
join config.copy_status ccs on ccs.id=acp.status
join actor.org_unit aou on aou.id=acp.circ_lib
join asset.call_number acn on acn.id=acp.call_number
join reporter.super_simple_record rssr on rssr.id=acn.record

where
acl.name='New'
and acp.circ_lib in (select id from actor.org_unit_descendants(127))
and acp.age_protect is null
;

rollback; --Change to commit once you are happy with the results