-- ============================================================ -- 考勤记录表增加请假相关字段,打通「请假 → 考勤」 -- 生成时间:2026-10-30 -- -- 背景:请假单与考勤完全脱节—— -- · 请假当天照样可以打卡、照样算迟到 -- · 请假当天不打卡会被算成「缺卡」 -- · 考勤记录里没有任何字段能体现「这天请假了」 -- · 月度统计的 leaveHours 字段写死为 0,从未计算 -- -- 做法:在 hrm_attendance_record 上增加三个字段—— -- leave_flag 当天是否请假(有审批通过的请假单覆盖当天即为 1) -- leave_hours 当天请假工时(按请假单的起止时间与考勤规则的工作时段求交集,精确到分钟) -- leave_types 当天涉及的请假类型,逗号分隔(支持一天内多段请假、不同类型) -- -- 粒度:按小时(0.01 小时 = 36 秒),与 hrm_leave_application.duration 精度一致 -- 幂等:先查 information_schema 再 ALTER -- ============================================================ DROP PROCEDURE IF EXISTS sp_attendance_add_leave_col; DELIMITER $$ CREATE PROCEDURE sp_attendance_add_leave_col(IN p_col VARCHAR(64), IN p_def VARCHAR(255), IN p_comment VARCHAR(255), IN p_after VARCHAR(64)) BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = p_col) THEN SET @s = CONCAT('ALTER TABLE `hrm_attendance_record` ADD COLUMN `', p_col, '` ', p_def, ' COMMENT ''', p_comment, ''' AFTER `', p_after, '`'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; END IF; END$$ DELIMITER ; CALL sp_attendance_add_leave_col('leave_flag', 'bit(1) NOT NULL DEFAULT b''0''', '当天是否请假(有审批通过的请假单覆盖当天)', 'overtime_hours'); CALL sp_attendance_add_leave_col('leave_hours', 'decimal(6,2) NOT NULL DEFAULT 0.00', '当天请假工时(按请假单与工作时段交集计算,精确到 0.01 小时)', 'leave_flag'); CALL sp_attendance_add_leave_col('leave_types', 'varchar(64) DEFAULT NULL', '当天请假类型,逗号分隔(如 1,2 表示既有事假又有病假)', 'leave_hours'); DROP PROCEDURE IF EXISTS sp_attendance_add_leave_col; -- ---------- 索引:按日期+请假标记查询更快 ---------- SET @idx = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND index_name = 'idx_leave_flag'); SET @s2 = IF(@idx = 0, 'CREATE INDEX `idx_leave_flag` ON `hrm_attendance_record` (`leave_flag`)', 'SELECT 1'); PREPARE st2 FROM @s2; EXECUTE st2; DEALLOCATE PREPARE st2; -- ---------- 校验 ---------- SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND COLUMN_NAME IN ('leave_flag', 'leave_hours', 'leave_types') ORDER BY ORDINAL_POSITION;