#!/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) orderType = request_dict.get('orderType', 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 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_type") 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))