123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577 |
- # -*- 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
|