/// <summary>
/// Recupera os usuarios de forma paginada
/// </summary>
/// <param name="advancedFilter"></param>
/// <returns></returns>
public UsuariosClienteResponse GetAllPaged(FilterUsuarioRequest advancedFilter)
{
var result = new UsuariosClienteResponse();
var sql = string.Empty;
var sqlCount = string.Empty;
try
{
using (var conn = CreateConnection())
{
result.TotalGeral = (int)conn.ExecuteScalar("SELECT COUNT(*) FROM Usuario_Cliente");
sql = RecuperarQuery(advancedFilter);
result.Usuarios = conn.Query <UsuarioCliente, Cliente, UsuarioCliente>(sql,
map: (usuario, cliente) =>
{
usuario.Cliente = cliente;
return(usuario);
},
splitOn: "id_cliente_entity").Distinct().ToList();
}
return(result);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Monta a clausula Order By
/// </summary>
/// <param name="advancedFilter"></param>
/// <returns></returns>
private static string BuildOrderBy(FilterUsuarioRequest advancedFilter)
{
var orderByClause = new StringBuilder();
orderByClause.Append("ORDER BY uc." + advancedFilter.OrderBy + " " + advancedFilter.Direction);
return(orderByClause.ToString());
}
/// <summary>
/// Monta as clausulas Offset e Fetch Next
/// </summary>
/// <param name="advancedFilter"></param>
/// <returns></returns>
private static string BuildOffSetNumRows(FilterUsuarioRequest advancedFilter)
{
if (advancedFilter.NumRows == 0)
{
return(string.Empty);
}
var offSetNumRowsClauses = new StringBuilder();
offSetNumRowsClauses.Append(" OFFSET " + advancedFilter.OffSet + " ROWS");
offSetNumRowsClauses.Append(" FETCH NEXT " + advancedFilter.NumRows + " ROWS ONLY");
return(offSetNumRowsClauses.ToString());
}
/// <summary>
/// Monta a query
/// </summary>
/// <param name="advancedFilter"></param>
/// <returns></returns>
private static string RecuperarQuery(FilterUsuarioRequest advancedFilter)
{
var sql = new StringBuilder();
var selectClause = string.Empty;
var orderByClause = string.Empty;
var offSetNumRows = string.Empty;
selectClause = selectQuery;
orderByClause = BuildOrderBy(advancedFilter);
offSetNumRows = BuildOffSetNumRows(advancedFilter);
sql.Append(selectClause);
sql.Append(orderByClause);
sql.Append(offSetNumRows);
return(sql.ToString());
}
public IHttpActionResult GetAllPaged(FilterUsuarioRequest advancedFilter)
{
try
{
var result = _usuarioClienteBusiness.GetAllPaged(advancedFilter);
var lista = result.Usuarios.ToList().Select(Mapper.Map <UsuarioCliente, UsuarioClienteResponse>);
var totalGeral = result.TotalGeral;
var totalLinhas = lista.Count();
//Monta response
_result = Ok(Retorno <IEnumerable <UsuarioClienteResponse> > .Criar(true, "Consulta Realizada Com Sucesso", lista, totalGeral));
//Retorna o response
return(_result);
}
catch (Exception)
{
throw new HttpResponseException(HttpStatusCode.InternalServerError);
}
}
public UsuariosClienteResponse GetAllPaged(FilterUsuarioRequest advancedFilter)
{
return(_repository.GetAllPaged(advancedFilter));
}
