Merge lp:~therp-nl/banking-addons/6.1-account_banking_nl_ing_mt940 into lp:banking-addons/6.1

[Holger Brunn (Therp)]

This adds a generic mt940 parser (account_banking_mt940) and on top of that a parser for ING's version of MT940 in the Netherlands.

The hope would be that account_banking_mt940 makes it very simple to support other MT940 dialects.

[lp:~therp-nl/banking-addons/6.1-account_banking_nl_ing_mt940]

197. By Holger Brunn (Therp) on 2014-02-26

[FIX] typos

198. By Holger Brunn (Therp) on 2014-02-26

[IMP] code formatting, forgotten comment

[Stefan Rijnhart (Opener) (stefan-opener)]

Awesome, finally a generic MT940 parser!

Just two comments from code review, I'll do some testing later on:
- l.122 You might want to add a comment that the exception handling here allows you to execute the file to test the parser outside the context of the OpenERP server (I think).
- l.165 Quite a fashion statement ;-) I know this runs, but you might want to either add two single quotes at the start or remove two at the end so that it looks like a regular docstring.

review: Needs Fixing

[lp:~therp-nl/banking-addons/6.1-account_banking_nl_ing_mt940]

199. By Holger Brunn (Therp) on 2014-02-26

[FIX] typos, manifest formatting, comments
[RFR] allow using custom mem_bank_{statement,transaction}s in a
convenient way

[Holger Brunn (Therp) (hbrunn)]

thanks for the points, I fixed a typo and added a minor refactoring

[Stefan Rijnhart (Opener) (stefan-opener)]

Thanks. I have not been able to find a sample statement file for this particular bank, so I won't be able to test. But the code looks excellent so it will have to do. Maybe there are other reviewers who have access to ING structured MT940 statement files?

review: Approve

[lp:~therp-nl/banking-addons/6.1-account_banking_nl_ing_mt940]

200. By Holger Brunn (Therp) on 2014-02-28

[FIX] pass cr to refactored method

201. By Holger Brunn (Therp) on 2014-02-28

[ADD] allow transaction without remote_account
[ADD] get some information for unstructured mt940

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

> This addon provides a generic parser for MT940 files. Given that MT940 is a
> non-open non-standard of pure evil in the way that every bank cooks up its own
> interpretation of it, this addon alone won't help you much. It is rather
> intended to be used by other addons to implement the dialect specific to a
> certain bank.

hmmm I trust you and will try to stay away of this evil dialect.

review: Approve

[Holger Brunn (Therp) (hbrunn)]

well, fortunately you have account_banking_mt940 now to use as a pitchfork if you ever face a situation where you can't avoid it

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

I will remember of that, thanks ;-)

[Stefan Rijnhart (Opener) (stefan-opener)]

Merged into 6.1, and cherrypicked unmodified in 7.0.

=== added directory 'account_banking_mt940'
=== added file 'account_banking_mt940/__init__.py'
--- account_banking_mt940/__init__.py	1970-01-01 00:00:00 +0000
+++ account_banking_mt940/__init__.py	2014-02-28 13:34:37 +0000
@@ -0,0 +1,21 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+#    OpenERP, Open Source Management Solution
+#    This module copyright (C) 2014 Therp BV (<http://therp.nl>).
+#
+#    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/>.
+#
+##############################################################################
+from . import mt940
 
=== added file 'account_banking_mt940/__openerp__.py'
--- account_banking_mt940/__openerp__.py	1970-01-01 00:00:00 +0000
+++ account_banking_mt940/__openerp__.py	2014-02-28 13:34:37 +0000
@@ -0,0 +1,53 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+#    OpenERP, Open Source Management Solution
+#    This module copyright (C) 2014 Therp BV (<http://therp.nl>).
+#
+#    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",
+    "version" : "1.0",
+    "author" : "Therp BV",
+    "complexity": "expert",
+    "description": """
+This addon provides a generic parser for MT940 files. Given that MT940 is a
+non-open non-standard of pure evil in the way that every bank cooks up its own
+interpretation of it, this addon alone won't help you much. It is rather
+intended to be used by other addons to implement the dialect specific to a
+certain bank.
+
+See account_banking_nl_ing_mt940 for an example on how to use it.
+    """,
+    "category" : "Dependency",
+    "depends" : [
+        'account_banking',
+    ],
+    "data" : [
+    ],
+    "js": [
+    ],
+    "css": [
+    ],
+    "qweb": [
+    ],
+    "auto_install": False,
+    "installable": True,
+    "application": False,
+    "external_dependencies" : {
+        'python' : [],
+    },
+}
 
