# BookingByOther.java

    package cloud.wconnect.rateshop.service.booking.report;

    import cloud.wconnect.commonlib.component.BeanMapper;
    import cloud.wconnect.commonlib.cons.*;
    import cloud.wconnect.commonlib.exception.BadRequestException;
    import cloud.wconnect.commonlib.exception.ParseReportException;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.util.*;
    import cloud.wconnect.rateshop.cons.S3BucketFunctionCons;
    import cloud.wconnect.rateshop.entity.booking.Booking;
    import cloud.wconnect.rateshop.entity.booking.BookingImportLog;
    import cloud.wconnect.rateshop.pojo.bo.booking.BookingBO;
    import cloud.wconnect.rateshop.service.AmazonS3Service;
    import cloud.wconnect.rateshop.service.booking.IBookingImportLogService;
    import cloud.wconnect.rateshop.service.booking.IBookingService;
    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.collections.CollectionUtils;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.*;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.scheduling.annotation.Async;
    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.time.LocalDate;
    import java.time.format.DateTimeFormatter;
    import java.util.List;
    import java.util.Objects;
    import java.util.stream.Collectors;

    /**
     * Booking By Other
     *
     * @author yyt
     * 2024/8/6
     */
    @Slf4j
    @Component
    @DS("rate_shop")
    public class BookingByOther implements IBookingFileParse {

        @Resource
        private IBookingImportLogService bookingImportLogService;
        @Resource
        private IBookingService bookingService;
        @Autowired
        private AmazonS3Service amazonS3Service;
        @Autowired
        private BeanMapper beanMapper;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;

        @Value("${aws-s3.third-party.key.booking-analysis}")
        private String s3ManualImportKey;

        @Value("${file-max-size.import-report.booking-analysis}")
        private Long importReportFileMaxSize;

        private static final String SOURCE = SystemPlatformEnum.OTHER.getDesc();

        /**
         * 判断可导入文件条件
         *
         * @param source 平台数据源
         * @return boolean
         * @author yyt
         * @date 2024/8/6
         */
        @Override
        public boolean supports(String source) {
            return source.equals(SOURCE);
        }

        /**
         * 解析报告
         *
         * @param propertyDTO 资产
         * @param inputStream 文件流
         * @param startDate   开始日期
         * @param endDate     结束日期
         * @return ScheduleBO
         * @author yyt
         * @date 2024/8/6
         */
        @Override
        public List<BookingBO> parsReport(PropertyDTO propertyDTO, InputStream inputStream, Long startDate, Long endDate) throws IOException {
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
            DateTimeFormatter dateTimeFormatter1 = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYYMMDD);
            String timezone = propertyDTO.getTimezone();
            LocalDate importStartDate = DateTimeUtils.toZonedDateTime(startDate, timezone).toLocalDate();
            LocalDate importEndDate = DateTimeUtils.toZonedDateTime(endDate, timezone).toLocalDate();

            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            List<BookingBO> bookingBOS = Lists.newArrayListWithExpectedSize(sheet.getLastRowNum());

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (Objects.isNull(row)) {
                    continue;
                }

                Cell bookedDateCell = row.getCell(0);
                LocalDate bookedDate = LocalDate.parse(CellUtils.convertCellStr(bookedDateCell,
                        BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                if (bookedDate.isBefore(importStartDate) || bookedDate.isAfter(importEndDate)) {
                    continue;
                }

                Cell startDateCell = row.getCell(1);
                Cell endDateCell = row.getCell(2);
                Cell totalAmountCell = row.getCell(3);

                bookingBOS.add(new BookingBO(Integer.parseInt(LocalDate.parse(CellUtils.convertCellStr(startDateCell,
                        BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter).format(dateTimeFormatter1)),
                        Integer.parseInt(LocalDate.parse(CellUtils.convertCellStr(endDateCell,
                                BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter).format(dateTimeFormatter1)),
                        new BigDecimal(totalAmountCell.toString().trim()),
                        Integer.parseInt(bookedDate.format(dateTimeFormatter1))));
            }

            workbook.close();

            return bookingBOS;
        }

        /**
         * 检查文件
         *
         * @param propertyDTO 资产
         * @param file        文件
         * @author yyt
         * @date 2024/8/6
         **/
        @Override
        public void checkFile(PropertyDTO propertyDTO, MultipartFile file) {
            // 限制文件大小
            FileCheckUtils.checkFileSize(file, importReportFileMaxSize);
            // 校验文件格式
            UploadUtils.checkFileFormat(file, Lists.newArrayList("xlsx", "xls"));

            try (InputStream inputStream = file.getInputStream()) {
                Workbook workbook = WorkbookFactory.create(inputStream);
                Sheet sheet = workbook.getSheetAt(0);
                Row titleRow = sheet.getRow(0);
                if (Objects.isNull(titleRow)) {
                    throw new BadRequestException(CommonErrorI18nConstant.IMPORT_REPORT_NOT_REQUIRED,
                            BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                            "The imported report is not the one required for the current functionality");
                }

                String bookedDateTitle = CellUtils.convertCellStr(titleRow.getCell(0));
                String startDateTitle = CellUtils.convertCellStr(titleRow.getCell(1));
                String endDateTitle = CellUtils.convertCellStr(titleRow.getCell(2));
                String totalAmountTitle = CellUtils.convertCellStr(titleRow.getCell(3));

                DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (Objects.isNull(row)) {
                        continue;
                    }

                    Cell bookedDateCell = row.getCell(0);
                    if (Objects.isNull(bookedDateCell) || StringUtils.isBlank(bookedDateCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(bookedDateTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The booked date cannot be empty");
                    } else {
                        try {
                            LocalDate.parse(CellUtils.convertCellStr(bookedDateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(bookedDateTitle, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The booked date format error");
                        }
                    }

                    Cell startDateCell = row.getCell(1);
                    if (Objects.isNull(startDateCell) || StringUtils.isBlank(startDateCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(startDateTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The start date cannot be empty");
                    } else {
                        try {
                            LocalDate.parse(CellUtils.convertCellStr(startDateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(startDateTitle, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The start date format error");
                        }
                    }

                    Cell endDateCell = row.getCell(2);
                    if (Objects.isNull(endDateCell) || StringUtils.isBlank(endDateCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(endDateTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The end date cannot be empty");
                    } else {
                        try {
                            LocalDate.parse(CellUtils.convertCellStr(endDateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.FORMAT_INCORRECT,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(endDateTitle, BaseConstant.DATE_FORMAT_YYYY_MM_DD),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The end date format error");
                        }
                    }

                    // 开始日期不能晚于结束日期
                    LocalDate startDate = LocalDate.parse(CellUtils.convertCellStr(startDateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                    LocalDate endDate = LocalDate.parse(CellUtils.convertCellStr(endDateCell, BaseConstant.DATE_FORMAT_YYYY_MM_DD), dateTimeFormatter);
                    if (startDate.isAfter(endDate)) {
                        throw new BadRequestException(CommonErrorI18nConstant.START_DATE_CANNOT_GREATER_THAN_END_DATE,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The start date must be less than or equal to the end date");
                    }

                    // 金额 整数位最大10位，小数根据国际化配置限制
                    Cell totalAmountCell = row.getCell(3);
                    if (Objects.isNull(totalAmountCell) || StringUtils.isBlank(totalAmountCell.toString())) {
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY,
                                i18nMessageUtils.getErrorParamMapNotNeedTranslate(totalAmountTitle),
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The total amount cannot be empty");
                    } else {
                        BigDecimal totalAmount;
                        try {
                            totalAmount = new BigDecimal(totalAmountCell.toString().trim());
                        } catch (Exception e) {
                            throw new BadRequestException(CommonErrorI18nConstant.MUST_NUMBER,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(totalAmountTitle),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The total amount must be number");
                        }
                        if (totalAmount.abs().toBigInteger().toString().length() > 10) {
                            throw new BadRequestException(CommonErrorI18nConstant.NUMBER_INTEGER_OVERLENGTH,
                                    i18nMessageUtils.getErrorParamMapNotNeedTranslate(totalAmountTitle, 10),
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The total amount exceed the limit");
                        }
                    }
                }

                workbook.close();
            } catch (BadRequestException e) {
                throw e;
            } catch (Exception e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "Parsing file failed", e);
            }
        }

        /**
         * 处理导入记录
         *
         * @param propertyDTO 资产
         * @param startDate   开始日期
         * @param endDate     结束日期
         * @param file        文件
         * @param userId      操作人
         * @return 导入记录
         * @author yyt
         * @date 2024/8/6
         **/
        @Override
        public BookingImportLog handleImportLog(PropertyDTO propertyDTO, Long startDate, Long endDate,
                                                MultipartFile file, int userId) {
            String key = toS3(propertyDTO.getTenantSymbol(), propertyDTO.getAbbr(), file);
            return bookingImportLogService.saveRecord(propertyDTO.getId(), startDate, endDate,
                    ReportImportResultCons.IMPORT_IN_PROGRESS, userId, key);
        }

        /**
         * 上传文件至 s3
         *
         * @param tenantSymbol   租户标识
         * @param propertySymbol 资产标识
         * @param file           文件
         * @return 文件路径
         * @author yyt
         * @date 2024/8/6
         **/
        private String toS3(String tenantSymbol, String propertySymbol, MultipartFile file) {
            String key = tenantSymbol + "/" + propertySymbol + "/" + s3ManualImportKey +
                    UploadUtils.getRandomFileName(file.getOriginalFilename());
            try {
                amazonS3Service.uploadFile(S3BucketFunctionCons.BOOKING_ANALYSIS_IMPORT_RECORD, file, key);
            } catch (IOException e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "Uploading to s3 failure", e);
            }
            return key;
        }

        /**
         * 处理文件
         *
         * @param propertyDTO 资产
         * @param importLog   导入记录
         * @param userId      操作人
         * @author yyt
         * @date 2024/8/6
         **/
        @Async
        @Override
        @Transactional(rollbackFor = Exception.class)
        public void handleFile(PropertyDTO propertyDTO, BookingImportLog importLog, Integer userId) {
            boolean importSuccess = true;
            try (InputStream inputStream = amazonS3Service.getFile(S3BucketFunctionCons.BOOKING_ANALYSIS_IMPORT_RECORD, importLog.getUrl())) {
                List<BookingBO> bookingBOS = parsReport(propertyDTO, inputStream, importLog.getFromDate(), importLog.getToDate());
                if (CollectionUtils.isEmpty(bookingBOS)) {
                    return;
                }

                Integer propertyId = propertyDTO.getId();
                long now = DateTimeUtils.nowTimestamp();
                bookingService.saveBatchRewrite(bookingBOS.parallelStream().map(item -> {
                    Booking booking = beanMapper.map(item, Booking.class);
                    booking.setPropertyId(propertyId).setUpdatedBy(userId).setUpdatedTime(now);
                    bookingService.setChartDataAttribute(booking, propertyDTO);
                    return booking;
                }).collect(Collectors.toList()));

                // 计算图表展示需要的数据
                String timezone = propertyDTO.getTimezone();
                LocalDate startMonth = DateTimeUtils.longToLocalDate(importLog.getFromDate(), timezone).withDayOfMonth(1);
                LocalDate endMonth = DateTimeUtils.longToLocalDate(importLog.getToDate(), timezone).withDayOfMonth(1);
                bookingService.calculateChartData(propertyDTO, startMonth, endMonth);
            } catch (Exception e) {
                importSuccess = false;
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse booking report error", e);
            } finally {
                if (importSuccess) {
                    importSuccess(importLog);
                } else {
                    importFail(importLog);
                }
            }
        }

        /**
         * 导入成功
         *
         * @param importLog 导入记录
         * @author yyt
         * @date 2024/8/6
         **/
        private void importSuccess(BookingImportLog importLog) {
            TransactionUtils.afterCommitSyncExecute(() -> {
                // 更新导入记录
                BookingImportLog updateLog = new BookingImportLog();
                updateLog.setId(importLog.getId());
                updateLog.setImportResult(ReportImportResultCons.IMPORT_RESULT_SUCCESS);
                bookingImportLogService.updateById(updateLog);
            });
        }

        /**
         * 导入失败
         *
         * @param importLog 导入记录
         * @author yyt
         * @date 2024/8/6
         **/
        private void importFail(BookingImportLog importLog) {
            TransactionUtils.afterCompletionRollbackSyncExecute(() -> {
                // 更新导入记录
                BookingImportLog updateLog = new BookingImportLog();
                updateLog.setId(importLog.getId());
                updateLog.setImportResult(ReportImportResultCons.IMPORT_RESULT_FAIL);
                bookingImportLogService.updateById(updateLog);
            });
        }

    }