# RateShopGroupPickupServiceByOther.java

    package cloud.wconnect.rateshop.service.rateshop.grouppickup;

    import cloud.wconnect.commonlib.component.BeanMapper;
    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.pojo.dto.internationalization.InternationalizationNumberDTO;
    import cloud.wconnect.commonlib.util.*;
    import cloud.wconnect.rateshop.cons.S3BucketFunctionCons;
    import cloud.wconnect.rateshop.cons.rateshop.BookingStatusEnum;
    import cloud.wconnect.rateshop.pojo.bo.rateshop.GroupPickupBO;
    import cloud.wconnect.rateshop.service.AmazonS3Service;
    import cloud.wconnect.rateshop.service.rateshop.IGroupPickupService;
    import cloud.wconnect.rateshop.service.rateshop.IReportImportLogService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    import com.google.common.collect.Maps;
    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.Autowired;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.context.i18n.LocaleContextHolder;
    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;
    import java.time.format.DateTimeFormatter;
    import java.time.format.DateTimeParseException;
    import java.util.*;

    /**
     * 解析团体预订信息
     *
     * @author lichen
     * @date 2024/7/3
     */
    @Slf4j
    @Component
    @DS("rate_shop")
    public class RateShopGroupPickupServiceByOther implements IRateShopGroupPickupService {

        @Autowired
        private I18nMessageUtils i18nMessageUtils;
        @Value("${aws-s3.third-party.key.rate-shop.group-pickup}")
        private String s3FilePath;
        @Autowired
        private IReportImportLogService importLogService;
        @Autowired
        private BeanMapper beanMapper;
        @Autowired
        private AmazonS3Service amazonS3Service;
        @Autowired
        private IGroupPickupService groupPickupService;
        @Value("${file-max-size.import-report.rate-shop}")
        private long fileMaxSize;

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

        /**
         * 解析报告
         *
         * @param in                  报告文件流
         * @param propertyDTO         资产
         * @param startDate           数据日期，开始时间（整点时间戳）
         * @param endDate             数据日期，结束时间（整点时间戳）
         * @param bookingStatusLocale 解析预订状态的语言标识(解析Other报告时，用户可以自主选择系统支持的语言模板)
         * @return 报告数据
         * @author zc
         * @date 2024/3/22
         */
        @Override
        public List<GroupPickupBO> parse(InputStream in, PropertyDTO propertyDTO, long startDate, long endDate, Locale bookingStatusLocale) throws ParseReportException {
            // 获取国际化的配置
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());

            List<GroupPickupBO> resultList = Lists.newArrayList();
            try (Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                //获取最大行数
                int lastRowNum = sheet.getLastRowNum();
                //只包含标题
                if (lastRowNum < 1) return Collections.emptyList();

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

                    //日期
                    String dateStr = CellUtils.convertCellStr(row.getCell(0));
                    long dateTimestamp = DateTimeUtils.dateStrToEpochSecond(dateStr, BaseConstant.DATE_FORMAT_YYYY_MM_DD, propertyDTO.getTimezone());
                    // 判断数据日期是否在开始日期 和 结束日期之间
                    if (dateTimestamp < startDate || dateTimestamp > endDate) {
                        continue;
                    }

                    //团体名称
                    String groupName = CellUtils.convertCellStr(row.getCell(1));

                    //预订状态
                    String bookingStatusStr = CellUtils.convertCellStr(row.getCell(2));
                    //预订状态集合
                    HashMap<String, Integer> bookingStatusStr2Code = Maps.newHashMapWithExpectedSize(4);
                    for (BookingStatusEnum bookingStatusEnum : Lists.newArrayList(BookingStatusEnum.values())) {
                        bookingStatusStr2Code.put(i18nMessageUtils.message(bookingStatusEnum.getI18nCode(), bookingStatusLocale), bookingStatusEnum.getCode());
                    }
                    Integer bookingStatus = bookingStatusStr2Code.get(bookingStatusStr);

                    //签约价
                    String rateBlockedStr = CellUtils.convertCellStr(row.getCell(3));
                    BigDecimal rateBlocked = StringUtils.isEmpty(rateBlockedStr) ? null : new BigDecimal(rateBlockedStr)
                            .setScale(BaseConstant.DATA_PRECISION, roundingMode);
                    //系统价
                    String ratePickupStr = CellUtils.convertCellStr(row.getCell(4));
                    BigDecimal ratePickup = StringUtils.isEmpty(ratePickupStr) ? null : new BigDecimal(ratePickupStr)
                            .setScale(BaseConstant.DATA_PRECISION, roundingMode);
                    //预先保留房间数
                    String roomBlockedStr = CellUtils.convertCellStr(row.getCell(5));
                    Integer roomBlocked = StringUtils.isEmpty(roomBlockedStr) ? null : Integer.parseInt(roomBlockedStr);
                    //实际订房数
                    String roomPickupStr = CellUtils.convertCellStr(row.getCell(6));
                    Integer roomPickup = StringUtils.isEmpty(roomPickupStr) ? null : Integer.parseInt(roomPickupStr);

                    resultList.add(GroupPickupBO.builder()
                            .date(dateStr)
                            .bookingStatus(bookingStatus)
                            .groupName(groupName)
                            .rateBlocked(rateBlocked)
                            .ratePickup(ratePickup)
                            .roomBlocked(roomBlocked)
                            .roomPickup(roomPickup)
                            .build());
                }

                return resultList;
            } catch (IOException ioException) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse group booking info excel error");
            }
        }

        /**
         * 校验文件内容是否合法
         *
         * @param file 文件
         * @author lichen
         * @date 2024/8/14
         */
        @Override
        public void checkFile(MultipartFile file) {
            // 限制文件大小
            FileCheckUtils.checkFileSize(file, fileMaxSize);
            //校验文件格式
            UploadUtils.checkFileFormat(file, Lists.newArrayList("xlsx", "xls"));
            //获取语言标识
            Locale locale = getLocale(file);

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

                //标题行
                Row titleRow = sheet.getRow(0);
                //标题集合
                ArrayList<String> titles = Lists.newArrayList();
                for (int i = 0; i < 7; i++) {
                    titles.add(CellUtils.convertCellStr(titleRow.getCell(i)));
                }

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

                    //日期
                    String date = CellUtils.convertCellStr(row.getCell(0));
                    if (StringUtils.isEmpty(date)) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(0));
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The date cannot be null");
                    } else {
                        try {
                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD, Locale.ENGLISH);
                            LocalDate.parse(date, formatter);
                        } catch (DateTimeParseException e) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(0),
                                    BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The date format is incorrect, date = " + date);
                        }
                    }

                    //团体名称
                    String groupName = CellUtils.convertCellStr(row.getCell(1));
                    if (StringUtils.isEmpty(groupName)) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(1));
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The group name cannot be null");
                    } else {
                        if (groupName.length() > 255) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(1), 255);
                            throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The group name maximum length is 255");
                        }
                    }

                    //预订状态
                    String bookingStatusStr = CellUtils.convertCellStr(row.getCell(2));
                    if (StringUtils.isEmpty(bookingStatusStr)) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(2));
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The booking status cannot be null");
                    } else {
                        //预订状态集合
                        List<String> bookingStatusStrList = Arrays.stream(BookingStatusEnum.values())
                                .map(bookingStatusEnum -> i18nMessageUtils.message(bookingStatusEnum.getI18nCode(), locale))
                                .toList();
                        if (!bookingStatusStrList.contains(bookingStatusStr)) {
                            throw new BadRequestException(CommonErrorI18nConstant.INVALID_VALUE,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(2), StringUtils.join(bookingStatusStrList, ",")),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The booking status is not valid, booking status = " + bookingStatusStr);
                        }
                    }

                    //签约价
                    String rateBlockedStr = CellUtils.convertCellStr(row.getCell(3));
                    if (StringUtils.isNotBlank(rateBlockedStr)) {
                        BigDecimal rateBlocked;
                        try {
                            rateBlocked = new BigDecimal(rateBlockedStr);
                            if (rateBlocked.compareTo(new BigDecimal("0")) < 0) {
                                //获取国际化的参数
                                Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(3));
                                throw new BadRequestException(CommonErrorI18nConstant.CANNOT_LESS_THAN_ZERO, language2ParamList,
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The contract price cannot be less than 0, contract price = " + rateBlockedStr);
                            }
                        } catch (NumberFormatException e) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(3));
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The contract price must number, contract price = " + rateBlockedStr);
                        }

                        //校验整数位是否超过限制
                        int intLength = rateBlocked.abs().toBigInteger().toString().length();
                        if (intLength > 8) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(3), 8);
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The integer of contract price exceeds the limit, contract price  = " + rateBlockedStr);
                        }
                    }

                    //系统价
                    String ratePickupStr = CellUtils.convertCellStr(row.getCell(4));
                    if (StringUtils.isNotBlank(ratePickupStr)) {
                        BigDecimal ratePickup;
                        try {
                            ratePickup = new BigDecimal(ratePickupStr);
                            if (ratePickup.compareTo(new BigDecimal("0")) < 0) {
                                //获取国际化的参数
                                Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(4));
                                throw new BadRequestException(CommonErrorI18nConstant.CANNOT_LESS_THAN_ZERO, language2ParamList,
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The system price cannot be less than 0, system price = " + ratePickupStr);
                            }
                        } catch (NumberFormatException e) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(4));
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The system price must number, system price = " + ratePickupStr);
                        }

                        //校验整数位是否超过限制
                        int intLength = ratePickup.abs().toBigInteger().toString().length();
                        if (intLength > 8) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(4), 8);
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The integer of system price exceeds the limit, system price  = " + ratePickupStr);
                        }
                    }

                    //预先保留房间数
                    String roomBlockedStr = CellUtils.convertCellStr(row.getCell(5));
                    if (StringUtils.isNotBlank(roomBlockedStr)) {
                        try {
                            int roomBlocked = Integer.parseInt(roomBlockedStr);
                            if (roomBlocked < 0) {
                                //获取国际化的参数
                                Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(5));
                                throw new BadRequestException(CommonErrorI18nConstant.CANNOT_LESS_THAN_ZERO, language2ParamList,
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The rooms booked cannot be less than 0, rooms booked = " + roomBlockedStr);
                            }
                        } catch (NumberFormatException e) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(5));
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_INTEGER, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The number of rooms booked must integer, rooms booked = " + roomBlockedStr);
                        }
                    }

                    //实际订房数
                    String roomPickupStr = CellUtils.convertCellStr(row.getCell(6));
                    if (StringUtils.isNotBlank(roomPickupStr)) {
                        try {
                            int roomPickup = Integer.parseInt(roomPickupStr);
                            if (roomPickup < 0) {
                                //获取国际化的参数
                                Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(6));
                                throw new BadRequestException(CommonErrorI18nConstant.CANNOT_LESS_THAN_ZERO, language2ParamList,
                                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The actual number of rooms booked cannot be less than 0, roomPickupStr = " + roomPickupStr);
                            }
                        } catch (NumberFormatException e) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(6));
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_INTEGER, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The actual number of rooms booked must integer, roomPickupStr = " + roomPickupStr);
                        }
                    }
                }
            } catch (IOException ioException) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse group booking info excel error", ioException);
            }
        }

        /**
         * 处理数据
         *
         * @param propertyDTO 资产DTO
         * @param file        文件
         * @param startDate   开始时间整点时刻时间戳
         * @param endDate     结束时间整点时刻时间戳
         * @param logId       日志ID
         * @param userId      操作人ID
         * @return void
         * @author lichen
         * @date 2024/8/14
         */
        @Override
        public void handleData(PropertyDTO propertyDTO, MultipartFile file, long startDate, long endDate, long logId, int userId) {
            boolean result = true;
            String key = propertyDTO.getTenantSymbol() + "/" + propertyDTO.getAbbr() + "/" + s3FilePath +
                    UploadUtils.getRandomFileName(file.getOriginalFilename());
            try (InputStream inputStream = file.getInputStream()) {
                // 上传报告文件到 S3
                amazonS3Service.uploadFile(S3BucketFunctionCons.RATE_SHOP_IMPORT_RECORD, file, key);

                List<GroupPickupBO> groupPickupBOList = parse(inputStream, propertyDTO, startDate, endDate, getLocale(file));

                // 保存 pms group pickup 报告数据
                groupPickupService.savePmsGroupPickupReportData(propertyDTO, userId, groupPickupBOList,
                        startDate, endDate, SystemPlatformEnum.OTHER.getDesc());
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                result = false;
            } finally {
                importLogService.updateLog(logId, userId, key, result);
            }
        }

        /**
         * 获取当前语言标识，优先级：根据文件名后缀获取 > 当前上下文语言标识
         *
         * @param file 文件
         * @return java.util.Locale
         * @author lichen
         * @date 2024/8/14
         */
        private Locale getLocale(MultipartFile file) {
            Locale locale;
            try {
                //从文件名中获取语言标识
                String originalFilename = file.getOriginalFilename();
                String language = originalFilename.substring(originalFilename.length() - 5);
                locale = InternationalizationUtils.getLocale(language);
            } catch (Exception e) {
                //从当前上下文中获取语言标识
                locale = LocaleContextHolder.getLocale();
            }
            return locale;
        }
    }