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