using Newtonsoft.Json; using Newtonsoft.Json.Linq; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Web; using System.Web.Http; using System.Web.Script.Serialization; using WebAPI.Models; using System.Globalization; namespace WebAPI.Controllers.SCGL.日计划管理 { public class JIT_DayPlanPlatFormImportController : ApiController { private json objJsonResult = new json(); public DataSet ds = new DataSet(); public WebServer webserver = new WebServer(); SQLHelper.ClsCN oCN = new SQLHelper.ClsCN(); public static string BillType = "4610"; Sc_WorkBillSortBillMain omdelMian = new Sc_WorkBillSortBillMain(); Pub_Class.ClsXt_SystemParameter oSystemParameter = new Pub_Class.ClsXt_SystemParameter(); #region 生产日计划 文件上传 [Route("JIT_DayPlanPlatFormImport/JIT_DayPlanPlatFormImport_DR")] [HttpPost] public object JIT_DayPlanPlatFormImport_DR() { 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("hmainid", typeof(Int32)); tb2.Columns.Add("HMaterID", typeof(Int32));//物料ID tb2.Columns.Add("单据号", typeof(string)); tb2.Columns.Add("HWorkShopID", typeof(Int32));//生产车间 tb2.Columns.Add("HEmpID", typeof(Int32));//职员 tb2.Columns.Add("员工", typeof(string));//职员 tb2.Columns.Add("HUnitID", typeof(Int32));//计量单位 tb2.Columns.Add("HSourceID", typeof(Int32));//生产资源id tb2.Columns.Add("ICMOBillHInterID", typeof(Int32));//生产订单主id tb2.Columns.Add("ICMOBillHEntryID", typeof(Int32));//生产订单子id tb2.Columns.Add("HProdORGID", typeof(Int32));//组织id tb2.Columns.Add("HSeOrderBillNo", typeof(string));//销售订单号 //获取系统参数 string Ret = ""; oSystemParameter.ShowBill(ref Ret); LogService.Write("excel值：" + ExcelDs.Tables[0].Rows[1][15].ToString());//2024-11-26 //添加数据 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() == "" && ExcelDs.Tables[0].Rows[i][1].ToString() == "") { continue; } //else if (oSystemParameter.omodel.JIT_DayPlan_TimeFrame != "0" && oSystemParameter.omodel.JIT_DayPlan_TimeFrame != "") //{ // Int32 HDays = Convert.ToInt32(oSystemParameter.omodel.JIT_DayPlan_TimeFrame) * 7; // for (int j = 0; j < ExcelDs.Tables[0].Columns.Count; j++) // { // if (Convert.ToInt32(ExcelDs.Tables[0].Rows[i][j].ToString()) > 0 ) // { // continue; // } // } //} else { tb2.Rows.Add(row); } } var error = ""; var ErrorResult = ""; //查询生产订单表判断有没有生产订单信息 for (int i = 0; i < tb2.Rows.Count; i++) { if (tb2.Rows[i]["组织编码*"].ToString() == "") error += "组织编码不能为空;"; if (tb2.Rows[i]["生产订单号*"].ToString() == "") error += "生产订单号不能为空;\n"; if (tb2.Rows[i]["生产订单明细行号*"].ToString() == "") error += "生产订单明细行号不能为空;"; if (tb2.Rows[i]["生产车间编码*"].ToString() == "") error += "生产车间编码不能为空;"; if (tb2.Rows[i]["生产资源编码*"].ToString() == "") error += "生产资源编码不能为空;"; if (tb2.Rows[i]["物料编码*"].ToString() == "") error += "物料编码不能为空;"; if (error.Length > 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = ErrorResult += $"Excel模板存在错误，行数{i + 1}:{error}\r\n"; ; objJsonResult.data = null; return objJsonResult; } string sql1 = string.Format("select * from h_v_JIT_ICMOBillList_DR where 单据号='{0}' and 生产车间代码='{1}' and 产品代码='{2}' and 明细行号={3} ", tb2.Rows[i]["生产订单号*"].ToString(), tb2.Rows[i]["生产车间编码*"].ToString(), tb2.Rows[i]["物料编码*"].ToString(), tb2.Rows[i]["生产订单明细行号*"].ToString()); ds = oCN.RunProcReturn(sql1, "h_v_JIT_ICMOBillList_DR"); if (ds.Tables[0].Rows.Count == 0) { error += $"没有生产订单信息，生产订单号:{ tb2.Rows[i]["生产订单号*"].ToString()},生产车间编码:{tb2.Rows[i]["生产车间编码*"].ToString()}" + $",生产订单明细行号:{ tb2.Rows[i]["生产订单明细行号*"].ToString()},物料编码:{ tb2.Rows[i]["物料编码*"].ToString()}"; } if (error.Length > 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = ErrorResult += $"Excel模板存在错误，行数{i + 1}:{error}\r\n"; ; objJsonResult.data = null; return objJsonResult; } else { tb2.Rows[i]["HMaterID"] = ds.Tables[0].Rows[0]["HMaterID"].ToString(); tb2.Rows[i]["物料名称"] = ds.Tables[0].Rows[0]["产品名称"].ToString(); tb2.Rows[i]["规格型号"] = ds.Tables[0].Rows[0]["规格型号"].ToString(); tb2.Rows[i]["HWorkShopID"] = ds.Tables[0].Rows[0]["HDeptID"].ToString(); tb2.Rows[i]["ICMOBillHInterID"] = ds.Tables[0].Rows[0]["hmainid"].ToString();//生产订单主id tb2.Rows[i]["ICMOBillHEntryID"] = ds.Tables[0].Rows[0]["HEntryID"].ToString();//生产订单子id string sErr = ""; if (oSystemParameter.ShowBill(ref sErr)) { if (oSystemParameter.omodel.WMS_CampanyName != "宝工") { tb2.Rows[i]["生产订单数量"] = ds.Tables[0].Rows[0]["生产任务单数量"].ToString();//生产订单数量 } } //tb2.Rows[i]["生产订单数量"] = ds.Tables[0].Rows[0]["生产任务单数量"].ToString();//生产订单数量 tb2.Rows[i]["HSeOrderBillNo"] = ds.Tables[0].Rows[0]["销售订单号"].ToString();//销售订单号 } ds = oCN.RunProcReturn("select * from h_v_JIT_WorkBillSortBillList_DR " + $"where 生产订单号='{tb2.Rows[i]["生产订单号*"].ToString()}' and 生产订单明细行号='{tb2.Rows[i]["生产订单明细行号*"].ToString()}' " + $"and 生产车间编码='{tb2.Rows[i]["生产车间编码*"].ToString()}' and 物料代码='{tb2.Rows[i]["物料编码*"].ToString()}' " + $"and 生产资源编码='{tb2.Rows[i]["生产资源编码*"].ToString()}' ", "h_v_JIT_WorkBillSortBillList_DR"); //组织编码='{tb2.Rows[i]["组织编码*"].ToString()}' and //and 生产资源编码='{tb2.Rows[i]["生产资源编码*"].ToString()}' if (ds.Tables[0].Rows.Count > 0) { tb2.Rows[i]["hmainid"] = ds.Tables[0].Rows[0]["hmainid"].ToString(); tb2.Rows[i]["单据号"] = ds.Tables[0].Rows[0]["单据号"].ToString(); tb2.Rows[i]["优先级"] = ds.Tables[0].Rows[0]["优先级"].ToString(); tb2.Rows[i]["HSourceID"] = ds.Tables[0].Rows[0]["HSourceID"].ToString(); tb2.Rows[i]["HEmpID"] = ds.Tables[0].Rows[0]["HEmpID"].ToString(); tb2.Rows[i]["员工编码"] = ds.Tables[0].Rows[0]["职员代码"].ToString(); tb2.Rows[i]["员工"] = ds.Tables[0].Rows[0]["职员"].ToString(); tb2.Rows[i]["HUnitID"] = ds.Tables[0].Rows[0]["HUnitID"].ToString(); tb2.Rows[i]["计量单位"] = ds.Tables[0].Rows[0]["计量单位"].ToString(); tb2.Rows[i]["批次号"] = ds.Tables[0].Rows[0]["批次"].ToString(); tb2.Rows[i]["销售订单数量"] = ds.Tables[0].Rows[0]["销售订单数量"].ToString(); tb2.Rows[i]["总齐套数量"] = ds.Tables[0].Rows[0]["齐套数量"].ToString(); } else { //员工 ds = oCN.RunProcReturn($"select * from Gy_Employee with(nolock) where HNumber='{tb2.Rows[i]["员工编码"].ToString()}'", "Gy_Employee"); if (ds.Tables[0].Rows.Count > 0) { tb2.Rows[i]["HEmpID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); tb2.Rows[i]["员工"] = ds.Tables[0].Rows[0]["HName"].ToString(); } else { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = $"第{i + 1}行,员工查无数据!"; objJsonResult.data = tb2; return objJsonResult; } //计量单位 ds = oCN.RunProcReturn($"select * from Gy_Unit with(nolock) where HNumber='{tb2.Rows[i]["计量单位"].ToString()}'", "Gy_Unit"); if (ds.Tables[0].Rows.Count > 0) { tb2.Rows[i]["HUnitID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); } else { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = $"第{i + 1}行,计量单位查无数据!"; objJsonResult.data = tb2; return objJsonResult; } //生产资源 ds = oCN.RunProcReturn($"select * from Gy_Source with(nolock) where HNumber='{tb2.Rows[i]["生产资源编码*"].ToString()}'", "Gy_Source"); if (ds.Tables[0].Rows.Count > 0) { tb2.Rows[i]["HSourceID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); } else { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = $"第{i+1}行,生产资源查无数据!"; objJsonResult.data = tb2; return objJsonResult; } } //组织 ds = oCN.RunProcReturn($"select * from Xt_ORGANIZATIONS with(nolock) where HNumber='{tb2.Rows[i]["组织编码*"].ToString()}'", "Xt_ORGANIZATIONS"); if (ds.Tables[0].Rows.Count > 0) { tb2.Rows[i]["HProdORGID"] = ds.Tables[0].Rows[0]["HItemID"].ToString(); } else { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = $"第{i + 1}行,组织查无数据!"; objJsonResult.data = tb2; return objJsonResult; } } objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = ErrorResult; 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("JIT_DayPlanPlatFormImport/JIT_DayPlanPlatFormImport_btnSave")] [HttpPost] public object JIT_DayPlanPlatFormImport_btnSave([FromBody] JObject sMainSub) { var _value = sMainSub["sMainSub"].ToString(); string msg1 = _value.ToString(); oCN.BeginTran(); //保存主表 objJsonResult = AddBillMain(msg1); if (objJsonResult.code == "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = objJsonResult.Message; objJsonResult.data = null; return objJsonResult; } oCN.Commit(); objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "单据保存成功！"; objJsonResult.data = null; return objJsonResult; } public json AddBillMain(string msg1) { string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); string msg2 = sArray[0].ToString(); string msg3 = sArray[1].ToString(); string user = sArray[2].ToString(); try { if (!DBUtility.ClsPub.Security_Log("Sc_WorkBillSortBill_Edit", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "无保存权限！"; objJsonResult.data = null; return objJsonResult; } //表头字段 omdelMian = Newtonsoft.Json.JsonConvert.DeserializeObject(msg2); //JSON序列化转换字典集合 List> list = new List>(); List jb = JsonConvert.DeserializeObject>(msg3); foreach (JObject item in jb) { Dictionary dic = new Dictionary(); foreach (var itm in item.Properties()) { dic.Add(itm.Name, itm.Value.ToString()); } list.Add(dic); } for (int i = 0; i < list.Count; i++) { int TrueCount = 0, SumCount = 0; long HInterID = 0; var HBillNo = ""; if (list[i]["单据号"].ToString() == "" && list[i]["hmainid"].ToString() == "") { HBillNo = DBUtility.ClsPub.CreateBillCode_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo, true);//获得一个新的单据号 HInterID = DBUtility.ClsPub.CreateBillID_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo);//获得一个新的id var HICMOBillNo = list[i]["生产订单号*"].ToString(); var HICMOEntrySEQ = list[i]["生产订单明细行号*"].ToString(); var HProdORGID = list[i]["HProdORGID"].ToString(); var HWorkShopID = list[i]["HWorkShopID"].ToString(); var HSourceID = list[i]["HSourceID"].ToString(); var HWorkQty = DBUtility.ClsPub.isDoule(list[i]["产能"]); var HYX = list[i]["优先级"].ToString(); var HEmpID = list[i]["HEmpID"].ToString(); var HMaterID = list[i]["HMaterID"].ToString(); var HMaterName = list[i]["物料名称"].ToString(); var HMaterModel = list[i]["规格型号"].ToString(); var HUnitID = list[i]["HUnitID"].ToString(); var HBatchNo = list[i]["批次号"].ToString(); var HSeOrderBillQty = list[i]["销售订单数量"].ToString(); var HPlanQty = list[i]["生产订单数量"].ToString(); var HCompleteQty = list[i]["总齐套数量"].ToString(); var HOrderType = omdelMian.HOrderType; var HDate = omdelMian.HDate; var HMainSourceInterID = list[i]["ICMOBillHInterID"].ToString(); var HMainSourceEntryID = list[i]["ICMOBillHEntryID"].ToString(); var HSeOrderBillNo = list[i]["HSeOrderBillNo"].ToString(); string sql = $"select * from Sc_WorkBillSortBillMain where HMainSourceInterID={HMainSourceInterID}" + $" and HMainSourceEntryID={HMainSourceEntryID} and HICMOBillNo='{HICMOBillNo}' and HICMOEntrySEQ={HICMOEntrySEQ} and HSourceID={HSourceID} and ISNULL(HDeleteMan,'') = ''"; LogService.Write("sql:" + sql); ds = oCN.RunProcReturn(sql, "Sc_WorkBillSortBillMain"); if (ds.Tables[0].Rows.Count > 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = $"第{i + 1}行生产资源有重复,请修改!"; objJsonResult.data = null; return objJsonResult; } //主表 oCN.RunProc("insert into Sc_WorkBillSortBillMain(HInterID,HBillNo,HYear,HPeriod,HBillType," + "HBillSubType,HDate,HBillStatus,HMaker,HMakeDate,HICMOBillNo,HOrderType," + "HICMOEntrySEQ,HWorkShopID,HSourceID, HYX,HWorkQty, HEmpID," + " HMaterID, HMaterName, HMaterModel, HUnitID, HBatchNo," + " HSeOrderBillQty, HPlanQty, HCompleteQty,HPreparatDate," + "HMainSourceInterID,HMainSourceEntryID,HICMOInterID,HICMOEntryID,HProdORGID,HICMOInterID_Sec,HICMOEntryID_Sec" + ",HSeOrderBillNo)values" + $"({HInterID},'{HBillNo}',{DateTime.Now.Year},{DateTime.Now.Month},'{BillType}'," + $"'{BillType}',GETDATE(),1,'{user}','{HDate}','{HICMOBillNo}','{HOrderType}'," + $"{(HICMOEntrySEQ == "" ? 0.ToString() : HICMOEntrySEQ)},{(HWorkShopID == "" ? 0.ToString() : HWorkShopID)},{(HSourceID == "" ? 0.ToString() : HSourceID)}, {(HYX == "" ? 0.ToString() : HYX)},{(HWorkQty)}, {(HEmpID == "" ? 0.ToString() : HEmpID)}," + $" {(HMaterID == "" ? 0.ToString() : HMaterID)}, '{HMaterName}', '{HMaterModel}', {(HUnitID == "" ? 0.ToString() : HUnitID)}, '{HBatchNo}'," + $" {(HSeOrderBillQty == "" ? 0.ToString() : HSeOrderBillQty)}, {(HPlanQty == "" ? 0.ToString() : HPlanQty)}, {(HCompleteQty == "" ? 0.ToString() : HCompleteQty)},getdate()," + $" {HMainSourceInterID},{HMainSourceEntryID},{HMainSourceInterID},{HMainSourceEntryID},{HProdORGID}, {HMainSourceInterID},{HMainSourceEntryID}" + $",'{HSeOrderBillNo}')"); } int SumDay = 31; //动态两月之差 DateTime.Now.AddMonths(1).AddDays(-1).Subtract(DateTime.Now).Days; // 统计总天数 HashSet uniqueDates = new HashSet(); foreach (var key in list[i].Keys) { if (DateTime.TryParseExact(key, "yyyy-M-d", CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime date)) { uniqueDates.Add(date); } } SumDay = uniqueDates.Count; for (int j = 0; j < SumDay; j++) { LogService.Write("1:" + list[i][DateTime.Now.AddDays(j).ToString("yyyy-MM-dd")].ToString()); LogService.Write("2:" + DateTime.Now.AddDays(j).ToString("yyyy-MM-dd")); //主表子表都有数据 if (list[i][DateTime.Now.AddDays(j).ToString("yyyy-MM-dd")].ToString() != "") { SumCount += 1; ////保存子表 objJsonResult = AddBillSub(HInterID == 0 ? list[i]["hmainid"].ToString() : HInterID.ToString() , HBillNo == "" ? list[i]["单据号"].ToString() : HBillNo , DateTime.Parse(DateTime.Now.AddDays(j).ToString("yyyy-MM-dd").ToString()) , int.Parse(list[i][DateTime.Now.AddDays(j).ToString("yyyy-MM-dd")].ToString())); if (objJsonResult.count == 1) { TrueCount += 1; } } //主表有数据 子表无数据 if (j == SumDay - 1 && SumCount == 0 && HInterID != 0 && HBillNo != "") { objJsonResult = AddBillSub(HInterID.ToString(), HBillNo, DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd").ToString()), 0); } } if (TrueCount != SumCount) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = objJsonResult.Message; objJsonResult.data = null; return objJsonResult; } } objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = null; objJsonResult.data = null; return objJsonResult; } catch (Exception e) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "Exception！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } public json AddBillSub(string HInterID,string HBillNo, DateTime date, int HQTY) { //获取表格数据 ds = oCN.RunProcReturn($"select * from h_v_JIT_Sc_WorkBillSortBillList where 单据号='{HBillNo}'", "h_v_JIT_Sc_WorkBillSortBillList"); int count = 0; if (ds.Tables[0].Rows.Count > 0) { //判断子表是否有当日的日期以及日期所对应的数量 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (DateTime.Parse(ds.Tables[0].Rows[i]["日计划生产日期"].ToString()) == date) { oCN.RunProc($"update Sc_WorkBillSortBillSub set HMasterDate='{date}',HQty={HQTY}" + $" where HInterID={HInterID} and HEntryID={ds.Tables[0].Rows[i]["hsubid"].ToString()}"); count += 1; } } } if (count != 1) { //插入子表 oCN.RunProc("insert into Sc_WorkBillSortBillSub(HInterID,HSEQ," + "HMasterDate,HQty)" + $"values({HInterID}, {(ds.Tables[0].Rows.Count + 1)}," + $" '{date}', {HQTY})"); } objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = null; objJsonResult.data = null; return objJsonResult; } #endregion #region 生产日计划(三升导入) 文件上传 [Route("JIT_DayPlanPlatFormImport/JIT_DayPlanPlatFormImport_SS")] [HttpPost] public object JIT_DayPlanPlatFormImport_SS() { 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("hmainid", typeof(Int32)); tb2.Columns.Add("HMaterID", typeof(Int32)); tb2.Columns.Add("ICMOBillHInterID", typeof(Int32)); tb2.Columns.Add("ICMOBillHEntryID", typeof(Int32)); //获取系统参数 string Ret = ""; oSystemParameter.ShowBill(ref Ret); LogService.Write("excel值：" + ExcelDs.Tables[0].Rows[1][1].ToString());//2024-11-26 //添加数据 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() == "" && ExcelDs.Tables[0].Rows[i][1].ToString() == "") { continue; } else { tb2.Rows.Add(row); } } var error = ""; var ErrorResult = ""; //查询生产订单表判断有没有生产订单信息 for (int i = 0; i < tb2.Rows.Count; i++) { //if (tb2.Rows[i]["计划结束日期"].ToString() == "") // error += "计划结束日期不能为空;"; if (tb2.Rows[i]["客户订单号"].ToString() == "") error += "客户订单号不能为空;"; if (tb2.Rows[i]["物料编码"].ToString() == "") error += "物料编码不能为空;"; if (error.Length > 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = ErrorResult += $"Excel模板存在错误，行数{i + 1}:{error}\r\n"; ; objJsonResult.data = null; return objJsonResult; } //查找是否存在对应生产订单 string sql1 = string.Format("select * from h_v_IF_ICMOBillList where 客户订单号='{0}' and 产品代码='{1}' and 状态 in ('计划确认','开工')", tb2.Rows[i]["客户订单号"].ToString(), tb2.Rows[i]["物料编码"].ToString()); ds = oCN.RunProcReturn(sql1, "h_v_IF_ICMOBillList"); if (ds.Tables[0].Rows.Count == 0) { error += $"没有符合条件得生产订单信息，客户订单号:{ tb2.Rows[i]["客户订单号"].ToString()},产品代码:{tb2.Rows[i]["物料编码"].ToString()}"; } if (error.Length > 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = ErrorResult += $"Excel模板存在错误，行数{i + 1}:{error}\r\n"; ; objJsonResult.data = null; return objJsonResult; } else { tb2.Rows[i]["HMaterID"] = ds.Tables[0].Rows[0]["HMaterID"].ToString(); tb2.Rows[i]["ICMOBillHInterID"] = ds.Tables[0].Rows[0]["hmainid"].ToString();//生产订单主id tb2.Rows[i]["ICMOBillHEntryID"] = ds.Tables[0].Rows[0]["HEntryID"].ToString();//生产订单子id } //查找是否存在对应日计划工单 var dt = oCN.RunProcReturn($"select * from h_v_JIT_Sc_WorkBillSortBillList where 源单主内码 in({ds.Tables[0].Rows[0]["hmainid"].ToString()}) and 源单子内码 in({ds.Tables[0].Rows[0]["HEntryID"].ToString()})", "h_v_JIT_Sc_WorkBillSortBillList"); if (dt.Tables[0].Rows.Count > 0) { tb2.Rows[i]["hmainid"] = dt.Tables[0].Rows[0]["hmainid"].ToString(); } } objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = ErrorResult; 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("JIT_DayPlanPlatFormImport/JIT_DayPlanPlatFormImport_btnSave_SS")] [HttpPost] public object JIT_DayPlanPlatFormImport_btnSave_SS([FromBody] JObject sMainSub) { var _value = sMainSub["sMainSub"].ToString(); string msg1 = _value.ToString(); oCN.BeginTran(); //保存主表 objJsonResult = AddBillMain_SS(msg1); if (objJsonResult.code == "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = objJsonResult.Message; objJsonResult.data = null; return objJsonResult; } oCN.Commit(); objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "单据保存成功！"; objJsonResult.data = null; return objJsonResult; } public json AddBillMain_SS(string msg1) { string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); string msg2 = sArray[0].ToString(); string msg3 = sArray[1].ToString(); string user = sArray[2].ToString(); try { if (!DBUtility.ClsPub.Security_Log("Sc_WorkBillSortBill_Edit", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "无保存权限！"; objJsonResult.data = null; return objJsonResult; } //表头字段 omdelMian = Newtonsoft.Json.JsonConvert.DeserializeObject(msg2); //JSON序列化转换字典集合 List> list = new List>(); List jb = JsonConvert.DeserializeObject>(msg3); foreach (JObject item in jb) { Dictionary dic = new Dictionary(); foreach (var itm in item.Properties()) { dic.Add(itm.Name, itm.Value.ToString()); } list.Add(dic); } for (int i = 0; i < list.Count; i++) { long HInterID = 0; var HBillNo = ""; if (list[i]["hmainid"].ToString() == "") { HBillNo = DBUtility.ClsPub.CreateBillCode_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo, true);//获得一个新的单据号 HInterID = DBUtility.ClsPub.CreateBillID_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo);//获得一个新的id var HMainSourceInterID = list[i]["ICMOBillHInterID"].ToString(); var HMainSourceEntryID = list[i]["ICMOBillHEntryID"].ToString(); var HPlanEndDate = list[i]["计划结束日期"].ToString(); //获取对应订单信息 ds = oCN.RunProcReturn($"exec h_p_ICMOBillList_PrimarySubTable '{HMainSourceInterID}','{HMainSourceEntryID}'", "h_p_ICMOBillList_PrimarySubTable"); if (ds.Tables[0].Rows.Count == 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = $"没找到对应符合条件得订单信息，行数{i + 1}\r\n"; ; objJsonResult.data = null; return objJsonResult; } var HICMOBillNo = ds.Tables[0].Rows[0]["生产订单号"].ToString(); var HICMOEntrySEQ = ds.Tables[0].Rows[0]["生产订单明细行号"].ToString(); var HProdORGID = ds.Tables[0].Rows[0]["HProdORGID"].ToString(); var HWorkShopID = ds.Tables[0].Rows[0]["HWorkShopID"].ToString(); var HSourceID = ds.Tables[0].Rows[0]["HSourceID"].ToString(); var HWorkQty = DBUtility.ClsPub.isDoule(ds.Tables[0].Rows[0]["小时产能"].ToString()); var HYX = ds.Tables[0].Rows[0]["优先级"].ToString(); var HEmpID = ds.Tables[0].Rows[0]["操作工"].ToString(); var HMaterID = ds.Tables[0].Rows[0]["HMaterID"].ToString(); var HMaterName = ds.Tables[0].Rows[0]["物料名称"].ToString(); var HMaterModel = ds.Tables[0].Rows[0]["规格型号"].ToString(); var HUnitID = ds.Tables[0].Rows[0]["HUnitID"].ToString(); var HBatchNo = "";//批次号 var HSeOrderBillQty = ds.Tables[0].Rows[0]["销售订单数量"].ToString(); var HPlanQty = ds.Tables[0].Rows[0]["计划数量"].ToString(); var HCompleteQty = "";//总齐套数量 var HOrderType = omdelMian.HOrderType; var HDate = omdelMian.HDate; var HSeOrderBillNo = ds.Tables[0].Rows[0]["销售订单号"].ToString(); //主表 oCN.RunProc("insert into Sc_WorkBillSortBillMain(HInterID,HBillNo,HYear,HPeriod,HBillType," + "HBillSubType,HDate,HBillStatus,HMaker,HMakeDate,HICMOBillNo,HOrderType," + "HICMOEntrySEQ,HWorkShopID,HSourceID, HYX,HWorkQty, HEmpID," + " HMaterID, HMaterName, HMaterModel, HUnitID, HBatchNo," + " HSeOrderBillQty, HPlanQty, HCompleteQty,HPreparatDate," + "HMainSourceInterID,HMainSourceEntryID,HICMOInterID,HICMOEntryID,HProdORGID,HICMOInterID_Sec,HICMOEntryID_Sec" + ",HSeOrderBillNo,HPlanEndDate)values" + $"({HInterID},'{HBillNo}',{DateTime.Now.Year},{DateTime.Now.Month},'{BillType}'," + $"'{BillType}',GETDATE(),1,'{user}','{HDate}','{HICMOBillNo}','{HOrderType}'," + $"{(HICMOEntrySEQ == "" ? 0.ToString() : HICMOEntrySEQ)},{(HWorkShopID == "" ? 0.ToString() : HWorkShopID)},{(HSourceID == "" ? 0.ToString() : HSourceID)}, {(HYX == "" ? 0.ToString() : HYX)},{(HWorkQty)}, {(HEmpID == "" ? 0.ToString() : HEmpID)}," + $" {(HMaterID == "" ? 0.ToString() : HMaterID)}, '{HMaterName}', '{HMaterModel}', {(HUnitID == "" ? 0.ToString() : HUnitID)}, '{HBatchNo}'," + $" {(HSeOrderBillQty == "" ? 0.ToString() : HSeOrderBillQty)}, {(HPlanQty == "" ? 0.ToString() : HPlanQty)}, {(HCompleteQty == "" ? 0.ToString() : HCompleteQty)},getdate()," + $" {HMainSourceInterID},{HMainSourceEntryID},{HMainSourceInterID},{HMainSourceEntryID},{HProdORGID}, {HMainSourceInterID},{HMainSourceEntryID}" + $",'{HSeOrderBillNo}','{HPlanEndDate}')"); //插入子表数据 objJsonResult = AddBillSub(HInterID.ToString(), HBillNo, DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd").ToString()), 0); }//如果已存在日计划工单更新对应计划结束日期 else { var hmainid = list[i]["hmainid"].ToString(); var HPlanEndDate = list[i]["计划结束日期"].ToString(); //主表 oCN.RunProc($"update Sc_WorkBillSortBillMain set HPlanEndDate = '{HPlanEndDate}' where HInterID ={hmainid}" ); } } objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = null; objJsonResult.data = null; return objJsonResult; } catch (Exception e) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "Exception！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } #endregion } }