Evergreen Phone - Only place one call a day

holdpickup-phone-post.sql

\echo Post-Hook SQL Scripts - Run after new events have been created.

begin;

\echo Summary of events for event_def=105
select state,count(id) from action_trigger.event where event_def=105 group by state;

\echo Change event to invalid if phone notify not set.
-- Set to invalid if phone notify not set.
UPDATE action_trigger.event ate
set state='invalid'
from action.hold_request ahr
where
ahr.id=ate.target
and ate.event_def=105
and ate.state='pending'
and ahr.phone_notify is null
;

\echo Change event to invalid if hold has been fulfilled.
UPDATE action_trigger.event ate
set state='invalid'
from action.hold_request ahr
where
ahr.id=ate.target
and ate.event_def=105
and ate.state='pending'
and ahr.fulfillment_time is not null
;

\echo Change event to pending-delay if call has been sent for this customer in last 12 hours.
UPDATE action_trigger.event ate
set state='invalid',user_data='{"state": "pending-delay"}'::json
from action.hold_request ahr
where
ahr.id=ate.target
and ate.event_def=105
and ate.state='pending'
and exists (
select 1
  from action_trigger.event ates
  join action.hold_request ahrs on ates.target=ahrs.id
  where
  ates.event_def=105
  and ates.state='complete'
  and ates.update_time > now()-'12 hours'::interval
  and ahrs.usr=ahr.usr
  )
returning ate.id eventid, ahr.usr userid, ahr.id holdid
;

\echo Summary of events for event_def=105 - After changes.
select state,count(id) from action_trigger.event where event_def=105 group by state;

\echo Summary of phone calls by pickup location
select aou.shortname, count(distinct ahr.usr) as patrons, count(distinct ahr.id) as items
from
action_trigger.event ate
join action.hold_request ahr on ate.target=ahr.id
join actor.org_unit aou on ahr.pickup_lib=aou.id
where
ate.event_def=105
and ate.state='pending'
group by aou.parent_ou, aou.shortname
order by aou.parent_ou, aou.shortname
;

commit;

holdpickup-phone-readme.txt

These scripts attempt to prevent customers from getting more than one hold pickup phone call a day.

holdpickup-phone-reset-post.sql

\echo Post-Hook SQL Scripts - Run after new events have been created.

begin;

\echo Summary of events for event_def=105
select state,count(id) from action_trigger.event where event_def=105 group by state;

\echo Set pending-delay events back to pending so they will get evaluated again
update action_trigger.event ate
set state='pending',user_data=null
where
ate.state='invalid'
and ate.user_data = '{"state": "pending-delay"}'
and ate.event_def=105
;

\echo Summary of events for event_def=105 - After changes.
select state,count(id) from action_trigger.event where event_def=105 group by state;

\echo Summary of phone calls by pickup location
select aou.shortname, count(distinct ahr.usr) as patrons, count(distinct ahr.id) as items
from
action_trigger.event ate
join action.hold_request ahr on ate.target=ahr.id
join actor.usr au on au.id=ahr.usr
join actor.org_unit aou on au.home_ou=aou.id
where
ate.event_def=105
and ate.state='pending'
group by aou.parent_ou, aou.shortname
order by aou.parent_ou, aou.shortname
;

commit;

holdpickup-phone-reset.bash

#!/bin/bash

NAME=holdpickup-phone-reset
DATE=`date +%Y-%m-%d-%H-%M-%S`
ATRUNNER=/openils/bin/action_trigger_runner.pl
ATAGGRUNNER=/openils/bin/action_trigger_aggregator.pl
GRAN=phonenotify
JSONPATH=/openils/conf
CONF_DIR=/openils/conf
POST_SCRIPT=$CONF_DIR/$NAME-post.sql


#Load DB Connection Defaults
source $CONF_DIR/db-environment.rc


echo "Post-hook sql script run."
psql -f $POST_SCRIPT

holdpickup-phone.bash

#!/bin/bash

NAME=holdpickup-phone
DATE=`date +%Y-%m-%d-%H-%M-%S`
ATRUNNER=/openils/bin/action_trigger_runner.pl
ATAGGRUNNER=/openils/bin/action_trigger_aggregator.pl
GRAN=phonenotify
JSONPATH=/openils/conf
CONF_DIR=/openils/conf
POST_SCRIPT=$CONF_DIR/$NAME-post.sql


#Load DB Connection Defaults
# Contains DB connection environment variables.
source $CONF_DIR/db-environment.rc


echo "Post-hook sql script run."
if ! psql -f $POST_SCRIPT; then
  echo "SQL Script failed - exiting script."
  exit 1
fi

echo "Run pending hooks"
$ATRUNNER \
--run-pending \
--granularity=$GRAN \
--verbose