User Tools

Site Tools


evergreen-reports:automated_sql_reports

This is an old revision of the document!


Automated SQL Report Scripting (Draft)

Requirements

  1. Setup or make use of a database user that can run SELECT queries against your database server.
  2. Use a converter script to change .csv to .xls file formatting.
  3. Install and configure Mutt for a text-based e-mail client to automatically send the finished reports to the library.

Overview of Process

  1. Run SQL query against database to retrieve data and produce output in CSV format.
  2. Add title of report and edit report with additional elements.
  3. Use converter script to change from CSV to XLS format.
  4. Generate e-mail text to include, and then attach XLS to email to library.

Sample scripts

The following sample scripts are used to provide daily e-mailed reports for the previous day's circulation for a given library.

Daily Sample Script

Control script

  • The directory where we store scripts is in /home/<user>/sql where <user> will be the name of the user you setup to run these scripts with.
  • Change other related anonymized places within this script with the appropriate values for your organization. Someday these should be updated to use more variables to allow for further customization or simpler setup instead of hardcoded values.
    • <db_user> = user that can access postgres database where Evergreen is running
    • <db_hostname> = hostname for Evergreen database server, usually localhost if single-server
    • <db_name> = usually "evergreen" if using default configuration options
    • The RECIPIENT variable defines the e-mail address you will be sending to
#!/bin/sh
 
# TEST_daily_stats.sh
#
# Script for generating daily circ stats for TEST Library
 
# Define our variables
 
BASEDIR=/home/<user>/sql
DATE=$(date --date='1 day ago' '+%F')
FOLDER=TEST
ORG=TEST
RECIPIENT="test@library.org"
 
# Run the SQL report to get csv output
 
psql -U <db_user> -h <db_hostname> -f $BASEDIR/$FOLDER/$ORG'_daily_stats'.postgresql <db_name>
 
# Insert Title at beginning and Delete the last line from the csv (number of rows)
 
sed -e '1 i Daily Circ for '$DATE'|' -e '1 i \ |' -e '$d' $BASEDIR/$FOLDER/$ORG'_daily_stats2'.csv > $BASEDIR/$FOLDER/$ORG'_daily_stats'.csv
 
rm $BASEDIR/$FOLDER/$ORG'_daily_stats2'.csv
 
# Convert csv to xls
 
$BASEDIR/csv2xls.pl -s '|' -v 0 -o $BASEDIR'/'$FOLDER'/'$ORG'_daily_circ_'$DATE $BASEDIR/$FOLDER/$ORG'_daily_stats'.csv
 
# Create email text for the day
 
$BASEDIR/$FOLDER/$ORG'_daily_text'.sh > $BASEDIR/$FOLDER/$ORG'_email_text_'$DATE.txt
 
# E-mail out the report
 
mutt -s "Daily Circ for $DATE" -a $BASEDIR/$FOLDER/$ORG'_daily_circ_'$DATE.xls -- $RECIPIENT < $BASEDIR/$FOLDER/$ORG'_email_text_'$DATE.txt
 
# Remove leftovers
 
rm $BASEDIR/$FOLDER/$ORG'_daily_stats'.csv
rm $BASEDIR/$FOLDER/$ORG'_daily_circ_'$DATE.xls
rm $BASEDIR/$FOLDER/$ORG'_email_text_'$DATE.txt

SQL Query

This file is called TEST_daily_circ.postgresql. It creates an output file used by the control script above. The path for the output is hardcoded into the first line of the SQL with the \o. The \f | indicates that | will be used as the delimiter between columns.

\o /home/<user>/SQL/TEST/TEST_daily_stats2.csv
\a
\f |
SELECT acl.name AS "Shelving Location", COUNT(acirc.id) AS "# of Circs"
   FROM action.circulation acirc
   JOIN asset.copy acopy ON acopy.id = acirc.target_copy
   JOIN asset.copy_location acl ON acopy.location = acl.id
  WHERE acirc.circ_lib = '11'
   AND date_trunc('DAY'::text, acirc.create_time) = date_trunc('DAY'::text, now() - '1 day'::INTERVAL)
  GROUP BY acl.name
UNION 
 SELECT 'ZZZ Total' AS "Shelving Location", COUNT(acirc.id) AS "# of Circs"
   FROM action.circulation acirc
   JOIN asset.copy acopy ON acopy.id = acirc.target_copy
   JOIN asset.copy_location acl ON acopy.location = acl.id
  WHERE acirc.circ_lib = '11'
   AND date_trunc('DAY'::text, acirc.create_time) = date_trunc('DAY'::text, now() - '1 day'::INTERVAL)
ORDER BY 1;
\o

E-mail Text Script

#!/bin/bash
 
# TEST_daily_text.sh
#
# Script for generating daily email body text for TEST Library
 
DATE=$(date --date='1 day ago' '+%A, %B %d, %Y')
 
echo "Hi,
 
Attached is your daily circ report for "$DATE". Please let us know if you have any questions.
 
Thank you,
 
Your Helpdesk"

Monthly Sample Script

Coming soon!

Some Future Ideas

  • Have less hardcoded values in the script; customize variables that can be set or passed to the shell script during run.
  • Add some additional options to the SQL query run:
    • Use \t to output tuples instead of ugly hack with sed removing the row count.
    • 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
evergreen-reports/automated_sql_reports.1310583072.txt.gz · Last modified: 2022/02/10 13:33 (external edit)

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki

© 2008-2022 GPLS and others. Evergreen is open source software, freely licensed under GNU GPLv2 or later.
The Evergreen Project is a U.S. 501(c)3 non-profit organization.