多账号下的阿里云账单财务分析

由于公司对各个部门、业务的财务账单要求具体到每一分钱上面，直接影响到部门的考核，员工的绩效等。领导极为重视这一块，要求自动计算各个财务单元的明细，并给出相应的财务报表，因此有了今天的这篇文章。而公司在阿里云、聚石塔都各有好几个账号，所以得统筹到一起来计算。 鉴于阿里云的特性，选择了给每个收费实例打标签的方式来进行财务分析，每个实例都必须打上 环境 业务单元 财务单元

而对于不能直接通过bucket分账的oss, 另起model，通过分账账单的接口，按bucket分账给各个财务单元

后端代码

models.py

"""
billing models
"""
from django.db import models
from django.utils.translation import gettext_lazy as _


class Tag(models.Model):
    instance_id = models.SlugField(_("Instance ID"), max_length=128, null=True)
    environment = models.CharField(_("Environment"), max_length=32, null=True, help_text="环境")
    business_unit = models.CharField(_("Business Unit "), max_length=32, null=True, help_text="业务单元")
    financial_unit = models.CharField(_("Financial Unit"), max_length=32, null=True, help_text="财务单元")
    product_detail = models.CharField(_("Product Detail"), max_length=128, null=True, help_text="产品详情")
    item_name = models.CharField(_("Item Name"), max_length=32, null=True, help_text="")
    owner = models.CharField(_("Bill Account Name"), max_length=32, null=True, help_text="所属账户")
    provider = models.CharField(_("Provider"), max_length=64, null=True, help_text="所在平台")
    created_at = models.DateTimeField(_("Created Time"), auto_now_add=True, help_text="创建时间")


class InstanceBill(models.Model):
    """ 参考 https://next.api.aliyun.com/api/BssOpenApi/2017-12-14/DescribeInstanceBill """

    Item_CHOICES = [("SubscriptionOrder", "预付订单"),
                    ("PayAsYouGoBill", "后付账单"),
                    ("Refund", "退款"),
                    ("Adjustment", "调账")]
    SubscriptionType_CHOICES = [("Subscription", "预付费"), ("PayAsYouGo", "后付费")]

    product_name = models.SlugField(_("Product Name"), max_length=64, null=True)
    instance_spec = models.SlugField(_("Instance Spec"), max_length=64, null=True)
    instance_id = models.SlugField(_("Instance ID"), max_length=128, null=True)
    nick_name = models.SlugField(_("Nick Name"), max_length=128, null=True)
    instance_config = models.TextField(_("Instance Config"), null=True)
    service_period = models.IntegerField(_("Service Period"), null=True)
    service_period_unit = models.CharField(_("Service Period Unit"), max_length=32, null=True)
    billing_type = models.CharField(_("BillingType"), max_length=32, null=True)
    billing_date = models.DateField(_("BillingDate"), null=True)
    billing_cycle = models.CharField(_("BillingCycle"), max_length=32, null=True, help_text="账单月周期")
    invoice_discount = models.FloatField(_("Invoice Discount"), null=True)
    internet_ip = models.GenericIPAddressField(_("InternetIP"), null=True)
    currency = models.CharField(_("Currency"), max_length=32, null=True)
    commodity_code = models.CharField(_("CommodityCode"), max_length=64, null=True)
    product_code = models.CharField(_("ProductCode"), max_length=64, null=True)
    product_type = models.CharField(_("ProductType"), max_length=64, null=True)
    product_detail = models.CharField(_("ProductDetail"), max_length=128, null=True)
    usage_unit = models.CharField(_("UsageUnit"), max_length=64, null=True)
    item = models.CharField(_("Item"), max_length=64, choices=Item_CHOICES)
    item_name = models.CharField(_("Item Name"), max_length=32, null=True)
    subscription_type = models.CharField(_("Subscription Type"), max_length=32, choices=SubscriptionType_CHOICES)
    cost_unit = models.CharField(_("CostUnit"), max_length=64, null=True)
    resource_group = models.CharField(_("ResourceGroup"), max_length=32, null=True)
    deducted_by_cash_coupons = models.FloatField(_("deducted by cash coupons"), null=True, help_text="代金券抵扣")
    payment_amount = models.FloatField(_("Payment Amount"), null=True, help_text="现金支付 含信用额度退款抵扣")
    pretax_amount = models.FloatField(_("PretaxAmount"), null=True, help_text="应付金额")
    pretax_gross_amount = models.FloatField(_("Pretax Gross Amount"), null=True, help_text="原始金额")
    outstanding_amount = models.FloatField(_("OutstandingAmount"), null=True, help_text="未结清金额")
    adjust_amount = models.FloatField(_("AdjustAmount"), null=True, help_text="信用额度退款抵扣")
    bill_account_id = models.BigIntegerField(_("Bill Account Id"), null=True)
    owner_id = models.CharField(_("OwnerID"), max_length=32, null=True)
    owner = models.CharField(_("BillAccountName"), max_length=32, null=True)
    provider = models.CharField(_("provider"), max_length=64, null=True)
    tag = models.ForeignKey(to=Tag, verbose_name=_("tag"), null=True, on_delete=models.SET_NULL)


