/// <summary>
/// 获取菜单、功能项等被使用的权限信息
/// </summary>
/// <param name="privilegeAccess">类型:菜单、功能项</param>
/// <param name="privilegeAccessValue">对应值</param>
/// <returns></returns>
public Task <IEnumerable <SystemPermission> > GetSystemPermissionsByPrivilegeAccessAndValue(EnumPrivilegeAccess privilegeAccess,
Guid?privilegeAccessValue = null)
{
StringBuilder sql =
new StringBuilder(
"SELECT * FROM System_Permission WHERE PrivilegeAccess=@privilegeAccess");
if (privilegeAccessValue != null)
{
sql.Append(" AND PrivilegeAccessValue=@privilegeAccessValue");
}
return(SqlMapperUtil.SqlWithParams <SystemPermission>(sql.ToString(),
new { privilegeAccess, privilegeAccessValue }));
}
/// <summary>
/// 根据特权类型及特权id获取特权用户信息
/// </summary>
/// <param name="privilegeMaster">特权类型</param>
/// <param name="privilegeMasterValue">特权id</param>
/// <returns></returns>
public Task <IEnumerable <SystemPermissionUser> > GetPermissionUsersByPrivilegeMasterAdnPrivilegeMasterValue(
EnumPrivilegeMaster privilegeMaster,
Guid?privilegeMasterValue = null)
{
var sql =
new StringBuilder("SELECT * FROM System_PermissionUser WHERE PrivilegeMaster=@privilegeMaster");
if (privilegeMasterValue != null)
{
sql.Append(" AND PrivilegeMasterValue=@privilegeMasterValue");
}
return(SqlMapperUtil.SqlWithParams <SystemPermissionUser>(sql.ToString(),
new { privilegeMaster, privilegeMasterValue }));
}
/// <summary>
/// 根据父级查询下级
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public Task <IEnumerable <SystemMenuOutput> > GetMenuByPid(IdInput input)
{
var sql = new StringBuilder();
sql.Append(@"select menu.*,menu1.name ParentName
from sys_menu menu
left join Sys_Menu menu1 on menu.ParentId=menu1.menuid where 1=1");
if (!string.IsNullOrWhiteSpace(input.Id))
{
sql.AppendFormat(" AND menu.ParentId='{0}'", input.Id);
}
sql.Append(" ORDER BY menu.menuid");
return(SqlMapperUtil.Query <SystemMenuOutput>(sql.ToString()));
}
/// <summary>
/// 根据角色ID获取具有的菜单信息
/// </summary>
/// <param name="input"></param>
/// <returns>树形菜单信息</returns>
public Task <IEnumerable <TreeEntity> > GetMenuHavePermissionByPrivilegeMasterValue(GetMenuHavePermissionByPrivilegeMasterValueInput input)
{
var sql = new StringBuilder(
@"SELECT MenuId id,ParentId pId,name,icon FROM Sys_Menu menu WHERE MenuId IN( SELECT PrivilegeAccessValue FROM Sys_Permission WHERE PrivilegeAccess=@privilegeAccess AND PrivilegeMasterValue=@privilegeMasterValue GROUP BY PrivilegeAccessValue)
order by OrderNo");
return(SqlMapperUtil.SqlWithParams <TreeEntity>(sql.ToString(),
new
{
privilegeAccess = EnumPrivilegeAccess.菜单,
privilegeMasterValue = input.PrivilegeMasterValue,
isFreeze = false
}));
}
/// <summary>
/// 获取处理任务信息
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public Task <WorkflowEngineDealWithTaskOutput> GetWorkflowEngineDealWithTaskOutput(WorkflowEngineRunnerInput input)
{
const string sql = @"select ActivityId,form.Url FormUrl,activity.Buttons,activity.Name,instance.Title ProcessName,instance.Urgency from Workflow_ProcessInstance_Activity activity
left join Workflow_Form form on activity.FormId=form.FormId
left join Workflow_ProcessInstance_Task task on task.CurrentActivityId=activity.ActivityId
left join Workflow_ProcessInstance instance on task.ProcessInstanceId=instance.ProcessInstanceId
where activity.ProcessInstanceId=@processInstanceId and task.TaskId=@taskId";
return(SqlMapperUtil.SqlWithParamsSingle <WorkflowEngineDealWithTaskOutput>(sql, new
{
processInstanceId = input.ProcessInstanceId,
taskId = input.CurrentTaskId
}));
}
/// <summary>
/// 查询归属某组织下的组信息
/// </summary>
/// <param name="input">组织机构Id</param>
/// <returns>组信息</returns>
public Task <IEnumerable <SystemGroupOutput> > GetGroupByOrganizationId(NullableIdInput input)
{
var sql = new StringBuilder();
sql.Append(
@"SELECT gr.GroupId,gr.Name,gr.BelongTo,gr.BelongToUserId,gr.[State],gr.IsFreeze,gr.OrderNo,gr.Remark,gr.CreateTime,gr.CreateUserName,gr.UpdateTime,gr.UpdateUserName,org.OrganizationId,org.Name OrganizationName
FROM System_Group gr LEFT JOIN System_Organization org ON org.OrganizationId=gr.OrganizationId");
if (!input.Id.IsNullOrEmptyGuid())
{
sql.Append(" WHERE gr.OrganizationId=@orgId");
}
sql.Append(" ORDER BY gr.OrganizationId");
return(SqlMapperUtil.SqlWithParams <SystemGroupOutput>(sql.ToString(), new { orgId = input.Id }));
}
public Task <IEnumerable <TreeEntity> > GetDataBaseColumnsTree(string Name)
{
var sql = @"SELECT
a.name id,
cast(a.name as varchar)+cast(isnull(g.[value], ' ') as varchar) as name,
isnull(g.[value], ' ') as code
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
where b.name is not null and d.name=@name
order by a.id,a.colorder";
return(SqlMapperUtil.Query <TreeEntity>(sql, new { name = Name }));
}
/// <summary>
/// 根据表名获取所有列
/// </summary>
/// <param name="idInput"></param>
/// <returns></returns>
public Task <IEnumerable <SystemDataBaseColumnOutput> > GetDataBaseColumn(IdInput idInput)
{
var sql = @"SELECT
b.name DataType,
a.name FieldName,
isnull(g.[value], ' ') AS Remarks
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
where b.name is not null and d.name=@name
order by a.id,a.colorder";
return(SqlMapperUtil.Query <SystemDataBaseColumnOutput>(sql, new{ name = idInput.Id }));
}
/// <summary>
/// 获取文章分页
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public Task <PagedResultsDto <SystemArticleOutput> > GetPagingArticleType(SystemArticlePagingInput param)
{
string sWhere = "";
if (!string.IsNullOrWhiteSpace(param.Title))
{
sWhere += $" And Title like '%{param.Title.Trim()}%'";
}
string sql = @"select article.*,articletype.Name ArticleTypeName
from Sys_Article article
left join Sys_ArticleType articletype on article.ArticleTypeId=articletype.ArticleTypeId
Where 1=1 " + sWhere;
return(SqlMapperUtil.PagingQuery <SystemArticleOutput>(sql, param));;
}
/// <summary>
/// 获取第一步开始流程信息
/// 1、活动Id
/// 2、对应表单地址
/// 3、能够操作的按钮
/// </summary>
/// <returns></returns>
public Task <WorkflowEngineDealWithTaskOutput> GetWorkflowEngineStartTaskOutput(
WorkflowEngineStartTaskInput input)
{
const string sql =
@"select ActivityId,form.Url FormUrl,activity.Buttons,activity.Name,process.Name ProcessName from Workflow_ProcessActivity activity
left join Workflow_Form form on activity.FormId=form.FormId
left join Workflow_Process process on activity.ProcessId=process.ProcessId
where activity.ProcessId=@processId and activity.[Type]=@type";
return(SqlMapperUtil.SqlWithParamsSingle <WorkflowEngineDealWithTaskOutput>(sql, new
{
processId = input.ProcessId,
input.Type
}));
}
/// <summary>
/// 根据用户名和密码查询用户信息
/// 1:用户登录使用
/// </summary>
/// <param name="input">登录名、密码等</param>
/// <returns></returns>
public Task <SystemUserOutput> CheckUserByCodeAndPwd(UserLoginInput input)
{
var sql = new StringBuilder();
sql.Append(@"SELECT UserId,userInfo.Code,userInfo.Name,FirstVisitTime,userInfo.IsFreeze,userInfo.State,org.Name OrganizationName,org.OrganizationId FROM [System_UserInfo] userInfo
LEFT JOIN System_PermissionUser perUser on userInfo.UserId=perUser.PrivilegeMasterUserId and perUser.PrivilegeMaster=1
LEFT JOIN System_Organization org on perUser.PrivilegeMasterValue=org.OrganizationId ");
sql.Append("WHERE userInfo.Code=@code AND userInfo.Password=@pwd");
return(SqlMapperUtil.SqlWithParamsSingle <SystemUserOutput>(sql.ToString(),
new
{
code = input.Code,
pwd = input.Pwd
}));
}
/// <summary>
/// 根据用户Id获取用户具有的菜单权限
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public Task <IEnumerable <TreeEntity> > GetSystemPermissionMenuByUserId(Guid userId)
{
var sql =
new StringBuilder(
@"SELECT menu.MenuId id,menu.ParentId pId,menu.name,menu.icon,(ISNULL(app.Domain,'')+menu.OpenUrl) url FROM System_Menu menu
LEFT JOIN System_Permission per ON per.PrivilegeAccessValue=menu.MenuId
LEFT JOIN System_PermissionUser perUser ON per.PrivilegeMasterValue=perUser.[PrivilegeMasterValue]
LEFT JOIN System_App app ON app.AppId=menu.AppId
WHERE per.PrivilegeAccess=@privilegeAccess and menu.IsShowMenu=@isShowMenu and menu.IsFreeze=@isFreeze and perUser.[PrivilegeMasterUserId]=@userId
GROUP BY menu.MenuId,menu.ParentId,menu.name,menu.icon,menu.OpenUrl,menu.OrderNo,app.Domain
ORDER BY menu.OrderNo");
return(SqlMapperUtil.SqlWithParams <TreeEntity>(sql.ToString(),
new { privilegeAccess = (byte)EnumPrivilegeAccess.菜单, isShowMenu = true, isFreeze = false, userId }));
}
/// <summary>
/// 获取字段权限Sql
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public Task <IEnumerable <SystemField> > GetFieldPermission(SystemPermissionSqlInput input)
{
//调用存储过程获取字段信息
const string procName = @"System_Proc_GetFiledSql";
MvcRote rote = PermissionRouteConvert.RoteConvert(input.EnumPermissionRoteConvert);
return(SqlMapperUtil.StoredProcWithParams <SystemField>(procName,
new
{
input.PrincipalUser.UserId,
rote.Area,
rote.Controller,
rote.Action
}));
}
/// <summary>
/// 根据路由信息获取菜单信息
/// </summary>
/// <param name="mvcRote"></param>
/// <returns></returns>
public Task <IEnumerable <SystemMenuButton> > GetMenuButtonByMvcRote(MvcRote mvcRote)
{
var sql = new StringBuilder();
sql.Append(
"SELECT * FROM System_MenuButton func " +
"WHERE func.MenuId IN (SELECT MenuId FROM System_Menu WHERE Area=@area AND Controller=@controller AND Action=@action) ORDER BY func.OrderNo");
return(SqlMapperUtil.SqlWithParams <SystemMenuButton>(sql.ToString(),
new
{
area = mvcRote.Area,
controller = mvcRote.Controller,
action = mvcRote.Action
}));
}
/// <summary>
/// 更新任务状态
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public Task <int> UpdateProcessTaskStatus(WorkflowEngineProcessTaskStatusInput input)
{
const string sql =
"UPDATE Workflow_ProcessInstance_Task SET Status=@status,DoUserId=@doUserId,DoUserName=@doUserName,DoTime=@doTime,Comment=@comment WHERE TaskId=@taskId";
return(SqlMapperUtil.InsertUpdateOrDeleteSql <WorkflowProcessInstanceTask>(sql, new
{
status = (byte)input.Status,
doUserId = input.PrincipalUser.UserId,
doUserName = input.PrincipalUser.Name,
doTime = DateTime.Now,
comment = input.Comment,
taskId = input.TaskId
}));
}
/// <summary>
/// </summary>
/// <param name="entity">
/// </param>
/// <param name="parameters">
/// </param>
/// <param name="connection">
/// </param>
/// <param name="transaction">
/// </param>
/// <returns>
/// </returns>
public int Save(
T entity,
object parameters = null,
IDbConnection connection = null,
IDbTransaction transaction = null)
{
int rowsAffected = 0;
IDbConnection conn = connection;
try
{
conn = conn ?? Connector.GetConnection();
IDbTransaction trans = transaction;
try
{
trans = trans ?? conn.BeginTransaction();
rowsAffected = conn.Execute(
SqlMapperUtil.CreateUpdateSQL(this.TableName, parameters ?? entity),
parameters ?? entity,
transaction);
}
finally
{
if (transaction == null)
{
if (trans != null)
{
trans.Commit();
trans.Dispose();
}
}
}
}
finally
{
if (connection == null)
{
if (conn != null)
{
conn.Dispose();
}
}
}
return(rowsAffected);
}
/// <summary>
/// 查询归属某组织下的岗位信息
/// </summary>
/// <param name="input">组织机构PostId</param>
/// <returns>岗位信息</returns>
public Task <IEnumerable <SystemPostOutput> > GetPostByOrganizationId(NullableIdInput input)
{
var sql = new StringBuilder();
sql.Append(
@"SELECT post.PostId,post.Name,post.MainSupervisor,post.MainSupervisorContact,post.State,post.OrderNo,post.Remark,post.CreateTime,post.CreateUserId,post.CreateUserName,post.IsFreeze,post.PostId,org.OrganizationId,org.Name OrganizationName
FROM System_Post post LEFT JOIN System_Organization org ON org.OrganizationId=post.OrganizationId");
if (!input.Id.IsNullOrEmptyGuid())
{
sql.Append(" WHERE post.OrganizationId=@orgId");
}
sql.Append(" ORDER BY post.OrganizationId");
return(SqlMapperUtil.SqlWithParams <SystemPostOutput>(sql.ToString(), new
{
orgId = input.Id
}));
}
/// <summary>
/// 根据菜单id获取数据权限定义
/// </summary>
/// <param name="input">菜单id</param>
/// <returns></returns>
public Task <IEnumerable <SystemDataOutput> > GetDataByMenuId(SystemDataGetDataByMenuIdInput input = null)
{
var sql = new StringBuilder(
"SELECT data.*,menu.Name MenuName FROM System_Data data LEFT JOIN System_Menu menu ON data.MenuId=menu.MenuId WHERE 1=1");
if (input != null)
{
sql.Append(input.Sql);
if (!input.Id.IsNullOrEmptyGuid())
{
sql.Append(" AND data.MenuId=@menuId");
return(SqlMapperUtil.SqlWithParams <SystemDataOutput>(sql.ToString(), new { menuId = input.Id }));
}
}
sql.Append(" ORDER BY data.OrderNo");
return(SqlMapperUtil.SqlWithParams <SystemDataOutput>(sql.ToString()));
}
/// <summary>
/// 根据角色id获取具有的菜单信息
/// </summary>
/// <param name="input"></param>
/// <returns>树形菜单信息</returns>
public Task <IEnumerable <SystemPermission> > GetPermissionByPrivilegeMasterValue(GetPermissionByPrivilegeMasterValueInput input)
{
var sql = new StringBuilder($"select * from Sys_Permission where PrivilegeAccess=@privilegeAccess");
sql.Append(" AND PrivilegeMasterValue=@privilegeMasterValue ");
if (!string.IsNullOrWhiteSpace(input.PrivilegeMenuId))
{
sql.Append(@" AND PrivilegeAccessValue in(select MenuButtonId from
sys_menubutton where MenuId=@privilegeMenuId)");
}
return(SqlMapperUtil.Query <SystemPermission>(sql.ToString(), new
{
privilegeAccess = (byte)input.PrivilegeAccess,
privilegeMasterValue = input.PrivilegeMasterValue
, privilegeMenuId = input.PrivilegeMenuId
}));
}
/// <summary>
/// 根据权限归属Id删除菜单权限信息
/// </summary>
/// <param name="privilegeAccess">权限类型:菜单、功能项</param>
/// <param name="privilegeMasterValue"></param>
/// <param name="privilegeMenuId"></param>
/// <returns></returns>
public Task <bool> DeletePermissionByPrivilegeMasterValue(EnumPrivilegeAccess?privilegeAccess,
Guid privilegeMasterValue, Guid privilegeMenuId)
{
var deleteSql = new StringBuilder("delete from sys_Permission where privilegeMasterValue=@privilegeMasterValue");
if (privilegeAccess != null)
{
deleteSql.Append(" AND PrivilegeAccess=@PrivilegeAccess");
if (privilegeMenuId != null)
{
deleteSql.Append(" AND privilegeMenuId=@privilegeMenuId");
}
return(SqlMapperUtil.InsertUpdateOrDeleteSqlBool
(deleteSql.ToString(), new { PrivilegeAccess = (int)privilegeAccess, privilegeMasterValue, privilegeMenuId }));
}
return(SqlMapperUtil.InsertUpdateOrDeleteSqlBool(deleteSql.ToString(), new { privilegeMasterValue }));
}
/// <summary>
/// 删除菜单按钮功能项
/// </summary>
/// <param name="menuButtonFunction"></param>
/// <returns></returns>
public Task <bool> DeleteMenuButtonFunction(SystemMenuButtonFunction menuButtonFunction)
{
StringBuilder sql = new StringBuilder("DELETE FROM System_MenuButtonFunction WHERE 1=1");
if (!menuButtonFunction.FunctionId.IsEmptyGuid())
{
sql.Append(" AND FunctionId=@functionId");
}
if (!menuButtonFunction.MenuButtonId.IsEmptyGuid())
{
sql.Append(" AND MenuButtonId=@menuButtonId");
}
return(SqlMapperUtil.InsertUpdateOrDeleteSqlBool <SystemUserInfo>(sql.ToString(), new
{
menuButtonId = menuButtonFunction.MenuButtonId,
functionId = menuButtonFunction.FunctionId
}));
}
/// <summary>
/// 根据菜单获取功能项信息
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public Task <IEnumerable <SystemMenuButtonOutput> > GetMenuButtonByMenuId(SystemMenuGetMenuButtonByMenuIdInput input = null)
{
var sql = new StringBuilder();
sql.Append(
"SELECT f.*,menu.Name MenuName FROM System_MenuButton f LEFT JOIN System_Menu menu ON menu.MenuId=f.MenuId WHERE 1=1 ");
if (input != null)
{
sql.Append(input.Sql);
if (!input.Id.IsNullOrEmptyGuid())
{
sql.Append(" AND f.MenuId=@menuId");
}
return(SqlMapperUtil.SqlWithParams <SystemMenuButtonOutput>(sql.ToString(), new { menuId = input.Id }));
}
return(SqlMapperUtil.SqlWithParams <SystemMenuButtonOutput>(sql.ToString()));
}
/// <summary>
/// 查询所有菜单
/// </summary>
/// <param name="haveUrl">是否具有菜单</param>
/// <param name="isMenuShow">是否菜单显示</param>
/// <returns></returns>
public Task <IEnumerable <TreeEntity> > GetAllMenu(bool haveUrl = false, bool isMenuShow = false)
{
var sql = new StringBuilder();
sql.Append(
"select menu.MenuId id,menu.ParentId pId,menu.name,menu.Code,menu.icon icon");
if (haveUrl)
{
sql.Append(",menu.OpenUrl url");
}
sql.Append(" from Sys_Menu menu");
if (isMenuShow)
{
sql.Append(" WHERE menu.IsShowMenu='true'");
}
sql.Append(" ORDER BY menu.OrderNo");
return(SqlMapperUtil.Query <TreeEntity> (sql.ToString()));
}
/// <summary>
/// 获取所有功能项,若input有值则排除该功能项
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public Task <IEnumerable <SystemMenuButtonFunctionOutput> > GetAllFunctions(SystemMenuButtonGetFunctionsInput input)
{
StringBuilder sql =
new StringBuilder(@"SELECT func.* AppCode FROM dbo.System_Function func ");
if (!input.Id.IsNullOrEmptyGuid())
{
sql.Append(
" WHERE 1=1 AND func.FunctionId NOT IN (SELECT FunctionId FROM System_MenuButtonFunction WHERE MenuButtonId=@menuButtonId)");
}
if (input.IsPage)
{
sql.Append(string.Format(" AND func.IsPage='{0}'", input.IsPage));
}
sql.Append(" ORDER BY Area,Controller,IsPage DESC");
return(SqlMapperUtil.SqlWithParams <SystemMenuButtonFunctionOutput>(sql.ToString(), new { menuButtonId = input.Id }));
}
/// <summary>
/// 根据菜单获取功能项信息
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public Task <IEnumerable <SystemMenuButtonOutput> > GetMenuButtonByMenuId(NullableIdInput input)
{
var stringBuilder = new StringBuilder();
stringBuilder.Append(
"SELECT f.*,menu.Name MenuName FROM System_MenuButton f LEFT JOIN System_Menu menu ON menu.MenuId=f.MenuId");
if (input == null)
{
return(SqlMapperUtil.SqlWithParams <SystemMenuButtonOutput>(stringBuilder.ToString(), new { }));
}
if (!input.Id.IsNullOrEmptyGuid())
{
stringBuilder.Append(" WHERE f.MenuId=@menuId");
}
return(SqlMapperUtil.SqlWithParams <SystemMenuButtonOutput>(stringBuilder.ToString(), new { menuId = input.Id }));
}
/// <summary>
/// 根据用户id获取权限
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public Task <IEnumerable <string> > GetHavePermisionStrByUserId(string userId)
{
var sql = new StringBuilder(@"SELECT
menu.Code
FROM
Sys_MenuButton menu
LEFT JOIN Sys_permission sper ON sper.PrivilegeAccessValue = menu.MenuButtonId
LEFT JOIN Sys_permissionuser speruser ON sper.PrivilegeMasterValue = speruser.PrivilegeMasterValue
where
sper.PrivilegeAccess = @privilegeAccess
AND speruser.PrivilegeMasterUserId=@userId
GROUP BY
menu.Code
");
return(SqlMapperUtil.SqlWithParams <string>(sql.ToString(),
new { privilegeAccess = (byte)EnumPrivilegeAccess.菜单按钮, userId }));
}
/// <summary>
/// 根据父级查询下级
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public Task <IEnumerable <SystemOrganizationOutput> > GetOrganizationsByParentId(SystemOrganizationDataPermissionTreeInput input)
{
var sql = new StringBuilder();
sql.Append("select *,(select name from System_Organization o where o.OrganizationId=org.ParentId) ParentName from System_Organization org where 1=1");
if (input.Id != null)
{
sql.Append(input.HaveSelf
? "AND org.ParentIds like '%" + input.Id + "%'"
: "AND org.ParentIds like '%" + (input.Id + ", ").Replace(", ", @",") + "%' ");
}
if (!input.DataSql.IsNullOrEmpty())
{
sql.Append("AND " + input.DataSql);
}
sql.Append(input.Sql);
return(SqlMapperUtil.SqlWithParams <SystemOrganizationOutput>(sql.ToString(), new { pId = input.Id }));
}
/// <summary>
/// 查询所有菜单
/// </summary>
/// <param name="haveUrl">是否具有菜单</param>
/// <param name="isMenuShow">是否菜单显示</param>
/// <returns></returns>
public Task <IEnumerable <TreeEntity> > GetAllMenu(bool haveUrl = false,
bool isMenuShow = false)
{
var sql = new StringBuilder();
sql.Append(
"SELECT menu.MenuId id,menu.ParentId pId,menu.name,menu.icon,menu.code");
if (haveUrl)
{
sql.Append(",((isnull(app.Domain,'')+menu.OpenUrl)) url");
}
sql.Append(" FROM System_Menu menu LEFT JOIN System_App app ON app.AppId=menu.AppId");
if (isMenuShow)
{
sql.Append(" WHERE menu.IsShowMenu='true'");
}
sql.Append(" ORDER BY menu.OrderNo");
return(SqlMapperUtil.SqlWithParams <TreeEntity>(sql.ToString()));
}
/// <summary>
/// 查询归属某组织下的岗位信息
/// </summary>
/// <param name="input">组织机构PostId</param>
/// <returns>岗位信息</returns>
public Task <IEnumerable <SystemPostOutput> > GetPostByOrganizationId(SystemPostGetByOrganizationId input)
{
var sql = new StringBuilder();
sql.Append(
@"SELECT post.*,org.OrganizationId,org.Name OrganizationName
FROM System_Post post LEFT JOIN System_Organization org ON org.OrganizationId=post.OrganizationId WHERE 1=1");
if (input != null && !input.Id.IsNullOrEmptyGuid())
{
sql.Append(@" AND post.OrganizationId in(
select org.OrganizationId from System_Organization org where org.ParentIds like '" + (input.Id + ",").Replace(",", @"\,") + "%" + "' escape '\\' OR OrganizationId = '" + input.Id + "') ");
}
if (input != null)
{
sql.Append(input.Sql);
}
sql.Append(" ORDER BY post.OrganizationId");
return(SqlMapperUtil.SqlWithParams <SystemPostOutput>(sql.ToString()));
}
/// <summary>
/// 根据用户编码获取权限按钮
/// </summary>
/// <param name="UserId"></param>
/// <param name="IsAdmin"></param>
/// <returns></returns>
public Task <IEnumerable <AuthMenuButtonOutput> > GetMenuButtonByUserId(string UserId, bool IsAdmin)
{
StringBuilder sql = new StringBuilder();
sql.Append(@" SELECT
distinct(MenuButtonId),func.* ,menu.Area,menu.Controller,menu.Action
FROM
sys_menubutton func
LEFT JOIN sys_menu menu ON func.MenuId = menu.MenuId
LEFT JOIN sys_permission sper ON sper.PrivilegeAccessValue = func.MenuButtonId
LEFT JOIN sys_permissionuser spuser ON sper.PrivilegeMasterValue = spuser.PrivilegeMasterValue
WHERE 1=1 " );
if (!IsAdmin)
{
sql.AppendFormat(" and spuser.PrivilegeMasterUserId='{0}'", UserId);
}
sql.Append(" order by OrderNo desc");
return(SqlMapperUtil.Query <AuthMenuButtonOutput>(sql.ToString()));
}