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