public async Task <List <UsuarioTabelaViewDto> > PesquisarTodosUsuariosAsync() { try { var auxDto = new UsuarioTabelaViewDto(); var listaViewDto = await _context.Users .Include(p => p.ListaUsuarioPerfil) .OrderBy(u => u.NomeCompleto) .Select(item => new UsuarioTabelaViewDto { Id = item.Id, Matricula = item.UserName, Nome = item.NomeCompleto, Email = item.Email, StatusUsuario = auxDto.descricaoStatusUsuarioEnum(item.StatusUsuario.ToString()), ListaPerfis = item.ListaUsuarioPerfil.OrderBy(x => x.Perfil.Name).Select(x => x.Perfil.Name).ToList(), }).ToListAsync(); return(listaViewDto); } catch (Exception ex) { throw ex; } }
public async Task <UsuarioPaginacaoViewtDto> PesquisarUsuariosPorFiltrosPaginacaoAsync(UsuarioFiltroDto filtroDto) { try { UsuarioPaginacaoViewtDto paginacaoViewDto = new UsuarioPaginacaoViewtDto(); filtroDto.RegistroInicial = filtroDto.RegistroInicial <= 1 ? 0 : filtroDto.RegistroInicial - 1; string query = @"SELECT SQL_CALC_FOUND_ROWS DISTINCT "; query = query + "usu.Id, usu.UserName, " + " usu.NomeCompleto, " + " usu.Email, " + " usu.StatusUsuario, "; query = query + "GROUP_CONCAT( (SELECT per.Name FROM perfil AS per WHERE Id = up.RoleId) SEPARATOR ', ' ) AS 'ListaPerfis' "; query = query + "FROM Usuario AS usu "; query = query + "INNER JOIN Usuarioperfil AS up ON up.UserId = usu.Id "; // WHERE query = query + " WHERE 1 = 1 "; if (!string.IsNullOrEmpty(filtroDto.Matricula)) { query = query + $" AND usu.UserName = {filtroDto.Matricula}"; } if (!string.IsNullOrEmpty(filtroDto.Nome)) { query = query + $" AND usu.NomeCompleto LIKE '%{filtroDto.Nome}%'"; } if (!string.IsNullOrEmpty(filtroDto.StatusUsuario)) { query = query + $" AND usu.StatusUsuario = {int.Parse(filtroDto.StatusUsuario)}"; } if (!string.IsNullOrEmpty(filtroDto.DataHoraCadastroInicio) && !string.IsNullOrEmpty(filtroDto.DataHoraCadastroFim)) { query = query + $" AND usu.DataHoraCadastro >= '{filtroDto.DataHoraCadastroInicio}'"; query = query + $" AND usu.DataHoraCadastro <= '{filtroDto.DataHoraCadastroFim}'"; } query = query + "GROUP BY usu.UserName "; query = query + "ORDER BY usu.NomeCompleto"; // LIMIT RegistroInicial, QtdRegistroPorPagina; query = query + $" LIMIT {filtroDto.RegistroInicial},{filtroDto.QtdRegistroPorPagina}"; string queryTotalRegitrosEncontrados = "SELECT FOUND_ROWS() AS totalRegistros"; var connection = _context.Database.GetDbConnection(); using (var command = connection.CreateCommand()) { await connection.OpenAsync(); command.CommandText = query; using (var dataReader = await command.ExecuteReaderAsync()) { if (dataReader.HasRows) { while (dataReader.Read()) { // Registros UsuarioTabelaViewDto viewDto = new UsuarioTabelaViewDto(); viewDto.Id = Guid.Parse(dataReader["Id"].ToString()); viewDto.Matricula = dataReader["UserName"].ToString(); viewDto.Nome = dataReader["NomeCompleto"].ToString(); viewDto.Email = dataReader["Email"].ToString(); viewDto.StatusUsuario = viewDto.descricaoStatusUsuarioEnum(dataReader["StatusUsuario"].ToString()); viewDto.ListaPerfis = dataReader["ListaPerfis"].ToString().Split(",").Select(item => item.Trim()).ToList(); paginacaoViewDto.ListaUsuarioTabelaViewDto.Add(viewDto); } } } command.CommandText = queryTotalRegitrosEncontrados; using (var dataReader = command.ExecuteReader()) { if (dataReader.HasRows) { while (dataReader.Read()) { // Total Registros.: TotalRegistros paginacaoViewDto.TotalRegistros = int.Parse(dataReader["totalRegistros"].ToString());; } } } } paginacaoViewDto.preencherDadosPaginacao(filtroDto.QtdRegistroPorPagina, filtroDto.RegistroInicial); return(paginacaoViewDto); } catch (Exception ex) { throw ex; } }