You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
313 lines
15 KiB
313 lines
15 KiB
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
|
|
}
|
|
}
|
|
|