def _banking_import_statements_file(self, cursor, uid, data, context):
"""
Import bank statements / bank transactions file.
This module/function represents the business logic, the parser modules
represent the decoding logic.
"""
form = data["form"]
statements_file = form["file"]
data = base64.decodestring(statements_file)
pool = pooler.get_pool(cursor.dbname)
company_obj = pool.get("res.company")
user_obj = pool.get("res.user")
journal_obj = pool.get("account.journal")
move_line_obj = pool.get("account.move.line")
payment_line_obj = pool.get("payment.line")
statement_obj = pool.get("account.bank.statement")
statement_line_obj = pool.get("account.bank.statement.line")
statement_file_obj = pool.get("account.banking.imported.file")
# account_obj = pool.get('account.account')
# payment_order_obj = pool.get('payment.order')
# currency_obj = pool.get('res.currency')
# get the parser to parse the file
parser_code = form["parser"]
parser = models.create_parser(parser_code)
if not parser:
raise wizard.except_wizard(
_("ERROR!"), _("Unable to import parser %(parser)s. Parser class not found.") % {"parser": parser_code}
)
# Get the company
company = form["company"]
if not company:
user_data = user_obj.browse(cursor, uid, uid, context)
company = company_obj.browse(cursor, uid, company or user_data.company_id.id, context)
# Parse the file
statements = parser.parse(data)
if any([x for x in statements if not x.is_valid()]):
raise wizard.except_wizard(_("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, date=time.strftime("%Y-%m-%d"), user_id=uid, state="unfinished"
),
)
# Results
no_stat_loaded = 0
no_trans_loaded = 0
no_stat_skipped = 0
no_trans_skipped = 0
no_trans_matched = 0
no_errors = 0
log = []
# Caching
error_accounts = {}
info = {}
imported_statement_ids = []
if statements:
# Get interesting journals once
if company:
journal_ids = journal_obj.search(
cursor, uid, [("type", "in", ("sale", "purchase")), ("company_id", "=", company.id)]
)
else:
journal_ids = None
if not journal_ids:
journal_ids = journal_obj.search(
cursor, uid, [("type", "in", ("sale", "purchase")), ("active", "=", True), ("company_id", "=", False)]
)
# 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)),
],
)
move_lines = move_line_obj.browse(cursor, uid, move_line_ids)
# 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
no_stat_skipped += 1
no_trans_skipped += len(statement.transactions)
continue
if not statement.local_account in info:
account_info = get_company_bank_account(pool, cursor, uid, statement.local_account, company, log)
if not account_info:
log.append(
_("Statements found for unknown account %(bank_account)s")
% {"bank_account": statement.local_account}
)
error_accounts[statement.local_account] = True
no_errors += 1
continue
if "journal_id" not in account_info:
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
no_errors += 1
continue
info[statement.local_account] = account_info
else:
account_info = info[statement.local_account]
if statement.local_currency and account_info.journal_id.code != statement.local_currency:
# TODO: convert currencies?
log.append(
_("Statement for account %(bank_account)s uses different " "currency than the defined bank journal.")
% {"bank_account": statement.local_account}
)
error_accounts[statement.local_account] = True
no_errors += 1
continue
# Check existence of previous statement
statement_ids = statement_obj.search(
cursor, uid, [("name", "=", statement.id), ("date", "=", date2str(statement.date))]
)
if statement_ids:
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",
currency=account_info.journal_id.currency.id,
user_id=uid,
banking_id=import_id,
),
)
imported_statement_ids.append(statement_id)
# move each line to the right period and try to match it with an
# invoice or payment
subno = 0
for transaction in statement.transactions:
move_info = False
# Keep a tracer for identification of order in a statement in case
# of missing transaction ids.
subno += 1
# Link remote partner, import account when needed
partner_bank = get_bank_account(pool, cursor, uid, transaction.remote_account, log, fail=True)
if partner_bank:
partner_id = partner_bank.partner_id.id
partner_bank_id = partner_bank.id
elif transaction.remote_owner:
partner_id = get_or_create_partner(pool, cursor, uid, transaction.remote_owner)
if transaction.remote_account:
partner_bank_id = create_bank_account(
pool, cursor, uid, partner_id, transaction.remote_account, transaction.remote_owner, log
)
else:
partner_id = False
# Link accounting period
period_id = get_period(pool, cursor, uid, transaction.effective_date, company, log)
# Credit means payment... isn't it?
if transaction.transferred_amount < 0 and payment_lines:
# Link open payment - if any
move_info = _link_payment(
pool, cursor, uid, transaction, payment_lines, partner_id, partner_bank_id, log
)
# Second guess, invoice
if not move_info:
# Link invoice - if any
move_info = _link_invoice(pool, cursor, uid, transaction, move_lines, partner_id, partner_bank_id, log)
if not move_info:
if transaction.transferred_amount < 0:
account_id = account_info.default_credit_account_id
else:
account_id = account_info.default_debit_account_id
else:
account_id = move_info.move_line.account_id
no_trans_matched += 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,
)
if partner_id:
values.partner_id = partner_id
if partner_bank_id:
values.partner_bank_id = partner_bank_id
if move_info:
values.type = move_info.type
values.reconcile_id = move_info.move_line.reconcile_id
statement_line_id = statement_line_obj.create(cursor, uid, values)
no_trans_loaded += 1
no_stat_loaded += 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(
"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 o.id NOT IN ("
"SELECT DISTINCT id FROM payment_line "
"WHERE date_done IS NULL "
"AND id IN (%s)"
")" % (time.strftime("%Y-%m-%d"), ",".join(map(str, payment_line_ids)))
)
report = [
"%s: %s" % (_("Total number of statements"), no_stat_skipped + no_stat_loaded),
"%s: %s" % (_("Total number of transactions"), no_trans_skipped + no_trans_loaded),
"%s: %s" % (_("Number of errors found"), no_errors),
"%s: %s" % (_("Number of statements skipped due to errors"), no_stat_skipped),
"%s: %s" % (_("Number of transactions skipped due to errors"), no_trans_skipped),
"%s: %s" % (_("Number of statements loaded"), no_stat_loaded),
"%s: %s" % (_("Number of transactions loaded"), no_trans_loaded),
"",
"%s:" % ("Error report"),
"",
]
text_log = "\n".join(report + log)
state = no_errors and "error" or "ready"
statement_file_obj.write(cursor, uid, import_id, dict(state=state, log=text_log))
return dict(log=text_log, statement_ids=imported_statement_ids)
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 _banking_import_statements_file(self, cursor, uid, data, context):
'''
Import bank statements / bank transactions file.
This module/function represents the business logic, the parser modules
represent the decoding logic.
'''
form = data['form']
statements_file = form['file']
data = base64.decodestring(statements_file)
pool = pooler.get_pool(cursor.dbname)
company_obj = pool.get('res.company')
user_obj = pool.get('res.user')
journal_obj = pool.get('account.journal')
move_line_obj = pool.get('account.move.line')
payment_line_obj = pool.get('payment.line')
statement_obj = pool.get('account.bank.statement')
statement_line_obj = pool.get('account.bank.statement.line')
statement_file_obj = pool.get('account.banking.imported.file')
#account_obj = pool.get('account.account')
#payment_order_obj = pool.get('payment.order')
#currency_obj = pool.get('res.currency')
# get the parser to parse the file
parser_code = form['parser']
parser = models.create_parser(parser_code)
if not parser:
raise wizard.except_wizard(
_('ERROR!'),
_('Unable to import parser %(parser)s. Parser class not found.') %
{'parser': parser_code})
# Get the company
company = form['company']
if not company:
user_data = user_obj.browse(cursor, uid, uid, context)
company = company_obj.browse(cursor, uid, company
or user_data.company_id.id, context)
# Parse the file
statements = parser.parse(data)
if any([x for x in statements if not x.is_valid()]):
raise wizard.except_wizard(
_('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,
date=time.strftime('%Y-%m-%d'),
user_id=uid,
state='unfinished'))
# Results
no_stat_loaded = 0
no_trans_loaded = 0
no_stat_skipped = 0
no_trans_skipped = 0
no_trans_matched = 0
no_errors = 0
log = []
# Caching
error_accounts = {}
info = {}
imported_statement_ids = []
if statements:
# Get interesting journals once
if company:
journal_ids = journal_obj.search(cursor, uid, [
('type', 'in', ('sale', 'purchase')),
('company_id', '=', company.id),
])
else:
journal_ids = None
if not journal_ids:
journal_ids = journal_obj.search(cursor, uid, [
('type', 'in', ('sale', 'purchase')),
('active', '=', True),
('company_id', '=', False),
])
# 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)),
])
move_lines = move_line_obj.browse(cursor, uid, move_line_ids)
# 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
no_stat_skipped += 1
no_trans_skipped += len(statement.transactions)
continue
if not statement.local_account in info:
account_info = get_company_bank_account(pool, cursor, uid,
statement.local_account,
company, log)
if not account_info:
log.append(
_('Statements found for unknown account %(bank_account)s')
% {'bank_account': statement.local_account})
error_accounts[statement.local_account] = True
no_errors += 1
continue
if 'journal_id' not in account_info:
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
no_errors += 1
continue
info[statement.local_account] = account_info
else:
account_info = info[statement.local_account]
if statement.local_currency \
and account_info.journal_id.code != statement.local_currency:
# TODO: convert currencies?
log.append(
_('Statement for account %(bank_account)s uses different '
'currency than the defined bank journal.') %
{'bank_account': statement.local_account})
error_accounts[statement.local_account] = True
no_errors += 1
continue
# Check existence of previous statement
statement_ids = statement_obj.search(cursor, uid, [
('name', '=', statement.id),
('date', '=', date2str(statement.date)),
])
if statement_ids:
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',
currency=account_info.journal_id.currency.id,
user_id=uid,
banking_id=import_id,
))
imported_statement_ids.append(statement_id)
# move each line to the right period and try to match it with an
# invoice or payment
subno = 0
for transaction in statement.transactions:
move_info = False
# Keep a tracer for identification of order in a statement in case
# of missing transaction ids.
subno += 1
# Link remote partner, import account when needed
partner_bank = get_bank_account(pool,
cursor,
uid,
transaction.remote_account,
log,
fail=True)
if partner_bank:
partner_id = partner_bank.partner_id.id
partner_bank_id = partner_bank.id
elif transaction.remote_owner:
partner_id = get_or_create_partner(pool, cursor, uid,
transaction.remote_owner)
if transaction.remote_account:
partner_bank_id = create_bank_account(
pool, cursor, uid, partner_id,
transaction.remote_account, transaction.remote_owner,
log)
else:
partner_id = False
partner_bank_id = False
# Link accounting period
period_id = get_period(pool, cursor, uid,
transaction.effective_date, company, log)
if not period_id:
no_trans_skipped += 1
continue
# Credit means payment... isn't it?
if transaction.transferred_amount < 0 and payment_lines:
# Link open payment - if any
move_info = _link_payment(pool, cursor, uid, transaction,
payment_lines, partner_id,
partner_bank_id, log)
# Second guess, invoice
if not move_info:
# Link invoice - if any
move_info = _link_invoice(pool, cursor, uid, transaction,
move_lines, partner_id,
partner_bank_id, log)
if not move_info:
if transaction.transferred_amount < 0:
account_id = account_info.default_credit_account_id
else:
account_id = account_info.default_debit_account_id
else:
account_id = move_info.move_line.account_id
no_trans_matched += 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,
)
if partner_id:
values.partner_id = partner_id
if partner_bank_id:
values.partner_bank_id = partner_bank_id
if move_info:
values.type = move_info.type
values.reconcile_id = move_info.move_line.reconcile_id
statement_line_id = statement_line_obj.create(cursor, uid, values)
no_trans_loaded += 1
no_stat_loaded += 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("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 o.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'), no_stat_skipped + no_stat_loaded),
'%s: %s' % (_('Total number of transactions'),
no_trans_skipped + no_trans_loaded),
'%s: %s' % (_('Number of errors found'), no_errors),
'%s: %s' %
(_('Number of statements skipped due to errors'), no_stat_skipped),
'%s: %s' %
(_('Number of transactions skipped due to errors'), no_trans_skipped),
'%s: %s' % (_('Number of statements loaded'), no_stat_loaded),
'%s: %s' % (_('Number of transactions loaded'), no_trans_loaded),
'',
'%s:' % ('Error report'),
'',
]
text_log = '\n'.join(report + log)
state = no_errors and 'error' or 'ready'
statement_file_obj.write(cursor, uid, import_id,
dict(
state=state,
log=text_log,
))
return dict(log=text_log, statement_ids=imported_statement_ids)
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,
}
