public void T4_AutoGenerationSetBudgetAmount()
{
BudgetTDS MainDS = LoadData();
if (MainDS.ABudget.Count == 0)
{
return;
}
int budgetSequence = MainDS.ABudget[0].BudgetSequence;
ABudgetPeriodRow bPRow = (ABudgetPeriodRow)MainDS.ABudgetPeriod.Rows.Find(new object[] { budgetSequence, 1 });
if (bPRow == null)
{
Assert.IsNotNull(bPRow, String.Format("Cannot find budget period 1 value for budget sequence {0}", budgetSequence));
return;
}
//Add 10 to Budget base value and check if it is written
decimal budgetBase = bPRow.BudgetBase;
TBudgetAutoGenerateWebConnector.SetBudgetPeriodBaseAmount(budgetSequence, 1, (budgetBase + 10));
decimal budgetBaseNew = TBudgetAutoGenerateWebConnector.GetBudgetPeriodAmount(budgetSequence, 1);
Assert.IsTrue(budgetBaseNew == (budgetBase + 10), String.Format("SetBudgetPeriod Failed. BudgetBase ({0}) has not been updated to: {1}",
budgetBase,
budgetBaseNew));
}
/// <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;
}
}
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);
}
/// <summary>
/// Unpost a budget
/// </summary>
/// <param name="ABudgetRow"></param>
/// <param name="ALedgerNumber"></param>
/// <returns>true if it seemed to go OK</returns>
private static bool UnPostBudget(ABudgetRow ABudgetRow, int ALedgerNumber)
{
/* post the negative budget, which will result in an empty a_glm_period.budget */
// get the current budget value for each GLM Period, and unpost that budget
GLPostingDS.AGeneralLedgerMaster.DefaultView.Sort = String.Format("{0},{1},{2},{3}",
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
AGeneralLedgerMasterTable.GetYearDBName(),
AGeneralLedgerMasterTable.GetAccountCodeDBName(),
AGeneralLedgerMasterTable.GetCostCentreCodeDBName());
int glmIndex = GLPostingDS.AGeneralLedgerMaster.DefaultView.Find(
new object[] { ALedgerNumber, ABudgetRow.Year, ABudgetRow.AccountCode, ABudgetRow.CostCentreCode });
if (glmIndex != -1)
{
AGeneralLedgerMasterRow glmRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.DefaultView[glmIndex].Row;
List <ABudgetPeriodRow> budgetPeriods = new List <ABudgetPeriodRow>();
for (int Period = 1; Period <= GLPostingDS.ALedger[0].NumberOfAccountingPeriods; Period++)
{
AGeneralLedgerMasterPeriodRow GeneralLedgerMasterPeriodRow =
(AGeneralLedgerMasterPeriodRow)GLPostingDS.AGeneralLedgerMasterPeriod.Rows.Find(
new object[] { glmRow.GlmSequence, Period });
ABudgetPeriodRow budgetPeriodRow = FBudgetTDS.ABudgetPeriod.NewRowTyped(true);
budgetPeriodRow.PeriodNumber = Period;
budgetPeriodRow.BudgetSequence = ABudgetRow.BudgetSequence;
// use negative amount for unposting
budgetPeriodRow.BudgetBase = -1 * GeneralLedgerMasterPeriodRow.BudgetBase;
// do not add to the budgetperiod table, but to our local list
budgetPeriods.Add(budgetPeriodRow);
}
PostBudget(ALedgerNumber, ABudgetRow, budgetPeriods);
}
ABudgetRow.BudgetStatus = false; //i.e. unposted
return(true);
}
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);
}
/// <summary>
/// Import the budget from a CSV file
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ACurrentBudgetYear"></param>
/// <param name="ACSVFileName"></param>
/// <param name="AFdlgSeparator"></param>
/// <param name="AImportDS"></param>
/// <param name="AVerificationResult"></param>
/// <returns>Total number of records imported and number of which updated as the fractional part</returns>
private static decimal ImportBudgetFromCSV(Int32 ALedgerNumber,
Int32 ACurrentBudgetYear,
string ACSVFileName,
string[] AFdlgSeparator,
ref BudgetTDS AImportDS,
ref TVerificationResultCollection AVerificationResult)
{
StreamReader DataFile = new StreamReader(ACSVFileName, System.Text.Encoding.Default);
string Separator = AFdlgSeparator[0];
string DateFormat = AFdlgSeparator[1];
string NumberFormat = AFdlgSeparator[2];
FCultureInfoNumberFormat = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE");
CultureInfo MyCultureInfoDate = new CultureInfo("en-GB");
MyCultureInfoDate.DateTimeFormat.ShortDatePattern = DateFormat;
// To store the From and To currencies
// Use an array to store these to make for easy
// inverting of the two currencies when calculating
// the inverse value.
//string currentBudgetVal = string.Empty;
//string mess = string.Empty;
string CostCentre = string.Empty;
string Account = string.Empty;
string budgetType = string.Empty;
string budgetYearString = string.Empty;
int budgetYear = 0;
Int32 numPeriods = TAccountingPeriodsWebConnector.GetNumberOfPeriods(ALedgerNumber);
decimal[] BudgetPeriods = new decimal[numPeriods];
int YearForBudgetRevision = 0;
int BdgRevision = 0; //not currently implementing versioning so always zero
decimal rowNumber = 0;
decimal duplicateRowNumber = 0;
while (!DataFile.EndOfStream)
{
decimal totalBudgetRowAmount = 0;
try
{
string Line = DataFile.ReadLine();
CostCentre = StringHelper.GetNextCSV(ref Line, Separator, false).ToString();
if (CostCentre == "Cost Centre")
{
//Read the next line
Line = DataFile.ReadLine();
CostCentre = StringHelper.GetNextCSV(ref Line, Separator, false).ToString();
}
//Increment row number
rowNumber++;
//Convert separator to a char
// char Sep = Separator[0];
//Turn current line into string array of column values
// string[] CsvColumns = Line.Split(Sep);
//int NumCols = CsvColumns.Length;
//If number of columns is not 4 then import csv file is wrongly formed.
// if (NumCols != 24)
// {
// AVerificationResult. MessageBox.Show(Catalog.GetString("Failed to import the CSV budget file:\r\n\r\n" +
// " " + ADataFilename + "\r\n\r\n" +
// "It contains " + NumCols.ToString() + " columns. " +
// ), AImportMode + " Exchange Rates Import Error");
// return;
// }
//Read the values for the current line
Account = StringHelper.GetNextCSV(ref Line, Separator, false).ToString();
budgetType = StringHelper.GetNextCSV(ref Line, Separator, false).ToString().ToUpper();
budgetType = budgetType.Replace(" ", ""); //Ad hoc will become ADHOC
//Allow for variations on Inf.Base and Inf.N
if (budgetType.Contains("INF"))
{
if (budgetType.Contains("BASE"))
{
if (budgetType != MFinanceConstants.BUDGET_INFLATE_BASE)
{
budgetType = MFinanceConstants.BUDGET_INFLATE_BASE;
}
}
else if (budgetType != MFinanceConstants.BUDGET_INFLATE_N)
{
budgetType = MFinanceConstants.BUDGET_INFLATE_N;
}
}
if ((budgetType != MFinanceConstants.BUDGET_ADHOC) &&
(budgetType != MFinanceConstants.BUDGET_SAME) &&
(budgetType != MFinanceConstants.BUDGET_INFLATE_N) &&
(budgetType != MFinanceConstants.BUDGET_SPLIT) &&
(budgetType != MFinanceConstants.BUDGET_INFLATE_BASE)
)
{
throw new InvalidOperationException("Budget Type: " + budgetType + " in row: " + rowNumber.ToString() + " does not exist.");
}
//Calculate the budget Year
budgetYearString = StringHelper.GetNextCSV(ref Line, Separator, false);
YearForBudgetRevision = BudgetRevisionYearNumber(ALedgerNumber, budgetYearString);
//Add budget revision record if there's not one already.
if (AImportDS.ABudgetRevision.Rows.Find(new object[] { ALedgerNumber, YearForBudgetRevision, BdgRevision }) == null)
{
ABudgetRevisionRow BudgetRevisionRow = (ABudgetRevisionRow)AImportDS.ABudgetRevision.NewRowTyped();
BudgetRevisionRow.LedgerNumber = ALedgerNumber;
BudgetRevisionRow.Year = YearForBudgetRevision;
BudgetRevisionRow.Revision = BdgRevision;
BudgetRevisionRow.Description = "Budget Import from: " + ACSVFileName;
AImportDS.ABudgetRevision.Rows.Add(BudgetRevisionRow);
}
//Read the budgetperiod values to check if valid according to type
Array.Clear(BudgetPeriods, 0, numPeriods);
bool successfulBudgetRowProcessing = ProcessBudgetTypeImportDetails(ref Line, Separator, budgetType, ref BudgetPeriods);
for (int i = 0; i < numPeriods; i++)
{
totalBudgetRowAmount += BudgetPeriods[i];
}
if (!successfulBudgetRowProcessing)
{
throw new InvalidOperationException(String.Format(
"The budget in row {0} for Ledger: {1}, Year: {2}, Cost Centre: {3} and Account: {4}, does not have values consistent with Budget Type: {5}.",
rowNumber,
ALedgerNumber,
budgetYear,
CostCentre,
Account,
budgetType));
}
BudgetTDS MainDS = new BudgetTDS();
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
ABudgetAccess.LoadByUniqueKey(MainDS, ALedgerNumber, YearForBudgetRevision, BdgRevision, CostCentre, Account, transaction);
//TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey
});
//Check to see if the budget combination already exists:
if (MainDS.ABudget.Count > 0)
{
ABudgetRow BR2 = (ABudgetRow)MainDS.ABudget.Rows[0];
int BTSeq = BR2.BudgetSequence;
ABudgetRow BdgTRow = (ABudgetRow)AImportDS.ABudget.Rows.Find(new object[] { BTSeq });
if (BdgTRow != null)
{
duplicateRowNumber++;
BdgTRow.BeginEdit();
//Edit the new budget row
BdgTRow.BudgetTypeCode = budgetType;
BdgTRow.EndEdit();
ABudgetPeriodRow BPRow = null;
for (int i = 0; i < numPeriods; i++)
{
BPRow = (ABudgetPeriodRow)AImportDS.ABudgetPeriod.Rows.Find(new object[] { BTSeq, i + 1 });
if (BPRow != null)
{
BPRow.BeginEdit();
BPRow.BudgetBase = BudgetPeriods[i];
BPRow.EndEdit();
}
BPRow = null;
}
}
}
else
{
//Add the new budget row
ABudgetRow BudgetRow = (ABudgetRow)AImportDS.ABudget.NewRowTyped();
int newSequence = -1 * (AImportDS.ABudget.Rows.Count + 1);
BudgetRow.BudgetSequence = newSequence;
BudgetRow.LedgerNumber = ALedgerNumber;
BudgetRow.Year = YearForBudgetRevision;
BudgetRow.Revision = BdgRevision;
BudgetRow.CostCentreCode = CostCentre;
BudgetRow.AccountCode = Account;
BudgetRow.BudgetTypeCode = budgetType;
AImportDS.ABudget.Rows.Add(BudgetRow);
//Add the budget periods
for (int i = 0; i < numPeriods; i++)
{
ABudgetPeriodRow BudgetPeriodRow = (ABudgetPeriodRow)AImportDS.ABudgetPeriod.NewRowTyped();
BudgetPeriodRow.BudgetSequence = newSequence;
BudgetPeriodRow.PeriodNumber = i + 1;
BudgetPeriodRow.BudgetBase = BudgetPeriods[i];
AImportDS.ABudgetPeriod.Rows.Add(BudgetPeriodRow);
}
}
}
catch (Exception)
{
throw;
}
}
DataFile.Close();
if (duplicateRowNumber > 0)
{
//fractional part is the number of updates divided by 10000
if (duplicateRowNumber < 10000)
{
rowNumber += (duplicateRowNumber / 10000);
}
}
return(rowNumber);
}
/// <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"];
// }
// }
//}
}
