manyi/script/check_launder_money2.py

# coding=utf-8
import csv
import sys
import os
import pandas
import datetime
from collections import OrderedDict
from base import init_env

env = sys.argv[1]
os.environ.setdefault('DJANGO_SETTINGS_MODULE', env)
init_env(interactive=False)

from apps.web.user.models import MyUser, RechargeRecord
from apps.web.dealer.models import Dealer
from apps.web.device.models import Group, Device
from apilib.monetary import RMB



def get_recharge_record():
    matchFilters = {
        "dateTimeAdded": {
            "$gte": datetime.datetime.now() - datetime.timedelta(days=7),
        },
        "money": {
            "$gt": 0
        },
        "result": RechargeRecord.PayResult.SUCCESS,
    }
    projectFilters = {
        "_id": 1,
        "devNo": 1,
        "money": 1
    }
    groupFilters = {
        "_id": "$devNo",
        "sum_recharge": {"$sum": "$money"},
        "recharge_count": {"$sum": 1}

    }

    renameProject = {
        "_id": 0,
        "devNo": "$_id",
        "recharge": "$sum_recharge",
        "count": "$recharge_count"
    }

    records = RechargeRecord.get_collection().aggregate([
        {"$match": matchFilters},
        {"$project": projectFilters},
        {"$group": groupFilters},
        {"$project": renameProject}
    ])

    for record in records:
        yield record


dataList = list()
resList = list()
dealerMap = dict()

def func1():
    for item in get_recharge_record():
        devNo = item.get("devNo")
        if devNo in dealerMap:
            dealerName = dealerMap.get(devNo)
        else:
            try:
                dev = Device.get_dev(devNo)
                dealer = Dealer.objects.filter(id=dev.get("ownerId")).only("username", "nickname").first()
                dealerName = dealer.nickname + "---" + dealer.username
            except Exception as e:
                dealerName = "不存在的dealerName"

            dealerMap[devNo] = dealerName
        item.update({"dealerName": dealerName})
        resList.append(item)

def func2():
    result = dict()
    for item in resList:
        dealerName = item.get("dealerName")

        if dealerName in result:
            c = result.get(dealerName)
            c.update({
                "recharge": c.get("recharge") +float(str(item.get("recharge"))),
                "dev": c.get("dev") + 1,
                "count": c.get("count") + item.get("count")
            })
        else:
            result[dealerName] = {
                "recharge": float(str(item.get("recharge"))),
                "dev": 1,
                "count": item.get("count")
            }

    return result


def main():
    func1()
    for key, value in func2().items():

        tempData = [
            (u"经销商", key),
            (u"有效订单总数", value.get("count")),
            (u"产生订单的设备数量", value.get("dev")),
            (u"订单充值总金额", value.get("recharge")),
            (u"订单设备比例", value.get("count")/(value.get("dev") * 1.0))
        ]

        dataList.append(OrderedDict(tempData))

    dataList.sort(key=lambda x:x.get(u"订单设备比例"))


    dataForm = pandas.DataFrame(dataList)
    writer = pandas.ExcelWriter(u"/var/www/upload/launderCheck/统计.xlsx")
    dataForm.to_excel(writer, sheet_name=u"统计", index=False)
    writer.save()


if __name__ == '__main__':
    main()