背景
通過定期輸出 每條產品的 BUG 情況，以此來反饋開發解決問題、測試跟進問題的情況；釘釘群推送提醒開發及時解決
以此我這邊開始著手準備編寫一個小工具，最終達到目的：自動定期傳送統計報告，報告維度（資料 + html展示）。
技術選型
python + markdown + pymysql + html + jenkins + 釘釘機器人
實現思路
python主要用到sshtunnel，pysql庫跳板機連線mysql資料庫，loguru庫日誌記錄，yaml記錄專案引數，request調取釘釘介面
- 讀取禪道資料庫資料及狀態，封裝sql類，把各維度統計資料通過dic格式返回
- 禪道bug彙總資料進行進行拼接，生成模板寫入markdown檔案。（釘釘會支援簡單markdown格式，表格等等不支援）
- 禪道bug明細資料，生成html頁面（沒找到合適的三方庫只能手動擼）
- 調取釘釘自定義介面，進行資料請求。
- jenkins實現定期推送+html頁面展示
主要程式碼
1、禪道sql封裝
ps.公司服務國外的所以時區+8H
from datetime import datetime
from middleware.config_handler import db_connect
"""針對產品返回所有bug資訊"""
def sql_pakeage(productid):
"""
BUG狀態統計SQL封裝
:param productid:
:return:
"""
bug_sql = "select count(*) from zt_bug where product in %s and deleted='0'"% (
productid)
resolved_bug_sql = "select count(*) from zt_bug where product in %s and deleted = '0' and `status` = 'resolved' and resolution <> 'postponed' " % (
productid)
not_resolved_bug_sql = "select count(*) from zt_bug where product in %s and deleted = '0' and `status` = 'active' " % (
productid)
postponed_bug_sql = "select count(*) from zt_bug where product in %s and deleted = '0' and `status` <> 'closed' and resolution = 'postponed' " % (
productid)
closed_bug_sql = "select count(*) from zt_bug where product in %s and deleted = '0' and `status` = 'closed' " % (
productid)
return bug_sql,resolved_bug_sql,not_resolved_bug_sql,postponed_bug_sql,closed_bug_sql
def test_product_bug(productid):
"""
產品BUG情況統計
:param productid:
:return:
"""
#總bug數
all_bug=db_connect.query_sql_df(sql_pakeage(productid)[0])
#已解決bug數
resolved_bug = db_connect.query_sql_df(sql_pakeage(productid)[1])
# 未解決BUG數（當前顯示BUG狀態為未解決的。包含當前還沒被解決的、之前遺留的未解決、以及reopen的BUG（累計資料））
not_resolved_bug = db_connect.query_sql_df(sql_pakeage(productid)[2])
# 延期BUG數
postponed_bug= db_connect.query_sql_df( sql_pakeage(productid)[3])
# 已關閉BUG數
closed_bug = db_connect.query_sql_df(sql_pakeage(productid)[4])
statistics_bug = { "總BUG數":all_bug[0],"已解決BUG": resolved_bug[0], "未解決BUG": not_resolved_bug[0], "已關閉BUG": closed_bug[0],
"延期解決BUG": postponed_bug[0]}
print(statistics_bug)
return statistics_bug
def test_product_bug_near(day, product):
"""
最近總的BUG情況統計統計
:param: day 根據輸入天數
:return:
"""
now = (datetime.datetime.now() + datetime.timedelta(hours=8)).strftime('%Y-%m-%d %H:%M:%S') #伺服器時區要 + 8h
recent_sevenday = (datetime.datetime.now() - datetime.timedelta(days=day)).strftime("%Y-%m-%d %H:%M:%S")
new_near_bug = db_connect.query_sql_df("""SELECT count(*) from zt_bug b where b.product in %s and openedDate between "%s" and "%s";"""%(product, recent_sevenday, now))
open_bug = db_connect.query_sql_df("""SELECT count(*) from zt_bug b where b.product in %s and b.STATUS = "active" and openedDate between "%s" and "%s";"""%(product, recent_sevenday, now))
close_bug = db_connect.query_sql_df("""SELECT count(*) from zt_bug b where b.product in %s and b.STATUS = "closed" and openedDate between "%s" and "%s";"""%(product, recent_sevenday, now))
close_unbug = db_connect.query_sql_df("""SELECT count(*) from zt_bug b where b.product in %s and b.STATUS = "resolved" and openedDate between "%s" and "%s";"""%(product, recent_sevenday, now))
statistics_bug = { "本週新增BUG數":new_near_bug[0], "本週未解決BUG數":open_bug[0],"本週已解決BUG數":close_bug[0],"本週已解決待驗證BUG數":close_unbug[0]}
return statistics_bug
def bug_count(day, product):
"""
最近總的BUG情況統計明細資料
:param: day 根據輸入天數
: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 = db_connect.query_sql_df(bug_sql)
return recent_sevenday_bug
2、釘釘介面推送
import json
import urllib.request
from config.path_config import data_file
from handler.loguru_handler import logger
def send_bug(url, data_file):
# url = r"https://oapi.dingtalk.com/robot/send?access_token=XXXXXXX"
logger.info("構建釘釘機器人的webhook")
header = {"Content-Type": "application/json","Charset": "UTF-8"}
with open(data_file, "r", encoding="utf-8") as f:
conntent = f.read()
data = {
"msgtype": "markdown",
"markdown": {
"title": "測試bug統計",
"text": conntent
},
"at": {
"isAtAll": False #@全體成員（在此可設定@特定某人）
}
}
logger.info(f"構建請求內容:{data}")
sendData = json.dumps(data)
sendData = sendData.encode("utf-8")
request = urllib.request.Request(url=url, data=sendData, headers=header)
logger.info("傳送請求")
opener = urllib.request.urlopen(request)
logger.info("請求求發回的資料構建成為檔案格式")
res = opener.read()
logger.info(f"返回結果為:{res}")
3、bug明細轉html檔案
def bug_html(lis ,html_file):
"""
對查詢bug明細轉html檔案
:param lis
:param html_file
"""
conten_title = []
for key in lis[0]:
conten_title.append(key)
a = "</th><th>".join(conten_title)
con_title = "<tr><th>" + a + "</th></tr>"
conten_val = []
con = ""
for i in range(0, len(lis)):
for index, v in enumerate(lis[i]):
if index ==0:
lis[i][v] ="<tr><td>" + lis[i][v]
con = con + str(lis[i][v]) + "</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
with open(html_file, "w", encoding="utf-8") as f:
f.write(htm)
另外功能基類以及呼叫傳送程式碼比較簡單就不展示了。
業務統計
按周統計(月份統計同理):
新增，新增日期為本週內的（包括本週內被解決或關閉的 BUG）
已解決，解決日期為本週內的。被開發設定為已解決的。其中可能有部分是上週遺留下來的，體現了開發在本週的變化情況（包括設計如此、重複 BUG、外部原因、無法重現、不予解決、轉為需求），不包含延期處理
已關閉，關閉日期為本週內的。是測試驗證過，確實已經解決的，包括其中有的是上週遺留下來的
未解決，當前顯示 BUG 狀態為未解決的。包含當前還沒被解決的、之前遺留的未解決、以及 reopen 的 BUG（累計資料）
延期解決，當前顯示 BUG 狀態為 延期處理的。BUG 狀態中新增一個延期解決 (累計資料)
應用截圖
釘釘通知
明細資料展示
