using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using OT.IDAL;
using DBUtility;//Please add references
using System.Collections.Generic;
namespace OT.SQLServerDAL
{
    /// <summary>
    /// 資料訪問類:OTB_SYS_Arguments
    /// </summary>
    public partial class OTB_SYS_Arguments : IOTB_SYS_Arguments
    {
        public OTB_SYS_Arguments()
        { }
        #region  Method
        /// <summary>
        /// 是否存在該記錄
        /// </summary>
        public bool Exists(string ArgumentClassID, string ArgumentID)
        {
            int rowsAffected;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from OTB_SYS_Arguments");
            strSql.Append(" where ArgumentClassID=@ArgumentClassID and ArgumentID=@ArgumentID ");
            SqlParameter[] parameters = {
					
					new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10),
					new SqlParameter("@ArgumentID", SqlDbType.VarChar,200)
							};
          
            parameters[0].Value = ArgumentClassID;
            parameters[1].Value = ArgumentID;
        
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
            //int result = DbHelperSQL.RunProcedure("OSP_OTB_SYS_Arguments_Exists", parameters, out rowsAffected);
            //if (result == 1)
            //{
            //    return true;
            //}
            //else
            //{
            //    return false;
            //}
        }

        /// <summary>
        ///  增加一條資料
        /// </summary>
        public bool Add(OT.Model.OTB_SYS_Arguments model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [OTB_SYS_Arguments]( ");
            strSql.Append(" [ArgumentClassID],[ArgumentID],[ArgumentValue],[OrderByValue],[DelStatus],[Effective],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate] ");
            strSql.Append(" )VALUES( ");
            strSql.Append(" @ArgumentClassID,@ArgumentID,@ArgumentValue,@OrderByValue,@DelStatus,@Effective,@Memo,@CreateUser,GETDATE(),@CreateUser,GETDATE()) ");
            SqlParameter[] parametersAdd = {
					new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10),
					new SqlParameter("@ArgumentID", SqlDbType.VarChar,200),
					new SqlParameter("@ArgumentValue", SqlDbType.NVarChar,200),
					new SqlParameter("@OrderByValue", SqlDbType.Int,4),
					new SqlParameter("@LevelOfArgument", SqlDbType.Int,4),
					new SqlParameter("@ParentArgument", SqlDbType.VarChar,200),
					new SqlParameter("@DelStatus", SqlDbType.Char,1),
					new SqlParameter("@Effective", SqlDbType.Char,1),
					new SqlParameter("@Memo", SqlDbType.Text),
					new SqlParameter("@CreateUser", SqlDbType.VarChar,50),
					new SqlParameter("@CreateDate", SqlDbType.DateTime),
					new SqlParameter("@ModifyUser", SqlDbType.VarChar,50),
					new SqlParameter("@ModifyDate", SqlDbType.DateTime)};
          
            parametersAdd[0].Value = model.ArgumentClassID;
            parametersAdd[1].Value = model.ArgumentID;
            parametersAdd[2].Value = model.ArgumentValue;
            parametersAdd[3].Value = model.OrderByValue;
            parametersAdd[4].Value = model.LevelOfArgument;
            parametersAdd[5].Value = model.ParentArgument;
            parametersAdd[6].Value = model.DelStatus;
            parametersAdd[7].Value = model.Effective;
            parametersAdd[8].Value = model.Memo;
            parametersAdd[9].Value = model.CreateUser;
            parametersAdd[10].Value = model.CreateDate;
            parametersAdd[11].Value = model.ModifyUser;
            parametersAdd[12].Value = model.ModifyDate;

            List<CommandInfo> lstCommandInfo = new List<CommandInfo>(); //定義事物執行的所有SQL
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append(" DECLARE @strSQL NVARCHAR(1000) ");
            strSql2.Append(" IF CONVERT(INT, @NewOrderByValue) > CONVERT(INT,@OldOrderByValue) ");
            strSql2.Append(" 	BEGIN ");                   //由小變大，OLD<-1<=NEW 
            strSql2.Append(" 		SET @strSQL=N'UPDATE '+@TableName+' ");
            strSql2.Append(" 		SET '+@FeildName+' = ISNULL('+@FeildName+',0) - 1  ");
            strSql2.Append(" 		WHERE '+@FeildName+' >= '+@OldOrderByValue  + ' AND '+@FeildName+'<='+@NewOrderByValue+' ' ");
            strSql2.Append(" 		IF @Where <>'' ");
            strSql2.Append(" 			SET @strSQL =@strSQL + ' AND ' + @Where ");
            strSql2.Append(" 		print @strSQL ");
            strSql2.Append(" 		exec sp_executesql @strSQL ");
            strSql2.Append(" 	END ");
            strSql2.Append(" ELSE	 ");
            strSql2.Append(" 	BEGIN ");  //由大變小，@NewOrderByValue<=+1<@OldOrderByValue
            strSql2.Append(" 		SET @strSQL=N'UPDATE '+@TableName+' ");
            strSql2.Append(" 		SET '+@FeildName+' = ISNULL('+@FeildName+',0) + 1  ");
            strSql2.Append(" 		WHERE '+@FeildName+' <= '+@OldOrderByValue  + ' AND '+@FeildName+'>='+@NewOrderByValue+' ' ");
            strSql2.Append(" 		IF @Where <>'' ");
            strSql2.Append(" 			SET @strSQL =@strSQL + ' AND ' + @Where ");
            strSql2.Append(" 		print @strSQL ");
            strSql2.Append(" 		exec sp_executesql @strSQL ");
            strSql2.Append(" END ");
            SqlParameter[] parameters = {
					new SqlParameter("@OldOrderByValue", SqlDbType.NVarChar,10),
					new SqlParameter("@NewOrderByValue", SqlDbType.NVarChar,10),
					new SqlParameter("@FeildName", SqlDbType.NVarChar,50),
					new SqlParameter("@TableName", SqlDbType.NVarChar,50),
					new SqlParameter("@Where", SqlDbType.NVarChar,500)};
            parameters[0].Value = model.OldOrderByValue;
            parameters[1].Value = model.OrderByValue;
            parameters[2].Value = "OrderByValue";
            parameters[3].Value = "OTB_SYS_Arguments";
            parameters[4].Value = "ArgumentClassID='" + model.ArgumentClassID + "'  AND ISNULL(DelStatus,'N') = 'N'";
            CommandInfo o_CommandInfo = new CommandInfo();  //定義每一個SQL，代表修改
            //o_CommandInfo.CommandText = "OSP_Common_UpdateOrderByValue";
            o_CommandInfo.CommandText = strSql2.ToString();
            o_CommandInfo.Parameters = parameters;
            lstCommandInfo.Add(o_CommandInfo);  //添加當前的SQL

            o_CommandInfo = new CommandInfo();  //定義每一個SQL，代表新增，雖然參數名稱相同，但是經過new以後就是新的對象
            //o_CommandInfo.CommandText = "OSP_OTB_SYS_Arguments_ADD";
            o_CommandInfo.CommandText = strSql.ToString();
            o_CommandInfo.Parameters = parametersAdd;
            lstCommandInfo.Add(o_CommandInfo);  //添加當前的SQL

            if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        ///  更新一條資料
        /// </summary>
        public bool Update(OT.Model.OTB_SYS_Arguments model)
        {
          

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" UPDATE [OTB_SYS_Arguments] SET  ");
            strSql.Append(" [ArgumentValue] = @ArgumentValue,[OrderByValue] = @OrderByValue,[DelStatus] = @DelStatus,[Effective] = @Effective,[Memo] = @Memo,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE() ");
            strSql.Append(" WHERE ArgumentClassID=@ArgumentClassID and ArgumentID=@ArgumentID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10),
					new SqlParameter("@ArgumentID", SqlDbType.VarChar,200),
					new SqlParameter("@ArgumentValue", SqlDbType.NVarChar,200),
					new SqlParameter("@OrderByValue", SqlDbType.Int,4),
					new SqlParameter("@LevelOfArgument", SqlDbType.Int,4),
					new SqlParameter("@ParentArgument", SqlDbType.VarChar,200),
					new SqlParameter("@DelStatus", SqlDbType.Char,1),
					new SqlParameter("@Effective", SqlDbType.Char,1),
					new SqlParameter("@Memo", SqlDbType.Text),
					new SqlParameter("@CreateUser", SqlDbType.VarChar,50),
					new SqlParameter("@CreateDate", SqlDbType.DateTime),
					new SqlParameter("@ModifyUser", SqlDbType.VarChar,50),
					new SqlParameter("@ModifyDate", SqlDbType.DateTime)};
           
            parameters[0].Value = model.ArgumentClassID;
            parameters[1].Value = model.ArgumentID;
     
            parameters[2].Value = model.ArgumentValue;
            parameters[3].Value = model.OrderByValue;
            parameters[4].Value = model.LevelOfArgument;
            parameters[5].Value = model.ParentArgument;
            parameters[6].Value = model.DelStatus;
            parameters[7].Value = model.Effective;
            parameters[8].Value = model.Memo;
            parameters[9].Value = model.CreateUser;
            parameters[10].Value = model.CreateDate;
            parameters[11].Value = model.ModifyUser;
            parameters[12].Value = model.ModifyDate;

            List<CommandInfo> lstCommandInfo = new List<CommandInfo>(); //定義事物執行的所有SQL
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append(" DECLARE @strSQL NVARCHAR(1000) ");
            strSql2.Append(" IF CONVERT(INT, @NewOrderByValue) > CONVERT(INT,@OldOrderByValue) ");
            strSql2.Append(" 	BEGIN ");                   //由小變大，OLD<-1<=NEW 
            strSql2.Append(" 		SET @strSQL=N'UPDATE '+@TableName+' ");
            strSql2.Append(" 		SET '+@FeildName+' = ISNULL('+@FeildName+',0) - 1  ");
            strSql2.Append(" 		WHERE '+@FeildName+' >= '+@OldOrderByValue  + ' AND '+@FeildName+'<='+@NewOrderByValue+' ' ");
            strSql2.Append(" 		IF @Where <>'' ");
            strSql2.Append(" 			SET @strSQL =@strSQL + ' AND ' + @Where ");
            strSql2.Append(" 		print @strSQL ");
            strSql2.Append(" 		exec sp_executesql @strSQL ");
            strSql2.Append(" 	END ");
            strSql2.Append(" ELSE	 ");
            strSql2.Append(" 	BEGIN ");  //由大變小，@NewOrderByValue<=+1<@OldOrderByValue
            strSql2.Append(" 		SET @strSQL=N'UPDATE '+@TableName+' ");
            strSql2.Append(" 		SET '+@FeildName+' = ISNULL('+@FeildName+',0) + 1  ");
            strSql2.Append(" 		WHERE '+@FeildName+' <= '+@OldOrderByValue  + ' AND '+@FeildName+'>='+@NewOrderByValue+' ' ");
            strSql2.Append(" 		IF @Where <>'' ");
            strSql2.Append(" 			SET @strSQL =@strSQL + ' AND ' + @Where ");
            strSql2.Append(" 		print @strSQL ");
            strSql2.Append(" 		exec sp_executesql @strSQL ");
            strSql2.Append(" END ");
            SqlParameter[] parametersChangeOrder = {
                    new SqlParameter("@OldOrderByValue", SqlDbType.NVarChar,10),
                    new SqlParameter("@NewOrderByValue", SqlDbType.NVarChar,10),
                    new SqlParameter("@FeildName", SqlDbType.NVarChar,50),
                    new SqlParameter("@TableName", SqlDbType.NVarChar,50),
                    new SqlParameter("@Where", SqlDbType.NVarChar,500)};
            parametersChangeOrder[0].Value = model.OldOrderByValue;
            parametersChangeOrder[1].Value = model.OrderByValue;
            parametersChangeOrder[2].Value = "OrderByValue";
            parametersChangeOrder[3].Value = "OTB_SYS_Arguments";
            parametersChangeOrder[4].Value = "ArgumentClassID='" + model.ArgumentClassID + "' AND ISNULL(DelStatus,'N') = 'N'";
            CommandInfo o_CommandInfo = new CommandInfo();  //定義每一個SQL，代表修改
            //o_CommandInfo.CommandText = "OSP_Common_UpdateOrderByValue";
            o_CommandInfo.CommandText = strSql2.ToString();
            o_CommandInfo.Parameters = parametersChangeOrder;
            lstCommandInfo.Add(o_CommandInfo);  //添加當前的SQL

            o_CommandInfo = new CommandInfo();  //定義每一個SQL，代表新增，雖然參數名稱相同，但是經過new以後就是新的對象
            //o_CommandInfo.CommandText = "OSP_OTB_SYS_Arguments_Update";
            o_CommandInfo.CommandText = strSql.ToString();
            o_CommandInfo.Parameters = parameters;
            lstCommandInfo.Add(o_CommandInfo);  //添加當前的SQL

            if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 刪除一條資料
        /// </summary>
        public bool Delete(OT.Model.OTB_SYS_Arguments model)
        {
            int rowsAffected = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" DECLARE @OrderByValue INT ");
            strSql.Append(" SELECT @OrderByValue = OrderByValue ");
            strSql.Append(" FROM [OTB_SYS_Arguments]  ");
            strSql.Append(" WHERE ArgumentID=@ArgumentID ");
            strSql.Append(" DELETE [OTB_SYS_Arguments]  ");
            strSql.Append(" WHERE ArgumentClassID=@ArgumentClassID and ArgumentID=@ArgumentID ");
           
            SqlParameter[] parameters = {
					
					new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10),
					new SqlParameter("@ArgumentID", SqlDbType.VarChar,200)			};
           
