#!/bin/ksh
# prcs_ftp, Jochen Seifarth, 2003-11-06
# Process type for PeopleSoft that allows to schedule and run FTP GETs and PUTs
# in Process Scheduler
#
# Parameters:
#   $0 = this program's name
#   $1 = command file with parameters
#   see variables below for parameters and expected format
#
set -x # for debug
#
# Read in arguments from command file
#
# cat $1 | awk '{ print NF }'
PRCSNAME=`cat $1 | awk '{ print $1 }'`
ACCESSID=`cat $1 | awk '{ print $2 }'`
ACCESSPSWD=`cat $1 | awk '{ print $3 }'`
RUNCNTLID=`cat $1 | awk '{ print $4 }'`
INSTANCE=`cat $1 | awk '{ print $5 }'`
DBNAME=`cat $1 | awk '{ print $6 }'`

# these fields MUST be seperated by semicolons (';') so that they can contain blanks
# the first field is all the above so we start at the second field, i.e. after the first semicolon
FTPHOST=`cat $1  | awk '{ FS = ";" } { print $2 }'`
FTPUSER=`cat $1  | awk '{ FS = ";" } { print $3 }'`
FTPPASS=`crypt PaSsWoRd < $PS_HOME/.$DBNAME/.$FTPUSER@$FTPHOST.crypt`
FTPPRE=`cat $1   | awk '{ FS = ";" } { print $4 }'`
FTPXFR=`cat $1   | awk '{ FS = ";" } { print $5 }'`
FTPFILE1=`cat $1 | awk '{ FS = ";" } { print $6 }'`
FTPFILE2=`cat $1 | awk '{ FS = ";" } { print $7 }'`
FTPPOST=`cat $1  | awk '{ FS = ";" } { print $8 }'`
# flag to tell us if we should use crypt for the file to be transferred
# (blank) or 'N' -> nothing; 'C' -> crypt with FTPUSER ID's keys
CRYPT=`cat $1  | awk '{ FS = ";" } { print $9 }'`
# List of messages that we accept as successful transfer confirmations
# Please be carefulfull when editing, the below are regular expressions so don't include any characters
# that have special RE meaning, e.g.: .*^[] etc.
FTPOK="226 Transfer complete|250 Transfer complete|250 Requested file action okay, complete|226 Closing data connection; requested file action successful|226 ASCII Transfer complete"
#set date
DATE=`date +%Y%m%d.%H%M%S`

# Tell PS we are running ---
$ORACLE_HOME/bin/sqlplus -S $ACCESSID/$ACCESSPSWD@$DBNAME <<EOF
update psprcsrqst set runstatus = '7', BEGINDTTM = sysdate, LASTUPDDTTM = sysdate  where prcsinstance = $INSTANCE;
commit;
exit
EOF

#if put we need to check that the files exists
#break if not OK
if [ $FTPXFR = "put" ]; then
     if [ x$FTPFILE1 = x`echo $FTPFILE1 | grep "*"` ]; then
     # * found, we need to expand the file name first
     # this finds the most recent file that matches the pattern
     cd /path/to/pshome/appserv/prcs/$DBNAME/files
     xFTPFILE1=`echo "$FTPFILE1" | sed "s/\*/\\\\\*/g"`
     FTPFILE1=`ls -t -1 $FTPFILE1 | head -1`
     # replace the portion of filename2 which was identical to filename1 with the expanded filename
     FTPFILE2=`echo "$FTPFILE2" | sed "s/$xFTPFILE1/$FTPFILE1/g"`
     fi
     if [ -r /path/to/pshome/appserv/prcs/$DBNAME/files/$4 ]; then
               if [ $CRYPT = "C" ]; then
                    # We use unix2dos to do the ASCII and CRLF conversion as we then encrypt the file which effectively tranfers it as binary.
                    # The ASCII and CRLF conversion would normally be done on-the-fly during FTP
                    rm /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE1.dos
                    /bin/unix2dos /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE1 /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE1.dos
                    /bin/crypt `cat $PS_HOME/.$DBNAME/.$FTPUSER@$FTPHOST.key` < /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE1.dos > /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE1.xfr
               else
                    /bin/cp /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE1 /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE1.xfr
               fi

          # regardless of what conversion we do we want to transfer the $FTPFILE1.xfr file
          ftp -inv $FTPHOST <<EOF > /tmp/$FTPHOST.$FTPFILE1.$DATE.log 2>&1
