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