=== added file 'account_banking_mt940/mt940.py'
--- account_banking_mt940/mt940.py	1970-01-01 00:00:00 +0000
+++ account_banking_mt940/mt940.py	2014-02-28 13:34:37 +0000
@@ -0,0 +1,217 @@
+#!/usr/bin/env python2
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+#    OpenERP, Open Source Management Solution
+#    This module copyright (C) 2014 Therp BV (<http://therp.nl>).
+#
+#    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/>.
+#
+##############################################################################
+
+"""
+Parser for MT940 format files
+"""
+import re
+import datetime
+import logging
+try:
+    from openerp.addons.account_banking.parsers.models import\
+            mem_bank_statement, mem_bank_transaction
+    from openerp.tools.misc import DEFAULT_SERVER_DATE_FORMAT
+except ImportError:
+    #this allows us to run this file standalone, see __main__ at the end
+    class mem_bank_statement:
+        def __init__(self):
+            self.transactions = []
+    class mem_bank_transaction:
+        pass
+    DEFAULT_SERVER_DATE_FORMAT = "%Y-%m-%d"
+
+class MT940(object):
+    '''Inherit this class in your account_banking.parsers.models.parser,
+    define functions to handle the tags you need to handle and adjust static
+    variables as needed.
+
+    Note that order matters: You need to do your_parser(MT940, parser), not the
+    other way around!
+    
+    At least, you should override handle_tag_61 and handle_tag_86. Don't forget
+    to call super.
+    handle_tag_* functions receive the remainder of the the line (that is,
+    without ':XX:') and are supposed to write into self.current_transaction'''
+
+    header_lines = 3
+    '''One file can contain multiple statements, each with its own poorly
+    documented header. For now, the best thing to do seems to skip that'''
+
+    footer_regex = '^-}$'
+    footer_regex = '^-XXX$'
+    'The line that denotes end of message, we need to create a new statement'
+    
+    tag_regex = '^:[0-9]{2}[A-Z]*:'
+    'The beginning of a record, should be anchored to beginning of the line'
+
+    def __init__(self, *args, **kwargs):
+        super(MT940, self).__init__(*args, **kwargs)
+        'state variables'
+        self.current_statement = None
+        'type account_banking.parsers.models.mem_bank_statement'
+        self.current_transaction = None
+        'type account_banking.parsers.models.mem_bank_transaction'
+        self.statements = []
+        'parsed statements up to now'
+
+    def parse(self, cr, data):
+        'implements account_banking.parsers.models.parser.parse()'
+        iterator = data.split('\r\n').__iter__()
+        line = None
+        record_line = ''
+        try:
+            while True:
+                if not self.current_statement:
+                    self.handle_header(cr, line, iterator)
+                line = iterator.next()
+                if not self.is_tag(cr, line) and not self.is_footer(cr, line):
+                    record_line = self.append_continuation_line(
+                        cr, record_line, line)
+                    continue
+                if record_line:
+                    self.handle_record(cr, record_line)
+                if self.is_footer(cr, line):
+                    self.handle_footer(cr, line, iterator)
+                    record_line = ''
+                    continue
+                record_line = line
+        except StopIteration:
+            pass
+        return self.statements
+
+    def append_continuation_line(self, cr, line, continuation_line):
+        '''append a continuation line for a multiline record.
+        Override and do data cleanups as necessary.'''
+        return line + continuation_line
+
+    def create_statement(self, cr):
+        '''create a mem_bank_statement - override if you need a custom
+        implementation'''
+        return mem_bank_statement()
+
+    def create_transaction(self, cr):
+        '''create a mem_bank_transaction - override if you need a custom
+        implementation'''
+        return mem_bank_transaction()
+
+    def is_footer(self, cr, line):
+        '''determine if a line is the footer of a statement'''
+        return line and bool(re.match(self.footer_regex, line))
+
+    def is_tag(self, cr, line):
+        '''determine if a line has a tag'''
+        return line and bool(re.match(self.tag_regex, line))
+
+    def handle_header(self, cr, line, iterator):
+        '''skip header lines, create current statement'''
+        for i in range(self.header_lines):
+            iterator.next()
+        self.current_statement = self.create_statement(cr)
+
+    def handle_footer(self, cr, line, iterator):
+        '''add current statement to list, reset state'''
+        self.statements.append(self.current_statement)
+        self.current_statement = None
+
+    def handle_record(self, cr, line):
+        '''find a function to handle the record represented by line'''
+        tag_match = re.match(self.tag_regex, line)
+        tag = tag_match.group(0).strip(':')
+        if not hasattr(self, 'handle_tag_%s' % tag):
+            logging.error('Unknown tag %s', tag)
+            logging.error(line)
+            return
+        handler = getattr(self, 'handle_tag_%s' % tag)
+        handler(cr, line[tag_match.end():])
+
+    def handle_tag_20(self, cr, data):
+        '''ignore reference number'''
+        pass
+
+    def handle_tag_25(self, cr, data):
+        '''get account owner information'''
+        self.current_statement.local_account = data
+
+    def handle_tag_28C(self, cr, data):
+        '''get sequence number _within_this_batch_ - this alone
+        doesn't provide a unique id!'''
+        self.current_statement.id = data
+
+    def handle_tag_60F(self, cr, data):
+        '''get start balance and currency'''
+        self.current_statement.local_currency = data[7:10]
+        self.current_statement.date = str2date(data[1:7])
+        self.current_statement.start_balance = \
+            (1 if data[0] == 'C' else -1) * str2float(data[10:])
+        self.current_statement.id = '%s/%s' % (
+            self.current_statement.date.strftime('%Y'),
+            self.current_statement.id)
+
+    def handle_tag_62F(self, cr, data):
+        '''get ending balance'''
+        self.current_statement.end_balance = \
+            (1 if data[0] == 'C' else -1) * str2float(data[10:])
+
+    def handle_tag_64(self, cr, data):
+        '''get current balance in currency'''
+        pass
+
+    def handle_tag_65(self, cr, data):
+        '''get future balance in currency'''
+        pass
+
+    def handle_tag_61(self, cr, data):
+        '''get transaction values'''
+        transaction = self.create_transaction(cr)
+        self.current_statement.transactions.append(transaction)
+        self.current_transaction = transaction
+        transaction.execution_date = str2date(data[:6])
+        transaction.effective_date = str2date(data[:6])
+        '...and the rest already is highly bank dependent'
+
+    def handle_tag_86(self, cr, data):
+        '''details for previous transaction, here most differences between
+        banks occur'''
+        pass
+
+'utility functions'
+def str2date(string, fmt='%y%m%d'):
+    return datetime.datetime.strptime(string, fmt)
+
+def str2float(string):
+    return float(string.replace(',', '.'))
+
+'testing'
+def main(filename):
+    parser = MT940()
+    parser.parse(None, open(filename, 'r').read())
+    for statement in parser.statements:
+        print '''statement found for %(local_account)s at %(date)s
+        with %(local_currency)s%(start_balance)s to %(end_balance)s
+        ''' % statement.__dict__
+        for transaction in statement.transactions:
+            print '''
+            transaction on %(execution_date)s''' % transaction.__dict__
+
+if __name__ == '__main__':
+    import sys
+    main(*sys.argv[1:])
 
