/// <summary>
/// 获取用户列表(分页)
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PagingResult <GetPagingUsersResponse> GetPagingUsers(GetPagingUsersRequest request)
{
//获取当前机构包括所有子机构(如果有子机构的话)id
//分页查询和获取总数
PagingResult <GetPagingUsersResponse> result = null;
List <int> orgIds = null;
var totalCount = 0;
var startIndex = (request.PageIndex - 1) * request.PageSize + 1;
var endIndex = request.PageIndex * request.PageSize;
//默认获取所有用户(不跟机构关联)
if (request.OrgId == 0)
{
using (var conn = DapperHelper.CreateConnection())
{
var multi = conn.QueryMultiple(@"--获取所有用户
SELECT r.*
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY u.created_time DESC ) AS RowNum ,
u.id ,
u.user_id AS UserId ,
u.user_name AS UserName ,
u.is_change_pwd AS IsChangePwd ,
u.enable_flag AS EnableFlag ,
u.created_time AS CreatedTime,
u.last_updated_time AS LastUpdatedTime
FROM dbo.t_rights_user AS u
) AS r
WHERE r.RowNum BETWEEN @Start AND @End;
--获取所有用户total
SELECT COUNT(DISTINCT u.id) FROM dbo.t_rights_user AS u;", new { @Start = startIndex, @End = endIndex });
var query1 = multi.Read <GetPagingUsersResponse>();
var query2 = multi.Read <int>();
totalCount = query2.First();
result = new PagingResult <GetPagingUsersResponse>(totalCount, request.PageIndex, request.PageSize, query1);
}
}
else
{
var childrenOrgs = new RightsOrganizationDao().GetChildrenOrgs(request.OrgId);
if (childrenOrgs.HasValue())
{
orgIds = childrenOrgs.DistinctBy(p => p.Id).OrderBy(p => p.Id).Select(p => p.Id).ToList();
}
using (var conn = DapperHelper.CreateConnection())
{
var multi = conn.QueryMultiple(@"--CTE,目的distinct
WITH cte_paging_user AS
(
SELECT DISTINCT u.id ,
u.user_id AS UserId ,
u.user_name AS UserName ,
u.is_change_pwd AS IsChangePwd ,
u.enable_flag AS EnableFlag ,
u.created_time AS CreatedTime,
u.last_updated_time AS LastUpdatedTime
FROM dbo.t_rights_user AS u
LEFT JOIN dbo.t_rights_user_organization AS userOrg ON u.id = userOrg.user_id
WHERE userOrg.organization_id IN @OrgIds
)
--分页
SELECT r.*
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY cu.id) AS RowNum, cu.* FROM cte_paging_user AS cu
) AS r
WHERE r.RowNum BETWEEN @Start AND @End;
--total
SELECT COUNT(DISTINCT u.id)
FROM dbo.t_rights_user AS u
LEFT JOIN dbo.t_rights_user_organization AS userOrg ON u.id = userOrg.user_id
WHERE userOrg.organization_id IN @OrgIds;", new { @OrgIds = orgIds, @Start = startIndex, @End = endIndex });
var query1 = multi.Read <GetPagingUsersResponse>();
var query2 = multi.Read <int>();
totalCount = query2.First();
result = new PagingResult <GetPagingUsersResponse>(totalCount, request.PageIndex, request.PageSize, query1);
}
}
//获取每个用户所属机构和所拥有的角色
using (var conn = DapperHelper.CreateConnection())
{
List <TRightsOrganization> userOrgs = null;
List <TRightsRole> userRoles = null;
foreach (var user in result.Entities)
{
userOrgs = conn.Query <TRightsOrganization, TRightsUserOrganization, TRightsUser, TRightsOrganization>(@"SELECT org.parent_id AS ParentId, org.organization_type AS OrganizationType, org.enable_flag AS EnableFlag,
org.created_by AS CreatedBy, org.created_time AS CreatedTime, org.last_updated_by AS LastUpdatedBy, org.last_updated_time AS LastUpdatedTime,*
FROM dbo.t_rights_organization AS org
LEFT JOIN dbo.t_rights_user_organization AS userOrg ON org.id= userOrg.organization_id
LEFT JOIN dbo.t_rights_user AS u ON userOrg.user_id= u.id
WHERE u.id= @UserId;", (org, userOrg, u) => { return(org); }, new { @UserId = user.Id }).ToList();
userRoles = conn.Query <TRightsRole, TRightsUserRole, TRightsUser, TRightsRole>(@"SELECT r.organization_id AS OrganizationId, r.created_by AS CreatedBy, r.created_time AS CreatedTime, r.last_updated_by AS LastUpdatedBy,
r.last_updated_time AS LastUpdatedTime,*
FROM dbo.t_rights_role AS r
LEFT JOIN dbo.t_rights_user_role AS userRole ON r.id= userRole.role_id
LEFT JOIN dbo.t_rights_user AS u ON userRole.user_id= u.id
WHERE u.id= @UserId;", (role, userRole, u) => { return(role); }, new { @UserId = user.Id }).ToList();
user.UserOrgIds = string.Join(",", userOrgs.Select(p => p.Id).ToList());
user.UserOrgNames = string.Join(",", userOrgs.Select(p => p.Name).ToList());
user.UserRoleIds = string.Join(",", userRoles.Select(p => p.Id).ToList());
user.UserRoleNames = string.Join(",", userRoles.Select(p => p.Name).ToList());
}
}
return(result);
}
/// <summary>
/// 角色列表(分页)
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public PagingResult <GetPagingRolesResponse> GetPagingRoles(GetPagingRolesRequest request)
{
PagingResult <GetPagingRolesResponse> result = null;
List <int> orgIds = null;
var totalCount = 0;
var startIndex = (request.PageIndex - 1) * request.PageSize + 1;
var endIndex = request.PageIndex * request.PageSize;
//默认获取所有角色(不跟机构关联)
if (request.OrgId == 0)
{
using (var conn = DapperHelper.CreateConnection())
{
var multi = conn.QueryMultiple(@"--获取所有角色并分页
SELECT rs.*
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY r.created_time DESC ) AS RowNum ,
r.id,
r.name,
r.description,
r.organization_id AS OrgId ,
r.created_by AS CreatedBy ,
r.created_time AS CreatedTime ,
r.last_updated_by AS LastUpdatedBy ,
r.last_updated_time AS LastUpdatedTime ,
org.name AS OrgName
FROM dbo.t_rights_role AS r
LEFT JOIN dbo.t_rights_organization AS org ON r.organization_id= org.id
) AS rs
WHERE rs.RowNum BETWEEN @Start AND @End;
--获取所有角色total
SELECT COUNT(DISTINCT r.id) FROM dbo.t_rights_role AS r;", new { @Start = startIndex, @End = endIndex });
var query1 = multi.Read <GetPagingRolesResponse>();
var query2 = multi.Read <int>();
totalCount = query2.First();
result = new PagingResult <GetPagingRolesResponse>(totalCount, request.PageIndex, request.PageSize, query1);
}
}
else
{
var childrenOrgs = new RightsOrganizationDao().GetChildrenOrgs(request.OrgId);
if (childrenOrgs.HasValue())
{
orgIds = childrenOrgs.DistinctBy(p => p.Id).OrderBy(p => p.Id).Select(p => p.Id).ToList();
}
using (var conn = DapperHelper.CreateConnection())
{
var multi = conn.QueryMultiple(@"--获取指定机构(包括所有子机构)的角色并分页
SELECT rs.*
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY r.created_time DESC ) AS RowNum ,
r.id,
r.name,
r.description,
r.organization_id AS OrgId ,
r.created_by AS CreatedBy ,
r.created_time AS CreatedTime ,
r.last_updated_by AS LastUpdatedBy ,
r.last_updated_time AS LastUpdatedTime ,
org.name AS OrgName
FROM dbo.t_rights_role AS r
LEFT JOIN dbo.t_rights_organization AS org ON r.organization_id= org.id
WHERE r.organization_id IN @OrgIds
) AS rs
WHERE rs.RowNum BETWEEN @Start AND @End;
--获取指定机构(包括所有子机构)的角色total
SELECT COUNT(DISTINCT r.id) FROM dbo.t_rights_role AS r
WHERE r.organization_id IN @OrgIds;", new { @OrgIds = orgIds, @Start = startIndex, @End = endIndex });
var query1 = multi.Read <GetPagingRolesResponse>();
var query2 = multi.Read <int>();
totalCount = query2.First();
result = new PagingResult <GetPagingRolesResponse>(totalCount, request.PageIndex, request.PageSize, query1);
}
}
return(result);
}
