# -*- encoding: utf-8 -*- """ @File : UserDataController.py @Time : 2022/8/16 10:44 @Author : peng @Email : zhangdongming@asj6.wecom.work @Software: PyCharm """ import datetime import openpyxl import requests from django.db.models import Sum from django.http import HttpResponse from django.views.generic.base import View from Model.models import VideoPlaybackTimeModel, Device_User, Device_Info, Order_Model from Service.CommonService import CommonService # 业务数据 class HomeDataView(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): token_code, user_id, response = CommonService.verify_token_get_user_id(request_dict, request) if token_code != 0: return response.json(token_code) if operation == 'allData': # 查询首页数据 return self.query_all_data(response) elif operation == 'salesVolume': # 查询销售额数据 return self.query_sales_volume_data(request_dict, response) elif operation == 'global/allData': # 查询全球首页数据 return self.query_global_all_data(request, request_dict, response) elif operation == 'global/salesVolume': # 查询全球销售额数据 return self.query_global_sales_volume_data(request, request_dict, response) elif operation == 'exportData': # 查询全球销售额数据 return self.export_data(request_dict, response) else: return response.json(414) @classmethod def query_all_data(cls, response): """ 查询首页数据 @param response:响应对象 @return: """ end_time = datetime.datetime.today().replace(hour=0, minute=0, second=0, microsecond=0) start_time = end_time + datetime.timedelta(days=-1) end_time_stamp = CommonService.str_to_timestamp(end_time.strftime('%Y-%m-%d %H:%M:%S')) start_time_stamp = CommonService.str_to_timestamp(start_time.strftime('%Y-%m-%d %H:%M:%S')) try: user_increase_count = Device_User.objects.filter(data_joined__range=(start_time, end_time)).count() user_active_count = Device_User.objects.filter(last_login__range=(start_time, end_time)).count() user_all_count = Device_User.objects.filter(data_joined__lte=end_time).count() device_increase_count = Device_Info.objects.filter(data_joined__range=(start_time, end_time)).values( 'UID').distinct().count() device_active_count = VideoPlaybackTimeModel.objects.filter( startTime__range=(start_time_stamp, end_time_stamp)).values('uid').distinct().count() device_all_count = Device_Info.objects.filter(data_joined__lte=end_time).values('UID').distinct().count() order_qs = Order_Model.objects.filter(status=1, addTime__range=(start_time_stamp, end_time_stamp)) order_total = order_qs.aggregate(total=Sum('price'))['total'] vod_order_total = order_qs.filter(order_type=0).aggregate(total=Sum('price'))['total'] ai_order_total = order_qs.filter(order_type=1).aggregate(total=Sum('price'))['total'] unicom_order_total = order_qs.filter(order_type=2).aggregate(total=Sum('price'))['total'] order_all_qs = Order_Model.objects.filter(status=1, addTime__lte=end_time_stamp) order_all_total = order_all_qs.aggregate(total=Sum('price'))['total'] vod_order_all_total = order_all_qs.filter(order_type=0).aggregate(total=Sum('price'))['total'] ai_order_all_total = order_all_qs.filter(order_type=1).aggregate(total=Sum('price'))['total'] unicom_order_all_total = order_all_qs.filter(order_type=2).aggregate(total=Sum('price'))['total'] res = { 'userIncreaseCount': user_increase_count, 'userActiveCount': user_active_count, 'userAllCount': user_all_count, 'deviceIncreaseCount': device_increase_count, 'deviceActiveCount': device_active_count, 'deviceAllCount': device_all_count, 'orderTotal': round(order_total, 2) if order_total else 0, 'vodOrderTotal': round(vod_order_total, 2) if vod_order_total else 0, 'aiOrderTotal': round(ai_order_total, 2) if ai_order_total else 0, 'unicomOrderTotal': round(unicom_order_total, 2) if unicom_order_total else 0, 'orderAllTotal': round(order_all_total, 2) if order_all_total else 0, 'vodOrderAllTotal': round(vod_order_all_total, 2) if vod_order_all_total else 0, 'aiOrderAllTotal': round(ai_order_all_total, 2) if ai_order_all_total else 0, 'unicomOrderAllTotal': round(unicom_order_all_total, 2) if unicom_order_all_total else 0, } return response.json(0, res) except Exception as e: return response.json(500, repr(e)) @classmethod def query_sales_volume_data(cls, request_dict, response): """ 查询销售额数据 @param request_dict:请求参数 @request_dict startTime:开始时间 @request_dict endTime:结束时间 @request_dict timeUnit:时间单位 @param response:响应对象 @return: """ start_time = request_dict.get('startTime', None) end_time = request_dict.get('endTime', None) time_unit = request_dict.get('timeUnit', None) if not all([start_time, end_time, time_unit]): return response.json(444, {'error param': 'startTime or endTime or timeUnit'}) try: order_qs = Order_Model.objects.filter(addTime__range=(start_time, end_time)) start_time = datetime.datetime.fromtimestamp(int(start_time)) end_time = datetime.datetime.fromtimestamp(int(end_time)) time_list = CommonService.cutting_time(start_time, end_time, time_unit) order_list = [] for item in time_list: total = order_qs.filter(addTime__range=item).aggregate(total=Sum('price'))['total'] res = { 'total': round(total, 2) if total else 0, 'startTime': item[0], 'endTime': item[1] } order_list.append(res) return response.json(0, order_list) except Exception as e: return response.json(500, repr(e)) @classmethod def query_global_all_data(cls, request, request_dict, response): """ 查询全球首页数据 @param request:请求 @param request_dict:请求参数 @param response:响应对象 @return: """ url_list = CommonService.get_domain_name() try: headers = { 'Authorization': request.META.get('HTTP_AUTHORIZATION') } user_increase_count = 0 user_active_count = 0 user_all_count = 0 device_increase_count = 0 device_active_count = 0 device_all_count = 0 order_total = 0 vod_order_total = 0 ai_order_total = 0 unicom_order_total = 0 order_all_total = 0 vod_order_all_total = 0 ai_order_all_total = 0 unicom_order_all_total = 0 for url in url_list: url = url + request.path.replace('global/', '') res = requests.get(url=url, params=request_dict, headers=headers) result = res.json() user_increase_count += result['result']['userIncreaseCount'] user_active_count += result['result']['userActiveCount'] user_all_count += result['result']['userAllCount'] device_increase_count += result['result']['deviceIncreaseCount'] device_active_count += result['result']['deviceActiveCount'] device_all_count += result['result']['deviceAllCount'] order_total += result['result']['orderTotal'] vod_order_total += result['result']['vodOrderTotal'] ai_order_total += result['result']['aiOrderTotal'] unicom_order_total += result['result']['unicomOrderTotal'] order_all_total += result['result']['orderAllTotal'] vod_order_all_total += result['result']['vodOrderAllTotal'] ai_order_all_total += result['result']['aiOrderAllTotal'] unicom_order_all_total += result['result']['unicomOrderAllTotal'] res = { 'userIncreaseCount': user_increase_count, 'userActiveCount': user_active_count, 'userAllCount': user_all_count, 'deviceIncreaseCount': device_increase_count, 'deviceActiveCount': device_active_count, 'deviceAllCount': device_all_count, 'orderTotal': order_total, 'vodOrderTotal': vod_order_total, 'aiOrderTotal': ai_order_total, 'unicomOrderTotal': unicom_order_total, 'orderAllTotal': order_all_total, 'vodOrderAllTotal': vod_order_all_total, 'aiOrderAllTotal': ai_order_all_total, 'unicomOrderAllTotal': unicom_order_all_total, } return response.json(0, res) except Exception as e: return response.json(500, repr(e)) @classmethod def query_global_sales_volume_data(cls, request, request_dict, response): """ 查询全球销售额数据 @param request:请求 @param request_dict:请求参数 @param response:响应对象 @return: """ url_list = CommonService.get_domain_name() try: headers = { 'Authorization': request.META.get('HTTP_AUTHORIZATION') } order_list = [] for url in url_list: url = url + request.path.replace('global/', '') res = requests.get(url=url, params=request_dict, headers=headers) result = res.json() for item in result['result']: flag = 0 for each in order_list: if item['startTime'] == each['startTime'] and item['endTime'] == each['endTime']: each['total'] += item['total'] break if flag == 0: order_list.append(item) return response.json(0, order_list) except Exception as e: return response.json(500, repr(e)) @classmethod def export_data(cls, request_dict, response): """ 下载文件 @param request_dict:请求参数 @request_dict tableData:表格数据 @request_dict fileName:文件名 @param response:响应对象 @return: """ table_data = request_dict.get('tableData', None) sheet_name = request_dict.get('fileName', None) if not all([table_data, sheet_name]): return response.json(444, {'error param': 'tableData or fileName'}) table_data = eval(table_data) file_name = sheet_name + '.xlsx' file_path = 'static/static/{}'.format(file_name) try: wb = openpyxl.Workbook() sh = wb.create_sheet(sheet_name, 0) for row, data in enumerate(table_data): row += 1 if row == 1: sh.append(list(data.keys())) sh.append(list(data.values())) wb.save(file_path) with open(file_path, 'rb') as f: res = HttpResponse(f) res['Content-Type'] = 'application/octet-stream' res['Content-Disposition'] = 'attachment;filename="{}"'.format(file_name) return res except Exception as e: return response.json(500, repr(e))