UNIX for Advanced & Expert Users

## The below path is defined for config file.
. /****/****/****/****/****/****/.config_****.env

## Below variables are populated from the config file defined above.
DBUSER=$DBUSR
DBPASS=$DBPWD
SYSID=$SYSTEMID
tgtpath=$path
tablenamepath=$Tablenamefilepath
TABLE_FILE=$Table_NAME_EXTERNALID
logfile=$errorlog
Emailuser_BE=$Emailuser_BE
Emailuser_NL=$Emailuser_NL
Emailuser_DK=$Emailuser_DK
zippath=$zippath
logfiledirectory=$log_dir


## Below variable is assigned from the argument passed while executing the script.
Countrycode=$1


## Below variables are assigned dynamically as and when the script is called
allfile=FF_ErrorReport_****_$(date +"%Y%m%d%H%M")_${Countrycode}.csv
allfilezip=FF_ErrorReport_****_$(date +"%Y%m%d%H%M")_${Countrycode}.csv.zip
time=$(date +"%Y%m%d%H%M%S")
today=$(date +"%Y%m%d")



if [ $Countrycode == **** ]
then
Emailuser=$Emailuser_****
CountryName=****
elif [ $Countrycode == **** ]
then
Emailuser=$Emailuser_****
CountryName=****
elif [ $Countrycode == **** ]
then
Emailuser=$Emailuser_****
CountryName=****
elif [ $Countrycode == **** ]
then
Emailuser=$Emailuser_****
CountryName=****
elif [ $Countrycode == **** ]
then
Emailuser=$Emailuser_****
CountryName=****
elif [ $Countrycode == **** ]
then
Emailuser=$Emailuser_****
CountryName=****
else
echo 'Please enter country code as either BE, NL or DK these are the country codes present in BENO. If you want to generate the file for any other OPU please contact the L2 support team.'
fi


email_with_attachment ()
{
email_body=" 
Hi All,\n
\n
**** Code $Countrycode\n
\n
   Reject Report File Name:$allfile\n
\n 
   Execution Time=$time\n
\n
This email is automatically generated. Please do not reply.\n
\n
Best Regards\n
-Integration Team "

(echo -e ${email_body};uuencode $zippath$allfilezip $allfilezip) | mail -s "The $allfile file is generated and mail is sent for $(date +"%Y%m%d") run for $CountryName is successful." $Emailuser

}
## End of email_with_attachment function




email_without_attachment ()
{
email_body=" 
Hi All,\n
\n
The Target file generated for today's run was more than 10 MB, so it is not attached. Please find the reject report in the following path:\n
\n
   Reject Report File Name:$zippath
\n
   Reject Report File Name:$allfile\n
\n 
   Execution Time=$time\n
\n
This email is automatically generated. Please do not reply.\n
\n
Best Regards\n
-Integration Team "

(echo -e ${email_body}) | mail -s "The $allfile file is generated and mail is sent for $(date +"%Y%m%d") run for $CountryName is successful." $Emailuser

}
## End of email_without_attachment function



## The below function is defined to log into the database and get the data according to the sql query.
errorsql () 
{

errorreport=FF_ErrorReport_Veeva

## This function is the InitCap function for unix
firstvalue=`echo $2 | awk -F"\_" '{print $3}' | awk '{print toupper(substr($1,1,1)) tolower(substr($1,2))}'`
secondvalue=`echo $2 | awk -F"\_" '{print $4}' | awk '{print toupper(substr($1,1,1)) tolower(substr($1,2))}'`
thirdvalue=`echo $2 | awk -F"\_" '{print $5}' | awk '{print toupper(substr($1,1,1)) tolower(substr($1,2))}'`
fourthvalue=`echo $2 | awk -F"\_" '{print $6}' | awk '{print toupper(substr($1,1,1)) tolower(substr($1,2))}'`

interfacename="${firstvalue}${secondvalue}${thirdvalue}${fourthvalue}"

tgtfile="${errorreport}_${interfacename}"_Load_$(date +"%Y%m%d%H%M%S")_${Countrycode}_BENO.csv

sqlplus -s $DBUSER/$DBPASS@$SYSID >  $logfiledirectory/$logfile  << EOF
SET HEADING OFF
SET SERVEROUTPUT ON
SET VERIFY OFF
SET TERMOUT OFF
SET TRIMSPOOL OFF
SET FEEDBACK OFF
SET LINESIZE 5000
WHENEVER SQLERROR EXIT FAILURE;
spool  $tgtpath/$tgtfile
SELECT 'EXTERNAL_ID_VOD__C,T_SRC_COUNTRY,T_SRC_SYSTEM,T_REC_ACTION,T_STATUS,T_REC_CREATE_DT,T_ETL_JOB_ID,T_REC_UPDATE_DT,T_LAYER_INDCTR,T_REJECT_REASON' FROM DUAL;
SELECT $3||','||T_SRC_COUNTRY||','||T_SRC_SYSTEM||','||T_REC_ACTION||','||T_STATUS||','||T_REC_CREATE_DT||','||T_ETL_JOB_ID||','||T_REC_UPDATE_DT||','||T_LAYER_INDCTR||','||T_REJECT_REASON 
FROM CRM_ODS_BX.$2 
WHERE T_STATUS = 'OPEN' 
AND T_LAYER_INDCTR IN ('ODS','VEEVA')
AND T_SRC_COUNTRY = '$1'
AND $3 IS NOT NULL;
spool off

EOF

}



 table which ********.
for i in `cat ${tablenamepath}/${TABLE_FILE}`
do
tablename=`echo $i | cut -d "," -f1`
externalid=`echo $i | cut -d "," -f2`
errorsql $Countrycode $tablename $externalid
done
## End of for loop.



zip -9 -r $zippath$allfilezip $tgtpath/FF_ErrorReport_Veeva*$today*_${Countrycode}_BENO.csv



Filesize=`ls -ltr $zippath$allfilezip | cut -d ' ' -f5`



if [ $Filesize -le 10485760 ]
then
email_with_attachment
else
email_without_attachment
fi

env|sort>/tmp/$$