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