From f1b5c366456c042b2cfa380d4c63d900dfeec4f8 Mon Sep 17 00:00:00 2001
From: tjx <t2856754968@163.com>
Date: 星期四, 11 十二月 2025 11:13:15 +0800
Subject: [PATCH] 111
---
StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs | 151 +++++++++++++++++++++++---------------------------
1 files changed, 70 insertions(+), 81 deletions(-)
diff --git a/StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs b/StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs
index 27520c0..3be79cf 100644
--- a/StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs
+++ b/StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs
@@ -19,32 +19,22 @@
public PagedResult<ReturnableStockDto> GetReturnableStocks(
ReturnableStockSearchDto searchDto)
{
- // 鍙傛暟鏍￠獙
if (searchDto.PageIndex < 1)
{
throw new Exception("椤电爜蹇呴』澶т簬0");
}
- // if (!new[] { 10, 20, 50 }.Contains(searchDto.PageSize))
- // {
- // throw new Exception("姣忛〉鏉℃暟蹇呴』涓�10銆�20鎴�50");
- // }
+ // 浼樺寲鐐�1: 浣跨敤鍘熺敓SQL ROW_NUMBER()鍦ㄦ暟鎹簱绔畬鎴愬幓閲嶅拰鎺掑簭
+ var rackingTaskSql = @"
+ SELECT ITEM_BARCODE AS ItemBarcode, PALLETCODE AS PalletCode, CODE AS Code
+ FROM (
+ SELECT ITEM_BARCODE, PALLETCODE, CODE,
+ ROW_NUMBER() OVER (PARTITION BY ITEM_BARCODE ORDER BY ID DESC) AS RN
+ FROM XB_RACKING_TASK_SYXT_LOG
+ WHERE ITEM_BARCODE IS NOT NULL AND (CODE IS NULL OR CODE != '500')
+ ) WHERE RN = 1";
- // 1. 鏌ヨXB_RACKING_TASK_SYXT_LOG涓璉temBarcode鍜孭alletCode鐨勬槧灏勫叧绯�
- var allRackingTaskData = Db.Queryable<XbRackingTaskSyxtLog>()
- .Where(x => !string.IsNullOrEmpty(x.ItemBarcode))
- .Select(x => new { x.ItemBarcode, x.PalletCode, x.Code, x.Id })
- .ToList();
-
- // ReturnableStockDto澧炲姞涓�涓簱瀛樼姸鎬佺殑瀛楁
- // 褰揳llRackingTaskData涓殑Code涓簄ull鏃讹紝搴撳瓨鐘舵�佷负绔嬪簱鍏ュ簱涓�
- // 褰揳llRackingTaskData涓殑Code涓�200鏃讹紝搴撳瓨鐘舵�佷负宸茬粡鍦ㄧ珛搴撳唴
- // 瀵规瘡涓潯鐮侊紝鍙栨渶鏂癐D鐨勮褰曪紝骞惰繃婊ゆ帀Code涓�500锛堣〃绀哄け璐ワ級鐨勮褰�
- var rackingTaskData = allRackingTaskData
- .GroupBy(x => x.ItemBarcode) // 鎸夋潯鐮佸垎缁�
- .Select(g => g.OrderByDescending(x => x.Id).First()) // 鍙栨瘡涓潯鐮佹渶鏂扮殑涓�鏉¤褰�
- .Where(x => x.Code != "500") // 杩囨护鎺塁ode涓�500鐨勫け璐ヨ褰�
- .ToList();
+ var rackingTaskData = Db.Ado.SqlQuery<RackingTaskDto>(rackingTaskSql);
if (rackingTaskData == null || !rackingTaskData.Any())
{
@@ -61,11 +51,28 @@
};
}
- // 1.1 鎻愬彇鍘婚噸鍚庣殑鏉＄爜鍒楄〃鐢ㄤ簬鏌ヨ
- var distinctBarcodes = rackingTaskData.Select(x => x.ItemBarcode)
- .Distinct().ToList();
+ // 浼樺寲鐐�2: 杩囨护null鍊煎苟浣跨敤瀛楀吀鎻愰珮鏌ユ壘鏁堢巼
+ var validRackingData = rackingTaskData.Where(x => !string.IsNullOrEmpty(x.ItemBarcode)).ToList();
+
+ if (!validRackingData.Any())
+ {
+ return new PagedResult<ReturnableStockDto>
+ {
+ TbBillList = new List<ReturnableStockDto>(),
+ Pagination = new PaginationInfo
+ {
+ CurrentPage = searchDto.PageIndex,
+ PageSize = searchDto.PageSize,
+ TotalRecords = 0,
+ TotalPages = 0
+ }
+ };
+ }
+
+ var rackingTaskDict = validRackingData.ToDictionary(x => x.ItemBarcode);
+ var distinctBarcodes = validRackingData.Select(x => x.ItemBarcode).ToList();
- // 2. 鏋勫缓鏌ヨ鏉′欢
+ // 浼樺寲鐐�3: 鏋勫缓鏌ヨ鏉′欢
var query = Db.Queryable<MesInvItemStocks>()
.LeftJoin<MesItems>((stock, item) => stock.ItemId == item.Id)
.LeftJoin<MesDepots>((stock, item, depot) =>
@@ -79,17 +86,13 @@
distinctBarcodes.Contains(stock.StackCode)) &&
stock.Quantity > 0);
- // 3. 搴旂敤鎼滅储鏉′欢
var conditions = searchDto.Conditions;
if (conditions != null)
{
- //褰揷onditions.IqcStatus涓�1鏃舵煡璇㈢殑鍊煎氨瑕佹槸鐗归噰鐩存帴浣跨敤锛屽凡妫�锛屽厤妫�锛�1鐨勫��
- // 绮剧‘鍖归厤鏉′欢
if (!string.IsNullOrEmpty(conditions.IqcStatus))
{
if (conditions.IqcStatus == "1")
{
- // 褰揑qcStatus涓�"1"鏃讹紝鏌ヨ鐗归噰鐩存帴浣跨敤銆佸凡妫�銆佸厤妫�鐘舵��
query = query.Where((stock, item, depot, org, unit) =>
stock.IqcStatus == "鐗归噰鐩存帴浣跨敤" ||
stock.IqcStatus == "宸叉" ||
@@ -97,7 +100,6 @@
}
else
{
- // 鍏朵粬鎯呭喌鎸夊師鍊煎尮閰�
query = query.Where((stock, item, depot, org, unit) =>
stock.IqcStatus == conditions.IqcStatus);
}
@@ -108,8 +110,6 @@
query = query.Where((stock, item, depot, org, unit) =>
stock.Quantity == conditions.Quantity.Value);
}
-
- // 妯＄硦鍖归厤鏉′欢
if (!string.IsNullOrEmpty(conditions.DepotName))
{
@@ -177,7 +177,6 @@
stock.StackCode.Contains(conditions.ItemBarcode)));
}
- // 鏃ユ湡鑼冨洿鏉′欢
if (!string.IsNullOrEmpty(conditions.IndepDateStart))
{
if (DateTime.TryParse(conditions.IndepDateStart,
@@ -198,7 +197,7 @@
}
}
- // 4. 鏌ヨ鎵�鏈夌鍚堟潯浠剁殑鏁版嵁锛堜笉鍒嗛〉锛�
+ // 浼樺寲鐐�4: 鏌ヨ鏁版嵁
var queryResult = query
.OrderByDescending((stock, item, depot, org, unit) =>
stock.IndepDate)
@@ -222,19 +221,16 @@
})
.ToList();
- // 5. 鍦ㄥ唴瀛樹腑杞崲涓篋TO锛屽叧鑱擯alletCode骞惰祴鍊糏temBarcode锛堜紭鍏堜娇鐢ㄧ鏉＄爜StockStackCode锛�
+ // 浼樺寲鐐�5: 浣跨敤瀛楀吀鏌ユ壘鏇夸唬Where().FirstOrDefault()
var tempDataList = queryResult.Select(x =>
{
- // 浼樺厛浣跨敤StockStackCode锛屽惁鍒欎娇鐢↖temBarcode鍘绘煡鎵綪alletCode
var barcodeToMatch = !string.IsNullOrEmpty(x.StockStackCode)
? x.StockStackCode
: x.ItemBarcode;
- var rackingTask = rackingTaskData
- .Where(r => r.ItemBarcode == barcodeToMatch)
- .FirstOrDefault();
+
+ rackingTaskDict.TryGetValue(barcodeToMatch, out var rackingTask);
- // 鏍规嵁Code鍊肩‘瀹氬簱瀛樼姸鎬�: null涓虹珛搴撳叆搴撲腑(0), 200涓哄凡鍦ㄧ珛搴撳唴(1)
- string stockStatus = "杩涘叆绔嬪簱鐨勮矾涓�"; // 榛樿涓虹珛搴撳叆搴撲腑
+ string stockStatus = "杩涘叆绔嬪簱鐨勮矾涓�";
if (rackingTask?.Code != null)
{
stockStatus = rackingTask.Code == "200" ? "宸插湪绔嬪簱涓�" : "杩涘叆绔嬪簱鐨勮矾涓�";
@@ -244,7 +240,7 @@
{
IqcStatus = x.IqcStatus == "宸叉" ? "1" : "0",
ItemType = x.DepotName == "鍘熸潗鏂欎粨" ? "0" : "1",
- StackCode = rackingTask?.PalletCode, // 浣跨敤PalletCode浣滀负姣嶆墭鐩樼紪鍙�
+ StackCode = rackingTask?.PalletCode,
x.DepotCode,
x.DepotName,
x.DepotSectionsCode,
@@ -258,11 +254,10 @@
x.OrgCode,
x.OrgName,
ItemBarcode = barcodeToMatch,
- StockStatus = stockStatus // 娣诲姞搴撳瓨鐘舵��
+ StockStatus = stockStatus
};
}).ToList();
- // 5.1 搴旂敤StackCode鎼滅储鏉′欢锛堝湪鍐呭瓨涓繃婊alletCode锛�
if (conditions != null && !string.IsNullOrEmpty(conditions.StackCode))
{
tempDataList = tempDataList
@@ -272,7 +267,6 @@
.ToList();
}
- // 5.2 搴旂敤StockStatus鎼滅储鏉′欢锛堝湪鍐呭瓨涓繃婊わ級
if (conditions != null && !string.IsNullOrEmpty(conditions.StockStatus))
{
tempDataList = tempDataList
@@ -280,7 +274,6 @@
.ToList();
}
- // 5.3 鍒嗙粍骞跺悎璁uantity
var dataList = tempDataList
.GroupBy(x => new
{
@@ -298,7 +291,7 @@
x.OrgCode,
x.OrgName,
x.ItemBarcode,
- x.StockStatus // 娣诲姞搴撳瓨鐘舵�佸埌鍒嗙粍閿腑
+ x.StockStatus
})
.Select(g => new ReturnableStockDto
{
@@ -318,27 +311,23 @@
OrgCode = g.Key.OrgCode,
OrgName = g.Key.OrgName,
ItemBarcode = g.Key.ItemBarcode,
- StockStatus = g.Key.StockStatus // 娣诲姞搴撳瓨鐘舵�佸埌DTO涓�
+ StockStatus = g.Key.StockStatus
}).ToList();
- // 6. 搴旂敤ItemType绛涢��(鍦ㄥ唴瀛樹腑杩囨护)
if (conditions?.ItemType != null)
{
dataList = dataList.Where(x => x.ItemType == conditions.ItemType)
.ToList();
}
- // 7. 璁＄畻鎬昏褰曟暟鍜屽垎椤靛弬鏁帮紙鍩轰簬鏈�缁堢粨鏋滐級
var totalRecords = dataList.Count;
var totalPages =
(int)Math.Ceiling((double)totalRecords / searchDto.PageSize);
var skip = (searchDto.PageIndex - 1) * searchDto.PageSize;
- // 8. 瀵规渶缁堢粨鏋滆繘琛屽垎椤�
var pagedDataList =
dataList.Skip(skip).Take(searchDto.PageSize).ToList();
- // 9. 杩斿洖鍒嗛〉缁撴灉
return new PagedResult<ReturnableStockDto>
{
TbBillList = pagedDataList,
@@ -358,30 +347,30 @@
/// <returns>鍙��璐х墿鏂欏簱瀛樺垪琛�</returns>
public List<ReturnableStockDto> GetReturnableStocks()
{
- // 1. 鏌ヨXB_RACKING_TASK_SYXT_LOG涓墍鏈夌殑鏉＄爜骞跺幓閲�
- var distinctBarcodes = Db.Queryable<XbRackingTaskSyxtLog>()
- .Where(x => !string.IsNullOrEmpty(x.ItemBarcode))
- .Select(x => x.ItemBarcode)
- .Distinct()
- .ToList();
+ // 浼樺寲鐐�1: 浣跨敤鍘熺敓SQL鍦ㄦ暟鎹簱绔畬鎴愬幓閲嶅拰鎺掑簭锛岄伩鍏嶅叏琛ㄥ姞杞藉埌鍐呭瓨
+ // Oracle 11g浣跨敤ROW_NUMBER()绐楀彛鍑芥暟鑾峰彇姣忎釜鏉＄爜鐨勬渶鏂拌褰�
+ var rackingTaskSql = @"
+ SELECT ITEM_BARCODE AS ItemBarcode, PALLETCODE AS PalletCode, CODE AS Code
+ FROM (
+ SELECT ITEM_BARCODE, PALLETCODE, CODE,
+ ROW_NUMBER() OVER (PARTITION BY ITEM_BARCODE ORDER BY ID DESC) AS RN
+ FROM XB_RACKING_TASK_SYXT_LOG
+ WHERE ITEM_BARCODE IS NOT NULL AND (CODE IS NULL OR CODE != '500')
+ ) WHERE RN = 1";
- if (distinctBarcodes == null || !distinctBarcodes.Any())
+ var rackingTaskData = Db.Ado.SqlQuery<RackingTaskDto>(rackingTaskSql);
+
+ if (rackingTaskData == null || !rackingTaskData.Any())
{
return new List<ReturnableStockDto>();
}
- // 1.1 鏌ヨXB_RACKING_TASK_SYXT_LOG涓璉temBarcode鍜孭alletCode銆丆ode鐨勬槧灏勫叧绯�
- var allRackingTaskData = Db.Queryable<XbRackingTaskSyxtLog>()
- .Where(x => !string.IsNullOrEmpty(x.ItemBarcode))
- .Select(x => new { x.ItemBarcode, x.PalletCode, x.Code, x.Id })
- .ToList();
+ // 浼樺寲鐐�2: 杩囨护null鍊煎苟浣跨敤瀛楀吀鎻愰珮鏉＄爜鍖归厤鏁堢巼锛屼粠O(n)闄嶄綆鍒癘(1)
+ var validRackingData = rackingTaskData.Where(x => !string.IsNullOrEmpty(x.ItemBarcode)).ToList();
+ var rackingTaskDict = validRackingData.ToDictionary(x => x.ItemBarcode);
+ var distinctBarcodes = validRackingData.Select(x => x.ItemBarcode).ToList();
- var rackingTaskData = allRackingTaskData
- .GroupBy(x => x.ItemBarcode)
- .Select(g => g.OrderByDescending(x => x.Id).First())
- .ToList();
-
- // 2. 浣跨敤鏉＄爜鏌ヨMES_INV_ITEM_STOCKS涓殑鏁版嵁,鍏宠仈MES_ITEMS銆丮ES_DEPOTS銆丱RGANIZE銆丮ES_UNIT琛�
+ // 浼樺寲鐐�3: 鍦ㄦ暟鎹簱灞傞潰瀹屾垚鍏宠仈鏌ヨ锛屼竴娆℃�ц幏鍙栨墍鏈夐渶瑕佺殑鏁版嵁
var queryResult = Db.Queryable<MesInvItemStocks>()
.LeftJoin<MesItems>((stock, item) => stock.ItemId == item.Id)
.LeftJoin<MesDepots>((stock, item, depot) =>
@@ -413,22 +402,15 @@
})
.ToList();
- // 3. 鍦ㄥ唴瀛樹腑杩涜鏁版嵁杞崲
+ // 浼樺寲鐐�4: 浣跨敤瀛楀吀鏌ユ壘鏇夸唬Where().FirstOrDefault()锛屾彁楂樺尮閰嶆�ц兘
var result = queryResult.Select(x =>
{
- // 鏍规嵁鏉＄爜鏌ユ壘瀵瑰簲鐨勭珛搴撲换鍔′俊鎭�
- var rackingTask = rackingTaskData
- .Where(r => r.ItemBarcode == x.ItemBarcode)
- .FirstOrDefault();
+ rackingTaskDict.TryGetValue(x.ItemBarcode, out var rackingTask);
- // 鏍规嵁Code鍊肩‘瀹氬簱瀛樼姸鎬�: null涓虹珛搴撳叆搴撲腑(0), 200涓哄凡鍦ㄧ珛搴撳唴(1)
- string stockStatus = "0"; // 榛樿涓虹珛搴撳叆搴撲腑
+ string stockStatus = "0";
if (rackingTask?.Code != null)
{
- stockStatus =
- rackingTask.Code == "200"
- ? "1"
- : "2"; // 200瀵瑰簲宸插湪绔嬪簱涓�(1)锛屽叾浠栭潪500鍊间负杩涘叆绔嬪簱鐨勮矾涓�(2)
+ stockStatus = rackingTask.Code == "200" ? "1" : "2";
}
return new ReturnableStockDto
@@ -449,13 +431,20 @@
OrgCode = x.OrgCode,
OrgName = x.OrgName,
ItemBarcode = x.ItemBarcode,
- StockStatus = stockStatus // 娣诲姞搴撳瓨鐘舵��
+ StockStatus = stockStatus
};
}).ToList();
return result;
}
+ private class RackingTaskDto
+ {
+ public string ItemBarcode { get; set; }
+ public string PalletCode { get; set; }
+ public string Code { get; set; }
+ }
+
/// <summary>
/// 鏍规嵁鐢ㄦ埛缂栫爜鍜屽瓧绗︿覆鐢熸垚閫�鏂欒姹傚崟
/// </summary>
--
Gitblit v1.9.3