测试之家

持续集成 [JAVA 版] 基于禅道统计 BUG 趋势图 及 持续集成落地

CC · 2017年08月14日 · 2332 次阅读

引言

团队形式:scrum 分组形式，每个 scrum 在禅道上有各自的产品模块，然后按照一个月 2 个迭代的方式持续进行；
需求:需要每天输出 当前 scrum 组当前迭代的 产品质量日报 (总 BUG 数，总激活数，总关闭数，目前哪个开发头上还挂着几个激活的 BUG 数)

设计

## 目标:采用钉钉方式，往各个 scrum 组群中发送日报，并且点击 详情的时候可以自动跳转到 html 报表页面，可筛选任何产品任何项目的产品质量图 (柱形图和饼图)
## 实现方案:
1.restful API 形式发布不同维度的禅道数据统计接口
2.Jenkins + 采用 python 调用 api 接口，并调用钉钉机器人接口,实现定期每天向钉钉群发送 日报

实现:

禅道查询方面性能比较一般，没有时间做过多优化，后续会进一步优化，总体思路减少 数据库的查询次数
## restful Api 实现
由于框架本身已经有连接了一个本地数据库，因此连接第三方数据库，就不再采用 jdbc.xml 方式进行连接
### 连接数据库实现
本地数据库存放禅道的连接地址，端口，用户名和密码

/**
 * 连接数据库
 * @return
 */
public Connection mysqlClient(TBasicInfoMapper basicInfoMapper){
    String driver = "com.mysql.jdbc.Driver";

    String mysqlHost = basicInfoMapper.selectByItemName("mysqlHost").getItemValue();
    String mysqlPort = basicInfoMapper.selectByItemName("mysqlPort").getItemValue();
    String mysqlDatabase = basicInfoMapper.selectByItemName("mysqlDatabase").getItemValue();
    String mysqlUserName = basicInfoMapper.selectByItemName("mysqlUserName").getItemValue();
    String mysqlUserPwd = basicInfoMapper.selectByItemName("mysqlUserPwd").getItemValue();

    String url = "jdbc:mysql://" + mysqlHost + ":" + mysqlPort + "/" + mysqlDatabase + "?zeroDateTimeBehavior=convertToNull&useSSL=false";

    try{
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url,mysqlUserName,mysqlUserPwd);

        if(connection.isClosed()){
            return null;
        }
        LOGGER.info("Succeeded connecting to the Database!");
        return connection;

    }catch (ClassNotFoundException e){
        LOGGER.info("Sorry,can`t find the Driver!");
        return null;
    }catch (Exception e){
        e.printStackTrace();
        return null;
    }

}
/**
 * 执行sql语句
 * @param sql
 * @return
 */
