虽然改用 java 做测试开发有段时间了，但之前在使用 python 过程中，还是写了不少小工具；
在此跟大家分享下 基于禅道统计 BUG 趋势图的 SQA 小工具 (python 版)

-----背景-----

当时技术总监要求我这边按周定期输出 每条产品的 BUG 情况，以此来反馈 开发解决问题、测试跟进问题的情况；当时公司内部有 5 大产品线，每条产品线至少 3 个以上产品在开发；如果按照手工方案一个一个来进行数据统计，那简直就是傻子行为了。以此我这边开始着手准备编写一个小工具，最终达到目的：自动定期发送统计报告，报告维度（数据 + 图表展示）

-----技术选型-----
python + excel + jenkins

-------达到目标--------------
1.导出当前一星期的 BUG 统计数据
2.导出指定时间 -- 当前时间的 BUG 统计数据
3.统计维度（新增、已解决、已关闭、未解决 (累计)、延期解决 (累计)）
4.领导可通过报表直观的看到产品线情况

说明下各大概念指标，与常规的统计指标有一定区别：
按周统计:
1、新增，新增日期为本周内的（包括本周内被解决或关闭的 BUG）
2、已解决，解决日期为本周内的。被开发设定为已解决的。其中可能有部分是上周遗留下来的，体现了开发在本周的变化情况（包括设计如此、重复 BUG、外部原因、无法重现、不予解决、转为需求），不包含延期处理
3、已关闭，关闭日期为本周内的。是测试验证过，确实已经解决的，包括其中有的是上周遗留下来的
4、未解决，当前显示 BUG 状态为未解决的。包含当前还没被解决的、之前遗留的未解决、以及 reopen 的 BUG（累计数据）
5、延期解决，当前显示 BUG 状态为 延期处理的。BUG 状态中新增一个延期解决 (累计数据)
最终实现效果:



核心代码:

'''
    创建图表图形方法--按周
    '''
    def chart_series_week(self,sheet_name,type,row_len,col_len):
        chart = self.workbook.add_chart({'type': '%s'%(type)})
        if type == "pie":
            for j in range(2,col_len-2):
                chart.add_series({
                    'name':       ['%s'%(sheet_name), 0, j],
                    'categories': ['%s'%(sheet_name), 1, 0, row_len, 0],
                    'values':     ['%s'%(sheet_name), 1, j, row_len,j],
                    'data_labels': {'percentage': 1},   #百分比显示数值
                })
        else:
            for j in range(2,col_len-2):
                chart.add_series({
                    'name':       ['%s'%(sheet_name), 0, j],
                    'categories': ['%s'%(sheet_name), 1, 0, row_len, 0],
                    'values':     ['%s'%(sheet_name), 1, j, row_len,j],#显示数据
                    'data_labels': {'value': 1},#显示数据表
                })
        #添加数据表
        chart.set_table()
        # 设置图表风格.
        chart.set_style(18)
        #设置图表大小
        chart.set_size({'width': 650, 'height': 450})
        return chart

 '''
    创建图表图形方法-按产品或项目
    '''
    def chart_series_all(self,sheet_name,type,row_len,col_len):
        chart = self.workbook.add_chart({'type': '%s'%(type)})
        if type == "pie":
            for j in range(col_len-4,col_len):
                chart.add_series({
                    'name':       ['%s'%(sheet_name), 0, j],
                    'categories': ['%s'%(sheet_name), 1, 0, row_len, 0],
                    'values':     ['%s'%(sheet_name), 1, j, row_len,j],
                    'data_labels': {'percentage': 1},   #百分比显示数值
                })
        else:
            for j in range(col_len-4,col_len):
                chart.add_series({
                    'name':       ['%s'%(sheet_name), 0, j],
                    'categories': ['%s'%(sheet_name), 1, 0, row_len, 0],
                    'values':     ['%s'%(sheet_name), 1, j, row_len,j],#显示数据
                    'data_labels': {'value': 1},#显示数据表
                })
        #添加数据表
        chart.set_table()
        # 设置图表风格.
        chart.set_style(18)
        #设置图表大小
        chart.set_size({'width': 650, 'height': 450})
        return chart

