private static Int64 GetDonorByBankAccountNumber(BankImportTDS AMainDS, string ABankSortCode, string ABankAccountNumber)
{
if (Regex.IsMatch(ABankAccountNumber, "^[A-Z]") && (ABankAccountNumber.Length > 2) && (ABankAccountNumber.Substring(0, 2) == "DE"))
{
// TODO search for IBAN / BIC instead of bank sort code and account number
// For the moment, we try to assume sort code and account number
// it might be wrong, but then we would not find a donor anyway.
// we should definitely not store these calculated numbers
// perhaps do validation against https://kontocheck.solidcharity.com
string IBAN = ABankAccountNumber;
ABankSortCode = IBAN.Substring(4, 8);
ABankAccountNumber = IBAN.Substring(12).TrimStart(new char[] { '0' });
// TLogging.Log("IBAN " + IBAN + " converted to sort code " + ABankSortCode + " and account number + " + ABankAccountNumber);
}
DataRowView[] bankingDetails = AMainDS.PBankingDetails.DefaultView.FindRows(new object[] { ABankSortCode, ABankAccountNumber });
if (bankingDetails.Length > 0)
{
if (bankingDetails.Length > 1)
{
TLogging.Log("Warning: 2 people own the same bank account " + ABankSortCode + " " + ABankAccountNumber);
}
// TODO: just return the first partner key; usually not 2 people owning the same bank account donate at the same time???
BankImportTDSPBankingDetailsRow row = (BankImportTDSPBankingDetailsRow)bankingDetails[0].Row;
return(row.PartnerKey);
}
return(-1);
}
public static bool DropBankStatement(Int32 AEpStatementKey)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
BankImportTDS MainDS = new BankImportTDS();
AEpStatementAccess.LoadByPrimaryKey(MainDS, AEpStatementKey, Transaction);
AEpTransactionAccess.LoadViaAEpStatement(MainDS, AEpStatementKey, Transaction);
DBAccess.GDBAccessObj.RollbackTransaction();
foreach (AEpStatementRow stmtRow in MainDS.AEpStatement.Rows)
{
stmtRow.Delete();
}
foreach (AEpTransactionRow transactionRow in MainDS.AEpTransaction.Rows)
{
transactionRow.Delete();
}
MainDS.ThrowAwayAfterSubmitChanges = true;
try
{
BankImportTDSAccess.SubmitChanges(MainDS);
return(true);
}
catch (Exception)
{
return(false);
}
}
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;
}
}
/// <summary>
/// train with imported bank statements and existing gift batches
/// </summary>
public static void Train(AEpStatementTable AStatements)
{
int stmtCounter = 0;
// go through all statements in the dataset, and find gift matches for those days
foreach (AEpStatementRow stmt in AStatements.Rows)
{
TLogging.LogAtLevel(1,
"Training Statement " + stmt.StatementKey.ToString() + " " + stmt.Date.ToShortDateString() + " " + stmt.Filename);
stmtCounter++;
if (TProgressTracker.GetCurrentState(DomainManager.GClientID.ToString()).CancelJob == true)
{
TProgressTracker.FinishJob(DomainManager.GClientID.ToString());
return;
}
// first stage: collect historic matches from database:
// go through each transaction of the statement,
// and see if you can find a donation on that date with the same amount from the same bank account
// store this as a match
TLogging.LogAtLevel(1, "loading data ...");
BankImportTDS MainDS = LoadData(stmt.LedgerNumber, stmt.StatementKey);
// Get all gifts at given date
TLogging.LogAtLevel(1, "get gifts by date ...");
List <int> GiftBatchNumbers;
GetGiftsByDate(stmt.LedgerNumber, MainDS, stmt.Date, stmt.BankAccountCode, out GiftBatchNumbers);
int SelectedGiftBatch = -1;
if (GiftBatchNumbers.Count > 0)
{
TLogging.LogAtLevel(1, "Found gift batches: " + GiftBatchNumbers.Count.ToString());
foreach (int i in GiftBatchNumbers)
{
TLogging.LogAtLevel(1, " " + i.ToString());
}
SelectedGiftBatch = FindGiftBatch(MainDS, stmt);
TLogging.LogAtLevel(1, " selected gift batch: " + SelectedGiftBatch.ToString());
}
if (SelectedGiftBatch == -1)
{
// cannot find the posted gift batch without any doubt
continue;
}
CreateMatches(MainDS, stmt, SelectedGiftBatch, true);
}
}
/// 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);
}
public static TSubmitChangesResult StoreNewBankStatement(BankImportTDS AStatementAndTransactionsDS,
out Int32 AFirstStatementKey)
{
string MyClientID = DomainManager.GClientID.ToString();
AFirstStatementKey = -1;
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Processing new bank statements"),
AStatementAndTransactionsDS.AEpStatement.Rows.Count + 1);
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Saving to database"),
0);
try
{
// Must not throw away the changes because we need the correct statement keys
AStatementAndTransactionsDS.DontThrowAwayAfterSubmitChanges = true;
BankImportTDSAccess.SubmitChanges(AStatementAndTransactionsDS);
AFirstStatementKey = -1;
if (AStatementAndTransactionsDS != null)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("starting to train"),
1);
AFirstStatementKey = AStatementAndTransactionsDS.AEpStatement[0].StatementKey;
// search for already posted gift batches, and do the matching for these imported statements
TBankImportMatching.Train(AStatementAndTransactionsDS.AEpStatement);
}
TProgressTracker.FinishJob(MyClientID);
}
catch (Exception ex)
{
TLogging.Log(ex.ToString());
TProgressTracker.CancelJob(MyClientID);
return(TSubmitChangesResult.scrError);
}
return(TSubmitChangesResult.scrOK);
}
/// <summary>
/// Program entry point.
/// </summary>
private static void Main(string[] args)
{
// need to call with config file as parameter: -C:/home/USERNAME/etc/PetraServerConsole.config
TPetraServerConnector.Connect();
TBankStatementImport import = new TBankStatementImport();
BankImportTDS StatementAndTransactionsDS = import.ImportBankStatementNonInteractive(
TAppSettingsManager.GetInt32("ledger"),
TAppSettingsManager.GetValue("bankaccount"),
TAppSettingsManager.GetValue("file"));
Int32 AFirstStatementKey;
TBankImportWebConnector.StoreNewBankStatement(StatementAndTransactionsDS, out AFirstStatementKey);
TPetraServerConnector.Disconnect();
}
private static Int64 GetDonorByIBAN(BankImportTDS AMainDS, string AIBAN)
{
DataRowView[] bankingDetails = AMainDS.PBankingDetails.DefaultView.FindRows(new object[] { AIBAN });
if (bankingDetails.Length > 0)
{
if (bankingDetails.Length > 1)
{
TLogging.Log("Warning: 2 people own the same bank account " + AIBAN);
}
// TODO: just return the first partner key; usually not 2 people owning the same bank account donate at the same time???
BankImportTDSPBankingDetailsRow row = (BankImportTDSPBankingDetailsRow)bankingDetails[0].Row;
return(row.PartnerKey);
}
return(-1);
}
public static bool CommitMatches(BankImportTDS AMainDS)
{
if (AMainDS == null)
{
return(false);
}
AMainDS.ThrowAwayAfterSubmitChanges = true;
try
{
BankImportTDSAccess.SubmitChanges(AMainDS);
return(true);
}
catch (Exception)
{
return(false);
}
}
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;
}
private static BankImportTDS LoadData(Int32 ALedgerNumber, Int32 AStatementKey)
{
TDBTransaction dbtransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
BankImportTDS MatchDS = new BankImportTDS();
// TODO: would it help not to load all? use a_recent_match_d?
AEpMatchAccess.LoadViaALedger(MatchDS, ALedgerNumber, dbtransaction);
TLogging.LogAtLevel(1, "loaded " + MatchDS.AEpMatch.Rows.Count.ToString() + " a_ep_match rows");
AEpTransactionAccess.LoadViaAEpStatement(MatchDS, AStatementKey, dbtransaction);
DBAccess.GDBAccessObj.RollbackTransaction();
MatchDS.AEpMatch.AcceptChanges();
MatchDS.AEpTransaction.AcceptChanges();
return(MatchDS);
}
public static bool CommitMatches(BankImportTDS AMainDS)
{
if (AMainDS == null)
{
return false;
}
AMainDS.ThrowAwayAfterSubmitChanges = true;
try
{
BankImportTDSAccess.SubmitChanges(AMainDS);
return true;
}
catch (Exception e)
{
TLogging.Log("Bankimport, CommitMatches: " + e.ToString());
return false;
}
}
/// <summary>
/// import the data of a CSV file
/// </summary>
/// <param name="ALedgerNumber">the current ledger number</param>
/// <param name="ABankAccountCode">the bank account against which the statement should be stored</param>
/// <param name="ABankStatementFilename"></param>
/// <param name="ACSVContent"></param>
/// <param name="ASeparator"></param>
/// <param name="ADateFormat">DMY or MDY</param>
/// <param name="ANumberFormat">European or American</param>
/// <param name="ACurrencyCode">eg. EUR</param>
/// <param name="AColumnMeaning"></param>
/// <param name="AStartAfterLine">can be empty, otherwise only the lines after the line matching AStartAfterLine will be parsed</param>
/// <param name="AStatementKey">this returns the first key of a statement that was imported. depending on the implementation, several statements can be created from one file</param>
/// <param name="AVerificationResult"></param>
public static bool ImportBankStatement(
Int32 ALedgerNumber,
string ABankAccountCode,
string ABankStatementFilename,
string ACSVContent,
string ASeparator,
string ADateFormat,
string ANumberFormat,
string ACurrencyCode,
string AColumnMeaning,
string AStartAfterLine,
out Int32 AStatementKey,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = new TVerificationResultCollection();
BankImportTDS MainDS = ImportBankStatementHelper(
ALedgerNumber,
ABankAccountCode,
ASeparator,
ADateFormat,
ANumberFormat,
ACurrencyCode,
AColumnMeaning,
AStartAfterLine,
ABankStatementFilename,
ACSVContent);
if (MainDS != null)
{
if (TBankStatementImport.StoreNewBankStatement(
MainDS,
out AStatementKey) == TSubmitChangesResult.scrOK)
{
return(AStatementKey != -1);
}
}
AStatementKey = -1;
return(false);
}
/// <summary>
/// match imported transactions from bank statement to an existing gift batch;
/// this method is only for donors that can be identified by their bank account
/// </summary>
private static void MatchDonorsWithKnownBankaccount(BankImportTDS AMainDS)
{
DataView GiftDetailWithoutAmountView = new DataView(AMainDS.AGiftDetail,
string.Empty,
BankImportTDSAGiftDetailTable.GetDonorKeyDBName() + "," +
BankImportTDSAGiftDetailTable.GetAlreadyMatchedDBName(),
DataViewRowState.CurrentRows);
DataView TransactionsByDonorView = new DataView(AMainDS.AEpTransaction,
string.Empty,
BankImportTDSAEpTransactionTable.GetDonorKeyDBName() + "," +
BankImportTDSAEpTransactionTable.GetMatchActionDBName(),
DataViewRowState.CurrentRows);
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
if ((transaction.DonorKey != -1) &&
(transaction.MatchAction == MFinanceConstants.BANK_STMT_STATUS_UNMATCHED))
{
// get all gifts of this donor, and all bank statement transactions
DataRowView[] GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows(
new object[] { transaction.DonorKey, false });
DataRowView[] TransactionsByDonor = TransactionsByDonorView.FindRows(
new object[] { transaction.DonorKey, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED });
while (MatchOneDonor(AMainDS, GiftDetailWithoutAmount, TransactionsByDonor))
{
GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows(
new object[] { transaction.DonorKey, false });
TransactionsByDonor = TransactionsByDonorView.FindRows(
new object[] { transaction.DonorKey, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED });
}
}
}
}
public static Int32 CreateGLBatch(BankImportTDS AMainDS,
Int32 ALedgerNumber,
Int32 AStatementKey,
Int32 AGLBatchNumber,
out TVerificationResultCollection AVerificationResult)
{
AMainDS.AEpTransaction.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpTransactionTable.GetStatementKeyDBName(),
AStatementKey);
AMainDS.AEpStatement.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpStatementTable.GetStatementKeyDBName(),
AStatementKey);
AEpStatementRow stmt = (AEpStatementRow)AMainDS.AEpStatement.DefaultView[0].Row;
AVerificationResult = null;
Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
if (!TFinancialYear.IsValidPostingPeriod(ALedgerNumber, stmt.Date, out DateEffectivePeriodNumber, out DateEffectiveYearNumber,
Transaction))
{
string msg = String.Format(Catalog.GetString("Cannot create a GL batch for date {0} since it is not in an open period of the ledger."),
stmt.Date.ToShortDateString());
TLogging.Log(msg);
AVerificationResult = new TVerificationResultCollection();
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating GL Batch"), msg, TResultSeverity.Resv_Critical));
DBAccess.GDBAccessObj.RollbackTransaction();
return(-1);
}
Int32 BatchYear, BatchPeriod;
// if DateEffective is outside the range of open periods, use the most fitting date
DateTime DateEffective = stmt.Date;
TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref DateEffective, out BatchYear, out BatchPeriod, Transaction, true);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
DBAccess.GDBAccessObj.RollbackTransaction();
GLBatchTDS GLDS = TGLTransactionWebConnector.CreateABatch(ALedgerNumber);
ABatchRow glbatchRow = GLDS.ABatch[0];
glbatchRow.BatchPeriod = BatchPeriod;
glbatchRow.DateEffective = DateEffective;
glbatchRow.BatchDescription = String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString());
decimal HashTotal = 0.0M;
decimal DebitTotal = 0.0M;
decimal CreditTotal = 0.0M;
// TODO: support several journals
// TODO: support several currencies, support other currencies than the base currency
AJournalRow gljournalRow = GLDS.AJournal.NewRowTyped();
gljournalRow.LedgerNumber = glbatchRow.LedgerNumber;
gljournalRow.BatchNumber = glbatchRow.BatchNumber;
gljournalRow.JournalNumber = glbatchRow.LastJournal + 1;
gljournalRow.TransactionCurrency = LedgerTable[0].BaseCurrency;
glbatchRow.LastJournal++;
gljournalRow.JournalPeriod = glbatchRow.BatchPeriod;
gljournalRow.DateEffective = glbatchRow.DateEffective;
gljournalRow.JournalDescription = glbatchRow.BatchDescription;
gljournalRow.SubSystemCode = CommonAccountingSubSystemsEnum.GL.ToString();
gljournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString();
gljournalRow.ExchangeRateToBase = 1.0m;
GLDS.AJournal.Rows.Add(gljournalRow);
foreach (DataRowView dv in AMainDS.AEpTransaction.DefaultView)
{
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataView v = AMainDS.AEpMatch.DefaultView;
v.RowFilter = AEpMatchTable.GetActionDBName() + " = '" + MFinanceConstants.BANK_STMT_STATUS_MATCHED_GL + "' and " +
AEpMatchTable.GetMatchTextDBName() + " = '" + transactionRow.MatchText + "'";
if (v.Count > 0)
{
AEpMatchRow match = (AEpMatchRow)v[0].Row;
ATransactionRow trans = GLDS.ATransaction.NewRowTyped();
trans.LedgerNumber = glbatchRow.LedgerNumber;
trans.BatchNumber = glbatchRow.BatchNumber;
trans.JournalNumber = gljournalRow.JournalNumber;
trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1;
trans.AccountCode = match.AccountCode;
trans.CostCentreCode = match.CostCentreCode;
trans.Reference = match.Reference;
trans.Narrative = match.Narrative;
trans.TransactionDate = transactionRow.DateEffective;
if (transactionRow.TransactionAmount < 0)
{
trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount;
trans.TransactionAmount = -1 * transactionRow.TransactionAmount;
trans.DebitCreditIndicator = true;
DebitTotal += trans.AmountInBaseCurrency;
}
else
{
trans.AmountInBaseCurrency = transactionRow.TransactionAmount;
trans.TransactionAmount = transactionRow.TransactionAmount;
trans.DebitCreditIndicator = false;
CreditTotal += trans.AmountInBaseCurrency;
}
GLDS.ATransaction.Rows.Add(trans);
gljournalRow.LastTransactionNumber++;
// add one transaction for the bank as well
trans = GLDS.ATransaction.NewRowTyped();
trans.LedgerNumber = glbatchRow.LedgerNumber;
trans.BatchNumber = glbatchRow.BatchNumber;
trans.JournalNumber = gljournalRow.JournalNumber;
trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1;
trans.AccountCode = stmt.BankAccountCode;
trans.CostCentreCode = TLedgerInfo.GetStandardCostCentre(ALedgerNumber);
trans.Reference = match.Reference;
trans.Narrative = match.Narrative;
trans.TransactionDate = transactionRow.DateEffective;
if (transactionRow.TransactionAmount < 0)
{
trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount;
trans.TransactionAmount = -1 * transactionRow.TransactionAmount;
trans.DebitCreditIndicator = false;
CreditTotal += trans.AmountInBaseCurrency;
}
else
{
trans.AmountInBaseCurrency = transactionRow.TransactionAmount;
trans.TransactionAmount = transactionRow.TransactionAmount;
trans.DebitCreditIndicator = true;
DebitTotal += trans.AmountInBaseCurrency;
}
GLDS.ATransaction.Rows.Add(trans);
gljournalRow.LastTransactionNumber++;
}
}
gljournalRow.JournalDebitTotal = DebitTotal;
gljournalRow.JournalCreditTotal = CreditTotal;
glbatchRow.BatchDebitTotal = DebitTotal;
glbatchRow.BatchCreditTotal = CreditTotal;
glbatchRow.BatchControlTotal = HashTotal;
TVerificationResultCollection VerificationResult;
TSubmitChangesResult result = TGLTransactionWebConnector.SaveGLBatchTDS(ref GLDS,
out VerificationResult);
if (result == TSubmitChangesResult.scrOK)
{
return(glbatchRow.BatchNumber);
}
TLogging.Log("Problems storing GL Batch");
return(-1);
}
public static Int32 CreateGiftBatch(
Int32 ALedgerNumber,
Int32 AStatementKey,
Int32 AGiftBatchNumber,
out TVerificationResultCollection AVerificationResult)
{
BankImportTDS MainDS = GetBankStatementTransactionsAndMatches(AStatementKey, ALedgerNumber);
string MyClientID = DomainManager.GClientID.ToString();
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Creating gift batch"),
MainDS.AEpTransaction.DefaultView.Count + 10);
AVerificationResult = new TVerificationResultCollection();
MainDS.AEpTransaction.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpTransactionTable.GetStatementKeyDBName(),
AStatementKey);
MainDS.AEpStatement.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpStatementTable.GetStatementKeyDBName(),
AStatementKey);
AEpStatementRow stmt = (AEpStatementRow)MainDS.AEpStatement.DefaultView[0].Row;
// TODO: optional: use the preselected gift batch, AGiftBatchNumber
Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber;
DateTime BatchDateEffective = stmt.Date;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
if (!TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref BatchDateEffective, out DateEffectiveYearNumber,
out DateEffectivePeriodNumber,
Transaction, true))
{
// just use the latest possible date
string msg =
String.Format(Catalog.GetString("Date {0} is not in an open period of the ledger, using date {1} instead for the gift batch."),
stmt.Date.ToShortDateString(),
BatchDateEffective.ToShortDateString());
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Info));
}
ACostCentreAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(MainDS, ALedgerNumber, Transaction);
MainDS.AEpMatch.DefaultView.Sort =
AEpMatchTable.GetActionDBName() + ", " +
AEpMatchTable.GetMatchTextDBName();
if (MainDS.AEpTransaction.DefaultView.Count == 0)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Creating Gift Batch"),
String.Format(Catalog.GetString("There are no transactions for statement #{0}."), AStatementKey),
TResultSeverity.Resv_Info));
return(-1);
}
foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView)
{
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] {
MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT,
transactionRow.MatchText
});
if (matches.Length > 0)
{
AEpMatchRow match = (AEpMatchRow)matches[0].Row;
if (match.IsDonorKeyNull() || (match.DonorKey == 0))
{
string msg =
String.Format(Catalog.GetString("Cannot create a gift for transaction {0} since there is no valid donor."),
transactionRow.Description);
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating Gift Batch"), msg, TResultSeverity.Resv_Critical));
DBAccess.GDBAccessObj.RollbackTransaction();
return(-1);
}
}
}
string MatchedGiftReference = stmt.Filename;
if (!stmt.IsBankAccountKeyNull())
{
string sqlGetBankSortCode =
"SELECT bank.p_branch_code_c " +
"FROM PUB_p_banking_details details, PUB_p_bank bank " +
"WHERE details.p_banking_details_key_i = ?" +
"AND details.p_bank_key_n = bank.p_partner_key_n";
OdbcParameter param = new OdbcParameter("detailkey", OdbcType.Int);
param.Value = stmt.BankAccountKey;
PBankTable bankTable = new PBankTable();
DBAccess.GDBAccessObj.SelectDT(bankTable, sqlGetBankSortCode, Transaction, new OdbcParameter[] { param }, 0, 0);
MatchedGiftReference = bankTable[0].BranchCode + " " + stmt.Date.Day.ToString();
}
DBAccess.GDBAccessObj.RollbackTransaction();
GiftBatchTDS GiftDS = TGiftTransactionWebConnector.CreateAGiftBatch(
ALedgerNumber,
BatchDateEffective,
String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString()));
AGiftBatchRow giftbatchRow = GiftDS.AGiftBatch[0];
giftbatchRow.BankAccountCode = stmt.BankAccountCode;
decimal HashTotal = 0.0M;
MainDS.AEpTransaction.DefaultView.Sort =
AEpTransactionTable.GetNumberOnPaperStatementDBName();
MainDS.AEpMatch.DefaultView.RowFilter = String.Empty;
MainDS.AEpMatch.DefaultView.Sort =
AEpMatchTable.GetActionDBName() + ", " +
AEpMatchTable.GetMatchTextDBName();
int counter = 5;
foreach (DataRowView dv in MainDS.AEpTransaction.DefaultView)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Preparing the gifts"),
counter++);
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataRowView[] matches = MainDS.AEpMatch.DefaultView.FindRows(new object[] {
MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT,
transactionRow.MatchText
});
if (matches.Length > 0)
{
AEpMatchRow match = (AEpMatchRow)matches[0].Row;
AGiftRow gift = GiftDS.AGift.NewRowTyped();
gift.LedgerNumber = giftbatchRow.LedgerNumber;
gift.BatchNumber = giftbatchRow.BatchNumber;
gift.GiftTransactionNumber = giftbatchRow.LastGiftNumber + 1;
gift.DonorKey = match.DonorKey;
gift.DateEntered = transactionRow.DateEffective;
gift.Reference = MatchedGiftReference;
GiftDS.AGift.Rows.Add(gift);
giftbatchRow.LastGiftNumber++;
foreach (DataRowView r in matches)
{
match = (AEpMatchRow)r.Row;
AGiftDetailRow detail = GiftDS.AGiftDetail.NewRowTyped();
detail.LedgerNumber = gift.LedgerNumber;
detail.BatchNumber = gift.BatchNumber;
detail.GiftTransactionNumber = gift.GiftTransactionNumber;
detail.DetailNumber = gift.LastDetailNumber + 1;
gift.LastDetailNumber++;
detail.GiftTransactionAmount = match.GiftTransactionAmount;
detail.GiftAmount = match.GiftTransactionAmount;
HashTotal += match.GiftTransactionAmount;
detail.MotivationGroupCode = match.MotivationGroupCode;
detail.MotivationDetailCode = match.MotivationDetailCode;
// do not use the description in comment one, because that could show up on the gift receipt???
// detail.GiftCommentOne = transactionRow.Description;
detail.CommentOneType = MFinanceConstants.GIFT_COMMENT_TYPE_BOTH;
detail.CostCentreCode = match.CostCentreCode;
detail.RecipientKey = match.RecipientKey;
detail.RecipientLedgerNumber = match.RecipientLedgerNumber;
AMotivationDetailRow motivation = (AMotivationDetailRow)MainDS.AMotivationDetail.Rows.Find(
new object[] { ALedgerNumber, detail.MotivationGroupCode, detail.MotivationDetailCode });
if (motivation == null)
{
AVerificationResult.Add(new TVerificationResult(
String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description),
String.Format(Catalog.GetString("Cannot find motivation group '{0}' and motivation detail '{1}'"),
detail.MotivationGroupCode, detail.MotivationDetailCode),
TResultSeverity.Resv_Critical));
}
if (detail.CostCentreCode.Length == 0)
{
// try to retrieve the current costcentre for this recipient
if (detail.RecipientKey != 0)
{
detail.RecipientLedgerNumber = TGiftTransactionWebConnector.GetRecipientFundNumber(detail.RecipientKey);
detail.CostCentreCode = TGiftTransactionWebConnector.IdentifyPartnerCostCentre(detail.LedgerNumber,
detail.RecipientLedgerNumber);
}
else
{
if (motivation != null)
{
detail.CostCentreCode = motivation.CostCentreCode;
}
}
}
// check for active cost centre
ACostCentreRow costcentre = (ACostCentreRow)MainDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, detail.CostCentreCode });
if ((costcentre == null) || !costcentre.CostCentreActiveFlag)
{
AVerificationResult.Add(new TVerificationResult(
String.Format(Catalog.GetString("creating gift for match {0}"), transactionRow.Description),
Catalog.GetString("Invalid or inactive cost centre"),
TResultSeverity.Resv_Critical));
}
GiftDS.AGiftDetail.Rows.Add(detail);
}
}
}
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Submit to database"),
counter++);
if (AVerificationResult.HasCriticalErrors)
{
return(-1);
}
giftbatchRow.HashTotal = HashTotal;
giftbatchRow.BatchTotal = HashTotal;
// do not overwrite the parameter, because there might be the hint for a different gift batch date
TVerificationResultCollection VerificationResultSubmitChanges;
TSubmitChangesResult result = TGiftTransactionWebConnector.SaveGiftBatchTDS(ref GiftDS,
out VerificationResultSubmitChanges);
TProgressTracker.FinishJob(MyClientID);
if (result == TSubmitChangesResult.scrOK)
{
return(giftbatchRow.BatchNumber);
}
return(-1);
}
public static BankImportTDS GetBankStatementTransactionsAndMatches(Int32 AStatementKey, Int32 ALedgerNumber)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
BankImportTDS ResultDataset = new BankImportTDS();
string MyClientID = DomainManager.GClientID.ToString();
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Load Bank Statement"),
100.0m);
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("loading statement"),
0);
try
{
AEpStatementAccess.LoadByPrimaryKey(ResultDataset, AStatementKey, Transaction);
if (ResultDataset.AEpStatement[0].BankAccountCode.Length == 0)
{
throw new Exception("Loading Bank Statement: Bank Account must not be empty");
}
ACostCentreAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction);
AEpTransactionAccess.LoadViaAEpStatement(ResultDataset, AStatementKey, Transaction);
BankImportTDS TempDataset = new BankImportTDS();
AEpTransactionAccess.LoadViaAEpStatement(TempDataset, AStatementKey, Transaction);
AEpMatchAccess.LoadViaALedger(TempDataset, ResultDataset.AEpStatement[0].LedgerNumber, Transaction);
// load all bankingdetails and partner shortnames related to this statement
string sqlLoadPartnerByBankAccount =
"SELECT DISTINCT p.p_partner_key_n AS PartnerKey, " +
"p.p_partner_short_name_c AS ShortName, " +
"t.p_branch_code_c AS BranchCode, " +
"t.a_bank_account_number_c AS BankAccountNumber " +
"FROM PUB_a_ep_transaction t, PUB_p_banking_details bd, PUB_p_bank b, PUB_p_partner_banking_details pbd, PUB_p_partner p " +
"WHERE t.a_statement_key_i = " + AStatementKey.ToString() + " " +
"AND bd.p_bank_account_number_c = t.a_bank_account_number_c " +
"AND b.p_partner_key_n = bd.p_bank_key_n " +
"AND b.p_branch_code_c = t.p_branch_code_c " +
"AND pbd.p_banking_details_key_i = bd.p_banking_details_key_i " +
"AND p.p_partner_key_n = pbd.p_partner_key_n";
DataTable PartnerByBankAccount = DBAccess.GDBAccessObj.SelectDT(sqlLoadPartnerByBankAccount, "partnerByBankAccount", Transaction);
PartnerByBankAccount.DefaultView.Sort = "BranchCode, BankAccountNumber";
// get all recipients that have been merged
string sqlGetMergedRecipients =
string.Format(
"SELECT DISTINCT p.p_partner_key_n AS PartnerKey, p.p_status_code_c AS StatusCode FROM PUB_a_ep_match m, PUB_p_partner p " +
"WHERE (m.p_recipient_key_n = p.p_partner_key_n OR m.p_donor_key_n = p.p_partner_key_n) AND p.p_status_code_c = '{0}'",
MPartnerConstants.PARTNERSTATUS_MERGED);
DataTable MergedPartners = DBAccess.GDBAccessObj.SelectDT(sqlGetMergedRecipients, "mergedPartners", Transaction);
MergedPartners.DefaultView.Sort = "PartnerKey";
DBAccess.GDBAccessObj.RollbackTransaction();
string BankAccountCode = ResultDataset.AEpStatement[0].BankAccountCode;
TempDataset.AEpMatch.DefaultView.Sort = AEpMatchTable.GetMatchTextDBName();
SortedList <string, AEpMatchRow> MatchesToAddLater = new SortedList <string, AEpMatchRow>();
int count = 0;
// load the matches or create new matches
foreach (BankImportTDSAEpTransactionRow row in ResultDataset.AEpTransaction.Rows)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("finding matches") +
" " + count + "/" + ResultDataset.AEpTransaction.Rows.Count.ToString(),
10.0m + (count * 80.0m / ResultDataset.AEpTransaction.Rows.Count));
count++;
BankImportTDSAEpTransactionRow tempTransactionRow =
(BankImportTDSAEpTransactionRow)TempDataset.AEpTransaction.Rows.Find(
new object[] {
row.StatementKey,
row.Order,
row.DetailKey
});
// find a match with the same match text, or create a new one
if (row.IsMatchTextNull() || (row.MatchText.Length == 0) || !row.MatchText.StartsWith(BankAccountCode))
{
row.MatchText = TBankImportMatching.CalculateMatchText(BankAccountCode, row);
tempTransactionRow.MatchText = row.MatchText;
}
DataRowView[] matches = TempDataset.AEpMatch.DefaultView.FindRows(row.MatchText);
if (matches.Length > 0)
{
Decimal sum = 0.0m;
// update the recent date
foreach (DataRowView rv in matches)
{
AEpMatchRow r = (AEpMatchRow)rv.Row;
sum += r.GiftTransactionAmount;
// check if the recipient key is still valid. could be that they have married, and merged into another family record
if ((r.RecipientKey != 0) &&
(MergedPartners.DefaultView.FindRows(r.RecipientKey).Length > 0))
{
TLogging.LogAtLevel(1, "partner has been merged: " + r.RecipientKey.ToString());
r.RecipientKey = 0;
r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
// check if the donor key is still valid. could be that they have married, and merged into another family record
if ((r.DonorKey != 0) &&
(MergedPartners.DefaultView.FindRows(r.DonorKey).Length > 0))
{
TLogging.LogAtLevel(1, "partner has been merged: " + r.DonorKey.ToString());
r.DonorKey = 0;
r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
if (r.RecentMatch < row.DateEffective)
{
r.RecentMatch = row.DateEffective;
}
// do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time
row.MatchAction = r.Action;
if (r.IsDonorKeyNull() || (r.DonorKey <= 0))
{
FindDonorByAccountNumber(r, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber);
}
}
if (sum != row.TransactionAmount)
{
TLogging.Log("we should drop this match since the total is wrong: " + row.Description);
row.MatchAction = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
foreach (DataRowView rv in matches)
{
AEpMatchRow r = (AEpMatchRow)rv.Row;
r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
}
}
else if (!MatchesToAddLater.ContainsKey(row.MatchText))
{
// create new match
AEpMatchRow tempRow = TempDataset.AEpMatch.NewRowTyped(true);
tempRow.EpMatchKey = (TempDataset.AEpMatch.Count + MatchesToAddLater.Count + 1) * -1;
tempRow.Detail = 0;
tempRow.MatchText = row.MatchText;
tempRow.LedgerNumber = ALedgerNumber;
tempRow.GiftTransactionAmount = row.TransactionAmount;
tempRow.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
FindDonorByAccountNumber(tempRow, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber);
#if disabled
// fuzzy search for the partner. only return if unique result
string sql =
"SELECT p_partner_key_n, p_partner_short_name_c FROM p_partner WHERE p_partner_short_name_c LIKE '{0}%' OR p_partner_short_name_c LIKE '{1}%'";
string[] names = row.AccountName.Split(new char[] { ' ' });
if (names.Length > 1)
{
string optionShortName1 = names[0] + ", " + names[1];
string optionShortName2 = names[1] + ", " + names[0];
DataTable partner = DBAccess.GDBAccessObj.SelectDT(String.Format(sql,
optionShortName1,
optionShortName2), "partner", Transaction);
if (partner.Rows.Count == 1)
{
tempRow.DonorKey = Convert.ToInt64(partner.Rows[0][0]);
}
}
#endif
MatchesToAddLater.Add(tempRow.MatchText, tempRow);
// do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time
row.MatchAction = tempRow.Action;
}
}
// for speed reasons, add the new rows after clearing the sort on the view
TempDataset.AEpMatch.DefaultView.Sort = string.Empty;
foreach (AEpMatchRow m in MatchesToAddLater.Values)
{
TempDataset.AEpMatch.Rows.Add(m);
}
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("save matches"),
90.0m);
TempDataset.ThrowAwayAfterSubmitChanges = true;
// only store a_ep_transactions and a_ep_matches, but without additional typed fields (ie MatchAction)
BankImportTDSAccess.SubmitChanges(TempDataset.GetChangesTyped(true));
}
catch (Exception e)
{
TLogging.Log(e.GetType().ToString() + " in BankImport, GetBankStatementTransactionsAndMatches; " + e.Message);
TLogging.Log(e.StackTrace);
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
// drop all matches that do not occur on this statement
ResultDataset.AEpMatch.Clear();
// reloading is faster than deleting all matches that are not needed
string sqlLoadMatchesOfStatement =
"SELECT DISTINCT m.* FROM PUB_a_ep_match m, PUB_a_ep_transaction t WHERE t.a_statement_key_i = ? AND m.a_ledger_number_i = ? AND m.a_match_text_c = t.a_match_text_c";
OdbcParameter param = new OdbcParameter("statementkey", OdbcType.Int);
param.Value = AStatementKey;
OdbcParameter paramLedgerNumber = new OdbcParameter("ledgerNumber", OdbcType.Int);
paramLedgerNumber.Value = ALedgerNumber;
DBAccess.GDBAccessObj.SelectDT(ResultDataset.AEpMatch,
sqlLoadMatchesOfStatement,
null,
new OdbcParameter[] { param, paramLedgerNumber }, -1, -1);
// update the custom field for cost centre name for each match
foreach (BankImportTDSAEpMatchRow row in ResultDataset.AEpMatch.Rows)
{
ACostCentreRow ccRow = (ACostCentreRow)ResultDataset.ACostCentre.Rows.Find(new object[] { row.LedgerNumber, row.CostCentreCode });
if (ccRow != null)
{
row.CostCentreName = ccRow.CostCentreName;
}
}
// remove all rows that we do not need on the client side
ResultDataset.AGiftDetail.Clear();
ResultDataset.AMotivationDetail.Clear();
ResultDataset.ACostCentre.Clear();
ResultDataset.AcceptChanges();
if (TLogging.DebugLevel > 0)
{
int CountMatched = 0;
foreach (BankImportTDSAEpTransactionRow transaction in ResultDataset.AEpTransaction.Rows)
{
if (!transaction.IsMatchActionNull() && (transaction.MatchAction != MFinanceConstants.BANK_STMT_STATUS_UNMATCHED))
{
CountMatched++;
}
}
TLogging.Log(
"Loading bank statement: matched: " + CountMatched.ToString() + " of " + ResultDataset.AEpTransaction.Rows.Count.ToString());
}
TProgressTracker.FinishJob(MyClientID);
return(ResultDataset);
}
/// <summary>
/// this non interactive function can be used from the unit tests
/// </summary>
public BankImportTDS ImportBankStatementNonInteractive(Int32 ALedgerNumber,
string ABankAccountCode,
string ASeparator,
string ADateFormat,
string ANumberFormat,
string AColumnsUsage,
string ABankStatementFilename,
string AStatementData)
{
Int32 FirstTransactionRow = 0;
string DateFormat = (ADateFormat == "MDY" ? "M/d/yyyy" : "d.M.yyyy");
string ThousandsSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "," : ".");
string DecimalSeparator = (ANumberFormat == TDlgSelectCSVSeparator.NUMBERFORMAT_AMERICAN ? "." : ",");
string[] StatementData = AStatementData.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
// skip headers
Int32 lineCounter = FirstTransactionRow;
// TODO: support splitting a file by month?
// at the moment this only works for files that are already split by month
// TODO: check if this statement has already been imported, by the stmt.Filename; delete old statement
BankImportTDS MainDS = new BankImportTDS();
AEpStatementRow stmt = MainDS.AEpStatement.NewRowTyped();
stmt.StatementKey = -1;
// TODO: depending on the path of BankStatementFilename you could determine between several bank accounts
// TODO: BankAccountKey should be NOT NULL. for the moment not time to implement
// stmt.BankAccountKey = Convert.ToInt64(TXMLParser.GetAttribute(RootNode, "BankAccountKey"));
stmt.Filename = ABankStatementFilename;
if (stmt.Filename.Length > AEpStatementTable.GetFilenameLength())
{
// use the last number of characters of the path and filename
stmt.Filename = ABankStatementFilename.Substring(ABankStatementFilename.Length - AEpStatementTable.GetFilenameLength());
}
stmt.LedgerNumber = ALedgerNumber;
stmt.CurrencyCode = string.Empty;
stmt.BankAccountCode = ABankAccountCode;
MainDS.AEpStatement.Rows.Add(stmt);
DateTime latestDate = DateTime.MinValue;
Int32 rowCount = 0;
// TODO would need to allow the user to change the order&meaning of columns
string[] ColumnsUsage = AColumnsUsage.Split(new char[] { ',' });
while (lineCounter < StatementData.Length)
{
string line = StatementData[lineCounter];
lineCounter++;
rowCount++;
AEpTransactionRow row = MainDS.AEpTransaction.NewRowTyped();
row.StatementKey = stmt.StatementKey;
row.Order = rowCount;
row.NumberOnPaperStatement = row.Order;
foreach (string UseAs in ColumnsUsage)
{
string Value = StringHelper.GetNextCSV(ref line, ASeparator);
if (UseAs.ToLower() == "dateeffective")
{
try
{
row.DateEffective = XmlConvert.ToDateTime(Value, DateFormat);
}
catch (Exception)
{
TLogging.Log("Problem with date effective: " + Value + " (Format: " + DateFormat + ")");
}
if (row.DateEffective > latestDate)
{
latestDate = row.DateEffective;
}
}
else if (UseAs.ToLower() == "accountname")
{
row.AccountName = Value;
}
else if (UseAs.ToLower() == "description")
{
// remove everything after DTA; it is not relevant and confused matching
if (Value.IndexOf(" DTA ") > 0)
{
Value = Value.Substring(0, Value.IndexOf(" DTA "));
}
row.Description = Value;
}
else if (UseAs.ToLower() == "amount")
{
if (Value.Contains(" "))
{
// cut off currency code; should have been defined in the data description file, for the whole batch
Value = Value.Substring(0, Value.IndexOf(" ") - 1);
}
Value = Value.Replace(ThousandsSeparator, "");
Value = Value.Replace(DecimalSeparator, ".");
row.TransactionAmount = Convert.ToDecimal(Value, System.Globalization.CultureInfo.InvariantCulture);
}
else if (UseAs.ToLower() == "currency")
{
if (stmt.CurrencyCode == string.Empty)
{
stmt.CurrencyCode = Value.ToUpper();
}
else if (stmt.CurrencyCode != Value.ToUpper())
{
throw new Exception("cannot mix several currencies in the same bank statement file");
}
}
}
// all transactions with positive amount can be donations
if (row.TransactionAmount > 0)
{
row.TransactionTypeCode = MFinanceConstants.BANK_STMT_POTENTIAL_GIFT;
}
MainDS.AEpTransaction.Rows.Add(row);
}
stmt.Date = latestDate;
return MainDS;
}
/// <summary>
/// asks the user to open a csv file and imports the contents according to the config file
/// </summary>
/// <param name="AStatementKey">this returns the first key of a statement that was imported. depending on the implementation, several statements can be created from one file</param>
/// <param name="ALedgerNumber">the current ledger number</param>
/// <param name="ABankAccountCode">the bank account against which the statement should be stored</param>
/// <returns></returns>
public bool ImportBankStatement(out Int32 AStatementKey, Int32 ALedgerNumber, string ABankAccountCode)
{
AStatementKey = -1;
// each time the button btnImportNewStatement is clicked, do a split and move action
SplitFilesAndMove();
ArchiveFilesLastMonth(ALedgerNumber);
OpenFileDialog DialogOpen = new OpenFileDialog();
DialogOpen.Filter = Catalog.GetString("bank statement CAMT (*.xml)|*.xml");
if (TAppSettingsManager.HasValue("BankimportPath" + ALedgerNumber.ToString()))
{
DialogOpen.InitialDirectory = TAppSettingsManager.GetValue("BankimportPath" + ALedgerNumber.ToString());
}
else
{
DialogOpen.RestoreDirectory = true;
}
DialogOpen.Multiselect = true;
DialogOpen.Title = Catalog.GetString("Please select the bank statement to import");
if (DialogOpen.ShowDialog() != DialogResult.OK)
{
return false;
}
BankImportTDS MainDS = new BankImportTDS();
// import several files at once
foreach (string BankStatementFilename in DialogOpen.FileNames)
{
if (!ImportFromFile(BankStatementFilename,
ABankAccountCode,
ref MainDS))
{
return false;
}
}
if (MainDS.AEpStatement.Count > 0)
{
foreach (AEpStatementRow stmt in MainDS.AEpStatement.Rows)
{
MainDS.AEpTransaction.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpTransactionTable.GetStatementKeyDBName(),
stmt.StatementKey);
stmt.LedgerNumber = ALedgerNumber;
}
Thread t = new Thread(() => ProcessStatementsOnServer(MainDS));
using (TProgressDialog dialog = new TProgressDialog(t))
{
if (dialog.ShowDialog() == DialogResult.Cancel)
{
return false;
}
else
{
AStatementKey = FStatementKey;
return FStatementKey != -1;
}
}
}
return false;
}
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);
}
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>
/// match imported transactions from bank statement to an existing gift batch;
/// this method is only for donors that can be identified by their bank account
/// </summary>
private static void MatchDonorsWithKnownBankaccount(BankImportTDS AMainDS)
{
DataView GiftDetailWithoutAmountView = new DataView(AMainDS.AGiftDetail,
string.Empty,
BankImportTDSAGiftDetailTable.GetDonorKeyDBName() + "," +
BankImportTDSAGiftDetailTable.GetAlreadyMatchedDBName(),
DataViewRowState.CurrentRows);
DataView TransactionsByDonorView = new DataView(AMainDS.AEpTransaction,
string.Empty,
BankImportTDSAEpTransactionTable.GetDonorKeyDBName() + "," +
BankImportTDSAEpTransactionTable.GetMatchActionDBName(),
DataViewRowState.CurrentRows);
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
if ((transaction.DonorKey != -1)
&& (transaction.MatchAction == MFinanceConstants.BANK_STMT_STATUS_UNMATCHED))
{
// get all gifts of this donor, and all bank statement transactions
DataRowView[] GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows(
new object[] { transaction.DonorKey, false });
DataRowView[] TransactionsByDonor = TransactionsByDonorView.FindRows(
new object[] { transaction.DonorKey, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED });
while (MatchOneDonor(AMainDS, GiftDetailWithoutAmount, TransactionsByDonor))
{
GiftDetailWithoutAmount = GiftDetailWithoutAmountView.FindRows(
new object[] { transaction.DonorKey, false });
TransactionsByDonor = TransactionsByDonorView.FindRows(
new object[] { transaction.DonorKey, MFinanceConstants.BANK_STMT_STATUS_UNMATCHED });
}
}
}
}
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;
}
private static BankImportTDS LoadData(Int32 ALedgerNumber, Int32 AStatementKey)
{
TDBTransaction dbtransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
BankImportTDS MatchDS = new BankImportTDS();
// TODO: would it help not to load all? use a_recent_match_d?
AEpMatchAccess.LoadViaALedger(MatchDS, ALedgerNumber, dbtransaction);
TLogging.LogAtLevel(1, "loaded " + MatchDS.AEpMatch.Rows.Count.ToString() + " a_ep_match rows");
AEpTransactionAccess.LoadViaAEpStatement(MatchDS, AStatementKey, dbtransaction);
DBAccess.GDBAccessObj.RollbackTransaction();
MatchDS.AEpMatch.AcceptChanges();
MatchDS.AEpTransaction.AcceptChanges();
return MatchDS;
}
/// <summary>
/// return a table with gift details for the given date with donor partner keys and bank account numbers
/// </summary>
private static bool GetGiftsByDate(Int32 ALedgerNumber,
BankImportTDS AMainDS,
DateTime ADateEffective,
string ABankAccountCode,
out List <int>AGiftBatchNumbers)
{
TDBTransaction transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadUncommitted);
// first get all gifts, even those that have no bank account associated
string stmt = TDataBase.ReadSqlFile("BankImport.GetDonationsByDate.sql");
OdbcParameter[] parameters = new OdbcParameter[3];
parameters[0] = new OdbcParameter("ALedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("ADateEffective", OdbcType.Date);
parameters[1].Value = ADateEffective;
parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar);
parameters[2].Value = ABankAccountCode;
DBAccess.GDBAccessObj.SelectDT(AMainDS.AGiftDetail, stmt, transaction, parameters, 0, 0);
// calculate the totals of gifts
AMainDS.AGift.Clear();
AGiftBatchNumbers = new List <int>();
foreach (BankImportTDSAGiftDetailRow giftdetail in AMainDS.AGiftDetail.Rows)
{
BankImportTDSAGiftRow giftRow =
(BankImportTDSAGiftRow)AMainDS.AGift.Rows.Find(new object[] { giftdetail.LedgerNumber, giftdetail.BatchNumber,
giftdetail.GiftTransactionNumber });
if (giftRow == null)
{
giftRow = AMainDS.AGift.NewRowTyped(true);
giftRow.LedgerNumber = giftdetail.LedgerNumber;
giftRow.BatchNumber = giftdetail.BatchNumber;
giftRow.GiftTransactionNumber = giftdetail.GiftTransactionNumber;
giftRow.TotalAmount = 0;
giftRow.DonorKey = giftdetail.DonorKey;
AMainDS.AGift.Rows.Add(giftRow);
}
giftRow.TotalAmount += giftdetail.GiftTransactionAmount;
if (!AGiftBatchNumbers.Contains(giftRow.BatchNumber))
{
AGiftBatchNumbers.Add(giftRow.BatchNumber);
}
}
// get PartnerKey and banking details (most important BankAccountNumber) for all donations on the given date
stmt = TDataBase.ReadSqlFile("BankImport.GetBankAccountByDate.sql");
parameters = new OdbcParameter[2];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("ADateEffective", OdbcType.Date);
parameters[1].Value = ADateEffective;
// TODO ? parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar);
//parameters[2].Value = ABankAccountCode;
// There can be several donors with the same banking details
AMainDS.PBankingDetails.Constraints.Clear();
DBAccess.GDBAccessObj.Select(AMainDS, stmt, AMainDS.PBankingDetails.TableName, transaction, parameters);
DBAccess.GDBAccessObj.RollbackTransaction();
return true;
}
private static Int64 GetDonorByBankAccountNumber(BankImportTDS AMainDS, string ABankSortCode, string ABankAccountNumber)
{
if (Regex.IsMatch(ABankAccountNumber, "^[A-Z]"))
{
// TODO search for IBAN / BIC instead of bank sort code and account number
// For the moment, we try to assume sort code and account number
// it might be wrong, but then we would not find a donor anyway.
// we should definitely not store these calculated numbers
// perhaps do validation against https://kontocheck.solidcharity.com
string IBAN = ABankAccountNumber;
ABankSortCode = IBAN.Substring(4, 8);
ABankAccountNumber = IBAN.Substring(12).TrimStart(new char[] { '0' });
// TLogging.Log("IBAN " + IBAN + " converted to sort code " + ABankSortCode + " and account number + " + ABankAccountNumber);
}
DataRowView[] bankingDetails = AMainDS.PBankingDetails.DefaultView.FindRows(new object[] { ABankSortCode, ABankAccountNumber });
if (bankingDetails.Length > 0)
{
if (bankingDetails.Length > 1)
{
TLogging.Log("Warning: 2 people own the same bank account " + ABankSortCode + " " + ABankAccountNumber);
}
// TODO: just return the first partner key; usually not 2 people owning the same bank account donate at the same time???
BankImportTDSPBankingDetailsRow row = (BankImportTDSPBankingDetailsRow)bankingDetails[0].Row;
return row.PartnerKey;
}
return -1;
}
/// <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);
}
public void TestMultipleGifts()
{
// import the test gift batch, and post it
TGiftImporting importer = new TGiftImporting();
string dirTestData = "../../csharp/ICT/Testing/lib/MFinance/server/BankImport/TestData/";
string testFile = dirTestData + "GiftBatch.csv";
StreamReader sr = new StreamReader(testFile);
string FileContent = sr.ReadToEnd();
sr.Close();
FileContent = FileContent.Replace("2010-09-30", DateTime.Now.Year.ToString("0000") + "-09-30");
Hashtable parameters = new Hashtable();
parameters.Add("Delimiter", ",");
parameters.Add("ALedgerNumber", FLedgerNumber);
parameters.Add("DateFormatString", "yyyy-MM-dd");
parameters.Add("NumberFormat", "American");
parameters.Add("NewLine", Environment.NewLine);
TVerificationResultCollection VerificationResult;
importer.ImportGiftBatches(parameters, FileContent, out VerificationResult);
int BatchNumber = importer.GetLastGiftBatchNumber();
Assert.AreNotEqual(-1, BatchNumber, "Failed to import gift batch: " + VerificationResult.BuildVerificationResultString());
if (!TGiftTransactionWebConnector.PostGiftBatch(FLedgerNumber, BatchNumber, out VerificationResult))
{
Assert.Fail("Gift Batch was not posted: " + VerificationResult.BuildVerificationResultString());
}
// import the test csv file, will already do the training
TBankStatementImport import = new TBankStatementImport();
testFile = dirTestData + "BankStatement.csv";
sr = new StreamReader(testFile);
FileContent = sr.ReadToEnd();
sr.Close();
FileContent = FileContent.Replace("30.09.2010", "30.09." + DateTime.Now.Year.ToString("0000"));
Int32 StatementKey;
BankImportTDS BankImportDS = import.ImportBankStatementNonInteractive(
FLedgerNumber,
"6200",
";",
"DMY",
TDlgSelectCSVSeparator.NUMBERFORMAT_EUROPEAN,
"unused,DateEffective,Description,Amount,Currency",
"BankStatementSeptember.csv",
FileContent);
Assert.AreNotEqual(null, BankImportDS, "valid bank import dataset september");
Assert.AreEqual(TSubmitChangesResult.scrOK, TBankImportWebConnector.StoreNewBankStatement(
BankImportDS,
out StatementKey), "save statement September");
// revert the gift batch, so that the training does not get confused if the test is run again;
// the training needs only one gift batch for that date
Hashtable requestParams = new Hashtable();
requestParams.Add("Function", "ReverseGiftBatch");
requestParams.Add("ALedgerNumber", FLedgerNumber);
requestParams.Add("BatchNumber", BatchNumber);
requestParams.Add("GiftDetailNumber", -1);
requestParams.Add("GiftNumber", -1);
requestParams.Add("NewBatchSelected", false);
requestParams.Add("GlEffectiveDate", new DateTime(DateTime.Now.Year, 09, 30));
Assert.AreEqual(true, TAdjustmentWebConnector.GiftRevertAdjust(requestParams, out VerificationResult), "reversing the gift batch");
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"Gift Batch was not reverted:");
// duplicate the bank import file, for the next month
FileContent = FileContent.Replace("30.09." + DateTime.Now.Year.ToString("0000"),
"30.10." + DateTime.Now.Year.ToString("0000"));
BankImportDS = import.ImportBankStatementNonInteractive(
FLedgerNumber,
"6200",
";",
"DMY",
TDlgSelectCSVSeparator.NUMBERFORMAT_EUROPEAN,
"unused,DateEffective,Description,Amount,Currency",
"BankStatementOcotober.csv",
FileContent);
Assert.AreNotEqual(null, BankImportDS, "valid bank import dataset october");
Assert.AreEqual(TSubmitChangesResult.scrOK, TBankImportWebConnector.StoreNewBankStatement(
BankImportDS,
out StatementKey), "save statement October");
// create gift batch from imported statement
Int32 GiftBatchNumber = TBankImportWebConnector.CreateGiftBatch(
FLedgerNumber,
StatementKey,
-1,
out VerificationResult);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"cannot create gift batch from bank statement:");
// check if the gift batch is correct
GiftBatchTDS GiftDS = TGiftTransactionWebConnector.LoadGiftBatchData(FLedgerNumber, GiftBatchNumber);
// since we are not able to match the split gift, only 2 gifts should be matched.
// TODO: allow 2 gifts to be merged in OpenPetra, even when they come separate on the bank statement.
// then 4 gifts could be matched.
Assert.AreEqual(2, GiftDS.AGift.Rows.Count, "expected two matched gifts");
}
/// <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;
}
/// <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;
}
/// <summary>
/// store historic Gift matches
/// </summary>
private static void StoreCurrentMatches(BankImportTDS AMatchDS, string ABankAccountCode)
{
TLogging.LogAtLevel(1, "StoreCurrentMatches...");
DataView GiftDetailView = new DataView(
AMatchDS.AGiftDetail, string.Empty,
BankImportTDSAGiftDetailTable.GetGiftTransactionNumberDBName() + "," +
BankImportTDSAGiftDetailTable.GetDetailNumberDBName(),
DataViewRowState.CurrentRows);
SortedList <string, AEpMatchRow>MatchesToAddLater = new SortedList <string, AEpMatchRow>();
// for speed reasons, use a sortedlist instead of a dataview
SortedList <string, AEpMatchRow>MatchesByText = new SortedList <string, AEpMatchRow>();
foreach (AEpMatchRow r in AMatchDS.AEpMatch.Rows)
{
MatchesByText[r.MatchText + ":::" + r.Detail.ToString()] = r;
}
foreach (BankImportTDSAEpTransactionRow tr in AMatchDS.AEpTransaction.Rows)
{
// create a match text which uniquely identifies this transaction
string MatchText = CalculateMatchText(ABankAccountCode, tr);
if (tr.MatchAction != MFinanceConstants.BANK_STMT_STATUS_MATCHED)
{
continue;
}
// get the gift details assigned to this transaction
StringCollection GiftDetailNumbers = StringHelper.GetCSVList(tr.GiftDetailNumbers, ",", false);
foreach (string strDetailNumber in GiftDetailNumbers)
{
DataRowView[] FilteredGiftDetails =
GiftDetailView.FindRows(
new object[] {
tr.GiftTransactionNumber,
Convert.ToInt32(strDetailNumber)
});
// add new matches, and modify existing matches
UpdateMatches(
AMatchDS,
(BankImportTDSAGiftDetailRow)FilteredGiftDetails[0].Row,
MatchText,
Convert.ToInt32(strDetailNumber) - 1,
MatchesByText,
MatchesToAddLater);
}
}
// for speed reasons, add the new rows at the end
foreach (AEpMatchRow m in MatchesToAddLater.Values)
{
AMatchDS.AEpMatch.Rows.Add(m);
}
AMatchDS.PBankingDetails.Clear();
AMatchDS.AGiftDetail.Clear();
AMatchDS.AGift.Clear();
AMatchDS.ThrowAwayAfterSubmitChanges = true;
TLogging.LogAtLevel(1, "before submitchanges");
BankImportTDSAccess.SubmitChanges(AMatchDS);
TLogging.LogAtLevel(1, "after submitchanges");
}
/// <summary>
/// import one MT940 file, split into multiple statements per year
/// </summary>
static public bool ImportFromFile(
Int32 ALedgerNumber,
string ABankAccountCode,
string AFileName,
string AFileContent,
bool AParsePreviousYear,
out Int32 AStatementKey,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = new TVerificationResultCollection();
TSwiftParser parser = new TSwiftParser();
parser.ProcessFileContent(AFileContent);
BankImportTDS MainDS = new BankImportTDS();
Int32 statementCounter = MainDS.AEpStatement.Rows.Count;
foreach (TStatement stmt in parser.statements)
{
Int32 transactionCounter = 0;
foreach (TTransaction tr in stmt.transactions)
{
BankImportTDSAEpTransactionRow row = MainDS.AEpTransaction.NewRowTyped();
row.StatementKey = (statementCounter + 1) * -1;
row.Order = transactionCounter;
row.DetailKey = -1;
row.AccountName = tr.partnerName;
if ((tr.accountCode != null) && Regex.IsMatch(tr.accountCode, "^[A-Z]"))
{
// this is an iban
row.Iban = tr.accountCode;
row.Bic = tr.bankCode;
row.BranchCode = tr.accountCode.Substring(4, 8).TrimStart(new char[] { '0' });
row.BankAccountNumber = tr.accountCode.Substring(12).TrimStart(new char[] { '0' });
}
else if (tr.accountCode != null)
{
row.BankAccountNumber = tr.accountCode.TrimStart(new char[] { '0' });
row.BranchCode = tr.bankCode == null ? string.Empty : tr.bankCode.TrimStart(new char[] { '0' });
row.Iban = string.Empty;
row.Bic = string.Empty;
}
row.DateEffective = tr.valueDate;
row.TransactionAmount = tr.amount;
row.Description = tr.description;
row.TransactionTypeCode = tr.typecode;
// see the codes: http://www.hettwer-beratung.de/sepa-spezialwissen/sepa-technische-anforderungen/sepa-gesch%C3%A4ftsvorfallcodes-gvc-mt-940/
if ((row.TransactionTypeCode == "052") ||
(row.TransactionTypeCode == "051") ||
(row.TransactionTypeCode == "053") ||
(row.TransactionTypeCode == "067") ||
(row.TransactionTypeCode == "068") ||
(row.TransactionTypeCode == "069") ||
(row.TransactionTypeCode == "119") || /* Einzelbuchung Spende (Purpose: CHAR) */
(row.TransactionTypeCode == "152") || /* SEPA Credit Transfer Einzelbuchung Dauerauftrag */
(row.TransactionTypeCode == "166") || /* SEPA Credit Transfer */
(row.TransactionTypeCode == "169") /* SEPA Credit Transfer Donation */
)
{
row.TransactionTypeCode += MFinanceConstants.BANK_STMT_POTENTIAL_GIFT;
}
MainDS.AEpTransaction.Rows.Add(row);
transactionCounter++;
}
AEpStatementRow epstmt = MainDS.AEpStatement.NewRowTyped();
epstmt.StatementKey = (statementCounter + 1) * -1;
epstmt.LedgerNumber = ALedgerNumber;
epstmt.Date = stmt.date;
epstmt.CurrencyCode = stmt.currency;
epstmt.Filename = AFileName;
epstmt.BankAccountCode = ABankAccountCode;
epstmt.IdFromBank = stmt.id;
if (AFileName.Length > AEpStatementTable.GetFilenameLength())
{
epstmt.Filename =
TAppSettingsManager.GetValue("BankNameFor" + stmt.bankCode + "/" + stmt.accountCode,
stmt.bankCode + "/" + stmt.accountCode, true);
}
epstmt.StartBalance = stmt.startBalance;
epstmt.EndBalance = stmt.endBalance;
MainDS.AEpStatement.Rows.Add(epstmt);
// sort by amount, and by accountname; this is the order of the paper statements and attachments
MainDS.AEpTransaction.DefaultView.Sort = BankImportTDSAEpTransactionTable.GetTransactionAmountDBName() + "," +
BankImportTDSAEpTransactionTable.GetOrderDBName();
MainDS.AEpTransaction.DefaultView.RowFilter = BankImportTDSAEpTransactionTable.GetStatementKeyDBName() + "=" +
epstmt.StatementKey.ToString();
// starting with the most negative amount, which should be the last in the order on the statement
Int32 countOrderOnStatement = MainDS.AEpTransaction.DefaultView.Count;
bool countingNegative = true;
foreach (DataRowView rv in MainDS.AEpTransaction.DefaultView)
{
BankImportTDSAEpTransactionRow row = (BankImportTDSAEpTransactionRow)rv.Row;
if ((row.TransactionAmount > 0) && countingNegative)
{
countingNegative = false;
countOrderOnStatement = 1;
}
if (countingNegative)
{
row.NumberOnPaperStatement = countOrderOnStatement;
countOrderOnStatement--;
}
else
{
row.NumberOnPaperStatement = countOrderOnStatement;
countOrderOnStatement++;
}
}
statementCounter++;
}
if (TBankStatementImport.StoreNewBankStatement(
MainDS,
out AStatementKey) == TSubmitChangesResult.scrOK)
{
return(true);
}
return(false);
}
/// <summary>
/// import one CAMT file, split into multiple statements per year
/// </summary>
static public bool ImportFromFile(
Int32 ALedgerNumber,
string ABankAccountCode,
string AFileName,
string AFileContent,
bool AParsePreviousYear,
out Int32 AStatementKey,
out TVerificationResultCollection AVerificationResult)
{
TCAMTParser parser = new TCAMTParser();
AStatementKey = -1;
parser.ProcessFileContent(AFileContent, AParsePreviousYear, out AVerificationResult);
if (AVerificationResult.HasCriticalErrors)
{
return(false);
}
BankImportTDS MainDS = new BankImportTDS();
Int32 statementCounter = MainDS.AEpStatement.Rows.Count;
foreach (TStatement stmt in parser.statements)
{
if (stmt.severalYears && !AParsePreviousYear)
{
// parse the transactions of the previous year separately
ImportFromFile(ALedgerNumber, ABankAccountCode, AFileName, AFileContent,
true,
out AStatementKey, out AVerificationResult);
if (AVerificationResult.HasCriticalErrors)
{
return(false);
}
}
Int32 transactionCounter = 0;
foreach (TTransaction tr in stmt.transactions)
{
BankImportTDSAEpTransactionRow row = MainDS.AEpTransaction.NewRowTyped();
row.StatementKey = (statementCounter + 1) * -1;
row.Order = transactionCounter;
row.DetailKey = -1;
row.AccountName = tr.partnerName;
if ((tr.accountCode != null) && Regex.IsMatch(tr.accountCode, "^[A-Z]"))
{
// this is an iban
row.Iban = tr.accountCode;
row.Bic = tr.bankCode;
row.BranchCode = tr.accountCode.Substring(4, 8).TrimStart(new char[] { '0' });
row.BankAccountNumber = tr.accountCode.Substring(12).TrimStart(new char[] { '0' });
}
else if (tr.accountCode != null)
{
row.BankAccountNumber = tr.accountCode.TrimStart(new char[] { '0' });
row.BranchCode = tr.bankCode == null ? string.Empty : tr.bankCode.TrimStart(new char[] { '0' });
row.Iban = string.Empty;
row.Bic = string.Empty;
}
row.DateEffective = tr.valueDate;
row.TransactionAmount = tr.amount;
row.Description = tr.description;
row.TransactionTypeCode = tr.typecode;
// see the codes: https://www.wgzbank.de/export/sites/wgzbank/de/wgzbank/downloads/produkte_leistungen/firmenkunden/zv_aktuelles/Uebersicht-GVC-und-Buchungstexte-WGZ-BANK_V062015.pdf
if ((row.TransactionTypeCode == "052") ||
(row.TransactionTypeCode == "051") ||
(row.TransactionTypeCode == "053") ||
(row.TransactionTypeCode == "067") ||
(row.TransactionTypeCode == "068") ||
(row.TransactionTypeCode == "069") ||
(row.TransactionTypeCode == "119") || /* Einzelbuchung Spende (Purpose: CHAR) */
(row.TransactionTypeCode == "152") || /* SEPA Credit Transfer Einzelbuchung Dauerauftrag */
(row.TransactionTypeCode == "166") || /* SEPA Credit Transfer */
(row.TransactionTypeCode == "169") /* SEPA Credit Transfer Donation */
)
{
// only incoming money is a potential gift
if (row.TransactionAmount > 0)
{
row.TransactionTypeCode += MFinanceConstants.BANK_STMT_POTENTIAL_GIFT;
}
}
MainDS.AEpTransaction.Rows.Add(row);
transactionCounter++;
}
AEpStatementRow epstmt = MainDS.AEpStatement.NewRowTyped();
epstmt.LedgerNumber = ALedgerNumber;
epstmt.StatementKey = (statementCounter + 1) * -1;
epstmt.Date = stmt.date;
epstmt.CurrencyCode = stmt.currency;
epstmt.BankAccountCode = ABankAccountCode;
epstmt.IdFromBank = stmt.id;
if (AFileName.Length > AEpStatementTable.GetFilenameLength())
{
epstmt.Filename =
stmt.bankCode + "/" + stmt.accountCode;
}
else
{
epstmt.Filename = AFileName;
}
epstmt.StartBalance = stmt.startBalance;
epstmt.EndBalance = stmt.endBalance;
MainDS.AEpStatement.Rows.Add(epstmt);
// sort by amount, and by accountname; this is the order of the paper statements and attachments
MainDS.AEpTransaction.DefaultView.Sort = BankImportTDSAEpTransactionTable.GetTransactionAmountDBName() + "," +
BankImportTDSAEpTransactionTable.GetOrderDBName();
MainDS.AEpTransaction.DefaultView.RowFilter = BankImportTDSAEpTransactionTable.GetStatementKeyDBName() + "=" +
epstmt.StatementKey.ToString();
// starting with the most negative amount, which should be the last in the order on the statement
Int32 countOrderOnStatement = MainDS.AEpTransaction.DefaultView.Count;
bool countingNegative = true;
foreach (DataRowView rv in MainDS.AEpTransaction.DefaultView)
{
BankImportTDSAEpTransactionRow row = (BankImportTDSAEpTransactionRow)rv.Row;
if ((row.TransactionAmount > 0) && countingNegative)
{
countingNegative = false;
countOrderOnStatement = 1;
}
if (countingNegative)
{
row.NumberOnPaperStatement = countOrderOnStatement;
countOrderOnStatement--;
}
else
{
row.NumberOnPaperStatement = countOrderOnStatement;
countOrderOnStatement++;
}
}
statementCounter++;
}
if (TBankStatementImport.StoreNewBankStatement(
MainDS,
out AStatementKey) == TSubmitChangesResult.scrOK)
{
return(true);
}
return(false);
}
/// <summary>
/// this can be used from the unit tests
/// </summary>
public static BankImportTDS ImportBankStatementHelper(Int32 ALedgerNumber,
string ABankAccountCode,
string ASeparator,
string ADateFormat,
string ANumberFormat,
string ACurrencyCode,
string AColumnsUsage,
string AStartAfterLine,
string ABankStatementFilename,
string AStatementData)
{
Int32 FirstTransactionRow = 0;
string DateFormat = (ADateFormat == "MDY" ? "M/d/yyyy" : "d.M.yyyy");
string ThousandsSeparator = (ANumberFormat == "American" ? "," : ".");
string DecimalSeparator = (ANumberFormat == "American" ? "." : ",");
List <String> StatementData = new List <string>();
string [] stmtarray = AStatementData.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string line in stmtarray)
{
StatementData.Add(line);
}
// skip headers
Int32 lineCounter = FirstTransactionRow;
// TODO: support splitting a file by month?
// at the moment this only works for files that are already split by month
// TODO: check if this statement has already been imported, by the stmt.Filename; delete old statement
BankImportTDS MainDS = new BankImportTDS();
AEpStatementRow stmt = MainDS.AEpStatement.NewRowTyped();
stmt.StatementKey = -1;
// TODO: BankAccountKey should be NOT NULL. for the moment not time to implement
// stmt.BankAccountKey = Convert.ToInt64(TXMLParser.GetAttribute(RootNode, "BankAccountKey"));
stmt.Filename = Path.GetFileName(ABankStatementFilename.Replace('\\', Path.DirectorySeparatorChar));
// depending on the path of BankStatementFilename you could determine between several bank accounts
// search all config parameters starting with "BankNameFor",
// and see if the rest of the parameter name is part of the filename or path
StringCollection BankNameForParameters = TAppSettingsManager.GetKeys("BankNameFor");
foreach (string BankNameForParameter in BankNameForParameters)
{
if (stmt.Filename.ToLower().Contains(BankNameForParameter.Substring("BankNameFor".Length).ToLower()))
{
stmt.Filename = TAppSettingsManager.GetValue(BankNameForParameter);
}
}
if (stmt.Filename.Length > AEpStatementTable.GetFilenameLength())
{
// use the last number of characters of the path and filename
stmt.Filename = ABankStatementFilename.Substring(ABankStatementFilename.Length - AEpStatementTable.GetFilenameLength());
}
stmt.LedgerNumber = ALedgerNumber;
stmt.CurrencyCode = ACurrencyCode;
stmt.BankAccountCode = ABankAccountCode;
MainDS.AEpStatement.Rows.Add(stmt);
// TODO would need to allow the user to change the order&meaning of columns
string[] ColumnsUsage = AColumnsUsage.Split(new char[] { ',' });
Dictionary <DateTime, List <AEpTransactionRow> > TransactionsPerMonth = new Dictionary <DateTime, List <AEpTransactionRow> >();
bool startParsing = (AStartAfterLine == String.Empty);
for (; lineCounter < StatementData.Count; lineCounter++)
{
string line = StatementData[lineCounter];
if (AStartAfterLine == line)
{
startParsing = true;
continue;
}
if (!startParsing)
{
continue;
}
AEpTransactionRow row = MainDS.AEpTransaction.NewRowTyped();
row.StatementKey = stmt.StatementKey;
foreach (string UseAs in ColumnsUsage)
{
if (line == String.Empty)
{
// this line is too short, does not have enough columns.
// ignore this row.
row = null;
continue;
}
string Value = StringHelper.GetNextCSV(ref line, StatementData, ref lineCounter, ASeparator);
if (UseAs.ToLower() == "dateeffective")
{
if (Value.Length == "dd.mm.yy".Length)
{
DateFormat = DateFormat.Replace("yyyy", "yy");
}
try
{
row.DateEffective = XmlConvert.ToDateTime(Value, DateFormat);
}
catch (Exception)
{
TLogging.Log("Problem with date effective: " + Value + " (Format: " + DateFormat + ")");
}
}
else if (UseAs.ToLower() == "accountname")
{
if (row.AccountName.Length > 0)
{
row.AccountName += " ";
}
row.AccountName += Value;
}
else if (UseAs.ToLower() == "description")
{
// remove everything after DTA; it is not relevant and confused matching
if (Value.IndexOf(" DTA ") > 0)
{
Value = Value.Substring(0, Value.IndexOf(" DTA "));
}
if (row.Description.Length > 0)
{
row.Description += " ";
}
row.Description += Value;
}
else if (UseAs.ToLower() == "amount")
{
if (Value.Contains(" "))
{
// cut off currency code; should have been defined in the data description file, for the whole batch
Value = Value.Substring(0, Value.IndexOf(" ") - 1);
}
Value = Value.Replace(ThousandsSeparator, "");
Value = Value.Replace(DecimalSeparator, ".");
row.TransactionAmount = Convert.ToDecimal(Value, System.Globalization.CultureInfo.InvariantCulture);
}
else if (UseAs.ToLower() == "directdebiths")
{
if (Value == "S")
{
row.TransactionAmount *= -1;
}
}
else if (UseAs.ToLower() == "currency")
{
if (stmt.CurrencyCode == string.Empty)
{
stmt.CurrencyCode = Value.ToUpper();
}
else if (stmt.CurrencyCode != Value.ToUpper())
{
throw new Exception("cannot mix several currencies in the same bank statement file");
}
}
}
if (row == null)
{
// ignore this line
continue;
}
// all transactions with positive amount can be donations
if (row.TransactionAmount > 0)
{
row.TransactionTypeCode = MFinanceConstants.BANK_STMT_POTENTIAL_GIFT;
}
DateTime month = new DateTime(row.DateEffective.Year, row.DateEffective.Month, 1);
if (!TransactionsPerMonth.ContainsKey(month))
{
TransactionsPerMonth.Add(month, new List <AEpTransactionRow>());
}
TransactionsPerMonth[month].Add(row);
}
if (TransactionsPerMonth.Keys.Count == 0)
{
// cannot find any transactions
return(MainDS);
}
// now find the month that should be imported
DateTime MonthToBeImported = DateTime.MinValue;
foreach (DateTime month in TransactionsPerMonth.Keys)
{
if (MonthToBeImported == DateTime.MinValue)
{
MonthToBeImported = month;
}
else
{
if (TransactionsPerMonth[month].Count > TransactionsPerMonth[MonthToBeImported].Count)
{
MonthToBeImported = month;
}
}
}
DateTime latestDate = DateTime.MinValue;
Int32 rowCount = 0;
foreach (AEpTransactionRow row in TransactionsPerMonth[MonthToBeImported])
{
rowCount++;
row.Order = rowCount;
row.NumberOnPaperStatement = row.Order;
MainDS.AEpTransaction.Rows.Add(row);
if (row.DateEffective > latestDate)
{
latestDate = row.DateEffective;
}
}
stmt.Date = latestDate;
return(MainDS);
}
public static Int32 CreateGLBatch(BankImportTDS AMainDS,
Int32 ALedgerNumber,
Int32 AStatementKey,
Int32 AGLBatchNumber,
out TVerificationResultCollection AVerificationResult)
{
AMainDS.AEpTransaction.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpTransactionTable.GetStatementKeyDBName(),
AStatementKey);
AMainDS.AEpStatement.DefaultView.RowFilter =
String.Format("{0}={1}",
AEpStatementTable.GetStatementKeyDBName(),
AStatementKey);
AEpStatementRow stmt = (AEpStatementRow)AMainDS.AEpStatement.DefaultView[0].Row;
AVerificationResult = null;
Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber;
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
if (!TFinancialYear.IsValidPostingPeriod(ALedgerNumber, stmt.Date, out DateEffectivePeriodNumber, out DateEffectiveYearNumber,
Transaction))
{
string msg = String.Format(Catalog.GetString("Cannot create a GL batch for date {0} since it is not in an open period of the ledger."),
stmt.Date.ToShortDateString());
TLogging.Log(msg);
AVerificationResult = new TVerificationResultCollection();
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating GL Batch"), msg, TResultSeverity.Resv_Critical));
DBAccess.GDBAccessObj.RollbackTransaction();
return -1;
}
Int32 BatchYear, BatchPeriod;
// if DateEffective is outside the range of open periods, use the most fitting date
DateTime DateEffective = stmt.Date;
TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref DateEffective, out BatchYear, out BatchPeriod, Transaction, true);
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
DBAccess.GDBAccessObj.RollbackTransaction();
GLBatchTDS GLDS = TGLTransactionWebConnector.CreateABatch(ALedgerNumber);
ABatchRow glbatchRow = GLDS.ABatch[0];
glbatchRow.BatchPeriod = BatchPeriod;
glbatchRow.DateEffective = DateEffective;
glbatchRow.BatchDescription = String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString());
decimal HashTotal = 0.0M;
decimal DebitTotal = 0.0M;
decimal CreditTotal = 0.0M;
// TODO: support several journals
// TODO: support several currencies, support other currencies than the base currency
AJournalRow gljournalRow = GLDS.AJournal.NewRowTyped();
gljournalRow.LedgerNumber = glbatchRow.LedgerNumber;
gljournalRow.BatchNumber = glbatchRow.BatchNumber;
gljournalRow.JournalNumber = glbatchRow.LastJournal + 1;
gljournalRow.TransactionCurrency = LedgerTable[0].BaseCurrency;
glbatchRow.LastJournal++;
gljournalRow.JournalPeriod = glbatchRow.BatchPeriod;
gljournalRow.DateEffective = glbatchRow.DateEffective;
gljournalRow.JournalDescription = glbatchRow.BatchDescription;
gljournalRow.SubSystemCode = CommonAccountingSubSystemsEnum.GL.ToString();
gljournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString();
gljournalRow.ExchangeRateToBase = 1.0m;
GLDS.AJournal.Rows.Add(gljournalRow);
foreach (DataRowView dv in AMainDS.AEpTransaction.DefaultView)
{
AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row;
DataView v = AMainDS.AEpMatch.DefaultView;
v.RowFilter = AEpMatchTable.GetActionDBName() + " = '" + MFinanceConstants.BANK_STMT_STATUS_MATCHED_GL + "' and " +
AEpMatchTable.GetMatchTextDBName() + " = '" + transactionRow.MatchText + "'";
if (v.Count > 0)
{
AEpMatchRow match = (AEpMatchRow)v[0].Row;
ATransactionRow trans = GLDS.ATransaction.NewRowTyped();
trans.LedgerNumber = glbatchRow.LedgerNumber;
trans.BatchNumber = glbatchRow.BatchNumber;
trans.JournalNumber = gljournalRow.JournalNumber;
trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1;
trans.AccountCode = match.AccountCode;
trans.CostCentreCode = match.CostCentreCode;
trans.Reference = match.Reference;
trans.Narrative = match.Narrative;
trans.TransactionDate = transactionRow.DateEffective;
if (transactionRow.TransactionAmount < 0)
{
trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount;
trans.TransactionAmount = -1 * transactionRow.TransactionAmount;
trans.DebitCreditIndicator = true;
DebitTotal += trans.AmountInBaseCurrency;
}
else
{
trans.AmountInBaseCurrency = transactionRow.TransactionAmount;
trans.TransactionAmount = transactionRow.TransactionAmount;
trans.DebitCreditIndicator = false;
CreditTotal += trans.AmountInBaseCurrency;
}
GLDS.ATransaction.Rows.Add(trans);
gljournalRow.LastTransactionNumber++;
// add one transaction for the bank as well
trans = GLDS.ATransaction.NewRowTyped();
trans.LedgerNumber = glbatchRow.LedgerNumber;
trans.BatchNumber = glbatchRow.BatchNumber;
trans.JournalNumber = gljournalRow.JournalNumber;
trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1;
trans.AccountCode = stmt.BankAccountCode;
trans.CostCentreCode = TLedgerInfo.GetStandardCostCentre(ALedgerNumber);
trans.Reference = match.Reference;
trans.Narrative = match.Narrative;
trans.TransactionDate = transactionRow.DateEffective;
if (transactionRow.TransactionAmount < 0)
{
trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount;
trans.TransactionAmount = -1 * transactionRow.TransactionAmount;
trans.DebitCreditIndicator = false;
CreditTotal += trans.AmountInBaseCurrency;
}
else
{
trans.AmountInBaseCurrency = transactionRow.TransactionAmount;
trans.TransactionAmount = transactionRow.TransactionAmount;
trans.DebitCreditIndicator = true;
DebitTotal += trans.AmountInBaseCurrency;
}
GLDS.ATransaction.Rows.Add(trans);
gljournalRow.LastTransactionNumber++;
}
}
gljournalRow.JournalDebitTotal = DebitTotal;
gljournalRow.JournalCreditTotal = CreditTotal;
glbatchRow.BatchDebitTotal = DebitTotal;
glbatchRow.BatchCreditTotal = CreditTotal;
glbatchRow.BatchControlTotal = HashTotal;
TVerificationResultCollection VerificationResult;
TSubmitChangesResult result = TGLTransactionWebConnector.SaveGLBatchTDS(ref GLDS,
out VerificationResult);
if (result == TSubmitChangesResult.scrOK)
{
return glbatchRow.BatchNumber;
}
TLogging.Log("Problems storing GL Batch");
return -1;
}
/// 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;
}
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);
}
public void TestMultipleGifts()
{
// import the test gift batch, and post it
TGiftImporting importer = new TGiftImporting();
string dirTestData = "../../csharp/ICT/Testing/lib/MFinance/server/BankImport/";
string testFile = dirTestData + "GiftBatch.csv";
StreamReader sr = new StreamReader(testFile);
string FileContent = sr.ReadToEnd();
sr.Close();
FileContent = FileContent.Replace("2010-09-30", DateTime.Now.Year.ToString("0000") + "-09-30");
Hashtable parameters = new Hashtable();
parameters.Add("Delimiter", ",");
parameters.Add("ALedgerNumber", FLedgerNumber);
parameters.Add("DateFormatString", "yyyy-MM-dd");
parameters.Add("NumberFormat", "American");
parameters.Add("NewLine", Environment.NewLine);
parameters.Add("DatesMayBeIntegers", false);
TVerificationResultCollection VerificationResult;
GiftBatchTDSAGiftDetailTable NeedRecipientLedgerNumber;
bool refreshRequired;
if (!importer.ImportGiftBatches(parameters, FileContent, out NeedRecipientLedgerNumber, out refreshRequired, out VerificationResult))
{
Assert.Fail("Gift Batch was not imported: " + VerificationResult.BuildVerificationResultString());
}
int BatchNumber = importer.GetLastGiftBatchNumber();
Assert.AreNotEqual(-1, BatchNumber, "Failed to import gift batch: " + VerificationResult.BuildVerificationResultString());
Int32 generatedGlBatchNumber;
if (!TGiftTransactionWebConnector.PostGiftBatch(FLedgerNumber, BatchNumber, out generatedGlBatchNumber, out VerificationResult))
{
Assert.Fail("Gift Batch was not posted: " + VerificationResult.BuildVerificationResultString());
}
// import the test csv file, will already do the training
testFile = dirTestData + "BankStatement.csv";
sr = new StreamReader(testFile);
FileContent = sr.ReadToEnd();
sr.Close();
FileContent = FileContent.Replace("30.09.2010", "30.09." + DateTime.Now.Year.ToString("0000"));
Int32 StatementKey;
BankImportTDS BankImportDS = TBankStatementImportCSV.ImportBankStatementHelper(
FLedgerNumber,
"6200",
";",
"DMY",
"European",
"EUR",
"unused,DateEffective,Description,Amount,Currency",
"",
"BankStatementSeptember.csv",
FileContent);
Assert.AreNotEqual(null, BankImportDS, "valid bank import dataset september");
Assert.AreEqual(TSubmitChangesResult.scrOK, TBankStatementImport.StoreNewBankStatement(
BankImportDS,
out StatementKey), "save statement September");
// revert the gift batch, so that the training does not get confused if the test is run again;
// the training needs only one gift batch for that date
Hashtable requestParams = new Hashtable();
requestParams.Add("Function", GiftAdjustmentFunctionEnum.ReverseGiftBatch);
requestParams.Add("ALedgerNumber", FLedgerNumber);
requestParams.Add("BatchNumber", BatchNumber);
requestParams.Add("GiftDetailNumber", -1);
requestParams.Add("GiftNumber", -1);
requestParams.Add("NewBatchSelected", false);
requestParams.Add("NoReceipt", true);
requestParams.Add("NewPct", 0.0m);
requestParams.Add("UpdateTaxDeductiblePct", new System.Collections.Generic.List <string[]>());
requestParams.Add("GlEffectiveDate", new DateTime(DateTime.Now.Year, 09, 30));
requestParams.Add("AutoCompleteComments", false);
requestParams.Add("ReversalCommentOne", String.Empty);
requestParams.Add("ReversalCommentTwo", String.Empty);
requestParams.Add("ReversalCommentThree", String.Empty);
requestParams.Add("ReversalCommentOneType", String.Empty);
requestParams.Add("ReversalCommentTwoType", String.Empty);
requestParams.Add("ReversalCommentThreeType", String.Empty);
int AdjustmentBatchNumber;
bool BatchIsUnposted;
GiftBatchTDS GiftReverseDS = TGiftTransactionWebConnector.LoadGiftTransactionsForBatch(FLedgerNumber, BatchNumber, out BatchIsUnposted);
Assert.AreEqual(true, TAdjustmentWebConnector.GiftRevertAdjust(requestParams, out AdjustmentBatchNumber, GiftReverseDS), "reversing the gift batch");
if (!TGiftTransactionWebConnector.PostGiftBatch(FLedgerNumber, AdjustmentBatchNumber, out generatedGlBatchNumber, out VerificationResult))
{
Assert.Fail("Gift Reverse Batch was not posted: " + VerificationResult.BuildVerificationResultString());
}
// duplicate the bank import file, for the next month
FileContent = FileContent.Replace("30.09." + DateTime.Now.Year.ToString("0000"),
"30.10." + DateTime.Now.Year.ToString("0000"));
BankImportDS = TBankStatementImportCSV.ImportBankStatementHelper(
FLedgerNumber,
"6200",
";",
"DMY",
"European",
"EUR",
"unused,DateEffective,Description,Amount,Currency",
"",
"BankStatementOctober.csv",
FileContent);
Assert.AreNotEqual(null, BankImportDS, "valid bank import dataset october");
Assert.AreEqual(TSubmitChangesResult.scrOK, TBankStatementImport.StoreNewBankStatement(
BankImportDS,
out StatementKey), "save statement October");
// create gift batch from imported statement
Int32 GiftBatchNumber;
TBankImportWebConnector.CreateGiftBatch(
FLedgerNumber,
StatementKey,
out VerificationResult,
out GiftBatchNumber);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"cannot create gift batch from bank statement:");
// check if the gift batch is correct
GiftBatchTDS GiftDS = TGiftTransactionWebConnector.LoadAGiftBatchAndRelatedData(FLedgerNumber, GiftBatchNumber);
// since we are not able to match the split gifts, only 1 donation should be matched.
// TODO: allow 2 gifts to be merged in OpenPetra, even when they come separate on the bank statement.
// then 4 gifts could be matched.
Assert.AreEqual(1, GiftDS.AGift.Rows.Count, "expected two matched gifts");
}
/// <summary>
/// export a number of gift batches to a file
/// </summary>
public static void ExportGiftBatches(string AOutputPath)
{
// store all gift batches in first period of current year
string SelectGiftBatchesJanuaryFromAndWhere =
"FROM PUB_a_ledger, PUB_a_gift_batch, PUB_a_gift, PUB_a_gift_detail, " +
" PUB_p_partner donor, PUB_p_partner_banking_details, PUB_p_banking_details_usage, PUB_p_banking_details, PUB_p_bank bank " +
"WHERE PUB_a_ledger.a_ledger_number_i = " + FLedgerNumber.ToString() + " " +
"AND PUB_a_gift_batch.a_ledger_number_i = PUB_a_ledger.a_ledger_number_i " +
"AND PUB_a_gift_batch.a_batch_year_i = PUB_a_ledger.a_current_financial_year_i " +
"AND PUB_a_gift_batch.a_batch_period_i = 1 " +
"AND PUB_a_gift.a_ledger_number_i = PUB_a_gift_batch.a_ledger_number_i " +
"AND PUB_a_gift.a_batch_number_i = PUB_a_gift_batch.a_batch_number_i " +
"AND PUB_a_gift_detail.a_ledger_number_i = PUB_a_gift.a_ledger_number_i " +
"AND PUB_a_gift_detail.a_batch_number_i = PUB_a_gift.a_batch_number_i " +
"AND PUB_a_gift_detail.a_gift_transaction_number_i = PUB_a_gift.a_gift_transaction_number_i " +
"AND donor.p_partner_key_n = PUB_a_gift.p_donor_key_n " +
"AND PUB_p_partner_banking_details.p_partner_key_n = PUB_a_gift.p_donor_key_n " +
"AND PUB_p_banking_details_usage.p_partner_key_n = PUB_a_gift.p_donor_key_n " +
"AND PUB_p_banking_details_usage.p_banking_details_key_i = PUB_p_partner_banking_details.p_banking_details_key_i " +
"AND PUB_p_banking_details_usage.p_type_c = 'MAIN' " +
"AND PUB_p_banking_details.p_banking_details_key_i = PUB_p_partner_banking_details.p_banking_details_key_i " +
"AND bank.p_partner_key_n = PUB_p_banking_details.p_bank_key_n ";
BankImportTDS MainDS = new BankImportTDS();
AGiftBatchTable batches = new AGiftBatchTable();
string SelectGiftBatchesJanuary =
"SELECT DISTINCT PUB_a_gift_batch.* " + SelectGiftBatchesJanuaryFromAndWhere;
DBAccess.GDBAccessObj.SelectDT(batches, SelectGiftBatchesJanuary, null, new OdbcParameter[0], 0, 0);
string SelectGiftDetailsJanuary =
"SELECT DISTINCT PUB_a_gift_detail.*, donor.p_partner_key_n AS DonorKey " + SelectGiftBatchesJanuaryFromAndWhere;
DBAccess.GDBAccessObj.Select(MainDS, SelectGiftDetailsJanuary, MainDS.AGiftDetail.TableName, null);
// get all banking details of donors involved in this gift batch
string SelectBankingDetailsJanuary =
"SELECT DISTINCT PUB_p_banking_details.*, bank.p_branch_code_c AS BankSortCode, donor.p_partner_key_n AS PartnerKey " +
SelectGiftBatchesJanuaryFromAndWhere;
DBAccess.GDBAccessObj.Select(MainDS, SelectBankingDetailsJanuary, MainDS.PBankingDetails.TableName, null);
MainDS.AGiftDetail.DefaultView.Sort = AGiftDetailTable.GetBatchNumberDBName();
MainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetPartnerKeyDBName();
int bankStatementCounter = 1000;
decimal balance = 34304.33m;
foreach (AGiftBatchRow batch in batches.Rows)
{
StoreMT940File(MainDS, AOutputPath, batch.BatchNumber, batch.GlEffectiveDate, bankStatementCounter, ref balance);
bankStatementCounter++;
}
}
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;
}
}
public static TSubmitChangesResult StoreNewBankStatement(BankImportTDS AStatementAndTransactionsDS,
out Int32 AFirstStatementKey)
{
string MyClientID = DomainManager.GClientID.ToString();
AFirstStatementKey = -1;
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Processing new bank statements"),
AStatementAndTransactionsDS.AEpStatement.Rows.Count + 1);
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("Saving to database"),
0);
try
{
// Must not throw away the changes because we need the correct statement keys
AStatementAndTransactionsDS.DontThrowAwayAfterSubmitChanges = true;
BankImportTDSAccess.SubmitChanges(AStatementAndTransactionsDS);
AFirstStatementKey = -1;
if (AStatementAndTransactionsDS != null)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("starting to train"),
1);
AFirstStatementKey = AStatementAndTransactionsDS.AEpStatement[0].StatementKey;
// search for already posted gift batches, and do the matching for these imported statements
TBankImportMatching.Train(AStatementAndTransactionsDS.AEpStatement);
}
TProgressTracker.FinishJob(MyClientID);
}
catch (Exception ex)
{
TLogging.Log(ex.ToString());
TProgressTracker.CancelJob(MyClientID);
return TSubmitChangesResult.scrError;
}
return TSubmitChangesResult.scrOK;
}
/// <summary>
/// store historic Gift matches
/// </summary>
private static void StoreCurrentMatches(BankImportTDS AMatchDS, string ABankAccountCode)
{
TLogging.LogAtLevel(1, "StoreCurrentMatches...");
DataView GiftDetailView = new DataView(
AMatchDS.AGiftDetail, string.Empty,
BankImportTDSAGiftDetailTable.GetGiftTransactionNumberDBName() + "," +
BankImportTDSAGiftDetailTable.GetDetailNumberDBName(),
DataViewRowState.CurrentRows);
SortedList <string, AEpMatchRow> MatchesToAddLater = new SortedList <string, AEpMatchRow>();
// for speed reasons, use a sortedlist instead of a dataview
SortedList <string, AEpMatchRow> MatchesByText = new SortedList <string, AEpMatchRow>();
foreach (AEpMatchRow r in AMatchDS.AEpMatch.Rows)
{
MatchesByText[r.MatchText + ":::" + r.Detail.ToString()] = r;
}
foreach (BankImportTDSAEpTransactionRow tr in AMatchDS.AEpTransaction.Rows)
{
// create a match text which uniquely identifies this transaction
string MatchText = CalculateMatchText(ABankAccountCode, tr);
if (tr.MatchAction != MFinanceConstants.BANK_STMT_STATUS_MATCHED)
{
continue;
}
// get the gift details assigned to this transaction
StringCollection GiftDetailNumbers = StringHelper.GetCSVList(tr.GiftDetailNumbers, ",", false);
foreach (string strDetailNumber in GiftDetailNumbers)
{
DataRowView[] FilteredGiftDetails =
GiftDetailView.FindRows(
new object[] {
tr.GiftTransactionNumber,
Convert.ToInt32(strDetailNumber)
});
// add new matches, and modify existing matches
UpdateMatches(
AMatchDS,
(BankImportTDSAGiftDetailRow)FilteredGiftDetails[0].Row,
MatchText,
Convert.ToInt32(strDetailNumber) - 1,
MatchesByText,
MatchesToAddLater);
}
}
// for speed reasons, add the new rows at the end
foreach (AEpMatchRow m in MatchesToAddLater.Values)
{
AMatchDS.AEpMatch.Rows.Add(m);
}
AMatchDS.PBankingDetails.Clear();
AMatchDS.AGiftDetail.Clear();
AMatchDS.AGift.Clear();
AMatchDS.ThrowAwayAfterSubmitChanges = true;
TLogging.LogAtLevel(1, "before submitchanges");
BankImportTDSAccess.SubmitChanges(AMatchDS);
TLogging.LogAtLevel(1, "after submitchanges");
}
/// <summary>
/// this non interactive function can be used from the unit tests
/// </summary>
public BankImportTDS ImportBankStatementNonInteractive(Int32 ALedgerNumber, string ABankAccountCode,
string ABankStatementFilename)
{
BankImportTDS MainDS = new BankImportTDS();
// import file
if (!ImportFromFile(ABankStatementFilename,
ABankAccountCode,
ref MainDS))
{
return null;
}
foreach (AEpStatementRow stmt in MainDS.AEpStatement.Rows)
{
stmt.LedgerNumber = ALedgerNumber;
}
return MainDS;
}
/// <summary>
/// return a table with gift details for the given date with donor partner keys and bank account numbers
/// </summary>
private static bool GetGiftsByDate(Int32 ALedgerNumber,
BankImportTDS AMainDS,
DateTime ADateEffective,
string ABankAccountCode,
out List <int> AGiftBatchNumbers)
{
TDataBase db = DBAccess.Connect("GetGiftsByDate");
TDBTransaction transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
// first get all gifts, even those that have no bank account associated
string stmt = TDataBase.ReadSqlFile("BankImport.GetDonationsByDate.sql");
OdbcParameter[] parameters = new OdbcParameter[3];
parameters[0] = new OdbcParameter("ALedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("ADateEffective", OdbcType.Date);
parameters[1].Value = ADateEffective;
parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar);
parameters[2].Value = ABankAccountCode;
db.SelectDT(AMainDS.AGiftDetail, stmt, transaction, parameters, 0, 0);
// calculate the totals of gifts
AMainDS.AGift.Clear();
AGiftBatchNumbers = new List <int>();
foreach (BankImportTDSAGiftDetailRow giftdetail in AMainDS.AGiftDetail.Rows)
{
BankImportTDSAGiftRow giftRow =
(BankImportTDSAGiftRow)AMainDS.AGift.Rows.Find(new object[] { giftdetail.LedgerNumber, giftdetail.BatchNumber,
giftdetail.GiftTransactionNumber });
if (giftRow == null)
{
giftRow = AMainDS.AGift.NewRowTyped(true);
giftRow.LedgerNumber = giftdetail.LedgerNumber;
giftRow.BatchNumber = giftdetail.BatchNumber;
giftRow.GiftTransactionNumber = giftdetail.GiftTransactionNumber;
giftRow.TotalAmount = 0;
giftRow.DonorKey = giftdetail.DonorKey;
AMainDS.AGift.Rows.Add(giftRow);
}
giftRow.TotalAmount += giftdetail.GiftTransactionAmount;
if (!AGiftBatchNumbers.Contains(giftRow.BatchNumber))
{
AGiftBatchNumbers.Add(giftRow.BatchNumber);
}
}
// get PartnerKey and banking details (most important BankAccountNumber) for all donations on the given date
stmt = TDataBase.ReadSqlFile("BankImport.GetBankAccountByDate.sql");
parameters = new OdbcParameter[2];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("ADateEffective", OdbcType.Date);
parameters[1].Value = ADateEffective;
// TODO ? parameters[2] = new OdbcParameter("ABankAccountCode", OdbcType.VarChar);
//parameters[2].Value = ABankAccountCode;
// There can be several donors with the same banking details
AMainDS.PBankingDetails.Constraints.Clear();
db.Select(AMainDS, stmt, AMainDS.PBankingDetails.TableName, transaction, parameters);
transaction.Rollback();
return(true);
}
/// <summary>
/// open the file and return a typed datatable
/// </summary>
private bool ImportFromFile(string AFilename,
string ABankAccountCode,
ref BankImportTDS AMainDS)
{
TCAMTParser parser = new TCAMTParser();
parser.ProcessFile(AFilename);
Int32 statementCounter = AMainDS.AEpStatement.Rows.Count;
foreach (TStatement stmt in parser.statements)
{
Int32 transactionCounter = 0;
foreach (TTransaction tr in stmt.transactions)
{
BankImportTDSAEpTransactionRow row = AMainDS.AEpTransaction.NewRowTyped();
row.StatementKey = (statementCounter + 1) * -1;
row.Order = transactionCounter;
row.DetailKey = -1;
row.AccountName = tr.partnerName;
if ((tr.accountCode != null) && Regex.IsMatch(tr.accountCode, "^[A-Z]"))
{
// this is an iban
row.Iban = tr.accountCode;
row.Bic = tr.bankCode;
row.BranchCode = tr.accountCode.Substring(4, 8).TrimStart(new char[] { '0' });
row.BankAccountNumber = tr.accountCode.Substring(12).TrimStart(new char[] { '0' });
}
else if (tr.accountCode != null)
{
row.BankAccountNumber = tr.accountCode.TrimStart(new char[] { '0' });
row.BranchCode = tr.bankCode == null ? string.Empty : tr.bankCode.TrimStart(new char[] { '0' });
row.Iban = string.Empty;
row.Bic = string.Empty;
}
row.DateEffective = tr.valueDate;
row.TransactionAmount = tr.amount;
row.Description = tr.description;
row.TransactionTypeCode = tr.typecode;
// see the codes: https://www.wgzbank.de/export/sites/wgzbank/de/wgzbank/downloads/produkte_leistungen/firmenkunden/zv_aktuelles/Uebersicht-GVC-und-Buchungstexte-WGZ-BANK_V062015.pdf
if ((row.TransactionTypeCode == "052")
|| (row.TransactionTypeCode == "051")
|| (row.TransactionTypeCode == "053")
|| (row.TransactionTypeCode == "067")
|| (row.TransactionTypeCode == "068")
|| (row.TransactionTypeCode == "069")
|| (row.TransactionTypeCode == "119") /* Einzelbuchung Spende (Purpose: CHAR) */
|| (row.TransactionTypeCode == "152") /* SEPA Credit Transfer Einzelbuchung Dauerauftrag */
|| (row.TransactionTypeCode == "166") /* SEPA Credit Transfer */
|| (row.TransactionTypeCode == "169") /* SEPA Credit Transfer Donation */
)
{
// only incoming money is a potential gift
if (row.TransactionAmount > 0)
{
row.TransactionTypeCode += MFinanceConstants.BANK_STMT_POTENTIAL_GIFT;
}
}
AMainDS.AEpTransaction.Rows.Add(row);
transactionCounter++;
}
AEpStatementRow epstmt = AMainDS.AEpStatement.NewRowTyped();
epstmt.StatementKey = (statementCounter + 1) * -1;
epstmt.Date = stmt.date;
epstmt.CurrencyCode = stmt.currency;
epstmt.Filename = AFilename;
epstmt.BankAccountCode = ABankAccountCode;
epstmt.IdFromBank = stmt.id;
if (AFilename.Length > AEpStatementTable.GetFilenameLength())
{
epstmt.Filename =
TAppSettingsManager.GetValue("BankNameFor" + stmt.bankCode + "/" + stmt.accountCode,
stmt.bankCode + "/" + stmt.accountCode, true);
}
epstmt.StartBalance = stmt.startBalance;
epstmt.EndBalance = stmt.endBalance;
AMainDS.AEpStatement.Rows.Add(epstmt);
// sort by amount, and by accountname; this is the order of the paper statements and attachments
AMainDS.AEpTransaction.DefaultView.Sort = BankImportTDSAEpTransactionTable.GetTransactionAmountDBName() + "," +
BankImportTDSAEpTransactionTable.GetOrderDBName();
AMainDS.AEpTransaction.DefaultView.RowFilter = BankImportTDSAEpTransactionTable.GetStatementKeyDBName() + "=" +
epstmt.StatementKey.ToString();
// starting with the most negative amount, which should be the last in the order on the statement
Int32 countOrderOnStatement = AMainDS.AEpTransaction.DefaultView.Count;
bool countingNegative = true;
foreach (DataRowView rv in AMainDS.AEpTransaction.DefaultView)
{
BankImportTDSAEpTransactionRow row = (BankImportTDSAEpTransactionRow)rv.Row;
if ((row.TransactionAmount > 0) && countingNegative)
{
countingNegative = false;
countOrderOnStatement = 1;
}
if (countingNegative)
{
row.NumberOnPaperStatement = countOrderOnStatement;
countOrderOnStatement--;
}
else
{
row.NumberOnPaperStatement = countOrderOnStatement;
countOrderOnStatement++;
}
}
statementCounter++;
}
return true;
}
/// <summary>
/// store historic Gift matches
/// </summary>
private static void StoreCurrentMatches(BankImportTDS AMatchDS, string ABankAccountCode)
{
DataView GiftDetailView = new DataView(
AMatchDS.AGiftDetail, string.Empty,
BankImportTDSAGiftDetailTable.GetGiftTransactionNumberDBName() + "," +
BankImportTDSAGiftDetailTable.GetDetailNumberDBName(),
DataViewRowState.CurrentRows);
SortedList <string, AEpMatchRow> MatchesToAddLater = new SortedList <string, AEpMatchRow>();
List <string> MatchesToDelete = new List <string>();
foreach (BankImportTDSAEpTransactionRow tr in AMatchDS.AEpTransaction.Rows)
{
// create a match text which uniquely identifies this transaction
string MatchText = CalculateMatchText(ABankAccountCode, tr);
// delete existing matches
MatchesToDelete.Add(MatchText);
if (tr.MatchAction != MFinanceConstants.BANK_STMT_STATUS_MATCHED)
{
continue;
}
// get the gift details assigned to this transaction
StringCollection GiftDetailNumbers = StringHelper.GetCSVList(tr.GiftDetailNumbers, ",", false);
foreach (string strDetailNumber in GiftDetailNumbers)
{
DataRowView[] FilteredGiftDetails =
GiftDetailView.FindRows(
new object[] {
tr.GiftTransactionNumber,
Convert.ToInt32(strDetailNumber)
});
// add new matches
// do not assign tr.EpMatchKey, because we cannot delete the old matches then
CreateNewMatches(
AMatchDS,
(BankImportTDSAGiftDetailRow)FilteredGiftDetails[0].Row,
MatchText,
MatchesToAddLater);
}
}
DataView MatchesByText = new DataView(
AMatchDS.AEpMatch, string.Empty,
AEpMatchTable.GetMatchTextDBName(),
DataViewRowState.CurrentRows);
foreach (string MatchToDelete in MatchesToDelete)
{
DataRowView[] MatchesToDeleteRv = MatchesByText.FindRows(MatchToDelete);
foreach (DataRowView rv in MatchesToDeleteRv)
{
rv.Row.Delete();
}
}
MatchesByText.Sort = string.Empty;
MatchesByText.RowFilter = string.Empty;
// for speed reasons, add the new rows at the end
foreach (AEpMatchRow m in MatchesToAddLater.Values)
{
AMatchDS.AEpMatch.Rows.Add(m);
}
AMatchDS.PBankingDetails.Clear();
AMatchDS.AGiftDetail.Clear();
AMatchDS.AGift.Clear();
AMatchDS.ThrowAwayAfterSubmitChanges = true;
if (TLogging.DebugLevel > 0)
{
TLogging.Log("before submitchanges");
}
BankImportTDSAccess.SubmitChanges(AMatchDS);
if (TLogging.DebugLevel > 0)
{
TLogging.Log("after submitchanges");
}
}
private void ProcessStatementsOnServer(BankImportTDS AMainDS)
{
TMBankimportNamespace PluginRemote = new TMBankimportNamespace();
if (PluginRemote.WebConnectors.StoreNewBankStatement(
AMainDS,
out FStatementKey) == TSubmitChangesResult.scrOK)
{
}
}
public static bool DropBankStatement(Int32 AEpStatementKey)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
BankImportTDS MainDS = new BankImportTDS();
AEpStatementAccess.LoadByPrimaryKey(MainDS, AEpStatementKey, Transaction);
AEpTransactionAccess.LoadViaAEpStatement(MainDS, AEpStatementKey, Transaction);
DBAccess.GDBAccessObj.RollbackTransaction();
foreach (AEpStatementRow stmtRow in MainDS.AEpStatement.Rows)
{
stmtRow.Delete();
}
foreach (AEpTransactionRow transactionRow in MainDS.AEpTransaction.Rows)
{
transactionRow.Delete();
}
MainDS.ThrowAwayAfterSubmitChanges = true;
try
{
BankImportTDSAccess.SubmitChanges(MainDS);
return true;
}
catch (Exception)
{
return false;
}
}
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 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);
}
public static BankImportTDS GetBankStatementTransactionsAndMatches(Int32 AStatementKey, Int32 ALedgerNumber)
{
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
BankImportTDS ResultDataset = new BankImportTDS();
string MyClientID = DomainManager.GClientID.ToString();
TProgressTracker.InitProgressTracker(MyClientID,
Catalog.GetString("Load Bank Statement"),
100.0m);
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("loading statement"),
0);
try
{
AEpStatementAccess.LoadByPrimaryKey(ResultDataset, AStatementKey, Transaction);
if (ResultDataset.AEpStatement[0].BankAccountCode.Length == 0)
{
throw new Exception("Loading Bank Statement: Bank Account must not be empty");
}
ACostCentreAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction);
AMotivationDetailAccess.LoadViaALedger(ResultDataset, ALedgerNumber, Transaction);
AEpTransactionAccess.LoadViaAEpStatement(ResultDataset, AStatementKey, Transaction);
BankImportTDS TempDataset = new BankImportTDS();
AEpTransactionAccess.LoadViaAEpStatement(TempDataset, AStatementKey, Transaction);
AEpMatchAccess.LoadViaALedger(TempDataset, ResultDataset.AEpStatement[0].LedgerNumber, Transaction);
// load all bankingdetails and partner shortnames related to this statement
string sqlLoadPartnerByBankAccount =
"SELECT DISTINCT p.p_partner_key_n AS PartnerKey, " +
"p.p_partner_short_name_c AS ShortName, " +
"t.p_branch_code_c AS BranchCode, " +
"t.a_bank_account_number_c AS BankAccountNumber " +
"FROM PUB_a_ep_transaction t, PUB_p_banking_details bd, PUB_p_bank b, PUB_p_partner_banking_details pbd, PUB_p_partner p " +
"WHERE t.a_statement_key_i = " + AStatementKey.ToString() + " " +
"AND bd.p_bank_account_number_c = t.a_bank_account_number_c " +
"AND b.p_partner_key_n = bd.p_bank_key_n " +
"AND b.p_branch_code_c = t.p_branch_code_c " +
"AND pbd.p_banking_details_key_i = bd.p_banking_details_key_i " +
"AND p.p_partner_key_n = pbd.p_partner_key_n";
DataTable PartnerByBankAccount = DBAccess.GDBAccessObj.SelectDT(sqlLoadPartnerByBankAccount, "partnerByBankAccount", Transaction);
PartnerByBankAccount.DefaultView.Sort = "BranchCode, BankAccountNumber";
// get all recipients that have been merged
string sqlGetMergedRecipients =
string.Format(
"SELECT DISTINCT p.p_partner_key_n AS PartnerKey, p.p_status_code_c AS StatusCode FROM PUB_a_ep_match m, PUB_p_partner p " +
"WHERE (m.p_recipient_key_n = p.p_partner_key_n OR m.p_donor_key_n = p.p_partner_key_n) AND p.p_status_code_c = '{0}'",
MPartnerConstants.PARTNERSTATUS_MERGED);
DataTable MergedPartners = DBAccess.GDBAccessObj.SelectDT(sqlGetMergedRecipients, "mergedPartners", Transaction);
MergedPartners.DefaultView.Sort = "PartnerKey";
DBAccess.GDBAccessObj.RollbackTransaction();
string BankAccountCode = ResultDataset.AEpStatement[0].BankAccountCode;
TempDataset.AEpMatch.DefaultView.Sort = AEpMatchTable.GetMatchTextDBName();
SortedList <string, AEpMatchRow>MatchesToAddLater = new SortedList <string, AEpMatchRow>();
List <MatchDate>NewDates = new List <MatchDate>();
List <AEpMatchRow>SetUnmatched = new List <AEpMatchRow>();
int count = 0;
// load the matches or create new matches
foreach (BankImportTDSAEpTransactionRow row in ResultDataset.AEpTransaction.Rows)
{
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("finding matches") +
" " + count + "/" + ResultDataset.AEpTransaction.Rows.Count.ToString(),
10.0m + (count * 80.0m / ResultDataset.AEpTransaction.Rows.Count));
count++;
BankImportTDSAEpTransactionRow tempTransactionRow =
(BankImportTDSAEpTransactionRow)TempDataset.AEpTransaction.Rows.Find(
new object[] {
row.StatementKey,
row.Order,
row.DetailKey
});
// find a match with the same match text, or create a new one
if (row.IsMatchTextNull() || (row.MatchText.Length == 0) || !row.MatchText.StartsWith(BankAccountCode))
{
row.MatchText = TBankImportMatching.CalculateMatchText(BankAccountCode, row);
tempTransactionRow.MatchText = row.MatchText;
}
DataRowView[] matches = TempDataset.AEpMatch.DefaultView.FindRows(row.MatchText);
if (matches.Length > 0)
{
Decimal sum = 0.0m;
// update the recent date
foreach (DataRowView rv in matches)
{
AEpMatchRow r = (AEpMatchRow)rv.Row;
sum += r.GiftTransactionAmount;
string action = r.Action;
// check if the recipient key is still valid. could be that they have married, and merged into another family record
if ((r.RecipientKey != 0)
&& (MergedPartners.DefaultView.FindRows(r.RecipientKey).Length > 0))
{
TLogging.LogAtLevel(1, "partner has been merged: " + r.RecipientKey.ToString());
action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
// check if the donor key is still valid. could be that they have married, and merged into another family record
if ((r.DonorKey != 0)
&& (MergedPartners.DefaultView.FindRows(r.DonorKey).Length > 0))
{
TLogging.LogAtLevel(1, "partner has been merged: " + r.DonorKey.ToString());
action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
// check if the costcentre is still active
ACostCentreRow costcentre = (ACostCentreRow)ResultDataset.ACostCentre.Rows.Find(new object[] { ALedgerNumber,
r.CostCentreCode });
if ((costcentre != null) && !costcentre.CostCentreActiveFlag)
{
TLogging.LogAtLevel(1, "costcentre " + r.CostCentreCode + " is not active anymore; donor: " + r.DonorKey.ToString());
action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
if (r.RecentMatch < row.DateEffective)
{
// do not modify RecentMatch here for speed reasons
// r.RecentMatch = row.DateEffective;
NewDates.Add(new MatchDate(r, row.DateEffective));
}
if (action == MFinanceConstants.BANK_STMT_STATUS_UNMATCHED)
{
SetUnmatched.Add(r);
}
row.MatchAction = action;
if (r.IsDonorKeyNull() || (r.DonorKey <= 0))
{
FindDonorByAccountNumber(r, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber);
}
}
if (sum != row.TransactionAmount)
{
TLogging.Log(
"we should drop this match since the total is wrong: " + row.Description + " " + sum.ToString() + " " +
row.TransactionAmount.ToString());
row.MatchAction = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
foreach (DataRowView rv in matches)
{
AEpMatchRow r = (AEpMatchRow)rv.Row;
if (!SetUnmatched.Contains(r))
{
SetUnmatched.Add(r);
}
}
}
}
else if (!MatchesToAddLater.ContainsKey(row.MatchText))
{
// create new match
AEpMatchRow tempRow = TempDataset.AEpMatch.NewRowTyped(true);
tempRow.EpMatchKey = (TempDataset.AEpMatch.Count + MatchesToAddLater.Count + 1) * -1;
tempRow.Detail = 0;
tempRow.MatchText = row.MatchText;
tempRow.LedgerNumber = ALedgerNumber;
tempRow.GiftTransactionAmount = row.TransactionAmount;
tempRow.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
FindDonorByAccountNumber(tempRow, PartnerByBankAccount.DefaultView, row.BranchCode, row.BankAccountNumber);
#if disabled
// fuzzy search for the partner. only return if unique result
string sql =
"SELECT p_partner_key_n, p_partner_short_name_c FROM p_partner WHERE p_partner_short_name_c LIKE '{0}%' OR p_partner_short_name_c LIKE '{1}%'";
string[] names = row.AccountName.Split(new char[] { ' ' });
if (names.Length > 1)
{
string optionShortName1 = names[0] + ", " + names[1];
string optionShortName2 = names[1] + ", " + names[0];
DataTable partner = DBAccess.GDBAccessObj.SelectDT(String.Format(sql,
optionShortName1,
optionShortName2), "partner", Transaction);
if (partner.Rows.Count == 1)
{
tempRow.DonorKey = Convert.ToInt64(partner.Rows[0][0]);
}
}
#endif
MatchesToAddLater.Add(tempRow.MatchText, tempRow);
// do not modify tempRow.MatchAction, because that will not be stored in the database anyway, just costs time
row.MatchAction = tempRow.Action;
}
}
// for speed reasons, add the new rows after clearing the sort on the view
TempDataset.AEpMatch.DefaultView.Sort = string.Empty;
foreach (AEpMatchRow m in MatchesToAddLater.Values)
{
TempDataset.AEpMatch.Rows.Add(m);
}
foreach (MatchDate date in NewDates)
{
date.r.RecentMatch = date.d;
}
foreach (AEpMatchRow r in SetUnmatched)
{
r.Action = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
r.DonorKey = 0;
r.RecipientKey = 0;
}
TProgressTracker.SetCurrentState(MyClientID,
Catalog.GetString("save matches"),
90.0m);
TempDataset.ThrowAwayAfterSubmitChanges = true;
// only store a_ep_transactions and a_ep_matches, but without additional typed fields (ie MatchAction)
BankImportTDSAccess.SubmitChanges(TempDataset.GetChangesTyped(true));
}
catch (Exception e)
{
TLogging.Log(e.GetType().ToString() + " in BankImport, GetBankStatementTransactionsAndMatches; " + e.Message);
TLogging.Log(e.StackTrace);
DBAccess.GDBAccessObj.RollbackTransaction();
throw;
}
// drop all matches that do not occur on this statement
ResultDataset.AEpMatch.Clear();
// reloading is faster than deleting all matches that are not needed
string sqlLoadMatchesOfStatement =
"SELECT DISTINCT m.* FROM PUB_a_ep_match m, PUB_a_ep_transaction t WHERE t.a_statement_key_i = ? AND m.a_ledger_number_i = ? AND m.a_match_text_c = t.a_match_text_c";
OdbcParameter param = new OdbcParameter("statementkey", OdbcType.Int);
param.Value = AStatementKey;
OdbcParameter paramLedgerNumber = new OdbcParameter("ledgerNumber", OdbcType.Int);
paramLedgerNumber.Value = ALedgerNumber;
DBAccess.GDBAccessObj.SelectDT(ResultDataset.AEpMatch,
sqlLoadMatchesOfStatement,
null,
new OdbcParameter[] { param, paramLedgerNumber }, -1, -1);
// update the custom field for cost centre name for each match
foreach (BankImportTDSAEpMatchRow row in ResultDataset.AEpMatch.Rows)
{
ACostCentreRow ccRow = (ACostCentreRow)ResultDataset.ACostCentre.Rows.Find(new object[] { row.LedgerNumber, row.CostCentreCode });
if (ccRow != null)
{
row.CostCentreName = ccRow.CostCentreName;
}
}
// remove all rows that we do not need on the client side
ResultDataset.AGiftDetail.Clear();
ResultDataset.AMotivationDetail.Clear();
ResultDataset.ACostCentre.Clear();
ResultDataset.AcceptChanges();
if (TLogging.DebugLevel > 0)
{
int CountMatched = 0;
foreach (BankImportTDSAEpTransactionRow transaction in ResultDataset.AEpTransaction.Rows)
{
if (!transaction.IsMatchActionNull() && (transaction.MatchAction != MFinanceConstants.BANK_STMT_STATUS_UNMATCHED))
{
CountMatched++;
}
}
TLogging.Log(
"Loading bank statement: matched: " + CountMatched.ToString() + " of " + ResultDataset.AEpTransaction.Rows.Count.ToString());
}
TProgressTracker.FinishJob(MyClientID);
return ResultDataset;
}
/// <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 void StoreMT940File(BankImportTDS AMainDS, string AOutputPath, int ABatchNumber,
DateTime ADateEffective,
int AStatementCounter,
ref decimal ABalance)
{
string outfile = Path.GetFullPath(AOutputPath + Path.DirectorySeparatorChar + ADateEffective.ToString("yyyy-MMM-dd") + ".sta");
StreamWriter sw = new StreamWriter(outfile, false, Encoding.UTF8);
sw.WriteLine(":20:STARTUMS");
sw.WriteLine(":25:20090500/0006853030");
sw.WriteLine(":28C:" + AStatementCounter.ToString("00000") + "/001");
sw.WriteLine(":60F:C" + ADateEffective.AddDays(-1).ToString("yyMMdd") + "EUR" +
ABalance.ToString(CultureInfo.InvariantCulture.NumberFormat).Replace(".", ","));
DataView GiftView = new DataView(AMainDS.AGiftDetail);
GiftView.Sort = AGiftDetailTable.GetBatchNumberDBName() + "," +
AGiftDetailTable.GetGiftTransactionNumberDBName();
DataRowView[] giftDetails = AMainDS.AGiftDetail.DefaultView.FindRows(ABatchNumber);
foreach (DataRowView rv in giftDetails)
{
BankImportTDSAGiftDetailRow giftDetail = (BankImportTDSAGiftDetailRow)rv.Row;
if (giftDetail.DetailNumber == 1)
{
// are there any other gift details for this gift?
string AndOthers = string.Empty;
decimal Amount = giftDetail.GiftTransactionAmount;
DataRowView[] otherGifts = GiftView.FindRows(new object[] { ABatchNumber, giftDetail.GiftTransactionNumber });
if (otherGifts.Length > 1)
{
Amount = 0;
AndOthers = " and others";
foreach (DataRowView rv2 in otherGifts)
{
BankImportTDSAGiftDetailRow otherGiftDetail = (BankImportTDSAGiftDetailRow)rv2.Row;
Amount += otherGiftDetail.GiftTransactionAmount;
}
}
BankImportTDSPBankingDetailsRow bankingDetails = (BankImportTDSPBankingDetailsRow)
AMainDS.PBankingDetails.DefaultView.FindRows(giftDetail.DonorKey)[0].Row;
sw.WriteLine(":61:" + ADateEffective.ToString("yyMMdd") + ADateEffective.ToString("MMdd") +
"C" + Amount.ToString(CultureInfo.InvariantCulture.NumberFormat).Replace(".", ",") + "N" +
"051" + "NONREF");
sw.WriteLine(":86:051?00Gutschrift?10999?20" + giftDetail.RecipientKey.ToString() + "?21" + giftDetail.MotivationDetailCode +
AndOthers +
"?30" + bankingDetails.BankSortCode + "?31" + bankingDetails.BankAccountNumber +
"?32" + bankingDetails.AccountName);
ABalance += Amount;
}
}
sw.WriteLine(":62F:C" + ADateEffective.ToString("yyMMdd") + "EUR" +
ABalance.ToString(CultureInfo.InvariantCulture.NumberFormat).Replace(".", ","));
sw.Close();
TLogging.Log("mt940 file has been written to: " + outfile);
}
