ASJServer/AdminController/dataSystemManagement/HomeDataController.py

# -*- 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}
            # 昨日云盘订单销售额
            icloud_order_total = order_qs.filter(service_type=4)
            icloud_cny_total = 0
            icloud_usd_total = 0
            for item in icloud_order_total:
                temp_total = eval(item['total'])
                icloud_cny_total = round(icloud_cny_total + temp_total.get('CNY', 0), 2)
                icloud_usd_total = round(icloud_usd_total + temp_total.get('USD', 0), 2)
            icloud_order_total = {'cnyTotal': icloud_cny_total, 'usdTotal': icloud_usd_total}
            # 昨日联通订单销售额
            unicom_order_total = order_qs.filter(service_type__in=[2, 3])
            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}
            # 所有云盘订单销售额
            icloud_order_all_total = order_all_qs.filter(service_type=4)
            icloud_cny_all_total = 0
            icloud_usd_all_total = 0
            for item in icloud_order_all_total:
                temp_total = eval(item['total'])
                icloud_cny_all_total = round(icloud_cny_all_total + temp_total.get('CNY', 0), 2)
                icloud_usd_all_total = round(icloud_usd_all_total + temp_total.get('USD', 0), 2)
            icloud_order_all_total = {'cnyTotal': icloud_cny_all_total, 'usdTotal': icloud_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,
                'icloudOrderTotal': icloud_order_total,
                'unicomOrderTotal': unicom_order_total,
                'orderAllTotal': order_all_total,
                'vodOrderAllTotal': vod_order_all_total,
                'aiOrderAllTotal': ai_order_all_total,
                'icloudOrderAllTotal': icloud_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, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, 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 = {
                    'cnyTotal': 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, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, 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}
            icloud_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}
            icloud_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)
                    icloud_order_total['cnyTotal'] = round(
                        icloud_order_total['cnyTotal'] + result['result']['icloudOrderTotal']['cnyTotal'], 2)
                    icloud_order_total['usdTotal'] = round(
                        icloud_order_total['usdTotal'] + result['result']['icloudOrderTotal']['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)
                    icloud_order_all_total['cnyTotal'] = round(
                        icloud_order_all_total['cnyTotal'] + result['result']['icloudOrderAllTotal']['cnyTotal'], 2)
                    icloud_order_all_total['usdTotal'] = round(
                        icloud_order_all_total['usdTotal'] + result['result']['icloudOrderAllTotal']['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,
                'icloudOrderTotal': icloud_order_total,
                'unicomOrderTotal': unicom_order_total,
                'orderAllTotal': order_all_total,
                'vodOrderAllTotal': vod_order_all_total,
                'aiOrderAllTotal': ai_order_all_total,
                'icloudOrderAllTotal': icloud_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, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, 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, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, 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, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e)))