예제 #1
0
        /// <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);
        }
예제 #2
0
        /// <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);
        }