能否修改数据库中generate_efte_date_type_table函数实现分部门休假计算？

问题背景 #

当前generate_efte_date_type_table函数通过leave_type表的calculate_efte字段统一计算所有部门的other_leave_count_per_staff，导致修改该字段时，Administration和Operation部门会同时被纳入休假计算。需要实现两个部门的休假统计逻辑独立区分。

修改后的函数代码 #

CREATE DEFINER=`root`@`localhost` FUNCTION `generate_efte_date_type_table`(par_date_range_type VARCHAR(45), par_property_record_key TEXT, par_date_year YEAR) RETURNS text CHARSET utf8mb4
    NO SQL
BEGIN
    DECLARE temp_date_type_factor INT DEFAULT 1; 
    DECLARE temp_number_days INT;
    DECLARE temp_number_weeks DECIMAL(10,2);
     
    SELECT DAYOFYEAR(CONCAT(par_date_year, '-12-31')) INTO temp_number_days;
    SET temp_number_weeks = temp_number_days / 7;
    IF par_date_range_type = 'daily' THEN
        SET temp_date_type_factor = temp_number_days;
    ELSEIF par_date_range_type = 'weekly' THEN
        SET temp_date_type_factor = temp_number_weeks; 
    ELSEIF par_date_range_type = 'monthly' THEN
        SET temp_date_type_factor = 12;
    ELSEIF par_date_range_type = 'quarterly' THEN
        SET temp_date_type_factor = 4;
    ELSEIF par_date_range_type = 'semi_yearly' THEN
        SET temp_date_type_factor = 2;
    END IF;

    -- 改为使用部门专属的休假天数计算
    SET @administration_other_leave_count_calculation = "`efte`.`administration_staff_count` * `efte`.`administration_other_leave`";
    SET @operation_other_leave_count_calculation = "`efte`.`operation_staff_count` * `efte`.`operation_other_leave`";

    RETURN CONCAT("(
        SELECT 
            `efte`.`property_record_key` AS `property_record_key`,
            `efte`.`property_display_name_short_code` AS `property_display_name_short_code`,
            `efte`.`property_hex_color_code` AS `property_hex_color_code`,
            IFNULL((((`efte`.`weekly_working_hour_administration` * ", temp_number_weeks, " * `efte`.`administration_staff_count`)
                - (`efte`.`daily_working_hour_administration` * (`efte`.`administration_annual_leave_count` + `efte`.`administration_ph_count` * `efte`.`ph_count` + `efte`.`administration_sh_count` * `efte`.`sh_count` + ", @administration_other_leave_count_calculation, "))) / `efte`.`administration_staff_count` / ", temp_date_type_factor, "), 0) AS `administration_working_hour`,
            IFNULL((((`efte`.`weekly_working_hour_operation` * ", temp_number_weeks, " * `efte`.`operation_staff_count`)
                - (`efte`.`daily_working_hour_operation` * (`efte`.`operation_annual_leave_count` + `efte`.`operation_ph_count` * `efte`.`ph_count` + `efte`.`operation_sh_count` * `efte`.`sh_count` + ", @operation_other_leave_count_calculation,") )) / `efte`.`operation_staff_count` / ", temp_date_type_factor, "), 0) AS `operation_working_hour`,
            (IFNULL((((`efte`.`weekly_working_hour_operation` * ", temp_number_weeks, " * `efte`.`operation_staff_count`)
                - (`efte`.`daily_working_hour_operation` * (`efte`.`operation_annual_leave_count` + `efte`.`operation_ph_count` * `efte`.`ph_count` + `efte`.`operation_sh_count` * `efte`.`sh_count` + ", @operation_other_leave_count_calculation,") )) / `efte`.`operation_staff_count` / ", temp_date_type_factor, "), 0)
            +
            IFNULL((((`efte`.`weekly_working_hour_administration` * ", temp_number_weeks, " * `efte`.`administration_staff_count`)
                - (`efte`.`daily_working_hour_administration` * (`efte`.`administration_annual_leave_count` + `efte`.`administration_ph_count` * `efte`.`ph_count` + `efte`.`administration_sh_count` * `efte`.`sh_count` + ", @administration_other_leave_count_calculation, "))) / `efte`.`administration_staff_count` / ", temp_date_type_factor, "), 0)) AS `all_working_hour`,
            IFNULL((`efte`.`weekly_working_hour_administration` * ", temp_number_weeks, "/ ", temp_date_type_factor, "),0) AS `administration_casual_working_hour`, 
            IFNULL((`efte`.`weekly_working_hour_operation` * ", temp_number_weeks, "/ ", temp_date_type_factor, "),0) AS `operation_casual_working_hour`,
            (IFNULL((`efte`.`weekly_working_hour_administration` * ", temp_number_weeks, "/ ", temp_date_type_factor, "),0)
            +
            IFNULL((`efte`.`weekly_working_hour_operation` * ", temp_number_weeks, "/ ", temp_date_type_factor, "),0)) AS `all_casual_working_hour`
        FROM 
            (SELECT 
                `p`.`record_key` AS `property_record_key`, 
                `p`.`display_name_short_code` AS `property_display_name_short_code`, 
                `p`.`hex_color_code` AS `property_hex_color_code`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Administration' THEN 1 ELSE 0 END), 0) AS `administration_staff_count`, 
                `ewh`.`daily_working_hour_administration` AS `daily_working_hour_administration`,
                `ewh`.`weekly_working_hour_administration` AS `weekly_working_hour_administration`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Administration' THEN `s`.`annual_leave` ELSE 0 END), 0) AS `administration_annual_leave_count`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Administration' AND `s`.`type_of_holiday` = 'PH' THEN 1 ELSE 0 END), 0) AS `administration_ph_count`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Administration' AND `s`.`type_of_holiday` = 'SH' THEN 1 ELSE 0 END), 0) AS `administration_sh_count`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Operation' THEN 1 ELSE 0 END), 0) AS `operation_staff_count`,
                `ewh`.`daily_working_hour_operation` AS `daily_working_hour_operation`,
                `ewh`.`weekly_working_hour_operation` AS `weekly_working_hour_operation`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Operation' THEN 1 ELSE 0 END), 0) AS `operation_annual_leave_count`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Operation' AND `s`.`type_of_holiday` = 'PH' THEN 1 ELSE 0 END), 0) AS `operation_ph_count`,
                IFNULL(SUM(CASE WHEN `s`.`staff_type` = 'Operation' AND `s`.`type_of_holiday` = 'SH' THEN 1 ELSE 0 END), 0) AS `operation_sh_count`,
                IFNULL(`hc`.`ph_count`, 0) AS `ph_count`,
                IFNULL(`hc`.`sh_count`, 0) AS `sh_count`,
                -- 新增部门专属休假字段
                IFNULL(`lc`.`administration_other_leave`, 0) AS `administration_other_leave`,
                IFNULL(`lc`.`operation_other_leave`, 0) AS `operation_other_leave`
            FROM `staff` `s`
            JOIN `property` `p` ON `s`.`property_record_key` = `p`.`record_key` AND INSTR('"', par_property_record_key, '"', `p`.`record_key`)
            JOIN `employment_status` `es` ON `s`.`employment_status_record_key` = `es`.`record_key`
            JOIN `efte_working_hour` `ewh` ON `s`.`property_record_key` = `ewh`.`property_record_key`
            LEFT JOIN (
                SELECT
                    `h`.`property_record_key` AS `property_record_key`,
                    IFNULL(SUM(CASE WHEN `h`.`type_of_holiday` = 'PH' THEN 1 ELSE 0 END), 0) AS `ph_count`,
                    IFNULL(SUM(CASE WHEN `h`.`type_of_holiday` = 'SH' THEN 1 ELSE 0 END), 0) AS `sh_count`
                FROM
                    `holiday` `h` 
                WHERE `h`.`year_of_holiday` = '"', par_date_year, '"'
                GROUP BY `h`.`property_record_key`
            ) `hc` ON `s`.`property_record_key` = `hc`.`property_record_key`
            LEFT JOIN (
                -- 重构休假统计逻辑，按部门区分计算
                SELECT 
                    `property_record_key`,
                    SUM(CASE WHEN `lt`.`department` = 'Administration' AND `lt`.`calculate_efte` IS TRUE THEN `lt`.`default_days` ELSE 0 END) AS `administration_other_leave`,
                    SUM(CASE WHEN `lt`.`department` = 'Operation' AND `lt`.`calculate_efte` IS TRUE THEN `lt`.`default_days` ELSE 0 END) AS `operation_other_leave`
                FROM `leave_type` `lt`
                GROUP BY `lt`.`property_record_key`
            ) `lc` ON `s`.`property_record_key` = `lc`.`property_record_key`
            WHERE `s`.`record_status` = 'Active' AND LOWER(`es`.`permanent`) = 'yes'
            GROUP BY `s`.`property_record_key`
            ) `efte`) `efte_date_type`");
END

关键修改说明 #

• 拆分休假统计维度：重构leave_type关联子查询，通过CASE语句按部门区分统计符合calculate_efte条件的休假天数，生成两个部门专属的休假字段
• 独立计算逻辑：将原统一的other_leave_count_per_staff替换为部门专属字段，分别用于Administration和Operation部门的工时扣减计算
• 保持原有结构兼容：仅修改休假统计相关逻辑，其余工时计算逻辑保持不变，确保函数输出结构与原有调用逻辑兼容

内容的提问来源于stack exchange，提问作者tuneup90