manyi/apps/web/superadmin/tasks.py

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