=== added directory 'account_banking_mt940/static'
=== added directory 'account_banking_mt940/static/src'
=== added directory 'account_banking_mt940/static/src/img'
=== added file 'account_banking_mt940/static/src/img/icon.png'
 Binary files account_banking_mt940/static/src/img/icon.png	1970-01-01 00:00:00 +0000 and account_banking_mt940/static/src/img/icon.png	2014-02-28 13:34:37 +0000 differ
=== added directory 'account_banking_nl_ing_mt940'
=== added file 'account_banking_nl_ing_mt940/__init__.py'
--- account_banking_nl_ing_mt940/__init__.py	1970-01-01 00:00:00 +0000
+++ account_banking_nl_ing_mt940/__init__.py	2014-02-28 13:34:37 +0000
@@ -0,0 +1,21 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+#    OpenERP, Open Source Management Solution
+#    This module copyright (C) 2014 Therp BV (<http://therp.nl>).
+#
+#    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/>.
+#
+##############################################################################
+from . import account_banking_nl_ing_mt940
 
=== added file 'account_banking_nl_ing_mt940/__openerp__.py'
--- account_banking_nl_ing_mt940/__openerp__.py	1970-01-01 00:00:00 +0000
+++ account_banking_nl_ing_mt940/__openerp__.py	2014-02-28 13:34:37 +0000
@@ -0,0 +1,48 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+#    OpenERP, Open Source Management Solution
+#    This module copyright (C) 2014 Therp BV (<http://therp.nl>).
+#
+#    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 import for Dutch ING",
+    "version" : "1.0",
+    "author" : "Therp BV",
+    "complexity": "normal",
+    "description": """
+This addon imports the structured MT940 format as offered by the Dutch ING
+bank.
+    """,
+    "category" : "Account Banking",
+    "depends" : [
+        'account_banking_mt940',
+    ],
+    "data" : [
+    ],
+    "js": [
+    ],
+    "css": [
+    ],
+    "qweb": [
+    ],
+    "auto_install": False,
+    "installable": True,
+    "application": False,
+    "external_dependencies" : {
+        'python' : [],
+    },
+}
 
