public static BudgetTDS LoadBudget(Int32 ALedgerNumber)
{
BudgetTDS MainDS = new BudgetTDS();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
//TODO: need to filter on Year
ABudgetAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
ABudgetRevisionAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
//TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey
ABudgetPeriodAccess.LoadAll(MainDS, Transaction);
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
// ABudgetPeriodTable BudgetPeriodTable = new ABudgetPeriodTable();
// ABudgetPeriodRow TemplateRow = (ABudgetPeriodRow)BudgetPeriodTable.NewRow(false);
//
// TemplateRow.BudgetSequence;
// ABudgetPeriodAccess.LoadViaABudgetTemplate(MainDS, TemplateRow, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
return MainDS;
}
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));
}
public static BudgetTDS LoadBudget(Int32 ALedgerNumber)
{
BudgetTDS MainDS = new BudgetTDS();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
//TODO: need to filter on Year
ABudgetAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
ABudgetRevisionAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
//TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey
ABudgetPeriodAccess.LoadAll(MainDS, Transaction);
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
// ABudgetPeriodTable BudgetPeriodTable = new ABudgetPeriodTable();
// ABudgetPeriodRow TemplateRow = (ABudgetPeriodRow)BudgetPeriodTable.NewRow(false);
//
// TemplateRow.BudgetSequence;
// ABudgetPeriodAccess.LoadViaABudgetTemplate(MainDS, TemplateRow, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
return(MainDS);
}
public static BudgetTDS LoadBudgetForAutoGenerate(Int32 ALedgerNumber)
{
BudgetTDS FMainDS = new BudgetTDS();
TDBTransaction Transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadBudgetForAutoGenerate");
db.ReadTransaction(
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(FMainDS, ALedgerNumber, Transaction);
#region Validate Data
if ((FMainDS.ALedger == null) || (FMainDS.ALedger.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
int CurrentFinancialYear = FMainDS.ALedger[0].CurrentFinancialYear;
//Load all by Ledger/Year but none may exist
ABudgetTable BudgetTable = new ABudgetTable();
ABudgetRow TemplateRow = (ABudgetRow)BudgetTable.NewRow();
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.Year = CurrentFinancialYear;
StringCollection Operators = StringHelper.InitStrArr(new string[] { "=", "=" });
StringCollection OrderList = new StringCollection();
OrderList.Add("ORDER BY");
OrderList.Add(ABudgetTable.GetCostCentreCodeDBName() + " ASC");
OrderList.Add(ABudgetTable.GetAccountCodeDBName() + " ASC");
ABudgetAccess.LoadUsingTemplate(FMainDS, TemplateRow, Operators, null, Transaction, OrderList, 0, 0);
ABudgetPeriodAccess.LoadViaABudgetTemplate(FMainDS, TemplateRow, Operators, null, Transaction, OrderList, 0, 0);
ABudgetRevisionAccess.LoadByPrimaryKey(FMainDS, ALedgerNumber, CurrentFinancialYear, 0, Transaction);
ABudgetTypeAccess.LoadAll(FMainDS, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
FMainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
FMainDS.RemoveEmptyTables();
return(FMainDS);
}
/// <summary>
/// test the budget autogeneration
/// </summary>
private BudgetTDS LoadData()
{
BudgetTDS MainDS = new BudgetTDS();
MainDS.Merge(TBudgetAutoGenerateWebConnector.LoadBudgetForAutoGenerate(FLedgerNumber));
//Not needed
MainDS.RemoveTable("AGeneralLedgerMasterPeriod");
return(MainDS);
}
public static TSubmitChangesResult SaveBudget(ref BudgetTDS AInspectDS)
{
if (AInspectDS != null)
{
BudgetTDSAccess.SubmitChanges(AInspectDS);
return(TSubmitChangesResult.scrOK);
}
return(TSubmitChangesResult.scrError);
}
/// <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(BudgetTDS FMainDS, 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);
}
public void T1_AutoGenerationLoadData()
{
BudgetTDS MainDS = LoadData();
string emptyTables = string.Empty;
foreach (DataTable tb in MainDS.Tables)
{
if (MainDS.Tables[tb.TableName].Rows.Count == 0)
{
emptyTables += tb.TableName + "; ";
}
}
Assert.IsEmpty(emptyTables, "Empty Budget Autogeneration Tables: " + emptyTables);
}
public void T2_AutoGenerationGenBudget()
{
BudgetTDS MainDS = LoadData();
int budgetSequence = MainDS.ABudget.Count > 0 ? MainDS.ABudget[0].BudgetSequence : 0;
string forecastType = MFinanceConstants.FORECAST_TYPE_BUDGET;
if (MainDS.ABudget.Count == 0)
{
return;
}
Assert.IsTrue(TBudgetAutoGenerateWebConnector.GenBudgetForNextYear(FLedgerNumber,
budgetSequence,
forecastType), "Budget Autogenerate failed!");
}
public static decimal SetBudgetPeriodBaseAmount(BudgetTDS FMainDS, 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);
}
public void T1_AutoGenerationLoadData()
{
FMainDS = new BudgetTDS();
FMainDS.Merge(TBudgetAutoGenerateWebConnector.LoadBudgetForAutoGenerate(FLedgerNumber));
//Not needed
FMainDS.RemoveTable("AGeneralLedgerMasterPeriod");
string emptyTables = string.Empty;
foreach (DataTable tb in FMainDS.Tables)
{
if (FMainDS.Tables[tb.TableName].Rows.Count == 0)
{
emptyTables += tb.TableName + "; ";
}
}
Assert.IsEmpty(emptyTables, "Empty Budget Autogeneration Tables: " + emptyTables);
}
public static int ExportBudgets(Int32 ALedgerNumber,
string ACSVFileName,
string[] AFdlgSeparator,
ref string AFileContents,
ref BudgetTDS AExportDS,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = null;
if (AExportDS != null)
{
int retVal = ExportBudgetToCSV(ALedgerNumber,
ACSVFileName,
AFdlgSeparator,
ref AFileContents,
ref AExportDS,
ref AVerificationResult);
return(retVal);
}
return(0);
}
public static decimal ImportBudgets(Int32 ALedgerNumber,
Int32 ACurrentBudgetYear,
string ACSVFileName,
string[] AFdlgSeparator,
ref BudgetTDS AImportDS,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = null;
if (AImportDS != null)
{
decimal retVal = ImportBudgetFromCSV(ALedgerNumber,
ACurrentBudgetYear,
ACSVFileName,
AFdlgSeparator,
ref AImportDS,
ref AVerificationResult);
return(retVal);
}
return(0);
}
public void T1_AutoGenerationLoadData()
{
FMainDS = new BudgetTDS();
FMainDS.Merge(TBudgetAutoGenerateWebConnector.LoadBudgetForAutoGenerate(FLedgerNumber));
//Not needed
FMainDS.RemoveTable("AGeneralLedgerMasterPeriod");
string emptyTables = string.Empty;
foreach (DataTable tb in FMainDS.Tables)
{
if (FMainDS.Tables[tb.TableName].Rows.Count == 0)
{
emptyTables += tb.TableName + "; ";
}
}
Assert.IsEmpty(emptyTables, "Empty Budget Autogeneration Tables: " + emptyTables);
}
/// <summary>
/// Import the budget from a CSV file
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ACurrentBudgetYear"></param>
/// <param name="AImportString"></param>
/// <param name="ACSVFileName"></param>
/// <param name="AFdlgSeparator"></param>
/// <param name="AImportDS"></param>
/// <param name="ARecordsUpdated"></param>
/// <param name="AFailedRows"></param>
/// <param name="AVerificationResult"></param>
/// <returns>Total number of records imported and number of which updated as the fractional part</returns>
private static Int32 ImportBudgetFromCSV(Int32 ALedgerNumber,
Int32 ACurrentBudgetYear,
string AImportString,
string ACSVFileName,
string[] AFdlgSeparator,
ref BudgetTDS AImportDS,
ref Int32 ARecordsUpdated,
ref Int32 AFailedRows,
ref TVerificationResultCollection AVerificationResult)
{
StringReader sr = new StringReader(AImportString);
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;
ACostCentreTable CostCentreTable = null;
AAccountTable AccountTable = null;
string CostCentre = string.Empty;
string Account = string.Empty;
string BudgetTypeUpper = string.Empty;
string BudgetYearStringUpper = string.Empty;
int BudgetYearNumber = 0;
int BdgRevision = 0; //not currently implementing versioning so always zero
int NumPeriods = TAccountingPeriodsWebConnector.GetNumberOfPeriods(ALedgerNumber);
decimal[] BudgetPeriods = new decimal[NumPeriods];
int RowNumber = 0;
AFailedRows = 0;
ABudgetTable BudgetTableExistingAndImported = new ABudgetTable();
// Go round a loop reading the file line by line
string ImportLine = sr.ReadLine();
while (ImportLine != null)
{
decimal totalBudgetRowAmount = 0;
try
{
CostCentre = StringHelper.GetNextCSV(ref ImportLine, Separator, false).ToString();
//Check if header row exists
if ((CostCentre == "Cost Centre") || string.IsNullOrEmpty(ImportLine))
{
continue;
}
//Increment row number
RowNumber++;
//Read the values for the current line
//Account
Account = StringHelper.GetNextCSV(ref ImportLine, Separator, false).ToString();
//BudgetType
BudgetTypeUpper = StringHelper.GetNextCSV(ref ImportLine, Separator, false).ToString().ToUpper();
BudgetTypeUpper = BudgetTypeUpper.Replace(" ", ""); //Ad hoc will become ADHOC
//Allow for variations on Inf.Base and Inf.N
if (BudgetTypeUpper.Contains("INF"))
{
if (BudgetTypeUpper.Contains("BASE"))
{
if (BudgetTypeUpper != MFinanceConstants.BUDGET_INFLATE_BASE)
{
BudgetTypeUpper = MFinanceConstants.BUDGET_INFLATE_BASE;
}
}
else if (BudgetTypeUpper != MFinanceConstants.BUDGET_INFLATE_N)
{
BudgetTypeUpper = MFinanceConstants.BUDGET_INFLATE_N;
}
}
//BudgetYear
BudgetYearStringUpper = (StringHelper.GetNextCSV(ref ImportLine, Separator, false)).ToUpper();
//Check validity of CSV file line values
if (!ValidateKeyBudgetFields(ALedgerNumber,
RowNumber,
ref CostCentreTable,
ref AccountTable,
CostCentre,
Account,
BudgetTypeUpper,
BudgetYearStringUpper,
ref AVerificationResult))
{
AFailedRows++;
continue;
}
//Read the budgetperiod values to check if valid according to type
Array.Clear(BudgetPeriods, 0, NumPeriods);
if (!ProcessBudgetTypeImportDetails(RowNumber,
ref ImportLine,
Separator,
BudgetTypeUpper,
ref BudgetPeriods,
BudgetYearStringUpper,
CostCentre,
Account,
ref AVerificationResult))
{
continue;
}
//Calculate the budget Year
BudgetYearNumber = GetBudgetYearNumber(ALedgerNumber, BudgetYearStringUpper);
//Add budget revision record if there's not one already.
if (AImportDS.ABudgetRevision.Rows.Find(new object[] { ALedgerNumber, BudgetYearNumber, BdgRevision }) == null)
{
ABudgetRevisionRow BudgetRevisionRow = (ABudgetRevisionRow)AImportDS.ABudgetRevision.NewRowTyped();
BudgetRevisionRow.LedgerNumber = ALedgerNumber;
BudgetRevisionRow.Year = BudgetYearNumber;
BudgetRevisionRow.Revision = BdgRevision;
BudgetRevisionRow.Description = "Budget Import from: " + ACSVFileName;
AImportDS.ABudgetRevision.Rows.Add(BudgetRevisionRow);
}
for (int i = 0; i < NumPeriods; i++)
{
totalBudgetRowAmount += BudgetPeriods[i];
}
BudgetTDS mainDS = new BudgetTDS();
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
//TODO: need to filter on ABudgetPeriod using LoadViaBudget or LoadViaUniqueKey
ABudgetAccess.LoadByUniqueKey(mainDS, ALedgerNumber, BudgetYearNumber, BdgRevision, CostCentre, Account, transaction);
#region Validate Data
if ((mainDS.ABudget != null) && (mainDS.ABudget.Count > 1))
{
//TODO: update when budget revisioning is added
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Duplicates unique keys exist in the Budget table for Ledger: {1} Year: '{2}' ({3}), Cost Centre: '{4}' & Account: '{5}'!"),
Utilities.GetMethodName(true),
ALedgerNumber,
BudgetYearStringUpper,
BudgetYearNumber,
CostCentre,
Account));
}
#endregion Validate Data
});
//Check to see if the budget combination already exists:
if (mainDS.ABudget.Count > 0)
{
//Will only be one row
ABudgetRow br2 = (ABudgetRow)mainDS.ABudget.Rows[0];
//Check if exists in AImportDS
int bdgSeq = br2.BudgetSequence;
//Add to duplicates-checking table
//If not in saved budget table, check if already been imported earlier in the file
DataRow duplicateBudgetRow = BudgetTableExistingAndImported.Rows.Find(new object[] { bdgSeq });
if (duplicateBudgetRow != null)
{
//TODO: update when budget revisioning is added
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Row: " + RowNumber.ToString("0000")),
String.Format(Catalog.GetString(
" This budget import row (Year: '{0}', Cost Centre: '{1}', Account: '{2}') is repeated in the import file!"),
BudgetYearStringUpper,
CostCentre,
Account),
TResultSeverity.Resv_Noncritical));
AFailedRows++;
continue;
}
BudgetTableExistingAndImported.ImportRow(br2);
ABudgetRow bdgRow = (ABudgetRow)AImportDS.ABudget.Rows.Find(new object[] { bdgSeq });
if (bdgRow != null)
{
bool rowUpdated = false;
if (bdgRow.BudgetTypeCode != BudgetTypeUpper)
{
rowUpdated = true;
bdgRow.BudgetTypeCode = BudgetTypeUpper;
}
ABudgetPeriodRow BPRow = null;
for (int i = 0; i < NumPeriods; i++)
{
BPRow = (ABudgetPeriodRow)AImportDS.ABudgetPeriod.Rows.Find(new object[] { bdgSeq, i + 1 });
if ((BPRow != null) && (BPRow.BudgetBase != BudgetPeriods[i]))
{
rowUpdated = true;
BPRow.BudgetBase = BudgetPeriods[i];
}
BPRow = null;
}
if (rowUpdated)
{
ARecordsUpdated++;
}
}
}
else
{
//If not in saved budget table, check if already been imported earlier in the file
DataRow[] duplicateBudgetRows =
BudgetTableExistingAndImported.Select(String.Format("{0}={1} And {2}={3} And {4}={5} And {6}='{7}' And {8}='{9}'",
ABudgetTable.GetLedgerNumberDBName(),
ALedgerNumber,
ABudgetTable.GetYearDBName(),
BudgetYearNumber,
ABudgetTable.GetRevisionDBName(),
BdgRevision,
ABudgetTable.GetCostCentreCodeDBName(),
CostCentre,
ABudgetTable.GetAccountCodeDBName(),
Account));
if ((duplicateBudgetRows != null) && (duplicateBudgetRows.Length > 0))
{
//TODO: update when budget revisioning is added
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Row: " + RowNumber.ToString("0000")),
String.Format(Catalog.GetString(
"This budget import row (Year: '{0}', Cost Centre: '{1}', Account: '{2}') is repeated in the import file!"),
BudgetYearStringUpper,
CostCentre,
Account),
TResultSeverity.Resv_Noncritical));
AFailedRows++;
continue;
}
//Add the new budget row
ABudgetRow BudgetRow = (ABudgetRow)AImportDS.ABudget.NewRowTyped();
int newSequence = Convert.ToInt32(TSequenceWebConnector.GetNextSequence(TSequenceNames.seq_budget)); // -1 * (AImportDS.ABudget.Rows.Count + 1);
BudgetRow.BudgetSequence = newSequence;
BudgetRow.LedgerNumber = ALedgerNumber;
BudgetRow.Year = BudgetYearNumber;
BudgetRow.Revision = BdgRevision;
BudgetRow.CostCentreCode = CostCentre;
BudgetRow.AccountCode = Account;
BudgetRow.BudgetTypeCode = BudgetTypeUpper;
AImportDS.ABudget.Rows.Add(BudgetRow);
//Add to import table to check for later duplicates
BudgetTableExistingAndImported.ImportRow(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 ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
finally
{
// Read the next line
ImportLine = sr.ReadLine();
}
}
//DataFile.Close();
return RowNumber;
}
/// <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>
/// Imports budgets from a file
/// </summary>
/// <param name="ASelectedBudgetYear"></param>
/// <param name="AMainDS"></param>
public void ImportBudget(int ASelectedBudgetYear, ref BudgetTDS AMainDS)
{
TVerificationResultCollection Messages = new TVerificationResultCollection();
int NumBudgetsToImport = 0;
int NumRecsUpdated = 0;
int NumRowsFailed = 0;
if (FPetraUtilsObject.HasChanges)
{
// saving failed, therefore do not try to post
MessageBox.Show(Catalog.GetString("Please save before trying to import!"), Catalog.GetString(
"Failure"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
String DateFormatString = TUserDefaults.GetStringDefault("Imp Date", "MDY");
OpenFileDialog OFDialog = new OpenFileDialog();
string ExportPath = TClientSettings.GetExportPath();
string FullPath = TUserDefaults.GetStringDefault("Imp Filename",
ExportPath + Path.DirectorySeparatorChar + "import.csv");
TImportExportDialogs.SetOpenFileDialogFilePathAndName(OFDialog, FullPath, ExportPath);
OFDialog.Title = Catalog.GetString("Import budget(s) from csv file");
OFDialog.Filter = Catalog.GetString("Budget files (*.csv)|*.csv");
String ImportOptions = TUserDefaults.GetStringDefault("Imp Options", ";" + TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN);
// This call fixes Windows7 Open File Dialogs. It must be the line before ShowDialog()
TWin7FileOpenSaveDialog.PrepareDialog(Path.GetFileName(FullPath));
if (OFDialog.ShowDialog() == DialogResult.OK)
{
FdlgSeparator = new TDlgSelectCSVSeparator(false);
try
{
FParentForm.UseWaitCursor = true;
Boolean fileCanOpen = FdlgSeparator.OpenCsvFile(OFDialog.FileName);
if (!fileCanOpen)
{
throw new Exception(String.Format(Catalog.GetString("File {0} cannot be opened."), OFDialog.FileName));
}
FdlgSeparator.DateFormat = DateFormatString;
if (ImportOptions.Length > 1)
{
FdlgSeparator.NumberFormat = ImportOptions.Substring(1);
}
FdlgSeparator.SelectedSeparator = ImportOptions.Substring(0, 1);
if (FdlgSeparator.ShowDialog() == DialogResult.OK)
{
string[] FdlgSeparatorVal = new string[] {
FdlgSeparator.SelectedSeparator, FdlgSeparator.DateFormat, FdlgSeparator.NumberFormat
};
// read contents of file
string ImportString = File.ReadAllText(OFDialog.FileName);
//TODO return the budget from the year, and -99 for fail
NumBudgetsToImport = TRemote.MFinance.Budget.WebConnectors.ImportBudgets(FLedgerNumber,
ASelectedBudgetYear,
ImportString,
OFDialog.FileName,
FdlgSeparatorVal,
ref AMainDS,
out NumRecsUpdated,
out NumRowsFailed,
out Messages);
ShowMessages(Messages, NumBudgetsToImport, NumRecsUpdated, NumRowsFailed);
}
}
catch (Exception ex)
{
NumBudgetsToImport = -1;
MessageBox.Show(ex.Message, Catalog.GetString("Budget Import"), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// We save the defaults even if ok is false - because the client will probably want to try and import
// the same file again after correcting any errors
SaveUserDefaults(OFDialog, ImportOptions);
FParentForm.UseWaitCursor = false;
}
// update grid
if (NumBudgetsToImport > 0)
{
FParentForm.UseWaitCursor = true;
UpdateABudgetPeriodAmounts(AMainDS, ASelectedBudgetYear);
FUserControl.SetBudgetDefaultView();
FParentForm.UseWaitCursor = false;
FUserControl.SelectRowInGrid(1);
FPetraUtilsObject.SetChangedFlag();
}
else if (NumBudgetsToImport <= 0)
{
FUserControl.SelectRowInGrid(1);
}
}
}
public static bool GenBudgetForNextYear(int ALedgerNumber, int ABudgetSeq, string AForecastType)
{
BudgetTDS FMainDS = LoadBudgetForAutoGenerate(ALedgerNumber);
bool retVal = false;
decimal budgetSum;
decimal priorAmount = 0;
decimal afterAmount = 0;
int periodOfChange = 0;
int gLMSequenceThisYear = 0;
int gLMSequenceLastYear = 0;
ALedgerTable ledgerTable = FMainDS.ALedger;
ALedgerRow ledgerRow = (ALedgerRow)ledgerTable.Rows[0];
int currentFinancialYear = ledgerRow.CurrentFinancialYear;
int currentPeriod = ledgerRow.CurrentPeriod;
int numAccPeriods = ledgerRow.NumberOfAccountingPeriods;
ABudgetTable budgetTable = FMainDS.ABudget;
ABudgetRow budgetRow = (ABudgetRow)budgetTable.Rows.Find(new object[] { ABudgetSeq });
#region Validate Method Parameters
if ((budgetRow == null) || (budgetRow.LedgerNumber != ledgerRow.LedgerNumber) || (budgetRow.Year != currentFinancialYear))
{
TLogging.Log("GenBudgetForNextYear: either could not find budget sequence " + ABudgetSeq.ToString() +
"or the sequence does not apply to ledger " + ledgerRow.LedgerNumber.ToString() +
"or to ledger current year " + currentFinancialYear.ToString());
return(retVal);
}
if (ALedgerNumber != ledgerRow.LedgerNumber)
{
TLogging.Log("GenBudgetForNextYear: method parameter ALedgerNumber is " + ALedgerNumber.ToString() +
" but the data set expected the ledger to be " + ledgerRow.LedgerNumber.ToString());
}
#endregion
string accountCode = budgetRow.AccountCode;
string costCentreCode = budgetRow.CostCentreCode;
gLMSequenceThisYear = TCommonBudgetMaintain.GetGLMSequenceForBudget(ALedgerNumber,
accountCode,
costCentreCode,
currentFinancialYear);
gLMSequenceLastYear = TCommonBudgetMaintain.GetGLMSequenceForBudget(ALedgerNumber,
accountCode,
costCentreCode,
(currentFinancialYear - 1));
try
{
//Update the budget status
budgetRow.BeginEdit();
budgetRow.BudgetStatus = false;
budgetRow.EndEdit();
string budgetType = budgetRow.BudgetTypeCode;
decimal budgetAmount = 0;
decimal actualAmount = 0;
bool validBudgetType = true;
switch (budgetType)
{
case MFinanceConstants.BUDGET_ADHOC:
case MFinanceConstants.BUDGET_INFLATE_BASE:
for (int i = 1; i < currentPeriod; i++)
{
//Set budget period
actualAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
i,
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
false,
MFinanceConstants.CURRENCY_BASE);
SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, i, actualAmount);
}
for (int j = currentPeriod; j <= MFinanceConstants.MAX_PERIODS; j++)
{
if (AForecastType == MFinanceConstants.FORECAST_TYPE_BUDGET)
{
budgetAmount =
Math.Round(TCommonBudgetMaintain.GetBudget(gLMSequenceThisYear, -1, j, numAccPeriods,
false,
MFinanceConstants.CURRENCY_BASE));
SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, j, budgetAmount);
}
else
{
actualAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
j,
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
false,
MFinanceConstants.CURRENCY_BASE);
SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, j, actualAmount);
}
}
break;
case MFinanceConstants.BUDGET_SAME: //because this case has no code it will fall through to the next case until it finds code.
case MFinanceConstants.BUDGET_SPLIT:
if ((currentPeriod - 1) != 0)
{
budgetSum =
TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceThisYear,
-1,
(currentPeriod - 1),
numAccPeriods,
currentFinancialYear,
currentFinancialYear,
true,
MFinanceConstants.CURRENCY_BASE);
}
else
{
budgetSum = 0;
}
if (AForecastType == MFinanceConstants.FORECAST_TYPE_BUDGET)
{
for (int i = currentPeriod; i <= numAccPeriods; i++)
{
budgetSum += TCommonBudgetMaintain.GetBudget(gLMSequenceThisYear,
-1,
i,
numAccPeriods,
false,
MFinanceConstants.CURRENCY_BASE);
}
}
else
{
if (currentPeriod > 1)
{
budgetSum += TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
numAccPeriods,
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
true,
MFinanceConstants.CURRENCY_BASE) -
TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
(currentPeriod - 1),
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
true,
MFinanceConstants.CURRENCY_BASE);
}
else
{
budgetSum += TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
numAccPeriods,
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
true,
MFinanceConstants.CURRENCY_BASE);
}
}
budgetSum = budgetSum / numAccPeriods;
for (int i = 1; i <= numAccPeriods; i++)
{
SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, i, Math.Round(budgetSum));
}
break;
case MFinanceConstants.BUDGET_INFLATE_N:
for (int i = 1; i <= numAccPeriods; i++)
{
if (GetBudgetPeriodAmount(FMainDS, ABudgetSeq, i) != GetBudgetPeriodAmount(FMainDS, ABudgetSeq, 1))
{
periodOfChange = i - 1;
break;
}
}
/* Calculate average prior to change and after change. */
if (periodOfChange < (currentPeriod - 1))
{
priorAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceThisYear,
-1,
periodOfChange,
numAccPeriods,
currentFinancialYear,
currentFinancialYear,
true,
MFinanceConstants.CURRENCY_BASE);
afterAmount =
TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceThisYear,
-1,
(currentPeriod - 1),
numAccPeriods,
currentFinancialYear,
currentFinancialYear,
true,
MFinanceConstants.CURRENCY_BASE) -
TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceThisYear,
-1,
periodOfChange + 1,
numAccPeriods,
currentFinancialYear,
currentFinancialYear,
true,
MFinanceConstants.CURRENCY_BASE);
if (AForecastType == MFinanceConstants.FORECAST_TYPE_BUDGET)
{
for (int i = currentPeriod; i <= numAccPeriods; i++)
{
afterAmount += TCommonBudgetMaintain.GetBudget(gLMSequenceThisYear,
-1,
i,
numAccPeriods,
false,
MFinanceConstants.CURRENCY_BASE);
}
}
else
{
afterAmount += TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
numAccPeriods,
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
true,
MFinanceConstants.CURRENCY_BASE) -
TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
currentPeriod,
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
true,
MFinanceConstants.CURRENCY_BASE);
}
}
else /* Period of change HAS NOT taken place. */
{
if ((currentPeriod - 1) != 0)
{
priorAmount =
TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceThisYear,
-1,
(currentPeriod - 1),
numAccPeriods,
currentFinancialYear,
currentFinancialYear,
true,
MFinanceConstants.CURRENCY_BASE);
}
else
{
priorAmount = 0;
}
if (AForecastType == MFinanceConstants.FORECAST_TYPE_BUDGET)
{
for (int i = currentPeriod; i <= periodOfChange; i++)
{
priorAmount += TCommonBudgetMaintain.GetBudget(gLMSequenceThisYear,
-1,
i,
numAccPeriods,
false,
MFinanceConstants.CURRENCY_BASE);
}
}
else
{
priorAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
periodOfChange,
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
true,
MFinanceConstants.CURRENCY_BASE) -
TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
currentPeriod,
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
true,
MFinanceConstants.CURRENCY_BASE);
}
if (AForecastType == MFinanceConstants.FORECAST_TYPE_BUDGET)
{
for (int i = (periodOfChange + 1); i <= numAccPeriods; i++)
{
afterAmount += TCommonBudgetMaintain.GetBudget(gLMSequenceThisYear,
-1,
i,
numAccPeriods,
false,
MFinanceConstants.CURRENCY_BASE);
}
}
else
{
afterAmount = TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
numAccPeriods,
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
true,
MFinanceConstants.CURRENCY_BASE) -
TCommonBudgetMaintain.GetActual(ALedgerNumber,
gLMSequenceLastYear,
gLMSequenceThisYear,
(periodOfChange + 1),
numAccPeriods,
currentFinancialYear,
(currentFinancialYear - 1),
true,
MFinanceConstants.CURRENCY_BASE);
}
/* Dividing after sum by prior sum gives rate of inflation. */
priorAmount = priorAmount / periodOfChange;
afterAmount = afterAmount / (numAccPeriods - periodOfChange);
for (int i = 1; i <= periodOfChange; i++)
{
SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, i, Math.Round(priorAmount, 0));
}
for (int i = (periodOfChange + 1); i <= numAccPeriods; i++)
{
SetBudgetPeriodBaseAmount(FMainDS, ABudgetSeq, i, Math.Round(afterAmount, 0));
}
}
break;
default:
validBudgetType = false;
break;
}
if (!validBudgetType)
{
throw new InvalidOperationException(String.Format("Invalid budget type of: {0} for Budget Seq.: {1}",
budgetType,
budgetRow.BudgetSequence));
}
retVal = true;
}
catch (Exception)
{
throw;
}
return(retVal);
}
/// <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"];
// }
// }
//}
}
public static int ExportBudgets(Int32 ALedgerNumber,
string ACSVFileName,
string[] AFdlgSeparator,
ref string AFileContents,
ref BudgetTDS AExportDS,
out TVerificationResultCollection AVerificationResult)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
else if (ACSVFileName.Length == 0)
{
throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The CSV File Name is missing!"),
Utilities.GetMethodName(true)));
}
else if (AFdlgSeparator.Length == 0)
{
throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - Information on how to parse the CSV file is missing!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
AVerificationResult = null;
if (AExportDS != null)
{
int retVal = ExportBudgetToCSV(ALedgerNumber,
ACSVFileName,
AFdlgSeparator,
ref AFileContents,
ref AExportDS,
ref AVerificationResult);
return retVal;
}
return 0;
}
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 static TSubmitChangesResult SaveBudget(ref BudgetTDS AInspectDS)
{
if (AInspectDS != null)
{
BudgetTDSAccess.SubmitChanges(AInspectDS);
return TSubmitChangesResult.scrOK;
}
return TSubmitChangesResult.scrError;
}
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);
}
/// <summary>
/// Imports budgets from a file
/// </summary>
/// <param name="ACurrentBudgetYear"></param>
/// <param name="AMainDS"></param>
public void ImportBudget(int ACurrentBudgetYear, ref BudgetTDS AMainDS)
{
TVerificationResultCollection Messages = new TVerificationResultCollection();
int BudgetsImported = 0;
int BudgetsAdded = 0;
int BudgetsUpdated = 0;
int BudgetsFailed = 0;
if (FPetraUtilsObject.HasChanges)
{
// saving failed, therefore do not try to post
MessageBox.Show(Catalog.GetString("Please save before trying to import!"), Catalog.GetString(
"Failure"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
String DateFormatString = TUserDefaults.GetStringDefault("Imp Date", "MDY");
OpenFileDialog OFDialog = new OpenFileDialog();
string ExportPath = TClientSettings.GetExportPath();
string FullPath = TUserDefaults.GetStringDefault("Imp Filename",
ExportPath + Path.DirectorySeparatorChar + "import.csv");
TImportExportDialogs.SetOpenFileDialogFilePathAndName(OFDialog, FullPath, ExportPath);
OFDialog.Title = Catalog.GetString("Import budget(s) from CSV file");
OFDialog.Filter = Catalog.GetString("Text Files(*.txt) | *.txt | Delimited Files(*.csv) | *.csv");
String ImportOptions = TUserDefaults.GetStringDefault("Imp Options", ";" + TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN);
// This call fixes Windows7 Open File Dialogs. It must be the line before ShowDialog()
TWin7FileOpenSaveDialog.PrepareDialog(Path.GetFileName(FullPath));
if (OFDialog.ShowDialog() == DialogResult.OK)
{
TFrmStatusDialog dlgStatus = new TFrmStatusDialog(FPetraUtilsObject.GetForm());
FdlgSeparator = new TDlgSelectCSVSeparator(false);
try
{
string fileTitle = OFDialog.SafeFileName;
Boolean fileCanOpen = FdlgSeparator.OpenCsvFile(OFDialog.FileName);
if (!fileCanOpen)
{
MessageBox.Show(Catalog.GetString("Unable to open file."),
Catalog.GetString("Budget Import"),
MessageBoxButtons.OK,
MessageBoxIcon.Stop);
return;
}
FdlgSeparator.DateFormat = DateFormatString;
if (ImportOptions.Length > 1)
{
FdlgSeparator.NumberFormat = ImportOptions.Substring(1);
}
FdlgSeparator.SelectedSeparator = ImportOptions.Substring(0, 1);
if (FdlgSeparator.ShowDialog() == DialogResult.OK)
{
string[] FdlgSeparatorVal = new string[] {
FdlgSeparator.SelectedSeparator, FdlgSeparator.DateFormat, FdlgSeparator.NumberFormat
};
Application.UseWaitCursor = true;
//New set of budgets to be loaded
dlgStatus.Show();
dlgStatus.Heading = Catalog.GetString("Budget Import");
dlgStatus.CurrentStatus = Catalog.GetString("Importing budgets from '" + fileTitle + "' ...");
// read contents of file
string ImportString = File.ReadAllText(OFDialog.FileName);
//TODO return the budget from the year, and -99 for fail
BudgetsImported = TRemote.MFinance.Budget.WebConnectors.ImportBudgets(FLedgerNumber,
ImportString,
OFDialog.FileName,
FdlgSeparatorVal,
ref AMainDS,
out BudgetsAdded,
out BudgetsUpdated,
out BudgetsFailed,
out Messages);
dlgStatus.Visible = false;
Application.UseWaitCursor = false;
ShowMessages(Messages, BudgetsImported, BudgetsAdded, BudgetsUpdated, BudgetsFailed);
}
// We save the defaults even if ok is false - because the client will probably want to try and import
// the same file again after correcting any errors
SaveUserDefaults(OFDialog, ImportOptions);
}
catch (Exception ex)
{
TLogging.LogException(ex, Utilities.GetMethodSignature());
throw;
}
finally
{
Application.UseWaitCursor = false;
}
// update grid
if ((BudgetsAdded + BudgetsUpdated) > 0)
{
try
{
dlgStatus.CurrentStatus = Catalog.GetString("Updating budget period data...");
dlgStatus.Visible = true;
Application.UseWaitCursor = true;
UpdateABudgetPeriodAmounts(AMainDS, ACurrentBudgetYear);
FUserControl.SetBudgetDefaultView(AMainDS);
}
finally
{
Application.UseWaitCursor = false;
dlgStatus.Close();
}
FPetraUtilsObject.SetChangedFlag();
}
else
{
dlgStatus.Close();
}
}
}
private static void LoadABudgetByYearWithCustomColumns(ref BudgetTDS AMainDS,
Int32 ALedgerNumber,
Int32 AYear,
Int32 ANumberOfPeriods,
TDBTransaction ATransaction)
{
//Parameters for SQL as strings
string prmLedgerNumber = ALedgerNumber.ToString();
string prmYear = AYear.ToString();
//Tables with alias
string BudgetTableAlias = "b";
string bBudgetTable = ABudgetTable.GetTableDBName() + " " + BudgetTableAlias;
string BudgetPeriodTableAlias = "bp";
string bpBudgetPeriodTable = ABudgetPeriodTable.GetTableDBName() + " " + BudgetPeriodTableAlias;
//Table: ABudgetTable and fields
string bLedgerNumber = BudgetTableAlias + "." + ABudgetTable.GetLedgerNumberDBName();
string bBudgetSequence = BudgetTableAlias + "." + ABudgetTable.GetBudgetSequenceDBName();
string bYear = BudgetTableAlias + "." + ABudgetTable.GetYearDBName();
string bRevision = BudgetTableAlias + "." + ABudgetTable.GetRevisionDBName();
string bCostCentreCode = BudgetTableAlias + "." + ABudgetTable.GetCostCentreCodeDBName();
string bAccountCode = BudgetTableAlias + "." + ABudgetTable.GetAccountCodeDBName();
string bBudgetTypeCode = BudgetTableAlias + "." + ABudgetTable.GetBudgetTypeCodeDBName();
string bBudgetStatus = BudgetTableAlias + "." + ABudgetTable.GetBudgetStatusDBName();
string bComment = BudgetTableAlias + "." + ABudgetTable.GetCommentDBName();
string bDateCreated = BudgetTableAlias + "." + ABudgetTable.GetDateCreatedDBName();
string bCreatedBy = BudgetTableAlias + "." + ABudgetTable.GetCreatedByDBName();
string bDateModified = BudgetTableAlias + "." + ABudgetTable.GetDateModifiedDBName();
string bModifiedBy = BudgetTableAlias + "." + ABudgetTable.GetModifiedByDBName();
string bModificationId = BudgetTableAlias + "." + ABudgetTable.GetModificationIdDBName();
//Table: ABudgetPeriodTable and fields
string bpPeriodNumber = BudgetPeriodTableAlias + "." + ABudgetPeriodTable.GetPeriodNumberDBName();
string bpBudgetBase = BudgetPeriodTableAlias + "." + ABudgetPeriodTable.GetBudgetBaseDBName();
string bpBudgetSequence = BudgetPeriodTableAlias + "." + ABudgetPeriodTable.GetBudgetSequenceDBName();
string SQLStatement =
"select " +
bBudgetSequence + ", " + bLedgerNumber + ", " + bYear + ", " +
bRevision + ", " + bCostCentreCode + ", " + bAccountCode + ", " + bBudgetTypeCode + ", " +
bBudgetStatus + ", " + bComment + ", " + bDateCreated + ", " + bCreatedBy + ", " +
bDateModified + ", " + bModifiedBy + ", " + bModificationId + ", " +
"min(case when " + bpPeriodNumber + " = 1 then " + bpBudgetBase + " end) Period01Amount, " +
"min(case when " + bpPeriodNumber + " = 2 then " + bpBudgetBase + " end) Period02Amount, " +
"min(case when " + bpPeriodNumber + " = 3 then " + bpBudgetBase + " end) Period03Amount, " +
"min(case when " + bpPeriodNumber + " = 4 then " + bpBudgetBase + " end) Period04Amount, " +
"min(case when " + bpPeriodNumber + " = 5 then " + bpBudgetBase + " end) Period05Amount, " +
"min(case when " + bpPeriodNumber + " = 6 then " + bpBudgetBase + " end) Period06Amount, " +
"min(case when " + bpPeriodNumber + " = 7 then " + bpBudgetBase + " end) Period07Amount, " +
"min(case when " + bpPeriodNumber + " = 8 then " + bpBudgetBase + " end) Period08Amount, " +
"min(case when " + bpPeriodNumber + " = 9 then " + bpBudgetBase + " end) Period09Amount, " +
"min(case when " + bpPeriodNumber + " = 10 then " + bpBudgetBase + " end) Period10Amount, " +
"min(case when " + bpPeriodNumber + " = 11 then " + bpBudgetBase + " end) Period11Amount, " +
"min(case when " + bpPeriodNumber + " = 12 then " + bpBudgetBase + " end) Period12Amount";
if (ANumberOfPeriods > 12)
{
SQLStatement += ", min(case when " + bpPeriodNumber + " = 13 then " + bpBudgetBase + " end) Period13Amount";
}
else
{
SQLStatement += ", 0 Period13Amount";
}
if (ANumberOfPeriods > 13)
{
SQLStatement += ", min(case when " + bpPeriodNumber + " = 14 then " + bpBudgetBase + " end) Period14Amount";
}
else
{
SQLStatement += ", 0 Period14Amount";
}
SQLStatement +=
" from " + bBudgetTable + " join " + bpBudgetPeriodTable +
" on " + bpBudgetSequence + " = " + bBudgetSequence +
" where " + bLedgerNumber + " = " + prmLedgerNumber +
" and " + bYear + " = " + prmYear +
" group by " +
bBudgetSequence + ", " + bLedgerNumber + ", " + bYear + ", " +
bRevision + ", " + bCostCentreCode + ", " + bAccountCode + ", " + bBudgetTypeCode + ", " +
bBudgetStatus + ", " + bComment + ", " + bDateCreated + ", " + bCreatedBy + ", " +
bDateModified + ", " + bModifiedBy + ", " + bModificationId + ";";
DBAccess.GDBAccessObj.Select(AMainDS, SQLStatement, AMainDS.ABudget.TableName, ATransaction);
}
public static BudgetTDS LoadAllBudgets(Int32 ALedgerNumber)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
#endregion Validate Arguments
BudgetTDS MainDS = new BudgetTDS();
TDBTransaction Transaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
#region Validate Data
if ((MainDS.ALedger == null) || (MainDS.ALedger.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
//Load all by Ledger but none may exist
ABudgetAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
ABudgetRevisionAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
ABudgetTable BudgetTable = new ABudgetTable();
ABudgetRow TemplateRow = (ABudgetRow)BudgetTable.NewRow();
TemplateRow.LedgerNumber = ALedgerNumber;
ABudgetPeriodAccess.LoadViaABudgetTemplate(MainDS, TemplateRow, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
return MainDS;
}
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");
TVerificationResultCollection VerificationResult;
BudgetTDS ImportDS = new BudgetTDS();
// import budget from CSV
decimal RowsImported = TBudgetMaintainWebConnector.ImportBudgets(
FLedgerNumber,
0,
budgetTestFile,
new string[] { ",", "dmy", "American" },
ref ImportDS,
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);
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction);
DBAccess.GDBAccessObj.CommitTransaction();
// 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);
Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction);
DBAccess.GDBAccessObj.ExecuteNonQuery(sqlMarkBudgetForConsolidation, Transaction);
DBAccess.GDBAccessObj.CommitTransaction();
// 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 LoadBudgets()
{
FBudgetDS = TRemote.MFinance.Budget.WebConnectors.LoadBudget(FLedgerNumber);
}
/// <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"];
// }
// }
//}
}
/// <summary>
/// Imports budgets from a file
/// </summary>
/// <param name="ASelectedBudgetYear"></param>
/// <param name="AMainDS"></param>
public void ImportBudget(int ASelectedBudgetYear, ref BudgetTDS AMainDS)
{
TVerificationResultCollection Messages = new TVerificationResultCollection();
int NumBudgetsToImport = 0;
int NumRecsUpdated = 0;
int NumRowsFailed = 0;
if (FPetraUtilsObject.HasChanges)
{
// saving failed, therefore do not try to post
MessageBox.Show(Catalog.GetString("Please save before trying to import!"), Catalog.GetString(
"Failure"), MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
String DateFormatString = TUserDefaults.GetStringDefault("Imp Date", "MDY");
OpenFileDialog OFDialog = new OpenFileDialog();
string ExportPath = TClientSettings.GetExportPath();
string FullPath = TUserDefaults.GetStringDefault("Imp Filename",
ExportPath + Path.DirectorySeparatorChar + "import.csv");
TImportExportDialogs.SetOpenFileDialogFilePathAndName(OFDialog, FullPath, ExportPath);
OFDialog.Title = Catalog.GetString("Import budget(s) from csv file");
OFDialog.Filter = Catalog.GetString("Budget files (*.csv)|*.csv");
String ImportOptions = TUserDefaults.GetStringDefault("Imp Options", ";" + TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN);
// This call fixes Windows7 Open File Dialogs. It must be the line before ShowDialog()
TWin7FileOpenSaveDialog.PrepareDialog(Path.GetFileName(FullPath));
if (OFDialog.ShowDialog() == DialogResult.OK)
{
FdlgSeparator = new TDlgSelectCSVSeparator(false);
try
{
FParentForm.UseWaitCursor = true;
Boolean fileCanOpen = FdlgSeparator.OpenCsvFile(OFDialog.FileName);
if (!fileCanOpen)
{
throw new Exception(String.Format(Catalog.GetString("File {0} cannot be opened."), OFDialog.FileName));
}
FdlgSeparator.DateFormat = DateFormatString;
if (ImportOptions.Length > 1)
{
FdlgSeparator.NumberFormat = ImportOptions.Substring(1);
}
FdlgSeparator.SelectedSeparator = ImportOptions.Substring(0, 1);
if (FdlgSeparator.ShowDialog() == DialogResult.OK)
{
string[] FdlgSeparatorVal = new string[] {
FdlgSeparator.SelectedSeparator, FdlgSeparator.DateFormat, FdlgSeparator.NumberFormat
};
// read contents of file
string ImportString = File.ReadAllText(OFDialog.FileName);
//TODO return the budget from the year, and -99 for fail
NumBudgetsToImport = TRemote.MFinance.Budget.WebConnectors.ImportBudgets(FLedgerNumber,
ASelectedBudgetYear,
ImportString,
OFDialog.FileName,
FdlgSeparatorVal,
ref AMainDS,
out NumRecsUpdated,
out NumRowsFailed,
out Messages);
ShowMessages(Messages, NumBudgetsToImport, NumRecsUpdated, NumRowsFailed);
}
}
catch (Exception ex)
{
NumBudgetsToImport = -1;
MessageBox.Show(ex.Message, Catalog.GetString("Budget Import"), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// We save the defaults even if ok is false - because the client will probably want to try and import
// the same file again after correcting any errors
SaveUserDefaults(OFDialog, ImportOptions);
FParentForm.UseWaitCursor = false;
}
// update grid
if (NumBudgetsToImport > 0)
{
FParentForm.UseWaitCursor = true;
UpdateABudgetPeriodAmounts(AMainDS, ASelectedBudgetYear);
FUserControl.SetBudgetDefaultView();
FParentForm.UseWaitCursor = false;
FUserControl.SelectRowInGrid(1);
FPetraUtilsObject.SetChangedFlag();
}
else if (NumBudgetsToImport <= 0)
{
FUserControl.SelectRowInGrid(1);
}
}
}
public static int ExportBudgets(Int32 ALedgerNumber,
string ACSVFileName,
string[] AFdlgSeparator,
ref string AFileContents,
ref BudgetTDS AExportDS,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = null;
if (AExportDS != null)
{
int retVal = ExportBudgetToCSV(ALedgerNumber,
ACSVFileName,
AFdlgSeparator,
ref AFileContents,
ref AExportDS,
ref AVerificationResult);
return retVal;
}
return 0;
}
private void LoadBudgets()
{
FBudgetDS = TRemote.MFinance.Budget.WebConnectors.LoadBudget(FLedgerNumber);
}
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?
}
public static BudgetTDS LoadBudgetsForYear(Int32 ALedgerNumber, Int32 ABudgetYear)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
else if (ABudgetYear < 0)
{
throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The Budget Year number cannot be negative!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
BudgetTDS MainDS = new BudgetTDS();
TDBTransaction Transaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, Transaction);
#region Validate Data
if ((MainDS.ALedger == null) || (MainDS.ALedger.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
int numPeriods = MainDS.ALedger[0].NumberOfAccountingPeriods;
//Load all by Year
LoadABudgetByYearWithCustomColumns(ref MainDS, ALedgerNumber, ABudgetYear, numPeriods, Transaction);
//Load budget period data
ABudgetTable BudgetTable = new ABudgetTable();
ABudgetRow TemplateRow = (ABudgetRow)BudgetTable.NewRow();
TemplateRow.Year = ABudgetYear;
ABudgetPeriodAccess.LoadViaABudgetTemplate(MainDS, TemplateRow, Transaction);
//TODO: add Budget Revision capability when decision made to add it to OP.
// Assume Revision=0 for now until implemented
ABudgetRevisionAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, ABudgetYear, 0, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
MainDS.AcceptChanges();
// Remove all Tables that were not filled with data before remoting them.
MainDS.RemoveEmptyTables();
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
return MainDS;
}
/// <summary>
/// test the budget autogeneration
/// </summary>
private BudgetTDS LoadData()
{
BudgetTDS MainDS = new BudgetTDS();
MainDS.Merge(TBudgetAutoGenerateWebConnector.LoadBudgetForAutoGenerate(FLedgerNumber));
//Not needed
MainDS.RemoveTable("AGeneralLedgerMasterPeriod");
return MainDS;
}
private TSubmitChangesResult StoreManualCode(ref BudgetTDS ASubmitChanges, out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = null;
TSubmitChangesResult TSCR = TRemote.MFinance.Budget.WebConnectors.SaveBudget(ref ASubmitChanges);
//Reset this flag if the save was successful
FRejectYearChange = !(TSCR == TSubmitChangesResult.scrOK);
return TSCR;
}
public static decimal ImportBudgets(Int32 ALedgerNumber,
Int32 ACurrentBudgetYear,
string ACSVFileName,
string[] AFdlgSeparator,
ref BudgetTDS AImportDS,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = null;
if (AImportDS != null)
{
decimal retVal = ImportBudgetFromCSV(ALedgerNumber,
ACurrentBudgetYear,
ACSVFileName,
AFdlgSeparator,
ref AImportDS,
ref AVerificationResult);
return retVal;
}
return 0;
}
public static Int32 ImportBudgets(Int32 ALedgerNumber,
Int32 ACurrentBudgetYear,
string AImportString,
string ACSVFileName,
string[] AFdlgSeparator,
ref BudgetTDS AImportDS,
out Int32 ARecordsUpdated,
out Int32 AFailedRows,
out TVerificationResultCollection AVerificationResult)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
else if (ACurrentBudgetYear < 0)
{
throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The Budget Year number cannot be negative!"),
Utilities.GetMethodName(true)));
}
else if (ACSVFileName.Length == 0)
{
throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The CSV File Name is missing!"),
Utilities.GetMethodName(true)));
}
else if (AFdlgSeparator.Length < 3)
{
throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - Information on how to parse the CSV file is missing!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
ARecordsUpdated = 0;
AFailedRows = 0;
AVerificationResult = new TVerificationResultCollection();
if (AImportDS != null)
{
int retVal = ImportBudgetFromCSV(ALedgerNumber,
ACurrentBudgetYear,
AImportString,
ACSVFileName,
AFdlgSeparator,
ref AImportDS,
ref ARecordsUpdated,
ref AFailedRows,
ref AVerificationResult);
return retVal;
}
return 0;
}
/// <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;
}
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;
}
