#!/usr/bin/env python3 # -*- coding: utf-8 -*- import hashlib import json import time import urllib import uuid import boto3 import threading import logging import xlwt from boto3.session import Session from django.http import JsonResponse, HttpResponseRedirect, HttpResponse, StreamingHttpResponse from django.views.generic.base import View from Model.models import Device_Info, Role, MenuModel, VodBucketModel, CDKcontextModel, Store_Meal, Order_Model, \ UID_Bucket, ExperienceContextModel, Lang, Device_User, CloudLogModel, UidSetModel from Object.ResponseObject import ResponseObject from Object.TokenObject import TokenObject from Object.UidTokenObject import UidTokenObject from Service.CommonService import CommonService from django.db.models import Q, F from time import strftime 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) 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', '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) if sm_qs.exists: 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, rank_id=rank, order=order, ) cdk_list.append(cdk_model) try: CDKcontextModel.objects.bulk_create(cdk_list) except Exception as e: print(repr(e)) return response.json(404, repr(e)) else: return response.json(0) 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_activate=0, rank__bucket__mold=0).values('cdk') else: # 下载国外未使用激活码 content += '激活码(国外)\n' cdk_inactivate_qs = CDKcontextModel.objects.filter( is_activate=0, rank__bucket__mold=1).values('cdk') for cdk_inactivate in cdk_inactivate_qs: content += cdk_inactivate['cdk'] + '\n' # print(content) 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", "channel", "desc", "price", "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 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__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) # 过滤套餐关联的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[(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) 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) 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() 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))