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 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>();
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);
}
private void AllTransactionsFocusedRowChanged(System.Object sender, EventArgs e)
{
pnlDetails.Visible = true;
pnlDetails.Enabled = false;
CurrentlySelectedMatch = null;
try
{
CurrentlySelectedTransaction = ((BankImportTDSAEpTransactionRow)grdAllTransactions.SelectedDataRowsAsDataRowView[0].Row);
}
catch (System.IndexOutOfRangeException)
{
// this can happen when the transaction type has changed, and the row disappears from the grid
// select another row
grdAllTransactions.SelectRowInGrid(1);
return;
}
// load selections from the a_ep_match table for the new row
FMatchView.RowFilter = AEpMatchTable.GetMatchTextDBName() +
" = '" + CurrentlySelectedTransaction.MatchText + "'";
AEpMatchRow match = (AEpMatchRow)FMatchView[0].Row;
if (match.Action == MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT)
{
txtDonorKey.Text = StringHelper.FormatStrToPartnerKeyString(match.DonorKey.ToString());
pnlGiftEdit.Visible = true;
pnlGLEdit.Visible = false;
grdGiftDetails.SelectRowInGrid(1);
// grdGiftDetails.SelectRowInGrid does not seem to update the gift details, so we call that manually
GiftDetailsFocusedRowChanged(null, null);
grdGiftDetails.AutoResizeGrid();
grdAllTransactions.Focus();
}
else if (match.Action == MFinanceConstants.BANK_STMT_STATUS_MATCHED_GL)
{
pnlGiftEdit.Visible = false;
pnlGLEdit.Visible = true;
DisplayGLDetails();
}
else
{
pnlDetails.Visible = false;
}
}
private void TransactionFilterChanged(System.Object sender, EventArgs e)
{
pnlDetails.Visible = false;
CurrentlySelectedMatch = null;
CurrentlySelectedTransaction = null;
if (FTransactionView == null)
{
return;
}
if (rbtListAll.Checked)
{
FTransactionView.RowFilter = String.Format("{0}={1}",
AEpStatementTable.GetStatementKeyDBName(),
CurrentStatement.StatementKey);
}
else if (rbtListGift.Checked)
{
// TODO: allow splitting a transaction, one part is GL/AP, the other is a donation?
// at Top Level: split transaction, results into 2 rows in aeptransaction (not stored). Merge Transactions again?
FTransactionView.RowFilter = String.Format("{0}={1} and {2}='{3}'",
AEpStatementTable.GetStatementKeyDBName(),
CurrentStatement.StatementKey,
BankImportTDSAEpTransactionTable.GetMatchActionDBName(),
MFinanceConstants.BANK_STMT_STATUS_MATCHED_GIFT);
}
else if (rbtListUnmatchedGift.Checked)
{
FTransactionView.RowFilter = String.Format("{0}={1} and {2}='{3}' and {4} LIKE '%{5}'",
AEpStatementTable.GetStatementKeyDBName(),
CurrentStatement.StatementKey,
BankImportTDSAEpTransactionTable.GetMatchActionDBName(),
MFinanceConstants.BANK_STMT_STATUS_UNMATCHED,
BankImportTDSAEpTransactionTable.GetTransactionTypeCodeDBName(),
MFinanceConstants.BANK_STMT_POTENTIAL_GIFT);
}
else if (rbtListUnmatchedGL.Checked)
{
FTransactionView.RowFilter = String.Format("{0}={1} and {2}='{3}' and ({4} NOT LIKE '%{5}' OR {4} IS NULL)",
AEpStatementTable.GetStatementKeyDBName(),
CurrentStatement.StatementKey,
BankImportTDSAEpTransactionTable.GetMatchActionDBName(),
MFinanceConstants.BANK_STMT_STATUS_UNMATCHED,
BankImportTDSAEpTransactionTable.GetTransactionTypeCodeDBName(),
MFinanceConstants.BANK_STMT_POTENTIAL_GIFT);
}
else if (rbtListIgnored.Checked)
{
FTransactionView.RowFilter = String.Format("{0}={1} and {2}='{3}'",
AEpStatementTable.GetStatementKeyDBName(),
CurrentStatement.StatementKey,
BankImportTDSAEpTransactionTable.GetMatchActionDBName(),
MFinanceConstants.BANK_STMT_STATUS_NO_MATCHING);
}
else if (rbtListGL.Checked)
{
FTransactionView.RowFilter = String.Format("{0}={1} and {2}='{3}'",
AEpStatementTable.GetStatementKeyDBName(),
CurrentStatement.StatementKey,
BankImportTDSAEpTransactionTable.GetMatchActionDBName(),
MFinanceConstants.BANK_STMT_STATUS_MATCHED_GL);
}
// update sumcredit and sumdebit
decimal sumCredit = 0.0M;
decimal sumDebit = 0.0M;
foreach (DataRowView rv in FTransactionView)
{
AEpTransactionRow Row = (AEpTransactionRow)rv.Row;
if (Row.TransactionAmount < 0)
{
sumDebit += Row.TransactionAmount * -1.0M;
}
else
{
sumCredit += Row.TransactionAmount;
}
}
txtCreditSum.NumberValueDecimal = sumCredit;
txtDebitSum.NumberValueDecimal = sumDebit;
txtTransactionCount.Text = FTransactionView.Count.ToString();
if (FTransactionView.Count > 0)
{
grdAllTransactions.SelectRowInGrid(1);
AllTransactionsFocusedRowChanged(null, null);
}
}
/// <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);
}
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;
}
