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