禅道消息同步到飞书

慢慢来_

已于 2023-02-24 14:12:53 修改

阅读量2k

点赞数

分类专栏：其他文章标签：飞书 spring boot java

于 2023-02-24 13:59:47 首次发布

本文链接：https://blog.csdn.net/qq_26818839/article/details/129197914

版权

其他专栏收录该内容

16 篇文章

订阅专栏

背景

在禅道上提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、测试账号建立参考官方连接，基本是建立测试企业和用户，在测试企业里测试接口和调用，一切正确后切换正式企业，解散测试企业

https://open.feishu.cn/document/home/introduction-to-custom-app-development/testing-enterprise-and-personnel-functions?lang=zh-CN

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