protected override List <DiscrepancyByAccountEnqResult> SelectDetails()
{
var list = new List <DiscrepancyByAccountEnqResult>();
ARGLDiscrepancyEnqFilter header = Filter.Current;
if (header == null ||
header.BranchID == null ||
header.PeriodFrom == null ||
header.PeriodTo == null ||
header.AccountID == null)
{
return(list);
}
#region GL balances
AccountByPeriodEnq graphGL = PXGraph.CreateInstance <AccountByPeriodEnq>();
AccountByPeriodFilter filterGL = PXCache <AccountByPeriodFilter> .CreateCopy(graphGL.Filter.Current);
graphGL.Filter.Cache.SetDefaultExt <AccountByPeriodFilter.ledgerID>(filterGL);
filterGL.BranchID = header.BranchID;
filterGL.SubID = header.SubCD;
filterGL.StartPeriodID = header.PeriodFrom;
filterGL.EndPeriodID = header.PeriodTo;
filterGL.AccountID = header.AccountID;
filterGL = graphGL.Filter.Update(filterGL);
Dictionary <DiscrepancyByAccountEnqResultKey, DiscrepancyByAccountEnqResult> dict =
new Dictionary <DiscrepancyByAccountEnqResultKey, DiscrepancyByAccountEnqResult>();
foreach (GLTranR gltran in graphGL.GLTranEnq.Select())
{
DiscrepancyByAccountEnqResult result;
DiscrepancyByAccountEnqResultKey key = new DiscrepancyByAccountEnqResultKey(gltran);
bool isARTran = gltran.Module == BatchModule.AR &&
!string.IsNullOrEmpty(gltran.TranType) &&
!string.IsNullOrEmpty(gltran.RefNbr) &&
gltran.ReferenceID != null;
decimal glTurnover = CalcGLTurnover(gltran);
decimal nonARTrans = isARTran ? 0m : glTurnover;
if (dict.TryGetValue(key, out result))
{
result.GLTurnover += glTurnover;
result.NonXXTrans += nonARTrans;
}
else
{
result = new DiscrepancyByAccountEnqResult();
result.GLTurnover = glTurnover;
result.XXTurnover = 0m;
result.NonXXTrans = nonARTrans;
graphGL.GLTranEnq.Cache.RestoreCopy(result, gltran);
dict.Add(key, result);
}
}
#endregion
#region AR balances
ARCustomerBalanceEnq graphAR = PXGraph.CreateInstance <ARCustomerBalanceEnq>();
ARCustomerBalanceEnq.ARHistoryFilter filterAR = PXCache <ARCustomerBalanceEnq.ARHistoryFilter> .CreateCopy(graphAR.Filter.Current);
filterAR.BranchID = header.BranchID;
filterAR.IncludeChildAccounts = false;
filterAR.ShowWithBalanceOnly = false;
filterAR.SplitByCurrency = false;
foreach (KeyValuePair <DiscrepancyByAccountEnqResultKey, DiscrepancyByAccountEnqResult> pair in dict)
{
DiscrepancyByAccountEnqResultKey key = pair.Key;
DiscrepancyByAccountEnqResult result = pair.Value;
filterAR.Period = pair.Key.FinPeriodID;
filterAR.ARAcctID = pair.Key.AccountID;
filterAR.ARSubID = pair.Key.SubID;
filterAR = graphAR.Filter.Update(filterAR);
foreach (ARCustomerBalanceEnq.ARHistoryResult res in graphAR.History.Select())
{
result.XXTurnover += (res.Balance ?? 0m) - (res.Deposits ?? 0m);
}
}
#endregion
list.AddRange(dict.Values.Where(result =>
header.ShowOnlyWithDiscrepancy != true ||
result.Discrepancy != 0m));
return(list);
}
protected override List <ARGLDiscrepancyByCustomerEnqResult> SelectDetails()
{
var list = new List <ARGLDiscrepancyByCustomerEnqResult>();
ARGLDiscrepancyByCustomerEnqFilter header = Filter.Current;
if (header == null ||
header.BranchID == null ||
header.PeriodFrom == null ||
header.AccountID == null ||
header.SubCD == null)
{
return(list);
}
#region AR balances
ARCustomerBalanceEnq graphAR = PXGraph.CreateInstance <ARCustomerBalanceEnq>();
ARCustomerBalanceEnq.ARHistoryFilter filterAR = PXCache <ARCustomerBalanceEnq.ARHistoryFilter> .CreateCopy(graphAR.Filter.Current);
filterAR.BranchID = header.BranchID;
filterAR.CustomerID = header.CustomerID;
filterAR.Period = header.PeriodFrom;
filterAR.ARAcctID = header.AccountID;
filterAR.SubCD = header.SubCD;
filterAR.IncludeChildAccounts = false;
filterAR.ShowWithBalanceOnly = false;
filterAR.SplitByCurrency = false;
filterAR = graphAR.Filter.Update(filterAR);
Dictionary <int, ARGLDiscrepancyByCustomerEnqResult> dict = new Dictionary <int, ARGLDiscrepancyByCustomerEnqResult>();
foreach (ARCustomerBalanceEnq.ARHistoryResult res in graphAR.History.Select())
{
ARGLDiscrepancyByCustomerEnqResult result;
int key = (int)res.CustomerID;
decimal arTurnover = (res.Balance ?? 0m) - (res.Deposits ?? 0m);
if (dict.TryGetValue(key, out result))
{
result.XXTurnover += arTurnover;
}
else
{
result = new ARGLDiscrepancyByCustomerEnqResult();
result.GLTurnover = 0m;
result.XXTurnover = arTurnover;
PXCache <ARCustomerBalanceEnq.ARHistoryResult> .RestoreCopy(result, res);
dict.Add(key, result);
}
}
#endregion
#region GL balances
AccountByPeriodEnq graphGL = PXGraph.CreateInstance <AccountByPeriodEnq>();
AccountByPeriodFilter filterGL = PXCache <AccountByPeriodFilter> .CreateCopy(graphGL.Filter.Current);
graphGL.Filter.Cache.SetDefaultExt <AccountByPeriodFilter.ledgerID>(filterGL);
filterGL.BranchID = header.BranchID;
filterGL.StartPeriodID = header.PeriodFrom;
filterGL.EndPeriodID = header.PeriodFrom;
filterGL.AccountID = header.AccountID;
filterGL.SubID = header.SubCD;
filterGL = graphGL.Filter.Update(filterGL);
foreach (GLTranR gltran in graphGL.GLTranEnq.Select()
.RowCast <GLTranR>()
.Where(row =>
row.Module == BatchModule.AR &&
row.ReferenceID != null &&
(header.CustomerID == null || row.ReferenceID == header.CustomerID)))
{
ARGLDiscrepancyByCustomerEnqResult result;
int key = (int)gltran.ReferenceID;
decimal glTurnover = CalcGLTurnover(gltran);
if (dict.TryGetValue(key, out result))
{
result.GLTurnover += glTurnover;
}
else
{
Customer customer = PXSelect <Customer,
Where <Customer.bAccountID, Equal <Required <Customer.bAccountID> > > > .SelectSingleBound(this, null, key);
if (customer != null)
{
result = new ARGLDiscrepancyByCustomerEnqResult();
result.CustomerID = customer.BAccountID;
result.AcctCD = customer.AcctCD;
result.AcctName = customer.AcctName;
result.GLTurnover = glTurnover;
result.XXTurnover = 0m;
dict.Add(key, result);
}
}
}
#endregion
list.AddRange(dict.Values.Where(result =>
header.ShowOnlyWithDiscrepancy != true ||
result.Discrepancy != 0m));
return(list);
}
