#!/bin/ksh


. ~/.profile
export FILE_DT=`date +%b%d-%Y-%H%M%S`
export FILE_PREFIX="process_extract"
export PROCESS_EXTRACT_DIR=$DATA_HOME/process/extract
export PROCESS_EXTRACT_AR_DIR=$DATA_HOME/process/archive
export PROCESS_EXTRACT_FILE="$FILE_PREFIX_$FILE_DT.txt"
export PROCESS_LOG_DIR=$LOG_HOME/process_extract
export PROCESS_LOG=$PROCESS_LOG_DIR/PROCESS_EXTRACT_log.$FILE_DT

PROC_STATUS="0"
#SFTP_DELAY=180  # how long to sleep; 180 = 3 minutes

if test -r $PROCESS_EXTRACT_DIR/$FILE_PREFIX*.txt
then                                    
    rm -f $PROCESS_EXTRACT_DIR/$FILE_PREFIX*.gz
    /usr/contrib/bin/gzip $PROCESS_EXTRACT_DIR/$FILE_PREFIX*.txt
fi                                      

# LOG FILE CLEAN UP
if test -r $PROCESS_LOG_DIR/PROCESS_EXTRACT_log.*
then                                    
    rm -f $PROCESS_LOG_DIR/PROCESS_EXTRACT_log*.gz
    /usr/contrib/bin/gzip $PROCESS_LOG_DIR/PROCESS_EXTRACT_log.*
fi                                      

#  REDIRECT STDOUT STDERR

exec 1>$PROCESS_LOG
exec 2>$PROCESS_LOG

echo "__________"
echo "executing $SCRIPTS/PROCESS_EXTRACT.ksh on $ORACLE_SID"
echo 'START DATE/TIME:  ' `date +%b%d-%Y-%H:%M:%S`

# CALL STORED PROCEDURE
if [ "$PROC_STATUS" = "0" ]
then
    echo "calling stored procedure to create csv file"
    $SCRIPTS_SQL/process_extract.sql > $PROCESS_LOG_DIR/process_extract.out

    # CHECK RETURN FOR SUCCESS
    wait
    if grep 'PROCESS_EXTRACT_FAILURE' $PROCESS_LOG_DIR/process_extract.out || grep 'ORA-'  
$PROCESS_LOG_DIR/process_extract.out
    then
        # on error notify operator 
        echo `date +%b%d-%Y-%H:%M:%S`" CMS/PROCESS EXTRACT PL/SQL PROCESS ERROR"
	 mailx -s "CRITICAL - $ORACLE_SID:  CMS/PROCESS EXTRACT PROCESS FAILURE" $IT_ML_LST < $PROCESS_LOG
        PROC_STATUS="1"
    else
        echo `date +%b%d-%Y-%H:%M:%S`" CMS/PROCESS EXTRACT PL/SQL PROCESS SUCCESS"
        #sleep $SFTP_DELAY  # wait for file permissions job to run

     fi
fi

# VERIFY THAT THE FILE IS PRESENT AND FIND OUT ITS SHORT NAME
if [ "$PROC_STATUS" = "0" ]
then
    echo 'Getting a list of CSV files'
    file_list=`ls $PROCESS_EXTRACT_DIR/*.csv | sed 's!.*\/\([^\/]*\).*!\1!g'`
fi

# Declare SFTP parameters
export USER_PORT="Port=12314"
export USER_HOST=$USER@$FTP_HOST
export UPLOAD_DIR=$PROCESS_UPLOAD_DIR
export FTPSCRIPT="process_extract_ftpscript.txt"
export HPOV_DIR="/opt/OV/bin/OpC"
 
