Overview: This code snippet demonstrates how use linq to aggregate an summed amount on the general ledger. Remember to use the base method GetObjectsAsync to get the AcctRec record to the avoid the entity framework duplicate id error. The amount is group by the the document number and the amount is summed into a variable called Amount paid. The open amount then is the total amount of the invoice less the amount paid to date less discount if applicable.
Linq
public async Task<bool> UpdateReceivableByCashLedger(GeneralLedgerView ledgerView)
{
bool retVal = false;
try
{
List<AcctRec> list = GetObjectsAsync(e => e.DocNumber == ledgerView.DocNumber).ToList<AcctRec>();
AcctRec acctRec = list[0];
if (acctRec != null)
{
//Find the General Ledger Cash Amount by Doc Number
var query = await (from e in _dbContext.GeneralLedgers
where e.DocNumber == ledgerView.DocNumber
&& e.DocType == "PV"
&& e.LedgerType == "AA"
&& e.AccountId== ledgerView.AccountId
group e by e.DocNumber
into g
select new { AmountPaid = g.Sum(e => e.Amount) }
).FirstOrDefaultAsync();
decimal? cash = query?.AmountPaid??0;
acctRec.DebitAmount = cash;
acctRec.OpenAmount = acctRec.Amount - acctRec.DebitAmount;
decimal discountAmount = acctRec.Amount * acctRec.DiscountPercent ?? 0;
//Check for Discount Dates
if (acctRec.DiscountDueDate <= ledgerView.GLDate)
{
acctRec.OpenAmount = acctRec.Amount - acctRec.DebitAmount - discountAmount;
}
UpdateObject(acctRec);
return true;
}
return false;
}
catch (Exception ex)
{ throw new Exception(GetMyMethodName(), ex); }
}
