-- 离职申请增强：交接员工/用户与用户数据转移、离职审批分类、到期自动离职 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');
|
