def check_refund_order():
connection = pymysql.connect(
host='localhost',
port=3306,
user='******',
password='******',
db='native',
charset='utf8',
)
cursor = connection.cursor() # 定义一个数据库游标
# 查找订单状态为3 ，且是15天前的发货订单，判断是否评价
cursor.execute(
'select id, user_id,orderType,out_trade_no,realTotal from main_order WHERE status=6 FOR UPDATE '
)
orders = cursor.fetchall() # 得到元组数据, 选择订单id 和 订单的交易号
if orders:
for o in orders:
try:
# 退款
# data = {
# 'out_trade_no': o[3]}
raw = WxPay().refund_query(out_trade_no=o[3])
if raw.get("return_code") == 'SUCCESS' and raw.get(
'result_code') == 'SUCCESS':
cursor.execute(
'update main_order set status=7 WHERE status=1 and id=%s',
o[0])
connection.commit()
except Exception as e:
connection.rollback()
print('8return_msg:' + str(e))
def check_no_send_collage_order():
connection = pymysql.connect(
host='localhost',
port=3306,
user='******',
password='******',
db='native',
charset='utf8',
)
cursor = connection.cursor() # 定义一个数据库游标
t = datetime.datetime.fromtimestamp(time.time() - 259200) # 查找3天后仍然未拼成团的订单
# 查找3天后仍然未拼成团的订单，
cursor.execute(
'select id, user_id,orderType,out_trade_no,realTotal,collagerId from main_order WHERE status=8 and orderType=1 and collageTime < %s FOR UPDATE ',
t)
orders = cursor.fetchall() # 得到元组数据, 选择订单id 和 订单的交易号
if orders:
for o in orders:
try:
# 退款
data = {
'out_trade_no': o[3],
'total_fee': int(float(o[4]) * 100),
'refund_fee': int(float(o[4]) * 100),
}
raw = WxPay().refund(c_api_cert_path, c_api_key_path, data)
if raw.get("return_code") == 'SUCCESS' and raw.get(
'result_code') == 'SUCCESS':
cursor.execute(
'update main_order set status=6 WHERE status=1 and id=%s',
o[0])
# 回源
cursor.execute(
'select id,user_id,collageSku_id from main_collageuser WHERE id=%s',
o[5])
collager = cursor.fetchone() # 团长
if collager[1] == o[1]: # # 是团长回源
cursor.execute(
'select id,residualNum,sku_id from main_collagesku WHERE id=%s FOR UPDATE ',
collager[2])
collageSku = cursor.fetchone()
cursor.execute(
'update main_collagesku set residualNum=%s WHERE id=%s',
[collageSku[1] + 1, collageSku[0]])
connection.commit()
except Exception as e:
connection.rollback()
print('10return_msg:' + str(e))
def check_no_send_order():
connection = pymysql.connect(
host='localhost',
port=3306,
user='******',
password='******',
db='native',
charset='utf8',
)
cursor = connection.cursor() # 定义一个数据库游标
t = datetime.datetime.fromtimestamp(time.time() - 259200) # 查找收货超过3天未发货的订单
# 查找订单状态为3 ，且是15天前的发货订单，判断是否评价
cursor.execute(
'select id, user_id,orderType,out_trade_no,realTotal from main_order WHERE status=1 and orderType=0 and payTime < %s FOR UPDATE ',
t)
orders = cursor.fetchall() # 得到元组数据, 选择订单id 和 订单的交易号
if orders:
for o in orders:
try:
# 退款
data = {
'out_trade_no': o[3],
'total_fee': int(float(o[4]) * 100),
'refund_fee': int(float(o[4]) * 100),
}
raw = WxPay().refund(c_api_cert_path, c_api_key_path, data)
if raw.get("return_code") == 'SUCCESS' and raw.get(
'result_code') == 'SUCCESS':
cursor.execute(
'update main_order set status=6 WHERE status=1 and id=%s',
o[0])
# 回源
cursor.execute(
'select id,skuNum,sku_id from main_ordersku WHERE order_id=%s',
o[0])
orderSkus = cursor.fetchall()
for osk in orderSkus:
cursor.execute(
'select id,residualNum from main_sku WHERE id=%s FOR UPDATE ',
osk[2])
sku = cursor.fetchone()
cursor.execute(
'update main_sku set residualNum=%s WHERE id=%s',
[sku[1] + osk[1], sku[0]])
connection.commit()
except Exception as e:
connection.rollback()
print('7return_msg:' + str(e))
def check_no_pay_collage_order():
connection = pymysql.connect(
host='localhost',
port=3306,
user='******',
password='******',
db='native',
charset='utf8',
)
cursor = connection.cursor() # 定义一个数据库游标
t = datetime.datetime.fromtimestamp(time.time() - 1800)
# 查找30分钟还没有支付的拼团订单
cursor.execute(
'select id,out_trade_no,collagerId,user_id '
'from main_order WHERE '
'status=0 and createTime < %s and orderType=1 FOR UPDATE ', t)
orders = cursor.fetchall() # 得到元组数据, 选择订单id 和 订单的交易号
wx = WxPay() # 生成工具类实例
if orders: # 如果存在未处理订单
for o in orders:
try:
cursor.execute(
'update main_order set status=5 WHERE id=%s and status=0',
o[0])
cursor.execute(
'delete from main_collageuser WHERE user_id=%s and collagerId=%s',
[o[3], o[2]])
connection.commit()
except Exception as e:
connection.rollback()
print('12return_msg:' + str(e))
# print(e)
cursor.close()
connection.close() # 最后关闭数据库
def check_no_pay_notify():
connection = pymysql.connect(
host='localhost',
port=3306,
user='******',
password='******',
db='native',
charset='utf8',
)
cursor = connection.cursor() # 定义一个数据库游标
# t = datetime.datetime.fromtimestamp(time.time() - 300)
# 查找通知过了但是支付失败的订单
cursor.execute(
'select id,out_trade_no from main_order WHERE status=0 and notifyStatus = 1'
)
orders = cursor.fetchall() # 得到元组数据, 选择订单id 和 订单的交易号
wx = WxPay() # 生成工具类实例ret
if orders: # 如果存在未处理订单
for o in orders:
data = {
'out_trade_no': o[1],
}
res = wx.order_query(data)
try:
if res.get("trade_state") == "SUCCESS": # 如果交易成功 # 更新订单信息
now = datetime.datetime.now()
cursor.execute(
'update main_order set status=1 WHERE id=%s and notifyStatus=1',
o[0])
else: # 交易已经过期(更改订单为未付款)
cursor.execute(
'update main_order set notifyStatus=0 WHERE id=%s and status=0',
o[0])
connection.commit()
except Exception as e:
connection.rollback()
print('11return_msg:' + str(e))
# print(e)
cursor.close()
connection.close() # 最后关闭数据库
def check_no_pay_order():
connection = pymysql.connect(
host='localhost',
port=3306,
user='******',
password='******',
db='native',
charset='utf8',
)
cursor = connection.cursor() # 定义一个数据库游标
t = datetime.datetime.fromtimestamp(time.time() - 86400) # 查找24小时之前的未付款订单
# 1查找订单状态为10 ，且是5分钟前俞支付订单，判断是否支付成功，进行回源（不使用悲观锁，使用乐观锁）
cursor.execute(
'select id, out_trade_no from main_order WHERE status=0 and prePayTime < %s FOR UPDATE',
t)
orders = cursor.fetchall() # 得到元组数据, 选择订单id 和 订单的交易号
wx = WxPay() # 生成工具类实例ret
if orders: # 如果存在未处理订单
for o in orders:
try:
cursor.execute(
'update main_order set status=5 WHERE id=%s and status=0',
o[0])
# 同时回源 查找订单sku
cursor.execute(
'select id, skuNum,sku_id from main_ordersku WHERE order_id=%s',
o[0])
orderSkus = cursor.fetchall() # 得到元组数据
for oks in orderSkus:
cursor.execute(
'select id, residualNum from main_sku WHERE id=%s FOR UPDATE ',
oks[0])
sku = cursor.fetchone() # 得到元组数据
tempresidualNum = sku[1] + oks[1]
cursor.execute(
'update main_sku set residualNum=%s WHERE id=%s',
[tempresidualNum, sku[0]])
connection.commit()
except Exception as e:
print('4return_msg:' + str(e))
# print(e)
cursor.close()
connection.close() # 最后关闭数据库
def check_no_notify_express_order():
connection = pymysql.connect(
host='localhost',
port=3306,
user='******',
password='******',
db='native',
charset='utf8',
)
cursor = connection.cursor() # 定义一个数据库游标
# t = datetime.datetime.fromtimestamp(time.time() - 300)
# 1查找订单状态为1 ，且通知状态为0的订单
cursor.execute(
'select id, phoneNum,order_code,user_id,realTotal,orderType,province,city,area,address,prepay_id,express,expressNo '
'from main_order WHERE status=2 and notifyStatus!=2')
orders = cursor.fetchall() # 得到元组数据, 选择订单id 和 订单的交易号
wx = WxPay() # 生成工具类实例ret
if orders: # 如果存在未处理订单
for o in orders:
try:
# 发送模板消息？
cursor.execute(
'select id, skuName,skuNum,productName from main_ordersku WHERE order_id=%s',
o[0])
orderSkus = cursor.fetchall()
cursor.execute(
'select openId,score,id from main_user WHERE id=%s FOR UPDATE ',
o[3])
user = cursor.fetchone()
orderContent = ''
for oks in orderSkus:
orderContent += oks[3] + ' ' + str(oks[1]) + '×' + str(
oks[2]) + ' '
# 发送发货通知
data = { # "first": {"value": "同渡旅行告诉你有新的通知", "color": "#173177"},
"keyword1": {"value": o[2], "color": "#173177"},
"keyword2": {"value": o[4], "color": "#173177"},
"keyword3": {"value": orderContent, "color": "#173177"},
"keyword4": {"value": o[11], "color": "#173177"},
"keyword5": {"value": o[12], "color": "#173177"},
"keyword6": {"value": o[6] + o[7] + o[8] + o[9],
"color": "#173177"},
"keyword7": {"value": o[1], "color": "#173177"},
}
wechat_push = WxPay()
res = wechat_push.do_push(
user[0],
c_templateID_order_send,
c_page,
data,
o[10],
)
if res.get('errcode') == 0 and res.get('errmsg') == 'ok': # 成功
# 把通知状态改为2，表示订单已经发送了发货通知
cursor.execute(
'update main_order set notifyStatus=2 WHERE id=%s',
o[0])
pass
else:
print('orderId' + str(o[0]))
print(res)
connection.commit()
except Exception as e:
print('3return_msg:' + str(e))
# print(e)
cursor.close()
connection.close() # 最后关闭数据库
def check_prepay_order():
connection = pymysql.connect(
host='localhost',
port=3306,
user='******',
password='******',
db='native',
charset='utf8',
)
cursor = connection.cursor() # 定义一个数据库游标
t = datetime.datetime.fromtimestamp(time.time() - 300)
# 1查找订单状态为10 ，且是5分钟前的支付订单，判断是否支付成功，进行回源（不使用悲观锁，使用乐观锁）
cursor.execute(
'select id, out_trade_no,order_code,user_id,realTotal,orderType,province,city,area,address,prepay_id,prePayTime,collagerId from main_order WHERE status=10 and prePayTime < %s',
t)
orders = cursor.fetchall() # 得到元组数据, 选择订单id 和 订单的交易号
wx = WxPay() # 生成工具类实例ret
if orders: # 如果存在未处理订单
for o in orders:
data = {
'out_trade_no': o[1],
}
res = wx.order_query(data)
try:
if res.get("trade_state") == "SUCCESS": # 如果交易成功 # 更新订单信息
now = datetime.datetime.now()
cursor.execute(
'update main_order set status=1,payTime=%s WHERE id=%s and status=10',
[now, o[0]])
# 发送模板消息？
cursor.execute(
'select id, skuName,skuNum,productName,sku_id from main_ordersku WHERE order_id=%s',
o[0])
orderSkus = cursor.fetchall()
cursor.execute(
'select openId,score,id from main_user WHERE id=%s FOR UPDATE ',
o[3])
user = cursor.fetchone()
orderType = ''
if o[5] == 0: # 是单买
orderType = '单买'
temscore = user[1] + 20
# 下单获得20积分
cursor.execute(
'update main_user set score=%s WHERE id=%s',
[temscore, o[3]])
elif o[5] == 1: # 是团购
orderType = '团购'
# print('ok1')
cursor.execute(
'select id, status,collageSku_id,user_id from main_collageuser WHERE id=%s FOR UPDATE ',
o[12])
# print(o[12])4
collager = cursor.fetchone()
if collager[4] == o[3]: # 是团长通知
cursor.execute(
'update main_collageuser set status=1 WHERE id=%s',
collager[0])
cursor.execute(
'update main_order set status=1 WHERE id=%s',
o[0])
else: # 是团员的通知
cursor.execute(
'select id, collage_id,collagePrice from main_collagesku WHERE id=%s FOR UPDATE ',
collager[2])
collageSku = cursor.fetchone()
cursor.execute(
'select id, collagePeople from main_collage WHERE id=%s FOR UPDATE ',
collageSku[1])
collage = cursor.fetchone()
cursor.execute(
'select id from main_collageuser WHERE collagerId=%s and status=1 FOR UPDATE ',
o[12])
collageUsers = cursor.fetchall() # 查找支付成功的订单
cursor.execute(
'select id from main_collageuser WHERE collagerId=%s and user_id=%s FOR UPDATE ',
[o[12], o[3]])
collageUser = cursor.fetchone() # 查找本人团员
if collage[1] == len(
collageUsers
) + 1 + 1: # 够数开团（本人+团长+支付成功的团员）
profit = collage[1] * collageSku[2]
cursor.execute(
'update main_collage set collageTotal=collageTotal+%s',
profit) # 活动营收
cursor.execute(
'update main_collageuser set status=2 WHERE id=%s',
collager[0])
cursor.execute(
'update main_collageuser set status=2 WHERE id=%s',
collageUser[0])
for cu in collageUsers:
cursor.execute(
'update main_collageuser set status=2 WHERE id=%s',
cu[0])
cursor.execute(
'select id from main_order WHERE collagerId=%s FOR UPDATE ',
o[12])
collageOrders = cursor.fetchall()
now = datetime.datetime.now()
for co in collageOrders:
cursor.execute(
'update main_order set status=8,collageTime=%s WHERE id=%s',
[now, co[0]])
cursor.execute(
'update main_order set status=8,collageTime=%s WHERE id=%s',
[now, o[0]])
else:
# print('ok5')
cursor.execute(
'update main_collageuser set status=1 WHERE id=%s',
collageUser[0])
orderContent = ''
for oks in orderSkus:
orderContent += oks[3] + ' ' + str(oks[1]) + '×' + str(
oks[2]) + ' '
# 3.商品的销量对应相加
cursor.execute(
'select id,saleNum from main_sku WHERE id=%s FOR UPDATE ',
oks[4])
sku = cursor.fetchone()
saleNum = sku[1] + oks[2]
cursor.execute(
'update main_sku set saleNum=%s WHERE id=%s',
[saleNum, o[3]])
# 商家总销售额和订单数量+1
cursor.execute('select id from main_count')
count = cursor.fetchall()
if count:
count = count[0]
cursor.execute(
'update main_count set allSale=allSale+%s,allOrderNum=allOrderNum+%s WHERE id=%s',
[o[4], 1, count[0]])
else:
cursor.execute(
'INSERT INTO main_coun (allSale,allOrderNum) VALUES (%s,%s)',
[o[4], 1])
cursor.execute('select id from main_countday WHERE day=%s',
datetime.datetime.now().date())
# 商家日销售额和订单数量+1
countDay = cursor.fetchall()
if countDay:
countDay = countDay[0]
cursor.execute(
'update main_countday set orderNum=orderNum+%s,sale=sale+%s WHERE id=%',
[1, o[4], countDay[0]])
else:
cursor.execute(
'INSERT INTO main_countday (orderNum,sale)VALUES (%s,%s)',
[1, o[4]])
# 开始写消息模板
data = { # "first": {"value": "同渡旅行告诉你有新的通知", "color": "#173177"},
"keyword1": {"value": o[2], "color": "#173177"},
"keyword2": {"value": o[4], "color": "#173177"},
"keyword3": {"value": orderType, "color": "#173177"},
"keyword4": {"value": orderContent, "color": "#173177"},
"keyword5": {"value": o[6] + o[7] + o[8] + o[9],
"color": "#173177"},
"keyword6": {"value": o[11], "color": "#173177"},
}
wechat_push = WxPay()
res = wechat_push.do_push(
user[0],
c_templateID_order_success,
c_page,
data,
o[10],
)
if res.get('errcode') == 0 and res.get(
'errmsg') == 'ok': # 成功
# 把通知状态改为1，表示订单已经发送了通知支付成功消息
cursor.execute(
'update main_order set notifyStatus=1 WHERE id=%s and notifyStatus=0',
o[0])
pass
else:
print('orderId' + str(o[0]) + 'prepayId' + str(o[10]))
print(res)
else: # #交易已经过期(更改订单为未付款)
cursor.execute(
'update main_order set status=0 WHERE id=%s and status=10',
o[0])
connection.commit()
except Exception as e:
connection.rollback()
print('1return_msg:' + str(e))
# print(e)
cursor.close()
connection.close() # 最后关闭数据库