public async Task <ActionResult <StatementResponse> > GetStatement([FromQuery] StatementArguments args, CancellationToken cancellation)
{
var serverTime = DateTimeOffset.UtcNow;
var result = await _service.GetStatement(args, cancellation);
// Flatten and Trim
var relatedEntities = Flatten(result.Data, cancellation);
var response = new StatementResponse
{
Closing = result.Closing,
ClosingQuantity = result.ClosingQuantity,
ClosingMonetaryValue = result.ClosingMonetaryValue,
Opening = result.Opening,
OpeningQuantity = result.OpeningQuantity,
OpeningMonetaryValue = result.OpeningMonetaryValue,
TotalCount = result.Count,
CollectionName = ControllerUtilities.GetCollectionName(typeof(DetailsEntry)),
RelatedEntities = relatedEntities,
Result = result.Data,
ServerTime = serverTime,
Skip = args.Skip,
Top = result.Data.Count
};
return(Ok(response));
}
public async Task <ActionResult <StatementResponse> > GetStatement([FromQuery] StatementArguments args, CancellationToken cancellation)
{
return(await ControllerUtilities.InvokeActionImpl(async() =>
{
var serverTime = DateTimeOffset.UtcNow;
var(data, opening, openingQuantity, openingMonetaryValue, closing, closingQuantity, closingMonetaryValue, count) = await _service.GetStatement(args, cancellation);
// Flatten and Trim
var relatedEntities = FlattenAndTrim(data, cancellation);
var response = new StatementResponse
{
Closing = closing,
ClosingQuantity = closingQuantity,
ClosingMonetaryValue = closingMonetaryValue,
Opening = opening,
OpeningQuantity = openingQuantity,
OpeningMonetaryValue = openingMonetaryValue,
TotalCount = count,
CollectionName = ControllerUtilities.GetCollectionName(typeof(DetailsEntry)),
RelatedEntities = relatedEntities,
Result = data,
ServerTime = serverTime,
Skip = args.Skip,
Top = data.Count
};
return Ok(response);
}
, _logger));
}
private static string UndatedFilter(StatementArguments args)
{
// State == Posted
string stateFilter = $"{nameof(DetailsEntry.Line)}.{nameof(LineForQuery.State)} eq {LineState.Posted}";
if (args.IncludeCompleted ?? false)
{
// OR State == Completed
stateFilter = $"({stateFilter} or {nameof(DetailsEntry.Line)}.{nameof(LineForQuery.State)} eq {LineState.Completed})";
}
var undatedFilterBldr = new StringBuilder(stateFilter);
if (args.AccountId != null)
{
undatedFilterBldr.Append($" and {nameof(DetailsEntry.AccountId)} eq {args.AccountId.Value}");
}
if (args.AgentId != null)
{
undatedFilterBldr.Append($" and {nameof(DetailsEntry.AgentId)} eq {args.AgentId.Value}");
}
if (args.ResourceId != null)
{
undatedFilterBldr.Append($" and {nameof(DetailsEntry.ResourceId)} eq {args.ResourceId.Value}");
}
if (args.NotedAgentId != null)
{
undatedFilterBldr.Append($" and {nameof(DetailsEntry.NotedAgentId)} eq {args.NotedAgentId.Value}");
}
if (args.NotedResourceId != null)
{
undatedFilterBldr.Append($" and {nameof(DetailsEntry.NotedResourceId)} eq {args.NotedResourceId.Value}");
}
if (args.EntryTypeId != null)
{
undatedFilterBldr.Append($" and {nameof(DetailsEntry.EntryTypeId)} eq {args.EntryTypeId.Value}");
}
if (args.CenterId != null)
{
undatedFilterBldr.Append($" and {nameof(DetailsEntry.CenterId)} eq {args.CenterId.Value}");
}
if (!string.IsNullOrWhiteSpace(args.CurrencyId))
{
undatedFilterBldr.Append($" and {nameof(DetailsEntry.CurrencyId)} eq '{args.CurrencyId.Replace("'", "''")}'");
}
return(undatedFilterBldr.ToString());
}
public async Task <StatementResult> GetStatement(StatementArguments args, CancellationToken cancellation)
{
await Initialize(cancellation);
// Step 1: Prepare the filters
string undatedFilter = UndatedFilter(args);
var beforeOpeningFilterBldr = new StringBuilder(undatedFilter);
var betweenFilterBldr = new StringBuilder(undatedFilter);
var beforeClosingFilterBldr = new StringBuilder(undatedFilter);
if (args.FromDate != null)
{
beforeOpeningFilterBldr.Append($" and {nameof(DetailsEntry.Line)}.{nameof(LineForQuery.PostingDate)} lt '{args.FromDate.Value:yyyy-MM-dd}'"); // <
betweenFilterBldr.Append($" and {nameof(DetailsEntry.Line)}.{nameof(LineForQuery.PostingDate)} ge '{args.FromDate.Value:yyyy-MM-dd}'"); // >=
}
if (args.ToDate != null)
{
betweenFilterBldr.Append($" and {nameof(DetailsEntry.Line)}.{nameof(LineForQuery.PostingDate)} le '{args.ToDate.Value:yyyy-MM-dd}'"); // <=
beforeClosingFilterBldr.Append($" and {nameof(DetailsEntry.Line)}.{nameof(LineForQuery.PostingDate)} le '{args.ToDate.Value:yyyy-MM-dd}'"); // <=
}
string beforeOpeningFilter = beforeOpeningFilterBldr.ToString();
string betweenDatesFilter = betweenFilterBldr.ToString();
string beforeClosingFilter = beforeClosingFilterBldr.ToString();
// Step 2: Load the entries
var factArgs = new GetArguments
{
Select = args.Select,
Top = args.Skip + args.Top, // We need this to compute openining balance, we do the skipping later in memory
Skip = 0, // args.Skip,
OrderBy = $"{nameof(DetailsEntry.Line)}.{nameof(LineForQuery.PostingDate)},{nameof(DetailsEntry.Direction)} desc,{nameof(DetailsEntry.Id)}",
CountEntities = true,
Filter = betweenDatesFilter,
};
var result = await GetEntities(factArgs, cancellation);
var data = result.Data;
var count = result.Count;
// Step 3: Load the opening balances
string valueExp = $"sum({nameof(DetailsEntry.Value)} * {nameof(DetailsEntry.Direction)})";
string quantityExp = $"sum({nameof(DetailsEntry.BaseQuantity)} * {nameof(DetailsEntry.Direction)})";
string monetaryValueExp = $"sum({nameof(DetailsEntry.MonetaryValue)} * {nameof(DetailsEntry.Direction)})";
var openingArgs = new GetAggregateArguments
{
Filter = beforeOpeningFilter,
Select = $"{valueExp},{quantityExp},{monetaryValueExp}"
};
var openingResult = await GetAggregate(openingArgs, cancellation);
var openingData = openingResult.Data;
decimal opening = (decimal)(openingData[0][0] ?? 0m);
decimal openingQuantity = (decimal)(openingData[0][1] ?? 0m);
decimal openingMonetaryValue = (decimal)(openingData[0][2] ?? 0m);
// step (4) Add the Acc. column
decimal acc = opening;
decimal accQuantity = openingQuantity;
decimal accMonetaryValue = openingMonetaryValue;
foreach (var entry in data)
{
acc += (entry.Value ?? 0m) * entry.Direction ?? throw new InvalidOperationException("Bug: Missing Direction");
entry.Accumulation = acc;
entry.EntityMetadata[nameof(entry.Accumulation)] = FieldMetadata.Loaded;
accQuantity += (entry.BaseQuantity ?? 0m) * entry.Direction ?? throw new InvalidOperationException("Bug: Missing Direction");
entry.QuantityAccumulation = accQuantity;
entry.EntityMetadata[nameof(entry.QuantityAccumulation)] = FieldMetadata.Loaded;
accMonetaryValue += (entry.MonetaryValue ?? 0m) * entry.Direction ?? throw new InvalidOperationException("Bug: Missing Direction");
entry.MonetaryValueAccumulation = accMonetaryValue;
entry.EntityMetadata[nameof(entry.MonetaryValueAccumulation)] = FieldMetadata.Loaded;
}
// Step (5) Load closing (if the data page is not the complete result)
decimal closing;
decimal closingQuantity;
decimal closingMonetaryValue;
if (args.Skip + args.Top >= count.Value)
{
var closingArgs = new GetAggregateArguments
{
Filter = beforeClosingFilter,
Select = $"{valueExp},{quantityExp},{monetaryValueExp}"
};
var closingResult = await GetAggregate(closingArgs, cancellation);
var closingData = closingResult.Data;
closing = (decimal)(closingData[0][0] ?? 0m);
closingQuantity = (decimal)(closingData[0][1] ?? 0m);
closingMonetaryValue = (decimal)(closingData[0][2] ?? 0m);
}
else
{
closing = acc;
closingQuantity = accQuantity;
closingMonetaryValue = accMonetaryValue;
}
data = data.Skip(args.Skip).ToList(); // Skip in memory
return(new StatementResult(data, opening, openingQuantity, openingMonetaryValue, closing, closingQuantity, closingMonetaryValue, count.Value));
}
