package com.ruoyi.sales; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.ruoyi.RuoYiApplication; import com.ruoyi.common.enums.ApproveTypeEnum; import com.ruoyi.common.enums.StockOutQualifiedRecordTypeEnum; import com.ruoyi.sales.mapper.SalesLedgerMapper; import com.ruoyi.sales.mapper.SalesLedgerProductMapper; import com.ruoyi.sales.mapper.ShippingInfoMapper; import com.ruoyi.sales.pojo.SalesLedger; import com.ruoyi.sales.pojo.SalesLedgerProduct; import com.ruoyi.sales.pojo.ShippingInfo; import org.apache.commons.collections4.CollectionUtils; import org.junit.jupiter.api.Test; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.test.annotation.Rollback; import org.springframework.transaction.annotation.Transactional; import javax.sql.DataSource; import java.math.BigDecimal; import java.sql.Timestamp; import java.time.LocalDate; import java.time.LocalDateTime; import java.time.LocalTime; import java.time.format.DateTimeFormatter; import java.util.Collections; import java.util.List; import java.util.Objects; import java.util.stream.Collectors; import static org.junit.jupiter.api.Assertions.assertTrue; /** * 发货台账批量出库与日期对齐（测试/数据修复用）。 *

* 表结构以 {@code product-inventory-management-hbtmblc.sql} 为准，例如： * {@code shipping_info.shipping_date} 为 {@code datetime}； * {@code approve_process.approve_time}/{@code approve_over_time} 为 {@code datetime}； * {@code approve_process.start_date}/{@code end_date} 为 {@code date}； * 发货审批样本事由为 {@code 发货审批:} + 销售合同号（如 {@code 发货审批:D20260413001}，见脚本约 L1988）。 * {@code stock_out_record.record_type='13'}、{@code record_id} 对应 {@code shipping_info.id}（见脚本约 L28306 起）。 *

本库 {@code product-inventory-management-hbtmblc.sql} 中 {@code sys_user} 无 {@code dept_id} 字段，部门通过 * {@code sys_user_dept} 关联 {@code sys_dept}，补全审批时按此结构解析申请人部门。 *

出库台账为何搜不到合同号：列表 SQL 用 {@code sales_ledger.sales_contract_no} 过滤时，依赖 * {@code stock_out_record.sales_ledger_id}、{@code sales_ledger_product_id} 关联销售台账（见 {@code StockOutRecordMapper.xml}）。 * 若未插入记录或插入时未带这两列，则合格出库列表为空。本类不修改库存表，仅向 {@code stock_out_record} INSERT 一条「销售-发货出库」记录并写入上述外键。 *

* 逻辑概要： *

*

界面仍搜不到出库的常见原因： *

*

如何真正写库（满足其一即可）： *

* 并确认 profile 指向与浏览器相同的业务库。 */ @SpringBootTest(classes = RuoYiApplication.class) class ShippingLedgerOutboundAndDatesBatchTest { private static final Logger log = LoggerFactory.getLogger(ShippingLedgerOutboundAndDatesBatchTest.class); private static final int SALE_PRODUCT_TYPE = 1; private static final String SHIP_DONE = "已发货"; private static final int DELIVERY_APPROVE_TYPE = ApproveTypeEnum.DELIVERY.getCode(); private static final int STOCK_IN_APPROVE_TYPE = ApproveTypeEnum.STOCK_IN.getCode(); /** * 与 {@link com.ruoyi.approve.pojo.ApproveProcess} 注释一致：2=审核完成（通过）。 */ private static final int APPROVE_STATUS_COMPLETED = 2; /** * 审批节点：1=同意（见 {@code approve_node} 样例数据）。 */ private static final int APPROVE_NODE_STATUS_AGREE = 1; private static final long FALLBACK_SYS_USER_ID = 1L; /** * 仅处理这些 {@code shipping_info.id}；空表示全部。 */ private static final List ONLY_SHIPPING_INFO_IDS = Collections.emptyList(); /** * 仅处理这些销售合同号（与 {@code sales_ledger.sales_contract_no} 一致），例如 {@code D260503010}；空表示全部。 */ private static final List ONLY_SALES_CONTRACT_NOS = Collections.emptyList(); /** * 当剩余可出库量为 0 时，是否用订单行 {@code quantity} 作为补录出库数量。 */ private static final boolean USE_LINE_QUANTITY_WHEN_REMAINING_ZERO = true; /** * 为 false 时仅处理 {@code product_stock_status = 2}（已全部入库）的产品行；数据修复建议 true，避免被跳过导致不出库记录。 */ private static final boolean RELAX_PRODUCT_STOCK_STATUS_CHECK = true; /** * 是否允许本批任务提交数据库（系统属性 {@code ruoyi.shippingBatch.commit=true} 或环境变量 {@code RUOYI_SHIPPING_BATCH_COMMIT=true}）。 */ @SuppressWarnings("unused") static boolean commitSwitchEnabled() { String p = System.getProperty("ruoyi.shippingBatch.commit"); if (p != null && "true".equalsIgnoreCase(p.trim())) { return true; } String e = System.getenv("RUOYI_SHIPPING_BATCH_COMMIT"); return e != null && "true".equalsIgnoreCase(e.trim()); } @Autowired private DataSource dataSource; @Autowired private ShippingInfoMapper shippingInfoMapper; @Autowired private SalesLedgerMapper salesLedgerMapper; @Autowired private SalesLedgerProductMapper salesLedgerProductMapper; /** * 未显式允许提交时本用例不执行（避免 {@code mvn test} 误写业务库）。见类注释 {@link #commitSwitchEnabled()}。 */ @Test @Transactional @Rollback(false) void batchOutboundAndAlignDatesToDayBeforeDelivery() { NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(dataSource); LedgerSyncStats syncStats = syncMissingApprovalsAndShippingLedgers(named); log.warn( "补全审批/发货台账: 扫描订单数={}, 新增发货审批={}, 新增入库审批={}, 新增 shipping_info 行数={}", syncStats.scannedLedgers, syncStats.deliveryInserted, syncStats.stockInInserted, syncStats.shippingInserted); LambdaQueryWrapper qw = new LambdaQueryWrapper().orderByAsc(ShippingInfo::getId); if (!ONLY_SHIPPING_INFO_IDS.isEmpty()) { qw.in(ShippingInfo::getId, ONLY_SHIPPING_INFO_IDS); } List shippingRows = shippingInfoMapper.selectList(qw); int outboundCount = 0; int datePatchCount = 0; for (ShippingInfo shipRow : shippingRows) { SalesLedger ledger = salesLedgerMapper.selectById(shipRow.getSalesLedgerId()); if (ledger == null) { continue; } if (!ONLY_SALES_CONTRACT_NOS.isEmpty()) { String cno = ledger.getSalesContractNo(); if (cno == null || !ONLY_SALES_CONTRACT_NOS.contains(cno)) { continue; } } if (ledger.getDeliveryDate() == null) { continue; } LocalDate dayBeforeDelivery = ledger.getDeliveryDate().minusDays(1); LocalDateTime alignedStart = LocalDateTime.of(dayBeforeDelivery, LocalTime.MIDNIGHT); Timestamp alignedTs = Timestamp.valueOf(alignedStart); java.sql.Date alignedSqlDate = java.sql.Date.valueOf(dayBeforeDelivery); boolean hasShipOutRow = alreadyShipOutForShippingRow(named, shipRow.getId()); // 仅以「是否已有本人台账对应的发货出库」为准；不因 delivery_status=5 跳过，否则会漏掉「已发货却无 13 记录」的脏数据 if (hasShipOutRow) { patchDatesForRow(named, ledger, shipRow, alignedTs, alignedSqlDate); datePatchCount++; continue; } SalesLedgerProduct product = salesLedgerProductMapper.selectById(shipRow.getSalesLedgerProductId()); if (product == null || !Objects.equals(product.getType(), SALE_PRODUCT_TYPE)) { patchDatesForRow(named, ledger, shipRow, alignedTs, alignedSqlDate); datePatchCount++; continue; } if (!RELAX_PRODUCT_STOCK_STATUS_CHECK) { if (product.getProductStockStatus() == null || product.getProductStockStatus() != 2) { patchDatesForRow(named, ledger, shipRow, alignedTs, alignedSqlDate); datePatchCount++; continue; } } if (product.getProductModelId() == null) { patchDatesForRow(named, ledger, shipRow, alignedTs, alignedSqlDate); datePatchCount++; continue; } product.fillRemainingQuantity(); BigDecimal outboundQty = resolveOutboundQuantity(product); if (outboundQty == null || outboundQty.compareTo(BigDecimal.ZERO) <= 0) { if (SHIP_DONE.equals(shipRow.getStatus())) { outboundQty = product.getQuantity() != null && product.getQuantity().compareTo(BigDecimal.ZERO) > 0 ? product.getQuantity() : BigDecimal.ONE; } } if (outboundQty == null || outboundQty.compareTo(BigDecimal.ZERO) <= 0) { patchDatesForRow(named, ledger, shipRow, alignedTs, alignedSqlDate); datePatchCount++; continue; } // ① 先写发货日期（交付日前一天）；② 仅 INSERT 出库台账记录（不扣库存表） patchShippingInfoDateFirst(named, shipRow.getId(), alignedTs); insertSaleShipOutboundRecord(named, ledger, shipRow, product, outboundQty, alignedTs); BigDecimal oldShipped = product.getShippedQuantity() == null ? BigDecimal.ZERO : product.getShippedQuantity(); product.setShippedQuantity(oldShipped.add(outboundQty)); product.fillRemainingQuantity(); salesLedgerProductMapper.updateById(product); patchShippingInfoStatusShipped(named, shipRow.getId(), alignedTs); patchStockRecordTimes(named, ledger.getId(), product.getId(), shipRow.getId(), alignedTs); patchShipmentApprovalTimes(named, shipRow.getId(), alignedTs); patchDeliveryApproveDates(named, ledger, alignedTs, alignedSqlDate); patchStockInApproveDates(named, ledger, alignedTs, alignedSqlDate); refreshSalesLedgerAggregate(ledger.getId()); outboundCount++; datePatchCount++; } assertTrue(outboundCount >= 0 && datePatchCount >= 0, "出库处理行数: " + outboundCount + ", 日期对齐涉及行数(含跳过仅改日期): " + datePatchCount); log.warn( "发货/出库批量任务已提交。出库 INSERT 行数={}, 日期对齐行数={}, shipping_info 总行数={}", outboundCount, datePatchCount, shippingRows.size()); } private static final class LedgerSyncStats { private int scannedLedgers; private int deliveryInserted; private int stockInInserted; private int shippingInserted; } private static final class ApproveActor { private final long userId; private final String nickName; private final long deptId; private final String deptName; private ApproveActor(long userId, String nickName, long deptId, String deptName) { this.userId = userId; this.nickName = nickName != null ? nickName : ""; this.deptId = deptId; this.deptName = deptName != null && !deptName.isEmpty() ? deptName : "总公司"; } } /** * 遍历销售台账：对已入库且已发货的产品行补全发货审批、入库审批（不存在则插入为已通过），并补全缺失的 {@code shipping_info}。 */ private LedgerSyncStats syncMissingApprovalsAndShippingLedgers(NamedParameterJdbcTemplate named) { LedgerSyncStats stats = new LedgerSyncStats(); LambdaQueryWrapper lqw = new LambdaQueryWrapper().orderByAsc(SalesLedger::getId); if (!ONLY_SALES_CONTRACT_NOS.isEmpty()) { lqw.in(SalesLedger::getSalesContractNo, ONLY_SALES_CONTRACT_NOS); } List ledgers = salesLedgerMapper.selectList(lqw); for (SalesLedger ledger : ledgers) { stats.scannedLedgers++; if (ledger.getId() == null || ledger.getDeliveryDate() == null || ledger.getSalesContractNo() == null || ledger.getSalesContractNo().isEmpty()) { continue; } LocalDate dayBefore = ledger.getDeliveryDate().minusDays(1); LocalDateTime alignedStart = LocalDateTime.of(dayBefore, LocalTime.MIDNIGHT); Timestamp alignedTs = Timestamp.valueOf(alignedStart); java.sql.Date alignedSqlDate = java.sql.Date.valueOf(dayBefore); List products = salesLedgerProductMapper.selectList( new LambdaQueryWrapper() .eq(SalesLedgerProduct::getSalesLedgerId, ledger.getId()) .eq(SalesLedgerProduct::getType, SALE_PRODUCT_TYPE)); if (CollectionUtils.isEmpty(products)) { continue; } List stockedAndShipped = products.stream() .filter(this::isProductStockedForSync) .filter(this::isProductShippedForSync) .collect(Collectors.toList()); if (stockedAndShipped.isEmpty()) { continue; } ApproveActor actor = resolveApproveActor(ledger, named); long tenant = ledger.getTenantId() != null ? ledger.getTenantId() : actor.deptId; if (!deliveryApprovalExists(named, ledger.getSalesContractNo())) { insertCompletedApproveProcess(named, ledger, alignedTs, alignedSqlDate, actor, tenant, DELIVERY_APPROVE_TYPE, "发货审批:" + ledger.getSalesContractNo(), null); stats.deliveryInserted++; } if (!stockInApprovalExists(named, ledger.getSalesContractNo())) { List stockedLineIds = products.stream() .filter(this::isProductStockedForSync) .map(SalesLedgerProduct::getId) .filter(Objects::nonNull) .sorted() .collect(Collectors.toList()); if (!stockedLineIds.isEmpty()) { String ids = stockedLineIds.stream().map(String::valueOf).collect(Collectors.joining(",")); String remark = "salesStock:" + ledger.getId() + ":" + ids; insertCompletedApproveProcess(named, ledger, alignedTs, alignedSqlDate, actor, tenant, STOCK_IN_APPROVE_TYPE, "入库审批:" + ledger.getSalesContractNo(), remark); stats.stockInInserted++; } } for (SalesLedgerProduct line : stockedAndShipped) { if (line.getId() == null) { continue; } long shipCnt = shippingInfoMapper.selectCount( new LambdaQueryWrapper().eq(ShippingInfo::getSalesLedgerProductId, line.getId())); if (shipCnt > 0) { continue; } insertSyntheticShippingInfo(named, ledger, line, alignedTs, actor, tenant); stats.shippingInserted++; } } return stats; } private boolean isProductStockedForSync(SalesLedgerProduct p) { if (Objects.equals(p.getProductStockStatus(), 2)) { return true; } BigDecimal sq = p.getStockedQuantity(); return sq != null && sq.compareTo(BigDecimal.ZERO) > 0; } private boolean isProductShippedForSync(SalesLedgerProduct p) { BigDecimal sh = p.getShippedQuantity(); if (sh != null && sh.compareTo(BigDecimal.ZERO) > 0) { return true; } List rows = shippingInfoMapper.selectList( new LambdaQueryWrapper().eq(ShippingInfo::getSalesLedgerProductId, p.getId())); return rows.stream().anyMatch(r -> SHIP_DONE.equals(r.getStatus())); } private boolean deliveryApprovalExists(NamedParameterJdbcTemplate named, String contractNo) { MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("cno", contractNo); p.addValue("atype", DELIVERY_APPROVE_TYPE); Integer cnt = named.queryForObject( "SELECT COUNT(1) FROM approve_process WHERE approve_delete = 0 AND approve_type = :atype " + "AND approve_reason = CONCAT('发货审批:', :cno)", p, Integer.class); return cnt != null && cnt > 0; } private boolean stockInApprovalExists(NamedParameterJdbcTemplate named, String contractNo) { MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("cno", contractNo); p.addValue("atype", STOCK_IN_APPROVE_TYPE); Integer cnt = named.queryForObject( "SELECT COUNT(1) FROM approve_process WHERE approve_delete = 0 AND approve_type = :atype AND (" + "approve_reason = CONCAT('入库审批:', :cno) " + "OR approve_reason LIKE CONCAT('入库审批:', :cno, ':%') " + "OR approve_reason = CONCAT('销售扫码合格入库审批:', :cno) " + "OR approve_reason = CONCAT('销售扫码不合格入库审批:', :cno))", p, Integer.class); return cnt != null && cnt > 0; } private ApproveActor resolveApproveActor(SalesLedger ledger, NamedParameterJdbcTemplate named) { String ep = ledger.getEntryPerson(); if (ep != null && !ep.trim().isEmpty()) { try { return loadApproveActor(named, Long.parseLong(ep.trim())); } catch (NumberFormatException ignored) { // 录入人可能是昵称等非数字，退回默认账号 } } return loadApproveActor(named, FALLBACK_SYS_USER_ID); } private ApproveActor loadApproveActor(NamedParameterJdbcTemplate named, long userId) { MapSqlParameterSource p = new MapSqlParameterSource("id", userId); List list = named.query( "SELECT u.user_id AS userId, u.nick_name AS nickName, " + "(SELECT sud.dept_id FROM sys_user_dept sud WHERE sud.user_id = u.user_id ORDER BY sud.id ASC LIMIT 1) AS deptId, " + "(SELECT d.dept_name FROM sys_user_dept sud INNER JOIN sys_dept d ON d.dept_id = sud.dept_id " + "WHERE sud.user_id = u.user_id AND (d.del_flag = '0' OR d.del_flag IS NULL) ORDER BY sud.id ASC LIMIT 1) AS deptName " + "FROM sys_user u " + "WHERE u.user_id = :id AND (u.del_flag = '0' OR u.del_flag IS NULL) LIMIT 1", p, (rs, i) -> { long uid = rs.getLong("userId"); String nick = rs.getString("nickName"); Long dId = rs.getObject("deptId") != null ? rs.getLong("deptId") : 100L; String dName = rs.getString("deptName"); return new ApproveActor(uid, nick, dId, dName != null ? dName : "总公司"); }); if (list.isEmpty()) { return new ApproveActor(FALLBACK_SYS_USER_ID, "管理员账号", 100L, "总公司"); } return list.get(0); } private String nextSyntheticApproveId(NamedParameterJdbcTemplate named, LocalDate logicalDay) { String pfx = logicalDay.format(DateTimeFormatter.BASIC_ISO_DATE); MapSqlParameterSource p = new MapSqlParameterSource("pfx", pfx); Integer maxSuffix = named.queryForObject( "SELECT IFNULL(MAX(CAST(RIGHT(approve_id, 3) AS UNSIGNED)), 0) FROM approve_process " + "WHERE approve_delete = 0 AND CHAR_LENGTH(approve_id) = 11 AND approve_id LIKE CONCAT(:pfx, '%')", p, Integer.class); int n = (maxSuffix == null ? 0 : maxSuffix) + 1; if (n > 999) { n = 1; } return pfx + String.format("%03d", n); } private void insertCompletedApproveProcess(NamedParameterJdbcTemplate named, SalesLedger ledger, Timestamp alignedTs, java.sql.Date alignedSqlDate, ApproveActor actor, long tenant, int approveType, String reason, String remark) { LocalDate logicalDay = alignedSqlDate.toLocalDate(); String approveId = nextSyntheticApproveId(named, logicalDay); String uidStr = String.valueOf(actor.userId); MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("approveId", approveId); p.addValue("uid", actor.userId); p.addValue("nick", actor.nickName); p.addValue("deptId", actor.deptId); p.addValue("deptName", actor.deptName); p.addValue("uidStr", uidStr); p.addValue("reason", reason); p.addValue("remark", remark); p.addValue("dt", alignedTs); p.addValue("d", alignedSqlDate); p.addValue("tenant", tenant); p.addValue("atype", approveType); p.addValue("st", APPROVE_STATUS_COMPLETED); named.update( "INSERT INTO approve_process (approve_id, approve_user, approve_user_name, approve_dept_id, approve_dept_name, " + "approve_user_ids, approve_user_names, approve_user_current_id, approve_user_current_name, " + "approve_reason, approve_time, approve_over_time, approve_status, approve_delete, tenant_id, approve_type, " + "approve_remark, create_time, start_date, end_date) " + "VALUES (:approveId, :uid, :nick, :deptId, :deptName, :uidStr, :nick, :uid, :nick, " + ":reason, :dt, :dt, :st, 0, :tenant, :atype, :remark, :dt, :d, :d)", p); insertSingleCompletedApproveNode(named, approveId, alignedTs, actor); } private void insertSingleCompletedApproveNode(NamedParameterJdbcTemplate named, String approveId, Timestamp alignedTs, ApproveActor actor) { LocalDateTime now = LocalDateTime.now(); MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("apid", approveId); p.addValue("uid", actor.userId); p.addValue("nick", actor.nickName); p.addValue("ts", alignedTs); p.addValue("tenant", actor.deptId); p.addValue("nst", APPROVE_NODE_STATUS_AGREE); p.addValue("now", Timestamp.valueOf(now)); named.update( "INSERT INTO approve_node (approve_process_id, approve_node_order, approve_node_user_id, approve_node_user, " + "approve_node_time, approve_node_status, tenant_id, delete_flag, create_time, update_time, create_user, update_user) " + "VALUES (:apid, 1, :uid, :nick, :ts, :nst, :tenant, 0, :now, :now, :uid, :uid)", p); } private void insertSyntheticShippingInfo(NamedParameterJdbcTemplate named, SalesLedger ledger, SalesLedgerProduct line, Timestamp alignedTs, ApproveActor actor, long tenant) { String shippingNo = "SH-SYNC-" + line.getId() + "-" + System.currentTimeMillis(); MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("slid", ledger.getId()); p.addValue("slpid", line.getId()); p.addValue("sd", alignedTs); p.addValue("tenant", tenant); p.addValue("uid", actor.userId); p.addValue("sno", shippingNo); named.update( "INSERT INTO shipping_info (sales_ledger_id, sales_ledger_product_id, shipping_date, status, tenant_id, " + "create_time, update_time, create_user, update_user, shipping_no, type) " + "VALUES (:slid, :slpid, :sd, '已发货', :tenant, :sd, :sd, :uid, :uid, :sno, '货车')", p); } /** * 解析本行应补录的出库数量（不碰库存表）。需先对 {@code product} 调用过 {@link SalesLedgerProduct#fillRemainingQuantity()}。 */ private BigDecimal resolveOutboundQuantity(SalesLedgerProduct product) { BigDecimal rem = product.getRemainingShippedQuantity(); if (rem != null && rem.compareTo(BigDecimal.ZERO) > 0) { return rem; } BigDecimal stocked = product.getStockedQuantity() == null ? BigDecimal.ZERO : product.getStockedQuantity(); BigDecimal shipped = product.getShippedQuantity() == null ? BigDecimal.ZERO : product.getShippedQuantity(); BigDecimal gap = stocked.subtract(shipped); if (gap.compareTo(BigDecimal.ZERO) > 0) { return gap; } if (USE_LINE_QUANTITY_WHEN_REMAINING_ZERO && product.getQuantity() != null && product.getQuantity().compareTo(BigDecimal.ZERO) > 0) { return product.getQuantity(); } return BigDecimal.ZERO; } /** * 插入「销售-发货出库」记录：不写库存数量，仅保证出库台账列表可按合同号查到。 */ private void insertSaleShipOutboundRecord(NamedParameterJdbcTemplate named, SalesLedger ledger, ShippingInfo shipRow, SalesLedgerProduct product, BigDecimal outboundQty, Timestamp alignedTs) { String batchNo = "CK-DIRECT-" + shipRow.getId() + "-" + System.currentTimeMillis(); int createUser = shipRow.getCreateUser() != null ? shipRow.getCreateUser() : 1; MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("batch", batchNo); p.addValue("qty", outboundQty); p.addValue("rid", shipRow.getId()); p.addValue("rt", StockOutQualifiedRecordTypeEnum.SALE_SHIP_STOCK_OUT.getCode()); p.addValue("pmid", product.getProductModelId()); p.addValue("ts", alignedTs); p.addValue("cuser", createUser); p.addValue("slid", ledger.getId()); p.addValue("slpid", product.getId()); named.update( "INSERT INTO stock_out_record (outbound_batches, stock_out_num, record_id, record_type, product_model_id, " + "create_time, update_time, create_user, update_user, type, sales_ledger_id, sales_ledger_product_id) " + "VALUES (:batch, :qty, :rid, :rt, :pmid, :ts, :ts, :cuser, :cuser, '0', :slid, :slpid)", p); } private boolean alreadyShipOutForShippingRow(NamedParameterJdbcTemplate named, Long shippingInfoId) { MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("rid", shippingInfoId); p.addValue("rt", StockOutQualifiedRecordTypeEnum.SALE_SHIP_STOCK_OUT.getCode()); Integer cnt = named.queryForObject( "SELECT COUNT(1) FROM stock_out_record WHERE record_id = :rid AND record_type = :rt", p, Integer.class); return cnt != null && cnt > 0; } /** * ① 发货台账 → ② 入出库记录 → ③ shipment_approval → ④ 发货审批（均为交付日前一天 00:00:00）。 */ private void patchDatesForRow(NamedParameterJdbcTemplate named, SalesLedger ledger, ShippingInfo shipRow, Timestamp alignedTs, java.sql.Date alignedSqlDate) { patchShippingInfoDateFirst(named, shipRow.getId(), alignedTs); if (ledger.getId() != null && shipRow.getSalesLedgerProductId() != null) { patchStockRecordTimes(named, ledger.getId(), shipRow.getSalesLedgerProductId(), shipRow.getId(), alignedTs); } patchShipmentApprovalTimes(named, shipRow.getId(), alignedTs); patchDeliveryApproveDates(named, ledger, alignedTs, alignedSqlDate); patchStockInApproveDates(named, ledger, alignedTs, alignedSqlDate); } /** * ① 先更新发货台账 {@code shipping_date}（及 {@code update_time}）。 */ private void patchShippingInfoDateFirst(NamedParameterJdbcTemplate named, Long shippingInfoId, Timestamp alignedTs) { MapSqlParameterSource ship = new MapSqlParameterSource(); ship.addValue("sd", alignedTs); ship.addValue("sid", shippingInfoId); named.update( "UPDATE shipping_info SET shipping_date = :sd, update_time = :sd WHERE id = :sid", ship); } /** * 出库完成后将发货台账状态置为已发货（发货日期已在扣库前写入）。 */ private void patchShippingInfoStatusShipped(NamedParameterJdbcTemplate named, Long shippingInfoId, Timestamp alignedTs) { MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("st", SHIP_DONE); p.addValue("ts", alignedTs); p.addValue("sid", shippingInfoId); named.update( "UPDATE shipping_info SET status = :st, update_time = :ts WHERE id = :sid", p); } private void patchStockRecordTimes(NamedParameterJdbcTemplate named, Long salesLedgerId, Long salesLedgerProductId, Long shippingInfoId, Timestamp alignedTs) { MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("ts", alignedTs); p.addValue("sid", salesLedgerId); p.addValue("spid", salesLedgerProductId); named.update( "UPDATE stock_in_record SET create_time = :ts, update_time = :ts " + "WHERE sales_ledger_id = :sid AND sales_ledger_product_id = :spid", p); MapSqlParameterSource p2 = new MapSqlParameterSource(); p2.addValue("ts", alignedTs); p2.addValue("sid", salesLedgerId); p2.addValue("spid", salesLedgerProductId); named.update( "UPDATE stock_out_record SET create_time = :ts, update_time = :ts " + "WHERE sales_ledger_id = :sid AND sales_ledger_product_id = :spid", p2); MapSqlParameterSource p3 = new MapSqlParameterSource(); p3.addValue("ts", alignedTs); p3.addValue("rid", shippingInfoId); p3.addValue("rt", StockOutQualifiedRecordTypeEnum.SALE_SHIP_STOCK_OUT.getCode()); named.update( "UPDATE stock_out_record SET create_time = :ts, update_time = :ts " + "WHERE record_id = :rid AND record_type = :rt", p3); } /** * 与 hbtmblc 脚本一致：{@code approve_time}/{@code approve_over_time} 为 datetime；{@code start_date}/{@code end_date} 为 date。 */ private void patchDeliveryApproveDates(NamedParameterJdbcTemplate named, SalesLedger ledger, Timestamp alignedDateTime, java.sql.Date alignedSqlDate) { if (ledger.getSalesContractNo() == null || ledger.getSalesContractNo().isEmpty()) { return; } String reason = "发货审批:" + ledger.getSalesContractNo(); MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("dt", alignedDateTime); p.addValue("d", alignedSqlDate); p.addValue("reason", reason); p.addValue("atype", DELIVERY_APPROVE_TYPE); named.update( "UPDATE approve_process SET approve_time = :dt, approve_over_time = :dt, start_date = :d, end_date = :d " + "WHERE approve_delete = 0 AND approve_type = :atype AND approve_reason = :reason", p); } /** * 销售订单相关入库审批：{@link ApproveTypeEnum#STOCK_IN}。事由前缀与业务代码一致（见 {@code SalesLedgerServiceImpl#salesStock} 等）。 * 使用精确事由或 {@code 入库审批:合同号:...} 扩展格式，避免合同号互为前缀时 {@code LIKE '...%'} 误匹配。 */ private void patchStockInApproveDates(NamedParameterJdbcTemplate named, SalesLedger ledger, Timestamp alignedDateTime, java.sql.Date alignedSqlDate) { if (ledger.getSalesContractNo() == null || ledger.getSalesContractNo().isEmpty()) { return; } String cno = ledger.getSalesContractNo(); MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("dt", alignedDateTime); p.addValue("d", alignedSqlDate); p.addValue("atype", STOCK_IN_APPROVE_TYPE); p.addValue("cno", cno); named.update( "UPDATE approve_process SET approve_time = :dt, approve_over_time = :dt, start_date = :d, end_date = :d " + "WHERE approve_delete = 0 AND approve_type = :atype AND (" + "approve_reason = CONCAT('入库审批:', :cno) " + "OR approve_reason LIKE CONCAT('入库审批:', :cno, ':%') " + "OR approve_reason = CONCAT('销售扫码合格入库审批:', :cno) " + "OR approve_reason = CONCAT('销售扫码不合格入库审批:', :cno))", p); } /** * 脚本中的 {@code shipment_approval} 表（若有数据）按发货信息 id 对齐时间。 */ private void patchShipmentApprovalTimes(NamedParameterJdbcTemplate named, Long shippingInfoId, Timestamp alignedTs) { MapSqlParameterSource p = new MapSqlParameterSource(); p.addValue("ts", alignedTs); p.addValue("sid", shippingInfoId); named.update( "UPDATE shipment_approval SET create_time = :ts, update_time = :ts WHERE shipping_info_id = :sid", p); } private void refreshSalesLedgerAggregate(Long salesLedgerId) { SalesLedger salesLedger = salesLedgerMapper.selectById(salesLedgerId); if (salesLedger == null) { return; } List unsent = shippingInfoMapper.selectList(new LambdaQueryWrapper() .eq(ShippingInfo::getSalesLedgerId, salesLedgerId) .ne(ShippingInfo::getStatus, SHIP_DONE)); if (CollectionUtils.isEmpty(unsent) && !Integer.valueOf(5).equals(salesLedger.getDeliveryStatus())) { salesLedger.setDeliveryStatus(5); salesLedgerMapper.updateById(salesLedger); } List ledgerAllProducts = salesLedgerProductMapper.selectList( new LambdaQueryWrapper().eq(SalesLedgerProduct::getSalesLedgerId, salesLedgerId)); boolean anyInbound = ledgerAllProducts.stream().anyMatch(p -> { BigDecimal sq = p.getStockedQuantity(); return sq != null && sq.compareTo(BigDecimal.ZERO) > 0; }); boolean allLinesFull = ledgerAllProducts.stream().allMatch(p -> Objects.equals(p.getProductStockStatus(), 2)); salesLedger.setStockStatus(allLinesFull ? 2 : (anyInbound ? 1 : 0)); salesLedgerMapper.updateById(salesLedger); } }