manyi/apps/web/management/tasks.py

# -*- 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<orderNo={}> 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<id={}> 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<orderNo={}> 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<id={}> has not been done in five days.'.format(str(record.id)))
            continue

        logger.debug('now retry withdraw for record<id={}, orderNo={}>'.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)