/// <summary> /// match imported transactions from bank statement to an existing gift batch; /// this method is only for donors that can be identified by their bank account /// </summary> private static void MatchDonorsWithKnownBankaccount(BankImportTDS AMainDS) { DataView GiftDetailWithoutAmountView = new DataView(AMainDS.AGiftDetail, string.Empty, BankImportTDSAGiftDetailTable.GetDonorKeyDBName() + "," + BankImportTDSAGiftDetailTable.GetAlreadyMatchedDBName(), DataViewRowState.CurrentRows); DataView TransactionsByDonorView = new DataView(AMainDS.AEpTransaction, string.Empty, BankImportTDSAEpTransactionTable.GetDonorKeyDBName() + "," + BankImportTDSAEpTransactionTable.GetMatchActionDBName(), DataViewRowState.CurrentRows); foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows) { if ((transaction.DonorKey != -1) && (transaction.MatchAction == MFinanceConstants.BANK_STMT_STATUS_UNMATCHED)) { // get all gifts of this donor, and all bank statement transactions DataRowView[] GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows( new object[] { transaction.DonorKey, false }); DataRowView[] TransactionsByDonor = TransactionsByDonorView.FindRows( new object[] { transaction.DonorKey, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED }); while (MatchOneDonor(AMainDS, GiftDetailWithoutAmount, TransactionsByDonor)) { GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows( new object[] { transaction.DonorKey, false }); TransactionsByDonor = TransactionsByDonorView.FindRows( new object[] { transaction.DonorKey, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED }); } } } }
/// <summary> /// match imported transactions from bank statement to an existing gift batch /// </summary> /// <returns>true while new matches are found</returns> private static bool MatchTransactionsToGiftBatch(BankImportTDS AMainDS) { bool newMatchFound = false; DataView GiftDetailWithoutAmountView = new DataView(AMainDS.AGiftDetail, string.Empty, BankImportTDSAGiftDetailTable.GetDonorKeyDBName() + "," + BankImportTDSAGiftDetailTable.GetAlreadyMatchedDBName(), DataViewRowState.CurrentRows); DataView GiftDetailByBatchNumberMatchStatus = new DataView(AMainDS.AGiftDetail, string.Empty, BankImportTDSAGiftDetailTable.GetAlreadyMatchedDBName(), DataViewRowState.CurrentRows); DataView TransactionsByBankAccountView = new DataView(AMainDS.AEpTransaction, string.Empty, BankImportTDSAEpTransactionTable.GetBankAccountNumberDBName() + "," + BankImportTDSAEpTransactionTable.GetBranchCodeDBName() + "," + BankImportTDSAEpTransactionTable.GetMatchActionDBName(), DataViewRowState.CurrentRows); foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows) { if (transaction.MatchAction == Ict.Petra.Shared.MFinance.MFinanceConstants.BANK_STMT_STATUS_UNMATCHED) { DataRowView[] filteredRows = GiftDetailByBatchNumberMatchStatus.FindRows(new object[] { false }); BankImportTDSAGiftDetailRow BestMatch = null; int BestMatchNumber = 0; foreach (DataRowView rv in filteredRows) { BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv.Row; int matchNumberDonorSurname = MatchingWords(Calculations.FormatShortName(detailrow.DonorShortName, eShortNameFormat.eOnlySurname), transaction.AccountName); if (matchNumberDonorSurname == 0) { // if surname does not match: ignore, just to be sure // problem: will ignore umlaut etc. can be fixed for the next time by entering the bank account into OpenPetra continue; } int matchNumberDonor = MatchingWords(detailrow.DonorShortName, transaction.AccountName) + matchNumberDonorSurname * 3; int matchNumberRecipient = MatchingWords(detailrow.RecipientDescription, transaction.Description); if ((matchNumberDonor > 0) && (matchNumberRecipient > 0) && ((matchNumberDonor > 1) || (matchNumberRecipient > 1)) && (matchNumberRecipient + matchNumberDonor > BestMatchNumber)) { BestMatchNumber = matchNumberRecipient + matchNumberDonor; BestMatch = detailrow; } } if (BestMatch != null) { // get all gifts of this donor, and all bank statement transactions DataRowView[] GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows( new object[] { BestMatch.DonorKey, false }); DataRowView[] TransactionsByBankAccount = TransactionsByBankAccountView.FindRows( new object[] { transaction.BankAccountNumber, transaction.BranchCode, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED }); while (MatchOneDonor(AMainDS, GiftDetailWithoutAmount, TransactionsByBankAccount)) { GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows( new object[] { BestMatch.DonorKey, false }); TransactionsByBankAccount = TransactionsByBankAccountView.FindRows( new object[] { transaction.BankAccountNumber, transaction.BranchCode, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED }); newMatchFound = true; } } } } return(newMatchFound); }
/// <summary> /// import one MT940 file, split into multiple statements per year /// </summary> static public bool ImportFromFile( Int32 ALedgerNumber, string ABankAccountCode, string AFileName, string AFileContent, bool AParsePreviousYear, out Int32 AStatementKey, out TVerificationResultCollection AVerificationResult) { AVerificationResult = new TVerificationResultCollection(); TSwiftParser parser = new TSwiftParser(); parser.ProcessFileContent(AFileContent); BankImportTDS MainDS = new BankImportTDS(); Int32 statementCounter = MainDS.AEpStatement.Rows.Count; foreach (TStatement stmt in parser.statements) { Int32 transactionCounter = 0; foreach (TTransaction tr in stmt.transactions) { BankImportTDSAEpTransactionRow row = MainDS.AEpTransaction.NewRowTyped(); row.StatementKey = (statementCounter + 1) * -1; row.Order = transactionCounter; row.DetailKey = -1; row.AccountName = tr.partnerName; if ((tr.accountCode != null) && Regex.IsMatch(tr.accountCode, "^[A-Z]")) { // this is an iban row.Iban = tr.accountCode; row.Bic = tr.bankCode; row.BranchCode = tr.accountCode.Substring(4, 8).TrimStart(new char[] { '0' }); row.BankAccountNumber = tr.accountCode.Substring(12).TrimStart(new char[] { '0' }); } else if (tr.accountCode != null) { row.BankAccountNumber = tr.accountCode.TrimStart(new char[] { '0' }); row.BranchCode = tr.bankCode == null ? string.Empty : tr.bankCode.TrimStart(new char[] { '0' }); row.Iban = string.Empty; row.Bic = string.Empty; } row.DateEffective = tr.valueDate; row.TransactionAmount = tr.amount; row.Description = tr.description; row.TransactionTypeCode = tr.typecode; // see the codes: http://www.hettwer-beratung.de/sepa-spezialwissen/sepa-technische-anforderungen/sepa-gesch%C3%A4ftsvorfallcodes-gvc-mt-940/ if ((row.TransactionTypeCode == "052") || (row.TransactionTypeCode == "051") || (row.TransactionTypeCode == "053") || (row.TransactionTypeCode == "067") || (row.TransactionTypeCode == "068") || (row.TransactionTypeCode == "069") || (row.TransactionTypeCode == "119") || /* Einzelbuchung Spende (Purpose: CHAR) */ (row.TransactionTypeCode == "152") || /* SEPA Credit Transfer Einzelbuchung Dauerauftrag */ (row.TransactionTypeCode == "166") || /* SEPA Credit Transfer */ (row.TransactionTypeCode == "169") /* SEPA Credit Transfer Donation */ ) { row.TransactionTypeCode += MFinanceConstants.BANK_STMT_POTENTIAL_GIFT; } MainDS.AEpTransaction.Rows.Add(row); transactionCounter++; } AEpStatementRow epstmt = MainDS.AEpStatement.NewRowTyped(); epstmt.StatementKey = (statementCounter + 1) * -1; epstmt.LedgerNumber = ALedgerNumber; epstmt.Date = stmt.date; epstmt.CurrencyCode = stmt.currency; epstmt.Filename = AFileName; epstmt.BankAccountCode = ABankAccountCode; epstmt.IdFromBank = stmt.id; if (AFileName.Length > AEpStatementTable.GetFilenameLength()) { epstmt.Filename = TAppSettingsManager.GetValue("BankNameFor" + stmt.bankCode + "/" + stmt.accountCode, stmt.bankCode + "/" + stmt.accountCode, true); } epstmt.StartBalance = stmt.startBalance; epstmt.EndBalance = stmt.endBalance; MainDS.AEpStatement.Rows.Add(epstmt); // sort by amount, and by accountname; this is the order of the paper statements and attachments MainDS.AEpTransaction.DefaultView.Sort = BankImportTDSAEpTransactionTable.GetTransactionAmountDBName() + "," + BankImportTDSAEpTransactionTable.GetOrderDBName(); MainDS.AEpTransaction.DefaultView.RowFilter = BankImportTDSAEpTransactionTable.GetStatementKeyDBName() + "=" + epstmt.StatementKey.ToString(); // starting with the most negative amount, which should be the last in the order on the statement Int32 countOrderOnStatement = MainDS.AEpTransaction.DefaultView.Count; bool countingNegative = true; foreach (DataRowView rv in MainDS.AEpTransaction.DefaultView) { BankImportTDSAEpTransactionRow row = (BankImportTDSAEpTransactionRow)rv.Row; if ((row.TransactionAmount > 0) && countingNegative) { countingNegative = false; countOrderOnStatement = 1; } if (countingNegative) { row.NumberOnPaperStatement = countOrderOnStatement; countOrderOnStatement--; } else { row.NumberOnPaperStatement = countOrderOnStatement; countOrderOnStatement++; } } statementCounter++; } if (TBankStatementImport.StoreNewBankStatement( MainDS, out AStatementKey) == TSubmitChangesResult.scrOK) { return(true); } return(false); }
/// <summary> /// import one CAMT file, split into multiple statements per year /// </summary> static public bool ImportFromFile( Int32 ALedgerNumber, string ABankAccountCode, string AFileName, string AFileContent, bool AParsePreviousYear, out Int32 AStatementKey, out TVerificationResultCollection AVerificationResult) { TCAMTParser parser = new TCAMTParser(); AStatementKey = -1; parser.ProcessFileContent(AFileContent, AParsePreviousYear, out AVerificationResult); if (AVerificationResult.HasCriticalErrors) { return(false); } BankImportTDS MainDS = new BankImportTDS(); Int32 statementCounter = MainDS.AEpStatement.Rows.Count; foreach (TStatement stmt in parser.statements) { if (stmt.severalYears && !AParsePreviousYear) { // parse the transactions of the previous year separately ImportFromFile(ALedgerNumber, ABankAccountCode, AFileName, AFileContent, true, out AStatementKey, out AVerificationResult); if (AVerificationResult.HasCriticalErrors) { return(false); } } Int32 transactionCounter = 0; foreach (TTransaction tr in stmt.transactions) { BankImportTDSAEpTransactionRow row = MainDS.AEpTransaction.NewRowTyped(); row.StatementKey = (statementCounter + 1) * -1; row.Order = transactionCounter; row.DetailKey = -1; row.AccountName = tr.partnerName; if ((tr.accountCode != null) && Regex.IsMatch(tr.accountCode, "^[A-Z]")) { // this is an iban row.Iban = tr.accountCode; row.Bic = tr.bankCode; row.BranchCode = tr.accountCode.Substring(4, 8).TrimStart(new char[] { '0' }); row.BankAccountNumber = tr.accountCode.Substring(12).TrimStart(new char[] { '0' }); } else if (tr.accountCode != null) { row.BankAccountNumber = tr.accountCode.TrimStart(new char[] { '0' }); row.BranchCode = tr.bankCode == null ? string.Empty : tr.bankCode.TrimStart(new char[] { '0' }); row.Iban = string.Empty; row.Bic = string.Empty; } row.DateEffective = tr.valueDate; row.TransactionAmount = tr.amount; row.Description = tr.description; row.TransactionTypeCode = tr.typecode; // see the codes: https://www.wgzbank.de/export/sites/wgzbank/de/wgzbank/downloads/produkte_leistungen/firmenkunden/zv_aktuelles/Uebersicht-GVC-und-Buchungstexte-WGZ-BANK_V062015.pdf if ((row.TransactionTypeCode == "052") || (row.TransactionTypeCode == "051") || (row.TransactionTypeCode == "053") || (row.TransactionTypeCode == "067") || (row.TransactionTypeCode == "068") || (row.TransactionTypeCode == "069") || (row.TransactionTypeCode == "119") || /* Einzelbuchung Spende (Purpose: CHAR) */ (row.TransactionTypeCode == "152") || /* SEPA Credit Transfer Einzelbuchung Dauerauftrag */ (row.TransactionTypeCode == "166") || /* SEPA Credit Transfer */ (row.TransactionTypeCode == "169") /* SEPA Credit Transfer Donation */ ) { // only incoming money is a potential gift if (row.TransactionAmount > 0) { row.TransactionTypeCode += MFinanceConstants.BANK_STMT_POTENTIAL_GIFT; } } MainDS.AEpTransaction.Rows.Add(row); transactionCounter++; } AEpStatementRow epstmt = MainDS.AEpStatement.NewRowTyped(); epstmt.LedgerNumber = ALedgerNumber; epstmt.StatementKey = (statementCounter + 1) * -1; epstmt.Date = stmt.date; epstmt.CurrencyCode = stmt.currency; epstmt.BankAccountCode = ABankAccountCode; epstmt.IdFromBank = stmt.id; if (AFileName.Length > AEpStatementTable.GetFilenameLength()) { epstmt.Filename = stmt.bankCode + "/" + stmt.accountCode; } else { epstmt.Filename = AFileName; } epstmt.StartBalance = stmt.startBalance; epstmt.EndBalance = stmt.endBalance; MainDS.AEpStatement.Rows.Add(epstmt); // sort by amount, and by accountname; this is the order of the paper statements and attachments MainDS.AEpTransaction.DefaultView.Sort = BankImportTDSAEpTransactionTable.GetTransactionAmountDBName() + "," + BankImportTDSAEpTransactionTable.GetOrderDBName(); MainDS.AEpTransaction.DefaultView.RowFilter = BankImportTDSAEpTransactionTable.GetStatementKeyDBName() + "=" + epstmt.StatementKey.ToString(); // starting with the most negative amount, which should be the last in the order on the statement Int32 countOrderOnStatement = MainDS.AEpTransaction.DefaultView.Count; bool countingNegative = true; foreach (DataRowView rv in MainDS.AEpTransaction.DefaultView) { BankImportTDSAEpTransactionRow row = (BankImportTDSAEpTransactionRow)rv.Row; if ((row.TransactionAmount > 0) && countingNegative) { countingNegative = false; countOrderOnStatement = 1; } if (countingNegative) { row.NumberOnPaperStatement = countOrderOnStatement; countOrderOnStatement--; } else { row.NumberOnPaperStatement = countOrderOnStatement; countOrderOnStatement++; } } statementCounter++; } if (TBankStatementImport.StoreNewBankStatement( MainDS, out AStatementKey) == TSubmitChangesResult.scrOK) { return(true); } return(false); }