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); }