aa

#!/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