#加载模块
import time
import smtplib #发送邮件模块
from email.mime.text import MIMEText #定义邮件内容
from email.mime.multipart import MIMEMultipart #用于传送附件
import cx_Oracle #Oracle 数据库连接
#数据库函数
def mysql_execute(in_sql, leixing):
# Oracle 的特殊设定
dsn = “134.80.200.1/111”
conn = cx_Oracle.connect(user=“zbweb”, password=“xxx_xxx_03”, dsn=dsn, encoding=“UTF-8”)
# 得到一个可以执行SQL语句的光标对象
cursor = conn.cursor()
# 数据库执行导入的语句
if leixing == '数量':
# 反馈数量
count = cursor.execute(in_sql)
elif leixing == '单条':
# 反馈单条
cursor.execute(in_sql)
count = cursor.fetchone()[0]
elif leixing == '多条':
# 反馈多条
cursor.execute(in_sql)
count = cursor.fetchall()
elif leixing == '编辑':
count = cursor.execute(in_sql)
conn.commit()
# 关闭光标对象
cursor.close()
# 关闭数据库连接
conn.close()
# 反馈
return count
#昨天时间计算
def get_yesterday_time(input_date =‘0’):
#如果时间传入为空
if input_date ==‘0’:
ct = time.time()-246060
local_time = time.localtime(ct)
data_head = time.strftime("%Y%m%d%H%M%S", local_time)
data_secs = abs(ct - round(ct)) * 1000
time_stamp = “%s%03d” % (data_head, data_secs)
else:
time_stamp = input_date +‘120000001’
return time_stamp
#今天时间计算
def get_today_time(input_date =‘0’):
#如果时间传入为空
if input_date ==‘0’:
ct = time.time()
local_time = time.localtime(ct)
data_head = time.strftime("%Y%m%d%H%M%S", local_time)
data_secs = abs(ct - round(ct)) * 1000
time_stamp = “%s%03d” % (data_head, data_secs)
else:
time_stamp = input_date +‘120000001’
return time_stamp
#邮件推送
def email_out(user,password,smtpserver,sender,receives,title,content,send_file,input_date,id,num_lists):
‘’’
:param user: 发送邮箱用户名
:param password:发送邮箱密码
:param smtpserver:发送服务器
:param sender:发送方邮箱
:param receives:接收方邮箱
:param title:邮件标题
:param content:邮件正文
:param send_file:邮件附件
:param input_date:发送时间，8位长度的yyyymmdd格式
:param id:工单编码
:param num_lists:发送区县
:return:
‘’’
#提取昨天日期
in_time = get_yesterday_time(input_date)
#邮件附件最后带日期，如果附件没有带点，就原名发出
try:
ddwz = send_file.index(’.’)
send_file_new = send_file[0:ddwz] + num_lists + ‘_’ + in_time[0:8] + send_file[ddwz:]
except:
send_file_new=send_file
# 发送邮件主题和内容
subject = title + ‘[%s]’ % in_time[0:8]
#文字描述中 \n 替换成 <br/>，头尾
content_head = '<html><h3>各位领导，您好：</h3><br/> '
content_tail = '<br/><h3> 此致<br/>敬礼<br/>'
content_new = content_head + content.replace('\n','<br/>') + content_tail
# 构造附件内容：定义附件，构造附件内容 fujian\20220302\5330001
send_file_path = r".\fujian\%s\%s\%s" % (in_time[0:8],id,send_file_new)
send_file_path = open(send_file_path, 'rb').read() # 'rb'表示r读取，b表示二进制方式读取
att = MIMEText(send_file_path, 'base64', 'utf-8') # 调用传送附件模块，传送附件
att["Content-Type"] = 'application/octet-stream'
att["Content-Disposition"] = 'attachment;filename="%s%s"' % (id,send_file[ddwz:]) # 附件描述外层要用单引号
print('email_out4')
# 构建发送与接收信息
msgRoot = MIMEMultipart() # 发送附件的方法定义为一个变量
msgRoot.attach(MIMEText(content_new, 'html', 'utf-8')) # 发送附件的方法中嵌套发送正文的方法
msgRoot['subject'] = subject
msgRoot['From'] = sender
#msgRoot['From'] ='xxx@sd.com' #可以设置接收者看到的发送人邮箱
msgRoot['Bcc'] = ','.join(receives)
msgRoot.attach(att) # 添加附件到正文中
# SSL协议端口号要使用465
smtp = smtplib.SMTP_SSL(smtpserver, 465)
# HELO 向服务器标识用户身份
smtp.helo(smtpserver)
# 服务器返回结果确认
smtp.ehlo(smtpserver)
# 登录邮箱服务器用户名和密码
smtp.login(user, password)
smtp.sendmail(sender, receives, msgRoot.as_string())
smtp.quit()
return "发送成功"
#获取数据库内邮箱信息
def email_user():
sql = “SELECT userid,password,email_server,email_post,smtpserver FROM zbweb.email_user_type where status=‘1’”
num = mysql_execute(sql, ‘多条’)
num_cf = str(num).replace(’(’, ‘’).replace(’)’, ‘’).replace(’[’, ‘’).replace(’]’, ‘’).replace("’", “”).replace(" “, “”).split(”,")
return {‘user’:num_cf[0],‘password’:num_cf[1],‘email_server’:num_cf[2],‘email_post’:num_cf[3],‘smtpserver’:num_cf[4]}
#获取邮件任务数据
def email_order():
num_zidian=[]
sql = “SELECT id,email_name,email_note,order_time,fujian_name FROM zbweb.email_order_biaoti where status=‘1’”
num_list = mysql_execute(sql, ‘多条’)
for num in num_list:
num_cf = str(num).replace(’(’, ‘’).replace(’)’, ‘’).replace("’", “”).replace(" “, “”).split(”,")
num_zidian.append({‘id’:num_cf[0],‘email_name’:num_cf[1],‘email_note’:num_cf[2],‘order_time’:num_cf[3],‘fujian_name’:num_cf[4]})
return num_zidian
#获取邮件当天是否已发送完成
def email_confirm(id,in_time):
sql = “SELECT count(id) FROM zbweb.email_order_confirm where status=‘1’ and id=’%s’ and in_time=’%s’” % (id,in_time)
num = mysql_execute(sql, ‘单条’)
return num
#y邮件发送失败记录
def email_faillog(id,in_time,op_time,status,notes):
sql = “insert into zbweb.email_order_faillog (id,in_time,createdate,status,notes) values(%d,’%s’,’%s’,’%s’,’%s’)” % (
int(id), in_time, op_time, status,notes)
mysql_execute(sql, ‘编辑’)
return ‘记录完成’
#邮件发送增加收件人
#user,password,smtpserver,sender,receives,title,content,send_file,input_date
def email_sending(id,user,password,smtpserver,sender,title,content,send_file,input_date):
# 当前系统时间
in_time = get_today_time()
# 语音提示时间
yy_time = in_time[0:4] + ‘年’ + in_time[4:6] + ‘月’ + in_time[6:8] + ‘日’ + in_time[8:10] + ‘点’ + in_time[10:12] + ‘分’
num_lists = []
sql = “SELECT a.email_id,b.qx FROM zbweb.email_order_chengyuan a,zhyw.zibo_jyfx_staff b where a.status=‘1’ and a.id=%s and a.servnumber=b.mobilephone” % id
num_list = mysql_execute(sql, ‘多条’)
for num in num_list:
num_cf = str(num).replace(’(’, ‘’).replace(’)’, ‘’).replace("’", “”).replace(" “, “”).split(”,")
num_cf_zidian = {‘qx’: num_cf[1], ‘num_yx’: num_cf[0]}
num_lists.append(num_cf_zidian)
# 地市与区县定死
num_dg_0 = []
num_dg_3 = []
num_dg_4 = []
num_dg_5 = []
num_dg_6 = []
num_dg_7 = []
num_dg_8 = []
num_dg_9 = []
num_dg_a = []
num_dg_b = []
num_dg_d = []
for num_dg in num_lists:
print('num_dg', num_dg)
if num_dg['qx'] == '0':
num_dg_0.append(num_dg['num_yx'])
elif num_dg['qx'] == '3':
num_dg_3.append(num_dg['num_yx'])
elif num_dg['qx'] == '4':
num_dg_4.append(num_dg['num_yx'])
elif num_dg['qx'] == '5':
num_dg_5.append(num_dg['num_yx'])
elif num_dg['qx'] == '6':
num_dg_6.append(num_dg['num_yx'])
elif num_dg['qx'] == '7':
num_dg_7.append(num_dg['num_yx'])
elif num_dg['qx'] == '8':
num_dg_8.append(num_dg['num_yx'])
elif num_dg['qx'] == '9':
num_dg_9.append(num_dg['num_yx'])
elif num_dg['qx'] == 'a':
num_dg_a.append(num_dg['num_yx'])
elif num_dg['qx'] == 'b':
num_dg_b.append(num_dg['num_yx'])
elif num_dg['qx'] == 'd':
num_dg_d.append(num_dg['num_yx'])
# 所有区县的数据都进入
num_lists_sy = {'0': num_dg_0, '3': num_dg_3, '4': num_dg_4,
'5': num_dg_5, '6': num_dg_6, '7': num_dg_7,
'8': num_dg_8, '9': num_dg_9, 'a': num_dg_a,
'b': num_dg_b, 'd': num_dg_d}
print('num_lists_sy', num_lists_sy)
# 如果收件人邮箱大于1才会触发下面数据
for num_lists in num_lists_sy:
print(num_lists, num_lists_sy[num_lists])
#如果收件人邮箱大于1才会触发下面数据
if len(num_lists_sy[num_lists]) >=1 and num_lists_sy[num_lists] !=[] :
try:
email_out_status = email_out(user,password,smtpserver,sender,num_lists_sy[num_lists],title,content,send_file,input_date,id,num_lists)
if email_out_status == '发送成功':
print(id,input_date,in_time[0:12],'1')
sql = "insert into zbweb.email_order_confirm (id,in_time,createdate,status) values(%d,'%s','%s','%s')" % (
int(id),input_date,in_time[0:12] + num_lists,'1')
mysql_execute(sql, '编辑')
print('id:',id,'发送完成')
else:
faillog = email_faillog(id,input_date,in_time[0:12] ,'1','发送失败请核实'+ num_lists)
print('id:', id, '发送失败请核实',faillog)
except:
faillog = email_faillog(id, input_date, in_time[0:12], '1', '发送失败请核实附件是否正常'+ num_lists)
print('id:', id, '发送失败请核实附件是否正常',faillog)
else:
#faillog = email_faillog(id, input_date, in_time[0:12], '1', '无接收人请核实'+ num_lists)
pass
#print('id:', id, '无接收人请核实',faillog)
if name == ‘main’:
# 循环执行
while True:
#第一步获取正常登陆的邮箱名称等信息
email_user_zd = email_user()
print(email_user_zd)
user = email_user_zd['user']
password = email_user_zd['password']
email_server = email_user_zd['email_server']
email_post = email_user_zd['email_post']
smtpserver = email_user_zd['smtpserver']
#当前系统时间
in_time = get_today_time()
yesterday_time = get_yesterday_time()
#邮件工单触发列表
email_trigger=[]
#邮件工单任务遍历
email_order_list = email_order()
#看看获取任务
print('获取任务',len(email_order_list),email_order_list)
#临时等待
time.sleep(2)
for email_order_id in email_order_list:
#print('工单id:',email_order_id['id'],'email_name:',email_order_id['email_name'],'email_note:',email_order_id['email_note'],'order_time:',email_order_id['order_time'],'fujian_name:',email_order_id['fujian_name'])
#time.sleep(2)
#判断邮件ID当天是否发送过，没有发送过的进入 邮件工单触发列表
email_ok = email_confirm(email_order_id['id'],yesterday_time[0:8])
#如果当天没有发送记录，并且发送时间在定制时间就发送
print('order_time',email_order_id['order_time'],in_time[8:12])
if email_ok==0 and email_order_id['order_time']<=in_time[8:12]:
email_trigger.append(email_order_id)
email_sending(email_order_id['id'],user,password,smtpserver,user,email_order_id['email_name'],email_order_id['email_note'],email_order_id['fujian_name'],yesterday_time[0:8])
else:
break
#邮件等待发送记录
if len(email_trigger)==0:
print('当前时间 %s ,无等待发送邮件任务' % in_time[0:12])
time.sleep(60)
6074
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
