From 7636c2cfa97cd3c24070593c899d4e4e5d0caf89 Mon Sep 17 00:00:00 2001
From: yusijie <ysj@hz-kingdee.com>
Date: 星期五, 10 四月 2026 16:49:10 +0800
Subject: [PATCH] 选择关键件进行解绑
---
WebAPI/Controllers/成本管理/CB_ItemMoneyBillController.cs | 249 ++++++++++++++++++++++++++++++++++---------------
1 files changed, 171 insertions(+), 78 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 8f9774e..e4cf686 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"
@@ -1011,8 +1011,8 @@
for (int i = 0; i <= provisional.Rows.Count - 1; i++)
{
- string HBillNo= DBUtility.ClsPub.CreateBillCode("1802", ref DBUtility.ClsPub.sExeReturnInfo, true);
- provisional.Rows[i]["鍗曟嵁鍙�"] = HBillNo;
+ //string HBillNo= DBUtility.ClsPub.CreateBillCode("1802", ref DBUtility.ClsPub.sExeReturnInfo, true);
+ //provisional.Rows[i]["鍗曟嵁鍙�"] = HBillNo;
string HDepNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["閮ㄩ棬浠ｇ爜"].ToString());
string HDepName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["閮ㄩ棬"].ToString());
@@ -1020,14 +1020,15 @@
string HEmpName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鑱屽憳"].ToString());
string HICMOBillNo = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鐢熶骇璁㈠崟鍙�"].ToString());
+ string HProc = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["宸ュ簭"].ToString());
string HCostItemNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鎴愭湰椤圭洰浠ｇ爜"].ToString());
string HCostItemName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鎴愭湰椤圭洰"].ToString());
- string HEntryID = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["瀛愯〃鍐呯爜"].ToString());
+ //string HEntryID = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["瀛愯〃鍐呯爜"].ToString());
- string HDepNumber_sub = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["瀛愰儴闂ㄤ唬鐮�"].ToString());
- string HDepName_sub = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["瀛愰儴闂�"].ToString());
+ //string HDepNumber_sub = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["瀛愰儴闂ㄤ唬鐮�"].ToString());
+ //string HDepName_sub = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["瀛愰儴闂�"].ToString());
string HMaterNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鐗╂枡缂栫爜"].ToString());
string HMaterName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鐗╂枡鍚嶇О"].ToString());
@@ -1036,8 +1037,8 @@
string HPrice = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鍗曚环"].ToString());
string HMoney = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["閲戦"].ToString());
string HWaster = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鎹熻�楅噺"].ToString());
- string HRelationQty = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鍏宠仈鏁伴噺"].ToString());
- string HRelationMoney = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鍏宠仈閲戦"].ToString());
+ //string HRelationQty = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鍏宠仈鏁伴噺"].ToString());
+ //string HRelationMoney = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鍏宠仈閲戦"].ToString());
string remark = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["澶囨敞"].ToString());
//鑾峰彇鐪熷疄琛屾暟
@@ -1070,8 +1071,8 @@
var error = "";
//鏌ヨ娌℃湁鐨勫垪
- if (!provisional.Columns.Contains("瀛愯〃鍐呯爜"))
- error += "娌℃湁鎵惧埌銆愬瓙琛ㄥ唴鐮併�戠殑鏍囬,";
+ //if (!provisional.Columns.Contains("瀛愯〃鍐呯爜"))
+ // error += "娌℃湁鎵惧埌銆愬瓙琛ㄥ唴鐮併�戠殑鏍囬,";
if (!provisional.Columns.Contains("鐗╂枡缂栫爜"))
error += "娌℃湁鎵惧埌銆愮墿鏂欑紪鐮併�戠殑鏍囬,";
@@ -1084,7 +1085,6 @@
[HttpPost]
public object Excel_btnSave([FromBody] JObject sMainSub)
{
-
var _value = sMainSub["sMainSub"].ToString();
string msg1 = _value.ToString();
string[] sArray = msg1.Split(new string[] { "&鍜�" }, StringSplitOptions.RemoveEmptyEntries);
@@ -1111,30 +1111,48 @@
// 1. 鏀堕泦鎵�鏈夐渶瑕佹煡璇㈢殑鏁版嵁
var HBillnos = list.Select(x => x["鍗曟嵁鍙�"].ToString()).Distinct().ToList();
- var HDeptNames = list.Select(x => x["閮ㄩ棬"].ToString()).Distinct().ToList();
- var HDeptNumbers = list.Select(x => x["閮ㄩ棬浠ｇ爜"].ToString()).Distinct().ToList();
- var HDeptNames_sub = list.Select(x => x["瀛愰儴闂�"].ToString()).Distinct().ToList();
- var HDeptNumbers_sub = list.Select(x => x["瀛愰儴闂ㄤ唬鐮�"].ToString()).Distinct().ToList();
+ var HProc = list.Select(x => x["宸ュ簭"].ToString()).Distinct().ToList();
+ //var HDeptNames = list.Select(x => x["閮ㄩ棬"].ToString()).Distinct().ToList();
+ //var HDeptNumbers = list.Select(x => x["閮ㄩ棬浠ｇ爜"].ToString()).Distinct().ToList();
+ var HDeptNames_sub = list.Select(x => x["閮ㄩ棬"].ToString()).Distinct().ToList();
+ 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 supplierQuery = $"SELECT HItemID, HName FROM Gy_Department WHERE HName IN ({string.Join(",", HDeptNames.Select(s => $"'{s.Replace("'", "''")}'"))})";
- DataSet supData = oCN.RunProcReturn(supplierQuery, "Gy_Department");
- Dictionary<string, int> supplierDict = new Dictionary<string, int>();
- foreach (DataRow row in supData.Tables[0].Rows)
+ // 2. 鎵归噺鏌ヨ鐢熶骇璁㈠崟鍐呯爜
+ 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)
{
- supplierDict[row["HName"].ToString()] = (int)row["HItemID"];
+ ICMODict[row["HBillNo"].ToString()] = (int)row["HInterID"];
}
+ //// 2. 鎵归噺鏌ヨ涓诲瓙琛ㄩ儴闂ㄤ俊鎭�
+ //string supplierQuery = $"SELECT HItemID, HName FROM Gy_Department WHERE HName IN ({string.Join(",", HDeptNames.Select(s => $"'{s.Replace("'", "''")}'"))})";
+ //DataSet supData = oCN.RunProcReturn(supplierQuery, "Gy_Department");
+ //Dictionary<string, int> supplierDict = new Dictionary<string, int>();
+ //foreach (DataRow row in supData.Tables[0].Rows)
+ //{
+ // supplierDict[row["HName"].ToString()] = (int)row["HItemID"];
+ //}
+
// 2.1 鎵归噺鏌ヨ涓诲瓙琛ㄩ儴闂ㄤ俊鎭�
- string supplierQuery_sub = $"SELECT HItemID, HName FROM Gy_Department WHERE HName IN ({string.Join(",", HDeptNames_sub.Select(s => $"'{s.Replace("'", "''")}'"))})";
- DataSet supData_sub = oCN.RunProcReturn(supplierQuery, "Gy_Department");
+ 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)
{
@@ -1143,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)
@@ -1151,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)
@@ -1164,16 +1190,23 @@
}
// 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("'", "''")}'"))})";
- DataSet CostData = oCN.RunProcReturn(CostQuery, "Gy_ItemMoney_1");
+ string CostQuery = $"SELECT HItemID, HNumber, HName FROM Gy_CostItem 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_CostItem");
Dictionary<string, int> CostDict = new Dictionary<string, int>();
foreach (DataRow row in CostData.Tables[0].Rows)
{
string key = $"{row["HName"].ToString()}";
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("'", "''")}'"))}) 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)
+ {
+ string key = $"{row["HName"].ToString()}";
+ ProcDict[key] = (int)row["HItemID"];
+ }
// 4. 鏌ヨ鐢ㄦ埛淇℃伅
DataSet emp = oCN.RunProcReturn($"SELECT Czybm FROM Gy_Czygl WHERE Czymc = '{user.Replace("'", "''")}'", "Gy_Czygl");
if (emp.Tables[0].Rows.Count == 0)
@@ -1189,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++)
@@ -1196,13 +1232,21 @@
var item = list[i];
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.ContainsKey(supplier))
+ if (!supplierDict_sub.ContainsKey(supplier))
{
missingSupplierMessages.Add($"绗瑊lineNumber}琛岀殑閮ㄩ棬銆恵supplier}銆�");
}
@@ -1211,6 +1255,25 @@
if (!materialDict.ContainsKey(materialKey))
{
missingMaterialMessages.Add($"绗瑊lineNumber}琛岀殑鐗╂枡銆恵materialName}(缂栫爜:{materialNumber})銆�");
+ }
+ // 妫�鏌ョ敓浜ц鍗�
+ if (!string.IsNullOrEmpty(ICMOcheck) && !ICMODict.ContainsKey(ICMOcheck))
+ {
+ missingMaterialMessages.Add($"绗瑊lineNumber}琛岀殑鐢熶骇璁㈠崟銆恵ICMOcheck}銆�");
+ }
+ // 妫�鏌ラ」鐩垚鏈�
+ if (CostDict.Count == 0|| !CostDict.ContainsKey(Costcheck))
+ {
+ missingCostMessages.Add($"绗瑊lineNumber}琛岀殑椤圭洰鎴愭湰銆恵Costcheck}銆�");
+ }
+ // 妫�鏌ュ伐搴�
+ if (ProcDict.Count == 0 && Procheck != "")
+ {
+ missingCostMessages.Add($"绗瑊lineNumber}琛岀殑宸ュ簭銆恵Procheck}銆�");
+ }
+ if (monetcheck == "" || monetcheck == null)
+ {
+ missingmoneyMessages.Add($"绗瑊lineNumber}琛岄噾棰濄�恵monetcheck}銆�");
}
}
@@ -1229,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)
{
@@ -1239,71 +1320,83 @@
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)
+ // 7. 鎸夌収閮ㄩ棬浠ｇ爜銆侀儴闂ㄣ�佽亴鍛樹唬鐮併�佽亴鍛樸�佺敓浜ц鍗曞彿杩涜鍒嗙粍
+ var groupedData = list.GroupBy(item => new
{
- existRecords.Add($"{row["HMaterID"]}_{row["HDeptID_sub"]}");
- }
+ 閮ㄩ棬浠ｇ爜 = item["閮ㄩ棬浠ｇ爜"],
+ 閮ㄩ棬 = item["閮ㄩ棬"],
+ 鑱屽憳浠ｇ爜 = item["鑱屽憳浠ｇ爜"],
+ 鑱屽憳 = item["鑱屽憳"],
+ 鐢熶骇璁㈠崟鍙� = item["鐢熶骇璁㈠崟鍙�"]
+ }).ToList();
StringBuilder insertSql = new StringBuilder();
StringBuilder insertSql_sub = new StringBuilder();
- StringBuilder updateSql = new StringBuilder();
- StringBuilder updateSql_sub = new StringBuilder();
string currentDate = DateTime.Today.ToString("yyyy-MM-dd");
- int HEntryID = 1;
- foreach (Dictionary<string, string> item in list)
+
+ foreach (var group in groupedData)
{
- int HSupplierID = supplierDict_sub[item["閮ㄩ棬"]];
- int EmpID = EmpDict[item["鑱屽憳"]];//
- int HCostID = CostDict[item["鎴愭湰椤圭洰"]] ;//EmpDict[item["鑱屽憳"]];
- int HMaterID = materialDict[$"{item["鐗╂枡缂栫爜"]}_{item["鐗╂枡鍚嶇О"]}"];
- string recordKey = $"{HMaterID}_{HSupplierID}";
- long HInterID = DBUtility.ClsPub.CreateBillID("1802", ref DBUtility.ClsPub.sExeReturnInfo);
- if (!existRecords.Contains(recordKey))
- {
-
- 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}', '{item["鍗曟嵁鍙�"]}', '{DateTime.Now}', {DateTime.Now.Year}, {DateTime.Now.Month}, {"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}', '{"0"}','{HMaterID}','{"0"}','{""}','{"0"}','{DateTime.Now}','{DateTime.Now}','{"100038"}');");
+ var firstItem = group.First();
- insertSql_sub.AppendLine($"INSERT INTO [CB_ItemMoneyBillSub] ([HInterID], [HEntryID], [HCloseMan], [HEntryCloseDate], [HCloseType], [HRemark],[HSourceInterID], [HSourceEntryID], [HSourceBillNo], [HSourceBillType],[HRelationQty], [HRelationMoney], [HBillNo_bak], [HCostItemID],[HDeptID], [HQty], [HPrice], [HMoney],[HMaterID], [HWaster], [HDate], [HBeginBalance], [HEndBalance], [HTimes]) VALUES (");
- insertSql_sub.AppendLine($"'{HInterID}', '{HEntryID}', '{""}', '{DateTime.Now}', '{"0"}', '{"excel瀵煎叆"}','{"0"}', '{"0"}', '{"0"}', '{"0"}','{item["鍏宠仈鏁伴噺"]}', '{item["鍏宠仈閲戦"]}', '{""}', '{HCostID}', '{HSupplierID}', '{item["鏁伴噺"]}', '{item["鍗曚环"]}', '{item["閲戦"]}','{HMaterID}', '{item["鎹熻�楅噺"]}', '{DateTime.Now}', '{"0"}', '{"0"}', '{"0"}');");
- HEntryID++;
- }
+ // 鑾峰彇缁勪俊鎭�
+ int HSupplierID = supplierDict_sub[firstItem["閮ㄩ棬"]];
+ //int EmpID = EmpDict[firstItem["鑱屽憳"]];
+ int EmpID;
+ string empName = firstItem["鑱屽憳"];
+ if (string.IsNullOrEmpty(empName))
+ EmpID = 0; // 鎴栧叾浠栭粯璁ゅ��
else
- {
- // 鏇存柊璇彞
- updateSql.AppendLine($"UPDATE CB_ItemMoneyBillMain SET HDate = '{currentDate}', HRemark = '{item["澶囨敞"]}', HEmpID = '{EmpID}', HMaterID = '{HMaterID}' , HDeptID = '{HSupplierID}' WHERE HBillNo = '{item["鍗曟嵁鍙�"]}'");
- updateSql_sub.AppendLine($"UPDATE CB_ItemMoneyBillSub SET HDate = '{currentDate}', HQty = '{item["鏁伴噺"]}', HDeptID = '{HSupplierID}', HPrice = '{item["鍗曚环"]}', HRelationQty = '{item["鍏宠仈鏁伴噺"]}', HRelationMoney = '{item["鍏宠仈閲戦"]}', HMoney = '{item["閲戦"]}', HWaster = '{item["鎹熻�楅噺"]}', HMaterID = '{HMaterID}' WHERE HInterID = {HInterID};");
- }
- }
+ EmpID = EmpDict[empName];
+ string ICMO = firstItem["鐢熶骇璁㈠崟鍙�"];
+ int ICMOInterID = !string.IsNullOrEmpty(ICMO) && ICMODict.ContainsKey(ICMO) ? ICMODict[ICMO] : 0;
-
+ // 鐢熸垚涓昏〃ID鍜屽崟鎹彿锛堟瘡缁勭敓鎴愪竴涓級
+ string HBillNo = DBUtility.ClsPub.CreateBillCode("1802", ref DBUtility.ClsPub.sExeReturnInfo, true);
+ long HInterID = DBUtility.ClsPub.CreateBillID("1802", ref DBUtility.ClsPub.sExeReturnInfo);
+
+
+
+
+ DateTime firstDayWithTime = new DateTime(Convert.ToInt32(list[0]["骞翠唤"]), Convert.ToInt32(list[0]["鏈堜唤"]), 1);
+ // 鎻掑叆涓昏〃锛堟瘡缁勬彃鍏ヤ竴娆★級
+ 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}', '{firstDayWithTime}', {list[0]["骞翠唤"]}, {list[0]["鏈堜唤"]}, {"1802"}, '{"1802"}', '{"1"}', '{"0"}', '{"0"}', '{"0"}', '{"excel瀵煎叆"}', '{""}', '{DateTime.Now}', '{""}', '{""}', '{DateTime.Now}', '{user}', '{DateTime.Now}', '{""}', '{DateTime.Now}', '{""}', '{DateTime.Now}', '{"0"}', '{""}', '{DateTime.Now}', '{""}', '{"0"}', '{"0"}', '{""}', '{"0"}', '{EmpID}', '{HSupplierID}', '{ICMOInterID}','{0}','{ICMO}','{""}','{"0"}','{DateTime.Now}','{DateTime.Now}','{"100038"}');");
+
+ // 鎻掑叆瀛愯〃锛堢粍鍐呮瘡涓墿鏂欐彃鍏ヤ竴娆★級
+ int HEntryID = 1;
+ foreach (var item in group)
+ {
+ int HMaterID = materialDict[$"{item["鐗╂枡缂栫爜"]}_{item["鐗╂枡鍚嶇О"]}"];
+ int HCostID = CostDict[item["鎴愭湰椤圭洰"]];
+ int HProcID = ProcDict[item["宸ュ簭"]];
+
+ insertSql_sub.AppendLine($"INSERT INTO [CB_ItemMoneyBillSub] ([HInterID], [HEntryID], [HCloseMan], [HEntryCloseDate], [HCloseType], [HRemark],[HSourceInterID], [HSourceEntryID], [HSourceBillNo], [HSourceBillType],[HRelationQty], [HRelationMoney], [HBillNo_bak], [HCostItemID],[HDeptID], [HQty], [HPrice], [HMoney],[HMaterID], [HWaster], [HDate], [HBeginBalance], [HEndBalance], [HTimes],[HProcID]) VALUES (");
+ 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++;
+ }
+
+
+
+ }
// 鎵ц鎵归噺鎿嶄綔
if (insertSql.Length > 0)
{
oCN.RunProc(insertSql.ToString());
+ }
+
+ if (insertSql_sub.Length > 0)
+ {
oCN.RunProc(insertSql_sub.ToString());
}
-
- if (updateSql.Length > 0)
- {
- oCN.RunProc(updateSql.ToString());
- oCN.RunProc(updateSql_sub.ToString());
- }
-
oCN.Commit();
res.code = "1";
- res.count = 1;
- res.Message = "瀵煎叆鎴愬姛!";
+ res.count = groupedData.Count;
+ res.Message = $"瀵煎叆鎴愬姛! 鍏卞鐞唟groupedData.Count}缁勬暟鎹�";
res.data = null;
return res;
}
--
Gitblit v1.9.1