# WMRevenueBudgetParseServiceByOther.java

    package cloud.wconnect.rateshop.service.weeklymonthlyrevenue.budget;

    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.exception.ParseReportException;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.util.*;
    import cloud.wconnect.rateshop.pojo.bo.weeklymonthlyrevenue.WMRevenueBudgetBO;
    import cloud.wconnect.rateshop.service.weeklymonthlyrevenue.IWeeklyMonthlyRevenueBudgetService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    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.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Component;
    import org.springframework.transaction.annotation.Transactional;
    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.YearMonth;
    import java.util.List;

    /**
     * Budget Other
     *
     * @author zc
     * @date 2024/08/23
     */
    @Slf4j
    @Component
    @DS("rate_shop")
    public class WMRevenueBudgetParseServiceByOther implements IWMRevenueBudgetParseService {
        @Resource
        private IWeeklyMonthlyRevenueBudgetService budgetService;
        @Resource
        private I18nMessageUtils i18nMessageUtils;
        @Value("${file-max-size.import-report.weekly-monthly-revenue}")
        private long fileMaxSize;

        /**
         * 支持的报告来源
         *
         * @param source 报告来源
         * @return boolean
         * @author zc
         * @date 2024/8/22
         */
        @Override
        public boolean supports(String source) {
            return SystemPlatformEnum.OTHER.getDesc().equals(source);
        }

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

            try (InputStream in = file.getInputStream();
                 Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                // 获取最大行数
                int lastRowNum = sheet.getLastRowNum();
                // 若只包含标题，直接返回
                if (lastRowNum < 2) {
                    return;
                }

                // 提取标题
                List<String> titleList = Lists.newArrayList();
                Row titleRow = sheet.getRow(1);
                for (int i = 0; i <= 6; i++) {
                    titleList.add(CellUtils.convertCellStr(titleRow.getCell(i)));
                }

                for (int i = 2; i <= lastRowNum; i++) {
                    Row row = sheet.getRow(i);

                    // BFR
                    String bfrStr = CellUtils.convertCellStr(row.getCell(1));
                    if (StringUtils.isNotBlank(bfrStr)) {
                        checkFileBigDecimalParam(titleList.get(1), 10, bfrStr);
                    }
                    // OOO
                    String oooStr = CellUtils.convertCellStr(row.getCell(2));
                    if (StringUtils.isNotBlank(oooStr)) {
                        checkFileIntegerParam(titleList.get(2), -1000000, 1000000, oooStr);
                    }
                    // COMP
                    String compStr = CellUtils.convertCellStr(row.getCell(3));
                    if (StringUtils.isNotBlank(compStr)) {
                        checkFileIntegerParam(titleList.get(3), -1000000, 1000000, compStr);
                    }
                    // No Show
                    String noShowStr = CellUtils.convertCellStr(row.getCell(4));
                    if (StringUtils.isNotBlank(noShowStr)) {
                        checkFileIntegerParam(titleList.get(4), -1000000, 1000000, noShowStr);
                    }
                    // Group Contracted
                    String groupContractedStr = CellUtils.convertCellStr(row.getCell(5));
                    if (StringUtils.isNotBlank(groupContractedStr)) {
                        checkFileIntegerParam(titleList.get(5), -1000000, 1000000, groupContractedStr);
                    }
                    // Group Cancelled
                    String groupCancelledStr = CellUtils.convertCellStr(row.getCell(6));
                    if (StringUtils.isNotBlank(groupCancelledStr)) {
                        checkFileIntegerParam(titleList.get(6), -1000000, 1000000, groupCancelledStr);
                    }
                }
            } catch (IOException e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle check Weekly Monthly Revenue Budget report error.", e);
            }
        }

        /**
         * Integer 字段校验
         *
         * @param title 标题
         * @param min   最小值
         * @param max   最大值
         * @param param 参数
         * @author zc
         * @date 2024/8/22
         */
        private void checkFileIntegerParam(String title, int min, int max, String param) {
            try {
                int value = Integer.parseInt(param);
                if (value < min) {
                    throw new BadRequestException(CommonErrorI18nConstant.NUMBER_OVERLENGTH,
                            i18nMessageUtils.getErrorParamMapNotNeedTranslate(title, String.valueOf(min).length() - 1, 0),
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            MessageFormat.format("The {0} cannot be less than {1}, {0} = {2}", title, min, param));
                }
                if (value > max) {
                    throw new BadRequestException(CommonErrorI18nConstant.NUMBER_OVERLENGTH,
                            i18nMessageUtils.getErrorParamMapNotNeedTranslate(title, String.valueOf(max).length() - 1, 0),
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            MessageFormat.format("The {0} cannot be greater than {1}, {0} = {2}", title, max, param));
                }
            } catch (NumberFormatException e) {
                throw new BadRequestException(CommonErrorI18nConstant.MUST_INTEGER,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(title),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        MessageFormat.format("The {0} must integer, {0} = {1}", title, param));
            }
        }

        /**
         * BigDecimal 字段校验
         *
         * @param title        标题
         * @param integerLimit 整数位限制
         * @param param        参数
         * @author zc
         * @date 2024/8/22
         */
        private void checkFileBigDecimalParam(String title, int integerLimit, String param) {
            BigDecimal value;
            try {
                value = new BigDecimal(param);
            } catch (NumberFormatException e) {
                throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(title),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        MessageFormat.format("The {0} format is incorrect, {0} = {1}", title, param));
            }
            // 校验整数位是否超过限制
            int intLength = value.abs().toBigInteger().toString().length();
            if (intLength > integerLimit) {
                throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                        i18nMessageUtils.getErrorParamMapNotNeedTranslate(title, integerLimit),
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        MessageFormat.format("The integer of {0} exceeds the limit, {0} = {1}", title, param));
            }
        }

        /**
         * 处理数据
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @param year        年（yyyy）
         * @param operatorId  操作人 id
         * @author zc
         * @date 2024/8/22
         */
        @Override
        @Transactional(rollbackFor = Exception.class)
        public void handleData(PropertyDTO propertyDTO, MultipartFile file, long year, int operatorId) {
            try (InputStream in = file.getInputStream()) {
                // 解析报告
                List<WMRevenueBudgetBO> budgetBOList = parse(propertyDTO, in, year);
                // 处理数据
                budgetService.handleBudgetReport(budgetBOList, year, propertyDTO, operatorId);
            } catch (Exception e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle import Weekly Monthly Revenue Budget report error.", e);
            }
        }

        /**
         * 解析报告
         *
         * @param propertyDTO 资产
         * @param in          报告文件流
         * @param year        年份（yyyy）
         * @return WMRevenueBudgetBO 集合
         * @author zc
         * @date 2024/8/22
         */
        @Override
        public List<WMRevenueBudgetBO> parse(PropertyDTO propertyDTO, InputStream in, long year) throws ParseReportException {
            try (Workbook workbook = WorkbookFactory.create(in)) {
                List<WMRevenueBudgetBO> budgetBOList = Lists.newArrayList();

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

                Sheet sheet = workbook.getSheetAt(0);
                // 直接取第 3 行数据
                for (int rowIndex = 2; rowIndex <= 13; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    WMRevenueBudgetBO budgetBO = new WMRevenueBudgetBO();
                    budgetBO.setTheMonth(YearMonthUtils.parseYearMonthToLong(YearMonth.of((int) year, rowIndex - 1)));
                    String bfrStr = CellUtils.convertCellStr(row.getCell(1));
                    budgetBO.setBfr(StringUtils.isBlank(bfrStr) ? null : new BigDecimal(bfrStr).setScale(BaseConstant.DATA_PRECISION, roundingMode));
                    String oooStr = CellUtils.convertCellStr(row.getCell(2));
                    budgetBO.setOoo(StringUtils.isBlank(oooStr) ? null : Long.parseLong(oooStr));
                    String complimentaryRoomsStr = CellUtils.convertCellStr(row.getCell(3));
                    budgetBO.setComplimentaryRooms(StringUtils.isBlank(complimentaryRoomsStr) ? null : Long.parseLong(complimentaryRoomsStr));
                    String noShowRoomsStr = CellUtils.convertCellStr(row.getCell(4));
                    budgetBO.setNoShowRooms(StringUtils.isBlank(noShowRoomsStr) ? null : Long.parseLong(noShowRoomsStr));
                    String groupContractedStr = CellUtils.convertCellStr(row.getCell(5));
                    budgetBO.setGroupContracted(StringUtils.isBlank(groupContractedStr) ? null : Long.parseLong(groupContractedStr));
                    String groupCancelledStr = CellUtils.convertCellStr(row.getCell(6));
                    budgetBO.setGroupCancelled(StringUtils.isBlank(groupCancelledStr) ? null : Long.parseLong(groupCancelledStr));

                    budgetBOList.add(budgetBO);
                }

                return budgetBOList;
            } catch (IOException e) {
                log.error(e.getMessage(), e);
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(),
                        "Handle parse Weekly Monthly Revenue Budget report error.", e);
            }
        }
    }