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.
356 lines
16 KiB
356 lines
16 KiB
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_Authorize
|
|
/// </summary>
|
|
public partial class OTB_SYS_Authorize : IOTB_SYS_Authorize
|
|
{
|
|
public OTB_SYS_Authorize()
|
|
{ }
|
|
#region Method
|
|
/// <summary>
|
|
/// 是否存在該記錄
|
|
/// </summary>
|
|
public bool Exists( string RuleID, string ProgramID)
|
|
{
|
|
int rowsAffected;
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select count(1) from OTB_SYS_Authorize");
|
|
strSql.Append(" where RuleID=@RuleID and ProgramID=@ProgramID ");
|
|
SqlParameter[] parameters = {
|
|
new SqlParameter("@RuleID", SqlDbType.VarChar,20),
|
|
new SqlParameter("@ProgramID", SqlDbType.VarChar,200) };
|
|
parameters[0].Value = RuleID;
|
|
parameters[1].Value = ProgramID;
|
|
|
|
return DbHelperSQL.Exists(strSql.ToString(), parameters);
|
|
//int result = DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_Exists", parameters, out rowsAffected);
|
|
//if (result == 1)
|
|
//{
|
|
// return true;
|
|
//}
|
|
//else
|
|
//{
|
|
// return false;
|
|
//}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 增加一條資料
|
|
/// </summary>
|
|
public bool Add(OT.Model.OTB_SYS_Authorize model)
|
|
{
|
|
int rowsAffected;
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(" INSERT INTO [OTB_SYS_Authorize]( ");
|
|
strSql.Append(" [RuleID],[ProgramID],[AllowRight],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate] ");
|
|
strSql.Append(" )VALUES( ");
|
|
strSql.Append(" @RuleID,@ProgramID,@AllowRight,@Memo,@CreateUser,GETDATE(),@CreateUser,GETDATE() ) ");
|
|
SqlParameter[] parameters = {
|
|
new SqlParameter("@RuleID", SqlDbType.VarChar,20),
|
|
new SqlParameter("@ProgramID", SqlDbType.VarChar,200),
|
|
new SqlParameter("@AllowRight", SqlDbType.VarChar,200),
|
|
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.RuleID;
|
|
parameters[1].Value = model.ProgramID;
|
|
parameters[2].Value = model.AllowRight;
|
|
parameters[3].Value = model.Memo;
|
|
parameters[4].Value = model.CreateUser;
|
|
parameters[5].Value = model.CreateDate;
|
|
parameters[6].Value = model.ModifyUser;
|
|
parameters[7].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_Authorize model)
|
|
{
|
|
int rowsAffected = 0;
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(" update [OTB_SYS_Authorize] set ");
|
|
strSql.Append("[ProgramID]=@ProgramID,[AllowRight]=@AllowRight,[Memo]=@Memo,[ModifyUser]=@ModifyUser,[ModifyDate]=GETDATE() ");
|
|
strSql.Append(" where [RuleID]=@RuleID ");
|
|
SqlParameter[] parameters = {
|
|
new SqlParameter("@RuleID", SqlDbType.VarChar,20),
|
|
new SqlParameter("@ProgramID", SqlDbType.VarChar,200),
|
|
new SqlParameter("@AllowRight", SqlDbType.VarChar,200),
|
|
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.RuleID;
|
|
parameters[1].Value = model.ProgramID;
|
|
parameters[2].Value = model.AllowRight;
|
|
parameters[3].Value = model.Memo;
|
|
parameters[4].Value = model.CreateUser;
|
|
parameters[5].Value = model.CreateDate;
|
|
parameters[6].Value = model.ModifyUser;
|
|
parameters[7].Value = model.ModifyDate;
|
|
|
|
//DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_Update", parameters, out rowsAffected);
|
|
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
|
|
if (rows > 0)
|
|
{
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 刪除一條資料
|
|
/// </summary>
|
|
public bool Delete( string RuleID, string ProgramID)
|
|
{
|
|
int rowsAffected = 0;
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(" DELETE [TB_SYS_Authorize] ");
|
|
strSql.Append(" WHERE RuleID=@RuleID and ProgramID=@ProgramID ");
|
|
SqlParameter[] parameters = {
|
|
new SqlParameter("@RuleID", SqlDbType.VarChar,20),
|
|
new SqlParameter("@ProgramID", SqlDbType.VarChar,200) };
|
|
|
|
parameters[0].Value = RuleID;
|
|
parameters[1].Value = ProgramID;
|
|
//DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_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_Authorize GetModel( string RuleID, string ProgramID)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(" DECLARE @AllowRight NVARCHAR(4000) ");
|
|
strSql.Append(" SET @AllowRight='' ");
|
|
strSql.Append(" SELECT @AllowRight=@AllowRight+'|'+RTRIM(AllowRight) "); //看看是不是能去掉重複的
|
|
strSql.Append(" FROM [OTB_SYS_Authorize] ");
|
|
strSql.Append(" WHERE RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RuleID) ");
|
|
strSql.Append(" and ProgramID=@ProgramID ");
|
|
strSql.Append(" PRINT @AllowRight ");
|
|
strSql.Append(" SELECT DISTINCT ");
|
|
strSql.Append(" @RuleID AS RuleID,A.ProgramID,@AllowRight AS AllowRight,'' AS Memo,'' AS CreateUser,'' AS CreateDate,'' AS ModifyUser,'' AS ModifyDate,P.Effective ");//Add by Alina 20141029 添加查詢出來一個程式的狀態欄位
|
|
strSql.Append(" FROM [OTB_SYS_Authorize] A ");
|
|
strSql.Append(" INNER JOIN ");
|
|
strSql.Append(" [OTB_SYS_ProgramList] P ");
|
|
strSql.Append(" ON A.ProgramID = P.ProgramID ");
|
|
strSql.Append(" WHERE RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RuleID) ");
|
|
strSql.Append(" and A.ProgramID=@ProgramID AND P.ProgramType <> 'S' ");
|
|
SqlParameter[] parameters = {
|
|
|
|
new SqlParameter("@RuleID", SqlDbType.VarChar,20),
|
|
new SqlParameter("@ProgramID", SqlDbType.VarChar,200) };
|
|
|
|
parameters[0].Value = RuleID;
|
|
parameters[1].Value = ProgramID;
|
|
|
|
OT.Model.OTB_SYS_Authorize model = new OT.Model.OTB_SYS_Authorize();
|
|
//DataSet ds = DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_GetModel", parameters, "ds");
|
|
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
|
|
if (ds.Tables[0].Rows[0]["RuleID"] != null && ds.Tables[0].Rows[0]["RuleID"].ToString() != "")
|
|
{
|
|
model.RuleID = ds.Tables[0].Rows[0]["RuleID"].ToString();
|
|
}
|
|
if (ds.Tables[0].Rows[0]["ProgramID"] != null && ds.Tables[0].Rows[0]["ProgramID"].ToString() != "")
|
|
{
|
|
model.ProgramID = ds.Tables[0].Rows[0]["ProgramID"].ToString();
|
|
}
|
|
if (ds.Tables[0].Rows[0]["AllowRight"] != null && ds.Tables[0].Rows[0]["AllowRight"].ToString() != "")
|
|
{
|
|
model.AllowRight = ds.Tables[0].Rows[0]["AllowRight"].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());
|
|
}
|
|
if (ds.Tables[0].Rows[0]["Effective"] != null && ds.Tables[0].Rows[0]["Effective"].ToString() != "")
|
|
{
|
|
model.ProgramStatus = ds.Tables[0].Rows[0]["Effective"].ToString();
|
|
}
|
|
return model;
|
|
}
|
|
else
|
|
{
|
|
return null;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 儲存權限表
|
|
/// </summary>
|
|
public bool Save(OT.Model.OTB_SYS_Authorize model)
|
|
{
|
|
List<CommandInfo> lstCommandInfo = new List<CommandInfo>();
|
|
CommandInfo o_CommandInfo;
|
|
|
|
//把該角色下所有的權限給刪除掉先
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(" DELETE [OTB_SYS_Authorize] ");
|
|
strSql.Append(" WHERE RuleID=@RuleID ");
|
|
SqlParameter[] parametersDel = {
|
|
|
|
new SqlParameter("@RuleID", SqlDbType.VarChar,20),
|
|
new SqlParameter("@ProgramID", SqlDbType.VarChar,200) };
|
|
|
|
parametersDel[0].Value = model.RuleID;
|
|
parametersDel[1].Value = model.ProgramID;
|
|
//DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_Delete", parametersDel);
|
|
o_CommandInfo = new CommandInfo();
|
|
//o_CommandInfo.CommandText = "OSP_OTB_SYS_Authorize_Delete";
|
|
o_CommandInfo.CommandText = strSql.ToString();
|
|
o_CommandInfo.Parameters = parametersDel;
|
|
lstCommandInfo.Add(o_CommandInfo);
|
|
|
|
//然後再插入新的值
|
|
foreach (OT.Model.OTB_SYS_Authorize ChildModel in model.AuthorizeList)
|
|
{
|
|
StringBuilder strSql2 = new StringBuilder();
|
|
strSql2.Append(" INSERT INTO [OTB_SYS_Authorize]( ");
|
|
strSql2.Append(" [RuleID],[ProgramID],[AllowRight],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate] ");
|
|
strSql2.Append(" )VALUES( ");
|
|
strSql2.Append(" @RuleID,@ProgramID,@AllowRight,@Memo,@CreateUser,GETDATE(),@CreateUser,GETDATE() ) ");
|
|
SqlParameter[] parametersAdd = {
|
|
|
|
new SqlParameter("@RuleID", SqlDbType.VarChar,20),
|
|
new SqlParameter("@ProgramID", SqlDbType.VarChar,200),
|
|
new SqlParameter("@AllowRight", SqlDbType.VarChar,200),
|
|
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 = ChildModel.RuleID;
|
|
parametersAdd[1].Value = ChildModel.ProgramID;
|
|
parametersAdd[2].Value = ChildModel.AllowRight;
|
|
parametersAdd[3].Value = ChildModel.Memo;
|
|
parametersAdd[4].Value = ChildModel.CreateUser;
|
|
parametersAdd[5].Value = ChildModel.CreateDate;
|
|
parametersAdd[6].Value = ChildModel.ModifyUser;
|
|
parametersAdd[7].Value = ChildModel.ModifyDate;
|
|
o_CommandInfo = new CommandInfo();
|
|
//o_CommandInfo.CommandText = "OSP_OTB_SYS_Authorize_Add";
|
|
o_CommandInfo.CommandText = strSql2.ToString();
|
|
o_CommandInfo.Parameters = parametersAdd;
|
|
lstCommandInfo.Add(o_CommandInfo);
|
|
}
|
|
|
|
//if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0)
|
|
if (DbHelperSQL.ExecuteSqlTran(lstCommandInfo) > 0)
|
|
{
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public bool GetDirectRoleByRoleSource(string strRoleSource, string strRoleDirect, string CreateUser)
|
|
{
|
|
int effectrow = 0;
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(" DELETE [OTB_SYS_Authorize] WHERE [RuleID]=@DirectRoleID ");
|
|
strSql.Append(" INSERT INTO [OTB_SYS_Authorize] ");
|
|
strSql.Append(" ([RuleID],[ProgramID],[AllowRight],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate]) ");
|
|
strSql.Append(" SELECT @DirectRoleID,[ProgramID],[AllowRight],[Memo],@CreateUser,GETDATE(),[ModifyUser],[ModifyDate] ");
|
|
strSql.Append(" FROM [OTB_SYS_Authorize] ");
|
|
strSql.Append(" WHERE [RuleID]=@RoleSourceID ");
|
|
SqlParameter[] parameter = {
|
|
new SqlParameter("@RoleSourceID",SqlDbType.VarChar,20),
|
|
new SqlParameter("@DirectRoleID",SqlDbType.VarChar,20),
|
|
new SqlParameter("@CreateUser",SqlDbType.VarChar,20)
|
|
|
|
};
|
|
parameter[0].Value = strRoleSource;
|
|
parameter[1].Value = strRoleDirect;
|
|
parameter[2].Value = CreateUser;
|
|
|
|
//DbHelperSQL.RunProcedure("OSP_GetDirectRoleSourceID", parameter, out effectrow);
|
|
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameter);
|
|
if (rows > 0)
|
|
{
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根據登入者判斷權限大小
|
|
/// </summary>
|
|
public DataSet GetListByRuleID(string strMemberID)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(" SELECT RuleID,ProgramID,AllowRight ");
|
|
strSql.Append(" FROM [OTB_SYS_Authorize] ");
|
|
strSql.Append(" WHERE RuleID IN (SELECT RuleID FROM OTB_SYS_MembersToRule WHERE MemberID = @RuleID) ");
|
|
SqlParameter[] parameters = {
|
|
new SqlParameter("@RuleID",SqlDbType.NVarChar,50)};
|
|
parameters[0].Value = strMemberID;
|
|
return DbHelperSQL.Query(strSql.ToString(), parameters);
|
|
//return DbHelperSQL.RunProcedure("OSP_OTB_SYS_Authorize_GetListByRuleID", parameter, "ds");
|
|
}
|
|
|
|
|
|
#endregion Method
|
|
#region MethodEx
|
|
|
|
#endregion MethodEx
|
|
}
|
|
}
|
|
|