att_csv="/authlistener/ProdA/service/attchment.csv"
att_op="/authlistener/ProdA/service/attachment.xml"
from="venkata.maddela@cgi.com"
to="venkata.maddela@cgi.com"
subject="test with attachment"
boundary="ZZ_/afg6432dfgkl.94531q"
body="This is the body of our email"
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 > $att_op <<EOF
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author></Author>
  <LastAuthor></LastAuthor>
  <Created>2017-08-13T11:58:30Z</Created>
  <LastSaved>2017-08-13T12:07:08Z</LastSaved>
  <Company>CGI-INDIA</Company>
  <Version>14.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>4680</WindowHeight>
  <WindowWidth>14355</WindowWidth>
  <WindowTopX>360</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
EOF

getResultSet()
{
#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 tmp.csv
$att_query
spool off
EOF
}

convertATTCSV2HTML()
{
[ "$#" -ne 1 ] && f_Usage && exit -1

file=$1

echo "<table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="10" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">"
head -n 1 $file | \
    sed -e 's/^/<Row ss:Index="5"><Cell ss:Index="2"><Data ss:Type="String">/' -e 's/,/<\/Data><\/Cell><Cell><Data ss:Type="String">/g' -e 	
's/$/<\/Data><\/Cell><\/R    ow>/'
tail -n +2 $file | \
    sed -e 's/^/<Row ss:Index="5"><Cell ss:Index="2"><Data ss:Type="String">/' -e 's/,/<\/Data><\/Cell><Cell><Data ss:Type="String">/g' -e 					
's/$/<\/Data><\/Cell><\/R    ow>/
'
echo "</table>"
}

#trigger SQL Script
getResultSet

#Converting CSV to HTML Table format
#convertATTCSV2HTML tmp.csv >> $att_op

cat >> $att_op <<EOF
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Selected/>
   <TopRowVisible>1</TopRowVisible>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>10</ActiveRow>
     <ActiveCol>1</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
EOF

get_mimetype(){
  # warning: assumes that the passed file exists
  file --mime "$1" | sed 's/.*: //'
}

sendWithAttachment()
{
declare -a attachments

attachments=( "$1" )

# 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   # one may also use -f here to set the envelope-from
}

#unix2dos $att_op $att_op
mv tmp.csv tmp.xls
sendWithAttachment tmp.xls

#rm tmp.csv