Merge lp:~gs.clearcorp/openerp-costa-rica/7.0_l10n_cr_account_banking_cr_promerica into lp:openerp-costa-rica

[Glen Sojo]

[ADD] - new module l10n_cr_account_banking_promerica: module to allow the import of statement from Promerica Bank.

[lp:~gs.clearcorp/openerp-costa-rica/7.0_l10n_cr_account_banking_cr_promerica]

280. By Glen Sojo on 2014-04-08

[FIX] - l10n_cr_account_banking_promerica: added main feature to module description

=== modified file 'l10n_cr_account_banking_cr_davivienda/davivienda_parser.py'
--- l10n_cr_account_banking_cr_davivienda/davivienda_parser.py	2014-03-06 15:45:09 +0000
+++ l10n_cr_account_banking_cr_davivienda/davivienda_parser.py	2014-04-08 22:30:06 +0000
@@ -313,5 +313,3 @@
             output.append( self.parse_stamenent_record( rec ) )
                 
         return output
-
-

=== added directory 'l10n_cr_account_banking_promerica'
=== added file 'l10n_cr_account_banking_promerica/__init__.py'
--- l10n_cr_account_banking_promerica/__init__.py	1970-01-01 00:00:00 +0000
+++ l10n_cr_account_banking_promerica/__init__.py	2014-04-08 22:30:06 +0000
@@ -0,0 +1,27 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+#    Copyright (C) 2011 credativ Ltd (<http://www.credativ.co.uk>).
+#    All Rights Reserved
+#
+#    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/>.
+#
+#    Refractoring and customization by 
+#                 (C) 2009-TODAY CLEARCORP S.A. (<http://clearcorp.co.cr>).
+#
+##############################################################################
+
+import promerica
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
\ No newline at end of file

=== added file 'l10n_cr_account_banking_promerica/__openerp__.py'
--- l10n_cr_account_banking_promerica/__openerp__.py	1970-01-01 00:00:00 +0000
+++ l10n_cr_account_banking_promerica/__openerp__.py	2014-04-08 22:30:06 +0000
@@ -0,0 +1,52 @@
+# -*- coding: utf-8 -*-
+##############################################################################
+#
+#    OpenERP, Open Source Management Solution
+#    Addons modules by CLEARCORP S.A.
+#    Copyright (C) 2009-TODAY CLEARCORP S.A. (<http://clearcorp.co.cr>).
+#
+#    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': 'Promerica Bank Statement Importer',
+    'version' : '1.0',
+    'author' : 'CLEARCORP S.A.',
+    'complexity': 'normal',
+    'description': '''
+Promerica Bank Statement Importer
+=================================
+Parser for Promerica's Bank Statements for the use with account_banking_ccorp module. 
+
+Main Features
+-------------
+* Parse xlsx format bank statements.
+* Serve bank statements to account_banking_ccorp module.
+''',
+    'category': 'Accounting & Finance',
+    'sequence': 3,
+    'website' : 'http://clearcorp.co.cr',
+    'images' : [],
+    'depends' : ['account'],
+    'data' : [],
+    'init_xml' : [],
+    'demo_xml' : [],
+    'update_xml' : [],
+    'test' : [],
+    'auto_install': False,
+    'application': False,
+    'installable': True,
+    'license': 'AGPL-3',
+}

