# -*- coding: utf-8 -*- # !/usr/bin/env python import datetime from collections import OrderedDict import arrow import requests from bson import ObjectId from celery.utils.log import get_task_logger from django.conf import settings from django.core.cache import cache from typing import TYPE_CHECKING, Optional, Union from apilib.utils_datetime import timestamp_to_dt, to_datetime from apps.web.agent.models import Agent from apps.web.agent.withdraw import AgentWithdrawRetryService from apps.web.common.models import WithdrawRecord from apps.web.common.proxy import ClientConsumeModelProxy from apps.web.common.transaction import WithdrawStatus from apps.web.constant import Const, MONTH_DATE_KEY, MANAGER_EXPORT_EXCEL_TYPE, DEALER_CONSUMPTION_AGG_KIND_TRANSLATION from apps.web.core import ROLE from apps.web.core.payment import WithdrawGateway from apps.web.core.payment.ali import AliPayWithdrawGateway, AlipayWithdrawQueryResult from apps.web.core.payment.wechat import WechatWithdrawQueryResult, WechatWithdrawGateway from apps.web.core.sysparas import SysParas from apps.web.core.utils import generate_excel_report from apps.web.dealer.models import DealerRechargeRecord, Dealer from apps.web.dealer.proxy import DealerIncomeProxy from apps.web.dealer.withdraw import DealerWithdrawRetryService from apps.web.device.models import Device, Group from apps.web.exceptions import WithdrawOrderNotExist from apps.web.helpers import get_inhouse_wechat_manager_mp_proxy from apps.web.management.models import Manager from apps.web.management.utils import get_dealerMap_by_managerId, query_device_income, query_device_consumption, \ get_user_stats, get_feed_back_stats, query_total_dealer_income_top, query_user_consume_frequency, \ get_device_being_used_trend from apps.web.report.models import DealerMonthlyStat from apps.web.user.models import RechargeRecord, CardRechargeOrder, MyUser, ConsumeRecord if TYPE_CHECKING: from apps.web.common.models import CapitalUser logger = get_task_logger(__name__) def _tell_finance_manager(account, obj, content): system_alarmer_list = SysParas.get_system_alarmer() # type: dict if not system_alarmer_list or len(system_alarmer_list) == 0: logger.debug('not set system alarm manager.') return for system_alarmer in system_alarmer_list: openId = system_alarmer.get('openId', None) if not openId: logger.debug('not set system alarm manager.') continue proxy = get_inhouse_wechat_manager_mp_proxy() proxy.notify(openId, 'system_alarm_notify', **{ 'title': u'{}同学,系统告警被触发,请关注,尽快处理。'.format(system_alarmer.get('username', '')), 'account': account, 'object': obj, 'content': content, 'time': arrow.now().format('YYYY-MM-DD HH:mm:ss') }) def weekly_notify_finance_manager(): """ 默认为星期3和星期5推送 提醒财务经理处理财务信息 [0] 注意调配资金池,确保客户能够正常提现 :return: """ now = datetime.datetime.now() weekday = now.weekday() logger.info('executing weekly_notify_finance_manager, today is %s' % (now.strftime('%Y-%m-%d %H:%M:%S'),)) #: 只在周一周三周五提醒 if weekday in (0, 2, 4): offset = now - datetime.timedelta(days = weekday + 1) withdrawal_sum = WithdrawRecord.objects(postTime__lte = now, postTime__gte = offset).sum('actualPay') appendix = u'今日为周%s,请注意额外添置!' % (weekday + 1,) return _tell_finance_manager(account = u'微付乐', obj = u'资金池', content = (u'请注意调配情况,本周一到今天的总额为%.2f' % (withdrawal_sum,)) + appendix) def whale_withdraw_order_alert(): """ 提现出现大单,预警资金池情况 :return: """ return _tell_finance_manager(account = u'微付乐', obj = u'资金池', content = u'请注意,有大单(金额 >= %s),请及时检查池情况!' % (settings.WHALE_WITHDRAWAL_ORDER_AMOUNT,)) def withdraw_error_alert(err_type): """ 当提现出现错误(大概率下的资金池不足),及时提醒财务经理进行操作 :return: """ if err_type == 'NOTENOUGH': return _tell_finance_manager(account = u'微付乐', obj = u'资金池', content = u'[重要]微信支付商户号资金不足,客户无法提现,请及时处理!') def check_website_is_online(): """ 每隔一段时间向几个页面发出请求,以确保页面能够正常工作 带上特定的user-agent :return: """ url = settings.SERVER_END_BASE_URL response = requests.get(url, timeout = 15) if response.status_code != 200: _tell_finance_manager(account = u'微付乐', object = u'服务器', content = u'服务器网站无法连通,请及时检查!') def backup_wechat_subscription_account_messages(): """ 每隔三天,腾讯会清空用户发至公众号的信息。 这里需要备份,以确保 [0] 能够及时收集用户反馈 [1] 能够追踪用户出现问题时,对系统发的图片以及 :return: """ def backup_third_party_order_bills(): """ 将微信与支付宝的订单拉取备份,以方便后台对账 :return: """ def generate_simCharge_excel_report(filepath, queryAttrs): queryAttrs['dateTimeAdded__lte'] = timestamp_to_dt(queryAttrs['dateTimeAdded__lte']) queryAttrs['dateTimeAdded__gte'] = timestamp_to_dt(queryAttrs['dateTimeAdded__gte']) managerId = queryAttrs['managerId'] startTime = queryAttrs['dateTimeAdded__gte'] endTime = queryAttrs['dateTimeAdded__lte'] agentId = queryAttrs.get('agentId', '') dealerId = queryAttrs.get('dealerId', '') if agentId == '' and dealerId == '': agentsIdList = [str(_['_id']) for _ in Agent.get_collection().find({'managerId': managerId}, {'_id': 1})] dealersIdList = [str(_['_id']) for _ in Dealer.get_collection().find({'agentId': {'$in': agentsIdList}}, {'_id': 1})] simRechargeRecords = DealerRechargeRecord.objects( dealerId__in = dealersIdList, status = DealerRechargeRecord.PayState.Paid, finishedTime__gte = startTime, finishedTime__lte = endTime ) elif agentId != '' and dealerId == '': dealersIdList = [str(_['_id']) for _ in Dealer.get_collection().find({'agentId': agentId}, {'_id': 1})] simRechargeRecords = DealerRechargeRecord.objects( dealerId__in = dealersIdList, status = DealerRechargeRecord.PayState.Paid, finishedTime__gte = startTime, finishedTime__lte = endTime ) elif agentId != '' and dealerId != '': simRechargeRecords = DealerRechargeRecord.objects( dealerId = dealerId, status = DealerRechargeRecord.PayState.Paid, finishedTime__gte = startTime, finishedTime__lte = endTime ) else: return None records = [] for simRechargeRecord in simRechargeRecords.order_by('-finishedTime'): dealer = Dealer.objects(id = simRechargeRecord.dealerId).first() if not dealer: logger.error(u'该经销商用户不存在, id=%s' % simRechargeRecord.dealerId) return None agent = Agent.objects(id = dealer.agentId).first() if not agent: logger.error(u'该代理商用户不存在, id=%s' % dealer.agentId) return None device = Device.objects(devNo = simRechargeRecord.items[0]['devNo']).first() if not device: logger.error(u'该设备信息不存在, devNo=%s' % simRechargeRecord.items[0]['devNo']) return None try: sumPrice = str(simRechargeRecord.sum_of_price) except Exception as e: continue records.append( OrderedDict([ (u'经销商', dealer.nickname), (u'手机号码', dealer.username), (u'所属代理商', agent.nickname), (u'设备编号', device.logicalCode), (u'设备类型', device.devTypeName), (u'设备注册时间', device.dateTimeAdded), (u'充值金额', sumPrice), (u'充值日期', datetime.datetime.strftime(simRechargeRecord.finishedTime, Const.DATETIME_FMT)) ]) ) generate_excel_report(filepath, records) def generate_dealerWithDraw_excel_report(filepath, queryAttrs): queryAttrs['dateTimeAdded__lte'] = timestamp_to_dt(queryAttrs['dateTimeAdded__lte']) queryAttrs['dateTimeAdded__gte'] = timestamp_to_dt(queryAttrs['dateTimeAdded__gte']) managerId = queryAttrs['managerId'] startTime = queryAttrs['dateTimeAdded__gte'] endTime = queryAttrs['dateTimeAdded__lte'] agentsIdList = [str(_['_id']) for _ in Agent.get_collection().find({'managerId': managerId}, {'_id': 1})] dealersIdList = [str(_['_id']) for _ in Dealer.get_collection().find({'agentId': {'$in': agentsIdList}}, {'_id': 1})] withdrawRecords = WithdrawRecord.objects( ownerId__in = dealersIdList, role = ROLE.dealer, status = WithdrawStatus.SUCCEEDED, postTime__gte = startTime, postTime__lte = endTime ) records = [] for withdrawRecord in withdrawRecords.order_by('-postTime'): dealer = Dealer.objects(id = withdrawRecord.ownerId).first() if not dealer: logger.error(u'没有找到该经销商, id=%s' % withdrawRecord.ownerId) return None records.append( OrderedDict([ (u'经销商', dealer.nickname), (u'手机号码', dealer.username), (u'提现金额', str(withdrawRecord.amount)), (u'提现日期', datetime.datetime.strftime(withdrawRecord.postTime, Const.DATETIME_FMT)) ]) ) generate_excel_report(filepath, records) def generate_manager_map_options(): midList = [str(_['_id']) for _ in list(Manager.get_collection().find({}, {'_id': 1}))] for mid in midList: # todo 先过滤掉自己的厂商, 数据太大会给celery造成负担, 后期可以改成有需要的厂商进行显示 if mid == '59974b4b8732d6480fb699e5': continue agentIdList = [str(_['_id']) for _ in list(Agent.get_collection().find({'managerId': mid}, {'_id': 1}))] dealerDict = {str(item['_id']): item['nickname'] for item in Dealer.get_collection().find({'agentId': {'$in': agentIdList}}, {'_id': 1, 'nickname': 1})} cache.set('getAllDeviceIncomeList_%s' % mid, query_device_income(dealerDict.keys()), 24 * 60 * 60) cache.set('getAllDeviceConsumption_%s' % mid, query_device_consumption(dealerDict.keys()), 24 * 60 * 60) cache.set('getAllUserStatistics_%s' % mid, get_user_stats(dealerDict.keys()), 24 * 60 * 60) cache.set('getAllFeedbackStatistics_%s' % mid, get_feed_back_stats(dealerDict.keys()), 24 * 60 * 60) cache.set('getDealerIncomeTotalTop_%s' % mid, query_total_dealer_income_top(dealerDict), 24 * 60 * 60) cache.set('getUserConsumeFrequency_%s' % mid, query_user_consume_frequency(dealerDict.keys()), 24 * 60 * 60) cache.set('getDevBeingUsedTrend_%s' % mid, get_device_being_used_trend(dealerDict.keys()), 24 * 60 * 60) def generate_biz_stats_for_manager(filepath, queryAttrs): endTime = timestamp_to_dt(queryAttrs['dateTimeAdded__lte']) startTime = timestamp_to_dt(queryAttrs['dateTimeAdded__gte']) agentId = queryAttrs.get('agentId') dealerId = queryAttrs.get('dealerId') managerId = queryAttrs['managerId'] dealerMap = get_dealerMap_by_managerId(managerId = managerId, agentId = agentId, dealerId = dealerId) dealerIds = dealerMap.keys() if queryAttrs['type'] == MANAGER_EXPORT_EXCEL_TYPE.RECHARGE: queryset = RechargeRecord.get_recharged_records( ownerId__in = dealerIds, dateTimeAdded__lte = endTime, dateTimeAdded__gte = startTime ) records = [ OrderedDict([ (u"订单号", record.orderNo), (u"支付网关订单号", record.wxOrderNo), (u"用户昵称", record.nickname), (u"经销商", dealerMap[record.ownerId]['nickname']), (u"代理商名称", dealerMap[record.ownerId]['agentName']), (u"设备编码", record.logicalCode), (u"设备类型", record.dev_type_name), (u"金额", str(record.money)), (u"地址", record.groupName), (u"时间", record.to_datetime_str(record.dateTimeAdded)) ]) for record in queryset ] elif queryAttrs['type'] == MANAGER_EXPORT_EXCEL_TYPE.RECHARGE_CARD: queryset = RechargeRecord.get_card_recharged_records( ownerId__in = dealerIds, dateTimeAdded__lte = endTime, dateTimeAdded__gte = startTime ) records = [] for record in queryset: # type: RechargeRecord order = CardRechargeOrder.get_by_rechargeRecord(record) entry = OrderedDict([ (u"订单号", record.orderNo), (u"支付网关订单号", record.wxOrderNo), (u"卡号", order.cardNo if order else ''), (u"用户昵称", record.nickname), (u"经销商", dealerMap[record.ownerId]['nickname']), (u"代理商名称", dealerMap[record.ownerId]['agentName']), (u"设备编码", record.logicalCode), (u"设备类型", record.dev_type_name), (u"金额", str(record.money)), (u"地址", record.groupName), (u"时间", record.to_datetime_str(record.dateTimeAdded)) ]) records.append(entry) else: return generate_excel_report(filepath, records) def check_withdraw_via_bank(): """ 由于不能实时获取是否微信或者支付宝成功给银行卡转账了,需要每日进行查询并更新状态 主要处理银行退单的情况,则需要将款项返还给经销商/代理商 :return: """ def process_withdraw(record): # type: (WithdrawRecord)->None try: payee = ROLE.from_role_id(record.role, str(record.ownerId)) # type: CapitalUser handler = payee.new_withdraw_handler(record) try: gateway = WithdrawGateway.from_withdraw_gateway_key( record.withdrawGatewayKey, record.extras.get( 'gateway_version', 'v1')) # type: Optional[WechatWithdrawGateway, AliPayWithdrawGateway] query_result = gateway.get_transfer_result_via_bank( record.order) # type: Union[WechatWithdrawQueryResult, AlipayWithdrawQueryResult] errcode, errmsg = query_result.error_desc if query_result.is_failed or query_result.is_refund: handler.revoke(remarks = errcode, description = errmsg) elif query_result.is_successful: handler.approve(finishedTime = query_result.finished_time, extra = query_result.extra) except WithdrawOrderNotExist: logger.warning('withdraw order is not exist.'.format(record.order)) handler.revoke(remarks = u'订单不存在', description = u'提现失败') except Exception as e: logger.exception(e) def process_v3_withdraw(record): # type: (WithdrawRecord)->None try: version = record.extras.get('gateway_version', 'v1') if version != 'v3': logger.warning('record is not v3.'.format(str(record.id))) return payee = ROLE.from_role_id(record.role, str(record.ownerId)) # type: CapitalUser handler = payee.new_withdraw_handler(record) try: gateway = WithdrawGateway.from_withdraw_gateway_key( record.withdrawGatewayKey, record.extras.get( 'gateway_version', 'v1')) # type: WechatWithdrawGateway query_result = gateway.get_transfer_result_via_changes( record.order) # type: WechatWithdrawQueryResult errcode, errmsg = query_result.error_desc if query_result.is_failed or query_result.is_refund: handler.revoke(remarks = errcode, description = errmsg) elif query_result.is_successful: handler.approve(finishedTime = query_result.finished_time, extra = query_result.extra) except WithdrawOrderNotExist: logger.warning('withdraw order is not exist.'.format(record.order)) handler.revoke(remarks = u'订单不存在', description = u'提现失败') except Exception as e: logger.exception(e) records = list(WithdrawRecord.get_processing_via_bank()) for record in records: process_withdraw(record) records = list(WithdrawRecord.get_processing_via_v3()) for record in records: process_v3_withdraw(record) def check_and_retry_withdraw(): def process_dealer_withdraw(record): # type: (WithdrawRecord)->None try: DealerWithdrawRetryService(record).execute() except Exception as e: logger.exception(e) def process_agent_withdraw(record): # type: (WithdrawRecord)->None try: AgentWithdrawRetryService(record).execute() except Exception as e: logger.exception(e) records = list(WithdrawRecord.get_failed_records()) for record in records: # type: WithdrawRecord if (datetime.datetime.now() - record.postTime).days > 5: logger.debug('record has not been done in five days.'.format(str(record.id))) continue logger.debug('now retry withdraw for record'.format(str(record.id), record.order)) if record.role == ROLE.dealer: process_dealer_withdraw(record) elif record.role == ROLE.agent: process_agent_withdraw(record) else: logger.error('invalid withdraw role') def manager_export_charge_order_excel_from_db(filepath, queryDict): logger.error('start manager_export_charge_order_excel_from_db,filepath=%s,query=%s' % (filepath, queryDict)) startDate = queryDict.get('startTime', None) endDate = queryDict.get('endTime', None) dealerId = queryDict.get('dealerId', '') agentId = queryDict.get('agentId', '') managerId = queryDict.get('managerId', '') chargeTypeDict = {'recharge': u'充值', 'sendcoin': u'赠币', 'refund': u'退币', 'chargeCard': u'卡充值', 'chargeVirtualCard': u'虚拟卡充值'} userDict = {} if agentId == '' and dealerId == '': agentDict = {} agentsIdList = [] for agent in Agent.get_collection().find({'managerId': managerId}, {'_id': 1, 'username': 1, 'nickname': 1}): agentDict[str(agent['_id'])] = '%s %s' % (agent['nickname'], agent['username']) agentsIdList.append(str(agent['_id'])) dealerDict = {} dealersIdList = [] for dealer in Dealer.get_collection().find({'agentId': {'$in': agentsIdList}}, {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1}): dealerDict[str(dealer['_id'])] = {'dealerName': '%s %s' % (dealer['nickname'], dealer['username']), 'agentName': agentDict.get(dealer['agentId'], '')} dealersIdList.append(str(dealer['_id'])) records = RechargeRecord.objects(ownerId__in = dealersIdList, result = 'success', dateTimeAdded__gte = to_datetime(startDate), dateTimeAdded__lte = to_datetime(endDate), via__nin = ['sendcoin', 'refund']).order_by('-dateTimeAdded') elif agentId != '' and dealerId == '': agents = Agent.get_collection().find({'_id': ObjectId(agentId)}, {'_id': 1, 'username': 1, 'nickname': 1}) dealerDict = {} dealersIdList = [] for dealer in Dealer.get_collection().find({'agentId': agentId}, {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1}): dealerDict[str(dealer['_id'])] = {'dealerName': '%s %s' % (dealer['nickname'], dealer['username']), 'agentName': '%s %s' % (agents[0]['nickname'], agents[0]['username'])} dealersIdList.append(str(dealer['_id'])) records = RechargeRecord.objects(ownerId__in = dealersIdList, result = 'success', dateTimeAdded__gte = to_datetime(startDate), dateTimeAdded__lte = to_datetime(endDate), via__nin = ['sendcoin', 'refund']).order_by('-dateTimeAdded') elif agentId != '' and dealerId != '': agents = Agent.get_collection().find({'_id': ObjectId(agentId)}, {'_id': 1, 'username': 1, 'nickname': 1}) dealers = Dealer.get_collection().find({'_id': ObjectId(dealerId)}, {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1}) dealerDict = {} dealerDict[str(dealers[0]['_id'])] = {'dealerName': '%s %s' % (dealers[0]['nickname'], dealers[0]['username']), 'agentName': '%s %s' % (agents[0]['nickname'], agents[0]['username'])} records = RechargeRecord.objects(ownerId = dealerId, result = 'success', dateTimeAdded__gte = to_datetime(startDate), dateTimeAdded__lte = to_datetime(endDate), via__nin = ['sendcoin', 'refund']).order_by('-dateTimeAdded') else: records = [] groupIds = [] rcdList = [] for obj in records: rcdList.append(obj) groupIds.append(obj.groupId) groupIds = list(set(groupIds)) partnerDict = {} for groupId in groupIds: group = Group.get_group(groupId) if group is None: continue partnerDict.update(group.get('partnerDict', {})) rows = [] for rcd in rcdList: if rcd.via in ['refund', 'sendcoin']: continue # 检查下如果是卡充值,就需要检查是否已经完成整个流程 if rcd.via == 'chargeCard' and CardRechargeOrder.objects.filter(rechargeNo = rcd.id, status = 'finished').count() == 0: continue openId = rcd.openId if not userDict.has_key(openId): try: user = MyUser.objects.filter(openId = openId).only('openId', 'nickname', 'sex', 'groupId', 'country', 'province', 'city').first() sex = u'未知' if user.sex == 0: sex = u'女' elif user.sex == 1: sex = u'男' else: pass user = {'sex': sex, 'nickname': user.nickname, 'zone': '%s%s%s' % (user.country, user.province, user.city)} userDict[openId] = user except Exception, e: continue else: user = userDict[openId] try: proxyRcd = DealerIncomeProxy.objects.get(ref_id = rcd.id) except Exception, e: continue # 获取代理商、经销商的收入 amountDict = DealerIncomeProxy.get_agent_partner_allocated_money(rcd.ownerId, proxyRcd.partition, partnerDict) dataList = [ (u'代理商', dealerDict.get(rcd.ownerId, {}).get('agentName')), (u'经销商', dealerDict.get(rcd.ownerId, {}).get('dealerName')), (u'逻辑编码', rcd.logicalCode), (u'IMEI', rcd.devNo), (u'设备类型', rcd.dev_type_name), (u'组名称', rcd.groupName), (u'组内编号', rcd.groupNumber), (u'组地址', rcd.address), (u'用户昵称', user.get('nickname', '')), (u'用户性别', user.get('sex', '')), (u'用户地域', user.get('zone', '')), (u'订单号', rcd.orderNo), (u'下单时间', rcd.time), (u'第三方支付单号', rcd.wxOrderNo), (u'支付方式', u'微信' if rcd.gateway == 'wechat' else u'支付宝'), (u'是否快捷支付', 'yes' if rcd.isQuickPay else 'no'), (u'充值金额', str(rcd.money)), (u'充值金币', str(rcd.coins)), (u'充值方式', chargeTypeDict.get(rcd.via, '')), # 卡充值、虚拟卡充值、直接充值等 (u'设备负责人分配收入', str(amountDict.get('ownerAmount'))), (u'代理商分配金额', str(amountDict.get('agentAmount'))) ] # 把所有合伙人的分配都记录下来 for partnerIncome in amountDict.get('partnerDict').values(): dataList.append((u'合伙人分配金额:%s(%s)' % (partnerIncome['nickname'], partnerIncome['username']), str(partnerIncome['money']))) rows.append(OrderedDict(dataList)) generate_excel_report(filepath, rows) logger.error('finished export_charge_order_excel_from_db') def manager_export_consume_order_excel_from_db(filepath, queryDict): logger.error('start manager_export_consume_order_excel_from_db,filepath=%s,query=%s' % (filepath, queryDict)) startDate = queryDict.get('startTime', None) endDate = queryDict.get('endTime', None) dealerId = queryDict.get('dealerId', '') agentId = queryDict.get('agentId', '') managerId = queryDict.get('managerId', '') records = [] dealerDict = {} dealersIdList = [] if agentId == '' and dealerId == '': agentDict = {} agentsIdList = [] for agent in Agent.get_collection().find({'managerId': managerId}, {'_id': 1, 'username': 1, 'nickname': 1}): agentDict[str(agent['_id'])] = '%s %s' % (agent['nickname'], agent['username']) agentsIdList.append(str(agent['_id'])) for dealer in Dealer.get_collection().find({'agentId': {'$in': agentsIdList}}, {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1}): dealerDict[str(dealer['_id'])] = {'dealerName': '%s %s' % (dealer['nickname'], dealer['username']), 'agentName': agentDict.get(dealer['agentId'], '')} dealersIdList.append(str(dealer['_id'])) elif agentId != '' and dealerId == '': agents = Agent.get_collection().find({'_id': ObjectId(agentId)}, {'_id': 1, 'username': 1, 'nickname': 1}) for dealer in Dealer.get_collection().find({'agentId': agentId}, {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1}): dealerDict[str(dealer['_id'])] = {'dealerName': '%s %s' % (dealer['nickname'], dealer['username']), 'agentName': '%s %s' % (agents[0]['nickname'], agents[0]['username'])} dealersIdList.append(str(dealer['_id'])) elif agentId != '' and dealerId != '': agents = Agent.get_collection().find({'_id': ObjectId(agentId)}, {'_id': 1, 'username': 1, 'nickname': 1}) dealers = Dealer.get_collection().find({'_id': ObjectId(dealerId)}, {'_id': 1, 'username': 1, 'nickname': 1, 'agentId': 1}) dealerDict[str(dealers[0]['_id'])] = {'dealerName': '%s %s' % (dealers[0]['nickname'], dealers[0]['username']), 'agentName': '%s %s' % (agents[0]['nickname'], agents[0]['username'])} dealersIdList = [dealerId] else: pass rcdList = [] servicedKeysTemp = [] for rcd in ClientConsumeModelProxy.get_data_list(startTime = startDate, endTime = endDate, ownerId__in = dealersIdList, isNormal = True): # type: ConsumeRecord servicedKeysTemp.extend(rcd.servicedInfo.keys()) rcdList.append(rcd) servicedKeysTemp = list(set(servicedKeysTemp)) servicedKeys = [] for key in servicedKeysTemp: if key in DEALER_CONSUMPTION_AGG_KIND_TRANSLATION: servicedKeys.append(key) else: continue userDict = {} records = [] for rcd in rcdList: openId = rcd.openId if not userDict.has_key(openId): try: user = MyUser.objects.filter(openId = openId).only('openId', 'nickname', 'sex', 'groupId', 'country', 'province', 'city').first() sex = u'未知' if user.sex == 0: sex = u'女' elif user.sex == 1: sex = u'男' else: pass user = {'sex': sex, 'nickname': user.nickname, 'zone': '%s%s%s' % (user.country, user.province, user.city)} userDict[openId] = user except Exception, e: continue else: user = userDict[openId] dataList = [ (u'代理商', dealerDict.get(rcd.ownerId, {}).get('agentName')), (u'经销商', dealerDict.get(rcd.ownerId, {}).get('dealerName')), (u'逻辑编码', rcd.logicalCode), (u'IMEI', rcd.devNo), (u'设备端口', rcd.attachParas.get('port', '-')), (u'设备类型', rcd.dev_type_name), (u'组名称', rcd.groupName), (u'组内编号', rcd.groupNumber), (u'组地址', rcd.address), (u'用户昵称', user.get('nickname', '')), (u'用户性别', user.get('sex', '')), (u'用户地域', user.get('zone', '')), (u'订单号', rcd.orderNo), (u'下单时间', rcd.to_datetime_str(rcd.dateTimeAdded)), (u'花费金币', str(rcd.coin)), (u'消费备注', str(rcd.remarks) if rcd.remarks else u'扫码消费'), (u'端口', str(rcd.attachParas.get('port', ''))) ] # 把消费细节也记录到excel表中 for key in servicedKeys: dataList.append((DEALER_CONSUMPTION_AGG_KIND_TRANSLATION.get(key), str(rcd.servicedInfo.get(key, '-')))) records.append(OrderedDict(dataList)) generate_excel_report(filepath, records) def manager_export_dealer_info_excel_from_db(filepath, queryDict): def count_devices(ownerId): devNoList = [device['devNo'] for device in Device.get_collection().find({'ownerId': ownerId, 'groupId': {'$ne': ''}}, {'devNo': 1, '_id': 0})] allCount = len(devNoList) devDict = Device.get_dev_by_nos(devNoList) if not devDict: return 0, 0, 0 onlineCount = 0 for devNo, dev in devDict.items(): if dev["online"]: onlineCount += 1 offlineCount = allCount - onlineCount return onlineCount, offlineCount, allCount logger.error('start manager_export_dealer_info_excel_from_db,filepath=%s,query=%s' % (filepath, queryDict)) mid = queryDict.get("mid") agentId = queryDict.get('agentId', None) searchKey = queryDict.get('searchKey', None) dealers = Dealer.search(searchKey) agentMap = {str(agent.id): agent.username for agent in Agent.objects(managerId=mid)} agentIds = agentMap.keys() if agentId: dealers = dealers.filter(agentId=agentId) elif agentIds: dealers = dealers.filter(agentId__in=agentIds) dataList = [] for d in dealers: onlineCount, offlineCount, allCount = count_devices(str(d.id)) now = datetime.datetime.now() today = now.strftime(Const.DATE_FMT) monthDay = MONTH_DATE_KEY.format(year=now.year, month=now.month) rcds = DealerMonthlyStat.get_collection().find({'dealerId': ObjectId(d.id), 'date': monthDay}, {'addedUserCount': 1}) if rcds.count() == 0: userCountAddedThisMonth = 0 else: info = rcds[0] userCountAddedThisMonth = info.get('addedUserCount', 0) lis = [ (u'商家名称', d.nickname), (u'联系方式', d.username), (u'注册时间', d.dateTimeAdded.strftime(Const.DATETIME_FMT)), (u'用户数', d.userCount), (u'本月添加用户数', userCountAddedThisMonth), (u'设备总数', allCount), (u'在线设备数', onlineCount), (u'离线设备数', offlineCount), (u'商家余额', str(d.total_balance)), ] dataList.append(OrderedDict(lis)) generate_excel_report(filepath, dataList)