using System;
|
using System.Collections.Generic;
|
using System.Text;
|
using Model;
|
using Common;
|
using System.Data;
|
using System.Collections;
|
using Commom.Utility;
|
|
namespace BLL
|
{
|
public class DALRoles : IDALRoles
|
{
|
public IList<Roles> GetList()
|
{
|
try
|
{
|
IList<Roles> ls = new List<Roles>();
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("Select guid,(RoleNum+'-'+RoleName) as RoleName from Roles where IsDel !=1;");
|
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql);
|
ls = ModelConvertHelper<Roles>.DataReaderToModel(dt);
|
|
return ls;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
public IList<Roles> GetList(AjaxRoleList Json, ref PageInfo page)
|
{
|
try
|
{
|
|
IList<Roles> list = new List<Roles>();
|
StringBuilder strSql = new StringBuilder();
|
List<SqlParam> para = new List<SqlParam>();
|
strSql.Append("Select guid,RoleNum,RoleName,Demo,CreatUser,CreatTime,UpdateUser,UpdateTime,Ord,IsDel from View_GetRoles ");
|
|
if (Json.RoleNum != null && Json.RoleNum != "")
|
{
|
if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
|
strSql.Append("RoleNum like '%' + @RoleNum + '%' ");
|
para.Add(new SqlParam("@RoleNum", Json.RoleNum));
|
}
|
|
if (Json.RoleName != null && Json.RoleName != "")
|
{
|
if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
|
strSql.Append("RoleName like '%' + @RoleName + '%' ");
|
para.Add(new SqlParam("@RoleName", Json.RoleName));
|
}
|
|
SqlParam[] param = null;
|
if (para != null)
|
param = para.ToArray();
|
|
DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "Ord", "DESC", ref page);
|
list = ModelConvertHelper<Roles>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
|
}
|
|
public Roles GetModel(string RoleNum)
|
{
|
try
|
{
|
Roles us = null;
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("Select RoleNum,RoleName,IsDel,Demo from View_GetRoles where ");
|
strSql.Append("RoleNum = @RoleNum ");
|
strSql.Append("and IsDel != 1");
|
SqlParam[] para = new SqlParam[]
|
{
|
new SqlParam("@RoleNum", RoleNum),
|
};
|
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql, para);
|
|
us = ModelConvertHelper<Roles>.ReaderToModel(dt);
|
|
return us;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
/// <summary>
|
/// 获取根菜单
|
/// </summary>
|
/// <param name="RoleNum"></param>
|
/// <returns></returns>
|
public IList<ResMenu> GetParentMenu(string RoleNum)
|
{
|
try
|
{
|
IList<ResMenu> list = new List<ResMenu>();
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("select tb1.ReNum as ResNum,tb2.ResName from[Relation] as tb1 ");
|
strSql.Append("left join ResMenu as tb2 on tb1.ReNum = tb2.ResNum ");
|
strSql.Append("where tb1.RoleId = '" + RoleNum + "' and ParentNum= '' order by Ord; ");
|
|
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSql);
|
list = ModelConvertHelper<ResMenu>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
/// <summary>
|
/// 获取角色可分配页面
|
/// </summary>
|
/// <param name="RoleNum">角色编号</param>
|
/// <param name="ParentNum">根菜单编号</param>
|
/// <returns></returns>
|
public IList<ResMenu> GetResMenus(string RoleNum, string Role, string ParentNum)
|
{
|
try
|
{
|
IList<ResMenu> list = new List<ResMenu>();
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select tb2.ResNum,tb2.ResName,tb2.CreatUser,tb2.CreatTime,tb2.UpdateUser,tb2.UpdateTime,");
|
strSql.Append("case when isnull((select ReNum from Relation where ReNum=tb2.ResNum and RoleNum ='" + Role + "'),'0')!='0' then '1' else '0' end as Sel ");
|
strSql.Append("from [ResMenu] as tb2 ");
|
strSql.Append("where tb2.ParentNum= '" + ParentNum + "' order by ord; ");
|
|
// liudl Eidt 不考虑多级分权限
|
//strSql.Append("select tb2.ResNum,tb2.ResName,tb2.CreatUser,tb2.CreatTime,tb2.UpdateUser,tb2.UpdateTime,");
|
//strSql.Append("case when isnull((select ReNum from Relation where ReNum=tb1.ReNum and RoleNum ='" + Role + "'),'0')!='0' then '1' else '0' end as Sel ");
|
//strSql.Append("from[Relation] as tb1 left join ResMenu as tb2 on tb1.ReNum = tb2.ResNum ");
|
//strSql.Append("where tb1.RoleNum = '" + RoleNum + "' and tb2.ParentNum= '" + ParentNum + "'; ");
|
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSql);
|
list = ModelConvertHelper<ResMenu>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
/// <summary>
|
/// 获取视图内按钮权限
|
/// </summary>
|
/// <param name="RoleNum"></param>
|
/// <param name="RoleNums"></param>
|
/// <param name="ParentNum"></param>
|
/// <returns></returns>
|
public IList<ResBtnItem> GetResBtnItems(string RoleNum, string RoleNums, string ParentNum)
|
{
|
try
|
{
|
IList<ResBtnItem> list = new List<ResBtnItem>();
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("select '" + RoleNum + "' as RoleNum,'"+ RoleNums + "' as Guid,tb2.ResNum,tb2.ResName,tb2.CreatUser,tb2.CreatTime,tb2.UpdateUser,tb2.UpdateTime, ");
|
strSql.Append("case when isnull((select ReNum from Relation where ReNum=tb2.ResNum and RoleNum ='" + RoleNum + "'),'0')!='0' then '1' else '0' end as Sel ");
|
strSql.Append("from [ResMenu] as tb2 ");
|
strSql.Append("where tb2.ParentNum= '" + ParentNum + "' order by ord; ");
|
|
// liudl Edit 不考虑多级分权限
|
//strSql.Append("select '" + RoleNum + "' as RoleNum,tb2.ResNum,tb2.ResName,tb2.CreatUser,tb2.CreatTime,tb2.UpdateUser,tb2.UpdateTime, ");
|
//strSql.Append("case when isnull((select ReNum from Relation where ReNum=tb1.ReNum and RoleNum ='" + RoleNum + "'),'0')!='0' then '1' else '0' end as Sel ");
|
//strSql.Append("from[Relation] as tb1 ");
|
//strSql.Append("left join ResMenu as tb2 on tb1.ReNum = tb2.ResNum ");
|
//strSql.Append("where tb1.RoleNum = '" + RoleNums + "' and tb2.ParentNum= '" + ParentNum + "' ");
|
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSql);
|
list = ModelConvertHelper<ResBtnItem>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
/// <summary>
|
/// 获取用户所属角色编码
|
/// </summary>
|
/// <param name="usercode"></param>
|
/// <returns></returns>
|
public string GetResCode(string usercode)
|
{
|
string roleNum = "";
|
StringBuilder sbstr = new StringBuilder();
|
sbstr.Append("select RoleNum from userinfo where userCode='" + usercode + "'");
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(sbstr);
|
if (dt != null)
|
{
|
roleNum = dt.Rows[0][0].ToString();
|
}
|
|
return roleNum;
|
}
|
|
/// <summary>
|
/// 保存菜按钮权限单权限
|
/// </summary>
|
/// <param name="resBtnItem"></param>
|
/// <param name="userCode"></param>
|
/// <returns></returns>
|
public bool SetRelationData(ResBtnItem resBtnItem, string userCode)
|
{
|
bool bl = false;
|
if (resBtnItem != null)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
if (resBtnItem.Sel == "1")
|
{
|
sbStr.Append("Delete from Relation where ReNum = '" + resBtnItem.ResNum + "' and RoleNum = '" + resBtnItem.RoleNum + "';");
|
if (!DoesItExist(resBtnItem))
|
{
|
sbStr.Append("Delete from Relation where ReNum = '" + resBtnItem.ParentNum + "' and RoleNum = '" + resBtnItem.RoleNum + "';");
|
}
|
}
|
else
|
{
|
sbStr.Append("INSERT INTO Relation values ('" + resBtnItem.RoleNum + "','" + resBtnItem.Guid+ "','" + resBtnItem.ResNum + "',GetDate(),'" + userCode + "');");
|
if (!DoesItExist(resBtnItem))
|
{
|
sbStr.Append("INSERT INTO Relation values ('" + resBtnItem.RoleNum + "','" + resBtnItem.Guid + "','" + resBtnItem.ParentNum + "',GetDate(),'" + userCode + "');");
|
}
|
}
|
|
int rowNum = DataFactory.SqlDataBase().ExecuteBySql(sbStr);
|
if (rowNum > 0)
|
{
|
bl = true;
|
}
|
}
|
|
return bl;
|
}
|
|
/// <summary>
|
/// 判断页面权限是否已存在
|
/// </summary>
|
/// <param name="resBtnItem"></param>
|
/// <returns>felse:不存在 true:已存在</returns>
|
private bool DoesItExist(ResBtnItem resBtnItem)
|
{
|
bool bl = false;
|
StringBuilder sbstr = new StringBuilder();
|
if (resBtnItem.Sel == "1")
|
{
|
sbstr.Append("select count(*) as RowNum from Relation as tb1 ");
|
sbstr.Append("left join ResMenu as tb2 on tb1.ReNum = tb2.ResNum ");
|
sbstr.Append("where tb1.ReNum != '" + resBtnItem.ResNum + "' ");
|
sbstr.Append("and tb1.RoleNum = '" + resBtnItem.RoleNum + "' and tb2.ParentNum = '" + resBtnItem.ParentNum + "'; ");
|
}
|
else
|
{
|
sbstr.Append("select count(*) as RowNum from Relation ");
|
sbstr.Append("where RoleNum = '" + resBtnItem.RoleNum + "' and ReNum = '" + resBtnItem.ParentNum + "';");
|
}
|
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(sbstr);
|
if (dt.Rows[0]["RowNum"].ToString() != "0")
|
{
|
bl = true;
|
}
|
|
return bl;
|
}
|
|
/// <summary>
|
/// 设置菜单页权限
|
/// </summary>
|
/// <param name="roles"></param>
|
/// <param name="userCode"></param>
|
/// <returns></returns>
|
public bool SetResMenus(ResBtnItem roles , string userCode)
|
{
|
bool bl = false;
|
if (roles != null)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
if (roles.Sel == "1")
|
{
|
// 删除按钮权限
|
sbStr.Append("delete from Relation where ReNum in ");
|
sbStr.Append("(select ResNum from ResMenu where ParentNum = '"+ roles.ResNum+ "') and RoleNum = '"+roles.RoleNum+"';");
|
|
// 删除菜单权限
|
sbStr.Append("delete from Relation where ReNum = '"+ roles.ResNum + "' and RoleNum ='" + roles.RoleNum + "';");
|
|
if (!DoesItExist(roles)) // 判断是否需要删除功能模块权限
|
{
|
sbStr.Append("delete from Relation where ReNum in ");
|
sbStr.Append("(select ParentNum from ResMenu where ResNum ='" + roles.ResNum + "') and RoleNum = '" + roles.RoleNum + "';");
|
}
|
}
|
else
|
{
|
StringBuilder sbSql = new StringBuilder();
|
sbSql.Append("select ResNum from ResMenu ");
|
sbSql.Append("where ParentNum = '" + roles.ResNum + "' or ResNum = '"+ roles.ResNum + "';");
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(sbSql);
|
foreach (DataRow row in dt.Rows)
|
{
|
StringBuilder sbIsExist = new StringBuilder();
|
sbIsExist.Append("select count(*) from Relation where RoleNum = '"+ roles.RoleNum + "'");
|
sbIsExist.Append(" and ReNum = '" + row["ResNum"] + "';");
|
DataTable dtt = DataFactory.SqlDataBase().GetDataTableBySQL(sbIsExist);
|
if (dtt.Rows[0][0].ToString() == "0")
|
{
|
// 新增菜单及按钮权限
|
sbStr.Append("Insert Into Relation values (");
|
sbStr.Append("'" + roles.RoleNum + "','" + roles.Guid + "','"+ row["ResNum"] + "',GetDate(),'" + userCode + "');");
|
}
|
}
|
|
if (!DoesItExist(roles)) // 判断是否需新增功能模块权限
|
{
|
sbStr.Append("Insert Into Relation ");
|
sbStr.Append("select '" + roles.RoleNum + "','" + roles.Guid + "',ParentNum,GetDate(),'" + userCode + "' from ResMenu ");
|
sbStr.Append("where ResNum = '" + roles.ResNum + "';");
|
}
|
}
|
|
if (sbStr.Length > 0)
|
{
|
int rowNum = DataFactory.SqlDataBase().ExecuteBySql(sbStr);
|
if (rowNum > 0)
|
{
|
bl = true;
|
}
|
}
|
else
|
{
|
bl = true;
|
}
|
|
}
|
|
return bl;
|
}
|
|
public bool Add(Roles model)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
|
ht["RoleNum"] = "'" + model.RoleNum + "'";
|
ht["RoleName"] = "'" + model.RoleName + "'";
|
|
ht["Ord"] = "(select Max(Ord) from Roles) + 1";
|
ht["IsDel"] = 0;
|
ht["CreatUser"] = "'" + model.CreatUser + "'";
|
ht["UpdateTime"] = "GetDate()";
|
ht["CreatTime"] = "GetDate()";
|
ht["Demo"] = "'" + model.Demo + "'";
|
ht["UpdateUser"] = "''";
|
|
int _ret = DataFactory.SqlDataBase().InsertByHashtableNullParam("Roles", ht);
|
|
if (_ret == 1) result = true;
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
public bool Update(Roles model)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
|
ht["RoleName"] = string.IsNullOrEmpty(model.RoleName) ? "''" : "'" + model.RoleName + "'";
|
ht["RoleNum"] = string.IsNullOrEmpty(model.RoleName) ? "''" : "'" + model.RoleNum + "'";
|
|
ht["UpdateTime"] = "GetDate()";
|
ht["UpdateUser"] = string.IsNullOrEmpty(model.UpdateUser) ? "''" : "'" + model.UpdateUser + "'";
|
ht["Demo"] = string.IsNullOrEmpty(model.Demo) ? "''" : "'" + model.Demo + "'";
|
|
string RoleNum = "'" + model.RoleNum + "'";
|
|
int _ret = DataFactory.SqlDataBase().UpdateByHashtable("Roles", nameof(model.RoleNum), RoleNum, ht);
|
|
if (_ret == 1) result = true;
|
|
return result;
|
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
public bool IsExist(string name, string value)
|
{
|
bool result = false;
|
try
|
{
|
string[] para = new string[] { value };
|
int dt = DataFactory.SqlDataBase().IsExist("Roles", name, para);
|
if (dt > 0) result = true;
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
|
}
|
|
public bool IsExist(AjaxRoleInfo model)
|
{
|
bool result = false;
|
try
|
{
|
StringBuilder sbStr = new StringBuilder();
|
sbStr.Append("select count(id) from Roles");
|
sbStr.Append(" where RoleName = " + model.RoleName.AddQuotes());
|
sbStr.Append(" and RoleNum != " + model.RoleNum.AddQuotes());
|
|
DataRow row = DataFactory.SqlDataBase().GetDataRowBySQL(sbStr);
|
if (row[0].ToString() != "0")
|
{
|
result = true;
|
}
|
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
public DataTable GetDataTable(string[] strWhere)
|
{
|
DataTable dt = null;
|
try
|
{
|
int index = 0;
|
string str = "@RoleNum" + index;
|
SqlParam[] param = new SqlParam[strWhere.Length];
|
StringBuilder sql = new StringBuilder();
|
sql.Append("Select RoleNum as 角色编号, RoleName as 角色名,CreatUser as 创建人,CreatTime as 创建时间,UpdateUser as 更新人,UpdateTime as 更新时间,Demo as 备注 FROM View_GetRoles where RoleNum in (");
|
|
for (int i = 0; i < param.Length - 1; i++)
|
{
|
string obj2 = strWhere[i];
|
str = "@RoleNum" + index;
|
sql.Append(str).Append(",");
|
param[index] = new SqlParam(str, obj2);
|
index++;
|
}
|
str = "@RoleNum" + index;
|
sql.Append(str);
|
param[index] = new SqlParam(str, strWhere[index]);
|
sql.Append(")");
|
|
dt = DataFactory.SqlDataBase().GetDataTableBySQL(sql, param, "");
|
|
return dt;
|
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
|
}
|
public bool BatchDelete(string[] RoleNum)
|
{
|
bool result = false;
|
try
|
{
|
int dt = DataFactory.SqlDataBase().IsExist("Roles", "RoleNum", RoleNum);
|
if (dt >= RoleNum.Length)
|
{
|
int _ret = 0;
|
foreach (string rolestr in RoleNum)
|
{
|
int rowCount = DataFactory.SqlDataBase().BatchDeleteData("Roles", "RoleNum", RoleNum);
|
if (rowCount > 0)
|
{
|
// 删除权限信息，防止沉余数据
|
StringBuilder sbstr = new StringBuilder();
|
sbstr.Append("Delete From Relation where RoleNum = '" + rolestr + "';");
|
DataFactory.SqlDataBase().ExecuteBySql(sbstr);
|
}
|
|
_ret += rowCount;
|
}
|
|
if (_ret >= RoleNum.Length)
|
{
|
result = true;
|
}
|
}
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// 验证该部门是否包含员工
|
/// </summary>
|
/// <param name="DepartNum"></param>
|
/// <returns></returns>
|
public string IsChecks(string[] ResNum)
|
{
|
try
|
{
|
string result = "";
|
foreach (string roleNum in ResNum)
|
{
|
StringBuilder sql = new StringBuilder();
|
sql.Append("select count(*) from userinfo where roleNum='" + roleNum + "' and isdel = '0';");
|
DataRow row = DataFactory.SqlDataBase().GetDataRowBySQL(sql);
|
if (row[0].ToString() != "0")
|
{
|
result += roleNum + ":请先停用所属部门员工。";
|
}
|
}
|
|
return result;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
}
|
}
|
