派生自 MES-WEB-API
WebAPI/Controllers/JHGL/Gy_RoutingBillController.cs
@@ -1,11 +1,16 @@ using Newtonsoft.Json.Linq; using Newtonsoft.Json; using Newtonsoft.Json.Linq; using Pub_Class; using SQLHelper; using System; using System.Collections; 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 @@ -19,9 +24,15 @@ SQLHelper.ClsCN oCN = new SQLHelper.ClsCN(); DataSet ds; DAL.ClsIF_Material_View oMaterHlp = new DAL.ClsIF_Material_View(); public DAL.ClsGy_Process_View oProcHlp = new DAL.ClsGy_Process_View(); DAL.ClsGy_WorkCenter_View oCenHlp = new DAL.ClsGy_WorkCenter_View(); Gy_RoutingBill_temporary omdelMian = new Gy_RoutingBill_temporary(); public string BillType = "3301"; #region 未知 /// <summary> /// 返回工艺路线列表 /// 未知 ///参数：string sql。 ///返回值：object。 /// </summary> @@ -33,11 +44,11 @@ { if (sWhere == null || sWhere.Equals("")) { ds = oCN.RunProcReturn("select * from h_v_MES_StationEntrustOutBillList " + sWhere, "h_v_MES_StationEntrustOutBillList"); ds = oCN.RunProcReturn("select * from h_v_MES_StationEntrustOutBillList " + sWhere, "h_v_MES_StationEntrustOutBillList"); } else { string sql1 = "select * from h_v_MES_StationEntrustOutBillList where 1 = 1 "; string sql1 = "select * from h_v_MES_StationEntrustOutBillList where 1 = 1 "; string sql = sql1 + sWhere; ds = oCN.RunProcReturn(sql, "h_v_MES_StationEntrustOutBillList"); } @@ -68,6 +79,9 @@ } } #endregion #region 工艺路线 新增 /// <summary> /// 新增单据-保存按钮 ///参数：string sql。 @@ -105,9 +119,9 @@ string msg2 = sArray[0].ToString(); string msg3 = sArray[1].ToString(); int hentryid = int.Parse(sArray[2].ToString());//子表的顺序id int OperationType = int.Parse(sArray[3].ToString());//数据类型 1添加 3修改 string user = sArray[4].ToString();//用户名 //int hentryid = int.Parse(sArray[2].ToString());//子表的顺序id int OperationType = int.Parse(sArray[2].ToString());//数据类型 1添加 3修改 string user = sArray[3].ToString();//用户名 try { //判断是否有编辑权限 @@ -122,7 +136,7 @@ msg2 = "[" + msg2.ToString() + "]"; List<Gy_RoutingBill> mainList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Gy_RoutingBill>>(msg2); string BillType = "3301"; long HInterID = mainList[0].HInterID;//递入type得到的单据ID string HBillNo = mainList[0].HBillNo;//递入type得到的单据号 DateTime HDate = mainList[0].HDate;//日期 @@ -148,15 +162,17 @@ double HStdSourceQty = mainList[0].HStdSourceQty;//标准资源数 double HAddSourceRate = mainList[0].HAddSourceRate;//加资源增量 double HDelSourceRate = mainList[0].HDelSourceRate;//减资源减量 int HPRDORGID = mainList[0].HPRDORGID; //组织 int HPRDORGID = mainList[0].HOrgID; //生产组织 int HOrgID = mainList[0].HOrgID; //组织 string HPicNumVer = mainList[0].HPicNumVer;//图号版本 string HPicNumAssemble = mainList[0].HPicNumAssemble;//总装图号 string HMaterTexture = mainList[0].HMaterTexture;//材质 string HProductNum = mainList[0].HProductNum;//成品编号 string HVerNum = mainList[0].HVerNum;//版本 if (OperationType == 1)//新增 ds = oCN.RunProcReturn("select * from h_v_Gy_RoutingBillList where hmainid=" + HInterID + " and 单据号='" + HBillNo + "'", "h_v_Gy_RoutingBillList"); if (OperationType == 1 && ds.Tables[0].Rows.Count == 0)//新增 { //主表 oCN.RunProc("Insert Into Gy_RoutingBillMain " + @@ -165,14 +181,14 @@ ",HRoutingGroupID,HUnitID,HMaterNumber,HUnitNumber,HStandard" + ",HMainGroupID,HMainProcID,HMainCenterID,HMainTimeUnit,HMainUnitTime,HMainWorkQty" + ",HMainPrice,HStdSourceQty,HAddSourceRate,HPRDORGID,HDelSourceRate" + ",HPicNumVer,HPicNumAssemble,HMaterTexture,HProductNum,HVerNum) " + " values('" + BillType + "'," + HInterID + ",'" + HBillNo + "','" + HDate + "'" + ",HPicNumVer,HPicNumAssemble,HMaterTexture,HProductNum,HVerNum,HOrgID) " + " values('" + BillType + "'," + HInterID + ",'" + HBillNo + "','" + HDate + "'" + "," + HYear + "," + HPeriod + ",'" + HRemark + "','" + HMaker + "',getdate()," + HMaterID + ",'" + HName + "'," + HMaterTypeID + "," + HRoutingGroupID + "," + HUnitID + ",'" + HMaterNumber + "','" + HUnitNumber + "'," + Convert.ToString(HStandard ? 1 : 0) + "," + HMainGroupID + "," + HMainProcID + "," + HMainCenterID + ",'" + HMainTimeUnit + "'," + HMainUnitTime + "," + HMainWorkQty + "," + HMainPrice + "," + HStdSourceQty + "," + HAddSourceRate + "," + HPRDORGID + "," + HDelSourceRate + ",'"+ HPicNumVer + "','" + HPicNumAssemble + "','" + HMaterTexture + "','" + HProductNum + "','" + HVerNum + "') "); ",'" + HPicNumVer + "','" + HPicNumAssemble + "','" + HMaterTexture + "','" + HProductNum + "','" + HVerNum + "'," + HOrgID + ") "); } else if (OperationType == 3) else if (OperationType == 3 || ds.Tables[0].Rows.Count != 0) { //修改 oCN.RunProc("update Gy_RoutingBillMain set " + "HDate='" + HDate + @@ -186,10 +202,10 @@ ",HProductNum='" + HProductNum + "',HVerNum='" + HVerNum + "' where HInterID='" + HInterID + "'"); //删除子表 oCN.RunProc("delete from Gy_RoutingBillSub where HInterID='" + HInterID + "' and HEntryID='" + hentryid + "'"); oCN.RunProc("delete from Gy_RoutingBillSub where HInterID='" + HInterID+"'"); } //保存子表 objJsonResult = AddBillSub(msg3, HInterID, hentryid); objJsonResult = AddBillSub(msg3, HInterID); if (objJsonResult.code == "0") { objJsonResult.code = "0"; @@ -241,7 +257,7 @@ } } public json AddBillSub(string msg3, long HInterID, int hentryid) public json AddBillSub(string msg3, long HInterID) { List<Gy_RoutingBillSub> DetailColl = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Gy_RoutingBillSub>>(msg3); int i = 0; @@ -270,7 +286,7 @@ ",HTechnologyParameter,HPicNum,HProcCheckNote" + ",HOverRate,HProcWorkNum" + ") values(" + HInterID + "," + (hentryid==-1?i: hentryid) + "," + oSub.HProcID.ToString() + ",'" + (hentryid == -1 ? i : hentryid) + "'," + oSub.HSupID.ToString() + "," + Convert.ToString(oSub.HSupFlag ? 1 : 0) + "" + + HInterID + "," + i + "," + oSub.HProcID.ToString() + ",'" + i + "'," + oSub.HSupID.ToString() + "," + Convert.ToString(oSub.HSupFlag ? 1 : 0) + "" + "," + oSub.HWorkQty.ToString() + "," + oSub.HCenterID.ToString() + ",'" + oSub.HTimeUnit.ToString() + "'," + oSub.HUnitTime.ToString() + "," + oSub.HReadyTime.ToString() + "," + oSub.HQueueTime.ToString() + "," + oSub.HMoveTime.ToString() + ",'" + oSub.HCloseMan + "'," + Convert.ToString(oSub.HCloseType ? 1 : 0) + ",'" + oSub.HRemark + "','" + oSub.HProcType + "'," + Convert.ToString(oSub.HNextProcFlag ? 1 : 0) + "," + Convert.ToString(oSub.HFlowProc ? 1 : 0) + @@ -291,6 +307,390 @@ return objJsonResult; } // #endregion #region 工艺路线 文件上传 [Route("Gy_RoutingBill/Gy_RoutingBillimport")] [HttpPost] public object Gy_RoutingBillimport() { try { var WorkBookName = HttpContext.Current.Request["WorkBookName"]; DBUtility.ClsPub.HOrgID = long.Parse(HttpContext.Current.Request["HOrgID"]); //获取文件名称 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,2, WorkBookName); //删除文件 File.Delete(ExcelPath); //创建临时表 DataTable tb2 = new DataTable("dt2"); DataTable tb3 = new DataTable("dt3"); //添加列名 for (int i = 0; i < ExcelDs.Tables[0].Columns.Count; i++) { tb2.Columns.Add(ExcelDs.Tables[0].Rows[0][i].ToString()); } //添加数据 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("产品代码")) 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; } objJsonResult = (json)Checkdata(tb2); if (objJsonResult.code == "0") { return objJsonResult; } for (int i = 0; i <= tb2.Rows.Count - 1; i++) { string sHNumber = ""; if (DBUtility.ClsPub.isStrNull(tb2.Rows[i]["产品代码"].ToString()) != "") { sHNumber = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["产品代码"].ToString()); //审核代码是否合理 if (!DBUtility.ClsPub.AllowNumber(sHNumber)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "物料代码中不能出现连续‘.’并且首位末位不能为‘.’！"; objJsonResult.data = null; return objJsonResult; } //得到物料内码 if (!oMaterHlp.GetInfoByNumber(sHNumber)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + (i + 1).ToString() + "行," + sHNumber + "没有找到对应的物料！"; objJsonResult.data = null; return objJsonResult; } } else { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + (i + 1).ToString() + "行,没有找到对应的物料！"; objJsonResult.data = null; return objJsonResult; } } //删除临时表数据 oCN.RunProc("delete from diyipi"); //临时表添加数据 for (int i = 0; i < tb2.Rows.Count; i++) { oCN.RunProc(@"insert into diyipi(产品代码,产品名称,工序序号,工序代码,工序,工作中心,加工数量 , 良率, 工序工价, 不合格单价, 报废单价, 本道固定开工天数, 本道开工余量, 上道等待天数 , 上道循环周期, 自动转移, 外协标记, 供应商代码, 供应商, 工艺参数, 图纸编号 , 本工序确认记录, 备注, 图号版本, 总装图号, 材质, 成品编号, 版本 , 模具编号, 程序号, 班产定额, 工艺路线名称, 时间单位,工艺路线代码)values ('" + tb2.Rows[i]["产品代码"].ToString() + "','" + tb2.Rows[i]["产品名称"].ToString() + "','" + tb2.Rows[i]["工序序号"].ToString() + "','" + tb2.Rows[i]["工序代码"].ToString() + "','" + tb2.Rows[i]["工序"].ToString() + "','" + tb2.Rows[i]["工作中心"].ToString() + "','" + tb2.Rows[i]["加工数量"].ToString() + "'" + ",'" + tb2.Rows[i]["良率"].ToString() + "','" + (tb2.Rows[i]["工序工价"].ToString()==""?"0": tb2.Rows[i]["工序工价"].ToString()) + "','" + tb2.Rows[i]["不合格单价"].ToString() + "','" + tb2.Rows[i]["报废单价"].ToString() + "','" + tb2.Rows[i]["本道固定开工天数"].ToString() + "','" + tb2.Rows[i]["本道开工余量"].ToString() + "','" + tb2.Rows[i]["上道等待天数"].ToString() + "'" + ",'" + tb2.Rows[i]["上道循环周期"].ToString() + "','" + tb2.Rows[i]["自动转移"].ToString() + "','" + tb2.Rows[i]["外协标记"].ToString() + "','" + tb2.Rows[i]["供应商代码"].ToString() + "','" + tb2.Rows[i]["供应商"].ToString() + "','" + tb2.Rows[i]["工艺参数"].ToString() + "','" + tb2.Rows[i]["图纸编号"].ToString() + "'" + ",'" + tb2.Rows[i]["本工序确认记录"].ToString() + "','" + tb2.Rows[i]["备注"].ToString() + "','" + tb2.Rows[i]["图号版本"].ToString() + "','" + tb2.Rows[i]["总装图号"].ToString() + "','" + tb2.Rows[i]["材质"].ToString() + "','" + tb2.Rows[i]["成品编号"].ToString() + "','" + tb2.Rows[i]["版本"].ToString() + "'" + ",'" + tb2.Rows[i]["模具编号"].ToString() + "','" + tb2.Rows[i]["程序号"].ToString() + "','" + tb2.Rows[i]["班产定额"].ToString() + "','" + tb2.Rows[i]["工艺路线名称"].ToString() + "','" + tb2.Rows[i]["时间单位"].ToString() + "','" + tb2.Rows[i]["工艺路线代码"].ToString() + "')"); } 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; } } //检查物料 private object Checkdata(DataTable dt) { bool b = false; string sErrMsg = ""; string sMsg = ""; for (int i = 0; i <= dt.Rows.Count - 1; i++) { string sHNumber = ""; string sHNamePoc = ""; string sHNumberCen = ""; Single sWorkQty = 0; Single sHPassRate = 0; // sHNumber = DBUtility.ClsPub.isStrNull(dt.Rows[i]["产品代码"].ToString()); sHNumberCen = DBUtility.ClsPub.isStrNull(dt.Rows[i]["工作中心"].ToString()); sHNamePoc = DBUtility.ClsPub.isStrNull(dt.Rows[i]["工序"].ToString()); sWorkQty = DBUtility.ClsPub.isSingle(dt.Rows[i]["加工数量"].ToString()); sHPassRate = DBUtility.ClsPub.isSingle(dt.Rows[i]["良率"].ToString()); if (sHNumber != "") { // int index = i + 1; //加工数量不能小于等于零 if (sWorkQty <= 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行加工数量不能小于等于零！"; objJsonResult.data = null; return objJsonResult; } //良率不能小于等于零 if (sHPassRate <= 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行良率不能小于等于零！"; objJsonResult.data = null; return objJsonResult; } //审核代码是否合理 if (!DBUtility.ClsPub.AllowNumber(sHNumber)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "物料代码中不能出现连续‘.’并且首位末位不能为‘.’！"; objJsonResult.data = null; return objJsonResult; } //得到物料内码 int j = i + 2; if (!oMaterHlp.GetInfoByNumber(sHNumber)) { sMsg = "[" + sHNumber + "]物料不存在"; if (sErrMsg.Contains(sMsg) == false) { sErrMsg = sErrMsg + "[" + sHNumber + "]物料不存在\r\n"; } b = true; } //得到工序内码 if (!oProcHlp.GetInfoByName(sHNamePoc)) { sMsg = "[" + sHNamePoc + "]工序不存在"; if (sErrMsg.Contains(sMsg) == false) { sErrMsg = sErrMsg + "[" + sHNamePoc + "]工序不存在\r\n"; } b = true; } //得到工作中心内码 if (!oCenHlp.GetInfoByName(sHNumberCen)) { sMsg = "[" + sHNumberCen + "]工作中心不存在"; if (sErrMsg.Contains(sMsg) == false) { sErrMsg = sErrMsg + "[" + sHNumberCen + "]工作中心不存在\r\n"; } b = true; } } } if (b == true) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = sErrMsg; objJsonResult.data = null; return objJsonResult; } else { objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = sErrMsg; objJsonResult.data = null; return objJsonResult; } } #endregion #region 工艺路线 导入(保存) //临时字段 public class Gy_RoutingBill_temporary { public string HYear; public string HPeriod; public string DataAppend; public string HOrgID; public Int64 HTProcessFlag; } [Route("Gy_RoutingBill/Gy_RoutingBill_btnSave")] [HttpPost] public object Gy_RoutingBill_btnSave([FromBody] JObject sMainSub) { var _value = sMainSub["sMainSub"].ToString(); string msg1 = _value.ToString(); oCN.BeginTran(); //保存主表 objJsonResult = RoutAddBillMain(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.Message; objJsonResult.data = null; return objJsonResult; } public json RoutAddBillMain(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("Gy_RoutingBill_Edit", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "无保存权限！"; objJsonResult.data = null; return objJsonResult; } //表头字段 omdelMian = Newtonsoft.Json.JsonConvert.DeserializeObject<Gy_RoutingBill_temporary>(msg2); ds = oCN.RunProcReturn("select distinct 产品代码,工艺路线代码 from diyipi ", "diyipi"); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { var HBillNo = ds.Tables[0].Rows[i]["工艺路线代码"].ToString();//获得一个新的单据号 var HInterID = DBUtility.ClsPub.CreateBillID_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo);//获得一个新的id oCN.RunProc($"exec Gy_RoutingBill_Excel '{ds.Tables[0].Rows[i]["产品代码"].ToString()}',{HInterID},'{HBillNo}','{user}',{omdelMian.HOrgID},{omdelMian.HTProcessFlag} "); } objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "导入完成,共" + ds.Tables[0].Rows.Count + "个工艺路线"; 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 } }
