manyi/script/dealer_income_report.py

# coding=utf-8
import datetime
import os
from collections import OrderedDict

import pandas

from base import init_env
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "configs.testing")
init_env(interactive = False)

from apps.web.dealer.models import Dealer
from apps.web.dealer.proxy import DealerIncomeProxy
from apps.web.device.models import Group
from apilib.monetary import RMB

# 前台传入参数
USER_NAME = "17625905488"
GROUP_ID = "5f3baceb81958aa4eaf66d9e"
GROUP_ID = None
START_TIME = "2020-07-01 00:00:00"
END_TIME = "2020-08-25 00:00:00"

# 通过时间 或者通过 地址 来聚合
AGGREGATE_BY = "time"



d = Dealer.objects.filter(username=USER_NAME).order_by("-dateTimeAdded").first()

"""
1. 物业需要导出 数据报表 主要是包含收益以及其他信息
2. 导出的时候可以选择是按地址分类还是按时间分类
3. 地址分类的 是一个地址一条记录  时间分类的是 一天一条记录导出
4. 导出从电脑端进行导出，导出格式为excel
"""

groupIds =[_id for _id in Group.get_group_ids_of_dealer_and_partner(ownerId=str(d.id)) if not GROUP_ID or _id == GROUP_ID]

filters = {
    "groupId__in": groupIds
}

s = datetime.datetime.strptime(START_TIME, "%Y-%m-%d %H:%M:%S")
filters.update({"dateTimeAdded__gte": s})
e = datetime.datetime.strptime(END_TIME, "%Y-%m-%d %H:%M:%S")
filters.update({"dateTimeAdded__lt": e})


records = DealerIncomeProxy.objects.filter(
    **filters
)

dealerCache = dict()


# 接下来处理的是以什么分类聚合的问题
def handle_records(records):
    totalIncome = RMB(0)
    actualTotalIncome = RMB(0)

    partDict = dict()
    for record in records:
        totalIncome += RMB(record.totalAmount)
        actualTotalIncome += RMB(sum(record.actualAmountMap.values(), RMB(0)))
        for part in record.partition:
            if part["role"] == "agent":
                continue
            if RMB(part["money"]) == RMB(0.00):
                continue
            partDict[part["id"]] = partDict.setdefault(part["id"], RMB(0)) + part["money"]

    ledgerInfo = ""
    # from pprint import pprint
    print partDict
    for k, _v in partDict.items():
        dealer = Dealer.objects.filter(id=k).only("nickname", "username").first()
        ledgerInfo += "{}-{}-{}\r\n".format(dealer.nickname, dealer.username, _v)

    return {
        "totalIncome": totalIncome,
        "actualTotalIncome": actualTotalIncome,
        "ledgerInfo": ledgerInfo
    }


if AGGREGATE_BY == "time":
    res = {(s+datetime.timedelta(day)).strftime("%Y-%m-%d"): list() for day in range((e-s).days)}
    for record in records:
       res[record.date].append(record)

    data = list()
    for key, items in res.items():
        d = handle_records(items)
        d.update({"time": key})
        data.append(d)

    writeDataList = list()
    a = RMB(0)
    b = RMB(0)
    for v in sorted(data, key=lambda x: x["time"]):
        tempData = [
            (u"日期", v.get("time")),
            (u"总收益", "{0:.2f}".format(float(v.get("totalIncome")))),
            (u"总分帐金额", "{0:.2f}".format(float(v.get("actualTotalIncome")))),
            (u"分账明细", v.get("ledgerInfo"))
        ]

        writeDataList.append(OrderedDict(tempData))

        a += v.get("totalIncome")
        b += v.get("actualTotalIncome")

    else:
        tempData = [
            (u"日期", u"总计"),
            (u"总收益", "{:.2f}".format(float(a))),
            (u"总分帐金额", "{:.2f}".format(float(b))),
            (u"分账明细", u"")
        ]
        writeDataList.append(OrderedDict(tempData))

    df = pandas.DataFrame(writeDataList)
    df.to_excel("time_test.xlsx", sheet_name='sheet1', index=False)

elif AGGREGATE_BY == "group":
    res = {g: list() for g in groupIds}

    for record in records:
        res[str(record.groupId)].append(record)

    data = list()
    for key, items in res.items():
        d = handle_records(items)
        group = Group.get_group(key)
        d.update({"groupName": group.get("groupName")})
        data.append(d)

    writeDataList = list()
    a = RMB(0)
    b = RMB(0)
    for v in sorted(data, key=lambda x: x["groupName"]):
        tempData = [
            (u"地址名称", v.get("groupName")),
            (u"总收益", "{0:.2f}".format(float(v.get("totalIncome")))),
            (u"总分帐金额", "{0:.2f}".format(float(v.get("actualTotalIncome")))),
            (u"分账明细", v.get("ledgerInfo"))
        ]

        a += v.get("totalIncome")
        b += v.get("actualTotalIncome")
        writeDataList.append(OrderedDict(tempData))
    else:
        tempData = [
            (u"地址名称", u"总计"),
            (u"总收益", "{:.2f}".format(float(a))),
            (u"总分帐金额", "{:.2f}".format(float(b))),
            (u"分账明细", u"")
        ]
        writeDataList.append(OrderedDict(tempData))

    df = pandas.DataFrame(writeDataList)
    df.to_excel("group_test.xlsx", sheet_name='sheet1', index=False)


else:
    print "please choose the type of aggregate!"