user $FTPUSER
$FTPPASS
$FTPPRE
$FTPXFR /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE1.xfr $FTPFILE2
$FTPPOST
bye
EOF
          # archive a copy of the original file, so that we can keep track of what we transferred and when
          /bin/mv /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE1.xfr /path/to/pshome/appserv/prcs/$DBNAME/files/archive/$FTPFILE1.xfr.$DATE
          /bin/gzip /path/to/pshome/appserv/prcs/$DBNAME/files/archive/$FTPFILE1.xfr.$DATE

          cat /tmp/$FTPHOST.$FTPFILE1.$DATE.log
          FTPRESULT=`egrep "$FTPOK" /tmp/$FTPHOST.$FTPFILE1.$DATE.log`
          rm /tmp/$FTPHOST.$FTPFILE1.$DATE.log
          if [ "x$FTPRESULT" = "x" ]; then
               # flag as UNsuccessful
               $ORACLE_HOME/bin/sqlplus -S $ACCESSID/$ACCESSPSWD@$DBNAME <<EOF
update psprcsrqst set runstatus = '10', PRCSRTNCD = 0, ENDDTTM = sysdate, LASTUPDDTTM = sysdate where prcsinstance = $INSTANCE;
update psprcsque  set runstatus = '10', PRCSRTNCD = 0 where prcsinstance = $INSTANCE;
commit;
exit
EOF
          else
               # flag as successful
               $ORACLE_HOME/bin/sqlplus -S $ACCESSID/$ACCESSPSWD@$DBNAME <<EOF
update psprcsrqst set runstatus = '9', PRCSRTNCD = 0, ENDDTTM = sysdate, LASTUPDDTTM = sysdate where prcsinstance = $INSTANCE;
update psprcsque  set runstatus = '9', PRCSRTNCD = 0 where prcsinstance = $INSTANCE;
commit;
exit
EOF
          fi
    else
          echo "/path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE1 doesn't exist"
          # flag as unsuccessful
          $ORACLE_HOME/bin/sqlplus -S $ACCESSID/$ACCESSPSWD@$DBNAME <<EOF
update psprcsrqst set runstatus = '10', PRCSRTNCD = 0, ENDDTTM = sysdate, LASTUPDDTTM = sysdate where prcsinstance = $INSTANCE;
update psprcsque  set runstatus = '10', PRCSRTNCD = 0 where prcsinstance = $INSTANCE;
commit;
exit
EOF
    fi

#if get we remove the local file so that we can check for file existance later
elif [ $FTPXFR = "get" ]; then
     rm /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE2
     rm /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE2.rcv

     if [ x$FTPFILE1 != x`echo $FTPFILE1 | grep "*"` ]; then
     #regular get, no * found
     ftp -inv $FTPHOST << EOF > /tmp/$FTPHOST.$FTPFILE2.$DATE.log 2>&1
user $FTPUSER
$FTPPASS
$FTPPRE
$FTPXFR $FTPFILE1 /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE2.rcv
$FTPPOST
bye
EOF
     else
     # a get with a *, this currently allows for one *, if more *'s are used the line below has to be changed
     FTP1nmap=`echo $FTPFILE1 | cut -f1 -d'*'`"\$1"`echo $FTPFILE1 | cut -f2 -d'*'`
     ftp -inv $FTPHOST << EOF > /tmp/$FTPHOST.$FTPFILE2.$DATE.log 2>&1
