/******************************************************************************* * 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.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels; using Dongke.IBOSS.PRD.WCF.DataModels.PMModule; using Oracle.DataAccess.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(); } } } /// /// 使用存储过程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 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"; 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.DataAccess.Client.OracleParameter[] oracleParameter = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.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(); // ccc todo if (!isInn) { //xuwei fix 2019-09-26 使用通用方法判定 if (PMModuleLogicDAL.IsNodeBegin(oracleConn, barcode) == 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; 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)) as GOODSMODELforCheck, -- 用物料编码验证产品型号是否一致 tp_mst_logo.logocode, u.username as GroutingUserName, tp_mst_logo.logoname 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 where tp_pm_inproduction.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(); dr["GOODSMODELforCheck"] = ds.Tables[0].Rows[0]["GOODSMODELforCheck"].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(); } } dtBarCode.Rows.Add(dr); returnDs.Tables.Add(dtBarCode); 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(); DataTable dtBarCode = PMModuleLogic.CreateBarCodeResultTable(); DataSet returnDs = 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"]); // ccc todo //xuwei fix 2019-09-26 使用通用方法判定 if (PMModuleLogicDAL.IsNodeBegin(oracleConn, barcode) == 1) { NodeType = (int)Constant.ProcedureNodeType.Begin; } 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 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, TP_PM_InProduction.Groutingdate, TP_PM_InProduction.SpecialRepairFlag, TP_PM_InProduction.IsReFire, 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)) as GOODSMODELforCheck, -- 用物料编码验证产品型号是否一致 --nvl(gdd.MaterialCode, g.GoodsCode) as GOODSMODELforCheck, -- 用物料编码验证产品型号是否一致 tp_mst_logo.logocode, tp_mst_logo.logoname 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 LEFT JOIN TP_MST_GOODS G ON G.GoodsID = tp_pm_inproduction.goodsid where TP_PM_InProduction.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()] = 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_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(); dr["PlateLimitNum"] = ds.Tables[0].Rows[0]["PlateLimitNum"]; } } } } 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 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 "; 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为重烧 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=: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='1' order by p.displayno "; 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 from tp_PM_ProductionData where ProductionDataid=(select max(ProductionDataid) from tp_PM_ProductionData where barcode=:barcode)"; Oracle.DataAccess.Client.OracleParameter[] oracleParameter = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.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(); // ccc todo if (!isInn) { //xuwei fix 2019-09-26 使用通用方法判定 if (PMModuleLogicDAL.IsNodeBegin(oracleConn, barcode) == 0) { 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(); 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(); } } } } 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; } 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 )"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":SemiCheckID",OracleDbType.Int32, semiCheckID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); ds.Tables[0].TableName = "TP_PM_SemiCheck"; DataSet ds2 = con.GetSqlResultToDs(sqlString2, paras); ds2.Tables[0].TableName = "TP_PM_SemiCheckDefect"; DataSet ds3 = con.GetSqlResultToDs(sqlString3, paras); ds3.Tables[0].TableName = "TP_PM_SCDefectResponsible"; 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(); } } } #endregion #region 获取条码所走过的工序 /// /// 根据条码获取经过的工序,用于绑定返工工序 /// /// 产品条码 /// DataSet public static DataSet GetSemiCheckPassProcedure(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); 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; } if (dsResult.Tables[0].Rows[0]["IsReworkFlag"].ToString() == "0") { 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() + "]"; ; } 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, Remarks, ValueFlag, CreateTime, UpdateTime from TP_PM_Order 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)); } 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 产成品交接 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 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.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(); 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 } 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 查询产品相关信息 /* 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)) 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" + " 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(); } } } #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()] = "报损待审产品不能回收"; } else if (Convert.ToInt32(dsNew.Tables[0].Rows[0]["ISREWORKFLAG"]) == 1) { 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 } 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(); } } } } }