public virtual void RunProjectBalanceVerification(PMProject project) { PXDatabase.Delete <PMTaskTotal>(new PXDataFieldRestrict(typeof(PMTaskTotal.projectID).Name, PXDbType.Int, 4, project.ContractID, PXComp.EQ)); PXDatabase.Delete <PMTaskAllocTotal>(new PXDataFieldRestrict(typeof(PMTaskAllocTotal.projectID).Name, PXDbType.Int, 4, project.ContractID, PXComp.EQ)); PXDatabase.Delete <PMHistory>(new PXDataFieldRestrict(typeof(PMHistory.projectID).Name, PXDbType.Int, 4, project.ContractID, PXComp.EQ)); foreach (PMProjectStatusEx status in PXSelect <PMProjectStatusEx, Where <PMProjectStatusEx.projectID, Equal <Required <PMProjectStatusEx.projectID> > > > .Select(this, project.ContractID)) { PXDatabase.Update <PMProjectStatus>(new PXDataFieldRestrict(typeof(PMProjectStatus.projectID).Name, PXDbType.Int, 4, project.ContractID, PXComp.EQ), new PXDataFieldAssign(typeof(PMProjectStatus.actualAmount).Name, PXDbType.Decimal, 0m), new PXDataFieldAssign(typeof(PMProjectStatus.actualQty).Name, PXDbType.Decimal, 0m)); PMHistory2Accum hist2 = new PMHistory2Accum(); hist2.ProjectID = status.ProjectID; hist2.ProjectTaskID = status.ProjectTaskID; hist2.AccountGroupID = status.AccountGroupID; hist2.InventoryID = status.InventoryID ?? PMProjectStatus.EmptyInventoryID; hist2.PeriodID = status.PeriodID; hist2 = History2.Insert(hist2); hist2.PTDBudgetAmount += status.Amount; hist2.PTDBudgetQty += status.Qty; hist2.BudgetAmount += status.Amount; hist2.BudgetQty += status.Qty; hist2.PTDRevisedAmount += status.RevisedAmount; hist2.PTDRevisedQty += status.RevisedQty; hist2.RevisedAmount += status.RevisedAmount; hist2.RevisedQty += status.RevisedQty; } PXSelectBase <PMTran> select = new PXSelectJoin <PMTran, LeftJoin <Account, On <PMTran.accountID, Equal <Account.accountID> >, InnerJoin <PMProject, On <PMProject.contractID, Equal <PMTran.projectID> >, LeftJoin <PMTask, On <PMTask.projectID, Equal <PMTran.projectID>, And <PMTask.taskID, Equal <PMTran.taskID> > >, LeftJoin <OffsetAccount, On <PMTran.offsetAccountID, Equal <OffsetAccount.accountID> >, LeftJoin <PMAccountGroup, On <PMAccountGroup.groupID, Equal <Account.accountGroupID> >, LeftJoin <OffsetPMAccountGroup, On <OffsetPMAccountGroup.groupID, Equal <OffsetAccount.accountGroupID> > > > > > > >, Where <PMTran.projectID, Equal <Required <PMTran.projectID> >, And <PMTran.released, Equal <True> > > >(this); foreach (PXResult <PMTran, Account, PMProject, PMTask, OffsetAccount, PMAccountGroup, OffsetPMAccountGroup> res in select.Select(project.ContractID)) { PMTran tran = (PMTran)res; Account acc = (Account)res; PMAccountGroup ag = (PMAccountGroup)res; OffsetAccount offsetAcc = (OffsetAccount)res; OffsetPMAccountGroup offsetAg = (OffsetPMAccountGroup)res; PMTask task = (PMTask)res; IList <PMHistory> list = RegisterReleaseProcess.UpdateProjectBalance(this, task, tran, acc, ag, offsetAcc, offsetAg); #region History Update foreach (PMHistory item in list) { PMHistoryAccum hist = new PMHistoryAccum(); hist.ProjectID = item.ProjectID; hist.ProjectTaskID = item.ProjectTaskID; hist.AccountGroupID = item.AccountGroupID; hist.InventoryID = item.InventoryID; hist.PeriodID = item.PeriodID; hist = History.Insert(hist); hist.FinPTDAmount += item.FinPTDAmount.GetValueOrDefault(); hist.FinYTDAmount += item.FinYTDAmount.GetValueOrDefault(); hist.FinPTDQty += item.FinPTDQty.GetValueOrDefault(); hist.FinYTDQty += item.FinYTDQty.GetValueOrDefault(); hist.TranPTDAmount += item.TranPTDAmount.GetValueOrDefault(); hist.TranYTDAmount += item.TranYTDAmount.GetValueOrDefault(); hist.TranPTDQty += item.TranPTDQty.GetValueOrDefault(); hist.TranYTDQty += item.TranYTDQty.GetValueOrDefault(); } #endregion } PXSelectBase <PMTran> select2 = new PXSelect <PMTran, Where <PMTran.origProjectID, Equal <Required <PMTran.origProjectID> >, And <PMTran.origTaskID, IsNotNull, And <PMTran.origAccountGroupID, IsNotNull> > > >(this); foreach (PMTran tran in select2.Select(project.ContractID)) { PMTaskAllocTotalAccum tat = new PMTaskAllocTotalAccum(); tat.ProjectID = tran.OrigProjectID; tat.TaskID = tran.OrigTaskID; tat.AccountGroupID = tran.OrigAccountGroupID; tat.InventoryID = tran.InventoryID; tat = AllocationTotals.Insert(tat); tat.Amount += tran.Amount; tat.Quantity += tran.Qty; } foreach (PMProjectStatusAccum item in this.Caches[typeof(PMProjectStatusAccum)].Inserted) { Debug.Print("Task={0} AG={1} Qty={2} Amt={3}", item.ProjectTaskID, item.AccountGroupID, item.ActualQty, item.ActualAmount); } }
public virtual void RunProjectBalanceVerification(PMProject project, bool recalculateUnbilledSummary) { PXDatabase.Delete <PMTaskTotal>(new PXDataFieldRestrict(typeof(PMTaskTotal.projectID).Name, PXDbType.Int, 4, project.ContractID, PXComp.EQ)); PXDatabase.Delete <PMTaskAllocTotal>(new PXDataFieldRestrict(typeof(PMTaskAllocTotal.projectID).Name, PXDbType.Int, 4, project.ContractID, PXComp.EQ)); PXDatabase.Delete <PMHistory>(new PXDataFieldRestrict(typeof(PMHistory.projectID).Name, PXDbType.Int, 4, project.ContractID, PXComp.EQ)); if (recalculateUnbilledSummary) { PXDatabase.Delete <PMUnbilledDailySummary>(new PXDataFieldRestrict(typeof(PMUnbilledDailySummary.projectID).Name, PXDbType.Int, 4, project.ContractID, PXComp.EQ)); } foreach (PMProjectStatusEx status in PXSelect <PMProjectStatusEx, Where <PMProjectStatusEx.projectID, Equal <Required <PMProjectStatusEx.projectID> > > > .Select(this, project.ContractID)) { PXDatabase.Update <PMProjectStatus>(new PXDataFieldRestrict(typeof(PMProjectStatus.projectID).Name, PXDbType.Int, 4, project.ContractID, PXComp.EQ), new PXDataFieldAssign(typeof(PMProjectStatus.actualAmount).Name, PXDbType.Decimal, 0m), new PXDataFieldAssign(typeof(PMProjectStatus.actualQty).Name, PXDbType.Decimal, 0m)); PMHistory2Accum hist2 = new PMHistory2Accum(); hist2.ProjectID = status.ProjectID; hist2.ProjectTaskID = status.ProjectTaskID; hist2.AccountGroupID = status.AccountGroupID; hist2.InventoryID = status.InventoryID ?? PMProjectStatus.EmptyInventoryID; hist2.PeriodID = status.PeriodID; hist2 = History2.Insert(hist2); hist2.PTDBudgetAmount += status.Amount; hist2.PTDBudgetQty += status.Qty; hist2.BudgetAmount += status.Amount; hist2.BudgetQty += status.Qty; hist2.PTDRevisedAmount += status.RevisedAmount; hist2.PTDRevisedQty += status.RevisedQty; hist2.RevisedAmount += status.RevisedAmount; hist2.RevisedQty += status.RevisedQty; } PXSelectBase <PMTran> select = new PXSelectJoinGroupBy <PMTran, LeftJoin <Account, On <PMTran.accountID, Equal <Account.accountID> >, LeftJoin <OffsetAccount, On <PMTran.offsetAccountID, Equal <OffsetAccount.accountID> >, LeftJoin <PMAccountGroup, On <PMAccountGroup.groupID, Equal <Account.accountGroupID> >, LeftJoin <OffsetPMAccountGroup, On <OffsetPMAccountGroup.groupID, Equal <OffsetAccount.accountGroupID> > > > > >, Where <PMTran.projectID, Equal <Required <PMTran.projectID> >, And <PMTran.released, Equal <True> > >, Aggregate <GroupBy <PMTran.tranType, GroupBy <PMTran.finPeriodID, GroupBy <PMTran.tranPeriodID, GroupBy <PMTran.projectID, GroupBy <PMTran.taskID, GroupBy <PMTran.inventoryID, GroupBy <PMTran.accountID, GroupBy <PMTran.accountGroupID, GroupBy <PMTran.offsetAccountID, GroupBy <PMTran.offsetAccountGroupID, GroupBy <PMTran.uOM, GroupBy <PMTran.released, Sum <PMTran.qty, Sum <PMTran.amount> > > > > > > > > > > > > > > >(this); if (recalculateUnbilledSummary) { select = new PXSelectJoinGroupBy <PMTran, LeftJoin <Account, On <PMTran.accountID, Equal <Account.accountID> >, LeftJoin <OffsetAccount, On <PMTran.offsetAccountID, Equal <OffsetAccount.accountID> >, LeftJoin <PMAccountGroup, On <PMAccountGroup.groupID, Equal <Account.accountGroupID> >, LeftJoin <OffsetPMAccountGroup, On <OffsetPMAccountGroup.groupID, Equal <OffsetAccount.accountGroupID> > > > > >, Where <PMTran.projectID, Equal <Required <PMTran.projectID> >, And <PMTran.released, Equal <True> > >, Aggregate <GroupBy <PMTran.tranType, GroupBy <PMTran.finPeriodID, GroupBy <PMTran.tranPeriodID, GroupBy <PMTran.projectID, GroupBy <PMTran.taskID, GroupBy <PMTran.inventoryID, GroupBy <PMTran.date, GroupBy <PMTran.accountID, GroupBy <PMTran.accountGroupID, GroupBy <PMTran.offsetAccountID, GroupBy <PMTran.offsetAccountGroupID, GroupBy <PMTran.uOM, GroupBy <PMTran.released, Sum <PMTran.qty, Sum <PMTran.amount, Max <PMTran.billable, Max <PMTran.billed, Max <PMTran.reversed> > > > > > > > > > > > > > > > > > > >(this); } else { select = new PXSelectJoinGroupBy <PMTran, LeftJoin <Account, On <PMTran.accountID, Equal <Account.accountID> >, LeftJoin <OffsetAccount, On <PMTran.offsetAccountID, Equal <OffsetAccount.accountID> >, LeftJoin <PMAccountGroup, On <PMAccountGroup.groupID, Equal <Account.accountGroupID> >, LeftJoin <OffsetPMAccountGroup, On <OffsetPMAccountGroup.groupID, Equal <OffsetAccount.accountGroupID> > > > > >, Where <PMTran.projectID, Equal <Required <PMTran.projectID> >, And <PMTran.released, Equal <True> > >, Aggregate <GroupBy <PMTran.tranType, GroupBy <PMTran.finPeriodID, GroupBy <PMTran.tranPeriodID, GroupBy <PMTran.projectID, GroupBy <PMTran.taskID, GroupBy <PMTran.inventoryID, GroupBy <PMTran.accountID, GroupBy <PMTran.accountGroupID, GroupBy <PMTran.offsetAccountID, GroupBy <PMTran.offsetAccountGroupID, GroupBy <PMTran.uOM, GroupBy <PMTran.released, Sum <PMTran.qty, Sum <PMTran.amount> > > > > > > > > > > > > > > >(this); } foreach (PXResult <PMTran, Account, OffsetAccount, PMAccountGroup, OffsetPMAccountGroup> res in select.Select(project.ContractID)) { PMTran tran = (PMTran)res; Account acc = (Account)res; PMAccountGroup ag = (PMAccountGroup)res; OffsetAccount offsetAcc = (OffsetAccount)res; OffsetPMAccountGroup offsetAg = (OffsetPMAccountGroup)res; //suppose we have allocated unbilled 100 - unearned 100 //during billing we reduced the amount to 80. //as a result of this. only 80 will be reversed. leaving 20 on the unbilled. //plus a remainder transaction will be generated. (if we allow this remainder to update balance it will add additional 20 to the unbilled.) if (tran.RemainderOfTranID != null) { continue; //skip remainder transactions. } IList <PMHistory> list = RegisterReleaseProcess.UpdateProjectBalance(this, tran, acc, ag, offsetAcc, offsetAg); RegisterReleaseProcess.AddToUnbilledSummary(this, tran); #region History Update foreach (PMHistory item in list) { PMHistoryAccum hist = new PMHistoryAccum(); hist.ProjectID = item.ProjectID; hist.ProjectTaskID = item.ProjectTaskID; hist.AccountGroupID = item.AccountGroupID; hist.InventoryID = item.InventoryID; hist.PeriodID = item.PeriodID; hist = History.Insert(hist); hist.FinPTDAmount += item.FinPTDAmount.GetValueOrDefault(); hist.FinYTDAmount += item.FinYTDAmount.GetValueOrDefault(); hist.FinPTDQty += item.FinPTDQty.GetValueOrDefault(); hist.FinYTDQty += item.FinYTDQty.GetValueOrDefault(); hist.TranPTDAmount += item.TranPTDAmount.GetValueOrDefault(); hist.TranYTDAmount += item.TranYTDAmount.GetValueOrDefault(); hist.TranPTDQty += item.TranPTDQty.GetValueOrDefault(); hist.TranYTDQty += item.TranYTDQty.GetValueOrDefault(); } #endregion } PXSelectBase <PMTran> select2 = new PXSelect <PMTran, Where <PMTran.origProjectID, Equal <Required <PMTran.origProjectID> >, And <PMTran.origTaskID, IsNotNull, And <PMTran.origAccountGroupID, IsNotNull> > > >(this); foreach (PMTran tran in select2.Select(project.ContractID)) { PMTaskAllocTotalAccum tat = new PMTaskAllocTotalAccum(); tat.ProjectID = tran.OrigProjectID; tat.TaskID = tran.OrigTaskID; tat.AccountGroupID = tran.OrigAccountGroupID; tat.InventoryID = tran.InventoryID; tat = AllocationTotals.Insert(tat); tat.Amount += tran.Amount; tat.Quantity += tran.Qty; } //foreach (PMProjectStatusAccum item in this.Caches[typeof(PMProjectStatusAccum)].Inserted) //{ // Debug.Print("Task={0} AG={1} Qty={2} Amt={3}", item.ProjectTaskID, item.AccountGroupID, item.ActualQty, item.ActualAmount); //} }