【工单查询脚本】

# -*- coding: utf-8 -*-
import openpyxl as xl
import datetime


def possZero(tem):
    if tem == 0 or tem == 0.0:
        return 0.0000000001
    return tem


def isCurWeek(tem):
    now = datetime.datetime.now()
    startTime = now - datetime.timedelta(now.weekday())
    startTime = datetime.datetime(startTime.year, startTime.month, startTime.day, 0, 0, 0)
    endTime = startTime + datetime.timedelta(6)
    endTime = datetime.datetime(endTime.year, endTime.month, endTime.day, 23, 59, 59)
    if tem >= startTime and tem <= endTime:
        return True
    else:
        return False


def nameContains(name, names):
    for ii in names:
        if name.__contains__(ii):
            return True
    return False

# 省份名字
provinceNames = ['青海', '黑龙江', '吉林', '河北', '山西', '福建']
nodeNames = ['结束', '需求验收', '关闭']
needInNodes = ['需求实现', "需求分析安排", "需求分析", "分析结果反馈"]
resMap = {}
resMap["本周上线需求分省量"] = {}
resMap["本周上线需求总量"] = 0
resMap["本周上线需求30天完成"] = 0
resMap["本周上线需求及时交付量"] = 0
resMap["已上线需求总量"] = 0
resMap["已上线需求量30天完成"] = 0
resMap["已上线需求量及时交付"] = 0
resMap["遗留分中心需求分省量"] = {}
resMap["遗留分中心需求总量"] = 0
resMap["遗留分中心需求明细"] = []
resMap["本周上线需求明细"] = []
resMap["超30天还未完成需求"] = []
for name in provinceNames:
    resMap["本周上线需求分省量"][name] = 0
    resMap["遗留分中心需求分省量"][name] = 0
#  修改文件路径
wb = xl.load_workbook(r'D:\download\Google\查密码看短信-20230327084933\20230327084933新需求管理流程需求统计报表.xlsx')

sheet = wb.get_sheet_by_name(wb.get_sheet_names()[0])
for i in range(2, sheet.max_row+1):
    # print(i)
    needNum = sheet.cell(i, 1).value
    needName = sheet.cell(i, 3).value
    acceptName = sheet.cell(i, 17).value
    if not acceptName.__contains__("通信云需求"):
        continue
    curNode = sheet.cell(i, 24).value
    arriveTime = sheet.cell(i, 49).value
    if arriveTime != "":
        arriveTime = datetime.datetime.strptime(arriveTime.split(' ')[0], "%Y-%m-%d")
    # 判断是否符合需求受理方
    if not nameContains(acceptName, provinceNames):
        continue
    fenName = acceptName.replace("通信云需求-", "").replace("分公司", "")  # 提取公司名字
    # 遗留需求统计
    if curNode in needInNodes:
        if arriveTime == "":
            continue
        if curNode == "分析结果反馈" and sheet.cell(i, 55).value == "拒绝":
            pass
        else:
            resMap["遗留分中心需求总量"] += 1
            resMap["遗留分中心需求分省量"][fenName] += 1
            resMap["遗留分中心需求明细"].append(needNum + "_" + needName+"-"+fenName+"_需求到达时间："+str(arriveTime))
    # 30天需求告警
    if curNode in ["需求实现", "需求实现确认"]:
        if (datetime.datetime.now() - arriveTime).days >= 23:
            resMap["超30天还未完成需求"].append("【{}天内超期】".format(30-(datetime.datetime.now() - arriveTime).days)
                                                +needNum + "_" + needName+"-"+fenName+"_需求到达时间："+str(arriveTime))
    # 判断当前节点是否符合
    if curNode not in nodeNames:
        continue
    # print(acceptName)
    # 计算需求流转出部门的最终时间
    achieveTime = sheet.cell(i, 63).value
    conformTime = sheet.cell(i, 65).value
    if achieveTime == '' and conformTime == '':
        continue
    if conformTime != '':
        possEndTime = conformTime
    elif achieveTime != '':
        possEndTime = achieveTime
    possEndTime = datetime.datetime.strptime(possEndTime.split(' ')[0], "%Y-%m-%d")
    resMap["已上线需求总量"] += 1
    if isCurWeek(possEndTime):
        resMap["本周上线需求总量"] += 1
        resMap["本周上线需求分省量"][fenName] += 1
        resMap["本周上线需求明细"].append(needNum+"_"+needName+"-"+fenName)
    arriveTime = datetime.datetime.strptime(str(arriveTime).split(' ')[0], "%Y-%m-%d")
    if (possEndTime - arriveTime).days <= 30:
        resMap["已上线需求量30天完成"] += 1
        if isCurWeek(possEndTime):
            resMap["本周上线需求30天完成"] += 1
    #  计算需求计划交付时间
    planTime = sheet.cell(i, 22).value
    expectTime = sheet.cell(i, 23).value
    if planTime == '' and expectTime == '':
        resMap["已上线需求量及时交付"] += 1
        if isCurWeek(possEndTime):
            resMap["本周上线需求及时交付量"] += 1
        continue
    if planTime != '':
        planTime = planTime.split(",")[0]
        possStartTime = planTime + ' 00:00:00'
    elif expectTime != '':
        possStartTime = expectTime
    possStartTime = datetime.datetime.strptime(possStartTime.split(' ')[0], "%Y-%m-%d")
    if possStartTime >= possEndTime:
        resMap["已上线需求量及时交付"] += 1
        if isCurWeek(possEndTime):
            resMap["本周上线需求及时交付量"] += 1
print(resMap)
print("本周上线需求分省量", resMap["本周上线需求分省量"])
print("本周上线需求总量", resMap["本周上线需求总量"])
print("本周上线需求30天完成", resMap["本周上线需求30天完成"])
print("本周上线需求30天内完成率", resMap['本周上线需求30天完成'] / possZero(resMap["本周上线需求总量"]))
print("本周上线需求及时交付量", resMap["本周上线需求及时交付量"])
print("本周上线需求及时交付率", resMap['本周上线需求及时交付量'] / possZero(resMap["本周上线需求总量"]))
print("已上线需求总量", resMap["已上线需求总量"])
print("已上线需求量30天完成", resMap["已上线需求量30天完成"])
print("已上线需求量30天完成率", resMap['已上线需求量30天完成'] / possZero(resMap["已上线需求总量"]))
print("已上线需求量及时交付", resMap["已上线需求量及时交付"])
print("已上线需求量及时交付率", resMap['已上线需求量及时交付'] / possZero(resMap["已上线需求总量"]))
print("遗留分中心需求分省量", resMap["遗留分中心需求分省量"])
print("遗留分中心需求总量", resMap["遗留分中心需求总量"])
print("超30天还未完成需求", resMap["超30天还未完成需求"])
print("30天内未完成需求告警")
for need in resMap["超30天还未完成需求"]:
    print(need)
print("遗留分中心需求明细")
print("遗留分中心需求明细", resMap["遗留分中心需求明细"])
for need in resMap["遗留分中心需求明细"]:
    print(need)
print("本周上线需求明细")
print("本周上线需求明细", resMap["本周上线需求明细"])
for need in resMap["本周上线需求明细"]:
    print(need)