# -*- 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.utils.encoding import escape_uri_path from django.views.generic.base import View from Model.models import DeviceUserSummary, OrdersSummary, DeviceInfoSummary 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 = DeviceUserSummary.objects.filter(time__gte=start_time_stamp, time__lt=end_time_stamp, query_type=0).values('count', 'country') user_increase_count = user_increase_qs.aggregate(count=Sum('count'))['count'] user_increase_count = user_increase_count if user_increase_count else 0 user_increase_region_dict = {} user_increase_region_list = [] for item in user_increase_qs: user_increase_region_temp_dict = eval(item['country']) for k, v in user_increase_region_temp_dict.items(): if k in user_increase_region_dict: user_increase_region_dict[k] += v else: user_increase_region_dict[k] = v for k, v in user_increase_region_dict.items(): user_increase_region_list.append({ 'countryName': k, 'count': v }) # 所有用户数据 user_all_qs = DeviceUserSummary.objects.filter(time__lte=start_time_stamp, query_type=0).values('count', 'country') user_all_region_dict = {} user_all_region_list = [] for item in user_all_qs: user_all_region_temp_dict = eval(item['country']) for k, v in user_all_region_temp_dict.items(): if k in user_all_region_dict: user_all_region_dict[k] += v else: user_all_region_dict[k] = v for k, v in user_all_region_dict.items(): user_all_region_list.append({ 'countryName': k, 'count': v }) user_all_count = user_all_qs.aggregate(count=Sum('count'))['count'] user_all_count = user_all_count if user_all_count else 0 # 活跃用户数据 user_active_count = DeviceUserSummary.objects.filter(time__gte=start_time_stamp, time__lt=end_time_stamp, query_type=1).aggregate(count=Sum('count'))['count'] user_active_count = round(user_active_count, 2) if user_active_count else 0 # 新增设备数据 device_increase_count = DeviceInfoSummary.objects.filter(time__gte=start_time_stamp, time__lt=end_time_stamp, query_type=0).aggregate(count=Sum('count')) device_increase_count = device_increase_count['count'] if device_increase_count['count'] else 0 # 活跃设备数据 device_active_count = DeviceInfoSummary.objects.filter(time__gte=start_time_stamp, time__lt=end_time_stamp, query_type=1).aggregate(count=Sum('count'))['count'] device_active_count = device_active_count if device_active_count else 0 # 所有设备数据 device_all_count = DeviceInfoSummary.objects.filter(time__lte=start_time_stamp, query_type=0).aggregate(count=Sum('count'))['count'] device_all_count = device_all_count if device_all_count else 0 # 昨日订单销售额 order_qs = OrdersSummary.objects.filter(time__gte=start_time_stamp, time__lt=end_time_stamp, query_type=0).values('total') cny_total = 0 usd_total = 0 for item in order_qs: temp_total = eval(item['total']) cny_total = round(cny_total + temp_total.get('CNY', 0), 2) usd_total = round(usd_total + temp_total.get('USD', 0), 2) order_total = {'cnyTotal': cny_total, 'usdTotal': usd_total} # 昨日云存订单销售额 vod_order_total = order_qs.filter(service_type=0) vod_cny_total = 0 vod_usd_total = 0 for item in vod_order_total: temp_total = eval(item['total']) vod_cny_total = round(vod_cny_total + temp_total.get('CNY', 0), 2) vod_usd_total = round(vod_usd_total + temp_total.get('USD', 0), 2) vod_order_total = {'cnyTotal': vod_cny_total, 'usdTotal': vod_usd_total} # 昨日AI订单销售额 ai_order_total = order_qs.filter(service_type=1) ai_cny_total = 0 ai_usd_total = 0 for item in ai_order_total: temp_total = eval(item['total']) ai_cny_total = round(ai_cny_total + temp_total.get('CNY', 0), 2) ai_usd_total = round(ai_usd_total + temp_total.get('USD', 0), 2) ai_order_total = {'cnyTotal': ai_cny_total, 'usdTotal': ai_usd_total} # 昨日联通订单销售额 unicom_order_total = order_qs.filter(service_type=2) unicom_cny_total = 0 unicom_usd_total = 0 for item in unicom_order_total: temp_total = eval(item['total']) unicom_cny_total = round(unicom_cny_total + temp_total.get('CNY', 0), 2) unicom_usd_total = round(unicom_usd_total + temp_total.get('USD', 0), 2) unicom_order_total = {'cnyTotal': unicom_cny_total, 'usdTotal': unicom_usd_total} # 所有订单销售额 order_all_qs = OrdersSummary.objects.filter(time__lte=start_time_stamp, query_type=0).values('total') cny_all_total = 0 usd_all_total = 0 for item in order_all_qs: temp_total = eval(item['total']) cny_all_total = round(cny_all_total + temp_total.get('CNY', 0), 2) usd_all_total = round(usd_all_total + temp_total.get('USD', 0), 2) order_all_total = {'cnyTotal': cny_all_total, 'usdTotal': usd_all_total} # 所有云存订单销售额 vod_order_all_total = order_all_qs.filter(service_type=0) vod_cny_all_total = 0 vod_usd_all_total = 0 for item in vod_order_all_total: temp_total = eval(item['total']) vod_cny_all_total = round(vod_cny_all_total + temp_total.get('CNY', 0), 2) vod_usd_all_total = round(vod_usd_all_total + temp_total.get('USD', 0), 2) vod_order_all_total = {'cnyTotal': vod_cny_all_total, 'usdTotal': vod_usd_all_total} # 所有AI订单销售额 ai_order_all_total = order_all_qs.filter(service_type=1) ai_cny_all_total = 0 ai_usd_all_total = 0 for item in ai_order_all_total: temp_total = eval(item['total']) ai_cny_all_total = round(ai_cny_all_total + temp_total.get('CNY', 0), 2) ai_usd_all_total = round(ai_usd_all_total + temp_total.get('USD', 0), 2) ai_order_all_total = {'cnyTotal': ai_cny_all_total, 'usdTotal': ai_usd_all_total} # 所有联通订单销售额 unicom_order_all_total = order_all_qs.filter(service_type=2) unicom_cny_all_total = 0 unicom_usd_all_total = 0 for item in unicom_order_all_total: temp_total = eval(item['total']) unicom_cny_all_total = round(unicom_cny_all_total + temp_total.get('CNY', 0), 2) unicom_usd_all_total = round(unicom_usd_all_total + temp_total.get('USD', 0), 2) unicom_order_all_total = {'cnyTotal': unicom_cny_all_total, 'usdTotal': unicom_usd_all_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': 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_region_list, 'userAllRegion': user_all_region_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 = OrdersSummary.objects.filter(time__gte=start_time, time__lt=end_time, query_type=0).values( 'total') 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: temp_order_qs = order_qs.filter(time__gte=item[0], time__lt=item[1]) cny_total = 0 usd_total = 0 for each in temp_order_qs: temp_total = eval(each['total']) cny_total += temp_total.get('CNY', 0) usd_total += temp_total.get('USD', 0) res = { 'rmbTotal': cny_total, 'usdTotal': usd_total, '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 = {'cnyTotal': 0, 'usdTotal': 0} vod_order_total = {'cnyTotal': 0, 'usdTotal': 0} ai_order_total = {'cnyTotal': 0, 'usdTotal': 0} unicom_order_total = {'cnyTotal': 0, 'usdTotal': 0} order_all_total = {'cnyTotal': 0, 'usdTotal': 0} vod_order_all_total = {'cnyTotal': 0, 'usdTotal': 0} ai_order_all_total = {'cnyTotal': 0, 'usdTotal': 0} unicom_order_all_total = {'cnyTotal': 0, 'usdTotal': 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['cnyTotal'] = round( order_total['cnyTotal'] + result['result']['orderTotal']['cnyTotal'], 2) order_total['usdTotal'] = round( order_total['usdTotal'] + result['result']['orderTotal']['usdTotal'], 2) vod_order_total['cnyTotal'] = round( vod_order_total['cnyTotal'] + result['result']['vodOrderTotal']['cnyTotal'], 2) vod_order_total['usdTotal'] = round( vod_order_total['usdTotal'] + result['result']['vodOrderTotal']['usdTotal'], 2) ai_order_total['cnyTotal'] = round( ai_order_total['cnyTotal'] + result['result']['aiOrderTotal']['cnyTotal'], 2) ai_order_total['usdTotal'] = round( ai_order_total['usdTotal'] + result['result']['aiOrderTotal']['usdTotal'], 2) unicom_order_total['cnyTotal'] = round( unicom_order_total['cnyTotal'] + result['result']['unicomOrderTotal']['cnyTotal'], 2) unicom_order_total['usdTotal'] = round( unicom_order_total['usdTotal'] + result['result']['unicomOrderTotal']['usdTotal'], 2) order_all_total['cnyTotal'] = round( order_all_total['cnyTotal'] + result['result']['orderAllTotal']['cnyTotal'], 2) order_all_total['usdTotal'] = round( order_all_total['usdTotal'] + result['result']['orderAllTotal']['usdTotal'], 2) vod_order_all_total['cnyTotal'] = round( vod_order_all_total['cnyTotal'] + result['result']['vodOrderAllTotal']['cnyTotal'], 2) vod_order_all_total['usdTotal'] = round( vod_order_all_total['usdTotal'] + result['result']['vodOrderAllTotal']['usdTotal'], 2) ai_order_all_total['cnyTotal'] = round( ai_order_all_total['cnyTotal'] + result['result']['aiOrderAllTotal']['cnyTotal'], 2) ai_order_all_total['usdTotal'] = round( ai_order_all_total['usdTotal'] + result['result']['aiOrderAllTotal']['usdTotal'], 2) unicom_order_all_total['cnyTotal'] = round( unicom_order_all_total['cnyTotal'] + result['result']['unicomOrderAllTotal']['cnyTotal'], 2) unicom_order_all_total['usdTotal'] = round( unicom_order_all_total['usdTotal'] + result['result']['unicomOrderAllTotal']['usdTotal'], 2) 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) else: return response.json(result['result_code'], result['result']) 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) if user_increase_other_dict['count']: 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) if user_all_other_dict['count']: user_all_list.append(user_all_other_dict) 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['cnyTotal'] = round(each['cnyTotal'] + item['cnyTotal'], 2) each['usdTotal'] = round(each['usdTotal'] + item['usdTotal'], 2) flag = 1 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))