# HotelKpiStrMonthParseService.java

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

    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.*;
    import cloud.wconnect.workperformance.pojo.bo.hotelkpi.str.month.HotelKpiStrMonthBO;
    import cloud.wconnect.workperformance.pojo.bo.hotelkpi.str.month.HotelKpiStrMonthValueBO;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    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.apache.poi.ss.util.CellReference;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Component;
    import org.springframework.web.multipart.MultipartFile;

    import java.io.IOException;
    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.math.RoundingMode;
    import java.time.LocalDate;

    /**
     * 解析 Str Month 报告
     *
     * @author zc
     * @date 2025/08/22
     */
    @Slf4j
    @Component
    @DS("work_performance")
    public class HotelKpiStrMonthParseService {

        @Value("${file-max-size.import-report.hotel-kpi}")
        private long fileMaxSize;

        /**
         * 校验文件内容是否合法
         *
         * @param file 文件
         * @author zc
         * @date 2025/8/22
         */
        public void checkFile(MultipartFile file) {
            // 限制文件大小
            FileCheckUtils.checkFileSize(file, fileMaxSize);
            // 校验文件格式
            UploadUtils.checkFileFormat(file, Lists.newArrayList("xls", "xlsx"));
        }

        /**
         * 解析报告
         *
         * @param propertyDTO 资产
         * @param in          报告文件流
         * @return HotelKpiStrMonthBO
         * @author zc
         * @date 2025/8/22
         */
        public HotelKpiStrMonthBO parse(PropertyDTO propertyDTO, InputStream in) throws ParseReportException {
            // 获取国际化数字配置
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(propertyDTO
                    .getInternationalization().getNumber().getCalculationRule());
            try (Workbook workbook = WorkbookFactory.create(in)) {
                Sheet glanceSheet = workbook.getSheet("Glance");

                HotelKpiStrMonthBO strParseDTO = new HotelKpiStrMonthBO();

                // 解析月日期
                LocalDate localDate = parseStrIhgReportDate(glanceSheet);
                strParseDTO.setDataDate(Long.parseLong(DateTimeUtils.localDateToStr(localDate,
                        BaseConstant.DATE_FORMAT_YYYYMM)));
                HotelKpiStrMonthValueBO mpi = new HotelKpiStrMonthValueBO();
                mpi.setMonthValue(getAndFormatValue(glanceSheet, 10, "I", roundingMode, false));
                mpi.setQuarterValue(getAndFormatValue(glanceSheet, 14, "I", roundingMode, false));
                mpi.setYearValue(getAndFormatValue(glanceSheet, 12, "I", roundingMode, false));
                strParseDTO.setMpi(mpi);

                HotelKpiStrMonthValueBO ari = new HotelKpiStrMonthValueBO();
                ari.setMonthValue(getAndFormatValue(glanceSheet, 10, "N", roundingMode, false));
                ari.setQuarterValue(getAndFormatValue(glanceSheet, 14, "N", roundingMode, false));
                ari.setYearValue(getAndFormatValue(glanceSheet, 12, "N", roundingMode, false));
                strParseDTO.setAri(ari);

                HotelKpiStrMonthValueBO rgi = new HotelKpiStrMonthValueBO();
                rgi.setMonthValue(getAndFormatValue(glanceSheet, 10, "S", roundingMode, false));
                rgi.setQuarterValue(getAndFormatValue(glanceSheet, 14, "S", roundingMode, false));
                rgi.setYearValue(getAndFormatValue(glanceSheet, 12, "S", roundingMode, false));
                strParseDTO.setRgi(rgi);

                return strParseDTO;
            } catch (IOException e) {
                log.error(e.getMessage(), e);
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle parse Hotel KPI Str Month report error.", e);
            }
        }

        /**
         * 解析 Str IHG 报告日期
         *
         * @param sheet sheet
         * @return Str IHG 报告日期
         * @author zc
         * @date 2025/8/22
         */
        private LocalDate parseStrIhgReportDate(Sheet sheet) {
            Row dateRow = sheet.getRow(5);
            String dateStr = CellUtils.convertCellStr(dateRow.getCell(CellReference.convertColStringToIndex("B")),
                    BaseConstant.DATE_FORMAT_M_D_YYYY);
            return DateTimeUtils.strToLocalDate(dateStr, BaseConstant.DATE_FORMAT_M_D_YYYY);
        }

        /**
         * 获取数值并格式化
         *
         * @param sheet        sheet
         * @param rowNum       行号
         * @param colStr       列号
         * @param roundingMode 舍入模式
         * @param isPercent    是否需要乘以百分比
         * @return java.math.BigDecimal
         * @author zc
         * @date 2025/8/22
         */
        private BigDecimal getAndFormatValue(Sheet sheet, int rowNum, String colStr, RoundingMode roundingMode, boolean isPercent) {
            String valueStr = CellUtils.convertCellStr(sheet.getRow(rowNum).getCell(CellReference.convertColStringToIndex(colStr)));
            if (StringUtils.isBlank(valueStr)) {
                return null;
            }
            if (isPercent) {
                return new BigDecimal(valueStr).multiply(new BigDecimal("100")).setScale(BaseConstant.DATA_PRECISION, roundingMode);
            }
            return new BigDecimal(valueStr).setScale(BaseConstant.DATA_PRECISION, roundingMode);
        }
    }