| | |
| | | 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 |
| | | { |
| | |
| | | } |
| | | } |
| | | #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(); |
| | | } |
| | | //å¦æè¡¨æ ¼ç¬¬iè¡ç第ä¸å为空ï¼åå¤æä¸ºè¿ä¸è¡çæ°æ®ä¸ºç©ºï¼è·³åºå¾ªç¯å¹¶ä¸ä¸ææ°æ®åå
¥ 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 |
| | | |
| | | } |
| | | } |