SHELL RECORD EXAMPLE

configFile=../../parm/output.config
runFolder=`cat ${configFile}|grep runFolder |awk -F= '{print $2}'`
outputFolder=`cat ${configFile}|grep outputFolder |awk -F= '{print $2}'`
outputFile=${outputFolder}/tolvdl_w_new_.txt
logName=$1
logName=${logName##*/}
logName=${logName%%.*}
logName="${logName}.log"
log=${runFolder}/output_FOLDER/${logName}
table=TABLENAME
fileSign=VDL
DB_SERV=`cat ${configFile}|grep DB_SERV |awk -F= '{print $2}'`
sql_source=`cat ${configFile}|grep sql_OSL |awk -F= '{print $2}'`
sql_result=`cat ${configFile}|grep sql_OIL |awk -F= '{print $2}'`

##replace the deffirent table and file name
sh tableReplace.sh $sql_source $sql_result ${fileSign} ${table}

#zip temp parameter
outputZip=${outputFolder}/zip/
zipPre=VDL_PRE
zipSuf=.zip
#zipAppend=(a b c d e f g h i j)
zipAppend=($(echo {a..z}))
tempNum=0
pageNum=`cat ${configFile}|grep pageNum |awk -F= '{print $2}'`

#read the email config
echo `date` "-------read the output.config file ------" >$log

failStr=`cat ${configFile}|grep fail |awk -F= '{print $2}'`
successStr=`cat ${configFile}|grep success |awk -F= '{print $2}'`
operatorName=`cat ${configFile}|grep operator |awk -F= '{print $2}'`
emailTitle=`cat ${configFile}|grep title |awk -F= '{print $2}'`


echo `date` "-------start to connect the DB and ouput the file ------" >>$log

#
#sqlplus to spool export the file
#
sqlplus -s /@${DB_SERV}<<EOF >>$log 2>&1
WHENEVER SQLERROR exit failure rollback
set echo off
set feedback off
set termout off
set trimspool off
set linesize 1078
set pagesize 0
set heading off
spool $outputFile
@${sql_result}
spool off
EOF

if [ $? -eq 0 ]; then
echo `date` ${successStr} >>$log
else
echo `date` ${failStr} $log>>$log
exit 1
fi

if [ -s $sql_result ]; then
rm $sql_result
fi


#make the file header and foot
lineLen=`cat ${configFile}|grep lineLen |awk -F= '{print $2}'`
dateStr=$(date +%m%d%y)
header=${dateStr}${fileSign}
while [ ${#header} -lt ${lineLen} ]
do
header=${header}0
done

lastLine=$(tail -1 ${outputFile})
rowNum=${lastLine:1:9}

#check the rownum is 0?----
if [ $rowNum = "000000000" ] ; then
tableEmpty=`cat ${configFile}|grep tableEmpty |awk -F= '{print $2}'`
echo `date` ${tableEmpty}---${table}>>$log
rm ${outputFile}
#java com.javamail
exit 1
fi

lastFileNum=`expr $rowNum % $pageNum `
#delete the last row
:|dd of=$outputFile seek=1 bs=$(($(find $outputFile -printf "%s")-$(tail -1 $outputFile|wc -c)))

#split the outputFile 2 million per file

echo `date` "-------start to split the file to ${pageNum} per file ------" >>$log

if [ -s $outputFile ]; then
mkdir ${outputZip} >>$log 2>&1
split -l ${pageNum} $outputFile ${outputZip}temp_ >>$log 2>&1
echo `date` "-------end to split the file to ${pageNum} per file ------" >>$log

#add the file header and footer
folderLen=$(ls -l ${outputZip}|grep "^-"|wc -l)
folderLen=`expr $folderLen - 1 `
for file_a in ${outputZip}/*; do
sed -i "1i${header}" $file_a
tempfoot=""
if [ $folderLen -eq $tempNum ] ; then
while [ ${#lastFileNum} -lt 9 ]
do
lastFileNum=0${lastFileNum}
done
tempfoot="V${lastFileNum}"
else
while [ ${#pageNum} -lt 9 ]
do
pageNum=0${pageNum}
done
tempfoot="V${pageNum}"

fi
while [ ${#tempfoot} -lt ${lineLen} ]
do
tempfoot=${tempfoot}0
done
echo $tempfoot >>$file_a
let tempNum++

done
tempNum=0
##END ADD HEADER AND FOOT
#gzip the file

for file_a in ${outputZip}/*; do
echo `date` "-------start to zip ------${outputFolder}/${zipPre}${zipAppend[$tempNum]}${zipSuf} from ${file_a} ">>$log
fileTarget=${outputZip}/${zipPre}${zipAppend[$tempNum]}.txt
mv ${file_a} ${fileTarget}
zip -qm ${outputFolder}/${zipPre}${zipAppend[$tempNum]}${zipSuf} ${fileTarget} >>$log 2>&1
echo `date` "-------End to zip ------${outputFolder}/${zipPre}${zipAppend[$tempNum]}${zipSuf} from ${fileTarget} ">>$log
let tempNum++

done

rm -rf ${outputZip}
rm -f $outputFile
echo `date` "-------remove the file-----${outputZip} and --${outputFile}">>$log
fi