ASJServer/AdminController/UnicomManageController.py

# Copyright (C) 2022 #
# @Time    : 2022/7/18 16:16
# @Author  : ghl
# @Email   : Guanhailogn@asj6.wecom.work
# @File    : UnicomManageController.py
# @Software: PyCharm
import datetime
import hashlib
import json
import time
import uuid
from decimal import Decimal

import openpyxl
import requests
from django.db import transaction, connection
from django.db.models import Q, F
from django.http import HttpResponse
from django.views.generic.base import View

from Ansjer.config import CONFIG_INFO
from Ansjer.config import LOGGER
from Controller.UnicomCombo.UnicomComboController import UnicomComboView
from Model.models import UnicomDeviceInfo, UnicomCombo, Pay_Type, UnicomComboOrderInfo, Device_User, Order_Model, \
    ExchangeCode, UnicomFlowPush, SysMsgModel, UnicomComboExperienceHistory, LogModel, SerialNumberPackage
from Object.Enums.WXOperatorEnum import WXOperatorEnum
from Object.RedisObject import RedisObject
from Object.ResponseObject import ResponseObject
from Object.TokenObject import TokenObject
from Object.UnicomObject import UnicomObjeect
from Object.WXTechObject import WXTechObject
from Service.CommonService import CommonService


