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