private static void OnModifyEffectiveTime(ExchangeRateTDSADailyExchangeRateUsageTable AUsageTable,
String AFromCurrencyCode,
String AToCurrencyCode,
DateTime AEffectiveDate,
int AEffectiveTime,
int ANewEffectiveTime,
decimal ARateOfExchange)
{
DataView dv = new DataView(AUsageTable, String.Format("{0}='{1}' AND {2}='{3}' AND {4}=#{5}# AND {6}={7} AND {8}={9}",
ADailyExchangeRateTable.GetFromCurrencyCodeDBName(),
AFromCurrencyCode,
ADailyExchangeRateTable.GetToCurrencyCodeDBName(),
AToCurrencyCode,
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
AEffectiveDate.ToString("d", CultureInfo.InvariantCulture),
ADailyExchangeRateTable.GetTimeEffectiveFromDBName(),
AEffectiveTime,
ADailyExchangeRateTable.GetRateOfExchangeDBName(),
ARateOfExchange.ToString(CultureInfo.InvariantCulture)), "", DataViewRowState.CurrentRows);
foreach (DataRowView drv in dv)
{
ExchangeRateTDSADailyExchangeRateUsageRow row = (ExchangeRateTDSADailyExchangeRateUsageRow)drv.Row;
row.BeginEdit();
row.TimeEffectiveFrom = ANewEffectiveTime;
row.EndEdit();
}
}
/// <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);
}
}
/// <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);
}
}
}
public static bool SaveChanges()
{
TTypedDataTable TableChanges = ADailyExchangeRate.GetChangesTyped();
if (!SerialisableDS.SaveChanges(ADailyExchangeRate, TableChanges, ADailyExchangeRateTable.GetTableDBName()))
{
// throwing an exception because the return value is not verified in many places
throw new Exception("TDailyExchangeRateTest:FMainDS:SaveChanges: Could not save the changes. see log for details");
}
return(true);
}
private bool UpdateDailyExchangeRateTable(ADailyExchangeRateTable DailyExchangeTable, string AFromCurrencyCode, string AToCurrencyCode,
decimal AExchangeRate, DateTime AEffectiveDate)
{
string SortByTimeDescending = ADailyExchangeRateTable.GetTimeEffectiveFromDBName() + " DESC";
string filter = String.Format("{0}='{1}' AND {2}='{3}' AND {4}=#{5}#",
ADailyExchangeRateTable.GetFromCurrencyCodeDBName(), AFromCurrencyCode,
ADailyExchangeRateTable.GetToCurrencyCodeDBName(), AToCurrencyCode,
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
AEffectiveDate.ToString("d", CultureInfo.InvariantCulture));
DataView dvTo = new DataView(DailyExchangeTable, filter, SortByTimeDescending, DataViewRowState.CurrentRows);
bool foundMatch = false;
for (int i = 0; i < dvTo.Count; i++)
{
if (((ADailyExchangeRateRow)dvTo[i].Row).RateOfExchange == AExchangeRate)
{
foundMatch = true;
break;
}
}
if (!foundMatch)
{
int newTime = 3600;
if (dvTo.Count > 0)
{
newTime = ((ADailyExchangeRateRow)dvTo[0].Row).TimeEffectiveFrom + 600;
}
ADailyExchangeRateRow newRow = DailyExchangeTable.NewRowTyped();
newRow.DateEffectiveFrom = AEffectiveDate;
newRow.FromCurrencyCode = AFromCurrencyCode;
newRow.ToCurrencyCode = AToCurrencyCode;
newRow.TimeEffectiveFrom = newTime;
newRow.RateOfExchange = AExchangeRate;
DailyExchangeTable.Rows.Add(newRow);
return true;
}
return false;
}
public static TSubmitChangesResult SaveData(string ATablename,
ref TTypedDataTable ASubmitTable,
out TVerificationResultCollection AVerificationResult)
{
TDBTransaction SubmitChangesTransaction = null;
bool SubmissionOK = false;
TTypedDataTable SubmitTable = ASubmitTable;
TVerificationResultCollection VerificationResult = null;
// TODO: check write permissions
if (ASubmitTable != null)
{
VerificationResult = new TVerificationResultCollection();
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref SubmitChangesTransaction, ref SubmissionOK,
delegate
{
try
{
if (ATablename == AAccountingPeriodTable.GetTableDBName())
{
AAccountingPeriodAccess.SubmitChanges((AAccountingPeriodTable)SubmitTable, SubmitChangesTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
ACurrencyAccess.SubmitChanges((ACurrencyTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
ADailyExchangeRateAccess.SubmitChanges((ADailyExchangeRateTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
ACorporateExchangeRateAccess.SubmitChanges((ACorporateExchangeRateTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
ACurrencyLanguageAccess.SubmitChanges((ACurrencyLanguageTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
AFeesPayableAccess.SubmitChanges((AFeesPayableTable)SubmitTable, SubmitChangesTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.FeesPayableList.ToString());
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
AFeesReceivableAccess.SubmitChanges((AFeesReceivableTable)SubmitTable, SubmitChangesTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.FeesReceivableList.ToString());
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Setup - please do not remove
// The method is not required for changes made to the gift batch screens, which use a TDS
AGiftBatchAccess.SubmitChanges((AGiftBatchTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Setup - please do not remove
// The method is not required for changes made to the journal screens, which use a TDS
AJournalAccess.SubmitChanges((AJournalTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ARecurringJournalTable.GetTableDBName())
{
// This method is called from Submit Recurring GL Batch form - please do not remove
// The method is not required for changes made to the journal screens, which use a TDS
ARecurringJournalAccess.SubmitChanges((ARecurringJournalTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Testing - please do not remove
ALedgerAccess.SubmitChanges((ALedgerTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
AAnalysisTypeAccess.SubmitChanges((AAnalysisTypeTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == ASuspenseAccountTable.GetTableDBName())
{
ASuspenseAccountAccess.SubmitChanges((ASuspenseAccountTable)SubmitTable, SubmitChangesTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.SuspenseAccountList.ToString());
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
PcAttendeeAccess.SubmitChanges((PcAttendeeTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PcConferenceTable.GetTableDBName())
{
PcConferenceAccess.SubmitChanges((PcConferenceTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
PcConferenceCostAccess.SubmitChanges((PcConferenceCostTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
PcEarlyLateAccess.SubmitChanges((PcEarlyLateTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
PcSupplementAccess.SubmitChanges((PcSupplementTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
PcDiscountAccess.SubmitChanges((PcDiscountTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
ValidateInternationalPostalType(ref VerificationResult, SubmitTable);
ValidateInternationalPostalTypeManual(ref VerificationResult, SubmitTable);
if (TVerificationHelper.IsNullOrOnlyNonCritical(VerificationResult))
{
PInternationalPostalTypeAccess.SubmitChanges((PInternationalPostalTypeTable)SubmitTable, SubmitChangesTransaction);
}
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
PtApplicationTypeAccess.SubmitChanges((PtApplicationTypeTable)SubmitTable, SubmitChangesTransaction);
// mark dependent lists for needing to be refreshed since there was a change in base list
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheablePersonTablesEnum.EventApplicationTypeList.ToString());
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheablePersonTablesEnum.FieldApplicationTypeList.ToString());
}
else if (ATablename == PFormTable.GetTableDBName())
{
PFormAccess.SubmitChanges((PFormTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PFormalityTable.GetTableDBName())
{
PFormalityAccess.SubmitChanges((PFormalityTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
PMailingAccess.SubmitChanges((PMailingTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
PPartnerGiftDestinationAccess.SubmitChanges((PPartnerGiftDestinationTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
PmDocumentTypeAccess.SubmitChanges((PmDocumentTypeTable)SubmitTable, SubmitChangesTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
SGroupAccess.SubmitChanges((SGroupTable)SubmitTable, SubmitChangesTransaction);
}
else
{
throw new EOPAppException("TCommonDataReader.SaveData: unknown table '" + ATablename + "'");
}
SubmissionOK = true;
}
catch (Exception Exc)
{
VerificationResult.Add(
new TVerificationResult(null, "Cannot SubmitChanges:" + Environment.NewLine +
Exc.Message, "UNDEFINED", TResultSeverity.Resv_Critical));
}
});
}
ASubmitTable = SubmitTable;
AVerificationResult = VerificationResult;
if ((AVerificationResult != null) &&
(AVerificationResult.Count > 0))
{
// Downgrade TScreenVerificationResults to TVerificationResults in order to allow
// Serialisation (needed for .NET Remoting).
TVerificationResultCollection.DowngradeScreenVerificationResults(AVerificationResult);
return(AVerificationResult.HasCriticalErrors ? TSubmitChangesResult.scrError : TSubmitChangesResult.scrOK);
}
return(TSubmitChangesResult.scrOK);
}
/// <summary>
/// This method is called when clients access the Daily Exchange Rate data.
/// The Daily Exchange Rate table is unusual in that it doesn't really need to hold any data because the DataSet that the client receives
/// contains all the used rates from the GL/Gift tables whether or not those rates are in the DER table itself. Any rates in the DER table
/// that are NOT used are also returned, but, of course, because they are not used anywhere they are not very inetresting!
/// Additionally, because the GL/Gift tables do not necessarily hold a time or a time that matches the same rate in the DER table, it is possible
/// for the DER table to have a rate that is used on the date but at a different time. As a result the client sometimes does not see all rows
/// from the DER table - and so has no way of deleting them.
///
/// That is the reason why we need to automatically clean the table.
///
/// But there is some value in having some 'unused' rows that are work-in-progress. So we delete everything in the DER table that
/// applies to dates older than 30 days. In the future this might become a configurable server option.
/// </summary>
private static void DoDailyExchangeRateClean()
{
if ((DateTime.UtcNow - PreviousDailyExchangeRateAccessTime).TotalHours > 8)
{
// Nobody has opened a DailyExchangeRate screen for 8 hours
if ((DateTime.UtcNow - PreviousDailyExchangeRateCleanTime).TotalHours > 24)
{
// It is more than 24 hours since our last clean
TDBTransaction t = null;
bool bSubmissionOk = false;
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref t, ref bSubmissionOk,
delegate
{
string logMsg = String.Empty;
int affectedRowCount = 0;
// Standard is that we delete rows applicable to dates more than 60 days old
string criticalDate = DateTime.Now.AddDays(-60).ToString("yyyy-MM-dd");
try
{
// Our deletion rule is to delete rows where
// either the effective date is too old and we have no info about creation or modification
// or the creation date is too old and we have no info about any modification
// or the modification date is too old
// These rules ensure that if rates are added to a DB that is past its last accounting period (like SA-DB)
// we can still continue to use the DER screen to add unused rates because they will have create/modify times
// that can be long past the final accounting period because we will keep
// any row that has been modified recently, whatever the effective date or creation date
// any row that was created recently but not subsequently modified, whatever the effective date
// any row where we don't have info about create/modify but where the effective date is recent
string sql = String.Format(
"DELETE FROM PUB_{0} WHERE (({1}<'{2}') and {3} is NULL and {4} is NULL) or (({3}<'{2}') and {4} is NULL) or ({4}<'{2}')",
ADailyExchangeRateTable.GetTableDBName(),
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
criticalDate,
ADailyExchangeRateTable.GetDateCreatedDBName(),
ADailyExchangeRateTable.GetDateModifiedDBName());
affectedRowCount = DBAccess.GDBAccessObj.ExecuteNonQuery(sql, t);
bSubmissionOk = true;
PreviousDailyExchangeRateCleanTime = DateTime.UtcNow;
}
catch (Exception ex)
{
logMsg = "An error occurred while trying to purge the Daily Exchange Rate table of 'aged' rows.";
logMsg += String.Format(" The exception message was: {0}", ex.Message);
}
if ((affectedRowCount > 0) && (logMsg == String.Empty))
{
logMsg =
String.Format("The Daily Exchange Rate table was purged of {0} entries applicable prior to ",
affectedRowCount) + criticalDate;
}
if (logMsg != String.Empty)
{
TLogging.Log(logMsg);
}
});
}
}
PreviousDailyExchangeRateAccessTime = DateTime.UtcNow;
}
public static ExchangeRateTDS LoadDailyExchangeRateData(bool ADeleteAgedExchangeRatesFirst, DateTime AFromDate, DateTime AToDate)
{
// If relevant, we do a clean of the data table first, purging 'aged' data
if (ADeleteAgedExchangeRatesFirst)
{
// We clean up the DER table unless there is an app setting in the server configuration
// If you want to set this as a developer you create a copy of /inc/template/etc/Server-postgresql.config
// and rename it to Server-postgresql.config.my. Then add a new <add> element with this value set to true.
// Then (re)start the server using nant or OPDA, which will generate the working copy of this file.
if (!TAppSettingsManager.GetBoolean("KeepAgedExchangeRates", false))
{
DoDailyExchangeRateClean();
}
}
ExchangeRateTDS WorkingDS = new ExchangeRateTDS();
WorkingDS.EnforceConstraints = false;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
// Populate the ExchangeRateTDSADailyExchangeRate table
//-- This is the complete query for the DAILYEXCHANGERATE TABLE
//-- It returns all rows from the Journal and Gift Batch tables
//-- PLUS all the rows from the DailyExchangeRate table that are NOT referenced by the Journal and Gift Batch tables.
string strSQL = "SELECT * FROM ";
strSQL += "( ";
// This returns all the rows in Daily Exchange rate that do NOT match any journal or gift
strSQL += "SELECT ";
strSQL += String.Format(
" 0 AS {0}, 0 AS {1}, 'DER' AS {2}, ",
ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
strSQL += " der.* ";
strSQL += "FROM PUB_a_daily_exchange_rate AS der ";
// By doing a left join and only selecting the NULL rows we get the rows from DER that are NOT used
strSQL += "LEFT JOIN ";
strSQL += "( ";
// This SELECT returns all the used rows (372 rows in the case of SA-DB)
strSQL += "SELECT ";
strSQL += " j.a_batch_number_i AS a_batch_number_i, ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_journal AS j ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " j.a_transaction_currency_c <> ldg.a_base_currency_c ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " j.a_batch_number_i AS a_batch_number_i, ";
strSQL += " r.a_revaluation_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " r.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM a_journal AS j ";
strSQL += "JOIN a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_revaluation r ON ";
strSQL +=
" r.a_ledger_number_i = j.a_ledger_number_i AND r.a_batch_number_i=j.a_batch_number_i AND r.a_journal_number_i=j.a_journal_number_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " gb.a_batch_number_i AS a_batch_number_i, ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_gift_batch AS gb ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += ") AS j_and_gb ";
strSQL += "ON ";
strSQL += " der.a_from_currency_code_c = j_and_gb.a_from_currency_code_c ";
strSQL += " AND der.a_to_currency_code_c = j_and_gb.a_to_currency_code_c ";
strSQL += " AND der.a_date_effective_from_d = j_and_gb.a_date_effective_from_d ";
strSQL += " AND der.a_rate_of_exchange_n = j_and_gb.a_rate_of_exchange_n ";
strSQL += "WHERE ";
strSQL += " a_batch_number_i IS NULL ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
// The second half of the UNION returns all the Forex rows from journal and gift
// They are aggregated by from/to/date/rate and the time is the min time.
// We also get the usage count as well as whether the row originated in the DER table or one of gift or batch
strSQL += "SELECT ";
strSQL += String.Format(
" sum(journalUsage) AS {0}, sum(giftBatchUsage) AS {1}, 'GBJ' AS {2}, ",
ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
strSQL += " a_from_currency_code_c, ";
strSQL += " a_to_currency_code_c, ";
strSQL += " a_rate_of_exchange_n, ";
strSQL += " a_date_effective_from_d, ";
strSQL += " min(a_time_effective_from_i), ";
strSQL += " NULL AS s_date_created_d, ";
strSQL += " NULL AS s_created_by_c, ";
strSQL += " NULL AS s_date_modified_d, ";
strSQL += " NULL AS s_modified_by_c, ";
strSQL += " NULL AS s_modification_id_t ";
strSQL += "FROM ";
strSQL += "( ";
// These are all the used rows again (same as part of the query above) but this time we can count the usages from the two tables
strSQL += "SELECT ";
strSQL += " 1 AS journalUsage, ";
strSQL += " 0 AS giftBatchUsage, ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_journal AS j ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " j.a_transaction_currency_c <> ldg.a_base_currency_c ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " 1 AS journalUsage, ";
strSQL += " 0 AS giftBatchUsage, ";
strSQL += " r.a_revaluation_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += " r.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM a_journal AS j ";
strSQL += "JOIN a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_revaluation r ON ";
strSQL +=
" r.a_ledger_number_i = j.a_ledger_number_i AND r.a_batch_number_i=j.a_batch_number_i AND r.a_journal_number_i=j.a_journal_number_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ALL ";
strSQL += Environment.NewLine;
strSQL += "SELECT ";
strSQL += " 0 AS journalUsage, ";
strSQL += " 1 AS giftBatchUsage, ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " 0 AS a_time_effective_from_i, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n ";
strSQL += "FROM PUB_a_gift_batch AS gb ";
strSQL += "JOIN PUB_a_ledger AS ldg ON ";
strSQL += " ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "WHERE ";
strSQL += " gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += ") AS j_and_gb ";
// GROUP the second half of the query (the UNION of used rates)
strSQL += "GROUP BY ";
strSQL += " a_from_currency_code_c, ";
strSQL += " a_to_currency_code_c, ";
strSQL += " a_date_effective_from_d, ";
strSQL += " a_rate_of_exchange_n ";
strSQL += ") AS all_rates ";
strSQL += ((AFromDate < DateTime.MaxValue) && (AToDate < DateTime.MaxValue)) ?
String.Format(" WHERE all_rates.{0}>='{1}' AND all_rates.{0}<='{2}' ",
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
AFromDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
AToDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)) :
String.Empty;
// ORDER of the outermost SELECT
strSQL += "ORDER BY ";
strSQL += " a_to_currency_code_c, ";
strSQL += " a_from_currency_code_c, ";
strSQL += " a_date_effective_from_d DESC, ";
strSQL += " a_time_effective_from_i DESC ";
DBAccess.GDBAccessObj.Select(WorkingDS, strSQL, WorkingDS.ADailyExchangeRate.TableName, Transaction);
// Now populate the ExchangeRateTDSADailyExchangerateUsage table
//-- COMPLETE QUERY TO RETURN ADailyExchangeRateUsage
//-- Query to return the Daily Exchange Rate Usage details
//-- Only returns rows that are in a foreign currency
//-- Querying this table by from/to/date/time will return one row per use case
//-- If the Journal is 0 the batch refers to a gift batch, otherwise it is a GL batch
strSQL = "SELECT * FROM ( ";
//-- This part of the query returns the use cases from the Journal table
strSQL += "SELECT ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += String.Format(
" j.a_ledger_number_i AS {0}, j.a_batch_number_i AS {1}, j.a_journal_number_i AS {2}, b.a_batch_status_c AS {3}, j.a_journal_description_c AS {4}, b.a_batch_year_i AS {5}, b.a_batch_period_i AS {6}, 'J' AS {7} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetDescriptionDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_journal j ";
strSQL += "JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_batch b ";
strSQL += " ON b.a_batch_number_i = j.a_batch_number_i ";
strSQL += " AND b.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "WHERE j.a_transaction_currency_c <> ldg.a_base_currency_c ";
strSQL += Environment.NewLine;
strSQL += "UNION ";
strSQL += Environment.NewLine;
//-- This part of the query returns the revaluation rows
strSQL += "SELECT ";
strSQL += " r.a_revaluation_currency_c as a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " r.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += String.Format(
" j.a_ledger_number_i AS {0}, j.a_batch_number_i AS {1}, j.a_journal_number_i AS {2}, b.a_batch_status_c AS {3}, j.a_journal_description_c AS {4}, b.a_batch_year_i AS {5}, b.a_batch_period_i AS {6}, 'J' AS {7} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetDescriptionDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_journal j ";
strSQL += "JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_batch b ";
strSQL += " ON b.a_batch_number_i = j.a_batch_number_i ";
strSQL += " AND b.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "JOIN a_revaluation r ";
strSQL +=
" ON r.a_ledger_number_i = j.a_ledger_number_i AND r.a_batch_number_i=j.a_batch_number_i AND r.a_journal_number_i=j.a_journal_number_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ";
strSQL += Environment.NewLine;
//-- This part of the query returns the use cases from the Gift Batch table
strSQL += "SELECT ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " 0 AS a_time_effective_from_i, ";
strSQL += String.Format(
" gb.a_ledger_number_i AS {0}, gb.a_batch_number_i AS {1}, 0 AS {2}, gb.a_batch_status_c AS {3}, gb.a_batch_description_c AS {4}, gb.a_batch_year_i AS {5}, gb.a_batch_period_i AS {6}, 'GB' AS {7} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetDescriptionDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_gift_batch gb ";
strSQL += "JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "WHERE gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += ") AS usage ";
strSQL += ((AFromDate < DateTime.MaxValue) && (AToDate < DateTime.MaxValue)) ?
String.Format(" WHERE usage.{0}>='{1}' AND usage.{0}<='{2}' ",
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
AFromDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
AToDate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)) :
String.Empty;
strSQL += "ORDER BY usage.a_date_effective_from_d DESC, usage.a_time_effective_from_i DESC ";
DBAccess.GDBAccessObj.Select(WorkingDS, strSQL, WorkingDS.ADailyExchangeRateUsage.TableName, Transaction);
// Now we start a tricky bit to resolve potential primary key conflicts when the constraints are turned on.
// By combining the Journal and Gift Batch data that is not referenced in the exchange rate table we can easily
// have introduced conflicts where more than one rate has been used for a given currency pair and effective date/time.
// This is because there is no constraint that enforces the batch/journal tables to use a time from the exch rate table.
// So we have to go through all the rows in our data table and potentially change the time to make it possible to get our primary key.
// Start by creating a data view on the whole result set. The ordering is important because we are going to step through the set row by row.
// Within one group of from/to/date it is essential that the first 'source' is the DER table because we don't change the time on that one -
// and of course that must stay the same because the user can modify that one.
// We need to deal with the following possibilities:
// From To Date Time Source Rate
// EUR GBP 2014-01-01 1234 DER 2.11
// EUR GBP 2014-01-01 1234 GBJ 2.115
// EUR GBP 2014-01-01 1234 GBJ 2.22
// EUR GBP 2014-01-01 1234 GBJ 3.11
//
// In the first row we have an entry from the DER table that is not used anywhere, but a (slightly) different rate is actually used
// in a Journal.
// In the other rows we have 3 different rates - all used somewhere. We need to adjust the times so they are different.
DataView dv = new DataView(WorkingDS.ADailyExchangeRate, "",
String.Format("{0}, {1}, {2} DESC, {3} DESC, {4}, {5}",
ADailyExchangeRateTable.GetFromCurrencyCodeDBName(),
ADailyExchangeRateTable.GetToCurrencyCodeDBName(),
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
ADailyExchangeRateTable.GetTimeEffectiveFromDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName(),
ADailyExchangeRateTable.GetRateOfExchangeDBName()), DataViewRowState.CurrentRows);
for (int i = 0; i < dv.Count - 1; i++)
{
// Get the 'current' row and the 'next' one...
ExchangeRateTDSADailyExchangeRateRow drThis = (ExchangeRateTDSADailyExchangeRateRow)dv[i].Row;
ExchangeRateTDSADailyExchangeRateRow drNext = (ExchangeRateTDSADailyExchangeRateRow)dv[i + 1].Row;
if (!drThis.FromCurrencyCode.Equals(drNext.FromCurrencyCode) ||
!drThis.ToCurrencyCode.Equals(drNext.ToCurrencyCode) ||
!drThis.DateEffectiveFrom.Equals(drNext.DateEffectiveFrom) ||
!drThis.TimeEffectiveFrom.Equals(drNext.TimeEffectiveFrom))
{
// Something is different so our primary key will be ok for the current row
continue;
}
// We have got two (or more) rows with the same potential primary key and different rates/usages.
// We need to work out how many rows ahead also have the same time and adjust them all
bool moveForwards = (drThis.TimeEffectiveFrom < 43200);
int timeOffset = 60; // 1 minute
// Start by adjusting our 'next' row we are already working with
drNext.BeginEdit();
int prevTimeEffectiveFrom = drNext.TimeEffectiveFrom;
drNext.TimeEffectiveFrom = (moveForwards) ? prevTimeEffectiveFrom + timeOffset : prevTimeEffectiveFrom - timeOffset;
timeOffset = (moveForwards) ? timeOffset + 60 : timeOffset - 60;
drNext.EndEdit();
i++; // we can increment our main loop counter now that we have dealt with our 'next' row.
TLogging.LogAtLevel(2, String.Format("Modifying {0} row: From {1}, To {2}, Date {3}, Time {4}, new Time {5}",
drThis.TableSource, drThis.FromCurrencyCode, drThis.ToCurrencyCode, drThis.DateEffectiveFrom.ToString("yyyy-MM-dd"),
prevTimeEffectiveFrom, drNext.TimeEffectiveFrom), TLoggingType.ToLogfile);
// Modify all the rows in the usage table that refer to the previous time
OnModifyEffectiveTime(WorkingDS.ADailyExchangeRateUsage, drNext.FromCurrencyCode, drNext.ToCurrencyCode,
drNext.DateEffectiveFrom,
prevTimeEffectiveFrom, drNext.TimeEffectiveFrom, drNext.RateOfExchange);
// Now look ahead even further than the 'next' row and modify those times too, adding 1 more minute to each
for (int k = i + 1; k < dv.Count; k++)
{
ExchangeRateTDSADailyExchangeRateRow drLookAhead = (ExchangeRateTDSADailyExchangeRateRow)dv[k].Row;
if (!drThis.FromCurrencyCode.Equals(drLookAhead.FromCurrencyCode) ||
!drThis.ToCurrencyCode.Equals(drLookAhead.ToCurrencyCode) ||
!drThis.DateEffectiveFrom.Equals(drLookAhead.DateEffectiveFrom) ||
!drThis.TimeEffectiveFrom.Equals(drLookAhead.TimeEffectiveFrom))
{
// No more rows match our potential primary key conflict on the 'current' row.
break;
}
// Do exactly the same to this row as we did to the 'next' row above
drLookAhead.BeginEdit();
prevTimeEffectiveFrom = drLookAhead.TimeEffectiveFrom;
drLookAhead.TimeEffectiveFrom = (moveForwards) ? prevTimeEffectiveFrom + timeOffset : prevTimeEffectiveFrom - timeOffset;
timeOffset = (moveForwards) ? timeOffset + 60 : timeOffset - 60;
drLookAhead.EndEdit();
i++;
TLogging.LogAtLevel(2, String.Format("Modifying additional {0} row: From {1}, To {2}, Date {3}, Time {4}, new Time {5}",
drThis.TableSource, drThis.FromCurrencyCode, drThis.ToCurrencyCode,
drThis.DateEffectiveFrom.ToString("yyyy-MM-dd"),
prevTimeEffectiveFrom, drLookAhead.TimeEffectiveFrom), TLoggingType.ToLogfile);
OnModifyEffectiveTime(WorkingDS.ADailyExchangeRateUsage, drLookAhead.FromCurrencyCode, drLookAhead.ToCurrencyCode,
drLookAhead.DateEffectiveFrom, prevTimeEffectiveFrom, drLookAhead.TimeEffectiveFrom, drLookAhead.RateOfExchange);
}
} // check the next row in the table so that it becomes the 'current' row.
WorkingDS.EnforceConstraints = true;
// We only load the following data if we are returning ALL exchange rate data
if ((AFromDate == DateTime.MaxValue) && (AToDate == DateTime.MaxValue))
{
// Load the Corporate exchange rate table using the usual method
ACorporateExchangeRateAccess.LoadAll(WorkingDS, Transaction);
// Load the daily exchange rate table as the 'raw' table. The client needs this for adding new rows to check for constraints.
// Note: April 2015. The MissingSchemaAction was added because SQLite gave a mismatched DataType on a_effective_time_i.
// As a result the GUI tests failed on SQLite - as well as the screen not loading(!)
// There should be no difference with PostgreSQL, which worked fine without the parameter.
WorkingDS.ARawDailyExchangeRate.Merge(DBAccess.GDBAccessObj.SelectDT("SELECT *, 0 AS Unused FROM PUB_a_daily_exchange_rate",
"a_raw_daily_exchange_rate",
Transaction), false, MissingSchemaAction.Ignore);
strSQL = "SELECT ";
strSQL += " a_ledger_number_i, ";
strSQL += " a_ledger_status_l, ";
strSQL += " max(a_ledger_name_c) AS a_ledger_name_c, ";
strSQL += " max(a_base_currency_c) AS a_base_currency_c, ";
strSQL += " max(a_intl_currency_c) AS a_intl_currency_c, ";
strSQL += " max(a_current_financial_year_i) AS a_current_financial_year_i, ";
strSQL += " max(a_current_period_i) AS a_current_period_i, ";
strSQL += " max(a_number_of_accounting_periods_i) AS a_number_of_accounting_periods_i, ";
strSQL += " max(a_number_fwd_posting_periods_i) AS a_number_fwd_posting_periods_i, ";
strSQL += " min(CurrentPeriodStartDate) AS CurrentPeriodStartDate, ";
strSQL += " max(CurrentPeriodEndDate) AS CurrentPeriodEndDate, ";
strSQL += " max(ForwardPeriodEndDate) AS ForwardPeriodEndDate ";
strSQL += "FROM ";
strSQL += "( ";
strSQL +=
"SELECT ldg.*, pd.a_period_start_date_d AS CurrentPeriodStartDate, pd.a_period_end_date_d AS CurrentPeriodEndDate, NULL AS ForwardPeriodEndDate ";
strSQL += "FROM a_ledger ldg ";
strSQL += "JOIN a_accounting_period pd ";
strSQL += "ON ldg.a_ledger_number_i=pd.a_ledger_number_i and ldg.a_current_period_i=pd.a_accounting_period_number_i ";
strSQL += "UNION ";
strSQL +=
"SELECT ldg.*, pd.a_period_start_date_d AS CurrentPeriodStartDate, NULL AS CurrentPeriodEndDate, pd.a_period_end_date_d AS ForwardPeriodEndDate ";
strSQL += "FROM a_ledger ldg ";
strSQL += "JOIN a_accounting_period pd ";
strSQL +=
"ON ldg.a_ledger_number_i=pd.a_ledger_number_i and (ldg.a_current_period_i + a_number_fwd_posting_periods_i)=pd.a_accounting_period_number_i ";
strSQL += ") AS all_info ";
strSQL += "GROUP BY a_ledger_number_i, a_ledger_status_l ";
DBAccess.GDBAccessObj.Select(WorkingDS, strSQL, WorkingDS.ALedgerInfo.TableName, Transaction);
}
});
// Accept row changes here so that the Client gets 'unmodified' rows
WorkingDS.AcceptChanges();
return(WorkingDS);
}
/// <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);
}
}
public static void LoadAll()
{
ADailyExchangeRate = new ADailyExchangeRateTable();
SerialisableDS.LoadAll(ADailyExchangeRate, ADailyExchangeRateTable.GetTableDBName());
}
public static TSubmitChangesResult SaveData(string ATablename,
ref TTypedDataTable ASubmitTable, out TVerificationResultCollection AVerificationResult,
TDBTransaction AWriteTransaction)
{
AVerificationResult = null;
// TODO: check write permissions
string context = string.Format("SaveData {0}", SharedConstants.MODULE_ACCESS_MANAGER);
if (ASubmitTable != null)
{
AVerificationResult = new TVerificationResultCollection();
try
{
if (ATablename == AAccountingPeriodTable.GetTableDBName())
{
AAccountingPeriodAccess.SubmitChanges((AAccountingPeriodTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
ACurrencyAccess.SubmitChanges((ACurrencyTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
TSecurityChecks.CheckUserModulePermissions(
string.Format("AND({0},{1})", SharedConstants.PETRAGROUP_FINANCE1, SharedConstants.PETRAMODULE_FINEXRATE),
context);
ADailyExchangeRateAccess.SubmitChanges((ADailyExchangeRateTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
// AlanP: I don't think this is used any more. There is a TDS Save method instead
ACorporateExchangeRateAccess.SubmitChanges((ACorporateExchangeRateTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
ACurrencyLanguageAccess.SubmitChanges((ACurrencyLanguageTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
AFeesPayableAccess.SubmitChanges((AFeesPayableTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.FeesPayableList.ToString());
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
AFeesReceivableAccess.SubmitChanges((AFeesReceivableTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.FeesReceivableList.ToString());
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Setup - please do not remove
// The method is not required for changes made to the gift batch screens, which use a TDS
AGiftBatchAccess.SubmitChanges((AGiftBatchTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Setup - please do not remove
// The method is not required for changes made to the journal screens, which use a TDS
AJournalAccess.SubmitChanges((AJournalTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ARecurringJournalTable.GetTableDBName())
{
// This method is called from Submit Recurring GL Batch form - please do not remove
// The method is not required for changes made to the journal screens, which use a TDS
ARecurringJournalAccess.SubmitChanges((ARecurringJournalTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
// This method is called from ADailyExchangeRate Testing - please do not remove
ALedgerAccess.SubmitChanges((ALedgerTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
AAnalysisTypeAccess.SubmitChanges((AAnalysisTypeTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == ASuspenseAccountTable.GetTableDBName())
{
ASuspenseAccountAccess.SubmitChanges((ASuspenseAccountTable)ASubmitTable, AWriteTransaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.SuspenseAccountList.ToString());
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
PcAttendeeAccess.SubmitChanges((PcAttendeeTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcConferenceTable.GetTableDBName())
{
PcConferenceAccess.SubmitChanges((PcConferenceTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
PcConferenceCostAccess.SubmitChanges((PcConferenceCostTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
PcEarlyLateAccess.SubmitChanges((PcEarlyLateTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
PcSupplementAccess.SubmitChanges((PcSupplementTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
PcDiscountAccess.SubmitChanges((PcDiscountTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
ValidateInternationalPostalType(ref AVerificationResult, ASubmitTable);
ValidateInternationalPostalTypeManual(ref AVerificationResult, ASubmitTable);
if (TVerificationHelper.IsNullOrOnlyNonCritical(AVerificationResult))
{
PInternationalPostalTypeAccess.SubmitChanges((PInternationalPostalTypeTable)ASubmitTable, AWriteTransaction);
}
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
PtApplicationTypeAccess.SubmitChanges((PtApplicationTypeTable)ASubmitTable, AWriteTransaction);
// mark dependent lists for needing to be refreshed since there was a change in base list
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheablePersonTablesEnum.EventApplicationTypeList.ToString());
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheablePersonTablesEnum.FieldApplicationTypeList.ToString());
}
else if (ATablename == PFormTable.GetTableDBName())
{
PFormAccess.SubmitChanges((PFormTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PFormalityTable.GetTableDBName())
{
PFormalityAccess.SubmitChanges((PFormalityTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
PMailingAccess.SubmitChanges((PMailingTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
PPartnerGiftDestinationAccess.SubmitChanges((PPartnerGiftDestinationTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
PmDocumentTypeAccess.SubmitChanges((PmDocumentTypeTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
SGroupAccess.SubmitChanges((SGroupTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == SSystemDefaultsTable.GetTableDBName())
{
SSystemDefaultsAccess.SubmitChanges((SSystemDefaultsTable)ASubmitTable, AWriteTransaction);
}
else if (ATablename == SSystemDefaultsGuiTable.GetTableDBName())
{
SSystemDefaultsGuiAccess.SubmitChanges((SSystemDefaultsGuiTable)ASubmitTable, AWriteTransaction);
}
else
{
throw new EOPAppException("TCommonDataReader.SaveData: unknown table '" + ATablename + "'");
}
}
catch (Exception Exc)
{
AVerificationResult.Add(
new TVerificationResult(null, "Cannot SubmitChanges:" + Environment.NewLine +
Exc.Message, "UNDEFINED", TResultSeverity.Resv_Critical));
}
}
if ((AVerificationResult != null) &&
(AVerificationResult.Count > 0))
{
// Downgrade TScreenVerificationResults to TVerificationResults in order to allow
// Serialisation (needed for .NET Remoting).
TVerificationResultCollection.DowngradeScreenVerificationResults(AVerificationResult);
return(AVerificationResult.HasCriticalErrors ? TSubmitChangesResult.scrError : TSubmitChangesResult.scrOK);
}
return(TSubmitChangesResult.scrOK);
}
/// <summary>
/// Gets the best (latest) exchange rate for a pair of currencies from a table of rates.
/// </summary>
/// <param name="ADailyRateTable">A table of rates that has been obtained by calling LoadExchangeRateData for a period of interest.</param>
/// <param name="ACurrencyFrom">The 'from' currency</param>
/// <param name="ACurrencyTo">The 'to' currency</param>
/// <param name="AEnforceUniqueRate">If true the method will only return true if there is a unique rate for the specified currencies on the 'latest' date.
/// If false the method returns the rate for the latest 'time' on the latest date.</param>
/// <param name="ARateOfExchange">The returned rate</param>
/// <param name="AEffectiveDate">The date associated with the returned rate.</param>
/// <returns>True if a matching rate was found. The rate and date are in the out parameters.</returns>
public static bool GetBestExchangeRate(ExchangeRateTDSADailyExchangeRateTable ADailyRateTable,
string ACurrencyFrom,
string ACurrencyTo,
Boolean AEnforceUniqueRate,
out decimal ARateOfExchange,
out DateTime AEffectiveDate)
{
ARateOfExchange = 0.0m;
AEffectiveDate = DateTime.MinValue;
ADailyExchangeRateRow uniqueFittingRow = null;
bool oppositeRate = false;
// sort rates by date, look for rate just before the date we are looking for
string rowFilter = String.Format("{0}='{1}' AND {2}='{3}'",
ADailyExchangeRateTable.GetFromCurrencyCodeDBName(),
ACurrencyFrom,
ADailyExchangeRateTable.GetToCurrencyCodeDBName(),
ACurrencyTo);
ADailyRateTable.DefaultView.RowFilter = rowFilter;
ADailyRateTable.DefaultView.Sort = String.Format("{0} DESC, {1} DESC",
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
ADailyExchangeRateTable.GetTimeEffectiveFromDBName());
// If we are after a unique row we need to remember the one from this view, if it exists
if (AEnforceUniqueRate && (ADailyRateTable.DefaultView.Count == 1))
{
uniqueFittingRow = (ADailyExchangeRateRow)ADailyRateTable.DefaultView[0].Row;
}
if ((ADailyRateTable.DefaultView.Count == 0) || AEnforceUniqueRate)
{
// try other way round
rowFilter = String.Format("{0}='{1}' AND {2}='{3}'",
ADailyExchangeRateTable.GetToCurrencyCodeDBName(),
ACurrencyFrom,
ADailyExchangeRateTable.GetFromCurrencyCodeDBName(),
ACurrencyTo);
ADailyRateTable.DefaultView.RowFilter = rowFilter;
oppositeRate = true;
if (AEnforceUniqueRate)
{
if (ADailyRateTable.DefaultView.Count > 1)
{
// This way round does not have a unique rate
uniqueFittingRow = null;
}
else if ((ADailyRateTable.DefaultView.Count == 1) && (uniqueFittingRow == null))
{
// we will use this as our unique rate
uniqueFittingRow = (ADailyExchangeRateRow)ADailyRateTable.DefaultView[0].Row;
}
else
{
// put this variable back as it was
oppositeRate = false;
}
}
}
ADailyExchangeRateRow fittingRate = null;
if (AEnforceUniqueRate)
{
// Did we get a unique rate?
if (uniqueFittingRow != null)
{
fittingRate = uniqueFittingRow;
}
}
else if (ADailyRateTable.DefaultView.Count > 0)
{
// Just return the first in the list
fittingRate = (ADailyExchangeRateRow)ADailyRateTable.DefaultView[0].Row;
}
if (fittingRate != null)
{
if (oppositeRate)
{
ARateOfExchange = GLRoutines.Divide(1.0m, fittingRate.RateOfExchange);
}
else
{
ARateOfExchange = fittingRate.RateOfExchange;
}
AEffectiveDate = fittingRate.DateEffectiveFrom;
}
//Returning 0 causes a validation error to force the user to select an exchange rate:
return(ARateOfExchange != 0.0m);
}
private static bool IsDailyExchangeRateIsStillValid(string ACurrencyFrom,
string ACurrencyTo,
DateTime ADateEffective,
decimal ARateOfExchange,
TDBTransaction ATransaction)
{
ADailyExchangeRateTable DailyExchangeRateTable = null;
ADailyExchangeRateTable TempTable = new ADailyExchangeRateTable();
ADailyExchangeRateRow TempRow = TempTable.NewRowTyped(false);
TempRow.FromCurrencyCode = ACurrencyFrom;
TempRow.ToCurrencyCode = ACurrencyTo;
TempRow.DateEffectiveFrom = ADateEffective;
TempRow.RateOfExchange = ARateOfExchange;
DailyExchangeRateTable = ADailyExchangeRateAccess.LoadUsingTemplate(TempRow, ATransaction);
if ((DailyExchangeRateTable != null) && (DailyExchangeRateTable.Rows.Count > 0))
{
return true;
}
return false;
}
public static ExchangeRateTDS LoadDailyExchangeRateData()
{
ExchangeRateTDS WorkingDS = new ExchangeRateTDS();
WorkingDS.EnforceConstraints = false;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
// Load the table so we can read bits of it into our dataset
ADailyExchangeRateTable exchangeRates = ADailyExchangeRateAccess.LoadAll(Transaction);
// Populate the ExchangeRateTDSADailyExchangeRate table
//-- This is the complete query for the DAILYEXCHANGERATE TABLE
//-- It returns all the rows from the DailyExchangeRate table
//-- PLUS all rows from the Journal and Gift Batch tables that are NOT referenced by the Daily Exchange Rate table.
string strSQL = "SELECT * FROM ( ";
// -- This part of the query returns all the rows from the ExchangeRate table
// -- All rows in Journal and Gift that DO match an entry are reported in the usage count
// -- It will always return exactly the same number of rows that are in the Daily Exchange Rate table itself.
// -- In the development database case it returns 312 rows.
// -- It includes 86 Journal entries and 1 Gift Batch entry
strSQL += "SELECT der.a_from_currency_code_c, ";
strSQL += " der.a_to_currency_code_c, ";
strSQL += " der.a_rate_of_exchange_n, ";
strSQL += " der.a_date_effective_from_d, ";
strSQL += " der.a_time_effective_from_i, ";
strSQL += String.Format(
" count(j.a_journal_number_i) AS {0}, count(gb.a_batch_number_i) AS {1}, 'DEX' as {2} ",
ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
strSQL += "FROM PUB_a_daily_exchange_rate AS der ";
strSQL += "LEFT OUTER JOIN PUB_a_journal j ";
strSQL += " ON j.a_transaction_currency_c = der.a_from_currency_code_c ";
strSQL += " AND j.a_base_currency_c = der.a_to_currency_code_c ";
strSQL += " AND j.a_exchange_rate_to_base_n = der.a_rate_of_exchange_n ";
strSQL += " AND j.a_date_effective_d = der.a_date_effective_from_d ";
strSQL += " AND a_exchange_rate_time_i = der.a_time_effective_from_i ";
strSQL += "LEFT OUTER JOIN PUB_a_gift_batch AS gb ";
strSQL += " ON gb.a_currency_code_c = der.a_from_currency_code_c ";
strSQL += " AND gb.a_exchange_rate_to_base_n = der.a_rate_of_exchange_n ";
strSQL += " AND gb.a_gl_effective_date_d = der.a_date_effective_from_d ";
strSQL += "LEFT OUTER JOIN PUB_a_ledger AS ldg ";
strSQL += " ON ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += " AND ldg.a_base_currency_c = der.a_to_currency_code_c ";
strSQL +=
"GROUP BY der.a_from_currency_code_c, der.a_to_currency_code_c, der.a_rate_of_exchange_n, der.a_date_effective_from_d, der.a_time_effective_from_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ";
strSQL += Environment.NewLine;
// -- This part of the query returns all the rows from the journal table that do NOT have an
// -- entry in the exchange rate table
// -- Using the devlopment database it returns 41 unique rows associated with 53 Journal entries
strSQL += "SELECT ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " j.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += String.Format(
" count(j.a_transaction_currency_c) AS {0}, 0 AS {1}, 'J' AS {2} ",
ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
strSQL += "FROM a_journal j ";
strSQL += "LEFT JOIN a_daily_exchange_rate der ";
strSQL += " ON der.a_from_currency_code_c = j.a_transaction_currency_c ";
strSQL += " AND der.a_to_currency_code_c = j.a_base_currency_c ";
strSQL += " AND der.a_date_effective_from_d = j.a_date_effective_d ";
strSQL += " AND der.a_time_effective_from_i = j.a_exchange_rate_time_i ";
strSQL += " AND der.a_rate_of_exchange_n = j.a_exchange_rate_to_base_n ";
strSQL += "WHERE j.a_transaction_currency_c <> j.a_base_currency_c ";
strSQL += " AND der.a_from_currency_code_c IS NULL ";
strSQL +=
"GROUP BY j.a_transaction_currency_c, j.a_base_currency_c, j.a_exchange_rate_to_base_n, j.a_date_effective_d, j.a_exchange_rate_time_i ";
strSQL += Environment.NewLine;
strSQL += "UNION ";
strSQL += Environment.NewLine;
// -- This part of the query returns all the rows in the gift batch table that do NOT have an
// -- entry in the exchange rate table
// -- Using the devlopment database it returns 0 rows, because the one row in the gift batch table has already been included
// -- in the first query (on the Daily Exchange rate table)
strSQL += "SELECT ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " 0 AS a_time_effective_from_i, ";
strSQL += String.Format(
" 0 AS {0}, count(gb.a_currency_code_c) AS {1}, 'GB' AS {2} ",
ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetTableSourceDBName());
strSQL += "FROM a_gift_batch gb ";
strSQL += "LEFT JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "LEFT JOIN a_daily_exchange_rate der ";
strSQL += " ON der.a_from_currency_code_c = gb.a_currency_code_c ";
strSQL += " AND der.a_to_currency_code_c = ldg.a_base_currency_c ";
strSQL += " AND der.a_date_effective_from_d = gb.a_gl_effective_date_d ";
strSQL += " AND der.a_rate_of_exchange_n = gb.a_exchange_rate_to_base_n ";
strSQL += "WHERE gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += " AND der.a_from_currency_code_c IS NULL ";
strSQL += "GROUP BY gb.a_currency_code_c, ldg.a_base_currency_c, gb.a_exchange_rate_to_base_n, gb.a_gl_effective_date_d ";
strSQL += ") AS allrates ";
strSQL += "ORDER BY allrates.a_date_effective_from_d DESC, allrates.a_time_effective_from_i DESC ";
DBAccess.GDBAccessObj.Select(WorkingDS, strSQL, WorkingDS.ADailyExchangeRate.TableName, Transaction);
// Now populate the ExchangeRateTDSADailyExchangerateUsage table
//-- COMPLETE QUERY TO RETURN ADailyExchangeRateUsage
//-- Query to return the Daily Exchange Rate Usage details
//-- Only returns rows that are in a foreign currency
//-- Querying this table by from/to/date/time will return one row per use case
//-- If the Journal is 0 the batch refers to a gift batch, otherwise it is a GL batch
strSQL = "SELECT * FROM ( ";
//-- This part of the query returns the use cases from the Journal table
strSQL += "SELECT ";
strSQL += " j.a_transaction_currency_c AS a_from_currency_code_c, ";
strSQL += " j.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " j.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " j.a_date_effective_d AS a_date_effective_from_d, ";
strSQL += " j.a_exchange_rate_time_i AS a_time_effective_from_i, ";
strSQL += String.Format(
" j.a_ledger_number_i AS {0}, j.a_batch_number_i AS {1}, j.a_journal_number_i AS {2}, b.a_batch_status_c AS {3}, b.a_batch_year_i AS {4}, b.a_batch_period_i AS {5}, 'J' AS {6} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_journal j ";
strSQL += "JOIN a_batch b ";
strSQL += " ON b.a_batch_number_i = j.a_batch_number_i ";
strSQL += " AND b.a_ledger_number_i = j.a_ledger_number_i ";
strSQL += "WHERE j.a_transaction_currency_c <> j.a_base_currency_c ";
strSQL += Environment.NewLine;
strSQL += "UNION ";
strSQL += Environment.NewLine;
//-- This part of the query returns the use cases from the Gift Batch table
strSQL += "SELECT ";
strSQL += " gb.a_currency_code_c AS a_from_currency_code_c, ";
strSQL += " ldg.a_base_currency_c AS a_to_currency_code_c, ";
strSQL += " gb.a_exchange_rate_to_base_n AS a_rate_of_exchange_n, ";
strSQL += " gb.a_gl_effective_date_d AS a_date_effective_from_d, ";
strSQL += " 0 AS a_time_effective_from_i, ";
strSQL += String.Format(
" gb.a_ledger_number_i AS {0}, gb.a_batch_number_i AS {1}, 0 AS {2}, gb.a_batch_status_c AS {3}, gb.a_batch_year_i AS {4}, gb.a_batch_period_i AS {5}, 'GB' AS {6} ",
ExchangeRateTDSADailyExchangeRateUsageTable.GetLedgerNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetJournalNumberDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchStatusDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchYearDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetBatchPeriodDBName(),
ExchangeRateTDSADailyExchangeRateUsageTable.GetTableSourceDBName());
strSQL += "FROM a_gift_batch gb ";
strSQL += "JOIN a_ledger ldg ";
strSQL += " ON ldg.a_ledger_number_i = gb.a_ledger_number_i ";
strSQL += "WHERE gb.a_currency_code_c <> ldg.a_base_currency_c ";
strSQL += ") AS usage ";
strSQL += "ORDER BY usage.a_date_effective_from_d DESC, usage.a_time_effective_from_i DESC ";
DBAccess.GDBAccessObj.Select(WorkingDS, strSQL, WorkingDS.ADailyExchangeRateUsage.TableName, Transaction);
// Now we start a tricky bit to resolve potential primary key conflicts when the constraints are turned on.
// By combining the Journal and Gift Batch data that is not referenced in the exchange rate table we can easily
// have introduced conflicts where more than one rate has been used for a given currency pair and effective date/time.
// This is because there is no constraint that enforces the batch/journal tables to use a time from the exch rate table.
// So we have to go through all the rows in our data table and potentially change the time to make it possible to get our primary key.
// Start by creating a data view on the whole result set. The oredering is important because we are going to step through the set row by row.
// We order on: From, To, Date, Time, JournalUsage, GiftBatchUsage
// We need to deal with the following possibilities:
// From To Date Time Rate Journals Gifts TableSource
// EUR GBP 2014-01-01 1234 2.115 0 0 DEX
// EUR GBP 2014-01-01 1234 2.11 3 0 J
// EUR GBP 2014-01-01 1234 2.22 1 0 J
// EUR GBP 2014-01-01 1234 3.11 0 1 GB
//
// In the first row we have an entry from the DEX table that is not used anywhere, but a (slightly) different rate is actually used
// in a Journal. So we actually don't show the DEX row.
// In the other rows we have 3 different rates - all used somewhere. We need to adjust the times so they are different.
DataView dv = new DataView(WorkingDS.ADailyExchangeRate, "",
String.Format("{0}, {1}, {2} DESC, {3} DESC, {4}, {5}",
ADailyExchangeRateTable.GetFromCurrencyCodeDBName(),
ADailyExchangeRateTable.GetToCurrencyCodeDBName(),
ADailyExchangeRateTable.GetDateEffectiveFromDBName(),
ADailyExchangeRateTable.GetTimeEffectiveFromDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetJournalUsageDBName(),
ExchangeRateTDSADailyExchangeRateTable.GetGiftBatchUsageDBName()), DataViewRowState.CurrentRows);
for (int i = 0; i < dv.Count - 1; i++)
{
// Get the 'current' row and the 'next' one...
ExchangeRateTDSADailyExchangeRateRow drThis = (ExchangeRateTDSADailyExchangeRateRow)dv[i].Row;
ExchangeRateTDSADailyExchangeRateRow drNext = (ExchangeRateTDSADailyExchangeRateRow)dv[i + 1].Row;
if ((drThis.JournalUsage == 0) && (drThis.GiftBatchUsage == 0))
{
// This will be a row that the client can edit/delete, so we need to add the modification info
ADailyExchangeRateRow foundRow = (ADailyExchangeRateRow)exchangeRates.Rows.Find(new object[] {
drThis.FromCurrencyCode, drThis.ToCurrencyCode, drThis.DateEffectiveFrom, drThis.TimeEffectiveFrom
});
if (foundRow != null)
{
// it should always be non-null
drThis.BeginEdit();
drThis.ModificationId = foundRow.ModificationId;
drThis.DateModified = foundRow.DateModified;
drThis.ModifiedBy = foundRow.ModifiedBy;
drThis.DateCreated = foundRow.DateCreated;
drThis.CreatedBy = foundRow.CreatedBy;
drThis.EndEdit();
}
}
if (!drThis.FromCurrencyCode.Equals(drNext.FromCurrencyCode) ||
!drThis.ToCurrencyCode.Equals(drNext.ToCurrencyCode) ||
!drThis.DateEffectiveFrom.Equals(drNext.DateEffectiveFrom) ||
!drThis.TimeEffectiveFrom.Equals(drNext.TimeEffectiveFrom))
{
// Something is different so our primary key will be ok for the current row
continue;
}
// We have got two (or more) rows with the same potential primary key and different rates.
// We need to work out how many rows ahead also have the same time and adjust them all
bool moveForwards = (drThis.TimeEffectiveFrom < 43200);
int timeOffset = 60; // 1 minute
// Start by adjusting our 'next' row we are already working with
drNext.BeginEdit();
int prevTimeEffectiveFrom = drNext.TimeEffectiveFrom;
drNext.TimeEffectiveFrom = (moveForwards) ? prevTimeEffectiveFrom + timeOffset : prevTimeEffectiveFrom - timeOffset;
timeOffset = (moveForwards) ? timeOffset + 60 : timeOffset - 60;
drNext.EndEdit();
i++; // we can increment our main loop counter now that we have dealt with our 'next' row.
// Modify all the rows in the usage table that refer to the previous time
OnModifyEffectiveTime(WorkingDS.ADailyExchangeRateUsage, drNext.FromCurrencyCode, drNext.ToCurrencyCode,
drNext.DateEffectiveFrom,
prevTimeEffectiveFrom, drNext.TimeEffectiveFrom, drNext.RateOfExchange);
// Now look ahead even further than the 'next' row and modify those times too, adding 1 more minute to each
for (int k = i + 2;; k++)
{
ExchangeRateTDSADailyExchangeRateRow drLookAhead = (ExchangeRateTDSADailyExchangeRateRow)dv[k].Row;
if (!drThis.FromCurrencyCode.Equals(drLookAhead.FromCurrencyCode) ||
!drThis.ToCurrencyCode.Equals(drLookAhead.ToCurrencyCode) ||
!drThis.DateEffectiveFrom.Equals(drLookAhead.DateEffectiveFrom) ||
!drThis.TimeEffectiveFrom.Equals(drLookAhead.TimeEffectiveFrom))
{
// No more rows match our potential primary key conflict on the 'current' row.
break;
}
// Do exactly the same to this row as we did to the 'next' row above
drLookAhead.BeginEdit();
prevTimeEffectiveFrom = drLookAhead.TimeEffectiveFrom;
drLookAhead.TimeEffectiveFrom = (moveForwards) ? prevTimeEffectiveFrom + timeOffset : prevTimeEffectiveFrom - timeOffset;
timeOffset = (moveForwards) ? timeOffset + 60 : timeOffset - 60;
drLookAhead.EndEdit();
i++;
OnModifyEffectiveTime(WorkingDS.ADailyExchangeRateUsage, drLookAhead.FromCurrencyCode, drLookAhead.ToCurrencyCode,
drLookAhead.DateEffectiveFrom, prevTimeEffectiveFrom, drLookAhead.TimeEffectiveFrom, drLookAhead.RateOfExchange);
}
} // check the next row in the table so that it becomes the 'current' row.
WorkingDS.EnforceConstraints = true;
// Load the Corporate exchange rate table using the usual method
ACorporateExchangeRateAccess.LoadAll(WorkingDS, Transaction);
});
// Accept row changes here so that the Client gets 'unmodified' rows
WorkingDS.AcceptChanges();
return(WorkingDS);
}
public static bool GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable)
{
// TODO: check access permissions for the current user
bool NewTransaction = false;
TDBTransaction ReadTransaction;
TTypedDataTable tempTable = null;
try
{
ReadTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
// TODO: auto generate
if (ATablename == AApSupplierTable.GetTableDBName())
{
tempTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == AApDocumentTable.GetTableDBName())
{
tempTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == ATransactionTypeTable.GetTableDBName())
{
tempTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
tempTable = ACurrencyAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
tempTable = ADailyExchangeRateAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
tempTable = ACorporateExchangeRateAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
tempTable = ACurrencyLanguageAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
tempTable = AFeesPayableAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
tempTable = AFeesReceivableAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
tempTable = AAnalysisTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
tempTable = AGiftBatchAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
tempTable = AJournalAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
tempTable = ALedgerAccess.LoadAll(ReadTransaction);
}
else if (ATablename == MExtractMasterTable.GetTableDBName())
{
if (ASearchCriteria == null)
{
tempTable = MExtractMasterAccess.LoadAll(ReadTransaction);
}
else
{
tempTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
}
else if (ATablename == MExtractTable.GetTableDBName())
{
// it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set
if (ASearchCriteria != null)
{
tempTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
tempTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
tempTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
tempTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
tempTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
tempTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
tempTable = PInternationalPostalTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
tempTable = PtApplicationTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
tempTable = PMailingAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
tempTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
tempTable = PmDocumentTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
tempTable = SGroupAccess.LoadAll(ReadTransaction);
}
else
{
throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename);
}
}
catch (Exception Exp)
{
DBAccess.GDBAccessObj.RollbackTransaction();
TLogging.Log("TCommonDataReader.GetData exception: " + Exp.ToString(), TLoggingType.ToLogfile);
TLogging.Log(Exp.StackTrace, TLoggingType.ToLogfile);
throw;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
TLogging.LogAtLevel(7, "TCommonDataReader.GetData: committed own transaction.");
}
}
// Accept row changes here so that the Client gets 'unmodified' rows
tempTable.AcceptChanges();
// return the table
AResultTable = tempTable;
return(true);
}
/// <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>
/// 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>
/// get daily exchange rate for the given currencies and date;
/// TODO: might even collect the latest exchange rate from the web
/// </summary>
/// <param name="ACurrencyFrom"></param>
/// <param name="ACurrencyTo"></param>
/// <param name="ADateEffective"></param>
/// <returns>Zero if no exchange rate found</returns>
public static decimal GetDailyExchangeRate(string ACurrencyFrom, string ACurrencyTo, DateTime ADateEffective)
{
if (ACurrencyFrom == ACurrencyTo)
{
return 1.0M;
}
bool NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction
(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction);
ADailyExchangeRateRow fittingRate = null;
// TODO: get the most recent exchange rate for the given date and currencies
bool oppositeRate = false;
ADailyExchangeRateTable rates = ADailyExchangeRateAccess.LoadByPrimaryKey(ACurrencyFrom, ACurrencyTo, ADateEffective, 0, Transaction);
if (rates.Count == 0)
{
// try other way round
rates = ADailyExchangeRateAccess.LoadByPrimaryKey(ACurrencyTo, ACurrencyFrom, ADateEffective, 0, Transaction);
oppositeRate = true;
}
if (rates.Count == 1)
{
fittingRate = rates[0];
}
else if (rates.Count == 0)
{
// TODO: collect exchange rate from the web; save to db
// see tracker http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=87
// Or look for most recent exchange rate???
ADailyExchangeRateTable tempTable = new ADailyExchangeRateTable();
ADailyExchangeRateRow templateRow = tempTable.NewRowTyped(false);
templateRow.FromCurrencyCode = ACurrencyFrom;
templateRow.ToCurrencyCode = ACurrencyTo;
oppositeRate = false;
rates = ADailyExchangeRateAccess.LoadUsingTemplate(templateRow, Transaction);
if (rates.Count == 0)
{
templateRow.FromCurrencyCode = ACurrencyTo;
templateRow.ToCurrencyCode = ACurrencyFrom;
oppositeRate = true;
rates = ADailyExchangeRateAccess.LoadUsingTemplate(templateRow, Transaction);
}
if (rates.Count > 0)
{
// sort rates by date, look for rate just before the date we are looking for
rates.DefaultView.Sort = ADailyExchangeRateTable.GetDateEffectiveFromDBName();
rates.DefaultView.RowFilter = ADailyExchangeRateTable.GetDateEffectiveFromDBName() + "<= #" +
ADateEffective.ToString("yyyy-MM-dd") + "#";
if (rates.DefaultView.Count > 0)
{
fittingRate = (ADailyExchangeRateRow)rates.DefaultView[rates.DefaultView.Count - 1].Row;
}
}
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
if (fittingRate != null)
{
if (oppositeRate)
{
return 1.0M / fittingRate.RateOfExchange;
}
return fittingRate.RateOfExchange;
}
TLogging.Log("Cannot find daily exchange rate for " + ACurrencyFrom + " " + ACurrencyTo);
//return 1.0M;
//Instead, cause a validation error to force the user to select an exchange rate
return 0M;
}
public static bool GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable)
{
// TODO: check access permissions for the current user
TDBTransaction ReadTransaction = null;
TTypedDataTable tempTable = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.RepeatableRead,
TEnforceIsolationLevel.eilMinimum,
ref ReadTransaction,
delegate
{
// TODO: auto generate
if (ATablename == AApSupplierTable.GetTableDBName())
{
tempTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == AApDocumentTable.GetTableDBName())
{
tempTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == ATransactionTypeTable.GetTableDBName())
{
tempTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
tempTable = ACurrencyAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
tempTable = ADailyExchangeRateAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
tempTable = ACorporateExchangeRateAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
tempTable = ACurrencyLanguageAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
tempTable = AFeesPayableAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
tempTable = AFeesReceivableAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
tempTable = AAnalysisTypeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
tempTable = AGiftBatchAccess.LoadAll(ReadTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
tempTable = AJournalAccess.LoadAll(ReadTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
tempTable = ALedgerAccess.LoadAll(ReadTransaction);
}
else if (ATablename == MExtractMasterTable.GetTableDBName())
{
if (ASearchCriteria == null)
{
tempTable = MExtractMasterAccess.LoadAll(ReadTransaction);
}
else
{
tempTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
}
else if (ATablename == MExtractTable.GetTableDBName())
{
// it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set
if (ASearchCriteria != null)
{
tempTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
tempTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
tempTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
tempTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
tempTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
tempTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PFormTable.GetTableDBName())
{
string[] columns = TTypedDataTable.GetColumnStringList(PFormTable.TableId);
StringCollection fieldList = new StringCollection();
for (int i = 0; i < columns.Length; i++)
{
// Do not load the template document - we don't display it and it is big!
if (columns[i] != PFormTable.GetTemplateDocumentDBName())
{
fieldList.Add(columns[i]);
}
}
tempTable = PFormAccess.LoadAll(fieldList, ReadTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
tempTable = PInternationalPostalTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
tempTable = PtApplicationTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PFormalityTable.GetTableDBName())
{
tempTable = PFormalityAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
tempTable = PMailingAccess.LoadAll(ReadTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
tempTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, ReadTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
tempTable = PmDocumentTypeAccess.LoadAll(ReadTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
tempTable = SGroupAccess.LoadAll(ReadTransaction);
}
else
{
throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename);
}
});
// Accept row changes here so that the Client gets 'unmodified' rows
tempTable.AcceptChanges();
// return the table
AResultTable = tempTable;
return(true);
}
/// <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);
}
}
public static void LoadAll()
{
ADailyExchangeRate = new ADailyExchangeRateTable();
SerialisableDS.LoadAll(ADailyExchangeRate, ADailyExchangeRateTable.GetTableDBName());
}
/// <summary>
/// get daily exchange rate for the given currencies and date;
/// TODO: might even collect the latest exchange rate from the web
/// </summary>
/// <param name="ACurrencyFrom"></param>
/// <param name="ACurrencyTo"></param>
/// <param name="ADateEffective"></param>
/// <returns>Zero if no exchange rate found</returns>
public static decimal GetDailyExchangeRate(string ACurrencyFrom, string ACurrencyTo, DateTime ADateEffective)
{
if (ACurrencyFrom == ACurrencyTo)
{
return(1.0M);
}
bool NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction
(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction);
ADailyExchangeRateRow fittingRate = null;
// TODO: get the most recent exchange rate for the given date and currencies
bool oppositeRate = false;
ADailyExchangeRateTable rates = ADailyExchangeRateAccess.LoadByPrimaryKey(ACurrencyFrom, ACurrencyTo, ADateEffective, 0, Transaction);
if (rates.Count == 0)
{
// try other way round
rates = ADailyExchangeRateAccess.LoadByPrimaryKey(ACurrencyTo, ACurrencyFrom, ADateEffective, 0, Transaction);
oppositeRate = true;
}
if (rates.Count == 1)
{
fittingRate = rates[0];
}
else if (rates.Count == 0)
{
// TODO: collect exchange rate from the web; save to db
// see tracker http://sourceforge.net/apps/mantisbt/openpetraorg/view.php?id=87
// Or look for most recent exchange rate???
ADailyExchangeRateTable tempTable = new ADailyExchangeRateTable();
ADailyExchangeRateRow templateRow = tempTable.NewRowTyped(false);
templateRow.FromCurrencyCode = ACurrencyFrom;
templateRow.ToCurrencyCode = ACurrencyTo;
oppositeRate = false;
rates = ADailyExchangeRateAccess.LoadUsingTemplate(templateRow, Transaction);
if (rates.Count == 0)
{
templateRow.FromCurrencyCode = ACurrencyTo;
templateRow.ToCurrencyCode = ACurrencyFrom;
oppositeRate = true;
rates = ADailyExchangeRateAccess.LoadUsingTemplate(templateRow, Transaction);
}
if (rates.Count > 0)
{
// sort rates by date, look for rate just before the date we are looking for
rates.DefaultView.Sort = ADailyExchangeRateTable.GetDateEffectiveFromDBName();
rates.DefaultView.RowFilter = ADailyExchangeRateTable.GetDateEffectiveFromDBName() + "<= #" +
ADateEffective.ToString("yyyy-MM-dd") + "#";
if (rates.DefaultView.Count > 0)
{
fittingRate = (ADailyExchangeRateRow)rates.DefaultView[rates.DefaultView.Count - 1].Row;
}
}
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
if (fittingRate != null)
{
if (oppositeRate)
{
return(1.0M / fittingRate.RateOfExchange);
}
return(fittingRate.RateOfExchange);
}
TLogging.Log("Cannot find daily exchange rate for " + ACurrencyFrom + " " + ACurrencyTo);
//return 1.0M;
//Instead, cause a validation error to force the user to select an exchange rate
return(0M);
}
public static bool SaveChanges()
{
TTypedDataTable TableChanges = ADailyExchangeRate.GetChangesTyped();
return(SerialisableDS.SaveChanges(ADailyExchangeRate, TableChanges, ADailyExchangeRateTable.GetTableDBName()));
}
public static void GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable,
TDBTransaction AReadTransaction)
{
AResultTable = null;
string context = string.Format("GetData {0}", SharedConstants.MODULE_ACCESS_MANAGER);
// check access permissions for the current user
TModuleAccessManager.CheckUserPermissionsForTable(ATablename, TTablePermissionEnum.eCanRead);
// TODO: auto generate
if (ATablename == AApSupplierTable.GetTableDBName())
{
AResultTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == AApDocumentTable.GetTableDBName())
{
AResultTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == ATransactionTypeTable.GetTableDBName())
{
AResultTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == ACurrencyTable.GetTableDBName())
{
AResultTable = ACurrencyAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ADailyExchangeRateTable.GetTableDBName())
{
AResultTable = ADailyExchangeRateAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ACorporateExchangeRateTable.GetTableDBName())
{
AResultTable = ACorporateExchangeRateAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ACurrencyLanguageTable.GetTableDBName())
{
AResultTable = ACurrencyLanguageAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AFeesPayableTable.GetTableDBName())
{
AResultTable = AFeesPayableAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AFeesReceivableTable.GetTableDBName())
{
AResultTable = AFeesReceivableAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AAnalysisTypeTable.GetTableDBName())
{
AResultTable = AAnalysisTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == AGiftBatchTable.GetTableDBName())
{
AResultTable = AGiftBatchAccess.LoadAll(AReadTransaction);
}
else if (ATablename == AJournalTable.GetTableDBName())
{
AResultTable = AJournalAccess.LoadAll(AReadTransaction);
}
else if (ATablename == ALedgerTable.GetTableDBName())
{
AResultTable = ALedgerAccess.LoadAll(AReadTransaction);
}
else if (ATablename == MExtractMasterTable.GetTableDBName())
{
if (ASearchCriteria == null)
{
AResultTable = MExtractMasterAccess.LoadAll(AReadTransaction);
}
else
{
AResultTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
}
else if (ATablename == MExtractTable.GetTableDBName())
{
// it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set
if (ASearchCriteria != null)
{
AResultTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
}
else if (ATablename == PcAttendeeTable.GetTableDBName())
{
AResultTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcConferenceCostTable.GetTableDBName())
{
AResultTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcEarlyLateTable.GetTableDBName())
{
AResultTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcSupplementTable.GetTableDBName())
{
AResultTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PcDiscountTable.GetTableDBName())
{
AResultTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PCountryTable.GetTableDBName())
{
AResultTable = PCountryAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PFormTable.GetTableDBName())
{
string[] columns = TTypedDataTable.GetColumnStringList(PFormTable.TableId);
StringCollection fieldList = new StringCollection();
for (int i = 0; i < columns.Length; i++)
{
// Do not load the template document - we don't display it and it is big!
if (columns[i] != PFormTable.GetTemplateDocumentDBName())
{
fieldList.Add(columns[i]);
}
}
AResultTable = PFormAccess.LoadAll(fieldList, AReadTransaction);
}
else if (ATablename == PInternationalPostalTypeTable.GetTableDBName())
{
AResultTable = PInternationalPostalTypeAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PtApplicationTypeTable.GetTableDBName())
{
AResultTable = PtApplicationTypeAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PFormalityTable.GetTableDBName())
{
AResultTable = PFormalityAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PMailingTable.GetTableDBName())
{
AResultTable = PMailingAccess.LoadAll(AReadTransaction);
}
else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName())
{
AResultTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction);
}
else if (ATablename == PmDocumentTypeTable.GetTableDBName())
{
AResultTable = PmDocumentTypeAccess.LoadAll(AReadTransaction);
}
else if (ATablename == SGroupTable.GetTableDBName())
{
TSecurityChecks.CheckUserModulePermissions(SharedConstants.PETRAMODULE_SYSADMIN, context);
AResultTable = SGroupAccess.LoadAll(AReadTransaction);
}
else if (ATablename == SSystemDefaultsTable.GetTableDBName())
{
TSecurityChecks.CheckUserModulePermissions(SharedConstants.PETRAMODULE_SYSADMIN, context);
AResultTable = SSystemDefaultsAccess.LoadAll(AReadTransaction);
}
else if (ATablename == SSystemDefaultsGuiTable.GetTableDBName())
{
AResultTable = SSystemDefaultsGuiAccess.LoadAll(AReadTransaction);
}
else
{
throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename);
}
// Accept row changes here so that the Client gets 'unmodified' rows
AResultTable.AcceptChanges();
}
/// <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);
}
}
}
