ASJServer/AdminController/ServeManagementController.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import calendar
import datetime
import hashlib
import time
import uuid
import json


import paypalrestsdk
import requests
import xlrd
import xlwt
from django.db import transaction, connection
from django.db.models import F, Sum, Count, Q
from django.http import HttpResponse, StreamingHttpResponse
from django.utils.encoding import escape_uri_path
from django.views.generic.base import View

from Ansjer.config import PAYPAL_CRD
from Controller.Cron.CronTaskController import CronUpdateDataView
from Controller.UnicomCombo.UnicomComboTaskController import UnicomComboTaskView
from Model.models import VodBucketModel, CDKcontextModel, Store_Meal, Order_Model, \
    UID_Bucket, ExperienceContextModel, Lang, CloudLogModel, UidSetModel, Unused_Uid_Meal, \
    Device_Info, DeviceTypeModel, UnicomComboOrderInfo, AiService, CountryModel, CouponLang, CouponConfigModel, \
    CouponCombo, CouponModel, Device_User, AbnormalOrder, DailyReconciliation, StsCrdModel, LogModel
from Object.ResponseObject import ResponseObject
from Object.TokenObject import TokenObject
from Object.UnicomObject import UnicomObjeect
from Object.utils.PayPalUtil import PayPalService
from Service.CommonService import CommonService