'''
    柱形图
    哼哈BUG按周统计图
    @sheet_name: Sheet页名称
    @sql_date: 2016-01-04 00:00:00格式
    例:今天为2016-01-04 00:00:00，输入这个时间后，会自动查询2015-12-28 00:00:00---2016-01-03 23:59:59时间段内BUG
    '''
    def CountBUGAsWeeklyForHuaLa(self,sheet_name,sql_date):
        #计算开始时间和结束时间
        dateResult = op_date.week_get(sql_date)
        start_date = dateResult[0][0]
        end_date = dateResult[1][0]
        workbook = self.workbook
        worksheet = self.workbook.add_worksheet(name=sheet_name)
        bold = workbook.add_format({'bold': 1})
        # 定义数据表头列表

        # title = [u'按周统计图', u'统计日期',u'新增', u'已解决',u'已关闭',u'未解决(累计)',u'延期解决(累计)',u'已关闭(累计)',u'总BUG数']
        # buname = [u"哼哈微信端",u"哼哈商户端(android)",u"哼哈商户端(iOS)",u"哼哈后台",u"哼哈生活(产品)"]
        title = cn.bugStatusList
        buname = cn.huala_week
        #获取row长度
        row_len = len(buname)
        #获取col长度
        col_len = len(title)
        #定义数据列表
        #花啦微信端统计所有BUG

        data = []
        #添加哼哈微信用户端BUG数据
        result1 = cn.BugCountByProject(cn.hl_pjct[0],sql_date)
        data.append(result1)
        #添加哼哈商户端(android)BUG数据
        result2 = cn.BugCountByProject(cn.hl_pjct[1],sql_date)
        data.append(result2)
        #添加哼哈商户端(iOS)BUG数据
        result3 = cn.BugCountByProject(cn.hl_pjct[2],sql_date)
        data.append(result3)

        #添加哼哈运营后台BUG数据
        result4 = cn.BugCountByProject(cn.hl_pjct[3],sql_date)
        data.append(result4)

        #添加哼哈生活BUG数据
        result5 = cn.BugCountByProduct(cn.hl_pdct[0],sql_date)
        data.append(result5)

        format_title=workbook.add_format()    #定义format_title格式对象
        format_title.set_border(1)   #定义format_title对象单元格边框加粗(1像素)的格式
        format_title.set_bg_color('#cccccc')   #定义format_title对象单元格背景颜色为
                                               #'#cccccc'的格式
        format_title.set_align('center')    #定义format_title对象单元格居中对齐的格式
        format_title.set_bold()    #定义format_title对象单元格内容加粗的格式


        worksheet.write_row('A1', title, format_title)

        worksheet.write_column('A2', buname,bold)
        for i in range(2,row_len+2):
            worksheet.write_row('B%d'%(i),data[i-2])

        #创建一个图表，类型是column(柱形图)
        chart = self.chart_series_week(sheet_name,"column",row_len,col_len)

        # Add a chart title and some axis labels.
        chart.set_title ({'name': u'按周统计BUG %s--%s'%(start_date,end_date)})
        chart.set_x_axis({'name': u'BUG状态'})
        chart.set_y_axis({'name': u'BUG数'})

        # Insert the chart into the worksheet (with an offset).
        worksheet.insert_chart('A9', chart, {'x_offset': 25, 'y_offset': 10})


        #创建一个图表，类型是column(柱形图)
        chart1 = self.chart_series_all(sheet_name,"column",row_len,col_len)

        # Add a chart title and some axis labels.
        chart1.set_title ({'name': u'按产品或项目统计总BUG %s'%(end_date)})
        chart1.set_x_axis({'name': u'BUG状态'})
        chart1.set_y_axis({'name': u'BUG数'})

        # Insert the chart into the worksheet (with an offset).
        worksheet.insert_chart('L9', chart1, {'x_offset': 25, 'y_offset': 10})

时间处理：

