From dcaa18d53b4d31c7d4a16a80e638568fa3069e6b Mon Sep 17 00:00:00 2001
From: llj <132905093+newwwwwwtree@users.noreply.github.com>
Date: 星期六, 28 二月 2026 11:29:19 +0800
Subject: [PATCH] 1.过滤加组织 2.职员和生产订单不是必填项 3.导入审查新增：字段信息是否在系统存在要有报错 4.前后导入主表信息相同时，新增：子表信息不同的数据会导入失败的报错 5.增加子表必填项目：成本项目必填，金额必填，其他不必填
---
WebAPI/Controllers/成本管理/CB_ItemMoneyBillController.cs | 120 +++++++++++++++++++++++++++++++++++++++++-------------------
1 files changed, 82 insertions(+), 38 deletions(-)
diff --git "a/WebAPI/Controllers/\346\210\220\346\234\254\347\256\241\347\220\206/CB_ItemMoneyBillController.cs" "b/WebAPI/Controllers/\346\210\220\346\234\254\347\256\241\347\220\206/CB_ItemMoneyBillController.cs"
index 079a2f1..34d81d4 100644
--- "a/WebAPI/Controllers/\346\210\220\346\234\254\347\256\241\347\220\206/CB_ItemMoneyBillController.cs"
+++ "b/WebAPI/Controllers/\346\210\220\346\234\254\347\256\241\347\220\206/CB_ItemMoneyBillController.cs"
@@ -1118,14 +1118,22 @@
var HDeptNumbers_sub = list.Select(x => x["閮ㄩ棬浠ｇ爜"].ToString()).Distinct().ToList();
var materialNumbers = list.Select(x => x["鐗╂枡缂栫爜"].ToString()).Distinct().ToList();
var materialNames = list.Select(x => x["鐗╂枡鍚嶇О"].ToString()).Distinct().ToList();
- var EmpNumbers = list.Select(x => x["鑱屽憳浠ｇ爜"].ToString()).Distinct().ToList();
- var EmpNames = list.Select(x => x["鑱屽憳"].ToString()).Distinct().ToList();
+ //var EmpNumbers = list.Select(x => x["鑱屽憳浠ｇ爜"].ToString()).Distinct().ToList();
+ //var EmpNames = list.Select(x => x["鑱屽憳"].ToString()).Distinct().ToList();
+ var EmpNumbers = list.Select(x => x["鑱屽憳浠ｇ爜"]?.ToString())
+ .Where(s => !string.IsNullOrEmpty(s))
+ .Distinct()
+ .ToList();
+ var EmpNames = list.Select(x => x["鑱屽憳"]?.ToString())
+ .Where(s => !string.IsNullOrEmpty(s))
+ .Distinct()
+ .ToList();
var CostNumbers = list.Select(x => x["鎴愭湰椤圭洰浠ｇ爜"].ToString()).Distinct().ToList();
var CostNames = list.Select(x => x["鎴愭湰椤圭洰"].ToString()).Distinct().ToList();
var ICMONumbers = list.Select(x => x["鐢熶骇璁㈠崟鍙�"].ToString()).Distinct().ToList();
// 2. 鎵归噺鏌ヨ鐢熶骇璁㈠崟鍐呯爜
- string ICMOQuery = $"SELECT HInterID,HBillNo FROM Sc_ICMOBillMain WHERE HBillNo IN ({string.Join(",", ICMONumbers.Select(s => $"'{s.Replace("'", "''")}'"))})";
+ string ICMOQuery = $"SELECT HInterID,HBillNo FROM Sc_ICMOBillMain WHERE HBillNo IN ({string.Join(",", ICMONumbers.Select(s => $"'{s.Replace("'", "''")}'"))}) and HPRDORGID={organ}";
DataSet icmoData = oCN.RunProcReturn(ICMOQuery, "Sc_ICMOBillMain");
Dictionary<string, int> ICMODict = new Dictionary<string, int>();
foreach (DataRow row in icmoData.Tables[0].Rows)
@@ -1143,7 +1151,7 @@
//}
// 2.1 鎵归噺鏌ヨ涓诲瓙琛ㄩ儴闂ㄤ俊鎭�
- string supplierQuery_sub = $"SELECT HItemID, HName FROM Gy_Department WHERE HName IN ({string.Join(",", HDeptNames_sub.Select(s => $"'{s.Replace("'", "''")}'"))})";
+ string supplierQuery_sub = $"SELECT HItemID, HName FROM Gy_Department WHERE HName IN ({string.Join(",", HDeptNames_sub.Select(s => $"'{s.Replace("'", "''")}'"))}) and HUSEORGID ={organ}";
DataSet supData_sub = oCN.RunProcReturn(supplierQuery_sub, "Gy_Department");
Dictionary<string, int> supplierDict_sub = new Dictionary<string, int>();
foreach (DataRow row in supData_sub.Tables[0].Rows)
@@ -1153,7 +1161,7 @@
// 3. 鎵归噺鏌ヨ鐗╂枡淇℃伅
string materialQuery =
- $"SELECT HItemID, HNumber, HName FROM Gy_Material WHERE HNumber IN ({string.Join(",", materialNumbers.Select(m => $"'{m.Replace("'", "''")}'"))}) AND HName IN ({string.Join(",", materialNames.Select(m => $"'{m.Replace("'", "''")}'"))})";
+ $"SELECT HItemID, HNumber, HName FROM Gy_Material WHERE HNumber IN ({string.Join(",", materialNumbers.Select(m => $"'{m.Replace("'", "''")}'"))}) AND HName IN ({string.Join(",", materialNames.Select(m => $"'{m.Replace("'", "''")}'"))}) and HUSEORGID ={organ}";
DataSet materData = oCN.RunProcReturn(materialQuery, "Gy_Material");
Dictionary<string, int> materialDict = new Dictionary<string, int>();
foreach (DataRow row in materData.Tables[0].Rows)
@@ -1161,10 +1169,18 @@
string key = $"{row["HNumber"]}_{row["HName"]}";
materialDict[key] = (int)row["HItemID"];
}
+ if (EmpNames.Count == 0 && EmpNumbers.Count == 0)
+ {
+ }
// 3. 鎵归噺鏌ヨ鑱屽憳淇℃伅
- string EmployeeQuery =
- $"SELECT HItemID, HNumber, HName FROM Gy_Employee WHERE HNumber IN ({string.Join(",", EmpNumbers.Select(m => $"'{m.Replace("'", "''")}'"))}) AND HName IN ({string.Join(",", EmpNames.Select(m => $"'{m.Replace("'", "''")}'"))})";
+ string empNumbersIn = EmpNumbers.Any()
+ ? string.Join(",", EmpNumbers.Select(m => $"'{m.Replace("'", "''")}'"))
+ : "''";
+ string empNamesIn = EmpNames.Any()
+ ? string.Join(",", EmpNames.Select(m => $"'{m.Replace("'", "''")}'"))
+ : "''";
+ string EmployeeQuery = $"SELECT HItemID, HNumber, HName FROM Gy_Employee WHERE HNumber IN ({empNumbersIn}) AND HName IN ({empNamesIn}) and HUSEORGID ={organ}";
DataSet EmpData = oCN.RunProcReturn(EmployeeQuery, "Gy_Employee");
Dictionary<string, int> EmpDict = new Dictionary<string, int>();
foreach (DataRow row in EmpData.Tables[0].Rows)
@@ -1174,7 +1190,7 @@
}
// 3. 鎵归噺鏌ヨchengbenxiangmu淇℃伅
- string CostQuery = $"SELECT HItemID, HNumber, HName FROM Gy_ItemMoney_1 WHERE HNumber IN ({string.Join(",", CostNumbers.Select(m => $"'{m.Replace("'", "''")}'"))}) AND HName IN ({string.Join(",", CostNames.Select(m => $"'{m.Replace("'", "''")}'"))})";
+ string CostQuery = $"SELECT HItemID, HNumber, HName FROM Gy_ItemMoney_1 WHERE HNumber IN ({string.Join(",", CostNumbers.Select(m => $"'{m.Replace("'", "''")}'"))}) AND HName IN ({string.Join(",", CostNames.Select(m => $"'{m.Replace("'", "''")}'"))}) and HUSEORGID ={organ}";
DataSet CostData = oCN.RunProcReturn(CostQuery, "Gy_ItemMoney_1");
Dictionary<string, int> CostDict = new Dictionary<string, int>();
foreach (DataRow row in CostData.Tables[0].Rows)
@@ -1183,7 +1199,7 @@
CostDict[key] = (int)row["HItemID"];
}
// 3.1 鎵归噺鏌ヨ宸ュ簭淇℃伅
- string ProcessQuery = $"SELECT HItemID, HNumber, HName FROM Gy_Process WHERE HName IN ({string.Join(",", HProc.Select(m => $"'{m.Replace("'", "''")}'"))})";
+ string ProcessQuery = $"SELECT HItemID, HNumber, HName FROM Gy_Process WHERE HName IN ({string.Join(",", HProc.Select(m => $"'{m.Replace("'", "''")}'"))}) and HUSEORGID ={organ}";
DataSet ProcData = oCN.RunProcReturn(ProcessQuery, "Gy_Process");
Dictionary<string, int> ProcDict = new Dictionary<string, int>();
foreach (DataRow row in ProcData.Tables[0].Rows)
@@ -1206,6 +1222,9 @@
// 5. 妫�鏌ョ己澶辩殑閮ㄩ棬鍜岀墿鏂欙紝骞惰褰曡鍙�
List<string> missingSupplierMessages = new List<string>();
List<string> missingMaterialMessages = new List<string>();
+ List<string> missingCostMessages = new List<string>();
+ List<string> missingmoneyMessages = new List<string>();
+ List<string> missingEmployeeMessages = new List<string>();
// 閬嶅巻姣忎竴琛屾暟鎹紝璁板綍琛屽彿锛堜粠1寮�濮嬶級
for (int i = 0; i < list.Count; i++)
@@ -1214,11 +1233,18 @@
int lineNumber = i + 1; // 琛屽彿浠�1寮�濮�
string ICMOcheck = item["鐢熶骇璁㈠崟鍙�"];
+ string Costcheck = item["鎴愭湰椤圭洰"];
+ string Procheck = item["宸ュ簭"];
string supplier = item["閮ㄩ棬"];
string materialNumber = item["鐗╂枡缂栫爜"];
string materialName = item["鐗╂枡鍚嶇О"];
+ string monetcheck = item["閲戦"];
string materialKey = $"{materialNumber}_{materialName}";
-
+ string empName = item["鑱屽憳"];
+ if (!string.IsNullOrEmpty(empName) && !EmpDict.ContainsKey(empName))
+ {
+ missingEmployeeMessages.Add($"绗瑊lineNumber}琛岀殑鑱屽憳銆恵empName}銆�");
+ }
// 妫�鏌ヤ緵搴斿晢
if (!supplierDict_sub.ContainsKey(supplier))
{
@@ -1234,6 +1260,20 @@
if (!string.IsNullOrEmpty(ICMOcheck) && !ICMODict.ContainsKey(ICMOcheck))
{
missingMaterialMessages.Add($"绗瑊lineNumber}琛岀殑鐢熶骇璁㈠崟銆恵ICMOcheck}銆�");
+ }
+ // 妫�鏌ラ」鐩垚鏈�
+ if (CostDict.Count == 0)
+ {
+ missingCostMessages.Add($"绗瑊lineNumber}琛岀殑椤圭洰鎴愭湰銆恵Costcheck}銆�");
+ }
+ // 妫�鏌ュ伐搴�
+ if (ProcDict.Count == 0 && Procheck != "")
+ {
+ missingCostMessages.Add($"绗瑊lineNumber}琛岀殑宸ュ簭銆恵Procheck}銆�");
+ }
+ if (monetcheck == "" || monetcheck == null)
+ {
+ missingmoneyMessages.Add($"绗瑊lineNumber}琛岄噾棰濄�恵monetcheck}銆�");
}
}
@@ -1252,6 +1292,24 @@
errorMessage.Append(string.Join("銆�", missingMaterialMessages));
errorMessage.Append("锛�");
}
+ if (missingCostMessages.Any())
+ {
+ errorMessage.Append("鎴愭湰椤圭洰鏈淮鎶ゆ垨鏈夎锛�");
+ errorMessage.Append(string.Join("銆�", missingCostMessages));
+ errorMessage.Append("锛�");
+ }
+ if (missingmoneyMessages.Any())
+ {
+ errorMessage.Append("閲戦鏈淮鎶ゆ垨鏈夎锛�");
+ errorMessage.Append(string.Join("銆�", missingmoneyMessages));
+ errorMessage.Append("锛�");
+ }
+ if (missingEmployeeMessages.Any())
+ {
+ errorMessage.Append("浠ヤ笅鑱屽憳鏈淮鎶わ細");
+ errorMessage.Append(string.Join("銆�", missingEmployeeMessages));
+ errorMessage.Append("锛�");
+ }
if (errorMessage.Length > 0)
{
@@ -1262,17 +1320,7 @@
return res;
}
- // 6. 鎵归噺鏌ヨ宸插瓨鍦ㄧ殑璁板綍
- var materialIds = list.Select(x => materialDict[$"{x["鐗╂枡缂栫爜"]}_{x["鐗╂枡鍚嶇О"]}"]).Distinct().ToList();
- var supplierIds = list.Select(x => supplierDict_sub[x["閮ㄩ棬"]]).Distinct().ToList();
-
- string existQuery = $"select * from h_v_CB_ItemMoneyBillMain_Edit_excel WHERE HMaterID IN ({string.Join(",", materialIds)}) AND HDeptID_sub IN ({string.Join(",", supplierIds)})";
- DataSet existData = oCN.RunProcReturn(existQuery, "h_v_CB_ItemMoneyBillMain_Edit_excel");
- HashSet<string> existRecords = new HashSet<string>();
- foreach (DataRow row in existData.Tables[0].Rows)
- {
- existRecords.Add($"{row["HMaterID"]}_{row["HDeptID_sub"]}");
- }
+
// 7. 鎸夌収閮ㄩ棬浠ｇ爜銆侀儴闂ㄣ�佽亴鍛樹唬鐮併�佽亴鍛樸�佺敓浜ц鍗曞彿杩涜鍒嗙粍
var groupedData = list.GroupBy(item => new
@@ -1294,7 +1342,13 @@
// 鑾峰彇缁勪俊鎭�
int HSupplierID = supplierDict_sub[firstItem["閮ㄩ棬"]];
- int EmpID = EmpDict[firstItem["鑱屽憳"]];
+ //int EmpID = EmpDict[firstItem["鑱屽憳"]];
+ int EmpID;
+ string empName = firstItem["鑱屽憳"];
+ if (string.IsNullOrEmpty(empName))
+ EmpID = 0; // 鎴栧叾浠栭粯璁ゅ��
+ else
+ EmpID = EmpDict[empName];
string ICMO = firstItem["鐢熶骇璁㈠崟鍙�"];
int ICMOInterID = !string.IsNullOrEmpty(ICMO) && ICMODict.ContainsKey(ICMO) ? ICMODict[ICMO] : 0;
@@ -1302,21 +1356,10 @@
string HBillNo = DBUtility.ClsPub.CreateBillCode("1802", ref DBUtility.ClsPub.sExeReturnInfo, true);
long HInterID = DBUtility.ClsPub.CreateBillID("1802", ref DBUtility.ClsPub.sExeReturnInfo);
- // 妫�鏌ヨ缁勬槸鍚﹀凡瀛樺湪锛堟牴鎹儴闂↖D鍜岀墿鏂橧D缁勫悎锛�
- bool groupExists = false;
- foreach (var item in group)
- {
- int HMaterID = materialDict[$"{item["鐗╂枡缂栫爜"]}_{item["鐗╂枡鍚嶇О"]}"];
- string recordKey = $"{HMaterID}_{HSupplierID}";
- if (existRecords.Contains(recordKey))
- {
- groupExists = true;
- break;
- }
- }
- if (!groupExists)
- {
+
+
+
// 鎻掑叆涓昏〃锛堟瘡缁勬彃鍏ヤ竴娆★級
insertSql.AppendLine($"INSERT INTO CB_ItemMoneyBillMain ([HInterID], [HBillNo], [HDate], [HYear], [HPeriod], [HBillType], [HBillSubType], [HBillStatus], [HCheckItemNowID], [HCheckItemNextID], [HCheckFlowID], [HRemark], [HBacker], [HBackDate], [HBackRemark], [HChecker], [HCheckDate], [HMaker], [HMakeDate], [HUpDater], [HUpDateDate], [HCloseMan], [HCloseDate], [HCloseType], [HDeleteMan], [HDeleteDate], [HMainSourceBillType], [HMainSourceInterID], [HMainSourceEntryID], [HMainSourceBillNo], [HPrintQty], [HEmpID], [HDeptID], [HICMOInterID], [HMaterID], [HICMOBillNo], [HExplanation], [HInnerBillNo], [HBeginDate], [HEndDate], [HOrgID]) VALUES (");
insertSql.AppendLine($"'{HInterID}', '{HBillNo}', '{DateTime.Now}', {list[0]["骞翠唤"]}, {list[0]["鏈堜唤"]}, {"1802"}, '{"1802"}', '{"1"}', '{"0"}', '{"0"}', '{"0"}', '{"excel瀵煎叆"}', '{""}', '{DateTime.Now}', '{""}', '{""}', '{DateTime.Now}', '{""}', '{DateTime.Now}', '{""}', '{DateTime.Now}', '{""}', '{DateTime.Now}', '{"0"}', '{""}', '{DateTime.Now}', '{""}', '{"0"}', '{"0"}', '{""}', '{"0"}', '{EmpID}', '{HSupplierID}', '{ICMOInterID}','{0}','{ICMO}','{""}','{"0"}','{DateTime.Now}','{DateTime.Now}','{"100038"}');");
@@ -1333,8 +1376,9 @@
insertSql_sub.AppendLine($"'{HInterID}', '{HEntryID}', '{""}', '{DateTime.Now}', '{"0"}', '{"excel瀵煎叆"}','{"0"}', '{"0"}', '{"0"}', '{"0"}','{"0"}', '{"0"}', '{""}', '{HCostID}', '{HSupplierID}', '{item["鏁伴噺"]}', '{item["鍗曚环"]}', '{item["閲戦"]}','{HMaterID}', '{item["鎹熻�楅噺"] ?? "0"}', '{DateTime.Now}', '{"0"}', '{"0"}', '{"0"}', '{HProcID}');");
HEntryID++;
}
- }
-
+
+
+
}
// 鎵ц鎵归噺鎿嶄綔
--
Gitblit v1.9.1