package cn.iocoder.yudao.module.mes.service.wm.returnsales; import cn.iocoder.yudao.module.mes.dal.mysql.wm.productsales.MesWmProductSalesDetailMapper; import org.apache.ibatis.annotations.Select; import org.junit.jupiter.api.Test; import java.sql.DriverManager; import static org.junit.jupiter.api.Assertions.assertEquals; /** 验证实际出库关联查询不会把其他订单、待出库单或其他盘号当作退货来源。 */ class MesWmFinishedSaleBatchSqlTest { @Test void checksFinishedOrderItemAndBatch() throws Exception { try (var c = DriverManager.getConnection("jdbc:h2:mem:reel_source;MODE=MySQL;DB_CLOSE_DELAY=-1")) { var st = c.createStatement(); st.execute("CREATE TABLE mes_wm_product_sales (id BIGINT, sale_order_id BIGINT, status INT, deleted INT)"); st.execute("CREATE TABLE mes_wm_product_sales_line (id BIGINT, sales_id BIGINT, sale_order_item_id BIGINT, item_id BIGINT, deleted INT)"); st.execute("CREATE TABLE mes_wm_product_sales_detail (id BIGINT, sales_id BIGINT, line_id BIGINT, item_id BIGINT, batch_id BIGINT, deleted INT)"); st.execute("CREATE TABLE erp_sale_order_items (id BIGINT, order_id BIGINT, product_id BIGINT, reel_no VARCHAR(128), deleted INT)"); st.execute("CREATE TABLE mes_wm_batch (id BIGINT, item_id BIGINT, reel_no VARCHAR(128), deleted INT)"); st.execute("INSERT INTO mes_wm_product_sales VALUES (1, 7, 5, 0), (2, 7, 1, 0), (3, 8, 5, 0)"); st.execute("INSERT INTO erp_sale_order_items VALUES (11, 7, 3, 'P1', 0), (12, 8, 3, 'P1', 0), (13, 7, 3, 'P2', 0)"); st.execute("INSERT INTO mes_wm_batch VALUES (21, 3, 'P1', 0), (22, 3, 'P2', 0)"); st.execute("INSERT INTO mes_wm_product_sales_line VALUES (31, 1, 11, 3, 0), (32, 2, 13, 3, 0), (33, 3, 12, 3, 0), (34, 1, 13, 3, 0)"); st.execute("INSERT INTO mes_wm_product_sales_detail VALUES (41, 1, 31, 3, 21, 0), (42, 2, 32, 3, 22, 0), (43, 3, 33, 3, 21, 0), (44, 1, 34, 3, 21, 0)"); String sql = MesWmProductSalesDetailMapper.class.getMethod("countFinishedSaleBatch", Long.class, Long.class, Long.class, Integer.class).getAnnotation(Select.class).value()[0] // H2 的字符串比较默认区分大小写;MySQL 的 BINARY 语法在真实库单独验证。 .replace("BINARY i.reel_no", "i.reel_no").replace("BINARY b.reel_no", "b.reel_no") .replace("#{orderId}", "?").replace("#{finishedStatus}", "?") .replace("#{itemId}", "?").replace("#{batchId}", "?"); try (var query = c.prepareStatement(sql)) { query.setLong(1, 7); query.setInt(2, 5); query.setLong(3, 3); query.setLong(4, 3); query.setLong(5, 3); query.setLong(6, 21); try (var rows = query.executeQuery()) { rows.next(); assertEquals(1, rows.getInt(1)); } query.setLong(6, 22); try (var rows = query.executeQuery()) { rows.next(); assertEquals(0, rows.getInt(1)); } } } } }