From b28e8fbf2a78cc9ae5047d2fbe1820fe42850151 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期一, 26 八月 2024 10:24:49 +0800
Subject: [PATCH] 1.模具导入接口数据校验优化
---
VueWebCoreApi/Controllers/ImportExcelController.cs | 3
VueWebCoreApi/Tools/ImportExcelData.cs | 189 ++++++++++++++++++++++++----------------------
VueWebCoreApi/appsettings.json | 3
3 files changed, 103 insertions(+), 92 deletions(-)
diff --git a/VueWebCoreApi/Controllers/ImportExcelController.cs b/VueWebCoreApi/Controllers/ImportExcelController.cs
index 1617421..02c0a1e 100644
--- a/VueWebCoreApi/Controllers/ImportExcelController.cs
+++ b/VueWebCoreApi/Controllers/ImportExcelController.cs
@@ -16,7 +16,7 @@
[ApiExplorerSettings(GroupName = "Excel瀵煎叆")]
[ApiController]
[Route("api/[controller]")]
- //[ChannelActionFilter]
+ [ChannelActionFilter]
public class ImportExcelController : Controller
{
private readonly IWebHostEnvironment _hostingEnvironment;
@@ -201,6 +201,7 @@
ToMessage mes = new ToMessage();
try
{
+ //var token = "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJ1c2VyY29kZSI6Ijk5OTkiLCJ1c2VybmFtZSI6Iuezu-e7n-euoeeQhuWRmCIsInN0b3JnX2NvZGUiOiIiLCJzdG9yZ19uYW1lIjoiIiwiaXNfc3lzdGVtX2FkbWluIjoiWSIsInJvbGVfY29kZSI6IiIsInJvbGVfZGF0YXBlcm1pc3Npb25zIjoiIiwidXNlcnR5cGUiOiJQQyIsInJlZGlza2V5IjoiU0ZNRVNMb2dpblVzZXJJRFBDOTk5OSIsInRpbWVvdXQiOiIyMDI0LTA4LTIzVDE2OjU5OjM1LjkzMDU5ODcrMDg6MDAifQ.XS4i_pJg4srt23pMoTJ8O0ZW8nq1dOkjQWOrkk6IfG4";
var token = HttpContext.Request.Headers["Token"].ToString();
User us = JwtTools.Denocode(token.ToString());
mes = ExcelCheckBLL.ExcelImportSubmit(FileCode, dataTable, us);
diff --git a/VueWebCoreApi/Tools/ImportExcelData.cs b/VueWebCoreApi/Tools/ImportExcelData.cs
index 5a7c185..c8ae6ec 100644
--- a/VueWebCoreApi/Tools/ImportExcelData.cs
+++ b/VueWebCoreApi/Tools/ImportExcelData.cs
@@ -3379,114 +3379,123 @@
List<ExcelErro> list = new List<ExcelErro>();
list = ImportExcel.InportExcelToTableListErro(excelTable); //楠岃瘉Excel鏁版嵁蹇呭～瀛楁鏄惁涓虹┖銆佸敮涓�瀛楁鏄惁閲嶅
- //妯″叿琛�
- for (int k = 0; k < excelTable[0].Rows.Count; k++)
+ //鏌ヨ妯″叿淇℃伅
+ sql = @"select code,name,spec from TMouldInfo";
+ dt = DapperHelper.selecttable(sql);
+ if (dt.Rows.Count > 0)
{
- if (excelTable[0].Rows[k][0].ToString().Trim() != null && excelTable[0].Rows[k][0].ToString().Trim() != "")
+ //鏍￠獙褰撳墠涓婁紶鐨勬ā鍏蜂俊鎭笌鏁版嵁琛ㄤ腑鐨勬ā鍏蜂俊鎭槸鍚﹂噸澶�
+ // 浣跨敤LINQ鍜孡ambda琛ㄨ揪寮忔潵鏌ユ壘鍦ㄦ暟鎹簱涓噸澶嶇殑璁板綍
+ var aRows = excelTable[0].AsEnumerable();
+ var duplicates = aRows.Where(aRow =>
+ dt.AsEnumerable().Any(bRow =>
+ aRow["*妯″叿缂栧彿(鍞竴)"].Equals(bRow["code"])||
+ aRow["*妯″叿鍚嶇О"].Equals(bRow["name"])
+ )
+ ).ToList();
+ if (duplicates.Count > 0)
{
- sql = @"select * from TMouldInfo where code=@code";
- dynamicParams.Add("@code", excelTable[0].Rows[k][0].ToString().Trim());
- dt = DapperHelper.selectdata(sql, dynamicParams);
- if (dt.Rows.Count > 0)
+ // 杈撳嚭鎴栧鐞嗘壘鍒扮殑閲嶅璁板綍
+ foreach (DataRow duplicateRow in duplicates)
{
ExcelErro erro = new ExcelErro();
erro.RoeNumber = "/";
- erro.ErrorField = "{*妯″叿缂栧彿(鍞竴)}";
- erro.ErrorCont = "妯″叿琛�:{*妯″叿缂栧彿(鍞竴)}瀛楁" + excelTable[0].Rows[k][0].ToString().Trim() + "宸插瓨鍦�";
- list.Add(erro);
- }
- }
- if (excelTable[0].Rows[k][1].ToString().Trim() != null && excelTable[0].Rows[k][1].ToString().Trim() != "")
- {
- sql = @"select * from TMouldInfo where name=@name";
- dynamicParams.Add("@name", excelTable[0].Rows[k][1].ToString().Trim());
- dt = DapperHelper.selectdata(sql, dynamicParams);
- if (dt.Rows.Count > 0)
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = "/";
- erro.ErrorField = "{*妯″叿鍚嶇О}";
- erro.ErrorCont = "妯″叿琛�:{*妯″叿鍚嶇О}瀛楁" + excelTable[0].Rows[k][1].ToString().Trim() + "宸插瓨鍦�";
- list.Add(erro);
- }
- }
- //鍒ゆ柇浠撳簱缂栫爜鏄惁涓虹┖
- if (excelTable[0].Rows[k][4].ToString().Trim() != null && excelTable[0].Rows[k][4].ToString().Trim() != "")
- {
- sql = @"select * from TSecStck where code=@code";
- dynamicParams.Add("@code", excelTable[0].Rows[k][4].ToString().Trim());
- dt = DapperHelper.selectdata(sql, dynamicParams);
- if (dt.Rows.Count <= 0)
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = "/";
- erro.ErrorField = "{*浠撳簱缂栫爜}";
- erro.ErrorCont = "妯″叿琛�:{*浠撳簱缂栫爜}瀛楁" + excelTable[0].Rows[k][4].ToString().Trim() + "涓嶅瓨鍦�";
- list.Add(erro);
- }
- }
- //鍒ゆ柇搴撲綅缂栫爜鏄惁涓虹┖
- if (excelTable[0].Rows[k][5].ToString().Trim() != null && excelTable[0].Rows[k][5].ToString().Trim() != "")
- {
- //搴撲綅涓嶄负绌烘椂,鍒ゆ柇浠撳簱鏄惁涓虹┖
- if (excelTable[0].Rows[k][4].ToString().Trim() != null && excelTable[0].Rows[k][4].ToString().Trim() != "")
- {
- //鍒ゆ柇搴撲綅鏄惁瀛樺湪
- sql = @"select * from TSecLoca where code=@code";
- dynamicParams.Add("@code", excelTable[0].Rows[k][5].ToString().Trim());
- dt = DapperHelper.selectdata(sql, dynamicParams);
- if (dt.Rows.Count <= 0)
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = "/";
- erro.ErrorField = "{*搴撲綅缂栫爜}";
- erro.ErrorCont = "妯″叿琛�:{*搴撲綅缂栫爜}瀛楁" + excelTable[0].Rows[k][5].ToString().Trim() + "涓嶅瓨鍦�";
- list.Add(erro);
- }
- else
- {
- //鍒ゆ柇搴撲綅鏄惁灞炰簬褰撳墠浠撳簱
- if (excelTable[0].Rows[k][4].ToString().Trim() != dt.Rows[0]["idwarehouse"].ToString())
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = "/";
- erro.ErrorField = "{*搴撲綅缂栫爜}";
- erro.ErrorCont = "妯″叿琛�:{*搴撲綅缂栫爜}瀛楁" + excelTable[0].Rows[k][5].ToString().Trim() + "涓巤*浠撳簱缂栫爜}瀛楁锛�" + excelTable[0].Rows[k][4].ToString().Trim() + "涓嶅尮閰�";
- list.Add(erro);
- }
- }
- }
- else
- {
- ExcelErro erro = new ExcelErro();
- erro.RoeNumber = "/";
- erro.ErrorField = "{*搴撲綅缂栫爜}";
- erro.ErrorCont = "妯″叿琛�:{*搴撲綅缂栫爜}瀛楁" + excelTable[0].Rows[k][5].ToString().Trim() + "瀵瑰簲鐨勪粨搴撳瓧娈典笉鑳戒负绌�";
+ erro.ErrorField = "{*妯″叿缂栧彿(鍞竴)}/{*妯″叿鍚嶇О}";
+ erro.ErrorCont = $"*妯″叿缂栧彿(鍞竴): {duplicateRow["*妯″叿缂栧彿(鍞竴)"]},鎴�,*妯″叿鍚嶇О: {duplicateRow["*妯″叿鍚嶇О"]}宸插瓨鍦�!";
list.Add(erro);
}
}
}
- //妯″叿鍏宠仈浜у搧琛�
- for (int j = 0; j < excelTable[1].Rows.Count; j++)
+ //鏌ヨ浠撳簱淇℃伅
+ sql = @"select code,name from TSecStck";
+ dt = DapperHelper.selecttable(sql);
+ if (dt.Rows.Count > 0)
{
- if (excelTable[1].Rows[j][1].ToString().Trim() != null && excelTable[1].Rows[j][1].ToString().Trim() != "")
+ //鏍￠獙褰撳墠涓婁紶鐨勬ā鍏蜂粨搴撴槸鍚﹀瓨鍦�
+ var aRows = excelTable[0].AsEnumerable();
+ var bCodes = dt.AsEnumerable().Select(row => row["code"].ToString()).ToList(); // 鎻愬彇鏁版嵁搴撴煡璇腑鎵�鏈塩ode鍊�
+ // 浣跨敤LINQ鍜孡ambda琛ㄨ揪寮忔潵鏌ユ壘鎻愪氦鏁版嵁浠撳簱缂栫爜涓湪鏁版嵁搴撲腑涓嶅瓨鍦ㄧ殑code
+ var notInB = aRows.Where(aRow => !bCodes.Contains(aRow["*浠撳簱缂栫爜"].ToString())).ToList();
+ // 杈撳嚭鎴栧鐞嗘壘鍒扮殑璁板綍
+ foreach (DataRow row in notInB)
{
- sql = @"select * from TMateriel_Info where partcode=@partcode";
- dynamicParams.Add("@partcode", excelTable[1].Rows[j][1].ToString().Trim());
- dt = DapperHelper.selectdata(sql, dynamicParams);
- if (dt.Rows.Count <= 0)
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{*浠撳簱缂栫爜}";
+ erro.ErrorCont = $"*浠撳簱缂栫爜: {row["*浠撳簱缂栫爜"]}涓嶅瓨鍦�!";
+ list.Add(erro);
+ }
+ }
+ //鏌ヨ搴撲綅淇℃伅
+ sql = @"select code,idwarehouse from TSecLoca";
+ dt = DapperHelper.selecttable(sql);
+ if (dt.Rows.Count > 0)
+ {
+ //鏍￠獙褰撳墠涓婁紶鐨勬ā鍏峰簱浣嶆槸鍚﹀瓨鍦�
+ var aRows = excelTable[0].AsEnumerable();
+ var bCodes = dt.AsEnumerable().Select(row => row["code"].ToString()).ToList(); // 鎻愬彇鏁版嵁搴撴煡璇腑鎵�鏈塩ode鍊�
+ // 浣跨敤LINQ鍜孡ambda琛ㄨ揪寮忔潵鏌ユ壘鎻愪氦鏁版嵁搴撲綅缂栫爜涓湪鏁版嵁搴撲腑涓嶅瓨鍦ㄧ殑code
+ var notInB = aRows.Where(aRow => !bCodes.Contains(aRow["*搴撲綅缂栫爜"].ToString())).ToList();
+ // 杈撳嚭鎴栧鐞嗘壘鍒扮殑璁板綍
+ foreach (DataRow row in notInB)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{*搴撲綅缂栫爜}";
+ erro.ErrorCont = $"*搴撲綅缂栫爜: {row["*浠撳簱缂栫爜"]}涓嶅瓨鍦�!";
+ list.Add(erro);
+ }
+
+ //鏍￠獙褰撳墠涓婁紶鐨勬ā鍏峰簱浣嶈窡浠撳簱鏄惁鍖归厤
+ var aItems = aRows.Select(row => new
+ {
+ WarehouseCode = row["*浠撳簱缂栫爜"].ToString(),
+ LocationCode = row["*搴撲綅缂栫爜"].ToString(),
+ }).ToList();
+
+ var bItems = dt.AsEnumerable().Select(row => new
+ {
+ IdWarehouse = row["idwarehouse"].ToString(),
+ Code = row["code"].ToString(),
+ }).ToList();
+
+ // 浣跨敤LINQ鍜孡ambda琛ㄨ揪寮忔潵鏌ユ壘A涓湪B涓病鏈夊尮閰峣dwarehouse+code鐨勯」
+ var notMatching = aItems.Where(a => !bItems.Any(b =>
+ a.WarehouseCode == b.IdWarehouse && a.LocationCode == b.Code
+ )).ToList();
+
+ // 杈撳嚭鎴栧鐞嗘壘鍒扮殑涓嶅尮閰嶉」
+ foreach (var item in notMatching)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{*浠撳簱缂栫爜}/{*搴撲綅缂栫爜}";
+ erro.ErrorCont = $"*浠撳簱缂栫爜: {item.WarehouseCode},涓庯紝*搴撲綅缂栫爜: {item.LocationCode}涓嶅尮閰�!";
+ list.Add(erro);
+ }
+ }
+ //妯″叿鍏宠仈浜у搧琛�
+ if (excelTable[1].Rows.Count > 0)
+ {
+ sql = @"select partcode,partname,partspec from TMateriel_Info";
+ dt = DapperHelper.selecttable(sql);
+ if (dt.Rows.Count > 0)
+ {
+ //鏍￠獙褰撳墠涓婁紶鐨勬ā鍏蜂粨搴撴槸鍚﹀瓨鍦�
+ var aRows = excelTable[1].AsEnumerable();
+ var bCodes = dt.AsEnumerable().Select(row => row["partcode"].ToString()).ToList(); // 鎻愬彇鏁版嵁搴撴煡璇腑鎵�鏈塸artcode鍊�
+ // 浣跨敤LINQ鍜孡ambda琛ㄨ揪寮忔潵鏌ユ壘鎻愪氦鏁版嵁浜у搧缂栫爜涓湪鏁版嵁搴撲腑涓嶅瓨鍦ㄧ殑code
+ var notInB = aRows.Where(aRow => !bCodes.Contains(aRow["浜у搧缂栫爜"].ToString())).ToList();
+ // 杈撳嚭鎴栧鐞嗘壘鍒扮殑璁板綍
+ foreach (DataRow row in notInB)
{
ExcelErro erro = new ExcelErro();
erro.RoeNumber = "/";
erro.ErrorField = "{浜у搧缂栫爜}";
- erro.ErrorCont = "妯″叿鍏宠仈浜у搧琛�:{浜у搧缂栫爜}瀛楁" + excelTable[1].Rows[j][1].ToString().Trim() + "涓嶅瓨鍦�";
+ erro.ErrorCont = "妯″叿鍏宠仈浜у搧琛�:{浜у搧缂栫爜}:" +row["浜у搧缂栫爜"] +"涓嶅瓨鍦�!";
list.Add(erro);
}
}
- }
- //鍒ゆ柇妯″叿鍏宠仈浜у搧琛ㄦ槸鍚︽湁鏁版嵁
- if (excelTable[1].Rows.Count > 0)
- {
//鍒ゆ柇瀛愯〃澶栭敭涓嶅瓨鍦ㄤ簬涓昏〃涓婚敭涓殑鏁版嵁
var dt3 = from r in excelTable[1].AsEnumerable()
where !(
diff --git a/VueWebCoreApi/appsettings.json b/VueWebCoreApi/appsettings.json
index f61e1d5..a79e77e 100644
--- a/VueWebCoreApi/appsettings.json
+++ b/VueWebCoreApi/appsettings.json
@@ -10,7 +10,8 @@
//鏈嶅姟鍣ㄧ幆澧�
"ConnectionStrings": {
//"DBServer": "Data Source=121.196.36.24,1533;Initial Catalog=vmes_JL;User ID=sa;Password=xkd@20230101;pooling=false",
- "DBServer": "Data Source=192.168.1.237,1433;Initial Catalog=vmes;User ID=sa;Password=123;pooling=false",
+ //"DBServer": "Data Source=192.168.1.237,1433;Initial Catalog=vmes;User ID=sa;Password=123;pooling=false",
+ "DBServer": "Data Source=121.196.36.24,1533;Initial Catalog=vmes_sf;User ID=sa;Password=xkd@20230101;pooling=false",
//浼佷笟缂栫爜
"CompanyCode": "000",
//浼佷笟鍚嶇О
--
Gitblit v1.9.3