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() # 最后关闭数据库