/******************************************************************************* * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential * 类的信息： * 1.程序名称：PCModuleLogicDAL.cs * 2.功能描述：生产配置数据库访问类（插入、修改、删除） * 编辑履历： * 作者日期版本修改内容 * 陈冰 2014/09/3 1.00 新建 *******************************************************************************/ using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Linq; using Dongke.IBOSS.PRD.Basics.BaseResources; using Dongke.IBOSS.PRD.Basics.DataAccess; using Dongke.IBOSS.PRD.Basics.Library; using Dongke.IBOSS.PRD.Service.DataModels; using Dongke.IBOSS.PRD.Service.SAPHegiiDataService; using Dongke.IBOSS.PRD.WCF.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels.PCModule; using Newtonsoft.Json; using Newtonsoft.Json.Linq; 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," + "P_Name," + "ProcedureModel," + "ModelType," + "NodeType," + "MustFlag," + "CollectType," + "PieceType," + "IsSpecialRework," + "IsSemireWork," + "OrganizationID," + "Remarks," + "AccountID," + "CreateUserID," + "UpdateUserID)" + " VALUES(" + ":procedureID," + ":nodeNo," + ":productionLineID," + ":procedureCode," + ":procedureName," + ":pName," + ":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(":pName",procedure.PName), 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," + "P_Name," + "ProcedureModel," + "ModelType," + "NodeType," + "MustFlag," + "CollectType," + "PieceType," + "IsSpecialRework," + "IsSemireWork," + "OrganizationID," + "Remarks," + "AccountID," + "CreateUserID," + "UpdateUserID)" + " VALUES(" + ":procedureID," + ":nodeNo," + ":productionLineID," + ":procedureCode," + ":procedureName," + ":pName," + ":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(":pName",procedure.PName), 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," + " P_Name=:pName," + " 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(":pName",procedure.PName), 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 ///

/// 保存生产线 xuwei modify 2019-11-22 ///

/// 生产线实体 /// 用户基本信息 /// 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," + "P_Name," + "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," + ":pName," + ":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(":pName",procedure.PName), 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," + "P_Name," + "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," + ":pName," + ":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(":pName",procedure.PName), 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," + " P_Name=:pName," + " 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 + " IsGlazeChange=:IsGlazeChange," //xuwei add 2020-01-02 + " 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(":pName",procedure.PName), 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(":IsGlazeChange",procedure.IsGlazeChange),//xuwei add 2020-01-02 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 #region 删除半检产品等级然后再插入 xuwei add 2019-12-12 sqlText = "DELETE TP_PC_PROCEDUREGOODSLEVEL 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 #region 插入生产工序对应半检产品等级 xuwei add 2019-12-12 if (!string.IsNullOrEmpty(procedure.SemiGoodsLevel)) { string[] ids = procedure.SemiGoodsLevel.Split(','); string sqlStr = @" BEGIN "; for (int i = 0; i < ids.Length; i++) { sqlStr += $@" INSERT INTO TP_PC_PROCEDUREGOODSLEVEL ( PRODUCTIONLINEID, PROCEDUREID, GOODSLEVELTYPEID) VALUES (:PRODUCTIONLINEID, :PROCEDUREID, {ids[i]}); "; } sqlStr += "END;"; oracleTrConn.ExecuteNonQuery( sqlStr, new OracleParameter[] { new OracleParameter(":PRODUCTIONLINEID",productionLine.ProductionLineID), new OracleParameter(":PROCEDUREID",procedure.ProcedureID) } ); } #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," + " P_Name=:PName," + " 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 + " IsGlazeChange=:IsGlazeChange," //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," + " FINISHEDCHECKPROCEDUREID=:FINISHEDCHECKPROCEDUREID," + " 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(":PName",procedure.PName), 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(":IsGlazeChange",procedure.IsGlazeChange),//xuwei add 2020-01-02 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), new OracleParameter(":FINISHEDCHECKPROCEDUREID",procedure.FinishedCheckProcedureID),//xiacm add 2022-10-12 }; foreach (OracleParameter item in paras) { if (item.Value + "" == "") { item.Value = System.DBNull.Value; } //成检工序id xiacm 2022-10-12 if (item.ParameterName== ":FINISHEDCHECKPROCEDUREID"&& Convert.ToInt32( item.Value)==-1) { 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 删除半检产品等级然后再插入 xuwei add 2019-12-12 sqlText = "DELETE TP_PC_PROCEDUREGOODSLEVEL WHERE PRODUCTIONLINEID = :productionLineID and ProcedureID=:procedureid"; returnRows += oracleTrConn.ExecuteNonQuery(sqlText, new OracleParameter[] { new OracleParameter(":productionLineID",procedure.ProductionLineID), new OracleParameter(":procedureid",procedure.ProcedureID), }); #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 #region 插入生产工序对应半检产品等级 xuwei add 2019-12-12 if (!string.IsNullOrEmpty(procedure.SemiGoodsLevel)) { string[] ids = procedure.SemiGoodsLevel.Split(','); string sqlStr = @" BEGIN "; for (int i = 0; i < ids.Length; i++) { sqlStr += $@" INSERT INTO TP_PC_PROCEDUREGOODSLEVEL ( PRODUCTIONLINEID, PROCEDUREID, GOODSLEVELTYPEID) VALUES (:PRODUCTIONLINEID, :PROCEDUREID, {ids[i]}); "; } sqlStr += "END;"; oracleTrConn.ExecuteNonQuery( sqlStr, new OracleParameter[] { new OracleParameter(":PRODUCTIONLINEID",procedure.ProductionLineID), new OracleParameter(":PROCEDUREID",procedure.ProcedureID) } ); } #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); string sql = @" SELECT T.BARCODE, T.GOODSID, T.MATERIALCODE AS OLDMATNR FROM TP_PM_GROUTINGDAILYDETAIL T WHERE T.BARCODE IS NOT NULL AND T.GROUTINGDAILYID = :GROUTINGDAILYID AND (:GROUTINGDAILYDETAILIDS IS NULL OR :GROUTINGDAILYDETAILIDS = '' OR INSTR(',' || :GROUTINGDAILYDETAILIDS || ',', ',' || T.GROUTINGDAILYDETAILID || ',') > 0) "; DataTable dtMatnr = tran.GetSqlResultToDt(sql, new OracleParameter[] { new OracleParameter(":GROUTINGDAILYID", OracleDbType.Int32, groutingDailyID, ParameterDirection.Input), new OracleParameter(":GROUTINGDAILYDETAILIDS", OracleDbType.Varchar2,groutingDailyDetailIDs, ParameterDirection.Input) }); #region 先查询sap数据 string barcode = ""; DataSet sapDataSet = new DataSet(); if (dtMatnr.Rows.Count > 0) { for (int i = 0; i < dtMatnr.Rows.Count; i++) { if (dtMatnr.Rows[i]["GOODSID"].ToString() != goodsID.ToString()) { if (i == 0) { barcode = dtMatnr.Rows[i]["BARCODE"].ToString(); } else { barcode = barcode + "," + dtMatnr.Rows[i]["BARCODE"].ToString(); } } } //查询变更的型号信息 OracleParameter[] paras1 = new OracleParameter[] { new OracleParameter("IN_BARCODE", OracleDbType.Varchar2, barcode, ParameterDirection.Input), new OracleParameter("IN_GOODSID", OracleDbType.Int32, goodsID, ParameterDirection.Input), new OracleParameter("IN_LOGOID", OracleDbType.Int32, 0, ParameterDirection.Input), new OracleParameter("OUT_RESULT", OracleDbType.RefCursor, null, ParameterDirection.Output), }; sapDataSet = tran.ExecStoredProcedure("PRO_SAP_HEGII_WORKDATA_BG_BIANGENG", paras1); } #endregion 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; } } //变更型号成功后 SAP报工数据同步，一正一负 if (out_Result == "0") { if (dtMatnr.Rows.Count > 0) { #region sap报工 if (sapDataSet != null && sapDataSet.Tables.Count > 0 && sapDataSet.Tables[0].Rows.Count > 0) { DataTable sapresultTable = sapDataSet.Tables[0]; //记录所有logid,先设置状态为Q，加完明细改为F List logids = new List(); DataTable dTable = new DataTable(); //获取总单datacode DataView dv = new DataView(sapresultTable); dTable = dv.ToTable(true, "DATACODE"); for (int j = 0; j < dTable.Rows.Count; j++) { //sap日志总单（不同节点） string sqlText = "SELECT SEQ_TSAP_DATALOG_BG.nextval FROM dual"; //判断有几个节点 20,30,40,50 #region 20节点 if (dTable.Rows[j]["DATACODE"].ToString() == "20") { int logid = int.Parse(tran.GetSqlResultToStr(sqlText)); //记录logid logids.Add(logid); #region log总单 sqlText = "INSERT INTO TSAP_HEGII_DATALOG_BG\n" + " (LOGID,\n" + " LOGTYPE,\n" + " BEGINTIME,\n" + " YYYYMMDD,\n" + " WORKCODE,\n" + " DATACODE,\n" + " DATASTUTS,\n" + " DATAMSG,\n" + " DATALOGID,\n" + " EXECUTEDATEBEGIN,\n" + " EXECUTEDATEEND,\n" + " REMARKS,\n" + " SAPGUID)\n" + " VALUES\n" + " (:LOGID,\n" + " '4',\n" + " SYSDATE,\n" + " TO_CHAR(SYSDATE, 'YYYYMMDD'),\n" + " '5000',\n" + " '20',\n" + " 'Q',\n" + " '',\n" + " :LOGID,\n" + " SYSDATE,\n" + " SYSDATE,\n" + " :REMARKS,\n" + " SYS_GUID())"; paras = new OracleParameter[] { new OracleParameter(":LOGID",logid), new OracleParameter(":REMARKS","注浆变更：新产品ID："+goodsID), }; int returnRows = tran.ExecuteNonQuery(sqlText, paras); #endregion #region 明细 DataTable table20 = sapresultTable.Copy(); DataRow[] drRow20 = table20.Select("DATACODE = 20"); foreach (DataRow row in drRow20) { sqlText = "INSERT INTO TSAP_HEGII_WORKDATA_BG\n" + " (YYYYMMDD,\n" + " WORKCODE,\n" + " DATACODE,\n" + " GOODSCODE,\n" + " SAPCODE,\n" + " USERCODE,\n" + " OUTPUTNUM,\n" + " SCRAPNUM,\n" + " CLEANUPNUM,\n" + " REPAIRNUM,\n" + " WORKSHOP,\n" + " LOGID,\n" + " TESTMOULDFLAG,\n" + " ZSCS,\n" + " CHARG)\n" + " VALUES\n" + " (TO_CHAR(SYSDATE, 'YYYYMMDD'),\n" + " '5000',\n" + " '20',\n" + " :GOODSCODE,\n" + " :SAPCODE,\n" + " :USERCODE,\n" + " :OUTPUTNUM,\n" + " :SCRAPNUM,\n" + " :CLEANUPNUM,\n" + " :REPAIRNUM,\n" + " :WORKSHOP,\n" + " :LOGID,\n" + " :TESTMOULDFLAG,\n" + " :ZSCS,\n" + " :CHARG)"; paras = new OracleParameter[] { new OracleParameter(":GOODSCODE",row["GOODSCODE"].ToString()), new OracleParameter(":SAPCODE",row["SAPCODE"].ToString()), new OracleParameter(":USERCODE",row["USERCODE"].ToString()), new OracleParameter(":OUTPUTNUM",row["OUTPUTNUM"].ToString()), new OracleParameter(":SCRAPNUM",row["SCRAPNUM"].ToString()), new OracleParameter(":CLEANUPNUM",row["CLEANUPNUM"].ToString()), new OracleParameter(":REPAIRNUM",row["REPAIRNUM"].ToString()), new OracleParameter(":WORKSHOP",row["WORKSHOP"].ToString()), new OracleParameter(":LOGID",logid), new OracleParameter(":TESTMOULDFLAG",row["TESTMOULDFLAG"].ToString()), new OracleParameter(":ZSCS",row["ZSCS"].ToString()), new OracleParameter(":CHARG",row["CHARG"].ToString()), }; returnRows += tran.ExecuteNonQuery(sqlText, paras); } #endregion } #endregion #region 30节点 else if (dTable.Rows[j]["DATACODE"].ToString() == "30") { int logid = int.Parse(tran.GetSqlResultToStr(sqlText)); //记录logid logids.Add(logid); sqlText = "INSERT INTO TSAP_HEGII_DATALOG_BG\n" + " (LOGID,\n" + " LOGTYPE,\n" + " BEGINTIME,\n" + " YYYYMMDD,\n" + " WORKCODE,\n" + " DATACODE,\n" + " DATASTUTS,\n" + " DATAMSG,\n" + " DATALOGID,\n" + " EXECUTEDATEBEGIN,\n" + " EXECUTEDATEEND,\n" + " REMARKS,\n" + " SAPGUID)\n" + " VALUES\n" + " (:LOGID,\n" + " '4',\n" + " SYSDATE,\n" + " TO_CHAR(SYSDATE, 'YYYYMMDD'),\n" + " 5000,\n" + " :DATACODE,\n" + " 'Q',\n" + " '',\n" + " :LOGID,\n" + " SYSDATE,\n" + " SYSDATE,\n" + " :REMARKS,\n" + " SYS_GUID())"; paras = new OracleParameter[] { new OracleParameter(":LOGID",logid), new OracleParameter(":DATACODE","30"), new OracleParameter(":REMARKS","注浆变更：新产品ID："+goodsID), }; int returnRows = tran.ExecuteNonQuery(sqlText, paras); #region 明细 DataTable table30 = sapresultTable.Copy(); DataRow[] drRow30 = table30.Select("DATACODE = 30"); foreach (DataRow row in drRow30) { sqlText = "INSERT INTO TSAP_HEGII_WORKDATA_BG\n" + " (YYYYMMDD,\n" + " WORKCODE,\n" + " DATACODE,\n" + " GOODSCODE,\n" + " SAPCODE,\n" + " USERCODE,\n" + " OUTPUTNUM,\n" + " SCRAPNUM,\n" + " CLEANUPNUM,\n" + " REPAIRNUM,\n" + " WORKSHOP,\n" + " LOGID,\n" + " TESTMOULDFLAG,\n" + " ZSCS,\n" + " CHARG)\n" + " VALUES\n" + " (TO_CHAR(SYSDATE, 'YYYYMMDD'),\n" + " '5000',\n" + " '30',\n" + " :GOODSCODE,\n" + " :SAPCODE,\n" + " :USERCODE,\n" + " :OUTPUTNUM,\n" + " :SCRAPNUM,\n" + " :CLEANUPNUM,\n" + " :REPAIRNUM,\n" + " :WORKSHOP,\n" + " :LOGID,\n" + " :TESTMOULDFLAG,\n" + " :ZSCS,\n" + " :CHARG)"; paras = new OracleParameter[] { new OracleParameter(":GOODSCODE",row["GOODSCODE"].ToString()), new OracleParameter(":SAPCODE",row["SAPCODE"].ToString()), new OracleParameter(":USERCODE",row["USERCODE"].ToString()), new OracleParameter(":OUTPUTNUM",row["OUTPUTNUM"].ToString()), new OracleParameter(":SCRAPNUM",row["SCRAPNUM"].ToString()), new OracleParameter(":CLEANUPNUM",row["CLEANUPNUM"].ToString()), new OracleParameter(":REPAIRNUM",row["REPAIRNUM"].ToString()), new OracleParameter(":WORKSHOP",row["WORKSHOP"].ToString()), new OracleParameter(":LOGID",logid), new OracleParameter(":TESTMOULDFLAG",row["TESTMOULDFLAG"].ToString()), new OracleParameter(":ZSCS",row["ZSCS"].ToString()), new OracleParameter(":CHARG",row["CHARG"].ToString()), }; returnRows += tran.ExecuteNonQuery(sqlText, paras); } #endregion } #endregion #region 40节点 else if (dTable.Rows[j]["DATACODE"].ToString() == "40") { int logid = int.Parse(tran.GetSqlResultToStr(sqlText)); //记录logid logids.Add(logid); sqlText = "INSERT INTO TSAP_HEGII_DATALOG_BG\n" + " (LOGID,\n" + " LOGTYPE,\n" + " BEGINTIME,\n" + " YYYYMMDD,\n" + " WORKCODE,\n" + " DATACODE,\n" + " DATASTUTS,\n" + " DATAMSG,\n" + " DATALOGID,\n" + " EXECUTEDATEBEGIN,\n" + " EXECUTEDATEEND,\n" + " REMARKS,\n" + " SAPGUID)\n" + " VALUES\n" + " (:LOGID,\n" + " '4',\n" + " SYSDATE,\n" + " TO_CHAR(SYSDATE, 'YYYYMMDD'),\n" + " 5000,\n" + " :DATACODE,\n" + " 'Q',\n" + " '',\n" + " :LOGID,\n" + " SYSDATE,\n" + " SYSDATE,\n" + " :REMARKS,\n" + " SYS_GUID())"; paras = new OracleParameter[] { new OracleParameter(":LOGID",logid), new OracleParameter(":DATACODE","40"), new OracleParameter(":REMARKS","注浆变更：新产品ID："+goodsID), }; int returnRows = tran.ExecuteNonQuery(sqlText, paras); #region 明细 DataTable table40 = sapresultTable.Copy(); DataRow[] drRow40 = table40.Select("DATACODE = 40"); foreach (DataRow row in drRow40) { sqlText = "INSERT INTO TSAP_HEGII_WORKDATA_BG\n" + " (YYYYMMDD,\n" + " WORKCODE,\n" + " DATACODE,\n" + " GOODSCODE,\n" + " SAPCODE,\n" + " USERCODE,\n" + " OUTPUTNUM,\n" + " SCRAPNUM,\n" + " CLEANUPNUM,\n" + " REPAIRNUM,\n" + " WORKSHOP,\n" + " LOGID,\n" + " TESTMOULDFLAG,\n" + " ZSCS,\n" + " CHARG)\n" + " VALUES\n" + " (TO_CHAR(SYSDATE, 'YYYYMMDD'),\n" + " '5000',\n" + " '40',\n" + " :GOODSCODE,\n" + " :SAPCODE,\n" + " :USERCODE,\n" + " :OUTPUTNUM,\n" + " :SCRAPNUM,\n" + " :CLEANUPNUM,\n" + " :REPAIRNUM,\n" + " :WORKSHOP,\n" + " :LOGID,\n" + " :TESTMOULDFLAG,\n" + " :ZSCS,\n" + " :CHARG)"; paras = new OracleParameter[] { new OracleParameter(":GOODSCODE",row["GOODSCODE"].ToString()), new OracleParameter(":SAPCODE",row["SAPCODE"].ToString()), new OracleParameter(":USERCODE",row["USERCODE"].ToString()), new OracleParameter(":OUTPUTNUM",row["OUTPUTNUM"].ToString()), new OracleParameter(":SCRAPNUM",row["SCRAPNUM"].ToString()), new OracleParameter(":CLEANUPNUM",row["CLEANUPNUM"].ToString()), new OracleParameter(":REPAIRNUM",row["REPAIRNUM"].ToString()), new OracleParameter(":WORKSHOP",row["WORKSHOP"].ToString()), new OracleParameter(":LOGID",logid), new OracleParameter(":TESTMOULDFLAG",row["TESTMOULDFLAG"].ToString()), new OracleParameter(":ZSCS",row["ZSCS"].ToString()), new OracleParameter(":CHARG",row["CHARG"].ToString()), }; returnRows += tran.ExecuteNonQuery(sqlText, paras); } #endregion } #endregion #region 50节点 else if (dTable.Rows[j]["DATACODE"].ToString() == "50") { int logid = int.Parse(tran.GetSqlResultToStr(sqlText)); //记录logid logids.Add(logid); sqlText = "INSERT INTO TSAP_HEGII_DATALOG_BG\n" + " (LOGID,\n" + " LOGTYPE,\n" + " BEGINTIME,\n" + " YYYYMMDD,\n" + " WORKCODE,\n" + " DATACODE,\n" + " DATASTUTS,\n" + " DATAMSG,\n" + " DATALOGID,\n" + " EXECUTEDATEBEGIN,\n" + " EXECUTEDATEEND,\n" + " REMARKS,\n" + " SAPGUID)\n" + " VALUES\n" + " (:LOGID,\n" + " '4',\n" + " SYSDATE,\n" + " TO_CHAR(SYSDATE, 'YYYYMMDD'),\n" + " 5000,\n" + " :DATACODE,\n" + " 'Q',\n" + " '',\n" + " :LOGID,\n" + " SYSDATE,\n" + " SYSDATE,\n" + " :REMARKS,\n" + " SYS_GUID())"; paras = new OracleParameter[] { new OracleParameter(":LOGID",logid), new OracleParameter(":DATACODE","50"), new OracleParameter(":REMARKS","注浆变更：新产品ID："+goodsID), }; int returnRows = tran.ExecuteNonQuery(sqlText, paras); #region 明细 DataTable table50 = sapresultTable.Copy(); DataRow[] drRow50 = table50.Select("DATACODE = 50"); foreach (DataRow row in drRow50) { sqlText = "INSERT INTO TSAP_HEGII_WORKDATA_BG\n" + " (YYYYMMDD,\n" + " WORKCODE,\n" + " DATACODE,\n" + " GOODSCODE,\n" + " SAPCODE,\n" + " USERCODE,\n" + " OUTPUTNUM,\n" + " SCRAPNUM,\n" + " CLEANUPNUM,\n" + " REPAIRNUM,\n" + " WORKSHOP,\n" + " LOGID,\n" + " TESTMOULDFLAG,\n" + " ZSCS,\n" + " CHARG)\n" + " VALUES\n" + " (TO_CHAR(SYSDATE, 'YYYYMMDD'),\n" + " '5000',\n" + " '50',\n" + " :GOODSCODE,\n" + " :SAPCODE,\n" + " :USERCODE,\n" + " :OUTPUTNUM,\n" + " :SCRAPNUM,\n" + " :CLEANUPNUM,\n" + " :REPAIRNUM,\n" + " :WORKSHOP,\n" + " :LOGID,\n" + " :TESTMOULDFLAG,\n" + " :ZSCS,\n" + " :CHARG)"; paras = new OracleParameter[] { new OracleParameter(":GOODSCODE",row["GOODSCODE"].ToString()), new OracleParameter(":SAPCODE",row["SAPCODE"].ToString()), new OracleParameter(":USERCODE",row["USERCODE"].ToString()), new OracleParameter(":OUTPUTNUM",row["OUTPUTNUM"].ToString()), new OracleParameter(":SCRAPNUM",row["SCRAPNUM"].ToString()), new OracleParameter(":CLEANUPNUM",row["CLEANUPNUM"].ToString()), new OracleParameter(":REPAIRNUM",row["REPAIRNUM"].ToString()), new OracleParameter(":WORKSHOP",row["WORKSHOP"].ToString()), new OracleParameter(":LOGID",logid), new OracleParameter(":TESTMOULDFLAG",row["TESTMOULDFLAG"].ToString()), new OracleParameter(":ZSCS",row["ZSCS"].ToString()), new OracleParameter(":CHARG",row["CHARG"].ToString()), }; returnRows += tran.ExecuteNonQuery(sqlText, paras); } #endregion } #endregion } #region 更新总单状态为F string ids = string.Join(",", logids); if (!string.IsNullOrWhiteSpace(ids)) { sql = "update TSAP_HEGII_DATALOG_BG set DATASTUTS='F' where DATASTUTS = 'Q' AND LOGID IN (" + ids + ") "; tran.ExecuteNonQuery(sql); } #endregion } #endregion } } if (dtMatnr.Rows.Count > 0) { foreach (DataRow row in dtMatnr.Rows) { // 切换物料，切换已装组件 string returnMessage = string.Empty; int returnRows = SetMatnrIdnrk(tran, row["OLDMATNR"] + "", row["BARCODE"] + "", user, out returnMessage); if (returnRows < 0) { sre.Status = Constant.ServiceResultStatus.Other; sre.OtherStatus = returnRows; sre.Message = returnMessage; 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(); } } } ///

/// 注浆变更 ///

/// 登录用户信息 /// 原条码 /// 新条码 /// 备注 /// 操作结果 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] + "'"; sql = "select BEGINNINGFLAG from TP_PM_GroutingDailyDetail where barcode='" + subBarCode[i] + "'"; returnValue = tran.GetSqlResultToStr(sql); if (returnValue == "1") { sre.Status = Constant.ServiceResultStatus.Other; //sre.Message = "条码[" + subBarCode[i] + "]已经交坯，不能取消绑定"; 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] + "'"; //sql = "update TP_PM_UsedBarCode set barcode='" + subBarCode[i] + "@" + user.UserCode + "@" // + DateTime.Now.ToString("yyyyMMddHHmmss") + "' 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(); } } } ///

/// 设置物料编码组件 ///

/// private static int SetMatnrIdnrk(IDBTransaction oracleTrConn, string oldMatnr, string barcode, SUserInfo sUserInfo, out string message) { int returnRows = 0; message = ""; try { string sql = ""; sql = @" SELECT GDD.MATERIALCODE AS MATNR, DECODE(GDD.TESTMOULDFLAG, 0, 'C', 1, 'Y', '-') AS ZSCMS, CASE WHEN (INSTR(GDD.GROUTINGLINECODE, 'A') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1) AND INSTR(GT.GOODSTYPECODE, '001002') = 1 THEN 1 WHEN (INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1) AND INSTR(GT.GOODSTYPECODE, '001001') = 1 THEN 2 WHEN INSTR(GDD.GROUTINGLINECODE, 'C') = 1 THEN 3 ELSE 9 END AS WORKSHOP FROM TP_PM_GROUTINGDAILYDETAIL GDD INNER JOIN TP_MST_GOODS G ON G.GOODSID = GDD.GOODSID INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID WHERE GDD.BARCODE = :BARCODE "; DataTable dtMatnr = oracleTrConn.GetSqlResultToDt(sql, new OracleParameter[] { new OracleParameter(":barcode", OracleDbType.Varchar2, barcode, ParameterDirection.Input) }); // 如果物料编码不一致 if (!oldMatnr.Equals(dtMatnr.Rows[0]["MATNR"])) { sql = @" SELECT IDNRK, MEINS, MENGE, IDNRKNAME, IDNRKONLYCODE, CHARG, LGORT FROM TP_PM_BARCODEIDNRKREL WHERE VALUEFLAG = '1' AND BARCODE = :BARCODE "; DataTable dtIdnrks = oracleTrConn.GetSqlResultToDt(sql, new OracleParameter[] { new OracleParameter(":BARCODE", OracleDbType.Varchar2, barcode, ParameterDirection.Input) }); // 如果装了配件了 if (dtIdnrks.Rows.Count > 0) { //string datuv = System.DateTime.Now.Date.ToString("yyyyMMdd"); //Hashtable pars = new Hashtable(); //pars.Add("MATNR", dtMatnr.Rows[0]["MATNR"]); // 物料 //pars.Add("WERKS", "5000"); // 工厂 //pars.Add("ZSCS", "T"); // 生产工艺 //pars.Add("ZSCCJ", dtMatnr.Rows[0]["WORKSHOP"]); // 生产车间 //pars.Add("ZSCMS", dtMatnr.Rows[0]["ZSCMS"]); // 生产模式 //pars.Add("ZJDNU", "60"); // 节点 //pars.Add("DATUV", datuv); // 查询日期 //pars.Add("EMENG", 1); // 需求数量 //Hashtable item = new Hashtable(); //item.Add("item", pars); //Hashtable body = new Hashtable(); //body.Add("T_INPUT", item); //string postString = JsonConvert.SerializeObject(body); //INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII); //string url039 = ini.ReadIniData("SAP_NEW_INFO", "Url039"); ////string url039 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM039"; //string result = string.Empty; //try //{ // result = SAPDataLogic.PostData(url039, postString, "POST"); //} //catch (Exception ex) //{ // message = "获取SAP库存接口异常：\n" + ex.Message; // return -4; //} //JObject returnObj = JsonConvert.DeserializeObject(result); //JObject output = returnObj["T_OUTPUT"] as JObject; //JArray array = output["item"] as JArray; //DataTable dtIdnrk = Utility.ConvertToDataTable(array); //if ("E".Equals(dtIdnrk.Rows[0]["ZTYPE"] + "")) //{ // message = "获取SAP库存失败"; // return -5; //} DataTable dtIdnrk = oracleTrConn.GetSqlResultToDt(@" SELECT P.MATNR, PD.IDNRK, PD.NAME AS MAKTX, PD.MENGE, PD.MEINS FROM TP_MST_PACKINGBOM P INNER JOIN TP_MST_PACKINGBOMDETAIL PD ON PD.PACKINGBOMID = P.PACKINGBOMID WHERE INSTR(PD.NAME, '半成品') = 0 AND P.MATNR = :MATNR ", new OracleParameter[] { new OracleParameter("MATNR", dtMatnr.Rows[0]["MATNR"]) } ); // 获取已走过的装配工序 string procedureidlist = oracleTrConn.GetSqlResultToStr(@" SELECT LISTAGG(PROCEDUREID, ',') WITHIN GROUP(ORDER BY PROCEDUREID) AS PROCEDUREIDLIST FROM TP_PM_PRODUCTIONDATA WHERE VALUEFLAG = '1' AND MODELTYPE = '-5' AND BARCODE = :BARCODE ", new OracleParameter[] { new OracleParameter("BARCODE", barcode) } ); procedureidlist = "," + procedureidlist + ","; // 过滤当前工序需要的组件 DataTable dtIdnrkType = oracleTrConn.GetSqlResultToDt(@" SELECT IDNRKTYPE,SCANFLAG FROM TP_PC_PROCEDUREIDNRKTYPE WHERE INSTR(:PROCEDUREIDLIST , ',' || PROCEDUREID || ',') > 0", new OracleParameter[] { new OracleParameter("PROCEDUREIDLIST", procedureidlist) } ); dtIdnrk.DefaultView.RowFilter = "MAKTX NOT LIKE '%半成品%'"; dtIdnrk = dtIdnrk.DefaultView.ToTable(); // 过滤当前工序需要的组件 //DataTable dtIdnrkType = oracleTrConn.GetSqlResultToDt(@" //SELECT IDNRKTYPE // FROM TP_PC_PROCEDUREIDNRKTYPE // WHERE INSTR(:PROCEDUREIDLIST , ',' || PROCEDUREID || ',') > 0", // new OracleParameter[] { new OracleParameter("PROCEDUREIDLIST", procedureidlist) } //); //dtIdnrk.DefaultView.RowFilter = "MAKTX NOT LIKE '%半成品%'"; //dtIdnrk = dtIdnrk.DefaultView.ToTable(); // 如果包含其它，就不判断了 if (dtIdnrkType.Select("IDNRKTYPE = '其它'").Length == 0) { string fifter = ""; foreach (DataRow row in dtIdnrkType.Rows) { fifter += " MAKTX LIKE '%" + row["IDNRKTYPE"] + "%' OR"; } // 如果没有条件，也不判断了 if (fifter.Length > 0) { fifter = fifter.Substring(0, fifter.Length - 2); dtIdnrk.DefaultView.RowFilter = fifter; dtIdnrk = dtIdnrk.DefaultView.ToTable(); } } // 过滤掉已安装的组件 object idnrklist = oracleTrConn.GetSqlResultToStr(@" SELECT LISTAGG(IDNRK, ''',''') WITHIN GROUP(ORDER BY IDNRK) AS IDNRKLIST FROM TP_PM_BARCODEIDNRKREL WHERE VALUEFLAG = '1' AND BARCODE = :BARCODE ", new OracleParameter[] { new OracleParameter("BARCODE", barcode) } ); // 可以保留的组件 DataTable dtCanSaveIdnrk = dtIdnrk.Copy(); dtCanSaveIdnrk.DefaultView.RowFilter = "IDNRK IN ('" + idnrklist + "')"; dtCanSaveIdnrk = dtCanSaveIdnrk.DefaultView.ToTable(); if (idnrklist != null) { dtIdnrk.DefaultView.RowFilter = "IDNRK NOT IN ('" + idnrklist + "')"; dtIdnrk = dtIdnrk.DefaultView.ToTable(); } if (dtIdnrk.Rows.Count == 0) { } // 加上需要的列 dtIdnrk.Columns.Add("LGORT", typeof(string)); dtIdnrk.Columns.Add("CHARG", typeof(string)); dtIdnrk.Columns.Add("IDNRKONLYCODE", typeof(string)); //// 提取包材物料编码 //List matnrs = new List(); //foreach (DataRow row in dtIdnrk.Rows) //{ // if (!matnrs.Contains(row["IDNRK"] + "")) // { // matnrs.Add(row["IDNRK"] + ""); // } //} //string ZMSG = string.Empty; //// 查线边仓库存 //DataTable dtSapInventory = SapApi.ZMM_WMS016("5000", matnrs, "", out ZMSG); //dtSapInventory.DefaultView.RowFilter = "LGORT IN('2420','2430','2440','2450','2460','2470','2480','2490')"; //dtSapInventory = dtSapInventory.DefaultView.ToTable(); //DataRow[] rows = null; //if (dtSapInventory != null && dtSapInventory.Rows.Count > 0) //{ // // 改名 // dtSapInventory.Columns["MATNR"].ColumnName = "IDNRK"; // // 判断是否缺库存 // List notEnoughIdnrks = new List(); // foreach (string idnrk in matnrs) // { // rows = dtSapInventory.Select("IDNRK = '" + idnrk + "'"); ; // if (rows.Length == 0) // { // notEnoughIdnrks.Add(idnrk); // } // } // if (notEnoughIdnrks.Count > 0) // { // message = "以下组件在SAP系统中库存不足：\n" + string.Join(",", notEnoughIdnrks.ToArray()); // return -6; // } // // 库存数量要改为数字类型 // dtSapInventory.Columns.Add("BALANCE", typeof(decimal)); // decimal balance = 0; // foreach (DataRow row in dtSapInventory.Rows) // { // decimal.TryParse(row["KYKC"] + "", out balance); // row["BALANCE"] = balance; // } //} //else //{ // message = "以下组件在SAP系统中库存不足：\n" + string.Join(",", matnrs.ToArray()); // return -6; //} //dtSapInventory.DefaultView.RowFilter = "BALANCE <> 0"; //dtSapInventory = dtSapInventory.DefaultView.ToTable(); //dtSapInventory.DefaultView.Sort = "LGORT, CHARG"; //dtSapInventory = dtSapInventory.DefaultView.ToTable(); //DateTime now = DateTime.Now; //foreach (DataRow row in dtIdnrk.Rows) //{ // rows = dtSapInventory.Select("IDNRK = '" + row["IDNRK"] + "' AND BALANCE >= " + row["MENGE"]); // if (rows.Length > 0) // { // row["LGORT"] = rows[0]["LGORT"]; // row["CHARG"] = rows[0]["CHARG"]; // row["IDNRKONLYCODE"] = ""; // } // else // { // message = row["IDNRK"] + ":库存不足"; // return -6; // } //} dtIdnrk.TableName = "Idnrk"; dtCanSaveIdnrk.TableName = "CanSaveIdnrk"; string canSaveIdnrks = ","; if (dtCanSaveIdnrk != null && dtCanSaveIdnrk.Rows.Count > 0) { foreach (DataRow row in dtCanSaveIdnrk.Rows) { canSaveIdnrks += row["IDNRK"] + ","; } } string delSql = @" DELETE FROM TP_PM_BARCODEIDNRKREL WHERE BARCODE = :BARCODE AND INSTR(:CANSAVEIDNRKS, ','|| IDNRK ||',') = 0 "; returnRows += oracleTrConn.ExecuteNonQuery(delSql, new OracleParameter[] { new OracleParameter(":BARCODE", barcode), new OracleParameter(":CANSAVEIDNRKS", canSaveIdnrks) }); if (dtIdnrk != null && dtIdnrk.Rows.Count > 0) { string barcodeidnrkrel = @" INSERT INTO TP_PM_BARCODEIDNRKREL (PROCEDUREID, BARCODE, MATNR, IDNRK, MEINS, MENGE, IDNRKNAME, IDNRKONLYCODE, CHARG, LGORT, ACCOUNTID, CREATEUSERID, UPDATEUSERID) VALUES (:PROCEDUREID, :BARCODE, :MATNR, :IDNRK, :MEINS, :MENGE, :IDNRKNAME, :IDNRKONLYCODE, :CHARG, :LGORT, :ACCOUNTID, :USERID, :USERID) "; foreach (DataRow row in dtIdnrk.Rows) { returnRows += oracleTrConn.ExecuteNonQuery(barcodeidnrkrel, new OracleParameter[] { new OracleParameter(":PROCEDUREID", -1), new OracleParameter(":BARCODE", barcode), new OracleParameter(":MATNR", row["MATNR"]), new OracleParameter(":IDNRK", row["IDNRK"]), new OracleParameter(":MEINS", row["MEINS"]), new OracleParameter(":MENGE", Convert.ToDecimal( row["MENGE"])), new OracleParameter(":IDNRKNAME", row["MAKTX"]), new OracleParameter(":IDNRKONLYCODE", row["IDNRKONLYCODE"]), new OracleParameter(":CHARG", row["CHARG"]), new OracleParameter(":LGORT", row["LGORT"]), new OracleParameter(":ACCOUNTID", sUserInfo.AccountID), new OracleParameter(":USERID", sUserInfo.UserID) }); } } } } } catch (Exception ex) { message = "接口异常：\n" + ex.Message; return -4; } return returnRows; } } }