# ReservationStatisticsListenerFileParseByShiJiImpl.java

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

    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.cons.BaseErrorCodeEnum;
    import cloud.wconnect.commonlib.cons.SystemPlatformEnum;
    import cloud.wconnect.commonlib.exception.ParseReportException;
    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.RevenueCustomersRule;
    import cloud.wconnect.rateshop.entity.revenue.RevenueSegment;
    import cloud.wconnect.rateshop.pojo.bo.revenue.CustomersBO;
    import cloud.wconnect.rateshop.pojo.bo.revenue.RoomsAndRevenueBO;
    import cloud.wconnect.rateshop.pojo.bo.revenue.SegmentAndCustomersBO;
    import cloud.wconnect.rateshop.pojo.bo.revenue.SegmentBO;
    import cloud.wconnect.rateshop.service.AmazonS3Service;
    import cloud.wconnect.rateshop.service.revenue.IRevenueCustomersRuleService;
    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
     * 2025/7/7
     */
    @Service
    @DS("rate_shop")
    public class ReservationStatisticsListenerFileParseByShiJiImpl implements IReservationStatisticsListenerFileParse {

        @Autowired
        private IRevenueSegmentService revenueSegmentService;
        @Autowired
        private IRevenueCustomersRuleService revenueCustomersRuleService;
        @Autowired
        private AmazonS3Service amazonS3Service;

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

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

        /**
         * 解析报告数据
         *
         * @param propertyId 资产 id
         * @param timeZone   时区
         * @param path       文件 s3 路径
         * @param date       指定日期
         * @param startDate  开始日期
         * @param endDate    结束日期
         * @return SegmentAndCustomersBO
         * @author yyt
         * @date 2025/7/7
         */
        @Override
        public SegmentAndCustomersBO parseReport(Integer propertyId, String timeZone, String path, String date,
                                                 Long startDate, Long endDate) {
            SegmentBO segmentBO = parseSegment(propertyId, timeZone, path, date, startDate, endDate);

            CustomersBO customersBO = parseCustomers(propertyId, timeZone, path, startDate, endDate);

            return new SegmentAndCustomersBO(date, segmentBO, customersBO);
        }

        /**
         * 解析 segment
         *
         * @param propertyId 资产 id
         * @param timeZone   时区
         * @param path       文件 s3 路径
         * @param date       指定日期
         * @param startDate  开始日期
         * @param endDate    结束日期
         * @return Segment
         * @author yyt
         * @date 2024/4/9
         **/
        private SegmentBO parseSegment(Integer propertyId, String timeZone, String path, String date, Long startDate, Long endDate) {
            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);

            try (InputStream inputStream = amazonS3Service.getFile(path)) {
                // 解析文件数据
                Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(new InputStreamReader(inputStream));
                // 先取第一行数据，锁定 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);
                if(CollectionUtils.isEmpty(allRows)) return new SegmentBO(date, Collections.emptyList());

                // 取出日期，检查日期是否正确
                CSVRecord dateRecord = allRows.get(0);
                if (Objects.isNull(dateRecord) || dateRecord.size() == BaseConstant.ZERO)
                    return new SegmentBO(date, Collections.emptyList());

                String dateStr = dateRecord.get(0);
                if (StringUtils.isEmpty(dateStr)) return new SegmentBO(date, Collections.emptyList());

                dateStr = dateStr.substring(dateStr.length() - 8);
                if (StringUtils.isEmpty(dateStr)) return new SegmentBO(date, Collections.emptyList());

                long dataDate = DateTimeUtils.dateStrToEpochSecond(dateStr, BaseConstant.DATE_TIME_FORMAT_MM_DD_YY, timeZone);
                if (dataDate < startDate || dataDate > endDate) {
                    return new SegmentBO(date, Collections.emptyList());
                }

                // 解析数据
                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("Month to Date")) {
                        // 下一行为房间数，在下一行为 revenue
                        CSVRecord roomsRecord = allRows.get(i + 1);
                        CSVRecord revenueRecord = allRows.get(i + 2);

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

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

                                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)));
                                    }
                                }
                            });
                        });

                        break;
                    }
                }

                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));
                }).collect(Collectors.toList()));
            } catch (Exception e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse reservation statistics report error", e);
            }
        }

        /**
         * 解析 Customers
         *
         * @param propertyId 资产 id
         * @param timeZone   时区
         * @param path       文件 s3 路径
         * @param startDate  开始日期
         * @param endDate    结束日期
         * @return Customers
         * @author yyt
         * @date 2024/4/9
         **/
        private CustomersBO parseCustomers(Integer propertyId, String timeZone, String path, Long startDate, Long endDate) {
            Map<Integer, List<String>> ruleByType = revenueCustomersRuleService.list(propertyId).stream()
                    .collect(Collectors.toMap(RevenueCustomersRule::getType, item -> Arrays.stream(item.getRule().split(",")).toList()));
            if (MapUtils.isEmpty(ruleByType)) {
                return null;
            }

            List<String> groupRules = ruleByType.getOrDefault(RevenueCons.GROUP, Collections.emptyList());
            List<String> contractRules = ruleByType.getOrDefault(RevenueCons.CONTRACT, Collections.emptyList());

            BigDecimal totalRevenue = null;
            Integer totalRooms = null;
            BigDecimal groupRevenue = null;
            Integer groupRooms = null;
            BigDecimal contractRevenue = null;
            Integer contractRooms = null;

            try (InputStream inputStream = amazonS3Service.getFile(path)) {
                // 解析文件数据
                Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(new InputStreamReader(inputStream));
                // 先取第一行数据，锁定 market code 所属 列
                Iterator<CSVRecord> iterator = records.iterator();
                if (!iterator.hasNext()) return null;

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

                // 取出所有行
                List<CSVRecord> allRows = getAllRecord(records);
                if (CollectionUtils.isEmpty(allRows)) return null;

                // 取出日期，检查日期是否正确
                CSVRecord dateRecord = allRows.get(0);
                if (Objects.isNull(dateRecord) || dateRecord.size() == BaseConstant.ZERO) return null;

                String dateStr = dateRecord.get(0);
                if (StringUtils.isEmpty(dateStr)) return null;

                dateStr = dateStr.substring(dateStr.length() - 8);
                long dataDate = DateTimeUtils.dateStrToEpochSecond(dateStr, BaseConstant.DATE_TIME_FORMAT_MM_DD_YY, timeZone);
                if (dataDate < startDate || dataDate > endDate) {
                    return null;
                }

                // 解析数据
                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("Month to Date")) {
                        // 下一行为房间数，在下一行为 revenue
                        CSVRecord roomsRecord = allRows.get(i + 1);
                        CSVRecord revenueRecord = allRows.get(i + 2);

                        for (Map.Entry<String, Integer> entry : marketCodeByIndex.entrySet()) {
                            String marketCode = entry.getKey();
                            Integer index = entry.getValue();

                            if (roomsRecord.size() > BaseConstant.ZERO) {
                                String roomsStr = NumberUtil.removeComma(roomsRecord.get(index));
                                if (StringUtils.isNotEmpty(roomsStr)) {
                                    int rooms = Integer.parseInt(roomsStr);
                                    // 累计 total rooms
                                    if (Objects.isNull(totalRooms)) {
                                        totalRooms = BaseConstant.ZERO;
                                    }
                                    totalRooms += rooms;

                                    if (groupRules.contains(marketCode)) {
                                        // 累计 group rooms
                                        if (Objects.isNull(groupRooms)) {
                                            groupRooms = BaseConstant.ZERO;
                                        }
                                        groupRooms += rooms;
                                    }

                                    if (contractRules.contains(marketCode)) {
                                        // 累计 contract rooms
                                        if (Objects.isNull(contractRooms)) {
                                            contractRooms = BaseConstant.ZERO;
                                        }
                                        contractRooms += rooms;
                                    }
                                }
                            }

                            if (revenueRecord.size() > BaseConstant.ZERO) {
                                String revenueStr = NumberUtil.removeComma(revenueRecord.get(index));
                                if (StringUtils.isNotEmpty(revenueStr)) {
                                    BigDecimal revenue = new BigDecimal(revenueStr);

                                    // 累计 total revenue
                                    if (Objects.isNull(totalRevenue)) {
                                        totalRevenue = BigDecimal.ZERO;
                                    }
                                    totalRevenue = totalRevenue.add(revenue);

                                    if (groupRules.contains(marketCode)) {
                                        // 累计 group revenue
                                        if (Objects.isNull(groupRevenue)) {
                                            groupRevenue = BigDecimal.ZERO;
                                        }
                                        groupRevenue = groupRevenue.add(revenue);
                                    }

                                    if (contractRules.contains(marketCode)) {
                                        // 累计 contract revenue
                                        if (Objects.isNull(contractRevenue)) {
                                            contractRevenue = BigDecimal.ZERO;
                                        }
                                        contractRevenue = contractRevenue.add(revenue);
                                    }
                                }
                            }
                        }

                        break;
                    }
                }

                CustomersBO customersBO = new CustomersBO();

                if (Objects.nonNull(totalRevenue)) {
                    // Transient = Total - Group - Contract
                    if (Objects.nonNull(groupRevenue)) {
                        totalRevenue = totalRevenue.subtract(groupRevenue);
                    }
                    if (Objects.nonNull(contractRevenue)) {
                        totalRevenue = totalRevenue.subtract(contractRevenue);
                    }
                }
                customersBO.setTransientRevenue(totalRevenue);
                if (Objects.nonNull(totalRooms)) {
                    // Transient = Total - Group - Contract
                    if (Objects.nonNull(groupRooms)) {
                        totalRooms -= groupRooms;
                    }
                    if (Objects.nonNull(contractRooms)) {
                        totalRooms -= contractRooms;
                    }
                }
                customersBO.setTransientRooms(totalRooms);

                customersBO.setGroupRevenue(groupRevenue);
                customersBO.setGroupRooms(groupRooms);

                customersBO.setContractRevenue(contractRevenue);
                customersBO.setContractRooms(contractRooms);

                return customersBO;
            } catch (Exception e) {
                throw new ParseReportException(BaseErrorCodeEnum.SYSTEM_ERROR.getI18nCode(),
                        BaseErrorCodeEnum.SYSTEM_ERROR.getCode(), "parse reservation statistics report error", e);
            }
        }

        /**
         * 取出 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(30);
            records.forEach(record -> {
                // 跳过前3行的标题
                if (record.getRecordNumber() < 4) {
                    return;
                }
                allRows.add(record);
            });
            return allRows;
        }

    }