public List<Privilege> GetChildPrivileges(string parentCode)
{
string sql = @"SELECT A.[PrivilegeCode],A.[PrivilegeName],A.[PrivilegeType],A.[ParentID]
,A.[Uri],ISNULL(B.ChildCount,0) AS ChildCount
FROM Privileges A
LEFT JOIN (SELECT COUNT(1) ChildCount,ParentID From Privileges Group By ParentID) B on A.[PrivilegeCode] =B.ParentID
where A.ParentID=@ParentCode Order By A.[Sequence]";
SqlParameter pa = new SqlParameter("@ParentCode", parentCode);
List<Privilege> list = new List<Privilege>();
using (IDataReader reader = base.ExcuteDataReader(sql,pa))
{
while (reader.Read())
{
Privilege p = new Privilege();
p.PrivilegeCode = reader.GetString(0);
p.PrivilegeName = reader.GetString(1);
p.PrivilegeType = reader.GetByte(2);
p.ParentID = reader.IsDBNull(3) ? null : reader.GetString(3);
p.Uri = reader.IsDBNull(4) ? null : reader.GetString(4);
p.HasChild = reader.GetInt32(5) > 0;
list.Add(p);
}
}
return list;
}
public Privilege GetPrivilege(string privilegeCode)
{
string sql = @"SELECT A.[PrivilegeCode],A.[PrivilegeName],A.[PrivilegeType],A.[ParentID],A.[Remark]
,A.[Uri],A.[Sequence],A.[LastUpdateUserUID],A.[LastUpdateUserName],A.[LastUpdateTime],B.[PrivilegeName] AS ParentName
FROM Privileges A
LEFT JOIN Privileges B on A.[ParentID] =B.[PrivilegeCode]
where A.PrivilegeCode = @PrivilegeCode ";
Privilege p = null;
SqlParameter pa = new SqlParameter("@PrivilegeCode",privilegeCode);
using (IDataReader reader = base.ExcuteDataReader(sql, pa))
{
if (reader.Read())
{
p = new Privilege();
p.PrivilegeCode = reader.GetString(0);
p.PrivilegeName = reader.GetString(1);
p.PrivilegeType = reader.GetByte(2);
p.ParentID = reader.IsDBNull(3) ? null : reader.GetString(3);
p.Remark = reader.IsDBNull(4) ? null : reader.GetString(4);
p.Uri = reader.IsDBNull(5) ? null : reader.GetString(5);
p.Sequence = reader.IsDBNull(6) ? 0 : reader.GetInt32(6);
p.LastUpdateUserUID = reader.GetString(7);
p.LastUpdateUserName = reader.GetString(8);
p.LastUpdateTime = reader.GetDateTime(9);
p.ParentName = reader.IsDBNull(10) ? null : reader.GetString(10);
}
}
return p;
}
public List<Privilege> GetAllMenu()
{
string sql = "SELECT [PrivilegeCode],[PrivilegeName],[ParentID],[Uri] FROM [Privileges] where [PrivilegeType]=2 Order By Sequence ";
List<Privilege> list = new List<Privilege>();
using (IDataReader reader = base.ExcuteDataReader(sql))
{
while (reader.Read())
{
Privilege p = new Privilege();
p.PrivilegeCode = reader.GetString(0);
p.PrivilegeName = reader.GetString(1);
p.ParentID = reader.IsDBNull(2) ? null : reader.GetString(2);
p.Uri = reader.IsDBNull(3) ? null : reader.GetString(3);
list.Add(p);
}
}
return list;
}
public List<Privilege> QueryTopLevelPrivilegeList()
{
string sql = @"SELECT A.[PrivilegeCode],A.[PrivilegeName],A.[PrivilegeType],A.[ParentID],A.[Remark]
,A.[Uri],A.[Sequence],A.[LastUpdateUserUID],A.[LastUpdateUserName],A.[LastUpdateTime],B.[PrivilegeName] AS ParentName
FROM Privileges A
LEFT JOIN Privileges B on A.[ParentID] =B.[PrivilegeCode]
where A.ParentID IS Null Order By A.[Sequence] ";
List<Privilege> list = new List<Privilege>();
using (IDataReader reader = base.ExcuteDataReader(sql))
{
while (reader.Read())
{
Privilege p = new Privilege();
p.PrivilegeCode = reader.GetString(0);
p.PrivilegeName = reader.GetString(1);
p.PrivilegeType = reader.GetByte(2);
p.ParentID = reader.IsDBNull(3) ? null : reader.GetString(3);
p.Remark = reader.IsDBNull(4) ? null : reader.GetString(4);
p.Uri = reader.IsDBNull(5) ? null : reader.GetString(5);
p.Sequence = reader.IsDBNull(6) ? 0 : reader.GetInt32(6);
p.LastUpdateUserUID = reader.GetString(7);
p.LastUpdateUserName = reader.GetString(8);
p.LastUpdateTime = reader.GetDateTime(9);
p.ParentName = reader.IsDBNull(10) ? null : reader.GetString(10);
list.Add(p);
}
}
return list;
}
public void Save(Privilege item)
{
if(item.IsNew)
{
Insert(item);
}
else
{
Update(item);
}
}
public void Update(Privilege item)
{
if(item.ChangedPropertyCount>0)
{
StringBuilder sqlbuilder = new StringBuilder();
sqlbuilder.Append("UPDATE [Privileges] SET ");
Dictionary<string,string> cols =new Dictionary<string,string>();
cols.Add("PrivilegeName","[PrivilegeName]");
cols.Add("PrivilegeType","[PrivilegeType]");
cols.Add("Remark","[Remark]");
cols.Add("ParentID","[ParentID]");
cols.Add("Uri","[Uri]");
cols.Add("Sequence","[Sequence]");
cols.Add("LastUpdateUserUID","[LastUpdateUserUID]");
cols.Add("LastUpdateUserName","[LastUpdateUserName]");
cols.Add("LastUpdateTime","[LastUpdateTime]");
int i = 0;
//UPDATE COLUMNS
foreach (string p in item.ChangedPropertyList)
{
if(!cols.ContainsKey(p))
{
continue;
}
if (i > 0)
{
sqlbuilder.Append(",");
}
sqlbuilder.AppendFormat("{0}=@{1}", cols[p], p);
i++;
}
//WHERE;
sqlbuilder.Append(" WHERE [PrivilegeCode]=@PrivilegeCode");
List<SqlParameter> SPParams = new List<SqlParameter>();
SPParams.Add(new SqlParameter("@PrivilegeCode",item.PrivilegeCode));
if(item.IsChanged("PrivilegeName"))
{
SPParams.Add(new SqlParameter("@PrivilegeName",item.PrivilegeName));
}
if(item.IsChanged("PrivilegeType"))
{
SPParams.Add(new SqlParameter("@PrivilegeType",item.PrivilegeType));
}
if(item.IsChanged("Remark"))
{
SPParams.Add(new SqlParameter("@Remark",item.Remark));
}
if(item.IsChanged("ParentID"))
{
SPParams.Add(new SqlParameter("@ParentID",item.ParentID));
}
if(item.IsChanged("Uri"))
{
SPParams.Add(new SqlParameter("@Uri",item.Uri));
}
if(item.IsChanged("Sequence"))
{
SPParams.Add(new SqlParameter("@Sequence",item.Sequence));
}
if(item.IsChanged("LastUpdateUserUID"))
{
SPParams.Add(new SqlParameter("@LastUpdateUserUID",item.LastUpdateUserUID));
}
if(item.IsChanged("LastUpdateUserName"))
{
SPParams.Add(new SqlParameter("@LastUpdateUserName",item.LastUpdateUserName));
}
if(item.IsChanged("LastUpdateTime"))
{
SPParams.Add(new SqlParameter("@LastUpdateTime",item.LastUpdateTime));
}
base.ExecuteNonQuery(sqlbuilder.ToString(), SPParams.ToArray());
}
}
public List<Privilege> QueryAll()
{
string sql ="SELECT [PrivilegeCode],[PrivilegeName],[PrivilegeType],[Remark],[ParentID],[Uri],[Sequence],[LastUpdateUserUID],[LastUpdateUserName],[LastUpdateTime] FROM [Privileges]";
List<Privilege> list =new List<Privilege>();
using(IDataReader reader = base.ExcuteDataReader(sql))
{
while(reader.Read())
{
Privilege item =new Privilege();
item.PrivilegeCode = reader.GetString(0);
item.PrivilegeName = reader.GetString(1);
item.PrivilegeType = reader.GetByte(2);
if(!reader.IsDBNull(3))
{
item.Remark = reader.GetString(3);
}
if(!reader.IsDBNull(4))
{
item.ParentID = reader.GetString(4);
}
if(!reader.IsDBNull(5))
{
item.Uri = reader.GetString(5);
}
item.Sequence = reader.GetInt32(6);
item.LastUpdateUserUID = reader.GetString(7);
item.LastUpdateUserName = reader.GetString(8);
item.LastUpdateTime = reader.GetDateTime(9);
list.Add(item);
}
}
return list;
}
public void Insert(Privilege item)
{
string sql="INSERT INTO [Privileges] ([PrivilegeCode],[PrivilegeName],[PrivilegeType],[Remark],[ParentID],[Uri],[Sequence],[LastUpdateUserUID],[LastUpdateUserName],[LastUpdateTime]) VALUES (@PrivilegeCode,@PrivilegeName,@PrivilegeType,@Remark,@ParentID,@Uri,@Sequence,@LastUpdateUserUID,@LastUpdateUserName,@LastUpdateTime)";
List<SqlParameter> SPParams = new List<SqlParameter>();
SPParams.Add(new SqlParameter("@PrivilegeCode",item.PrivilegeCode));
SPParams.Add(new SqlParameter("@PrivilegeName",item.PrivilegeName));
SPParams.Add(new SqlParameter("@PrivilegeType",item.PrivilegeType));
SPParams.Add(new SqlParameter("@Remark",item.Remark));
SPParams.Add(new SqlParameter("@ParentID",item.ParentID));
SPParams.Add(new SqlParameter("@Uri",item.Uri));
SPParams.Add(new SqlParameter("@Sequence",item.Sequence));
SPParams.Add(new SqlParameter("@LastUpdateUserUID",item.LastUpdateUserUID));
SPParams.Add(new SqlParameter("@LastUpdateUserName",item.LastUpdateUserName));
SPParams.Add(new SqlParameter("@LastUpdateTime",item.LastUpdateTime));
base.ExecuteNonQuery(sql, SPParams.ToArray());
}
public Privilege Get(string key)
{
string sql = "SELECT [PrivilegeCode],[PrivilegeName],[PrivilegeType],[Remark],[ParentID],[Uri],[Sequence],[LastUpdateUserUID],[LastUpdateUserName],[LastUpdateTime] FROM [Privileges] WHERE [PrivilegeCode]=@PrivilegeCode";
SqlParameter p =new SqlParameter("@PrivilegeCode",key);
Privilege item =null;
using(IDataReader reader = base.ExcuteDataReader(sql,p))
{
if(reader.Read())
{
item =new Privilege();
item.PrivilegeCode = reader.GetString(0);
item.PrivilegeName = reader.GetString(1);
item.PrivilegeType = reader.GetByte(2);
if(!reader.IsDBNull(3))
{
item.Remark = reader.GetString(3);
}
if(!reader.IsDBNull(4))
{
item.ParentID = reader.GetString(4);
}
if(!reader.IsDBNull(5))
{
item.Uri = reader.GetString(5);
}
item.Sequence = reader.GetInt32(6);
item.LastUpdateUserUID = reader.GetString(7);
item.LastUpdateUserName = reader.GetString(8);
item.LastUpdateTime = reader.GetDateTime(9);
}
}
return item;
}
/// <summary>
/// Edits the privilege.
/// </summary>
/// <param name="id">The id.</param>
/// <param name="parentCode">The parent code.</param>
/// <param name="parentName">Name of the parent.</param>
/// <returns></returns>
public ActionResult EditPrivilege(string id, string parentCode, string parentName)
{
Privilege p = null;
if (string.IsNullOrEmpty(id))
{
p = new Privilege();
if (string.IsNullOrEmpty(parentCode))
{
p.ParentName = "根权限";
}
else
{
p.ParentID = parentCode;
p.ParentName = parentName;
}
}
else
{
p = sysManageService.GetPrivilege(id);
if (p == null)
{
throw new ArgumentException("参数错误", "Privilege");
}
}
return View(p);
}
public JsonResult SavePrivilegeInfo(string id, Privilege p)
{
JsonReturnMessages msg = new JsonReturnMessages();
try
{
if (string.IsNullOrEmpty(id))
{
p.IsNew = true;
}
else
{
p.IsNew = false;
}
//在根下新建权限
if (p.PrivilegeCode == "")
{
p.PrivilegeCode = null;
}
p.LastUpdateUserUID = base.UserId;
p.LastUpdateUserName = base.CurrentUser.FullName;
p.LastUpdateTime = DateTime.Now;
sysManageService.SavePrivilege(p);
msg.IsSuccess = true;
msg.Msg = "操作成功";
}
catch (BizException bizex)
{
msg.IsSuccess = false;
msg.Msg = bizex.Message;
}
catch
{
msg.IsSuccess = false;
msg.Msg = "操作失败,请稍后重试";
}
return Json(msg);
}