# 流水线监控

监听新 projId，自动补全内容，获取从 20240101 到当前的所有流水线信息

监听功能未知原因不好用，先不做了，改手动遍历了

拿到 project_id 后，获取数据表中最新的 pipeline 的 Id，然后比对接口中的 ID 进行填充

如果没有 pipeline 的 id，直接从接口中获取 20240101 到当前的流水线信息

先从数据中获取用户信息填充，随后在根据填充完的用户信息获取全部的 userid 的列表，再写 pipeline 表

# 流水线成功提醒

接收Gitlab 的 WebHooks请求，Secret 令牌 实际上为 Egg 的 API_KEY

获取对应的MR信息，MR 的发起者也需要发送通知

组织卡片信息，给 Commit的用户以及 可能的 MR发起者发送通知

## 处理在中间Stage需要提醒的情况

~~在stage全部成功的情况下，按finish_at时间排序，找到最后一个stage，如果stage的状态是成功，且该build的id没有发送过通知，则发送通知~~

流水线通知只是在Pipeline纬度上，所以某个stage的变化不会触发通知

在流水线为`running`时，如果需要监听Stage，且该stage全部的job有非结束状态时即`created`、`pending`、`running`、`manual`、`scheduled`时

加入数据库监控列表，每10s检查一次，如果stage状态全部成功的时候则发送通知，并删除监控

在流水线结束，即状态为`failed`、`canceled`、`skipped`、`success`，的时候，删除监控，并在状态为`success`的时候，发送通知

# 数据库表信息

[数据库地址](https://gitlab-pb.xiaomiwh.cn/_/)

project 信息

```js
{
  id: 'aaa',
  project_id: 131366,
  description: "场景复现平台-展示设备（移动、音箱、小爱建议、车载、手表等设备）上小爱执行结果及相关处理流程",
  name: "ai-scene-review-fe",
  path_with_namespace: "miai-fe/fe/ai-scene-review-fe",
  web_url: "https://git.n.xiaomi.com/miai-fe/fe/ai-scene-review-fe",
  avatar_url: null,
  has_new_cicd: false,
}
```

pipeline 信息

```js
{
  id: 'bbb',
  project_id: 'aaa',
  user_id: 'ccc',
  pipeline_id: 7646046,
  ref: "preview",
  status: "success",
  web_url: "https://git.n.xiaomi.com/miai-fe/fe/ai-scene-review-fe/-/pipelines/7646046",
  started_at: "2024-03-01T16:47:40.192+08:00",
  finished_at: "2024-03-01T16:49:30.624+08:00",
  duration: 100,
  queued_duration: 6,
}
```

user 信息

```js
{
  id: 'ccc',
  user_id: 10011,
  username: "zhaoyingbo",
  name: "赵英博",
  avatar_url: "https://git.n.xiaomi.com/uploads/-/system/user/avatar/10011/avatar.png",
  web_url: "https://git.n.xiaomi.com/zhaoyingbo"
}
```

每周每个项目按分支的运行时长统计 SQL `statisticsPerProj`

```SQL
SELECT
  (ROW_NUMBER() OVER()) as id,
  strftime('%Y-%W', datetime(pip.started_at)) AS week,
  p.name AS name,
  ROUND(AVG(pip.duration/60.0), 1) AS duration,
  pip.ref
FROM project p
JOIN pipeline pip ON p.id = pip.project_id
GROUP BY name, week, pip.ref;
```

每周流水线运行统计 SQL `statisticsPerWeek`

```SQL
SELECT
  (ROW_NUMBER() OVER()) as id,
  strftime('%Y-%W', datetime(started_at)) AS week,
  COUNT(*) AS total_count,
  SUM(CASE WHEN status = 'success' THEN 0 ELSE 1 END) AS failed_count,
  SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count,
  ROUND(SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS success_rate,
  ROUND(AVG(duration/60.0), 1) AS duration
FROM pipeline
GROUP BY week;
```

# GPT

我有一个 sqlite 数据库，表如下
project 表

```js
{
  id: 'aaa',
  project_id: 131366,
  description: "场景复现平台-展示设备（移动、音箱、小爱建议、车载、手表等设备）上小爱执行结果及相关处理流程",
  name: "ai-scene-review-fe",
  path_with_namespace: "miai-fe/fe/ai-scene-review-fe",
  web_url: "https://git.n.xiaomi.com/miai-fe/fe/ai-scene-review-fe",
  avatar_url: null,
  has_new_cicd: false,
}
```

pipeline 表

```js
{
  id: 'bbb',
  project_id: 'aaa',
  user_id: 'ccc',
  pipeline_id: 7646046,
  ref: "preview",
  status: "success",
  web_url: "https://git.n.xiaomi.com/miai-fe/fe/ai-scene-review-fe/-/pipelines/7646046",
  started_at: "2024-03-01T16:47:40.192+08:00",
  finished_at: "2024-03-01T16:49:30.624+08:00",
  duration: 100,
  queued_duration: 6,
}
```

user 表

```js
{
  id: 'ccc',
  user_id: 10011,
  username: "zhaoyingbo",
  name: "赵英博",
  avatar_url: "https://git.n.xiaomi.com/uploads/-/system/user/avatar/10011/avatar.png",
  web_url: "https://git.n.xiaomi.com/zhaoyingbo"
}
```

我想按天展示每个项目的 pipeline 按 ref 区分的平均 duration，如何创建视图

# 机器人

## CICD 统计总结

卡片 ID：ctp_AAyVLS6Q37cL

JSON 示例

```json
{
  "total_count": "29", // OK
  "group_table": [
    {
      "project_name": "ai-ak-fe",
      "project_ref": "master",
      "project_duration": "1.4",
      "project_duration_rate": "<font color='green'>↓12%</font>"
    },
    {
      "project_name": "ai-class-schedule-fe",
      "project_ref": "preview",
      "project_duration": "3.2",
      "project_duration_rate": "<font color='red'>↑5%</font>"
    },
    {
      "project_name": "ai-scene-review-fe",
      "project_ref": "staging",
      "project_duration": "5.4",
      "project_duration_rate": "<font color='green'>↓6%</font>"
    }
  ],
  "weekly_count_rate": "<font color='red'>较上周 ↑5%</font>", // OK
  "weekly_duration_rate": "<font color='green'>较上周 ↓12%</font>", // OK
  "weekly_success_rate": "<font color='red'>较上周 ↑12%</font>", // OK
  "duration": "0.9", // OK
  "success_rate": "100", // OK
  "has_new_cicd_count": "15", // OK
  "without_new_cicd_count": "20" // OK
}
```

## 流水线成功通知

卡片 ID：ctp_AA36QafWyob2

JSON 示例

```json
{
  "project": "cloudml-visuals/fe/test",
  "project_link": "https://git.n.xiaomi.com/cloudml-visuals/fe/test",
  "pipeline": "8815519",
  "pipeline_link": "https://git.n.xiaomi.com/cloudml-visuals/fe/cloud-ml-fe/-/pipelines",
  "ref": "preview",
  "ref_link": "https://git.n.xiaomi.com/cloudml-visuals/fe/cloud-ml-fe/-/commits/preview",
  "commit_user": "赵英博",
  "duration": "1m35s",
  "participant": "赵英博、吴婷",
  "commit_message": "chore: Add .gitlab-ci.yml for printing \"Hello, world!\"",
  "mr": "cloudml-visuals/fe/cloud-ml-fe!374",
  "mr_link": "https://git.n.xiaomi.com/cloudml-visuals/fe/cloud-ml-fe/-/merge_requests/374"
}
```