From ba76af92c90c52823b8140ac3be26fbf69d467bd Mon Sep 17 00:00:00 2001 From: duhe <226547893@qq.com> Date: 星期三, 18 十二月 2024 10:04:10 +0800 Subject: [PATCH] 1 --- WebAPI/Controllers/CJGL/Sc_WorkBeginDotCheckBillController.cs | 482 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 481 insertions(+), 1 deletions(-) diff --git a/WebAPI/Controllers/CJGL/Sc_WorkBeginDotCheckBillController.cs b/WebAPI/Controllers/CJGL/Sc_WorkBeginDotCheckBillController.cs index 1315f90..79861b9 100644 --- a/WebAPI/Controllers/CJGL/Sc_WorkBeginDotCheckBillController.cs +++ b/WebAPI/Controllers/CJGL/Sc_WorkBeginDotCheckBillController.cs @@ -10,7 +10,9 @@ using System.Web.Http; using System.Windows.Forms; using WebAPI.Models; - +using WebAPI.Controllers.SCGL.鏃ヨ鍒掔鐞�; +using System.IO; +using SyntacticSugar.constant; namespace WebAPI.Controllers.CJGL { public class Sc_WorkBeginDotCheckBillController : ApiController @@ -1656,5 +1658,483 @@ } } #endregion + + #region 璁惧鍚姩鐐规娓呭崟 鏂囦欢瀵煎叆淇濆瓨 + #region 璁惧鍚姩鐐规娓呭崟 鏂囦欢涓婁紶 + [Route("Sc_WorkBeginDotCheckBill/Sc_WorkBeginDotCheckBill_Excel")] + [HttpPost] + public object Sc_WorkBeginDotCheckBill_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("HOrgID", typeof(Int32));//缁勭粐ID + tb2.Columns.Add("HMaterID", typeof(Int32));//鐗╂枡ID + tb2.Columns.Add("HProcID", typeof(Int32));//宸ュ簭ID + tb2.Columns.Add("HSourceID", typeof(Int32));//鐢熶骇璧勬簮ID + tb2.Columns.Add("HCheckNoteItemID", typeof(Int32));//妫�鏌ラ」鐩甀D + tb2.Columns.Add("HCheckPostID", 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.Rows.Add(row); + } + + + var error = ""; + + //鏌ヨ娌℃湁鐨勫垪 + if (!tb2.Columns.Contains("缁勭粐浠g爜")) + error += "娌℃湁鎵惧埌銆愮粍缁囦唬鐮併�戠殑鏍囬,"; + + if (!tb2.Columns.Contains("缁勭粐鍚嶇О")) + error += "娌℃湁鎵惧埌銆愮粍缁囧悕绉般�戠殑鏍囬,"; + + if (!tb2.Columns.Contains("鍗曟嵁鍙�")) + error += "娌℃湁鎵惧埌銆愬崟鎹彿銆戠殑鏍囬,"; + + if (!tb2.Columns.Contains("鐗╂枡浠g爜")) + error += "娌℃湁鎵惧埌銆愮墿鏂欎唬鐮併�戠殑鏍囬,"; + + if (!tb2.Columns.Contains("鐗╂枡鍚嶇О")) + error += "娌℃湁鎵惧埌銆愮墿鏂欏悕绉般�戠殑鏍囬,"; + + if (!tb2.Columns.Contains("宸ュ簭浠g爜")) + error += "娌℃湁鎵惧埌銆愬伐搴忎唬鐮併�戠殑鏍囬,"; + + if (!tb2.Columns.Contains("宸ュ簭鍚嶇О")) + error += "娌℃湁鎵惧埌銆愬伐搴忓悕绉般�戠殑鏍囬,"; + + if (!tb2.Columns.Contains("鐢熶骇璧勬簮浠g爜")) + 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("宀椾綅浠g爜")) + error += "娌℃湁鎵惧埌銆愬矖浣嶄唬鐮併�戠殑鏍囬,"; + + if (!tb2.Columns.Contains("宀椾綅鍚嶇О")) + error += "娌℃湁鎵惧埌銆愬矖浣嶅悕绉般�戠殑鏍囬,"; + + if (!tb2.Columns.Contains("琛ㄤ綋澶囨敞")) + error += "娌℃湁鎵惧埌銆愯〃浣撳娉ㄣ�戠殑鏍囬,"; + + if (error.Length > 0) + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = $"Excel妯℃澘瀛樺湪閿欒,{error}\r\n"; + objJsonResult.data = null; + return objJsonResult; + } + + for (int i = 0; i <= tb2.Rows.Count - 1; i++) + { + string HBillNo = ""; + string HORGNumber = ""; + string HORGName = ""; + string HMaterName = ""; + string HMaterNumber = ""; + string HProcNumber = ""; + string HProcName = ""; + string HSourceNumber = ""; + string HSourceName = ""; + string HCheckNoteItemNumber = ""; + string HCheckNoteItemName = ""; + string HCheckPostNumber = ""; + string HCheckPostName = ""; + + HBillNo = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["鍗曟嵁鍙�"].ToString()); + HORGNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["缁勭粐浠g爜"].ToString()); + HORGName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["缁勭粐鍚嶇О"].ToString()); + HMaterName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["鐗╂枡鍚嶇О"].ToString()); + HMaterNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["鐗╂枡浠g爜"].ToString()); + HProcName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["宸ュ簭鍚嶇О"].ToString()); + HProcNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["宸ュ簭浠g爜"].ToString()); + HSourceName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["鐢熶骇璧勬簮鍚嶇О"].ToString()); + HSourceNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["鐢熶骇璧勬簮浠g爜"].ToString()); + HCheckNoteItemName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["妫�鏌ラ」鐩悕绉�"].ToString()); + HCheckNoteItemNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["妫�鏌ラ」鐩唬鐮�"].ToString()); + HCheckPostName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["宀椾綅鍚嶇О"].ToString()); + HCheckPostNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["宀椾綅浠g爜"].ToString()); + + //妫�鏌� + int index = i + 1; + //缁勭粐 + if (HORGNumber != "") + { + //鏌ヨ缁勭粐 + ds = oCN.RunProcReturn("select * from Xt_ORGANIZATIONS where HNumber='" + HORGNumber + "' and Hname='" + HORGName + "'", "Xt_ORGANIZATIONS"); + + if (ds.Tables[0].Rows.Count == 0) + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,缁勭粐涓嶅瓨鍦紒"; + objJsonResult.data = null; + return objJsonResult; + } + else + { + tb2.Rows[i]["HOrgID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); + } + string HORGid = ds.Tables[0].Rows[0]["HItemID"].ToString(); + } + else + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,缁勭粐浠g爜涓虹┖"; + objJsonResult.data = null; + return objJsonResult; + } + //鐗╂枡浠g爜 + if (HMaterNumber != "") + { + //鏌ヨ鐗╂枡 + ds = oCN.RunProcReturn("select * from Gy_Material where HNumber='" + HMaterNumber + "' and Hname='" + HMaterName + "'", "Gy_Maintain"); + + if (ds.Tables[0].Rows.Count == 0) + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,鐗╂枡涓嶅瓨鍦紒"; + objJsonResult.data = null; + return objJsonResult; + } + else + { + tb2.Rows[i]["HMaterID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); + } + } + else + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,鐗╂枡浠g爜涓虹┖"; + objJsonResult.data = null; + return objJsonResult; + } + //宸ュ簭浠g爜 + if (HProcNumber != "") + { + //鏌ヨ宸ュ簭 + ds = oCN.RunProcReturn("select * from Gy_Process where HNumber='" + HProcNumber + "' and Hname='" + HProcName + "'", "Gy_Process"); + + if (ds.Tables[0].Rows.Count == 0) + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,宸ュ簭涓嶅瓨鍦紒"; + objJsonResult.data = null; + return objJsonResult; + } + else + { + tb2.Rows[i]["HProcID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); + } + } + else + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,宸ュ簭浠g爜涓虹┖"; + objJsonResult.data = null; + return objJsonResult; + } + //鐢熶骇璧勬簮 + if (HSourceNumber != "") + { + //鏌ヨ宸ュ簭 + ds = oCN.RunProcReturn("select * from Gy_Source where HNumber='" + HSourceNumber + "' and Hname='" + HSourceName + "'", "Gy_Source"); + + if (ds.Tables[0].Rows.Count == 0) + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,鐢熶骇璧勬簮涓嶅瓨鍦紒"; + objJsonResult.data = null; + return objJsonResult; + } + else + { + tb2.Rows[i]["HSourceID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); + } + } + //妫�鏌ラ」鐩唬鐮� + if (HCheckNoteItemNumber != "") + { + //鏌ヨ鐗╂枡 + ds = oCN.RunProcReturn("select * from Gy_CheckNoteItem where HNumber='" + HCheckNoteItemNumber + "' and Hname='" + HCheckNoteItemName + "'", "Gy_CheckNoteItem"); + + if (ds.Tables[0].Rows.Count == 0) + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,妫�鏌ラ」鐩笉瀛樺湪锛�"; + objJsonResult.data = null; + return objJsonResult; + } + else + { + tb2.Rows[i]["HCheckNoteItemID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); + } + string HMaintainItemID = ds.Tables[0].Rows[0]["HItemID"].ToString(); + } + else + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,妫�鏌ラ」鐩唬鐮佷负绌�"; + objJsonResult.data = null; + return objJsonResult; + } + //宀椾綅浠g爜 + if (HCheckPostNumber != "") + { + //鏌ヨ鐗╂枡 + ds = oCN.RunProcReturn("select * from Gy_Post where HNumber='" + HCheckPostNumber + "' and Hname='" + HCheckPostName + "'", "Gy_Post"); + + if (ds.Tables[0].Rows.Count == 0) + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,宀椾綅涓嶅瓨鍦紒"; + objJsonResult.data = null; + return objJsonResult; + } + else + { + tb2.Rows[i]["HCheckPostID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); + } + } + else + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,宀椾綅浠g爜涓虹┖"; + objJsonResult.data = null; + return objJsonResult; + } + //鍗曟嵁鍙� + if (HBillNo == "") + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "绗�" + index + "琛�,鍗曟嵁鍙蜂笉鑳戒负绌猴紒"; + objJsonResult.data = null; + return objJsonResult; + } + } + + objJsonResult.code = CodeConstant.SUCCEED; + objJsonResult.count = CountConstant.SUCCEED; + objJsonResult.Message = error; + objJsonResult.data = tb2; + return objJsonResult; + } + catch (Exception e) + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "Exception锛�" + e.ToString(); + objJsonResult.data = null; + return objJsonResult; + } + } + #endregion + + #region 璁惧鍚姩鐐规娓呭崟椤圭洰 瀵煎叆(淇濆瓨) + [Route("Sc_WorkBeginDotCheckBill/Sc_WorkBeginDotCheckBill_btnSave")] + [HttpPost] + public object Sc_WorkBeginDotCheckBill_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_WorkBeginDotCheckListBillMain_Edit", 1, false, user)) + { + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + 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 HBillType = "3744"; + string HMaker = user;//鍒跺崟浜� + DateTime HDate = DBUtility.ClsPub.isDate(DateTime.Now.ToString("yyyy-MM-dd"));// --鏃ユ湡 + long HYear = DBUtility.ClsPub.isLong(DateTime.Now.Year); + long HPeriod = DBUtility.ClsPub.isLong(DateTime.Now.Month); + string HOrgID = item["HOrgID"].ToString();//缁勭粐ID + string HBillNo = item["鍗曟嵁鍙�"].ToString(); + string HMaterID = item["HMaterID"].ToString() == "" ? "0" : item["HMaterID"].ToString(); + string HProcID = item["HProcID"].ToString() == "" ? "0" : item["HProcID"].ToString(); + string HSourceID = item["HSourceID"].ToString() == "" ? "0" : item["HSourceID"].ToString(); + string HNote = item["璁板綍浜嬮」"].ToString(); + string HRemark = item["澶囨敞"].ToString(); + //瀛愯〃 + string HCheckNoteItemID = item["HCheckNoteItemID"].ToString() == "" ? "0" : item["HCheckNoteItemID"].ToString(); + string HCheckPostID = item["HCheckPostID"].ToString() == "" ? "0" : item["HCheckPostID"].ToString(); + string HSubRemark = item["琛ㄤ綋澶囨敞"].ToString(); + + ds = oCN.RunProcReturn("select * from Gy_WorkBeginDotCheckListBillMain where HBillNo='" + HBillNo + "'", "Gy_WorkBeginDotCheckListBillMain"); + + if (ds.Tables[0].Rows.Count == 0) + { + long HInterID = DBUtility.ClsPub.CreateBillID(HBillType, ref DBUtility.ClsPub.sExeReturnInfo); + //鎻掑叆涓昏〃 + oCN.RunProc("insert into Gy_WorkBeginDotCheckListBillMain" + + "(HYear,HPeriod,HBillType,HBillSubType,HInterID,HDate,HBillNo,HRemark,HMaker,HMakeDate" + + ",HMaterID,HSourceID,HProcID,HNote) " + + "values(" + + "" + (HYear.ToString() != "0" ? HYear.ToString() : DateTime.Now.Year.ToString()) + + "," + HPeriod.ToString() + + ",'" + HBillType + + "','" + HBillType + + "'," + HInterID + + ",'" + HDate + + "','" + HBillNo + + "','" + HRemark + + "','" + user + + "'," + "getdate()" + + ",'" + HMaterID + + "','" + HSourceID + + "','" + HProcID + + "','" + HNote + + "')"); + //鎻掑叆瀛愯〃 + oCN.RunProc("insert into Gy_WorkBeginDotCheckListBillSub " + + "(HInterID,HEntryID,HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRemark" + + ",HCheckNoteItemID,HCheckPostID,HDotCheckType,HRelationID) " + + " values(" + + "" + HInterID.ToString() + + "," + 1 + + "," + 0 + + "," + 0 + + ",'" + "" + + "','" + "" + + "','" + HSubRemark + + "','" + HCheckNoteItemID + + "','" + HCheckPostID + + "','" + "璁惧" + + "','" + 0 + + "')"); + } + else + { + long HInterID = long.Parse(ds.Tables[0].Rows[0]["HInterID"].ToString()); + //鑾峰彇鏈�澶у瓙id + DataSet ds2 = oCN.RunProcReturn("select max(HEntryID) HEntryID from Gy_WorkBeginDotCheckListBillSub where HInterID='" + HInterID + "'", "Gy_WorkBeginDotCheckListBillSub"); + long HEntryID = 1; + if (ds2.Tables[0].Rows.Count > 0) + { + HEntryID = long.Parse(ds2.Tables[0].Rows[0]["HEntryID"].ToString()) + 1; + } + //鎻掑叆瀛愯〃 + oCN.RunProc("insert into Gy_WorkBeginDotCheckListBillSub " + + "(HInterID,HEntryID,HSourceInterID,HSourceEntryID,HSourceBillNo,HSourceBillType,HRemark" + + ",HCheckNoteItemID,HCheckPostID,HDotCheckType,HRelationID) " + + " values(" + + "" + HInterID.ToString() + + "," + HEntryID + + "," + 0 + + "," + 0 + + ",'" + "" + + "','" + "" + + "','" + HSubRemark + + "','" + HCheckNoteItemID + + "','" + HCheckPostID + + "','" + "璁惧" + + "','" + 0 + + "')"); + } + + i++; + } + + oCN.Commit(); + + objJsonResult.code = CodeConstant.SUCCEED; + objJsonResult.count = CountConstant.SUCCEED; + objJsonResult.Message = "瀵煎叆鎴愬姛!"; + objJsonResult.data = null; + return objJsonResult; + } + catch (Exception e) + { + LogService.Write(e); + oCN.RollBack(); + objJsonResult.code = CodeConstant.FAIL; + objJsonResult.count = CountConstant.FAIL; + objJsonResult.Message = "Exception锛�" + e.ToString(); + objJsonResult.data = null; + return objJsonResult; + } + } + #endregion + #endregion + } } \ No newline at end of file -- Gitblit v1.9.1