From fbd1dae61bb8eeccd303341ff3b5290d805a8155 Mon Sep 17 00:00:00 2001
From: tjx <t2856754968@163.com>
Date: 星期五, 19 十二月 2025 18:40:25 +0800
Subject: [PATCH] 111
---
StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs | 590 ++++++++++++++++++++++++++++++++--------------------------
1 files changed, 321 insertions(+), 269 deletions(-)
diff --git a/StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs b/StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs
index 634aaab..ab4db63 100644
--- a/StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs
+++ b/StandardPda/MES.Service/service/Warehouse/MesInvItemStocksManager.cs
@@ -4,6 +4,8 @@
using SqlSugar;
using Newtonsoft.Json;
using System.Text;
+using OfficeOpenXml;
+using OfficeOpenXml.Style;
namespace MES.Service.service.Warehouse;
@@ -17,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, TASKTYPE AS TaskType
+ FROM (
+ SELECT ITEM_BARCODE, PALLETCODE, CODE, TASKTYPE,
+ 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. 浼樺寲锛氫娇鐢ㄥ師鐢烻QL鑾峰彇姣忎釜鏉＄爜鐨勬渶鏂拌褰曪紙閬垮厤鍏ㄨ〃鍔犺浇鍒板唴瀛橈級
- // 浣跨敤绐楀彛鍑芥暟ROW_NUMBER鍦ㄦ暟鎹簱灞傞潰瀹屾垚鍒嗙粍鍙栨渶鏂拌褰�
- var sql = @"
- SELECT ITEM_BARCODE, PalletCode, Code, Id
- FROM (SELECT ITEM_BARCODE,
- PalletCode,
- Code,
- Id,
- 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 != '500') t
- WHERE rn = 1";
-
- var rackingTaskData = Db.Ado.SqlQuery<RackingTaskInfo>(sql);
+ var rackingTaskData = Db.Ado.SqlQuery<RackingTaskDto>(rackingTaskSql);
if (rackingTaskData == null || !rackingTaskData.Any())
{
@@ -59,210 +51,202 @@
};
}
- // 1.1 鎻愬彇鍘婚噸鍚庣殑鏉＄爜鍒楄〃鐢ㄤ簬鏌ヨ
- var distinctBarcodes = rackingTaskData.Select(x => x.ItemBarcode)
- .Distinct().ToList();
-
- // 1.2 鎻愬彇鎼滅储鏉′欢锛堟彁鍗囦綔鐢ㄥ煙锛�
- var conditions = searchDto.Conditions;
-
- // 2. 浼樺寲锛氬垎鎵瑰鐞嗘潯鐮佸垪琛紙閬垮厤IN鏌ヨ杩囧ぇ锛�
- const int batchSize = 500; // 姣忔壒澶勭悊500鏉�
- var allQueryResults = new List<StockQueryResult>();
+ // 浼樺寲鐐�2: 杩囨护null鍊煎苟浣跨敤瀛楀吀鎻愰珮鏌ユ壘鏁堢巼
+ var validRackingData = rackingTaskData.Where(x => !string.IsNullOrEmpty(x.ItemBarcode)).ToList();
- for (int i = 0; i < distinctBarcodes.Count; i += batchSize)
+ if (!validRackingData.Any())
{
- var batchBarcodes = distinctBarcodes.Skip(i).Take(batchSize).ToList();
-
- // 2.1 鏋勫缓鏌ヨ鏉′欢
- var query = Db.Queryable<MesInvItemStocks>()
- .LeftJoin<MesItems>((stock, item) => stock.ItemId == item.Id)
- .LeftJoin<MesDepots>((stock, item, depot) =>
- stock.DepotsCode == depot.DepotCode)
- .LeftJoin<Organize>((stock, item, depot, org) =>
- item.UseOrg == org.Id.ToString())
- .LeftJoin<MesUnit>((stock, item, depot, org, unit) =>
- item.ItemUnit == unit.Id.ToString())
- .Where((stock, item, depot, org, unit) =>
- (batchBarcodes.Contains(stock.ItemBarcode) ||
- batchBarcodes.Contains(stock.StackCode)) &&
- stock.Quantity > 0);
-
- // 2.2 搴旂敤鎼滅储鏉′欢锛堝湪鏁版嵁搴撳眰闈㈣繃婊わ級
- if (conditions != null)
+ return new PagedResult<ReturnableStockDto>
{
- //褰揷onditions.IqcStatus涓�1鏃舵煡璇㈢殑鍊煎氨瑕佹槸鐗归噰鐩存帴浣跨敤锛屽凡妫�锛屽厤妫�锛�1鐨勫��
- // 绮剧‘鍖归厤鏉′欢
- if (!string.IsNullOrEmpty(conditions.IqcStatus))
+ TbBillList = new List<ReturnableStockDto>(),
+ Pagination = new PaginationInfo
{
- if (conditions.IqcStatus == "1")
- {
- // 褰揑qcStatus涓�"1"鏃讹紝鏌ヨ鐗归噰鐩存帴浣跨敤銆佸凡妫�銆佸厤妫�鐘舵��
- query = query.Where((stock, item, depot, org, unit) =>
- stock.IqcStatus == "鐗归噰鐩存帴浣跨敤" ||
- stock.IqcStatus == "宸叉" ||
- stock.IqcStatus == "鍏嶆");
- }
- else
- {
- // 鍏朵粬鎯呭喌鎸夊師鍊煎尮閰�
- query = query.Where((stock, item, depot, org, unit) =>
- stock.IqcStatus == conditions.IqcStatus);
- }
+ 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();
- if (conditions.Quantity.HasValue)
+ // 浼樺寲鐐�3: 鏋勫缓鏌ヨ鏉′欢
+ var query = Db.Queryable<MesInvItemStocks>()
+ .LeftJoin<MesItems>((stock, item) => stock.ItemId == item.Id)
+ .LeftJoin<MesDepots>((stock, item, depot) =>
+ stock.DepotsCode == depot.DepotCode)
+ .LeftJoin<Organize>((stock, item, depot, org) =>
+ item.UseOrg == org.Id.ToString())
+ .LeftJoin<MesUnit>((stock, item, depot, org, unit) =>
+ item.ItemUnit == unit.Id.ToString())
+ .Where((stock, item, depot, org, unit) =>
+ (distinctBarcodes.Contains(stock.ItemBarcode) ||
+ distinctBarcodes.Contains(stock.StackCode)) &&
+ stock.Quantity > 0);
+
+ var conditions = searchDto.Conditions;
+ if (conditions != null)
+ {
+ if (!string.IsNullOrEmpty(conditions.IqcStatus))
+ {
+ if (conditions.IqcStatus == "1")
{
query = query.Where((stock, item, depot, org, unit) =>
- stock.Quantity == conditions.Quantity.Value);
+ stock.IqcStatus == "鐗归噰鐩存帴浣跨敤" ||
+ stock.IqcStatus == "宸叉" ||
+ stock.IqcStatus == "鍏嶆");
}
-
- // 妯＄硦鍖归厤鏉′欢
-
- if (!string.IsNullOrEmpty(conditions.DepotName))
+ else
{
query = query.Where((stock, item, depot, org, unit) =>
- depot.DepotName != null &&
- depot.DepotName.Contains(conditions.DepotName));
- }
-
- if (!string.IsNullOrEmpty(conditions.DepotSectionsCode))
- {
- query = query.Where((stock, item, depot, org, unit) =>
- stock.DepotSectionsCode != null &&
- stock.DepotSectionsCode.Contains(conditions
- .DepotSectionsCode));
- }
-
- if (!string.IsNullOrEmpty(conditions.ItemNo))
- {
- query = query.Where((stock, item, depot, org, unit) =>
- item.ItemNo != null &&
- item.ItemNo.Contains(conditions.ItemNo));
- }
-
- if (!string.IsNullOrEmpty(conditions.ItemName))
- {
- query = query.Where((stock, item, depot, org, unit) =>
- item.ItemName != null &&
- item.ItemName.Contains(conditions.ItemName));
- }
-
- if (!string.IsNullOrEmpty(conditions.ItemModel))
- {
- query = query.Where((stock, item, depot, org, unit) =>
- item.ItemModel != null &&
- item.ItemModel.Contains(conditions.ItemModel));
- }
-
- if (!string.IsNullOrEmpty(conditions.ItemUnitName))
- {
- query = query.Where((stock, item, depot, org, unit) =>
- unit.Fname != null &&
- unit.Fname.Contains(conditions.ItemUnitName));
- }
-
- if (!string.IsNullOrEmpty(conditions.OrgCode))
- {
- query = query.Where((stock, item, depot, org, unit) =>
- org.Fnumber != null &&
- org.Fnumber.Contains(conditions.OrgCode));
- }
-
- if (!string.IsNullOrEmpty(conditions.OrgName))
- {
- query = query.Where((stock, item, depot, org, unit) =>
- org.Fname != null &&
- org.Fname.Contains(conditions.OrgName));
- }
-
- if (!string.IsNullOrEmpty(conditions.ItemBarcode))
- {
- query = query.Where((stock, item, depot, org, unit) =>
- (stock.ItemBarcode != null &&
- stock.ItemBarcode.Contains(conditions.ItemBarcode)) ||
- (stock.StackCode != null &&
- stock.StackCode.Contains(conditions.ItemBarcode)));
- }
-
- // 鏃ユ湡鑼冨洿鏉′欢
- if (!string.IsNullOrEmpty(conditions.IndepDateStart))
- {
- if (DateTime.TryParse(conditions.IndepDateStart,
- out var startDate))
- {
- query = query.Where((stock, item, depot, org, unit) =>
- stock.IndepDate >= startDate);
- }
- }
-
- if (!string.IsNullOrEmpty(conditions.IndepDateEnd))
- {
- if (DateTime.TryParse(conditions.IndepDateEnd, out var endDate))
- {
- query = query.Where((stock, item, depot, org, unit) =>
- stock.IndepDate <= endDate);
- }
+ stock.IqcStatus == conditions.IqcStatus);
}
}
- // 2.3 鎵ц褰撳墠鎵规鏌ヨ
- var batchQueryResult = query
- .OrderByDescending((stock, item, depot, org, unit) =>
- stock.IndepDate)
- .Select((stock, item, depot, org, unit) => new StockQueryResult
+ if (conditions.Quantity.HasValue)
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ stock.Quantity == conditions.Quantity.Value);
+ }
+
+ if (!string.IsNullOrEmpty(conditions.DepotName))
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ depot.DepotName != null &&
+ depot.DepotName.Contains(conditions.DepotName));
+ }
+
+ if (!string.IsNullOrEmpty(conditions.DepotSectionsCode))
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ stock.DepotSectionsCode != null &&
+ stock.DepotSectionsCode.Contains(conditions
+ .DepotSectionsCode));
+ }
+
+ if (!string.IsNullOrEmpty(conditions.ItemNo))
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ item.ItemNo != null &&
+ item.ItemNo.Contains(conditions.ItemNo));
+ }
+
+ if (!string.IsNullOrEmpty(conditions.ItemName))
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ item.ItemName != null &&
+ item.ItemName.Contains(conditions.ItemName));
+ }
+
+ if (!string.IsNullOrEmpty(conditions.ItemModel))
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ item.ItemModel != null &&
+ item.ItemModel.Contains(conditions.ItemModel));
+ }
+
+ if (!string.IsNullOrEmpty(conditions.ItemUnitName))
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ unit.Fname != null &&
+ unit.Fname.Contains(conditions.ItemUnitName));
+ }
+
+ if (!string.IsNullOrEmpty(conditions.OrgCode))
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ org.Fnumber != null &&
+ org.Fnumber.Contains(conditions.OrgCode));
+ }
+
+ if (!string.IsNullOrEmpty(conditions.OrgName))
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ org.Fname != null &&
+ org.Fname.Contains(conditions.OrgName));
+ }
+
+ if (!string.IsNullOrEmpty(conditions.ItemBarcode))
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ (stock.ItemBarcode != null &&
+ stock.ItemBarcode.Contains(conditions.ItemBarcode)) ||
+ (stock.StackCode != null &&
+ stock.StackCode.Contains(conditions.ItemBarcode)));
+ }
+
+ if (!string.IsNullOrEmpty(conditions.IndepDateStart))
+ {
+ if (DateTime.TryParse(conditions.IndepDateStart,
+ out var startDate))
{
- IqcStatus = stock.IqcStatus,
- DepotCode = stock.DepotsCode,
- DepotName = depot.DepotName,
- DepotSectionsCode = stock.DepotSectionsCode,
- ItemNo = item.ItemNo,
- ItemName = item.ItemName,
- ItemModel = item.ItemModel,
- Quantity = stock.Quantity,
- ItemUnit = item.ItemUnit,
- ItemUnitName = unit.Fname,
- IndepDate = stock.IndepDate,
- OrgCode = org.Fnumber,
- OrgName = org.Fname,
- ItemBarcode = stock.ItemBarcode,
- StockStackCode = stock.StackCode
- })
- .ToList();
-
- allQueryResults.AddRange(batchQueryResult);
+ query = query.Where((stock, item, depot, org, unit) =>
+ stock.IndepDate >= startDate);
+ }
+ }
+
+ if (!string.IsNullOrEmpty(conditions.IndepDateEnd))
+ {
+ if (DateTime.TryParse(conditions.IndepDateEnd, out var endDate))
+ {
+ query = query.Where((stock, item, depot, org, unit) =>
+ stock.IndepDate <= endDate);
+ }
+ }
}
-
- // 3. 鍚堝苟鎵�鏈夋壒娆＄殑鏌ヨ缁撴灉
- var queryResult = allQueryResults;
- // 4. 浼樺寲锛氭瀯寤烘潯鐮佹槧灏勫瓧鍏革紙鎻愰珮鏌ユ壘鏁堢巼锛�
- var rackingTaskDict = rackingTaskData
- .ToDictionary(x => x.ItemBarcode, x => x);
+ // 浼樺寲鐐�4: 鏌ヨ鏁版嵁
+ var queryResult = query
+ .OrderByDescending((stock, item, depot, org, unit) =>
+ stock.IndepDate)
+ .Select((stock, item, depot, org, unit) => new
+ {
+ stock.IqcStatus,
+ DepotCode = stock.DepotsCode,
+ depot.DepotName,
+ stock.DepotSectionsCode,
+ item.ItemNo,
+ item.ItemName,
+ item.ItemModel,
+ stock.Quantity,
+ item.ItemUnit,
+ ItemUnitName = unit.Fname,
+ stock.IndepDate,
+ OrgCode = org.Fnumber,
+ OrgName = org.Fname,
+ stock.ItemBarcode,
+ StockStackCode = stock.StackCode
+ })
+ .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;
- // 浣跨敤瀛楀吀鏌ユ壘锛屾�ц兘鏇翠紭
rackingTaskDict.TryGetValue(barcodeToMatch, out var rackingTask);
- // 鏍规嵁Code鍊肩‘瀹氬簱瀛樼姸鎬�: null涓虹珛搴撳叆搴撲腑(0), 200涓哄凡鍦ㄧ珛搴撳唴(1)
- string? stockStatus = "杩涘叆绔嬪簱鐨勮矾涓�"; // 榛樿涓虹珛搴撳叆搴撲腑
- if (rackingTask?.Code != null)
+ string stockStatus;
+ if (rackingTask?.TaskType == "绔嬪簱鍑哄簱璇锋眰")
{
- stockStatus = rackingTask.Code == "200" ? "宸插湪绔嬪簱涓�" : "杩涘叆绔嬪簱鐨勮矾涓�";
+ // 鍑哄簱璇锋眰锛欳ode=200琛ㄧず宸插嚭搴擄紝Code=null琛ㄧず鍑哄簱璺笂
+ stockStatus = rackingTask.Code == "200" ? "宸插嚭绔嬪簱" : "鍑虹珛搴撹矾涓�";
+ }
+ else
+ {
+ // 鍥炲簱璇锋眰锛堥粯璁わ級锛欳ode=200琛ㄧず宸插湪绔嬪簱涓紝Code=null琛ㄧず杩涘叆绔嬪簱鐨勮矾涓�
+ stockStatus = rackingTask?.Code == "200" ? "宸插湪绔嬪簱涓�" : "杩涘叆绔嬪簱鐨勮矾涓�";
}
return new
{
IqcStatus = x.IqcStatus == "宸叉" ? "1" : "0",
ItemType = x.DepotName == "鍘熸潗鏂欎粨" ? "0" : "1",
- StackCode = rackingTask?.PalletCode, // 浣跨敤PalletCode浣滀负姣嶆墭鐩樼紪鍙�
+ StackCode = rackingTask?.PalletCode,
x.DepotCode,
x.DepotName,
x.DepotSectionsCode,
@@ -276,11 +260,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
@@ -290,7 +273,6 @@
.ToList();
}
- // 5.2 搴旂敤StockStatus鎼滅储鏉′欢锛堝湪鍐呭瓨涓繃婊わ級
if (conditions != null && !string.IsNullOrEmpty(conditions.StockStatus))
{
tempDataList = tempDataList
@@ -298,7 +280,6 @@
.ToList();
}
- // 5.3 鍒嗙粍骞跺悎璁uantity
var dataList = tempDataList
.GroupBy(x => new
{
@@ -316,7 +297,7 @@
x.OrgCode,
x.OrgName,
x.ItemBarcode,
- x.StockStatus // 娣诲姞搴撳瓨鐘舵�佸埌鍒嗙粍閿腑
+ x.StockStatus
})
.Select(g => new ReturnableStockDto
{
@@ -336,27 +317,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,
@@ -371,68 +348,35 @@
}
/// <summary>
- /// 杈呭姪绫伙細绔嬪簱浠诲姟淇℃伅
- /// </summary>
- private class RackingTaskInfo
- {
- public string? ItemBarcode { get; set; }
- public string? PalletCode { get; set; }
- public string? Code { get; set; }
- public decimal Id { get; set; }
- }
-
- /// <summary>
- /// 杈呭姪绫伙細搴撳瓨鏌ヨ涓棿缁撴灉
- /// </summary>
- private class StockQueryResult
- {
- public string? IqcStatus { get; set; }
- public string? DepotCode { get; set; }
- public string? DepotName { get; set; }
- public string? DepotSectionsCode { get; set; }
- public string? ItemNo { get; set; }
- public string? ItemName { get; set; }
- public string? ItemModel { get; set; }
- public decimal? Quantity { get; set; }
- public string? ItemUnit { get; set; }
- public string? ItemUnitName { get; set; }
- public DateTime? IndepDate { get; set; }
- public string? OrgCode { get; set; }
- public string? OrgName { get; set; }
- public string? ItemBarcode { get; set; }
- public string? StockStackCode { get; set; }
- }
-
- /// <summary>
/// 鏌ヨ鍙互閫�璐х殑鐗╂枡(鏃х増鏈�,淇濈暀鍏煎)
/// </summary>
/// <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) =>
@@ -464,22 +408,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
@@ -500,11 +437,19 @@
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; }
+ public string TaskType { get; set; }
}
/// <summary>
@@ -542,7 +487,7 @@
}
decimal messageId = 0;
- string? taskCode = "";
+ string taskCode = "";
// 鏍规嵁鏉＄爜鏌ヨXB_RACKING_TASK_SYXT_LOG琛紝鏌ヨmax(PALLETCODE)鍜屽搴旂殑widthType
var rackingTaskInfo = Db.Queryable<XbRackingTaskSyxtLog>()
@@ -550,6 +495,20 @@
.OrderByDescending(x => x.Id)
.Select(x => new { x.PalletCode, x.WidthType })
.First();
+
+ // 妫�鏌ユ槸鍚﹀瓨鍦ㄦ湭瀹屾垚鐨勪换鍔★紙鍩轰簬PalletCode锛孋ode涓簄ull锛�
+ if (!string.IsNullOrEmpty(rackingTaskInfo?.PalletCode))
+ {
+ var pendingTask = Db.Queryable<XbRackingTaskSyxtLog>()
+ .Where(x => x.PalletCode == rackingTaskInfo.PalletCode && x.Code == null)
+ .OrderByDescending(x => x.Id)
+ .First();
+
+ if (pendingTask != null)
+ {
+ throw new Exception($"鎵樼洏缂栧彿 {rackingTaskInfo.PalletCode} 宸插瓨鍦ㄦ湭瀹屾垚鐨勭珛搴撲换鍔★紝璇风瓑寰呬换鍔″畬鎴愬悗鍐嶆搷浣溿��");
+ }
+ }
try
{
@@ -647,8 +606,8 @@
throw new Exception($"瑙ｆ瀽鍝嶅簲澶辫触: {responseStr}");
}
- var code = responseJson?.Code?.ToString()();
- var jsonMessage = responseJson?.JsonMessage?.ToString()();
+ var code = responseJson?.Code?.ToString();
+ var jsonMessage = responseJson?.JsonMessage?.ToString();
// 7. 鏍规嵁Code鍒ゆ柇鎴愬姛鎴栧け璐�
if (code == "200")
@@ -688,7 +647,7 @@
? "浠诲姟璇锋眰澶辫触"
: jsonMessage;
var failureMessage =
- (string?)("绔嬪簱浠诲姟涓嬪彂澶辫触: " + errorMessage);
+ (string)("绔嬪簱浠诲姟涓嬪彂澶辫触: " + errorMessage);
Db.Updateable<MessageCenter>()
.SetColumns(it => it.Result == 0)
@@ -707,7 +666,7 @@
if (messageId > 0)
{
var exceptionMessage =
- (string?)("绔嬪簱浠诲姟寮傚父: " + ex.Message);
+ (string)("绔嬪簱浠诲姟寮傚父: " + ex.Message);
Db.Updateable<MessageCenter>()
.SetColumns(it => it.Result == 0)
.SetColumns(it =>
@@ -731,4 +690,97 @@
throw;
}
}
+
+ /// <summary>
+ /// 瀵煎嚭鍙��璐х墿鏂欏簱瀛樹负Excel
+ /// </summary>
+ /// <param name="searchDto">鎼滅储璇锋眰鍙傛暟</param>
+ /// <returns>Excel鏂囦欢瀛楄妭鏁扮粍</returns>
+ public byte[] ExportReturnableStocksToExcel(
+ ReturnableStockSearchDto searchDto)
+ {
+ // 璁剧疆 EPPlus 璁稿彲璇佷笂涓嬫枃(闈炲晢涓氱敤閫�)
+ ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
+
+ // 鑾峰彇鎵�鏈夋暟鎹�(涓嶅垎椤�)
+ var tempSearchDto = new ReturnableStockSearchDto
+ {
+ PageIndex = 1,
+ PageSize = int.MaxValue, // 鑾峰彇鎵�鏈夋暟鎹�
+ Conditions = searchDto.Conditions
+ };
+
+ // 璋冪敤鏌ヨ鏂规硶鑾峰彇鏁版嵁
+ var result = GetReturnableStocks(tempSearchDto);
+ var dataList = result.TbBillList;
+
+ // 鍒涘缓 Excel 鏂囦欢
+ using var package = new ExcelPackage();
+ var worksheet = package.Workbook.Worksheets.Add("鍙��璐х墿鏂欏簱瀛�");
+
+ // 璁剧疆琛ㄥご
+ var headers = new[]
+ {
+ "鑹搧鐘舵��",
+ "鏄惁鎴愬搧",
+ "姣嶆墭鐩樼紪鍙�",
+ "浠撳簱缂栫爜",
+ "浠撳簱鍚嶇О",
+ "铏氭嫙搴撲綅",
+ "鐗╂枡缂栧彿",
+ "鐗╂枡鍚嶇О",
+ "鐗╂枡瑙勬牸",
+ "鐗╂枡鏁伴噺",
+ "鐗╂枡鍗曚綅",
+ "鍏ュ簱鏃堕棿",
+ "缁勭粐缂栫爜",
+ "缁勭粐鍚嶇О",
+ "鐗╂枡鏉＄爜",
+ "搴撳瓨鐘舵��"
+ };
+
+ // 鍐欏叆琛ㄥご
+ for (int i = 0; i < headers.Length; i++)
+ {
+ worksheet.Cells[1, i + 1].Value = headers[i];
+ worksheet.Cells[1, i + 1].Style.Font.Bold = true;
+ worksheet.Cells[1, i + 1].Style.Fill.PatternType =
+ ExcelFillStyle.Solid;
+ worksheet.Cells[1, i + 1].Style.Fill.BackgroundColor
+ .SetColor(System.Drawing.Color.LightGray);
+ worksheet.Cells[1, i + 1].Style.HorizontalAlignment =
+ ExcelHorizontalAlignment.Center;
+ }
+
+ // 鍐欏叆鏁版嵁
+ int row = 2;
+ foreach (var item in dataList)
+ {
+ worksheet.Cells[row, 1].Value =
+ item.IqcStatus == "1" ? "鑹搧" : "涓嶈壇鍝�";
+ worksheet.Cells[row, 2].Value = item.ItemType == "1" ? "鎴愬搧" : "闈炴垚鍝�";
+ worksheet.Cells[row, 3].Value = item.StackCode;
+ worksheet.Cells[row, 4].Value = item.DepotCode;
+ worksheet.Cells[row, 5].Value = item.DepotName;
+ worksheet.Cells[row, 6].Value = item.DepotSectionsCode;
+ worksheet.Cells[row, 7].Value = item.ItemNo;
+ worksheet.Cells[row, 8].Value = item.ItemName;
+ worksheet.Cells[row, 9].Value = item.ItemModel;
+ worksheet.Cells[row, 10].Value = item.Quantity;
+ worksheet.Cells[row, 11].Value = item.ItemUnitName;
+ worksheet.Cells[row, 12].Value =
+ item.IndepDate?.ToString("yyyy-MM-dd HH:mm:ss");
+ worksheet.Cells[row, 13].Value = item.OrgCode;
+ worksheet.Cells[row, 14].Value = item.OrgName;
+ worksheet.Cells[row, 15].Value = item.ItemBarcode;
+ worksheet.Cells[row, 16].Value = item.StockStatus;
+ row++;
+ }
+
+ // 鑷姩璋冩暣鍒楀
+ worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
+
+ // 杩斿洖 Excel 鏂囦欢瀛楄妭鏁扮粍
+ return package.GetAsByteArray();
+ }
}
\ No newline at end of file
--
Gitblit v1.9.3