admin/iot_java
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
iot_java/sql/2026-04-08.sql
Gjm a65b23cdad feat(iot): 新增设备类型字段和分组管理功能 
- 在 iot_things_model 表添加 dev_type 设备类型字段
- 修改 model_name 字段字符集并更新注释
- 新增设备类型字典数据和翻译配置
- 创建 iot_group 设备分组表并初始化基础数据
- 在 iot_category 表添加 industry_code 设备编码字段
- 更新 ThingsModel 实体类添加 devType 属性
- 修改 Excel 导入功能支持 sheetName 参数并优化类型识别逻辑
- 更新数据库映射文件支持设备类型字段操作
- 优化缓存实现按设备类型区分标识符避免冲突
- 调整导入数据验证逻辑并完善错误处理机制

Signed-off-by: Gjm <你的邮箱>
2026-04-09 16:38:57 +08:00

92 lines
12 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
ALTER TABLE `iot_things_model`
ADD COLUMN `dev_type` int NULL COMMENT '设备类型' AFTER `is_app`;
ALTER TABLE `iot_things_model`
MODIFY COLUMN `model_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '物模型名称' AFTER `model_id`;
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (753, '灭菌柜', 'Sterilizer');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (752, '铝盖机', 'Aluminum Capping Machine');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (751, '冻干机', 'Lyophilizer');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (750, '灌装机', 'Filling Machine');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (749, '胶塞机', 'Stoppering Machine');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (748, '进料系统', 'Feeding System');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (747, '出料系统', 'Sterilizer & Discharging System');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (746, '轧盖机', 'Sealing Machine');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (745, 'AGV', 'Automated Guided Vehicle');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (744, '灯检机', 'Light Inspection Machine');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (743, '裹包机', 'Wrapping Machine');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (742, '烘箱机', 'Drying Oven');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (741, '空调', 'HVAC');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (740, '配液', 'Solution Preparation');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (739, '贴标机', 'Labeling Machine');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (738, '灌封机', 'Filling and Sealing Machine');
INSERT INTO `sys_dict_data_translate` (`id`, `zh_cn`, `en_us`) VALUES (737, '洗瓶机', 'Bottle Washing Machine');
INSERT INTO `sys_dict_type` (`dict_name`, `dict_type`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('模型设备类型', 'model_dev_type', '0', 'admin', '2026-04-08 17:09:50', '', NULL, '模型设备类型');
INSERT INTO `sys_dict_data` (`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (753, 0, '灭菌柜', '17', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:11:52', 'admin', '2026-04-09 08:47:13', NULL);
INSERT INTO `sys_dict_data` (`dict_code`, `dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (752, 0, '铝盖机', '16', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:11:52', 'admin', '2026-04-09 08:47:13', NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '15', '冻干机', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:15:31', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '14', '灌装机', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:15:16', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '13', '胶塞机', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:15:04', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '12', '进料系统', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:14:52', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '11', '出料系统', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:14:38', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '10', '轧盖机', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:14:16', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '9', 'AGV', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:14:05', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '8', '灯验机', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:13:45', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '7', '裹包机', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:13:32', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '6', '烘箱机', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:13:08', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '5', '空调', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:12:56', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '4', '配液', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:12:48', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '3', '贴标机', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:12:36', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '2', '灌封机', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:12:09', '', NULL, NULL);
INSERT INTO `sys_dict_data` (`dict_sort`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `status`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (0, '1', '洗瓶机', 'model_dev_type', NULL, 'default', 'N', '0', 'admin', '2026-04-08 17:11:52', 'admin', '2026-04-08 17:12:21', NULL);
ALTER TABLE `iot_category`
ADD COLUMN `industry_code` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '设备编码' AFTER `remark`;
DROP TABLE IF EXISTS `iot_group`;
CREATE TABLE `iot_group` (
`group_id` bigint NOT NULL AUTO_INCREMENT COMMENT '分组ID',
`group_name` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '分组名称',
`group_order` tinyint NOT NULL DEFAULT 0 COMMENT '分组排序',
`user_id` bigint NULL DEFAULT NULL COMMENT '用户ID',
`user_name` varchar(30) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT '' COMMENT '用户昵称',
`del_flag` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
`create_by` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT '' COMMENT '创建者',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT '' COMMENT '更新者',
`update_time` datetime NULL DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '备注',
`parent_id` bigint NULL DEFAULT NULL COMMENT '父级ID',
`industry_code` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '设备编码',
PRIMARY KEY (`group_id`) USING BTREE,
INDEX `iot_group_index_user_id`(`user_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 28 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci COMMENT = '设备分组' ROW_FORMAT = DYNAMIC;
INSERT INTO `iot_group` VALUES (1, '生产线', 1, 1, 'admin', '0', '', '2026-03-31 10:30:26', '', '2026-03-31 10:30:29', NULL, NULL, 'SCX');
INSERT INTO `iot_group` VALUES (9, '一般粉针生产线', 0, 1, 'admin', '0', '', '2026-03-31 10:31:39', '', '2026-03-31 11:52:37', NULL, 1, 'YBFZ');
INSERT INTO `iot_group` VALUES (10, '冻干粉针1号生产线', 1, 1, 'admin', '0', '', '2026-03-31 11:11:50', '', '2026-03-31 11:55:29', NULL, 1, 'DGFZ001');
INSERT INTO `iot_group` VALUES (11, '动力车间', 4, 1, 'admin', '0', '', '2026-03-31 11:18:34', '', '2026-03-31 11:55:45', NULL, 1, 'DLCJ');
INSERT INTO `iot_group` VALUES (12, '冻干粉针2号生产线', 2, 1, 'admin', '0', '', '2026-03-31 11:53:50', '', '2026-03-31 11:55:32', NULL, 1, 'DGFZ002');
INSERT INTO `iot_group` VALUES (13, '冻干粉针3号生产线', 3, 1, 'admin', '0', '', '2026-03-31 11:54:59', '', '2026-03-31 11:55:36', NULL, 1, 'DGFZ003');
INSERT INTO `iot_group` VALUES (14, '化验室', 5, 1, 'admin', '0', '', '2026-03-31 11:55:57', '', NULL, NULL, 1, 'HYS');
INSERT INTO `iot_group` VALUES (15, '原料车间1号生产线', 6, 1, 'admin', '0', '', '2026-03-31 11:56:17', '', NULL, NULL, 1, 'YLCJ001');
INSERT INTO `iot_group` VALUES (16, '原料车间2号生产线', 7, 1, 'admin', '0', '', '2026-03-31 11:56:34', '', '2026-03-31 11:56:44', '', 1, 'YLCJ002');
INSERT INTO `iot_group` VALUES (17, '原料车间3号生产线', 8, 1, 'admin', '0', '', '2026-03-31 11:57:01', '', NULL, NULL, 1, 'YLCJ003');
INSERT INTO `iot_group` VALUES (18, '口服固体制剂1号生产线', 9, 1, 'admin', '0', '', '2026-03-31 11:57:14', '', NULL, NULL, 1, 'KFGT001');
INSERT INTO `iot_group` VALUES (19, '口服固体制剂2号生产线', 10, 1, 'admin', '0', '', '2026-03-31 11:57:33', '', NULL, NULL, 1, 'KFGT002');
INSERT INTO `iot_group` VALUES (20, '头孢粉针生产线', 11, 1, 'admin', '0', '', '2026-03-31 11:57:44', '', NULL, NULL, 1, 'TBFZ');
INSERT INTO `iot_group` VALUES (21, '小容量注射剂1号生产线', 12, 1, 'admin', '0', '', '2026-03-31 11:57:57', '', NULL, NULL, 1, 'XRLZSJ001');
INSERT INTO `iot_group` VALUES (22, '小容量注射剂2号生产线', 13, 1, 'admin', '0', '', '2026-03-31 11:58:11', '', NULL, NULL, 1, 'XRLZSJ002');
INSERT INTO `iot_group` VALUES (23, '技术中心', 15, 1, 'admin', '0', '', '2026-03-31 13:20:38', '', NULL, NULL, 1, 'JSZX');
INSERT INTO `iot_group` VALUES (24, '储运部', 16, 1, 'admin', '0', '', '2026-03-31 13:21:13', '', NULL, NULL, 1, 'CYB');
INSERT INTO `iot_group` VALUES (25, '其他', 17, 1, 'admin', '0', '', '2026-03-31 13:21:48', '', NULL, NULL, 1, 'QT');
SET FOREIGN_KEY_CHECKS = 1;
Reference in New Issue View Git Blame Copy Permalink