=== added file 'account_banking_nl_ing_mt940/account_banking_nl_ing_mt940.py'
--- account_banking_nl_ing_mt940/account_banking_nl_ing_mt940.py	1970-01-01 00:00:00 +0000
+++ account_banking_nl_ing_mt940/account_banking_nl_ing_mt940.py	2014-02-28 13:34:37 +0000
@@ -0,0 +1,100 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+#    OpenERP, Open Source Management Solution
+#    This module copyright (C) 2014 Therp BV (<http://therp.nl>).
+#
+#    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
+from openerp.tools.translate import _
+from openerp.addons.account_banking.parsers.models import parser,\
+        mem_bank_transaction
+from openerp.addons.account_banking_mt940.mt940 import MT940, str2float
+
+
+class transaction(mem_bank_transaction):
+    def is_valid(self):
+        '''allow transactions without remote account'''
+        return bool(self.execution_date) and bool(self.transferred_amount)
+
+class IngMT940Parser(MT940, parser):
+    name = _('ING MT940 (structured)')
+    country_code = 'NL'
+    code = 'INT_MT940_STRUC'
+
+    tag_61_regex = re.compile(
+        '^(?P<date>\d{6})(?P<sign>[CD])(?P<amount>\d+,\d{2})N(?P<type>\d{3})'
+        '(?P<reference>\w{1,16})')
+
+    def create_transaction(self, cr):
+        return transaction()
+
+    def handle_tag_60F(self, cr, data):
+        super(IngMT940Parser, self).handle_tag_60F(cr, data)
+        self.current_statement.id = '%s-%s' % (
+            self.get_unique_account_identifier(
+                cr, self.current_statement.local_account),
+            self.current_statement.id)
+
+    def handle_tag_61(self, cr, data):
+        super(IngMT940Parser, self).handle_tag_61(cr, data)
+        parsed_data = self.tag_61_regex.match(data).groupdict()
+        self.current_transaction.transferred_amount = \
+            (-1 if parsed_data['sign'] == 'D' else 1) * str2float(
+                parsed_data['amount'])
+        self.current_transaction.reference = parsed_data['reference']
+
+    def handle_tag_86(self, cr, data):
+        if not self.current_transaction:
+            return
+        super(IngMT940Parser, self).handle_tag_86(cr, data)
+        codewords = ['RTRN', 'BENM', 'ORDP', 'CSID', 'BUSP', 'MARF', 'EREF',
+                     'PREF', 'REMI', 'ID', 'PURP', 'ULTB', 'ULTD']
+        subfields = {}
+        current_codeword = None
+        for word in data.split('/'):
+            if not word and not current_codeword:
+                continue
+            if word in codewords:
+                current_codeword = word
+                subfields[current_codeword] = []
+                continue
+            subfields[current_codeword].append(word)
+
+        if 'BENM' in subfields:
+            self.current_transaction.remote_account = subfields['BENM'][0]
+            self.current_transaction.remote_bank_bic = subfields['BENM'][1]
+            self.current_transaction.remote_owner = subfields['BENM'][2]
+            self.current_transaction.remote_owner_city = subfields['BENM'][3]
+
+        if 'ORDP' in subfields:
+            self.current_transaction.remote_account = subfields['ORDP'][0]
+            self.current_transaction.remote_bank_bic = subfields['ORDP'][1]
+            self.current_transaction.remote_owner = subfields['ORDP'][2]
+            self.current_transaction.remote_owner_city = subfields['ORDP'][3]
+
+        if 'REMI' in subfields:
+            self.current_transaction.message = '/'.join(
+                filter(lambda x: bool(x), subfields['REMI']))
+
+        if self.current_transaction.reference in subfields:
+            self.current_transaction.reference = ''.join(
+                subfields[self.current_transaction.reference])
+
+        if not subfields:
+            self.current_transaction.message = data
+            
+        self.current_transaction = None
 
=== added directory 'account_banking_nl_ing_mt940/static'
=== added directory 'account_banking_nl_ing_mt940/static/src'
=== added directory 'account_banking_nl_ing_mt940/static/src/img'
=== added file 'account_banking_nl_ing_mt940/static/src/img/icon.png'
 Binary files account_banking_nl_ing_mt940/static/src/img/icon.png	1970-01-01 00:00:00 +0000 and account_banking_nl_ing_mt940/static/src/img/icon.png	2014-02-28 13:34:37 +0000 differ