-- ============================================= -- 薪酬核算:新增「全勤奖」「实出天数」 -- -- 背景: -- 1) 全勤奖:与其他津贴口径一致,由人工在核算单中维护(默认 0),计入应发/实发 -- 2) 实出天数:取自考勤汇总表 hrm_attendance_summary.actual_days -- 「应出天数」沿用原 work_days(按排班累计的应出勤天数,无排班回退 22),语义不变,仅前端改名 -- 「排班工时」原列 total_work_hours 保留但不再使用(历史数据不删除) -- -- 脚本可重复执行(幂等) -- ============================================= SET NAMES utf8mb4; SET @sch := DATABASE(); -- 1. 全勤奖 SET @exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_salary_calculation' AND COLUMN_NAME = 'full_attendance_bonus'); SET @sql := IF(@exists = 0, 'ALTER TABLE `hrm_salary_calculation` ADD COLUMN `full_attendance_bonus` decimal(10,2) DEFAULT ''0.00'' COMMENT ''全勤奖'' AFTER `other_allowance`', 'SELECT ''full_attendance_bonus 已存在'' 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 = 'actual_days'); SET @sql := IF(@exists = 0, 'ALTER TABLE `hrm_salary_calculation` ADD COLUMN `actual_days` int DEFAULT 0 COMMENT ''实出天数(取自考勤汇总)'' AFTER `work_days`', 'SELECT ''actual_days 已存在'' AS msg'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 3. 历史数据回填实出天数(按同员工同周期从考勤汇总带一次,避免历史单全是 0) UPDATE `hrm_salary_calculation` c JOIN `hrm_attendance_summary` s ON s.`user_id` = c.`user_id` AND s.`year_month` = c.`period` AND s.`deleted` = b'0' SET c.`actual_days` = s.`actual_days` WHERE c.`deleted` = b'0' AND (c.`actual_days` IS NULL OR c.`actual_days` = 0) AND s.`actual_days` IS NOT NULL; -- 4. 核对 SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @sch AND TABLE_NAME = 'hrm_salary_calculation' AND COLUMN_NAME IN ('work_days','actual_days','full_attendance_bonus','total_work_hours') ORDER BY ORDINAL_POSITION; SELECT COUNT(*) AS 有实出天数的核算单数 FROM `hrm_salary_calculation` WHERE `deleted` = b'0' AND `actual_days` > 0; SELECT '薪酬核算 全勤奖/实出天数 字段初始化完成' AS result;