# EventServiceImpl.java

    package cloud.wconnect.rateshop.service.rateshop.impl;

    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.exception.BadRequestException;
    import cloud.wconnect.commonlib.exception.InternalServerErrorException;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.pojo.dto.UpdateLog;
    import cloud.wconnect.commonlib.service.PropertyInternalService;
    import cloud.wconnect.commonlib.util.CellUtils;
    import cloud.wconnect.commonlib.util.DateTimeUtils;
    import cloud.wconnect.commonlib.util.I18nMessageUtils;
    import cloud.wconnect.commonlib.util.UpdateLogUtil;
    import cloud.wconnect.rateshop.cons.rateshop.EventDatasourceEnum;
    import cloud.wconnect.rateshop.cons.rateshop.RateShopCons;
    import cloud.wconnect.rateshop.cons.rateshop.RateShopErrorI18nConstant;
    import cloud.wconnect.rateshop.entity.rateshop.*;
    import cloud.wconnect.rateshop.mapper.rateshop.EventMapper;
    import cloud.wconnect.rateshop.pojo.dto.rateshop.event.EventDTO;
    import cloud.wconnect.rateshop.pojo.dto.rateshop.event.EventExcelDTO;
    import cloud.wconnect.rateshop.pojo.query.rateshop.EventQuery;
    import cloud.wconnect.rateshop.pojo.vo.rateshop.event.CalendarEventVO;
    import cloud.wconnect.rateshop.pojo.vo.rateshop.event.EventVO;
    import cloud.wconnect.rateshop.service.rateshop.*;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import com.google.common.collect.ImmutableMap;
    import com.google.common.collect.Lists;
    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.stereotype.Service;
    import org.springframework.web.multipart.MultipartFile;

    import java.io.IOException;
    import java.io.InputStream;
    import java.text.MessageFormat;
    import java.time.LocalDate;
    import java.util.*;
    import java.util.function.Function;
    import java.util.stream.Collectors;

    /**
     * <p>
     * 事件 服务实现类
     * </p>
     *
     * @author lichen
     * @since 2023-06-13
     */
    @DS("rate_shop")
    @Service
    public class EventServiceImpl extends ServiceImpl<EventMapper, Event> implements IEventService {

        @Autowired
        private UpdateLogUtil updateLogUtil;
        @Autowired
        private BeanMapper beanMapper;
        @Autowired
        private EventMapper eventMapper;
        @Autowired
        private IEventSourceSettingService eventSourceSettingService;
        @Autowired
        private IEventSourceLocationService eventSourceLocationService;
        @Autowired
        private PropertyInternalService propertyInternalService;
        @Autowired
        private IEventSourceUrlService eventSourceUrlService;
        @Autowired
        private I18nMessageUtils i18nMessageUtils;

        /**
         * 查询事件信息
         *
         * @param propertyDTO 资产DTO
         * @param eventQuery  查询对象
         * @return java.util.List<cloud.wconnect.rateshop.pojo.vo.event.CalendarEventVO>
         * @author lichen
         * @date 2023/6/19
         */
        @Override
        public List<CalendarEventVO> list(PropertyDTO propertyDTO, EventQuery eventQuery) {
            String fromDate = eventQuery.getFromDate();
            String toDate = eventQuery.getToDate();
            List<Integer> queryUrlId = eventQuery.getUrlId();

            //查询资产配置的事件源
            List<EventSourceSetting> eventSourceSettings = eventSourceSettingService.listByPropertyId(propertyDTO.getId());
            if (CollectionUtils.isEmpty(eventSourceSettings)) return Collections.emptyList();
            List<Integer> urlIdsBySetting = eventSourceSettings.stream().map(EventSourceSetting::getUrlId).collect(Collectors.toList());
            Set<Integer> locationSetBySetting = eventSourceSettings.stream().map(EventSourceSetting::getLocationId).collect(Collectors.toSet());

            List<Event> allEvents = new ArrayList<>();
            if (CollectionUtils.isEmpty(queryUrlId)) {//查询日期范围内所有事件（资产手动创建，自动创建，租户下同地区其他资产手动创建的事件）
                //当前资产手动创建的事件
                List<Event> manualCreateEvents = listManualCreate(propertyDTO.getId(),
                        Integer.parseInt(fromDate.replace("-", "")),
                        Integer.parseInt(toDate.replace("-", "")));
                allEvents.addAll(manualCreateEvents);

                //租户下同地区其他资产手动创建的事件
                List<Event> sameLocationElsePropertyManualCreateEvents = listManualCreateBySameLocation(propertyDTO, locationSetBySetting,
                        Integer.parseInt(fromDate.replace("-", "")),
                        Integer.parseInt(toDate.replace("-", "")));
                allEvents.addAll(sameLocationElsePropertyManualCreateEvents);

                //自动创建的事件
                List<Event> autoCreateEvents = listAutoCreate(urlIdsBySetting,
                        Integer.parseInt(fromDate.replace("-", "")),
                        Integer.parseInt(toDate.replace("-", "")));
                allEvents.addAll(autoCreateEvents);
            } else {
                if (queryUrlId.contains(EventQuery.MANUAL_CREATE_EVENT)) {
                    //当前资产手动创建的事件
                    List<Event> manualCreateEvents = listManualCreate(propertyDTO.getId(),
                            Integer.parseInt(fromDate.replace("-", "")),
                            Integer.parseInt(toDate.replace("-", "")));
                    allEvents.addAll(manualCreateEvents);
                }
                if (queryUrlId.contains(EventQuery.SHARE_EVENT)) {
                    //租户下同地区其他资产手动创建的事件
                    List<Event> sameLocationElsePropertyManualCreateEvents = listManualCreateBySameLocation(propertyDTO, locationSetBySetting,
                            Integer.parseInt(fromDate.replace("-", "")),
                            Integer.parseInt(toDate.replace("-", "")));
                    allEvents.addAll(sameLocationElsePropertyManualCreateEvents);
                }
                List<Integer> grabUrlId = Lists.newArrayList(queryUrlId);
                grabUrlId.removeAll(Lists.newArrayList(EventQuery.MANUAL_CREATE_EVENT, EventQuery.SHARE_EVENT));
                if (CollectionUtils.isNotEmpty(grabUrlId)) {
                    //自动创建的事件
                    List<Event> autoCreateEvents = listAutoCreate(grabUrlId,
                            Integer.parseInt(fromDate.replace("-", "")),
                            Integer.parseInt(toDate.replace("-", "")));
                    allEvents.addAll(autoCreateEvents);
                }
            }
            if (CollectionUtils.isEmpty(allEvents)) return Collections.emptyList();

            //查询资产信息
            Set<Integer> propertyIds = allEvents.stream().map(Event::getPropertyId).filter(propertyId -> propertyId != 0).collect(Collectors.toSet());
            Map<Integer, String> propertyId2Name = Collections.emptyMap();
            if (CollectionUtils.isNotEmpty(propertyIds)) {
                propertyId2Name = propertyInternalService.getProperty(propertyIds)
                        .stream().collect(Collectors.toMap(PropertyDTO::getId, PropertyDTO::getName));
            }
            Map<Integer, String> finalPropertyId2Name = propertyId2Name;

            //查询location信息
            List<Integer> locationIdsByEvents = allEvents.stream().map(Event::getLocationId).toList();
            Map<Integer, String> locationId2Location = eventSourceLocationService.listByIds(locationIdsByEvents)
                    .stream().collect(Collectors.toMap(EventSourceLocation::getId, EventSourceLocation::getLocation));

            //查询url信息
            List<Integer> urlIdsByEvent = allEvents.stream().map(Event::getUrlId).toList();
            List<EventSourceUrl> eventSourceUrls = eventSourceUrlService.listByIds(urlIdsByEvent);
            Map<Integer, EventSourceUrl> urlId2Url = eventSourceUrls.stream().collect(Collectors.toMap(EventSourceUrl::getId, Function.identity()));

            //时间转换为localDate
            LocalDate fromLocalDate = DateTimeUtils.strToLocalDate(fromDate, BaseConstant.DATE_FORMAT_YYYY_MM_DD);
            LocalDate toLocalDate = DateTimeUtils.strToLocalDate(toDate, BaseConstant.DATE_FORMAT_YYYY_MM_DD);

            List<CalendarEventVO> resultList = new ArrayList<>();

            //生成日历板
            for (LocalDate localDate = fromLocalDate; !localDate.isAfter(toLocalDate); localDate = localDate.plusDays(1)) {
                //把localDate转成Integer类型的时间（yyyyMMdd）
                int l = Integer.parseInt(DateTimeUtils.localDateToStr(localDate, BaseConstant.DATE_FORMAT_YYYYMMDD));
                List<Event> matchEvents = allEvents.stream()
                        .filter(event -> l >= event.getBeginDate() && l <= event.getEndDate())
                        .collect(Collectors.toList());
                if (CollectionUtils.isNotEmpty(matchEvents)) {
                    String date = DateTimeUtils.localDateToStr(localDate, BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                    //List<Event> -> List<EventVO>
                    List<EventVO> eventVOList = matchEvents.stream().map(event -> {
                        //获取更新日志
                        List<UpdateLog> updateLogs = updateLogUtil.strToList(event.getUpdateLog());
                        //获取最后一次的更新时间
                        Long timestamp = updateLogs.stream()
                                .max(Comparator.comparingLong(UpdateLog::getTimestamp)).get().getTimestamp();

                        //格式化时间
                        LocalDate beginLocalDate = DateTimeUtils.strToLocalDate(event.getBeginDate().toString(), BaseConstant.DATE_FORMAT_YYYYMMDD);
                        LocalDate endLocalDate = DateTimeUtils.strToLocalDate(event.getEndDate().toString(), BaseConstant.DATE_FORMAT_YYYYMMDD);

                        EventVO eventVO = EventVO.builder()
                                .id(event.getId())
                                .propertyId(event.getPropertyId())
                                .propertyName(finalPropertyId2Name.get(event.getPropertyId()))
                                .category(event.getCategory())
                                .title(event.getTitle())
                                .beginDate(DateTimeUtils.localDateToStr(beginLocalDate, BaseConstant.DATE_FORMAT_YYYY_MM_DD))
                                .endDate(DateTimeUtils.localDateToStr(endLocalDate, BaseConstant.DATE_FORMAT_YYYY_MM_DD))
                                //urlId大于0表示系统创建的事件，需要隐藏掉link字段
                                .link(event.getUrlId() > 0 ? "" : event.getLink())
                                .address(event.getAddress())
                                .updateTime(timestamp)
                                .description(event.getDescription())
                                .locationId(event.getLocationId())
                                .location(locationId2Location.get(event.getLocationId()))
                                .urlId(event.getUrlId())
                                .build();

                        if (Objects.nonNull(urlId2Url.get(event.getUrlId()))) {
                            EventSourceUrl eventSourceUrl = urlId2Url.get(event.getUrlId());
                            eventVO.setDatasourceId(eventSourceUrl.getDatasourceId());
                            eventVO.setSymbol(EventDatasourceEnum.getSymbol(eventSourceUrl.getDatasourceId()));
                        }
                        return eventVO;
                    }).collect(Collectors.toList());
                    resultList.add(new CalendarEventVO(date, eventVOList));
                }
            }
            return resultList;
        }

        /**
         * 租户下同地区其他资产手动创建的事件
         *
         * @param propertyDTO 资产DTO
         * @param locationIds 地址ID集合
         * @param beginDate   开始时间
         * @param endDate     结束时间
         * @return java.util.List<cloud.wconnect.rateshop.entity.rateshop.Event>
         * @author lichen
         * @date 2024/2/29
         */
        private List<Event> listManualCreateBySameLocation(PropertyDTO propertyDTO, Set<Integer> locationIds, int beginDate, int endDate) {
            if (CollectionUtils.isEmpty(locationIds)) return Collections.emptyList();
            return list(new LambdaQueryWrapper<Event>()
                    .eq(Event::getTenantId, propertyDTO.getTenantId())
                    .ne(Event::getPropertyId, propertyDTO.getId())
                    .in(Event::getLocationId, locationIds)
                    .and(queryWrapper ->
                            queryWrapper.between(Event::getBeginDate, beginDate, endDate)
                                    .or().between(Event::getEndDate, beginDate, endDate)
                                    .or(childQw -> childQw.le(Event::getBeginDate, beginDate).ge(Event::getEndDate, endDate))
                    ));
        }

        /**
         * 查询自动创建的事件信息
         *
         * @param urlIds   事件源id集合
         * @param fromDate 开始时间(yyyyMMdd)
         * @param toDate   结束时间(yyyyMMdd)
         * @return java.util.List<cloud.wconnect.rateshop.entity.rateshop.Event>
         * @author lichen
         * @date 2023/6/19
         */
        @Override
        public List<Event> listAutoCreate(List<Integer> urlIds, int fromDate, int toDate) {
            if (CollectionUtils.isEmpty(urlIds)) return Collections.emptyList();
            return list(new LambdaQueryWrapper<Event>()
                    .in(Event::getUrlId, urlIds)
                    .and(queryWrapper ->
                            queryWrapper.between(Event::getBeginDate, fromDate, toDate)
                                    .or().between(Event::getEndDate, fromDate, toDate)
                                    .or(childQw -> childQw.le(Event::getBeginDate, fromDate).ge(Event::getEndDate, toDate))
                    ));
        }

        /**
         * 查询手动创建的事件信息
         *
         * @param propertyId 资产id
         * @param fromDate   开始时间(yyyyMMdd)
         * @param toDate     结束时间(yyyyMMdd)
         * @return java.util.List<cloud.wconnect.rateshop.entity.rateshop.Event>
         * @author lichen
         * @date 2023/6/19
         */
        @Override
        public List<Event> listManualCreate(Integer propertyId, int fromDate, int toDate) {
            return list(new LambdaQueryWrapper<Event>()
                    .eq(Event::getPropertyId, propertyId)
                    .eq(Event::getUrlId, 0)
                    .and(queryWrapper ->
                            queryWrapper.between(Event::getBeginDate, fromDate, toDate)
                                    .or().between(Event::getEndDate, fromDate, toDate)
                                    .or(childQw -> childQw.le(Event::getBeginDate, fromDate).ge(Event::getEndDate, toDate))
                    ));
        }

        /**
         * 新增或修改事件信息
         *
         * @param propertyDTO 资产DTO
         * @param eventDTO    事件dto
         * @return java.util.Map<java.lang.String, java.lang.Integer>
         * @author lichen
         * @date 2023/6/19
         */
        @Override
        public Map<String, Integer> saveOrUpdate(PropertyDTO propertyDTO, EventDTO eventDTO) {
            //把yyyy-MM-dd的日期转化成时间戳
            long beginTimestamp = DateTimeUtils.dateStrToEpochSecond(eventDTO.getBeginDate(), BaseConstant.DATE_FORMAT_YYYY_MM_DD, propertyDTO.getTimezone());
            long endTimestamp = DateTimeUtils.dateStrToEpochSecond(eventDTO.getEndDate(), BaseConstant.DATE_FORMAT_YYYY_MM_DD, propertyDTO.getTimezone());
            //开始时间不能大于结束时间
            if (beginTimestamp > endTimestamp) {
                throw new BadRequestException(CommonErrorI18nConstant.START_DATE_CANNOT_GREATER_THAN_END_DATE,
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The endDate must be longer than the beginDate");
            }

            //校验是否和同租户下手动新增的事件重复
            //校验当前资产下手动新增的事件是否重复
            long count = count(new LambdaQueryWrapper<Event>()
                    .eq(Event::getBeginDate, eventDTO.getBeginDate().replace("-", ""))
                    .eq(Event::getTitle, eventDTO.getTitle())
                    .eq(Event::getLocationId, eventDTO.getLocationId())
                    .eq(Event::getTenantId, propertyDTO.getTenantId())
                    .ne(Objects.nonNull(eventDTO.getId()), Event::getId, eventDTO.getId()));
            if (count > 0) {
                String logMsg = MessageFormat.format("The event already exists; beginDate:{0}, title:{1}, locationId:{2} ",
                        eventDTO.getBeginDate(), eventDTO.getTitle(), eventDTO.getLocationId());
                throw new BadRequestException(RateShopErrorI18nConstant.EVENT_REPEAT,
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), logMsg);
            }

            //构建event对象
            Event event = Event.builder()
                    .id(eventDTO.getId())
                    .tenantId(eventDTO.getTenantId())
                    .propertyId(eventDTO.getPropertyId())
                    .locationId(eventDTO.getLocationId())
                    .urlId(0)
                    .category(StringUtils.isNotBlank(eventDTO.getCategory()) ? eventDTO.getCategory() : "")
                    .title(eventDTO.getTitle())
                    .beginDate(Integer.parseInt(eventDTO.getBeginDate().replace("-", "")))
                    .endDate(Integer.parseInt(eventDTO.getEndDate().replace("-", "")))
                    .link(StringUtils.isNotBlank(eventDTO.getLink()) ? eventDTO.getLink() : "")
                    .address(eventDTO.getAddress())
                    .description(eventDTO.getDescription())
                    .updateLog(eventDTO.getUpdateLog())
                    .build();
            //保存
            saveOrUpdate(event);
            return ImmutableMap.of("id", event.getId());
        }

        /**
         * 校验事件是否存在 且 是否手动创建
         *
         * @param id event主键id
         * @return cloud.wconnect.rateshop.entity.rateshop.Event
         * @author lichen
         * @date 2023/6/19
         */
        @Override
        public Event checkExistAndIsManualCreate(Integer id) {
            Event event = getById(id);
            if (Objects.isNull(event)) {
                throw new BadRequestException(CommonErrorI18nConstant.DATA_NOT_FOUND,
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "The event not found, eventId = " + id);
            }
            if (event.getPropertyId() == 0) {
                throw new BadRequestException(RateShopErrorI18nConstant.AUTO_IMPORT_EVENT_CANNOT_OPERATE,
                        BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                        "You can operate only manually created event data, eventId = " + id);
            }
            return event;
        }

        /**
         * 导入事件
         *
         * @param propertyDTO 资产DTO
         * @param file        文件
         * @param loginUserId 登录用户id
         * @return void
         * @author lichen
         * @date 2023/12/20
         */
        @Override
        public void importExcel(PropertyDTO propertyDTO, MultipartFile file, int loginUserId) {
            //解析excel
            List<EventExcelDTO> eventExcelDTOS = parseExcel(propertyDTO.getId(), file);
            if (CollectionUtils.isEmpty(eventExcelDTOS)) return;

            List<Event> events = beanMapper.mapList(eventExcelDTOS, Event.class, (dto, entity) -> {
                entity.setTenantId(propertyDTO.getTenantId());
                entity.setPropertyId(propertyDTO.getId());
                entity.setUrlId(0);
                entity.setBeginDate(Integer.valueOf(dto.getStartDateStr().replace("-", "")));
                entity.setEndDate(Integer.valueOf(dto.getEndDateStr().replace("-", "")));
                entity.setCategory(dto.getType());
                entity.setUpdateLog(updateLogUtil.get(loginUserId));
            });

            //查询同租户下其他资产手动创建的事件
            List<Event> elsePropertyManualEvents = eventMapper.listElsePropertyManualEventByBeginDateAndTitleAndLocationId(propertyDTO, events);
            if (CollectionUtils.isNotEmpty(elsePropertyManualEvents)) {
                List<String> existEventUniqueKey = elsePropertyManualEvents.stream().map(Event::beginDateAndTitleAndLocationIdKey).toList();
                //过滤掉已经存在的其他资产手动创建的事件
                events = events.stream().filter(event -> !existEventUniqueKey.contains(event.beginDateAndTitleAndLocationIdKey())).toList();
            }

            //根据唯一索引新增或修改
            if (CollectionUtils.isNotEmpty(events)) {
                eventMapper.saveOrUpdateByUnique(events);
            }
        }

        /**
         * 解析Excel
         *
         * @param propertyId 资产id
         * @param file       excel文件
         * @return java.util.List<cloud.wconnect.rateshop.pojo.dto.rateshop.event.EventExcelDTO>
         * @author lichen
         * @date 2023/12/20
         */
        private List<EventExcelDTO> parseExcel(int propertyId, MultipartFile file) {
            //查询资产配置的事件源
            List<EventSourceSetting> eventSourceSettings = eventSourceSettingService.listByPropertyId(propertyId);
            if (CollectionUtils.isEmpty(eventSourceSettings)) return Collections.emptyList();
            //查询地址信息
            Set<Integer> locationIds = eventSourceSettings.stream().map(EventSourceSetting::getLocationId).collect(Collectors.toSet());
            Map<String, Integer> location2LocationId = eventSourceLocationService.listByIds(locationIds)
                    .stream().collect(Collectors.toMap(EventSourceLocation::getLocation, EventSourceLocation::getId));
            //地址集合
            Set<String> locations = location2LocationId.keySet();

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

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

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

                    EventExcelDTO eventExcelDTO = new EventExcelDTO();

                    //标题
                    String title = CellUtils.convertCellStr(row.getCell(0));
                    if (StringUtils.isBlank(title)) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(0));
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The title cannot be null");
                    } else {
                        if (title.length() > 255) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(0), 255);
                            throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The title cannot be longer than 255, title =" + title);
                        }
                    }
                    eventExcelDTO.setTitle(title);

                    //类型
                    String type = CellUtils.convertCellStr(row.getCell(1));
                    if (StringUtils.isBlank(type)) type = "";
                    if (type.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 type cannot be longer than 255, type =" + type);
                    }
                    eventExcelDTO.setType(type);

                    //开始日期
                    String startDate = CellUtils.convertCellStr(row.getCell(2));
                    LocalDate startDateOfLocalDate;
                    if (StringUtils.isBlank(startDate)) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(2));
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The startDate cannot be null");
                    } else {
                        try {
                            startDateOfLocalDate = DateTimeUtils.strToLocalDate(startDate, BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                        } catch (Exception 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 startDate format is incorrect, startDate = " + startDate);
                        }
                    }
                    eventExcelDTO.setStartDateStr(startDate);

                    //结束日期
                    String endDate = CellUtils.convertCellStr(row.getCell(3));
                    LocalDate endDateLocalDate;
                    if (StringUtils.isBlank(endDate)) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(3));
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The endDate cannot be null");
                    } else {
                        try {
                            endDateLocalDate = DateTimeUtils.strToLocalDate(endDate, BaseConstant.DATE_FORMAT_YYYY_MM_DD);
                        } catch (Exception 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 endDate format is incorrect, endDate = " + endDate);
                        }
                    }
                    eventExcelDTO.setEndDateStr(endDate);

                    //校验开始时间不能大于结束时间
                    if (startDateOfLocalDate.isAfter(endDateLocalDate)) {
                        throw new BadRequestException(CommonErrorI18nConstant.START_DATE_CANNOT_GREATER_THAN_END_DATE,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                MessageFormat.format("Start Date Cannot Be Later Than End Dat, startDate:{0}, endDate:{1}", startDate, endDate));
                    }

                    String address = CellUtils.convertCellStr(row.getCell(4));
                    if (StringUtils.isBlank(address)) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(4));
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The address cannot be null");
                    } else {
                        if (address.length() > 255) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(4), 255);
                            throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The address cannot be longer than 255, address =" + address);
                        }
                    }
                    eventExcelDTO.setAddress(address);

                    String link = CellUtils.convertCellStr(row.getCell(5));
                    if (StringUtils.isBlank(link)) link = "";
                    if (link.length() > 255) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(5), 255);
                        throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                "The link cannot be longer than 255, link =" + link);
                    }
                    eventExcelDTO.setLink(link);

                    String location = CellUtils.convertCellStr(row.getCell(6));
                    if (StringUtils.isBlank(location)) {
                        //获取国际化的参数
                        Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(6));
                        throw new BadRequestException(CommonErrorI18nConstant.CANNOT_EMPTY, language2ParamList,
                                BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(), "The location cannot be null");
                    } else {
                        if (location.length() > 255) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(6), 255);
                            throw new BadRequestException(CommonErrorI18nConstant.OVERLENGTH, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    "The location cannot be longer than 255, location =" + location);
                        }
                        if (!locations.contains(location)) {
                            //获取国际化的参数
                            Map<String, List<Object>> language2ParamList = i18nMessageUtils.getErrorParamMapNotNeedTranslate(titles.get(6),
                                    StringUtils.join(locations, ","));
                            throw new BadRequestException(CommonErrorI18nConstant.INVALID_VALUE, language2ParamList,
                                    BaseErrorCodeEnum.DATA_VALIDATION_ERROR.getCode(),
                                    MessageFormat.format("The location must between in [{0}]", StringUtils.join(locations, ",")));
                        }
                    }
                    eventExcelDTO.setLocation(location);
                    eventExcelDTO.setLocationId(location2LocationId.get(location));

                    String desc = CellUtils.convertCellStr(row.getCell(7));
                    eventExcelDTO.setDescription(desc);

                    eventExcelDTOS.add(eventExcelDTO);
                }

                return eventExcelDTOS;
            } catch (IOException ioException) {
                throw new InternalServerErrorException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse event excel error");
            }
        }
    }