-- 离职申请增强:交接员工/用户与用户数据转移、离职审批分类、到期自动离职 Job -- 可重复执行;不包含 tenant_id。 -- 1. 用户数据交接映射表(补齐员工ID维度) CREATE TABLE IF NOT EXISTS `hrm_user_handover` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '映射ID', `from_employee_id` bigint DEFAULT NULL COMMENT '离职员工ID', `from_user_id` bigint NOT NULL COMMENT '离职用户ID', `to_employee_id` bigint DEFAULT NULL COMMENT '交接目标员工ID', `to_user_id` bigint NOT NULL COMMENT '交接目标用户ID', `resignation_id` bigint NOT NULL COMMENT '关联的离职申请ID', `handover_status` tinyint NOT NULL DEFAULT 0 COMMENT '交接状态:0-进行中,20-已完成', `handover_time` datetime DEFAULT NULL COMMENT '交接完成时间', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uk_from_to_resignation` (`from_user_id`, `to_user_id`, `resignation_id`), KEY `idx_from_user_id` (`from_user_id`), KEY `idx_to_user_id` (`to_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HRM用户数据交接映射表'; -- 1.1 已存在的旧表补齐员工ID列 SET @target_db = DATABASE(); SET @ddl = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE hrm_user_handover ADD COLUMN from_employee_id BIGINT NULL COMMENT ''离职员工ID'' AFTER id', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @target_db AND table_name = 'hrm_user_handover' AND column_name = 'from_employee_id'); PREPARE hrm_stmt FROM @ddl; EXECUTE hrm_stmt; DEALLOCATE PREPARE hrm_stmt; SET @ddl = (SELECT IF(COUNT(*) = 0, 'ALTER TABLE hrm_user_handover ADD COLUMN to_employee_id BIGINT NULL COMMENT ''交接目标员工ID'' AFTER from_user_id', 'SELECT 1') FROM information_schema.columns WHERE table_schema = @target_db AND table_name = 'hrm_user_handover' AND column_name = 'to_employee_id'); PREPARE hrm_stmt FROM @ddl; EXECUTE hrm_stmt; DEALLOCATE PREPARE hrm_stmt; -- 2. 离职审批分类(前端提交的 processDefinitionKey 历史值为该分类编码) SET @category_id = (SELECT COALESCE(MAX(id), 0) + 1 FROM bpm_category); INSERT INTO `bpm_category` (`id`, `name`, `code`, `description`, `status`, `sort`, `creator`, `updater`) SELECT @category_id, '离职审批', 'hrm_resignation_approve', '离职申请审批流程', 0, 0, '1', '1' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'hrm_resignation_approve' AND deleted = b'0'); -- 3. 到期自动离职 Job(每天 00:10 执行) INSERT INTO `infra_job` (`name`, `status`, `handler_name`, `handler_param`, `cron_expression`, `retry_count`, `retry_interval`, `monitor_timeout`, `creator`, `updater`) SELECT '离职到期自动办理', 1, 'hrmResignationAutoLeaveJob', '', '0 10 0 * * ?', 0, 0, 0, '1', '1' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM infra_job WHERE handler_name = 'hrmResignationAutoLeaveJob' AND deleted = b'0');