Add Hold Notification to Forced ILL Holds - Evergreen ILS

ILL Force Holds add Notifications.sql

-- Our ILL workflow works best when ILL staff use force holds to placed holds on incoming ILL items for our users.
-- This causes a problem becuase hold notifications are not set for those holds.
-- This script adds back in the default hold notifications for those holds.

begin;

-- Add notification to forced holds where possible
update action.hold_request ahr

set email_notify = CASE WHEN (aus.value~'email' or aus.value is null) and au.email is not null THEN true else false END,
    phone_notify = CASE WHEN (aus.value~'phone' or aus.value is null) and (au.day_phone is not null or ausp.value is not null) then coalesce(ausp.value,au.day_phone) else phone_notify END,
    sms_notify = CASE WHEN aus.value~'sms' and aussn.value is not null and aussc.value is not null THEN aussn.value ELSE sms_notify END,
    sms_carrier = CASE WHEN aus.value~'sms' and aussc.value is not null and aussn.value is not null THEN trim( both '"' from aussc.value)::integer ELSE sms_carrier END
from
asset.copy acp
join actor.usr au on true
left outer join actor.usr_setting aus on aus.name='opac.hold_notify' and au.id=aus.usr
left outer join actor.usr_setting ausp on ausp.name='opac.default_phone' and au.id=ausp.usr
left outer join actor.usr_setting aussn on aussn.name='opac.default_sms_notify' and au.id=aussn.usr
left outer join actor.usr_setting aussc on aussc.name='opac.default_sms_carrier' and au.id=aussc.usr
where
acp.id=ahr.current_copy
and au.id=ahr.usr
-- Not fulfilled
and ahr.fulfillment_time is null
-- Not canceled
and ahr.cancel_time is null
-- Not on the holdshelf
and ahr.shelf_time is null
-- Force Hold
and ahr.hold_type='F'
-- Current Copy Circ Mod = ILL
and acp.circ_modifier='ILL'
-- No Notification Set
and not ahr.email_notify
and ahr.phone_notify is null
and ahr.sms_notify is null
and ahr.sms_carrier is null
--Restrict to one pickup location for testing
and ahr.pickup_lib in (102)
-- User has some notification settings
and (
      ( (aus.value~'email' or aus.value is null) and au.email is not null )
      OR
      ( (aus.value~'phone' or aus.value is null) and (au.day_phone is not null or ausp.value is not null) )
      OR
      ( aus.value~'sms' and aussn.value is not null and aussc.value is not null )
   )
returning ahr.id,ahr.email_notify, ahr.phone_notify, ahr.sms_notify, ahr.sms_carrier, au.email, au.day_phone, aus.value, ausp.value, aussn.value, aussc.value
;

rollback;