class serveManagement(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):
        language = request_dict.get('language', 'en')
        response = ResponseObject(language, 'pc')
        if operation == 'exportCloudUserList':  # 导出云存用户信息
            return self.exportCloudUserList(request_dict, response)
        elif operation == 'getCloudDataList':
            return self.getCloudDataList(request_dict, response)
        elif operation == 'vodOrderReconcile':
            return self.vodOrderReconcile(request, request_dict, response)
        elif operation == 'cloudDataExport':  # 导出流失预警
            return self.cloudDataExport(request, response)
        elif operation == 'getDeviceOrderList':
            return self.getDeviceOrderList(request_dict, 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 == 'getVodBucketList':
                return self.getVodBucketList(userID, request_dict, response)
            elif operation == 'addOrEditVodBucket':
                return self.addOrEditVodBucket(userID, request_dict, response)
            elif operation == 'deleteVodBucket':
                return self.deleteVodBucket(userID, request_dict, response)
            elif operation == 'getStoreMealList':
                return self.getStoreMealList(userID, request_dict, response)
            elif operation == 'addOrEditStoreMeal':
                return self.addOrEditStoreMeal(userID, request_dict, response)
            elif operation == 'deleteStoreMeal':
                return self.deleteStoreMeal(userID, request_dict, response)
            elif operation == 'getStoreMealLanguage':
                return self.getStoreMealLanguage(
                    userID, request_dict, response)
            elif operation == 'addOrEditStoreMealLanguage':
                return self.addOrEditStoreMealLanguage(
                    userID, request_dict, response)
            elif operation == 'deleteStoreMealLanguage':
                return self.deleteStoreMealLanguage(
                    userID, request_dict, response)
            # CDK
            elif operation == 'getCdkList':
                return self.getCdkList(userID, request_dict, response)
            elif operation == 'createCdk':
                return self.createCdk(request_dict, response)
            elif operation == 'getVodStoreMeal':
                return self.getVodStoreMeal(response)
            elif operation == 'editExpress':
                return self.editExpress(request_dict, response)
            elif operation == 'deleteCdk':
                return self.deleteCdk(request_dict, response)
            elif operation == 'downloadCDK':
                return self.downloadCDK(request_dict, response)
            # 优惠券
            elif operation == 'getCouponList':  # 查询优惠券
                return self.getCouponList(request_dict, response)
            elif operation == 'getCouponId':  # 查询优惠券id
                return self.getCouponId(response)
            elif operation == 'addOrEditCoupon':  # 添加/编辑优惠券
                return self.addOrEditCoupon(request_dict, response)
            elif operation == 'deleteCoupon':  # 删除优惠券
                return self.deleteCoupon(request_dict, response)

            # 优惠券语言
            elif operation == 'getCouponLangList':  # 查询优惠券语言
                return self.getCouponLangList(request_dict, response)
            elif operation == 'addOrEditCouponLang':  # 添加/编辑优惠券语言
                return self.addOrEditCouponLang(request_dict, response)
            elif operation == 'deleteCouponLang':  # 删除优惠券语言
                return self.deleteCouponLang(request_dict, response)

            # 优惠券使用
            elif operation == 'getCouponUsingList':  # 查询优惠券使用
                return self.getCouponUsingList(request_dict, response)
            elif operation == 'editCouponUsing':  # 编辑优惠券使用
                return self.editCouponUsing(request_dict, response)
            elif operation == 'deleteCouponUsing':  # 删除优惠券使用
                return self.deleteCouponUsing(request_dict, response)

            elif operation == 'getAbnormalOrderList':
                return self.getAbnormalOrderList(request_dict, response)
            elif operation == 'updateAbnormalOrder':
                return self.updateAbnormalOrder(request_dict, response)
            elif operation == 'getDailyReconciliation':
                return self.getDailyReconciliation(request_dict, response)
            elif operation == 'deleteDeviceOrder':
                return self.deleteDeviceOrder(userID, request_dict, response)
            elif operation == 'getDevicePackageList':  # 云存设备套餐
                return self.getDevicePackageList(request_dict, response)
            elif operation == 'deleteDevicePackage':
                return self.deleteDevicePackage(userID, request_dict, response)
            elif operation == 'experiencereset':  # 重置设备云存体验
                return self.do_experience_reset(request_dict, userID, response)
            elif operation == 'mealTransfer':
                return self.meal_transfer(request, request_dict, response)

            # 云存用户信息
            elif operation == 'getCloudUserList':  # 获取云存用户信息
                return self.getCloudUserList(request_dict, response)
            elif operation == 'paypal-cycle-cancel':  # 取消循环扣款
                return self.paypal_cycle_cancel(request_dict, response)
            elif operation == 'distributeCoupons':  # 发放云存优惠券
                return self.distributeCoupons(request_dict, response)
            # 流失预警
            elif operation == 'deviceAttritionAlert':
                return self.deviceAttritionAlert(request_dict, response)
            elif operation == 'deactivationPackage':  # 停用套餐
                return self.deactivationPackage(request_dict, response)
            else:
                return response.json(404)

    @staticmethod
    def paypal_cycle_cancel(request_dict, response):
        """
        取消云存循环扣款
        """
        order_id = request_dict.get('orderID', None)
        if not order_id:
            return response.json(444)
        order_qs = Order_Model.objects.filter(orderID=order_id)
        order_qs = order_qs.filter(~Q(agreement_id='')).values("agreement_id")
        if not order_qs.exists():
            return response.json(800)
        paypalrestsdk.configure(PAYPAL_CRD)
        agreement_id = order_qs[0]['agreement_id']
        try:
            now_time = int(time.time())
            billing_agreement = paypalrestsdk.BillingAgreement.find(agreement_id)
            if billing_agreement.state != 'Active':
                Order_Model.objects.filter(agreement_id=agreement_id).update(agreement_id='', updTime=now_time)
                return response.json(0)
            cancel_note = {"note": "Canceling the agreement"}
            if billing_agreement.cancel(cancel_note):
                Order_Model.objects.filter(agreement_id=agreement_id).update(agreement_id='', updTime=now_time)
                return response.json(0)
            else:
                return response.json(10052)
        except Exception as e:
            print(repr(e))
            return response.json(10052)

    @staticmethod
    def distributeCoupons(request_dict, response):
        user_id = request_dict.get('userID', None)
        coupon_id = request_dict.get('couponID', None)
        valid_time = request_dict.get('validTime', None)

        if not all([user_id, coupon_id]):
            return response.json(444)

        now_time = int(time.time())
        try:
            CouponModel.objects.create(userID=user_id, coupon_config_id=coupon_id, valid_time=valid_time,
                                       distribute_time=now_time, create_time=now_time, update_time=now_time)

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

    def getVodBucketList(self, userID, request_dict, response):
        # 查询存储桶数据
        print('request_dict: ', request_dict)

        isSelect = request_dict.get('isSelect', None)
        if isSelect:
            # 获取全部数据作为存储桶选项
            vod_bucket_qs = VodBucketModel.objects.all().values('id', 'bucket')
            return response.json(
                0, {'list': CommonService.qs_to_list(vod_bucket_qs)})

        bucket = request_dict.get('bucket', None)
        mold = request_dict.get('mold', None)
        is_free = request_dict.get('is_free', None)
        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)

        if not all([pageNo, pageSize]):
            return response.json(444)

        page = int(pageNo)
        line = int(pageSize)
        try:
            if bucket or mold or is_free:  # 条件查询
                if bucket:
                    vod_bucket_qs = VodBucketModel.objects.filter(
                        bucket=bucket)
                elif mold:
                    vod_bucket_qs = VodBucketModel.objects.filter(
                        mold=int(mold))
                elif is_free:
                    vod_bucket_qs = VodBucketModel.objects.filter(
                        is_free=int(is_free))
            else:  # 查询全部
                vod_bucket_qs = VodBucketModel.objects.filter().all()
            total = len(vod_bucket_qs)
            vod_buckets = vod_bucket_qs[(page - 1) * line:page * line]
            vod_bucket_list = []
            for vod_bucket in vod_buckets:
                vod_bucket_list.append({
                    'bucketID': vod_bucket.id,
                    'bucket': vod_bucket.bucket,
                    'content': vod_bucket.content,
                    'mold': vod_bucket.mold,
                    'area': vod_bucket.area,
                    'region': vod_bucket.region,
                    'endpoint': vod_bucket.endpoint,
                    'is_free': vod_bucket.is_free,
                    'storeDay': vod_bucket.storeDay,
                    'region_id': vod_bucket.region_id,
                    'addTime': time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(vod_bucket.addTime)),
                    'updTime': time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(vod_bucket.updTime)),
                })
            print('vod_bucket_list: ', vod_bucket_list)
            return response.json(
                0, {'list': vod_bucket_list, 'total': total})
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def addOrEditVodBucket(self, userID, request_dict, response):
        # 添加/编辑存储桶
        print('request_dict: ', request_dict)
        bucketID = request_dict.get('bucketID', None)
        bucket = request_dict.get('bucket', '').strip()  # 移除字符串头尾的空格
        content = request_dict.get('content', '').strip()
        mold = int(request_dict.get('mold', 1))
        area = request_dict.get('area', '').strip()
        region = request_dict.get('region', '').strip()
        endpoint = request_dict.get('endpoint', '').strip()
        is_free = int(request_dict.get('is_free', 0))
        storeDay = int(request_dict.get('storeDay', 0))
        region_id = int(request_dict.get('region_id', 1))
        isEdit = request_dict.get('isEdit', None)

        if not all([bucket, content, area, region, endpoint]):
            return response.json(444)

        try:
            now_time = int(time.time())
            vod_bucket_data = {
                'bucket': bucket,
                'content': content,
                'mold': mold,
                'area': area,
                'region': region,
                'endpoint': endpoint,
                'is_free': is_free,
                'storeDay': storeDay,
                'region_id': region_id,
            }
            if isEdit:
                if not bucketID:
                    return response.json(444)
                vod_bucket_data['updTime'] = now_time
                VodBucketModel.objects.filter(
                    id=bucketID).update(
                    **vod_bucket_data)
            else:
                vod_bucket_data['addTime'] = now_time
                VodBucketModel.objects.create(**vod_bucket_data)
            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)))

    def deleteVodBucket(self, userID, request_dict, response):
        # 删除存储桶
        print('request_dict: ', request_dict)
        bucketID = request_dict.get('bucketID', None)
        if not bucketID:
            return response.json(444)
        try:
            VodBucketModel.objects.filter(id=bucketID).delete()
            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)))

    def getStoreMealList(self, userID, request_dict, response):
        # 获取云存套餐信息数据
        print('request_dict: ', request_dict)

        isSelect = request_dict.get('isSelect', None)
        if isSelect:
            # 获取套餐ID作为选项
            store_meal_qs = Store_Meal.objects.all().values('id', 'bucket__bucket')
            return response.json(
                0, {'list': CommonService.qs_to_list(store_meal_qs)})

        bucket = request_dict.get('bucket', None)
        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)

        if not all([pageNo, pageSize]):
            return response.json(444)

        page = int(pageNo)
        line = int(pageSize)
        try:
            if bucket:  # 条件查询
                bucket_id = VodBucketModel.objects.filter(
                    bucket=bucket).values('id')[0]['id']
                store_meal_qs = Store_Meal.objects.filter(
                    bucket_id=bucket_id)
            else:  # 查询全部
                store_meal_qs = Store_Meal.objects.filter()
            store_meal_val = store_meal_qs.values(
                'id',
                'bucket__bucket',
                'day',
                'expire',
                'commodity_type',
                'commodity_code',
                'is_discounts',
                'discount_price',
                'virtual_price',
                'price',
                'currency',
                'symbol',
                'is_show',
                'is_ai',
                'pixel_level',
                'add_time',
                'update_time')
            total = len(store_meal_val)
            store_meals = store_meal_val[(page - 1) * line:page * line]
            store_meal_list = []
            for store_meal in store_meals:
                # 获取支付方式列表
                pay_type_list = [
                    pay_type['id'] for pay_type in Store_Meal.objects.get(
                        id=store_meal['id']).pay_type.values('id')]
                # 组织响应数据
                store_meal_list.append({
                    'storeMealID': store_meal['id'],
                    'bucket': store_meal['bucket__bucket'],
                    'day': store_meal['day'],
                    'expire': store_meal['expire'],
                    'commodity_type': store_meal['commodity_type'],
                    'pay_type': pay_type_list,
                    'commodity_code': store_meal['commodity_code'],
                    'is_discounts': store_meal['is_discounts'],
                    'discount_price': store_meal['discount_price'],
                    'virtual_price': store_meal['virtual_price'],
                    'price': store_meal['price'],
                    'currency': store_meal['currency'],
                    'symbol': store_meal['symbol'],
                    'is_show': store_meal['is_show'],
                    'is_ai': store_meal['is_ai'],
                    'pixel_level': store_meal['pixel_level'],
                    'addTime': store_meal['add_time'].strftime("%Y-%m-%d %H:%M:%S"),
                    'updTime': store_meal['update_time'].strftime("%Y-%m-%d %H:%M:%S"),
                })
            print('store_meal_list: ', store_meal_list)
            return response.json(
                0, {'list': store_meal_list, 'total': total})
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def addOrEditStoreMeal(self, userID, request_dict, response):
        # 添加/编辑套餐
        print('request_dict: ', request_dict)
        storeMealID = request_dict.get('storeMealID', None)
        bucket = request_dict.get('bucket', '')
        day = int(request_dict.get('day', 0))
        expire = int(request_dict.get('expire', 0))
        commodity_type = int(request_dict.get('commodity_type', 0))
        pay_type = request_dict.get(
            'pay_type', '')[
                   1:-1].split(',')  # '[1,2]' -> ['1','2']
        commodity_code = request_dict.get('commodity_code', '')
        is_discounts = int(request_dict.get('is_discounts', 0))
        discount_price = request_dict.get('discount_price', '')
        virtual_price = request_dict.get('virtual_price', '')
        price = request_dict.get('price', '')
        currency = request_dict.get('currency', '')
        symbol = request_dict.get('symbol', '')
        is_show = int(request_dict.get('is_show', 0))
        is_ai = int(request_dict.get('is_ai', 0))
        pixel_level = int(request_dict.get('pixel_level', 0))
        isEdit = request_dict.get('isEdit', None)

        if not all([bucket, pay_type, price, currency, symbol]):
            return response.json(444)

        try:
            bucket_id = VodBucketModel.objects.filter(
                bucket=bucket).values('id')[0]['id']
            store_meal_data = {
                'bucket_id': bucket_id,
                'day': day,
                'expire': expire,
                'commodity_type': commodity_type,
                'commodity_code': commodity_code,
                'is_discounts': is_discounts,
                'discount_price': discount_price,
                'virtual_price': virtual_price,
                'price': price,
                'currency': currency,
                'symbol': symbol,
                'is_show': is_show,
                'is_ai': is_ai,
                'pixel_level': pixel_level,
            }
            if isEdit:
                if not storeMealID:
                    return response.json(444)
                Store_Meal.objects.filter(
                    id=storeMealID).update(
                    **store_meal_data)
                Store_Meal.objects.get(id=storeMealID).pay_type.set(pay_type)
            else:
                Store_Meal.objects.create(
                    **store_meal_data).pay_type.set(pay_type)
            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)))

    def deleteStoreMeal(self, userID, request_dict, response):
        # 删除套餐信息
        print('request_dict: ', request_dict)
        storeMealID = request_dict.get('storeMealID', None)
        if not storeMealID:
            return response.json(444)
        try:
            Store_Meal.objects.filter(id=storeMealID).delete()
            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)))

    def getStoreMealLanguage(self, userID, request_dict, response):
        # 获取套餐语言
        print('request_dict: ', request_dict)
        storeMealID = request_dict.get('storeMealID', None)
        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)

        if not all([pageNo, pageSize]):
            return response.json(444)

        page = int(pageNo)
        line = int(pageSize)
        try:
            if storeMealID:  # 条件查询
                store_meal_lang_qs = Store_Meal.objects.filter(id=storeMealID)
            else:  # 查询全部
                store_meal_lang_qs = Store_Meal.objects.filter(
                    lang__isnull=False)
            store_meal_lang_val = store_meal_lang_qs.values(
                'id',
                'lang__id',
                'lang__lang',
                'lang__title',
                'lang__content',
                'lang__discount_content',
            )
            total = len(store_meal_lang_val)
            store_meal_langs = store_meal_lang_val[(
                                                           page - 1) * line:page * line]
            store_meal_lang_list = []
            for store_meal_lang in store_meal_langs:
                store_meal_lang_list.append({
                    'storeMealID': store_meal_lang['id'],
                    'langID': store_meal_lang['lang__id'],
                    'lang': store_meal_lang['lang__lang'],
                    'title': store_meal_lang['lang__title'],
                    'content': store_meal_lang['lang__content'],
                    'discountContent': store_meal_lang['lang__discount_content'],
                })
            print('store_meal_lang_list: ', store_meal_lang_list)
            return response.json(
                0, {'list': store_meal_lang_list, 'total': total})
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def addOrEditStoreMealLanguage(self, userID, request_dict, response):
        # 添加/编辑套餐语言
        print('request_dict: ', request_dict)
        storeMealID = request_dict.get('storeMealID', None)
        lang = request_dict.get('lang', None)
        title = request_dict.get('title', None)
        content = request_dict.get('content', None)
        discount_content = request_dict.get('discount_content', '')
        isEdit = request_dict.get('isEdit', None)

        if not all([storeMealID, lang, title, content]):
            return response.json(444)

        try:
            # 查询套餐是否存在
            store_meal_qs = Store_Meal.objects.get(id=storeMealID)
            if not store_meal_qs:
                return response.json(173)
            if isEdit:  # 编辑
                langID = request_dict.get('langID', None)
                if not langID:
                    return response.json(444)
                Lang.objects.filter(
                    id=langID).update(
                    lang=lang,
                    title=title,
                    content=content,
                    discount_content=discount_content)
            else:  # 添加
                lang_obj = Lang.objects.filter(
                    lang=lang,
                    title=title,
                    content=content,
                    discount_content=discount_content)
                if not lang_obj.exists():
                    # 数据不存在,lang表创建数据
                    Lang.objects.create(
                        lang=lang,
                        title=title,
                        content=content,
                        discount_content=discount_content)
                    lang_obj = Lang.objects.filter(
                        lang=lang,
                        title=title,
                        content=content,
                        discount_content=discount_content)
                store_meal_qs.lang.add(*lang_obj)  # store_meal表添加语言数据
            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)))

    def deleteStoreMealLanguage(self, userID, request_dict, response):
        # 删除套餐语言
        storeMealID = request_dict.get('storeMealID', None)
        langID = request_dict.get('langID', None)

        if not all([storeMealID, langID]):
            return response.json(444)

        try:
            storeMeal_qs = Store_Meal.objects.get(id=storeMealID)
            if not storeMeal_qs:
                return response.json(173)
            lang_qs = Lang.objects.filter(id=langID)
            storeMeal_qs.lang.remove(*lang_qs)
            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)))

    def getCdkList(self, userID, request_dict, response):
        # 获取激活码列表
        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)
        cdk = request_dict.get('cdk', None)
        order = request_dict.get('order', None)
        is_activate = request_dict.get('is_activate', None)
        mold = request_dict.get('mold', None)
        lang = request_dict.get('lang', 'cn')
        if not all([pageNo, pageSize]):
            return response.json(444)

        page = int(pageNo)
        line = int(pageSize)
        try:
            if cdk:
                searchVal = cdk.strip()
            if order:
                searchVal = order.strip()
            if is_activate:
                searchVal = is_activate.strip()

            cdk_qs = CDKcontextModel.objects.filter().all()
            if cdk:
                cdk_qs = cdk_qs.filter(cdk__contains=searchVal)
            if order:
                cdk_qs = cdk_qs.filter(order__contains=searchVal)
            if is_activate:
                cdk_qs = cdk_qs.filter(is_activate=searchVal)
            if mold:
                cdk_qs = cdk_qs.filter(rank__bucket__mold=mold)

            cdk_qs = cdk_qs.filter(rank__lang__lang=lang)
            cdk_qs = cdk_qs.annotate(rank__title=F('rank__lang__title'))
            cdk_qs = cdk_qs.values(
                'id',
                'cdk',
                'create_time',
                'valid_time',
                'is_activate',
                'is_down',
                'rank__id',
                'rank__title',
                'order',
                'create_time',
                'rank__bucket__mold')
            cdk_qs = cdk_qs.order_by('-create_time')  # 根据CDK创建时间降序排序
            count = cdk_qs.count()
            cdk_qs = cdk_qs[(page - 1) * line:page * line]
            return response.json(
                0, {'list': list(cdk_qs), 'total': count})
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def editExpress(self, request_dict, response):
        order_id = request_dict.get('orderID', None)
        express_id = request_dict.get('express_id', None)
        if not all([order_id, express_id]):
            return response.json(444, {'error param': 'orderID or express_id'})
        try:
            CDKcontextModel.objects.filter(order=order_id).update(express_id=express_id)
            return response.json(0)
        except Exception as e:
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def getVodStoreMeal(self, response):
        try:
            store_meal = Store_Meal.objects.filter(Q(is_show=0), ~Q(commodity_code='paypal_cycle'), ~Q(pay_type=10),
                                                   Q(lang__lang='cn')).values('id', 'lang__title', 'lang__content',
                                                                              'pixel_level', 'is_ai', 'pay_type')
            if not store_meal.exists():
                return response.json(0, [])
            store_meal_list = []
            for item in store_meal:
                pixel_content = ''
                if item['pay_type'] != 11:
                    pixel_content = '(4k)' if item['pixel_level'] == 1 else '(4k以下)'
                ai_content = '+AI套餐' if item['is_ai'] == 1 else ''
                store_meal_list.append({'id': item['id'], 'title': item['lang__title'] + '-' + item[
                    'lang__content'] + ai_content + pixel_content})
            return response.json(0, store_meal_list)
        except Exception as e:
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def createCdk(self, request_dict, response):
        cdk_num = request_dict.get("cdknum", None)
        mold = request_dict.get('mold', None)
        order = request_dict.get('order', None)
        rank = request_dict.get('rank', None)
        if not all([cdk_num, rank]):
            return response.json(444, {'error param': 'cdknum or rank'})
        store_meal = Store_Meal.objects.filter(id=rank)
        # sm_qs = Store_Meal.objects.filter(
        #     pay_type__payment='cdk_pay', bucket__mold=mold, is_show=0)
        if not store_meal.exists():
            return response.json(173)
        cdk_list = []
        for i in range(int(cdk_num)):
            now_time = int(time.time())
            cdk = hashlib.md5((str(uuid.uuid1()) +
                               str(now_time)).encode('utf-8')).hexdigest()
            cdk_model = CDKcontextModel(
                cdk=cdk,
                create_time=now_time,
                valid_time=0,
                is_activate=0,
                is_down=0,
                rank_id=rank,
                order=order,
            )
            cdk_list.append(cdk_model)
        try:
            CDKcontextModel.objects.bulk_create(cdk_list)
        except Exception as e:
            return response.json(404, repr(e))
        else:
            return response.json(0)

    def deleteCdk(self, request_dict, response):
        cdk_id = request_dict.get("id", None)
        try:
            CDKcontextModel.objects.get(id=cdk_id).delete()
            return response.json(0)
        except Exception as e:
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def downloadCDK(self, request_dict, response):
        region = request_dict.get('region', None)
        content = ''
        if region == 'cn':
            # 下载国内未使用激活码
            content += '激活码(国内)\n'
            cdk_inactivate_qs = CDKcontextModel.objects.filter(is_down=0, is_activate=0, rank__bucket__mold=0,
                                                               rank__is_show=0).values('cdk')
        else:
            # 下载国外未使用激活码
            content += '激活码(国外)\n'
            cdk_inactivate_qs = CDKcontextModel.objects.filter(is_down=0, is_activate=0, rank__bucket__mold=1,
                                                               rank__is_show=0).values('cdk')
        for cdk_inactivate in cdk_inactivate_qs:
            content += cdk_inactivate['cdk'] + '\n'
        # print(content)

        cdk_inactivate_qs.update(is_down=1)
        response = StreamingHttpResponse(content)
        response['Content-Type'] = 'application/octet-stream'
        response['Content-Disposition'] = 'attachment;filename="CDK.txt"'
        return response

    @staticmethod
    def getCouponList(request_dict, response):
        combo_id = request_dict.get('comboID', None)
        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)

        if not all([pageNo, pageSize]):
            return response.json(444)

        page = int(pageNo)
        line = int(pageSize)

        try:
            if combo_id:  # 根据套餐id查询
                coupon_id_list = CouponCombo.objects.filter(combo_id=combo_id).values_list('coupon_id', flat=True)
                coupon_qs = CouponConfigModel.objects.filter(id__in=coupon_id_list).values(
                    'id', 'type', 'use_range', 'coupon_discount')
            else:
                coupon_qs = CouponConfigModel.objects.filter().values(
                    'id', 'type', 'use_range', 'coupon_discount')
            count = coupon_qs.count()
            coupon_list = list(coupon_qs[(page - 1) * line:page * line])
            for coupon in coupon_list:
                coupon['combo_id'] = 'NA'
                coupon_combo_qs = CouponCombo.objects.filter(coupon_id=coupon['id']).values('combo_id')
                if coupon_combo_qs.exists():
                    coupon['combo_id'] = coupon_combo_qs[0]['combo_id']
            return response.json(0, {'list': coupon_list, 'total': count})
        except Exception as e:
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @staticmethod
    def getCouponId(response):
        coupon_qs = CouponConfigModel.objects.all().values('id')
        return response.json(0, {'list': list(coupon_qs)})

    @staticmethod
    def addOrEditCoupon(request_dict, response):
        coupon_id = request_dict.get('couponID', None)
        combo_id = request_dict.get('comboID', None)
        coupon_type = int(request_dict.get('type', 0))
        use_range = request_dict.get('useRange', '')
        coupon_discount = request_dict.get('couponDiscount', '')
        is_edit = request_dict.get('isEdit', None)

        if not all([coupon_type, use_range, coupon_discount]):
            return response.json(444)

        try:
            now_time = int(time.time())
            coupon_data = {
                'type': coupon_type,
                'use_range': use_range,
                'coupon_discount': coupon_discount
            }
            if is_edit:
                if not coupon_id:
                    return response.json(444)
                CouponConfigModel.objects.filter(id=coupon_id).update(**coupon_data)
                CouponCombo.objects.filter(coupon_id=coupon_id).update(combo_id=combo_id, update_time=now_time)
            else:
                coupon = CouponConfigModel.objects.create(**coupon_data)
                coupon_id = coupon.id
                # 关联套餐
                CouponCombo.objects.create(coupon_id=coupon_id, combo_id=combo_id,
                                           create_time=now_time, update_time=now_time)
            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 deleteCoupon(request_dict, response):
        coupon_id = request_dict.get('couponID', None)
        if not coupon_id:
            return response.json(444)

        try:
            CouponConfigModel.objects.filter(id=coupon_id).delete()
            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 getCouponLangList(request_dict, response):
        coupon_id = request_dict.get('couponID', None)
        page = request_dict.get('pageNo', None)
        line = request_dict.get('pageSize', None)

        if not all([page, line]):
            return response.json(444)

        page = int(page)
        line = int(line)

        try:
            if coupon_id:  # 条件查询
                coupon_qs = CouponConfigModel.objects.filter(id=coupon_id)
            else:  # 查询语言id非空数据
                coupon_qs = CouponConfigModel.objects.filter(~Q(lang__id=None))
            coupon_lang_qs = coupon_qs.values(
                'id',
                'lang__id',
                'lang__lang',
                'lang__instruction',
                'lang__quota',
                'lang__unit',
                'lang__remark'
            )
            total = len(coupon_lang_qs)
            coupon_lang_qs = coupon_lang_qs[(page - 1) * line:page * line]
            coupon_lang_list = []
            for coupon_lang in coupon_lang_qs:
                coupon_lang_list.append({
                    'couponID': coupon_lang['id'],
                    'langID': coupon_lang['lang__id'],
                    'lang': coupon_lang['lang__lang'],
                    'instruction': coupon_lang['lang__instruction'],
                    'quota': coupon_lang['lang__quota'],
                    'unit': coupon_lang['lang__unit'],
                    'remark': coupon_lang['lang__remark']
                })
            return response.json(0, {'list': coupon_lang_list, 'total': total})
        except Exception as e:
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @staticmethod
    def addOrEditCouponLang(request_dict, response):
        coupon_id = request_dict.get('couponID', None)
        lang = request_dict.get('lang', None)
        instruction = request_dict.get('instruction', '')
        quota = request_dict.get('quota', '')
        unit = request_dict.get('unit', '')
        unit = request_dict.get('unit', '')
        remark = request_dict.get('remark', None)
        is_edit = request_dict.get('isEdit', None)

        if not all([coupon_id, lang]):
            return response.json(444)

        try:
            # 查询优惠券是否存在
            coupon_qs = CouponConfigModel.objects.get(id=coupon_id)
            if not coupon_qs:
                return response.json(173)
            if is_edit:  # 编辑
                lang_id = request_dict.get('langID', None)
                if not lang_id:
                    return response.json(444)
                CouponLang.objects.filter(
                    id=lang_id).update(
                    lang=lang,
                    instruction=instruction,
                    quota=quota,
                    unit=unit,
                    remark=remark
                )
            else:  # 添加
                lang_obj = CouponLang.objects.filter(
                    lang=lang,
                    instruction=instruction,
                    quota=quota,
                    unit=unit,
                    remark=remark
                )
                if not lang_obj.exists():
                    # 数据不存在,lang表创建数据
                    CouponLang.objects.create(
                        lang=lang,
                        instruction=instruction,
                        quota=quota,
                        unit=unit,
                        remark=remark
                    )
                    lang_obj = CouponLang.objects.filter(
                        lang=lang,
                        instruction=instruction,
                        quota=quota,
                        unit=unit,
                        remark=remark
                    )
                coupon_qs.lang.add(*lang_obj)  # coupon_config表添加语言数据
            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 deleteCouponLang(request_dict, response):
        coupon_id = request_dict.get('couponID', None)
        lang_id = request_dict.get('langID', None)

        if not all([coupon_id, lang_id]):
            return response.json(444)

        try:
            coupon_qs = CouponConfigModel.objects.get(id=coupon_id)
            if not coupon_qs:
                return response.json(173)
            lang_qs = CouponLang.objects.filter(id=lang_id)
            coupon_qs.lang.remove(*lang_qs)
            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 getCouponUsingList(request_dict, response):
        username = request_dict.get('username', None)
        phone = request_dict.get('phone', None)
        user_email = request_dict.get('userEmail', None)
        use_status = request_dict.get('useStatus', None)
        page_no = request_dict.get('pageNo', None)
        page_size = request_dict.get('pageSize', None)

        if not all([page_no, page_size]):
            return response.json(444)

        page = int(page_no)
        line = int(page_size)

        try:
            coupon_qs = CouponModel.objects.all()
            if use_status:
                coupon_qs = coupon_qs.filter(use_status=use_status)
            if not coupon_qs.exists():
                return response.json(0, [])

            device_user_qs = Device_User.objects.filter().values(
                'userID', 'username', 'phone', 'userEmail')
            if username:
                device_user_qs = device_user_qs.filter(username=username)
                if not device_user_qs.exists():
                    return response.json(0, [])
                user_id = device_user_qs.first()['userID']
                coupon_qs = coupon_qs.filter(userID=user_id)
            if phone:
                device_user_qs = device_user_qs.filter(phone__contains=phone)
                if not device_user_qs.exists():
                    return response.json(0, [])
                user_id = device_user_qs.first()['userID']
                coupon_qs = coupon_qs.filter(userID=user_id)
            if user_email:
                device_user_qs = device_user_qs.filter(userEmail__contains=user_email)
                if not device_user_qs.exists():
                    return response.json(0, [])
                user_id = device_user_qs.first()['userID']
                coupon_qs = coupon_qs.filter(userID=user_id)

            coupon_qs = coupon_qs.values(
                'id', 'use_status', 'distribute_time', 'valid_time', 'update_time', 'create_time', 'userID',
                'coupon_config__id', 'userID')
            count = coupon_qs.count()
            coupons = coupon_qs[(page - 1) * line:page * line]
            coupon_list = []
            for coupon in coupons:
                device_user_qs = Device_User.objects.filter(userID=coupon['userID']).values('username', 'phone',
                                                                                            'userEmail')
                username = device_user_qs[0]['username'] if device_user_qs.exists() else ''
                phone = device_user_qs[0]['phone'] if device_user_qs.exists() else ''
                user_email = device_user_qs[0]['userEmail'] if device_user_qs.exists() else ''
                coupon_list.append({
                    'id': coupon['id'],
                    'useStatus': coupon['use_status'],
                    'distributeTime': coupon['distribute_time'],
                    'validTime': coupon['valid_time'],
                    'updateTime': coupon['update_time'],
                    'createTime': coupon['create_time'],
                    'couponID': coupon['coupon_config__id'],
                    'username': username,
                    'phone': phone,
                    'userEmail': user_email,
                })
            return response.json(0, {'list': coupon_list, 'total': count})
        except Exception as e:
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    @staticmethod
    def editCouponUsing(request_dict, response):
        id = request_dict.get('id', None)
        use_status = request_dict.get('useStatus', 0)
        valid_time = request_dict.get('validTime', None)

        if not all([id, use_status, valid_time]):
            return response.json(444)

        try:
            now_time = int(time.time())
            CouponModel.objects.filter(id=id).update(use_status=use_status, valid_time=valid_time, update_time=now_time)
            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 deleteCouponUsing(request_dict, response):
        id = request_dict.get('id', None)
        if not id:
            return response.json(444)

        try:
            CouponModel.objects.filter(id=id).delete()
            return response.json(0)
        except Exception as e:
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def getDeviceOrderList(self, request_dict, response):
        print('request_dict: ', request_dict)
        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)
        uid = request_dict.get('uid', None)
        channel = request_dict.get('channel', None)
        orderID = request_dict.get('orderID', None)
        order_ids = request_dict.get('order_ids', None)
        userID__username = request_dict.get('userID__username', None)
        currency = request_dict.get('currency', None)
        payType = request_dict.get('payType', None)
        status = request_dict.get('status', None)
        timeRange = request_dict.getlist('timeRange[]', None)
        orderType = request_dict.get('orderType', None)
        serialNumber = request_dict.get('serialNumber', None)
        trade_no = request_dict.get('tradeNo', None)
        userID__phone = request_dict.get('userID__phone', None)

        if not all([pageNo, pageSize]):
            return response.json(444)

        page = int(pageNo)
        line = int(pageSize)
        try:
            omqs = Order_Model.objects.all()
            # 筛选指定设备id的订单
            if uid:
                omqs = omqs.filter(UID=uid)
            if channel:
                omqs = omqs.filter(channel=channel)
            if orderID:
                omqs = omqs.filter(orderID=orderID)
            if userID__username:
                omqs = omqs.filter(userID__username=userID__username)
            if currency:
                omqs = omqs.filter(currency=currency)
            if payType:
                omqs = omqs.filter(payType=payType)
            if status:
                omqs = omqs.filter(status=status)
            if orderType:
                omqs = omqs.filter(order_type=int(orderType))
            if trade_no:
                omqs = omqs.filter(trade_no=trade_no)
            if userID__phone:
                omqs = omqs.filter(userID__phone=userID__phone)
            if order_ids:
                order_ids = order_ids.split(',')
                omqs = omqs.filter(Q(orderID__in=order_ids) | Q(trade_no__in=order_ids))
            if serialNumber:
                device_uid = CommonService.query_uid_with_serial(serialNumber)
                omqs = omqs.filter(UID=device_uid)
            if timeRange:
                startTime, endTime = int(
                    timeRange[0][:-3]), int(timeRange[1][:-3])
                omqs = omqs.filter(
                    addTime__gte=startTime,
                    addTime__lte=endTime)
            if not omqs.exists():
                return response.json(0, [])

            order_list = []
            count = omqs.count()

            order_ql = omqs.values("orderID", "UID", "userID__username", "userID__NickName", "channel", "desc", "price",
                                   "refunded_amount", "currency", "addTime", "updTime", "paypal", "payType",
                                   "rank__day", "rank__price", "status", "order_type", "paymentID", "trade_no",
                                   "payTime", "userID__region_country", "userID__phone")
            order_ql = order_ql.order_by('-addTime')  # 根据CDK创建时间降序排序
            order_ql = order_ql[(page - 1) * line:page * line]
            for order in order_ql:
                # 查询国家信息
                country = 'N/A'
                country_qs = CountryModel.objects.filter(id=order['userID__region_country']).values('country_name')
                if country_qs.exists():
                    country = country_qs[0]['country_name']
                serialNumber = CommonService.query_serial_with_uid(order['UID'])
                data = {
                    'orderID': order['orderID'],
                    'UID': order['UID'],
                    'userID__username': order['userID__username'],
                    'userID__NickName': order['userID__NickName'],
                    'channel': order['channel'],
                    'desc': order['desc'],
                    'price': order['price'],
                    'refunded_amount': order['refunded_amount'],
                    'currency': order['currency'],
                    'addTime': order['addTime'],
                    'updTime': order['updTime'],
                    'paypal': order['paypal'],
                    'payType': order['payType'],
                    'rank__day': order['rank__day'],
                    'rank__price': order['rank__price'],
                    'status': order['status'],
                    'refund_status': 1 if order['status'] in [1, 4, 6] else 2,  # 订单显示(或不显示)退款功能
                    'order_type': order['order_type'],
                    'payTime': order['payTime'] if order['payTime'] else 'N/A',
                    'serialNumber': 'N/A' if serialNumber == order['UID'] else serialNumber,
                    'country': country,
                    'userID__phone': order['userID__phone'],
                }
                #  订单显示(或不显示)停用
                if order['order_type'] == 0:  # 云存
                    uid_bucket = UID_Bucket.objects.filter(uid=order['UID']).values('use_status')
                    user_status = uid_bucket[0]['use_status'] if uid_bucket.exists() else ''
                    if user_status != '':
                        data['user_status'] = user_status
                    else:
                        data['user_status'] = 2
                elif order['order_type'] == 1:  # ai
                    ai_service_qs = AiService.objects.filter(
                        Q(orders_id=order['orderID']) & ~Q(use_status=2))
                    if ai_service_qs.exists():
                        data['user_status'] = 1
                    else:
                        data['user_status'] = 2
                elif order['order_type'] == 2:  # 联通4G
                    unicom_combor_order_qs = UnicomComboOrderInfo.objects.filter(
                        Q(order_id=order['orderID']) & ~Q(status=2))
                    if unicom_combor_order_qs.exists():
                        data['user_status'] = 1
                    else:
                        data['user_status'] = 2
                #  添加PayPal交易号字段
                data['trade_no'] = 'N/A'
                if data['payType'] == 1:
                    data['trade_no'] = order['trade_no'] if order['trade_no'] else 'N/A'
                data['express_id'] = ''
                if data['payType'] == 11:
                    cdk_context_qs = CDKcontextModel.objects.filter(order=data['orderID']).values('express_id')
                    if cdk_context_qs.exists():
                        data['express_id'] = cdk_context_qs[0]['express_id']
                order_list.append(data)
            return response.json(
                0, {'list': order_list, 'total': count})
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def getAbnormalOrderList(self, request_dict, response):
        print('request_dict: ', request_dict)
        pageNo = request_dict.get('page_no', None)
        pageSize = request_dict.get('page_size', None)
        order_id = request_dict.get('order_id', None)
        username = request_dict.get('username', None)
        pay_type = request_dict.get('pay_type', None)
        status = request_dict.get('status', None)
        trade_no = request_dict.get('trade_no', None)
        agreement_id = request_dict.get('agreement_id', None)
        start_time = request_dict.get('start_time', None)
        end_time = request_dict.get('end_time', None)

        if not all([pageNo, pageSize]):
            return response.json(444)

        page = int(pageNo)
        line = int(pageSize)
        try:
            order_qs = AbnormalOrder.objects.all()
            # 筛选指定订单
            if order_id:
                order_qs = order_qs.filter(order_id=order_id)
            if username:
                order_qs = order_qs.filter(username=username)
            if pay_type:
                order_qs = order_qs.filter(pay_type=pay_type)
            if status:
                order_qs = order_qs.filter(status=status)
            if trade_no:
                order_qs = order_qs.filter(trade_no=trade_no)
            if agreement_id:
                order_qs = order_qs.filter(agreement_id=agreement_id)
            if start_time and end_time:
                order_qs = order_qs.filter(pay_time__gte=start_time, pay_time__lt=end_time)
            if not order_qs.exists():
                return response.json(0, [])
            count = order_qs.count()
            order_qs = order_qs.values("id", "order_id", "username", "pay_type", "status", "trade_no", "agreement_id",
                                       "price", "pay_time", "meal_name").order_by('-pay_time')[
                       (page - 1) * line:page * line]
            return response.json(0, {'list': list(order_qs), 'total': count})
        except Exception as e:
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def updateAbnormalOrder(self, request_dict, response):
        abnormal_order_id = request_dict.get('id', None)
        if not all([abnormal_order_id]):
            return response.json(444)
        abnormal_order = AbnormalOrder.objects.filter(id=abnormal_order_id)
        if not abnormal_order.exists():
            return response.json(173)
        try:
            abnormal_order_qs = abnormal_order.values('trade_no', 'pay_time', 'pay_type')
            trade_no = abnormal_order_qs[0]['trade_no']
            pay_time = abnormal_order_qs[0]['pay_time']
            pay_type = abnormal_order_qs[0]['pay_type']
            if pay_type in [0, 1]:  # 处理paypal订单
                order_qs = Order_Model.objects.filter(trade_no=trade_no)
                if order_qs.exists():
                    order_qs.update(payTime=pay_time, addTime=pay_time)
                    abnormal_order.update(status=1)
                    return response.json(0)
                params = {'trade_no': trade_no, 'pay_time': pay_time}
                eur_response = requests.get('https://www.zositeche.com/testApi/checkOrderExist', params=params)
                if eur_response.status_code == 200:
                    result = eur_response.json()
                    if result['result_code'] == 0 and result['result']['is_exist']:
                        abnormal_order.update(status=1)
            elif pay_type == 3:  # 处理微信订单
                pass
            return response.json(0)
        except Exception as e:
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def getDailyReconciliation(self, request_dict, response):
        print('request_dict: ', request_dict)
        page_no = request_dict.get('page_no', None)
        page_size = request_dict.get('page_size', None)
        start_time = request_dict.get('start_time', None)
        end_time = request_dict.get('end_time', None)

        if not all([page_no, page_size]):
            return response.json(444)

        page = int(page_no)
        line = int(page_size)
        try:
            daily_reconciliation = DailyReconciliation.objects.all()
            # 筛选指定订单
            if start_time and end_time:
                daily_reconciliation = daily_reconciliation.filter(time__gte=start_time, time__lt=end_time)
            if not daily_reconciliation.exists():
                return response.json(0, [])
            count = daily_reconciliation.count()
            daily_reconciliation_qs = daily_reconciliation.values("id", "order_ids", "ansjer_total", "ansjer_num",
                                                                  "paypal_num", "paypal_total", "time").order_by(
                '-time')[(page - 1) * line:page * line]
            for item in daily_reconciliation_qs:
                item['order_info'] = []
                if not item['order_ids']:
                    continue
                order_list = item['order_ids'].split(',')
                order_qs = Order_Model.objects.filter(Q(orderID__in=order_list) | Q(trade_no__in=order_list)).values(
                    "orderID", "UID", "userID__username", "userID__NickName", "channel", "desc", "price",
                    "refunded_amount", "currency", "addTime", "updTime", "paypal", "payType", "rank__day",
                    "rank__price", "status", "order_type", "paymentID", "trade_no", "payTime", "userID__region_country",
                    "userID__phone").order_by('-addTime')
                while True:
                    params = {'pageNo': 1, 'pageSize': 100, 'order_ids': item['order_ids']}
                    eur_response = requests.get('https://www.zositeche.com/serveManagement/getDeviceOrderList', params)
                    if eur_response.status_code == 200:
                        result = eur_response.json()
                        if result['code'] == 0:
                            item['order_info'] = result['data']['list'] if result['data'] else result['data']
                            break
                for order in order_qs:
                    # 查询国家信息
                    country = 'N/A'
                    country_qs = CountryModel.objects.filter(id=order['userID__region_country']).values('country_name')
                    if country_qs.exists():
                        country = country_qs[0]['country_name']
                    serial_number = CommonService.query_serial_with_uid(order['UID'])
                    data = {'orderID': order['orderID'], 'UID': order['UID'],
                            'userID__username': order['userID__username'],
                            'userID__NickName': order['userID__NickName'], 'channel': order['channel'],
                            'desc': order['desc'], 'price': order['price'], 'refunded_amount': order['refunded_amount'],
                            'currency': order['currency'], 'addTime': order['addTime'], 'updTime': order['updTime'],
                            'paypal': order['paypal'], 'payType': order['payType'], 'rank__day': order['rank__day'],
                            'rank__price': order['rank__price'], 'status': order['status'],
                            'order_type': order['order_type'],
                            'payTime': order['payTime'] if order['payTime'] else 'N/A',
                            'serialNumber': 'N/A' if serial_number == order['UID'] else serial_number,
                            'country': country, 'userID__phone': order['userID__phone'], 'trade_no': 'N/A'}
                    #  添加PayPal交易号字段
                    if data['payType'] == 1:
                        data['trade_no'] = order['trade_no'] if order['trade_no'] else 'N/A'
                    data['express_id'] = ''
                    if data['payType'] == 11:
                        cdk_context_qs = CDKcontextModel.objects.filter(order=data['orderID']).values('express_id')
                        if cdk_context_qs.exists():
                            data['express_id'] = cdk_context_qs[0]['express_id']
                    item['order_info'].append(data)
            return response.json(0, {'list': list(daily_reconciliation_qs), 'total': count})
        except Exception as e:
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def vodOrderReconcile(self, request, request_dict, response):

        file = request.FILES.get('file', None)
        if not all([file]):
            return response.json(444, {'error param': 'file'})

        try:
            rd_book = xlrd.open_workbook(filename=None, file_contents=file.read())
            rd_sheet = rd_book.sheet_by_index(0)
            date = rd_sheet.cell_value(1, 0)
            month = int(date.split('/')[0])
            year = int(date.split('/')[2])
            last_day = calendar.monthrange(year, month)[1]
            start_time = datetime.datetime(year, month, 1)
            end_time = datetime.datetime(year, month, last_day) + datetime.timedelta(hours=24)
            start_time = CommonService.str_to_timestamp(start_time.strftime('%Y-%m-%d %H:%M:%S'))
            end_time = CommonService.str_to_timestamp(end_time.strftime('%Y-%m-%d %H:%M:%S'))
            wt_book = xlwt.Workbook(encoding='utf-8')
            wt_sheet = wt_book.add_sheet('对账结果', cell_overwrite_ok=True)
            row_start = 0
            # 第一部分表格填写
            first_header = ['支付类型', '销售总金额', '销售总数量', '退款总额', '退款总数量', '应收金额', '平台手续费', '账务实收', '金额相差']
            for index, content in enumerate(first_header):
                wt_sheet.write(row_start, index, content)
            row_start += 1
            all_order_qs = Order_Model.objects.filter(addTime__gte=start_time, addTime__lt=end_time,
                                                      status__in=[1, 5, 6])
            trade_no_list = []
            paid_order_qs = all_order_qs.filter(status=1)
            for i in paid_order_qs:
                if i.trade_no not in rd_sheet.col_values(9, 1):
                    trade_no_list.append(i.trade_no)

            # 已付款订单
            paid = all_order_qs.filter(payType=1, status=1).aggregate(total=Sum('price'),
                                                                      count=Count('UID'))
            paid_total = paid['total'] if paid['total'] else 0
            paid_count = paid['count'] if paid['count'] else 0
            # 全额退款订单
            refund = all_order_qs.filter(payType=1, status__in=[5, 6]).aggregate(total=Sum('refunded_amount'),
                                                                                 count=Count('UID'))
            refund_total = refund['total'] if refund['total'] else 0
            refund_count = refund['count'] if refund['count'] else 0
            wt_sheet.write(row_start, 0, 'PayPal')
            wt_sheet.write(row_start, 1, paid_total + refund_total)
            wt_sheet.write(row_start, 2, paid_count + refund_count)
            wt_sheet.write(row_start, 3, refund_total)
            wt_sheet.write(row_start, 4, refund_count)
            wt_sheet.write(row_start, 5, paid_total)
            wt_sheet.write(row_start, 8, label=xlwt.Formula('f{row}-h{row}+g{row}'.format(row=row_start + 1)))
            row_start += 1
            #  第二部分表格填写
            row_start += 2
            paypal_money = 0
            fee_money = 0
            for row in range(rd_sheet.nrows):
                if row == 0:
                    wt_sheet.write(row_start, 0, '是否匹配账单')
                    for col in range(rd_sheet.ncols):
                        value = rd_sheet.cell_value(row, col)
                        temp_col = col + 1
                        wt_sheet.write(row_start, temp_col, str(value))
                    row_start += 1
                    continue
                if rd_sheet.cell_value(row, 0) == '合计':
                    break
                paypal_money += rd_sheet.cell_value(row, 7)
                fee_money += rd_sheet.cell_value(row, 6)
                transaction_id = rd_sheet.cell_value(row, 9)
                order_qs = all_order_qs.filter(trade_no=transaction_id)
                if not order_qs.exists():
                    col_value_list = rd_sheet.row_values(row)
                    col_value_list.insert(0, '否')
                    for index, value in enumerate(col_value_list):
                        wt_sheet.write(row_start, index, str(value))
                    row_start += 1

            # 第三部分表格填写
            row_start += 1
            third_header = ['是否匹配账单', '交易ID', '订单ID', '设备UID', '用户名', '账号昵称', '通道', '商品描述', '支付方式', '价格', '支付状态',
                            '已退金额', '添加时间', '更新时间']
            diff_order_qs = all_order_qs.filter(trade_no__in=trade_no_list).values('trade_no', 'orderID', 'UID',
                                                                                   'userID__username',
                                                                                   'userID__NickName', 'channel',
                                                                                   'desc', 'payType',
                                                                                   'price', 'status',
                                                                                   'refunded_amount', 'addTime',
                                                                                   'updTime')
            for index, content in enumerate(third_header):
                wt_sheet.write(row_start, index, content)
            row_start += 1
            for item in diff_order_qs:
                values_list = item.values()
                for col, content in enumerate(values_list):
                    if col == 0:
                        wt_sheet.write(row_start, col, '否')
                    if col == 7:
                        content = 'PayPal'
                    if col == 9:
                        if content == 1:
                            content = '支付成功'
                        elif content == 5:
                            content = '全额退款'
                        elif content == 6:
                            content = '部分退款'
                    if col in [11, 12]:
                        content = CommonService.timestamp_to_str(int(content))
                    wt_sheet.write(row_start, col + 1, str(content))
                row_start += 1
            wt_sheet.write(1, 6, fee_money)
            wt_sheet.write(1, 7, paypal_money)

            res = HttpResponse(content_type='application/vnd.ms-excel')
            res['Content-Disposition'] = 'attachment; filename={}'.format(escape_uri_path(file.name))
            wt_book.save(res)
            return res
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def deleteDeviceOrder(self, userID, request_dict, response):
        orderID = request_dict.get('orderID', None)

        if orderID:
            Order_Model.objects.filter(orderID=orderID).delete()
            return response.json(0)
        else:
            return response.json(444)

    def getDevicePackageList(self, request_dict, response):
        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)
        uid = request_dict.get('uid', None)
        if not all([pageNo, pageSize]):
            return response.json(444)

        page = int(pageNo)
        line = int(pageSize)
        try:
            ubqs = UID_Bucket.objects.all()
            if uid:
                ubqs = ubqs.filter(uid__contains=uid)

            if not ubqs.exists():
                return response.json(0, [])

            count = ubqs.count()
            ubqs = ubqs.values(
                'id',
                'uid',
                'channel',
                'status',
                'endTime',
                'bucket__bucket',
                'bucket__storeDay',
                'bucket__area')

            ubqs = ubqs.order_by('-addTime')  # 根据CDK创建时间降序排序
            ubqs = ubqs[(page - 1) * line:page * line]
            return response.json(
                0, {'list': list(ubqs), 'total': count})
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def deleteDevicePackage(self, userID, request_dict, response):
        orderID = request_dict.get('orderID', None)

        if orderID:
            Order_Model.objects.filter(orderID=orderID).delete()
            return response.json(0)
        else:
            return response.json(444)

    # 重置设备云存体验
    def do_experience_reset(self, request_dict, userID, response):
        bid = request_dict.get("id", None)
        ubq = UID_Bucket.objects.filter(id=bid)
        if ubq:
            eq = ExperienceContextModel.objects.filter(uid=ubq[0].uid)
            if eq:
                eq.delete()
                # Order_Model.objects.filter(uid_bucket_id=bid).delete()
                ubq.delete()
                return response.json(0)
            else:
                return response.json(10007)

        else:
            return response.json(0, '重置云存体验失败')

    def meal_transfer(self, request, request_dict, response):
        orderID = request_dict.get("orderID")
        old_uid = request_dict.get("old_uid")
        new_uid = request_dict.get("new_uid")
        nowTime = int(time.time())

        # 判断两个设备是否在同一账号下
        old_uid_userIDs = set(Device_Info.objects.filter(UID=old_uid).values_list('userID_id', flat=True))
        new_uid_userIDs = set(Device_Info.objects.filter(UID=new_uid).values_list('userID_id', flat=True))

        # 检查两个集合是否有交集
        if not old_uid_userIDs.intersection(new_uid_userIDs):
            if not new_uid_userIDs:
                return response.json(10010)

        # 查订单表查看订单类型
        order_type = Order_Model.objects.values_list('order_type', flat=True).get(orderID=orderID)
        if order_type not in [0, 1]:
            return response.json(10017)

        # 查老设备 orderID
        old_using_uid_bucket = UID_Bucket.objects.filter(orderId=orderID, endTime__gte=nowTime)
        old_unusing_uid_bucket = Unused_Uid_Meal.objects.filter(order_id=orderID)

        if not old_using_uid_bucket.exists() and not old_unusing_uid_bucket.exists():
            return response.json(10017)

        # 订单套餐未使用
        if old_unusing_uid_bucket.exists():
            with transaction.atomic():
                # 旧设备不包含 ai
                if old_unusing_uid_bucket.filter(is_ai=1).exists():
                    new_uid_set = UidSetModel.objects.filter(uid=new_uid).values('is_ai')
                    if new_uid_set[0]['is_ai'] == 2:
                        return response.json(10016)
                old_unusing_uid_bucket.update(uid=new_uid, addTime=nowTime)
                new_unused_uid_bucket = Unused_Uid_Meal.objects.filter(uid=new_uid, addTime=nowTime).values(
                    'id', 'channel', 'bucket_id', 'addTime', 'expire', 'is_ai', 'order_id'
                )

                # 转移后如果没有未使用套餐则把 has_unused 改为 0
                unused_uid_meal_qs = Unused_Uid_Meal.objects.filter(uid=old_uid)
                if not unused_uid_meal_qs.exists():
                    UID_Bucket.objects.filter(uid=old_uid).update(has_unused=0)

                # 判断新设备是否有正在使用的套餐
                new_uid_bucket_qs = UID_Bucket.objects.filter(uid=new_uid, endTime__gte=nowTime, use_status=1)
                if new_uid_bucket_qs.exists():
                    new_uid_bucket_qs.update(has_unused=1)
                else:
                    # 激活转移的套餐
                    unused = new_unused_uid_bucket[0]
                    endTime = CommonService.calcMonthLater(unused['expire'])
                    # 判断是否有已过期套餐
                    if UID_Bucket.objects.filter(uid=new_uid).exists():
                        UID_Bucket.objects.filter(uid=new_uid).update(status=1, orderId=orderID, updateTime=nowTime,
                                                                      use_status=1, has_unused=0, endTime=endTime,
                                                                      bucket_id=unused['bucket_id'], channel=unused['channel'],
                                                                      addTime=unused['addTime'])
                    data_dict = {
                        'uid': new_uid,
                        'channel': unused['channel'],
                        'bucket_id': unused['bucket_id'],
                        'addTime': unused['addTime'],
                        'endTime': endTime,
                        'updateTime': nowTime,
                        'status': 1,
                        'use_status': 1,
                        'has_unused': 0,
                        'orderId': unused['order_id']
                    }
                    UID_Bucket.objects.create(**data_dict)  # 正在使用套餐表添加数据
                    if unused['is_ai']:  # 开通AI服务
                        new_aiservice = AiService.objects.filter(uid=new_uid)
                        if new_aiservice.filter(use_status=1 or 0).exists():
                            return response.json(10014)
                        if new_aiservice.exists():
                            new_aiservice.update(channel=unused['channel'], detect_status=1, addTime=nowTime,
                                                 updTime=nowTime, endTime=endTime, use_status=1, orders_id=unused['order_id'])
                        else:
                            AiService.objects.create(uid=new_uid, channel=unused['channel'], detect_status=1, addTime=nowTime,
                                                 updTime=nowTime, endTime=endTime, use_status=1, orders_id=unused['order_id'])

                    Unused_Uid_Meal.objects.filter(order_id=orderID).first().delete()

                # 修改订单表
                Order_Model.objects.filter(orderID=orderID).update(UID=new_uid, updTime=nowTime)

        # 订单套餐已使用
        else:
            old_using_uid_bucket = old_using_uid_bucket.values('id', 'bucket_id', 'has_unused', 'bucket__content').order_by('addTime')
            old_ai_service = AiService.objects.filter(uid=old_uid, endTime__gte=nowTime, use_status=1).values('id',
                                                                                                              'detect_interval',
                                                                                                              'detect_status',
                                                                                                              'detect_group')
            # new_ai_service = AiService.objects.filter(uid=new_uid, endTime__gte=nowTime, use_status=1)
            # if new_ai_service.exists():  # 转入设备有开通AI功能，不能转
            #     return response.json(10014)

            new_uid_set = UidSetModel.objects.filter(uid=new_uid).values('is_ai')
            if old_ai_service.exists() and new_uid_set[0]['is_ai'] == 2:  # 转出设备有开通AI，但是转入设备不支持AI，不能转
                return response.json(10016)

            # 查询转入设备是否存在正在使用的套餐
            new_using_uid_bucket = UID_Bucket.objects.filter(uid=new_uid, endTime__gte=nowTime).first()
            is_delete = 0
            if new_using_uid_bucket:
                vod_bucket = VodBucketModel.objects.get(id=new_using_uid_bucket.bucket_id)
                if vod_bucket.is_free:
                    # 是免费套餐则删除
                    is_delete = 1
                else:
                    # 非免费套餐不可转移
                    return response.json(10014)
            with transaction.atomic():
                if is_delete == 1:
                    if AiService.objects.filter(orders_id=orderID).exists():
                        ai_service = AiService.objects.filter(uid=old_uid).first()
                        if ai_service:
                            ai_service.delete()
                    new_using_uid_bucket.delete()
                # 如果转出设备有未使用套餐,更改第一个未使用套餐为正在使用
                if old_using_uid_bucket[0]['has_unused'] == 1:
                    old_unused_uid_bucket = Unused_Uid_Meal.objects.filter(uid=old_uid).values('id', 'channel',
                                                                                               'bucket_id',
                                                                                               'addTime', 'expire',
                                                                                               'is_ai',
                                                                                               'order_id')
                    count = old_unused_uid_bucket.count()
                    unused = old_unused_uid_bucket[0]
                    has_unused = 1 if count > 1 else 0  # 如果存在不止一个未使用套餐,has_unused=1
                    endTime = CommonService.calcMonthLater(unused['expire'])
                    data_dict = {
                        'uid': old_uid,
                        'channel': unused['channel'],
                        'bucket_id': unused['bucket_id'],
                        'addTime': unused['addTime'],
                        'endTime': endTime,
                        'updateTime': nowTime,
                        'status': 1,
                        'use_status': 1,
                        'has_unused': has_unused,
                        'orderId': unused['order_id']
                    }
                    UID_Bucket.objects.create(**data_dict)  # 正在使用套餐表添加数据
                    Unused_Uid_Meal.objects.filter(uid=old_uid).first().delete()  # 删除未使用套餐表中的数据
                    if unused['is_ai']:  # 开通AI服务
                        AiService.objects.create(uid=old_uid, channel=unused['channel'],
                                                 detect_status=old_ai_service[0]['detect_status'],
                                                 addTime=nowTime, updTime=nowTime, endTime=endTime, use_status=1,
                                                 orders_id=unused['order_id'],
                                                 detect_group=old_ai_service[0]['detect_group'],
                                                 detect_interval=old_ai_service[0]['detect_interval'])

                # 更新正在使用套餐的uid,重置拥有未使用套餐
                old_using_uid_bucket_id = old_using_uid_bucket[0]['id']
                UID_Bucket.objects.filter(id=old_using_uid_bucket_id).update(uid=new_uid, has_unused=0)
                StsCrdModel.objects.filter(uid=old_uid).delete()  # 删除转出设备stscrd表关联数据

                # 转移AI服务
                if old_ai_service.exists() and new_uid_set[0]['is_ai'] != 2:
                    AiService.objects.filter(id=old_ai_service[0]['id']).update(uid=new_uid, detect_status=0,
                                                                                detect_group='', detect_interval=60)
                    msg = {'commandType': 'AIDisable'}
                    thing_name = CommonService.query_serial_with_uid(old_uid)  # 存在序列号则为使用序列号作为物品名
                    topic_name = 'ansjer/generic/{}'.format(thing_name)
                    req_success = CommonService.req_publish_mqtt_msg(thing_name, topic_name, msg)
                    if not req_success:
                        return response.json(10044)
                # 修改订单表
                Order_Model.objects.filter(orderID=orderID).update(UID=new_uid, updTime=nowTime)

        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': 'cloudTransfer/mealTransfer',
            'content': json.dumps(content),
            'operation': f'订单号:{orderID}套餐从设备{old_uid}转移到设备{new_uid}'
        }
        LogModel.objects.create(**log)
        return response.json(0)

    @classmethod
    def getCloudUserList(cls, request_dict, response):
        print('request_dict: ', request_dict)

        # UID_Bucket表查询数据
        uid = request_dict.get('uid', None)
        status = request_dict.get('status', None)
        use_status = request_dict.get('use_status', None)
        has_unused = request_dict.get('has_unused', None)
        addTimeRange = request_dict.getlist('addTimeRange[]', None)
        endTimeRange = request_dict.getlist('endTimeRange[]', None)

        # Order_Model表查询数据
        username = request_dict.get('username', None)
        phone = request_dict.get('phone', None)
        userEmail = request_dict.get('userEmail', None)
        payType = request_dict.get('payType', None)

        # uid_set 表查询
        ucode = request_dict.getlist('ucode', None)
        version = request_dict.getlist('version', None)

        # 日志表查询
        logTimeRange = request_dict.getlist('logTimeRange[]', None)

        # 序列号查询
        serialNumber = request_dict.get('serialNumber', None)

        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)
        if not all([pageNo, pageSize]):
            return response.json(444)
        page = int(pageNo)
        line = int(pageSize)

        try:
            uid_bucket_qs = UID_Bucket.objects.all()
            if uid:
                uid_bucket_qs = uid_bucket_qs.filter(uid__icontains=uid)
            if status:
                uid_bucket_qs = uid_bucket_qs.filter(status=status)
            if use_status:
                uid_bucket_qs = uid_bucket_qs.filter(use_status=use_status)
            if has_unused:
                uid_bucket_qs = uid_bucket_qs.filter(has_unused=has_unused)
            if serialNumber:
                device_info_qs = Device_Info.objects.filter(serial_number=serialNumber).values('UID')
                uid = device_info_qs[0]['UID'] if device_info_qs.exists() else 'N/A'
                uid_bucket_qs = uid_bucket_qs.filter(uid__icontains=uid)
            if addTimeRange:
                addStartTime, addEndTime = int(
                    addTimeRange[0][:-3]), int(addTimeRange[1][:-3])
                uid_bucket_qs = uid_bucket_qs.filter(
                    addTime__gte=addStartTime,
                    addTime__lte=addEndTime)
            if endTimeRange:
                endStartTime, endEndTime = int(
                    endTimeRange[0][:-3]), int(endTimeRange[1][:-3])
                uid_bucket_qs = uid_bucket_qs.filter(
                    addTime__gte=endStartTime,
                    addTime__lte=endEndTime)

            uid_list = []
            uid_set_dict = {}
            if ucode and ucode != ['']:
                uid_set_qs = UidSetModel.objects.filter(ucode__in=ucode).values('uid', 'ucode', 'version').distinct()
                for uid_set in uid_set_qs:
                    uid_list.append(uid_set['uid'])
                    uid_set_dict[uid_set['uid']] = {
                        'ucode': uid_set['ucode'],
                        'version': uid_set['version']
                    }
                uid_bucket_qs = uid_bucket_qs.filter(uid__in=uid_list)
            else:
                uid_set_qs = UidSetModel.objects.filter().values('uid', 'ucode', 'version').distinct()
                for uid_set in uid_set_qs:
                    uid_list.append(uid_set['uid'])
                    uid_set_dict[uid_set['uid']] = {
                        'ucode': uid_set['ucode'],
                        'version': uid_set['version']
                    }

            if not uid_bucket_qs.exists():
                return response.json(0, [])

            order_qs = Order_Model.objects.filter(uid_bucket_id__in=uid_bucket_qs.values('id'))
            if username or phone or userEmail or payType:
                if username:
                    order_qs = order_qs.filter(userID__username=username)
                if phone:
                    order_qs = order_qs.filter(userID__phone__contains=phone)
                if userEmail:
                    order_qs = order_qs.filter(
                        userID__userEmail__contains=userEmail)
                if payType:
                    order_qs = order_qs.filter(payType=int(payType))
                # 过滤套餐关联的UID_Bucket数据
                uid_bucket_qs = uid_bucket_qs.filter(
                    id__in=order_qs.values_list(
                        'uid_bucket_id', flat=True))

            cg_qs = CloudLogModel.objects.filter(
                operation='cloudstorage/queryvodlist')
            if logTimeRange:
                logStartTime, logEndTime = int(
                    logTimeRange[0][:-3]), int(logTimeRange[1][:-3])
                cg_qs = cg_qs.filter(
                    time__gte=logStartTime,
                    time__lte=logEndTime)
                # 过滤套餐关联的UID_Bucket数据
                uid_bucket_qs = uid_bucket_qs.filter(
                    uid__in=cg_qs.values('uid'))

            list_data = []
            count = uid_bucket_qs.count()
            uid_bucket_qs = uid_bucket_qs.order_by('-addTime')[(page - 1) * line:page * line]

            for uid_bucket in uid_bucket_qs:
                for order in order_qs.filter(uid_bucket_id=uid_bucket.id) \
                        .values('orderID', 'uid_bucket_id', 'desc', 'userID__userID',
                                'UID', 'price', 'payType', 'userID__username', 'userID__phone',
                                'userID__userEmail', 'userID__data_joined', 'agreement_id'):
                    # 套餐到期时间累加未使用套餐
                    unused_qs = Unused_Uid_Meal.objects.filter(uid=uid_bucket.uid).values('expire')
                    if unused_qs.exists():
                        addMonth = 0
                        for unused in unused_qs:
                            addMonth += unused['expire']
                        endTime = CommonService.calcMonthLater(addMonth, uid_bucket.endTime)
                        endTime = time.strftime("%Y--%m--%d %H:%M:%S", time.localtime(endTime))
                    else:
                        endTime = time.strftime("%Y--%m--%d %H:%M:%S", time.localtime(uid_bucket.endTime))
                    uid = uid_bucket.uid.upper()
                    data = {
                        'id': uid_bucket.id,
                        'orderId': order['orderID'],
                        'uid': uid,
                        'agreementId': order['agreement_id'] if order['agreement_id'] else 'N/A',
                        'channel': uid_bucket.channel,
                        'status': uid_bucket.status,
                        'endTime': endTime,
                        'addTime': time.strftime("%Y--%m--%d %H:%M:%S", time.localtime(uid_bucket.addTime)),
                        'use_status': uid_bucket.use_status,
                        'has_unused': uid_bucket.has_unused,
                        'desc': order['desc'],
                        'payType': order['payType'],
                        'price': order['price'],
                        'username': order['userID__username'],
                        'userID': order['userID__userID'],
                        'phone': order['userID__phone'],
                        'userEmail': order['userID__userEmail'],
                        'data_joined': order['userID__data_joined'].strftime("%Y-%m-%d %H:%M:%S"),
                        'playcount': cg_qs.filter(operation='cloudstorage/queryvodlist', uid=order['UID']).count(),
                        'serial_number': 'N/A'
                    }
                    device_info_qs = Device_Info.objects.filter(UID=uid).values('serial_number')
                    if device_info_qs.exists():
                        data['serial_number'] = device_info_qs[0]['serial_number'] if device_info_qs[0][
                            'serial_number'] else 'N/A'
                    if uid in uid_set_dict:
                        data['ucode'] = uid_set_dict[uid]['ucode']
                        data['version'] = uid_set_dict[uid]['version']

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

    def exportCloudUserList(self, request_dict, response):
        # UID_Bucket表查询数据
        uid = request_dict.get('uid', None)
        status = request_dict.get('status', None)
        use_status = request_dict.get('use_status', None)
        has_unused = request_dict.get('has_unused', None)
        addTimeRange = request_dict.getlist('addTimeRange[]', None)
        endTimeRange = request_dict.getlist('endTimeRange[]', None)

        # Order_Model表查询数据
        username = request_dict.get('username', None)
        phone = request_dict.get('phone', None)
        userEmail = request_dict.get('userEmail', None)
        payType = request_dict.get('payType', None)

        # uid_set 表查询
        ucode = request_dict.getlist('ucode', None)
        version = request_dict.getlist('version', None)

        # 日志表查询
        logTimeRange = request_dict.getlist('logTimeRange[]', None)

        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)
        if not all([pageNo, pageSize]):
            return response.json(444)
        page = int(pageNo)
        line = int(pageSize)

        try:
            uid_bucket_qs = UID_Bucket.objects.all()
            if uid:
                uid_bucket_qs = uid_bucket_qs.filter(uid__contains=uid)
            if status:
                uid_bucket_qs = uid_bucket_qs.filter(status=status)
            if use_status:
                uid_bucket_qs = uid_bucket_qs.filter(use_status=use_status)
            if has_unused:
                uid_bucket_qs = uid_bucket_qs.filter(has_unused=has_unused)
            if addTimeRange:
                addStartTime, addEndTime = int(
                    addTimeRange[0][:-3]), int(addTimeRange[1][:-3])
                uid_bucket_qs = uid_bucket_qs.filter(
                    addTime__gte=addStartTime,
                    addTime__lte=addEndTime)
            if endTimeRange:
                endStartTime, endEndTime = int(
                    endTimeRange[0][:-3]), int(endTimeRange[1][:-3])
                uid_bucket_qs = uid_bucket_qs.filter(
                    addTime__gte=endStartTime,
                    addTime__lte=endEndTime)
            if not uid_bucket_qs.exists():
                return response.json(0, [])

            order_qs = Order_Model.objects.filter(
                uid_bucket_id__in=uid_bucket_qs.values('id'))
            if username or phone or userEmail or payType:
                if username:
                    order_qs = order_qs.filter(userID__username=username)
                if phone:
                    order_qs = order_qs.filter(userID__phone__contains=phone)
                if userEmail:
                    order_qs = order_qs.filter(
                        userID__userEmail__contains=userEmail)
                if payType:
                    order_qs = order_qs.filter(payType=int(payType))
                # 过滤套餐关联的UID_Bucket数据
                uid_bucket_qs = uid_bucket_qs.filter(
                    id__in=order_qs.values_list(
                        'uid_bucket_id', flat=True))

            uidset_qs = UidSetModel.objects.filter(
                uid__in=uid_bucket_qs.values('uid'))
            if ucode or version:
                if ucode:
                    uidset_qs = uidset_qs.filter(ucode=ucode)
                if version:
                    uidset_qs = uidset_qs.filter(version=version)

            cg_qs = CloudLogModel.objects.filter(
                operation='cloudstorage/queryvodlist')
            if logTimeRange:
                logStartTime, logEndTime = int(
                    logTimeRange[0][:-3]), int(logTimeRange[1][:-3])
                cg_qs = cg_qs.filter(
                    time__gte=logStartTime,
                    time__lte=logEndTime)
            list_data = []
            count = uid_bucket_qs.count()
            uid_bucket_qs = uid_bucket_qs.order_by('-addTime')[(page - 1) * line:page * line]
            for uid_bucket in uid_bucket_qs:
                data = {
                    'id': uid_bucket.id,
                    'uid': uid_bucket.uid,
                    'channel': uid_bucket.channel,
                    'status': uid_bucket.status,
                    'endTime': time.strftime(
                        "%Y--%m--%d %H:%M:%S",
                        time.localtime(
                            uid_bucket.endTime)),
                    'addTime': time.strftime(
                        "%Y--%m--%d %H:%M:%S",
                        time.localtime(
                            uid_bucket.addTime)),
                    'use_status': uid_bucket.use_status,
                    'has_unused': uid_bucket.has_unused}

                for order in order_qs.filter(
                        uid_bucket_id=uid_bucket.id).values(
                    'uid_bucket_id',
                    'desc',
                    'userID__userID',
                    'UID',
                    'price',
                    'payType',
                    'userID__username',
                    'userID__phone',
                    'userID__userEmail',
                    'userID__data_joined'):
                    data['desc'] = order['desc']
                    data['payType'] = order['payType']
                    data['price'] = order['price']
                    data['username'] = order['userID__username']
                    data['phone'] = order['userID__phone']
                    data['userEmail'] = order['userID__userEmail']
                    data['data_joined'] = order['userID__data_joined'].strftime(
                        "%Y-%m-%d %H:%M:%S")
                    data['playcount'] = cg_qs.filter(
                        operation='cloudstorage/queryvodlist', uid=order['UID']).count()

                for uidset in uidset_qs.filter(
                        uid=uid_bucket.uid).values(
                    'ucode',
                    'version'):
                    data['ucode'] = uidset['ucode']
                    data['version'] = uidset['version']

                list_data.append(data)

            response = HttpResponse(content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=userinfo.xls'
            workbook = xlwt.Workbook(encoding='utf-8')
            sheet1 = workbook.add_sheet('UID')

            headtitle = [
                'id',
                '用户账号',
                '用户手机号',
                '用户邮箱',
                '注册时间',
                '设备UID',
                '设备通道',
                '云存状态',
                '添加时间',
                '到期时间',
                '使用状态',
                '是否有未使用套餐',
                '套餐描述',
                '支付方式',
                '价格',
                '播放次数',
                '产品编码',
                '版本'
            ]
            headnum = 0
            for title in headtitle:
                sheet1.write(0, headnum, title)
                headnum = headnum + 1

            fields = [
                'id',
                'username',
                'phone',
                'userEmail',
                'data_joined',
                'uid',
                'channel',
                'status',
                'addTime',
                'endTime',
                'use_status',
                'has_unused',
                'desc',
                'payType',
                'price',
                'playcount',
                'ucode',
                'version'
            ]
            num = 1
            for item in list_data:
                fieldnum = 0
                for key in fields:
                    val = item[key]
                    if key == 'payType':
                        if val == 1:
                            val = 'PayPal'
                        if val == 2:
                            val = '支付宝'
                        if val == 3:
                            val = '微信支付'
                        if val == 10:
                            val = '免费体验'
                        if val == 11:
                            val = '激活码'

                    sheet1.write(num, fieldnum, val)
                    fieldnum = fieldnum + 1
                num = num + 1
            workbook.save(response)
            return response
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def getCloudDataList(self, request_dict, response):
        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 = []
        vod_bucket_qs = VodBucketModel.objects.filter()
        if not vod_bucket_qs.exists():
            return response.json(173)
        try:
            for vod_bucket in vod_bucket_qs:
                vod_bucket_id = vod_bucket.id
                store_meal = Store_Meal.objects.filter(bucket_id=vod_bucket_id, lang__lang='cn').values('lang__title',
                                                                                                        'lang__content')
                if not store_meal.exists():
                    continue
                name = store_meal[0]['lang__title'] + '-' + store_meal[0]['lang__content']
                order = Order_Model.objects.filter(rank__bucket_id=vod_bucket_id)
                Jan_count = order.filter(status=1, addTime__range=[Jan, Feb]).count()
                Feb_count = order.filter(status=1, addTime__range=[Feb, Mar]).count()
                Mar_count = order.filter(status=1, addTime__range=[Mar, Apr]).count()
                Apr_count = order.filter(status=1, addTime__range=[Apr, May]).count()
                May_count = order.filter(status=1, addTime__range=[May, Jun]).count()
                Jun_count = order.filter(status=1, addTime__range=[Jun, Jul]).count()
                Jul_count = order.filter(status=1, addTime__range=[Jul, Aug]).count()
                Aug_count = order.filter(status=1, addTime__range=[Aug, Sep]).count()
                Sep_count = order.filter(status=1, addTime__range=[Sep, Oct]).count()
                Oct_count = order.filter(status=1, addTime__range=[Oct, Nov]).count()
                Nov_count = order.filter(status=1, addTime__range=[Nov, Dec]).count()
                Dec_count = order.filter(status=1, addTime__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 deviceAttritionAlert(cls, request_dict, response):
        """
        流失预警界面
        @param request_dict:
        @param response:
        @param userName:用户名
        @param uid:设备uid
        @param grade:预警等级
        """
        userName = request_dict.get('userName', None)
        uid = request_dict.get('uid', None)
        grade = request_dict.get('grade', None)
        pageNo = request_dict.get('pageNo', None)
        pageSize = request_dict.get('pageSize', None)
        if not all({pageNo, pageSize}):
            return response.json(444)
        # 获取云存套餐信息表内的设备uid
        page = int(pageNo)
        line = int(pageSize)
        if userName == '' or uid == '' or grade == '':  # 判断是否为空
            if userName == '':
                userName = None
            if uid == '':
                uid = None
            if grade == '':
                grade = None
        para_list = [userName, uid, grade]
        not_upload_list = []
        para_count = para_list.count(None)
        try:
            # 筛选数据
            cursor = connection.cursor()
            sql = "SELECT t1.userID_id, du.username, t1.uid, t1.addTime, t1.use_status, t1.`day`, t1.grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day`, t1.grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day`,(CASE WHEN t1.`day` >= '15' AND t1.`day` < '25' THEN '一号预警' WHEN t1.use_status = '1' AND t1.`day` >= '25' THEN '二号预警' WHEN t1.use_status = 2 AND t1.`day` >= '25' THEN '八号预警' ELSE '预警取消' END ) AS grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day` FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, TIMESTAMPDIFF(DAY,FROM_UNIXTIME(t1.addTime, '%Y-%m-%d' ),DATE_FORMAT( NOW(), '%Y-%m-%d' )) AS 'day' FROM (SELECT di.userID_id, vub.uid, vub.addTime, vub.use_status FROM vod_uid_bucket AS vub, device_info AS di WHERE vub.uid = di.UID AND vub.addTime > '1669824000' GROUP BY di.userID_id) AS t1 WHERE t1.uid NOT IN (SELECT uid FROM uid_cloud_storage_count)) AS t1 WHERE t1.`day`>='15' AND t1.userID_id is NOT NULL) AS t1 ORDER BY t1.userID_id)AS t1 WHERE t1.userID_id NOT IN (SELECT di.userID_id FROM `uid_cloud_storage_count` AS vcsc LEFT JOIN device_info AS di ON vcsc.uid = di.UID WHERE di.userID_id is NOT NULL))AS t1 LEFT JOIN device_user AS du ON t1.userID_id = du.userID order by t1.addTime "
            cursor.execute(sql)
            uid_type_tuple = cursor.fetchall()
            total = len(uid_type_tuple)
            cursor.close()  # 执行完，关闭
            connection.close()
            result_list = []
            more_list = []  # 多个参数查询结果
            new_list = []  # 单独一个参数查询结果
            col_names = [desc[0] for desc in cursor.description]
            for uid_type in uid_type_tuple:
                uid_dict = dict(zip(col_names, uid_type))
                result_list.append(uid_dict)
            if para_count != 2 and para_count != 3:  # 两个及以上参数进行筛查
                if userName and uid and grade == None:
                    for result in result_list:
                        if result['username'] == userName and result['uid'] == uid:
                            more_list.append(result)
                if userName and grade and uid == None:
                    for result in result_list:
                        if result['username'] == userName and result['grade'] == grade:
                            more_list.append(result)
                if uid and grade and userName == None:
                    for result in result_list:
                        if result['uid'] == uid and result['grade'] == grade:
                            more_list.append(result)
                if userName and uid and grade:
                    for result in result_list:
                        if result['username'] == userName and result['uid'] == uid and result['grade'] == grade:
                            more_list.append(result)
                result_list = more_list
            if para_count == 2:  # 一个参数进行筛查
                if userName:
                    for result in result_list:
                        if result['username'] == userName:
                            new_list.append(result)
                if uid:
                    for result in result_list:
                        if result['uid'] == uid:
                            new_list.append(result)
                if grade:
                    for result in result_list:
                        if result['grade'] == grade:
                            new_list.append(result)
                result_list = new_list
            else:
                result_list = result_list
            result_list = result_list[(page - 1) * line:page * line]
            for item in result_list:  # 获取账号下的云存设备数量
                userID_id = item['userID_id']
                device_info_qs = Device_Info.objects.filter(userID_id=userID_id).annotate(count=Count('UID')).values(
                    'UID')
                count = device_info_qs.count()
                if count == 1 or count == 0:
                    item['device_number'] = count
                else:
                    device_number = 0
                    for device_info in device_info_qs:
                        uid = device_info['UID']
                        uid_set_qs = UidSetModel.objects.filter(uid=uid).values('ucode', 'device_type')
                        ucode = uid_set_qs[0]['ucode'] if uid_set_qs.exists() else ''
                        device_type = uid_set_qs[0]['device_type'] if uid_set_qs.exists() else ''
                        cloud_vod = CommonService.is_cloud_device(ucode, device_type)
                        #  判断设备是否为ipc设备和是否支持云存
                        if cloud_vod:
                            device_number += 1
                    item['device_number'] = device_number
                item['other_device'] = 0
                not_upload_list.append(item)
            return response.json(0, {'result_list': not_upload_list, 'total': total})
        except Exception as e:
            meg = '异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e))
            return response.json(500, meg)

    def deactivationPackage(self, request_dict, response):
        """
        停用套餐
        @param request_dict:
        @param response:
        """
        uid = request_dict.get('uid', None)
        orderID = request_dict.get('orderID', None)
        orderType = request_dict.get('orderType', None)
        payType = request_dict.get('payType', None)
        userName = request_dict.get('userName', None)
        # 查询订单
        if not all([orderID, orderType, uid, userName]):
            return response.json(444)
        nowTime = int(time.time())
        try:
            with transaction.atomic():
                #  云存套餐
                if orderType == '0' and payType != '10':
                    AiService.objects.filter(orders=orderID).delete()  # 删除AI服务
                    order_qs = Order_Model.objects.filter(orderID=orderID, userID__username=userName, UID=uid).values(
                        'rank__bucket_id', 'payTime', 'rank__expire')
                    if not order_qs.exists():
                        return response.json(14)
                    pay_time = order_qs[0]['payTime']
                    bucket_id = order_qs[0]['rank__bucket_id']
                    expire = order_qs[0]['rank__expire']
                    unused_uid_Meal_qs = Unused_Uid_Meal.objects.filter(order_id=orderID)
                    if unused_uid_Meal_qs.exists():  # 如果能通过订单id查询到未使用套餐则删除套餐
                        unused_uid_Meal_qs.delete()
                        unused_meal_qs = Unused_Uid_Meal.objects.filter(uid=uid)
                        if not unused_meal_qs.exists():
                            UID_Bucket.objects.filter(uid=uid).update(has_unused=0, updateTime=nowTime)
                        return response.json(0)
                    unused_uid_Meal_qs = Unused_Uid_Meal.objects.filter(uid=uid, bucket_id=bucket_id, order_id='')
                    if unused_uid_Meal_qs.exists():  # 如果通过uid查询到未使用套餐则删除套餐
                        unused_uid_Meal_qs.first().delete()
                        unused_meal_qs = Unused_Uid_Meal.objects.filter(uid=uid)
                        if not unused_meal_qs.exists():
                            UID_Bucket.objects.filter(uid=uid).update(has_unused=0, updateTime=nowTime)
                        return response.json(0)
                    uid_bucket_qs = UID_Bucket.objects.filter(uid=uid, use_status=1, orderId=orderID)
                    if uid_bucket_qs.exists():  # 如果通过订单号查询正在使用的套餐，则删除套餐
                        uid_bucket_qs.update(use_status=2, endTime=nowTime, updateTime=nowTime)
                        return response.json(0)
                    uid_bucket_qs = UID_Bucket.objects.filter(uid=uid, use_status=1, orderId='').values('endTime')
                    if uid_bucket_qs.exists():  # 如果通过uid查询正在使用的套餐，则更新过期时间和使用状态
                        end_time = pay_time + uid_bucket_qs[0]['endTime'] - CommonService.calcMonthLater(expire,
                                                                                                         pay_time)
                        use_status = 2 if end_time < nowTime else 1
                        uid_bucket_qs.update(endTime=end_time, use_status=use_status, updateTime=nowTime)
                        return response.json(0)
                    return response.json(10059)  # 未使用套餐类型重复
                #  AI套餐
                if orderType == '1' and payType != '10':
                    return response.json(10059)
                    # order_qs = Order_Model.objects.filter(orderID=orderID, userID__username=userName, UID=uid).values(
                    #     'ai_rank_id')
                    # order_qs = order_qs.objects.filter(UID=uid)
                    # ai_service_qs = AiService.objects.filter(uid=uid, use_status=1).values('addTime', 'endTime')
                    # ai_service_number = ai_service_qs.count()
                    # if ai_service_number <= 1:
                    #     ai_service_qs.update(endTime=nowTime)
                    #     return response.json(0)
                    # # 当设备套餐不唯一时
                    # ai_service_qs = AiService.objects.filter(uid=uid, bucket_id=order_qs[0]['ai_rank_id'])
                    # ai_serverice_number = ai_service_qs.count()
                    # if ai_serverice_number == 1:
                    #     ai_service_qs.update(endTime=nowTime)
                    #     return response.json(0)
                    # return response.json(10059)  # 未使用套餐类型重复
                #  联通4G套餐
                if orderType == '2' and payType != '10':
                    now_time = int(time.time())
                    combo_info_qs = UnicomComboOrderInfo.objects.filter(~Q(status=2), order_id=orderID)
                    if not combo_info_qs.exists():
                        return response.json(10059)
                    iccid = combo_info_qs.first().iccid
                    combo_info_qs.update(status=2, updated_time=now_time)
                    combo_info_qs = UnicomComboOrderInfo.objects.filter(status=1)
                    if combo_info_qs:
                        return response.json(10059)
                    unicom_api = UnicomObjeect()
                    usage_flow = unicom_api.get_flow_usage_total(iccid)
                    today = datetime.datetime.today()
                    year = today.year
                    month = today.month
                    task_view = UnicomComboTaskView()
                    result = task_view.query_unused_combo_and_activate(iccid, year, month, usage_flow)
                    if not result:
                        # 停用设备
                        unicom_api.change_device_to_disable(iccid)
                    else:
                        unicom_api.change_device_to_activate(iccid)
                    return response.json(0)
                return response.json(173)
        except Exception as e:
            print(e)
            return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))

    def cloudDataExport(self, request_dict, response):
        """
        流失预警导出功能
        """
        cursor = connection.cursor()
        sql = "SELECT t1.userID_id, du.username, t1.uid, t1.addTime, t1.use_status, t1.`day`, t1.grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day`, t1.grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day`,(CASE WHEN t1.`day` >= '15' AND t1.`day` < '25' THEN '一号预警' WHEN t1.use_status = '1' AND t1.`day` >= '25' THEN '二号预警' WHEN t1.use_status = 2 AND t1.`day` >= '25' THEN '八号预警' ELSE '预警取消' END ) AS grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day` FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, TIMESTAMPDIFF(DAY,FROM_UNIXTIME(t1.addTime, '%Y-%m-%d' ),DATE_FORMAT( NOW(), '%Y-%m-%d' )) AS 'day' FROM (SELECT di.userID_id, vub.uid, vub.addTime, vub.use_status FROM vod_uid_bucket AS vub, device_info AS di WHERE vub.uid = di.UID AND vub.addTime > '1669824000' GROUP BY di.userID_id) AS t1 WHERE t1.uid NOT IN (SELECT uid FROM uid_cloud_storage_count)) AS t1 WHERE t1.`day`>='15' AND t1.userID_id is NOT NULL) AS t1 ORDER BY t1.userID_id)AS t1 WHERE t1.userID_id NOT IN (SELECT di.userID_id FROM `uid_cloud_storage_count` AS vcsc LEFT JOIN device_info AS di ON vcsc.uid = di.UID WHERE di.userID_id is NOT NULL))AS t1 LEFT JOIN device_user AS du ON t1.userID_id = du.userID order by t1.addTime "
        cursor.execute(sql)
        uid_type_tuple = cursor.fetchall()
        cursor.close()  # 执行完，关闭
        connection.close()
        result_list = []
        not_upload_list = []
        try:
            with transaction.atomic():
                col_names = [desc[0] for desc in cursor.description]
                for uid_type in uid_type_tuple:
                    uid_dict = dict(zip(col_names, uid_type))
                    result_list.append(uid_dict)
                for item in result_list:  # 获取账号下的云存设备数量
                    device_info_qs = Device_Info.objects.filter(userID_id=item['userID_id']).values('UID')
                    count = device_info_qs.count()
                    if count == 1 or count == 0:
                        item['device_number'] = count
                    else:
                        device_number = 0
                        for device_info in device_info_qs:
                            uid_set_qs = UidSetModel.objects.filter(uid=device_info['UID']).values('ucode',
                                                                                                   'device_type')
                            device_type_qs = DeviceTypeModel.objects.filter(type=uid_set_qs[0]['device_type']).values(
                                'model')
                            model = device_type_qs[0]['model'] if device_type_qs.exists() else ''
                            #  判断设备是否为ipc设备和是否支持云存
                            if model == 2:
                                if len(uid_set_qs[0]['ucode']) > 4:
                                    number = uid_set_qs[0]['ucode'][-4]
                                else:
                                    continue
                                if number in ['4', '5']:
                                    device_number += 1
                            else:
                                continue
                        item['device_number'] = device_number
                    item['other_device'] = 0
                    not_upload_list.append(item)
                #  创建Excel,导出所有流失预警数据
                file_name = "流失预警.xls"
                if not_upload_list:
                    ws = xlwt.Workbook(encoding="UTF-8")
                    w = ws.add_sheet('流失预警', cell_overwrite_ok=True)
                    w.write(0, 0, u'用户名')
                    w.write(0, 1, u'用户ID')
                    w.write(0, 2, u'设备UID')
                    w.write(0, 3, u'设备自云存开通起多少天没有上传数据')
                    w.write(0, 4, u'设备数量')
                    w.write(0, 5, u'用户有无上传过数据')
                    w.write(0, 6, u'是否有有效期内的付费套餐')
                    w.write(0, 7, u'预警级别')
                    excel_row = 1
                    for obj in not_upload_list:
                        data_username = obj['username']
                        data_userID_id = obj['userID_id']
                        data_uid = obj['uid']
                        data_day = obj['day']
                        data_device_number = obj['device_number']
                        if data_device_number == 1:
                            data_device_number = 'N/A'
                        data_use_status = obj['use_status']
                        if data_use_status == 1:
                            data_use_status = '有'
                        else:
                            data_use_status = '无'
                        data_other_device = '无'
                        data_grade = obj['grade']
                        w.write(excel_row, 0, data_username)
                        w.write(excel_row, 1, data_userID_id)
                        w.write(excel_row, 2, data_uid)
                        w.write(excel_row, 3, data_day)
                        w.write(excel_row, 4, data_device_number)
                        w.write(excel_row, 5, data_other_device)
                        w.write(excel_row, 6, data_use_status)
                        w.write(excel_row, 7, data_grade)
                        excel_row += 1
                    # 生成相应格式给前端
                    res = HttpResponse(content_type='application/vnd.ms-excel')
                    res['Content-Disposition'] = 'attachment; filename={}'.format(escape_uri_path(file_name))
                    ws.save(res)
                    return res
        except Exception as e:
            meg = '异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e))
            return response.json(500, meg)