class OssBill(models.Model):
    instance_id = models.CharField(_("实例账单ID"), max_length=128, null=False)
    bucket_name = models.CharField(_("BucketName"), max_length=256, null=False)
    storage_class = models.CharField(_("存储类型"), max_length=64, null=False)

    billing_cycle = models.CharField(_("账单周期"), max_length=32, null=True)
    billing_date = models.DateField(_("账单日期"), null=True)

    storage = models.FloatField(_('存储总容量/GB'), null=True)
    standard_storage = models.FloatField(_('标准存储容量/GB'), null=True)
    standard_storage_amount = models.FloatField(_('标准存储费用'), null=False, default=0)

    infrequent_access_storage = models.FloatField(_('低频访问存储容量/GB'), null=True)
    infrequent_access_storage_amount = models.FloatField(_('低频访问存储费用'), null=False, default=0)

    archive_storage = models.FloatField(_('归档存储容量/GB'), null=True)
    archive_storage_amount = models.FloatField(_('归档存储费用'), null=False, default=0)

    cold_archive_storage = models.FloatField(_('冷归档存储容量/GB'), null=True)
    cold_archive_storage_amount = models.FloatField(_('冷归档存储费用'), null=False, default=0)

    retrieval_storage = models.FloatField(_('数据取回容量/GB'), null=True)
    retrieval_storage_amount = models.FloatField(_('数据取回费用'), null=False, default=0)

    get_request_count = models.FloatField(_('GET类请求次数/万 '), null=True)
    get_request_count_amount = models.FloatField(_('GET类请求费用 '), null=False, default=0)

    put_request_count = models.FloatField(_('PUT类请求次数/万'), null=True)
    put_request_count_amount = models.FloatField(_('PUT类请求费用'), null=False, default=0)

    process_count = models.FloatField(_('视屏截帧数/千张'), null=True)
    process_count_amount = models.FloatField(_('视屏截帧费用'), null=False, default=0)

    internet_send = models.FloatField(_('公网流出流量/GB'), null=True)
    internet_send_amount = models.FloatField(_('公网流出流量费用'), null=False, default=0)

    cdn_send = models.FloatField(_('CDN回源流出流量/GB'), null=True)
    cdn_send_amount = models.FloatField(_('CDN回源流出流量费用'), null=False, default=0)

    other_item_amount = models.FloatField(_('其他非常见项费用'), null=False, default=0)

    pretax_amount = models.FloatField(_("应付总金额"), null=False, default=0)

    tag = models.ForeignKey(to=Tag, verbose_name=_("tag"), null=True, on_delete=models.SET_NULL)
    owner = models.CharField(_("owner"), max_length=64, null=True)
    provider = models.CharField(_("provider"), max_length=64, null=True)
    ownerid = models.BigIntegerField(_("账户ID"), null=True)

