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 hashlib
import time
import uuid

import openpyxl
from django.db import transaction, connection
from django.http import HttpResponse
from django.views.generic.base import View

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
from Object.ResponseObject import ResponseObject
from Object.TokenObject import TokenObject
from Object.UnicomObject import UnicomObjeect
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)
        elif operation == 'downloadCDK':  # 下载兑换码
            return self.package_cdk_export_excel(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)
            else:
                return response.json(404)

    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
                }
                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, 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` as flowTotal, '
        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))
                if order_dict['status'] == 1:
                    total_flow = UnicomObjeect.current_sim_traffic_usage_details(order_dict['iccid'])  # 4G总值
                    flow_total_usage = order_dict['flowTotal']  # 激活时当月已用流量
                    if flow_total_usage == '':  # 判断数值是否为空
                        flow_total_usage = 0
                    flow_total_usage = float(flow_total_usage)
                    order_dict['using_total'] = total_flow - flow_total_usage
                else:
                    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, 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, 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, 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, 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, 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, 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))
                if exchange_code_list:
                    ExchangeCode.objects.bulk_create(exchange_code_list)
                return response.json(0)
            return response.json(0)
        except Exception as e:
            LOGGER.info('*****UnicomManageController.create_package_cdk:errLine:{}, errMsg:{}'
                        .format(e.__traceback__.tb_lineno, repr(e)))
            return response.json(500, 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