From 7e794cf491679e9eef80a2746e11f4f90592c9c9 Mon Sep 17 00:00:00 2001 From: Andy Witter Date: Mon, 1 Oct 2012 11:33:57 -0400 Subject: [PATCH] adding intrapines loan script --- intrapines-loans/README.template | 6 ++ intrapines-loans/intrapines-loans-reports.sh | 49 ++++++++++ .../intrapines_matrix_report-template.sql | 102 +++++++++++++++++++++ 3 files changed, 157 insertions(+) create mode 100644 intrapines-loans/README.template create mode 100755 intrapines-loans/intrapines-loans-reports.sh create mode 100644 intrapines-loans/intrapines_matrix_report-template.sql diff --git a/intrapines-loans/README.template b/intrapines-loans/README.template new file mode 100644 index 0000000..e3c10b1 --- /dev/null +++ b/intrapines-loans/README.template @@ -0,0 +1,6 @@ +Georgia PINES IntraPINES Loans Reports +====================================== + +These reports were generated on TODAYS_DATE on FQDN. + +Please notify ADMIN_EMAIL of any problems you encounter. diff --git a/intrapines-loans/intrapines-loans-reports.sh b/intrapines-loans/intrapines-loans-reports.sh new file mode 100755 index 0000000..b01d9c6 --- /dev/null +++ b/intrapines-loans/intrapines-loans-reports.sh @@ -0,0 +1,49 @@ +#!/bin/bash +# +# Simplifying the process of running IntraPINES loans. +# Chris Sharp +# + + +WORK_DIR=$(pwd) +PGUSERNAME="evergreen" +DBHOST="localhost" +DBNAME="evergreen" +OUTBOX="$WORK_DIR/outbox" + + +clear +echo "This script will generate an SQL script to run IntraPINES loans reports" +echo "(for loans between systems not including loans between branches within" +echo "systems)." +echo +read -p "Please enter the start date for the reports (in YYYY-MM-DD format): " START_DATE +read -p "Please enter the end date for the reports (in YYYY-MM-DD format): " END_DATE +echo +read -p "Reports will be run between $START_DATE and $END_DATE. Is this correct (y/n)? " RESPONSE +if [ $RESPONSE != "y" ]; then + echo "Aborting." + exit +fi + +SQL_TEMPLATE="intrapines_matrix_report-template.sql" +TMP_TEMPLATE="/tmp/intrapines_matrix_report-$START_DATE-to-$END_DATE.sql" +README="/tmp/README-intraPINES-loans-$START_DATE-to-$END_DATE.txt" +ADMIN_EMAIL="admin-email@example.org" + +echo "Generating SQL template." +cp -f "$SQL_TEMPLATE" "$TMP_TEMPLATE" +sed -i "s^START_DATE^$START_DATE^g" "$TMP_TEMPLATE" +sed -i "s^END_DATE^$END_DATE^g" "$TMP_TEMPLATE" +echo "Performing SQL queries." +psql -F\| -A -f "$TMP_TEMPLATE" -h "$DBHOST" -U "$DBNAME" +echo "Generating README.txt." +cp "$WORK_DIR"/README.template "$README" +sed -i "s^FQDN^$(hostname -f)^g" "$README" +sed -i "s^TODAYS_DATE^$(date)^g" "$README" +sed -i "s^ADMIN_EMAIL^$ADMIN_EMAIL^g" "$README" +echo "Zipping results." +zip -jmT $OUTBOX/intrapines-reports-$START_DATE-to-$END_DATE.zip /tmp/*$START_DATE-to-$END_DATE.csv $README +echo "Complete! Zipped results available at $OUTBOX/intrapines-reports-$START_DATE-to-$END_DATE.zip" + +#TODO: automatically transfer these to the local PINES samba share diff --git a/intrapines-loans/intrapines_matrix_report-template.sql b/intrapines-loans/intrapines_matrix_report-template.sql new file mode 100644 index 0000000..739669c --- /dev/null +++ b/intrapines-loans/intrapines_matrix_report-template.sql @@ -0,0 +1,102 @@ +-- must %s/{date}/{marker}g for this to work ... dunno why :( +\set start 'START_DATE' +\set end 'END_DATE' + +\o /tmp/intrapines-facility-all-transit-totals-START_DATE-to-END_DATE.csv + +select s.shortname, send_count, recv_count, recv_count - send_count as delta + from + ( select s.shortname, count(*) as send_count + from "action".transit_copy t + join actor.org_unit s on (s.id = t.source) + join actor.org_unit r on (r.id = t.dest) + where s.parent_ou <> r.parent_ou and source_send_time between 'START_DATE' and 'END_DATE' group by 1) s + join + ( select r.shortname, count(*) as recv_count + from "action".transit_copy t + join actor.org_unit s on (s.id = t.source) + join actor.org_unit r on (r.id = t.dest) + where s.parent_ou <> r.parent_ou and source_send_time between 'START_DATE' and 'END_DATE' group by 1) r + using (shortname) + order by 1; + +\o /tmp/intrapines-facility-hold-transit-totals-START_DATE-to-END_DATE.csv + +select s.shortname, send_count, recv_count, recv_count - send_count as delta + from + ( select s.shortname, count(*) as send_count + from "action".hold_transit_copy t + join actor.org_unit s on (s.id = t.source) + join actor.org_unit r on (r.id = t.dest) + where s.parent_ou <> r.parent_ou and source_send_time between 'START_DATE' and 'END_DATE' group by 1) s + join + ( select r.shortname, count(*) as recv_count + from "action".hold_transit_copy t + join actor.org_unit s on (s.id = t.source) + join actor.org_unit r on (r.id = t.dest) + where s.parent_ou <> r.parent_ou and source_send_time between 'START_DATE' and 'END_DATE' group by 1) r + using (shortname) + order by 1; + +\o /tmp/intrapines-system-hold-transit-totals-START_DATE-to-END_DATE.csv + +select s.shortname, send_count, recv_count, recv_count - send_count as delta + from + ( select p.shortname, count(*) as send_count + from "action".hold_transit_copy t + join actor.org_unit s on (s.id = t.source) + join actor.org_unit r on (r.id = t.dest) + join actor.org_unit p on (s.parent_ou = p.id) + where s.parent_ou <> r.parent_ou and source_send_time between 'START_DATE' and 'END_DATE' group by 1) s + join + ( select p.shortname, count(*) as recv_count + from "action".hold_transit_copy t + join actor.org_unit s on (s.id = t.source) + join actor.org_unit r on (r.id = t.dest) + join actor.org_unit p on (r.parent_ou = p.id) + where s.parent_ou <> r.parent_ou and source_send_time between 'START_DATE' and 'END_DATE' group by 1) r + using (shortname) + order by 1; + +\o /tmp/intrapines-system-all-transit-totals-START_DATE-to-END_DATE.csv + +select s.shortname, send_count, recv_count, recv_count - send_count as delta + from + ( select p.shortname, count(*) as send_count + from "action".transit_copy t + join actor.org_unit s on (s.id = t.source) + join actor.org_unit r on (r.id = t.dest) + join actor.org_unit p on (s.parent_ou = p.id) + where s.parent_ou <> r.parent_ou and source_send_time between 'START_DATE' and 'END_DATE' group by 1) s + join + ( select p.shortname, count(*) as recv_count + from "action".transit_copy t + join actor.org_unit s on (s.id = t.source) + join actor.org_unit r on (r.id = t.dest) + join actor.org_unit p on (r.parent_ou = p.id) + where s.parent_ou <> r.parent_ou and source_send_time between 'START_DATE' and 'END_DATE' group by 1) r + using (shortname) + order by 1; + +\o /tmp/intrapines-facility-to-facility-hold-transit-START_DATE-to-END_DATE.csv + +select s.shortname as source, r.shortname as destination, count(*) as count + from "action".hold_transit_copy t + join actor.org_unit s on (s.id = t.source) + join actor.org_unit r on (r.id = t.dest) + where s.parent_ou <> r.parent_ou and source_send_time between 'START_DATE' and 'END_DATE' + group by 1, 2 + order by 1,2; + +\o /tmp/intrapines-facility-to-facility-all-transit-START_DATE-to-END_DATE.csv + +select s.shortname as source, r.shortname as destination, count(*) as count + from "action".transit_copy t + join actor.org_unit s on (s.id = t.source) + join actor.org_unit r on (r.id = t.dest) + where s.parent_ou <> r.parent_ou and source_send_time between 'START_DATE' and 'END_DATE' + group by 1, 2 + order by 1,2; + +\o + -- 2.11.0