一、创建数据库

1、创建测试人员统计表

统计测试人员每天每个项目的BUG数量、验证数量、被拒绝数量、重复提交数量、各严重等级缺陷数量、到期未验证数量、加权数量

CREATE TABLE `daily_test_bug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL COMMENT '测试账号',
  `test_name` varchar(20) DEFAULT NULL,
  `project_name` varchar(100) DEFAULT NULL COMMENT '项目名称',
  `Iterative_version` varchar(50) DEFAULT NULL COMMENT '迭代版本',
  `find_amount` int(11) DEFAULT NULL COMMENT 'BUG数量',
  `verrify_amount` int(11) DEFAULT NULL COMMENT '验证数量',
  `refuse_amount` int(11) DEFAULT NULL COMMENT '被拒绝数量',
  `repeat_amount` int(11) DEFAULT NULL COMMENT '重复提交数量',
  `fatal_amount` int(11) DEFAULT NULL COMMENT '致命级别BUG',
  `severe_amount` int(11) DEFAULT NULL COMMENT '严重级BUG',
  `average_amount` int(11) DEFAULT NULL COMMENT '一般级别BUG',
  `minor_amount` int(11) DEFAULT NULL COMMENT '轻微级别BUG',
  `due_verrify_amount` int(11) DEFAULT NULL COMMENT '到期未验证数量',
  `weighted_quantity` double(10,2) DEFAULT NULL COMMENT 'fatal_amount*5+severe_amount*3+average_amount*1+minor_amount*0.5',
  `date` date DEFAULT NULL COMMENT '统计日期',
  `createtime` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7009 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

2、创建开发人员统计表

统计开发人员每天每个项目的BUG数量、修复数量、打回数量、重新引入数量、各严重等级缺陷数量、到期未修复数量、加权数量

CREATE TABLE `daily_assign_bug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL COMMENT '开发账号',
  `assign_name` varchar(20) DEFAULT NULL COMMENT '开发者姓名',
  `project_name` varchar(100) DEFAULT NULL COMMENT '项目名称',
  `Iterative_version` varchar(50) DEFAULT NULL COMMENT '迭代版本',
  `introduce_amount` int(11) DEFAULT NULL COMMENT 'BUG数量',
  `repair_amount` int(11) DEFAULT NULL COMMENT '修复数量',
  `return_amount` int(11) DEFAULT NULL COMMENT '打回数量',
  `re_introduce_amount` int(11) DEFAULT NULL COMMENT '重新引入BUG',
  `fatal_amount` int(11) DEFAULT NULL COMMENT '致命级别BUG',
  `severe_amount` int(11) DEFAULT NULL COMMENT '严重级BUG',
  `average_amount` int(11) DEFAULT NULL COMMENT '一般级别BUG',
  `minor_amount` int(11) DEFAULT NULL COMMENT '轻微级别BUG',
  `due_amount` int(11) DEFAULT NULL COMMENT '到期未修复BUG',
  `weighted_quantity` double(10,2) DEFAULT NULL COMMENT 'fatal_amount*5+severe_amount*3+average_amount*1+minor_amount*0.5',
  `date` date DEFAULT NULL COMMENT '统计日期',
  `createtime` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=35832 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

 

二、组装入库SQL

# 测试人员入库SQL

def carry_insert_test(project_name, user_name, *amounts):
    """执行写入测试BUG日报表"""
    test_insertsql = """
    INSERT INTO `daily_test_bug` 
    (`id`, `user_name`, `test_name`, `project_name`, `Iterative_version`, `find_amount`,`verrify_amount`,
     `refuse_amount`, `repeat_amount`, `fatal_amount`,`severe_amount`, `average_amount`, `minor_amount`,
     `due_verrify_amount`, `weighted_quantity`, `date`, `createtime`) 
     VALUES 
     (id, "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}","{}","{}","{}","{}", {} ,"{}", "{}");
    """.format(user_name, user_name, project_name, '',
               amounts[0], amounts[1], amounts[2], amounts[3],
               amounts[4], amounts[5], amounts[6], amounts[7], amounts[8],
               amounts[5]*5 + amounts[6]*3 + amounts[7]*1 + amounts[8]*0.5,
               yesterday, datetime.now())
    MysqlUntil().mysql_insert(test_insertsql)

