protected override List <APGLDiscrepancyByVendorEnqResult> SelectDetails() { var list = new List <APGLDiscrepancyByVendorEnqResult>(); APGLDiscrepancyByVendorEnqFilter header = Filter.Current; if (header == null || header.BranchID == null || header.PeriodFrom == null || header.AccountID == null || header.SubCD == null) { return(list); } #region AP balances APVendorBalanceEnq graphAP = PXGraph.CreateInstance <APVendorBalanceEnq>(); APVendorBalanceEnq.APHistoryFilter filterAP = PXCache <APVendorBalanceEnq.APHistoryFilter> .CreateCopy(graphAP.Filter.Current); filterAP.BranchID = header.BranchID; filterAP.VendorID = header.VendorID; filterAP.FinPeriodID = header.PeriodFrom; filterAP.AccountID = header.AccountID; filterAP.SubID = header.SubCD; filterAP.ByFinancialPeriod = true; filterAP.ShowWithBalanceOnly = false; filterAP.SplitByCurrency = false; filterAP = graphAP.Filter.Update(filterAP); Dictionary <int, APGLDiscrepancyByVendorEnqResult> dict = new Dictionary <int, APGLDiscrepancyByVendorEnqResult>(); foreach (APVendorBalanceEnq.APHistoryResult res in graphAP.History.Select()) { APGLDiscrepancyByVendorEnqResult result; int key = (int)res.VendorID; decimal apTurnover = (res.Balance ?? 0m) - (res.Deposits ?? 0m); if (dict.TryGetValue(key, out result)) { result.XXTurnover += apTurnover; } else { result = new APGLDiscrepancyByVendorEnqResult(); result.GLTurnover = 0m; result.XXTurnover = apTurnover; PXCache <APVendorBalanceEnq.APHistoryResult> .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.AP && row.ReferenceID != null && (header.VendorID == null || row.ReferenceID == header.VendorID))) { APGLDiscrepancyByVendorEnqResult result; int key = (int)gltran.ReferenceID; decimal glTurnover = CalcGLTurnover(gltran); if (dict.TryGetValue(key, out result)) { result.GLTurnover += glTurnover; } else { Vendor vendor = PXSelect <Vendor, Where <Vendor.bAccountID, Equal <Required <Vendor.bAccountID> > > > .SelectSingleBound(this, null, key); if (vendor != null) { result = new APGLDiscrepancyByVendorEnqResult(); result.VendorID = vendor.BAccountID; result.AcctCD = vendor.AcctCD; result.AcctName = vendor.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); }
protected override List <DiscrepancyByAccountEnqResult> SelectDetails() { var list = new List <DiscrepancyByAccountEnqResult>(); APGLDiscrepancyEnqFilter 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 isAPTran = gltran.Module == BatchModule.AP && !string.IsNullOrEmpty(gltran.TranType) && !string.IsNullOrEmpty(gltran.RefNbr) && gltran.ReferenceID != null; decimal glTurnover = CalcGLTurnover(gltran); decimal nonAPTrans = isAPTran ? 0m : glTurnover; if (dict.TryGetValue(key, out result)) { result.GLTurnover += glTurnover; result.NonXXTrans += nonAPTrans; } else { result = new DiscrepancyByAccountEnqResult(); result.GLTurnover = glTurnover; result.XXTurnover = 0m; result.NonXXTrans = nonAPTrans; graphGL.GLTranEnq.Cache.RestoreCopy(result, gltran); dict.Add(key, result); } } #endregion #region AP balances APVendorBalanceEnq graphAP = PXGraph.CreateInstance <APVendorBalanceEnq>(); APVendorBalanceEnq.APHistoryFilter filterAP = PXCache <APVendorBalanceEnq.APHistoryFilter> .CreateCopy(graphAP.Filter.Current); filterAP.BranchID = header.BranchID; filterAP.ByFinancialPeriod = true; filterAP.ShowWithBalanceOnly = false; filterAP.SplitByCurrency = false; foreach (KeyValuePair <DiscrepancyByAccountEnqResultKey, DiscrepancyByAccountEnqResult> pair in dict) { DiscrepancyByAccountEnqResultKey key = pair.Key; DiscrepancyByAccountEnqResult result = pair.Value; filterAP.FinPeriodID = pair.Key.FinPeriodID; filterAP.AccountID = pair.Key.AccountID; filterAP.SubID = GetSubCD(pair.Key.SubID); filterAP = graphAP.Filter.Update(filterAP); foreach (APVendorBalanceEnq.APHistoryResult res in graphAP.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); }