ChickenFarm/SERVER/ChickenFarmV3/.script/sql/new/init_y.sql

/* 设备管理表 */
DROP TABLE IF EXISTS `d_device`;
CREATE TABLE `d_device`(
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '设备ID',
    `name` varchar(255) DEFAULT NULL COMMENT '设备名称',
    `type` varchar(255) DEFAULT NULL COMMENT '设备类型',
    `status`  tinyint NOT NULL DEFAULT '0' COMMENT '状态（ 0：正常工作中 1：维修中 2：带病运行 3：退役）',
    `location` varchar(255) DEFAULT NULL COMMENT '位置',
    `manufacturer` varchar(255) DEFAULT NULL COMMENT '厂家',
    `installation_date` date DEFAULT NULL COMMENT '安装日期',
    `responsible_person` varchar(255) DEFAULT NULL COMMENT '负责人',
    `responsible_phone` varchar(255) DEFAULT NULL COMMENT '负责人电话',
    `manual` varchar(255) DEFAULT NULL COMMENT '说明书',
    `create_org` bigint NOT NULL COMMENT '创建组织',
    `create_by` bigint NOT NULL COMMENT '创建人',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` bigint NOT NULL COMMENT '更新人',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `remark` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '备注',
    `del_flag` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
    PRIMARY KEY (`id`),
    INDEX `idx_device_name` (`name`),
    INDEX `idx_device_status` (`status`),
    INDEX `idx_device_location` (`location`),
    INDEX `idx_device_manufacturer` (`manufacturer`),
    INDEX `idx_device_create_by` (`create_by`),
    INDEX `idx_device_update_by` (`update_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备资产台账表';

-- ----------------------------
-- 故障报修和保养管理工单表
-- ----------------------------
DROP TABLE IF EXISTS `d_device_order`;
CREATE TABLE `d_device_order` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '工单ID',
    `task_id` bigint NULL DEFAULT NULL COMMENT '任务ID',
    `order_no` varchar(32) NOT NULL COMMENT '工单编号',
    `title` varchar(255) NOT NULL COMMENT '工单标题',
    `device_id` int(11) NOT NULL COMMENT '设备ID',
    `order_type` tinyint NOT NULL DEFAULT '1' COMMENT '工单类型（1:故障报修 2:保养管理 3:清洁消毒）',
    `clean_type` tinyint NULL DEFAULT NULL COMMENT '清洁消毒类型（1:清洁 2:消毒）',
    `content` text COMMENT '工单内容',
    `status` tinyint NOT NULL DEFAULT '0' COMMENT '工单状态（0:待处理 1:处理中 2:已暂缓 3:待验收 4:验收未通过 5:已完成）',
    `reporter_id` bigint NOT NULL COMMENT '报修人ID',
    `report_time` datetime NOT NULL COMMENT '报修时间',
    `receiver_id` bigint NULL DEFAULT NULL COMMENT '接收人ID',
    `receive_time` datetime NULL DEFAULT NULL COMMENT '接收时间',
    `petty_workers` varchar(500) NULL DEFAULT NULL COMMENT '小工',
    `complete_time` datetime NULL DEFAULT NULL COMMENT '完成时间',
    `suspend_reason` varchar(500) NULL DEFAULT NULL COMMENT '暂缓原因',
    `suspend_time` datetime NULL DEFAULT NULL COMMENT '暂缓时间',
    `resume_time` datetime NULL DEFAULT NULL COMMENT '恢复时间',
    `acceptance_time` datetime NULL DEFAULT NULL COMMENT '验收时间',
    `acceptance_result` tinyint NULL DEFAULT NULL COMMENT '验收结果（1:通过 2:拒绝）',
    `acceptance_remark` varchar(500) NULL DEFAULT NULL COMMENT '验收备注',
    `acceptor_id` bigint NULL DEFAULT NULL COMMENT '验收人ID',
    `fault_duration` float NULL DEFAULT 0 COMMENT '故障耗时（单位：小时）',
    `suspend_duration` float NULL DEFAULT 0 COMMENT '暂缓时长（单位：小时）',
    `attachment_urls` text COMMENT '附件URL列表（JSON格式）',
    `create_org` bigint NOT NULL COMMENT '创建组织',
    `create_by` bigint NOT NULL COMMENT '创建人',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` bigint NOT NULL COMMENT '更新人',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `remark` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '备注',
    `del_flag` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uk_order_no` (`order_no`),
    INDEX `idx_device_id` (`device_id`),
    INDEX `idx_order_type` (`order_type`),
    INDEX `idx_status` (`status`),
    INDEX `idx_reporter_id` (`reporter_id`),
    INDEX `idx_receiver_id` (`receiver_id`),
    INDEX `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单表';

-- ----------------------------
-- 工单协助人关联表
-- ----------------------------
DROP TABLE IF EXISTS `d_device_order_assistant`;
CREATE TABLE `d_device_order_assistant` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `order_id` bigint NOT NULL COMMENT '工单ID',
    `user_id` bigint NOT NULL COMMENT '用户ID',
    `work_hours` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '工时',
    `work_content` varchar(500) NULL DEFAULT NULL COMMENT '工作内容',
    `create_org` bigint NOT NULL COMMENT '创建组织',
    `create_by` bigint NOT NULL COMMENT '创建人',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` bigint NOT NULL COMMENT '更新人',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `remark` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '备注',
    `del_flag` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
    PRIMARY KEY (`id`),
    INDEX `idx_order_id` (`order_id`),
    INDEX `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单协助人关联表';

-- ----------------------------
-- 工单流转记录表
-- ----------------------------
DROP TABLE IF EXISTS `d_device_order_flow`;
CREATE TABLE `d_device_order_flow` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '流转记录ID',
    `order_id` bigint NOT NULL COMMENT '工单ID',
    `flow_type` tinyint NOT NULL COMMENT '流转类型（1:创建 2:接收 3:提交验收 4:验收通过 5:验收拒绝 6:关闭）',
    `operator_id` bigint NOT NULL COMMENT '操作人ID',
    `operate_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    `content` text COMMENT '操作内容/说明',
    `attachment_urls` text COMMENT '附件URL列表',
    `create_org` bigint NOT NULL COMMENT '创建组织',
    `create_by` bigint NOT NULL COMMENT '创建人',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` bigint NOT NULL COMMENT '更新人',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `remark` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '备注',
    `del_flag` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
    PRIMARY KEY (`id`),
    INDEX `idx_order_id` (`order_id`),
    INDEX `idx_flow_type` (`flow_type`),
    INDEX `idx_operator_id` (`operator_id`),
    INDEX `idx_operate_time` (`operate_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单流转记录表';

-- ----------------------------
-- Table structure for d_device_task
-- ----------------------------
DROP TABLE IF EXISTS `d_device_task`;
CREATE TABLE `d_device_task`  (
      `id` bigint NOT NULL AUTO_INCREMENT COMMENT '任务ID',
      `task_name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '保养任务名称',
      `task_desc` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '保养任务描述',
      `task_type` tinyint NOT NULL COMMENT '任务类型（1:设备保养 2:设备清洁）',
      `device_id` bigint NOT NULL COMMENT '设备ID',
      `cycle` float NOT NULL default 0 COMMENT '保养周期(小时)',
      `executor_id` bigint NOT NULL COMMENT '执行责任人ID',
      `acceptor_id` bigint NOT NULL COMMENT '验收责任人ID',
      `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态（0:启用 1:停用）',
      `startTime` datetime NULL DEFAULT NULL COMMENT '开始时间',
      `endTime` datetime NULL DEFAULT NULL COMMENT '结束时间',
      `lastCreateTime` datetime NULL DEFAULT NULL COMMENT '最后创建工单时间',
      `create_org` bigint NULL DEFAULT NULL COMMENT '创建组织',
      `create_by` bigint NULL DEFAULT NULL COMMENT '创建者',
      `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_by` bigint NULL DEFAULT NULL COMMENT '更新者',
      `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      `remark` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL COMMENT '备注',
      `del_flag` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT '0' COMMENT '删除标志（0代表存在 1代表删除）',
      PRIMARY KEY (`id`) USING BTREE,
      INDEX `idx_device_id`(`device_id` ASC) USING BTREE,
      INDEX `idx_task_name`(`task_name` ASC) USING BTREE,
      INDEX `idx_task_type`(`task_type` ASC) USING BTREE,
      INDEX `idx_executor_id`(`executor_id` ASC) USING BTREE,
      INDEX `idx_acceptor_id`(`acceptor_id` ASC) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT = '设备任务表';

-- ----------------------------
-- Table structure for d_inspection_rule
-- ----------------------------
DROP TABLE IF EXISTS `d_inspection_rule`;
CREATE TABLE `d_inspection_rule` (
     `id` bigint NOT NULL AUTO_INCREMENT COMMENT '点检规则ID',
     `task_name` varchar(255) NOT NULL COMMENT '点检任务名称',
     `location` varchar(255) DEFAULT NULL COMMENT '地点',
     `cycle_hours` float NOT NULL COMMENT '点检周期（小时）',
     `tolerance_hours` float DEFAULT NULL COMMENT '允许误差范围（小时）',
     `start_time` datetime DEFAULT NULL COMMENT '点检开始时间',
     `end_time` datetime DEFAULT NULL COMMENT '点检结束时间',
     `required_count` int NOT NULL COMMENT '需点检总次数',
     `actual_count` int NOT NULL DEFAULT '0'COMMENT '实际点检次数',
     `missed_count` int NOT NULL DEFAULT '0' COMMENT '漏检次数',
     `executor_id` bigint DEFAULT NULL COMMENT '执行人ID',
     `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态（0:正常 1:停用）',
     `create_org` bigint NOT NULL COMMENT '创建组织',
     `create_by` bigint NOT NULL COMMENT '创建人',
     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `update_by` bigint NOT NULL COMMENT '更新人',
     `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
     `remark` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '备注',
     `del_flag` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
     PRIMARY KEY (`id`),
     INDEX `idx_task_name` (`task_name`),
     INDEX `idx_executor_id` (`executor_id`),
     INDEX `idx_status` (`status`),
     INDEX `idx_create_by` (`create_by`),
     INDEX `idx_update_by` (`update_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='点检规则管理表';

-- ----------------------------
-- Table structure for d_inspection_checkin
-- ----------------------------
DROP TABLE IF EXISTS `d_inspection_checkin`;
CREATE TABLE `d_inspection_checkin` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '点检签到ID',
    `inspection_rule_id` bigint NOT NULL COMMENT '点检任务ID',
    `inspector_id` bigint NOT NULL COMMENT '点检人ID',
    `planned_sequence` int NOT NULL COMMENT '点检计划次序',
    `execute_time` datetime  NULL COMMENT '执行时间',
    `plan_time` datetime  NULL COMMENT '计划时间',
    `image_url` varchar(255) DEFAULT NULL COMMENT '签到图片',
    `checkin_status` tinyint NOT NULL DEFAULT '0' COMMENT '状态（0:正常 1:异常）',
    `create_org` bigint NOT NULL COMMENT '创建组织',
    `create_by` bigint NOT NULL COMMENT '创建人',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` bigint NOT NULL COMMENT '更新人',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `remark` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL COMMENT '备注',
    `del_flag` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
    PRIMARY KEY (`id`),
    INDEX `idx_inspection_rule_id` (`inspection_rule_id`),
    INDEX `idx_inspector_id` (`inspector_id`),
    INDEX `idx_execute_time` (`execute_time`),
    INDEX `idx_status` (`checkin_status`),
    INDEX `idx_create_by` (`create_by`),
    INDEX `idx_update_by` (`update_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='点检签到表';

-- ----------------------------
-- Table structure for d_statistics_device_failure
-- ----------------------------
DROP TABLE IF EXISTS `d_statistics_device_failure`;
CREATE TABLE `d_statistics_device_failure`(
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `device_id` bigint NOT NULL COMMENT '设备ID',
    `stat_cycle` varchar(50) NOT NULL COMMENT '统计周期（例如：2025-W42表示2025年第42周）',
    `failure_duration` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '故障时长（小时）',
    `good_duration` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '完好时长（小时）',
    `sick_duration` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '带病运行时长（小时）',
    `failure_rate` decimal(5,4) NOT NULL DEFAULT '0.0000' COMMENT '故障率（故障时长/完好时长）',
    `sick_rate` decimal(5,4) NOT NULL DEFAULT '0.0000' COMMENT '带病运行率（带病时长/完好时长）',
    `stat_start_time` datetime NOT NULL COMMENT '统计开始时间',
    `stat_end_time` datetime NOT NULL COMMENT '统计结束时间',
    `create_org` bigint NOT NULL COMMENT '创建组织',
    `create_by` bigint NOT NULL COMMENT '创建人',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` bigint NOT NULL COMMENT '更新人',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '备注',
    `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
    PRIMARY KEY (`id`),
    INDEX `idx_device_id` (`device_id`),
    INDEX `idx_stat_cycle` (`stat_cycle`),
    INDEX `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备故障率统计表';

-- ----------------------------
-- Table structure for d_statistics_mechanic_repair_details
-- ----------------------------
DROP TABLE IF EXISTS `d_statistics_mechanic_repair_details`;
CREATE TABLE `d_statistics_mechanic_repair_details`(
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `stat_cycle` varchar(50) NOT NULL COMMENT '统计周期（例如：2025-W42表示2025年第42周）',
    `mechanic_id` bigint NOT NULL COMMENT '机修工ID',
    `devices` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '设备列表',
    `repair_count` int NOT NULL DEFAULT '0' COMMENT '维修次数',
    `stat_start_time` datetime NOT NULL COMMENT '统计开始时间',
    `stat_end_time` datetime NOT NULL COMMENT '统计结束时间',
    `create_org` bigint NOT NULL COMMENT '创建组织',
    `create_by` bigint NOT NULL COMMENT '创建人',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` bigint NOT NULL COMMENT '更新人',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '备注',
    `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
    PRIMARY KEY (`id`),
    INDEX `idx_mechanic_id` (`mechanic_id`),
    INDEX `idx_stat_cycle` (`stat_cycle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='机修工维修明细统计表';

-- ----------------------------
-- Table structure for d_statistics_inspection_missed
-- ----------------------------
DROP TABLE IF EXISTS `d_statistics_inspection_missed`;
CREATE TABLE `d_statistics_inspection_missed`(
     `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
     `rule_id` bigint NOT NULL COMMENT '点检规则ID',
     `rule_name` varchar(255) NOT NULL COMMENT '点检规则名称',
     `inspector_id` bigint NOT NULL COMMENT '点检人ID',
     `stat_cycle` varchar(50) NOT NULL COMMENT '统计周期（例如：2025-W42表示2025年第42周）',
     `missed_count` int NOT NULL DEFAULT '0' COMMENT '漏检次数',
     `missed_duration` decimal(10,2) NULL DEFAULT NULL COMMENT '漏检时长（小时）',
     `stat_start_time` datetime NOT NULL COMMENT '统计开始时间',
     `stat_end_time` datetime NOT NULL COMMENT '统计结束时间',
     `create_org` bigint NOT NULL COMMENT '创建组织',
     `create_by` bigint NOT NULL COMMENT '创建人',
     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `update_by` bigint NOT NULL COMMENT '更新人',
     `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
     `remark` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '备注',
     `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
     PRIMARY KEY (`id`),
     INDEX `idx_rule_id` (`rule_id`),
     INDEX `idx_inspector_id` (`inspector_id`),
     INDEX `idx_stat_cycle` (`stat_cycle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='点检漏检情况统计表';


INSERT INTO `sys_config` VALUES (11, '000000', '维修人员USER_ID', 'sys.repair.userIds', '5,6', 'Y', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '维修人员ID');

INSERT INTO `sys_dict_type` VALUES (13, '000000', '消息阅读状态', 'sys_notice_read_status', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '0：未读 1：已读');
INSERT INTO `sys_dict_data` VALUES (41, '000000', 0, '未读', '0', 'sys_notice_read_status', '', 'danger', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '未读');
INSERT INTO `sys_dict_data` VALUES (42, '000000', 0, '已读', '1', 'sys_notice_read_status', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '已读');

INSERT INTO `sys_dict_type` VALUES (301, '000000', '设备状态', 'device_status', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '0：正常运行 1：维修中 2：带病运行 3：退役');
INSERT INTO `sys_dict_data` VALUES (301, '000000', 0, '正常运行', '0', 'device_status', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '维修中');
INSERT INTO `sys_dict_data` VALUES (302, '000000', 0, '维修中', '1', 'device_status', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '维修中');
INSERT INTO `sys_dict_data` VALUES (303, '000000', 0, '带病运行', '2', 'device_status', '', 'danger', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '带病运行');
INSERT INTO `sys_dict_data` VALUES (304, '000000', 0, '退役', '3', 'device_status', '', 'info', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '退役');

INSERT INTO `sys_dict_type` VALUES (302, '000000', '设备工单类型', 'device_order_type', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '1:故障报修 2:设备保养 3:清洁 4:消毒');
INSERT INTO `sys_dict_data` VALUES (307, '000000', 0, '故障报修', '1', 'device_order_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '故障报修');
INSERT INTO `sys_dict_data` VALUES (308, '000000', 0, '设备保养', '2', 'device_order_type', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '设备保养');
INSERT INTO `sys_dict_data` VALUES (309, '000000', 0, '清洁', '3', 'device_order_type', '', 'success', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '清洁');
INSERT INTO `sys_dict_data` VALUES (310, '000000', 0, '消毒', '4', 'device_order_type', '', 'success', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '消毒');

INSERT INTO `sys_dict_type` VALUES (303, '000000', '设备工单状态', 'device_order_status', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '0:待处理 1:处理中 2:已暂缓 3:待验收 4:验收未通过 5:已完成');
INSERT INTO `sys_dict_data` VALUES (311, '000000', 0, '待处理', '0', 'device_order_status', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '待处理');
INSERT INTO `sys_dict_data` VALUES (312, '000000', 0, '处理中', '1', 'device_order_status', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '处理中');
INSERT INTO `sys_dict_data` VALUES (313, '000000', 0, '已暂缓', '2', 'device_order_status', '', 'danger', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '已暂缓');
INSERT INTO `sys_dict_data` VALUES (314, '000000', 0, '待验收', '3', 'device_order_status', '', 'success', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '待验收');
INSERT INTO `sys_dict_data` VALUES (315, '000000', 0, '验收未通过', '4', 'device_order_status', '', 'danger', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '验收未通过');
INSERT INTO `sys_dict_data` VALUES (316, '000000', 0, '已完成', '5', 'device_order_status', '', 'default', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '已完成');

INSERT INTO `sys_dict_type` VALUES (304, '000000', '工单流转类型', 'device_order_flow_type', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '1:创建工单 2:修改工单 3:变更协助人 4:接收工单 5:暂缓工单 6:恢复工单 7:提交验收 8:验收通过 9:验收拒绝');
INSERT INTO `sys_dict_data` VALUES (319, '000000', 0, '创建工单', '1', 'device_order_flow_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '创建工单');
INSERT INTO `sys_dict_data` VALUES (320, '000000', 0, '修改工单', '2', 'device_order_flow_type', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '修改工单');
INSERT INTO `sys_dict_data` VALUES (321, '000000', 0, '变更协助人', '3', 'device_order_flow_type', '', 'success', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '变更协助人');
INSERT INTO `sys_dict_data` VALUES (322, '000000', 0, '接收工单', '4', 'device_order_flow_type', '', 'success', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '接收工单');
INSERT INTO `sys_dict_data` VALUES (323, '000000', 0, '暂缓工单', '5', 'device_order_flow_type', '', 'danger', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '暂缓工单');
INSERT INTO `sys_dict_data` VALUES (324, '000000', 0, '恢复工单', '6', 'device_order_flow_type', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '恢复工单');
INSERT INTO `sys_dict_data` VALUES (325, '000000', 0, '提交验收', '7', 'device_order_flow_type', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '提交验收');
INSERT INTO `sys_dict_data` VALUES (326, '000000', 0, '验收通过', '8', 'device_order_flow_type', '', 'success', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '验收通过');
INSERT INTO `sys_dict_data` VALUES (327, '000000', 0, '验收拒绝', '9', 'device_order_flow_type', '', 'danger', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '验收拒绝');

INSERT INTO `sys_dict_type` VALUES (305, '000000', '任务类型', 'device_task_type', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '1:设备保养 2:设备清洁');
INSERT INTO `sys_dict_data` VALUES (331, '000000', 0, '设备保养', '1', 'device_task_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '设备保养');
INSERT INTO `sys_dict_data` VALUES (332, '000000', 0, '设备清洁', '2', 'device_task_type', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '设备清洁');
INSERT INTO `sys_dict_data` VALUES (333, '000000', 0, '设备消毒', '3', 'device_task_type', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '设备消毒');

INSERT INTO `sys_dict_type` VALUES (306, '000000', '清洁类型', 'device_clean_type', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '1:清洁 2:消毒');
INSERT INTO `sys_dict_data` VALUES (335, '000000', 0, '清洁', '1', 'device_clean_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '清洁');
INSERT INTO `sys_dict_data` VALUES (336, '000000', 0, '消毒', '2', 'device_clean_type', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '消毒');

INSERT INTO `sys_dict_type` VALUES (307, '000000', '点检签到类型', 'device_inspection_type', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '0:未签到 1:签到');
INSERT INTO `sys_dict_data` VALUES (341, '000000', 0, '未签到', '0', 'device_inspection_type', '', 'danger', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '未签到');
INSERT INTO `sys_dict_data` VALUES (342, '000000', 0, '签到', '1', 'device_inspection_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '签到');

INSERT INTO `sys_menu` VALUES (20, '工单管理', 0, 21, 'workOrder', NULL, '', 1, 0, 'M', '0', '0', 'workOrder', 'suitcase-lg', '', '', 100, 1, '2025-10-15 12:00:00', 1, '2025-10-15 12:00:00', '设备管理');
INSERT INTO `sys_menu` VALUES (341, '待接受工单', 20, 1, 'needReceive', '/workOrder/needReceive', '', 1, 0, 'C', '0', '0', 'workOrder:needReceive', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (342, '维修工单', 20, 2, 'myReceiveR', '/workOrder/myReceiveR', '', 1, 0, 'C', '0', '0', 'workOrder:myReceiveR', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (343, '保养工单', 20, 3, 'myReceiveM', '/workOrder/myReceiveM', '', 1, 0, 'C', '0', '0', 'workOrder:myReceiveM', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (344, '清洁工单', 20, 4, 'myReceiveC', '/workOrder/myReceiveC', '', 1, 0, 'C', '0', '0', 'workOrder:myReceiveC', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (345, '消毒工单', 20, 5, 'myReceiveD', '/workOrder/myReceiveD', '', 1, 0, 'C', '0', '0', 'workOrder:myReceiveD', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');

INSERT INTO `sys_menu` VALUES (21, '设备管理', 0, 21, 'deviceMg', NULL, '', 1, 0, 'M', '0', '0', '', 'suitcase-lg', '', '', 100, 1, '2025-10-15 12:00:00', 1, '2025-10-15 12:00:00', '设备管理');

INSERT INTO `sys_menu` VALUES (351, '设备资产', 21, 1, 'device', 'device/device/index', '', 1, 0, 'C', '0', '0', 'device:device', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2001, '查询设备资产', 351, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:device:query', 'eye', '', '', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2002, '新增设备资产', 351, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:device:add', 'plus-square', 'btn btn-light-primary', 'handleCreate', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2003, '修改设备资产', 351, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:device:edit', 'pencil-square', 'btn btn-light-success', 'handleUpdate@1', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2004, '删除设备资产', 351, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:device:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (352, '点检签到规则', 21, 2, 'inspection', 'device/inspection/index', '', 1, 0, 'C', '0', '0', 'device:inspection', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2011, '查询点检规则', 352, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:inspection:query', 'eye', '', '', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2012, '新增点检规则', 352, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:inspection:add', 'plus-square', 'btn btn-light-primary', 'handleCreate', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2013, '修改点检规则', 352, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:inspection:edit', 'pencil-square', 'btn btn-light-success', 'handleUpdate@1', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2014, '删除点检规则', 352, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:inspection:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2015, '启停点检签到', 352, 0, '#', NULL, '', 1, 0, 'F', '1', '0', 'device:inspection:startStop', '', '', '', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (353, '设备维修工单', 21, 3, 'deviceRepairOrder', 'device/deviceRepairOrder/index', '', 1, 0, 'C', '0', '0', 'device:deviceRepairOrder', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2021, '查询维修工单', 353, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceRepairOrder:query', 'eye', '', '', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2022, '新增维修工单', 353, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceRepairOrder:add', 'plus-square', 'btn btn-light-primary', 'handleCreate', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2023, '修改维修工单', 353, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceRepairOrder:edit', 'pencil-square', 'btn btn-light-success', 'handleUpdate@1', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2024, '删除维修工单', 353, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceRepairOrder:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (354, '设备保养任务', 21, 4, 'deviceMaintenanceTask', 'device/deviceMaintenanceTask/index', '', 1, 0, 'C', '0', '0', 'device:deviceMaintenanceTask', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2031, '查询保养任务', 354, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceMaintenanceTask:query', 'eye', '', '', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2032, '新增保养任务', 354, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceMaintenanceTask:add', 'plus-square', 'btn btn-light-primary', 'handleCreate', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2033, '修改保养任务', 354, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceMaintenanceTask:edit', 'pencil-square', 'btn btn-light-success', 'handleUpdate@1', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2034, '删除保养任务', 354, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceMaintenanceTask:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2035, '启停保养任务', 354, 0, '#', NULL, '', 1, 0, 'F', '1', '0', 'device:deviceMaintenanceTask:startStop', '', '', '', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (355, '设备保养工单', 21, 5, 'deviceMaintenanceOrder', 'device/deviceMaintenanceOrder/index', '', 1, 0, 'C', '0', '0', 'device:deviceMaintenanceOrder', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2041, '查询保养工单', 355, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceMaintenanceOrder:query', 'eye', '', '', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2042, '新增保养工单', 355, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceMaintenanceOrder:add', 'plus-square', 'btn btn-light-primary', 'handleCreate', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2043, '修改保养工单', 355, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceMaintenanceOrder:edit', 'pencil-square', 'btn btn-light-success', 'handleUpdate@1', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2044, '删除保养工单', 355, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceMaintenanceOrder:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (356, '设备报表', 21, 6, 'report', NULL, '', 1, 0, 'M', '0', '0', '', 'file-earmark-bar-graph', '', '', 100, 1, '2025-10-15 12:00:00', 1, '2025-10-15 12:00:00', '设备报表');
INSERT INTO `sys_menu` VALUES (2051, '设备故障统计表', 356, 1, 'deviceFault', 'device/report/fault', '', 1, 0, 'C', '0', '0', 'device:report:fault', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2052, '生成设备故障统计表', 2051, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:deviceMaintenanceOrder:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2053, '机修工维修明细表', 356, 2, 'deviceRepairDetail', 'device/report/repair', '', 1, 0, 'C', '0', '0', 'device:report:repair', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2054, '生成机修工维修明细表', 2053, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:report:repair:gen', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2055, '点检签到漏点统计表', 356, 3, 'inspectionLose', 'device/report/inspection', '', 1, 0, 'C', '0', '0', 'device:report:inspection', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2056, '生成点检签到漏点统计表', 2055, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'device:report:inspection:gen', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');

-- INSERT INTO `sys_role_menu` (`role_id`, `menu_id`) VALUES (2, );


-- ----------------------------
-- Table structure for i_sample
-- ----------------------------
DROP TABLE IF EXISTS `e_sample`;
CREATE TABLE `e_sample` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '样本ID',
    `sample_name` varchar(255) NOT NULL COMMENT '样本名称',
    `batch_num` varchar(32) DEFAULT NULL COMMENT '取样的批次',
    `chicken_id` bigint DEFAULT NULL COMMENT '取样的个体id',
    `sample_time` datetime DEFAULT NULL COMMENT '取样时间',
    `description` varchar(500) DEFAULT NULL COMMENT '样本描述',
    `sample_type` tinyint NULL COMMENT '样品类型（1：血液 2：粪便）',
    `sample_status` tinyint NULL DEFAULT '1' COMMENT '样品状态（1：已创建 2：已采样 3：实验中 4：已销毁）',
    `create_org` bigint NOT NULL COMMENT '创建组织',
    `create_by` bigint NOT NULL COMMENT '创建人',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` bigint NOT NULL COMMENT '更新人',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
    PRIMARY KEY (`id`),
    KEY `idx_sample_batch` (`batch_num`),
    KEY `idx_sample_chicken_id` (`chicken_id`),
    KEY `idx_sample_status` (`sample_status`),
    KEY `idx_sample_create_by` (`create_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='样本表';
-- ----------------------------
-- Table structure for i_sample_flow
-- ----------------------------
DROP TABLE IF EXISTS `e_sample_flow`;
CREATE TABLE `e_sample_flow` (
     `id` bigint NOT NULL AUTO_INCREMENT COMMENT '流转记录ID',
     `sample_id` bigint NOT NULL COMMENT '样品ID',
     `handler` bigint NOT NULL COMMENT '经手人',
     `handle_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '处理时间',
     `remark` varchar(500) DEFAULT NULL COMMENT '备注',
     PRIMARY KEY (`id`),
     KEY `idx_sample_flow_sample` (`sample_id`),
     KEY `idx_sample_flow_handler` (`handler`),
     KEY `idx_sample_flow_time` (`handle_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='样本流转表';

-- ----------------------------
-- Table structure for i_experiment
-- ----------------------------
DROP TABLE IF EXISTS `e_experiment`;
CREATE TABLE `e_experiment` (
    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '实验ID',
    `experiment_name` varchar(255) NOT NULL COMMENT '实验名称',
    `experiment_type` tinyint NOT NULL COMMENT '实验类型（1：平板凝集-沙门氏菌 2：ELISA-白血病P27抗原 3：ELISA-抗体 4：荧光定量PCR 5：HI-抗体）',
    `experiment_manager` bigint DEFAULT NULL COMMENT '实验负责人',
    `review_manager` bigint DEFAULT NULL COMMENT '审核负责人',
    `raw_data_url` varchar(500) DEFAULT NULL COMMENT '实验原始数据URL（文件格式）',
    `report_url` varchar(500) DEFAULT NULL COMMENT '实验报告URL（文件格式）',
    `experiment_status` tinyint DEFAULT '1' COMMENT '实验状态（1：未开始 2：进行中 3：审核中 4：发回报告 5：已结束）',
    `description` varchar(500) DEFAULT NULL COMMENT '实验状态（1：未开始 2：进行中 3：审核中 4：发回报告 5：已结束）',
    `create_org` bigint NOT NULL COMMENT '创建组织',
    `create_by` bigint NOT NULL COMMENT '创建人',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_by` bigint NOT NULL COMMENT '更新人',
    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0:未删除, 1:已删除）',
    PRIMARY KEY (`id`),
    KEY `idx_experiment_manager` (`experiment_manager`),
    KEY `idx_review_manager` (`review_manager`),
    KEY `idx_experiment_status` (`experiment_status`),
    KEY `idx_experiment_create_by` (`create_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='实验管理表';

-- ----------------------------
-- Table structure for e_experiment_sample
-- ----------------------------
DROP TABLE IF EXISTS `e_experiment_sample`;
CREATE TABLE `e_experiment_sample` (
       `id` bigint NOT NULL AUTO_INCREMENT COMMENT '关联ID',
       `sample_id` bigint NOT NULL COMMENT '样本ID',
       `experiment_id` bigint NOT NULL COMMENT '实验ID',
       `create_by` bigint NOT NULL COMMENT '创建人',
       `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
       PRIMARY KEY (`id`),
       UNIQUE KEY `uk_sample_experiment` (`sample_id`,`experiment_id`),
       KEY `idx_sample_experiment_sample` (`sample_id`),
       KEY `idx_sample_experiment_experiment` (`experiment_id`),
       KEY `idx_sample_experiment_create_by` (`create_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='样本-实验关联表';

INSERT INTO `sys_config` VALUES (12, '000000', '实验审核人员USER_ID', 'sys.experiment.review.userIds', '8,9', 'Y', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '实验审核人员ID');

INSERT INTO `sys_dict_type` VALUES (311, '000000', '实验样品类型', 'experiment_sample_type', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '1：血液 2：粪便');
INSERT INTO `sys_dict_data` VALUES (351, '000000', 0, '血液', '1', 'experiment_sample_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '血液');
INSERT INTO `sys_dict_data` VALUES (352, '000000', 0, '粪便', '2', 'experiment_sample_type', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '粪便');

INSERT INTO `sys_dict_type` VALUES (312, '000000', '实验样品状态', 'experiment_sample_status', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '1：新建 2：已采样 3：实验中 4：已销毁');
INSERT INTO `sys_dict_data` VALUES (355, '000000', 0, '已创建', '1', 'experiment_sample_status', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '新建');
INSERT INTO `sys_dict_data` VALUES (356, '000000', 0, '已采样', '2', 'experiment_sample_status', '', 'success', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '已采样');
INSERT INTO `sys_dict_data` VALUES (357, '000000', 0, '实验中', '3', 'experiment_sample_status', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '实验中');
INSERT INTO `sys_dict_data` VALUES (358, '000000', 0, '已销毁', '4', 'experiment_sample_status', '', 'danger', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '已销毁');

INSERT INTO `sys_dict_type` VALUES (313, '000000', '实验类型', 'experiment_type', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '1：平板凝集-沙门氏菌 2：ELISA-白血病P27抗原 3：ELISA-抗体 4：荧光定量PCR 5：HI-抗体');
INSERT INTO `sys_dict_data` VALUES (361, '000000', 0, '平板凝集-沙门氏菌', '1', 'experiment_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '平板凝集-沙门氏菌');
INSERT INTO `sys_dict_data` VALUES (362, '000000', 0, 'ELISA-白血病P27抗原', '2', 'experiment_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, 'ELISA-白血病P27抗原');
INSERT INTO `sys_dict_data` VALUES (363, '000000', 0, 'ELISA-抗体', '3', 'experiment_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, 'ELISA-抗体');
INSERT INTO `sys_dict_data` VALUES (364, '000000', 0, '荧光定量PCR', '4', 'experiment_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '荧光定量PCR');
INSERT INTO `sys_dict_data` VALUES (365, '000000', 0, 'HI-抗体', '5', 'experiment_type', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, 'HI-抗体');

INSERT INTO `sys_dict_type` VALUES (314, '000000', '实验状态', 'experiment_status', 100, 1, '2025-10-15 12:00:00',  NULL, NULL, '1：新建 2：进行中 3：审核中 4：发回报告 5：已结束');
INSERT INTO `sys_dict_data` VALUES (371, '000000', 0, '未开始', '1', 'experiment_status', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '新建');
INSERT INTO `sys_dict_data` VALUES (372, '000000', 0, '进行中', '2', 'experiment_status', '', 'success', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '进行中');
INSERT INTO `sys_dict_data` VALUES (373, '000000', 0, '审核中', '3', 'experiment_status', '', 'warning', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '审核中');
INSERT INTO `sys_dict_data` VALUES (374, '000000', 0, '发回报告', '4', 'experiment_status', '', 'danger', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '发回报告');
INSERT INTO `sys_dict_data` VALUES (375, '000000', 0, '已结束', '5', 'experiment_status', '', 'primary', 'N', 100, 1, '2025-10-15 12:00:00', NULL, NULL, '已结束');


INSERT INTO `sys_menu` VALUES (22, '检验（实验）管理', 0, 22, 'experimentMg', NULL, '', 1, 0, 'M', '0', '0', '', 'suitcase-lg', '', '', 100, 1, '2025-10-15 12:00:00', 1, '2025-10-15 12:00:00', '设备管理');

INSERT INTO `sys_menu` VALUES (361, '样品管理', 22, 1, 'sample', 'experiment/sample/index', '', 1, 0, 'C', '0', '0', 'experiment:sample', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2081, '查询样品', 361, 1, '#', NULL, '', 1, 0, 'F', '0', '0', 'experiment:sample:query', 'eye', '', '', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2082, '新增样品', 361, 2, '#', NULL, '', 1, 0, 'F', '0', '0', 'experiment:sample:add', 'plus-square', 'btn btn-light-primary', 'handleCreate', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2083, '修改样品', 361, 3, '#', NULL, '', 1, 0, 'F', '0', '0', 'experiment:sample:edit', 'pencil-square', 'btn btn-light-success', 'handleUpdate@1', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2084, '删除样品', 361, 4, '#', NULL, '', 1, 0, 'F', '0', '0', 'experiment:sample:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2085, '销毁样品', 361, 5, '#', NULL, '', 1, 0, 'F', '1', '0', 'experiment:sample:destroy', 'dash-square', 'btn btn-light-danger', 'handleDestroy@1', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');

INSERT INTO `sys_menu` VALUES (362, '实验管理', 22, 2, 'experiment', 'experiment/experiment/index', '', 1, 0, 'C', '0', '0', 'experiment:experiment', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2091, '查询实验', 362, 1, '#', NULL, '', 1, 0, 'F', '0', '0', 'experiment:experiment:query', 'eye', '', '', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2092, '新增实验', 362, 2, '#', NULL, '', 1, 0, 'F', '0', '0', 'experiment:experiment:add', 'plus-square', 'btn btn-light-primary', 'handleCreate', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2093, '修改实验', 362, 3, '#', NULL, '', 1, 0, 'F', '0', '0', 'experiment:experiment:edit', 'pencil-square', 'btn btn-light-success', 'handleUpdate@1', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (2094, '删除实验', 362, 4, '#', NULL, '', 1, 0, 'F', '0', '0', 'experiment:experiment:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');
INSERT INTO `sys_menu` VALUES (363, '实验审核', 22, 3, 'experimentAudit', 'experiment/experiment/audit', '', 1, 0, 'C', '0', '0', 'experiment:experiment:audit', 'patch-check-fill', NULL, NULL, 100, 1, '2025-10-15 15:02:26', NULL, '2025-10-15 15:02:26', '');

INSERT INTO `sys_menu` VALUES (23, '生物安全管理', 0, 23, 'biosecurityMg', NULL, '', 1, 0, 'M', '0', '0', '', 'suitcase-lg', '', '', 100, 1, '2025-10-15 12:00:00', 1, '2025-10-15 12:00:00', '设备管理');
INSERT INTO `sys_menu` VALUES (367, '清洁消毒任务', 23, 1, 'biosecurityTask', 'biosecurity/task/index', '', 1, 0, 'C', '0', '0', 'biosecurity:task', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2131, '查询清消任务', 367, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:task:query', 'eye', '', '', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2132, '新增清消任务', 367, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:task:add', 'plus-square', 'btn btn-light-primary', 'handleCreate', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2133, '修改清消任务', 367, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:task:edit', 'pencil-square', 'btn btn-light-success', 'handleUpdate@1', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2134, '删除清消任务', 367, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:task:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2135, '启停清消任务', 367, 0, '#', NULL, '', 1, 0, 'F', '1', '0', 'biosecurity:task:startStop', '', '', '', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (368, '清洁工单', 23, 2, 'biosecurityCleanOrder', 'biosecurity/cleanOrder/index', '', 1, 0, 'C', '0', '0', 'biosecurity:cleanOrder', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2141, '查询清洁工单', 368, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:cleanOrder:query', 'eye', '', '', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2142, '新增清洁工单', 368, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:cleanOrder:add', 'plus-square', 'btn btn-light-primary', 'handleCreate', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2143, '修改清洁工单', 368, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:cleanOrder:edit', 'pencil-square', 'btn btn-light-success', 'handleUpdate@1', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2144, '删除清洁工单', 368, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:cleanOrder:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (369, '消毒工单', 23, 3, 'biosecurityDisinfectOrder', 'biosecurity/disinfectOrder/index', '', 1, 0, 'C', '0', '0', 'biosecurity:disinfectOrder', '#', NULL, NULL, 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2151, '查询消毒工单', 369, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:disinfectOrder:query', 'eye', '', '', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2152, '新增消毒工单', 369, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:disinfectOrder:add', 'plus-square', 'btn btn-light-primary', 'handleCreate', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2153, '修改消毒工单', 369, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:disinfectOrder:edit', 'pencil-square', 'btn btn-light-success', 'handleUpdate@1', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');
INSERT INTO `sys_menu` VALUES (2154, '删除消毒工单', 369, 0, '#', NULL, '', 1, 0, 'F', '0', '0', 'biosecurity:disinfectOrder:remove', 'dash-square', 'btn btn-light-danger', 'handleDelete@0', 100, 1, '2025-10-15 15:02:19', NULL, '2025-10-15 15:02:19', '');