banking-addons-team team mailing list archive

["Ruchir Shukla\(Endian Solutions\)" <ruchiryshukla@xxxxxxxxx>]

Ruchir Shukla(Endian Solutions) has proposed merging lp:~ruchir.shukla/banking-addons/account_banking_nl_mt940structured into lp:banking-addons.

Requested reviews:
  Banking Addons Core Editors (banking-addons-team)

For more details, see:
https://code.launchpad.net/~ruchir.shukla/banking-addons/account_banking_nl_mt940structured/+merge/172233

Module to import MT940 Structured bank format transaction files
This modules contains no logic, just an import filter for account_banking.
-- 
https://code.launchpad.net/~ruchir.shukla/banking-addons/account_banking_nl_mt940structured/+merge/172233
Your team Banking Addons Core Editors is requested to review the proposed merge of lp:~ruchir.shukla/banking-addons/account_banking_nl_mt940structured into lp:banking-addons.

=== added directory 'account_banking_nl_mt940structured'
=== added file 'account_banking_nl_mt940structured/__init__.py'
--- account_banking_nl_mt940structured/__init__.py	1970-01-01 00:00:00 +0000
+++ account_banking_nl_mt940structured/__init__.py	2013-06-30 08:49:27 +0000
@@ -0,0 +1,23 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+#    Copyright 2013 Endian Solutions BV
+#
+#    This program is free software: you can redistribute it and/or modify
+#    it under the terms of the GNU Affero General Public License as
+#    published by the Free Software Foundation, either version 3 of the
+#    License, or (at your option) any later version.
+#
+#    This program is distributed in the hope that it will be useful,
+#    but WITHOUT ANY WARRANTY; without even the implied warranty of
+#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+#    GNU Affero General Public License for more details.
+#
+#    You should have received a copy of the GNU Affero General Public License
+#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+
+import mt940
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:

=== added file 'account_banking_nl_mt940structured/__openerp__.py'
--- account_banking_nl_mt940structured/__openerp__.py	1970-01-01 00:00:00 +0000
+++ account_banking_nl_mt940structured/__openerp__.py	2013-06-30 08:49:27 +0000
@@ -0,0 +1,39 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+#    Copyright 2013 Endian Solutions BV
+#
+#    This program is free software: you can redistribute it and/or modify
+#    it under the terms of the GNU Affero General Public License as
+#    published by the Free Software Foundation, either version 3 of the
+#    License, or (at your option) any later version.
+#
+#    This program is distributed in the hope that it will be useful,
+#    but WITHOUT ANY WARRANTY; without even the implied warranty of
+#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+#    GNU Affero General Public License for more details.
+#
+#    You should have received a copy of the GNU Affero General Public License
+#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+
+{
+    'name': 'MT940 structured (NL) Bank Statements Import',
+    'version': '0.1',
+    'license': 'GPL-3',
+    'author': 'Endian Solutions BV',
+    'website': 'http://www.endiansolutions.nl',
+    'category': 'Account Banking',
+    'depends': ['account_banking'],
+    'init_xml': [],
+    'update_xml': [],
+    'demo_xml': [],
+    'description': '''
+    Module to import MT940 Structured bank format transaction files
+
+    This modules contains no logic, just an import filter for account_banking.
+    ''',
+    'active': False,
+    'installable': True,
+}

=== added directory 'account_banking_nl_mt940structured/i18n'
=== added file 'account_banking_nl_mt940structured/i18n/nl.po'
--- account_banking_nl_mt940structured/i18n/nl.po	1970-01-01 00:00:00 +0000
+++ account_banking_nl_mt940structured/i18n/nl.po	2013-06-30 08:49:27 +0000
@@ -0,0 +1,48 @@
+# Translation of OpenERP Server.
+# This file contains the translation of the following modules:
+# 	* account_banking_nl_mt940structured
+#
+msgid ""
+msgstr ""
+"Project-Id-Version: OpenERP Server 7.0\n"
+"Report-Msgid-Bugs-To: \n"
+"POT-Creation-Date: 2013-06-28 07:18+0000\n"
+"PO-Revision-Date: 2013-06-28 09:31+0100\n"
+"Last-Translator: Erwin van der Ploeg | Endian Solutions "
+"<erwin@xxxxxxxxxxxxxxxxxx>\n"
+"Language-Team: \n"
+"MIME-Version: 1.0\n"
+"Content-Type: text/plain; charset=UTF-8\n"
+"Content-Transfer-Encoding: 8bit\n"
+"Plural-Forms: \n"
+"X-Generator: Poedit 1.5.5\n"
+
+#. module: account_banking_nl_mt940structured
+#: code:addons/account_banking_nl_mt940structured/mt940.py:85
+#, python-format
+msgid "No Execution Date"
+msgstr "Geen uitvoeringsdatum"
+
+#. module: account_banking_nl_mt940structured
+#: code:addons/account_banking_nl_mt940structured/mt940.py:87
+#, python-format
+msgid "No Transferred Amount"
+msgstr "Geen overgemaakt bedrag"
+
+#. module: account_banking_nl_mt940structured
+#: code:addons/account_banking_nl_mt940structured/mt940.py:176
+#, python-format
+msgid "Accounting banking Interface is used"
+msgstr "De accounting banking Interface is gebruikt"
+
+#. module: account_banking_nl_mt940structured
+#: code:addons/account_banking_nl_mt940structured/mt940.py:77
+#, python-format
+msgid "Invalid: %s"
+msgstr "Ongeldig: %s"
+
+#. module: account_banking_nl_mt940structured
+#: code:addons/account_banking_nl_mt940structured/mt940.py:174
+#, python-format
+msgid "MT940 Structured"
+msgstr "MT940 Structured"