public ResultSet executeSql(String sql){
    try {
        Statement statement = mysqlClient(basicInfoMapper).createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        mysqlClient(basicInfoMapper).close();
        if(!mysqlClient(basicInfoMapper).isClosed()){
            mysqlClient(basicInfoMapper).close();
        }
        return resultSet;
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}
/**
 * 基于产品获取项目信息
 * @return
 */
public Result<Void> getProjectInfoViaProduct(String prodcutId){
    Result result = new Result();
    JSONArray projectList = projectInfoArray();
    StringBuffer sql = new StringBuffer();
    sql.append( "select * from zt_projectproduct ");
    if(prodcutId == null){
        result.setMsg("没有productId,请检查输入");
        result.setStatus(0);
        return result;
    }
    sql.append("where product = " + prodcutId + "");
    ResultSet resultSet = executeSql(sql.toString());
    JSONArray resultArray = new JSONArray();

    try {
        while (resultSet.next()){
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("id",resultSet.getInt("project"));
            jsonObject.put("name",getProjectName(projectList,String.valueOf(resultSet.getInt("project"))));
            resultArray.add(jsonObject);
        }
        resultSet.close();
        result.setStatus(0);
        result.setMsg("查询成功");
        result.setRes(resultArray);
        return result;
    } catch (Exception e) {
        e.printStackTrace();
        return result;
    }

}

核心 SQL

    /**
     * 针对产品，返回所有的BUG信息
     * @param bugParamsInfo
     * @return
     */
    public JSONArray totalBugInfo(BugParamsInfo bugParamsInfo){
        StringBuffer sql = new StringBuffer();

        sql.append("select * from zt_bug where deleted = '0'");

        if(bugParamsInfo.getFlag() ==null){
            sql.append(" and resolution in('fixed','','postponed')");
        }

        if(bugParamsInfo.getProductId() != null){
            sql.append(" and product = " + bugParamsInfo.getProductId());
        }

        if(bugParamsInfo.getProjectId() != null && !"0".equals(bugParamsInfo.getProjectId())){
            sql.append(" and project = " + bugParamsInfo.getProjectId());
        }

        if(bugParamsInfo.getModuleId() != null){
            sql.append(" and module = " + bugParamsInfo.getModuleId());
        }

        if(bugParamsInfo.getOs() != null){
            sql.append(" and os = '" + bugParamsInfo.getOs() + "'");
        }

        if(bugParamsInfo.getDeveloperName() != null){
            sql.append(" and (assignedTo = '" + bugParamsInfo.getDeveloperName() + "' or resolvedBy = '" + bugParamsInfo.getDeveloperName() + "')");
        }

        if(bugParamsInfo.getOpenedStartDate() != null){
            sql.append(" and openedDate >= '" + DateUtils.timestampToDate(bugParamsInfo.getOpenedStartDate())+ "'");
        }

        if(bugParamsInfo.getOpenedEndDate() != null){
            sql.append(" and openedDate <= '" + DateUtils.timestampToDate(bugParamsInfo.getOpenedEndDate())+ "'");
        }

        if(bugParamsInfo.getResolvedStartDate() != null){
            sql.append(" and resolvedDate >= '" + DateUtils.timestampToDate(bugParamsInfo.getResolvedStartDate())+ "'");
        }

        if(bugParamsInfo.getResolvedEndDate() != null){
            sql.append(" and resolvedDate <= '" + DateUtils.timestampToDate(bugParamsInfo.getResolvedEndDate())+ "'");
        }


        if(bugParamsInfo.getClosedStartDate() != null){
            sql.append(" and closedDate >= '" + DateUtils.timestampToDate(bugParamsInfo.getClosedStartDate())+ "'");
        }

        if(bugParamsInfo.getClosedEndDate() != null){
            sql.append(" and closedDate <= '" + DateUtils.timestampToDate(bugParamsInfo.getClosedEndDate())+ "'");
        }

        LOGGER.info("sql:" + sql.toString());
        ResultSet resultSet = executeSql(sql.toString());
        JSONArray resultArray = new JSONArray();
        try {
            while (resultSet.next()) {
                JSONObject bugInfo = new JSONObject();
                bugInfo.put("id", resultSet.getString("id"));
                bugInfo.put("product", resultSet.getString("product"));
                bugInfo.put("module", resultSet.getString("module"));
                bugInfo.put("project", resultSet.getString("project"));
                bugInfo.put("severity", resultSet.getString("severity"));
                bugInfo.put("status", resultSet.getString("status"));
                bugInfo.put("os", resultSet.getString("os"));
                bugInfo.put("activatedCount", resultSet.getString("activatedCount"));
                bugInfo.put("assignedTo", resultSet.getString("assignedTo"));
                bugInfo.put("resolution", resultSet.getString("resolution"));
                bugInfo.put("resolvedBy", resultSet.getString("resolvedBy"));
                if(resultSet.getDate("openedDate") != null) {
//                    bugInfo.put("openedDate", String.valueOf(resultSet.getDate("openedDate").getTime()));
                    bugInfo.put("openedDate", String.valueOf(DateUtils.strToDateLong(resultSet.getString("openedDate"))));
                }else {
                    bugInfo.put("openedDate", "0");
                }
                if(resultSet.getDate("resolvedDate") != null) {
                    bugInfo.put("resolvedDate", String.valueOf(DateUtils.strToDateLong(resultSet.getString("resolvedDate"))));
                }else {
                    bugInfo.put("resolvedDate", "0");
                }
                if(resultSet.getDate("closedDate") != null) {
                    bugInfo.put("closedDate", String.valueOf(DateUtils.strToDateLong(resultSet.getString("closedDate"))));
                }else {
                    bugInfo.put("closedDate", "0");
                }
                resultArray.add(bugInfo);

            }
            resultSet.close();
            return resultArray;
        } catch (SQLException e) {
            e.printStackTrace();
            return resultArray;
        }

    }

Python 语句

实现思路:
0.查询当前时间，获取到指定项目的名称和项目 ID
1.获取该产品指定项目的所有 BUG 信息
2.获取该产品指定项目的激活 BUG 归属

#coding=utf-8
from datetime import *
import sys
import urllib2
import json
reload(sys)
sys.setdefaultencoding('utf8')


# 发送到钉钉
def dingTalk(mToken,mContentStr):
    dingUrl = "https://oapi.dingtalk.com/robot/send?access_token=" + mToken;
    dingParams = {"msgtype": "text", "text": {"content": mContentStr}};

    dingMarkdown = {"msgtype": "markdown", "markdown": {"title": "时代在召唤!", "text": "#### " + mContentStr.replace(",",
                                                                                                                " \n\n") + "[查看报表详情](http://192.168.3.14:8980/static-test/testing/zentao.html?productId=" +
                                                                                   sys.argv[1] + "&projectId=" +
                                                                                   sys.argv[2] + ")"}};
    dingRequest = urllib2.Request(url=dingUrl, headers=headers, data=json.dumps(dingMarkdown));
    dingResponse = urllib2.urlopen(dingRequest);
    return ;

def getDayChar(mDay):
    mResult = "";
    if mDay >= 0 and mDay <=15:
        mResult = "a";
    else:
        mResult = "b";
    return mResult;

# 根据产品ID及当前月份 获取指定的projectId
def getProjectId(mProductId):
    mProjectId = 0;
    mMonth = date.today().month;
    mDay = date.today().day;

    host = "http://192.168.3.14:8980/solo-testframework/finger/bug"
    url = host + "/getProjectInfoViaProduct?productId=" + str(mProductId);
    headers = {"Content-Type": "application/json"}

    devRequest = urllib2.Request(url=url, headers=headers)
    devResponse = urllib2.urlopen(devRequest)
    devRes = json.loads(devResponse.read())
    listRes = devRes[u'res']
    for i in listRes:
        mProjectName = i['name'].encode("utf-8")
        if str(mMonth) in mProjectName and getDayChar(mDay) in mProjectName:
            print mProjectName
            mProjectId = i['id']
    return mProjectId

host = "http://192.168.3.14:8980/solo-testframework/finger/bug"
params = {"productId":sys.argv[1],"projectId":getProjectId(sys.argv[1])}
url = host + "/globalBugTotalInfo"
headers = {"Content-Type":"application/json"}
request = urllib2.Request(url=url, headers=headers, data=json.dumps(params))
response = urllib2.urlopen(request)
res= json.loads(response.read())
globatlStr = res['res']['des'];

devUrl = host + "/getBugInfoAsDeveloper?productId=" + sys.argv[1] + "&projectId=" + str(getProjectId(sys.argv[1]))
devRequest = urllib2.Request(url=devUrl, headers=headers)
devResponse = urllib2.urlopen(devRequest)
devRes= json.loads(devResponse.read())
devStr = devRes['res']['des'];

contentStr = globatlStr + "开发者Bug详情: \n\n" + devStr;

print contentStr
#测试钉钉Token
#token = "b1c3da8c469e4afe65415286d7d7c8e062442ef9a36e9a0a5f82d7083430c663"
#技术部token
token = "5cf0228c3217a441ad8352f95aaf1xxxxxxb83cb63feeaa9237fc69c7c4d4f53"
#基础scrum群token
base_token = "42e28e583fb870a24c1e945xxxxx7e9398fa1d279456125548b1e824191b2"
#课程scrum群token
course_token = "0b1a0b41cf6e8aba91be3xxxxxx00cdfd737195dd2148bc081e3a36d0c14"
#社区scrum群token
shequ_token = "c2320dc2d324c7c9bc4xxxxx66c5bfbab68f644a27d66fa1abb0598744"
    dingTalk(token,contentStr);
    if sys.argv[1] == "33":
        dingTalk(course_token,contentStr);
    elif sys.argv[1] == "34":
        dingTalk(base_token, contentStr);
    elif sys.argv[1] == "35":
        dingTalk(shequ_token, contentStr);

Jenkins 配置

1.参数化构建，把对应禅道-Scrum 的产品 id 填写进去，以空格 分割

2.构建

实现效果

PS:restful api 实现可参考的 github 中编写 app 发布平台的开源代码，前端采用 echart，这边暂不过多描述


3 个赞
如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作！
打赏支持
暂无回复。
需要 登录 后方可回复, 如果你还没有账号请点击这里 注册。