/******************************************************************************* * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential * 类的信息： * 1.程序名称：PCModuleLogicDAL.cs * 2.功能描述：生产配置数据库访问类（插入、修改、删除） * 编辑履历： * 作者 日期 版本 修改内容 * 陈冰 2014/09/3 1.00 新建 *******************************************************************************/ using System; using System.Data; using System.Linq; using Dongke.IBOSS.PRD.Basics.BaseResources; using Dongke.IBOSS.PRD.Basics.DataAccess; using Dongke.IBOSS.PRD.Service.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels.PCModule; using Oracle.ManagedDataAccess.Client; namespace Dongke.IBOSS.PRD.Service.PCModuleLogic { /// /// 生产配置数据库访问类（插入、修改、删除） /// public partial class PCModuleLogicDAL { #region 生产线管理 /// /// 保存生产线 /// /// 生产线实体 /// 用户基本信息 /// public static int SaveProductionLine(ProductionLineEntity productionLine, SUserInfo sUserInfo) { int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { #region 新建生产线 if (productionLine.ProductionLineID == 0) { #region 判断编码是否重复 string sqlText = " SELECT ProductionLineID " + " FROM TP_PC_ProductionLine " + " WHERE " + " AccountID=:accountID " + " AND ProductionLineCode=:productionLineCode"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":productionLineCode",productionLine.ProductionLineCode), }; string returnCode = oracleTrConn.GetSqlResultToStr(sqlText, paras); if (!string.IsNullOrEmpty(returnCode)) { return Constant.RETURN_IS_EXIST; } #endregion #region 查询新ID sqlText = "SELECT SEQ_PC_PRODUCTIONLINE_ID.nextval FROM dual"; productionLine.ProductionLineID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText)); #endregion #region 插入生产线 sqlText = "INSERT INTO TP_PC_ProductionLine (ProductionLineID," + "ProductionLineCode," + "ProductionLineName," + "FlowXML," + "Remarks," + "AccountID," + "CreateUserID," + "UpdateUserID)" + " VALUES(:ProductionLineID," + ":productionLineCode," + ":productionLineName," + ":flowXML," + ":remarks," + ":accountID," + ":createUserID," + ":updateUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":ProductionLineID",productionLine.ProductionLineID), new OracleParameter(":productionLineCode",productionLine.ProductionLineCode), new OracleParameter(":productionLineName",productionLine.ProductionLineName), new OracleParameter(":flowXML", System.Text.Encoding.UTF8.GetBytes(productionLine.FlowXML)), new OracleParameter(":remarks",productionLine.Remarks), new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":createUserID",sUserInfo.UserID), new OracleParameter(":updateUserID",sUserInfo.UserID), }; foreach (OracleParameter item in paras) { if (item.Value + "" == "") { item.Value = System.DBNull.Value; } } returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion //#region 查询新ID //sqlText = "SELECT SEQ_PC_PRODUCTIONLINE_ID.currval FROM dual"; //productionLine.ProductionLineID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText)); //#endregion #region 插入工序 foreach (ProcedureEntity procedure in productionLine.ProcedureList) { sqlText = "SELECT SEQ_PC_PROCEDURE_PROCEDUREID.Nextval FROM dual"; procedure.ProcedureID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText)); sqlText = "INSERT INTO TP_PC_Procedure (" + "ProcedureID," + "NodeNo," + "ProductionLineID," + "ProcedureCode," + "ProcedureName," + "ProcedureModel," + "ModelType," + "NodeType," + "MustFlag," + "CollectType," + "PieceType," + "IsSpecialRework," + "IsSemireWork," + "OrganizationID," + "Remarks," + "AccountID," + "CreateUserID," + "UpdateUserID)" + " VALUES(" + ":procedureID," + ":nodeNo," + ":productionLineID," + ":procedureCode," + ":procedureName," + ":procedureModel," + ":ModelType," + ":NodeType," + ":mustFlag," + ":collectType," + ":pieceType," + ":isSpecialRework," + ":IsSemireWork," + ":organizationID," + ":remarks," + ":accountID," + ":createUserID," + ":updateUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureCode",procedure.ProcedureCode), new OracleParameter(":procedureName",procedure.ProcedureName), new OracleParameter(":procedureModel",procedure.ProcedureModel), new OracleParameter(":ModelType",procedure.ModelType), new OracleParameter(":NodeType",procedure.NodeType), new OracleParameter(":mustFlag",procedure.MustFlag), new OracleParameter(":collectType",procedure.CollectType), new OracleParameter(":pieceType",procedure.PieceType), new OracleParameter(":isSpecialRework",procedure.IsSpecialRework), new OracleParameter(":IsSemireWork",procedure.IsSemireWork), new OracleParameter(":organizationID",procedure.OrganizationID), new OracleParameter(":remarks",procedure.Remarks), new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":createUserID",sUserInfo.UserID), new OracleParameter(":updateUserID",sUserInfo.UserID), }; foreach (OracleParameter item in paras) { if (item.Value + "" == "") { item.Value = System.DBNull.Value; } } returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion } #endregion #region 编辑生产线 else { #region 校验时间戳 // todo string sql = "SELECT OPTimeStamp FROM TP_PC_ProductionLine" + " WHERE ProductionLineID = " + productionLine.ProductionLineID + " and OPTimeStamp = :OPTimeStamp"; OracleParameter[] parmetersSql = new OracleParameter[] { new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, productionLine.OPTimeStamp, ParameterDirection.Input), }; DataSet returnDataset = oracleTrConn.GetSqlResultToDs(sql, parmetersSql); if (returnDataset != null && returnDataset.Tables[0].Rows.Count == 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); return Constant.RETURN_IS_DATACHANGED; } #endregion #region 判断编码是否重复 string sqlText = " SELECT ProductionLineID " + " FROM TP_PC_ProductionLine " + " WHERE " + " AccountID=:accountID " + " AND ProductionLineCode=:productionLineCode" + " AND ProductionLineID <> :productionLineID"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":productionLineCode",productionLine.ProductionLineCode), new OracleParameter(":productionLineID",productionLine.ProductionLineID), }; string returnCode = oracleTrConn.GetSqlResultToStr(sqlText, paras); if (!string.IsNullOrEmpty(returnCode)) { return Constant.RETURN_IS_EXIST; } #endregion #region 编辑生产线 sqlText = "UPDATE TP_PC_ProductionLine SET " + "ProductionLineName=:productionLineName," + "FlowXML=:flowXML," + "Remarks=:remarks," + "UpdateTime=sysdate, " + "UpdateUserID=:updateUserID " + " WHERE ProductionLineID=:productionLineID"; paras = new OracleParameter[] { new OracleParameter(":productionLineName",productionLine.ProductionLineName), new OracleParameter(":flowXML", OracleDbType.Blob,System.Text.Encoding.UTF8.GetBytes(productionLine.FlowXML),ParameterDirection.Input), new OracleParameter(":remarks",productionLine.Remarks), new OracleParameter(":updateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":productionLineID", OracleDbType.Int32, productionLine.ProductionLineID,ParameterDirection.Input) }; //foreach (OracleParameter item in paras) //{ // if (item.Value + "" == "") // { // item.Value = System.DBNull.Value; // } //} returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 编辑工序 foreach (ProcedureEntity procedure in productionLine.ProcedureList) { if (procedure.EditingAddFlag) { #region 编辑时新添加的节点 sqlText = "SELECT SEQ_PC_PROCEDURE_PROCEDUREID.Nextval FROM dual"; procedure.ProcedureID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText)); sqlText = "INSERT INTO TP_PC_Procedure (" + "ProcedureID," + "NodeNo," + "ProductionLineID," + "ProcedureCode," + "ProcedureName," + "ProcedureModel," + "ModelType," + "NodeType," + "MustFlag," + "CollectType," + "PieceType," + "IsSpecialRework," + "IsSemireWork," + "OrganizationID," + "Remarks," + "AccountID," + "CreateUserID," + "UpdateUserID)" + " VALUES(" + ":procedureID," + ":nodeNo," + ":productionLineID," + ":procedureCode," + ":procedureName," + ":procedureModel," + ":ModelType," + ":NodeType," + ":mustFlag," + ":collectType," + ":pieceType," + ":isSpecialRework," + ":isSemireWork," + ":organizationID," + ":remarks," + ":accountID," + ":createUserID," + ":updateUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureCode",procedure.ProcedureCode), new OracleParameter(":procedureName",procedure.ProcedureName), new OracleParameter(":procedureModel",procedure.ProcedureModel), new OracleParameter(":ModelType",procedure.ModelType), new OracleParameter(":NodeType",procedure.NodeType), new OracleParameter(":mustFlag",procedure.MustFlag), new OracleParameter(":collectType",procedure.CollectType), new OracleParameter(":pieceType",procedure.PieceType), new OracleParameter(":isSpecialRework",procedure.IsSpecialRework), new OracleParameter(":isSemireWork",procedure.IsSemireWork), new OracleParameter(":organizationID",procedure.OrganizationID), new OracleParameter(":remarks",procedure.Remarks), new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":createUserID",sUserInfo.UserID), new OracleParameter(":updateUserID",sUserInfo.UserID), }; #endregion } else { #region 编辑 sqlText = "UPDATE TP_PC_Procedure SET " + " ProcedureName=:procedureName," + " ProcedureCode=:ProcedureCode," + " ModelType=:modelType," + " MustFlag=:mustFlag," + " CollectType=:collectType," + " OrganizationID=:organizationID," + " NodeType=:nodeType," + " PieceType=:pieceType," + " IsSpecialRework=:isSpecialRework," + " IsSemireWork=:isSemireWork," + " NodeNo=:nodeNo," + " Remarks=:remarks," + " updateUserID=:updateUserID," + " UpdateTime=sysdate" + " WHERE NodeNo=:nodeNo " + " AND ProductionLineID=:productionLineID"; paras = new OracleParameter[] { new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureName",procedure.ProcedureName), new OracleParameter(":ProcedureCode",procedure.ProcedureCode), new OracleParameter(":modelType",procedure.ModelType), new OracleParameter(":mustFlag",procedure.MustFlag), new OracleParameter(":collectType",procedure.CollectType), new OracleParameter(":organizationID",procedure.OrganizationID), new OracleParameter(":nodeType",procedure.NodeType), new OracleParameter(":pieceType",procedure.PieceType), new OracleParameter(":isSpecialRework",procedure.IsSpecialRework), new OracleParameter(":isSemireWork",procedure.IsSemireWork), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":remarks",procedure.Remarks), new OracleParameter(":updateUserID",sUserInfo.UserID), }; #endregion } foreach (OracleParameter item in paras) { if (item.Value + "" == "") { item.Value = System.DBNull.Value; } } returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 先删除生产工序流程明细 然后再插入 sqlText = "DELETE TP_PC_ProcedureFlow WHERE ProductionLineID=:productionLineID"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除对应产品 然后再插入 sqlText = "DELETE TP_PC_ProcedureGoods WHERE ProductionLineID=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除对应工号 然后再插入 sqlText = "DELETE TP_PC_ProcedureUser WHERE ProductionLineID=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除对应缺陷 然后再插入 sqlText = "DELETE TP_PC_ProcedureDefect WHERE ProductionLineID=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除缺陷对应工序工种表 然后再插入 sqlText = "DELETE TP_PC_DefectProcedureJobs WHERE ProductionLineID=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion } #endregion #region 插入生产工序属性等信息 foreach (ProcedureEntity procedure in productionLine.ProcedureList) { string sqlText; OracleParameter[] paras; #region 插入生产工序流程明细 if (procedure.ProcedureFlowDetailList != null) { foreach (ProcedureFlowEntity flowDetail in procedure.ProcedureFlowDetailList) { ProcedureEntity procedureWhere = productionLine.ProcedureList.Where(p => p.NodeNo == flowDetail.ArriveNodeNo).SingleOrDefault(); // 没有找到对应节点的ID if (procedureWhere == null) { return Constant.INT_IS_ZERO; } else { flowDetail.ArriveProcedureID = procedureWhere.ProcedureID; } sqlText = "INSERT INTO TP_PC_ProcedureFlow (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "FlowFlag," + "ArriveProcedureID," + "ArriveNodeNo" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":flowFlag," + ":arriveProcedureID," + ":arriveNodeNo" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":flowFlag",flowDetail.FlowFlag), new OracleParameter(":arriveProcedureID",flowDetail.ArriveProcedureID), new OracleParameter(":arriveNodeNo",flowDetail.ArriveNodeNo), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } } #endregion #region 插入生产工序对应产品表 foreach (DataRow row in procedure.ProcedureGoodsTable.Rows) { sqlText = "INSERT INTO TP_PC_ProcedureGoods (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "GOODSID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":goodsID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":goodsID",OracleDbType.Int32,row["goodsID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入生产工序对应工号表 foreach (DataRow row in procedure.ProcedureUserTable.Rows) { sqlText = "INSERT INTO TP_PC_ProcedureUser (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "UserID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":userID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":userID",OracleDbType.Int32,row["userID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入生产工序对应缺陷表 foreach (DataRow row in procedure.ProcedureDefectTable.Rows) { if (row["DefectID"] == DBNull.Value) { continue; } sqlText = "INSERT INTO TP_PC_ProcedureDefect (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "DefectID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":defectID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":defectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入缺陷对应工序工种表 foreach (DataRow row in procedure.DefectProcedureJobsTable.Rows) { if (row["DefectID"] == DBNull.Value || row["jobsID"] == DBNull.Value) { continue; } sqlText = "INSERT INTO TP_PC_DefectProcedureJobs (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "DefectID," + "JobsID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":DefectID," + ":jobsID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":DefectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input), new OracleParameter(":jobsID",OracleDbType.Int32,row["jobsID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion } #endregion oracleTrConn.Commit(); } catch (Exception ex) { oracleTrConn.Rollback(); throw ex; } finally { // 释放资源 if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return returnRows; } /// /// 停用生产线 /// /// 生产线ID /// 用户基本信息 /// int /// /// 陈冰 2014.09.15 新建 /// public static int StopProductionLine(int lineID, int flag, SUserInfo sUserInfo) { int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { // 更新总表 // 更新明细表 if (flag == 0) { string sqlText = "UPDATE tp_pc_productionline SET valueflag=0,UpdateUserID=:updateUserID WHERE productionlineid=:productionLineID"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":updateUserID",sUserInfo.UserID), new OracleParameter(":productionLineID",lineID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); sqlText = "UPDATE TP_PC_Procedure SET valueflagback=valueflag, valueflag=0,UpdateUserID=:updateUserID WHERE productionlineid=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } else { string sqlText = "UPDATE tp_pc_productionline SET valueflag=1,UpdateUserID=:updateUserID WHERE productionlineid=:productionLineID"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":updateUserID",sUserInfo.UserID), new OracleParameter(":productionLineID",lineID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); sqlText = "UPDATE TP_PC_Procedure SET valueflag=valueflagback, UpdateUserID=:updateUserID WHERE productionlineid=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } oracleTrConn.Commit(); } catch (Exception ex) { oracleTrConn.Rollback(); throw ex; } finally { // 释放资源 if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return returnRows; } #endregion #region 班次配置 /// /// 保存班次配置信息 /// /// 班次配置实体 /// 班次配置明细table /// 用户基本信息 /// >0 保存成功 else 失败 /// /// 作者 日期 内容 /// 冯雪 2014-9-24 新建 /// public static int SaveClassesSetting(ClassesSettingEntity setEntity, DataTable dtClassesSetting, SUserInfo sUserInfo) { int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); int classesSettingID = 0; if (setEntity.ClassesSettingID == 0) { // 查询新插入的生产数据ID string sqlText = "SELECT SEQ_PC_ClassesSetting_ID.NEXTVAL FROM dual"; classesSettingID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText)); // 插入班次配置表 string sqlString1 = " 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[] parmeters1 = new OracleParameter[] { new OracleParameter(":ClassesSettingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input), new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":userID",OracleDbType.Int32,setEntity.UserID,ParameterDirection.Input), new OracleParameter(":userCode",OracleDbType.Varchar2,setEntity.UserCode,ParameterDirection.Input), new OracleParameter(":AccountDate",OracleDbType.Date,setEntity.AccountDate,ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,setEntity.Remarks,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), }; returnRows = oracleTrConn.ExecuteNonQuery(sqlString1, parmeters1); } else { classesSettingID = setEntity.ClassesSettingID; // 删除以前的班次 string sqlDelete = "DELETE FROM TP_PC_ClassesDetail WHERE ClassesSettingID=:ClassesSettingID"; OracleParameter[] parmeters1 = new OracleParameter[] { new OracleParameter(":ClassesSettingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input), }; returnRows = oracleTrConn.ExecuteNonQuery(sqlDelete, parmeters1); } #region 保存班次配置明细信息 foreach (DataRow dataRow in dtClassesSetting.Rows) { if (dataRow["UJobsId"].ToString() == "" && dataRow["StaffCode"].ToString() == "") { continue; } if (dataRow.RowState == DataRowState.Deleted) { continue; } string sqlInsertString = "INSERT INTO TP_PC_ClassesDetail " + " (ClassesSettingID,AccountDate,userid,usercode,ujobsid,staffid,sjobsid,staffstatus," + " remarks,accountid,createtime,createuserid,UpdateUserID,UJobsNum) " + "VALUES (:settingID,:AccountDate,:userid,:usercode,:ujobsid,:staffid,:sjobsid,:staffstatus," + " :remarks,:accountid,sysdate,:createuserid,:updateUserID,:uJobsNum)"; int UJobsNum = 1;//默认一个 if (dtClassesSetting.Select("ujobsid=" + dataRow["UJobsId"]).Length > 0) { UJobsNum = dtClassesSetting.Select("ujobsid=" + dataRow["UJobsId"]).Length; } OracleParameter[] oracleParameter = new OracleParameter[] { new OracleParameter(":settingID",OracleDbType.Int32,classesSettingID,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32,dataRow["userid"].ToString(),ParameterDirection.Input), new OracleParameter(":usercode",OracleDbType.Varchar2,dataRow["usercode"].ToString(),ParameterDirection.Input), new OracleParameter(":ujobsid",OracleDbType.Int32,dataRow["UJobsId"].ToString(),ParameterDirection.Input), new OracleParameter(":staffid",OracleDbType.Int32,dataRow["staffid"].ToString(),ParameterDirection.Input), new OracleParameter(":sjobsid",OracleDbType.Int32,dataRow["Jobs"].ToString(),ParameterDirection.Input), new OracleParameter(":staffstatus",OracleDbType.Int32,dataRow["staffstatus"].ToString(),ParameterDirection.Input), new OracleParameter(":remarks",OracleDbType.Varchar2,dataRow["remarks"].ToString(),ParameterDirection.Input), new OracleParameter(":AccountDate",OracleDbType.Date,setEntity.AccountDate,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(":uJobsNum",OracleDbType.Decimal,UJobsNum,ParameterDirection.Input) }; returnRows = oracleTrConn.ExecuteNonQuery(sqlInsertString, oracleParameter); } #endregion oracleTrConn.Commit(); oracleTrConn.Disconnect(); return returnRows; } catch (Exception ex) { oracleTrConn.Rollback(); oracleTrConn.Disconnect(); throw ex; } finally { // 释放资源 if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } } #endregion /// /// 保存生产线 /// /// 生产线实体 /// 用户基本信息 /// public static int SaveProductionLine2(ProductionLineEntity productionLine, SUserInfo sUserInfo) { int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { #region 验证工序个数 if (DataManager.LicDataSet != null) { int licCount = Convert.ToInt32(DataManager.LicDataSet.Tables["Info"].Rows[0]["PFNum"]); if (licCount > -1) { int pNum = Convert.ToInt32(oracleTrConn.GetSqlResultToObj("select count(*) from tp_pc_procedure p where p.valueflag = '1' and p.productionlineid <> " + productionLine.ProductionLineID)); foreach (ProcedureEntity procedure in productionLine.ProcedureList) { if(procedure.ProcedureState != 2) { pNum++; } } if (pNum > licCount) { oracleTrConn.Rollback(); return -10; } } } #endregion #region 新建生产线 if (productionLine.ProductionLineID == 0) { #region 判断编码是否重复 string sqlText = " SELECT ProductionLineID " + " FROM TP_PC_ProductionLine " + " WHERE " + " AccountID=:accountID " + " AND ProductionLineCode=:productionLineCode"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":productionLineCode",productionLine.ProductionLineCode), }; string returnCode = oracleTrConn.GetSqlResultToStr(sqlText, paras); if (!string.IsNullOrEmpty(returnCode)) { return Constant.RETURN_IS_EXIST; } #endregion #region 查询新ID sqlText = "SELECT SEQ_PC_PRODUCTIONLINE_ID.nextval FROM dual"; productionLine.ProductionLineID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText)); #endregion #region 插入生产线 sqlText = "INSERT INTO TP_PC_ProductionLine (ProductionLineID," + "ProductionLineCode," + "ProductionLineName," + "FlowXML," + "Remarks," + "AccountID," + "CreateUserID," + "UpdateUserID)" + " VALUES(:ProductionLineID," + ":productionLineCode," + ":productionLineName," + ":flowXML," + ":remarks," + ":accountID," + ":createUserID," + ":updateUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":ProductionLineID",productionLine.ProductionLineID), new OracleParameter(":productionLineCode",productionLine.ProductionLineCode), new OracleParameter(":productionLineName",productionLine.ProductionLineName), new OracleParameter(":flowXML", System.Text.Encoding.UTF8.GetBytes(productionLine.FlowXML)), new OracleParameter(":remarks",productionLine.Remarks), new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":createUserID",sUserInfo.UserID), new OracleParameter(":updateUserID",sUserInfo.UserID), }; foreach (OracleParameter item in paras) { if (item.Value + "" == "") { item.Value = System.DBNull.Value; } } returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion //#region 查询新ID //sqlText = "SELECT SEQ_PC_PRODUCTIONLINE_ID.currval FROM dual"; //productionLine.ProductionLineID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText)); //#endregion #region 插入工序 foreach (ProcedureEntity procedure in productionLine.ProcedureList) { sqlText = "SELECT SEQ_PC_PROCEDURE_PROCEDUREID.Nextval FROM dual"; procedure.ProcedureID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText)); sqlText = "INSERT INTO TP_PC_Procedure (" + "ProcedureID," + "NodeNo," + "ProductionLineID," + "ProcedureCode," + "ProcedureName," + "ProcedureModel," + "ModelType," + "NodeType," + "MustFlag," + "CollectType," + "PieceType," + "IsSpecialRework," + "IsSemireWork," + "OrganizationID," + "Remarks," + "AccountID," + "CreateUserID," + "UpdateUserID,misspriority,displayno,UNDOFLAG,DeliverType,BarCodePrintCopies,BarCodeFlag)" + " VALUES(" + ":procedureID," + ":nodeNo," + ":productionLineID," + ":procedureCode," + ":procedureName," + ":procedureModel," + ":ModelType," + ":NodeType," + ":mustFlag," + ":collectType," + ":pieceType," + ":isSpecialRework," + ":isSemireWork," + ":organizationID," + ":remarks," + ":accountID," + ":createUserID," + ":updateUserID,:misspriority,:displayno,:UNDOFLAG,:DeliverType,:BarCodePrintCopies,:BarCodeFlag" + ")"; paras = new OracleParameter[] { new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureCode",procedure.ProcedureCode), new OracleParameter(":procedureName",procedure.ProcedureName), new OracleParameter(":procedureModel",procedure.ProcedureModel), new OracleParameter(":ModelType",procedure.ModelType), new OracleParameter(":NodeType",procedure.NodeType), new OracleParameter(":mustFlag",procedure.MustFlag), new OracleParameter(":collectType",procedure.CollectType), new OracleParameter(":pieceType",procedure.PieceType), new OracleParameter(":isSpecialRework",procedure.IsSpecialRework), new OracleParameter(":isSemireWork",procedure.IsSemireWork), new OracleParameter(":organizationID",procedure.OrganizationID), new OracleParameter(":remarks",procedure.Remarks), new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":createUserID",sUserInfo.UserID), new OracleParameter(":updateUserID",sUserInfo.UserID), new OracleParameter(":misspriority",procedure.MissPriority), new OracleParameter(":displayno",procedure.DisplayNo), new OracleParameter(":UNDOFLAG",procedure.UnDo), new OracleParameter(":DeliverType",procedure.DeliverType), new OracleParameter(":BarCodePrintCopies",procedure.BarCodePrintCopies), new OracleParameter(":BarCodeFlag",procedure.BarCodeFlag), }; foreach (OracleParameter item in paras) { if (item.Value + "" == "") { item.Value = System.DBNull.Value; } } returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion } #endregion #region 编辑生产线 else { #region 校验时间戳 // todo string sql = "SELECT OPTimeStamp FROM TP_PC_ProductionLine" + " WHERE ProductionLineID = " + productionLine.ProductionLineID + " and OPTimeStamp = :OPTimeStamp"; OracleParameter[] parmetersSql = new OracleParameter[] { new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, productionLine.OPTimeStamp, ParameterDirection.Input), }; DataSet returnDataset = oracleTrConn.GetSqlResultToDs(sql, parmetersSql); if (returnDataset != null && returnDataset.Tables[0].Rows.Count == 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); return Constant.RETURN_IS_DATACHANGED; } #endregion #region 判断编码是否重复 string sqlText = " SELECT ProductionLineID " + " FROM TP_PC_ProductionLine " + " WHERE " + " AccountID=:accountID " + " AND ProductionLineCode=:productionLineCode" + " AND ProductionLineID <> :productionLineID"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":productionLineCode",productionLine.ProductionLineCode), new OracleParameter(":productionLineID",productionLine.ProductionLineID), }; string returnCode = oracleTrConn.GetSqlResultToStr(sqlText, paras); if (!string.IsNullOrEmpty(returnCode)) { return Constant.RETURN_IS_EXIST; } #endregion #region 编辑生产线 sqlText = "UPDATE TP_PC_ProductionLine SET " + "ProductionLineName=:productionLineName," + "FlowXML=:flowXML," + "Remarks=:remarks," + "UpdateTime=sysdate, " + "UpdateUserID=:updateUserID " + " WHERE ProductionLineID=:productionLineID"; paras = new OracleParameter[] { new OracleParameter(":productionLineName",productionLine.ProductionLineName), new OracleParameter(":flowXML", OracleDbType.Blob,System.Text.Encoding.UTF8.GetBytes(productionLine.FlowXML),ParameterDirection.Input), new OracleParameter(":remarks",productionLine.Remarks), new OracleParameter(":updateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":productionLineID", OracleDbType.Int32, productionLine.ProductionLineID,ParameterDirection.Input) }; //foreach (OracleParameter item in paras) //{ // if (item.Value + "" == "") // { // item.Value = System.DBNull.Value; // } //} returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 编辑工序 foreach (ProcedureEntity procedure in productionLine.ProcedureList) { //if (procedure.EditingAddFlag) if (procedure.ProcedureState == 1) { #region 编辑时新添加的节点 sqlText = "SELECT SEQ_PC_PROCEDURE_PROCEDUREID.Nextval FROM dual"; procedure.ProcedureID = int.Parse(oracleTrConn.GetSqlResultToStr(sqlText)); sqlText = "INSERT INTO TP_PC_Procedure (" + "ProcedureID," + "NodeNo," + "ProductionLineID," + "ProcedureCode," + "ProcedureName," + "ProcedureModel," + "ModelType," + "NodeType," + "MustFlag," + "CollectType," + "PieceType," + "IsSpecialRework," + "IsSemireWork," + "OrganizationID," + "Remarks," + "AccountID," + "CreateUserID," + "UpdateUserID,misspriority,displayno,UNDOFLAG,DeliverType,BarCodePrintCopies,BarCodeFlag)" + " VALUES(" + ":procedureID," + ":nodeNo," + ":productionLineID," + ":procedureCode," + ":procedureName," + ":procedureModel," + ":ModelType," + ":NodeType," + ":mustFlag," + ":collectType," + ":pieceType," + ":isSpecialRework," + ":isSemireWork," + ":organizationID," + ":remarks," + ":accountID," + ":createUserID," + ":updateUserID,:misspriority,:displayno,:UNDOFLAG,:DeliverType,:BarCodePrintCopies,:BarCodeFlag" + ")"; paras = new OracleParameter[] { new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureCode",procedure.ProcedureCode), new OracleParameter(":procedureName",procedure.ProcedureName), new OracleParameter(":procedureModel",procedure.ProcedureModel), new OracleParameter(":ModelType",procedure.ModelType), new OracleParameter(":NodeType",procedure.NodeType), new OracleParameter(":mustFlag",procedure.MustFlag), new OracleParameter(":collectType",procedure.CollectType), new OracleParameter(":pieceType",procedure.PieceType), new OracleParameter(":isSpecialRework",procedure.IsSpecialRework), new OracleParameter(":isSemireWork",procedure.IsSemireWork), new OracleParameter(":organizationID",procedure.OrganizationID), new OracleParameter(":remarks",procedure.Remarks), new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":createUserID",sUserInfo.UserID), new OracleParameter(":updateUserID",sUserInfo.UserID), new OracleParameter(":misspriority",procedure.MissPriority), new OracleParameter(":displayno",procedure.DisplayNo), new OracleParameter(":UNDOFLAG",procedure.UnDo), new OracleParameter(":DeliverType",procedure.DeliverType), new OracleParameter(":BarCodePrintCopies",procedure.BarCodePrintCopies), new OracleParameter(":BarCodeFlag",procedure.BarCodeFlag), }; #endregion } else if (procedure.ProcedureState == 2) //后添加的,删除后标用节点 { #region 编辑 sqlText = "update TP_PC_Procedure set valueflag=0 " + " WHERE ProcedureID=:procedureID " + " AND ProductionLineID=:productionLineID"; paras = new OracleParameter[] { new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":productionLineID",productionLine.ProductionLineID), }; #endregion } else { #region 编辑 sqlText = "UPDATE TP_PC_Procedure SET " + " ProcedureName=:procedureName," + " ProcedureCode=:ProcedureCode," + " ModelType=:modelType," + " MustFlag=:mustFlag," + " CollectType=:collectType," + " OrganizationID=:organizationID," + " NodeType=:nodeType," + " PieceType=:pieceType," + " IsSpecialRework=:isSpecialRework," + " IsSemireWork=:isSemireWork," + " PrintType=:printType," //xuwei add 2019-11-20 + " NodeNo=:nodeNo," + " Remarks=:remarks," + " updateUserID=:updateUserID," + " UpdateTime=sysdate," + " misspriority=:misspriority," + " displayno=:displayno," + " UNDOFLAG=:UNDOFLAG," + " DeliverType=:DeliverType," + " BarCodePrintCopies=:BarCodePrintCopies," + " BarCodeFlag=:BarCodeFlag" + " WHERE NodeNo=:nodeNo " + " AND ProductionLineID=:productionLineID"; paras = new OracleParameter[] { new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureName",procedure.ProcedureName), new OracleParameter(":ProcedureCode",procedure.ProcedureCode), new OracleParameter(":modelType",procedure.ModelType), new OracleParameter(":mustFlag",procedure.MustFlag), new OracleParameter(":collectType",procedure.CollectType), new OracleParameter(":organizationID",procedure.OrganizationID), new OracleParameter(":nodeType",procedure.NodeType), new OracleParameter(":pieceType",procedure.PieceType), new OracleParameter(":isSpecialRework",procedure.IsSpecialRework), new OracleParameter(":isSemireWork",procedure.IsSemireWork), new OracleParameter(":printType",procedure.PrintType),//xuwei add 2019-11-20 new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":remarks",procedure.Remarks), new OracleParameter(":updateUserID",sUserInfo.UserID), new OracleParameter(":misspriority",procedure.MissPriority), new OracleParameter(":displayno",procedure.DisplayNo), new OracleParameter(":UNDOFLAG",procedure.UnDo), new OracleParameter(":DeliverType",procedure.DeliverType), new OracleParameter(":BarCodePrintCopies",procedure.BarCodePrintCopies), new OracleParameter(":BarCodeFlag",procedure.BarCodeFlag), }; #endregion } foreach (OracleParameter item in paras) { if (item.Value + "" == "") { item.Value = System.DBNull.Value; } } returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 先删除生产工序流程明细 然后再插入 sqlText = "DELETE TP_PC_ProcedureFlow WHERE ProductionLineID=:productionLineID"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除对应产品 然后再插入 sqlText = "DELETE TP_PC_ProcedureGoods WHERE ProductionLineID=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除对应工号 然后再插入 sqlText = "DELETE TP_PC_ProcedureUser WHERE ProductionLineID=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除对应缺陷 然后再插入 sqlText = "DELETE TP_PC_ProcedureDefect WHERE ProductionLineID=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除缺陷对应工序工种表 然后再插入 sqlText = "DELETE TP_PC_DefectProcedureJobs WHERE ProductionLineID=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除对应窑炉 然后再插入 sqlText = "DELETE TP_PC_ProcedureKiln WHERE ProductionLineID=:productionLineID"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion } #endregion #region 插入生产工序属性等信息 foreach (ProcedureEntity procedure in productionLine.ProcedureList) { string sqlText; OracleParameter[] paras; #region 插入生产工序流程明细 if (procedure.ProcedureFlowDetailList != null) { foreach (ProcedureFlowEntity flowDetail in procedure.ProcedureFlowDetailList) { ProcedureEntity procedureWhere = productionLine.ProcedureList.Where(p => p.NodeNo == flowDetail.ArriveNodeNo).SingleOrDefault(); // 没有找到对应节点的ID if (procedureWhere == null) { return Constant.INT_IS_ZERO; } else { flowDetail.ArriveProcedureID = procedureWhere.ProcedureID; } sqlText = "INSERT INTO TP_PC_ProcedureFlow (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "FlowFlag," + "ArriveProcedureID," + "ArriveNodeNo" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":flowFlag," + ":arriveProcedureID," + ":arriveNodeNo" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":flowFlag",flowDetail.FlowFlag), new OracleParameter(":arriveProcedureID",flowDetail.ArriveProcedureID), new OracleParameter(":arriveNodeNo",flowDetail.ArriveNodeNo), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } } #endregion #region 插入生产工序对应产品表 foreach (DataRow row in procedure.ProcedureGoodsTable.Rows) { sqlText = "INSERT INTO TP_PC_ProcedureGoods (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "GOODSID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":goodsID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":goodsID",OracleDbType.Int32,row["goodsID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入生产工序对应工号表 foreach (DataRow row in procedure.ProcedureUserTable.Rows) { sqlText = "INSERT INTO TP_PC_ProcedureUser (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "UserID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":userID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":userID",OracleDbType.Int32,row["userID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入生产工序对应缺陷表 foreach (DataRow row in procedure.ProcedureDefectTable.Rows) { if (row["DefectID"] == DBNull.Value) { continue; } sqlText = "INSERT INTO TP_PC_ProcedureDefect (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "DefectID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":defectID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":defectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入缺陷对应工序工种表 foreach (DataRow row in procedure.DefectProcedureJobsTable.Rows) { if (row["DefectID"] == DBNull.Value || row["jobsID"] == DBNull.Value) { continue; } sqlText = "INSERT INTO TP_PC_DefectProcedureJobs (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "DefectID," + "JobsID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":DefectID," + ":jobsID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":DefectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input), new OracleParameter(":jobsID",OracleDbType.Int32,row["jobsID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入生产工序对应窑炉 if (procedure.ProcedureKilnTable != null) { foreach (DataRow row in procedure.ProcedureKilnTable.Rows) { sqlText = "INSERT INTO tp_pc_procedurekiln (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "KilnID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":kilnID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",productionLine.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":kilnID",OracleDbType.Int32,row["kilnID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } } #endregion } #endregion oracleTrConn.Commit(); } catch (Exception ex) { oracleTrConn.Rollback(); throw ex; } finally { // 释放资源 if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return returnRows; } /// /// 保存工序节点 /// /// 工序实体 /// 用户基本信息 /// public static int SaveProcedureInfo(ProcedureEntity procedure, SUserInfo sUserInfo) { int returnRows = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { #region 校验时间戳 // todo string sql = "SELECT OPTimeStamp FROM TP_PC_Procedure" + " WHERE ProcedureID = " + procedure.ProcedureID + " and OPTimeStamp = :OPTimeStamp and productionlineid=:productionlineid and nodeno=:nodeno"; OracleParameter[] parmetersSql = new OracleParameter[] { new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, procedure.OPTimeStamp, ParameterDirection.Input), new OracleParameter(":productionlineid", OracleDbType.Int32, procedure.ProductionLineID, ParameterDirection.Input), new OracleParameter(":nodeno", OracleDbType.Int32, procedure.NodeNo, ParameterDirection.Input), }; DataSet returnDataset = oracleTrConn.GetSqlResultToDs(sql, parmetersSql); if (returnDataset != null && returnDataset.Tables[0].Rows.Count == 0) { oracleTrConn.Commit(); oracleTrConn.Disconnect(); return Constant.RETURN_IS_DATACHANGED; } #endregion string sqlText = ""; #region 更新工序信息 sqlText = "UPDATE TP_PC_Procedure SET " + " ProcedureName=:procedureName," + " ProcedureCode=:ProcedureCode," + " CollectType=:collectType," + " OrganizationID=:organizationID," + " NodeType=:nodeType," + " PieceType=:pieceType," + " IsSpecialRework=:isSpecialRework," + " IsSemireWork=:isSemireWork," //xuwei add 2019-10-14 + " PrintType=:printType," //xuwei add 2019-11-20 + " NodeNo=:nodeNo," + " Remarks=:remarks," + " updateUserID=:updateUserID," + " UpdateTime=sysdate," + " misspriority=:misspriority," + " displayno=:displayno," + " UNDOFLAG=:UNDOFLAG," + " DeliverType=:DeliverType," + " MustFlag=:mustFlag," + " BarCodePrintCopies=:BarCodePrintCopies," + " BarCodeFlag=:BarCodeFlag" + " WHERE NodeNo=:nodeNo " + " AND ProductionLineID=:productionLineID"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":productionLineID",procedure.ProductionLineID), new OracleParameter(":procedureName",procedure.ProcedureName), new OracleParameter(":ProcedureCode",procedure.ProcedureCode), new OracleParameter(":collectType",procedure.CollectType), new OracleParameter(":organizationID",procedure.OrganizationID), new OracleParameter(":nodeType",procedure.NodeType), new OracleParameter(":pieceType",procedure.PieceType), new OracleParameter(":isSpecialRework",procedure.IsSpecialRework), new OracleParameter(":isSemireWork",procedure.IsSemireWork),//xuwei add 2019-10-14 new OracleParameter(":printType",procedure.PrintType),//xuwei add 2019-11-20 new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":remarks",procedure.Remarks), new OracleParameter(":updateUserID",sUserInfo.UserID), new OracleParameter(":misspriority",procedure.MissPriority), new OracleParameter(":displayno",procedure.DisplayNo), new OracleParameter(":UNDOFLAG",procedure.UnDo), new OracleParameter(":DeliverType",procedure.DeliverType), new OracleParameter(":mustFlag",procedure.MustFlag), new OracleParameter(":BarCodePrintCopies",procedure.BarCodePrintCopies), new OracleParameter(":BarCodeFlag",procedure.BarCodeFlag), }; foreach (OracleParameter item in paras) { if (item.Value + "" == "") { item.Value = System.DBNull.Value; } } returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion paras = new OracleParameter[] { new OracleParameter(":productionLineID",procedure.ProductionLineID), new OracleParameter(":nodeno",procedure.NodeNo), new OracleParameter(":procedureid",procedure.ProcedureID), }; #region 删除对应产品 然后再插入 sqlText = "DELETE TP_PC_ProcedureGoods WHERE ProductionLineID=:productionLineID and NodeNo=:nodeno and ProcedureID=:procedureid"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除对应工号 然后再插入 sqlText = "DELETE TP_PC_ProcedureUser WHERE ProductionLineID=:productionLineID and NodeNo=:nodeno and ProcedureID=:procedureid"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除对应缺陷 然后再插入 sqlText = "DELETE TP_PC_ProcedureDefect WHERE ProductionLineID=:productionLineID and NodeNo=:nodeno and ProcedureID=:procedureid"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除缺陷对应工序工种表 然后再插入 sqlText = "DELETE TP_PC_DefectProcedureJobs WHERE ProductionLineID=:productionLineID and NodeNo=:nodeno and ProcedureID=:procedureid"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 删除对应窑炉 然后再插入 sqlText = "DELETE TP_PC_ProcedureKiln WHERE ProductionLineID=:productionLineID and NodeNo=:nodeno and ProcedureID=:procedureid"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); #endregion #region 插入生产工序对应产品表 foreach (DataRow row in procedure.ProcedureGoodsTable.Rows) { sqlText = "INSERT INTO TP_PC_ProcedureGoods (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "GOODSID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":goodsID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",procedure.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":goodsID",OracleDbType.Int32,row["goodsID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入生产工序对应工号表 foreach (DataRow row in procedure.ProcedureUserTable.Rows) { sqlText = "INSERT INTO TP_PC_ProcedureUser (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "UserID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":userID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",procedure.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":userID",OracleDbType.Int32,row["userID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入生产工序对应缺陷表 foreach (DataRow row in procedure.ProcedureDefectTable.Rows) { if (row["DefectID"] == DBNull.Value) { continue; } sqlText = "INSERT INTO TP_PC_ProcedureDefect (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "DefectID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":defectID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",procedure.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":defectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入缺陷对应工序工种表 foreach (DataRow row in procedure.DefectProcedureJobsTable.Rows) { if (row["DefectID"] == DBNull.Value || row["jobsID"] == DBNull.Value) { continue; } sqlText = "INSERT INTO TP_PC_DefectProcedureJobs (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "DefectID," + "JobsID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":DefectID," + ":jobsID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",procedure.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":DefectID",OracleDbType.Int32,row["DefectID"],ParameterDirection.Input), new OracleParameter(":jobsID",OracleDbType.Int32,row["jobsID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion #region 插入生产工序对应窑炉 foreach (DataRow row in procedure.ProcedureKilnTable.Rows) { sqlText = "INSERT INTO tp_pc_procedurekiln (" + "ProductionLineID," + "ProcedureID," + "NodeNo," + "KilnID," + "CreateUserID" + ")" + " VALUES(" + ":productionLineID," + ":procedureID," + ":nodeNo," + ":kilnID," + ":createUserID" + ")"; paras = new OracleParameter[] { new OracleParameter(":productionLineID",procedure.ProductionLineID), new OracleParameter(":procedureID",procedure.ProcedureID), new OracleParameter(":nodeNo",procedure.NodeNo), new OracleParameter(":kilnID",OracleDbType.Int32,row["kilnID"],ParameterDirection.Input), new OracleParameter(":createUserID",sUserInfo.UserID), }; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, paras); } #endregion oracleTrConn.Commit(); } catch (Exception ex) { oracleTrConn.Rollback(); throw ex; } finally { // 释放资源 if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return returnRows; } /// /// 注浆变更 /// /// 登录用户信息 /// 原条码 /// 新条码 /// 备注 /// 操作结果 public static ServiceResultEntity SetFPM2105Data(SUserInfo user, int? groutingLineID, int? groutingDailyID, string groutingDailyDetailIDs, DateTime groutingDate, string groutingUserCode, int? goodsID) { IDBTransaction tran = null; try { ServiceResultEntity sre = new ServiceResultEntity(); tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); OracleParameter[] paras = new OracleParameter[] { new OracleParameter("in_AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input), new OracleParameter("in_UserID", OracleDbType.Int32, user.UserID, ParameterDirection.Input), new OracleParameter("in_GroutingLineID", OracleDbType.Int32, groutingLineID, ParameterDirection.Input), new OracleParameter("in_GroutingDailyID", OracleDbType.Int32, groutingDailyID, ParameterDirection.Input), new OracleParameter("in_GroutingDailyDetailIDs", OracleDbType.Varchar2,groutingDailyDetailIDs, ParameterDirection.Input), new OracleParameter("in_GroutingDate", OracleDbType.Date, groutingDate, ParameterDirection.Input), new OracleParameter("in_GroutingUserCode", OracleDbType.Varchar2, groutingUserCode, ParameterDirection.Input), new OracleParameter("in_GoodsID", OracleDbType.Int32, goodsID, ParameterDirection.Input), new OracleParameter("out_Result", OracleDbType.Varchar2, 100, null, ParameterDirection.Output), new OracleParameter("out_Message", OracleDbType.Varchar2, 100, null, ParameterDirection.Output), }; DataSet ds = tran.ExecStoredProcedure("PRO_PM_ReplacedGroutingInfo", paras); string out_Result = paras[8].Value.ToString(); if (out_Result != "0") { sre.Status = Constant.ServiceResultStatus.Other; switch (out_Result) { case "1.0": sre.OtherStatus = 1; sre.Message = "当前用户没有此成型线的操作权限"; break; case "1.1": sre.OtherStatus = 2; sre.Message = "此注浆日报中的条码已交坯不能修改成型工号。"; break; case "1.2": sre.OtherStatus = 3; sre.Message = "工号[" + groutingUserCode + "]不存在或不是生产工号"; break; case "1.3": sre.OtherStatus = 7; sre.Message = "工号[" + groutingUserCode + "]在" + groutingDate.Date + "没有班次配置。"; break; case "2.1": sre.OtherStatus = 4; sre.Message = "此产品不存在。"; break; default: break; } } 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(); } } } /// /// 注浆变更 /// /// 登录用户信息 /// 原条码 /// 新条码 /// 备注 /// 操作结果 public static ServiceResultEntity SetFPM2105CancelBarCodeData(SUserInfo user, string BarCodes) { IDBTransaction tran = null; try { ServiceResultEntity sre = new ServiceResultEntity(); tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); string[] subBarCode = BarCodes.Split(','); string sql = ""; string returnValue = ""; bool isError = false; for (int i = 0; i < subBarCode.Length; i++) { if (subBarCode[i] != "") { sql = "select DELIVERFLAG from TP_PM_GroutingDailyDetail where barcode='" + subBarCode[i] + "'"; returnValue = tran.GetSqlResultToStr(sql); if (returnValue == "1") { sre.Status = Constant.ServiceResultStatus.Other; sre.Message = "条码[" + subBarCode[i] + "]已经交坯，不能取消绑定"; isError = true; break; } sql = "update TP_PM_GroutingDailyDetail set barcode='' where barcode='" + subBarCode[i] + "'"; tran.ExecuteNonQuery(sql); sql = "delete from TP_PM_UsedBarCode where barcode='" + subBarCode[i] + "'"; tran.ExecuteNonQuery(sql); } } if (isError) //有错误 { tran.Rollback(); } else { 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(); } } } } }