Z四

chengf

2026-01-27 b6184e2ddf3db37a94f7efb3b619bbc64642a292

 service-job/src/main/java/com/java110/job/importData/adapt/ImportReportMainV2QueueDataAdapt.java

@@ -7,14 +7,18 @@
import com.java110.dto.msg.MaintenancePaymentPo;
import com.java110.dto.msg.MpFifthPaymentRecordPo;
import com.java110.dto.msg.MpPaymentRecordPo;
import com.java110.fee.api.*;
import com.java110.job.dao.IMaintenancePaymentService;
import com.java110.job.dao.IMpFifthPaymentRecordService;
import com.java110.job.dao.IMpPaymentRecordService;
import com.java110.job.importData.DefaultImportData;
import com.java110.job.importData.IImportDataAdapt;
import com.java110.po.importFee.MaintenancePayment;
import com.java110.po.importFee.MpFifthPaymentRecord;
import com.java110.utils.util.BeanConvertUtil;
import com.java110.utils.util.DateUtil;
import com.java110.utils.util.NumberUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@@ -31,67 +35,212 @@
@Service("importReportMainV2QueueData")
public class ImportReportMainV2QueueDataAdapt extends DefaultImportData implements IImportDataAdapt {

    // 日期格式化器（处理Excel中的多种日期格式：2025/6/11、8/11/25、2025.5.13等）
    private static final SimpleDateFormat[] DATE_FORMATS = {
            new SimpleDateFormat("yyyy/MM/dd"),
            new SimpleDateFormat("MM/dd/yy"),
            new SimpleDateFormat("yyyy.MM.dd"),
            new SimpleDateFormat("yyyy-MM-dd")
    };

    @Autowired
    MaintenancePaymentApi maintenancePaymentApi;
    @Autowired
    AnnouncementTimeRangeApi announcementTimeRangeApi;
    @Autowired
    OwnersCommitteeConventionApi ownersCommitteeConventionApi;
    @Autowired
    OwnerWithdrawalInfoApi ownerWithdrawalInfoApi;
    @Autowired
    OwnerQualityGuaranteeApi ownerQualityGuaranteeApi;
    @Autowired
    MpPaymentRecordApi mpPaymentRecordApi;
    @Autowired
    MpFifthPaymentRecordApi mpFifthPaymentRecordApi;
    @Autowired
    OwnerRemarkInfoApi ownerRemarkInfoApi;

    // 注入数据库服务（SqlSessionTemplate版Service）
    @Autowired
    private IMaintenancePaymentService maintenancePaymentService;
    @Autowired
    private IMpPaymentRecordService mpPaymentRecordService;
    @Autowired
    private IMpFifthPaymentRecordService mpFifthPaymentRecordService;