#  开发人员入库SQL

def carry_insert_assignee(project_name, user_name, *amounts):
    """执行写入开发BUG日报表"""
    assignee_insertsql = """
    INSERT INTO `daily_assign_bug`
    (`id`, `user_name`,`assign_name`, `project_name`, `Iterative_version`,`introduce_amount`, `repair_amount`,
     `return_amount`, `re_introduce_amount`, `fatal_amount`, `severe_amount`, `average_amount`, `minor_amount`,
     `due_amount`, `weighted_quantity`, `date`, `createtime`) 
     VALUES 
     (id, "{}", "{}", "{}", "{}", "{}", "{}", "{}", "{}","{}","{}","{}","{}","{}",{},"{}", "{}");
    """.format(user_name, user_name, project_name, '',
               amounts[0], amounts[1], amounts[2], amounts[3],
               amounts[4], amounts[5], amounts[6], amounts[7], amounts[8],
               amounts[5]*5 + amounts[6]*3 + amounts[7]*1 + amounts[8]*0.5,
               yesterday, datetime.now())
    MysqlUntil().mysql_insert(assignee_insertsql)

 

三、定义执行JQL类，返回统计数量

req_jira统计符合查询条件的缺陷数量

get_reopen_count函数和get_re_open_count函数分别为获取缺陷打回次数和重新引入次数（其实是一样的，但是 issue.fields.customfield_10404不知道怎么通过传参将customfield_10404，customfield_10403替换掉，欢迎大佬指教）

class Ana_jira:
    """
    执行JQL，返回统计数量
    """
    def __init__(self, test_jira, test_jql):
        self.test_jira = test_jira
        self.test_jql = test_jql
        # self.product_name=product_name

    def req_jira(self):
        issues = self.test_jira.search_issues(self.test_jql, maxResults=-1, fields='')
        len_all = len(issues)
        return len_all
    
    def get_reopen_count(self):
        # 获取BUG打回次数
        issues = self.test_jira.search_issues(self.test_jql, maxResults=-1, fields='')
        reopen_counts = 0
        for issue in issues:
            reopen_count = issue.fields.customfield_10403
            if reopen_count != None:
                reopen_counts += reopen_count
            else:
                reopen_counts += 0
        return reopen_counts
    
    def get_re_open_count(self):
        # 获取BUG重新引入次数
        issues = self.test_jira.search_issues(self.test_jql, maxResults=-1, fields='')
        re_open_counts = 0
        for issue in issues:
            re_open_count = issue.fields.customfield_10404
            if re_open_count != None:
                re_open_counts += re_open_count
            else:
                re_open_counts += 0
        return re_open_counts

def do_jira_jql(jqls):
    find_amounts = []
    for jql in jqls:
        find_amount = Ana_jira(test_jira, jql).req_jira()
        find_amounts.append(find_amount)
    return find_amounts

 

四、组装查询JQL

有一项查询逾期未验证缺陷的统计，参考 基于jira的缺陷自动化报表分析 获取N天前的日期（前天和今天上一篇已获取）

due_verrify_day = NowDatetime().get_date(4)  # 延期验证：审查三天后未验证

获取JIRA测试人员BUG数据