views.py

from rest_framework import viewsets
from apps.mixins import ModelViewSet
from apps.authorizer.mixins import DefaultRBACMixin
from apps.billing.models import InstanceBill, Tag, OssBill
from apps.billing import serializers, filters, mixins


class InstanceBillViewSet(DefaultRBACMixin, viewsets.ReadOnlyModelViewSet, mixins.AuditMixin, mixins.EchartsMixin,
                          mixins.AmountMixin, mixins.InstanceBillDownloadExcelMixin):
    resource_name = "billing"
    queryset = InstanceBill.objects.all()
    serializer_class = serializers.ListInstanceBillSerializer
    filter_class = filters.InstanceBillFilter
    search_fields = ['instance_id', 'product_detail']


class TagViewSet(DefaultRBACMixin, ModelViewSet):
    resource_name = "billing_tag"
    queryset = Tag.objects.all()
    serializer_class = serializers.ListTagSerializer
    filterset_fields = ('instance_id', 'environment', 'business_unit', 'financial_unit', 'owner', 'provider')
    search_fields = ['instance_id', 'product_detail']


class OssBillViewSet(DefaultRBACMixin, viewsets.ReadOnlyModelViewSet, mixins.AmountMixin,
                     mixins.AnalyExcelMixin, mixins.OssBillDownloadExcelMixin):
    resource_name = "billing_oss"
    queryset = OssBill.objects.all().order_by('-pretax_amount')
    serializer_class = serializers.ListOssBillSerializer
    analy_excel_serializer_class = serializers.AnalyExcelSerializer
    filter_class = filters.OssBillFilter
    search_fields = ['instance_id', 'bucket_name']

    def get_serializer_class(self):
        if self.action in ['analy_excel']:
            action_serializer_class = self.action + '_serializer_class'
            return getattr(self, action_serializer_class, None)
        return super().get_serializer_class()

filters.py

from django_filters import rest_framework as filters
from apps.billing import models


class InstanceBillFilter(filters.FilterSet):
    start_date = filters.DateFilter(label='账单日期大于', field_name='billing_date', lookup_expr='gte')
    end_date = filters.DateFilter(label='账单日期小于', field_name='billing_date', lookup_expr='lte')
    nick_name = filters.CharFilter(label="实例名称", field_name="nick_name", lookup_expr="exact")

    environment = filters.CharFilter(label="环境", field_name="tag__environment", lookup_expr="exact")
    business_unit = filters.CharFilter(label="业务单元", field_name="tag__business_unit", lookup_expr="exact")
    financial_unit = filters.CharFilter(label='财务单元', field_name='tag__financial_unit', lookup_expr='exact')

    class Meta:
        model = models.InstanceBill
        fields = ['product_name', 'product_detail', 'item_name', 'product_code', 'owner', 'provider']


class OssBillFilter(filters.FilterSet):
    start_date = filters.DateFilter(label='账单日期大于', field_name='billing_date', lookup_expr='gte')
    end_date = filters.DateFilter(label='账单日期小于', field_name='billing_date', lookup_expr='lte')

    environment = filters.CharFilter(label="环境", field_name="tag__environment", lookup_expr="exact")
    business_unit = filters.CharFilter(label="业务单元", field_name="tag__business_unit", lookup_expr="exact")
    financial_unit = filters.CharFilter(label='财务单元', field_name='tag__financial_unit', lookup_expr='exact')

    class Meta:
        model = models.OssBill
        fields = ['owner', 'provider']

serializers.py

from django.db import Error
from rest_framework import serializers, status, exceptions
from apps.billing.models import InstanceBill, Tag, OssBill


class ListInstanceBillSerializer(serializers.ModelSerializer):
    class Meta:
        model = InstanceBill
        depth = 1
        exclude = ['instance_config', 'owner_id']


class ListOssBillSerializer(serializers.ModelSerializer):
    class Meta:
        model = OssBill
        depth = 1
        fields = '__all__'


