/// <summary>
/// Retrieves the first financial period of the year corresponding
/// to the financial period specified.
/// If no such period exists, returns <c>null</c>.
/// </summary>
public string GetFirstPeriodOfYear(string financialPeriodID)
{
if (financialPeriodID == null)
{
return(null);
}
PXGraph graph = PXGraph.CreateInstance <FiscalPeriodMaint>();
string firstPeriodOfYear = FinPeriodIDAttribute.GetFirstFinPeriodIDOfYear(financialPeriodID);
if (!FinPeriodIDAttribute.PeriodExists(graph, financialPeriodID))
{
return(null);
}
return(FinPeriodIDFormattingAttribute.FormatForDisplay(firstPeriodOfYear));
}
/// <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)));
}