=== added directory 'l10n_cr_account_banking_promerica/i18n'
=== added file 'l10n_cr_account_banking_promerica/i18n/es.po'
--- l10n_cr_account_banking_promerica/i18n/es.po	1970-01-01 00:00:00 +0000
+++ l10n_cr_account_banking_promerica/i18n/es.po	2014-04-08 22:30:06 +0000
@@ -0,0 +1,64 @@
+# Translation of OpenERP Server.
+# This file contains the translation of the following modules:
+#	* l10n_cr_account_banking_promerica
+#
+msgid ""
+msgstr ""
+"Project-Id-Version: OpenERP Server 7.0\n"
+"Report-Msgid-Bugs-To: \n"
+"POT-Creation-Date: 2014-04-07 22:01+0000\n"
+"PO-Revision-Date: 2014-04-07 22:01+0000\n"
+"Last-Translator: <>\n"
+"Language-Team: \n"
+"MIME-Version: 1.0\n"
+"Content-Type: text/plain; charset=UTF-8\n"
+"Content-Transfer-Encoding: \n"
+"Plural-Forms: \n"
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica_parser.py:65
+#, python-format
+msgid "Import Error"
+msgstr "Error de importación"
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica.py:113
+#, python-format
+msgid "Import error"
+msgstr "Error de importación"
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica_parser.py:73
+#: code:addons/l10n_cr_account_banking_promerica/promerica_parser.py:82
+#, python-format
+msgid "Promerica %s from %s to %s"
+msgstr "Promerica %s desde %s hasta %s"
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica.py:125
+#, python-format
+msgid "Importer for Promerica's xslx bank statements"
+msgstr "Importador para extractos bancarios xslx de Promerica"
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica.py:114
+#, python-format
+msgid "Error in import:%s\n"
+"\n"
+"%s"
+msgstr "Error en importación:%s\n"
+"\n"
+"%s"
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica.py:123
+#, python-format
+msgid "Promerica Bank Statement Importer"
+msgstr "Promerica Importador de extractos bancarios"
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica_parser.py:66
+#, python-format
+msgid "The account specified in file does not match with the one selected in wizard."
+msgstr "La cuenta especificada en el archivo no coincide con la seleccionada en el asistente."
+

=== added file 'l10n_cr_account_banking_promerica/i18n/l10n_cr_account_banking_promerica.pot'
--- l10n_cr_account_banking_promerica/i18n/l10n_cr_account_banking_promerica.pot	1970-01-01 00:00:00 +0000
+++ l10n_cr_account_banking_promerica/i18n/l10n_cr_account_banking_promerica.pot	2014-04-08 22:30:06 +0000
@@ -0,0 +1,61 @@
+# Translation of OpenERP Server.
+# This file contains the translation of the following modules:
+#	* l10n_cr_account_banking_promerica
+#
+msgid ""
+msgstr ""
+"Project-Id-Version: OpenERP Server 7.0\n"
+"Report-Msgid-Bugs-To: \n"
+"POT-Creation-Date: 2014-04-07 22:01+0000\n"
+"PO-Revision-Date: 2014-04-07 22:01+0000\n"
+"Last-Translator: <>\n"
+"Language-Team: \n"
+"MIME-Version: 1.0\n"
+"Content-Type: text/plain; charset=UTF-8\n"
+"Content-Transfer-Encoding: \n"
+"Plural-Forms: \n"
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica_parser.py:65
+#, python-format
+msgid "Import Error"
+msgstr ""
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica.py:113
+#, python-format
+msgid "Import error"
+msgstr ""
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica_parser.py:73
+#: code:addons/l10n_cr_account_banking_promerica/promerica_parser.py:82
+#, python-format
+msgid "Promerica %s from %s to %s"
+msgstr ""
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica.py:125
+#, python-format
+msgid "Importer for Promerica's xslx bank statements"
+msgstr ""
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica.py:114
+#, python-format
+msgid "Error in import:%s\n"
+"\n"
+"%s"
+msgstr ""
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica.py:123
+#, python-format
+msgid "Promerica Bank Statement Importer"
+msgstr ""
+
+#. module: l10n_cr_account_banking_promerica
+#: code:addons/l10n_cr_account_banking_promerica/promerica_parser.py:66
+#, python-format
+msgid "The account specified in file does not match with the one selected in wizard."
+msgstr ""

