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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
| -- =====================================================
| -- 报表图表管理模块数据库脚本
| -- 执行前请备份数据库
| -- 创建时间: 2026-06-04
| -- =====================================================
|
| -- =============================================
| -- 1. 语音播报队列表 (voice_queue) - 新增
| -- =============================================
| DROP TABLE IF EXISTS `voice_queue`;
| CREATE TABLE `voice_queue` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
| `event_type` varchar(50) NOT NULL COMMENT '事件类型(sample_receive/task_assign/report_submit/emergency)',
| `event_name` varchar(100) NOT NULL COMMENT '事件名称',
| `details` varchar(500) DEFAULT '' COMMENT '事件详情',
| `voice_text` varchar(500) NOT NULL COMMENT '语音播报文本',
| `priority` int DEFAULT 0 COMMENT '优先级(0普通/1重要/2紧急)',
| `status` tinyint DEFAULT 0 COMMENT '状态(0待播报/1已播报/2已取消)',
| `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
| `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
| PRIMARY KEY (`id`),
| KEY `idx_status_priority` (`status`, `priority`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='语音播报队列表';
|
| -- =============================================
| -- 2. 报表配置表
| -- =============================================
| DROP TABLE IF EXISTS `report_config`;
| CREATE TABLE `report_config` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
| `report_name` varchar(100) NOT NULL COMMENT '报表名称',
| `report_code` varchar(50) NOT NULL COMMENT '报表编码',
| `report_type` varchar(20) NOT NULL COMMENT '报表类型(sample_progress/test_item/sample_record/device_record/dashboard)',
| `query_config` text COMMENT '查询条件配置JSON',
| `column_config` text COMMENT '列配置JSON',
| `chart_config` text COMMENT '图表配置JSON',
| `status` tinyint DEFAULT 1 COMMENT '状态(1启用/0停用)',
| `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
| `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
| `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
| `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
| `remark` varchar(500) DEFAULT '' COMMENT '备注',
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_report_code` (`report_code`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报表配置表';
|
| -- =============================================
| -- 3. 图表配置表
| -- =============================================
| DROP TABLE IF EXISTS `chart_config`;
| CREATE TABLE `chart_config` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
| `chart_name` varchar(100) NOT NULL COMMENT '图表名称',
| `chart_code` varchar(50) NOT NULL COMMENT '图表编码',
| `chart_type` varchar(20) NOT NULL COMMENT '图表类型(bar/line/pie/radar/spc/normal)',
| `data_source` varchar(100) NOT NULL COMMENT '数据源SQL或接口标识',
| `x_axis_field` varchar(50) COMMENT 'X轴字段',
| `y_axis_field` varchar(100) COMMENT 'Y轴字段(JSON数组)',
| `query_params` text COMMENT '查询参数配置JSON',
| `chart_options` text COMMENT 'ECharts配置JSON',
| `status` tinyint DEFAULT 1 COMMENT '状态(1启用/0停用)',
| `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
| `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
| `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
| `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
| `remark` varchar(500) DEFAULT '' COMMENT '备注',
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_chart_code` (`chart_code`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='图表配置表';
|
| -- =============================================
| -- 4. 看板配置表
| -- =============================================
| DROP TABLE IF EXISTS `dashboard_config`;
| CREATE TABLE `dashboard_config` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
| `dashboard_name` varchar(100) NOT NULL COMMENT '看板名称',
| `dashboard_code` varchar(50) NOT NULL COMMENT '看板编码',
| `layout_config` text COMMENT '布局配置JSON',
| `components` text COMMENT '组件配置JSON',
| `voice_config` text COMMENT '语音播报配置JSON',
| `refresh_interval` int DEFAULT 30 COMMENT '刷新间隔(秒)',
| `status` tinyint DEFAULT 1 COMMENT '状态(1启用/0停用)',
| `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
| `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
| `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
| `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
| `remark` varchar(500) DEFAULT '' COMMENT '备注',
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_dashboard_code` (`dashboard_code`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='看板配置表';
|
| -- =============================================
| -- 5. 菜单权限配置
| -- =============================================
| -- 报表图表管理（一级目录）- 排在最后，order_num = 21
| INSERT INTO `sys_menu` (`menu_id`, `menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `route_name`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`, `is_rersonal_button`) VALUES
| (3000, '报表图表管理', 0, 21, 'reportChart', '', NULL, '', 1, 0, 'M', '0', '0', '', 'chart', 'admin', NOW(), '', NULL, '报表图表管理目录', 0),
|
| -- 数字化语音看板（二级目录）
| (3001, '数字化语音看板', 3000, 1, 'dashboard', '', NULL, '', 1, 0, 'M', '0', '0', '', 'monitor', 'admin', NOW(), '', NULL, '数字化语音看板目录', 0),
|
| -- 试验大厅
| (3002, '试验大厅', 3001, 1, 'testHall', 'report/dashboard/index', NULL, '', 1, 0, 'C', '0', '0', 'report:dashboard:list', 'monitor', 'admin', NOW(), '', NULL, '试验大厅菜单', 0),
|
| -- 报表管理（二级目录）
| (3010, '报表管理', 3000, 2, 'report', '', NULL, '', 1, 0, 'M', '0', '0', '', 'form', 'admin', NOW(), '', NULL, '报表管理目录', 0),
|
| -- 样品进度报表
| (3011, '样品进度报表', 3010, 1, 'sampleProgress', 'report/sampleProgress/index', NULL, '', 1, 0, 'C', '0', '0', 'report:sampleProgress:list', 'table', 'admin', NOW(), '', NULL, '样品进度报表菜单', 0),
|
| -- 检测项目数据
| (3012, '检测项目数据', 3010, 2, 'testItemData', 'report/testItemData/index', NULL, '', 1, 0, 'C', '0', '0', 'report:testItemData:list', 'table', 'admin', NOW(), '', NULL, '检测项目数据菜单', 0),
|
| -- 样品领样记录
| (3013, '样品领样记录', 3010, 3, 'sampleRecord', 'report/sampleRecord/index', NULL, '', 1, 0, 'C', '0', '0', 'report:sampleRecord:list', 'table', 'admin', NOW(), '', NULL, '样品领样记录菜单', 0),
|
| -- 设备使用记录
| (3014, '设备使用记录', 3010, 4, 'deviceRecord', 'report/deviceRecord/index', NULL, '', 1, 0, 'C', '0', '0', 'report:deviceRecord:list', 'table', 'admin', NOW(), '', NULL, '设备使用记录菜单', 0),
|
| -- 智能图表（二级目录）
| (3020, '智能图表', 3000, 3, 'chart', '', NULL, '', 1, 0, 'M', '0', '0', '', 'chart', 'admin', NOW(), '', NULL, '智能图表目录', 0),
|
| -- 工作统计
| (3021, '工作统计', 3020, 1, 'workStatistics', 'report/workStatistics/index', NULL, '', 1, 0, 'C', '0', '0', 'chart:workStatistics:list', 'peoples', 'admin', NOW(), '', NULL, '工作统计菜单', 0),
|
| -- 合格率统计
| (3022, '合格率统计', 3020, 2, 'passRate', 'report/passRate/index', NULL, '', 1, 0, 'C', '0', '0', 'chart:passRate:list', 'chart', 'admin', NOW(), '', NULL, '合格率统计菜单', 0),
|
| -- SPC控制图
| (3023, 'SPC控制图', 3020, 3, 'spcChart', 'report/spcChart/index', NULL, '', 1, 0, 'C', '0', '0', 'chart:spcChart:list', 'chart', 'admin', NOW(), '', NULL, 'SPC控制图菜单', 0),
|
| -- 正态分布图
| (3024, '正态分布图', 3020, 4, 'normalDistribution', 'report/normalDistribution/index', NULL, '', 1, 0, 'C', '0', '0', 'chart:normalDistribution:list', 'chart', 'admin', NOW(), '', NULL, '正态分布图菜单', 0);
|
| -- =============================================
| -- 6. 初始化看板配置数据
| -- =============================================
| INSERT INTO `dashboard_config` (`dashboard_name`, `dashboard_code`, `layout_config`, `components`, `voice_config`, `refresh_interval`, `status`, `create_by`) VALUES
| ('试验大厅看板', 'test_hall',
| '[{"i":"history","x":0,"y":0,"w":8,"h":4},{"i":"future","x":8,"y":0,"w":4,"h":4},{"i":"ranking","x":0,"y":4,"w":6,"h":3},{"i":"status","x":6,"y":4,"w":6,"h":3},{"i":"result","x":0,"y":7,"w":12,"h":3}]',
| '[{"id":"history","type":"calendar","title":"历史15天检测任务","api":"/report/dashboard/history15Days"},{"id":"future","type":"calendar","title":"未来15天检测任务","api":"/report/dashboard/future15Days"},{"id":"ranking","type":"ranking","title":"提交排行","api":"/report/dashboard/ranking"},{"id":"status","type":"stat","title":"待处理统计","api":"/report/dashboard/overview"},{"id":"result","type":"chart","title":"检验结果统计","api":"/report/dashboard/insResult"}]',
| '{"enabled":true,"events":["sample_receive","task_assign","report_submit","emergency"],"template":"紧急通知：{eventName}，{details}"}',
| 30, 1, 'admin');
|
| -- =============================================
| -- 7. 初始化报表配置数据
| -- =============================================
| INSERT INTO `report_config` (`report_name`, `report_code`, `report_type`, `query_config`, `status`, `create_by`) VALUES
| ('样品进度报表', 'sample_progress', 'sample_progress', '[{"field":"entrustCode","label":"委托编号","type":"input"},{"field":"sampleCode","label":"样品编号","type":"input"},{"field":"sampleName","label":"样品名称","type":"input"},{"field":"insState","label":"检测状态","type":"select","options":[{"value":0,"label":"待检"},{"value":1,"label":"检验中"},{"value":5,"label":"已完成"}]}]', 1, 'admin'),
| ('检测项目数据', 'test_item_data', 'test_item', '[{"field":"productionOrder","label":"生产订单","type":"input"},{"field":"batchNo","label":"批次号","type":"input"},{"field":"sampleCode","label":"样品编号","type":"input"},{"field":"itemName","label":"检测项目","type":"input"}]', 1, 'admin'),
| ('样品领样记录', 'sample_record', 'sample_record', '[{"field":"sampleCode","label":"样品编号","type":"input"},{"field":"operateUser","label":"操作人","type":"input"},{"field":"startTime","label":"开始时间","type":"date"},{"field":"endTime","label":"结束时间","type":"date"}]', 1, 'admin'),
| ('设备使用记录', 'device_record', 'device_record', '[{"field":"deviceCode","label":"设备编号","type":"input"},{"field":"deviceName","label":"设备名称","type":"input"},{"field":"useUser","label":"使用人","type":"input"}]', 1, 'admin');
|
| -- =============================================
| -- 8. 初始化图表配置数据
| -- =============================================
| INSERT INTO `chart_config` (`chart_name`, `chart_code`, `chart_type`, `data_source`, `x_axis_field`, `y_axis_field`, `status`, `create_by`) VALUES
| ('工作统计图', 'work_statistics', 'bar', '/chart/workStatistics/byUser', 'userName', '[{"field":"sampleCount","name":"样品数量"},{"field":"itemCount","name":"项目数量"}]', 1, 'admin'),
| ('及时率统计', 'timely_rate', 'line', '/chart/workStatistics/timelyRate', 'userName', '[{"field":"timelyRate","name":"及时率(%)"}]', 1, 'admin'),
| ('原材料合格率', 'raw_material_pass_rate', 'bar', '/chart/passRate/rawMaterial', 'sampleName', '[{"field":"passRate","name":"合格率(%)"}]', 1, 'admin'),
| ('帕累托图', 'pareto', 'bar', '/chart/passRate/pareto', 'itemName', '[{"field":"unqualifiedCount","name":"不合格数"},{"field":"cumulativePercent","name":"累计百分比"}]', 1, 'admin'),
| ('工序合格率', 'process_pass_rate', 'pie', '/chart/passRate/process', 'processName', '[{"field":"passRate","name":"合格率(%)"}]', 1, 'admin'),
| ('SPC控制图', 'spc_chart', 'line', '/chart/spc/analyze', 'subgroup', '[{"field":"xBar","name":"X-Bar"},{"field":"ucl","name":"UCL"},{"field":"lcl","name":"LCL"}]', 1, 'admin'),
| ('正态分布图', 'normal_distribution', 'line', '/chart/normalDistribution/analyze', 'value', '[{"field":"frequency","name":"频率"},{"field":"normalCurve","name":"正态曲线"}]', 1, 'admin');
|
| -- =============================================
| -- 9. 初始化语音播报测试数据
| -- =============================================
| INSERT INTO `voice_queue` (`event_type`, `event_name`, `details`, `voice_text`, `priority`, `status`) VALUES
| ('sample_receive', '样品接收', '样品A001已到达实验室', '样品A001已到达实验室，请及时领取', 0, 0),
| ('task_assign', '任务分配', '检测任务已分配给张三', '检测任务已分配给张三，请尽快开始检测', 1, 0),
| ('emergency', '紧急通知', '设备故障需要维修', '紧急通知：关键检测设备出现故障，请立即联系维修人员', 2, 0);
|
| -- =====================================================
| -- 执行完成
| -- =====================================================
|
|
