Differences

This shows you the differences between two versions of the page.

--- evergreen-reports:automated_sql_reports [2011/07/13 14:51] – created bshum
+++ evergreen-reports:automated_sql_reports [2022/02/10 13:34] (current) – external edit 127.0.0.1
@@ Line 127: / Line 127: @@
 ==== Monthly Sample Script ====
-Coming soon!
+Using a similar method as described above for the daily control scripts but with some enhancements.  First, there is now a control loop.sh script that runs through variables from a file to generate reports content.  Queries are now stored in a folder called "queries" and use postgrsql variables passed from other scripts.
+=== Running the LOOP Script ===
+To run the action, we now have a single script called loop.sh which runs with a variable file.  The example in this case could be libs.txt.  The loop.sh script takes the variables out of libs.txt and passes them to the monthly_stats.sh script to be used there.
+Run with:
+<code bash>
+./loop.sh libs.txt
+</code>
+== libs.txt ==
+<code>
+SHORTNAME|11|test@email.com
+SHORTNAME2|13|test2@email.com
+</code>
+== loop.sh ==
+<code bash>
+#!/bin/sh
+# Looping SQL Running
+echo "Starting Loop Cycle..."
+INPUT=$1
+OLDIFS=$IFS
+IFS=\|
+[ ! -f $INPUT ] && { echo "$INPUT file not found"; exit 99; }
+while read shortname ou email
+do
+        echo "\nLib : $shortname"
+        echo "OU : $ou"
+        echo "E-mail : $email\n"
+        ./monthly_stats.sh $shortname $ou $email
+done < $INPUT
+IFS=$OLDIFS
+echo "\n... and we're done!"
+</code>
+=== monthly_stats.sh ===
+<code bash>
+#!/bin/sh
+# Generic monthly_stats.sh
+#
+# Script for generating monthly reports for an EG Library
+#
+# The whole thing
+# Define variables
+DATE=$(date --date='1 month ago' '+%Y-%m')
+EMAILDATE=$(date '+%F')
+QUERIES=/home/<user>/sql/queries
+BASEDIR=/home/<user>/sql/monthly
+FOLDER=TEST
+ORG=$1
+OU=$2
+RECIPIENT="$3"
+# Run the SQL reports to get csv outputs
+psql -U <db_user> -h <db_hostname> -f $BASEDIR/$FOLDER/$ORG'_daily_stats'.postgresql <db_name>
+psql -U <db_user> -h <db_hostname> -f $QUERIES/'patrons_without_residency'.sql <db_name> -o $BASEDIR/$FOLDER/$ORG'_'$DATE'_patrons_without_residency2'.csv -v circ_lib=$OU
+# Add title of report to csv
+sed -e '1 i Patrons Without Residency Stat Cat Entries as of '$DATE'|' -e '1 i \ |' -e '$d' $BASEDIR/$FOLDER/$ORG'_'$DATE'_patrons_without_residency2'.csv > $BASEDIR/$FOLDER/$ORG'_'$DATE'_patrons_without_residency'.csv
+rm $BASEDIR/$FOLDER/$ORG'_'$DATE'_patrons_without_residency2'.csv
+# Convert csv to xls
+/home/<user>/sql/csv2xls.pl -s '|' -v 0 -o $BASEDIR'/'$FOLDER'/'$ORG'_'$DATE'_patrons_without_residency' $BASEDIR/$FOLDER/$ORG'_'$DATE'_patrons_without_residency'.csv
+# Generate email text
+$BASEDIR/residency_monthly_text.sh > $BASEDIR/$FOLDER/$ORG'_'$DATE'_email_text'.txt
+# E-mail out the report
+mutt -s "$ORG - Patrons Missing Residency Code as of $EMAILDATE" -a $BASEDIR/$FOLDER/*.xls -- $RECIPIENT < $BASEDIR/$FOLDER/$ORG'_'$DATE'_email_text'.txt
+# Remove leftovers
+rm $BASEDIR/$FOLDER/$ORG'_'$DATE'_patrons_without_residency'.csv
+rm $BASEDIR/$FOLDER/$ORG'_'$DATE'_patrons_without_residency'.xls
+rm $BASEDIR/$FOLDER/$ORG'_'$DATE'_email_text'.txt
+</code>
+=== queries/patrons_without_residency.sql ===
+<code sql>
+-- patrons_without_residency.sql
+-- This report lists patron information for those who do not have an entry for stat_cat "1" (Residency)
+\a
+\f |
+SELECT acard.barcode AS "Barcode", au.family_name AS "Last Name",
+   au.first_given_name AS "First Name", aou.name AS "Library Name"
+FROM actor.usr au
+INNER JOIN actor.org_unit aou ON au.home_ou = aou.id
+INNER JOIN actor.card acard ON acard.id = au.card
+INNER JOIN permission.grp_tree pgt ON pgt.id = au.profile
+LEFT OUTER JOIN (SELECT * FROM actor.stat_cat_entry_usr_map WHERE stat_cat = 1) AS asceum ON asceum.target_usr = au.id
+WHERE au.home_ou = :circ_lib AND asceum.id IS NULL AND au.deleted IS FALSE AND pgt.parent = 2
+ORDER BY 4, 2, 1;
+</code>
 ===== Some Future Ideas =====
@@ Line 134: / Line 252: @@
   * Add some additional options to the SQL query run:
     * Use \t to output tuples instead of ugly hack with sed removing the row count.
+      * This didn't work because we lost the first row showing the column titles, so retaining sed hack for now.
     * Make some components of the query into variables that can be passed via script.
-    * Perhaps consider moving the query as a saved view in the database
+    * Perhaps consider moving the query as a saved view in the database.