private static void StringBudgetTypeSplitAmounts(int ABudgetSequence, int ANumPeriods, ref ABudgetPeriodTable ABudgetPeriod, char ASeparator, out String ASb) { ABudgetPeriodRow budgetPeriodRow; ASb = string.Empty; decimal perPeriodAmount = 0; decimal endPeriodAmount = 0; //Find periods 1-(total periods-1) amount budgetPeriodRow = (ABudgetPeriodRow)ABudgetPeriod.Rows.Find(new object[] { ABudgetSequence, 1 }); if (budgetPeriodRow != null) { perPeriodAmount = budgetPeriodRow.BudgetBase; budgetPeriodRow = null; //Find period FNumberOfPeriods amount budgetPeriodRow = (ABudgetPeriodRow)ABudgetPeriod.Rows.Find(new object[] { ABudgetSequence, ANumPeriods }); if (budgetPeriodRow != null) { endPeriodAmount = budgetPeriodRow.BudgetBase; } } //Calculate the total amount ASb += (perPeriodAmount * (ANumPeriods - 1) + endPeriodAmount).ToString(); }
private static void StringBudgetTypeAdhocAmounts(int ABudgetSequence, int ANumPeriods, ref ABudgetPeriodTable ABudgetPeriod, char ASeparator, out String ASb) { ABudgetPeriodRow budgetPeriodRow; ASb = string.Empty; for (int i = 1; i <= ANumPeriods; i++) { budgetPeriodRow = (ABudgetPeriodRow)ABudgetPeriod.Rows.Find(new object[] { ABudgetSequence, i }); if (budgetPeriodRow != null) { ASb += budgetPeriodRow.BudgetBase.ToString(); if (i < ANumPeriods) { ASb += ASeparator.ToString(); } } budgetPeriodRow = null; } }
/// <summary> /// Update Budget Period Amounts for each record that was created or modified during an import /// </summary> private void UpdateABudgetPeriodAmounts(BudgetTDS AMainDS, int ACurrentBudgetYear) { DataView BudgetDV = new DataView(AMainDS.ABudget); BudgetDV.RowFilter = String.Format("Isnull({0},'') <> '' And ({0}='Added' Or {0}='Updated')", ABudgetTable.GetCommentDBName()); BudgetDV.Sort = String.Format("{0} ASC", ABudgetTable.GetYearDBName()); foreach (DataRowView drv in BudgetDV) { BudgetTDSABudgetRow budgetRow = (BudgetTDSABudgetRow)drv.Row; int budgetSeq = budgetRow.BudgetSequence; DataView budgetPeriodsDV = new DataView(AMainDS.ABudgetPeriod); budgetPeriodsDV.RowFilter = String.Format("{0}={1}", ABudgetPeriodTable.GetBudgetSequenceDBName(), budgetRow.BudgetSequence); budgetPeriodsDV.Sort = String.Format("{0} ASC", ABudgetPeriodTable.GetPeriodNumberDBName()); foreach (DataRowView drv2 in budgetPeriodsDV) { ABudgetPeriodRow budgetPeriodRow = (ABudgetPeriodRow)drv2.Row; int period = budgetPeriodRow.PeriodNumber; string periodAmountColumn = string.Empty; if (period <= FNumberOfPeriods) { periodAmountColumn = "Period" + period.ToString("00") + "Amount"; budgetRow[periodAmountColumn] = budgetPeriodRow.BudgetBase; } else { //TODO After data migration, this should not happen so add an error message. // In old Petra, budget periods always go up to 20, but are only populated // up to number of financial periods } } } //Remove import related comment // - done separately to loop above to avoid confusion as DataView filtering is on Comment field foreach (DataRowView drv in BudgetDV) { BudgetTDSABudgetRow budgetRow = (BudgetTDSABudgetRow)drv.Row; budgetRow.Comment = string.Empty; } }
private static void StringBudgetTypeSameAmounts(int ABudgetSequence, ref ABudgetPeriodTable ABudgetPeriod, out String ASb) { ABudgetPeriodRow budgetPeriodRow; ASb = string.Empty; budgetPeriodRow = (ABudgetPeriodRow)ABudgetPeriod.Rows.Find(new object[] { ABudgetSequence, 1 }); if (budgetPeriodRow != null) { ASb += budgetPeriodRow.BudgetBase.ToString(); } }
public static decimal GetBudgetPeriodAmount(int ABudgetSequence, int APeriodNumber) { decimal retVal = 0m; ABudgetPeriodTable BudgetPeriodTable = FMainDS.ABudgetPeriod; ABudgetPeriodRow BudgetPeriodRow = (ABudgetPeriodRow)BudgetPeriodTable.Rows.Find(new object[] { ABudgetSequence, APeriodNumber }); if (BudgetPeriodRow != null) { retVal = (decimal)BudgetPeriodRow.BudgetBase; } return(retVal); }
private static void StringBudgetTypeInflateNAmounts(int ABudgetSequence, int ANumPeriods, ref ABudgetPeriodTable ABudgetPeriod, char ASeparator, out String ASb) { ABudgetPeriodRow budgetPeriodRow; ASb = string.Empty; decimal firstPeriodAmount = 0; decimal currentPeriodAmount; for (int i = 1; i <= ANumPeriods; i++) { budgetPeriodRow = (ABudgetPeriodRow)ABudgetPeriod.Rows.Find(new object[] { ABudgetSequence, i }); if (budgetPeriodRow != null) { currentPeriodAmount = budgetPeriodRow.BudgetBase; if (i == 1) { firstPeriodAmount = currentPeriodAmount; ASb += currentPeriodAmount.ToString(); ASb += ASeparator.ToString(); } else { if (currentPeriodAmount != firstPeriodAmount) { ASb += (i - 1).ToString(); ASb += ASeparator.ToString(); ASb += ((currentPeriodAmount - firstPeriodAmount) / firstPeriodAmount * 100).ToString(); break; } else if (i == ANumPeriods) // and by implication CurrentPeriodAmount == FirstPeriodAmount { //This is an odd case that the user should never implement, but still needs to be covered. // It is equivalent to using BUDGET TYPE: SAME ASb += "0"; ASb += ASeparator.ToString(); ASb += "0"; } } } budgetPeriodRow = null; } }
public static decimal SetBudgetPeriodBaseAmount(int ABudgetSequence, int APeriodNumber, decimal ABudgetAmount) { decimal retVal = 0; ABudgetPeriodTable BudgetPeriodTable = FMainDS.ABudgetPeriod; ABudgetPeriodRow BudgetPeriodRow = (ABudgetPeriodRow)BudgetPeriodTable.Rows.Find(new object[] { ABudgetSequence, APeriodNumber }); if (BudgetPeriodRow != null) { BudgetPeriodRow.BeginEdit(); BudgetPeriodRow.BudgetBase = ABudgetAmount; BudgetPeriodRow.EndEdit(); retVal = ABudgetAmount; } return(retVal); }
private static void StringBudgetTypeInflateBaseAmounts(int ABudgetSequence, int ANumPeriods, ref ABudgetPeriodTable ABudgetPeriod, char ASeparator, out String ASb) { ABudgetPeriodRow budgetPeriodRow; ASb = string.Empty; decimal priorPeriodAmount = 0; decimal currentPeriodAmount = 0; for (int i = 1; i <= ANumPeriods; i++) { budgetPeriodRow = (ABudgetPeriodRow)ABudgetPeriod.Rows.Find(new object[] { ABudgetSequence, i }); if (budgetPeriodRow != null) { currentPeriodAmount = budgetPeriodRow.BudgetBase; if (i == 1) { ASb += currentPeriodAmount.ToString(); } else { ASb += ((currentPeriodAmount - priorPeriodAmount) / priorPeriodAmount * 100).ToString(); } if (i < ANumPeriods) { ASb += ASeparator.ToString(); } priorPeriodAmount = currentPeriodAmount; } budgetPeriodRow = null; } }
/// <summary> /// Update Budget Period Amounts for each record that was created or modified during an import /// </summary> private void UpdateABudgetPeriodAmounts(BudgetTDS AMainDS, int ASelectedBudgetYear) { foreach (BudgetTDSABudgetRow budgetRow in AMainDS.ABudget.Rows) { if (budgetRow.RowState == DataRowState.Unchanged) { continue; } int budgetSeq = budgetRow.BudgetSequence; DataView budgetPeriodsDV = new DataView(AMainDS.ABudgetPeriod); budgetPeriodsDV.RowFilter = String.Format("{0}={1}", ABudgetPeriodTable.GetBudgetSequenceDBName(), budgetRow.BudgetSequence); budgetPeriodsDV.Sort = String.Format("{0} ASC", ABudgetPeriodTable.GetPeriodNumberDBName()); foreach (DataRowView drv2 in budgetPeriodsDV) { ABudgetPeriodRow budgetPeriodRow = (ABudgetPeriodRow)drv2.Row; int period = budgetPeriodRow.PeriodNumber; string periodAmountColumn = string.Empty; if (period <= FNumberOfPeriods) { periodAmountColumn = "Period" + period.ToString("00") + "Amount"; budgetRow[periodAmountColumn] = budgetPeriodRow.BudgetBase; } else { //TODO After data migration, this should not happen so add an error message. // In old Petra, budget periods always go up to 20, but are only populated // up to number of financial periods } } } //Attempts using LINQ //DataTable BudgetPeriodAmounts = new DataTable(); //BudgetPeriodAmounts.Columns.Add("BudgetSequence", typeof(int)); //BudgetPeriodAmounts.Columns.Add("PeriodNumber", typeof(int)); //BudgetPeriodAmounts.Columns.Add("Amount", typeof(decimal)); //BudgetPeriodAmounts.PrimaryKey = new DataColumn[] {BudgetPeriodAmounts.Columns["BudgetSequence"], // BudgetPeriodAmounts.Columns["PeriodNumber"]}; //var varBudgetPeriodAmounts = // from BudgetTDSABudgetRow budgetRow in FMainDS.ABudget.Rows // where budgetRow.Year == ASpecificYear // join ABudgetPeriodRow budgetPeriodRow in FMainDS.ABudgetPeriod.Rows on budgetRow.BudgetSequence equals budgetPeriodRow.BudgetSequence // select new // { // BudgetSequence = budgetRow.BudgetSequence, // PeriodNumber = budgetPeriodRow.PeriodNumber, // Amount = budgetPeriodRow.BudgetBase // }; //produces flat sequence //foreach (var rowObj in varBudgetPeriodAmounts) //{ // DataRow row = BudgetPeriodAmounts.NewRow(); // BudgetPeriodAmounts.Rows.Add(rowObj.BudgetSequence, rowObj.PeriodNumber, rowObj.Amount); //} //DataView BudgetsPeriodAmountsForYearDV = new DataView(BudgetPeriodAmounts); //BudgetsPeriodAmountsForYearDV.Sort = "BudgetSequence ASC, PeriodNumber ASC"; //for (int i = 0; i < BudgetsForYear.Count; i++) //{ // BudgetTDSABudgetRow budgetRow = (BudgetTDSABudgetRow)BudgetsForYear[i].Row; // for (int j = 1; j <= FNumberOfPeriods; j++) // { // DataRow budgetsPeriodAmounts = BudgetsPeriodAmountsForYearDV[(FNumberOfPeriods * i) + j - 1].Row; // string columnName = "Period" + j.ToString("00") + "Amount"; // if (budgetRow.BudgetSequence == (int)budgetsPeriodAmounts["BudgetSequence"]) // { // budgetRow[columnName] = (decimal)budgetsPeriodAmounts["Amount"]; // } // } //} }
private static Int32 ExportBudgetToCSV(Int32 ALedgerNumber, string ACSVFileName, string[] AFdlgSeparator, ref string AFileContents, ref BudgetTDS AExportDS, ref TVerificationResultCollection AVerificationResult) { Int32 numBudgetsExported = 0; ALedgerRow lr = (ALedgerRow)AExportDS.ALedger.Rows[0]; ABudgetPeriodTable budgetPeriod = (ABudgetPeriodTable)AExportDS.ABudgetPeriod; Int32 numPeriods = lr.NumberOfAccountingPeriods; char separator = AFdlgSeparator[0].Substring(0, 1).ToCharArray()[0]; TLogging.Log("Writing file: " + ACSVFileName); StringBuilder sb = new StringBuilder(); string budgetAmounts = string.Empty; foreach (ABudgetRow row in AExportDS.ABudget.Rows) { switch (row.BudgetTypeCode) { case MFinanceConstants.BUDGET_SAME: StringBudgetTypeSameAmounts(row.BudgetSequence, ref budgetPeriod, out budgetAmounts); break; case MFinanceConstants.BUDGET_SPLIT: StringBudgetTypeSplitAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; case MFinanceConstants.BUDGET_INFLATE_BASE: StringBudgetTypeInflateBaseAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; case MFinanceConstants.BUDGET_INFLATE_N: StringBudgetTypeInflateNAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; default: //MFinanceConstants.BUDGET_ADHOC: StringBudgetTypeAdhocAmounts(row.BudgetSequence, numPeriods, ref budgetPeriod, separator, out budgetAmounts); break; } sb.Append(StringHelper.StrMerge( new string[] { Encase(row.CostCentreCode), Encase(row.AccountCode), Encase(row.BudgetTypeCode), Encase(BudgetRevisionYearName(ALedgerNumber, row.Year)) }, separator)); sb.Append(separator.ToString()); sb.Append(budgetAmounts); sb.Append(Environment.NewLine); numBudgetsExported++; } AFileContents = sb.ToString(); return(numBudgetsExported); }
public void T0_Consolidation() { // reset the database, so that there is no consolidated budget CommonNUnitFunctions.ResetDatabase(); string budgetTestFile = TAppSettingsManager.GetValue("GiftBatch.file", CommonNUnitFunctions.rootPath + "/csharp/ICT/Testing/lib/MFinance/SampleData/BudgetImport-All.csv"); int NumBudgetsUpdated; int NumFailedRows; TVerificationResultCollection VerificationResult; BudgetTDS ImportDS = new BudgetTDS(); string ImportString = File.ReadAllText(budgetTestFile); // import budget from CSV decimal RowsImported = TBudgetMaintainWebConnector.ImportBudgets( FLedgerNumber, 0, ImportString, budgetTestFile, new string[] { ",", "dmy", "American" }, ref ImportDS, out NumBudgetsUpdated, out NumFailedRows, out VerificationResult); Assert.AreNotEqual(0, RowsImported, "expect to import several rows"); CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult, "ImportBudgets has critical errors:"); BudgetTDSAccess.SubmitChanges(ImportDS); // check for value in budget table string sqlQueryBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_revision_i = 0 AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), ABudgetPeriodTable.GetTableDBName(), FLedgerNumber); decimal budgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(250m, budgetValue, "problem with importing budget from CSV"); // check for zero in glmperiod budget: that row does not even exist yet, so check that it does not exist string sqlQueryCheckEmptyConsolidatedBudget = String.Format( "SELECT COUNT(*) FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {2} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); Assert.AreEqual(0, DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryCheckEmptyConsolidatedBudget, IsolationLevel.ReadCommitted), "budget should not be consolidated yet"); // consolidate the budget TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); // check for correct value in glmperiod budget string sqlQueryConsolidatedBudget = String.Format( "SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_glm_sequence_i = {2}.a_glm_sequence_i AND a_period_number_i = 1 AND " + "a_ledger_number_i = {3} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", AGeneralLedgerMasterPeriodTable.GetBudgetBaseDBName(), AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), FLedgerNumber); decimal consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(250m, consolidatedBudgetValue, "budget should now be consolidated"); // TODO: also check some summary account and cost centre for summed up budget values // check how reposting a budget works string sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 44 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); bool SubmissionOK = true; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction); }); // post all budgets again TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true); consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(44.0m, consolidatedBudgetValue, "budget should be consolidated with the new value"); // post only a modified budget (testing UnPostBudget) sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 65 WHERE a_period_number_i = 1 AND " + "EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')", ABudgetPeriodTable.GetTableDBName(), ABudgetPeriodTable.GetBudgetBaseDBName(), ABudgetTable.GetTableDBName(), FLedgerNumber); string sqlMarkBudgetForConsolidation = String.Format("UPDATE PUB_{0} SET {1} = false WHERE " + "a_ledger_number_i = {2} " + "AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'", ABudgetTable.GetTableDBName(), ABudgetTable.GetBudgetStatusDBName(), FLedgerNumber); SubmissionOK = true; Transaction = null; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction); DBAccess.GDBAccessObj.ExecuteNonQuery(sqlMarkBudgetForConsolidation, Transaction); }); // post only modified budget again TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber); TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, false); consolidatedBudgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted)); Assert.AreEqual(65.0m, consolidatedBudgetValue, "budget should be consolidated with the new value, after UnPostBudget"); // TODO: test forwarding periods. what happens to next year values, when there is no next year glm record yet? }
private void ConsolidateBudgetsInternal(Int32 ALedgerNumber, bool AConsolidateAll) { TDBTransaction Transaction = new TDBTransaction(); Boolean SubmissionOK = false; TDataBase db = DBAccess.Connect("Budget"); db.WriteTransaction(ref Transaction, ref SubmissionOK, delegate { ALedgerRow LedgerRow = FBudgetTDS.ALedger[0]; // first clear the old budget from GLMPeriods if (AConsolidateAll) { foreach (ABudgetRow BudgetRow in FBudgetTDS.ABudget.Rows) { BudgetRow.BudgetStatus = false; } foreach (AGeneralLedgerMasterRow GeneralLedgerMasterRow in FGLPostingDS.AGeneralLedgerMaster.Rows) { for (int Period = 1; Period <= LedgerRow.NumberOfAccountingPeriods; Period++) { ClearAllBudgetValues(GeneralLedgerMasterRow.GlmSequence, Period); } } } else { foreach (ABudgetRow BudgetRow in FBudgetTDS.ABudget.Rows) { if (!BudgetRow.BudgetStatus) { UnPostBudget(BudgetRow, ALedgerNumber); } } } foreach (ABudgetRow BudgetRow in FBudgetTDS.ABudget.Rows) { if (!BudgetRow.BudgetStatus || AConsolidateAll) { List <ABudgetPeriodRow> budgetPeriods = new List <ABudgetPeriodRow>(); FBudgetTDS.ABudgetPeriod.DefaultView.RowFilter = ABudgetPeriodTable.GetBudgetSequenceDBName() + " = " + BudgetRow.BudgetSequence.ToString(); foreach (DataRowView rv in FBudgetTDS.ABudgetPeriod.DefaultView) { budgetPeriods.Add((ABudgetPeriodRow)rv.Row); } PostBudget(ALedgerNumber, BudgetRow, budgetPeriods); } } /*Consolidate_Budget*/ foreach (ABudgetRow BudgetRow in FBudgetTDS.ABudget.Rows) { BudgetRow.BudgetStatus = true; } ABudgetAccess.SubmitChanges(FBudgetTDS.ABudget, Transaction); FGLPostingDS.ThrowAwayAfterSubmitChanges = true; GLPostingTDSAccess.SubmitChanges(FGLPostingDS, Transaction.DataBaseObj); FGLPostingDS.Clear(); SubmissionOK = true; }); }
public static bool LoadBudgetForConsolidate(Int32 ALedgerNumber) { FBudgetTDS = new BudgetTDS(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerAccess.LoadByPrimaryKey(FBudgetTDS, ALedgerNumber, Transaction); string sqlLoadBudgetForThisAndNextYear = string.Format("SELECT * FROM PUB_{0} WHERE {1}=? AND ({2} = ? OR {2} = ?)", ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); List <OdbcParameter> parameters = new List <OdbcParameter>(); OdbcParameter param = new OdbcParameter("ledgernumber", OdbcType.Int); param.Value = ALedgerNumber; parameters.Add(param); param = new OdbcParameter("thisyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear; parameters.Add(param); param = new OdbcParameter("nextyear", OdbcType.Int); param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear + 1; parameters.Add(param); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetForThisAndNextYear, FBudgetTDS.ABudget.TableName, Transaction, parameters.ToArray()); string sqlLoadBudgetPeriodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_budget_sequence_i = {1}.a_budget_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", ABudgetPeriodTable.GetTableDBName(), ABudgetTable.GetTableDBName(), ABudgetTable.GetLedgerNumberDBName(), ABudgetTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetPeriodForThisAndNextYear, FBudgetTDS.ABudgetPeriod.TableName, Transaction, parameters.ToArray()); // Accept row changes here so that the Client gets 'unmodified' rows FBudgetTDS.AcceptChanges(); GLPostingDS = new GLPostingTDS(); AAccountAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); AAccountHierarchyDetailAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ACostCentreAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction); ALedgerAccess.LoadByPrimaryKey(GLPostingDS, ALedgerNumber, Transaction); // get the glm sequences for this year and next year for (int i = 0; i <= 1; i++) { int Year = GLPostingDS.ALedger[0].CurrentFinancialYear + i; AGeneralLedgerMasterRow TemplateRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false); TemplateRow.LedgerNumber = ALedgerNumber; TemplateRow.Year = Year; GLPostingDS.AGeneralLedgerMaster.Merge(AGeneralLedgerMasterAccess.LoadUsingTemplate(TemplateRow, Transaction)); } string sqlLoadGlmperiodForThisAndNextYear = string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND " + "{2}=? AND ({3} = ? OR {3} = ?)", AGeneralLedgerMasterPeriodTable.GetTableDBName(), AGeneralLedgerMasterTable.GetTableDBName(), AGeneralLedgerMasterTable.GetLedgerNumberDBName(), AGeneralLedgerMasterTable.GetYearDBName()); DBAccess.GDBAccessObj.Select(GLPostingDS, sqlLoadGlmperiodForThisAndNextYear, GLPostingDS.AGeneralLedgerMasterPeriod.TableName, Transaction, parameters.ToArray()); }); GLPostingDS.AcceptChanges(); return(true); }
public static void ConsolidateBudgets(Int32 ALedgerNumber, bool AConsolidateAll) { TDBTransaction transaction = null; Boolean SubmissionOK = false; DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref transaction, ref SubmissionOK, delegate { ALedgerRow LedgerRow = FBudgetTDS.ALedger[0]; // first clear the old budget from GLMPeriods if (AConsolidateAll) { foreach (ABudgetRow BudgetRow in FBudgetTDS.ABudget.Rows) { BudgetRow.BudgetStatus = false; } foreach (AGeneralLedgerMasterRow GeneralLedgerMasterRow in GLPostingDS.AGeneralLedgerMaster.Rows) { for (int Period = 1; Period <= LedgerRow.NumberOfAccountingPeriods; Period++) { ClearAllBudgetValues(GeneralLedgerMasterRow.GlmSequence, Period); } } } else { foreach (ABudgetRow BudgetRow in FBudgetTDS.ABudget.Rows) { if (!BudgetRow.BudgetStatus) { UnPostBudget(BudgetRow, ALedgerNumber); } } } foreach (ABudgetRow BudgetRow in FBudgetTDS.ABudget.Rows) { if (!BudgetRow.BudgetStatus || AConsolidateAll) { List <ABudgetPeriodRow> budgetPeriods = new List <ABudgetPeriodRow>(); FBudgetTDS.ABudgetPeriod.DefaultView.RowFilter = ABudgetPeriodTable.GetBudgetSequenceDBName() + " = " + BudgetRow.BudgetSequence.ToString(); foreach (DataRowView rv in FBudgetTDS.ABudgetPeriod.DefaultView) { budgetPeriods.Add((ABudgetPeriodRow)rv.Row); } PostBudget(ALedgerNumber, BudgetRow, budgetPeriods); } } FinishConsolidateBudget(transaction); GLPostingDS.ThrowAwayAfterSubmitChanges = true; GLPostingTDSAccess.SubmitChanges(GLPostingDS); GLPostingDS.Clear(); SubmissionOK = true; }); // Get NewOrExisting AutoTransaction } // Consolidate Budgets
public static void ConsolidateBudgets(Int32 ALedgerNumber, bool AConsolidateAll) { bool NewTransaction = false; TDBTransaction SubmitChangesTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction); ALedgerRow LedgerRow = FBudgetTDS.ALedger[0]; try { // first clear the old budget from GLMPeriods if (AConsolidateAll) { foreach (ABudgetRow BudgetRow in FBudgetTDS.ABudget.Rows) { BudgetRow.BudgetStatus = false; } foreach (AGeneralLedgerMasterRow GeneralLedgerMasterRow in GLPostingDS.AGeneralLedgerMaster.Rows) { for (int Period = 1; Period <= LedgerRow.NumberOfAccountingPeriods; Period++) { ClearAllBudgetValues(GeneralLedgerMasterRow.GlmSequence, Period); } } } else { foreach (ABudgetRow BudgetRow in FBudgetTDS.ABudget.Rows) { if (!BudgetRow.BudgetStatus) { UnPostBudget(BudgetRow, ALedgerNumber); } } } foreach (ABudgetRow BudgetRow in FBudgetTDS.ABudget.Rows) { if (!BudgetRow.BudgetStatus || AConsolidateAll) { List <ABudgetPeriodRow> budgetPeriods = new List <ABudgetPeriodRow>(); FBudgetTDS.ABudgetPeriod.DefaultView.RowFilter = ABudgetPeriodTable.GetBudgetSequenceDBName() + " = " + BudgetRow.BudgetSequence.ToString(); foreach (DataRowView rv in FBudgetTDS.ABudgetPeriod.DefaultView) { budgetPeriods.Add((ABudgetPeriodRow)rv.Row); } PostBudget(ALedgerNumber, BudgetRow, budgetPeriods); } } FinishConsolidateBudget(SubmitChangesTransaction); GLPostingDS.ThrowAwayAfterSubmitChanges = true; GLPostingTDSAccess.SubmitChanges(GLPostingDS); GLPostingDS.Clear(); if (NewTransaction) { DBAccess.GDBAccessObj.CommitTransaction(); } } catch (Exception Exc) { TLogging.Log("An Exception occured during the consolidation of Budgets:" + Environment.NewLine + Exc.ToString()); if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } throw; } }