Shell查询数据库，和发送邮件
原创
sendmail.sh
!/bin/bash
#
function getDateStr()
{
echo date -d "-$1 day" +%Y-%m-%d
}
定义配置文件的地址
PROPERTY_FILE="/home/xueping.you/conf/conf.properties"
数据库
db_name=sudo cat $PROPERTY_FILE | grep "mysql.host" | awk -F '=' '{print $2}'
用户名
db_username=sudo cat $PROPERTY_FILE | grep "mysql.username" | awk -F '=' '{print $2}'
密码
db_password=sudo cat $PROPERTY_FILE | grep "mysql.password" | awk -F '=' '{print $2}'
数据库
db=sudo cat $PROPERTY_FILE | grep "mysql.database" | awk -F '=' '{print $2}'
对账天数
max_day=sudo cat $PROPERTY_FILE | grep "size_day" | awk -F '=' '{print $2}'
存放对账总结果存放文件
reconcile_result=sudo cat $PROPERTY_FILE | grep "reconcile_result" | awk -F '=' '{print $2}'
存放对账详情存放文件 biz_typediff + 1 “酒店直销TTS” 2 “酒店OTATTS” 3 “酒店国际业务” 4 “酒店一口价分销”
reconcile_diffresult='/home/xueping.you/out/biztypediff'
查询出来的邮件发送人
sendEmail=sudo cat $PROPERTY_FILE | grep "sendEmail" | awk -F '=' '{print $2}'
定义查询一句
date_str=getDateStr "$max_day"
构建查询语句
select_reconcileResult="select * from reconcile_result where reconcile_ts < '${date_str} 00:00:00';"
执行查询语句
mysql -s -h $db_name -u$db_username -p$db_password $db -e "$select_reconcileResult" > $reconcile_result
$1为邮件发送人列表,$2为biztype
function sendmail()
{
if [ -s $reconcile_diff_result$2 ]
then
from=alert@qunar.com
#to=`cat $1`
to="xueping.you@qunar.com"
echo "<html><body><table border=1>">>mailcontent
echo "<tr>">>mailcontent
title=`cat /home/xueping.you/conf/emailformat|grep 'email_title' | awk -F '=' '{print $2}' `
#分割字符串，变量OLD_IFS存着默认分隔符，IFS存着分隔符，用完之后还原分隔符
OLD_IFS="$IFS"
IFS=" "
arr=($title)
IFS="$OLD_IFS"
for titles in ${arr[@]}
do
echo "<td>"$titles"</td>">>mailcontent
done
echo "</tr>">>mailcontent
cat $reconcile_diff_result$2 | while read diff_line
do
echo "<tr>">>mailcontent
OLD_IFS="$IFS"
IFS=" "
diff_line_arr=($diff_line)
IFS="$OLD_IFS"
for diff_item in ${diff_line_arr[@]}
do
echo "<td>"$diff_item"</td>">>mailcontent
done
echo "</tr>">>mailcontent
done
echo "</table></body></html>">>mailcontent
#具体邮件发送代码
#设置邮件发送日期
email_date=$(date "+%Y-%m-%d_%H:%M:%S")
#设置邮件标题
email_subject="对账差异未处理提醒"$email_date
#发送邮件
(echo "Subject: $email_subject";echo "From: $from";echo "To: $to";echo "Content-Type: text/html" ;echo `cat mailcontent`;)|/usr/lib/sendmail -t
fi
sudo rm mailcontent
}
查询出对账总结过对应的差异
while read line
do
reconcile_id=echo $line | awk '{print $1}'
biztype=echo $line | awk '{print $2}'
mysql -s -h $db_name -u$db_username -p$db_password $db -e "select id , trade_no , order_no,reconcile_ts ,reconcile_op_type , reconcile_direct , trade_amount , biz_type , status from reconcile_diff_detail where reconcile_result_id = '$reconcile_id' and invalid=0;">>$reconcile_diff_result$biztype
#查询邮件
mysql -s -h $db_name -u$db_username -p$db_password $db -e "select biz_type,email_address from reconcile_notify_email;">$sendEmail
done<$reconcile_result
发送邮件的代码
ls /home/xueping.you/out/biztypediff*>outfile
while read line
do
biztype=`echo $line | grep 'biztypediff' | awk -F '' '{print $2}'`
#生成邮件接收人地址的字符串
emailTo=''
cat $sendEmail|grep $biztype | awk '{print $2}' | while read line;do echo $line";";done > readysend
sendmail "readysend" "$biztype"
done<outfile
sudo rm outfile
sudo rm /home/xueping.you/out/*
sudo rm readysend</pre> 原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读