Shell查詢數據庫，和發送郵件

d66g 9年前發布 | 3K 次閱讀 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>
 本文由用戶 d66g 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！