# -*- 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, Count from django.http import HttpResponse from django.utils.encoding import escape_uri_path from django.views.generic.base import View from Model.models import VideoPlaybackTimeModel, Device_User, Device_Info, Order_Model, CountryModel 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_qs = Device_User.objects.filter(data_joined__range=(start_time, end_time)) user_increase_list = user_increase_qs.values('region_country').annotate( count=Count('region_country')).order_by('-count') for item in user_increase_list: country_qs = CountryModel.objects.filter(id=item['region_country']).values('country_name') item['countryName'] = country_qs[0]['country_name'] if country_qs.exists() else '未知区域' user_increase_count = user_increase_qs.count() user_active_count = Device_User.objects.filter(last_login__range=(start_time, end_time)).count() user_all_qs = Device_User.objects.filter(data_joined__lte=end_time) user_all_list = user_all_qs.values('region_country').annotate( count=Count('region_country')).order_by('-count') for item in user_all_list: country_qs = CountryModel.objects.filter(id=item['region_country']).values('country_name') item['countryName'] = country_qs[0]['country_name'] if country_qs.exists() else '未知区域' user_all_count = user_all_qs.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, 'userIncreaseRegion': list(user_increase_list), 'userAllRegion': list(user_all_list) } 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 user_increase_temp_list = [] user_increase_list = [] user_increase_other_dict = {'count': 0, 'countryName': '其他', 'rate': 0} user_all_temp_list = [] user_all_list = [] user_all_other_dict = {'count': 0, 'countryName': '其他', 'rate': 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() if result['result_code'] == 0: 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'] for item in result['result']['userIncreaseRegion']: flag = 0 for each in user_increase_temp_list: if item['countryName'] == each['countryName']: each['count'] += item['count'] flag = 1 break if flag == 0: user_increase_temp_list.append(item) for item in result['result']['userAllRegion']: flag = 0 for each in user_all_temp_list: if item['countryName'] == each['countryName']: each['count'] += item['count'] flag = 1 break if flag == 0: user_all_temp_list.append(item) if user_increase_temp_list: for item in user_increase_temp_list: if user_increase_count: rate = round(item['count'] / user_increase_count * 100, 2) else: rate = 0 if rate >= 10: item['rate'] = rate user_increase_list.append(item) else: user_increase_other_dict['count'] += item['count'] if user_increase_count: user_increase_other_dict['rate'] = round(user_increase_other_dict['count'] / user_increase_count * 100, 2) user_increase_list.append(user_increase_other_dict) if user_all_temp_list: for item in user_all_temp_list: if user_all_count: rate = round(item['count'] / user_all_count * 100, 2) else: rate = 0 if rate >= 10: item['rate'] = rate user_all_list.append(item) else: user_all_other_dict['count'] += item['count'] if user_all_count: user_all_other_dict['rate'] = round(user_all_other_dict['count'] / user_all_count * 100, 2) user_all_list.append(user_all_other_dict) else: return response.json(result['result_code'], result['result']) 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, 'userIncreaseRegion': user_increase_list, 'userAllRegion': user_all_list } 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() if result['result_code'] == 0: 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) else: return response.json(result['result_code'], result['result']) 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' try: res = HttpResponse(content_type='application/octet-stream') res['Content-Disposition'] = 'attachment; filename={}'.format(escape_uri_path(file_name)) wb = openpyxl.Workbook() sh = wb.create_sheet(sheet_name, 0) for row, data in enumerate(table_data): if row == 0: sh.append(list(data.keys())) sh.append(list(data.values())) wb.save(res) # 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))