# ParseFileServiceByOther.java

    package cloud.wconnect.frontoffice.parsereport.badguest.impl;

    import cloud.wconnect.commonlib.cons.*;
    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.CellUtils;
    import cloud.wconnect.commonlib.util.DateTimeUtils;
    import cloud.wconnect.commonlib.util.I18nMessageUtils;
    import cloud.wconnect.commonlib.util.UploadUtils;
    import cloud.wconnect.frontoffice.cons.badguest.BgReportTypeEnum;
    import cloud.wconnect.frontoffice.parsereport.badguest.IParseFileService;
    import cloud.wconnect.frontoffice.pojo.dto.badguest.GuestInHouseDTO;
    import cloud.wconnect.frontoffice.service.badguest.IBadGuestNotifyService;
    import cloud.wconnect.frontoffice.service.badguest.IBgReportLogService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.collections.CollectionUtils;
    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.context.annotation.Lazy;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.web.multipart.MultipartFile;

    import java.io.IOException;
    import java.io.InputStream;
    import java.text.MessageFormat;
    import java.time.LocalDate;
    import java.time.ZoneId;
    import java.time.format.DateTimeFormatter;
    import java.time.format.DateTimeParseException;
    import java.util.*;

    /**
     * 处理导入的other类型报告
     *
     * @author lichen
     * @date 2024/7/29
     */
    @Slf4j
    @Service
    @DS("logbook")
    public class ParseFileServiceByOther implements IParseFileService {

        @Autowired
        @Lazy
        private IBadGuestNotifyService notifyService;
        @Autowired
        private IBgReportLogService bgReportLogService;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;

        /**
         * 支持的文件来源 、文件类型
         *
         * @param source 来源
         * @param type   类型
         * @return boolean
         * @author lichen
         * @date 2024/7/30
         */
        @Override
        public boolean supports(String source, int type) {
            List<Integer> typeList = Arrays.stream(BgReportTypeEnum.values()).map(BgReportTypeEnum::getType).toList();
            return SystemPlatformEnum.OTHER.getDesc().equals(source) && typeList.contains(type);
        }

        /**
         * 解析文件数据
         *
         * @param in          文件流
         * @param propertyDTO 资产DTO
         * @return java.util.List<cloud.wconnect.frontoffice.pojo.dto.badguest.GuestInHouseDTO>
         * @author lichen
         * @date 2024/7/29
         */
        @Override
        public List<GuestInHouseDTO> parse(InputStream in, PropertyDTO propertyDTO) throws ParseReportException {
            ArrayList<GuestInHouseDTO> guestInHouseDTOList = Lists.newArrayList();
            try(Workbook workbook = WorkbookFactory.create(in)) {
                Sheet sheet = workbook.getSheetAt(0);
                //获取最大行数
                int lastRowNum = sheet.getLastRowNum();
                //只包含标题
                if (lastRowNum < 1) Collections.emptyList();

                ZoneId zoneId = ZoneId.of(propertyDTO.getTimezone());
                DateTimeFormatter formatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD, Locale.ENGLISH);

                for (int i = 1; i <= lastRowNum; i++) {
                    Row row = sheet.getRow(i);
                    GuestInHouseDTO guestInHouse = new GuestInHouseDTO();
                    guestInHouse.setPropertyId(propertyDTO.getId());
                    guestInHouse.setLastName(CellUtils.convertCellStr(row.getCell(0)));
                    guestInHouse.setFirstName(CellUtils.convertCellStr(row.getCell(1)));
                    //入住日期
                    String checkInDateStr = CellUtils.convertCellStr(row.getCell(2));
                    guestInHouse.setCheckInDate(LocalDate.parse(checkInDateStr, formatter)
                            .atStartOfDay(zoneId).toEpochSecond());
                    //退房日期
                    String checkOutDateStr = CellUtils.convertCellStr(row.getCell(3));
                    guestInHouse.setCheckOutDate(LocalDate.parse(checkOutDateStr, formatter)
                            .atStartOfDay(zoneId).toEpochSecond());

                    guestInHouse.setCreatedBy(0);
                    guestInHouse.setCreatedTime(DateTimeUtils.nowTimestamp());
                    guestInHouseDTOList.add(guestInHouse);
                }
                return guestInHouseDTOList;
            } catch (IOException ioException) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse check in and reservation info excel error");
            }
        }

        /**
         * 校验文件内容是否合法
         *
         * @param file 文件
         * @return void
         * @author lichen
         * @date 2024/7/29
         */
        @Override
        public void checkFile(MultipartFile file) {
            //校验文件格式
            UploadUtils.checkFileFormat(file, Lists.newArrayList("xlsx", "xls"));
            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 < 4; i++) {
                    titles.add(CellUtils.convertCellStr(titleRow.getCell(i)));
                }

                DateTimeFormatter formatter = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD, Locale.ENGLISH);

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

                    //last name
                    String lastName = CellUtils.convertCellStr(row.getCell(0));
                    if (StringUtils.isEmpty(lastName)) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(0));
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The last name cannot be null");
                    } else {
                        if (lastName.length() > 30) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(0),
                                    "30");
                            throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The last name length cannot be greater than 30, lastName = " + lastName);
                        }
                    }

                    //first name
                    String firstName = CellUtils.convertCellStr(row.getCell(1));
                    if (StringUtils.isEmpty(firstName)) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(1));
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The first name cannot be null");
                    } else {
                        if (firstName.length() > 30) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(1),
                                    "30");
                            throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The first name length cannot be greater than 30, firstName = " + firstName);
                        }
                    }

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

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

                    //校验入住日期不能晚于退房日期
                    if (checkInDateOfLocalDate.isAfter(checkOutDateOfLocalDate)) {
                        throw new BadRequestException(CommonErrorI18nConstant.CHECK_IN_DATE_CANNOT_GREATER_THAN_CHECK_OUT_DATE,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                MessageFormat.format("Check-in Date Cannot Be Later Than Check-out Date, check-in date:{0}, check-out date:{1}", checkInDateStr, checkOutDateStr));
                    }
                }
            } catch (IOException ioException) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse check in and reservation info excel error");
            }
        }

        /**
         * 处理数据
         *
         * @param propertyDTO 资产DTO
         * @param file        文件
         * @param userId      操作人ID
         * @param type        文件类型
         * @return void
         * @author lichen
         * @date 2024/7/29
         */
        @Override
        @Transactional(rollbackFor = Exception.class)
        public void handleData(PropertyDTO propertyDTO, MultipartFile file, int userId, int type) {
            try (InputStream inputStream = file.getInputStream()) {
                List<GuestInHouseDTO> guestInHouseDTOS = parse(inputStream, propertyDTO);
                if (CollectionUtils.isEmpty(guestInHouseDTOS)) return;
                //发送黑名单客人入住预警邮件
                notifyService.notifyBadGuestCheckIn(guestInHouseDTOS, propertyDTO);
                //保存手动导入报告时间
                bgReportLogService.save(propertyDTO, type, userId);
            } catch (ParseReportException e) {
                throw e;
            } catch (IOException e) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse check in and reservation info excel error");
            }
        }
    }