manyi/script/2020_12_31_sim_card.py

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

os.environ["DJANGO_SETTINGS_MODULE"] = "configs.production"
import pandas as pd

from base import init_env

init_env(False)

from apps.web.device.models import Device, Group
from apps.web.dealer.models import Dealer
from apps.web.agent.models import Agent
from apps.web.device.models import SIMCard


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


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


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 main(ownerId):
    if not ownerId:
        return
    dataList = list()
    t = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    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


if __name__ == '__main__':
    phone = raw_input("输入经销商账号：")
    basePath = raw_input("输入文件夹路径：")
    for dealer in Dealer.objects.filter(username=phone):
        try:
            a = Agent.objects.get(id=dealer.agentId)

            dataLis = main(str(dealer.id))
            write_to_csv(a, dealer, dataLis, basePath)
        except Exception as e:
            print e
            continue