credativ team mailing list archive

[Kinner Vachhani <kin.vachhani@xxxxxxxxx>]

Kinner Vachhani has proposed merging lp:~credativ/banking-addons/6.1-lloyds-corporate into lp:banking-addons/6.1.

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

For more details, see:
https://code.launchpad.net/~credativ/banking-addons/6.1-lloyds-corporate/+merge/184196

This modules contains a parser of uk lloyds corporate csv and imports as bank statement. 

-- 
https://code.launchpad.net/~credativ/banking-addons/6.1-lloyds-corporate/+merge/184196
Your team credativ is subscribed to branch lp:~credativ/banking-addons/6.1-lloyds-corporate.

=== added directory 'account_banking_uk_lloyds_corporate'
=== added file 'account_banking_uk_lloyds_corporate/__init__.py'
--- account_banking_uk_lloyds_corporate/__init__.py	1970-01-01 00:00:00 +0000
+++ account_banking_uk_lloyds_corporate/__init__.py	2013-09-05 21:56:06 +0000
@@ -0,0 +1,24 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+#    Copyright (C) 2013 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 lloydscorporate
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:

=== added file 'account_banking_uk_lloyds_corporate/__openerp__.py'
--- account_banking_uk_lloyds_corporate/__openerp__.py	1970-01-01 00:00:00 +0000
+++ account_banking_uk_lloyds_corporate/__openerp__.py	2013-09-05 21:56:06 +0000
@@ -0,0 +1,37 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+#    Copyright (C) 2013 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/>.
+#
+##############################################################################
+{
+    'name': 'Account Banking Lloyds Corporate CSV import',
+    'version': '1.0',
+    'license': 'AGPL-3',
+    'author': 'credativ Ltd',
+    'website': 'http://www.credativ.co.uk',
+    'category': 'Account Banking',
+    'depends': ['account_banking'],
+    'init_xml': [],
+    'update_xml': [],
+    'demo_xml': [],
+    'description': '''
+    Module to import bank statement CSV for Bank of Lloyds Corporate.
+    ''',
+    'active': False,
+    'installable': True,
+}

