public static bool TrainBankStatement(Int32 ALedgerNumber, DateTime ADateOfStatement, string ABankAccountCode)
{
// get the statement keys
TDBTransaction ReadTransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
AEpStatementTable Statements = new AEpStatementTable();
AEpStatementRow row = Statements.NewRowTyped(false);
row.LedgerNumber = ALedgerNumber;
row.Date = ADateOfStatement;
row.BankAccountCode = ABankAccountCode;
Statements = AEpStatementAccess.LoadUsingTemplate(row, ReadTransaction);
DBAccess.GDBAccessObj.RollbackTransaction();
if (Statements.Rows.Count == 0)
{
return(false);
}
// search for already posted gift batches, and do the matching for these imported statements
TBankImportMatching.Train(Statements);
return(true);
}
public static AEpStatementTable GetImportedBankStatements(Int32 ALedgerNumber, DateTime AStartDate)
{
TDBTransaction ReadTransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
AEpStatementTable localTable = new AEpStatementTable();
AEpStatementRow row = localTable.NewRowTyped(false);
row.LedgerNumber = ALedgerNumber;
row.Date = AStartDate;
StringCollection operators = new StringCollection();
operators.Add("=");
operators.Add(">=");
localTable = AEpStatementAccess.LoadUsingTemplate(row, operators, null, ReadTransaction);
DBAccess.GDBAccessObj.RollbackTransaction();
return(localTable);
}
private void DeleteStatement(object sender, EventArgs e)
{
DataRowView[] SelectedGridRow = grdSelectStatement.SelectedDataRowsAsDataRowView;
if (SelectedGridRow.Length >= 1)
{
AEpStatementRow toDelete = (AEpStatementRow)SelectedGridRow[0].Row;
if (MessageBox.Show(
String.Format(Catalog.GetString("Do you really want to delete the bank statement {0}?"),
toDelete.Filename),
Catalog.GetString("Confirmation"),
MessageBoxButtons.YesNo,
MessageBoxIcon.Question) == DialogResult.Yes)
{
if (FPluginRemote.WebConnectors.DropBankStatement(toDelete.StatementKey))
{
PopulateStatementGrid(null, null);
}
}
}
}
private static void CreateMatches(BankImportTDS AMainDS,
AEpStatementRow ACurrentStatement,
Int32 ASelectedGiftBatch, bool APostedBatch)
{
// remove all gifts and giftdetails that don't belong to the selected batch
List <DataRow> ToDelete = new List <DataRow>();
foreach (AGiftDetailRow giftdetail in AMainDS.AGiftDetail.Rows)
{
if (giftdetail.BatchNumber != ASelectedGiftBatch)
{
ToDelete.Add(giftdetail);
}
}
foreach (DataRow del in ToDelete)
{
AMainDS.AGiftDetail.Rows.Remove(del);
}
ToDelete = new List <DataRow>();
foreach (AGiftRow gift in AMainDS.AGift.Rows)
{
if (gift.BatchNumber != ASelectedGiftBatch)
{
ToDelete.Add(gift);
}
}
foreach (DataRow del in ToDelete)
{
AMainDS.AGift.Rows.Remove(del);
}
ToDelete = new List <DataRow>();
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
// delete transactions with negative amount
if (transaction.TransactionAmount < 0)
{
ToDelete.Add(transaction);
}
else
{
transaction.MatchAction = MFinanceConstants.BANK_STMT_STATUS_UNMATCHED;
}
}
foreach (DataRow del in ToDelete)
{
AMainDS.AEpTransaction.Rows.Remove(del);
}
FindDonorKeysByBankAccount(AMainDS);
AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetPartnerKeyDBName();
MatchDonorsWithKnownBankaccount(AMainDS);
while (MatchTransactionsToGiftBatch(AMainDS))
{
;
}
if (TLogging.DebugLevel > 0)
{
TLogging.Log("transactions not matched yet:");
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
if (transaction.MatchAction != Ict.Petra.Shared.MFinance.MFinanceConstants.BANK_STMT_STATUS_MATCHED)
{
TLogging.Log(
" " + transaction.DonorKey.ToString() + " " + transaction.AccountName + " --- " + transaction.Description + " " +
transaction.TransactionAmount.ToString());
if (transaction.DonorKey == -1)
{
TLogging.Log(" " + transaction.BankAccountNumber + " " + transaction.BranchCode);
}
}
}
TLogging.Log("gifts not matched yet:");
foreach (BankImportTDSAGiftDetailRow giftdetail in AMainDS.AGiftDetail.Rows)
{
if (!giftdetail.AlreadyMatched)
{
string HasBankDetails = "-";
int BankDetailsIndex = AMainDS.PBankingDetails.DefaultView.Find(giftdetail.DonorKey);
if (BankDetailsIndex != -1)
{
HasBankDetails = "*";
}
TLogging.Log(
" " + HasBankDetails + " " + giftdetail.DonorKey.ToString() + " " + giftdetail.DonorShortName + " --- " +
giftdetail.RecipientDescription + " " + giftdetail.GiftAmount.ToString());
if (BankDetailsIndex != -1)
{
BankImportTDSPBankingDetailsRow bankdetail =
(BankImportTDSPBankingDetailsRow)AMainDS.PBankingDetails.DefaultView[BankDetailsIndex].Row;
TLogging.Log(" " + bankdetail.BankAccountNumber + " " + bankdetail.BankSortCode);
}
}
}
int CountMatched = 0;
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
if (transaction.MatchAction == Ict.Petra.Shared.MFinance.MFinanceConstants.BANK_STMT_STATUS_MATCHED)
{
CountMatched++;
}
}
TLogging.Log("matched: " + CountMatched.ToString() + " of " + AMainDS.AEpTransaction.Rows.Count.ToString());
}
StoreCurrentMatches(AMainDS, ACurrentStatement.BankAccountCode);
}
/// <summary>
/// there are several gift batches that might fit this bank statement. find the right one!
/// simple matching; no split gifts, bank account number fits and amount fits
/// </summary>
private static int FindGiftBatch(BankImportTDS AMainDS, AEpStatementRow AStmt)
{
SortedList <Int32, Int32> MatchedGiftBatches = new SortedList <int, int>();
// create the dataview only after loading, otherwise loading is much slower
DataView GiftDetailByAmountAndDonor = new DataView(AMainDS.AGiftDetail,
string.Empty,
AGiftDetailTable.GetGiftAmountDBName() + "," +
BankImportTDSAGiftDetailTable.GetDonorKeyDBName(),
DataViewRowState.CurrentRows);
DataView GiftByAmountAndDonor = new DataView(AMainDS.AGift,
string.Empty,
BankImportTDSAGiftTable.GetTotalAmountDBName() + "," +
AGiftTable.GetDonorKeyDBName(),
DataViewRowState.CurrentRows);
AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetBankSortCodeDBName() + "," +
BankImportTDSPBankingDetailsTable.GetBankAccountNumberDBName();
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
// find the donor for this transaction, by his bank account number
Int64 DonorKey = GetDonorByBankAccountNumber(AMainDS, transaction.BranchCode, transaction.BankAccountNumber);
if (transaction.BankAccountNumber.Length == 0)
{
// useful for NUnit testing for csv import: partnerkey in description
try
{
DonorKey = Convert.ToInt64(transaction.Description);
}
catch (Exception)
{
DonorKey = -1;
}
}
BankImportTDSAGiftDetailRow detailrow = null;
if (DonorKey != -1)
{
DataRowView[] giftDetails = GiftDetailByAmountAndDonor.FindRows(new object[] { transaction.TransactionAmount, DonorKey });
if (giftDetails.Length == 1)
{
// found a possible match
detailrow = (BankImportTDSAGiftDetailRow)giftDetails[0].Row;
}
else
{
// check if we can find a gift with several gift details, that would match this transaction amount
DataRowView[] gifts = GiftByAmountAndDonor.FindRows(new object[] { transaction.TransactionAmount, DonorKey });
if (gifts.Length >= 1)
{
AGiftRow gift = (AGiftRow)gifts[0].Row;
detailrow =
(BankImportTDSAGiftDetailRow)AMainDS.AGiftDetail.Rows.Find(new object[] { gift.LedgerNumber, gift.BatchNumber,
gift.GiftTransactionNumber,
1 });
}
}
}
if (detailrow != null)
{
if (MatchedGiftBatches.ContainsKey(detailrow.BatchNumber))
{
MatchedGiftBatches[detailrow.BatchNumber]++;
}
else
{
MatchedGiftBatches.Add(detailrow.BatchNumber, 1);
}
}
}
int SelectedGiftBatch = -1;
int maxMatches = 0;
foreach (int GiftBatchNumber in MatchedGiftBatches.Keys)
{
if (MatchedGiftBatches[GiftBatchNumber] > maxMatches)
{
maxMatches = MatchedGiftBatches[GiftBatchNumber];
SelectedGiftBatch = GiftBatchNumber;
}
}
if ((SelectedGiftBatch != -1) &&
((AMainDS.AEpTransaction.Rows.Count > 2) &&
(MatchedGiftBatches[SelectedGiftBatch] < AMainDS.AEpTransaction.Rows.Count / 2)))
{
TLogging.Log(
"cannot find enough gifts that look the same, for statement " + AStmt.Filename +
". CountMatches for batch " + SelectedGiftBatch.ToString() + ": " +
MatchedGiftBatches[SelectedGiftBatch].ToString());
SelectedGiftBatch = -1;
}
return(SelectedGiftBatch);
}
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);
}
/// <summary>
/// dump unmatched gifts or other transactions to a HTML table for printing
/// </summary>
private static string PrintHTML(
AEpStatementRow ACurrentStatement,
DataView AEpTransactions, AEpMatchTable AMatches, string ATitle,
string ALetterTemplateFilename)
{
if ((ALetterTemplateFilename.Length == 0) || !File.Exists(ALetterTemplateFilename))
{
OpenFileDialog DialogOpen = new OpenFileDialog();
DialogOpen.Filter = "Report template (*.html)|*.html";
DialogOpen.RestoreDirectory = true;
DialogOpen.Title = "Open Report Template";
if (DialogOpen.ShowDialog() == DialogResult.OK)
{
ALetterTemplateFilename = DialogOpen.FileName;
}
}
// message body from HTML template
StreamReader reader = new StreamReader(ALetterTemplateFilename);
string msg = reader.ReadToEnd();
reader.Close();
msg = msg.Replace("#TITLE", ATitle);
msg = msg.Replace("#PRINTDATE", DateTime.Now.ToShortDateString());
if (!ACurrentStatement.IsIdFromBankNull())
{
msg = msg.Replace("#STATEMENTNR", ACurrentStatement.IdFromBank);
}
if (!ACurrentStatement.IsStartBalanceNull())
{
msg = msg.Replace("#STARTBALANCE", String.Format("{0:N}", ACurrentStatement.StartBalance));
}
if (!ACurrentStatement.IsEndBalanceNull())
{
msg = msg.Replace("#ENDBALANCE", String.Format("{0:N}", ACurrentStatement.EndBalance));
}
// recognise detail lines automatically
string RowTemplate;
msg = TPrinterHtml.GetTableRow(msg, "#NRONSTATEMENT", out RowTemplate);
string rowTexts = "";
BankImportTDSAEpTransactionRow row = null;
AEpTransactions.Sort = BankImportTDSAEpTransactionTable.GetNumberOnPaperStatementDBName();
Decimal Sum = 0.0m;
Int32 NumberPrinted = 0;
DataView MatchesByMatchText = new DataView(AMatches,
string.Empty,
AEpMatchTable.GetMatchTextDBName(),
DataViewRowState.CurrentRows);
string thinLine = "<font size=\"-3\">-------------------------------------------------------------------------<br/></font>";
foreach (DataRowView rv in AEpTransactions)
{
row = (BankImportTDSAEpTransactionRow)rv.Row;
string rowToPrint = RowTemplate;
// short description, remove all SEPA stuff
string ShortDescription = RemoveSEPAText(row.Description);
rowToPrint = rowToPrint.Replace("#NAME", row.AccountName);
rowToPrint = rowToPrint.Replace("#DESCRIPTION", row.Description);
rowToPrint = rowToPrint.Replace("#SHORTDESCRIPTION", ShortDescription);
string RecipientDescription = string.Empty;
DataRowView[] matches = MatchesByMatchText.FindRows(row.MatchText);
AEpMatchRow match = null;
foreach (DataRowView rvMatch in matches)
{
match = (AEpMatchRow)rvMatch.Row;
if (RecipientDescription.Length > 0)
{
RecipientDescription += "<br/>";
}
if (!match.IsRecipientKeyNull() && (match.RecipientKey > 0))
{
RecipientDescription += match.RecipientKey.ToString() + " ";
}
RecipientDescription += match.RecipientShortName;
}
if (RecipientDescription.Trim().Length > 0)
{
rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTIONUNMATCHED", string.Empty);
rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTION", "<br/>" + thinLine + RecipientDescription);
}
else
{
// extra space for unmatched gifts
rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTIONUNMATCHED", "<br/><br/>");
rowToPrint = rowToPrint.Replace("#RECIPIENTDESCRIPTION", string.Empty);
}
if ((match != null) && !match.IsDonorKeyNull() && (match.DonorKey > 0))
{
string DonorDescription = "<br/>" + thinLine + match.DonorKey.ToString() + " " + match.DonorShortName;
rowToPrint = rowToPrint.Replace("#DONORDESCRIPTION", DonorDescription);
rowToPrint = rowToPrint.Replace("#DONORKEY", StringHelper.PartnerKeyToStr(match.DonorKey));
rowToPrint = rowToPrint.Replace("#DONORNAMEORDESCRIPTION", match.DonorShortName);
}
else
{
rowToPrint = rowToPrint.Replace("#DONORDESCRIPTION", string.Empty);
rowToPrint = rowToPrint.Replace("#DONORKEY", string.Empty);
rowToPrint = rowToPrint.Replace("#DONORNAMEORDESCRIPTION", row.AccountName);
}
rowTexts += rowToPrint.
Replace("#NRONSTATEMENT", row.NumberOnPaperStatement.ToString()).
Replace("#AMOUNT", String.Format("{0:C}", row.TransactionAmount)).
Replace("#IBANANDBIC",
row.IsIbanNull() ? string.Empty : "<br/>" + row.Iban + "<br/>" + row.Bic).
Replace("#IBAN", row.Iban).
Replace("#BIC", row.Bic).
Replace("#ACCOUNTNUMBER", row.BankAccountNumber).
Replace("#BANKSORTCODE", row.BranchCode);
Sum += Convert.ToDecimal(row.TransactionAmount);
NumberPrinted++;
}
Sum = Math.Round(Sum, 2);
msg = msg.Replace("#ROWTEMPLATE", rowTexts);
msg = msg.Replace("#TOTALAMOUNT", String.Format("{0:C}", Sum));
msg = msg.Replace("#TOTALNUMBER", NumberPrinted.ToString());
return msg;
}
/// <summary>
/// select the bank statement that should be loaded
/// </summary>
/// <param name="AStatementKey"></param>
private void SelectBankStatement(Int32 AStatementKey)
{
CurrentlySelectedMatch = null;
CurrentStatement = null;
// merge the cost centres and the motivation details from the cacheable tables
FMainDS.ACostCentre.Merge(TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.CostCentreList, FLedgerNumber));
FMainDS.AMotivationDetail.Merge(TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.MotivationList, FLedgerNumber));
FMainDS.ACostCentre.AcceptChanges();
FMainDS.AMotivationDetail.AcceptChanges();
// load the transactions of the selected statement, and the matches
Thread t = new Thread(() => GetBankStatementTransactionsAndMatches(AStatementKey));
using (TProgressDialog dialog = new TProgressDialog(t))
{
if (dialog.ShowDialog() == DialogResult.Cancel)
{
return;
}
}
while (FMainDS.AEpStatement.Rows.Count != 1)
{
// wait for the merging of the dataset to finish in the thread
Thread.Sleep(300);
}
// an old version of the CSV import plugin did not set the potential gift typecode
foreach (AEpTransactionRow r in FMainDS.AEpTransaction.Rows)
{
if (r.IsTransactionTypeCodeNull() && (r.TransactionAmount > 0))
{
r.TransactionTypeCode = MFinanceConstants.BANK_STMT_POTENTIAL_GIFT;
}
}
CurrentStatement = (AEpStatementRow)FMainDS.AEpStatement[0];
FTransactionView = FMainDS.AEpTransaction.DefaultView;
FTransactionView.AllowNew = false;
FTransactionView.Sort = AEpTransactionTable.GetOrderDBName() + " ASC";
grdAllTransactions.DataSource = new DevAge.ComponentModel.BoundDataView(FTransactionView);
TFinanceControls.InitialiseMotivationGroupList(ref cmbMotivationGroup, FLedgerNumber, true);
TFinanceControls.InitialiseMotivationDetailList(ref cmbMotivationDetail, FLedgerNumber, true);
TFinanceControls.InitialiseCostCentreList(ref cmbGLCostCentre, FLedgerNumber, true, false, true, true);
TFinanceControls.InitialiseAccountList(ref cmbGLAccount, FLedgerNumber, true, false, true, false);
FMatchView = FMainDS.AEpMatch.DefaultView;
FMatchView.AllowNew = false;
grdGiftDetails.DataSource = new DevAge.ComponentModel.BoundDataView(FMatchView);
TFinanceControls.InitialiseAccountList(ref cmbBankAccount, FLedgerNumber, true, false, true, true);
if (CurrentStatement != null)
{
FMainDS.AEpStatement.DefaultView.RowFilter = String.Format("{0}={1}",
AEpStatementTable.GetStatementKeyDBName(),
CurrentStatement.StatementKey);
cmbBankAccount.SetSelectedString(CurrentStatement.BankAccountCode);
txtBankStatement.Text = CurrentStatement.Filename;
dtpBankStatementDate.Date = CurrentStatement.Date;
FMainDS.AEpStatement.DefaultView.RowFilter = string.Empty;
}
TransactionFilterChanged(null, null);
grdAllTransactions.SelectRowInGrid(1);
grdAllTransactions.AutoResizeGrid();
}
/// <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);
}
/// <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>
/// there are several gift batches that might fit this bank statement. find the right one!
/// simple matching; no split gifts, bank account number fits and amount fits
/// </summary>
private static int FindGiftBatch(BankImportTDS AMainDS, AEpStatementRow AStmt)
{
SortedList <Int32, Int32>MatchedGiftBatches = new SortedList <int, int>();
// create the dataview only after loading, otherwise loading is much slower
DataView GiftDetailByAmountAndDonor = new DataView(AMainDS.AGiftDetail,
string.Empty,
AGiftDetailTable.GetGiftAmountDBName() + "," +
BankImportTDSAGiftDetailTable.GetDonorKeyDBName(),
DataViewRowState.CurrentRows);
DataView GiftByAmountAndDonor = new DataView(AMainDS.AGift,
string.Empty,
BankImportTDSAGiftTable.GetTotalAmountDBName() + "," +
AGiftTable.GetDonorKeyDBName(),
DataViewRowState.CurrentRows);
AMainDS.PBankingDetails.DefaultView.Sort = BankImportTDSPBankingDetailsTable.GetBankSortCodeDBName() + "," +
BankImportTDSPBankingDetailsTable.GetBankAccountNumberDBName();
foreach (BankImportTDSAEpTransactionRow transaction in AMainDS.AEpTransaction.Rows)
{
// find the donor for this transaction, by his bank account number
Int64 DonorKey = GetDonorByBankAccountNumber(AMainDS, transaction.BranchCode, transaction.BankAccountNumber);
if (transaction.BankAccountNumber.Length == 0)
{
// useful for NUnit testing for csv import: partnerkey in description
try
{
DonorKey = Convert.ToInt64(transaction.Description);
}
catch (Exception)
{
DonorKey = -1;
}
}
BankImportTDSAGiftDetailRow detailrow = null;
if (DonorKey != -1)
{
DataRowView[] giftDetails = GiftDetailByAmountAndDonor.FindRows(new object[] { transaction.TransactionAmount, DonorKey });
if (giftDetails.Length == 1)
{
// found a possible match
detailrow = (BankImportTDSAGiftDetailRow)giftDetails[0].Row;
}
else
{
// check if we can find a gift with several gift details, that would match this transaction amount
DataRowView[] gifts = GiftByAmountAndDonor.FindRows(new object[] { transaction.TransactionAmount, DonorKey });
if (gifts.Length >= 1)
{
AGiftRow gift = (AGiftRow)gifts[0].Row;
detailrow =
(BankImportTDSAGiftDetailRow)AMainDS.AGiftDetail.Rows.Find(new object[] { gift.LedgerNumber, gift.BatchNumber,
gift.GiftTransactionNumber,
1 });
}
}
}
if (detailrow != null)
{
if (MatchedGiftBatches.ContainsKey(detailrow.BatchNumber))
{
MatchedGiftBatches[detailrow.BatchNumber]++;
}
else
{
MatchedGiftBatches.Add(detailrow.BatchNumber, 1);
}
}
}
int SelectedGiftBatch = -1;
int maxMatches = 0;
foreach (int GiftBatchNumber in MatchedGiftBatches.Keys)
{
if (MatchedGiftBatches[GiftBatchNumber] > maxMatches)
{
maxMatches = MatchedGiftBatches[GiftBatchNumber];
SelectedGiftBatch = GiftBatchNumber;
}
}
if ((SelectedGiftBatch != -1)
&& ((AMainDS.AEpTransaction.Rows.Count > 2)
&& (MatchedGiftBatches[SelectedGiftBatch] < AMainDS.AEpTransaction.Rows.Count / 2)))
{
TLogging.Log(
"cannot find enough gifts that look the same, for statement " + AStmt.Filename +
". CountMatches for batch " + SelectedGiftBatch.ToString() + ": " +
MatchedGiftBatches[SelectedGiftBatch].ToString());
SelectedGiftBatch = -1;
}
return SelectedGiftBatch;
}
private static void 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);
}
