Code:
#!/bin/bash
body_csv="/authlistener/ProdA/service/queryRS.csv"
body_html="/authlistener/ProdA/service/queryRS.html"
attach_csv="/authlistener/ProdA/service/attchment.csv"
attach_html="/authlistener/ProdA/service/attachment.html"
from="venkata.maddela@cgi.com"
to="venkata.maddela@cgi.com"
subject="Monthly Invoice"
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 > $body_html <<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>
<p><b>Dear Cindy/Candy,</b></p>
<p>Please find the attached report</p>
EOF
cat > $attach_html <<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
getResultSet()
{
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
$QUERY
spool off
EOF
}
getAttachmentResult()
{
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
$ATT_QUERY
spool off
EOF
}
convertCSV2HTML()
{
[ "$#" -ne 1 ] && exit -1
file=$1
echo "<table border=1>"
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 $body_csv
getAttachmentResult $attach_csv
#Converting CSV to HTML Table format
convertCSV2HTML $body_csv >> $body_html
convertCSV2HTML $attach_csv >> $attach_html
body=`cat $body_html`
get_mimetype(){
file --mime "$1" | sed 's/.*: //'
}
sendWithAttachment()
{
declare -a attachments
attachments=( "$1" )
{
printf '%s\n' "From: $from
To: $to
Subject: $subject
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary=\"$boundary\"
--${boundary}
Content-Type: text/html; 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
printf '%s\n' "--${boundary}--"
} | sendmail -t -oi #to send email with attachment as html
}
sendWithAttachment $attach_html
rm $body_csv
rm $body_html
rm $attach_csv
rm $attach_html