def test_jql(testor, project_name):
    """测试每日BUG统计"""
    jqls = []
    # 发现BUG
    jql_find = 'project="{}" AND issuetype = 故障 AND creator in ('.format(project_name) + '{}'.format(testor) + \
               ') AND created >= {} AND created < {}'.format(yesterday, today)
    # 验证BUG
    jql_verrify = 'project = {} AND issuetype = 故障 AND status in (Reopened, Closed) AND 验证日期 = {} AND creator in ('.format(
        project_name, yesterday) + '{})'.format(testor)
    # 被拒绝BUG
    jql_refuse = 'project = {} AND issuetype = 故障 AND status in (refused, 已拒绝)  AND 拒绝日期 = {} AND creator in ('.format(
        project_name, yesterday) + '{})'.format(testor)
    # 重复提交BUG
    jql_repeat = 'project = {} AND issuetype = 故障 AND status in (重复提交, repeated) AND 重复日期 = {} AND creator in ('.format(
        project_name, yesterday) + '{})'.format(testor)
    # 致命级级BUG
    fatal_jql = 'project="{}" AND issuetype = 故障 AND creator in ('.format(project_name) + '{}'.format(
        testor) + ') AND 严重性 = 致命 AND created >= {} AND created <= {}'.format(yesterday, today)
    # 严重级BUG
    serious_jql = 'project="{}" AND issuetype = 故障 AND creator in ('.format(project_name) + '{}'.format(
        testor) + ') AND 严重性 = 严重 AND created >= {} AND created <= {}'.format(yesterday, today)
    # 一般级BUG
    average_jql = 'project="{}" AND issuetype = 故障 AND creator in ('.format(project_name) + '{}'.format(
        testor) + ') AND 严重性 = 一般 AND created >= {} AND created <= {}'.format(yesterday, today)
    # 轻微级BUG
    minor_jql = 'project="{}" AND issuetype = 故障 AND creator in ('.format(project_name) + '{}'.format(
        testor) + ') AND 严重性 = 优化 AND created >= {} AND created <= {}'.format(yesterday, today)
    # 逾期未验证BUG
    due_verrify_jql = 'project="{}" AND issuetype = 故障 AND creator in ('.format(project_name) + '{}'.format(
        testor) + ') AND status in ("待验证") AND 审查日期 = {}'.format(due_verrify_day)
    jqls.append(jql_find)
    jqls.append(jql_verrify)
    jqls.append(jql_refuse)
    jqls.append(jql_repeat)
    jqls.append(fatal_jql)
    jqls.append(serious_jql)
    jqls.append(average_jql)
    jqls.append(minor_jql)
    jqls.append(due_verrify_jql)
    return jqls

获取JIRA开发人员BUG数据

