基于redmine的测试日报终于实现99.99%的自动化了

龚振
2023-12-01

今年一月份的时候,我的博客出了一版自动采集数据生成excel的脚本,其实节省了测试组的很多数据处理时间

https://blog.csdn.net/FBX_fbx_FBX/article/details/104051445  

我觉得时间还可以再节省更多,就在工作之余又慢慢尝试多项改进,实现了99.99%的自动化--因为有每个人每天的进度变更,所以不可能实现完全自动化

说一下本次基于上次的变更

1.不再使用excel作为中间媒介,直接写成html

2.不再手动制图,使用matplotlib绘制

3.精简数据库存储内容,使用一张表存储历史统计数据

4.实现代码与配置分离

5.自动发送报告


优先看下配置文件properties.init 

[Mysql]
host=192.168.11.111
port=3306
user=root
passwd=Z121zZ
db=test_report

# host 为redmine的地址,在浏览器查看接口参数就可以看到项目的其他参数
[Redmine]
host=http://2.2.8.8:19507
username=fengbingxue
pw=123456
project_name=3d-room
fixed_version_id=986   

[Plan]
#start_time和end_time 数据库中取的数据的时间段
start_time = 2020-05-01
end_time = 2020-07-01
#以下三项配置,为测试计划的信息
test_time = 2019/2/12-2020/2/22
test_plan = http://2.2.8.8:19507/issues/67563
tester = 冯冰雪,冯冰雪,冯冰雪

 

主体部分:
导入以下三方库

from datetime import datetime
from redminelib import Redmine
import pymysql
import matplotlib.pyplot as plt
import configparser

引用配置文件内容

config = configparser.ConfigParser()
config.read("./properties.init")
config_mysql=config['Mysql']
config_Redmine=config['Redmine']
config_Plan=config['Plan']

文件分析模块部分,这一版加入了每日变动数据的读入(写在一起便于查找维护):

# 缺陷对应人员的数量及缺陷模块数量
def current_day():
    resolve_user_list,module_list,all_user_list,march = [],[],[],[]
    # test_rate edition
    i = 0
    with open('curren.txt', 'r', encoding='UTF-8') as file_current_read:
        current_lines = file_current_read.readlines()
        for line in current_lines:
            i += 1
            if i == 2: install_time = line
            if i == 4: branch = line
            if i == 6: git_log = line
            if int(i) > 7:
                line = line.split(',')
                march.append(line)
    test_edition=[install_time,branch,git_log]
    # user
    with open('user.txt', 'r',encoding='UTF-8') as file_user_read:
        user_lines = file_user_read.readlines()
        for user in user_lines:
            n = 0
            j = 0
            for issue in issues:
                if issue.assigned_to.name + '\n' == user:
                    j +=1
                    if issue.status.name == "已解决":
                        n += 1
            resolve_user_list.append([user[:-1],n]) #已解决的人员分布
            all_user_list.append([user[:-1],j])
        # module
        with open('module.txt', 'r', encoding='UTF-8') as file_module_read:
            module_lines = file_module_read.readlines()
            for module in module_lines:
                n = 0
                for issue in issues:
                    if issue.custom_fields._resources[2]['value'] + '\n' == module:
                        n += 1
                module_list.append([module[:-1],n])
    return resolve_user_list,module_list,all_user_list,march,test_edition
    # current_day[0] 已解决人员对应# current_day[1] 模块对应# current_day[2] 人员对应 #current_day[3] 测试进度  #current_day[4] 版本信息

redmine数据分析模块没有变动(特别说明:在我所在项目组,我们把阻塞缺陷定义为重大+紧急,如果你不一样,你可以在以下代码,stop_list部分修改)