# PROCESS PROCESS EXTRACT FILES
for PROCESS_EXTRACT_FILE in $file_list
do

   ux2dos $PROCESS_EXTRACT_FILE
   echo 'ux2dos executed'

   echo 'processing file  ' $PROCESS_EXTRACT_FILE
   
  # Create SFTP script to send process extract files to upload directory#
  echo 'create sftp script'
  print "cd $UPLOAD_DIR" > $SCRIPTS/ftp/$FTPSCRIPT
  print "lcd $PROCESS_EXTRACT_DIR" >> $SCRIPTS/ftp/$FTPSCRIPT
  if [ -s $PROCESS_EXTRACT_DIR/$PROCESS_EXTRACT_FILE ]
  then

    print "put $PROCESS_EXTRACT_FILE $PROCESS_EXTRACT_FILE" >> $SCRIPTS/ftp/$FTPSCRIPT
  else 
    echo 'Unable to locate file '$PROCESS_EXTRACT_FILE
  fi
  print "bye" >> $SCRIPTS/ftp/$FTPSCRIPT

  # Running FTPSCRIPT
  echo 'Calling FTP script for ' $PROCESS_EXTRACT_FILE
  cd $DATA_HOME
  wait
  if [ "$PROC_STATUS" = "0" ]
  then
    sftp -b $SCRIPTS/ftp/$FTPSCRIPT -o $USER_PORT $USER_HOST
    sftp_rc=$?
    echo "ftp return code is " $sftp_rc
    if [ "$sftp_rc" = "0" ]
    then
      echo `date +%b%d-%Y-%H:%M:%S`" $PROCESS_EXTRACT_FILE  file FTP TO EFT LOCATION SUCCESS"
      # Find out the name of the archived file
      export EXTR_FN_DT=`date +%b%d-%Y-%H:%M:%S`
      IDX=`expr index $PROCESS_EXTRACT_FILE 20`
      IDX=`expr $IDX - 1`
      PROCESS_EXTRACT_FILE_AR=`expr substr $PROCESS_EXTRACT_FILE 1 $IDX`$EXTR_FN_DT
    
 	 # ARCHIVE THE FILE
   	    mv $PROCESS_EXTRACT_DIR/$PROCESS_EXTRACT_FILE $PROCESS_EXTRACT_AR_DIR/$PROCESS_EXTRACT_FILE_AR
	      /usr/contrib/bin/gzip $PROCESS_EXTRACT_AR_DIR/$PROCESS_EXTRACT_FILE_AR
    else
      echo `date +%b%d-%Y-%H:%M:%S`"$PROCESS_EXTRACT_FILE file FTP TO EFT LOCATION FAILURE"
      PROC_STATUS="1"
      cat $BATCH_LOG | mailx -s "PROCESS EXTRACT PROCESS FAILURE ON $ORACLE_SID"  $PROCESS_ML_LST
    fi
  else
    echo 'Stopping FTP as there was an error'
  fi

  # REMOVE FTP SCRIPT
  #rm $SCRIPTS/ftp/$FTPSCRIPT
  STOPFLAG="1"

done

echo 'ready to check overall process and do clean-up'
echo "PROC_STATUS is " $PROC_STATUS
echo 'STOP DATE/TIME:  ' `date +%b%d-%Y-%H:%M:%S`
echo ' '



# CHECK OVERALL PROCESS

if [ "$PROC_STATUS" = "1" ]
then
    # notify operator
    echo `date +%b%d-%Y-%H:%M:%S`" CMS/PROCESS EXTRACT PROCESS FAILURE"
    #mailx -s "$ORACLE_SID:  CMS/PROCESS EXTRACT PROCESS FAILURE" $IT_ML_LST < $PROCESS_LOG
    #echo "Please refer to e-mail or log for more info." > ./tmp_mail_msg
    #mailx -s "$ORACLE_SID:  CMS/PROCESS EXTRACT PROCESS FAILURE" $IT_ML_LST_PAGER < ./tmp_mail_msg
   # mailx -s "CRITICAL - $ORACLE_SID:  CMS/PROCESS EXTRACT PROCESS FAILURE" $IT_ML_LST < $PROCESS_LOG
    echo "Please refer to e-mail or log for more info." > ./tmp_mail_msg
    ###mailx -s "CRITICAL - $ORACLE_SID:  CMS/PROCESS EXTRACT PROCESS FAILURE" $IT_ML_LST_PAGER < ./tmp_mail_msg
elif [ "$PROC_STATUS" = "2" ]
then
    # no action for empty file
    :
else
    # log success
    echo `date +%b%d-%Y-%H:%M:%S`" CMS/PROCESS EXTRACT PROCESS SUCCESS"
  #  mailx -s "$ORACLE_SID:  CMS/PROCESS EXTRACT PROCESS SUCCESS" $IT_ML_LST < $PROCESS_LOG
    echo "File $PROCESS_FILE has been created and transmitted." > ./tmp_mail_msg
    ###mailx -s "$ORACLE_SID:  EXTRACT  PROCESS" $ENMASSE_BS_ML_LST < ./tmp_mail_msg
fi                           

# GET DBMS_OUTPUT RESULTS

cat $PROCESS_LOG_DIR/process_extract.out >> $PROCESS_LOG
#rm -f $PROCESS_LOG_DIR/process_extract.out
rm -f ./tmp_mail_msg

exit