manyi/script/yinguo_device_sim.py

# coding=utf-8
import csv
import datetime
import os

import pandas as pd

from base import init_env

init_env(True)
from apps.web.agent.models import Agent
from apps.web.dealer.models import Dealer
from apps.web.device.models import Device, SIMCard, Group

logicalCodes = """
352081
302219
100555
302232
108410
100987
352192
372184
112159
352161
108411
352170
362536
352080
352193
352163
302217
112129
362486
362487
302203
302275
352166
302202
"""

logicalCodeList = logicalCodes.split()

dealerSet = set()


def write_to_csv(agent, client, dataList, path):

    dataList.sort(key=lambda x: x.get(u"流量卡过期时间"), reverse=True)

    if not path:
        path = "/var/www/UserServer/script/2020_12_31_sim"
    headers = [
        u"设备编号",
        u"当前二维码号",
        u"流量卡",
        u"流量卡状态",
        u"流量卡过期时间",
        u"流量卡供应商",
        u"设备所属组",
        u"软件版本",
        u"核心版本",
        u"驱动编码",
        u"模块版本",
        u"是否离线",
        u"离线时间",
        u"统计时间",
    ]
    fileName = os.path.join(path, u"{}_{}_{}.csv".format(client.nickname, client.username, agent.nickname))
    with open(fileName, "w") as f:
        writer = csv.DictWriter(f, fieldnames=headers)
        writer.writeheader()
        writer.writerows(dataList)


    excelFileName = fileName.replace(".csv", ".xlsx")
    csvFile = pd.read_csv(fileName, encoding='utf-8')
    csvFile.to_excel(excelFileName, sheet_name=u"模块统计")


def get_device(ownerId):
    return Device.objects.filter(logicalCode__in=logicalCodeList)


def get_box_type(ver):
    verLis = ver.split(".")
    if verLis[0] in ["v50", "v60", "v7"]:
        return "4G"
    else:
        return "2G"


def getDataList(ownerId):
    # if not ownerId:
    #     return
    dataList = list()
    t = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    # for dev in get_device(ownerId):
    for dev in get_device(ownerId):
        try:
            simCard = SIMCard.objects.get(iccid=dev.iccid)
        except Exception:
            print "dev <{}> iccid not found!".format(dev.devNo)
            simCard = SIMCard()


        group = Group.get_group(dev.groupId) or dict()

        softVer = dev.softVer

        dev = Device.get_dev(dev.devNo)
        online = dev.online if dev else ""
        offTime = datetime.datetime.fromtimestamp(int(str(dev.offTime)[0:10])).strftime("%Y-%m-%d") if dev.offTime else ""

        data = {
            u"设备编号": "IMEI_{}".format(dev.devNo),
            u"当前二维码号": dev.logicalCode,

            u"流量卡": dev.iccid,
            u"流量卡状态": dev.simStatus,
            u"流量卡过期时间": datetime.datetime.strftime(simCard.expireTime, "%Y-%m-%d %H:%M:%S") if simCard.expireTime else "not expired time",
            u"流量卡供应商": simCard.supplier,

            u"设备所属组": group.get("groupName", u"未知地址"),

            u"软件版本": softVer,
            u"核心版本": dev.coreVer,
            u"驱动编码": dev.devType.get("code"),
            u"模块版本": get_box_type(softVer),

            u"是否离线": str(online),
            u"离线时间": str(offTime),
            u"统计时间": t
        }

        dataList.append(data)
    print u"设备统计完成"
    return dataList


def main():

    # for dev in Device.objects.filter(logicalCode__in=logicalCodeList):
    #     dealer = Dealer.objects.get(id=dev.ownerId)
    #     agent = Agent.objects.get(id=dealer.agentId)
    #     dealerSet.add("{}-{}-{}-{}".format(agent.nickname, dealer.nickname, dealer.username, str(dealer.id)))
    #
    # for dealerInfo in dealerSet:
    #     dealerId = dealerInfo.split('-')[-1]
    #     dealer = Dealer.objects.get(id=dealerId)
    #     agent = Agent.objects.get(id=dealer.agentId)

    device = Device.objects.get(logicalCode=logicalCodeList[0])
    dealer = Dealer.objects.get(id=device.ownerId)
    dealerId = str(dealer.id)
    agent = Agent.objects.get(id=dealer.agentId)

    dataList = getDataList(dealerId)
    print "dataList"
    print dataList
    write_to_csv(agent, dealer, dataList, "")


if __name__ == '__main__':
    main()