Example #1
0
        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;
            }
        }
Example #2
0
        /// <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);
        }