/// <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>
/// store historic Gift matches
/// </summary>
private static void StoreCurrentMatches(BankImportTDS AMatchDS, string ABankAccountCode)
{
TLogging.LogAtLevel(1, "StoreCurrentMatches...");
DataView GiftDetailView = new DataView(
AMatchDS.AGiftDetail, string.Empty,
BankImportTDSAGiftDetailTable.GetGiftTransactionNumberDBName() + "," +
BankImportTDSAGiftDetailTable.GetDetailNumberDBName(),
DataViewRowState.CurrentRows);
SortedList <string, AEpMatchRow> MatchesToAddLater = new SortedList <string, AEpMatchRow>();
// for speed reasons, use a sortedlist instead of a dataview
SortedList <string, AEpMatchRow> MatchesByText = new SortedList <string, AEpMatchRow>();
foreach (AEpMatchRow r in AMatchDS.AEpMatch.Rows)
{
MatchesByText[r.MatchText + ":::" + r.Detail.ToString()] = r;
}
foreach (BankImportTDSAEpTransactionRow tr in AMatchDS.AEpTransaction.Rows)
{
// create a match text which uniquely identifies this transaction
string MatchText = CalculateMatchText(ABankAccountCode, tr);
if (tr.MatchAction != MFinanceConstants.BANK_STMT_STATUS_MATCHED)
{
continue;
}
// get the gift details assigned to this transaction
StringCollection GiftDetailNumbers = StringHelper.GetCSVList(tr.GiftDetailNumbers, ",", false);
foreach (string strDetailNumber in GiftDetailNumbers)
{
DataRowView[] FilteredGiftDetails =
GiftDetailView.FindRows(
new object[] {
tr.GiftTransactionNumber,
Convert.ToInt32(strDetailNumber)
});
// add new matches, and modify existing matches
UpdateMatches(
AMatchDS,
(BankImportTDSAGiftDetailRow)FilteredGiftDetails[0].Row,
MatchText,
Convert.ToInt32(strDetailNumber) - 1,
MatchesByText,
MatchesToAddLater);
}
}
// for speed reasons, add the new rows at the end
foreach (AEpMatchRow m in MatchesToAddLater.Values)
{
AMatchDS.AEpMatch.Rows.Add(m);
}
AMatchDS.PBankingDetails.Clear();
AMatchDS.AGiftDetail.Clear();
AMatchDS.AGift.Clear();
AMatchDS.ThrowAwayAfterSubmitChanges = true;
TLogging.LogAtLevel(1, "before submitchanges");
BankImportTDSAccess.SubmitChanges(AMatchDS);
TLogging.LogAtLevel(1, "after submitchanges");
}
/// <summary>
/// there are several gift batches that might fit this bank statement. find the right one!
/// simple matching; no split gifts, bank account number fits and amount fits
/// </summary>
private static int FindGiftBatch(BankImportTDS AMainDS, AEpStatementRow AStmt)
{
SortedList <Int32, Int32> MatchedGiftBatches = new SortedList <int, int>();
// create the dataview only after loading, otherwise loading is much slower
DataView GiftDetailByAmountAndDonor = new DataView(AMainDS.AGiftDetail,
string.Empty,
AGiftDetailTable.GetGiftAmountDBName() + "," +
BankImportTDSAGiftDetailTable.GetDonorKeyDBName(),
DataViewRowState.CurrentRows);
DataView GiftByAmountAndDonor = new DataView(AMainDS.AGift,
string.Empty,
BankImportTDSAGiftTable.GetTotalAmountDBName() + "," +
AGiftTable.GetDonorKeyDBName(),
DataViewRowState.CurrentRows);
AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetBankSortCodeDBName() + "," +
BankImportTDSPBankingDetailsTable.GetBankAccountNumberDBName();
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
// find the donor for this transaction, by his bank account number
Int64 DonorKey = GetDonorByBankAccountNumber(AMainDS, transaction.BranchCode, transaction.BankAccountNumber);
if (transaction.BankAccountNumber.Length == 0)
{
// useful for NUnit testing for csv import: partnerkey in description
try
{
DonorKey = Convert.ToInt64(transaction.Description);
}
catch (Exception)
{
DonorKey = -1;
}
}
BankImportTDSAGiftDetailRow detailrow = null;
if (DonorKey != -1)
{
DataRowView[] giftDetails = GiftDetailByAmountAndDonor.FindRows(new object[] { transaction.TransactionAmount, DonorKey });
if (giftDetails.Length == 1)
{
// found a possible match
detailrow = (BankImportTDSAGiftDetailRow)giftDetails[0].Row;
}
else
{
// check if we can find a gift with several gift details, that would match this transaction amount
DataRowView[] gifts = GiftByAmountAndDonor.FindRows(new object[] { transaction.TransactionAmount, DonorKey });
if (gifts.Length >= 1)
{
AGiftRow gift = (AGiftRow)gifts[0].Row;
detailrow =
(BankImportTDSAGiftDetailRow)AMainDS.AGiftDetail.Rows.Find(new object[] { gift.LedgerNumber, gift.BatchNumber,
gift.GiftTransactionNumber,
1 });
}
}
}
if (detailrow != null)
{
if (MatchedGiftBatches.ContainsKey(detailrow.BatchNumber))
{
MatchedGiftBatches[detailrow.BatchNumber]++;
}
else
{
MatchedGiftBatches.Add(detailrow.BatchNumber, 1);
}
}
}
int SelectedGiftBatch = -1;
int maxMatches = 0;
foreach (int GiftBatchNumber in MatchedGiftBatches.Keys)
{
if (MatchedGiftBatches[GiftBatchNumber] > maxMatches)
{
maxMatches = MatchedGiftBatches[GiftBatchNumber];
SelectedGiftBatch = GiftBatchNumber;
}
}
if ((SelectedGiftBatch != -1) &&
((AMainDS.AEpTransaction.Rows.Count > 2) &&
(MatchedGiftBatches[SelectedGiftBatch] < AMainDS.AEpTransaction.Rows.Count / 2)))
{
TLogging.Log(
"cannot find enough gifts that look the same, for statement " + AStmt.Filename +
". CountMatches for batch " + SelectedGiftBatch.ToString() + ": " +
MatchedGiftBatches[SelectedGiftBatch].ToString());
SelectedGiftBatch = -1;
}
return(SelectedGiftBatch);
}
/// <summary>
/// store historic Gift matches
/// </summary>
private static void StoreCurrentMatches(BankImportTDS AMatchDS, string ABankAccountCode)
{
DataView GiftDetailView = new DataView(
AMatchDS.AGiftDetail, string.Empty,
BankImportTDSAGiftDetailTable.GetGiftTransactionNumberDBName() + "," +
BankImportTDSAGiftDetailTable.GetDetailNumberDBName(),
DataViewRowState.CurrentRows);
SortedList <string, AEpMatchRow> MatchesToAddLater = new SortedList <string, AEpMatchRow>();
List <string> MatchesToDelete = new List <string>();
foreach (BankImportTDSAEpTransactionRow tr in AMatchDS.AEpTransaction.Rows)
{
// create a match text which uniquely identifies this transaction
string MatchText = CalculateMatchText(ABankAccountCode, tr);
// delete existing matches
MatchesToDelete.Add(MatchText);
if (tr.MatchAction != MFinanceConstants.BANK_STMT_STATUS_MATCHED)
{
continue;
}
// get the gift details assigned to this transaction
StringCollection GiftDetailNumbers = StringHelper.GetCSVList(tr.GiftDetailNumbers, ",", false);
foreach (string strDetailNumber in GiftDetailNumbers)
{
DataRowView[] FilteredGiftDetails =
GiftDetailView.FindRows(
new object[] {
tr.GiftTransactionNumber,
Convert.ToInt32(strDetailNumber)
});
// add new matches
// do not assign tr.EpMatchKey, because we cannot delete the old matches then
CreateNewMatches(
AMatchDS,
(BankImportTDSAGiftDetailRow)FilteredGiftDetails[0].Row,
MatchText,
MatchesToAddLater);
}
}
DataView MatchesByText = new DataView(
AMatchDS.AEpMatch, string.Empty,
AEpMatchTable.GetMatchTextDBName(),
DataViewRowState.CurrentRows);
foreach (string MatchToDelete in MatchesToDelete)
{
DataRowView[] MatchesToDeleteRv = MatchesByText.FindRows(MatchToDelete);
foreach (DataRowView rv in MatchesToDeleteRv)
{
rv.Row.Delete();
}
}
MatchesByText.Sort = string.Empty;
MatchesByText.RowFilter = string.Empty;
// for speed reasons, add the new rows at the end
foreach (AEpMatchRow m in MatchesToAddLater.Values)
{
AMatchDS.AEpMatch.Rows.Add(m);
}
AMatchDS.PBankingDetails.Clear();
AMatchDS.AGiftDetail.Clear();
AMatchDS.AGift.Clear();
AMatchDS.ThrowAwayAfterSubmitChanges = true;
if (TLogging.DebugLevel > 0)
{
TLogging.Log("before submitchanges");
}
BankImportTDSAccess.SubmitChanges(AMatchDS);
if (TLogging.DebugLevel > 0)
{
TLogging.Log("after submitchanges");
}
}