user $FTPUSER
$FTPPASS
$FTPPRE
lcd /path/to/pshome/appserv/prcs/$DBNAME/files
nmap $FTP1nmap $FTPFILE2.rcv
m$FTPXFR $FTPFILE1
$FTPPOST
bye
EOF
     fi
     # Have we actually received a file ?
     cat /tmp/$FTPHOST.$FTPFILE2.$DATE.log
     FTPRESULT=`egrep "$FTPOK" /tmp/$FTPHOST.$FTPFILE2.$DATE.log`
     #rm /tmp/$FTPHOST.$FTPFILE2.$DATE.log
     if [ "x$FTPRESULT" != "x" ]; then
               if [ $CRYPT = "C" ]; then
                    /bin/crypt `cat $PS_HOME/.$DBNAME/.$FTPUSER@$FTPHOST.key` < /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE2.rcv > /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE2
                    if [ "$?" = "0" ]; then
                    # flag as successful
                    $ORACLE_HOME/bin/sqlplus -S $ACCESSID/$ACCESSPSWD@$DBNAME <<EOF
update psprcsrqst set runstatus = '9', PRCSRTNCD = 0, ENDDTTM = sysdate, LASTUPDDTTM = sysdate where prcsinstance = $INSTANCE;
update psprcsque  set runstatus = '9', PRCSRTNCD = 0 where prcsinstance = $INSTANCE;
commit;
exit
EOF
                    else
                    # flag as UNsuccessful
                    $ORACLE_HOME/bin/sqlplus -S $ACCESSID/$ACCESSPSWD@$DBNAME <<EOF
update psprcsrqst set runstatus = '10', PRCSRTNCD = 0, ENDDTTM = sysdate, LASTUPDDTTM = sysdate where prcsinstance = $INSTANCE;
update psprcsque  set runstatus = '10', PRCSRTNCD = 0 where prcsinstance = $INSTANCE;
commit;
exit
EOF
                    fi
               else
                    /bin/cp /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE2.rcv /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE2
                    # flag as successful
                    $ORACLE_HOME/bin/sqlplus -S $ACCESSID/$ACCESSPSWD@$DBNAME <<EOF
update psprcsrqst set runstatus = '9', PRCSRTNCD = 0, ENDDTTM = sysdate, LASTUPDDTTM = sysdate where prcsinstance = $INSTANCE;
update psprcsque  set runstatus = '9', PRCSRTNCD = 0 where prcsinstance = $INSTANCE;
commit;
exit
EOF
               fi
          # archive a copy of it, so that the next run can remove the file and we don't lose the history
          /bin/mv /path/to/pshome/appserv/prcs/$DBNAME/files/$FTPFILE2.rcv /path/to/pshome/appserv/prcs/$DBNAME/files/archive/$FTPFILE2.rcv.$DATE
          /bin/gzip /path/to/pshome/appserv/prcs/$DBNAME/files/archive/$FTPFILE2.rcv.$DATE
     else
          # no file received flag the process as unsuccessful
          $ORACLE_HOME/bin/sqlplus -S $ACCESSID/$ACCESSPSWD@$DBNAME <<EOF
update psprcsrqst set runstatus = '10', PRCSRTNCD = 0, ENDDTTM = sysdate, LASTUPDDTTM = sysdate where prcsinstance = $INSTANCE;
update psprcsque  set runstatus = '10', PRCSRTNCD = 0 where prcsinstance = $INSTANCE;
commit;
exit
EOF
     fi
else
     #FTPXFR is neither 'get' nor 'put', error
     echo "I can get or put but I don't understand" $FTPXFR
     $ORACLE_HOME/bin/sqlplus -S $ACCESSID/$ACCESSPSWD@$DBNAME <<EOF
update psprcsrqst set runstatus = '3', ENDDTTM = sysdate, LASTUPDDTTM = sysdate where prcsinstance = $INSTANCE;
update psprcsque  set runstatus = '3', PRCSRTNCD = 0 where prcsinstance = $INSTANCE;
commit;
exit
EOF
fi

#Remove input file
rm $1