/// <summary> /// 根据ID删除 /// </summary> /// <param name="id">id</param> /// Author : Napoleon /// Created : 2015-01-24 10:43:31 public int DeleteMenu(string id) { int count; try { string sql = "SELECT * FROM dbo.System_Menu WHERE ParentId=@ParentId"; SqlParameter[] parameters = { new SqlParameter("@ParentId", id) }; DataTable dt = DbHelper.GetDataTable(sql, parameters); if (dt.Rows.Count > 0) { count = -1; } else { sql = "DELETE FROM dbo.System_Menu WHERE Id=@Id"; count = DbHelper.ExecuteSql(sql, new { @Id = id }); } } catch (Exception exception) { count = 0; Log4Dao.InsertLog4(exception.Message); } return(count); }
/// <summary> /// 根据ID删除 /// </summary> /// <param name="projectId">projectId</param> /// Author : Napoleon /// Created : 2015-01-24 10:43:31 public int DeleteProject(string projectId) { int count; try { string sql = "SELECT COUNT(*),(SELECT COUNT(*) FROM dbo.System_Rule AS sr WHERE sr.ProjectId='{0}') FROM dbo.System_Menu AS SM WHERE SM.ProjectId=@ProjectId"; SqlParameter[] parameters = { new SqlParameter("@ProjectId", projectId) }; DataTable dt = DbHelper.GetDataTable(sql, parameters); if (dt.Rows[0][0].ToString().Equals("0") && dt.Rows[0][1].ToString().Equals("0")) { sql = "DELETE FROM dbo.System_Project WHERE ProjectId=@ProjectId"; count = DbHelper.ExecuteSql(sql, new { ProjectId = projectId }); } else { count = -1; } } catch (Exception exception) { count = 0; Log4Dao.InsertLog4(exception.Message); } return(count); }
/// <summary> /// 更新 /// </summary> /// <param name="rule">The rule.</param> /// Author : Napoleon /// Created : 2015-01-27 16:28:34 public int UpdateRule(SystemRule rule) { int count; try { string sql = "SELECT Id FROM dbo.System_Rule WHERE ProjectId=@ProjectId AND Name=@Name"; SqlParameter[] parameters = { new SqlParameter("@ProjectId", rule.ProjectId), new SqlParameter("@Name", rule.Name) }; DataTable dt = DbHelper.GetDataTable(sql, parameters); if (dt.Rows.Count == 0 || dt.Rows[0][0].ToString().Equals(rule.Id)) { sql = "UPDATE dbo.System_Rule SET Name=@Name,ParentId=@ParentId,Sort=@Sort,Remark=@Remark,Operator=@Operator WHERE Id=@Id"; count = DbHelper.ExecuteSql(sql, rule); } else { count = -1; } } catch (Exception exception) { count = 0; Log4Dao.InsertLog4(exception.Message); } return(count); }
/// <summary> /// 新增角色 /// </summary> /// <param name="rule">rule</param> /// Author : Napoleon /// Created : 2015-01-27 15:29:05 public int InsertRule(SystemRule rule) { int count; try { string sql = "SELECT Id FROM dbo.System_Rule WHERE ProjectId=@ProjectId AND Name=@Name"; SqlParameter[] parameters = { new SqlParameter("@ProjectId", rule.ProjectId), new SqlParameter("@Name", rule.Name) }; DataTable dt = DbHelper.GetDataTable(sql, parameters); if (dt.Rows.Count > 0) { count = -1; } else { sql = "INSERT INTO dbo.System_Rule (ProjectId,Id,Name,ParentId,Sort,Remark,Operator) VALUES (@ProjectId,@Id,@Name,@ParentId,@Sort,@Remark,@Operator)"; count = DbHelper.ExecuteSql(sql, rule); } } catch (Exception exception) { count = 0; Log4Dao.InsertLog4(exception.Message); } return(count); }
/// <summary> /// 菜单父节点 /// </summary> /// <param name="projectId">projectId</param> /// <param name="parentId">parentId</param> /// Author : Napoleon /// Created : 2015-01-24 16:35:16 public DataTable GetTreeParentId(string projectId, string parentId = "") { DataTable dt = new DataTable(); try { StringBuilder sb = new StringBuilder(); sb.Append("SELECT Id,Name FROM dbo.System_Menu WHERE ProjectId=@ProjectId "); if (!string.IsNullOrWhiteSpace(parentId)) { sb.AppendFormat("AND ParentId=@ParentId "); } SqlParameter[] parameters = { new SqlParameter("@ProjectId", projectId), new SqlParameter("@ParentId", parentId) }; dt = DbHelper.GetDataTable(sb.ToString(), parameters); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(dt); }
/// <summary> /// 新增用户权限 /// </summary> /// <param name="rules">rules</param> /// Author : Napoleon /// Created : 2015-01-23 10:04:00 public int InsertUserRule(SystemUserAndRule rules) { int count; try { string sql = "SELECT * FROM dbo.System_UserAndRule WHERE UserId=@UserId AND RuleParentId=@RuleParentId AND ProjectId=@ProjectId"; SqlParameter[] parameters = { new SqlParameter("@UserId", rules.UserId), new SqlParameter("@RuleParentId", rules.RuleParentId), new SqlParameter("@ProjectId", rules.ProjectId) }; DataTable dt = DbHelper.GetDataTable(sql, parameters); if (dt.Rows.Count > 0) { count = -1; } else { sql = "INSERT INTO dbo.System_UserAndRule( Id, UserId, RuleId, RuleParentId,ProjectId ) VALUES (@Id,@UserId,@RuleId,@RuleParentId,@ProjectId)"; count = DbHelper.ExecuteSql(sql, rules); } } catch (Exception exception) { count = 0; Log4Dao.InsertLog4(exception.Message); } return(count); }
/// <summary> /// 新增系统 /// </summary> /// <param name="project">The project.</param> /// Author : Napoleon /// Created : 2015-01-24 09:42:13 public int InsertProject(SystemProject project) { int count; try { string sql = "SELECT * FROM dbo.System_Project WHERE ProjectId=@ProjectId"; SqlParameter[] parameters = { new SqlParameter("@ProjectId", project.ProjectId) }; DataTable dt = DbHelper.GetDataTable(sql, parameters); if (dt.Rows.Count > 0) { count = -1; } else { sql = "INSERT INTO dbo.System_Project ( ProjectId ,ProjectName ,Remark ,Operator) VALUES (@ProjectId,@ProjectName,@Remark,@Operator)"; count = DbHelper.ExecuteSql(sql, project); } } catch (Exception exception) { count = 0; Log4Dao.InsertLog4(exception.Message); } return(count); }
/// <summary> /// 获取 /// </summary> /// <param name="ruleId">角色Id</param> /// <param name="menuId">menuId</param> /// <param name="projectId">projectId</param> /// Author : Napoleon /// Created : 2015-01-12 20:03:14 public List <SystemMenu> GetOperation(string ruleId, string menuId, string projectId) { List <SystemMenu> menus = new List <SystemMenu>(); try { string sql = "SELECT * FROM dbo.System_Menu WHERE id IN (SELECT OperationId FROM dbo.System_MenuAndRule WHERE RuleId=@RuleId AND MenuId=@MenuId AND ProjectId=@ProjectId) AND ParentId != @ParentId ORDER BY Sort"; menus = DbHelper.GetEnumerables <SystemMenu>(sql, new { @RuleId = ruleId, @MenuId = menuId, @ProjectId = projectId, @ParentId = PublicFields.RuleParentId }); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(menus); }
/// <summary> /// 根据ID查询 /// </summary> /// <param name="projectId">projectId</param> /// Author : Napoleon /// Created : 2015-01-24 10:06:30 public SystemProject GetProjectById(string projectId) { SystemProject project = new SystemProject(); try { string sql = "SELECT * FROM dbo.System_Project WHERE ProjectId=@ProjectId"; project = DbHelper.GetEnumerable <SystemProject>(sql, new { @ProjectId = projectId }); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(project); }
/// <summary> /// 根据Id查询 /// </summary> /// <param name="id">id</param> /// Author : Napoleon /// Created : 2015-01-24 15:11:11 public SystemMenu GetMenu(string id) { SystemMenu menu = new SystemMenu(); try { string sql = "SELECT ProjectId,Id,Name,ParentId,HtmlId,Url,Icon,Sort,Remark,Operator FROM dbo.System_Menu WHERE Id=@Id order by Sort"; menu = DbHelper.GetEnumerable <SystemMenu>(sql, new { @Id = id }); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(menu); }
/// <summary> /// 根据ID查询 /// </summary> /// <param name="id">id</param> /// Author : Napoleon /// Created : 2015-01-27 16:24:30 public SystemRule GetRule(string id) { SystemRule rule = new SystemRule(); try { string sql = "SELECT ProjectId ,Id ,Name ,ParentId,Person,Address,TelPhone ,Sort ,Remark ,Operator FROM dbo.System_Rule WHERE Id=@Id"; rule = DbHelper.GetEnumerable <SystemRule>(sql, new { @Id = id }); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(rule); }
/// <summary> /// 获取权限ID /// </summary> /// <param name="userId">用户ID</param> /// <param name="projectId">系统代码</param> /// Author : Napoleon /// Created : 2015-01-20 13:24:49 public SystemUserAndRule GetRule(string userId, string projectId) { SystemUserAndRule userAndRule = new SystemUserAndRule(); try { string sql = "SELECT Id,ProjectId,UserId,RuleId,RuleParentId FROM dbo.System_UserAndRule WHERE UserId=@UserId AND ProjectId=@ProjectId"; userAndRule = DbHelper.GetEnumerable <SystemUserAndRule>(sql, new { @UserId = userId, @ProjectId = projectId }); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(userAndRule); }
/// <summary> /// 获取系统下拉框 /// </summary> /// <returns>DataTable.</returns> /// Author : Napoleon /// Created : 2015-01-27 11:23:40 public DataTable GetProjecTable() { DataTable dt = new DataTable(); try { string sql = string.Format("SELECT ProjectId,ProjectName FROM dbo.System_Project"); dt = DbHelper.GetDataTable(sql); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(dt); }
/// <summary> /// 根据ID删除用户对应的权限数据 /// </summary> /// <param name="id">The identifier.</param> /// Author : Napoleon /// Created : 2015-01-21 15:56:01 public int DeleteRuleById(string id) { int i; try { string sql = "DELETE FROM dbo.System_UserAndRule WHERE Id=@Id"; i = DbHelper.ExecuteSql(sql, new { Id = id }); } catch (Exception exception) { i = -1; Log4Dao.InsertLog4(exception.Message); } return(i); }
/// <summary> /// 新增系统 /// </summary> /// <param name="project">The project.</param> /// Author : Napoleon /// Created : 2015-01-24 09:42:13 public int UpdateProject(SystemProject project) { int i; try { string sql = "UPDATE dbo.System_Project SET ProjectName=@projectName,Remark=@remark where ProjectId=@projectId"; i = DbHelper.ExecuteSql(sql, project); } catch (Exception exception) { i = 0; Log4Dao.InsertLog4(exception.Message); } return(i); }
/// <summary> /// 获取角色列表 /// </summary> /// <param name="projectId">projectId</param> /// Author : Napoleon /// Created : 2015-01-27 14:33:15 public DataTable GetRuleTable(string projectId) { DataTable dt = new DataTable(); try { string sql = string.Format("SELECT ProjectId,ParentId,Id,Name,Person,TelPhone,Address,Sort,Remark FROM dbo.System_Rule WHERE ProjectId=@ProjectId ORDER BY Sort"); SqlParameter[] parameters = { new SqlParameter("@ProjectId", projectId) }; dt = DbHelper.GetDataTable(sql, parameters); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(dt); }
/// <summary> /// 新增菜单 /// </summary> /// <param name="menu">The menu.</param> /// Author : Napoleon /// Created : 2015-01-26 14:18:32 public int InsertMenu(SystemMenu menu) { int i; try { string sql = "INSERT INTO dbo.System_Menu (ProjectId ,Id ,Name ,ParentId,HtmlId ,Url ,Icon, Sort ,Remark ,Operator) VALUES(@ProjectId,@Id,@Name,@ParentId,@HtmlId,@Url,@Icon,@Sort,@Remark,@Operator)"; i = DbHelper.ExecuteSql(sql, menu); } catch (Exception exception) { i = -1; Log4Dao.InsertLog4(exception.Message); } return(i); }
/// <summary> /// 根据父节点获取下拉框内容 /// </summary> /// <param name="parentId">parentId</param> /// Author : Napoleon /// Created : 2015-01-19 09:29:19 public DataTable GetTableByParentId(string parentId) { DataTable dt = new DataTable(); try { string sql = "SELECT Id,Name FROM dbo.System_Code WHERE ParentId=@ParentId"; SqlParameter[] parameters = { new SqlParameter("@ParentId", parentId) }; dt = DbHelper.GetDataTable(sql, parameters); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(dt); }
/// <summary> /// 更新菜单 /// </summary> /// <param name="menu">The menu.</param> /// Author : Napoleon /// Created : 2015-01-26 16:06:40 public int UpdateMenu(SystemMenu menu) { int count; try { string sql = "UPDATE dbo.System_Menu SET Name=@name,ParentId=@parentId,HtmlId=@htmlId,Url=@url,Icon=@icon,Sort=@sort,Remark=@remark,Operator=@operator WHERE Id=@id"; count = DbHelper.ExecuteSql(sql, menu); } catch (Exception exception) { count = -1; Log4Dao.InsertLog4(exception.Message); } return(count); }
/// <summary> /// 根据projectId查询列表 /// </summary> /// <param name="projectId">projectId</param> /// Author : Napoleon /// Created : 2015-01-24 15:11:11 public DataTable GetMenuTable(string projectId) { DataTable dt = new DataTable(); try { string sql = "SELECT ProjectId,Id,Name,ParentId,HtmlId,Url,Icon,Sort,Remark,Operator,'false' as checked FROM dbo.System_Menu WHERE ProjectId=@ProjectId order by Sort"; SqlParameter[] parameters = { new SqlParameter("@ProjectId", projectId) }; dt = DbHelper.GetDataTable(sql, parameters); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(dt); }
/// <summary> /// 获取拥有的权限 /// </summary> /// <param name="ruleId">权限ID</param> /// <param name="projectId">系统ID</param> /// Author : Napoleon /// Created : 2015-01-10 15:15:22 public DataTable GetOperatorTable(string projectId, string ruleId) { DataTable dt = new DataTable(); try { string sql = "SELECT OperationId FROM dbo.System_MenuAndRule WHERE ProjectId=@ProjectId and RuleId=@RuleId"; SqlParameter[] parameters = { new SqlParameter("@ProjectId", projectId), new SqlParameter("@RuleId", ruleId) }; dt = DbHelper.GetDataTable(sql, parameters); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(dt); }
/// <summary> /// 获取菜单 /// </summary> /// <param name="ruleId">角色ID</param> /// <param name="projectId">系统ID</param> /// Author : Napoleon /// Created : 2015-01-10 15:15:22 public DataTable GetMenuTable(string ruleId, string projectId) { DataTable dt = new DataTable(); try { string sql = "SELECT DISTINCT mu.Id,mu.Name,mu.ParentId,mu.Url,mu.Icon,mu.Sort FROM dbo.System_MenuAndRule AS md LEFT JOIN dbo.System_Menu AS mu ON md.MenuId=mu.Id WHERE md.RuleId=@RuleId and mu.ProjectId=@ProjectId Order by mu.Sort "; SqlParameter[] parameters = { new SqlParameter("@RuleId", ruleId), new SqlParameter("@ProjectId", projectId) }; dt = DbHelper.GetDataTable(sql, parameters); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(dt); }
/// <summary> /// 系统总数 /// </summary> /// Author : Napoleon /// Created : 2015-01-23 16:31:28 public int GetProjectCount() { int i = 0; try { string sql = string.Format("SELECT * FROM dbo.System_Project"); DataTable dt = DbHelper.GetDataTable(sql); if (dt.Rows.Count > 0) { i = dt.Rows.Count; } } catch (Exception exception) { i = -1; Log4Dao.InsertLog4(exception.Message); } return(i); }
/// <summary> /// 获取用户所在角色的列表 /// </summary> /// <param name="userId">The user identifier.</param> /// <param name="startCount">The start count.</param> /// <param name="endCount">The end count.</param> /// Author : Napoleon /// Created : 2015-01-21 15:38:44 public DataTable GetRuleTable(string userId, int startCount, int endCount) { DataTable dt = new DataTable(); try { string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY uar.Id) AS number,uar.ProjectId,uar.Id,uar.UserId,sr1.Name AS Company,sr.Name AS RuleName FROM dbo.System_UserAndRule AS uar LEFT JOIN dbo.System_Rule AS sr ON sr.Id=uar.RuleId LEFT JOIN dbo.System_Rule AS sr1 ON sr1.Id=uar.RuleParentId WHERE UserId =@UserId) AS new WHERE new.number>@StartCount AND new.number<=@EndCount"; SqlParameter[] parameters = { new SqlParameter("@UserId", userId), new SqlParameter("@StartCount", startCount), new SqlParameter("@EndCount", endCount) }; dt = DbHelper.GetDataTable(sql, parameters); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(dt); }
/// <summary> /// 获取系统列表 /// </summary> /// Author : Napoleon /// Created : 2015-01-23 16:20:56 public DataTable GetProjectTable(string projectId, string projectName, int startCount, int endCount) { DataTable dt = new DataTable(); try { string sql = "SELECT ProjectId,ProjectName,Remark,Operator FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProjectId) AS number, ProjectId,ProjectName,Remark,Operator FROM dbo.System_Project) AS new WHERE new.ProjectId like @ProjectId and new.ProjectName like @ProjectName and new.number>@StartCount AND new.number <=@EndCount"; SqlParameter[] parameters = { new SqlParameter("@ProjectId", string.Format("%{0}%", projectId)), new SqlParameter("@ProjectName", string.Format("%{0}%", projectName)), new SqlParameter("@StartCount", startCount), new SqlParameter("@EndCount", endCount) }; dt = DbHelper.GetDataTable(sql, parameters); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(dt); }
/// <summary> /// 获取角色权限 /// </summary> /// Author : Napoleon /// Created : 2015-01-21 21:24:14 public DataTable GetRuleTable(string projectId, string id) { DataTable dt = new DataTable(); try { StringBuilder sb = new StringBuilder(); sb.AppendFormat("SELECT Id,Name FROM dbo.System_Rule WHERE ParentId=@ParentId"); List <SqlParameter> list = new List <SqlParameter>(); list.Add(new SqlParameter("@ParentId", id)); if (!string.IsNullOrWhiteSpace(projectId)) { sb.AppendFormat(" AND ProjectId=@ProjectId"); list.Add(new SqlParameter("@ProjectId", projectId)); } dt = DbHelper.GetDataTable(sb.ToString(), list.ToArray()); } catch (Exception exception) { Log4Dao.InsertLog4(exception.Message); } return(dt); }
/// <summary> /// 用户所在角色的总数 /// </summary> /// <param name="userId">The user identifier.</param> /// Author : Napoleon /// Created : 2015-01-21 15:41:22 public int GetRuleCount(string userId) { int i = 0; try { string sql = "SELECT count(*) FROM dbo.System_UserAndRule WHERE UserId =@UserId"; SqlParameter[] parameters = { new SqlParameter("@UserId", userId) }; DataTable dt = DbHelper.GetDataTable(sql, parameters); if (dt.Rows.Count > 0) { i = Convert.ToInt32(dt.Rows[0][0]); } } catch (Exception exception) { i = -1; Log4Dao.InsertLog4(exception.Message); } return(i); }