# -*- coding: utf-8 -*- #!/usr/bin/env python import datetime import os import time 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.bridge.wechat import MyWechatClientV3 from apps.web.core.models import WechatChannelApp 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.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(complaintUtil, mchid, origin_mchid, complaintId, responseContent): # trueMchid = origin_mchid if mchid == '' else mchid # submitResult = complaintUtil.complaint_response( # complaint_id = complaintId, complainted_mchid = trueMchid, response_content = responseContent) # if submitResult != 204: # logger.error('handle_customer_complaints, submit error, c_id=%s' % complaintId) # else: # complaintUtil.complaint_complete(complaint_id = complaintId, complainted_mchid = trueMchid) # 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 channels = [item for item in WechatChannelApp.objects().all()] crt = ComplaintResponseTemplate() for channel in channels: # type: WechatChannelApp loop = 0 pageSize = 30 while True: wechatComplaintUtil = MyWechatClientV3(channel).complaint # type: Complaint yesterday = (datetime.date.today() + datetime.timedelta(days = -1)).strftime("%Y-%m-%d") threeDaysAgo = (datetime.date.today() + datetime.timedelta(days = -20)).strftime("%Y-%m-%d") result = wechatComplaintUtil.complaint_list_query( begin_date = threeDaysAgo, end_date = yesterday, offset = loop * pageSize, limit = pageSize) if 'data' not in result: logger.warning('handle_customer_complaints, no data in query result.') break for _ in [w for w in result['data'] if w['complaint_state'] == u'PENDING']: 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 trueMchid = channel.mchid if mchid == '' else mchid wechatComplaintUtil.complaint_response(complaint_id=complaintId, complainted_mchid=trueMchid, response_content=responseContent) time.sleep(1) wechatComplaintUtil.complaint_complete(complaint_id=complaintId, complainted_mchid=trueMchid) 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 trueMchid = channel.mchid if mchid == '' else mchid wechatComplaintUtil.complaint_response(complaint_id=complaintId, complainted_mchid=trueMchid, response_content=responseContent) time.sleep(1) wechatComplaintUtil.complaint_complete(complaint_id=complaintId, complainted_mchid=trueMchid) except Exception as e: logger.error('error=%s, error_mchid=%s' % (e, mchid)) continue if len(result['data']) < pageSize: break loop = loop + 1