/// add new matches
private static Int32 CreateNewMatches(
BankImportTDS AMatchDS,
BankImportTDSAGiftDetailRow AGiftDetailRow,
string AMatchText,
SortedList <string, AEpMatchRow> AMatchesToAddLater)
{
AEpMatchRow newMatch = null;
// we might have added such a match for the current statement
int MatchDetail = 0;
while (AMatchesToAddLater.ContainsKey(AMatchText + ":::" + MatchDetail.ToString()))
{
MatchDetail++;
}
string key = AMatchText + ":::" + MatchDetail.ToString();
newMatch = AMatchDS.AEpMatch.NewRowTyped();
// matchkey will be set properly on save, by sequence
newMatch.EpMatchKey = -1 * (AMatchesToAddLater.Count + 1);
newMatch.MatchText = AMatchText;
AMatchesToAddLater.Add(key, newMatch);
newMatch.Detail = MatchDetail;
newMatch.Action = MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT;
newMatch.RecipientKey = AGiftDetailRow.RecipientKey;
newMatch.RecipientLedgerNumber = AGiftDetailRow.RecipientLedgerNumber;
newMatch.LedgerNumber = AGiftDetailRow.LedgerNumber;
newMatch.DonorKey = AGiftDetailRow.DonorKey;
newMatch.DonorShortName = AGiftDetailRow.DonorShortName;
newMatch.RecipientShortName = AGiftDetailRow.RecipientDescription;
newMatch.MotivationGroupCode = AGiftDetailRow.MotivationGroupCode;
newMatch.MotivationDetailCode = AGiftDetailRow.MotivationDetailCode;
newMatch.GiftCommentOne = AGiftDetailRow.GiftCommentOne;
newMatch.GiftCommentTwo = AGiftDetailRow.GiftCommentTwo;
newMatch.GiftCommentThree = AGiftDetailRow.GiftCommentThree;
newMatch.CommentOneType = AGiftDetailRow.CommentOneType;
newMatch.CommentTwoType = AGiftDetailRow.CommentTwoType;
newMatch.CommentThreeType = AGiftDetailRow.CommentThreeType;
newMatch.MailingCode = AGiftDetailRow.MailingCode;
newMatch.CostCentreCode = AGiftDetailRow.CostCentreCode;
newMatch.ChargeFlag = AGiftDetailRow.ChargeFlag;
newMatch.ConfidentialGiftFlag = AGiftDetailRow.ConfidentialGiftFlag;
newMatch.GiftTransactionAmount = AGiftDetailRow.GiftTransactionAmount;
return(newMatch.EpMatchKey);
}
private static Decimal SumAmounts(DataView AGiftDetailViewByTransactionNumber,
Int32 AGiftTransactionNumber)
{
Decimal Result = 0.0m;
DataRowView[] detailsOfGift = AGiftDetailViewByTransactionNumber.FindRows(
new object[] { AGiftTransactionNumber });
foreach (DataRowView rv in detailsOfGift)
{
BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv.Row;
Result += detailrow.GiftTransactionAmount;
}
return(Result);
}
private static void MarkTransactionMatched(
BankImportTDS AMainDS,
BankImportTDSAEpTransactionRow transactionRow,
BankImportTDSAGiftDetailRow giftDetail)
{
giftDetail.AlreadyMatched = true;
if (giftDetail.RecipientDescription.Length == 0)
{
giftDetail.RecipientDescription = giftDetail.MotivationGroupCode + "/" + giftDetail.MotivationDetailCode;
}
transactionRow.MatchAction = Ict.Petra.Shared.MFinance.MFinanceConstants.BANK_STMT_STATUS_MATCHED;
transactionRow.GiftLedgerNumber = giftDetail.LedgerNumber;
transactionRow.GiftBatchNumber = giftDetail.BatchNumber;
transactionRow.GiftTransactionNumber = giftDetail.GiftTransactionNumber;
transactionRow.GiftDetailNumbers = StringHelper.AddCSV(transactionRow.GiftDetailNumbers, giftDetail.DetailNumber.ToString(), ",");
transactionRow.DonorKey = giftDetail.DonorKey;
}
/// <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);
}
private static bool MatchOneDonor(BankImportTDS AMainDS, DataRowView[] AGiftDetailWithoutAmount, DataRowView[] ATransactionsByDonor)
{
// check that the total amount matches
Decimal TotalAmountStatement = 0.0m;
Decimal TotalAmountGiftBatch = 0.0m;
foreach (DataRowView rv in ATransactionsByDonor)
{
BankImportTDSAEpTransactionRow trRow = (BankImportTDSAEpTransactionRow)rv.Row;
TotalAmountStatement += trRow.TransactionAmount;
}
foreach (DataRowView rv in AGiftDetailWithoutAmount)
{
BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv.Row;
TotalAmountGiftBatch += detailrow.GiftAmount;
}
if (TotalAmountGiftBatch != TotalAmountStatement)
{
TLogging.Log("Strange situation, amounts do not match:");
foreach (DataRowView rv in AGiftDetailWithoutAmount)
{
BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv.Row;
TLogging.Log(
" gift detail: " + detailrow.DonorShortName + " " + detailrow.RecipientDescription + " " + detailrow.GiftAmount.ToString());
}
foreach (DataRowView rv in ATransactionsByDonor)
{
BankImportTDSAEpTransactionRow trRow = (BankImportTDSAEpTransactionRow)rv.Row;
TLogging.Log(" transaction: " + trRow.AccountName + " " + trRow.Description + " " + trRow.TransactionAmount.ToString());
}
return(false);
}
bool debug = false;
foreach (DataRowView rv in AGiftDetailWithoutAmount)
{
BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv.Row;
if (detailrow.DonorShortName.Contains("David"))
{
debug = false;
}
}
if (debug)
{
TLogging.Log("does this match? ");
foreach (DataRowView rv in AGiftDetailWithoutAmount)
{
BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv.Row;
TLogging.Log(
" gift detail: " + detailrow.DonorShortName + " " + detailrow.RecipientDescription + " " + detailrow.GiftAmount.ToString());
}
foreach (DataRowView rv in ATransactionsByDonor)
{
BankImportTDSAEpTransactionRow trRow = (BankImportTDSAEpTransactionRow)rv.Row;
TLogging.Log(" transaction: " + trRow.AccountName + " " + trRow.Description + " " + trRow.TransactionAmount.ToString());
}
}
if ((AGiftDetailWithoutAmount.Length == 1) && (ATransactionsByDonor.Length == 1))
{
// found exactly one match
MarkTransactionMatched(AMainDS,
(BankImportTDSAEpTransactionRow)ATransactionsByDonor[0].Row,
(BankImportTDSAGiftDetailRow)AGiftDetailWithoutAmount[0].Row);
return(true);
}
else if (AGiftDetailWithoutAmount.Length == ATransactionsByDonor.Length)
{
bool matched = false;
// there is one bank transaction for each gift detail,
// or two bank transactions that go into one gift with 2 details;
// check for amount, and matching words
foreach (DataRowView rv in ATransactionsByDonor)
{
int maxMatchingWords = -1;
bool duplicate = false;
BankImportTDSAGiftDetailRow BestMatch = null;
BankImportTDSAEpTransactionRow trRow = (BankImportTDSAEpTransactionRow)rv.Row;
foreach (DataRowView rv2 in AGiftDetailWithoutAmount)
{
BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv2.Row;
if ((detailrow.GiftAmount == trRow.TransactionAmount) && !detailrow.AlreadyMatched)
{
int matchNumber = MatchingWords(detailrow.RecipientDescription, trRow.Description);
if (matchNumber > 0)
{
if (matchNumber == maxMatchingWords)
{
duplicate = true;
}
else if (matchNumber > maxMatchingWords)
{
maxMatchingWords = matchNumber;
duplicate = false;
BestMatch = detailrow;
}
}
}
}
if ((BestMatch != null) && !duplicate)
{
MarkTransactionMatched(AMainDS, trRow, BestMatch);
matched = true;
}
}
if (matched)
{
return(true);
}
}
else if (ATransactionsByDonor.Length == 1)
{
// one bank transactions with split gifts
foreach (DataRowView rv in AGiftDetailWithoutAmount)
{
BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv.Row;
MarkTransactionMatched(AMainDS,
(BankImportTDSAEpTransactionRow)ATransactionsByDonor[0].Row,
detailrow);
}
return(true);
}
else if (AGiftDetailWithoutAmount.Length == 1)
{
// 3 bank transactions have been merged into one split gift (very special case... 1 Euro per day...)
foreach (DataRowView rv in ATransactionsByDonor)
{
BankImportTDSAEpTransactionRow trRow = (BankImportTDSAEpTransactionRow)rv.Row;
MarkTransactionMatched(AMainDS,
trRow,
(BankImportTDSAGiftDetailRow)AGiftDetailWithoutAmount[0].Row);
}
return(true);
}
else
{
TLogging.Log("TODO: several split gifts, for multiple transactions");
foreach (DataRowView rv in AGiftDetailWithoutAmount)
{
BankImportTDSAGiftDetailRow detailrow = (BankImportTDSAGiftDetailRow)rv.Row;
TLogging.Log(
" gift detail: " + detailrow.DonorShortName + " " + detailrow.RecipientDescription + " " + detailrow.GiftAmount.ToString());
}
foreach (DataRowView rv in ATransactionsByDonor)
{
BankImportTDSAEpTransactionRow trRow = (BankImportTDSAEpTransactionRow)rv.Row;
TLogging.Log(" transaction: " + trRow.AccountName + " " + trRow.Description + " " + trRow.TransactionAmount.ToString());
}
return(false);
}
return(false);
}
/// <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);
}
private static void MarkTransactionMatched(
BankImportTDS AMainDS,
BankImportTDSAEpTransactionRow transactionRow,
BankImportTDSAGiftDetailRow giftDetail)
{
giftDetail.AlreadyMatched = true;
if (giftDetail.RecipientDescription.Length == 0)
{
giftDetail.RecipientDescription = giftDetail.MotivationGroupCode + "/" + giftDetail.MotivationDetailCode;
}
transactionRow.MatchAction = Ict.Petra.Shared.MFinance.MFinanceConstants.BANK_STMT_STATUS_MATCHED;
transactionRow.GiftLedgerNumber = giftDetail.LedgerNumber;
transactionRow.GiftBatchNumber = giftDetail.BatchNumber;
transactionRow.GiftTransactionNumber = giftDetail.GiftTransactionNumber;
transactionRow.GiftDetailNumbers = StringHelper.AddCSV(transactionRow.GiftDetailNumbers, giftDetail.DetailNumber.ToString(), ",");
transactionRow.DonorKey = giftDetail.DonorKey;
}
/// add new matches, and modify existing matches
private static Int32 UpdateMatches(
BankImportTDS AMatchDS,
BankImportTDSAGiftDetailRow AGiftDetailRow,
string AMatchText,
int ADetailNr,
SortedList <string, AEpMatchRow>AMatchesByText,
SortedList <string, AEpMatchRow>AMatchesToAddLater)
{
AEpMatchRow newMatch = null;
if (AMatchesByText.ContainsKey(AMatchText + ":::" + ADetailNr.ToString()))
{
newMatch = AMatchesByText[AMatchText + ":::" + ADetailNr.ToString()];
}
else
{
// we might have added such a match for the current statement
int MatchDetail = 0;
while (AMatchesToAddLater.ContainsKey(AMatchText + ":::" + MatchDetail.ToString())
|| AMatchesByText.ContainsKey(AMatchText + ":::" + MatchDetail.ToString()))
{
MatchDetail++;
}
string key = AMatchText + ":::" + MatchDetail.ToString();
newMatch = AMatchDS.AEpMatch.NewRowTyped();
// matchkey will be set properly on save, by sequence
newMatch.EpMatchKey = -1 * (AMatchesToAddLater.Count + 1);
newMatch.MatchText = AMatchText;
AMatchesToAddLater.Add(key, newMatch);
newMatch.Detail = MatchDetail;
}
newMatch.Action = MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT;
newMatch.RecipientKey = AGiftDetailRow.RecipientKey;
newMatch.RecipientLedgerNumber = AGiftDetailRow.RecipientLedgerNumber;
newMatch.LedgerNumber = AGiftDetailRow.LedgerNumber;
newMatch.DonorKey = AGiftDetailRow.DonorKey;
newMatch.DonorShortName = AGiftDetailRow.DonorShortName;
newMatch.RecipientShortName = AGiftDetailRow.RecipientDescription;
newMatch.MotivationGroupCode = AGiftDetailRow.MotivationGroupCode;
newMatch.MotivationDetailCode = AGiftDetailRow.MotivationDetailCode;
newMatch.GiftCommentOne = AGiftDetailRow.GiftCommentOne;
newMatch.GiftCommentTwo = AGiftDetailRow.GiftCommentTwo;
newMatch.GiftCommentThree = AGiftDetailRow.GiftCommentThree;
newMatch.CommentOneType = AGiftDetailRow.CommentOneType;
newMatch.CommentTwoType = AGiftDetailRow.CommentTwoType;
newMatch.CommentThreeType = AGiftDetailRow.CommentThreeType;
newMatch.MailingCode = AGiftDetailRow.MailingCode;
newMatch.CostCentreCode = AGiftDetailRow.CostCentreCode;
newMatch.ChargeFlag = AGiftDetailRow.ChargeFlag;
newMatch.ConfidentialGiftFlag = AGiftDetailRow.ConfidentialGiftFlag;
newMatch.GiftTransactionAmount = AGiftDetailRow.GiftTransactionAmount;
return newMatch.EpMatchKey;
}