/// <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);
}