#coding = utf-8
import datetime
#a = datetime.datetime.now()
'''
Create by 古月随笔
'''
class op_date(object):
    def day_get(self,d):
        if type(d).__name__ == "str":
            d = datetime.datetime.strptime(d,'%Y-%m-%d %H:%M:%S')
        oneday = datetime.timedelta(days=1)
        day = d - oneday
        date_from = datetime.datetime(day.year, day.month, day.day, 0, 0, 0)
        date_to = datetime.datetime(day.year, day.month, day.day, 23, 59, 59)
        print '---'.join([str(date_from), str(date_to)])

    def week_get(self,d):
        if type(d).__name__ == "str":
            d = datetime.datetime.strptime(d,'%Y-%m-%d %H:%M:%S')
        dayscount = datetime.timedelta(days=d.isoweekday())
        dayto = d - dayscount
        sixdays = datetime.timedelta(days=6)
        dayfrom = dayto - sixdays
        date_from = datetime.datetime(dayfrom.year, dayfrom.month, dayfrom.day, 0, 0, 0)
        date_to = datetime.datetime(dayto.year, dayto.month, dayto.day, 23, 59, 59)
        datelist=[[str(date_from)],[str(date_to)]]
        # print '---'.join([str(date_from), str(date_to)])
        return datelist

    def multi_week_get(self,d,num):
        if type(d).__name__ == "str":
            d = datetime.datetime.strptime(d,'%Y-%m-%d %H:%M:%S')
        date_num = []
        # date_num = [date_to1]
        for i in range(num-1,0,-1):
            dayscount = datetime.timedelta(days=d.isoweekday())
            dayto = d - dayscount
            sixdays = datetime.timedelta(days=6*i)
            dayfrom = dayto - sixdays
            date_from = str(datetime.datetime(dayfrom.year, dayfrom.month, dayfrom.day, 10, 0, 0))
            date_num.append(date_from)

        dayscount1 = datetime.timedelta(days=d.isoweekday())
        onedays = datetime.timedelta(days=1)
        dayto1 = d - dayscount1 + onedays
        date_to1 = str(datetime.datetime(dayto1.year, dayto1.month, dayto1.day, 10, 0, 0))
        date_num.append(date_to1)
        return date_num

    def month_get(self,d):
        if type(d).__name__ == "str":
            d = datetime.datetime.strptime(d,'%Y-%m-%d %H:%M:%S')
        dayscount = datetime.timedelta(days=d.day)
        dayto = d - dayscount
        date_from = datetime.datetime(dayto.year, dayto.month, 1, 0, 0, 0)
        date_to = datetime.datetime(dayto.year, dayto.month, dayto.day, 23, 59, 59)
        # print '---'.join([str(date_from), str(date_to)])
        datelist=[[str(date_from)],[str(date_to)]]
        return datelist

if __name__ == "__main__":
    op_date = op_date()
    # print op_date.week_get("2016-01-09 23:00:00")[0]
    #print op_date.week_get("2016-01-3 23:00:00")[1]
    print op_date.multi_week_get("2015-12-23 23:00:00",4)

统计 BUG 的 SQL 语句

#coding=utf-8

