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?
}
/// <summary>
/// export all GL Balances in the given year, towards the specified cost centres
/// </summary>
public static void ExportGLBalances(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres,
string AIgnoreAccounts)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "balance.csv"));
Console.WriteLine("Writing file: " + filename);
string sql =
String.Format("SELECT GLM.{1} AS CostCentre, GLM.{0} AS Account, {2} AS StartBalance, GLMP.{3} AS EndBalance " +
"FROM PUB_{4} AS GLM, PUB_{10} AS A, PUB_{13} AS GLMP " +
"WHERE GLM.{5} = {6} AND {7} = {8} AND GLM.{1} IN ({9}) " +
"AND GLMP.{14} = GLM.{14} AND GLMP.{15} = 12 " +
"AND A.{5} = GLM.{5} AND A.{0} = GLM.{0} AND " +
"A.{11}=true AND NOT GLM.{0} IN ({12})" +
"ORDER BY GLM.{1}, GLM.{0}",
AGeneralLedgerMasterTable.GetAccountCodeDBName(),
AGeneralLedgerMasterTable.GetCostCentreCodeDBName(),
AGeneralLedgerMasterTable.GetStartBalanceBaseDBName(),
AGeneralLedgerMasterPeriodTable.GetActualBaseDBName(),
AGeneralLedgerMasterTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGeneralLedgerMasterTable.GetYearDBName(),
AFinancialYear,
"'" + ACostCentres.Replace(",", "','") + "'",
AAccountTable.GetTableDBName(),
AAccountTable.GetPostingStatusDBName(),
"'" + AIgnoreAccounts.Replace(",", "','") + "'",
AGeneralLedgerMasterPeriodTable.GetTableDBName(),
AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName(),
AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName());
TDBTransaction Transaction = new TDBTransaction();
DataTable balances = null;
DBAccess.ReadTransaction(ref Transaction,
delegate
{
balances = Transaction.DataBaseObj.SelectDT(sql, "balances", Transaction);
});
StringBuilder sb = new StringBuilder();
if (balances != null)
{
foreach (DataRow row in balances.Rows)
{
sb.Append(StringHelper.StrMerge(
new string[] {
row["CostCentre"].ToString(),
row["Account"].ToString(),
String.Format("{0:N}", Convert.ToDecimal(row["StartBalance"])),
String.Format("{0:N}", Convert.ToDecimal(row["EndBalance"]))
}, ACSVSeparator));
sb.Append(ANewLine);
}
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
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);
}