def analysis():
    # 严重 grave 致命 deadly 紧急 urgent 已解决 resolved  超时 timeout 阻塞 stop
    become_list,grave_list,stop_list = [],[],[]  #退化缺陷 、重大、阻塞
    new_num, become,grave_num, deadly_num, timeout_num,stop_num,resolved_all = 0,0,0, 0, 0, 0, 0
    for issue in issues:
        update_time = str(issue.updated_on.year) + "-" + str(issue.updated_on.month) + "-" + str(issue.updated_on.day)
        updatetime_date = datetime.strptime(update_time, '%Y-%m-%d')
        today_date = datetime.strptime(today, '%Y-%m-%d')
        delta = today_date - updatetime_date
        if delta.days >= 7: timeout_num += 1
        if issue.custom_fields._resources[0]['value'] == "重大" or issue.custom_fields._resources[0]['value'] == "致命": deadly_num += 1
        if issue.custom_fields._resources[3]['value'] == '1':
            become += 1
            become_list.append([become,issue.assigned_to.name, issue.id,issue.subject])
        if issue.custom_fields._resources[0]['value'] == "重大" and issue.priority['name'] == "紧急":
            stop_num += 1
            stop_list.append([stop_num,issue.assigned_to.name, issue.id,issue.subject])
        if issue.status.name == "已解决": resolved_all += 1
        create_time = str(issue.created_on.year) + "-" + str(issue.created_on.month) + "-" + str(issue.created_on.day)
        if create_time == today:
            new_num += 1
            if issue.custom_fields._resources[0]['value'] == "重大":
                grave_num += 1
                grave_list.append([grave_num,issue.status.name, issue.id, issue.subject])
    analysis_list=[grave_list,stop_list,grave_num,deadly_num,resolved_all,timeout_num,new_num,become,become_list]
    return analysis_list
    #重大缺陷列表 [0]#阻塞缺陷列表 [1]#重大缺陷个数 [2] #重大/致命缺陷个数 [3]#已解决数 [4]# 超过一周未更新数 [5]#今日新增数 [6] # 退化数 [7] # 退化列表 [8]

数据库存储部分,精简了很多,只存储了一个表,表里只有三列:

def SandP_mysql():  # 2 3 6 7 点
    x, new_num = 0, 0
    summer = [['时间', '已解决', '今日新建', '待解决问题']]
    daily_row = [['时间', '新建', '进行中', '已解决', '反馈', '阻塞', '推迟', 'Reopen']]
    new = 0
    new_all, ongoing_all, feedback_all, delay_all, reopen_all, block_all, resolved_all = 0, 0, 0, 0, 0, 0, 0
    for issue in issues:
        if issue.status.name == "新建": new_all += 1
        if issue.status.name == "进行中": ongoing_all += 1
        if issue.status.name == "已解决": resolved_all += 1
        if issue.status.name == "反馈": feedback_all += 1
        if issue.status.name == "推迟": delay_all += 1
        if issue.status.name == "重新打开": reopen_all += 1
        if issue.status.name == "阻塞": block_all += 1
        create_time = str(issue.created_on.year) + "-" + str(issue.created_on.month) + "-" + str(issue.created_on.day)
        if create_time == today:
            new_num += 1
            if issue.status.name == "新建": new += 1
    totle_unresolved = new_all + ongoing_all + feedback_all + delay_all + reopen_all + block_all  # 未解决
    daily_row.append([today, new_all, ongoing_all, resolved_all, feedback_all,block_all, delay_all,reopen_all])
    db = pymysql.connect(host=config_mysql['host'], port=int(config_mysql['port']), user=config_mysql['user'], passwd=config_mysql['passwd'], db=config_mysql['db'])
    cursor = db.cursor()
    sql_summer = "replace into summer_data(time_t,resolved,new,unresolved) VALUES ('%s','%s','%s','%s')" % (today,resolved_all,new,totle_unresolved)
    try:
        cursor.execute(sql_summer)
        db.commit()
        print("ok")
    except:
        # 如果发生错误则回滚
        db.rollback()
        print("no")
    try:
        sql_summer = "SELECT * FROM summer_data WHERE time_t BETWEEN '%s' and '%s'" % (config_Plan['start_time'],config_Plan['end_time'])
        cursor.execute(sql_summer)  # 执行SQL语句
        results_summer = cursor.fetchall()  # 获取所有记录列表
        for row in results_summer:
            time_summer = row[0]
            resolve_summer = row[1]
            new_summer = row[2]
            unresolved_summer = row[-1]
            summer.append([str(time_summer),resolve_summer, new_summer,unresolved_summer])
        print('ok')
    except:
        print("Error: unable to fetch data")
    db.close()
    return summer, daily_row

