IMEX.mes.wcf/DK.Service/PMModuleLogic/PMModuleLogic.cs

/*******************************************************************************
 * 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.ManagedDataAccess.Client;

namespace Dongke.IBOSS.PRD.Service.PMModuleLogic
{
    /// <summary>
    /// 生产管理
    /// </summary>
    public partial class PMModuleLogic
    {
        /// <summary>
        /// 构建 计数/检验时返回的信息
        /// </summary>
        /// <returns></returns>
        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;
        }

        /// <summary>
        /// 构建 校验窑车号时返回的信息
        /// </summary>
        /// <returns></returns>
        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;
        }

        /// <summary>
        /// 校验窑车是否可用
        /// </summary>
        /// <param name="pProcedureId">工序ID</param>
        /// <param name="kilnCarCode">窑车号</param>
        /// <param name="pModelType">类别</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>CheckKilnCarResultEntity实体类</returns>
        /// <remarks>
        /// 陈冰              2014.09.26              新建
        /// </remarks>
        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<KilnCarInfo>();
                }
                else
                {
                    kilnCarResultEntity.KilnCarInfos = DataConvert.TableConvertToObject<KilnCarInfo>(returnDataSet.Tables[0]);
                }
                return kilnCarResultEntity;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (oracleConn.ConnState == ConnectionState.Open)
                {
                    oracleConn.Close();
                }
            }
        }

        /// <summary>
        /// 由工序获取产缺陷列表
        /// </summary>
        /// <param name="procedureID">工序ID</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 由当前检验工序ID和缺陷Code获取缺陷数据
        /// </summary>
        /// <param name="procedureID">当前检验工序ID</param>
        /// <param name="defectCode">缺陷Code</param>
        /// <returns>object</returns>
        /// <remarks>
        /// 陈冰              2014.10.04              新建
        /// </remarks>
        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();
                }
            }
        }

        /// <summary>
        /// 根据产品ID查出缺陷位置
        /// </summary>
        /// <param name="goodsID">产品ID</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 根据缺陷位置
        /// </summary>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 根据产品ID查出缺陷位置
        /// </summary>
        /// <param name="goodsID">产品ID</param>
        /// <param name="positionCode">位置编码</param>
        /// <returns>object</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 通过条码查出责任工序
        /// </summary>
        /// <param name="barcode">产品条码</param>
        /// <param name="defectid">缺陷ID</param>
        /// <returns>DataSet</returns>
        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.ManagedDataAccess.Client.OracleParameter[] 
                //           {
                //                new Oracle.ManagedDataAccess.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.ManagedDataAccess.Client.OracleParameter[] 
                           {
                                new Oracle.ManagedDataAccess.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();
                }
            }
        }

        /// <summary>
        /// 通过条码与工序查出责任工号
        /// </summary>
        /// <param name="barcode">产品条码</param>
        /// <param name="dutyProcedureID">责任工序</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 责任工序查出工号根据生产数据ID
        /// </summary>
        /// <param name="ProductionDataID"></param>
        /// <returns></returns>
        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();
                }
            }
        }

        /// <summary>
        /// 根据所选工序默认查出工种信息
        /// </summary>
        /// <param name="userID">工号ID</param>
        /// <param name="productionDataID">生产数据ID</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 根据所选生产数据ID，用户ID及工种选出责任员工
        /// </summary>
        /// <param name="productionDataID">生产数据ID</param>
        /// <param name="userID">用户ID</param>
        /// <param name="jobs">工种ID</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 根据传入的实体获取进度考核奖惩信息
        /// </summary>
        /// <param name="searchAdminRAPEntity">查询实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet进度考核奖惩信息表</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 根据条码获取窑炉窑车信息
        /// </summary>
        /// <param name="barcode">产品条码</param>
        /// <returns>DataSet</returns>
        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 生产数据

        /// <summary>
        /// 查询在产产品数据
        /// </summary>
        /// <param name="searchInProductionEntity">在产产品数据实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet在产产品数据信息表</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 查询成品数据
        /// </summary>
        /// <param name="searchInProductionEntity">产成品数据实体</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet产成品数据信息表</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 查询半检数据一览
        /// </summary>
        /// <param name="SearchSemiTestDetailEntity">查询半检数据明细实体类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        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 校验条码是否可以下车

        /// <summary>
        /// 校验条码是否可以下车
        /// </summary>
        /// <param name="procedureID">当前工序</param>
        /// <param name="barcode">产品条码</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>CheckCancelLoadCar</returns>
        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

        /// <summary>
        /// 根据所选生产数据ID,显示成检数据信息
        /// </summary>
        /// <param name="productionDataID">生产数据ID</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取产品完成工序的ID
        /// </summary>
        /// <param name="barcode">产品条码</param>
        /// <returns>int</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 编辑后删除生产数据
        /// </summary>
        /// <param name="productionDataID">生产数据ID</param>
        /// <returns>int</returns>
        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;
        }

        /// <summary>
        /// 由产品条码获取注浆信息
        /// </summary>
        /// <param name="barcode">产品条码</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 根据所选工号对应的工种，查出缺陷责任员工
        /// </summary>
        /// <param name="jobs">工种ID</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 根据所选工号，查出漏检责任员工
        /// </summary>
        /// <param name="userid">工号</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 根据所选工号对应的工种，查出缺陷责任员工
        /// </summary>
        /// <param name="jobs">工种ID</param>
        /// <param name="userid">用户ID</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取干补标识
        /// </summary>
        /// <param name="barcode">产品条码</param>ram>
        /// <returns>int</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取窑车对应产品列表
        /// <param name="KilnCarID">窑车ID</param>
        /// </summary>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取未在生产线上报损的注浆信息
        /// <param name="barcode">产品条码</param>
        /// </summary>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取编辑的生产数据ID与列表所选生产数据ID做比较
        /// </summary>
        /// <param name="barcode">产品条码</param>
        /// <returns>int</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取在产产品的信息标识列表
        /// </summary>
        /// <param name="barcode">产品条码</param>
        /// <returns>int</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 成检时获取此条码是否报损
        /// <param name="barcode">产品条码</param>
        /// </summary>
        /// <returns>int</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取产品完成工序的ID(PDA)
        /// </summary>
        /// <param name="barcode">产品条码</param>
        /// <returns>int</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 成检时获取条码的产品信息（成检(正品)--报损-->干补-->成检）
        /// </summary>
        /// <param name="barcode">产品条码</param>
        /// <returns>int</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 生成盘点明细
        /// </summary>
        /// <param name="ProcedureIDS">工序ID集</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns></returns>
        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<OracleParameter> parameters = new List<OracleParameter>();
                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();
                }
            }
        }

        /// <summary>
        /// 获取盘点单列表
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        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<OracleParameter> parameters = new List<OracleParameter>();
                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();
                }
            }
        }

        /// <summary>
        /// 获取盘点单工号列表
        /// </summary>
        /// <param name="sUserInfo"></param>
        /// <returns></returns>
        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<OracleParameter> parameters = new List<OracleParameter>();
                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();
                }
            }
        }

        /// <summary>
        /// 获取主表盘点信息
        /// </summary>
        /// <param name="entity">盘点类</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns></returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取盘点单名细信息
        /// </summary>
        /// <param name="InCheckedID">盘点单号</param>
        /// <returns></returns>
        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();
                }
            }
        }



        /// <summary>
        /// 获取盘点单名细信息
        /// </summary>
        /// <param name="InCheckedID">盘点单号</param>
        /// <returns></returns>
        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();
                }
            }
        }



        /// <summary>
        /// 获取盘点单名细信息
        /// </summary>
        /// <param name="InCheckedID">盘点单号</param>
        /// <returns></returns>
        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 清除在产残留数据

        /// <summary>
        /// 取得PM2108画面（在产品明细表）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="se">查询条件</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetPM2108Data(SUserInfo user, RPT010401_SE se)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

                List<OracleParameter> parameters = new List<OracleParameter>();
                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();
                }
            }
        }

        /// <summary>
        /// 获取RPT010401画面（在产品明细表）的查询sql
        /// </summary>
        /// <returns>sql</returns>
        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<OracleParameter> parameters = new List<OracleParameter>();
                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();
                }
            }
        }


        /// <summary>
        /// 获取RPT010401画面（在产品明细表）的查询sql
        /// </summary>
        /// <returns>sql</returns>
        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<OracleParameter> parameters = new List<OracleParameter>();
                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();
                }
            }
        }


        /// <summary>
        /// 获取RPT010401画面（在产品明细表）的查询sql
        /// </summary>
        /// <returns>sql</returns>
        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

        /// <summary>
        /// 工序是否有商标ID
        /// <param name="ProcedureID">工序ID</param>
        /// </summary>
        /// <returns>int</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取条码商标
        /// <param name="barcode">产品条码</param>
        /// </summary>
        /// <returns>int</returns>
        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 g.LogoID,l.logocode,l.logoname, g.glazetypeid, d.DICTIONARYVALUE glazetypename
                                from tp_pm_groutingdailydetail g
                                     left join tp_mst_logo l
                                     on g.logoid=l.logoid
                                     left join TP_MST_DataDictionary d on d.DICTIONARYID = g.glazetypeid
                                        where g.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();
                }
            }
        }

        /// <summary>
        /// 半检时,入窑前检验获取此条码是否报损为废品
        /// <param name="barcode">产品条码</param>
        /// </summary>
        /// <returns>int</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取盘点单明细
        /// </summary>
        /// <param name="procedureID">工序ID</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取半成品检验列表
        /// <param name="entity">实体类</param>
        /// </summary>
        /// <returns>DataSet</returns>
        public static DataSet GetSemiCheck(SemiCheckEntity entity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                List<OracleParameter> parameters = new List<OracleParameter>();
                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();
                }
            }
        }

        /// <summary>
        /// 获取半成品检验缺陷列表
        /// <param name="semiCheckID">半成品检验ID</param>
        /// </summary>
        /// <returns>DataSet</returns>
        public static DataSet GetSemiCheckDefect(int semiCheckID)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                List<OracleParameter> parameters = new List<OracleParameter>();
                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();
                }
            }
        }


        /// <summary>
        /// 根据所选工号，查出缺陷责任员工
        /// </summary>
        /// <param name="userid">用户ID</param>
        /// <param name="sUserInfo">用户基本信息</param>
        /// <returns>DataSet</returns>
        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();
                }
            }
        }

        /// <summary>
        /// 获取标识
        /// </summary>
        /// <param name="barcode">产品条码</param>ram>
        /// <returns>int</returns>
        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();
                }
            }
        }
    }
}