ASJServer/AdminController/ServeManagementController.py

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

import xlrd
import xlwt
import calendar
from django.utils.encoding import escape_uri_path
from django.http import HttpResponse, StreamingHttpResponse
from django.views.generic.base import View
from Model.models import VodBucketModel, CDKcontextModel, Store_Meal, Order_Model, \
    UID_Bucket, ExperienceContextModel, Lang, CloudLogModel, UidSetModel, Unused_Uid_Meal
from Object.ResponseObject import ResponseObject
from Object.TokenObject import TokenObject
from Service.CommonService import CommonService
from django.db.models import F, Sum, Count, Q


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)
        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)
            elif operation == 'getCdkList':
                return self.getCdkList(userID, request_dict, response)
            elif operation == 'createCdk':
                return self.createCdk(request_dict, response)
            elif operation == 'deleteCdk':
                return self.deleteCdk(request_dict, response)
            elif operation == 'downloadCDK':
                return self.downloadCDK(request_dict, response)
            elif operation == 'getDeviceOrderList':
                return self.getDeviceOrderList(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)
            else:
                return response.json(404)

    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, 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, 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, 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',
                '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'],
                    '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, 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))
        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,
            }
            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, 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, 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, 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, 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, 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, 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)
        cdk_list = []
        sm_qs = Store_Meal.objects.filter(
            pay_type__payment='cdk_pay', bucket__mold=mold, is_show=0)
        if not sm_qs.exists():
            return response.json(173)
        rank = sm_qs[0].id
        for i in range(int(cdk_num)):
            nowTime = int(time.time())
            cdk = hashlib.md5((str(uuid.uuid1()) +
                               str(nowTime)).encode('utf-8')).hexdigest()
            cdk_model = CDKcontextModel(
                cdk=cdk,
                create_time=nowTime,
                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, 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

    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)

        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 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, [])

            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_ql = order_ql.order_by('-addTime')  # 根据CDK创建时间降序排序
            order_ql = order_ql[(page - 1) * line:page * line]
            return response.json(
                0, {'list': list(order_ql), 'total': count})
        except Exception as e:
            print(e)
            return response.json(500, 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, 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, repr(e))

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

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

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

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

    def getCloudUserList(self, 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)

        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 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(
                    'uid_bucket_id',
                    'desc',
                    'userID__userID',
                    'UID',
                    'price',
                    'payType',
                    'userID__username',
                    'userID__phone',
                    'userID__userEmail',
                    'userID__data_joined'):
                    # 套餐到期时间累加未使用套餐
                    unused_qs = Unused_Uid_Meal.objects.filter(uid=uid_bucket.uid).values('num', 'expire')
                    if unused_qs.exists():
                        addMonth = 0
                        for unused in unused_qs:
                            addMonth += unused['num'] * 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,
                        'uid': uid,
                        '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'],
                        '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()
                    }

                    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, 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, 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, repr(e))