# ParseReportByOperatingStatement.java

    package cloud.wconnect.workperformance.service.monthlyreport.parsereport.impl;

    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.cons.CommonErrorI18nConstant;
    import cloud.wconnect.commonlib.cons.SystemPlatformEnum;
    import cloud.wconnect.commonlib.exception.BadRequestException;
    import cloud.wconnect.commonlib.exception.InternalServerErrorException;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.pojo.dto.internationalization.InternationalizationNumberDTO;
    import cloud.wconnect.commonlib.util.CellUtils;
    import cloud.wconnect.commonlib.util.I18nMessageUtils;
    import cloud.wconnect.commonlib.util.InternationalizationUtils;
    import cloud.wconnect.workperformance.cons.monthlyreport.ReportTypeEnum;
    import cloud.wconnect.workperformance.pojo.dto.monthlyreport.parsereport.ExpenseCategoryByReportDTO;
    import cloud.wconnect.workperformance.pojo.dto.monthlyreport.parsereport.ParseReportDTO;
    import cloud.wconnect.workperformance.pojo.dto.monthlyreport.parsereport.ProfitByReportDTO;
    import cloud.wconnect.workperformance.pojo.dto.monthlyreport.parsereport.RoomRevenueByReportDTO;
    import cloud.wconnect.workperformance.service.monthlyreport.parsereport.IParseReport;
    import com.google.common.collect.ImmutableList;
    import com.google.common.collect.Lists;
    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.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Component;

    import java.io.IOException;
    import java.io.InputStream;
    import java.math.BigDecimal;
    import java.math.RoundingMode;
    import java.util.List;

    /**
     * 解析M3系统的perating Statement - MTD_YTD报告
     *
     * @author lichen
     * @date 2023/9/6
     */
    @Component
    public class ParseReportByOperatingStatement implements IParseReport {

        @Autowired
        private I18nMessageUtils i18nMessageUtils;

        /**
         * 是否支持
         *
         * @param system     系统名称
         * @param reportType 报告类型
         * @return boolean
         * @author lichen
         * @date 2023/9/6
         */
        @Override
        public boolean supports(String system, Integer reportType) {
            return system.equals(SystemPlatformEnum.M3.getDesc())
                    && reportType == ReportTypeEnum.OPERATING_STATEMENT.getReportType();
        }

        /**
         * 解析报告
         *
         * @param propertyDTO 资产DTO
         * @param in          文件流
         * @return cloud.wconnect.workperformance.pojo.dto.monthlyreport.parsereport.ParseReportDTO
         * @author lichen
         * @date 2023/9/6
         */
        @Override
        public ParseReportDTO parse(PropertyDTO propertyDTO, InputStream in) {
            try (Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                //提取Room Revenue部分数据
                List<RoomRevenueByReportDTO> roomRevenueByReportDTOS = extractRevenuesContent(propertyDTO, sheet);
                //提取Profit部分数据
                List<ProfitByReportDTO> profitByReportDTOS = extractProfitsContent(propertyDTO, sheet);
                //提取Expenses 部分数据
                List<ExpenseCategoryByReportDTO> expenseCategoryByReportDTOS = extractExpensesContent(propertyDTO, sheet);
                return new ParseReportDTO(roomRevenueByReportDTOS, profitByReportDTOS, expenseCategoryByReportDTOS);
            } catch (Exception e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "parse Operating statement report error.", e);
            }
        }

        /**
         * 提取Room Revenue部分数据
         *
         * @param propertyDTO 资产DTO
         * @param sheet       页
         * @return java.util.List<cloud.wconnect.workperformance.pojo.dto.monthlyreport.parsereport.RevenueCompSetByReportDTO>
         * @author lichen
         * @date 2023/9/6
         */
        private List<RoomRevenueByReportDTO> extractRevenuesContent(PropertyDTO propertyDTO, final Sheet sheet) {
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());

            List<RoomRevenueByReportDTO> revenueCompSets = Lists.newArrayList();

            //Room Revenue部分的信息所在的行
            int[] rowIndexArray = new int[]{5, 6, 7, 10, 11, 12};

            for (int rowIndex : rowIndexArray) {
                //获取固定单元格的值
                List<String> cellValueList = listFixedCellValueForRevenueAndProfit(sheet, rowIndex);

                revenueCompSets.add(RoomRevenueByReportDTO.builder()
                        .actual(new BigDecimal(cellValueList.get(0)).setScale(BaseConstant.DATA_PRECISION, roundingMode))
                        .lastYear(new BigDecimal(cellValueList.get(1)).setScale(BaseConstant.DATA_PRECISION, roundingMode))
                        .budget(new BigDecimal(cellValueList.get(2)).setScale(BaseConstant.DATA_PRECISION, roundingMode))
                        .categoryName(cellValueList.get(3).trim())
                        .build());
            }
            return revenueCompSets;
        }

        /**
         * 提取Profit部分数据
         *
         * @param propertyDTO 资产DTO
         * @param sheet       页
         * @return java.util.List<cloud.wconnect.workperformance.pojo.dto.monthlyreport.parsereport.ProfitByReportDTO>
         * @author lichen
         * @date 2023/9/6
         */
        private List<ProfitByReportDTO> extractProfitsContent(PropertyDTO propertyDTO, final Sheet sheet) {
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());

            List<ProfitByReportDTO> profits = Lists.newArrayList();

            //Profit部分的信息所在的行
            int[] rowIndexArray = new int[]{24, 35, 48};

            for (int rowIndex : rowIndexArray) {
                List<String> cellValueList = listFixedCellValueForRevenueAndProfit(sheet, rowIndex);
                profits.add(ProfitByReportDTO.builder()
                        .actual(new BigDecimal(cellValueList.get(0)).setScale(BaseConstant.DATA_PRECISION, roundingMode))
                        .lastYear(new BigDecimal(cellValueList.get(1)).setScale(BaseConstant.DATA_PRECISION, roundingMode))
                        .budget(new BigDecimal(cellValueList.get(2)).setScale(BaseConstant.DATA_PRECISION, roundingMode))
                        .categoryName(cellValueList.get(3).trim())
                        .build());
            }
            return profits;
        }

        /**
         * 获取固定单元格的数据
         * 提取Revenue 和 Profit 数据时使用
         *
         * @param sheet    页
         * @param rowIndex 行号
         * @return java.util.List<java.lang.String>
         * @author lichen
         * @date 2023/9/6
         */
        private List<String> listFixedCellValueForRevenueAndProfit(final Sheet sheet, final int rowIndex) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate("row(" + rowIndex + ")"),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "The row is not found; Row Number: " + (rowIndex + 1));
            }

            String ptd = CellUtils.convertCellStr(row.getCell(0));
            String ptdBdgt = CellUtils.convertCellStr(row.getCell(2));
            String ptdLy = CellUtils.convertCellStr(row.getCell(4));
            String categoryName = CellUtils.convertCellStr(row.getCell(6));

            /**
             * 第5行的数据有%，需要去除%
             */
            final int occupancyRow = 5;
            if (rowIndex == occupancyRow) {
                BigDecimal oneHundred = BigDecimal.valueOf(100);
                ptd = ptd.isEmpty() ? ptd : new BigDecimal(ptd).multiply(oneHundred).toString();
                ptdLy = ptdLy.isEmpty() ? ptdLy : new BigDecimal(ptdLy).multiply(oneHundred).toString();
                ptdBdgt = ptdBdgt.isEmpty() ? ptdBdgt : new BigDecimal(ptdBdgt).multiply(oneHundred).toString();
            }

            return ImmutableList.of(ptd, ptdLy, ptdBdgt, categoryName);
        }

        /**
         * 提取Expenses 部分数据
         *
         * @param propertyDTO 资产DTO
         * @param sheet       页
         * @return java.util.List<cloud.wconnect.workperformance.pojo.dto.monthlyreport.parsereport.ExpenseCategoryByReportDTO>
         * @author lichen
         * @date 2023/9/6
         */
        private List<ExpenseCategoryByReportDTO> extractExpensesContent(PropertyDTO propertyDTO, final Sheet sheet) {
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());

            List<ExpenseCategoryByReportDTO> expenseCategoryDTOList = Lists.newArrayList();
            /*
             * 第17行: Rooms, 第18行: F&B, 第19行: Other/Golf, 第27行: Admin & Gen, 第28行: Sales & Marketing,
             * 第29行: Maintenance, 第30行: Utilities, 第31行: Technology, 第20行: Laundry + 第21行: Transportation,
             * 第32行: Franchise Expenses
             */
            int[] rowIndexArray = new int[]{16, 17, 18, 26, 27, 28, 29, 30, 19, 20, 31};

            for (int rowIndex : rowIndexArray) {
                List<String> varBdgtList = list4VarBdgt(sheet, rowIndex);

                expenseCategoryDTOList.add(ExpenseCategoryByReportDTO.builder()
                        .varBdgtMtd(new BigDecimal(varBdgtList.get(0)).setScale(BaseConstant.DATA_PRECISION, roundingMode))
                        .varBdgtYtd(new BigDecimal(varBdgtList.get(1)).setScale(BaseConstant.DATA_PRECISION, roundingMode))
                        .categoryName(varBdgtList.get(2).trim())
                        .build());
            }
            return expenseCategoryDTOList;
        }

        /**
         * 获取指定行的VAR BDGT列数据
         *
         * @param sheet    页
         * @param rowIndex 行号
         * @return java.util.List<java.lang.String>
         * @author lichen
         * @date 2023/9/6
         */
        private List<String> list4VarBdgt(final Sheet sheet, final int rowIndex) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate("row(" + rowIndex + ")"),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "The row for VAR BDGT is not found; Row Number: " + (rowIndex + 1));
            }

            String varBdgtMtd = CellUtils.convertCellStr(row.getCell(3));
            varBdgtMtd = varBdgtMtd.isEmpty() ? "0" : varBdgtMtd;
            String varBdgtYtd = CellUtils.convertCellStr(row.getCell(11));
            varBdgtYtd = varBdgtYtd.isEmpty() ? "0" : varBdgtYtd;
            String categoryName = CellUtils.convertCellStr(row.getCell(6));

            return ImmutableList.of(varBdgtMtd, varBdgtYtd, categoryName);
        }

    }