# HistoryAndForecastListenerFileParseByShiJiImpl.java

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

    import cloud.wconnect.commonlib.cons.BaseConstant;
    import cloud.wconnect.commonlib.cons.SystemPlatformEnum;
    import cloud.wconnect.commonlib.pojo.dto.PropertyDTO;
    import cloud.wconnect.commonlib.pojo.dto.RoomsConfigDTO;
    import cloud.wconnect.commonlib.pojo.dto.internationalization.InternationalizationNumberDTO;
    import cloud.wconnect.commonlib.redis.PropertyI18nRedisService;
    import cloud.wconnect.commonlib.service.RoomsConfigInternalService;
    import cloud.wconnect.commonlib.util.DateTimeUtils;
    import cloud.wconnect.commonlib.util.InternationalizationUtils;
    import cloud.wconnect.commonlib.util.ShijiFileCheckUtils;
    import cloud.wconnect.rateshop.cons.revenue.RevenueCons;
    import cloud.wconnect.rateshop.entity.revenue.RevenueAdrConfig;
    import cloud.wconnect.rateshop.pojo.bo.revenue.HistoryAndForecastBO;
    import cloud.wconnect.rateshop.pojo.bo.revenue.OtbAndTransientGroupBO;
    import cloud.wconnect.rateshop.pojo.bo.revenue.OtbRevenueBO;
    import cloud.wconnect.rateshop.pojo.bo.revenue.TransientGroupBO;
    import cloud.wconnect.rateshop.service.revenue.IRevenueAdrConfigService;
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.google.common.collect.Lists;
    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.math.RoundingMode;
    import java.time.format.DateTimeFormatter;
    import java.util.*;

    /**
     * @author yyt
     * 2025/7/4
     */
    @Service
    @DS("rate_shop")
    public class HistoryAndForecastListenerFileParseByShiJiImpl implements IHistoryAndForecastListenerFileParse {

        @Autowired
        private RoomsConfigInternalService roomsConfigInternalService;
        @Autowired
        private IRevenueAdrConfigService revenueAdrConfigService;
        @Autowired
        private PropertyI18nRedisService propertyI18nRedisService;

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

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

        /**
         * 解析报告数据
         *
         * @param propertyId 资产 id
         * @param timeZone   时区
         * @param in         文件流
         * @param startDate  开始日期
         * @param endDate    结束日期
         * @return Otb And TransientGroup
         * @author yyt
         * @date 2025/7/4
         */
        @Override
        public OtbAndTransientGroupBO parseReport(Integer propertyId, String timeZone, InputStream in, Long startDate, Long endDate) throws Exception {
            // 解析文件数据
            Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(new InputStreamReader(in));
            Iterator<CSVRecord> iterator = records.iterator();
            if (!iterator.hasNext()) return new OtbAndTransientGroupBO();

            // 先加载所有行
            List<CSVRecord> allRows = new ArrayList<>();
            records.forEach(record -> {
                // 跳过第一行的标题
                if (record.getRecordNumber() == 1) {
                    return;
                }
                allRows.add(record);
            });

            OtbRevenueBO otbRevenueBO = parseFileForOtb(propertyId, allRows, startDate, endDate, timeZone);
            List<TransientGroupBO> transientGroups = parseFileForTransientGroup(propertyId, timeZone, allRows, startDate, endDate);
            return new OtbAndTransientGroupBO(otbRevenueBO, transientGroups);
        }

        /**
         * 解析History and Forecast from thirty days ago to yesterday报告数据
         *
         * @param propertyDTO 资产
         * @param in          文件流
         * @param startDate   开始日期
         * @param endDate     结束日期
         * @return java.util.List<cloud.wconnect.rateshop.pojo.bo.revenue.HistoryAndForecastBO>
         * @author lichen
         * @date 2025/8/5
         */
        @Override
        public List<HistoryAndForecastBO> parseReportForTransientGroupLastYear(PropertyDTO propertyDTO, InputStream in,
                                                                               Long startDate, Long endDate) throws Exception {
            // 解析文件数据
            Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(new InputStreamReader(in));
            Iterator<CSVRecord> iterator = records.iterator();
            if (!iterator.hasNext()) return Collections.emptyList();

            // 最多31天数据
            List<HistoryAndForecastBO> historyAndForecastBOS = Lists.newArrayListWithExpectedSize(31);
            String timeZone = propertyDTO.getTimezone();
            DateTimeFormatter dateTimeFormatter2 = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
            InternationalizationNumberDTO number = propertyDTO.getInternationalization().getNumber();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());

            // 先加载所有行
            List<CSVRecord> allRows = Lists.newArrayListWithExpectedSize(104 * 3);
            records.forEach(record -> {
                // 跳过第一行的标题
                if (record.getRecordNumber() == 1) {
                    return;
                }
                allRows.add(record);
            });

            for (int i = 0; i < allRows.size(); i+=3) {
                CSVRecord record = allRows.get(i);
                // 空行
                if (record.size() == BaseConstant.ZERO) continue;

                // 解析每天数据
                String date = ShijiFileCheckUtils.get(record, "A");
                if (StringUtils.isEmpty(date)) continue;
                String dateStr = date.substring(date.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;
                }

                HistoryAndForecastBO historyAndForecastBO = new HistoryAndForecastBO();
                historyAndForecastBO.setTargetDate(DateTimeUtils.strToLocalDate(dateStr, BaseConstant.DATE_TIME_FORMAT_MM_DD_YY).format(dateTimeFormatter2));

                String occPercentStr = ShijiFileCheckUtils.getAndFormatNumber(record, "E");
                if (StringUtils.isNotEmpty(occPercentStr)) {
                    historyAndForecastBO.setOccPercent(new BigDecimal(occPercentStr).setScale(BaseConstant.DATA_PRECISION, roundingMode));
                }

                // 如果勾选了 Transient deduct，则累加交订金的 Transient 房间数
                CSVRecord transientRecord = allRows.get(i + 1);
                String transientRoomsStr = ShijiFileCheckUtils.getAndFormatNumber(transientRecord, "F");
                if (StringUtils.isNotEmpty(transientRoomsStr)) {
                    historyAndForecastBO.setTransientRoomNum(Integer.valueOf(transientRoomsStr));
                }

                CSVRecord groupRecord = allRows.get(i + 2);
                // 如果勾选了 Group deduct，则累加交订金的 Group 房间数
                String groupRoomsStr = ShijiFileCheckUtils.getAndFormatNumber(groupRecord, "F");
                if (StringUtils.isNotEmpty(groupRoomsStr)) {
                    historyAndForecastBO.setGroupRoomNum(Integer.valueOf(groupRoomsStr));
                }

                String adrStr = ShijiFileCheckUtils.getAndFormatNumber(record, "P");
                if (StringUtils.isNotEmpty(adrStr)) {
                    historyAndForecastBO.setAdr(new BigDecimal(adrStr).setScale(BaseConstant.DATA_PRECISION, roundingMode));
                }
                historyAndForecastBOS.add(historyAndForecastBO);

                // 提前结束循环
                if (dataDate == endDate) {
                    break;
                }
            }
            return historyAndForecastBOS;
        }

        /**
         * 解析 Otb 数据
         *
         * @param propertyId 资产 id
         * @param records    文件
         * @param startDate  开始日期
         * @param endDate    结束日期
         * @param timeZone   时区
         * @return otb revenue
         * @author yyt
         * @date 2025/7/4
         */
        private OtbRevenueBO parseFileForOtb(Integer propertyId, List<CSVRecord> records, Long startDate, Long endDate, String timeZone) {
            BigDecimal totalRooms = null;
            BigDecimal totalRevenue = null;
            BigDecimal totalOcc = null;
            int totalDays = BaseConstant.ZERO;

            for (int i = 0; i < records.size(); i += 3) {
                CSVRecord record = records.get(i);
                // 空行
                if (record.size() == BaseConstant.ZERO) continue;

                // 解析每天数据
                String date = ShijiFileCheckUtils.get(record, "A");
                if (StringUtils.isEmpty(date)) continue;

                String dateStr = date.substring(date.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;
                }

                String roomsStr = ShijiFileCheckUtils.getAndFormatNumber(record, "B");
                if (StringUtils.isNotEmpty(roomsStr)) {
                    totalRooms = totalRooms == null ? new BigDecimal(roomsStr) : totalRooms.add(new BigDecimal(roomsStr));
                }
                String revenueStr = ShijiFileCheckUtils.getAndFormatNumber(record, "N");
                if (StringUtils.isNotEmpty(revenueStr)) {
                    totalRevenue = totalRevenue == null ? new BigDecimal(revenueStr) : totalRevenue.add(new BigDecimal(revenueStr));
                }
                String occStr = ShijiFileCheckUtils.getAndFormatNumber(record, "E");
                if (StringUtils.isNotEmpty(occStr)) {
                    totalOcc = totalOcc == null ? new BigDecimal(occStr) : totalOcc.add(new BigDecimal(occStr));
                }

                totalDays++;
            }

            InternationalizationNumberDTO number = propertyI18nRedisService.get(propertyId).getInternationalization().getNumber();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());

            OtbRevenueBO otbRevenue = new OtbRevenueBO();
            otbRevenue.setOtbRevenue(totalRevenue);
            otbRevenue.setOtbAdr(Objects.nonNull(totalRevenue) && Objects.nonNull(totalRooms) ? totalRevenue.divide(totalRooms, BaseConstant.DATA_PRECISION, roundingMode) : null);

            // 如果配置了房间数，则根据 occ 计算规则判断：1.使用配置的房间数计算 occ，2.直接使用文件中的 occ
            RoomsConfigDTO roomsConfig = roomsConfigInternalService.getRoomsConfig(propertyId);
            if (roomsConfig.getOccRule() == RevenueCons.USE_ROOMS_TO_CALCULATE) {
                if (Objects.nonNull(totalRooms)) {
                    //  Occ. % = Total Occ / (房间数 * 总天数)
                    BigDecimal occPercent = totalRooms.multiply(new BigDecimal("100"))
                            .divide(BigDecimal.valueOf((long) roomsConfig.getRooms() * totalDays), BaseConstant.DATA_PRECISION, roundingMode);
                    otbRevenue.setOtbOccPercent(occPercent);
                }
            } else {
                // 读取 Occ%
                if (Objects.nonNull(totalOcc)) {
                    otbRevenue.setOtbOccPercent(totalOcc.divide(BigDecimal.valueOf(totalDays), BaseConstant.DATA_PRECISION, roundingMode));
                }
            }
            return otbRevenue;
        }

        /**
         * 解析 Transient Group 数据
         *
         * @param propertyId 资产 id
         * @param timeZone   时区
         * @param records   文件
         * @param startDate  开始日期
         * @param endDate    结束日期
         * @return Transient And Group
         * @author yyt
         * @date 2023/10/30
         */
        private List<TransientGroupBO> parseFileForTransientGroup(Integer propertyId, String timeZone, List<CSVRecord> records, Long startDate, Long endDate) {
            RoomsConfigDTO roomsConfig = roomsConfigInternalService.getRoomsConfig(propertyId);
            Integer rooms = roomsConfig.getRooms();

            RevenueAdrConfig revenueAdrConfig = revenueAdrConfigService.get(propertyId);
            boolean checkTransientDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckTransientDeduct();
            boolean checkGroupDeduct = Objects.nonNull(revenueAdrConfig) && revenueAdrConfig.getCheckGroupDeduct();

            // 最多1个月的数据
            List<TransientGroupBO> transientGroups = Lists.newArrayListWithExpectedSize(31);
            DateTimeFormatter dateTimeFormatter2 = DateTimeFormatter.ofPattern(BaseConstant.DATE_FORMAT_YYYY_MM_DD);
            BigDecimal oneHundred = BigDecimal.valueOf(100L);
            InternationalizationNumberDTO number = propertyI18nRedisService.get(propertyId).getInternationalization().getNumber();
            RoundingMode roundingMode = InternationalizationUtils.getRoundingMode(number.getCalculationRule());

            for (int i = 0; i < records.size(); i += 3) {
                CSVRecord record = records.get(i);
                // 空行
                if (record.size() == BaseConstant.ZERO) continue;

                // 解析每天数据
                String date = ShijiFileCheckUtils.get(record, "A");
                if (StringUtils.isEmpty(date)) continue;
                String dateStr = date.substring(date.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;
                }

                TransientGroupBO transientGroup = new TransientGroupBO();
                transientGroup.setTargetDate(DateTimeUtils.strToLocalDate(dateStr, BaseConstant.DATE_TIME_FORMAT_MM_DD_YY).format(dateTimeFormatter2));

                // 如果配置了房间数，则根据 occ 计算规则判断：1.使用配置的房间数计算 occ，2.直接使用文件中的 occ
                if (roomsConfig.getOccRule() == RevenueCons.USE_ROOMS_TO_CALCULATE) {
                    String totalOccRoomsStr = ShijiFileCheckUtils.getAndFormatNumber(record, "B");
                    if (StringUtils.isNotEmpty(totalOccRoomsStr)) {
                        //  Occ. % = total occ rooms / 总房间数
                        BigDecimal occPercent = new BigDecimal(totalOccRoomsStr).divide(BigDecimal.valueOf(rooms),
                                BaseConstant.DATA_PRECISION, roundingMode).multiply(oneHundred);
                        transientGroup.setOccPercent(occPercent);
                    }
                } else {
                    String occPercentStr = ShijiFileCheckUtils.getAndFormatNumber(record, "E");
                    if (StringUtils.isNotEmpty(occPercentStr)) {
                        transientGroup.setOccPercent(new BigDecimal(occPercentStr).setScale(BaseConstant.DATA_PRECISION, roundingMode));
                    }
                }

                String adrStr = ShijiFileCheckUtils.getAndFormatNumber(record, "P");
                if (StringUtils.isNotEmpty(adrStr)) {
                    transientGroup.setAdr(new BigDecimal(adrStr).setScale(BaseConstant.DATA_PRECISION, roundingMode));
                }

                // 如果勾选了 Transient deduct，则累加交订金的 Transient 房间数
                CSVRecord transientRecord = records.get(i + 1);
                String transientRoomsStr = ShijiFileCheckUtils.getAndFormatNumber(transientRecord, "F");
                if (checkTransientDeduct && StringUtils.isNotEmpty(transientRoomsStr)) {
                    transientGroup.setTransientRoomNum(Integer.valueOf(transientRoomsStr));
                }
                BigDecimal transientRoomRevenue = null;
                String transientDeductRoomRevStr = ShijiFileCheckUtils.getAndFormatNumber(transientRecord, "N");
                if (checkTransientDeduct && StringUtils.isNotEmpty(transientDeductRoomRevStr)) {
                    transientRoomRevenue = new BigDecimal(transientDeductRoomRevStr);
                }
                Integer transientRoomNum = transientGroup.getTransientRoomNum();
                if (Objects.nonNull(transientRoomRevenue) && Objects.nonNull(transientRoomNum) && transientRoomNum != BaseConstant.ZERO) {
                    BigDecimal transientAdr = transientRoomRevenue.divide(BigDecimal.valueOf(transientRoomNum), BaseConstant.DATA_PRECISION, roundingMode);
                    transientGroup.setTransientAdr(transientAdr);
                }

                CSVRecord groupRecord = records.get(i + 2);
                // 如果勾选了 Group deduct，则累加交订金的 Group 房间数
                String groupRoomsStr = ShijiFileCheckUtils.getAndFormatNumber(groupRecord, "F");
                if (checkGroupDeduct && StringUtils.isNotEmpty(groupRoomsStr)) {
                    transientGroup.setGroupRoomNum(Integer.valueOf(groupRoomsStr));
                }
                BigDecimal groupRoomRevenue = null;
                String groupDeductRoomRevStr = ShijiFileCheckUtils.getAndFormatNumber(groupRecord, "N");
                if (checkGroupDeduct && !groupDeductRoomRevStr.isEmpty()) {
                    groupRoomRevenue = new BigDecimal(groupDeductRoomRevStr);
                }
                Integer groupRoomNum = transientGroup.getGroupRoomNum();
                if (Objects.nonNull(groupRoomRevenue) && Objects.nonNull(groupRoomNum) && groupRoomNum != BaseConstant.ZERO) {
                    BigDecimal groupAdr = groupRoomRevenue.divide(BigDecimal.valueOf(groupRoomNum), BaseConstant.DATA_PRECISION, roundingMode);
                    transientGroup.setGroupAdr(groupAdr);
                }

                // 计算 Rooms Available = 酒店房间数 - Total Occ. - OOO Rooms
                Integer roomsAvailable = rooms;
                if (Objects.nonNull(roomsAvailable)) {
                    String totalOcc = ShijiFileCheckUtils.getAndFormatNumber(record, "B");
                    String oooRooms = ShijiFileCheckUtils.getAndFormatNumber(record, "S");
                    if (StringUtils.isNotEmpty(totalOcc)) {
                        roomsAvailable -= Integer.parseInt(totalOcc);
                    }
                    if (StringUtils.isNotEmpty(oooRooms)) {
                        roomsAvailable -= Integer.parseInt(oooRooms);
                    }
                }
                transientGroup.setRoomsAvailable(roomsAvailable);

                transientGroups.add(transientGroup);

                // 提前结束循环
                if (dataDate == endDate) {
                    break;
                }
            }
            return transientGroups;
        }

    }