=== added file 'l10n_cr_account_banking_promerica/promerica.py'
--- l10n_cr_account_banking_promerica/promerica.py	1970-01-01 00:00:00 +0000
+++ l10n_cr_account_banking_promerica/promerica.py	2014-04-08 22:30:06 +0000
@@ -0,0 +1,208 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+#    Copyright (C) 2011 credativ Ltd (<http://www.credativ.co.uk>).
+#    All Rights Reserved
+#
+#    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 base64
+import logging
+import StringIO
+from datetime import datetime
+from openpyxl import load_workbook
+from account_banking_ccorp.parsers import models
+from promerica_parser import PromericaParser
+from openerp.osv import osv, fields
+from openerp.tools.translate import _
+
+bt = models.mem_bank_transaction
+logger = logging.getLogger( 'l10n_cr_account_banking_promerica' )
+
+class transaction(models.mem_bank_transaction):
+
+    mapping = {
+        'execution_date' : '',
+        'effective_date' : '',
+        'local_currency' : '',
+        'transfer_type' : '',
+        'reference' : '',
+        'message' : '',
+        'name' : '',
+        'amount': '',
+        'creditmarker': '',
+    }
+
+    def __init__(self, record, *args, **kwargs):
+        '''Transaction creation'''
+        #record is a dictionary, that is the reason to use iteritems().
+        super(transaction, self).__init__(*args, **kwargs)
+        for key, value in record.iteritems():
+            if record.has_key(key):
+                setattr(self, key, record[key])
+
+        if not self.is_valid():
+            logger.info("Invalid: %s", record)
+    
+    def is_valid(self):
+        '''We don't have remote_account so override base'''
+        return (self.execution_date and True) or False
+
+class statement(models.mem_bank_statement):
+    '''Bank statement imported data    '''
+      
+    def _transmission_number(self, record):
+        self.id = record['transref']
+    
+    def _account_number(self, record):
+        self.local_account = record['account_number']
+        self.local_currency = record['currencycode']
+
+    def _statement_number(self, record):
+        self.id = record['id']
+        
+    def _opening_balance(self, record):
+        self.start_balance = float(record['startingbalance'])
+    
+    def _closing_balance(self, record):
+        self.end_balance = float(record['endingbalance'])
+        self.date = record['bookingdate']
+     
+    def _transaction_new(self, record):
+        parser = PromericaParser()
+        sub_record = parser.statement_lines(record)
+        for sub in sub_record:
+            self.transactions.append(transaction(sub))
+
+    def _not_used():
+        logger.info("Didn't use record: %s", record)
+        
+    def _forward_available(self, record):
+        self.end_balance =  float(record['endingbalance'])
+        self.date = record['bookingdate'] 
+    
+    def _execution_date_transferred_amount (self, record):        
+        self.execution_date = record['bookingdate']       
+        self.transferred_amount = float(record['amount'])
+
+    def transaction_info(self, record):
+        '''Add extra information to transaction'''
+        # Additional information for previous transaction
+        if len(self.transactions) < 1:
+            logger.info("Received additional information for non existent transaction:")
+            logger.info(record)
+        else:
+            transaction = self.transactions[-1]
+            transaction.id = ','.join([record[k] for k in ['infoline{0}'.format(i) for i in range(2,5)] if record.has_key(k)])
+
+def raise_error(message, line):
+    raise osv.osv.except_osv(_('Import error'),
+        _('Error in import:%s\n\n%s') % (message, line))
+
+class parser_promerica(models.parser):
+    
+    '''
+        This adds a new parser in the selection options. 
+        When the account is associated to a parser, the following code makes it appear as an option
+    '''
+    code = 'Promerica-Parser'
+    name = _('Promerica Bank Statement Importer')
+    country_code = 'CR'
+    doc = _('''Importer for Promerica's xslx bank statements''')
+
+    def parse(self, cr, statements_file, **kwargs):
+        result = []
+        parser = PromericaParser() 
+        stmnt = statement()
+        
+        # Decode the base64 encoded file
+        data = base64.decodestring(statements_file)
+        
+        # Gather IO from the decoded string
+        file = StringIO.StringIO()
+        file.write(data)
+        
+        # Load workbook from IO
+        workbook = load_workbook(file)
+        worksheet = workbook.get_active_sheet() # Get the first sheet
+        
+        # General Information
+        account_number = worksheet.cell('B7').value # TODO: setup configuration for account number cell
+        starting_balance = worksheet.cell('G13').value
+        date_fields = worksheet.cell('A5').value
+        
+        try:
+            # TODO: gather format from configuration
+            # Get dates using Regular Expression
+            expr = re.compile('[0-9][0-9]\/[0-9][0-9]\/[0-9][0-9][0-9][0-9]')
+            dates = expr.findall(date_fields)
+            
+            # Format date '%Y-%m-%d'
+            date_from_str = datetime.strftime(datetime.strptime(dates[0],'%d/%m/%Y'),'%Y-%m-%d')
+            date_to_str = datetime.strftime(datetime.strptime(dates[1],'%d/%m/%Y'),'%Y-%m-%d')
+        except:
+            logger.error('Not able to read dates from file, this will possibly fail.')
+            date_from_str = False
+            date_to_str = False
+        
+        data_rows = worksheet.rows[13:]
+        data = []
+        for row in data_rows:
+            if reduce(lambda result, value: result and (value is None),
+                      [cell.value for cell in row], True):
+                break
+            data.append([cell.value for cell in row])
+        
+        ending_balance = starting_balance
+        if data:
+            ending_balance = data[len(data)-1][6]
+        
+        # Update kwargs with read values
+        kwargs.update({
+                       'account_number': account_number,
+                       'starting_balance': starting_balance,
+                       'ending_balance': ending_balance,
+                       'date_from_str': date_from_str,
+                       'date_to_str': date_to_str,
+                       })
+        # Parse the data for the header of the stament.amount
+        records = parser.parse_stamenent_record(data, **kwargs)        
+        
+        stmnt._transmission_number(records)
+        stmnt._account_number(records)
+        stmnt._statement_number(records)
+        stmnt._opening_balance(records)
+        stmnt._closing_balance(records)
+        stmnt._forward_available(records)
+        stmnt._execution_date_transferred_amount (records)
+        stmnt._transaction_new(data)
+        
+        #call the method statement_lines in
+        #parser to parse all the lines in file and add to stament.
+                  
+        '''
+        A stament must have a header and transacctions. The method
+        parse_stamenent_record parse the header and the 
+        method _transaction_new parse all the line 
+        (transactions) in the file. 
+        '''
+        if stmnt.is_valid():
+            result.append(stmnt)
+                      
+        return result
+      
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:

