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