=== added file 'account_banking_nl_mt940structured/mt940.py'
--- account_banking_nl_mt940structured/mt940.py	1970-01-01 00:00:00 +0000
+++ account_banking_nl_mt940structured/mt940.py	2013-06-30 08:49:27 +0000
@@ -0,0 +1,209 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+#    Copyright 2013 Endian Solutions BV
+#    Copyright 2011 credativ Ltd
+#
+#    This program is free software: you can redistribute it and/or modify
+#    it under the terms of the GNU Affero General Public License as
+#    published by the Free Software Foundation, either version 3 of the
+#    License, or (at your option) any later version.
+#
+#    This program is distributed in the hope that it will be useful,
+#    but WITHOUT ANY WARRANTY; without even the implied warranty of
+#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+#    GNU Affero General Public License for more details.
+#
+#    You should have received a copy of the GNU Affero General Public License
+#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+import re
+import logging
+import time
+from account_banking.parsers import models
+from tools.translate import _
+from mt940_parser import MT940Parser
+
+bt = models.mem_bank_transaction
+logger = logging.getLogger('mt940')
+
+
+def record2float(record, value):
+    if record['creditmarker'][-1] == 'C':
+        return float(record[value])
+    return -float(record[value])
+
+
+class transaction(models.mem_bank_transaction):
+
+    mapping = {
+        'execution_date': 'valuedate',
+        'effective_date': 'valuedate',
+        'local_currency': 'currency',
+        'transfer_type': 'bookingcode',
+        'remote_account': 'custrefno',
+        'message': 'infoline1',
+    }
+
+    type_map = {
+        'NTRF': bt.ORDER,
+        'NMSC': bt.ORDER,
+        'NPAY': bt.PAYMENT_BATCH,
+        'NCHK': bt.CHECK,
+    }
+
+    def __init__(self, record, *args, **kwargs):
+        '''
+        Transaction creation
+        '''
+        self.error_message = ""
+        super(transaction, self).__init__(*args, **kwargs)
+        for key, value in self.mapping.iteritems():
+            if value in record:
+                setattr(self, key, record[value])
+
+        self.transferred_amount = record2float(record, 'amount')
+        self.reference = None
+
+        # Set the transfer type based on the bookingcode
+        if record.get('bookingcode', 'ignore') in self.type_map:
+            self.transfer_type = self.type_map[record['bookingcode']]
+        else:
+            # Default to the generic order, so it will be eligible for matching
+            self.transfer_type = bt.ORDER
+
+        if not self.is_valid():
+            self.error_message += _("Invalid: %s", record)
+            logger.warning("Invalid: %s", record)
+
+    def is_valid(self):
+        '''
+        We don't have remote_account so override base
+        '''
+        if not self.execution_date:
+            self.error_message += _("No Execution Date")
+        if not self.transferred_amount:
+            self.error_message += _("No Transferred Amount")
+
+        return (self.execution_date
+                and self.transferred_amount and True) or False
+
+
+class statement(models.mem_bank_statement):
+    '''
+    Bank statement imported data
+    '''
+
+    def __init__(self, *args, **kwargs):
+        """Set defaults to fill from first statement."""
+        super(statement, self).__init__(*args, **kwargs)
+        self.local_account = None
+        self.start_balance = 0
+
+    def import_record(self, record):
+        def _transmission_number():
+            self.id = record['transref']
+
+        def _account_number():
+            acc_num = record['accnum'].replace('.', '').zfill(10)
+            self.local_account = acc_num
+            self.id = time.strftime('%Yw%W')
+
+        def _statement_number():
+            pass
+
+        def _opening_balance():
+            if not self.start_balance:
+                self.start_balance = record2float(record, 'startingbalance')
+                self.local_currency = record['currencycode']
+
+        def _closing_balance():
+            if record2float(record, 'endingbalance'):
+                self.end_balance = record2float(record, 'endingbalance')
+                self.date = record['bookingdate']
+
+        def _transaction_new():
+            self.transactions.append(transaction(record))
+
+        def _transaction_info():
+            self.transaction_info(record)
+
+        def _not_used():
+            logger.warning("Didn't use record: %s", record)
+
+        rectypes = {
+            '20': _transmission_number,
+            '25': _account_number,
+            '28': _statement_number,
+            '28C': _statement_number,
+            '60F': _opening_balance,
+            '62F': _closing_balance,
+            #'64': _forward_available,
+            #'62M': _interim_balance,
+            '61': _transaction_new,
+            '86': _transaction_info,
+        }
+
+        rectypes.get(record.get('recordid'), _not_used)()
+
+    def transaction_info(self, record):
+        '''
+        Add extra information to transaction
+        '''
+        # Additional information for previous transaction
+        if len(self.transactions) < 1:
+            logger.error(
+                "Received additional information for non existent transaction")
+            logger.info(record)
+        else:
+            transaction = self.transactions[-1]
+            if not transaction.reference:
+                transaction.reference = record['infoline1']
+                transaction.reference += record.get('infoline2', '')
+                transaction.reference = transaction.reference.split(
+                    "REMI/")[-1]
+                transaction.reference = transaction.reference.split("/ISDT")[0]
+                transaction.statement_id = time.strftime('%Yw%W')
+            transaction.message += '{0}\n'.format(
+                record['infoline1'] + record.get('infoline2', ''))
+
+
+class parser_mt940(models.parser):
+    code = 'MT940'
+    name = _('MT940 Structured')
+    country_code = 'NL'
+    doc = _('''Accounting banking Interface is used''')
+
+    def parse(self, cr, data):
+        result = []
+        parser = MT940Parser()
+        # Split into statements
+        statements = [st for st in re.split('[\r\n]*(?=:20:)', data)]
+        # Split by records
+        statement_list = [re.split('[\r\n ]*(?=:\d\d[\w]?:)', st)
+                          for st in statements]
+        stmnt = None
+        current_account = None
+        for statement_lines in statement_list:
+            records = [parser.parse_record(record) for
+                       record in statement_lines]
+            if len(records) > 2 and not (
+                    current_account == records[1]['accnum']):
+                current_account = records[1]['accnum']
+                if stmnt:
+                    if stmnt.is_valid() and stmnt.transactions:
+                        result.append(stmnt)
+                    else:
+                        logger.info("Invalid Statement:")
+                        logger.info(records[0])
+                stmnt = statement()
+            [stmnt.import_record(r) for r in records if r is not None]
+        # Last statement check
+        if stmnt.is_valid() and stmnt.transactions:
+
+            result.append(stmnt)
+
+        return result
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:

=== added file 'account_banking_nl_mt940structured/mt940_parser.py'
--- account_banking_nl_mt940structured/mt940_parser.py	1970-01-01 00:00:00 +0000
+++ account_banking_nl_mt940structured/mt940_parser.py	2013-06-30 08:49:27 +0000
@@ -0,0 +1,156 @@
+#!/usr/bin/env python
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+#    Copyright 2013 Endian Solutions BV
+#    Copyright 2011 credativ Ltd
+#
+#    This program is free software: you can redistribute it and/or modify
+#    it under the terms of the GNU Affero General Public License as
+#    published by the Free Software Foundation, either version 3 of the
+#    License, or (at your option) any later version.
+#
+#    This program is distributed in the hope that it will be useful,
+#    but WITHOUT ANY WARRANTY; without even the implied warranty of
+#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+#    GNU Affero General Public License for more details.
+#
+#    You should have received a copy of the GNU Affero General Public License
+#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+"""
+Based on fi_patu's parser
+"""
+import re
+import logging
+from datetime import datetime
+
+_logger = logging.getLogger(__name__)
+
+
+class MT940Parser(object):
+
+    def __init__(self):
+        recparse = dict()
+
+        # MT940 header
+        #recparse["940"] = ":(?P<recordid>940):"
+        recparse["20"] = ":(?P<recordid>20):(?P<transref>.{1,14})"
+        recparse["25"] = ":(?P<recordid>25):(?P<accnum>.*\d{2,15})"
+        recparse["28"] = ":(?P<recordid>28?):(?P<statementnr>.{1,8})"
+
+        # Opening balance 60F
+        recparse["60F"] = ":(?P<recordid>60F):(?P<creditmarker>[CD])" \
+                + "(?P<prevstmtdate>\d{6})(?P<currencycode>.{3})" \
+                + "(?P<startingbalance>[\d,]{1,15})"
+
+        # Transaction
+        recparse["61"] = """\
+:(?P<recordid>61):\
+(?P<valuedate>\d{6})\
+(?P<creditmarker>R?[CD])\
+(?P<amount>[\d,]{1,15})\
+(?P<bookingcode>[A-Z0-9]{4})\
+(?P<custref>[A-Z0-9]{1,6})\
+(?P<custrefno>[A-Z0-9\s]{1,80})\
+(?P<furtherinfo>[\s*A-Z0-9\s]{1-34})?"""
+
+        # Further info
+        recparse["86"] = ":(?P<recordid>86):" \
+                + "(?P<infoline1>.{1,80})?" \
+                + "(?:\n(?P<infoline2>.{1,80}))?" \
+                + "(?:\n(?P<infoline3>.{1,80}))?" \
+                + "(?:\n(?P<infoline4>.{1,80}))?" \
+                + "(?:\n(?P<infoline5>.{1,80}))?"
+
+        # Forward available balance (64) /  Closing balance (62F) / Interim balance (62M)
+        recparse["64"] = ":(?P<recordid>64|62[FM]):" \
+                + "(?P<creditmarker>[CD])" \
+                + "(?P<bookingdate>\d{6})(?P<currencycode>.{3})" \
+                + "(?P<endingbalance>[\d,]{1,15})"
+
+        for record in recparse:
+            recparse[record] = re.compile(recparse[record])
+        self.recparse = recparse
+
+    def parse_record(self, line):
+        """
+        Parse record using regexps and apply post processing
+        """
+        for matcher in self.recparse:
+            matchobj = self.recparse[matcher].match(line)
+            if matchobj:
+                break
+        if not matchobj:
+            _logger.warning(" **** failed to match line '%s'" % (line))
+            return
+        # Strip strings
+        matchdict = matchobj.groupdict()
+        # Remove members set to None
+        matchdict = dict([(k, v) for k, v in matchdict.iteritems() if v])
+
+        matchkeys = set(matchdict.keys())
+        needstrip = set(["transref", "accnum", "statementnr", "custrefno",
+                         "bankref", "furtherinfo", "infoline1", "infoline2",
+                         "infoline3", "infoline4", "infoline5",
+                         "startingbalance", "endingbalance"])
+        for field in matchkeys & needstrip:
+            matchdict[field] = matchdict[field].strip()
+
+        # Convert to float. Comma is decimal separator
+        needsfloat = set(["startingbalance", "endingbalance", "amount"])
+        for field in matchkeys & needsfloat:
+            matchdict[field] = float(matchdict[field].replace(',', '.'))
+
+        # Convert date fields
+        needdate = set(["prevstmtdate", "valuedate", "bookingdate"])
+        for field in matchkeys & needdate:
+            datestring = matchdict[field]
+
+            post_check = False
+            if (len(datestring) == 4 and field == "bookingdate" and
+                    'valudedate' in matchdict):
+                # Get year from valuedate
+                datestring = matchdict['valuedate'].strftime('%y') + datestring
+                post_check = True
+            try:
+                matchdict[field] = datetime.strptime(datestring, '%y%m%d')
+                if post_check and matchdict[field] > matchdict["valuedate"]:
+                    matchdict[field] = matchdict[field].replace(
+                        year=matchdict[field].year - 1)
+            except ValueError:
+                matchdict[field] = None
+
+        return matchdict
+
+    def parse(self, cr, data):
+        records = []
+        # Some records are multiline
+        for line in data:
+            if len(line) <= 1:
+                continue
+            if line[0] == ':' and len(line) > 1:
+                records.append(line)
+            else:
+                records[-1] = '\n'.join([records[-1], line])
+        output = []
+        for rec in records:
+            output.append(self.parse_record(rec))
+        return output
+
+
+def parse_file(filename):
+    inputfile = open(filename, "r")
+    MT940Parser().parse(inputfile.readlines())
+
+
+def main():
+    """The main function, currently just calls a dummy filename
+
+    :returns: description
+    """
+    parse_file("mut.swi")
+
+if __name__ == '__main__':
+    main()