class AnalyExcelSerializer(serializers.ModelSerializer):
    files = serializers.FileField(max_length=None, allow_empty_file=False, required=False)

    class Meta:
        model = OssBill
        fields = ['files']


class ListTagSerializer(serializers.ModelSerializer):
    class Meta:
        model = Tag
        fields = '__all__'

mixins.py

import pandas as pd
from datetime import datetime
from rest_framework.decorators import action
from rest_framework.response import Response
from rest_framework import status
from django.http.response import HttpResponse

from apps.billing.models import InstanceBill, OssBill
from apps.billing.utils import analyOssBillAmount, queryset_analysis
from apps.utils import audit_instance_bill, echarts_instance_bill, get_firstmonth_now


class AmountMixin:
    @action(methods=['get'], detail=False)
    def amount(self, request, *args, **kwargs):
        objs = self.filter_queryset(self.get_queryset())
        total_amount = sum(obj.pretax_amount for obj in objs)
        return Response(data={"total_amount": total_amount}, status=status.HTTP_200_OK)


class AuditMixin:
    @action(methods=['get'], detail=False)
    def audit(self, request, *args, **kwargs):
        today_month = datetime.now().strftime("%Y-%m")
        provider = request.query_params.get('provider', 'aliyun jst titans')
        billing_cycle = request.query_params.get('billing_cycle', today_month)
        financial_unit = request.query_params.get('financial_unit')
        result = audit_instance_bill(InstanceBill, provider, billing_cycle, financial_unit)
        return Response(data=result, status=status.HTTP_200_OK)


class EchartsMixin:
    @action(methods=['get'], detail=False)
    def echarts(self, request, *args, **kwargs):
        start_date_default, end_date_default = get_firstmonth_now()
        provider = request.query_params.get('provider', 'aliyun jst titans')
        start_date = request.query_params.get('start_date', start_date_default)
        end_date = request.query_params.get('end_date', end_date_default)
        financial_unit = request.query_params.get('financial_unit')
        data = echarts_instance_bill(InstanceBill, provider, start_date, end_date, financial_unit)
        return Response(data=data, status=status.HTTP_200_OK)


class InstanceBillDownloadExcelMixin:
    @action(methods=['get'], detail=False)
    def download_excel(self, request):
        # objs = self.filter_queryset(self.get_queryset())
        start_date_default, end_date_default = get_firstmonth_now()
        start_date = request.query_params.get('start_date', start_date_default.strftime("%Y-%m-%d"))
        end_date = request.query_params.get('end_date', end_date_default.strftime("%Y-%m-%d"))
        column_header = ["实例ID", "实例名称", "产品名称", "产品CODE", "产品详情", "计费项", '账期', "付费单元", "资源组", "金额", "环境",
                         "业务单元", "财务单元", "平台", "账户"]
        objs = InstanceBill.objects.filter(
            billing_date__gte=start_date,
            billing_date__lte=end_date
        ).values(
            'instance_id', 'nick_name', 'product_name', 'product_code', 'product_detail', 'item_name', 'billing_cycle', 'cost_unit',
            'resource_group', 'pretax_amount',
            'tag__environment', 'tag__business_unit', 'tag__financial_unit', 'provider', 'owner')

        df = pd.DataFrame(objs)
        df.columns = column_header
        df["财务单元"].fillna('未知', inplace=True)
        df["业务单元"].fillna('未知', inplace=True)

        outfile = queryset_analysis(df, values="金额", columns=["账期"], index=["财务单元", "业务单元"])
        response = HttpResponse(content_type='application/vnd.ms-excel')
        response['Content-Disposition'] = "attachment;filename=阿里云实例账单.xlsx"
        # response['Cache-Control'] = 'no-cache'
        response.write(outfile.getvalue())
        return response


