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;
|
|
/**
|
* 发货台账批量出库与日期对齐（测试/数据修复用）。
|
* <p>
|
* 表结构以 {@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 起）。
|
* <p>本库 {@code product-inventory-management-hbtmblc.sql} 中 {@code sys_user} 无 {@code dept_id} 字段，部门通过
|
* {@code sys_user_dept} 关联 {@code sys_dept}，补全审批时按此结构解析申请人部门。
|
* <p><b>出库台账为何搜不到合同号</b>：列表 SQL 用 {@code sales_ledger.sales_contract_no} 过滤时，依赖
|
* {@code stock_out_record.sales_ledger_id}、{@code sales_ledger_product_id} 关联销售台账（见 {@code StockOutRecordMapper.xml}）。
|
* 若未插入记录或插入时未带这两列，则合格出库列表为空。本类<b>不修改库存表</b>，仅向 {@code stock_out_record} <b>INSERT</b> 一条「销售-发货出库」记录并写入上述外键。
|
* <p>
|
* 逻辑概要：
|
* <ul>
|
* <li>遍历 {@code shipping_info}；若 {@code sales_ledger.delivery_date} 为空则跳过。</li>
|
* <li>若该行已存在 {@code record_type = 13} 且 {@code record_id = shipping_info.id} 的出库记录：不再插入，仅对齐日期。</li>
|
* <li>否则在满足产品已入库等条件时，按出库数量解析规则写入一条出库记录（{@code record_type=13}，{@code record_id=shipping_info.id}），
|
* 并回写产品 {@code shipped_quantity}；<b>不调用</b>库存扣减逻辑。</li>
|
* <li><b>日期基准（唯一）</b>：一律取关联销售订单 {@code sales_ledger.delivery_date}（交付日期）的<strong>日历日前一天</strong>，
|
* 时刻统一为当日 00:00:00。例如交付日 2026-04-10 → 发货日期、出库日期、入库记录时间、审批申请/结束日期等均对齐到 2026-04-09 00:00:00（合同 {@code D260403007} 同类场景）。</li>
|
* <li><b>对齐顺序</b>：① 先改发货台账 {@code shipping_info.shipping_date}（及 {@code update_time}）；② 再改该产品行的入库/出库记录时间；
|
* ③ {@code shipment_approval}；④ {@code approve_process} 中与该销售合同相关的<b>发货审批</b>（{@code approve_type=7}）与<b>销售入库审批</b>（{@code approve_type=9}，含 Web 入库与扫码合格/不合格入库事由前缀）。插入出库记录在①之后。</li>
|
* <li><b>主流程前「补全」</b>：对「已入库且已发货」的销售产品行（见 {@link #isProductStockedForSync} / {@link #isProductShippedForSync}），若缺少
|
* {@code approve_process} 发货审批（事由 {@code 发货审批:合同号}）或缺少任一形式销售入库审批，则插入<b>已通过</b>（{@code approve_status=2}）记录及一条同意的 {@code approve_node}；
|
* 若该产品行尚无 {@code shipping_info}，则插入一条状态为「已发货」的发货台账（与业务「一行产品一条发货记录」对齐）。</li>
|
* </ul>
|
* <p><b>界面仍搜不到出库的常见原因</b>：
|
* <ul>
|
* <li>未打开提交开关（见下方「如何真正写库」）时本用例不会执行，数据库不会有任何变更。</li>
|
* <li>加了参数但 Maven/IDE 连的不是浏览器同一套数据源，界面仍显示旧数据。</li>
|
* <li>产品行 {@code product_stock_status != 2}（未标记全部入库）时原逻辑会 {@code continue}，已用 {@link #RELAX_PRODUCT_STOCK_STATUS_CHECK} 放宽。</li>
|
* <li>解析出的出库数量为 0 时原先只改日期不 INSERT——已对「发货状态=已发货」兜底仍插入一条。</li>
|
* <li>合格出库列表会带 {@code type=0}，插入时已写 {@code type='0'}。</li>
|
* </ul>
|
* <p><b>如何真正写库</b>（满足其一即可）：
|
* <ul>
|
* <li>IDE：Run Configuration → VM options 增加 {@code -Druoyi.shippingBatch.commit=true}</li>
|
* <li>环境变量：{@code RUOYI_SHIPPING_BATCH_COMMIT=true}（Windows 可在运行前 {@code set RUOYI_SHIPPING_BATCH_COMMIT=true}）</li>
|
* <li>Maven：{@code mvn test -Dtest=ShippingLedgerOutboundAndDatesBatchTest "-DargLine=-Druoyi.shippingBatch.commit=true"}</li>
|
* </ul>
|
* 并确认 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<Long> ONLY_SHIPPING_INFO_IDS = Collections.emptyList();
|
|
/**
|
* 仅处理这些销售合同号（与 {@code sales_ledger.sales_contract_no} 一致），例如 {@code D260503010}；空表示全部。
|
*/
|
private static final List<String> 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<ShippingInfo> qw = new LambdaQueryWrapper<ShippingInfo>().orderByAsc(ShippingInfo::getId);
|
if (!ONLY_SHIPPING_INFO_IDS.isEmpty()) {
|
qw.in(ShippingInfo::getId, ONLY_SHIPPING_INFO_IDS);
|
}
|
List<ShippingInfo> 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<SalesLedger> lqw = new LambdaQueryWrapper<SalesLedger>().orderByAsc(SalesLedger::getId);
|
if (!ONLY_SALES_CONTRACT_NOS.isEmpty()) {
|
lqw.in(SalesLedger::getSalesContractNo, ONLY_SALES_CONTRACT_NOS);
|
}
|
List<SalesLedger> 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<SalesLedgerProduct> products = salesLedgerProductMapper.selectList(
|
new LambdaQueryWrapper<SalesLedgerProduct>()
|
.eq(SalesLedgerProduct::getSalesLedgerId, ledger.getId())
|
.eq(SalesLedgerProduct::getType, SALE_PRODUCT_TYPE));
|
if (CollectionUtils.isEmpty(products)) {
|
continue;
|
}
|
List<SalesLedgerProduct> 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<Long> 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<ShippingInfo>().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<ShippingInfo> rows = shippingInfoMapper.selectList(
|
new LambdaQueryWrapper<ShippingInfo>().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<ApproveActor> 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<ShippingInfo> unsent = shippingInfoMapper.selectList(new LambdaQueryWrapper<ShippingInfo>()
|
.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<SalesLedgerProduct> ledgerAllProducts = salesLedgerProductMapper.selectList(
|
new LambdaQueryWrapper<SalesLedgerProduct>().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);
|
}
|
}
|
