python3实现自动生成redmine测试日报

我们每天的测试数据，需要及时反馈给领导，领导不喜欢看细节，就喜欢看结果，一目了然，快速又不费事。

领导最想看到就是  每个人的头上都有多少缺陷放着，大家上班的时候到底是不是在踏踏实实做事。报告真的很能说明事实。

测试组需要每天整理一份日报发送邮件给大家，从redmine（我司用的这款工具，相当好用啊，相比jira/禅道/bugzilla）上下载筛选过的缺陷列表，然后进行数据统计，重新处理模板报表···随着想要的信息越来越多，没半个小时这报告出不来，关键是有时候下班下得早，缺陷状态还不稳定（开发都还在处理），回家之后还要远程连接公司服务器。

反正就一个目的，为了这件事情能做得快点，大家早点下班，早点休息。

由于此版本是改版代码，存在一些多余的数据，后期修改

-----------

先来一些准备工作

其中有一些可能随时跟新的文本信息，单独放到txt，如下（注意文件名字要一致）：

current.txt （每日更新的数据）

（此行代码不读）请在下面输入今日的git号:
9543a8c16e133f7a323d124e024622a59f938727
（此行代码不读）请按照格式[报表,谭秋,进度：30%;阻塞缺陷：3个;...]输入进展:
会员管理,李四,1.测试进度:48.6%;2.提交bug数:5个;3.提交的严重问题:无;4.阻塞进度的问题描述:无
信息管理,张三,1.测试进度:48.6%;2.提交bug数:5个;3.提交的严重问题:无;4.阻塞进度的问题描述:无
仓库管理,王五,1.测试进度:48.6%;2.提交bug数:5个;3.提交的严重问题:无;4.阻塞进度的问题描述:无

module.txt（项目所有的模块，会以此做统计，此处只举例三个）

会员管理
注册管理
系统管理

user.txt  (redmine 是姓与名 之间存在空格的存储，需要注意书写)

张 三
李 四
王 五
冯 宝宝

数据库结构：

你不想自己建的话，可以用这个sql：

