/******************************************************************************* * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential * 类的信息： * 1.程序名称：PCModuleLogic.cs * 2.功能描述：生产配置服务端业务逻辑 * 编辑履历： * 作者 日期 版本 修改内容 * 陈晓野 2015/03/30 1.00 新建 *******************************************************************************/ using Dongke.IBOSS.PRD.Basics.BaseResources; using Dongke.IBOSS.PRD.Basics.DataAccess; using Dongke.IBOSS.PRD.Service.CMNModuleService; using Dongke.IBOSS.PRD.Service.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels; using Dongke.WinForm.Utilities; using Oracle.ManagedDataAccess.Client; using SAP.Middleware.Connector; using System; using System.Collections.Generic; using System.Data; using System.Text; using System.Configuration; using Dongke.IBOSS.PRD.Basics.Library; namespace Dongke.IBOSS.PRD.Service.PCModuleService { /// /// 生产配置服务端业务逻辑 /// public static class PCModuleLogic { //SAP服务器配置参数 //public static string appServerHost = ConfigurationManager.AppSettings["SapAppServerHost"].ToString(); //public static string systemNumber = ConfigurationManager.AppSettings["SapSystemNumber"].ToString(); //public static string user = ConfigurationManager.AppSettings["SapUser"].ToString(); //public static string password = ConfigurationManager.AppSettings["SapPassword"].ToString(); //public static string client = ConfigurationManager.AppSettings["SapClient"].ToString(); //SAP服务器配置参数 public static Basics.Library.INIUtility ini = Basics.Library.INIUtility.Instance(Basics.Library.INIUtility.IniFile.SAP_HEGII); public static string appServerHost = ini.ReadIniData("SAP_RFC_INFO", "SapAppServerHost"); public static string systemNumber = ini.ReadIniData("SAP_RFC_INFO", "SapSystemNumber"); public static string user = ini.ReadIniData("SAP_RFC_INFO", "SapUser"); public static string password = ini.ReadIniData("SAP_RFC_INFO", "SapPassword"); public static string client = ini.ReadIniData("SAP_RFC_INFO", "SapClient"); #region 成型线管理 #region 成型线一览 /// /// 取得FPC0101画面（成型线管理）的初始化数据 /// /// 登录用户信息 /// 初始化数据 public static ServiceResultEntity GetFPC0101IData(SUserInfo user) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); DataTable type = CMNModuleLogic.GetTP_MST_GMouldType(conn, user, Constant.ValueFlag.Effective); DataTable status = CMNModuleLogic.GetTP_SYS_GMouldStatus(conn, true); ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = new DataSet(); sre.Data.Tables.Add(type); sre.Data.Tables.Add(status); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得FPC0101画面（成型线管理）的查询数据 /// /// 登录用户信息 /// 查询条件 /// 查询结果 public static ServiceResultEntity GetFPC0101SData(SUserInfo user, FPC0101_SE se) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); StringBuilder sql = new StringBuilder(PCModuleLogic.GetGroutingLineSQL()); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); if (se != null) { // 成型线范围权限 if (se.PurviewType.HasValue) { sql.Append(" AND EXISTS (SELECT UP.PurviewID"); sql.Append(" FROM TP_MST_UserPurview UP"); sql.Append(" WHERE UP.PurviewType = '" + (se.PurviewType.Value ? (int)Constant.PurviewType.OperateGroutingLine : (int)Constant.PurviewType.ViewGroutingLine) + "'"); sql.Append(" AND (UP.PurviewID = -1 OR UP.PurviewID = gl.GroutingLineID)"); sql.Append(" AND UP.UserId = :UPUserId) "); parameters.Add(new OracleParameter(":UPUserId", OracleDbType.Int32, user.UserID, ParameterDirection.Input)); } // 楼号 if (!string.IsNullOrEmpty(se.BuildingNo)) { sql.Append(" AND INSTR(gl.BuildingNo, :BuildingNo) > 0"); parameters.Add(new OracleParameter(":BuildingNo", OracleDbType.NVarchar2, se.BuildingNo, ParameterDirection.Input)); } // 楼层 if (!string.IsNullOrEmpty(se.FloorNo)) { sql.Append(" AND INSTR(gl.FloorNo, :FloorNo) > 0"); parameters.Add(new OracleParameter(":FloorNo", OracleDbType.NVarchar2, se.FloorNo, ParameterDirection.Input)); } // 线号 if (!string.IsNullOrEmpty(se.GroutingLineNo)) { sql.Append(" AND INSTR(gl.GroutingLineNo, :GroutingLineNo) > 0"); parameters.Add(new OracleParameter(":GroutingLineNo", OracleDbType.NVarchar2, se.GroutingLineNo, ParameterDirection.Input)); } // 成型线编码 if (!string.IsNullOrEmpty(se.GroutingLineCode)) { sql.Append(" AND INSTR(gl.GroutingLineCode, :GroutingLineCode) > 0"); parameters.Add(new OracleParameter(":GroutingLineCode", OracleDbType.NVarchar2, se.GroutingLineCode, ParameterDirection.Input)); } // 成型线名称 if (!string.IsNullOrEmpty(se.GroutingLineName)) { sql.Append(" AND INSTR(gl.GroutingLineName, :GroutingLineName) > 0"); parameters.Add(new OracleParameter(":GroutingLineName", OracleDbType.NVarchar2, se.GroutingLineName, ParameterDirection.Input)); } // 成型线类别 if (se.GMouldTypeID.HasValue) { sql.Append(" AND gl.GMouldTypeID = :GMouldTypeID"); parameters.Add(new OracleParameter(":GMouldTypeID", OracleDbType.Int32, se.GMouldTypeID.Value, ParameterDirection.Input)); } // 成型线状态 if (se.GMouldStatus.HasValue) { sql.Append(" AND gl.GMouldStatus = :GMouldStatus"); parameters.Add(new OracleParameter(":GMouldStatus", OracleDbType.Int32, se.GMouldStatus.Value, ParameterDirection.Input)); } // 成型工号 if (!string.IsNullOrEmpty(se.GUserCode)) { sql.Append(" AND INSTR(U.UserCode, :GroutingUserCode) > 0"); parameters.Add(new OracleParameter(":GroutingUserCode", OracleDbType.NVarchar2, se.GUserCode, ParameterDirection.Input)); } // 开始使用日期-开始 if (se.BeginUsedDateBegin.HasValue) { sql.Append(" AND gl.BeginUsedDate >= :BeginUsedDateBegin"); parameters.Add(new OracleParameter(":BeginUsedDateBegin", OracleDbType.Date, se.BeginUsedDateBegin.Value, ParameterDirection.Input)); } // 开始使用日期-结束 if (se.BeginUsedDateEnd.HasValue) { sql.Append(" AND gl.BeginUsedDate <= :BeginUsedDateEnd"); parameters.Add(new OracleParameter(":BeginUsedDateEnd", OracleDbType.Date, se.BeginUsedDateEnd.Value, ParameterDirection.Input)); } // 结束使用日期-开始 if (se.EndUsedDateBegin.HasValue) { sql.Append(" AND gl.EndUsedDate >= :EndUsedDateBegin"); parameters.Add(new OracleParameter(":EndUsedDateBegin", OracleDbType.Date, se.EndUsedDateBegin.Value, ParameterDirection.Input)); } // 结束使用日期-结束 if (se.EndUsedDateEnd.HasValue) { sql.Append(" AND gl.EndUsedDate <= :EndUsedDateEnd"); parameters.Add(new OracleParameter(":EndUsedDateEnd", OracleDbType.Date, se.EndUsedDateEnd.Value, ParameterDirection.Input)); } // 产品编号 if (!string.IsNullOrEmpty(se.GoodsCode)) { sql.Append(" AND EXISTS(SELECT 1"); sql.Append(" FROM tp_pc_groutinglinedetail gdd"); sql.Append(" INNER JOIN tp_mst_goods g"); sql.Append(" ON g.goodsid = gdd.goodsid"); sql.Append(" WHERE gdd.groutinglineid = gl.groutinglineid"); sql.Append(" AND INSTR(g.GoodsCode, :GoodsCode) > 0"); sql.Append(" )"); parameters.Add(new OracleParameter(":GoodsCode", OracleDbType.NVarchar2, se.GoodsCode, ParameterDirection.Input)); } // 备注 if (!string.IsNullOrEmpty(se.Remarks)) { sql.Append(" AND INSTR(gl.Remarks, :Remarks) > 0"); parameters.Add(new OracleParameter(":Remarks", OracleDbType.NVarchar2, se.Remarks, ParameterDirection.Input)); } } sql.Append(" ORDER BY gl.GroutingLineCode"); DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); if (data == null || data.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data = new DataSet(); sre.Data.Tables.Add(data); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":GroutingLineID", OracleDbType.Int32, data.Rows[0]["GroutingLineID"], ParameterDirection.Input), }; DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetGroutingLineDetailSQL(), paras); if (dData != null) { sre.Data.Tables.Add(dData); } return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得FPC0101画面（成型线管理）的查询数据 /// /// 登录用户信息 /// 多个注浆id（用[,]分开） /// 查询结果 public static ServiceResultEntity GetFPC0101SData(SUserInfo user, string ids) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); StringBuilder sql = new StringBuilder(PCModuleLogic.GetGroutingLineSQL()); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); //if (!string.IsNullOrWhiteSpace(ids)) { ids = "," + ids + ","; sql.Append(" AND INSTR(:GroutingLineIDs, ',' || gl.GroutingLineID || ',') > 0"); parameters.Add(new OracleParameter(":GroutingLineIDs", OracleDbType.Varchar2, ids, ParameterDirection.Input)); } sql.Append(" ORDER BY gl.GroutingLineCode"); DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); if (data == null || data.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data = new DataSet(); sre.Data.Tables.Add(data); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":GroutingLineID", OracleDbType.Int32, data.Rows[0]["GroutingLineID"], ParameterDirection.Input), }; DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetGroutingLineDetailSQL(), paras); if (dData != null) { sre.Data.Tables.Add(dData); } return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得FPC0101画面（成型线管理）的查询成型线明细数据 /// /// 登录用户信息 /// 成型线ID /// 成型线明细数据 public static ServiceResultEntity GetFPC0101SDData(SUserInfo user, int id) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":GroutingLineID", OracleDbType.Int32, id, ParameterDirection.Input), }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(PCModuleLogic.GetGroutingLineDetailSQL(), paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 获取查询sql /// /// sql private static string GetGroutingLineSQL() { string selSql = "SELECT gl.GroutingLineID," + " gl.BuildingNo," + " gl.FloorNo," + " gl.GroutingLineNo," + " gl.GroutingLineCode," + " gl.GroutingLineName," + " gl.MouldQuantity," + " gl.userid," + " u.usercode," + " u.username," + " gl.gmouldtypeid," + " gmt.gmouldtypename," + " gl.gmouldstatus," + " gms.gmouldstatusname," + " gl.beginuseddate," + " gl.enduseddate," + " gl.remarks," + " gl.OPTimeStamp" + " FROM tp_pc_groutingline gl" + " INNER JOIN tp_mst_gmouldtype gmt" + " ON gl.gmouldtypeid = gmt.gmouldtypeid" + " INNER JOIN tp_sys_gmouldstatus gms" + " ON gl.gmouldstatus = gms.gmouldstatusid" + " INNER JOIN tp_mst_user u" + " ON u.userid = gl.userid" + // 账套 " WHERE gl.AccountID = :AccountID" + " AND gl.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'"; return selSql; } /// /// 获取查询sql /// /// sql private static string GetGroutingLineDetailSQL() { string selSql = "select gld.groutinglinedetailid," + " gld.groutinglineid," + " gld.groutingmouldcode," + " gld.goodsid," + " g.goodscode," + " g.goodsname," + " g.goodsspecification," + " gld.groutingcount," + " gld.gmouldstatus," + " gms.gmouldstatusname," + " gld.beginuseddate," + " gld.mouldcode," + " gld.remarks" + " from tp_pc_groutinglinedetail gld" + " inner join tp_mst_goods g" + " on g.goodsid = gld.goodsid" + " inner join tp_mst_goodstype gt" + " on gt.goodstypeid = g.goodstypeid" + " inner join tp_sys_gmouldstatus gms" + " on gld.gmouldstatus = gms.gmouldstatusid" + " where gld.groutinglineid = :GroutingLineID" + " and gld.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'" + " order by gld.groutingmouldcode"; return selSql; } /// /// FPC0101画面（成型线管理）停用、启用成型线 /// /// 登录用户信息 /// 成型线ID /// 时间戳 /// 有效标识 /// 操作结果 public static ServiceResultEntity SetFPC0101DataStatus(SUserInfo user, int groutingLineID, object opTimeStamp, Constant.ValueFlag valueFlag) { IDBTransaction tran = null; try { ServiceResultEntity sre = new ServiceResultEntity(); tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); int executeResult = 0; #region 校验时间戳 // 校验时间戳 string checkSql = "SELECT GroutingLineID" + " FROM TP_PC_GroutingLine" + " WHERE GroutingLineID = :GroutingLineID" + " AND OPTimeStamp = :OPTimeStamp" + " AND ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'"; OracleParameter[] checkParas = new OracleParameter[] { new OracleParameter(":GroutingLineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input), new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, opTimeStamp, ParameterDirection.Input), }; object checkResult = tran.GetSqlResultToObj(checkSql, checkParas); if (checkResult == null) { sre.Status = Constant.ServiceResultStatus.DataChanged; return sre; } #endregion 校验时间戳 StringBuilder sql = new StringBuilder("UPDATE TP_PC_GroutingLine"); if (valueFlag == Constant.ValueFlag.Invalid) { sql.Append(" SET GMouldStatus = '" + (int)Constant.GMouldStatus.Disable + "',"); sql.Append(" EndUsedDate = to_date(sysdate, 'yyyy-mm-dd')"); sql.Append(" WHERE GMouldStatus = '" + (int)Constant.GMouldStatus.Normal + "'"); } else if (valueFlag == Constant.ValueFlag.Effective) { sql.Append(" SET GMouldStatus = '" + (int)Constant.GMouldStatus.Normal + "',"); sql.Append(" BeginUsedDate = to_date(sysdate, 'yyyy-mm-dd'),"); sql.Append(" EndUsedDate = null"); sql.Append(" WHERE GMouldStatus = '" + (int)Constant.GMouldStatus.Disable + "'"); } sql.Append(" AND GroutingLineID = :GroutingLine"); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":GroutingLineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input), }; executeResult = tran.ExecuteNonQuery(sql.ToString(), paras); if (executeResult <= 0) { sre.Status = Constant.ServiceResultStatus.NoModifyData; return sre; } tran.Commit(); return sre; } catch (Exception ex) { if (tran != null && tran.ConnState == ConnectionState.Open) { tran.Rollback(); } throw ex; } finally { if (tran != null && tran.ConnState == ConnectionState.Open) { tran.Disconnect(); } } } #endregion 成型线一览 #region 成型线 /// /// 取得FPC0102画面（成型线管理）的查询数据 /// /// 登录用户信息 /// 成型线ID /// 查询结果 public static ServiceResultEntity GetFPC0102Data(SUserInfo user, int groutingLineID) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); StringBuilder sql = new StringBuilder(PCModuleLogic.GetGroutingLineSQL()); sql.Append(" AND gl.GroutingLineID = :GroutingLineID"); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":GroutingLineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input)); DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = new DataSet(); if (data != null) { sre.Data.Tables.Add(data); } OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":GroutingLineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input), }; DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetGroutingLineDetailSQL(), paras); if (dData != null) { dData.Columns["GroutingMouldCode"].Unique = true; sre.Data.Tables.Add(dData); } return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 保存FPC0102画面（成型线管理）的数据 /// /// 登录用户信息 /// 成型线数据 /// 保存结果 public static ServiceResultEntity SetFPC0102Data(SUserInfo user, DataSet groutingLineInfo) { IDBTransaction tran = null; try { ServiceResultEntity sre = new ServiceResultEntity(); if (groutingLineInfo == null || groutingLineInfo.Tables.Count != 2 || groutingLineInfo.Tables[0].Rows.Count < 1 || groutingLineInfo.Tables[1].Rows.Count < 1) { sre.Status = Constant.ServiceResultStatus.NoModifyData; return sre; } DataRow line = groutingLineInfo.Tables[0].Rows[0]; DataTable detail = groutingLineInfo.Tables[1]; int lineID = 0; object id = line["GroutingLine"]; if (id != null && id != DBNull.Value) { lineID = Convert.ToInt32(id); } tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); int executeResult = 0; // 新建成型线 if (lineID < 1) { #region 数据校验 成型线编码唯一 // 成型线编码唯一 string checkLineCodeSql = "SELECT GL.GroutingLineCode" + " FROM TP_PM_GroutingLine GL" + " WHERE GL.AccountID = :AccountID" + " AND GL.GroutingLineCode = :GroutingLineCode"; OracleParameter[] checkLineCodeParas = new OracleParameter[] { new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input), new OracleParameter(":GroutingLineCode", OracleDbType.NVarchar2, line["GroutingLineCode"], ParameterDirection.Input), }; DataTable checkLineCodeData = tran.GetSqlResultToDt(checkLineCodeSql, checkLineCodeParas); if (checkLineCodeData != null && checkLineCodeData.Rows.Count > 0) { sre.Status = Constant.ServiceResultStatus.DataDuplicated; sre.Message = string.Format("（成型线编码：{0}）", line["GroutingLineCode"]); } #endregion 数据校验 成型线编码唯一 #region 新建成型线 // 新建成型线 #region SQL string seqGroutingLineSql = "select SEQ_PC_GroutingLine_ID.Nextval from dual;"; #region LineSQL string insertLineSQL = "insert into tp_pc_groutingline" + " (groutinglineid," + " buildingno," + " floorno," + " groutinglineno," + " groutinglinecode," + " groutinglinename," + " mouldquantity," + " gmouldtypeid," + " userid," + //" MonitorID," + " beginuseddate," + " enduseddate," + " gmouldstatus," + " remarks," + " accountid," + " valueflag," + " createtime," + " createuserid," + " updatetime," + " updateuserid," + " optimestamp)" + "values" + " (:groutinglineid," + " :buildingno," + " :floorno," + " :groutinglineno," + " :groutinglinecode," + " :groutinglinename," + " :mouldquantity," + " :gmouldtypeid," + " :userid," + //" :MonitorID," + " to_date(sysdate, 'yyyy-mm-dd')," + " null," + " '" + (int)Constant.GMouldStatus.Normal + "'," + " :remarks," + " :accountid," + " '" + (int)Constant.ValueFlag.Effective + "'," + " sysdate," + " :opuserid," + " sysdate," + " :opuserid," + " systimestamp);"; #endregion LineSQL #region DetailSQL string insertDetailSQL = "insert into tp_pc_groutinglinedetail" + " (groutinglineid," + " groutingmouldcode," + " mouldcode," + " userid," + " goodsid," + " groutingcount," + " gmouldstatus," + " beginuseddate," + " enduseddate," + " remarks," + " accountid," + " valueflag," + " createtime," + " createuserid," + " updatetime," + " updateuserid," + " optimestamp)" + "values" + " (:groutinglineid," + " :groutingmouldcode," + " :mouldcode," + " :userid," + " :goodsid," + " 0," + " '" + (int)Constant.GMouldStatus.Normal + "'," + " to_date(sysdate, 'yyyy-mm-dd')," + " null," + " :remarks," + " :accountid," + " '" + (int)Constant.ValueFlag.Effective + "'," + " sysdate," + " :opuserid," + " sysdate," + " :opuserid," + " systimestamp);"; #endregion DetailSQL #endregion SQL object glSeq = tran.GetSqlResultToObj(seqGroutingLineSql); OracleParameter[] insertLineParas = new OracleParameter[] { new OracleParameter(":groutinglineid", OracleDbType.Int32, glSeq, ParameterDirection.Input), new OracleParameter(":buildingno", OracleDbType.NVarchar2, line["buildingno"], ParameterDirection.Input), new OracleParameter(":floorno", OracleDbType.NVarchar2, line["floorno"], ParameterDirection.Input), new OracleParameter(":groutinglineno", OracleDbType.NVarchar2, line["groutinglineno"], ParameterDirection.Input), new OracleParameter(":groutinglinecode", OracleDbType.NVarchar2, line["groutinglinecode"], ParameterDirection.Input), new OracleParameter(":groutinglinename", OracleDbType.NVarchar2, line["groutinglinename"], ParameterDirection.Input), new OracleParameter(":mouldquantity", OracleDbType.Decimal, line["mouldquantity"], ParameterDirection.Input), new OracleParameter(":gmouldtypeid", OracleDbType.Int32, line["gmouldtypeid"], ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, line["userid"], ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, line["userid"], ParameterDirection.Input), new OracleParameter(":remarks", OracleDbType.NVarchar2, line["remarks"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, user.AccountID, ParameterDirection.Input), new OracleParameter(":opuserid", OracleDbType.Int32, user.UserID, ParameterDirection.Input), }; executeResult = tran.ExecuteNonQuery(insertLineSQL, insertLineParas); if (executeResult <= 0) { sre.Status = Constant.ServiceResultStatus.NoModifyData; return sre; } foreach (DataRow row in detail.Rows) { if (row.RowState != DataRowState.Added) { continue; } OracleParameter[] insertDetailParas = new OracleParameter[] { new OracleParameter(":groutinglineid", OracleDbType.Int32, glSeq, ParameterDirection.Input), new OracleParameter(":groutingmouldcode", OracleDbType.NVarchar2, row["groutingmouldcode"], ParameterDirection.Input), new OracleParameter(":mouldcode", OracleDbType.NVarchar2, row["mouldcode"], ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, row["userid"], ParameterDirection.Input), new OracleParameter(":goodsid", OracleDbType.Int32, row["goodsid"], ParameterDirection.Input), new OracleParameter(":remarks", OracleDbType.NVarchar2, row["remarks"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, user.AccountID, ParameterDirection.Input), new OracleParameter(":opuserid", OracleDbType.Int32, user.UserID, ParameterDirection.Input), }; executeResult = tran.ExecuteNonQuery(insertDetailSQL, insertDetailParas); if (executeResult <= 0) { sre.Status = Constant.ServiceResultStatus.NoModifyData; return sre; } } #endregion 新建成型线 } // 编辑成型线 else { #region 校验时间戳 string checkSql = "SELECT GroutingLineID" + " FROM TP_PC_GroutingLine" + " WHERE GroutingLineID = :GroutingLineID" + " AND OPTimeStamp = :OPTimeStamp" + " AND ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'"; OracleParameter[] checkParas = new OracleParameter[] { new OracleParameter(":GroutingLineID", OracleDbType.Int32, lineID, ParameterDirection.Input), new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, line["OPTimeStamp"], ParameterDirection.Input), }; object checkResult = tran.GetSqlResultToObj(checkSql, checkParas); if (checkResult == null) { sre.Status = Constant.ServiceResultStatus.DataChanged; return sre; } #endregion 校验时间戳 #region 编辑成型线 #region LineSQL string updateLineSQL = "update tp_pc_groutingline" + " set groutinglinename = :groutinglinename," + " mouldquantity = :mouldquantity," + " gmouldtypeid = :gmouldtypeid," + " userid = :userid," + " remarks = :remarks," + " updateuserid = :opuserid" + " where groutinglineid = :groutinglineid;"; #endregion LineSQL #region DetailSQL string insertDetailSQL = "insert into tp_pc_groutinglinedetail" + " (groutinglineid," + " groutingmouldcode," + " mouldcode," + " userid," + " goodsid," + " groutingcount," + " gmouldstatus," + " beginuseddate," + " enduseddate," + " remarks," + " accountid," + " valueflag," + " createtime," + " createuserid," + " updatetime," + " updateuserid," + " optimestamp)" + "values" + " (:groutinglineid," + " :groutingmouldcode," + " :mouldcode," + " :userid," + " :goodsid," + " 0," + " '" + (int)Constant.GMouldStatus.Normal + "'," + " to_date(sysdate, 'yyyy-mm-dd')," + " null," + " :remarks," + " :accountid," + " '" + (int)Constant.ValueFlag.Effective + "'," + " sysdate," + " :opuserid," + " sysdate," + " :opuserid," + " systimestamp);"; string updateDetailSQL = "update tp_pc_groutinglinedetail" + " set remarks = :remarks, updateuserid = :opuserid" + " where groutinglinedetailid = :groutinglinedetailid;"; #endregion DetailSQL OracleParameter[] updateLineParas = new OracleParameter[] { new OracleParameter(":groutinglineid", OracleDbType.Int32, lineID, ParameterDirection.Input), new OracleParameter(":mouldquantity", OracleDbType.Decimal, line["mouldquantity"], ParameterDirection.Input), new OracleParameter(":gmouldtypeid", OracleDbType.Int32, line["gmouldtypeid"], ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, line["userid"], ParameterDirection.Input), new OracleParameter(":remarks", OracleDbType.NVarchar2, line["remarks"], ParameterDirection.Input), new OracleParameter(":opuserid", OracleDbType.Int32, user.UserID, ParameterDirection.Input), }; executeResult = tran.ExecuteNonQuery(updateLineSQL, updateLineParas); if (executeResult <= 0) { sre.Status = Constant.ServiceResultStatus.NoModifyData; return sre; } foreach (DataRow row in detail.Rows) { if (row.RowState == DataRowState.Added) { OracleParameter[] insertDetailParas = new OracleParameter[] { new OracleParameter(":groutinglineid", OracleDbType.Int32, lineID, ParameterDirection.Input), new OracleParameter(":groutingmouldcode", OracleDbType.NVarchar2, row["groutingmouldcode"], ParameterDirection.Input), new OracleParameter(":mouldcode", OracleDbType.NVarchar2, row["mouldcode"], ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, row["userid"], ParameterDirection.Input), new OracleParameter(":goodsid", OracleDbType.Int32, row["goodsid"], ParameterDirection.Input), new OracleParameter(":remarks", OracleDbType.NVarchar2, row["remarks"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, user.AccountID, ParameterDirection.Input), new OracleParameter(":opuserid", OracleDbType.Int32, user.UserID, ParameterDirection.Input), }; executeResult = tran.ExecuteNonQuery(insertDetailSQL, insertDetailParas); if (executeResult <= 0) { sre.Status = Constant.ServiceResultStatus.NoModifyData; return sre; } } if (row.RowState == DataRowState.Modified) { OracleParameter[] updateDetailParas = new OracleParameter[] { new OracleParameter(":groutinglinedetailid", OracleDbType.Int32, row["groutinglinedetailid"], ParameterDirection.Input), new OracleParameter(":remarks", OracleDbType.NVarchar2, row["remarks"], ParameterDirection.Input), new OracleParameter(":opuserid", OracleDbType.Int32, user.UserID, ParameterDirection.Input), }; executeResult = tran.ExecuteNonQuery(updateDetailSQL, updateDetailParas); if (executeResult <= 0) { sre.Status = Constant.ServiceResultStatus.NoModifyData; return sre; } } } #endregion 编辑成型线 } tran.Commit(); return sre; } catch (Exception ex) { if (tran != null && tran.ConnState == ConnectionState.Open) { tran.Rollback(); } throw ex; } finally { if (tran != null && tran.ConnState == ConnectionState.Open) { tran.Disconnect(); } } } #endregion 成型线 #region 模具管理 #endregion 模具管理 #endregion 成型线管理 #region 班次配置管理 /// /// 取得FPC0401画面（班次配置）的查询数据 /// /// 登录用户信息 /// 查询条件 /// 查询结果 public static ServiceResultEntity GetFPC0401SData(SUserInfo user, FPC0401_SE se) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); StringBuilder sql = new StringBuilder(PCModuleLogic.GetClassesSettingSQL()); List parameters = new List(); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":UPUserId", OracleDbType.Int32, user.UserID, ParameterDirection.Input)); if (se != null) { // 员工工号 if (!string.IsNullOrEmpty(se.UserCode)) { sql.Append(" AND INSTR(U.UserCode, :UserCode) > 0"); parameters.Add(new OracleParameter(":UserCode", OracleDbType.NVarchar2, se.UserCode, ParameterDirection.Input)); } // 开始时间 if (se.DateBegin.HasValue) { sql.Append(" AND C.AccountDate >= :DateBegin"); parameters.Add(new OracleParameter(":DateBegin", OracleDbType.Date, se.DateBegin, ParameterDirection.Input)); } // 结束时间 if (se.DateEnd.HasValue) { sql.Append(" AND C.AccountDate <= :DateEnd"); parameters.Add(new OracleParameter(":DateEnd", OracleDbType.Date, se.DateEnd, ParameterDirection.Input)); } // 员工工号 if (!string.IsNullOrEmpty(se.Remarks)) { sql.Append(" AND INSTR(C.Remarks, :Remarks) > 0"); parameters.Add(new OracleParameter(":Remarks", OracleDbType.NVarchar2, se.Remarks, ParameterDirection.Input)); } } sql.Append(" ORDER BY C.AccountDate, U.UserCode, C.ClassesSettingID"); DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); if (data == null || data.Rows.Count == 0) { //sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data = new DataSet(); sre.Data.Tables.Add(data); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":ClassesSettingID", OracleDbType.Int32, data.Rows[0]["ClassesSettingID"], ParameterDirection.Input), }; DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetClassesDetailSQL(), paras); if (dData != null) { sre.Data.Tables.Add(dData); } return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得FPC0401画面（班次配置）的查询数据 /// /// 登录用户信息 /// 班次配置ID（多个用【，】隔开） /// 查询结果 public static ServiceResultEntity GetFPC0401SData(SUserInfo user, string ids) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); StringBuilder sql = new StringBuilder(PCModuleLogic.GetClassesSettingSQL()); List parameters = new List(); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":UPUserId", OracleDbType.Int32, user.UserID, ParameterDirection.Input)); //if (string.IsNullOrWhiteSpace(ids)) { ids = "," + ids + ","; sql.Append(" AND INSTR(:ClassesSettingIDs, ',' || C.ClassesSettingID || ',') > 0"); parameters.Add(new OracleParameter(":ClassesSettingIDs", OracleDbType.Varchar2, ids, ParameterDirection.Input)); } sql.Append(" ORDER BY C.AccountDate, U.UserCode, C.ClassesSettingID"); DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); if (data == null || data.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data = new DataSet(); sre.Data.Tables.Add(data); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":ClassesSettingID", OracleDbType.Int32, data.Rows[0]["ClassesSettingID"], ParameterDirection.Input), }; DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetClassesDetailSQL(), paras); if (dData != null) { sre.Data.Tables.Add(data); } return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得FPC0401画面（班次配置明细）的查询数据 /// /// 登录用户信息 /// 班次配置ID /// 班次配置明细 public static ServiceResultEntity GetFPC0401SDData(SUserInfo user, int id) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":ClassesSettingID", OracleDbType.Int32, id, ParameterDirection.Input), }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(PCModuleLogic.GetClassesDetailSQL(), paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得FPC0402画面（班次配置）的查询数据 /// /// 登录用户信息 /// 班次配置ID /// 班次配置明细 public static ServiceResultEntity GetFPC0402SData(SUserInfo user, int id) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":userid", OracleDbType.Int32, id, ParameterDirection.Input), }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(PCModuleLogic.GetClassUserStaffSQL(), paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得FPC0401画面，班次配置查询SQL /// /// 班次配置查询SQL private static string GetClassesSettingSQL() { string sqlString = "SELECT C.ClassesSettingID, " + " C.AccountDate, " + " U.UserID," + " U.UserCode, " + " U.UserName, " + " O.OrganizationCode, " + " O.OrganizationName, " + " C.Remarks, " + " C.ValueFlag, " + " C.CreateTime, " + " CU.UserName AS CreateUserName " + " FROM TP_PC_ClassesSetting C " + " INNER JOIN TP_MST_User U " + " ON U.UserID = C.UserID " + " INNER JOIN TP_MST_Organization O " + " ON O.OrganizationID = C.OrganizationID " + " INNER JOIN TP_MST_User CU " + " ON CU.UserID = C.CreateUserID " + " WHERE C.AccountID = :AccountID " + " AND U.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'" + // 工号操作权限 " AND EXISTS (SELECT UP.PurviewID" + " FROM TP_MST_UserPurview UP" + " WHERE UP.PurviewType = '" + (int)Constant.PurviewType.OperateUser + "'" + " AND (UP.PurviewID = U.UserID OR UP.PurviewID = -1)" + " AND UP.UserId = :UPUserId)"; return sqlString; } /// /// 取得FPC0401画面，班次配置明细查询SQL /// /// 班次配置明细查询SQL private static string GetClassesDetailSQL() { string sqlString = "SELECT CD.ClassesDetailID, " + " CD.userid," + " CD.usercode," + " UJ.JobsCode AS UJobsCode, " + " UJ.JobsName AS UJobsName, " + " UJ.JobsId AS UJobsId, " + " CD.StaffID," + " S.StaffCode, " + " S.StaffName, " + " CD.SOrganizationID," + " SO.OrganizationCode AS SOrganizationCode, " + " SO.OrganizationName AS SOrganizationName, " + " CD.SJobsID," + " CD.SJobsID as jobs," + " SJ.JobsCode AS SJobsCode, " + " SJ.JobsName AS SJobsName, " + " CD.StaffStatus," + " SSS.StaffStatusName, " + " CD.Remarks " + " FROM TP_PC_ClassesDetail CD " + " INNER JOIN TP_MST_Jobs UJ " + " ON UJ.JobsID = CD.UJobsID " + " INNER JOIN TP_MST_Jobs SJ " + " ON SJ.JobsID = CD.SJobsID " + " INNER JOIN TP_HR_Staff S " + " ON S.StaffID = CD.StaffID " + " INNER JOIN TP_MST_Organization SO " + " ON SO.OrganizationID = CD.SOrganizationID " + " INNER JOIN TP_SYS_StaffStatus SSS " + " ON SSS.StaffStatusID = CD.StaffStatus " + " WHERE CD.ClassesSettingID = :ClassesSettingID" + " ORDER BY S.StaffCode"; return sqlString; } /// /// 取得FPC0402画面，工号对应的员工信息SQL /// /// private static string GetClassUserStaffSQL() { string sqlString = " " + "SELECT usf.userstaffid AS infoid, " + " usf.UserID, " + " usr.UserCode, " + " usf.ujobsid AS UJobsID, " + " usf.StaffID, " + " usf.optimestamp, " + " j.jobsname AS UJobsName, " + " j.jobscode AS UJobsCode, " + " s.StaffCode, " + " s.StaffName, " + " s.jobs, " + " sj.jobsname as SJobsName, " + " sj.jobscode as SJobsCode , " + " s.organizationid as SOrganizationid, " + " og.organizationcode as SOrganizationCode, " + " og.organizationname as SOrganizationName, " + " og.organizationfullname as SOrganizationFullName, " + " s.staffstatus, " + " ss.staffstatusname, " + " s.remarks " + " FROM tp_mst_userstaff usf " + " inner join tp_mst_user usr on usf.userid=usr.userid" + " INNER JOIN tp_mst_jobs j ON usf.ujobsid = j.jobsid " + " INNER JOIN tp_hr_staff s ON usf.staffid = s.staffid " + " INNER JOIN tp_mst_jobs sj ON s.jobs = sj.jobsid " + " inner join TP_MST_ORGANIZATION og on s.organizationid=og.organizationid " + " inner join tp_sys_staffstatus ss on ss.staffstatusid=s.staffstatus " + " WHERE usf.userid = :userid " + " order by sj.jobscode"; return sqlString; } /// /// 保存班次配置信息 /// /// /// 班次配置实体 /// 配置情况,0--新增,1--更新 /// /// public static ServiceResultEntity SetFPC0402SData(SUserInfo sUserInfo, FPC0402_SE se, int editStatus, DataTable dtClassesSettingDetail) { IDBTransaction tran = null; try { ServiceResultEntity sre = new ServiceResultEntity(); //if (dsClassesSettingDetailInfo == null || // dsClassesSettingDetailInfo.Tables.Count != 1 || // dsClassesSettingDetailInfo.Tables[0].Rows.Count < 1) //{ // sre.Status = Constant.ServiceResultStatus.NoModifyData; // return sre; //} //DataTable dtClassesSettingDetail = dsClassesSettingDetailInfo.Tables[0]; tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); int executeResult = 0; if (editStatus == 0)//新增 { // 查询新插入的配置数据ID string sqlText = "SELECT SEQ_PC_ClassesSetting_ID.NextVAL FROM dual"; int classesSettingID = int.Parse(tran.GetSqlResultToStr(sqlText)); // 插入班次配置表 string sqlInsMainString = " INSERT INTO TP_PC_ClassesSetting " + " (ClassesSettingID,AccountDate,UserID,UserCode, " + " Remarks,AccountID,CreateTime,CreateUserID,UpdateUserID) " + " VALUES (:ClassesSettingID,:AccountDate,:userID,:userCode, " + " :remarks,:accountID,sysdate,:createUserID,:updateUserID)"; OracleParameter[] paramMain = new OracleParameter[] { new OracleParameter(":ClassesSettingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input), new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":userID",OracleDbType.Int32,se.UserID,ParameterDirection.Input), new OracleParameter(":userCode",OracleDbType.Varchar2,se.UserCode,ParameterDirection.Input), new OracleParameter(":AccountDate",OracleDbType.Date,se.AccountDate,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,se.ClassRemarks,ParameterDirection.Input), new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":createUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":updateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), }; executeResult = tran.ExecuteNonQuery(sqlInsMainString, paramMain); //明细 foreach (DataRow drFor in dtClassesSettingDetail.Rows) { if (drFor["StaffID"] == DBNull.Value) { continue; } string sqlInsStr = "insert into TP_PC_ClassesDetail(ClassesSettingID,AccountDate,userid,usercode,staffid," + "staffstatus,remarks,accountid,valueflag,createtime,createuserid,updatetime,updateuserid," + "ujobsid,sjobsid,uorganizationid,sorganizationid)" + "values(:settingID,:AccountDate,:UserID,:UserCode,:StaffID," + ":StaffStatus,:StaffRemarks,:AccountID,1,sysdate,:CreateUserID,sysdate,:UpdateUserID," + ":UserJobsID,:StaffJobsID,:UserOrganizationID,:StaffOrganizationID)"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":settingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input), new OracleParameter(":AccountDate",OracleDbType.Date,se.AccountDate,ParameterDirection.Input), new OracleParameter(":UserID",OracleDbType.Int32,se.UserID,ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2,se.UserCode,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32,drFor["staffid"].ToString(),ParameterDirection.Input), new OracleParameter(":StaffStatus",OracleDbType.Int32,drFor["staffstatus"].ToString(),ParameterDirection.Input), new OracleParameter(":StaffRemarks",OracleDbType.Varchar2,drFor["remarks"].ToString(),ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UserJobsID",OracleDbType.Int32,drFor["ujobsid"].ToString(),ParameterDirection.Input), new OracleParameter(":StaffJobsID",OracleDbType.Int32,drFor["jobs"].ToString(),ParameterDirection.Input), new OracleParameter(":UserOrganizationID",OracleDbType.Int32,se.UserOrganizationID,ParameterDirection.Input), new OracleParameter(":StaffOrganizationID",OracleDbType.Int32,drFor["sorganizationid"].ToString(),ParameterDirection.Input) }; executeResult = tran.ExecuteNonQuery(sqlInsStr, oracleParameter); } } else { //更新主表 string sqlUpMainString = "update TP_PC_ClassesSetting set Remarks=:remarks where CLASSESSETTINGID=:classSettingID"; OracleParameter[] paramMain = new OracleParameter[] { new OracleParameter(":remarks",OracleDbType.Varchar2,se.ClassRemarks,ParameterDirection.Input), new OracleParameter(":classSettingID",OracleDbType.Int32,se.ClassSettingID,ParameterDirection.Input) }; executeResult = tran.ExecuteNonQuery(sqlUpMainString, paramMain); int classesSettingID = se.ClassSettingID; //明细 foreach (DataRow drFor in dtClassesSettingDetail.Rows) { if (drFor.RowState == DataRowState.Added) { if (drFor["StaffID"] == DBNull.Value) { continue; } string sqlInsStr = "insert into TP_PC_ClassesDetail(ClassesSettingID,AccountDate,userid,usercode,staffid," + "staffstatus,remarks,accountid,valueflag,createtime,createuserid,updatetime,updateuserid," + "ujobsid,sjobsid,uorganizationid,sorganizationid)" + "values(:settingID,:AccountDate,:UserID,:UserCode,:StaffID," + ":StaffStatus,:StaffRemarks,:AccountID,1,sysdate,:CreateUserID,sysdate,:UpdateUserID," + ":UserJobsID,:StaffJobsID,:UserOrganizationID,:StaffOrganizationID)"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":settingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input), new OracleParameter(":AccountDate",OracleDbType.Date,se.AccountDate,ParameterDirection.Input), new OracleParameter(":UserID",OracleDbType.Int32,se.UserID,ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2,se.UserCode,ParameterDirection.Input), new OracleParameter(":StaffID",OracleDbType.Int32,drFor["staffid"].ToString(),ParameterDirection.Input), new OracleParameter(":StaffStatus",OracleDbType.Int32,drFor["staffstatus"].ToString(),ParameterDirection.Input), new OracleParameter(":StaffRemarks",OracleDbType.Varchar2,drFor["remarks"].ToString(),ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UserJobsID",OracleDbType.Int32,drFor["ujobsid"].ToString(),ParameterDirection.Input), new OracleParameter(":StaffJobsID",OracleDbType.Int32,drFor["jobs"].ToString(),ParameterDirection.Input), new OracleParameter(":UserOrganizationID",OracleDbType.Int32,se.UserOrganizationID,ParameterDirection.Input), new OracleParameter(":StaffOrganizationID",OracleDbType.Int32,drFor["sorganizationid"].ToString(),ParameterDirection.Input) }; executeResult = tran.ExecuteNonQuery(sqlInsStr, oracleParameter); } else if (drFor.RowState == DataRowState.Deleted) { string sqlstring = @"Delete from TP_PC_ClassesDetail Where CLASSESDETAILID=:ClassesDetailID"; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":ClassesDetailID",drFor["ClassesDetailID",DataRowVersion.Original].ToString()), }; executeResult = tran.ExecuteNonQuery(sqlstring, parmeters); } else if (drFor.RowState == DataRowState.Modified) { string sqlstring = @"update TP_PC_ClassesDetail set STAFFID=:StaffID, STAFFSTATUS=:StaffStatus, REMARKS=:Remarks, UPDATETIME=sysdate, UPDATEUSERID=:UserID, SJOBSID=:SJobID, SORGANIZATIONID=:SorganizationID where CLASSESDETAILID=:ClassesDetailID "; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":StaffID",drFor["StaffID"].ToString()), new OracleParameter(":StaffStatus",drFor["StaffStatus"].ToString()), new OracleParameter(":Remarks",drFor["Remarks"].ToString()), new OracleParameter(":UserID",sUserInfo.UserID), new OracleParameter(":SJobID",drFor["Jobs"].ToString()), new OracleParameter(":SorganizationID",drFor["SorganizationID"].ToString()), new OracleParameter(":ClassesDetailID",drFor["ClassesDetailID"].ToString()), }; executeResult = tran.ExecuteNonQuery(sqlstring, parmeters); } } } tran.Commit(); return sre; } catch (Exception ex) { if (tran != null && tran.ConnState == ConnectionState.Open) { tran.Rollback(); } throw ex; } finally { if (tran != null && tran.ConnState == ConnectionState.Open) { tran.Disconnect(); } } } #endregion 班次配置管理 #region 人员关联 /// /// 取得FPC0601画面（人员关联）的查询数据 /// /// 登录用户信息 /// 查询条件 /// 查询结果 public static ServiceResultEntity GetFPC0601SData(SUserInfo user, FPC0601_SE se) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); StringBuilder sql = new StringBuilder(PCModuleLogic.GetFPC0601UserSQL()); List parameters = new List(); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":UPUserId", OracleDbType.Int32, user.UserID, ParameterDirection.Input)); if (se != null) { // 员工工号 if (!string.IsNullOrEmpty(se.UserCode)) { sql.Append(" AND INSTR(U.UserCode, :UserCode) > 0"); parameters.Add(new OracleParameter(":UserCode", OracleDbType.NVarchar2, se.UserCode, ParameterDirection.Input)); } // 员工工号备注 if (!string.IsNullOrEmpty(se.Remarks)) { sql.Append(" AND INSTR(U.Remarks, :Remarks) > 0"); parameters.Add(new OracleParameter(":Remarks", OracleDbType.NVarchar2, se.Remarks, ParameterDirection.Input)); } } sql.Append(" ORDER BY U.UserCode"); DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); if (data == null || data.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data = new DataSet(); sre.Data.Tables.Add(data); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":userid", OracleDbType.Int32, data.Rows[0]["userid"], ParameterDirection.Input), }; DataTable dData = conn.GetSqlResultToDt(PCModuleLogic.GetFPC0601UserStaffSQL(), paras); if (dData != null) { sre.Data.Tables.Add(dData); } return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得FPC0601画面（人员关联）的查询数据 /// /// 登录用户信息 /// 工号人员关联ID /// 工号人员关联明细 public static ServiceResultEntity GetFPC0601SNData(SUserInfo user, int userid) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":userid", OracleDbType.Int32, userid, ParameterDirection.Input), }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(PCModuleLogic.GetFPC0601UserStaffSQL(), paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得FPC0601画面，工号查询SQL /// /// 工号查询SQL private static string GetFPC0601UserSQL() { string sqlString = "SELECT 0 AS sel, " + " U.userid, " + " U.usercode, " + " U.limitmac, " + " U.username, " + " U.valueflag, " + " U.valueflag AS checkvalueflag, " + " U.organizationid, " + " tp_mst_organization.organizationname, " + " tp_mst_organization.organizationfullname, " + " tp_mst_userlogin.islocked, " + " U.remarks " + " FROM tp_mst_user u LEFT JOIN tp_mst_organization ON u.organizationid = tp_mst_organization.organizationid " + " INNER JOIN tp_mst_userlogin ON u.userid = tp_mst_userlogin.userid " + " WHERE u.accountid = :AccountID " + " AND U.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'" + // 工号操作权限 " AND EXISTS (SELECT UP.PurviewID" + " FROM TP_MST_UserPurview UP" + " WHERE UP.PurviewType = '" + (int)Constant.PurviewType.OperateUser + "'" + " AND (UP.PurviewID = U.UserID OR UP.PurviewID = -1)" + " AND UP.UserId = :UPUserId)"; return sqlString; } /// /// 取得FPC0601画面，关联人员查询SQL /// /// 关联人员查询SQL private static string GetFPC0601UserStaffSQL() { string sqlString = " " + "SELECT usf.userstaffid AS infoid, " + " usf.UserID, " + " usf.ujobsid AS UserJobsID, " + " usf.StaffID, " + " usf.optimestamp, " + " j.jobsname AS UserJobsName, " + " j.jobscode AS UserJobsCode, " + " s.StaffCode, " + " s.StaffName, " + " sj.jobsname, " + " sj.jobscode , " + " s.organizationid, " + " og.organizationcode, " + " og.organizationname, " + " og.organizationfullname, " + " s.staffstatus, " + " ss.staffstatusname, " + " s.remarks " + " FROM tp_mst_userstaff usf INNER JOIN tp_mst_jobs j ON usf.ujobsid = j.jobsid " + " INNER JOIN tp_hr_staff s ON usf.staffid = s.staffid " + " INNER JOIN tp_mst_jobs sj ON s.jobs = sj.jobsid " + " inner join TP_MST_ORGANIZATION og on s.organizationid=og.organizationid " + " inner join tp_sys_staffstatus ss on ss.staffstatusid=s.staffstatus " + " WHERE userid = :userid " + " order by sj.jobscode"; return sqlString; } /// /// 保存FPC0602画面（班次配置管理）的数据 /// /// /// /// /// public static ServiceResultEntity SetFPC0602Data(SUserInfo userInfo, int UserId, DataSet StaffInfo) { IDBTransaction tran = null; try { ServiceResultEntity sre = new ServiceResultEntity(); if (StaffInfo == null || StaffInfo.Tables.Count != 1 || StaffInfo.Tables[0].Rows.Count < 1) { sre.Status = Constant.ServiceResultStatus.NoModifyData; return sre; } DataTable StaffTable = StaffInfo.Tables[0]; tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); int executeResult = 0; foreach (DataRow drFor in StaffTable.Rows) { if (drFor.RowState == DataRowState.Added) { if (drFor["StaffID"] == DBNull.Value) { continue; } string sqlstring = @"Insert into TP_MST_UserStaff(UserID,UJobsID,StaffID,AccountID,CreateUserID,UpdateUserID) Values(:UserID,:UJobsID,:StaffID,:AccountID,:CreateUserID,:UpdateUserID)"; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":UserID",UserId), new OracleParameter(":UJobsID",drFor["UserJobsID"].ToString()), new OracleParameter(":StaffID",drFor["StaffID"].ToString()), new OracleParameter(":AccountID",userInfo.AccountID), new OracleParameter(":CreateUserID",userInfo.UserID), new OracleParameter(":UpdateUserID",userInfo.UserID), }; executeResult = tran.ExecuteNonQuery(sqlstring, parmeters); } else if (drFor.RowState == DataRowState.Deleted) { string sqlstring = @"Delete from TP_MST_UserStaff Where UserStaffID=:UserStaffID"; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":UserStaffID",drFor["InfoID",DataRowVersion.Original].ToString()), }; executeResult = tran.ExecuteNonQuery(sqlstring, parmeters); } else if (drFor.RowState == DataRowState.Modified) { string sqlstring = @"Update TP_MST_UserStaff Set UJobsID=:UJobsID, StaffID=:StaffID, UpdateUserID=:UpdateUserID Where UserStaffID=:UserStaffID"; OracleParameter[] parmeters = new OracleParameter[] { new OracleParameter(":UJobsID",drFor["UserJobsId"].ToString()), new OracleParameter(":StaffID",drFor["StaffID"].ToString()), new OracleParameter(":UpdateUserID",userInfo.UserID), new OracleParameter(":UserStaffID",drFor["InfoID"].ToString()) }; executeResult = tran.ExecuteNonQuery(sqlstring, parmeters); } } tran.Commit(); return sre; } catch (Exception ex) { if (tran != null && tran.ConnState == ConnectionState.Open) { tran.Rollback(); } throw ex; } finally { if (tran != null && tran.ConnState == ConnectionState.Open) { tran.Disconnect(); } } } /// /// 根据工号ID获取该工号的工种关联 /// /// 用户ID /// DataSet public static ServiceResultEntity GetJobByUserId(int UserId) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); StringBuilder sbSql = new StringBuilder(); sbSql.Append(@"Select JobsID as UserJobsID,JobsCode as UserJobsCode,JobsName as UserJobsName,JobsCode||'-'|| JobsName as UserJobsCodeName from TP_MST_Jobs where JobsID in (Select JobsID From TP_MST_UserJobs where UserID=:UserID) "); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":UserID", OracleDbType.Int32, UserId, ParameterDirection.Input), }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(sbSql.ToString(), paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 根据工号ID获取该工号的基础信息 /// /// 用户ID /// DataSet public static ServiceResultEntity GetUserInfo(SUserInfo user, int UserId) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); StringBuilder sbSql = new StringBuilder(GetUserInfoSQL()); // List parameters = new List(); OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input), new OracleParameter(":UPUserId", OracleDbType.Int32, user.UserID, ParameterDirection.Input), new OracleParameter(":UserID", OracleDbType.Int32,UserId, ParameterDirection.Input) }; ServiceResultEntity sre = new ServiceResultEntity(); sre.Data = conn.GetSqlResultToDs(sbSql.ToString(), paras); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 取得FPC0601画面，工号查询SQL /// /// 工号查询SQL private static string GetUserInfoSQL() { string sqlString = "SELECT 0 AS sel, " + " U.userid, " + " U.usercode, " + " U.limitmac, " + " U.username, " + " U.valueflag, " + " U.valueflag AS checkvalueflag, " + " U.organizationid, " + " tp_mst_organization.organizationname, " + " tp_mst_organization.organizationfullname, " + " tp_mst_userlogin.islocked, " + " U.remarks " + " FROM tp_mst_user u LEFT JOIN tp_mst_organization ON u.organizationid = tp_mst_organization.organizationid " + " INNER JOIN tp_mst_userlogin ON u.userid = tp_mst_userlogin.userid " + " WHERE u.accountid = :AccountID " + " AND U.ValueFlag = '" + (int)Constant.ValueFlag.Effective + "'" + " and u.userid=:UserID " + // 工号操作权限 " AND EXISTS (SELECT UP.PurviewID" + " FROM TP_MST_UserPurview UP" + " WHERE UP.PurviewType = '" + (int)Constant.PurviewType.OperateUser + "'" + " AND (UP.PurviewID = U.UserID OR UP.PurviewID = -1)" + " AND UP.UserId = :UPUserId)"; return sqlString; } #endregion 人员关联 #region 模具档案 /// /// 获取模具档案一览画面期初数据 /// /// /// public static ServiceResultEntity GetFPC1001LoadData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // 模具类型 string sqlString = "SELECT dd.dictionaryid, dd.dictionaryvalue, dd.displayno\n" + " FROM TP_MST_DataDictionary dd\n" + " WHERE dd.DictionaryType = 'GMM002'\n" + " AND dd.valueflag = '1'\n" + " AND dd.accountid = :accountid\n" + " ORDER BY dd.displayno"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); sre.Data.Tables[0].TableName = "MouldType"; // 材料供应商 sqlString = "SELECT mms.supplierid, mms.suppliername, mms.displayno\n" + " FROM TP_MST_MouldMaterialSuppliers mms\n" + " WHERE mms.valueflag = '1'\n" + " AND mms.accountid = :accountid\n" + " ORDER BY mms.displayno"; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters)); sre.Data.Tables[1].TableName = "Suppliers"; sqlString = "SELECT ms.mouldstatusid, ms.mouldstatusname, ms.displayno\n" + " FROM TP_SYS_MouldStatus ms\n" + " WHERE ms.mouldstatusid > 0\n" + " ORDER BY ms.displayno"; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString)); sre.Data.Tables[2].TableName = "MouldStatus"; return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取模具档案一览查询数据 /// /// /// public static ServiceResultEntity GetFPC1001QueryData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT 0 Sel, m.MouldID\n" + " ,m.MouldCode\n" + " ,m.MouldBarcode\n" + " ,mtype.dictionaryvalue MouldTypeName\n" + " ,mms.SupplierName\n" + " ,m.ProductionDate\n" + " ,m.GoodsID\n" + " ,m.GoodsCode\n" + " ,m.UserID\n" + " ,m.UserCode\n" + " ,m.Weight\n" + " ,m.Cost\n" + " ,m.StandardGroutingNum\n" + " ,m.StandardGroutingSum\n" + " ,m.GroutingNum GroutingNums\n" + " ,case when m.OutputCount=1 then to_char(m.GroutingNum)\n" + " else (select listagg(to_char(t.GroutingNum), ';') within GROUP(ORDER BY t.outputno) from TP_PC_MouldOutput t where t.MouldID=m.MouldID) end GroutingNum\n" + " ,m.GroutingDateBegin\n" + " ,m.GroutingDateEnd\n" + " ,m.LastGroutingBatchNo\n" + " ,m.MouldStatus\n" + " ,ms.mouldstatusname\n" + " ,m.RepairFlag\n" + " ,m.ScrapDate\n" + " ,m.ScrapUserID\n" + " ,m.ScrapUserCode\n" + " ,ScReason.dictionaryvalue ScrapReason\n" + " ,m.ScrapResponsibility\n" + " ,gl.groutinglinecode\n" + //" ,gld.groutingmouldcode\n" + " ,case when m.GroutingLineDetailID is null or m.OutputCount=1 then to_char(gld.groutingmouldcode) else \n" + " (select listagg(to_char(gldd.groutingmouldcode), ',') within GROUP(ORDER BY gldd.groutinglinedetailid) from tp_pc_groutinglinedetail gldd where gldd.valueflag='1' and gldd.MouldID=m.MouldID) end groutingmouldcode\n" + " ,m.Remarks\n" + " ,m.CreateTime\n" + " ,cu.usercode CreateUserCode\n" + " ,m.OutputCount\n" + " ,g.mouldmaterialcode\n" + " ,m.STANDARDGROUTINGSUM - m.GroutingNum notusedGroutingNum\n" + //2021年12月21日11:41:10 by fy modify 列表中增加一列【卸模线号】，显示他最后一次上线的线号 //begin ",gl2.groutinglinecode UnloadingMouldLine\n" + //end ",mp.plan_code\n" + // add 夏常明 模具生产计划单号 2023-6-25 " FROM tp_pc_mould m\n" + " LEFT JOIN tp_mst_datadictionary mtype\n" + " ON mtype.dictionaryid = m.mouldtype\n" + " LEFT JOIN tp_mst_goods g\n" + " ON g.goodsid = m.goodsid\n" + " LEFT JOIN tp_mst_mouldmaterialsuppliers mms\n" + " ON mms.SupplierID = m.MaterialSupplier\n" + " INNER JOIN tp_sys_mouldstatus ms\n" + " ON ms.mouldstatusid = m.MouldStatus\n" + " LEFT JOIN tp_mst_datadictionary ScReason\n" + " ON ScReason.dictionaryid = m.ScrapReason\n" + " LEFT JOIN tp_pc_groutinglinedetail gld\n" + " ON gld.groutinglinedetailid = m.GroutingLineDetailID\n" + " LEFT JOIN tp_pc_groutingline gl\n" + " ON gl.groutinglineid = gld.groutinglineid\n" + " INNER JOIN tp_mst_user cu\n" + " ON cu.userid = m.createuserid\n" + //2021年12月21日11:41:10 by fy modify 列表中增加一列【卸模线号】，显示他最后一次上线的线号 //begin "LEFT JOIN(\n" + "SELECT\n" + " t.groutinglinedetailid,\n" + " t.mouldcode,\n" + " t.rk\n" + "FROM\n" + " (\n" + " SELECT\n" + " t.groutinglinedetailid,\n" + " t.mouldcode,\n" + " rank ( ) over (PARTITION BY t.mouldcode ORDER BY t.createtime DESC) rk\n" + " FROM\n" + " tp_pc_mouldchangehistory t\n" + " WHERE\n" + " t.valueflag = 1\n" + " AND t.mouldstatus IN( 2, 3 ) \n" + " AND t.mouldstatusafter IN( 1, 4 ) \n" + " ) t\n" + "WHERE\n" + " t.rk <= 1\n" + ") mch ON mch.mouldcode = m.mouldcode\n" + "LEFT JOIN tp_pc_groutinglinedetail gld2 ON gld2.GROUTINGLINEDETAILID = mch.GROUTINGLINEDETAILID\n" + "LEFT JOIN tp_pc_groutingline gl2 ON gl2.groutinglineid = gld2.groutinglineid\n" + //end "left join t_mould_plan mp on m.MOULD_PLAN_ID = mp.plan_id \n" +//模具生产计划 add 夏常明 2023-6-25 " WHERE m.accountid = :accountid\n" + " AND m.valueflag = '1'\n" + " AND (:MouldBarcode IS NULL OR instr(m.MouldBarcode, :MouldBarcode) > 0)\n" + " AND (:PDateBegin IS NULL OR m.ProductionDate >= :PDateBegin)\n" + " AND (:PDateEnd IS NULL OR m.ProductionDate <= :PDateEnd)\n" + " AND (:UserCode IS NULL OR instr(m.UserCode, :UserCode) > 0)\n" + " AND (:GoodsCode IS NULL OR instr(m.GoodsCode, :GoodsCode) > 0)\n" + " AND (:Remarks IS NULL OR instr(m.Remarks, :Remarks) > 0)\n" + " AND (:MouldTypes IS NULL OR\n" + " instr(',' || :MouldTypes || ',', ',' || m.MouldType || ',') > 0)\n" + " AND (:Suppliers IS NULL OR\n" + " instr(',' || :Suppliers || ',', ',' || m.MaterialSupplier || ',') > 0)\n" + " AND (:MouldStatus IS NULL OR\n" + " instr(',' || :MouldStatus || ',', ',' || m.MouldStatus || ',') > 0)\n" + " AND (:SDateBegin IS NULL OR m.ScrapDate >= :SDateBegin)\n" + " AND (:SDateEnd IS NULL OR m.ScrapDate <= :SDateEnd)\n" + " AND (:MouldIDs IS NULL OR\n" + " instr(',' || :MouldIDs || ',', ',' || m.MouldID || ',') > 0)\n" + " ORDER BY m.MouldBarcode"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":MouldIDs", OracleDbType.Varchar2, cre.Properties["MouldIDs"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":PDateBegin", OracleDbType.Date, cre.Properties["PDateBegin"], ParameterDirection.Input), new OracleParameter(":PDateEnd", OracleDbType.Date, cre.Properties["PDateEnd"], ParameterDirection.Input), new OracleParameter(":MouldBarcode", OracleDbType.NVarchar2, cre.Properties["MouldBarcode"], ParameterDirection.Input), new OracleParameter(":UserCode", OracleDbType.NVarchar2, cre.Properties["UserCode"], ParameterDirection.Input), new OracleParameter(":GoodsCode", OracleDbType.NVarchar2, cre.Properties["GoodsCode"], ParameterDirection.Input), new OracleParameter(":Remarks", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), new OracleParameter(":MouldTypes", OracleDbType.NVarchar2, cre.Properties["MouldTypes"], ParameterDirection.Input), new OracleParameter(":Suppliers", OracleDbType.NVarchar2, cre.Properties["Suppliers"], ParameterDirection.Input), new OracleParameter(":MouldStatus", OracleDbType.NVarchar2, cre.Properties["MouldStatus"], ParameterDirection.Input), new OracleParameter(":SDateBegin", OracleDbType.Date, cre.Properties["ScrapDateBegin"], ParameterDirection.Input), new OracleParameter(":SDateEnd", OracleDbType.Date, cre.Properties["ScrapDateEnd"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); if (sre.Data == null || sre.Data.Tables.Count == 0 || sre.Data.Tables[0].Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; } return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 查询模具是否报工 /// /// /// /// public static ServiceResultEntity GetMouldPlan(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT COUNT(1)\n" + " FROM tp_pc_mould pm\n" + " WHERE pm.mould_plan_id IS NOT NULL\n" + " AND pm.accountid = :accountid\n" + " AND instr(',' || :MouldIDs || ',', ',' || pm.mouldid || ',') > 0"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":MouldIDs", OracleDbType.Varchar2, cre.Properties["MouldIDs"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 产品型号是否相同 /// /// /// /// public static ServiceResultEntity GoodsIdSame(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT DISTINCT pm.goodsid\n" + " FROM tp_pc_mould pm\n" + " WHERE pm.accountid = :accountid\n" + " AND instr(',' || :MouldIDs || ',', ',' || pm.mouldid || ',') > 0"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":MouldIDs", OracleDbType.Varchar2, cre.Properties["MouldIDs"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据产品查询模具生产计划数据源 /// /// /// /// public static ServiceResultEntity GetFPC1004MouldPlanData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int goodsid = Convert.ToInt32(cre.Properties["goodsid"]); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = "SELECT mp.plan_id\n" + " ,mp.plan_code ||'('||TO_CHAR(mp.plan_qty-mp.make_qty,'fm9999')||')' as plan_code\n" + " ,mp.plan_status\n" + " FROM t_mould_plan mp\n" + " WHERE mp.goodsid = :goodsid\n" + " AND mp.plan_status <>3"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":goodsid", OracleDbType.Int32, goodsid, ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取模具新建、编辑画面期初数据 /// /// /// public static ServiceResultEntity GetFPC1002LoadData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int mouldID = cre.Properties.GetValue("MouldID"); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // 模具类型 string sqlString = "SELECT dd.dictionaryid, dd.dictionaryvalue, dd.displayno\n" + " FROM TP_MST_DataDictionary dd\n" + " WHERE dd.DictionaryType = 'GMM002'\n" + " AND dd.valueflag = '1'\n" + " AND dd.accountid = :accountid\n" + " ORDER BY dd.displayno"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); sre.Data.Tables[0].TableName = "MouldType"; // 材料供应商 sqlString = "SELECT mms.supplierid, mms.suppliername, mms.displayno\n" + " FROM TP_MST_MouldMaterialSuppliers mms\n" + " WHERE mms.valueflag = '1'\n" + " AND mms.accountid = :accountid\n" + " ORDER BY mms.displayno"; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters)); sre.Data.Tables[1].TableName = "Suppliers"; if (mouldID > 0) { sqlString = "SELECT m.mouldid\n" + " ,m.mouldbarcode\n" + " ,m.mouldtype\n" + " ,dd.dictionaryvalue mouldtypeName\n" + " ,m.materialsupplier\n" + " ,mms.suppliername\n" + " ,m.productiondate\n" + " ,m.goodsid\n" + " ,m.goodscode\n" + " ,m.userid\n" + " ,m.usercode\n" + " ,m.weight\n" + " ,m.cost\n" + " ,m.standardgroutingnum\n" + " ,m.remarks\n" + " ,m.OutputCount\n" + " ,m.MOULD_PLAN_ID\n" + " FROM tp_pc_mould m\n" + " LEFT JOIN tp_mst_datadictionary dd\n" + " ON dd.dictionaryid = m.mouldtype\n" + " LEFT JOIN tp_mst_mouldmaterialsuppliers mms\n" + " ON mms.supplierid = m.materialsupplier\n" + " WHERE m.mouldid = :mouldid"; parameters = new OracleParameter[] { new OracleParameter(":mouldid", OracleDbType.Int32, mouldID, ParameterDirection.Input), }; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters)); sre.Data.Tables[2].TableName = "InfoData"; //模具生产计划不为空 返回模具生产计划数据源 if (!string.IsNullOrEmpty(sre.Data.Tables[2].Rows[0]["MOULD_PLAN_ID"] + "")) { sqlString = "SELECT mp.plan_id\n" + " ,mp.plan_code\n" + " ,mp.plan_status\n" + " FROM t_mould_plan mp\n" + " WHERE mp.goodsid = :goodsid and mp.valueflag = 1 and mp.plan_status != 3"; parameters = new OracleParameter[] { new OracleParameter(":goodsid", OracleDbType.Int32, Convert.ToInt32(sre.Data.Tables[2].Rows[0]["goodsid"]), ParameterDirection.Input), }; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters)); sre.Data.Tables[3].TableName = "Mouldplan"; } } return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取模具产品期初数据 /// /// /// public static ServiceResultEntity GetFPC1002GoodsData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); string mouldID = cre.Properties.GetValue("GoodsCode"); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT g.goodsid, g.goodscode,g.GOODSSPECIFICATION, g.mouldweight, g.mouldcost\n" + " , g.StandardGroutingNum, g.MouldOutputCount" + " FROM tp_mst_goods g\n" + " WHERE g.goodscode = :goodscode"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":goodscode", OracleDbType.NVarchar2, cre.Properties["GoodsCode"], ParameterDirection.Input), }; DataSet DS = con.GetSqlResultToDs(sqlString, parameters); //获取未完成模具生产计划 sqlString = "SELECT mp.plan_id\n" + " ,mp.plan_code ||'('||TO_CHAR(mp.plan_qty-mp.make_qty,'fm9999')||')' as plan_code\n" + " FROM t_mould_plan mp\n" + " INNER JOIN tp_mst_goods mg\n" + " ON mp.goodsid = mg.goodsid\n" + " WHERE mg.accountid = :accountid\n" + " AND mg.goodscode = :goodscode\n" + " AND mp.plan_status <>3"; parameters = new OracleParameter[] { new OracleParameter(":goodscode", OracleDbType.NVarchar2, cre.Properties["GoodsCode"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), }; DataTable dt = con.GetSqlResultToDt(sqlString, parameters); DS.Tables.Add(dt.Copy()); sre.Data = DS; return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取模具新建、编辑画面期初数据 /// /// /// public static ServiceResultEntity SetFPC1002Data(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int mouldID = Convert.ToInt32(cre.Properties["MouldID"]); int retrunRow = 0; IDBTransaction con = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Connect(); if (mouldID == 0) { // 新建 //// 验证 条码是否重复 //string sql = "Select count(*) from TP_PC_Mould m where m.MouldBarcode = :MouldBarcode"; //OracleParameter[] paras = new OracleParameter[] //{ // new OracleParameter(":MouldBarcode", OracleDbType.Varchar2, // cre.Properties["MouldBarcode"], ParameterDirection.Input), //}; //string count = con.GetSqlResultToStr(sql, paras); //if ("0" != count) //{ // sre.Status = Constant.ServiceResultStatus.Other; // sre.Message = "模具条码【" + cre.Properties["MouldBarcode"] + "】已存在"; // return sre; //} string plan_code = string.Empty; //模具计划编码 //模具计划 未完成数量 模具数大小校验 if (cre.Properties["MOULD_PLAN_ID"] != null) { string sqlString = "SELECT mp.plan_qty - mp.make_qty AS incomplete,mp.PLAN_CODE\n" + " FROM t_mould_plan mp\n" + " WHERE mp.plan_id = :mould_plan_id"; OracleParameter[] paras1 = new OracleParameter[] { new OracleParameter(":mould_plan_id", OracleDbType.Varchar2, cre.Properties["MOULD_PLAN_ID"], ParameterDirection.Input), }; DataTable dt = con.GetSqlResultToDt(sqlString, paras1); plan_code = dt.Rows[0]["PLAN_CODE"].ToString(); if (Convert.ToInt32(dt.Rows[0][0]) < Convert.ToInt32(cre.Properties["MCount"])) { sre.Message = " 模具个数不能大于模具生产计划未完成数量！"; sre.Status = Constant.ServiceResultStatus.Other; return sre; } } int mCount = 1; if (cre.Properties["MCount"] != null) { mCount = Convert.ToInt32(cre.Properties["MCount"]); } mCount = (mCount < 1 ? 1 : mCount); string sql = null; OracleParameter[] paras = null; for (int i = 0; i < mCount; i++) { mouldID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mould_id.nextval from dual ")); string mouldcode = con.GetSqlResultToStr("select fun_cmn_convertto36(" + mouldID + ", 6) from dual "); int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual ")); int len = mouldcode.Length; string mouldBarcode = DateTime.Now.ToString("yyMMdd") + (len > 6 ? mouldcode.Substring(len - 6) : mouldcode); if (i > 0) { sre.Result += "," + mouldBarcode; } else { sre.Result = mouldBarcode; } //cre.Properties["MouldBarcode"] = mouldBarcode; sql = "INSERT INTO tp_pc_mouldchangehistory\n" + " (CHANGEID\n" + " ,MOULDID\n" + " ,MOULDCODE\n" + " ,MOULDBARCODE\n" + " ,GOODSID\n" + " ,GOODSCODE\n" + " ,OPERATIONTYPE\n" + " ,MOULDSTATUS\n" + " ,MOULDSTATUSAFTER\n" + " ,GROUTINGNUM\n" + " ,REMARKS\n" + " ,CHANGEFLAG\n" + " ,ACCOUNTID\n" + " ,CREATEUSERID)\n" + " Values( :changeID\n" + " ,:MOULDID\n" + " ,:MOULDCODE\n" + " ,:MOULDBARCODE\n" + " ,:GOODSID\n" + " ,:GOODSCODE\n" + " ,:OPERATIONTYPE\n" + " ,0\n" + " ,1\n" + " ,0\n" + " ,:REMARKS\n" + " ,'1'\n" + " ,:ACCOUNTID\n" + " ,:CREATEUSERID\n" + " )"; paras = new OracleParameter[] { new OracleParameter(":changeID", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":MOULDID", OracleDbType.Int32, mouldID, ParameterDirection.Input), new OracleParameter(":MOULDCODE", OracleDbType.Varchar2, mouldcode, ParameterDirection.Input), new OracleParameter(":MOULDBARCODE", OracleDbType.Varchar2, mouldBarcode, ParameterDirection.Input), new OracleParameter(":GOODSID", OracleDbType.Int32, cre.Properties["GoodsID"], ParameterDirection.Input), new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2, cre.Properties["GoodsCode"], ParameterDirection.Input), new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32, (int)Constant.MouldOperationType.ToInv, ParameterDirection.Input), new OracleParameter(":REMARKS", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":CREATEUSERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); sql = "INSERT INTO tp_pc_mould\n" + " (MOULDID\n" + " ,MOULDCODE\n" + " ,MOULDBARCODE\n" + " ,MOULDTYPE\n" + " ,MATERIALSUPPLIER\n" + " ,PRODUCTIONDATE\n" + " ,GOODSID\n" + " ,GOODSCODE\n" + " ,USERID\n" + " ,USERCODE\n" + " ,WEIGHT\n" + " ,COST\n" + " ,STANDARDGROUTINGNUM\n" + " ,OUTPUTCOUNT\n" + " ,MOULDSTATUS\n" + " ,PREMOULDSTATUS\n" + " ,LASTCHANGEHISTORYID\n" + " ,REMARKS\n" + " ,MOULD_PLAN_ID\n" + //add 夏常明 模具生产计划id 2023-6-26 " ,ACCOUNTID\n" + " ,CREATEUSERID\n" + " ,UPDATEUSERID)\n" + "VALUES\n" + " (:MOULDID\n" + " ,:MOULDCODE\n" + " ,:MOULDBARCODE\n" + " ,:MOULDTYPE\n" + " ,:MATERIALSUPPLIER\n" + " ,:PRODUCTIONDATE\n" + " ,:GOODSID\n" + " ,:GOODSCODE\n" + " ,:USERID\n" + " ,:USERCODE\n" + " ,:WEIGHT\n" + " ,:COST\n" + " ,:STANDARDGROUTINGNUM\n" + " ,:OUTPUTCOUNT\n" + " ,:MOULDSTATUS\n" + " ,:PREMOULDSTATUS\n" + " ,:LASTCHANGEHISTORYID\n" + " ,:REMARKS\n" + " ,:MOULD_PLAN_ID\n" + //add 夏常明 模具生产计划id 2023-6-26 " ,:ACCOUNTID\n" + " ,:CREATEUSERID\n" + " ,:CREATEUSERID)"; paras = new OracleParameter[] { new OracleParameter(":MOULDCODE", OracleDbType.Varchar2, mouldcode, ParameterDirection.Input), new OracleParameter(":MOULDSTATUS", OracleDbType.Int32, (int)Constant.MouldStatus.Inv, ParameterDirection.Input), new OracleParameter(":PREMOULDSTATUS", OracleDbType.Int32, (int)Constant.MouldStatus.Add, ParameterDirection.Input), new OracleParameter(":LASTCHANGEHISTORYID", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":MOULDID", OracleDbType.Int32, mouldID, ParameterDirection.Input), new OracleParameter(":MOULDBARCODE", OracleDbType.Varchar2, mouldBarcode, ParameterDirection.Input), new OracleParameter(":MOULDTYPE", OracleDbType.Int32, cre.Properties["MouldType"], ParameterDirection.Input), new OracleParameter(":MATERIALSUPPLIER", OracleDbType.Int32, cre.Properties["MaterialSupplier"], ParameterDirection.Input), new OracleParameter(":PRODUCTIONDATE", OracleDbType.Date, cre.Properties["ProductionDate"], ParameterDirection.Input), new OracleParameter(":GOODSID", OracleDbType.Int32, cre.Properties["GoodsID"], ParameterDirection.Input), new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2, cre.Properties["GoodsCode"], ParameterDirection.Input), new OracleParameter(":USERID", OracleDbType.Int32, cre.Properties["UserID"], ParameterDirection.Input), new OracleParameter(":USERCODE", OracleDbType.NVarchar2, cre.Properties["UserCode"], ParameterDirection.Input), new OracleParameter(":WEIGHT", OracleDbType.Decimal, cre.Properties["Weight"], ParameterDirection.Input), new OracleParameter(":COST", OracleDbType.Decimal, cre.Properties["Cost"], ParameterDirection.Input), new OracleParameter(":STANDARDGROUTINGNUM", OracleDbType.Decimal, cre.Properties["StandardGroutingNum"], ParameterDirection.Input), new OracleParameter(":OUTPUTCOUNT", OracleDbType.Decimal, cre.Properties["OutputCount"], ParameterDirection.Input), new OracleParameter(":REMARKS", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), new OracleParameter(":MOULD_PLAN_ID", OracleDbType.Int32, cre.Properties["MOULD_PLAN_ID"], ParameterDirection.Input),// 模具生产计划id new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":CREATEUSERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); //更新模具生产计划完成数量 添加报工履历 add 夏常明 2023-6-26 if (!string.IsNullOrEmpty(cre.Properties["MOULD_PLAN_ID"] + "")) { //更新模具生产计划完成数量 string sqlString = "UPDATE t_mould_plan\n" + " SET make_qty = make_qty + 1\n" + " WHERE plan_id = :MOULD_PLAN_ID"; paras = new OracleParameter[] { new OracleParameter(":MOULD_PLAN_ID", OracleDbType.Int32, cre.Properties["MOULD_PLAN_ID"], ParameterDirection.Input),// 模具生产计划id }; retrunRow += con.ExecuteNonQuery(sqlString, paras); //添加报工履历 sqlString = "INSERT INTO t_mould_plan_report_his\n" + " (his_type\n" + " ,plan_id\n" + " ,plan_code\n" + " ,mouldid\n" + " ,goodsid\n" + " ,materialsupplier\n" + " ,CREATEUSER\n" + " ,accountid)\n" + "VALUES\n" + " (1\n" + " ,:plan_id\n" + " ,:plan_code\n" + " ,:mouldid\n" + " ,:goodsid\n" + " ,:materialsupplier\n" + " ,:CREATEUSER\n" + " ,:accountid)"; paras = new OracleParameter[] { new OracleParameter(":plan_id", OracleDbType.Int32, cre.Properties["MOULD_PLAN_ID"], ParameterDirection.Input),// 模具生产计划id new OracleParameter(":plan_code", OracleDbType.Varchar2, plan_code ,ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, mouldID ,ParameterDirection.Input), new OracleParameter(":goodsid", OracleDbType.Int32, cre.Properties["GoodsID"] ,ParameterDirection.Input), new OracleParameter(":materialsupplier", OracleDbType.Int32, cre.Properties["MaterialSupplier"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":CREATEUSER", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sqlString, paras); } // 多胞胎 int outputcount = Convert.ToInt32(cre.Properties["OutputCount"]); if (outputcount > 1) { string sqlString = "insert into TP_PC_MouldOutput\n" + " (MouldID, MouldCode, OutputNo, OutputCount, GroutingNum, CreateUserID)\n" + "values\n" + " (:MouldID, :MouldCode, :OutputNo, :OutputCount, 0, :CreateUserID)"; for (int j = 1; j <= outputcount; j++) { paras = new OracleParameter[] { new OracleParameter(":MouldID", OracleDbType.Int32, mouldID, ParameterDirection.Input), new OracleParameter(":MouldCode", OracleDbType.Varchar2, mouldcode, ParameterDirection.Input), new OracleParameter(":OutputNo", OracleDbType.Int32, j, ParameterDirection.Input), new OracleParameter(":OutputCount", OracleDbType.Int32, outputcount , ParameterDirection.Input), new OracleParameter(":CreateUserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sqlString, paras); } } } } else { sre.Result = cre.Properties["MouldBarcode"]; // 编辑 string sqlString = "UPDATE tp_pc_mould m\n" + " SET m.mouldtype = :mouldtype\n" + " ,m.materialsupplier = :materialsupplier\n" + " ,m.productiondate = :productiondate\n" + " ,m.userid = :userid\n" + " ,m.usercode = :usercode\n" + " ,m.weight = :weight\n" + " ,m.cost = :cost\n" + " ,m.standardgroutingnum = :standardgroutingnum\n" + " ,m.remarks = :remarks\n" + " ,m.UpdateUserID = :UpdateUserID\n" + " WHERE m.mouldid = :mouldid"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":mouldtype", OracleDbType.Int32, cre.Properties["MouldType"], ParameterDirection.Input), new OracleParameter(":materialsupplier", OracleDbType.Int32, cre.Properties["MaterialSupplier"], ParameterDirection.Input), new OracleParameter(":productiondate", OracleDbType.Date, cre.Properties["ProductionDate"], ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, cre.Properties["UserID"], ParameterDirection.Input), new OracleParameter(":usercode", OracleDbType.NVarchar2, cre.Properties["UserCode"], ParameterDirection.Input), new OracleParameter(":weight", OracleDbType.Decimal, cre.Properties["Weight"], ParameterDirection.Input), new OracleParameter(":cost", OracleDbType.Decimal, cre.Properties["Cost"], ParameterDirection.Input), new OracleParameter(":standardgroutingnum", OracleDbType.Decimal, cre.Properties["StandardGroutingNum"], ParameterDirection.Input), new OracleParameter(":remarks", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, mouldID, ParameterDirection.Input), new OracleParameter(":UpdateUserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow = con.ExecuteNonQuery(sqlString, parameters); } con.Commit(); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Disconnect(); } } } /// /// 获取模具信息 /// /// /// public static ServiceResultEntity GetMouldData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int? mouldID = null; string mouldBarcode = null; if (cre.Properties.ContainsKey("MouldID")) { mouldID = cre.Properties.GetValue("MouldID"); } if (cre.Properties.ContainsKey("MouldBarcode")) { mouldBarcode = cre.Properties["MouldBarcode"].ToString(); } IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT m.MouldID\n" + " ,m.MouldCode\n" + " ,m.MouldBarcode\n" + " ,mtype.dictionaryvalue MouldTypeName\n" + " ,mms.SupplierName\n" + " ,m.ProductionDate\n" + " ,m.GoodsID\n" + " ,m.GoodsCode\n" + " ,g.GoodsName\n" + " ,g.GoodsSpecification\n" + " ,m.UserID\n" + " ,m.UserCode\n" + " ,m.Weight\n" + " ,m.Cost\n" + " ,m.StandardGroutingNum\n" + " ,m.OutputCount\n" + " ,m.GroutingNum GroutingNums\n" + " ,m.GroutingDateBegin\n" + " ,m.GroutingDateEnd\n" + " ,m.LastGroutingBatchNo\n" + " ,m.MouldStatus\n" + " ,ms.mouldstatusname\n" + " ,m.RepairFlag\n" + " ,m.ScrapDate\n" + " ,m.ScrapUserID\n" + " ,m.ScrapUserCode\n" + " ,ScReason.dictionaryvalue ScrapReason\n" + " ,m.ScrapResponsibility\n" + " ,gl.groutinglinecode\n" + " ,gld.groutingmouldcode\n" + " ,m.Remarks\n" + " ,m.CreateTime\n" + " ,cu.usercode CreateUserCode\n" + " ,nvl(mo.OutputNo,0) OutputNo\n" + " ,nvl(mo.GroutingNum, m.GroutingNum) GroutingNum\n" + " FROM tp_pc_mould m\n" + " LEFT JOIN tp_mst_datadictionary mtype\n" + " ON mtype.dictionaryid = m.mouldtype\n" + " LEFT JOIN tp_mst_mouldmaterialsuppliers mms\n" + " ON mms.SupplierID = m.MaterialSupplier\n" + " INNER JOIN tp_sys_mouldstatus ms\n" + " ON ms.mouldstatusid = m.MouldStatus\n" + " LEFT JOIN tp_mst_datadictionary ScReason\n" + " ON ScReason.dictionaryid = m.ScrapReason\n" + " LEFT JOIN tp_pc_groutinglinedetail gld\n" + " ON gld.groutinglinedetailid = m.GroutingLineDetailID\n" + " LEFT JOIN tp_pc_groutingline gl\n" + " ON gl.groutinglineid = gld.groutinglineid\n" + " INNER JOIN tp_mst_user cu\n" + " ON cu.userid = m.createuserid\n" + " INNER JOIN tp_mst_goods g\n" + " ON g.goodsid = m.goodsid\n" + " LEFT JOIN TP_PC_MouldOutput mo\n" + " ON mo.mouldid = m.mouldid\n" + " WHERE m.accountid = :accountid\n" + " AND m.valueflag = '1'\n" + " AND (:MouldBarcode IS NULL OR m.MouldBarcode = :MouldBarcode)\n" + " AND (:MouldStatus IS NULL OR\n" + " instr(',' || :MouldStatus || ',', ',' || m.MouldStatus || ',') > 0)\n" + " AND (:MouldID IS NULL OR m.MouldID = :MouldID)"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":MouldID", OracleDbType.Int32, mouldID, ParameterDirection.Input), new OracleParameter(":MouldBarcode", OracleDbType.NVarchar2, mouldBarcode, ParameterDirection.Input), new OracleParameter(":MouldStatus", OracleDbType.Varchar2, cre.Properties["MouldStatus"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); sre.Data.Tables[0].TableName = "InfoData"; return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 替换模具条码 /// /// /// public static ServiceResultEntity SetFPC1003Data(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int mouldID = Convert.ToInt32(cre.Properties["MouldID"]); string mouldBarcode = cre.Properties["MouldBarcode"].ToString(); string newMouldBarcode = cre.Properties["NewMouldBarcode"].ToString(); int retrunRow = 0; IDBTransaction con = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Connect(); // 验证 条码是否重复 string sql = "Select count(*) from TP_PC_Mould m where m.MouldBarcode = :MouldBarcode"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":MouldBarcode", OracleDbType.Varchar2, newMouldBarcode, ParameterDirection.Input), }; string count = con.GetSqlResultToStr(sql, paras); if ("0" != count) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "新模具条码【" + newMouldBarcode + "】已存在"; return sre; } int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual ")); sql = "INSERT INTO tp_pc_mouldchangehistory\n" + " (CHANGEID\n" + " ,MOULDID\n" + " ,MOULDCODE\n" + " ,MOULDBARCODE\n" + " ,GOODSID\n" + " ,GOODSCODE\n" + " ,OPERATIONTYPE\n" + " ,GroutingLineDetailID\n" + " ,MOULDSTATUS\n" + " ,MOULDSTATUSAFTER\n" + " ,GROUTINGNUM\n" + " ,REMARKS\n" + " ,CHANGEFLAG\n" + " ,MouldBarcodeAfter\n" + " ,ACCOUNTID\n" + " ,CREATEUSERID)\n" + " SELECT :changeID\n" + " ,MOULDID\n" + " ,MOULDCODE\n" + " ,:MOULDBARCODE\n" + " ,GOODSID\n" + " ,GOODSCODE\n" + " ,:OPERATIONTYPE\n" + " ,GroutingLineDetailID\n" + " ,MOULDSTATUS\n" + " ,MOULDSTATUS\n" + " ,GROUTINGNUM\n" + " ,:REMARKS\n" + " ,'0'\n" + " ,:NEWMOULDBARCODE\n" + " ,ACCOUNTID\n" + " ,CREATEUSERID\n" + " FROM tp_pc_mould m\n" + " WHERE m.mouldID = :MouldID"; paras = new OracleParameter[] { new OracleParameter(":MouldID", OracleDbType.Int32, mouldID, ParameterDirection.Input), new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32, (int)Constant.MouldOperationType.ChangeBarcode, ParameterDirection.Input), new OracleParameter(":changeID", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":NEWMOULDBARCODE", OracleDbType.NVarchar2, newMouldBarcode, ParameterDirection.Input), new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2, mouldBarcode, ParameterDirection.Input), new OracleParameter(":REMARKS", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); sql = "UPDATE tp_pc_mould m\n" + " SET m.mouldbarcode = :newmouldbarcode\n" + " ,m.updateuserid = :userid\n" + " ,m.lastchangehistoryid = :changeid\n" + " WHERE m.mouldid = :mouldid"; paras = new OracleParameter[] { new OracleParameter(":newmouldbarcode", OracleDbType.NVarchar2, newMouldBarcode, ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, mouldID, ParameterDirection.Input), new OracleParameter(":changeid", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); con.Commit(); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Disconnect(); } } } /// /// 获取模具操作画面期初数据 /// /// /// public static ServiceResultEntity GetFPC1004LoadData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int mouldOperationType = Convert.ToInt32(cre.Properties["MouldOperationType"]); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = null; OracleParameter[] parameters = null; if (mouldOperationType == (int)Constant.MouldOperationType.InvToScrap || mouldOperationType == (int)Constant.MouldOperationType.OnLineToScrap || mouldOperationType == (int)Constant.MouldOperationType.OutToScrap) { // 报废原因 sqlString = "SELECT dd.dictionaryid, dd.dictionaryvalue, dd.displayno\n" + " FROM TP_MST_DataDictionary dd\n" + " WHERE dd.DictionaryType = 'GMM001'\n" + " AND dd.valueflag = '1'\n" + " AND dd.accountid = :accountid\n" + " ORDER BY dd.displayno"; parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); } return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 模具操作 /// /// /// public static ServiceResultEntity SetFPC1004Data(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int mouldOperationType = Convert.ToInt32(cre.Properties["MouldOperationType"]); int retrunRow = 0; IDBTransaction con = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Connect(); OracleParameter[] paras = null; object goods = cre.Properties["GoodsID"]; string sql = "SELECT m.MouldID\n" + " ,m.MouldCode\n" + " ,m.MouldBarcode\n" + " ,m.GoodsID\n" + " ,m.GoodsCode\n" + " ,m.GroutingNum\n" + " ,m.MouldStatus\n" + //" ,m.RepairFlag\n" + //" ,m.ScrapDate\n" + //" ,m.ScrapUserID\n" + //" ,m.ScrapUserCode\n" + " ,m.GroutingLineDetailID\n" + " ,m.MaterialSupplier\n" + " ,m.mould_plan_id\n" + //add 夏常明 模具生产计划id " FROM tp_pc_mould m\n" + " WHERE m.accountid = :accountid\n" + " AND m.valueflag = '1'\n" + " AND m.MouldID in (" + cre.Properties["MouldIDs"] + ")\n" + " ORDER BY m.MouldBarcode for update"; paras = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), }; DataTable mouldTable = con.GetSqlResultToDt(sql, paras); // 变更型号 if (mouldOperationType == (int)Constant.MouldOperationType.ChangeGoodsCode) { #region //校验是否存在已报工模具 DataRow[] mouldPlanDt = mouldTable.Select(" mould_plan_id IS NOT NULL", null); if (mouldTable != null && mouldTable.Rows.Count > 0 && Convert.ToInt32(mouldTable.Rows[0]["MouldStatus"]) != 3 && mouldPlanDt.Length > 0) { sre.Message = "已报工模具，不能变更型号！"; sre.Status = Constant.ServiceResultStatus.Other; return sre; } foreach (DataRow item in mouldTable.Rows) { int mouldStatus = Convert.ToInt32(item["MouldStatus"]); if (mouldStatus != 1 && mouldStatus != 2 && mouldStatus != 3) { continue; } int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual ")); //操作类别 DataTable OPERATIONTYPE = con.GetSqlResultToDt(@"SELECT OPERATIONTYPE FROM TP_PC_MOULDCHANGEHISTORY WHERE CREATETIME=(SELECT MAX(CREATETIME) FROM TP_PC_MOULDCHANGEHISTORY WHERE MOULDID=" + item["MouldID"] + ") AND MOULDID = " + item["MouldID"] + "", null); sql = "INSERT INTO tp_pc_mouldchangehistory\n" + " (CHANGEID\n" + " ,MOULDID\n" + " ,MOULDCODE\n" + " ,MOULDBARCODE\n" + " ,GOODSID\n" + " ,GOODSCODE\n" + " ,OPERATIONTYPE\n" + " ,GroutingLineDetailID\n" + " ,MOULDSTATUS\n" + " ,MOULDSTATUSAFTER\n" + " ,GROUTINGNUM\n" + " ,REMARKS\n" + " ,CHANGEFLAG\n" + " ,GoodsIDAfter\n" + " ,GoodsCodeAfter\n" + " ,ACCOUNTID\n" + " ,CREATEUSERID)\n" + " Values( :changeID\n" + " ,:MOULDID\n" + " ,:MOULDCODE\n" + " ,:MOULDBARCODE\n" + " ,:GOODSID\n" + " ,:GOODSCODE\n" + " ,:OPERATIONTYPE\n" + " ,:GroutingLineDetailID\n" + " ,:MOULDSTATUS\n" + " ,:MOULDSTATUS\n" + " ,:GROUTINGNUM\n" + " ,:REMARKS\n" + " ,decode(:MOULDSTATUS,1,'3','0')\n" + " ,:GoodsIDAfter\n" + " ,:GoodsCodeAfter\n" + " ,:ACCOUNTID\n" + " ,:USERID\n" + ")"; paras = new OracleParameter[] { new OracleParameter(":GroutingLineDetailID", OracleDbType.Int32, item["GroutingLineDetailID"], ParameterDirection.Input), new OracleParameter(":changeID", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":MOULDID", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), new OracleParameter(":MOULDCODE", OracleDbType.Varchar2, item["MOULDCODE"], ParameterDirection.Input), new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2, item["MOULDBARCODE"], ParameterDirection.Input), new OracleParameter(":GoodsCodeAfter", OracleDbType.NVarchar2, cre.Properties["GoodsCode"], ParameterDirection.Input), new OracleParameter(":GoodsIDAfter", OracleDbType.Int32, cre.Properties["GoodsID"], ParameterDirection.Input), new OracleParameter(":GOODSID", OracleDbType.Int32, item["GOODSID"], ParameterDirection.Input), new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2, item["GOODSCODE"], ParameterDirection.Input), new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32, mouldOperationType, ParameterDirection.Input), new OracleParameter(":MOULDSTATUS", OracleDbType.Int32, item["MOULDSTATUS"], ParameterDirection.Input), new OracleParameter(":GROUTINGNUM", OracleDbType.Int32, item["GROUTINGNUM"], ParameterDirection.Input), new OracleParameter(":REMARKS", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); if (mouldStatus == 2) { // 改成型线明细的产品型号 sql = "UPDATE TP_PC_GroutingLineDetail gld\n" + " SET gld.goodsid = :goodsid\n" + " ,gld.updateuserid = :userid\n" + " WHERE gld.GroutingLineDetailID = :DetailID and gld.mouldid = :mouldid"; paras = new OracleParameter[] { new OracleParameter(":goodsid", OracleDbType.Int32, cre.Properties["GoodsID"], ParameterDirection.Input), new OracleParameter(":DetailID", OracleDbType.Int32, item["GroutingLineDetailID"], ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); } #region SAP模具变更接口传值 if (goods.ToString()!=item["GOODSID"].ToString() && (OPERATIONTYPE.Rows[0]["OPERATIONTYPE"].ToString() == "8" || OPERATIONTYPE.Rows[0]["OPERATIONTYPE"].ToString() == "-1")) { sql = @"SELECT m.MouldID, m.MOULDBARCODE, (SELECT g.mouldmaterialcode FROM tp_mst_goods g WHERE g.goodsid = :goodsID ) AS 变更后, g.mouldmaterialcode AS 变更前, m.STANDARDGROUTINGSUM - m.GroutingNum as 剩余注浆次数, CASE WHEN INSTR(GL.GROUTINGLINECODE, 'A') = 1 THEN '2110' WHEN (INSTR(GL.GROUTINGLINECODE, 'B') = 1 OR INSTR(GL.GROUTINGLINECODE, 'D') = 1) THEN '2210' WHEN INSTR(GL.GROUTINGLINECODE, 'C') = 1 THEN '2310' ELSE '3001' END AS 库存地点 FROM tp_pc_mould m left join tp_mst_goods g on m.goodsid = g.goodsid LEFT JOIN TP_PC_MOULDCHANGEHISTORY mhis on mhis.MOULDID=m.MOULDID LEFT JOIN TP_PC_GROUTINGLINEDETAIL gd on gd.GROUTINGLINEDETAILID=mhis.GROUTINGLINEDETAILID LEFT JOIN TP_PC_GROUTINGLINE gl ON gl.GROUTINGLINEID=gd.GROUTINGLINEID WHERE m.MouldID =:MouldIDs AND m.ACCOUNTID=:accountid AND m.VALUEFLAG='1' AND (mhis.OPERATIONTYPE='8') AND MHIS.CREATETIME=(SELECT MAX(CREATETIME) FROM TP_PC_MOULDCHANGEHISTORY WHERE MouldID =:MouldIDs AND ( OPERATIONTYPE='8')) "; paras = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.NVarchar2, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":MouldIDs", OracleDbType.NVarchar2, item["MouldID"], ParameterDirection.Input), new OracleParameter(":goodsID", OracleDbType.NVarchar2, goods.ToString(), ParameterDirection.Input), }; DataTable dt = con.GetSqlResultToDt(sql, paras); string ZTYPE; string ZMSG; if (dt != null && dt.Rows.Count > 0) { ZPPFM050(DateTime.Now.Date.ToString("yyyyMMdd"), dt, out ZTYPE, out ZMSG); string upsql = string.Empty; if ("S".Equals(ZTYPE)) { //数据库写入日志表名TP_SAP_MOULDCHANGELOG（成功）； upsql = @"INSERT INTO TP_SAP_MOULDCHANGELOG(WERKS,MOULDID,MOULDBARCODE,MATERIALCODEFORMER,MATERIALCODEAFTER,INVENTORYPLACE,MSGTY,MSGTX) VALUES(5000,:MOULDID,:MOULDBARCODE,:MATERIALCODEFORMER,:MATERIALCODEAFTER,:INVENTORYPLACE,:MSGTY,:MSGTX)"; paras = new OracleParameter[] { new OracleParameter(":MOULDID", OracleDbType.NVarchar2, dt.Rows[0]["MouldID"].ToString(), ParameterDirection.Input), new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2, dt.Rows[0]["MOULDBARCODE"].ToString(), ParameterDirection.Input), new OracleParameter(":MATERIALCODEFORMER", OracleDbType.NVarchar2, dt.Rows[0]["变更前"].ToString(), ParameterDirection.Input), new OracleParameter(":MATERIALCODEAFTER", OracleDbType.NVarchar2, dt.Rows[0]["变更后"].ToString(), ParameterDirection.Input), new OracleParameter(":INVENTORYPLACE", OracleDbType.NVarchar2, dt.Rows[0]["库存地点"].ToString(), ParameterDirection.Input), new OracleParameter(":MSGTY", OracleDbType.NVarchar2, ZTYPE, ParameterDirection.Input), new OracleParameter(":MSGTX", OracleDbType.NVarchar2, ZMSG, ParameterDirection.Input), }; con.ExecuteNonQuery(upsql, paras); sre.Message = ""; sre.Status = Constant.ServiceResultStatus.Success; } else if ("E".Equals(ZTYPE)) { //数据库写入日志表名TP_SAP_MOULDCHANGELOG（失败）； upsql = @"INSERT INTO TP_SAP_MOULDCHANGELOG(WERKS,MOULDID,MOULDBARCODE,MATERIALCODEFORMER,MATERIALCODEAFTER,INVENTORYPLACE,MSGTY,MSGTX) VALUES(5000,:MOULDID,:MOULDBARCODE,:MATERIALCODEFORMER,:MATERIALCODEAFTER,:INVENTORYPLACE,:MSGTY,:MSGTX)"; paras = new OracleParameter[] { new OracleParameter(":MOULDID", OracleDbType.NVarchar2, dt.Rows[0]["MouldID"].ToString(), ParameterDirection.Input), new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2, dt.Rows[0]["MOULDBARCODE"].ToString(), ParameterDirection.Input), new OracleParameter(":MATERIALCODEFORMER", OracleDbType.NVarchar2, dt.Rows[0]["变更前"].ToString(), ParameterDirection.Input), new OracleParameter(":MATERIALCODEAFTER", OracleDbType.NVarchar2, dt.Rows[0]["变更后"].ToString(), ParameterDirection.Input), new OracleParameter(":INVENTORYPLACE", OracleDbType.NVarchar2, dt.Rows[0]["库存地点"].ToString(), ParameterDirection.Input), new OracleParameter(":MSGTY", OracleDbType.NVarchar2, ZTYPE, ParameterDirection.Input), new OracleParameter(":MSGTX", OracleDbType.NVarchar2, ZMSG, ParameterDirection.Input), }; con.ExecuteNonQuery(upsql, paras); con.Commit(); sre.Message = ZMSG; sre.Status = Constant.ServiceResultStatus.Other; return sre; } } } #endregion //模具变更sql sql = "UPDATE tp_pc_mould m\n" + " SET m.goodsid = :goodsid\n" + " ,m.goodscode = :goodscode\n" + " ,m.updateuserid = :userid\n" + " ,m.lastchangehistoryid = :changeid\n" + " WHERE m.mouldid = :mouldid"; paras = new OracleParameter[] { new OracleParameter(":goodscode", OracleDbType.NVarchar2, cre.Properties["GoodsCode"], ParameterDirection.Input), new OracleParameter(":goodsid", OracleDbType.Int32, cre.Properties["GoodsID"], ParameterDirection.Input), new OracleParameter(":changeid", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); } #region 转模具料号SAP //待产 //sql = "SELECT m.MouldID\n" + // " ,m.MouldCode\n" + // " ,m.MouldBarcode\n" + // " ,m.GoodsID\n" + // " ,m.GoodsCode\n" + // " ,m.GroutingNum\n" + // " ,m.MouldStatus\n" + // " ,m.GroutingLineDetailID\n" + // " ,g.mouldmaterialcode\n" + // " ,m.STANDARDGROUTINGSUM - m.GroutingNum notusedGroutingNum\n" + // " FROM tp_pc_mould m\n" + // " left join tp_mst_goods g on m.goodsid = g.goodsid\n" + // " WHERE m.accountid = :accountid\n" + // " AND m.valueflag = '1'\n" + // " AND m.MouldID in (" + cre.Properties["MouldIDs"] + ")\n" + // " AND m.MOULDSTATUS = 3\n"; //paras = new OracleParameter[] //{ // new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), //}; //mouldTable = con.GetSqlResultToDt(sql, paras); //string ZTYPE; //string ZMSG; //DataTable dt = new DataTable(); //// 查询更新后的产品编码-物料编码 //string sqlString = "SELECT g.mouldmaterialcode FROM tp_mst_goods g WHERE g.goodscode = :goodsCode"; //paras = new OracleParameter[] //{ // new OracleParameter(":goodsCode", OracleDbType.NVarchar2,cre.Properties["GoodsCode"].ToString(), ParameterDirection.Input), //}; //DataTable newGoodsCodeTable = con.GetSqlResultToDt(sqlString, paras); ////更新前的模具信息 //sqlString = ""; //DataTable dtResult = ZPPFM050(DateTime.Now.Date.ToString("yyyyMMdd"), dt, out ZTYPE, out ZMSG); #endregion #endregion } // 模具领用 else if (mouldOperationType == (int)Constant.MouldOperationType.InvToOut) { #region foreach (DataRow item in mouldTable.Rows) { int mouldStatus = Convert.ToInt32(item["MouldStatus"]); if (mouldStatus != 1) { continue; } int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual ")); sql = "INSERT INTO tp_pc_mouldchangehistory\n" + " (CHANGEID\n" + " ,MOULDID\n" + " ,MOULDCODE\n" + " ,MOULDBARCODE\n" + " ,GOODSID\n" + " ,GOODSCODE\n" + " ,OPERATIONTYPE\n" + " ,MOULDSTATUS\n" + " ,MOULDSTATUSAFTER\n" + " ,GROUTINGNUM\n" + " ,REMARKS\n" + " ,CHANGEFLAG\n" + " ,ACCOUNTID\n" + " ,CREATEUSERID)\n" + " Values( :changeID\n" + " ,:MOULDID\n" + " ,:MOULDCODE\n" + " ,:MOULDBARCODE\n" + " ,:GOODSID\n" + " ,:GOODSCODE\n" + " ,:OPERATIONTYPE\n" + " ,:MOULDSTATUS\n" + " ,:MOULDSTATUSAFTER\n" + " ,:GROUTINGNUM\n" + " ,:REMARKS\n" + " ,'2'\n" + " ,:ACCOUNTID\n" + " ,:USERID\n" + ")"; paras = new OracleParameter[] { new OracleParameter(":changeID", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":MOULDID", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), new OracleParameter(":MOULDCODE", OracleDbType.Varchar2, item["MOULDCODE"], ParameterDirection.Input), new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2, item["MOULDBARCODE"], ParameterDirection.Input), new OracleParameter(":GOODSID", OracleDbType.Int32, item["GOODSID"], ParameterDirection.Input), new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2, item["GOODSCODE"], ParameterDirection.Input), new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32, mouldOperationType, ParameterDirection.Input), new OracleParameter(":MOULDSTATUSAFTER", OracleDbType.Int32, (int)Constant.MouldStatus.Out, ParameterDirection.Input), new OracleParameter(":MOULDSTATUS", OracleDbType.Int32, item["MOULDSTATUS"], ParameterDirection.Input), new OracleParameter(":GROUTINGNUM", OracleDbType.Int32, item["GROUTINGNUM"], ParameterDirection.Input), new OracleParameter(":REMARKS", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); sql = "UPDATE tp_pc_mould m\n" + " SET m.premouldstatus = m.mouldstatus\n" + " ,m.mouldstatus = :mouldstatus\n" + " ,m.updateuserid = :userid\n" + " ,m.lastchangehistoryid = :changeid\n" + " WHERE m.mouldid = :mouldid"; paras = new OracleParameter[] { new OracleParameter(":mouldstatus", OracleDbType.Int32, (int)Constant.MouldStatus.Out, ParameterDirection.Input), new OracleParameter(":changeid", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); } #endregion } // 模具回收 else if (mouldOperationType == (int)Constant.MouldOperationType.OutToInv) { #region foreach (DataRow item in mouldTable.Rows) { int mouldStatus = Convert.ToInt32(item["MouldStatus"]); if (mouldStatus != 3) { continue; } int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual ")); sql = "INSERT INTO tp_pc_mouldchangehistory\n" + " (CHANGEID\n" + " ,MOULDID\n" + " ,MOULDCODE\n" + " ,MOULDBARCODE\n" + " ,GOODSID\n" + " ,GOODSCODE\n" + " ,OPERATIONTYPE\n" + " ,MOULDSTATUS\n" + " ,MOULDSTATUSAFTER\n" + " ,GROUTINGNUM\n" + " ,REMARKS\n" + " ,CHANGEFLAG\n" + " ,ACCOUNTID\n" + " ,CREATEUSERID)\n" + " Values( :changeID\n" + " ,:MOULDID\n" + " ,:MOULDCODE\n" + " ,:MOULDBARCODE\n" + " ,:GOODSID\n" + " ,:GOODSCODE\n" + " ,:OPERATIONTYPE\n" + " ,:MOULDSTATUS\n" + " ,:MOULDSTATUSAFTER\n" + " ,:GROUTINGNUM\n" + " ,:REMARKS\n" + " ,'1'\n" + " ,:ACCOUNTID\n" + " ,:USERID\n" + ")"; paras = new OracleParameter[] { new OracleParameter(":changeID", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":MOULDID", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), new OracleParameter(":MOULDCODE", OracleDbType.Varchar2, item["MOULDCODE"], ParameterDirection.Input), new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2, item["MOULDBARCODE"], ParameterDirection.Input), new OracleParameter(":GOODSID", OracleDbType.Int32, item["GOODSID"], ParameterDirection.Input), new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2, item["GOODSCODE"], ParameterDirection.Input), new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32, mouldOperationType, ParameterDirection.Input), new OracleParameter(":MOULDSTATUSAFTER", OracleDbType.Int32, (int)Constant.MouldStatus.Inv, ParameterDirection.Input), new OracleParameter(":MOULDSTATUS", OracleDbType.Int32, item["MOULDSTATUS"], ParameterDirection.Input), new OracleParameter(":GROUTINGNUM", OracleDbType.Int32, item["GROUTINGNUM"], ParameterDirection.Input), new OracleParameter(":REMARKS", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); sql = "UPDATE tp_pc_mould m\n" + " SET m.premouldstatus = m.mouldstatus\n" + " ,m.mouldstatus = :mouldstatus\n" + " ,m.updateuserid = :userid\n" + " ,m.lastchangehistoryid = :changeid\n" + " WHERE m.mouldid = :mouldid"; paras = new OracleParameter[] { new OracleParameter(":mouldstatus", OracleDbType.Int32, (int)Constant.MouldStatus.Inv, ParameterDirection.Input), new OracleParameter(":changeid", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); } #endregion } // 报废撤销 else if (mouldOperationType == (int)Constant.MouldOperationType.ScrapToInv) { #region foreach (DataRow item in mouldTable.Rows) { int mouldStatus = Convert.ToInt32(item["MouldStatus"]); if (mouldStatus != 4) { continue; } int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual ")); sql = "INSERT INTO tp_pc_mouldchangehistory\n" + " (CHANGEID\n" + " ,MOULDID\n" + " ,MOULDCODE\n" + " ,MOULDBARCODE\n" + " ,GOODSID\n" + " ,GOODSCODE\n" + " ,OPERATIONTYPE\n" + " ,MOULDSTATUS\n" + " ,MOULDSTATUSAFTER\n" + " ,GROUTINGNUM\n" + " ,REMARKS\n" + " ,CHANGEFLAG\n" + " ,ACCOUNTID\n" + " ,CREATEUSERID)\n" + " Values( :changeID\n" + " ,:MOULDID\n" + " ,:MOULDCODE\n" + " ,:MOULDBARCODE\n" + " ,:GOODSID\n" + " ,:GOODSCODE\n" + " ,:OPERATIONTYPE\n" + " ,:MOULDSTATUS\n" + " ,:MOULDSTATUSAFTER\n" + " ,:GROUTINGNUM\n" + " ,:REMARKS\n" + " ,'1'\n" + " ,:ACCOUNTID\n" + " ,:USERID\n" + ")"; paras = new OracleParameter[] { new OracleParameter(":changeID", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":MOULDID", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), new OracleParameter(":MOULDCODE", OracleDbType.Varchar2, item["MOULDCODE"], ParameterDirection.Input), new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2, item["MOULDBARCODE"], ParameterDirection.Input), new OracleParameter(":GOODSID", OracleDbType.Int32, item["GOODSID"], ParameterDirection.Input), new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2, item["GOODSCODE"], ParameterDirection.Input), new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32, mouldOperationType, ParameterDirection.Input), new OracleParameter(":MOULDSTATUSAFTER", OracleDbType.Int32, (int)Constant.MouldStatus.Inv, ParameterDirection.Input), new OracleParameter(":MOULDSTATUS", OracleDbType.Int32, item["MOULDSTATUS"], ParameterDirection.Input), new OracleParameter(":GROUTINGNUM", OracleDbType.Int32, item["GROUTINGNUM"], ParameterDirection.Input), new OracleParameter(":REMARKS", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); sql = "UPDATE tp_pc_mould m\n" + " SET m.premouldstatus = m.mouldstatus\n" + " ,m.mouldstatus = :mouldstatus\n" + " ,m.updateuserid = :userid\n" + " ,m.lastchangehistoryid = :changeid\n" + " ,m.ScrapDate = null\n" + " ,m.ScrapUserID = null\n" + " ,m.ScrapUserCode = null\n" + " ,m.ScrapReason = null\n" + " ,m.ScrapResponsibility = null\n" + " WHERE m.mouldid = :mouldid"; paras = new OracleParameter[] { new OracleParameter(":mouldstatus", OracleDbType.Int32, (int)Constant.MouldStatus.Inv, ParameterDirection.Input), new OracleParameter(":changeid", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); } #endregion } // 报废 else if (mouldOperationType == (int)Constant.MouldOperationType.InvToScrap) { #region foreach (DataRow item in mouldTable.Rows) { int mouldStatus = Convert.ToInt32(item["MouldStatus"]); if (mouldStatus != 1 && mouldStatus != 3) { continue; } int mouldHistoryID = Convert.ToInt32(con.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual ")); sql = "INSERT INTO tp_pc_mouldchangehistory\n" + " (CHANGEID\n" + " ,MOULDID\n" + " ,MOULDCODE\n" + " ,MOULDBARCODE\n" + " ,GOODSID\n" + " ,GOODSCODE\n" + " ,OPERATIONTYPE\n" + " ,MOULDSTATUS\n" + " ,MOULDSTATUSAFTER\n" + " ,GROUTINGNUM\n" + " ,REMARKS\n" + " ,CHANGEFLAG\n" + " ,ScrapReason\n" + " ,ScrapResponsibility\n" + " ,ACCOUNTID\n" + " ,CREATEUSERID)\n" + " Values( :changeID\n" + " ,:MOULDID\n" + " ,:MOULDCODE\n" + " ,:MOULDBARCODE\n" + " ,:GOODSID\n" + " ,:GOODSCODE\n" + " ,:OPERATIONTYPE\n" + " ,:MOULDSTATUS\n" + " ,:MOULDSTATUSAFTER\n" + " ,:GROUTINGNUM\n" + " ,:REMARKS\n" + " ,:CHANGEFLAG\n" + " ,:ScrapReason\n" + " ,:ScrapResponsibility\n" + " ,:ACCOUNTID\n" + " ,:USERID\n" + ")"; paras = new OracleParameter[] { new OracleParameter(":changeID", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":MOULDID", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), new OracleParameter(":MOULDCODE", OracleDbType.Varchar2, item["MOULDCODE"], ParameterDirection.Input), new OracleParameter(":MOULDBARCODE", OracleDbType.NVarchar2, item["MOULDBARCODE"], ParameterDirection.Input), new OracleParameter(":GOODSID", OracleDbType.Int32, item["GOODSID"], ParameterDirection.Input), new OracleParameter(":GOODSCODE", OracleDbType.NVarchar2, item["GOODSCODE"], ParameterDirection.Input), new OracleParameter(":OPERATIONTYPE", OracleDbType.Int32, (int)(mouldStatus == 1 ? Constant.MouldOperationType.InvToScrap : Constant.MouldOperationType.OutToScrap) , ParameterDirection.Input), new OracleParameter(":CHANGEFLAG", OracleDbType.Char, (mouldStatus == 1 ? "2" : "0") , ParameterDirection.Input), new OracleParameter(":MOULDSTATUSAFTER", OracleDbType.Int32, (int)Constant.MouldStatus.Scrap, ParameterDirection.Input), new OracleParameter(":MOULDSTATUS", OracleDbType.Int32, item["MOULDSTATUS"], ParameterDirection.Input), new OracleParameter(":GROUTINGNUM", OracleDbType.Int32, item["GROUTINGNUM"], ParameterDirection.Input), new OracleParameter(":REMARKS", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":ScrapReason", OracleDbType.Int32, cre.Properties["ScrapReason"], ParameterDirection.Input), new OracleParameter(":ScrapResponsibility", OracleDbType.NVarchar2, cre.Properties["ScrapResponsibility"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); sql = "UPDATE tp_pc_mould m\n" + " SET m.premouldstatus = m.mouldstatus\n" + " ,m.mouldstatus = :mouldstatus\n" + " ,m.updateuserid = :userid\n" + " ,m.lastchangehistoryid = :changeid\n" + " ,m.scrapdate = SYSDATE\n" + " ,m.scrapuserid = :scrapuserid\n" + " ,m.scrapusercode = :scrapusercode\n" + " ,m.ScrapReason = :ScrapReason\n" + " ,m.ScrapResponsibility = :ScrapResponsibility\n" + " WHERE m.mouldid = :mouldid"; paras = new OracleParameter[] { new OracleParameter(":mouldstatus", OracleDbType.Int32, (int)Constant.MouldStatus.Scrap, ParameterDirection.Input), new OracleParameter(":changeid", OracleDbType.Int32, mouldHistoryID, ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), new OracleParameter(":scrapuserid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":scrapusercode", OracleDbType.NVarchar2, sUserInfo.UserCode, ParameterDirection.Input), new OracleParameter(":ScrapReason", OracleDbType.Int32, cre.Properties["ScrapReason"], ParameterDirection.Input), new OracleParameter(":ScrapResponsibility", OracleDbType.NVarchar2, cre.Properties["ScrapResponsibility"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sql, paras); } #endregion } // 撤销报工 else if (mouldOperationType == (int)Constant.MouldOperationType.UnMouldPlan) { //校验是否存在未报工模具 DataRow[] mouldPlanDt = mouldTable.Select(" mould_plan_id IS NULL", null); if (mouldPlanDt.Length > 0) { sre.Message = "存在未报工模具！"; sre.Status = Constant.ServiceResultStatus.Other; return sre; } foreach (DataRow item in mouldTable.Rows) { string sqlString = "SELECT mp.plan_qty - mp.make_qty AS incomplete,mp.PLAN_CODE\n" + " FROM t_mould_plan mp\n" + " WHERE mp.plan_id = :mould_plan_id"; OracleParameter[] paras1 = new OracleParameter[] { new OracleParameter(":mould_plan_id", OracleDbType.Varchar2, item["mould_plan_id"], ParameterDirection.Input), }; DataTable dt = con.GetSqlResultToDt(sqlString, paras1); string plan_code = dt.Rows[0]["PLAN_CODE"].ToString(); // 撤销报工（清空模具关联模具生产计划id） sqlString = "UPDATE tp_pc_mould\n" + " SET mould_plan_id = NULL\n" + " ,updateuserid =:userid\n" + " WHERE mouldid = :mouldid"; paras = new OracleParameter[] { new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sqlString, paras); // 报工计划 完成数量-1 sqlString = "UPDATE t_mould_plan\n" + " SET make_qty = make_qty - 1\n" + " ,updateuserid =:userid\n" + " WHERE plan_id = :plan_id"; paras = new OracleParameter[] { new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":plan_id", OracleDbType.Int32, item["mould_plan_id"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sqlString, paras); //报工计划履历 sqlString = "INSERT INTO t_mould_plan_report_his\n" + " (his_type\n" + " ,plan_id\n" + " ,plan_code\n" + " ,mouldid\n" + " ,goodsid\n" + " ,materialsupplier\n" + " ,remarks\n" + " ,CREATEUSER\n" + " ,accountid)\n" + "VALUES\n" + " (-1\n" + " ,:plan_id\n" + " ,:plan_code\n" + " ,:mouldid\n" + " ,:goodsid\n" + " ,:materialsupplier\n" + " ,:remarks\n" + " ,:CREATEUSER\n" + " ,:accountid)"; paras = new OracleParameter[] { new OracleParameter(":plan_id", OracleDbType.Int32, item["MOULD_PLAN_ID"], ParameterDirection.Input),// 模具生产计划id new OracleParameter(":plan_code", OracleDbType.Varchar2, plan_code ,ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), new OracleParameter(":goodsid", OracleDbType.Int32, item["GoodsID"] ,ParameterDirection.Input), new OracleParameter(":materialsupplier", OracleDbType.Int32, item["MaterialSupplier"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":remarks", OracleDbType.Varchar2, cre.Properties["Remarks"] ,ParameterDirection.Input), new OracleParameter(":CREATEUSER", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sqlString, paras); } } // 生产报工 else if (mouldOperationType == (int)Constant.MouldOperationType.MouldPlan) { int mould_plan_id = Convert.ToInt32(cre.Properties["mould_plan_id"]); //校验是否存在已报工模具 DataRow[] mouldPlanDt = mouldTable.Select(" mould_plan_id IS not NULL", null); if (mouldPlanDt.Length > 0) { sre.Message = "存在已报工模具！"; sre.Status = Constant.ServiceResultStatus.Other; return sre; } DataView dataView = new DataView(mouldTable); DataTable dts = dataView.ToTable(true, "GoodsID"); if (dts.Rows.Count > 1) { sre.Message = "存在不同的产品型号！"; sre.Status = Constant.ServiceResultStatus.Other; return sre; } string sqlString = "SELECT mp.plan_qty - mp.make_qty AS incomplete,mp.PLAN_CODE\n" + " FROM t_mould_plan mp\n" + " WHERE mp.plan_id = :mould_plan_id"; OracleParameter[] paras1 = new OracleParameter[] { new OracleParameter(":mould_plan_id", OracleDbType.Varchar2, mould_plan_id, ParameterDirection.Input), }; DataTable dt = con.GetSqlResultToDt(sqlString, paras1); //生产计划未完成数量 int incomplete = Convert.ToInt32(dt.Rows[0]["incomplete"]); if (incomplete < mouldTable.Rows.Count) { sre.Message = "报工数量大于生产计划未完成数量！"; sre.Status = Constant.ServiceResultStatus.Other; return sre; } string plan_code = dt.Rows[0]["PLAN_CODE"].ToString(); foreach (DataRow item in mouldTable.Rows) { // 生产报工（清空模具关联模具生产计划id） sqlString = "UPDATE tp_pc_mould\n" + " SET mould_plan_id = :mould_plan_id\n" + " ,updateuserid =:userid\n" + " WHERE mouldid = :mouldid"; paras = new OracleParameter[] { new OracleParameter(":mould_plan_id", OracleDbType.Varchar2, mould_plan_id, ParameterDirection.Input), new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sqlString, paras); // 报工计划 完成数量+1 sqlString = "UPDATE t_mould_plan\n" + " SET make_qty = make_qty + 1\n" + " ,updateuserid =:userid\n" + " WHERE plan_id = :plan_id"; paras = new OracleParameter[] { new OracleParameter(":userid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":plan_id", OracleDbType.Int32, mould_plan_id, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sqlString, paras); //报工计划履历 sqlString = "INSERT INTO t_mould_plan_report_his\n" + " (his_type\n" + " ,plan_id\n" + " ,plan_code\n" + " ,mouldid\n" + " ,goodsid\n" + " ,materialsupplier\n" + " ,remarks\n" + " ,CREATEUSER\n" + " ,accountid)\n" + "VALUES\n" + " (1\n" + " ,:plan_id\n" + " ,:plan_code\n" + " ,:mouldid\n" + " ,:goodsid\n" + " ,:materialsupplier\n" + " ,:remarks\n" + " ,:CREATEUSER\n" + " ,:accountid)"; paras = new OracleParameter[] { new OracleParameter(":plan_id", OracleDbType.Int32, mould_plan_id, ParameterDirection.Input),// 模具生产计划id new OracleParameter(":plan_code", OracleDbType.Varchar2, plan_code ,ParameterDirection.Input), new OracleParameter(":mouldid", OracleDbType.Int32, item["MouldID"], ParameterDirection.Input), new OracleParameter(":goodsid", OracleDbType.Int32, item["GoodsID"] ,ParameterDirection.Input), new OracleParameter(":materialsupplier", OracleDbType.Int32, item["MaterialSupplier"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":remarks", OracleDbType.Varchar2, cre.Properties["Remarks"] ,ParameterDirection.Input), new OracleParameter(":CREATEUSER", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; retrunRow += con.ExecuteNonQuery(sqlString, paras); } } con.Commit(); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Disconnect(); } } } /// /// 保存试验标识 /// /// /// public static ServiceResultEntity SaveTestMouldFlag(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction con = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string testMouldFlag = cre.Properties["TestMouldFlag"] + ""; con.Connect(); OracleParameter[] paras = null; string sql = @" SELECT DISTINCT G.GROUTINGLINEID ,G.GROUTINGLINECODE ,G.TESTFLAG FROM TP_PC_GROUTINGLINE G INNER JOIN TP_PC_GROUTINGLINEDETAIL GD ON GD.GROUTINGLINEID = G.GROUTINGLINEID WHERE INSTR(:MOULDIDS, ',' || GD.MOULDID || ',') > 0 "; paras = new OracleParameter[] { new OracleParameter(":MOULDIDS", OracleDbType.Varchar2, cre.Properties["MouldIDs"], ParameterDirection.Input), }; DataTable dt = con.GetSqlResultToDt(sql, paras); foreach (DataRow row in dt.Rows) { // 如果要改成‘0’，但成型线整线试验，则不允许改 if (testMouldFlag.Equals("0") && (row["TESTFLAG"] + "").Equals("1")) { sre.OtherStatus = -1; sre.Message = row["GROUTINGLINECODE"] + "是试验成型线，不能改为非试验模具。"; return sre; } } sql = @" UPDATE TP_PC_MOULD M SET M.TESTMOULDFLAG = :TESTMOULDFLAG, M.UPDATEUSERID = :USERID WHERE INSTR(:MOULDIDS, ',' || M.MOULDID || ',') > 0 "; paras = new OracleParameter[] { new OracleParameter(":TESTMOULDFLAG", OracleDbType.Char, testMouldFlag, ParameterDirection.Input), new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter(":MOULDIDS", OracleDbType.Varchar2, cre.Properties["MouldIDs"], ParameterDirection.Input), }; sre.OtherStatus += con.ExecuteNonQuery(sql, paras); sql = @" INSERT INTO TP_PC_MOULDHISTORY (MOULDID, TESTMOULDFLAG, ACCOUNTID, CREATEUSERID) VALUES (:MOULDID, :TESTMOULDFLAG, :ACCOUNTID, :USERID) "; string[] mouldids = (cre.Properties["MouldIDs"] + "").Split(','); foreach (string mouldid in mouldids) { if (!string.IsNullOrEmpty(mouldid)) { paras = new OracleParameter[] { new OracleParameter(":MOULDID", OracleDbType.Varchar2, mouldid, ParameterDirection.Input), new OracleParameter(":TESTMOULDFLAG", OracleDbType.Char, testMouldFlag, ParameterDirection.Input), new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":USERID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), }; sre.OtherStatus += con.ExecuteNonQuery(sql, paras); } } foreach (DataRow row in dt.Rows) { sql = @" SELECT 1 FROM TP_PC_GROUTINGLINE G INNER JOIN TP_PC_GROUTINGLINEDETAIL GD ON GD.GROUTINGLINEID = G.GROUTINGLINEID INNER JOIN TP_PC_MOULD M ON M.MOULDID = GD.MOULDID WHERE M.TESTMOULDFLAG = '0' AND G.GROUTINGLINEID = :GROUTINGLINEID "; paras = new OracleParameter[] { new OracleParameter(":GROUTINGLINEID", OracleDbType.Int32, row["GROUTINGLINEID"], ParameterDirection.Input), }; object isExists = con.GetSqlResultToObj(sql, paras); if (isExists == null) { sql = @" UPDATE TP_PC_GROUTINGLINE SET TESTFLAG = '1' WHERE GROUTINGLINEID = :GROUTINGLINEID "; paras = new OracleParameter[] { new OracleParameter(":GROUTINGLINEID", OracleDbType.Int32, row["GROUTINGLINEID"], ParameterDirection.Input), }; sre.OtherStatus += con.ExecuteNonQuery(sql, paras); } } con.Commit(); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Disconnect(); } } } /// /// sap接口-模具型号变更-转模具料号接口 ZPPFM050 /// /// /// /// public static void ZPPFM050(string BUDAT, DataTable dtTable, out string ZTYPE, out string ZMSG) { RfcConfigParameters rfcPara = new RfcConfigParameters(); rfcPara.Add(RfcConfigParameters.AppServerHost, appServerHost); rfcPara.Add(RfcConfigParameters.SystemNumber, systemNumber); rfcPara.Add(RfcConfigParameters.User, user); rfcPara.Add(RfcConfigParameters.Password, password); rfcPara.Add(RfcConfigParameters.Client, client); rfcPara.Add(RfcConfigParameters.Name, "CON"); rfcPara.Add(RfcConfigParameters.Language, "ZH"); rfcPara.Add(RfcConfigParameters.PoolSize, "5"); rfcPara.Add(RfcConfigParameters.ConnectionIdleTimeout, "60"); RfcDestination rfcDest = RfcDestinationManager.GetDestination(rfcPara); RfcRepository rfcRep = rfcDest.Repository; //接口API IRfcFunction rfcApi = rfcRep.CreateFunction("ZPPFM050"); //WERKS CHAR 4 0 工厂 Y 5000 //MATNR_B CHAR 40 0 物料编号_转换前 Y 211860087N2000 //MATNR_A CHAR 40 0 物料编号_转换后 Y 211860088N2000 //MENGE QUAN 13 3 数量 Y 50 //MEINS UNIT 3 0 基本计量单位 Y CI //BUDAT DATS 8 0 凭证中的过帐日期 Y 格式: yyyymmdd //LGORT CHAR 4 0 库存地点 Y 2110、2210、2310 //输入参数 IRfcStructure imTable = rfcApi.GetStructure("IS_ITAB"); foreach (DataRow dr in dtTable.Rows) { imTable.SetValue("WERKS", "5000"); imTable.SetValue("MATNR_B", dr["变更前"]); imTable.SetValue("MATNR_A", dr["变更后"]); imTable.SetValue("MENGE", dr["剩余注浆次数"]); imTable.SetValue("MEINS", "CI"); imTable.SetValue("BUDAT", BUDAT); imTable.SetValue("LGORT", dr["库存地点"]); } //调用接口 rfcApi.Invoke(rfcDest); //获取输出 ZTYPE = rfcApi.GetValue("ZTYPE").ToString(); ZMSG = rfcApi.GetValue("ZMSG").ToString();; rfcDest = null; rfcRep = null; } /// /// IRfcTable转DataTable /// /// /// private static DataTable GetDataTableFromRFCTable(IRfcTable myrfcTable) { DataTable loTable = new DataTable(); int liElement = 0; for (liElement = 0; liElement <= myrfcTable.ElementCount - 1; liElement++) { RfcElementMetadata metadata = myrfcTable.GetElementMetadata(liElement); loTable.Columns.Add(metadata.Name); } foreach (IRfcStructure Row in myrfcTable) { DataRow ldr = loTable.NewRow(); for (liElement = 0; liElement <= myrfcTable.ElementCount - 1; liElement++) { RfcElementMetadata metadata = myrfcTable.GetElementMetadata(liElement); ldr[metadata.Name] = Row.GetString(metadata.Name); } loTable.Rows.Add(ldr); } return loTable; } #endregion #region 模具盘点 /// /// 获取盘点单列表（searchbox用） /// /// /// public static ServiceResultEntity GetMouldCheckedList(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select CheckedID inCheckedID,CheckedNo inCheckedNo,AccountDate,Remarks from TP_PC_MouldChecked where accountid=:accountid and ValueFlag=1 order by CheckedID desc"; List parameters = new List(); parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input)); sre.Data = con.GetSqlResultToDs(sqlString, parameters.ToArray()); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取盘点单列表（一览用） /// /// /// public static ServiceResultEntity GetAllMouldCheckList(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT gbc.checkedid\n" + " ,gbc.checkedno\n" + " ,gbc.checkname\n" + " ,gbc.remarks\n" + " ,gbc.createtime\n" + " ,u.usercode CreateUserCode\n" + " ,u.username CreateUserName\n" + " FROM TP_PC_MouldChecked gbc\n" + " LEFT JOIN tp_mst_user u\n" + " ON u.userid = gbc.createuserid\n" + " WHERE gbc.valueflag = '1'\n" + " AND gbc.accountid = :accountid\n" + " AND (:datebegin IS NULL OR gbc.createtime >= :datebegin)\n" + " AND (:dateend IS NULL OR gbc.createtime <= :dateend)\n" + " AND (:checkedno IS NULL OR instr(gbc.checkedno, :checkedno) > 0)\n" + " AND (:checkname IS NULL OR instr(gbc.checkname, :checkname) > 0)\n" + " AND (:remarks IS NULL OR instr(gbc.remarks, :remarks) > 0)" + " ORDER BY checkedid desc"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":datebegin", OracleDbType.Date, cre.Properties["datebegin"], ParameterDirection.Input), new OracleParameter(":dateend", OracleDbType.Date, cre.Properties["dateend"], ParameterDirection.Input), new OracleParameter(":checkedno", OracleDbType.NVarchar2, cre.Properties["checkedno"], ParameterDirection.Input), new OracleParameter(":checkname", OracleDbType.NVarchar2, cre.Properties["checkname"], ParameterDirection.Input), new OracleParameter(":remarks", OracleDbType.NVarchar2, cre.Properties["remarks"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取盘点单明细（一览用） /// /// /// public static ServiceResultEntity GetAllMouldCheckDetail(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { sre.Data = new DataSet(); con.Open(); string sqlString = "SELECT gbu.checkedid, u.userid, u.usercode, u.username\n" + " FROM Tp_PC_Mouldcheckeduser gbu\n" + " LEFT JOIN tp_mst_user u\n" + " ON u.userid = gbu.userid\n" + " WHERE gbu.checkedid = :checkedid"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":checkedid", OracleDbType.Int32, checkedID, ParameterDirection.Input), }; DataTable userTable = con.GetSqlResultToDt(sqlString, parameters); userTable.TableName = "UserTable"; sre.Data.Tables.Add(userTable); sqlString = "SELECT 0 sel\n" + " ,gbcd.CheckedID -- 盘点单ID\n" + " ,gbcd.mouldBarCode -- 模具条码\n" + " ,gbcd.MouldID\n" + " ,mtype.dictionaryvalue MouldTypeName\n" + " ,mms.SupplierName\n" + " ,gbcd.ProductionDate\n" + " ,gbcd.GoodsID\n" + " ,gbcd.GoodsCode\n" + " ,gbcd.UserID\n" + " ,gbcd.UserCode\n" + " ,gbcd.Weight\n" + " ,gbcd.Cost\n" + //" ,gbcd.StandardGroutingNum\n" + " ,m.STANDARDGROUTINGSUM StandardGroutingNum\n" + " ,m.STANDARDGROUTINGSUM - m.GroutingNum notusedGroutingNum\n" + " ,m.GroutingNum\n" + " ,m.GroutingDateBegin\n" + " ,m.GroutingDateEnd\n" + " ,m.LastGroutingBatchNo\n" + " ,m.MouldStatus\n" + //" ,gbcd.GroutingNum\n" + //" ,gbcd.GroutingDateBegin\n" + //" ,gbcd.GroutingDateEnd\n" + //" ,gbcd.LastGroutingBatchNo\n" + //" ,gbcd.MouldStatus\n" + " ,ms.mouldstatusname\n" + " ,gbcd.RepairFlag\n" + " ,gbcd.ScrapDate\n" + " ,gbcd.ScrapUserID\n" + " ,gbcd.ScrapUserCode\n" + " ,ScReason.dictionaryvalue ScrapReason\n" + " ,gbcd.ScrapResponsibility\n" + " ,gl.groutinglinecode\n" + " ,gld.groutingmouldcode\n" + " ,gbcd.Remarks -- 备注\n" + " ,gbcd.CheckedFlag -- 盘点标识\n" + " ,gbcd.CheckedDate -- 盘点时间\n" + " ,CUser.usercode CheckedUserCode -- 盘点工号\n" + " ,g.mouldmaterialcode\n" + " FROM tp_pc_mouldcheckeddetail gbcd\n" + " LEFT JOIN tp_mst_datadictionary mtype\n" + " ON mtype.dictionaryid = gbcd.mouldtype\n" + " LEFT JOIN tp_pc_mould m\n" + " ON m.MouldID = gbcd.MouldID\n" + " LEFT JOIN tp_mst_goods g\n" + " ON g.goodsid = m.goodsid\n" + " LEFT JOIN tp_mst_mouldmaterialsuppliers mms\n" + " ON mms.SupplierID = gbcd.MaterialSupplier\n" + " INNER JOIN tp_sys_mouldstatus ms\n" + " ON ms.mouldstatusid = gbcd.MouldStatus\n" + " LEFT JOIN tp_mst_datadictionary ScReason\n" + " ON ScReason.dictionaryid = gbcd.ScrapReason\n" + " LEFT JOIN tp_pc_groutinglinedetail gld\n" + " ON gld.groutinglinedetailid = gbcd.GroutingLineDetailID\n" + " LEFT JOIN tp_pc_groutingline gl\n" + " ON gl.groutinglineid = gld.groutinglineid\n" + " LEFT JOIN tp_mst_user CUser\n" + " ON CUser.userid = gbcd.CheckedUserID" + " WHERE gbcd.checkedid = :checkedid\n" + " AND gbcd.checkedflag = :checkedflag"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":checkedflag",OracleDbType.Int32,(int)Constant.InCheckedFlag.InCheckedNo,ParameterDirection.Input), new OracleParameter(":checkedid",OracleDbType.Int32,checkedID,ParameterDirection.Input), }; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, paras)); paras[0].Value = (int)Constant.InCheckedFlag.InCheckeded; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, paras)); paras[0].Value = (int)Constant.InCheckedFlag.InCheckedWin; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, paras)); paras[0].Value = (int)Constant.InCheckedFlag.Loss; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, paras)); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取盘点单信息（编辑用） /// /// /// public static ServiceResultEntity GetMouldCheckedInfoForUpdate(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT gbc.checkedid, gbc.checkedno, gbc.checkname, gbc.remarks\n" + " FROM TP_PC_MouldChecked gbc\n" + " WHERE gbc.checkedid = :checkedid"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":checkedid", OracleDbType.Int32, checkedID, ParameterDirection.Input) }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); sqlString = "SELECT u.userid, u.usercode, u.username\n" + " FROM Tp_PC_Mouldcheckeduser gbu\n" + " INNER JOIN tp_mst_user u\n" + " ON u.userid = gbu.userid\n" + " WHERE gbu.checkedid = :checkedid" + " ORDER BY u.usercode"; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters)); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 保存盘点单 /// /// 用户基本信息 /// public static ServiceResultEntity SaveMouldChecked(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]); string checkedName = cre.Properties["CheckedName"].ToString(); string remarks = cre.Properties["Remarks"].ToString(); int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sql = null; OracleParameter[] paras = null; if (checkedID > 0) { // 编辑 sql = "UPDATE TP_PC_MouldChecked gbc\n" + " SET gbc.checkname = :checkname\n" + " ,gbc.remarks = :remarks\n" + " ,gbc.updateuserid = :updateuserid\n" + " WHERE gbc.checkedid = :checkedid"; paras = new OracleParameter[] { new OracleParameter(":CheckedID",OracleDbType.Int32, checkedID,ParameterDirection.Input), new OracleParameter(":checkname",OracleDbType.NVarchar2, checkedName,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.NVarchar2, remarks,ParameterDirection.Input), new OracleParameter(":updateuserid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input) }; returnRows += oracleTrConn.ExecuteNonQuery(sql, paras); sql = "DELETE FROM Tp_PC_Mouldcheckeduser gbu\n" + " WHERE gbu.checkedid = :checkedid"; paras = new OracleParameter[] { new OracleParameter(":CheckedID",OracleDbType.Int32, checkedID,ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sql, paras); if (cre.Data != null && cre.Data.Tables.Count > 0) { sql = "INSERT INTO Tp_PC_Mouldcheckeduser gbu\n" + " (checkedid, userid, usercode)\n" + "VALUES\n" + " (:checkedid, :userid, :usercode)"; foreach (DataRow item in cre.Data.Tables[0].Rows) { paras = new OracleParameter[] { new OracleParameter(":CheckedID",OracleDbType.Int32, checkedID,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, item["UserID"],ParameterDirection.Input), new OracleParameter(":usercode",OracleDbType.NVarchar2, item["UserCode"],ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sql, paras); } } } else { // 新建 //盘点单号 string checkedNo = string.Format("MIC{0}", DateTime.Now.ToString("yyyyMMddHHmmss")); //获得账务日期 DateTime accountDate = CommonModuleLogic.CommonModuleLogic.GetAccountDate(oracleTrConn, sUserInfo); //盘点单ID sql = "select SEQ_PC_MouldChecked_ID.nextval from dual"; checkedID = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sql)); #region 插入盘点单主表信息 sql = @"insert into TP_PC_MouldChecked ( CheckedID, CheckedNo, AccountDate, Remarks, AccountID, CreateUserID, UpdateUserID, CHECKNAME ) values ( :CheckedID, :CheckedNo, :AccountDate, :Remarks, :AccountID, :CreateUserID, :CreateUserID, :CHECKNAME )"; OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":CheckedID",OracleDbType.Int32, checkedID,ParameterDirection.Input), new OracleParameter(":CheckedNo",OracleDbType.NVarchar2, checkedNo,ParameterDirection.Input), new OracleParameter(":AccountDate",OracleDbType.Date, accountDate,ParameterDirection.Input), new OracleParameter(":Remarks",OracleDbType.NVarchar2, remarks,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":CHECKNAME",OracleDbType.NVarchar2, checkedName,ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sql, Paras); #endregion #region 盘点工号 if (cre.Data != null && cre.Data.Tables.Count > 0) { sql = "INSERT INTO Tp_PC_Mouldcheckeduser gbu\n" + " (checkedid, userid, usercode)\n" + "VALUES\n" + " (:checkedid, :userid, :usercode)"; foreach (DataRow item in cre.Data.Tables[0].Rows) { paras = new OracleParameter[] { new OracleParameter(":CheckedID",OracleDbType.Int32, checkedID,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, item["UserID"],ParameterDirection.Input), new OracleParameter(":usercode",OracleDbType.NVarchar2, item["UserCode"],ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sql, paras); } } #endregion #region 盘点信息 sql = "INSERT INTO TP_PC_MouldCHECKEDDETAIL\n" + " (CHECKEDID\n" + " ,CHECKEDNO\n" + " ,MouldID\n" + " ,MouldCODE\n" + " ,MouldBARCODE\n" + " ,mouldtype\n" + " ,materialsupplier\n" + " ,productiondate\n" + " ,goodsid\n" + " ,goodscode\n" + " ,userid\n" + " ,usercode\n" + " ,weight\n" + " ,cost\n" + " ,standardgroutingnum\n" + " ,groutingnum\n" + " ,groutingdatebegin\n" + " ,groutingdateend\n" + " ,lastgroutingbatchno\n" + " ,mouldstatus\n" + " ,premouldstatus\n" + " ,repairflag\n" + " ,scrapdate\n" + " ,scrapuserid\n" + " ,scrapusercode\n" + " ,scrapreason\n" + " ,scrapresponsibility\n" + " ,lastchangehistoryid\n" + " ,groutinglinedetailid\n" + " ,REMARKS\n" + " ,ACCOUNTID\n" + " ,VALUEFLAG\n" + " ,CREATEUSERID\n" + " ,UPDATEUSERID)\n" + " SELECT :CHECKEDID\n" + " ,:CHECKEDNO\n" + " ,gdd.MouldID\n" + " ,gdd.MouldCODE\n" + " ,gdd.MouldBARCODE\n" + " ,gdd.mouldtype\n" + " ,gdd.materialsupplier\n" + " ,gdd.productiondate\n" + " ,gdd.goodsid\n" + " ,gdd.goodscode\n" + " ,gdd.userid\n" + " ,gdd.usercode\n" + " ,gdd.weight\n" + " ,gdd.cost\n" + " ,gdd.standardgroutingnum\n" + " ,gdd.groutingnum\n" + " ,gdd.groutingdatebegin\n" + " ,gdd.groutingdateend\n" + " ,gdd.lastgroutingbatchno\n" + " ,gdd.mouldstatus\n" + " ,gdd.premouldstatus\n" + " ,gdd.repairflag\n" + " ,gdd.scrapdate\n" + " ,gdd.scrapuserid\n" + " ,gdd.scrapusercode\n" + " ,gdd.scrapreason\n" + " ,gdd.scrapresponsibility\n" + " ,gdd.lastchangehistoryid\n" + " ,gdd.groutinglinedetailid\n" + " ,gdd.REMARKS\n" + " ,gdd.ACCOUNTID\n" + " ,'1'\n" + " ,:CHECKEDUSERID\n" + " ,:CHECKEDUSERID\n" + " FROM tp_pC_Mould gdd\n" + " LEFT JOIN tp_mst_goods g\n" + " ON g.goodsid = gdd.goodsid\n" + " LEFT JOIN tp_mst_goodsType gt\n" + " ON gt.goodstypeid = g.goodstypeid\n" + "WHERE gdd.valueflag = '1'\n" + " AND gdd.accountid = :accountid\n" + " AND gdd.mouldstatus in (1,2,3) \n" + " AND (:DateBegin IS NULL OR gdd.productiondate >= :DateBegin)\n" + " AND (:DateEnd IS NULL OR gdd.productiondate <= :DateEnd)\n" + " AND (:mouldstatus IS NULL OR\n" + " instr(',' || :mouldstatus || ',', ',' || gdd.mouldstatus || ',') > 0)\n" + " AND (:GoodsIDS IS NULL OR\n" + " instr(',' || :GoodsIDS || ',', ',' || gdd.GoodsID || ',') > 0)\n" + " AND (:GoodsTypeCode IS NULL OR\n" + " instr(gt.goodstypecode, :GoodsTypeCode) = 1)"; paras = new OracleParameter[] { new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CHECKEDID",OracleDbType.Int32, checkedID,ParameterDirection.Input), new OracleParameter(":CHECKEDNO",OracleDbType.NVarchar2, checkedNo,ParameterDirection.Input), new OracleParameter(":CHECKEDUSERID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":DateBegin",OracleDbType.Date, cre.Properties["DateBegin"],ParameterDirection.Input), new OracleParameter(":DateEnd",OracleDbType.Date, cre.Properties["DateEnd"],ParameterDirection.Input), new OracleParameter(":mouldstatus",OracleDbType.NVarchar2, cre.Properties["MouldStatus"],ParameterDirection.Input), new OracleParameter(":GoodsIDS",OracleDbType.NVarchar2, cre.Properties["GoodsCodeList"],ParameterDirection.Input), new OracleParameter(":GoodsTypeCode",OracleDbType.NVarchar2, cre.Properties["GoodsTypeCode"],ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sql, paras); #endregion } oracleTrConn.Commit(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } /// /// 停用盘点单 /// /// 用户基本信息 /// public static ServiceResultEntity DisableMouldChecked(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]); int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sql = "update TP_PC_MouldChecked set valueflag='0', updateuserid=:updateuserid where CheckedID=:CheckedID and valueflag='1'"; OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":CheckedID",OracleDbType.Int32, checkedID,ParameterDirection.Input), new OracleParameter(":updateuserid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input) }; returnRows = oracleTrConn.ExecuteNonQuery(sql, Paras); if (returnRows > 0) { sre.Status = Constant.ServiceResultStatus.Success; } else { sre.Status = Constant.ServiceResultStatus.Other; } oracleTrConn.Commit(); oracleTrConn.Disconnect(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } /// /// 更新盘点单(条码盘点) /// /// 盘点单号 /// 产品条码 /// 用户基本信息 /// public static ServiceResultEntity UpdateMouldChecked(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]); string barcode = cre.Properties["Barcode"].ToString(); int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sql = null; OracleParameter[] paras = null; DataSet ds = null; sql = @"select CheckedFlag,CHECKEDUSERID from TP_PC_MouldCheckedDetail where CheckedID=:CheckedID and MouldBarCode=:MouldBarCode and Valueflag=1"; paras = new OracleParameter[] { new OracleParameter(":CheckedID",OracleDbType.Int32, checkedID,ParameterDirection.Input), new OracleParameter(":MouldBarCode",OracleDbType.NVarchar2, barcode,ParameterDirection.Input) }; ds = oracleTrConn.GetSqlResultToDs(sql, paras); // 不在盘点单中 if (ds != null && ds.Tables[0].Rows.Count == 0) { sql = @"select CheckedNo from tp_pC_Mouldchecked where CheckedID=:CheckedID"; paras = new OracleParameter[] { new OracleParameter(":CheckedID",OracleDbType.Int32, checkedID,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sql, paras); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = -1; sre.Message = "盘点单不存在"; return sre; } string checkedNo = ds.Tables[0].Rows[0]["CheckedNo"].ToString(); // 条码是否在模具档案中 sql = "SELECT gdd.MouldID\n" + " FROM tp_pC_Mould gdd\n" + " WHERE gdd.Mouldbarcode = :MouldBarCode"; paras = new OracleParameter[] { new OracleParameter(":MouldBarCode",OracleDbType.NVarchar2, barcode,ParameterDirection.Input) }; DataTable dt = oracleTrConn.GetSqlResultToDt(sql, paras); if (dt == null || dt.Rows.Count == 0) { // 无效条码 sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = -1; sre.Message = "条码【" + barcode + "】为无效条码"; return sre; } sql = "INSERT INTO TP_PC_MouldCHECKEDDETAIL\n" + " (CHECKEDID\n" + " ,CHECKEDNO\n" + " ,MouldID\n" + " ,MouldCODE\n" + " ,MouldBARCODE\n" + " ,mouldtype\n" + " ,materialsupplier\n" + " ,productiondate\n" + " ,goodsid\n" + " ,goodscode\n" + " ,userid\n" + " ,usercode\n" + " ,weight\n" + " ,cost\n" + " ,standardgroutingnum\n" + " ,groutingnum\n" + " ,groutingdatebegin\n" + " ,groutingdateend\n" + " ,lastgroutingbatchno\n" + " ,mouldstatus\n" + " ,premouldstatus\n" + " ,repairflag\n" + " ,scrapdate\n" + " ,scrapuserid\n" + " ,scrapusercode\n" + " ,scrapreason\n" + " ,scrapresponsibility\n" + " ,lastchangehistoryid\n" + " ,groutinglinedetailid\n" + " ,REMARKS\n" + " ,CHECKEDFLAG\n" + " ,CHECKEDDATE\n" + " ,CHECKEDUSERID\n" + " ,ACCOUNTID\n" + " ,VALUEFLAG\n" + " ,CREATEUSERID\n" + " ,UPDATEUSERID)\n" + " SELECT :CHECKEDID\n" + " ,:CHECKEDNO\n" + " ,gdd.MouldID\n" + " ,gdd.MouldCODE\n" + " ,gdd.MouldBARCODE\n" + " ,gdd.mouldtype\n" + " ,gdd.materialsupplier\n" + " ,gdd.productiondate\n" + " ,gdd.goodsid\n" + " ,gdd.goodscode\n" + " ,gdd.userid\n" + " ,gdd.usercode\n" + " ,gdd.weight\n" + " ,gdd.cost\n" + " ,gdd.standardgroutingnum\n" + " ,gdd.groutingnum\n" + " ,gdd.groutingdatebegin\n" + " ,gdd.groutingdateend\n" + " ,gdd.lastgroutingbatchno\n" + " ,gdd.mouldstatus\n" + " ,gdd.premouldstatus\n" + " ,gdd.repairflag\n" + " ,gdd.scrapdate\n" + " ,gdd.scrapuserid\n" + " ,gdd.scrapusercode\n" + " ,gdd.scrapreason\n" + " ,gdd.scrapresponsibility\n" + " ,gdd.lastchangehistoryid\n" + " ,gdd.groutinglinedetailid\n" + " ,REMARKS\n" + " ,'2'\n" + " ,SYSDATE\n" + " ,:CHECKEDUSERID\n" + " ,ACCOUNTID\n" + " ,'1'\n" + " ,:CHECKEDUSERID\n" + " ,:CHECKEDUSERID\n" + " FROM tp_pC_Mould gdd\n" + " WHERE MouldID = :MouldID"; paras = new OracleParameter[] { new OracleParameter(":CHECKEDID",OracleDbType.Int32, checkedID,ParameterDirection.Input), new OracleParameter(":CHECKEDNO",OracleDbType.NVarchar2, checkedNo,ParameterDirection.Input), new OracleParameter(":CHECKEDUSERID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":MouldID",OracleDbType.Int32, dt.Rows[0]["MouldID"],ParameterDirection.Input), }; returnRows = oracleTrConn.ExecuteNonQuery(sql, paras); //returnRows = 10; } else { string checkedFlag = ds.Tables[0].Rows[0]["CheckedFlag"].ToString(); if (checkedFlag == "1") { sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = -1; sre.Message = "条码【" + barcode + "】已被盘点"; return sre; } else if (checkedFlag == "2") { sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = -1; sre.Message = "条码【" + barcode + "】已被盘盈"; return sre; } sql = "update TP_PC_MouldCheckedDetail set CheckedFlag=:CheckedFlag,CheckedDate=sysdate,CheckedUserID=:CheckedUserID,UpdateUserID=:UpdateUserID where CheckedID=:CheckedID and MouldBarCode=:BarCode and CheckedFlag=:whereCheckedFlag"; paras = new OracleParameter[] { new OracleParameter(":CheckedUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":CheckedFlag",OracleDbType.Int32, (int)Constant.InCheckedFlag.InCheckeded,ParameterDirection.Input), new OracleParameter(":CheckedID",OracleDbType.Int32, checkedID,ParameterDirection.Input), new OracleParameter(":BarCode",OracleDbType.NVarchar2, barcode,ParameterDirection.Input), new OracleParameter(":whereCheckedFlag",OracleDbType.Int32, (int)Constant.InCheckedFlag.InCheckedNo,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; returnRows = oracleTrConn.ExecuteNonQuery(sql, paras); //returnRows = 1; } if (returnRows == 0) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } else { oracleTrConn.Commit(); oracleTrConn.Disconnect(); } } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } /// /// 报损未盘点 /// /// 用户基本信息 /// public static ServiceResultEntity SaveClearMouldChecked(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int checkedID = Convert.ToInt32(cre.Properties["CheckedID"]); string barcodes = cre.Properties["Barcodes"].ToString(); int barcodeCount = Convert.ToInt32(cre.Properties["BarcodeCount"]); int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { // 待产 在库 可以报损 string sqlString = "SELECT m.mouldid,m.mouldcode, m.mouldbarcode, m.mouldstatus,m.GoodsID,m.GoodsCode,m.GroutingNum\n" + " FROM TP_PC_Mould m\n" + " WHERE m.valueflag = '1'\n" + " AND m.accountid = :accountid\n" + " AND m.mouldstatus IN (1, 3)\n" + " AND instr(:barcodes, m.mouldbarcode) > 0\n" + " for update"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":barcodes",OracleDbType.NVarchar2, barcodes,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; DataTable dt = oracleTrConn.GetSqlResultToDt(sqlString, paras); if (dt == null || dt.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "【在产】或【已报废】的模具不能直接报废"; return sre; } if (dt.Rows.Count != barcodeCount) { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "【在产】或【已报废】的模具不能直接报废"; } string sqlStringInsertHis = "INSERT INTO TP_PC_MouldChangeHistory\n" + " (ChangeID\n" + " ,MouldID\n" + " ,MouldCode\n" + " ,MouldBarcode\n" + " ,GoodsID\n" + " ,GoodsCode\n" + " ,OperationType\n" + " ,MouldStatus\n" + " ,MouldStatusAfter\n" + " ,GroutingNum\n" + " ,ChangeFlag\n" + " ,ScrapResponsibility\n" + " ,AccountID\n" + " ,CreateUserID)\n" + "VALUES\n" + " (:ChangeID\n" + " ,:MouldID\n" + " ,:MouldCode\n" + " ,:MouldBarcode\n" + " ,:GoodsID\n" + " ,:GoodsCode\n" + " ,:OperationType\n" + " ,:MouldStatus\n" + " ,4\n" + " ,:GroutingNum\n" + " ,decode(:OperationType, 2, '2','0')\n" + " ,'盘亏报损'\n" + " ,:AccountID\n" + " ,:CreateUserID)"; string sqlStringUpdateMould = "UPDATE TP_PC_Mould m\n" + " SET m.premouldstatus = m.mouldstatus\n" + " ,m.mouldstatus = 4\n" + " ,m.scrapdate = SYSDATE\n" + " ,m.scrapuserid = :scrapuserid\n" + " ,m.scrapusercode = :scrapusercode\n" + " ,m.ScrapResponsibility = '盘亏报损'\n" + " ,m.LastChangeHistoryID = :LastChangeHistoryID\n" + " ,m.updateuserid = :updateuserid\n" + " WHERE m.mouldid = :mouldid"; sqlString = "UPDATE tp_pc_Mouldcheckeddetail gdd\n" + " SET gdd.checkedflag = '3'\n" + " ,gdd.CheckedDate = SYSDATE\n" + " ,gdd.CheckedUserID = :UserID\n" + " ,gdd.UpdateUserID = :UserID\n" + " WHERE gdd.checkedflag = '0'\n" + " AND gdd.CheckedID = :CheckedID" + " AND gdd.mouldid = :MouldID"; foreach (DataRow item in dt.Rows) { int mouldHistoryID = Convert.ToInt32(oracleTrConn.GetSqlResultToObj("select seq_pc_mouldhistory_id.nextval from dual ")); // 插入变更履历 paras = new OracleParameter[] { new OracleParameter(":ChangeID",OracleDbType.Int32, mouldHistoryID,ParameterDirection.Input), new OracleParameter(":MouldID",OracleDbType.Int32, item["MouldID"],ParameterDirection.Input), new OracleParameter(":MouldCode",OracleDbType.NVarchar2, item["MouldCode"],ParameterDirection.Input), new OracleParameter(":MouldBarcode",OracleDbType.NVarchar2, item["MouldBarcode"],ParameterDirection.Input), new OracleParameter(":GoodsID",OracleDbType.Int32, item["GoodsID"],ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.NVarchar2, item["GoodsCode"],ParameterDirection.Input), new OracleParameter(":MouldStatus",OracleDbType.Int32, item["MouldStatus"],ParameterDirection.Input), new OracleParameter(":GroutingNum",OracleDbType.Int32, item["GroutingNum"],ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":OperationType",OracleDbType.Int32, (item["MouldStatus"].ToString() == "1"? (int)Constant.MouldOperationType.InvToScrap:(int)Constant.MouldOperationType.OutToScrap) ,ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlStringInsertHis, paras); // 修改模具状态 paras = new OracleParameter[] { new OracleParameter(":MouldID",OracleDbType.Int32, item["MouldID"],ParameterDirection.Input), new OracleParameter(":scrapuserid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":LastChangeHistoryID",OracleDbType.Int32, mouldHistoryID,ParameterDirection.Input), new OracleParameter(":updateuserid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":scrapusercode",OracleDbType.NVarchar2, sUserInfo.UserCode,ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlStringUpdateMould, paras); // 清除盘点明细 paras = new OracleParameter[] { new OracleParameter(":MouldID",OracleDbType.Int32, item["MouldID"],ParameterDirection.Input), new OracleParameter(":UserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":CheckedID",OracleDbType.Int32, checkedID,ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlString, paras); } oracleTrConn.Commit(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } /// /// 获取盘点单明细 /// /// 工序ID /// DataSet public static DataSet GetUpdateMouldCheckedInfo(int checkedID, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"SELECT to_char(checked.UserCheckedCount) || '/' || to_char(checked.CheckedCount) CheckedCount ,to_char(checked.UserOverageCount) || '/' || to_char(checked.OverageCount) OverageCount ,to_char(checked.UserCheckedCount + checked.UserOverageCount) || '/' || to_char(checked.CheckedCount + checked.OverageCount) || '/' || to_char(TCount) TCount FROM (SELECT icd.checkedid , SUM(CASE WHEN icd.checkeduserid = :userid AND icd.CheckedFlag = '1' THEN 1 ELSE 0 END) UserCheckedCount , SUM(CASE WHEN icd.checkeduserid = :userid AND icd.CheckedFlag = '2' THEN 1 ELSE 0 END) UserOverageCount , SUM(CASE WHEN icd.CheckedFlag = '1' THEN 1 ELSE 0 END) CheckedCount , SUM(CASE WHEN icd.CheckedFlag = '2' THEN 1 ELSE 0 END) OverageCount ,COUNT(icd.checkedid) TCount FROM tp_pC_Mouldcheckeddetail icd WHERE icd.checkedid = :checkedid AND icd.valueflag = '1' -- xuwei add 2022-02-26 group by icd.checkedid -- xuwei end ) checked"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":checkedid",OracleDbType.Int32, checkedID, ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); ds.Tables[0].TableName = "Table1"; sqlString = @"SELECT icd.goodscode, COUNT(icd.goodscode) goodscount FROM tp_pC_Mouldcheckeddetail icd WHERE icd.checkedid = :checkedid AND icd.checkeduserid = :userid AND icd.CheckedFlag = '1' AND icd.valueflag = '1' GROUP BY icd.goodscode"; DataTable dt = con.GetSqlResultToDt(sqlString, paras); dt.TableName = "Table2"; ds.Tables.Add(dt); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 模具库存结转 /// /// 获取模具库存结转一览查询数据 /// /// /// public static ServiceResultEntity GetFPC1201QueryData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT mc.yyyymm, mc.begindate, mc.enddate, mc.remarks\n" + " FROM TP_PC_MouldCarryOver mc\n" + " WHERE mc.accountid = :accountid\n" + " AND mc.yyyymm >= :DateBegin\n" + " AND mc.yyyymm <= :DateEnd\n" + " ORDER BY mc.yyyymm desc"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":DateBegin", OracleDbType.Char, cre.Properties["DateBegin"], ParameterDirection.Input), new OracleParameter(":DateEnd", OracleDbType.Char, cre.Properties["DateEnd"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); if (sre.Data == null || sre.Data.Tables.Count == 0 || sre.Data.Tables[0].Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sqlString = "SELECT mcd.yyyymm\n" + " ,gt2.goodstypecode goodstypecode2\n" + " ,gt2.goodstypename goodstypename2\n" + " ,mcd.goodscode\n" + " ,mcd.mouldnum\n" + " FROM TP_PC_Mouldcarryoverdetail mcd\n" + " INNER JOIN tp_mst_goods g\n" + " ON g.goodsid = mcd.goodsid\n" + " INNER JOIN tp_mst_goodstype gt\n" + " ON gt.goodstypeid = g.goodstypeid\n" + " INNER JOIN tp_mst_goodstype gt2\n" + " ON gt2.accountid = gt.accountid\n" + " AND gt2.goodstypecode = substr(gt.goodstypecode, 1, 6)\n" + " WHERE mcd.accountid = :accountid\n" + " AND mcd.yyyymm = :YYYYMM\n" + " AND mcd.mouldstatus = 1 -- 在库\n" + " ORDER BY gt2.goodstypecode, mcd.goodscode"; parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":YYYYMM", OracleDbType.Char, sre.Data.Tables[0].Rows[0]["YYYYMM"], ParameterDirection.Input), }; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters)); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } public static ServiceResultEntity SAPQuery(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { #region 获取东科单据号 int ZDKNO = Convert.ToInt32(oracleTrConn.GetSqlResultToObj("select SEQ_MOULDCHANGELOGID.NEXTVAL from dual ")); #endregion #region 普通参数 DateTime createTime = DateTime.Now; string ceshi1 = createTime.ToString("yyyyMMdd"); //消息类型: S 成功,E 错误 string ZTYPE; //消息文本 string ZMSG; #endregion #region 调用SAP接口方法 string upsql = string.Empty; DataTable dtResult = new DataTable(); string sqlString = @"SELECT * FROM TP_CESHI "; DataTable newTable = oracleTrConn.GetSqlResultToDt(sqlString); for (int j = 0; j < newTable.Rows.Count; j++) { int count = Convert.ToInt32(newTable.Rows[j]["mouldcount"]); dtResult = ZMMFM045("20231108", ZDKNO + "", newTable.Rows[j]["cj"].ToString(), newTable.Rows[j]["MATNR"].ToString(),count, out ZTYPE, out ZMSG); if (dtResult != null && dtResult.Rows.Count > 0) { if ("S".Equals(ZTYPE)) { #region 插入SAP模具变更传输日志 string insql = @" INSERT INTO TP_SAP_MOULDCHANGELOG ( BLDAT , BUDAT , WERKS , LGORT_FM , LGORT_TO , MATNR , BWART , MEINS , MENGE , MBLNR , MJAHR , MSGTY , MSGTX) VALUES ( SYSDATE , SYSDATE , '5000' , '3001' , :LGORT_TO , :MATNR , '311' , 'PCS' , :MENGE , :MBLNR , :MJAHR , :MSGTY , :MSGTX)"; OracleParameter[] inparas = new OracleParameter[]{ //new OracleParameter(":LGORT_TO", OracleDbType.NVarchar2,ZDKNO, ParameterDirection.Input), new OracleParameter(":LGORT_TO", OracleDbType.NVarchar2,newTable.Rows[j]["cj"], ParameterDirection.Input), new OracleParameter(":MATNR", OracleDbType.NVarchar2,newTable.Rows[j]["MATNR"], ParameterDirection.Input), new OracleParameter(":MENGE", OracleDbType.Int32,count, ParameterDirection.Input), new OracleParameter(":MBLNR", OracleDbType.NVarchar2, dtResult.Rows[0]["MBLNR"].ToString(), ParameterDirection.Input), new OracleParameter(":MJAHR", OracleDbType.NVarchar2,dtResult.Rows[0]["MJAHR"].ToString(), ParameterDirection.Input), new OracleParameter(":MSGTY", OracleDbType.NVarchar2,dtResult.Rows[0]["MSGTY"].ToString(), ParameterDirection.Input), new OracleParameter(":MSGTX", OracleDbType.NVarchar2,dtResult.Rows[0]["MSGTX"].ToString(), ParameterDirection.Input) }; oracleTrConn.ExecuteNonQuery(insql, inparas); #endregion } else if ("E".Equals(ZTYPE)) { #region 插入SAP模具变更传输日志 string insql = @" INSERT INTO TP_SAP_MOULDCHANGELOG ( BLDAT , BUDAT , WERKS , LGORT_FM , LGORT_TO , MATNR , BWART , MEINS , MENGE , MBLNR , MJAHR , MSGTY , MSGTX) VALUES ( SYSDATE , SYSDATE , '5000' , '3001' , :LGORT_TO , :MATNR , '311' , 'PCS' , :MENGE , :MBLNR , :MJAHR , :MSGTY , :MSGTX)"; OracleParameter[] inparas = new OracleParameter[]{ //new OracleParameter(":LGORT_TO", OracleDbType.NVarchar2,ZDKNO, ParameterDirection.Input), new OracleParameter(":LGORT_TO", OracleDbType.NVarchar2,newTable.Rows[j]["cj"], ParameterDirection.Input), new OracleParameter(":MATNR", OracleDbType.NVarchar2,newTable.Rows[j]["MATNR"], ParameterDirection.Input), new OracleParameter(":MENGE", OracleDbType.Int32,count, ParameterDirection.Input), new OracleParameter(":MBLNR", OracleDbType.NVarchar2, dtResult.Rows[0]["MBLNR"].ToString(), ParameterDirection.Input), new OracleParameter(":MJAHR", OracleDbType.NVarchar2,dtResult.Rows[0]["MJAHR"].ToString(), ParameterDirection.Input), new OracleParameter(":MSGTY", OracleDbType.NVarchar2,dtResult.Rows[0]["MSGTY"].ToString(), ParameterDirection.Input), new OracleParameter(":MSGTX", OracleDbType.NVarchar2,dtResult.Rows[0]["MSGTX"].ToString(), ParameterDirection.Input) }; oracleTrConn.ExecuteNonQuery(insql, inparas); #endregion if (dtResult != null && dtResult.Rows.Count > 0) { sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = -10001; sre.Message = dtResult.Rows[0]["MSGTX"].ToString(); oracleTrConn.Rollback(); oracleTrConn.Disconnect(); return sre; } else { sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = -10002; sre.Message = dtResult.Rows[0]["MSGTX"].ToString(); oracleTrConn.Rollback(); oracleTrConn.Disconnect(); return sre; } } } } #endregion //提交事务 oracleTrConn.Commit(); oracleTrConn.Disconnect(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } return sre; } /// /// 东科与SAP系统之摸具状态变更（上模） /// /// /// /// /// /// /// public static DataTable ZMMFM045(string BLDAT, string ZDKNO, string cj, string MATNR, int dtcount, out string ZTYPE, out string ZMSG) { #region 接口定义 RfcConfigParameters rfcPara = new RfcConfigParameters(); rfcPara.Add(RfcConfigParameters.AppServerHost, appServerHost); rfcPara.Add(RfcConfigParameters.SystemNumber, systemNumber); rfcPara.Add(RfcConfigParameters.User, user); rfcPara.Add(RfcConfigParameters.Password, password); rfcPara.Add(RfcConfigParameters.Client, client); rfcPara.Add(RfcConfigParameters.Name, "CON"); rfcPara.Add(RfcConfigParameters.Language, "ZH"); rfcPara.Add(RfcConfigParameters.PoolSize, "5"); rfcPara.Add(RfcConfigParameters.ConnectionIdleTimeout, "60"); RfcDestination rfcDest = RfcDestinationManager.GetDestination(rfcPara); RfcRepository rfcRep = rfcDest.Repository; #endregion //接口API IRfcFunction rfcApi = rfcRep.CreateFunction("ZMMFM045"); //输入参数 IRfcTable imTable = rfcApi.GetTable("IT_ITEM"); #region 传参 imTable.Append(); imTable.SetValue("BLDAT", BLDAT); //凭证日期 imTable.SetValue("BUDAT", BLDAT); //过帐日期 imTable.SetValue("WERKS", "5000"); //工厂（默认：5000） imTable.SetValue("LGORT_FM", "3001"); //发出库位：3001 imTable.SetValue("LGORT_TO", cj); //接收库位：一车间2110、二车间2210、三车间2310 imTable.SetValue("MATNR", MATNR); //物料编号：模具对应产品的物料编号 imTable.SetValue("BWART", "311"); //移动类型(库存管理)：311 imTable.SetValue("MEINS", "PCS"); //基本计量单位：PCS imTable.SetValue("MENGE", dtcount); //数量 imTable.SetValue("ZDKNO", ZDKNO); //东科单据号 （LOGOID） #endregion //调用接口 rfcApi.Invoke(rfcDest); //获取输出 ZTYPE = rfcApi.GetValue("ZTYPE").ToString(); ZMSG = rfcApi.GetValue("ZMSG").ToString(); IRfcTable table = rfcApi.GetTable("ET_RETURN"); DataTable dt = GetDataTableFromRFCTable(table); rfcDest = null; rfcRep = null; return dt; } /// /// 获取模具库存结转明细查询数据 /// /// /// public static ServiceResultEntity GetFPC1201QueryDetailData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT mcd.yyyymm\n" + " ,gt2.goodstypecode goodstypecode2\n" + " ,gt2.goodstypename goodstypename2\n" + " ,mcd.goodscode\n" + " ,mcd.mouldnum\n" + " FROM TP_PC_Mouldcarryoverdetail mcd\n" + " INNER JOIN tp_mst_goods g\n" + " ON g.goodsid = mcd.goodsid\n" + " INNER JOIN tp_mst_goodstype gt\n" + " ON gt.goodstypeid = g.goodstypeid\n" + " INNER JOIN tp_mst_goodstype gt2\n" + " ON gt2.accountid = gt.accountid\n" + " AND gt2.goodstypecode = substr(gt.goodstypecode, 1, 6)\n" + " WHERE mcd.accountid = :accountid\n" + " AND mcd.yyyymm = :YYYYMM\n" + " AND mcd.mouldstatus = 1 -- 在库\n" + " ORDER BY gt2.goodstypecode, mcd.goodscode"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":YYYYMM", OracleDbType.Char, cre.Properties["YYYYMM"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #region 模具库存自动结转设置 /// /// 获取模具库存自动结转设置 /// /// /// /// public static ServiceResultEntity GetFPC1204Data(ClientRequestEntity cre, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { ServiceResultEntity sre = new ServiceResultEntity(); string sqlString = "SELECT JOB_NAME, REPEAT_INTERVAL, LAST_START_DATE, NEXT_RUN_DATE, ENABLED\n" + " FROM USER_SCHEDULER_JOBS\n" + " WHERE JOB_NAME = 'JOB_AUTOMOULDCARRYOVER'"; oracleConn.Open(); DataSet data = oracleConn.GetSqlResultToDs(sqlString); sre.Data = data; oracleConn.Close(); return sre; } catch (Exception ex) { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } throw ex; } } /// /// 保存模具库存自动结转设置 /// /// /// /// public static ServiceResultEntity SetFPC1204Data(ClientRequestEntity cre, SUserInfo sUserInfo) { IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { ServiceResultEntity sre = new ServiceResultEntity(); // 检验参数的有效性 if (cre == null || cre.Request == null || cre.Request.ToString() == "") { sre.Status = Constant.ServiceResultStatus.NoModifyData; return sre; } oracleTrConn.Connect(); OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter("name","JOB_AUTOMOULDCARRYOVER"), new OracleParameter("attribute", "repeat_interval"), new OracleParameter("value", cre.Request.ToString()) }; oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.set_attribute", oracleParameter); oracleParameter = new OracleParameter[] { new OracleParameter("name","JOB_AUTOMOULDCARRYOVER") }; if (cre.Properties["Enabled"].ToString() == "1") { oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.enable", oracleParameter); } else { oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.disable", oracleParameter); } //if (cre.Properties["Run"].ToString() == "1") //{ // // oracleParameter = new OracleParameter[] // // { // // new OracleParameter("job_name","JOB_AUTOMOULDCARRYOVER"), // // new OracleParameter("use_current_session", OracleDbType., 0) // // }; // //oracleTrConn.ExecStoredProcedure("sys.dbms_scheduler.run_job", oracleParameter); // string sqlString = "begin\n" + // "sys.dbms_scheduler.run_job('JOB_AUTOMOULDCARRYOVER', false);\n" + // "end;"; // oracleTrConn.ExecuteNonQuery(sqlString); //} oracleTrConn.Commit(); oracleTrConn.Disconnect(); return sre; } catch (Exception ex) { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } } #endregion /// /// 获取模具库存结转期初 /// /// /// /// public static ServiceResultEntity GetFPC1202Data(ClientRequestEntity cre, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { ServiceResultEntity sre = new ServiceResultEntity(); string sqlString = "SELECT --nvl(MAX(mc.yyyymm), ' ') LastYYYYMM\n" + " MAX(mc.yyyymm) LastYYYYMM\n" + " ,to_char(SYSDATE, 'yyyymm') YYYYMM\n" + " ,trunc(SYSDATE, 'mm') CurMonth\n" + " FROM tp_pc_mouldcarryover mc\n" + " WHERE mc.accountid = :accountid"; OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), }; sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); oracleConn.Close(); return sre; } catch (Exception ex) { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } throw ex; } } /// /// 模具库存结转 /// /// /// /// public static ServiceResultEntity SetFPC1202Data(ClientRequestEntity cre, SUserInfo sUserInfo) { IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { ServiceResultEntity sre = new ServiceResultEntity(); oracleTrConn.Connect(); OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter("in_CarryoverMonth", OracleDbType.Date, cre.Properties["CarryoverMonth"], ParameterDirection.Input), new OracleParameter("in_AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter("in_UserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input), new OracleParameter("in_Remarks", OracleDbType.NVarchar2, cre.Properties["Remarks"], ParameterDirection.Input), }; oracleTrConn.ExecStoredProcedure("pro_job_pc_mouldcarryover", oracleParameter); oracleTrConn.Commit(); oracleTrConn.Disconnect(); return sre; } catch (Exception ex) { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } } /// /// 模具库存反结转 /// /// /// /// public static ServiceResultEntity SetFPC1203Data(ClientRequestEntity cre, SUserInfo sUserInfo) { IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { ServiceResultEntity sre = new ServiceResultEntity(); oracleTrConn.Connect(); OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":yyyymm", OracleDbType.Char, cre.Properties["YYYYMM"], ParameterDirection.Input), }; string sqlString = "DELETE FROM tp_pc_mouldcarryover mc\n" + " WHERE mc.accountid = :accountid\n" + " AND mc.yyyymm >= :yyyymm"; oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); sqlString = "DELETE FROM tp_pc_mouldcarryoverdetail mcd\n" + " WHERE mcd.accountid = :accountid\n" + " AND mcd.yyyymm >= :yyyymm"; oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); sqlString = "UPDATE tp_pc_mouldchangehistory mch\n" + " SET mch.yyyymm = NULL\n" + " WHERE mch.accountid = :accountid\n" + " AND mch.yyyymm >= :yyyymm"; oracleTrConn.ExecuteNonQuery(sqlString, oracleParameter); oracleTrConn.Commit(); oracleTrConn.Disconnect(); return sre; } catch (Exception ex) { if (oracleTrConn.ConnState == ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } } #endregion #region 成检材料报损配置 /// /// 成检材料报损配置工序查询 /// /// /// public static ServiceResultEntity GetFinishedCheckProcedure(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT t.procedureid\n" + " ,t.procedurename\n" + " FROM tp_pc_procedure t\n" + " WHERE t.valueflag = '1'\n" + " AND t.AccountID = :accountid\n" + " AND t.modeltype = -5" + " AND (:PROCEDURENAME is null or :PROCEDURENAME = '' or instr(t.procedurename, :PROCEDURENAME) > 0 )\n"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":PROCEDURENAME", OracleDbType.Varchar2, cre.Properties["PROCEDURENAME"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 成检材料报损配置查询 /// /// /// public static ServiceResultEntity GetIdnrktype(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT (SELECT 1\n" + " FROM tp_pc_procedureidnrktype pit\n" + " WHERE pit.idnrktype = d.dictionaryvalue\n" + " AND pit.procedureid = :PROCEDUREID) AS check1\n" + " ,d.dictionaryvalue as idnrktype \n" + " ,(SELECT 1\n" + " FROM tp_pc_procedureidnrktype pit\n" + " WHERE pit.idnrktype = d.dictionaryvalue\n" + " AND pit.procedureid = :PROCEDUREID AND pit.SCANFLAG = 1) AS SCANFLAG\n" + " FROM tp_mst_datadictionary d\n" + " WHERE d.dictionarytype = 'TPC010'\n" + " AND d.valueflag = '1'\n" + " AND d.accountid = :accountid" + " AND (:PROCEDURENAME is null or :PROCEDURENAME = '' or instr(d.dictionaryvalue, :PROCEDURENAME) > 0 )\n"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":PROCEDUREID", OracleDbType.Int32, cre.Properties["PROCEDUREID"], ParameterDirection.Input), new OracleParameter(":PROCEDURENAME", OracleDbType.Varchar2, cre.Properties["PROCEDURENAME"]+"", ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); sre.Data.Tables[0].TableName = "idnrk"; sqlString = @" SELECT w.workstationid,w.workstationname,wc.barcodeip,wc.idnrkip,wc.scanip,wc.plcip,wc.servicePort,wc.db FROM tp_mst_workstation w left join tp_Pc_procedure p on p.procedureid = w.procedureid left join TP_PM_WORKSTATIONIPCONFIG wc on wc.workstationname = w.workstationname WHERE w.procedureid = :PROCEDUREID order by w.workstationid "; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters)); sre.Data.Tables[1].TableName = "ip"; return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 已选成检材料类型查询 /// /// /// public static ServiceResultEntity GetProcedureIdnrktype(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "SELECT pi.procedureid\n" + " ,pi.idnrktype\n" + " ,pi.SCANFLAG\n" + " ,1 as AlreadyCheck\n" + " FROM tp_pc_procedureidnrktype pi\n" + " WHERE pi.procedureid = :PROCEDUREID"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":PROCEDUREID", OracleDbType.Int32, cre.Properties["PROCEDUREID"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 保存成检材料类型 /// /// /// public static ServiceResultEntity SavePackingBOMData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int procedureId = Convert.ToInt32(cre.Properties["ProcedureId"]); DataTable procedureTable = cre.Data.Tables[0]; int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = null; if (procedureTable != null && procedureTable.Rows.Count > 0) { //先删除后插入 sqlString = "delete from tp_pc_procedureidnrktype where procedureid=" + procedureId; oracleTrConn.ExecuteNonQuery(sqlString); foreach (DataRow item in procedureTable.Rows) { sqlString = @"insert into tp_pc_procedureidnrktype ( procedureid, idnrktype, SCANFLAG ) values ( :procedureid, :idnrktype, :scanflag )"; OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":procedureid",OracleDbType.Int32,procedureId,ParameterDirection.Input), new OracleParameter(":idnrktype",OracleDbType.Varchar2, item["idnrktype"] + "",ParameterDirection.Input), new OracleParameter(":scanflag",OracleDbType.Varchar2, item["SCANFLAG"] + "",ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlString, Paras); } } oracleTrConn.Commit(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } /// /// 保存成检材料类型 /// /// /// public static ServiceResultEntity SaveWorkIP(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int procedureId = Convert.ToInt32(cre.Properties["ProcedureId"]); DataTable procedureTable = cre.Data.Tables[0]; int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = null; if (procedureTable != null && procedureTable.Rows.Count > 0) { //先删除后插入 sqlString = "delete from TP_PM_WORKSTATIONIPCONFIG where procedureid=" + procedureId; oracleTrConn.ExecuteNonQuery(sqlString); foreach (DataRow item in procedureTable.Rows) { sqlString = @"insert into TP_PM_WORKSTATIONIPCONFIG ( PROCEDUREID, WORKSTATIONID, WORKSTATIONNAME, BARCODEIP, IDNRKIP, SCANIP, PLCIP, SERVICEPORT, DB, CREATEUSERID, UPDATEUSERID ) values ( :PROCEDUREID, :WORKSTATIONID, :WORKSTATIONNAME, :BARCODEIP, :IDNRKIP, :SCANIP, :PLCIP, :SERVICEPORT, :DB, :CREATEUSERID, :UPDATEUSERID )"; OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":PROCEDUREID",OracleDbType.Int32,procedureId,ParameterDirection.Input), new OracleParameter(":WORKSTATIONID",OracleDbType.Varchar2, item["WORKSTATIONID"] + "",ParameterDirection.Input), new OracleParameter(":WORKSTATIONNAME",OracleDbType.Varchar2, item["WORKSTATIONNAME"] + "",ParameterDirection.Input), new OracleParameter(":BARCODEIP",OracleDbType.Varchar2, item["BARCODEIP"] + "",ParameterDirection.Input), new OracleParameter(":IDNRKIP",OracleDbType.Varchar2, item["IDNRKIP"] + "",ParameterDirection.Input), new OracleParameter(":SCANIP",OracleDbType.Varchar2, item["SCANIP"] + "",ParameterDirection.Input), new OracleParameter(":PLCIP",OracleDbType.Varchar2, item["PLCIP"] + "",ParameterDirection.Input), new OracleParameter(":SERVICEPORT",OracleDbType.Varchar2, item["SERVICEPORT"] + "",ParameterDirection.Input), new OracleParameter(":DB",OracleDbType.Varchar2, item["DB"] + "",ParameterDirection.Input), new OracleParameter(":CREATEUSERID",OracleDbType.Int32,sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UPDATEUSERID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlString, Paras); } } oracleTrConn.Commit(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } #endregion #region 线边仓组件配置 /// /// 线边仓查询 /// /// /// /// public static ServiceResultEntity GetLGORT(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"SELECT dictionaryvalue,dictionaryID FROM TP_MST_DATADICTIONARY MDD WHERE DICTIONARYTYPE = :DICTIONARYTYPE AND ACCOUNTID =:accountid AND VALUEFLAG =1 AND (:dictionaryvalue IS NULL OR :dictionaryvalue = '' OR INSTR(MDD.DICTIONARYVALUE,:dictionaryvalue)>0)"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":DICTIONARYTYPE", OracleDbType.NVarchar2, cre.Properties["DICTIONARYTYPE"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":dictionaryvalue", OracleDbType.NVarchar2, cre.Properties["dictionaryvalue"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 组件类型配置 /// /// /// /// public static ServiceResultEntity GetIDNRKTYPE(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"SELECT mdd.dictionaryvalue ModuleName ,CASE WHEN pl.idnrktype IS NULL THEN 0 ELSE 1 END check1 FROM tp_mst_datadictionary mdd LEFT JOIN (SELECT tpl.idnrktype FROM tp_pm_lgortidnrktyperel tpl WHERE tpl.lgort = :lgort) pl ON mdd.dictionaryvalue = pl.idnrktype WHERE mdd.valueflag = 1 AND mdd.dictionarytype = 'TPC010' AND mdd.accountid = :accountid AND (:ModuleName is null or :ModuleName = '' or instr (mdd.dictionaryvalue,:ModuleName)>0)"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":LGORT", OracleDbType.NVarchar2, cre.Properties["LGORT"], ParameterDirection.Input), new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":ModuleName", OracleDbType.NVarchar2,cre.Properties["ModuleName"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 保存线边仓与组件类别关联关系 /// /// /// /// public static ServiceResultEntity SaveLgortIdnrktype(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); string LGORT = cre.Properties["LGORT"].ToString(); DataTable LGORTTable = cre.Data.Tables[0]; int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = "delete from tp_pm_lgortidnrktyperel where LGORT =" + LGORT; oracleTrConn.ExecuteNonQuery(sqlString); if (LGORTTable != null && LGORTTable.Rows.Count > 0) { foreach (DataRow item in LGORTTable.Rows) { sqlString = @"INSERT INTO tp_pm_lgortidnrktyperel (lgort ,idnrktype) VALUES (:lgort ,:idnrktype)"; OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":lgort",OracleDbType.NVarchar2,LGORT,ParameterDirection.Input), new OracleParameter(":idnrktype",OracleDbType.Varchar2, item["idnrktype"] + "",ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlString, Paras); } } oracleTrConn.Commit(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } #endregion #region 产品装配明细表 /// /// 产品装配明细表查询 /// /// /// public static ServiceResultEntity GetBarCodeIdnrkRel(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = @" SELECT T.RELATIONID, T.BARCODE, T.MATNR, T.IDNRK, T.MEINS, T.MENGE, T.IDNRKNAME, case when instr(T.IDNRKONLYCODE,',') > 0 then substr(T.IDNRKONLYCODE,0,instr(T.IDNRKONLYCODE,',')-1) else T.IDNRKONLYCODE end as IDNRKONLYCODE, T.CHARG, T.LGORT, P.PROCEDURENAME, T.CREATETIME, CU.USERNAME AS CREATEUSERNAME FROM TP_PM_BARCODEIDNRKREL T INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.BARCODE = T.BARCODE AND (GDD.ONELEVELTYPEID IS NULL OR GDD.ONELEVELTYPEID <> 7) AND (GDD.TWOLEVELTYPEID IS NULL OR GDD.TWOLEVELTYPEID <> 7) LEFT JOIN TP_PC_PROCEDURE P ON T.PROCEDUREID = P.PROCEDUREID LEFT JOIN TP_MST_USER CU ON CU.USERID = T.CREATEUSERID WHERE T.ACCOUNTID = :ACCOUNTID AND T.CREATETIME >= :CREATETIMEBEGIN AND T.CREATETIME < :CREATETIMEEND AND (:BARCODE IS NULL OR :BARCODE = '' OR INSTR(T.BARCODE, :BARCODE) > 0) AND (:MATNR IS NULL OR :MATNR = '' OR INSTR(T.MATNR, :MATNR) > 0) AND (:IDNRK IS NULL OR :IDNRK = '' OR INSTR(T.IDNRK, :IDNRK) > 0) AND (:IDNRKNAME IS NULL OR :IDNRKNAME = '' OR INSTR(T.IDNRKNAME, :IDNRKNAME) > 0) AND (:NOONLYCODEFLAG = 1 OR T.IDNRKONLYCODE IS NULL OR T.IDNRKONLYCODE = '') "; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":BARCODE", OracleDbType.Varchar2, cre.Properties["BarCode"], ParameterDirection.Input), new OracleParameter(":MATNR", OracleDbType.Varchar2, cre.Properties["Matnr"], ParameterDirection.Input), new OracleParameter(":IDNRK", OracleDbType.Varchar2, cre.Properties["Idnrk"], ParameterDirection.Input), new OracleParameter(":IDNRKNAME", OracleDbType.Varchar2, cre.Properties["IdnrkName"], ParameterDirection.Input), new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input), new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input), new OracleParameter(":NOONLYCODEFLAG", OracleDbType.Int32, cre.Properties["NoOnlyCodeFlag"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 保存产品装配组件唯一编码 /// /// /// public static ServiceResultEntity SaveIdnrkOnlyCode(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int relationID = Convert.ToInt32(cre.Properties["RelationID"]); string idnrkOnlyCode = cre.Properties["IdnrkOnlyCode"] + ""; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = null; //更新 sqlString = @"update TP_PM_BARCODEIDNRKREL set IDNRKONLYCODE = :idnrkOnlyCode where RELATIONID = :relationID"; OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":relationID",OracleDbType.Int32,relationID,ParameterDirection.Input), new OracleParameter(":idnrkOnlyCode",OracleDbType.Varchar2, idnrkOnlyCode,ParameterDirection.Input), }; sre.OtherStatus = oracleTrConn.ExecuteNonQuery(sqlString, Paras); oracleTrConn.Commit(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } #endregion #region 品管数据记录明细表 /// /// 品管数据记录明细表 /// /// /// public static ServiceResultEntity GetPinGuanDetail(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = @" SELECT DISTINCT PGI.BARCODE, GD.GOODSCODE, GD.GROUTINGLINECODE, PGI.WORKSTATION, U.USERNAME, U.USERCODE, PGI.CREATETIME, PGI.INSPECTIONLEVEL, PGI.PACKINGDEFECT, PGI.INSPECTIONGOODSLEVEL, L.GOODSLEVELNAME, (SELECT LISTAGG(to_char(D.defectcode) || '#' || to_char(d.defectname) || '#' || to_char(d.defectusercode), ',') within GROUP(ORDER BY d.productiondefectid) FROM tp_pm_defect d WHERE D.PRODUCTIONDATAID = PDD.PRODUCTIONDATAID AND D.VALUEFLAG = '1') AS DEFECT, PGI.VALUEFLAG FROM TP_PM_PinGuanInspection PGI LEFT JOIN TP_MST_USER U ON PGI.CREATEUSERID = U.USERID LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GD ON GD.BARCODE = PGI.BARCODE LEFT JOIN TP_MST_GOODSLEVEL L ON L.GOODSLEVELTYPEID = GD.GOODSLEVELTYPEID LEFT JOIN (SELECT PD2.BARCODE, PD2.PRODUCTIONDATAID FROM ( SELECT PD1.BARCODE, PD1.PRODUCTIONDATAID, ROW_NUMBER() OVER(PARTITION BY PD1.BARCODE ORDER BY PD1.PRODUCTIONDATAID DESC) AS DATAID FROM TP_PM_PRODUCTIONDATA PD1 WHERE PD1.VALUEFLAG = '1' AND PD1.CHECKBATCHNO = '1' ORDER BY PD1.CREATETIME DESC ) PD2 WHERE PD2.DATAID = 1) PDD ON PDD.BARCODE = PGI.BARCODE WHERE PGI.CREATETIME >= :datebeginMasterStar AND PGI.CREATETIME <= :datebeginMasterEnd "; if (cre.Properties["BarCode"] + "" != "" && cre.Properties["BarCode"] + "" != null) { string barcode = cre.Properties["BarCode"].ToString(); sqlString += " AND PGI.BARCODE = :BARCODE "; } if (cre.Properties["WorkStation"] + "" != "" && cre.Properties["WorkStation"] + "" != null) { sqlString += " AND PGI.WORKSTATION = :WORKSTATION "; } if (cre.Properties["User"] + "" != "" && cre.Properties["User"] + "" != null) { sqlString += " AND U.USERCODE like :USERCODE "; } sqlString += " order by PGI.CREATETIME,PGI.BARCODE "; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":datebeginMasterStar", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input), new OracleParameter(":datebeginMasterEnd", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input), new OracleParameter(":BARCODE", OracleDbType.Varchar2, cre.Properties["BarCode"], ParameterDirection.Input), new OracleParameter(":WORKSTATION", OracleDbType.Varchar2, cre.Properties["WorkStation"], ParameterDirection.Input), new OracleParameter(":USERCODE", OracleDbType.Varchar2, "%"+cre.Properties["User"]+"%", ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 作业指导书配置 /// /// 作业指导书配置 /// /// /// /// public static ServiceResultEntity GetProcedure(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = ""; if (cre.Properties["onlyflag"].ToString() == "1") { sqlString = " SELECT sd.procedureName,sd.procedureID,1 as AlreadyCheck" + " FROM TP_PC_SOPBOOKTYPEDATA sd\n" + " WHERE sd.soptypeid = :soptypeid"; } else { sqlString = "SELECT (SELECT 1\n" + " FROM TP_PC_SOPBOOKTYPEDATA sd\n" + " WHERE sd.procedurename = p.procedureName " + " and sd.soptypeid = :soptypeid) AS check1\n" + " ,p.procedureName as procedureName\n" + " ,p.procedureID\n" + " FROM tp_pc_procedure p\n" + " WHERE p.valueflag = '1'\n" + " AND p.accountid = :accountid"; if (!string.IsNullOrWhiteSpace(cre.Properties["ProcedureName"] + "")) { sqlString += " AND P.PROCEDURENAME LIKE :PROCEDURENAME"; } } OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), new OracleParameter(":soptypeid", OracleDbType.NVarchar2, cre.Properties["SOPTYPEID"], ParameterDirection.Input), new OracleParameter(":PROCEDURENAME", OracleDbType.NVarchar2, "%"+cre.Properties["ProcedureName"]+"%", ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 保存 作业指导书配置 /// /// /// public static ServiceResultEntity SaveSOPData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int soptypeID = Convert.ToInt32(cre.Properties["SOPTYPEID"]); string soptype = cre.Properties["SOPTYPE"] + ""; DataTable procedureTable = cre.Data.Tables[0]; int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = null; if (procedureTable != null && procedureTable.Rows.Count > 0) { //先删除后插入 sqlString = "delete from TP_PC_SOPBOOKTYPEDATA where soptypeid=" + soptypeID; oracleTrConn.ExecuteNonQuery(sqlString); foreach (DataRow item in procedureTable.Rows) { sqlString = @"insert into TP_PC_SOPBOOKTYPEDATA ( soptypeid, SOPTYPENAME, PROCEDUREID, PROCEDURENAME, VALUEFLAG ) values ( :soptypeid, :SOPTYPENAME, :PROCEDUREID, :PROCEDURENAME, 1 )"; OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":soptypeid",OracleDbType.Int32,soptypeID,ParameterDirection.Input), new OracleParameter(":SOPTYPENAME",OracleDbType.Varchar2, soptype,ParameterDirection.Input), new OracleParameter(":PROCEDUREID",OracleDbType.Int32,item["PROCEDUREID"],ParameterDirection.Input), new OracleParameter(":PROCEDURENAME",OracleDbType.Varchar2, item["PROCEDURENAME"] + "",ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlString, Paras); } } oracleTrConn.Commit(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } #endregion #region 分配到工序节点 /// /// 分配到工序节点 /// /// /// /// public static ServiceResultEntity GetProcedureID(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = "SELECT PU.PROCEDUREID,PU.NODENO,P.PROCEDURENAME,1 as AlreadyCheck\n" + "FROM TP_PC_ProcedureUser PU\n" + "LEFT JOIN TP_PC_procedure P ON PU.PROCEDUREID = P.PROCEDUREID\n" + "WHERE PU.USERID = :userID"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":userID", OracleDbType.NVarchar2, cre.Properties["userID"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 保存 分配到工序节点 /// /// /// public static ServiceResultEntity SaveProcedureData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); int userID = Convert.ToInt32(cre.Properties["UserID"]); DataTable procedureTable = cre.Data.Tables[0]; int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = null; if (procedureTable != null && procedureTable.Rows.Count > 0) { //先删除后插入 sqlString = "delete from TP_PC_ProcedureUser where userid=" + userID; oracleTrConn.ExecuteNonQuery(sqlString); foreach (DataRow item in procedureTable.Rows) { sqlString = @"SELECT NODENO FROM TP_PC_procedure WHERE PROCEDUREID = " + item["PROCEDUREID"]; int nodeNo = int.Parse(oracleTrConn.GetSqlResultToStr(sqlString)); sqlString = @"insert into TP_PC_ProcedureUser ( USERID, PROCEDUREID, PRODUCTIONLINEID, NODENO, CREATEUSERID ) values ( :USERID, :PROCEDUREID, 1, :NODENO, :CREATEUSERID )"; OracleParameter[] Paras = new OracleParameter[] { new OracleParameter(":USERID",OracleDbType.Int32,userID,ParameterDirection.Input), new OracleParameter(":PROCEDUREID",OracleDbType.Int32,item["PROCEDUREID"],ParameterDirection.Input), new OracleParameter(":NODENO",OracleDbType.Varchar2, nodeNo,ParameterDirection.Input), new OracleParameter(":CREATEUSERID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlString, Paras); } } oracleTrConn.Commit(); } catch (Exception ex) { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); } throw ex; } finally { if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return sre; } #endregion #region BOM物料检验明细表 /// /// BOM物料检验明细表 /// /// /// public static ServiceResultEntity GetBOMDetailData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = @" SELECT f.barcode ,CASE WHEN f.checkvalue = 1 THEN '装配正确' WHEN f.checkvalue = 2 THEN '装配错误' WHEN f.checkvalue = 3 THEN '漏装' WHEN f.checkvalue = 4 THEN '多装' ELSE '' END checkvalue ,pd.idnrk ,pd.MEINS ,f.bomname IDNRKNAME ,cu.usercode CREATEUSERNAME ,cu.USERNAME CheckNames ,f.createtime FROM tp_pm_fqcbom f LEFT JOIN tp_mst_packingbomdetail pd ON pd.packingbomdetailid = f.packingbomdetailid LEFT JOIN tp_mst_user cu ON cu.userid = f.createuserid WHERE f.CREATETIME >= :CREATETIMEBEGIN AND f.CREATETIME < :CREATETIMEEND AND (:BARCODE IS NULL OR :BARCODE = '' OR INSTR(f.BARCODE, :BARCODE) > 0) AND (:MATNR IS NULL OR :MATNR = '' OR INSTR(pd.idnrk, :MATNR) > 0) AND (:IDNRKNAME IS NULL OR :IDNRKNAME = '' OR INSTR(f.bomname, :IDNRKNAME) > 0) AND (:CHECKVALUE = 0 OR f.CHECKVALUE IS NULL OR f.checkvalue = :CHECKVALUE) "; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":BARCODE", OracleDbType.Varchar2, cre.Properties["BarCode"], ParameterDirection.Input), new OracleParameter(":MATNR", OracleDbType.Varchar2, cre.Properties["Matnr"], ParameterDirection.Input), new OracleParameter(":IDNRKNAME", OracleDbType.Varchar2, cre.Properties["IdnrkName"], ParameterDirection.Input), new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input), new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input), new OracleParameter(":CHECKVALUE", OracleDbType.Int32, cre.Properties["CHECKVALUE"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// BOM物料检验汇总 /// /// /// public static ServiceResultEntity GetBOMDSummary(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = @" SELECT BARCODE, GOODSCODE, INSPECTNAME, CREATEUSERNAME, CheckNames, CASE WHEN Unqualifiednums <> 0 THEN '不合格' ELSE ' 合格'END AS ConformityMarking, createtime FROM( SELECT DISTINCT pd.BARCODE, PD.GOODSCODE, PD.INSPECTNAME , pd.CREATEUSERNAME, pd.CheckNames, PD.createtime, SUM(CASE WHEN PD.checkflag=1 THEN 1 ELSE 0 END ) qualifiednums, SUM(CASE WHEN PD.checkflag<>1 THEN 1 ELSE 0 END ) Unqualifiednums FROM( SELECT f.barcode ,GD.GOODSCODE ,CASE WHEN f.checkvalue = 1 THEN '装配正确' WHEN f.checkvalue = 2 THEN '装配错误' WHEN f.checkvalue = 3 THEN '漏装' WHEN f.checkvalue = 4 THEN '多装' ELSE '' END checkvalue ,f.checkvalue AS checkflag ,pd.idnrk ,pd.MEINS ,f.bomname IDNRKNAME ,cu.usercode CREATEUSERNAME ,cu.USERNAME CheckNames ,f.createtime ,fi.INSPECTNAME FROM tp_pm_fqcbom f LEFT JOIN tp_mst_packingbomdetail pd ON pd.packingbomdetailid = f.packingbomdetailid LEFT JOIN tp_mst_user cu ON cu.userid = f.createuserid INNER JOIN TP_PM_GROUTINGDAILYDETAIL GD ON GD.BARCODE=F.BARCODE LEFT JOIN TP_PM_FQCITEMSDATA fid ON fid.barcode = f.barcode and fid.INSPECTTYPE = '617' left join TP_PM_FQCITEMS fi on fi.itemsid = fid.itemsid WHERE f.CREATETIME >= :CREATETIMEBEGIN AND f.CREATETIME < :CREATETIMEEND AND (:BARCODE IS NULL OR :BARCODE = '' OR INSTR(f.BARCODE, :BARCODE) > 0) AND (:MATNR IS NULL OR :MATNR = '' OR INSTR(pd.idnrk, :MATNR) > 0) AND (:IDNRKNAME IS NULL OR :IDNRKNAME = '' OR INSTR(f.bomname, :IDNRKNAME) > 0) AND (:CHECKVALUE = 0 OR f.CHECKVALUE IS NULL OR f.checkvalue = :CHECKVALUE) ) PD GROUP BY pd.BARCODE, PD.GOODSCODE,pd.CREATEUSERNAME,pd.CheckNames, PD.INSPECTNAME, PD.createtime ) "; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":BARCODE", OracleDbType.Varchar2, cre.Properties["BarCode"], ParameterDirection.Input), new OracleParameter(":MATNR", OracleDbType.Varchar2, cre.Properties["Matnr"], ParameterDirection.Input), new OracleParameter(":IDNRKNAME", OracleDbType.Varchar2, cre.Properties["IdnrkName"], ParameterDirection.Input), new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input), new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input), new OracleParameter(":CHECKVALUE", OracleDbType.Int32, cre.Properties["CHECKVALUE"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region BOM物料检验明细表 /// /// BOM物料检验明细表 /// /// /// public static ServiceResultEntity GetBarCodeData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string barcodes = ""; string goodscodes = ""; if (cre.Properties.ContainsKey("InpectName")) { string barcodeString = @"SELECT distinct p.BARCODE,p.BARCODE ||'[' || gdd.GOODSCODE || ']' GOODSCODES FROM TP_PM_FQCITEMSDATA p left join TP_PM_GROUTINGDAILYDETAIL gdd on gdd.BARCODE = p.barcode WHERE p.VALUEFLAG =1 AND p.CREATETIME>=:CREATETIMEBEGIN AND p.CREATETIME<=:CREATETIMEEND AND ( p.BARCODE = :BARCODE OR :BARCODE IS NULL OR :BARCODE='') AND (p.ITEMSID = :ITEMSID OR :ITEMSID IS NULL OR :ITEMSID='') "; OracleParameter[] parameters1 = new OracleParameter[] { new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input), new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input), new OracleParameter(":ITEMSID", OracleDbType.Int32, cre.Properties["InpectName"], ParameterDirection.Input), new OracleParameter(":BARCODE", OracleDbType.NVarchar2, cre.Properties["BarCode"], ParameterDirection.Input), }; DataTable barcodeTable = con.GetSqlResultToDt(barcodeString, parameters1); if (barcodeTable != null && barcodeTable.Rows.Count > 0) { for (int i = 0; i < barcodeTable.Rows.Count; i++) { if (i == 0) { barcodes = "'"+barcodeTable.Rows[i]["BARCODE"].ToString()+"'"; goodscodes = "'" + barcodeTable.Rows[i]["GOODSCODES"].ToString() + "'"; } else if (i <= 100) { barcodes = barcodes + ",'" + barcodeTable.Rows[i]["BARCODE"].ToString()+"'"; goodscodes = goodscodes + ",'" + barcodeTable.Rows[i]["GOODSCODES"].ToString() + "'"; } } } else { return sre; } string sqlString1 = ""; if (string.IsNullOrEmpty(cre.Properties["BarCode"].ToString())) { sqlString1 = "SELECT * " + " FROM (select " + "tpf.XUHAO,p.ITEMSDETAILID, " + "p.INSPECTITEM AS 检验项目, " + "fd.technicalrequirement as 检验依据, " + "p.BARCODE ||'[' || gdd.GOODSCODE || ']' BARCODE," + " CASE WHEN tpf.inspectmark = 630 THEN CASE WHEN p.isqualified = 1 THEN '合格' ELSE '不合格' END ELSE to_char(p.inspectvalue) END AS inspectvalue" + " from TP_PM_FQCITEMSDATA p" + " LEFT JOIN TP_PM_FQCITEMSDETAIL tpf ON p.INSPECTITEM = tpf.INSPECTITEM AND p.ITEMSID = tpf.ITEMSID" + " left join tp_pm_fqcitemsdetail fd on p.itemsdetailid = fd.itemsdetailid" + " left join TP_PM_GROUTINGDAILYDETAIL gdd on gdd.BARCODE = p.barcode" + " WHERE p.VALUEFLAG =1 " + " AND p.CREATETIME>=:CREATETIMEBEGIN" + " AND p.CREATETIME<=:CREATETIMEEND" + " AND (p.ITEMSID = :ITEMSID OR :ITEMSID IS NULL OR :ITEMSID='')" + " AND ( p.BARCODE IN (" + barcodes + ") ) ) PIVOT (listagg ('['||INSPECTVALUE||']', ',') WITHIN" + " GROUP (ORDER BY INSPECTVALUE) FOR BARCODE IN (" + goodscodes + "))" + "ORDER BY XUHAO, ITEMSDETAILID DESC "; } else { sqlString1 = @" SELECT * FROM (select tpf.XUHAO,p.ITEMSDETAILID, p.INSPECTITEM AS 检验项目, fd.technicalrequirement as 检验依据, p.BARCODE ||'[' || gdd.GOODSCODE || ']' BARCODE, CASE WHEN tpf.inspectmark = 630 THEN CASE WHEN p.isqualified = 1 THEN '合格' ELSE '不合格' END ELSE to_char(p.inspectvalue) END AS inspectvalue from TP_PM_FQCITEMSDATA p LEFT JOIN TP_PM_FQCITEMSDETAIL tpf ON p.INSPECTITEM = tpf.INSPECTITEM AND p.ITEMSID = tpf.ITEMSID left join tp_pm_fqcitemsdetail fd on p.itemsdetailid = fd.itemsdetailid left join TP_PM_GROUTINGDAILYDETAIL gdd on gdd.BARCODE = p.barcode WHERE p.VALUEFLAG =1 AND p.CREATETIME>=:CREATETIMEBEGIN AND p.CREATETIME<=:CREATETIMEEND AND (p.ITEMSID = :ITEMSID OR :ITEMSID IS NULL OR :ITEMSID='') AND (p.BARCODE = :BARCODE OR :BARCODE IS NULL OR :BARCODE='') ) PIVOT (listagg ('['||INSPECTVALUE||']', ',') WITHIN GROUP (ORDER BY INSPECTVALUE) FOR BARCODE IN (" + goodscodes + ")) ORDER BY XUHAO,ITEMSDETAILID DESC "; } OracleParameter[] parameters2 = new OracleParameter[] { new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input), new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input), new OracleParameter(":ITEMSID", OracleDbType.Int32, cre.Properties["InpectName"], ParameterDirection.Input), new OracleParameter(":BARCODE", OracleDbType.NVarchar2, cre.Properties["BarCode"], ParameterDirection.Input), }; DataSet dt = con.GetSqlResultToDs(sqlString1, parameters2); dt.Tables[0].Columns.Remove("XUHAO"); dt.Tables[0].Columns.Remove("ITEMSDETAILID"); for (int i = 0; i < dt.Tables[0].Columns.Count; i++) { if (dt.Tables[0].Columns[i].ColumnName.Contains("'")) { dt.Tables[0].Columns[i].ColumnName = dt.Tables[0].Columns[i].ColumnName.Replace("'", ""); } } sre.Data = dt; sre.Data.Tables[0].TableName = "Detail"; string sqlString = @" SELECT distinct fd.barcode,g.goodscode,f.inspectname ,u.username, to_char(fd.createtime, 'yyyy-mm-dd hh24:mi') createtime FROM TP_PM_FQCITEMSDATA fd left join TP_PM_FQCITEMS f on f.itemsid = fd.itemsid left join tp_mst_goods g on fd.goodsid = g.goodsid left join tp_mst_user u on fd.createuserid = u.userid WHERE fd.VALUEFLAG =1 AND fd.CREATETIME>=:CREATETIMEBEGIN AND fd.CREATETIME<=:CREATETIMEEND AND (fd.barcode=:BARCODE OR :BARCODE IS NULL OR :BARCODE='') AND (fd.ITEMSID = :ITEMSID OR :ITEMSID IS NULL OR :ITEMSID='') "; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input), new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input), new OracleParameter(":ITEMSID", OracleDbType.Int32, cre.Properties["InpectName"], ParameterDirection.Input), new OracleParameter(":BARCODE", OracleDbType.NVarchar2, cre.Properties["BarCode"], ParameterDirection.Input), }; sre.Data.Tables.Add(con.GetSqlResultToDt(sqlString, parameters)); sre.Data.Tables[1].TableName = "Data"; } else { string sqlString = @" SELECT distinct fd.barcode,g.goodscode,f.inspectname ,u.username, to_char(fd.createtime, 'yyyy-mm-dd hh24:mi') createtime FROM TP_PM_FQCITEMSDATA fd left join TP_PM_FQCITEMS f on f.itemsid = fd.itemsid left join tp_mst_goods g on fd.goodsid = g.goodsid left join tp_mst_user u on fd.createuserid = u.userid WHERE fd.VALUEFLAG =1 AND fd.CREATETIME>=:CREATETIMEBEGIN AND fd.CREATETIME<=:CREATETIMEEND AND (fd.barcode=:BARCODE OR :BARCODE IS NULL OR :BARCODE='') "; if (cre.Properties.ContainsKey("InpectType")) { sqlString += " AND f.INSPECTTYPE = 615 "; } else { sqlString += " AND f.INSPECTTYPE <> 615 "; } sqlString += " order by to_char(fd.createtime, 'yyyy-mm-dd hh24:mi') "; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input), new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input), new OracleParameter(":BARCODE", OracleDbType.NVarchar2, cre.Properties["BarCode"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); sre.Data.Tables[0].TableName = "Data"; } return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 查询导出明细 /// /// /// public static ServiceResultEntity GetDaochuData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = @" SELECT fd.barcode ,g.goodscode ,gt.goodstypename ,g.groutingdate ,fd.inspectitem ,case when fdd.unitname is null then '-' else to_char(fdd.unitname) end unitname ,fdd.technicalrequirement ,fd.inspectvalue ,case when fd.isqualified = 1 then '合格' else '不合格' end as isqualified FROM tp_pm_fqcitemsdata fd LEFT JOIN tp_pm_fqcitemsdetail fdd ON fd.itemsdetailid = fdd.itemsdetailid LEFT JOIN tp_pm_fqcitems f ON f.itemsid = fd.itemsid left join tp_pm_groutingdailydetail g on g.barcode = fd.barcode left join tp_mst_goods gg on gg.goodsid = g.goodsid left join tp_mst_goodstype gt on gg.goodstypeid = gt.goodstypeid WHERE fd.barcode = :BARCODE AND f.inspectname = :INSPECTNAME AND fd.valueflag = 1 "; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":BARCODE", OracleDbType.Varchar2, cre.Properties["BarCode"], ParameterDirection.Input), new OracleParameter(":INSPECTNAME", OracleDbType.Varchar2, cre.Properties["InspectName"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region QC检验计划完成明细表 /// /// BOM物料检验明细表 /// /// /// public static ServiceResultEntity GetInspectType(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = @"SELECT dd.dictionaryid, dd.dictionaryvalue, dd.displayno " + " FROM TP_MST_DataDictionary dd\n" + " WHERE dd.DictionaryType = 'TPC021'\n" + " AND dd.valueflag = '1'\n" + " AND dd.accountid = :ACCOUNTID\n" + " ORDER BY dd.displayno"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// BOM物料检验计划汇总 /// /// /// public static ServiceResultEntity GetDetail(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = @" SELECT DISTINCT TP_PM_FQCITEMSPLAN.PLANID , TP_PM_FQCITEMSPLAN.GOODSCODE , TP_PM_FQCITEMSPLAN.MATERIALCODE , TP_MST_DATADICTIONARY.DICTIONARYVALUE , TP_PM_FQCITEMSPLAN.PLANDATE , OUTQUANTITY , TP_PM_FQCITEMSPLAN.PLANINSPECTIONQUANTITY , TP_PM_FQCITEMSPLAN.INSPECTIONQUANTITY , DECODE( NVL( INSPECTIONQUANTITY, 0 ), 0, '0%', TO_CHAR( ( (NVL( INSPECTIONQUANTITY, 0 ))/ DECODE( NVL( PLANINSPECTIONQUANTITY, 1 ), 0, 1, NVL( PLANINSPECTIONQUANTITY, 1 ) ) ) * 100, 'fm99990.00' ) || '%' ) AS WANCHENG FROM TP_PM_FQCITEMSPLAN LEFT JOIN TP_MST_DATADICTIONARY ON TP_MST_DATADICTIONARY.DICTIONARYID=TP_PM_FQCITEMSPLAN.INSPECTTYPE LEFT JOIN TP_MST_GOODS ON TP_MST_GOODS.GOODSID=TP_PM_FQCITEMSPLAN.GOODSID LEFT JOIN tp_mst_goodsType gt ON gt.goodstypeid = TP_MST_GOODS.goodstypeid AND TP_MST_DATADICTIONARY.DICTIONARYTYPE='TPC021' WHERE TP_PM_FQCITEMSPLAN.VALUEFLAG=1 AND TP_PM_FQCITEMSPLAN.ACCOUNTID=1 AND TP_PM_FQCITEMSPLAN.PLANDATE = :PLANDATE AND (:INSPECTTYPE = 0 OR TP_PM_FQCITEMSPLAN.INSPECTTYPE IS NULL OR TP_PM_FQCITEMSPLAN.INSPECTTYPE = :INSPECTTYPE) AND (TP_PM_FQCITEMSPLAN.GOODSCODE = :GOODSCODE OR :GOODSCODE IS NULL OR :GOODSCODE='') AND (:GOODSTYPE IS NULL OR instr(gt.goodstypecode, :GOODSTYPE) = 1)"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":GOODSTYPE", OracleDbType.Varchar2, cre.Properties["GoodsType"], ParameterDirection.Input), new OracleParameter(":GOODSCODE", OracleDbType.Varchar2, cre.Properties["GoodsCode"], ParameterDirection.Input), new OracleParameter(":PLANDATE", OracleDbType.Date, cre.Properties["CreateTime"], ParameterDirection.Input), new OracleParameter(":INSPECTTYPE", OracleDbType.Int32, cre.Properties["InpectType"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// BOM物料检验计划明细 /// /// /// public static ServiceResultEntity GetDetailData(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = @" SELECT DISTINCT TPF.BARCODE, TMU.USERCODE, TMU.USERNAME FROM TP_PM_FQCITEMSDATA TPF LEFT JOIN TP_MST_USER TMU ON TPF.CREATEUSERID = TMU.USERID WHERE TPF.PLANID = :PLANID "; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":PLANID", OracleDbType.Int32, cre.Properties["PLANID"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region QC检验分析汇总表 /// /// BOM物料检验明细表 /// /// /// public static ServiceResultEntity GetInspectName(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { //string sqlString = @"SELECT ITEMSID,INSPECTTYPE,INSPECTNAME FROM TP_PM_FQCITEMS WHERE VALUEFLAG=1 and ACCOUNTID=:AccountID"; string sqlString = "SELECT T.ITEMSID,T.INSPECTTYPE,T.INSPECTNAME ,T.INSPECTVERSION ,T.VALUEFLAG " + "FROM TP_PM_FQCITEMS T " + "WHERE T.ACCOUNTID = :ACCOUNTID " + "AND EXISTS( SELECT 1 " + "FROM TP_PM_FQCITEMSDATA F " + "WHERE F.ITEMSID = T.ITEMSID " + "AND F.DATAID IS NOT NULL ) " + "ORDER BY " + "T.INSPECTNAME, " + "T.INSPECTVERSION"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":ACCOUNTID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 检验计划汇总 /// /// /// public static ServiceResultEntity Get080116Detail(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = @" WITH 基础 AS( SELECT INSPECTITEM , COUNT(DISTINCT BARCODE) COUNTS, SUM(CASE WHEN ISQUALIFIED =0 THEN 1 else 0 END) ISQUALIFIED FROM TP_PM_FQCITEMSDATA WHERE VALUEFLAG=1 AND (ITEMSID = :ITEMSID OR :ITEMSID IS NULL OR :ITEMSID='') AND CREATETIME>=:CREATETIMEBEGIN AND CREATETIME<=:CREATETIMEEND GROUP BY INSPECTITEM ), 总不合格数 AS ( SELECT SUM(CASE WHEN ISQUALIFIED =0 THEN 1 else 0 END) 总不合格数 FROM TP_PM_FQCITEMSDATA WHERE VALUEFLAG=1 AND CREATETIME>=:CREATETIMEBEGIN AND CREATETIME<=:CREATETIMEEND AND (ITEMSID = :ITEMSID OR :ITEMSID IS NULL OR :ITEMSID='') ) SELECT INSPECTITEM,COUNTS,ISQUALIFIED, DECODE( NVL( ISQUALIFIED, 0 ), 0, '0%', TO_CHAR( ( NVL( ISQUALIFIED, 0 ) / DECODE( NVL( COUNTS, 1 ), 0, 1, NVL( COUNTS, 1 ) ) ) * 100, 'fm99990.0' ) || '%' ) AS BUHEGELV, DECODE( NVL( ISQUALIFIED, 0 ), 0, '0%', TO_CHAR( ( NVL( ISQUALIFIED, 0 ) / DECODE( NVL( 总不合格数, 1 ), 0, 1, NVL( 总不合格数, 1 ) ) ) * 100, 'fm99990.0' ) || '%' ) AS ZHANBI FROM 基础 LEFT JOIN 总不合格数 ON 1=1"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":ITEMSID", OracleDbType.Int32, cre.Properties["ITEMSID"], ParameterDirection.Input), new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input), new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 检验计划明细 /// /// /// public static ServiceResultEntity GetDetailInspection(SUserInfo sUserInfo, ClientRequestEntity cre) { ServiceResultEntity sre = new ServiceResultEntity(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string sqlString = @" SELECT TPF.BARCODE, TPF.INSPECTITEM INSPECTITEMS, TPF.INSPECTVALUE, TMU.USERCODE, TMU.USERNAME, TPF.CREATETIME, (SELECT GOODSCODE FROM TP_PM_GROUTINGDAILYDETAIL WHERE VALUEFLAG=1 AND BARCODE=TPF.BARCODE) GOODSCODE FROM TP_PM_FQCITEMSDATA TPF LEFT JOIN TP_MST_USER TMU ON TPF.CREATEUSERID = TMU.USERID WHERE TPF.VALUEFLAG = 1 AND TMU.VALUEFLAG = 1 AND BARCODE IN ( SELECT BARCODE FROM TP_PM_FQCITEMSDATA WHERE VALUEFLAG = 1 AND ( ITEMSID = NULL OR NULL IS NULL OR NULL = '' ) AND CREATETIME >= :CREATETIMEBEGIN AND CREATETIME <= :CREATETIMEEND AND INSPECTITEM = :INSPECTITEM AND ISQUALIFIED = 0 ) ORDER BY BARCODE DESC"; OracleParameter[] parameters = new OracleParameter[] { new OracleParameter(":INSPECTITEM", OracleDbType.Varchar2, cre.Properties["INSPECTITEM"], ParameterDirection.Input), new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["CreateTimeBegin"], ParameterDirection.Input), new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["CreateTimeEnd"], ParameterDirection.Input), }; sre.Data = con.GetSqlResultToDs(sqlString, parameters); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion } }