派生自 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,7 +24,13 @@ 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。 @@ -68,6 +79,9 @@ } } #endregion #region 工艺路线 新增 /// <summary> /// 新增单据-保存按钮 ///参数：string sql。 @@ -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,8 +162,8 @@ double HStdSourceQty = mainList[0].HStdSourceQty;//标准资源数 double HAddSourceRate = mainList[0].HAddSourceRate;//加资源增量 double HDelSourceRate = mainList[0].HDelSourceRate;//减资源减量 int HPRDORGID = mainList[0].HPRDORGID; //组织 int HPRDORGID = mainList[0].HPRDORGID; //生产组织 int HOrgID = mainList[0].HOrgID; //组织 string HPicNumVer = mainList[0].HPicNumVer;//图号版本 string HPicNumAssemble = mainList[0].HPicNumAssemble;//总装图号 string HMaterTexture = mainList[0].HMaterTexture;//材质 @@ -165,12 +179,12 @@ ",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) { //修改 @@ -291,6 +305,467 @@ 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"); //添加列名 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; } } 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; } [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); //JSON序列化转换字典集合 List<Dictionary<string, string>> list = new List<Dictionary<string, string>>(); List<object> jb = JsonConvert.DeserializeObject<List<object>>(msg3); foreach (JObject item in jb) { Dictionary<string, string> dic = new Dictionary<string, string>(); foreach (var itm in item.Properties()) { dic.Add(itm.Name, itm.Value.ToString()); } list.Add(dic); } var hnumber = ""; var HBillNo = ""; long HInterID = 0; int[] num = new int[2] { 1, 0 }; for (int i = 0; i < list.Count; i++) { //获取物料数据 ds = oCN.RunProcReturn("select * from h_v_IF_Material Where HNumber='" + list[i]["产品代码"].ToString() + "' and HUSEORGID=" + omdelMian.HOrgID, "h_v_IF_Material"); var HMaterNumber = list[i]["产品代码"].ToString(); var HMaterID = ds.Tables[0].Rows[0]["HItemID"].ToString(); var Hunitid = ds.Tables[0].Rows[0]["Hunitid"].ToString(); var HUnitNumber = ds.Tables[0].Rows[0]["HUnitNumber"].ToString(); var HName = list[i]["工艺路线名称"].ToString(); var HPicNumVer = list[i]["图号版本"].ToString(); var HPicNumAssemble = list[i]["总装图号"].ToString(); var HMaterTexture = list[i]["材质"].ToString(); var HProductNum = list[i]["成品编号"].ToString(); var HVerNum = list[i]["版本"].ToString(); var HMainPrice = list[i]["工序工价"].ToString(); if (hnumber != list[i]["产品代码"].ToString()) { //获取相同主表的子表的添加次数 HEntryID num[0] = 1; hnumber = list[i]["产品代码"].ToString(); HBillNo = DBUtility.ClsPub.CreateBillCode_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo, true);//获得一个新的单据号 HInterID = DBUtility.ClsPub.CreateBillID_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo);//获得一个新的id //添加主表数据 oCN.RunProc("insert into Gy_RoutingBillMain(HInterID, HBillNo, HYear, HPeriod, HBillType, HDate, HBillStatus, " + "HMaker, HMakeDate,HMaterNumber, HMaterID, Hunitid, HUnitNumber, HName," + "HPicNumVer, HPicNumAssemble, HMaterTexture, HProductNum, HVerNum, HRoutingGroupID, HStandard, HRemark,HMainPrice" + ",HOrgID)values" + $"({HInterID}, '{HBillNo}', {omdelMian.HYear}, {omdelMian.HPeriod}, '{BillType}', GETDATE(), 1" + $", '{user}', GETDATE(), '{HMaterNumber}', {HMaterID}, {Hunitid}, '{HUnitNumber}', '{HName}'," + $" '{HPicNumVer}', '{HPicNumAssemble}', '{HMaterTexture}', '{HProductNum}', '{HVerNum}', 0, 1, '',{(HMainPrice==""?"0":HMainPrice)}" + $",{omdelMian.HOrgID})"); //获取主表添加的次数 num[1]++; } //获取供应商 ds = oCN.RunProcReturn("select * from h_v_Gy_SupplierList Where 供应商代码='" + list[i]["供应商代码"].ToString()+"'", "h_v_Gy_SupplierList"); var HSupID = "0"; if (ds.Tables[0].Rows.Count > 0) HSupID = ds.Tables[0].Rows[0]["HItemID"].ToString(); //获取工序 ds = oCN.RunProcReturn("select * from Gy_Process Where HNumber='" + list[i]["工序代码"].ToString() + "'", "Gy_Process"); var HProcID = "0"; if (ds.Tables[0].Rows.Count > 0) HProcID = ds.Tables[0].Rows[0]["HItemID"].ToString(); //获取工作中心 ds = oCN.RunProcReturn("select * from Gy_WorkCenter Where HName='" + list[i]["工作中心"].ToString() + "'", "Gy_WorkCenter"); var HCenterID = "0"; if (ds.Tables[0].Rows.Count > 0) HCenterID = ds.Tables[0].Rows[0]["HItemID"].ToString(); var HProcNo = list[i]["工序序号"].ToString(); var HTimeUnit = list[i]["时间单位"].ToString(); var hworkqty = list[i]["加工数量"].ToString(); var HFixPrice = list[i]["班产定额"].ToString(); var HBadPrice = list[i]["不合格单价"].ToString(); var HWasterPrice = list[i]["报废单价"].ToString(); var HBeginFixQty = list[i]["本道固定开工天数"].ToString(); var HBeginDayQty = list[i]["本道开工余量"].ToString(); var HFixWorkDays = list[i]["上道等待天数"].ToString(); var HTrunWorkDays = list[i]["上道循环周期"].ToString(); var HAutoTrunFlag = list[i]["自动转移"].ToString(); var HSupFlag = list[i]["外协标记"].ToString(); var HTechnologyParameter = list[i]["工艺参数"].ToString(); var HPicNum = list[i]["图纸编号"].ToString(); var HProcCheckNote = list[i]["本工序确认记录"].ToString(); var hpassrate = list[i]["良率"].ToString(); var hremark = list[i]["备注"].ToString(); var HMouldNo = list[i]["模具编号"].ToString(); var HProcWorkNum = list[i]["程序号"].ToString(); //物料分类 oCN.RunProc("Insert into Gy_RoutingBillSub (HInterID,HEntryID,HSupID,HProcID,HCenterID,HProcNO,HTimeUnit" + ", hworkqty, HFixPrice, HBadPrice, HWasterPrice, HBeginFixQty, HBeginDayQty, HFixWorkDays" + ", HTrunWorkDays, HAutoTrunFlag, HSupFlag, HTechnologyParameter, HPicNum, HProcCheckNote" + ", hpassrate, hremark, HMouldNo, HProcWorkNum, HReadyTime, HQueueTime, HMoveTime, HUnitTime)values" + $"({HInterID}, {num[0]}, {HSupID}, {HProcID},{HCenterID}, '{HProcNo}', '{HTimeUnit}'" + $", {hworkqty}, {(HFixPrice==""?"0": HFixPrice)}, {(HBadPrice == "" ? "0": HBadPrice)}, {(HWasterPrice == "" ? "0": HWasterPrice)}, {(HBeginFixQty == "" ? "0": HBeginFixQty)}, {(HBeginDayQty == "" ? "0": HBeginDayQty)}, {(HFixWorkDays == "" ? "0": HFixWorkDays)}" + $", {(HTrunWorkDays == "" ? "0": HTrunWorkDays)},{(HAutoTrunFlag == "True" ? "1": "0")},{(HSupFlag == "True" ? "1" : "0")}, '{HTechnologyParameter}', '{HPicNum}', '{HProcCheckNote}'" + $", {hpassrate}, '{hremark}', '{HMouldNo}', '{HProcWorkNum}', 0, 0, 0, 0)"); //自增次数 num[0]++; } objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "导入完成,共" + num[1] + "个工艺路线"; 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 } }
