# HotelKpiQuoreReportServiceImpl.java

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

    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.util.*;
    import cloud.wconnect.workperformance.cons.S3BucketFunctionCons;
    import cloud.wconnect.workperformance.cons.hotelkpi.HotelKpiMetricsEnum;
    import cloud.wconnect.workperformance.entity.hotelkpi.*;
    import cloud.wconnect.workperformance.pojo.dto.hotelkpi.originkpi.*;
    import cloud.wconnect.workperformance.service.AmazonS3Service;
    import cloud.wconnect.workperformance.service.hotelkpi.*;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.fasterxml.jackson.databind.ObjectMapper;
    import com.google.common.collect.Lists;
    import com.google.common.collect.Maps;
    import jakarta.annotation.Resource;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.collections.CollectionUtils;
    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.Service;
    import org.springframework.transaction.annotation.Transactional;

    import java.io.IOException;
    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.math.RoundingMode;
    import java.time.DayOfWeek;
    import java.time.LocalDate;
    import java.time.YearMonth;
    import java.time.temporal.TemporalAdjusters;
    import java.util.List;
    import java.util.Map;
    import java.util.Objects;

    /**
     * <p>
     * Hotel KPI Quore Report 服务实现类
     * </p>
     *
     * @author zc
     * @since 2024-07-16
     */
    @Slf4j
    @Service
    @DS("work_performance")
    public class HotelKpiQuoreReportServiceImpl implements IHotelKpiQuoreReportService {
        @Resource
        private IHotelKpiService hotelKpiService;
        @Resource
        private IHotelKpiWeeklyService weeklyService;
        @Resource
        private IHotelKpiMonthlyService monthlyService;
        @Resource
        private IHotelKpiQuarterlyService quarterlyService;
        @Resource
        private IHotelKpiYearlyService yearlyService;
        @Resource
        private IHotelKpiMetricsConfigService metricsConfigService;
        @Resource
        private IHotelKpiWeekStartDayService weekStartDayService;
        @Resource
        private AmazonS3Service amazonS3Service;
        @Resource
        private ObjectMapper objectMapper;

        private static final String NO_DATA_FOUND = "No Data Found";

        /**
         * 解析 Quore Complaint 报告数据
         *
         * @param propertyDTO 资产
         * @param in          文件流
         * @param fileUrl     文件地址
         * @param metricsType 指标类型
         * @author zc
         * @date 2024/7/16
         */
        @Override
        @Transactional(rollbackFor = Exception.class)
        public void handleQuoreReport(PropertyDTO propertyDTO, InputStream in, String fileUrl, int metricsType) throws IOException {
            HotelKpiMetricsConfig metricsConfig = metricsConfigService.getMetricsConfigByTypeForUpdate(propertyDTO.getId(), metricsType);
            if (Objects.isNull(metricsConfig)) {
                return;
            }

            // 获取解析报告数据
            HotelKpiParseValueDTO parseValueDTO = getParseValueDTO(propertyDTO, in, metricsType);
            if (Objects.isNull(parseValueDTO)) {
                return;
            }

            // 上传文件到 S3
            uploadReport(fileUrl);
            // 组装文件路径参数
            parseValueDTO.setFileUrl(fileUrl);

            LocalDate startLocalDate = DateTimeUtils.strToLocalDate(parseValueDTO.getStartDate().toString(), BaseConstant.DATE_FORMAT_YYYYMMDD);
            LocalDate endLocalDate = DateTimeUtils.strToLocalDate(parseValueDTO.getEndDate().toString(), BaseConstant.DATE_FORMAT_YYYYMMDD);

            // 保存指标数据
            saveWeeklyList(propertyDTO, metricsConfig, parseValueDTO, startLocalDate, endLocalDate);
            saveMonthlyList(propertyDTO, metricsConfig, parseValueDTO, startLocalDate, endLocalDate);
            saveQuarterlyList(propertyDTO, metricsConfig, parseValueDTO, startLocalDate, endLocalDate);
            saveYearlyList(propertyDTO, metricsConfig, parseValueDTO, startLocalDate, endLocalDate);
        }

        /**
         * 上传报告，用于点击指标下载报告
         *
         * @param fileUrl S3 路径
         * @author zc
         * @date 2024/9/27
         */
        private void uploadReport(String fileUrl) {
            try (InputStream in = amazonS3Service.getFile(fileUrl)) {
                amazonS3Service.uploadFile(S3BucketFunctionCons.HOTEL_KPI, in, fileUrl);
            } catch (IOException e) {
                log.error("HotelKpiQuoreReportServiceImpl Upload report error. fileUrl: {}", fileUrl, e);
            }
        }

        /**
         * 保存周数据
         *
         * @param propertyDTO    资产
         * @param metricsConfig  指标配置
         * @param parseValueDTO  解析的原始数据
         * @param startLocalDate 开始日期
         * @param endLocalDate   结束日期
         * @author zc
         * @date 2024/12/17
         */
        private void saveWeeklyList(PropertyDTO propertyDTO, HotelKpiMetricsConfig metricsConfig, HotelKpiParseValueDTO parseValueDTO,
                                    LocalDate startLocalDate, LocalDate endLocalDate) {
            boolean isWeekly = checkDateRangeIsWeekly(startLocalDate, endLocalDate);
            if (!isWeekly) {
                return;
            }

            // 转换为周的开始日期
            DayOfWeek dayOfWeek = weekStartDayService.getWeekStartDay(propertyDTO.getId());
            LocalDate weekStartDate = endLocalDate.with(TemporalAdjusters.previousOrSame(dayOfWeek));
            long date = Long.parseLong(DateTimeUtils.localDateToStr(weekStartDate, BaseConstant.DATE_FORMAT_YYYYMMDD));

            // 获取原始数据
            HotelKpiWeekly originKpiWeekly = weeklyService.getByDateAndMetricsType(propertyDTO.getId(),
                    date, metricsConfig.getMetricsType());
            if (Objects.isNull(originKpiWeekly)) {
                originKpiWeekly = new HotelKpiWeekly();
            }

            HotelKpiWeekly hotelKpiWeekly = HotelKpiWeekly.builder()
                    .propertyId(propertyDTO.getId())
                    .week(date)
                    .metricsType(metricsConfig.getMetricsType())
                    .detail(packageOriginDetail(metricsConfig, parseValueDTO, originKpiWeekly.getDetail()))
                    .build();
            weeklyService.insertOrUpdate(hotelKpiWeekly);
        }

        /**
         * 保存月数据
         *
         * @param propertyDTO    资产
         * @param metricsConfig  指标配置
         * @param parseValueDTO  解析的原始数据
         * @param startLocalDate 开始日期
         * @param endLocalDate   结束日期
         * @author zc
         * @date 2024/12/17
         */
        private void saveMonthlyList(PropertyDTO propertyDTO, HotelKpiMetricsConfig metricsConfig, HotelKpiParseValueDTO parseValueDTO,
                                     LocalDate startLocalDate, LocalDate endLocalDate) {
            boolean isMonthly = checkDateRangeIsMonthly(startLocalDate, endLocalDate);
            if (!isMonthly) {
                return;
            }

            // 转换为年月
            long date = Long.parseLong(DateTimeUtils.localDateToStr(startLocalDate, BaseConstant.DATE_FORMAT_YYYYMM));

            // 获取原始数据
            HotelKpiMonthly originKpiMonthly = monthlyService.getByDateAndMetricsType(propertyDTO.getId(),
                    date, metricsConfig.getMetricsType());
            if (Objects.isNull(originKpiMonthly)) {
                originKpiMonthly = new HotelKpiMonthly();
            }

            HotelKpiMonthly hotelKpiMonthly = HotelKpiMonthly.builder()
                    .propertyId(propertyDTO.getId())
                    .month(date)
                    .metricsType(metricsConfig.getMetricsType())
                    .detail(packageOriginDetail(metricsConfig, parseValueDTO, originKpiMonthly.getDetail()))
                    .build();
            monthlyService.insertOrUpdate(hotelKpiMonthly);

            List<Integer> effectiveMetricsTypeList = List.of(HotelKpiMetricsEnum.FW_INSPECTION_GR_PM.getMetricsType(),
                    HotelKpiMetricsEnum.FW_INSPECTION_HOTEL.getMetricsType());
            if (effectiveMetricsTypeList.contains(metricsConfig.getMetricsType())) {
                // 保存季度的月数据平均值
                saveSummaryAvgMetricsForQuarterly(propertyDTO, metricsConfig, startLocalDate);

                // 保存全年的月数据平均值
                saveSummaryAvgMetricsForYearly(propertyDTO, metricsConfig, startLocalDate);
            }
        }

        /**
         * 保存季数据
         *
         * @param propertyDTO    资产
         * @param metricsConfig  指标配置
         * @param parseValueDTO  解析的原始数据
         * @param startLocalDate 开始日期
         * @param endLocalDate   结束日期
         * @author zc
         * @date 2024/12/17
         */
        private void saveQuarterlyList(PropertyDTO propertyDTO, HotelKpiMetricsConfig metricsConfig, HotelKpiParseValueDTO parseValueDTO,
                                       LocalDate startLocalDate, LocalDate endLocalDate) {
            boolean isQuarterly = checkDateRangeIsQuarterly(startLocalDate, endLocalDate);
            if (!isQuarterly) {
                return;
            }

            // 转换为季的开始日期
            long date = Long.parseLong(DateTimeUtils.localDateToStr(startLocalDate, BaseConstant.DATE_FORMAT_YYYYMM));

            // 获取原始数据
            HotelKpiQuarterly originKpiQuarterly = quarterlyService.getByDateAndMetricsType(propertyDTO.getId(),
                    date, metricsConfig.getMetricsType());
            if (Objects.isNull(originKpiQuarterly)) {
                originKpiQuarterly = new HotelKpiQuarterly();
            }

            HotelKpiQuarterly hotelKpiQuarterly = HotelKpiQuarterly.builder()
                    .propertyId(propertyDTO.getId())
                    .month(date)
                    .metricsType(metricsConfig.getMetricsType())
                    .detail(packageOriginDetail(metricsConfig, parseValueDTO, originKpiQuarterly.getDetail()))
                    .build();
            quarterlyService.insertOrUpdate(hotelKpiQuarterly);
        }

        /**
         * 保存年数据
         *
         * @param propertyDTO    资产
         * @param metricsConfig  指标配置
         * @param parseValueDTO  解析的原始数据
         * @param startLocalDate 开始日期
         * @param endLocalDate   结束日期
         * @author zc
         * @date 2024/12/17
         */
        private void saveYearlyList(PropertyDTO propertyDTO, HotelKpiMetricsConfig metricsConfig, HotelKpiParseValueDTO parseValueDTO,
                                    LocalDate startLocalDate, LocalDate endLocalDate) {
            boolean isYearly = checkDateRangeIsYearly(startLocalDate, endLocalDate);
            if (!isYearly) {
                return;
            }

            // 转换为年
            int date = startLocalDate.getYear();

            // 获取原始数据
            HotelKpiYearly originKpiYearly = yearlyService.getByDateAndMetricsType(propertyDTO.getId(),
                    date, metricsConfig.getMetricsType());
            if (Objects.isNull(originKpiYearly)) {
                originKpiYearly = new HotelKpiYearly();
            }

            HotelKpiYearly hotelKpiYearly = HotelKpiYearly.builder()
                    .propertyId(propertyDTO.getId())
                    .year((long) date)
                    .metricsType(metricsConfig.getMetricsType())
                    .detail(packageOriginDetail(metricsConfig, parseValueDTO, originKpiYearly.getDetail()))
                    .build();
            yearlyService.insertOrUpdate(hotelKpiYearly);
        }

        /**
         * 校验日期范围是否归属于周
         *
         * @param startLocalDate 开始日期
         * @param endLocalDate   结束日期
         * @return boolean true: 是，false: 否
         * @author zc
         * @date 2024/12/17
         */
        private boolean checkDateRangeIsWeekly(LocalDate startLocalDate, LocalDate endLocalDate) {
            // 校验两个日期是否属于同一年月
            if (!YearMonth.from(startLocalDate).equals(YearMonth.from(endLocalDate))) {
                return false;
            }
            // 校验开始日期是否是月初
            if (!startLocalDate.isEqual(startLocalDate.withDayOfMonth(1))) {
                return false;
            }
            // 校验日期相差天数是否有效（最大：31 - 1 = 30）
            return DateTimeUtils.numberOfDays(startLocalDate, endLocalDate) <= 30;
        }

        /**
         * 校验日期范围是否归属于月
         *
         * @param startLocalDate 开始日期
         * @param endLocalDate   结束日期
         * @return boolean true: 是，false: 否
         * @author zc
         * @date 2024/12/17
         */
        private boolean checkDateRangeIsMonthly(LocalDate startLocalDate, LocalDate endLocalDate) {
            // 校验两个日期是否属于同一年月
            if (!YearMonth.from(startLocalDate).equals(YearMonth.from(endLocalDate))) {
                return false;
            }
            // 校验开始和结束日期是否分别是月初和月末
            return startLocalDate.isEqual(startLocalDate.withDayOfMonth(1)) &&
                    endLocalDate.isEqual(endLocalDate.withDayOfMonth(endLocalDate.lengthOfMonth()));
        }

        /**
         * 校验日期范围是否归属于季
         *
         * @param startLocalDate 开始日期
         * @param endLocalDate   结束日期
         * @return boolean true: 是，false: 否
         * @author zc
         * @date 2024/12/17
         */
        private boolean checkDateRangeIsQuarterly(LocalDate startLocalDate, LocalDate endLocalDate) {
            // 校验开始日期的月份，若不在 [1, 4, 7, 10] 之中，则直接返回
            if (!Lists.newArrayList(1, 4, 7, 10).contains(startLocalDate.getMonthValue())) {
                return false;
            }
            // 校验结束日期是否大于开始日期两个月
            if (!YearMonth.from(startLocalDate).plusMonths(2).equals(YearMonth.from(endLocalDate))) {
                return false;
            }
            // 校验开始和结束日期是否分别是月初和月末
            return startLocalDate.isEqual(startLocalDate.withDayOfMonth(1)) &&
                    endLocalDate.isEqual(endLocalDate.withDayOfMonth(endLocalDate.lengthOfMonth()));
        }

        /**
         * 校验日期范围是否归属于年
         *
         * @param startLocalDate 开始日期
         * @param endLocalDate   结束日期
         * @return boolean true: 是，false: 否
         * @author zc
         * @date 2024/12/17
         */
        private boolean checkDateRangeIsYearly(LocalDate startLocalDate, LocalDate endLocalDate) {
            // 校验两个日期是否属于同一年
            if (startLocalDate.getYear() != endLocalDate.getYear()) {
                return false;
            }
            // 校验开始和结束日期是否分别是年初和月末
            return startLocalDate.isEqual(startLocalDate.withDayOfYear(1)) &&
                    endLocalDate.isEqual(endLocalDate.with(TemporalAdjusters.lastDayOfMonth()));
        }

        /**
         * 保存保存月平均值为季数据
         *
         * @param propertyDTO   资产
         * @param metricsConfig 指标配置
         * @param localDate     日期
         * @author zc
         * @date 2024/12/17
         */
        private void saveSummaryAvgMetricsForQuarterly(PropertyDTO propertyDTO, HotelKpiMetricsConfig metricsConfig, LocalDate localDate) {
            YearMonth yearMonth = YearMonth.of(localDate.getYear(), localDate.getMonth().firstMonthOfQuarter());
            long startQuarterDate = YearMonthUtils.parseYearMonthToLong(yearMonth);
            long endQuarterDate = YearMonthUtils.parseYearMonthToLong(yearMonth.plusMonths(2));

            // 获取原始数据
            HotelKpiQuarterly originKpiQuarterly = quarterlyService.getByDateAndMetricsType(propertyDTO.getId(),
                    startQuarterDate, metricsConfig.getMetricsType());
            if (Objects.isNull(originKpiQuarterly)) {
                originKpiQuarterly = new HotelKpiQuarterly();
            }

            // 获取季度范围的月数据
            List<HotelKpiMonthly> originKpiMonthlyList = monthlyService.listByDateRangeAndMetricsType(propertyDTO.getId(),
                    startQuarterDate, endQuarterDate, metricsConfig.getMetricsType());
            HotelKpiParseValueDTO parseValueDTO = getParseValueDTOAvg(propertyDTO, originKpiMonthlyList);

            HotelKpiQuarterly hotelKpiQuarterly = HotelKpiQuarterly.builder()
                    .propertyId(propertyDTO.getId())
                    .month(startQuarterDate)
                    .metricsType(metricsConfig.getMetricsType())
                    .detail(packageOriginDetail(metricsConfig, parseValueDTO, originKpiQuarterly.getDetail()))
                    .build();
            quarterlyService.insertOrUpdate(hotelKpiQuarterly);
        }

        /**
         * 保存月平均值为年数据
         *
         * @param propertyDTO   资产
         * @param metricsConfig 指标配置
         * @param localDate     日期
         * @author zc
         * @date 2024/12/17
         */
        private void saveSummaryAvgMetricsForYearly(PropertyDTO propertyDTO, HotelKpiMetricsConfig metricsConfig, LocalDate localDate) {
            // 转换为年
            int date = localDate.getYear();
            long startYearDate = YearMonthUtils.parseYearMonthToLong(YearMonth.of(date, 1));
            long endYearDate = YearMonthUtils.parseYearMonthToLong(YearMonth.of(date, 12));

            // 获取原始数据
            HotelKpiYearly originKpiYearly = yearlyService.getByDateAndMetricsType(propertyDTO.getId(),
                    date, metricsConfig.getMetricsType());
            if (Objects.isNull(originKpiYearly)) {
                originKpiYearly = new HotelKpiYearly();
            }

            // 获取全年范围的月数据
            List<HotelKpiMonthly> originKpiMonthlyList = monthlyService.listByDateRangeAndMetricsType(propertyDTO.getId(),
                    startYearDate, endYearDate, metricsConfig.getMetricsType());
            HotelKpiParseValueDTO parseValueDTO = getParseValueDTOAvg(propertyDTO, originKpiMonthlyList);

            HotelKpiYearly hotelKpiYearly = HotelKpiYearly.builder()
                    .propertyId(propertyDTO.getId())
                    .year((long) date)
                    .metricsType(metricsConfig.getMetricsType())
                    .detail(packageOriginDetail(metricsConfig, parseValueDTO, originKpiYearly.getDetail()))
                    .build();
            yearlyService.insertOrUpdate(hotelKpiYearly);
        }

        /**
         * 获取数据平均值
         *
         * @param propertyDTO    资产
         * @param kpiMonthlyList 月数据
         * @return HotelKpiParseValueDTO
         * @author zc
         * @date 2024/12/17
         */
        private HotelKpiParseValueDTO getParseValueDTOAvg(PropertyDTO propertyDTO, List<HotelKpiMonthly> kpiMonthlyList) {
            int index = 1;
            BigDecimal totalValue = BigDecimal.ZERO;
            int size = 0;
            for (HotelKpiMonthly originKpiMonthly : kpiMonthlyList) {
                // 组装原始指标数据DTO
                HotelKpiOriginDetailDTO originDetailDTO = Objects.isNull(originKpiMonthly.getDetail()) ? new HotelKpiOriginDetailDTO() :
                        JsonUtils.convertStringToObject(objectMapper, originKpiMonthly.getDetail(), HotelKpiOriginDetailDTO.class);

                BigDecimal actualFactorCrawlValue = originDetailDTO.getActual().getCalculationFactor().stream()
                        .filter(item -> item.getIndex() == index).findFirst().orElse(new HotelKpiOriginCalculationFactorDTO()).getCrawl();
                if (Objects.nonNull(actualFactorCrawlValue)) {
                    totalValue = totalValue.add(actualFactorCrawlValue);
                    size++;
                }
            }

            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(propertyDTO
                    .getInternationalization().getNumber().getCalculationRule());

            BigDecimal value = size == 0 ? null : totalValue.divide(new BigDecimal(String.valueOf(size)), BaseConstant.DATA_PRECISION, roundingMode);
            return HotelKpiParseValueDTO.builder()
                    .value(value)
                    .originValues(List.of(HotelKpiOriginCrawlValueIndexDTO.builder().index(index).value(value).build()))
                    .build();
        }

        /**
         * 组装原始指标数据DTO
         *
         * @param metricsConfig 指标配置
         * @param parseValueDTO 解析的原始数据
         * @param originDetail  原始数据详情
         * @return 数据详情
         * @author zc
         * @date 2024/12/17
         */
        private String packageOriginDetail(HotelKpiMetricsConfig metricsConfig, HotelKpiParseValueDTO parseValueDTO, String originDetail) {
            HotelKpiOriginDetailDTO originDetailDTO = Objects.isNull(originDetail) ? new HotelKpiOriginDetailDTO() :
                    JsonUtils.convertStringToObject(objectMapper, originDetail, HotelKpiOriginDetailDTO.class);

            HotelKpiOriginValueDTO actual = originDetailDTO.getActual();
            // 组装 Actual 文件路径
            actual.setFileUrl(parseValueDTO.getFileUrl());

            List<Integer> ignoreMetricsTypeList = List.of(HotelKpiMetricsEnum.QUORE_DEEP_CLEAN.getMetricsType(),
                    HotelKpiMetricsEnum.QUORE_GR_PM.getMetricsType(),
                    HotelKpiMetricsEnum.QUORE_CLEANINGS_CHECKOUT_INSPECTED.getMetricsType());
            if (ignoreMetricsTypeList.contains(metricsConfig.getMetricsType())) {
                return JsonUtils.convertObjToString(objectMapper, originDetailDTO);
            }

            HotelKpiOriginCalculationFactorDTO newFactorDTO = new HotelKpiOriginCalculationFactorDTO().setIndex(1).setCrawl(parseValueDTO.getValue())
                    .setCrawlOrigin(parseValueDTO.getOriginValues());

            // 查找并替换或新增新的指标计算因子原始数据
            hotelKpiService.replaceOriginFactorList(actual.getCalculationFactor(), newFactorDTO);
            return JsonUtils.convertObjToString(objectMapper, originDetailDTO);
        }

        /**
         * 转换字符串为 Map
         *
         * @param str 字符串内容
         * @return Map<String, String>
         * @author zc
         * @date 2024/7/30
         */
        private Map<String, String> covertStringToMap(String str) {
            Map<String, String> map = Maps.newHashMap();
            String[] strSplit = str.split("\\|");
            for (String s : strSplit) {
                String[] sSplit = s.split(":");
                if (sSplit.length != 2) {
                    continue;
                }
                map.put(sSplit[0].trim().toLowerCase(), sSplit[1].trim());
            }
            return map;
        }

        /**
         * 获取解析报告数据
         *
         * @param propertyDTO 资产
         * @param in          输入流
         * @param metricsType 指标类型
         * @return HotelKpiParseValueDTO
         * @author zc
         * @date 2024/12/17
         */
        private HotelKpiParseValueDTO getParseValueDTO(PropertyDTO propertyDTO, InputStream in, int metricsType) throws IOException {
            if (metricsType == HotelKpiMetricsEnum.QUORE_COMPLAINT.getMetricsType()) {
                return parseQuoreComplaint(in);
            } else if (metricsType == HotelKpiMetricsEnum.QUORE_CLEANINGS_CHECKOUT_INSPECTED.getMetricsType()) {
                return parseQuoreCleaningsCheckoutInspected(in);
            } else if (metricsType == HotelKpiMetricsEnum.QUORE_DEEP_CLEAN.getMetricsType()) {
                return parseQuoreDeepClean(in);
            } else if (metricsType == HotelKpiMetricsEnum.QUORE_GR_PM.getMetricsType()) {
                return parseQuoreGuestRoomPM(in);
            } else if (metricsType == HotelKpiMetricsEnum.FW_INSPECTION_GR_PM.getMetricsType()) {
                return parseQuoreFWGuestRoomPM(propertyDTO, in);
            } else if (metricsType == HotelKpiMetricsEnum.FW_INSPECTION_HOTEL.getMetricsType()) {
                return parseQuoreFWHotel(propertyDTO, in);
            } else {
                return null;
            }
        }

        /**
         * 解析 Quore Complaint 报告数据
         *
         * @param in 文件流
         * @return HotelKpiParseValueDTO
         * @author zc
         * @date 2024/7/13
         */
        private HotelKpiParseValueDTO parseQuoreComplaint(InputStream in) throws IOException {
            HotelKpiParseValueDTO parseValueDTO = new HotelKpiParseValueDTO();

            try (Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);

                // 获取日期范围
                Row dateRow = sheet.getRow(1);
                String dateStr = CellUtils.convertCellStr(dateRow.getCell(0));
                Map<String, String> title2DateStr = covertStringToMap(dateStr);

                String fromDateStr = title2DateStr.get("start date");
                LocalDate startLocalDate = DateTimeUtils.strToLocalDate(fromDateStr, BaseConstant.DATE_FORMAT_MM_DD_YYYY);
                long startDate = Long.parseLong(DateTimeUtils.localDateToStr(startLocalDate, BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setStartDate(startDate);

                String toDateStr = title2DateStr.get("end date");
                LocalDate endLocalDate = DateTimeUtils.strToLocalDate(toDateStr, BaseConstant.DATE_FORMAT_MM_DD_YYYY);
                long endDate = Long.parseLong(DateTimeUtils.localDateToStr(endLocalDate, BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setEndDate(endDate);

                int count = 0;
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum >= 3) {
                    // 获取内容，校验是否等于 No Data Found
                    Row rowData = sheet.getRow(3);
                    String content = CellUtils.convertCellStr(rowData.getCell(0));
                    if (!NO_DATA_FOUND.equalsIgnoreCase(content)) {
                        count = lastRowNum - 2;
                    }
                }

                BigDecimal value = new BigDecimal(String.valueOf(count));
                parseValueDTO.setValue(value);
                parseValueDTO.setOriginValues(List.of(HotelKpiOriginCrawlValueIndexDTO.builder()
                        .index(1).value(value).build()));
                return parseValueDTO;
            }
        }

        /**
         * 解析 Quore Cleanings Checkout Inspected 报告数据
         *
         * @param in 文件流
         * @return HotelKpiParseValueDTO
         * @author zc
         * @date 2024/7/13
         */
        private HotelKpiParseValueDTO parseQuoreCleaningsCheckoutInspected(InputStream in) throws IOException {
            HotelKpiParseValueDTO parseValueDTO = new HotelKpiParseValueDTO();

            try (Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);

                // 获取日期范围
                Row dateRow = sheet.getRow(1);
                String dateStr = CellUtils.convertCellStr(dateRow.getCell(0));
                Map<String, String> title2DateStr = covertStringToMap(dateStr);

                String fromDateStr = title2DateStr.get("from");
                long startDate = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(fromDateStr,
                        BaseConstant.DATE_FORMAT_MM_DD_YYYY), BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setStartDate(startDate);

                String toDateStr = title2DateStr.get("to");
                long endDate = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(toDateStr,
                        BaseConstant.DATE_FORMAT_MM_DD_YYYY), BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setEndDate(endDate);

                // 获取标题索引
                Row titleRow = sheet.getRow(3);
                int totalInspectionsIndex = CellUtils.getCellIndex(titleRow, "Total Inspections");

                int total = 0;

                // 获取内容，校验是否等于 No Data Found
                Row rowData = sheet.getRow(4);
                if (Objects.nonNull(rowData)) {
                    String content = CellUtils.convertCellStr(rowData.getCell(0));
                    if (!NO_DATA_FOUND.equalsIgnoreCase(content)) {
                        int rowIndex = 4;
                        while (true) {
                            Row row = sheet.getRow(rowIndex);
                            if (Objects.isNull(row)) {
                                break;
                            }
                            rowIndex++;

                            String totalInspectionsStr = CellUtils.convertCellStr(row.getCell(totalInspectionsIndex));
                            if (StringUtils.isBlank(totalInspectionsStr)) {
                                continue;
                            }
                            total += Integer.parseInt(totalInspectionsStr);
                        }
                    }
                }

                BigDecimal value = new BigDecimal(String.valueOf(total));
                parseValueDTO.setOriginValues(List.of(HotelKpiOriginCrawlValueIndexDTO.builder()
                        .index(1).value(value).build()));
                parseValueDTO.setValue(value);
                return parseValueDTO;
            }
        }

        /**
         * 解析 Quore Deep Clean 报告数据
         *
         * @param in 文件流
         * @return HotelKpiParseValueDTO
         * @author zc
         * @date 2024/7/13
         */
        private HotelKpiParseValueDTO parseQuoreDeepClean(InputStream in) throws IOException {
            HotelKpiParseValueDTO parseValueDTO = new HotelKpiParseValueDTO();

            try (Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);

                // 获取日期范围
                Row dateRow = sheet.getRow(1);
                String dateStr = CellUtils.convertCellStr(dateRow.getCell(0));
                Map<String, String> title2DateStr = covertStringToMap(dateStr);

                String fromDateStr = title2DateStr.get("from");
                long startDate = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(fromDateStr,
                        BaseConstant.DATE_FORMAT_MM_DD_YYYY), BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setStartDate(startDate);

                String toDateStr = title2DateStr.get("to");
                long endDate = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(toDateStr,
                        BaseConstant.DATE_FORMAT_MM_DD_YYYY), BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setEndDate(endDate);

                // 获取标题索引
                Row titleRow = sheet.getRow(3);
                int totalInspectionsIndex = CellUtils.getCellIndex(titleRow, "Total Inspections");

                int total = 0;

                // 获取内容，校验是否等于 No Data Found
                Row rowData = sheet.getRow(4);
                if (Objects.nonNull(rowData)) {
                    String content = CellUtils.convertCellStr(rowData.getCell(0));
                    if (!NO_DATA_FOUND.equalsIgnoreCase(content)) {
                        int rowIndex = 4;
                        while (true) {
                            Row row = sheet.getRow(rowIndex);
                            if (Objects.isNull(row)) {
                                break;
                            }
                            rowIndex++;

                            String totalInspectionsStr = CellUtils.convertCellStr(row.getCell(totalInspectionsIndex));
                            if (StringUtils.isBlank(totalInspectionsStr)) {
                                continue;
                            }
                            total += Integer.parseInt(totalInspectionsStr);
                        }
                    }
                }

                BigDecimal value = new BigDecimal(String.valueOf(total));
                parseValueDTO.setValue(value);
                parseValueDTO.setOriginValues(List.of(HotelKpiOriginCrawlValueIndexDTO.builder()
                        .index(1).value(value).build()));
                return parseValueDTO;
            }
        }

        /**
         * 解析 Quore Guest Room PM 报告数据
         *
         * @param in 文件流
         * @return HotelKpiParseValueDTO
         * @author zc
         * @date 2024/7/10
         */
        private HotelKpiParseValueDTO parseQuoreGuestRoomPM(InputStream in) throws IOException {
            HotelKpiParseValueDTO parseValueDTO = new HotelKpiParseValueDTO();

            try (Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);

                // 获取日期范围
                Row dateRow = sheet.getRow(1);
                String dateStr = CellUtils.convertCellStr(dateRow.getCell(0));
                Map<String, String> title2DateStr = covertStringToMap(dateStr);

                String fromDateStr = title2DateStr.get("completed between");
                long startDate = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(fromDateStr,
                        BaseConstant.DATE_FORMAT_MM_DD_YYYY), BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setStartDate(startDate);

                String toDateStr = title2DateStr.get("and");
                long endDate = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(toDateStr,
                        BaseConstant.DATE_FORMAT_MM_DD_YYYY), BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setEndDate(endDate);

                int completedRooms = 0;
                if (sheet.getLastRowNum() >= 3) {
                    // 获取内容，校验是否等于 No Data Found
                    Row rowData = sheet.getRow(3);
                    String content = CellUtils.convertCellStr(rowData.getCell(0));
                    if (!NO_DATA_FOUND.equalsIgnoreCase(content)) {
                        completedRooms = sheet.getLastRowNum() - 2;
                    }
                }

                BigDecimal value = new BigDecimal(String.valueOf(completedRooms));
                parseValueDTO.setValue(value);
                parseValueDTO.setOriginValues(List.of(HotelKpiOriginCrawlValueIndexDTO.builder()
                        .index(1).value(value).build()));
                return parseValueDTO;
            }
        }

        /**
         * 解析 Quore FW Guest Room PM 报告数据
         *
         * @param propertyDTO 资产
         * @param in          文件流
         * @return HotelKpiParseValueDTO
         * @author zc
         * @date 2024/7/13
         */
        private HotelKpiParseValueDTO parseQuoreFWGuestRoomPM(PropertyDTO propertyDTO, InputStream in) throws IOException {
            HotelKpiParseValueDTO parseValueDTO = new HotelKpiParseValueDTO();

            try (Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);

                // 获取日期范围
                Row dateRow = sheet.getRow(1);
                String dateStr = CellUtils.convertCellStr(dateRow.getCell(0));
                Map<String, String> title2DateStr = covertStringToMap(dateStr);

                String fromDateStr = title2DateStr.get("between");
                long startDate = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(fromDateStr,
                        BaseConstant.DATE_FORMAT_MM_DD_YYYY), BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setStartDate(startDate);

                String toDateStr = title2DateStr.get("and");
                long endDate = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(toDateStr,
                        BaseConstant.DATE_FORMAT_MM_DD_YYYY), BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setEndDate(endDate);

                // 获取标题索引
                Row titleRow = sheet.getRow(3);
                int scoreIndex = CellUtils.getCellIndex(titleRow, "Score");

                // 计算平均值
                List<BigDecimal> scoreList = Lists.newArrayList();
                // 从第 5 行开始获取数据
                int rowIndex = 4;
                Row row = sheet.getRow(rowIndex);
                String cellStr = CellUtils.convertCellStr(row.getCell(0));
                // 校验是否等于 No Data Found
                if (!NO_DATA_FOUND.equalsIgnoreCase(cellStr)) {
                    while (Objects.nonNull(row)) {
                        String scoreStr = CellUtils.convertCellStr(row.getCell(scoreIndex));
                        if (StringUtils.isBlank(scoreStr)) {
                            break;
                        }
                        scoreList.add(new BigDecimal(scoreStr.replace("%", "").trim()));

                        // 获取下一行的数据
                        row = sheet.getRow(++rowIndex);
                    }
                }

                BigDecimal avgScore = null;
                if (CollectionUtils.isNotEmpty(scoreList)) {
                    RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(propertyDTO
                            .getInternationalization().getNumber().getCalculationRule());

                    avgScore = scoreList.stream().reduce(BigDecimal.ZERO, BigDecimal::add)
                            .divide(new BigDecimal(scoreList.size()), BaseConstant.DATA_PRECISION, roundingMode);
                }
                parseValueDTO.setValue(avgScore);
                parseValueDTO.setOriginValues(List.of(HotelKpiOriginCrawlValueIndexDTO.builder()
                        .index(1).value(avgScore).build()));
                return parseValueDTO;
            }
        }

        /**
         * 解析 Quore FW Hotel 报告数据
         *
         * @param propertyDTO 资产
         * @param in          文件流
         * @return HotelKpiParseValueDTO
         * @author zc
         * @date 2024/7/13
         */
        private HotelKpiParseValueDTO parseQuoreFWHotel(PropertyDTO propertyDTO, InputStream in) throws IOException {
            HotelKpiParseValueDTO parseValueDTO = new HotelKpiParseValueDTO();

            try (Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);

                // 获取日期范围
                Row dateRow = sheet.getRow(1);
                String dateStr = CellUtils.convertCellStr(dateRow.getCell(0));
                Map<String, String> title2DateStr = covertStringToMap(dateStr);

                String fromDateStr = title2DateStr.get("between");
                long startDate = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(fromDateStr,
                        BaseConstant.DATE_FORMAT_MM_DD_YYYY), BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setStartDate(startDate);

                String toDateStr = title2DateStr.get("and");
                long endDate = Long.parseLong(DateTimeUtils.localDateToStr(DateTimeUtils.strToLocalDate(toDateStr,
                        BaseConstant.DATE_FORMAT_MM_DD_YYYY), BaseConstant.DATE_FORMAT_YYYYMMDD));
                parseValueDTO.setEndDate(endDate);

                // 获取标题索引
                Row titleRow = sheet.getRow(3);
                int scoreIndex = CellUtils.getCellIndex(titleRow, "Score");

                // 计算平均值
                List<BigDecimal> scoreList = Lists.newArrayList();
                // 从第 5 行开始获取数据
                int rowIndex = 4;
                Row row = sheet.getRow(rowIndex);
                String cellStr = CellUtils.convertCellStr(row.getCell(0));
                // 校验是否等于 No Data Found
                if (!NO_DATA_FOUND.equalsIgnoreCase(cellStr)) {
                    while (Objects.nonNull(row)) {
                        String scoreStr = CellUtils.convertCellStr(row.getCell(scoreIndex));
                        if (StringUtils.isBlank(scoreStr)) {
                            break;
                        }
                        scoreList.add(new BigDecimal(scoreStr.replace("%", "").trim()));

                        // 获取下一行的数据
                        row = sheet.getRow(++rowIndex);
                    }
                }

                BigDecimal avgScore = null;
                if (CollectionUtils.isNotEmpty(scoreList)) {
                    RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(propertyDTO
                            .getInternationalization().getNumber().getCalculationRule());

                    avgScore = scoreList.stream().reduce(BigDecimal.ZERO, BigDecimal::add)
                            .divide(new BigDecimal(scoreList.size()), BaseConstant.DATA_PRECISION, roundingMode);
                }
                parseValueDTO.setValue(avgScore);
                parseValueDTO.setOriginValues(List.of(HotelKpiOriginCrawlValueIndexDTO.builder()
                        .index(1).value(avgScore).build()));
                return parseValueDTO;
            }
        }
    }