新增的是matplotlib画图部分的代码,分别是折线图、柱状图、条形图三个:

# 折线图
def chart_line(chart_value,chart_name,img_name):
    x_value=[]
    y1_value,y2_value,y3_value=[],[],[]
    for i in range(1, len(chart_value)):
        if i > 0:
            x_value.append(chart_value[i][0])
            y1_value.append(chart_value[i][1])
            y2_value.append(chart_value[i][2])
            y3_value.append(chart_value[i][3])
    plt.figure(figsize=(17,6))  # 设置画布大小
    plt.grid(axis="y",linestyle='-.') #设置画布背景,横向,虚线
    plt.xticks(rotation=45) #设置x轴展示标签旋转度数
    plt.style.use('ggplot')
    plt.title(chart_name) # 设置图标名称
    line1, =plt.plot(x_value, y1_value,color='#4876FF', marker='*')
    line2, =plt.plot(x_value, y2_value, color='#228B22', marker='*')
    line3, =plt.plot(x_value, y3_value, color='#8B3A62', marker='*')
    plt.legend((line1, line2, line3), ('已解决', '今日新增', '待解决问题'))  # 绘图,并添加标签
    plt.rcParams['font.sans-serif'] = ['simsun']  # 显示中文标签,没有字体
    plt.rcParams['axes.unicode_minus'] = False
    for a, b in zip(x_value, y1_value):
        plt.text
        plt.text(a, b + 0.05, '%.0f' % b, ha='center', va='bottom', fontsize=7)  # 折线添加指标值
    for a, b in zip(x_value, y2_value):
        plt.text
        plt.text(a, b + 0.05, '%.0f' % b, ha='center', va='bottom', fontsize=7)
    for a, b in zip(x_value, y3_value):
        plt.text
        plt.text(a, b + 0.05, '%.0f' % b, ha='center', va='bottom', fontsize=7)
    plt.savefig(img_name+'.png')  # 保存数据到本地
    plt.clf()
    plt.close()
    #plt.show()

#柱状图
def chart_column(chart_value,chart_name,img_name):
    x_value = chart_value[0]
    y_value = chart_value[1]
    plt.bar(x_value[1:], y_value[1:],label='num')
    # plt.figure(figsize=(20, 10))  # 设置画布大小
    plt.grid(axis="y", linestyle='-.')  # 设置画布背景,横向,虚线
    # plt.xticks(rotation=90)  # 设置x轴展示标签旋转度数
    # plt.style.use('ggplot')
    plt.rcParams['font.sans-serif'] = ['simsun']  # 显示中文标签,没有字体
    plt.rcParams['axes.unicode_minus'] = False
    plt.title(chart_name)  # 设置图标名称
    for a, b in zip(x_value[1:], y_value[1:]):
        plt.text
        plt.text(a, b + 0.05, '%.0f' % b, ha='center', va='bottom', fontsize=7)  # 折线添加指标值
    plt.savefig(img_name+'.png')  # 保存数据到本地
    #plt.show()
    plt.clf()
    plt.close()

#条形图
def chart_barh(chart_value,chart_name,img_name):
    x_value,y_value = [],[]
    for line in chart_value:
        x_value.append(line[0])
        y_value.append(line[1])
    plt.barh(x_value, y_value ,label='num')
    # # plt.figure(figsize=(20, 10))  # 设置画布大小
    plt.grid(axis="x", linestyle='-.')  # 设置画布背景,横向,虚线
    plt.title(chart_name)  # 设置图标名称
    plt.savefig(img_name+'.png')  # 保存数据到本地
    #plt.show()
    plt.clf()
    plt.close()

新增删除历史生成的文件的函数,因为我的文件名字都是写好的,所以这里文件名称直接写死了,你也可以放到一个文件夹里直接全删除,这是我之前想实现的一步,但是忘记了··就先这个样吧~

def rm_path():
    import os
    path_list = ['daily_report.html','daily_summary_chart.png','ticet_module_chart.png','ticet_people_chart.png','ticet_resolve_chart.png','ticket_summary_chart.png']
    for path in path_list:
        if os.path.exists(path):
            os.remove(path)
        else:
            print('no such file')  # 则返回文件不存在

最后,直接用携带每日的分析数据去自动写一个html存在本地,便于邮件发送的代码去调用

