evergreen-reports:automated_sql_reports
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
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 ==== | ==== Monthly Sample Script ==== | ||
- | Coming soon! | + | Using a similar method as described above for the daily control scripts but with some enhancements. |
+ | |||
+ | === 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. | ||
+ | |||
+ | Run with: | ||
+ | <code bash> | ||
+ | ./loop.sh libs.txt | ||
+ | </ | ||
+ | |||
+ | == libs.txt == | ||
+ | |||
+ | < | ||
+ | SHORTNAME|11|test@email.com | ||
+ | SHORTNAME2|13|test2@email.com | ||
+ | </ | ||
+ | |||
+ | == loop.sh == | ||
+ | |||
+ | <code bash> | ||
+ | |||
+ | #!/bin/sh | ||
+ | # Looping SQL Running | ||
+ | |||
+ | echo " | ||
+ | |||
+ | INPUT=$1 | ||
+ | OLDIFS=$IFS | ||
+ | IFS=\| | ||
+ | [ ! -f $INPUT ] && { echo " | ||
+ | while read shortname ou email | ||
+ | do | ||
+ | echo "\nLib : $shortname" | ||
+ | echo "OU : $ou" | ||
+ | echo " | ||
+ | ./ | ||
+ | done < $INPUT | ||
+ | IFS=$OLDIFS | ||
+ | |||
+ | echo "\n... and we're done!" | ||
+ | |||
+ | </ | ||
+ | |||
+ | === 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=' | ||
+ | EMAILDATE=$(date ' | ||
+ | QUERIES=/ | ||
+ | BASEDIR=/ | ||
+ | FOLDER=TEST | ||
+ | ORG=$1 | ||
+ | OU=$2 | ||
+ | RECIPIENT=" | ||
+ | |||
+ | # Run the SQL reports to get csv outputs | ||
+ | |||
+ | psql -U < | ||
+ | |||
+ | psql -U < | ||
+ | |||
+ | # Add title of report to csv | ||
+ | |||
+ | sed -e '1 i Patrons Without Residency Stat Cat Entries as of ' | ||
+ | |||
+ | rm $BASEDIR/ | ||
+ | |||
+ | # Convert csv to xls | ||
+ | |||
+ | / | ||
+ | |||
+ | # Generate email text | ||
+ | |||
+ | $BASEDIR/ | ||
+ | |||
+ | # E-mail out the report | ||
+ | |||
+ | mutt -s "$ORG - Patrons Missing Residency Code as of $EMAILDATE" | ||
+ | |||
+ | # Remove leftovers | ||
+ | |||
+ | rm $BASEDIR/ | ||
+ | |||
+ | rm $BASEDIR/ | ||
+ | |||
+ | rm $BASEDIR/ | ||
+ | </ | ||
+ | |||
+ | === queries/ | ||
+ | |||
+ | <code sql> | ||
+ | -- patrons_without_residency.sql | ||
+ | |||
+ | -- This report lists patron information for those who do not have an entry for stat_cat " | ||
+ | |||
+ | \a | ||
+ | \f | | ||
+ | |||
+ | SELECT acard.barcode AS " | ||
+ | | ||
+ | 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; | ||
+ | </ | ||
===== Some Future Ideas ===== | ===== Some Future Ideas ===== | ||
Line 134: | Line 252: | ||
* Add some additional options to the SQL query 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. | * 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. | * 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. |
evergreen-reports/automated_sql_reports.txt · Last modified: 2022/02/10 13:34 by 127.0.0.1