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