            parameters[0].Value = model.ArgumentClassID;
            parameters[1].Value = model.ArgumentID;
          

            List<CommandInfo> lstCommandInfo = new List<CommandInfo>(); //定義事物執行的所有SQL

            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append(" DECLARE @strSQL NVARCHAR(1000) ");
            strSql2.Append(" IF CONVERT(INT, @NewOrderByValue) > CONVERT(INT,@OldOrderByValue) ");
            strSql2.Append(" 	BEGIN ");                   //由小變大，OLD<-1<=NEW 
            strSql2.Append(" 		SET @strSQL=N'UPDATE '+@TableName+' ");
            strSql2.Append(" 		SET '+@FeildName+' = ISNULL('+@FeildName+',0) - 1  ");
            strSql2.Append(" 		WHERE '+@FeildName+' >= '+@OldOrderByValue  + ' AND '+@FeildName+'<='+@NewOrderByValue+' ' ");
            strSql2.Append(" 		IF @Where <>'' ");
            strSql2.Append(" 			SET @strSQL =@strSQL + ' AND ' + @Where ");
            strSql2.Append(" 		print @strSQL ");
            strSql2.Append(" 		exec sp_executesql @strSQL ");
            strSql2.Append(" 	END ");
            strSql2.Append(" ELSE	 ");
            strSql2.Append(" 	BEGIN ");  //由大變小，@NewOrderByValue<=+1<@OldOrderByValue
            strSql2.Append(" 		SET @strSQL=N'UPDATE '+@TableName+' ");
            strSql2.Append(" 		SET '+@FeildName+' = ISNULL('+@FeildName+',0) + 1  ");
            strSql2.Append(" 		WHERE '+@FeildName+' <= '+@OldOrderByValue  + ' AND '+@FeildName+'>='+@NewOrderByValue+' ' ");
            strSql2.Append(" 		IF @Where <>'' ");
            strSql2.Append(" 			SET @strSQL =@strSQL + ' AND ' + @Where ");
            strSql2.Append(" 		print @strSQL ");
            strSql2.Append(" 		exec sp_executesql @strSQL ");
            strSql2.Append(" END ");
            SqlParameter[] parametersChangeOrder = {
                    new SqlParameter("@OldOrderByValue", SqlDbType.NVarChar,10),
                    new SqlParameter("@NewOrderByValue", SqlDbType.NVarChar,10),
                    new SqlParameter("@FeildName", SqlDbType.NVarChar,50),
                    new SqlParameter("@TableName", SqlDbType.NVarChar,50),
                    new SqlParameter("@Where", SqlDbType.NVarChar,500)};
            parametersChangeOrder[0].Value = model.OldOrderByValue;
            parametersChangeOrder[1].Value = model.OrderByValue;
            parametersChangeOrder[2].Value = "OrderByValue";
            parametersChangeOrder[3].Value = "OTB_SYS_Arguments";
            parametersChangeOrder[4].Value = "ArgumentClassID='" + model.ArgumentClassID + "' AND ISNULL(DelStatus,'N') = 'N'";
            CommandInfo o_CommandInfo = new CommandInfo();  //定義每一個SQL，代表修改
            //o_CommandInfo.CommandText = "OSP_Common_UpdateOrderByValue";
            o_CommandInfo.CommandText = strSql2.ToString();
            o_CommandInfo.Parameters = parametersChangeOrder;
            lstCommandInfo.Add(o_CommandInfo);  //添加當前的SQL