=== added file 'account_banking_uk_lloyds_corporate/lloydscorporate.py'
--- account_banking_uk_lloyds_corporate/lloydscorporate.py	1970-01-01 00:00:00 +0000
+++ account_banking_uk_lloyds_corporate/lloydscorporate.py	2013-09-05 21:56:06 +0000
@@ -0,0 +1,157 @@
+# -*- encoding: utf-8 -*-
+##############################################################################
+#
+#    Copyright (C) 2013 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/>.
+#
+##############################################################################
+# Imports LLoyds Corporate format
+#
+
+from account_banking.parsers import models, convert
+from tools.translate import _
+import re
+import osv
+import logging
+import csv
+from StringIO import StringIO
+from operator import itemgetter
+
+logger = logging.getLogger('lloydscorporate_csv_import')
+
+class CSVTransaction(models.mem_bank_transaction):
+
+    mapping = {
+        'execution_date' : 'date',
+        'effective_date': 'date',
+        'message' : 'description',
+        'name' : 'description',          # Use description as transaction name
+        'balance' : 'Balance',           # Store balance from line for calculating statement balances
+    }
+
+    def __init__(self, record, *args, **kwargs):
+        '''
+        Transaction creation
+        '''
+        super(CSVTransaction, self).__init__(*args, **kwargs)
+
+        # Parse date of format 01APR13
+        record['date'] = convert.str2date(re.sub(r'\W*','',record['Date']), '%d%b%y')
+
+        record['description'] = record['Narrative'].strip()
+
+        # Mapping of simple items
+        for key, value in self.mapping.iteritems():
+            if record.has_key(value):
+                setattr(self, key, record[value])
+
+        # Convert debit/credit to float amount
+        if len(record['Payments'].strip()):
+            self.transferred_amount = record['Payments'] and -float(record['Payments']) or 0.0
+        else:
+            self.transferred_amount = record['Receipts'] and float(record['Receipts']) or 0.0
+
+        # Cheque - set reference
+        transfer_account = re.match(r'\w*\s\d{1,12}$', record['description'])
+        if transfer_account:
+            self.reference = transfer_account.group()
+
+        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 self.transferred_amount and True) or False
+
+class Statement(models.mem_bank_statement):
+
+    def import_statement(self, record):
+        self.transactions.append(CSVTransaction(record))
+
+def raise_error(message, line):
+    raise osv.osv.except_osv(_('Import error'),
+        'Error in import:%s\n\n%s' % (message, line))
+
+class parser(models.parser):
+    code = 'LLOYDSCORPORATE-CSV'
+    name = _('Lloyds Corporate CSV Statement IMPORT')
+    country_code = 'GB'
+    doc = _('''\
+            This format is available through
+            the web interface.
+            ''')
+
+    def parse(self, cr, data):
+        ''' Lloyds corporate CSV parser'''
+
+        data = data.replace('\r','')
+        csv_header = data.split('\n')[0].replace('"', '').split(',')
+        header_list = ['Account', 'Date', 'Type', 'Narrative', 'Value Date', 'Payments', 'Receipts', 'Balance']
+        result = []
+
+        #compare header list and process csv if equal
+        if cmp(csv_header,header_list) != 0:
+            logger.info("Invalid import Statement:")
+            logger.info("Expected Header: %s" %(str(header_list)))
+            logger.info("Header found: %s"%(str(csv_header)))
+            raise osv.osv.except_osv(_('Import error'),
+                'Error in import:%s\n' % (_('Invalid file format')))
+
+        bankdata = StringIO(data)
+        lines = list(csv.DictReader(bankdata))
+        stmnt = Statement()
+        # lines as they are imported
+        if len(lines):
+            #Store opening balance from first record
+            line = lines[0]
+            stmnt.start_balance = line['Balance']
+            account_number = re.sub('\D', '', line['Account'])
+
+            #Assuming if payment and receipts are both null then its opening balance
+            if not (line['Payments'] and line['Receipts']):
+                lines = lines[1:]
+
+            #Skip records which do not contains transaction type
+            for line in lines[:-int(len(lines)-map(itemgetter('Type'), lines).index(''))]:
+                #create Statement lines from csv records
+                stmnt.import_statement(line)
+
+            #Get statement Closing balance from CSV data list
+            try:
+                stmnt.end_balance = lines[map(itemgetter('Narrative'), lines).\
+                                  index('Closing Ledger Balance')]['Balance']
+            except ValueError:
+                raise osv.osv.except_osv(_('Closing Balance'),
+                        _('Statement Closing Balance not found.'))
+
+
+            #GB account number format stored in ERP
+            stmnt.local_account = account_number[:6] +' '+ account_number[6:]
+
+            # Take date of last line of statement
+            stmnt.date = stmnt.transactions[-1].effective_date
+
+        statement_id = self.get_unique_statement_id(
+            cr, stmnt.date.strftime('%Yw%W'))
+        stmnt.id = statement_id
+        result.append(stmnt)
+        return result
+
+
+# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:

=== added file 'account_banking_uk_lloyds_corporate/samplestatement.csv'
--- account_banking_uk_lloyds_corporate/samplestatement.csv	1970-01-01 00:00:00 +0000
+++ account_banking_uk_lloyds_corporate/samplestatement.csv	2013-09-05 21:56:06 +0000
@@ -0,0 +1,14 @@
+"Account","Date","Type","Narrative","Value Date","Payments","Receipts","Balance"
+"210999-40010000 (GBP)",31MAY13,"","Opening Ledger Balance",,,,30000
+"210999-40010000 (GBP)",31MAY13,"BGC","AXELOR  56465463    BGC
+5656546456 30/05","","",20000,50000
+"210999-40010000 (GBP)",31MAY13,"CR","ARGOS
+INVOICES PAID","","",10000,60000
+"210999-40010000 (GBP)",31MAY13,"DR","DEBANHAMS 
+EUROS       307.70","",2000,"",58000
+"210999-40010000 (GBP)",31MAY13,"DR","HOTEL HILTON PARKLANE","",2000,"",56000
+"210999-40010000 (GBP)",31MAY13,"DR","TO 30999999999999     TFR","",6000,"",50000
+"210999-40010000 (GBP)",21JUN13,"","Value of Credits (96)","","",30000,
+"210999-40010000 (GBP)",21JUN13,"","Value of Debits (43)","",10000,,
+"210999-40010000 (GBP)",21JUN13,"","Closing Ledger Balance","","","",50000
+"210999-40010000 (GBP)",21JUN13,"","Closing Cleared Balance","","","",50000