/// <summary> /// Gets documents which has been reported in the tax period. /// </summary> public IEnumerable <DocumentID> GetReportedDocuments(int?branchID, int?taxAgency, string taxPeriodID) { var childBranchIds = PXAccess.GetChildBranchIDs(GetBranchByID(branchID).BranchCD); return(PXSelectGroupBy <TaxTran, Where <TaxTran.branchID, In <Required <TaxTran.branchID> >, And <TaxTran.vendorID, Equal <Required <TaxTran.vendorID> >, And <TaxTran.taxPeriodID, Equal <Required <TaxTran.taxPeriodID> >, And <TaxTran.voided, Equal <False>, And <TaxTran.released, Equal <True> > > > > >, Aggregate <GroupBy <TaxTran.refNbr, GroupBy <TaxTran.tranType> > > > .Select(_graph, childBranchIds, taxAgency, taxPeriodID) .Select(row => { var taxTran = (TaxTran)row; return new DocumentID() { DocType = taxTran.TranType, RefNbr = taxTran.RefNbr, Module = taxTran.Module }; })); }
public virtual IEnumerable records() { ScheduleTransFilter filter = this.Filter.Current; if (filter != null) { PXSelectBase <DRScheduleTran> select = new PXSelectJoin <DRScheduleTran, InnerJoin <DRScheduleDetail, On <DRScheduleTran.scheduleID, Equal <DRScheduleDetail.scheduleID>, And <DRScheduleTran.componentID, Equal <DRScheduleDetail.componentID> > >, InnerJoin <DRDeferredCode, On <DRDeferredCode.deferredCodeID, Equal <DRScheduleDetail.defCode> >, LeftJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <DRScheduleDetail.componentID> > > > >, Where <DRDeferredCode.accountType, Equal <Current <ScheduleTransFilter.accountType> >, And <DRScheduleTran.status, Equal <DRScheduleTranStatus.PostedStatus>, And <DRScheduleTran.finPeriodID, Equal <Current <ScheduleTransFilter.finPeriodID> > > > > >(this); if (!string.IsNullOrEmpty(filter.DeferredCode)) { select.WhereAnd <Where <DRScheduleDetail.defCode, Equal <Current <ScheduleTransFilter.deferredCode> > > >(); } int[] branchIDs = null; if (filter.BranchID != null) { select.WhereAnd <Where <DRScheduleTran.branchID, Equal <Current <ScheduleTransFilter.branchID> > > >(); } else if (filter.OrganizationID != null) { branchIDs = PXAccess.GetChildBranchIDs(filter.OrganizationID); select.WhereAnd <Where <DRScheduleTran.branchID, In <Required <ScheduleTransFilter.branchID> > > >(); } if (filter.AccountID != null) { select.WhereAnd <Where <DRScheduleTran.accountID, Equal <Current <ScheduleTransFilter.accountID> > > >(); } if (filter.SubID != null) { select.WhereAnd <Where <DRScheduleTran.subID, Equal <Current <ScheduleTransFilter.subID> > > >(); } if (filter.BAccountID != null) { select.WhereAnd <Where <DRScheduleDetail.bAccountID, Equal <Current <ScheduleTransFilter.bAccountID> > > >(); } foreach (object x in select.Select(branchIDs)) { yield return(x); } } else { yield break; } }
public IEnumerable <GLTran> GetPostedGLTrans(int?branchID, int?ledgerID, int?accountID, string finPeriodID) { var childBranchIds = PXAccess.GetChildBranchIDs(GetBranchByID(branchID).BranchCD); return(PXSelect <GLTran, Where <GLTran.branchID, In <Required <GLTran.branchID> >, And <GLTran.ledgerID, Equal <Required <GLTran.ledgerID> >, And <GLTran.accountID, Equal <Required <GLTran.accountID> >, And <GLTran.finPeriodID, Equal <Required <GLTran.finPeriodID> >, And <GLTran.posted, Equal <True> > > > > > > .Select(_graph, childBranchIds, ledgerID, accountID, finPeriodID) .RowCast <GLTran>()); }
public bool CheckDoNotExistUnreleasedOrUnpostedGLTransWithBranchWithFinPeriodLessOrEqual(int?branchID, string finPeriod) { var childBranchIds = PXAccess.GetChildBranchIDs(GetBranchByID(branchID).BranchCD); var query = new PXSelect <GLTran, Where <Where2 <Where <GLTran.released, Equal <False>, Or <GLTran.posted, Equal <False> > >, And <GLTran.finPeriodID, LessEqual <Required <Batch.finPeriodID> >, And <GLTran.branchID, In <Required <GLTran.branchID> > > > > > >(_graph); var tran = query.SelectSingle(finPeriod, childBranchIds); return(tran == null); }
public bool CheckDoNotExistUnreleasedCAAdjsWithCASplitsWithBranchWithFinPeriodLessOrEqual(int?branchID, string finPeriod) { var childBranchIds = PXAccess.GetChildBranchIDs(GetBranchByID(branchID).BranchCD); var query = new PXSelectJoin <CAAdj, LeftJoin <CASplit, On <CAAdj.adjTranType, Equal <CASplit.adjTranType>, And <CAAdj.adjRefNbr, Equal <CASplit.adjRefNbr> > > >, Where <CAAdj.released, Equal <False>, And <CAAdj.finPeriodID, LessEqual <Required <CAAdj.finPeriodID> >, And <Where <CAAdj.branchID, In <Required <CAAdj.branchID> >, Or <CASplit.branchID, In <Required <CASplit.branchID> > > > > > > >(_graph); var caAdj = query.SelectSingle(finPeriod, childBranchIds, childBranchIds); return(caAdj == null); }
public bool CheckDoNotExistUnreleasedARRegistersWithARTransWithBranchWithFinPeriodLessOrEqual(int?branchID, string finPeriod) { var childBranchIds = PXAccess.GetChildBranchIDs(GetBranchByID(branchID).BranchCD); var query = new PXSelectJoin <ARRegister, LeftJoin <ARTran, On <ARRegister.docType, Equal <ARTran.tranType>, And <ARRegister.refNbr, Equal <ARTran.refNbr> > > >, Where <ARRegister.released, Equal <False>, And <ARRegister.finPeriodID, LessEqual <Required <ARRegister.finPeriodID> >, And <Where <ARRegister.branchID, In <Required <ARRegister.branchID> >, Or <ARTran.branchID, In <Required <ARTran.branchID> > > > > > > >(_graph); var arRegister = query.SelectSingle(finPeriod, childBranchIds, childBranchIds); return(arRegister == null); }
public IEnumerable <PXResult <GLTran, CurrencyInfo> > GetTaxableGLTransWithCuryInfoGroupedByDocumentAttrAndTaxCategory(int?branchID, string[] batchNbrs) { var childBranchIds = PXAccess.GetChildBranchIDs(GetBranchByID(branchID).BranchCD); return(PXSelectJoinGroupBy <GLTran, InnerJoin <CurrencyInfo, On <GLTran.curyInfoID, Equal <CurrencyInfo.curyInfoID> > >, Where <GLTran.branchID, In <Required <GLTran.branchID> >, And <GLTran.batchNbr, In <Required <GLTran.batchNbr> >, And <GLTran.taxCategoryID, IsNotNull, And <GLTran.module, Equal <BatchModule.moduleGL> > > > >, Aggregate <GroupBy <GLTran.branchID, GroupBy <GLTran.batchNbr, GroupBy <GLTran.refNbr, GroupBy <GLTran.taxCategoryID> > > > > > .Select(_graph, childBranchIds, batchNbrs) .Cast <PXResult <GLTran, CurrencyInfo> >()); }
/// <summary> /// Query to get data for calculation of the begin balances of accounts. /// </summary> /// <returns>Account; /// GLHistory: total of ptdDebit and ptdCredit of specified period; /// AH: total of finYtdBalance for last period activity period.</returns> public IEnumerable <PXResult <Account, GLHistory, AH> > GetAccountsWithDataToCalcBeginBalancesExcludingYTDNetIncAcc(int?branchID, int?ledgerID, string finPeriodID) { var glSetup = GetGLSetup(); var childBranchIds = PXAccess.GetChildBranchIDs(GetBranchByID(branchID).BranchCD); return(PXSelectJoinGroupBy <Account, LeftJoin <GLHistoryByPeriod, On <GLHistoryByPeriod.branchID, In <Required <GLHistoryByPeriod.branchID> >, And <GLHistoryByPeriod.ledgerID, Equal <Required <GLHistoryByPeriod.ledgerID> >, And <Account.accountID, Equal <GLHistoryByPeriod.accountID>, And <GLHistoryByPeriod.finPeriodID, Equal <Required <GLHistoryByPeriod.finPeriodID> >, And <Where2 <Where <Account.type, Equal <AccountType.asset>, Or <Account.type, Equal <AccountType.liability> > >, Or <Where <GLHistoryByPeriod.lastActivityPeriod, GreaterEqual <Required <GLHistoryByPeriod.lastActivityPeriod> >, // greater than the first period of a year And <Where <Account.type, Equal <AccountType.expense>, Or <Account.type, Equal <AccountType.income> > > > > > > > > > > >, //to get saldo in selected period LeftJoin <GLHistory, On <GLHistoryByPeriod.branchID, Equal <GLHistory.branchID>, And <GLHistoryByPeriod.ledgerID, Equal <GLHistory.ledgerID>, And <GLHistoryByPeriod.accountID, Equal <GLHistory.accountID>, And <GLHistoryByPeriod.subID, Equal <GLHistory.subID>, And <GLHistoryByPeriod.finPeriodID, Equal <GLHistory.finPeriodID> > > > > >, //to get ending balance for last activity period LeftJoin <AH, On <GLHistoryByPeriod.ledgerID, Equal <AH.ledgerID>, And <GLHistoryByPeriod.branchID, Equal <AH.branchID>, And <GLHistoryByPeriod.accountID, Equal <AH.accountID>, And <GLHistoryByPeriod.subID, Equal <AH.subID>, And <GLHistoryByPeriod.lastActivityPeriod, Equal <AH.finPeriodID> > > > > > > > >, Where <Account.accountID, NotEqual <Required <Account.accountID> > >, // not YTD Net Income Account>, Aggregate < Sum <AH.finYtdBalance, Sum <GLHistory.finPtdCredit, Sum <GLHistory.finPtdDebit, GroupBy <Account.accountID> > > > > > .Select(_graph, childBranchIds, ledgerID, finPeriodID, FinPeriodIDAttribute.GetFirstFinPeriodIDOfYear(finPeriodID), glSetup.YtdNetIncAccountID) .Cast <PXResult <Account, GLHistoryByPeriod, GLHistory, AH> >() .Select(row => new PXResult <Account, GLHistory, AH>((Account)row, (GLHistory)row, (AH)row))); }
protected virtual void AP1099Year_RowSelected(PXCache sender, PXRowSelectedEventArgs e) { AP1099Year year1099 = (AP1099Year)e.Row; if (year1099 == null) { return; } close1099Year.SetEnabled(!string.IsNullOrEmpty(year1099.FinYear) && year1099.Status == AP1099Year.status.Open); int[] childBranches = PXAccess.GetChildBranchIDs(Year_Header.Current.OrganizationID); if (childBranches.Length == 0) { return; } bool hasUnappliedPrepayments = PXSelectJoin <APRegister, InnerJoin <Vendor, On <APRegister.vendorID, Equal <Vendor.bAccountID> >, InnerJoin <OrganizationFinPeriod, On <APRegister.finPeriodID, Equal <OrganizationFinPeriod.finPeriodID> > > >, Where <Vendor.vendor1099, Equal <True>, And <APRegister.docType, Equal <APDocType.prepayment>, And <APRegister.status, NotEqual <APDocStatus.closed>, And <OrganizationFinPeriod.finYear, Equal <Current <AP1099Year.finYear> >, And <OrganizationFinPeriod.organizationID, Equal <Required <OrganizationFinPeriod.organizationID> >, And <APRegister.branchID, In <Required <APRegister.branchID> > > > > > > > > .SelectWindowed(this, 0, 1, Year_Header.Current.OrganizationID, childBranches) .AsEnumerable() .Any(); PXSetPropertyException finYearExeption = hasUnappliedPrepayments ? new PXSetPropertyException(Messages.ExistsUnappliedPayments, PXErrorLevel.Warning, year1099.FinYear) : null; sender.RaiseExceptionHandling <AP1099Year.finYear>( year1099, year1099.FinYear, finYearExeption); }
public override object Evaluate(PXCache cache, object item, Dictionary <Type, object> parameters) { int?organizationID = (int?)parameters[typeof(OrganizationID)]; return(PXAccess.GetChildBranchIDs(organizationID).Length < 2); }
private List <int> GetSameOrganizationBranches(int?branchID) { int?organizationID = PXAccess.GetParentOrganizationID(branchID); return(PXAccess.GetChildBranchIDs(organizationID, false).ToList()); }
protected virtual IEnumerable enqResult() { GLHistoryEnqFilter filter = CurrentFilter; bool showCurrency = filter.ShowCuryDetail == true; PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyID>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyPtdCreditTotal>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyPtdDebitTotal>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyBegBalance>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyEndBalance>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.signCuryBegBalance>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.signCuryEndBalance>(EnqResult.Cache, null, showCurrency); if (filter.AccountID == null || filter.LedgerID == null || filter.FinPeriodID == null) { yield break; //Prevent code from accessing database; } PXSelectBase <GLHistoryByPeriod> cmd = new PXSelectJoinGroupBy <GLHistoryByPeriod, InnerJoin <Account, On <GLHistoryByPeriod.accountID, Equal <Account.accountID>, And <Match <Account, Current <AccessInfo.userName> > > >, InnerJoin <Sub, On <GLHistoryByPeriod.subID, Equal <Sub.subID>, And <Match <Sub, Current <AccessInfo.userName> > > >, LeftJoin <GLHistory, On <GLHistoryByPeriod.accountID, Equal <GLHistory.accountID>, And <GLHistoryByPeriod.ledgerID, Equal <GLHistory.ledgerID>, And <GLHistoryByPeriod.branchID, Equal <GLHistory.branchID>, And <GLHistoryByPeriod.subID, Equal <GLHistory.subID>, And <GLHistoryByPeriod.finPeriodID, Equal <GLHistory.finPeriodID> > > > > >, LeftJoin <AH, On <GLHistoryByPeriod.ledgerID, Equal <AH.ledgerID>, And <GLHistoryByPeriod.branchID, Equal <AH.branchID>, And <GLHistoryByPeriod.accountID, Equal <AH.accountID>, And <GLHistoryByPeriod.subID, Equal <AH.subID>, And <GLHistoryByPeriod.lastActivityPeriod, Equal <AH.finPeriodID> > > > > > > > > >, Where <GLHistoryByPeriod.ledgerID, Equal <Current <GLHistoryEnqFilter.ledgerID> >, And <GLHistoryByPeriod.accountID, Equal <Current <GLHistoryEnqFilter.accountID> >, And <GLHistoryByPeriod.finPeriodID, Equal <Current <GLHistoryEnqFilter.finPeriodID> >, And < Where2 < Where <Account.accountID, NotEqual <Current <GLSetup.ytdNetIncAccountID> >, And <Where <Account.type, Equal <AccountType.asset>, Or <Account.type, Equal <AccountType.liability> > > > >, Or <Where <GLHistoryByPeriod.lastActivityPeriod, GreaterEqual <Required <GLHistoryByPeriod.lastActivityPeriod> >, And <Where <Account.type, Equal <AccountType.expense>, Or <Account.type, Equal <AccountType.income>, Or <Account.accountID, Equal <Current <GLSetup.ytdNetIncAccountID> > > > > > > > > > > > >, Aggregate < Sum <AH.finYtdBalance, Sum <AH.curyFinYtdBalance, Sum <GLHistory.finPtdDebit, Sum <GLHistory.finPtdCredit, Sum <GLHistory.finBegBalance, Sum <GLHistory.finYtdBalance, Sum <GLHistory.curyFinBegBalance, Sum <GLHistory.curyFinYtdBalance, Sum <GLHistory.curyFinPtdCredit, Sum <GLHistory.curyFinPtdDebit, GroupBy <GLHistoryByPeriod.ledgerID, GroupBy <GLHistoryByPeriod.accountID, GroupBy <GLHistoryByPeriod.subID> > > > > > > > > > > > > > >(this); int[] branchIDs = null; if (filter.BranchID != null) { cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, Equal <Current <GLHistoryEnqFilter.branchID> > > >(); } else if (filter.OrganizationID != null) { branchIDs = PXAccess.GetChildBranchIDs(filter.OrganizationID); cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, In <Required <GLHistoryEnqFilter.branchID> > > >(); } if (!SubCDUtils.IsSubCDEmpty(filter.SubCD)) { cmd.WhereAnd <Where <Sub.subCD, Like <Current <GLHistoryEnqFilter.subCDWildcard> > > >(); } foreach (PXResult <GLHistoryByPeriod, Account, Sub, GLHistory, AH> it in cmd.Select(filter.BegFinPeriod, branchIDs)) { GLHistoryByPeriod baseview = it; Account acct = it; GLHistory ah = it; AH ah1 = it; GLHistoryEnquiryResult item = new GLHistoryEnquiryResult { AccountID = baseview.AccountID, AccountCD = acct.AccountCD, LedgerID = baseview.LedgerID, Type = acct.Type, Description = acct.Description, LastActivityPeriod = baseview.LastActivityPeriod, PtdCreditTotal = ah.FinPtdCredit, PtdDebitTotal = ah.FinPtdDebit, CuryID = ah1.CuryID, SubCD = ((Sub)it).SubCD, EndBalance = ah1.FinYtdBalance }; if (!string.IsNullOrEmpty(ah1.CuryID)) { item.CuryEndBalance = ah1.CuryFinYtdBalance; item.CuryPtdCreditTotal = ah.CuryFinPtdCredit; item.CuryPtdDebitTotal = ah.CuryFinPtdDebit; } else { item.CuryEndBalance = null; item.CuryPtdCreditTotal = null; item.CuryPtdDebitTotal = null; } item.recalculate(true); // End balance is considered as correct digit - so we need to calculate begBalance base on ending one item.recalculateSignAmount(glsetup.Current?.TrialBalanceSign == GLSetup.trialBalanceSign.Reversed); yield return(item); } }
public virtual IEnumerable amts(PXAdapter adapter) { AccBalanceByAssetFilter filter = Filter.Current; if (filter == null) { yield break; } PXSelectBase <FATran> select = new PXSelectJoin <FATran, InnerJoin <FixedAsset, On <FixedAsset.assetID, Equal <FATran.assetID>, And <FixedAsset.recordType, Equal <FARecordType.assetType> > >, InnerJoin <FADetails, On <FADetails.assetID, Equal <FixedAsset.assetID> >, InnerJoin <FALocationHistoryCurrent, On <FALocationHistoryCurrent.assetID, Equal <FixedAsset.assetID> >, InnerJoin <FALocationHistory, On <FALocationHistory.assetID, Equal <FixedAsset.assetID>, And <FALocationHistory.periodID, Equal <FALocationHistoryCurrent.lastPeriodID>, And <FALocationHistory.revisionID, Equal <FALocationHistoryCurrent.lastRevisionID> > > > > > > >, Where <FATran.released, Equal <True>, And <FATran.finPeriodID, LessEqual <Current <AccBalanceByAssetFilter.periodID> >, And <FATran.bookID, Equal <Current <AccBalanceByAssetFilter.bookID> >, And2 <Where <FALocationHistory.fAAccountID, Equal <Current <AccBalanceByAssetFilter.accountID> >, And <FALocationHistory.fASubID, Equal <Current <AccBalanceByAssetFilter.subID> >, Or <FALocationHistory.accumulatedDepreciationAccountID, Equal <Current <AccBalanceByAssetFilter.accountID> >, And <FALocationHistory.accumulatedDepreciationSubID, Equal <Current <AccBalanceByAssetFilter.subID> > > > > >, And <Where <FATran.debitAccountID, Equal <Current <AccBalanceByAssetFilter.accountID> >, And <FATran.debitSubID, Equal <Current <AccBalanceByAssetFilter.subID> >, Or <FATran.creditAccountID, Equal <Current <AccBalanceByAssetFilter.accountID> >, And <FATran.creditSubID, Equal <Current <AccBalanceByAssetFilter.subID> > > > > > > > > > > >(this); int[] branchIDs = null; if (filter.BranchID != null) { select.WhereAnd <Where <FALocationHistory.locationID, Equal <Current <AccBalanceByAssetFilter.branchID> > > >(); } else if (filter.OrganizationID != null) { branchIDs = PXAccess.GetChildBranchIDs(filter.OrganizationID); select.WhereAnd <Where <FALocationHistory.locationID, In <Required <FALocationHistory.locationID> > > >(); } Dictionary <int?, Amounts> dict = new Dictionary <int?, Amounts>(); foreach (PXResult <FATran, FixedAsset, FADetails, FALocationHistoryCurrent, FALocationHistory> res in select.Select(branchIDs)) { FATran tran = (FATran)res; FixedAsset asset = (FixedAsset)res; FADetails details = (FADetails)res; FALocationHistory location = (FALocationHistory)res; Amounts record = null; if (!dict.TryGetValue(asset.AssetID, out record)) { record = new Amounts { AssetID = asset.AssetID, Description = asset.Description, Status = details.Status, ClassID = asset.ClassID, DepreciateFromDate = details.DepreciateFromDate, BranchID = location.BranchID, Department = location.Department, ItdAmt = decimal.Zero, YtdAmt = decimal.Zero, PtdAmt = decimal.Zero }; } decimal tranAmt = tran.TranAmt ?? decimal.Zero; decimal amount = tran.DebitAccountID == tran.CreditAccountID && tran.DebitSubID == tran.CreditSubID ? decimal.Zero : tran.DebitAccountID == filter.AccountID && tran.DebitSubID == filter.SubID ? tranAmt : -tranAmt; record.ItdAmt += amount; record.YtdAmt += (FinPeriodIDAttribute.FinPeriodEqual(filter.PeriodID, tran.FinPeriodID, FinPeriodIDAttribute.FinPeriodComparison.Year) ? amount : decimal.Zero); record.PtdAmt += (filter.PeriodID == tran.FinPeriodID ? amount : decimal.Zero); dict[asset.AssetID] = record; } foreach (Amounts amt in dict.Values) { if (amt.ItdAmt != decimal.Zero || amt.YtdAmt != decimal.Zero || amt.PtdAmt != decimal.Zero) { yield return(amt); } } }
protected virtual IEnumerable enqResult() { GLHistoryEnqFilter filter = (GLHistoryEnqFilter)this.Filter.Current; bool showCurrency = filter.ShowCuryDetail.HasValue && filter.ShowCuryDetail.Value; PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyID>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyPtdCreditTotal>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyPtdDebitTotal>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyBegBalance>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyEndBalance>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.signCuryBegBalance>(EnqResult.Cache, null, showCurrency); PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.signCuryEndBalance>(EnqResult.Cache, null, showCurrency); if (filter.LedgerID == null || filter.FinPeriodID == null) { yield break; //Prevent code from accessing database; } #region cmd PXSelectBase <GLHistoryByPeriod> cmd = new PXSelectJoinGroupBy <GLHistoryByPeriod, InnerJoin <Account, On <GLHistoryByPeriod.accountID, Equal <Account.accountID>, And <Match <Account, Current <AccessInfo.userName> > > >, InnerJoin <Sub, On <GLHistoryByPeriod.subID, Equal <Sub.subID>, And <Match <Sub, Current <AccessInfo.userName> > > >, LeftJoin <GLHistory, On <GLHistoryByPeriod.accountID, Equal <GLHistory.accountID>, And <GLHistoryByPeriod.ledgerID, Equal <GLHistory.ledgerID>, And <GLHistoryByPeriod.branchID, Equal <GLHistory.branchID>, And <GLHistoryByPeriod.subID, Equal <GLHistory.subID>, And <GLHistoryByPeriod.finPeriodID, Equal <GLHistory.finPeriodID> > > > > >, LeftJoin <AH, On <GLHistoryByPeriod.ledgerID, Equal <AH.ledgerID>, And <GLHistoryByPeriod.branchID, Equal <AH.branchID>, And <GLHistoryByPeriod.accountID, Equal <AH.accountID>, And <GLHistoryByPeriod.subID, Equal <AH.subID>, And <GLHistoryByPeriod.lastActivityPeriod, Equal <AH.finPeriodID> > > > > > > > > >, Where <GLHistoryByPeriod.ledgerID, Equal <Current <GLHistoryEnqFilter.ledgerID> >, And <GLHistoryByPeriod.finPeriodID, Equal <Current <GLHistoryEnqFilter.finPeriodID> >, And < Where2 < Where <Account.accountID, NotEqual <Current <GLSetup.ytdNetIncAccountID> >, And <Where <Account.type, Equal <AccountType.asset>, Or <Account.type, Equal <AccountType.liability> > > > >, Or <Where <GLHistoryByPeriod.lastActivityPeriod, GreaterEqual <Required <GLHistoryByPeriod.lastActivityPeriod> >, And <Where <Account.type, Equal <AccountType.expense>, Or <Account.type, Equal <AccountType.income>, Or <Account.accountID, Equal <Current <GLSetup.ytdNetIncAccountID> > > > > > > > > > > >, Aggregate < Sum <AH.finYtdBalance, Sum <AH.tranYtdBalance, Sum <AH.curyFinYtdBalance, Sum <AH.curyTranYtdBalance, Sum <GLHistory.finPtdDebit, Sum <GLHistory.tranPtdDebit, Sum <GLHistory.finPtdCredit, Sum <GLHistory.tranPtdCredit, Sum <GLHistory.finBegBalance, Sum <GLHistory.tranBegBalance, Sum <GLHistory.finYtdBalance, Sum <GLHistory.tranYtdBalance, Sum <GLHistory.curyFinBegBalance, Sum <GLHistory.curyTranBegBalance, Sum <GLHistory.curyFinYtdBalance, Sum <GLHistory.curyTranYtdBalance, Sum <GLHistory.curyFinPtdCredit, Sum <GLHistory.curyTranPtdCredit, Sum <GLHistory.curyFinPtdDebit, Sum <GLHistory.curyTranPtdDebit, GroupBy <GLHistoryByPeriod.branchID, GroupBy <GLHistoryByPeriod.ledgerID, GroupBy <GLHistoryByPeriod.accountID, GroupBy <GLHistoryByPeriod.finPeriodID > > > > > > > > > > > > > > > > > > > > > > > > > >(this); if (filter.LedgerID != null) { Ledger ledger = (Ledger)PXSelectorAttribute.Select <GLHistoryEnqFilter.ledgerID>(Filter.Cache, filter); if (ledger?.BalanceType == LedgerBalanceType.Budget) { // we shouldn't select history from the previous years for the budget ledgers cmd.WhereAnd <Where <Substring <GLHistoryByPeriod.finPeriodID, int1, int4>, Equal <Substring <GLHistoryByPeriod.lastActivityPeriod, int1, int4> > > >(); } } if (filter.AccountID != null) { cmd.WhereAnd <Where <GLHistoryByPeriod.accountID, Equal <Current <GLHistoryEnqFilter.accountID> > > >(); } if (filter.AccountClassID != null) { cmd.WhereAnd <Where <Account.accountClassID, Equal <Current <GLHistoryEnqFilter.accountClassID> > > >(); } if (filter.SubID != null) { cmd.WhereAnd <Where <GLHistoryByPeriod.subID, Equal <Current <GLHistoryEnqFilter.subID> > > >(); } int[] branchIDs = null; if (filter.BranchID != null) { cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, Equal <Current <GLHistoryEnqFilter.branchID> > > >(); } else if (filter.OrganizationID != null) { branchIDs = PXAccess.GetChildBranchIDs(filter.OrganizationID, false); cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, In <Required <GLHistoryEnqFilter.branchID> >, And <MatchWithBranch <GLHistoryByPeriod.branchID> > > >(); } if (!SubCDUtils.IsSubCDEmpty(filter.SubCD)) { cmd.WhereAnd <Where <Sub.subCD, Like <Current <GLHistoryEnqFilter.subCDWildcard> > > >(); } //cmd.WhereAnd<Where<Match<Current<AccessInfo.userName>>>>(); #endregion string yearBegPeriod = filter.BegFinPeriod; GLSetup glSetup = glsetup.Current; bool reverseSign = (glSetup != null) && (glSetup.TrialBalanceSign == GLSetup.trialBalanceSign.Reversed); foreach (PXResult <GLHistoryByPeriod, Account, Sub, GLHistory, AH> it in cmd.Select(yearBegPeriod, branchIDs)) { GLHistoryByPeriod baseview = (GLHistoryByPeriod)it; Account acct = (Account)it; GLHistory ah = (GLHistory)it; AH ah1 = (AH)it; ah.FinFlag = filter.UseMasterCalendar != true; ah1.FinFlag = filter.UseMasterCalendar != true; if (reverseSign && acct.AccountID == glSetup.YtdNetIncAccountID) { continue; } GLHistoryEnquiryResult item = new GLHistoryEnquiryResult(); item.BranchID = baseview.BranchID; item.LedgerID = baseview.LedgerID; item.AccountID = baseview.AccountID; item.AccountCD = acct.AccountCD; item.Type = acct.Type; item.Description = acct.Description; item.LastActivityPeriod = baseview.LastActivityPeriod; item.PtdCreditTotal = ah.PtdCredit ?? 0m; item.PtdDebitTotal = ah.PtdDebit ?? 0m; item.EndBalance = ah1.YtdBalance ?? 0m; item.ConsolAccountCD = acct.GLConsolAccountCD; item.AccountClassID = acct.AccountClassID; if (!(string.IsNullOrEmpty(ah.CuryID) && string.IsNullOrEmpty(ah1.CuryID))) { item.CuryEndBalance = ah1.CuryYtdBalance ?? 0m; item.CuryPtdCreditTotal = ah.CuryPtdCredit ?? 0m; item.CuryPtdDebitTotal = ah.CuryPtdDebit ?? 0m; item.CuryID = string.IsNullOrEmpty(ah.CuryID) ? ah1.CuryID : ah.CuryID; } else { item.CuryEndBalance = null; item.CuryPtdCreditTotal = null; item.CuryPtdDebitTotal = null; item.CuryID = null; } item.recalculate(true); // End balance is considered as correct digit - so we need to calculate begBalance base on ending one item.recalculateSignAmount(reverseSign); yield return(item); } }