manyi/apps/web/superadmin/tasks.py

# -*- coding: utf-8 -*-
#!/usr/bin/env python

import datetime
import os
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.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