# HotelKpiMedalliaResponseParseGehService.java

    package cloud.wconnect.workperformance.service.hotelkpi.medalliaresponse;

    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.exception.InternalServerErrorException;
    import cloud.wconnect.commonlib.exception.ParseReportException;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.util.CellUtils;
    import cloud.wconnect.commonlib.util.DateTimeUtils;
    import cloud.wconnect.commonlib.util.InternationalizationUtils;
    import cloud.wconnect.workperformance.cons.hotelkpi.formula.HotelKpiFormulaParseCrawlCons;
    import cloud.wconnect.workperformance.pojo.dto.hotelkpi.originkpi.HotelKpiOriginCrawlValueIndexDTO;
    import cloud.wconnect.workperformance.pojo.dto.hotelkpi.originkpi.HotelKpiParseValueDTO;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Maps;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.springframework.stereotype.Component;

    import java.io.IOException;
    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.math.RoundingMode;
    import java.time.Duration;
    import java.time.LocalDateTime;
    import java.util.Collections;
    import java.util.List;
    import java.util.Map;
    import java.util.Objects;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;

    /**
     * @author zc
     * @date 2025/08/24
     */
    @Slf4j
    @Component
    @DS("work_performance")
    public class HotelKpiMedalliaResponseParseGehService implements IHotelKpiMedalliaResponseParseService {

        /**
         * 支持的报告来源
         *
         * @param formula 公式
         * @return boolean
         * @author zc
         * @date 2025/8/24
         */
        @Override
        public boolean supports(String formula) {
            return HotelKpiFormulaParseCrawlCons.MEDALLIA_RESPONSE_GEH.equals(formula.split(",")[0]);
        }

        /**
         * 解析 Medallia Response 报告数据
         *
         * @param propertyDTO 资产
         * @param fileDate    文件下载时间戳
         * @param in          文件流
         * @param formula     公式
         * @author zc
         * @date 2025/8/24
         */
        @Override
        public HotelKpiParseValueDTO parse(PropertyDTO propertyDTO, long fileDate, InputStream in, String formula) throws ParseReportException {
            try (Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);

                // 提取分值范围
                Map<Integer, BigDecimal> scoreMap = extractScoreMap(formula);
                BigDecimal minScore = scoreMap.get(1);
                BigDecimal maxScore = scoreMap.get(2);
                boolean validScore = Objects.nonNull(minScore) && Objects.nonNull(maxScore);

                // 提取响应时间阈值
                Long responseTimeThreshold = extractThreshold(formula);

                // 提取日期字段
                String parseDateStr = extractParseDateStr(formula);
                if (StringUtils.isBlank(parseDateStr)) {
                    parseDateStr = "Reply Sent";
                }

                // 获取标题索引
                Row titleRow = sheet.getRow(2);
                int feedbackDateIndex = CellUtils.getCellIndexIgnoreCase(titleRow, "Feedback Date");
                int overallExperienceIndex = CellUtils.getCellIndexIgnoreCase(titleRow, "Overall Experience");
                int allLogNotesCombinedIfAnyIndex = CellUtils.getCellIndexIgnoreCase(titleRow, "All log notes combined (if any)");

                // 正则表达式，匹配指定字段及其前面的日期
                Pattern parseDatePattern = Pattern.compile("\\[(\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2})] " + parseDateStr);

                int lastRowNum = sheet.getLastRowNum();

                // 分母
                BigDecimal denominator = BigDecimal.ZERO;
                // 分子
                BigDecimal numerator = BigDecimal.ZERO;
                for (int rowNum = 3; rowNum <= lastRowNum; rowNum++) {
                    Row row = sheet.getRow(rowNum);

                    // 校验分值
                    if (validScore) {
                        String overallExperienceStr = CellUtils.convertCellStr(row.getCell(overallExperienceIndex));
                        if (StringUtils.isBlank(overallExperienceStr)) {
                            continue;
                        }
                        BigDecimal overallScore = new BigDecimal(overallExperienceStr);
                        if (overallScore.compareTo(minScore) < 0 || overallScore.compareTo(maxScore) > 0) {
                            continue;
                        }
                    }
                    // 分母 + 1
                    denominator = denominator.add(BigDecimal.ONE);

                    // 校验阈值时间
                    if (Objects.nonNull(responseTimeThreshold)) {
                        String feedbackDateStr = CellUtils.convertCellStr(row.getCell(feedbackDateIndex),
                                BaseConstant.DATE_TIME_FORMAT_SLASH_YYYY_M_D_H_MM_SS);
                        if (StringUtils.isBlank(feedbackDateStr)) {
                            continue;
                        }
                        String combinedStr = CellUtils.convertCellStr(row.getCell(allLogNotesCombinedIfAnyIndex));
                        Matcher matcher = parseDatePattern.matcher(combinedStr);
                        // 跳过超过阈值时间的数据
                        LocalDateTime parseDate = matcher.find() ? DateTimeUtils.strToLocalDateTime(matcher.group(1),
                                BaseConstant.DATE_TIME_FORMAT_YYYY_MM_DD_HH_MM_SS) :
                                DateTimeUtils.timestampToLocalDateTime(fileDate, propertyDTO.getTimezone());
                        LocalDateTime feedbackDate = DateTimeUtils.strToLocalDateTime(feedbackDateStr,
                                BaseConstant.DATE_TIME_FORMAT_SLASH_YYYY_M_D_H_MM_SS);
                        if (Duration.between(feedbackDate, parseDate).toSeconds() > responseTimeThreshold) {
                            continue;
                        }
                    }
                    // 分子 + 1
                    numerator = numerator.add(BigDecimal.ONE);
                }
                // 获取国际化配置
                RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(propertyDTO
                        .getInternationalization().getNumber().getCalculationRule());

                BigDecimal value = (denominator.compareTo(BigDecimal.ZERO) == 0) ? BigDecimal.ZERO :
                        numerator.multiply(new BigDecimal("100")).divide(denominator, BaseConstant.DATA_PRECISION, roundingMode);

                return HotelKpiParseValueDTO.builder()
                        // 无法计算或计算结果等于 0 时，value 设置为 null
                        .value(value.compareTo(BigDecimal.ZERO) == 0 ? null : value)
                        .originValues(List.of(HotelKpiOriginCrawlValueIndexDTO.builder().index(1).value(numerator).build(),
                                HotelKpiOriginCrawlValueIndexDTO.builder().index(2).value(denominator).build()))
                        .build();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle parse Hotel KPI Medallia Response APA report error.", e);
            }
        }

        /**
         * 提取分值
         *
         * @param formulaStr 公式
         * @return 分值
         * @author zc
         * @date 2025/8/24
         */
        private Map<Integer, BigDecimal> extractScoreMap(String formulaStr) {
            String[] formulaParams = formulaStr.split(",");
            if (formulaParams.length < 3) {
                return Collections.emptyMap();
            }
            // 提取分值范围
            String[] scoreRange = formulaParams[1].split("-");
            if (scoreRange.length != 2) {
                return Collections.emptyMap();
            }
            Map<Integer, BigDecimal> scoreMap = Maps.newHashMap();
            scoreMap.put(1, new BigDecimal(scoreRange[0]));
            scoreMap.put(2, new BigDecimal(scoreRange[1]));
            return scoreMap;
        }

        /**
         * 提取阈值
         *
         * @param formulaStr 公式
         * @return java.lang.Long
         * @author zc
         * @date 2025/8/24
         */
        private Long extractThreshold(String formulaStr) {
            String[] formulaParams = formulaStr.split(",");
            if (formulaParams.length < 3) {
                return null;
            }
            return Long.parseLong(formulaParams[2]);
        }

        /**
         * 提取解析日期字段
         *
         * @param formulaStr 公式
         * @return 日期字段
         * @author zc
         * @date 2025/8/24
         */
        private String extractParseDateStr(String formulaStr) {
            String[] formulaParams = formulaStr.split(",");
            if (formulaParams.length < 4) {
                return null;
            }
            return formulaParams[3];
        }
    }