SqlProvider.java
2.8 KB
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
43
44
45
46
47
package com.huaheng.api.erp.service;
import org.apache.ibatis.jdbc.SQL;
import java.time.LocalDate;
import java.util.Calendar;
public class SqlProvider {
public String selectCustomQuery(final String boxCode, final String chipCode) {
//获取当前年 取尾数决定去那个service做处理
LocalDate currentDate = LocalDate.now(); // 获取当前日期
int currentYear = currentDate.getYear(); // 获取当前年份
int lastDigit = currentYear % 10; // 取尾数
String table1 = "receipt_container_detail_" + (lastDigit-1);
String table2 = "receipt_container_detail_" + lastDigit;
return new SQL() {{
SELECT("e.customerCode AS customerCode",
"e.customerName AS customerName",
"e.referCode AS invoiceNumber",
"e.shipmentNote AS shipmentNote",
"e.driver_leave_time AS confirmedArrivalTime",
"e.driverName AS driverInformation");
FROM("(SELECT * FROM " + table1 + " WHERE containerType='J' AND (chipCode1=#{chipCode} OR boxCode=#{boxCode}) " +
"UNION ALL " +
"SELECT * FROM " + table2 + " WHERE containerType='J' AND (chipCode1=#{chipCode} OR boxCode=#{boxCode})) a");
LEFT_OUTER_JOIN("(SELECT a.boxCode, a.chipCode1, a.materialCode, a.materialName, a.containerCode, " +
"CASE WHEN b.num=1 THEN '否' ELSE '是' END AS mix, '箱' AS Package, d.customerCode, d.customerName, " +
"d.referCode, d.shipmentNote, " +
"CONCAT(d.driverName, '/', IFNULL(e.car_number, ''), '/', IFNULL(e.driver_name, ''), '/', IFNULL(e.phone_number, '')) AS driverName, " +
"e.driver_leave_time " +
"FROM inventory_history_detail a " +
"LEFT JOIN (SELECT containerCode, count(1) AS num FROM (SELECT DISTINCT containerCode, materialCode, `level`, color " +
"FROM inventory_history_detail " +
"WHERE containerCode IN (SELECT DISTINCT containerCode " +
"FROM inventory_history_detail " +
"WHERE left(containerCode, 1) <> 'J' AND (chipCode1=#{chipCode} OR boxCode=#{boxCode}))) a GROUP BY containerCode) b " +
"ON b.containerCode = a.containerCode " +
"LEFT JOIN receipt_header c ON c.code = a.receiptCode " +
"LEFT JOIN shipment_header d ON d.code = c.shipmentCode " +
"LEFT JOIN reservation_submission e ON e.order_number = d.referCode " +
"WHERE left(a.containerCode, 1) <> 'J' AND (a.chipCode1=#{chipCode} OR a.boxCode=#{boxCode})) e " +
"ON e.boxCode = a.boxCode AND e.chipCode1 = a.chipCode1");
}}.toString();
}
}