private void ParseTransactionLine(AGiftRow AGift, AGiftBatchRow AGiftBatch, ref AGiftRow APreviousGift, int ANumberOfColumns,
ref decimal ATotalBatchAmount, ref string AImportMessage, int ARowNumber, TVerificationResultCollection AMessages,
TValidationControlsDict AValidationControlsDictGift, TValidationControlsDict AValidationControlsDictGiftDetail,
ACostCentreTable AValidationCostCentreTable, AMotivationGroupTable AValidationMotivationGroupTable,
AMotivationDetailTable AValidationMotivationDetailTable, AMethodOfGivingTable AValidationMethodOfGivingTable,
AMethodOfPaymentTable AValidationMethodOfPaymentTable,
ref GiftBatchTDSAGiftDetailTable ANeedRecipientLedgerNumber, out AGiftDetailRow AGiftDetails)
{
// Is this the format with extra columns?
// Actually if it has the extra columns but does not have the optional final 8 columns we cannot distiguish using this test...
// A file without extra columns will have between 13 and 21 columns - depending on whether some of the optional ones at the end are included.
// A file with extra columns will be between 19 and 27.
// So any count between 19 and 21 is ambiguous. We will assume that if the file has extra columns it also has
// at least enough of the optional ones to exceed 21.
bool HasExtraColumns = (ANumberOfColumns > 21);
AImportMessage = Catalog.GetString("Importing the gift data");
AGift.DonorKey = ImportInt64(Catalog.GetString("Donor key"),
FMainDS.AGift.ColumnDonorKey, ARowNumber, AMessages, AValidationControlsDictGift);
ImportString(Catalog.GetString("short name of donor (unused)"), null, null); // unused
// This group is optional and database NULL's are allowed
AGift.MethodOfGivingCode = ImportString(Catalog.GetString("Method of giving Code"),
FMainDS.AGift.ColumnMethodOfGivingCode, AValidationControlsDictGift, false);
AGift.MethodOfPaymentCode = ImportString(Catalog.GetString("Method Of Payment Code"),
FMainDS.AGift.ColumnMethodOfPaymentCode, AValidationControlsDictGift, false);
AGift.Reference = ImportString(Catalog.GetString("Reference"),
FMainDS.AGift.ColumnReference, AValidationControlsDictGift, false);
AGift.ReceiptLetterCode = ImportString(Catalog.GetString("Receipt letter code"),
FMainDS.AGift.ColumnReceiptLetterCode, AValidationControlsDictGift, false);
if (HasExtraColumns)
{
ImportInt32(Catalog.GetString("Receipt number"),
FMainDS.AGift.ColumnReceiptNumber, ARowNumber, AMessages, AValidationControlsDictGift);
ImportBoolean(Catalog.GetString("First time gift"),
FMainDS.AGift.ColumnFirstTimeGift, AValidationControlsDictGift);
ImportBoolean(Catalog.GetString("Receipt printed"),
FMainDS.AGift.ColumnReceiptPrinted, AValidationControlsDictGift);
}
AImportMessage = Catalog.GetString("Importing the gift details");
AGiftDetails = FMainDS.AGiftDetail.NewRowTyped(true);
if ((APreviousGift != null) && (AGift.DonorKey == APreviousGift.DonorKey)
&& (AGift.MethodOfGivingCode == APreviousGift.MethodOfGivingCode)
&& (AGift.MethodOfPaymentCode == APreviousGift.MethodOfPaymentCode)
&& (AGift.Reference == APreviousGift.Reference)
&& (AGift.ReceiptLetterCode == APreviousGift.ReceiptLetterCode)
&& (AGift.ReceiptNumber == APreviousGift.ReceiptNumber)
&& (AGift.FirstTimeGift == APreviousGift.FirstTimeGift)
&& (AGift.ReceiptPrinted == APreviousGift.ReceiptPrinted))
{
// this row is a new detail for the previousGift
AGift = APreviousGift;
AGift.LastDetailNumber++;
AGiftDetails.DetailNumber = AGift.LastDetailNumber;
}
else
{
APreviousGift = AGift;
AGift.LedgerNumber = AGiftBatch.LedgerNumber;
AGift.BatchNumber = AGiftBatch.BatchNumber;
AGift.GiftTransactionNumber = AGiftBatch.LastGiftNumber + 1;
AGiftBatch.LastGiftNumber++;
AGift.LastDetailNumber = 1;
FMainDS.AGift.Rows.Add(AGift);
AGiftDetails.DetailNumber = 1;
}
AGiftDetails.LedgerNumber = AGift.LedgerNumber;
AGiftDetails.BatchNumber = AGiftBatch.BatchNumber;
AGiftDetails.GiftTransactionNumber = AGift.GiftTransactionNumber;
FMainDS.AGiftDetail.Rows.Add(AGiftDetails);
AGiftDetails.RecipientKey = ImportInt64(Catalog.GetString("Recipient key"),
FMainDS.AGiftDetail.ColumnRecipientKey, ARowNumber, AMessages, AValidationControlsDictGiftDetail);
ImportString(Catalog.GetString("short name of recipient (unused)"), null, null); // unused
if (HasExtraColumns)
{
ImportInt32(Catalog.GetString("Recipient ledger number"),
FMainDS.AGiftDetail.ColumnRecipientLedgerNumber, ARowNumber, AMessages, AValidationControlsDictGiftDetail);
}
// we always calculate RecipientLedgerNumber
AGiftDetails.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(
AGiftDetails.RecipientKey, AGiftBatch.GlEffectiveDate);
decimal currentGiftAmount = ImportDecimal(Catalog.GetString("Gift amount"),
FMainDS.AGiftDetail.ColumnGiftTransactionAmount, ARowNumber, AMessages, AValidationControlsDictGiftDetail);
AGiftDetails.GiftTransactionAmount = currentGiftAmount; // amount in batch currency
ATotalBatchAmount += currentGiftAmount;
AGiftDetails.GiftAmount = GLRoutines.Divide(currentGiftAmount, AGiftBatch.ExchangeRateToBase); // amount in ledger currency
if (HasExtraColumns)
{
// amount in international currency
ImportDecimal(Catalog.GetString("Gift amount intl"),
FMainDS.AGiftDetail.ColumnGiftAmountIntl, ARowNumber, AMessages, AValidationControlsDictGiftDetail);
}
AGiftDetails.ConfidentialGiftFlag = ImportBoolean(Catalog.GetString("Confidential gift"),
FMainDS.AGiftDetail.ColumnConfidentialGiftFlag, AValidationControlsDictGiftDetail, "no");
AGiftDetails.MotivationGroupCode = ImportString(Catalog.GetString("Motivation group code"),
FMainDS.AGiftDetail.ColumnMotivationGroupCode, AValidationControlsDictGiftDetail);
AGiftDetails.MotivationDetailCode = ImportString(Catalog.GetString("Motivation detail"),
FMainDS.AGiftDetail.ColumnMotivationDetailCode, AValidationControlsDictGiftDetail);
if (HasExtraColumns)
{
ImportString(Catalog.GetString("Cost centre code"),
FMainDS.AGiftDetail.ColumnCostCentreCode, AValidationControlsDictGiftDetail);
}
// "In Petra Cost Centre is always inferred from recipient field and motivation detail so is not needed in the import."
AGiftDetails.CostCentreCode = InferCostCentre(AGiftDetails);
// All the remaining columns are optional and can contain database NULL
AGiftDetails.GiftCommentOne = ImportString(Catalog.GetString("Gift comment one"),
FMainDS.AGiftDetail.ColumnGiftCommentOne, AValidationControlsDictGiftDetail, false);
string commentOneType = ImportString(Catalog.GetString("Comment one type"),
FMainDS.AGiftDetail.ColumnCommentOneType, AValidationControlsDictGiftDetail, false);
AGiftDetails.MailingCode = ImportString(Catalog.GetString("Mailing code"),
FMainDS.AGiftDetail.ColumnMailingCode, AValidationControlsDictGiftDetail, false);
AGiftDetails.GiftCommentTwo = ImportString(Catalog.GetString("Gift comment two"),
FMainDS.AGiftDetail.ColumnGiftCommentTwo, AValidationControlsDictGiftDetail, false);
string commentTwoType = ImportString(Catalog.GetString("Comment two type"),
FMainDS.AGiftDetail.ColumnCommentTwoType, AValidationControlsDictGiftDetail, false);
AGiftDetails.GiftCommentThree = ImportString(Catalog.GetString("Gift comment three"),
FMainDS.AGiftDetail.ColumnGiftCommentThree, AValidationControlsDictGiftDetail, false);
string commentThreeType = ImportString(Catalog.GetString("Comment three type"),
FMainDS.AGiftDetail.ColumnCommentThreeType, AValidationControlsDictGiftDetail, false);
SetCommentTypeCase(ref commentOneType);
AGiftDetails.CommentOneType = commentOneType;
SetCommentTypeCase(ref commentTwoType);
AGiftDetails.CommentOneType = commentTwoType;
SetCommentTypeCase(ref commentThreeType);
AGiftDetails.CommentOneType = commentThreeType;
// Find the default Tax deductabilty from the motivation detail. This ensures that the column can be missing.
AMotivationDetailRow motivationDetailRow = (AMotivationDetailRow)AValidationMotivationDetailTable.Rows.Find(
new object[] { FLedgerNumber, AGiftDetails.MotivationGroupCode, AGiftDetails.MotivationDetailCode });
string defaultTaxDeductible =
((motivationDetailRow != null) && !motivationDetailRow.IsTaxDeductibleAccountNull()
&& motivationDetailRow.TaxDeductible) ? "yes" : "no";
AGiftDetails.TaxDeductible = ImportBoolean(Catalog.GetString("Tax deductible"),
FMainDS.AGiftDetail.ColumnTaxDeductible, AValidationControlsDictGiftDetail, defaultTaxDeductible);
// Date entered cannot be imported although it can be modified in the GUI.
// This is because it would have to be the last column in the import for compatibility
// but it belongs with the gift and not the detail so it would need to go in an earlier column.
// For now the import date entered is the effective date.
AGift.DateEntered = AGiftBatch.GlEffectiveDate;
// Enforce the correct case for our GIFT constant
if (String.Compare(AGiftDetails.MotivationGroupCode, MFinanceConstants.MOTIVATION_GROUP_GIFT, true) == 0)
{
AGiftDetails.MotivationGroupCode = MFinanceConstants.MOTIVATION_GROUP_GIFT;
}
TPartnerClass RecipientClass;
string RecipientDescription;
TPartnerServerLookups.GetPartnerShortName(AGiftDetails.RecipientKey, out RecipientDescription, out RecipientClass);
// If the gift has a Family recipient with no Gift Destination then the import will fail. Gift is added to a table and returned to client.
if ((AGiftDetails.RecipientLedgerNumber == 0) && (AGiftDetails.MotivationGroupCode == MFinanceConstants.MOTIVATION_GROUP_GIFT))
{
if (RecipientClass == TPartnerClass.FAMILY)
{
((GiftBatchTDSAGiftDetailRow)AGiftDetails).RecipientDescription = RecipientDescription;
ANeedRecipientLedgerNumber.Rows.Add((object[])AGiftDetails.ItemArray.Clone());
}
}
AImportMessage = Catalog.GetString("Validating the gift data");
int messageCountBeforeValidate = AMessages.Count;
// Do our standard validation on this gift
AGiftValidation.Validate(this, AGift, ref AMessages, AValidationControlsDictGift);
TSharedFinanceValidation_Gift.ValidateGiftManual(this, AGift, AGiftBatch.BatchYear, AGiftBatch.BatchPeriod,
null, ref AMessages, AValidationControlsDictGift, AValidationMethodOfGivingTable, AValidationMethodOfPaymentTable);
AImportMessage = Catalog.GetString("Validating the gift details data");
AGiftDetailValidation.Validate(this, AGiftDetails, ref AMessages, AValidationControlsDictGiftDetail);
TSharedFinanceValidation_Gift.ValidateGiftDetailManual(this, (GiftBatchTDSAGiftDetailRow)AGiftDetails,
ref AMessages, AValidationControlsDictGiftDetail, RecipientClass, AValidationCostCentreTable, AValidationMotivationGroupTable,
AValidationMotivationDetailTable, AGiftDetails.RecipientKey);
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);
}
}
}
/// <summary>
/// Downgrades all <see cref="TScreenVerificationResult" /> items in a <see cref="TVerificationResultCollection" />
/// to <see cref="TVerificationResult" /> items.
/// </summary>
/// <param name="AScreenVerificationResults">A <see cref="TVerificationResultCollection" /> holding <em>exclusively</em>
/// <see cref="TScreenVerificationResult" /> items.</param>
public static void DowngradeScreenVerificationResults(TVerificationResultCollection AScreenVerificationResults)
{
int NumberOfVerificationResults = AScreenVerificationResults.Count;
int NumberOfDowngradedVerificationResults = 0;
for (int Counter1 = 0; Counter1 < NumberOfVerificationResults; Counter1++)
{
if (AScreenVerificationResults[Counter1] is TScreenVerificationResult)
{
AScreenVerificationResults.Add(new TVerificationResult((TScreenVerificationResult)AScreenVerificationResults[Counter1]));
NumberOfDowngradedVerificationResults++;
}
}
for (int Counter2 = 0; Counter2 < NumberOfDowngradedVerificationResults; Counter2++)
{
AScreenVerificationResults.RemoveAt(0);
}
}
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>
/// Import GL Batches 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="AMessages">Additional messages to display in a messagebox</param>
/// <returns>false if error</returns>
public bool ImportGLBatches(
Hashtable ARequestParams,
String AImportString,
out TVerificationResultCollection AMessages
)
{
TProgressTracker.InitProgressTracker(DomainManager.GClientID.ToString(),
Catalog.GetString("Importing GL Batches"),
100);
TProgressTracker.SetCurrentState(DomainManager.GClientID.ToString(),
Catalog.GetString("Initialising"),
5);
TVerificationResultCollection Messages = new TVerificationResultCollection();
// fix for Mono issue with out parameter: https://bugzilla.xamarin.com/show_bug.cgi?id=28196
AMessages = Messages;
GLBatchTDS MainDS = new GLBatchTDS();
GLSetupTDS SetupDS = new GLSetupTDS();
SetupDS.CaseSensitive = true;
StringReader sr = new StringReader(AImportString);
// Parse the supplied parameters
FDelimiter = (String)ARequestParams["Delimiter"];
Int32 LedgerNumber = (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;
// Initialise our working variables
Int32 InitialTextLength = AImportString.Length;
Int32 PercentDone = 10;
Int32 PreviousPercentDone = 0;
ABatchRow NewBatch = null;
AJournalRow NewJournal = null;
int BatchPeriodNumber = -1;
int BatchYearNr = -1;
String ImportMessage = "";
Int32 RowNumber = 0;
decimal intlRateFromBase = -1.0m;
Boolean gotFirstBatch = false;
Boolean CancelledByUser = false;
// Create some validation dictionaries
TValidationControlsDict ValidationControlsDictBatch = new TValidationControlsDict();
TValidationControlsDict ValidationControlsDictJournal = new TValidationControlsDict();
TValidationControlsDict ValidationControlsDictTransaction = new TValidationControlsDict();
// This needs to be initialised because we will be calling the method
TSharedFinanceValidationHelper.GetValidPostingDateRangeDelegate = @TFinanceServerLookups.GetCurrentPostingRangeDates;
TSharedFinanceValidationHelper.GetValidPeriodDatesDelegate = @TAccountingPeriodsWebConnector.GetPeriodDates;
// Get a new transaction
TDBTransaction transaction = null;
Boolean 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(LedgerNumber, transaction);
ACurrencyTable CurrencyTable = ACurrencyAccess.LoadAll(transaction);
AAnalysisTypeAccess.LoadViaALedger(SetupDS, LedgerNumber, transaction);
AFreeformAnalysisAccess.LoadViaALedger(SetupDS, LedgerNumber, transaction);
AAnalysisAttributeAccess.LoadViaALedger(SetupDS, LedgerNumber, transaction);
ACostCentreAccess.LoadViaALedger(SetupDS, LedgerNumber, transaction);
AAccountAccess.LoadViaALedger(SetupDS, LedgerNumber, transaction);
ALedgerInitFlagAccess.LoadViaALedger(SetupDS, LedgerNumber, transaction);
ATransactionTypeAccess.LoadViaALedger(SetupDS, LedgerNumber, transaction);
if (LedgerTable.Rows.Count == 0)
{
return;
}
string LedgerBaseCurrency = LedgerTable[0].BaseCurrency;
string LedgerIntlCurrency = LedgerTable[0].IntlCurrency;
ACorporateExchangeRateTable CorporateExchangeRateTable =
ACorporateExchangeRateAccess.LoadViaACurrencyToCurrencyCode(LedgerIntlCurrency,
transaction);
ADailyExchangeRateTable DailyExchangeRateTable = ADailyExchangeRateAccess.LoadAll(transaction);
// Go round a loop reading the file line by line
ImportMessage = Catalog.GetString("Parsing first line");
FImportLine = sr.ReadLine();
Int32 TextProcessedLength = 0;
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;
int preParseMessageCount = Messages.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");
if (numberOfElements < 4)
{
Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber),
Catalog.GetString("Wrong number of batch columns. Expected 4 columns."),
TResultSeverity.Resv_Critical));
FImportLine = sr.ReadLine();
if (FImportLine != null)
{
TextProcessedLength += (FImportLine.Length + FNewLine.Length);
}
continue;
}
if (NewBatch != null) // update the totals of the batch that has just been imported
{
GLRoutines.UpdateBatchTotals(ref MainDS, ref NewBatch);
if (TVerificationHelper.IsNullOrOnlyNonCritical(Messages))
{
// Save the current batch and its components
ImportMessage = Catalog.GetString("Saving batch");
ABatchAccess.SubmitChanges(MainDS.ABatch, transaction);
MainDS.ABatch.AcceptChanges();
ImportMessage = Catalog.GetString("Saving gift");
AJournalAccess.SubmitChanges(MainDS.AJournal, transaction);
MainDS.AJournal.AcceptChanges();
ImportMessage = Catalog.GetString("Saving giftdetails");
ATransactionAccess.SubmitChanges(MainDS.ATransaction, transaction);
MainDS.ATransaction.AcceptChanges();
}
}
ImportMessage = Catalog.GetString("Starting new batch");
gotFirstBatch = true;
NewBatch = MainDS.ABatch.NewRowTyped(true);
NewBatch.LedgerNumber = LedgerNumber;
LedgerTable[0].LastBatchNumber++;
NewBatch.BatchNumber = LedgerTable[0].LastBatchNumber;
NewBatch.BatchPeriod = LedgerTable[0].CurrentPeriod;
MainDS.ABatch.Rows.Add(NewBatch);
NewJournal = null;
intlRateFromBase = -1.0m;
NewBatch.BatchDescription =
TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Batch description"),
MainDS.ABatch.ColumnBatchDescription, RowNumber, Messages, ValidationControlsDictBatch);
NewBatch.BatchControlTotal =
TCommonImport.ImportDecimal(ref FImportLine, FDelimiter, FCultureInfoNumberFormat,
Catalog.GetString("Batch hash value"),
MainDS.ABatch.ColumnBatchControlTotal, RowNumber, Messages, ValidationControlsDictBatch);
NewBatch.DateEffective =
TCommonImport.ImportDate(ref FImportLine, FDelimiter, FCultureInfoDate,
Catalog.GetString("Batch effective date"),
MainDS.ABatch.ColumnDateEffective, RowNumber, Messages, ValidationControlsDictBatch);
if (Messages.Count == preParseMessageCount)
{
if (TFinancialYear.IsValidPostingPeriod(LedgerNumber,
NewBatch.DateEffective,
out BatchPeriodNumber,
out BatchYearNr,
transaction))
{
NewBatch.BatchYear = BatchYearNr;
NewBatch.BatchPeriod = BatchPeriodNumber;
}
else
{
Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine,
RowNumber),
String.Format(Catalog.GetString(
"The effective date [{0}] of the imported batch is not in an open period."),
StringHelper.DateToLocalizedString(NewBatch.DateEffective)), TResultSeverity.Resv_Critical));
}
int messageCountBeforeValidate = Messages.Count;
// Validate using the standard validation
ImportMessage = Catalog.GetString("Validating the batch data");
ABatchValidation.Validate(this, NewBatch, ref Messages, ValidationControlsDictBatch);
// Now do the additional manual validation
ImportMessage = Catalog.GetString("Additional validation of the batch data");
TSharedFinanceValidation_GL.ValidateGLBatchManual(this, NewBatch, ref Messages, ValidationControlsDictBatch);
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 ((NewBatch.BatchDescription == null) // raise error if empty batch description is imported
|| (NewBatch.BatchDescription == ""))
{
Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine,
RowNumber),
Catalog.GetString("The batch description must not be empty."), TResultSeverity.Resv_Critical));
}
}
}
else if (RowType == "J")
{
ImportMessage = Catalog.GetString("Parsing a journal row");
if (numberOfElements < 7)
{
Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber),
Catalog.GetString("Wrong number of journal columns. Expected 7 columns."),
TResultSeverity.Resv_Critical));
FImportLine = sr.ReadLine();
if (FImportLine != null)
{
TextProcessedLength += (FImportLine.Length + FNewLine.Length);
}
continue;
}
if (NewBatch == null)
{
Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber),
Catalog.GetString(
"Expected a Batch line, but found a Journal. 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
NewBatch = MainDS.ABatch.NewRowTyped(true);
NewBatch.LedgerNumber = LedgerNumber;
LedgerTable[0].LastBatchNumber++;
NewBatch.BatchNumber = LedgerTable[0].LastBatchNumber;
NewBatch.BatchPeriod = LedgerTable[0].CurrentPeriod;
MainDS.ABatch.Rows.Add(NewBatch);
}
NewJournal = MainDS.AJournal.NewRowTyped(true);
NewJournal.LedgerNumber = NewBatch.LedgerNumber;
NewJournal.BatchNumber = NewBatch.BatchNumber;
NewJournal.JournalNumber = NewBatch.LastJournal + 1;
NewJournal.SubSystemCode = MFinanceConstants.SUB_SYSTEM_GL;
NewJournal.TransactionTypeCode = MFinanceConstants.STANDARD_JOURNAL;
NewJournal.TransactionCurrency = LedgerBaseCurrency;
NewJournal.ExchangeRateToBase = 1;
NewJournal.DateEffective = NewBatch.DateEffective;
NewJournal.JournalPeriod = NewBatch.BatchPeriod;
intlRateFromBase = -1.0m;
NewBatch.LastJournal++;
MainDS.AJournal.Rows.Add(NewJournal);
NewJournal.JournalDescription =
TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Journal description"),
MainDS.AJournal.ColumnJournalDescription, RowNumber, Messages, ValidationControlsDictJournal);
NewJournal.SubSystemCode =
TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Journal sub system code"),
MainDS.AJournal.ColumnSubSystemCode, RowNumber, Messages, ValidationControlsDictJournal).ToUpper();
NewJournal.TransactionTypeCode =
TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Journal transaction type"),
MainDS.AJournal.ColumnTransactionTypeCode, RowNumber, Messages, ValidationControlsDictJournal).ToUpper();
NewJournal.TransactionCurrency =
TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Journal transaction currency"),
MainDS.AJournal.ColumnTransactionCurrency, RowNumber, Messages, ValidationControlsDictJournal).ToUpper();
NewJournal.ExchangeRateToBase =
TCommonImport.ImportDecimal(ref FImportLine, FDelimiter, FCultureInfoNumberFormat,
Catalog.GetString("Journal exchange rate"),
MainDS.AJournal.ColumnExchangeRateToBase, RowNumber, Messages, ValidationControlsDictJournal);
NewJournal.DateEffective =
TCommonImport.ImportDate(ref FImportLine, FDelimiter, FCultureInfoDate,
Catalog.GetString("Journal effective date"),
MainDS.AJournal.ColumnDateEffective, RowNumber, Messages, ValidationControlsDictJournal);
if (Messages.Count == preParseMessageCount)
{
int messageCountBeforeValidate = Messages.Count;
// Validate using the standard validation
ImportMessage = Catalog.GetString("Validating the journal data");
AJournalValidation.Validate(this, NewJournal, ref Messages, ValidationControlsDictJournal);
// Now do the additional manual validation
ImportMessage = Catalog.GetString("Additional validation of the journal data");
TSharedFinanceValidation_GL.ValidateGLJournalManual(this, NewJournal, ref Messages,
ValidationControlsDictJournal, SetupDS, CurrencyTable,
CorporateExchangeRateTable, LedgerBaseCurrency, LedgerIntlCurrency);
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 this batch is in my base currency,
// the ExchangeRateToBase must be 1:
if ((NewJournal.TransactionCurrency == LedgerBaseCurrency)
&& (NewJournal.ExchangeRateToBase != 1.0m))
{
Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine,
RowNumber),
Catalog.GetString("Journal in base currency must have exchange rate of 1.00."),
TResultSeverity.Resv_Critical));
}
//
// The DateEffective might be different to that of the Batch,
// but it must be in the same accounting period.
Int32 journalYear;
Int32 journalPeriod;
DateTime journalDate = NewJournal.DateEffective;
TFinancialYear.GetLedgerDatePostingPeriod(LedgerNumber, ref journalDate,
out journalYear, out journalPeriod, transaction, false);
if ((journalYear != BatchYearNr) || (journalPeriod != BatchPeriodNumber))
{
Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine,
RowNumber),
String.Format(Catalog.GetString(
"The journal effective date {0} is not in the same period as the batch date {1}."),
journalDate.ToShortDateString(), NewBatch.DateEffective.ToShortDateString()),
TResultSeverity.Resv_Critical));
}
if (TVerificationHelper.IsNullOrOnlyNonCritical(Messages))
{
// Get a Corporate Exchange Rate for international currency
// 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)
DateTime firstDayOfMonth;
if (TSharedFinanceValidationHelper.GetFirstDayOfAccountingPeriod(LedgerNumber, NewJournal.DateEffective,
out firstDayOfMonth))
{
intlRateFromBase =
TExchangeRateTools.GetCorporateExchangeRate(LedgerBaseCurrency, LedgerIntlCurrency,
firstDayOfMonth,
NewJournal.DateEffective);
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(firstDayOfMonth),
StringHelper.DateToLocalizedString(NewJournal.DateEffective)),
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
if (NewJournal.TransactionCurrency != LedgerBaseCurrency)
{
// we need to create a daily exchange rate pair for the transaction date
// start with To Ledger currency
if (UpdateDailyExchangeRateTable(DailyExchangeRateTable, NewJournal.TransactionCurrency,
LedgerBaseCurrency,
NewJournal.ExchangeRateToBase, NewJournal.DateEffective))
{
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."),
NewJournal.ExchangeRateToBase,
NewJournal.TransactionCurrency,
LedgerBaseCurrency,
StringHelper.DateToLocalizedString(NewJournal.DateEffective)),
TResultSeverity.Resv_Info));
}
// Now the inverse for From Ledger currency
decimal inverseRate = Math.Round(1 / NewJournal.ExchangeRateToBase, 10);
UpdateDailyExchangeRateTable(DailyExchangeRateTable, LedgerBaseCurrency,
NewJournal.TransactionCurrency,
inverseRate, NewJournal.DateEffective);
}
}
}
}
else if (RowType == "T")
{
ImportMessage = Catalog.GetString("Parsing a transaction row");
bool skipThisLine = false;
if (numberOfElements < 8)
{
Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber),
Catalog.GetString("Wrong number of transaction columns. Expected at least 8 columns."),
TResultSeverity.Resv_Critical));
skipThisLine = true;
}
if (NewJournal == null)
{
Messages.Add(new TVerificationResult(String.Format(MCommonConstants.StrParsingErrorInLine, RowNumber),
Catalog.GetString("Expected a Journal row but found a Transaction row."),
TResultSeverity.Resv_Critical));
skipThisLine = true;
}
if (skipThisLine)
{
FImportLine = sr.ReadLine();
if (FImportLine != null)
{
TextProcessedLength += (FImportLine.Length + FNewLine.Length);
}
continue;
}
ImportGLTransactionsInner(LedgerNumber, RowNumber, ref MainDS, ref SetupDS, ref NewBatch, ref NewJournal,
intlRateFromBase, ref transaction, ref ImportMessage, ref Messages,
ref ValidationControlsDictTransaction);
}
else
{
if ((NewBatch == null) && !gotFirstBatch)
{
string msg = Catalog.GetString(
"Expecting a Row Type definition. Valid types are 'B', 'J' 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', 'J' 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 contain 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 GL Batch Import file and have chosen the correct Field Delimiter."),
TResultSeverity.Resv_Info));
}
}
// Do the 'finally' actions and return false
return;
}
// Everything is ok, so we can do our finish actions
// Save all pending changes (last xxx number is updated)
ImportMessage = Catalog.GetString("Saving daily exchange rate data");
ADailyExchangeRateAccess.SubmitChanges(DailyExchangeRateTable, transaction);
DailyExchangeRateTable.AcceptChanges();
ImportMessage = Catalog.GetString("Saving changes to Ledger table");
ALedgerAccess.SubmitChanges(LedgerTable, transaction);
LedgerTable.AcceptChanges();
// Update totals of final batch
ImportMessage = Catalog.GetString("Saving changes to batch totals");
if (NewBatch != null)
{
GLRoutines.UpdateBatchTotals(ref MainDS, ref NewBatch);
}
ImportMessage = Catalog.GetString("Saving changes to batch");
ABatchAccess.SubmitChanges(MainDS.ABatch, transaction);
MainDS.ABatch.AcceptChanges();
ImportMessage = Catalog.GetString("Saving changes to journal");
AJournalAccess.SubmitChanges(MainDS.AJournal, transaction);
MainDS.AJournal.AcceptChanges();
ImportMessage = Catalog.GetString("Saving changes to transactions");
ATransactionAccess.SubmitChanges(MainDS.ATransaction, transaction);
MainDS.ATransaction.AcceptChanges();
// Now we are done!!!
submissionOK = true;
} // try
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,
NewBatch.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);
} // catch
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'
}); // Begin Auto Transaction
// Set our 'out' parameters
AMessages = Messages;
return submissionOK;
} // Import GL Batches
} // Import GL Transactions
private void ImportGLTransactionsInner(Int32 ALedgerNumber,
Int32 ARowNumber,
ref GLBatchTDS AMainDS,
ref GLSetupTDS ASetupDS,
ref ABatchRow ANewBatchRow,
ref AJournalRow ANewJournalRow,
decimal AIntlRateFromBase,
ref TDBTransaction ATransaction,
ref string AImportMessage,
ref TVerificationResultCollection AMessages,
ref TValidationControlsDict AValidationControlsDictTransaction)
{
AImportMessage = Catalog.GetString("Parsing a transaction line.");
string strIgnoreAnalysisTypeAndValue = Catalog.GetString(" The analysis type/value pair will be ignored.");
GLBatchTDSATransactionRow NewTransaction = AMainDS.ATransaction.NewRowTyped(true);
NewTransaction.LedgerNumber = ANewJournalRow.LedgerNumber;
NewTransaction.BatchNumber = ANewJournalRow.BatchNumber;
NewTransaction.JournalNumber = ANewJournalRow.JournalNumber;
NewTransaction.TransactionNumber = ++ANewJournalRow.LastTransactionNumber;
AMainDS.ATransaction.Rows.Add(NewTransaction);
int preParseMessageCount = AMessages.Count;
int nonCriticalErrorCount = 0;
string costCentreCode = TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Cost centre"),
AMainDS.ATransaction.ColumnCostCentreCode, ARowNumber, AMessages, AValidationControlsDictTransaction).ToUpper();
string accountCode = TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Account code"),
AMainDS.ATransaction.ColumnAccountCode, ARowNumber, AMessages, AValidationControlsDictTransaction).ToUpper();
// This might add a non-critical error
int msgCount = AMessages.Count;
TCommonImport.FixAccountCodes(ALedgerNumber, ARowNumber, ref accountCode, ASetupDS.AAccount,
ref costCentreCode, ASetupDS.ACostCentre, AMessages);
nonCriticalErrorCount = AMessages.Count - msgCount;
NewTransaction.CostCentreCode = costCentreCode;
NewTransaction.AccountCode = accountCode;
NewTransaction.Narrative = TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Narrative"),
AMainDS.ATransaction.ColumnNarrative, ARowNumber, AMessages, AValidationControlsDictTransaction);
NewTransaction.Reference = TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Reference"),
AMainDS.ATransaction.ColumnReference, ARowNumber, AMessages, AValidationControlsDictTransaction);
DateTime TransactionDate = TCommonImport.ImportDate(ref FImportLine, FDelimiter, FCultureInfoDate, Catalog.GetString("Transaction date"),
AMainDS.ATransaction.ColumnTransactionDate, ARowNumber, AMessages, AValidationControlsDictTransaction);
decimal DebitAmount = TCommonImport.ImportDecimal(ref FImportLine, FDelimiter, FCultureInfoNumberFormat, Catalog.GetString("Debit amount"),
AMainDS.ATransaction.ColumnTransactionAmount, ARowNumber, AMessages, AValidationControlsDictTransaction, "0");
decimal CreditAmount =
TCommonImport.ImportDecimal(ref FImportLine, FDelimiter, FCultureInfoNumberFormat, Catalog.GetString("Credit amount"),
AMainDS.ATransaction.ColumnTransactionAmount, ARowNumber, AMessages, AValidationControlsDictTransaction, "0");
// The critical parsing is complete now
bool hasParsingErrors = (AMessages.Count != (preParseMessageCount + nonCriticalErrorCount));
for (int i = 0; i < 10; i++)
{
String analysisType = TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Analysis Type") + "#" + i,
AMainDS.ATransAnalAttrib.ColumnAnalysisTypeCode, ARowNumber, AMessages, AValidationControlsDictTransaction).ToUpper();
String analysisValue = TCommonImport.ImportString(ref FImportLine, FDelimiter, Catalog.GetString("Analysis Value") + "#" + i,
AMainDS.ATransAnalAttrib.ColumnAnalysisAttributeValue, ARowNumber, AMessages, AValidationControlsDictTransaction);
bool gotType = (analysisType != null) && (analysisType.Length > 0);
bool gotValue = (analysisValue != null) && (analysisValue.Length > 0);
if (gotType && !gotValue)
{
// All analysis analysisType errors are non-critical
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationWarningInLine, ARowNumber),
Catalog.GetString("Transaction analysis attributes must have an attribute value.") + strIgnoreAnalysisTypeAndValue,
TResultSeverity.Resv_Noncritical));
}
if (!hasParsingErrors)
{
// The analysis data is only imported if all corresponding values are there:
// Errors are recorded on-the-fly but are marked as non-critical
if (gotType && gotValue)
{
DataRow atrow = ASetupDS.AAnalysisType.Rows.Find(new Object[] { NewTransaction.LedgerNumber, analysisType });
DataRow afrow = ASetupDS.AFreeformAnalysis.Rows.Find(new Object[] { NewTransaction.LedgerNumber, analysisType, analysisValue });
AAnalysisAttributeRow anrow = (AAnalysisAttributeRow)ASetupDS.AAnalysisAttribute.Rows.Find(
new Object[] { NewTransaction.LedgerNumber, analysisType, NewTransaction.AccountCode });
bool isActive = (anrow != null) && anrow.Active;
if ((atrow != null) && (afrow != null) && isActive)
{
ATransAnalAttribRow NewTransAnalAttrib = AMainDS.ATransAnalAttrib.NewRowTyped(true);
NewTransAnalAttrib.LedgerNumber = NewTransaction.LedgerNumber;
NewTransAnalAttrib.BatchNumber = NewTransaction.BatchNumber;
NewTransAnalAttrib.JournalNumber = NewTransaction.JournalNumber;
NewTransAnalAttrib.TransactionNumber = NewTransaction.TransactionNumber;
NewTransAnalAttrib.AnalysisTypeCode = analysisType;
NewTransAnalAttrib.AnalysisAttributeValue = analysisValue;
NewTransAnalAttrib.AccountCode = NewTransaction.AccountCode;
AMainDS.ATransAnalAttrib.Rows.Add(NewTransAnalAttrib);
}
else
{
// All analysis analysisType errors are non-critical
if (atrow == null)
{
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationWarningInLine, ARowNumber),
String.Format(Catalog.GetString("Unknown transaction analysis attribute '{0}'."),
analysisType) + strIgnoreAnalysisTypeAndValue,
TResultSeverity.Resv_Noncritical));
}
else if (afrow == null)
{
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationWarningInLine, ARowNumber),
String.Format(Catalog.GetString("Unknown transaction analysis value '{0}' for type '{1}'."),
analysisValue, analysisType) + strIgnoreAnalysisTypeAndValue,
TResultSeverity.Resv_Noncritical));
}
else if (!isActive)
{
if (anrow == null)
{
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationWarningInLine, ARowNumber),
String.Format(Catalog.GetString(
"Transaction analysis type/value '{0}'/'{1}' is not associated with account '{2}'."),
analysisType, analysisValue, NewTransaction.AccountCode) + strIgnoreAnalysisTypeAndValue,
TResultSeverity.Resv_Noncritical));
}
else
{
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationWarningInLine, ARowNumber),
String.Format(Catalog.GetString("Transaction analysis type/value '{0}'/'{1}' is no longer active."),
analysisType, analysisValue) + strIgnoreAnalysisTypeAndValue,
TResultSeverity.Resv_Noncritical));
}
}
}
}
}
}
if (!hasParsingErrors)
{
NewTransaction.TransactionDate = TransactionDate;
if ((DebitAmount == 0) && (CreditAmount == 0))
{
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, ARowNumber),
Catalog.GetString("Either the debit amount or the credit amount must be greater than 0."), TResultSeverity.Resv_Critical));
}
if ((DebitAmount < 0) || (CreditAmount < 0))
{
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, ARowNumber),
Catalog.GetString("Negative amount specified - debits and credits must be positive."), TResultSeverity.Resv_Critical));
}
if ((DebitAmount != 0) && (CreditAmount != 0))
{
AMessages.Add(new TVerificationResult(String.Format(MCommonConstants.StrImportValidationErrorInLine, ARowNumber),
Catalog.GetString("Transactions cannot have values for both debit and credit amounts."), TResultSeverity.Resv_Critical));
}
if (DebitAmount != 0)
{
NewTransaction.DebitCreditIndicator = true;
NewTransaction.TransactionAmount = DebitAmount;
}
else
{
NewTransaction.DebitCreditIndicator = false;
NewTransaction.TransactionAmount = CreditAmount;
}
NewTransaction.AmountInBaseCurrency = GLRoutines.Divide(NewTransaction.TransactionAmount, ANewJournalRow.ExchangeRateToBase);
// If we know the international currency exchange rate we can set the value for the transaction amount
if (AIntlRateFromBase > 0.0m)
{
NewTransaction.AmountInIntlCurrency = GLRoutines.Divide(NewTransaction.AmountInBaseCurrency, AIntlRateFromBase, 2);
}
// Now we can start validation
int messageCountBeforeValidate = AMessages.Count;
// Do our standard gift batch validation checks on this row
AImportMessage = Catalog.GetString("Validating the transaction data");
ATransactionValidation.Validate(this, NewTransaction, ref AMessages, AValidationControlsDictTransaction);
// And do the additional manual ones
AImportMessage = Catalog.GetString("Additional validation of the transaction data");
TSharedFinanceValidation_GL.ValidateGLDetailManual(this,
ANewBatchRow,
NewTransaction,
null,
ref AMessages,
AValidationControlsDictTransaction,
ASetupDS.ACostCentre,
ASetupDS.AAccount);
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 (NewTransaction.TransactionAmount <= 0.0m)
{
// We will have a validation message that will duplicate one we already have and may not really make sense in the context
// of separate credit and debit amounts
for (int i = messageCountBeforeValidate; i < AMessages.Count; i++)
{
TVerificationResult msg = (TVerificationResult)AMessages[i];
if (msg.ResultText.Contains(Catalog.GetString("Debit amount")) || msg.ResultText.Contains(Catalog.GetString("Credit amount")))
{
AMessages.RemoveAt(i);
break;
}
}
}
}
}
/// <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);
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;
}
