private static void FindDonorKeysByBankAccount(BankImportTDS AMainDS)
{
AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetBankSortCodeDBName() + "," +
BankImportTDSPBankingDetailsTable.GetBankAccountNumberDBName();
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
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;
}
}
transaction.DonorKey = DonorKey;
}
}
private static void CreateMatches(BankImportTDS AMainDS,
AEpStatementRow ACurrentStatement,
Int32 ASelectedGiftBatch, bool APostedBatch)
{
// remove all gifts and giftdetails that don't belong to the selected batch
List <DataRow> ToDelete = new List <DataRow>();
foreach (AGiftDetailRow giftdetail in AMainDS.AGiftDetail.Rows)
{
if (giftdetail.BatchNumber != ASelectedGiftBatch)
{
ToDelete.Add(giftdetail);
}
}
foreach (DataRow del in ToDelete)
{
AMainDS.AGiftDetail.Rows.Remove(del);
}
ToDelete = new List <DataRow>();
foreach (AGiftRow gift in AMainDS.AGift.Rows)
{
if (gift.BatchNumber != ASelectedGiftBatch)
{
ToDelete.Add(gift);
}
}
foreach (DataRow del in ToDelete)
{
AMainDS.AGift.Rows.Remove(del);
}
ToDelete = new List <DataRow>();
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
// delete transactions with negative amount
if (transaction.TransactionAmount < 0)
{
ToDelete.Add(transaction);
}
else
{
transaction.MatchAction = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
}
foreach (DataRow del in ToDelete)
{
AMainDS.AEpTransaction.Rows.Remove(del);
}
FindDonorKeysByBankAccount(AMainDS);
AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetPartnerKeyDBName();
MatchDonorsWithKnownBankaccount(AMainDS);
while (MatchTransactionsToGiftBatch(AMainDS))
{
;
}
if (TLogging.DebugLevel > 0)
{
TLogging.Log("transactions not matched yet:");
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
if (transaction.MatchAction != Ict.Petra.Shared.MFinance.MFinanceConstants.BANK_STMT_STATUS_MATCHED)
{
TLogging.Log(
" " + transaction.DonorKey.ToString() + " " + transaction.AccountName + " --- " + transaction.Description + " " +
transaction.TransactionAmount.ToString());
if (transaction.DonorKey == -1)
{
TLogging.Log(" " + transaction.BankAccountNumber + " " + transaction.BranchCode);
}
}
}
TLogging.Log("gifts not matched yet:");
foreach (BankImportTDSAGiftDetailRow giftdetail in AMainDS.AGiftDetail.Rows)
{
if (!giftdetail.AlreadyMatched)
{
string HasBankDetails = "-";
int BankDetailsIndex = AMainDS.PBankingDetails.DefaultView.Find(giftdetail.DonorKey);
if (BankDetailsIndex != -1)
{
HasBankDetails = "*";
}
TLogging.Log(
" " + HasBankDetails + " " + giftdetail.DonorKey.ToString() + " " + giftdetail.DonorShortName + " --- " +
giftdetail.RecipientDescription + " " + giftdetail.GiftAmount.ToString());
if (BankDetailsIndex != -1)
{
BankImportTDSPBankingDetailsRow bankdetail =
(BankImportTDSPBankingDetailsRow)AMainDS.PBankingDetails.DefaultView[BankDetailsIndex].Row;
TLogging.Log(" " + bankdetail.BankAccountNumber + " " + bankdetail.BankSortCode);
}
}
}
int CountMatched = 0;
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
if (transaction.MatchAction == Ict.Petra.Shared.MFinance.MFinanceConstants.BANK_STMT_STATUS_MATCHED)
{
CountMatched++;
}
}
TLogging.Log("matched: " + CountMatched.ToString() + " of " + AMainDS.AEpTransaction.Rows.Count.ToString());
}
StoreCurrentMatches(AMainDS, ACurrentStatement.BankAccountCode);
}
/// <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 FindDonorKeysByBankAccount(BankImportTDS AMainDS)
{
if (AMainDS.PBankingDetails.Rows.Count == 0)
{
// First collect all IBANs in this statement
List <string> IBANs = new List <string>();
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
if (transaction.Iban.Length > 0)
{
IBANs.Add(transaction.Iban);
}
}
// load all banking details by IBAN into AMainDS
if (IBANs.Count > 0)
{
string sql = "SELECT " + PPartnerBankingDetailsTable.GetPartnerKeyDBName() + " as PartnerKey, " +
"bd.* " +
"FROM " + PBankingDetailsTable.GetTableDBName() + " bd, " + PPartnerBankingDetailsTable.GetTableDBName() + " pbd " +
"WHERE pbd." + PPartnerBankingDetailsTable.GetBankingDetailsKeyDBName() + " = bd." + PBankingDetailsTable.GetBankingDetailsKeyDBName() + " " +
"AND " + BankImportTDSPBankingDetailsTable.GetIbanDBName() + " IN (";
List <OdbcParameter> parameters = new List <OdbcParameter>();
foreach (string iban in IBANs)
{
if (parameters.Count > 0)
{
sql += ",";
}
sql += "?";
OdbcParameter p = new OdbcParameter("IBAN" + parameters.Count.ToString(), OdbcType.VarChar);
p.Value = iban;
parameters.Add(p);
}
sql += ")";
TDataBase db = DBAccess.Connect("FindDonorKeysByBankAccount");
TDBTransaction transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
db.SelectDT(AMainDS.PBankingDetails, sql, transaction, parameters.ToArray());
transaction.Rollback();
db.CloseDBConnection();
}
}
AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetIbanDBName();
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
// find the donor for this transaction, by his IBAN number
Int64 DonorKey = GetDonorByIBAN(AMainDS, transaction.Iban);
if (transaction.Iban.Length == 0)
{
// useful for NUnit testing for csv import: partnerkey in description
try
{
DonorKey = Convert.ToInt64(transaction.Description);
}
catch (Exception)
{
DonorKey = -1;
}
}
transaction.DonorKey = DonorKey;
}
}