    @Override
    @Transactional(rollbackFor = Exception.class) // 事务控制，确保3张表数据一致性
    public void importData(List<AssetImportLogDetailDto> assetImportLogDetailDtos) {
        for (AssetImportLogDetailDto logDetailDto : assetImportLogDetailDtos) {
            try {
                // 1. 解析Excel行数据（JSONArray对应每行的单元格列表，顺序与"荣顺苑签报单"表头一致）
                JSONArray cellArray = JSONObject.parseArray(logDetailDto.getContent());
//                if (cellArray.size() < 132) { // 荣顺苑签报单表头共132列，过滤无效行
//                    throw new RuntimeException("行数据列数不足（需132列），当前列数：" + cellArray.size());
//                }

                // 2. 校验核心必填字段（流转编号、项目名称、业委会金额等）
                JSONArray cellArray = JSONObject.parseArray(logDetailDto.getContent());



                String flowNumber = getCellValue(cellArray, 1); // 第2列：流转编号
                String projectName = getCellValue(cellArray, 4); // 第5列：项目名称
                String ownersCommitteeAmountStr = getCellValue(cellArray, 12); // 第13列：业委会金额
                if (isBlank(flowNumber) || isBlank(projectName) || isBlank(ownersCommitteeAmountStr)) {
                    throw new RuntimeException(
                    throw new IllegalArgumentException(
                            String.format("核心字段缺失：流转编号[%s]、项目名称[%s]、业委会金额[%s]",
                                    flowNumber, projectName, ownersCommitteeAmountStr)
                    );
                }
                AnnouncementTimeRangePo announcementTimeRangePo = null;
                OwnersCommitteeConventionPo ocoPo = null;
                OwnerWithdrawalInfoPo owiPo = null;
                OwnerQualityGuaranteePo oqgPo = null;
                OwnerRemarkInfoPo ownerRemarkInfoPo = null;

                // 3. 构建3张表的PO对象（字段映射+业务计算）
                MaintenancePaymentPo mainPo = buildMaintenancePaymentPo(cellArray);
                List<MpPaymentRecordPo> paymentRecordPos = buildMpPaymentRecordPos(cellArray, mainPo.getFlowNumber());
                MpFifthPaymentRecordPo fifthPo = buildMpFifthPaymentRecordPo(cellArray, mainPo.getFlowNumber());
                MaintenancePayment mainPo = buildMaintenancePaymentPoV2(cellArray);//主表
                if (!getCellValue(cellArray, 22).isEmpty() && !getCellValue(cellArray, 23).isEmpty()) {
                    announcementTimeRangePo = buildAnnouncementTimeRangePo(cellArray, Long.valueOf(mainPo.getId()));//公共收益金
                }
                if (!getCellValue(cellArray, 26).isEmpty() && !getCellValue(cellArray, 27).isEmpty()) {
                    ocoPo = buildOwnersCommitteeConventionPo(cellArray, Long.valueOf(mainPo.getId()));
                }
                if (!getCellValue(cellArray, 29).isEmpty()) {
                    owiPo = buildOwnerWithdrawalInfoPo(cellArray, Long.valueOf(mainPo.getId()));
                }
                if (!getCellValue(cellArray, 39).isEmpty()) {
                    oqgPo = buildOwnerQualityGuaranteePo(cellArray, Long.valueOf(mainPo.getId()));
                }
                List<MpPaymentRecordPo> paymentRecordPos = buildMpPaymentRecordPos(cellArray, mainPo.getFlowNumber(), String.valueOf(mainPo.getId()));
                List<MpFifthPaymentRecord> fifthPos = buildMpFifthPaymentRecordPos(cellArray, mainPo.getFlowNumber(), String.valueOf(mainPo.getId()));
                if (!getCellValue(cellArray, 137).isEmpty()) {
                    ownerRemarkInfoPo = buildOwnerRemarkInfoPo(cellArray, mainPo.getFlowNumber(), String.valueOf(mainPo.getId()));
                }

                // 4. 批量保存到数据库
                saveToDatabase(mainPo, paymentRecordPos, fifthPo);
                importData(mainPo, announcementTimeRangePo, ocoPo, owiPo, oqgPo, paymentRecordPos, fifthPos, ownerRemarkInfoPo);

                // 5. 更新导入日志状态（成功）- 调用父类方法
                super.updateImportLogDetailState(logDetailDto.getDetailId());

            } catch (Exception e) {
                e.printStackTrace();
                // 6. 更新导入日志状态（失败）- 调用父类异常重载方法
                super.updateImportLogDetailState(logDetailDto.getDetailId(), e);
                throw new IllegalArgumentException(e.getMessage());
            }
        }
    }

    /**
     * 构建 MaintenancePaymentPo（维修资金支取信息表）
     * 字段映射对应"荣顺苑签报单"表头顺序，包含17个自动计算字段
     */
    private void importData(MaintenancePayment mainPo, AnnouncementTimeRangePo announcementTimeRangePo, OwnersCommitteeConventionPo ocoPo, OwnerWithdrawalInfoPo owiPo, OwnerQualityGuaranteePo oqgPo,
                            List<MpPaymentRecordPo> paymentRecordPos, List<MpFifthPaymentRecord> fifthPos, OwnerRemarkInfoPo ownerRemarkInfoPo) {
        maintenancePaymentApi.saveMaintenancePayment(mainPo);
        if (announcementTimeRangePo != null) {
            announcementTimeRangeApi.saveAnnouncementTimeRangeInfo(announcementTimeRangePo);
        }
        if (ocoPo != null) {
            ownersCommitteeConventionApi.saveOwnersCommitteeConvention(ocoPo);
        }
        if (owiPo != null) {
            ownerWithdrawalInfoApi.saveOwnerWithdrawalInfo(owiPo);
        }
        if (oqgPo != null) {
            ownerQualityGuaranteeApi.saveOwnerQualityGuarantee(oqgPo);
        }
        for (MpPaymentRecordPo paymentRecordPo : paymentRecordPos) {
            mpPaymentRecordApi.saveMpPaymentRecord(paymentRecordPo);
        }
        for (MpFifthPaymentRecord fifthPo : fifthPos) {
            mpFifthPaymentRecordApi.saveMpFifthPaymentRecord(fifthPo);
        }
        if (ownerRemarkInfoPo != null) {
            ownerRemarkInfoApi.saveOwnerRemarkInfo(ownerRemarkInfoPo);
        }
    }

    private OwnerRemarkInfoPo buildOwnerRemarkInfoPo(JSONArray cellArray, String flowNumber, String id) {
        OwnerRemarkInfoPo po = new OwnerRemarkInfoPo();
        long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 避免负数
        po.setId(longId);
        po.setMpId(flowNumber);
        po.setRemarkPerson("导入内容");
        po.setRemarkContent(getCellValue(cellArray, 137));
        return po;
    }

    private OwnerQualityGuaranteePo buildOwnerQualityGuaranteePo(JSONArray cellArray, Long aLong) {
        OwnerQualityGuaranteePo po = new OwnerQualityGuaranteePo();
        long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 避免负数
        po.setId(longId); // 主键ID
        po.setMpId(aLong.toString());
        try {
            po.setQualityGuaranteePeriod2Start(parseDateToString(getCellValue(cellArray, 37)));
            po.setQualityGuaranteePeriod2End(parseDateToString(getCellValue(cellArray, 38)));
            po.setAcceptanceDate(parseDateToString(getCellValue(cellArray, 41)));
            po.setAvailableWithdrawalDate(parseDate(getCellValue(cellArray, 43)));
        } catch (ParseException e) {
            throw new RuntimeException("日期转换错误");
        }
        po.setQualityGuaranteeRatio(getCellNum(cellArray, 39));
        po.setQualityGuaranteeAmount(getCellNum(cellArray, 12) * po.getQualityGuaranteeRatio());
        po.setAuditStatus(getCellValue(cellArray, 42));
        return po;
    }

    private OwnerWithdrawalInfoPo buildOwnerWithdrawalInfoPo(JSONArray cellArray, Long aLong) {
        OwnerWithdrawalInfoPo po = new OwnerWithdrawalInfoPo();
        long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 避免负数
        po.setId(longId); // 主键ID
        po.setMpId(aLong.toString());
        po.setWithdrawalEncounteredProblem(getCellValue(cellArray, 28));
        po.setShortageOrArrears(getCellNum(cellArray, 29));
        po.setRoadName(getCellValue(cellArray, 30));
        po.setLane(getCellValue(cellArray, 31));
        po.setDoor(getCellValue(cellArray, 32));
        po.setRoom(getCellValue(cellArray, 33));
        po.setDoorRoomNumber(getCellValue(cellArray, 34));
        po.setOwnerAddress(getCellValue(cellArray, 35));
        return po;
    }

    private OwnersCommitteeConventionPo buildOwnersCommitteeConventionPo(JSONArray cellArray, Long mpId) {
        OwnersCommitteeConventionPo po = new OwnersCommitteeConventionPo();

        long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 避免负数
        po.setId(longId); // 主键ID
        po.setMpId(mpId);
        po.setQuota(getCellNum(cellArray, 25));
        po.setOwnersCommitteeResolution(getCellValue(cellArray, 26));
        po.setOwnersCommitteeConsultation(getCellValue(cellArray, 27));

        return po;
    }

    private AnnouncementTimeRangePo buildAnnouncementTimeRangePo(JSONArray cellArray, Long mpId) {
        AnnouncementTimeRangePo po = new AnnouncementTimeRangePo();
        long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 避免负数
        po.setId(longId); // 主键ID
        po.setMpId(mpId);
        po.setPlannedAnnouncementStart(getCellValue(cellArray, 21));
        po.setPlannedAnnouncementEnd(getCellValue(cellArray, 22));
        po.setPublishedAnnouncementStart(getCellValue(cellArray, 23));
        po.setPublishedAnnouncementEnd(getCellValue(cellArray, 24));

        return po;
    }

    private MaintenancePayment buildMaintenancePaymentPoV2(JSONArray cellArray) throws ParseException {
        MaintenancePayment po = new MaintenancePayment();
        po.setId(GenerateCodeFactory.getGeneratorId("10")); // 主键ID
        po.setAuxiliaryColumn(getCellValue(cellArray, 0)); // 第1列：辅助列
        po.setFlowNumber(getCellValue(cellArray, 1)); // 第2列：流转编号
        po.setSerialNumber(parseInteger(getCellValue(cellArray, 2))); // 第3列：序号
        po.setProjectCode(getCellValue(cellArray, 3)); // 第4列：项目编码
        po.setProjectName(getCellValue(cellArray, 4)); // 第5列：项目名称
        po.setYear(parseInteger(getCellValue(cellArray, 5))); // 第6列：年份
        po.setMonth(parseInteger(getCellValue(cellArray, 6))); // 第7列：月份
        po.setDay(parseInteger(getCellValue(cellArray, 7))); // 第8列：日
        po.setDate(parseDate(getCellValue(cellArray, 8))); // 第9列：日期
        po.setProjectContent(getCellValue(cellArray, 9)); // 第10列：工程内容
        po.setManagementOfficeAmount(getCellValue(cellArray, 10)); // 第11列：管理处金额
        po.setManagementOfficeSeal(convertSealStatus(getCellValue(cellArray, 11))); // 第12列：管理处是否已盖章（√转"是"，否则"否"）
        po.setOwnersCommitteeAmount(getCellValue(cellArray, 12)); // 第13列：业委会金额
        po.setAuditAmount(getCellNum(cellArray, 13).toString()); // 第14列：审价金额
        po.setOwnersCommitteeSeal(convertSealStatus(getCellValue(cellArray, 14))); // 第15列：业委会是否已盖章
        po.setReportDepartment(getCellValue(cellArray, 15)); // 第16列：签报部门
        po.setFundTypeLevel1(getCellValue(cellArray, 16)); // 第17列：基金类型-一级分类
        po.setFundTypeLevel2(getCellValue(cellArray, 17)); // 第18列：基金类型-二级分类
        po.setMaintenanceType(getCellValue(cellArray, 18)); // 第19列：幢/全体
        po.setBuildingOrAll(getCellValue(cellArray, 19)); // 第20列：维修类型
        po.setPayeeName(getCellValue(cellArray, 145)); // 支付公司名称/个人名字
        po.setIdCardNumber(getCellValue(cellArray, 146)); // 个人身份证号码
        po.setBankName(getCellValue(cellArray, 147)); // 开户银行
        po.setBankAccount(getCellValue(cellArray, 148)); // 开户账号
        return po;
    }

        /**
         * 构建 MaintenancePaymentPo（维修资金支取信息表）
         * 字段映射对应"荣顺苑签报单"表头顺序，包含17个自动计算字段
         */
    private MaintenancePaymentPo buildMaintenancePaymentPo(JSONArray cellArray) throws ParseException {
        MaintenancePaymentPo po = new MaintenancePaymentPo();
        // 基础字段（直接映射Excel单元格）
@@ -251,65 +400,94 @@
    /**
     * 构建 MpPaymentRecordPo 列表（5次打印数据）
     */
    private List<MpPaymentRecordPo> buildMpPaymentRecordPos(JSONArray cellArray, String flowNumber) throws ParseException {
    private List<MpPaymentRecordPo> buildMpPaymentRecordPos(JSONArray cellArray, String flowNumber,String mainPo) throws ParseException {
        List<MpPaymentRecordPo> pos = new ArrayList<>();
        // 第一次打印数据（第47-53列）
        pos.add(buildSinglePaymentRecord(cellArray, flowNumber, 1, 43, 44, 45, 46, 47, 48, 49));
        buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 1, 45, pos);
        // 第二次打印数据（第54-60列）
        pos.add(buildSinglePaymentRecord(cellArray, flowNumber, 2, 55, 56, 57, 58, 59, 60, 61));
        buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 2, 57, pos);
        // 第三次打印数据（第61-67列）
        pos.add(buildSinglePaymentRecord(cellArray, flowNumber, 3, 67, 68, 69, 70, 71, 72, 73));
        buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 3, 69, pos);
        // 第四次打印数据（第68-74列）
        pos.add(buildSinglePaymentRecord(cellArray, flowNumber, 4, 79, 80, 81, 82, 83, 84, 85));
        buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 4, 81, pos);
        // 第五次打印数据（第75-81列）
        pos.add(buildSinglePaymentRecord(cellArray, flowNumber, 5, 91, 92, 93, 94, 95, 96, 97));
        buildSinglePaymentRecord(mainPo, cellArray, flowNumber, 5, 93, pos);
        return pos;
    }

    /**
     * 构建单次打印的 MpPaymentRecordPo
     */
    private MpPaymentRecordPo buildSinglePaymentRecord(JSONArray cellArray, String flowNumber, int printTimes,
                                                       int dateCol, int resolutionCol, int amountCol,
                                                       int arrivalDateCol, int arrivalAmountCol,
                                                       int withdrawerCol, int shortageCol) throws ParseException {
    private void buildSinglePaymentRecord(String mpId, JSONArray cellArray, String flowNumber, int printTimes,
                                                       int index, List<MpPaymentRecordPo> pos) throws ParseException {

        if (getCellValue(cellArray, index).isEmpty()) {
            return;
        }

        MpPaymentRecordPo po = new MpPaymentRecordPo();

        long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 避免负数
        po.setId(longId+"");
        po.setMpId(flowNumber + "_PRINT" + printTimes); // mp_id = 流转编号_打印次数（确保唯一）
        po.setFirstPrintDate(parseDate(getCellValue(cellArray, dateCol)));
        po.setResolutionNumber(getCellValue(cellArray, resolutionCol));
        po.setPrintAmount(parseBigDecimal(getCellValue(cellArray, amountCol)));
        po.setArrivalDate(parseDate(getCellValue(cellArray, arrivalDateCol)));
        po.setArrivalAmount(parseBigDecimal(getCellValue(cellArray, arrivalAmountCol)));
        po.setWithdrawer(getCellValue(cellArray, withdrawerCol));
        po.setShortageOrArrears(parseBigDecimal(getCellValue(cellArray, shortageCol)));
        po.setMpId(mpId); // mp_id = 流转编号_打印次数（确保唯一）
        po.setFirstPrintDate(parseDate(getCellValue(cellArray, index)));
        po.setResolutionNumber(getCellValue(cellArray, index + 1));
        po.setPrintAmount(parseBigDecimal(getCellValue(cellArray, index + 2)));
        po.setArrivalDate(parseDate(getCellValue(cellArray, index + 3)));
        po.setArrivalAmount(parseBigDecimal(getCellValue(cellArray, index + 4)));
        po.setWithdrawer(getCellValue(cellArray, index + 5));
        po.setShortageOrArrears(parseBigDecimal(getCellValue(cellArray, index + 6)));
        // 业主地址信息（复用main表的地址）
        po.setRoadName(getCellValue(cellArray, 31));
        po.setLane(getCellValue(cellArray, 32));
        po.setDoor(getCellValue(cellArray, 33));
        po.setRoom(getCellValue(cellArray, 34));
        po.setOwnerAddress(getCellValue(cellArray, 36));
        return po;
        po.setRoadName(getCellValue(cellArray, index + 7));
        po.setLane(getCellValue(cellArray, index + 8));
        po.setDoor(getCellValue(cellArray, index + 9));
        po.setRoom(getCellValue(cellArray, index + 10));
        po.setOwnerAddress(getCellValue(cellArray, index + 11));
        po.setPrintAmount(new BigDecimal(flowNumber));
        po.setSnakeCase(getCellValue(cellArray, 105));
        pos.add(po);
    }

    /**
     * 构建 MpFifthPaymentRecordPo（第五次拟付实付）
     */
    private MpFifthPaymentRecordPo buildMpFifthPaymentRecordPo(JSONArray cellArray, String flowNumber) throws ParseException {
        MpFifthPaymentRecordPo po = new MpFifthPaymentRecordPo();
    private List<MpFifthPaymentRecord> buildMpFifthPaymentRecordPos(JSONArray cellArray, String flowNumber,String mainPo) throws ParseException {
        List<MpFifthPaymentRecord> pos = new ArrayList<>();

        buildMpFifthPaymentRecordPo(pos, cellArray, flowNumber, mainPo, 115);

        buildMpFifthPaymentRecordPo(pos, cellArray, flowNumber, mainPo, 121);

        buildMpFifthPaymentRecordPo(pos, cellArray, flowNumber, mainPo, 127);

        buildMpFifthPaymentRecordPo(pos, cellArray, flowNumber, mainPo, 133);

        buildMpFifthPaymentRecordPo(pos, cellArray, flowNumber, mainPo, 139);

        return pos;
    }

    private void buildMpFifthPaymentRecordPo(List<MpFifthPaymentRecord> pos, JSONArray cellArray, String flowNumber, String mainPo, int index) {

        if (getCellValue(cellArray, index).isEmpty()) {
            return;
        }

        MpFifthPaymentRecord po = new MpFifthPaymentRecord();

        long longId = UUID.randomUUID().getMostSignificantBits() & Long.MAX_VALUE; // 避免负数
        po.setId(longId+"");
        po.setMpId(flowNumber + "_FIFTH"); // mp_id = 流转编号_FIFTH
        // 第五次拟付实付数据（第106-111列）
        po.setFifthPlannedPaymentAmount(parseBigDecimal(getCellValue(cellArray, 137))); // 第五次拟付金额
        po.setPlannedPaymentDate(parseDate(getCellValue(cellArray, 138))); // 第五次拟付日期
        po.setCategory(getCellValue(cellArray, 139)); // 类别
        po.setReimburser(getCellValue(cellArray, 140)); // 报销人
        po.setActualPaymentAmount(parseBigDecimal(getCellValue(cellArray, 141))); // 第五次实付金额
        po.setActualPaymentDate(parseDate(getCellValue(cellArray, 142))); // 第五次实付日期
        return po;
        po.setMpId(flowNumber);
        po.setFifthPlannedPaymentAmount(new BigDecimal(getCellValue(cellArray, index)).toString());
        try {
            po.setPlannedPaymentDate(parseDate(getCellValue(cellArray, index + 1)));
            po.setActualPaymentDate(parseDate(getCellValue(cellArray, index  + 5)));
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
        po.setCategory(getCellValue(cellArray, index + 2));
        po.setReimburser(getCellValue(cellArray, index + 3));
        po.setActualPaymentAmount(new BigDecimal(getCellValue(cellArray, index + 4)).toString());
    }

    /**
@@ -351,31 +529,6 @@
        }
    }

    /**
     * 数据库保存（调用SqlSessionTemplate版Service）
     */
    private void saveToDatabase(MaintenancePaymentPo mainPo,
                                List<MpPaymentRecordPo> paymentRecordPos,
                                MpFifthPaymentRecordPo fifthPo) {
        // 1. 保存主表（单条）
        int mainFlag = maintenancePaymentService.saveMaintenancePayment(mainPo);
        if (mainFlag <= 0) {
            throw new RuntimeException("维修资金支取主表插入失败，流转编号：" + mainPo.getFlowNumber());
        }

        // 2. 批量保存5次打印记录
        int batchFlag = mpPaymentRecordService.batchSaveMpPaymentRecord(paymentRecordPos);
        if (batchFlag <= 0) {
            throw new RuntimeException("打印支取到账记录批量插入失败，流转编号：" + mainPo.getFlowNumber());
        }

        // 3. 保存第五次拟付实付记录
        int fifthFlag = mpFifthPaymentRecordService.saveMpFifthPaymentRecord(fifthPo);
        if (fifthFlag <= 0) {
            throw new RuntimeException("第五次拟付实付记录插入失败，流转编号：" + mainPo.getFlowNumber());
        }
    }

    // -------------------------- 工具方法 --------------------------
    /**
     * 获取单元格值（处理null/空字符串）
@@ -386,6 +539,15 @@
        }
        Object value = cellArray.get(index);
        return value == null ? "" : value.toString().trim();
    }


    private Double getCellNum(JSONArray cellArray, int index) {
        if (index < 0 || index >= cellArray.size()) {
            return 0.0;
        }
        Object value = cellArray.get(index);
        return value == null ? 0 : Double.parseDouble((String) value);
    }

    /**
@@ -425,40 +587,81 @@
        }
    }

    private String parseDateToString(String str) throws ParseException{
        return parseDate(str) == null ? "" : str;
    }

    /**
     * 解析日期（支持多种格式，空值返回null）
     */
    // 扩展支持的日期格式：覆盖横线/斜杠分隔、年月日/月日年等常见格式
    private static final List<SimpleDateFormat> DATE_FORMATS = new ArrayList<SimpleDateFormat>() {{
        add(new SimpleDateFormat("yyyy-MM-dd"));      // 2025-12-12
        add(new SimpleDateFormat("yyyy/MM/dd"));      // 2025/12/12
        add(new SimpleDateFormat("yyyyMMdd"));        // 20251212
        add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")); // 带时分秒的格式（兼容）
        add(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"));
        add(new SimpleDateFormat("MM/dd/yyyy"));      // 兼容国外格式（可选）
    }};

    /**
     * 解析日期：支持Excel日期序列号、2025-12-12、2025/12/12等格式
     * @param str 待解析的日期字符串（或Excel序列号）
     * @return 解析后的Date对象，空字符串返回null
     * @throws ParseException 不支持的格式抛出异常
     */
    private Date parseDate(String str) throws ParseException {
        if (isBlank(str)) {
        // 空值处理
        if (StringUtils.isBlank(str)) {
            return null;
        }
        String trimStr = str.trim();

        // 手动解析Excel日期序列号（1900-01-01为基准，注意Excel的1900闰年bug）
        // 第一步：尝试解析Excel日期序列号（数字格式）
        try {
            double excelDateNum = Double.parseDouble(str.trim());
            // Excel 1900日期系统：序列号1=1900-01-01，且错误认为1900是闰年（多算1天）
            Calendar cal = Calendar.getInstance();
            cal.set(1900, 0, 1); // 1900-01-01
            cal.add(Calendar.DAY_OF_YEAR, (int) excelDateNum - 2); // 修正闰年bug（减2）
            return cal.getTime();
            double excelDateNum = Double.parseDouble(trimStr);
            return convertExcelSerialToDate(excelDateNum);
        } catch (NumberFormatException e) {
            // 不是数字，继续解析文本格式
            // 不是数字，继续解析文本日期格式
        }

        // 文本格式解析（同之前逻辑）
        // 第二步：尝试解析文本格式日期（覆盖-和/分隔符）
        for (SimpleDateFormat format : DATE_FORMATS) {
            try {
                format.setLenient(false);
                return format.parse(str.trim());
                format.setLenient(false); // 严格校验，避免2025-13-32这类无效日期
                return format.parse(trimStr);
            } catch (ParseException e) {
                // 该格式解析失败，尝试下一个
                continue;
            }
        }

        // 所有格式都不匹配，抛异常
        throw new ParseException("不支持的日期格式：" + str, 0);
    }

    /**
     * 转换Excel日期序列号为Date（修正1900闰年bug）
     * Excel序列号规则：1=1900-01-01（错误认为1900是闰年，多算1天）
     * @param excelSerial Excel日期序列号（如45735）
     * @return 对应的Date对象
     */
    private Date convertExcelSerialToDate(double excelSerial) {
        // 初始化Calendar：时区设为GMT，避免本地时区偏移
        Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
        // Excel 1900日期系统基准：序列号1对应1900-01-01
        cal.set(1900, Calendar.JANUARY, 1, 0, 0, 0);
        cal.set(Calendar.MILLISECOND, 0);

        // 修正Excel的1900闰年bug：Excel认为1900是闰年，实际不是，需减2天
        // 注：如果序列号<60（对应1900-02-28），减1天即可；通用场景减2天兼容所有情况
        int daysToAdd = (int) excelSerial - 2;
        cal.add(Calendar.DAY_OF_YEAR, daysToAdd);

        return cal.getTime();
    }

    /**
     * 转换盖章状态（√→是，其他→否）
     */
    private String convertSealStatus(String str) {