From 07efb4adce78a0da7406c8ce8241b1584c6a0506 Mon Sep 17 00:00:00 2001
From: Bill Erickson <berickxx@gmail.com>
Date: Tue, 9 Feb 2016 17:28:52 -0500
Subject: [PATCH] JBAS-984 Student cards automation scripts

Signed-off-by: Bill Erickson <berickxx@gmail.com>
---
 .../import_students/generate-patrons-from-csv.pl   |   7 +-
 .../import_students/sftp-client-agent.sh           | 189 +++++++++++++++++++++
 .../import_students/sftp-server-agent.sh           | 123 ++++++++++++++
 3 files changed, 317 insertions(+), 2 deletions(-)
 create mode 100755 KCLS/utility-scripts/import_students/sftp-client-agent.sh
 create mode 100755 KCLS/utility-scripts/import_students/sftp-server-agent.sh

diff --git a/KCLS/utility-scripts/import_students/generate-patrons-from-csv.pl b/KCLS/utility-scripts/import_students/generate-patrons-from-csv.pl
index ca91a73323..534d82112a 100755
--- a/KCLS/utility-scripts/import_students/generate-patrons-from-csv.pl
+++ b/KCLS/utility-scripts/import_students/generate-patrons-from-csv.pl
@@ -497,11 +497,11 @@ sub process_each_patron {
     iterate_csv_rows($row_handler);
 
     if ($commit_mode eq 'rollback') {
-        announce('INFO', "Rolling back batch transaction");
+        announce('DEBUG', "Rolling back batch transaction");
         eval { $db_handle->rollback };
 
     } elsif ($commit_mode eq 'batch') {
-        announce('INFO', "Committing batch transaction");
+        announce('DEBUG', "Committing batch transaction");
         eval { $db_handle->commit };
         announce('ERR', "Unable to commit batch transaction : $@", 1) if $@;
     }
@@ -776,6 +776,9 @@ sub generate_result_files {
     }
 
     close($err_file);
+
+    announce('INFO',  # not really a warning, but want to bubble up.
+        "Created $summary_stats{create} new accounts for $district_code");
 }
 
 # ------ execution starts here -----------
