private void NewRowManual(ref ACorporateExchangeRateRow ARow)
{
// We just need to decide on the appropriate currency pair and then call the standard method to get a suggested rate and date
if (FPreviouslySelectedDetailRow == null)
{
// Corporate Exchange rates are not part of any ledger, so baseCurrencyOfLedger may be null...
if ((baseCurrencyOfLedger == null) || (intlCurrencyOfLedger == null) || (baseCurrencyOfLedger == intlCurrencyOfLedger))
{
ARow.FromCurrencyCode = "GBP";
ARow.ToCurrencyCode = "USD";
}
else
{
ARow.FromCurrencyCode = baseCurrencyOfLedger;
ARow.ToCurrencyCode = intlCurrencyOfLedger;
}
}
else
{
// Use the same settings as the highlighted row
ARow.FromCurrencyCode = cmbDetailFromCurrencyCode.GetSelectedString();
ARow.ToCurrencyCode = cmbDetailToCurrencyCode.GetSelectedString();
}
DateTime suggestedDate;
decimal suggestedRate;
GetSuggestedDateAndRateForCurrencyPair(ARow.FromCurrencyCode, ARow.ToCurrencyCode, out suggestedDate, out suggestedRate);
ARow.DateEffectiveFrom = suggestedDate;
ARow.RateOfExchange = suggestedRate;
// The time is always 0 for corporate exchange rate
ARow.TimeEffectiveFrom = 0;
}
private void GetDetailDataFromControlsManual(ACorporateExchangeRateRow ARow)
{
// only do this is the user has actually changed the exchange rate
if ((ARow.RowState != DataRowState.Added) &&
(Convert.ToDecimal(ARow[ACorporateExchangeRateTable.GetRateOfExchangeDBName(), DataRowVersion.Original]) != ARow.RateOfExchange))
{
// Check if we have an inverse rate for this date/time and currency pair
ACorporateExchangeRateRow mainRow = (ACorporateExchangeRateRow)FMainDS.ACorporateExchangeRate.Rows.Find(
new object[] { ARow.ToCurrencyCode, ARow.FromCurrencyCode, ARow.DateEffectiveFrom });
if ((mainRow != null) && (ARow.RateOfExchange != 0.0m))
{
// Checking to see if we have a matching rate is tricky because rounding errors mean that the inverse of an inverse
// does not always get you back where you started. So we check both ways to look for a match.
// If neither way matches we need to do an update, but if there is a match in at least one direction, we leave the other row as it is.
decimal inverseRate = Math.Round(1 / ARow.RateOfExchange, 10);
decimal inverseRateAlt = Math.Round(1 / mainRow.RateOfExchange, 10);
if ((mainRow.RateOfExchange != inverseRate) && (ARow.RateOfExchange != inverseRateAlt))
{
// Neither way matches so we must have made a change that requires an update to the inverse row
mainRow.BeginEdit();
mainRow.RateOfExchange = inverseRate;
mainRow.EndEdit();
}
}
}
}
private void ValidateDataDetailsManual(ACorporateExchangeRateRow ARow)
{
TVerificationResultCollection VerificationResultCollection = FPetraUtilsObject.VerificationResultCollection;
TSharedFinanceValidation_GLSetup.ValidateCorporateExchangeRate(this, ARow, ref VerificationResultCollection,
FPetraUtilsObject.ValidationControlsDict, FAvailableLedgers, FAlternativeFirstDayInMonth);
// In MODAL mode we can validate that the date is the same as an accounting period...
}
private bool PreDeleteManual(ACorporateExchangeRateRow ARowToDelete, ref string ADeletionQuestion)
{
ADeletionQuestion = Catalog.GetString("Are you sure you want to delete the current row?");
ADeletionQuestion += String.Format(Catalog.GetString("{0}{0}({1} to {2} effective from {3})"),
Environment.NewLine,
ARowToDelete.FromCurrencyCode,
ARowToDelete.ToCurrencyCode,
ARowToDelete.DateEffectiveFrom.ToString("dd-MMM-yyyy"));
return(true);
}
private static void AddARow(String FromCurrency, String ToCurrency, DateTime EffectiveDate, decimal Rate)
{
ACorporateExchangeRateRow newRow = FMainDS.ACorporateExchangeRate.NewRowTyped();
newRow.FromCurrencyCode = FromCurrency;
newRow.ToCurrencyCode = ToCurrency;
newRow.DateEffectiveFrom = EffectiveDate;
newRow.TimeEffectiveFrom = 0;
newRow.RateOfExchange = Rate;
FMainDS.ACorporateExchangeRate.Rows.Add(newRow);
}
/// <summary>
/// init the exchange rate, to avoid messages "Cannot find exchange rate for EUR USD"
/// </summary>
public static void InitExchangeRate()
{
TAccountPeriodInfo AccountingPeriodInfo =
new TAccountPeriodInfo(FLedgerNumber, 1);
ADailyExchangeRateTable dailyrates = new ADailyExchangeRateTable();
ADailyExchangeRateRow row = dailyrates.NewRowTyped(true);
row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
row.TimeEffectiveFrom = 100;
row.FromCurrencyCode = "USD";
row.ToCurrencyCode = "EUR";
row.RateOfExchange = 1.34m;
dailyrates.Rows.Add(row);
row = dailyrates.NewRowTyped(true);
row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
row.TimeEffectiveFrom = 100;
row.FromCurrencyCode = "USD";
row.ToCurrencyCode = "GBP";
row.RateOfExchange = 1.57m;
dailyrates.Rows.Add(row);
if (!ADailyExchangeRateAccess.Exists(row.FromCurrencyCode, row.ToCurrencyCode, row.DateEffectiveFrom, row.TimeEffectiveFrom, null))
{
ADailyExchangeRateAccess.SubmitChanges(dailyrates, null);
}
ALedgerTable Ledger = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, null);
for (int periodCounter = 1; periodCounter <= Ledger[0].NumberOfAccountingPeriods + Ledger[0].NumberFwdPostingPeriods; periodCounter++)
{
AccountingPeriodInfo = new TAccountPeriodInfo(FLedgerNumber, periodCounter);
ACorporateExchangeRateTable corprates = new ACorporateExchangeRateTable();
ACorporateExchangeRateRow corprow = corprates.NewRowTyped(true);
corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
corprow.TimeEffectiveFrom = 100;
corprow.FromCurrencyCode = "USD";
corprow.ToCurrencyCode = "EUR";
corprow.RateOfExchange = 1.34m;
corprates.Rows.Add(corprow);
corprow = corprates.NewRowTyped(true);
corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
corprow.TimeEffectiveFrom = 100;
corprow.FromCurrencyCode = "USD";
corprow.ToCurrencyCode = "GBP";
corprow.RateOfExchange = 1.57m;
corprates.Rows.Add(corprow);
if (!ACorporateExchangeRateAccess.Exists(corprow.FromCurrencyCode, corprow.ToCurrencyCode, corprow.DateEffectiveFrom, null))
{
ACorporateExchangeRateAccess.SubmitChanges(corprates, null);
}
}
}
/// <summary>
/// Validates the Corporate Exchange Rates screen data.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that
/// display data that is about to be validated.</param>
public static void ValidateCorporateExchangeRate(object AContext, ACorporateExchangeRateRow ARow,
ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult;
// Don't validate deleted DataRows
if (ARow.RowState == DataRowState.Deleted)
{
return;
}
// RateOfExchange must be positive (definitely not zero because we can invert it)
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnRateOfExchangeId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TNumericalChecks.IsPositiveDecimal(ARow.RateOfExchange,
ValidationControlsData.ValidationControlLabel,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
// Date must not be empty
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnDateEffectiveFromId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TDateChecks.IsNotUndefinedDateTime(ARow.DateEffectiveFrom,
ValidationControlsData.ValidationControlLabel,
true, AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
// Date must be first of month
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnDateEffectiveFromId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TDateChecks.IsNotCorporateDateTime(ARow.DateEffectiveFrom,
ValidationControlsData.ValidationControlLabel,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
}
/// <summary>
/// Standardroutine
/// </summary>
/// <param name="ARow"></param>
private void ShowDetailsManual(ACorporateExchangeRateRow ARow)
{
if (ARow != null)
{
if (ARow.FromCurrencyCode == ARow.ToCurrencyCode)
{
ARow.RateOfExchange = 1.0m;
}
SetEnabledStates(ARow.RowState == DataRowState.Added);
}
else
{
txtDetailRateOfExchange.NumberValueDecimal = null;
}
UpdateExchangeRateLabels();
}
private bool PreDeleteManual(ACorporateExchangeRateRow ARowToDelete, ref string ADeletionQuestion)
{
// Check if corporate exchange rate can be deleted.
// Cannot be deleted if it is effective for a period in the current year which has at least one batch.
if (!TRemote.MFinance.Setup.WebConnectors.CanDeleteCorporateExchangeRate(
ARowToDelete.DateEffectiveFrom, ARowToDelete.ToCurrencyCode, ARowToDelete.FromCurrencyCode))
{
MessageBox.Show(Catalog.GetString("Corporate Exchange Rate cannot be deleted because there are still accounts with balances."),
Catalog.GetString("Delete Corporate Exchange Rate"), MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
ADeletionQuestion = Catalog.GetString("Are you sure you want to delete the current row?");
ADeletionQuestion += String.Format(Catalog.GetString("{0}{0}({1} to {2} effective from {3})"),
Environment.NewLine,
ARowToDelete.FromCurrencyCode,
ARowToDelete.ToCurrencyCode,
ARowToDelete.DateEffectiveFrom.ToString("dd-MMM-yyyy"));
return(true);
}
public static void CreateCorporateRate(string FromCurrencyCode, string ToCurrencyCode, DateTime EffectiveDate, decimal RateOfExchange)
{
FCorporateDS.LoadAll();
if (FCorporateDS.ACorporateRateTable.Rows.Find(new object[] { FromCurrencyCode, ToCurrencyCode, EffectiveDate }) != null)
{
// exists already
return;
}
ACorporateExchangeRateRow newRow = FCorporateDS.ACorporateRateTable.NewRowTyped(true);
newRow.FromCurrencyCode = FromCurrencyCode;
newRow.ToCurrencyCode = ToCurrencyCode;
newRow.TimeEffectiveFrom = 0;
newRow.DateEffectiveFrom = EffectiveDate;
newRow.RateOfExchange = RateOfExchange;
FCorporateDS.ACorporateRateTable.Rows.Add(newRow);
FCorporateDS.SaveChanges();
}
private bool PreDeleteManual(ACorporateExchangeRateRow ARowToDelete, ref string ADeletionQuestion)
{
// Check if corporate exchange rate can be deleted.
// Cannot be deleted if it is effective for a period in the current year which has at least one batch.
if (!TRemote.MFinance.Setup.WebConnectors.CanDeleteCorporateExchangeRate(
ARowToDelete.DateEffectiveFrom, ARowToDelete.ToCurrencyCode, ARowToDelete.FromCurrencyCode))
{
MessageBox.Show(Catalog.GetString("Corporate Exchange Rate cannot be deleted because there are still accounts with balances."),
Catalog.GetString("Delete Corporate Exchange Rate"), MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
ADeletionQuestion = Catalog.GetString("Are you sure you want to delete the current row?");
ADeletionQuestion += String.Format(Catalog.GetString("{0}{0}({1} to {2} effective from {3})"),
Environment.NewLine,
ARowToDelete.FromCurrencyCode,
ARowToDelete.ToCurrencyCode,
ARowToDelete.DateEffectiveFrom.ToString("dd-MMM-yyyy"));
return true;
}
private void ValidateDataDetailsManual(ACorporateExchangeRateRow ARow)
{
TVerificationResultCollection VerificationResultCollection = FPetraUtilsObject.VerificationResultCollection;
TSharedFinanceValidation_GLSetup.ValidateCorporateExchangeRate(this, ARow, ref VerificationResultCollection,
FPetraUtilsObject.ValidationControlsDict);
// In MODAL mode we can validate that the date is the same as an accounting period...
}
void FPetraUtilsObject_DataSavingStarted(object Sender, EventArgs e)
{
// The user has clicked Save. We need to consider if we need to make any Inverse currency additions...
// We need to update the details and validate them first
// When we return from this method the standard code will do the validation again and might not allow the save to go ahead
FPetraUtilsObject.VerificationResultCollection.Clear();
ValidateAllData(false, TErrorProcessingMode.Epm_None);
if (!TVerificationHelper.IsNullOrOnlyNonCritical(FPetraUtilsObject.VerificationResultCollection))
{
return;
}
// Now go through all the grid rows (view) checking all the added rows. Keep a list of inverses
List <tInverseItem> lstInverses = new List <tInverseItem>();
DataView gridView = ((DevAge.ComponentModel.BoundDataView)grdDetails.DataSource).DataView;
for (int i = 0; i < gridView.Count; i++)
{
ACorporateExchangeRateRow ARow = (ACorporateExchangeRateRow)gridView[i].Row;
if (ARow.RowState == DataRowState.Added)
{
tInverseItem item = new tInverseItem();
item.FromCurrencyCode = ARow.ToCurrencyCode;
item.ToCurrencyCode = ARow.FromCurrencyCode;
item.RateOfExchange = Math.Round(1 / ARow.RateOfExchange, 10);
item.DateEffective = ARow.DateEffectiveFrom;
lstInverses.Add(item);
}
}
if (lstInverses.Count == 0)
{
return;
}
// Now go through our list and check if any items need adding to the data Table
// The user may already have put an inverse currency in by hand
DataView dv = new DataView(FMainDS.ACorporateExchangeRate);
for (int i = 0; i < lstInverses.Count; i++)
{
tInverseItem item = lstInverses[i];
// Does the item exist already?
dv.RowFilter = String.Format(CultureInfo.InvariantCulture, "{0}='{1}' AND {2}='{3}' AND {4}=#{5}#",
ACorporateExchangeRateTable.GetFromCurrencyCodeDBName(),
item.FromCurrencyCode,
ACorporateExchangeRateTable.GetToCurrencyCodeDBName(),
item.ToCurrencyCode,
ACorporateExchangeRateTable.GetDateEffectiveFromDBName(),
item.DateEffective.ToString("d", CultureInfo.InvariantCulture));
if (dv.Count == 0)
{
ACorporateExchangeRateRow NewRow = FMainDS.ACorporateExchangeRate.NewRowTyped();
NewRow.FromCurrencyCode = item.FromCurrencyCode;
NewRow.ToCurrencyCode = item.ToCurrencyCode;
NewRow.DateEffectiveFrom = DateTime.Parse(item.DateEffective.ToLongDateString());
NewRow.RateOfExchange = item.RateOfExchange;
FMainDS.ACorporateExchangeRate.Rows.Add(NewRow);
}
}
// Now make sure to select the row that was currently selected when we started the Save operation
SelectRowInGrid(grdDetails.DataSourceRowToIndex2(FPreviouslySelectedDetailRow) + 1);
}
/// <summary>
/// Validates the Corporate Exchange Rates screen data.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that
/// display data that is about to be validated.</param>
public static void ValidateCorporateExchangeRate(object AContext, ACorporateExchangeRateRow ARow,
ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult;
// Don't validate deleted DataRows
if (ARow.RowState == DataRowState.Deleted)
{
return;
}
// RateOfExchange must be positive (definitely not zero because we can invert it)
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnRateOfExchangeId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TNumericalChecks.IsPositiveDecimal(ARow.RateOfExchange,
ValidationControlsData.ValidationControlLabel,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
// Date must not be empty
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnDateEffectiveFromId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TDateChecks.IsNotUndefinedDateTime(ARow.DateEffectiveFrom,
ValidationControlsData.ValidationControlLabel,
true, AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
// Date must be first of month
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnDateEffectiveFromId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TDateChecks.IsNotCorporateDateTime(ARow.DateEffectiveFrom,
ValidationControlsData.ValidationControlLabel,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
}
/// <summary>
/// Imports currency exchange rates, daily and corporate,
/// from a one-of-two-styles formatted CSV file
/// </summary>
/// <param name="AExchangeRDT">Daily or Corporate exchange rate table</param>
/// <param name="ADataFilename">The .CSV file to process</param>
/// <param name="ACSVSeparator"></param>
/// <param name="ANumberFormat"></param>
/// <param name="ADateFormat"></param>
/// <param name="AImportMode">Daily or Corporate</param>
/// <param name="AResultCollection">A validation collection to which errors will be added</param>
/// <returns>The number of rows that were actually imported. Rows that duplicate existing rows do not count.
/// This is usually because this is an attempt to import again after a failed previous attempt.</returns>
private static int ImportCurrencyExRatesFromCSV(TTypedDataTable AExchangeRDT,
string ADataFilename,
string ACSVSeparator,
string ANumberFormat,
string ADateFormat,
string AImportMode,
TVerificationResultCollection AResultCollection)
{
// Keep a list of errors/warnings and severity
List <Tuple <string, TResultSeverity> > InvalidRows = new List <Tuple <string, TResultSeverity> >();
// keep a variable that becomes true if any row has an invalid column count, so we can show a helpful message
bool InvalidColumnCount = false;
// Check our method parameters
if ((AImportMode != "Corporate") && (AImportMode != "Daily"))
{
throw new ArgumentException("Invalid value '" + AImportMode + "' for mode argument: Valid values are Corporate and Daily");
}
else if ((AImportMode == "Corporate") && !(AExchangeRDT is ACorporateExchangeRateTable))
{
throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Corporate'. Needs to be: ACorporateExchangeRateTable");
}
else if ((AImportMode == "Daily") && !(AExchangeRDT is ADailyExchangeRateTable))
{
throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Daily'. Needs to be: ADailyExchangeRateTable");
}
bool IsShortFileFormat;
int x, y;
// 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[] Currencies = new string[2];
Type DataTableType;
int RowsImported = 0;
// This table will tell us the base currencies used by the current set of ledgers
ALedgerTable ledgers = TRemote.MFinance.Setup.WebConnectors.GetAvailableLedgers();
List <string> allBaseCurrencies = new List <string>();
DateTime maxRecommendedEffectiveDate = DateTime.MaxValue;
DateTime minRecommendedEffectiveDate = DateTime.MinValue;
int preferredPeriodStartDay = 0;
// Use the ledger table rows to get a list of base currencies and current period end dates
for (int i = 0; i < ledgers.Rows.Count; i++)
{
ALedgerRow ledger = (ALedgerRow)ledgers.Rows[i];
string code = ledger.BaseCurrency;
if (ledger.LedgerStatus == true)
{
if (allBaseCurrencies.Contains(code) == false)
{
allBaseCurrencies.Add(code);
}
DataTable AccountingPeriods = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountingPeriodList,
ledger.LedgerNumber);
AAccountingPeriodRow currentPeriodRow = (AAccountingPeriodRow)AccountingPeriods.Rows.Find(new object[] { ledger.LedgerNumber,
ledger.CurrentPeriod });
AAccountingPeriodRow forwardPeriodRow = (AAccountingPeriodRow)AccountingPeriods.Rows.Find(
new object[] { ledger.LedgerNumber,
ledger.CurrentPeriod +
ledger.NumberFwdPostingPeriods });
if ((forwardPeriodRow != null) &&
((maxRecommendedEffectiveDate == DateTime.MaxValue) || (forwardPeriodRow.PeriodEndDate > maxRecommendedEffectiveDate)))
{
maxRecommendedEffectiveDate = forwardPeriodRow.PeriodEndDate;
}
if ((currentPeriodRow != null) &&
((minRecommendedEffectiveDate == DateTime.MinValue) || (currentPeriodRow.PeriodStartDate > minRecommendedEffectiveDate)))
{
minRecommendedEffectiveDate = currentPeriodRow.PeriodStartDate;
}
if ((currentPeriodRow != null) && (preferredPeriodStartDay == 0))
{
preferredPeriodStartDay = currentPeriodRow.PeriodStartDate.Day;
}
else if ((currentPeriodRow != null) && (currentPeriodRow.PeriodStartDate.Day != preferredPeriodStartDay))
{
preferredPeriodStartDay = -1;
}
}
}
// This will tell us the complete list of all available currencies
ACurrencyTable allCurrencies = new ACurrencyTable();
DataTable CacheDT = TDataCache.GetCacheableDataTableFromCache("CurrencyCodeList", String.Empty, null, out DataTableType);
allCurrencies.Merge(CacheDT);
allCurrencies.CaseSensitive = true;
// Start reading the file
using (StreamReader DataFile = new StreamReader(ADataFilename, System.Text.Encoding.Default))
{
string ThousandsSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "," : ".");
string DecimalSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "." : ",");
CultureInfo MyCultureInfoDate = new CultureInfo("en-GB");
MyCultureInfoDate.DateTimeFormat.ShortDatePattern = ADateFormat;
// TODO: disconnect the grid from the datasource to avoid flickering?
string FileNameWithoutExtension = Path.GetFileNameWithoutExtension(ADataFilename).ToUpper();
if ((FileNameWithoutExtension.IndexOf("_") == 3) &&
(FileNameWithoutExtension.LastIndexOf("_") == 3) &&
(FileNameWithoutExtension.Length == 7))
{
// File name format assumed to be like this: USD_HKD.csv
IsShortFileFormat = true;
Currencies = FileNameWithoutExtension.Split(new char[] { '_' });
}
else
{
IsShortFileFormat = false;
}
int LineNumber = 0;
while (!DataFile.EndOfStream)
{
string Line = DataFile.ReadLine();
LineNumber++;
// See if the first line is a special case??
if (LineNumber == 1)
{
// see if the first line is a text header - look for digits
// A valid header would look like: From,To,Date,Rate
bool bFoundDigit = false;
for (int i = 0; i < Line.Length; i++)
{
char c = Line[i];
if ((c >= '0') && (c <= '9'))
{
bFoundDigit = true;
break;
}
}
if (!bFoundDigit)
{
// No digits so we will assume the line is a header
continue;
}
}
//Convert separator to a char
char Sep = ACSVSeparator[0];
//Turn current line into string array of column values
string[] CsvColumns = Line.Split(Sep);
int NumCols = CsvColumns.Length;
// Do we have the correct number of columns?
int minColCount = IsShortFileFormat ? 2 : 4;
int maxColCount = (AImportMode == "Daily") ? minColCount + 1 : minColCount;
if ((NumCols < minColCount) || (NumCols > maxColCount))
{
// raise an error
string resultText = String.Format(Catalog.GetPluralString(
"Line {0}: contains 1 column", "Line {0}: contains {1} columns", NumCols, true),
LineNumber, NumCols.ToString());
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
InvalidColumnCount = true;
continue;
}
if (!IsShortFileFormat)
{
//Read the values for the current line
//From currency
Currencies[0] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString().ToUpper();
//To currency
Currencies[1] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString().ToUpper();
}
// Perform validation on the From and To currencies at this point!!
if ((allCurrencies.Rows.Find(Currencies[0]) == null) && (allCurrencies.Rows.Find(Currencies[1]) == null))
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid currency codes ({1} and {2})"), LineNumber.ToString(), Currencies[0], Currencies[1]);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
else if (allCurrencies.Rows.Find(Currencies[0]) == null)
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid currency code ({1})"), LineNumber.ToString(), Currencies[0]);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
else if (allCurrencies.Rows.Find(Currencies[1]) == null)
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid currency code ({1})"), LineNumber.ToString(), Currencies[1]);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
if ((allBaseCurrencies.Contains(Currencies[0]) == false) && (allBaseCurrencies.Contains(Currencies[1]) == false))
{
//raise a non-critical error
string resultText = String.Format(Catalog.GetString(
"Line {0}: Warning: One of '{1}' and '{2}' should be a base currency in one of the active ledgers."),
LineNumber.ToString(), Currencies[0], Currencies[1]);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical));
}
// Date parsing as in Petra 2.x instead of using XML date format!!!
string DateEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace("\"", String.Empty);
DateTime DateEffective;
if (!DateTime.TryParse(DateEffectiveStr, MyCultureInfoDate, DateTimeStyles.None, out DateEffective))
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid date ({1})"), LineNumber.ToString(), DateEffectiveStr);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
if (DateEffective > maxRecommendedEffectiveDate)
{
// raise a warning
string resultText = String.Format(Catalog.GetString(
"Line {0}: Warning: The date '{1}' is after the latest forwarding period of any active ledger"),
LineNumber.ToString(), DateEffectiveStr);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical));
}
else if (DateEffective < minRecommendedEffectiveDate)
{
// raise a warning
string resultText = String.Format(Catalog.GetString(
"Line {0}: Warning: The date '{1}' is before the current accounting period of any active ledger"),
LineNumber.ToString(), DateEffectiveStr);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical));
}
if (AImportMode == "Corporate")
{
if ((preferredPeriodStartDay >= 1) && (DateEffective.Day != preferredPeriodStartDay))
{
// raise a warning
string resultText = String.Format(Catalog.GetString(
"Line {0}: Warning: The date '{1}' should be the first day of an accounting period used by all the active ledgers."),
LineNumber.ToString(), DateEffectiveStr);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical));
}
}
decimal ExchangeRate = 0.0m;
try
{
string ExchangeRateString =
StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace(ThousandsSeparator, "").Replace(
DecimalSeparator, ".").Replace("\"", String.Empty);
ExchangeRate = Convert.ToDecimal(ExchangeRateString, System.Globalization.CultureInfo.InvariantCulture);
if (ExchangeRate == 0)
{
throw new Exception();
}
}
catch (Exception)
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid rate of exchange ({1})"), LineNumber.ToString(), ExchangeRate);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
int TimeEffective = 7200;
if (AImportMode == "Daily")
{
// Daily rate imports can have an optional final column which is the time
// Otherwise we assume the time is a default of 7200 (02:00am)
if ((IsShortFileFormat && (NumCols == 3)) ||
(!IsShortFileFormat && (NumCols == 5)))
{
string timeEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true);
int t = (int)new Ict.Common.TypeConverter.TShortTimeConverter().ConvertTo(timeEffectiveStr, typeof(int));
if (t < 0)
{
// it wasn't in the format 02:00
if (!Int32.TryParse(timeEffectiveStr, out t))
{
// Not a regular Int32 either
t = -1;
}
}
if ((t >= 0) && (t < 86400))
{
TimeEffective = t;
}
else
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Line {0}: invalid effective time ({1})"), LineNumber.ToString(), t);
InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical));
continue;
}
}
}
if ((AImportMode == "Corporate") && AExchangeRDT is ACorporateExchangeRateTable)
{
ACorporateExchangeRateTable ExchangeRateDT = (ACorporateExchangeRateTable)AExchangeRDT;
// run this code in the loop twice to get ExchangeRate value and its inverse
for (int i = 0; i <= 1; i++)
{
//this will cause x and y to go from 0 to 1 and 1 to 0 respectively
x = i;
y = Math.Abs(i - 1);
ACorporateExchangeRateRow ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.Rows.
Find(new object[] { Currencies[x], Currencies[y], DateEffective });
if (ExchangeRow == null) // remove 0 for Corporate
{
ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.NewRowTyped();
ExchangeRow.FromCurrencyCode = Currencies[x];
ExchangeRow.ToCurrencyCode = Currencies[y];
ExchangeRow.DateEffectiveFrom = DateEffective;
ExchangeRateDT.Rows.Add(ExchangeRow);
RowsImported++;
}
if (i == 0)
{
ExchangeRow.RateOfExchange = ExchangeRate;
}
else
{
ExchangeRow.RateOfExchange = Math.Round(1 / ExchangeRate, 10);
}
}
}
else if ((AImportMode == "Daily") && AExchangeRDT is ADailyExchangeRateTable)
{
ADailyExchangeRateTable ExchangeRateDT = (ADailyExchangeRateTable)AExchangeRDT;
// run this code in the loop twice to get ExchangeRate value and its inverse
for (int i = 0; i <= 1; i++)
{
//this will cause x and y to go from 0 to 1 and 1 to 0 respectively
x = i;
y = Math.Abs(i - 1);
ADailyExchangeRateRow ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.Rows.
Find(new object[] { Currencies[x], Currencies[y], DateEffective, TimeEffective });
if (ExchangeRow == null) // remove 0 for Corporate
{
ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.NewRowTyped();
ExchangeRow.FromCurrencyCode = Currencies[x];
ExchangeRow.ToCurrencyCode = Currencies[y];
ExchangeRow.DateEffectiveFrom = DateEffective;
ExchangeRow.TimeEffectiveFrom = TimeEffective;
ExchangeRateDT.Rows.Add(ExchangeRow);
RowsImported++;
}
if (i == 0)
{
ExchangeRow.RateOfExchange = ExchangeRate;
}
else
{
ExchangeRow.RateOfExchange = Math.Round(1 / ExchangeRate, 10);
}
}
}
}
// if there are rows that could not be imported
if ((InvalidRows != null) && (InvalidRows.Count > 0))
{
int errorCount = 0;
int warningCount = 0;
// Go through once just to count the errors and warnings
foreach (Tuple <string, TResultSeverity> Row in InvalidRows)
{
if (Row.Item2 == TResultSeverity.Resv_Noncritical)
{
warningCount++;
}
else
{
errorCount++;
}
}
string resultText = String.Empty;
bool messageListIsFull = false;
int counter = 0;
if (errorCount > 0)
{
resultText = string.Format(Catalog.GetPluralString("1 row was not imported due to invalid data:",
"{0} rows were not imported due to invalid data:", errorCount, true), errorCount) +
Environment.NewLine;
}
if (warningCount > 0)
{
resultText = string.Format(Catalog.GetPluralString("There was 1 warning associated with the imported rows:",
"There were {0} warnings associated with the imported rows:", warningCount, true), warningCount) +
Environment.NewLine;
}
// Now go through again itemising each one
foreach (Tuple <string, TResultSeverity> Row in InvalidRows)
{
counter++;
if (counter <= MAX_MESSAGE_COUNT)
{
resultText += Environment.NewLine + Row.Item1;
}
else if (!messageListIsFull)
{
resultText += String.Format(Catalog.GetString(
"{0}{0}{1} errors/warnings were reported in total. This message contains the first {2}."),
Environment.NewLine, InvalidRows.Count, MAX_MESSAGE_COUNT);
messageListIsFull = true;
}
}
// additional message if one or more rows has an invalid number of columns
if (InvalidColumnCount && IsShortFileFormat)
{
resultText += String.Format("{0}{0}" + Catalog.GetString("Each row should contain 2 or 3 columns as follows:") + "{0}" +
Catalog.GetString(
" 1. Effective Date{0} 2. Exchange Rate{0} 3. Effective time in seconds (Optional for Daily Rate only)"),
Environment.NewLine);
}
else if (InvalidColumnCount && !IsShortFileFormat)
{
resultText += String.Format("{0}{0}" + Catalog.GetString("Each row should contain 4 or 5 columns as follows:") + "{0}" +
Catalog.GetString(
" 1. From Currency{0} 2. To Currency{0} 3. Effective Date{0} 4. Exchange Rate{0} 5. Effective time in seconds (Optional for Daily Rate only)"),
Environment.NewLine);
}
TVerificationResult result = new TVerificationResult(AImportMode,
resultText,
CommonErrorCodes.ERR_INCONGRUOUSSTRINGS,
(errorCount > 0) ? TResultSeverity.Resv_Critical : TResultSeverity.Resv_Noncritical);
AResultCollection.Add(result);
}
DataFile.Close();
return(RowsImported);
}
}
private bool PreDeleteManual(ACorporateExchangeRateRow ARowToDelete, ref string ADeletionQuestion)
{
ADeletionQuestion = Catalog.GetString("Are you sure you want to delete the current row?");
ADeletionQuestion += String.Format(Catalog.GetString("{0}{0}({1} to {2} effective from {3})"),
Environment.NewLine,
ARowToDelete.FromCurrencyCode,
ARowToDelete.ToCurrencyCode,
ARowToDelete.DateEffectiveFrom.ToString("dd-MMM-yyyy"));
return true;
}
/// <summary>
/// Validates the Corporate Exchange Rates screen data.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that
/// display data that is about to be validated.</param>
/// <param name="ALedgerTableRef">A reference to a ledger table that has contains the ledgers that a client has access to</param>
/// <param name="AAlternativeFirstDayOfPeriod">An alternative day (apart from 1) that is the start of an accounting period
/// for at least one of the availbale ledgers</param>
public static void ValidateCorporateExchangeRate(object AContext, ACorporateExchangeRateRow ARow,
ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict,
ALedgerTable ALedgerTableRef, int AAlternativeFirstDayOfPeriod)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TVerificationResult VerificationResult;
// Don't validate deleted DataRows
if (ARow.RowState == DataRowState.Deleted)
{
return;
}
// RateOfExchange must be positive (definitely not zero because we can invert it)
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnRateOfExchangeId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TNumericalChecks.IsPositiveDecimal(ARow.RateOfExchange,
ValidationControlsData.ValidationControlLabel,
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
// Date must not be empty
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnDateEffectiveFromId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = TDateChecks.IsNotUndefinedDateTime(ARow.DateEffectiveFrom,
ValidationControlsData.ValidationControlLabel,
true, AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
// Date must be first of month or first day in accounting period of a ledger
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnDateEffectiveFromId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = null;
if (AAlternativeFirstDayOfPeriod != 0)
{
// day must be either 1 or AAlternativeFirstDayOfPeriod
VerificationResult = TDateChecks.IsNotCorporateDateTime(ARow.DateEffectiveFrom,
ValidationControlsData.ValidationControlLabel,
AContext, ValidationColumn, ValidationControlsData.ValidationControl, AAlternativeFirstDayOfPeriod);
}
else
{
// when the value is 0 we cannot do validation because there are too many alternatives!
// How complicated is this set of ledgers???
}
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn);
}
if (AContext is System.Windows.Forms.Form || AContext is System.Windows.Forms.UserControl)
{
// These tests are for the GUI only
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnToCurrencyCodeId];
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
// One of the currencies should be the base currency of one of the ledgers
if ((ARow.RowState == DataRowState.Added) && (ALedgerTableRef != null))
{
// Only do this test on new rows
TScreenVerificationResult vr = null;
DataView fromView = new DataView(ALedgerTableRef, String.Format("{0}='{1}'",
ALedgerTable.GetBaseCurrencyDBName(), ARow.FromCurrencyCode), String.Empty, DataViewRowState.CurrentRows);
if (fromView.Count == 0)
{
DataView toView = new DataView(ALedgerTableRef, String.Format("{0}='{1}'",
ALedgerTable.GetBaseCurrencyDBName(), ARow.ToCurrencyCode), String.Empty, DataViewRowState.CurrentRows);
if (toView.Count == 0)
{
vr = new TScreenVerificationResult(AContext, ValidationColumn,
"One of the currencies should normally be a base currency for one of the Ledgers",
ValidationControlsData.ValidationControl, TResultSeverity.Resv_Noncritical);
}
}
// Handle addition to/removal from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, vr, ValidationColumn);
}
}
}
}
/// <summary>
/// init the exchange rate, to avoid messages "Cannot find exchange rate for EUR USD"
/// </summary>
public static void InitExchangeRate(TDataBase ADataBase = null)
{
TDataBase db = DBAccess.Connect("InitExchangeRate", ADataBase);
TDBTransaction Transaction = new TDBTransaction();
bool SubmitOK = false;
db.WriteTransaction(ref Transaction,
ref SubmitOK,
delegate
{
TAccountPeriodInfo AccountingPeriodInfo =
new TAccountPeriodInfo(FLedgerNumber, 1);
ADailyExchangeRateTable dailyrates = new ADailyExchangeRateTable();
ADailyExchangeRateRow row = dailyrates.NewRowTyped(true);
row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
row.TimeEffectiveFrom = 100;
row.FromCurrencyCode = "USD";
row.ToCurrencyCode = "EUR";
row.RateOfExchange = 1.34m;
dailyrates.Rows.Add(row);
row = dailyrates.NewRowTyped(true);
row.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
row.TimeEffectiveFrom = 100;
row.FromCurrencyCode = "USD";
row.ToCurrencyCode = "GBP";
row.RateOfExchange = 1.57m;
dailyrates.Rows.Add(row);
if (!ADailyExchangeRateAccess.Exists(row.FromCurrencyCode, row.ToCurrencyCode, row.DateEffectiveFrom, row.TimeEffectiveFrom, Transaction))
{
ADailyExchangeRateAccess.SubmitChanges(dailyrates, Transaction);
}
ALedgerTable Ledger = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, Transaction);
for (int periodCounter = 1; periodCounter <= Ledger[0].NumberOfAccountingPeriods + Ledger[0].NumberFwdPostingPeriods; periodCounter++)
{
AccountingPeriodInfo = new TAccountPeriodInfo(FLedgerNumber, periodCounter);
ACorporateExchangeRateTable corprates = new ACorporateExchangeRateTable();
ACorporateExchangeRateRow corprow = corprates.NewRowTyped(true);
corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
corprow.TimeEffectiveFrom = 100;
corprow.FromCurrencyCode = "USD";
corprow.ToCurrencyCode = "EUR";
corprow.RateOfExchange = 1.34m;
corprates.Rows.Add(corprow);
corprow = corprates.NewRowTyped(true);
corprow.DateEffectiveFrom = AccountingPeriodInfo.PeriodStartDate;
corprow.TimeEffectiveFrom = 100;
corprow.FromCurrencyCode = "USD";
corprow.ToCurrencyCode = "GBP";
corprow.RateOfExchange = 1.57m;
corprates.Rows.Add(corprow);
if (!ACorporateExchangeRateAccess.Exists(corprow.FromCurrencyCode, corprow.ToCurrencyCode, corprow.DateEffectiveFrom, Transaction))
{
ACorporateExchangeRateAccess.SubmitChanges(corprates, Transaction);
}
}
SubmitOK = true;
});
if (ADataBase == null)
{
db.CloseDBConnection();
}
}
/// <summary>
/// Standardroutine
/// </summary>
/// <param name="ARow"></param>
private void ShowDetailsManual(ACorporateExchangeRateRow ARow)
{
if (ARow != null)
{
if (ARow.FromCurrencyCode == ARow.ToCurrencyCode)
{
ARow.RateOfExchange = 1.0m;
}
SetEnabledStates(ARow.RowState == DataRowState.Added);
}
else
{
txtDetailRateOfExchange.NumberValueDecimal = null;
}
UpdateExchangeRateLabels();
}
private void GetDetailDataFromControlsManual(ACorporateExchangeRateRow ARow)
{
// Check if we have an inverse rate for this date/time and currency pair
ACorporateExchangeRateRow mainRow = (ACorporateExchangeRateRow)FMainDS.ACorporateExchangeRate.Rows.Find(
new object[] { ARow.ToCurrencyCode, ARow.FromCurrencyCode, ARow.DateEffectiveFrom });
if ((mainRow != null) && (ARow.RateOfExchange != 0.0m))
{
// Checking to see if we have a matching rate is tricky because rounding errors mean that the inverse of an inverse
// does not always get you back where you started. So we check both ways to look for a match.
// If neither way matches we need to do an update, but if there is a match in at least one direction, we leave the other row as it is.
decimal inverseRate = Math.Round(1 / ARow.RateOfExchange, 10);
decimal inverseRateAlt = Math.Round(1 / mainRow.RateOfExchange, 10);
if ((mainRow.RateOfExchange != inverseRate) && (ARow.RateOfExchange != inverseRateAlt))
{
// Neither way matches so we must have made a change that requires an update to the inverse row
mainRow.BeginEdit();
mainRow.RateOfExchange = inverseRate;
mainRow.EndEdit();
}
}
}
/// <summary>
/// Validates the GL Journal data.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that
/// display data that is about to be validated.</param>
/// <param name="ACorporateExchangeTableRef">Corporate exchange rate table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ABaseCurrency">Ledger base currency. Required when importing</param>
/// <returns>True if the validation found no data validation errors, otherwise false.</returns>
public static bool ValidateGLJournalManual(object AContext, AJournalRow ARow,
ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict,
ACorporateExchangeRateTable ACorporateExchangeTableRef = null, string ABaseCurrency = null)
{
DataColumn ValidationColumn;
TValidationControlsData ValidationControlsData;
TScreenVerificationResult VerificationResult;
string ValidationContext;
int VerifResultCollAddedCount = 0;
// Don't validate deleted or posted DataRows
if ((ARow.RowState == DataRowState.Deleted) || (ARow.JournalStatus == MFinanceConstants.BATCH_POSTED))
{
return(true);
}
bool isImporting = AContext.ToString().Contains("Importing");
// 'Exchange Rate' must be greater than 0
ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnExchangeRateToBaseId];
ValidationContext = ARow.JournalNumber.ToString() + " of Batch Number: " + ARow.BatchNumber.ToString();
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
VerificationResult = (TScreenVerificationResult)TNumericalChecks.IsPositiveDecimal(ARow.ExchangeRateToBase,
ValidationControlsData.ValidationControlLabel + (isImporting ? String.Empty : " of Journal Number: " + ValidationContext.ToString()),
AContext, ValidationColumn, ValidationControlsData.ValidationControl);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true))
{
VerifResultCollAddedCount++;
}
}
if ((ACorporateExchangeTableRef != null) && (ABaseCurrency != null) && (ARow.TransactionCurrency != ABaseCurrency))
{
// For gifts in non-base currency there must be a corporate exchange rate
ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnTransactionCurrencyId];
ValidationContext = ARow.JournalNumber.ToString() + " of Batch Number: " + ARow.BatchNumber.ToString();
if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData))
{
DateTime firstOfMonth = new DateTime(ARow.DateEffective.Year, ARow.DateEffective.Month, 1);
ACorporateExchangeRateRow foundRow = (ACorporateExchangeRateRow)ACorporateExchangeTableRef.Rows.Find(
new object[] { ARow.TransactionCurrency, ABaseCurrency, firstOfMonth });
if ((foundRow == null) &&
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("There is no Corporate Exchange Rate defined for the month starting on '{0}'."),
StringHelper.DateToLocalizedString(firstOfMonth)),
TResultSeverity.Resv_Critical),
ValidationColumn))
{
VerifResultCollAddedCount++;
}
}
}
return(VerifResultCollAddedCount == 0);
}
private void NewRowManual(ref ACorporateExchangeRateRow ARow)
{
// We just need to decide on the appropriate currency pair and then call the standard method to get a suggested rate and date
if (FPreviouslySelectedDetailRow == null)
{
// Corporate Exchange rates are not part of any ledger, so baseCurrencyOfLedger may be null...
if (baseCurrencyOfLedger == null)
{
ARow.FromCurrencyCode = "GBP";
ARow.ToCurrencyCode = "USD";
}
else
{
if (baseCurrencyOfLedger == "USD")
{
ARow.FromCurrencyCode = "GBP";
}
else
{
ARow.FromCurrencyCode = "USD";
}
ARow.ToCurrencyCode = baseCurrencyOfLedger;
}
}
else
{
// Use the same settings as the highlighted row
ARow.FromCurrencyCode = cmbDetailFromCurrencyCode.GetSelectedString();
ARow.ToCurrencyCode = cmbDetailToCurrencyCode.GetSelectedString();
}
DateTime suggestedDate;
decimal suggestedRate;
GetSuggestedDateAndRateForCurrencyPair(ARow.FromCurrencyCode, ARow.ToCurrencyCode, out suggestedDate, out suggestedRate);
ARow.DateEffectiveFrom = suggestedDate;
ARow.RateOfExchange = suggestedRate;
// The time is always 0 for corporate exchange rate
ARow.TimeEffectiveFrom = 0;
}
/// <summary>
/// Validates the GL Journal data.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="AGLSetupDSRef">A GLSetupTDS reference with a populated ATransactionTypeTable. A reference to this DataSet is REQUIRED when importing - optional otherwise</param>
/// <param name="ACurrencyTableRef">A reference to the Currency table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ACorporateExchangeTableRef">Corporate exchange rate table. A reference to this table is REQUIRED when importing - optional otherwise</param>
/// <param name="ABaseCurrency">Ledger base currency. Required when importing</param>
/// <param name="AIntlCurrency">Ledger international currency. Required when importing</param>
/// <returns>True if the validation found no data validation errors, otherwise false.</returns>
public static bool ValidateGLJournalManual(object AContext, AJournalRow ARow,
ref TVerificationResultCollection AVerificationResultCollection,
GLSetupTDS AGLSetupDSRef = null,
ACurrencyTable ACurrencyTableRef = null,
ACorporateExchangeRateTable ACorporateExchangeTableRef = null,
String ABaseCurrency = null,
String AIntlCurrency = null)
{
DataColumn ValidationColumn;
TScreenVerificationResult VerificationResult;
string ValidationContext;
int VerifResultCollAddedCount = 0;
// Don't validate deleted or posted DataRows
if ((ARow.RowState == DataRowState.Deleted) || (ARow.JournalStatus == MFinanceConstants.BATCH_POSTED))
{
return(true);
}
bool isImporting = AContext.ToString().Contains("Importing");
// 'Exchange Rate' must be greater than 0
ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnExchangeRateToBaseId];
ValidationContext = ARow.JournalNumber.ToString() + " of Batch Number: " + ARow.BatchNumber.ToString();
if (true)
{
VerificationResult = (TScreenVerificationResult)TNumericalChecks.IsPositiveDecimal(ARow.ExchangeRateToBase,
String.Empty + (isImporting ? String.Empty : " of Journal Number: " + ValidationContext.ToString()),
AContext, ValidationColumn);
// Handle addition/removal to/from TVerificationResultCollection
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult))
{
VerifResultCollAddedCount++;
}
// Exchange rate must be 1.00 if the currency is the base ledger currency
if ((ABaseCurrency != null) &&
(!ARow.IsExchangeRateToBaseNull()) &&
(ARow.TransactionCurrency == ABaseCurrency) &&
(ARow.ExchangeRateToBase != 1.00m))
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext,
new TVerificationResult(ValidationContext,
Catalog.GetString("A journal in the ledger base currency must have exchange rate of 1.00."),
TResultSeverity.Resv_Critical)))
{
VerifResultCollAddedCount++;
}
}
}
// Transaction currency must be valid
bool isValidTransactionCurrency = true;
if (isImporting && (ACurrencyTableRef != null))
{
ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnTransactionCurrencyId];
if (true)
{
ACurrencyRow foundRow = (ACurrencyRow)ACurrencyTableRef.Rows.Find(ARow.TransactionCurrency);
isValidTransactionCurrency = (foundRow != null);
if ((foundRow == null) &&
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString("'{0}' is not a valid currency."), ARow.TransactionCurrency),
TResultSeverity.Resv_Critical)))
{
VerifResultCollAddedCount++;
}
}
}
if ((ACorporateExchangeTableRef != null) && isValidTransactionCurrency && (ABaseCurrency != null) && (AIntlCurrency != null) &&
!ARow.IsDateEffectiveNull() && (ABaseCurrency != AIntlCurrency))
{
// For ledgers where the base currency and intl currency differ there must be a corporate exchange rate to the international currency
ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnTransactionCurrencyId];
ValidationContext = ARow.JournalNumber.ToString() + " of Batch Number: " + ARow.BatchNumber.ToString();
if (true)
{
DateTime firstOfMonth;
if (TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriod(ARow.LedgerNumber,
ARow.DateEffective, out firstOfMonth))
{
ACorporateExchangeRateRow foundRow = (ACorporateExchangeRateRow)ACorporateExchangeTableRef.Rows.Find(
new object[] { ABaseCurrency, AIntlCurrency, firstOfMonth });
if ((foundRow == null) &&
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"There is no Corporate Exchange Rate defined for '{0}' to '{1}' for the month starting on '{2}'."),
ABaseCurrency, AIntlCurrency,
StringHelper.DateToLocalizedString(firstOfMonth)),
TResultSeverity.Resv_Noncritical)))
{
VerifResultCollAddedCount++;
}
}
}
}
// Sub-system code must exist in the transaction type table
if (isImporting && (AGLSetupDSRef.ATransactionType != null))
{
ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnSubSystemCodeId];
if (true)
{
ATransactionTypeRow foundRow = (ATransactionTypeRow)AGLSetupDSRef.ATransactionType.Rows.Find(
new object[] { ARow.LedgerNumber, ARow.SubSystemCode, ARow.TransactionTypeCode });
if ((foundRow == null) &&
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
String.Format(Catalog.GetString(
"The combination of Transaction Type of '{0}' and Sub-system Code of '{1}' is not valid for journals in Ledger {2}."),
ARow.TransactionTypeCode, ARow.SubSystemCode, ARow.LedgerNumber),
TResultSeverity.Resv_Critical)))
{
VerifResultCollAddedCount++;
}
}
}
// Journal description must not be null
if (isImporting)
{
ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnJournalDescriptionId];
if (true)
{
if ((ARow.JournalDescription == null) || (ARow.JournalDescription.Length == 0))
{
if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(
AContext,
new TVerificationResult(ValidationContext,
Catalog.GetString("The journal description must not be empty."),
TResultSeverity.Resv_Critical)))
{
VerifResultCollAddedCount++;
}
}
}
}
return(VerifResultCollAddedCount == 0);
}
/// <summary>
/// Validates the Corporate Exchange Rates screen data.
/// </summary>
/// <param name="AContext">Context that describes where the data validation failed.</param>
/// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param>
/// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if
/// data validation errors occur.</param>
/// <param name="ALedgerTableRef">A reference to a ledger table that has contains the ledgers that a client has access to</param>
/// <param name="AAlternativeFirstDayOfPeriod">An alternative day (apart from 1) that is the start of an accounting period
/// for at least one of the availbale ledgers</param>
public static void ValidateCorporateExchangeRate(object AContext, ACorporateExchangeRateRow ARow,
ref TVerificationResultCollection AVerificationResultCollection,
ALedgerTable ALedgerTableRef, int AAlternativeFirstDayOfPeriod)
{
DataColumn ValidationColumn;
TVerificationResult VerificationResult;
// Don't validate deleted DataRows
if (ARow.RowState == DataRowState.Deleted)
{
return;
}
// RateOfExchange must be positive (definitely not zero because we can invert it)
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnRateOfExchangeId];
if (true)
{
VerificationResult = TNumericalChecks.IsPositiveDecimal(ARow.RateOfExchange,
String.Empty,
AContext, ValidationColumn);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult);
}
// Date must not be empty
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnDateEffectiveFromId];
if (true)
{
VerificationResult = TDateChecks.IsNotUndefinedDateTime(ARow.DateEffectiveFrom,
String.Empty,
true, AContext, ValidationColumn);
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult);
}
// Date must be first of month or first day in accounting period of a ledger
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnDateEffectiveFromId];
if (true)
{
VerificationResult = null;
if (AAlternativeFirstDayOfPeriod != 0)
{
// day must be either 1 or AAlternativeFirstDayOfPeriod
VerificationResult = TDateChecks.IsNotCorporateDateTime(ARow.DateEffectiveFrom,
String.Empty,
AContext, ValidationColumn, AAlternativeFirstDayOfPeriod);
}
else
{
// when the value is 0 we cannot do validation because there are too many alternatives!
// How complicated is this set of ledgers???
}
// Handle addition/removal to/from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult);
}
if (true)
{
// These tests are for the GUI only
ValidationColumn = ARow.Table.Columns[ACorporateExchangeRateTable.ColumnToCurrencyCodeId];
if (true)
{
// One of the currencies should be the base currency of one of the ledgers
if ((ARow.RowState == DataRowState.Added) && (ALedgerTableRef != null))
{
// Only do this test on new rows
TScreenVerificationResult vr = null;
DataView fromView = new DataView(ALedgerTableRef, String.Format("{0}='{1}'",
ALedgerTable.GetBaseCurrencyDBName(), ARow.FromCurrencyCode), String.Empty, DataViewRowState.CurrentRows);
if (fromView.Count == 0)
{
DataView toView = new DataView(ALedgerTableRef, String.Format("{0}='{1}'",
ALedgerTable.GetBaseCurrencyDBName(), ARow.ToCurrencyCode), String.Empty, DataViewRowState.CurrentRows);
if (toView.Count == 0)
{
vr = new TScreenVerificationResult(AContext, ValidationColumn,
"One of the currencies should normally be a base currency for one of the Ledgers",
TResultSeverity.Resv_Noncritical);
}
}
// Handle addition to/removal from TVerificationResultCollection
AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, vr);
}
}
}
}
/// <summary>
/// get corporate exchange rate for the given currencies and date;
/// </summary>
/// <param name="ACurrencyFrom"></param>
/// <param name="ACurrencyTo"></param>
/// <param name="AStartDate"></param>
/// <param name="AEndDate"></param>
/// <param name="AExchangeRateToFind"></param>
/// <returns>true if a exchange rate was found for the date. Otherwise false</returns>
public static bool GetCorporateExchangeRate(string ACurrencyFrom,
string ACurrencyTo,
DateTime AStartDate,
DateTime AEndDate,
out decimal AExchangeRateToFind)
{
AExchangeRateToFind = decimal.MinValue;
decimal ExchangeRateToFind = AExchangeRateToFind;
TDBTransaction Transaction = null;
ACorporateExchangeRateTable tempTable = new ACorporateExchangeRateTable();
ACorporateExchangeRateRow templateRow = tempTable.NewRowTyped(false);
templateRow.FromCurrencyCode = ACurrencyFrom;
templateRow.ToCurrencyCode = ACurrencyTo;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
try
{
ACorporateExchangeRateTable ExchangeRates = ACorporateExchangeRateAccess.LoadUsingTemplate(templateRow, Transaction);
if (ExchangeRates.Count > 0)
{
// sort rates by date, look for rate just before the date we are looking for
ExchangeRates.DefaultView.Sort = ACorporateExchangeRateTable.GetDateEffectiveFromDBName();
ExchangeRates.DefaultView.RowFilter = ACorporateExchangeRateTable.GetDateEffectiveFromDBName() + ">= #" +
AStartDate.ToString("yyyy-MM-dd") + "# AND " +
ACorporateExchangeRateTable.GetDateEffectiveFromDBName() + "<= #" +
AEndDate.ToString("yyyy-MM-dd") + "#";
if (ExchangeRates.DefaultView.Count > 0)
{
ExchangeRateToFind = ((ACorporateExchangeRateRow)ExchangeRates.DefaultView[0].Row).RateOfExchange;
}
}
if (ExchangeRateToFind == decimal.MinValue)
{
// try other way round
templateRow.FromCurrencyCode = ACurrencyTo;
templateRow.ToCurrencyCode = ACurrencyFrom;
ExchangeRates = ACorporateExchangeRateAccess.LoadUsingTemplate(templateRow, Transaction);
if (ExchangeRates.Count > 0)
{
// sort rates by date, look for rate just before the date we are looking for
ExchangeRates.DefaultView.Sort = ACorporateExchangeRateTable.GetDateEffectiveFromDBName();
ExchangeRates.DefaultView.RowFilter = ACorporateExchangeRateTable.GetDateEffectiveFromDBName() + ">= #" +
AStartDate.ToString("yyyy-MM-dd") + "# AND " +
ACorporateExchangeRateTable.GetDateEffectiveFromDBName() + "<= #" +
AEndDate.ToString("yyyy-MM-dd") + "#";
if (ExchangeRates.DefaultView.Count > 0)
{
ExchangeRateToFind = 1 / ((ACorporateExchangeRateRow)ExchangeRates.DefaultView[0].Row).RateOfExchange;
}
}
}
}
catch (Exception e)
{
TLogging.Log("Error in GetCorporateExchangeRate: " + e.Message);
}
});
AExchangeRateToFind = ExchangeRateToFind;
return(AExchangeRateToFind != decimal.MinValue);
}
/// <summary>
/// Imports currency exchange rates, daily and corporate,
/// from a one-of-two-styles formatted CSV file
/// </summary>
/// <param name="AExchangeRDT">Daily or Corporate exchange rate table</param>
/// <param name="ADataFilename">The .CSV file to process</param>
/// <param name="ACSVSeparator"></param>
/// <param name="ANumberFormat"></param>
/// <param name="ADateFormat"></param>
/// <param name="AImportMode">Daily or Corporate</param>
/// <param name="AResultCollection">A validation collection to which errors will be added</param>
/// <returns>The number of rows that were actually imported. Rows that duplicate existing rows do not count.
/// This is usually because this is an attempt to import again after a failed previous attempt.</returns>
private static int ImportCurrencyExRatesFromCSV(TTypedDataTable AExchangeRDT,
string ADataFilename,
string ACSVSeparator,
string ANumberFormat,
string ADateFormat,
string AImportMode,
TVerificationResultCollection AResultCollection)
{
if ((AImportMode != "Corporate") && (AImportMode != "Daily"))
{
throw new ArgumentException("Invalid value '" + AImportMode + "' for mode argument: Valid values are Corporate and Daily");
}
else if ((AImportMode == "Corporate") && (AExchangeRDT.GetType() != typeof(ACorporateExchangeRateTable)))
{
throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Corporate'. Needs to be: ACorporateExchangeRateTable");
}
else if ((AImportMode == "Daily") && (AExchangeRDT.GetType() != typeof(ADailyExchangeRateTable)))
{
throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Daily'. Needs to be: ADailyExchangeRateTable");
}
bool IsShortFileFormat;
int x, y;
// 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[] Currencies = new string[2];
Type DataTableType;
int RowsImported = 0;
ACurrencyTable allCurrencies = new ACurrencyTable();
DataTable CacheDT = TDataCache.GetCacheableDataTableFromCache("CurrencyCodeList", String.Empty, null, out DataTableType);
allCurrencies.Merge(CacheDT);
using (StreamReader DataFile = new StreamReader(ADataFilename, System.Text.Encoding.Default))
{
string ThousandsSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "," : ".");
string DecimalSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "." : ",");
CultureInfo MyCultureInfoDate = new CultureInfo("en-GB");
MyCultureInfoDate.DateTimeFormat.ShortDatePattern = ADateFormat;
// TODO: disconnect the grid from the datasource to avoid flickering?
string FileNameWithoutExtension = Path.GetFileNameWithoutExtension(ADataFilename);
if ((FileNameWithoutExtension.IndexOf("_") == 3) &&
(FileNameWithoutExtension.LastIndexOf("_") == 3) &&
(FileNameWithoutExtension.Length == 7))
{
// File name format assumed to be like this: USD_HKD.csv
IsShortFileFormat = true;
Currencies = FileNameWithoutExtension.Split(new char[] { '_' });
}
else
{
IsShortFileFormat = false;
}
int LineNumber = 0;
while (!DataFile.EndOfStream)
{
string Line = DataFile.ReadLine();
LineNumber++;
// See if the first line is a special case??
if (LineNumber == 1)
{
// see if the first line is a text header - look for digits
// A valid header would look like: From,To,Date,Rate
bool bFoundDigit = false;
for (int i = 0; i < Line.Length; i++)
{
char c = Line[i];
if ((c >= '0') && (c <= '9'))
{
bFoundDigit = true;
break;
}
}
if (!bFoundDigit)
{
// No digits so we will assume the line is a header
continue;
}
}
//Convert separator to a char
char Sep = ACSVSeparator[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 (IsShortFileFormat && (NumCols < 2))
{
// raise an error
string resultText = String.Format(Catalog.GetString("Failed to import the CSV currency file:{0} {1}{0}{0}"),
Environment.NewLine, ADataFilename);
resultText +=
String.Format(Catalog.GetString(
"Line #{1} contains {2} column(s). Import files with names like 'USD_HKD.csv', where the From and To currencies are given in the name, should contain 2 or 3 columns:{0}{0}"),
Environment.NewLine, LineNumber, NumCols.ToString());
resultText +=
String.Format(Catalog.GetString(
" 1. Effective Date{0} 2. Exchange Rate{0} 3. Effective time in seconds (Optional for Daily Rate only)"),
Environment.NewLine);
TVerificationResult result = new TVerificationResult(AImportMode,
resultText,
CommonErrorCodes.ERR_INFORMATIONMISSING,
TResultSeverity.Resv_Critical);
AResultCollection.Add(result);
return(RowsImported);
}
else if (!IsShortFileFormat && (NumCols < 4))
{
string resultText = String.Format(Catalog.GetString("Failed to import the CSV currency file:{0} {1}{0}{0}"),
Environment.NewLine, ADataFilename);
resultText += String.Format(Catalog.GetString("Line #{1} contains {2} column(s). It should have 4 or 5 as follows:{0}{0}"),
Environment.NewLine, LineNumber, NumCols.ToString());
resultText +=
String.Format(Catalog.GetString(
" 1. From Currency{0} 2. To Currency{0} 3. Effective Date{0} 4. Exchange Rate{0} 5. Effective time in seconds (Optional for Daily Rate only)"),
Environment.NewLine);
TVerificationResult result = new TVerificationResult(AImportMode,
resultText,
CommonErrorCodes.ERR_INFORMATIONMISSING,
TResultSeverity.Resv_Critical);
AResultCollection.Add(result);
return(RowsImported);
}
if (!IsShortFileFormat)
{
//Read the values for the current line
//From currency
Currencies[0] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString();
//To currency
Currencies[1] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString();
}
// Perform validation on the From and To currencies at this point!!
if ((allCurrencies.Rows.Find(Currencies[0]) == null) || (allCurrencies.Rows.Find(Currencies[1]) == null))
{
// raise an error
string resultText = String.Format(Catalog.GetString("Invalid currency in import file in line #{0}"), LineNumber.ToString());
TVerificationResult result = new TVerificationResult(AImportMode,
resultText,
CommonErrorCodes.ERR_INCONGRUOUSSTRINGS,
TResultSeverity.Resv_Critical);
AResultCollection.Add(result);
return(RowsImported);
}
// Date parsing as in Petra 2.x instead of using XML date format!!!
string DateEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace("\"", String.Empty);
DateTime DateEffective;
if (!DateTime.TryParse(DateEffectiveStr, MyCultureInfoDate, DateTimeStyles.None, out DateEffective))
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Invalid date ({0}) in import file in line #{1}"), DateEffectiveStr, LineNumber.ToString());
TVerificationResult result = new TVerificationResult(AImportMode,
resultText,
CommonErrorCodes.ERR_INVALIDDATE,
TResultSeverity.Resv_Critical);
AResultCollection.Add(result);
return(RowsImported);
}
decimal ExchangeRate = 0.0m;
try
{
string ExchangeRateString =
StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace(ThousandsSeparator, "").Replace(
DecimalSeparator, ".").Replace("\"", String.Empty);
ExchangeRate = Convert.ToDecimal(ExchangeRateString, System.Globalization.CultureInfo.InvariantCulture);
}
catch (Exception)
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Invalid rate of exchange in import file in line #{0}"), LineNumber.ToString());
TVerificationResult result = new TVerificationResult(AImportMode,
resultText,
CommonErrorCodes.ERR_INVALIDNUMBER,
TResultSeverity.Resv_Critical);
AResultCollection.Add(result);
return(RowsImported);
}
int TimeEffective = 7200;
if (AImportMode == "Daily")
{
// Daily rate imports can have an optional final column which is the time
// Otherwise we assume the time is a default of 7200 (02:00am)
if ((IsShortFileFormat && (NumCols == 3)) ||
(!IsShortFileFormat && (NumCols == 5)))
{
string timeEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true);
int t = (int)new Ict.Common.TypeConverter.TShortTimeConverter().ConvertTo(timeEffectiveStr, typeof(int));
if (t < 0)
{
// it wasn't in the format 02:00
if (!Int32.TryParse(timeEffectiveStr, out t))
{
// Not a regular Int32 either
t = -1;
}
}
if ((t >= 0) && (t < 86400))
{
TimeEffective = t;
}
else
{
// raise an error
string resultText = String.Format(Catalog.GetString(
"Invalid effective time in import file in line #{0}"), LineNumber.ToString());
TVerificationResult result = new TVerificationResult(AImportMode,
resultText,
CommonErrorCodes.ERR_INVALIDINTEGERTIME,
TResultSeverity.Resv_Critical);
AResultCollection.Add(result);
return(RowsImported);
}
}
}
if ((AImportMode == "Corporate") && AExchangeRDT is ACorporateExchangeRateTable)
{
ACorporateExchangeRateTable ExchangeRateDT = (ACorporateExchangeRateTable)AExchangeRDT;
// run this code in the loop twice to get ExchangeRate value and its inverse
for (int i = 0; i <= 1; i++)
{
//this will cause x and y to go from 0 to 1 and 1 to 0 respectively
x = i;
y = Math.Abs(i - 1);
ACorporateExchangeRateRow ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.Rows.
Find(new object[] { Currencies[x], Currencies[y], DateEffective });
if (ExchangeRow == null) // remove 0 for Corporate
{
ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.NewRowTyped();
ExchangeRow.FromCurrencyCode = Currencies[x];
ExchangeRow.ToCurrencyCode = Currencies[y];
ExchangeRow.DateEffectiveFrom = DateEffective;
ExchangeRateDT.Rows.Add(ExchangeRow);
RowsImported++;
}
if (i == 0)
{
ExchangeRow.RateOfExchange = ExchangeRate;
}
else
{
ExchangeRow.RateOfExchange = 1 / ExchangeRate;
}
}
}
else if ((AImportMode == "Daily") && AExchangeRDT is ADailyExchangeRateTable)
{
ADailyExchangeRateTable ExchangeRateDT = (ADailyExchangeRateTable)AExchangeRDT;
// run this code in the loop twice to get ExchangeRate value and its inverse
for (int i = 0; i <= 1; i++)
{
//this will cause x and y to go from 0 to 1 and 1 to 0 respectively
x = i;
y = Math.Abs(i - 1);
ADailyExchangeRateRow ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.Rows.
Find(new object[] { Currencies[x], Currencies[y], DateEffective, TimeEffective });
if (ExchangeRow == null) // remove 0 for Corporate
{
ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.NewRowTyped();
ExchangeRow.FromCurrencyCode = Currencies[x];
ExchangeRow.ToCurrencyCode = Currencies[y];
ExchangeRow.DateEffectiveFrom = DateEffective;
ExchangeRow.TimeEffectiveFrom = TimeEffective;
ExchangeRateDT.Rows.Add(ExchangeRow);
RowsImported++;
}
if (i == 0)
{
ExchangeRow.RateOfExchange = ExchangeRate;
}
else
{
ExchangeRow.RateOfExchange = 1 / ExchangeRate;
}
}
}
}
DataFile.Close();
return(RowsImported);
}
}
