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