protected virtual IEnumerable AccountByPeriod(PXAdapter adapter) { if (this.EnqResult.Current != null) { AccountHistoryByYearEnq graph = PXGraph.CreateInstance <AccountHistoryByYearEnq>(); AccountByYearFilter filter = PXCache <AccountByYearFilter> .CreateCopy(graph.Filter.Current); filter.OrgBAccountID = Filter.Current.OrgBAccountID; filter.OrganizationID = Filter.Current.OrganizationID; filter.BranchID = EnqResult.Current.BranchID; filter.LedgerID = Filter.Current.LedgerID; filter.AccountID = EnqResult.Current.AccountID; filter.SubCD = Filter.Current.SubCD; filter.FinPeriodID = EnqResult.Current.LastActivityPeriod; OrganizationFinPeriod fp = PXSelect < OrganizationFinPeriod, Where <OrganizationFinPeriod.finPeriodID, Equal <Required <OrganizationFinPeriod.finPeriodID> >, And <OrganizationFinPeriod.organizationID, Equal <Required <OrganizationFinPeriod.organizationID> > > > > .Select(this, filter.FinPeriodID, PXAccess.GetParentOrganizationID(filter.BranchID)); if (fp != null) { filter.FinYear = fp.FinYear; } filter.ShowCuryDetail = this.Filter.Current.ShowCuryDetail; graph.Filter.Update(filter); throw new PXRedirectRequiredException(graph, "Account By Period"); } return(adapter.Get()); }
public virtual IEnumerable nextperiod(PXAdapter adapter) { AccountByYearFilter filter = CurrentFilter; FinYear nextperiod = PXSelect <FinYear, Where <FinYear.year, Greater <Current <AccountByYearFilter.finYear> > >, OrderBy <Asc <FinYear.year> > > .SelectWindowed(this, 0, 1) ?? PXSelectOrderBy <FinYear, OrderBy <Asc <FinYear.year> > > .SelectWindowed(this, 0, 1); filter.FinYear = nextperiod?.Year; return(adapter.Get()); }
protected virtual void AccountByYearFilter_RowInserted(PXCache cache, PXRowInsertedEventArgs e) { AccountByYearFilter filter = e.Row as AccountByYearFilter; if (filter != null) { if (filter.FinPeriodID != null && filter.FinPeriodID != "") { filter.FinYear = FiscalPeriodUtils.FiscalYear(filter.FinPeriodID); //Fill year from finPeriodID } if (filter.FinYear == null || filter.FinYear == "") { DateTime businessDate = this.Accessinfo.BusinessDate.Value; filter.FinYear = businessDate.Year.ToString("0000"); } } }
protected virtual void AccountByYearFilter_RowInserted(PXCache cache, PXRowInsertedEventArgs e) { AccountByYearFilter filter = e.Row as AccountByYearFilter; if (filter != null) { if (!string.IsNullOrEmpty(filter.FinPeriodID)) { filter.FinYear = FinPeriodUtils.FiscalYear(filter.FinPeriodID); //Fill year from finPeriodID } if (string.IsNullOrEmpty(filter.FinYear)) { DateTime businessDate = Accessinfo.BusinessDate.Value; filter.FinYear = businessDate.Year.ToString(FiscalPeriodSetupCreator.CS_YEAR_FORMAT); } } }
public virtual IEnumerable previousperiod(PXAdapter adapter) { AccountByYearFilter filter = this.CurrentFilter; FinYear nextperiod = PXSelect <FinYear, Where <FinYear.year, Less <Current <AccountByYearFilter.finYear> > >, OrderBy <Desc <FinYear.year> > > .SelectWindowed(this, 0, 1); if (nextperiod == null) { nextperiod = PXSelectOrderBy <FinYear, OrderBy <Desc <FinYear.year> > > .SelectWindowed(this, 0, 1); if (nextperiod == null) { yield return(filter); } } filter.FinYear = nextperiod != null ? nextperiod.Year : null; yield return(filter); }
public virtual IEnumerable nextperiod(PXAdapter adapter) { AccountByYearFilter filter = CurrentFilter; int?calendarOrganizationID = FinPeriodRepository.GetCalendarOrganizationID(filter.OrganizationID, filter.BranchID, filter.UseMasterCalendar); FinYear nextperiod = PXSelect < FinYear, Where <FinYear.year, Greater <Required <AccountByYearFilter.finYear> >, And <FinYear.organizationID, Equal <Required <AccountByYearFilter.organizationID> > > >, OrderBy < Asc <FinYear.year> > > .Select(this, filter.FinYear, calendarOrganizationID) ?? PXSelect < FinYear, Where <FinYear.organizationID, Equal <Required <AccountByYearFilter.organizationID> > >, OrderBy < Asc <FinYear.year> > > .Select(this, calendarOrganizationID); filter.FinYear = nextperiod?.Year; return(adapter.Get()); }
protected virtual IEnumerable enqResult() { AccountByYearFilter filter = this.CurrentFilter; 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.AccountID == null || filter.LedgerID == null || filter.FinYear == 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 <FinPeriod, On <GLHistoryByPeriod.finPeriodID, Equal <FinPeriod.finPeriodID> >, InnerJoin <Sub, On <GLHistoryByPeriod.subID, Equal <Sub.subID>, And <Match <Sub, Current <AccessInfo.userName> > > >, LeftJoin <GLHistory, On <GLHistoryByPeriod.accountID, Equal <GLHistory.accountID>, And <GLHistoryByPeriod.subID, Equal <GLHistory.subID>, And <GLHistoryByPeriod.branchID, Equal <GLHistory.branchID>, And <GLHistoryByPeriod.ledgerID, Equal <GLHistory.ledgerID>, 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 <AccountByYearFilter.ledgerID> >, And <FinPeriod.finYear, Equal <Current <AccountByYearFilter.finYear> >, And <GLHistoryByPeriod.accountID, Equal <Current <AccountByYearFilter.accountID> >, 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.finPeriodID > > > > > > > > > > > > > > >(this); if (filter.SubID != null) { cmd.WhereAnd <Where <GLHistoryByPeriod.subID, Equal <Current <AccountByYearFilter.subID> > > >(); } if (filter.BranchID != null) { cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, Equal <Current <AccountByYearFilter.branchID> > > >(); } if (!SubCDUtils.IsSubCDEmpty(filter.SubCD)) { cmd.WhereAnd <Where <Sub.subCD, Like <Current <AccountByYearFilter.subCDWildcard> > > >(); } string yearBegPeriod = filter.BegFinPeriod; GLSetup glSetup = glsetup.Current; bool reverseSign = (glSetup != null) && (glSetup.TrialBalanceSign == GLSetup.trialBalanceSign.Reversed); foreach (PXResult <GLHistoryByPeriod, Account, FinPeriod, Sub, GLHistory, AH> it in cmd.Select(yearBegPeriod)) { GLHistoryByPeriod baseview = (GLHistoryByPeriod)it; Account acct = (Account)it; GLHistory ah = (GLHistory)it; AH ah1 = (AH)it; if (reverseSign && acct.AccountID == glSetup.YtdNetIncAccountID) { continue; } GLHistoryEnquiryResult item = new GLHistoryEnquiryResult(); item.AccountID = baseview.AccountID; item.LedgerID = baseview.LedgerID; item.LastActivityPeriod = baseview.FinPeriodID; item.PtdCreditTotal = ah.FinPtdCredit; item.PtdDebitTotal = ah.FinPtdDebit; item.CuryID = ah1.CuryID; item.Type = acct.Type; item.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(reverseSign); yield return(item); } }
protected virtual IEnumerable enqResult() { AccountByYearFilter filter = CurrentFilter; 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.AccountID == null || filter.LedgerID == null || filter.FinYear == null) { yield break; //Prevent code from accessing database; } using (new PXReadBranchRestrictedScope(null, null, restrictByAccessRights: false, requireAccessForAllSpecified: false)) { PXSelectBase <GLHistoryByPeriod> cmd = new PXSelectJoinGroupBy <GLHistoryByPeriod, LeftJoin <Account, On <GLHistoryByPeriod.accountID, Equal <Account.accountID>, And <Match <Account, Current <AccessInfo.userName> > > >, LeftJoin <MasterFinPeriod, On <GLHistoryByPeriod.finPeriodID, Equal <MasterFinPeriod.finPeriodID> >, LeftJoin <Sub, On <GLHistoryByPeriod.subID, Equal <Sub.subID>, And <Match <Sub, Current <AccessInfo.userName> > > >, LeftJoin <GLHistory, On <GLHistoryByPeriod.accountID, Equal <GLHistory.accountID>, And <GLHistoryByPeriod.subID, Equal <GLHistory.subID>, And <GLHistoryByPeriod.branchID, Equal <GLHistory.branchID>, And <GLHistoryByPeriod.ledgerID, Equal <GLHistory.ledgerID>, 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 <AccountByYearFilter.ledgerID> >, And <MasterFinPeriod.finYear, Equal <Current <AccountByYearFilter.finYear> >, And <GLHistoryByPeriod.accountID, Equal <Current <AccountByYearFilter.accountID> >, 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.ledgerID, GroupBy <GLHistoryByPeriod.accountID, GroupBy <GLHistoryByPeriod.finPeriodID> > > > > > > > > > > > > > > > > > > > > > > > >(this); if (filter.SubID != null) { cmd.WhereAnd <Where <GLHistoryByPeriod.subID, Equal <Current <AccountByYearFilter.subID> > > >(); } int[] branchIDs = null; if (filter.BranchID != null) { cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, Equal <Current <AccountByYearFilter.branchID> > > >(); } else if (filter.OrganizationID != null) { branchIDs = PXAccess.GetChildBranchIDs(filter.OrganizationID, false); cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, In <Required <GLHistoryByPeriod.branchID> >, And <MatchWithBranch <GLHistoryByPeriod.branchID> > > >(); } if (!SubCDUtils.IsSubCDEmpty(filter.SubCD)) { cmd.WhereAnd <Where <Sub.subCD, Like <Current <AccountByYearFilter.subCDWildcard> > > >(); } foreach (PXResult <GLHistoryByPeriod, Account, MasterFinPeriod, Sub, GLHistory, AH> it in cmd.Select(filter.BegFinPeriod, branchIDs)) { GLHistoryByPeriod baseview = it; Account acct = it; GLHistory ah = it; AH ah1 = it; ah.FinFlag = filter.UseMasterCalendar != true; ah1.FinFlag = filter.UseMasterCalendar != true; GLHistoryEnquiryResult item = new GLHistoryEnquiryResult { AccountID = baseview.AccountID, AccountCD = acct.AccountCD, LedgerID = baseview.LedgerID, LastActivityPeriod = baseview.FinPeriodID, PtdCreditTotal = ah.PtdCredit ?? 0m, PtdDebitTotal = ah.PtdDebit ?? 0m, CuryID = ah1.CuryID, Type = acct.Type, EndBalance = ah1.YtdBalance ?? 0m }; if (!string.IsNullOrEmpty(ah1.CuryID)) { item.CuryEndBalance = ah1.CuryYtdBalance ?? 0m; item.CuryPtdCreditTotal = ah.CuryPtdCredit ?? 0m; item.CuryPtdDebitTotal = ah.CuryPtdDebit ?? 0m; } 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); } } }