# coding=utf-8 import csv import pandas as pd from bson import ObjectId from base import init_env init_env(True) from apps.web.user.models import RechargeRecord, ConsumeRecord from apps.web.user.transaction_deprecated import refund_cash from apilib.monetary import RMB, VirtualCoin from apps.web.device.models import Device from apps.web.dealer.proxy import DealerIncomeProxy from apps.web.agent.define import AGENT_INCOME_TYPE from apps.web.agent.models import Agent, AgentIncomeReport from apps.web.constant import PARTITION_ROLE from apps.web.dealer.define import DEALER_INCOME_TYPE from apps.web.dealer.models import Dealer from apps.web.report.utils import update_income_stats FILE_NAME = ["9月27日19点-24点.csv", "9月28日0点-12点.csv", "9月28日12点-18点.csv"] ALI_ALL_FILE = "liGeAll.xlsx" ALI_REFUND_FILE = "liGeHasRefunded.xlsx" WECHAT_NO = [ "4200000694202009275252929409", "4200000684202009275674877850", "4200000679202009278707739298", "4200000682202009273085271461", "4200000690202009270275987685", "4200000702202009279868079657" ] def parse_csv_file(fileName): """ 读取里格原始的csv文件 :param fileName: :return: """ columns = ["wxOrderNo", "orderNo", "status"] columnNums = [1, 2, 4] dataFrame = pd.read_csv(fileName, skiprows=5, encoding="GBK", usecols=columnNums, names=columns) return dataFrame.iterrows() def write_need_refund(): writeData = list() for fileName in FILE_NAME: # 读取csv的表格文件 for index, row in parse_csv_file(fileName): # unPay 状态的不处理 if row.get("status") == u"待买家支付": continue # 退款成功的这个地方不做处理 elif row.get("status") == u"全额退款完成": continue # 最后的状态应该是 买家已支付 else: orderNo = row.get("orderNo").replace("`", "") wxOrderNo = row.get("wxOrderNo").replace("`", "") rechargeRecord = RechargeRecord.objects.filter(orderNo=orderNo, wxOrderNo=wxOrderNo).first() if not rechargeRecord: print "not rechargeRecord!, wxOrderNo is <{}>".format(wxOrderNo) tempData = { "wxOrderNo": wxOrderNo + "\t", "orderNo": orderNo, "status": row.get("status"), "isNeedRefund": 0, "reason": u"未找到订单", "payTime": rechargeRecord.dateTimeAdded } writeData.append(tempData) continue if not rechargeRecord.isQuickPay: tempData = { "wxOrderNo": wxOrderNo + "\t", "orderNo": orderNo, "status": row.get("status"), "isNeedRefund": 0, "reason": u"非快捷支付（是账户充值等付款）", "payTime": rechargeRecord.dateTimeAdded } writeData.append(tempData) print "not quickPay, wxOrderNo is <{}>".format(wxOrderNo) continue startKey = rechargeRecord.attachParas.get("startKey") consumeRecord = ConsumeRecord.objects.filter(startKey=startKey).first() # 这个地方如果没有找到消费订单 说明回调都没有过来 说明需要执行退款 if not consumeRecord: tempData = { "wxOrderNo": wxOrderNo + "\t", "orderNo": orderNo, "status": row.get("status"), "isNeedRefund": 1, "reason": u"未找到消费订单 系统未执行启动设备", "money": rechargeRecord.money.mongo_amount, "payTime": rechargeRecord.dateTimeAdded } writeData.append(tempData) print "not quickPay, wxOrderNo is <{}>".format(wxOrderNo) continue time1 = consumeRecord.dateTimeAdded time2 = rechargeRecord.dateTimeAdded timeDiff = (time1 - time2).seconds # 启动时间和订单支付时间差小于10分钟， 视为正常启动 if timeDiff < 600: tempData = { "wxOrderNo": wxOrderNo + "\t", "orderNo": orderNo, "status": row.get("status"), "isNeedRefund": 0, "reason": u"正常启动设备", "payTime": rechargeRecord.dateTimeAdded, "devStartTime": consumeRecord.dateTimeAdded } else: tempData = { "wxOrderNo": wxOrderNo + "\t", "orderNo": orderNo, "status": row.get("status"), "isNeedRefund": 0, "reason": u"支付成功10分种后才启动设备", "money": rechargeRecord.money.mongo_amount, "payTime": rechargeRecord.dateTimeAdded, "devStartTime": consumeRecord.dateTimeAdded } writeData.append(tempData) with open("lige_wechat.csv", "w") as f: writer = csv.DictWriter(f, fieldnames=["wxOrderNo", "orderNo", "status", "isNeedRefund", "reason", "money", "payTime", "devStartTime"]) writer.writeheader() writer.writerows(writeData) def refund_wechat(): for wxOrderNo in WECHAT_NO: rechargeRecord = RechargeRecord.objects.filter(wxOrderNo=wxOrderNo).first() if not rechargeRecord: print "not recharge, <{}>".format(wxOrderNo) continue refundFee = RMB(rechargeRecord.money) try: result, desc = refund_cash(rechargeRecord, refundFee, VirtualCoin(0)) except Exception as e: continue if result: attachParas = rechargeRecord.attachParas attachParas["refundCash"] = refundFee.mongo_amount rechargeRecord.attachParas = attachParas rechargeRecord.save() def parse_alipay_csv_file(fileName): columns = ["orderNo", "wxOrderNo"] columnNums = [3, 4] dataFrame = pd.read_excel(fileName, skiprows=3, encoding="GBK", usecols=columnNums, names=columns) return dataFrame.iterrows() def parse_alipay_refund_csv_file(fileName): columns = ["orderNo", "wxOrderNo"] columnNums = [4, 11] dataFrame = pd.read_excel(fileName, skiprows=3, encoding="GBK", usecols=columnNums, names=columns) return dataFrame.iterrows() def write_ali_need_refund(): # 首先读取已经退款的订单的编号 这个退款的里面会有一笔订单退了两次 订单总额是2元 每次退了1元 最终还是退还了全额 refundOrders = set() for index, row in parse_alipay_refund_csv_file(ALI_REFUND_FILE): orderNo = row.get("orderNo") refundOrders.add(orderNo) for orderNo in refundOrders: refund_info = repaired_ledger(orderNo) def repaired_ledger(orderNo): """ 处理 分账记录的修复 :return: """ record = RechargeRecord.objects.filter(orderNo=orderNo).first() if not record: print "no rechargeRecord orderNo is <{}>".format(orderNo) return refundFee = record.money # 获取分账记录 proxy = DealerIncomeProxy.objects(ref_id=record.id).first() if not proxy: print "not proxy" return # 处理经销商以及参与设备分成的 记录先修改 refund_partition = proxy.update_for_refund(refund_fee=refundFee) # 更新经销商,组和设备汇总信息 if record.logicalCode: update_income_stats(proxy=proxy, refund_partion=refund_partition, refund_fee=refundFee, allowed={'dealer': True, 'group': True, 'device': False}) else: update_income_stats(proxy=proxy, refund_fee=refundFee, refund_partion=refund_partition) # 更新经销商收益和代理商收益以及汇总数据 refund_info = list() for item in refund_partition: if item['amount'] > RMB(0): if item['role'] == PARTITION_ROLE.PARTNER or item['role'] == PARTITION_ROLE.OWNER: try: dealer = Dealer.objects(id=item['id']).first() dealer.decr_fund(DEALER_INCOME_TYPE.DEVICE_INCOME, record.withdrawSourceKey, item['amount']) except Exception, e: print('refund money from dealer error={}'.format(item['id'], str(e))) return else: try: agent = Agent.objects(id=item['id']).first() # type: Agent agent.decr_fund(AGENT_INCOME_TYPE.DEALER_DEVICE_FEE, record.withdrawSourceKey, item['amount']) AgentIncomeReport.get_collection().update( {'detail.recharge_record_id': ObjectId(record.id), 'agentId': item['id']}, {'$inc': {'amount': (-item['amount']).mongo_amount}}) except Exception, e: print('refund money from agent error={}'.format(item['id'], str(e))) return refund_info.append({"role": item["role"], "id": item["id"], "money": str(item["amount"])}) record.attachParas.update({"lige": True, "refund_partition": refund_info}) record.save() return refund_info write_ali_need_refund()