/// <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;
}
}
/// <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);
}
}
/// <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 void NewRowManual(ref ACurrencyLanguageRow ARow)
{
// Deal with primary key. CurrencyCode (varchar(16)) and LanguageCode (varchar(20)) are unique.
if (FMainDS.ACurrencyLanguage.Rows.Count == 0)
{
// Our first row is always USD/EN
ARow.CurrencyCode = "USD";
ARow.LanguageCode = "EN";
return;
}
else
{
// We already have some rows, so we use the currently selected language in the comboBox as a starter
// This may or may not be the most recently added language depending on how the main data set has been sorted
// But once we have started adding rows it should remain the 'active' row
string prevLanguage = cmbDetailLanguageCode.cmbCombobox.Text;
// Try and find some popular or at least likely currencies
// Remember that we will almost certainly select a currency that the user does NOT want in their language
// So this will be the one we always propose!!!!
string[] tryCurrencies =
{
"USD", "GBP", "EUR", "INR", "AUD", "CAD", "CHF", "CNY", "JPY", "NZD", "PHP", "PKR", "ZAR"
};
int nTryCurrency = 0;
bool bFoundCurrency = true;
while (FMainDS.ACurrencyLanguage.Rows.Find(new object[] { tryCurrencies[nTryCurrency], prevLanguage }) != null)
{
if (++nTryCurrency == tryCurrencies.Length)
{
// We have exhausted our popular choices
bFoundCurrency = false;
break;
}
}
if (bFoundCurrency)
{
ARow.CurrencyCode = tryCurrencies[nTryCurrency];
ARow.LanguageCode = prevLanguage;
return;
}
// So we have tried all the popular currencies in the current language
// Now we fall back to trying all currencies in the current languages.
// Remember that we will almost certainly select a currency that the user does NOT want in their language
// So this will be the one we always propose!!!!
Type DataTableType;
ACurrencyTable allCurrencies = new ACurrencyTable();
DataTable CacheDT = TDataCache.GetCacheableDataTableFromCache("CurrencyCodeList", String.Empty, null, out DataTableType);
allCurrencies.Merge(CacheDT);
nTryCurrency = 0;
bFoundCurrency = true;
while (FMainDS.ACurrencyLanguage.Rows.Find(new object[] { allCurrencies.Rows[nTryCurrency][0].ToString(), prevLanguage }) != null)
{
if (++nTryCurrency == tryCurrencies.Length)
{
bFoundCurrency = false;
break;
}
}
if (bFoundCurrency)
{
ARow.CurrencyCode = allCurrencies.Rows[nTryCurrency][0].ToString();
ARow.LanguageCode = prevLanguage;
}
// We could at this point start trying other languages - but there seems little point since the currency list contains
// currencies that will never be used since they no longer exist!! It is therefore assumed that by this time
// the user has gone on to a different langauge where we will have started again with USD ....
}
}
private void NewRowManual(ref ACurrencyLanguageRow ARow)
{
// Deal with primary key. CurrencyCode (varchar(16)) and LanguageCode (varchar(20)) are unique.
if (FMainDS.ACurrencyLanguage.Rows.Count == 0)
{
// Our first row is always USD/EN
ARow.CurrencyCode = "USD";
ARow.LanguageCode = "EN";
return;
}
else
{
// We already have some rows, so we use the currently selected language in the comboBox as a starter
// This may or may not be the most recently added language depending on how the main data set has been sorted
// But once we have started adding rows it should remain the 'active' row
string prevLanguage = cmbDetailLanguageCode.cmbCombobox.Text;
// Try and find some popular or at least likely currencies
// Remember that we will almost certainly select a currency that the user does NOT want in their language
// So this will be the one we always propose!!!!
string[] tryCurrencies =
{
"USD", "GBP", "EUR", "INR", "AUD", "CAD", "CHF", "CNY", "JPY", "NZD", "PHP", "PKR", "ZAR"
};
int nTryCurrency = 0;
bool bFoundCurrency = true;
while (FMainDS.ACurrencyLanguage.Rows.Find(new object[] { tryCurrencies[nTryCurrency], prevLanguage }) != null)
{
if (++nTryCurrency == tryCurrencies.Length)
{
// We have exhausted our popular choices
bFoundCurrency = false;
break;
}
}
if (bFoundCurrency)
{
ARow.CurrencyCode = tryCurrencies[nTryCurrency];
ARow.LanguageCode = prevLanguage;
return;
}
// So we have tried all the popular currencies in the current language
// Now we fall back to trying all currencies in the current languages.
// Remember that we will almost certainly select a currency that the user does NOT want in their language
// So this will be the one we always propose!!!!
Type DataTableType;
ACurrencyTable allCurrencies = new ACurrencyTable();
DataTable CacheDT = TDataCache.GetCacheableDataTableFromCache("CurrencyCodeList", String.Empty, null, out DataTableType);
allCurrencies.Merge(CacheDT);
nTryCurrency = 0;
bFoundCurrency = true;
while (FMainDS.ACurrencyLanguage.Rows.Find(new object[] { allCurrencies.Rows[nTryCurrency][0].ToString(), prevLanguage }) != null)
{
if (++nTryCurrency == tryCurrencies.Length)
{
bFoundCurrency = false;
break;
}
}
if (bFoundCurrency)
{
ARow.CurrencyCode = allCurrencies.Rows[nTryCurrency][0].ToString();
ARow.LanguageCode = prevLanguage;
}
// We could at this point start trying other languages - but there seems little point since the currency list contains
// currencies that will never be used since they no longer exist!! It is therefore assumed that by this time
// the user has gone on to a different langauge where we will have started again with USD ....
}
}
/// <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);
}
}