class OssBillDownloadExcelMixin:
    @action(methods=['get'], detail=False)
    def download_excel(self, request):
        start_date_default, end_date_default = get_firstmonth_now()
        start_date = request.query_params.get('start_date', start_date_default.strftime("%Y-%m-%d"))
        end_date = request.query_params.get('end_date', end_date_default.strftime("%Y-%m-%d"))
        column_header = ["实例ID", "桶名称", "存储类型", '账期', "金额", "环境", "业务单元", "财务单元", "平台", "账户"]
        objs = OssBill.objects.filter(
            billing_date__gte=start_date,
            billing_date__lte=end_date
        ).values(
            'instance_id', 'bucket_name', 'storage_class', 'billing_cycle', 'pretax_amount',
            'tag__environment', 'tag__business_unit', 'tag__financial_unit', 'provider', 'owner')

        df = pd.DataFrame(objs)
        df.columns = column_header
        df["财务单元"].fillna('未知', inplace=True)
        df["业务单元"].fillna('未知', inplace=True)

        outfile = queryset_analysis(df, values="金额", columns=["账期"], index=["财务单元", "业务单元"])
        response = HttpResponse(content_type='application/vnd.ms-excel')
        response['Content-Disposition'] = "attachment;filename=阿里云实例账单.xlsx"
        # response['Cache-Control'] = 'no-cache'
        response.write(outfile.getvalue())
        return response


class AnalyExcelMixin:
    @action(methods=['post'], detail=False)
    def analy_excel(self, request, *args, **kwargs):
        file_obj = request.data['files']
        data = analyOssBillAmount(file_obj)
        return Response(data=data, status=status.HTTP_200_OK)

utils.py

from io import BytesIO
import pandas as pd
import numpy as np
from django.db.models import Count, Sum, F
from apps.billing.models import *


def groupByOssBill(**kwargs):
    sum_data = dict()
    objs = OssBill.objects.filter(**kwargs).values(
        "instance_id", "billing_cycle", "ownerid"
    ).annotate(
        standard_storage_sum=Sum('standard_storage'),
        infrequent_access_storage_sum=Sum('infrequent_access_storage'),
        archive_storage_sum=Sum('archive_storage'),
        cold_archive_storage_sum=Sum('cold_archive_storage'),
        get_request_count_sum=Sum('get_request_count'),
        put_request_count_sum=Sum('put_request_count'),
        internet_send_sum=Sum('internet_send'),
        cdn_send_sum=Sum('cdn_send'),
    )

    for i in objs:
        if i["billing_cycle"] not in sum_data.keys():
            sum_data[i["billing_cycle"]] = {}

        bucket_key = f'{i["ownerid"]}/{i["instance_id"].lower()}'
        sum_data[i["billing_cycle"]][bucket_key] = {
            "standard_storage_sum": i["standard_storage_sum"],
            "infrequent_access_storage_sum": i["infrequent_access_storage_sum"],
            "archive_storage_sum": i["archive_storage_sum"],
            "cold_archive_storage_sum": i["cold_archive_storage_sum"],
            "get_request_count_sum": i["get_request_count_sum"],
            "put_request_count_sum": i["put_request_count_sum"],
            "internet_send_sum": i["internet_send_sum"],
            "cdn_send_sum": i["cdn_send_sum"]
        }
    return sum_data


def checkFileContent(file_obj):
    bio = BytesIO()
    for chunk in file_obj.chunks():
        bio.write(chunk)
    bio.seek(0)

    try:
        df = pd.read_csv(bio)
    except ValueError:
        df = pd.read_excel(bio)
    except Exception:
        return {"code": 100400, "message": "Excel 文件读取失败"}

    df = df.replace(to_replace=[r"\t|\n|\r", " "], value=["", ""], regex=True)

    select_cols = ['账期', '账号ID', '账号', 'Owner账号', '实例ID', '产品Code', '计费项', '应付金额', '计费项Code']
    try:
        all_data = df[select_cols].values
    except KeyError as ex:
        return {"code": 100400, "message": "Excel格式识别错误,请确认是从阿里云账单控制台导出的OSS明细!"}

    if 'oss' not in df["产品Code"].unique():
        return {"code": 100401, "message": "没有找到OSS的相关明细账单,请确认是从阿里云账单控制台导出的OSS明细!"}

    cleanAnalyAmount(df["账号ID"].unique(), df["账期"].unique())

    return {"code": 0, "data": all_data}


