#!/bin/bash # 日常运营数据 # 发送邮件 ############# DB_USER=<username> DB_PASSWD=<password> DB_HOST='192.168.xx.xx' # DATE=$(date +"%Y%m%d_%H%M%S") # EXCEL_DIR="/root/oms/${DATE}" #日期目录 # [[ -d ${EXCEL_DIR} ]] || mkdir -p ${EXCEL_DIR} #不存在则创建 #sql查询语句写入文件 cat > /root/oms/test_example.sql <<EOF use student; SELECT student.sname, student.age, student.java, student.php FROM student WHERE sid IN ( SELECT student_id, FROM score WHERE course_id IN ( SELECT cid FROM course WHERE cname = '英语' OR cname = '生物' ) GROUP BY student_id HAVING COUNT(course_id) = 1 ); EOF # cd ${EXCEL_DIR} # 执行写入的sql文件 mysql -u${DB_USER} -p${DB_PASSWD} -h $DB_HOST < /root/oms/test_example.sql > /root/oms/test_example.txt #格式转换 python /root/oms/txt2excel.py /root/oms/test_example.txt /root/oms/test_example #cd .. #zip ${DATE}_all.zip *.xls #zip -r ${DATE}_data.zip ${DATE}/ EMAIL_FROM="admin.xxx@hhaa.com" EMAIL_PASSWD="xxxxxxx" EMAIL_HOST="smtp.exmail.qq.com:587" TO_ADDRESS="admin.local1@hhaa.com,admin.local2@hhaa.com" #收件人,多个逗号隔开 message_subject_utf8="XXX运营数据execl统计" #邮件标题 message_body_utf8="您好,此邮件为系统自动发送,详细见附件,请查收!若未收到请在垃圾邮件中查看" #邮件内容 #邮件标题转换编码 message_subject_gb2312=`iconv -t GB2312 -f UTF-8 << EOF $message_subject_utf8 EOF` [ $? -eq 0 ] && message_subject="$message_subject_gb2312" || message_subject="$message_subject_utf8" #换邮件内容为GB2312 message_body_gb2312=`iconv -t GB2312 -f UTF-8 << EOF $message_body_utf8 EOF` [ $? -eq 0 ] && message_body="$message_body_gb2312" || message_body="$message_body_utf8" #以下为腾讯企业邮箱的发送方式 -a表示以附件方式,如果多个文件可以zip打包发送附件 /usr/local/bin/sendEmail -o tls=yes -f "${EMAIL_FROM}" -s "${EMAIL_HOST}" -u "$message_subject" -o message-content-type=text -o message-charset=utf8 -t "${TO_ADDRESS}" -xu "${EMAIL_FROM}" -xp "${EMAIL_PASSWD}" -m "$message_body" -a /root/oms/test_example.xls #删除临时文件 rm -f /root/oms/test_example.txt rm -f /root/oms/test_example.sql rm -f /root/oms/test_example.xls