/*
Navicat MySQL Data Transfer

Source Server         : 1
Source Server Version : 50728
Source Host           : 192.168.1.1:3306
Source Database       : report

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

Date: 2019-12-25 14:12:43
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for all_data
-- ----------------------------
DROP TABLE IF EXISTS `all_data`;
CREATE TABLE `all_data` (
  `time_t` date NOT NULL,
  `new` int(255) unsigned zerofill DEFAULT NULL,
  `ongoing` int(255) unsigned zerofill DEFAULT NULL,
  `feedback` int(255) unsigned zerofill DEFAULT NULL,
  `delay` int(255) unsigned zerofill DEFAULT NULL,
  `reopen` int(255) unsigned zerofill DEFAULT NULL,
  `block` int(255) unsigned zerofill DEFAULT NULL,
  `refused` int(255) unsigned zerofill DEFAULT NULL,
  `untreated` int(255) unsigned zerofill DEFAULT NULL,
  `resolved` int(255) unsigned zerofill DEFAULT NULL,
  PRIMARY KEY (`time_t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for daily_data
-- ----------------------------
DROP TABLE IF EXISTS `daily_data`;
CREATE TABLE `daily_data` (
  `time_t` date NOT NULL,
  `new` int(255) unsigned zerofill DEFAULT NULL,
  `ongoing` int(255) unsigned zerofill DEFAULT NULL,
  `feedback` int(255) unsigned zerofill DEFAULT NULL,
  `delay` int(255) unsigned zerofill DEFAULT NULL,
  `reopen` int(255) unsigned zerofill DEFAULT NULL,
  `block` int(255) unsigned zerofill DEFAULT NULL,
  `resolved` int(255) unsigned zerofill DEFAULT NULL,
  PRIMARY KEY (`time_t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

-- ----------------------------
-- Table structure for git_msg
-- ----------------------------
DROP TABLE IF EXISTS `git_msg`;
CREATE TABLE `git_msg` (
  `time_t` date NOT NULL,
  `git_num` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`time_t`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

准备好之后

附代码吧，你可以根据你的情况进行改动（由于此为改版，存在多余的操作，还望大家不要指责，后面会进行完善的）

# -*- coding:UTF-8 -*-
import xlwt
from datetime import datetime
from redminelib import Redmine
import pymysql

file_user = 'user.txt'  # 参与人员名单
file_module = 'module.txt'  # 功能模块列表
file_curren = 'curren.txt'  # 每日进度和git数据
file_report = 'dailyreport.xls'   # 报表生成位置
time_list = ['2019-11-25', '2020-04-10']  # 设置数据获取时间段
#sql配置数据
conn_host='192.168.1.1'
conn_port=3306
conn_user='root'
conn_passwd='123456'
conn_db='report'


# 缺陷对应人员的数量及缺陷模块数量
def current_day():
    resolve_user_list=[]
    module_list = []
    all_user_list =[]
    # user
    with open(file_user, '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,n]) #已解决的人员分布
            all_user_list.append([user,j])
        # module
        with open(file_module, '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,n])
    return resolve_user_list,module_list,all_user_list
    # current_day[0] 已解决人员对应
    # current_day[1] 模块对应
    # current_day[2] 人员对应


def SandP_mysql():                    #2 3 6 7 点
    x = 0
    summer = [['时间','已解决','今日新建','待解决问题']]
    summer_daily = []
    summer_all = []
    march = [['模块名称', '测试人员', '测试内容及进度']]
    git_row = [['序号', '安装日期', 'branch： commit ID']]
    daily_row = [['时间','新建','进行中','已解决','反馈','阻塞','推迟','Reopen']]
    #daily_row.append([str(time_all), new_all, ongoing_all, resolved_all, feedback_all, block_all,delay_all, reopen_all])
    all_row = [['时间', '新建', '进行中', '反馈', '推迟', '重新打开', '阻塞', '已拒绝', '不处理', '已解决', '未修复']]
    i = 0
    with open(file_curren, 'r', encoding='UTF-8') as file_user_read:
        user_lines = file_user_read.readlines()
        for line in user_lines:
            i += 1
            if i == 2:
                daily_git_log = line
            if int(i) > 3:
                line = line.split(',')
                march.append(line)
    # new 新增 ongoing 进行中 feedback 反馈 delay 推迟 reopen 重新打开 block 阻塞 closed 已关闭 refused 已拒绝 untreated 不处理 resolved 已解决 unrepaired 已验证
    new, ongoing, resolved, feedback, delay, reopen, block = 0, 0, 0, 0, 0, 0, 0
    new_all, ongoing_all, feedback_all, delay_all, reopen_all, block_all, refused_all, untreated_all, resolved_all = 0, 0, 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 == "已拒绝": refused_all += 1
        if issue.status.name == "推迟": delay_all += 1
        if issue.status.name == "重新打开": reopen_all += 1
        if issue.status.name == "不处理": untreated_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)
        solving = new_all+ongoing_all+ feedback_all+reopen_all+block_all
        if create_time == today:
            if issue.status.name == "新建": new += 1
            if issue.status.name == "进行中": ongoing += 1
            if issue.status.name == "已解决": resolved += 1
            if issue.status.name == "反馈": feedback += 1
            if issue.status.name == "推迟": delay += 1
            if issue.status.name == "重新打开": reopen += 1
            if issue.status.name == "阻塞": block += 1
    db = pymysql.connect(host=conn_host, port=conn_port, user=conn_user, passwd=conn_passwd, db=conn_db)
    cursor = db.cursor()
    sql_git_log = "replace into git_msg(time_t,git_num) VALUES ('%s','%s')" % (today,daily_git_log)
    sql_daily = "replace into daily_data(time_t,new,ongoing,resolved,feedback,delay,reopen,block) VALUES ('%s',%s,%s,%s,%s,%s,%s,%s)"%(today, new, ongoing, resolved, feedback, delay, reopen, block)
    sql_all = "replace into all_data(time_t,new,ongoing,feedback,delay,reopen,block,refused,untreated,resolved) VALUES ('%s',%s,%s,%s,%s,%s,%s,%s,%s,%s)"%(today, new_all, ongoing_all, feedback_all, delay_all, reopen_all, block_all, refused_all,untreated_all, resolved_all)
    try:
        cursor.execute(sql_git_log)
        db.commit()
        cursor.execute(sql_daily)
        db.commit()
        cursor.execute(sql_all)
        db.commit()
        print("ok")
    except:
        # 如果发生错误则回滚
        db.rollback()
        print("no")
    try:
        sql_daily = "SELECT * FROM daily_data WHERE time_t BETWEEN '%s' and '%s'" % (time_list[0], time_list[-1])
        sql_git = "SELECT * FROM git_msg WHERE time_t='%s'" % (today)
        sql_all = "SELECT * FROM all_data WHERE time_t BETWEEN '%s' and '%s'" % (time_list[0], time_list[-1])
        cursor.execute(sql_daily)  # 执行SQL语句
        results_daily = cursor.fetchall()  # 获取所有记录列表
        cursor.execute(sql_git)  # 执行SQL语句
        results_git = cursor.fetchall()  # 获取所有记录列表
        cursor.execute(sql_all)  # 执行SQL语句
        results_all = cursor.fetchall()  # 获取所有记录列表
        for row in results_daily:
            time_daily = row[0]
            new = row[1]
            summer_daily.append([str(time_daily), new])
        for row_all in results_all:
            time_all = row_all[0]
            new_all = row_all[1]
            ongoing_all = row_all[2]
            feedback_all = row_all[3]
            delay_all = row_all[4]
            reopen_all = row_all[5]
            block_all = row_all[6]
            refused_all = row_all[7]
            untreated_all = row_all[8]
            resolved_all = row_all[9]
            totle_unresolved = new_all+ongoing_all+feedback_all+delay_all+reopen_all+block_all
            all_row.append([str(time_all), new_all, ongoing_all, feedback_all, delay_all, reopen_all, block_all, refused_all,untreated_all,resolved_all,totle_unresolved])
            summer_all.append([resolved_all, totle_unresolved])
        daily_row.append([str(time_all), new_all, ongoing_all, resolved_all, feedback_all, block_all, delay_all, reopen_all])
        for i in range(0,len(summer_all)):
            summer_daily_vulue = list(zip(summer_daily[i],summer_all[i]))
            summer_li = []
            summer_li.append(summer_daily_vulue[0][0])
            summer_li.append(summer_daily_vulue[0][1])
            summer_li.append(summer_daily_vulue[1][0])
            summer_li.append(summer_daily_vulue[1][1])
            summer.append(summer_li)
        for git_line in results_git:
            x += 1
            time_daily = git_line[0]
            git_log = git_line[1]
            git_row.append([x,str(time_daily),git_log])
    except:
        print("Error: unable to fetch data")
    db.close()
    return git_row,march,summer,daily_row
    #  SandP_mysql [0]  git_num
    #  SandP_mysql [1]  测试进展
    #  SandP_mysql [2]  ticket汇总图
    #  SandP_mysql [3]  每日ticket状态分布


def analysis():
    # 严重 grave 致命 deadly 紧急 urgent 已解决 resolved  超时 timeout 阻塞 stop
    analysis_list = []
    become_list = [['序号', '关系人','ticket_ID', 'ticket详情']]  #退化缺陷
    new_num,become = 0,0
    grave_list  = [['序号', '状态','ticket_ID', 'ticket详情']]  # 重大缺陷列表
    stop_list = [['序号', '关系人','ticket_ID', 'ticket详情']]  #阻塞缺陷列表
    grave_num, deadly_num, timeout_num,stop_num,resolved_all = 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.append(grave_list)  #重大缺陷列表 [0]
    analysis_list.append(stop_list)  #阻塞缺陷列表 [1]
    analysis_list.append(grave_num)  #重大缺陷个数 [2]
    analysis_list.append(deadly_num)  #重大/致命缺陷个数 [3]
    analysis_list.append(resolved_all) #已解决数 [4]
    analysis_list.append(timeout_num)  # 超过一周未更新数 [5]
    analysis_list.append(new_num)     #今日新增数 [6]
    analysis_list.append(become)     # 退化数 [7]
    analysis_list.append(become_list)  # 退化列表 [8]
    return analysis_list


def set_style(name, height,bold=False):
    style = xlwt.XFStyle()   # 初始化样式
    font = xlwt.Font()       # 为样式创建字体
    font.name = name
    font.bold = bold  #粗体
    font.color_index = 4
    font.height = height
    style.font = font
    return style


def set_boder_style(name, height,bold=False):
    style = xlwt.XFStyle()   # 初始化样式
    font = xlwt.Font()       # 为样式创建字体
    font.name = name
    font.bold = bold
    font.color_index = 4
    font.height = height
    style.font = font
    borders = xlwt.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    style.borders = borders
    return style


def sheet_write(row,sheet,n,style):
    for i in range(len(row)):
        for j in range(len(row[i])):
            sheet.write(n + i + 1, j, row[i][j], style)

def write_csv():
    workbook = xlwt.Workbook(encoding='utf-8')
    data_sheet = workbook.add_sheet(today)
    boder_style = set_boder_style('Times New Roman', 220)
    unboder_style = set_style('Times New Roman', 220)
    data_analy=analysis()
    data_sql=SandP_mysql()
    data_BJS=current_day()
    row1 = [u'1.测试计划']
    row1_value = [['时间安排', '20190826-20191104'],['测试计划','http://xxx.xxx.87.87:19503/issues/62635'], ['人员安排', '张三、李四、王五']]
    row2 = [u'2.测试版本']
    row2_value = data_sql[0]  # git
    row3 = [u'3.测试进展']
    row3_value = data_sql[1]
    row4 = ['4.'+today + u'今日测试情况']
    row4_value1 = [['新增缺陷个数', data_analy[6]]] # , ['严重问题',data_analy[2]]]
    row5 = [u'ticket总量情况']
    row5_value1 = data_sql[2]    # 折线图 三个
    row5_value2 = data_sql[3]    #柱状图 竖着
    row5_value3 = [['超过一周未更新ticket数',data_analy[5],'退化的ticket数',data_analy[7],'致命/重大ticket数',data_analy[3],'已解决缺陷总数',data_analy[4],'今日新建ticket数',data_analy[6]]]
    row6 = [u'缺陷概况']
    row6_value1_title = [u'退化问题列表']
    row6_value1_value = data_analy[8]
    row6_value2_title = [u'严重问题列表']
    row6_value2_value = data_analy[0]
    row6_value3_title = [u'阻塞问题列表']
    row6_value3_value = data_analy[1]
    row7 = [u'所有缺陷人员对应']
    row7_value = data_BJS[2]
    row8 = [u'已解决缺陷分布']
    row8_value = data_BJS[0]
    row9 = [u'所有缺陷模块分布']
    row9_value = data_BJS[1]
    n = 0
    #---1----
    data_sheet.write(0, 0, row1, unboder_style)
    n += 1
    sheet_write(row1_value,data_sheet,n,boder_style)
    n += len(row1_value) + 1
    # ----2---
    data_sheet.write(n + 1, 0, row2, unboder_style)
    n += 1
    sheet_write(row2_value, data_sheet, n, boder_style)
    n += len(row2_value) + 1
    # ----3--
    data_sheet.write(n + 1, 0, row3, unboder_style)
    n += 1
    sheet_write(row3_value,data_sheet,n,boder_style)
    n += len(row3_value) + 1
    #---4-
    data_sheet.write(n + 1, 0, row4, unboder_style)
    n += 1
    sheet_write(row4_value1,data_sheet,n,boder_style)
    n += len(row4_value1) + 1
    # sheet_write(row4_value2, data_sheet, n, boder_style)
    # n += len(row4_value2) + 1
    # ----5---
    data_sheet.write(n + 1, 0, row5, unboder_style)
    n += 1
    sheet_write(row5_value1,data_sheet,n,boder_style)
    n += len(row5_value1) + 1
    sheet_write(row5_value2, data_sheet, n, boder_style)
    n += len(row5_value2) + 1
    sheet_write(row5_value3, data_sheet, n, boder_style)
    n += len(row5_value3) + 1
    # ----6--
    data_sheet.write(n + 1, 0, row6, unboder_style)
    n += 1
    data_sheet.write(n + 1, 0, row6_value1_title, unboder_style)
    n += 1
    sheet_write(row6_value1_value,data_sheet,n,boder_style)
    n += len(row6_value1_value) + 1
    data_sheet.write(n + 1, 0, row6_value2_title, unboder_style)
    n += 1
    sheet_write(row6_value2_value,data_sheet,n,boder_style)
    n += len(row6_value2_value) + 1
    data_sheet.write(n + 1, 0, row6_value3_title, unboder_style)
    n += 1
    sheet_write(row6_value3_value,data_sheet,n,boder_style)
    n += len(row6_value3_value) + 1
    # ---7----
    data_sheet.write(n + 1, 0, row7, unboder_style)
    n += 1
    sheet_write(row7_value,data_sheet,n,boder_style)
    n += len(row7_value) + 1
    #---8---
    data_sheet.write(n + 1, 0, row8, unboder_style)
    n += 1
    sheet_write(row8_value,data_sheet,n,boder_style)
    n += len(row8_value) + 1
    #------9----------
    data_sheet.write(n + 1, 0, row9, unboder_style)
    n += 1
    sheet_write(row9_value,data_sheet,n,boder_style)
    n += len(row9_value) + 1
    workbook.save(file_report)

if __name__ == '__main__':
    redmine = Redmine('http://xxx.xxx.87.87:8080', username='username', password='password')
    issues = list(redmine.issue.filter(project_id=308, tracker_id=1, status_id='o', set_filter=1,fixed_version_id=986))
    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')
    write_csv()

放在同一个目录下，运行这个python文件就可以出报告了

出来的结果是这样的，这些数据都是为了画趋势图

润色之后，就是这样的哟

---

从趋势图，可以结合自己项目的实际情况，看出缺陷的收敛情况，以及变化状态，阶段性的改变等等

以后 会考虑更多的功能进去，比如自动构图和发送报告等等，现在先发一个基础版。

 

对此有什么疑问或者意见，可留言~