=== added file 'l10n_cr_account_banking_promerica/promerica_parser.py'
--- l10n_cr_account_banking_promerica/promerica_parser.py	1970-01-01 00:00:00 +0000
+++ l10n_cr_account_banking_promerica/promerica_parser.py	2014-04-08 22:30:06 +0000
@@ -0,0 +1,179 @@
+#!/usr/bin/env python
+
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+#    Copyright (C) 2011 credativ Ltd (<http://www.credativ.co.uk>).
+#    All Rights Reserved
+#
+#    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 datetime import datetime
+from dateutil import parser
+from pprint import PrettyPrinter
+from copy import copy
+from openerp.osv import osv, fields
+from openerp.tools.translate import _
+
+class PromericaParser(object):
+    """Parser for Promerica's xlsx Bank Statements"""
+    #from_card = False TODO: remove
+
+
+    def statement_record (self, rec, **kwargs):
+        '''** Kwargs parameter is used for a dynamic list of parameters. Due to
+        the fact that not all bank statements include all the necessary information, 
+        so to get it is strictly necessary to gather information from user. 
+        If you need a new parameter, you specify its name and value, using the **kwargs
+        as a dictionary, to extract a value use the respective key'''
+
+        line_dict = {
+            'transref': '', # _transmission_number
+            'account_number': '', #_account_number
+            'statementnr':'', # statement_number
+            'startingbalance': 0.0, #_opening_balance
+            'currencycode': '', #currencycode
+            'endingbalance': 0.0, #_closing_balance
+            'bookingdate': '', #moving_date
+            'amount': 0.0,
+            'id': '',
+        }
+
+        real_account = kwargs['real_account']
+        account_number = kwargs['account_number']
+        date_from = kwargs['date_from_str'] 
+        date_to = kwargs['date_to_str']
+        starting_balance = kwargs['starting_balance']
+        ending_balance = kwargs['ending_balance']
+
+        # Check if account numbers match
+        if real_account != account_number:
+            raise osv.except_osv(_('Import Error'),
+                                 _('The account specified in file does not '
+                                   'match with the one selected in wizard.'))
+
+        line_dict['account_number'] = account_number
+        # Get currency from wizard
+        line_dict['currencycode'] = kwargs['local_currency']
+        # Set name for the new bank statements to be created
+        line_dict['statementnr'] = _('Promerica %s from %s to %s') % \
+        (account_number, date_from, date_to)
+        # transmission_number (Date when done the import)
+        date_obj = datetime.strptime(date_from, '%Y-%m-%d')
+        line_dict['transref'] = date_obj.strftime('%d-%m-%Y %H:%M:%S')
+        line_dict['bookingdate'] = date_obj
+        line_dict['startingbalance'] = starting_balance
+        line_dict['endingbalance'] =  ending_balance
+        line_dict['amount'] = starting_balance + ending_balance
+        line_dict['id'] = _('Promerica %s from %s to %s') % \
+        (account_number, date_from, date_to)
+        
+        return line_dict
+    
+    '''
+    Parse all the lines in the file. Once the header
+    is parser, the next step are the lines.
+    '''     
+    def statement_lines (self, rec):
+        parser = PromericaParser()
+        mapping = {
+            'execution_date' : '',
+            'effective_date' : '',
+            'transfer_type' : '',
+            'reference' : '',
+            'message' : '',
+            'name' : '',
+            'transferred_amount': '',
+            'creditmarker': '',
+        }
+        
+        lines = []
+        currencycode = '' # TODO: remove not used
+        
+        for record in rec:
+            mapping['effective_date'] = record[0]
+            mapping['execution_date'] = record[0]
+            mapping['transfer_type'] = 'NTRF'
+            mapping['reference'] = record[1]
+            mapping['name'] = record[2]
+            mapping['id'] = record[2]
+            mapping['message'] = record[4]
+            
+            #Check if it is debit or credit
+            amount = record[5]
+            mapping['transferred_amount'] = amount
+            if amount < 0:
+                mapping['creditmarker'] = 'D'
+            else:
+                mapping['creditmarker'] = 'C'
+                    
+            lines.append(copy(mapping))
+                            
+        return lines   
+    
+    #clear special characters in a row. 
+    def clean_special_characters(self, text_celd):
+        special_characters = {'\r':''}
+         
+        for i, j in special_characters.iteritems():
+            text = text_celd.replace(i, j)    
+            
+        #remove all the blank space.
+        return re.sub(r'\s', '', text) 
+    
+    """
+    ** Kwargs parameter is used for a dynamic list of parameters. 
+        The wizard imported extracts used in all parsers 
+        and not all parsers have all the necessary information in your file, 
+        so get information from the wizard and passed by the ** kwargs. 
+        Then in the parses that are needed, are extracted from the ** kwargs and if needed, 
+        the parser still works the same way without this parameter.
+        
+        The rest of the methods must receive this parameter. (As the 
+        method that parse the header and the lines). 
+        
+        If you need a new parameter, you specify its name and value, 
+        using the ** kwargs is a dictionary, 
+        extract its value, with the respective key
+    """
+    
+    def parse_stamenent_record( self, rec, **kwargs): #parse the header.
+        matchdict = dict()
+        # Set the header for the statement
+        matchdict = self.statement_record(rec, **kwargs);
+        # TODO: check if we need effective_date or execution_date as date
+        return matchdict
+    
+    #call the method that parse the header and the statements.    
+    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:
+            #parse_stamenent_record parse the header and the statements
+            output.append( self.parse_stamenent_record( rec ) )
+                
+        return output