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