def cleanAnalyAmount(ownerids: list, billing_cycles: list):
    for ownerid in ownerids:
        for billing_cycle in billing_cycles:
            OssBill.objects.filter(
                ownerid=ownerid,
                billing_cycle=billing_cycle
            ).update(
                standard_storage_amount=0,
                infrequent_access_storage_amount=0,
                archive_storage_amount=0,
                cold_archive_storage_amount=0,
                get_request_count_amount=0,
                put_request_count_amount=0,
                internet_send_amount=0,
                cdn_send_amount=0,
                other_item_amount=0,
                pretax_amount=0,
            )


def analyOssBillAmount(file_obj):
    check_response = checkFileContent(file_obj)
    if check_response["code"] != 0:
        return check_response

    groupby_data = groupByOssBill()

    for data in check_response["data"]:
        billing_cycle, account_id, _, owner, instance_id, product_code, charging, pretax_amount, charging_code = data

        if product_code != "oss" or int(pretax_amount) == 0:
            continue

        CHARGE_M = {
            "Storage": "standard_storage",
            # "ChargedDatasize": ["infrequent_access_storage", "archive_storage"],
            "LessthanMonthDatasize": "infrequent_access_storage",
            "PutRequest": "put_request_count",
            "GetRequest": "get_request_count",
            "NetworkOut": "internet_send",
            "CdnOut": "cdn_send",
            # "ProcessI": "",
            "RetrievalData": "get_request_count"

        }
        objects = OssBill.objects.filter(
            billing_cycle=billing_cycle,
            ownerid=account_id,
            owner=owner,
            instance_id=instance_id
        ).values(
            "bucket_name", "standard_storage", "infrequent_access_storage",
            "archive_storage", 'cold_archive_storage',
            "get_request_count", "put_request_count",
            "internet_send", "cdn_send",
        )
        instance = f"{account_id}/{instance_id.lower()}"
        for obj in objects:
            if charging_code == "ChargedDatasize" and "archive" in instance_id.lower():
                sum_result = float(groupby_data[billing_cycle][instance]["archive_storage_sum"])
                amount = round(float(obj["archive_storage"]) / sum_result * pretax_amount, 2)
                OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=account_id, bucket_name=obj["bucket_name"]
                ).update(
                    archive_storage_amount=F('archive_storage_amount') + amount,
                    pretax_amount=F('pretax_amount') + amount
                )

            elif charging_code == "ChargedDatasize" and "ia" in instance_id.lower():
                sum_result = float(groupby_data[billing_cycle][instance]["infrequent_access_storage_sum"])
                amount = round(float(obj["infrequent_access_storage"]) / sum_result * pretax_amount, 2)
                OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=account_id, bucket_name=obj["bucket_name"]
                ).update(
                    infrequent_access_storage_amount=F('infrequent_access_storage_amount') + amount,
                    pretax_amount=F('pretax_amount') + amount
                )

            elif charging_code == "ProcessI":
                sum_result = float(groupby_data[billing_cycle][instance]["put_request_count_sum"])
                amount = round(float(obj["put_request_count"]) / sum_result * pretax_amount, 2)
                OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=account_id, instance_id=instance_id,
                    bucket_name=obj["bucket_name"]
                ).update(
                    put_request_count_amount=F('put_request_count_amount') + amount,
                    pretax_amount=F('pretax_amount') + amount
                )

            elif charging_code == "RetrievalData":
                sum_result = float(groupby_data[billing_cycle][instance]["get_request_count_sum"])
                amount = round(float(obj["get_request_count"]) / sum_result * pretax_amount, 2)
                OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=account_id, instance_id=instance_id,
                    bucket_name=obj["bucket_name"]
                ).update(
                    other_item_amount=F('other_item_amount') + amount,
                    pretax_amount=F('pretax_amount') + amount
                )

            elif charging_code not in CHARGE_M.keys():
                sum_result = float(groupby_data[billing_cycle][instance]["standard_storage_sum"])
                amount = round(float(obj["standard_storage"]) / sum_result * pretax_amount, 2)
                OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=account_id, instance_id=instance_id,
                    bucket_name=obj["bucket_name"]
                ).update(
                    other_item_amount=F('other_item_amount') + amount,
                    pretax_amount=F('pretax_amount') + amount
                )

            else:
                sum_result = float(groupby_data[billing_cycle][instance][CHARGE_M[charging_code] + "_sum"])
                amount = round(float(obj[CHARGE_M[charging_code]]) / sum_result * pretax_amount, 2)
                amount_item = CHARGE_M[charging_code] + '_amount'
                o = OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=account_id,
                    instance_id=instance_id, bucket_name=obj["bucket_name"]
                )
                o.update(**{'pretax_amount': F('pretax_amount') + amount, amount_item: F(amount_item) + amount})

    return {"code": 0, "message": "OSS桶分配金额完成"}


