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);
}
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);
}