这里真的尝试了好多方法,也其实和我能力有关,做不出来我满意的效果,最后选了一个最笨的(就是以下函数了),但是实现出来的效果是最接近我们手动调整的样子----满意 O(∩_∩)O

def write_html():
    person_testrate=current_day()
    condition=analysis()
    daily_summer=SandP_mysql()
    daily=daily_summer[1]
    #----画图----
    summery_line = daily_summer[0]    # 折线图 三个
    daily_colume = daily_summer[1]    #柱状图 竖着
    ticet_people = person_testrate[2]
    ticet_resolve = person_testrate[0]
    ticet_module = person_testrate[1]
    chart_line(summery_line, 'ticket总览', 'ticket_summary_chart')
    chart_column(daily_colume, '今日缺陷状态汇总', 'daily_summary_chart')
    chart_barh(ticet_people, '所有ticket人员对应', 'ticet_people_chart')
    chart_barh(ticet_resolve, '已解决缺陷人员对应', 'ticet_resolve_chart')
    chart_barh(ticet_module, '所有ticket模块对应', 'ticet_module_chart')
    #-----------html生成---------
    file_html = 'daily_report.html'  # html
    with open(file_html, mode='a') as filename:
        filename.write('<html><meta http-equiv="Content-Type" content="text/html; charset=utf-8">'
                       '<body link="blue" vlink="purple">')
        filename.write('<div style="margin-top: 30px">1.测试计划</div>'
                       '<table border="1" cellspacing="0" width="440">'
                       '<tr><td>时间安排</td><td>'+str(config_Plan['test_time'])+'</td></tr>'
                       '<tr><td>测试计划</td><td>'+str(config_Plan['test_plan'])+'</td></tr>'
                       '<tr><td>人员安排</td><td>'+str(config_Plan['tester'])+'</td></tr>'
                       '</table>')
        filename.write('<div style="margin-top: 30px">2.测试版本</div>'
                       '<table border="1" cellspacing="0" width="740">'
                       '<tr><td>序号</td><td>安装日期</td><td>branch</td><td>commit ID</td></tr>'
                       '<tr><td>1</td><td>'+str(person_testrate[4][0])+'</td><td>'+str(person_testrate[4][1])+'</td>'
                       '<td>'+str(person_testrate[4][2])+'</td></tr>'
                       '</table>')
        #   test_rate
        filename.write('<div style="margin-top: 30px">3.测试进度</div><table border="1" cellspacing="0" width="640">'
                       '<tr><td width="100px">模块名称</td><td width="75px">测试人员</td><td width="300px">测试内容及进度</td></tr>')
        for testdate in person_testrate[3]:
            filename.write('<tr><td>'+str(testdate[0])+'</td><td>'+str(testdate[1])+'</td><td>'+str(testdate[2])+'</td></tr>')
        filename.write('</table>')
        # -------循环-----
        filename.write('<div style="margin-top: 30px">4.'+str(today)+'今日测试情况</div>')
        filename.write('<table border="1" cellspacing="0" width="140" >'
                       '<tr><td>新增缺陷数</td><td>'+str(condition[6])+'</td></tr></table>')
        filename.write('<div style="margin-top: 30px">5.ticket汇总情况</div><div>'
                       '<img src="cid:image1" width="1240px" height="600px"></div>'  #  折线图
                       '<table border="1" cellspacing="0" width="740" style="margin-top: 30px">'
                       '<tr><td>时间</td><td>新建</td><td>进行中</td><td>已解决</td><td>反馈</td><td>阻塞</td><td>推迟</td><td>Reopen</td></tr>'
                       '<tr><td>'+str(daily[1][0])+'</td><td>'+str(daily[1][1])+'</td>'
                       '<td>'+str(daily[1][2])+'</td><td>'+str(daily[1][3])+'</td>'
                       '<td>'+str(daily[1][4])+'</td><td>'+str(daily[1][5])+'</td>'
                       '<td>'+str(daily[1][6])+'</td><td>'+str(daily[1][7])+'</td></tr>'
                       '</table>')
        filename.write('<div style="margin-top: 10px">'
                       '<img src="cid:image2" width="640px" height="400px"></div>' # 柱状图
                       '<table border="1" cellspacing="0" width="740" style="margin-top: 30px"><tr>'
                       '<td>超过一周未更新</td><td>'+str(condition[5])+'</td><td>退化的ticket数</td><td>'+str(condition[7])+'</td>'
                       '<td>致命/重大ticket数</td><td>'+str(condition[3])+'</td><td>已解决缺陷</td><td>'+str(condition[4])+'</td>'
                       '<td>今日新建数</td><td>'+str(condition[6])+'</td></tr></table>')
        filename.write('<div style="margin-top: 30px">6.缺陷概况</div><div style="margin-top: 2px">'
                       '<div style="margin-top: 2px">退化问题列表       </div><table border="1" cellspacing="0" width="440">'
                       '<tr><td>序号</td><td>关系人</td><td>ticket_ID</td><td>ticket详情</td></tr>')
        if condition[8]:
            for back in enumerate(condition[8]):
                filename.write('<tr><td>'+str(back[0]) +'</td><td>'+str(back[1])+'</td><td>'+str(back[2])+'</td><td>'+str(back[3])+'</td></tr>')
        filename.write('</table>')

        filename.write('<div style="margin-top: 10px">严重问题列表</div><table border="1" cellspacing="0" width="440">'
                       '<tr><td>序号</td><td>状态</td><td>ticket_ID</td><td>ticket详情</td></tr>')
        if condition[0]:
            for perish in enumerate(condition[0]):
                filename.write('<tr><td>' + str(perish[0]) + '</td><td>' + str(perish[1]) + '</td><td>' + str(
                    perish[2]) + '</td><td>' + str(perish[3]) + '</td></tr>')
        filename.write('</table>')

        filename.write('<div style="margin-top: 10px">阻塞问题列表</div><table border="1" cellspacing="0" width="440">'
                       '<tr><td>序号</td><td>关系人</td><td>ticket_ID</td><td>ticket详情</td></tr>')
        if condition[1]:
            for stop in enumerate(condition[1]):
                filename.write('<tr><td>' + str(stop[0]) + '</td><td>' + str(stop[1]) + '</td><td>' + str(
                    stop[2]) + '</td><td>' + str(stop[3]) + '</td></tr>')
        filename.write('</table>')
        filename.write('<div style="margin-top: 30px">7.所有缺陷人员对应</div><div style="margin-top: 2px">'
                       '<img src="cid:image3" width="640px" height="400px"></div>' # 所有缺陷人员对应
                       '<div style="margin-top: 30px">8.已解决缺陷人员对应</div><div style="margin-top: 2px">'
                       '<img src="cid:image4" width="640px" height="400px"></div>' # 已解决缺陷人员对应
                       '<div style="margin-top: 30px">9.所有缺陷模块对应</div><div style="margin-top: 2px">'
                       '<img src="cid:image5" width="640px" height="400px"></div>') # 所有缺陷模块对应
        filename.write('</body></html>')

