From f7ac54f86129a2d4875c7107a84556849c3d8c2a Mon Sep 17 00:00:00 2001
From: chen.llin <1442464845@qq.comm>
Date: 星期三, 24 十二月 2025 18:16:54 +0800
Subject: [PATCH] 月结-完成字段调整,和部分sql优化
---
src/main/resources/mapper/ManLocDetlMapper.xml | 18 +++
src/main/resources/mapper/MonthlySettleMapper.xml | 121 ++++++++++----------
src/main/java/com/zy/asrs/service/impl/MonthlySettleServiceImpl.java | 154 +++++++++++++++++++++++--
src/main/java/com/zy/asrs/service/ManLocDetlService.java | 6 +
src/main/java/com/zy/asrs/service/impl/ManLocDetlServiceImpl.java | 8 +
src/main/java/com/zy/asrs/mapper/ManLocDetlMapper.java | 6 +
src/main/java/com/zy/asrs/mapper/MonthlySettleMapper.java | 8
src/main/java/com/zy/asrs/service/MonthlySettleService.java | 7 +
8 files changed, 249 insertions(+), 79 deletions(-)
diff --git a/src/main/java/com/zy/asrs/mapper/ManLocDetlMapper.java b/src/main/java/com/zy/asrs/mapper/ManLocDetlMapper.java
index 3f2b4e5..3869f68 100644
--- a/src/main/java/com/zy/asrs/mapper/ManLocDetlMapper.java
+++ b/src/main/java/com/zy/asrs/mapper/ManLocDetlMapper.java
@@ -57,6 +57,12 @@
Double queryStockAnfme(String matnr, String batch);
+ /**
+ * 鎵归噺鏌ヨ搴撳瓨鏁伴噺
+ * @param matnrBatchList 鐗╂枡缂栫爜鍜屾壒娆＄殑缁勫悎鍒楄〃锛屾牸寮忥細matnr_batch
+ */
+ List<com.zy.asrs.entity.result.StockQtyDTO> queryStockAnfmeBatch(@Param("matnrBatchList") List<String> matnrBatchList);
+
List<StockVo> queryStockTotal();
diff --git a/src/main/java/com/zy/asrs/mapper/MonthlySettleMapper.java b/src/main/java/com/zy/asrs/mapper/MonthlySettleMapper.java
index 8737987..13b17bf 100644
--- a/src/main/java/com/zy/asrs/mapper/MonthlySettleMapper.java
+++ b/src/main/java/com/zy/asrs/mapper/MonthlySettleMapper.java
@@ -20,14 +20,14 @@
MonthlySettle selectLatestSettle();
/**
- * 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呯殑璁㈠崟鏁伴噺
+ * 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呮湭瀹屾垚鐨勫叆搴撹鍗曟暟閲�
*/
- int countOrdersInRange(@Param("startDate") String startDate, @Param("endDate") String endDate);
+ int countUnfinishedOrdersInRangePakin(@Param("startDate") String startDate, @Param("endDate") String endDate);
/**
- * 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呮湭瀹屾垚鐨勮鍗曟暟閲忥紙鍏ュ簱鍜屽嚭搴擄級
+ * 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呮湭瀹屾垚鐨勫嚭搴撹鍗曟暟閲�
*/
- int countUnfinishedOrdersInRange(@Param("startDate") String startDate, @Param("endDate") String endDate);
+ int countUnfinishedOrdersInRangePakout(@Param("startDate") String startDate, @Param("endDate") String endDate);
/**
* 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呯殑鐗╂枡鍑哄叆搴撴暟閲忥紙浠庡叆搴撹〃鏌ヨ锛�
diff --git a/src/main/java/com/zy/asrs/service/ManLocDetlService.java b/src/main/java/com/zy/asrs/service/ManLocDetlService.java
index 7863f35..69b369f 100644
--- a/src/main/java/com/zy/asrs/service/ManLocDetlService.java
+++ b/src/main/java/com/zy/asrs/service/ManLocDetlService.java
@@ -47,6 +47,12 @@
Double queryStockAnfme(String matnr, String batch);
+ /**
+ * 鎵归噺鏌ヨ搴撳瓨鏁伴噺
+ * @param matnrBatchList 鐗╂枡缂栫爜鍜屾壒娆＄殑缁勫悎鍒楄〃锛屾牸寮忥細matnr_batch
+ */
+ List<com.zy.asrs.entity.result.StockQtyDTO> queryStockAnfmeBatch(List<String> matnrBatchList);
+
List<StockVo> queryStockTotal();
diff --git a/src/main/java/com/zy/asrs/service/MonthlySettleService.java b/src/main/java/com/zy/asrs/service/MonthlySettleService.java
index 599215d..b9b54d1 100644
--- a/src/main/java/com/zy/asrs/service/MonthlySettleService.java
+++ b/src/main/java/com/zy/asrs/service/MonthlySettleService.java
@@ -49,6 +49,13 @@
* 鍒犻櫎鏈堢粨璁板綍
*/
void deleteSettle(Long settleId);
+
+ /**
+ * 妫�鏌ユ寚瀹氭棩鏈熸槸鍚﹀湪浠讳綍宸叉湀缁撶殑鍖洪棿鍐�
+ * @param orderTime 璁㈠崟涓氬姟鏃堕棿锛堝瓧绗︿覆鏍煎紡锛歽yyy-MM-dd HH:mm:ss锛�
+ * @return 濡傛灉鏃ユ湡鍦ㄤ换浣曞凡鏈堢粨鍖洪棿鍐咃紝杩斿洖true锛涘惁鍒欒繑鍥瀎alse
+ */
+ boolean isOrderTimeInSettledRange(String orderTime);
}
diff --git a/src/main/java/com/zy/asrs/service/impl/ManLocDetlServiceImpl.java b/src/main/java/com/zy/asrs/service/impl/ManLocDetlServiceImpl.java
index 690ca25..f022938 100644
--- a/src/main/java/com/zy/asrs/service/impl/ManLocDetlServiceImpl.java
+++ b/src/main/java/com/zy/asrs/service/impl/ManLocDetlServiceImpl.java
@@ -105,6 +105,14 @@
}
@Override
+ public List<com.zy.asrs.entity.result.StockQtyDTO> queryStockAnfmeBatch(List<String> matnrBatchList) {
+ if (matnrBatchList == null || matnrBatchList.isEmpty()) {
+ return new java.util.ArrayList<>();
+ }
+ return this.baseMapper.queryStockAnfmeBatch(matnrBatchList);
+ }
+
+ @Override
public List<StockVo> queryStockTotal() {
return this.baseMapper.queryStockTotal();
}
diff --git a/src/main/java/com/zy/asrs/service/impl/MonthlySettleServiceImpl.java b/src/main/java/com/zy/asrs/service/impl/MonthlySettleServiceImpl.java
index 79921c6..0325a9d 100644
--- a/src/main/java/com/zy/asrs/service/impl/MonthlySettleServiceImpl.java
+++ b/src/main/java/com/zy/asrs/service/impl/MonthlySettleServiceImpl.java
@@ -3,6 +3,7 @@
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
+import com.core.common.Cools;
import com.core.common.SnowflakeIdWorker;
import com.core.exception.CoolException;
import com.zy.asrs.entity.MonthlySettle;
@@ -91,12 +92,21 @@
cal.set(Calendar.MILLISECOND, 999);
endDate = cal.getTime();
- SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
+ // 娉ㄦ剰锛歰rder_time 鏄� varchar 绫诲瀷锛岄渶瑕佹牸寮忓寲涓哄畬鏁寸殑鏃ユ湡鏃堕棿瀛楃涓茶繘琛屾瘮杈�
SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- String startDateStr = dateFormat.format(startDate);
+ // 璧峰鏃ユ湡璁剧疆涓哄綋澶╃殑 00:00:00
+ Calendar startCal = Calendar.getInstance();
+ startCal.setTime(startDate);
+ startCal.set(Calendar.HOUR_OF_DAY, 0);
+ startCal.set(Calendar.MINUTE, 0);
+ startCal.set(Calendar.SECOND, 0);
+ startCal.set(Calendar.MILLISECOND, 0);
+ String startDateStr = dateTimeFormat.format(startCal.getTime());
String endDateStr = dateTimeFormat.format(endDate);
- int count = this.baseMapper.countUnfinishedOrdersInRange(startDateStr, endDateStr);
- return count > 0;
+ // 鍒嗕袱娆℃煡璇細鍏ュ簱鍜屽嚭搴�
+ int pakinCount = this.baseMapper.countUnfinishedOrdersInRangePakin(startDateStr, endDateStr);
+ int pakoutCount = this.baseMapper.countUnfinishedOrdersInRangePakout(startDateStr, endDateStr);
+ return (pakinCount + pakoutCount) > 0;
}
@Override
@@ -141,9 +151,16 @@
}
// 缁熻鐗╂枡鍑哄叆搴撴暟閲忥紙鍒嗗埆鏌ヨ涓や釜琛紝鍦↗ava浠ｇ爜涓悎骞讹級
- SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
+ // 娉ㄦ剰锛歰rder_time 鏄� varchar 绫诲瀷锛岄渶瑕佹牸寮忓寲涓哄畬鏁寸殑鏃ユ湡鏃堕棿瀛楃涓茶繘琛屾瘮杈�
SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- String startDateStr = dateFormat.format(startDate);
+ // 璧峰鏃ユ湡璁剧疆涓哄綋澶╃殑 00:00:00
+ Calendar startCal = Calendar.getInstance();
+ startCal.setTime(startDate);
+ startCal.set(Calendar.HOUR_OF_DAY, 0);
+ startCal.set(Calendar.MINUTE, 0);
+ startCal.set(Calendar.SECOND, 0);
+ startCal.set(Calendar.MILLISECOND, 0);
+ String startDateStr = dateTimeFormat.format(startCal.getTime());
String endDateStr = dateTimeFormat.format(endDate);
// 鍒嗗埆鏌ヨ鍏ュ簱琛ㄥ拰鍑哄簱琛�
@@ -259,6 +276,42 @@
currentMaterialMap.put(key, stat);
}
+ // 鏀堕泦鎵�鏈夐渶瑕佹煡璇㈠簱瀛樼殑鐗╂枡锛堟湰鏈熸湁鍑哄叆搴撶殑 + 涓婁竴鏈熷瓨鍦ㄤ絾鏈湡娌℃湁鍑哄叆搴撶殑锛�
+ List<String> matnrBatchList = new java.util.ArrayList<>();
+ for (MaterialInOutStatDTO stat : materialStats) {
+ String matnr = stat.getMatnr();
+ String batch = stat.getBatch() != null ? stat.getBatch() : "";
+ matnrBatchList.add(matnr + "_" + batch);
+ }
+ if (previousSettle != null && !previousDetailMap.isEmpty()) {
+ for (Map.Entry<String, PreviousSettleEndingQtyDTO> entry : previousDetailMap.entrySet()) {
+ String key = entry.getKey();
+ if (!currentMaterialMap.containsKey(key)) {
+ PreviousSettleEndingQtyDTO previousDetail = entry.getValue();
+ String matnr = previousDetail.getMatnr();
+ String batch = previousDetail.getBatch() != null ? previousDetail.getBatch() : "";
+ String matnrBatchKey = matnr + "_" + batch;
+ if (!matnrBatchList.contains(matnrBatchKey)) {
+ matnrBatchList.add(matnrBatchKey);
+ }
+ }
+ }
+ }
+ // 鎵归噺鏌ヨ搴撳瓨
+ Map<String, BigDecimal> stockQtyMap = new HashMap<>();
+ if (!matnrBatchList.isEmpty()) {
+ List<com.zy.asrs.entity.result.StockQtyDTO> stockQtyList = manLocDetlService.queryStockAnfmeBatch(matnrBatchList);
+ if (stockQtyList != null) {
+ for (com.zy.asrs.entity.result.StockQtyDTO stockQtyDTO : stockQtyList) {
+ String key = stockQtyDTO.getMatnr() + "_" + (stockQtyDTO.getBatch() != null ? stockQtyDTO.getBatch() : "");
+ stockQtyMap.put(key, stockQtyDTO.getStockQty() != null ? stockQtyDTO.getStockQty() : BigDecimal.ZERO);
+ }
+ }
+ }
+
+ // 鏀堕泦鎵�鏈夋槑缁嗚褰曪紝鐢ㄤ簬鎵归噺鎻掑叆
+ List<MonthlySettleDetail> detailList = new java.util.ArrayList<>();
+
// 1. 澶勭悊鏈湡鏈夊嚭鍏ュ簱鐨勭墿鏂�
for (MaterialInOutStatDTO stat : materialStats) {
// 1. 鎻愬彇鍩虹淇℃伅
@@ -274,16 +327,18 @@
// 3. 璁＄畻搴撳瓨鐩稿叧鏁伴噺
BigDecimal beginningQty = getBeginningQty(matnr, batch, brand, previousEndingQtyMap);
BigDecimal endingQty = calculateEndingQty(beginningQty, inQty, outQty);
- BigDecimal stockQtyDecimal = getCurrentStockQty(matnr, batch);
+ // 浠庢壒閲忔煡璇㈢殑Map涓幏鍙栧簱瀛�
+ String stockKey = matnr + "_" + batch;
+ BigDecimal stockQtyDecimal = stockQtyMap.getOrDefault(stockKey, BigDecimal.ZERO);
BigDecimal diffQty = calculateDiffQty(stockQtyDecimal, endingQty);
- // 4. 鍒涘缓骞朵繚瀛樻槑缁嗚褰�
+ // 4. 鍒涘缓鏄庣粏璁板綍锛堟殏涓嶆彃鍏ワ級
MonthlySettleDetail detail = buildMonthlySettleDetail(
monthlySettle.getId(), settleNo, matnr, batch, maktx, brand,
beginningQty, inQty, outQty, endingQty, stockQtyDecimal, diffQty
);
detail.setIsDeleted(0); // 鏈垹闄�
- monthlySettleDetailMapper.insert(detail);
+ detailList.add(detail);
// 5. 绱缁熻
totalInQty = totalInQty.add(inQty);
@@ -315,21 +370,36 @@
BigDecimal beginningQty = previousEndingQty;
// 鏈熸湯搴撳瓨 = 鏈熷垵 + 鍏ュ簱 - 鍑哄簱 = 鏈熷垵锛堝洜涓烘湰鏈熸病鏈夊嚭鍏ュ簱锛�
BigDecimal endingQty = beginningQty;
- // 鑾峰彇褰撳墠瀹為檯搴撳瓨
- BigDecimal stockQtyDecimal = getCurrentStockQty(matnr, batch);
+ // 浠庢壒閲忔煡璇㈢殑Map涓幏鍙栧簱瀛�
+ String stockKey = matnr + "_" + batch;
+ BigDecimal stockQtyDecimal = stockQtyMap.getOrDefault(stockKey, BigDecimal.ZERO);
// 璁＄畻宸紓
BigDecimal diffQty = calculateDiffQty(stockQtyDecimal, endingQty);
- // 鍒涘缓骞朵繚瀛樻槑缁嗚褰�
+ // 鍒涘缓鏄庣粏璁板綍锛堟殏涓嶆彃鍏ワ級
MonthlySettleDetail detail = buildMonthlySettleDetail(
monthlySettle.getId(), settleNo, matnr, batch, maktx, brand,
beginningQty, inQty, outQty, endingQty, stockQtyDecimal, diffQty
);
detail.setIsDeleted(0); // 鏈垹闄�
- monthlySettleDetailMapper.insert(detail);
+ detailList.add(detail);
// 绱缁熻锛堣櫧鐒跺叆搴撳拰鍑哄簱涓�0锛屼絾涔熻璁″叆鐗╂枡绉嶇被鏁帮級
materialCount++;
+ }
+ }
+ }
+
+ // 鍒嗘壒鎻掑叆锛屾瘡鎵�1000鏉★紝閬垮厤涓�娆℃�ф彃鍏ヨ繃澶氭暟鎹�
+ if (!detailList.isEmpty()) {
+ int batchSize = 1000;
+ for (int i = 0; i < detailList.size(); i += batchSize) {
+ int end = Math.min(i + batchSize, detailList.size());
+ List<MonthlySettleDetail> batch = detailList.subList(i, end);
+ for (MonthlySettleDetail detail : batch) {
+ if (monthlySettleDetailMapper.insert(detail) <= 0) {
+ throw new CoolException("鎻掑叆鏈堢粨鏄庣粏澶辫触");
+ }
}
}
}
@@ -429,8 +499,10 @@
}
/**
- * 鑾峰彇褰撳墠瀹為檯搴撳瓨鏁伴噺
+ * 鑾峰彇褰撳墠瀹為檯搴撳瓨鏁伴噺锛堝凡搴熷純锛屾敼鐢ㄦ壒閲忔煡璇級
+ * @deprecated 浣跨敤鎵归噺鏌ヨ鏂规硶鏇夸唬锛岄伩鍏峃+1鏌ヨ闂
*/
+ @Deprecated
private BigDecimal getCurrentStockQty(String matnr, String batch) {
Double stockQty = manLocDetlService.queryStockAnfme(matnr, batch);
return stockQty != null ? BigDecimal.valueOf(stockQty) : BigDecimal.ZERO;
@@ -499,5 +571,59 @@
settle.setUpdateTime(new Date());
this.updateById(settle);
}
+
+ @Override
+ public boolean isOrderTimeInSettledRange(String orderTime) {
+ if (Cools.isEmpty(orderTime)) {
+ return false;
+ }
+ try {
+ // 瑙ｆ瀽璁㈠崟涓氬姟鏃堕棿
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ Date orderDate = sdf.parse(orderTime);
+
+ // 鏌ヨ鎵�鏈夊凡鏈堢粨鐨勮褰曪紙status=1涓旀湭鍒犻櫎锛�
+ EntityWrapper<MonthlySettle> wrapper = new EntityWrapper<>();
+ wrapper.eq("status", 1);
+ wrapper.eq("is_deleted", 0);
+ List<MonthlySettle> settledList = this.selectList(wrapper);
+
+ if (settledList == null || settledList.isEmpty()) {
+ return false;
+ }
+
+ // 妫�鏌ヨ鍗曟椂闂存槸鍚﹀湪浠讳綍宸叉湀缁撶殑鍖洪棿鍐�
+ for (MonthlySettle settle : settledList) {
+ Date startDate = settle.getStartDate();
+ Date endDate = settle.getEndDate();
+
+ // 纭繚startDate鏄綋澶╃殑00:00:00
+ Calendar startCal = Calendar.getInstance();
+ startCal.setTime(startDate);
+ startCal.set(Calendar.HOUR_OF_DAY, 0);
+ startCal.set(Calendar.MINUTE, 0);
+ startCal.set(Calendar.SECOND, 0);
+ startCal.set(Calendar.MILLISECOND, 0);
+
+ // 纭繚endDate鏄綋澶╃殑23:59:59.999
+ Calendar endCal = Calendar.getInstance();
+ endCal.setTime(endDate);
+ endCal.set(Calendar.HOUR_OF_DAY, 23);
+ endCal.set(Calendar.MINUTE, 59);
+ endCal.set(Calendar.SECOND, 59);
+ endCal.set(Calendar.MILLISECOND, 999);
+
+ // 鍒ゆ柇璁㈠崟鏃堕棿鏄惁鍦ㄥ尯闂村唴锛歔startDate鐨�00:00:00, endDate鐨�23:59:59.999]
+ if (!orderDate.before(startCal.getTime()) && !orderDate.after(endCal.getTime())) {
+ return true;
+ }
+ }
+
+ return false;
+ } catch (Exception e) {
+ log.error("妫�鏌ヨ鍗曟椂闂存槸鍚﹀湪宸叉湀缁撳尯闂村唴澶辫触", e);
+ return false;
+ }
+ }
}
diff --git a/src/main/resources/mapper/ManLocDetlMapper.xml b/src/main/resources/mapper/ManLocDetlMapper.xml
index 940e46c..5fabe93 100644
--- a/src/main/resources/mapper/ManLocDetlMapper.xml
+++ b/src/main/resources/mapper/ManLocDetlMapper.xml
@@ -313,4 +313,22 @@
where node_id = #{nodeId} ;
</update>
+ <!-- 鎵归噺鏌ヨ搴撳瓨鏁伴噺 -->
+ <select id="queryStockAnfmeBatch" resultType="com.zy.asrs.entity.result.StockQtyDTO">
+ SELECT
+ matnr,
+ ISNULL(batch, '') as batch,
+ SUM(anfme) as stockQty
+ FROM man_loc_detl
+ WHERE 1=1
+ <if test="matnrBatchList != null and matnrBatchList.size > 0">
+ AND (
+ <foreach collection="matnrBatchList" item="item" separator=" OR ">
+ (matnr + '_' + ISNULL(batch, '') = #{item})
+ </foreach>
+ )
+ </if>
+ GROUP BY matnr, ISNULL(batch, '')
+ </select>
+
</mapper>
diff --git a/src/main/resources/mapper/MonthlySettleMapper.xml b/src/main/resources/mapper/MonthlySettleMapper.xml
index 318d66e..77e8f9f 100644
--- a/src/main/resources/mapper/MonthlySettleMapper.xml
+++ b/src/main/resources/mapper/MonthlySettleMapper.xml
@@ -27,75 +27,74 @@
ORDER BY end_date DESC
</select>
- <!-- 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呯殑璁㈠崟鏁伴噺 -->
- <select id="countOrdersInRange" resultType="int">
- SELECT COUNT(*) FROM man_order
- WHERE status = 1
- AND order_time >= #{startDate}
- AND order_time <= #{endDate}
+ <!-- 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呮湭瀹屾垚鐨勫叆搴撹鍗曟暟閲� -->
+ <select id="countUnfinishedOrdersInRangePakin" resultType="int">
+ SELECT COUNT(1) FROM man_order_log_pakin molpi
+ WHERE molpi.status = 1
+ AND molpi.order_time >= #{startDate}
+ AND molpi.order_time <= #{endDate}
+ AND (molpi.settle IS NULL OR molpi.settle != 6)
+ AND (molpi.monthly_settle_id IS NULL OR molpi.monthly_settle_id = 0)
+ AND EXISTS (
+ SELECT 1 FROM man_order_detl_log_pakin modlpi
+ WHERE modlpi.order_id = molpi.id
+ AND (modlpi.anfme > modlpi.qty OR modlpi.qty IS NULL)
+ )
</select>
- <!-- 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呮湭瀹屾垚鐨勮鍗曟暟閲忥紙鍏ュ簱鍜屽嚭搴擄級 -->
- <select id="countUnfinishedOrdersInRange" resultType="int">
- SELECT COUNT(*) FROM (
- SELECT DISTINCT o.id FROM man_order_log_pakin o
- INNER JOIN man_order_detl_log_pakin od ON o.id = od.order_id
- WHERE o.status = 1
- AND CONVERT(date, o.order_time) >= #{startDate}
- AND o.order_time <= #{endDate}
- AND o.move_status != 2
- AND (od.anfme > od.qty OR od.qty IS NULL)
- AND (o.monthly_settle_id IS NULL OR o.monthly_settle_id = 0)
- UNION
- SELECT DISTINCT o.id FROM man_order_log_pakout o
- INNER JOIN man_order_detl_log_pakout od ON o.id = od.order_id
- WHERE o.status = 1
- AND CONVERT(date, o.order_time) >= #{startDate}
- AND o.order_time <= #{endDate}
- AND o.move_status != 2
- AND (od.anfme > od.qty OR od.qty IS NULL)
- AND (o.monthly_settle_id IS NULL OR o.monthly_settle_id = 0)
- ) t
+ <!-- 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呮湭瀹屾垚鐨勫嚭搴撹鍗曟暟閲� -->
+ <select id="countUnfinishedOrdersInRangePakout" resultType="int">
+ SELECT COUNT(1) FROM man_order_log_pakout molpo
+ WHERE molpo.status = 1
+ AND molpo.order_time >= #{startDate}
+ AND molpo.order_time <= #{endDate}
+ AND (molpo.settle IS NULL OR molpo.settle != 6)
+ AND (molpo.monthly_settle_id IS NULL OR molpo.monthly_settle_id = 0)
+ AND EXISTS (
+ SELECT 1 FROM man_order_detl_log_pakout modlpo
+ WHERE modlpo.order_id = molpo.id
+ AND (modlpo.anfme > modlpo.qty OR modlpo.qty IS NULL)
+ )
</select>
<!-- 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呯殑鐗╂枡鍑哄叆搴撴暟閲忥紙浠庡叆搴撹〃鏌ヨ锛� -->
<select id="statisticsMaterialInOutFromPakin" resultType="com.zy.asrs.entity.result.MaterialInOutRawDTO">
SELECT
- od.matnr,
- od.maktx,
- od.batch,
- od.brand,
- SUM(od.qty) as qty,
- COALESCE(od.pakin_pakout_status, o.pakin_pakout_status) as pakinPakoutStatus
- FROM man_order_log_pakin o
- INNER JOIN man_order_detl_log_pakin od ON o.id = od.order_id
- WHERE o.status = 1
- AND CONVERT(date, o.order_time) >= #{startDate}
- AND o.order_time <= #{endDate}
- AND o.move_status = 2
- AND (o.monthly_settle_id IS NULL OR o.monthly_settle_id = 0)
- AND COALESCE(od.pakin_pakout_status, o.pakin_pakout_status) IN (1, 2)
- GROUP BY od.matnr, od.maktx, od.batch, od.brand, COALESCE(od.pakin_pakout_status, o.pakin_pakout_status)
+ modlpi.matnr,
+ modlpi.maktx,
+ modlpi.batch,
+ modlpi.brand,
+ SUM(modlpi.qty) as qty,
+ COALESCE(modlpi.pakin_pakout_status, molpi.pakin_pakout_status) as pakinPakoutStatus
+ FROM man_order_log_pakin molpi
+ INNER JOIN man_order_detl_log_pakin modlpi ON molpi.id = modlpi.order_id
+ WHERE molpi.status = 1
+ AND molpi.order_time >= #{startDate}
+ AND molpi.order_time <= #{endDate}
+ AND molpi.settle = 6
+ AND (molpi.monthly_settle_id IS NULL OR molpi.monthly_settle_id = 0)
+ AND COALESCE(modlpi.pakin_pakout_status, molpi.pakin_pakout_status) IN (1, 2)
+ GROUP BY modlpi.matnr, modlpi.maktx, modlpi.batch, modlpi.brand, COALESCE(modlpi.pakin_pakout_status, molpi.pakin_pakout_status)
</select>
<!-- 缁熻鏈堢粨鏃堕棿鑼冨洿鍐呯殑鐗╂枡鍑哄叆搴撴暟閲忥紙浠庡嚭搴撹〃鏌ヨ锛� -->
<select id="statisticsMaterialInOutFromPakout" resultType="com.zy.asrs.entity.result.MaterialInOutRawDTO">
SELECT
- od.matnr,
- od.maktx,
- od.batch,
- od.brand,
- SUM(od.qty) as qty,
- COALESCE(od.pakin_pakout_status, o.pakin_pakout_status) as pakinPakoutStatus
- FROM man_order_log_pakout o
- INNER JOIN man_order_detl_log_pakout od ON o.id = od.order_id
- WHERE o.status = 1
- AND CONVERT(date, o.order_time) >= #{startDate}
- AND o.order_time <= #{endDate}
- AND o.move_status = 2
- AND (o.monthly_settle_id IS NULL OR o.monthly_settle_id = 0)
- AND COALESCE(od.pakin_pakout_status, o.pakin_pakout_status) IN (1, 2)
- GROUP BY od.matnr, od.maktx, od.batch, od.brand, COALESCE(od.pakin_pakout_status, o.pakin_pakout_status)
+ modlpo.matnr,
+ modlpo.maktx,
+ modlpo.batch,
+ modlpo.brand,
+ SUM(modlpo.qty) as qty,
+ COALESCE(modlpo.pakin_pakout_status, molpo.pakin_pakout_status) as pakinPakoutStatus
+ FROM man_order_log_pakout molpo
+ INNER JOIN man_order_detl_log_pakout modlpo ON molpo.id = modlpo.order_id
+ WHERE molpo.status = 1
+ AND molpo.order_time >= #{startDate}
+ AND molpo.order_time <= #{endDate}
+ AND molpo.settle = 6
+ AND (molpo.monthly_settle_id IS NULL OR molpo.monthly_settle_id = 0)
+ AND COALESCE(modlpo.pakin_pakout_status, molpo.pakin_pakout_status) IN (1, 2)
+ GROUP BY modlpo.matnr, modlpo.maktx, modlpo.batch, modlpo.brand, COALESCE(modlpo.pakin_pakout_status, molpo.pakin_pakout_status)
</select>
<!-- 鑾峰彇涓婁竴涓湀缁撶殑鐗╂枡鏈熸湯搴撳瓨 -->
@@ -117,9 +116,9 @@
SET monthly_settle_id = #{settleId},
monthly_settle_no = #{settleNo}
WHERE status = 1
- AND CONVERT(date, order_time) >= #{startDate}
+ AND order_time >= #{startDate}
AND order_time <= #{endDate}
- AND move_status = 2
+ AND settle = 6
AND (monthly_settle_id IS NULL OR monthly_settle_id = 0)
</update>
@@ -129,9 +128,9 @@
SET monthly_settle_id = #{settleId},
monthly_settle_no = #{settleNo}
WHERE status = 1
- AND CONVERT(date, order_time) >= #{startDate}
+ AND order_time >= #{startDate}
AND order_time <= #{endDate}
- AND move_status = 2
+ AND settle = 6
AND (monthly_settle_id IS NULL OR monthly_settle_id = 0)
</update>
--
Gitblit v1.9.1