def get_or_create_bank(pool, cr, uid, bic, online=False, code=None, name=None): ''' Find or create the bank with the provided BIC code. When online, the SWIFT database will be consulted in order to provide for missing information. ''' # UPDATE: Free SWIFT databases are since 2/22/2010 hidden behind an # image challenge/response interface. bank_obj = pool.get('res.bank') # Self generated key? if len(bic) < 8: # search key bank_ids = bank_obj.search(cr, uid, [('bic', '=', bic[:6])]) if not bank_ids: bank_ids = bank_obj.search(cr, uid, [('bic', 'ilike', bic + '%')]) else: bank_ids = bank_obj.search(cr, uid, [('bic', '=', bic)]) if bank_ids and len(bank_ids) == 1: banks = bank_obj.browse(cr, uid, bank_ids) return banks[0].id, banks[0].country.id country_obj = pool.get('res.country') country_ids = country_obj.search(cr, uid, [('code', '=', bic[4:6])]) country_id = country_ids and country_ids[0] or False bank_id = False if online: info, address = bank_obj.online_bank_info(cr, uid, bic, context=context) if info: bank_id = bank_obj.create( cr, uid, dict( code=info.code, name=info.name, street=address.street, street2=address.street2, zip=address.zip, city=address.city, country=country_id, bic=info.bic[:8], )) else: info = struct(name=name, code=code) if not online or not bank_id: bank_id = bank_obj.create( cr, uid, dict( code=info.code or 'UNKNOW', name=info.name or _('Unknown Bank'), country=country_id, bic=bic, )) return bank_id, country_id
def get_iban_bic_NL(bank_acc): ''' Consult the Dutch online banking database to check both the account number and the bank to which it belongs. Will not work offline, is limited to banks operating in the Netherlands and will only convert Dutch local account numbers. ''' # sanity check: Dutch 7 scheme uses ING as sink and online convertor # calculates accounts, so no need to consult it - calculate our own number = bank_acc.lstrip('0') if len(number) <= 7: iban = IBAN.create(BBAN='INGB' + number.rjust(10, '0'), countrycode='NL' ) return struct( iban = iban.replace(' ',''), account = iban.BBAN[4:], bic = 'INGBNL2A', code = 'INGBNL', bank = 'ING Bank N.V.', country_id = 'NL', ) data = urllib.urlencode(dict(number=number, method='POST')) request = urllib2.Request(IBANlink_NL, data) response = urllib2.urlopen(request) soup = BeautifulSoup(response) result = struct() for _pass, td in enumerate(soup.findAll('td')): if _pass % 2 == 1: result[attr] = unicode(td.find('font').contents[0]) else: attr = td.find('strong').contents[0][:4].strip().lower() if result: result.account = bank_acc result.country_id = result.bic[4:6] # Nationalized bank code result.code = result.bic[:6] # All Dutch banks use generic channels # result.bic += 'XXX' return result return None
def get_iban_bic_NL(bank_acc): ''' Consult the Dutch online banking database to check both the account number and the bank to which it belongs. Will not work offline, is limited to banks operating in the Netherlands and will only convert Dutch local account numbers. ''' # sanity check: Dutch 7 scheme uses ING as sink and online convertor # calculates accounts, so no need to consult it - calculate our own number = bank_acc.lstrip('0') if len(number) <= 7: iban = IBAN.create(BBAN='INGB' + number.rjust(10, '0'), countrycode='NL') return struct( iban=iban.replace(' ', ''), account=iban.BBAN[4:], bic='INGBNL2A', code='INGBNL', bank='ING Bank N.V.', country_id='NL', ) data = urllib.urlencode(dict(number=number, method='POST')) request = urllib2.Request(IBANlink_NL, data) response = urllib2.urlopen(request) soup = BeautifulSoup(response) result = struct() for _pass, td in enumerate(soup.findAll('td')): if _pass % 2 == 1: result[attr] = unicode(td.find('font').contents[0]) else: attr = td.find('strong').contents[0][:4].strip().lower() if result: result.account = bank_acc result.country_id = result.bic[4:6] # Nationalized bank code result.code = result.bic[:6] # All Dutch banks use generic channels # result.bic += 'XXX' return result return None
def harvest(soup): retval = struct() for trsoup in soup('tr'): for stage, tdsoup in enumerate(trsoup('td')): if stage == 0: attr = tdsoup.contents[0].strip().replace(' ','_') elif stage == 2: if tdsoup.contents: retval[attr] = tdsoup.contents[0].strip() else: retval[attr] = '' return retval
def harvest(soup): retval = struct() for trsoup in soup('tr'): for stage, tdsoup in enumerate(trsoup('td')): if stage == 0: attr = tdsoup.contents[0].strip().replace(' ', '_') elif stage == 2: if tdsoup.contents: retval[attr] = tdsoup.contents[0].strip() else: retval[attr] = '' return retval
def create_bank_account(pool, cr, uid, partner_id, account_number, holder_name, address, city, country_id, bic=False, context=None): ''' Create a matching bank account with this holder for this partner. ''' values = struct( partner_id=partner_id, owner_name=holder_name, country_id=country_id, ) # Are we dealing with IBAN? iban = sepa.IBAN(account_number) if iban.valid: # Take as much info as possible from IBAN values.state = 'iban' values.acc_number = str(iban) else: # No, try to convert to IBAN values.state = 'bank' values.acc_number = account_number if country_id: country_code = pool.get('res.country').read( cr, uid, country_id, ['code'], context=context)['code'] if country_code in sepa.IBAN.countries: account_info = pool['res.partner.bank'].online_account_info( cr, uid, country_code, values.acc_number, context=context) if account_info: values.acc_number = iban = account_info.iban values.state = 'iban' bic = account_info.bic if bic: values.bank = get_or_create_bank(pool, cr, uid, bic)[0] values.bank_bic = bic # Create bank account and return return pool.get('res.partner.bank').create(cr, uid, values, context=context)
def iban_lookup(account): proxy = xmlrpclib.ServerProxy('https://ibanconvert.therp.nl') iban, bic, error = proxy.iban_convert(USER, PASS, account) if error or not iban or not bic: logger.error('Error fetching IBAN for %s: %s', account, error) return False return struct( iban=iban.replace(' ', ''), account=account, bic=bic, bank=None, country_id=bic[4:6], code=bic[:6], )
def BBAN_is_IBAN(bank_acc): ''' Intelligent copy, valid for SEPA members who switched to SEPA from old standards before SEPA actually started. ''' if isinstance(bank_acc, IBAN): iban_acc = bank_acc else: iban_acc = IBAN(bank_acc) return struct( iban = str(iban_acc), account = str(bank_acc), country_id = iban_acc.countrycode, code = iban_acc.BIC_searchkey, # Note: BIC can not be constructed here! bic = False, bank = False, )
def BBAN_is_IBAN(bank_acc): ''' Intelligent copy, valid for SEPA members who switched to SEPA from old standards before SEPA actually started. ''' if isinstance(bank_acc, IBAN): iban_acc = bank_acc else: iban_acc = IBAN(bank_acc) return struct( iban=str(iban_acc), account=str(bank_acc), country_id=iban_acc.countrycode, code=iban_acc.BIC_searchkey, # Note: BIC can not be constructed here! bic=False, bank=False, )
def create_bank_account(pool, cr, uid, partner_id, account_number, holder_name, address, city, country_id, bic=False, context=None): ''' Create a matching bank account with this holder for this partner. ''' values = struct( partner_id = partner_id, owner_name = holder_name, country_id = country_id, ) # Are we dealing with IBAN? iban = sepa.IBAN(account_number) if iban.valid: # Take as much info as possible from IBAN values.state = 'iban' values.acc_number = str(iban) else: # No, try to convert to IBAN values.state = 'bank' values.acc_number = account_number if country_id: country_code = pool.get('res.country').read( cr, uid, country_id, ['code'], context=context)['code'] if country_code in sepa.IBAN.countries: account_info = pool['res.partner.bank'].online_account_info( cr, uid, country_code, values.acc_number, context=context) if account_info: values.acc_number = iban = account_info.iban values.state = 'iban' bic = account_info.bic if bic: values.bank = get_or_create_bank(pool, cr, uid, bic)[0] values.bank_bic = bic # Create bank account and return return pool.get('res.partner.bank').create( cr, uid, values, context=context)
def get_iban_bic_BE(bank_acc): ''' Consult the Belgian online database to check both account number and the bank it belongs to. Will not work offline, is limited to banks operating in Belgium and will only convert Belgian local account numbers. ''' def contents(soup, attr): return soup.find('input', { 'id': 'textbox%s' % attr }).get('value').strip() if not bank_acc.strip(): return None # Get empty form with hidden validators agent = URLAgent() request = agent.open(IBANlink_BE) # Isolate form and fill it in soup = BeautifulSoup(request) form = SoupForm(soup.find('form', {'id': 'form1'})) form['textboxBBAN'] = bank_acc.strip() form['Convert'] = 'Convert Number' # Submit the form response = agent.submit(form) # Parse the results soup = BeautifulSoup(response) iban = contents(soup, 'IBAN') if iban.lower().startswith('not a'): return None result = struct(iban=iban.replace(' ', '')) result.bic = contents(soup, 'BIC').replace(' ', '') result.bank = contents(soup, 'BankName') # Add substracts result.account = bank_acc result.country_id = result.bic[4:6] result.code = result.bic[:6] return result
def get_iban_bic_BE(bank_acc): ''' Consult the Belgian online database to check both account number and the bank it belongs to. Will not work offline, is limited to banks operating in Belgium and will only convert Belgian local account numbers. ''' def contents(soup, attr): return soup.find('input', {'id': 'textbox%s' % attr}).get('value').strip() if not bank_acc.strip(): return None # Get empty form with hidden validators agent = URLAgent() request = agent.open(IBANlink_BE) # Isolate form and fill it in soup = BeautifulSoup(request) form = SoupForm(soup.find('form', {'id': 'form1'})) form['textboxBBAN'] = bank_acc.strip() form['Convert'] = 'Convert Number' # Submit the form response = agent.submit(form) # Parse the results soup = BeautifulSoup(response) iban = contents(soup, 'IBAN') if iban.lower().startswith('not a'): return None result = struct(iban=iban.replace(' ', '')) result.bic = contents(soup, 'BIC').replace(' ', '') result.bank = contents(soup, 'BankName') # Add substracts result.account = bank_acc result.country_id = result.bic[4:6] result.code = result.bic[:6] return result
def get_company_bank_account(pool, cr, uid, account_number, currency, company, log): ''' Get the matching bank account for this company. Currency is the ISO code for the requested currency. ''' results = struct() bank_accounts = get_bank_accounts(pool, cr, uid, account_number, log, fail=True) if not bank_accounts: return False elif len(bank_accounts) != 1: log.append( _('More than one bank account was found with the same number %(account_no)s' ) % dict(account_no=account_number)) return False if bank_accounts[0].partner_id.id != company.partner_id.id: log.append( _('Account %(account_no)s is not owned by %(partner)s') % dict( account_no=account_number, partner=company.partner_id.name, )) return False results.account = bank_accounts[0] bank_settings_obj = pool.get('account.banking.account.settings') criteria = [('partner_bank_id', '=', bank_accounts[0].id)] # Find matching journal for currency journal_obj = pool.get('account.journal') journal_ids = journal_obj.search( cr, uid, [('type', '=', 'bank'), ('currency.name', '=', currency or company.currency_id.name)]) if currency == company.currency_id.name: journal_ids_no_curr = journal_obj.search(cr, uid, [('type', '=', 'bank'), ('currency', '=', False)]) journal_ids.extend(journal_ids_no_curr) if journal_ids: criteria.append(('journal_id', 'in', journal_ids)) # Find bank account settings bank_settings_ids = bank_settings_obj.search(cr, uid, criteria) if bank_settings_ids: settings = bank_settings_obj.browse(cr, uid, bank_settings_ids)[0] results.company_id = company results.journal_id = settings.journal_id # Take currency from settings or from company if settings.journal_id.currency.id: results.currency_id = settings.journal_id.currency else: results.currency_id = company.currency_id # Rest just copy/paste from settings. Why am I doing this? results.default_debit_account_id = settings.default_debit_account_id results.default_credit_account_id = settings.default_credit_account_id results.costs_account_id = settings.costs_account_id results.invoice_journal_id = settings.invoice_journal_id results.bank_partner_id = settings.bank_partner_id return results
def import_statements_file(self, cr, uid, ids, context): ''' Import bank statements / bank transactions file. This method is a wrapper for the business logic on the transaction. The parser modules represent the decoding logic. ''' banking_import = self.browse(cr, uid, ids, context)[0] statements_file = banking_import.file data = base64.decodestring(statements_file) user_obj = self.pool.get('res.user') statement_obj = self.pool.get('account.bank.statement') statement_file_obj = self.pool.get('account.banking.imported.file') import_transaction_obj = self.pool.get('banking.import.transaction') period_obj = self.pool.get('account.period') # get the parser to parse the file parser_code = banking_import.parser parser = models.create_parser(parser_code) if not parser: raise orm.except_orm( _('ERROR!'), _('Unable to import parser %(parser)s. Parser class not found.') % {'parser': parser_code} ) # Get the company company = (banking_import.company or user_obj.browse(cr, uid, uid, context).company_id) # Parse the file statements = parser.parse(cr, data) if any([x for x in statements if not x.is_valid()]): raise orm.except_orm( _('ERROR!'), _('The imported statements appear to be invalid! Check your file.') ) # Create the file now, as the statements need to be linked to it import_id = statement_file_obj.create(cr, uid, dict( company_id = company.id, file = statements_file, file_name = banking_import.file_name, state = 'unfinished', format = parser.name, )) bank_country_code = False if hasattr(parser, 'country_code'): bank_country_code = parser.country_code # Results results = struct( stat_loaded_cnt = 0, trans_loaded_cnt = 0, stat_skipped_cnt = 0, trans_skipped_cnt = 0, trans_matched_cnt = 0, bank_costs_invoice_cnt = 0, error_cnt = 0, log = [], ) # Caching error_accounts = {} info = {} imported_statement_ids = [] transaction_ids = [] for statement in statements: if statement.local_account in error_accounts: # Don't repeat messages results.stat_skipped_cnt += 1 results.trans_skipped_cnt += len(statement.transactions) continue # Create fallback currency code currency_code = statement.local_currency or company.currency_id.name # Check cache for account info/currency if statement.local_account in info and \ currency_code in info[statement.local_account]: account_info = info[statement.local_account][currency_code] else: # Pull account info/currency account_info = banktools.get_company_bank_account( self.pool, cr, uid, statement.local_account, statement.local_currency, company, results.log ) if not account_info: results.log.append( _('Statements found for unknown account %(bank_account)s') % {'bank_account': statement.local_account} ) error_accounts[statement.local_account] = True results.error_cnt += 1 continue if 'journal_id' not in account_info.keys(): results.log.append( _('Statements found for account %(bank_account)s, ' 'but no default journal was defined.' ) % {'bank_account': statement.local_account} ) error_accounts[statement.local_account] = True results.error_cnt += 1 continue # Get required currency code currency_code = account_info.currency_id.name # Cache results if not statement.local_account in info: info[statement.local_account] = { currency_code: account_info } else: info[statement.local_account][currency_code] = account_info # Final check: no coercion of currencies! if statement.local_currency \ and account_info.currency_id.name != statement.local_currency: # TODO: convert currencies? results.log.append( _('Statement %(statement_id)s for account %(bank_account)s' ' uses different currency than the defined bank journal.' ) % { 'bank_account': statement.local_account, 'statement_id': statement.id } ) error_accounts[statement.local_account] = True results.error_cnt += 1 continue # Check existence of previous statement # Less well defined formats can resort to a # dynamically generated statement identification # (e.g. a datetime string of the moment of import) # and have potential duplicates flagged by the # matching procedure statement_ids = statement_obj.search(cr, uid, [ ('name', '=', statement.id), ('date', '=', convert.date2str(statement.date)), ]) if statement_ids: results.log.append( _('Statement %(id)s known - skipped') % { 'id': statement.id } ) continue # Get the period for the statement (as bank statement object checks this) period_ids = period_obj.search( cr, uid, [ ('company_id', '=', company.id), ('date_start', '<=', statement.date), ('date_stop', '>=', statement.date), ('special', '=', False), ], context=context) if not period_ids: results.log.append( _('No period found covering statement date %(date)s, ' 'statement %(id)s skipped') % { 'date': statement.date, 'id': statement.id, } ) continue # Create the bank statement record statement_id = statement_obj.create(cr, uid, dict( name = statement.id, journal_id = account_info.journal_id.id, date = convert.date2str(statement.date), balance_start = statement.start_balance, balance_end_real = statement.end_balance, balance_end = statement.end_balance, state = 'draft', user_id = uid, banking_id = import_id, company_id = company.id, period_id = period_ids[0], )) imported_statement_ids.append(statement_id) subno = 0 for transaction in statement.transactions: subno += 1 if not transaction.id: transaction.id = str(subno) values = {} for attr in transaction.__slots__ + ['type']: if attr in import_transaction_obj.column_map: values[import_transaction_obj.column_map[attr]] = eval('transaction.%s' % attr) elif attr in import_transaction_obj._columns: values[attr] = eval('transaction.%s' % attr) values['statement_id'] = statement_id values['bank_country_code'] = bank_country_code values['local_account'] = statement.local_account values['local_currency'] = statement.local_currency transaction_id = import_transaction_obj.create( cr, uid, values, context=context) transaction_ids.append(transaction_id) results.stat_loaded_cnt += 1 import_transaction_obj.match(cr, uid, transaction_ids, results=results, context=context) #recompute statement end_balance for validation statement_obj.button_dummy( cr, uid, imported_statement_ids, context=context) # Original code. Didn't take workflow logistics into account... # #cr.execute( # "UPDATE payment_order o " # "SET state = 'done', " # "date_done = '%s' " # "FROM payment_line l " # "WHERE o.state = 'sent' " # "AND o.id = l.order_id " # "AND l.id NOT IN (" # "SELECT DISTINCT id FROM payment_line " # "WHERE date_done IS NULL " # "AND id IN (%s)" # ")" % ( # time.strftime('%Y-%m-%d'), # ','.join([str(x) for x in payment_line_ids]) # ) #) report = [ '%s: %s' % (_('Total number of statements'), results.stat_skipped_cnt + results.stat_loaded_cnt), '%s: %s' % (_('Total number of transactions'), results.trans_skipped_cnt + results.trans_loaded_cnt), '%s: %s' % (_('Number of errors found'), results.error_cnt), '%s: %s' % (_('Number of statements skipped due to errors'), results.stat_skipped_cnt), '%s: %s' % (_('Number of transactions skipped due to errors'), results.trans_skipped_cnt), '%s: %s' % (_('Number of statements loaded'), results.stat_loaded_cnt), '%s: %s' % (_('Number of transactions loaded'), results.trans_loaded_cnt), '%s: %s' % (_('Number of transactions matched'), results.trans_matched_cnt), '%s: %s' % (_('Number of bank costs invoices created'), results.bank_costs_invoice_cnt), '', '%s:' % (_('Error report')), '', ] text_log = '\n'.join(report + results.log) state = results.error_cnt and 'error' or 'ready' statement_file_obj.write(cr, uid, import_id, dict( state = state, log = text_log, ), context) if not imported_statement_ids or not results.trans_loaded_cnt: # file state can be 'ready' while import state is 'error' state = 'error' self.write(cr, uid, [ids[0]], dict( import_id = import_id, log = text_log, state = state, statement_ids = [(6, 0, imported_statement_ids)], ), context) return { 'name': (state == 'ready' and _('Review Bank Statements') or _('Error')), 'view_type': 'form', 'view_mode': 'form', 'view_id': False, 'res_model': self._name, 'domain': [], 'context': dict(context, active_ids=ids), 'type': 'ir.actions.act_window', 'target': 'new', 'res_id': ids[0] or False, }
def get_company_bank_account(pool, cr, uid, account_number, currency, company, log): ''' Get the matching bank account for this company. Currency is the ISO code for the requested currency. ''' results = struct() bank_accounts = get_bank_accounts(pool, cr, uid, account_number, log, fail=True) if not bank_accounts: return False elif len(bank_accounts) != 1: log.append( _('More than one bank account was found with the same number %(account_no)s') % dict(account_no = account_number) ) return False if bank_accounts[0].partner_id.id != company.partner_id.id: log.append( _('Account %(account_no)s is not owned by %(partner)s') % dict(account_no = account_number, partner = company.partner_id.name, )) return False results.account = bank_accounts[0] bank_settings_obj = pool.get('account.banking.account.settings') criteria = [('partner_bank_id', '=', bank_accounts[0].id)] # Find matching journal for currency journal_obj = pool.get('account.journal') journal_ids = journal_obj.search(cr, uid, [ ('type', '=', 'bank'), ('currency.name', '=', currency or company.currency_id.name) ]) if currency == company.currency_id.name: journal_ids_no_curr = journal_obj.search(cr, uid, [ ('type', '=', 'bank'), ('currency', '=', False) ]) journal_ids.extend(journal_ids_no_curr) if journal_ids: criteria.append(('journal_id', 'in', journal_ids)) # Find bank account settings bank_settings_ids = bank_settings_obj.search(cr, uid, criteria) if bank_settings_ids: settings = bank_settings_obj.browse(cr, uid, bank_settings_ids)[0] results.company_id = company results.journal_id = settings.journal_id # Take currency from settings or from company if settings.journal_id.currency.id: results.currency_id = settings.journal_id.currency else: results.currency_id = company.currency_id # Rest just copy/paste from settings. Why am I doing this? results.default_debit_account_id = settings.default_debit_account_id results.default_credit_account_id = settings.default_credit_account_id results.costs_account_id = settings.costs_account_id results.invoice_journal_id = settings.invoice_journal_id results.bank_partner_id = settings.bank_partner_id return results
def bank_info(bic): ''' Consult the free online SWIFT service to obtain the name and address of a bank. This call may take several seconds to complete, due to the number of requests to make. In total three HTTP requests are made per function call. In theory one request could be stripped, but the SWIFT terms of use prevent automated usage, so user like behavior is required. Update January 2012: Always return None, as the SWIFT page to retrieve the information does no longer exist. If demand exists, maybe bite the bullet and integrate with a paid web service such as http://www.iban-rechner.de. lp914922 additionally suggests to make online lookup optional. ''' return None, None def harvest(soup): retval = struct() for trsoup in soup('tr'): for stage, tdsoup in enumerate(trsoup('td')): if stage == 0: attr = tdsoup.contents[0].strip().replace(' ', '_') elif stage == 2: if tdsoup.contents: retval[attr] = tdsoup.contents[0].strip() else: retval[attr] = '' return retval # Get form agent = URLAgent() request = agent.open(SWIFTlink) soup = BeautifulSoup(request) # Parse request form. As this form is intertwined with a table, use the parent # as root to search for form elements. form = SoupForm(soup.find('form', {'id': 'frmFreeSearch1'}), parent=True) # Fill form fields form['selected_bic'] = bic # Get intermediate response response = agent.submit(form) # Parse response soup = BeautifulSoup(response) # Isolate the full 11 BIC - there may be more, but we only use the first bic_button = soup.find('a', {'class': 'bigbuttonblack'}) if not bic_button: return None, None # Overwrite the location with 'any' ('XXX') to narrow the results to one or less. # Assume this regexp will never fail... full_bic = bic_re.match(bic_button.get('href')).groups()[0][:8] + 'XXX' # Get the detail form form = SoupForm(soup.find('form', {'id': 'frmDetail'})) # Fill detail fields form['selected_bic11'] = full_bic # Get final response response = agent.submit(form) soup = BeautifulSoup(response) # Now parse the results tables = soup.find('div', {'id': 'Middle'}).findAll('table') if not tables: return None, None tablesoup = tables[2]('table') if not tablesoup: return None, None codes = harvest(tablesoup[0]) if not codes: return None, None bankinfo = struct( # Most banks use the first four chars of the BIC as an identifier for # their 'virtual bank' accross the world, containing all national # banks world wide using the same name. # The concatenation with the two character country code is for most # national branches sufficient as a unique identifier. code=full_bic[:6], bic=full_bic, name=codes.Institution_name, ) address = harvest(tablesoup[1]) # The address in the SWIFT database includes a postal code. # We need to split it into two fields... if not address.Zip_Code: if address.Location: iso, address.Zip_Code, address.Location = \ postalcode.split(address.Location, full_bic[4:6]) bankaddress = struct( street=address.Address.title(), city=address.Location.strip().title(), zip=address.Zip_Code, country=address.Country.title(), country_id=full_bic[4:6], ) if ' ' in bankaddress.street: bankaddress.street, bankaddress.street2 = [ x.strip() for x in bankaddress.street.split(' ', 1) ] else: bankaddress.street2 = '' return bankinfo, bankaddress
def import_statements_file(self, cr, uid, ids, context): ''' Import bank statements / bank transactions file. This method is a wrapper for the business logic on the transaction. The parser modules represent the decoding logic. ''' banking_import = self.browse(cr, uid, ids, context)[0] statements_file = banking_import.file data = base64.decodestring(statements_file) user_obj = self.pool.get('res.user') statement_obj = self.pool.get('account.bank.statement') statement_file_obj = self.pool.get('account.banking.imported.file') import_transaction_obj = self.pool.get('banking.import.transaction') period_obj = self.pool.get('account.period') # get the parser to parse the file parser_code = banking_import.parser parser = models.create_parser(parser_code) if not parser: raise osv.except_osv( _('ERROR!'), _('Unable to import parser %(parser)s. Parser class not found.' ) % {'parser': parser_code}) # Get the company company = (banking_import.company or user_obj.browse(cr, uid, uid, context).company_id) # Parse the file statements = parser.parse(cr, data) if any([x for x in statements if not x.is_valid()]): raise osv.except_osv( _('ERROR!'), _('The imported statements appear to be invalid! Check your file.' )) # Create the file now, as the statements need to be linked to it import_id = statement_file_obj.create( cr, uid, dict( company_id=company.id, file=statements_file, file_name=banking_import.file_name, state='unfinished', format=parser.name, )) bank_country_code = False if hasattr(parser, 'country_code'): bank_country_code = parser.country_code # Results results = struct( stat_loaded_cnt=0, trans_loaded_cnt=0, stat_skipped_cnt=0, trans_skipped_cnt=0, trans_matched_cnt=0, bank_costs_invoice_cnt=0, error_cnt=0, log=[], ) # Caching error_accounts = {} info = {} imported_statement_ids = [] transaction_ids = [] for statement in statements: if statement.local_account in error_accounts: # Don't repeat messages results.stat_skipped_cnt += 1 results.trans_skipped_cnt += len(statement.transactions) continue # Create fallback currency code currency_code = statement.local_currency or company.currency_id.name # Check cache for account info/currency if statement.local_account in info and \ currency_code in info[statement.local_account]: account_info = info[statement.local_account][currency_code] else: # Pull account info/currency account_info = banktools.get_company_bank_account( self.pool, cr, uid, statement.local_account, statement.local_currency, company, results.log) if not account_info: results.log.append( _('Statements found for unknown account %(bank_account)s' ) % {'bank_account': statement.local_account}) error_accounts[statement.local_account] = True results.error_cnt += 1 continue if 'journal_id' not in account_info.keys(): results.log.append( _('Statements found for account %(bank_account)s, ' 'but no default journal was defined.') % {'bank_account': statement.local_account}) error_accounts[statement.local_account] = True results.error_cnt += 1 continue # Get required currency code currency_code = account_info.currency_id.name # Cache results if not statement.local_account in info: info[statement.local_account] = { currency_code: account_info } else: info[statement.local_account][currency_code] = account_info # Final check: no coercion of currencies! if statement.local_currency \ and account_info.currency_id.name != statement.local_currency: # TODO: convert currencies? results.log.append( _('Statement %(statement_id)s for account %(bank_account)s' ' uses different currency than the defined bank journal.' ) % { 'bank_account': statement.local_account, 'statement_id': statement.id }) error_accounts[statement.local_account] = True results.error_cnt += 1 continue # Check existence of previous statement # Less well defined formats can resort to a # dynamically generated statement identification # (e.g. a datetime string of the moment of import) # and have potential duplicates flagged by the # matching procedure statement_ids = statement_obj.search(cr, uid, [ ('name', '=', statement.id), ('date', '=', convert.date2str(statement.date)), ]) if statement_ids: results.log.append( _('Statement %(id)s known - skipped') % {'id': statement.id}) continue # Get the period for the statement (as bank statement object checks this) period_ids = period_obj.search( cr, uid, [ ('company_id', '=', company.id), ('date_start', '<=', statement.date), ('date_stop', '>=', statement.date), ('special', '=', False), ], context=context) if not period_ids: results.log.append( _('No period found covering statement date %(date)s, ' 'statement %(id)s skipped') % { 'date': statement.date, 'id': statement.id, }) continue # Create the bank statement record statement_id = statement_obj.create( cr, uid, dict( name=statement.id, journal_id=account_info.journal_id.id, date=convert.date2str(statement.date), balance_start=statement.start_balance, balance_end_real=statement.end_balance, balance_end=statement.end_balance, state='draft', user_id=uid, banking_id=import_id, company_id=company.id, period_id=period_ids[0], )) imported_statement_ids.append(statement_id) subno = 0 for transaction in statement.transactions: subno += 1 if not transaction.id: transaction.id = str(subno) values = {} for attr in transaction.__slots__ + ['type']: if attr in import_transaction_obj.column_map: values[import_transaction_obj.column_map[attr]] = eval( 'transaction.%s' % attr) elif attr in import_transaction_obj._columns: values[attr] = eval('transaction.%s' % attr) values['statement_id'] = statement_id values['bank_country_code'] = bank_country_code values['local_account'] = statement.local_account values['local_currency'] = statement.local_currency transaction_id = import_transaction_obj.create(cr, uid, values, context=context) transaction_ids.append(transaction_id) results.stat_loaded_cnt += 1 import_transaction_obj.match(cr, uid, transaction_ids, results=results, context=context) #recompute statement end_balance for validation statement_obj.button_dummy(cr, uid, imported_statement_ids, context=context) # Original code. Didn't take workflow logistics into account... # #cr.execute( # "UPDATE payment_order o " # "SET state = 'done', " # "date_done = '%s' " # "FROM payment_line l " # "WHERE o.state = 'sent' " # "AND o.id = l.order_id " # "AND l.id NOT IN (" # "SELECT DISTINCT id FROM payment_line " # "WHERE date_done IS NULL " # "AND id IN (%s)" # ")" % ( # time.strftime('%Y-%m-%d'), # ','.join([str(x) for x in payment_line_ids]) # ) #) report = [ '%s: %s' % (_('Total number of statements'), results.stat_skipped_cnt + results.stat_loaded_cnt), '%s: %s' % (_('Total number of transactions'), results.trans_skipped_cnt + results.trans_loaded_cnt), '%s: %s' % (_('Number of errors found'), results.error_cnt), '%s: %s' % (_('Number of statements skipped due to errors'), results.stat_skipped_cnt), '%s: %s' % (_('Number of transactions skipped due to errors'), results.trans_skipped_cnt), '%s: %s' % (_('Number of statements loaded'), results.stat_loaded_cnt), '%s: %s' % (_('Number of transactions loaded'), results.trans_loaded_cnt), '%s: %s' % (_('Number of transactions matched'), results.trans_matched_cnt), '%s: %s' % (_('Number of bank costs invoices created'), results.bank_costs_invoice_cnt), '', '%s:' % (_('Error report')), '', ] text_log = '\n'.join(report + results.log) state = results.error_cnt and 'error' or 'ready' statement_file_obj.write(cr, uid, import_id, dict( state=state, log=text_log, ), context) if not imported_statement_ids or not results.trans_loaded_cnt: # file state can be 'ready' while import state is 'error' state = 'error' self.write( cr, uid, [ids[0]], dict( import_id=import_id, log=text_log, state=state, statement_ids=[(6, 0, imported_statement_ids)], ), context) return { 'name': (state == 'ready' and _('Review Bank Statements') or _('Error')), 'view_type': 'form', 'view_mode': 'form', 'view_id': False, 'res_model': self._name, 'domain': [], 'context': dict(context, active_ids=ids), 'type': 'ir.actions.act_window', 'target': 'new', 'res_id': ids[0] or False, }
def bank_info(bic): ''' Consult the free online SWIFT service to obtain the name and address of a bank. This call may take several seconds to complete, due to the number of requests to make. In total three HTTP requests are made per function call. In theory one request could be stripped, but the SWIFT terms of use prevent automated usage, so user like behavior is required. Update January 2012: Always return None, as the SWIFT page to retrieve the information does no longer exist. If demand exists, maybe bite the bullet and integrate with a paid web service such as http://www.iban-rechner.de. lp914922 additionally suggests to make online lookup optional. ''' return None, None def harvest(soup): retval = struct() for trsoup in soup('tr'): for stage, tdsoup in enumerate(trsoup('td')): if stage == 0: attr = tdsoup.contents[0].strip().replace(' ','_') elif stage == 2: if tdsoup.contents: retval[attr] = tdsoup.contents[0].strip() else: retval[attr] = '' return retval # Get form agent = URLAgent() request = agent.open(SWIFTlink) soup = BeautifulSoup(request) # Parse request form. As this form is intertwined with a table, use the parent # as root to search for form elements. form = SoupForm(soup.find('form', {'id': 'frmFreeSearch1'}), parent=True) # Fill form fields form['selected_bic'] = bic # Get intermediate response response = agent.submit(form) # Parse response soup = BeautifulSoup(response) # Isolate the full 11 BIC - there may be more, but we only use the first bic_button = soup.find('a', {'class': 'bigbuttonblack'}) if not bic_button: return None, None # Overwrite the location with 'any' ('XXX') to narrow the results to one or less. # Assume this regexp will never fail... full_bic = bic_re.match(bic_button.get('href')).groups()[0][:8] + 'XXX' # Get the detail form form = SoupForm(soup.find('form', {'id': 'frmDetail'})) # Fill detail fields form['selected_bic11'] = full_bic # Get final response response = agent.submit(form) soup = BeautifulSoup(response) # Now parse the results tables = soup.find('div', {'id':'Middle'}).findAll('table') if not tables: return None, None tablesoup = tables[2]('table') if not tablesoup: return None, None codes = harvest(tablesoup[0]) if not codes: return None, None bankinfo = struct( # Most banks use the first four chars of the BIC as an identifier for # their 'virtual bank' accross the world, containing all national # banks world wide using the same name. # The concatenation with the two character country code is for most # national branches sufficient as a unique identifier. code = full_bic[:6], bic = full_bic, name = codes.Institution_name, ) address = harvest(tablesoup[1]) # The address in the SWIFT database includes a postal code. # We need to split it into two fields... if not address.Zip_Code: if address.Location: iso, address.Zip_Code, address.Location = \ postalcode.split(address.Location, full_bic[4:6]) bankaddress = struct( street = address.Address.title(), city = address.Location.strip().title(), zip = address.Zip_Code, country = address.Country.title(), country_id = full_bic[4:6], ) if ' ' in bankaddress.street: bankaddress.street, bankaddress.street2 = [ x.strip() for x in bankaddress.street.split(' ', 1) ] else: bankaddress.street2 = '' return bankinfo, bankaddress
def get_or_create_bank(pool, cr, uid, bic, online=False, code=None, name=None): ''' Find or create the bank with the provided BIC code. When online, the SWIFT database will be consulted in order to provide for missing information. ''' # UPDATE: Free SWIFT databases are since 2/22/2010 hidden behind an # image challenge/response interface. bank_obj = pool.get('res.bank') # Self generated key? if len(bic) < 8: # search key bank_ids = bank_obj.search( cr, uid, [ ('bic', '=', bic[:6]) ]) if not bank_ids: bank_ids = bank_obj.search( cr, uid, [ ('bic', 'ilike', bic + '%') ]) else: bank_ids = bank_obj.search( cr, uid, [ ('bic', '=', bic) ]) if bank_ids and len(bank_ids) == 1: banks = bank_obj.browse(cr, uid, bank_ids) return banks[0].id, banks[0].country.id country_obj = pool.get('res.country') country_ids = country_obj.search( cr, uid, [('code', '=', bic[4:6])] ) country_id = country_ids and country_ids[0] or False bank_id = False if online: info, address = bank_obj.online_bank_info(cr, uid, bic, context=context) if info: bank_id = bank_obj.create(cr, uid, dict( code = info.code, name = info.name, street = address.street, street2 = address.street2, zip = address.zip, city = address.city, country = country_id, bic = info.bic[:8], )) else: info = struct(name=name, code=code) if not online or not bank_id: bank_id = bank_obj.create(cr, uid, dict( code = info.code or 'UNKNOW', name = info.name or _('Unknown Bank'), country = country_id, bic = bic, )) return bank_id, country_id