就是这么笨的函数,你可别笑··

最后就是调用主函数了

if __name__ == '__main__':
    redmine = Redmine(config_Redmine['host'], username=config_Redmine['username'], password=config_Redmine['pw'])
    project_name = redmine.project.get(config_Redmine['project_name'])
    issues = list(redmine.issue.filter(project_id=project_name.id, tracker_id=1, status_id='o', set_filter=1, fixed_version_id=config_Redmine['fixed_version_id']))
    time = datetime.now().timetuple()
    today = str(time.tm_year) + '-' + str(time.tm_mon) + '-' + str(time.tm_mday)
    today_date = datetime.strptime(today, '%Y-%m-%d')
    rm_path()  # 清理已生成的文件
    write_html()

 


接下来就是发送邮件的py脚本了

import smtplib
from email.mime.image import MIMEImage
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from datetime import datetime

time = datetime.now().timetuple()
today = str(time.tm_year) + '-' + str(time.tm_mon) + '-' + str(time.tm_mday)
msg_from = 'fengbingxue@asd.com'  # 发送人邮箱
passwd = 'xxxxx'  # 密码,自己填
msg_to = ['fengbingxue@asd.com','fengbingxue@asd.com']  # 收件人邮箱,多人用英文逗号隔开

 # 创建一个带附件的实例
