public async Task <ActionResult> PesquisarUsuariosPorFiltrosPaginacao([FromQuery] UsuarioFiltroDto usuarioFiltroDto) { UsuarioPaginacaoViewtDto viewDto = await _usuarioService.PesquisarUsuariosPorFiltrosPaginacaoAsync(usuarioFiltroDto); if (viewDto.ListaUsuarioTabelaViewDto == null || viewDto.ListaUsuarioTabelaViewDto.Count == 0 || viewDto.ListaUsuarioTabelaViewDto.Contains(null)) { return(NotFound(new { success = false, status = 404, mensagem = "Nenhum usuário foi encontrado!", })); } return(CustomResponse(viewDto)); }
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; } }