1.使用工具sendEmail 参照博客 http://my.oschina.net/forrest420/blog/609874 下载安装sendEmail-v1.56
2.shell脚本如下:
#!/usr/bin/env bash
export LANG=zh_CN.UTF-8
EXE_DATE=`date -d "-1 day" +%Y-%m-%d`
EXE_MONTH=`date -d "-1 day" +%Y-%m`
[ "$#" -ne 0 ] && EXE_DATE=$1
[ "$#" -gt 1 ] && EXE_MONTH=$2
echo "EXE_DATE is ${EXE_DATE},EXE_MONTH is ${EXE_MONTH}"
current_bin_path="`dirname "$0"`"
oneProject_every_day_output_file=${current_bin_path}/oneProject_process_every_day.txt
oneProject_total_output_file=${current_bin_path}/oneProject_process_unitl_today.txt
oneProject_mail_output_file=${current_bin_path}/oneProject_mail.txt
#mysql_utf8文件的内容为:
#mysql -hxxx -uyyy -pzzz -Pqqqq -Dwwww --default-character-set=utf8 -N -e
EXE_SQL_PROD="`cat /etc/xxx/mysql_utf8 ` "
#计算每天的进度值process_of_every_day,截止到今日凌晨的总进度值process_of_today
get_day_of_month=`date -d "${EXE_DATE} -0 day" +%d`
get_month_of_year=`date -d "${EXE_DATE} -0 day" +%m`
get_year=`date -d "${EXE_DATE} -0 day" +%Y`
days_in_month=`cal ${get_month_of_year} ${get_year} | awk 'NF{out=$NF;}END{print out}'`
process_of_every_day=`gawk -v x=100 -v y=${days_in_month} 'BEGIN{printf "%.2f\n",x/y}'`
process_of_today=`gawk -v x=${process_of_every_day} -v y=${get_day_of_month} 'BEGIN{printf "%.2f\n",x*y}'`
echo "process_of_every_day is ${process_of_every_day},process_of_today is ${process_of_today}"
#导出oneProject每天的数据到文件中
sql="select day as '日期',
round(act_sale) as '交易额',
'${process_of_every_day}%' as '时间进度',
allcustomer as '顾客数',
ordernum as '订单数',
round(act_sale/ordernum) as '客单价',
concat(round((ordernum/uv)*100,2),'%') as '转化率',
pv as '点击量',
uv as '点击用户数',
concat(round(secondjumprate*100,2),'%') as '二跳率'
from aaa.bbb
where day between '${EXE_MONTH}-01' and '${EXE_DATE}' and
xxx='oneProject'
"
echo "sql is ${sql}"
${EXE_SQL_PROD} "${sql}" > ${oneProject_every_day_output_file}
#导出oneProject每天的数据到文件中 oneProject总计
sql="select '总计' as '日期',
round(sum(act_sale)) as '交易额',
'${process_of_today}%' as '时间进度',
sum(allcustomer) as '顾客数',
sum(ordernum) as '订单数',
round(sum(act_sale)/sum(ordernum)) as '客单价',
concat(round((sum(ordernum)/sum(uv))*100,2),'%') as '转化率',
sum(pv) as '点击量',
sum(uv) as '点击用户数',
concat(round(sum(secondjumprate*uv)/sum(uv)*100,2),'%') as '二跳率'
from aaa.bbb
where day between '${EXE_MONTH}-01' and '${EXE_DATE}' and
xxx='oneProject'
"
echo "sql is ${sql}"
${EXE_SQL_PROD} "${sql}" > ${oneProject_total_output_file}
#拼装HTML格式文本
head_style="
<html>
<head>
<style>
.table {
border-collapse: collapse;background-color: #fff;
font-size:14px;
}
.table-bordered th,
.table-bordered td {
border: 1px solid #000;
}
.table-bordered th{background:#00b0f4;}
.table-bordered .td_color1{background:#79da4c;}
</style>
</head>
<body>
"
echo "${head_style}" > ${oneProject_mail_output_file}
echo "Hi all~~<br>电商oneProject数据如下~<br>" >> ${oneProject_mail_output_file}
echo "<table class='table table-bordered'>" >> ${oneProject_mail_output_file}
#写入HTML TABLE列头
oneProject_title="日期,交易额,时间进度,顾客数,订单数,客单价,转化率,点击量,点击用户数,二跳率"
head_str=`echo ${oneProject_title} |awk -F ',' '{print "<thead><tr><th>"$1"</th><th>"$2"</th><th>"$3"</th><th>"$4"</th><th>"$5"</th><th>"$6"</th><th>"$7"</th><th>"$8"</th><th>"$9"</th><th>"$10"</th></tr></thead><tbody>"}'`
echo "${head_str}" >> ${oneProject_mail_output_file}
#日期,交易额,时间进度,顾客数,订单数,客单价,转化率,点击量,点击用户数,二跳率
cat ${oneProject_every_day_output_file} |while read record
do
day_var=`echo ${record} |awk '{print $1}'`
get_week=`date -d "${day_var} -0 day" +%w`
if [ ${get_week} -eq 6 ] || [ ${get_week} -eq 0 ];then
content_str=`echo ${record} |awk '{print "<tr><td class=\"td_color1\">"$1"</td><td>"$2"</td><td>"$3"</td><td>"$4"</td><td>"$5"</td><td>"$6"</td><td>"$7"</td><td>"$8"</td><td>"$9"</td><td>"$10"</td></tr>"}'`
echo "${content_str}" >> ${oneProject_mail_output_file}
else
content_str=`echo ${record} |awk '{print "<tr><td>"$1"</td><td>"$2"</td><td>"$3"</td><td>"$4"</td><td>"$5"</td><td>"$6"</td><td>"$7"</td><td>"$8"</td><td>"$9"</td><td>"$10"</td></tr>"}'`
echo "${content_str}" >> ${oneProject_mail_output_file}
fi
done
#当月总计
cat ${oneProject_total_output_file} |while read record
do
content_str=`echo ${record} |awk '{print "<tr><td>"$1"</td><td>"$2"</td><td>"$3"</td><td>"$4"</td><td>"$5"</td><td>"$6"</td><td>"$7"</td><td>"$8"</td><td>"$9"</td><td>"$10"</td></tr>"}'`
echo "${content_str}" >> ${oneProject_mail_output_file}
done
echo "</tbody></table><br>" >> ${oneProject_mail_output_file}
echo "</body><html>" >> ${oneProject_mail_output_file}
#发送邮件
mail_subject="电商oneProject${get_month_of_year}月1日-${get_month_of_year}月${get_day_of_month}日"
mail_from="qqqq@xxx.com"
mail_cc="bbbbb@cccc.com"
mail_to="bbbbb@cccc.com"
mail_bcc="bbbbb@cccc.com"
mail_content=`cat ${oneProject_mail_output_file}`
/..../sendEmail-v1.56/sendEmail -f "${mail_from}" -t "${mail_to}" -cc ${mail_cc} -bcc ${mail_bcc} -u "${mail_subject}" -m "${mail_content}" -o message-content-type=text/html -o message-charset=utf-8