禅道bug统计并钉钉机器人发送html统计详情[复制即用]

一、背景

现在大多数的公司对bug管理基本上是使用禅道，一方面禅道是国产开源，另一方面禅道也提供了统计报表和钉钉机器人接入。但是由于我们公司是使用的低于17版本的禅道，无法对统计报表进行自定义，所以自己就研究了一下，使用python脚本进行需求的实现。需求是每周以人员为维度进行bug统计。我这里进行了周统计和月统计，并可以钉钉告警，好了费话少说，我们上代码：

二、实现

主要使用了5个python脚本：db_config.py ，mysql.py，dingtalk.py ，run_week.py ,run_month.py。详细的内容和使用注意的点，我会在脚本中标识。

1、db_config.py

#/usr/bin/pythonclassmysql_config():'''def __init__(self,name):
        #print 'aaaa'
        self.name = name
        print name
    '''defget_config(self,name):
        self.name = name
        config ={'zentao':{'host':'127.0.0.1','user':'root','passwd':'123456','db':'zentao','port':3307,'charset':'utf8'#这里是解决中文乱码情况},}return config[name]

2、mysql.py

#/uer/bin/python                                                                                                                           import MySQLdb;import datetime
from db_config import mysql_config
m_config  = mysql_config()classdb_mysql():def__init__(self):print('class:db_mysql -import -true')#连接mysqldefconnect(self,name):#self.sql  = sql
        self.name = name
        try:#self.config = m_config.abc(name)
            config  = m_config.get_config(name)
            db = MySQLdb.connect(**config)
            cursor = db.cursor()#cursor.execute(sql)except MySQLdb.connector.Error as err:print("Something went wrong: {}".format(err))return cursor
#执行Sql语句defexecute(self,cursor,sql):
        cursor.execute(sql)return cursor
#获取全部结果deffetchall(self,cursor):
        data = cursor.fetchall()return data
#获取一个结果deffetchone(self,cursor):return cursor.fetchone()#查询bug汇总defbug_total(self,cursor,now,recent_sevenday):"""
        最近总的BUG情况统计统计
        :param: day 根据输入天数
        :return:
        """#新增bug数
        new_near_bug_sql ="""SELECT COUNT(*) as new_near_bug from zt_bug where openedDate between "%s" and "%s";"""%(recent_sevenday,now)
        cursor.execute(new_near_bug_sql)
        new_near_bug = cursor.fetchone()#已解决bug数
        close_bug_sql ="""SELECT COUNT(*) as close_bug from zt_bug where status = "closed" and openedDate between "%s" and "%s";"""%(recent_sevenday,now)
        cursor.execute(close_bug_sql)
        close_bug = cursor.fetchone()#未解决bug数
        open_bug_sql ="""SELECT COUNT(*) as open_bug from zt_bug where status = "active" and openedDate between "%s" and "%s";"""%(recent_sevenday,now)
        cursor.execute(open_bug_sql)
        open_bug = cursor.fetchone()#已解决待验证bug数
        close_unbug_sql ="""SELECT COUNT(*) as close_unbug from zt_bug where status = "resolved" and openedDate between "%s" and "%s";"""%(recent_sevenday,now)
        cursor.execute(close_unbug_sql)
        close_unbug = cursor.fetchone()#提BUG、指派、转派、改BUG
        statistics_bug ="新增BUG数:{0} \n\n 未解决BUG数:{1} \n\n 已解决BUG数:{2} \n\n 已解决待验证BUG数:{3}".format(new_near_bug[0],open_bug[0],close_bug[0],close_unbug[0])return statistics_bug
# 查询bug明细defbug_detail(self,cursor,now,recent_sevenday):"""
        最近总的BUG情况统计明细数据
        :param: day 根据输入天数
        :return:
        """
        cursor.execute("""select "%s" as 开始时间 ,"%s" as 结束时间,u.realname as 姓名 ,b.assignedTo as 用户名,count(*) as 总bug数, sum(case when b.status="active" then 1 else 0  end) as 未解决bug数,sum(case when b.status="resolved" then 1 else 0 end) as 已解决待验证bug数,sum(case when b.status="closed" then 1 else 0 end) as 已解决bug数 from zt_bug b left join zt_user u on b.assignedTo = u.account where b.status and b.openedDate BETWEEN "%s" and "%s" group by b.assignedTo order by 总bug数 desc;"""%(recent_sevenday,now,recent_sevenday,now))return cursor

3、dingtalk.py

html详情页面可根据自己的情况放置

import json
import urllib.request
import datetime
from dingtalkchatbot.chatbot import DingtalkChatbot, FeedLink

classding_talk():defsend_bug(self,url, data_file,sign_mark):
        xiaoding = DingtalkChatbot(url)# Markdown消息@所有人
        now = datetime.datetime.now().strftime("%Y%m%d")
        html="http://192.168.88.88/zentao/html/bug_detail_{0}.html".format(now)print(html)
        xiaoding.send_markdown(title='BUG统计', text='#### **{0}禅道BUG情况统计**\n\n 各位同学，以下是{1}的禅道BUG情况统计，详情页为{2}个人Bug汇总，如有自己未解决的BUG数据，请尽快处理！\n\n {3} \n\n[查看详情](http://192.168.88.88/zentao/html/bug_detail_{4}.html) \n'.format(sign_mark,sign_mark,sign_mark,data_file,now),is_at_all=True)#html_file /opt/zbox/app/zentao/www/html/test.htmldefbug_html(self,lis ,html_file):"""
        对查询bug明细转html文件
        :param lis
        :param html_file
        """
        conten_title =[]for key in lis.description:
            conten_title.append(key[0])
        a ="</th><th>".join(conten_title)
        con_title ="<tr><th>"+ a +"</th></tr>"
        conten_val =""
        con =""
        lis_arr = lis.fetchall()for i inrange(0,len(lis_arr)):for index, v inenumerate(lis_arr[i]):if index ==0:
                    conten_val ="<tr><td>"+ lis_arr[i][index]+"</td><td>"
                    con = con + conten_val;continue
                con = con +str(lis_arr[i][index])+"</td><td>"
            con = con[0:-2]+"r>"
            con = con +"\n"
        head ="""<meta charset="utf-8">
        <style type="text/css">
        table.tftable {font-size:12px;color:#333333;width:100%;border-width: 1px;border-color: #9dcc7a;border-collapse: collapse;}
        table.tftable th {font-size:12px;background-color:#abd28e;border-width: 1px;padding: 8px;border-style: solid;border-color: #9dcc7a;text-align:left;}
        table.tftable tr {background-color:#ffffff;}
        table.tftable td {font-size:12px;border-width: 1px;padding: 8px;border-style: solid;border-color: #9dcc7a;}
        </style>\n<table id="tfhover" class="tftable" border="1">\n"""
        last ="</table>"
        htm = head + con_title + con + last
        withopen(html_file,"w", encoding="utf-8")as f:
            f.write(htm)

4、run_week.py

这里要用到钉钉机器人的webhook，要在钉钉群添加后复制webhook，在相应的位置替换

#/usr/bin/python/                                                                                                                       import datetime
from mysql import db_mysql
from dingtalk import ding_talk 
ding_ding = ding_talk()
mysql_obj = db_mysql()#获取时间:
now = datetime.datetime.now().strftime("%Y-%m-%d")
recent_sevenday =(datetime.datetime.now()- datetime.timedelta(days=4)).strftime("%Y-%m-%d")#bug明细写入html
cursor_connect  = mysql_obj.connect('zentao')
cursor_execute  = mysql_obj.bug_detail(cursor_connect,now,recent_sevenday)
today_ymd = datetime.datetime.now().strftime("%Y%m%d")
html ="/opt/zbox/app/zentao/www/html/bug_detail_{0}.html".format(today_ymd)
ding_ding.bug_html(cursor_execute,html)#bug统计
seven_count  = mysql_obj.bug_total(cursor_connect,now,recent_sevenday)
url="https://oapi.dingtalk.com/robot/send?access_token=1111111111"
ding_ding.send_bug(url,seven_count,"本周");

4、run_month.py

#/usr/bin/python/   import calendar
import datetime
from datetime import timedelta
from mysql import db_mysql
from dingtalk import ding_talk 
ding_ding = ding_talk()
mysql_obj = db_mysql()#获取时间:
now = datetime.datetime.now()
this_month_start = datetime.datetime(now.year, now.month,1)
last_month_end = this_month_start - timedelta(days=1) 
last_month_start = datetime.datetime(last_month_end.year, last_month_end.month,1).strftime("%Y-%m-%d")
last_month_end_str = last_month_end.strftime("%Y-%m-%d")
last_month = last_month_end.month
month_sign ="{0}月".format(last_month)#bug明细写入html
cursor_connect  = mysql_obj.connect('zentao')
cursor_execute  = mysql_obj.bug_detail(cursor_connect,last_month_end_str,last_month_start)
today_ymd = datetime.datetime.now().strftime("%Y%m%d")
html ="/opt/zbox/app/zentao/www/html/bug_detail_{0}.html".format(today_ymd)
ding_ding.bug_html(cursor_execute,html)#bug统计
month_count  = mysql_obj.bug_total(cursor_connect,last_month_end_str,last_month_start)
url="https://oapi.dingtalk.com/robot/send?access_token=1111111"
ding_ding.send_bug(url,month_count,month_sign);

展示效果
机器人效果：

查看详情展示：

以上就全部的脚本及结果。在自己研究过程中参照了以下文章，如果我的文章无法满足你的需要，可以参照下他们的，或许能给你一些灵感。
python数据统计之禅道bug统计
DingtalkChatbot 1.5.3