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;
}
}
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 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 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;
}
}
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;
}
}
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;
});
}
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();
}
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
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 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;
}
}
