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
