背景
在禅道上提bug，由于不想一直登录着禅道，所以不能及时知道是否有需要自己处理的，公司统一办公软件用的飞书，飞书和禅道没有现成的关联，为了解决这一问题，自己编写代码进行修改
技术
springboot+httpclient (写完发现飞书有sdk,但是已经写完了，调用也简单，就先不用了）
解决思路
1、查询禅道数据库，查询出近一分钟刚刚提交并激活的禅道bug，查询结果中必须含有手机号或者邮箱
2、发送消息
2.1、获取飞书token
2.2、根据手机号或者邮箱获取用户id
2.3、编辑消息并发送消息给对应用户
实现效果
实现
这里先按正式环境的配置进行描述，其中遇到的问题和测试环境如何测试，放在最后面。
禅道查询语句
查询刚刚生成的bug
-- 必要字段说明：severity 严重等级、pri 优先级、assignedName 指派人、openName 创建人、openedDate 创建时间
-- u.mobile,u.phone,u.email 用于查询飞书用户
SELECT b.id as bugId , b.title, b.severity, b.pri, u.realname as assignedName, u1.realname as openName, u.mobile,u.phone,u.email, p.name as projectName,b.openedDate
FROM zt_bug b LEFT JOIN zt_user u ON b.assignedTo = u.account
LEFT JOIN zt_user u1 ON b.openedBy = u1.account
left join zt_project p on p.id = b.project
where b.status like '%active%' and b.assignedDate between date_add(now(), interval -1 minute) and date_add(now(), interval +1 minute);查询刚刚解决完的bug
-- 必要字段说明：severity 严重等级、pri 优先级、assignedName 指派人、openName 创建人、openedDate 创建时间
-- u.mobile,u.phone,u.email 用于查询飞书用户
SELECT b.id , b.title, b.severity, b.pri, u.realname as assignedName, u1.realname as openName,u.mobile,u.phone,u.email, p.name as projectName,b.openedDate
FROM zt_bug b
LEFT JOIN zt_user u ON b.assignedTo = u.account
LEFT JOIN zt_user u1 ON b.openedBy = u1.account
LEFT JOIN zt_project p on p.id = b.project
where b.status like '%resolved%'
and b.resolvedDate between date_add(now(), interval -100 minute) and date_add(now(), interval +1 minute);消息发送
2.1、飞书创建应用
打开飞书客户端->工作台->右上角 “创建应用”，进入网页。
在网页的右上角有个“开发者后台”，打开，屏幕中间“企业自建应用”，创建企业自建应用
输入应用信息，完成应用创建；需要申请的权限如下：
创建版本信息，然后发起申请，飞书部分准备完成。
代码：
插件
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>commons-httpclient</groupId>
<artifactId>commons-httpclient</artifactId>
<version>3.1</version>
</dependency>工具类：连接飞书，获取token,用户id，发送消息
package com.project.generator.utils;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.methods.PostMethod;
import org.apache.commons.httpclient.methods.StringRequestEntity;
import java.io.IOException;
/**
* @program: generate_code
* @description: http 连接
* @author:
* @create: 2023-02-22 15:28
*/
public class HttpUtils {
public final static String app_id = "cli_*************";
public final static String app_secret = "***************************";
public final static String url_token = "https://open.feishu.cn/open-apis/auth/v3/tenant_access_token/internal";
public final static String url_user_id = "https://open.feishu.cn/open-apis/contact/v3/users/batch_get_id?user_id_type=user_id";
public final static String url_send_user = "https://open.feishu.cn/open-apis/im/v1/messages?receive_id_type=user_id";
public static String getToken() throws IOException {
String token = null;
HttpClient httpClient = new HttpClient();
// // 获取token 直接把上面获取token对应的请求头请求体 和url粘贴过来就可以了
PostMethod postMethod = new PostMethod(url_token);
// headers='Content-Type:application/json'
postMethod.addRequestHeader("Content-Type", "application/json");
JSONObject data = new JSONObject();
data.put("app_id", app_id);
data.put("app_secret", app_secret);
StringRequestEntity stringRequestEntity = new StringRequestEntity(data.toString(),"application/json","UTF-8");
postMethod.setRequestEntity(stringRequestEntity);
int code = httpClient.executeMethod(postMethod);
System.out.println();
if (code == 200) {
String result = postMethod.getResponseBodyAsString();
System.out.println("result:" + result);
//tenant_access_token
JSONObject resultJson = JSONObject.parseObject(result);
token = resultJson.getString("tenant_access_token");
}
System.out.println();
return token;
}
public static String getFeiShuUserId(String[] mobiles) throws IOException {
String user_id = null;
String token = getToken();
if (token == null) {
return null;
}
HttpClient httpClient = new HttpClient();
PostMethod postMethod = new PostMethod(url_user_id);
postMethod.addRequestHeader("Content-Type", "application/json");
postMethod.addRequestHeader("Authorization", "Bearer " + token);
JSONObject data = new JSONObject();
data.put("mobiles", mobiles);
StringRequestEntity stringRequestEntity = new StringRequestEntity(data.toString(),"application/json","UTF-8");
postMethod.setRequestEntity(stringRequestEntity);
int code = httpClient.executeMethod(postMethod);
if (code == 200) {
String result = postMethod.getResponseBodyAsString();
System.out.println("result:" + result);
JSONObject resultJson = JSONObject.parseObject(result);
JSONObject resultData = resultJson.getJSONObject("data");
JSONArray user_list = resultData.getJSONArray("user_list");
user_id = user_list.getJSONObject(0).getString("user_id");
}
return user_id;
}
public static String sendFeiShu(String userId,JSONObject json) throws IOException {
String token = getToken();
if (token == null) {
return null;
}
HttpClient httpClient = new HttpClient();
PostMethod postMethod = new PostMethod(url_send_user);
postMethod.addRequestHeader("Content-Type", "application/json");
postMethod.addRequestHeader("Authorization", "Bearer " + token);
// 拼接消息
JSONObject data = new JSONObject();
data.put("msg_type", "interactive");
data.put("receive_id",userId);
data.put("content",json.toString());
StringRequestEntity stringRequestEntity = new StringRequestEntity(data.toString(),"application/json","UTF-8");
System.out.println(data.toString());
postMethod.setRequestEntity(stringRequestEntity);
int code = httpClient.executeMethod(postMethod);
if (code == 200) {
String result = postMethod.getResponseBodyAsString();
System.out.println("result:" + result);
JSONObject resultJson = JSONObject.parseObject(result);
JSONObject resultData = resultJson.getJSONObject("data");
System.out.println(resultData.toString());
}
return null;
}
}
实体类：消息实体
package com.project.generator.model;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @program: generate_code
* @description: 卡片子项
* @author:
* @create: 2023-02-23 16:05
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ElementVo {
private String tag;
private String content;
}
查询接收实体类：
package org.javaboy.generate_code.entity;
import lombok.Data;
/**
* @program: generate_code
* @description: 禅道
* @author:
* @create: 2023-02-22 15:23
*/
@Data
public class ZenTaoVO {
private String id;
private String title;
private String severity;
private String pri;
private String realname;
private String assignedName;
private String openName;
private String projectName;
private String mobile;
private String phone;
private String email;
private String openedDate;
}
测试类
@Test
public void Test() throws SQLException, ClassNotFoundException, IOException {
// 连接数据库
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3307/zentao?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root", "123456");
Class.forName("com.mysql.jdbc.Driver");
// 运行sql
List<Object> params = new ArrayList<>();
PreparedStatement pst = null;
ResultSet rst = null;
// 获取bug
String sql = "SELECT b.id , b.title, b.severity, b.pri, u.realname as assignedName, u1.realname as openName," +
" u.mobile,u.phone,u.email,p.name as projectName,b.openedDate" +
"FROM zt_bug b LEFT JOIN zt_user u ON b.assignedTo = u.account " +
"LEFT JOIN zt_user u1 ON b.openedBy = u1.account " +
"left join zt_project p on p.id = b.project " +
"WHERE b.status like '%active%' and b.assignedDate " +
"between date_add(now(), interval -1 minute) and date_add(now(), interval +1 minute);";
pst = connection.prepareStatement(sql);
rst = pst.executeQuery();
List<ZenTaoVO> list = new ArrayList<>();
while (rst.next()) {
ZenTaoVO taoVO = new ZenTaoVO();
taoVO.setId(rst.getString("id"));
taoVO.setTitle(rst.getString("title"));
taoVO.setSeverity(rst.getString("severity"));
taoVO.setRealname(rst.getString("realname"));
taoVO.setOpenName(rst.getString("openname"));
taoVO.setMobile(rst.getString("mobile"));
taoVO.setPhone(rst.getString("phone"));
taoVO.setEmail(rst.getString("email"));
taoVO.setProjectName(rst.getString("projectname"));
taoVO.setPri(rst.getString("pri"));
taoVO.setOpenedDate(DateUtil.formatDate(rst.getDate("createtime")));
list.add(taoVO);
}
//#禅道bug详情页面链接 http://localhost:8000/zentao/bug-view-17.html
String bugBaseUrl = "http://localhost:8000/zentao/bug-view-";
// 获取分析结果
for (ZenTaoVO taoVO : list) {
if (taoVO.getMobile() == null) {
continue;
}
String bugUrl = bugBaseUrl + taoVO.getId() + ".html";
// 根据手机号获取用户id，也可以用phone，或者邮箱，目前没有判断
String userId = HttpUtils.getFeiShuUserId(new String[]{taoVO.getMobile()});
if (userId == null) {
continue;
}
// 拼接消息
JSONObject msgUrl = new JSONObject();
JSONObject headerJson = new JSONObject();
msgUrl.put("header", headerJson);
// 警告 红色-template 消息 绿色-green 蓝色-blue
headerJson.put("template", "red");
headerJson.put("title", new ElementVo("plain_text", "禅道消息通知"));
JSONArray elements = new JSONArray();
String msgMd = "**Bug标题：**" + taoVO.getTitle() + "\n**优先级：** <font color='red'>" + taoVO.getPri()
+ "</font>\n**创建者：** " + taoVO.getOpenName()
+ "\n**创建时间：**" + taoVO.getOpenedDate()
+ "\n**Bug地址：**[" + bugUrl + "](" + bugUrl + ")\n\n有您的bug待签收，请及时处理~";
elements.add(JSONObject.toJSON(new ElementVo("markdown", msgMd)));
msgUrl.put("elements", elements);
HttpUtils.sendFeiShu(userId, msgUrl);
}
return;
}测试环境搭建以及遇到的问题
1、测试账号建立参考官方连接，基本是建立测试企业和用户，在测试企业里测试接口和调用，一切正确后切换正式企业，解散测试企业
2、禅道数据库连接问题：数据库连接不上
Navicat 连不上数据库：
禅道数据库端口号为 3307
先本地 给root用户加上连接所有连接的权限
在禅道目录 找到run/mysql/
./mysql -u root -p
# 输入密码 默认密码123456
#输入修改语句
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
# 权限刷新
flush privileges;
#退出数据库
quit;
#修改mysql 配置文件 屏蔽 bind-address = 127.0.0.1 即加井号
#bind-address = 127.0.0.1
# 重启禅道
zbox/zbox restart
#运行命令后的结果
Restart Apache success
Restart Mysql success
4969
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