            o_CommandInfo = new CommandInfo();  //定義每一個SQL，代表新增，雖然參數名稱相同，但是經過new以後就是新的對象
            //o_CommandInfo.CommandText = "OSP_OTB_SYS_Arguments_Update";
            o_CommandInfo.CommandText = strSql.ToString();
            o_CommandInfo.Parameters = parameters;
            lstCommandInfo.Add(o_CommandInfo);  //添加當前的SQL

            //DbHelperSQL.RunProcedure("OSP_OTB_SYS_Arguments_Delete", parameters, out rowsAffected);
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一個對象實體
        /// </summary>
        public OT.Model.OTB_SYS_Arguments GetModel(string ArgumentClassID, string ArgumentID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 ArgumentClassID,ArgumentID,ArgumentValue,MaxNumber,OrderByValue,DelStatus,Effective,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate from OTB_SYS_Arguments ");
            strSql.Append(" where ArgumentClassID=@ArgumentClassID and ArgumentID=@ArgumentID ");
            SqlParameter[] parameters = {
					
					new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10),
					new SqlParameter("@ArgumentID", SqlDbType.VarChar,200)
					};
          
            parameters[0].Value = ArgumentClassID;
            parameters[1].Value = ArgumentID;
          

            OT.Model.OTB_SYS_Arguments model = new OT.Model.OTB_SYS_Arguments();
            //DataSet ds = DbHelperSQL.RunProcedure("OSP_OTB_SYS_Arguments_GetModel", parameters, "ds");
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                
                if (ds.Tables[0].Rows[0]["ArgumentClassID"] != null && ds.Tables[0].Rows[0]["ArgumentClassID"].ToString() != "")
                {
                    model.ArgumentClassID = ds.Tables[0].Rows[0]["ArgumentClassID"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ArgumentID"] != null && ds.Tables[0].Rows[0]["ArgumentID"].ToString() != "")
                {
                    model.ArgumentID = ds.Tables[0].Rows[0]["ArgumentID"].ToString();
                }
               
                if (ds.Tables[0].Rows[0]["ArgumentValue"] != null && ds.Tables[0].Rows[0]["ArgumentValue"].ToString() != "")
                {
                    model.ArgumentValue = ds.Tables[0].Rows[0]["ArgumentValue"].ToString();
                }
                if (ds.Tables[0].Rows[0]["OrderByValue"] != null && ds.Tables[0].Rows[0]["OrderByValue"].ToString() != "")
                {
                    model.OrderByValue = int.Parse(ds.Tables[0].Rows[0]["OrderByValue"].ToString());
                }
                //if (ds.Tables[0].Rows[0]["LevelOfArgument"] != null && ds.Tables[0].Rows[0]["LevelOfArgument"].ToString() != "")
                //{
                //    model.LevelOfArgument = int.Parse(ds.Tables[0].Rows[0]["LevelOfArgument"].ToString());
                //}
                //if (ds.Tables[0].Rows[0]["ParentArgument"] != null && ds.Tables[0].Rows[0]["ParentArgument"].ToString() != "")
                //{
                //    model.ParentArgument = ds.Tables[0].Rows[0]["ParentArgument"].ToString();
                //}
                if (ds.Tables[0].Rows[0]["DelStatus"] != null && ds.Tables[0].Rows[0]["DelStatus"].ToString() != "")
                {
                    model.DelStatus = ds.Tables[0].Rows[0]["DelStatus"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Effective"] != null && ds.Tables[0].Rows[0]["Effective"].ToString() != "")
                {
                    model.Effective = ds.Tables[0].Rows[0]["Effective"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Memo"] != null && ds.Tables[0].Rows[0]["Memo"].ToString() != "")
                {
                    model.Memo = ds.Tables[0].Rows[0]["Memo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CreateUser"] != null && ds.Tables[0].Rows[0]["CreateUser"].ToString() != "")
                {
                    model.CreateUser = ds.Tables[0].Rows[0]["CreateUser"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CreateDate"] != null && ds.Tables[0].Rows[0]["CreateDate"].ToString() != "")
                {
                    model.CreateDate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ModifyUser"] != null && ds.Tables[0].Rows[0]["ModifyUser"].ToString() != "")
                {
                    model.ModifyUser = ds.Tables[0].Rows[0]["ModifyUser"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ModifyDate"] != null && ds.Tables[0].Rows[0]["ModifyDate"].ToString() != "")
                {
                    model.ModifyDate = DateTime.Parse(ds.Tables[0].Rows[0]["ModifyDate"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一個對象實體
        /// </summary>
        public OT.Model.OTB_SYS_Arguments DataRowToModel(DataRow row)
        {
            OT.Model.OTB_SYS_Arguments model = new OT.Model.OTB_SYS_Arguments();
            if (row != null)
            {
                if (row["ArgumentClassID"] != null)
                {
                    model.ArgumentClassID = row["ArgumentClassID"].ToString();
                }
                if (row["ArgumentID"] != null)
                {
                    model.ArgumentID = row["ArgumentID"].ToString();
                }
                if (row["ArgumentValue"] != null)
                {
                    model.ArgumentValue = row["ArgumentValue"].ToString();
                }
                if (row["MaxNumber"] != null && row["MaxNumber"].ToString() != "")
                {
                    model.MaxNumber = int.Parse(row["MaxNumber"].ToString());
                }
                if (row["OrderByValue"] != null && row["OrderByValue"].ToString() != "")
                {
                    model.OrderByValue = int.Parse(row["OrderByValue"].ToString());
                }
                if (row["DelStatus"] != null)
                {
                    model.DelStatus = row["DelStatus"].ToString();
                }
                if (row["Effective"] != null)
                {
                    model.Effective = row["Effective"].ToString();
                }
                if (row["Memo"] != null)
                {
                    model.Memo = row["Memo"].ToString();
                }
                if (row["CreateUser"] != null)
                {
                    model.CreateUser = row["CreateUser"].ToString();
                }
                if (row["CreateDate"] != null && row["CreateDate"].ToString() != "")
                {
                    model.CreateDate = DateTime.Parse(row["CreateDate"].ToString());
                }
                if (row["ModifyUser"] != null)
                {
                    model.ModifyUser = row["ModifyUser"].ToString();
                }
                if (row["ModifyDate"] != null && row["ModifyDate"].ToString() != "")
                {
                    model.ModifyDate = DateTime.Parse(row["ModifyDate"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 獲得資料列表
        /// </summary>
        public DataSet GetList(int StartRecordIndex, int EndRecordIndex, string ArgumentClassID, string ArgumentID, string ArgumentValue, string Effective, string SortExpression)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT    ");
            strSql.Append("  	 RowId,ArgumentClassID,ArgumentClassName,ArgumentID,ArgumentValue,Effective,OrderByValue   ");
            strSql.Append("  	,(SELECT COUNT(0) FROM OTB_SYS_Arguments WHERE ArgumentClassID=AA.ArgumentClassID) AS ClassCount   ");
            strSql.Append("  FROM    ");
            strSql.Append("  (   ");
            strSql.Append("  	SELECT    ");
            strSql.Append("  		CASE @SortExpression WHEN N'ArgumentClassName' THEN   ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY ptm.ArgumentClassName))    ");
            strSql.Append("  			WHEN N'ArgumentClassName DESC' THEN   ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY ptm.ArgumentClassName DESC)) 	   ");
            strSql.Append("  			WHEN N'ArgumentID' THEN    ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY ArgumentID)) 	   ");
            strSql.Append("  			WHEN N'ArgumentID DESC' THEN   ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY ArgumentID DESC))   ");
            strSql.Append("  			WHEN N'ArgumentValue' THEN    ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY ArgumentValue)) 	   ");
            strSql.Append("  			WHEN N'ArgumentValue DESC' THEN   ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY ArgumentValue DESC))   ");
            strSql.Append("  			WHEN N'Effective' THEN    ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY tm.Effective)) 	   ");
            strSql.Append("  			WHEN N'Effective DESC' THEN   ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY tm.Effective DESC))   ");
            strSql.Append("  				WHEN N'OrderByValue' THEN    ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY tm.OrderByValue)) 	   ");
            strSql.Append("  			WHEN N'OrderByValue DESC' THEN   ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY tm.OrderByValue DESC))   ");
            strSql.Append("  			ELSE   ");
            strSql.Append("  				(ROW_NUMBER() OVER(Order BY tm.ArgumentClassID,tm.OrderByValue)) 	   ");
            strSql.Append("  		END AS RowId,ptm.ArgumentClassID,ptm.ArgumentClassName,ArgumentID   ");
            strSql.Append("  		,ArgumentValue,tm.Effective,tm.OrderByValue   ");
            strSql.Append("  	 FROM OTB_SYS_Argumentclass as ptm   ");
            strSql.Append("  		LEFT JOIN OTB_SYS_Arguments as tm on ptm.ArgumentClassID = tm.ArgumentClassID    ");
            strSql.Append("  	WHERE (ptm.ArgumentClassID = @ArgumentClassID OR @ArgumentClassID IS NULL OR @ArgumentClassID='')   ");
            strSql.Append("  	   AND (ArgumentID LIKE @ArgumentID OR @ArgumentID IS NULL OR @ArgumentID='' OR @ArgumentID = '%%')   ");
            strSql.Append("  		AND (ArgumentValue LIKE @ArgumentValue OR @ArgumentValue IS NULL OR @ArgumentValue='' OR @ArgumentValue = '%%')   ");
            strSql.Append("  		AND (tm.Effective = @Effective OR @Effective IS NULL OR @Effective='')   ");
            strSql.Append("  		AND tm.[DelStatus] = 'N'   ");
            strSql.Append("  		AND ptm.[DelStatus] = 'N'   ");
            strSql.Append("  		) AS AA   ");
            strSql.Append("  WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex    ");
            strSql.Append("  ORDER BY RowId   ");
            SqlParameter[] parameters = {
                    new SqlParameter("@StartRecordIndex", SqlDbType.Int)	
					,new SqlParameter("@EndRecordIndex", SqlDbType.Int)	
					,new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10)	
					,new SqlParameter("@ArgumentID", SqlDbType.NVarChar,20)
	                ,new SqlParameter("@ArgumentValue", SqlDbType.NVarChar,200)	
					,new SqlParameter("@Effective", SqlDbType.Char,1)		
	                ,new SqlParameter("@SortExpression", SqlDbType.NVarChar,500)
                                        };
            parameters[0].Value = StartRecordIndex;
            parameters[1].Value = EndRecordIndex;
            parameters[2].Value = ArgumentClassID;
            parameters[3].Value = "%" + ArgumentID + "%";
            parameters[4].Value = "%" + ArgumentValue + "%";
            parameters[5].Value = Effective;
            parameters[6].Value = SortExpression;
            //return DbHelperSQL.RunProcedure("OSP_OTB_SYS_Arguments_GetListByIdEff", parameters, "ds");
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 獲得資料總筆數
        /// </summary>
        public int GetListCount(string ArgumentClassID, string ArgumentID, string ArgumentValue, string Effective)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" 	 SELECT  ");
            strSql.Append(" 	 COUNT(0) ");
            strSql.Append(" 	 FROM  ");
            strSql.Append(" 	 OTB_SYS_Argumentclass as ptm ");
            strSql.Append("      inner join OTB_SYS_Arguments as tm on ptm.ArgumentClassID = tm.ArgumentClassID 	  ");
            strSql.Append(" 	 WHERE (ptm.ArgumentClassID = @ArgumentClassID OR @ArgumentClassID IS NULL OR @ArgumentClassID='') ");
            strSql.Append(" 		AND (tm.ArgumentID LIKE @ArgumentID OR @ArgumentID IS NULL OR @ArgumentID='' OR @ArgumentID = '%%') ");
            strSql.Append(" 		AND (tm.ArgumentValue LIKE @ArgumentValue OR @ArgumentValue IS NULL OR @ArgumentValue='' OR @ArgumentValue = '%%') ");
            strSql.Append(" 		AND (tm.Effective = @Effective OR @Effective IS NULL OR @Effective='') ");
            strSql.Append(" 		AND tm.[DelStatus] = 'N' ");
            strSql.Append(" 		AND ptm.[DelStatus] = 'N' ");
            SqlParameter[] parameters = {
					new SqlParameter("@ArgumentClassID", SqlDbType.VarChar,10)	
				    ,new SqlParameter("@ArgumentID", SqlDbType.NVarChar,20)
	                ,new SqlParameter("@ArgumentValue", SqlDbType.NVarChar,200)	
					,new SqlParameter("@Effective", SqlDbType.Char,1)
                                        };
            parameters[0].Value = ArgumentClassID;
            parameters[1].Value = "%" + ArgumentID + "%";
            parameters[2].Value = "%" + ArgumentValue + "%";
            parameters[3].Value = Effective;
            //return (int)DbHelperSQL.GetSingle("OSP_OTB_SYS_Arguments_GetCount", parameters);
            return (int)DbHelperSQL.GetSingle(strSql.ToString(), parameters);
        }
        #endregion  Method
    }
}