Created
October 26, 2016 16:41
-
-
Save stompro/c1795afd55d28289b4ecd7e325e80ea3 to your computer and use it in GitHub Desktop.
Evergreen Phone - Only place one call a day
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
\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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
These scripts attempt to prevent customers from getting more than one hold pickup phone call a day. | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
\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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment