From 319d80c37022dd5847e1f9243d6137a479a2f5af Mon Sep 17 00:00:00 2001
From: yusijie <ysj@hz-kingdee.com>
Date: 星期四, 21 三月 2024 10:26:12 +0800
Subject: [PATCH] excel导入工序工价
---
WebAPI/Controllers/基础资料/工资基础资料/Gy_ProcPriceController.cs | 414 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 414 insertions(+), 0 deletions(-)
diff --git "a/WebAPI/Controllers/\345\237\272\347\241\200\350\265\204\346\226\231/\345\267\245\350\265\204\345\237\272\347\241\200\350\265\204\346\226\231/Gy_ProcPriceController.cs" "b/WebAPI/Controllers/\345\237\272\347\241\200\350\265\204\346\226\231/\345\267\245\350\265\204\345\237\272\347\241\200\350\265\204\346\226\231/Gy_ProcPriceController.cs"
index ee5d53c..f0ac8e4 100644
--- "a/WebAPI/Controllers/\345\237\272\347\241\200\350\265\204\346\226\231/\345\267\245\350\265\204\345\237\272\347\241\200\350\265\204\346\226\231/Gy_ProcPriceController.cs"
+++ "b/WebAPI/Controllers/\345\237\272\347\241\200\350\265\204\346\226\231/\345\267\245\350\265\204\345\237\272\347\241\200\350\265\204\346\226\231/Gy_ProcPriceController.cs"
@@ -6,7 +6,10 @@
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
+using System.IO;
+using System.Web;
using System.Web.Http;
+using WebAPI.Controllers.SCGL.鏃ヨ鍒掔鐞�;
using WebAPI.Models;
namespace WebAPI.Controllers
{
@@ -463,5 +466,416 @@
}
}
#endregion
+
+ #region 宸ュ簭宸ヤ环 鏂囦欢涓婁紶
+ [Route("Gy_ProcPrice/Gy_ProcPrice_Excel")]
+ [HttpPost]
+ public object Gy_ProcPrice_Excel()
+ {
+ try
+ {
+ //鑾峰彇鏂囦欢鍚嶇О
+ var file = HttpContext.Current.Request.Files[0];
+ //鑾峰彇鏂囦欢鐗╃悊璺緞
+ string ExcelPath = HttpContext.Current.Server.MapPath("~/" + file.FileName);
+ //淇濆瓨鏂囦欢
+ file.SaveAs(ExcelPath);
+
+ NpoiHelper np = new NpoiHelper();
+ DataSet ExcelDs = np.ReadExcel(ExcelPath, 1, 1, "0");
+
+ //鍒犻櫎鏂囦欢
+ File.Delete(ExcelPath);
+
+ //鍒涘缓涓存椂琛�
+ DataTable tb2 = new DataTable("dt2");
+
+ //娣诲姞鍒楀悕
+ for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++)
+ {
+ tb2.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString());
+ }
+
+ //妯℃澘缂哄皯鍒� 浣嗛渶瑕佷粠鏁版嵁搴撲腑鏌ヨ鍑烘潵鏄剧ず鍦ㄩ〉闈㈢殑瀛楁
+ tb2.Columns.Add("HMaterID", typeof(Int32));//鐗╂枡ID
+ tb2.Columns.Add("HProcID", typeof(Int32));//宸ュ簭ID
+ tb2.Columns.Add("HSourceID", typeof(Int32));//鐢熶骇璧勬簮ID
+ tb2.Columns.Add("HWorkCenterID", typeof(Int32));//宸ヤ綔涓績ID
+
+ //娣诲姞鏁版嵁
+ for (int i = 1; i < ExcelDs.Tables[0].Rows.Count; i++)
+ {
+ DataRow row = tb2.NewRow();
+ for (int j = 0; j < ExcelDs.Tables[0].Columns.Count; j++)
+ {
+ row[j] = ExcelDs.Tables[0].Rows[i][j].ToString();
+ }
+ //濡傛灉琛ㄦ牸绗琲琛岀殑绗竴鍒椾负绌猴紝鍒欏垽鏂负杩欎竴琛岀殑鏁版嵁涓虹┖锛岃烦鍑哄惊鐜苟涓斾笉鎶婃暟鎹啓鍏� tb2
+ if (ExcelDs.Tables[0].Rows[i][0].ToString() == "")
+ {
+ continue;
+ }
+ else
+ {
+ tb2.Rows.Add(row);
+ }
+ }
+
+
+ var error = "";
+
+ //鏌ヨ宸ュ簭宸ヤ环娌℃湁鐨勫垪
+ if (!tb2.Columns.Contains("鐗╂枡浠ｇ爜"))
+ error += "娌℃湁鎵惧埌銆愮墿鏂欎唬鐮併�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鐗╂枡鍚嶇О"))
+ error += "娌℃湁鎵惧埌銆愮墿鏂欏悕绉般�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("瑙勬牸鍨嬪彿"))
+ error += "娌℃湁鎵惧埌銆愯鏍煎瀷鍙枫�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("宸ュ簭浠ｇ爜"))
+ error += "娌℃湁鎵惧埌銆愬伐搴忎唬鐮併�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("宸ュ簭鍚嶇О"))
+ error += "娌℃湁鎵惧埌銆愬伐搴忓悕绉般�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鐢熶骇璧勬簮浠ｇ爜"))
+ error += "娌℃湁鎵惧埌銆愮敓浜ц祫婧愪唬鐮併�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鐢熶骇璧勬簮鍚嶇О"))
+ error += "娌℃湁鎵惧埌銆愮敓浜ц祫婧愬悕绉般�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("宸ヤ綔涓績浠ｇ爜"))
+ error += "娌℃湁鎵惧埌銆愬伐浣滀腑蹇冧唬鐮併�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("宸ヤ綔涓績鍚嶇О"))
+ error += "娌℃湁鎵惧埌銆愬伐浣滀腑蹇冨悕绉般�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鍗曚环"))
+ error += "娌℃湁鎵惧埌銆愬崟浠枫�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("灏忔椂浜ц兘"))
+ error += "娌℃湁鎵惧埌銆愬皬鏃朵骇鑳姐�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鏍囧噯浜哄姏"))
+ error += "娌℃湁鎵惧埌銆愭爣鍑嗕汉鍔涖�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鎹㈢嚎鏃堕棿"))
+ error += "娌℃湁鎵惧埌銆愭崲绾挎椂闂淬�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鎹㈢嚎琛ヨ创"))
+ error += "娌℃湁鎵惧埌銆愭崲绾胯ˉ璐淬�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("宸ヨ壓璺嚎娴佹按鍙�"))
+ error += "娌℃湁鎵惧埌銆愬伐鑹鸿矾绾挎祦姘村彿銆戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鍗曚欢鍔犲伐鏃堕棿"))
+ error += "娌℃湁鎵惧埌銆愬崟浠跺姞宸ユ椂闂淬�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("妯″叿鏁伴噺"))
+ error += "娌℃湁鎵惧埌銆愭ā鍏锋暟閲忋�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鎹㈡ā琛ヨ创"))
+ error += "娌℃湁鎵惧埌銆愭崲妯¤ˉ璐淬�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鍗曚欢鍐叉娆℃暟"))
+ error += "娌℃湁鎵惧埌銆愬崟浠跺啿娆℃鏁般�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("寮�濮嬫棩鏈�"))
+ error += "娌℃湁鎵惧埌銆愬紑濮嬫棩鏈熴�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("缁撴潫鏃ユ湡"))
+ error += "娌℃湁鎵惧埌銆愮粨鏉熸棩鏈熴�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("鎴愭湰蹇呴��"))
+ error += "娌℃湁鎵惧埌銆愭垚鏈繀閫夈�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("娴佽浆榛樿"))
+ error += "娌℃湁鎵惧埌銆愭祦杞粯璁ゃ�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("宸ヨ祫榛樿"))
+ error += "娌℃湁鎵惧埌銆愬伐璧勯粯璁ゃ�戠殑鏍囬,";
+
+ if (!tb2.Columns.Contains("澶囨敞"))
+ error += "娌℃湁鎵惧埌銆愬娉ㄣ�戠殑鏍囬,";
+
+ if (error.Length > 0)
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = $"Excel妯℃澘瀛樺湪閿欒,{error}\r\n";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+
+ for (int i = 0; i <= tb2.Rows.Count - 1; i++)
+ {
+ string HMaterNumber = "";
+ string HMaterName = "";
+ string HMaterModel = "";
+ string HProcNumber = "";
+ string HProcName = "";
+ string HSourceNumber = "";
+ string HSourceName = "";
+ decimal HPrice = 0;
+ string HWorkCenterNumber = "";
+ string HWorkCenterName = "";
+ decimal HWorkQty = 0;
+ decimal HStdEmp = 0;
+ decimal HChangeTimes = 0;
+ decimal HChangeMoney = 0;
+ Int64 HProcNo = 0;
+ decimal HPieceTimes = 0;
+ decimal HMouldQty = 0;
+ decimal HChangeMouldMoney = 0;
+ decimal HRushQty = 0;
+ string HBeginDate = System.DateTime.Now.ToString("G");
+ string HEndDate = System.DateTime.Now.ToString("G");
+ Int64 HCostFlag = 0;
+ Int64 HFlowFlag = 0;
+ Int64 HPayFlag = 0;
+ string HRemark = "";
+
+ HMaterNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["鐗╂枡浠ｇ爜"].ToString());
+ HMaterName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["鐗╂枡鍚嶇О"].ToString());
+ HMaterModel = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["瑙勬牸鍨嬪彿"].ToString());
+ HProcNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["宸ュ簭浠ｇ爜"].ToString());
+ HProcName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["宸ュ簭鍚嶇О"].ToString());
+ HSourceNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["鐢熶骇璧勬簮浠ｇ爜"].ToString());
+ HSourceName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["鐢熶骇璧勬簮鍚嶇О"].ToString());
+ HPrice = DBUtility.ClsPub.isLong(tb2.Rows[i]["鍗曚环"].ToString());
+ HWorkCenterNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["宸ヤ綔涓績浠ｇ爜"].ToString());
+ HWorkCenterName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["宸ヤ綔涓績鍚嶇О"].ToString());
+ HWorkQty = DBUtility.ClsPub.isLong(tb2.Rows[i]["灏忔椂浜ц兘"].ToString());
+ HStdEmp = DBUtility.ClsPub.isLong(tb2.Rows[i]["鏍囧噯浜哄姏"].ToString());
+ HChangeTimes = DBUtility.ClsPub.isLong(tb2.Rows[i]["鎹㈢嚎鏃堕棿"].ToString());
+ HChangeMoney = DBUtility.ClsPub.isLong(tb2.Rows[i]["鎹㈢嚎琛ヨ创"].ToString());
+ HProcNo = DBUtility.ClsPub.isLong(tb2.Rows[i]["宸ヨ壓璺嚎娴佹按鍙�"].ToString());
+ HPieceTimes = DBUtility.ClsPub.isLong(tb2.Rows[i]["鍗曚欢鍔犲伐鏃堕棿"].ToString());
+ HMouldQty = DBUtility.ClsPub.isLong(tb2.Rows[i]["妯″叿鏁伴噺"].ToString());
+ HChangeMouldMoney = DBUtility.ClsPub.isLong(tb2.Rows[i]["鎹㈡ā琛ヨ创"].ToString());
+ HRushQty = DBUtility.ClsPub.isLong(tb2.Rows[i]["鍗曚欢鍐叉娆℃暟"].ToString());
+ HBeginDate = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["寮�濮嬫棩鏈�"].ToString());
+ HEndDate = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["缁撴潫鏃ユ湡"].ToString());
+ HCostFlag = DBUtility.ClsPub.isLong(tb2.Rows[i]["鎴愭湰蹇呴��"].ToString());
+ HFlowFlag = DBUtility.ClsPub.isLong(tb2.Rows[i]["娴佽浆榛樿"].ToString());
+ HPayFlag = DBUtility.ClsPub.isLong(tb2.Rows[i]["宸ヨ祫榛樿"].ToString());
+ HRemark = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["澶囨敞"].ToString());
+
+ //妫�鏌ヨ〃鏍兼暟鎹�
+ int index = i + 1;
+
+ if (HMaterNumber != "" && HMaterName != "")
+ {
+
+
+ //鏌ヨ鐗╂枡
+ ds = oCN.RunProcReturn("select * from Gy_Material where HNumber='" + HMaterNumber + "' and HName='" + HMaterName + "' and HModel ='" + HMaterModel + "'", "Gy_Material");
+
+ if (ds.Tables[0].Rows.Count == 0)
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "绗�" + index + "琛�,鐗╂枡涓嶅瓨鍦紒";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+ else
+ {
+ tb2.Rows[i]["HMaterID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
+ }
+
+ //鏌ヨ宸ュ簭
+ ds = oCN.RunProcReturn("select * from Gy_Process where HNumber='" + HProcNumber + "'", "Gy_Process");
+
+ if (ds.Tables[0].Rows.Count == 0)
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "绗�" + index + "琛�,宸ュ簭涓嶅瓨鍦紒";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+ else
+ {
+ tb2.Rows[i]["HProcID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
+ }
+
+ //鏌ヨ鐢熶骇璧勬簮
+ ds = oCN.RunProcReturn("select * from Gy_Source where HNumber='" + HSourceNumber + "'" , "Gy_Source");
+
+ if (ds.Tables[0].Rows.Count == 0)
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "绗�" + index + "琛�,鐢熶骇璧勬簮涓嶅瓨鍦紒";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+ else
+ {
+ tb2.Rows[i]["HSourceID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
+ }
+
+ //鏌ヨ宸ヤ綔涓績
+ ds = oCN.RunProcReturn("select * from Gy_WorkCenter where HNumber='" + HWorkCenterNumber + "'", "Gy_WorkCenter"); //
+
+ if (ds.Tables[0].Rows.Count == 0)
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "绗�" + index + "琛�,宸ヤ綔涓績涓嶅瓨鍦紒";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+ else
+ {
+ tb2.Rows[i]["HWorkCenterID"] = ds.Tables[0].Rows[0]["HItemID"].ToString();
+ }
+
+ //宸ュ簭浠ｇ爜
+ if (HProcNumber == "")
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "绗�" + index + "琛�,宸ュ簭浠ｇ爜涓嶈兘涓虹┖锛�";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+
+ //鐢熶骇璧勬簮鍚嶇О
+ if (HSourceName == "")
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "绗�" + index + "琛�,鐢熶骇璧勬簮鍚嶇О涓嶈兘涓虹┖锛�";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+
+ //宸ヤ綔涓績浠ｇ爜
+ if (HWorkCenterNumber == "")
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "绗�" + index + "琛�,宸ヤ綔涓績浠ｇ爜涓嶈兘涓虹┖锛�";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+ }
+ else
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "绗�" + index + "琛�,鐗╂枡淇℃伅涓嶅叏锛堢墿鏂欎唬鐮侊紝鐗╂枡鍚嶇О锛�";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+ }
+
+ objJsonResult.code = "1";
+ objJsonResult.count = 1;
+ objJsonResult.Message = error;
+ objJsonResult.data = tb2;
+ return objJsonResult;
+ }
+ catch (Exception e)
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "Exception锛�" + e.ToString();
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+ }
+ #endregion
+
+ #region 宸ュ簭宸ヤ环 瀵煎叆(淇濆瓨)
+ [Route("Gy_ProcPrice/Gy_ProcPrice_btnSave")]
+ [HttpPost]
+ public object Gy_ProcPrice_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();
+ try
+ {
+ if (!DBUtility.ClsPub.Security_Log("Gy_ProcPriceList_Edit", 1, false, user))
+ {
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "鏃犱繚瀛樻潈闄愶紒";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+
+ 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);
+ }
+
+ oCN.BeginTran();
+ int i = 1;
+ foreach (Dictionary<string, string> item in list)
+ {
+ string HProcID = item["HProcID"].ToString();//宸ュ簭
+ string HMaterID = item["HMaterID"].ToString();//鐗╂枡
+ string HSourceID = item["HSourceID"].ToString();//鐢熶骇璧勬簮
+ string HWorkCenterID = item["HWorkCenterID"].ToString();//宸ヤ綔涓績
+ string HPrice = item["鍗曚环"].ToString();
+ string HWorkQty = item["灏忔椂浜ц兘"].ToString();
+ string HStdEmp = item["鏍囧噯浜哄姏"].ToString();
+ string HChangeTimes = item["鎹㈢嚎鏃堕棿"].ToString();
+ string HChangeMoney = item["鎹㈢嚎琛ヨ创"].ToString();
+ string HProcNo = item["宸ヨ壓璺嚎娴佹按鍙�"].ToString();
+ string HPieceTimes = item["鍗曚欢鍔犲伐鏃堕棿"].ToString();
+ string HMouldQty = item["妯″叿鏁伴噺"].ToString();
+ string HChangeMouldMoney = item["鎹㈡ā琛ヨ创"].ToString();
+ string HRushQty = item["鍗曚欢鍐叉娆℃暟"].ToString();
+ string HCostFlag = item["鎴愭湰蹇呴��"].ToString();
+ string HFlowFlag = item["娴佽浆榛樿"].ToString();
+ string HPayFlag = item["宸ヨ祫榛樿"].ToString();
+ string HRemark = item["澶囨敞"].ToString();
+ string HBeginDate = item["寮�濮嬫棩鏈�"].ToString();
+ string HEndDate = item["缁撴潫鏃ユ湡"].ToString();
+
+ string sql = "insert into Gy_ProcPrice(HMaterID,HProcID,HSourceID,HBeginDate,HEndDate,HCostFlag,HFlowFlag,HPayFlag,HDeptID,HWorkCenterID,HWorkQty,HStdEmp,HChangeTimes,HChangeMoney,HProcNo,HPieceTimes,HMouldQty,HChangeMouldMoney,HRushQty,HPrice,HStopflag,HRemark,HMaker,HMakeDate)" +
+ $"values({HMaterID}, {HProcID},{HSourceID}, '{HBeginDate}', '{HEndDate}', '{(HCostFlag == "鏄�" ? 1 : 0)}', {(HFlowFlag == "鏄�" ? 1 : 0)},'{(HPayFlag == "鏄�" ? 1 : 0)}',{0},{HWorkCenterID},{HWorkQty},{HStdEmp},{HChangeTimes},{HChangeMoney},{HProcNo},{HPieceTimes},{HMouldQty},{HChangeMouldMoney},{HRushQty},{HPrice},{0},'{HRemark}','{user}','{System.DateTime.Now.ToString("G")}')";
+ oCN.RunProc(sql);
+
+ i++;
+ }
+
+ oCN.Commit();
+
+ objJsonResult.code = "1";
+ objJsonResult.count = 1;
+ objJsonResult.Message = "瀵煎叆鎴愬姛!";
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+ catch (Exception e)
+ {
+ LogService.Write(e);
+ objJsonResult.code = "0";
+ objJsonResult.count = 0;
+ objJsonResult.Message = "Exception锛�" + e.ToString();
+ objJsonResult.data = null;
+ return objJsonResult;
+ }
+ }
+ #endregion
+
}
}
\ No newline at end of file
--
Gitblit v1.9.1