/*******************************************************************************
 * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：PAMModuleLogic.cs
 *      2.功能描述：工资管理策略
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      王鑫            2015/08/17          1.00            新建
*******************************************************************************/

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels.PAMModule;
using Dongke.IBOSS.PRD.WCF.DataModels.PMModule;
using Oracle.DataAccess.Client;

namespace Dongke.IBOSS.PRD.Service.PAMModuleService
{
    /// <summary>
    /// 工资管理策略
    /// </summary>
    public partial class PAMModuleLogic
    {
        /// <summary>
        /// 获取工资方案参数设定
        /// </summary>
        /// <returns>DataSet</returns>
        public static DataSet GetPayPlanSetting(SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString = @"select TP_PAS_PayPlanSetting.SettingCode,TP_PAS_PayPlanSetting.SettingName,
                                        TP_PAS_PayPlanSetting.SettingValue,TP_PAS_PayPlan.PayPlanName,TP_PAS_PayPlanSetting.SettingValue as SettingValueOrg    from TP_PAS_PayPlanSetting
                                        left join TP_PAS_PayPlan on TP_PAS_PayPlanSetting.PayPlanID=TP_PAS_PayPlan.PayPlanID";
                DataSet ds = con.GetSqlResultToDs(sqlString);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 获取工资方案列表
        /// </summary>
        /// <returns>DataSet</returns>
        public static DataSet GetPayPlan(SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                string sqlString = @"select PayPlanID,PayPlanName  from TP_PAS_PayPlan where AccountID=:AccountID and ValueFlag=1";
                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter(":AccountID",OracleDbType.Int32,sUserInfo.AccountID,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>
        /// <returns>DataSet</returns>
        public static DataSet GetJobsPayPlan(string jobsCode, string PayPlanName, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {

                con.Open();
                string sqlString = @"select TP_MST_Jobs.JobsID, TP_MST_Jobs.JobsName,TP_MST_Jobs.JobsCode,TP_PAS_PayPlan.PayPlanName,TP_PAS_PayPlan.PayPlanID  from TP_PAM_JobsPayPlan
                                    left join TP_PAS_PayPlan on TP_PAM_JobsPayPlan.Payplanid=TP_PAS_PayPlan.Payplanid
                                    left join TP_MST_Jobs on TP_PAM_JobsPayPlan.JobsID=TP_MST_Jobs.JobsID
                                    where  TP_PAS_PayPlan.accountid=:accountid and TP_PAS_PayPlan.valueflag=1 ";

                List<OracleParameter> parameters = new List<OracleParameter>();
                parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
                if (!string.IsNullOrEmpty(jobsCode))
                {
                    sqlString += " AND INSTR(TP_MST_Jobs.JobsCode, :JobsCode) > 0 ";
                    parameters.Add(new OracleParameter(":JobsCode", OracleDbType.NVarchar2, jobsCode, ParameterDirection.Input));
                }
                if (!string.IsNullOrEmpty(PayPlanName))
                {
                    sqlString += " AND  INSTR(TP_PAS_PayPlan.PayPlanName, :PayPlanName) > 0 ";
                    parameters.Add(new OracleParameter(":PayPlanName", OracleDbType.NVarchar2, PayPlanName, 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>
        /// <returns>DataSet</returns>
        public static DataSet GetJobsPayPlanList(string jobsCode, string PayPlanName,string jobsName, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {

                con.Open();
                string sqlString = @"select TP_MST_Jobs.JobsID, TP_MST_Jobs.JobsName,TP_MST_Jobs.JobsCode,TP_PAS_PayPlan.PayPlanName,TP_PAS_PayPlan.PayPlanID  from TP_PAM_JobsPayPlan
                                    left join TP_PAS_PayPlan on TP_PAM_JobsPayPlan.Payplanid=TP_PAS_PayPlan.Payplanid
                                    left join TP_MST_Jobs on TP_PAM_JobsPayPlan.JobsID=TP_MST_Jobs.JobsID
                                    where  TP_PAS_PayPlan.accountid=:accountid and TP_PAS_PayPlan.valueflag=1 ";

                List<OracleParameter> parameters = new List<OracleParameter>();
                parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
                if (!string.IsNullOrEmpty(jobsCode))
                {
                    sqlString += " AND INSTR(TP_MST_Jobs.JobsCode, :JobsCode) > 0 ";
                    parameters.Add(new OracleParameter(":JobsCode", OracleDbType.NVarchar2, jobsCode, ParameterDirection.Input));
                }
                if (!string.IsNullOrEmpty(PayPlanName))
                {
                    sqlString += " AND  INSTR(TP_PAS_PayPlan.PayPlanName, :PayPlanName) > 0 ";
                    parameters.Add(new OracleParameter(":PayPlanName", OracleDbType.NVarchar2, PayPlanName, ParameterDirection.Input));
                }
                if (!string.IsNullOrEmpty(jobsName))
                {
                    sqlString += " AND INSTR(TP_MST_Jobs.JobsName, :JobsName) > 0 ";
                    parameters.Add(new OracleParameter(":JobsName", OracleDbType.NVarchar2, jobsName, 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>
        /// <returns>DataSet</returns>
        public static DataSet GetJobsPayPlanInfo(int PayPlanID, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {

                con.Open();
                string sqlString = @"select TP_MST_Jobs.JobsID, TP_MST_Jobs.JobsName,TP_MST_Jobs.JobsCode,TP_PAS_PayPlan.PayPlanName,TP_PAS_PayPlan.PayPlanID  from TP_PAM_JobsPayPlan
                                    left join TP_PAS_PayPlan on TP_PAM_JobsPayPlan.Payplanid=TP_PAS_PayPlan.Payplanid
                                    left join TP_MST_Jobs on TP_PAM_JobsPayPlan.JobsID=TP_MST_Jobs.JobsID
                                    where  TP_PAS_PayPlan.accountid=:accountid and TP_PAS_PayPlan.valueflag=1 and  TP_PAS_PayPlan.PayPlanID=:PayPlanID";

                List<OracleParameter> parameters = new List<OracleParameter>();
                parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
                parameters.Add(new OracleParameter(":PayPlanID", OracleDbType.Int32, PayPlanID, 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>
        /// <returns>DataSet</returns>
        public static DataSet GetWagesType(string WagesTypeName, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {

                con.Open();
                string sqlString = @"select TP_PAM_WagesType.WagesTypeID,TP_PAM_WagesType.WagesTypeName,
                                    TP_PAM_WagesType.CreateTime,TP_MST_User.UserName,TP_MST_User.UserCode
                                    from TP_PAM_WagesType
                                    left join TP_MST_User on TP_PAM_WagesType.CreateUserID=TP_MST_User.UserID
                                    where  TP_PAM_WagesType.accountid=:accountid and TP_PAM_WagesType.valueflag=1 ";

                List<OracleParameter> parameters = new List<OracleParameter>();
                parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
                if (!string.IsNullOrEmpty(WagesTypeName))
                {
                    sqlString += " AND INSTR(TP_PAM_WagesType.WagesTypeName, :WagesTypeName) > 0 ";
                    parameters.Add(new OracleParameter(":WagesTypeName", OracleDbType.NVarchar2, WagesTypeName, 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 GetAllWagesType(SUserInfo sUserInfo)
        {
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                string sqlString = "Select WagesTypeID,WagesTypeName,AccountID,ValueFlag,CreateTime,CreateUserID,UpdateTime,UpdateUserID,OPTimeStamp "
                                  + "from TP_PAM_WagesType where AccountID = :AccountID";
                Oracle.DataAccess.Client.OracleParameter[] oracleParameter = new Oracle.DataAccess.Client.OracleParameter[] 
                   {
                    new Oracle.DataAccess.Client.OracleParameter(":AccountID",sUserInfo.AccountID)
                   };
                oracleConn.Open();
                DataSet result = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
                oracleConn.Close();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (oracleConn.ConnState == ConnectionState.Open)
                {
                    oracleConn.Close();
                }
            }
        }


        /// <summary>
        /// 搜索产品工价分类列表
        /// </summary>
        /// <returns>DataSet</returns>
        public static DataSet GetGoodsWagesType(string WagesTypeName, string GoodsCode, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {

                con.Open();
                string sqlString = @"select TP_PAM_WagesType.WagesTypeID, TP_PAM_WagesType.WagesTypeName,
                                    TP_MST_Goods.goodsCode,TP_MST_Goods.goodsName,TP_MST_Goods.StartingDate,TP_MST_Goods.GoodsID
                                    from TP_PAM_GoodsWagesType
                                    left join TP_PAM_WagesType on TP_PAM_GoodsWagesType.WagesTypeID=TP_PAM_WagesType.WagesTypeID
                                    left join TP_MST_Goods on TP_PAM_GoodsWagesType.GoodsID=TP_MST_Goods.GoodsID
                                    where  TP_PAM_WagesType.accountid=:accountid and TP_PAM_WagesType.valueflag=1 ";

                List<OracleParameter> parameters = new List<OracleParameter>();
                parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
                if (!string.IsNullOrEmpty(WagesTypeName))
                {
                    sqlString += " AND INSTR(TP_PAM_WagesType.WagesTypeName, :WagesTypeName) > 0 ";
                    parameters.Add(new OracleParameter(":WagesTypeName", OracleDbType.NVarchar2, WagesTypeName, ParameterDirection.Input));
                }

                if (!string.IsNullOrEmpty(GoodsCode))
                {
                    sqlString += " AND INSTR(TP_MST_Goods.goodsCode, :GoodsCode) > 0 ";
                    parameters.Add(new OracleParameter(":GoodsCode", OracleDbType.NVarchar2, GoodsCode, 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>
        /// <returns>DataSet</returns>
        public static DataSet GetPieceworkData(PieceworkEntity pieceworkEndity, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {

                con.Open();
                string sqlString = @"select pw.piecetacticsid,
                                            pw.PieceTacticsName,
                                       wmsys.wm_concat(decode(pp.procedureflag,
                                                              '1',
                                                              to_char(p.procedureName),
                                                              null)) as PieceProcedure,
                                       wmsys.wm_concat(decode(pp.procedureflag,
                                                              '2',
                                                              to_char(p.procedureName),
                                                              null)) as QualityBaseProcedure,
                                                              pw.PieceCoefficient,
                                       pw.DamageFlag,
                                       pw.DamageCoefficient,
                                       pw.UnqualifiedFlag,
                                       pw.UnqualifiedCoefficient,
                                       pw.QualifiedFlag,
                                       pw.QualifiedCoefficient,
                                       decode(pw.PieceType, '0', '工序计件', '经过工序计件') as PieceTypeName,
                                        pw.ValueFlag,
                                       TP_PAS_PayPlan.Payplanname,
                                        pw.PayPlanID
                                  from TP_PAT_Piecework pw
                                  left join TP_PAT_PieceProcedure pp on pw.piecetacticsid =
                                                                        pp.piecetacticsid
                                  left join Tp_Pc_Procedure p on p.procedureid = pp.procedureid
                                   left join TP_PAS_PayPlan on pw.Payplanid =
                                                              TP_PAS_PayPlan.Payplanid
                                    where  pw.accountid=:accountid and pw.valueflag=1 ";

                List<OracleParameter> parameters = new List<OracleParameter>();
                parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));

                //if (!string.IsNullOrEmpty(pieceworkEndity.PieceProcedureIDS))
                //{
                //    sqlString = sqlString + " AND instr(','||:PieceProcedureIDS||',',','||TP_PAT_Piecework.PieceProcedureID||',')>0 ";
                //    parameters.Add(new OracleParameter(":PieceProcedureIDS", OracleDbType.NVarchar2, pieceworkEndity.PieceProcedureIDS, ParameterDirection.Input));
                //}
                //if (pieceworkEndity.PieceType != null)
                //{
                //    sqlString += " AND TP_PAT_Piecework.PieceType=:PieceType ";
                //    parameters.Add(new OracleParameter(":PieceType", OracleDbType.Int32, pieceworkEndity.PieceType, ParameterDirection.Input));
                //}

                if (!string.IsNullOrEmpty(pieceworkEndity.PayPlanName))
                {
                    //sqlString = sqlString + " AND instr(','||:PayPlanName||',',','||pw.PieceTacticsName||',')>0 ";
                    sqlString = sqlString + " AND instr(pw.PieceTacticsName,:PayPlanName)>0 ";
                    parameters.Add(new OracleParameter(":PayPlanName", OracleDbType.NVarchar2, pieceworkEndity.PayPlanName, ParameterDirection.Input));
                }
                if (pieceworkEndity.PayPlanID != null)
                {
                    sqlString += " AND pw.PayPlanID=:PayPlanID ";
                    parameters.Add(new OracleParameter(":PayPlanID", OracleDbType.Int32, pieceworkEndity.PayPlanID, ParameterDirection.Input));
                }

                sqlString += @" group by pw.piecetacticsid,
                         pw.PieceCoefficient,
                        pw.PieceTacticsName,
                               pw.DamageFlag,
                               pw.DamageCoefficient,
                               pw.UnqualifiedFlag,
                               pw.UnqualifiedCoefficient,
                               pw.QualifiedFlag,
                               pw.QualifiedCoefficient,
                               pw.PieceType,
                               pw.ValueFlag,
                               TP_PAS_PayPlan.Payplanname,pw.PayPlanID";
                DataSet ds = con.GetSqlResultToDs(sqlString, parameters.ToArray());
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }


        /// <summary>
        /// 根据所选计件工资策略ID,显示数据信息
        /// </summary>
        /// <param name="PieceTacticsID">计件工资策略ID</param>
        /// <returns>DataSet</returns>
        public static DataSet GetPieceworkByID(int PieceTacticsID, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                DataSet dsReturn = new DataSet();
                string sqlString = @"select 
                                   
                                    TP_PAT_Piecework.PieceCoefficient
                                    ,TP_PAT_Piecework.DamageFlag
                                    ,TP_PAT_Piecework.DamageCoefficient
                                    ,TP_PAT_Piecework.UnqualifiedFlag
                                    ,TP_PAT_Piecework.UnqualifiedCoefficient
                                    ,TP_PAT_Piecework.QualifiedFlag
                                    ,TP_PAT_Piecework.QualifiedCoefficient
                                    
                                    ,decode(TP_PAT_Piecework.PieceType, '0', '工序计件' , '经过工序计件') as PieceTypeName,
                                    TP_PAT_Piecework.PieceType,
                                    TP_PAT_Piecework.PayPlanID,
                                    TP_PAT_Piecework.ValueFlag,
                                    TP_PAT_Piecework.PieceTacticsName
                                    from TP_PAT_Piecework
                                    where 
                                    TP_PAT_Piecework.PieceTacticsID=:PieceTacticsID
                                    ";
                string sqlString2 = @"
                                    select TP_PAT_Wages.PieceTacticsID,
                                            nvl(TP_PAT_Wages.WagesTypeID,TP_PAM_WagesType.WagesTypeID) as WagesTypeID,
                                            nvl(TP_PAT_Wages.StandardWages,0) as StandardWages,
                                            nvl(TP_PAT_Wages.DamageSubsidyRate,1) as DamageSubsidyRate,
                                            nvl(TP_PAT_Wages.DamageSubsidy,0) as DamageSubsidy,
                                            nvl(TP_PAT_Wages.RSuperiorCoefficient,1) as RSuperiorCoefficient,
                                            nvl(TP_PAT_Wages.RQualifiedCoefficient,1) as RQualifiedCoefficient,
                                            nvl(TP_PAT_Wages.RepairSubsidyRate,1) as RepairSubsidyRate ,
                                            nvl(TP_PAT_Wages.RepairSubsidy,0) as RepairSubsidy
                              from TP_PAM_WagesType
                              left join TP_PAT_Wages on TP_PAM_WagesType.WagesTypeID =
                                                        TP_PAT_Wages.WagesTypeID
                                                    and TP_PAT_Wages.PieceTacticsID = :PieceTacticsID
                             where TP_PAM_WagesType.valueflag = '1'
                               and TP_PAM_WagesType.AccountID =:AccountID
                                    
                                   ";
                string sqlString3 = @"select 
                                        *
                                    from TP_PAT_QualityWages
                                    where 
                                    TP_PAT_QualityWages.PieceTacticsID=:PieceTacticsID order by QualityRate desc
                                ";
                string sqlString4 = @"select 
                                        *
                                    from TP_PAT_PieceProcedure
                                    left join TP_PC_Procedure
                                    on TP_PC_Procedure.ProcedureID=TP_PAT_PieceProcedure.ProcedureID
                                    where 
                                    TP_PAT_PieceProcedure.PieceTacticsID=:PieceTacticsID
                                ";

                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter(":PieceTacticsID",OracleDbType.Int32, PieceTacticsID,ParameterDirection.Input),
                };

                OracleParameter[] paras2 = new OracleParameter[]{
                    new OracleParameter(":PieceTacticsID",OracleDbType.Int32, PieceTacticsID,ParameterDirection.Input),
                    new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input),
                };
                DataSet ds = con.GetSqlResultToDs(sqlString, paras);
                ds.Tables[0].TableName = "TP_PAT_Piecework";
                DataSet ds2 = con.GetSqlResultToDs(sqlString2, paras2);
                ds2.Tables[0].TableName = "TP_PAT_Wages";
                DataSet ds3 = con.GetSqlResultToDs(sqlString3, paras);
                ds3.Tables[0].TableName = "TP_PAT_QualityWages";
                DataSet ds4= con.GetSqlResultToDs(sqlString4, paras);
                ds4.Tables[0].TableName = "TP_PAT_PieceProcedure";

                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());
                return dsReturn;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.ConnState == ConnectionState.Open)
                {
                    con.Close();
                }
            }
        }


        /// <summary>
        /// 搜索品质考核策略
        /// </summary>
        /// <returns>DataSet</returns>
        public static DataSet GetQualityASS(int? QualityBaseProcedureID, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {

                con.Open();
                string sqlString = @"select 
                                    TP_PAT_QualityASS.QualityASSTacticsID,
                                    TP_PAT_QualityASS.DefectFine,
                                    TP_PAT_QualityASS.QualityBaseProcedureID,
                                    TP_PC_Procedure.ProcedureName as QualityBaseProcedure,
                                    TP_PAT_QualityASS.CreateTime
                                    from TP_PAT_QualityASS 
                                    left join TP_PC_Procedure on TP_PAT_QualityASS.QualityBaseProcedureID=TP_PC_Procedure.ProcedureID
                                    where  TP_PAT_QualityASS.accountid=:accountid and TP_PAT_QualityASS.valueflag=1 ";

                List<OracleParameter> parameters = new List<OracleParameter>();
                parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));

                if (QualityBaseProcedureID != null)
                {
                    sqlString += " AND TP_PAT_QualityASS.QualityBaseProcedureID=:QualityBaseProcedureID ";
                    parameters.Add(new OracleParameter(":QualityBaseProcedureID", OracleDbType.Int32, QualityBaseProcedureID, 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>
        /// <returns>DataSet</returns>
        public static DataSet GetQualityASSList(string QualityTacticsName,int? PayPlanID, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {

                con.Open();
                string sqlString = @"select TP_PAT_QualityASS.QualityASSTacticsID,
                                       TP_PAT_QualityASS.QualityTacticsName,
                                       TP_PAT_QualityASS.DefectFine,
                                       TP_PAS_PayPlan.PayPlanName,
      
                                       TP_PAT_QualityASS.CreateTime,
                                       TP_MST_DataDictionary.Dictionaryvalue
                              from TP_PAT_QualityASS
                              left join TP_PAS_PayPlan on TP_PAS_PayPlan.PayPlanid=TP_PAT_QualityASS.PayPlanid
                              left join TP_MST_DataDictionary on TP_PAT_QualityASS.DefectFine =
                                                                 TP_MST_DataDictionary.DictionaryID
                                                             and TP_MST_DataDictionary.DictionaryType =
                                                                 'ASE002'
                                    where  TP_PAT_QualityASS.accountid=:accountid and TP_PAT_QualityASS.valueflag=1 ";

                List<OracleParameter> parameters = new List<OracleParameter>();
                parameters.Add(new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));

                if (!string.IsNullOrEmpty(QualityTacticsName))
                {
                   // sqlString += " AND TP_PAT_QualityASS.QualityTacticsName=:QualityTacticsName ";
                    sqlString = sqlString + " AND instr(TP_PAT_QualityASS.QualityTacticsName,:QualityTacticsName)>0 ";
                    parameters.Add(new OracleParameter(":QualityTacticsName", OracleDbType.NVarchar2, QualityTacticsName, ParameterDirection.Input));
                }
                if (PayPlanID!=null)
                {
                    sqlString += " AND TP_PAT_QualityASS.PayPlanID=:PayPlanID ";
                    parameters.Add(new OracleParameter(":PayPlanID", OracleDbType.Int32, PayPlanID, ParameterDirection.Input));
                }
                sqlString += @" group by TP_PAT_QualityASS.QualityASSTacticsID,
                      TP_PAT_QualityASS.DefectFine,
                      TP_PAS_PayPlan.PayPlanName,
                      TP_PAT_QualityASS.QualityTacticsName,
                      TP_PAT_QualityASS.CreateTime,
                      TP_MST_DataDictionary.Dictionaryvalue";
                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="QualityASSTacticsID">ID</param>
        /// <returns>DataSet</returns>
        public static DataSet GetQualityASSByID(int? QualityASSTacticsID, SUserInfo sUserInfo)
        {
            IDBConnection con = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            try
            {
                con.Open();
                DataSet dsReturn = new DataSet();
                string sqlString = @"select 
                                    TP_PAT_QualityASS.QualityASSTacticsID
                                    ,TP_PAT_QualityASS.PayPlanID
                                    ,TP_PAT_QualityASS.DefectFine
                                   ,TP_PAT_QualityASS.QualityTacticsName
                                    from TP_PAT_QualityASS
                                    
                                    where 
                                    TP_PAT_QualityASS.QualityASSTacticsID=:QualityASSTacticsID
                                    ";
                string sqlString2 = @"
                                     select *
                                      from TP_PAM_WagesType
                                      left join TP_PAT_QualityGoods on TP_PAM_WagesType.WagesTypeID =
                                                                TP_PAT_QualityGoods.WagesTypeID
                                                            and TP_PAT_QualityGoods.QualityASSTacticsID = :QualityASSTacticsID
                                    
                                    where TP_PAM_WagesType.valueflag = '1'
                                    and TP_PAM_WagesType.AccountID =:AccountID";
                string sqlString3 = @"select 
                                        *
                                    from TP_PAT_QualityReward
                                    where 
                                    TP_PAT_QualityReward.QualityASSTacticsID=:QualityASSTacticsID order by QualityRate desc
                                ";
//                string sqlString4 = @"select 
//                                        *
//                                    from TP_PAT_QualityProcedure
//                                    left join TP_PC_Procedure
//                                    on TP_PAT_QualityProcedure.ProcedureID=TP_PC_Procedure.ProcedureID
//                                    where 
//                                    TP_PAT_QualityProcedure.QualityASSTacticsID=:QualityASSTacticsID
//                                ";

                OracleParameter[] paras = new OracleParameter[]{
                    new OracleParameter(":QualityASSTacticsID",OracleDbType.Int32, QualityASSTacticsID,ParameterDirection.Input),
                };

                OracleParameter[] paras2 = new OracleParameter[]{
                    new OracleParameter(":QualityASSTacticsID",OracleDbType.Int32, QualityASSTacticsID,ParameterDirection.Input),
                    new OracleParameter(":AccountID",OracleDbType.Int32, sUserInfo.AccountID,ParameterDirection.Input),
                };

                DataSet ds = con.GetSqlResultToDs(sqlString, paras);
                ds.Tables[0].TableName = "TP_PAT_QualityASS";
                DataSet ds2 = con.GetSqlResultToDs(sqlString2, paras2);
                ds2.Tables[0].TableName = "TP_PAT_QualityGoods";
                DataSet ds3 = con.GetSqlResultToDs(sqlString3, paras);
                ds3.Tables[0].TableName = "TP_PAT_QualityReward";
                //DataSet ds4 = con.GetSqlResultToDs(sqlString4, paras);
                //ds4.Tables[0].TableName = "TP_PAT_QualityProcedure";

                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());

                return dsReturn;
            }
            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 ServiceResultEntity GetAdminEXA(AdminEXAEntity entity, SUserInfo sUserInfo)
		{
			IDBConnection conn = null;
			try
			{
				conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
				List<OracleParameter> parameters = new List<OracleParameter>();
				parameters.Add(new OracleParameter(":AccountID", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input));
				parameters.Add(new OracleParameter(":UPUserId", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input));
	
				string sqlString = "\n" +
				"select a.AdminEXAID,\n" +
				"       --a.UserID,\n" +
				"       a.StaffID,\n" +
				"       a.AdminEXATypeID,\n" +
				"       a.Amount,\n" +
				"       a.Remarks,\n" +
				"       a.AuditStatus,\n" +
				"       a.Auditor,\n" +
				"       a.AuditlDate,\n" +
				"       --a.AccountID,\n" +
				"       --a.ValueFlag,\n" +
				"       a.SettlementFlag,\n" +
				"       a.CreateTime,\n" +
				"       a.CreateUserID,\n" +
				"       a.UpdateTime,\n" +
				"       a.UpdateUserID,\n" +
				"       a.OPTimeStamp,\n" +
				"       s.staffcode,\n" +
				"       s.staffname,\n" +
				"       cu.username         cname,\n" +
				"       uu.username         uname,\n" +
				"       au.username         aname,\n" +
				"       dd.dictionaryvalue  AdminEXATypeName,\n" +
				"       ass.auditstatusname\n" +
				"  from TP_PAD_AdminEXA a\n" +
				"  left join tp_hr_staff s\n" +
				"    on s.staffid = a.staffid\n" +
				"  left join tp_mst_user cu\n" +
				"    on cu.userid = a.CreateUserID\n" +
				"  left join tp_mst_user uu\n" +
				"    on uu.userid = a.updateuserid\n" +
				"  left join tp_mst_user au\n" +
				"    on au.userid = a.auditor\n" +
				"  left join TP_MST_DataDictionary dd\n" +
				"    on dd.dictionaryid = a.AdminEXATypeID\n" +
				"  left join Tp_Sys_Auditstatus ass\n" +
				"    on ass.auditstatusid = a.AuditStatus\n" +
				" where a.valueflag = '1'\n" +
				"   and a.AccountID = :AccountID\n" +
				"   AND (a.CreateUserID = :UPUserId OR EXISTS (SELECT UP.PurviewID\n" +
				"          FROM TP_MST_UserPurview UP\n" +
				"         WHERE UP.PurviewType = '" + (int)Constant.PurviewType.OperateUser + "'" +
                "           AND (UP.PurviewID = a.CreateUserID OR UP.PurviewID = -1)\n" +
				"           AND UP.UserId = :UPUserId))";
				StringBuilder sql = new StringBuilder(sqlString);
				if (entity.StaffID.HasValue)
				{
					sql.Append(" AND a.StaffID =:StaffID ");
					parameters.Add(new OracleParameter(":StaffID", OracleDbType.Int32, entity.StaffID, ParameterDirection.Input));
				}

				if (!string.IsNullOrWhiteSpace( entity.SettlementFlag))
				{
					sql.Append(" AND a.SettlementFlag =:SettlementFlag ");
					parameters.Add(new OracleParameter(":SettlementFlag", OracleDbType.Char, entity.SettlementFlag, ParameterDirection.Input));
				}

				if (entity.AuditStatus.HasValue)
				{
					sql.Append(" AND a.AuditStatus =:AuditStatus ");
					parameters.Add(new OracleParameter(":AuditStatus", OracleDbType.Int32, entity.AuditStatus, ParameterDirection.Input));
				}

				if (entity.AdminEXATypeID.HasValue)
				{
					sql.Append(" AND a.AdminEXATypeID =:AdminEXATypeID ");
					parameters.Add(new OracleParameter(":AdminEXATypeID", OracleDbType.Int32, entity.AdminEXATypeID, ParameterDirection.Input));
				}

				if (!string.IsNullOrWhiteSpace(entity.Remarks))
				{
					sql.Append(" AND INSTR(a.Remarks, :Remarks) > 0");
					parameters.Add(new OracleParameter(":Remarks", OracleDbType.NVarchar2, entity.Remarks, ParameterDirection.Input));
				}


				sql.Append(" ORDER BY a.AdminEXAID");
				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>
		/// 获取行政考核记录
		/// </summary>
		/// <param name="id">行政考核记录ID</param>
		/// <returns>查询结果</returns>
		public static ServiceResultEntity GetAdminEXAByID(int id)
		{
			IDBConnection conn = null;
			try
			{
				conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

				string sqlString = "\n" +
				"select a.AdminEXAID,\n" +
				"       --a.UserID,\n" +
				"       a.StaffID,\n" +
				"       a.AdminEXATypeID,\n" +
				"       a.Amount,\n" +
				"       a.Remarks,\n" +
				"       a.AuditStatus,\n" +
				"       a.Auditor,\n" +
				"       a.AuditlDate,\n" +
				"       --a.AccountID,\n" +
				"       --a.ValueFlag,\n" +
				"       a.SettlementFlag,\n" +
				"       a.CreateTime,\n" +
				"       a.CreateUserID,\n" +
				"       a.UpdateTime,\n" +
				"       a.UpdateUserID,\n" +
				"       a.OPTimeStamp,\n" +
				"       s.staffcode,\n" +
				"       s.staffname,\n" +
				"       cu.username         cname,\n" +
				"       uu.username         uname,\n" +
				"       au.username         aname,\n" +
				"       dd.dictionaryvalue  AdminEXATypeName,\n" +
				"       ass.auditstatusname\n" +
				"  from TP_PAD_AdminEXA a\n" +
				"  left join tp_hr_staff s\n" +
				"    on s.staffid = a.staffid\n" +
				"  left join tp_mst_user cu\n" +
				"    on cu.userid = a.CreateUserID\n" +
				"  left join tp_mst_user uu\n" +
				"    on uu.userid = a.updateuserid\n" +
				"  left join tp_mst_user au\n" +
				"    on au.userid = a.auditor\n" +
				"  left join TP_MST_DataDictionary dd\n" +
				"    on dd.dictionaryid = a.AdminEXATypeID\n" +
				"  left join Tp_Sys_Auditstatus ass\n" +
				"    on ass.auditstatusid = a.AuditStatus\n" +
				" where a.valueflag = '1'\n" +
				"   and a.AdminEXAID = :AdminEXAID\n";
				OracleParameter[] parameters = new OracleParameter[]
				{
					new OracleParameter(":AdminEXAID", OracleDbType.Int32, id, ParameterDirection.Input),
				};
				DataTable data = conn.GetSqlResultToDt(sqlString, parameters);

				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>
		/// 停用行政考核记录
		/// </summary>
		/// <param name="id">行政考核记录ID</param>
		/// <param name="opTimeStamp">时间戳</param>
		/// <returns>执行结果</returns>
		public static ServiceResultEntity StopAdminEXAByID(int id, object opTimeStamp, SUserInfo sUserInfo)
		{
			IDBTransaction tran = null;
			try
			{
				tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
				tran.IsCommandTimeout = false;
				string sql = "update TP_PAD_AdminEXA a set a.valueflag = '0', a.UpdateUserID=:UpdateUserID "
						   + " where a.AdminEXAID=:AdminEXAID and a.OPTimeStamp=:OPTimeStamp";

				OracleParameter[] parameters = new OracleParameter[]
				{
					new OracleParameter(":AdminEXAID", OracleDbType.Int32, id, ParameterDirection.Input),
					new OracleParameter(":UpdateUserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input),
					new OracleParameter(":OPTimeStamp", OracleDbType.TimeStamp, opTimeStamp, ParameterDirection.Input),
				};

				int result = tran.ExecuteNonQuery(sql, parameters);

				ServiceResultEntity sre = new ServiceResultEntity();
				if (result <= 0)
				{
					sre.Status = Constant.ServiceResultStatus.DataChanged;
					return sre;
				}

				tran.Commit();
				return sre;
			}
			catch (Exception ex)
			{
				if (tran != null &&
					tran.ConnState == ConnectionState.Open)
				{
					tran.Rollback();
				}
				throw ex;
			}
			finally
			{
				if (tran != null &&
					tran.ConnState == ConnectionState.Open)
				{
					tran.Disconnect();
				}
			}
		}

		/// <summary>
		/// 审核行政考核记录
		/// </summary>
		/// <param name="ids">行政考核记录IDs</param>
		/// <returns>执行结果</returns>
		public static ServiceResultEntity AuditAdminEXA(string ids, Constant.AuditStatus auditStatus, SUserInfo sUserInfo)
		{
			IDBTransaction tran = null;
			try
			{
				tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);

				string sql = "update TP_PAD_AdminEXA a set a.AuditStatus = :AuditStatus, a.Auditor=:UpdateUserID,a.AuditlDate=sysdate, a.UpdateUserID=:UpdateUserID "
						   + " where INSTR(:AdminEXAIDs, ',' || a.AdminEXAID || ',') > 0 and a.AuditStatus=" + (int)Constant.AuditStatus.Pending;

				ids = "," + ids + ",";
				OracleParameter[] parameters = new OracleParameter[]
				{
					new OracleParameter(":AdminEXAIDs", OracleDbType.Varchar2, ids, ParameterDirection.Input),
					new OracleParameter(":UpdateUserID", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input),
					new OracleParameter(":AuditStatus", OracleDbType.Int32, (int)auditStatus, ParameterDirection.Input),
				};

				int result = tran.ExecuteNonQuery(sql, parameters);

				ServiceResultEntity sre = new ServiceResultEntity();
				if (result <= 0)
				{
					sre.Status = Constant.ServiceResultStatus.NoModifyData;
					return sre;
				}

				tran.Commit();
				return sre;
			}
			catch (Exception ex)
			{
				if (tran != null &&
					tran.ConnState == ConnectionState.Open)
				{
					tran.Rollback();
				}
				throw ex;
			}
			finally
			{
				if (tran != null &&
					tran.ConnState == ConnectionState.Open)
				{
					tran.Disconnect();
				}
			}
		}

		/// <summary>
		/// 新建行政考核记录
		/// </summary>
		/// <param name="entity">行政考核记录</param>
		/// <param name="sUserInfo">登录用户</param>
		/// <returns></returns>
		public static ServiceResultEntity AddAdminEXA(AdminEXAEntity entity, SUserInfo sUserInfo)
		{
			IDBTransaction tran = null;
			try
			{
				tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);

				string sql = "insert into tp_pad_adminexa\n" +
				"  (adminexaid,\n" +
				//"   userid,\n" +
				"   staffid,\n" +
				"   adminexatypeid,\n" +
				"   amount,\n" +
				"   remarks,\n" +
				"   auditstatus,\n" +
				//"   auditor,\n" +
				//"   auditldate,\n" +
				"   accountid,\n" +
				"   valueflag,\n" +
				"   settlementflag,\n" +
				"   createtime,\n" +
				"   createuserid,\n" +
				"   updatetime,\n" +
				"   updateuserid)\n" +
				"values\n" +
				"  (null,\n" +
				//"   v_userid,\n" +
				"   :staffid,\n" +
				"   :adminexatypeid,\n" +
				"   :amount,\n" +
				"   :remarks,\n" +
				"   :auditstatus,\n" +
				//"   :auditor,\n" +
				//"   :auditldate,\n" +
				"   :accountid,\n" +
				"   '1',\n" +
				"   '0',\n" +
				"   sysdate,\n" +
				"   :createuserid,\n" +
				"   sysdate,\n" +
				"   :createuserid)";

				OracleParameter[] parameters = new OracleParameter[]
				{
					new OracleParameter(":staffid", OracleDbType.Int32, entity.StaffID, ParameterDirection.Input),
					new OracleParameter(":adminexatypeid", OracleDbType.Int32, entity.AdminEXATypeID, ParameterDirection.Input),
					new OracleParameter(":amount", OracleDbType.Decimal, entity.Amount, ParameterDirection.Input),
					new OracleParameter(":remarks", OracleDbType.NVarchar2, entity.Remarks, ParameterDirection.Input),
					new OracleParameter(":auditstatus", OracleDbType.Int32, (int)Constant.AuditStatus.Pending, ParameterDirection.Input),
					new OracleParameter(":accountid", OracleDbType.Int32, sUserInfo.AccountID, ParameterDirection.Input),
					new OracleParameter(":createuserid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input),
				};

				int result = tran.ExecuteNonQuery(sql, parameters);

				ServiceResultEntity sre = new ServiceResultEntity();
				if (result <= 0)
				{
					sre.Status = Constant.ServiceResultStatus.NoModifyData;
					return sre;
				}

				tran.Commit();
				return sre;
			}
			catch (Exception ex)
			{
				if (tran != null &&
					tran.ConnState == ConnectionState.Open)
				{
					tran.Rollback();
				}
				throw ex;
			}
			finally
			{
				if (tran != null &&
					tran.ConnState == ConnectionState.Open)
				{
					tran.Disconnect();
				}
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="ids"></param>
		/// <param name="auditStatus"></param>
		/// <param name="sUserInfo"></param>
		/// <returns></returns>
		public static ServiceResultEntity EditAdminEXA(DataTable dt, SUserInfo sUserInfo)
		{
			if (dt == null || dt.Rows.Count == 0)
			{
				ServiceResultEntity sre = new ServiceResultEntity();
				sre.Status = Constant.ServiceResultStatus.NoModifyData;
				return sre;
			}
			IDBTransaction tran = null;
			try
			{
				tran = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);

				string sql = "update tp_pad_adminexa\n" +
				"   set staffid        = :staffid,\n" +
					//"       userid         = :userid,\n" +
				"       adminexatypeid = :adminexatypeid,\n" +
				"       amount         = :amount,\n" +
				"       remarks        = :remarks,\n" +
				"       updateuserid   = :updateuserid\n" +
				" where adminexaid = :adminexaid\n" +
				"   and auditstatus    = " + (int)Constant.AuditStatus.Pending + "\n" +
				"   and optimestamp    = :optimestamp\n" +
				"   and valueflag      = '1'";

				OracleParameter[] parameters = new OracleParameter[]
				{
					new OracleParameter(":adminexaid", OracleDbType.Int32, dt.Rows[0]["adminexaid"], ParameterDirection.Input),
					new OracleParameter(":staffid", OracleDbType.Int32,dt.Rows[0]["staffid"], ParameterDirection.Input),
					new OracleParameter(":adminexatypeid", OracleDbType.Int32, dt.Rows[0]["adminexatypeid"], ParameterDirection.Input),
					new OracleParameter(":amount", OracleDbType.Decimal, dt.Rows[0]["amount"], ParameterDirection.Input),
					new OracleParameter(":remarks", OracleDbType.NVarchar2, dt.Rows[0]["remarks"], ParameterDirection.Input),
					new OracleParameter(":updateuserid", OracleDbType.Int32, sUserInfo.UserID, ParameterDirection.Input),
					new OracleParameter(":optimestamp", OracleDbType.TimeStamp, dt.Rows[0]["optimestamp"], ParameterDirection.Input),
				};

				int result = tran.ExecuteNonQuery(sql, parameters);

				ServiceResultEntity sre = new ServiceResultEntity();
				if (result <= 0)
				{
					sre.Status = Constant.ServiceResultStatus.DataChanged;
					return sre;
				}

				tran.Commit();
				return sre;
			}
			catch (Exception ex)
			{
				if (tran != null &&
					tran.ConnState == ConnectionState.Open)
				{
					tran.Rollback();
				}
				throw ex;
			}
			finally
			{
				if (tran != null &&
					tran.ConnState == ConnectionState.Open)
				{
					tran.Disconnect();
				}
			}
		}

    }
}