1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
| -- 生产设备大屏(dashboard_id=3) 新增 4 类统计维度（5 个图表） | 目标库 mom-hm(远端 36.213.150.237) | 2026-10-15
| -- 背景：该大屏此前仅有工单/设备计数、工单状态、工单类型、工单列表等，缺设备与维保维度的统计口径。
| -- 维度（均已按远端真实数据验证非空）：
| -- ① 设备按车间分布 (pie) 9 个车间 4~31 台
| -- ② 点检/保养月度趋势 (line) 2026-09/10（远端仅这两个月有记录），双序列
| -- ③ 点检完成率 (gauge) 81.0% = 已完成(20)/总数
| -- ④ 保养完成率 (gauge) 81.5% = 已提交(4)/总数
| -- ⑤ 点检计划类型分布 (pie) 点检计划 102 / 保养计划 284
| -- 说明：月度趋势用单图双序列（点检/保养）呈现，等价于"折线×2"。
| -- 口径：状态枚举 MesDvCheckRecordStatusEnum(10 草稿/20 已完成)、MesDvMaintenRecordStatusEnum(0 草稿/4 已提交)、
| -- MesDvCheckPlanTypeEnum(1 点检/2 保养)。后端 DashboardServiceImpl 无缓存，status=1 且按 sort 排序，插入即生效。
| -- 幂等：先按 id 删本脚本 5 行再插入。回滚：见文件末尾注释。
|
| START TRANSACTION;
|
| DELETE FROM bi_chart_config WHERE dashboard_id = 3 AND id IN (53, 54, 55, 56, 57);
|
| INSERT INTO bi_chart_config
| (id, dashboard_id, name, chart_type, data_source_id, query_sql, position, chart_options, refresh_interval, sort, status,
| creator, create_time, updater, update_time, deleted)
| VALUES
| (53, 3, '设备按车间分布', 'pie', NULL,
| 'SELECT w.name AS name, COUNT(*) AS value FROM mes_dv_machinery m JOIN mes_md_workshop w ON w.id = m.workshop_id AND w.deleted = 0 WHERE m.deleted = 0 GROUP BY w.id, w.name ORDER BY w.id',
| '{"h": 8, "w": 6, "x": 0, "y": 12}', NULL, 0, 9, 1, '1', NOW(), '1', NOW(), b'0'),
|
| (54, 3, '点检·保养月度趋势', 'line', NULL,
| 'SELECT t.m AS name, SUM(t.check_cnt) AS \'点检\', SUM(t.mainten_cnt) AS \'保养\' FROM (SELECT DATE_FORMAT(check_time,\'%Y-%m\') AS m, 1 AS check_cnt, 0 AS mainten_cnt FROM mes_dv_check_record WHERE deleted = 0 UNION ALL SELECT DATE_FORMAT(mainten_time,\'%Y-%m\') AS m, 0 AS check_cnt, 1 AS mainten_cnt FROM mes_dv_mainten_record WHERE deleted = 0) t GROUP BY t.m ORDER BY t.m',
| '{"h": 8, "w": 6, "x": 6, "y": 12}', NULL, 0, 10, 1, '1', NOW(), '1', NOW(), b'0'),
|
| (55, 3, '点检完成率', 'gauge', NULL,
| 'SELECT ROUND(SUM(status = 20) * 100.0 / NULLIF(COUNT(*), 0), 1) AS value FROM mes_dv_check_record WHERE deleted = 0',
| '{"h": 4, "w": 3, "x": 0, "y": 20}', NULL, 0, 11, 1, '1', NOW(), '1', NOW(), b'0'),
|
| (56, 3, '保养完成率', 'gauge', NULL,
| 'SELECT ROUND(SUM(status = 4) * 100.0 / NULLIF(COUNT(*), 0), 1) AS value FROM mes_dv_mainten_record WHERE deleted = 0',
| '{"h": 4, "w": 3, "x": 3, "y": 20}', NULL, 0, 12, 1, '1', NOW(), '1', NOW(), b'0'),
|
| (57, 3, '点检计划类型分布', 'pie', NULL,
| 'SELECT CASE type WHEN 1 THEN \'点检计划\' WHEN 2 THEN \'保养计划\' ELSE \'其他\' END AS name, COUNT(*) AS value FROM mes_dv_check_plan WHERE deleted = 0 GROUP BY type ORDER BY type',
| '{"h": 8, "w": 6, "x": 0, "y": 24}', NULL, 0, 13, 1, '1', NOW(), '1', NOW(), b'0');
|
| COMMIT;
|
| -- 回滚：
| -- DELETE FROM bi_chart_config WHERE dashboard_id = 3 AND id IN (53, 54, 55, 56, 57);
|
|
