3 # Copyright (C) 2014 Georgia Public Library Service
4 # Chris Sharp <csharp@georgialibraries.org>
6 # A program that takes a library system or branch name and a date or set of dates
7 # on the command line to apply a batch void for billings applied on that day.
8 # This is particularly useful if closed dates are set after the fine generator has
11 # This script assumes that you are on a server with direct access to your master
12 # database, and that you have set the credentials for that database in the .pgpass
13 # file in your user's home directory.
15 # See http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html for more information.
19 # You'll want to set the $EGUSER/$EGPASS variables to the username/password of an Evergreen
20 # user (e.g., your administrative user in Evergreen). Adjust the $SRFSH variable to suit
21 # your environment. As of this writing, we're still installing Evergreen in /openils.
22 # The $VOID_NOTE variable should be something informative to your staff users as to why these
23 # bills were voided. Our use case was closings due to inclement weather.
25 # The $BACKUP_SCHEMA variable should be set to a schema in your postgresql database that is
26 # for administrative use. You should NOT use an existing Evergreen schema name (e.g. "action",
27 # "actor", etc. for this purpose. I use "csharp" for that in our case.
29 # My hope is that this can be re-implemented in Perl or something with actual OpenSRF bindings,
30 # but my current skill level and available time prevented me from doing so now.
34 SRFSH="/openils/bin/srfsh"
37 VOID_NOTE="VOIDED BY SYSTEM STAFF - INCLEMENT WEATHER"
38 BACKUP_SCHEMA="myschema"
40 Usage() { echo "Usage: ./batch_void_fines.sh -d YYYY-MM-DD,YYYY-MM-DD,... -s systemname OR -b branchname."
43 while getopts s:b:d:h OPTIONS
53 if [ -n "$SYSTEM" ] && [ -n "$BRANCH" ]; then
54 echo "Please only specify either system OR branch. Not both."
59 if [ -z "$DATE" ]; then
60 echo "Date is required."
65 if [ ! $(echo $DATE | egrep '20[0-9][0-9]-[01][0-9]-[0-3][0-9](,20[0-9][0-9]-[01][0-9]-[0-3][0-9])?') ]; then
66 echo "Date must be in YYYY-MM-DD format. Please check."
71 if [ $(echo $DATE | grep ",") ]; then
72 SPLITDATE=`echo $DATE | sed "s/,/', '/g"`
76 if [ -n "$SYSTEM" ]; then
77 if [ $(echo $SYSTEM | grep "-") ]; then
78 echo "System names do not have hyphens. Did you mean -b?"
82 BACKUP_TABLE="$BACKUP_SCHEMA.`echo $SYSTEM | tr '[:upper:]' '[:lower:]'`_batch_voided_fines_`echo $DATE | tr '-' '_' | sed "s/'//g" | sed "s/, /_/g"`"
84 if [ ! $(echo $BRANCH | grep "-") ]; then
85 echo "Branch names have hyphens. Did you mean -s?"
89 BACKUP_TABLE="$BACKUP_SCHEMA.`echo $BRANCH | tr '[:upper:]' '[:lower:]' | tr '-' '_'`_batch_voided_fines_`echo $DATE | tr '-' '_' | sed "s/'//g" | sed "s/, /_/g"`"
91 read -d '' SYSTEM_Q <<END_OF_Q
92 SELECT * INTO $BACKUP_TABLE
94 WHERE date(billing_ts) IN (
99 FROM action.circulation ac
100 WHERE mb.xact = ac.id
107 WHERE shortname = '$SYSTEM')));
109 read -d '' BRANCH_Q <<END_OF_Q
110 SELECT * INTO $BACKUP_TABLE
111 FROM money.billing mb
112 WHERE date(billing_ts) IN (
117 FROM action.circulation ac
118 WHERE mb.xact = ac.id
122 WHERE shortname = '$BRANCH'));
125 if [ -n "$SYSTEM" ]; then
127 psql -U "$DBUSER" -h "$DBHOST" -c "$SYSTEM_Q"
130 if [ -n "$BRANCH" ]; then
132 psql -U "$DBUSER" -h "$DBHOST" -c "$BRANCH_Q"
135 read -d '' BILLS_SQL <<END_OF_Q
136 SELECT id FROM $BACKUP_TABLE;
139 read -d '' COUNT_SQL <<END_OF_Q
149 read -d '' UPDATE_SQL <<END_OF_Q
152 SET note = '$VOID_NOTE'
160 BILLS=`psql -A -t -U "$DBUSER" -h "$DBHOST" -c "$BILLS_SQL" | sed 's/^/"/g' | sed 's/$/", /g' | tr '\n' ' '`
162 AUTHTOKEN=`echo "login $EGUSER $EGPASS" | $SRFSH | grep "Login Session" | cut -d':' -f 2 | cut -d'.' -f1 | sed 's/ //g'`
164 SRFSH_COMMAND="request open-ils.circ open-ils.circ.money.billing.void \"$AUTHTOKEN\" $BILLS"
166 echo "$SRFSH_COMMAND" >> "${BACKUP_TABLE}_void.srfsh"
168 echo "$SRFSH_COMMAND" | $SRFSH
170 until [ $(psql -A -t -U "$DBUSER" -h "$DBHOST" -c "$COUNT_SQL") == "0" ]; do
171 echo "Waiting for srfsh command to complete..."
175 psql -U "$DBUSER" -h "$DBHOST" -c "$UPDATE_SQL"
177 echo "Update complete"