private void LoadDatabase() { intBaseCurrencyDigits = DIGIT_INIT_VALUE; intForeignCurrencyDigits = DIGIT_INIT_VALUE; bool NewTransaction; TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); currencyTable = ACurrencyAccess.LoadAll(transaction); if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } if (currencyTable.Rows.Count == 0) { EVerificationException terminate = new EVerificationException( Catalog.GetString("The table a_currency is empty!")); terminate.Context = "Common Accounting"; terminate.ErrorCode = "TCurrencyInfo01"; throw terminate; } }
private void LoadDatabase() { intBaseCurrencyDigits = DIGIT_INIT_VALUE; intForeignCurrencyDigits = DIGIT_INIT_VALUE; TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadDatabase"); db.ReadTransaction( ref transaction, delegate { currencyTable = ACurrencyAccess.LoadAll(transaction); }); db.CloseDBConnection(); if (currencyTable.Rows.Count == 0) { EVerificationException terminate = new EVerificationException( Catalog.GetString("The table a_currency is empty!")); terminate.Context = "Common Accounting"; terminate.ErrorCode = "TCurrencyInfo01"; throw terminate; } }
private void RunOnceOnActivationManual() { rbtPayFullOutstandingAmount.CheckedChanged += new EventHandler(EnablePartialPayment); chkClaimDiscount.Visible = false; txtExchangeRate.TextChanged += new EventHandler(UpdateTotalAmount); ALedgerRow LedgerRow = ((ALedgerTable)TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.LedgerDetails, FLedgerNumber))[0]; FCurrencyTable = (ACurrencyTable)TDataCache.TMPartner.GetCacheablePartnerTable(TCacheablePartnerTablesEnum.CurrencyCodeList); //txtExchangeRate.SetControlProperties(10); txtBaseAmount.CurrencyCode = LedgerRow.BaseCurrency; TExchangeRateCache.ResetCache(); FocusedRowChanged(null, null); }
private void RunOnceOnActivationManual() { rbtPayFullOutstandingAmount.CheckedChanged += new EventHandler(EnablePartialPayment); chkClaimDiscount.Visible = false; txtExchangeRate.TextChanged += new EventHandler(UpdateTotalAmount); ALedgerRow LedgerRow = ((ALedgerTable)TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.LedgerDetails, FLedgerNumber))[0]; FCurrencyTable = (ACurrencyTable)TDataCache.TMCommon.GetCacheableCommonTable(TCacheableCommonTablesEnum.CurrencyCodeList); //txtExchangeRate.SetControlProperties(10); txtBaseAmount.CurrencyCode = LedgerRow.BaseCurrency; TExchangeRateCache.ResetCache(); FocusedRowChanged(null, null); }
private void RunOnceOnActivationManual() { // We need to set the number of decimal places for the Mailing Cost // We check all the ledgers to see which one has the highest number of decimal places. ALedgerTable ledgers = TRemote.MFinance.Setup.WebConnectors.GetAvailableLedgers(); ACurrencyTable currencyTable = (ACurrencyTable)TDataCache.TMCommon.GetCacheableCommonTable(TCacheableCommonTablesEnum.CurrencyCodeList); int numDecimalPlaces = 0; if (ledgers.Count == 0) { txtDetailMailingCost.CurrencyCode = "USD"; } else { for (int i = 0; i < ledgers.Count; i++) { string code = ledgers[i].BaseCurrency; ACurrencyRow row = (ACurrencyRow)currencyTable.Rows.Find(code); if (row != null) { int decimals = StringHelper.DecimalPlacesForCurrency(code); if (decimals > numDecimalPlaces) { numDecimalPlaces = decimals; } } } if (numDecimalPlaces == 0) { txtDetailMailingCost.CurrencyCode = TTxtCurrencyTextBox.CURRENCY_STANDARD_0_DP; } else { txtDetailMailingCost.CurrencyCode = TTxtCurrencyTextBox.CURRENCY_STANDARD_2_DP; } } FPetraUtilsObject.DataSaved += FPetraUtilsObject_DataSaved; }
/// <summary> /// Load specific test data /// </summary> public void LoadTestTata() { ACurrencyTable currencyTable = null; TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("LoadTestTata"); db.ReadTransaction( ref Transaction, delegate { currencyTable = ACurrencyAccess.LoadByPrimaryKey("DMG", Transaction); }); if (currencyTable.Rows.Count == 0) { CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\server\\GL\\" + "test-sql\\gl-test-currency-data.sql"); } }
/// <summary> /// Load specific test data /// </summary> public void LoadTestTata() { ACurrencyTable currencyTable = null; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { currencyTable = ACurrencyAccess.LoadByPrimaryKey("DMG", Transaction); }); if (currencyTable.Rows.Count == 0) { CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\GL\\" + "test-sql\\gl-test-currency-data.sql"); } }
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> /// Validates the Gift Batch data. /// </summary> /// <param name="AContext">Context that describes where the data validation failed.</param> /// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param> /// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if /// data validation errors occur.</param> /// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that /// display data that is about to be validated.</param> /// <param name="AAccountTableRef">Account Table. A reference to this table is REQUIRED when importing - optional otherwise</param> /// <param name="ACostCentreTableRef">Cost centre table. A reference to this table is REQUIRED when importing - optional otherwise</param> /// <param name="AAccountPropertyTableRef">Account Property Table. A reference to this table is REQUIRED when importing - optional otherwise</param> /// <param name="AAccountingPeriodTableRef">Accounting Period Table. A reference to this table is REQUIRED when importing - optional otherwise</param> /// <param name="ACorporateExchangeTableRef">Corporate exchange rate table. A reference to this table is REQUIRED when importing - optional otherwise</param> /// <param name="ACurrencyTableRef">Currency table. A reference to this table is REQUIRED when importing - optional otherwise</param> /// <param name="ABaseCurrency">Ledger base currency. Required when importing</param> /// <param name="AInternationalCurrency">Ledger international currency. Required when importing</param> /// <returns>True if the validation found no data validation errors, otherwise false.</returns> public static bool ValidateGiftBatchManual(object AContext, AGiftBatchRow ARow, ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict, AAccountTable AAccountTableRef = null, ACostCentreTable ACostCentreTableRef = null, AAccountPropertyTable AAccountPropertyTableRef = null, AAccountingPeriodTable AAccountingPeriodTableRef = null, ACorporateExchangeRateTable ACorporateExchangeTableRef = null, ACurrencyTable ACurrencyTableRef = null, string ABaseCurrency = null, string AInternationalCurrency = null) { DataColumn ValidationColumn; TValidationControlsData ValidationControlsData; TScreenVerificationResult VerificationResult; object ValidationContext; int VerifResultCollAddedCount = 0; // Don't validate deleted or posted DataRows if ((ARow.RowState == DataRowState.Deleted) || (ARow.BatchStatus == MFinanceConstants.BATCH_POSTED)) { return true; } bool IsImporting = AContext.ToString().Contains("Importing"); // Bank Account Code must be active ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnBankAccountCodeId]; ValidationContext = ARow.BankAccountCode; if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { if (!ARow.IsBankAccountCodeNull() && (AAccountTableRef != null)) { // We even need to check that the code exists! AAccountRow foundRow = (AAccountRow)AAccountTableRef.Rows.Find(new object[] { ARow.LedgerNumber, ARow.BankAccountCode }); if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString("Unknown bank account code '{0}'."), ARow.BankAccountCode), TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } // If it does exist and the account is a foreign currency account then the batch currency must match if ((foundRow != null) && foundRow.ForeignCurrencyFlag) { if ((foundRow.ForeignCurrencyCode != ARow.CurrencyCode) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString( "The bank account code '{0}' is a foreign currency account so the currency code for the batch must be '{1}'."), ARow.BankAccountCode, foundRow.ForeignCurrencyCode), TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } // If it does exist it must be a posting account if (foundRow != null) { if (!foundRow.PostingStatus && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString( "The bank account code '{0}' is not a posting account."), ARow.BankAccountCode), TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } if ((foundRow != null) && (ARow.GiftType == MFinanceConstants.GIFT_TYPE_GIFT)) { // The account must be a bank account as defined in the AccountProperty table if (AAccountPropertyTableRef != null) { AAccountPropertyRow foundRow2 = (AAccountPropertyRow)AAccountPropertyTableRef.Rows.Find( new object[] { ARow.LedgerNumber, ARow.BankAccountCode, "BANK ACCOUNT", "true" }); if ((foundRow2 == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString( "The bank account code '{0}' must be associated with a real 'Bank Account' when the gift type is a 'Gift'."), ARow.BankAccountCode), TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } } } VerificationResult = (TScreenVerificationResult)TStringChecks.ValidateValueIsActive(ARow.LedgerNumber, AAccountTableRef, ValidationContext.ToString(), AAccountTable.GetAccountActiveFlagDBName(), AContext, ValidationColumn, ValidationControlsData.ValidationControl); // Handle addition/removal to/from TVerificationResultCollection if ((VerificationResult != null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true)) { VerifResultCollAddedCount++; } } // Bank Cost Centre Code validation ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnBankCostCentreId]; ValidationContext = ARow.BankCostCentre; if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { if (!ARow.IsBankCostCentreNull() && (ACostCentreTableRef != null)) { // We even need to check that the code exists! ACostCentreRow foundRow = (ACostCentreRow)ACostCentreTableRef.Rows.Find(new object[] { ARow.LedgerNumber, ARow.BankCostCentre }); if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TScreenVerificationResult(ValidationContext, ValidationColumn, String.Format(Catalog.GetString("Unknown Bank Cost Centre: '{0}'."), ARow.BankCostCentre), ValidationControlsData.ValidationControl, TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } // Even if the cost centre exists it must be a 'posting' cost centre if (foundRow != null) { if (!foundRow.PostingCostCentreFlag && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TScreenVerificationResult(ValidationContext, ValidationColumn, String.Format(Catalog.GetString("The cost centre '{0}' is not a Posting Cost Centre."), ARow.BankCostCentre), ValidationControlsData.ValidationControl, TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } } // Bank Cost Centre Code must be active VerificationResult = (TScreenVerificationResult)TStringChecks.ValidateValueIsActive(ARow.LedgerNumber, ACostCentreTableRef, ValidationContext.ToString(), ACostCentreTable.GetCostCentreActiveFlagDBName(), AContext, ValidationColumn, ValidationControlsData.ValidationControl); // Handle addition/removal to/from TVerificationResultCollection if ((VerificationResult != null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true)) { VerifResultCollAddedCount++; } } // Currency Code validation ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnCurrencyCodeId]; ValidationContext = ARow.BatchNumber; if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { if (!ARow.IsCurrencyCodeNull() && (ACurrencyTableRef != null)) { // Currency code must exist in the currency table ACurrencyRow foundRow = (ACurrencyRow)ACurrencyTableRef.Rows.Find(ARow.CurrencyCode); if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TScreenVerificationResult(ValidationContext, ValidationColumn, String.Format(Catalog.GetString("Unknown currency code '{0}'."), ARow.CurrencyCode), ValidationControlsData.ValidationControl, TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } } // 'Exchange Rate' must be greater than 0 ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnExchangeRateToBaseId]; ValidationContext = ARow.BatchNumber; if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { if (!ARow.IsExchangeRateToBaseNull()) { VerificationResult = (TScreenVerificationResult)TNumericalChecks.IsPositiveDecimal(ARow.ExchangeRateToBase, ValidationControlsData.ValidationControlLabel + (IsImporting ? String.Empty : " of Batch Number " + ValidationContext.ToString()), AContext, ValidationColumn, ValidationControlsData.ValidationControl); // Handle addition/removal to/from TVerificationResultCollection if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true)) { VerifResultCollAddedCount++; } // Exchange rate must be 1.00 if the currency is the the base ledger currency if ((ABaseCurrency != null) && (!ARow.IsCurrencyCodeNull()) && (ARow.CurrencyCode == ABaseCurrency) && (ARow.ExchangeRateToBase != 1.00m)) { if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, new TScreenVerificationResult(ValidationContext, ValidationColumn, Catalog.GetString("A batch in the ledger base currency must have exchange rate of 1.00."), ValidationControlsData.ValidationControl, TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } } } // 'Effective From Date' must be valid ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnGlEffectiveDateId]; ValidationContext = ARow.BatchNumber; DateTime StartDateCurrentPeriod; DateTime EndDateLastForwardingPeriod; TSharedFinanceValidationHelper.GetValidPostingDateRange(ARow.LedgerNumber, out StartDateCurrentPeriod, out EndDateLastForwardingPeriod); if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { VerificationResult = (TScreenVerificationResult)TDateChecks.IsDateBetweenDates(ARow.GlEffectiveDate, StartDateCurrentPeriod, EndDateLastForwardingPeriod, ValidationControlsData.ValidationControlLabel + (IsImporting ? String.Empty : " of Batch Number " + ValidationContext.ToString()), TDateBetweenDatesCheckType.dbdctUnspecific, TDateBetweenDatesCheckType.dbdctUnspecific, AContext, ValidationColumn, ValidationControlsData.ValidationControl); // Handle addition/removal to/from TVerificationResultCollection if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true)) { VerifResultCollAddedCount++; } // If the GL date was good we need to have a corporate exchange rate for base currency to Intl for the first day of the period if ((VerificationResult == null) && (ACorporateExchangeTableRef != null) && !ARow.IsGlEffectiveDateNull() && (ABaseCurrency != null) && (AInternationalCurrency != null) && (ABaseCurrency != AInternationalCurrency)) { DateTime firstOfMonth; if (TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriod(ARow.LedgerNumber, ARow.GlEffectiveDate, out firstOfMonth)) { ACorporateExchangeRateRow foundRow = (ACorporateExchangeRateRow)ACorporateExchangeTableRef.Rows.Find( new object[] { ABaseCurrency, AInternationalCurrency, firstOfMonth }); if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString( "International currency: there is no Corporate Exchange Rate defined for '{0}' to '{1}' for the month starting on '{2}'."), ABaseCurrency, AInternationalCurrency, StringHelper.DateToLocalizedString(firstOfMonth)), TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } } } // Gift Type must be one of our predefined constants ValidationColumn = ARow.Table.Columns[AGiftBatchTable.ColumnGiftTypeId]; ValidationContext = ARow.BatchNumber; if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { if (!ARow.IsGiftTypeNull()) { // Ensure the gift type is correct and that it matches one of the allowable options (applies when importing) if ((ARow.GiftType != MFinanceConstants.GIFT_TYPE_GIFT) && (ARow.GiftType != MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND) && (ARow.GiftType != MFinanceConstants.GIFT_TYPE_OTHER)) { if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TScreenVerificationResult(ValidationContext, ValidationColumn, String.Format(Catalog.GetString("Unknown gift type '{0}'. Expected one of '{1}', '{2}' or '{3}'"), ARow.GiftType, MFinanceConstants.GIFT_TYPE_GIFT, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND, MFinanceConstants.GIFT_TYPE_OTHER), ValidationControlsData.ValidationControl, TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } } } return VerifResultCollAddedCount == 0; }
/// <summary> /// Imports currency exchange rates, daily and corporate, /// from a one-of-two-styles formatted CSV file /// </summary> /// <param name="AExchangeRDT">Daily or Corporate exchange rate table</param> /// <param name="ADataFilename">The .CSV file to process</param> /// <param name="ACSVSeparator"></param> /// <param name="ANumberFormat"></param> /// <param name="ADateFormat"></param> /// <param name="AImportMode">Daily or Corporate</param> /// <param name="AResultCollection">A validation collection to which errors will be added</param> /// <returns>The number of rows that were actually imported. Rows that duplicate existing rows do not count. /// This is usually because this is an attempt to import again after a failed previous attempt.</returns> private static int ImportCurrencyExRatesFromCSV(TTypedDataTable AExchangeRDT, string ADataFilename, string ACSVSeparator, string ANumberFormat, string ADateFormat, string AImportMode, TVerificationResultCollection AResultCollection) { if ((AImportMode != "Corporate") && (AImportMode != "Daily")) { throw new ArgumentException("Invalid value '" + AImportMode + "' for mode argument: Valid values are Corporate and Daily"); } else if ((AImportMode == "Corporate") && (AExchangeRDT.GetType() != typeof(ACorporateExchangeRateTable))) { throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Corporate'. Needs to be: ACorporateExchangeRateTable"); } else if ((AImportMode == "Daily") && (AExchangeRDT.GetType() != typeof(ADailyExchangeRateTable))) { throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Daily'. Needs to be: ADailyExchangeRateTable"); } bool IsShortFileFormat; int x, y; // To store the From and To currencies // Use an array to store these to make for easy // inverting of the two currencies when calculating // the inverse value. string[] Currencies = new string[2]; Type DataTableType; int RowsImported = 0; ACurrencyTable allCurrencies = new ACurrencyTable(); DataTable CacheDT = TDataCache.GetCacheableDataTableFromCache("CurrencyCodeList", String.Empty, null, out DataTableType); allCurrencies.Merge(CacheDT); using (StreamReader DataFile = new StreamReader(ADataFilename, System.Text.Encoding.Default)) { string ThousandsSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "," : "."); string DecimalSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "." : ","); CultureInfo MyCultureInfoDate = new CultureInfo("en-GB"); MyCultureInfoDate.DateTimeFormat.ShortDatePattern = ADateFormat; // TODO: disconnect the grid from the datasource to avoid flickering? string FileNameWithoutExtension = Path.GetFileNameWithoutExtension(ADataFilename); if ((FileNameWithoutExtension.IndexOf("_") == 3) && (FileNameWithoutExtension.LastIndexOf("_") == 3) && (FileNameWithoutExtension.Length == 7)) { // File name format assumed to be like this: USD_HKD.csv IsShortFileFormat = true; Currencies = FileNameWithoutExtension.Split(new char[] { '_' }); } else { IsShortFileFormat = false; } int LineNumber = 0; while (!DataFile.EndOfStream) { string Line = DataFile.ReadLine(); LineNumber++; // See if the first line is a special case?? if (LineNumber == 1) { // see if the first line is a text header - look for digits // A valid header would look like: From,To,Date,Rate bool bFoundDigit = false; for (int i = 0; i < Line.Length; i++) { char c = Line[i]; if ((c >= '0') && (c <= '9')) { bFoundDigit = true; break; } } if (!bFoundDigit) { // No digits so we will assume the line is a header continue; } } //Convert separator to a char char Sep = ACSVSeparator[0]; //Turn current line into string array of column values string[] CsvColumns = Line.Split(Sep); int NumCols = CsvColumns.Length; //If number of columns is not 4 then import csv file is wrongly formed. if (IsShortFileFormat && (NumCols < 2)) { // raise an error string resultText = String.Format(Catalog.GetString("Failed to import the CSV currency file:{0} {1}{0}{0}"), Environment.NewLine, ADataFilename); resultText += String.Format(Catalog.GetString( "Line #{1} contains {2} column(s). Import files with names like 'USD_HKD.csv', where the From and To currencies are given in the name, should contain 2 or 3 columns:{0}{0}"), Environment.NewLine, LineNumber, NumCols.ToString()); resultText += String.Format(Catalog.GetString( " 1. Effective Date{0} 2. Exchange Rate{0} 3. Effective time in seconds (Optional for Daily Rate only)"), Environment.NewLine); TVerificationResult result = new TVerificationResult(AImportMode, resultText, CommonErrorCodes.ERR_INFORMATIONMISSING, TResultSeverity.Resv_Critical); AResultCollection.Add(result); return RowsImported; } else if (!IsShortFileFormat && (NumCols < 4)) { string resultText = String.Format(Catalog.GetString("Failed to import the CSV currency file:{0} {1}{0}{0}"), Environment.NewLine, ADataFilename); resultText += String.Format(Catalog.GetString("Line #{1} contains {2} column(s). It should have 4 or 5 as follows:{0}{0}"), Environment.NewLine, LineNumber, NumCols.ToString()); resultText += String.Format(Catalog.GetString( " 1. From Currency{0} 2. To Currency{0} 3. Effective Date{0} 4. Exchange Rate{0} 5. Effective time in seconds (Optional for Daily Rate only)"), Environment.NewLine); TVerificationResult result = new TVerificationResult(AImportMode, resultText, CommonErrorCodes.ERR_INFORMATIONMISSING, TResultSeverity.Resv_Critical); AResultCollection.Add(result); return RowsImported; } if (!IsShortFileFormat) { //Read the values for the current line //From currency Currencies[0] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString(); //To currency Currencies[1] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString(); } // Perform validation on the From and To currencies at this point!! if ((allCurrencies.Rows.Find(Currencies[0]) == null) || (allCurrencies.Rows.Find(Currencies[1]) == null)) { // raise an error string resultText = String.Format(Catalog.GetString("Invalid currency in import file in line #{0}"), LineNumber.ToString()); TVerificationResult result = new TVerificationResult(AImportMode, resultText, CommonErrorCodes.ERR_INCONGRUOUSSTRINGS, TResultSeverity.Resv_Critical); AResultCollection.Add(result); return RowsImported; } // Date parsing as in Petra 2.x instead of using XML date format!!! string DateEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace("\"", String.Empty); DateTime DateEffective; if (!DateTime.TryParse(DateEffectiveStr, MyCultureInfoDate, DateTimeStyles.None, out DateEffective)) { // raise an error string resultText = String.Format(Catalog.GetString( "Invalid date ({0}) in import file in line #{1}"), DateEffectiveStr, LineNumber.ToString()); TVerificationResult result = new TVerificationResult(AImportMode, resultText, CommonErrorCodes.ERR_INVALIDDATE, TResultSeverity.Resv_Critical); AResultCollection.Add(result); return RowsImported; } decimal ExchangeRate = 0.0m; try { string ExchangeRateString = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace(ThousandsSeparator, "").Replace( DecimalSeparator, ".").Replace("\"", String.Empty); ExchangeRate = Convert.ToDecimal(ExchangeRateString, System.Globalization.CultureInfo.InvariantCulture); } catch (Exception) { // raise an error string resultText = String.Format(Catalog.GetString( "Invalid rate of exchange in import file in line #{0}"), LineNumber.ToString()); TVerificationResult result = new TVerificationResult(AImportMode, resultText, CommonErrorCodes.ERR_INVALIDNUMBER, TResultSeverity.Resv_Critical); AResultCollection.Add(result); return RowsImported; } int TimeEffective = 7200; if (AImportMode == "Daily") { // Daily rate imports can have an optional final column which is the time // Otherwise we assume the time is a default of 7200 (02:00am) if ((IsShortFileFormat && (NumCols == 3)) || (!IsShortFileFormat && (NumCols == 5))) { string timeEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true); int t = (int)new Ict.Common.TypeConverter.TShortTimeConverter().ConvertTo(timeEffectiveStr, typeof(int)); if (t < 0) { // it wasn't in the format 02:00 if (!Int32.TryParse(timeEffectiveStr, out t)) { // Not a regular Int32 either t = -1; } } if ((t >= 0) && (t < 86400)) { TimeEffective = t; } else { // raise an error string resultText = String.Format(Catalog.GetString( "Invalid effective time in import file in line #{0}"), LineNumber.ToString()); TVerificationResult result = new TVerificationResult(AImportMode, resultText, CommonErrorCodes.ERR_INVALIDINTEGERTIME, TResultSeverity.Resv_Critical); AResultCollection.Add(result); return RowsImported; } } } if ((AImportMode == "Corporate") && AExchangeRDT is ACorporateExchangeRateTable) { ACorporateExchangeRateTable ExchangeRateDT = (ACorporateExchangeRateTable)AExchangeRDT; // run this code in the loop twice to get ExchangeRate value and its inverse for (int i = 0; i <= 1; i++) { //this will cause x and y to go from 0 to 1 and 1 to 0 respectively x = i; y = Math.Abs(i - 1); ACorporateExchangeRateRow ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.Rows. Find(new object[] { Currencies[x], Currencies[y], DateEffective }); if (ExchangeRow == null) // remove 0 for Corporate { ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.NewRowTyped(); ExchangeRow.FromCurrencyCode = Currencies[x]; ExchangeRow.ToCurrencyCode = Currencies[y]; ExchangeRow.DateEffectiveFrom = DateEffective; ExchangeRateDT.Rows.Add(ExchangeRow); RowsImported++; } if (i == 0) { ExchangeRow.RateOfExchange = ExchangeRate; } else { ExchangeRow.RateOfExchange = 1 / ExchangeRate; } } } else if ((AImportMode == "Daily") && AExchangeRDT is ADailyExchangeRateTable) { ADailyExchangeRateTable ExchangeRateDT = (ADailyExchangeRateTable)AExchangeRDT; // run this code in the loop twice to get ExchangeRate value and its inverse for (int i = 0; i <= 1; i++) { //this will cause x and y to go from 0 to 1 and 1 to 0 respectively x = i; y = Math.Abs(i - 1); ADailyExchangeRateRow ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.Rows. Find(new object[] { Currencies[x], Currencies[y], DateEffective, TimeEffective }); if (ExchangeRow == null) // remove 0 for Corporate { ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.NewRowTyped(); ExchangeRow.FromCurrencyCode = Currencies[x]; ExchangeRow.ToCurrencyCode = Currencies[y]; ExchangeRow.DateEffectiveFrom = DateEffective; ExchangeRow.TimeEffectiveFrom = TimeEffective; ExchangeRateDT.Rows.Add(ExchangeRow); RowsImported++; } if (i == 0) { ExchangeRow.RateOfExchange = ExchangeRate; } else { ExchangeRow.RateOfExchange = 1 / ExchangeRate; } } } } DataFile.Close(); return RowsImported; } }
/// <summary> /// Imports currency exchange rates, daily and corporate, /// from a one-of-two-styles formatted CSV file /// </summary> /// <param name="AExchangeRDT">Daily or Corporate exchange rate table</param> /// <param name="ADataFilename">The .CSV file to process</param> /// <param name="ACSVSeparator"></param> /// <param name="ANumberFormat"></param> /// <param name="ADateFormat"></param> /// <param name="AImportMode">Daily or Corporate</param> /// <param name="AResultCollection">A validation collection to which errors will be added</param> /// <returns>The number of rows that were actually imported. Rows that duplicate existing rows do not count. /// This is usually because this is an attempt to import again after a failed previous attempt.</returns> private static int ImportCurrencyExRatesFromCSV(TTypedDataTable AExchangeRDT, string ADataFilename, string ACSVSeparator, string ANumberFormat, string ADateFormat, string AImportMode, TVerificationResultCollection AResultCollection) { // Keep a list of errors/warnings and severity List <Tuple <string, TResultSeverity> > InvalidRows = new List <Tuple <string, TResultSeverity> >(); // keep a variable that becomes true if any row has an invalid column count, so we can show a helpful message bool InvalidColumnCount = false; // Check our method parameters if ((AImportMode != "Corporate") && (AImportMode != "Daily")) { throw new ArgumentException("Invalid value '" + AImportMode + "' for mode argument: Valid values are Corporate and Daily"); } else if ((AImportMode == "Corporate") && !(AExchangeRDT is ACorporateExchangeRateTable)) { throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Corporate'. Needs to be: ACorporateExchangeRateTable"); } else if ((AImportMode == "Daily") && !(AExchangeRDT is ADailyExchangeRateTable)) { throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Daily'. Needs to be: ADailyExchangeRateTable"); } bool IsShortFileFormat; int x, y; // To store the From and To currencies // Use an array to store these to make for easy // inverting of the two currencies when calculating // the inverse value. string[] Currencies = new string[2]; Type DataTableType; int RowsImported = 0; // This table will tell us the base currencies used by the current set of ledgers ALedgerTable ledgers = TRemote.MFinance.Setup.WebConnectors.GetAvailableLedgers(); List <string> allBaseCurrencies = new List <string>(); DateTime maxRecommendedEffectiveDate = DateTime.MaxValue; DateTime minRecommendedEffectiveDate = DateTime.MinValue; int preferredPeriodStartDay = 0; // Use the ledger table rows to get a list of base currencies and current period end dates for (int i = 0; i < ledgers.Rows.Count; i++) { ALedgerRow ledger = (ALedgerRow)ledgers.Rows[i]; string code = ledger.BaseCurrency; if (ledger.LedgerStatus == true) { if (allBaseCurrencies.Contains(code) == false) { allBaseCurrencies.Add(code); } DataTable AccountingPeriods = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountingPeriodList, ledger.LedgerNumber); AAccountingPeriodRow currentPeriodRow = (AAccountingPeriodRow)AccountingPeriods.Rows.Find(new object[] { ledger.LedgerNumber, ledger.CurrentPeriod }); AAccountingPeriodRow forwardPeriodRow = (AAccountingPeriodRow)AccountingPeriods.Rows.Find( new object[] { ledger.LedgerNumber, ledger.CurrentPeriod + ledger.NumberFwdPostingPeriods }); if ((forwardPeriodRow != null) && ((maxRecommendedEffectiveDate == DateTime.MaxValue) || (forwardPeriodRow.PeriodEndDate > maxRecommendedEffectiveDate))) { maxRecommendedEffectiveDate = forwardPeriodRow.PeriodEndDate; } if ((currentPeriodRow != null) && ((minRecommendedEffectiveDate == DateTime.MinValue) || (currentPeriodRow.PeriodStartDate > minRecommendedEffectiveDate))) { minRecommendedEffectiveDate = currentPeriodRow.PeriodStartDate; } if ((currentPeriodRow != null) && (preferredPeriodStartDay == 0)) { preferredPeriodStartDay = currentPeriodRow.PeriodStartDate.Day; } else if ((currentPeriodRow != null) && (currentPeriodRow.PeriodStartDate.Day != preferredPeriodStartDay)) { preferredPeriodStartDay = -1; } } } // This will tell us the complete list of all available currencies ACurrencyTable allCurrencies = new ACurrencyTable(); DataTable CacheDT = TDataCache.GetCacheableDataTableFromCache("CurrencyCodeList", String.Empty, null, out DataTableType); allCurrencies.Merge(CacheDT); allCurrencies.CaseSensitive = true; // Start reading the file using (StreamReader DataFile = new StreamReader(ADataFilename, System.Text.Encoding.Default)) { string ThousandsSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "," : "."); string DecimalSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "." : ","); CultureInfo MyCultureInfoDate = new CultureInfo("en-GB"); MyCultureInfoDate.DateTimeFormat.ShortDatePattern = ADateFormat; // TODO: disconnect the grid from the datasource to avoid flickering? string FileNameWithoutExtension = Path.GetFileNameWithoutExtension(ADataFilename).ToUpper(); if ((FileNameWithoutExtension.IndexOf("_") == 3) && (FileNameWithoutExtension.LastIndexOf("_") == 3) && (FileNameWithoutExtension.Length == 7)) { // File name format assumed to be like this: USD_HKD.csv IsShortFileFormat = true; Currencies = FileNameWithoutExtension.Split(new char[] { '_' }); } else { IsShortFileFormat = false; } int LineNumber = 0; while (!DataFile.EndOfStream) { string Line = DataFile.ReadLine(); LineNumber++; // See if the first line is a special case?? if (LineNumber == 1) { // see if the first line is a text header - look for digits // A valid header would look like: From,To,Date,Rate bool bFoundDigit = false; for (int i = 0; i < Line.Length; i++) { char c = Line[i]; if ((c >= '0') && (c <= '9')) { bFoundDigit = true; break; } } if (!bFoundDigit) { // No digits so we will assume the line is a header continue; } } //Convert separator to a char char Sep = ACSVSeparator[0]; //Turn current line into string array of column values string[] CsvColumns = Line.Split(Sep); int NumCols = CsvColumns.Length; // Do we have the correct number of columns? int minColCount = IsShortFileFormat ? 2 : 4; int maxColCount = (AImportMode == "Daily") ? minColCount + 1 : minColCount; if ((NumCols < minColCount) || (NumCols > maxColCount)) { // raise an error string resultText = String.Format(Catalog.GetPluralString( "Line {0}: contains 1 column", "Line {0}: contains {1} columns", NumCols, true), LineNumber, NumCols.ToString()); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); InvalidColumnCount = true; continue; } if (!IsShortFileFormat) { //Read the values for the current line //From currency Currencies[0] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString().ToUpper(); //To currency Currencies[1] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString().ToUpper(); } // Perform validation on the From and To currencies at this point!! if ((allCurrencies.Rows.Find(Currencies[0]) == null) && (allCurrencies.Rows.Find(Currencies[1]) == null)) { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid currency codes ({1} and {2})"), LineNumber.ToString(), Currencies[0], Currencies[1]); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } else if (allCurrencies.Rows.Find(Currencies[0]) == null) { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid currency code ({1})"), LineNumber.ToString(), Currencies[0]); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } else if (allCurrencies.Rows.Find(Currencies[1]) == null) { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid currency code ({1})"), LineNumber.ToString(), Currencies[1]); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } if ((allBaseCurrencies.Contains(Currencies[0]) == false) && (allBaseCurrencies.Contains(Currencies[1]) == false)) { //raise a non-critical error string resultText = String.Format(Catalog.GetString( "Line {0}: Warning: One of '{1}' and '{2}' should be a base currency in one of the active ledgers."), LineNumber.ToString(), Currencies[0], Currencies[1]); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical)); } // Date parsing as in Petra 2.x instead of using XML date format!!! string DateEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace("\"", String.Empty); DateTime DateEffective; if (!DateTime.TryParse(DateEffectiveStr, MyCultureInfoDate, DateTimeStyles.None, out DateEffective)) { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid date ({1})"), LineNumber.ToString(), DateEffectiveStr); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } if (DateEffective > maxRecommendedEffectiveDate) { // raise a warning string resultText = String.Format(Catalog.GetString( "Line {0}: Warning: The date '{1}' is after the latest forwarding period of any active ledger"), LineNumber.ToString(), DateEffectiveStr); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical)); } else if (DateEffective < minRecommendedEffectiveDate) { // raise a warning string resultText = String.Format(Catalog.GetString( "Line {0}: Warning: The date '{1}' is before the current accounting period of any active ledger"), LineNumber.ToString(), DateEffectiveStr); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical)); } if (AImportMode == "Corporate") { if ((preferredPeriodStartDay >= 1) && (DateEffective.Day != preferredPeriodStartDay)) { // raise a warning string resultText = String.Format(Catalog.GetString( "Line {0}: Warning: The date '{1}' should be the first day of an accounting period used by all the active ledgers."), LineNumber.ToString(), DateEffectiveStr); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical)); } } decimal ExchangeRate = 0.0m; try { string ExchangeRateString = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace(ThousandsSeparator, "").Replace( DecimalSeparator, ".").Replace("\"", String.Empty); ExchangeRate = Convert.ToDecimal(ExchangeRateString, System.Globalization.CultureInfo.InvariantCulture); if (ExchangeRate == 0) { throw new Exception(); } } catch (Exception) { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid rate of exchange ({1})"), LineNumber.ToString(), ExchangeRate); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } int TimeEffective = 7200; if (AImportMode == "Daily") { // Daily rate imports can have an optional final column which is the time // Otherwise we assume the time is a default of 7200 (02:00am) if ((IsShortFileFormat && (NumCols == 3)) || (!IsShortFileFormat && (NumCols == 5))) { string timeEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true); int t = (int)new Ict.Common.TypeConverter.TShortTimeConverter().ConvertTo(timeEffectiveStr, typeof(int)); if (t < 0) { // it wasn't in the format 02:00 if (!Int32.TryParse(timeEffectiveStr, out t)) { // Not a regular Int32 either t = -1; } } if ((t >= 0) && (t < 86400)) { TimeEffective = t; } else { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid effective time ({1})"), LineNumber.ToString(), t); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } } } if ((AImportMode == "Corporate") && AExchangeRDT is ACorporateExchangeRateTable) { ACorporateExchangeRateTable ExchangeRateDT = (ACorporateExchangeRateTable)AExchangeRDT; // run this code in the loop twice to get ExchangeRate value and its inverse for (int i = 0; i <= 1; i++) { //this will cause x and y to go from 0 to 1 and 1 to 0 respectively x = i; y = Math.Abs(i - 1); ACorporateExchangeRateRow ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.Rows. Find(new object[] { Currencies[x], Currencies[y], DateEffective }); if (ExchangeRow == null) // remove 0 for Corporate { ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.NewRowTyped(); ExchangeRow.FromCurrencyCode = Currencies[x]; ExchangeRow.ToCurrencyCode = Currencies[y]; ExchangeRow.DateEffectiveFrom = DateEffective; ExchangeRateDT.Rows.Add(ExchangeRow); RowsImported++; } if (i == 0) { ExchangeRow.RateOfExchange = ExchangeRate; } else { ExchangeRow.RateOfExchange = Math.Round(1 / ExchangeRate, 10); } } } else if ((AImportMode == "Daily") && AExchangeRDT is ADailyExchangeRateTable) { ADailyExchangeRateTable ExchangeRateDT = (ADailyExchangeRateTable)AExchangeRDT; // run this code in the loop twice to get ExchangeRate value and its inverse for (int i = 0; i <= 1; i++) { //this will cause x and y to go from 0 to 1 and 1 to 0 respectively x = i; y = Math.Abs(i - 1); ADailyExchangeRateRow ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.Rows. Find(new object[] { Currencies[x], Currencies[y], DateEffective, TimeEffective }); if (ExchangeRow == null) // remove 0 for Corporate { ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.NewRowTyped(); ExchangeRow.FromCurrencyCode = Currencies[x]; ExchangeRow.ToCurrencyCode = Currencies[y]; ExchangeRow.DateEffectiveFrom = DateEffective; ExchangeRow.TimeEffectiveFrom = TimeEffective; ExchangeRateDT.Rows.Add(ExchangeRow); RowsImported++; } if (i == 0) { ExchangeRow.RateOfExchange = ExchangeRate; } else { ExchangeRow.RateOfExchange = Math.Round(1 / ExchangeRate, 10); } } } } // if there are rows that could not be imported if ((InvalidRows != null) && (InvalidRows.Count > 0)) { int errorCount = 0; int warningCount = 0; // Go through once just to count the errors and warnings foreach (Tuple <string, TResultSeverity> Row in InvalidRows) { if (Row.Item2 == TResultSeverity.Resv_Noncritical) { warningCount++; } else { errorCount++; } } string resultText = String.Empty; bool messageListIsFull = false; int counter = 0; if (errorCount > 0) { resultText = string.Format(Catalog.GetPluralString("1 row was not imported due to invalid data:", "{0} rows were not imported due to invalid data:", errorCount, true), errorCount) + Environment.NewLine; } if (warningCount > 0) { resultText = string.Format(Catalog.GetPluralString("There was 1 warning associated with the imported rows:", "There were {0} warnings associated with the imported rows:", warningCount, true), warningCount) + Environment.NewLine; } // Now go through again itemising each one foreach (Tuple <string, TResultSeverity> Row in InvalidRows) { counter++; if (counter <= MAX_MESSAGE_COUNT) { resultText += Environment.NewLine + Row.Item1; } else if (!messageListIsFull) { resultText += String.Format(Catalog.GetString( "{0}{0}{1} errors/warnings were reported in total. This message contains the first {2}."), Environment.NewLine, InvalidRows.Count, MAX_MESSAGE_COUNT); messageListIsFull = true; } } // additional message if one or more rows has an invalid number of columns if (InvalidColumnCount && IsShortFileFormat) { resultText += String.Format("{0}{0}" + Catalog.GetString("Each row should contain 2 or 3 columns as follows:") + "{0}" + Catalog.GetString( " 1. Effective Date{0} 2. Exchange Rate{0} 3. Effective time in seconds (Optional for Daily Rate only)"), Environment.NewLine); } else if (InvalidColumnCount && !IsShortFileFormat) { resultText += String.Format("{0}{0}" + Catalog.GetString("Each row should contain 4 or 5 columns as follows:") + "{0}" + Catalog.GetString( " 1. From Currency{0} 2. To Currency{0} 3. Effective Date{0} 4. Exchange Rate{0} 5. Effective time in seconds (Optional for Daily Rate only)"), Environment.NewLine); } TVerificationResult result = new TVerificationResult(AImportMode, resultText, CommonErrorCodes.ERR_INCONGRUOUSSTRINGS, (errorCount > 0) ? TResultSeverity.Resv_Critical : TResultSeverity.Resv_Noncritical); AResultCollection.Add(result); } DataFile.Close(); return(RowsImported); } }
private void ParseBatchLine(ref AGiftBatchRow AGiftBatch, ref TDBTransaction ATransaction, ref ALedgerTable ALedgerTable, ref string AImportMessage, int ARowNumber, TVerificationResultCollection AMessages, TValidationControlsDict AValidationControlsDictBatch, AAccountTable AValidationAccountTable, AAccountPropertyTable AValidationAccountPropertyTable, AAccountingPeriodTable AValidationAccountingPeriodTable, ACostCentreTable AValidationCostCentreTable, ACorporateExchangeRateTable AValidationCorporateExchTable, ACurrencyTable AValidationCurrencyTable) { // There are 8 elements to import (the last of which can be blank) string BatchDescription = ImportString(Catalog.GetString("Batch description"), FMainDS.AGiftBatch.ColumnBatchDescription, AValidationControlsDictBatch); string BankAccountCode = ImportString(Catalog.GetString("Bank account code"), FMainDS.AGiftBatch.ColumnBankAccountCode, AValidationControlsDictBatch).ToUpper(); decimal HashTotal = ImportDecimal(Catalog.GetString("Hash total"), FMainDS.AGiftBatch.ColumnHashTotal, ARowNumber, AMessages, AValidationControlsDictBatch); DateTime GlEffectiveDate = ImportDate(Catalog.GetString("Effective Date"), FMainDS.AGiftBatch.ColumnGlEffectiveDate, ARowNumber, AMessages, AValidationControlsDictBatch); AImportMessage = "Creating new batch"; // This call sets: BatchNumber, BatchYear, BatchPeriod, GlEffectiveDate, ExchangeRateToBase, BatchDescription, BankAccountCode // BankCostCentre and CurrencyCode. The effective date will NOT be modified. // The first three are not validated because they should be ok by default AGiftBatch = TGiftBatchFunctions.CreateANewGiftBatchRow(ref FMainDS, ref ATransaction, ref ALedgerTable, FLedgerNumber, GlEffectiveDate, false); // Now we modify some of these in the light of the imported data AGiftBatch.BatchDescription = BatchDescription; AGiftBatch.BankAccountCode = BankAccountCode; AGiftBatch.HashTotal = HashTotal; AGiftBatch.CurrencyCode = ImportString(Catalog.GetString("Currency code"), FMainDS.AGiftBatch.ColumnCurrencyCode, AValidationControlsDictBatch); AGiftBatch.ExchangeRateToBase = ImportDecimal(Catalog.GetString("Exchange rate to base"), FMainDS.AGiftBatch.ColumnExchangeRateToBase, ARowNumber, AMessages, AValidationControlsDictBatch); AGiftBatch.BankCostCentre = ImportString(Catalog.GetString("Bank cost centre"), FMainDS.AGiftBatch.ColumnBankCostCentre, AValidationControlsDictBatch).ToUpper(); AGiftBatch.GiftType = ImportString(Catalog.GetString("Gift type"), FMainDS.AGiftBatch.ColumnGiftType, AValidationControlsDictBatch); // If GiftType was empty, will default to GIFT // In all cases we ensure that the case entered by the user is converted to the case of our constants if ((AGiftBatch.GiftType == String.Empty) || (String.Compare(AGiftBatch.GiftType, MFinanceConstants.GIFT_TYPE_GIFT, true) == 0)) { AGiftBatch.GiftType = MFinanceConstants.GIFT_TYPE_GIFT; } else if (String.Compare(AGiftBatch.GiftType, MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND, true) == 0) { AGiftBatch.GiftType = MFinanceConstants.GIFT_TYPE_GIFT_IN_KIND; } else if (String.Compare(AGiftBatch.GiftType, MFinanceConstants.GIFT_TYPE_OTHER, true) == 0) { AGiftBatch.GiftType = MFinanceConstants.GIFT_TYPE_OTHER; } int messageCountBeforeValidate = AMessages.Count; // Do our standard gift batch validation checks on this row AImportMessage = Catalog.GetString("Validating the gift batch data"); AGiftBatchValidation.Validate(this, AGiftBatch, ref AMessages, AValidationControlsDictBatch); // And do the additional manual ones AImportMessage = Catalog.GetString("Additional validation of the gift batch data"); TSharedFinanceValidation_Gift.ValidateGiftBatchManual(this, AGiftBatch, ref AMessages, AValidationControlsDictBatch, AValidationAccountTable, AValidationCostCentreTable, AValidationAccountPropertyTable, AValidationAccountingPeriodTable, AValidationCorporateExchTable, AValidationCurrencyTable, FLedgerBaseCurrency, FLedgerIntlCurrency); for (int i = messageCountBeforeValidate; i < AMessages.Count; i++) { ((TVerificationResult)AMessages[i]).OverrideResultContext(String.Format(MCommonConstants.StrValidationErrorInLine, ARowNumber)); if (AMessages[i] is TScreenVerificationResult) { TVerificationResult downgrade = new TVerificationResult((TScreenVerificationResult)AMessages[i]); AMessages.RemoveAt(i); AMessages.Insert(i, downgrade); } } if (AGiftBatch.ExchangeRateToBase > 10000000) // Huge numbers here indicate that the decimal comma/point is incorrect. { AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, ARowNumber), String.Format(Catalog.GetString("A huge exchange rate of {0} suggests a decimal point format problem."), AGiftBatch.ExchangeRateToBase), TResultSeverity.Resv_Noncritical)); } }
/// <summary> /// Validates the GL Journal data. /// </summary> /// <param name="AContext">Context that describes where the data validation failed.</param> /// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param> /// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if /// data validation errors occur.</param> /// <param name="AValidationControlsDict">A <see cref="TValidationControlsDict" /> containing the Controls that /// display data that is about to be validated.</param> /// <param name="AGLSetupDSRef">A GLSetupTDS reference with a populated ATransactionTypeTable. A reference to this DataSet is REQUIRED when importing - optional otherwise</param> /// <param name="ACurrencyTableRef">A reference to the Currency table. A reference to this table is REQUIRED when importing - optional otherwise</param> /// <param name="ACorporateExchangeTableRef">Corporate exchange rate table. A reference to this table is REQUIRED when importing - optional otherwise</param> /// <param name="ABaseCurrency">Ledger base currency. Required when importing</param> /// <param name="AIntlCurrency">Ledger international currency. Required when importing</param> /// <returns>True if the validation found no data validation errors, otherwise false.</returns> public static bool ValidateGLJournalManual(object AContext, AJournalRow ARow, ref TVerificationResultCollection AVerificationResultCollection, TValidationControlsDict AValidationControlsDict, GLSetupTDS AGLSetupDSRef = null, ACurrencyTable ACurrencyTableRef = null, ACorporateExchangeRateTable ACorporateExchangeTableRef = null, String ABaseCurrency = null, String AIntlCurrency = null) { DataColumn ValidationColumn; TValidationControlsData ValidationControlsData; TScreenVerificationResult VerificationResult; string ValidationContext; int VerifResultCollAddedCount = 0; // Don't validate deleted or posted DataRows if ((ARow.RowState == DataRowState.Deleted) || (ARow.JournalStatus == MFinanceConstants.BATCH_POSTED)) { return true; } bool isImporting = AContext.ToString().Contains("Importing"); // 'Exchange Rate' must be greater than 0 ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnExchangeRateToBaseId]; ValidationContext = ARow.JournalNumber.ToString() + " of Batch Number: " + ARow.BatchNumber.ToString(); if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { VerificationResult = (TScreenVerificationResult)TNumericalChecks.IsPositiveDecimal(ARow.ExchangeRateToBase, ValidationControlsData.ValidationControlLabel + (isImporting ? String.Empty : " of Journal Number: " + ValidationContext.ToString()), AContext, ValidationColumn, ValidationControlsData.ValidationControl); // Handle addition/removal to/from TVerificationResultCollection if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult, ValidationColumn, true)) { VerifResultCollAddedCount++; } // Exchange rate must be 1.00 if the currency is the base ledger currency if ((ABaseCurrency != null) && (!ARow.IsExchangeRateToBaseNull()) && (ARow.TransactionCurrency == ABaseCurrency) && (ARow.ExchangeRateToBase != 1.00m)) { if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, new TVerificationResult(ValidationContext, Catalog.GetString("A journal in the ledger base currency must have exchange rate of 1.00."), TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } } // Transaction currency must be valid bool isValidTransactionCurrency = true; if (isImporting && (ACurrencyTableRef != null)) { ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnTransactionCurrencyId]; if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { ACurrencyRow foundRow = (ACurrencyRow)ACurrencyTableRef.Rows.Find(ARow.TransactionCurrency); isValidTransactionCurrency = (foundRow != null); if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString("'{0}' is not a valid currency."), ARow.TransactionCurrency), TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } } if ((ACorporateExchangeTableRef != null) && isValidTransactionCurrency && (ABaseCurrency != null) && (AIntlCurrency != null) && !ARow.IsDateEffectiveNull() && (ABaseCurrency != AIntlCurrency)) { // For ledgers where the base currency and intl currency differ there must be a corporate exchange rate to the international currency ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnTransactionCurrencyId]; ValidationContext = ARow.JournalNumber.ToString() + " of Batch Number: " + ARow.BatchNumber.ToString(); if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { DateTime firstOfMonth; if (TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriod(ARow.LedgerNumber, ARow.DateEffective, out firstOfMonth)) { ACorporateExchangeRateRow foundRow = (ACorporateExchangeRateRow)ACorporateExchangeTableRef.Rows.Find( new object[] { ABaseCurrency, AIntlCurrency, firstOfMonth }); if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString( "There is no Corporate Exchange Rate defined for '{0}' to '{1}' for the month starting on '{2}'."), ABaseCurrency, AIntlCurrency, StringHelper.DateToLocalizedString(firstOfMonth)), TResultSeverity.Resv_Noncritical), ValidationColumn)) { VerifResultCollAddedCount++; } } } } // Sub-system code must exist in the transaction type table if (isImporting && (AGLSetupDSRef.ATransactionType != null)) { ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnSubSystemCodeId]; if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { ATransactionTypeRow foundRow = (ATransactionTypeRow)AGLSetupDSRef.ATransactionType.Rows.Find( new object[] { ARow.LedgerNumber, ARow.SubSystemCode, ARow.TransactionTypeCode }); if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString( "The combination of Transaction Type of '{0}' and Sub-system Code of '{1}' is not valid for journals in Ledger {2}."), ARow.TransactionTypeCode, ARow.SubSystemCode, ARow.LedgerNumber), TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } } // Journal description must not be null if (isImporting) { ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnJournalDescriptionId]; if (AValidationControlsDict.TryGetValue(ValidationColumn, out ValidationControlsData)) { if ((ARow.JournalDescription == null) || (ARow.JournalDescription.Length == 0)) { if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, Catalog.GetString("The journal description must not be empty."), TResultSeverity.Resv_Critical), ValidationColumn)) { VerifResultCollAddedCount++; } } } } return VerifResultCollAddedCount == 0; }
private void LoadDatabase() { intBaseCurrencyDigits = DIGIT_INIT_VALUE; intForeignCurrencyDigits = DIGIT_INIT_VALUE; TDBTransaction transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref transaction, delegate { currencyTable = ACurrencyAccess.LoadAll(transaction); }); if (currencyTable.Rows.Count == 0) { EVerificationException terminate = new EVerificationException( Catalog.GetString("The table a_currency is empty!")); terminate.Context = "Common Accounting"; terminate.ErrorCode = "TCurrencyInfo01"; throw terminate; } }
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); }
private void NewRowManual(ref ACurrencyLanguageRow ARow) { // Deal with primary key. CurrencyCode (varchar(16)) and LanguageCode (varchar(20)) are unique. if (FMainDS.ACurrencyLanguage.Rows.Count == 0) { // Our first row is always USD/EN ARow.CurrencyCode = "USD"; ARow.LanguageCode = "EN"; return; } else { // We already have some rows, so we use the currently selected language in the comboBox as a starter // This may or may not be the most recently added language depending on how the main data set has been sorted // But once we have started adding rows it should remain the 'active' row string prevLanguage = cmbDetailLanguageCode.cmbCombobox.Text; // Try and find some popular or at least likely currencies // Remember that we will almost certainly select a currency that the user does NOT want in their language // So this will be the one we always propose!!!! string[] tryCurrencies = { "USD", "GBP", "EUR", "INR", "AUD", "CAD", "CHF", "CNY", "JPY", "NZD", "PHP", "PKR", "ZAR" }; int nTryCurrency = 0; bool bFoundCurrency = true; while (FMainDS.ACurrencyLanguage.Rows.Find(new object[] { tryCurrencies[nTryCurrency], prevLanguage }) != null) { if (++nTryCurrency == tryCurrencies.Length) { // We have exhausted our popular choices bFoundCurrency = false; break; } } if (bFoundCurrency) { ARow.CurrencyCode = tryCurrencies[nTryCurrency]; ARow.LanguageCode = prevLanguage; return; } // So we have tried all the popular currencies in the current language // Now we fall back to trying all currencies in the current languages. // Remember that we will almost certainly select a currency that the user does NOT want in their language // So this will be the one we always propose!!!! Type DataTableType; ACurrencyTable allCurrencies = new ACurrencyTable(); DataTable CacheDT = TDataCache.GetCacheableDataTableFromCache("CurrencyCodeList", String.Empty, null, out DataTableType); allCurrencies.Merge(CacheDT); nTryCurrency = 0; bFoundCurrency = true; while (FMainDS.ACurrencyLanguage.Rows.Find(new object[] { allCurrencies.Rows[nTryCurrency][0].ToString(), prevLanguage }) != null) { if (++nTryCurrency == tryCurrencies.Length) { bFoundCurrency = false; break; } } if (bFoundCurrency) { ARow.CurrencyCode = allCurrencies.Rows[nTryCurrency][0].ToString(); ARow.LanguageCode = prevLanguage; } // We could at this point start trying other languages - but there seems little point since the currency list contains // currencies that will never be used since they no longer exist!! It is therefore assumed that by this time // the user has gone on to a different langauge where we will have started again with USD .... } }
/// <summary> /// get more details of the last gift of the partner /// </summary> /// <param name="APartnerKey"></param> /// <param name="ALastGiftDate"></param> /// <param name="ALastGiftAmount"></param> /// <param name="ALastGiftGivenToPartnerKey"></param> /// <param name="ALastGiftRecipientLedger"></param> /// <param name="ALastGiftCurrencyCode"></param> /// <param name="ALastGiftDisplayFormat"></param> /// <param name="ALastGiftGivenToShortName"></param> /// <param name="ALastGiftRecipientLedgerShortName"></param> /// <param name="ARestrictedOrConfidentialGiftAccessDenied"></param> /// <returns></returns> public static Boolean GetLastGiftDetails(Int64 APartnerKey, out DateTime ALastGiftDate, out decimal ALastGiftAmount, out Int64 ALastGiftGivenToPartnerKey, out Int64 ALastGiftRecipientLedger, out String ALastGiftCurrencyCode, out String ALastGiftDisplayFormat, out String ALastGiftGivenToShortName, out String ALastGiftRecipientLedgerShortName, out Boolean ARestrictedOrConfidentialGiftAccessDenied) { DataSet LastGiftDS; AGiftDetailTable GiftDetailDT; SGroupGiftTable GroupGiftDT; SUserGroupTable UserGroupDT; AGiftRow GiftDR; AGiftBatchRow GiftBatchDR; AGiftDetailRow GiftDetailDR; ACurrencyRow CurrencyDR; Int16 Counter; Boolean AccessToGift = false; DataRow[] FoundUserGroups; ALastGiftAmount = 0; ALastGiftCurrencyCode = ""; ALastGiftDisplayFormat = ""; ALastGiftDate = DateTime.MinValue; ALastGiftGivenToPartnerKey = 0; ALastGiftGivenToShortName = ""; ALastGiftRecipientLedger = 0; ALastGiftRecipientLedgerShortName = ""; ARestrictedOrConfidentialGiftAccessDenied = false; DateTime tmpLastGiftDate = ALastGiftDate; decimal tmpLastGiftAmount = ALastGiftAmount; Int64 tmpLastGiftGivenToPartnerKey = ALastGiftGivenToPartnerKey; Int64 tmpLastGiftRecipientLedger = ALastGiftRecipientLedger; String tmpLastGiftCurrencyCode = ALastGiftCurrencyCode; String tmpLastGiftDisplayFormat = ALastGiftDisplayFormat; String tmpLastGiftGivenToShortName = ALastGiftGivenToShortName; String tmpLastGiftRecipientLedgerShortName = ALastGiftRecipientLedgerShortName; Boolean tmpRestrictedOrConfidentialGiftAccessDenied = ARestrictedOrConfidentialGiftAccessDenied; if ((!UserInfo.GUserInfo.IsTableAccessOK(TTableAccessPermission.tapINQUIRE, AGiftTable.GetTableDBName()))) { // User hasn't got access to a_gift Table in the DB return(false); } // Set up temp DataSet LastGiftDS = new DataSet("LastGiftDetails"); LastGiftDS.Tables.Add(new AGiftTable()); LastGiftDS.Tables.Add(new AGiftBatchTable()); LastGiftDS.Tables.Add(new AGiftDetailTable()); LastGiftDS.Tables.Add(new ACurrencyTable()); LastGiftDS.Tables.Add(new PPartnerTable()); TDBTransaction Transaction = null; bool SubmissionOK = true; // Important: The IsolationLevel here needs to correspond with the IsolationLevel in the // Ict.Petra.Server.MPartner.Partner.UIConnectors.TPartnerEditUIConnector.LoadData Method // as otherwise the attempt of taking-out of a DB Transaction here will lead to Bug #4167! DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, ref SubmissionOK, delegate { try { try { AGiftAccess.LoadViaPPartner(LastGiftDS, APartnerKey, null, Transaction, StringHelper.InitStrArr(new String[] { "ORDER BY", AGiftTable.GetDateEnteredDBName() + " DESC" }), 0, 1); } catch (ESecurityDBTableAccessDeniedException) { // User hasn't got access to a_gift Table in the DB return; } catch (Exception ex) { throw ex; } if (LastGiftDS.Tables[AGiftTable.GetTableName()].Rows.Count == 0) { // Partner hasn't given any Gift so far return; } // Get the last gift GiftDR = ((AGiftTable)LastGiftDS.Tables[AGiftTable.GetTableName()])[0]; if (GiftDR.Restricted) { AccessToGift = false; GroupGiftDT = SGroupGiftAccess.LoadViaAGift( GiftDR.LedgerNumber, GiftDR.BatchNumber, GiftDR.GiftTransactionNumber, Transaction); UserGroupDT = SUserGroupAccess.LoadViaSUser(UserInfo.GUserInfo.UserID, Transaction); // Loop over all rows of GroupGiftDT for (Counter = 0; Counter <= GroupGiftDT.Rows.Count - 1; Counter += 1) { // To be able to view a Gift, ReadAccess must be granted if (GroupGiftDT[Counter].ReadAccess) { // Find out whether the user has a row in s_user_group with the // GroupID of the GroupGift row FoundUserGroups = UserGroupDT.Select(SUserGroupTable.GetGroupIdDBName() + " = '" + GroupGiftDT[Counter].GroupId + "'"); if (FoundUserGroups.Length != 0) { // Access to gift can be granted AccessToGift = true; continue; // don't evaluate further GroupGiftDT rows } } } } else { AccessToGift = true; } if (AccessToGift) { tmpLastGiftDate = GiftDR.DateEntered; // Console.WriteLine('GiftDR.LedgerNumber: ' + GiftDR.LedgerNumber.ToString + '; ' + // 'GiftDR.BatchNumber: ' + GiftDR.BatchNumber.ToString); // Load Gift Batch AGiftBatchAccess.LoadByPrimaryKey(LastGiftDS, GiftDR.LedgerNumber, GiftDR.BatchNumber, StringHelper.InitStrArr(new String[] { AGiftBatchTable.GetCurrencyCodeDBName() }), Transaction, null, 0, 0); if (LastGiftDS.Tables[AGiftBatchTable.GetTableName()].Rows.Count != 0) { GiftBatchDR = ((AGiftBatchRow)LastGiftDS.Tables[AGiftBatchTable.GetTableName()].Rows[0]); tmpLastGiftCurrencyCode = GiftBatchDR.CurrencyCode; // Get Currency ACurrencyAccess.LoadByPrimaryKey(LastGiftDS, GiftBatchDR.CurrencyCode, Transaction); if (LastGiftDS.Tables[ACurrencyTable.GetTableName()].Rows.Count != 0) { CurrencyDR = (ACurrencyRow)(LastGiftDS.Tables[ACurrencyTable.GetTableName()].Rows[0]); tmpLastGiftCurrencyCode = CurrencyDR.CurrencyCode; tmpLastGiftDisplayFormat = CurrencyDR.DisplayFormat; } else { tmpLastGiftCurrencyCode = ""; tmpLastGiftDisplayFormat = ""; } } else { // missing Currency tmpLastGiftCurrencyCode = ""; tmpLastGiftDisplayFormat = ""; } // Load Gift Detail AGiftDetailAccess.LoadViaAGift(LastGiftDS, GiftDR.LedgerNumber, GiftDR.BatchNumber, GiftDR.GiftTransactionNumber, StringHelper.InitStrArr(new String[] { AGiftDetailTable.GetGiftTransactionAmountDBName(), AGiftDetailTable.GetRecipientKeyDBName(), AGiftDetailTable. GetRecipientLedgerNumberDBName(), AGiftDetailTable.GetConfidentialGiftFlagDBName() }), Transaction, null, 0, 0); GiftDetailDT = (AGiftDetailTable)LastGiftDS.Tables[AGiftDetailTable.GetTableName()]; if (GiftDetailDT.Rows.Count != 0) { if (GiftDR.LastDetailNumber > 1) { // Gift is a Split Gift tmpLastGiftAmount = 0; for (Counter = 0; Counter <= GiftDetailDT.Rows.Count - 1; Counter += 1) { GiftDetailDR = (AGiftDetailRow)GiftDetailDT.Rows[Counter]; // Check for confidential gift and whether the current user is allowed to see it if (GiftDetailDR.ConfidentialGiftFlag) { if (!((UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE2)) || (UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE3)))) { // User isn't allowed to see the gift tmpRestrictedOrConfidentialGiftAccessDenied = true; tmpLastGiftAmount = 0; return; } } tmpLastGiftAmount = tmpLastGiftAmount + GiftDetailDR.GiftTransactionAmount; } tmpLastGiftGivenToShortName = ""; tmpLastGiftRecipientLedgerShortName = ""; tmpLastGiftGivenToPartnerKey = -1; tmpLastGiftRecipientLedger = -1; } else { // Gift isn't a Split Gift GiftDetailDR = (AGiftDetailRow)GiftDetailDT.Rows[0]; // Check for confidential gift and whether the current user is allowed to see it if (GiftDetailDR.ConfidentialGiftFlag) { if (!((UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE2)) || (UserInfo.GUserInfo.IsInGroup(SharedConstants.PETRAGROUP_FINANCE3)))) { // User isn't allowed to see the gift tmpRestrictedOrConfidentialGiftAccessDenied = true; return; } } tmpLastGiftAmount = GiftDetailDR.GiftTransactionAmount; tmpLastGiftGivenToPartnerKey = GiftDetailDR.RecipientKey; // Get Partner ShortName PPartnerAccess.LoadByPrimaryKey(LastGiftDS, GiftDetailDR.RecipientKey, StringHelper.InitStrArr(new String[] { PPartnerTable.GetPartnerShortNameDBName() }), Transaction, null, 0, 0); if (LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows.Count != 0) { tmpLastGiftGivenToShortName = ((PPartnerRow)(LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows[0])).PartnerShortName; } else { // missing Partner tmpLastGiftGivenToShortName = ""; } // Get rid of last record because we are about to select again into the same DataTable... LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows.Clear(); // Get Recipient Ledger PPartnerAccess.LoadByPrimaryKey(LastGiftDS, GiftDetailDR.RecipientLedgerNumber, StringHelper.InitStrArr(new String[] { PPartnerTable.GetPartnerShortNameDBName() }), Transaction, null, 0, 0); if (LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows.Count != 0) { tmpLastGiftRecipientLedgerShortName = ((PPartnerRow)(LastGiftDS.Tables[PPartnerTable.GetTableName()].Rows[0])).PartnerShortName; } else { // missing Ledger tmpLastGiftRecipientLedgerShortName = ""; } } } else { // missing Gift Detail tmpLastGiftAmount = 0; tmpLastGiftGivenToShortName = ""; tmpLastGiftRecipientLedgerShortName = ""; tmpLastGiftGivenToPartnerKey = -1; tmpLastGiftRecipientLedger = -1; } } else { // Gift is a restriced Gift and the current user isn't allowed to see it tmpRestrictedOrConfidentialGiftAccessDenied = true; } } finally { TLogging.LogAtLevel(7, "TGift.GetLastGiftDetails: committed own transaction."); } }); ALastGiftDate = tmpLastGiftDate; ALastGiftAmount = tmpLastGiftAmount; ALastGiftGivenToPartnerKey = tmpLastGiftGivenToPartnerKey; ALastGiftRecipientLedger = tmpLastGiftRecipientLedger; ALastGiftCurrencyCode = tmpLastGiftCurrencyCode; ALastGiftDisplayFormat = tmpLastGiftDisplayFormat; ALastGiftGivenToShortName = tmpLastGiftGivenToShortName; ALastGiftRecipientLedgerShortName = tmpLastGiftRecipientLedgerShortName; ARestrictedOrConfidentialGiftAccessDenied = tmpRestrictedOrConfidentialGiftAccessDenied; return(AccessToGift); }
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); }
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> /// Imports currency exchange rates, daily and corporate, /// from a one-of-two-styles formatted CSV file /// </summary> /// <param name="AExchangeRDT">Daily or Corporate exchange rate table</param> /// <param name="ADataFilename">The .CSV file to process</param> /// <param name="ACSVSeparator"></param> /// <param name="ANumberFormat"></param> /// <param name="ADateFormat"></param> /// <param name="AImportMode">Daily or Corporate</param> /// <param name="AResultCollection">A validation collection to which errors will be added</param> /// <returns>The number of rows that were actually imported. Rows that duplicate existing rows do not count. /// This is usually because this is an attempt to import again after a failed previous attempt.</returns> private static int ImportCurrencyExRatesFromCSV(TTypedDataTable AExchangeRDT, string ADataFilename, string ACSVSeparator, string ANumberFormat, string ADateFormat, string AImportMode, TVerificationResultCollection AResultCollection) { // Keep a list of errors/warnings and severity List <Tuple <string, TResultSeverity>>InvalidRows = new List <Tuple <string, TResultSeverity>>(); // keep a variable that becomes true if any row has an invalid column count, so we can show a helpful message bool InvalidColumnCount = false; // Check our method parameters if ((AImportMode != "Corporate") && (AImportMode != "Daily")) { throw new ArgumentException("Invalid value '" + AImportMode + "' for mode argument: Valid values are Corporate and Daily"); } else if ((AImportMode == "Corporate") && !(AExchangeRDT is ACorporateExchangeRateTable)) { throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Corporate'. Needs to be: ACorporateExchangeRateTable"); } else if ((AImportMode == "Daily") && !(AExchangeRDT is ADailyExchangeRateTable)) { throw new ArgumentException("Invalid type of exchangeRateDT argument for mode: 'Daily'. Needs to be: ADailyExchangeRateTable"); } bool IsShortFileFormat; int x, y; // To store the From and To currencies // Use an array to store these to make for easy // inverting of the two currencies when calculating // the inverse value. string[] Currencies = new string[2]; Type DataTableType; int RowsImported = 0; // This table will tell us the base currencies used by the current set of ledgers ALedgerTable ledgers = TRemote.MFinance.Setup.WebConnectors.GetAvailableLedgers(); List <string>allBaseCurrencies = new List <string>(); DateTime maxRecommendedEffectiveDate = DateTime.MaxValue; DateTime minRecommendedEffectiveDate = DateTime.MinValue; int preferredPeriodStartDay = 0; // Use the ledger table rows to get a list of base currencies and current period end dates for (int i = 0; i < ledgers.Rows.Count; i++) { ALedgerRow ledger = (ALedgerRow)ledgers.Rows[i]; string code = ledger.BaseCurrency; if (ledger.LedgerStatus == true) { if (allBaseCurrencies.Contains(code) == false) { allBaseCurrencies.Add(code); } DataTable AccountingPeriods = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountingPeriodList, ledger.LedgerNumber); AAccountingPeriodRow currentPeriodRow = (AAccountingPeriodRow)AccountingPeriods.Rows.Find(new object[] { ledger.LedgerNumber, ledger.CurrentPeriod }); AAccountingPeriodRow forwardPeriodRow = (AAccountingPeriodRow)AccountingPeriods.Rows.Find( new object[] { ledger.LedgerNumber, ledger.CurrentPeriod + ledger.NumberFwdPostingPeriods }); if ((forwardPeriodRow != null) && ((maxRecommendedEffectiveDate == DateTime.MaxValue) || (forwardPeriodRow.PeriodEndDate > maxRecommendedEffectiveDate))) { maxRecommendedEffectiveDate = forwardPeriodRow.PeriodEndDate; } if ((currentPeriodRow != null) && ((minRecommendedEffectiveDate == DateTime.MinValue) || (currentPeriodRow.PeriodStartDate > minRecommendedEffectiveDate))) { minRecommendedEffectiveDate = currentPeriodRow.PeriodStartDate; } if ((currentPeriodRow != null) && (preferredPeriodStartDay == 0)) { preferredPeriodStartDay = currentPeriodRow.PeriodStartDate.Day; } else if ((currentPeriodRow != null) && (currentPeriodRow.PeriodStartDate.Day != preferredPeriodStartDay)) { preferredPeriodStartDay = -1; } } } // This will tell us the complete list of all available currencies ACurrencyTable allCurrencies = new ACurrencyTable(); DataTable CacheDT = TDataCache.GetCacheableDataTableFromCache("CurrencyCodeList", String.Empty, null, out DataTableType); allCurrencies.Merge(CacheDT); allCurrencies.CaseSensitive = true; // Start reading the file using (StreamReader DataFile = new StreamReader(ADataFilename, System.Text.Encoding.Default)) { string ThousandsSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "," : "."); string DecimalSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "." : ","); CultureInfo MyCultureInfoDate = new CultureInfo("en-GB"); MyCultureInfoDate.DateTimeFormat.ShortDatePattern = ADateFormat; // TODO: disconnect the grid from the datasource to avoid flickering? string FileNameWithoutExtension = Path.GetFileNameWithoutExtension(ADataFilename).ToUpper(); if ((FileNameWithoutExtension.IndexOf("_") == 3) && (FileNameWithoutExtension.LastIndexOf("_") == 3) && (FileNameWithoutExtension.Length == 7)) { // File name format assumed to be like this: USD_HKD.csv IsShortFileFormat = true; Currencies = FileNameWithoutExtension.Split(new char[] { '_' }); } else { IsShortFileFormat = false; } int LineNumber = 0; while (!DataFile.EndOfStream) { string Line = DataFile.ReadLine(); LineNumber++; // See if the first line is a special case?? if (LineNumber == 1) { // see if the first line is a text header - look for digits // A valid header would look like: From,To,Date,Rate bool bFoundDigit = false; for (int i = 0; i < Line.Length; i++) { char c = Line[i]; if ((c >= '0') && (c <= '9')) { bFoundDigit = true; break; } } if (!bFoundDigit) { // No digits so we will assume the line is a header continue; } } //Convert separator to a char char Sep = ACSVSeparator[0]; //Turn current line into string array of column values string[] CsvColumns = Line.Split(Sep); int NumCols = CsvColumns.Length; // Do we have the correct number of columns? int minColCount = IsShortFileFormat ? 2 : 4; int maxColCount = (AImportMode == "Daily") ? minColCount + 1 : minColCount; if ((NumCols < minColCount) || (NumCols > maxColCount)) { // raise an error string resultText = String.Format(Catalog.GetPluralString( "Line {0}: contains 1 column", "Line {0}: contains {1} columns", NumCols, true), LineNumber, NumCols.ToString()); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); InvalidColumnCount = true; continue; } if (!IsShortFileFormat) { //Read the values for the current line //From currency Currencies[0] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString().ToUpper(); //To currency Currencies[1] = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).ToString().ToUpper(); } // Perform validation on the From and To currencies at this point!! if ((allCurrencies.Rows.Find(Currencies[0]) == null) && (allCurrencies.Rows.Find(Currencies[1]) == null)) { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid currency codes ({1} and {2})"), LineNumber.ToString(), Currencies[0], Currencies[1]); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } else if (allCurrencies.Rows.Find(Currencies[0]) == null) { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid currency code ({1})"), LineNumber.ToString(), Currencies[0]); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } else if (allCurrencies.Rows.Find(Currencies[1]) == null) { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid currency code ({1})"), LineNumber.ToString(), Currencies[1]); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } if ((allBaseCurrencies.Contains(Currencies[0]) == false) && (allBaseCurrencies.Contains(Currencies[1]) == false)) { //raise a non-critical error string resultText = String.Format(Catalog.GetString( "Line {0}: Warning: One of '{1}' and '{2}' should be a base currency in one of the active ledgers."), LineNumber.ToString(), Currencies[0], Currencies[1]); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical)); } // Date parsing as in Petra 2.x instead of using XML date format!!! string DateEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace("\"", String.Empty); DateTime DateEffective; if (!DateTime.TryParse(DateEffectiveStr, MyCultureInfoDate, DateTimeStyles.None, out DateEffective)) { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid date ({1})"), LineNumber.ToString(), DateEffectiveStr); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } if (DateEffective > maxRecommendedEffectiveDate) { // raise a warning string resultText = String.Format(Catalog.GetString( "Line {0}: Warning: The date '{1}' is after the latest forwarding period of any active ledger"), LineNumber.ToString(), DateEffectiveStr); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical)); } else if (DateEffective < minRecommendedEffectiveDate) { // raise a warning string resultText = String.Format(Catalog.GetString( "Line {0}: Warning: The date '{1}' is before the current accounting period of any active ledger"), LineNumber.ToString(), DateEffectiveStr); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical)); } if (AImportMode == "Corporate") { if ((preferredPeriodStartDay >= 1) && (DateEffective.Day != preferredPeriodStartDay)) { // raise a warning string resultText = String.Format(Catalog.GetString( "Line {0}: Warning: The date '{1}' should be the first day of an accounting period used by all the active ledgers."), LineNumber.ToString(), DateEffectiveStr); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Noncritical)); } } decimal ExchangeRate = 0.0m; try { string ExchangeRateString = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true).Replace(ThousandsSeparator, "").Replace( DecimalSeparator, ".").Replace("\"", String.Empty); ExchangeRate = Convert.ToDecimal(ExchangeRateString, System.Globalization.CultureInfo.InvariantCulture); if (ExchangeRate == 0) { throw new Exception(); } } catch (Exception) { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid rate of exchange ({1})"), LineNumber.ToString(), ExchangeRate); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } int TimeEffective = 7200; if (AImportMode == "Daily") { // Daily rate imports can have an optional final column which is the time // Otherwise we assume the time is a default of 7200 (02:00am) if ((IsShortFileFormat && (NumCols == 3)) || (!IsShortFileFormat && (NumCols == 5))) { string timeEffectiveStr = StringHelper.GetNextCSV(ref Line, ACSVSeparator, false, true); int t = (int)new Ict.Common.TypeConverter.TShortTimeConverter().ConvertTo(timeEffectiveStr, typeof(int)); if (t < 0) { // it wasn't in the format 02:00 if (!Int32.TryParse(timeEffectiveStr, out t)) { // Not a regular Int32 either t = -1; } } if ((t >= 0) && (t < 86400)) { TimeEffective = t; } else { // raise an error string resultText = String.Format(Catalog.GetString( "Line {0}: invalid effective time ({1})"), LineNumber.ToString(), t); InvalidRows.Add(new Tuple <string, TResultSeverity>(resultText, TResultSeverity.Resv_Critical)); continue; } } } if ((AImportMode == "Corporate") && AExchangeRDT is ACorporateExchangeRateTable) { ACorporateExchangeRateTable ExchangeRateDT = (ACorporateExchangeRateTable)AExchangeRDT; // run this code in the loop twice to get ExchangeRate value and its inverse for (int i = 0; i <= 1; i++) { //this will cause x and y to go from 0 to 1 and 1 to 0 respectively x = i; y = Math.Abs(i - 1); ACorporateExchangeRateRow ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.Rows. Find(new object[] { Currencies[x], Currencies[y], DateEffective }); if (ExchangeRow == null) // remove 0 for Corporate { ExchangeRow = (ACorporateExchangeRateRow)ExchangeRateDT.NewRowTyped(); ExchangeRow.FromCurrencyCode = Currencies[x]; ExchangeRow.ToCurrencyCode = Currencies[y]; ExchangeRow.DateEffectiveFrom = DateEffective; ExchangeRateDT.Rows.Add(ExchangeRow); RowsImported++; } if (i == 0) { ExchangeRow.RateOfExchange = ExchangeRate; } else { ExchangeRow.RateOfExchange = Math.Round(1 / ExchangeRate, 10); } } } else if ((AImportMode == "Daily") && AExchangeRDT is ADailyExchangeRateTable) { ADailyExchangeRateTable ExchangeRateDT = (ADailyExchangeRateTable)AExchangeRDT; // run this code in the loop twice to get ExchangeRate value and its inverse for (int i = 0; i <= 1; i++) { //this will cause x and y to go from 0 to 1 and 1 to 0 respectively x = i; y = Math.Abs(i - 1); ADailyExchangeRateRow ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.Rows. Find(new object[] { Currencies[x], Currencies[y], DateEffective, TimeEffective }); if (ExchangeRow == null) // remove 0 for Corporate { ExchangeRow = (ADailyExchangeRateRow)ExchangeRateDT.NewRowTyped(); ExchangeRow.FromCurrencyCode = Currencies[x]; ExchangeRow.ToCurrencyCode = Currencies[y]; ExchangeRow.DateEffectiveFrom = DateEffective; ExchangeRow.TimeEffectiveFrom = TimeEffective; ExchangeRateDT.Rows.Add(ExchangeRow); RowsImported++; } if (i == 0) { ExchangeRow.RateOfExchange = ExchangeRate; } else { ExchangeRow.RateOfExchange = Math.Round(1 / ExchangeRate, 10); } } } } // if there are rows that could not be imported if ((InvalidRows != null) && (InvalidRows.Count > 0)) { int errorCount = 0; int warningCount = 0; // Go through once just to count the errors and warnings foreach (Tuple <string, TResultSeverity>Row in InvalidRows) { if (Row.Item2 == TResultSeverity.Resv_Noncritical) { warningCount++; } else { errorCount++; } } string resultText = String.Empty; bool messageListIsFull = false; int counter = 0; if (errorCount > 0) { resultText = string.Format(Catalog.GetPluralString("1 row was not imported due to invalid data:", "{0} rows were not imported due to invalid data:", errorCount, true), errorCount) + Environment.NewLine; } if (warningCount > 0) { resultText = string.Format(Catalog.GetPluralString("There was 1 warning associated with the imported rows:", "There were {0} warnings associated with the imported rows:", warningCount, true), warningCount) + Environment.NewLine; } // Now go through again itemising each one foreach (Tuple <string, TResultSeverity>Row in InvalidRows) { counter++; if (counter <= MAX_MESSAGE_COUNT) { resultText += Environment.NewLine + Row.Item1; } else if (!messageListIsFull) { resultText += String.Format(Catalog.GetString( "{0}{0}{1} errors/warnings were reported in total. This message contains the first {2}."), Environment.NewLine, InvalidRows.Count, MAX_MESSAGE_COUNT); messageListIsFull = true; } } // additional message if one or more rows has an invalid number of columns if (InvalidColumnCount && IsShortFileFormat) { resultText += String.Format("{0}{0}" + Catalog.GetString("Each row should contain 2 or 3 columns as follows:") + "{0}" + Catalog.GetString( " 1. Effective Date{0} 2. Exchange Rate{0} 3. Effective time in seconds (Optional for Daily Rate only)"), Environment.NewLine); } else if (InvalidColumnCount && !IsShortFileFormat) { resultText += String.Format("{0}{0}" + Catalog.GetString("Each row should contain 4 or 5 columns as follows:") + "{0}" + Catalog.GetString( " 1. From Currency{0} 2. To Currency{0} 3. Effective Date{0} 4. Exchange Rate{0} 5. Effective time in seconds (Optional for Daily Rate only)"), Environment.NewLine); } TVerificationResult result = new TVerificationResult(AImportMode, resultText, CommonErrorCodes.ERR_INCONGRUOUSSTRINGS, (errorCount > 0) ? TResultSeverity.Resv_Critical : TResultSeverity.Resv_Noncritical); AResultCollection.Add(result); } DataFile.Close(); return RowsImported; } }
/// <summary> /// Imports currency exchange rates, daily and corporate, /// from a one-of-two-styles formatted CSV file /// </summary> /// <param name="AExchangeRDT">Daily or Corporate exchange rate table</param> /// <param name="ADataFilename">The .CSV file to process</param> /// <param name="ACSVSeparator"></param> /// <param name="ANumberFormat"></param> /// <param name="ADateFormat"></param> /// <param name="AImportMode">Daily or Corporate</param> /// <param name="AResultCollection">A validation collection to which errors will be added</param> /// <returns>The number of rows that were actually imported. Rows that duplicate existing rows do not count. /// This is usually because this is an attempt to import again after a failed previous attempt.</returns> private static int ImportCurrencyExRatesFromCSV(TTypedDataTable AExchangeRDT, string ADataFilename, string ACSVSeparator, string ANumberFormat, string ADateFormat, string AImportMode, TVerificationResultCollection AResultCollection) { 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); } }
public static void GetData(string ATablename, TSearchCriteria[] ASearchCriteria, out TTypedDataTable AResultTable, TDBTransaction AReadTransaction) { AResultTable = null; string context = string.Format("GetData {0}", SharedConstants.MODULE_ACCESS_MANAGER); // check access permissions for the current user TModuleAccessManager.CheckUserPermissionsForTable(ATablename, TTablePermissionEnum.eCanRead); // TODO: auto generate if (ATablename == AApSupplierTable.GetTableDBName()) { AResultTable = AApSupplierAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == AApDocumentTable.GetTableDBName()) { AResultTable = AApDocumentAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == ATransactionTypeTable.GetTableDBName()) { AResultTable = ATransactionTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == ACurrencyTable.GetTableDBName()) { AResultTable = ACurrencyAccess.LoadAll(AReadTransaction); } else if (ATablename == ADailyExchangeRateTable.GetTableDBName()) { AResultTable = ADailyExchangeRateAccess.LoadAll(AReadTransaction); } else if (ATablename == ACorporateExchangeRateTable.GetTableDBName()) { AResultTable = ACorporateExchangeRateAccess.LoadAll(AReadTransaction); } else if (ATablename == ACurrencyLanguageTable.GetTableDBName()) { AResultTable = ACurrencyLanguageAccess.LoadAll(AReadTransaction); } else if (ATablename == AFeesPayableTable.GetTableDBName()) { AResultTable = AFeesPayableAccess.LoadAll(AReadTransaction); } else if (ATablename == AFeesReceivableTable.GetTableDBName()) { AResultTable = AFeesReceivableAccess.LoadAll(AReadTransaction); } else if (ATablename == AAnalysisTypeTable.GetTableDBName()) { AResultTable = AAnalysisTypeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == AGiftBatchTable.GetTableDBName()) { AResultTable = AGiftBatchAccess.LoadAll(AReadTransaction); } else if (ATablename == AJournalTable.GetTableDBName()) { AResultTable = AJournalAccess.LoadAll(AReadTransaction); } else if (ATablename == ALedgerTable.GetTableDBName()) { AResultTable = ALedgerAccess.LoadAll(AReadTransaction); } else if (ATablename == MExtractMasterTable.GetTableDBName()) { if (ASearchCriteria == null) { AResultTable = MExtractMasterAccess.LoadAll(AReadTransaction); } else { AResultTable = MExtractMasterAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } } else if (ATablename == MExtractTable.GetTableDBName()) { // it does not make sense to load ALL extract rows for all extract masters so search criteria needs to be set if (ASearchCriteria != null) { AResultTable = MExtractAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } } else if (ATablename == PcAttendeeTable.GetTableDBName()) { AResultTable = PcAttendeeAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PcConferenceCostTable.GetTableDBName()) { AResultTable = PcConferenceCostAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PcEarlyLateTable.GetTableDBName()) { AResultTable = PcEarlyLateAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PcSupplementTable.GetTableDBName()) { AResultTable = PcSupplementAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PcDiscountTable.GetTableDBName()) { AResultTable = PcDiscountAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PCountryTable.GetTableDBName()) { AResultTable = PCountryAccess.LoadAll(AReadTransaction); } else if (ATablename == PFormTable.GetTableDBName()) { string[] columns = TTypedDataTable.GetColumnStringList(PFormTable.TableId); StringCollection fieldList = new StringCollection(); for (int i = 0; i < columns.Length; i++) { // Do not load the template document - we don't display it and it is big! if (columns[i] != PFormTable.GetTemplateDocumentDBName()) { fieldList.Add(columns[i]); } } AResultTable = PFormAccess.LoadAll(fieldList, AReadTransaction); } else if (ATablename == PInternationalPostalTypeTable.GetTableDBName()) { AResultTable = PInternationalPostalTypeAccess.LoadAll(AReadTransaction); } else if (ATablename == PtApplicationTypeTable.GetTableDBName()) { AResultTable = PtApplicationTypeAccess.LoadAll(AReadTransaction); } else if (ATablename == PFormalityTable.GetTableDBName()) { AResultTable = PFormalityAccess.LoadAll(AReadTransaction); } else if (ATablename == PMailingTable.GetTableDBName()) { AResultTable = PMailingAccess.LoadAll(AReadTransaction); } else if (ATablename == PPartnerGiftDestinationTable.GetTableDBName()) { AResultTable = PPartnerGiftDestinationAccess.LoadUsingTemplate(ASearchCriteria, AReadTransaction); } else if (ATablename == PmDocumentTypeTable.GetTableDBName()) { AResultTable = PmDocumentTypeAccess.LoadAll(AReadTransaction); } else if (ATablename == SGroupTable.GetTableDBName()) { TSecurityChecks.CheckUserModulePermissions(SharedConstants.PETRAMODULE_SYSADMIN, context); AResultTable = SGroupAccess.LoadAll(AReadTransaction); } else if (ATablename == SSystemDefaultsTable.GetTableDBName()) { TSecurityChecks.CheckUserModulePermissions(SharedConstants.PETRAMODULE_SYSADMIN, context); AResultTable = SSystemDefaultsAccess.LoadAll(AReadTransaction); } else if (ATablename == SSystemDefaultsGuiTable.GetTableDBName()) { AResultTable = SSystemDefaultsGuiAccess.LoadAll(AReadTransaction); } else { throw new Exception("TCommonDataReader.GetData: unknown table " + ATablename); } // Accept row changes here so that the Client gets 'unmodified' rows AResultTable.AcceptChanges(); }
/// <summary> /// Validates the GL Journal data. /// </summary> /// <param name="AContext">Context that describes where the data validation failed.</param> /// <param name="ARow">The <see cref="DataRow" /> which holds the the data against which the validation is run.</param> /// <param name="AVerificationResultCollection">Will be filled with any <see cref="TVerificationResult" /> items if /// data validation errors occur.</param> /// <param name="AGLSetupDSRef">A GLSetupTDS reference with a populated ATransactionTypeTable. A reference to this DataSet is REQUIRED when importing - optional otherwise</param> /// <param name="ACurrencyTableRef">A reference to the Currency table. A reference to this table is REQUIRED when importing - optional otherwise</param> /// <param name="ACorporateExchangeTableRef">Corporate exchange rate table. A reference to this table is REQUIRED when importing - optional otherwise</param> /// <param name="ABaseCurrency">Ledger base currency. Required when importing</param> /// <param name="AIntlCurrency">Ledger international currency. Required when importing</param> /// <returns>True if the validation found no data validation errors, otherwise false.</returns> public static bool ValidateGLJournalManual(object AContext, AJournalRow ARow, ref TVerificationResultCollection AVerificationResultCollection, GLSetupTDS AGLSetupDSRef = null, ACurrencyTable ACurrencyTableRef = null, ACorporateExchangeRateTable ACorporateExchangeTableRef = null, String ABaseCurrency = null, String AIntlCurrency = null) { DataColumn ValidationColumn; TScreenVerificationResult VerificationResult; string ValidationContext; int VerifResultCollAddedCount = 0; // Don't validate deleted or posted DataRows if ((ARow.RowState == DataRowState.Deleted) || (ARow.JournalStatus == MFinanceConstants.BATCH_POSTED)) { return(true); } bool isImporting = AContext.ToString().Contains("Importing"); // 'Exchange Rate' must be greater than 0 ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnExchangeRateToBaseId]; ValidationContext = ARow.JournalNumber.ToString() + " of Batch Number: " + ARow.BatchNumber.ToString(); if (true) { VerificationResult = (TScreenVerificationResult)TNumericalChecks.IsPositiveDecimal(ARow.ExchangeRateToBase, String.Empty + (isImporting ? String.Empty : " of Journal Number: " + ValidationContext.ToString()), AContext, ValidationColumn); // Handle addition/removal to/from TVerificationResultCollection if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, VerificationResult)) { VerifResultCollAddedCount++; } // Exchange rate must be 1.00 if the currency is the base ledger currency if ((ABaseCurrency != null) && (!ARow.IsExchangeRateToBaseNull()) && (ARow.TransactionCurrency == ABaseCurrency) && (ARow.ExchangeRateToBase != 1.00m)) { if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove(AContext, new TVerificationResult(ValidationContext, Catalog.GetString("A journal in the ledger base currency must have exchange rate of 1.00."), TResultSeverity.Resv_Critical))) { VerifResultCollAddedCount++; } } } // Transaction currency must be valid bool isValidTransactionCurrency = true; if (isImporting && (ACurrencyTableRef != null)) { ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnTransactionCurrencyId]; if (true) { ACurrencyRow foundRow = (ACurrencyRow)ACurrencyTableRef.Rows.Find(ARow.TransactionCurrency); isValidTransactionCurrency = (foundRow != null); if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString("'{0}' is not a valid currency."), ARow.TransactionCurrency), TResultSeverity.Resv_Critical))) { VerifResultCollAddedCount++; } } } if ((ACorporateExchangeTableRef != null) && isValidTransactionCurrency && (ABaseCurrency != null) && (AIntlCurrency != null) && !ARow.IsDateEffectiveNull() && (ABaseCurrency != AIntlCurrency)) { // For ledgers where the base currency and intl currency differ there must be a corporate exchange rate to the international currency ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnTransactionCurrencyId]; ValidationContext = ARow.JournalNumber.ToString() + " of Batch Number: " + ARow.BatchNumber.ToString(); if (true) { DateTime firstOfMonth; if (TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriod(ARow.LedgerNumber, ARow.DateEffective, out firstOfMonth)) { ACorporateExchangeRateRow foundRow = (ACorporateExchangeRateRow)ACorporateExchangeTableRef.Rows.Find( new object[] { ABaseCurrency, AIntlCurrency, firstOfMonth }); if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString( "There is no Corporate Exchange Rate defined for '{0}' to '{1}' for the month starting on '{2}'."), ABaseCurrency, AIntlCurrency, StringHelper.DateToLocalizedString(firstOfMonth)), TResultSeverity.Resv_Noncritical))) { VerifResultCollAddedCount++; } } } } // Sub-system code must exist in the transaction type table if (isImporting && (AGLSetupDSRef.ATransactionType != null)) { ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnSubSystemCodeId]; if (true) { ATransactionTypeRow foundRow = (ATransactionTypeRow)AGLSetupDSRef.ATransactionType.Rows.Find( new object[] { ARow.LedgerNumber, ARow.SubSystemCode, ARow.TransactionTypeCode }); if ((foundRow == null) && AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, String.Format(Catalog.GetString( "The combination of Transaction Type of '{0}' and Sub-system Code of '{1}' is not valid for journals in Ledger {2}."), ARow.TransactionTypeCode, ARow.SubSystemCode, ARow.LedgerNumber), TResultSeverity.Resv_Critical))) { VerifResultCollAddedCount++; } } } // Journal description must not be null if (isImporting) { ValidationColumn = ARow.Table.Columns[AJournalTable.ColumnJournalDescriptionId]; if (true) { if ((ARow.JournalDescription == null) || (ARow.JournalDescription.Length == 0)) { if (AVerificationResultCollection.Auto_Add_Or_AddOrRemove( AContext, new TVerificationResult(ValidationContext, Catalog.GetString("The journal description must not be empty."), TResultSeverity.Resv_Critical))) { VerifResultCollAddedCount++; } } } } return(VerifResultCollAddedCount == 0); }