from op_date import *
from op_mysql import *
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
'''
Create by 古月随笔
'''
class config(object):
    def __init__(self):
        self.op_date = op_date()
        # self.ms = op_mysql(host="127.0.0.1",user="root",pwd="",db="zentao")
        self.ms = op_mysql(host="192.168.200.60",user="root",pwd="123456",db="zentao")

    #BUG状态分类:
    bugStatusList = [u'按周统计图', u'统计日期',u'新增', u'已解决',u'已关闭',u'未解决(累计)',u'延期解决(累计)',u'已关闭(累计)',u'总BUG数']
    #xxxx产品&项目
    hengha = [[u"哼哈微信端"],[u"哼哈商户端(android)"],[u"哼哈商户端(iOS)"],[u"哼哈后台"],[u"哼哈生活(产品)"]]
    #花xxxxx产品&项目
    hengha_week = [u"哼哈微信端",u"哼哈商户端(android)",u"哼哈商户端(iOS)",u"哼哈后台",u"哼哈生活(产品)"]

    #ERP
    erpyuvmp = [[u"ERP2.0(产品)"],[u"CRM(产品)"],[u"VMP(产品)"]]
    #ERP
    erpyuvmp_week = [u"ERP2.0(产品)",u"CRM(产品)",u"VMP(产品)"]


    def BugCountByProject(self,projectNo,sql_date):
        ms = self.ms
        data = []
        projectNo = int(projectNo)
        date_result = self.op_date.week_get(sql_date)
        start_date = date_result[0][0]
        end_date = date_result[1][0]
        #查找一个星期内新增的BUG数openedDate 例如今天为2016-01-04 00:00:00，输入这个时间后，会自动查询2015-12-28 00:00:00---2016-01-03 23:59:59时间段内BUG
        AllNewBugCount_OneWeek = "select count(*) from zt_bug where project = '%d' and deleted = '0' and openedDate >= '%s' and openedDate <= '%s'"%(projectNo,start_date,end_date)
        #查找一个星期内已解决的BUG数(以最近的星期天为准，计算星期一到星期天,包含本周 解决到关闭的BUG) resolvedDate
        AllResolvedBugCount_OneWeek = "select count(*) from zt_bug where project = '%d' and deleted = '0' and `status` <> 'active' and resolution <> 'postponed' and resolvedDate >= '%s' and resolvedDate <= '%s'"%(projectNo,start_date,end_date)
        #查找所有未解决BUG数(以最近的星期天为准，计算星期一到星期天)（当前显示BUG状态为未解决的。包含当前还没被解决的、之前遗留的未解决、以及reopen的BUG（累计数据））
        AllNotResolvedBugCount = "select count(*) from zt_bug where project = '%d' and deleted = '0' and `status` =  'active' and openedDate <= '%s'"%(projectNo,end_date)
        #查找用户所有延期解决的问题
        AllPostponedBugCount = "select count(*) from zt_bug where project = '%d' and deleted = '0' and `status` <> 'closed' and resolution = 'postponed' and resolvedDate <= '%s'"%(projectNo,end_date)
        #查找 一个星期内已关闭的BUG数(以最近的星期天为准，计算星期一到星期天) closedDate
        AllClosedBugCount_OneWeek = "select count(*) from zt_bug where project  = '%d' and deleted = '0' and `status` = 'closed' and closedDate >= '%s' and closedDate <= '%s'"%(projectNo,start_date,end_date)

        #查找 已关闭BUG数(累计)
        AllClosedBugCount = "select count(*) from zt_bug where project  = '%d' and deleted = '0' and `status` = 'closed' and closedDate <= '%s'"%(projectNo,end_date)

        #查找 总BUG数
        AllBugCount = "select count(*) from zt_bug where project  = '%d' and deleted = '0' and openedDate <='%s'"%(projectNo,end_date)

        #新增
        dAllNewBugCount_OneWeek = ms.ExecQuery(AllNewBugCount_OneWeek)[0][0]
        #已解决
        dAllResolvedBugCount_OneWeek = ms.ExecQuery(AllResolvedBugCount_OneWeek)[0][0]
        #已关闭
        dAllClosedBugCount_OneWeek = ms.ExecQuery(AllClosedBugCount_OneWeek)[0][0]
        #未解决(累计数据)
        dAllNotResolvedBugCount = ms.ExecQuery(AllNotResolvedBugCount)[0][0]
        #延期解决(累计数据)
        dAllPostponedBugCount = ms.ExecQuery(AllPostponedBugCount)[0][0]
        #已关闭(累计)
        dAllClosedBugCount = ms.ExecQuery(AllClosedBugCount)[0][0]
        #总BUG数
        dAllBugCount = ms.ExecQuery(AllBugCount)[0][0]
        data = ["%s~%s"%(start_date[:-9],end_date[:-9]),dAllNewBugCount_OneWeek,dAllResolvedBugCount_OneWeek,dAllClosedBugCount_OneWeek,dAllNotResolvedBugCount,dAllPostponedBugCount,dAllClosedBugCount,dAllBugCount]
        return data


    def BugCountByProduct(self,productNo,sql_date):
        ms = self.ms
        data = []
        productNo = int(productNo)
        date_result = self.op_date.week_get(sql_date)
        start_date = date_result[0][0]
        end_date = date_result[1][0]
        #查找一个星期内新增的BUG数openedDate 例如今天为2016-01-04 00:00:00，输入这个时间后，会自动查询2015-12-28 00:00:00---2016-01-03 23:59:59时间段内BUG
        AllNewBugCount_OneWeek = "select count(*) from zt_bug where product = '%d' and deleted = '0' and openedDate >= '%s' and openedDate <= '%s'"%(productNo,start_date,end_date)
        #查找一个星期内已解决的BUG数(以最近的星期天为准，计算星期一到星期天) resolvedDate
        AllResolvedBugCount_OneWeek = "select count(*) from zt_bug where product = '%d' and deleted = '0' and `status` <> 'active' and resolution <> 'postponed' and resolvedDate >= '%s' and resolvedDate <= '%s'"%(productNo,start_date,end_date)
        #查找 一个星期内已关闭的BUG数(以最近的星期天为准，计算星期一到星期天) closedDate
        AllClosedBugCount_OneWeek = "select count(*) from zt_bug where product  = '%d' and deleted = '0' and `status` = 'closed' and closedDate >= '%s' and closedDate <= '%s'"%(productNo,start_date,end_date)

        #查找所有未解决BUG数(以最近的星期天为准，计算星期一到星期天)（当前显示BUG状态为未解决的。包含当前还没被解决的、之前遗留的未解决、以及reopen的BUG（累计数据））
        AllNotResolvedBugCount = "select count(*) from zt_bug where product = '%d' and deleted = '0' and `status` =  'active' and openedDate <= '%s'"%(productNo,end_date)
        #查找用户所有延期解决的问题
        AllPostponedBugCount = "select count(*) from zt_bug where product = '%d' and deleted = '0' and `status` <> 'closed' and resolution = 'postponed'and resolvedDate <= '%s'"%(productNo,end_date)
        #查找 已关闭BUG数(累计)
        AllClosedBugCount = "select count(*) from zt_bug where product  = '%d' and deleted = '0' and `status` = 'closed' and closedDate <= '%s'"%(productNo,end_date)

        #查找 总BUG数
        AllBugCount = "select count(*) from zt_bug where product  = '%d' and deleted = '0'and openedDate <='%s'"%(productNo,end_date)

        #新增
        dAllNewBugCount_OneWeek = ms.ExecQuery(AllNewBugCount_OneWeek)[0][0]
        #已解决
        dAllResolvedBugCount_OneWeek = ms.ExecQuery(AllResolvedBugCount_OneWeek)[0][0]
        #已关闭
        dAllClosedBugCount_OneWeek = ms.ExecQuery(AllClosedBugCount_OneWeek)[0][0]
        #未解决(累计数据)
        dAllNotResolvedBugCount = ms.ExecQuery(AllNotResolvedBugCount)[0][0]
        #延期解决(累计数据)
        dAllPostponedBugCount = ms.ExecQuery(AllPostponedBugCount)[0][0]
        #已关闭(累计)
        dAllClosedBugCount = ms.ExecQuery(AllClosedBugCount)[0][0]
        #总BUG数
        dAllBugCount = ms.ExecQuery(AllBugCount)[0][0]
        data = ["%s~%s"%(start_date[:-9],end_date[:-9]),dAllNewBugCount_OneWeek,dAllResolvedBugCount_OneWeek,dAllClosedBugCount_OneWeek,dAllNotResolvedBugCount,dAllPostponedBugCount,dAllClosedBugCount,dAllBugCount]

        return data
    """
    花啦生活:
        按project统计
        花啦生活-微信用户端：37
        花啦生活--商户端:39
        花啦生活--运营后台:38
    ERP&CRM(Product):
        按照产品编号来进行统计
        ERP2.0 : 3
        CRM:25
        VMP :7
    """
    '''
    花啦生活(project)
    '''
    #哼哈生活-微信用户端：37
    henghawx_pjct = 37
    #哼哈生活--商户端(android):39
    henghashandroid_pjct = 39
    #哼哈生活-商户端（IOS）:64
    henghashios_pjct = 64
    #哼哈生活--运营后台:38
    henghayy_pjct = 38
    #哼哈生活 ： 22(产品)
    hengha_pdct = 22

    hh_pjct = [henghawx_pjct,henghashandroid_pjct,henghashios_pjct,henghayy_pjct]
    hh_pdct = [hengha_pdct]

    '''
    ERP&CRM(Product):
    '''
    # ERP2.0 : 3
    erp_pdct = 3
    # CRM:25
    crm_pdct = 25
    # VMP :7
    vmp_pdct = 7
    erp_pdct_list = [erp_pdct,crm_pdct,vmp_pdct]

if __name__ == "__main__":
    cn = config()
    data = []
    result1 = cn.BugCountByProject(cn.henghawx_pjct,"2016-01-06 00:00:00")
    data.append(result1)
    result2 = cn.BugCountByProject(cn.henghash_pjct,"2016-01-06 00:00:00")
    data.append(result2)
    result3 = cn.BugCountByProject(cn.henghayy_pjct,"2016-01-06 00:00:00")
    data.append(result3)
    print data

GitHub 地址: git@github.com:tigerge000/SQA_BUG_Count.git