using System; using System.Collections.Generic; using Newtonsoft.Json; using Newtonsoft.Json.Linq; using System.Linq; using System.Net; using System.Net.Http; using System.Web.Http; using WebAPI.Models; using System.Data; using SyntacticSugar.constant; using DBUtility; using System.Text; using System.Web; using WebAPI.Controllers.SCGL.日计划管理; using System.IO; using System.Text.RegularExpressions; namespace WebAPI.Controllers.基础资料.基础资料 { public class Gy_PNLInfoController : ApiController { private json objJsonResult = new json(); SQLHelper.ClsCN oCN = new SQLHelper.ClsCN(); DataSet ds; #region 获取PNLInfo列表 [Route("Gy_PNLInfoController/GetGy_PNLInfoList")] [HttpGet] public object GetGy_PNLInfoList(string sWhere, string user) { try { List columnNameList = new List(); if (!DBUtility.ClsPub.Security_Log("Gy_PNLInfo_Query", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "没有查看权限"; objJsonResult.data = null; return objJsonResult; } DataSet ds = oCN.RunProcReturn("select * from h_v_Gy_PNLInfoList where 1=1 " + sWhere + " order by HInterID", "h_v_Gy_PNLInfoList"); //添加列名 foreach (DataColumn col in ds.Tables[0].Columns) { Type dataType = col.DataType; string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}"; columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名 } objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "Sucess！"; objJsonResult.data = ds.Tables[0]; objJsonResult.list = columnNameList; return objJsonResult; } catch (Exception e) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "Exception！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } #endregion #region 获取PNLInfo列表子表 [Route("Gy_PNLInfoController/GetGy_PNLInfoSubList")] [HttpGet] public object GetGy_PNLInfoSubList(string sWhere, string user) { try { List columnNameList = new List(); if (!DBUtility.ClsPub.Security_Log("Gy_PNLInfo_Query", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "没有查看权限"; objJsonResult.data = null; return objJsonResult; } DataSet ds = oCN.RunProcReturn("select * from h_v_Gy_PNLInfoSubList where 1=1 " + sWhere + " order by HInterID", "h_v_Gy_PNLInfoSubList"); //添加列名 foreach (DataColumn col in ds.Tables[0].Columns) { Type dataType = col.DataType; string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}"; columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名 } objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "Sucess！"; objJsonResult.data = ds.Tables[0]; objJsonResult.list = columnNameList; return objJsonResult; } catch (Exception e) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "Exception！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } #endregion #region 获取PNLInfo列表 分页 [Route("Gy_PNLInfoController/GetGy_PNLInfoListPage")] [HttpGet] public object GetGy_PNLInfoListPage(string sWhere, string user, string page, string size) { try { List columnNameList = new List(); if (!DBUtility.ClsPub.Security_Log("Gy_PNLInfo_Query", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "没有查看权限"; objJsonResult.data = null; return objJsonResult; } if (string.IsNullOrWhiteSpace(sWhere) == false) { sWhere = sWhere.Replace("'", "''"); } string sql = "exec h_p_Gy_PNLInfo_ListPage " + page + "," + size + ",'" + sWhere + "'"; DataSet ds = oCN.RunProcReturn(sql, "h_p_Gy_PNLInfo_ListPage"); //添加列名 foreach (DataColumn col in ds.Tables[0].Columns) { Type dataType = col.DataType; string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}"; columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名 } objJsonResult.code = CodeConstant.SUCCEED; objJsonResult.count = int.Parse(ds.Tables[1].Rows[0]["count"].ToString()); objJsonResult.Message = "Sucess！"; objJsonResult.data = ds.Tables[0]; objJsonResult.list = columnNameList; return objJsonResult; } catch (Exception e) { objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = 0; objJsonResult.Message = "没有返回任何记录！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } #endregion #region 获取PNLInfo列表子表 分页 [Route("Gy_PNLInfoController/GetGy_PNLInfoSubListPage")] [HttpGet] public object GetGy_PNLInfoSubListPage(string sWhere, string user, string page, string size) { try { List columnNameList = new List(); if (!DBUtility.ClsPub.Security_Log("Gy_PNLInfo_Query", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "没有查看权限"; objJsonResult.data = null; return objJsonResult; } if (string.IsNullOrWhiteSpace(sWhere) == false) { sWhere = sWhere.Replace("'", "''"); } string sql = "exec h_p_Gy_PNLInfoSub_ListPage " + page + "," + size + ",'" + sWhere + "'"; DataSet ds = oCN.RunProcReturn(sql, "h_p_Gy_PNLInfoSub_ListPage"); //添加列名 foreach (DataColumn col in ds.Tables[0].Columns) { Type dataType = col.DataType; string ColmString = "{\"ColmCols\":\"" + col.ColumnName + "\",\"ColmType\":\"" + dataType.Name + "\"}"; columnNameList.Add(JsonConvert.DeserializeObject(ColmString));//获取到DataColumn列对象的列名 } objJsonResult.code = CodeConstant.SUCCEED; objJsonResult.count = int.Parse(ds.Tables[1].Rows[0]["count"].ToString()); objJsonResult.Message = "Sucess！"; objJsonResult.data = ds.Tables[0]; objJsonResult.list = columnNameList; return objJsonResult; } catch (Exception e) { objJsonResult.code = CodeConstant.FAIL; objJsonResult.count = 0; objJsonResult.Message = "没有返回任何记录！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } #endregion #region 添加PNLInfo 添加子表 [Route("Gy_PNLInfoController/AddBill")] [HttpPost] public object AddBill([FromBody] JObject oMain) { try { var _value = oMain["oMain"].ToString(); string msg1 = _value.ToString(); string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); string msg2 = sArray[0].ToString(); // 表单信息 string msg3 = sArray[1].ToString();// 子表信息 string msg4 = sArray[2].ToString(); // 制单人信息 //string msg4 = sArray[2].ToString(); //反序列化 msg2 = "[" + msg2.ToString() + "]"; List list = JsonConvert.DeserializeObject>(msg2); List lsmain = JsonConvert.DeserializeObject>(msg3); //判断权限 if (!ClsPub.Security_Log("Gy_PNLInfo_Edit", 1, false, msg4)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "没有新增权限！"; objJsonResult.data = null; return objJsonResult; } oCN.BeginTran(); DataSet ds_editCheck; //保存前控制========================================= ds_editCheck = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_BeforeSaveCtrl " + msg4 + ",'1'", "h_p_Gy_PNLInfo_BeforeSaveCtrl"); if (ds_editCheck == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "保存前判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "保存失败！" + DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= ds_editCheck = oCN.RunProcReturn("select top 1 HInterID from Gy_PNLInfo where HLayOutName=N'" + list[0].HLayOutName + "'", "Gy_PNLInfo"); if(ds_editCheck.Tables[0].Rows.Count > 0) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "PNL布局重复！"; objJsonResult.data = null; return objJsonResult; } string sql1 = "insert into Gy_PNLInfo(HLayOutName,HIcon,HRowCount,HColCount,HMaker,HMakeDate,HPNLPosition_x,HPNLPosition_y) values(" + "N'" + list[0].HLayOutName + "'," + "N'" + list[0].HIcon + "'," + "N'" + list[0].HRowCount + "'," + "N'" + list[0].HColCount + "'," + "N'" + list[0].HMaker + "'," + "N'" + list[0].HMakeDate + "'," + "N'" + list[0].HPNLPosition_x + "'," + "N'" + list[0].HPNLPosition_y + "'" + ")"; //LogService.Write("执行sql语句:" + sql1); DataSet ds = oCN.RunProcReturn(sql1, "Gy_PNLInfo"); ds = oCN.RunProcReturn("Select TOP 1 HInterID from Gy_PNLInfo where HLayOutName=N'" + list[0].HLayOutName + "'", "Gy_PNLInfo"); if(ds.Tables[0].Rows.Count < 1) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "内码不存在！"; objJsonResult.data = null; return objJsonResult; } string currHInterID = ds.Tables[0].Rows[0]["HInterID"].ToString(); HashSet HSetNoHashSet = new HashSet(); HashSet HPCSNoHashSet = new HashSet(); List HUpdateLangList = new List(); string insertLangTemplet = "insert into Gy_PNLInfoSub(HInterID,HLayOutName,HSetNo,HPCSNo,HPosition_x,HPosition_y" + ",HSetPosition_y, HSetPosition_x ) values( "; int batchNum = 100; StringBuilder updateLangBuilder = new StringBuilder(); foreach (var lsone in lsmain) { if(HPCSNoHashSet.Contains(lsone.HPCSNo) == true) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "PCS码重复！"; objJsonResult.data = null; return objJsonResult; } HSetNoHashSet.Add(lsone.HSetNo); HPCSNoHashSet.Add(lsone.HPCSNo); batchNum--; updateLangBuilder.Append(insertLangTemplet); updateLangBuilder.Append("N'" + currHInterID + "',"); updateLangBuilder.Append("N'" + lsone.HLayOutName + "',"); updateLangBuilder.Append("N'" + lsone.HSetNo + "',"); updateLangBuilder.Append("N'" + lsone.HPCSNo + "',"); updateLangBuilder.Append("N'" + lsone.HPosition_x + "',"); updateLangBuilder.Append("N'" + lsone.HPosition_y + "',"); updateLangBuilder.Append("N'" + lsone.HSetPosition_y + "',"); updateLangBuilder.Append("N'" + lsone.HSetPosition_x + "'"); updateLangBuilder.Append(");"); if (batchNum == 0) { HUpdateLangList.Add(updateLangBuilder.ToString()); updateLangBuilder.Clear(); batchNum = 100; } } HUpdateLangList.Add(updateLangBuilder.ToString()); updateLangBuilder.Clear(); string HSetNoInStr = "(" + string.Join(",", HSetNoHashSet.ToArray()) + ")"; string HPCSNoInStr = "(" + string.Join(",", HPCSNoHashSet.ToArray()) + ")"; // 判断是否有set码或者PCS码重复的部分 ds = oCN.RunProcReturn("select top 1 HEntryID from Gy_PNLInfoSub where HSetNo in " + HSetNoInStr + "AND HPCSNo in " + HPCSNoInStr + "AND HInterID = " + currHInterID, "Gy_PNLInfoSub"); //LogService.Write("执行 SQL 语句: select top 1 HEntryID from Gy_PNLInfoSub where HSetNo in " + HSetNoInStr // + "AND HPCSNo in " + HPCSNoInStr); if(ds.Tables[0].Rows.Count > 0) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "Set码或者PCS码有重复！"; objJsonResult.data = null; return objJsonResult; } HUpdateLangList.ForEach(HUpdateLang => { //LogService.Write("执行 SQL 语句: " + HUpdateLang); ds = oCN.RunProcReturn(HUpdateLang, "Gy_PNLInfoSub"); }); //保存后控制========================================= ds_editCheck = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_AfterSaveCtrl " + msg4 + ",'1'", "h_p_Gy_PNLInfo_AfterSaveCtrl"); if (ds_editCheck == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "保存后判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "" + DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= oCN.Commit(); objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "保存成功！"; //objJsonResult.data = null; return objJsonResult; } catch (Exception e) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "Exception！" + e.Message; objJsonResult.data = null; return objJsonResult; } } #endregion #region 删除PNLInfo 删除子表 [Route("Gy_PNLInfoController/DeleteBill")] [HttpGet] public object DeleteBill(string HInterID, string user) { try { //判断权限 if (!ClsPub.Security_Log("Gy_PNLInfo_Drop", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "没有删除权限！"; objJsonResult.data = null; return objJsonResult; } DataSet ds_editCheck; oCN.BeginTran(); //删除前控制========================================= ds_editCheck = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_BeforeDelCtrl " + HInterID + "," + user, "h_p_Gy_PNLInfo_BeforeDelCtrl"); if (ds_editCheck == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "删除前判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "删除失败！" + DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= ds = oCN.RunProcReturn("delete from Gy_PNLInfo where HInterID = " + HInterID, "Gy_PNLInfo"); ds = oCN.RunProcReturn("delete from Gy_PNLInfoSub where HInterID = " + HInterID, "Gy_PNLInfoSub"); // 删除子表 //删除后控制========================================= ds_editCheck = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_AfterDelCtrl " + HInterID + "," + user, "h_p_Gy_PNLInfo_AfterDelCtrl"); if (ds_editCheck == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "删除后判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "删除失败" + DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= oCN.Commit(); objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "删除成功！"; objJsonResult.data = null; return objJsonResult; } catch (Exception e) { //LogService.Write(e); oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "Exception！" + e.Message; objJsonResult.data = null; return objJsonResult; } } #endregion #region 更新PNLInfo 更新子表 [Route("Gy_PNLInfoController/EditBill")] [HttpPost] public object EditBill([FromBody] JObject oMain) { try { var _value = oMain["oMain"].ToString(); string msg1 = _value.ToString(); string[] sArray = msg1.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); string msg2 = sArray[0].ToString(); // 表单信息 string msg3 = sArray[1].ToString();// 子表信息 string msg4 = sArray[2].ToString(); // 制单人信息 string msg5 = sArray[3].ToString(); // 删除表单信息(HEntryID 列表) //反序列化 msg2 = "[" + msg2.ToString() + "]"; List list = JsonConvert.DeserializeObject>(msg2); List lsmain = JsonConvert.DeserializeObject>(msg3); List delEntryIDList = JsonConvert.DeserializeObject>(msg5); //判断权限 if (!ClsPub.Security_Log("Gy_PNLInfo_Edit", 1, false, msg4)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "没有新增权限！"; objJsonResult.data = null; return objJsonResult; } oCN.BeginTran(); DataSet ds_editCheck; //保存前控制========================================= ds_editCheck = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_BeforeSaveCtrl " + msg4 + ",'2'", "h_p_Gy_PNLInfo_BeforeSaveCtrl"); if (ds_editCheck == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "保存前判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "保存失败！" + DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= ds_editCheck = oCN.RunProcReturn("select top 1 HInterID from Gy_PNLInfo where HLayOutName=N'" + list[0].HLayOutName + "'" + "AND HInterID != N'" + list[0].HInterID + "'", "Gy_PNLInfo"); if (ds_editCheck.Tables[0].Rows.Count > 0) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "PNL布局重复！"; objJsonResult.data = null; return objJsonResult; } // 主表 更新 StringBuilder sb = new StringBuilder(); sb.Append("Update Gy_PNLInfo set "); sb.Append("HLayOutName=N'"+ list[0].HLayOutName + "',"); sb.Append("HIcon=N'" + list[0].HIcon + "',"); sb.Append("HRowCount=N'" + list[0].HRowCount + "',"); sb.Append("HColCount=N'" + list[0].HColCount + "',"); sb.Append("HPNLPosition_x=N'" + list[0].HPNLPosition_x + "',"); sb.Append("HPNLPosition_y=N'" + list[0].HPNLPosition_y + "',"); sb.Append("HModifyMan=N'" + msg4 + "',"); sb.Append("HModifyDate=getdate()"); sb.Append(" where HInterID=N'" + list[0].HInterID + "'"); string sql1 = sb.ToString(); oCN.RunProcReturn(sql1, "Gy_PNLInfo"); oCN.RunProcReturn("Update Gy_PNLInfoSub set HLayOutName = N'" + list[0].HLayOutName + "'" + " where HInterID=N'" + list[0].HInterID + "'", "Gy_PNLInfoSub"); sb.Clear(); // 构建删除 更新语句 List HUpdateLangList = new List(); int batchNum = 100; HashSet HSetNoHashSet = new HashSet(); HashSet HPCSNoHashSet = new HashSet(); foreach( var lsone in lsmain) { batchNum--; if (HPCSNoHashSet.Contains(lsone.HPCSNo) == true) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "PCS码重复！"; objJsonResult.data = null; return objJsonResult; } HSetNoHashSet.Add(lsone.HSetNo); HPCSNoHashSet.Add(lsone.HPCSNo); if (lsone.HEntryID != 0) { delEntryIDList.Add(lsone.HEntryID); } sb.Append("insert into Gy_PNLInfoSub(HInterID,"); sb.Append("HLayOutName, HSetNo, HPCSNo, HPosition_x, HPosition_y" + ",HSetPosition_y, HSetPosition_x ) values( "); sb.Append("N'" + lsone.HInterID + "',"); sb.Append("N'" + lsone.HLayOutName + "',"); sb.Append("N'" + lsone.HSetNo + "',"); sb.Append("N'" + lsone.HPCSNo + "',"); sb.Append("N'" + lsone.HPosition_x + "',"); sb.Append("N'" + lsone.HPosition_y + "',"); sb.Append("N'" + lsone.HSetPosition_y + "',"); sb.Append("N'" + lsone.HSetPosition_x + "'"); sb.Append(");"); if (batchNum == 0) { HUpdateLangList.Add(sb.ToString()); sb.Clear(); batchNum = 100; } } HUpdateLangList.Add(sb.ToString()); sb.Clear(); // 子表删除 string delWhereStr = "(" + string.Join(",", delEntryIDList.ToArray()) + ")"; string sql2 = "Delete from Gy_PNLInfoSub where HEntryID in " + delWhereStr; LogService.Write("执行SQL语句：" + sql2); oCN.RunProcReturn(sql2, "Gy_PNLInfoSub"); // 增加前判断 当前需要新增的数据中是否有PCS码和SET码重复的部分 string HSetNoInStr = "(" + string.Join(",", HSetNoHashSet.ToArray()) + ")"; string HPCSNoInStr = "(" + string.Join(",", HPCSNoHashSet.ToArray()) + ")"; // 判断是否有set码或者PCS码重复的部分 ds = oCN.RunProcReturn("select top 1 HEntryID from Gy_PNLInfoSub where HSetNo in " + HSetNoInStr + " AND HPCSNo in " + HPCSNoInStr + "and HInterID = " + list[0].HInterID , "Gy_PNLInfoSub"); LogService.Write("执行 SQL 语句: select top 1 HEntryID from Gy_PNLInfoSub where HSetNo in " + HSetNoInStr + " AND HPCSNo in " + HPCSNoInStr + "and HInterID = " + list[0].HInterID); if (ds.Tables[0].Rows.Count > 0) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "set码和PCS码有重复！"; objJsonResult.data = null; return objJsonResult; } // 子表 新增 HUpdateLangList.ForEach(HUpdateLang => { ds = oCN.RunProcReturn(HUpdateLang, "Gy_PNLInfoSub"); } ); //保存后控制========================================= ds_editCheck = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_AfterSaveCtrl " + msg4 + ",'2'", "h_p_Gy_PNLInfo_AfterSaveCtrl"); if (ds_editCheck == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "保存后判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "" + DBUtility.ClsPub.isStrNull(ds_editCheck.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= oCN.Commit(); objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "保存成功！"; //objJsonResult.data = null; return objJsonResult; } catch (Exception e) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "Exception！" + e.Message; objJsonResult.data = null; return objJsonResult; } } #endregion #region 审核PNL布局 [Route("Gy_PNLInfoController/CheckBill")] [HttpGet] public object CheckBill(int HInterID, string user, int IsAudit) { try { //审核权限 if (!DBUtility.ClsPub.Security_Log_second("Gy_PNLInfo_Check", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "审核失败！无权限！"; objJsonResult.data = null; return objJsonResult; } var ds = oCN.RunProcReturn("select top 1 HCheckMan from Gy_PNLInfo where HInterID=" + HInterID, "Gy_PNLInfo"); if (ds.Tables[0].Rows.Count > 0) { if (IsAudit == 0) //审核判断 { if (string.IsNullOrEmpty(ds.Tables[0].Rows[0]["HCheckMan"].ToString()) != true) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "单据已审核!不能再次审核！"; objJsonResult.data = null; return objJsonResult; } } if (IsAudit == 1) //反审核判断 { if (string.IsNullOrEmpty(ds.Tables[0].Rows[0]["HCheckMan"].ToString()) == true) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "单据未审核!不需要反审核!"; objJsonResult.data = null; return objJsonResult; } } } else { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "单据不存在!"; objJsonResult.data = null; return objJsonResult; } oCN.BeginTran(); if (IsAudit == 0) //审核判断 { //审核前控制========================================= ds = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_BeforeCheckCtrl " + HInterID + ",'" + user + "'", "h_p_Gy_PNLInfo_BeforeCheckCtrl"); if (ds == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "审核前判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "审核失败！" + DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= oCN.RunProc("update Gy_PNLInfo set HCheckMan='" + user + "',HCheckDate=getdate() where HInterID=" + HInterID); //审核后控制========================================= DataSet ds2 = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_AfterCheckCtrl " + HInterID + ",'" + user + "'", "h_p_Gy_PNLInfo_AfterCheckCtrl"); if (ds2 == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "审核后判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds2.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "审核失败！" + DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "审核成功"; objJsonResult.data = null; } if (IsAudit == 1) //反审核判断 { //反审核前控制========================================= ds = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_BeforeUnCheckCtrl " + HInterID + ",'" + user + "'", "h_p_Gy_PNLInfo_BeforeUnCheckCtrl"); if (ds == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "审核前判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "审核失败！" + DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= oCN.RunProc("update Gy_PNLInfo set HCheckMan='',HCheckDate=null where HInterID=" + HInterID); //反审核后控制========================================= DataSet ds2 = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_AfterUnCheckCtrl " + HInterID + ",'" + user + "'", "h_p_Gy_PNLInfo_AfterUnCheckCtrl"); if (ds2 == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "审核后判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds2.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "审核失败！" + DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "反审核成功"; objJsonResult.data = null; } oCN.Commit(); return objJsonResult; } catch (Exception e) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "审核失败或者反审核失败！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } #endregion #region 禁用PNL布局 [Route("Gy_PNLInfoController/StopBill")] [HttpGet] public object StopBill(int HInterID, string user, int IsStop) { try { //禁用权限 if (!DBUtility.ClsPub.Security_Log_second("Gy_PNLInfo_Stop", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "禁用失败！无权限！"; objJsonResult.data = null; return objJsonResult; } var ds = oCN.RunProcReturn("select top 1 HStopMan from Gy_PNLInfo where HInterID=" + HInterID, "Gy_PNLInfo"); if (ds.Tables[0].Rows.Count > 0) { if (IsStop == 0) //禁用判断 { if (ds.Tables[0].Rows[0]["HStopMan"].ToString() != "") { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "单据已禁用!不能再次禁用！"; objJsonResult.data = null; return objJsonResult; } } if (IsStop == 1) //反禁用判断 { if (ds.Tables[0].Rows[0]["HStopMan"].ToString() == "") { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "单据未禁用!不需要反禁用!"; objJsonResult.data = null; return objJsonResult; } } } else { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "单据不存在!"; objJsonResult.data = null; return objJsonResult; } oCN.BeginTran(); if (IsStop == 0) //禁用判断 { //禁用前控制========================================= ds = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_BeforeStopCtrl " + HInterID + ",'" + user + "'", "h_p_Gy_PNLInfo_BeforeStopCtrl"); if (ds == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "禁用前判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "禁用失败！" + DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= oCN.RunProc("update Gy_PNLInfo set HStopMan='" + user + "',HStopDate=getdate() where HInterID=" + HInterID); //禁用后控制========================================= DataSet ds2 = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_AfterStopCtrl " + HInterID + ",'" + user + "'", "h_p_Gy_PNLInfo_AfterStopCtrl"); if (ds2 == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "禁用后判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds2.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "禁用失败！" + DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "禁用成功"; objJsonResult.data = null; } if (IsStop == 1) //反禁用判断 { //反禁用前控制========================================= ds = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_BeforeUnStopCtrl " + HInterID + ",'" + user + "'", "h_p_Gy_PNLInfo_BeforeUnStopCtrl"); if (ds == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "禁用前判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "禁用失败！" + DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= oCN.RunProc("update Gy_PNLInfo set HStopMan='',HStopDate=null where HInterID=" + HInterID); //反禁用后控制========================================= DataSet ds2 = oCN.RunProcReturn("Exec h_p_Gy_PNLInfo_AfterUnStopCtrl " + HInterID + ",'" + user + "'", "h_p_Gy_PNLInfo_AfterUnStopCtrl"); if (ds2 == null) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "禁用后判断失败！"; objJsonResult.data = null; return objJsonResult; } if (DBUtility.ClsPub.isStrNull(ds2.Tables[0].Rows[0]["HBack"]) != "0") { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "禁用失败！" + DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HBackRemark"]); objJsonResult.data = null; return objJsonResult; } //========================================================= objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "反禁用成功"; objJsonResult.data = null; } oCN.Commit(); return objJsonResult; } catch (Exception e) { oCN.RollBack(); objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "禁用失败或者反禁用失败！" + e.ToString(); objJsonResult.data = null; return objJsonResult; } } #endregion #region PNL布局信息 文件上传 [Route("Gy_PNLInfo/Gy_PNLInfo_Excel")] [HttpPost] public object Gy_PNLInfo_Excel() { 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, 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("Set号", typeof(string)); tb2.Columns.Add("行索引", typeof(Int32)); tb2.Columns.Add("列索引", typeof(Int32)); tb2.Columns.Add("HPNLInfoTypeID", typeof(Int32)); //添加数据 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("PCS号")) error += "上传文件中没有找到【PCS号】的标题,"; if (error.Length > 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = $"Excel模板存在错误,{error}\r\n"; objJsonResult.data = null; return objJsonResult; } string HLayOutName_temp = ""; string HPNLInfoTypeName_temp = ""; List HPCSNo_Array = new List(); if (tb2.Rows.Count > 0) { HLayOutName_temp = DBUtility.ClsPub.isStrNull(tb2.Rows[0]["料号"].ToString()); HPNLInfoTypeName_temp = DBUtility.ClsPub.isStrNull(tb2.Rows[0]["分类"].ToString()); } for (int i = 0; i <= tb2.Rows.Count - 1; i++) { string HLayOutName = ""; //料号 string HPNLInfoTypeName = ""; //分类 string HPCSNo = ""; //PCS号 HLayOutName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["料号"].ToString()); HPNLInfoTypeName = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["分类"].ToString()); HPCSNo = DBUtility.ClsPub.isStrNull(tb2.Rows[i]["PCS号"].ToString()); //检查数据是否完整 int index = i + 2; if (HLayOutName != "") { if (HLayOutName != HLayOutName_temp) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,【料号】与首行数据中【料号】不一致！"; objJsonResult.data = null; return objJsonResult; } if (HPNLInfoTypeName != HPNLInfoTypeName_temp) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,【分类】与首行数据中【分类】不一致！"; objJsonResult.data = null; return objJsonResult; } if (HPCSNo_Array.Contains(HPCSNo)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,【PCS号】存在重复！"; objJsonResult.data = null; return objJsonResult; } else { HPCSNo_Array.Add(HPCSNo); } //查询料号资料是否已经存在 ds = oCN.RunProcReturn("select * from Gy_PNLInfo where HLayOutName = '" + HLayOutName + "' ", "Gy_PNLInfo"); if (ds.Tables[0].Rows.Count > 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,料号【" + HLayOutName + "】资料已经存在！"; objJsonResult.data = null; return objJsonResult; } //查询 分类 ds = oCN.RunProcReturn("select * from Gy_PNLInfoType where HName = '" + HPNLInfoTypeName + "' ", "Gy_PNLInfoType"); if (ds.Tables[0].Rows.Count == 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,分类【" + HPNLInfoTypeName + "】不存在！"; objJsonResult.data = null; return objJsonResult; } else { tb2.Rows[i]["HPNLInfoTypeID"] = ds.Tables[0].Rows[0]["HInterID"]; } //判断PCS号格式是否正确 if (!Regex.IsMatch(HPCSNo, @"^\d{6}$")) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,PCS号【" + HPCSNo + "】格式错误，应设置为由6位数字组成！"; objJsonResult.data = null; return objJsonResult; } else { tb2.Rows[i]["Set号"] = HPCSNo.Substring(0, 2); tb2.Rows[i]["行索引"] = int.Parse(HPCSNo.Substring(2, 2)); tb2.Rows[i]["列索引"] = int.Parse(HPCSNo.Substring(4, 2)); } } } 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 PNL布局信息 导入(保存) [Route("Gy_PNLInfo/Gy_PNLInfo_btnSave")] [HttpPost] public object Gy_PNLInfo_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_PNLInfo_Edit", 1, false, user)) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "无保存权限！"; objJsonResult.data = null; return objJsonResult; } List Excel = Newtonsoft.Json.JsonConvert.DeserializeObject>(msg2); List> list = new List>(); foreach (JObject item in Excel) { Dictionary dic = new Dictionary(); foreach (var itm in item.Properties()) { dic.Add(itm.Name, itm.Value.ToString()); } list.Add(dic); } oCN.BeginTran(); int i = 1; foreach (Dictionary item in list) { string HInterID = "0"; string HLayOutName = item["料号"].ToString(); //料号 string HPNLInfoTypeID = item["HPNLInfoTypeID"].ToString(); //分类ID string HPNLInfoTypeName = item["分类"].ToString(); //分类 string HSetNo = item["Set号"].ToString(); //Set号 string HPCSNo = item["PCS号"].ToString(); //PCS号 string HPosition_x = item["行索引"].ToString(); //行索引 string HPosition_y = item["列索引"].ToString(); //列索引 //检查数据是否完整 int index = i; if (i == 1) { //查询料号资料是否已经存在 ds = oCN.RunProcReturn("select * from Gy_PNLInfo where HLayOutName = '" + HLayOutName + "' ", "Gy_PNLInfo"); if (ds.Tables[0].Rows.Count > 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,料号【+ " + HLayOutName + "】资料已经存在！"; objJsonResult.data = null; return objJsonResult; } string HIcon = "/img/智能芯片＊.png"; string HMaker = user; string sql_main = "insert into Gy_PNLInfo(HLayOutName,HIcon,HMaker,HMakeDate,HPNLInfoTypeID) " + "values(" + "'" + HLayOutName + "'" + ",'" + HIcon + "'" + ",'" + HMaker + "'" + ",getdate()" + "," + HPNLInfoTypeID + "" + ")"; oCN.RunProc(sql_main); } string sql_getInterID = "select * from Gy_PNLInfo where HLayOutName = '" + HLayOutName + "'"; ds = oCN.RunProcReturn(sql_getInterID, "Gy_PNLInfo"); if (ds == null || ds.Tables[0].Rows.Count == 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "PNL布局信息导入失败！！"; objJsonResult.data = null; return objJsonResult; } else { HInterID = DBUtility.ClsPub.isStrNull(ds.Tables[0].Rows[0]["HInterID"]); } //查询 分类 ds = oCN.RunProcReturn("select * from Gy_PNLInfoType where HName = '" + HPNLInfoTypeName + "' ", "Gy_PNLInfoType"); if (ds.Tables[0].Rows.Count == 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,分类【+ " + HPNLInfoTypeName + "】不存在！"; objJsonResult.data = null; return objJsonResult; } //判断PCS号格式是否正确 if (!Regex.IsMatch(HPCSNo, @"^\d{6}$")) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "第" + index + "行,PCS号【" + HPCSNo + "】格式错误，应设置为由6位数字组成！"; objJsonResult.data = null; return objJsonResult; } string sql_sub = "insert into Gy_PNLInfoSub(HInterID,HLayOutName,HSetNo,HPCSNo,HPosition_x,HPosition_y) " + "values(" + "" + HInterID + "" + ",'" + HLayOutName + "'" + ",'" + HSetNo + "'" + ",'" + HPCSNo + "'" + "," + HPosition_x + "" + "," + HPosition_y + "" + ")"; oCN.RunProc(sql_sub); 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 } }