Hello Everyone,
In the Balances info on the Projects screen, by default the data is grouped by AccountGroup. I need to group by the records using the ProjectTaskId, so I have created a custom field for ProjectTaskID on the PMProjectBalanceRecord DAC.
For these changes, I used the base ViewDelegate, to which I have added the PMTask Table to the BQL and grouped by the ProjectTaskID; however, during debugging, getting the records as needed but, on the screen, I am getting the duplicate records.
Below is my Duplicate Records Screenshot and the code behind for the BQL. Please help if there is anything wrong in the approach.
public class ProjectEntry_Extension : PXGraphExtension<PX.Objects.PM.ProjectEntry>
{
[PXCopyPasteHiddenView]
[PXVirtualDAC]
public PXSelect<PMProjectBalanceRecord, Where<PMProjectBalanceRecord.recordID, IsNotNull>,
OrderBy<Asc<PMProjectBalanceRecord.sortOrder>>> BalanceRecords;
private readonly bool IsCopyPaste;
public IEnumerable balanceRecords()
{
List<PMProjectBalanceRecord> asset = new List<PMProjectBalanceRecord>();
List<PMProjectBalanceRecord> liability = new List<PMProjectBalanceRecord>();
List<PMProjectBalanceRecord> income = new List<PMProjectBalanceRecord>();
List<PMProjectBalanceRecord> expense = new List<PMProjectBalanceRecord>();
List<PMProjectBalanceRecord> offbalance = new List<PMProjectBalanceRecord>();
PXSelectBase<PMBudget> select = new PXSelectJoinGroupBy<PMBudget,
InnerJoin<PMAccountGroup,
On<PMAccountGroup.groupID, Equal<PMBudget.accountGroupID>>,
InnerJoin<PMTask,
On<PMTask.projectID, Equal<PMBudget.projectID>,
And<PMTask.taskID, Equal<PMBudget.projectTaskID>>>>>,
Where<PMBudget.projectID, Equal<Current<PMProject.contractID>>>,
Aggregate<GroupBy<PMBudget.projectTaskID,
GroupBy<PMAccountGroup.type,
GroupBy<PMAccountGroup.groupID,
Sum<PMBudget.curyAmount,
Sum<PMBudget.amount,
Sum<PMBudget.curyRevisedAmount,
Sum<PMBudget.revisedAmount,
Sum<PMBudget.curyActualAmount,
Sum<PMBudget.actualAmount,
Sum<PMBudget.curyCommittedAmount,
Sum<PMBudget.committedAmount,
Sum<PMBudget.curyCommittedOrigAmount,
Sum<PMBudget.committedOrigAmount,
Sum<PMBudget.curyCommittedOpenAmount,
Sum<PMBudget.committedOpenAmount,
Sum<PMBudget.curyChangeOrderAmount,
Sum<PMBudget.changeOrderAmount,
Sum<PMBudget.curyCommittedInvoicedAmount,
Sum<PMBudget.committedInvoicedAmount,
Sum<PMBudget.curyDraftChangeOrderAmount>>>>>>>>>>>>>>>>>>>>>>(Base);
foreach (PXResult<PMBudget, PMAccountGroup, PMTask> res in select.Select())
{
PMBudget ps = (PMBudget)res;
PMAccountGroup ag = (PMAccountGroup)res;
PMTask pt = (PMTask)res;
if (ag.IsExpense == true)
{
expense.Add(BalanceRecordFromBudget(ps, ag ,pt));
}
else
{
switch (ag.Type)
{
case AccountType.Asset:
asset.Add(BalanceRecordFromBudget(ps, ag, pt));
break;
case AccountType.Liability:
liability.Add(BalanceRecordFromBudget(ps, ag, pt));
break;
case AccountType.Income:
income.Add(BalanceRecordFromBudget(ps, ag, pt));
break;
case AccountType.Expense:
expense.Add(BalanceRecordFromBudget(ps, ag, pt));
break;
case PMAccountType.OffBalance:
offbalance.Add(BalanceRecordFromBudget(ps, ag, pt));
break;
}
}
}
asset.Sort((x, y) => CompareBalanceRecords(x, y));
liability.Sort((x, y) => CompareBalanceRecords(x, y));
income.Sort((x, y) => CompareBalanceRecords(x, y));
expense.Sort((x, y) => CompareBalanceRecords(x, y));
offbalance.Sort((x, y) => CompareBalanceRecords(x, y));
int cx = 0;
var cache = Base.BalanceRecords.Cache;
foreach (PMProjectBalanceRecord line in GetBalanceLines(AccountType.Asset, asset))
{
line.SortOrder = cx++;
yield return cache.Locate(line) ?? cache.Insert(line);
}
foreach (PMProjectBalanceRecord line in GetBalanceLines(AccountType.Liability, liability))
{
line.SortOrder = cx++;
yield return cache.Locate(line) ?? cache.Insert(line);
}
foreach (PMProjectBalanceRecord line in GetBalanceLines(AccountType.Income, income))
{
line.SortOrder = cx++;
yield return cache.Locate(line) ?? cache.Insert(line);
}
foreach (PMProjectBalanceRecord line in GetBalanceLines(AccountType.Expense, expense))
{
line.SortOrder = cx++;
yield return cache.Locate(line) ?? cache.Insert(line);
}
foreach (PMProjectBalanceRecord line in GetBalanceLines(PMAccountType.OffBalance, offbalance))
{
line.SortOrder = cx++;
yield return cache.Locate(line) ?? cache.Insert(line);
}
Base.BalanceRecords.Cache.IsDirty = false;
}
public virtual IEnumerable GetBalanceLines(string accountType, List<PMProjectBalanceRecord> records)
{
if (records.Count > 0)
{
if (!Base.IsMobile)
{
yield return Base.CreateHeader(accountType);
}
decimal curyTotalAmt = 0;
decimal curyTotalRevAmt = 0;
decimal curyTotalActAmt = 0;
decimal totalActAmt = 0;
decimal curyTotalComAmt = 0;
decimal curyTotalComOpenAmt = 0;
decimal curyTotalComInvoicedAmt = 0;
decimal curyTotalComOrigAmt = 0;
decimal curyDraftCOAmt = 0;
decimal curyBudgetedCOAmt = 0;
decimal curyCommittedCOAmt = 0;
foreach (PMProjectBalanceRecord record in records)
{
if (!Base.IsMobile)
{
curyTotalAmt += record.CuryAmount ?? 0;
curyTotalRevAmt += record.CuryRevisedAmount ?? 0;
curyTotalActAmt += record.CuryActualAmount ?? 0;
totalActAmt += record.ActualAmount ?? 0;
curyTotalComAmt += record.CuryCommittedAmount ?? 0;
curyTotalComOpenAmt += record.CuryCommittedOpenAmount ?? 0;
curyTotalComInvoicedAmt += record.CuryCommittedInvoicedAmount ?? 0;
curyTotalComOrigAmt += record.CuryOriginalCommittedAmount ?? 0;
curyDraftCOAmt += record.CuryDraftCOAmount ?? 0;
curyBudgetedCOAmt += record.CuryBudgetedCOAmount ?? 0;
curyCommittedCOAmt += record.CuryCommittedCOAmount ?? 0;
}
yield return record;
}
if (!Base.IsMobile)
{
yield return Base.CreateTotal(accountType, curyTotalAmt, curyTotalRevAmt, curyTotalActAmt, totalActAmt, curyTotalComAmt, curyTotalComOpenAmt, curyTotalComInvoicedAmt, curyTotalComOrigAmt, curyDraftCOAmt, curyBudgetedCOAmt, curyCommittedCOAmt);
}
}
}
public virtual PMProjectBalanceRecord BalanceRecordFromBudget(PMBudget ps, PMAccountGroup ag, PMTask pt)
{
PMProjectBalanceRecord record = new PMProjectBalanceRecord();
record.RecordID = ps.AccountGroupID;
record.AccountGroup = ag.GroupCD;
record.Description = ag.Description;
record.CuryAmount = ps.CuryAmount;
record.Amount = ps.Amount;
record.CuryRevisedAmount = ps.CuryRevisedAmount;
record.RevisedAmount = ps.RevisedAmount;
record.CuryActualAmount = ps.CuryActualAmount;
record.ActualAmount = ps.ActualAmount;
record.CuryDraftCOAmount = ps.CuryDraftChangeOrderAmount;
record.CuryBudgetedCOAmount = ps.CuryChangeOrderAmount;
record.BudgetedCOAmount = ps.ChangeOrderAmount;
record.CuryOriginalCommittedAmount = ps.CuryCommittedOrigAmount;
record.OriginalCommittedAmount = ps.CommittedOrigAmount;
record.CuryCommittedCOAmount = ps.CuryCommittedCOAmount;
record.CommittedCOAmount = ps.CommittedCOAmount;
record.CuryCommittedAmount = ps.CuryCommittedAmount;
record.CommittedAmount = ps.CommittedAmount;
record.CuryCommittedOpenAmount = ps.CuryCommittedOpenAmount;
record.CommittedOpenAmount = ps.CommittedOpenAmount;
record.CuryCommittedInvoicedAmount = ps.CuryCommittedInvoicedAmount;
record.CommittedInvoicedAmount = ps.CommittedInvoicedAmount;
AAPMProjectBalanceRecordExt rowExt = PXCache<PMProjectBalanceRecord>.GetExtension<AAPMProjectBalanceRecordExt>(record);
rowExt.UsrAAProjectTaskId = pt.TaskCD;
return record;
}
private int CompareBalanceRecords(PMProjectBalanceRecord x, PMProjectBalanceRecord y)
{
return x.AccountGroup.CompareTo(y.AccountGroup);
}
}
public class AAPMProjectBalanceRecordExt : PXCacheExtension<PX.Objects.PM.ProjectEntry.PMProjectBalanceRecord>
{
#region UsrProjectTaskId
[PXString]
[PXUIField(DisplayName = "Project Task Id")]
public virtual string UsrAAProjectTaskId { get; set; }
public abstract class usrProjectTaskId : PX.Data.BQL.BqlString.Field<usrProjectTaskId> { }
#endregion
}