# HotelKpiMedalliaResponseParseIhgService.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.Lists;
    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;

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

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

        /**
         * 解析 Medallia Response IHG 报告数据
         *
         * @param propertyDTO 资产
         * @param fileDate    文件下载时间戳
         * @param in          文件流
         * @param formula     公式
         * @return HotelKpiParseValueDTO
         * @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);

                // 过滤条件内容关键字
                String filterKeyWordStr = "Yes, you may contact me by phone or email";

                // 提取分值范围
                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);

                // 获取标题索引
                Row titleRow = sheet.getRow(2);
                int overallExperienceIndex = CellUtils.getCellIndexIgnoreCase(titleRow, "Overall Experience");
                int hotelFollowUpRequestedIndex = CellUtils.getCellIndexIgnoreCase(titleRow, "Hotel follow-up requested?");
                int courtesyContactPermissionIndex = CellUtils.getCellIndexIgnoreCase(titleRow, "Courtesy Contact Permission");
                int rapidResponseSentDateIndex = CellUtils.getCellIndexIgnoreCase(titleRow, "Rapid Response Sent Date");
                int reviewSurveyCompletedDateIndex = CellUtils.getCellIndexIgnoreCase(titleRow, "Review/Survey Completed Date");

                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;
                        }
                    }
                    // 跳过内容不匹配的数据
                    String hotelFollowUpRequestedStr = CellUtils.convertCellStr(row.getCell(hotelFollowUpRequestedIndex));
                    String courtesyContactPermissionStr = CellUtils.convertCellStr(row.getCell(courtesyContactPermissionIndex));
                    // 若内容至少有一个不为空时，按规则判断是否需要统计；都为空时，按需要回复来统计
                    if (StringUtils.isNotBlank(hotelFollowUpRequestedStr) || StringUtils.isNotBlank(courtesyContactPermissionStr)) {
                        boolean matched = Lists.newArrayList(hotelFollowUpRequestedStr, courtesyContactPermissionStr).stream()
                                .anyMatch(str -> StringUtils.equalsIgnoreCase(str, filterKeyWordStr));
                        // 跳过不符合规则的数据
                        if (!matched) {
                            continue;
                        }
                    }
                    // 分母 + 1
                    denominator = denominator.add(BigDecimal.ONE);

                    // 校验阈值时间
                    if (Objects.nonNull(responseTimeThreshold)) {
                        String completedDateStr = CellUtils.convertCellStr(row.getCell(reviewSurveyCompletedDateIndex),
                                BaseConstant.DATE_TIME_FORMAT_SLASH_YYYY_M_D_H_MM_SS);
                        if (StringUtils.isBlank(completedDateStr)) {
                            continue;
                        }
                        String rapidResponseSentDateStr = CellUtils.convertCellStr(row.getCell(rapidResponseSentDateIndex),
                                BaseConstant.DATE_TIME_FORMAT_SLASH_YYYY_M_D_H_MM_SS);

                        LocalDateTime rapidResponseSentDate = StringUtils.isBlank(rapidResponseSentDateStr) ?
                                DateTimeUtils.timestampToLocalDateTime(fileDate, propertyDTO.getTimezone()) :
                                DateTimeUtils.strToLocalDateTime(rapidResponseSentDateStr,
                                        BaseConstant.DATE_TIME_FORMAT_SLASH_YYYY_M_D_H_MM_SS);
                        LocalDateTime completedDate = DateTimeUtils.strToLocalDateTime(completedDateStr,
                                BaseConstant.DATE_TIME_FORMAT_SLASH_YYYY_M_D_H_MM_SS);
                        // 跳过超过阈值时间的数据
                        if (Duration.between(completedDate, rapidResponseSentDate).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 IHG 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]);
        }
    }