1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
| 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)); }
| }
| }
| }
| }
|
|
