using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using OT.IDAL;
using DBUtility;//Please add references
namespace OT.SQLServerDAL
{
    /// <summary>
    /// 資料訪問類:OTB_SYS_SystemSetting
    /// </summary>
    public partial class OTB_SYS_SystemSetting : IOTB_SYS_SystemSetting
    {
        public OTB_SYS_SystemSetting()
        { }
        #region  Method

        /// <summary>
        /// 是否存在該記錄
        /// </summary>
        public bool Exists(string SettingItem)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from OTB_SYS_SystemSetting");
            strSql.Append(" where SettingItem=@SettingItem ");
            SqlParameter[] parameters = {
					
					new SqlParameter("@SettingItem", SqlDbType.NVarChar,50)			};
          
            parameters[0].Value = SettingItem;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        ///  增加一條資料
        /// </summary>
        public bool Add(OT.Model.OTB_SYS_SystemSetting model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [OTB_SYS_SystemSetting]( ");
            strSql.Append(" [SettingItem],[SettingDescription],[SettingValue],[Memo],[Effective],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate] ");
            strSql.Append(" )VALUES( ");
            strSql.Append(" @SettingItem,@SettingDescription,@SettingValue,@Memo,@Effective,@CreateUser,GETDATE(),@CreateUser,GETDATE()	) ");
            SqlParameter[] parameters = {
					
					new SqlParameter("@SettingItem", SqlDbType.NVarChar,50),
					new SqlParameter("@SettingDescription", SqlDbType.NVarChar,100),
					new SqlParameter("@SettingValue", SqlDbType.NVarChar,400),
					new SqlParameter("@Memo", SqlDbType.Text),
					new SqlParameter("@Effective", SqlDbType.Char,1),
					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.SettingItem;
            parameters[1].Value = model.SettingDescription;
            parameters[2].Value = model.SettingValue;
            parameters[3].Value = model.Memo;
            parameters[4].Value = model.Effective;
            parameters[5].Value = model.CreateUser;
            parameters[6].Value = model.CreateDate;
            parameters[7].Value = model.ModifyUser;
            parameters[8].Value = model.ModifyDate;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        ///  更新一條資料
        /// </summary>
        public bool Update(OT.Model.OTB_SYS_SystemSetting model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" UPDATE [OTB_SYS_SystemSetting] SET  ");
            strSql.Append(" [SettingDescription] = @SettingDescription,[SettingValue] = @SettingValue,[Memo] = @Memo,[Effective] = @Effective,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE() ");
            strSql.Append(" WHERE SettingItem=@SettingItem "); 
            SqlParameter[] parameters = {
					
					new SqlParameter("@SettingItem", SqlDbType.NVarChar,50),
					new SqlParameter("@SettingDescription", SqlDbType.NVarChar,100),
					new SqlParameter("@SettingValue", SqlDbType.NVarChar,400),
					new SqlParameter("@Memo", SqlDbType.Text),
					new SqlParameter("@Effective", SqlDbType.Char,1),
					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.SettingItem;
            parameters[1].Value = model.SettingDescription;
            parameters[2].Value = model.SettingValue;
            parameters[3].Value = model.Memo;
            parameters[4].Value = model.Effective;
            parameters[5].Value = model.CreateUser;
            parameters[6].Value = model.CreateDate;
            parameters[7].Value = model.ModifyUser;
            parameters[8].Value = model.ModifyDate;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 刪除一條資料
        /// </summary>
        public bool Delete( string SettingItem)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from OTB_SYS_SystemSetting ");
            strSql.Append(" where SettingItem=@SettingItem ");
            SqlParameter[] parameters = {
					
					new SqlParameter("@SettingItem", SqlDbType.NVarChar,50)			};
            parameters[0].Value = SettingItem;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一個對象實體
        /// </summary>
        public OT.Model.OTB_SYS_SystemSetting GetModel(string SettingItem)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT  ");
            strSql.Append(" SettingItem,SettingDescription,SettingValue,Memo,Effective,CreateUser,CreateDate,ModifyUser,ModifyDate ");
            strSql.Append("  FROM [OTB_SYS_SystemSetting] ");
            strSql.Append(" WHERE SettingItem=@SettingItem ");
            SqlParameter[] parameters = {
					
					new SqlParameter("@SettingItem", SqlDbType.NVarChar,50)			};
         
            parameters[0].Value = SettingItem;
            OT.Model.OTB_SYS_SystemSetting model = new OT.Model.OTB_SYS_SystemSetting();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["SettingItem"] != null && ds.Tables[0].Rows[0]["SettingItem"].ToString() != "")
                {
                    model.SettingItem = ds.Tables[0].Rows[0]["SettingItem"].ToString();
                }
                if (ds.Tables[0].Rows[0]["SettingDescription"] != null && ds.Tables[0].Rows[0]["SettingDescription"].ToString() != "")
                {
                    model.SettingDescription = ds.Tables[0].Rows[0]["SettingDescription"].ToString();
                }
                if (ds.Tables[0].Rows[0]["SettingValue"] != null && ds.Tables[0].Rows[0]["SettingValue"].ToString() != "")
                {
                    model.SettingValue = ds.Tables[0].Rows[0]["SettingValue"].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]["Effective"] != null && ds.Tables[0].Rows[0]["Effective"].ToString() != "")
                {
                    model.Effective = ds.Tables[0].Rows[0]["Effective"].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 DataSet GetList(int StartRecordIndex, int EndRecordIndex, string SettingItem, string SettingDescription, string SettingValue, string Effective, string SortExpression)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT  ");
            strSql.Append("         RowId,SettingItem,SettingDescription,SettingValue,Memo,Effective  ");
            strSql.Append("  FROM   ");
            strSql.Append("  (  ");
            strSql.Append("  	SELECT   ");
            strSql.Append("  		CASE @SortExpression WHEN N'SettingItem' THEN  ");
            strSql.Append("  			(ROW_NUMBER() OVER(Order BY SettingItem))   ");
            strSql.Append("  		WHEN N'SettingItem DESC' THEN  ");
            strSql.Append("  			(ROW_NUMBER() OVER(Order BY SettingItem DESC)) 	  ");
            strSql.Append("  		WHEN N'SettingDescription' THEN   ");
            strSql.Append("  			(ROW_NUMBER() OVER(Order BY SettingDescription)) 	  ");
            strSql.Append("  		WHEN N'SettingDescription DESC' THEN  ");
            strSql.Append("  			(ROW_NUMBER() OVER(Order BY SettingDescription DESC))  ");
            strSql.Append("  		WHEN N'SettingValue' THEN   ");
            strSql.Append("  			(ROW_NUMBER() OVER(Order BY SettingValue)) 	  ");
            strSql.Append("  		WHEN N'SettingValue DESC' THEN  ");
            strSql.Append("  			(ROW_NUMBER() OVER(Order BY SettingValue DESC))  ");
            strSql.Append("  		WHEN N'Effective' THEN   ");
            strSql.Append("  			(ROW_NUMBER() OVER(Order BY Effective)) 	  ");
            strSql.Append("  		WHEN N'Effective DESC' THEN  ");
            strSql.Append("  			(ROW_NUMBER() OVER(Order BY Effective DESC))  ");
            strSql.Append("  		ELSE  ");
            strSql.Append("  			(ROW_NUMBER() OVER(Order BY SettingItem)) 		  ");
            strSql.Append("  	END AS RowId,SettingItem,SettingDescription,SettingValue,Memo,Effective  ");
            strSql.Append("   FROM [OTB_SYS_SystemSetting]  ");
            strSql.Append("   WHERE (SettingItem LIKE @SettingItem OR @SettingItem IS NULL OR @SettingItem='')  ");
            strSql.Append("  	AND (SettingDescription LIKE @SettingDescription OR @SettingDescription IS NULL OR SettingDescription IS NULL OR @SettingDescription='')  ");
            strSql.Append("  	AND (SettingValue LIKE @SettingValue OR @SettingValue IS NULL OR @SettingValue='' OR SettingValue IS NULL)  ");
            strSql.Append("  	AND (Effective = @Effective OR @Effective IS NULL OR @Effective='')  ");
            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("@SettingItem", SqlDbType.NVarChar,50)	
					,new SqlParameter("@SettingDescription", SqlDbType.NVarChar,100)
	                ,new SqlParameter("@SettingValue", 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 = "%" + SettingItem + "%";
            parameters[3].Value = "%" + SettingDescription + "%";
            parameters[4].Value = "%" + SettingValue + "%";
            parameters[5].Value = Effective;
            parameters[6].Value = SortExpression;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 獲得資料總筆數
        /// </summary>
        public int GetListCount(string SettingItem, string SettingDescription, string SettingValue, string Effective)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT ");
            strSql.Append("  COUNT(0) ");
            strSql.Append("  FROM [OTB_SYS_SystemSetting]	  ");
            strSql.Append(" WHERE (SettingItem LIKE @SettingItem OR @SettingItem IS NULL OR @SettingItem='') ");
            strSql.Append(" 	AND (SettingDescription LIKE @SettingDescription OR @SettingDescription IS NULL OR SettingDescription IS NULL OR @SettingDescription='') ");
            strSql.Append(" 	AND (SettingValue LIKE @SettingValue  OR @SettingValue IS NULL OR @SettingValue='' OR SettingValue IS NULL) ");
            strSql.Append(" 	AND (Effective = @Effective OR @Effective IS NULL OR @Effective='') ");
            SqlParameter[] parameters = {
					new SqlParameter("@SettingItem", SqlDbType.NVarChar,50)	
					,new SqlParameter("@SettingDescription", SqlDbType.NVarChar,100)
	                ,new SqlParameter("@SettingValue", SqlDbType.NVarChar,200)	
					,new SqlParameter("@Effective", SqlDbType.Char,1)		
                                        };
            parameters[0].Value = "%" + SettingItem + "%";
            parameters[1].Value = "%" + SettingDescription + "%";
            parameters[2].Value = "%" + SettingValue + "%";
            parameters[3].Value = Effective;
            return (int)DbHelperSQL.GetSingle(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 獲得資料列表
        /// </summary>
        public DataSet GetAllList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT  ");
            strSql.Append("  	SettingItem,SettingDescription,SettingValue ");
            strSql.Append("  FROM [OTB_SYS_SystemSetting] ");
            strSql.Append("  WHERE Effective = 'Y' ");
            SqlParameter[] parameters = new SqlParameter[0];
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        public DataSet GetMitakeInfo()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" DECLARE @UserID NVARCHAR(50),");
            strSql.Append("      @UserPSWD NVARCHAR(50)");
            strSql.Append(" SET @UserID=(SELECT  SettingValue FROM    OTB_SYS_SystemSetting Where SettingItem='Mitake_UserID' And Effective='Y');");
            strSql.Append(" SET @UserPSWD=(SELECT  SettingValue FROM    OTB_SYS_SystemSetting Where SettingItem='Mitake_UserPSWD' And Effective='Y');");
            strSql.Append(" SELECT @UserID As UserID ,@UserPSWD As UserPSWD ");
            SqlParameter[] parameters = new SqlParameter[0];
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }
        #endregion  Method
    }
}