package com.ruoyi.sales;
|
|
import com.ruoyi.RuoYiApplication;
|
import com.ruoyi.common.enums.ApproveTypeEnum;
|
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.util.ArrayList;
|
import java.util.LinkedHashSet;
|
import java.util.List;
|
import java.util.Set;
|
import java.util.stream.Collectors;
|
|
/**
|
* 清理多出来的「发货审批」{@code approve_process}（{@code approve_type=7}，事由 {@code 发货审批:合同号}）。
|
* <p>
|
* 典型场景：销售订单已发货条数与有效发货审批条数应一致；若批量脚本重复插入，会出现同一事由多条
|
* {@code approve_delete=0} 的记录，或存在合同号在「未发货」台账上仍有一条发货审批的孤儿数据。
|
* <p>
|
* 处理策略（与若依删除习惯一致，<b>软删除</b>）：
|
* <ol>
|
* <li><b>重复事由</b>：同一 {@code approve_reason} 下保留 {@code id} 最小的一条，其余 {@code approve_delete=1}。</li>
|
* <li><b>孤儿事由</b>：事由解析出的合同号在 {@code sales_ledger} 中不存在 {@code delivery_status=5}（已发货）的订单时，
|
* 将该发货审批软删除。（若你希望以其它规则定义「已发货」，需改 SQL。）</li>
|
* </ol>
|
* 同步将对应 {@code approve_node} 的 {@code delete_flag} 置为 1（删除标记）。
|
* <p>
|
* <b>执行前必须</b>：VM options {@code -Druoyi.deliveryApprove.cleanup.commit=true} 或环境变量
|
* {@code RUOYI_DELIVERY_APPROVE_CLEANUP_COMMIT=true}，且连业务库。未开启时本用例跳过。
|
*/
|
@SpringBootTest(classes = RuoYiApplication.class)
|
class DeliveryApproveExtraCleanupTest {
|
|
private static final Logger log = LoggerFactory.getLogger(DeliveryApproveExtraCleanupTest.class);
|
|
private static final String REASON_PREFIX = "发货审批:";
|
|
@Autowired
|
private DataSource dataSource;
|
|
@Test
|
@Transactional
|
@Rollback(false)
|
void softRemoveExtraDeliveryApprovals() {
|
NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(dataSource);
|
int type7 = ApproveTypeEnum.DELIVERY.getCode();
|
|
List<ApproveRowRef> duplicateExtras = selectDuplicateDeliveryExtras(named, type7);
|
int n1 = applySoftRemove(named, duplicateExtras);
|
log.warn("[发货审批清理] 重复事由软删除行数={}, id列表={}", n1, formatIds(duplicateExtras));
|
|
List<ApproveRowRef> orphans = selectOrphanDeliveryApprovals(named, type7);
|
int n2 = applySoftRemove(named, orphans);
|
log.warn("[发货审批清理] 孤儿事由软删除行数={}, id列表={}", n2, formatIds(orphans));
|
|
log.warn("[发货审批清理] 完成。重复={}, 孤儿={}, 合计软删除={}", n1, n2, n1 + n2);
|
}
|
|
private static String formatIds(List<ApproveRowRef> refs) {
|
return refs.stream().map(r -> String.valueOf(r.id)).collect(Collectors.joining(","));
|
}
|
|
private List<ApproveRowRef> selectDuplicateDeliveryExtras(NamedParameterJdbcTemplate named, int approveType) {
|
MapSqlParameterSource p = new MapSqlParameterSource();
|
p.addValue("atype", approveType);
|
p.addValue("pfx", REASON_PREFIX + "%");
|
return named.query(
|
"SELECT ap.id, ap.approve_id AS approveId FROM approve_process ap "
|
+ "INNER JOIN ( "
|
+ " SELECT approve_reason, MIN(id) AS keep_id "
|
+ " FROM approve_process "
|
+ " WHERE approve_delete = 0 AND approve_type = :atype AND approve_reason LIKE :pfx "
|
+ " GROUP BY approve_reason "
|
+ " HAVING COUNT(*) > 1 "
|
+ ") d ON ap.approve_reason = d.approve_reason AND ap.id <> d.keep_id "
|
+ "WHERE ap.approve_delete = 0 AND ap.approve_type = :atype",
|
p,
|
(rs, i) -> new ApproveRowRef(rs.getLong("id"), rs.getString("approveId")));
|
}
|
|
/**
|
* 事由中合同号在「已发货」销售台账中不存在则视为孤儿
|
*/
|
private List<ApproveRowRef> selectOrphanDeliveryApprovals(NamedParameterJdbcTemplate named, int approveType) {
|
MapSqlParameterSource p = new MapSqlParameterSource();
|
p.addValue("atype", approveType);
|
p.addValue("pfx", REASON_PREFIX + "%");
|
return named.query(
|
"SELECT ap.id, ap.approve_id AS approveId FROM approve_process ap "
|
+ "WHERE ap.approve_delete = 0 AND ap.approve_type = :atype AND ap.approve_reason LIKE :pfx "
|
+ "AND NOT EXISTS ( "
|
+ " SELECT 1 FROM sales_ledger sl "
|
+ " WHERE sl.sales_contract_no = TRIM(SUBSTRING(ap.approve_reason, CHAR_LENGTH('发货审批:') + 1)) "
|
+ " AND sl.delivery_status = 5 "
|
+ ")",
|
p,
|
(rs, i) -> new ApproveRowRef(rs.getLong("id"), rs.getString("approveId")));
|
}
|
|
private int applySoftRemove(NamedParameterJdbcTemplate named, List<ApproveRowRef> refs) {
|
if (refs.isEmpty()) {
|
return 0;
|
}
|
Set<Long> idSet = new LinkedHashSet<>();
|
Set<String> approveIdSet = new LinkedHashSet<>();
|
for (ApproveRowRef r : refs) {
|
idSet.add(r.id);
|
if (r.approveId != null) {
|
approveIdSet.add(r.approveId);
|
}
|
}
|
List<Long> ids = new ArrayList<>(idSet);
|
List<String> approveIds = new ArrayList<>(approveIdSet);
|
|
MapSqlParameterSource p1 = new MapSqlParameterSource();
|
p1.addValue("ids", ids);
|
int u1 = named.update("UPDATE approve_process SET approve_delete = 1 WHERE id IN (:ids)", p1);
|
|
MapSqlParameterSource p2 = new MapSqlParameterSource();
|
p2.addValue("aids", approveIds);
|
int u2 = named.update("UPDATE approve_node SET delete_flag = 1 WHERE delete_flag = 0 AND approve_process_id IN (:aids)", p2);
|
|
log.debug("approve_process 更新行数={}, approve_node 更新行数={}", u1, u2);
|
return u1;
|
}
|
|
private static final class ApproveRowRef {
|
private final long id;
|
private final String approveId;
|
|
private ApproveRowRef(long id, String approveId) {
|
this.id = id;
|
this.approveId = approveId;
|
}
|
}
|
}
|
