一、背景
通過定期輸出每條産品的 BUG 情況，以此來回報開發解決問題、測試跟進問題的情況；釘釘群推送提醒開發及時解決
以此我這邊開始着手準備編寫一個小工具，最終達到目的：自動定期發送統計報告，報告次元（資料 + html展示）
二、技術選型
python + markdown + pymysql + html + jenkins + 釘釘機器人
三、實作思路
python主要用到sshtunnel，pysql庫跳闆機連接配接mysql資料庫，yaml記錄項目配置參數，request調取釘釘接口
1、讀取禅道資料庫資料及狀态，封裝sql類，把各次元統計資料通過str格式傳回
2、禅道bug彙總資料進行進行拼接，生成模闆寫入markdown檔案。（釘釘會支援簡單markdown格式，表格等等不支援）
3、禅道bug明細資料，生成html頁面（沒找到合适的三方庫隻能手動撸）
4、調取釘釘自定義接口，進行資料請求。
5、jenkins實作定期推送+html頁面展示
四、功能代碼
1、禅道SQL統計代碼
import datetime
import pymysql
class MysqlDB(object):
def __init__(self, host, port, user, passwd, db):
self.conn = pymysql.connect(host=host,
port=port,
user=user,
passwd=passwd, # passwd 不是 password
db=db)
self.cur = self.conn.cursor()
def __del__(self): # 析構函數，執行個體删除時觸發
self.cur.close()
self.conn.close()
def bug_count(self, day, product):
"""
最近總的BUG情況統計明細資料
:param: day 根據輸入天數
:param: product 産品号
:return:
"""
now = (datetime.datetime.now() + datetime.timedelta(hours=8)).strftime('%Y-%m-%d %H:%M:%S') # 伺服器時區要 + 8h
now_day = datetime.datetime.now().strftime('%Y-%m-%d')
recent_sevenday = (datetime.datetime.now() - datetime.timedelta(days=day)).strftime("%Y-%m-%d %H:%M:%S")
bug_sql = """SELECT p.name,b.title,b.assignedTo,b.severity,b.type,b.status,b.openedBy, CAST(openedDate AS CHAR) AS openedDate from zt_bug b left join zt_product p on b.product = p.id
where b.STATUS <> 'closed' and b.product in (%s) and openedDate between "%s" and "%s";""" % (
product, recent_sevenday, now)
print(bug_sql)
recent_sevenday_bug = self.query(bug_sql)
return recent_sevenday_bug
# 查詢低代碼bug彙總
def bug_total(self, day, product):
"""
最近總的BUG情況統計統計
:param: day 根據輸入天數
:param: product 産品号
:return:
"""
now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print(now)
recent_sevenday = (datetime.datetime.now() - datetime.timedelta(days=day)).strftime('%Y-%m-%d')
print(recent_sevenday)
# 新增bug數
new_near_bug_sql = """SELECT COUNT(*) as new_near_bug from zt_bug where product = "%s" and openedDate between "%s" and "%s";""" % (
product, recent_sevenday, now)
self.cur.execute(new_near_bug_sql)
new_near_bug = self.cur.fetchone()
# 已解決bug數
# close_bug_sql = """SELECT COUNT(*) as close_bug from zt_bug where product = 39 and status = "closed" and openedDate between "%s" and "%s";""" % (
# recent_sevenday, now)
# self.cur.execute(close_bug_sql)
# close_bug = self.cur.fetchone()
# 未解決bug數
open_bug_sql = """SELECT COUNT(*) as open_bug from zt_bug where product = "%s" and status = "active" and openedDate between "%s" and "%s";""" % (
product, recent_sevenday, now)
self.cur.execute(open_bug_sql)
open_bug = self.cur.fetchone()
print("dayi", open_bug_sql)
# 未解決嚴重程度1級或優先級1級的bug
open_bug_sql1 = """SELECT COUNT(*) as open_bug from zt_bug where product = "%s" and status = "active" and openedDate between "%s" and "%s" and (severity=1 or pri=1);""" % (
product, recent_sevenday, now)
self.cur.execute(open_bug_sql1)
open_bug1 = self.cur.fetchone()
# 未解決嚴重程度2級或優先級2級的bug
open_bug_sql2 = """SELECT COUNT(*) as open_bug from zt_bug where product = "%s" and status = "active" and openedDate between "%s" and "%s" and ((severity=2 and pri>=2) or (severity>=2 and pri=2));""" % (
product, recent_sevenday, now)
self.cur.execute(open_bug_sql2)
open_bug2 = self.cur.fetchone()
print("11111", open_bug2)
# 未解決嚴重程度3級或優先級3級的bug
open_bug_sql3 = """SELECT COUNT(*) as open_bug from zt_bug where product = "%s" and status = "active" and openedDate between "%s" and "%s" and ((severity=3 and pri>=3) or (severity>=3 and pri=3));""" % (
product, recent_sevenday, now)
self.cur.execute(open_bug_sql3)
open_bug3 = self.cur.fetchone()
print("222", open_bug3)
# 未解決嚴重程度4級或優先級4級的bug
open_bug_sql4 = """SELECT COUNT(*) as open_bug from zt_bug where product = "%s" and status = "active" and openedDate between "%s" and "%s" and (severity=4 and pri=4);""" % (
product, recent_sevenday, now)
self.cur.execute(open_bug_sql4)
open_bug4 = self.cur.fetchone()
# 已解決待驗證bug數
close_unbug_sql = """SELECT COUNT(*) as close_unbug from zt_bug where product = "%s" and status = "resolved" and openedDate between "%s" and "%s";""" % (
product, recent_sevenday, now)
self.cur.execute(close_unbug_sql)
close_unbug = self.cur.fetchone()
# 提BUG、指派、轉派、改BUG
statistics_bug = "新增BUG數:{0} \n\n 未解決BUG數:{1} \n\n 未解決嚴重程度1級或優先級1級bug:{2} \n\n未解決嚴重程度2級或優先級2級bug:{3} \n\n未解決嚴重程度3級或優先級3級bug:{4} \n\n未解決嚴重程度4級或優先級4級bug:{5} \n\n 已解決待驗證BUG數:{6}".format(
new_near_bug[0],
open_bug[0], open_bug1[0], open_bug2[0], open_bug3[0], open_bug4[0],
close_unbug[0])
print(statistics_bug)
print(type(statistics_bug))
return statistics_bug
# 查詢bug明細
def bug_detail(self, day, product):
"""
最近總的BUG情況統計明細資料
:param: day 根據輸入天數
:param: product 産品号
:return:
"""
now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
recent_sevenday = (datetime.datetime.now() - datetime.timedelta(days=day)).strftime('%Y-%m-%d')
self.cur.execute(
"""select "%s" as 開始時間 ,"%s" as 結束時間,u.realname as 姓名 ,count(*) as 總bug數,
sum(case when b.status="active" then 1 else 0 end) as 未解決bug數,
sum(case when b.status="active" and (b.severity=1 or b.pri=1) then 1 else 0 end) as 未解決1級bug,
sum(case when b.status="active" and ((severity=2 and pri>=2) or (severity>=2 and pri=2)) then 1 else 0 end) as 未解決2級bug,
sum(case when b.status="active" and ((severity=3 and pri>=3) or (severity>=3 and pri=3)) then 1 else 0 end) as 未解決3級bug,
sum(case when b.status="active" and (b.severity=4 and b.pri=4) then 1 else 0 end) as 未解決4級bug,
sum(case when b.status="resolved" then 1 else 0 end) as 已解決待驗證bug數
from zt_bug b right join zt_user u on b.assignedTo = u.account
where b.status and b.openedDate BETWEEN "%s" and "%s" and b.product = "%s"
group by b.assignedTo order by 總bug數 desc;""" % (
recent_sevenday, now, recent_sevenday, now, product))
return self.cur 2、配置檔案代碼（讀取配置的代碼可以參考我之前寫的config.py的檔案）
zentao_mysql:
host: 禅道連結位址
port: 3306
user: 使用者名
passwd: 密碼
db: zentao
zentao_data:
# 我上傳的檔案伺服器位址，如果需要統一域名下的話，可以起個服務再輸入需要通路機器的ipv4位址
server: "http://xxxxxxxxxxx:63332/"
#釘釘機器人的webhook
url1: "https://oapi.dingtalk.com/robot/send?access_token=xxxxxxxxxxxxx"
sign_mark: "本周"
markdown:
title: "測試bug統計"
is_at_all: True # @全體成員（在此可設定@特定某人） 3、釘釘接口推送以及html界面繪制
import datetime
from dingtalkchatbot.chatbot import DingtalkChatbot, FeedLink
from utils.config import Config
class ding_talk():
def send_bug(self, data_file):
data = Config("/Users/leiyuxing/PycharmProjects/project/ZYL/bug_tj/db_config.yaml").get('zentao_data')
title = data.get('markdown').get('title')
server = data.get('server')
is_at_all = data.get('is_at_all')
url = data.get('url')
sign_mark = data.get('sign_mark')
xiaoding = DingtalkChatbot(url)
# Markdown消息@所有人
now = datetime.datetime.now().strftime("%Y%m%d%H%M")
now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
xiaoding.send_markdown(title=title,
text='#### **{0}禅道BUG情況統計**\n\n 各位同學，以下是截止到{1}的禅道BUG情況統計，詳情頁為個人Bug彙總明細，如有自己未解決的BUG資料，請盡快處理！\n\n {2} \n\n[檢視詳情]({3}bug_tj/bug_detail_{4}.html) \n'.format(
sign_mark, now_time, data_file, server, now), is_at_all=is_at_all)
def bug_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 in range(0, len(lis_arr)):
for index, v in enumerate(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"""
remarks = "<tr><td colspan=10><font size=4 color=red >備注說明:</font><font size=2 color=red ><br><br>1、總bug數 = 未解決bug數 + 已解決待驗證bug數<br>2、未解決bug數 = 未解決1級bug + 未解決2級bug + 未解決3級bug + 未解決4級bug<br>3、以上bug統計包含前幾個版本遺留的問題且在目前版本也需要處理的統計<br>4、以上人員排序按照總bug數倒序排序</br></font></td><tr>"
last = "</table>"
htm = head + con_title + con + remarks + last
with open(html_file, "w", encoding="utf-8") as f:
f.write(htm) 4、調用函數
import requests
import datetime
from bug_tj.dingtalk import ding_talk
from bug_tj.mysql import MysqlDB
from utils.config import Config
ding_ding = ding_talk()
mysql = Config("/Users/leiyuxing/PycharmProjects/project/ZYL/bug_tj/db_config.yaml").get('zentao_mysql')
HOST = mysql.get('host')
PORT = mysql.get('port')
USER = mysql.get('user')
PASSWD = mysql.get('passwd')
DB = mysql.get('db')
db = MysqlDB(HOST, PORT, USER, PASSWD, DB)
cursor_execute = db.bug_detail(30, 39)
print(cursor_execute)
today_ymd = datetime.datetime.now().strftime("%Y%m%d%H%M")
path = "/Users/leiyuxing/PycharmProjects/project/ZYL/bug_tj/bug_detail_{0}.html".format(today_ymd)
ding_ding.bug_html(cursor_execute, path)
seven_count = db.bug_total(30, 39)
print(seven_count)
ding_ding.send_bug(seven_count)
# 上傳檔案伺服器位址(這段是上傳寶塔伺服器的代碼）
url = "http://xxxxxxx:63333/file_upload"
files = {'file': open(path, 'rb')}
r = requests.post(url, files=files) 5、展示效果：
釘釘通知
檢視詳情：
五、業務統計規則說明
新增，新增日期為曆史遺留bug截止到本周内的bug
已解決，解決日期為本周内的。被開發設定為已解決的。其中可能有部分是上個版本或上周遺留下來的，展現了開發在本周的變化情況（包括設計如此、重複 BUG、外部原因、無法重制、不予解決、轉為需求），不包含延期處理
已關閉，關閉日期為截止到本周内的bug。是測試驗證過，确實已經解決的，包括其中有的是上幾個版本或上周遺留下來的
未解決，目前顯示 BUG 狀态為未解決的。包含目前還沒被解決的、之前遺留的未解決、以及 reopen 的 BUG（累計資料）
未解決一級bug，截止到目前嚴重程度一級或優先級一級的bug（二、三、四級同理），大概如下一個規則：
一級: （1，2），（1，1），（1，3），（1，4）(2,1),(3,1),(4,1). s=1 / p=1
二級：（2，2）（2，3）（3，2）（4，2）（2，4）s=2and p>=2 / s>=2,p=2
三級:（3，3）（3，4）(4,3). s=3, p>=3. s=32&p>=3 / s>=3,p=3
四級:（4，4）s=32& p>=3
以上就全部的腳本及結果。在自己研究過程中參照了以下文章，如果我的文章無法滿足你的需要，可以參照下他們的，或許能給你一些靈感。：
禅道bug統計并釘釘機器人發送html統計詳情[複制即用]