173 lines
7.5 KiB
SQL
173 lines
7.5 KiB
SQL
-- 员工用工类型、人工转正、社保状态及人事变更材料。
|
|
-- 适用数据库：MySQL 8.0+
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
DROP PROCEDURE IF EXISTS ob_upgrade_employee_hr_management;
|
|
DELIMITER $$
|
|
|
|
CREATE PROCEDURE ob_upgrade_employee_hr_management()
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'ob_onboarding_task'
|
|
AND COLUMN_NAME = 'EmploymentType'
|
|
) THEN
|
|
ALTER TABLE ob_onboarding_task
|
|
ADD COLUMN EmploymentType VARCHAR(20) COLLATE utf8mb4_unicode_ci
|
|
NOT NULL DEFAULT 'full_time' COMMENT '用工类型：full_time正式、part_time兼职'
|
|
AFTER PositionId;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'ob_employee'
|
|
AND COLUMN_NAME = 'EmploymentType'
|
|
) THEN
|
|
ALTER TABLE ob_employee
|
|
ADD COLUMN EmploymentType VARCHAR(20) COLLATE utf8mb4_unicode_ci
|
|
NOT NULL DEFAULT 'full_time' COMMENT '用工类型：full_time正式、part_time兼职'
|
|
AFTER PositionId;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'ob_employee'
|
|
AND COLUMN_NAME = 'RegularizationStatus'
|
|
) THEN
|
|
ALTER TABLE ob_employee
|
|
ADD COLUMN RegularizationStatus VARCHAR(20) COLLATE utf8mb4_unicode_ci
|
|
NOT NULL DEFAULT 'probation' COMMENT '转正状态：probation、regular、not_applicable'
|
|
AFTER EmploymentType;
|
|
END IF;
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'ob_employee'
|
|
AND COLUMN_NAME = 'SocialInsuranceStatus'
|
|
) THEN
|
|
ALTER TABLE ob_employee
|
|
ADD COLUMN SocialInsuranceStatus VARCHAR(20) COLLATE utf8mb4_unicode_ci
|
|
NOT NULL DEFAULT 'unknown' COMMENT '社保状态：unknown、not_purchased、purchased、stopped、not_applicable'
|
|
AFTER RegularizationStatus;
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'ob_employee_contract_snapshot'
|
|
) AND NOT EXISTS (
|
|
SELECT 1 FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'ob_employee_contract_snapshot'
|
|
AND COLUMN_NAME = 'ProbationSalary'
|
|
) THEN
|
|
ALTER TABLE ob_employee_contract_snapshot
|
|
ADD COLUMN ProbationSalary DECIMAL(18,2) DEFAULT NULL
|
|
COMMENT '试用薪资，对应hr.ProbationSalary，不自动推算'
|
|
AFTER ProbationTerm;
|
|
END IF;
|
|
END$$
|
|
|
|
DELIMITER ;
|
|
CALL ob_upgrade_employee_hr_management();
|
|
DROP PROCEDURE IF EXISTS ob_upgrade_employee_hr_management;
|
|
|
|
-- 历史任务及员工默认按正式用工处理；历史转正日期仅用于一次性初始化状态。
|
|
UPDATE ob_onboarding_task
|
|
SET EmploymentType = 'full_time'
|
|
WHERE EmploymentType IS NULL OR EmploymentType NOT IN ('full_time', 'part_time');
|
|
|
|
UPDATE ob_employee
|
|
SET
|
|
EmploymentType = CASE
|
|
WHEN EmploymentType = 'part_time' THEN 'part_time'
|
|
ELSE 'full_time'
|
|
END,
|
|
RegularizationStatus = CASE
|
|
WHEN EmploymentType = 'part_time' THEN 'not_applicable'
|
|
WHEN RegularizationDate IS NOT NULL THEN 'regular'
|
|
ELSE 'probation'
|
|
END,
|
|
SocialInsuranceStatus = CASE
|
|
WHEN SocialInsuranceStatus IN (
|
|
'unknown', 'not_purchased', 'purchased', 'stopped', 'not_applicable'
|
|
) THEN SocialInsuranceStatus
|
|
ELSE 'unknown'
|
|
END;
|
|
|
|
CREATE TABLE IF NOT EXISTS ob_employee_change_record (
|
|
Id BIGINT NOT NULL COMMENT '主键',
|
|
EmployeeId BIGINT NOT NULL COMMENT '员工Id',
|
|
ChangeType VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '变更类型',
|
|
EffectiveDate DATE DEFAULT NULL COMMENT '业务生效日期',
|
|
BeforeJson LONGTEXT COLLATE utf8mb4_unicode_ci COMMENT '变更前快照',
|
|
AfterJson LONGTEXT COLLATE utf8mb4_unicode_ci COMMENT '变更后快照',
|
|
Remark VARCHAR(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
|
|
ExtJson LONGTEXT COLLATE utf8mb4_unicode_ci COMMENT '扩展信息',
|
|
Status VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT 'enable' COMMENT '记录状态',
|
|
CreateTime DATETIME(3) DEFAULT NULL COMMENT '创建时间',
|
|
UpdateTime DATETIME(3) DEFAULT NULL COMMENT '更新时间',
|
|
CreateUserId BIGINT DEFAULT NULL COMMENT '创建者Id',
|
|
UpdateUserId BIGINT DEFAULT NULL COMMENT '修改者Id',
|
|
CreateUser VARCHAR(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',
|
|
UpdateUser VARCHAR(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',
|
|
IsDelete TINYINT(1) DEFAULT 0 COMMENT '软删除',
|
|
PRIMARY KEY (Id),
|
|
KEY idx_ob_employee_change_record_employee (EmployeeId, IsDelete, CreateTime),
|
|
KEY idx_ob_employee_change_record_type (ChangeType, EffectiveDate)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='员工人事信息变更记录';
|
|
|
|
CREATE TABLE IF NOT EXISTS ob_employee_attachment (
|
|
Id BIGINT NOT NULL COMMENT '主键',
|
|
EmployeeId BIGINT NOT NULL COMMENT '员工Id',
|
|
ChangeRecordId BIGINT DEFAULT NULL COMMENT '关联人事变更记录Id',
|
|
BusinessType VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '附件业务类型',
|
|
FileId BIGINT NOT NULL COMMENT '系统文件Id',
|
|
FileName VARCHAR(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '上传时文件名快照',
|
|
UploadedAt DATETIME(3) NOT NULL COMMENT '上传关联时间',
|
|
ExtJson LONGTEXT COLLATE utf8mb4_unicode_ci COMMENT '扩展信息',
|
|
Status VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT 'enable' COMMENT '记录状态',
|
|
CreateTime DATETIME(3) DEFAULT NULL COMMENT '创建时间',
|
|
UpdateTime DATETIME(3) DEFAULT NULL COMMENT '更新时间',
|
|
CreateUserId BIGINT DEFAULT NULL COMMENT '创建者Id',
|
|
UpdateUserId BIGINT DEFAULT NULL COMMENT '修改者Id',
|
|
CreateUser VARCHAR(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',
|
|
UpdateUser VARCHAR(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',
|
|
IsDelete TINYINT(1) DEFAULT 0 COMMENT '软删除',
|
|
PRIMARY KEY (Id),
|
|
KEY idx_ob_employee_attachment_employee (EmployeeId, BusinessType, IsDelete),
|
|
KEY idx_ob_employee_attachment_change (ChangeRecordId, IsDelete),
|
|
KEY idx_ob_employee_attachment_file (FileId)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='员工人事材料附件';
|
|
|
|
-- 标准试用薪资变量；已有同编码历史数据时恢复并统一为内置数值变量。
|
|
INSERT INTO ob_variable_definition (
|
|
Id, VariableCode, VariableName, VariableCategory, DataType,
|
|
IsBuiltIn, Required, SortCode, Remark, Status,
|
|
CreateTime, IsDelete
|
|
) VALUES (
|
|
841400000000115, 'hr.ProbationSalary', '试用薪资(元)', 'hr', 'number',
|
|
1, 0, 335, '合同约定试用期薪资，不自动推算', 'enable',
|
|
NOW(), 0
|
|
)
|
|
ON DUPLICATE KEY UPDATE
|
|
VariableName = VALUES(VariableName),
|
|
VariableCategory = 'hr',
|
|
DataType = 'number',
|
|
IsBuiltIn = 1,
|
|
SortCode = 335,
|
|
Remark = VALUES(Remark),
|
|
Status = 'enable',
|
|
IsDelete = 0;
|
|
|
|
SELECT
|
|
(SELECT COUNT(*) FROM ob_employee_change_record) AS '员工人事变更记录数',
|
|
(SELECT COUNT(*) FROM ob_employee_attachment) AS '员工人事附件数',
|
|
(SELECT COUNT(*) FROM ob_variable_definition
|
|
WHERE VariableCode = 'hr.ProbationSalary' AND IsDelete = 0) AS '试用薪资标准变量数';
|