任务：每日下午五点半统计数据，发送给领导。
人工流程：Windows10上通过ODBC连接远程生产Cache数据库，在sqlDbx执行sql语句，得到数据后填到模板，微信发给领导。
自动流程：
1.安装python3.7.8(注意坑，python3.7.4会setup报错)
2.按照Cache提供的python调用说明，操作一下步骤：
1) Install Cache'
2) Make sure CACHE_INSTALL_DIR/bin (or CACHE_INSTALL_DIR\bin) is on your path (Your CACHE_INSTALL_DIR might be /usr/cachesys/bin.)
3) Update LD_LIBRARY_PATH (LINUX only)
4) Update DYLD_LIBRARY_PATH instead of LD_LIBRARY_PATH (MAC only)
4) Setup your enviornment to use Microsoft or LINUX C/C++ compiler
5) Invoke python extension setup command (python setup.py install)
6) Test by invoking python test
3.环境搭建好后，编写调用数据库和发送邮件的py脚本。
import smtplib
from email.mime.text import MIMEText
from email.header import Header
import codecs, sys
import intersys.pythonbind3
import datetime
id = 1;
user = "_user";
password = "pwd";
host = "localhost";
port = "1972";
query = "A";
numList=[];
args = sys.argv[1:]
index = 0
while 1:
if index >= len(args): break
arg = args[index]
if arg == "-user":
index += 1
user = args[index]
elif arg == "-password":
index += 1
password = args[index]
elif arg == "-host":
index += 1
host = args[index]
elif arg == "-port":
index += 1
port = args[index]
elif arg == "-query":
index += 1
query = args[index]
else:
raise Exception("Unknown option: " + str(arg))
index += 1
# try:
#
# Connect to specified machine, in the SAMPLES namespace
url = host + "[" + port + "]:dhc-app"
conn = intersys.pythonbind3.connection()
conn.connect_now(url, user, password, None)
database = intersys.pythonbind3.database(conn)
# Create the SQL statement */
sql="SELECT count(*) FROM PA_Adm a WHERE a.PAADM_AdmDate =current_date AND a.PAADM_Type IN ('O') AND a.PAADM_VisitStatus<>'C' AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE '%测试%' \
UNION ALl \
SELECT count(*) FROM PA_Adm a WHERE a.PAADM_AdmDate =current_date AND a.PAADM_AdmTime<='17:30:00' AND a.PAADM_Type IN ('E') AND a.PAADM_VisitStatus<>'C' AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE '%测试%' \
UNION ALl \
SELECT count(*) FROM PA_Adm a WHERE a.PAADM_AdmDate=current_date AND a.PAADM_Type='I' AND a.PAADM_AdmTime<='17:30:00' AND a.PAADM_VisitStatus NOT IN ('C') AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE '%测试%' \
UNION ALl \
SELECT count(*) FROM PA_Adm a WHERE a.PAADM_AdmDate=current_date AND a.PAADM_Type='O' AND a.PAADM_VisitStatus<>'C' AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE '%测试%' AND a.PAADM_AdmReason_DR->REA_Desc NOT LIKE '%自费%' \
UNION ALl \
SELECT count(*) FROM PA_Adm a WHERE a.PAADM_AdmDate=current_date AND a.PAADM_AdmTime<='17:30:00' AND a.PAADM_Type NOT IN ('O','H') AND a.PAADM_VisitStatus<>'C' AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE '%测试%' AND a.PAADM_AdmReason_DR->REA_Desc NOT LIKE '%自费%' \
UNION ALl \
SELECT count(*) FROM PA_Adm a WHERE a.PAADM_AdmDate =current_date AND a.PAADM_Type IN ('H') AND a.PAADM_VisitStatus<>'C' AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE '%测试%'"
sql0 = "SELECT count(1) FROM SQLUser.PA_Adm a WHERE a.PAADM_AdmDate =current_date AND a.PAADM_Type IN ('O')AND a.PAADM_VisitStatus<>'C'AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE '%测试%'";
sql1 = "SELECT count(1) FROM SQLUser.PA_Adm a WHERE a.PAADM_AdmDate =current_date AND a.PAADM_Type IN ('E')AND a.PAADM_VisitStatus<>'C'AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE '%测试%' AND a.PAADM_AdmTime<='17:30:00'";
sql2 = "SELECT count(1) FROM SQLUser.PA_Adm a WHERE a.PAADM_AdmDate =current_date AND a.PAADM_Type IN (\"O\")AND a.PAADM_VisitStatus<>\"C\"AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE \"%测试%\"";
sql3 = "SELECT count(1) FROM SQLUser.PA_Adm a WHERE a.PAADM_AdmDate =current_date AND a.PAADM_Type IN (\"O\")AND a.PAADM_VisitStatus<>\"C\"AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE \"%测试%\"";
sql4 = "SELECT count(1) FROM SQLUser.PA_Adm a WHERE a.PAADM_AdmDate =current_date AND a.PAADM_Type IN (\"O\")AND a.PAADM_VisitStatus<>\"C\"AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE \"%测试%\"";
sql5 = "SELECT count(1) FROM SQLUser.PA_Adm a WHERE a.PAADM_AdmDate =current_date AND a.PAADM_Type IN (\"O\")AND a.PAADM_VisitStatus<>\"C\"AND a.PAADM_PAPMI_DR->PAPMI_Name NOT LIKE \"%测试%\"";
# create a query
print(" test in sqluser a query")
cq = intersys.pythonbind3.query(database)
sqlcode = 0;
cq.prepare(sql);
# cq.set_par(1, query);
cq.execute();
i = 0;
while 1:
cols = cq.fetch([None])
print("cols长度 ：",len(cols))
if len(cols) == 0:
break
# dump the columns in each row
# @line = join ":", @cols;
print(cols);
numList.append(cols[0]);
msg_from = '--------@163.com' # 发送方邮箱
passwd = '-------' # 填入发送方邮箱的授权码(填入自己的授权码，相当于邮箱密码)
msg_to = ['-------@163.com','@qq.com'] # 收件人邮箱
subject = "今日数据" # 主题
content = "截止{}月{}日下午17：30，挂号{}人，体检{}人，收住院{}人，医保就诊{}人".format(datetime.datetime.now().month,datetime.datetime.now().day,numList[0]+numList[1],numList[5],numList[2],numList[3]+numList[4])
# 生成一个MIMEText对象(还有一些其它参数)
msg = MIMEText(content)
# 放入邮件主题
msg['Subject'] = subject
# 也可以这样传参
# msg['Subject'] = Header(subject, 'utf-8')
# 放入发件人
msg['From'] = msg_from
# 放入收件人
# msg['To'] = '@qq.com'
# msg['To'] = '发给你的邮件啊'
try:
# 通过ssl方式发送，服务器地址，端口
s = smtplib.SMTP_SSL("smtp.163.com", 465)
# 登录到邮箱
s.login(msg_from, passwd)
# 发送邮件：发送方，收件方，要发送的消息
s.sendmail(msg_from, msg_to, msg.as_string())
print('成功')
except s.SMTPException as e:
print(e)
finally:
s.quit()
4.打开windows任务计划程序按步骤创建计划，定时启动程序
5.大功告成
哈哈哈哈哈哈哈哈哈a'ha'ha'ha'hah