public ClientesResponse GetAllPaged(FilterClienteRequest advancedFilter)
{
var result = new ClientesResponse();
var sql = string.Empty;
var sqlCount = string.Empty;
try
{
using (var conn = CreateConnection())
{
result.TotalGeral = (int)conn.ExecuteScalar("SELECT COUNT(*) FROM Cliente");
sql = RecuperarQuery(advancedFilter);
result.Clientes = conn.Query <Cliente, Empresa, Cliente>(sql,
map: (cliente, empresa) =>
{
cliente.Empresa = empresa;
return(cliente);
},
splitOn: "id_empresa_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(FilterClienteRequest advancedFilter)
{
var orderByClause = new StringBuilder();
orderByClause.Append("ORDER BY c." + 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(FilterClienteRequest 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 string RecuperarQuery(FilterClienteRequest 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(FilterClienteRequest advancedFilter)
{
try
{
var result = _clienteBusiness.GetAllPaged(advancedFilter);
var lista = result.Clientes.ToList().Select(Mapper.Map <Cliente, ClienteResponse>);
var totalGeral = result.TotalGeral;
var totalLinhas = lista.Count();
//Monta response
_result = Ok(Retorno <IEnumerable <ClienteResponse> > .Criar(true, "Consulta Realizada Com Sucesso", lista, totalGeral));
//Retorna o response
return(_result);
}
catch (Exception)
{
throw new HttpResponseException(HttpStatusCode.InternalServerError);
}
}
public ClientesResponse GetAllPaged(FilterClienteRequest advancedFilter)
{
return(_repository.GetAllPaged(advancedFilter));
}