/// <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);
        }
Exemple #2
0
        /// <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);
        }
Exemple #3
0
        /// <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);
        }