diff --git a/KCLS/utility-scripts/import_students/sftp-client-agent.sh b/KCLS/utility-scripts/import_students/sftp-client-agent.sh
new file mode 100755
index 0000000000..f05bb67103
--- /dev/null
+++ b/KCLS/utility-scripts/import_students/sftp-client-agent.sh
@@ -0,0 +1,189 @@
+#!/bin/bash
+set -euo pipefail
+# -------------------------------------------------------------------------
+# Author: Bill Erickson
+# Date: 2015-01-21
+#
+# Reports on and updates student import CSV files.  Script is executed
+# from a remote server to perform actions on the SFTP server.
+# -------------------------------------------------------------------------
+
+SUCCESS=0
+SERVER=prod-depot.eg.kcls.org;
+USER=sftpagent;
+LIST_PENDING="";
+FETCH_PENDING="";
+ARCHIVE="";
+DISTRICT_CODE="";
+DB_HOST="127.0.0.1";
+PROCESS_FILES="";
+MAIL_CMD=mailx
+MAIL_RECIP=bserickson@kcls.org,bserickson@localhost
+
+# Hard-code the district codes eligible for automatic processing so we
+# don't accidentally process a new/pending account or a test account.
+# Use these when no code is specified by the caller.
+DISTRICT_CODES=(
+    405 # Bellevue
+    409 # Tahoma
+    415 # Kent
+);
+
+function usage {
+
+    cat <<USAGE
+
+        Synopsis:
+
+            $0 -d 415 -l
+
+        Options:
+            
+            -s SFTP Server Hostname
+
+            -u SFTP User
+
+            -d District Code
+
+            -l List Pending Files
+
+            -p Process selected files
+            
+            -f Fetch pending files
+
+            -b Database server
+
+            -a Archive files
+                Moves files from 'pending' to 'complete'
+
+            -h Help 
+                Show this menu
+
+USAGE
+trap - EXIT
+exit;
+}
+
+ALL_MESSAGES=""
+function announce {
+    ALL_MESSAGES="$ALL_MESSAGES$1\n"
+    echo "$(date +'%F %T') $(hostname): $1"
+}
+
+# email import summary output
+function report_results {
+
+    if [ ! "$(which $MAIL_CMD)" ]; then
+        return;
+    fi
+
+    echo -e "$ALL_MESSAGES" | $MAIL_CMD \
+        -s "Student Account Imports For $(date +'%F %T')" $MAIL_RECIP
+}
+
+# called when the script exits for any reason. 
+# if the script exits early, warn the user.
+function on_exit {
+    if [ $SUCCESS -ne 1 ]; then
+        announce "** $0 exited unexpectedly! **"
+    fi
+}
+
+# -- script starts here --
+
+trap on_exit EXIT;
+
+while getopts "s:u:d:b:plfah" opt; do
+    case $opt in
+        s) SERVER="$OPTARG";;
+        u) USER="$OPTARG";;
+        b) DB_HOST="$OPTARG";;
+        p) PROCESS_FILES="YES";;
+        l) LIST_PENDING="YES";;
+        f) FETCH_PENDING="YES";;
+        a) ARCHIVE="YES";;
+        d) DISTRICT_CODE="$OPTARG";;
+        h) usage;
+    esac
+done;
+
+SSH="ssh $USER@$SERVER"
+SCP="scp -q $USER@$SERVER"
+SERVER_AGENT="/home/$USER/sftp-server-agent.sh"
+
+# only process the requested district if specified
+if [ -n "$DISTRICT_CODE" ]; then 
+    DISTRICT_CODES=($DISTRICT_CODE);
+fi;
+
+announce "Connecting to server $SERVER"
+if [ -n "$PROCESS_FILES" ]; then
+    announce "Processing files on DB $DB_HOST"
+fi
+
+for code in "${DISTRICT_CODES[@]}"; do
+    COMMAND="$SERVER_AGENT -d $code"; 
+    COMPLETE_DIR="/home/sftp$code/complete"
+
+    if [ -n "$PROCESS_FILES" ]; then
+
+        for FILE in $($SSH "$COMMAND -l"); do
+            LOCAL_FILE=$(basename $FILE)
+
+            announce "Retrieving file $FILE"
+            $SCP:$FILE $LOCAL_FILE
+
+            announce "Processing file $LOCAL_FILE"
+            INFO=$(perl ./generate-patrons-from-csv.pl \
+                --log-stdout \
+                --db-host $DB_HOST \
+                --district-code $code \
+                --commit-mode each \
+                $LOCAL_FILE | grep -v DEBUG)
+            # TODO: ^-- support passing log level 
+            # to generate-patrons-from-csv.pl
+            announce "$INFO"
+
+            if [ $? -eq 0 ]; then
+
+                # If -a/archive is turned on, tell the server to archive
+                # the original CSV file and copy the error and summary
+                # files to the server archive directory.
+                if [ -n "$ARCHIVE" ]; then
+                    $SSH "$COMMAND -a -f $FILE"
+
+                    LOCAL_FILE_NOEXT=${LOCAL_FILE%.*}
+                    ERR_FILE="${LOCAL_FILE_NOEXT}.err.csv"
+                    SUM_FILE="${LOCAL_FILE_NOEXT}.sum.csv"
+
+                    if [ -f $ERR_FILE ]; then
+                        scp -q $ERR_FILE "$USER@$SERVER:$COMPLETE_DIR"
+                        rm $ERR_FILE
+                    fi
+                    if [ -f $SUM_FILE ]; then
+                        scp -q $SUM_FILE "$USER@$SERVER:$COMPLETE_DIR"
+                        rm $SUM_FILE
+                    fi
+
+                    # All is well.  Remove our copy of the CSV import file.
+                    rm -f $LOCAL_FILE
+                fi
+            else
+                announce "Error processing $FILE"
+            fi
+        done;
+
+    else # one-off operations
+
+        if [ -n "$LIST_PENDING" ]; then COMMAND="$COMMAND -l"; fi;
+        if [ -n "$ARCHIVE" ];      then COMMAND="$COMMAND -a"; fi;
+
+        $SSH "$COMMAND"
+    fi
+
+done
+
+report_results;
+
+SUCCESS=1
+
diff --git a/KCLS/utility-scripts/import_students/sftp-server-agent.sh b/KCLS/utility-scripts/import_students/sftp-server-agent.sh
new file mode 100755
index 0000000000..e0cd35a637
--- /dev/null
+++ b/KCLS/utility-scripts/import_students/sftp-server-agent.sh
@@ -0,0 +1,123 @@
+#!/bin/bash
+set -euo pipefail
+# -------------------------------------------------------------------------
+# Author: Bill Erickson
+# Date: 2016-02-10
+#
+# Reports on and updates student import CSV files.  Script is executed
+# from a remote server to perform actions on the SFTP server.
+# -------------------------------------------------------------------------
+
+SUCCESS=0
+LIST_PENDING="";
+ARCHIVE="";
+FILE="";
+DISTRICT_CODE="";
+ALL="";
+
+function usage {
+
+    cat <<USAGE
+
+        Synopsis:
+
+            $0 -d 415 -l
+
+        Options:
+            
+            -d District Code [REQUIRED]
+                
+                School district code to process.  If no code is provided,
+                all school districts are processed.
+
+            -l List Pending Files
+            
+            -a Archive files
+                Moves files from 'pending' to 'complete'.
+
+            -f Process a specific file (e.g. archive)
+
+            -h Help 
+                Show this menu
+
+USAGE
+trap - EXIT
+exit;
+}
+
+function announce {
+    echo "$(date +'%F %T') $(hostname): $1" >&2
+}
+
+# called when the script exits for any reason. 
+# if the script exits early, warn the user.
+function on_exit {
+    if [ $SUCCESS -ne 1 ]; then
+        announce "** $0 exited unexpectedly! **"
+    fi
+}
+
+# -- script starts here --
+
+trap on_exit EXIT;
+
+while getopts "s:u:d:f:lah" opt; do
+    case $opt in
+        l) LIST_PENDING="YES";;
+        a) ARCHIVE="YES";;
+        d) DISTRICT_CODE="$OPTARG";;
+        f) FILE="$OPTARG";;
+        h) usage;
+    esac
+done;
+
+if [ -z "$DISTRICT_CODE" ]; then
+    announce "District code (-d) required"
+    exit;
+fi;
+
+BASE_DIR="/home/sftp$DISTRICT_CODE";
+PENDING_DIR="$BASE_DIR/pending"
+COMPLETE_DIR="$BASE_DIR/complete"
+PENDING_FILES=""
+
+# FILE is the full path to the file in question.
+function handle_file {
+    FILE=$1
+
+    if [ ! -f $FILE ]; then
+        announce "File does not exist: $FILE"
+        return;
+    fi
+
+    if [ -z "$PENDING_FILES" ]; then
+        # avoid opening space
+        PENDING_FILES="$FILE"
+    else 
+        PENDING_FILES="$PENDING_FILES $PENDING_DIR/$file"
+    fi;
+
+    if [ -n "$ARCHIVE" ]; then
+        mv $FILE $COMPLETE_DIR;
+    fi
+}
+
+if [ ! -d "$BASE_DIR" ]; then
+    announce "No home directory for district $DISTRICT_CODE"
+    exit;
+fi;
+
+if [ -n "$FILE" ]; then
+    handle_file $FILE
+else
+    for file in $(ls $PENDING_DIR | sort); do
+        handle_file $PENDING_DIR/$file;
+    done
+fi
+
+if [ -n "$LIST_PENDING" -a -n "$PENDING_FILES" ]; then
+    echo "$PENDING_FILES"
+fi
+
+SUCCESS=1
+
-- 
2.11.0