# HotelKpiStrWeekParseService.java

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

    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.cons.EventBodyParamEnum;
    import cloud.wconnect.commonlib.exception.BadRequestException;
    import cloud.wconnect.commonlib.exception.InternalServerErrorException;
    import cloud.wconnect.commonlib.exception.ParseReportException;
    import cloud.wconnect.commonlib.pojo.dto.EventBodyDTO;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.util.*;
    import cloud.wconnect.sns.http.processor.Sender;
    import cloud.wconnect.workperformance.cons.BusinessEnum;
    import cloud.wconnect.workperformance.cons.EventSubjectEnum;
    import cloud.wconnect.workperformance.cons.hotelkpi.HotelKpiErrorI18nConstant;
    import cloud.wconnect.workperformance.cons.hotelkpi.strdata.HotelKpiStrWeekDateEnum;
    import cloud.wconnect.workperformance.cons.hotelkpi.strdata.HotelKpiStrWeekEnum;
    import cloud.wconnect.workperformance.pojo.bo.hotelkpi.str.week.HotelKpiStrWeekBO;
    import cloud.wconnect.workperformance.pojo.bo.hotelkpi.str.week.HotelKpiStrWeekCategoryBO;
    import cloud.wconnect.workperformance.pojo.bo.hotelkpi.str.week.HotelKpiStrWeekDetailBO;
    import cloud.wconnect.workperformance.service.AmazonS3Service;
    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.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.Qualifier;
    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.text.MessageFormat;
    import java.time.LocalDate;
    import java.time.temporal.TemporalAdjusters;
    import java.util.HashMap;
    import java.util.List;

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

        @Resource
        private ObjectMapper objectMapper;
        @Resource
        private AmazonS3Service amazonS3Service;
        @Resource
        @Qualifier("3pSender")
        private Sender snsSender;

        @Value("${aws-s3.third-party.key.hotel-kpi.str.week}")
        private String s3FilePath;
        @Value("${file-max-size.import-report.hotel-kpi}")
        private long fileMaxSize;

        /**
         * 校验文件是否合法
         *
         * @param file 文件
         * @author zc
         * @date 2025/8/22
         */
        public void checkFile(LocalDate localDate, MultipartFile file) {
            // 限制文件大小
            FileCheckUtils.checkFileSize(file, fileMaxSize);
            // 校验文件格式
            UploadUtils.checkFileFormat(file, Lists.newArrayList("xls", "xlsx"));
            try (InputStream in = file.getInputStream(); Workbook workbook = WorkbookFactory.create(in)) {
                Sheet glanceSheet = workbook.getSheet("Glance");

                // 检查工作表是否存在
                if (glanceSheet == null) {
                    throw new BadRequestException(HotelKpiErrorI18nConstant.REPORT_DATE_NOT_BELONG_TO_DATE,
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            "The report does not contain the required 'Glance' sheet");
                }

                // 解析报告日期
                LocalDate reportDate = parseStrIhgReportDate(glanceSheet);
                // 报告归属周期的日期
                LocalDate reportBelongDate = reportDate.with(TemporalAdjusters.nextOrSame(localDate.getDayOfWeek()));
                // 校验报告归属日期与当前日期是否一致
                if (!localDate.isEqual(reportBelongDate)) {
                    throw new BadRequestException(HotelKpiErrorI18nConstant.REPORT_DATE_NOT_BELONG_TO_DATE,
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            MessageFormat.format("The reporting data date is not part of the current period; reportDate:{0}", reportDate));
                }
            } catch (IOException e) {
                throw new BadRequestException(HotelKpiErrorI18nConstant.REPORT_DATE_NOT_BELONG_TO_DATE,
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "Failed to read the uploaded file", e);
            } catch (BadRequestException e) {
                // 重新抛出业务异常
                throw e;
            } catch (Exception e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle parse Hotel KPI Str report error.", e);
            }
        }

        /**
         * 处理数据
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @param strWeekId   Str Week id
         * @param operatorId  操作人 id
         * @author zc
         * @date 2025/8/22
         */
        public void handleData(PropertyDTO propertyDTO, MultipartFile file, int strWeekId, int operatorId) {
            try {
                String key = propertyDTO.getTenantSymbol() + "/" + propertyDTO.getAbbr() + "/" + s3FilePath
                        + UploadUtils.getRandomFileName(file.getOriginalFilename());

                // 上传报告到 S3 服务器，用于事件监听解析报告
                amazonS3Service.uploadFile(file, key);

                // 封装事件体
                HashMap<String, Object> parameter = Maps.newHashMap();
                parameter.put(EventBodyParamEnum.TENANT_SYMBOL.getDesc(), propertyDTO.getTenantSymbol());
                parameter.put(EventBodyParamEnum.PROPERTY_ABBR.getDesc(), List.of(propertyDTO.getAbbr()));
                parameter.put(EventBodyParamEnum.ORIGINATE_FROM.getDesc(), BusinessEnum.HOTEL_KPI.getBusiness());
                parameter.put(EventBodyParamEnum.ID.getDesc(), strWeekId);
                parameter.put(EventBodyParamEnum.UID.getDesc(), operatorId);

                // 手动发送事件通知
                snsSender.publish(EventSubjectEnum.STR_WEEK.getSubject(),
                        JsonUtils.convertObjToString(objectMapper, new EventBodyDTO(key, parameter)));
            } catch (Exception e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle import Hotel KPI Str Week report exception", e);
            }
        }

        /**
         * 解析报告
         *
         * @param propertyDTO 资产
         * @param in          报告文件流
         * @return HotelKpiStrWeekBO
         * @author zc
         * @date 2025/8/22
         */
        public HotelKpiStrWeekBO 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");

                HotelKpiStrWeekBO strParseDTO = new HotelKpiStrWeekBO();

                // 解析日期
                LocalDate localDate = parseStrIhgReportDate(glanceSheet);
                strParseDTO.setDataDate(Long.parseLong(DateTimeUtils.localDateToStr(localDate,
                        BaseConstant.DATE_FORMAT_YYYYMMDD)));

                // Weekly
                HotelKpiStrWeekCategoryBO week = new HotelKpiStrWeekCategoryBO();
                week.setCategory(HotelKpiStrWeekDateEnum.WEEK.getCategory());
                // 解析 Weekly
                week.setList(parseStrIhgWeekly(glanceSheet, roundingMode));

                // R28D
                HotelKpiStrWeekCategoryBO r28d = new HotelKpiStrWeekCategoryBO();
                r28d.setCategory(HotelKpiStrWeekDateEnum.R28D.getCategory());
                // 解析 R28D
                r28d.setList(parseStrIhgR28d(glanceSheet, roundingMode));

                strParseDTO.setList(Lists.newArrayList(week, r28d));

                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 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 cellStr = CellUtils.convertCellStr(dateRow.getCell(CellReference.convertColStringToIndex("B")));
            String dateStr = StringUtils.substringBefore(cellStr, " - ");
            return DateTimeUtils.strToLocalDate(dateStr, BaseConstant.DATE_FORMAT_MMMM_DD_YYYY);
        }

        /**
         * 解析 Str IHG Weekly
         *
         * @param sheet        sheet
         * @param roundingMode 舍入模式
         * @return HotelKpiStrWeekValueSaveDTO 集合
         * @author zc
         * @date 2025/8/22
         */
        private List<HotelKpiStrWeekDetailBO> parseStrIhgWeekly(Sheet sheet, RoundingMode roundingMode) {
            return Lists.newArrayList(HotelKpiStrWeekDetailBO.builder()
                            .category(HotelKpiStrWeekEnum.OCC_PERCENT.getCategory())
                            .property(getAndFormatValue(sheet, 9, "Z", roundingMode, true))
                            .propertyChg(getAndFormatValue(sheet, 9, "AA", roundingMode, false))
                            .compSet(getAndFormatValue(sheet, 10, "Z", roundingMode, true))
                            .compSetChg(getAndFormatValue(sheet, 10, "AA", roundingMode, false))
                            .index(getAndFormatValue(sheet, 11, "Z", roundingMode, false))
                            .indexChg(getAndFormatValue(sheet, 11, "AA", roundingMode, false))
                            .build(),
                    HotelKpiStrWeekDetailBO.builder()
                            .category(HotelKpiStrWeekEnum.ADR.getCategory())
                            .property(getAndFormatValue(sheet, 13, "Z", roundingMode, false))
                            .propertyChg(getAndFormatValue(sheet, 13, "AA", roundingMode, false))
                            .compSet(getAndFormatValue(sheet, 14, "Z", roundingMode, false))
                            .compSetChg(getAndFormatValue(sheet, 14, "AA", roundingMode, false))
                            .index(getAndFormatValue(sheet, 15, "Z", roundingMode, false))
                            .indexChg(getAndFormatValue(sheet, 15, "AA", roundingMode, false))
                            .build(),
                    HotelKpiStrWeekDetailBO.builder()
                            .category(HotelKpiStrWeekEnum.REV_PAR.getCategory())
                            .property(getAndFormatValue(sheet, 17, "Z", roundingMode, false))
                            .propertyChg(getAndFormatValue(sheet, 17, "AA", roundingMode, false))
                            .compSet(getAndFormatValue(sheet, 18, "Z", roundingMode, false))
                            .compSetChg(getAndFormatValue(sheet, 18, "AA", roundingMode, false))
                            .index(getAndFormatValue(sheet, 19, "Z", roundingMode, false))
                            .indexChg(getAndFormatValue(sheet, 19, "AA", roundingMode, false))
                            .build());
        }

        /**
         * 解析 Str IHG R28D
         *
         * @param sheet        sheet
         * @param roundingMode 舍入模式
         * @return HotelKpiStrWeekValueSaveDTO 集合
         * @author zc
         * @date 2025/8/22
         */
        private List<HotelKpiStrWeekDetailBO> parseStrIhgR28d(Sheet sheet, RoundingMode roundingMode) {
            return Lists.newArrayList(HotelKpiStrWeekDetailBO.builder()
                            .category(HotelKpiStrWeekEnum.OCC_PERCENT.getCategory())
                            .property(getAndFormatValue(sheet, 26, "Z", roundingMode, true))
                            .propertyChg(getAndFormatValue(sheet, 26, "AA", roundingMode, false))
                            .compSet(getAndFormatValue(sheet, 27, "Z", roundingMode, true))
                            .compSetChg(getAndFormatValue(sheet, 27, "AA", roundingMode, false))
                            .index(getAndFormatValue(sheet, 28, "Z", roundingMode, false))
                            .indexChg(getAndFormatValue(sheet, 28, "AA", roundingMode, false))
                            .build(),
                    HotelKpiStrWeekDetailBO.builder()
                            .category(HotelKpiStrWeekEnum.ADR.getCategory())
                            .property(getAndFormatValue(sheet, 30, "Z", roundingMode, false))
                            .propertyChg(getAndFormatValue(sheet, 30, "AA", roundingMode, false))
                            .compSet(getAndFormatValue(sheet, 31, "Z", roundingMode, false))
                            .compSetChg(getAndFormatValue(sheet, 31, "AA", roundingMode, false))
                            .index(getAndFormatValue(sheet, 32, "Z", roundingMode, false))
                            .indexChg(getAndFormatValue(sheet, 32, "AA", roundingMode, false))
                            .build(),
                    HotelKpiStrWeekDetailBO.builder()
                            .category(HotelKpiStrWeekEnum.REV_PAR.getCategory())
                            .property(getAndFormatValue(sheet, 34, "Z", roundingMode, false))
                            .propertyChg(getAndFormatValue(sheet, 34, "AA", roundingMode, false))
                            .compSet(getAndFormatValue(sheet, 35, "Z", roundingMode, false))
                            .compSetChg(getAndFormatValue(sheet, 35, "AA", roundingMode, false))
                            .index(getAndFormatValue(sheet, 36, "Z", roundingMode, false))
                            .indexChg(getAndFormatValue(sheet, 36, "AA", roundingMode, false))
                            .build());
        }

        /**
         * 获取数值并格式化
         *
         * @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);
        }
    }