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 random
import threading
import time
import uuid
from decimal import Decimal
from math import ceil

import openpyxl
import requests
from django.db import transaction, connection
from django.db.models import Q, F, Count
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, \
    AccessNumberTaskQueue
from Object.EIoTClubObject import EIoTClubObject
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
from Service.TelecomService import TelecomService

UNICOM_MANAGE_LOCK = 'BASIC:UNICOM:MANAGE:OPERATION:LOCK'


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.serial_number_bind_package_bulk(userID, tko.user, request, request_dict, response)
            elif operation == 'iccidBatchReset':  # 批量重置流量
                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)
            elif operation == 'transferDevicePackage':  # 转移设备套餐
                return self.transfer_device_package(request_dict, response, tko.user)
            elif operation == 'updateExpirationDate':  # 修改套餐过期时间
                return self.update_expiration_date(request_dict, response)
            elif operation == 'batchOrderFlowPackage':  # 批量订购流量套餐包
                return self.batch_order_flow_package(request, request_dict, response, userID)
            elif operation == 'updateFlowComboById':  # 根据ID更新流量套餐
                return self.update_flow_combo_by_id(request_dict, response)
            elif operation == 'verifyPackageExport':
                return self.verify_package_export_excel(request, request_dict, response)
            elif operation == 'batchSerialNumberCombo':  # 批量查询序列号绑定iccid情况
                return self.batch_serial_number_combo(request, response)
            else:
                return response.json(404)

    @classmethod
    def order_flow_package(cls, request_dict, response, user_id):
        """
        订购流量套餐包
        """
        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)
            serial_number = serial_number[0:9]
            result = UnicomComboView().generate_flow_package(serial_number, int(package_id), user_id)
            LOGGER.info(f'{serial_number}订购流量套餐包,createdBy:{user_id},result:{result}')
            if not result:
                response.json(503)
            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):
        """
        iccid单卡重置流量
        @param request: 请求体
        @param request_dict: 请求参数
        @param response: 响应体
        @param user: 用户名称
        @return: 重置结果
        """
        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卡绑定信息是否存在
                if device_info_qs.count() > 1:
                    return response.json(177)
                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:  # 五兴电信API删除所有套餐历史
                    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()
                        LOGGER.info(f"{serial_number}电信重置流量成功:{json.dumps(res)}")
                        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=2, 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()

                # 重置流量后，恢复测试流量
                result = UnicomComboView().activate_test_flow_package(serial_number)
                ip = CommonService.get_ip_address(request)
                describe = '重置4G流量序列号{},iccid:{},{}'.format(serial_number, iccid, result)

                # 当前缓存是客户首次扫序列号，激活赠送测试流量生成的，重置流量所以要清除
                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,
                                                                      unicom_device.access_number)}
                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('-created_time', '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.get_uid_by_serial_number(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 or card_type == 3:
            o_qs = UnicomComboOrderInfo.objects.filter(iccid=iccid) \
                .values('id', '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)})
        if card_type == 5:
            data = {'iccid': iccid, 'timestamp': int(time.time()), 'nonce': random.randint(10000, 99999)}
            original_data = EIoTClubObject.query_order_record_list("v3", **data)
            return response.json(0, {'package_list': cls.get_dx_package_list(original_data)})
        return response.json(0, {'package_list': package_list})

    @classmethod
    def update_flow_combo_by_id(cls, request_dict, response):
        """
        根据ID修改流量套餐
        """
        try:
            flow_combo_id = request_dict.get('id', None)
            expire_time = request_dict.get('expireTime', None)
            if not all([flow_combo_id, expire_time]):
                return response.json(0)
            UnicomComboOrderInfo.objects.filter(id=int(flow_combo_id)).update(expire_time=int(expire_time))
            return response.json(0)
        except Exception as e:
            LOGGER.error('*****更新流量套餐异常:errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)))
            return response(500)

    @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({
                'id': item['id'],
                '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, access_number=''):
        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)
                LOGGER.info(f'查询iccid{iccid}状态结果:{result}')
                return status_dict.get(int(result['data']['cardStatusCode']), 'N/A')

            elif card_type == 3:

                status_dict = {'connect': '已激活', 'disconnect': '已停用'}
                i = 0
                while i < 3:
                    status_code = TelecomService().get_access_number_network_status(access_number)
                    if status_code:
                        return status_dict.get(status_code, 'N/A')
                    i += 1

                access_info = AccessNumberTaskQueue.objects.filter(iccid=iccid, status=1) \
                    .order_by('-completion_time').values('action')
                if not access_info.exists():
                    return 'N/A'
                return '已激活' if access_info[0]['action'] == 2 else '已停用'

            else:
                return 'N/A'
        except Exception as e:
            LOGGER.error('查询ICCID卡状态异常iccid:{},:errLine:{}, errMsg:{}'
                         .format(iccid, e.__traceback__.tb_lineno, 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__in=[0, 3])
        if not unicom_qs.exists():
            return response.json(173)
        unicom_service = UnicomObjeect()
        iccid = unicom_qs.first().iccid
        access_number = unicom_qs.first().access_number
        card_type = unicom_qs.first().card_type
        if status == 1:  # 激活
            unicom_service.change_device_to_activate(iccid=iccid, card_type=card_type, access_number=access_number,
                                                     reason='后台操作恢复网络')
            return response.json(0)
        elif status == 3:  # 停用
            unicom_service.change_device_to_disable(iccid=iccid, card_type=card_type, access_number=access_number,
                                                    reason='后台操作断网')
            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: 套餐列表
        """
        c_type = request_dict.get('type', None)
        combo_qs = UnicomCombo.objects.filter(is_del=False)
        if c_type:
            combo_qs = combo_qs.filter(combo_type=int(c_type))
        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 serial_number_bind_package_bulk(cls, user_id, user, request, request_dict, response):
        """
        上传序列号文件绑定套餐id
        @param user_id: user_id
        @param user: 当前操作用户
        @param request: file txt
        @param request_dict: package_id 4G套餐id
        @param response: 响应对象
        @return: 成功数以及异常序列号列表
        """
        redis_obj = RedisObject(5)
        try:
            lock = redis_obj.try_lock(UNICOM_MANAGE_LOCK, 'iccid', 15, 60)
            if not lock:
                return response.json(10074)

            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 = []

            uc_qs = UnicomCombo.objects.filter(id=package_id)
            if not uc_qs.exists():
                return response.json(173)

            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:
                # 计算总数量和页数，仅计算一次
                total_count = len(sn_list)
                pages = ceil(total_count / 100)

                # 遍历每一页，使用列表切片处理批次
                for page in range(pages):
                    # 计算本次循环实际处理的结束索引，考虑最后一页可能不足100的情况
                    end = min((page + 1) * 100, total_count)
                    # 切片获取当前批次的sn_list
                    batch_sn_list = sn_list[page * 100:end]

                    # 执行批量创建
                    SerialNumberPackage.objects.bulk_create(batch_sn_list)

                # 终身免流量时只保留一个有效套餐并自动激活
                if uc_qs.first().remark == 'LIFETIME_FREE':
                    asy = threading.Thread(target=cls.activate_4G_lifetime_free, args=(sn_list, user_id))
                    asy.start()
            # 释放锁
            redis_obj.release_lock(UNICOM_MANAGE_LOCK, 'iccid')
            return response.json(0, {'total': len(serial_list), 'errData': error_list})
        except Exception as e:
            LOGGER.info('批量绑定异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @classmethod
    def activate_4G_lifetime_free(cls, sn_list, user_id):
        """
        激活4G终身免流量
        """

        for item in sn_list:
            try:
                serial_number = item.serial_number

                # 查询ICCID
                iccid_qs = UnicomDeviceInfo.objects.filter(serial_no=serial_number).values('iccid')
                if not iccid_qs.exists():
                    LOGGER.info(f'*****激活终身免流量iccid不存在serial_number:{serial_number}')
                    continue

                now_time = int(time.time())
                # 写入套餐订购记录
                experience_history_vo = {'iccid': iccid_qs[0]['iccid'], 'experience_type': 1, 'do_time': now_time}
                UnicomComboExperienceHistory.objects.create(**experience_history_vo)

                # 根据ICCID删除套餐记录
                UnicomComboOrderInfo.objects.filter(iccid=iccid_qs[0]['iccid']).delete()

                # 激活终身流量
                UnicomComboView().generate_flow_package(serial_number, item.package_id, user_id)

            except Exception as e:
                LOGGER.info('UnicomManageControllerView.activate_4G_lifetime_free,'
                            'errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)))
                continue

    @classmethod
    def iccid_batch_reset(cls, user, request, request_dict, response):
        """
        iccid批量重置流量
        """
        redis_obj = RedisObject(5)
        lock = redis_obj.try_lock(UNICOM_MANAGE_LOCK, 'iccid', 15, 60)
        if not lock:
            return response.json(10074)
        file = request.FILES['file']
        binding_type = int(request_dict.get('type', 0))
        if not file:
            return response.json(444)
        ip = CommonService.get_ip_address(request)
        asy = threading.Thread(target=UnicomManageControllerView().async_bulk_reset_flow_package,
                               args=(file, user, ip, binding_type))
        asy.start()
        return response.json(0)

    @staticmethod
    def async_bulk_reset_flow_package(file, user, ip, binding_type):
        err_data = []
        date_str = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        num = 0
        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': 'data is null'})
                    continue
                if device_info_qs.count() > 1:
                    err_data.append({'serialNumber': serial_number, 'msg': 'Bind multiple iccid'})
                    continue
                iccid = device_info_qs.first().iccid
                if binding_type == 1:
                    SerialNumberPackage.objects.filter(serial_number=serial_number).delete()
                else:
                    # 根据序列号重置出厂流量套餐
                    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()
                        num += 1
                        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=2, 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()

                result = UnicomComboView().activate_test_flow_package(serial_number)  # 订购新的100M测试流量
                describe = '批量重置4G流量序列号{},iccid:{},{}'.format(serial_number, iccid, result)

                num += 1
                # 当前缓存是客户首次扫序列号，激活赠送测试流量生成的，重置流量所以要清除
                key = f'ASJ:UNICOM:CARD:ACTIVATE:{serial_number}'
                redis = RedisObject()
                redis.del_data(key)
                UnicomManageControllerView().create_operation_log('unicom/manage/iccidBatchReset', ip, binding_type,
                                                                  describe)
                continue
            except Exception as e:
                err_data.append({'serialNumber': serial_number, 'msg': '重置流量异常:{}'.format(repr(e))})
                continue
        if err_data:
            describe = json.loads(json.dumps(err_data))
            UnicomManageControllerView().create_operation_log('unicom/manage/iccidBatchReset', ip, binding_type,
                                                              describe)
        try:
            redis_obj = RedisObject(5)
            lock = redis_obj.release_lock(UNICOM_MANAGE_LOCK, 'iccid')
            describe = f'{date_str}批量重置流量释放锁{lock},数量:{num}'
            UnicomManageControllerView().create_operation_log('unicom/manage/iccidBatchReset', ip, binding_type,
                                                              describe)
        except Exception as e:
            LOGGER.info('释放锁异常,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

        LOGGER.info(f'批量重置流量type={binding_type},err_data={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')
        total = serial_package_qs.count()

        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, {'list': serial_list, 'total': total})

    @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)

    @classmethod
    def transfer_device_package(cls, request_dict, response, user):
        """
        联通4G订单套餐转移
        @param request_dict: orderId(当前订单套餐id)、targetSerialNumber(目标设备序列号)
        @param response: 响应结果
        @param user: 操作用户
        @return: 转移结果
        """
        try:
            order_id = request_dict.get('orderId')
            target_serial_number = request_dict.get('targetSerialNumber')
            LOGGER.info('UnicomManageControllerView.transfer_device_package,serialNumber:{},orderId:{}'.format(
                target_serial_number, order_id))

            if not all([order_id, target_serial_number]):
                return response.json(444)

            # 查询旧设备订单套餐信息
            order_query_set = Order_Model.objects.filter(orderID=order_id)
            if not order_query_set.exists():
                return response.json(173)

            # 查询目标设备信息是否存在
            u_device_query_set = UnicomDeviceInfo.objects.filter(serial_no=target_serial_number)
            if not u_device_query_set.exists():
                return response.json(173)

            order_data = order_query_set.values().first()
            old_order_id = order_data['orderID']
            desc = '{} 转移后,前orderId:{}'.format(order_data['store_meal_name'], old_order_id)
            order_data['desc'] = desc
            now_order = CommonService.createOrderID()
            uid = CommonService.get_uid_by_serial_number(target_serial_number)
            order_data['orderID'] = now_order
            order_data['UID'] = uid
            order_data['currency'] = 'CNY'
            package_id = int(order_data['unify_combo_id'])
            Order_Model.objects.create(**order_data)

            iccid = u_device_query_set.values('iccid').first()['iccid']  # 目标设备4G iccid

            u_package_query_set = UnicomComboOrderInfo.objects.filter(order_id=old_order_id)
            now_time = int(time.time())
            unicom_api = UnicomObjeect()

            if u_package_query_set.exists():  # 套餐记录存在则直接copy
                package_info = u_package_query_set.values().first()
                package_info.pop('id')
                package_info['iccid'] = iccid
                package_info['order_id'] = now_order
                package_info['activation_time'] = now_time
                package_info['updated_time'] = now_time
                package_info['created_time'] = now_time

                now_package_query_set = UnicomComboOrderInfo.objects.filter(iccid=iccid, status=1)
                if now_package_query_set.exists():
                    package_info['status'] = 0
                    package_info['flow_total_usage'] = '0'
                else:
                    package_info['status'] = 1
                    package_info['flow_total_usage'] = unicom_api.get_flow_usage_total(iccid)
                UnicomComboOrderInfo.objects.create(**package_info)
                # 设置旧套餐状态为2
                u_package_query_set.update(status=2, updated_time=int(time.time()))

            else:
                UnicomComboView.create_combo_order_info(now_order, 0, iccid, package_id)
            # 激活新卡
            unicom_api.change_device_to_activate(iccid)

            describe = f'{order_id}套餐转移成功，操作人{user}'
            cls.create_operation_log('unicom/manage/transferDevicePackage', '127.0.0.1', request_dict, describe)
            return response.json(0)
        except Exception as e:
            LOGGER.info('UnicomManageControllerView.transfer_device_package, errLine:{}, errMsg:{}'.format(
                e.__traceback__.tb_lineno, repr(e)))
            return response.json(500)

    def get_dx_package_list(original_data):
        """
        鼎芯数据处理
        """
        package_list = []
        for package in original_data["data"]:
            new_package = {
                "packageName": package.get("packageName", ""),
                "status": package.get("state"),  # Assuming state 2 means 'In Use'
                "flowTotal": package.get("flowTotal", 0),
                "used": package.get("flowTotal", 0) - package.get("flowRemain", 0),
                "activationTime": package.get("startDate", ""),
                "expireTime": package.get("endDate", ""),
                "updatedTime": ""  # 没有这个数据接口
            }
            package_list.append(new_package)
        return package_list

    @classmethod
    def update_expiration_date(cls, request_dict, response):
        """
        更改套餐过期时间
        @param request_dict: 请求参数
        @request_dict id: 套餐id
        @request_dict expireTime: 过期时间
        @request_dict serialNumber: 序列号
        @param response: 响应对象
        @return:
        """
        id = request_dict.get("id", None)
        expire_time = request_dict.get("expireTime", None)
        serial_number = request_dict.get("serialNumber", None)

        if not all([id, serial_number, expire_time]):
            return response.json(444)
        try:
            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})

            card_type = ud_qs[0]['card_type']
            if card_type == 0 or card_type == 3:
                UnicomComboOrderInfo.objects.filter(pk=id).update(expire_time=expire_time)
                return response.json(0)
            else:
                return response.json(177, "这个类型的卡不能更改过期时间")

        except Exception as e:
            LOGGER.info('UnicomManageControllerView.update_expiration_date, errLine:{}, errMsg:{}'.format(
                e.__traceback__.tb_lineno, repr(e)))
            return response.json(500)

    @classmethod
    def verify_package_export_excel(cls, request, request_dict, response):
        """
        验证套餐导出excel
        @param request_dict:
        @param request:
        @param response: 响应对象
        @return:
        """
        try:
            serial_list = []
            file = request.FILES['serialFile']
            opt_type = int(request_dict.get('optType', 1))
            for line in file:
                serial_number = line.decode().strip()[0:9]
                serial_list.append(serial_number)
            total_count = len(serial_list)
            pages = ceil(total_count / 100)

            package_list = []
            # 遍历每一页，使用列表切片处理批次
            for page in range(pages):
                start = page * 100
                end = (page + 1) * 100
                serial_list_batch = serial_list[start:end]
                # 获取设备信息
                pck_qs = SerialNumberPackage.objects.filter(serial_number__in=serial_list_batch) \
                    .values('serial_number', 'package_id')
                if not pck_qs.exists():
                    return response.json(0)
                for item in pck_qs:
                    package_list.append(item)
            if opt_type == 1:
                return response.json(0, {'fileTotal': total_count, 'queryCount': len(package_list)})
            combo_list = list(UnicomCombo.objects.filter(is_del=False).values('id', 'combo_name'))

            # 创建一个新的excel文档
            wb = openpyxl.Workbook()
            # 获取默认的工作表
            sheet = wb.active
            sheet.title = '套餐核对'
            sheet.column_dimensions['A'].width = 20
            sheet.column_dimensions['B'].width = 20
            sheet.column_dimensions['C'].width = 20
            sheet.column_dimensions['D'].width = 20

            for i, item in enumerate(package_list):
                combo_name = next((combo['combo_name'] for combo in combo_list if combo['id'] == item['package_id']),
                                  None)
                u_device_qs = UnicomDeviceInfo.objects.filter(serial_no=item['serial_number']).values('card_type',
                                                                                                      'iccid')
                sheet.cell(row=i + 1, column=1, value=item['serial_number'])
                if u_device_qs.exists():
                    card_name = ''
                    if u_device_qs[0]['card_type'] == 0:
                        card_name = '珠海联通'
                    elif u_device_qs[0]['card_type'] == 1:
                        card_name = '五兴电信'
                    sheet.cell(row=i + 1, column=2, value=card_name)
                    sheet.cell(row=i + 1, column=3, value=u_device_qs[0]['iccid'])
                sheet.cell(row=i + 1, column=4, value=combo_name)

            filename = '序列号流量套餐核对-{}.xlsx'.format(len(package_list))
            # 创建一个http响应
            res = HttpResponse(content_type='application/vnd.ms-excel')
            # 设置响应头，告诉浏览器文件要下载而不是直接打开
            res['Content-Disposition'] = 'attachment; filename={}'.format(filename)
            # 将excel文档保存到http响应中
            wb.save(res)
            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)))

    @classmethod
    def batch_order_flow_package(cls, request, request_dict, response, user_id):
        serial_number_file = request.FILES.get('serialNumberFile', None)
        package_id = request_dict.get('packageId', None)

        if serial_number_file is None or package_id is None:
            return response.json(444)

        try:
            # 读取序列号文件并处理
            serial_numbers = []
            for line in serial_number_file.readlines():
                # 去除空格并截取前9个字符
                cleaned_line = line.decode('utf-8').strip().replace(' ', '')[0:9]
                serial_numbers.append(cleaned_line)

            thread = threading.Thread(target=cls.batch_order_flow,
                                      args=(serial_numbers, package_id, request, request_dict, user_id))
            thread.start()

            return response.json(0)

        except Exception as e:
            LOGGER.info('*****UnicomManageController.batch_order_flow_package: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 batch_order_flow(cls, serial_numbers, package_id, request, request_dict, user_id):
        try:
            failures = []  # 记录失败的序列号
            successes = []  # 记录成功的数量

            # 循环添加
            for serial_number in serial_numbers:
                serial_number = serial_number[:9]
                result = UnicomComboView().generate_flow_package(serial_number, int(package_id), user_id)
                LOGGER.info(f'{serial_number} 订购流量套餐包, createdBy:{user_id}, result:{result}')
                if not result:
                    failures.append(serial_number)
                else:
                    successes.append(serial_number)

            # 写入操作日志
            ip = CommonService.get_ip_address(request)
            content = json.loads(json.dumps(request_dict))
            log = {
                'ip': ip,
                'user_id': 1,
                'status': 200,
                'time': int(time.time()),
                'url': 'unicom/manage/batchOrderFlowPackage',
                'content': json.dumps(content),
                'operation': f'批量充值流量，序列号{failures}充值失败，序列号{successes}流量充值成功',
            }
            LogModel.objects.create(**log)
            return

        except Exception as e:
            LOGGER.info('*****UnicomManageController.batch_order_flow:errLine:{}, errMsg:{}'
                        .format(e.__traceback__.tb_lineno, repr(e)))
            return

    @staticmethod
    def batch_serial_number_combo(request, response):
        file = request.FILES.get('file', None)
        if file is None:
            return response.json(444)

        # 读取文件内容并只保留每行的前9位
        serial_numbers = set()
        if file.name.endswith('.txt'):
            content = file.read().decode('utf-8')
            for line in content.splitlines():
                if line:  # 确保行不为空
                    serial_numbers.add(line[:9])  # 只保留前9位

        print(serial_numbers)
        # 如果没有有效的 serial_no，直接返回
        if not serial_numbers:
            return response.json(0, {
                'single_iccid': [],
                'no_iccid': [],
                'multiple_iccid': []
            })

        # 查询数据库中每个 serial_no 绑定的 iccid 数量
        results = (
            UnicomDeviceInfo.objects
            .filter(serial_no__in=serial_numbers)  # 过滤出有效的 serial_no
            .values('serial_no')  # 只查询 serial_no 字段
            .annotate(iccid_count=Count('iccid'))  # 统计每个 serial_no 的 iccid 数量
        )

        # 初始化结果字典
        result_dict = {
            'single_iccid': [],
            'no_iccid': [],
            'multiple_iccid': []
        }

        # 将查询结果转换为字典，方便快速查找
        serial_iccid_count = {item['serial_no']: item['iccid_count'] for item in results}

        # 分类统计
        for serial_no in serial_numbers:
            iccid_count = serial_iccid_count.get(serial_no, 0)  # 如果没有记录，默认为0
            if iccid_count == 1:
                result_dict['single_iccid'].append(serial_no)
            elif iccid_count >= 2:
                result_dict['multiple_iccid'].append(serial_no)
            else:
                result_dict['no_iccid'].append(serial_no)

        return response.json(0, result_dict)