Code:
#!/bin/bash
echo $1
echo $2
OIFS="$IFS"
IFS=$'\n'
#sudo su
RECEIVED_DIR="/storage/sas_source/SDTM-Development/FileWatcher/GCSDW_InBound/"
REJECTED_DIR="/storage/sas_source/SDTM-Development/FileWatcher/GCSDW_Rejected/"
PROCESSED_DIR="/storage/sas_source/SDTM-Development/FileWatcher/LSH_Processing/"
GCSDW_TEMP="/storage/sas_source/SDTM-Development/FileWatcher/GCSDW_Temp/"
GCSDW_Inboundzip="/storage/sas_source/SDTM-Development/FileWatcher/GCSDW_Temp/"
export seq
export file_ext
#sudo su
echo "filewatcher.sh started"
export ORACLE_HOME=/storage/software/oracle/product/11.2.0/client_1
export PATH=$ORACLE_HOME/bin:$PATH
cd "$RECEIVED_DIR"
for i in `ls`
do
#sleep 59s
echo "file name: $i"
filetype=`echo $i| cut -d'_' -f4`
if [ `echo $filetype | grep -i 'Analysis'` ];then
continue
fi
seq=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set serveroutput on
set pagesize 0 feedback off verify off heading off echo off;
select clinicalglobal.seq_jobid.nextval from dual;
exit;
EOM`
echo "$seq"
FILESIZE=$(stat -c%s "$i")
echo "size of a file is: $FILESIZE"
compound=`echo $i| cut -d'_' -f1`
study=`echo $i| cut -d'_' -f2`
datatype=`echo $i| cut -d'_' -f5|cut -d'.' -f1`
file_ext=`echo "$i" | sed 's/.*\(...\)/\1/'`
file_last_string=`echo $i| cut -d'_' -f6`
version=`sed 's/.\{4\}$//' <<< $file_last_string`
filetype=`echo $i| cut -d'_' -f4`
echo "version is:"$version
echo "compound is:$compound"
echo "study is:$study"
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set heading off
set feedback off
set serveroutput on
SET LINESIZE 320
SET PAGESIZE 4000
insert into CLINICALGLOBAL.AUTOMATION_ERROR_LOG(jobid,JOB_NAME,FILE_NAME,FILE_SIZE,COMPOUND_NAME,
STUDY_NAME,FILE_TYPE,VERSION,FILERECEIVED_DT,UnixFileExtnChk,
UnixFileExtnChkDT,UnixFileExtnChkcomments,SASProcessing,SASProcessingDT,
SASProcessingComments,LSHJobId
)
VALUES($seq,'Datalaod','$i','$FILESIZE','$compound',
'$study','$datatype','$version',sysdate,null,null,null,null,null,null,null
);
commit;
exit;
EOM`
CNT=`echo $i | awk -F"_" '{print NF}'`
echo "Count is $CNT"
if [ ${CNT} == 6 ];then
if [ `echo $file_ext | grep -i 'XPT'` ] || [ `echo $file_ext | grep -i 'cpt'` ] || [ `echo $file_ext | grep -i 'zip'` ];then
echo "correct file extension"
else
mv "$i" "$REJECTED_DIR"
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET UnixFileExtnChk ='Failure',UnixFileExtnChkcomments ='File is not a valid .CPT or .XPT or .ZIP file.',
UNIXFILEEXTNCHKDT = SYSDATE where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
continue;
fi
fi
#CNT=`echo $i | awk -F"_" '{print NF}'`
if [ ${CNT} -eq 6 ] || [ ${CNT} -eq 3 ]; then
echo "number of fields in filename is ${CNT}"
else
echo "moving file to reject directory,incorrect naming convention"
mv "$i" "$REJECTED_DIR"
echo $?
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET UnixFileExtnChk ='Failure',UnixFileExtnChkcomments ='incorrect File Naming Convention'
,UNIXFILEEXTNCHKDT = sysdate where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
continue
fi
if [ ${CNT} -eq 6 ]; then
echo "datatype is:$datatype"
if [ `echo "$datatype" | grep -i 'SDTM'` ] || [ `echo "$datatype" | grep -i 'Raw'` ] || [ `echo "$datatype" | grep -i 'Ongoing'` ];then
echo "datatype is:"$datatype
if [ `echo "$datatype" | grep -i 'Raw'` ] && [ "$version" = "Version" ]; then
mv "$i" "$REJECTED_DIR"
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET UnixFileExtnChk ='Failure',UnixFileExtnChkcomments ='Invalid File naming convention.Version of the RAW data file can not be VERSION.'
where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
continue
fi
else
mv "$i" "$REJECTED_DIR"
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET UnixFileExtnChk ='Failure',UnixFileExtnChkcomments ='incorrect datatype' where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
continue
fi
if [ `echo $file_ext | grep -i 'XPT'` ] || [ `echo $file_ext | grep -i 'cpt'` ]; then
echo "file_ext is:"$file_ext
mv "$i" "$PROCESSED_DIR"
echo $?
echo "going to update db table"
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET UnixFileExtnChk ='Success',unixfileextnchkdt = sysdate
,sasprocessing ='Success',SASProcessingDT = sysdate where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
#echo "$sql"
if [ $? -eq 0 ]
then
echo "Updated Cycle successfully\n\n"
fi
elif [ `echo $file_ext | grep -i 'ZIP'` ]; then
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET UnixFileExtnChk ='Success',unixfileextnchkdt = sysdate where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
cd /sas/SASHomeNew/SASFoundation/9.4/samples/base/Lsh/SAS_TEMP
#echo $?
if [ `echo $? -eq 0` ]
then
echo "removing files"
rm -rf *
echo $?
fi
cd $RECEIVED_DIR
unzip -o $i -d /sas/SASHomeNew/SASFoundation/9.4/samples/base/Lsh/SAS_TEMP
if [ `echo $? -eq 0` ]
then
echo "file $i has been unzipped successfully"
mv $RECEIVED_DIR$i $GCSDW_Inboundzip
#k=`echo $i | cut -d'.' -f1`
k=${i%.*}
#mv "$k" "$SAS_TEMP"
echo "k $k"
if [ -d "/sas/SASHomeNew/SASFoundation/9.4/samples/base/Lsh/SAS_TEMP/$k" ]; then
dir=/sas/SASHomeNew/SASFoundation/9.4/samples/base/Lsh/SAS_TEMP/$k
else
dir=/sas/SASHomeNew/SASFoundation/9.4/samples/base/Lsh/SAS_TEMP
fi
echo "directory is $dir"
cd /storage/sas_source/SDTM-Development/FileWatcher
/sas/SASHomeNew/SASFoundation/9.4/bin/sas_u8 ConvertZiptoCpt.sas -set InFilePath $dir -set OutFilePath "/sas/SASHomeNew/SASFoundation/9.4/samples/base/Lsh/LSH_TEMP" -set InFileName $k
if [ -s /sas/SASHomeNew/SASFoundation/9.4/samples/base/Lsh/LSH_TEMP/$k.cpt ]
then
echo "File is available"
mv /sas/SASHomeNew/SASFoundation/9.4/samples/base/Lsh/LSH_TEMP/"$k".cpt $PROCESSED_DIR
i=$k.cpt
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET sasprocessing ='Success',SASProcessingDT = sysdate,unixfileextnchk = 'Success',unixfileextnchkdt = sysdate where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
else
echo "File not found,error in converting file to cpt format"
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET sasprocessing ='Failure',SASProcessingDT = sysdate,SASProcessingComments ='sas program didnt complete successfully' where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
fi
else
echo "error in unzipping the file"
fi
else
echo "moving file to rejected dir"
mv "$i" "$REJECTED_DIR"
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET UnixFileExtnChk ='Failure',UnixFileExtnChkcomments ='incorrect extension'
,unixfileextnchkdt = sysdate where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
continue
fi
fi
#echo "here"
if [ ${CNT} -eq 3 ]; then
file_ext=`echo "$i" | grep -o '.\{8\}$'`
if [ `echo $file_ext | grep -i 'SAS7BDAT'` ]; then
echo "file extension is $file_ext"
datatype=`echo $i| cut -d'_' -f2`
echo "datatype is $datatype"
newfile="$compound"_"$datatype"."$file_ext"
echo "new file is $newfile"
if [ `echo $datatype | grep -i 'e2'` ];then
cp "$RECEIVED_DIR$i" "$PROCESSED_DIR$newfile"
mv "$RECEIVED_DIR$i" "$GCSDW_TEMP"
i=$newfile
echo $?
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET UnixFileExtnChk ='Success',UnixFileExtnChkcomments ='Filemoved to process directory'
,unixfileextnchkdt = sysdate,file_type ='$file_ext',sasprocessing ='Success',SASProcessingDT = sysdate where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
else
mv "$i" "$REJECTED_DIR"
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET UnixFileExtnChk ='Failure',UnixFileExtnChkcomments ='incorrect datatype',UnixFileExtnChkDT = sysdate ,file_type ='$file_ext' where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
fi
else
mv "$i" "$REJECTED_DIR"
sql=`sqlplus -s clinicalglobal/$1@$2 <<EOM
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
declare
l_jobid number;
BEGIN
UPDATE CLINICALGLOBAL.AUTOMATION_ERROR_LOG SET UnixFileExtnChk ='Failure',UnixFileExtnChkcomments ='incorrect filenaming convention'
,unixfileextnchkdt = sysdate ,file_type ='$file_ext' where
jobid = $seq ;
commit;
END;
/
exit;
EOM`
continue
fi
fi
echo "calling db procedure for file $i"
run_sql1=`sqlplus clinicalglobal/$1@$2 <<EOM
whenever sqlerror exit sql.sqlcode;
set verify off
set heading off
set feedback off
set serveroutput on size 1000000
SET LINESIZE 320
SET PAGESIZE 4000
set feed off
declare
l_ret_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(4000);
begin
dbms_output.put_line('running database procedure');
CDR_PUB_API_INITIALIZATION.ENABLEAPIS(
P_API_VERSION=>1,
P_INIT_MSG_LIST=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
p_COMMIT=>CDR_PUB_DEF_CONSTANTS.G_FALSE,
P_VALIDATION_LEVEL=>CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,
X_RETURN_STATUS => l_ret_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data
);
--if UPPER('$datatype') = 'SDTM' then
clinicalglobal.PKG_SDTM_LOAD.PRC_SDTM_LOAD(pi_file_name => '$i',
pi_file_path => '$PROCESSED_DIR',
pi_study => '$study',
pi_compound => '$compound',
pi_sdtm_ver => '$version',
pi_datatype => '$datatype',
pi_jobid => $seq,
pi_debug_flag =>'Y'
);
--elsif UPPER('$datatype') = 'RAW' then
-- DBMS_OUTPUT.PUT_LINE('Calling RAW data load');
--end if;
end;
/
spool off
exit;
EOM`&
echo $ERRORCODE
echo $run_sql
done
10-20-2016