/// <summary>
/// 增加或更新数据， 如果存在则更新
/// </summary>
/// <param name="pid">页面ID</param>
/// <param name="btnRightExp">访问权限</param>
/// <param name="gids">组ID</param>
/// <returns></returns>
public int AddOrUpdate(int pid, string btnRightExp, bool updateWhenExists, params int[] gids)
{
int result = 0;
Safe.Base.Contract.IDbHelper qmnobj = SQLHelpers.TcAdmin();
qmnobj.SetHandClose(false);
try {
if (gids != null)
{
foreach (int gid in gids)
{
string pname = "p_AddUpdateGroupRight";
SqlParameter[] parameters =
{
new SqlParameter("@gid", gid),
new SqlParameter("@pid", pid),
new SqlParameter("@btnRightExp", btnRightExp),
new SqlParameter("@updateWhenExists", updateWhenExists)
};
result = result + qmnobj.ExecuteProc(pname, false, parameters).ReturnValue;
}
}
} finally {
qmnobj.EndConnection();
}
return(result);
}
/// <summary>
/// 根据用户名获取一个管理员信息实体
/// <param name="aname">用户名</param>
/// </summary>
public MR_Admin GetModel(string aname, string ip)
{
Safe.Base.Contract.IDbHelper dbHelper = SQLHelpers.TcAdmin();
try {
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 AID,AName,ANickName,IP,Email,R_Admin.GID ,R_Admin.ALastTime from R_Admin ");
strSql.Append(" where AName=@AName ");
SqlParameter[] parameters =
{
new SqlParameter("@AName", aname)
};
DataTable dtbl = dbHelper.ExecuteFillDataTable(strSql.ToString(), parameters);
if (dtbl.Rows.Count > 0)
{
MR_Admin result = Safe.Base.Utility.ModelConvertHelper <MR_Admin> .ToModel(dtbl.Rows[0]);
dbHelper.ExecuteNonQuery("UPDATE R_Admin SET ALastTime=getdate() , IP=@ip Where AID=@aid", new SqlParameter("@aid", result.AID), new SqlParameter("@ip", ip));
return(result);
}
else
{
return(null);
}
} finally {
dbHelper.EndConnection();
}
}
/// <summary>
/// 获得所有节点
/// </summary>
public IList <Model.MR_PageInfo> GetList(bool?hasHidePage, bool?isHidePage)
{
string whereSQL = "";
bool hasWhere = false;
if (hasHidePage.HasValue && hasHidePage.Value)
{
if (!hasWhere)
{
hasWhere = true;
whereSQL += " where ";
}
whereSQL += " ParentID > -1";
}
if (isHidePage.HasValue)
{
if (!hasWhere)
{
hasWhere = true;
whereSQL += " where ";
}
if (isHidePage.Value)
{
whereSQL += " ParentID = -1";
}
else
{
whereSQL += " ParentID > -1";
}
}
string cmdText = "select p.PID," + SELECT_ALL_COL_NOPK + " FROM R_PageInfo p " + whereSQL + " order by Queue asc";
DataTable tbl = SQLHelpers.TcAdmin().ExecuteFillDataTable(cmdText);
return(Safe.Base.Utility.ModelConvertHelper <MR_PageInfo> .ToModels(tbl));
}
///// <summary>
///// 获得权限记录列表
///// </summary>
//public DataTable GetList() {
// string cmdText = "select AID,PID,BtnRightExp,ClickTimes FROM R_AdminRight order by ClickTimes desc";
// return SQLHelpers.TcFAQ().ExecuteFillDataTable(cmdText);
//}
/// <summary>
/// 检查管理员是否有目录的访问权限，同时返回对该页面按钮的访问权限表达式
/// </summary>
/// <remarks>当有权限访问页面时，点击次数+1</remarks>
/// <param name="adminId">当前登录的作者</param>
/// <param name="url">当前访问页面的URL</param>
/// <param name="btnRights">当前页面的按钮访问权限表达式</param>
/// <returns>
/// true:可以访问该目录
/// false:不可访问该目录
/// </returns>
public bool IsInRoles(int adminId, int pageId, string superAdminRole, bool updateClickTime, out string btnRights)
{
string pname = "p_IsInRoles";
btnRights = string.Empty;
SqlParameter[] sqlparams = new SqlParameter[] {
new SqlParameter("@aid", adminId),
new SqlParameter("@pageId", pageId),
new SqlParameter("@btnRights", SqlDbType.NVarChar, 20),
new SqlParameter("@updateClickTime", updateClickTime),
new SqlParameter("@superAdminRole", superAdminRole)
};
sqlparams[2].Direction = ParameterDirection.Output;
Safe.Base.Contract.CommandResult ComResult = SQLHelpers.TcAdmin().ExecuteProc(pname, false, sqlparams);
int result = ComResult.ReturnValue;
if (result == 0)
{
return(false);
}
btnRights = ComResult.OutPutValue["btnRights"].ToString();
return(true);
}
/// <summary>
/// 获得所有节点
/// </summary>
public IList <MR_PageInfo> GetList()
{
string cmdText = "select r.ParentID as 'HideParentID',r.PID," + DR_PageInfo.SELECT_ALL_COL_NOPK + " FROM R_PageParent r left join R_PageInfo p on p.PID=r.PID";
DataTable tbl = SQLHelpers.TcAdmin().ExecuteFillDataTable(cmdText);
return(Safe.Base.Utility.ModelConvertHelper <MR_PageInfo> .ToModels(tbl));
}
/// <summary>
/// 获取权限内,某个页面下的所有层级子页面,并转换为List
/// </summary>
/// <param name="adminId"></param>
/// <param name="parentID"></param>
/// <param name="incluedChild"></param>
/// <param name="result"></param>
/// <param name="dbhelper"></param>
private void GetChildsToList(int adminId, int parentID, bool incluedChild, ref IList <MR_PageInfo> result, ref Safe.Base.Contract.IDbHelper dbhelper)
{
StringBuilder sb = new StringBuilder();
sb.Append("select a.*,b.btnrightexp from r_pageinfo as a left join r_adminright as b on a.pid=b.pid where b.aid=@aid and a.parentID=@parentID order by a.Queue asc, b.ClickTimes desc");
SqlParameter[] sqlparams = new SqlParameter[] {
new SqlParameter("@aid", adminId),
new SqlParameter("@parentID", parentID)
};
DataTable tbl = SQLHelpers.TcAdmin().ExecuteFillDataTable(sb.ToString(), sqlparams);
IList <MR_PageInfo> tmp = Safe.Base.Utility.ModelConvertHelper <MR_PageInfo> .ToModels(tbl); //该结点的子节点
if (tmp != null)
{
foreach (MR_PageInfo tmpc in tmp)
{
if (!result.Contains(tmpc))
{
result.Add(tmpc);
if (incluedChild)
{
GetChildsToList(adminId, tmpc.PID, incluedChild, ref result, ref dbhelper);
}
}
}
}
}
/// <summary>
/// 根据管理员名字获取其ID
/// </summary>
/// <param name="adminName"></param>
/// <returns></returns>
public int GetIdByAdminName(string adminName)
{
string sql = "select aid from R_Admin where AName=@AName";
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@AName", adminName)
};
IDataReader reader = null;
try {
reader = SQLHelpers.TcAdmin().ExecuteReader(sql, parameters);
if (reader.Read())
{
MR_Admin mr_admin = Safe.Base.Utility.ModelConvertHelper <MR_Admin> .ToModel(reader);
return(mr_admin.AID);
}
else
{
return(0);
}
} finally {
if (reader != null)
{
reader.Close();
}
}
}
/// <summary>
/// 增加权限记录， 如果存在则更新
/// 返回影响的行数，失败返回0
/// </summary>
/// <param name="pid">页面ID</param>
/// <param name="btnRightExp">按钮权限表达式</param>
/// <param name="aid">管理员ID</param>
public int AddOrUpdate(int pid, string btnRightExp, bool updateWhenExists, params int[] aid)
{
int result = 0;
Safe.Base.Contract.IDbHelper qmnobj = SQLHelpers.TcAdmin();
qmnobj.SetHandClose(false);
try {
if (aid != null)
{
foreach (int item in aid)
{
string pname = "p_AddUpdateAdminRight";
SqlParameter[] parameters =
{
new SqlParameter("@aid", SqlDbType.Int, 4),
new SqlParameter("@pid", SqlDbType.Int, 4),
new SqlParameter("@btnRightExp", SqlDbType.NVarChar, 20),
new SqlParameter("@updateWhenExists", true)
};
parameters[0].Value = item;
parameters[1].Value = pid;
parameters[2].Value = btnRightExp;
result = result + qmnobj.ExecuteProc(pname, false, parameters).ReturnValue;
}
}
} finally {
qmnobj.EndConnection();
}
return(result);
}
/// <summary>
/// 批量更新权限
/// 返回受影响的行数
/// </summary>
/// <param name="aids">管理员ID数组</param>
/// <param name="info">权限集合</param>
public int UpdateRights(int[] aids, Dictionary <int, string> info)
{
int result = 0;
Safe.Base.Contract.IDbHelper qmnobj = SQLHelpers.TcAdmin();
qmnobj.SetHandClose(false);
try {
foreach (int aid in aids)
{
foreach (KeyValuePair <int, string> item in info)
{
string pname = "p_AddUpdateAdminRight";
SqlParameter[] parameters =
{
new SqlParameter("@aid", aid),
new SqlParameter("@pid", item.Key),
new SqlParameter("@btnRightExp", item.Value),
new SqlParameter("@updateWhenExists", true)
};
result = result + qmnobj.ExecuteProc(pname, false, parameters).ReturnValue;
}
}
} finally {
qmnobj.EndConnection();
}
return(result);
}
/// <summary>
/// 获得权限组列表
/// </summary>
public IList <MR_Group> GetList()
{
string cmdText = "select GID,GName FROM R_Group ";
DataTable tbl = SQLHelpers.TcAdmin().ExecuteFillDataTable(cmdText);
return(Safe.Base.Utility.ModelConvertHelper <MR_Group> .ToModels(tbl));
}
/// <summary>
/// 增加节点， 返回新增加的标识列
/// 增加失败返回0
/// </summary>
public int Add(MR_PageInfo model)
{
string cmdText = @"
if not exists (select top 1 1 from R_PageInfo where PUrl=@Purl and PName=@PName and ParentID=@ParentID) begin
insert into R_PageInfo(PName,PUrl,IsUrl,Queue,ParentID,DefShowChild) values (@PName,@PUrl,@IsUrl,@Queue,@ParentID,@DefShowChild);
select SCOPE_IDENTITY();
end else begin
select 0
end
";
SqlParameter[] parameters =
{
new SqlParameter("@PName", model.PName),
new SqlParameter("@PUrl", model.PUrl),
new SqlParameter("@IsUrl", model.IsUrl),
new SqlParameter("@Queue", model.Queue),
new SqlParameter("@ParentID", model.ParentID),
new SqlParameter("@DefShowChild", model.DefShowChild)
};
object obj = SQLHelpers.TcAdmin().ExecuteScalar(cmdText, parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
public DataTable GetDTList()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select AID,AName,ANickName,IP,Email ,R_Admin.ALastTime ,R_Admin.GID, case when R_Admin.GID=-1 then '超级管理员' else R_Group.GName end as GName from R_Admin ");
strSql.Append("left join R_Group on R_Admin.GID = R_Group.GID");
strSql.Append(" order by R_Admin.GID asc");
return(SQLHelpers.TcAdmin().ExecuteFillDataTable(strSql.ToString()));
}
/// <summary>
/// 是否存在该节点
/// </summary>
public bool Exists(int PID)
{
string cmdText = "select 1 from R_PageInfo where PID=@PID";
SqlParameter[] parameters =
{
new SqlParameter("@PID", PID)
};
return(SQLHelpers.TcAdmin().Exists(cmdText, parameters));
}
/// <summary>
/// 是否管理员信息
/// </summary>
/// <param name="AID">管理员ID</param>
public bool Exists(string AName)
{
string cmdText = "select 1 from R_Admin where AName=@AName";
SqlParameter[] parameters =
{
new SqlParameter("@AName", AName)
};
return(SQLHelpers.TcAdmin().Exists(cmdText, parameters));
}
/// <summary>
/// 是否存在该权限组
/// </summary>
public bool Exists(int GID)
{
string cmdText = "select 1 from R_Group where GID=@GID";
SqlParameter[] parameters =
{
new SqlParameter("@GID", GID)
};
return(SQLHelpers.TcAdmin().Exists(cmdText, parameters));
}
/// <summary>
/// 修改密码
/// </summary>
/// <param name="adminID">管理员ID</param>
/// <param name="oldPwdMD5">旧密码（MD5加密）</param>
/// <param name="newPwdMD5">新密码（MD5加密）</param>
/// <returns>
/// -1：旧密码不正确
/// 1：修改成功
/// </returns>
public int UpdatePwd(int adminID, string oldPwdMD5, string newPwdMD5)
{
IDataParameter[] ipas =
{
new SqlParameter("@adminID", adminID),
new SqlParameter("@oldPwdMD5", oldPwdMD5),
new SqlParameter("@newPwdMD5", newPwdMD5)
};
return(SQLHelpers.TcAdmin().ExecuteProc("P_UPDATE_AdminPWD", false, ipas).ReturnValue);
}
/// <summary>
/// 取得组信息
/// </summary>
/// <param name="groupID">组ID</param>
/// <returns></returns>
public MR_Group GetModel(int groupID)
{
string cmdText = "select GID,GName FROM R_Group where gid=@GID";
SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@GID", groupID) };
DataTable tbl = SQLHelpers.TcAdmin().ExecuteFillDataTable(cmdText, sqlparams);
return(Safe.Base.Utility.ModelConvertHelper <MR_Group> .ToModel(tbl.Rows[0]));
}
/// <summary>
/// 删除用户
/// 只有超级管理员才可以删除用户
/// <param name="AID">管理员ID</param>
/// <para param name="delID">要删除的用户ID</para>
/// </summary>
public int Delete(int AID, int delID)
{
string pname = "p_DeleteAdmin";// -1 只有管理员可以删除 0 删除失败 1 删除成功
SqlParameter[] parameters =
{
new SqlParameter("@aid", AID),
new SqlParameter("@delId", delID)
};
return(SQLHelpers.TcAdmin().ExecuteProc(pname, false, parameters).ReturnValue);
}
/// <summary>
/// 重新设置预设组
/// </summary>
/// <param name="adminID">管理员ID</param>
/// <param name="newGroupid">新的组</param>
/// <param name="updateRights">是否保留预设的权限,
/// 0 不调整页面访问权限
/// 1 更新页面访问权限为新组的
/// 2 保留旧的权限，同时追加新用户组的权限
/// </param>
public bool UpdateGroup(int adminID, int newGroupid, int updateRights)
{
IDataParameter[] ipas =
{
new SqlParameter("@adminID", adminID),
new SqlParameter("@newGroupID", newGroupid),
new SqlParameter("@updateRights", updateRights)
};
SQLHelpers.TcAdmin().ExecuteProc("P_UPDATE_AdminGroup", false, ipas);
return(true);
}
/// <summary>
/// 是否存在该权限记录
/// </summary>
/// <param name="AID">管理员ID</param>
/// <param name="PID">页面ID</param>
public bool Exists(int AID, int PID)
{
string cmdText = "select 1 from R_AdminRight where AID=@AID and PID=@PID";
SqlParameter[] parameters =
{
new SqlParameter("@AID", AID),
new SqlParameter("@PID", PID)
};
return(SQLHelpers.TcAdmin().Exists(cmdText, parameters));
}
/// <summary>
/// 取父节点下的所有子节点
/// </summary>
/// <param name="parentId"></param>
/// <returns></returns>
public IList <MR_PageInfo> GetChildToList(int parentId, bool includeHide)
{
StringBuilder sql = new StringBuilder("SELECT p.PID," + SELECT_ALL_COL_NOPK + " FROM R_PageInfo p WHERE ParentID=@ParentID order by Queue asc");
if (includeHide)
{
sql.Append(" OR PID IN (SELECT * FROM r_pageparent WHERE parentID=@parentID)");
}
DataTable dt = SQLHelpers.TcAdmin().ExecuteFillDataTable(sql.ToString(), new SqlParameter("@ParentID", parentId));
return(Safe.Base.Utility.ModelConvertHelper <MR_PageInfo> .ToModels(dt));
}
/// <summary>
/// 获得管理员信息列表
/// </summary>
public IList <MR_Admin> GetListByNickName(string nickName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select AID,AName,ANickName,IP,Email ,R_Admin.ALastTime ,R_Admin.GID, case when R_Admin.GID=-1 then '超级管理员' else R_Group.GName end as GName from R_Admin ");
strSql.Append("left join R_Group on R_Admin.GID = R_Group.GID");
strSql.Append(" Where ANickName like '%" + nickName + "%'");
strSql.Append(" order by R_Admin.GID asc");
DataTable tbl = SQLHelpers.TcAdmin().ExecuteFillDataTable(strSql.ToString());
return(Safe.Base.Utility.ModelConvertHelper <MR_Admin> .ToModels(tbl));
}
/// <summary>
/// 判断是否存在此密码
/// </summary>
/// <param name="id"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public bool IsExists_PageSecPwd(int id, string pwd)
{
string sql = string.Empty;
sql = "select * from SecondPwd where id=@id and pwd=@pwd";
SqlParameter[] sqlparams = new SqlParameter[] {
new SqlParameter("@id", id),
new SqlParameter("@pwd", pwd)
};
return(SQLHelpers.TcAdmin().Exists(sql, sqlparams));
}
/// <summary>
/// 更新管理员信息，返回受影响的行数
/// 更新失败，返回0
/// </summary>
public int UpdateBaseInfo(string nickName, string email, int aid)
{
StringBuilder cmdText = new StringBuilder("update R_Admin set ANickName=@ANickName,Email=@Email where AID=@AID;");
SqlParameter[] parameters =
{
new SqlParameter("@AID", aid),
new SqlParameter("@ANickName", nickName),
new SqlParameter("@Email", email)
};
return(SQLHelpers.TcAdmin().ExecuteNonQuery(cmdText.ToString(), parameters));
}
/// <summary>
/// 检测是否存在URL
/// <param name="PID">页面ID</param>
/// <param name="PID">如果是update,那么需要传入页面ID,是insert则传入0</param>
/// </summary>
public bool ExistURL(string url, int pID)
{
string cmdText = "select top 1 1 from R_PageInfo where PUrl=@PUrl" + (pID > 0 ? " and PID<>@PID" : "");
SqlParameter[] parameters =
{
new SqlParameter("@PUrl", url)
, new SqlParameter("@PID", pID)
};
return(SQLHelpers.TcAdmin().Exists(cmdText, parameters));
}
/// <summary>
/// 取一个页面下的隐藏页
/// </summary>
/// <param name="nodeID"></param>
/// <returns></returns>
public IList <MR_PageInfo> GetHidePage(int nodeID)
{
StringBuilder sb = new StringBuilder();
sb.Append("select p.PID," + SELECT_ALL_COL_NOPK + " from r_pageinfo p WHERE parentID=-1 and pid in (select pid from r_pageparent where parentID=@parentID) order by Queue asc");
SqlParameter[] sqlparams = new SqlParameter[] {
new SqlParameter("@parentID", nodeID)
};
DataTable tbl = SQLHelpers.TcAdmin().ExecuteFillDataTable(sb.ToString(), sqlparams);
return(Safe.Base.Utility.ModelConvertHelper <MR_PageInfo> .ToModels(tbl));
}
/// <summary>
/// 获得同组管理员信息列表
/// </summary>
/// <param name="groupID">用户组ID</param>
public IList <MR_Admin> GetList(int groupID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select AID,AName,ANickName,IP,Email,R_Admin.GID,R_Admin.ALastTime, case when R_Admin.GID=-1 then '超级管理员' else R_Group.GName end as GName from R_Admin ");
strSql.Append("left join R_Group on R_Admin.GID = R_Group.GID");
strSql.Append(" where R_Admin.GID=@GID");
strSql.Append(" order by R_Admin.GID asc");
SqlParameter[] sqlparams = new SqlParameter[] { new SqlParameter("@GID", groupID) };
DataTable tbl = SQLHelpers.TcAdmin().ExecuteFillDataTable(strSql.ToString(), sqlparams);
return(Safe.Base.Utility.ModelConvertHelper <MR_Admin> .ToModels(tbl));
}
/// <summary>
/// 取得组的预设菜单， 不含隐藏目录
/// </summary>
/// <param name="groupID">组ID</param>
/// <param name="parentID">用户ID</param>
/// <param name="includeChild">是否同时取子节点</param>
/// <returns></returns>
public IList <MR_PageInfo> GetList(int groupID, int parentID, bool includeChild)
{
IList <MR_PageInfo> result = new List <MR_PageInfo>();
Safe.Base.Contract.IDbHelper tqmn = SQLHelpers.TcAdmin();
tqmn.SetHandClose(false);
try {
GetChilds(groupID, parentID, includeChild, ref result, ref tqmn);
return(result);
} finally {
tqmn.EndConnection();
}
}
/// <summary>
/// 取父节点下的所有子节点
/// </summary>
/// <param name="parentId"></param>
/// <returns></returns>
public IList <MR_PageInfo> GetChild(int parentId, bool includeHide, bool includeChild)
{
IDbHelper db = SQLHelpers.TcAdmin();
db.SetHandClose(false);
try {
return(GetChildRec(parentId, includeHide, includeChild, ref db));
} catch (Exception ex) {
throw ex;
} finally {
db.EndConnection();
}
}
/// <summary>
/// 所有权限配置同时带页面对象(包含隐藏页面)<para/>
/// 隐藏页面的权限为父级页面的权限
/// </summary>
/// <returns></returns>
public IList <MR_PageInfo> GetAllList()
{
var dt = SQLHelpers.TcAdmin().ExecuteFillDataTable(@"
SELECT r.AID,r.BtnRightExp,p.* FROM R_AdminRight r
left join R_PageInfo p on p.PID=r.PID
union all
SELECT r.AID,r.BtnRightExp,p.* FROM R_AdminRight r
left join R_PageParent pp on r.PID=pp.ParentID
left join R_PageInfo p on pp.PID=p.PID
where pp.ParentID is not null
");
return(ModelConvertHelper <MR_PageInfo> .ToModels(dt));
}
