ASJServer/AdminController/ServeManagementController.py

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

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
from Object.ResponseObject import ResponseObject
from Object.TokenObject import TokenObject
from Object.UnicomObject import UnicomObjeect
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)
        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 == 'getDeviceOrderList':
                return self.getDeviceOrderList(request_dict, response)
            elif operation == 'getAbnormalOrderList':
                return self.getAbnormalOrderList(request_dict, response)
            elif operation == 'updateAbnormalOrder':
                return self.updateAbnormalOrder(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 == '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(lang__lang='cn')).values('id', 'lang__title', 'lang__content',
                                                                              'pixel_level')
            if not store_meal.exists():
                return response.json(0, [])
            store_meal_list = []
            for item in store_meal:
                pixel_content = '4k像素' if item['pixel_level'] == 1 else '4k像素以下'
                store_meal_list.append({'id': item['id'], 'title': item['lang__title'] + '--' + item[
                    'lang__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)
        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 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'],
                    '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").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:
            trade_no = abnormal_order.values('trade_no')[0]['trade_no']
            order_qs = Order_Model.objects.filter(trade_no=trade_no)
            if order_qs.exists():
                abnormal_order.update(status=1)
                return response.json(0)
            params = {'trade_no': trade_no}
            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)
            return response.json(0)
        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, '重置云存体验失败')

    @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:
        """
        status = request_dict.get('status', None)
        if status in [1, 5, 6, 7]:  # 付款状态
            return response.json(10059)
        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':
                    order_qs = Order_Model.objects.filter(orderID=orderID, userID__username=userName, UID=uid).values(
                        'rank__bucket_id')
                    if not order_qs.exists():
                        return response.json(14)
                    uid_bucket_qs = UID_Bucket.objects.filter(uid=uid, use_status=1).values('has_unused')
                    has_unused = uid_bucket_qs[0]['has_unused'] if uid_bucket_qs.exists() else ''
                    #  判断套餐是否唯一
                    if has_unused == 0:
                        uid_bucket_qs.update(endTime=nowTime)
                        CronUpdateDataView.updateUnusedUidBucket(response)
                        return response.json(0)
                    # 当设备套餐不唯一时
                    # 判断停用套餐是否是为未使用套餐
                    unused_uid_Meal_qs = Unused_Uid_Meal.objects.filter(uid=uid,
                                                                        bucket_id=order_qs[0]['rank__bucket_id'])
                    unused_uid_Meal_number = unused_uid_Meal_qs.count()
                    if unused_uid_Meal_number == 1:
                        unused_uid_Meal_qs.delete()
                        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)