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?
}