class UnicomManageControllerView(View):

    def get(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        operation = kwargs.get('operation')
        return self.validation(request.GET, request, operation)

    def post(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        operation = kwargs.get('operation')
        return self.validation(request.POST, request, operation)

    def validation(self, request_dict, request, operation):
        response = ResponseObject()
        # 获取支付类型
        if operation == 'get/pay':
            return self.get_pay_type(response)
        # 获取套餐类型
        elif operation == 'combo/type':
            return self.get_unicom_combo_type(response)
        else:
            tko = TokenObject(
                request.META.get('HTTP_AUTHORIZATION'),
                returntpye='pc')
            if tko.code != 0:
                return response.json(tko.code)
            response.lang = tko.lang
            userID = tko.userID
            # 获取套餐详细表
            if operation == 'get/deta/info':
                return self.get_unicom_info(request_dict, response)
            # 添加和编辑卡套餐
            elif operation == 'edit/combo':
                return self.edit_combo(request_dict, response)
            # 统计4G套餐
            elif operation == 'getComboDataList':
                return self.static_info(request_dict, response)
            # 删除卡套餐
            elif operation == 'dele/combo/info':
                return self.combo_order_info(request_dict, response)
            # 获取/筛选用户信息
            elif operation == 'filter/user':
                return self.get_user_info(request_dict, response)
            # 充值流量
            elif operation == 'getFlowPackages':
                return self.get_flow_packages(request_dict, response)
            #  获取/筛选4G流量卡订单信息
            elif operation == 'query-order':
                return self.query_4G_user_order(request_dict, response)
            elif operation == 'sim-info':
                return self.get_iccid_info(request_dict, response)
            elif operation == 'batchGenerateCDK':  # 批量生成兑换码
                return self.create_package_cdk(request_dict, response)
            elif operation == 'getExchangeCodePage':  # 分页获取兑换码
                return self.get_exchange_code_page(request_dict, response)
            elif operation == 'updateExchangeCode':  # 修改兑换码
                return self.update_exchange_code(request_dict, response)
            elif operation == 'resetCardPackage':
                return self.reset_card_package(request, request_dict, response, tko.user)
            elif operation == 'getPackageDetails':
                return self.get_package_details(request_dict, response)
            elif operation == 'asyncUpdateStatus':
                return self.async_update_status(request_dict, response)
            elif operation == 'getFlowComboList':  # 获取流量套餐列表
                return self.get_flow_combo_list(request_dict, response)
            elif operation == 'downloadCDK':  # 下载兑换码
                return self.package_cdk_export_excel(response)
            elif operation == 'uploadSerialNumberFile':  # 上传序列号文件绑定4G套餐
                return self.upload_file(tko.user, request, request_dict, response)
            elif operation == 'iccidBatchReset':  # 上传序列号文件绑定4G套餐
                return self.iccid_batch_reset(tko.user, request, request_dict, response)
            elif operation == 'orderFlowPackage':  # 订购流量套餐包
                return self.order_flow_package(request_dict, response, userID)
            elif operation == 'getSerialNumberPackagePage':  # 订购流量套餐包
                return self.serial_number_package_page(request_dict, response)
            elif operation == 'serialNumberPackageUpdate':  # 订购流量套餐包
                return self.serial_number_package_update(request_dict, response, tko.user)
            else:
                return response.json(404)

    @classmethod
    def order_flow_package(cls, request_dict, response, user):
        """
        订购流量套餐包
        """
        try:
            serial_number = request_dict.get('serialNumber', None)
            package_id = request_dict.get('packageId', None)
            if not all([serial_number, package_id]):
                return response.json(444)
            u_device_info_qs = UnicomDeviceInfo.objects.filter(serial_no=serial_number, card_type=0) \
                .exclude(Q(user_id__isnull=True) | Q(user_id=''))
            if not u_device_info_qs.exists():
                return response.json(173)
            package_id = int(package_id)
            combo_qs = UnicomCombo.objects.filter(id=package_id) \
                .values('id', 'combo_name', 'price', 'virtual_price', 'remark').order_by('sort')
            if not combo_qs.exists():
                return response.json(173)

            now_time = int(time.time())
            combo_info_vo = combo_qs[0]
            u_device_info = u_device_info_qs.first()
            rank_id, ai_rank_id = UnicomComboView.get_cloud_or_ai_combo()  # 生成订单必须添加该字段
            uid = CommonService.query_uid_with_serial(serial_number)

            order_id = CommonService.createOrderID()
            order_dict = {'orderID': order_id, 'UID': uid, 'rank_id': rank_id, 'ai_rank_id': ai_rank_id,
                          'userID_id': u_device_info.user_id, 'desc': combo_info_vo['combo_name'], 'payType': 10,
                          'payTime': now_time, 'price': combo_info_vo['price'], 'addTime': now_time,
                          'updTime': now_time, 'status': 1,
                          'unify_combo_id': str(combo_info_vo['id']), 'order_type': 2,
                          'store_meal_name': combo_info_vo['combo_name']
                          }
            Order_Model.objects.create(**order_dict)
            LOGGER.info(f'{serial_number}订购流量套餐包,createdBy：{user}')
            UnicomComboView.create_combo_order_info(order_id, 0, u_device_info.iccid, package_id)
            if u_device_info.status != 2:
                UnicomDeviceInfo.objects.filter(iccid=u_device_info.iccid).update(status=2, updated_time=now_time)
            return response.json(0)
        except Exception as e:
            LOGGER.info('UnicomManageControllerView.order_flow_package,errLine:{}, errMsg:{}'
                        .format(e.__traceback__.tb_lineno, repr(e)))
            return response.json(500)

    @classmethod
    def reset_card_package(cls, request, request_dict, response, user):
        try:
            serial_number = request_dict.get('serialNumber', None)
            if not serial_number:
                return response.json(444)
            device_info_qs = UnicomDeviceInfo.objects.filter(serial_no=serial_number)
            now_time = int(time.time())
            if device_info_qs.exists():  # 首先查询SIM卡绑定信息是否存在
                iccid = device_info_qs.first().iccid
                # 根据序列号重置出厂流量套餐
                serial_package_qs = SerialNumberPackage.objects.filter(~Q(status=1), serial_number=serial_number)
                if serial_package_qs.exists():
                    serial_package_qs.update(status=1, updated_time=now_time, updated_by=user)

                if device_info_qs.first().card_type == 1:  # 五兴电信
                    data = {'iccids': iccid, 'operator': WXOperatorEnum.TELECOM.value}
                    wx_tech = WXTechObject()
                    res = wx_tech.delete_card_package(**data)
                    if res['code'] == '0':
                        UnicomComboExperienceHistory.objects.filter(iccid=iccid).delete()
                        return response.json(0)
                    return response.json(176)
                flow_push_qs = UnicomFlowPush.objects.filter(serial_no=serial_number)
                if flow_push_qs.exists():  # 删除流量预警推送
                    flow_push_qs.delete()
                sys_msg_qs = SysMsgModel.objects.filter(uid=serial_number)
                if sys_msg_qs.exists():  # 删除有关系统消息数据
                    sys_msg_qs.delete()
                # 将4G用户信息状态改为已完成测试状态
                device_info_qs.update(status=1, updated_time=now_time, user_id='')
                combo_order_qs = UnicomComboOrderInfo.objects.filter(iccid=iccid)
                if combo_order_qs.exists():
                    combo_order_qs.delete()
                combo_experience_history_qs = UnicomComboExperienceHistory.objects.filter(iccid=iccid)
                if combo_experience_history_qs.exists():
                    combo_experience_history_qs.delete()
                UnicomObjeect().change_device_to_disable(iccid)  # 重置流量停用设备
                ip = CommonService.get_ip_address(request)
                describe = '重置4G流量序列号{}，iccid:{}'.format(serial_number, iccid)
                key = f'ASJ:UNICOM:CARD:ACTIVATE:{serial_number}'
                redis = RedisObject()
                redis.del_data(key)
                cls.create_operation_log('unicom/manage/resetCardPackage', ip, request_dict, describe)
                return response.json(0)
            return response.json(173)
        except Exception as e:
            LOGGER.info('异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)))
            return response.json(500)

    @classmethod
    def generate_card_package_order(cls, iccid, serial_number):
        """
        模拟设备上电赠送1G
        @param iccid:
        @param serial_number:
        @return:
        """
        url = 'https://www.zositechc.cn/' if CONFIG_INFO == 'cn' else 'https://test.zositechc.cn/'
        url = url + 'unicom/api/device-bind'
        now_time = int(time.time())
        sign = CommonService.encode_data(str(now_time))
        data = {
            'iccid': iccid,
            'serialNo': serial_number,
            'timeStamp': now_time,
            'sign': sign,
            'sim': 1
        }
        response = requests.post(url=url, data=data, timeout=5)
        LOGGER.info(f"生成体验套餐结果:{json.loads(response.text)}")

    @classmethod
    def create_operation_log(cls, url, ip, request_dict, describe):
        """
        存入操作日志
        @param url: 请求路径
        @param describe: 描述
        @param ip: 当前IP
        @param request_dict: 请求参数
        @return: True | False
        """
        try:
            # 记录操作日志
            content = json.loads(json.dumps(request_dict))
            log = {
                'ip': ip,
                'user_id': 1,
                'status': 200,
                'time': int(time.time()),
                'content': json.dumps(content),
                'url': url,
                'operation': describe,
            }
            LogModel.objects.create(**log)
            return True
        except Exception as e:
            print('日志异常,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)))
            return False

    def get_user_info(self, request_dict, response):
        """
        获取/筛选卡用户信息
        @param request_dict:
        @param response:
        @return:
        NickName:用户昵称 phone: 电话
        serial_no: 设备序列号
        """
        NickName = request_dict.get('NickName', None)
        phone = request_dict.get('phone', None)
        iccid = request_dict.get('iccid', None)
        serial_no = request_dict.get('serialNo', None)
        status = request_dict.get('status')
        pageSize = request_dict.get('pageSize', None)
        pageNo = request_dict.get('pageNo', None)

        if not all({pageNo, pageSize}):
            return response.json(444)
        page = int(pageNo)
        line = int(pageSize)

        try:
            unicom_device_qs = UnicomDeviceInfo.objects.all().order_by('-updated_time')
            device_user_qs = Device_User.objects.filter().values(
                'userID', 'NickName', 'phone')
            if status:
                unicom_device_qs = unicom_device_qs.filter(status=status)
            if iccid:
                unicom_device_qs = unicom_device_qs.filter(iccid__icontains=iccid)
            if serial_no:
                unicom_device_qs = unicom_device_qs.filter(serial_no__icontains=serial_no)
            if NickName:
                device_user_qs = device_user_qs.filter(NickName__icontains=NickName)
                if not device_user_qs.exists():
                    return response.json(0, [])
                userID = device_user_qs.first()['userID']
                unicom_device_qs = unicom_device_qs.filter(user_id=userID)
            if phone:
                device_user_qs = device_user_qs.filter(phone=phone)
                if not device_user_qs.exists():
                    return response.json(0, [])
                userID = device_user_qs.first()['userID']
                unicom_device_qs = unicom_device_qs.filter(user_id=userID)
            total = unicom_device_qs.count()
            unicom_device_qs = unicom_device_qs[(page - 1) * line:page * line]
            list_data = []
            for unicom_device in unicom_device_qs:
                data = {'iccid': unicom_device.iccid, 'serialNo': unicom_device.serial_no,
                        'userID': unicom_device.user_id, 'cardType': unicom_device.card_type,
                        'status': unicom_device.status, 'mainCard': unicom_device.main_card,
                        'createdTime': unicom_device.created_time, 'updatedTime': unicom_device.updated_time,
                        'cardStatus': self.get_device_status_by_iccid(unicom_device.iccid, unicom_device.card_type)}
                device_user_qs = Device_User.objects.filter(userID=unicom_device.user_id).values('username', 'NickName',
                                                                                                 'phone')
                data['userName'] = device_user_qs[0]['username'] if device_user_qs.exists() else ''
                data['NickName'] = device_user_qs[0]['NickName'] if device_user_qs.exists() else ''
                data['phone'] = device_user_qs[0]['phone'] if device_user_qs.exists() else ''
                list_data.append(data)
            return response.json(0, {'list': list_data, 'total': total})
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @staticmethod
    def query_sql_4g():
        """
        4G关联查询SQL
        @return: str
        """
        sql = 'SELECT '
        sql += 'du.username,du.phone,o.UID as uid,o.`status`,udi.serial_no as serialNo,o.orderID,o.`desc`, '
        sql += 'o.price,uo.next_month_activate as nextActivate,uo.iccid,uo.`status` as useStatus,uo.`flow_total_usage`,'
        sql += 'uo.updated_time as upTime, uo.activation_time as acTime,uo.expire_time as epTime '
        sql += 'FROM orders o '
        sql += 'LEFT JOIN unicom_combo_order_info uo ON o.orderID = uo.order_id '
        sql += 'INNER JOIN device_user du ON du.userID = o.userID_id '
        sql += 'INNER JOIN unicom_device_info udi ON udi.iccid = uo.iccid '
        return sql

    @staticmethod
    def query_4G_user_order(request_dict, response):
        """
        查询4G用户订单
        """
        try:
            page = int(request_dict.get('pageNo', 1))
            size = int(request_dict.get('pageSize', 10))
            user_name = request_dict.get('userName', None)
            uid = request_dict.get('uid', None)
            serial_no = request_dict.get('serialNo', None)
            combo_use_type = request_dict.get('comboUseType', None)
            cursor = connection.cursor()
            sql = UnicomManageControllerView.query_sql_4g()
            sql += 'WHERE o.order_type = %s '
            param_list = [2]
            if user_name:
                sql += "and du.username LIKE %s "
                param_list.append(user_name)
            if uid:
                sql += "and o.UID LIKE %s "
                param_list.append(uid)
            if serial_no:
                sql += "and udi.serial_no LIKE %s "
                param_list.append(serial_no)
            if combo_use_type:
                sql += 'and uo.status = %s '
                param_list.append(int(combo_use_type))
            cursor.execute(sql, param_list)
            total = len(cursor.fetchall())
            param_list.append((page - 1) * size)
            param_list.append(size, )
            sql += 'order by o.addTime DESC LIMIT %s,%s '
            cursor.execute(sql, param_list)
            data_obj = cursor.fetchall()
            cursor.close()  # 执行完，关闭
            connection.close()
            result_list = []
            col_names = [desc[0] for desc in cursor.description]
            for item in data_obj:
                order_dict = dict(zip(col_names, item))
                order_dict['using_total'] = 0
                result_list.append(order_dict)
            return response.json(0, {'orderList': result_list, 'total': total})
        except Exception as e:
            meg = '异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e))
            return response.json(500, meg)

    @staticmethod
    def check_sim_user(iccid):
        """
        检查SIM卡用户
        @param iccid:物联卡
        @return:
        """
        u_device_qs = UnicomDeviceInfo.objects.filter(iccid=iccid).values('user_id')
        if not u_device_qs.exists() or not u_device_qs[0]['user_id']:
            return False
        return True

    @classmethod
    def edit_combo(cls, request_dict, response):
        """
        添加和编辑卡套餐
        @param request_dict:
        @param response:
        @return:
        """
        combo_id = request_dict.get('id', None)
        combo_name = request_dict.get('comboName', None)
        status = request_dict.get('status', None)
        combo_type = request_dict.get('comboType', None)
        flow_total = request_dict.get('flowTotal', None)
        expiration_days = request_dict.get('expirationDays', None)
        expiration_type = request_dict.get('expirationType', None)
        pay_type = request_dict.get(
            'payTypes', '')[
                   1:-1].split(',')  # '[1,2]' -> ['1','2']
        sort = request_dict.get('sort', None)
        price = request_dict.get('price', None)
        remark = request_dict.get('remark', None)
        is_show = request_dict.get('isShow', None)
        virtualPrice = request_dict.get('virtualPrice', None)

        if not all([pay_type, price, is_show, status, combo_type, flow_total, expiration_days, expiration_type]):
            return response.json(444)
        flow_total = int(flow_total)
        expiration_days = int(expiration_days)
        expiration_type = int(expiration_type)
        status = int(status)
        combo_type = int(combo_type)
        is_show = int(is_show)
        sort = int(sort)
        nowTime = int(time.time())

        # 判断是编辑还是添加
        with transaction.atomic():
            try:
                re_data = {
                    'combo_name': combo_name,
                    'status': status,
                    'combo_type': combo_type,
                    'flow_total': flow_total,
                    'expiration_days': expiration_days,
                    'expiration_type': expiration_type,
                    'price': price,
                    'sort': sort,
                    'remark': remark if remark else '',
                    'is_show': is_show,
                    'virtual_price': virtualPrice,
                }
                if combo_id:
                    combo_type_qs = UnicomCombo.objects.filter(id=combo_id)
                    if not combo_type_qs.exists():
                        return response.json(173)
                    re_data['updated_time'] = nowTime
                    combo_type_qs.filter(id=combo_id).update(**re_data)
                    combo_type_qs.get(id=combo_id).pay_type.set(pay_type)
                else:
                    re_data['updated_time'] = int(time.time())
                    re_data['created_time'] = int(time.time())
                    UnicomCombo.objects.create(**re_data).pay_type.set(pay_type)
                return response.json(0)
            except Exception as e:
                return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @staticmethod
    def get_unicom_info(request_dict, response):
        """
        获取套餐详细表
        @param request_dict:
        @param response:
        @return:
        """
        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)
        if not all([pageNo, pageSize]):
            return response.json(444)
        elif pageNo and pageSize:
            pass
        page = int(pageNo)
        line = int(pageSize)
        try:
            combo_qs = UnicomCombo.objects.filter(is_del=False) \
                .values('id', 'status', 'combo_name',
                        'flow_total', 'combo_type',
                        'expiration_days',
                        'expiration_type', 'price', 'is_unlimited',
                        'updated_time', 'created_time',
                        'remark', 'is_show', 'sort', 'virtual_price').order_by('sort')
            if not combo_qs.exists():
                return response.json(0, [])
            total = combo_qs.count()
            combo_qs = combo_qs[(page - 1) * line:page * line]
            combo_list = []
            for item in combo_qs:
                # 获取支付方式列表
                pay_type_list = [pay_type['id'] for pay_type in
                                 UnicomCombo.objects.get(id=item['id']).pay_type.values('id')]
                combo_list.append({
                    'id': item['id'],
                    'status': item['status'],
                    'comboType': item['combo_type'],
                    'comboName': item['combo_name'],
                    'flowTotal': item['flow_total'],
                    'expirationDays': item['expiration_days'],
                    'expirationType': item['expiration_type'],
                    'price': item['price'],
                    'sort': item['sort'],
                    'isUnlimited': item['is_unlimited'],
                    'updatedTime': item['updated_time'],
                    'createdTime': item['created_time'],
                    'remark': item['remark'],
                    'isShow': item['is_show'],
                    'payTypes': pay_type_list,
                    'virtualPrice': item['virtual_price']
                })
            return response.json(0, {'list': combo_list, 'total': total})
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @classmethod
    def get_pay_type(cls, response):
        """
        获取支付类型
        @param response:
        @return:
        """
        pay_type_qs = Pay_Type.objects.all().values('id', 'payment')
        if not pay_type_qs.exists():
            return response.json(0, [])
        pay_type_list = []
        for pay_type in pay_type_qs:
            pay_type_list.append(pay_type)
        return response.json(0, pay_type_list)

    @classmethod
    def get_unicom_combo_type(cls, response):
        """
        获取赠送套餐
        @param response:
        @return:
        """
        unicom_combo_qs = UnicomCombo.objects.filter(combo_type=2, status=0).values('id', 'combo_name')
        if not unicom_combo_qs.exists():
            return response.json(0, [])
        combo_list = []
        for combo in unicom_combo_qs:
            combo_list.append(combo)
        return response.json(0, combo_list)

    @classmethod
    def combo_order_info(cls, request_dict, response):
        """
        删除卡套餐信息（修改状态）
        @param request_dict
        @param response
        @return:
        """
        combo_id = request_dict.get('id', None)

        if not combo_id:
            return response.json(444)
        combo_qs = UnicomCombo.objects.filter(id=combo_id)
        #  只修改默认状态
        if combo_qs.exists():
            combo_qs.update(is_del=True)
        return response.json(0)

    def static_info(self, request_dict, response):
        """
        统计联通套餐
        @param request_dict:请求参数
        @param response: 响应对象
        @param return:
        """
        year = request_dict.get('year', None)
        Jan = int(time.mktime(time.strptime(year + '-1-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Feb = int(time.mktime(time.strptime(year + '-2-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Mar = int(time.mktime(time.strptime(year + '-3-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Apr = int(time.mktime(time.strptime(year + '-4-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        May = int(time.mktime(time.strptime(year + '-5-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Jun = int(time.mktime(time.strptime(year + '-6-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Jul = int(time.mktime(time.strptime(year + '-7-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Aug = int(time.mktime(time.strptime(year + '-8-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Sep = int(time.mktime(time.strptime(year + '-9-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Oct = int(time.mktime(time.strptime(year + '-10-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Nov = int(time.mktime(time.strptime(year + '-11-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Dec = int(time.mktime(time.strptime(year + '-12-1 00:00:00', "%Y-%m-%d %H:%M:%S")))
        Jan_next = int(time.mktime(time.strptime(str(int(year) + 1) + '-1-1 00:00:00', "%Y-%m-%d %H:%M:%S")))

        list_data = []
        unicom_combo_qs = UnicomCombo.objects.filter().values('id', 'combo_type', 'combo_name')
        if not unicom_combo_qs.exists():
            return response.json(173)
        try:
            for unicom_combo in unicom_combo_qs:
                name = unicom_combo['combo_name']
                combo_order = UnicomComboOrderInfo.objects.filter(combo_id=unicom_combo['id'])
                if not combo_order.exists():
                    continue
                Jan_count = combo_order.filter(created_time__range=[Jan, Feb]).count()
                Feb_count = combo_order.filter(created_time__range=[Feb, Mar]).count()
                Mar_count = combo_order.filter(created_time__range=[Mar, Apr]).count()
                Apr_count = combo_order.filter(created_time__range=[Apr, May]).count()
                May_count = combo_order.filter(created_time__range=[May, Jun]).count()
                Jun_count = combo_order.filter(created_time__range=[Jun, Jul]).count()
                Jul_count = combo_order.filter(created_time__range=[Jul, Aug]).count()
                Aug_count = combo_order.filter(created_time__range=[Aug, Sep]).count()
                Sep_count = combo_order.filter(created_time__range=[Sep, Oct]).count()
                Oct_count = combo_order.filter(created_time__range=[Oct, Nov]).count()
                Nov_count = combo_order.filter(created_time__range=[Nov, Dec]).count()
                Dec_count = combo_order.filter(created_time__range=[Dec, Jan_next]).count()
                data = [Jan_count, Feb_count, Mar_count, Apr_count, May_count, Jun_count, Jul_count, Aug_count,
                        Sep_count,
                        Oct_count, Nov_count, Dec_count]

                cloud_data = {
                    'name': name,
                    'type': 'line',
                    'data': data,
                }
                list_data.append(cloud_data)

            return response.json(0, {'list': list_data})
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @classmethod
    def get_flow_packages(cls, request_dict, response):
        """
        赠送套餐流量
        @param request_dict:请求参数
        @username request_dict:用户名
        @comboType request_dict：套餐类型
        @serialNo request_dict：序列号
        @param response: 响应对象
        @return:
        """
        userId = request_dict.get('userId', None)
        serialNo = request_dict.get('serialNo', None)
        comboId = request_dict.get('comboId', None)
        if not all([userId, serialNo, comboId]):
            return response.json(444)
        try:
            while transaction.atomic():
                combo_info_qs = UnicomCombo.objects.filter(id=comboId, combo_type=2, status=0) \
                    .values('id', 'combo_name', 'price', 'virtual_price', 'remark', 'combo_type')
                unicom_device_info_qs = UnicomDeviceInfo.objects.filter(serial_no=serialNo,
                                                                        user_id=userId).values \
                    ('iccid')
                if not unicom_device_info_qs.exists() or not combo_info_qs.exists():
                    return response.json(173)
                combo_info_qs = combo_info_qs.first()
                unicom_device_info_qs = unicom_device_info_qs.first()
                n_time = int(time.time())
                order_id = CommonService.createOrderID()  # 生成订单号
                #  赠送套餐下个月生效
                unicom_combo = UnicomComboView.create_combo_order_info(order_id=order_id, activate_type=1,
                                                                       iccid=unicom_device_info_qs['iccid'],
                                                                       combo_id=comboId)
                if unicom_combo is False:
                    return response.json(178)
                rank_id, ai_rank_id = UnicomComboView.get_cloud_or_ai_combo()  # 生成订单必须添加该字段
                uid = CommonService.query_uid_with_serial(serialNo)  # 获取序列号或UID
                # 获取套餐信息
                order_dict = {
                    'orderID': order_id,
                    'UID': uid,
                    'rank_id': rank_id,
                    'ai_rank_id': ai_rank_id,
                    'userID_id': userId,
                    'desc': combo_info_qs['combo_name'],
                    'payType': 10,
                    'payTime': n_time,
                    'price': combo_info_qs['price'],
                    'addTime': n_time,
                    'updTime': n_time,
                    'status': 1,
                    'unify_combo_id': str(combo_info_qs['id']),
                    'order_type': 2,
                    'store_meal_name': combo_info_qs['combo_name']
                }
                Order_Model.objects.create(**order_dict)
                return response.json(0)
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @classmethod
    def get_iccid_info(cls, request_dict, response):
        """
        获取联通iccid最新状态
        """
        try:
            iccid = request_dict.get('iccid', None)
            if not iccid:
                return response.json(444)
            re_data = {'iccid': iccid}
            result = UnicomObjeect().query_device_status(**re_data)
            res_dict = UnicomObjeect().get_text_dict(result)
            # 状态不等于1（激活）时进行激活 1:激活；2:停用
            return response.json(0, res_dict['data']['status'])
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @classmethod
    def package_cdk_export_excel(cls, response):
        """
        流量包兑换码导出excel
        """
        try:
            # 创建一个新的excel文档
            wb = openpyxl.Workbook()
            # 获取默认的工作表
            sheet = wb.active
            sheet.title = '周视国内流量年卡兑换码'
            sheet.column_dimensions['A'].width = 15
            sheet.column_dimensions['B'].width = 20
            exchange_code = ExchangeCode.objects.filter(status=False, is_down=False)
            if not exchange_code.exists():
                return response.json(173)
            # 将兑换码写入到excel表
            for i, vo in enumerate(list(exchange_code)):
                code_no = cls.fix_string_length(str(vo.id))
                sheet.cell(row=i + 1, column=1, value=code_no)
                sheet.cell(row=i + 1, column=2, value=vo.code)
            filename = '国内流量年卡兑换码-{}.xlsx'.format(exchange_code.count())
            # 创建一个http响应
            res = HttpResponse(content_type='application/vnd.ms-excel')
            # 设置响应头，告诉浏览器文件要下载而不是直接打开
            res['Content-Disposition'] = 'attachment; filename={}'.format(filename)
            # 将excel文档保存到http响应中
            wb.save(res)
            exchange_code.update(is_down=True, updated_time=int(time.time()))
            return res
        except Exception as e:
            LOGGER.info('*****UnicomManageController.package_cdk_export_excel:errLine:{}, errMsg:{}'
                        .format(e.__traceback__.tb_lineno, repr(e)))
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @staticmethod
    def fix_string_length(code_no):
        """
        将兑换码编号生成固定6位长度
        """
        if len(code_no) < 6:
            return 'NO.' + code_no.rjust(6, '0')
        else:
            return 'NO.' + code_no

    @classmethod
    def create_package_cdk(cls, request_dict, response):
        """
        批量生成兑换码
        """
        try:
            LOGGER.info('*****UnicomManageController.create_package_cdk,params:{}'.format(request_dict))
            quantity = request_dict.get('quantity', None)
            package_id = request_dict.get('packageId', None)
            if not all([quantity, package_id]):
                return response.json(444)
            combo_qs = UnicomCombo.objects.filter(id=int(package_id))
            if not combo_qs.exists():
                return response.json(173)
            combo = combo_qs.first()
            exchange_code_list = []
            now_time = int(time.time())
            if combo.combo_type == 3:  # 五兴电信
                for i in range(int(quantity)):
                    # 10位兑换码 后面两位为标识代表五兴电信
                    code = cls.generate_code() + 'WD'
                    exchange_code_list.append(ExchangeCode(code=code, status=False, is_down=0,
                                                           package_type=1, package_id=combo.id,
                                                           expire_time=0,
                                                           created_time=now_time,
                                                           updated_time=now_time))
            elif combo.combo_type == 0:  # 珠海联通
                for i in range(int(quantity)):
                    # 10位兑换码 后面两位为标识代表五兴电信
                    code = cls.generate_code() + 'ZL'
                    exchange_code_list.append(ExchangeCode(code=code, status=False, is_down=0,
                                                           package_type=0, package_id=combo.id,
                                                           expire_time=0,
                                                           created_time=now_time,
                                                           updated_time=now_time))
            if exchange_code_list:
                ExchangeCode.objects.bulk_create(exchange_code_list)
                return response.json(0)
            return response.json(178)
        except Exception as e:
            LOGGER.info('*****UnicomManageController.create_package_cdk:errLine:{}, errMsg:{}'
                        .format(e.__traceback__.tb_lineno, repr(e)))
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @classmethod
    def generate_code(cls):
        # 生成uuid并移除-
        uuid_str = str(uuid.uuid4()).replace('-', '')
        now_time = int(time.time())
        uuid_str += str(now_time)
        # 使用SHA1算法生成哈希值
        sha1 = hashlib.sha1(uuid_str.encode('utf-8'))
        # 取哈希值的前8位，并将其转换为大写字母
        code = sha1.hexdigest()[:8].upper()
        return code

    @classmethod
    def get_package_details(cls, request_dict, response):
        """
        根据序列号获取套餐包列表
        """
        serial_number = request_dict.get('serialNumber', None)
        if not serial_number:
            return response.json(444)
        ud_qs = UnicomDeviceInfo.objects.filter(serial_no=serial_number) \
            .values('iccid', 'card_type')
        package_list = []
        if not ud_qs.exists():
            return response.json(0, {'packageList': package_list})
        iccid = ud_qs[0]['iccid']
        card_type = ud_qs[0]['card_type']
        if card_type == 0:
            o_qs = UnicomComboOrderInfo.objects.filter(iccid=iccid) \
                .values('status', 'flow_total_usage', 'flow_exceed', 'activation_time', 'expire_time',
                        'combo__combo_name', 'combo__flow_total', 'updated_time') \
                .order_by('created_time')
            if not o_qs:
                return response.json(0, {'packageList': package_list})
            return response.json(0, {'package_list': cls.get_unicom_package_list(iccid, o_qs)})
        if card_type == 1:
            data = {'iccid': iccid, 'operator': 3}
            return response.json(0, {'package_list': cls.get_wx_package_list(**data)})
        return response.json(0, {'package_list': package_list})

    @staticmethod
    def get_unicom_package_list(iccid, o_qs):
        package_list = []
        unicom_api = UnicomObjeect()
        for i, item in enumerate(o_qs):
            package_status = item['status']
            flow_total = item['combo__flow_total']
            flow_total_usage = float(unicom_api.get_flow_usage_total(iccid))
            activate_flow = float(item['flow_total_usage']) if item['flow_total_usage'] else 0
            used = 0
            if package_status == 1:
                used = flow_total_usage - activate_flow  # 已用流量
            elif package_status == 2:
                index = i + 1
                if index < len(o_qs) and o_qs[index]['flow_total_usage']:
                    package_used_flow = float(o_qs[i + 1]['flow_total_usage'])
                    used = package_used_flow - activate_flow
                else:
                    used = flow_total_usage - activate_flow
            status_dict = {0: "待使用", 1: "使用中", 2: "已失效"}
            status = status_dict.get(item['status'])
            package_list.append({
                'packageName': item['combo__combo_name'],
                'status': status,
                'flowTotal': flow_total,
                'used': Decimal(used).quantize(Decimal('0.00')),
                'activationTime': datetime.datetime.fromtimestamp(item['activation_time']).strftime(
                    '%Y-%m-%d %H:%M:%S'),
                'expireTime': datetime.datetime.fromtimestamp(item['expire_time']).strftime('%Y-%m-%d %H:%M:%S'),
                'updatedTime': datetime.datetime.fromtimestamp(item['updated_time']).strftime('%Y-%m-%d %H:%M:%S')
            })
        return package_list

    @staticmethod
    def get_wx_package_list(**data):
        """
        获取五兴套餐订购记录
        @param data: iccid，operator
        @return: 订购记录结果
        """
        try:
            package_list = []
            wx_tech = WXTechObject()
            result = wx_tech.get_package_order_record(**data)
            if not result:
                return package_list
            status_dict = {0: "待使用", 1: "使用中", 2: "已完成", 3: "已退订", 4: "已失效", 5: "已删除"}
            for item in result['data']:
                used_flow = float(item['flowTotal']) - float(item['flowRemain'])
                package_list.append({
                    'packageName': item['packageName'],
                    'status': status_dict.get(int(item['state'])),
                    'flowTotal': item['flowTotal'],
                    'used': Decimal(used_flow).quantize(Decimal('0.00')),
                    'activationTime': item['startDate'],
                    'expireTime': item['endDate'],
                    'updatedTime': ''
                })
            return package_list
        except Exception as e:
            print(repr(e))
            return []

    @staticmethod
    def get_device_status_by_iccid(iccid, card_type):
        try:
            re_data = {'iccid': iccid}
            if card_type == 0:
                status_dict = {1: '已激活', 2: '可激活', 3: '已停用', 4: '已失效', 5: '可测试', 6: '库存', 7: '已更换', 8: '已清除'}
                result = UnicomObjeect().query_device_status(**re_data)
                res_dict = UnicomObjeect().get_text_dict(result)
                return status_dict.get(int(res_dict['data']['status']), 'N/A')
            elif card_type == 1:
                status_dict = {1: '库存', 2: '可激活', 3: '已激活', 4: '已停用', 5: '已失效', 6: '强制停机'}
                data = {'iccid': iccid, 'operator': 3}
                wx_tech = WXTechObject()
                result = wx_tech.get_cards_info(**data)
                return status_dict.get(int(result['data']['cardStatusCode']), 'N/A')
            else:
                return 'N/A'
        except Exception as e:
            print(repr(e))
            return 'N/A'

    @classmethod
    def async_update_status(cls, request_dict, response):
        """
        联通根据序列号异步修改卡状态
        @return: 修改结果
        """
        serial_number = request_dict.get('serialNumber', None)
        if not serial_number:
            return response.json(444)
        status = int(request_dict.get('status', 0))
        unicom_qs = UnicomDeviceInfo.objects.filter(serial_no=serial_number, card_type=0)
        if not unicom_qs.exists():
            return response.json(173)
        unicom_service = UnicomObjeect()
        iccid = unicom_qs.first().iccid
        if status == 1:  # 激活
            unicom_service.change_device_to_activate(iccid)
            return response.json(0)
        elif status == 3:  # 停用
            unicom_service.change_device_to_disable(iccid)
            return response.json(0)
        return response.json(902)

    @classmethod
    def get_exchange_code_page(cls, request_dict, response):
        """
        分页获取兑换码
        @return: 分页数据
        """
        pageSize = request_dict.get('pageSize', None)
        pageNo = request_dict.get('pageNo', None)
        code = request_dict.get('code', None)
        is_down = request_dict.get('isDown', None)
        if not all({pageNo, pageSize}):
            return response.json(444)
        page = int(pageNo)
        line = int(pageSize)
        try:
            code_qs = ExchangeCode.objects.all()
            if code:
                code_qs = code_qs.filter(code__icontains=code)
            if is_down:
                code_qs = code_qs.filter(is_down=int(is_down))
            code_list = []
            total = code_qs.count()
            result = {'total': total, 'data': code_list}
            code_qs = code_qs.order_by('-created_time')[(page - 1) * line:page * line]
            if not code_qs.exists():
                return response.json(0, result)
            for item in code_qs:
                combo_name = UnicomCombo.objects.filter(id=item.package_id)[0].combo_name
                code_list.append({
                    'id': item.id,
                    'code': item.code,
                    'status': item.status,
                    'isDown': item.is_down,
                    'packageType': item.package_type,
                    'comboName': combo_name,
                    'expireTime': item.expire_time,
                    'createdTime': item.created_time,
                    'updatedTime': item.updated_time
                })
            result['data'] = code_list
            return response.json(0, result)
        except Exception as e:
            LOGGER.info('异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)))
            return response.json(500)

    @classmethod
    def update_exchange_code(cls, request_dict, response):
        """
        修改兑换码数据
        """
        code_id = request_dict.get('id', None)
        is_down = request_dict.get('isDown', None)
        status = request_dict.get('status', None)
        expire_time = request_dict.get('expireTime', None)
        if not code_id:
            return response.json(444)
        try:
            code_qs = ExchangeCode.objects.filter(id=int(code_id))
            if not code_qs.exists():
                return response.json(173)
            params = {}
            if is_down:
                params['is_down'] = int(is_down)
            if status:
                params['status'] = int(status)
            if expire_time:
                params['expire_time'] = int(expire_time)
            if not params:
                return response.json(0)
            code_qs.update(**params)
            return response.json(0)
        except Exception as e:
            LOGGER.info('异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)))
            return response.json(500)

    @classmethod
    def get_flow_combo_list(cls, request_dict, response):
        """
        获取4G流量套餐列表
        @return: 套餐列表
        """
        combo_qs = UnicomCombo.objects.filter(is_del=False, combo_type__in=[0, 3])
        if not combo_qs.exists():
            return response.json(173)
        combo_qs = combo_qs.values('id', 'combo_name').order_by('-created_time')
        flow_combo_list = list(combo_qs)
        return response.json(0, flow_combo_list)

    @classmethod
    def upload_file(cls, user, request, request_dict, response):
        """
        上传序列号文件绑定套餐id
        @param user: 当前操作用户
        @param request: file txt
        @param request_dict: package_id 4G套餐id
        @param response: 响应对象
        @return: 成功数以及异常序列号列表
        """
        package_id = request_dict.get('packageId', None)
        file = request.FILES['file']
        serial_list = []
        error_list = []
        if not package_id:
            return response.json(444)
        n_time = int(time.time())
        package_id = int(package_id)
        sn_list = []
        for line in file:
            serial_number = line.decode().strip()[0:9]
            try:
                sn_qs = SerialNumberPackage.objects.filter(serial_number=serial_number)
                if sn_qs:
                    error_list.append({'serialNumber': serial_number, 'msg': '此序列号已绑定套餐'})
                    continue
                if serial_number in serial_list:
                    error_list.append({'serialNumber': serial_number, 'msg': 'txt存在重复序列号'})
                    continue
                data = {'status': 1, 'serial_number': serial_number, 'package_id': package_id,
                        'created_time': n_time, 'updated_time': n_time, 'created_by': user, 'updated_by': user}
                serial_number_p = SerialNumberPackage(**data)
                serial_list.append(serial_number)
                sn_list.append(serial_number_p)
            except Exception as e:
                error_list.append({'serialNumber': serial_number, 'msg': repr(e)})
        if sn_list:
            SerialNumberPackage.objects.bulk_create(sn_list)
        return response.json(0, {'total': len(serial_list), 'errData': error_list})

    @classmethod
    def iccid_batch_reset(cls, user, request, request_dict, response):
        """
        iccid批量重置流量
        """
        file = request.FILES['file']
        if not file:
            return response.json(444)
        err_data = []
        for line in file:
            serial_number = line.decode().strip()[0:9]
            try:
                device_info_qs = UnicomDeviceInfo.objects.filter(serial_no=serial_number)
                now_time = int(time.time())
                if not device_info_qs.exists():
                    err_data.append({'serialNumber': serial_number, 'msg': '数据不存在'})
                    continue

                iccid = device_info_qs.first().iccid
                # 根据序列号重置出厂流量套餐
                serial_package_qs = SerialNumberPackage.objects.filter(~Q(status=1), serial_number=serial_number)
                if serial_package_qs.exists():
                    serial_package_qs.update(status=1, updated_time=now_time, updated_by=user)

                if device_info_qs.first().card_type == 1:  # 五兴电信
                    data = {'iccids': iccid, 'operator': WXOperatorEnum.TELECOM.value}
                    wx_tech = WXTechObject()
                    res = wx_tech.delete_card_package(**data)
                    if res['code'] == '0':
                        UnicomComboExperienceHistory.objects.filter(iccid=iccid).delete()
                        continue
                    err_data.append({'serialNumber': serial_number, 'msg': '电信重置流量异常:{}'.format(res['code'])})
                    continue
                # 联通
                flow_push_qs = UnicomFlowPush.objects.filter(serial_no=serial_number)
                if flow_push_qs.exists():  # 删除流量预警推送
                    flow_push_qs.delete()
                sys_msg_qs = SysMsgModel.objects.filter(uid=serial_number)
                if sys_msg_qs.exists():  # 删除有关系统消息数据
                    sys_msg_qs.delete()
                # 将4G用户信息状态改为已完成测试状态
                device_info_qs.update(status=1, updated_time=now_time, user_id='')

                combo_order_qs = UnicomComboOrderInfo.objects.filter(iccid=iccid)
                if combo_order_qs.exists():
                    combo_order_qs.delete()

                combo_experience_history_qs = UnicomComboExperienceHistory.objects.filter(iccid=iccid)
                if combo_experience_history_qs.exists():
                    combo_experience_history_qs.delete()
                UnicomObjeect().change_device_to_disable(iccid)  # 重置流量停用设备
                # 记录日志，清除缓存
                ip = CommonService.get_ip_address(request)
                describe = '重置4G流量序列号{}，iccid:{}'.format(serial_number, iccid)
                key = f'ASJ:UNICOM:CARD:ACTIVATE:{serial_number}'
                redis = RedisObject()
                redis.del_data(key)
                cls.create_operation_log('unicom/manage/iccidBatchReset', ip, request_dict, describe)
                continue
            except Exception as e:
                err_data.append({'serialNumber': serial_number, 'msg': '重置流量异常:{}'.format(repr(e))})
        return response.json(0, err_data)

    @classmethod
    def serial_number_package_page(cls, request_dict, response):
        """
        分页查询序列号无限流量套餐
        """
        serial_number = request_dict.get('serialNumber', None)
        pageSize = request_dict.get('pageSize', None)
        pageNo = request_dict.get('pageNo', None)
        package_id = request_dict.get('packageId', None)

        if not all({pageNo, pageSize}):
            return response.json(444)
        page = int(pageNo)
        line = int(pageSize)
        serial_package_qs = SerialNumberPackage.objects.all()
        if serial_number:
            serial_package_qs = serial_package_qs.filter(serial_number=serial_number)
        if package_id:
            serial_package_qs = serial_package_qs.filter(package_id=int(package_id))
        serial_package_qs = serial_package_qs.annotate(serialNumber=F('serial_number'), packageId=F('package_id'),
                                                       createdTime=F('created_time'), createdBy=F('created_by'),
                                                       updatedTime=F('updated_time'), updatedBy=F('updated_by')) \
            .values('id', 'serialNumber', 'status', 'packageId', 'createdTime', 'createdBy', 'updatedTime', 'updatedBy')
        serial_package_qs = serial_package_qs.order_by('-created_time')[(page - 1) * line:page * line]
        if not serial_package_qs.exists():
            return response.json(0, [])
        serial_list = []
        for item in serial_package_qs:
            data = item
            data['packageName'] = UnicomCombo.objects.filter(id=item['packageId']).values('combo_name')[0]['combo_name']
            serial_list.append(data)
        return response.json(0, serial_list)

    @classmethod
    def serial_number_package_update(cls, request_dict, response, user):
        """
        修改序列号绑定无限流量套餐
        """
        try:
            s_id = request_dict.get('id', None)
            package_id = request_dict.get('packageId', None)
            status = request_dict.get('status', None)
            if not s_id:
                return response.json(444)
            if not package_id and not status:
                return response.json(444)
            s_package_qs = SerialNumberPackage.objects.filter(id=int(s_id))
            if not s_package_qs.exists():
                return response.json(173)
            n_time = int(time.time())
            data = {'updated_time': n_time, 'updated_by': user}
            if package_id:
                data['package_id'] = int(package_id)
            if status:
                data['status'] = int(status)
            s_package_qs.update(**data)
            return response.json(0)
        except Exception as e:
            LOGGER.info('UnicomManageControllerView.serial_number_package_update,'
                        'errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)))
            return response.json(500)