/******************************************************************************* * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential * 类的信息： * 1.程序名称：PMModuleLogic.cs * 2.功能描述：生产管理信息与数据库的交互类 * 编辑履历： * 作者 日期 版本 修改内容 * 庄天威 2014/09/17 1.00 新建 * 徐伟 2019/09/26 1.00 修改 *******************************************************************************/ using System; using System.Collections.Generic; using System.Data; using System.Text; using Dongke.IBOSS.PRD.Basics.BaseResources; using Dongke.IBOSS.PRD.Basics.DataAccess; using Dongke.IBOSS.PRD.Basics.Library; using Dongke.IBOSS.PRD.Service.CMNModuleService; using Dongke.IBOSS.PRD.Service.DataModels; using Dongke.IBOSS.PRD.Service.SAPHegiiDataService; using Dongke.IBOSS.PRD.WCF.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels.PMModule; using Newtonsoft.Json; using Newtonsoft.Json.Linq; using Oracle.ManagedDataAccess.Client; namespace Dongke.IBOSS.PRD.Service.PMModuleLogic { /// /// 生产管理信息与数据库的交互类 /// public partial class PMModuleLogic { #region 注浆日报表 /// /// 获得注浆日报表 /// /// 注浆日报实体 /// 用户基本信息 /// 数据源Dataset /// /// 庄天威 2014.09.10 新建 /// public static DataSet GetGroutingDaily(GroutingDailyEntity groutingDailyEntity, SUserInfo user) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("GroutingDailyID",OracleDbType.Int32, groutingDailyEntity.GroutingDailyID,ParameterDirection.Input), new OracleParameter("AccountID",OracleDbType.Int32, user.AccountID,ParameterDirection.Input), new OracleParameter("GroutingLineCode",OracleDbType.NVarchar2, groutingDailyEntity.GroutingLineCode,ParameterDirection.Input), new OracleParameter("GroutingLineName",OracleDbType.NVarchar2, groutingDailyEntity.GroutingLineName,ParameterDirection.Input), new OracleParameter("GroutingDateStart",OracleDbType.Date, groutingDailyEntity.GroutingDate,ParameterDirection.Input), new OracleParameter("GroutingDateEnd",OracleDbType.Date, groutingDailyEntity.GroutingDateEnd,ParameterDirection.Input), new OracleParameter("GroutingMouldCode",OracleDbType.NVarchar2, groutingDailyEntity.GroutingMouldCode,ParameterDirection.Input), new OracleParameter("GoodsCode",OracleDbType.NVarchar2, groutingDailyEntity.GoodsCode,ParameterDirection.Input), new OracleParameter("GoodsName",OracleDbType.NVarchar2, groutingDailyEntity.GoodsName,ParameterDirection.Input), new OracleParameter("GroutingFlag",OracleDbType.Int32, groutingDailyEntity.GroutingFlag,ParameterDirection.Input), new OracleParameter("ScrapFlag",OracleDbType.Int32, groutingDailyEntity.ScrapFlag,ParameterDirection.Input), new OracleParameter("GMouldTypeID",OracleDbType.Int32, groutingDailyEntity.GMouldTypeID,ParameterDirection.Input), new OracleParameter("remarks",OracleDbType.NVarchar2, groutingDailyEntity.Remarks,ParameterDirection.Input), new OracleParameter("UserId",OracleDbType.Int32, user.UserID,ParameterDirection.Input), new OracleParameter("PurviewType",OracleDbType.Int32, groutingDailyEntity.Purview,ParameterDirection.Input), new OracleParameter("rs_result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_PM_GetGroutingDaily", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获得注浆日报表明细(根据注浆日报表ID) /// /// 注浆日报主ID /// 用户基本信息 /// Dataset数据源 /// /// 庄天威 2014.09.10 新建 /// public static DataSet GetGroutingDailyDetailByMainId(int DailyMainId, SUserInfo user) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("AccountID",OracleDbType.Int32, user.AccountID,ParameterDirection.Input), new OracleParameter("GroutingDailyID",OracleDbType.Int32, DailyMainId,ParameterDirection.Input), new OracleParameter("rs_result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_PM_GetGDailyDetailByMainId", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获得注浆产品(根据注浆日报明细ID) /// /// 注浆日报明细ID /// 用户基本信息 /// string条码字符串集合 /// /// 庄天威 2014.09.10 新建 /// public static string GetGroutingProductByDetailId(int DailyDetailId, SUserInfo user) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); StringBuilder sbSql = new StringBuilder(); sbSql.Append(" select Barcode from TP_PM_GroutingProduct where GroutingDailyDetailID=" + DailyDetailId); DataSet dsProduct = con.GetSqlResultToDs(sbSql.ToString(), null); StringBuilder sbBarCodeList = new StringBuilder(); if (dsProduct.Tables.Count != 0) { foreach (DataRow dr in dsProduct.Tables[0].Rows) { sbBarCodeList.Append(dr[0].ToString() + ","); } } if (sbBarCodeList.Length != 0) { return sbBarCodeList.ToString().Substring(0, sbBarCodeList.Length - 1); } else { return ""; } } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 查看某成型线今天是否注浆 /// /// 成型线ID /// 注浆时间 /// int /// /// 庄天威 2014.09.29 新建 /// public static int GetCanManyTimes(int GroutingLineID, DateTime GroutingDate) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); StringBuilder sbSql = new StringBuilder(); sbSql.Append("select GroutingdailyId from TP_PM_GroutingDaily"); sbSql.Append(" where Groutingdate = to_date('" + GroutingDate.ToString() + "','yyyy-MM-dd hh24:mi:ss')"); sbSql.Append(" and CanManyTimes = 0 and GroutingLineID = " + GroutingLineID); DataTable dtReturn = con.GetSqlResultToDt(sbSql.ToString(), null); if (dtReturn != null) { return dtReturn.Rows.Count; } else { return 0; } } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据成型线ID获取注浆明细信息（外联条码） /// /// 成型线ID集 /// 注浆时间起始 /// 注浆时间终止 /// 用户基本信息 /// DataSet /// /// 庄天威 2014.09.30 新建 /// public static DataSet GetGDailyDetailLeftBarCode(string GroutingLineIds, DateTime? GroutingDateStart, DateTime? GroutingDateEnd, SUserInfo user) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string GroutingDateStartStr = null; string GroutingDateEndStr = null; if (GroutingDateStart != null) { GroutingDateStartStr = GroutingDateStart.ToString(); } if (GroutingDateEnd != null) { GroutingDateEndStr = GroutingDateEnd.ToString(); } OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("GroutingLineID",OracleDbType.NVarchar2, GroutingLineIds,ParameterDirection.Input), new OracleParameter("GroutingDateStart",OracleDbType.NVarchar2, GroutingDateStartStr,ParameterDirection.Input), new OracleParameter("GroutingDateEnd",OracleDbType.NVarchar2, GroutingDateEndStr,ParameterDirection.Input), new OracleParameter("AccountID",OracleDbType.Int32, user.AccountID,ParameterDirection.Input), new OracleParameter("rs_result",OracleDbType.RefCursor, ParameterDirection.Output) }; DataSet ds = con.ExecStoredProcedure("PRO_PM_GetGDailyDetailLBarCode", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 条码检索 /// /// 条码检索实体 /// 用户基本信息 /// 数据源Dataset /// /// 庄天威 2014.09.10 新建 /// public static DataSet GetBarCode(GetBarCodeEntity gbEntity, SUserInfo user) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("In_GroutingLineCode",OracleDbType.NVarchar2, gbEntity.GroutingLineCode,ParameterDirection.Input), new OracleParameter("In_GroutingLineDetailCode",OracleDbType.NVarchar2, gbEntity.GroutingLineDetailCode,ParameterDirection.Input), new OracleParameter("In_GroutingUserCode",OracleDbType.NVarchar2, gbEntity.GroutingUserCode,ParameterDirection.Input), new OracleParameter("In_GroutingDateS",OracleDbType.Date, gbEntity.GroutingDateS,ParameterDirection.Input), new OracleParameter("In_GroutingDateE",OracleDbType.Date, gbEntity.GroutingDateE,ParameterDirection.Input), new OracleParameter("In_AccountID",OracleDbType.Int32, user.AccountID,ParameterDirection.Input), new OracleParameter("Out_Result",OracleDbType.RefCursor, ParameterDirection.Output) }; DataSet ds = con.ExecStoredProcedure("PRO_PM_GetBarCode", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 废弃产品 /// /// 根据条码获取该产品的在产信息以及生产数据 /// /// 产品条码 /// 装有两个DataTable的数据集，第一个是在产信息，第二个是生产数据 public static DataSet GetInProductionAndProductionData(string BarCode, SUserInfo sUserInfo) { DataSet dsList = new DataSet(); IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); StringBuilder sbSql = new StringBuilder(); ////sbSql.Append("Select inp.*,pt.ProcedureModelTypeID from TP_PM_InProduction inp "); ////sbSql.Append(" Inner join TP_PC_Procedure p"); //////sbSql.Append(" On inp.CompleteProcedureID = p.ProcedureID"); ////sbSql.Append(" On inp.FlowProcedureID = p.ProcedureID"); ////sbSql.Append(" Inner join TP_SYS_ProcedureModelType pt"); ////sbSql.Append(" On p.ModelType = pt.ProcedureModelTypeID"); ////sbSql.Append(" where inp.Barcode=:Barcode and inp.ValueFlag=1"); sbSql.Append("Select inp.*, p.ProcedureCode,p.ProcedureName, inp.ModelType as ProcedureModelTypeID,1 as endmode from TP_PM_InProduction inp "); sbSql.Append(" inner join tp_pc_Procedure p on p.ProcedureID = inp.ProcedureID "); sbSql.Append(" where inp.Barcode=:Barcode and inp.ValueFlag=1"); OracleParameter[] Paras = new OracleParameter[]{ new OracleParameter(":Barcode",OracleDbType.NVarchar2, BarCode,ParameterDirection.Input), }; DataTable dtProduction = con.GetSqlResultToDt(sbSql.ToString(), Paras); if (dtProduction != null) { if (dtProduction.Rows.Count != 0) { dsList.Tables.Add(dtProduction); } else { //在产产品不存在的话，看看是不是变成了成品 sbSql.Clear(); sbSql.Append("Select fp.*,2 as endmode from TP_PM_FinishedProduct fp "); sbSql.Append(" where fp.Barcode=:Barcode and fp.ValueFlag=1"); dtProduction = con.GetSqlResultToDt(sbSql.ToString(), Paras); if (dtProduction != null) { dsList.Tables.Add(dtProduction); } } } else { return null; } DataSet sysSetting = CommonModuleLogic.CommonModuleLogic.GetSysSettingBySettingType("S_PM_015", sUserInfo); string spm015 = null; if (sysSetting != null && sysSetting.Tables.Count > 0 && sysSetting.Tables[0].Rows.Count > 0) { spm015 = sysSetting.Tables[0].Rows[0]["SettingValue"] + ""; } sbSql.Clear(); sbSql.Append("Select ProductionDataID,ProcedureID,ProcedureName,ProcedureCode from TP_PM_ProductionDataIn where Barcode=:Barcode and ValueFlag=1 "); // 报损不能选成型责任 if ("0" == spm015) { sbSql.Append(" and MODELTYPE <> 5"); } sbSql.Append(" order by ProductionDataid"); DataTable dtProductionData = con.GetSqlResultToDt(sbSql.ToString(), Paras); if (dtProductionData != null) { dsList.Tables.Add(dtProductionData); } else { return null; } return dsList; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据页面条件获取废弃产品一览 /// /// 废弃产品 /// 用户基本信息 /// DataSet public static DataSet GetScrapProduct(ScrapProductEntity selectProEntity, SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_ProductionLineCode",OracleDbType.NVarchar2, selectProEntity.ProductionLineCode,ParameterDirection.Input), new OracleParameter("in_ProductionLineName",OracleDbType.NVarchar2, selectProEntity.ProductionLineName,ParameterDirection.Input), new OracleParameter("in_ResponProcedureCode",OracleDbType.NVarchar2, selectProEntity.ResponProcedureCode,ParameterDirection.Input), new OracleParameter("in_ResponProcedureName",OracleDbType.NVarchar2, selectProEntity.ResponProcedureName,ParameterDirection.Input), new OracleParameter("in_ScrapProductID",OracleDbType.Int32, selectProEntity.ScrapProductID,ParameterDirection.Input), new OracleParameter("in_BarCode",OracleDbType.NVarchar2, selectProEntity.BarCode,ParameterDirection.Input), //new OracleParameter("in_ResponUserID",OracleDbType.Int32, // selectProEntity.ResponUserID,ParameterDirection.Input), new OracleParameter("in_ResponUserCode",OracleDbType.Varchar2, selectProEntity.ResponUserCode,ParameterDirection.Input), new OracleParameter("in_GoodsCode",OracleDbType.NVarchar2, selectProEntity.GoodsCode,ParameterDirection.Input), new OracleParameter("in_GoodsName",OracleDbType.NVarchar2, selectProEntity.GoodsName,ParameterDirection.Input), new OracleParameter("in_Rreason",OracleDbType.NVarchar2, selectProEntity.Rreason,ParameterDirection.Input), new OracleParameter("in_Remarks",OracleDbType.NVarchar2, selectProEntity.Remarks,ParameterDirection.Input), new OracleParameter("in_ScrapDateStart",OracleDbType.Date, selectProEntity.ScrapDate,ParameterDirection.Input), new OracleParameter("in_ScrapDateEnd",OracleDbType.Date, selectProEntity.ScrapDateEnd,ParameterDirection.Input), new OracleParameter("in_AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter("in_AuditStatus",OracleDbType.Int32, selectProEntity.AuditStatus,ParameterDirection.Input), new OracleParameter("in_GroutingUserCode",OracleDbType.Varchar2, selectProEntity.GroutingUserCode,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet dsReturn = con.ExecStoredProcedure("PRO_PM_GetScrapProduct", paras); return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据责任工序ID获取责任人列表 /// /// 责任工序ID /// DataSet public static DataSet GetScrapResponsibleByResponProcedureID(int ResponProcedureId) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = "Select * from TP_PM_ScrapResponsible where ValueFlag=1 and ResponProcedureID=" + ResponProcedureId; DataSet dsReturn = con.GetSqlResultToDs(strSql); return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据废弃产品ID获取责任人列表 /// /// 废弃产品ID /// DataSet public static DataSet GetScrapResponsibleBySPId(int SPId) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = @"Select sr.*,userinfo.userName from TP_PM_ScrapResponsible sr inner join TP_MST_User userinfo on sr.UserID = userinfo.UserID where ScrapProductID=" + SPId; DataSet dsReturn = con.GetSqlResultToDs(strSql); return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据废弃产品ID获取责任工序 /// /// 废弃产品ID /// DataSet public static DataSet GetResponProcedureBySPId(int SPId) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = "Select * from TP_PM_ResponProcedure where ValueFlag=1 and ScrapProductID=" + SPId; DataSet dsReturn = con.GetSqlResultToDs(strSql); return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 验证废弃产品唯一性 /// /// 产品条码 /// int public static string ScrapProductChack(string BarCode, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // 当前工号是否有条码所在工序的操作权限 string sqlString = "SELECT inp.procedureid\n" + " ,p.procedurename\n" + " ,p.MODELTYPE\n" + " ,(SELECT 1\n" + " FROM tp_mst_userpurview t\n" + " WHERE t.purviewtype = 10\n" + " AND t.userid = :userid\n" + " AND (t.purviewid = -1 OR t.purviewid = inp.procedureid)\n" + " AND rownum = 1) userpurview\n" + " FROM tp_pm_inproduction inp\n" + " LEFT JOIN tp_pc_procedure p\n" + " ON p.procedureid = inp.procedureid\n" + " WHERE inp.barcode = :barcode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":userid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":barcode",OracleDbType.Varchar2, BarCode,ParameterDirection.Input), }; DataTable dt = con.GetSqlResultToDt(sqlString, paras); if (dt == null || dt.Rows.Count == 0) { // 条码不在产。 return "2"; } object userpurview = dt.Rows[0]["userpurview"]; if (userpurview == null || userpurview == DBNull.Value) { return "3:" + dt.Rows[0]["procedurename"]; } int MODELTYPE = Convert.ToInt32(dt.Rows[0]["MODELTYPE"]); if (MODELTYPE == 1 || MODELTYPE == 2 || MODELTYPE == 3 || MODELTYPE == 6) { return "4:该产品处于无法报损的特殊工序中"; } #region 判断产品是否被挂起 sqlString = "SELECT 1 AS RES\n" + " FROM TP_PM_PRODUCTSUSPEND PS\n" + " INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD\n" + " ON GDD.GROUTINGDAILYDETAILID = PS.GROUTINGDAILYDETAILID\n" + " WHERE GDD.BARCODE = :Barcode"; OracleParameter[] pars = new OracleParameter[] { new OracleParameter(":Barcode",OracleDbType.NVarchar2, BarCode,ParameterDirection.Input), }; object objResult = con.GetSqlResultToObj(sqlString, pars); if (objResult != null) { return "4:该产品被挂起"; } #endregion /* string strSql = @"Select * from TP_PM_ScrapProduct where ValueFlag=1 and BarCode='" + BarCode + "' and (AuditStatus=0 or AuditStatus=1) " ; */ string strSql = "select GoodsLevelTypeID,AuditStatus from TP_PM_ScrapProduct " + " where BarCode='" + BarCode + "'and CreateTime=(select max(CreateTime) " + " from TP_PM_ScrapProduct " + " where BarCode='" + BarCode + "' and ValueFlag=1 and (AuditStatus = 0 or AuditStatus = 1) and recyclingflag=0 )" + " and ValueFlag=1 and (AuditStatus = 0 or AuditStatus = 1) and recyclingflag=0"; DataSet dsReturn = con.GetSqlResultToDs(strSql); if (dsReturn.Tables[0].Rows.Count == 0) { return "1"; } else { if (dsReturn.Tables[0].Rows[0]["GOODSLEVELTYPEID"].ToString().Equals("9") && dsReturn.Tables[0].Rows[0]["AUDITSTATUS"].ToString().Equals("1")) { return "1"; } return "0"; } } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 温湿计信息 /// /// 获得温湿计信息 /// /// 温湿计信息实体 /// 用户基本信息 /// 数据源Dataset /// /// 庄天威 2014.09.10 新建 /// public static DataSet GetCelsiusRecord(CelsiusRecordEntity celsiusRecordEntity, SUserInfo user) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("In_RecordID",OracleDbType.Int32, celsiusRecordEntity.RecordID,ParameterDirection.Input), new OracleParameter("In_PurviewType",OracleDbType.Int32, celsiusRecordEntity.PurviewType,ParameterDirection.Input), new OracleParameter("In_AccountID",OracleDbType.Int32, user.AccountID,ParameterDirection.Input), new OracleParameter("In_ThermometerID",OracleDbType.Int32, celsiusRecordEntity.ThermometerID,ParameterDirection.Input), new OracleParameter("In_ThermometerCode",OracleDbType.NVarchar2, celsiusRecordEntity.ThermometerCode,ParameterDirection.Input), new OracleParameter("In_RecorderID",OracleDbType.Int32, user.UserID,ParameterDirection.Input), new OracleParameter("In_RecordDateS",OracleDbType.Date, celsiusRecordEntity.RecordDate,ParameterDirection.Input), new OracleParameter("In_RecordDateE",OracleDbType.Date, celsiusRecordEntity.RecordDateEnd,ParameterDirection.Input), new OracleParameter("In_Remarks",OracleDbType.NVarchar2, celsiusRecordEntity.Remarks,ParameterDirection.Input), new OracleParameter("In_ManagerName",OracleDbType.NVarchar2, celsiusRecordEntity.ManagerName,ParameterDirection.Input), new OracleParameter("In_InsertTime",OracleDbType.Date, celsiusRecordEntity.InsertTime,ParameterDirection.Input), new OracleParameter("Out_Result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_PM_GetCelsiusRecord", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获得温湿计信息（图表用，平均值） /// /// 温湿计信息实体 /// 用户基本信息 /// 数据源Dataset /// /// 庄天威 2014.09.10 新建 /// public static DataSet GetCelsiusRecordView(CelsiusRecordEntity celsiusRecordEntity, SUserInfo user) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("In_AccountID",OracleDbType.Int32, user.AccountID,ParameterDirection.Input), new OracleParameter("In_LookerID",OracleDbType.Int32, user.UserID,ParameterDirection.Input), new OracleParameter("In_ThermometerCode",OracleDbType.NVarchar2, celsiusRecordEntity.ThermometerCode,ParameterDirection.Input), new OracleParameter("In_RecordDateS",OracleDbType.Date, celsiusRecordEntity.RecordDate,ParameterDirection.Input), new OracleParameter("In_RecordDateE",OracleDbType.Date, celsiusRecordEntity.RecordDateEnd,ParameterDirection.Input), new OracleParameter("In_Remarks",OracleDbType.NVarchar2, celsiusRecordEntity.Remarks,ParameterDirection.Input), new OracleParameter("In_Days",OracleDbType.NVarchar2, celsiusRecordEntity.Days,ParameterDirection.Input), new OracleParameter("Out_Result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_PM_GetCelsiusRecordView", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 获取注浆次数（By工号） /// /// 获取注浆次数（By工号） /// /// 查询条件实体 /// 当前用户 /// 返回信息数据集 public static DataSet GetGroutingCountByUser(GroutingCountByUserEntity gcEntity, SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("In_AccountID",OracleDbType.Int32, userInfo.AccountID,ParameterDirection.Input), new OracleParameter("In_GroutingDateS",OracleDbType.Date, gcEntity.GroutingDateS,ParameterDirection.Input), new OracleParameter("In_GroutingDateE",OracleDbType.Date, gcEntity.GroutingDateE,ParameterDirection.Input), new OracleParameter("In_GroutingLineCode",OracleDbType.NVarchar2, gcEntity.GroutingLineCode,ParameterDirection.Input), new OracleParameter("In_UserId",OracleDbType.Int32, gcEntity.UserId,ParameterDirection.Input), new OracleParameter("In_UserIds",OracleDbType.NVarchar2, gcEntity.UserIds,ParameterDirection.Input), new OracleParameter("In_UserCode",OracleDbType.NVarchar2, gcEntity.UserCode,ParameterDirection.Input), new OracleParameter("In_GoodsId",OracleDbType.Int32, gcEntity.GoodsId,ParameterDirection.Input), new OracleParameter("In_GoodsIds",OracleDbType.NVarchar2, gcEntity.GoodsIds,ParameterDirection.Input), new OracleParameter("In_GoodsTypeCode",OracleDbType.NVarchar2, gcEntity.GoodsTypeCode,ParameterDirection.Input), new OracleParameter("In_GoodsCode",OracleDbType.NVarchar2, gcEntity.GoodsCode,ParameterDirection.Input), new OracleParameter("Out_Result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_RPT_GetGroutingCountByUser", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 根据条码获得在产信息 /// /// 根据条码获得在产信息 /// /// 产品条码 /// DataSet public static DataSet GetInProductionByBarcode(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "select t.goodsid,t.goodsname,t.userid from tp_pm_InProduction t where t.barcode= " + barcode; DataSet ds = con.GetSqlResultToDs(sqlString); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 获取生产工序计件数据 /// /// 使用存储过程PRO_PM_SearchProductionData获取生产工序计件数据 /// /// 生产数据实体类 /// DataTable public static DataTable SearchProductionData(SearchProductionDataEntity searchEntity) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); string procsql = "PRO_PM_SearchProductionData"; try { IDataParameter[] paras = new OracleParameter[] { new OracleParameter("in_beginDate",OracleDbType.Date, searchEntity.BeginDate, ParameterDirection.Input), new OracleParameter("in_endDate", OracleDbType.Date,searchEntity.EndDate, ParameterDirection.Input), new OracleParameter("in_procedureID", OracleDbType.Int32,searchEntity.ProcedureID, ParameterDirection.Input), new OracleParameter("in_modeltype", OracleDbType.Int32,searchEntity.ProcedureModelId, ParameterDirection.Input), new OracleParameter("in_organizationid", OracleDbType.Int32,searchEntity.OrganizationID, ParameterDirection.Input), new OracleParameter("in_isreworked", OracleDbType.NVarchar2,searchEntity.IsRework, ParameterDirection.Input), new OracleParameter("out_result", OracleDbType.RefCursor, ParameterDirection.Output) }; oracleConn.Open(); DataSet returnDs = oracleConn.ExecStoredProcedure(procsql, paras); if (returnDs != null && returnDs.Tables.Count > 0) { return returnDs.Tables[0]; } else { return null; } } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 使用存储过程PRO_PM_GetProductionData获取生产工序计件数据 /// /// 生产数据实体类 /// 用户基本信息 /// DataTable public static DataTable GetProductionData(SearchProductionDataEntity searchEntity, SUserInfo user) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); //string procsql = "PRO_PM_GetProductionData"; //try //{ // IDataParameter[] paras = new OracleParameter[] // { // new OracleParameter("in_procedureID",OracleDbType.Int32, searchEntity.ProcedureID, ParameterDirection.Input), // new OracleParameter("in_barCode", OracleDbType.Varchar2,searchEntity.BarCode, ParameterDirection.Input), // new OracleParameter("in_goodsCode", OracleDbType.Varchar2,searchEntity.GoodsCode, ParameterDirection.Input), // new OracleParameter("in_goodsName", OracleDbType.Varchar2,searchEntity.GoodsName, ParameterDirection.Input), // new OracleParameter("in_userCode", OracleDbType.Varchar2,searchEntity.UserCode, ParameterDirection.Input), // new OracleParameter("in_organizationID", OracleDbType.Int32,searchEntity.OrganizationID, ParameterDirection.Input), // new OracleParameter("in_remarks", OracleDbType.Varchar2,searchEntity.Remarks, ParameterDirection.Input), // new OracleParameter("in_isReworked", OracleDbType.Varchar2,searchEntity.IsRework, ParameterDirection.Input), // new OracleParameter("in_begindate", OracleDbType.Date,searchEntity.BeginDate, ParameterDirection.Input), // new OracleParameter("in_enddate", OracleDbType.Date,searchEntity.EndDate, ParameterDirection.Input), // new OracleParameter("in_accountID", OracleDbType.Int32,user.AccountID, ParameterDirection.Input), // new OracleParameter("in_userID", OracleDbType.Int32,user.UserID, ParameterDirection.Input), // new OracleParameter("in_kilnCode", OracleDbType.Varchar2,searchEntity.KilnCode, ParameterDirection.Input), // new OracleParameter("in_kilnCarCode", OracleDbType.Varchar2,searchEntity.KilnCarCode, ParameterDirection.Input), // new OracleParameter("in_kilnCarPosition", OracleDbType.Int32,searchEntity.KilnCarPosition, ParameterDirection.Input), // new OracleParameter("in_currentUserID", OracleDbType.Int32,user.UserID, ParameterDirection.Input), // new OracleParameter("out_result", OracleDbType.RefCursor, ParameterDirection.Output), // new OracleParameter("in_goodsTypeCode", OracleDbType.Varchar2,searchEntity.GoodsTypeCode, ParameterDirection.Input), // }; // oracleConn.Open(); // DataSet returnDs = oracleConn.ExecStoredProcedure(procsql, paras); // if (returnDs != null && returnDs.Tables.Count > 0) // { // return returnDs.Tables[0]; // } // else // { // return null; // } //} //catch (Exception ex) //{ // throw ex; //} //finally //{ // if (oracleConn.ConnState == ConnectionState.Open) // { // oracleConn.Close(); // } //} string sqlString = ""; //IDataParameter[] paras = null; List parameters = new List(); try { sqlString = @"SELECT TP_PM_PRODUCTIONDATA.CREATETIME ,TP_PM_PRODUCTIONDATA.UPDATETIME ,TP_PM_PRODUCTIONDATA.PRODUCTIONDATAID ,TP_PM_PRODUCTIONDATA.PRODUCTIONLINECODE ,TP_PM_PRODUCTIONDATA.BARCODE ,TP_PM_PRODUCTIONDATA.GOODSCODE ,TP_PM_PRODUCTIONDATA.GOODSNAME ,TP_PM_PRODUCTIONDATA.USERCODE ,TP_PM_PRODUCTIONDATA.ISPUBLICBODY ,DECODE(TP_PM_PRODUCTIONDATA.PROCEDUREMODEL ,'1 ' ,'计数模型' ,'检验模型') AS PROCEDUREMODEL ,TP_SYS_PROCEDUREMODELTYPE.PROCEDUREMODELTYPENAME ,DECODE(TP_PM_PRODUCTIONDATA.PIECETYPE ,'1' ,'不计件' ,'同工种策略') AS PIECETYPE ,TP_PM_PRODUCTIONDATA.ISREWORKED ,TP_MST_GOODSLEVEL.GOODSLEVELNAME AS GOODSGRADE ,TP_MST_ORGANIZATION.ORGANIZATIONNAME ,TP_PM_PRODUCTIONDATA.REMARKS ,TP_PM_PRODUCTIONDATA.KILNID ,TP_PM_PRODUCTIONDATA.KILNCODE ,TP_PM_PRODUCTIONDATA.KILNNAME ,TP_PM_PRODUCTIONDATA.KILNCARID ,TP_PM_PRODUCTIONDATA.KILNCARCODE ,TP_PM_PRODUCTIONDATA.KILNCARNAME ,TP_PM_PRODUCTIONDATA.KILNCARPOSITION ,TP_PM_PRODUCTIONDATA.KILNCARBATCHNO ,TP_MST_DATADICTIONARY.DICTIONARYVALUE KILNCARPOSITIONNAME ,TP_MST_GOODSTYPE.GOODSTYPENAME ,TP_MST_USER.USERCODE AS BARUSERCODE ,TP_PM_PRODUCTIONDATA.GROUTINGUSERCODE ,TP_PM_PRODUCTIONDATA.GROUTINGMOULDCODE ,TP_PM_PRODUCTIONDATA.GROUTINGDATE ,TP_PM_PRODUCTIONDATA.GROUTINGNUM ,TP_MST_USER.USERNAME AS BARUSERNAME ,TP_MST_GOODSLEVEL.GOODSLEVELNAME AS GOODSLEVELTYPE ,REPLACE((TP_MST_LOGO.LOGONAME || '[' || TP_MST_LOGO.LOGOCODE || ']') ,'[]' ,'') AS LOGOCODENAME ,TP_PM_PRODUCTIONDATA.SPECIALREPAIRFLAG ,PCP.PROCEDURENAME INPROCEDURENAME FROM TP_PM_PRODUCTIONDATA LEFT JOIN TP_PM_INPRODUCTION INP ON INP.BARCODE = TP_PM_PRODUCTIONDATA.BARCODE LEFT JOIN TP_PC_PROCEDURE PCP ON PCP.PROCEDUREID = INP.PROCEDUREID LEFT JOIN TP_SYS_PROCEDUREMODELTYPE ON TP_PM_PRODUCTIONDATA.MODELTYPE = TP_SYS_PROCEDUREMODELTYPE.PROCEDUREMODELTYPEID LEFT JOIN TP_MST_ORGANIZATION ON TP_PM_PRODUCTIONDATA.ORGANIZATIONID = TP_MST_ORGANIZATION.ORGANIZATIONID LEFT JOIN TP_MST_DATADICTIONARY ON TP_PM_PRODUCTIONDATA.KILNCARPOSITION = TP_MST_DATADICTIONARY.DICTIONARYID LEFT JOIN TP_MST_GOODSLEVEL ON TP_PM_PRODUCTIONDATA.GOODSLEVELID = TP_MST_GOODSLEVEL.GOODSLEVELID LEFT JOIN TP_MST_GOODS ON TP_PM_PRODUCTIONDATA.GOODSID = TP_MST_GOODS.GOODSID LEFT JOIN TP_MST_GOODSTYPE ON TP_MST_GOODS.GOODSTYPEID = TP_MST_GOODSTYPE.GOODSTYPEID LEFT JOIN TP_MST_USER ON TP_PM_PRODUCTIONDATA.CREATEUSERID = TP_MST_USER.USERID LEFT JOIN TP_MST_LOGO ON TP_PM_PRODUCTIONDATA.LOGOID = TP_MST_LOGO.LOGOID WHERE TP_PM_PRODUCTIONDATA.VALUEFLAG = '1' AND TP_PM_PRODUCTIONDATA.PROCEDUREID = :ProcedureIDS AND (TP_PM_PRODUCTIONDATA.CREATETIME >= :begindate AND TP_PM_PRODUCTIONDATA.CREATETIME <= :enddate) "; parameters.Add(new OracleParameter(":ProcedureIDS", OracleDbType.Int32, searchEntity.ProcedureID, ParameterDirection.Input)); parameters.Add(new OracleParameter(":begindate", OracleDbType.Date, searchEntity.BeginDate, ParameterDirection.Input)); parameters.Add(new OracleParameter(":enddate", OracleDbType.Date, searchEntity.EndDate, ParameterDirection.Input)); if (!string.IsNullOrEmpty(searchEntity.BarCode)) { sqlString = sqlString + " AND TP_PM_PRODUCTIONDATA.BARCODE = :barCode "; parameters.Add(new OracleParameter(":barCode", OracleDbType.Varchar2, searchEntity.BarCode, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.GoodsCode)) { sqlString = sqlString + " AND INSTR(TP_PM_PRODUCTIONDATA.GOODSCODE, :goodsCode) > 0 "; parameters.Add(new OracleParameter(":goodsCode", OracleDbType.Varchar2, searchEntity.GoodsCode, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.GoodsName)) { sqlString = sqlString + " AND INSTR(TP_PM_PRODUCTIONDATA.GOODSNAME, :goodsName) > 0 "; parameters.Add(new OracleParameter(":goodsName", OracleDbType.Varchar2, searchEntity.GoodsName, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.UserCode)) { sqlString = sqlString + " AND INSTR(TP_PM_PRODUCTIONDATA.USERCODE, :userCode) > 0 "; parameters.Add(new OracleParameter(":userCode", OracleDbType.Varchar2, searchEntity.UserCode, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.OrganizationID + "")) { sqlString = sqlString + " AND ( :organizationID = 0 OR TP_PM_PRODUCTIONDATA.ORGANIZATIONID = :organizationID ) "; parameters.Add(new OracleParameter(":organizationID", OracleDbType.Int32, searchEntity.OrganizationID, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.Remarks)) { sqlString = sqlString + " AND INSTR(TP_PM_PRODUCTIONDATA.REMARKS, :remarks) > 0 "; parameters.Add(new OracleParameter(":remarks", OracleDbType.Varchar2, searchEntity.Remarks, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.IsRework)) { sqlString = sqlString + " AND INSTR( :isReworked, TP_PM_PRODUCTIONDATA.ISREWORKED) > 0 "; parameters.Add(new OracleParameter(":isReworked", OracleDbType.Varchar2, searchEntity.IsRework, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.KilnCode)) { sqlString = sqlString + " AND TP_PM_PRODUCTIONDATA.KILNCODE = :pKilnCode "; parameters.Add(new OracleParameter(":pKilnCode", OracleDbType.Varchar2, searchEntity.KilnCode, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.KilnCarCode)) { sqlString = sqlString + " AND TP_PM_PRODUCTIONDATA.KILNCARCODE = :pKilnCarCode "; parameters.Add(new OracleParameter(":pKilnCarCode", OracleDbType.Varchar2, searchEntity.KilnCarCode, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.KilnCarPosition + "")) { sqlString = sqlString + " AND TP_PM_PRODUCTIONDATA.KILNCARPOSITION = :pKilnCarPosition "; parameters.Add(new OracleParameter(":pKilnCarPosition", OracleDbType.Int32, searchEntity.KilnCarPosition, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.KilnCarPosition + "")) { sqlString = sqlString + " AND TP_PM_PRODUCTIONDATA.KILNCARPOSITION = :pKilnCarPosition "; parameters.Add(new OracleParameter(":pKilnCarPosition", OracleDbType.Int32, searchEntity.KilnCarPosition, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(user.UserID + "")) { sqlString = sqlString + @" AND ( TP_PM_PRODUCTIONDATA.CREATEUSERID = : userID OR EXISTS ( SELECT 1 FROM TP_MST_USERPURVIEW UP WHERE UP.PURVIEWTYPE = 4 AND ( UP.PURVIEWID = - 1 OR UP.PURVIEWID = TP_PM_PRODUCTIONDATA.CREATEUSERID ) AND UP.USERID = : userID ) ) "; parameters.Add(new OracleParameter(":userID", OracleDbType.Int32, user.UserID, ParameterDirection.Input)); } if (!string.IsNullOrEmpty(searchEntity.GoodsTypeCode)) { sqlString = sqlString + " AND INSTR(TP_MST_GOODSTYPE.GOODSTYPECODE, :goodstypecode) > 0 "; parameters.Add(new OracleParameter(":goodstypecode", OracleDbType.Varchar2, searchEntity.GoodsTypeCode, ParameterDirection.Input)); } oracleConn.Open(); DataSet ds = oracleConn.GetSqlResultToDs(sqlString, parameters.ToArray()); if (ds != null && ds.Tables.Count > 0) { return ds.Tables[0]; } else { return null; } } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 使用存储过程PRO_PM_GetProductionData获取生产工序计件数据 /// /// 生产数据实体类 /// 用户基本信息 /// DataTable public static DataTable GetProductionDataPDA(SearchProductionDataEntity searchEntity, SUserInfo user) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); string procsql = "";//PRO_PM_GetProductionDataPDA //xuwei modify 2019-12-13 修正读取当前工序 是编辑还是插入 procsql = @"SELECT TP_PM_ProductionDataIn.ProductionDataID FROM TP_PM_ProductionDataIn where valueflag=1 and 1=1 and ProcedureID=" + searchEntity.ProcedureID + " and barcode='" + searchEntity.BarCode + "' order by TP_PM_ProductionDataIn.ProductionDataid desc"; //procsql = $@" // SELECT // TP_PM_PRODUCTIONDATAIN.PRODUCTIONDATAID // FROM // TP_PM_PRODUCTIONDATAIN // WHERE // VALUEFLAG = 1 // AND 1 = 1 // AND PROCEDUREID = {searchEntity.ProcedureID} // AND BARCODE = '{searchEntity.BarCode}' // AND PRODUCTIONDATAID = ( SELECT MAX( PRODUCTIONDATAID ) FROM TP_PM_PRODUCTIONDATAIN WHERE BARCODE = '{searchEntity.BarCode}' ) // ORDER BY // TP_PM_PRODUCTIONDATAIN.PRODUCTIONDATAID DESC //"; try { //IDataParameter[] paras = new OracleParameter[] //{ // new OracleParameter("in_procedureID",OracleDbType.Int32, searchEntity.ProcedureID, ParameterDirection.Input), // new OracleParameter("in_barCode", OracleDbType.Varchar2,searchEntity.BarCode, ParameterDirection.Input), // new OracleParameter("in_goodsCode", OracleDbType.Varchar2,searchEntity.GoodsCode, ParameterDirection.Input), // new OracleParameter("in_goodsName", OracleDbType.Varchar2,searchEntity.GoodsName, ParameterDirection.Input), // new OracleParameter("in_userCode", OracleDbType.Varchar2,searchEntity.UserCode, ParameterDirection.Input), // new OracleParameter("in_organizationID", OracleDbType.Int32,searchEntity.OrganizationID, ParameterDirection.Input), // new OracleParameter("in_remarks", OracleDbType.Varchar2,searchEntity.Remarks, ParameterDirection.Input), // new OracleParameter("in_isReworked", OracleDbType.Varchar2,searchEntity.IsRework, ParameterDirection.Input), // new OracleParameter("in_begindate", OracleDbType.Date,searchEntity.BeginDate, ParameterDirection.Input), // new OracleParameter("in_enddate", OracleDbType.Date,searchEntity.EndDate, ParameterDirection.Input), // new OracleParameter("in_accountID", OracleDbType.Int32,user.AccountID, ParameterDirection.Input), // new OracleParameter("in_userID", OracleDbType.Int32,user.UserID, ParameterDirection.Input), // new OracleParameter("in_kilnCode", OracleDbType.Varchar2,searchEntity.KilnCode, ParameterDirection.Input), // new OracleParameter("in_kilnCarCode", OracleDbType.Varchar2,searchEntity.KilnCarCode, ParameterDirection.Input), // new OracleParameter("in_kilnCarPosition", OracleDbType.Int32,searchEntity.KilnCarPosition, ParameterDirection.Input), // new OracleParameter("in_currentUserID", OracleDbType.Int32,user.UserID, ParameterDirection.Input), // new OracleParameter("out_result", OracleDbType.RefCursor, ParameterDirection.Output), // new OracleParameter("in_goodsTypeCode", OracleDbType.Varchar2,searchEntity.GoodsTypeCode, ParameterDirection.Input), //}; oracleConn.Open(); DataSet returnDs = oracleConn.GetSqlResultToDs(procsql); if (returnDs != null && returnDs.Tables.Count > 0) { return returnDs.Tables[0]; } else { return null; } } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 获取次品产品条码允许编辑 /// /// 产品条码 /// Datase public static DataSet GetSubstandardInfo(string barcode) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { //获取窑炉管理数据 string sqlString = "Select barcode from TP_PM_InProductionTrash where barcode=:barcode"; Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[] { new Oracle.ManagedDataAccess.Client.OracleParameter(":barcode",barcode) }; oracleConn.Open(); DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); oracleConn.Close(); return result; } catch (Exception ex) { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion #region 获取生产工序实体 /// /// 获取生产工序实体 /// /// 工序ID /// 用户基本信息 /// ProcedureEntity实体类 public static ProcedureEntity GetProcedureDataEntityByID(int procedureID, SUserInfo userInfo) { ProcedureEntity procedureDataentity = new ProcedureEntity(); IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); string procsql = "pro_pm_searchProcedurbyID"; try { IDataParameter[] paras = new OracleParameter[] { new OracleParameter("in_procedureID", OracleDbType.Int32, procedureID, ParameterDirection.Input), new OracleParameter("out_result", OracleDbType.RefCursor, ParameterDirection.Output) }; oracleConn.Open(); DataSet returnDs = oracleConn.ExecStoredProcedure(procsql, paras); if (returnDs != null && returnDs.Tables.Count > 0 && returnDs.Tables[0].Rows.Count > 0) { DataRow row = returnDs.Tables[0].Rows[0]; procedureDataentity = DataConvert.DataRowConvertToObject(row); return procedureDataentity; } else { return null; } } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion #region 判断操作人员是否具有工序权限 /// /// 判断操作人员是否具有工序权限 /// /// 工序ID /// 工号ID /// DataSet public static DataSet DecideUserInProcedure(int procedureID, int userID) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); string procsql = "pro_pm_DecideUserInProcedure"; try { IDataParameter[] paras = new OracleParameter[] { new OracleParameter("in_procedureID",OracleDbType.Int32,procedureID,ParameterDirection.Input), new OracleParameter("in_userID",OracleDbType.Int32,userID,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output) }; oracleConn.Open(); DataSet returnDs = oracleConn.ExecStoredProcedure(procsql, paras); return returnDs; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion #region 检验条码 /// /// 检验条码(非首节点) /// /// 工序ID /// 产品条码 /// DataSet public static DataSet CheckBarcode(int procedureID, string barcode, SUserInfo sUserInfo, bool isInn = false) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); // 2019-1016 if (!isInn) { //xuwei fix 2019-09-26 使用通用方法判定 //if (PMModuleLogicDAL.IsNodeBegin(oracleConn, barcode) == 1) //{ // return CheckBarcodeDeliverMud(procedureID, barcode, sUserInfo, true); //} string sql1 = "select NodeType from tp_pc_procedure where procedureid=:procedureid"; string NodeType = oracleConn.GetSqlResultToObj(sql1, new OracleParameter[] { new OracleParameter(":procedureid", procedureID) }) + ""; // 89[3#一检半检] 特殊处理 if (NodeType == "1" || procedureID == 89) { int isNodeBegin = PMModuleLogicDAL.IsNodeBegin(oracleConn, barcode); if (isNodeBegin == 1) { return CheckBarcodeDeliverMud(procedureID, barcode, sUserInfo, true); } } } DataTable dtBarCode = PMModuleLogic.CreateBarCodeResultTable(); DataSet returnDs = new DataSet(); DataRow dr = dtBarCode.NewRow(); string sqlString = string.Empty; DataSet ds = null; #region 查出工序条码类型 wangx 2017-3-13 string sqlBarcode = GetConvertBarCode(oracleConn, barcode, procedureID, sUserInfo); if (sqlBarcode == string.Empty) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "无效条码"; dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = 0; dtBarCode.Rows.Add(dr); returnDs.Tables.Add(dtBarCode); return returnDs; } else { dr[Constant.BarCodeResultTableColumns.out_barcode.ToString()] = sqlBarcode; barcode = sqlBarcode; } #endregion OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_procedureid",OracleDbType.Int32,procedureID,ParameterDirection.Input), new OracleParameter("in_barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), new OracleParameter("out_errMsg",OracleDbType.NVarchar2,2000,null,ParameterDirection.Output), new OracleParameter("out_goodsID",OracleDbType.Int32,ParameterDirection.Output), new OracleParameter("out_goodsCode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_goodsName",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_groutingUserCode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_errMsg_Status",OracleDbType.Varchar2,50,"",ParameterDirection.Output), }; oracleConn.ExecStoredProcedure("PRO_PM_CheckBarcode", paras); dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = paras[2].Value.ToString() == "null" ? "" : paras[2].Value.ToString().Replace("\\n\\r", "\n\r"); //if (!string.IsNullOrEmpty(paras[2].Value.ToString() == "null" ? "" : paras[2].Value.ToString().Replace("\\n\\r", "\n\r"))) //{ // AddBarCodeMissing(oracleConn, barcode, procedureID, sUserInfo); //} int missFlag = 0; if (paras[7].Value != null && paras[7].Value.ToString() == "1") { missFlag = AddBarCodeMissing(oracleConn, barcode, procedureID, sUserInfo); } dr[Constant.BarCodeResultTableColumns.out_goodsID.ToString()] = paras[3].Value.ToString() == "null" ? 0 : paras[3].Value; dr[Constant.BarCodeResultTableColumns.out_goodsCode.ToString()] = paras[4].Value.ToString() == "null" ? "" : paras[4].Value; dr[Constant.BarCodeResultTableColumns.out_goodsName.ToString()] = paras[5].Value.ToString() == "null" ? "" : paras[5].Value; dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = paras[6].Value.ToString() == "null" ? 0 : paras[6].Value; dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = missFlag; DataSet defectSet = new DataSet(); if (paras[7].Value.ToString() == "null") //只有正确的条码，读注浆信息 { sqlString = @"select tp_pm_inproduction.GoodsName, tp_pm_inproduction.GroutingUserID, tp_pm_inproduction.GroutingUserCode, tp_pm_inproduction.GroutingNum, tp_pm_inproduction.GroutingMouldCode as MouldCode, tp_pm_inproduction.ispublicbody, tp_pm_inproduction.Groutingdate, tp_pm_inproduction.SpecialRepairFlag, tp_pm_inproduction.logoid, g.goodsid,g.goodscode,g.PlateLimitNum,g.DeliverLimitCycle, --nvl(g.MaterialCode,g.GoodsCode) as GOODSMODELforCheck, -- 用物料编码验证产品型号是否一致 nvl(gdd.MaterialCode, nvl(g.MaterialCode,g.GoodsCode)) ||'#'|| to_char(g.GOODS_LINE_TYPE) as GOODSMODELforCheck, -- 用物料编码验证产品型号是否一致 --gt.gt.goodstypecode,gt.goodstypename,a.workcode, tp_mst_logo.logocode, u.username as GroutingUserName, tp_mst_logo.logoname , case when pg.goodsid=g.goodsid then 1 else 0 end as refireflag from tp_pm_inproduction left join TP_PM_GroutingDailyDetail gdd on gdd.GroutingDailyDetailID = tp_pm_inproduction.GroutingDailyDetailID left join tp_mst_logo on tp_pm_inproduction.logoid=tp_mst_logo.logoid inner join tp_mst_user u on u.userid = gdd.userid LEFT JOIN TP_MST_GOODS G ON G.GoodsID = tp_pm_inproduction.goodsid LEFT JOIN TP_PC_PROCEDUREGOODS PG ON PG.GOODSID = G.GOODSID AND PG.PROCEDUREID = 138 --left join tp_mst_goodstype gt on gt.goodstypeid = g.goodstypeid --left join tp_mst_account a on a.accountid = gdd.accountid where tp_pm_inproduction.BarCode=:barcode"; paras = new OracleParameter[]{ new OracleParameter(":barcode",barcode), }; //20221129 by qinqi 查询上一次缺陷 string sqlString1 = @"SELECT pi.* FROM TP_PM_ProductionDataIn pi where pi.Barcode= :barcode and pi.MODELTYPE IN (-1, -4, -5) and pi.ValueFlag = '1' order by pi.CREATETIME desc"; ds = oracleConn.GetSqlResultToDs(sqlString, paras); DataSet dataTable = oracleConn.GetSqlResultToDs(sqlString1, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { if (dataTable != null && dataTable.Tables.Count > 0 && dataTable.Tables[0].Rows.Count > 0) { dr["DefectFlagID"] = Convert.ToInt32(dataTable.Tables[0].Rows[0]["goodsleveltypeid"]); } //dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = ds.Tables[0].Rows[0]["GroutingUserCode"].ToString(); //dr[Constant.BarCodeResultTableColumns.out_groutingNum.ToString()] = ds.Tables[0].Rows[0]["GroutingNum"].ToString(); //dr[Constant.BarCodeResultTableColumns.out_mouldCode.ToString()] = ds.Tables[0].Rows[0]["MouldCode"].ToString(); //dr[Constant.BarCodeResultTableColumns.out_ispublicbody.ToString()] = ds.Tables[0].Rows[0]["ispublicbody"].ToString(); //dr[Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString()] = "0"; //dr[Constant.BarCodeResultTableColumns.out_groutingdate.ToString()] = ds.Tables[0].Rows[0]["Groutingdate"].ToString(); //dr[Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString()] = ds.Tables[0].Rows[0]["SpecialRepairFlag"].ToString(); //dr[Constant.BarCodeResultTableColumns.out_isReFire.ToString()] = "0"; //dr[Constant.BarCodeResultTableColumns.out_logoID.ToString()] = ds.Tables[0].Rows[0]["logoid"].ToString(); //dr[Constant.BarCodeResultTableColumns.out_logoCode.ToString()] = ds.Tables[0].Rows[0]["logoCode"].ToString(); //dr[Constant.BarCodeResultTableColumns.out_logoName.ToString()] = ds.Tables[0].Rows[0]["logoName"].ToString(); dr["GOODSMODELforCheck"] = ds.Tables[0].Rows[0]["GOODSMODELforCheck"].ToString(); //// hegii 三水 特殊处理 大件高压可以和普通合并装板，小件高压不能和普通合并装板 //if (ds.Tables[0].Rows[0]["workcode"].ToString() == "5020" && // ds.Tables[0].Rows[0]["goodstypename"].ToString() == "小件") //{ // dr["GOODSMODELforCheck"] = ds.Tables[0].Rows[0]["goodscode"].ToString(); //} dr["PlateLimitNum"] = ds.Tables[0].Rows[0]["PlateLimitNum"]; dr[Constant.BarCodeResultTableColumns.out_goodsID.ToString()] = ds.Tables[0].Rows[0]["GoodsID"].ToString(); dr[Constant.BarCodeResultTableColumns.out_goodsCode.ToString()] = ds.Tables[0].Rows[0]["GoodsCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_goodsName.ToString()] = ds.Tables[0].Rows[0]["GoodsName"].ToString(); dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = 0; dr[Constant.BarCodeResultTableColumns.out_groutingUserName.ToString()] = ds.Tables[0].Rows[0]["GroutingUserName"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = ds.Tables[0].Rows[0]["GroutingUserCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingUserID.ToString()] = ds.Tables[0].Rows[0]["GroutingUserID"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingNum.ToString()] = ds.Tables[0].Rows[0]["GroutingNum"].ToString(); dr[Constant.BarCodeResultTableColumns.out_mouldCode.ToString()] = ds.Tables[0].Rows[0]["MouldCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbody.ToString()] = ds.Tables[0].Rows[0]["ispublicbody"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_groutingdate.ToString()] = ds.Tables[0].Rows[0]["Groutingdate"].ToString(); dr[Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString()] = ds.Tables[0].Rows[0]["SpecialRepairFlag"].ToString(); dr[Constant.BarCodeResultTableColumns.out_isReFire.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_logoID.ToString()] = ds.Tables[0].Rows[0]["logoid"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoCode.ToString()] = ds.Tables[0].Rows[0]["logocode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoName.ToString()] = ds.Tables[0].Rows[0]["logoname"].ToString(); dr[Constant.BarCodeResultTableColumns.out_deliverLimitCycle.ToString()] = ds.Tables[0].Rows[0]["DeliverLimitCycle"].ToString(); } //获取上一次成品检验的缺陷 if (dataTable != null && dataTable.Tables.Count > 0 && dataTable.Tables[0].Rows.Count > 0) { sqlString1 = @"select TP_PM_Defect.DefectID ,replace( concat(TP_PM_Defect.DefectCode||'->',TP_PM_Defect.DefectName) , concat(TP_PM_Defect.DefectCode||'->',TP_PM_Defect.DefectCode||'->'),TP_PM_Defect.DefectCode||'->' ) as DefectName, TP_PM_Defect.DefectCode, TP_PM_Defect.DefectPositionID, replace( concat(TP_PM_Defect.DefectPositionCode||'->',TP_PM_Defect.DefectPositionName) , concat(TP_PM_Defect.DefectPositionCode||'->',TP_PM_Defect.DefectPositionCode||'->'),TP_PM_Defect.DefectPositionCode||'->' ) as DefectPositionName, TP_PM_Defect.DefectPositionCode, TP_PM_Defect.DefectProcedureID, TP_PM_Defect.DefectProcedureCode, TP_PM_Defect.DefectProcedureName,TP_PM_Defect.DefectUserID ,TP_PM_Defect.DefectUserCode,TP_PM_Defect.DefectUserName, TP_PM_Defect.DefectProductionDataID, TP_PM_Defect.Remarks AS DefectRemarks , TP_MST_Jobs.JobsID AS Jobs, TP_MST_Jobs.JobsName AS JobsText, TP_MST_DefectFine.DefectFineID, TP_MST_DefectFine.DefectFineCode AS DefectFineValue, TP_PM_Defect.MissedUserID, TP_PM_Defect.MissedUserCode, TP_PM_Defect.MissedUserName, TP_PM_Defect.DefectDeductionNum, TP_PM_Defect.SpecialDefect, TP_PM_Defect.CheckTime, (SELECT listagg(to_char(s.staffname), ',') within GROUP(ORDER BY s.staffid) FROM tp_pm_defectresponsible dp INNER JOIN tp_hr_staff s ON s.staffid = dp.staffid where dp.productiondefectid = TP_PM_Defect.PRODUCTIONDEFECTID) DefectStaffNames , ROWNUM-1 as TempCount from TP_PM_Defect left join TP_MST_Jobs on TP_PM_Defect.DefectJobs=TP_MST_Jobs.JobsID left join TP_MST_DefectFine on TP_PM_Defect.DefectFine=TP_MST_DefectFine.DefectFineID where TP_PM_Defect.ProductionDataID = :ProductionDataID"; OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":ProductionDataID",Convert.ToInt32(dataTable.Tables[0].Rows[0]["ProductionDataID"])), }; defectSet = oracleConn.GetSqlResultToDs(sqlString1, paras1); } } dtBarCode.Rows.Add(dr); returnDs.Tables.Add(dtBarCode); if (defectSet != null && defectSet.Tables.Count > 0 && defectSet.Tables[0].Rows.Count > 0) { defectSet.Tables[0].TableName = "DefectTable"; returnDs.Tables.Add(defectSet.Tables[0].Copy()); } return returnDs; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #region 2017-3-13 /// /// 根据条码及工序判断是否漏扫 /// /// 产品条码 /// 校验工序ID /// 用户基本信息 /// DataSet public static string GetConvertBarCode(IDBConnection oracleTrConn, string barcode, int produceid, SUserInfo sUserInfo) { try { #region 查出工序条码类型 wangx 2017-3-13 string sqlString = "select BarCodeFlag from TP_PC_Procedure where ProcedureID=:ProcedureID"; OracleParameter[] paras2 = new OracleParameter[]{ new OracleParameter(":ProcedureID",produceid), }; DataSet ds = oracleTrConn.GetSqlResultToDs(sqlString, paras2); int BarCodeFlag = 0; string returnBarcode = string.Empty;//返回条码 if (ds != null && ds.Tables[0].Rows.Count > 0) { BarCodeFlag = Convert.ToInt32(ds.Tables[0].Rows[0]["BarCodeFlag"]); if (BarCodeFlag > 0) { //sqlString = @"select FUN_CMN_GetBarCode(:barcode,:procedureid,:accountid) From DUAL"; sqlString = @"select FUN_CMN_GetBarCode(:barcode,null,:accountid) From DUAL"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), //new OracleParameter(":procedureid",OracleDbType.Int32, produceid,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { returnBarcode = ds.Tables[0].Rows[0][0].ToString(); } return returnBarcode; } else { returnBarcode = barcode; } } #endregion return returnBarcode; } catch (Exception ex) { throw ex; } } #endregion /// /// 检验条码pda /// /// 工序ID /// 产品条码 /// DataSet public static DataSet CheckBarcodePDA(int procedureID, string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); //2020-03-10 chenxy fix 中陶出现非必须首节点 提示 未进入生产流程，应在开始工序输入 string sql1 = "select NodeType from tp_pc_procedure where procedureid=:procedureid"; string NodeType1 = oracleConn.GetSqlResultToObj(sql1, new OracleParameter[] { new OracleParameter(":procedureid", procedureID) }) + ""; if (NodeType1 == "1") { int isNodeBegin = PMModuleLogicDAL.IsNodeBegin(oracleConn, barcode); if (isNodeBegin == 1) { return CheckBarcodeDeliverMud(procedureID, barcode, sUserInfo, true); } } //fix end DataTable dtBarCode = PMModuleLogic.CreateBarCodeResultTable(); DataSet returnDs = new DataSet(); DataTable leakFlag4Table = new DataTable(); DataTable leakFlag5Table = new DataTable(); DataTable leakFlag6Table = new DataTable(); DataSet defectSet = new DataSet(); DataRow dr = dtBarCode.NewRow(); #region 查出工序条码类型 wangx 2017-3-13 string sqlBarcode = GetConvertBarCode(oracleConn, barcode, procedureID, sUserInfo); if (sqlBarcode == string.Empty) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "无效条码"; dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = 0; dtBarCode.Rows.Add(dr); returnDs.Tables.Add(dtBarCode); return returnDs; } else { dr[Constant.BarCodeResultTableColumns.out_barcode.ToString()] = sqlBarcode; barcode = sqlBarcode; } #endregion OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_procedureid",OracleDbType.Int32,procedureID,ParameterDirection.Input), new OracleParameter("in_barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), new OracleParameter("out_errMsg",OracleDbType.NVarchar2,2000,null,ParameterDirection.Output), new OracleParameter("out_goodsID",OracleDbType.Int32,ParameterDirection.Output), new OracleParameter("out_goodsCode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_goodsName",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_groutingUserCode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_errMsg_Status",OracleDbType.Varchar2,50,"",ParameterDirection.Output), }; oracleConn.ExecStoredProcedure("PRO_PM_CheckBarcode", paras); dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = paras[2].Value.ToString() == "null" ? "" : paras[2].Value.ToString().Replace("\\n\\r", "\n\r"); //if (!string.IsNullOrEmpty(paras[2].Value.ToString() == "null" ? "" : paras[2].Value.ToString().Replace("\\n\\r", "\n\r"))) //{ // AddBarCodeMissing(oracleConn, barcode, procedureID, sUserInfo); //} int missFlag = 0; if (paras[7].Value != null && paras[7].Value.ToString() == "1") { missFlag = AddBarCodeMissing(oracleConn, barcode, procedureID, sUserInfo); } dr[Constant.BarCodeResultTableColumns.out_goodsID.ToString()] = paras[3].Value.ToString() == "null" ? 0 : paras[3].Value; dr[Constant.BarCodeResultTableColumns.out_goodsCode.ToString()] = paras[4].Value.ToString() == "null" ? "" : paras[4].Value; dr[Constant.BarCodeResultTableColumns.out_goodsName.ToString()] = paras[5].Value.ToString() == "null" ? "" : paras[5].Value; dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = paras[6].Value.ToString() == "null" ? 0 : paras[6].Value; dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = missFlag; if (paras[7].Value.ToString() == "null") //只有正确的条码，读注浆信息 { string sql = "select NodeType from tp_pc_procedure where procedureid=:procedureid"; paras = new OracleParameter[]{ new OracleParameter(":procedureid",procedureID), }; DataSet ds = oracleConn.GetSqlResultToDs(sql, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { string sqlString = ""; int NodeType = Convert.ToInt32(ds.Tables[0].Rows[0]["NodeType"]); //xuwei fix 2019-09-26 使用通用方法判定 // 2019-1016 //if (PMModuleLogicDAL.IsNodeBegin(oracleConn, barcode) == 1) //{ // NodeType = (int)Constant.ProcedureNodeType.Begin; //} if (NodeType == (int)Constant.ProcedureNodeType.Begin) { int isNodeBegin = PMModuleLogicDAL.IsNodeBegin(oracleConn, barcode); if (isNodeBegin == 0) { NodeType = (int)Constant.ProcedureNodeType.Middle; } } if (NodeType == (int)Constant.ProcedureNodeType.Begin) //开始节点 { sqlString = @"select TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode, tp_pm_groutingdailydetail.groutingcount as GroutingNum, tp_pm_groutingdailydetail.GroutingMouldCode as MouldCode, 0 as ispublicbody, tp_pm_groutingdailydetail.Groutingdate, tp_pm_groutingdailydetail.SpecialRepairFlag, tp_mst_logo.logoid, tp_mst_logo.logocode, tp_mst_logo.logoname from tp_pm_groutingdailydetail left join tp_mst_logo on tp_pm_groutingdailydetail.logoid=tp_mst_logo.logoid where tp_pm_groutingdailydetail.BarCode=:barcode"; paras = new OracleParameter[]{ new OracleParameter(":barcode",barcode), }; ds = oracleConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = ds.Tables[0].Rows[0]["GroutingUserCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingNum.ToString()] = ds.Tables[0].Rows[0]["GroutingNum"].ToString(); dr[Constant.BarCodeResultTableColumns.out_mouldCode.ToString()] = ds.Tables[0].Rows[0]["MouldCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbody.ToString()] = ds.Tables[0].Rows[0]["ispublicbody"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_groutingdate.ToString()] = ds.Tables[0].Rows[0]["Groutingdate"].ToString(); dr[Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString()] = ds.Tables[0].Rows[0]["SpecialRepairFlag"].ToString(); dr[Constant.BarCodeResultTableColumns.out_isReFire.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_logoID.ToString()] = ds.Tables[0].Rows[0]["logoid"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoCode.ToString()] = ds.Tables[0].Rows[0]["logoCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoName.ToString()] = ds.Tables[0].Rows[0]["logoName"].ToString(); } } else { sqlString = @"select nvl(gdd.MaterialCode, nvl(g.MaterialCode,g.GoodsCode)) MaterialCode, TP_PM_InProduction.GroutingUserCode, TP_PM_InProduction.GroutingNum, TP_PM_InProduction.GroutingMouldCode as MouldCode, TP_PM_InProduction.ispublicbody, (select tp_pm_inproductiontrash.ispublicbody from tp_pm_inproductiontrash where tp_pm_inproductiontrash.BarCode=:barcode) as ispublicbodyTrach, TPPD.REWORKPROCEDUREID REWORKPROCEDUREID, TP_PM_InProduction.Groutingdate, TP_PM_InProduction.SpecialRepairFlag, TP_PM_InProduction.IsReFire, TP_PM_InProduction.ISLENGBU, tp_mst_logo.logoid, g.goodsid,g.goodscode,g.PlateLimitNum, --nvl(g.MaterialCode,g.GoodsCode) as GOODSMODELforCheck, -- 用物料编码验证产品型号是否一致 nvl(gdd.MaterialCode, nvl(g.MaterialCode,g.GoodsCode)) ||'#'|| to_char(g.GOODS_LINE_TYPE) as GOODSMODELforCheck, -- 用物料编码验证产品型号是否一致 --nvl(gdd.MaterialCode, g.GoodsCode) as GOODSMODELforCheck, -- 用物料编码验证产品型号是否一致 tp_mst_logo.logocode, tp_mst_logo.logoname, glaze.DICTIONARYVALUE AS GLAZENAME, --xuwei add 2020-06-11 漏气 内漏 标识 gdd.LEAKFLAG1, gdd.LEAKFLAG2, gdd.LEAKFLAG3, gdd.LEAKFLAG4, gdd.LEAKFLAG5, gdd.LEAKFLAG7, decode(TP_PM_InProduction.SpecialRepairFlag,'1','是','0','否') as SpecialRepairflagName, decode(TP_PM_InProduction.IsReFire,'6','是','0','否') as IsReFireName, DECODE(TP_PM_INPRODUCTION.ISLENGBU, '1', '是', '0', '否') AS ISLENGBUNAME, decode(gdd.LEAKFLAG1,'1','合格','0','不合格','未检测') as LEAKFLAG1Name, decode(gdd.LEAKFLAG2,'1','合格','0','不合格','未检测') as LEAKFLAG2Name, decode(gdd.LEAKFLAG3,'1','合格','0','不合格','未检测') as LEAKFLAG3Name, decode(gdd.LEAKFLAG4,'1','合格','0','不合格','未检测') as LEAKFLAG4Name, decode(gdd.LEAKFLAG5,'1','合格','0','不合格','未检测') as LEAKFLAG5Name, decode(gdd.LEAKFLAG7,'1','合格','0','不合格','未检测') as LEAKFLAG7Name, gdd.InspectionLevel, gdd.PackingDefect, gdd.InspectionGoodsLevel, gdd.OFFLINEFLAG, gdd.RECYCLINGFLAG, G.WATERLABELCODE, G.GOODS_LINE_TYPE, case when pg.goodsid=g.goodsid then 1 else 0 end as refireflag from TP_PM_InProduction left join TP_PM_GroutingDailyDetail gdd on gdd.GroutingDailyDetailID = tp_pm_inproduction.GroutingDailyDetailID left join tp_mst_logo on gdd.logoid=tp_mst_logo.logoid LEFT JOIN TP_MST_GOODS G ON G.GoodsID = tp_pm_inproduction.goodsid LEFT JOIN TP_MST_DataDictionary glaze ON glaze.DICTIONARYID = gdd.GLAZETYPEID LEFT JOIN (SELECT T.REWORKPROCEDUREID,T.BARCODE FROM(SELECT REWORKPROCEDUREID,BARCODE FROM TP_PM_PRODUCTIONDATA WHERE BARCODE = :barcode ORDER BY CREATETIME DESC) T WHERE ROWNUM = 1) TPPD ON TPPD.BARCODE = TP_PM_InProduction.BARCODE LEFT JOIN TP_PC_PROCEDUREGOODS PG ON PG.GOODSID = G.GOODSID AND PG.PROCEDUREID = 138 where TP_PM_InProduction.BarCode=:barcode"; paras = new OracleParameter[]{ new OracleParameter(":barcode",barcode), }; ds = oracleConn.GetSqlResultToDs(sqlString, paras); // 取最新的窑炉批次号 string sqlString2 = @" SELECT KILNCARBATCHNO FROM TP_PM_PRODUCTIONDATA WHERE KILNCARBATCHNO IS NOT NULL AND BARCODE = :barcode ORDER BY CREATETIME DESC"; object kilncarbatchno = oracleConn.GetSqlResultToObj(sqlString2, paras); // 取当前批次号下最新的检验类型的生产数据 sqlString2 = @" SELECT PI.* FROM TP_PM_PRODUCTIONDATA PI WHERE PI.MODELTYPE IN (-1, -4, -5) AND PI.VALUEFLAG = '1' AND PI.BARCODE = :BARCODE AND PI.KILNCARBATCHNO = :KILNCARBATCHNO ORDER BY PI.CREATETIME DESC "; paras = new OracleParameter[] { new OracleParameter(":BARCODE", barcode), new OracleParameter(":KILNCARBATCHNO", kilncarbatchno), }; DataSet dataTable = oracleConn.GetSqlResultToDs(sqlString2, paras); //二车间三检后回收再次三检时，会带出三检信息（次品等），此时产品为正品，不带出缺陷及等级信息 // 取回收标识 sqlString2 = @" SELECT gdd.RECYCLINGFLAG FROM TP_PM_GroutingDailyDetail gdd WHERE gdd.BARCODE = :BARCODE "; paras = new OracleParameter[] { new OracleParameter(":BARCODE", barcode), }; DataTable recyclingdata = oracleConn.GetSqlResultToDt(sqlString2, paras); // 取重烧标识 sqlString2 = @" SELECT case when pg.goodsid=gdd.goodsid then 1 else 0 end as refireflag FROM TP_PM_GroutingDailyDetail gdd LEFT JOIN TP_PC_PROCEDUREGOODS PG ON PG.GOODSID = gdd.GOODSID AND PG.PROCEDUREID = 138 WHERE gdd.BARCODE = :BARCODE "; paras = new OracleParameter[] { new OracleParameter(":BARCODE", barcode), }; DataTable refireflagdata = oracleConn.GetSqlResultToDt(sqlString2, paras); if (recyclingdata != null && recyclingdata.Rows.Count > 0) { //回收过，取回收后的检验数据 if (recyclingdata.Rows[0]["RECYCLINGFLAG"].ToString() == "1") { // 取当前批次号下最新的检验类型的生产数据 sqlString2 = @" SELECT PI.* FROM TP_PM_PRODUCTIONDATA PI WHERE PI.MODELTYPE IN (-1, -4, -5) AND PI.VALUEFLAG = '1' AND PI.BARCODE = :BARCODE AND PI.KILNCARBATCHNO = :KILNCARBATCHNO AND PI.CREATETIME > (SELECT max(PDI.createtime) FROM TP_PM_PRODUCTIONDATA PDI WHERE PDI.BARCODE = PI.BARCODE AND PDI.PROCEDUREID = 80) ORDER BY PI.CREATETIME DESC "; paras = new OracleParameter[] { new OracleParameter(":BARCODE", barcode), new OracleParameter(":KILNCARBATCHNO", kilncarbatchno), }; dataTable = oracleConn.GetSqlResultToDs(sqlString2, paras); } } //if (dtIsRefire.Rows.Count == 0) //{ // sqlString2 = @"SELECT pi.isrefire,pi.kilncarbatchno FROM TP_PM_SCRAPPRODUCT pi // where pi.Barcode= :barcode"; // dtIsRefire = oracleConn.GetSqlResultToDt(sqlString2, paras); //} //string isrefire = "0"; //if (dtIsRefire.Rows.Count > 0) // { // isrefire = dtIsRefire.Rows[0]["isrefire"] + ""; //} //DataSet dataTable = new DataSet(); //if (isrefire.Equals("0")) //{ // sqlString2 = @"SELECT pi.* FROM TP_PM_ProductionDataIn pi // where pi.Barcode= :barcode // and pi.MODELTYPE IN (-1, -4, -5) // and pi.ValueFlag = '1' // and pi.isrefire = '0' // order by pi.CREATETIME desc"; // paras = new OracleParameter[]{ // new OracleParameter(":barcode",barcode), // }; // dataTable = oracleConn.GetSqlResultToDs(sqlString2, paras); //} //else //{ // string kilncarbatchno = dtIsRefire.Rows[0]["kilncarbatchno"] + ""; // sqlString2 = @"SELECT pi.* FROM TP_PM_ProductionDataIn pi // where pi.Barcode= :barcode // and pi.kilncarbatchno= :kilncarbatchno // and pi.MODELTYPE IN (-1, -4, -5) // and pi.ValueFlag = '1' // and pi.isrefire = '6' // order by pi.CREATETIME desc"; // paras = new OracleParameter[]{ // new OracleParameter(":barcode", barcode), // new OracleParameter(":kilncarbatchno", kilncarbatchno), // }; // dataTable = oracleConn.GetSqlResultToDs(sqlString2, paras); //} //缺陷 int pdid = 0; if (dataTable != null && dataTable.Tables.Count > 0 && dataTable.Tables[0].Rows.Count > 0) { dr["DefectFlagID"] = Convert.ToInt32(dataTable.Tables[0].Rows[0]["goodsleveltypeid"]); pdid = Convert.ToInt32(dataTable.Tables[0].Rows[0]["PRODUCTIONDATAID"]); // 判断当前条码在最近一次检验后，是否走过冷补或回收 string sqlCheckLengBu = @" SELECT 1 FROM TP_PM_PRODUCTIONDATA P WHERE P.PROCEDUREID IN (135, 80) AND P.BARCODE = :BARCODE AND p.createtime >= (select max(createtime) from TP_PM_PRODUCTIONDATA pd where pd.barcode=p.barcode and pd.MODELTYPE IN (-1, -4, -5) AND pd.VALUEFLAG = '1' AND pd.KILNCARBATCHNO = :KILNCARBATCHNO )"; //AND P.PRODUCTIONDATAID > :PID paras = new OracleParameter[] { new OracleParameter(":BARCODE", barcode), new OracleParameter(":KILNCARBATCHNO", kilncarbatchno), new OracleParameter(":PID", pdid), }; object isExists = oracleConn.GetSqlResultToObj(sqlCheckLengBu, paras); if (isExists == null) { dr["pdid"] = pdid; } } if (ds != null && ds.Tables[0].Rows.Count > 0) { dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = ds.Tables[0].Rows[0]["GroutingUserCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingNum.ToString()] = ds.Tables[0].Rows[0]["GroutingNum"].ToString(); dr[Constant.BarCodeResultTableColumns.out_mouldCode.ToString()] = ds.Tables[0].Rows[0]["MouldCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbody.ToString()] = ds.Tables[0].Rows[0]["ispublicbody"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString()] = ds.Tables[0].Rows[0]["ispublicbodyTrach"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingdate.ToString()] = ds.Tables[0].Rows[0]["Groutingdate"].ToString(); dr[Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString()] = ds.Tables[0].Rows[0]["SpecialRepairFlag"].ToString(); dr[Constant.BarCodeResultTableColumns.out_isReFire.ToString()] = ds.Tables[0].Rows[0]["IsReFire"].ToString(); dr[Constant.BarCodeResultTableColumns.out_isLengBu.ToString()] = ds.Tables[0].Rows[0]["ISLENGBU"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoID.ToString()] = ds.Tables[0].Rows[0]["logoid"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoCode.ToString()] = ds.Tables[0].Rows[0]["logoCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoName.ToString()] = ds.Tables[0].Rows[0]["logoName"].ToString(); dr[Constant.BarCodeResultTableColumns.out_glazeName.ToString()] = ds.Tables[0].Rows[0]["glazename"].ToString(); dr[Constant.BarCodeResultTableColumns.out_MaterialCode.ToString()] = ds.Tables[0].Rows[0]["MaterialCode"].ToString(); //增加漏气标识内漏标识 xuwei add 2020-06-11 dr[Constant.BarCodeResultTableColumns.out_LeakFlag1.ToString()] = ds.Tables[0].Rows[0]["LEAKFLAG1"].ToString(); dr[Constant.BarCodeResultTableColumns.out_LeakFlag2.ToString()] = ds.Tables[0].Rows[0]["LEAKFLAG2"].ToString(); // 增加干补、重烧、内漏等相关字段名称 fubin add 2020-06-30 dr[Constant.BarCodeResultTableColumns.out_LeakFlag3.ToString()] = ds.Tables[0].Rows[0]["LEAKFLAG3"].ToString(); dr[Constant.BarCodeResultTableColumns.out_specialRepairFlagName.ToString()] = ds.Tables[0].Rows[0]["SpecialRepairFlagName"].ToString(); dr[Constant.BarCodeResultTableColumns.out_lengBuName.ToString()] = ds.Tables[0].Rows[0]["ISLENGBUNAME"].ToString(); dr[Constant.BarCodeResultTableColumns.out_isReFireName.ToString()] = ds.Tables[0].Rows[0]["IsReFireName"].ToString(); dr[Constant.BarCodeResultTableColumns.out_LeakFlag1Name.ToString()] = ds.Tables[0].Rows[0]["LEAKFLAG1Name"].ToString(); dr[Constant.BarCodeResultTableColumns.out_LeakFlag2Name.ToString()] = ds.Tables[0].Rows[0]["LEAKFLAG2Name"].ToString(); dr[Constant.BarCodeResultTableColumns.out_LeakFlag3Name.ToString()] = ds.Tables[0].Rows[0]["LEAKFLAG3Name"].ToString(); //增加返工工序 dr["out_ReworkProcedureId"] = ds.Tables[0].Rows[0]["REWORKPROCEDUREID"].ToString(); dr["GOODSMODELforCheck"] = ds.Tables[0].Rows[0]["GOODSMODELforCheck"].ToString(); dr["PlateLimitNum"] = ds.Tables[0].Rows[0]["PlateLimitNum"]; dr["out_LeakFlag4"] = ds.Tables[0].Rows[0]["LEAKFLAG4"].ToString(); dr["out_LeakFlag5"] = ds.Tables[0].Rows[0]["LEAKFLAG5"].ToString(); dr["out_LeakFlag4Name"] = ds.Tables[0].Rows[0]["LEAKFLAG4Name"].ToString(); dr["out_LeakFlag5Name"] = ds.Tables[0].Rows[0]["LEAKFLAG5Name"].ToString(); dr["InspectionLevel"] = ds.Tables[0].Rows[0]["InspectionLevel"].ToString(); dr["PackingDefect"] = ds.Tables[0].Rows[0]["PackingDefect"].ToString(); dr["InspectionGoodsLevel"] = ds.Tables[0].Rows[0]["InspectionGoodsLevel"].ToString(); dr["offlineFlag"] = ds.Tables[0].Rows[0]["OFFLINEFLAG"].ToString(); dr["recyclingFlag"] = ds.Tables[0].Rows[0]["RECYCLINGFLAG"].ToString(); dr["waterLabelCode"] = ds.Tables[0].Rows[0]["WATERLABELCODE"].ToString(); dr["goodsLineType"] = ds.Tables[0].Rows[0]["GOODS_LINE_TYPE"].ToString(); //修磨 dr["out_LeakFlag7"] = ds.Tables[0].Rows[0]["LEAKFLAG7"].ToString(); dr["out_LeakFlag7Name"] = ds.Tables[0].Rows[0]["LEAKFLAG7Name"].ToString(); //if (string.IsNullOrWhiteSpace(ds.Tables[0].Rows[0]["refireflag"].ToString())) //{ // dr["refireflag"] = Convert.ToInt32(refireflagdata.Rows[0]["refireflag"]); //} dr["refireflag"] = Convert.ToInt32(ds.Tables[0].Rows[0]["refireflag"]); //养水不合格，查询养水不合格位置 if (ds.Tables[0].Rows[0]["LEAKFLAG4"].ToString() == "0") { string leakFlag4Positions = @"SELECT t.* FROM TP_PM_BARCODELEAKPOSITION t where t.barcode= :BARCODE and leaktype = 4"; paras = new OracleParameter[]{ new OracleParameter(":BARCODE",barcode),}; leakFlag4Table = oracleConn.GetSqlResultToDt(leakFlag4Positions, paras); } //试水不合格，查询试水不合格位置 if (ds.Tables[0].Rows[0]["LEAKFLAG5"].ToString() == "0") { string leakFlag5Positions = @"SELECT t.* FROM TP_PM_BARCODELEAKPOSITION t where t.barcode= :BARCODE and leaktype = 5"; paras = new OracleParameter[]{ new OracleParameter(":BARCODE",barcode),}; leakFlag5Table = oracleConn.GetSqlResultToDt(leakFlag5Positions, paras); } //补裂位置 string leakFlag6Positions = @"SELECT t.* FROM TP_PM_BARCODELEAKPOSITION t where t.barcode= :BARCODE and leaktype = 6"; OracleParameter[] paras6 = new OracleParameter[]{ new OracleParameter(":BARCODE",barcode),}; leakFlag6Table = oracleConn.GetSqlResultToDt(leakFlag6Positions, paras6); } else { dr["refireflag"] = Convert.ToInt32(refireflagdata.Rows[0]["refireflag"]); } //获取上一次成品检验的缺陷 if (dataTable != null && dataTable.Tables.Count > 0 && dataTable.Tables[0].Rows.Count > 0) { sqlString2 = @"select TP_PM_Defect.DefectID ,replace( concat(TP_PM_Defect.DefectCode||'->',TP_PM_Defect.DefectName) , concat(TP_PM_Defect.DefectCode||'->',TP_PM_Defect.DefectCode||'->'),TP_PM_Defect.DefectCode||'->' ) as DefectName, TP_PM_Defect.DefectCode, TP_PM_Defect.DefectPositionID, replace( concat(TP_PM_Defect.DefectPositionCode||'->',TP_PM_Defect.DefectPositionName) , concat(TP_PM_Defect.DefectPositionCode||'->',TP_PM_Defect.DefectPositionCode||'->'),TP_PM_Defect.DefectPositionCode||'->' ) as DefectPositionName, TP_PM_Defect.DefectPositionCode, TP_PM_Defect.DefectProcedureID, TP_PM_Defect.DefectProcedureCode, TP_PM_Defect.DefectProcedureName,TP_PM_Defect.DefectUserID ,TP_PM_Defect.DefectUserCode,TP_PM_Defect.DefectUserName, TP_PM_Defect.DefectProductionDataID, TP_PM_Defect.Remarks AS DefectRemarks , TP_MST_Jobs.JobsID AS Jobs, TP_MST_Jobs.JobsName AS JobsText, TP_MST_DefectFine.DefectFineID, TP_MST_DefectFine.DefectFineCode AS DefectFineValue, TP_PM_Defect.MissedUserID, TP_PM_Defect.MissedUserCode, TP_PM_Defect.MissedUserName, TP_PM_Defect.DefectDeductionNum, TP_PM_Defect.SpecialDefect, TP_PM_Defect.CheckTime, (SELECT listagg(to_char(s.staffname), ',') within GROUP(ORDER BY s.staffid) FROM tp_pm_defectresponsible dp INNER JOIN tp_hr_staff s ON s.staffid = dp.staffid where dp.productiondefectid = TP_PM_Defect.PRODUCTIONDEFECTID) DefectStaffNames , ROWNUM-1 as TempCount from TP_PM_Defect left join TP_MST_Jobs on TP_PM_Defect.DefectJobs=TP_MST_Jobs.JobsID left join TP_MST_DefectFine on TP_PM_Defect.DefectFine=TP_MST_DefectFine.DefectFineID where TP_PM_Defect.ProductionDataID = :ProductionDataID"; OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":ProductionDataID",Convert.ToInt32(dataTable.Tables[0].Rows[0]["ProductionDataID"])), }; defectSet = oracleConn.GetSqlResultToDs(sqlString2, paras1); } // 取回收标识 sqlString2 = @" SELECT gdd.RECYCLINGFLAG FROM TP_PM_GroutingDailyDetail gdd WHERE gdd.BARCODE = :BARCODE "; paras = new OracleParameter[] { new OracleParameter(":BARCODE", barcode), }; DataSet recyclingSet = oracleConn.GetSqlResultToDs(sqlString2, paras); if (recyclingSet != null && recyclingSet.Tables.Count > 0) { dr["recyclingFlag"] = recyclingSet.Tables[0].Rows[0]["RECYCLINGFLAG"].ToString(); } } } } dtBarCode.Rows.Add(dr); // 获取plc重量 add by fubin 2020-7-20 if (!dtBarCode.Columns.Contains("PLCWeight")) { dtBarCode.Columns.Add("PLCWeight", typeof(decimal)); dtBarCode.Rows[0]["PLCWeight"] = 0; } // 成检交接（二检） 获取重量 if (procedureID == 104) { dtBarCode.Rows[0]["PLCWeight"] = Get3PLCWeight(sUserInfo); } returnDs.Tables.Add(dtBarCode); if (leakFlag4Table != null && leakFlag4Table.Rows.Count > 0) { leakFlag4Table.TableName = "leakFlag4Table"; returnDs.Tables.Add(leakFlag4Table); } if (leakFlag5Table != null && leakFlag5Table.Rows.Count > 0) { leakFlag5Table.TableName = "leakFlag5Table"; returnDs.Tables.Add(leakFlag5Table); } if (leakFlag6Table != null && leakFlag6Table.Rows.Count > 0) { leakFlag6Table.TableName = "leakFlag6Table"; returnDs.Tables.Add(leakFlag6Table); } //缺陷 if (defectSet != null && defectSet.Tables.Count > 0 && defectSet.Tables[0].Rows.Count > 0) { defectSet.Tables[0].TableName = "DefectTable"; returnDs.Tables.Add(defectSet.Tables[0].Copy()); } return returnDs; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 获取plc重量 add by fubin 2020-7-20 /// /// /// public static decimal Get3PLCWeight(SUserInfo sUserInfo) { ServiceResultEntity sre = PLCModuleLogic.ReadPLCWeightByUser_HEGII_S3(sUserInfo); if (sre.Result != null && sre.Result is JArray && (sre.Result as JArray)[0]["weight"] != null) { return Convert.ToDecimal((sre.Result as JArray)[0]["weight"]); } return 0; } /// /// 根据条码及工序判断是否漏扫 /// /// 产品条码 /// 校验工序ID /// 用户基本信息 /// DataSet public static int AddBarCodeMissing(IDBConnection oracleTrConn, string barcode, int produceid, SUserInfo sUserInfo) { try { #region 查询产成品 string sqlString = @"select TP_PM_InProduction.FlowProcedureID,NVL(TP_PM_InProduction.ReworkProcedureID,0) as ReworkProcedureID, TP_PM_InProduction.GoodsID,TP_PM_InProduction.GoodsCode,TP_PM_InProduction.GoodsName, TP_PC_Procedure.ProcedureCode,TP_PC_Procedure.ProcedureName,TP_MST_Goods.GoodsTypeID,TP_PM_InProduction.isrefire from TP_PM_InProduction left join TP_PC_Procedure on TP_PM_InProduction.FlowProcedureID=TP_PC_Procedure.ProcedureID left join TP_MST_Goods on TP_PM_InProduction.GoodsID=TP_MST_Goods.GoodsID where TP_PM_InProduction.BarCode =:barCode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); #endregion int AddBarCodeMissingID = 0;//新增漏扫表的ID if (ds != null && ds.Tables[0].Rows.Count > 0) //只有是在产的，才可以去进行漏扫 { #region 获取此条码为在产产品 int CompleteProcedureID = Convert.ToInt32(ds.Tables[0].Rows[0]["FlowProcedureID"]);//WMSYS.WM_CONCAT(to_char(pro.ProcedureName)),max(line.ProductionLineName) string CompleteProcedureCode = ds.Tables[0].Rows[0]["ProcedureCode"].ToString(); string CompleteProcedureName = ds.Tables[0].Rows[0]["ProcedureName"].ToString(); int GoodsID = Convert.ToInt32(ds.Tables[0].Rows[0]["GoodsID"]); string GoodsCode = ds.Tables[0].Rows[0]["GoodsCode"].ToString(); string GoodsName = ds.Tables[0].Rows[0]["GoodsName"].ToString(); int GoodsTypeID = Convert.ToInt32(ds.Tables[0].Rows[0]["GoodsTypeID"]); int ReworkProcedureID = Convert.ToInt32(ds.Tables[0].Rows[0]["ReworkProcedureID"]);//返工工序 int isrefire = Convert.ToInt32(ds.Tables[0].Rows[0]["isrefire"]);//重烧标记 #endregion // 漏扫工序与检验工序不在一条路径上 modify by chenxy 2016-08-08 begin // 漏扫序号大的可到达工序与检验工序不在一条路径上时，要验证下一个可到达工序。 // 即与检验工序在一条路径上的漏扫序号最大可到达工序为漏扫工序。 #region 获取漏扫工序信息 if (ReworkProcedureID == 0) { sqlString = @" select TP_PC_Procedure.ProcedureID,TP_PC_Procedure.ProcedureCode,TP_PC_Procedure.ProcedureName,TP_PC_Procedure.NodeType from TP_PC_ProcedureGoods left join TP_PC_Procedure on TP_PC_ProcedureGoods.ProcedureID=TP_PC_Procedure.ProcedureID where TP_PC_ProcedureGoods.goodsid=( select Goodsid from TP_PM_GroutingDailyDetail where BarCode=:barCode) and TP_PC_ProcedureGoods.ProcedureID in( select pro.ProcedureID from TP_PC_ProcedureFlow flow inner join TP_PC_Procedure pro on flow.arriveprocedureid = pro.procedureid inner join TP_PC_ProductionLine line on pro.ProductionLineID = line.ProductionLineID where flow.ProcedureID =:procedureID and flow.FlowFlag = 2 and pro.valueflag = '1' -- 非必须工序，不记漏扫 and pro.MUSTFLAG = '1' ) " + //" and exists ( " + // "select 1 from " + // "(" + // "select procedureid, arriveprocedureid,ltrim(sys_connect_by_path(procedureid,'->')||'->'||arriveprocedureid) sybp from " + // "( " + // " select * from TP_PC_PROCEDUREFLOW where arriveprocedureid<>:missProcedureid and flowflag=2 " + // ") " + // //" start with procedureid=:missProcedureid" + // " start with procedureid=TP_PC_ProcedureGoods.ProcedureID" + // " connect by nocycle procedureid=prior arriveprocedureid" + // ") where arriveprocedureid=:judgeProcedureid " + // " ) " + //" order by ProcedureID"; " order by MissPriority DESC, displayno --ProcedureCode DESC"; paras = new OracleParameter[]{ new OracleParameter(":procedureID",OracleDbType.Int32,CompleteProcedureID,ParameterDirection.Input), new OracleParameter(":barCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), //new OracleParameter(":missProcedureid",OracleDbType.Int32,missprocedureid,ParameterDirection.Input), //new OracleParameter(":judgeProcedureid",OracleDbType.Int32,produceid,ParameterDirection.Input), }; } else { sqlString = "select TP_PC_Procedure.ProcedureID,TP_PC_Procedure.ProcedureCode,TP_PC_Procedure.ProcedureName,TP_PC_Procedure.NodeType from TP_PC_Procedure where ProcedureID=:procedureID"; paras = new OracleParameter[]{ new OracleParameter(":procedureID",OracleDbType.Int32,ReworkProcedureID,ParameterDirection.Input), }; } ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { return AddBarCodeMissingID; } //int missprocedureid = Convert.ToInt32(ds.Tables[0].Rows[0]["ProcedureID"]); //string missprocedurecode = ds.Tables[0].Rows[0]["ProcedureCode"].ToString(); //string missprocedurename = ds.Tables[0].Rows[0]["ProcedureName"].ToString(); int missprocedureid = -1; string missprocedurecode = ""; string missprocedurename = ""; #endregion #region 增加判断校验工序必须是在漏扫工序的后面工序 //sqlString = "select * from " + // "(" + // "select procedureid, arriveprocedureid,ltrim(sys_connect_by_path(procedureid,'->')||'->'||arriveprocedureid) sybp from " + // "( " + // " select * from TP_PC_PROCEDUREFLOW where arriveprocedureid<>:missProcedureid and flowflag=2 " + // ")" + // " start with procedureid=:missProcedureid" + // " connect by nocycle procedureid=prior arriveprocedureid" + // ") where arriveprocedureid=:judgeProcedureid "; //paras = new OracleParameter[]{ // new OracleParameter(":missProcedureid",OracleDbType.Int32,missprocedureid,ParameterDirection.Input), // new OracleParameter(":judgeProcedureid",OracleDbType.Int32,produceid,ParameterDirection.Input), // }; //ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); //if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) //{ // return AddBarCodeMissingID; //} foreach (DataRow item in ds.Tables[0].Rows) { //sqlString = "select * from " + // "(" + // "select procedureid, arriveprocedureid,ltrim(sys_connect_by_path(procedureid,'->')||'->'||arriveprocedureid) sybp from " + // "( " + // " select * from TP_PC_PROCEDUREFLOW where arriveprocedureid<>:missProcedureid and flowflag=2 " + // ")" + // " start with procedureid=:missProcedureid" + // " connect by nocycle procedureid=prior arriveprocedureid" + // ") where arriveprocedureid=:judgeProcedureid "; sqlString = "SELECT procedureid\n" + " ,arriveprocedureid\n" + " ,ltrim(sys_connect_by_path(procedureid, '->') || '->' || arriveprocedureid) sybp\n" + " FROM (SELECT procedureid\n" + " ,arriveprocedureid\n" + " FROM (SELECT *\n" + " FROM tp_pc_procedureflow\n" + " WHERE arriveprocedureid <> :missProcedureid\n" + " AND flowflag = 2)\n" + " WHERE arriveprocedureid = :judgeProcedureid )\n" + " START WITH procedureid = :missProcedureid\n" + " CONNECT BY nocycle procedureid = PRIOR arriveprocedureid"; paras = new OracleParameter[]{ new OracleParameter(":missProcedureid",OracleDbType.Int32,Convert.ToInt32(item["ProcedureID"]),ParameterDirection.Input), new OracleParameter(":judgeProcedureid",OracleDbType.Int32,produceid,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { continue; } missprocedureid = Convert.ToInt32(item["ProcedureID"]); missprocedurecode = item["ProcedureCode"].ToString(); missprocedurename = item["ProcedureName"].ToString(); break; } if (missprocedureid == -1) { return AddBarCodeMissingID; } #endregion // 漏扫工序与检验工序不在一条路径上 modify by chenxy 2016-08-08 end //sqlString = "select TP_PC_Procedure.ProcedureID,TP_PC_Procedure.ProcedureCode,TP_PC_Procedure.ProcedureName,TP_PC_Procedure.NodeType from TP_PC_Procedure where ProcedureID=:procedureID"; //paras = new OracleParameter[]{ // new OracleParameter(":procedureID",OracleDbType.Int32,produceid,ParameterDirection.Input), // }; //ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); //int nodetype = Convert.ToInt32(ds.Tables[0].Rows[0]["NodeType"]);//获取当前工序的类型，为了判断如果成检设置重燃，未走重燃，直接包装，还有就是不记录以前工序的值 //if ((produceid > missprocedureid) || (ReworkProcedureID > 0 && nodetype == 3) || (isrefire == 6))//3 为结束节点,6为重烧 // 前面已经判断过 校验工序在漏扫工序（应该是当前工序吧）后 by chenxy 2020-03-29 begin //sqlString = "select 1 from TP_PM_ProductionDataIn where barcode=:barcode and ProcedureID=:ProcedureID and ValueFlag=1"; //paras = new OracleParameter[]{ // new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), // new OracleParameter(":ProcedureID",OracleDbType.Int32, produceid,ParameterDirection.Input), // }; //ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); //// if (produceid > missprocedureid) //if (ds != null && ds.Tables[0].Rows.Count == 0) // 未走过该工序 // 前面已经判断过 校验工序在漏扫工序（应该是当前工序吧）后 by chenxy 2020-03-29 end { #region 插入漏扫表,如果漏扫表里有相应的条码，不允许重复插入 sqlString = @"select missid from TP_PM_BarCodeMissing where barcode=:barcode and MissProcedureID=:MissProcedureID and ProcedureID=:ProcedureID"; paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":MissProcedureID",OracleDbType.Int32, missprocedureid,ParameterDirection.Input), new OracleParameter(":ProcedureID",OracleDbType.Int32, produceid,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count == 0) // 没有此条码的工序，即插入 { int OrganizationID = 0; string OrganizationName = ""; string OrganizationCode = ""; string OrganizationFullName = ""; sqlString = @"select TP_MST_Organization.OrganizationID ,TP_MST_Organization.OrganizationName ,TP_MST_Organization.OrganizationCode ,TP_MST_Organization.OrganizationFullName from TP_PC_Procedure left join TP_MST_Organization on TP_MST_Organization.OrganizationID=TP_PC_Procedure.OrganizationID where TP_PC_Procedure.ProcedureID=:ProcedureID"; paras = new OracleParameter[]{ new OracleParameter(":ProcedureID",OracleDbType.Int32, missprocedureid,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); OrganizationID = Convert.ToInt32(ds.Tables[0].Rows[0]["OrganizationID"]); //组织机构ID OrganizationName = ds.Tables[0].Rows[0]["OrganizationName"].ToString(); //组织机构名称 OrganizationCode = ds.Tables[0].Rows[0]["OrganizationCode"].ToString(); //组织机构编码 OrganizationFullName = ds.Tables[0].Rows[0]["OrganizationFullName"].ToString(); //组织机构全称 // 获取校验工序信息 sqlString = @"select TP_PC_Procedure.ProcedureID ,TP_PC_Procedure.ProcedureCode ,TP_PC_Procedure.ProcedureName from TP_PC_Procedure where TP_PC_Procedure.ProcedureID=:ProcedureID"; paras = new OracleParameter[]{ new OracleParameter(":ProcedureID",OracleDbType.Int32, produceid,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); string ProcedureCode = ""; string ProcedureName = ""; if (ds != null && ds.Tables[0].Rows.Count > 0) // 没有此条码的工序，即插入 { ProcedureCode = ds.Tables[0].Rows[0]["ProcedureCode"].ToString(); ProcedureName = ds.Tables[0].Rows[0]["ProcedureName"].ToString(); } //漏扫表ID string sql = "select SEQ_PM_BarCodeMissing_MissID.nextval from dual"; AddBarCodeMissingID = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sql)); sqlString = @"insert into TP_PM_BarCodeMissing(MissID, BarCode, OrganizationID, OrganizationName, OrganizationCode, OrganizationFullName, CompleteProcedureID, CompleteProcedureCode, CompleteProcedureName, ProcedureID, ProcedureCode, ProcedureName, MissProcedureID, MissProcedureCode, MissProcedureName, GoodsID, GoodsCode, GoodsName, GoodsTypeID, CreateUserID, UpdateUserID, AccountID ) values ( :MissID, :BarCode, :OrganizationID, :OrganizationName, :OrganizationCode, :OrganizationFullName, :CompleteProcedureID, :CompleteProcedureCode, :CompleteProcedureName, :ProcedureID, :ProcedureCode, :ProcedureName, :MissProcedureID, :MissProcedureCode, :MissProcedureName, :GoodsID, :GoodsCode, :GoodsName, :GoodsTypeID, :CreateUserID, :UpdateUserID, :AccountID ) "; paras = new OracleParameter[]{ new OracleParameter(":MissID",OracleDbType.Int32, AddBarCodeMissingID,ParameterDirection.Input), new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":OrganizationID",OracleDbType.Int32, OrganizationID,ParameterDirection.Input), new OracleParameter(":OrganizationName",OracleDbType.Varchar2, OrganizationName,ParameterDirection.Input), new OracleParameter(":OrganizationCode",OracleDbType.Varchar2, OrganizationCode,ParameterDirection.Input), new OracleParameter(":OrganizationFullName",OracleDbType.Varchar2, OrganizationFullName,ParameterDirection.Input), new OracleParameter(":CompleteProcedureID",OracleDbType.Int32, CompleteProcedureID,ParameterDirection.Input), new OracleParameter(":CompleteProcedureCode",OracleDbType.Varchar2, CompleteProcedureCode,ParameterDirection.Input), new OracleParameter(":CompleteProcedureName",OracleDbType.Varchar2, CompleteProcedureName,ParameterDirection.Input), new OracleParameter(":ProcedureID",OracleDbType.Int32, produceid,ParameterDirection.Input), new OracleParameter(":ProcedureCode",OracleDbType.Varchar2, ProcedureCode,ParameterDirection.Input), new OracleParameter(":ProcedureName",OracleDbType.Varchar2, ProcedureName,ParameterDirection.Input), new OracleParameter(":MissProcedureID",OracleDbType.Int32, missprocedureid,ParameterDirection.Input), new OracleParameter(":MissProcedureCode",OracleDbType.Varchar2,missprocedurecode ,ParameterDirection.Input), new OracleParameter(":MissProcedureName",OracleDbType.Varchar2, missprocedurename,ParameterDirection.Input), new OracleParameter(":GoodsID",OracleDbType.Int32, GoodsID,ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2,GoodsCode ,ParameterDirection.Input), new OracleParameter(":GoodsName",OracleDbType.Varchar2, GoodsName,ParameterDirection.Input), new OracleParameter(":GoodsTypeID",OracleDbType.Int32, GoodsTypeID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; oracleTrConn.GetSqlResultToStr(sqlString, paras); } #endregion } } else //不在产 { #region 是否存在注浆产品 sqlString = @"select TP_PM_GroutingDailyDetail.BarCode,TP_PM_GroutingDailyDetail.GoodsID,TP_PM_GroutingDailyDetail.GoodsCode, TP_PM_GroutingDailyDetail.GoodsName,TP_MST_Goods.GoodsTypeID from TP_PM_GroutingDailyDetail left join TP_MST_Goods on TP_PM_GroutingDailyDetail.GoodsID=TP_MST_Goods.GoodsID where TP_PM_GroutingDailyDetail.BarCode=:BarCode"; paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.Varchar2,barcode,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); #endregion if (ds != null && ds.Tables[0].Rows.Count > 0) { // 此条码为在产产品 //int? CompleteProcedureID = null;//WMSYS.WM_CONCAT(to_char(pro.ProcedureName)),max(line.ProductionLineName) //string CompleteProcedureCode = ""; //string CompleteProcedureName = ""; int GoodsID = Convert.ToInt32(ds.Tables[0].Rows[0]["GoodsID"]); string GoodsCode = ds.Tables[0].Rows[0]["GoodsCode"].ToString(); string GoodsName = ds.Tables[0].Rows[0]["GoodsName"].ToString(); int GoodsTypeID = Convert.ToInt32(ds.Tables[0].Rows[0]["GoodsTypeID"]); // #region 存在注浆产品,查看是否存在报损记录或者产成品记录 // sqlString = @"select BarCode from TP_PM_ScrapProduct where BarCode=:BarCode and ValueFlag=1 // union select BarCode from TP_PM_FinishedProduct where BarCode=:BarCode and ValueFlag=1"; // paras = new OracleParameter[]{ // new OracleParameter(":BarCode",OracleDbType.Varchar2,barcode,ParameterDirection.Input), // }; // ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); // #endregion // if (ds != null && ds.Tables[0].Rows.Count == 0) //即不在报损 ，也不在产成,说明未走任何工序 // { #region 查出校验工序的据在生产线，从而查出此生产上的漏扫开始工序节点 int ProductionLineID = 0; //查出校验工序生产线ID，用于区分多生产线的首个开始节点 sqlString = @"select ProductionLineID from TP_PC_Procedure where ProcedureID=:procedureID"; paras = new OracleParameter[]{ new OracleParameter(":procedureID",OracleDbType.Int32,produceid,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) // { ProductionLineID = Convert.ToInt32(ds.Tables[0].Rows[0]["ProductionLineID"]); } sqlString = @" select TP_PC_Procedure.ProcedureID,TP_PC_Procedure.ProcedureCode,TP_PC_Procedure.ProcedureName,TP_PC_Procedure.NodeType from TP_PC_ProcedureGoods left join TP_PC_Procedure on TP_PC_ProcedureGoods.ProcedureID=TP_PC_Procedure.ProcedureID where TP_PC_ProcedureGoods.goodsid=( select Goodsid from TP_PM_GroutingDailyDetail where BarCode=:barCode) and TP_PC_ProcedureGoods.ProcedureID in( select ProcedureID from TP_PC_Procedure where ProductionLineID =:ProductionLineID and NodeType=1 and valueflag = '1') order by TP_PC_Procedure.misspriority desc, TP_PC_Procedure.displayno"; paras = new OracleParameter[]{ new OracleParameter(":ProductionLineID",OracleDbType.Int32,ProductionLineID,ParameterDirection.Input), new OracleParameter(":barCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); #endregion if (ds != null && ds.Tables[0].Rows.Count > 0) //即不在报损 ，也不在产成,说明未走任何工序 { int missprocedureid = Convert.ToInt32(ds.Tables[0].Rows[0]["ProcedureID"]); string missprocedurecode = ds.Tables[0].Rows[0]["ProcedureCode"].ToString(); string missprocedurename = ds.Tables[0].Rows[0]["ProcedureName"].ToString(); #region 插入漏扫表,如果漏扫表里有相应的条码，不允许重复插入 sqlString = @"select 1 from TP_PM_BarCodeMissing where barcode=:barcode and MissProcedureID=:MissProcedureID and ProcedureID=:ProcedureID"; paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":MissProcedureID",OracleDbType.Int32, missprocedureid,ParameterDirection.Input), new OracleParameter(":ProcedureID",OracleDbType.Int32,produceid,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count == 0) // 没有此条码的工序，即插入 { int OrganizationID = 0; string OrganizationName = ""; string OrganizationCode = ""; string OrganizationFullName = ""; sqlString = @"select TP_MST_Organization.OrganizationID ,TP_MST_Organization.OrganizationName ,TP_MST_Organization.OrganizationCode ,TP_MST_Organization.OrganizationFullName from TP_PC_Procedure left join TP_MST_Organization on TP_MST_Organization.OrganizationID=TP_PC_Procedure.OrganizationID where TP_PC_Procedure.ProcedureID=:ProcedureID"; paras = new OracleParameter[]{ new OracleParameter(":ProcedureID",OracleDbType.Int32, missprocedureid,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); OrganizationID = Convert.ToInt32(ds.Tables[0].Rows[0]["OrganizationID"]); //组织机构ID OrganizationName = ds.Tables[0].Rows[0]["OrganizationName"].ToString(); //组织机构名称 OrganizationCode = ds.Tables[0].Rows[0]["OrganizationCode"].ToString(); //组织机构编码 OrganizationFullName = ds.Tables[0].Rows[0]["OrganizationFullName"].ToString(); //组织机构全称 // 获取校验工序信息 sqlString = @"select TP_PC_Procedure.ProcedureID ,TP_PC_Procedure.ProcedureCode ,TP_PC_Procedure.ProcedureName from TP_PC_Procedure where TP_PC_Procedure.ProcedureID=:ProcedureID"; paras = new OracleParameter[]{ new OracleParameter(":ProcedureID",OracleDbType.Int32, produceid,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); string ProcedureCode = ""; string ProcedureName = ""; if (ds != null && ds.Tables[0].Rows.Count > 0) // 没有此条码的工序，即插入 { ProcedureCode = ds.Tables[0].Rows[0]["ProcedureCode"].ToString(); ProcedureName = ds.Tables[0].Rows[0]["ProcedureName"].ToString(); } //漏扫表ID string sql = "select SEQ_PM_BarCodeMissing_MissID.nextval from dual"; AddBarCodeMissingID = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sql)); sqlString = @"insert into TP_PM_BarCodeMissing(MissID, BarCode, OrganizationID, OrganizationName, OrganizationCode, OrganizationFullName, ProcedureID, ProcedureCode, ProcedureName, MissProcedureID, MissProcedureCode, MissProcedureName, GoodsID, GoodsCode, GoodsName, GoodsTypeID, CreateUserID, UpdateUserID, AccountID ) values ( :MissID, :BarCode, :OrganizationID, :OrganizationName, :OrganizationCode, :OrganizationFullName, :ProcedureID, :ProcedureCode, :ProcedureName, :MissProcedureID, :MissProcedureCode, :MissProcedureName, :GoodsID, :GoodsCode, :GoodsName, :GoodsTypeID, :CreateUserID, :UpdateUserID, :AccountID ) "; paras = new OracleParameter[]{ new OracleParameter(":MissID",OracleDbType.Int32, AddBarCodeMissingID,ParameterDirection.Input), new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":OrganizationID",OracleDbType.Int32, OrganizationID,ParameterDirection.Input), new OracleParameter(":OrganizationName",OracleDbType.Varchar2, OrganizationName,ParameterDirection.Input), new OracleParameter(":OrganizationCode",OracleDbType.Varchar2, OrganizationCode,ParameterDirection.Input), new OracleParameter(":OrganizationFullName",OracleDbType.Varchar2, OrganizationFullName,ParameterDirection.Input), //new OracleParameter(":CompleteProcedureID",OracleDbType.Int32, CompleteProcedureID,ParameterDirection.Input), //new OracleParameter(":CompleteProcedureCode",OracleDbType.Varchar2, CompleteProcedureCode,ParameterDirection.Input), //new OracleParameter(":CompleteProcedureName",OracleDbType.Varchar2, CompleteProcedureName,ParameterDirection.Input), new OracleParameter(":ProcedureID",OracleDbType.Int32, produceid,ParameterDirection.Input), new OracleParameter(":ProcedureCode",OracleDbType.Varchar2, ProcedureCode,ParameterDirection.Input), new OracleParameter(":ProcedureName",OracleDbType.Varchar2, ProcedureName,ParameterDirection.Input), new OracleParameter(":MissProcedureID",OracleDbType.Int32, missprocedureid,ParameterDirection.Input), new OracleParameter(":MissProcedureCode",OracleDbType.Varchar2,missprocedurecode ,ParameterDirection.Input), new OracleParameter(":MissProcedureName",OracleDbType.Varchar2, missprocedurename,ParameterDirection.Input), new OracleParameter(":GoodsID",OracleDbType.Int32, GoodsID,ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2,GoodsCode ,ParameterDirection.Input), new OracleParameter(":GoodsName",OracleDbType.Varchar2, GoodsName,ParameterDirection.Input), new OracleParameter(":GoodsTypeID",OracleDbType.Int32, GoodsTypeID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; oracleTrConn.GetSqlResultToStr(sqlString, paras); } #endregion } //} } } return AddBarCodeMissingID; } catch (Exception ex) { throw ex; } } #endregion #region 校验生产工号 /// /// 检验此用户是否允许生产工序 /// /// 工序ID /// 工号ID /// CheckProcedureUserResult实体类 public static CheckProcedureUserResult CheckProcedureUser(int procedureID, string UserCode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); string sqlString = @"SELECT TP_MST_USER.ispublicbody,TP_MST_USER.USERID,TP_MST_USER.UserCode,TP_MST_USER.UserName,nvl(TP_PC_ProcedureUser.Userid,0) as ProcedureUserid FROM TP_MST_USER left join (select TP_PC_ProcedureUser.Userid from TP_PC_ProcedureUser where TP_PC_ProcedureUser.ProcedureID=:ProcedureID) TP_PC_ProcedureUser on TP_PC_ProcedureUser.Userid=TP_MST_USER.UserID WHERE TP_MST_USER.UserCode=:UserCode and TP_MST_USER.ValueFlag = 1 and TP_MST_USER.IsWorker=1 and TP_MST_USER.AccountID=:accountID"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":ProcedureID",OracleDbType.Int32,procedureID,ParameterDirection.Input), new OracleParameter(":UserCode",OracleDbType.Varchar2,UserCode,ParameterDirection.Input), new OracleParameter(":accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), }; DataSet resultDs = oracleConn.GetSqlResultToDs(sqlString, paras); CheckProcedureUserResult checkProcedureUserResult = new CheckProcedureUserResult(); if (resultDs != null && resultDs.Tables.Count > Constant.INT_IS_ZERO && resultDs.Tables[0].Rows.Count > Constant.INT_IS_ZERO) { if (Convert.ToInt32(resultDs.Tables[0].Rows[0]["ProcedureUserid"]) == Constant.INT_IS_ZERO) { // 此工号不允许生产工序 checkProcedureUserResult.ErrMsg = string.Format(Messages.MSG_PM_W006, UserCode); } else { checkProcedureUserResult.UserID = Convert.ToInt32(resultDs.Tables[0].Rows[0]["UserID"]); checkProcedureUserResult.UserCode = resultDs.Tables[0].Rows[0]["UserCode"].ToString(); checkProcedureUserResult.UserName = resultDs.Tables[0].Rows[0]["UserName"].ToString(); checkProcedureUserResult.Ispublicbody = resultDs.Tables[0].Rows[0]["Ispublicbody"].ToString() == "1" ? 1 : 0; } } else { checkProcedureUserResult.ErrMsg = string.Format(Messages.MSG_CMN_W019, UserCode, "生产工号"); } #region 查询该工号下是否有试用或者转正的生产员工 if (string.IsNullOrEmpty(checkProcedureUserResult.ErrMsg)) { sqlString = @" select count(*) from tp_hr_staff where staffid in(select staffid from tp_mst_userstaff where userid=:userID) and valueflag=1 and (staffstatus=1 or staffstatus=2)"; paras = new OracleParameter[]{ new OracleParameter(":userID",OracleDbType.Int32,checkProcedureUserResult.UserID,ParameterDirection.Input), }; string count = oracleConn.GetSqlResultToStr(sqlString, paras); if (int.Parse(count) == Constant.INT_IS_ZERO) { checkProcedureUserResult.ErrMsg = string.Format(Messages.MSG_PM_W005, UserCode); } } #endregion return checkProcedureUserResult; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion /// /// 根据工序条码获得生产数据 /// /// 工序ID /// 产品条码 /// DataSet public static DataSet GetProductionByBarcode(int procedureID, string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "select t.ProductionDataID,t.Barcode,t.GoodsID,t.GoodsCode,t.GoodsName,t.UserID,t.UserCode,t.UserName,t.DefectFlag,t.ReworkProcedureID,t.Remarks from TP_PM_ProductionData t where t.barcode='" + barcode + "' and t.ProcedureID=" + procedureID; DataSet ds = con.GetSqlResultToDs(sqlString); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取生产返工工序 /// /// 工序ID /// 产品条码 /// DataSet public static DataSet GetReworkProcedureByBarcode(int procedureID, string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select t.ProcedureID as ReworkProcedureID, t.ProcedureCode as ReworkProcedureCode, t.ProcedureName as ReworkProcedureName from TP_PM_ProductionData t where t.ProcedureModel=1 and t.ModelType=0 and t.barcode='" + barcode + "' "; DataSet ds = con.GetSqlResultToDs(sqlString); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取重烧生产返工工序 /// /// 工序ID /// 产品条码 /// DataSet public static DataSet GetReworkProcedureByProcedureID(int procedureID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select ProcedureID as ReworkProcedureID ,ProcedureName as ReworkProcedureName from TP_PC_Procedure p where ProductionLineID in( select ProductionLineID from TP_PC_Procedure where ProcedureID=:ProcedureID ) and p.valueflag='1' and IsSpecialRework IN('1','2') order by ReworkProcedureID "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":ProcedureID",OracleDbType.Int32, procedureID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据生产数据ID获取对应的缺陷信息 /// /// 生产数据ID /// DataSet public static DataSet GetProductionDefectByProductionDataID(int ProductionDataID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select replace( concat(TP_PM_Defect.DefectCode||'->',TP_PM_Defect.DefectName) , concat(TP_PM_Defect.DefectCode||'->',TP_PM_Defect.DefectCode||'->'),TP_PM_Defect.DefectCode||'->' ) as DefectName, replace( concat(TP_PM_Defect.DefectPositionCode||'->',TP_PM_Defect.DefectPositionName) , concat(TP_PM_Defect.DefectPositionCode||'->',TP_PM_Defect.DefectPositionCode||'->'),TP_PM_Defect.DefectPositionCode||'->' ) as DefectPositionName, TP_PM_Defect.DefectProcedureName ,TP_PM_Defect.DefectUserCode,TP_PM_Defect.DefectUserName,TP_PM_Defect.Remarks,TP_MST_Jobs.JobsName,TP_MST_DefectFine.DefectFineCode as DefectFine, TP_PM_Defect.DefectDeductionNum, (SELECT listagg(to_char(s.staffname), ',') within GROUP(ORDER BY s.staffid) FROM tp_pm_defectresponsible dp INNER JOIN tp_hr_staff s ON s.staffid = dp.staffid where dp.productiondefectid = TP_PM_Defect.PRODUCTIONDEFECTID) DefectStaffNames from TP_PM_Defect left join TP_MST_Jobs on TP_PM_Defect.DefectJobs=TP_MST_Jobs.JobsID left join TP_MST_DefectFine on TP_PM_Defect.DefectFine=TP_MST_DefectFine.DefectFineID where TP_PM_Defect.ProductionDataID=" + ProductionDataID; DataSet ds = con.GetSqlResultToDs(sqlString); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据产品ID查出缺陷位置 /// /// 产品ID /// object public static object GetDefectPosition(string positionCode, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select DEFECTPOSITIONID, DEFECTPOSITIONCODE, DEFECTPOSITIONNAME, concat(DEFECTPOSITIONCODE||'->',DEFECTPOSITIONNAME) as DEFECTPOSITIONCODEANDNAME from TP_MST_DEFECTPOSITION where AccountID=:accountID and DEFECTPOSITIONCODE=:defectpositioncode and ValueFlag=1"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":accountID",sUserInfo.AccountID), new OracleParameter(":defectpositioncode",positionCode), }; DataSet resultds = con.GetSqlResultToDs(sqlString, paras); if (resultds != null && resultds.Tables.Count > Constant.INT_IS_ZERO) { string errMsg = string.Empty; int defectPositionID = 0; string defectpositionname = string.Empty; if (resultds.Tables[0].Rows.Count == Constant.INT_IS_ZERO) { errMsg = "缺陷位置在系统中不存在"; } else { defectPositionID = int.Parse(resultds.Tables[0].Rows[0]["defectPositionID"].ToString()); defectpositionname = resultds.Tables[0].Rows[0]["defectpositionname"].ToString(); } return new { ErrMsg = errMsg, DefectPositionID = defectPositionID, DefectPositionName = defectpositionname }; } else { return null; } } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取半检相应的数据 /// /// 半检ID /// DataSet public static DataSet GetSemiTestByID(int semiTestID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); DataSet dsReturn = new DataSet(); string stringSql1 = @"select TP_MST_User.Userid, TP_MST_User.Usercode, TP_MST_User.Username, TP_PM_SemiTest.TestDate, TP_PM_SemiTest.Remarks, TP_PM_SemiTest.Optimestamp, TP_PM_SemiTest.AuditStatus, TP_PM_SemiTest.SemiTestType from TP_PM_SemiTest left join TP_MST_User on TP_PM_SemiTest.TestUserID= TP_MST_User.Userid where TP_PM_SemiTest.SemiTestID=:SemiTestID"; string stringSql2 = @"select TP_PM_SemiTestDetail.Semitestdetailid, TP_PM_SemiTestDetail.Groutinguserid, TP_PM_SemiTestDetail.Goodsid, TP_PM_SemiTestDetail.Goodscode, TP_PM_SemiTestDetail.Goodsname, TP_PM_SemiTestDetail.Testnum, TP_PM_SemiTestDetail.Scrapnum, TP_PM_SemiTestDetail.Scrapreason, TP_PM_SemiTestDetail.Feedback, TP_MST_Goods.Goodsspecification, TP_MST_Goods.Goodsmodel, TP_MST_GoodsType.GoodsTypeName from TP_PM_SemiTestDetail left join TP_MST_Goods on TP_PM_SemiTestDetail.Goodsid=TP_MST_Goods.Goodsid left join TP_MST_GoodsType on TP_MST_Goods.Goodstypeid=TP_MST_GoodsType.Goodstypeid where TP_PM_SemiTestDetail.Semitestid=:SemiTestID"; string stringSql3 = @"select TP_PM_SemiTestDefect.SemiTestDefectID, TP_PM_SemiTestDefect.SemiTestDetailID, TP_PM_SemiTestDefect.SemiTestID, TP_PM_SemiTestDefect.SemiTestDate, TP_PM_SemiTestDefect.Defectid, TP_PM_SemiTestDefect.Defectpositionid, TP_PM_SemiTestDefect.DefectNum from TP_PM_SemiTestDefect where TP_PM_SemiTestDefect.Semitestid=:SemiTestID "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":SemiTestID",OracleDbType.Int32, semiTestID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(stringSql1, paras); ds.Tables[0].TableName = "TP_PM_SemiTest"; DataSet ds2 = con.GetSqlResultToDs(stringSql2, paras); ds2.Tables[0].TableName = "TP_PM_SemiTestDetail"; DataSet ds3 = con.GetSqlResultToDs(stringSql3, paras); ds3.Tables[0].TableName = "TP_PM_SemiTestDefect"; dsReturn.Tables.Add(ds.Tables[0].Copy()); dsReturn.Tables.Add(ds2.Tables[0].Copy()); dsReturn.Tables.Add(ds3.Tables[0].Copy()); return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取审批半检数据结果集 /// /// 半检实体类 /// 用户基本信息 /// DataSet public static DataSet SearchSemiTestList(SemiTestEntity semiTestEntity, SUserInfo user) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_begindate",OracleDbType.Date, semiTestEntity.beginDate,ParameterDirection.Input), new OracleParameter("in_enddate",OracleDbType.Date, semiTestEntity.endDate,ParameterDirection.Input), new OracleParameter("in_accountID",OracleDbType.Int32, user.AccountID,ParameterDirection.Input), new OracleParameter("in_testuserIDS",OracleDbType.NVarchar2, semiTestEntity.TestUserIDS,ParameterDirection.Input), new OracleParameter("in_currentUserID",OracleDbType.Int32, user.UserID,ParameterDirection.Input), new OracleParameter("in_remarks",OracleDbType.NVarchar2, semiTestEntity.Remarks,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), new OracleParameter("in_semitesttype",OracleDbType.Int32, semiTestEntity.SemiTestType,ParameterDirection.Input), new OracleParameter("in_testuserid",OracleDbType.Int32, semiTestEntity.TestUserID,ParameterDirection.Input), }; DataSet ds = con.ExecStoredProcedure("PRO_PM_SearchSemiTestList", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取半检数据详细数据源 /// /// 半检ID /// 用户基本信息 /// DataSet public static DataSet SearchSemiTestInfo(int semitestID, SUserInfo user) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_semitestID",OracleDbType.Int32, semitestID,ParameterDirection.Input), new OracleParameter("in_accountID",OracleDbType.Int32, user.AccountID,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_PM_SearchSemiTestInfo", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取半检数据详细数据源 /// /// 半检明细ID /// 用户基本信息 /// DataSet public static DataSet SearchSemiTestDetailInfo(int semitestDetailID, SUserInfo user) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_semitestDetailID",OracleDbType.Int32, semitestDetailID,ParameterDirection.Input), new OracleParameter("in_accountID",OracleDbType.Int32, user.AccountID,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet ds = con.ExecStoredProcedure("PRO_PM_SearchSemiTestDetail", paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取产品条码是否重烧 /// /// 产品条码 /// Datase public static DataSet GetReFine(string barcode) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { //获取窑炉管理数据 //string sqlString = "Select IsReFire from TP_PM_InProduction where barcode=:barcode"; string sqlString = "select IsReFire, IsLengBu from tp_PM_ProductionData where ProductionDataid=(select max(ProductionDataid) from tp_PM_ProductionData where barcode=:barcode)"; Oracle.ManagedDataAccess.Client.OracleParameter[] oracleParameter = new Oracle.ManagedDataAccess.Client.OracleParameter[] { new Oracle.ManagedDataAccess.Client.OracleParameter(":barcode",barcode) }; oracleConn.Open(); DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter); oracleConn.Close(); return result; } catch (Exception ex) { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 检验干补条码 /// /// 工序ID /// 产品条码 /// DataSet public static DataSet CheckDryRepairBarcode(int procedureID, string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_procedureid",OracleDbType.Int32,procedureID,ParameterDirection.Input), new OracleParameter("in_barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), new OracleParameter("out_errMsg",OracleDbType.NVarchar2,2000,null,ParameterDirection.Output), new OracleParameter("out_goodsID",OracleDbType.Int32,ParameterDirection.Output), new OracleParameter("out_goodsCode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_goodsName",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_groutingUserCode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), }; oracleConn.ExecStoredProcedure("pro_pm_checkdryrepairbarcode", paras); DataTable dtBarCode = PMModuleLogic.CreateBarCodeResultTable(); DataSet returnDs = new DataSet(); DataRow dr = dtBarCode.NewRow(); dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = paras[2].Value.ToString() == "null" ? "" : paras[2].Value.ToString().Replace("\\n\\r", "\n\r"); dr[Constant.BarCodeResultTableColumns.out_goodsID.ToString()] = paras[3].Value.ToString() == "null" ? 0 : paras[3].Value; dr[Constant.BarCodeResultTableColumns.out_goodsCode.ToString()] = paras[4].Value.ToString() == "null" ? "" : paras[4].Value; dr[Constant.BarCodeResultTableColumns.out_goodsName.ToString()] = paras[5].Value.ToString() == "null" ? "" : paras[5].Value; dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = paras[6].Value.ToString() == "null" ? 0 : paras[6].Value; if (paras[2].Value.ToString() == "null") //只有正确的条码，读注浆信息 { string sqlString = @"select TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode, tp_pm_groutingdailydetail.groutingcount as GroutingNum, tp_pm_groutingdailydetail.GroutingMouldCode as MouldCode, tp_pm_groutingdailydetail.ispublicbody, tp_pm_groutingdailydetail.Groutingdate, tp_pm_groutingdailydetail.SpecialRepairFlag, tp_mst_logo.logoid, tp_mst_logo.logocode, tp_mst_logo.logoname from tp_pm_groutingdailydetail left join tp_mst_logo on tp_pm_groutingdailydetail.logoid=tp_mst_logo.logoid where tp_pm_groutingdailydetail.BarCode=:barcode"; paras = new OracleParameter[]{ new OracleParameter(":barcode",barcode), }; DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = ds.Tables[0].Rows[0]["GroutingUserCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingNum.ToString()] = ds.Tables[0].Rows[0]["GroutingNum"].ToString(); dr[Constant.BarCodeResultTableColumns.out_mouldCode.ToString()] = ds.Tables[0].Rows[0]["MouldCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbody.ToString()] = ds.Tables[0].Rows[0]["ispublicbody"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_groutingdate.ToString()] = ds.Tables[0].Rows[0]["Groutingdate"].ToString(); dr[Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString()] = ds.Tables[0].Rows[0]["SpecialRepairFlag"].ToString(); dr[Constant.BarCodeResultTableColumns.out_isReFire.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_logoID.ToString()] = ds.Tables[0].Rows[0]["logoID"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoCode.ToString()] = ds.Tables[0].Rows[0]["logoCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoName.ToString()] = ds.Tables[0].Rows[0]["logoName"].ToString(); } } dtBarCode.Rows.Add(dr); returnDs.Tables.Add(dtBarCode); return returnDs; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 报损工序查出工号根据生产数据ID /// /// /// public static DataSet GetScrapProductUserCodeByProductionDataID(int ProductionDataID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select UserID,UserCode,UserName from TP_PM_ProductionDataIn where ProductionDataID=:ProductionDataID"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":ProductionDataID",OracleDbType.Int32, ProductionDataID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 交坯验证条码（首节点） /// /// 工序ID /// 产品条码 /// DataSet public static DataSet CheckBarcodeDeliverMud(int procedureID, string barcode, SUserInfo sUserInfo, bool isInn = false) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.IgnoreCase = false; oracleConn.Open(); // 2019-1016 if (!isInn) { //xuwei fix 2019-09-26 使用通用方法判定 //if (PMModuleLogicDAL.IsNodeBegin(oracleConn, barcode) == 0) //{ // return CheckBarcode(procedureID, barcode, sUserInfo, true); //} string sql1 = "select NodeType from tp_pc_procedure where procedureid=:procedureid"; string NodeType = oracleConn.GetSqlResultToObj(sql1, new OracleParameter[] { new OracleParameter(":procedureid", procedureID) }) + ""; if (NodeType == "1") { int isNodeBegin = PMModuleLogicDAL.IsNodeBegin(oracleConn, barcode); if (isNodeBegin == 0) { return CheckBarcode(procedureID, barcode, sUserInfo, true); } } else { return CheckBarcode(procedureID, barcode, sUserInfo, true); } } string sql = "select barcodestatus,GroutingDailyDetailid from TP_PM_USEDBARCODE where BarCode='" + barcode + "'"; sql = "select barcodestatus,GroutingDailyDetailid from TP_PM_USEDBARCODE where BarCode=:barcode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), }; DataSet ds = oracleConn.GetSqlResultToDs(sql, paras); DataTable dtBarCode = PMModuleLogic.CreateBarCodeResultTable(); DataSet returnDs = new DataSet(); DataRow dr = dtBarCode.NewRow(); dr[Constant.BarCodeResultTableColumns.out_barcode.ToString()] = barcode; if (ds != null && ds.Tables[0].Rows.Count == 0) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "无效条码[" + barcode + "]"; } else { //不等于空的时候 if (ds.Tables[0].Rows[0]["barcodestatus"].ToString() == "-1") { sql = "select max(newbarcode) from TP_PM_BarCodeRecord where BarCode='" + barcode + "'"; string newbarcode = oracleConn.GetSqlResultToStr(sql); dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "条码已被替换,新条码为" + newbarcode; } else if (ds.Tables[0].Rows[0]["barcodestatus"].ToString() == "2") { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "产品已成型报损"; } else if (ds.Tables[0].Rows[0]["barcodestatus"].ToString() == "3" || ds.Tables[0].Rows[0]["barcodestatus"].ToString() == "4") { //dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "产品已交坯"; dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "产品已在产"; } else if (ds.Tables[0].Rows[0]["barcodestatus"].ToString() == "1") { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = ""; string sqlString = @"select tp_pm_GroutingDailyDetail.GoodsID, tp_pm_GroutingDailyDetail.GoodsCode, tp_pm_GroutingDailyDetail.GoodsName, tp_pm_GroutingDailyDetail.UserID as GroutingUserID, tp_pm_GroutingDailyDetail.UserCode as GroutingUserCode, tp_mst_user.username as GroutingUserName, tp_pm_GroutingDailyDetail.groutingcount as GroutingNum, tp_pm_GroutingDailyDetail.GroutingMouldCode as MouldCode, tp_pm_GroutingDailyDetail.ispublicbody, tp_pm_GroutingDailyDetail.Groutingdate, tp_pm_GroutingDailyDetail.SpecialRepairFlag, tp_mst_logo.logoid, tp_mst_logo.logocode, tp_mst_logo.logoname, TP_MST_Goods.DeliverLimitCycle from tp_pm_GroutingDailyDetail left join tp_mst_logo on tp_pm_GroutingDailyDetail.logoid=tp_mst_logo.logoid left join tp_mst_user on tp_pm_GroutingDailyDetail.userid=tp_mst_user.userid left join TP_MST_Goods on tp_pm_GroutingDailyDetail.GoodsID=TP_MST_Goods.GoodsID where tp_pm_GroutingDailyDetail.groutingdailydetailid=" + ds.Tables[0].Rows[0]["GroutingDailyDetailid"].ToString(); //paras = new OracleParameter[]{ // new OracleParameter(":groutingdailydetailid",ds.Tables[0].Rows[0]["GroutingDailyDetailid"].ToString()), // }; ds = oracleConn.GetSqlResultToDs(sqlString, null); if (ds != null && ds.Tables[0].Rows.Count > 0) { string sqlExistGoods = "select count(GoodsID) from TP_PC_ProcedureGoods where GoodsID=:GoodsID and ProcedureID=:ProcedureID"; paras = new OracleParameter[]{ new OracleParameter(":GoodsID",ds.Tables[0].Rows[0]["GoodsID"].ToString()), new OracleParameter(":ProcedureID",procedureID), }; DataSet dsGoods = oracleConn.GetSqlResultToDs(sqlExistGoods, paras); if (dsGoods.Tables[0].Rows[0][0].ToString() != "1") { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = @"条码[" + barcode + "]不可以经过该工序\n\r原因：条码对应的产品编码[" + ds.Tables[0].Rows[0]["GoodsCode"].ToString() + "]没有在该工序中配置"; } else { dr[Constant.BarCodeResultTableColumns.out_goodsID.ToString()] = ds.Tables[0].Rows[0]["GoodsID"].ToString(); dr[Constant.BarCodeResultTableColumns.out_goodsCode.ToString()] = ds.Tables[0].Rows[0]["GoodsCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_goodsName.ToString()] = ds.Tables[0].Rows[0]["GoodsName"].ToString(); dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = 0; dr[Constant.BarCodeResultTableColumns.out_groutingUserName.ToString()] = ds.Tables[0].Rows[0]["GroutingUserName"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = ds.Tables[0].Rows[0]["GroutingUserCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingUserID.ToString()] = ds.Tables[0].Rows[0]["GroutingUserID"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingNum.ToString()] = ds.Tables[0].Rows[0]["GroutingNum"].ToString(); dr[Constant.BarCodeResultTableColumns.out_mouldCode.ToString()] = ds.Tables[0].Rows[0]["MouldCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbody.ToString()] = ds.Tables[0].Rows[0]["ispublicbody"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_groutingdate.ToString()] = ds.Tables[0].Rows[0]["Groutingdate"].ToString(); dr[Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString()] = ds.Tables[0].Rows[0]["SpecialRepairFlag"].ToString(); dr[Constant.BarCodeResultTableColumns.out_isReFire.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_logoID.ToString()] = ds.Tables[0].Rows[0]["logoid"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoCode.ToString()] = ds.Tables[0].Rows[0]["logocode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoName.ToString()] = ds.Tables[0].Rows[0]["logoname"].ToString(); dr[Constant.BarCodeResultTableColumns.out_deliverLimitCycle.ToString()] = ds.Tables[0].Rows[0]["DeliverLimitCycle"].ToString(); // 首节点无需暂时字段 //barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_WaterLabelCode.ToString()); //barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_CodeCheckFlag.ToString()); } } } } dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = 0; dtBarCode.Rows.Add(dr); returnDs.Tables.Add(dtBarCode); return returnDs; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 检验条码(除了交坯单点检验) /// /// 工序ID /// 产品条码 /// DataSet public static DataSet CheckBarcodeSinglePoint(int procedureID, string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); DataTable dtBarCode = PMModuleLogic.CreateBarCodeResultTable(); DataSet returnDs = new DataSet(); DataRow dr = dtBarCode.NewRow(); string sqlBarcode = GetConvertBarCode(oracleConn, barcode, procedureID, sUserInfo); if (sqlBarcode == string.Empty) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "无效条码"; dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = 0; dtBarCode.Rows.Add(dr); returnDs.Tables.Add(dtBarCode); return returnDs; } else { dr[Constant.BarCodeResultTableColumns.out_barcode.ToString()] = sqlBarcode; barcode = sqlBarcode; } OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_procedureid",OracleDbType.Int32,procedureID,ParameterDirection.Input), new OracleParameter("in_barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), new OracleParameter("out_errMsg",OracleDbType.NVarchar2,2000,null,ParameterDirection.Output), new OracleParameter("out_goodsID",OracleDbType.Int32,ParameterDirection.Output), new OracleParameter("out_goodsCode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_goodsName",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_groutingUserCode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), new OracleParameter("out_errMsg_Status",OracleDbType.Varchar2,50,"",ParameterDirection.Output), }; oracleConn.ExecStoredProcedure("PRO_PM_CheckBarcode", paras); dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = paras[2].Value.ToString() == "null" ? "" : paras[2].Value.ToString().Replace("\\n\\r", "\n\r"); int missFlag = 0; if (paras[7].Value != null && paras[7].Value.ToString() == "1") { missFlag = AddBarCodeMissing(oracleConn, barcode, procedureID, sUserInfo); } dr[Constant.BarCodeResultTableColumns.out_goodsID.ToString()] = paras[3].Value.ToString() == "null" ? 0 : paras[3].Value; dr[Constant.BarCodeResultTableColumns.out_goodsCode.ToString()] = paras[4].Value.ToString() == "null" ? "" : paras[4].Value; dr[Constant.BarCodeResultTableColumns.out_goodsName.ToString()] = paras[5].Value.ToString() == "null" ? "" : paras[5].Value; dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = paras[6].Value.ToString() == "null" ? 0 : paras[6].Value; dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = missFlag; if (paras[7].Value.ToString() == "null") //只有正确的条码，读注浆信息 { // string sqlString = @"select // TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode, // tp_pm_groutingdailydetail.groutingcount as GroutingNum, // tp_pm_groutingdailydetail.GroutingMouldCode as MouldCode, // tp_pm_groutingdailydetail.ispublicbody, // tp_pm_groutingdailydetail.Groutingdate, // tp_pm_groutingdailydetail.SpecialRepairFlag // from tp_pm_groutingdailydetail // where tp_pm_groutingdailydetail.GroutingDailyDetailID in (select GroutingDailyDetailID from TP_PM_UsedBarCode where barcode=:barcode)"; string sqlString = "SELECT PI.GROUTINGUSERCODE,\n" + " PI.GROUTINGNUM,\n" + " PI.GROUTINGMOULDCODE AS MOULDCODE,\n" + " PI.ISPUBLICBODY,\n" + " PI.GROUTINGDATE,\n" + " PI.SPECIALREPAIRFLAG,\n" + " L.LOGOID,\n" + " L.LOGOCODE,\n" + " L.LOGONAME,\n" + //" GOODS.WATERLABELCODE\n" + " nvl((select gls.WATERLABELCODE\n" + " from TP_MST_GOODSLOGOSAP gls\n" + " where gls.goodsid = PI.goodsid\n" + " and gls.logoid = PI.LOGOID)\n" + " ,GOODS.WATERLABELCODE) WATERLABELCODE\n" + " FROM TP_PM_INPRODUCTION PI\n" + " LEFT JOIN TP_MST_LOGO L\n" + " ON PI.LOGOID = L.LOGOID\n" + " LEFT JOIN TP_MST_GOODS GOODS\n" + " ON PI.GOODSID = GOODS.GOODSID\n" + " WHERE PI.BARCODE = :BARCODE"; paras = new OracleParameter[] { new OracleParameter(":BARCODE",barcode) }; DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = ds.Tables[0].Rows[0]["GROUTINGUSERCODE"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingNum.ToString()] = ds.Tables[0].Rows[0]["GROUTINGNUM"].ToString(); dr[Constant.BarCodeResultTableColumns.out_mouldCode.ToString()] = ds.Tables[0].Rows[0]["MOULDCODE"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbody.ToString()] = ds.Tables[0].Rows[0]["ISPUBLICBODY"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_groutingdate.ToString()] = ds.Tables[0].Rows[0]["GROUTINGDATE"].ToString(); dr[Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString()] = ds.Tables[0].Rows[0]["SPECIALREPAIRFLAG"].ToString(); dr[Constant.BarCodeResultTableColumns.out_isReFire.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_logoID.ToString()] = ds.Tables[0].Rows[0]["LOGOID"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoCode.ToString()] = ds.Tables[0].Rows[0]["LOGOCODE"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoName.ToString()] = ds.Tables[0].Rows[0]["LOGONAME"].ToString(); dr[Constant.BarCodeResultTableColumns.out_WaterLabelCode.ToString()] = ds.Tables[0].Rows[0]["WATERLABELCODE"].ToString(); // 获取编码检验标识 sqlString = "SELECT CODECHECKFLAG FROM TP_PC_PROCEDURE WHERE PROCEDUREID = :PROCEDUREID"; paras = new OracleParameter[] { new OracleParameter(":PROCEDUREID",procedureID) }; DataTable dt = oracleConn.GetSqlResultToDt(sqlString, paras); if (dt != null && dt.Rows.Count > 0) { dr[Constant.BarCodeResultTableColumns.out_CodeCheckFlag.ToString()] = dt.Rows[0]["CODECHECKFLAG"] + ""; } } } dtBarCode.Rows.Add(dr); returnDs.Tables.Add(dtBarCode); return returnDs; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 交坯验证条码 /// /// 工序ID /// 产品条码 /// DataSet public static DataSet CheckBarcodeDeliverMudSinglePoint(int procedureID, string barcode, SUserInfo sUserInfo, ref int? GroutingDailyDetailID) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); string sql = "select barcodestatus,barcode,GroutingDailyDetailid from TP_PM_USEDBARCODE where barcode=:barcode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), }; DataSet ds = oracleConn.GetSqlResultToDs(sql, paras); DataTable dtBarCode = PMModuleLogic.CreateBarCodeResultTable(); DataSet returnDs = new DataSet(); DataRow dr = dtBarCode.NewRow(); dr[Constant.BarCodeResultTableColumns.out_barcode.ToString()] = barcode; if (ds != null && ds.Tables[0].Rows.Count == 0) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "无效条码[" + barcode + "]"; } else { //不等于空的时候 if (ds.Tables[0].Rows[0]["barcodestatus"].ToString() == "-1") { sql = "select max(newbarcode) from TP_PM_BarCodeRecord where BarCode='" + ds.Tables[0].Rows[0]["barcode"].ToString() + "'"; string newbarcode = oracleConn.GetSqlResultToStr(sql); dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "条码已被替换,新条码为" + newbarcode; } else if (ds.Tables[0].Rows[0]["barcodestatus"].ToString() == "2") { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "产品已成型报损"; } // else if (ds.Tables[0].Rows[0]["barcodestatus"].ToString() == "3" || ds.Tables[0].Rows[0]["barcodestatus"].ToString() == "4") { //dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "产品已交坯"; dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "产品已在产"; } else if (ds.Tables[0].Rows[0]["barcodestatus"].ToString() == "1") { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = ""; // string sqlString = @"select // TP_PM_GroutingDailyDetail.GoodsID, // TP_PM_GroutingDailyDetail.GoodsCode, // TP_PM_GroutingDailyDetail.GoodsName, // TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode, // tp_pm_groutingdailydetail.groutingcount as GroutingNum, // tp_pm_groutingdailydetail.GroutingMouldCode as MouldCode, // tp_pm_groutingdailydetail.ispublicbody, // tp_pm_groutingdailydetail.Groutingdate, // tp_pm_groutingdailydetail.SpecialRepairFlag // from TP_PM_USEDBARCODE // left join // tp_pm_groutingdailydetail // on TP_PM_USEDBARCODE.groutingdailydetailid=tp_pm_groutingdailydetail.groutingdailydetailid // where tp_pm_groutingdailydetail.BarCode=:barcode"; GroutingDailyDetailID = Convert.ToInt32(ds.Tables[0].Rows[0]["GroutingDailyDetailid"].ToString()); string sqlString = @"select tp_pm_GroutingDailyDetail.groutingdailydetailid, tp_pm_GroutingDailyDetail.GoodsID, tp_pm_GroutingDailyDetail.GoodsCode, tp_pm_GroutingDailyDetail.GoodsName, tp_pm_GroutingDailyDetail.UserCode as GroutingUserCode, tp_pm_GroutingDailyDetail.groutingcount as GroutingNum, tp_pm_GroutingDailyDetail.GroutingMouldCode as MouldCode, tp_pm_GroutingDailyDetail.ispublicbody, tp_pm_GroutingDailyDetail.Groutingdate, tp_pm_GroutingDailyDetail.SpecialRepairFlag, tp_mst_logo.logoid, tp_mst_logo.logocode, tp_mst_logo.logoname from tp_pm_GroutingDailyDetail left join tp_mst_logo on tp_mst_logo.logoid=tp_pm_GroutingDailyDetail.logoid where tp_pm_GroutingDailyDetail.groutingdailydetailid=:groutingdailydetailid"; //where tp_pm_GroutingDailyDetail.barcode=:barcode"; paras = new OracleParameter[]{ new OracleParameter(":groutingdailydetailid",ds.Tables[0].Rows[0]["GroutingDailyDetailid"].ToString()), //new OracleParameter("barcode",OracleDbType.Varchar2,barcode,ParameterDirection.Input), }; ds = oracleConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { GroutingDailyDetailID = Convert.ToInt32(ds.Tables[0].Rows[0]["GroutingDailyDetailid"].ToString()); string sqlExistGoods = "select count(GoodsID) from TP_PC_ProcedureGoods where GoodsID=:GoodsID and ProcedureID=:ProcedureID"; paras = new OracleParameter[]{ new OracleParameter(":GoodsID",ds.Tables[0].Rows[0]["GoodsID"].ToString()), new OracleParameter(":ProcedureID",procedureID), }; DataSet dsGoods = oracleConn.GetSqlResultToDs(sqlExistGoods, paras); if (dsGoods.Tables[0].Rows[0][0].ToString() != "1") { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = @"条码[" + barcode + "]不可以经过该工序\n\r原因：条码对应的产品编码[" + ds.Tables[0].Rows[0]["GoodsCode"].ToString() + "]没有在该工序中配置"; } else { dr[Constant.BarCodeResultTableColumns.out_goodsID.ToString()] = ds.Tables[0].Rows[0]["GoodsID"].ToString(); dr[Constant.BarCodeResultTableColumns.out_goodsCode.ToString()] = ds.Tables[0].Rows[0]["GoodsCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_goodsName.ToString()] = ds.Tables[0].Rows[0]["GoodsName"].ToString(); dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = 0; dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = ds.Tables[0].Rows[0]["GroutingUserCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingNum.ToString()] = ds.Tables[0].Rows[0]["GroutingNum"].ToString(); dr[Constant.BarCodeResultTableColumns.out_mouldCode.ToString()] = ds.Tables[0].Rows[0]["MouldCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbody.ToString()] = ds.Tables[0].Rows[0]["ispublicbody"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_groutingdate.ToString()] = ds.Tables[0].Rows[0]["Groutingdate"].ToString(); dr[Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString()] = ds.Tables[0].Rows[0]["SpecialRepairFlag"].ToString(); dr[Constant.BarCodeResultTableColumns.out_isReFire.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_logoID.ToString()] = ds.Tables[0].Rows[0]["logoid"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoCode.ToString()] = ds.Tables[0].Rows[0]["logoCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoName.ToString()] = ds.Tables[0].Rows[0]["logoName"].ToString(); } } } } dr[Constant.BarCodeResultTableColumns.out_missFlag.ToString()] = 0; dtBarCode.Rows.Add(dr); returnDs.Tables.Add(dtBarCode); return returnDs; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #region 半成品检验校验条码 /// /// 半检检验条码 /// /// 产品条码 /// DataSet public static ServiceResultEntity SemiCheckBarcode(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity resultEntity = new ServiceResultEntity(); // 1.判断产品是否在产 string sql = @"select InScrapFlag, KilnCarID, KilnCarName, IsReworkFlag, SemiCheckID from TP_PM_InProduction where BarCode=:BarCode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), }; DataSet dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { //// 如果不合格，查询报损表里是否回收 //sql = "select recyclingflag from TP_PM_ScrapProduct " // + " where BarCode=:BarCode and CreateTime=(select max(CreateTime) " // + " from TP_PM_ScrapProduct " // + " where BarCode=:BarCode and ValueFlag=1)" // + " and ValueFlag=1"; //paras = new OracleParameter[]{ // new OracleParameter(":BarCode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), // }; //DataSet dsResult2 = oracleConn.GetSqlResultToDs(sql, paras); //if (dsResult2 != null && dsResult2.Tables[0].Rows.Count > 0) //{ // if (Convert.ToInt32(dsResult2.Tables[0].Rows[0]["recyclingflag"]) > 0) // { // resultEntity.Result = -4;// 已经回收不允许编辑 // resultEntity.Message = "此产品已经回收，不能改判"; // return resultEntity; // } //} if (dsResult.Tables[0].Rows[0]["InScrapFlag"].ToString() == "1") { resultEntity.Result = -2; //报损待审批 resultEntity.Message = "条码[" + barcode + "]已经报损待审批"; return resultEntity; } if (!string.IsNullOrEmpty(dsResult.Tables[0].Rows[0]["KilnCarID"].ToString())) { resultEntity.Result = -3; //登窑车后不允许半成品检测 resultEntity.Message = "条码[" + barcode + "]已经在窑车[" + dsResult.Tables[0].Rows[0]["KilnCarName"].ToString() + "]"; return resultEntity; } // chenxy 2019-10-02 半检返修 IsReworkFlag=2 if (dsResult.Tables[0].Rows[0]["IsReworkFlag"].ToString() == "2") { resultEntity.Result = -4; //半检返修 resultEntity.Message = "条码[" + barcode + "]已半检返修"; return resultEntity; } if (dsResult.Tables[0].Rows[0]["IsReworkFlag"].ToString() == "1") { resultEntity.Result = Convert.ToInt32(dsResult.Tables[0].Rows[0]["SemiCheckID"]);//编辑状态 resultEntity.Message = "返工"; } else { resultEntity.Result = "0";//新建状态 } } else { // 同时查是否在在产回收表中 sql = @"select SemiCheckID from TP_PM_InProductionTrash where BarCode=:BarCode and GoodsLevelTypeID=13 and SemiCheckID is not null"; // 13代表产品不合格 dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Result = Convert.ToInt32(dsResult.Tables[0].Rows[0]["SemiCheckID"]);//编辑状态 resultEntity.Message = "不合格"; } else { resultEntity.Result = -1;// 不在在产表中 resultEntity.Message = "条码[" + barcode + "]不在在产流程"; } } return resultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion #region 半成品编辑数据 /// /// 根据半成品检验数据ID,显示半成品数据信息 /// /// 半成品数据ID /// DataSet public static DataSet GetSemiCheckByID(int semiCheckID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); DataSet dsReturn = new DataSet(); string sqlString = @"select TP_PM_SemiCheck.SemiCheckID, TP_PM_SemiCheck.Barcode as BarCode, TP_PM_SemiCheck.Goodsid as GoodsID, TP_PM_SemiCheck.Goodscode as GoodsCode, TP_MST_Goods.Goodsname as GoodsName, TP_PM_SemiCheck.GoodsLevelID as DefectFlagID, TP_PM_SemiCheck.ReworkProcedureID as ReworkProcedureID, TP_PM_SemiCheck.ReworkProcedureCode, TP_PM_SemiCheck.Remarks as Remarks, TP_PM_SemiCheck.SemiCheckUserID as UserID, TP_PM_SemiCheck.SemiCheckUserCode as UserCode, tp_mst_user.UserName as UserName, TP_PM_SemiCheck.Goodsleveltypeid as GoodsLevelTypeID, TP_PM_SemiCheck.SpecialRepairflag, TP_PM_SemiCheck.GroutingUserCode, TP_PM_SemiCheck.GroutingMouldCode as MouldCode, TP_PM_SemiCheck.GroutingNum, TP_PM_SemiCheck.GroutingDate, TP_PM_SemiCheck.IsPublicBody, TP_PM_SemiCheck.logoid, TP_MST_Logo.logocode, TP_MST_Logo.logoname, TP_PM_SemiCheck.SemiCheckType, TP_PM_SemiCheck.ReworkUserID, TP_PM_SemiCheck.ReworkUserCode, TP_PM_SemiCheck.ReSemiCheckUserCode, tp_pc_procedure.procedurename as ReworkProcedureName, TP_PM_SemiCheck.ReSemiCheckType, TP_PM_SemiCheck.CreateTime, TP_PM_SemiCheck.ReSemiCheckTime, TP_PM_SemiCheck.SemiCheckTime from TP_PM_SemiCheck left join TP_MST_Goods on TP_PM_SemiCheck.Goodsid=TP_MST_Goods.Goodsid left join tp_mst_user on TP_PM_SemiCheck.SemiCheckUserID=tp_mst_user.userid left join TP_MST_Logo on TP_PM_SemiCheck.Logoid=TP_MST_Logo.Logoid left join tp_pc_procedure on TP_PM_SemiCheck.ReworkProcedureID=tp_pc_procedure.procedureID where TP_PM_SemiCheck.SemiCheckID=:SemiCheckID "; string sqlString2 = @" select defect.semicheckdefectid, defect.semicheckid, defect.DefectID, defect.defectcode, defect.defectname, defect.defectpositionid, defect.defectpositioncode, defect.defectpositionname, defect.scrapresponflag, Procedure.Procedurename as DefectProcedureName, Procedure.Procedurecode as DefectProcedureCode, defect.defectuserid, defect.defectusercode, TP_mst_user.username as defectusername, defect.remarks, defect.DefectProcedureID, defect.DefectProductionDataID from TP_PM_SemiCheckDefect defect left join TP_PC_Procedure Procedure on defect.defectprocedureid=Procedure.Procedureid left join TP_mst_user on defect.DefectUserID=TP_mst_user.userid where defect.SemiCheckID =:SemiCheckID"; string sqlString3 = @"select TP_PM_SCDefectResponsible.SemiCheckDefectID, TP_PM_SCDefectResponsible.Staffid, TP_HR_Staff.Staffcode as StaffCode, TP_HR_Staff.Staffname as StaffName, TP_PM_SCDefectResponsible.Staffstatus as StaffStatus, TP_PM_SCDefectResponsible.UJobsID, TP_PM_SCDefectResponsible.SJobsID from TP_PM_SCDefectResponsible left join TP_HR_Staff on TP_PM_SCDefectResponsible.StaffID=TP_HR_Staff.Staffid" + //where TP_PM_SCDefectResponsible.SemiCheckDefectID in //( // select SemiCheckDefectID from TP_PM_SemiCheckDefect where TP_PM_SemiCheckDefect.SemiCheckID=:SemiCheckID //)"; " where TP_PM_SCDefectResponsible.SemiCheckID = :SemiCheckID"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":SemiCheckID",OracleDbType.Int32, semiCheckID,ParameterDirection.Input), }; DataTable ds = con.GetSqlResultToDt(sqlString, paras); ds.TableName = "TP_PM_SemiCheck"; DataTable ds2 = con.GetSqlResultToDt(sqlString2, paras); ds2.TableName = "TP_PM_SemiCheckDefect"; DataTable ds3 = con.GetSqlResultToDt(sqlString3, paras); ds3.TableName = "TP_PM_SCDefectResponsible"; dsReturn.Tables.Add(ds); dsReturn.Tables.Add(ds2); dsReturn.Tables.Add(ds3); return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 获取条码所走过的工序 /// /// 根据条码获取经过的工序,用于绑定返工工序 /// /// 产品条码 /// DataSet public static DataSet GetSemiCheckPassProcedure(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // hg5020 半检只定成型和干补的责任 by chenxy 2020-03-30 // and(TP_PM_ProductionDataIn.modeltype = 8 or(TP_PM_ProductionDataIn.modeltype = 5 and(exists(select 1 from tp_pm_groutingdailydetail gdd where gdd.groutingdailydetailID = TP_PM_ProductionDataIn.groutingdailydetailID and gdd.SPECIALREPAIRFLAG = '0')))) // and (TP_PM_ProductionDataIn.modeltype <> 5 or (exists (select 1 from tp_pm_groutingdailydetail gdd where gdd.groutingdailydetailID=TP_PM_ProductionDataIn.groutingdailydetailID and gdd.SPECIALREPAIRFLAG='0'))) string twcode = con.GetSqlResultToStr("select t.tagcode||t.workcode twcode from tp_mst_account t where t.accountid=1"); if (twcode == "HEGII5020") { // hg5020 半检只定成型和干补的责任 by chenxy 2020-03-30 // and(TP_PM_ProductionDataIn.modeltype = 8 or(TP_PM_ProductionDataIn.modeltype = 5 and(exists(select 1 from tp_pm_groutingdailydetail gdd where gdd.groutingdailydetailID = TP_PM_ProductionDataIn.groutingdailydetailID and gdd.SPECIALREPAIRFLAG = '0')))) // and (TP_PM_ProductionDataIn.modeltype <> 5 or (exists (select 1 from tp_pm_groutingdailydetail gdd where gdd.groutingdailydetailID=TP_PM_ProductionDataIn.groutingdailydetailID and gdd.SPECIALREPAIRFLAG='0'))) string sqlString1 = @"select TP_PM_ProductionDataIn.Barcode as BarCode, TP_PM_ProductionDataIn.UserID, TP_PM_ProductionDataIn.UserCode, TP_PM_ProductionDataIn.UserName, TP_PM_ProductionDataIn.ProcedureID, TP_PM_ProductionDataIn.ProcedureCode, TP_PM_ProductionDataIn.ProcedureName, TP_PM_ProductionDataIn.ProductionDataID from TP_PM_ProductionDataIn --left join TP_PC_Procedure --on TP_PM_ProductionDataIn.ProcedureID=TP_PC_Procedure.ProcedureID where TP_PM_ProductionDataIn.BarCode=:BarCode and TP_PM_ProductionDataIn.valueflag = '1' --and (TP_PM_ProductionDataIn.modeltype <> 5 or (exists (select 1 from tp_pm_groutingdailydetail gdd where gdd.groutingdailydetailID=TP_PM_ProductionDataIn.groutingdailydetailID and gdd.SPECIALREPAIRFLAG='0'))) and (TP_PM_ProductionDataIn.modeltype=8 or (TP_PM_ProductionDataIn.modeltype = 5 and (exists (select 1 from tp_pm_groutingdailydetail gdd where gdd.groutingdailydetailID=TP_PM_ProductionDataIn.groutingdailydetailID and gdd.SPECIALREPAIRFLAG='0')))) order by TP_PM_ProductionDataIn.ProductionDataID "; // 干补后 半检 不能选成型责任 OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2, barcode,ParameterDirection.Input), }; return con.GetSqlResultToDs(sqlString1, paras1); } string sqlString = @"select TP_PM_ProductionDataIn.Barcode as BarCode, TP_PM_ProductionDataIn.UserID, TP_PM_ProductionDataIn.UserCode, TP_PM_ProductionDataIn.UserName, TP_PM_ProductionDataIn.ProcedureID, TP_PM_ProductionDataIn.ProcedureCode, TP_PM_ProductionDataIn.ProcedureName, TP_PM_ProductionDataIn.ProductionDataID from TP_PM_ProductionDataIn --left join TP_PC_Procedure --on TP_PM_ProductionDataIn.ProcedureID=TP_PC_Procedure.ProcedureID where TP_PM_ProductionDataIn.BarCode=:BarCode and TP_PM_ProductionDataIn.valueflag = '1' and (TP_PM_ProductionDataIn.modeltype <> 5 or (exists (select 1 from tp_pm_groutingdailydetail gdd where gdd.groutingdailydetailID=TP_PM_ProductionDataIn.groutingdailydetailID and gdd.SPECIALREPAIRFLAG='0'))) order by TP_PM_ProductionDataIn.ProductionDataID "; // 干补后 半检 不能选成型责任 OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2, barcode,ParameterDirection.Input), }; DataSet dsReturn = con.GetSqlResultToDs(sqlString, paras); return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 获取登陆帐户有无半检状态权限 /// /// 获取登陆帐户有无半检状态权限 /// /// 工号编码 /// public static int GetSemiCheckStatusFuntion(SUserInfo userInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string strSql = ""; strSql = @"SELECT F.Functionlevel, F.FunctionCode, F.FunctionName, F.FullName, F.FunctionFlag, F.FunctionButtonFlag, F.FormName, F.ButtonName FROM TP_MST_UserRight R LEFT OUTER JOIN TP_SYS_Function F ON (R.FunctionCode = F.FunctionCode OR R.FunctionCode = '[ALL]') WHERE F.ValueFlag = 1 AND F.FunctionCode NOT LIKE '0101%' AND F.FunctionCode NOT LIKE '0102%' and F.FunctionCode='06200104'"; strSql = strSql + " AND R.UserID = " + userInfo.UserID; DataSet ds = con.GetSqlResultToDs(strSql); if (ds != null && ds.Tables[0].Rows.Count > 0) { return 1; } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 复检验校验条码 /// /// 复检验条码 /// /// 产品条码 /// DataSet public static ServiceResultEntity ReSemiCheckBarcode(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity resultEntity = new ServiceResultEntity(); // 1.判断产品是否在产 string sql = @"select InScrapFlag, KilnCarID, KilnCarName, IsReworkFlag, SemiCheckID from TP_PM_InProduction where BarCode=:BarCode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), }; DataSet dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Result = Convert.ToInt32(dsResult.Tables[0].Rows[0]["SemiCheckID"] == DBNull.Value ? 0 : dsResult.Tables[0].Rows[0]["SemiCheckID"]); if (dsResult.Tables[0].Rows[0]["InScrapFlag"].ToString() == "1") { resultEntity.Result = -2; //报损待审批 resultEntity.Message = "条码[" + barcode + "]已经报损待审批"; return resultEntity; } if (!string.IsNullOrEmpty(dsResult.Tables[0].Rows[0]["KilnCarID"].ToString())) { resultEntity.Result = -3; //登窑车后不允许半成品检测 resultEntity.Message = "条码[" + barcode + "]已经在窑车[" + dsResult.Tables[0].Rows[0]["KilnCarName"].ToString() + "]"; return resultEntity; } // chenxy 2019-10-02 半检返修 IsReworkFlag=2 //if (dsResult.Tables[0].Rows[0]["IsReworkFlag"].ToString() == "0") if (dsResult.Tables[0].Rows[0]["IsReworkFlag"].ToString() != "1") { resultEntity.Result = -4; resultEntity.Message = "条码[" + barcode + "]不是返工状态"; } } else { resultEntity.Result = -1;// 不在在产表中 resultEntity.Message = "条码[" + barcode + "]不在在产流程"; } return resultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion #region 撤销复检验条码 /// /// 撤销复检验条码 /// /// 产品条码 /// DataSet public static ServiceResultEntity CancelSemiCheckBarcode(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity resultEntity = new ServiceResultEntity(); // 查出此条码是否存在复检，如果存在复检查出复检状态 string sql = @"select SemiCheckID,ReSemiCheckType,ProcedureID,productiondataid from TP_PM_SemiCheck where barcode=:barcode and ValueFlag='1' and accountid=:accountid order by SemiCheckID desc"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), }; //int? procedureID = null; //完成工序 int? productiondataid = null; DataSet dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { //procedureID = Convert.ToInt32(dsResult.Tables[0].Rows[0]["ProcedureID"]); productiondataid = Convert.ToInt32(dsResult.Tables[0].Rows[0]["productiondataid"]); if (dsResult.Tables[0].Rows[0]["ReSemiCheckType"].ToString() == "1") { resultEntity.Result = Convert.ToInt32(dsResult.Tables[0].Rows[0]["SemiCheckID"]); // 查出复检状态名称 sql = @"select ReSemiCheckTypeName from TP_SYS_ReSemiCheckType where ReSemiCheckTypeID=" + dsResult.Tables[0].Rows[0]["ReSemiCheckType"]; DataSet dss = oracleConn.GetSqlResultToDs(sql); if (dss != null && dss.Tables[0].Rows.Count > 0) { resultEntity.Message = dss.Tables[0].Rows[0]["ReSemiCheckTypeName"].ToString(); } else { resultEntity.Message = "合格(返)"; } // 合格(返) paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2,barcode,ParameterDirection.Input), }; sql = @" select InScrapFlag, KilnCarID, KilnCarName, IsReworkFlag, SemiCheckID,ProcedureID,productiondataid from TP_PM_InProduction where barcode=:barcode "; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { if (dsResult.Tables[0].Rows[0]["InScrapFlag"].ToString() == "1") { resultEntity.Result = -3; resultEntity.Message = "条码[" + barcode + "]已经报损待审批"; } if (!string.IsNullOrEmpty(dsResult.Tables[0].Rows[0]["KilnCarID"].ToString())) { resultEntity.Result = -4; resultEntity.Message = "条码[" + barcode + "]已经在窑车[" + dsResult.Tables[0].Rows[0]["KilnCarName"].ToString() + "]"; ; } // chenxy 2019-10-02 半检返修 IsReworkFlag=2 if (dsResult.Tables[0].Rows[0]["IsReworkFlag"].ToString() != "0") { resultEntity.Result = -5; resultEntity.Message = "条码[" + barcode + "]不是返工状态"; } //if (Convert.ToInt32(dsResult.Tables[0].Rows[0]["ProcedureID"]) != procedureID) if (Convert.ToInt32(dsResult.Tables[0].Rows[0]["productiondataid"]) != productiondataid) { resultEntity.Result = -6; resultEntity.Message = "条码[" + barcode + "]已经经过半检时完成的工序，不允许撤销"; } } else { resultEntity.Result = -7; resultEntity.Message = "条码[" + barcode + "]不在在产流程"; } } else if (dsResult.Tables[0].Rows[0]["ReSemiCheckType"].ToString() == "2") { resultEntity.Result = Convert.ToInt32(dsResult.Tables[0].Rows[0]["SemiCheckID"]); // 查出复检状态名称 sql = @"select ReSemiCheckTypeName from TP_SYS_ReSemiCheckType where ReSemiCheckTypeID=" + dsResult.Tables[0].Rows[0]["ReSemiCheckType"]; DataSet dss = oracleConn.GetSqlResultToDs(sql); if (dss != null && dss.Tables[0].Rows.Count > 0) { resultEntity.Message = dss.Tables[0].Rows[0]["ReSemiCheckTypeName"].ToString(); } else { resultEntity.Message = "不合格(返)"; } // 如果不合格，查询报损表里是否回收 sql = "select recyclingflag from TP_PM_ScrapProduct " + " where BarCode=:BarCode and CreateTime=(select max(CreateTime) " + " from TP_PM_ScrapProduct " + " where BarCode=:BarCode and ValueFlag=1)" + " and ValueFlag=1"; paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), }; DataSet dsResult2 = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult2 != null && dsResult2.Tables[0].Rows.Count > 0) { if (Convert.ToInt32(dsResult2.Tables[0].Rows[0]["recyclingflag"]) > 0) { resultEntity.Result = -9;// 已经回收不允许撤销 resultEntity.Message = "此产品已经回收，不能撤销"; return resultEntity; } } // 不合格(返) sql = @" select 1 from TP_PM_InProductionTrash where barcode=:barcode and GoodsLevelTypeID=14 and SemiCheckID is not null "; paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2,barcode,ParameterDirection.Input), }; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count == 0) { resultEntity.Result = -8; resultEntity.Message = "条码[" + barcode + "]不在在产回收站中"; } } else if (dsResult.Tables[0].Rows[0]["ReSemiCheckType"].ToString() == "0") { // 未复检 resultEntity.Result = -2; resultEntity.Message = "条码[" + barcode + "]没有复检不允许撤销"; } } else { resultEntity.Result = -1;// 不在半成品检验 resultEntity.Message = "条码[" + barcode + "]没有半成品检验"; } // 查出此条码是否存在复检，如果存在复检查出复检状态 return resultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion #region 校验是否允许进行撤销操作 /// /// 校验条码是否允许撤销，如果不允许提示错误消息 /// /// 原时间 /// 允许撤销天数 /// 设置code,用于返回不同错误消息 /// /// public static ServiceResultEntity BarcodeAllowCancel(DateTime orgTime, int days, string settingCode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { ServiceResultEntity resultEntity = new ServiceResultEntity(); orgTime = orgTime.Date; // 取日期部分 DateTime currentTime = DateTime.Now.Date; // 参数验证用服务端当前设置 string sqlString = "SELECT sst.settingvalue\n" + " FROM tp_mst_systemsetting sst\n" + " WHERE sst.accountid = " + sUserInfo.AccountID + "\n" + " AND sst.settingcode = '" + settingCode + "'"; string strValue = oracleConn.GetSqlResultToStr(sqlString); int pDays = 0; if (int.TryParse(strValue, out pDays)) { days = pDays; } if (days == 0) { // 表示可以撤销 resultEntity.Result = 1; return resultEntity; } if (orgTime > currentTime.AddDays(-days)) { // 表示可以撤销 resultEntity.Result = 1; } else { resultEntity.Result = -1; // 提示不同错误消息 if (settingCode.Equals(Constant.SettingType.S_PM_002.ToString())) { //交坯限制天数 resultEntity.Message = string.Format(Messages.MSG_S_PM_002, orgTime.ToString("yyyy-MM-dd"), days.ToString(), orgTime.AddDays(days - 1).ToString("yyyy-MM-dd")); } else if (settingCode.Equals(Constant.SettingType.S_PM_003.ToString())) { //交坯撤销限制天数 resultEntity.Message = string.Format(Messages.MSG_S_PM_003, orgTime.ToString("yyyy-MM-dd"), days.ToString(), orgTime.AddDays(days - 1).ToString("yyyy-MM-dd")); } else if (settingCode.Equals(Constant.SettingType.S_PM_004.ToString())) { //计件撤销限制天数 resultEntity.Message = string.Format(Messages.MSG_S_PM_004, orgTime.ToString("yyyy-MM-dd"), days.ToString(), orgTime.AddDays(days - 1).ToString("yyyy-MM-dd")); } else if (settingCode.Equals(Constant.SettingType.S_PM_005.ToString())) { //成品撤销限制天数 resultEntity.Message = string.Format(Messages.MSG_S_PM_005, orgTime.ToString("yyyy-MM-dd"), days.ToString(), orgTime.AddDays(days - 1).ToString("yyyy-MM-dd")); } else if (settingCode.Equals(Constant.SettingType.S_PM_006.ToString())) { //损坯撤销限制天数 resultEntity.Message = string.Format(Messages.MSG_S_PM_006, orgTime.ToString("yyyy-MM-dd"), days.ToString(), orgTime.AddDays(days - 1).ToString("yyyy-MM-dd")); } else if (settingCode.Equals(Constant.SettingType.S_PM_007.ToString())) { //半检登记改判限制天数 resultEntity.Message = string.Format(Messages.MSG_S_PM_007, orgTime.ToString("yyyy-MM-dd"), days.ToString(), orgTime.AddDays(days - 1).ToString("yyyy-MM-dd")); } else if (settingCode.Equals(Constant.SettingType.S_PM_008.ToString())) { //半检复检撤销限制天数 resultEntity.Message = string.Format(Messages.MSG_S_PM_008, orgTime.ToString("yyyy-MM-dd"), days.ToString(), orgTime.AddDays(days - 1).ToString("yyyy-MM-dd")); } else if (settingCode.Equals(Constant.SettingType.S_PM_009.ToString())) { //成检登记改判限制天数 resultEntity.Message = string.Format(Messages.MSG_S_PM_009, orgTime.ToString("yyyy-MM-dd"), days.ToString(), orgTime.AddDays(days - 1).ToString("yyyy-MM-dd")); } } return resultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion #region 生产订单 wangx 2017-2-7 /// /// 获取生产订单一览列表 /// /// /// /// public static DataSet GetOrderList(OrderEntity order, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); List parameters = new List(); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input)); string sqlString = @"select OrderID, OrderNo, OrderDate, VBELN, POSNR, SUMMARY, Remarks, ValueFlag, displayno, CreateTime, UpdateTime from TP_PM_Order t where AccountID=:AccountID "; // 订单ID 编辑信息用 if (order.OrderID > 0) { sqlString = sqlString + " AND OrderID=:OrderID"; parameters.Add(new OracleParameter(":OrderID", OracleDbType.Int32, order.OrderID, ParameterDirection.Input)); } // 订单号 if (!string.IsNullOrEmpty(order.OrderNo)) { sqlString = sqlString + " AND instr(OrderNo,:OrderNo)>0"; parameters.Add(new OracleParameter(":OrderNo", OracleDbType.NVarchar2, order.OrderNo, ParameterDirection.Input)); } // 订单时间起始 if (order.OrderFromDate.HasValue) { sqlString = sqlString + " AND OrderDate >= :OrderFromDate "; parameters.Add(new OracleParameter(":OrderFromDate", OracleDbType.Date, order.OrderFromDate.Value, ParameterDirection.Input)); } // 订单时间结束 if (order.OrderToDate.HasValue) { sqlString = sqlString + " AND OrderDate <= :OrderToDate "; parameters.Add(new OracleParameter(":OrderToDate", OracleDbType.Date, order.OrderToDate.Value, ParameterDirection.Input)); } // 有效标识 if (!string.IsNullOrEmpty(order.ValueFlagList)) { sqlString = sqlString + " AND instr(','||:ValueFlag||',',','||ValueFlag||',')>0 "; parameters.Add(new OracleParameter(":ValueFlag", OracleDbType.NVarchar2, order.ValueFlagList, ParameterDirection.Input)); } // 备注 if (!string.IsNullOrEmpty(order.Remarks)) { sqlString = sqlString + " AND instr(Remarks,:Remarks)>0"; parameters.Add(new OracleParameter(":Remarks", OracleDbType.NVarchar2, order.Remarks, ParameterDirection.Input)); } sqlString = sqlString + " order by t.displayno, t.orderid"; DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray()); if (ds != null && ds.Tables[0].Rows.Count > 0) { ServiceResultEntity sre = GetOrderDetail(order.OrderID); DataTable dtOrderDetail = sre.Data.Tables[0]; dtOrderDetail.DataSet?.Tables.Clear(); dtOrderDetail.TableName = "OrderDetail"; ds.Tables.Add(dtOrderDetail); return ds; } return null; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取生产订单明细表 /// /// /// /// public static ServiceResultEntity GetOrderDetail(int orderID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); ServiceResultEntity sre = new ServiceResultEntity(); try { string sqlString = @" SELECT OD.MATERIALCODE, OD.MAKTX, OD.KWMENG, G.GOODSCODE, L.LOGONAME FROM TP_PM_ORDERDETAIL OD LEFT JOIN (SELECT PB.MATNR, PB.GOODSID, PB.LOGOID FROM TP_MST_PACKINGBOM PB WHERE PB.VALUEFLAG = '1' GROUP BY PB.MATNR, PB.GOODSID, PB.LOGOID) T ON T.MATNR = OD.MATERIALCODE LEFT JOIN TP_MST_GOODS G ON G.GOODSID = T.GOODSID LEFT JOIN TP_MST_LOGO L ON L.LOGOID = T.LOGOID WHERE OD.VALUEFLAG = '1' AND OD.ORDERID = :ORDERID "; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":ORDERID", orderID), }; sre.Data = con.GetSqlResultToDs(sqlString, paras); return sre; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 产成品交接 2017-2-8 /// /// 获取产成品交接 /// /// /// /// public static DataSet GetFinishedHandover(SearchFinishedProductEntity entity, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); List parameters = new List(); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input)); string sqlString = @"select O.OrderNo, F.FHTime, F.FHUserCode, F.BarCode, F.GoodsCode, L.LogoName, F. GroutingUserCode, F.GroutingMouldCode, F.GroutingDate, G.DeliverTime, --(Goods.MaterialCode || L.tagcode || G.onlycode) as FinishedBarCode nvl(g.outlabelcode, Goods.MaterialCode || (select a.workcode from tp_mst_account a where a.accountid = g.accountid) || L.tagcode || G.onlycode) as FinishedBarCode from TP_PM_FinishedProduct F left join TP_PM_Order O on F.FHOrderID=O.OrderID left join TP_MST_Logo L on F.LogoID=L.LogoID left join TP_PM_GroutingDailyDetail G on F.GroutingDailyDetailID=G.GroutingDailyDetailID left join TP_MST_Goods Goods on F.GoodsID=Goods.GoodsID left join TP_MST_GoodsType GoodsType on Goods.GoodsTypeID=GoodsType.GoodsTypeID where F.AccountID=:AccountID and F.FHTime is not null "; // 交接时间起始 if (entity.FHTimeStart.HasValue) { sqlString = sqlString + " AND F.FHTime >= :FHTimeStart "; parameters.Add(new OracleParameter(":FHTimeStart", OracleDbType.Date, entity.FHTimeStart.Value, ParameterDirection.Input)); } // 交接时间结束 if (entity.FHTimeEnd.HasValue) { sqlString = sqlString + " AND F.FHTime <= :FHTimeEnd "; parameters.Add(new OracleParameter(":FHTimeEnd", OracleDbType.Date, entity.FHTimeEnd.Value.AddSeconds(59), ParameterDirection.Input)); } // 订单号 if (!string.IsNullOrEmpty(entity.OrderNo)) { sqlString = sqlString + " AND instr(O.OrderNo,:OrderNo)>0"; parameters.Add(new OracleParameter(":OrderNo", OracleDbType.NVarchar2, entity.OrderNo, ParameterDirection.Input)); } // 产品类别编码 if (!string.IsNullOrEmpty(entity.GoodsTypeCode)) { sqlString = sqlString + " AND instr(GoodsType.GoodsTypeCode,:GoodsTypeCode)=1"; parameters.Add(new OracleParameter(":GoodsTypeCode", OracleDbType.NVarchar2, entity.GoodsTypeCode, ParameterDirection.Input)); } // 产品型号 if (!string.IsNullOrEmpty(entity.GoodsModel)) { //sqlString = sqlString + " AND instr(Goods.GoodsModel,:GoodsModel)>0"; sqlString = sqlString + " AND instr(Goods.GoodsCode,:GoodsCode)>0"; parameters.Add(new OracleParameter(":GoodsCode", OracleDbType.NVarchar2, entity.GoodsModel, ParameterDirection.Input)); } // 产品商标ID集 if (!string.IsNullOrEmpty(entity.LogoIDS)) { sqlString += " AND instr(','||:LogoIDS||',', ',' || L.logoid || ',') > 0"; parameters.Add(new OracleParameter(":LogoIDS", OracleDbType.Varchar2, entity.LogoIDS, ParameterDirection.Input)); } // 交接工号 if (!string.IsNullOrEmpty(entity.FHUserCode)) { sqlString = sqlString + " AND instr(F.FHUserCode,:FHUserCode)>0"; parameters.Add(new OracleParameter(":FHUserCode", OracleDbType.NVarchar2, entity.FHUserCode, ParameterDirection.Input)); } sqlString = sqlString + " order by F.FHTime desc"; DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray()); if (ds != null && ds.Tables[0].Rows.Count > 0) { return ds; } return null; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 产成品交接验证 /// /// 产成品交接条码验证 /// /// /// /// 0:未交接，1:已交接，2:不限 /// public static ServiceResultEntity FinishedHandoverBarcode(string barcode, SUserInfo sUserInfo, int handoverFlag = 0) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity resultEntity = new ServiceResultEntity(); // 如果是板码 string banMa = string.Empty; if (barcode.Length == 8) { if (handoverFlag == 1) { resultEntity.Result = -1; resultEntity.Message = "撤销交接不允许扫板码。"; return resultEntity; } banMa = barcode; string sqlBanMa = @" SELECT BARCODE, FINISHEDLOADBATCHNO FROM TP_PM_FINISHEDPRODUCT WHERE FHTIME IS NULL AND LCFHTIME IS NULL AND BANMA = :barcode "; OracleParameter[] parasBanMa = new OracleParameter[] { new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input) }; DataTable dtBanMa = oracleConn.GetSqlResultToDt(sqlBanMa, parasBanMa); if (dtBanMa.Rows.Count == 0) { resultEntity.Result = -1; resultEntity.Message = "当前载具上无可交接的产品。"; return resultEntity; } DataTable dtFinishedLoadBatchNo = dtBanMa.DefaultView.ToTable(true, new string[] { "FINISHEDLOADBATCHNO" }); if (dtFinishedLoadBatchNo.Rows.Count > 1) { resultEntity.Result = -1; resultEntity.Message = "当前载具存在超过两版以上的产品。"; return resultEntity; } barcode = dtBanMa.Rows[0]["BARCODE"].ToString(); } string gbarcode = null; string FINISHEDLOADBATCHNO = null; string sqlString = @"select FUN_CMN_GetBarCode(:barcode,null,:accountid) From DUAL"; OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras1); if (ds != null && ds.Tables[0].Rows.Count > 0) { gbarcode = ds.Tables[0].Rows[0][0].ToString(); } // 1.判断产品是否在产成品表中 string sql = @"select f.FHUserID, f.FHUserCode, f.LCFHUSERCODE, f.GoodsID, f.GoodsCode, f.GoodsName, f.BarCode, f.FINISHEDLOADBATCHNO from TP_PM_FinishedProduct f where f.BarCode=:BarCode"; // where BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid)"; string currentBarcode = string.Empty; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2,gbarcode,ParameterDirection.Input), //new OracleParameter(":Accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), }; DataSet dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { FINISHEDLOADBATCHNO = dsResult.Tables[0].Rows[0]["FINISHEDLOADBATCHNO"].ToString(); // 在产成品表中 #region 是否交接过 string fhUserCode = dsResult.Tables[0].Rows[0]["FHUserCode"].ToString(); string lcfhUserCode = dsResult.Tables[0].Rows[0]["LCFHUSERCODE"].ToString(); if ( !string.IsNullOrEmpty(lcfhUserCode)) { resultEntity.Result = -1; //已交接，不能再次进行交接 resultEntity.Message = "此产品【" + barcode + "】已完成裸瓷交接，不能进行该操作"; return resultEntity; } if (handoverFlag == 0 && !string.IsNullOrEmpty(fhUserCode)) { resultEntity.Result = -1; //已交接，不能再次进行交接 resultEntity.Message = "此产品【" + barcode + "】已交接，不能进行该操作"; return resultEntity; } if (handoverFlag == 1 && string.IsNullOrEmpty(fhUserCode)) { resultEntity.Result = -1; //已交接，不能再次进行交接 resultEntity.Message = "此产品【" + barcode + "】未交接，不能进行该操作"; return resultEntity; } #endregion #region 是否是裸瓷交接 //查询最近一次是不是裸瓷保存 sql = "SELECT procedureid\n" + " FROM (SELECT procedureid\n" + " FROM tp_pm_productiondata\n" + " WHERE barcode = :BarCode\n" + " ORDER BY productiondataid DESC)\n" + " WHERE rownum = 1"; paras = new OracleParameter[]{ new OracleParameter(":Barcode",OracleDbType.Varchar2, gbarcode,ParameterDirection.Input), }; DataTable productionDataIDdt = oracleConn.GetSqlResultToDt(sql, paras); if (productionDataIDdt != null && productionDataIDdt.Rows.Count > 0 && productionDataIDdt.Rows[0]["procedureid"].ToString() == "159" ) { resultEntity.Result = -1; //已交接，不能再次进行交接 resultEntity.Message = "此产品【" + barcode + "】属于裸瓷产品，不能进行该操作，请使用裸瓷交接"; return resultEntity; } #endregion } else { if (handoverFlag == 0) { // 不在产成品表中 #region 1 查询是否报损待审批与漏扫 sql = @"select I.InScrapFlag,P.procedureName,I.GoodsCode from TP_PM_InProduction I left join tp_pc_procedure P on I.flowprocedureid=P.procedureid where I.BarCode=:BarCode"; //where I.BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid)"; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { // 存在在产表中 if (dsResult.Tables[0].Rows[0]["InScrapFlag"].ToString() == "1") { resultEntity.Result = -3; //产品已经待审批报损 resultEntity.Message = "此产品【" + barcode + "】己待审报废，不能进行该操作"; return resultEntity; } int missbarcode = AddBarCodeMissingFinishedHandover(oracleConn, gbarcode, sUserInfo); if (missbarcode > 0) { // 第一次记录漏扫 resultEntity.Result = -4; resultEntity.OtherStatus = missbarcode;// 需要弹窗 resultEntity.Message = "此产品【" + barcode + "】当前工序为【" + dsResult.Tables[0].Rows[0]["procedureName"].ToString() + "】不是产成品，不能进行该操作"; return resultEntity; } else { resultEntity.Result = -5; resultEntity.OtherStatus = -1;// 不需要弹窗 resultEntity.Message = "此产品【" + barcode + "】当前工序为【" + dsResult.Tables[0].Rows[0]["procedureName"].ToString() + "】不是产成品，不能进行该操作"; return resultEntity; } } #endregion } #region 2 条码在废弃产品表中且审批通过，提示：此产品【999999】已被废弃，不能进行该操作。 //sql = @"select GoodsCode from tp_pm_inproductiontrash where BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid)"; sql = @"select GoodsCode from tp_pm_inproductiontrash where BarCode=:BarCode"; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Result = -6; //产品已经报损 resultEntity.Message = "此产品【" + barcode + "】已被废弃,不能进行该操作"; return resultEntity; } #endregion #region 3 未交坯 //sql = "select GoodsCode from TP_PM_GroutingDailyDetail where BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid) and DeliverTime is null"; sql = "select GoodsCode from TP_PM_GroutingDailyDetail where BarCode=:BarCode and DeliverTime is null"; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Result = -7; //产品未交坏 resultEntity.Message = "此产品【" + barcode + "】还未交坯,不能进行该操作"; return resultEntity; } else { resultEntity.Result = -8; //无效条码 resultEntity.Message = "此条码【" + barcode + "】为无效条码,不能进行该操作"; return resultEntity; } #endregion } #region 校验包装箱编码于物料编码是否相同 // 如果输入的是产品条码，不检查 // if (barcode.Length != 11) // { // string Strsql = @"SELECT MATERIALCODE FROM TP_PM_GROUTINGDAILYDETAIL WHERE OUTLABELCODE = :barcode"; // OracleParameter[] paras2 = new OracleParameter[]{ // new OracleParameter(":barcode",OracleDbType.NVarchar2,barcode,ParameterDirection.Input), // //new OracleParameter(":Accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), // }; // DataSet dst = oracleConn.GetSqlResultToDs(Strsql, paras2); // if (dst == null || dst.Tables[0].Rows.Count == 0) // { // resultEntity.Result = -1; // resultEntity.Message = "物料编码与外包装箱码不匹配,不允许装板"; // return resultEntity; // } // string barcodeSub = string.Empty; // if (barcode.Length >= 14) // { // barcodeSub = barcode.Substring(0, 14); // } // if (dst.Tables[0].Rows[0]["MATERIALCODE"].ToString() != barcodeSub) // { // resultEntity.Result = -1; // resultEntity.Message = "物料编码与外包装箱码不匹配,不允许装板"; // return resultEntity; // } //} #endregion #region 查询产品相关信息 /* sql = @"select gd.BarCode, l.logoid, l.logocode, l.logoname, gd.goodscode, gd.UserID, gd.UserCode as GroutingUserCode, gd.GroutingMouldCode as MouldCode, gd.GroutingDate, gd.DeliverTime, --Goods.GoodsCode as GoodsModel, --nvl(Goods.MaterialCode,Goods.GoodsCode) as GoodsModel, -- 用物料编码验证产品型号是否一致 nvl(gd.MaterialCode, nvl(Goods.MaterialCode,Goods.GoodsCode)) as GoodsModel, -- 用物料编码验证产品型号是否一致 Goods.PlateLimitNum, --(Goods.MaterialCode || l.tagcode || gd.onlycode) as FinishedBarCode , nvl(gd.outlabelcode, Goods.MaterialCode || (select a.workcode from tp_mst_account a where a.accountid = gd.accountid) || l.tagcode || gd.onlycode) as FinishedBarCode, o.ORDERNO from TP_PM_GroutingDailyDetail gd inner join tp_mst_logo l on gd.logoid=l.logoid inner join TP_PM_FinishedProduct f on f.GROUTINGDAILYDETAILID=gd.GROUTINGDAILYDETAILID left join tp_pm_order o on o.orderid = f.FHORDERID inner join TP_MST_Goods Goods on gd.goodsid=Goods.goodsid"; //where gd.BarCode=:BarCode"; */ sql = "select gd.BarCode,\n" + " l.logoid,\n" + " l.logocode,\n" + " l.logoname,\n" + " gd.goodscode,\n" + " gd.UserID,\n" + " gd.UserCode as GroutingUserCode,\n" + " gd.GroutingMouldCode as MouldCode,\n" + " gd.GroutingDate,\n" + " gd.DeliverTime,\n" + //" --Goods.GoodsCode as GoodsModel,\n" + //" --nvl(Goods.MaterialCode,Goods.GoodsCode) as GoodsModel, -- 用物料编码验证产品型号是否一致\n" + " nvl(gd.MaterialCode, nvl(Goods.MaterialCode, Goods.GoodsCode)) ||'#'|| to_char(Goods.GOODS_LINE_TYPE) as GoodsModel, -- 用物料编码验证产品型号是否一致\n" + " Goods.PlateLimitNum,\n" + //" --(Goods.MaterialCode || l.tagcode || gd.onlycode) as FinishedBarCode ,\n" + " nvl(gd.outlabelcode,\n" + " Goods.MaterialCode ||\n" + " (select a.workcode\n" + " from tp_mst_account a\n" + " where a.accountid = gd.accountid) || l.tagcode || gd.onlycode) as FinishedBarCode,\n" + " o.ORDERNO,\n" + " SUBSTR( gd.OUTLABELCODE, 0, 14 ) OUTLABELCODE,\n" + " gd.MATERIALCODE,\n" + " gd.OUTLABELCODE as OUTLABELCODES,\n" + " f.BanMa\n" + " from TP_PM_GroutingDailyDetail gd\n" + " inner join tp_mst_logo l\n" + " on gd.logoid = l.logoid\n" + " inner join TP_PM_FinishedProduct f\n" + " on f.GROUTINGDAILYDETAILID = gd.GROUTINGDAILYDETAILID\n" + " left join tp_pm_order o\n" + " on o.orderid = f.FHORDERID\n" + " inner join TP_MST_Goods Goods\n" + " on gd.goodsid = Goods.goodsid \n"; if (string.IsNullOrWhiteSpace(FINISHEDLOADBATCHNO)) { //sql += " where gd.BarCode=:BarCode"; sql += " where gd.BarCode='"+ gbarcode+"'"; } else { //sql += " where gd.GROUTINGDAILYDETAILID in ( select GROUTINGDAILYDETAILID from TP_PM_FinishedProduct f where f.FINISHEDLOADBATCHNO = :BarCode)"; //sql += " where f.FINISHEDLOADBATCHNO = :BarCode "; sql += " where f.FINISHEDLOADBATCHNO ='" + FINISHEDLOADBATCHNO + "'"; paras[0].Value = FINISHEDLOADBATCHNO; } //where gd.BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid)"; // 不用参数比用参数效率高 ？？？ edit by chenxy //dsResult = oracleConn.GetSqlResultToDs(sql, paras); dsResult = oracleConn.GetSqlResultToDs(sql); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { for(int i = 0; i< dsResult.Tables[0].Rows.Count; i++) { if (dsResult.Tables[0].Rows[i]["OUTLABELCODE"].ToString() != dsResult.Tables[0].Rows[i]["MATERIALCODE"].ToString()) { resultEntity.Result = -1; resultEntity.Message = "物料编码与外包装箱码不匹配,不允许装板。外包装箱码：" + dsResult.Tables[0].Rows[i]["OUTLABELCODES"]; return resultEntity; } } //增加校验 普通项目类别组为NORM才需要卡控，251202 string mtposmarasql = "SELECT SSS.MTPOSMARA FROM TP_PC_SAPBOMDETAIL SSS WHERE SSS.MATNR = '" + dsResult.Tables[0].Rows[0]["MATERIALCODE"].ToString() + "'"; DataTable mtposmaradata = oracleConn.GetSqlResultToDt(mtposmarasql, null); if (mtposmaradata != null && mtposmaradata.Rows.Count > 0 && mtposmaradata.Rows[0]["MTPOSMARA"].ToString() == "NORM") { //增加SAP接口校验产成品交接卡控数量->SAP根据物料判断STO单剩余可产出数量，小于不允许保存 //根据系统参数判断 是否开启卡控 //add by qq 20250918 #region 卡控产成品交接数量 //S_PC_002:计划卡控默认选项(产成品交接) string sqs = "SELECT sss.settingcode, sss.settingvalue\n" + " FROM tp_mst_systemsetting sss\n" + " WHERE sss.accountid = " + sUserInfo.AccountID + "\n" + " AND sss.settingcode = 'S_PC_002'"; DataTable data = oracleConn.GetSqlResultToDt(sqs, null); if (data != null && data.Rows.Count > 0) { string settingvalue = data.Rows[0]["settingvalue"].ToString(); string materialCode = dsResult.Tables[0].Rows[0]["MATERIALCODE"].ToString(); string ztype = ""; string message = ""; //开启校验 if (settingvalue == "1") { //查询接口数量 decimal stonum = 0; // 配置文件 string postString = "{\"IN_WERKS\":\"" + "5000" + "\",\"IN_TABLE\":{\"item\":{\"MATNR\":\"" + materialCode + "\"}}}"; INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII); string url082 = ini.ReadIniData("SAP_NEW_INFO", "Url082"); //测试 //url082 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZMMFM082"; string result = string.Empty; //调用接口 try { result = SAPDataLogic.PostData(url082, postString, "POST"); } catch (Exception ex) { resultEntity.Result = -1; resultEntity.Message = "SAP-ZMMFM082接口调用失败，" + ex.Message; return resultEntity; } //判断物料是否一致 if (JObject.Parse(result)["OUT_TABLE"] != null && JObject.Parse(result)["OUT_TABLE"].ToString().Length > 0) { ztype = JObject.Parse(result)["ZTYPE"].ToString(); message = JObject.Parse(result)["ZMSG"].ToString(); Dictionary obj = JsonConvert.DeserializeObject>(result); object OUT_TABLE; obj.TryGetValue("OUT_TABLE", out OUT_TABLE); obj = JsonConvert.DeserializeObject>(OUT_TABLE + ""); object item1; obj.TryGetValue("item", out item1); JObject jsonObject = JObject.Parse(item1.ToString()); if (ztype == "S") { //物料编码 string matnr = jsonObject["MATNR"].ToString(); //未清STO总数 decimal zwjsl = Convert.ToDecimal(jsonObject["ZWJSL"].ToString()); //未清SO数量 decimal zwjso = Convert.ToDecimal(jsonObject["ZWJSO"].ToString()); stonum = zwjsl + zwjso; if (materialCode == matnr) { //查询本地未同步SAP数量 string sqlstr = @"SELECT SUM(OUTPUTNUM) OUTPUTNUM FROM tsap_hegii_workdata_bg WB LEFT JOIN tsap_hegii_datalog_bg D ON WB.LOGID = D.LOGID WHERE D.datacode = '60' And D.DATASTUTS = 'F' AND WB.SAPCODE = '" + materialCode + "' GROUP BY WB.SAPCODE"; DataTable numdata = oracleConn.GetSqlResultToDt(sqlstr, null); //未同步数量 decimal unscynnum = 0; if (numdata != null && numdata.Rows.Count > 0) { unscynnum = Convert.ToDecimal(numdata.Rows[0]["OUTPUTNUM"]); } //此次交接一板数量 decimal bannum = Convert.ToDecimal(dsResult.Tables[0].Rows.Count); decimal allnum = unscynnum + bannum; //判断是否允许交接 if (stonum < allnum) { resultEntity.Result = 1; resultEntity.Message = "该板及未同步交接产品物料【"+matnr+"】数量【"+ allnum + "】超出SAP允许出单数量【" + stonum + "】, 是否暂存？（暂存后需扫码推送SAP数据）"; resultEntity.Data = dsResult; return resultEntity; } } else { resultEntity.Result = -1; resultEntity.Message = "物料不一致；SAP返回物料【"+ matnr + "】"; return resultEntity; } } else { resultEntity.Result = -1; resultEntity.Message = message; return resultEntity; } } } } #endregion } resultEntity.Result = 1;//成功 //resultEntity.Data = new DataSet(); //resultEntity.Data.Tables.Add(dsResult.Tables[0].Copy()); resultEntity.Data = dsResult; } else { resultEntity.Result = -2;// 条码未注浆 resultEntity.Message = "条码【" + barcode + "】未注浆"; return resultEntity; } #endregion return resultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 产成品交接条码验证（二次） /// /// /// /// 0:未交接，1:已交接，2:不限 /// public static ServiceResultEntity FinishedHandoverBarcodeAgain(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity resultEntity = new ServiceResultEntity(); // 如果是板码 string banMa = string.Empty; if (barcode.Length == 8) { banMa = barcode; string sqlBanMa = @" SELECT BARCODE, FINISHEDLOADBATCHNO FROM TP_PM_FINISHEDPRODUCT WHERE FHTIME IS NOT NULL AND TemporarilyFlag = 1 AND BANMA = :barcode "; OracleParameter[] parasBanMa = new OracleParameter[] { new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input) }; DataTable dtBanMa = oracleConn.GetSqlResultToDt(sqlBanMa, parasBanMa); if (dtBanMa.Rows.Count == 0) { resultEntity.Result = -1; resultEntity.Message = "当前载具上无暂存可交接的产品。"; return resultEntity; } DataTable dtFinishedLoadBatchNo = dtBanMa.DefaultView.ToTable(true, new string[] { "FINISHEDLOADBATCHNO" }); if (dtFinishedLoadBatchNo.Rows.Count > 1) { resultEntity.Result = -1; resultEntity.Message = "当前载具存在超过两版以上的暂存产品。"; return resultEntity; } barcode = dtBanMa.Rows[0]["BARCODE"].ToString(); } string gbarcode = null; string FINISHEDLOADBATCHNO = null; string sqlString = @"select FUN_CMN_GetBarCode(:barcode,null,:accountid) From DUAL"; OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras1); if (ds != null && ds.Tables[0].Rows.Count > 0) { gbarcode = ds.Tables[0].Rows[0][0].ToString(); } // 1.判断产品是否在产成品表中 string sql = @"select f.FHUserID, f.FHUserCode, f.LCFHUSERCODE, f.GoodsID, f.GoodsCode, f.GoodsName, f.BarCode, f.FINISHEDLOADBATCHNO, f.TEMPORARILYFLAG from TP_PM_FinishedProduct f where f.BarCode=:BarCode"; string currentBarcode = string.Empty; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2,gbarcode,ParameterDirection.Input), }; DataSet dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { FINISHEDLOADBATCHNO = dsResult.Tables[0].Rows[0]["FINISHEDLOADBATCHNO"].ToString(); // 在产成品表中 #region 是否交接过 string fhUserCode = dsResult.Tables[0].Rows[0]["FHUserCode"].ToString(); string temporarilyFlag = dsResult.Tables[0].Rows[0]["TEMPORARILYFLAG"].ToString(); if (string.IsNullOrEmpty(fhUserCode)) { resultEntity.Result = -1; //已交接，不能再次进行交接 resultEntity.Message = "此产品【" + barcode + "】非暂存产品，不能进行该操作"; return resultEntity; } if (temporarilyFlag == "0") { resultEntity.Result = -1; //已交接，不能再次进行交接 resultEntity.Message = "此产品【" + barcode + "】非暂存产品，不能进行该操作"; return resultEntity; } #endregion } else { resultEntity.Result = -8; //无效条码 resultEntity.Message = "此条码【" + barcode + "】非暂存产品,不能进行该操作"; return resultEntity; } #region 查询产品相关信息 sql = "select gd.BarCode,\n" + " l.logoid,\n" + " l.logocode,\n" + " l.logoname,\n" + " gd.goodscode,\n" + " gd.UserID,\n" + " gd.UserCode as GroutingUserCode,\n" + " gd.GroutingMouldCode as MouldCode,\n" + " gd.GroutingDate,\n" + " gd.DeliverTime,\n" + " nvl(gd.MaterialCode, nvl(Goods.MaterialCode, Goods.GoodsCode)) ||'#'|| to_char(Goods.GOODS_LINE_TYPE) as GoodsModel, -- 用物料编码验证产品型号是否一致\n" + " Goods.PlateLimitNum,\n" + " nvl(gd.outlabelcode,\n" + " Goods.MaterialCode ||\n" + " (select a.workcode\n" + " from tp_mst_account a\n" + " where a.accountid = gd.accountid) || l.tagcode || gd.onlycode) as FinishedBarCode,\n" + " o.ORDERNO,\n" + " SUBSTR( gd.OUTLABELCODE, 0, 14 ) OUTLABELCODE,\n" + " gd.MATERIALCODE,\n" + " gd.OUTLABELCODE as OUTLABELCODES,\n" + " f.BanMa\n" + " from TP_PM_GroutingDailyDetail gd\n" + " inner join tp_mst_logo l\n" + " on gd.logoid = l.logoid\n" + " inner join TP_PM_FinishedProduct f\n" + " on f.GROUTINGDAILYDETAILID = gd.GROUTINGDAILYDETAILID\n" + " left join tp_pm_order o\n" + " on o.orderid = f.FHORDERID\n" + " inner join TP_MST_Goods Goods\n" + " on gd.goodsid = Goods.goodsid \n"; if (string.IsNullOrWhiteSpace(FINISHEDLOADBATCHNO)) { sql += " where gd.BarCode='" + gbarcode + "'"; } else { sql += " where f.FINISHEDLOADBATCHNO ='" + FINISHEDLOADBATCHNO + "'"; paras[0].Value = FINISHEDLOADBATCHNO; } dsResult = oracleConn.GetSqlResultToDs(sql); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { for (int i = 0; i < dsResult.Tables[0].Rows.Count; i++) { if (dsResult.Tables[0].Rows[i]["OUTLABELCODE"].ToString() != dsResult.Tables[0].Rows[i]["MATERIALCODE"].ToString()) { resultEntity.Result = -1; resultEntity.Message = "物料编码与外包装箱码不匹配,不允许装板。外包装箱码：" + dsResult.Tables[0].Rows[i]["OUTLABELCODES"]; return resultEntity; } } //增加校验 普通项目类别组为NORM才需要卡控，251202 string mtposmarasql = "SELECT SSS.MTPOSMARA FROM TP_PC_SAPBOMDETAIL SSS WHERE SSS.MATNR = '" + dsResult.Tables[0].Rows[0]["MATERIALCODE"].ToString() + "'"; DataTable mtposmaradata = oracleConn.GetSqlResultToDt(mtposmarasql, null); if (mtposmaradata != null && mtposmaradata.Rows.Count > 0 && mtposmaradata.Rows[0]["MTPOSMARA"].ToString() == "NORM") { //增加SAP接口校验产成品交接卡控数量->SAP根据物料判断STO单剩余可产出数量，小于不允许保存 //根据系统参数判断 是否开启卡控 //add by qq 20250918 #region 卡控产成品交接数量 //S_PC_002:计划卡控默认选项(产成品交接) string sqs = "SELECT sss.settingcode, sss.settingvalue\n" + " FROM tp_mst_systemsetting sss\n" + " WHERE sss.accountid = " + sUserInfo.AccountID + "\n" + " AND sss.settingcode = 'S_PC_002'"; DataTable data = oracleConn.GetSqlResultToDt(sqs, null); if (data != null && data.Rows.Count > 0) { string settingvalue = data.Rows[0]["settingvalue"].ToString(); string materialCode = dsResult.Tables[0].Rows[0]["MATERIALCODE"].ToString(); string ztype = ""; string message = ""; //开启校验 if (settingvalue == "1") { //查询接口数量 decimal stonum = 0; // 配置文件 string postString = "{\"IN_WERKS\":\"" + "5000" + "\",\"IN_TABLE\":{\"item\":{\"MATNR\":\"" + materialCode + "\"}}}"; INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII); string url082 = ini.ReadIniData("SAP_NEW_INFO", "Url082"); //测试 //url082 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZMMFM082"; string result = string.Empty; //调用接口 try { result = SAPDataLogic.PostData(url082, postString, "POST"); } catch (Exception ex) { resultEntity.Result = -1; resultEntity.Message = "SAP-ZMMFM082接口调用失败，" + ex.Message; return resultEntity; } //判断物料是否一致 if (JObject.Parse(result)["OUT_TABLE"] != null && JObject.Parse(result)["OUT_TABLE"].ToString().Length > 0) { ztype = JObject.Parse(result)["ZTYPE"].ToString(); message = JObject.Parse(result)["ZMSG"].ToString(); Dictionary obj = JsonConvert.DeserializeObject>(result); object OUT_TABLE; obj.TryGetValue("OUT_TABLE", out OUT_TABLE); obj = JsonConvert.DeserializeObject>(OUT_TABLE + ""); object item1; obj.TryGetValue("item", out item1); JObject jsonObject = JObject.Parse(item1.ToString()); if (ztype == "S") { //物料编码 string matnr = jsonObject["MATNR"].ToString(); //未清STO总数 decimal zwjsl = Convert.ToDecimal(jsonObject["ZWJSL"].ToString()); //未清SO数量 decimal zwjso = Convert.ToDecimal(jsonObject["ZWJSO"].ToString()); stonum = zwjsl + zwjso; if (materialCode == matnr) { //查询本地未同步SAP数量 string sqlstr = @"SELECT SUM(OUTPUTNUM) OUTPUTNUM FROM tsap_hegii_workdata_bg WB LEFT JOIN tsap_hegii_datalog_bg D ON WB.LOGID = D.LOGID WHERE D.datacode = '60' And D.DATASTUTS = 'F' AND WB.SAPCODE = '" + materialCode + "' GROUP BY WB.SAPCODE"; DataTable numdata = oracleConn.GetSqlResultToDt(sqlstr, null); //未同步数量 decimal unscynnum = 0; if (numdata != null && numdata.Rows.Count > 0) { unscynnum = Convert.ToDecimal(numdata.Rows[0]["OUTPUTNUM"]); } //此次交接一板数量 decimal bannum = Convert.ToDecimal(dsResult.Tables[0].Rows.Count); decimal allnum = unscynnum + bannum; //判断是否允许交接 if (stonum < allnum) { resultEntity.Result = 1; resultEntity.Message = "该板及未同步交接产品物料【" + matnr + "】数量【" + allnum + "】超出SAP允许出单数量【" + stonum + "】, 不允许二次交接;"; return resultEntity; } } else { resultEntity.Result = -1; resultEntity.Message = "物料不一致；SAP返回物料【" + matnr + "】"; return resultEntity; } } else { resultEntity.Result = -1; resultEntity.Message = message; return resultEntity; } } } } #endregion } resultEntity.Result = 1;//成功 resultEntity.Data = dsResult; } else { resultEntity.Result = -2;// 条码未注浆 resultEntity.Message = "条码【" + barcode + "】未注浆"; return resultEntity; } #endregion return resultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 整版撤销产成品交接条码验证 /// /// /// /// public static ServiceResultEntity BackFinishedHandoverCheck(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity resultEntity = new ServiceResultEntity(); if (barcode.Length == 8) { resultEntity.Result = -1; resultEntity.Message = "撤销交接不允许扫板码。"; return resultEntity; } string gbarcode = null; string FINISHEDLOADBATCHNO = null; string sqlString = @"select FUN_CMN_GetBarCode(:barcode,null,:accountid) From DUAL"; OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras1); if (ds != null && ds.Tables[0].Rows.Count > 0) { gbarcode = ds.Tables[0].Rows[0][0].ToString(); } // 1.判断产品是否在产成品表中 string sql = @"select FHUserID, FHUserCode, GoodsID, GoodsCode, GoodsName, FINISHEDLOADBATCHNO from TP_PM_FinishedProduct where BarCode=:BarCode"; //where BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid)"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2,gbarcode,ParameterDirection.Input), //new OracleParameter(":Accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), }; DataSet dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { FINISHEDLOADBATCHNO = dsResult.Tables[0].Rows[0]["FINISHEDLOADBATCHNO"].ToString(); // 在产成品表中 #region 是否交接过 string fhUserCode = dsResult.Tables[0].Rows[0]["FHUserCode"].ToString(); if (string.IsNullOrEmpty(fhUserCode)) { resultEntity.Result = -1; //未交接，不能撤销 resultEntity.Message = "此产品【" + barcode + "】未交接，不能进行该操作"; return resultEntity; } #endregion } else { resultEntity.Result = -2; //不能产成品，不能撤销 //sql = "select GoodsCode from TP_PM_GroutingDailyDetail where BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid)"; sql = "select GoodsCode from TP_PM_GroutingDailyDetail where BarCode=:BarCode"; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Message = "此产品【" + barcode + "】不是产成品，不能进行该操作"; } else { resultEntity.Message = "此条码【" + barcode + "】无效，不能进行该操作"; } return resultEntity; } #region 查询产品相关信息 sql = "select gd.BarCode,\n" + " l.logoid,\n" + " l.logocode,\n" + " l.logoname,\n" + " gd.goodscode,\n" + " gd.UserID,\n" + " gd.UserCode as GroutingUserCode,\n" + " gd.GroutingMouldCode as MouldCode,\n" + " gd.GroutingDate,\n" + " gd.DeliverTime,\n" + //" --Goods.GoodsCode as GoodsModel,\n" + //" --nvl(Goods.MaterialCode,Goods.GoodsCode) as GoodsModel, -- 用物料编码验证产品型号是否一致\n" + " nvl(gd.MaterialCode, nvl(Goods.MaterialCode, Goods.GoodsCode)) ||'#'|| to_char(Goods.GOODS_LINE_TYPE) as GoodsModel, -- 用物料编码验证产品型号是否一致\n" + " Goods.PlateLimitNum,\n" + //" --(Goods.MaterialCode || l.tagcode || gd.onlycode) as FinishedBarCode ,\n" + " nvl(gd.outlabelcode,\n" + " Goods.MaterialCode ||\n" + " (select a.workcode\n" + " from tp_mst_account a\n" + " where a.accountid = gd.accountid) || l.tagcode || gd.onlycode) as FinishedBarCode,\n" + " o.ORDERNO,\n" + " f.BanMa,\n" + " f.TemporarilyFlag\n" + " from TP_PM_GroutingDailyDetail gd\n" + " inner join tp_mst_logo l\n" + " on gd.logoid = l.logoid\n" + " inner join TP_PM_FinishedProduct f\n" + " on f.GROUTINGDAILYDETAILID = gd.GROUTINGDAILYDETAILID\n" + " left join tp_pm_order o\n" + " on o.orderid = f.FHORDERID\n" + " inner join TP_MST_Goods Goods\n" + " on gd.goodsid = Goods.goodsid \n"; if (string.IsNullOrWhiteSpace(FINISHEDLOADBATCHNO)) { //sql += " where gd.BarCode=:BarCode"; sql += " where gd.BarCode='" + gbarcode + "'"; } else { //sql += " where gd.GROUTINGDAILYDETAILID in ( select GROUTINGDAILYDETAILID from TP_PM_FinishedProduct f where f.FINISHEDLOADBATCHNO = :BarCode)"; //sql += " where f.FINISHEDLOADBATCHNO = :BarCode "; sql += " where f.FINISHEDLOADBATCHNO ='" + FINISHEDLOADBATCHNO + "'"; paras[0].Value = FINISHEDLOADBATCHNO; } //where gd.BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid)"; // 不用参数比用参数效率高 ？？？ edit by chenxy //dsResult = oracleConn.GetSqlResultToDs(sql, paras); dsResult = oracleConn.GetSqlResultToDs(sql); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Result = 1;//成功 //resultEntity.Data = new DataSet(); //resultEntity.Data.Tables.Add(dsResult.Tables[0].Copy()); resultEntity.Data = dsResult; } else { resultEntity.Result = -2;// 条码未注浆 resultEntity.Message = "条码【" + barcode + "】未注浆"; return resultEntity; } #endregion return resultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion /// /// 根据条码及工序判断是否漏扫 /// /// 产品条码 /// 校验工序ID /// 用户基本信息 /// DataSet public static int AddBarCodeMissingFinishedHandover(IDBConnection oracleTrConn, string barcode, SUserInfo sUserInfo) { try { #region 查询产成品 string sqlString = @"select TP_PM_InProduction.FlowProcedureID,NVL(TP_PM_InProduction.ReworkProcedureID,0) as ReworkProcedureID, TP_PM_InProduction.GoodsID,TP_PM_InProduction.GoodsCode,TP_PM_InProduction.GoodsName, TP_PC_Procedure.ProcedureCode,TP_PC_Procedure.ProcedureName,TP_MST_Goods.GoodsTypeID,TP_PM_InProduction.isrefire from TP_PM_InProduction left join TP_PC_Procedure on TP_PM_InProduction.FlowProcedureID=TP_PC_Procedure.ProcedureID left join TP_MST_Goods on TP_PM_InProduction.GoodsID=TP_MST_Goods.GoodsID where TP_PM_InProduction.BarCode =:barCode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); #endregion int AddBarCodeMissingID = 0;//新增漏扫表的ID if (ds != null && ds.Tables[0].Rows.Count > 0) //只有是在产的，才可以去进行漏扫 { #region 获取此条码为在产产品 int CompleteProcedureID = Convert.ToInt32(ds.Tables[0].Rows[0]["FlowProcedureID"]);//WMSYS.WM_CONCAT(to_char(pro.ProcedureName)),max(line.ProductionLineName) string CompleteProcedureCode = ds.Tables[0].Rows[0]["ProcedureCode"].ToString(); string CompleteProcedureName = ds.Tables[0].Rows[0]["ProcedureName"].ToString(); int GoodsID = Convert.ToInt32(ds.Tables[0].Rows[0]["GoodsID"]); string GoodsCode = ds.Tables[0].Rows[0]["GoodsCode"].ToString(); string GoodsName = ds.Tables[0].Rows[0]["GoodsName"].ToString(); int GoodsTypeID = Convert.ToInt32(ds.Tables[0].Rows[0]["GoodsTypeID"]); int ReworkProcedureID = Convert.ToInt32(ds.Tables[0].Rows[0]["ReworkProcedureID"]);//返工工序 int isrefire = Convert.ToInt32(ds.Tables[0].Rows[0]["isrefire"]);//重烧标记 #endregion // 漏扫工序与检验工序不在一条路径上 modify by chenxy 2016-08-08 begin // 漏扫序号大的可到达工序与检验工序不在一条路径上时，要验证下一个可到达工序。 // 即与检验工序在一条路径上的漏扫序号最大可到达工序为漏扫工序。 #region 获取漏扫工序信息 if (ReworkProcedureID == 0) { sqlString = @" select TP_PC_Procedure.ProcedureID,TP_PC_Procedure.ProcedureCode,TP_PC_Procedure.ProcedureName,TP_PC_Procedure.NodeType from TP_PC_ProcedureGoods left join TP_PC_Procedure on TP_PC_ProcedureGoods.ProcedureID=TP_PC_Procedure.ProcedureID where TP_PC_ProcedureGoods.goodsid=( select Goodsid from TP_PM_GroutingDailyDetail where BarCode=:barCode) and TP_PC_ProcedureGoods.ProcedureID in( select pro.ProcedureID from TP_PC_ProcedureFlow flow inner join TP_PC_Procedure pro on flow.arriveprocedureid = pro.procedureid inner join TP_PC_ProductionLine line on pro.ProductionLineID = line.ProductionLineID where flow.ProcedureID =:procedureID and flow.FlowFlag = 2 and pro.valueflag = '1' -- 非必须工序，不记漏扫 and pro.MUSTFLAG = '1' ) " + //" and exists ( " + // "select 1 from " + // "(" + // "select procedureid, arriveprocedureid,ltrim(sys_connect_by_path(procedureid,'->')||'->'||arriveprocedureid) sybp from " + // "( " + // " select * from TP_PC_PROCEDUREFLOW where arriveprocedureid<>:missProcedureid and flowflag=2 " + // ") " + // //" start with procedureid=:missProcedureid" + // " start with procedureid=TP_PC_ProcedureGoods.ProcedureID" + // " connect by nocycle procedureid=prior arriveprocedureid" + // ") where arriveprocedureid=:judgeProcedureid " + // " ) " + //" order by ProcedureID"; " order by MissPriority DESC, displayno --ProcedureCode DESC"; paras = new OracleParameter[]{ new OracleParameter(":procedureID",OracleDbType.Int32,CompleteProcedureID,ParameterDirection.Input), new OracleParameter(":barCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), //new OracleParameter(":missProcedureid",OracleDbType.Int32,missprocedureid,ParameterDirection.Input), //new OracleParameter(":judgeProcedureid",OracleDbType.Int32,produceid,ParameterDirection.Input), }; } else { sqlString = "select TP_PC_Procedure.ProcedureID,TP_PC_Procedure.ProcedureCode,TP_PC_Procedure.ProcedureName,TP_PC_Procedure.NodeType from TP_PC_Procedure where ProcedureID=:procedureID"; paras = new OracleParameter[]{ new OracleParameter(":procedureID",OracleDbType.Int32,ReworkProcedureID,ParameterDirection.Input), }; } ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) { return AddBarCodeMissingID; } int missprocedureid = Convert.ToInt32(ds.Tables[0].Rows[0]["ProcedureID"]); string missprocedurecode = ds.Tables[0].Rows[0]["ProcedureCode"].ToString(); string missprocedurename = ds.Tables[0].Rows[0]["ProcedureName"].ToString(); //int missprocedureid = -1; //string missprocedurecode = ""; //string missprocedurename = ""; #endregion //#region 增加判断校验工序必须是在漏扫工序的后面工序 //foreach (DataRow item in ds.Tables[0].Rows) //{ // sqlString = "select * from " + // "(" + // "select procedureid, arriveprocedureid,ltrim(sys_connect_by_path(procedureid,'->')||'->'||arriveprocedureid) sybp from " + // "( " + // " select * from TP_PC_PROCEDUREFLOW where arriveprocedureid<>:missProcedureid and flowflag=2 " + // ")" + // " start with procedureid=:missProcedureid" + // " connect by nocycle procedureid=prior arriveprocedureid" + // ") where arriveprocedureid=:judgeProcedureid "; // paras = new OracleParameter[]{ // new OracleParameter(":missProcedureid",OracleDbType.Int32,Convert.ToInt32(item["ProcedureID"]),ParameterDirection.Input), // new OracleParameter(":judgeProcedureid",OracleDbType.Int32,produceid,ParameterDirection.Input), // }; // ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); // if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) // { // continue; // } // missprocedureid = Convert.ToInt32(item["ProcedureID"]); // missprocedurecode = item["ProcedureCode"].ToString(); // missprocedurename = item["ProcedureName"].ToString(); // break; //} //if (missprocedureid == -1) //{ // return AddBarCodeMissingID; //} //#endregion // 漏扫工序与检验工序不在一条路径上 modify by chenxy 2016-08-08 end //sqlString = "select 1 from TP_PM_ProductionDataIn where barcode=:barcode and ProcedureID=:ProcedureID and ValueFlag=1"; //paras = new OracleParameter[]{ // new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), // new OracleParameter(":ProcedureID",OracleDbType.Int32, produceid,ParameterDirection.Input), // }; //ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); //// if (produceid > missprocedureid) //if (ds != null && ds.Tables[0].Rows.Count == 0) // 未走过该工序 //{ #region 插入漏扫表,如果漏扫表里有相应的条码，不允许重复插入 sqlString = @"select 1 from TP_PM_BarCodeMissing where barcode=:barcode and MissProcedureID=:MissProcedureID and ProcedureID is null"; paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":MissProcedureID",OracleDbType.Int32, missprocedureid,ParameterDirection.Input), //new OracleParameter(":ProcedureID",OracleDbType.Int32, produceid,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count == 0) // 没有此条码的工序，即插入 { int OrganizationID = 0; string OrganizationName = ""; string OrganizationCode = ""; string OrganizationFullName = ""; sqlString = @"select TP_MST_Organization.OrganizationID ,TP_MST_Organization.OrganizationName ,TP_MST_Organization.OrganizationCode ,TP_MST_Organization.OrganizationFullName from TP_PC_Procedure left join TP_MST_Organization on TP_MST_Organization.OrganizationID=TP_PC_Procedure.OrganizationID where TP_PC_Procedure.ProcedureID=:ProcedureID"; paras = new OracleParameter[]{ new OracleParameter(":ProcedureID",OracleDbType.Int32, missprocedureid,ParameterDirection.Input), }; ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); OrganizationID = Convert.ToInt32(ds.Tables[0].Rows[0]["OrganizationID"]); //组织机构ID OrganizationName = ds.Tables[0].Rows[0]["OrganizationName"].ToString(); //组织机构名称 OrganizationCode = ds.Tables[0].Rows[0]["OrganizationCode"].ToString(); //组织机构编码 OrganizationFullName = ds.Tables[0].Rows[0]["OrganizationFullName"].ToString(); //组织机构全称 // // 获取校验工序信息 // sqlString = @"select TP_PC_Procedure.ProcedureID // ,TP_PC_Procedure.ProcedureCode // ,TP_PC_Procedure.ProcedureName // from TP_PC_Procedure // where TP_PC_Procedure.ProcedureID=:ProcedureID"; // paras = new OracleParameter[]{ // new OracleParameter(":ProcedureID",OracleDbType.Int32, produceid,ParameterDirection.Input), // }; // ds = oracleTrConn.GetSqlResultToDs(sqlString, paras); string ProcedureCode = ""; string ProcedureName = ""; //if (ds != null && ds.Tables[0].Rows.Count > 0) // 没有此条码的工序，即插入 //{ // ProcedureCode = ds.Tables[0].Rows[0]["ProcedureCode"].ToString(); // ProcedureName = ds.Tables[0].Rows[0]["ProcedureName"].ToString(); //} //漏扫表ID string sql = "select SEQ_PM_BarCodeMissing_MissID.nextval from dual"; AddBarCodeMissingID = Convert.ToInt32(oracleTrConn.GetSqlResultToStr(sql)); sqlString = @"insert into TP_PM_BarCodeMissing(MissID, BarCode, OrganizationID, OrganizationName, OrganizationCode, OrganizationFullName, CompleteProcedureID, CompleteProcedureCode, CompleteProcedureName, ProcedureID, ProcedureCode, ProcedureName, MissProcedureID, MissProcedureCode, MissProcedureName, GoodsID, GoodsCode, GoodsName, GoodsTypeID, CreateUserID, UpdateUserID, AccountID ) values ( :MissID, :BarCode, :OrganizationID, :OrganizationName, :OrganizationCode, :OrganizationFullName, :CompleteProcedureID, :CompleteProcedureCode, :CompleteProcedureName, :ProcedureID, :ProcedureCode, :ProcedureName, :MissProcedureID, :MissProcedureCode, :MissProcedureName, :GoodsID, :GoodsCode, :GoodsName, :GoodsTypeID, :CreateUserID, :UpdateUserID, :AccountID ) "; paras = new OracleParameter[]{ new OracleParameter(":MissID",OracleDbType.Int32, AddBarCodeMissingID,ParameterDirection.Input), new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":OrganizationID",OracleDbType.Int32, OrganizationID,ParameterDirection.Input), new OracleParameter(":OrganizationName",OracleDbType.Varchar2, OrganizationName,ParameterDirection.Input), new OracleParameter(":OrganizationCode",OracleDbType.Varchar2, OrganizationCode,ParameterDirection.Input), new OracleParameter(":OrganizationFullName",OracleDbType.Varchar2, OrganizationFullName,ParameterDirection.Input), new OracleParameter(":CompleteProcedureID",OracleDbType.Int32, CompleteProcedureID,ParameterDirection.Input), new OracleParameter(":CompleteProcedureCode",OracleDbType.Varchar2, CompleteProcedureCode,ParameterDirection.Input), new OracleParameter(":CompleteProcedureName",OracleDbType.Varchar2, CompleteProcedureName,ParameterDirection.Input), new OracleParameter(":ProcedureID",OracleDbType.Int32, null,ParameterDirection.Input), new OracleParameter(":ProcedureCode",OracleDbType.Varchar2, ProcedureCode,ParameterDirection.Input), new OracleParameter(":ProcedureName",OracleDbType.Varchar2, ProcedureName,ParameterDirection.Input), new OracleParameter(":MissProcedureID",OracleDbType.Int32, missprocedureid,ParameterDirection.Input), new OracleParameter(":MissProcedureCode",OracleDbType.Varchar2,missprocedurecode ,ParameterDirection.Input), new OracleParameter(":MissProcedureName",OracleDbType.Varchar2, missprocedurename,ParameterDirection.Input), new OracleParameter(":GoodsID",OracleDbType.Int32, GoodsID,ParameterDirection.Input), new OracleParameter(":GoodsCode",OracleDbType.Varchar2,GoodsCode ,ParameterDirection.Input), new OracleParameter(":GoodsName",OracleDbType.Varchar2, GoodsName,ParameterDirection.Input), new OracleParameter(":GoodsTypeID",OracleDbType.Int32, GoodsTypeID,ParameterDirection.Input), new OracleParameter(":CreateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":UpdateUserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; oracleTrConn.GetSqlResultToStr(sqlString, paras); } #endregion //} } return AddBarCodeMissingID; } catch (Exception ex) { throw ex; } } /// /// 撤销产成品交接条码验证 /// /// /// /// public static ServiceResultEntity CancelFinishedHandoverBarcode(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity resultEntity = new ServiceResultEntity(); string gbarcode = null; string sqlString = @"select FUN_CMN_GetBarCode(:barcode,null,:accountid) From DUAL"; OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras1); if (ds != null && ds.Tables[0].Rows.Count > 0) { gbarcode = ds.Tables[0].Rows[0][0].ToString(); } // 1.判断产品是否在产成品表中 string sql = @"select FHUserID, FHUserCode, GoodsID, GoodsCode, GoodsName from TP_PM_FinishedProduct where BarCode=:BarCode"; //where BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid)"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2,gbarcode,ParameterDirection.Input), //new OracleParameter(":Accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), }; DataSet dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { // 在产成品表中 #region 是否交接过 string fhUserCode = dsResult.Tables[0].Rows[0]["FHUserCode"].ToString(); if (string.IsNullOrEmpty(fhUserCode)) { resultEntity.Result = -1; //未交接，不能撤销 resultEntity.Message = "此产品【" + barcode + "】未交接，不能进行该操作"; return resultEntity; } #endregion } else { resultEntity.Result = -2; //不能产成品，不能撤销 //sql = "select GoodsCode from TP_PM_GroutingDailyDetail where BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid)"; sql = "select GoodsCode from TP_PM_GroutingDailyDetail where BarCode=:BarCode"; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Message = "此产品【" + barcode + "】不是产成品，不能进行该操作"; } else { resultEntity.Message = "此条码【" + barcode + "】无效，不能进行该操作"; } return resultEntity; } #region 查询产品相关信息 sql = @"select O.OrderNo, F.FHOrderID, F.FHTime, F.FHUserCode, F.BarCode, F.GoodsCode, L.LogoName, F.GroutingUserCode, F.GroutingMouldCode, F.GroutingDate, G.DeliverTime, L.LogoID, --(Goods.MaterialCode || L.tagcode || G.onlycode) as FinishedBarCode nvl(g.outlabelcode, Goods.MaterialCode || (select a.workcode from tp_mst_account a where a.accountid = g.accountid) || L.tagcode || G.onlycode) as FinishedBarCode from TP_PM_FinishedProduct F left join TP_PM_Order O on F.FHOrderID=O.OrderID left join TP_MST_Logo L on F.LogoID=L.LogoID left join TP_PM_GroutingDailyDetail G on F.GroutingDailyDetailID=G.GroutingDailyDetailID left join TP_MST_Goods Goods on F.GoodsID=Goods.GoodsID left join TP_MST_GoodsType GoodsType on Goods.GoodsTypeID=GoodsType.GoodsTypeID where F.BarCode=:BarCode"; //where F.BarCode=FUN_CMN_GetBarCode(:BarCode,null,:Accountid)"; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Result = 1;//成功 resultEntity.Data = new DataSet(); resultEntity.Data.Tables.Add(dsResult.Tables[0].Copy()); } else { resultEntity.Result = -2;// 条码未注浆 resultEntity.Message = "条码【" + barcode + "】未注浆"; return resultEntity; } #endregion return resultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 检验回收条码 /// /// 工序ID /// 产品条码 /// DataSet public static DataSet CheckRecydingFlagBarcode(int procedureID, string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); string sqlString = @"select settingcode,settingvalue,settingdefaultvalues from tp_mst_systemsetting where Accountid=:Accountid and settingcode in ('S_PM_017','S_PM_018','S_PM_019','S_PM_020')"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("Accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), }; DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras); #region 读出系统配置 17(损坯可否回收) 18(半检不合格可否回收) 19(复检不合格可否回收) 20(次品可否回收) int S_PM_017_Value = 0, S_PM_018_Value = 0, S_PM_019_Value = 0, S_PM_020_Value = 0; if (ds != null && ds.Tables[0].Rows.Count > 0) { foreach (DataRow r in ds.Tables[0].Rows) { if (r["settingcode"].ToString() == "S_PM_017") { S_PM_017_Value = Convert.ToInt32(r["settingvalue"]); } else if (r["settingcode"].ToString() == "S_PM_018") { S_PM_018_Value = Convert.ToInt32(r["settingvalue"]); } else if (r["settingcode"].ToString() == "S_PM_019") { S_PM_019_Value = Convert.ToInt32(r["settingvalue"]); } else if (r["settingcode"].ToString() == "S_PM_020") { S_PM_020_Value = Convert.ToInt32(r["settingvalue"]); } } } #endregion int goodsID = 0; string goodsCode = "", goodsName = "", groutingUserCode = ""; DataTable dtBarCode = PMModuleLogic.CreateBarCodeResultTable(); DataSet returnDs = new DataSet(); DataRow dr = dtBarCode.NewRow(); bool isError = false; #region 第1步 查当前工序在系统是否存在 sqlString = @"select NodeType,ModelType from tp_pc_procedure where procedureid=:procedureid and ValueFlag=1"; paras = new OracleParameter[]{ new OracleParameter(":procedureid",procedureID), }; ds = oracleConn.GetSqlResultToDs(sqlString, paras); if (ds == null || ds.Tables[0].Rows.Count == 0) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "当前工序在系统中不存在"; isError = true; } #endregion #region 第2步 查产品是否有效 if (!isError) { sqlString = @"select distinct GoodsID, GoodsCode,GoodsName,UserCode,DeliverFlag from TP_PM_GroutingDailyDetail where BarCode=:barCode and ValueFlag=1 "; paras = new OracleParameter[]{ new OracleParameter(":barCode",barcode), }; ds = oracleConn.GetSqlResultToDs(sqlString, paras); if (ds == null || ds.Tables[0].Rows.Count == 0) { // 是否被替换 string sql = @"select count(barcode),max(newbarcode) from TP_PM_BarCodeRecord where BarCode=:barcode"; OracleParameter[] paras2 = new OracleParameter[]{ new OracleParameter(":barCode",barcode) }; DataSet ds2 = oracleConn.GetSqlResultToDs(sql, paras2); if (ds2 != null && Convert.ToInt32(ds2.Tables[0].Rows[0][0]) == 0) { // 无效条件 dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "无效条码[" + barcode + "]"; isError = true; } else { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "条码[" + barcode + "]已经被[" + ds2.Tables[0].Rows[0][1] + "]替换"; isError = true; } } else { if (Convert.ToInt32(ds.Tables[0].Rows[0]["DeliverFlag"]) != 1) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "条码[" + barcode + "]未交坯，不能回收"; isError = true; } else { goodsID = Convert.ToInt32(ds.Tables[0].Rows[0]["GoodsID"]); goodsName = ds.Tables[0].Rows[0]["goodsName"].ToString(); goodsCode = ds.Tables[0].Rows[0]["GoodsCode"].ToString(); groutingUserCode = ds.Tables[0].Rows[0]["UserCode"].ToString(); } sqlString = @" SELECT GOODSCODE FROM TP_MST_GOODS WHERE GOODSID IN (SELECT GOODSID FROM TP_PM_GROUTINGDAILYDETAIL WHERE BARCODE=:BARCODE) AND CEASEFLAG = 0 "; paras = new OracleParameter[]{ new OracleParameter(":BARCODE",barcode), }; ds = oracleConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0 && !string.IsNullOrWhiteSpace(ds.Tables[0].Rows[0]["GOODSCODE"].ToString())) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "产品[" + ds.Tables[0].Rows[0]["GOODSCODE"].ToString() + "]已停产，不允许操作，请联系管理员！"; isError = true; } } } #endregion #region 第3步 查产品是否在当前工序配置 if (!isError) { sqlString = @"select count(GoodsID) from TP_PC_ProcedureGoods where GoodsID=:GoodsID and ProcedureID=:ProcedureID"; paras = new OracleParameter[]{ new OracleParameter(":GoodsID",goodsID), new OracleParameter(":ProcedureID",procedureID), }; ds = oracleConn.GetSqlResultToDs(sqlString, paras); if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) != 1) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = @"条码[" + barcode + "]不可以经过该工序\n\r原因：条码对应的产品编码[" + goodsCode + "]没有在该工序中配置"; isError = true; } } #endregion if (!isError) { #region 第4步,校验在产产品不能回收 string sqlString2 = "select InScrapFlag,ISREWORKFLAG from tp_pm_inproduction where barcode=:barcode"; OracleParameter[] parasNew = new OracleParameter[]{ new OracleParameter(":barcode",barcode) }; DataSet dsNew = oracleConn.GetSqlResultToDs(sqlString2, parasNew); if (dsNew != null && dsNew.Tables[0].Rows.Count > 0) { if (Convert.ToInt32(dsNew.Tables[0].Rows[0]["InScrapFlag"]) == 1) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "报损待审产品不能回收"; } // chenxy 2019-10-02 半检返修 IsReworkFlag=2 else if (Convert.ToInt32(dsNew.Tables[0].Rows[0]["ISREWORKFLAG"]) == 1) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "半检返工中不能回收"; } else if (Convert.ToInt32(dsNew.Tables[0].Rows[0]["ISREWORKFLAG"]) == 2) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "半检返修中不能回收"; } else { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "在产产品不能回收"; } isError = true; } #endregion #region 第5步,校验报损表数据是否可以回收 if (!isError) { sqlString = @"select GoodsLevelID,GoodsLevelTypeID,ScrapType,SpecialRepairFlag,RecyclingFlag,AuditStatus,SpecialRepairUserID from TP_PM_ScrapProduct where barcode=:barcode and ValueFlag=1 and CreateTime= (select max(CreateTime) from TP_PM_ScrapProduct where barcode=:barcode and ValueFlag=1)"; paras = new OracleParameter[]{ new OracleParameter(":barcode",barcode), }; ds = oracleConn.GetSqlResultToDs(sqlString, paras); if (ds == null || ds.Tables[0].Rows.Count == 0) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "不存在回收数据，不能回收"; isError = true; } else { if (Convert.ToInt32(ds.Tables[0].Rows[0]["AuditStatus"]) == 1 && Convert.ToInt32(ds.Tables[0].Rows[0]["GoodsLevelTypeID"]) != 9 && Convert.ToInt32(ds.Tables[0].Rows[0]["RecyclingFlag"]) == 0) { // 可回收 string sqlString3 = "select 1 from tp_pm_inproductiontrash where barcode=:barcode"; OracleParameter[] parasNewt = new OracleParameter[]{ new OracleParameter(":barcode",barcode) }; DataSet dsNewt = oracleConn.GetSqlResultToDs(sqlString3, parasNewt); if (dsNewt == null || dsNewt.Tables[0].Rows.Count == 0) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "报损数据被清除，不能回收"; isError = true; } } else { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "不存在回收数据，不能回收"; isError = true; } if (!isError) { if (Convert.ToInt32(ds.Tables[0].Rows[0]["ScrapType"]) == 0) { // 损坯 if (S_PM_017_Value == 0) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "系统参数产品回收-报损未启用"; isError = true; } } else if (Convert.ToInt32(ds.Tables[0].Rows[0]["ScrapType"]) == 1) { // 成检 if (S_PM_020_Value == 0) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "系统参数产品回收-次品未启用"; isError = true; } } else if (Convert.ToInt32(ds.Tables[0].Rows[0]["ScrapType"]) == 2) { // 半检 if (S_PM_018_Value == 0) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "系统参数产品回收-半检不合格未启用"; isError = true; } } else if (Convert.ToInt32(ds.Tables[0].Rows[0]["ScrapType"]) == 3) { // 复检 if (S_PM_019_Value == 0) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = "系统参数产品回收-复检不合格未启用"; isError = true; } } } } } #endregion #region 第6步,回收过一次不能再回收 sqlString = @"SELECT COUNT(*) count FROM TP_PM_PRODUCTIONDATA WHERE PROCEDUREID = 80 AND BARCODE = :barCode AND VALUEFLAG = 1 GROUP BY BARCODE"; OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":barCode",barcode), }; DataSet ds1 = oracleConn.GetSqlResultToDs(sqlString, paras1); string errorMessage1 = ""; if (ds1 != null && ds1.Tables[0].Rows.Count > 0) { if (Convert.ToInt16(ds1.Tables[0].Rows[0]["count"]) >= 2) { // 无效条件 errorMessage1 = "条码[" + barcode + "]:已回收过两次，不允许再次回收"; } } if (!string.IsNullOrEmpty(errorMessage1)) { dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] = errorMessage1; isError = true; } #endregion } dr[Constant.BarCodeResultTableColumns.out_goodsID.ToString()] = goodsID; dr[Constant.BarCodeResultTableColumns.out_goodsCode.ToString()] = goodsCode; dr[Constant.BarCodeResultTableColumns.out_goodsName.ToString()] = goodsName; dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = groutingUserCode; //oracleConn.ExecStoredProcedure("pro_pm_checkdryrepairbarcode", paras); //dr[Constant.BarCodeResultTableColumns.out_errMsg.ToString()] // = paras[2].Value.ToString() == "null" ? "" : paras[2].Value.ToString().Replace("\\n\\r", "\n\r"); //dr[Constant.BarCodeResultTableColumns.out_goodsID.ToString()] = paras[3].Value.ToString() == "null" ? 0 : paras[3].Value; //dr[Constant.BarCodeResultTableColumns.out_goodsCode.ToString()] = paras[4].Value.ToString() == "null" ? "" : paras[4].Value; //dr[Constant.BarCodeResultTableColumns.out_goodsName.ToString()] = paras[5].Value.ToString() == "null" ? "" : paras[5].Value; //dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = paras[6].Value.ToString() == "null" ? 0 : paras[6].Value; //if (paras[2].Value.ToString() == "null") //只有正确的条码，读注浆信息 if (!isError) //只有正确的条码，读注浆信息 { sqlString = @"select TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode, tp_pm_groutingdailydetail.groutingcount as GroutingNum, tp_pm_groutingdailydetail.GroutingMouldCode as MouldCode, tp_pm_groutingdailydetail.ispublicbody, tp_pm_groutingdailydetail.Groutingdate, tp_pm_groutingdailydetail.SpecialRepairFlag, tp_mst_logo.logoid, tp_mst_logo.logocode, tp_mst_logo.logoname from tp_pm_groutingdailydetail left join tp_mst_logo on tp_pm_groutingdailydetail.logoid=tp_mst_logo.logoid where tp_pm_groutingdailydetail.BarCode=:barcode"; paras = new OracleParameter[]{ new OracleParameter(":barcode",barcode), }; ds = oracleConn.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { dr[Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()] = ds.Tables[0].Rows[0]["GroutingUserCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_groutingNum.ToString()] = ds.Tables[0].Rows[0]["GroutingNum"].ToString(); dr[Constant.BarCodeResultTableColumns.out_mouldCode.ToString()] = ds.Tables[0].Rows[0]["MouldCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbody.ToString()] = ds.Tables[0].Rows[0]["ispublicbody"].ToString(); dr[Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_groutingdate.ToString()] = ds.Tables[0].Rows[0]["Groutingdate"].ToString(); dr[Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString()] = ds.Tables[0].Rows[0]["SpecialRepairFlag"].ToString(); dr[Constant.BarCodeResultTableColumns.out_isReFire.ToString()] = "0"; dr[Constant.BarCodeResultTableColumns.out_logoID.ToString()] = ds.Tables[0].Rows[0]["logoID"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoCode.ToString()] = ds.Tables[0].Rows[0]["logoCode"].ToString(); dr[Constant.BarCodeResultTableColumns.out_logoName.ToString()] = ds.Tables[0].Rows[0]["logoName"].ToString(); } } dtBarCode.Rows.Add(dr); returnDs.Tables.Add(dtBarCode); return returnDs; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #region 裸瓷交接验证 /// /// 产成品交接条码验证 /// /// /// /// 0:未交接，1:已交接，2:不限 /// public static ServiceResultEntity LCFinishedHandoverBarcode(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity resultEntity = new ServiceResultEntity(); // 如果是板码 string banMa = string.Empty; if (barcode.Length == 8) { banMa = barcode; string sqlBanMa = @" SELECT BARCODE, FINISHEDLOADBATCHNO FROM TP_PM_FINISHEDPRODUCT WHERE FHTIME IS NULL AND LCFHTIME IS NULL AND BANMA = :barcode "; OracleParameter[] parasBanMa = new OracleParameter[] { new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input) }; DataTable dtBanMa = oracleConn.GetSqlResultToDt(sqlBanMa, parasBanMa); if (dtBanMa.Rows.Count == 0) { resultEntity.Result = -1; resultEntity.Message = "当前载具上无可交接的产品。"; return resultEntity; } DataTable dtFinishedLoadBatchNo = dtBanMa.DefaultView.ToTable(true, new string[] { "FINISHEDLOADBATCHNO" }); if (dtFinishedLoadBatchNo.Rows.Count > 1) { resultEntity.Result = -1; resultEntity.Message = "当前载具存在超过两版以上的产品。"; return resultEntity; } barcode = dtBanMa.Rows[0]["BARCODE"].ToString(); } string gbarcode = null; string FINISHEDLOADBATCHNO = null; string sqlString = @"select FUN_CMN_GetBarCode(:barcode,null,:accountid) From DUAL"; OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras1); if (ds != null && ds.Tables[0].Rows.Count > 0) { gbarcode = ds.Tables[0].Rows[0][0].ToString(); } // 1.判断产品是否在产成品表中 string sql = @"select f.FHUserID, f.FHUserCode, f.LCFHUserID, f.LCFHUserCode, f.GoodsID, f.GoodsCode, f.GoodsName, f.BarCode, f.FINISHEDLOADBATCHNO from TP_PM_FinishedProduct f where f.BarCode=:BarCode"; string currentBarcode = string.Empty; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2,gbarcode,ParameterDirection.Input), }; DataSet dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { FINISHEDLOADBATCHNO = dsResult.Tables[0].Rows[0]["FINISHEDLOADBATCHNO"].ToString(); // 在产成品表中 #region 是否交接过 string fhUserCode = dsResult.Tables[0].Rows[0]["FHUserCode"].ToString(); string lcfhUserCode = dsResult.Tables[0].Rows[0]["LCFHUserCode"].ToString(); if ( !string.IsNullOrEmpty(lcfhUserCode)) { resultEntity.Result = -1; //已交接，不能再次进行交接 resultEntity.Message = "此产品【" + barcode + "】已进行裸瓷交接，不能进行该操作"; return resultEntity; } if ( !string.IsNullOrEmpty(fhUserCode)) { resultEntity.Result = -1; //已交接，不能再次进行交接 resultEntity.Message = "此产品【" + barcode + "】已包装交接，不能进行该操作"; return resultEntity; } #endregion #region 是否是裸瓷交接 //查询最近一次是不是裸瓷保存 sql = "SELECT procedureid\n" + " FROM (SELECT procedureid\n" + " FROM tp_pm_productiondata\n" + " WHERE barcode = :BarCode\n" + " ORDER BY productiondataid DESC)\n" + " WHERE rownum = 1"; paras = new OracleParameter[]{ new OracleParameter(":Barcode",OracleDbType.Varchar2, gbarcode,ParameterDirection.Input), }; DataTable productionDataIDdt = oracleConn.GetSqlResultToDt(sql, paras); if (productionDataIDdt != null && productionDataIDdt.Rows.Count > 0 && productionDataIDdt.Rows[0]["procedureid"].ToString() != "159") { resultEntity.Result = -1; //已交接，不能再次进行交接 resultEntity.Message = "此产品【" + barcode + "】不能进行该操作，请使用产成品交接"; return resultEntity; } #endregion } else { // 不在产成品表中 #region 1 查询是否报损待审批与漏扫 sql = @"select I.InScrapFlag,P.procedureName,I.GoodsCode from TP_PM_InProduction I left join tp_pc_procedure P on I.flowprocedureid=P.procedureid where I.BarCode=:BarCode"; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { // 存在在产表中 if (dsResult.Tables[0].Rows[0]["InScrapFlag"].ToString() == "1") { resultEntity.Result = -3; //产品已经待审批报损 resultEntity.Message = "此产品【" + barcode + "】己待审报废，不能进行该操作"; return resultEntity; } int missbarcode = AddBarCodeMissingFinishedHandover(oracleConn, gbarcode, sUserInfo); if (missbarcode > 0) { // 第一次记录漏扫 resultEntity.Result = -4; resultEntity.OtherStatus = missbarcode;// 需要弹窗 resultEntity.Message = "此产品【" + barcode + "】当前工序为【" + dsResult.Tables[0].Rows[0]["procedureName"].ToString() + "】不是产成品，不能进行该操作"; return resultEntity; } else { resultEntity.Result = -5; resultEntity.OtherStatus = -1;// 不需要弹窗 resultEntity.Message = "此产品【" + barcode + "】当前工序为【" + dsResult.Tables[0].Rows[0]["procedureName"].ToString() + "】不是产成品，不能进行该操作"; return resultEntity; } } #endregion #region 2 条码在废弃产品表中且审批通过，提示：此产品【999999】已被废弃，不能进行该操作。 sql = @"select GoodsCode from tp_pm_inproductiontrash where BarCode=:BarCode"; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Result = -6; //产品已经报损 resultEntity.Message = "此产品【" + barcode + "】已被废弃,不能进行该操作"; return resultEntity; } #endregion #region 3 未交坯 sql = "select GoodsCode from TP_PM_GroutingDailyDetail where BarCode=:BarCode and DeliverTime is null"; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Result = -7; //产品未交坏 resultEntity.Message = "此产品【" + barcode + "】还未交坯,不能进行该操作"; return resultEntity; } else { resultEntity.Result = -8; //无效条码 resultEntity.Message = "此条码【" + barcode + "】为无效条码,不能进行该操作"; return resultEntity; } #endregion } #region 查询产品相关信息 sql = "select gd.BarCode,\n" + " l.logoid,\n" + " l.logocode,\n" + " l.logoname,\n" + " gd.goodscode,\n" + " gd.UserID,\n" + " gd.UserCode as GroutingUserCode,\n" + " gd.GroutingMouldCode as MouldCode,\n" + " gd.GroutingDate,\n" + " gd.DeliverTime,\n" + " nvl(gd.MaterialCode, nvl(Goods.MaterialCode, Goods.GoodsCode)) ||'#'|| to_char(Goods.GOODS_LINE_TYPE) as GoodsModel, -- 用物料编码验证产品型号是否一致\n" + " Goods.PlateLimitNum,\n" + " nvl(gd.outlabelcode,\n" + " Goods.MaterialCode ||\n" + " (select a.workcode\n" + " from tp_mst_account a\n" + " where a.accountid = gd.accountid) || l.tagcode || gd.onlycode) as FinishedBarCode,\n" + " o.ORDERNO,\n" + " SUBSTR( gd.OUTLABELCODE, 0, 14 ) OUTLABELCODE,\n" + " gd.MATERIALCODE,\n" + " gd.OUTLABELCODE as OUTLABELCODES,\n" + " f.BanMa\n" + " from TP_PM_GroutingDailyDetail gd\n" + " inner join tp_mst_logo l\n" + " on gd.logoid = l.logoid\n" + " inner join TP_PM_FinishedProduct f\n" + " on f.GROUTINGDAILYDETAILID = gd.GROUTINGDAILYDETAILID\n" + " left join tp_pm_order o\n" + " on o.orderid = f.FHORDERID\n" + " inner join TP_MST_Goods Goods\n" + " on gd.goodsid = Goods.goodsid \n"; if (string.IsNullOrWhiteSpace(FINISHEDLOADBATCHNO)) { sql += " where gd.BarCode='" + gbarcode + "'"; } else { sql += " where f.FINISHEDLOADBATCHNO ='" + FINISHEDLOADBATCHNO + "'"; paras[0].Value = FINISHEDLOADBATCHNO; } dsResult = oracleConn.GetSqlResultToDs(sql); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { for (int i = 0; i < dsResult.Tables[0].Rows.Count; i++) { if (dsResult.Tables[0].Rows[i]["OUTLABELCODE"].ToString() != dsResult.Tables[0].Rows[i]["MATERIALCODE"].ToString()) { resultEntity.Result = -1; resultEntity.Message = "物料编码与外包装箱码不匹配,不允许装板。外包装箱码：" + dsResult.Tables[0].Rows[i]["OUTLABELCODES"]; return resultEntity; } } resultEntity.Result = 1;//成功 resultEntity.Data = dsResult; } else { resultEntity.Result = -2;// 条码未注浆 resultEntity.Message = "条码【" + barcode + "】未注浆"; return resultEntity; } #endregion return resultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 整版撤销产成品交接条码验证 /// /// /// /// public static ServiceResultEntity LCBackFinishedHandoverCheck(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); ServiceResultEntity resultEntity = new ServiceResultEntity(); if (barcode.Length == 8) { resultEntity.Result = -1; resultEntity.Message = "撤销交接不允许扫板码。"; return resultEntity; } string gbarcode = null; string FINISHEDLOADBATCHNO = null; string sqlString = @"select FUN_CMN_GetBarCode(:barcode,null,:accountid) From DUAL"; OracleParameter[] paras1 = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), }; DataSet ds = oracleConn.GetSqlResultToDs(sqlString, paras1); if (ds != null && ds.Tables[0].Rows.Count > 0) { gbarcode = ds.Tables[0].Rows[0][0].ToString(); } // 1.判断产品是否在产成品表中 string sql = @"select FHUserID, FHUserCode, LCFHUserID, LCFHUserCode, GoodsID, GoodsCode, GoodsName, FINISHEDLOADBATCHNO from TP_PM_FinishedProduct where BarCode=:BarCode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.NVarchar2,gbarcode,ParameterDirection.Input), }; DataSet dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { FINISHEDLOADBATCHNO = dsResult.Tables[0].Rows[0]["FINISHEDLOADBATCHNO"].ToString(); // 在产成品表中 #region 是否交接过 string fhUserCode = dsResult.Tables[0].Rows[0]["FHUserCode"].ToString(); string lcfhUserCode = dsResult.Tables[0].Rows[0]["LCFHUserCode"].ToString(); if (string.IsNullOrEmpty(lcfhUserCode)) { resultEntity.Result = -1; //未交接，不能撤销 resultEntity.Message = "此产品【" + barcode + "】未交接，不能进行该操作"; return resultEntity; } if (!string.IsNullOrEmpty(fhUserCode)) { resultEntity.Result = -1; //已交接，不能撤销 resultEntity.Message = "此产品【" + barcode + "】已包装交接，不能进行该操作"; return resultEntity; } #endregion } else { resultEntity.Result = -2; //不能产成品，不能撤销 sql = "select GoodsCode from TP_PM_GroutingDailyDetail where BarCode=:BarCode"; dsResult = oracleConn.GetSqlResultToDs(sql, paras); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Message = "此产品【" + barcode + "】不是产成品，不能进行该操作"; } else { resultEntity.Message = "此条码【" + barcode + "】无效，不能进行该操作"; } return resultEntity; } #region 查询产品相关信息 sql = "select gd.BarCode,\n" + " l.logoid,\n" + " l.logocode,\n" + " l.logoname,\n" + " gd.goodscode,\n" + " gd.UserID,\n" + " gd.UserCode as GroutingUserCode,\n" + " gd.GroutingMouldCode as MouldCode,\n" + " gd.GroutingDate,\n" + " gd.DeliverTime,\n" + " nvl(gd.MaterialCode, nvl(Goods.MaterialCode, Goods.GoodsCode)) ||'#'|| to_char(Goods.GOODS_LINE_TYPE) as GoodsModel, -- 用物料编码验证产品型号是否一致\n" + " Goods.PlateLimitNum,\n" + " nvl(gd.outlabelcode,\n" + " Goods.MaterialCode ||\n" + " (select a.workcode\n" + " from tp_mst_account a\n" + " where a.accountid = gd.accountid) || l.tagcode || gd.onlycode) as FinishedBarCode,\n" + " o.ORDERNO,\n" + " f.BanMa\n" + " from TP_PM_GroutingDailyDetail gd\n" + " inner join tp_mst_logo l\n" + " on gd.logoid = l.logoid\n" + " inner join TP_PM_FinishedProduct f\n" + " on f.GROUTINGDAILYDETAILID = gd.GROUTINGDAILYDETAILID\n" + " left join tp_pm_order o\n" + " on o.orderid = f.FHORDERID\n" + " inner join TP_MST_Goods Goods\n" + " on gd.goodsid = Goods.goodsid \n"; if (string.IsNullOrWhiteSpace(FINISHEDLOADBATCHNO)) { sql += " where gd.BarCode='" + gbarcode + "'"; } else { sql += " where f.FINISHEDLOADBATCHNO ='" + FINISHEDLOADBATCHNO + "'"; paras[0].Value = FINISHEDLOADBATCHNO; } dsResult = oracleConn.GetSqlResultToDs(sql); if (dsResult != null && dsResult.Tables[0].Rows.Count > 0) { resultEntity.Result = 1;//成功 resultEntity.Data = dsResult; } else { resultEntity.Result = -2;// 条码未注浆 resultEntity.Message = "条码【" + barcode + "】未注浆"; return resultEntity; } #endregion return resultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion } }