Code:
#!/bin/bash
CSV="/authlistener/ProdA/service/queryRS.csv"
OP="/authlistener/ProdA/service/queryRS.html"
att_csv="/authlistener/ProdA/service/attchment.csv"
att_op="/authlistener/ProdA/service/attachment.html"
to="venkata.maddela@cgi.com"
subject="Daily Report"
boundary="ZZ_/afg6432dfgkl.94531q"
QUERY="SELECT DECODE(SUBSTR(TRAN.iin,5,2),'25','34',mbr.mbr_no) Country_No,
DECODE(SUBSTR(TRAN.iin,5,2),'25','Hungary',mbr.name) Country_Name,
CASE TRAN.DENY_fLAG
WHEN 'N' THEN 'Approved'
ELSE 'Declined'
END Status,
SUM (TRAN.TRAN_AMOUNT) TOTAL_AMOUNT, COUNT(*) TOTALTRANSACTIONS
FROM TRAN
LEFT OUTER JOIN MBR ON substr(TRAN.iin,5,2) = (MBR.mbr_no)
LEFT OUTER JOIN CONDITION ON TRAN.CONDITION_ID = condition.condition_id
WHERE POST_DATE BETWEEN TO_DATE('20170601', 'yyyymmdd')
AND TO_DATE('20170630', 'yyyymmdd')
AND TRAN.post_ts BETWEEN TO_DATE('01 JUN 2017 00:00:00', 'DD MON YYYY HH24:MI:SS') AND TO_DATE('30 JUN 2017 23:59:59', 'DD MON YYYY HH24:MI:SS')
GROUP BY DECODE(SUBSTR(TRAN.iin,5,2),'25','34',mbr.mbr_no),
DECODE(SUBSTR(TRAN.iin,5,2),'25','Hungary',mbr.name),
TRAN.DENY_fLAG
ORDER BY Country_Name;"
att_query="SELECT DECODE(SUBSTR(TRAN.iin,5,2),'25','34',mbr.mbr_no) Country_No,
DECODE(SUBSTR(TRAN.iin,5,2),'25','Hungary',mbr.name) Country_Name,
CASE TRAN.DENY_fLAG
WHEN 'N' THEN 'Approved'
ELSE 'Declined'
END Status,
SUM (TRAN.TRAN_AMOUNT) TOTAL_AMOUNT, COUNT(*) TOTALTRANSACTIONS
FROM TRAN
LEFT OUTER JOIN MBR ON substr(TRAN.iin,5,2) = (MBR.mbr_no)
LEFT OUTER JOIN CONDITION ON TRAN.CONDITION_ID = condition.condition_id
WHERE POST_DATE BETWEEN TO_DATE('20170601', 'yyyymmdd')
AND TO_DATE('20170630', 'yyyymmdd')
AND TRAN.post_ts BETWEEN TO_DATE('01 JUN 2017 00:00:00', 'DD MON YYYY HH24:MI:SS') AND TO_DATE('30 JUN 2017 23:59:59', 'DD MON YYYY HH24:MI:SS')
GROUP BY DECODE(SUBSTR(TRAN.iin,5,2),'25','34',mbr.mbr_no),
DECODE(SUBSTR(TRAN.iin,5,2),'25','Hungary',mbr.name),
TRAN.DENY_fLAG
ORDER BY Country_Name;"
CON_STRING="atlas/atlas@atlasprd"
cat > $OP <<EOF
<!doctype html public �-//w3c//dtd html 4.0 transitional//en�>
<html>
<head>
<style>
table {
border: 1px solid black;
border-collapse: collapse;
}
th, td {
text-align: left;
padding: 8px;
}
tr:nth-child(even){background-color: #f2f2f2}
th {
background-color: #4CAF50;
color: white;
}
</style>
</head>
<body>
EOF
cat > $att_op <<EOF
<!doctype html public �-//w3c//dtd html 4.0 transitional//en�>
<html>
<head>
<style>
table {
border: 1px solid black;
border-collapse: collapse;
}
th, td {
text-align: left;
padding: 8px;
}
tr:nth-child(even){background-color: #f2f2f2}
th {
if(document.getElementById("STATUS").value='APPROVED'){
background-color: #4CAF50
}else{
background-color: red
}
color: white;
}
</style>
</head>
<body>
EOF
#Function to fetch the result set from the query
getResultSet()
{
echo $2
#echo -e "Query :=========>"$QUERY
sqlplus -s $CON_STRING <<EOF
set term off
set echo off
set underline off
set pagesize 0
set sqlprompt "
set lines 1000 pages 1000
set linesize 1000
set colsep ","
set trimspool on
set heading on
set newpage 0
set headsep off
set feedback off
spool $1
$2
spool off
EOF
}
convertCSV2HTML()
{
[ "$#" -ne 1 ] && exit -1
file=$1
echo "<table>"
head -n 1 $file | \
sed -e 's/^/<tr><th>/' -e 's/,/<\/th><th>/g' -e 's/$/<\/th><\/tr>/'
tail -n +2 $file | \
sed -e 's/^/<tr><td>/' -e 's/,/<\/td><td>/g' -e 's/$/<\/td><\/tr>/'
echo "</table>"
}
#trigger SQL Script
getResultSet $CSV $QUERY
getResultSet $att_csv $att_query
#Converting CSV to HTML Table format
convertCSV2HTML $CSV >> $OP
convertCSV2HTML $att_csv >> $att_op
#body=`cat $OP`
get_mimetype(){
# warning: assumes that the passed file exists
file --mime "$1" | sed 's/.*: //'
}
sendWithAttachment()
{
declare -a attachments
attachments=( "$1" )
cat $OP
echo
# Build headers
{
printf '%s\n' "From: $from
To: $to
Subject: $subject
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary=\"$boundary\"
--${boundary}
Content-Type: text/plain; charset=\"US-ASCII\"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
$body
"
for file in "${attachments[@]}"; do
[ ! -f "$file" ] && echo "Warning: attachment $file not found, skipping" >&2 && continue
mimetype=$(get_mimetype "$file")
printf '%s\n' "--${boundary}
Content-Type: $mimetype
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename=\"$file\"
"
base64 "$file"
echo
done
# print last boundary with closing --
printf '%s\n' "--${boundary}--"
} | sendmail -t -oi #to send email with attachment as html
}
sendWithAttachment $att_op
11-23-2017
