# -*- coding: utf-8 -*- #!/usr/bin/env python import datetime import os from collections import OrderedDict import pandas import xlrd from celery.utils.log import get_task_logger from django.conf import settings from xlrd import xldate_as_tuple from apilib.monetary import sum_rmb from apilib.utils_datetime import to_datetime from apps.thirdparties.dingding import DingDingRobot from apps.web.agent.define import AgentConst from apps.web.agent.models import Agent from apps.web.constant import Const, ComplaintResponseTemplate from apps.web.core.models import WechatServiceProvider from apps.web.core.payment import WithdrawGateway from apps.web.core.utils import generate_excel_report from apps.web.dealer.define import DealerConst from apps.web.dealer.models import Dealer, DealerRechargeRecord from apps.web.device.models import SIMCard, Device from apps.web.core.bridge.wechat.v3api import WechatComplaint from apps.web.user.models import RechargeRecord, ConsumeRecord logger = get_task_logger(__name__) def import_simcard_excel_to_db(excelFile, source): book = xlrd.open_workbook(excelFile) try: sheet = book.sheet_by_name("Sheet") except Exception, e: logger.info('open excel file error=%s' % e) return None nrows = sheet.nrows allCount = 0 needNotUpdated, devUpdated = 0, 0 for i in range(1, nrows): row = sheet.row_values(i) try: if source == 'qiben': expireTime = to_datetime(row[2], time_format = "%Y-%m-%d") elif source == 'hezhou': expireTime = datetime.datetime(*xldate_as_tuple(row[2], 0)) elif source == 'jieyang': expireTime = to_datetime(row[2], time_format = "%Y-%m-%d") else: logger.info('source type is error') return None iccid = row[0] SIMCard.get_collection().update_one({'iccid': iccid}, { '$set': {'iccid': iccid, 'imsi': row[1], 'supplier': source, 'expireTime': expireTime}}, upsert = True) allCount += 1 if allCount % 300 == 0: logger.info('import simcard into simcard table finishe num is %s' % allCount) except Exception, e: logger.error('import simcard some exception = %s' % e) continue try: # 直接查询device的数据，检查是否需要更新 devObj = Device.objects(iccid = iccid).only('simExpireDate', 'simStatus').get() except Exception, e: continue # 如果过期日期比数据库中小20天（最差也会充值一个月，所以20天够用了），认为卡商并没有更新此卡的数据，就不需要更新，直接pass掉。 if (devObj.simExpireDate is not None) and (expireTime - devObj.simExpireDate).days < 20: needNotUpdated += 1 logger.info('this dev iccid =%s need not be updated,sim \ card of device expireTime=%s,sim card expireTime=%s' % (iccid, expireTime.strftime(Const.DATETIME_FMT), devObj.simExpireDate.strftime(Const.DATETIME_FMT))) continue try: devObj.simExpireDate = expireTime devObj.simStatus = 'updated' devObj.save() devUpdated += 1 except Exception, e: logger.info('save dev obj iccid =%s error=%s' % (iccid, e)) def export_simcard_excel_from_db(filepath, queryDict): logger.info('start export_simcard_excel_from_db,filepath=%s,query=%s' % (filepath, queryDict)) startTimeStr = queryDict.get('startTimeStr', None) endTimeStr = queryDict.get('endTimeStr', None) searchKey = queryDict.get('searchKey', '') searchType = queryDict.get('searchType', '') simCardSource = queryDict.get('simCardSource', '') startTime = datetime.datetime.strptime(startTimeStr + ' 00:00:00', "%Y-%m-%d %H:%M:%S") endTime = datetime.datetime.strptime(endTimeStr + ' 23:59:59', "%Y-%m-%d %H:%M:%S") queryArr = ['expireTime__gte=startTime', 'expireTime__lte=endTime'] if searchType == 'IMSI' and searchKey != '': queryArr.append('imsi=searchKey') elif searchType == 'ICCID' and searchKey != '': queryArr.append('iccid=searchKey') else: pass if simCardSource != '': queryArr.append('supplier=searchKey') query = ','.join(queryArr) ss = eval('SIMCard.objects({})'.format(query)) records = [] for _ in ss: records.append( OrderedDict([ (u'imsi', _.imsi), (u'iccid', _.iccid), (u'卡商', _.supplier), (u'过期时间', _.expireTime.strftime(Const.DATETIME_FMT)), ]) ) generate_excel_report(filepath, records) return def export_device_excel_from_db(filepath, queryDict): logger.info('start export_device_excel_from_db,filepath=%s,query=%s' % (filepath, queryDict)) logicalCodeList = queryDict.get('logicalCodeList', None) ds = Device.objects(logicalCode__in=logicalCodeList) if ds.count() == 0: logger.error('devices are not found') return dds = [_.logicalCode for _ in ds] records = [] for _ in dds: try: d = Device.objects(logicalCode=_).first() try: dd = Device.get_dev(d.devNo) except Exception as e: continue # 不存在的设备跳过 if d is None: continue # 1.检测是否在线 if dd.online != 0: onlineStatus = 1 else: onlineStatus = 0 # 2.检测最近离线 if dd.offTime != 0: lastOfflineTime = datetime.datetime.fromtimestamp(int(str(dd.offTime)[0:10])).strftime("%Y-%m-%d") else: lastOfflineTime = 0 # 3.检测流量卡充值时间 simRechargeRcd = DealerRechargeRecord.objects( __raw__={'dealerId': d.ownerId, 'status': 'Paid', 'name': {'$regex': str(_)}}).order_by('-id').first() if simRechargeRcd is not None: simRechargeTime = simRechargeRcd.finishedTime.strftime("%Y-%m-%d") else: simRechargeTime = 0 try: # 4.检测设备过期时间 if d.simExpireDate is not None: simExpireTime = d.simExpireDate.strftime("%Y-%m-%d") elif d.expireDate is not None: simExpireTime = d.expireDate.strftime("%Y-%m-%d") else: simExpireTime = 'None' except Exception as e: continue records.append( OrderedDict([ (u'设备编号', _), (u'最近离线时间', lastOfflineTime), (u'流量卡过期时间', simExpireTime), (u'最后充值时间', simRechargeTime), (u'是否在线', onlineStatus), ]) ) except Exception as e: print e generate_excel_report(filepath, records) return class BaseSum(object): def __init__(self, fieldMap, filters=None, checkKey=True): if filters is None: filters = dict() self._fieldMap = fieldMap self._filters = filters self._check = checkKey def __repr__(self): return self.__class__.__name__ def sub_balance(self, obj, incomeType, no_check = False): balance_dict = getattr(obj, self._fieldMap.get(incomeType)) balanceList = list() frozenBalanceList = list() for key, value in balance_dict.iteritems(): if not WithdrawGateway.is_ledger(key): continue balanceList.append(self.get_balance(key, value, no_check)) return sum_rmb(balanceList), sum_rmb(frozenBalanceList) def get_balance(self, key, value, no_check = False): if no_check or not self._check: return value.balance else: return value.balance if settings.MY_PRIMARY_AGENT_ID in key.split("-") else 0 def get_frozen_balance(self, key, value, no_check): if no_check or not self._check: return value.frozenBalance else: return value.frozenBalance if settings.MY_PRIMARY_AGENT_ID in key.split("-") else 0 def get_model_objects(self): return list() def get_sum_data(self): return list() class AgentSum(BaseSum): def get_model_objects(self): return Agent.objects.filter(**self._filters) def get_sum_data(self): dataList = list() total = float(0) totalFrozen = float(0) for agent in self.get_model_objects(): adBalance, adFrozenBalance = self.sub_balance(agent, "ad", True) withdrawBalance, withdrawFrozenBalance = self.sub_balance(agent, "withdraw") deviceBalance, deviceFrozenBalance = self.sub_balance(agent, "device") trafficBalance, trafficFrozenBalance = self.sub_balance(agent, "traffic", True) totalBalance = adBalance + withdrawBalance + deviceBalance + trafficBalance totalFrozenBalance = adFrozenBalance + withdrawFrozenBalance + deviceFrozenBalance + trafficFrozenBalance total += float(totalBalance) totalFrozen += float(totalFrozenBalance) dataList.append(OrderedDict([ (u"代理商", str(agent.nickname)), (u"联系方式", str(agent.username)), (u"广告余额", float(adBalance)), (u"广告余额(冻结)", float(adFrozenBalance)), (u"提现余额", float(withdrawBalance)), (u"提现余额(冻结)", float(withdrawFrozenBalance)), (u"设备余额", float(deviceBalance)), (u"设备余额(冻结)", float(deviceFrozenBalance)), (u"交通余额", float(trafficBalance)), (u"交通余额(冻结)", float(trafficFrozenBalance)), (u"余额", float(totalBalance)), (u"余额(冻结)", float(totalFrozenBalance)), ("", ""), (u"总余额", float(totalBalance + totalFrozenBalance)) ])) dataList.insert(0, OrderedDict([])) dataList.insert(0, OrderedDict([ (u"代理商", "总统计"), (u"联系方式", "没排除自己的"), (u"广告余额", ""), (u"广告余额(冻结)", ""), (u"提现余额", ""), (u"提现余额(冻结)", ""), (u"设备余额", ""), (u"设备余额(冻结)", ""), (u"交通余额", ""), (u"交通余额(冻结)", ""), (u"余额", float(total)), (u"余额(冻结)", float(totalFrozen)), ("", ""), (u"总余额", float(total+totalFrozen)) ])) return dataList class DealerSum(BaseSum): def get_model_objects(self): return Dealer.objects.filter(**self._filters) def get_sum_data(self): dataList = list() total = float(0) totalFrozen = float(0) for dealer in self.get_model_objects(): deviceBalance, deviceFrozenBalance = self.sub_balance(dealer, "device") adBalance, adFrozenBalance = self.sub_balance(dealer, "ad", True) totalBalance = deviceBalance + adBalance totalFrozenBalance = deviceFrozenBalance + adFrozenBalance total += float(totalBalance) totalFrozen += float(totalFrozenBalance) dataList.append(OrderedDict([ (u"经销商", str(dealer.nickname)), (u"联系方式", str(dealer.username)), (u"设备余额", float(deviceBalance)), (u"设备余额(冻结)", float(deviceFrozenBalance)), (u"广告余额", float(adBalance)), (u"广告余额(冻结)", float(adFrozenBalance)), (u"余额", float(totalBalance)), (u"余额(冻结)", float(totalFrozenBalance)), (u"", ""), (u"总余额", float(totalBalance)) ])) dataList.insert(0, OrderedDict([])) dataList.insert(0, OrderedDict([ (u"经销商", "总统计"), (u"联系方式", "没排除自己的"), (u"设备余额", ""), (u"设备余额(冻结)", ""), (u"广告余额", ""), (u"广告余额(冻结)", ""), (u"余额", float(total)), (u"余额(冻结)", float(totalFrozen)), (u"", ""), (u"总余额", float(total+totalFrozen)) ])) return dataList class SumContext(object): def __init__(self, strategy=None): self._strategy = strategy @property def strategy(self): return self._strategy @strategy.setter def strategy(self, strategy): if isinstance(strategy, BaseSum): self._strategy = strategy def sum_balance(self): return self.strategy.get_sum_data() class Writer(object): def __init__(self, path): if not os.path.exists(path): raise Exception("path is not exists!") filePath = os.path.join(path, str(datetime.date.today())) fileName = "{}.xlsx".format(filePath) self._writer = pandas.ExcelWriter(fileName) self._context = SumContext() def __enter__(self): return self def __exit__(self, exc_type, exc_val, exc_tb): if exc_val: logger.error(exc_val) else: self._writer.save() @property def context(self): return self._context @property def write_data(self): return self.context.sum_balance() def write(self, sheetName=None): if sheetName is None: sheetName = str(self.context.strategy) dataForm = pandas.DataFrame(self.write_data) dataForm.to_excel(self._writer, sheet_name=sheetName, index=False) def sum_customer(path=None): if path is None: path = "/var/www/uploaded/sumIncome" with Writer(path=path) as w: w.context.strategy = AgentSum(fieldMap=AgentConst.MAP_TYPE_TO_FIELD, checkKey=True) w.write() w.context.strategy = DealerSum(fieldMap=DealerConst.MAP_TYPE_TO_FIELD, checkKey=True) w.write() def handle_customer_complaints_yesterday(): def submitAndFeedback(mchid, origin_mchid, complaintId, responseContent): trueMchid = origin_mchid if mchid == '' else mchid submitResult = wechatComplaintUtil.submit_complaint_reply(mchid=trueMchid, complaintId=complaintId, responseContent=responseContent) if submitResult != 204: logger.error('handle_customer_complaints, submit error, c_id=%s' % complaintId) else: wechatComplaintUtil.feedback_complaint_completed(trueMchid, complaintId) logger.info('done! for handle_customer_complaints %s' % complaintId) if settings.SKIP_HANDLE_WECHAT_COMPLAINT_TASK: logger.debug('SKIP_HANDLE_WECHAT_COMPLAINT_TASK is True.') return # 处理投诉有单次最大处理限制或者单次最大处理时间限制，所以需要重复执行几次 for times in range(3): crt = ComplaintResponseTemplate() wsps = WechatServiceProvider.objects() for wsp in wsps: wechatComplaintUtil = WechatComplaint( mchid=wsp.mchid, asn=wsp.apiclient_serial_number, slk=wsp.sslKey ) yesterday = (datetime.date.today() + datetime.timedelta(days=-1)).strftime("%Y-%m-%d") threeDaysAgo = (datetime.date.today() + datetime.timedelta(days=-20)).strftime("%Y-%m-%d") wechatComplaints = wechatComplaintUtil.query_complaint_list(dateStart=threeDaysAgo, dateEnd=yesterday) loop = 1 while len(wechatComplaints) == loop * 30: tempWechatComplaints = wechatComplaintUtil.query_complaint_list(dateStart=threeDaysAgo, dateEnd=yesterday, pageIndex=loop * 30, pageSize=30) wechatComplaints.extend(tempWechatComplaints) loop += 1 if loop == 100: break trueWechatComplaints = [w for w in wechatComplaints if w['complaint_state'] == u'PENDING'] for _ in trueWechatComplaints: mchid = _.get('complainted_mchid', '') userComplaintTimes = _.get('user_complaint_times', 1) try: complaintOrderInfo = _.get('complaint_order_info', []) complaintId = _['complaint_id'] if not complaintOrderInfo: logger.error('handle_customer_complaints, no complaintOrderInfo') continue transactionId = complaintOrderInfo[0].get('transaction_id', '') if transactionId == '': logger.error('handle_customer_complaints, no transaction_id') continue rechargeRecord = RechargeRecord.objects(__raw__={ '$or': [ { 'wxOrderNo': transactionId }, { 'transactionId': transactionId } ]}).first() if rechargeRecord is None: outTradeNo = complaintOrderInfo[0].get('out_trade_no', '') logger.error('handle_customer_complaints, no rechargeRecord, id=%s' % transactionId) responseContent = crt.NO_RECHARGE_RECORD_CONTENT submitAndFeedback(mchid, wsp.mchid, complaintId, responseContent) DingDingRobot().send_msg(msg=u'[客诉异常] 投诉单关联的商户订单号不存在, outTradeNo=%s' % outTradeNo) continue dealer = Dealer.objects(id=rechargeRecord.ownerId).first() if dealer is None: logger.error('handle_customer_complaints, no dealer, ref_id=%s' % str(rechargeRecord.id)) continue startKey = rechargeRecord.attachParas.get('startKey', '') refundedType = 0 consumeRecord = None refundedMoney = 0 refundedCash = 0 if startKey != '': consumeRecord = ConsumeRecord.objects(startKey=startKey).first() if consumeRecord is not None: if consumeRecord.isNormal is True: refundedMoney = consumeRecord.servicedInfo.get('refundedMoney', 0.0) # 金币 if str(refundedMoney) == str(consumeRecord.coin): refundedType = 1 refundedCash = consumeRecord.servicedInfo.get('refundedCash', 0.0) # 现金 if str(refundedCash) == str(consumeRecord.money): refundedType = 2 else: refundedType = 3 device = Device.objects(logicalCode=rechargeRecord.logicalCode).first() if device is None: logger.error('handle_customer_complaints, no device, ref_id=%s' % str(rechargeRecord.id)) deviceTypeStr = u'设备' else: deviceTypeStr = device.devTypeName if userComplaintTimes == 1: if refundedType == 1: responseContent = crt.REFUND_RESPONSE_CONTENT_1.format(deviceTypeStr, rechargeRecord.logicalCode, consumeRecord.finishedTime.strftime('%Y-%m-%d %H:%M:%S'), str(refundedMoney)) elif refundedType == 2: responseContent = crt.REFUND_RESPONSE_CONTENT_2.format(deviceTypeStr, rechargeRecord.logicalCode, consumeRecord.finishedTime.strftime('%Y-%m-%d %H:%M:%S'), str(refundedCash)) elif refundedType == 3: responseContent = crt.REFUND_RESPONSE_CONTENT_3.format(deviceTypeStr, rechargeRecord.logicalCode) else: responseContent = crt.COMMON_RESPONSE_CONTENT.format(rechargeRecord.dateTimeAdded.strftime('%Y-%m-%d %H:%M:%S'), rechargeRecord.logicalCode, deviceTypeStr, str(rechargeRecord.money), rechargeRecord.orderNo, dealer.service_phone) elif userComplaintTimes == 2: responseContent = crt.COMMON_RESPONSE_CONTENT_2.format(dealer.service_phone) elif userComplaintTimes == 3: responseContent = crt.REAL_PROBLEM_RESPONSE_CONTENT.format(dealer.service_phone) else: responseContent = crt.ERROR_RESPONSE_CONTENT submitAndFeedback(mchid, wsp.mchid, complaintId, responseContent) except Exception as e: logger.error('error=%s, error_mchid=%s' % (e, mchid)) continue