/******************************************************************************* * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential * 类的信息： * 1.程序名称：PMModuleLogic.cs * 2.功能描述：生产管理 * 编辑履历： * 作者 日期 版本 修改内容 * 陈冰 2014/09/3 1.00 新建 *******************************************************************************/ using System; using System.Collections.Generic; using System.Data; using System.Drawing; using System.IO; 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 { /// /// 构建 计数/检验时返回的信息 /// /// public static DataTable CreateBarCodeResultTable() { // 注意：更新表字段时，一定把后续引用的字段全部更新一遍 DataTable barCodeResultTable = new DataTable("BarCodeTable"); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_errMsg.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_goodsID.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_goodsCode.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_goodsName.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_groutingUserCode.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_groutingUserName.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_groutingUserID.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_groutingNum.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_mouldCode.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_ispublicbody.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_ispublicbodyTrach.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_groutingdate.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_specialRepairFlag.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_isReFire.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_missFlag.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_logoID.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_logoCode.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_logoName.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_deliverLimitCycle.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_barcode.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_WaterLabelCode.ToString()); barCodeResultTable.Columns.Add(Constant.BarCodeResultTableColumns.out_CodeCheckFlag.ToString()); // 包装装板用 barCodeResultTable.Columns.Add("GOODSMODELforCheck"); // 最大装板数量 barCodeResultTable.Columns.Add("PlateLimitNum", typeof(int)); return barCodeResultTable; } /// /// 构建 校验窑车号时返回的信息 /// /// public static DataTable CreateKilnCarResultTable() { // 注意：更新表字段时，一定把后续引用的字段全部更新一遍 DataTable kilnCarResultTable = new DataTable("KilnCarTable"); kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_errMsg.ToString()); kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_kilnCarID.ToString()); kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_kilnCarName.ToString()); kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_kilnID.ToString()); kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_kilnCode.ToString()); kilnCarResultTable.Columns.Add(Constant.KilnCarResultTableColumns.out_kilnName.ToString()); return kilnCarResultTable; } /// /// 校验窑车是否可用 /// /// 工序ID /// 窑车号 /// 类别 /// 用户基本信息 /// CheckKilnCarResultEntity实体类 /// /// 陈冰 2014.09.26 新建 /// public static CheckKilnCarResultEntity CheckKilnCar(int pProcedureId, string kilnCarCode, int pModelType, SUserInfo sUserInfo) { CheckKilnCarResultEntity kilnCarResultEntity = new CheckKilnCarResultEntity(); IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_accountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("in_kilnCarCode",OracleDbType.NVarchar2, kilnCarCode,ParameterDirection.Input), new OracleParameter("in_modelType",OracleDbType.Int32, pModelType,ParameterDirection.Input), new OracleParameter("out_errMsg",OracleDbType.NVarchar2,2000,null,ParameterDirection.Output), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), new OracleParameter("in_procedureid",OracleDbType.Int32, pProcedureId,ParameterDirection.Input), // 验证能否卸窑 new OracleParameter("in_UserID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; DataSet returnDataSet = oracleConn.ExecStoredProcedure("PRO_PM_CheckKilnCar", paras); kilnCarResultEntity.ErrMsg = paras[3].Value.ToString() == "null" ? string.Empty : paras[3].Value.ToString().Replace("\\n\\r", "\n\r"); if (returnDataSet == null || returnDataSet.Tables.Count <= 0) { kilnCarResultEntity.KilnCarInfos = new List(); } else { kilnCarResultEntity.KilnCarInfos = DataConvert.TableConvertToObject(returnDataSet.Tables[0]); } return kilnCarResultEntity; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 由工序获取产缺陷列表 /// /// 工序ID /// DataSet public static DataSet GetDefectByProcedure(int procedureID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_MST_Defect.DefectID as DefectFlagID,TP_MST_Defect.DefectCode, TP_MST_Defect.DefectName as ViewDefectFlagName, concat(TP_MST_Defect.DefectCode||'->',TP_MST_Defect.DefectName) as DefectFlagName from TP_PC_ProcedureDefect left join TP_MST_Defect on TP_PC_ProcedureDefect.DefectID=TP_MST_Defect.DefectID where TP_MST_Defect.valueflag = '1' and TP_PC_ProcedureDefect.procedureID=" + procedureID; DataSet ds = con.GetSqlResultToDs(sqlString); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 由当前检验工序ID和缺陷Code获取缺陷数据 /// /// 当前检验工序ID /// 缺陷Code /// object /// /// 陈冰 2014.10.04 新建 /// public static object GetDefectByProcedureIDAndDefectCode(int procedureID, string defectCode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_MST_Defect.DefectID, TP_MST_Defect.DefectName, TP_PC_ProcedureDefect.procedureID from TP_MST_Defect left join TP_PC_ProcedureDefect on TP_MST_Defect.DefectID = TP_PC_ProcedureDefect.DefectID and TP_PC_ProcedureDefect.procedureID = :procedureID where TP_MST_Defect.DefectCode = :defectCode and TP_MST_Defect.Valueflag='1'"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":procedureID",procedureID), new OracleParameter(":defectCode",defectCode), }; DataSet resultds = con.GetSqlResultToDs(sqlString, paras); if (resultds != null && resultds.Tables.Count > Constant.INT_IS_ZERO) { string errMsg = string.Empty; int defectID = 0; string defectName = string.Empty; if (resultds.Tables[0].Rows.Count == Constant.INT_IS_ZERO) { errMsg = "缺陷在系统中不存在"; } else { if (string.IsNullOrEmpty(resultds.Tables[0].Rows[0]["ProcedureID"].ToString())) { errMsg = "当前工序没有配置该缺陷"; } else { defectID = int.Parse(resultds.Tables[0].Rows[0]["DefectID"].ToString()); defectName = resultds.Tables[0].Rows[0]["DefectName"].ToString(); } } return new { ErrMsg = errMsg, DefectID = defectID, DefectName = defectName }; } else { return null; } } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据产品ID查出缺陷位置 /// /// 产品ID /// DataSet public static DataSet GetDefectLocaionByGoodsID(int goodsID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_MST_DEFECTPOSITION.DEFECTPOSITIONID,TP_MST_DEFECTPOSITION.DEFECTPOSITIONCODE,TP_MST_DEFECTPOSITION.DEFECTPOSITIONNAME ,concat(TP_MST_DEFECTPOSITION.DEFECTPOSITIONCODE||'->',TP_MST_DEFECTPOSITION.DEFECTPOSITIONNAME) as DEFECTPOSITIONCODEANDNAME from tp_mst_goodsdefectposition left join TP_MST_DEFECTPOSITION on tp_mst_goodsdefectposition.defectpositionid=TP_MST_DEFECTPOSITION.defectpositionid where tp_mst_goodsdefectposition.goodsid=" + goodsID; DataSet ds = con.GetSqlResultToDs(sqlString); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据缺陷位置 /// /// 用户基本信息 /// DataSet public static DataSet GetDefectLocaion(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 ValueFlag=1"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":accountID",sUserInfo.AccountID), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据产品ID查出缺陷位置 /// /// 产品ID /// 位置编码 /// object public static object GetDefectPositionByGoodsIDAndPositionCode(int goodsID, string positionCode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select tp_mst_defectposition.defectpositionid, tp_mst_defectposition.defectpositionname, tp_mst_goodsdefectposition.goodsid from tp_mst_defectposition left join tp_mst_goodsdefectposition on tp_mst_goodsdefectposition.defectpositionid = tp_mst_defectposition.defectpositionid and tp_mst_goodsdefectposition.goodsid =:goodsid where tp_mst_defectposition.defectpositioncode = :defectpositioncode and tp_mst_defectposition.Valueflag='1'"; OracleParameter[] paras = new OracleParameter[] { new OracleParameter(":goodsid",goodsID), 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 { if (string.IsNullOrEmpty(resultds.Tables[0].Rows[0]["goodsid"].ToString())) { 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 GetDutyProcedureByBarCode(string barcode, int defectid, int accountid) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); DataSet dsReturn = new DataSet(); // string sqlString = @"select TP_PM_ProductionDataIn.ProductionDataID, DutyProcedureID,DutyProcedureCode,DutyProcedureName,NodeType,ModelType from // ( // select TP_PM_ProductionDataIn.ProductionDataID,TP_PM_ProductionDataIn.ProcedureID as DutyProcedureID, // TP_PM_ProductionDataIn.ProcedureCode as DutyProcedureCode, // TP_PM_ProductionDataIn.ProcedureName as DutyProcedureName, // TP_PM_ProductionDataIn.NodeType, // TP_PM_ProductionDataIn.ModelType // from TP_PM_ProductionDataIn // where valueflag = '1' and TP_PM_ProductionDataIn.Barcode=:Barcode // order by TP_PM_ProductionDataIn.ProductionDataID // ) TP_PM_ProductionDataIn // where exists // ( // select // TP_PC_DefectProcedureJobs.DefectID, // TP_PC_DefectProcedureJobs.ProductionLineID, // TP_PC_DefectProcedureJobs.ProcedureID, // TP_PC_DefectProcedureJobs.NodeNo, // TP_PC_DefectProcedureJobs.JobsID // from TP_PC_DefectProcedureJobs // where TP_PC_DefectProcedureJobs.DefectID=:DefectID // and TP_PM_ProductionDataIn.DutyProcedureID=TP_PC_DefectProcedureJobs.ProcedureID // )"; //string sqlString = //"SELECT pdin.ProductionDataID\n" + //" ,pdin.ProcedureID AS DutyProcedureID\n" + //" ,pdin.ProcedureCode AS DutyProcedureCode\n" + //" ,pdin.ProcedureName AS DutyProcedureName\n" + //" ,pdin.NodeType\n" + //" ,pdin.ModelType\n" + //" ,pdin.classessettingid\n" + //" ,pdin.userid\n" + //" ,pdin.usercode\n" + //" ,pdin.username\n" + //" FROM TP_PM_ProductionDataIn pdin\n" + //" WHERE pdin.valueflag = '1'\n" + //" AND pdin.accountid = :accountid\n" + //" AND pdin.Barcode = :Barcode\n" + //" AND EXISTS (SELECT 1\n" + //" FROM TP_PC_DefectProcedureJobs dpJobs\n" + //" WHERE dpJobs.DefectID = :DefectID\n" + //" AND pdin.ProcedureID = dpJobs.ProcedureID)\n" + //" ORDER BY pdin.ProductionDataID"; // 去掉重复工序（取最后一个） string sqlString = "select pdin.ProductionDataID\n" + " ,pdin.ProcedureID as DutyProcedureID\n" + " ,pdin.ProcedureCode as DutyProcedureCode\n" + " ,pdin.ProcedureName as DutyProcedureName\n" + " ,pdin.NodeType\n" + " ,pdin.ModelType\n" + " ,pdin.classessettingid\n" + " ,pdin.userid\n" + " ,pdin.usercode\n" + " ,pdin.username\n" + " from TP_PM_ProductionDataIn pdin\n" + " where pdin.productiondataid in\n" + " (select max(pdi.productiondataid) pdid\n" + " from TP_PM_ProductionDataIn pdi\n" + " where pdi.valueflag = '1'\n" + " and pdi.accountid = :accountid\n" + " and pdi.Barcode = :Barcode\n" + " and exists\n" + " (select 1\n" + " from TP_PC_DefectProcedureJobs dpJobs\n" + " where dpJobs.DefectID = :DefectID\n" + " and pdi.ProcedureID = dpJobs.ProcedureID)\n" + " group by pdi.ProcedureID)\n" + " order by pdin.ProductionDataID"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":accountid",OracleDbType.Int32, accountid,ParameterDirection.Input), new OracleParameter(":Barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":DefectID",OracleDbType.Int32, defectid,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); ds.Tables[0].TableName = "TProcedure"; //sqlString = "Select DictionaryID,DictionaryValue" // + " from TP_MST_DataDictionary where valueflag = 1 and DictionaryType = 'ASE002' and AccountID = :AccountID"; //paras = new Oracle.DataAccess.Client.OracleParameter[] // { // new Oracle.DataAccess.Client.OracleParameter(":AccountID",accountid), // }; //DataSet ds2 = con.GetSqlResultToDs(sqlString, paras); //ds2.Tables[0].TableName = "TDataDictionary"; sqlString = "select procedureid,jobsid from TP_PC_DefectProcedureJobs where TP_PC_DefectProcedureJobs.Defectid=:Defectid"; paras = new Oracle.DataAccess.Client.OracleParameter[] { new Oracle.DataAccess.Client.OracleParameter(":Defectid",defectid), }; DataSet ds3 = con.GetSqlResultToDs(sqlString, paras); ds3.Tables[0].TableName = "TP_PC_DefectProcedureJobs"; if (!dsReturn.Tables.Contains("TProcedure")) { dsReturn.Tables.Add(ds.Tables[0].Copy()); } //if (!dsReturn.Tables.Contains("TDataDictionary")) //{ // dsReturn.Tables.Add(ds2.Tables[0].Copy()); //} if (!dsReturn.Tables.Contains("TP_PC_DefectProcedureJobs")) { 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 GetDutyProcedureCodeByBarCode(string barcode, int dutyProcedureID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select UserID,UserCode,UserName,Remarks,ProductionDataID from TP_PM_ProductionDataIn where Barcode=:Barcode and ProcedureID=:ProcedureID and valueflag=1"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":Barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), new OracleParameter(":ProcedureID",OracleDbType.Int32, dutyProcedureID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 责任工序查出工号根据生产数据ID /// /// /// public static DataSet GetDutyProcedureCodeByProductionDataID(int ProductionDataID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select UserID,UserCode,UserName,Remarks,ProductionDataID 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 /// 生产数据ID /// DataSet public static DataSet GetDutyJobsCodeByUser(int userID, int productionDataID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // string sqlString = @"select distinct // TP_PM_Producer.UserID,TP_MST_Jobs.JobsID,TP_MST_Jobs.JobsName,TP_MST_Jobs.JobsCode // from TP_PM_Producer // left join TP_MST_Jobs // on TP_PM_Producer.UJobsID=TP_MST_Jobs.JobsID // where TP_PM_Producer.UserID=:UserID // and TP_PM_Producer.UJobsID in // ( // select // JobsID // from TP_PC_DefectProcedureJobs where ProcedureID= // ( // Select ProcedureID from TP_PM_ProductionDataIn where ProductionDataID=:ProductionDataID // ) // )"; string sqlString = @"select distinct tp_pc_classesdetail.UserID, TP_MST_Jobs.JobsID, TP_MST_Jobs.JobsName, TP_MST_Jobs.JobsCode from tp_pc_classesdetail left join TP_MST_Jobs on tp_pc_classesdetail.UJobsID = TP_MST_Jobs.JobsID where tp_pc_classesdetail.UserID = :UserID and tp_pc_classesdetail.UJobsID in (select JobsID from TP_PC_DefectProcedureJobs where ProcedureID = (Select ProcedureID from TP_PM_ProductionDataIn where ProductionDataID = :ProductionDataID) )"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":UserID",OracleDbType.Int32, userID,ParameterDirection.Input), 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，用户ID及工种选出责任员工 /// /// 生产数据ID /// 用户ID /// 工种ID /// DataSet public static DataSet GetDutyStaffByUserIDAndJobs(int productionDataID, int userID, int jobs) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // string sqlString = @"select TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_PM_Producer.SJobsID // from TP_PM_Producer // left join TP_HR_Staff on TP_PM_Producer.StaffID=TP_HR_Staff.StaffID // where TP_PM_Producer.ProductionDataID=:ProductionDataID // and TP_PM_Producer.UserID=:UserID and TP_PM_Producer.UJobsID=:JobsID // "; string sqlString = @"select TP_HR_Staff.StaffID, TP_HR_Staff.StaffCode, TP_HR_Staff.StaffName, TP_HR_Staff.StaffStatus, tp_pc_classesdetail.SJobsID from tp_pc_classesdetail left join TP_HR_Staff on tp_pc_classesdetail.StaffID = TP_HR_Staff.StaffID where tp_pc_classesdetail.ClassesSettingID=(select ClassesSettingID from tp_pm_productiondatain where productiondataid=:ProductionDataID) and tp_pc_classesdetail.UserID = :UserID and tp_pc_classesdetail.UJobsID = :JobsID "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":ProductionDataID",OracleDbType.Int32, productionDataID,ParameterDirection.Input), new OracleParameter(":UserID",OracleDbType.Int32, userID,ParameterDirection.Input), new OracleParameter(":JobsID",OracleDbType.Int32, jobs,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据传入的实体获取进度考核奖惩信息 /// /// 查询实体 /// 用户基本信息 /// DataSet进度考核奖惩信息表 public static DataSet SearcStaffAdminRAPInfo(SearchProgressRAPEntity searchProgressRAPEntity, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { string strIdList = string.Empty; if (searchProgressRAPEntity.IDList != null && searchProgressRAPEntity.IDList.Length > 0) { strIdList = DataConvert.ConvertListToSqlInWhere(searchProgressRAPEntity.IDList); } con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("in_staffID",OracleDbType.Int32,searchProgressRAPEntity.StaffID,ParameterDirection.Input), new OracleParameter("in_rapType",OracleDbType.Double,searchProgressRAPEntity.RAPType,ParameterDirection.Input), new OracleParameter("in_startRAPDate",OracleDbType.Date,searchProgressRAPEntity.StartRAPDate,ParameterDirection.Input), new OracleParameter("in_endRAPDate",OracleDbType.Date,searchProgressRAPEntity.EndRAPDate,ParameterDirection.Input), new OracleParameter("in_startRAPAmount",OracleDbType.Double,searchProgressRAPEntity.StartRAPAmount,ParameterDirection.Input), new OracleParameter("in_endRAPAmount",OracleDbType.Double,searchProgressRAPEntity.EndRAPAmount,ParameterDirection.Input), new OracleParameter("in_auditStatus",OracleDbType.Int32,searchProgressRAPEntity.AuditStatus,ParameterDirection.Input), new OracleParameter("in_settlementFlag",OracleDbType.Char,searchProgressRAPEntity.SettlementFlag,ParameterDirection.Input), new OracleParameter("in_valueFlag",OracleDbType.Char,searchProgressRAPEntity.ValueFlag,ParameterDirection.Input), new OracleParameter("in_sIDList",OracleDbType.Varchar2,strIdList,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), }; return con.ExecStoredProcedure("PRO_PM_StaffProgressRAP", paras); } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据条码获取窑炉窑车信息 /// /// 产品条码 /// DataSet public static DataSet GetKilnCarByBarCode(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // string sqlString = @" // select // TP_MST_KilnCar.KilnCarCode, // TP_MST_Kiln.KilnCode, // TP_MST_DataDictionary.Dictionaryvalue // from TP_PM_KilnCarGoods // left join TP_MST_KilnCar // on TP_PM_KilnCarGoods.KilnCarID=TP_MST_KilnCar.Kilncarid // left join TP_MST_Kiln // on TP_MST_KilnCar.Kilnid=TP_MST_Kiln.Kilnid // left join TP_MST_DataDictionary // on TP_PM_KilnCarGoods.KilnCarPosition=TP_MST_DataDictionary.DictionaryID // where TP_PM_KilnCarGoods.Barcode=:Barcode // "; string sqlString = @" select TP_PM_InProduction.KilnCarCode, TP_PM_InProduction.KilnCode, TP_MST_DataDictionary.Dictionaryvalue from TP_PM_InProduction inner join TP_MST_DataDictionary on TP_PM_InProduction.KilnCarPosition=TP_MST_DataDictionary.DictionaryID where TP_PM_InProduction.Barcode=:Barcode "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":Barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #region 生产数据 /// /// 查询在产产品数据 /// /// 在产产品数据实体 /// 用户基本信息 /// DataSet在产产品数据信息表 public static DataSet GetInProductionData(SearchInProductionEntity searchInProductionEntity, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_barCode",OracleDbType.Varchar2,searchInProductionEntity.BarCode,ParameterDirection.Input), new OracleParameter("in_productionLineId",OracleDbType.Varchar2,searchInProductionEntity.ProductionLineIDS,ParameterDirection.Input), new OracleParameter("in_completeProcedureId",OracleDbType.Varchar2,searchInProductionEntity.ProcedureIDS,ParameterDirection.Input), new OracleParameter("in_goodsId",OracleDbType.Varchar2,searchInProductionEntity.GoodsIDS,ParameterDirection.Input), new OracleParameter("in_userId",OracleDbType.Varchar2,searchInProductionEntity.UserIDS,ParameterDirection.Input), new OracleParameter("in_groutingLineId",OracleDbType.Varchar2,searchInProductionEntity.GroutingLineIDS,ParameterDirection.Input), new OracleParameter("in_gMouldTypeId",OracleDbType.Varchar2,searchInProductionEntity.GMouldTypeIDS,ParameterDirection.Input), new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("in_createTimeStart",OracleDbType.Varchar2,searchInProductionEntity.CreateTimeStart,ParameterDirection.Input), new OracleParameter("in_createTimeEnd",OracleDbType.Varchar2,searchInProductionEntity.CreateTimeEnd,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet dsInproduction = con.ExecStoredProcedure("PRO_PM_GetInProductionData", paras); return dsInproduction; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 查询成品数据 /// /// 产成品数据实体 /// 用户基本信息 /// DataSet产成品数据信息表 public static DataSet GetFinishedProductionData(SearchFinishedProductEntity searchFinishedProductionEntity, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_barCode",OracleDbType.Varchar2,searchFinishedProductionEntity.BarCode,ParameterDirection.Input), new OracleParameter("in_productionLineId",OracleDbType.Varchar2,searchFinishedProductionEntity.ProductionLineIDS,ParameterDirection.Input), new OracleParameter("in_goodsId",OracleDbType.Varchar2,searchFinishedProductionEntity.GoodsIDS,ParameterDirection.Input), new OracleParameter("in_groutingLineId",OracleDbType.Varchar2,searchFinishedProductionEntity.GroutingLineIDS,ParameterDirection.Input), new OracleParameter("in_gMouldTypeId",OracleDbType.Varchar2,searchFinishedProductionEntity.GMouldTypeIDS,ParameterDirection.Input), new OracleParameter("in_accountDateStart",OracleDbType.Varchar2,searchFinishedProductionEntity.AccountDateStart,ParameterDirection.Input), new OracleParameter("in_accountDateEnd",OracleDbType.Varchar2,searchFinishedProductionEntity.AccountDateEnd,ParameterDirection.Input), new OracleParameter("in_createTimeStart",OracleDbType.Varchar2,searchFinishedProductionEntity.CreateTimeStart,ParameterDirection.Input), new OracleParameter("in_createTimeEnd",OracleDbType.Varchar2,searchFinishedProductionEntity.CreateTimeEnd,ParameterDirection.Input), new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet dsInproduction = con.ExecStoredProcedure("PRO_PM_GetFinishdProductData", paras); return dsInproduction; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 查询半检数据一览 /// /// 查询半检数据明细实体类 /// 用户基本信息 /// DataSet public static DataSet GetSearchSemiTestListModule(SemiTestDetailEntity semiTestDetailEntity, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_goodsId",OracleDbType.Varchar2,semiTestDetailEntity.GoodsIDS,ParameterDirection.Input), new OracleParameter("in_testUserID",OracleDbType.Varchar2,semiTestDetailEntity.TestUserIDS,ParameterDirection.Input), new OracleParameter("in_groutingUserID",OracleDbType.Varchar2,semiTestDetailEntity.GroutingUserIDS,ParameterDirection.Input), new OracleParameter("in_semitesttype",OracleDbType.Int32,semiTestDetailEntity.SemiTestType,ParameterDirection.Input), new OracleParameter("in_remarks",OracleDbType.Varchar2,semiTestDetailEntity.Remarks,ParameterDirection.Input), new OracleParameter("in_userPurviews",OracleDbType.Varchar2,semiTestDetailEntity.UserPurviews,ParameterDirection.Input), new OracleParameter("in_semiTestDateStart",OracleDbType.Varchar2,semiTestDetailEntity.SemiTestDateStart,ParameterDirection.Input), new OracleParameter("in_semiTestDateEnd",OracleDbType.Varchar2,semiTestDetailEntity.SemiTestDateEnd,ParameterDirection.Input), new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), }; DataSet dsSearchReport = con.ExecStoredProcedure("PRO_PM_GetSemiTestList", paras); return dsSearchReport; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } #endregion #region 校验条码是否可以下车 /// /// 校验条码是否可以下车 /// /// 当前工序 /// 产品条码 /// 用户基本信息 /// CheckCancelLoadCar public static CheckCancelLoadCar CheckCancelLoadCar(int procedureID, string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_barcode",OracleDbType.Varchar2,barcode,ParameterDirection.Input), new OracleParameter("in_procedureid",OracleDbType.Int32,procedureID,ParameterDirection.Input), new OracleParameter("in_accountID",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("out_errMsg",OracleDbType.NVarchar2,200,null,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_kilncarcode",OracleDbType.NVarchar2,50,null,ParameterDirection.Output), }; oracleConn.ExecStoredProcedure("PRO_PM_CheckCancelLoadCar", paras); CheckCancelLoadCar checkCancelLoadCar = new CheckCancelLoadCar(); checkCancelLoadCar.ErrMsg = paras[3].Value.ToString() == "null" ? "" : paras[3].Value.ToString().Replace("\\n\\r", "\n\r"); checkCancelLoadCar.GoodsCode = paras[4].Value.ToString() == "null" ? "" : paras[4].Value.ToString(); checkCancelLoadCar.GoodsName = paras[5].Value.ToString() == "null" ? "" : paras[5].Value.ToString(); checkCancelLoadCar.KilnCarCode = paras[6].Value.ToString() == "null" ? "" : paras[6].Value.ToString(); return checkCancelLoadCar; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #endregion /// /// 根据所选生产数据ID,显示成检数据信息 /// /// 生产数据ID /// DataSet public static DataSet GetProductionDataByID(int productionDataID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); DataSet dsReturn = new DataSet(); string sqlString = @"select TP_PM_ProductionData.Barcode as BarCode, TP_PM_ProductionData.Goodsid as GoodsID, TP_PM_ProductionData.Goodscode as GoodsCode, TP_PM_ProductionData.Goodsname as GoodsName, TP_PM_ProductionData.GoodsLevelID as DefectFlagID, TP_PM_ProductionData.Reworkprocedureid as ReworkProcedureID, TP_PM_ProductionData.Remarks as Remarks, TP_PM_ProductionData.Userid as UserID, TP_PM_ProductionData.UserCode as UserCode, TP_PM_ProductionData.UserName as UserName, TP_PM_ProductionData.Goodsleveltypeid as GoodsLevelTypeID, TP_PM_ProductionData.SpecialRepairflag, TP_PM_ProductionData.UserCode, TP_PM_ProductionData.KilnCode, TP_PM_ProductionData.KilnCarCode, TP_PM_ProductionData.GroutingUserCode, TP_PM_ProductionData.GroutingMouldCode as MouldCode, TP_PM_ProductionData.GroutingNum, TP_PM_ProductionData.GroutingDate, TP_MST_DataDictionary.Dictionaryvalue, TP_PM_ProductionData.IsPublicBody, TP_MST_Logo.logoid, TP_MST_Logo.logocode, TP_MST_Logo.logoname, TP_PM_ProductionData.CreateTime, TP_PM_ProductionData.CheckTime from TP_PM_ProductionData left join TP_MST_DataDictionary on TP_PM_ProductionData.KilnCarPosition=TP_MST_DataDictionary.DictionaryID left join TP_MST_Logo on TP_PM_ProductionData.logoid=TP_MST_Logo.logoid where TP_PM_ProductionData.Productiondataid=:ProductionDataID "; string sqlString2 = @" select TP_PM_Defect.ProductionDefectID as ProductionDefectID, TP_PM_Defect.Barcode as BarCode, TP_PM_Defect.DefectID as DefectID, TP_PM_Defect.Defectname as DefectName, TP_PM_Defect.Defectcode as DefectCode, TP_PM_Defect.Defectpositionid as DefectPositionID, TP_PM_Defect.Defectpositionname as DefectPositionName, TP_PM_Defect.Defectpositioncode as DefectPositionCode, TP_PM_Defect.Defectprocedureid as DefectProcedureID, TP_PM_Defect.Defectprocedurename as DefectProcedureName, TP_PM_Defect.Defectprocedurecode as DefectProcedureCode, TP_PM_Defect.Defectuserid as DefectUserID, TP_PM_Defect.Defectusername as DefectUserName, TP_PM_Defect.Defectusercode as DefectUserCode, TP_PM_Defect.DefectJobs as Jobs, nvl(TP_PM_Defect.MissedUserID,-1) as MissedUserID, TP_PM_Defect.MissedUserCode, TP_PM_Defect.MissedUserName, TP_MST_Jobs.Jobsname as JobsText, TP_PM_Defect.Remarks as DefectRemarks, TP_PM_Defect.Productiondataid as ProductionDataID, nvl(TP_PM_Defect.DefectProductionDataID,0) as DefectProductionDataID, TP_PM_Defect.Defectfine as DefectFineID, TP_MST_DefectFine.DefectFineCode as DefectFineValue, TP_PM_Defect.SpecialDefect, TP_PM_Defect.DefectDeductionNum, TP_PM_Defect.CheckTime 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"; string sqlString3 = @"select TP_PM_DefectResponsible.ProductionDefectID as ProductionDefectID, TP_PM_DefectResponsible.Staffid as StaffID, TP_HR_Staff.Staffcode as StaffCode, TP_HR_Staff.Staffname as StaffName, TP_PM_DefectResponsible.Staffstatus as StaffStatus from TP_PM_DefectResponsible left join TP_HR_Staff on TP_PM_DefectResponsible.StaffID=TP_HR_Staff.Staffid where TP_PM_DefectResponsible.Productiondefectid in ( select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID )"; string sqlString4 = @" select TP_PM_DefectImage.ProductionDefectID, TP_PM_DefectImage.Thumbnailpath, TP_PM_DefectImage.Imagepath from TP_PM_DefectImage where TP_PM_DefectImage.Productiondefectid in ( select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID )"; string sqlString5 = @"select TP_PM_DefectMissedResponsible.ProductionDefectID as ProductionDefectID, TP_PM_DefectMissedResponsible.Staffid as StaffID, TP_HR_Staff.Staffcode as StaffCode, TP_HR_Staff.Staffname as StaffName, TP_PM_DefectMissedResponsible.Staffstatus as StaffStatus, TP_PM_DefectMissedResponsible.UJobsID, TP_PM_DefectMissedResponsible.SJobsID from TP_PM_DefectMissedResponsible left join TP_HR_Staff on TP_PM_DefectMissedResponsible.StaffID=TP_HR_Staff.Staffid where TP_PM_DefectMissedResponsible.Productiondefectid in ( select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID )"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":ProductionDataID",OracleDbType.Int32, productionDataID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); ds.Tables[0].TableName = "TP_PM_ProductionData"; DataSet ds2 = con.GetSqlResultToDs(sqlString2, paras); ds2.Tables[0].TableName = "TP_PM_Defect"; DataSet ds3 = con.GetSqlResultToDs(sqlString3, paras); ds3.Tables[0].TableName = "TP_PM_DefectResponsible"; DataSet ds4 = con.GetSqlResultToDs(sqlString4, paras); ds4.Tables[0].TableName = "TP_PM_DefectImage"; DataSet ds5 = con.GetSqlResultToDs(sqlString5, paras); ds5.Tables[0].TableName = "TP_PM_DefectMissedResponsible"; dsReturn.Tables.Add(ds.Tables[0].Copy()); dsReturn.Tables.Add(ds2.Tables[0].Copy()); dsReturn.Tables.Add(ds3.Tables[0].Copy()); dsReturn.Tables.Add(ds4.Tables[0].Copy()); dsReturn.Tables.Add(ds5.Tables[0].Copy()); dsReturn.Tables["TP_PM_DefectImage"].Columns.Add("SourcePathByte", typeof(byte[])); for (int i = 0; i < dsReturn.Tables["TP_PM_DefectImage"].Rows.Count; i++) { string saveAllFilePath = AppDomain.CurrentDomain.BaseDirectory + dsReturn.Tables["TP_PM_DefectImage"].Rows[i]["ImagePath"].ToString(); if (File.Exists(saveAllFilePath)) { FileInfo file = new FileInfo(saveAllFilePath); Image PicImage = Image.FromStream(file.OpenRead()); byte[] smallbuffer = null; using (MemoryStream ms = new MemoryStream()) { PicImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg); ms.Position = 0; smallbuffer = new byte[ms.Length]; ms.Read(smallbuffer, 0, Convert.ToInt32(ms.Length)); ms.Flush(); } dsReturn.Tables["TP_PM_DefectImage"].Rows[i]["SourcePathByte"] = smallbuffer; } } return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取产品完成工序的ID /// /// 产品条码 /// int public static int GetCompleteProcedureID(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select FlowProcedureID as CompleteProcedureID, ProductionDataID, ProcedureID from TP_PM_InProduction where TP_PM_InProduction.BarCode=:BarCode union select FlowProcedureID as CompleteProcedureID, ProductionDataID, ProcedureID from Tp_Pm_Inproductiontrash where Tp_Pm_Inproductiontrash.BarCode=:BarCode "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { return Convert.ToInt32(ds.Tables[0].Rows[0]["CompleteProcedureID"]); } else { sqlString = @"select BarCode from TP_PM_FinishedProduct where TP_PM_FinishedProduct.BarCode=:BarCode "; paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds2 = con.GetSqlResultToDs(sqlString, paras); if (ds2 != null && ds2.Tables[0].Rows.Count > 0) { return -2; } else { return -1; } } } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 编辑后删除生产数据 /// /// 生产数据ID /// int public static int DeleteProductionDataByID(int productionDataID) { int deleteRow = 0; IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleTrConn.Connect(); string sqlString5 = @"update TP_PM_DefectMissedResponsible set ValueFlag=0 where TP_PM_DefectMissedResponsible.Productiondefectid in ( select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID )"; string sqlString4 = @"update TP_PM_ProductionData set ValueFlag=0 where TP_PM_ProductionData.Productiondataid=:ProductionDataID "; string sqlString3 = @"update TP_PM_Defect set ValueFlag=0 where TP_PM_Defect.Productiondataid =:ProductionDataID"; string sqlString2 = @"update TP_PM_DefectResponsible set ValueFlag=0 where TP_PM_DefectResponsible.Productiondefectid in ( select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID )"; string sqlString = @"update TP_PM_DefectImage set ValueFlag=0 where TP_PM_DefectImage.Productiondefectid in ( select ProductionDefectID from TP_PM_Defect where TP_PM_Defect.Productiondataid=:ProductionDataID )"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":ProductionDataID",OracleDbType.Int32, productionDataID,ParameterDirection.Input), }; deleteRow += oracleTrConn.ExecuteNonQuery(sqlString, paras); deleteRow += oracleTrConn.ExecuteNonQuery(sqlString2, paras); deleteRow += oracleTrConn.ExecuteNonQuery(sqlString3, paras); deleteRow += oracleTrConn.ExecuteNonQuery(sqlString4, paras); deleteRow += oracleTrConn.ExecuteNonQuery(sqlString5, paras); // 没有错误 提交事务 if (deleteRow > 0) { oracleTrConn.Commit(); } else { oracleTrConn.Rollback(); } } catch (Exception ex) { oracleTrConn.Rollback(); throw ex; } finally { // 释放资源 if (oracleTrConn.ConnState == System.Data.ConnectionState.Open) { oracleTrConn.Disconnect(); } } return deleteRow; } /// /// 由产品条码获取注浆信息 /// /// 产品条码 /// DataSet public static DataSet GetGroutingProducttByBarCode(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode, TP_PM_GroutingDailyDetail.GroutingCount as GroutingNum, TP_PM_GroutingDailyDetail.GroutingMouldCode as MouldCode, TP_MST_Logo.logoid, TP_MST_Logo.logocode, TP_MST_Logo.logoname, (select tp_pm_inproduction.ispublicbody from tp_pm_inproduction where tp_pm_inproduction.BarCode=:barcode) as ispublicbody, (select tp_pm_inproductiontrash.ispublicbody from tp_pm_inproductiontrash where tp_pm_inproductiontrash.BarCode=:barcode) as ispublicbodyTrach, TP_PM_GroutingDailyDetail.Groutingdate from TP_PM_GroutingDailyDetail left join TP_MST_Logo on TP_PM_GroutingDailyDetail.logoid=TP_MST_Logo.logoid where TP_PM_GroutingDailyDetail.BarCode=:barcode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",barcode), }; 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 GetDutyStaffByUserID(int jobs, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as SJobsID from TP_MST_UserStaff left join TP_HR_Staff on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid where TP_MST_UserStaff.Ujobsid=:jobs and TP_HR_Staff.StaffStatus in (1,2) order by TP_HR_Staff.StaffCode "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":jobs",OracleDbType.Int32, jobs,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 根据所选工号，查出漏检责任员工 /// /// 工号 /// 用户基本信息 /// DataSet public static DataSet GetMissedStaffByUserID(int userid, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as UJobsID,TP_HR_Staff.Jobs as SJobsID from TP_MST_UserStaff left join TP_HR_Staff on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid where TP_MST_UserStaff.Userid=:userid "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":userid",OracleDbType.Int32, userid,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 GetDutyStaffByUserJobsID(int jobs, SUserInfo sUserInfo, int userid) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName,TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as SJobsID from TP_MST_UserStaff left join TP_HR_Staff on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid where TP_MST_UserStaff.Ujobsid=:jobs and TP_MST_UserStaff.Userid=:userid "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":jobs",OracleDbType.Int32, jobs,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, userid,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取干补标识 /// /// 产品条码ram> /// int public static int GetSpecialRepairflagByBarcode(string barcode, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select SpecialRepairflag from tp_pm_inproduction where barcode=:barcode union select SpecialRepairflag from tp_pm_inproductiontrash where barcode=:barcode union select SpecialRepairflag from tp_pm_finishedproduct where barcode=:barcode "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { return Convert.ToInt32(ds.Tables[0].Rows[0]["SpecialRepairflag"]); } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取窑车对应产品列表 /// 窑车ID /// /// DataSet public static DataSet GetKilnCarGoodsByKilnCarID(int KilnCarID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select distinct TP_PM_KilnCarGoods.Barcode, TP_MST_Goods.Goodsid, TP_MST_Goods.Goodscode, TP_MST_Goods.Goodsname, TP_PM_KilnCarGoods.Userid, TP_PM_KilnCarGoods.Usercode, TP_PM_KilnCarGoods.Username, TP_MST_KilnCar.Kilncarname, TP_MST_KilnCar.Kilncarcode, TP_MST_Kiln.KilnName, TP_MST_Kiln.KilnCode, TP_MST_KilnCar.Kilnid, TP_MST_KilnCar.KilnCarid, TP_PM_KilnCarGoods.KilnCarPosition, TP_MST_DataDictionary.Dictionaryvalue, TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode, TP_PM_GroutingDailyDetail.GroutingCount as GroutingNum, TP_PM_GroutingDailyDetail.GroutingMouldCode as MouldCode, (select max(tp_pm_inproduction.ispublicbody) from tp_pm_inproduction where tp_pm_inproduction.BarCode=TP_PM_KilnCarGoods.Barcode) as ispublicbody, TP_PM_GroutingDailyDetail.Groutingdate, tp_mst_logo.logoid, tp_mst_logo.logocode, tp_mst_logo.logoname from TP_PM_KilnCarGoods left join TP_MST_Goods on TP_PM_KilnCarGoods.Goodsid=TP_MST_Goods.Goodsid left join TP_MST_KilnCar on TP_PM_KilnCarGoods.Kilncarid=TP_MST_KilnCar.Kilncarid left join TP_MST_Kiln on TP_MST_KilnCar.Kilnid=TP_MST_Kiln.Kilnid left join TP_MST_DataDictionary on TP_PM_KilnCarGoods.KilnCarPosition=TP_MST_DataDictionary.Dictionaryid left join TP_PM_GroutingDailyDetail on TP_PM_KilnCarGoods.Barcode=TP_PM_GroutingDailyDetail.BarCode left join tp_mst_logo on TP_PM_GroutingDailyDetail.logoid=tp_mst_logo.logoid where TP_PM_KilnCarGoods.KilnCarID=:KilnCarID "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":KilnCarID",OracleDbType.Int32, KilnCarID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取未在生产线上报损的注浆信息 /// 产品条码 /// /// DataSet public static DataSet GetBarCodeInGroutingDailyDetail(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // 首先查看条码是否有效 string sqlString = @"select TP_PM_GroutingDailyDetail.GoodsID,TP_PM_GroutingDailyDetail.GoodsCode,TP_PM_GroutingDailyDetail.GoodsName ,TP_PM_GroutingDailyDetail.GroutingDailyID,TP_PM_GroutingDailyDetail.GroutingDailyDetailID ,TP_PM_GroutingDailyDetail.GroutingDate,TP_PM_GroutingDailyDetail.GroutingLineID,TP_PM_GroutingDailyDetail.GroutingLineCode ,TP_PM_GroutingDailyDetail.GroutingLineName ,TP_PM_GroutingDailyDetail.GroutingLineDetailID,TP_PM_GroutingDailyDetail.GroutingMouldCode,TP_PM_GroutingDailyDetail.MouldCode ,TP_PM_GroutingDailyDetail.UserID as GroutingUserID ,TP_PM_GroutingDailyDetail.UserCode as GroutingUserCode ,TP_PM_GroutingDailyDetail.GroutingCount as GroutingNum ,0 as IsPublicBody ,0 as IsReFire ,TP_PC_GroutingLine.GMouldTypeID ,TP_PM_GroutingDailyDetail.SpecialRepairFlag from TP_PM_GroutingDailyDetail left join TP_PC_GroutingLine on TP_PM_GroutingDailyDetail.GroutingLineID=TP_PC_GroutingLine.GroutingLineID where TP_PM_GroutingDailyDetail.barcode=:barcode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,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做比较 /// /// 产品条码 /// int public static int GetCompleteProductionDataID(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select ProductionDataID from TP_PM_InProduction where TP_PM_InProduction.BarCode=:BarCode union select ProductionDataID from Tp_Pm_Inproductiontrash where Tp_Pm_Inproductiontrash.BarCode=:BarCode "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { return Convert.ToInt32(ds.Tables[0].Rows[0]["ProductionDataID"]); } else { return 0; } } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取在产产品的信息标识列表 /// /// 产品条码 /// int public static DataSet GetInProductionDataList(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select BarCode, FlowProcedureID, FlowProcedureTime, ProcedureID, ProcedureTime, ProductionDataID, IsReFire, SpecialRepairFlag from TP_PM_InProduction where TP_PM_InProduction.BarCode=:BarCode "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 成检时获取此条码是否报损 /// 产品条码 /// /// int public static int CheckScrapProduct(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select auditstatus from tp_pm_scrapproduct where auditstatus in(0,1) and valueflag=1 and goodsleveltypeid=8 and barcode=:barcode and recyclingflag=0"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { return Convert.ToInt32(ds.Tables[0].Rows[0]["auditstatus"]); } return -100; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取产品完成工序的ID(PDA) /// /// 产品条码 /// int public static DataSet GetCompleteProcedureIDPDA(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select FlowProcedureID as CompleteProcedureID, ProductionDataID, ProcedureID, OPTimeStamp, to_char(OPTimeStamp,'DD-MM-YYHH12.MI.SS.FFAM') as ConvertOPTimeStamp from TP_PM_InProduction where TP_PM_InProduction.BarCode=:BarCode union select FlowProcedureID as CompleteProcedureID, ProductionDataID, ProcedureID, OPTimeStamp, to_char(OPTimeStamp,'DD-MM-YYHH12.MI.SS.FFAM') as ConvertOPTimeStamp from Tp_Pm_Inproductiontrash where Tp_Pm_Inproductiontrash.BarCode=:BarCode "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 成检时获取条码的产品信息（成检(正品)--报损-->干补-->成检） /// /// 产品条码 /// int public static DataSet GetGoodsInfoBybarcode(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select GoodsID,GoodsCode,GoodsName, ( select TP_PM_InProduction.SpecialRepairFlag from TP_PM_InProduction where TP_PM_InProduction.BarCode=:BarCode ) SpecialRepairFlag, ( select TP_PM_InProduction.IsReFire from TP_PM_InProduction where TP_PM_InProduction.BarCode=:BarCode ) IsReFire, tp_mst_logo.logoid, tp_mst_logo.logocode, tp_mst_logo.logoname, TP_PM_GroutingDailyDetail.UserCode, TP_PM_GroutingDailyDetail.GroutingMouldCode as MouldCode, TP_PM_GroutingDailyDetail.GroutingCount, TP_PM_GroutingDailyDetail.GroutingDate from TP_PM_GroutingDailyDetail left join tp_mst_logo on TP_PM_GroutingDailyDetail.logoid=tp_mst_logo.logoid where TP_PM_GroutingDailyDetail.BarCode=:BarCode "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 生成盘点明细 /// /// 工序ID集 /// 用户基本信息 /// public static DataSet GetInCheckedDetail(string ProcedureIDS, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_PM_InProduction.BarCode, TP_PM_InProduction.ProductionLineID, TP_PM_InProduction.ProductionLineCode, TP_PM_InProduction.ProductionLineName, TP_PM_InProduction.FlowProcedureID, TP_PM_InProduction.ProcedureID, TP_PM_InProduction.ProcedureModel, TP_PM_InProduction.ModelType, TP_PM_InProduction.GoodsID, TP_PM_InProduction.GoodsCode, TP_PM_InProduction.GoodsName, TP_PM_InProduction.GroutingDate, TP_PM_InProduction.GroutingLineCode, TP_PM_InProduction.GroutingLineName, TP_PM_InProduction.GroutingMouldCode, TP_PM_InProduction.GroutingUserCode, TP_PM_InProduction.GroutingNum, TP_PM_InProduction.KilnCode, TP_PM_InProduction.KilnName, TP_PM_InProduction.KilnCarCode, TP_PM_InProduction.KilnCarName, TP_PM_InProduction.IsPublicBody, TP_PM_InProduction.IsReFire, TP_PM_InProduction.SpecialRepairFlag, TP_PC_Procedure.ProcedureName from TP_PM_InProduction left join TP_PC_Procedure on TP_PM_InProduction.FlowProcedureID=TP_PC_Procedure.ProcedureID where TP_PM_InProduction.accountid=:accountid "; List parameters = new List(); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input)); // 工序IDS if (!string.IsNullOrEmpty(ProcedureIDS)) { sqlString += "AND instr(','||:ProcedureIDS||',',','||TP_PM_InProduction.FlowProcedureID||',')>0 "; parameters.Add(new OracleParameter(":ProcedureIDS", OracleDbType.NVarchar2, ProcedureIDS, ParameterDirection.Input)); } DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray()); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取盘点单列表 /// /// /// public static DataSet GetInCheckedList(SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select InCheckedID,InCheckedNo,AccountDate from TP_PM_InChecked where accountid=:accountid and ValueFlag=1"; List parameters = new List(); parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input)); DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray()); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取盘点单工号列表 /// /// /// public static DataSet GetInCheckedUserList(int InCheckedID, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select tp_pm_incheckeduser.InCheckedID,tp_pm_incheckeduser.UserID,tp_pm_incheckeduser.UserCode, tp_mst_user.UserName from tp_pm_incheckeduser left join tp_mst_user on tp_pm_incheckeduser.userid=tp_mst_user.userid where tp_pm_incheckeduser.InCheckedID=:InCheckedID"; List parameters = new List(); parameters.Add(new OracleParameter(":InCheckedID", OracleDbType.Int32, InCheckedID, ParameterDirection.Input)); DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray()); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取主表盘点信息 /// /// 盘点类 /// 用户基本信息 /// public static DataSet GetAllInChecked(InCheckedEntity entity, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); OracleParameter[] paras = new OracleParameter[]{ new OracleParameter("in_inCheckedNo",OracleDbType.NVarchar2, entity.InCheckedNo,ParameterDirection.Input), new OracleParameter("in_inCheckedName",OracleDbType.NVarchar2, entity.InCheckedName,ParameterDirection.Input), new OracleParameter("in_remarks",OracleDbType.NVarchar2, entity.Remarks,ParameterDirection.Input), new OracleParameter("in_begindate",OracleDbType.Date, entity.BeginDate,ParameterDirection.Input), new OracleParameter("in_enddate",OracleDbType.Date,entity.EndDate,ParameterDirection.Input), new OracleParameter("out_result",OracleDbType.RefCursor, ParameterDirection.Output), new OracleParameter("in_accountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter("in_userID",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; DataSet returnDataSet = oracleConn.ExecStoredProcedure("PRO_PM_GetInChecked", paras); return returnDataSet; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 获取盘点单名细信息 /// /// 盘点单号 /// public static DataSet GetAllInCheckedDetail(int inCheckedID) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); string sql = @"select TP_PM_InCheckedDetail.InCheckedID, 0 as Sel, TP_PM_InCheckedDetail.BarCode, TP_PM_InCheckedDetail.InCheckedNo, TP_PM_InCheckedDetail.ProductionLineCode, TP_PM_InCheckedDetail.ProductionLineName, TP_PC_Procedure.ProcedureName, decode(TP_PM_InCheckedDetail.ProcedureModel, '1', '计件模型' , '检验模型') as ProcedureModel, decode(TP_PM_InCheckedDetail.DefectFlag, '1', '无缺陷' , '有缺陷') as DefectFlag, TP_PM_InCheckedDetail.IsPublicBody, TP_PM_InCheckedDetail.IsReFire, TP_PM_InCheckedDetail.SpecialRepairFlag, TP_PM_InCheckedDetail.GoodsCode, TP_PM_InCheckedDetail.GoodsName, TP_PM_InCheckedDetail.GroutingDate, TP_PM_InCheckedDetail.GroutingLineCode, TP_PM_InCheckedDetail.GroutingLineName, TP_PM_InCheckedDetail.GroutingMouldCode, TP_PM_InCheckedDetail.GroutingUserCode, TP_PM_InCheckedDetail.GroutingNum, TP_PM_InCheckedDetail.KilnCode, TP_PM_InCheckedDetail.KilnName, TP_PM_InCheckedDetail.KilnCarCode, TP_PM_InCheckedDetail.KilnCarName, decode(TP_PM_InCheckedDetail.InCheckedFlag, '0', '未盘点' , '1','已盘点','盘盈') as InCheckedFlagName, TP_PM_InCheckedDetail.InCheckedFlag, TP_PM_InCheckedDetail.CheckedDate, Tp_mst_user.usercode CheckedUserCode, u.usercode, inpu.usercode cusercode, TP_PM_InCheckedDetail.ProcedureTime, TP_PM_InCheckedDetail.IsReworkFlag, TP_PM_InCheckedDetail.InScrapFlag, decode(sp.SCRAPPRODUCTID, null, 0, 1) SCRAPPRODUCTID, su.usercode susercode, spin.CREATETIME sdatetime, decode(spin.AUDITSTATUS,1, au.usercode , null) ausercode, spin.AUDITDATE adatetime,gt.goodstypename --,inpp.procedurename currentprocedurename --,inp.proceduretime currentproceduretime" + "\n" + " ,case when inp.barcode is not null then to_char(inpp.procedurename) " + " when sp.barcode is not null then to_char(glt.goodsleveltypename) " + " when fp.barcode is not null then '生产完成' else '' end currentprocedurename -- 当前工序\n" + " ,case when inp.barcode is not null then inp.PROCEDURETIME " + " when sp.barcode is not null then sp.auditdate " + " when fp.barcode is not null then fp.createtime else null end currentproceduretime -- 当前工序时间\n" + @"from TP_PM_InCheckedDetail left join tp_mst_goods g on TP_PM_InCheckedDetail.goodsid = g.goodsid left join tp_mst_goodstype gt on gt.goodstypeid = g.goodstypeid left join TP_PC_Procedure on TP_PM_InCheckedDetail.ProcedureID=TP_PC_Procedure.ProcedureID left join Tp_mst_user on TP_PM_InCheckedDetail.CheckedUserID=Tp_mst_user.userid left join Tp_mst_user u on TP_PM_InCheckedDetail.UserID=u.userid left join TP_PM_SCRAPPRODUCT spin -- on spin.barcode = TP_PM_InCheckedDetail.BarCode on spin.groutingdailydetailid = TP_PM_InCheckedDetail.groutingdailydetailid and spin.GOODSLEVELTYPEID = 8 and spin.AUDITSTATUS in (0,1) and spin.valueflag = '1' and spin.RECYCLINGFLAG = '0' left join Tp_mst_user su on su.userid = spin.CREATEUSERID left join Tp_mst_user au on au.userid = spin.AUDITOR LEFT JOIN Tp_Pm_Inproduction inp --ON inp.barcode = TP_PM_InCheckedDetail.Barcode ON inp.groutingdailydetailid = TP_PM_InCheckedDetail.groutingdailydetailid" + // 完成 groutingdailydetailid " LEFT JOIN tp_pm_finishedproduct fp \n" + //" ON inp.barcode is null and fp.barcode = TP_PM_InCheckedDetail.barcode\n" + " ON inp.barcode is null and fp.groutingdailydetailid = TP_PM_InCheckedDetail.groutingdailydetailid\n" + // 损坯 " LEFT JOIN tp_pm_scrapproduct sp \n" + //" ON inp.barcode is null and fp.barcode is null and sp.barcode = TP_PM_InCheckedDetail.barcode\n" + " ON inp.barcode is null and fp.barcode is null and sp.groutingdailydetailid = TP_PM_InCheckedDetail.groutingdailydetailid\n" + " AND sp.valueflag = '1'\n" + " AND sp.auditstatus = 1\n" + " AND sp.goodsleveltypeid <> 9\n" + " AND sp.RECYCLINGFLAG = '0'\n" + " LEFT JOIN tp_sys_goodsleveltype glt \n" + " ON glt.goodsleveltypeid = sp.goodsleveltypeid\n" + @"LEFT JOIN tp_pc_procedure inpp ON inpp.procedureid = inp.procedureid left join Tp_mst_user inpu on inp.UserID=inpu.userid where InCheckedID=:InCheckedID and TP_PM_InCheckedDetail.valueflag=1 and TP_PM_InCheckedDetail.InCheckedFlag = :InCheckedFlag "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":InCheckedFlag",OracleDbType.Int32,(int)Constant.InCheckedFlag.InCheckedNo,ParameterDirection.Input), new OracleParameter(":InCheckedID",OracleDbType.Int32,inCheckedID,ParameterDirection.Input), }; DataSet returnDataSet = oracleConn.GetSqlResultToDs(sql, paras); paras[0].Value = (int)Constant.InCheckedFlag.InCheckeded; returnDataSet.Tables.Add(oracleConn.GetSqlResultToDt(sql, paras)); paras[0].Value = (int)Constant.InCheckedFlag.InCheckedWin; returnDataSet.Tables.Add(oracleConn.GetSqlResultToDt(sql, paras)); sql = @"select tp_pm_incheckeduser.InCheckedID,tp_pm_incheckeduser.UserID,tp_pm_incheckeduser.UserCode, tp_mst_user.UserName from tp_pm_incheckeduser left join tp_mst_user on tp_pm_incheckeduser.userid=tp_mst_user.userid where tp_pm_incheckeduser.InCheckedID=:InCheckedID"; paras = new OracleParameter[]{ new OracleParameter(":InCheckedID",OracleDbType.Int32,inCheckedID,ParameterDirection.Input), }; DataTable userTable = oracleConn.GetSqlResultToDt(sql, paras); userTable.TableName = "UserTable"; returnDataSet.Tables.Add(userTable); return returnDataSet; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 获取盘点单名细信息 /// /// 盘点单号 /// public static DataSet GetGroutingInfoBybarcode(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); // Flowprocedureid 换成 procedureid,干补特殊，会不一样，别的都一样 string sql = @"select gdd.barcode, gdd.GoodsCode, gdd.UserCode, gdd.GroutingDate, gdd.GroutingMouldCode, logo.logoname -- 商标 --,g.MaterialCode || logo.TagCode || gdd.OnlyCode OutOnlyCode -- 外包装唯一码 ,nvl(gdd.outlabelcode, g.MaterialCode || (select a.workcode from tp_mst_account a where a.accountid = gdd.accountid) || logo.TagCode || gdd.OnlyCode) OutOnlyCode ,tp_pc_procedure.procedurename from TP_PM_GroutingDailyDetail gdd INNER JOIN tp_mst_goods g ON g.goodsid = gdd.goodsid LEFT JOIN tp_mst_logo logo ON logo.logoid = gdd.logoid left join TP_PM_InProduction on gdd.barcode=TP_PM_InProduction.barcode left join tp_pc_procedure on TP_PM_InProduction.procedureid=tp_pc_procedure.procedureid where gdd.barcode=FUN_CMN_GetBarCode(:barcode,null,:accountid) and gdd.valueflag=1 and gdd.scrapflag=0 "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2,barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), }; DataSet returnDataSet = oracleConn.GetSqlResultToDs(sql, paras); return returnDataSet; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } /// /// 获取盘点单名细信息 /// /// 盘点单号 /// public static DataSet GetFinishedProductGroutingInfoBybarcode(string barcode, SUserInfo sUserInfo) { IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { oracleConn.Open(); string sql = @"select gdd.barcode, gdd.GoodsCode, gdd.UserCode, gdd.GroutingDate, gdd.GroutingMouldCode, logo.logoname -- 商标 --,g.MaterialCode || logo.TagCode || gdd.OnlyCode OutOnlyCode -- 外包装唯一码 ,nvl(gdd.outlabelcode, g.MaterialCode || (select a.workcode from tp_mst_account a where a.accountid = gdd.accountid) || logo.TagCode || gdd.OnlyCode) OutOnlyCode ,tp_pc_procedure.procedurename from TP_PM_GroutingDailyDetail gdd INNER JOIN tp_mst_goods g ON g.goodsid = gdd.goodsid LEFT JOIN tp_mst_logo logo ON logo.logoid = gdd.logoid left join TP_PM_InProduction on gdd.barcode=TP_PM_InProduction.barcode left join tp_pc_procedure on TP_PM_InProduction.Flowprocedureid=tp_pc_procedure.procedureid where gdd.barcode=FUN_CMN_GetBarCode(:barcode,null,:accountid) and gdd.valueflag=1 and gdd.scrapflag=0 "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2,barcode,ParameterDirection.Input), new OracleParameter(":accountid",OracleDbType.Int32,sUserInfo.AccountID,ParameterDirection.Input), }; DataSet returnDataSet = oracleConn.GetSqlResultToDs(sql, paras); return returnDataSet; } catch (Exception ex) { throw ex; } finally { if (oracleConn.ConnState == ConnectionState.Open) { oracleConn.Close(); } } } #region 清除在产残留数据 /// /// 取得PM2108画面（在产品明细表）的查询数据 /// /// 登录用户信息 /// 查询条件 /// 查询结果 public static ServiceResultEntity GetPM2108Data(SUserInfo user, RPT010401_SE se) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); StringBuilder sql = new StringBuilder(PMModuleLogic.GetRPT010401SSQL()); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); sql.Append(" and TP_PM_InProduction.modeltype not in (1,2,3) "); if (se != null) { //生产线IDS if (!string.IsNullOrEmpty(se.ProductionLineIDS)) { sql.Append(" AND instr(','||:ProductionLineIDS||',',','||TP_PM_InProduction.ProductionLineID||',')>0 "); parameters.Add(new OracleParameter(":ProductionLineIDS", OracleDbType.NVarchar2, se.ProductionLineIDS, ParameterDirection.Input)); } //完成工序IDS if (!string.IsNullOrEmpty(se.ProcedureIDS)) { sql.Append(" AND instr(','||:ProcedureIDS||',',','||TP_PM_InProduction.ProcedureID||',')>0 "); parameters.Add(new OracleParameter(":ProcedureIDS", OracleDbType.NVarchar2, se.ProcedureIDS, ParameterDirection.Input)); } // 生产时间--开始 if (se.UpdateTimeStart.HasValue) { sql.Append(" AND TP_PM_InProduction.Proceduretime >= :UpdateTimeStart "); parameters.Add(new OracleParameter(":UpdateTimeStart", OracleDbType.Date, se.UpdateTimeStart.Value, ParameterDirection.Input)); } // 生产时间--结束 if (se.UpdateTimeEnd.HasValue) { sql.Append(" AND TP_PM_InProduction.Proceduretime <= :UpdateTimeEnd "); parameters.Add(new OracleParameter(":UpdateTimeEnd", OracleDbType.Date, se.UpdateTimeEnd.Value, ParameterDirection.Input)); } //产品条码 if (!string.IsNullOrEmpty(se.Barcode)) { sql.Append(" AND instr(TP_PM_InProduction.barcode,:barcode)>0 "); parameters.Add(new OracleParameter(":barcode", OracleDbType.NVarchar2, se.Barcode, ParameterDirection.Input)); } sql.Append(" AND TP_PM_InProduction.inscrapflag=0 "); } sql.Append(" order by TP_PM_InProduction.UpdateTime desc"); DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); if (data == null || data.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data = new DataSet(); sre.Data.Tables.Add(data); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 获取RPT010401画面（在产品明细表）的查询sql /// /// sql private static string GetRPT010401SSQL() { string selSql = "SELECT 0 as Sel," + " TP_PM_InProduction.BarCode, " + " TP_PM_InProduction.GoodsID,TP_PM_InProduction.GoodsCode, " + " TP_PM_InProduction.GoodsName, " + " TP_PM_InProduction.Userid,A.USERNAME as UserName,A.USERCode as UserCode, " + " TP_PM_InProduction.ProductionLineID, " + " TP_PM_InProduction.ProductionLineCode, " + " TP_PM_InProduction.ProductionLineName, " + " TP_PM_InProduction.ModelType, " + " decode(TP_PM_InProduction.inscrapflag , '1', '待审核的报废品' , '正常') as inscrapflag, " + //" decode(TP_PM_InProduction.IsPublicBody, '1', '是' , '否') as IsPublicBody, " + //" decode(TP_PM_InProduction.IsReFire, '1', '是' , '否') as IsReFire, " + " TP_PM_InProduction.IsPublicBody, " + " TP_PM_InProduction.IsReFire, " + " decode(TP_PM_InProduction.CanManyTimes, '1', '能' , '不能') as CanManyTimes, " + " decode(TP_PM_InProduction.ProcedureModel, '1', '计件模型' , '检验模型') as ProcedureModel, " + " TP_PM_InProduction.GroutingDailyID, " + " TP_PM_InProduction.GroutingDailyDetailID, " + " TP_PM_InProduction.GroutingDate, " + " TP_PM_InProduction.GroutingLineID, " + " TP_PM_InProduction.GroutingLineCode, " + " TP_PM_InProduction.GroutingLineName, " + " TP_PM_InProduction.ProcedureID CompleteProcedureID, " + " D.ProcedureName as CompleteProcedureName, " + " TP_PM_InProduction.GMouldTypeID, " + " TP_MST_GMouldType.GMOULDTYPENAME as GMouldTypeName, " + " TP_PM_InProduction.GroutingLineDetailID, " + " TP_PM_InProduction.GroutingMouldCode, " + " TP_PM_InProduction.SPECIALREPAIRFLAG, " + " TP_PM_InProduction.GROUTINGUSERCODE, " + " TP_PM_GroutingDailyDetail.GROUTINGCOUNT, " + " TP_PM_InProduction.Remarks, " + " TP_PM_InProduction.GoodsLevelID,TP_MST_GoodsLevel.GOODSLEVELNAME, " + " TP_PM_InProduction.GoodsLevelTypeID,TP_SYS_GoodsLevelType.GOODSLEVELTYPENAME, " + " TP_PM_InProduction.AccountID,TP_PM_InProduction.ValueFlag, " + " TP_PM_InProduction.CreateTime,TP_PM_InProduction.CreateUserID,B.USERNAME as CreateUserName, " + " TP_PM_InProduction.PROCEDURETIME AS UpdateTime,TP_PM_InProduction.UpdateUserID,C.USERNAME as UpdateUserName, " + " TP_PM_InProduction.IsReworkFlag " + "FROM TP_PM_InProduction " + " inner join TP_MST_User A on A.UserID=TP_PM_InProduction.Userid " + " inner join TP_MST_User B on B.UserID=TP_PM_InProduction.CreateUserID " + " inner join TP_MST_User C on C.UserID=TP_PM_InProduction.UpdateUserID " + " inner join TP_PC_Procedure D on D.PROCEDUREID=TP_PM_InProduction.ProcedureID " + " inner join TP_MST_GMouldType on TP_MST_GMouldType.GMouldTypeID=TP_PM_InProduction.GMouldTypeID " + " inner join TP_SYS_ProcedureModelType on TP_SYS_ProcedureModelType.ProcedureModelTypeID=TP_PM_InProduction.ModelType " + " inner join TP_PM_GroutingDaily on TP_PM_GroutingDaily.GroutingDailyID=TP_PM_InProduction.GroutingDailyID " + " inner join TP_PM_GroutingDailyDetail on TP_PM_GroutingDailyDetail.Groutingdailydetailid=TP_PM_InProduction.GroutingDailyDetailID " + " inner join TP_PC_GroutingLineDetail on TP_PC_GroutingLineDetail.Groutinglinedetailid=TP_PM_InProduction.GroutingLineDetailID " + " inner join TP_MST_Account on TP_MST_Account.Accountid=TP_PM_InProduction.Accountid " + " left join TP_MST_GoodsLevel on TP_MST_GoodsLevel.GoodsLevelID=TP_PM_InProduction.GoodsLevelID " + " left join TP_SYS_GoodsLevelType on TP_SYS_GoodsLevelType.GoodsLevelTypeID=TP_PM_InProduction.GoodsLevelTypeID " + " Where 1=1 and TP_PM_InProduction.ValueFlag = 1 " + " and TP_PM_InProduction.AccountID=:AccountID "; return selSql; } #endregion #region 清除在产回收站数据 public static ServiceResultEntity GetPM2110Data(SUserInfo user, FPM2110_SE se) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); StringBuilder sql = new StringBuilder(PMModuleLogic.GetPM2110SQL()); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); if (se != null) { //生产线IDS if (!string.IsNullOrEmpty(se.ProductionLineIDS)) { sql.Append(" AND instr(','||:ProductionLineIDS||',',','||Tp_Pm_Inproductiontrash.ProductionLineID||',')>0 "); parameters.Add(new OracleParameter(":ProductionLineIDS", OracleDbType.NVarchar2, se.ProductionLineIDS, ParameterDirection.Input)); } //完成工序IDS if (!string.IsNullOrEmpty(se.ProcedureIDS)) { sql.Append(" AND instr(','||:ProcedureIDS||',',','||Tp_Pm_Inproductiontrash.ProcedureID||',')>0 "); parameters.Add(new OracleParameter(":ProcedureIDS", OracleDbType.NVarchar2, se.ProcedureIDS, ParameterDirection.Input)); } // 生产时间--开始 if (se.UpdateTimeStart.HasValue) { sql.Append(" AND Tp_Pm_Inproductiontrash.Proceduretime >= :UpdateTimeStart "); parameters.Add(new OracleParameter(":UpdateTimeStart", OracleDbType.Date, se.UpdateTimeStart.Value, ParameterDirection.Input)); } // 生产时间--结束 if (se.UpdateTimeEnd.HasValue) { sql.Append(" AND Tp_Pm_Inproductiontrash.Proceduretime <= :UpdateTimeEnd "); parameters.Add(new OracleParameter(":UpdateTimeEnd", OracleDbType.Date, se.UpdateTimeEnd.Value, ParameterDirection.Input)); } //报废日期开始 if (se.ScrapDataStart.HasValue) { sql.Append(" AND TP_PM_SCRAPPRODUCT.SCRAPDATE >= trunc(:ScrapDateStart) "); parameters.Add(new OracleParameter(":ScrapDateStart", OracleDbType.Date, se.ScrapDataStart.Value, ParameterDirection.Input)); } //报废日期结束 if (se.ScrapDataEnd.HasValue) { sql.Append(" AND TP_PM_SCRAPPRODUCT.SCRAPDATE <= trunc(:ScrapDateEnd)"); parameters.Add(new OracleParameter(":ScrapDateEnd", OracleDbType.Date, se.ScrapDataEnd.Value, ParameterDirection.Input)); } //产品条码 if (!string.IsNullOrEmpty(se.Barcode)) { sql.Append(" AND instr(Tp_Pm_Inproductiontrash.barcode,:barcode)>0 "); parameters.Add(new OracleParameter(":barcode", OracleDbType.NVarchar2, se.Barcode, ParameterDirection.Input)); } // 产品分级 sql.Append(" AND TP_PM_SCRAPPRODUCT.GOODSLEVELTYPEID = :GooddLevelTypeID "); parameters.Add(new OracleParameter(":GooddLevelTypeID", OracleDbType.Int32, se.GooddLevelTypeID, ParameterDirection.Input)); } sql.Append(" order by Tp_Pm_Inproductiontrash.UpdateTime desc"); DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); if (data == null || data.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data = new DataSet(); sre.Data.Tables.Add(data); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 获取RPT010401画面（在产品明细表）的查询sql /// /// sql private static string GetPM2110SQL() { string selSql = @"SELECT 0 as Sel, Tp_Pm_Inproductiontrash.BarCode, Tp_Pm_Inproductiontrash.GoodsID, Tp_Pm_Inproductiontrash.GoodsCode, Tp_Pm_Inproductiontrash.GoodsName, Tp_Pm_Inproductiontrash.Userid, A.USERNAME as UserName, A.USERCode as UserCode, Tp_Pm_Inproductiontrash.ProductionLineID, Tp_Pm_Inproductiontrash.ProductionLineCode, Tp_Pm_Inproductiontrash.ProductionLineName, Tp_Pm_Inproductiontrash.ModelType, --decode(Tp_Pm_Inproductiontrash.IsPublicBody, '1', '是', '否') as IsPublicBody, --decode(Tp_Pm_Inproductiontrash.IsReFire, '1', '是', '否') as IsReFire, Tp_Pm_Inproductiontrash.IsPublicBody, Tp_Pm_Inproductiontrash.IsReFire, decode(Tp_Pm_Inproductiontrash.CanManyTimes, '1', '能', '不能') as CanManyTimes, decode(Tp_Pm_Inproductiontrash.ProcedureModel, '1', '计件模型', '检验模型') as ProcedureModel, Tp_Pm_Inproductiontrash.GroutingDailyID, Tp_Pm_Inproductiontrash.GroutingDailyDetailID, Tp_Pm_Inproductiontrash.GroutingDate, Tp_Pm_Inproductiontrash.GroutingLineID, Tp_Pm_Inproductiontrash.GroutingLineCode, Tp_Pm_Inproductiontrash.GroutingLineName, Tp_Pm_Inproductiontrash.ProcedureID CompleteProcedureID, D.ProcedureName as CompleteProcedureName, Tp_Pm_Inproductiontrash.GMouldTypeID, TP_MST_GMouldType.GMOULDTYPENAME as GMouldTypeName, Tp_Pm_Inproductiontrash.GroutingLineDetailID, Tp_Pm_Inproductiontrash.GroutingMouldCode, Tp_Pm_Inproductiontrash.SPECIALREPAIRFLAG, Tp_Pm_Inproductiontrash.GROUTINGUSERCODE, TP_PM_GroutingDailyDetail.GROUTINGCOUNT, Tp_Pm_Inproductiontrash.Remarks, Tp_Pm_Inproductiontrash.GoodsLevelID, TP_MST_GoodsLevel.GOODSLEVELNAME, Tp_Pm_Inproductiontrash.GoodsLevelTypeID, TP_SYS_GoodsLevelType.GOODSLEVELTYPENAME, Tp_Pm_Inproductiontrash.AccountID, Tp_Pm_Inproductiontrash.ValueFlag, Tp_Pm_Inproductiontrash.CreateTime, Tp_Pm_Inproductiontrash.CreateUserID, B.USERNAME as CreateUserName, Tp_Pm_Inproductiontrash.PROCEDURETIME AS UpdateTime, Tp_Pm_Inproductiontrash.UpdateUserID, C.USERNAME as UpdateUserName FROM Tp_Pm_Inproductiontrash inner join TP_MST_User A on A.UserID = Tp_Pm_Inproductiontrash.Userid inner join TP_MST_User B on B.UserID = Tp_Pm_Inproductiontrash.CreateUserID inner join TP_MST_User C on C.UserID = Tp_Pm_Inproductiontrash.UpdateUserID inner join TP_PC_Procedure D on D.PROCEDUREID = Tp_Pm_Inproductiontrash.ProcedureID inner join TP_MST_GMouldType on TP_MST_GMouldType.GMouldTypeID = Tp_Pm_Inproductiontrash.GMouldTypeID inner join TP_PM_GroutingDaily on TP_PM_GroutingDaily.GroutingDailyID = Tp_Pm_Inproductiontrash.GroutingDailyID inner join TP_PM_GroutingDailyDetail on TP_PM_GroutingDailyDetail.Groutingdailydetailid = Tp_Pm_Inproductiontrash.GroutingDailyDetailID inner join TP_PC_GroutingLineDetail on TP_PC_GroutingLineDetail.Groutinglinedetailid = Tp_Pm_Inproductiontrash.GroutingLineDetailID inner join TP_MST_Account on TP_MST_Account.Accountid = Tp_Pm_Inproductiontrash.Accountid left join TP_PM_SCRAPPRODUCT on TP_PM_SCRAPPRODUCT.barcode=Tp_Pm_Inproductiontrash.barcode left join TP_MST_GoodsLevel on TP_MST_GoodsLevel.GoodsLevelID = TP_PM_SCRAPPRODUCT.GoodsLevelID left join TP_SYS_GoodsLevelType on TP_SYS_GoodsLevelType.GoodsLevelTypeID = TP_PM_SCRAPPRODUCT.GoodsLevelTypeID Where 1 = 1 and Tp_Pm_Inproductiontrash.ValueFlag = 1 and Tp_Pm_Inproductiontrash.AccountID = :AccountID"; return selSql; } #endregion #region 清除在产临时表数据 public static ServiceResultEntity GetPM2112Data(SUserInfo user, FPM2112_SE se) { IDBConnection conn = null; try { conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); List parameters = new List(); StringBuilder sql = new StringBuilder(PMModuleLogic.GetPM2112SQL()); parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, user.AccountID, ParameterDirection.Input)); if (se != null) { //生产线IDS if (!string.IsNullOrEmpty(se.ProductionLineIDS)) { sql.Append(" AND instr(','||:ProductionLineIDS||',',','||Tp_Pm_Inproduction_Tmp.ProductionLineID||',')>0 "); parameters.Add(new OracleParameter(":ProductionLineIDS", OracleDbType.NVarchar2, se.ProductionLineIDS, ParameterDirection.Input)); } //完成工序IDS if (!string.IsNullOrEmpty(se.ProcedureIDS)) { sql.Append(" AND instr(','||:ProcedureIDS||',',','||Tp_Pm_Inproduction_Tmp.ProcedureID||',')>0 "); parameters.Add(new OracleParameter(":ProcedureIDS", OracleDbType.NVarchar2, se.ProcedureIDS, ParameterDirection.Input)); } // 生产时间--开始 if (se.UpdateTimeStart.HasValue) { sql.Append(" AND Tp_Pm_Inproduction_Tmp.Proceduretime >= :UpdateTimeStart "); parameters.Add(new OracleParameter(":UpdateTimeStart", OracleDbType.Date, se.UpdateTimeStart.Value, ParameterDirection.Input)); } // 生产时间--结束 if (se.UpdateTimeEnd.HasValue) { sql.Append(" AND Tp_Pm_Inproduction_Tmp.Proceduretime <= :UpdateTimeEnd "); parameters.Add(new OracleParameter(":UpdateTimeEnd", OracleDbType.Date, se.UpdateTimeEnd.Value, ParameterDirection.Input)); } //产品条码 if (!string.IsNullOrEmpty(se.Barcode)) { sql.Append(" AND instr(Tp_Pm_Inproduction_Tmp.barcode,:barcode)>0 "); parameters.Add(new OracleParameter(":barcode", OracleDbType.NVarchar2, se.Barcode, ParameterDirection.Input)); } if (se.TrashFlag == 1) { //报废日期开始 if (se.ScrapDataStart.HasValue) { sql.Append(" AND TP_PM_SCRAPPRODUCT.SCRAPDATE >= trunc(:ScrapDateStart) "); parameters.Add(new OracleParameter(":ScrapDateStart", OracleDbType.Date, se.ScrapDataStart.Value, ParameterDirection.Input)); } //报废日期结束 if (se.ScrapDataEnd.HasValue) { sql.Append(" AND TP_PM_SCRAPPRODUCT.SCRAPDATE <= trunc(:ScrapDateEnd)"); parameters.Add(new OracleParameter(":ScrapDateEnd", OracleDbType.Date, se.ScrapDataEnd.Value, ParameterDirection.Input)); } } // 清除时间 if (se.DeletedTimeStart.HasValue) { sql.Append(" AND Tp_Pm_Inproduction_Tmp.deletedtime >= :deletedtime "); parameters.Add(new OracleParameter(":deletedtime", OracleDbType.Date, se.DeletedTimeStart.Value, ParameterDirection.Input)); } // 清除时间--结束 if (se.DeletedTimeEnd.HasValue) { sql.Append(" AND Tp_Pm_Inproduction_Tmp.deletedtime <= :deletedtimeEnd "); parameters.Add(new OracleParameter(":deletedtimeEnd", OracleDbType.Date, se.DeletedTimeEnd.Value, ParameterDirection.Input)); } if (se.GooddLevelTypeID != -100) { // 产品分级 sql.Append(" AND TP_PM_SCRAPPRODUCT.GOODSLEVELTYPEID = :GooddLevelTypeID "); parameters.Add(new OracleParameter(":GooddLevelTypeID", OracleDbType.Int32, se.GooddLevelTypeID, ParameterDirection.Input)); } else { // 产品分级 //// sql.Append(" AND TP_PM_SCRAPPRODUCT.GOODSLEVELTYPEID is null "); } //数据来源 sql.Append(" AND Tp_Pm_Inproduction_Tmp.TrashFlag = :TrashFlag "); parameters.Add(new OracleParameter(":TrashFlag", OracleDbType.Int32, se.TrashFlag, ParameterDirection.Input)); } sql.Append(" order by Tp_Pm_Inproduction_Tmp.UpdateTime desc"); DataTable data = conn.GetSqlResultToDt(sql.ToString(), parameters.ToArray()); ServiceResultEntity sre = new ServiceResultEntity(); if (data == null || data.Rows.Count == 0) { sre.Status = Constant.ServiceResultStatus.NoSearchResults; return sre; } sre.Data = new DataSet(); sre.Data.Tables.Add(data); return sre; } catch (Exception ex) { throw ex; } finally { if (conn != null && conn.ConnState == ConnectionState.Open) { conn.Close(); } } } /// /// 获取RPT010401画面（在产品明细表）的查询sql /// /// sql private static string GetPM2112SQL() { string selSql = @"SELECT 0 as Sel, TP_PM_SCRAPPRODUCT.SCRAPDATE, Tp_Pm_Inproduction_Tmp.BarCode, Tp_Pm_Inproduction_Tmp.GoodsID, Tp_Pm_Inproduction_Tmp.GoodsCode, Tp_Pm_Inproduction_Tmp.GoodsName, Tp_Pm_Inproduction_Tmp.Userid, A.USERNAME as UserName, A.USERCode as UserCode, Tp_Pm_Inproduction_Tmp.ProductionLineID, Tp_Pm_Inproduction_Tmp.ProductionLineCode, Tp_Pm_Inproduction_Tmp.ProductionLineName, Tp_Pm_Inproduction_Tmp.ModelType, --decode(Tp_Pm_Inproduction_Tmp.IsPublicBody, '1', '是', '否') as IsPublicBody, --decode(Tp_Pm_Inproduction_Tmp.IsReFire, '1', '是', '否') as IsReFire, Tp_Pm_Inproduction_Tmp.IsPublicBody, Tp_Pm_Inproduction_Tmp.IsReFire, decode(Tp_Pm_Inproduction_Tmp.CanManyTimes, '1', '能', '不能') as CanManyTimes, decode(Tp_Pm_Inproduction_Tmp.ProcedureModel, '1', '计件模型', '检验模型') as ProcedureModel, Tp_Pm_Inproduction_Tmp.GroutingDailyID, Tp_Pm_Inproduction_Tmp.GroutingDailyDetailID, Tp_Pm_Inproduction_Tmp.GroutingDate, Tp_Pm_Inproduction_Tmp.GroutingLineID, Tp_Pm_Inproduction_Tmp.GroutingLineCode, Tp_Pm_Inproduction_Tmp.GroutingLineName, Tp_Pm_Inproduction_Tmp.ProcedureID CompleteProcedureID, D.ProcedureName as CompleteProcedureName, Tp_Pm_Inproduction_Tmp.GMouldTypeID, TP_MST_GMouldType.GMOULDTYPENAME as GMouldTypeName, Tp_Pm_Inproduction_Tmp.GroutingLineDetailID, Tp_Pm_Inproduction_Tmp.GroutingMouldCode, Tp_Pm_Inproduction_Tmp.SPECIALREPAIRFLAG, Tp_Pm_Inproduction_Tmp.GROUTINGUSERCODE, TP_PM_GroutingDailyDetail.GROUTINGCOUNT, Tp_Pm_Inproduction_Tmp.Remarks, Tp_Pm_Inproduction_Tmp.GoodsLevelID, TP_MST_GoodsLevel.GOODSLEVELNAME, Tp_Pm_Inproduction_Tmp.GoodsLevelTypeID, TP_SYS_GoodsLevelType.GOODSLEVELTYPENAME, Tp_Pm_Inproduction_Tmp.AccountID, Tp_Pm_Inproduction_Tmp.ValueFlag, Tp_Pm_Inproduction_Tmp.CreateTime, Tp_Pm_Inproduction_Tmp.CreateUserID, B.USERNAME as CreateUserName, Tp_Pm_Inproduction_Tmp.PROCEDURETIME AS UpdateTime, Tp_Pm_Inproduction_Tmp.UpdateUserID, C.USERNAME as UpdateUserName FROM Tp_Pm_Inproduction_Tmp inner join TP_MST_User A on A.UserID = Tp_Pm_Inproduction_Tmp.Userid inner join TP_MST_User B on B.UserID = Tp_Pm_Inproduction_Tmp.CreateUserID inner join TP_MST_User C on C.UserID = Tp_Pm_Inproduction_Tmp.UpdateUserID inner join TP_PC_Procedure D on D.PROCEDUREID = Tp_Pm_Inproduction_Tmp.ProcedureID inner join TP_MST_GMouldType on TP_MST_GMouldType.GMouldTypeID = Tp_Pm_Inproduction_Tmp.GMouldTypeID inner join TP_PM_GroutingDaily on TP_PM_GroutingDaily.GroutingDailyID = Tp_Pm_Inproduction_Tmp.GroutingDailyID inner join TP_PM_GroutingDailyDetail on TP_PM_GroutingDailyDetail.Groutingdailydetailid = Tp_Pm_Inproduction_Tmp.GroutingDailyDetailID inner join TP_PC_GroutingLineDetail on TP_PC_GroutingLineDetail.Groutinglinedetailid = Tp_Pm_Inproduction_Tmp.GroutingLineDetailID inner join TP_MST_Account on TP_MST_Account.Accountid = Tp_Pm_Inproduction_Tmp.Accountid left join TP_MST_GoodsLevel on TP_MST_GoodsLevel.GoodsLevelID = Tp_Pm_Inproduction_Tmp.GoodsLevelID left join TP_SYS_GoodsLevelType on TP_SYS_GoodsLevelType.GoodsLevelTypeID = Tp_Pm_Inproduction_Tmp.GoodsLevelTypeID left join TP_PM_SCRAPPRODUCT on TP_PM_SCRAPPRODUCT.barcode=Tp_Pm_Inproduction_Tmp.barcode and TP_PM_SCRAPPRODUCT.AUDITSTATUS=1 and TP_PM_SCRAPPRODUCT.RECYCLINGFLAG='0' and TP_PM_SCRAPPRODUCT.GOODSLEVELTYPEID=8 Where Tp_Pm_Inproduction_Tmp.AccountID = :AccountID"; return selSql; //and Tp_Pm_Inproduction_Tmp.ValueFlag = 1 } #endregion /// /// 工序是否有商标ID /// 工序ID /// /// int public static int GetLogoID(int ProcedureID, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select LogoID from tp_pc_procedure where accountid=:accountid and ProcedureID=:ProcedureID"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":accountid",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input), new OracleParameter(":ProcedureID",OracleDbType.Int32, ProcedureID,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["LogoID"] == DBNull.Value) { return 0; } else { return Convert.ToInt32(ds.Tables[0].Rows[0]["LogoID"]); } } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取条码商标 /// 产品条码 /// /// int public static DataSet GetBarCodeLogoID(string barcode, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); // 转换条码 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), }; barcode = con.GetSqlResultToStr(sqlString, paras1); sqlString = @"select tp_pm_groutingdailydetail.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"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); 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(); } } } /// /// 半检时,入窑前检验获取此条码是否报损为废品 /// 产品条码 /// /// int public static int CheckWasteScrapProduct(string barcode) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select auditstatus from tp_pm_scrapproduct where auditstatus in(0,1) and valueflag=1 and goodsleveltypeid=3 and barcode=:barcode"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":barcode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { return Convert.ToInt32(ds.Tables[0].Rows[0]["auditstatus"]); } return -100; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取盘点单明细 /// /// 工序ID /// DataSet public static DataSet GetUpdateInCheckedInfo(string incheckedno, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select tp_pm_inchecked.incheckedid from tp_pm_inchecked where tp_pm_inchecked.incheckedno=:incheckedno"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":incheckedno",OracleDbType.Varchar2, incheckedno,ParameterDirection.Input), }; DataSet dsReturn = null; DataSet ds = con.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { int incheckedid = Convert.ToInt32(ds.Tables[0].Rows[0]["incheckedid"]); sqlString = @"SELECT to_char(checked.UserCheckedCount) || '/' || to_char(checked.CheckedCount) CheckedCount ,to_char(checked.UserOverageCount) || '/' || to_char(checked.OverageCount) OverageCount ,to_char(checked.UserCheckedCount + checked.UserOverageCount) || '/' || to_char(checked.CheckedCount + checked.OverageCount) || '/' || to_char(TCount) TCount FROM (SELECT icd.incheckedid , SUM(CASE WHEN icd.checkeduserid = :userid AND icd.InCheckedFlag = '1' THEN 1 ELSE 0 END) UserCheckedCount , SUM(CASE WHEN icd.checkeduserid = :userid AND icd.InCheckedFlag = '2' THEN 1 ELSE 0 END) UserOverageCount , SUM(CASE WHEN icd.InCheckedFlag = '1' THEN 1 ELSE 0 END) CheckedCount , SUM(CASE WHEN icd.InCheckedFlag = '2' THEN 1 ELSE 0 END) OverageCount ,COUNT(icd.incheckedid) TCount FROM tp_pm_incheckeddetail icd WHERE icd.incheckedid = :incheckedid AND icd.valueflag = '1') checked"; paras = new OracleParameter[]{ new OracleParameter(":incheckedid",OracleDbType.Int32, incheckedid,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; ds = con.GetSqlResultToDs(sqlString, paras); ds.Tables[0].TableName = "Table1"; dsReturn = new DataSet(); dsReturn.Tables.Add(ds.Tables[0].Copy()); sqlString = @" SELECT icd.goodscode, COUNT(icd.goodscode) goodscount FROM tp_pm_incheckeddetail icd WHERE icd.incheckedid = :incheckedid AND icd.checkeduserid = :userid AND icd.InCheckedFlag = '1' AND icd.valueflag = '1' GROUP BY icd.goodscode"; paras = new OracleParameter[]{ new OracleParameter(":incheckedid",OracleDbType.Int32, incheckedid,ParameterDirection.Input), new OracleParameter(":userid",OracleDbType.Int32, sUserInfo.UserID,ParameterDirection.Input), }; ds = con.GetSqlResultToDs(sqlString, paras); ds.Tables[0].TableName = "Table2"; dsReturn.Tables.Add(ds.Tables[0].Copy()); } return dsReturn; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取半成品检验列表 /// 实体类 /// /// DataSet public static DataSet GetSemiCheck(SemiCheckEntity 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 SemiCheck.SemiCheckID, CProcedure.ProcedureName as CProcedureName, SemiCheck.ProcedureTime, SemiCheck.UserCode, GoodsType2.GoodsTypeName as LevelGoodsTypeName, GoodsType.GoodsTypeName, Goods.GoodsCode, SemiCheck.BarCode, -- 复检后不显示半检状态 SemiCheckType.SemiCheckTypeName, --decode(SemiCheck.ReSemiCheckType, 0, SemiCheckType.SemiCheckTypeName, '已复检') SemiCheckTypeName SemiCheck.SemiCheckUserCode, SemiCheck.SemiCheckTime, ReSemiCheckType.ReSemiCheckTypeName, SemiCheck.ReSemiCheckUserCode, SemiCheck.ReSemiCheckTime, decode(SemiCheck.BackOutFlag,'0','正常','撤销') as BackOutFlag, SemiCheck.BackOutUserCode, SemiCheck.BackOutTime, SemiCheck.GroutingUserCode, SemiCheck.GroutingDate, GroutingDailyDetail.DeliverTime, logo.logoName, RProcedure.ProcedureName as RProcedureName, SemiCheck.ReworkUserCode from TP_PM_SemiCheck SemiCheck left join TP_PC_Procedure CProcedure on SemiCheck.ProcedureID=CProcedure.ProcedureID left join TP_MST_Goods Goods on SemiCheck.GoodsID=Goods.GoodsID left join TP_MST_GoodsType GoodsType on Goods.GoodsTypeID=GoodsType.GoodsTypeID left join TP_MST_GoodsType GoodsType2 on GoodsType2.accountid=GoodsType.accountid and GoodsType2.GoodsTypecode=substr(GoodsType.GoodsTypecode,1,6) left join TP_SYS_SemiCheckType SemiCheckType on SemiCheck.SemiCheckType=SemiCheckType.SemiCheckTypeID left join TP_SYS_ReSemiCheckType ReSemiCheckType on SemiCheck.ReSemiCheckType=ReSemiCheckType.ReSemiCheckTypeID left join TP_PM_GroutingDailyDetail GroutingDailyDetail on GroutingDailyDetail.GroutingDailyDetailID=SemiCheck.GroutingDailyDetailID left join TP_MST_Logo logo on GroutingDailyDetail.LogoID=logo.LogoID left join TP_PC_Procedure RProcedure on SemiCheck.ReworkProcedureID=RProcedure.ProcedureID where SemiCheck.AccountID=:AccountID "; // 完成工序 if (!string.IsNullOrEmpty(entity.CProcedureIDS)) { sqlString = sqlString + " AND instr(','||:CProcedureIDS||',',','||SemiCheck.ProcedureID||',')>0 "; parameters.Add(new OracleParameter(":CProcedureIDS", OracleDbType.NVarchar2, entity.CProcedureIDS, ParameterDirection.Input)); } // 完成时间起始 if (entity.CDateTimeStart.HasValue) { sqlString = sqlString + " AND SemiCheck.ProcedureTime >= :CDateTimeStart "; parameters.Add(new OracleParameter(":CDateTimeStart", OracleDbType.Date, entity.CDateTimeStart.Value, ParameterDirection.Input)); } // 完成时间结束 if (entity.CDateTimeEnd.HasValue) { sqlString = sqlString + " AND SemiCheck.ProcedureTime <= :CDateTimeEnd "; parameters.Add(new OracleParameter(":CDateTimeEnd", OracleDbType.Date, entity.CDateTimeStart.Value, ParameterDirection.Input)); } // 完成工号 if (!string.IsNullOrEmpty(entity.CUserCode)) { sqlString = sqlString + " AND instr(SemiCheck.UserCode,:UserCode)>0"; parameters.Add(new OracleParameter(":UserCode", OracleDbType.NVarchar2, entity.CUserCode, 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.GoodsCode)) { sqlString = sqlString + " AND instr(Goods.GoodsCode,:GoodsCode)>0"; parameters.Add(new OracleParameter(":GoodsCode", OracleDbType.NVarchar2, entity.GoodsCode, ParameterDirection.Input)); } // 成型工号 if (!string.IsNullOrEmpty(entity.GroutingUserCode)) { sqlString = sqlString + " AND instr(SemiCheck.GroutingUserCode,:GroutingUserCode)>0"; parameters.Add(new OracleParameter(":GroutingUserCode", OracleDbType.NVarchar2, entity.GroutingUserCode, ParameterDirection.Input)); } // 注浆时间起始 if (entity.GroutingDateTimeStart.HasValue) { sqlString = sqlString + " AND SemiCheck.GroutingDate >= :GroutingDateTimeStart "; parameters.Add(new OracleParameter(":GroutingDateTimeStart", OracleDbType.Date, entity.GroutingDateTimeStart.Value, ParameterDirection.Input)); } // 注浆时间结束 if (entity.GroutingDateTimeEnd.HasValue) { sqlString = sqlString + " AND SemiCheck.GroutingDate <= :GroutingDateTimeEnd "; parameters.Add(new OracleParameter(":GroutingDateTimeEnd", OracleDbType.Date, entity.GroutingDateTimeEnd.Value, ParameterDirection.Input)); } // 交坯时间起始 if (entity.DeliveryDateTimeStart.HasValue) { sqlString = sqlString + " AND GroutingDailyDetail.DeliverTime >= :DeliveryDateTimeStart "; parameters.Add(new OracleParameter(":DeliveryDateTimeStart", OracleDbType.Date, entity.DeliveryDateTimeStart.Value, ParameterDirection.Input)); } // 交坯时间结束 if (entity.DeliveryDateTimeEnd.HasValue) { sqlString = sqlString + " AND GroutingDailyDetail.DeliverTime <= :DeliveryDateTimeEnd "; parameters.Add(new OracleParameter(":DeliveryDateTimeEnd", OracleDbType.Date, entity.DeliveryDateTimeEnd.Value, ParameterDirection.Input)); } // 半检状态 if (!string.IsNullOrEmpty(entity.SemiCheckType)) { sqlString = sqlString + " AND instr(','||:SemiCheckType||',',','||SemiCheck.SemiCheckType||',')>0 "; parameters.Add(new OracleParameter(":SemiCheckType", OracleDbType.NVarchar2, entity.SemiCheckType, ParameterDirection.Input)); } // 半检时间起始 if (entity.SemiCheckDateTimeStart.HasValue) { sqlString = sqlString + " AND SemiCheck.SemiCheckTime >= :SemiCheckDateTimeStart "; parameters.Add(new OracleParameter(":SemiCheckDateTimeStart", OracleDbType.Date, entity.SemiCheckDateTimeStart.Value, ParameterDirection.Input)); } // 半检时间结束 if (entity.SemiCheckDateTimeEnd.HasValue) { sqlString = sqlString + " AND SemiCheck.SemiCheckTime <= :SemiCheckDateTimeEnd "; parameters.Add(new OracleParameter(":SemiCheckDateTimeEnd", OracleDbType.Date, entity.SemiCheckDateTimeEnd.Value, ParameterDirection.Input)); } // 半检工号 if (!string.IsNullOrEmpty(entity.SemiCheckUserCode)) { sqlString = sqlString + " AND instr(SemiCheck.SemiCheckUserCode,:SemiCheckUserCode)>0"; parameters.Add(new OracleParameter(":SemiCheckUserCode", OracleDbType.NVarchar2, entity.SemiCheckUserCode, ParameterDirection.Input)); } // 返工工序 if (!string.IsNullOrEmpty(entity.RProcedureIDS)) { sqlString = sqlString + " AND instr(','||:RProcedureIDS||',',','||SemiCheck.ReworkProcedureID||',')>0 "; parameters.Add(new OracleParameter(":RProcedureIDS", OracleDbType.NVarchar2, entity.RProcedureIDS, ParameterDirection.Input)); } // 返工工号 if (!string.IsNullOrEmpty(entity.ReworkUserCode)) { sqlString = sqlString + " AND instr(SemiCheck.ReworkUserCode,:ReworkUserCode)>0"; parameters.Add(new OracleParameter(":ReworkUserCode", OracleDbType.NVarchar2, entity.ReworkUserCode, ParameterDirection.Input)); } // 复检状态 if (!string.IsNullOrEmpty(entity.ReSemiCheckType)) { sqlString = sqlString + " AND instr(','||:ReSemiCheckType||',',','||SemiCheck.ReSemiCheckType||',')>0 "; parameters.Add(new OracleParameter(":ReSemiCheckType", OracleDbType.NVarchar2, entity.ReSemiCheckType, ParameterDirection.Input)); } // 复检时间起始 if (entity.ReSemiCheckDateTimeStart.HasValue) { sqlString = sqlString + " AND SemiCheck.ReSemiCheckTime >= :ReSemiCheckDateTimeStart "; parameters.Add(new OracleParameter(":ReSemiCheckDateTimeStart", OracleDbType.Date, entity.ReSemiCheckDateTimeStart.Value, ParameterDirection.Input)); } // 复检时间结束 if (entity.ReSemiCheckDateTimeEnd.HasValue) { sqlString = sqlString + " AND SemiCheck.ReSemiCheckTime <= :ReSemiCheckDateTimeEnd "; parameters.Add(new OracleParameter(":ReSemiCheckDateTimeEnd", OracleDbType.Date, entity.ReSemiCheckDateTimeEnd.Value, ParameterDirection.Input)); } // 复检工号 if (!string.IsNullOrEmpty(entity.ReSemiCheckUserCode)) { sqlString = sqlString + " AND instr(SemiCheck.ReSemiCheckUserCode,:ReSemiCheckUserCode)>0"; parameters.Add(new OracleParameter(":ReSemiCheckUserCode", OracleDbType.NVarchar2, entity.ReSemiCheckUserCode, ParameterDirection.Input)); } // 撤销标识 if (!string.IsNullOrEmpty(entity.BackOutFlag)) { sqlString = sqlString + " AND instr(','||:BackOutFlag||',',','||SemiCheck.BackOutFlag||',')>0 "; parameters.Add(new OracleParameter(":BackOutFlag", OracleDbType.NVarchar2, entity.BackOutFlag, ParameterDirection.Input)); } // 撤销时间起始 if (entity.BackOutTimeStart.HasValue) { sqlString = sqlString + " AND SemiCheck.BackOutTime >= :BackOutTimeStart "; parameters.Add(new OracleParameter(":BackOutTimeStart", OracleDbType.Date, entity.BackOutTimeStart.Value, ParameterDirection.Input)); } // 撤销时间结束 if (entity.BackOutTimeEnd.HasValue) { sqlString = sqlString + " AND SemiCheck.BackOutTime <= :BackOutTimeEnd "; parameters.Add(new OracleParameter(":BackOutTimeEnd", OracleDbType.Date, entity.BackOutTimeEnd.Value, ParameterDirection.Input)); } // 撤销工号 if (!string.IsNullOrEmpty(entity.BackOutUserCode)) { sqlString = sqlString + " AND instr(SemiCheck.BackOutUserCode,:BackOutUserCode)>0"; parameters.Add(new OracleParameter(":BackOutUserCode", OracleDbType.NVarchar2, entity.BackOutUserCode, ParameterDirection.Input)); } // 产品条码 if (!string.IsNullOrEmpty(entity.BarCode)) { sqlString = sqlString + " AND instr(SemiCheck.BarCode,:BarCode)>0"; parameters.Add(new OracleParameter(":BarCode", OracleDbType.NVarchar2, entity.BarCode, 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(); } } } /// /// 获取半成品检验缺陷列表 /// 半成品检验ID /// /// DataSet public static DataSet GetSemiCheckDefect(int semiCheckID) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); List parameters = new List(); parameters.Add(new OracleParameter(":semiCheckID", OracleDbType.Int32, semiCheckID, ParameterDirection.Input)); string sqlString = @"select TP_PM_SemiCheckDefect.DefectCode, TP_PM_SemiCheckDefect.DefectName, TP_PM_SemiCheckDefect.DefectPositionCode, TP_PM_SemiCheckDefect.DefectPositionName, TP_PC_Procedure.ProcedureName, TP_PM_SemiCheckDefect.DefectUserCode from TP_PM_SemiCheckDefect left join TP_PC_Procedure on TP_PM_SemiCheckDefect.DefectProcedureID=TP_PC_Procedure.ProcedureID where TP_PM_SemiCheckDefect.semiCheckID=:semiCheckID "; 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(); } } } /// /// 根据所选工号，查出缺陷责任员工 /// /// 用户ID /// 用户基本信息 /// DataSet public static DataSet GetSemiCheckDefectStaffByUserID(SUserInfo sUserInfo, int userid) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = @"select TP_HR_Staff.StaffID,TP_HR_Staff.StaffCode,TP_HR_Staff.StaffName, TP_HR_Staff.StaffStatus,TP_MST_UserStaff.UJobsID as SJobsID, TP_MST_UserStaff.Ujobsid from TP_MST_UserStaff left join TP_HR_Staff on TP_MST_UserStaff.Staffid=TP_HR_Staff.Staffid where TP_MST_UserStaff.Userid=:userid "; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":userid",OracleDbType.Int32, userid,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); return ds; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } /// /// 获取标识 /// /// 产品条码ram> /// int public static int GetRecyclingflagByBarcode(string barcode, SUserInfo sUserInfo) { IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString); try { con.Open(); string sqlString = "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"; OracleParameter[] paras = new OracleParameter[]{ new OracleParameter(":BarCode",OracleDbType.Varchar2, barcode,ParameterDirection.Input), }; DataSet ds = con.GetSqlResultToDs(sqlString, paras); if (ds != null && ds.Tables[0].Rows.Count > 0) { return Convert.ToInt32(ds.Tables[0].Rows[0]["recyclingflag"]); } return 0; } catch (Exception ex) { throw ex; } finally { if (con.ConnState == ConnectionState.Open) { con.Close(); } } } } }