From 8ecf62e4978a9bf751996b8964140bcecbfb0fc9 Mon Sep 17 00:00:00 2001
From: llj <132905093+newwwwwwtree@users.noreply.github.com>
Date: 星期一, 09 二月 2026 10:59:22 +0800
Subject: [PATCH] 成本费用单文件上传模块
---
WebAPI/Controllers/成本管理/CB_ItemMoneyBillController.cs | 370 ++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 370 insertions(+), 0 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 ffd37ef..8f9774e 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"
@@ -6,8 +6,13 @@
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
+using System.Web;
using System.Web.Http;
using WebAPI.Models;
+using WebAPI.Controllers.SCGL.鏃ヨ鍒掔鐞�;
+using System.IO;
+using System.Linq;
+using System.Text;
namespace WebAPI.Controllers
{
@@ -950,5 +955,370 @@
#endregion
+
+ #region 鏂囦欢涓婁紶
+ [Route("CB_ItemMoneyBillController/CB_ItemMoneyBill_Excel")]
+ [HttpPost]
+ public json CB_ItemMoneyBill_Excel()
+ {
+ json res = new json();
+ try
+ {
+ //鑾峰彇鏂囦欢鍚嶇О
+ var file = HttpContext.Current.Request.Files[0];
+ //鑾峰彇鏂囦欢鐗╃悊璺緞
+ string ExcelPath = HttpContext.Current.Server.MapPath("~/" + file.FileName);
+ //淇濆瓨鏂囦欢
+ file.SaveAs(ExcelPath);
+
+ SCGL.鏃ヨ鍒掔鐞�.NpoiHelper np = new NpoiHelper();
+ DataSet ExcelDs = np.ReadExcel(ExcelPath, 1, 1, "0");
+
+ //鍒犻櫎鏂囦欢
+ File.Delete(ExcelPath);
+
+ //鍒涘缓涓存椂琛�
+ DataTable provisional = new DataTable("dt2");
+
+ //娣诲姞鍒楀悕
+ for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++)
+ {
+ provisional.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString());
+ }
+ provisional.Columns.Add("鍗曟嵁鍙�", typeof(string));
+
+ //娣诲姞鏁版嵁
+ for (int i = 1; i < ExcelDs.Tables[0].Rows.Count; i++)
+ {
+ DataRow row = provisional.NewRow();
+ for (int j = 0; j < ExcelDs.Tables[0].Columns.Count; j++)
+ {
+ row[j] = ExcelDs.Tables[0].Rows[i][j].ToString();
+ }
+ provisional.Rows.Add(row);
+ }
+
+ //鍒ゆ柇鍒�
+ string error = JudgmentColumns(provisional);
+ if (error.Length > 0)
+ {
+ res.code = "0";
+ res.count = 0;
+ res.Message = $"Excel妯℃澘瀛樺湪閿欒,{error}\r\n";
+ res.data = null;
+ return res;
+ }
+
+ 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 HDepNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["閮ㄩ棬浠ｇ爜"].ToString());
+ string HDepName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["閮ㄩ棬"].ToString());
+
+ string HEmpNumber = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鑱屽憳浠ｇ爜"].ToString());
+ string HEmpName = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鑱屽憳"].ToString());
+
+ string HICMOBillNo = 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 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());
+
+ string HQty = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["鏁伴噺"].ToString());
+ 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 remark = DBUtility.ClsPub.isStrNull(provisional.Rows[i]["澶囨敞"].ToString());
+
+ //鑾峰彇鐪熷疄琛屾暟
+ int line = i + 1;
+ }
+
+ res.code = "1";
+ res.count = 1;
+ res.Message = error;
+ res.data = provisional;
+ return res;
+ }
+ catch (Exception e)
+ {
+ res.code = "0";
+ res.count = 0;
+ res.Message = "Exception锛�" + e.ToString();
+ res.data = null;
+ return res;
+ }
+ }
+
+ /// <summary>
+ /// 鍒ゆ柇鍒�
+ /// </summary>
+ /// <param name="provisional"></param>
+ /// <returns></returns>
+ private static string JudgmentColumns(DataTable provisional)
+ {
+ var error = "";
+
+ //鏌ヨ娌℃湁鐨勫垪
+ if (!provisional.Columns.Contains("瀛愯〃鍐呯爜"))
+ error += "娌℃湁鎵惧埌銆愬瓙琛ㄥ唴鐮併�戠殑鏍囬,";
+
+ if (!provisional.Columns.Contains("鐗╂枡缂栫爜"))
+ error += "娌℃湁鎵惧埌銆愮墿鏂欑紪鐮併�戠殑鏍囬,";
+ return error;
+ }
+ #endregion
+
+ #region 涓婁紶淇濆瓨
+ [Route("CB_ItemMoneyBillController/Excel_btnSave")]
+ [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);
+ string msg2 = sArray[0].ToString();
+ string user = sArray[1].ToString();
+ string organ = sArray[2].ToString();
+ json res = new json();
+
+ try
+ {
+ oCN.BeginTran();
+ List<object> Excel = Newtonsoft.Json.JsonConvert.DeserializeObject<List<object>>(msg2);
+ List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
+
+ foreach (JObject item in Excel)
+ {
+ Dictionary<string, string> dic = new Dictionary<string, string>();
+ foreach (var itm in item.Properties())
+ {
+ dic.Add(itm.Name, itm.Value.ToString());
+ }
+ list.Add(dic);
+ }
+
+ // 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 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 CostNumbers = list.Select(x => x["鎴愭湰椤圭洰浠ｇ爜"].ToString()).Distinct().ToList();
+ var CostNames = 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)
+ {
+ 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");
+ Dictionary<string, int> supplierDict_sub = new Dictionary<string, int>();
+ foreach (DataRow row in supData_sub.Tables[0].Rows)
+ {
+ supplierDict_sub[row["HName"].ToString()] = (int)row["HItemID"];
+ }
+
+ // 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("'", "''")}'"))})";
+ DataSet materData = oCN.RunProcReturn(materialQuery, "Gy_Material");
+ Dictionary<string, int> materialDict = new Dictionary<string, int>();
+ foreach (DataRow row in materData.Tables[0].Rows)
+ {
+ string key = $"{row["HNumber"]}_{row["HName"]}";
+ materialDict[key] = (int)row["HItemID"];
+ }
+
+ // 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("'", "''")}'"))})";
+ DataSet EmpData = oCN.RunProcReturn(EmployeeQuery, "Gy_Employee");
+ Dictionary<string, int> EmpDict = new Dictionary<string, int>();
+ foreach (DataRow row in EmpData.Tables[0].Rows)
+ {
+ string key = $"{row["HName"].ToString()}";
+ EmpDict[key] = (int)row["HItemID"];
+ }
+
+ // 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");
+ 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"];
+ }
+
+
+ // 4. 鏌ヨ鐢ㄦ埛淇℃伅
+ DataSet emp = oCN.RunProcReturn($"SELECT Czybm FROM Gy_Czygl WHERE Czymc = '{user.Replace("'", "''")}'", "Gy_Czygl");
+ if (emp.Tables[0].Rows.Count == 0)
+ {
+ res.code = "0";
+ res.count = 0;
+ res.Message = "鐢ㄦ埛涓嶅瓨鍦紒";
+ res.data = null;
+ return res;
+ }
+ string HUserID = emp.Tables[0].Rows[0]["Czybm"].ToString();
+
+ // 5. 妫�鏌ョ己澶辩殑閮ㄩ棬鍜岀墿鏂欙紝骞惰褰曡鍙�
+ List<string> missingSupplierMessages = new List<string>();
+ List<string> missingMaterialMessages = new List<string>();
+
+ // 閬嶅巻姣忎竴琛屾暟鎹紝璁板綍琛屽彿锛堜粠1寮�濮嬶級
+ for (int i = 0; i < list.Count; i++)
+ {
+ var item = list[i];
+ int lineNumber = i + 1; // 琛屽彿浠�1寮�濮�
+
+ string supplier = item["閮ㄩ棬"];
+ string materialNumber = item["鐗╂枡缂栫爜"];
+ string materialName = item["鐗╂枡鍚嶇О"];
+ string materialKey = $"{materialNumber}_{materialName}";
+
+ // 妫�鏌ヤ緵搴斿晢
+ if (!supplierDict.ContainsKey(supplier))
+ {
+ missingSupplierMessages.Add($"绗瑊lineNumber}琛岀殑閮ㄩ棬銆恵supplier}銆�");
+ }
+
+ // 妫�鏌ョ墿鏂�
+ if (!materialDict.ContainsKey(materialKey))
+ {
+ missingMaterialMessages.Add($"绗瑊lineNumber}琛岀殑鐗╂枡銆恵materialName}(缂栫爜:{materialNumber})銆�");
+ }
+ }
+
+ StringBuilder errorMessage = new StringBuilder();
+
+ if (missingSupplierMessages.Any())
+ {
+ errorMessage.Append("浠ヤ笅閮ㄩ棬鏈淮鎶わ細");
+ errorMessage.Append(string.Join("銆�", missingSupplierMessages));
+ errorMessage.Append("锛�");
+ }
+
+ if (missingMaterialMessages.Any())
+ {
+ errorMessage.Append("浠ヤ笅鐗╂枡鏈淮鎶わ細");
+ errorMessage.Append(string.Join("銆�", missingMaterialMessages));
+ errorMessage.Append("锛�");
+ }
+
+ if (errorMessage.Length > 0)
+ {
+ res.code = "0";
+ res.count = 0;
+ res.Message = errorMessage.ToString();
+ res.data = null;
+ 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"]}");
+ }
+
+ 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)
+ {
+ 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"}');");
+
+ 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++;
+ }
+ 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};");
+ }
+ }
+
+
+
+ // 鎵ц鎵归噺鎿嶄綔
+ if (insertSql.Length > 0)
+ {
+ oCN.RunProc(insertSql.ToString());
+ 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.data = null;
+ return res;
+ }
+ catch (Exception e)
+ {
+ //oCN.Rollback();
+ LogService.Write(e);
+ res.code = "0";
+ res.count = 0;
+ res.Message = "Exception锛�" + e.ToString();
+ res.data = null;
+ return res;
+ }
+ }
+ #endregion
+
}
}
\ No newline at end of file
--
Gitblit v1.9.1