# ReservationForecastListenerFileParseByShiJiImpl.java

    package cloud.wconnect.rateshop.service.revenue.reservationforecast;

    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.cons.SystemPlatformEnum;
    import cloud.wconnect.commonlib.util.DateTimeUtils;
    import cloud.wconnect.commonlib.util.NumberUtil;
    import cloud.wconnect.rateshop.cons.revenue.RevenueCons;
    import cloud.wconnect.rateshop.entity.revenue.RevenueMarketCode;
    import cloud.wconnect.rateshop.entity.revenue.RevenueSegment;
    import cloud.wconnect.rateshop.pojo.bo.revenue.*;
    import cloud.wconnect.rateshop.service.revenue.IRevenueImportService;
    import cloud.wconnect.rateshop.service.revenue.IRevenueMarketCodeService;
    import cloud.wconnect.rateshop.service.revenue.IRevenueSegmentService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    import com.google.common.collect.Maps;
    import org.apache.commons.collections.CollectionUtils;
    import org.apache.commons.collections.MapUtils;
    import org.apache.commons.csv.CSVFormat;
    import org.apache.commons.csv.CSVRecord;
    import org.apache.commons.lang3.StringUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;

    import java.io.InputStream;
    import java.io.InputStreamReader;
    import java.math.BigDecimal;
    import java.util.*;
    import java.util.stream.Collectors;

    /**
     * @author yyt
     * 2023/11/3
     */
    @Service
    @DS("rate_shop")
    public class ReservationForecastListenerFileParseByShiJiImpl implements IReservationForecastListenerFileParse {

        @Autowired
        private IRevenueSegmentService revenueSegmentService;
        @Autowired
        private IRevenueMarketCodeService revenueMarketCodeService;
        @Autowired
        private IRevenueImportService revenueImportService;
        @Autowired
        private IRFParseCommonService rfParseCommonService;

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

        /**
         * 判断支持的平台
         *
         * @param source 平台
         * @return boolean
         * @author yyt
         * @date 2023/11/3
         */
        @Override
        public boolean supports(String source) {
            return SOURCE.equals(source);
        }

        /**
         * 解析报告数据
         *
         * @param propertyId 资产 id
         * @param timeZone   时区
         * @param in         文件流
         * @param date       指定日期
         * @param startDate  开始日期
         * @param endDate    结束日期
         * @return Segment
         * @author yyt
         * @date 2023/11/3
         */
        @Override
        public SegmentBO parseReport(Integer propertyId, String timeZone, InputStream in, String date,
                                           Long startDate, Long endDate) throws Exception {
            List<RevenueSegment> segments = revenueSegmentService.listActiveSegment(propertyId);
            if (CollectionUtils.isEmpty(segments)) {
                return new SegmentBO(date, Collections.emptyList());
            }

            Map<Integer, List<String>> pmsSegmentsById = segments.stream().collect(Collectors.toMap(RevenueSegment::getId,
                    segment -> Lists.newArrayList(StringUtils.split(segment.getPmsSegment(), ","))));

            int size = segments.size();
            Map<Integer, BigDecimal> revenueBySegmentId = Maps.newHashMapWithExpectedSize(size);
            Map<Integer, Integer> roomsBySegmentId = Maps.newHashMapWithExpectedSize(size);

            // 解析文件数据
            Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(new InputStreamReader(in));
            // 先取第一行数据，锁定 market code 所属 列
            Iterator<CSVRecord> iterator = records.iterator();
            if (!iterator.hasNext()) return new SegmentBO(date, Collections.emptyList());

            iterator.next();
            Map<String, Integer> marketCodeByIndex = getMarketCodeByIndex(iterator.next());
            if(MapUtils.isEmpty(marketCodeByIndex)) return new SegmentBO(date, Collections.emptyList());

            // 先取出所有行
            List<CSVRecord> allRows = getAllRecord(records);
            // 解析数据
            for (int i = 0; i < allRows.size(); i++) {
                CSVRecord record = allRows.get(i);
                String title = record.get(0);
                if (StringUtils.isEmpty(title)) continue;
                if (title.equals("Rooms Sold")) {
                    // 上一行为 日期，下一行为 revenue
                    // 检查日期是否在区间内
                    CSVRecord dateRecord = allRows.get(i - 1);
                    String dateStr = dateRecord.get(0);
                    if (StringUtils.isEmpty(dateStr)) continue;
                    if (dateStr.trim().equals("Total")) break;

                    if (dateStr.length() < 8) continue;
                    dateStr = dateStr.substring(dateStr.length() - 8);
                    if (StringUtils.isEmpty(dateStr)) continue;

                    long dataDate = DateTimeUtils.dateStrToEpochSecond(dateStr, BaseConstant.DATE_TIME_FORMAT_MM_DD_YY, timeZone);
                    if (dataDate < startDate || dataDate > endDate) {
                        continue;
                    }

                    int finalI = i;
                    marketCodeByIndex.forEach((marketCode, index) -> {
                        pmsSegmentsById.forEach((segmentId, operaSegments) -> {
                            if (!operaSegments.contains(marketCode)) return;

                            String rooms = NumberUtil.removeComma(record.get(index));
                            if (StringUtils.isNotEmpty(rooms)) {
                                roomsBySegmentId.put(segmentId, roomsBySegmentId.getOrDefault(segmentId, 0) + Integer.parseInt(rooms));
                            }

                            CSVRecord revenueRecord = allRows.get(finalI + 1);
                            if (revenueRecord.size() > BaseConstant.ZERO) {
                                String revenueStr = NumberUtil.removeComma(revenueRecord.get(index));
                                if (StringUtils.isNotEmpty(revenueStr)) {
                                    revenueBySegmentId.put(segmentId, revenueBySegmentId.getOrDefault(segmentId, BigDecimal.ZERO).add(new BigDecimal(revenueStr)));
                                }
                            }
                        });
                    });
                }
            }

            if (MapUtils.isEmpty(revenueBySegmentId) && MapUtils.isEmpty(roomsBySegmentId)) {
                return new SegmentBO(date, Collections.emptyList());
            }

            return new SegmentBO(date, segments.stream().map(item -> {
                Integer id = item.getId();
                return new RoomsAndRevenueBO(id, roomsBySegmentId.get(id), revenueBySegmentId.get(id));
            }).toList());
        }

        /**
         * 取出 market code 所属列
         *
         * @param marketCodeRecord market code record
         * @return market code 所属列
         * @author yyt
         * @date 2025/7/7
         **/
        private Map<String, Integer> getMarketCodeByIndex(CSVRecord marketCodeRecord) {
            if (marketCodeRecord.size() == BaseConstant.ZERO) return Collections.emptyMap();

            Map<String, Integer> marketCodeByIndex = Maps.newHashMap();
            for (int i = 0; i < marketCodeRecord.size(); i++) {
                String marketCode = marketCodeRecord.get(i);
                if (StringUtils.isEmpty(marketCode)) continue;
                if (marketCode.equals("Total")) break;

                marketCodeByIndex.put(marketCode, i);
            }
            return marketCodeByIndex;
        }

        /**
         * 取出所有数据行
         *
         * @param records record
         * @return 所有数据行
         * @author yyt
         * @date 2025/7/7
         **/
        private List<CSVRecord> getAllRecord(Iterable<CSVRecord> records) {
            List<CSVRecord> allRows = Lists.newArrayListWithExpectedSize(104 * 3);
            records.forEach(record -> {
                // 跳过前3行的标题
                if (record.getRecordNumber() < 4) {
                    return;
                }
                allRows.add(record);
            });
            return allRows;
        }

        /**
         * 解析报告数据
         *
         * @param propertyId 资产 id
         * @param timeZone   时区
         * @param in         文件流
         * @param startDate  开始日期
         * @param endDate    结束日期
         * @return MarketCodeBO
         * @author yyt
         * @date 2024/3/25
         */
        @Override
        public List<MarketCodeBO> parseReportForNext14Days(Integer propertyId, String timeZone, InputStream in,
                                              Long startDate, Long endDate) throws Exception {
            List<RevenueMarketCode> marketCodes = revenueMarketCodeService.listEffiective(propertyId);
            if (CollectionUtils.isEmpty(marketCodes)) {
                return Collections.emptyList();
            }

            Map<Integer, Map<String, Integer>> marketCodeByType = marketCodes.stream().collect(Collectors.groupingBy(RevenueMarketCode::getType,
                    Collectors.toMap(RevenueMarketCode::getMarketCode, RevenueMarketCode::getId)));
            Map<String, Integer> marketCodeByTransient = marketCodeByType.getOrDefault(RevenueCons.TRANSIENT, Collections.emptyMap());
            Map<String, Integer> marketCodeByGroup = marketCodeByType.getOrDefault(RevenueCons.GROUP, Collections.emptyMap());

            // 解析文件数据
            // 解析文件数据
            Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(new InputStreamReader(in));
            // 先取第一行数据，锁定 market code 所属 列
            Iterator<CSVRecord> iterator = records.iterator();
            if (!iterator.hasNext()) return Collections.emptyList();

            iterator.next();
            CSVRecord marketCodeRecord = iterator.next();
            if (marketCodeRecord.size() == BaseConstant.ZERO) return Collections.emptyList();

            Map<String, Integer> marketCodeByIndex = Maps.newHashMap();
            for (int i = 0; i < marketCodeRecord.size(); i++) {
                String marketCode = marketCodeRecord.get(i);
                if (StringUtils.isEmpty(marketCode)) continue;
                if(marketCode.equals("Total")) break;

                marketCodeByIndex.put(marketCode, i);
            }
            if(MapUtils.isEmpty(marketCodeByIndex)) return Collections.emptyList();

            List<CSVRecord> allRows = Lists.newArrayListWithExpectedSize(14 * 20);
            records.forEach(record -> {
                // 跳过前3行的标题
                if (record.getRecordNumber() < 4) {
                    return;
                }
                allRows.add(record);
            });

            List<MarketCodeBO> marketCodeBOs = Lists.newArrayList();
            // 解析其他行数据
            for (int i = 0; i < allRows.size(); i++) {
                CSVRecord record = allRows.get(i);
                String title = record.get(0);
                if (StringUtils.isEmpty(title)) continue;
                if (title.equals("Rooms Sold")) {
                    // 上一行为 日期，下一行为 revenue
                    // 检查日期是否在区间内
                    CSVRecord dateRecord = allRows.get(i - 1);
                    String dateStr = dateRecord.get(0);
                    if (StringUtils.isEmpty(dateStr)) continue;
                    if (dateStr.trim().equals("Total")) break;

                    dateStr = dateStr.substring(dateStr.length() - 8);
                    if (StringUtils.isEmpty(dateStr)) continue;

                    long dataDate = DateTimeUtils.dateStrToEpochSecond(dateStr, BaseConstant.DATE_TIME_FORMAT_MM_DD_YY, timeZone);
                    if (dataDate < startDate || dataDate > endDate) {
                        continue;
                    }

                    marketCodeByIndex.forEach((marketCode, index) -> {
                        int rooms = BaseConstant.ZERO;
                        // 查找当前 market code 下的房间数
                        String roomsStr = NumberUtil.removeComma(record.get(index));
                        if (StringUtils.isNotEmpty(roomsStr)) {
                            rooms = Integer.parseInt(roomsStr);
                        }

                        Integer transientMarketCodeId = marketCodeByTransient.get(marketCode);
                        if (Objects.nonNull(transientMarketCodeId)) {
                            marketCodeBOs.add(new MarketCodeBO(dataDate, transientMarketCodeId, rooms));
                        }
                        Integer groupMarketCodeId = marketCodeByGroup.get(marketCode);
                        if (Objects.nonNull(groupMarketCodeId)) {
                            marketCodeBOs.add(new MarketCodeBO(dataDate, groupMarketCodeId, rooms));
                        }
                    });
                }
            }

            return marketCodeBOs;
        }

        /**
         * 解析报告数据
         *
         * @param propertyId 资产 id
         * @param timeZone   时区
         * @param in         文件流
         * @param startDate  开始日期
         * @param endDate    结束日期
         * @return Segment
         * @author yyt
         * @date 2024/5/21
         */
        @Override
        public List<List<SegmentBO>> parseReportForNext90Days(Integer propertyId, String timeZone, InputStream in, Long startDate, Long endDate) throws Exception {
            List<RevenueSegment> segments = revenueSegmentService.listActiveSegment(propertyId);
            if (CollectionUtils.isEmpty(segments)) {
                return Collections.emptyList();
            }

            return rfParseCommonService.packageSegmentBO(propertyId, segments, startDate, endDate, parseFile(in, timeZone, startDate, endDate), timeZone);
        }

        /**
         * 解析文件节点数据
         *
         * @param in        文件
         * @param timeZone  时区
         * @param startDate 开始日期
         * @param endDate   结束日期
         * @return ReservationForecastBO
         * @author yyt
         * @date 2024/5/21
         **/
        private List<ReservationForecastBO> parseFile(InputStream in, String timeZone, Long startDate, Long endDate) throws Exception {
            // 解析文件数据
            Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(new InputStreamReader(in));
            // 先取第一行数据，锁定 market code 所属 列
            Iterator<CSVRecord> iterator = records.iterator();
            if (!iterator.hasNext()) return Collections.emptyList();

            iterator.next();
            Map<String, Integer> marketCodeByIndex = getMarketCodeByIndex(iterator.next());
            if(MapUtils.isEmpty(marketCodeByIndex)) return Collections.emptyList();

            // 先取出所有行
            List<CSVRecord> allRows = getAllRecord(records);
            List<ReservationForecastBO> reservationForecastBOList = Lists.newArrayListWithExpectedSize(allRows.size());
            // 解析数据
            for (int i = 0; i < allRows.size(); i++) {
                CSVRecord record = allRows.get(i);
                String title = record.get(0);
                if (StringUtils.isEmpty(title)) continue;
                if (title.equals("Rooms Sold")) {
                    // 上一行为 日期，下一行为 revenue
                    // 检查日期是否在区间内
                    CSVRecord dateRecord = allRows.get(i - 1);
                    String dateStr = dateRecord.get(0);
                    if (StringUtils.isEmpty(dateStr)) continue;
                    if (dateStr.trim().equals("Total")) break;

                    dateStr = dateStr.substring(dateStr.length() - 8);
                    if (StringUtils.isEmpty(dateStr)) continue;

                    long dataDate = DateTimeUtils.dateStrToEpochSecond(dateStr, BaseConstant.DATE_TIME_FORMAT_MM_DD_YY, timeZone);
                    if (dataDate < startDate || dataDate > endDate) {
                        continue;
                    }

                    int finalI = i;
                    List<ReservationForecastMarketCodeBO> reservationForecastMarketCodeBOList = Lists.newArrayListWithExpectedSize(marketCodeByIndex.size());
                    marketCodeByIndex.forEach((marketCode, index) -> {
                        int rooms = BaseConstant.ZERO;
                        String roomsStr = NumberUtil.removeComma(record.get(index));
                        if (StringUtils.isNotEmpty(roomsStr)) {
                            rooms = Integer.parseInt(roomsStr);
                        }

                        BigDecimal revenue = BigDecimal.ZERO;
                        CSVRecord revenueRecord = allRows.get(finalI + 1);
                        if (revenueRecord.size() > BaseConstant.ZERO) {
                            String revenueStr = NumberUtil.removeComma(revenueRecord.get(index));
                            if (StringUtils.isNotEmpty(revenueStr)) {
                                revenue = new BigDecimal(revenueStr);
                            }
                        }

                        ReservationForecastMarketCodeBO reservationForecastMarketCodeBO = new ReservationForecastMarketCodeBO();
                        reservationForecastMarketCodeBO.setMarketCode(marketCode);
                        reservationForecastMarketCodeBO.setDetail(Lists.newArrayList(new ReservationForecastMarketCodesBO(rooms, revenue)));
                        reservationForecastMarketCodeBOList.add(reservationForecastMarketCodeBO);
                    });

                    ReservationForecastBO reservationForecastBO = new ReservationForecastBO();
                    reservationForecastBO.setDate(dataDate);
                    reservationForecastBO.setMarketCodeBOS(reservationForecastMarketCodeBOList);
                    reservationForecastBOList.add(reservationForecastBO);
                }
            }

            return reservationForecastBOList;
        }

    }