public void ApplyPaginationForOracle(string orderByFieldAlias, SortOrderTypes sortDirection, int startRow, int endRow)
{
//add order by clause
this.AddOrderByClause(orderByFieldAlias, sortDirection);
//Create a new query builder for paged query
QueryBuilder pagedQueryBuilder = new QueryBuilder(SQLType.SelectSQL, this.Name);
//Add current query builder as an inner query to new query builder
pagedQueryBuilder.AddTable(this.QueryBuilder, pagedTableName);
//Add all columns to new query builder
foreach (var fieldName in this.FieldNames)
{
pagedQueryBuilder.AddField(pagedTableName, fieldName, fieldName);
}
//Add row number column to the new query builder
//TO DO:Add this to the Data Helper
pagedQueryBuilder.AddField("ROWNUM AS " + rowNumberColumnNameForOracle);
this.QueryBuilder = pagedQueryBuilder;
//Add new query builder as an inner query to ANOTHER new query builder
QueryBuilder newPagedQueryBuilder = new QueryBuilder(SQLType.SelectSQL, this.Name);
newPagedQueryBuilder.AddTable(pagedQueryBuilder, newPagedTableName);
foreach (var fieldName in this.FieldNames)
{
if (fieldName != rowNumberColumnNameForOracle)
{
newPagedQueryBuilder.AddField(newPagedTableName, fieldName, fieldName);
}
}
//Add pagination where clause to new query builder
newPagedQueryBuilder.AddWhereClauseItem(DataHelper.Between(rowNumberColumnNameForOracle, startRow.ToString(CultureInfo.InvariantCulture), endRow.ToString(CultureInfo.InvariantCulture)));
this.QueryBuilder = newPagedQueryBuilder;
}
public async Task <IActionResult> GetValueLog(Guid uuid, DateTime?from, DateTime?until, int count = 100, SortOrderTypes order = SortOrderTypes.DESC, DateTime?start = null, bool sticky = false)
{
try
{
_logger?.LogDebug($"GetValueLog({uuid}, {from}, {until}, {count}, {order}, {start}, {sticky})...");
if (!_zipato.IsLocked)
{
return(StatusCode(StatusCodes.Status406NotAcceptable, "Locked: update not yet finished."));
}
var(report, status) = await _zipato.ReadLogAttributeAsync(uuid, from, until, count, order, start, sticky);
if (status.IsGood)
{
if (report != null)
{
return(Ok(report));
}
else
{
return(NotFound($"Value logs for attribute with uuid '{uuid}' not found."));
}
}
else
{
return(StatusCode(StatusCodes.Status502BadGateway, status));
}
}
catch (Exception ex)
{
return(StatusCode(StatusCodes.Status500InternalServerError, ex.Message));
}
}
/// <summary>
/// An overload method of AddOrderByClause method.Gets Expression as SqlExpression type and the sortOder to set the OrderBy clause
/// </summary>
/// <param name="expression">SqlExpression </param>
/// <param name="sortOrder">SortOrderTypes</param>
public void AddOrderByClause(SqlExpression expression, SortOrderTypes sortOrder)
{
queryBuilder.AddOrderClauseItem(expression.ToString(), sortOrder, orderBySequance);
orderBySequance++;
}
/// <summary>
///
/// </summary>
/// <param name="from"></param>
/// <param name="until"></param>
/// <param name="count"></param>
/// <param name="order"></param>
/// <param name="start"></param>
/// <param name="sticky"></param>
/// <returns></returns>
private string CreateQuery(DateTime?from = null, DateTime?until = null, int count = 100, SortOrderTypes order = SortOrderTypes.DESC, DateTime?start = null, bool sticky = false)
{
string query = string.Empty;
if (from.HasValue && until.HasValue)
{
query += $"?from={from.Value.ToString("o")}";
query += $"&until={until.Value.ToString("o")}";
query += $"&count={count}";
}
else
{
query += $"?count={count}";
}
query += $"&order={order.ToString().ToLower()}";
if (start.HasValue)
{
query += $"?start={start.Value.ToString("o")}";
query += $"?sticky={sticky.ToString().ToLower()}";
}
return(query);
}
