def _set_iban(self, iban):
iban_obj = sepa.IBAN(iban)
if not iban_obj.valid:
raise ValueError("IBAN is invalid: " + str(iban))
self._iban = iban
self.country = iban_obj.countrycode
def create_bank_account(pool, cursor, uid, partner_id, account_number,
holder_name, log):
'''
Create a matching bank account with this holder for this partner.
'''
values = struct(
partner_id=partner_id,
owner_name=holder_name,
)
bankcode = None
bic = None
# Are we dealing with IBAN?
iban = sepa.IBAN(account_number)
if iban.valid:
values.state = 'iban'
values.acc_number = iban.BBAN
bankcode = iban.bankcode + iban.countrycode
else:
# No, try to convert to IBAN
country = pool.get('res.partner').browse(cursor, uid,
partner_id).country_id
values.state = 'bank'
values.acc_number = account_number
if country.code in sepa.IBAN.countries:
account_info = sepa.online.account_info(country.code,
values.acc_number)
if account_info:
values.iban = iban = account_info.iban
values.state = 'iban'
bankcode = account_info.code
bic = account_info.bic
if bic:
values.bank_id = get_or_create_bank(pool, cursor, uid, bic)
elif bankcode:
# Try to link bank
bank_obj = pool.get('res.bank')
bank_ids = bank_obj.search(cursor, uid, [('code', 'ilike', bankcode)])
if bank_ids:
# Check BIC on existing banks
values.bank_id = bank_ids[0]
bank = bank_obj.browse(cursor, uid, values.bank_id)
if not bank.bic:
bank_obj.write(cursor, uid, values.bank_id, dict(bic=bic))
else:
# New bank - create
values.bank_id = bank_obj.create(
cursor,
uid,
dict(
code=account_info.code,
# Only the first eight positions of BIC are used for bank
# transfers, so ditch the rest.
bic=account_info.bic[:8],
name=account_info.bank,
country_id=country.id,
))
# Create bank account and return
return pool.get('res.partner.bank').create(cursor, uid, values)
def import_statements_file(self, cursor, uid, ids, context):
'''
Import bank statements / bank transactions file.
This method represents the business logic, the parser modules
represent the decoding logic.
'''
banking_import = self.browse(cursor, uid, ids, context)[0]
statements_file = banking_import.file
data = base64.decodestring(statements_file)
company_obj = self.pool.get('res.company')
user_obj = self.pool.get('res.user')
partner_bank_obj = self.pool.get('res.partner.bank')
journal_obj = self.pool.get('account.journal')
move_line_obj = self.pool.get('account.move.line')
payment_line_obj = self.pool.get('payment.line')
statement_obj = self.pool.get('account.bank.statement')
statement_line_obj = self.pool.get('account.bank.statement.line')
statement_file_obj = self.pool.get('account.banking.imported.file')
payment_order_obj = self.pool.get('payment.order')
currency_obj = self.pool.get('res.currency')
# 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(cursor, uid, uid, context).company_id)
# Parse the file
statements = parser.parse(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(
cursor, uid,
dict(
company_id=company.id,
file=statements_file,
state='unfinished',
format=parser.name,
))
# 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 = []
linked_payments = {}
linked_invoices = {}
if statements:
# Get default defaults
def_pay_account_id = company.partner_id.property_account_payable.id
def_rec_account_id = company.partner_id.property_account_receivable.id
# Get interesting journals once
journal_ids = journal_obj.search(cursor, uid, [
('type', 'in',
('sale', 'purchase', 'purchase_refund', 'sale_refund')),
('company_id', '=', company.id),
])
# Get all unreconciled moves predating the last statement in one big
# swoop. Assumption: the statements in the file are sorted in ascending
# order of date.
move_line_ids = move_line_obj.search(cursor, uid, [
('reconcile_id', '=', False),
('journal_id', 'in', journal_ids),
('account_id.reconcile', '=', True),
('date', '<=', date2str(statements[-1].date)),
])
if move_line_ids:
move_lines = move_line_obj.browse(cursor, uid, move_line_ids)
else:
move_lines = []
# Get all unreconciled sent payment lines in one big swoop.
# No filtering can be done, as empty dates carry value for C2B
# communication. Most likely there are much less sent payments
# than reconciled and open/draft payments.
cursor.execute("SELECT l.id FROM payment_order o, payment_line l "
"WHERE l.order_id = o.id AND "
"o.state = 'sent' AND "
"l.date_done IS NULL")
payment_line_ids = [x[0] for x in cursor.fetchall()]
if payment_line_ids:
payment_lines = payment_line_obj.browse(
cursor, uid, payment_line_ids)
else:
payment_lines = []
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 = get_company_bank_account(
self.pool, cursor, 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:
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
statement_ids = statement_obj.search(cursor, uid, [
('name', '=', statement.id),
('date', '=', date2str(statement.date)),
])
if statement_ids:
results.log.append(
_('Statement %(id)s known - skipped') %
{'id': statement.id})
continue
statement_id = statement_obj.create(
cursor, uid,
dict(
name=statement.id,
journal_id=account_info.journal_id.id,
date=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,
))
imported_statement_ids.append(statement_id)
# move each transaction to the right period and try to match it with an
# invoice or payment
subno = 0
injected = []
i = 0
max_trans = len(statement.transactions)
while i < max_trans:
move_info = False
if injected:
# Force FIFO behavior
transaction = injected.pop(0)
else:
transaction = statement.transactions[i]
# Keep a tracer for identification of order in a statement in case
# of missing transaction ids.
subno += 1
# Link accounting period
period_id = get_period(self.pool, cursor, uid,
transaction.effective_date, company,
results.log)
if not period_id:
results.trans_skipped_cnt += 1
if not injected:
i += 1
continue
# When bank costs are part of transaction itself, split it.
if transaction.type != bt.BANK_COSTS and transaction.provision_costs:
# Create new transaction for bank costs
costs = transaction.copy()
costs.type = bt.BANK_COSTS
costs.id = '%s-prov' % transaction.id
costs.transferred_amount = transaction.provision_costs
costs.remote_currency = transaction.provision_costs_currency
costs.message = transaction.provision_costs_description
injected.append(costs)
# Remove bank costs from current transaction
# Note that this requires that the transferred_amount
# includes the bank costs and that the costs itself are
# signed correctly.
transaction.transferred_amount -= transaction.provision_costs
transaction.provision_costs = None
transaction.provision_costs_currency = None
transaction.provision_costs_description = None
# Allow inclusion of generated bank invoices
if transaction.type == bt.BANK_COSTS:
lines = self._link_costs(cursor, uid, transaction,
period_id, account_info,
results.log)
results.bank_costs_invoice_cnt += bool(lines)
for line in lines:
if not [x for x in move_lines if x.id == line.id]:
move_lines.append(line)
partner_ids = [account_info.bank_partner_id.id]
partner_banks = []
else:
# Link remote partner, import account when needed
partner_banks = get_bank_accounts(
self.pool,
cursor,
uid,
transaction.remote_account,
results.log,
fail=True)
if partner_banks:
partner_ids = [x.partner_id.id for x in partner_banks]
elif transaction.remote_owner:
iban = sepa.IBAN(transaction.remote_account)
if iban.valid:
country_code = iban.countrycode
elif transaction.remote_owner_country_code:
country_code = transaction.remote_owner_country_code
elif hasattr(parser,
'country_code') and parser.country_code:
country_code = parser.country_code
else:
country_code = None
partner_id = get_or_create_partner(
self.pool, cursor, uid, transaction.remote_owner,
transaction.remote_owner_address,
transaction.remote_owner_postalcode,
transaction.remote_owner_city, country_code,
results.log)
if transaction.remote_account:
partner_bank_id = create_bank_account(
self.pool, cursor, uid, partner_id,
transaction.remote_account,
transaction.remote_owner,
transaction.remote_owner_address,
transaction.remote_owner_city, country_code,
results.log)
partner_banks = partner_bank_obj.browse(
cursor, uid, [partner_bank_id])
else:
partner_bank_id = None
partner_banks = []
partner_ids = [partner_id]
else:
partner_ids = []
partner_banks = []
# Credit means payment... isn't it?
if transaction.transferred_amount < 0 and payment_lines:
# Link open payment - if any
move_info = self._link_payment(
cursor,
uid,
transaction,
payment_lines,
partner_ids,
partner_banks,
results.log,
linked_payments,
)
# Second guess, invoice -> may split transaction, so beware
if not move_info:
# Link invoice - if any. Although bank costs are not an
# invoice, automatic invoicing on bank costs will create
# these, and invoice matching still has to be done.
move_info, remainder = self._link_invoice(
cursor,
uid,
transaction,
move_lines,
partner_ids,
partner_banks,
results.log,
linked_invoices,
)
if remainder:
injected.append(remainder)
if not move_info:
# Use the default settings, but allow individual partner
# settings to overrule this. Note that you need to change
# the internal type of these accounts to either 'payable'
# or 'receivable' to enable usage like this.
if transaction.transferred_amount < 0:
if len(partner_banks) == 1:
account_id = partner_banks[
0].partner_id.property_account_payable
if len(
partner_banks
) != 1 or not account_id or account_id.id == def_pay_account_id:
account_id = account_info.default_credit_account_id
else:
if len(partner_banks) == 1:
account_id = partner_banks[
0].partner_id.property_account_receivable
if len(
partner_banks
) != 1 or not account_id or account_id.id == def_rec_account_id:
account_id = account_info.default_debit_account_id
else:
account_id = move_info.move_line.account_id
results.trans_matched_cnt += 1
values = struct(
name='%s.%s' % (statement.id, transaction.id or subno),
date=transaction.effective_date,
amount=transaction.transferred_amount,
account_id=account_id.id,
statement_id=statement_id,
note=transaction.message,
ref=transaction.reference,
period_id=period_id,
currency=account_info.currency_id.id,
)
if move_info:
values.type = move_info.type
values.reconcile_id = move_info.move_line.reconcile_id
values.partner_id = move_info.partner_id
values.partner_bank_id = move_info.partner_bank_id
else:
values.partner_id = values.partner_bank_id = False
if not values.partner_id and partner_ids and len(
partner_ids) == 1:
values.partner_id = partner_ids[0]
if not values.partner_bank_id and partner_banks and \
len(partner_banks) == 1:
values.partner_bank_id = partner_banks[0].id
statement_line_id = statement_line_obj.create(
cursor, uid, values)
results.trans_loaded_cnt += 1
# Only increase index when all generated transactions are
# processed as well
if not injected:
i += 1
results.stat_loaded_cnt += 1
if payment_lines:
# As payments lines are treated as individual transactions, the
# batch as a whole is only marked as 'done' when all payment lines
# have been reconciled.
cursor.execute("SELECT DISTINCT o.id "
"FROM payment_order o, payment_line l "
"WHERE o.state = 'sent' "
"AND o.id = l.order_id "
"AND o.id NOT IN ("
"SELECT DISTINCT order_id AS id "
"FROM payment_line "
"WHERE date_done IS NULL "
"AND id IN (%s)"
")" % (','.join([str(x)
for x in payment_line_ids])))
order_ids = [x[0] for x in cursor.fetchall()]
if order_ids:
# Use workflow logics for the orders. Recode logic from
# account_payment, in order to increase efficiency.
payment_order_obj.set_done(cursor, uid, order_ids,
{'state': 'done'})
wf_service = netsvc.LocalService('workflow')
for id in order_ids:
wf_service.trg_validate(uid, 'payment.order', id, 'done',
cursor)
# Original code. Didn't take workflow logistics into account...
#
#cursor.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(cursor, uid, import_id,
dict(
state=state,
log=text_log,
), context)
if not imported_statement_ids:
# file state can be 'ready' while import state is 'error'
state = 'error'
self.write(
cursor, uid, [ids[0]],
dict(
import_id=import_id,
log=text_log,
state=state,
statement_ids=[[6, 0, imported_statement_ids]],
), context)
return {
'name': _('Import Bank Transactions File'),
'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 _create_clieop(self, cursor, uid, data, context):
'''
Wizard to actually create the ClieOp3 file
'''
pool = pooler.get_pool(cursor.dbname)
payment_order_obj = pool.get('payment.order')
form = data['form']
clieopfile = None
payment_orders = payment_order_obj.browse(cursor, uid, data['ids'])
for payment_order in payment_orders:
if not clieopfile:
# Just once: create clieop file
our_account_owner = payment_order.mode.bank_id.owner_name
our_account_nr = payment_order.mode.bank_id.acc_number
if not our_account_nr and payment_order.mode.bank_id.iban:
our_account_nr = sepa.IBAN(
payment_order.mode.bank_id.iban).localized_BBAN
if not our_account_nr:
raise wizard.except_wizard(
_('Error'),
_('Your bank account has to have a valid account number'
))
clieopfile = {
'CLIEOPPAY': clieop.PaymentsFile,
'CLIEOPINC': clieop.DirectDebitFile,
'CLIEOPSAL': clieop.SalaryPaymentsFile,
}[form['batchtype']](identification=form['reference'],
execution_date=form['execution_date'],
name_sender=our_account_owner,
accountno_sender=our_account_nr,
test=form['test'])
# As payment_orders can have multiple transactions, create a new batch
# for each payment_order
if form['fixed_message']:
messages = [form['fixed_message']]
else:
messages = []
batch = clieopfile.batch(messages=messages,
batch_id=payment_order.reference)
for line in payment_order.line_ids:
kwargs = dict(
name=line.bank_id.owner_name,
amount=line.amount_currency,
reference=line.communication or None,
)
if line.communication2:
kwargs['messages'] = [line.communication2]
other_account_nr = line.bank_id.acc_number
iban = sepa.IBAN(other_account_nr)
if iban.valid:
if iban.countrycode != 'NL':
raise wizard.except_wizard(
_('Error'),
_('You cannot send international bank transfers '
'through ClieOp3!'))
other_account_nr = iban.localized_BBAN
if form['batchtype'] == 'CLIEOPINC':
kwargs['accountno_beneficiary'] = our_account_nr
kwargs['accountno_payer'] = other_account_nr
else:
kwargs['accountno_beneficiary'] = other_account_nr
kwargs['accountno_payer'] = our_account_nr
transaction = batch.transaction(**kwargs)
# Generate the specifics of this clieopfile
order = clieopfile.order
values = dict(
filetype=order.name_transactioncode,
identification=order.identification,
prefered_date=strfdate(order.preferred_execution_date),
total_amount=int(order.total_amount) / 100.0,
check_no_accounts=order.total_accountnos,
no_transactions=order.nr_posts,
testcode=order.testcode,
file=base64.encodestring(clieopfile.rawdata),
)
form.update(values)
values['daynumber'] = int(clieopfile.header.file_id[2:])
values['payment_order_ids'] = ','.join(map(str, data['ids']))
data['file_id'] = pool.get('banking.export.clieop').create(
cursor, uid, values)
data['clieop'] = clieopfile
form['log'] = ''
return form
def create_bank_account(pool, cursor, uid, partner_id,
account_number, holder_name, address, city,
country_code, log
):
'''
Create a matching bank account with this holder for this partner.
'''
values = struct(
partner_id = partner_id,
owner_name = holder_name,
)
bankcode = None
bic = None
country_obj = pool.get('res.country')
# 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.iban = str(iban)
values.acc_number = iban.BBAN
bankcode = iban.bankcode + iban.countrycode
country_code = iban.countrycode
if not country_code:
country = pool.get('res.partner').browse(
cursor, uid, partner_id).country
country_code = country.code
country_id = country.id
else:
if iban.valid:
country_ids = country_obj.search(cursor, uid,
[('code', '=', iban.countrycode)]
)
else:
country_ids = country_obj.search(cursor, uid,
[('code', '=', country_code)]
)
country_id = country_ids[0]
account_info = False
if not iban.valid:
# No, try to convert to IBAN
values.state = 'bank'
values.acc_number = account_number
if country_code in sepa.IBAN.countries:
account_info = sepa.online.account_info(country_code,
values.acc_number
)
if account_info:
values.iban = iban = account_info.iban
values.state = 'iban'
bankcode = account_info.code
bic = account_info.bic
if bic:
values.bank = get_or_create_bank(pool, cursor, uid, bic)[0]
else:
if not bankcode:
bankcode = "UNKNOW"
# Try to link bank
bank_obj = pool.get('res.bank')
bank_ids = bank_obj.search(cursor, uid, [
('code', 'ilike', bankcode)
])
if bank_ids:
# Check BIC on existing banks
values.bank = bank_ids[0]
bank = bank_obj.browse(cursor, uid, values.bank)
if not bank.bic:
bank_obj.write(cursor, uid, values.bank, dict(bic=bic))
else:
# New bank - create
res = struct(country_id=country_id)
if account_info:
res.code = account_info.code
# Only the first eight positions of BIC are used for bank
# transfers, so ditch the rest.
res.bic = account_info.bic[:8]
res.name = account_info.bank
else:
res.code = bankcode
res.name = _('Unknown Bank')
values.bank = bank_obj.create(cursor, uid, res)
# Create bank account and return
return pool.get('res.partner.bank').create(cursor, uid, values)