message = MIMEMultipart()
message['From'] = Header('冯冰雪', 'utf-8')   # 发起人名字,不填展示为空
message['To'] = Header('项目组成员', 'utf-8')  # 接收人名字,不填展示为空
subject = '3D机房-测试日报'+ today  #邮件标题
message['Subject'] = Header(subject, 'utf-8')
file_html="daily_report.html"
with open('daily_report.html','r',encoding='utf-8') as f:
    content = f.read()
content = str(content)
mail_msg = content
message.attach(MIMEText(mail_msg, 'html', 'utf-8')) # 如果要发送html需要将plain改成html

fp1 = open('ticket_summary_chart.png', 'rb')
msgImage1 = MIMEImage(fp1.read())
fp1.close()
msgImage1.add_header('Content-ID', '<image1>')
message.attach(msgImage1)

fp2 = open('daily_summary_chart.png', 'rb')
msgImage2 = MIMEImage(fp2.read())
fp2.close()
msgImage2.add_header('Content-ID', '<image2>')
message.attach(msgImage2)

fp3 = open('ticet_people_chart.png', 'rb')
msgImage3 = MIMEImage(fp3.read())
fp3.close()
msgImage3.add_header('Content-ID', '<image3>')
message.attach(msgImage3)

fp4 = open('ticet_resolve_chart.png', 'rb')
msgImage4 = MIMEImage(fp4.read())
fp4.close()
msgImage4.add_header('Content-ID', '<image4>')
message.attach(msgImage4)

fp5 = open('ticet_module_chart.png', 'rb')
msgImage5 = MIMEImage(fp5.read())
fp5.close()
msgImage5.add_header('Content-ID', '<image5>')
message.attach(msgImage5)
try:
    smtpObj = smtplib.SMTP_SSL("smtp.exmail.qq.com", 465)  # qq邮箱的smtp地址及端口号
    smtpObj.login(msg_from, passwd)
    smtpObj.sendmail(msg_from, msg_to, message.as_string())
    print ("成功")
except smtplib.SMTPException:
    print("失败")

current.txt 就是每天大家更新变更信息的地方---也是唯一不自动的地方·

本次版本安装时间:  
2020-09-22
branch:  --git仓库的分支
report-1.2.0-t4
git_log:  
3fdb5ac395e723b2bc630dbae60229e3aab865ff
请按照格式[报表,冯冰雪,进度:30%;阻塞缺陷:3个;...]输入进展:  --不可移动
sql编辑器,冯冰雪,1.测试进度:98.8%;2.提交bug数:1个;3.提交的严重问题: 暂无4.阻塞进度的问题描述:暂无
工作表,冯冰雪,1.测试进度:90.1%;2.提交bug数:2个;3.提交的严重问题: 暂无4.阻塞进度的问题描述:暂无

module.txt  和 user.txt 没有改变,和上一版一样,可以参考上一版博文

https://blog.csdn.net/FBX_fbx_FBX/article/details/104051445  

 

这一套使用你还需要将自己的linux环境字体加一下宋体,不然自动生成的图片不能显示中文。(你可以尝试一下windows下可不可以,应该也是没问题的哦~)

sql也只有一个表了(附上结构):

/*
Navicat MySQL Data Transfer

Source Server         : 159
Source Server Version : 50728
Source Host           : 192.168.1.159:3306
Source Database       : report

Target Server Type    : MYSQL
Target Server Version : 50728
File Encoding         : 65001

Date: 2020-06-02 16:21:23
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for summer_data
-- ----------------------------
DROP TABLE IF EXISTS `summer_data`;
CREATE TABLE `summer_data` (
  `time_t` date NOT NULL,
  `resolved` int(255) DEFAULT NULL,
  `new` int(11) DEFAULT NULL,
  `unresolved` int(11) DEFAULT NULL,
  PRIMARY KEY (`time_t`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

如果你这一套都懒得集成,想直接用,那就下载我上传的资源吧,里面有中文字体及打包的python,解压之后可以直接用,

还有详细的操作说明。

https://download.csdn.net/download/FBX_fbx_FBX/12546154

操作说明其实我都写得很详细,不明白可以留言评论!好,就酱!

 类似资料: