# SvaProductivityRatioSettingsServiceImpl.java

    package cloud.wconnect.workperformance.service.sva.setting.impl;

    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.cons.CommonErrorI18nConstant;
    import cloud.wconnect.commonlib.exception.BadRequestException;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.redis.PropertyI18nRedisService;
    import cloud.wconnect.commonlib.util.CellUtils;
    import cloud.wconnect.commonlib.util.I18nMessageUtils;
    import cloud.wconnect.workperformance.cons.sva.SvaSettingCalculateTypeEnum;
    import cloud.wconnect.workperformance.cons.sva.SvaSettingDriverTypeEnum;
    import cloud.wconnect.workperformance.cons.sva.SvaSettingVolumeReferenceEnum;
    import cloud.wconnect.workperformance.entity.sva.SvaProductivityRatioSettings;
    import cloud.wconnect.workperformance.mapper.sva.SvaProductivityRatioSettingsMapper;
    import cloud.wconnect.workperformance.pojo.dto.sva.productivityratiosettings.ProductivityRatioSettingsUploadDTO;
    import cloud.wconnect.workperformance.service.sva.setting.ISvaProductivityRatioSettingsService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import com.google.common.collect.Sets;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.collections.CollectionUtils;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.*;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;

    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.util.*;
    import java.util.function.Function;
    import java.util.stream.Collectors;

    /**
     * <p>
     * 服务实现类
     * </p>
     *
     * @author lichen
     * @since 2026-03-05
     */
    @Slf4j
    @Service
    @DS("work_performance")
    public class SvaProductivityRatioSettingsServiceImpl extends ServiceImpl<SvaProductivityRatioSettingsMapper, SvaProductivityRatioSettings> implements ISvaProductivityRatioSettingsService {

        @Autowired
        private PropertyI18nRedisService propertyI18nRedisService;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;

        /**
         * 查询指定年份的配置
         *
         * @param propertyId 资产 id
         * @param years      年份集合
         * @return java.util.List<cloud.wconnect.workperformance.entity.sva.SvaProductivityRatioSettings>
         * @author lichen
         * @date 2026/3/6
         */
        @Override
        public List<SvaProductivityRatioSettings> listByYears(int propertyId, Set<Integer> years) {
            if (CollectionUtils.isEmpty(years)) return Collections.emptyList();
            return list(new LambdaQueryWrapper<SvaProductivityRatioSettings>()
                    .eq(SvaProductivityRatioSettings::getPropertyId, propertyId)
                    .in(SvaProductivityRatioSettings::getYear, years)
                    .orderByAsc(SvaProductivityRatioSettings::getId));
        }

        /**
         * 上传Productivity Ratio Excel
         *
         * @param uploadDTO 上传DTO
         * @author lichen
         * @date 2026/3/6
         */
        @Override
        @Transactional(rollbackFor = Exception.class)
        public void upload(ProductivityRatioSettingsUploadDTO uploadDTO) {
            List<PropertyDTO> propertyDTOList = propertyI18nRedisService.list(uploadDTO.getPropertyIds());

            try (InputStream inputStream = uploadDTO.getFile().getInputStream()) {
                // 解析 Excel
                List<SvaProductivityRatioSettings> settingsList = parseExcel(inputStream, propertyDTOList, uploadDTO.getYear());

                if (CollectionUtils.isEmpty(settingsList)) {
                    throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                            i18nMessageUtils.getErrorParamMapNotNeedTranslate("file"),
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            "No valid data found in Excel");
                }

                // 批量插入或更新
                batchInsertOrUpdate(settingsList, uploadDTO.getPropertyIds(), uploadDTO.getYear());
            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new BadRequestException(BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Excel parsing failed: " + e.getMessage());
            }
        }

        /**
         * 检查指定资产指定年份的配置是否存在
         *
         * @param propertyId 资产id
         * @param year       年份
         * @return java.util.List<cloud.wconnect.workperformance.entity.sva.SvaProductivityRatioSettings>
         * @author lichen
         * @date 2026/3/6
         */
        @Override
        public List<SvaProductivityRatioSettings> checkExist(int propertyId, int year) {
            List<SvaProductivityRatioSettings> svaProductivityRatioSettings = listByYears(propertyId, Sets.newHashSet(year));
            if (CollectionUtils.isEmpty(svaProductivityRatioSettings)) {
                throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate("ProductivityRatioSettings"),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "The SvaProductivityRatioSettings does not exist");
            }
            return svaProductivityRatioSettings;
        }

        /**
         * 解析 Excel 文件
         *
         * @param inputStream     Excel 输入流
         * @param propertyDTOList 资产集合
         * @param year            年份
         * @return java.util.List<cloud.wconnect.workperformance.entity.sva.SvaProductivityRatioSettings>
         * @author lichen
         * @date 2026/3/6
         */
        private List<SvaProductivityRatioSettings> parseExcel(InputStream inputStream, List<PropertyDTO> propertyDTOList, int year) {
            Map<String, PropertyDTO> abbr2PropertyDTO = propertyDTOList.stream().collect(Collectors.toMap(item -> {
                String abbr = item.getAbbr();
                if (abbr.equals("ahw")) {
                    return "APA";
                } else {
                    return abbr.toUpperCase();
                }
            }, Function.identity()));
            List<SvaProductivityRatioSettings> allSettings = new ArrayList<>();

            try (Workbook workbook = WorkbookFactory.create(inputStream)) {
                // 获取 Sheet 数量
                int numberOfSheets = workbook.getNumberOfSheets();

                // 遍历所有 Sheet（跳过第一个 Readme Sheet）
                for (int i = 1; i < numberOfSheets; i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    String sheetName = sheet.getSheetName().toUpperCase();
                    if (abbr2PropertyDTO.containsKey(sheetName)) {
                        PropertyDTO propertyDTO = abbr2PropertyDTO.get(sheetName);
                        // 解析每个 Sheet
                        List<SvaProductivityRatioSettings> settingsList = parseSheet(sheet, propertyDTO, year, sheetName);
                        allSettings.addAll(settingsList);
                    }
                }
            } catch (Exception e) {
                throw new BadRequestException(BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Failed to parse Excel: " + e.getMessage());
            }

            return allSettings;
        }

        /**
         * 解析 Sheet 页
         *
         * @param sheet       Sheet 对象
         * @param propertyDTO 资产DTO
         * @param year        年份
         * @param sheetName   Sheet 名称（资产标识）
         * @return java.util.List<cloud.wconnect.workperformance.entity.sva.SvaProductivityRatioSettings>
         * @author lichen
         * @date 2026/3/6
         */
        private List<SvaProductivityRatioSettings> parseSheet(Sheet sheet, PropertyDTO propertyDTO, int year, String sheetName) {
            List<SvaProductivityRatioSettings> settingsList = new ArrayList<>();
            // 获取表头
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(sheetName + " header Row"),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Missing header row in sheet: " + sheetName);
            }

            // 从第二行开始解析数据
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue; // 跳过空行
                }

                try {
                    SvaProductivityRatioSettings setting = parseRow(row, sheetName, rowNum + 1);
                    setting.setPropertyId(propertyDTO.getId());
                    setting.setTenantId(propertyDTO.getTenantId());
                    setting.setYear(year);
                    settingsList.add(setting);
                } catch (BadRequestException e) {
                    throw e;
                } catch (Exception e) {
                    throw new BadRequestException(BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getI18nCode(),
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            "Failed to parse row " + (rowNum + 1) + " in sheet '" + sheetName + "': " + e.getMessage());
                }
            }

            return settingsList;
        }

        /**
         * 解析数据行
         *
         * @param row       数据行
         * @param sheetName Sheet 名称
         * @param rowNum    行号（从 1 开始）
         * @return cloud.wconnect.workperformance.entity.sva.SvaProductivityRatioSettings
         * @author lichen
         * @date 2026/3/6
         */
        private SvaProductivityRatioSettings parseRow(Row row, String sheetName, int rowNum) {
            // 获取单元格值
            String positionDesc = CellUtils.convertCellStr(row.getCell(0));
            String departmentCode = CellUtils.convertCellStr(row.getCell(1));
            String calculationType = CellUtils.convertCellStr(row.getCell(2));
            String driverType = CellUtils.convertCellStr(row.getCell(3));
            String volumeReference = CellUtils.convertCellStr(row.getCell(4));
            String levelAOccStr = CellUtils.convertCellStr(row.getCell(5));
            String levelBOccStr = CellUtils.convertCellStr(row.getCell(6));
            String levelCOccStr = CellUtils.convertCellStr(row.getCell(7));
            String levelDOccStr = CellUtils.convertCellStr(row.getCell(8));

            String levelARatioStr = removeNull(CellUtils.convertCellStr(row.getCell(9)));
            String levelBRatioStr = removeNull(CellUtils.convertCellStr(row.getCell(10)));
            String levelCRatioStr = removeNull(CellUtils.convertCellStr(row.getCell(11)));
            String levelDRatioStr = removeNull(CellUtils.convertCellStr(row.getCell(12)));
            String levelAFixedHoursStr = removeNull(CellUtils.convertCellStr(row.getCell(13)));
            String levelBFixedHoursStr = removeNull(CellUtils.convertCellStr(row.getCell(14)));
            String levelCFixedHoursStr = removeNull(CellUtils.convertCellStr(row.getCell(15)));
            String levelDFixedHoursStr = removeNull(CellUtils.convertCellStr(row.getCell(16)));
            String notes = CellUtils.convertCellStr(row.getCell(17));

            // 校验必填字段
            validateRequired(rowNum, sheetName, positionDesc, departmentCode, calculationType,
                    driverType, volumeReference, levelAOccStr, levelBOccStr, levelCOccStr, levelDOccStr);

            // 使用枚举校验字段值
            validateCalculationType(rowNum, sheetName, calculationType);
            validateDriverType(rowNum, sheetName, driverType);
            validateVolumeReference(rowNum, sheetName, volumeReference);

            // 转换数值类型
            Integer levelAOcc = parseInteger(levelAOccStr, rowNum, sheetName, "LEVEL_A_OCC");
            Integer levelBOcc = parseInteger(levelBOccStr, rowNum, sheetName, "LEVEL_B_OCC");
            Integer levelCOcc = parseInteger(levelCOccStr, rowNum, sheetName, "LEVEL_C_OCC");
            Integer levelDOcc = parseInteger(levelDOccStr, rowNum, sheetName, "LEVEL_D_OCC");

            BigDecimal levelARatio = parseNumber(levelARatioStr, rowNum, sheetName, "LEVEL_A_Ratio");
            BigDecimal levelBRatio = parseNumber(levelBRatioStr, rowNum, sheetName, "LEVEL_B_Ratio");
            BigDecimal levelCRatio = parseNumber(levelCRatioStr, rowNum, sheetName, "LEVEL_C_Ratio");
            BigDecimal levelDRatio = parseNumber(levelDRatioStr, rowNum, sheetName, "LEVEL_D_Ratio");

            Integer levelAFixedHours = parseInteger(levelAFixedHoursStr, rowNum, sheetName, "LEVEL_A_Fixed_hours");
            Integer levelBFixedHours = parseInteger(levelBFixedHoursStr, rowNum, sheetName, "LEVEL_B_Fixed_hours");
            Integer levelCFixedHours = parseInteger(levelCFixedHoursStr, rowNum, sheetName, "LEVEL_C_Fixed_hours");
            Integer levelDFixedHours = parseInteger(levelDFixedHoursStr, rowNum, sheetName, "LEVEL_D_Fixed_hours");

            // 构建实体对象
            SvaProductivityRatioSettings setting = new SvaProductivityRatioSettings();
            setting.setDepartmentCode(departmentCode);
            setting.setDepartmentDesc(positionDesc);
            setting.setCalculationType(calculationType);
            setting.setDriverType(driverType);
            setting.setVolumeReference(volumeReference);
            setting.setLevelAOcc(levelAOcc);
            setting.setLevelBOcc(levelBOcc);
            setting.setLevelCOcc(levelCOcc);
            setting.setLevelDOcc(levelDOcc);
            setting.setLevelARatio(levelARatio);
            setting.setLevelBRatio(levelBRatio);
            setting.setLevelCRatio(levelCRatio);
            setting.setLevelDRatio(levelDRatio);
            setting.setLevelAFixedHours(levelAFixedHours);
            setting.setLevelBFixedHours(levelBFixedHours);
            setting.setLevelCFixedHours(levelCFixedHours);
            setting.setLevelDFixedHours(levelDFixedHours);
            setting.setNote(notes);
            return setting;
        }

        /**
         * 校验必填字段
         *
         * @param rowNum          行号（从 1 开始）
         * @param sheetName       Sheet 名称
         * @param positionDesc    位置描述
         * @param departmentCode  部门代码
         * @param calculationType 计算类型
         * @param driverType      驱动类型
         * @param volumeReference 引用量
         * @param levelAOcc       A级Occ
         * @param levelBOcc       B级Occ
         * @param levelCOcc       C级Occ
         * @param levelDOcc       D级Occ
         * @author lichen
         * @date 2026/3/6
         */
        private void validateRequired(int rowNum, String sheetName, String positionDesc, String departmentCode,
                                      String calculationType, String driverType, String volumeReference,
                                      String levelAOcc, String levelBOcc, String levelCOcc, String levelDOcc) {
            List<String> missingFields = new ArrayList<>();

            if (StringUtils.isBlank(positionDesc)) {
                missingFields.add("Position Description");
            }
            if (StringUtils.isBlank(departmentCode)) {
                missingFields.add("Timecard Work Department");
            }
            if (StringUtils.isBlank(calculationType)) {
                missingFields.add("Calculation Type");
            }
            if (StringUtils.isBlank(driverType)) {
                missingFields.add("Driver_Type");
            }
            if (StringUtils.isBlank(volumeReference)) {
                missingFields.add("VOLUME_REFERENCE");
            }
            if (StringUtils.isBlank(levelAOcc)) {
                missingFields.add("Level_A_OCC");
            }
            if (StringUtils.isBlank(levelBOcc)) {
                missingFields.add("Level_B_OCC");
            }
            if (StringUtils.isBlank(levelCOcc)) {
                missingFields.add("Level_C_OCC");
            }
            if (StringUtils.isBlank(levelDOcc)) {
                missingFields.add("Level_D_OCC");
            }

            if (!missingFields.isEmpty()) {
                // "Sheet 'CPE' row 5: Fields A, B, C cannot be empty"
                String fields = String.join(", ", missingFields);
                throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate("Sheet '" + sheetName + "' row " + rowNum + ": Fields " + fields),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Missing required fields in row " + rowNum + " sheet '" + sheetName + "': " + fields);
            }
        }

        /**
         * 校验 Calculation Type
         *
         * @param rowNum          行号
         * @param sheetName       工作表名称
         * @param calculationType 值
         * @author lichen
         * @date 2026/3/6
         */
        private void validateCalculationType(int rowNum, String sheetName, String calculationType) {
            boolean isValid = false;
            for (SvaSettingCalculateTypeEnum type : SvaSettingCalculateTypeEnum.values()) {
                if (type.getValue().equals(calculationType)) {
                    isValid = true;
                    break;
                }
            }
            if (!isValid) {
                throw new BadRequestException(CommonErrorI18nConstant.DATA_INVALID,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate("Sheet '" + sheetName + "' row " + rowNum + ": Calculation Type "),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Invalid Calculation Type in row " + rowNum + " sheet '" + sheetName + "': " + calculationType);
            }
        }

        /**
         * 校验 Driver Type
         *
         * @param rowNum     行号
         * @param sheetName  工作表名称
         * @param driverType 值
         * @author lichen
         * @date 2026/3/6
         */
        private void validateDriverType(int rowNum, String sheetName, String driverType) {
            boolean isValid = false;
            for (SvaSettingDriverTypeEnum type : SvaSettingDriverTypeEnum.values()) {
                if (type.getValue().equals(driverType)) {
                    isValid = true;
                    break;
                }
            }
            if (!isValid) {
                throw new BadRequestException(CommonErrorI18nConstant.DATA_INVALID,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate("Sheet '" + sheetName + "' row " + rowNum + ": Driver Type "),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Invalid Driver Type in row " + rowNum + " sheet '" + sheetName + "': " + driverType);
            }
        }

        /**
         * 校验 Volume Reference
         *
         * @param rowNum          行号
         * @param sheetName       工作表名称
         * @param volumeReference 值
         * @author lichen
         * @date 2026/3/6
         */
        private void validateVolumeReference(int rowNum, String sheetName, String volumeReference) {
            boolean isValid = false;
            for (SvaSettingVolumeReferenceEnum type : SvaSettingVolumeReferenceEnum.values()) {
                if (type.getValue().equals(volumeReference)) {
                    isValid = true;
                    break;
                }
            }
            if (!isValid) {
                throw new BadRequestException(CommonErrorI18nConstant.DATA_INVALID,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate("Sheet '" + sheetName + "' row " + rowNum + ": Volume Reference "),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Invalid Volume Reference in row " + rowNum + " sheet '" + sheetName + "': " + volumeReference);
            }
        }

        /**
         * 解析整数
         *
         * @param value      待解析的值
         * @param rowNum     行号
         * @param sheetName  表格名称
         * @param columnDesc 列描述
         * @return java.lang.Integer
         * @author lichen
         * @date 2026/3/6
         */
        private Integer parseInteger(String value, int rowNum, String sheetName, String columnDesc) {
            if (StringUtils.isBlank(value)) {
                return null;
            }
            try {
                return Integer.parseInt(value.trim());
            } catch (NumberFormatException e) {
                throw new BadRequestException(CommonErrorI18nConstant.MUST_INTEGER,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate("Sheet '" + sheetName + "' row " + rowNum + ": " + columnDesc),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Must be an Integer in row " + rowNum + " sheet '" + sheetName + "': " + columnDesc);
            }
        }

        /**
         * 解析数字
         *
         * @param value      待解析的值
         * @param rowNum     行号
         * @param sheetName  表格名称
         * @param columnDesc 列描述
         * @return java.math.BigDecimal
         * @author lichen
         * @date 2026/3/6
         */
        private BigDecimal parseNumber(String value, int rowNum, String sheetName, String columnDesc) {
            if (StringUtils.isBlank(value)) {
                return null;
            }
            try {
                String trimmedValue = value.trim();
                return new BigDecimal(trimmedValue);
            } catch (NumberFormatException e) {
                throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate("Sheet '" + sheetName + "' row " + rowNum + ": " + columnDesc),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Must be an number in row " + rowNum + " sheet '" + sheetName + "': " + columnDesc);
            }
        }

        /**
         * 去除 null 字符传
         *
         * @param value 值
         * @return java.lang.String
         * @author lichen
         * @date 2026/3/6
         */
        private String removeNull(String value) {
            if (StringUtils.isEmpty(value)) return null;

            if (value.equals("null")) return null;

            return value;
        }

        /**
         * 批量插入或更新
         *
         * @param settingsList 配置列表
         * @param propertyIds  资产id集合
         * @param year         年份
         * @author lichen
         * @date 2026/3/6
         */
        private void batchInsertOrUpdate(List<SvaProductivityRatioSettings> settingsList, List<Integer> propertyIds, int year) {
            // 删除该年份的旧数据
            remove(new LambdaQueryWrapper<SvaProductivityRatioSettings>()
                    .in(SvaProductivityRatioSettings::getPropertyId, propertyIds)
                    .eq(SvaProductivityRatioSettings::getYear, year));
            // 插入新数据
            saveBatch(settingsList);
        }
    }