def develop_amounts(user, project_name):
    """开发每日BUG"""
    amounts = []
    # 引入BUG
    jql_introduce = 'project="{}" AND issuetype = 故障 AND assignee in ('.format(project_name) + '{}'.format(
        user) + ') AND created >= {} AND created < {}'.format(yesterday, today)
    amount_introduce = Ana_jira(test_jira, jql_introduce).req_jira()
    amounts.append(amount_introduce)
    # 修复BUG
    jql_repair = 'project = {} AND issuetype = 故障 AND 修复日期 = {} AND assignee in ('.format(
        project_name, yesterday) + '{})'.format(user)
    amount_repair = Ana_jira(test_jira, jql_repair).req_jira()
    amounts.append(amount_repair)
    # 被打回BUG
    reopen_jql = 'project="{}" AND issuetype = 故障 AND assignee in ('.format(project_name) + '{}'.format(
        user) + ') AND 打回日期 = {}'.format(yesterday)
    amount_reopen = Ana_jira(test_jira, reopen_jql).get_reopen_count()
    amounts.append(amount_reopen)
    # 重新引入BUG
    re_open_jql = 'project="{}" AND issuetype = 故障 AND assignee in ('.format(project_name) + '{}'.format(
        user) + ') AND 重新引入日期 = {}'.format(yesterday)
    amount_re_open = Ana_jira(test_jira, re_open_jql).get_re_open_count()
    amounts.append(amount_re_open)
    # 致命级BUG
    fatal_jql = 'project="{}" AND issuetype = 故障 AND assignee in ('.format(project_name) + '{}'.format(
        user) + ') AND 严重性 = 致命 AND created >= {} AND created <= {}'.format(yesterday, today)
    amount_fatal = Ana_jira(test_jira, fatal_jql).req_jira()
    amounts.append(amount_fatal)
    # 严重级BUG
    serious_jql = 'project="{}" AND issuetype = 故障 AND assignee in ('.format(project_name) + '{}'.format(
        user) + ') AND 严重性 = 严重 AND created >= {} AND created <= {}'.format(yesterday, today)
    amount_serious = Ana_jira(test_jira, serious_jql).req_jira()
    amounts.append(amount_serious)
    # 一般级BUG
    average_jql = 'project="{}" AND issuetype = 故障 AND assignee in ('.format(project_name) + '{}'.format(
        user) + ') AND 严重性 = 一般 AND created >= {} AND created <= {}'.format(yesterday, today)
    amount_average = Ana_jira(test_jira, average_jql).req_jira()
    amounts.append(amount_average)
    # 轻微级BUG
    minor_jql = 'project="{}" AND issuetype = 故障 AND assignee in ('.format(project_name) + '{}'.format(
        user) + ') AND 严重性 = 优化 AND created >= {} AND created <= {}'.format(yesterday, today)
    amount_minor = Ana_jira(test_jira, minor_jql).req_jira()
    amounts.append(amount_minor)
    # 逾期未修复BUG
    due_jql = 'project="{}" AND issuetype = 故障 AND assignee in ('.format(project_name) + '{}'.format(
        user) + ') AND status in (Open, "In Progress", Reopened) AND due = {}'.format(yesterday)
    amount_due = Ana_jira(test_jira, due_jql).req_jira()
    amounts.append(amount_due)
    return amounts

 

五、按人员统计缺陷情况

1、获取缺陷人员

缺陷相关人员都可以从基于jira的缺陷自动化报表分析 （三）中保存的缺陷信息中获取

def get_project_user(*args):
   sql = """
   SELECT {creator} FROM `jira_issues` WHERE project='{project_name}' GROUP BY {creator};
   """.format(creator=args[1], project_name=args[0])
   project_user = MysqlUntil().mysql_select(sql)
   return project_user

 分别获取与项目有关的测试人员和开发人员

testor = get_project_user(project_name, 'creator')
assignee = get_project_user(project_name, 'assignee')

2、按照缺陷人员统计缺陷

def ana_jira_issues(project_name, users):
    if len(users)>0:
        for tester in users:
            for user_type, user_name in tester.items():
                if user_type == 'creator':
                    # find_amounts, verrify_amounts, refuse_amounts, repeat_amounts = insertassignee(project_name, test_jira, testers, yesterday, today) # 测试
                    jqls = test_jql(user_name, project_name) #, yesterday, today, due_verrify_day
                    t_amounts = do_jira_jql(jqls)
                    carry_insert_test(
                        project_name, user_name, user_name, t_amounts[0], t_amounts[1], t_amounts[2],t_amounts[3],
                        t_amounts[4], t_amounts[5], t_amounts[6], t_amounts[7], t_amounts[8]
                    )
                elif user_type == 'assignee':
                    de_amounts = develop_amounts(user_name, project_name)  # yesterday, today,
                    carry_insert_assignee(
                        project_name, user_name, user_name, de_amounts[0], de_amounts[1], de_amounts[2], de_amounts[3],
                        de_amounts[4], de_amounts[5], de_amounts[6], de_amounts[7], de_amounts[8]
                    )
                else:
                    print('未找到用户类型')
    else:
        print('项目{}无用户'.format(project_name))

六、执行统计

ana_jira_issues(project_name, testor)
ana_jira_issues(project_name, assignee)

测试人员统计结果

开发人员统计结果