private async Task <IEnumerable <PostingAccountBalance> > GetPostingAccountBalances(
IList <Olma.PostingAccount> postingAccounts = null, bool useCalculatedBalance = true)
{
IQueryable <Ltms.Bookings> ltmsBookingsQuery = null;
if (postingAccounts != null)
{
if (postingAccounts.Count == 0)
{
return(new List <PostingAccountBalance>());
}
// build a union clause with all posting accounts and potential date limiters
foreach (var postingAccount in postingAccounts)
{
IQueryable <Ltms.Bookings> subQuery;
if (useCalculatedBalance && postingAccount.CalculatedBalance == null)
{
subQuery = _ltmsBookingsRepo.FindAll()
.AsNoTracking()
.Where(i => i.Quantity.HasValue)
.Where(i => i.AccountId == postingAccount.RefLtmsAccountId);
}
else
{
subQuery = _ltmsBookingsRepo.FindAll()
.AsNoTracking()
.Where(i => i.Quantity.HasValue)
// TODO discuss if created field is the correct field to filter on
.Where(i => i.AccountId == postingAccount.RefLtmsAccountId &&
i.CreateTime > postingAccount.CalculatedBalance.LastBookingDateTime);
}
// TODO discuss if we need to filter on bookings type
//subQuery = subQuery.Where(i => i.BookingTypeId == 0)
ltmsBookingsQuery =
ltmsBookingsQuery == null ? subQuery : ltmsBookingsQuery.Union(subQuery);
}
}
else
{
ltmsBookingsQuery = _ltmsBookingsRepo.FindAll()
.AsNoTracking()
.IgnoreQueryFilters()
.Where(i => i.OlmaPostingAccount != null);
}
var inCoordinationQuery = ltmsBookingsQuery
.Where(i => i.IncludeInBalance && !i.Matched && i.ReportBookings.Any(rb =>
rb.Report.DeleteTime == null && rb.Report.ReportStateId == "U"));
// perfrom left outer join to add uncordinated info to data before grouping as other ef core will fail
var joined = from balance in ltmsBookingsQuery
join inCoordination in inCoordinationQuery on balance.Id equals inCoordination.Id into
inCoordinationMatch
from u in inCoordinationMatch.DefaultIfEmpty()
select new { balance, isInCoordination = u != null };
// now we need to group by
var dbResult = joined
.GroupBy(i => new
{
i.balance.AccountId,
i.balance.ArticleId,
i.balance.QualityId,
})
.Select(g => new
{
g.Key,
Balances = new
{
LastBookingDateTime = g.Max(i => i.balance.BookingDate),
CoordinatedBalance = g.Sum(i => i.balance.Matched ? i.balance.Quantity.Value : 0),
ProvisionalCharge = g.Sum(i =>
!i.balance.Matched && !i.isInCoordination && !i.balance.IncludeInBalance &&
i.balance.Quantity > 0
? i.balance.Quantity.Value
: 0),
ProvisionalCredit = g.Sum(i =>
!i.balance.Matched && !i.isInCoordination && !i.balance.IncludeInBalance &&
i.balance.Quantity < 0
? i.balance.Quantity.Value * -1
: 0),
InCoordinationCharge = g.Sum(i =>
!i.balance.Matched && i.isInCoordination && i.balance.IncludeInBalance &&
i.balance.Quantity > 0
? i.balance.Quantity.Value
: 0),
InCoordinationCredit = g.Sum(i =>
!i.balance.Matched && i.isInCoordination && i.balance.IncludeInBalance &&
i.balance.Quantity < 0
? i.balance.Quantity.Value * -1
: 0),
UncoordinatedCharge = g.Sum(i =>
!i.balance.Matched && !i.isInCoordination && i.balance.IncludeInBalance &&
i.balance.Quantity > 0
? i.balance.Quantity.Value
: 0),
UncoordinatedCredit = g.Sum(i =>
!i.balance.Matched && !i.isInCoordination && i.balance.IncludeInBalance &&
i.balance.Quantity < 0
? i.balance.Quantity.Value * -1
: 0),
}
})
// HACK Balances from Bookings are cached after first request
.FromCache()
.ToList();
var ltmsAccountIds = dbResult.Select(i => i.Key.AccountId).Distinct().ToArray();
var accountMap = _ltmsLookup.GetOlmaPostingAccountIds(ltmsAccountIds);
var ltmsArticleQualityIds = dbResult.Select(i => Tuple.Create(i.Key.ArticleId, i.Key.QualityId)).Distinct()
.ToArray();
var loadCarrierMap = _ltmsLookup.GetOlmaLoadCarrierIds(ltmsArticleQualityIds);
// convert db result to dictionary
var postingAccountBalances = dbResult
// HACK Fix mappings Tuple(6,12)
.Where(i => loadCarrierMap.ContainsKey(Tuple.Create(i.Key.ArticleId, i.Key.QualityId)))
.Select(i => new PostingAccountBalance()
{
PostingAccountId = accountMap[i.Key.AccountId],
LoadCarrierId = loadCarrierMap[Tuple.Create(i.Key.ArticleId, i.Key.QualityId)],
LastBookingDateTime = i.Balances.LastBookingDateTime,
CoordinatedBalance = i.Balances.CoordinatedBalance,
ProvisionalCharge = i.Balances.ProvisionalCharge,
ProvisionalCredit = i.Balances.ProvisionalCredit,
InCoordinationCharge = i.Balances.InCoordinationCharge,
InCoordinationCredit = i.Balances.InCoordinationCredit,
UncoordinatedCharge = i.Balances.UncoordinatedCharge,
UncoordinatedCredit = i.Balances.UncoordinatedCredit,
});
return(postingAccountBalances);
}