def queryset_analysis(df, values: str, columns, index):
    outfile = BytesIO()
    alys = pd.pivot_table(
        data=df,
        values=values,
        columns=columns,
        index=index,
        aggfunc=np.sum,
        margins=True,
        margins_name='合计',
        dropna=True,
        fill_value=0
    )

    writer = pd.ExcelWriter(outfile, engine='xlsxwriter')
    df.to_excel(writer, sheet_name='原始数据')
    alys.to_excel(writer, sheet_name='透视分析数据')

    workbook = writer.book
    border_format = {
        'border': 1,  # 边框宽度
        'top': 1,  # 上边框
        'left': 1,  # 左边框
        'right': 1,  # 右边框
        'bottom': 1  # 底边框
    }
    common_format = {
        'font_name': '微软雅黑',
        'align': 'center',
        'valign': 'vcenter',
        **border_format
    }
    header_format = workbook.add_format({
        **common_format,
        'fg_color': '#b9c7f9',
        'font_size': 14
    })

    money_format = workbook.add_format({'num_format': '#,##0.00', **common_format})
    worksheet_alys = writer.sheets['透视分析数据']
    worksheet_ori = writer.sheets['原始数据']

    (max_row_aly, max_col_aly) = alys.shape
    (max_row_ori, max_col_ori) = df.shape
    worksheet_alys.set_column(0, max_col_aly + 1, 25)
    worksheet_ori.set_column(0, max_col_ori + 1, 25)

    alys_index_headers = index + list(alys.columns.values)
    for col_num, value in enumerate(alys_index_headers):
        worksheet_alys.write(0, col_num, value, header_format)

    for col_num, value in enumerate(df.columns.values):
        worksheet_ori.write(0, col_num + 1, value, header_format)

    worksheet_alys.conditional_format(first_row=1, first_col=2,
                                      last_row=max_row_aly, last_col=max_col_aly + 1,
                                      options={"type": 'no_errors', 'format': money_format}
                                      )
    worksheet_ori.conditional_format(first_row=1, first_col=1,
                                     last_row=max_row_ori, last_col=max_col_ori,
                                     options={"type": 'no_errors', 'format': money_format}
                                     )
    writer.close()

    return outfile


