-- =============================================
|
-- 薪酬核算：新增「岗位工资」「工龄工资」
|
--
|
-- 业务说明：
|
-- 1) 岗位工资、工龄工资与「基本工资」同源，在【员工管理】的员工信息中维护
|
-- 2) 执行薪酬核算时带入核算单，并计入应发工资与实发工资
|
-- 3) 薪酬档案(hrm_employee_salary)同步加列，便于后续扩展；当前核算以员工信息为准
|
--
|
-- 脚本可重复执行（幂等）
|
-- =============================================
|
SET NAMES utf8mb4;
|
|
SET @sch := DATABASE();
|
|
-- ---------- 1. 员工信息：岗位工资 / 工龄工资 ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_employee' AND COLUMN_NAME = 'post_salary');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_employee` ADD COLUMN `post_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''岗位工资'' AFTER `base_salary`',
|
'SELECT ''hrm_employee.post_salary 已存在'' AS msg');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_employee' AND COLUMN_NAME = 'seniority_salary');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_employee` ADD COLUMN `seniority_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''工龄工资'' AFTER `post_salary`',
|
'SELECT ''hrm_employee.seniority_salary 已存在'' AS msg');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------- 2. 薪酬核算单：岗位工资 / 工龄工资 ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_salary_calculation' AND COLUMN_NAME = 'post_salary');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_salary_calculation` ADD COLUMN `post_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''岗位工资'' AFTER `performance_salary`',
|
'SELECT ''hrm_salary_calculation.post_salary 已存在'' AS msg');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_salary_calculation' AND COLUMN_NAME = 'seniority_salary');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_salary_calculation` ADD COLUMN `seniority_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''工龄工资'' AFTER `post_salary`',
|
'SELECT ''hrm_salary_calculation.seniority_salary 已存在'' AS msg');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------- 3. 薪酬档案（可选，当前核算不取此表的基本/岗位/工龄工资） ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_employee_salary' AND COLUMN_NAME = 'post_salary');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_employee_salary` ADD COLUMN `post_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''岗位工资'' AFTER `base_salary`',
|
'SELECT ''hrm_employee_salary.post_salary 已存在'' AS msg');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_employee_salary' AND COLUMN_NAME = 'seniority_salary');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_employee_salary` ADD COLUMN `seniority_salary` decimal(10,2) DEFAULT ''0.00'' COMMENT ''工龄工资'' AFTER `post_salary`',
|
'SELECT ''hrm_employee_salary.seniority_salary 已存在'' AS msg');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------- 4. 核对 ----------
|
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, COLUMN_COMMENT
|
FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = @sch
|
AND TABLE_NAME IN ('hrm_employee', 'hrm_salary_calculation', 'hrm_employee_salary')
|
AND COLUMN_NAME IN ('post_salary', 'seniority_salary')
|
ORDER BY TABLE_NAME, COLUMN_NAME;
|
|
SELECT '岗位工资 / 工龄工资字段初始化完成' AS result;
|
