/// <summary> /// Import Gift Transactions from a file /// </summary> /// <param name="ARequestParams"></param> /// <param name="AImportString"></param> /// <param name="AGiftBatchNumber"></param> /// <param name="ANeedRecipientLedgerNumber"></param> /// <param name="AMessages"></param> /// <returns></returns> public bool ImportGiftTransactions( Hashtable ARequestParams, String AImportString, Int32 AGiftBatchNumber, out GiftBatchTDSAGiftDetailTable ANeedRecipientLedgerNumber, out TVerificationResultCollection AMessages ) { TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Importing Gift Batches"), 100); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Initialising"), 5); AMessages = new TVerificationResultCollection(); FMainDS = new GiftBatchTDS(); StringReader sr = new StringReader(AImportString); ANeedRecipientLedgerNumber = new GiftBatchTDSAGiftDetailTable(); // Parse the supplied parameters FDelimiter = (String)ARequestParams["Delimiter"]; FLedgerNumber = (Int32)ARequestParams["ALedgerNumber"]; FDateFormatString = (String)ARequestParams["DateFormatString"]; String NumberFormat = (String)ARequestParams["NumberFormat"]; FNewLine = (String)ARequestParams["NewLine"]; // Set culture from parameters FCultureInfoNumberFormat = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); FCultureInfoDate = new CultureInfo("en-GB"); FCultureInfoDate.DateTimeFormat.ShortDatePattern = FDateFormatString; bool TaxDeductiblePercentageEnabled = Convert.ToBoolean( TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE")); // Initialise our working variables TDBTransaction Transaction = null; decimal totalBatchAmount = 0; Int32 RowNumber = 0; Int32 InitialTextLength = AImportString.Length; Int32 TextProcessedLength = 0; Int32 PercentDone = 10; Int32 PreviousPercentDone = 0; bool ok = false; string ImportMessage = Catalog.GetString("Initialising"); // Create some validation dictionaries TValidationControlsDict ValidationControlsDictGift = new TValidationControlsDict(); TValidationControlsDict ValidationControlsDictGiftDetail = new TValidationControlsDict(); try { // This needs to be initialised because we will be calling the method TSharedFinanceValidationHelper.GetValidPeriodDatesDelegate = @TAccountingPeriodsWebConnector.GetPeriodDates; TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriodDelegate = @TAccountingPeriodsWebConnector.GetFirstDayOfAccountingPeriod; // Get a new transaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable); // If we did not succeed there is something wrong (a transaction is already dangling somewhere?) if (Transaction == null) { throw new Exception(Catalog.GetString( "Could not create a new import transaction because an existing transaction has not completed.")); } // Load supplementary tables that we are going to need for validation ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, Transaction); ACostCentreTable CostCentreTable = ACostCentreAccess.LoadViaALedger(FLedgerNumber, Transaction); AMotivationGroupTable MotivationGroupTable = AMotivationGroupAccess.LoadViaALedger(FLedgerNumber, Transaction); AMotivationDetailTable MotivationDetailTable = AMotivationDetailAccess.LoadViaALedger(FLedgerNumber, Transaction); AMethodOfGivingTable MethodOfGivingTable = AMethodOfGivingAccess.LoadAll(Transaction); AMethodOfPaymentTable MethodOfPaymentTable = AMethodOfPaymentAccess.LoadAll(Transaction); AGiftBatchTable giftBatchTable = AGiftBatchAccess.LoadViaALedger(FLedgerNumber, Transaction); DataView giftBatchDV = new DataView(giftBatchTable, String.Format("{0}={1}", AGiftBatchTable.GetBatchNumberDBName(), AGiftBatchNumber), "", DataViewRowState.CurrentRows); FMainDS.AGiftBatch.ImportRow(giftBatchDV[0].Row); FMainDS.AcceptChanges(); AGiftBatchRow giftBatch = (AGiftBatchRow)FMainDS.AGiftBatch.Rows.Find(new object[] { FLedgerNumber, AGiftBatchNumber }); if (LedgerTable.Rows.Count == 0) { throw new Exception(String.Format(Catalog.GetString("Ledger {0} doesn't exist."), FLedgerNumber)); } ImportMessage = Catalog.GetString("Parsing first line"); AGiftRow previousGift = null; // Go round a loop reading the file line by line FImportLine = sr.ReadLine(); while (FImportLine != null) { RowNumber++; TextProcessedLength += (FImportLine.Length + FNewLine.Length); PercentDone = 10 + ((TextProcessedLength * 90) / InitialTextLength); // skip empty lines and commented lines if ((FImportLine.Trim().Length > 0) && !FImportLine.StartsWith("/*") && !FImportLine.StartsWith("#")) { // number of elements is incremented by 1 as though the line started with 'T' int numberOfElements = StringHelper.GetCSVList(FImportLine, FDelimiter).Count + 1; // It is a Transaction row if (numberOfElements < 13) // Perhaps this CSV file is a summary, and can't be imported? { AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), Catalog.GetString("Wrong number of gift columns. Expected at least 13 columns. (This may be a summary?)"), TResultSeverity.Resv_Critical)); FImportLine = sr.ReadLine(); if (FImportLine != null) { TextProcessedLength += (FImportLine.Length + FNewLine.Length); } continue; } // Parse the line into a new row ImportMessage = Catalog.GetString("Parsing transaction line"); AGiftRow gift = FMainDS.AGift.NewRowTyped(true); AGiftDetailRow giftDetails; ParseTransactionLine(gift, giftBatch, ref previousGift, numberOfElements, ref totalBatchAmount, ref ImportMessage, RowNumber, AMessages, ValidationControlsDictGift, ValidationControlsDictGiftDetail, CostCentreTable, MotivationGroupTable, MotivationDetailTable, MethodOfGivingTable, MethodOfPaymentTable, ref ANeedRecipientLedgerNumber, out giftDetails); if (TaxDeductiblePercentageEnabled) { // Sets TaxDeductiblePct and uses it to calculate the tax deductibility amounts for a Gift Detail TGift.SetDefaultTaxDeductibilityData(ref giftDetails, gift.DateEntered, Transaction); } } if (AMessages.Count > 100) { // This probably means that it is a big file and the user has made the same mistake many times over break; } // Update progress tracker every few percent if ((PercentDone - PreviousPercentDone) > 3) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), String.Format(Catalog.GetString("Importing row {0}"), RowNumber), (PercentDone > 98) ? 98 : PercentDone); PreviousPercentDone = PercentDone; } // Read the next line FImportLine = sr.ReadLine(); if (FImportLine != null) { TextProcessedLength += (FImportLine.Length + FNewLine.Length); } } // while CSV lines // Finished reading the file - did we have critical errors? if (!TVerificationHelper.IsNullOrOnlyNonCritical(AMessages)) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Batch has critical errors"), 100); // Record error count AMessages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, String.Format(Catalog.GetString("{0} messages reported."), AMessages.Count), TResultSeverity.Resv_Info)); if (FImportLine == null) { // We did reach the end of the file AMessages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString( "Reached the end of file but errors occurred. When these errors are fixed the batch will import successfully."), TResultSeverity.Resv_Info)); } else { // We gave up before the end AMessages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString( "Stopped reading the file after generating more than 100 messages. The file may contian more errors beyond the ones listed here."), TResultSeverity.Resv_Info)); } TLogging.Log("Return from here!"); // we do not want to think about Gift Destination problems if the import has failed for another reason ANeedRecipientLedgerNumber.Clear(); // Do the 'finally' actions and return false return false; } // if the import contains gifts with Motivation Group 'GIFT' and that have a Family recipient with no Gift Destination then the import will fail if (ANeedRecipientLedgerNumber.Rows.Count > 0) { // Do the 'finally' actions and return false return false; } // Everything is ok, so we can do our finish actions //Update batch total for the last batch entered. if (giftBatch != null) { giftBatch.BatchTotal = totalBatchAmount; } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Saving all data into the database"), 100); //Finally save pending changes (the last number is updated !) ImportMessage = Catalog.GetString("Saving gift batch"); AGiftBatchAccess.SubmitChanges(FMainDS.AGiftBatch, Transaction); FMainDS.AGiftBatch.AcceptChanges(); ImportMessage = Catalog.GetString("Saving gifts"); AGiftAccess.SubmitChanges(FMainDS.AGift, Transaction); FMainDS.AGift.AcceptChanges(); ImportMessage = Catalog.GetString("Saving giftdetails"); AGiftDetailAccess.SubmitChanges(FMainDS.AGiftDetail, Transaction); FMainDS.AGiftDetail.AcceptChanges(); ImportMessage = Catalog.GetString("Saving ledger"); ALedgerAccess.SubmitChanges(LedgerTable, Transaction); LedgerTable.AcceptChanges(); // Commit the transaction (we know that we got a new one and can control it) DBAccess.GDBAccessObj.CommitTransaction(); ok = true; } catch (Exception ex) { // Parse the exception text for possible references to database foreign keys // Make the message more friendly in that case string friendlyExceptionText = MakeFriendlyFKExceptions(ex); if (AMessages == null) { AMessages = new TVerificationResultCollection(); } if (RowNumber > 0) { // At least we made a start string msg = ImportMessage; if (friendlyExceptionText.Length > 0) { msg += FNewLine + friendlyExceptionText; } if (ImportMessage.StartsWith(Catalog.GetString("Saving "))) { // Do not display any specific line number because these errors occur outside the parsing loop AMessages.Add(new TVerificationResult(MCommonConstants.StrExceptionWhileSavingTransactions, msg, TResultSeverity.Resv_Critical)); } else { AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileParsingLine, RowNumber), msg, TResultSeverity.Resv_Critical)); } } else { // We got an exception before we even started parsing the rows (getting a transaction?) AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileParsingLine, RowNumber), friendlyExceptionText, TResultSeverity.Resv_Critical)); } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Exception Occurred"), 0); ok = false; } finally { try { sr.Close(); } catch (Exception Exc) { TLogging.Log("An Exception occured while closing the Import File:" + Environment.NewLine + Exc.ToString()); if (AMessages == null) { AMessages = new TVerificationResultCollection(); } AMessages.Add(new TVerificationResult(Catalog.GetString("Import exception"), Catalog.GetString("A problem was encountered while closing the Import File:"), TResultSeverity.Resv_Critical)); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Exception Occurred"), 0); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw; } if (ok) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Gift batch import successful"), 100); } else { DBAccess.GDBAccessObj.RollbackTransaction(); if (AMessages == null) { AMessages = new TVerificationResultCollection(); } AMessages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString("None of the data from the import was saved."), TResultSeverity.Resv_Critical)); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Data could not be saved."), 0); } TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); } // end of 'finally' return ok; }
/// <summary> /// Import Gift Transactions from a file /// </summary> /// <param name="ARequestParams"></param> /// <param name="AImportString"></param> /// <param name="AGiftBatchNumber"></param> /// <param name="ANeedRecipientLedgerNumber"></param> /// <param name="AMessages"></param> /// <returns></returns> public bool ImportGiftTransactions( Hashtable ARequestParams, String AImportString, Int32 AGiftBatchNumber, out GiftBatchTDSAGiftDetailTable ANeedRecipientLedgerNumber, out TVerificationResultCollection AMessages ) { TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Importing Gift Batches"), 100); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Initialising"), 5); GiftBatchTDSAGiftDetailTable NeedRecipientLedgerNumber = new GiftBatchTDSAGiftDetailTable(); TVerificationResultCollection Messages = new TVerificationResultCollection(); // fix for Mono issue with out parameter: https://bugzilla.xamarin.com/show_bug.cgi?id=28196 AMessages = Messages; FMainDS = new GiftBatchTDS(); StringReader sr = new StringReader(AImportString); // Parse the supplied parameters FDelimiter = (String)ARequestParams["Delimiter"]; FLedgerNumber = (Int32)ARequestParams["ALedgerNumber"]; FDateFormatString = (String)ARequestParams["DateFormatString"]; String NumberFormat = (String)ARequestParams["NumberFormat"]; FNewLine = (String)ARequestParams["NewLine"]; // Set culture from parameters FCultureInfoNumberFormat = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); FCultureInfoDate = new CultureInfo("en-GB"); FCultureInfoDate.DateTimeFormat.ShortDatePattern = FDateFormatString; bool TaxDeductiblePercentageEnabled = Convert.ToBoolean( TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE")); // Initialise our working variables decimal totalBatchAmount = 0; Boolean CancelledByUser = false; string ImportMessage = Catalog.GetString("Initialising"); // This needs to be initialised because we will be calling the method TSharedFinanceValidationHelper.GetValidPeriodDatesDelegate = @TAccountingPeriodsWebConnector.GetPeriodDates; TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriodDelegate = @TAccountingPeriodsWebConnector.GetFirstDayOfAccountingPeriod; TDBTransaction Transaction = null; bool SubmissionOK = false; Int32 RowNumber = 0; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { try { // Load supplementary tables that we are going to need for validation ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, Transaction); ACostCentreTable CostCentreTable = ACostCentreAccess.LoadViaALedger(FLedgerNumber, Transaction); AAccountTable AccountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, Transaction); AMotivationGroupTable MotivationGroupTable = AMotivationGroupAccess.LoadViaALedger(FLedgerNumber, Transaction); AMotivationDetailTable MotivationDetailTable = AMotivationDetailAccess.LoadViaALedger(FLedgerNumber, Transaction); AMethodOfGivingTable MethodOfGivingTable = AMethodOfGivingAccess.LoadAll(Transaction); AMethodOfPaymentTable MethodOfPaymentTable = AMethodOfPaymentAccess.LoadAll(Transaction); PMailingTable MailingTable = PMailingAccess.LoadAll(Transaction); PFormTable MailingFormTable = TFormTemplatesWebConnector.GetPartnerForms(); AGiftBatchTable giftBatchTable = AGiftBatchAccess.LoadByPrimaryKey(FLedgerNumber, AGiftBatchNumber, Transaction); FMainDS.AGiftBatch.ImportRow(giftBatchTable[0]); FMainDS.AcceptChanges(); AGiftBatchRow giftBatch = FMainDS.AGiftBatch[0]; if (LedgerTable.Rows.Count == 0) { throw new Exception(String.Format(Catalog.GetString("Ledger {0} doesn't exist."), FLedgerNumber)); } string LedgerBaseCurrency = ((ALedgerRow)LedgerTable.Rows[0]).BaseCurrency; string LedgerIntlCurrency = ((ALedgerRow)LedgerTable.Rows[0]).IntlCurrency; decimal intlRateFromBase = -1.0m; DateTime firstOfMonth; if (TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriod(FLedgerNumber, giftBatch.GlEffectiveDate, out firstOfMonth)) { intlRateFromBase = TExchangeRateTools.GetCorporateExchangeRate(LedgerBaseCurrency, LedgerIntlCurrency, firstOfMonth, giftBatch.GlEffectiveDate); } ImportMessage = Catalog.GetString("Parsing first line"); AGiftRow previousGift = null; // Go round a loop reading the file line by line FImportLine = sr.ReadLine(); Boolean ImportingEsr = false; Int32 PercentDone = 10; Int32 PreviousPercentDone = 0; Int32 InitialTextLength = AImportString.Length; Int32 totalRows = AImportString.Split('\n').Length; TValidationControlsDict EmptyControlsDict = new TValidationControlsDict(); while (FImportLine != null) { RowNumber++; PercentDone = 10 + ((RowNumber * 90) / totalRows); // skip empty lines and commented lines if ((FImportLine.Trim().Length > 0) && !FImportLine.StartsWith("/*") && !FImportLine.StartsWith("#")) { // number of elements is incremented by 1 as though the line started with 'T' int numberOfElements = StringHelper.GetCSVList(FImportLine, FDelimiter).Count + 1; Boolean IsEsrString = false; if (numberOfElements == 2) { IsEsrString = ((FImportLine.Trim().Length == 100) && (FImportLine.Substring(53, 2) == " ")); } if (ImportingEsr && !IsEsrString) // I did previously succeed with ESR, but now not so much - { // I'm probably at the last line of the file. FImportLine = sr.ReadLine(); continue; } if (!IsEsrString) { // It is a Transaction row if (numberOfElements < 13) // Perhaps this CSV file is a summary, and can't be imported? { Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), Catalog.GetString( "Wrong number of gift columns. Expected at least 13 columns. (This may be a summary?)"), TResultSeverity.Resv_Critical)); FImportLine = sr.ReadLine(); continue; } } // Parse the line into a new row ImportMessage = Catalog.GetString("Parsing transaction line"); Int32 preParseMessageCount = Messages.Count; AGiftRow gift = FMainDS.AGift.NewRowTyped(true); AGiftDetailRow giftDetails = FMainDS.AGiftDetail.NewRowTyped(true); if (IsEsrString) { ImportingEsr = ParseEsrTransactionLine( FImportLine, giftBatch, gift, giftDetails, intlRateFromBase, MotivationDetailTable, NeedRecipientLedgerNumber, Messages ); } else { ParseTransactionLine(gift, giftBatch, ref previousGift, numberOfElements, ref totalBatchAmount, ref ImportMessage, RowNumber, intlRateFromBase, Messages, MotivationDetailTable, NeedRecipientLedgerNumber, giftDetails); } if (Messages.Count == preParseMessageCount) // No parsing errors so we can validate { // (parsing errors will have assumed, probably invalid, values) ImportMessage = Catalog.GetString("Validating the gift data"); int messageCountBeforeValidate = preParseMessageCount; TPartnerClass RecipientClass; string RecipientDescription; TPartnerServerLookups.GetPartnerShortName(giftDetails.RecipientKey, out RecipientDescription, out RecipientClass); // Do our standard validation on this gift AGiftValidation.Validate(this, gift, ref Messages, EmptyControlsDict); TSharedFinanceValidation_Gift.ValidateGiftManual(this, gift, giftBatch.BatchYear, giftBatch.BatchPeriod, null, ref Messages, EmptyControlsDict, MethodOfGivingTable, MethodOfPaymentTable, MailingFormTable); ImportMessage = Catalog.GetString("Validating the gift details data"); AGiftDetailValidation.Validate(this, giftDetails, ref Messages, EmptyControlsDict); TSharedFinanceValidation_Gift.ValidateGiftDetailManual(this, (GiftBatchTDSAGiftDetailRow)giftDetails, ref Messages, EmptyControlsDict, RecipientClass, null, CostCentreTable, AccountTable, MotivationGroupTable, MotivationDetailTable, MailingTable, giftDetails.RecipientKey); // Fix up the messages for (int i = messageCountBeforeValidate; i < Messages.Count; i++) { ((TVerificationResult)Messages[i]).OverrideResultContext(String.Format(MCommonConstants. StrValidationErrorInLine, RowNumber)); if (Messages[i] is TScreenVerificationResult) { TVerificationResult downgrade = new TVerificationResult((TScreenVerificationResult)Messages[i]); Messages.RemoveAt(i); Messages.Insert(i, downgrade); } } } if (TaxDeductiblePercentageEnabled) { // Sets TaxDeductiblePct and uses it to calculate the tax deductibility amounts for a Gift Detail TGift.SetDefaultTaxDeductibilityData(ref giftDetails, gift.DateEntered, Transaction); } } if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { CancelledByUser = true; break; } if (Messages.HasCriticalErrors && (Messages.Count > 100)) { // This probably means that it is a big file and the user has made the same mistake many times over break; } // Update progress tracker every few percent if ((PercentDone - PreviousPercentDone) > 3) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), String.Format(Catalog.GetString("Importing row {0}"), RowNumber), (PercentDone > 98) ? 98 : PercentDone); PreviousPercentDone = PercentDone; } // Read the next line FImportLine = sr.ReadLine(); } // while CSV lines if (CancelledByUser) { Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, String.Format(Catalog.GetString("{0} messages reported."), Messages.Count), TResultSeverity.Resv_Info)); Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, "The import was cancelled by the user.", TResultSeverity.Resv_Info)); return; } // Finished reading the file - did we have critical errors? if (!TVerificationHelper.IsNullOrOnlyNonCritical(Messages)) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Batch has critical errors"), 100); // Record error count Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, String.Format(Catalog.GetString("{0} messages reported."), Messages.Count), TResultSeverity.Resv_Info)); if (FImportLine == null) { // We did reach the end of the file Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString( "Reached the end of file but errors occurred. When these errors are fixed the batch will import successfully."), TResultSeverity.Resv_Info)); } else { // We gave up before the end Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString( "Stopped reading the file after generating more than 100 messages. The file may contian more errors beyond the ones listed here."), TResultSeverity.Resv_Info)); } // we do not want to think about Gift Destination problems if the import has failed for another reason NeedRecipientLedgerNumber.Clear(); // Do the 'finally' actions and return false return; } // if the import contains gifts with Motivation Group 'GIFT' and that have a Family recipient with no Gift Destination then the import will fail if (NeedRecipientLedgerNumber.Rows.Count > 0) { // Do the 'finally' actions and return false return; } // Everything is ok, so we can do our finish actions //Update batch total for the last batch entered. if ((giftBatch != null) && !ImportingEsr) { giftBatch.BatchTotal = totalBatchAmount; } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Saving all data into the database"), 100); //Finally save pending changes (the last number is updated !) ImportMessage = Catalog.GetString("Saving gift batch"); AGiftBatchAccess.SubmitChanges(FMainDS.AGiftBatch, Transaction); FMainDS.AGiftBatch.AcceptChanges(); ImportMessage = Catalog.GetString("Saving gifts"); AGiftAccess.SubmitChanges(FMainDS.AGift, Transaction); FMainDS.AGift.AcceptChanges(); ImportMessage = Catalog.GetString("Saving giftdetails"); AGiftDetailAccess.SubmitChanges(FMainDS.AGiftDetail, Transaction); FMainDS.AGiftDetail.AcceptChanges(); ImportMessage = Catalog.GetString("Saving ledger"); ALedgerAccess.SubmitChanges(LedgerTable, Transaction); LedgerTable.AcceptChanges(); // Commit the transaction (we know that we got a new one and can control it) SubmissionOK = true; } catch (Exception ex) { // Parse the exception text for possible references to database foreign keys // Make the message more friendly in that case string friendlyExceptionText = MakeFriendlyFKExceptions(ex); if (RowNumber > 0) { // At least we made a start string msg = ImportMessage; if (friendlyExceptionText.Length > 0) { msg += FNewLine + friendlyExceptionText; } if (ImportMessage.StartsWith(Catalog.GetString("Saving "))) { // Do not display any specific line number because these errors occur outside the parsing loop Messages.Add(new TVerificationResult(MCommonConstants.StrExceptionWhileSavingTransactions, msg, TResultSeverity.Resv_Critical)); } else { Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileParsingLine, RowNumber), msg, TResultSeverity.Resv_Critical)); } } else { // We got an exception before we even started parsing the rows (getting a transaction?) Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileParsingLine, RowNumber), friendlyExceptionText, TResultSeverity.Resv_Critical)); } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Exception Occurred"), 0); SubmissionOK = false; } finally { sr.Close(); if (SubmissionOK) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Gift batch import successful"), 100); } else { Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString("None of the data from the import was saved."), TResultSeverity.Resv_Critical)); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Data could not be saved."), 0); } TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); } // end of 'finally' }); // Set our 'out' parameters ANeedRecipientLedgerNumber = NeedRecipientLedgerNumber; AMessages = Messages; return SubmissionOK; }
/// <summary> /// Import Gift batch data /// The data file contents from the client is sent as a string, imported in the database /// and committed immediately /// </summary> /// <param name="ARequestParams">Hashtable containing the given params </param> /// <param name="AImportString">Big parts of the export file as a simple String</param> /// <param name="ANeedRecipientLedgerNumber">Gifts in this table are responsible for failing the /// import becuase their Family recipients do not have an active Gift Destination</param> /// <param name="AMessages">Additional messages to display in a messagebox</param> /// <returns>false if error</returns> public bool ImportGiftBatches( Hashtable ARequestParams, String AImportString, out GiftBatchTDSAGiftDetailTable ANeedRecipientLedgerNumber, out TVerificationResultCollection AMessages ) { TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Importing Gift Batches"), 100); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Initialising"), 0); AMessages = new TVerificationResultCollection(); FMainDS = new GiftBatchTDS(); StringReader sr = new StringReader(AImportString); ANeedRecipientLedgerNumber = new GiftBatchTDSAGiftDetailTable(); // Parse the supplied parameters FDelimiter = (String)ARequestParams["Delimiter"]; FLedgerNumber = (Int32)ARequestParams["ALedgerNumber"]; FDateFormatString = (String)ARequestParams["DateFormatString"]; String NumberFormat = (String)ARequestParams["NumberFormat"]; FNewLine = (String)ARequestParams["NewLine"]; // Set culture from parameters FCultureInfoNumberFormat = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); FCultureInfoDate = new CultureInfo("en-GB"); FCultureInfoDate.DateTimeFormat.ShortDatePattern = FDateFormatString; bool TaxDeductiblePercentageEnabled = Convert.ToBoolean( TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE")); // Initialise our working variables TDBTransaction Transaction = null; AGiftBatchRow giftBatch = null; decimal totalBatchAmount = 0; Int32 RowNumber = 0; Int32 InitialTextLength = AImportString.Length; Int32 TextProcessedLength = 0; Int32 PercentDone = 10; Int32 PreviousPercentDone = 0; bool ok = false; string ImportMessage = Catalog.GetString("Initialising"); // Create some validation dictionaries TValidationControlsDict ValidationControlsDictBatch = new TValidationControlsDict(); TValidationControlsDict ValidationControlsDictGift = new TValidationControlsDict(); TValidationControlsDict ValidationControlsDictGiftDetail = new TValidationControlsDict(); try { // This needs to be initialised because we will be calling the method TSharedFinanceValidationHelper.GetValidPeriodDatesDelegate = @TAccountingPeriodsWebConnector.GetPeriodDates; TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriodDelegate = @TAccountingPeriodsWebConnector.GetFirstDayOfAccountingPeriod; // Get a new transaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable); // If we did not succeed there is something wrong (a transaction is already dangling somewhere?) if (Transaction == null) { throw new Exception(Catalog.GetString( "Could not create a new import transaction because an existing transaction has not completed.")); } // Load supplementary tables that we are going to need for validation ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, Transaction); AAccountTable AccountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, Transaction); ACostCentreTable CostCentreTable = ACostCentreAccess.LoadViaALedger(FLedgerNumber, Transaction); AMotivationGroupTable MotivationGroupTable = AMotivationGroupAccess.LoadViaALedger(FLedgerNumber, Transaction); AMotivationDetailTable MotivationDetailTable = AMotivationDetailAccess.LoadViaALedger(FLedgerNumber, Transaction); AAccountPropertyTable AccountPropertyTable = AAccountPropertyAccess.LoadViaALedger(FLedgerNumber, Transaction); AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, Transaction); AMethodOfGivingTable MethodOfGivingTable = AMethodOfGivingAccess.LoadAll(Transaction); AMethodOfPaymentTable MethodOfPaymentTable = AMethodOfPaymentAccess.LoadAll(Transaction); ACurrencyTable CurrencyTable = ACurrencyAccess.LoadAll(Transaction); if (LedgerTable.Rows.Count == 0) { throw new Exception(String.Format(Catalog.GetString("Ledger {0} doesn't exist."), FLedgerNumber)); } FLedgerBaseCurrency = ((ALedgerRow)LedgerTable.Rows[0]).BaseCurrency; FLedgerIntlCurrency = ((ALedgerRow)LedgerTable.Rows[0]).IntlCurrency; ACorporateExchangeRateTable CorporateExchangeToLedgerTable = ACorporateExchangeRateAccess.LoadViaACurrencyFromCurrencyCode( FLedgerBaseCurrency, Transaction); ADailyExchangeRateTable DailyExchangeToLedgerTable = ADailyExchangeRateAccess.LoadViaACurrencyToCurrencyCode(FLedgerBaseCurrency, Transaction); ADailyExchangeRateTable DailyExchangeToIntlTable = ADailyExchangeRateAccess.LoadViaACurrencyToCurrencyCode(FLedgerIntlCurrency, Transaction); ImportMessage = Catalog.GetString("Parsing first line"); AGiftRow previousGift = null; // Go round a loop reading the file line by line FImportLine = sr.ReadLine(); while (FImportLine != null) { RowNumber++; TextProcessedLength += (FImportLine.Length + FNewLine.Length); PercentDone = 10 + ((TextProcessedLength * 90) / InitialTextLength); // skip empty lines and commented lines if ((FImportLine.Trim().Length > 0) && !FImportLine.StartsWith("/*") && !FImportLine.StartsWith("#")) { int numberOfElements = StringHelper.GetCSVList(FImportLine, FDelimiter).Count; // Read the row analysisType - there is no 'validation' on this so we can make the call with null parameters string RowType = ImportString(Catalog.GetString("row type"), null, null); if (RowType == "B") { ImportMessage = Catalog.GetString("Parsing a batch row"); // It is a Batch row if (numberOfElements < 8) { AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), Catalog.GetString( "Wrong number of batch columns. The correct number is either 8 columns (in which case the gift type is assumed to be 'Gift') or 9 columns, which allows for alternative gift types."), TResultSeverity.Resv_Critical)); FImportLine = sr.ReadLine(); if (FImportLine != null) { TextProcessedLength += (FImportLine.Length + FNewLine.Length); } continue; } //Check if this is the start of a new batch (i.e. not the first batch) if ((previousGift != null) && (giftBatch != null)) { //New batch so set total amount of Batch for previous batch giftBatch.BatchTotal = totalBatchAmount; if (TVerificationHelper.IsNullOrOnlyNonCritical(AMessages)) { ImportMessage = Catalog.GetString("Saving batch"); AGiftBatchAccess.SubmitChanges(FMainDS.AGiftBatch, Transaction); FMainDS.AGiftBatch.AcceptChanges(); ImportMessage = Catalog.GetString("Saving gift"); AGiftAccess.SubmitChanges(FMainDS.AGift, Transaction); FMainDS.AGift.AcceptChanges(); ImportMessage = Catalog.GetString("Saving giftdetails"); AGiftDetailAccess.SubmitChanges(FMainDS.AGiftDetail, Transaction); FMainDS.AGiftDetail.AcceptChanges(); } previousGift = null; } ImportMessage = Catalog.GetString("Starting new batch"); totalBatchAmount = 0; // Parse the complete line and validate it ParseBatchLine(ref giftBatch, ref Transaction, ref LedgerTable, ref ImportMessage, RowNumber, AMessages, ValidationControlsDictBatch, AccountTable, AccountPropertyTable, AccountingPeriodTable, CostCentreTable, CorporateExchangeToLedgerTable, CurrencyTable); if (TVerificationHelper.IsNullOrOnlyNonCritical(AMessages)) { // This row passes validation so we can do final actions if the batch is not in the ledger currency if (giftBatch.CurrencyCode != FLedgerBaseCurrency) { ImportMessage = Catalog.GetString("Updating foreign exchange data"); // Validation will have ensured that we have a corporate rate for the effective date // We need to know what that rate is... DateTime firstOfMonth = new DateTime(giftBatch.GlEffectiveDate.Year, giftBatch.GlEffectiveDate.Month, 1); ACorporateExchangeRateRow corporateRateRow = (ACorporateExchangeRateRow)CorporateExchangeToLedgerTable.Rows.Find( new object[] { giftBatch.CurrencyCode, FLedgerBaseCurrency, firstOfMonth }); decimal corporateRate = corporateRateRow.RateOfExchange; if (Math.Abs((giftBatch.ExchangeRateToBase - corporateRate) / corporateRate) > 0.20m) { AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationWarningInLine, RowNumber), String.Format(Catalog.GetString( "The exchange rate of {0} differs from the Corporate Rate of {1} for the month commencing {2} by more than 20 percent."), giftBatch.ExchangeRateToBase, corporateRate, StringHelper.DateToLocalizedString(firstOfMonth)), TResultSeverity.Resv_Noncritical)); } // we need to create a daily exchange rate pair for the transaction date // start with To Ledger currency if (UpdateDailyExchangeRateTable(DailyExchangeToLedgerTable, giftBatch.CurrencyCode, FLedgerBaseCurrency, giftBatch.ExchangeRateToBase, giftBatch.GlEffectiveDate)) { ADailyExchangeRateAccess.SubmitChanges(DailyExchangeToLedgerTable, Transaction); DailyExchangeToLedgerTable.AcceptChanges(); AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportInformationForLine, RowNumber), String.Format(Catalog.GetString("Added exchange rate of {0} to Daily Exchange Rate table for {1}"), giftBatch.ExchangeRateToBase, StringHelper.DateToLocalizedString(giftBatch.GlEffectiveDate)), TResultSeverity.Resv_Info)); } // Now the inverse for From Ledger currency ADailyExchangeRateTable DailyExchangeFromTable = ADailyExchangeRateAccess.LoadViaACurrencyFromCurrencyCode(giftBatch.CurrencyCode, Transaction); decimal inverseRate = Math.Round(1 / giftBatch.ExchangeRateToBase, 10); if (UpdateDailyExchangeRateTable(DailyExchangeFromTable, FLedgerBaseCurrency, giftBatch.CurrencyCode, inverseRate, giftBatch.GlEffectiveDate)) { ADailyExchangeRateAccess.SubmitChanges(DailyExchangeFromTable, Transaction); } } } } else if (RowType == "T") { ImportMessage = Catalog.GetString("Parsing a transaction row"); // It is a Transaction row if (numberOfElements < 13) // Perhaps this CSV file is a summary, and can't be imported? { AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), Catalog.GetString("Wrong number of gift columns. Expected at least 13 columns. (This may be a summary?)"), TResultSeverity.Resv_Critical)); FImportLine = sr.ReadLine(); if (FImportLine != null) { TextProcessedLength += (FImportLine.Length + FNewLine.Length); } continue; } if (giftBatch == null) { AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), Catalog.GetString( "Expected a GiftBatch line, but found a Gift Transaction. Will create a dummy working batch for the current period."), TResultSeverity.Resv_Critical)); // in order to carry on we will make a dummy batch and force the date to fit giftBatch = TGiftBatchFunctions.CreateANewGiftBatchRow(ref FMainDS, ref Transaction, ref LedgerTable, FLedgerNumber, DateTime.Today); } // Parse the line into a new row AGiftRow gift = FMainDS.AGift.NewRowTyped(true); AGiftDetailRow giftDetails; ParseTransactionLine(gift, giftBatch, ref previousGift, numberOfElements, ref totalBatchAmount, ref ImportMessage, RowNumber, AMessages, ValidationControlsDictGift, ValidationControlsDictGiftDetail, CostCentreTable, MotivationGroupTable, MotivationDetailTable, MethodOfGivingTable, MethodOfPaymentTable, ref ANeedRecipientLedgerNumber, out giftDetails); if (TaxDeductiblePercentageEnabled) { // Sets TaxDeductiblePct and uses it to calculate the tax deductibility amounts for a Gift Detail TGift.SetDefaultTaxDeductibilityData(ref giftDetails, gift.DateEntered, Transaction); } if (TVerificationHelper.IsNullOrOnlyNonCritical(AMessages)) { if ((FLedgerBaseCurrency != FLedgerIntlCurrency) && (giftDetails.GiftAmountIntl != 0)) { ImportMessage = Catalog.GetString("Updating international exchange rate data"); // We should add a Daily Exchange Rate row pair // start with To Ledger currency decimal fromIntlToBase = GLRoutines.Divide(giftDetails.GiftAmount, giftDetails.GiftAmountIntl); if (UpdateDailyExchangeRateTable(DailyExchangeToLedgerTable, FLedgerIntlCurrency, FLedgerBaseCurrency, fromIntlToBase, giftBatch.GlEffectiveDate)) { ADailyExchangeRateAccess.SubmitChanges(DailyExchangeToLedgerTable, Transaction); DailyExchangeToLedgerTable.AcceptChanges(); AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportInformationForLine, RowNumber), String.Format(Catalog.GetString( "Added exchange rate of {0} to Daily Exchange Rate table for ledger currency / international currency on {1}"), fromIntlToBase, StringHelper.DateToLocalizedString(giftBatch.GlEffectiveDate)), TResultSeverity.Resv_Info)); } // Now the inverse for From Ledger currency decimal inverseRate = GLRoutines.Divide(giftDetails.GiftAmountIntl, giftDetails.GiftAmount); if (UpdateDailyExchangeRateTable(DailyExchangeToIntlTable, FLedgerBaseCurrency, FLedgerIntlCurrency, inverseRate, giftBatch.GlEffectiveDate)) { ADailyExchangeRateAccess.SubmitChanges(DailyExchangeToIntlTable, Transaction); DailyExchangeToIntlTable.AcceptChanges(); } } } } // If known row analysisType else { AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), Catalog.GetString("Invalid Row Type. Perhaps using wrong CSV separator?"), TResultSeverity.Resv_Critical)); } } // if the CSV line qualifies if (AMessages.Count > 100) { // This probably means that it is a big file and the user has made the same mistake many times over break; } // Update progress tracker every few percent if ((PercentDone - PreviousPercentDone) > 3) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), String.Format(Catalog.GetString("Importing row {0}"), RowNumber), (PercentDone > 98) ? 98 : PercentDone); PreviousPercentDone = PercentDone; } // Read the next line FImportLine = sr.ReadLine(); if (FImportLine != null) { TextProcessedLength += (FImportLine.Length + FNewLine.Length); } } // while CSV lines // Finished reading the file - did we have critical errors? if (!TVerificationHelper.IsNullOrOnlyNonCritical(AMessages)) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Batch has critical errors"), 0); // Record error count AMessages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, String.Format(Catalog.GetString("{0} messages reported."), AMessages.Count), TResultSeverity.Resv_Info)); if (FImportLine == null) { // We did reach the end of the file AMessages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString( "Reached the end of file but errors occurred. When these errors are fixed the batch will import successfully."), TResultSeverity.Resv_Info)); } else { // We gave up before the end AMessages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString( "Stopped reading the file after generating more than 100 messages. The file may contian more errors beyond the ones listed here."), TResultSeverity.Resv_Info)); } TLogging.Log("Return from here!"); // we do not want to think about Gift Destination problems if the import has failed for another reason ANeedRecipientLedgerNumber.Clear(); // Do the 'finally' actions and return false return false; } // if the import contains gifts with Motivation Group 'GIFT' and that have a Family recipient with no Gift Destination then the import will fail if (ANeedRecipientLedgerNumber.Rows.Count > 0) { return false; } // Everything is ok, so we can do our finish actions //Update batch total for the last batch entered. if (giftBatch != null) { giftBatch.BatchTotal = totalBatchAmount; } ImportMessage = Catalog.GetString("Saving all data into the database"); //Finally save pending changes (the last number is updated !) ImportMessage = Catalog.GetString("Saving final batch"); AGiftBatchAccess.SubmitChanges(FMainDS.AGiftBatch, Transaction); FMainDS.AGiftBatch.AcceptChanges(); ImportMessage = Catalog.GetString("Saving final gift"); AGiftAccess.SubmitChanges(FMainDS.AGift, Transaction); FMainDS.AGift.AcceptChanges(); ImportMessage = Catalog.GetString("Saving final giftdetails"); AGiftDetailAccess.SubmitChanges(FMainDS.AGiftDetail, Transaction); FMainDS.AGiftDetail.AcceptChanges(); ImportMessage = Catalog.GetString("Saving ledger changes"); ALedgerAccess.SubmitChanges(LedgerTable, Transaction); FMainDS.ALedger.AcceptChanges(); // Commit the transaction (we know that we got a new one and can control it) DBAccess.GDBAccessObj.CommitTransaction(); ok = true; } catch (Exception ex) { // Parse the exception text for possible references to database foreign keys // Make the message more friendly in that case string friendlyExceptionText = MakeFriendlyFKExceptions(ex); if (AMessages == null) { AMessages = new TVerificationResultCollection(); } if (RowNumber > 0) { // At least we made a start string msg = ImportMessage; if (friendlyExceptionText.Length > 0) { msg += FNewLine + friendlyExceptionText; } if (ImportMessage.StartsWith(Catalog.GetString("Saving "))) { // Do not display any specific line number because these errors occur outside the parsing loop AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileSavingBatch, giftBatch.BatchDescription), msg, TResultSeverity.Resv_Critical)); } else { AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileParsingLine, RowNumber), msg, TResultSeverity.Resv_Critical)); } } else { // We got an exception before we even started parsing the rows (getting a transaction?) AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileParsingLine, RowNumber), friendlyExceptionText, TResultSeverity.Resv_Critical)); } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Exception Occurred"), 0); ok = false; } finally { try { sr.Close(); } catch (Exception Exc) { TLogging.Log("An Exception occured while closing the Import File:" + Environment.NewLine + Exc.ToString()); if (AMessages == null) { AMessages = new TVerificationResultCollection(); } AMessages.Add(new TVerificationResult(Catalog.GetString("Import exception"), Catalog.GetString("A problem was encountered while closing the Import File:"), TResultSeverity.Resv_Critical)); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Exception Occurred"), 0); TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); throw; } if (ok) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Gift batch import successful"), 100); } else { DBAccess.GDBAccessObj.RollbackTransaction(); if (AMessages == null) { AMessages = new TVerificationResultCollection(); } AMessages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString("None of the data from the import was saved."), TResultSeverity.Resv_Critical)); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Data could not be saved."), 0); } TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); } // end of 'finally' return ok; }
/// <summary> /// Import Gift batch data /// The data file contents from the client is sent as a string, imported in the database /// and committed immediately /// </summary> /// <param name="ARequestParams">Hashtable containing the given params </param> /// <param name="AImportString">Big parts of the export file as a simple String</param> /// <param name="ANeedRecipientLedgerNumber">Gifts in this table are responsible for failing the /// import becuase their Family recipients do not have an active Gift Destination</param> /// <param name="AMessages">Additional messages to display in a messagebox</param> /// <returns>false if error</returns> public bool ImportGiftBatches( Hashtable ARequestParams, String AImportString, out GiftBatchTDSAGiftDetailTable ANeedRecipientLedgerNumber, out TVerificationResultCollection AMessages ) { TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(), Catalog.GetString("Importing Gift Batches"), 100); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Initialising"), 0); TVerificationResultCollection Messages = new TVerificationResultCollection(); // fix for Mono issue with out parameter: https://bugzilla.xamarin.com/show_bug.cgi?id=28196 AMessages = Messages; GiftBatchTDSAGiftDetailTable NeedRecipientLedgerNumber = new GiftBatchTDSAGiftDetailTable(); FMainDS = new GiftBatchTDS(); StringReader sr = new StringReader(AImportString); // Parse the supplied parameters FDelimiter = (String)ARequestParams["Delimiter"]; FLedgerNumber = (Int32)ARequestParams["ALedgerNumber"]; FDateFormatString = (String)ARequestParams["DateFormatString"]; String NumberFormat = (String)ARequestParams["NumberFormat"]; FNewLine = (String)ARequestParams["NewLine"]; // Set culture from parameters FCultureInfoNumberFormat = new CultureInfo(NumberFormat.Equals("American") ? "en-US" : "de-DE"); FCultureInfoDate = new CultureInfo("en-GB"); FCultureInfoDate.DateTimeFormat.ShortDatePattern = FDateFormatString; bool TaxDeductiblePercentageEnabled = Convert.ToBoolean( TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE")); // Initialise our working variables AGiftBatchRow giftBatch = null; decimal totalBatchAmount = 0; Int32 RowNumber = 0; Int32 InitialTextLength = AImportString.Length; Int32 TextProcessedLength = 0; Int32 PercentDone = 10; Int32 PreviousPercentDone = 0; Boolean CancelledByUser = false; decimal intlRateFromBase = -1.0m; string ImportMessage = Catalog.GetString("Initialising"); // This needs to be initialised because we will be calling the method TSharedFinanceValidationHelper.GetValidPeriodDatesDelegate = @TAccountingPeriodsWebConnector.GetPeriodDates; TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriodDelegate = @TAccountingPeriodsWebConnector.GetFirstDayOfAccountingPeriod; TDBTransaction Transaction = null; bool SubmissionOK = false; DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { try { // Load supplementary tables that we are going to need for validation ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(FLedgerNumber, Transaction); AAccountTable AccountTable = AAccountAccess.LoadViaALedger(FLedgerNumber, Transaction); ACostCentreTable CostCentreTable = ACostCentreAccess.LoadViaALedger(FLedgerNumber, Transaction); AMotivationGroupTable MotivationGroupTable = AMotivationGroupAccess.LoadViaALedger(FLedgerNumber, Transaction); AMotivationDetailTable MotivationDetailTable = AMotivationDetailAccess.LoadViaALedger(FLedgerNumber, Transaction); AAccountPropertyTable AccountPropertyTable = AAccountPropertyAccess.LoadViaALedger(FLedgerNumber, Transaction); AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, Transaction); AMethodOfGivingTable MethodOfGivingTable = AMethodOfGivingAccess.LoadAll(Transaction); AMethodOfPaymentTable MethodOfPaymentTable = AMethodOfPaymentAccess.LoadAll(Transaction); ACurrencyTable CurrencyTable = ACurrencyAccess.LoadAll(Transaction); PMailingTable MailingTable = PMailingAccess.LoadAll(Transaction); if (LedgerTable.Rows.Count == 0) { throw new Exception(String.Format(Catalog.GetString("Ledger {0} doesn't exist."), FLedgerNumber)); } string LedgerBaseCurrency = ((ALedgerRow)LedgerTable.Rows[0]).BaseCurrency; string LedgerIntlCurrency = ((ALedgerRow)LedgerTable.Rows[0]).IntlCurrency; ACorporateExchangeRateTable CorporateExchangeRateTable = ACorporateExchangeRateAccess.LoadViaACurrencyToCurrencyCode( LedgerIntlCurrency, Transaction); ADailyExchangeRateTable DailyExchangeRateTable = ADailyExchangeRateAccess.LoadAll(Transaction); ImportMessage = Catalog.GetString("Parsing first line"); AGiftRow previousGift = null; // Go round a loop reading the file line by line FImportLine = sr.ReadLine(); TValidationControlsDict EmptyControlsDict = new TValidationControlsDict(); while (FImportLine != null) { RowNumber++; TextProcessedLength += (FImportLine.Length + FNewLine.Length); PercentDone = 10 + ((TextProcessedLength * 90) / InitialTextLength); // skip empty lines and commented lines if ((FImportLine.Trim().Length > 0) && !FImportLine.StartsWith("/*") && !FImportLine.StartsWith("#")) { int numberOfElements = StringHelper.GetCSVList(FImportLine, FDelimiter).Count; // Read the row analysisType - there is no 'validation' on this so we can make the call with null parameters string RowType = TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("row type"), null, RowNumber, Messages, null); if (RowType == "B") { ImportMessage = Catalog.GetString("Parsing a batch row"); // It is a Batch row if (numberOfElements < 8) { Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), Catalog.GetString( "Wrong number of batch columns. The correct number is either 8 columns (in which case the gift type is assumed to be 'Gift') or 9 columns, which allows for alternative gift types."), TResultSeverity.Resv_Critical)); FImportLine = sr.ReadLine(); if (FImportLine != null) { TextProcessedLength += (FImportLine.Length + FNewLine.Length); } continue; } //Check if this is the start of a new batch (i.e. not the first batch) if ((previousGift != null) && (giftBatch != null)) { //New batch so set total amount of Batch for previous batch giftBatch.BatchTotal = totalBatchAmount; intlRateFromBase = -1.0m; if (TVerificationHelper.IsNullOrOnlyNonCritical(Messages)) { ImportMessage = Catalog.GetString("Saving batch"); AGiftBatchAccess.SubmitChanges(FMainDS.AGiftBatch, Transaction); FMainDS.AGiftBatch.AcceptChanges(); ImportMessage = Catalog.GetString("Saving gift"); AGiftAccess.SubmitChanges(FMainDS.AGift, Transaction); FMainDS.AGift.AcceptChanges(); ImportMessage = Catalog.GetString("Saving giftdetails"); AGiftDetailAccess.SubmitChanges(FMainDS.AGiftDetail, Transaction); FMainDS.AGiftDetail.AcceptChanges(); } previousGift = null; } ImportMessage = Catalog.GetString("Starting new batch"); totalBatchAmount = 0; // Parse the complete line and validate it ParseBatchLine(ref giftBatch, ref Transaction, ref LedgerTable, ref ImportMessage, RowNumber, LedgerBaseCurrency, LedgerIntlCurrency, Messages, EmptyControlsDict, AccountTable, AccountPropertyTable, AccountingPeriodTable, CostCentreTable, CorporateExchangeRateTable, CurrencyTable); if (TVerificationHelper.IsNullOrOnlyNonCritical(Messages)) { // This row passes validation so we can do final actions // Validation will have ensured that we have a corporate rate for intl currency // at least for the first day of the accounting period. // (There may possibly be others between then and the effective date) // We need to know what that rate is... DateTime firstOfMonth; if (TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriod(FLedgerNumber, giftBatch.GlEffectiveDate, out firstOfMonth)) { intlRateFromBase = TExchangeRateTools.GetCorporateExchangeRate(LedgerBaseCurrency, LedgerIntlCurrency, firstOfMonth, giftBatch.GlEffectiveDate); if (intlRateFromBase <= 0.0m) { // This should never happen (see above) Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), String.Format( "There is no Corporate Exchange Rate for {0} to {1} applicable to the period {2} to {3}. Please set up an appropriate rate and then import the data again.", LedgerBaseCurrency, LedgerIntlCurrency, StringHelper.DateToLocalizedString(firstOfMonth), StringHelper.DateToLocalizedString(giftBatch.GlEffectiveDate)), TResultSeverity.Resv_Critical)); } } } if (TVerificationHelper.IsNullOrOnlyNonCritical(Messages)) { // This row passes validation so we can do final actions // If the batch is not in the ledger currency we populate the Daily Exchange Rate table if (giftBatch.CurrencyCode != LedgerBaseCurrency) { ImportMessage = Catalog.GetString("Updating foreign exchange data"); // we need to create a daily exchange rate pair for the transaction date // start with To Ledger currency if (UpdateDailyExchangeRateTable(DailyExchangeRateTable, giftBatch.CurrencyCode, LedgerBaseCurrency, giftBatch.ExchangeRateToBase, giftBatch.GlEffectiveDate)) { Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportInformationForLine, RowNumber), String.Format(Catalog.GetString( "An exchange rate of {0} for '{1}' to '{2}' on {3} will be added to the Daily Exchange Rate table after a successful import."), giftBatch.ExchangeRateToBase, giftBatch.CurrencyCode, LedgerBaseCurrency, StringHelper.DateToLocalizedString(giftBatch.GlEffectiveDate)), TResultSeverity.Resv_Info)); } // Now the inverse for From Ledger currency decimal inverseRate = Math.Round(1 / giftBatch.ExchangeRateToBase, 10); UpdateDailyExchangeRateTable(DailyExchangeRateTable, LedgerBaseCurrency, giftBatch.CurrencyCode, inverseRate, giftBatch.GlEffectiveDate); } } } else if (RowType == "T") { ImportMessage = Catalog.GetString("Parsing a transaction row"); // It is a Transaction row if (numberOfElements < 13) // Perhaps this CSV file is a summary, and can't be imported? { Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), Catalog.GetString( "Wrong number of gift columns. Expected at least 13 columns. (This may be a summary?)"), TResultSeverity.Resv_Critical)); FImportLine = sr.ReadLine(); if (FImportLine != null) { TextProcessedLength += (FImportLine.Length + FNewLine.Length); } continue; } if (giftBatch == null) { Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), Catalog.GetString( "Expected a GiftBatch line, but found a Gift Transaction. Will create a dummy working batch for the current period."), TResultSeverity.Resv_Critical)); // in order to carry on we will make a dummy batch and force the date to fit giftBatch = TGiftBatchFunctions.CreateANewGiftBatchRow(ref FMainDS, ref Transaction, ref LedgerTable, FLedgerNumber, DateTime.Today); } // Parse the line into a new row Int32 preParseMessageCount = Messages.Count; AGiftRow gift = FMainDS.AGift.NewRowTyped(true); AGiftDetailRow giftDetails = FMainDS.AGiftDetail.NewRowTyped(true); ParseTransactionLine(gift, giftBatch, ref previousGift, numberOfElements, ref totalBatchAmount, ref ImportMessage, RowNumber, intlRateFromBase, Messages, MotivationDetailTable, NeedRecipientLedgerNumber, giftDetails); if (Messages.Count == preParseMessageCount) // No parsing errors so we can validate { // (parsing errors will have assumed, probably invalid, values) ImportMessage = Catalog.GetString("Validating the gift data"); int messageCountBeforeValidate = preParseMessageCount; TPartnerClass RecipientClass; string RecipientDescription; TPartnerServerLookups.GetPartnerShortName(giftDetails.RecipientKey, out RecipientDescription, out RecipientClass); // Do our standard validation on this gift AGiftValidation.Validate(this, gift, ref Messages, EmptyControlsDict); TSharedFinanceValidation_Gift.ValidateGiftManual(this, gift, giftBatch.BatchYear, giftBatch.BatchPeriod, null, ref Messages, EmptyControlsDict, MethodOfGivingTable, MethodOfPaymentTable); ImportMessage = Catalog.GetString("Validating the gift details data"); AGiftDetailValidation.Validate(this, giftDetails, ref Messages, EmptyControlsDict); TSharedFinanceValidation_Gift.ValidateGiftDetailManual(this, (GiftBatchTDSAGiftDetailRow)giftDetails, ref Messages, EmptyControlsDict, RecipientClass, null, CostCentreTable, AccountTable, MotivationGroupTable, MotivationDetailTable, MailingTable, giftDetails.RecipientKey); // Fix up the messages for (int i = messageCountBeforeValidate; i < Messages.Count; i++) { ((TVerificationResult)Messages[i]).OverrideResultContext(String.Format(MCommonConstants. StrValidationErrorInLine, RowNumber)); if (Messages[i] is TScreenVerificationResult) { TVerificationResult downgrade = new TVerificationResult((TScreenVerificationResult)Messages[i]); Messages.RemoveAt(i); Messages.Insert(i, downgrade); } } } if (TaxDeductiblePercentageEnabled) { // Sets TaxDeductiblePct and uses it to calculate the tax deductibility amounts for a Gift Detail TGift.SetDefaultTaxDeductibilityData(ref giftDetails, gift.DateEntered, Transaction); } if (TVerificationHelper.IsNullOrOnlyNonCritical(Messages)) { if ((LedgerBaseCurrency != LedgerIntlCurrency) && (giftDetails.GiftAmountIntl != 0)) { // Check if the intl amount is what we expected if (giftDetails.GiftAmountIntl != GLRoutines.Divide(giftDetails.GiftAmount, intlRateFromBase, 2)) { ImportMessage = Catalog.GetString("Updating international exchange rate data"); // The import has used an intl rate that is different from the corporate rate // so we should add a Daily Exchange Rate row pair // start with To Ledger currency decimal fromIntlToBase = GLRoutines.Divide(giftDetails.GiftAmount, giftDetails.GiftAmountIntl); if (UpdateDailyExchangeRateTable(DailyExchangeRateTable, LedgerIntlCurrency, LedgerBaseCurrency, fromIntlToBase, giftBatch.GlEffectiveDate)) { Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportInformationForLine, RowNumber), String.Format(Catalog.GetString( "An exchange rate of {0} for '{1}' to '{2}' on {3} will be added to the Daily Exchange Rate table after a successful import."), fromIntlToBase, StringHelper.DateToLocalizedString(giftBatch.GlEffectiveDate)), TResultSeverity.Resv_Info)); } // Now the inverse for From Ledger currency decimal inverseRate = GLRoutines.Divide(giftDetails.GiftAmountIntl, giftDetails.GiftAmount); UpdateDailyExchangeRateTable(DailyExchangeRateTable, LedgerBaseCurrency, LedgerIntlCurrency, inverseRate, giftBatch.GlEffectiveDate); } } } } // If known row analysisType else { if (giftBatch == null) { string msg = Catalog.GetString( "Expecting a Row Type definition. Valid types are 'B' or 'T'. Maybe you are opening a 'Transactions' file."); msg += Catalog.GetString( " You need to be on the 'Transactions' Tab to import transaction-only data into an existing batch."); msg += Catalog.GetString(" Alternatively you may have selected the wrong Field Delimiter."); msg += Catalog.GetString(" Choose a delimiter that shows multiple columns in the preview window."); Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), msg, TResultSeverity.Resv_Critical)); break; } else { Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber), String.Format(Catalog.GetString( "'{0}' is not a valid Row Type. Valid types are 'B' or 'T'."), RowType), TResultSeverity.Resv_Critical)); } } } // if the CSV line qualifies if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true) { CancelledByUser = true; break; } if (Messages.Count > 100) { // This probably means that it is a big file and the user has made the same mistake many times over break; } // Update progress tracker every few percent if ((PercentDone - PreviousPercentDone) > 3) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), String.Format(Catalog.GetString("Importing row {0}"), RowNumber), (PercentDone > 98) ? 98 : PercentDone); PreviousPercentDone = PercentDone; } // Read the next line FImportLine = sr.ReadLine(); if (FImportLine != null) { TextProcessedLength += (FImportLine.Length + FNewLine.Length); } } // while CSV lines if (CancelledByUser) { Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, String.Format(Catalog.GetString("{0} messages reported."), Messages.Count), TResultSeverity.Resv_Info)); Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, "The import was cancelled by the user.", TResultSeverity.Resv_Info)); return; } // Finished reading the file - did we have critical errors? if (!TVerificationHelper.IsNullOrOnlyNonCritical(Messages)) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Batch has critical errors"), 0); // Record error count Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, String.Format(Catalog.GetString("{0} messages reported."), Messages.Count), TResultSeverity.Resv_Info)); if (FImportLine == null) { // We did reach the end of the file Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString( "Reached the end of file but errors occurred. When these errors are fixed the batch will import successfully."), TResultSeverity.Resv_Info)); } else { // We gave up before the end if (Messages.Count > 100) { Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString( "Stopped reading the file after generating more than 100 messages. The file may contian more errors beyond the ones listed here."), TResultSeverity.Resv_Info)); } else { Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString( "Stopped reading the file. Please check you are using a Gift Batch Import file and have chosen the correct Field Delimiter."), TResultSeverity.Resv_Info)); } } // we do not want to think about Gift Destination problems if the import has failed for another reason NeedRecipientLedgerNumber.Clear(); // Do the 'finally' actions and return false return; } // if the import contains gifts with Motivation Group 'GIFT' and that have a Family recipient with no Gift Destination then the import will fail if (NeedRecipientLedgerNumber.Rows.Count > 0) { return; } // Everything is ok, so we can do our finish actions //Update batch total for the last batch entered. if (giftBatch != null) { giftBatch.BatchTotal = totalBatchAmount; } ImportMessage = Catalog.GetString("Saving all data into the database"); //Finally save pending changes (the last number is updated !) ImportMessage = Catalog.GetString("Saving daily exchange rates"); ADailyExchangeRateAccess.SubmitChanges(DailyExchangeRateTable, Transaction); DailyExchangeRateTable.AcceptChanges(); ImportMessage = Catalog.GetString("Saving final batch"); AGiftBatchAccess.SubmitChanges(FMainDS.AGiftBatch, Transaction); FMainDS.AGiftBatch.AcceptChanges(); ImportMessage = Catalog.GetString("Saving final gift"); AGiftAccess.SubmitChanges(FMainDS.AGift, Transaction); FMainDS.AGift.AcceptChanges(); ImportMessage = Catalog.GetString("Saving final giftdetails"); AGiftDetailAccess.SubmitChanges(FMainDS.AGiftDetail, Transaction); FMainDS.AGiftDetail.AcceptChanges(); ImportMessage = Catalog.GetString("Saving ledger changes"); ALedgerAccess.SubmitChanges(LedgerTable, Transaction); FMainDS.ALedger.AcceptChanges(); // Commit the transaction (we know that we got a new one and can control it) SubmissionOK = true; } catch (Exception ex) { // Parse the exception text for possible references to database foreign keys // Make the message more friendly in that case string friendlyExceptionText = MakeFriendlyFKExceptions(ex); if (RowNumber > 0) { // At least we made a start string msg = ImportMessage; if (friendlyExceptionText.Length > 0) { msg += FNewLine + friendlyExceptionText; } if (ImportMessage.StartsWith(Catalog.GetString("Saving "))) { // Do not display any specific line number because these errors occur outside the parsing loop Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileSavingBatch, giftBatch.BatchDescription), msg, TResultSeverity.Resv_Critical)); } else { Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileParsingLine, RowNumber), msg, TResultSeverity.Resv_Critical)); } } else { // We got an exception before we even started parsing the rows (getting a transaction?) Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrExceptionWhileParsingLine, RowNumber), friendlyExceptionText, TResultSeverity.Resv_Critical)); } TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Exception Occurred"), 0); SubmissionOK = false; } finally { sr.Close(); if (SubmissionOK) { TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Gift batch import successful"), 100); } else { Messages.Add(new TVerificationResult(MCommonConstants.StrImportInformation, Catalog.GetString("None of the data from the import was saved."), TResultSeverity.Resv_Critical)); TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(), Catalog.GetString("Data could not be saved."), 0); } TProgressTracker.FinishJob(DomainManager.GClientID.ToString()); } // end of 'finally' }); // Set our 'out' parameters AMessages = Messages; ANeedRecipientLedgerNumber = NeedRecipientLedgerNumber; return SubmissionOK; }