def analysisOssBillAmountByAPI(billing_cycle, json_data):
    groupby_data = groupByOssBill()

    for data in json_data:
        if data["PretaxAmount"] == 0:
            continue

        CHARGE_M = {
            "Storage": "standard_storage",
            # "ChargedDatasize": ["infrequent_access_storage", "archive_storage"],
            "LessthanMonthDatasize": "infrequent_access_storage",
            "PutRequest": "put_request_count",
            "GetRequest": "get_request_count",
            "NetworkOut": "internet_send",
            "CdnOut": "cdn_send",
            # "ProcessI": "",
            "RetrievalData": "get_request_count"

        }
        objects = OssBill.objects.filter(
            billing_cycle=billing_cycle,
            ownerid=data["BillAccountID"],
            owner=data["BillAccountName"],
            instance_id=data["InstanceID"]
        ).values(
            "bucket_name", "standard_storage", "infrequent_access_storage",
            "archive_storage", 'cold_archive_storage',
            "get_request_count", "put_request_count",
            "internet_send", "cdn_send",
        )
        instance = f"{data['BillAccountID']}/{data['InstanceID'].lower()}"
        for obj in objects:
            if data['BillingItemCode'] == "ChargedDatasize" and "archive" in data['InstanceID'].lower():
                sum_result = float(groupby_data[billing_cycle][instance]["archive_storage_sum"])
                amount = round(float(obj["archive_storage"]) / sum_result * data["PretaxAmount"], 2)
                OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=data["BillAccountID"], bucket_name=obj["bucket_name"]
                ).update(
                    archive_storage_amount=F('archive_storage_amount') + amount,
                    pretax_amount=F('pretax_amount') + amount
                )

            elif data['BillingItemCode'] == "ChargedDatasize" and "ia" in data['InstanceID'].lower():
                sum_result = float(groupby_data[billing_cycle][instance]["infrequent_access_storage_sum"])
                amount = round(float(obj["infrequent_access_storage"]) / sum_result * data["PretaxAmount"], 2)
                OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=data["BillAccountID"], bucket_name=obj["bucket_name"]
                ).update(
                    infrequent_access_storage_amount=F('infrequent_access_storage_amount') + amount,
                    pretax_amount=F('pretax_amount') + amount
                )

            elif data['BillingItemCode'] == "ProcessI":
                sum_result = float(groupby_data[billing_cycle][instance]["put_request_count_sum"])
                amount = round(float(obj["put_request_count"]) / sum_result * data["PretaxAmount"], 2)
                OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=data["BillAccountID"], instance_id=data['InstanceID'],
                    bucket_name=obj["bucket_name"]
                ).update(
                    put_request_count_amount=F('put_request_count_amount') + amount,
                    pretax_amount=F('pretax_amount') + amount
                )

            elif data['BillingItemCode'] == "RetrievalData":
                sum_result = float(groupby_data[billing_cycle][instance]["get_request_count_sum"])
                amount = round(float(obj["get_request_count"]) / sum_result * data["PretaxAmount"], 2)
                OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=data["BillAccountID"], instance_id=data['InstanceID'],
                    bucket_name=obj["bucket_name"]
                ).update(
                    other_item_amount=F('other_item_amount') + amount,
                    pretax_amount=F('pretax_amount') + amount
                )

            elif data['BillingItemCode'] not in CHARGE_M.keys():
                sum_result = float(groupby_data[billing_cycle][instance]["standard_storage_sum"])
                amount = round(float(obj["standard_storage"]) / sum_result * data["PretaxAmount"], 2)
                OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=data["BillAccountID"], instance_id=data['InstanceID'],
                    bucket_name=obj["bucket_name"]
                ).update(
                    other_item_amount=F('other_item_amount') + amount,
                    pretax_amount=F('pretax_amount') + amount
                )

            else:
                sum_result = float(
                    groupby_data[billing_cycle][instance][CHARGE_M[data["BillingItemCode"]] + "_sum"])
                amount = round(float(obj[CHARGE_M[data["BillingItemCode"]]]) / sum_result * data["PretaxAmount"], 2)
                amount_item = CHARGE_M[data["BillingItemCode"]] + '_amount'
                o = OssBill.objects.filter(
                    billing_cycle=billing_cycle, ownerid=data["BillAccountID"],
                    instance_id=data['InstanceID'], bucket_name=obj["bucket_name"]
                )
                o.update(**{'pretax_amount': F('pretax_amount') + amount, amount_item: F(amount_item) + amount})