def get_company_bank_account(pool, cursor, uid, account_number, company, log):
'''
Get the matching bank account for this company.
'''
results = struct()
bank_account = get_bank_account(pool,
cursor,
uid,
account_number,
log,
fail=True)
if not bank_account:
return False
if bank_account.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_account
bank_settings_obj = pool.get('account.banking.account.settings')
bank_settings_ids = bank_settings_obj.search(
cursor, uid, [('partner_bank_id', '=', bank_account.id)])
if bank_settings_ids:
settings = bank_settings_obj.browse(cursor, uid, bank_settings_ids)[0]
results.journal_id = settings.journal_id
results.default_debit_account_id = settings.default_debit_account_id
results.default_credit_account_id = settings.default_credit_account_id
return results
def get_company_bank_account(pool, cursor, uid, account_number,
company, log):
'''
Get the matching bank account for this company.
'''
results = struct()
bank_account = get_bank_account(pool, cursor, uid, account_number, log,
fail=True)
if not bank_account:
return False
if bank_account.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_account
bank_settings_obj = pool.get('account.banking.account.settings')
bank_settings_ids = bank_settings_obj.search(cursor, uid, [
('partner_bank_id', '=', bank_account.id)
])
if bank_settings_ids:
settings = bank_settings_obj.browse(cursor, uid, bank_settings_ids)[0]
results.journal_id = settings.journal_id
results.default_debit_account_id = settings.default_debit_account_id
results.default_credit_account_id = settings.default_credit_account_id
return results
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.
'''
data = urllib.urlencode(dict(number=bank_acc, 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.
'''
data = urllib.urlencode(dict(number=bank_acc, 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_company_bank_account(pool, cursor, 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, cursor, 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(cursor, uid, [
('type', '=', 'bank'),
('currency', '=', currency or company.currency_id.name)
])
if not journal_ids and currency == company.currency_id.name:
journal_ids = journal_obj.search(cursor, uid, [
('type', '=', 'bank'), ('currency', '=', False)
])
if journal_ids:
criteria.append(('journal_id', 'in', journal_ids))
# Find bank account settings
bank_settings_ids = bank_settings_obj.search(cursor, uid, criteria)
if bank_settings_ids:
settings = bank_settings_obj.browse(cursor, 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 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 _get_move_info(self,
cursor,
uid,
move_line,
partner_bank_id=False,
partial=False):
reconcile_obj = self.pool.get('account.move.reconcile')
type_map = {
'out_invoice': 'customer',
'in_invoice': 'supplier',
'out_refund': 'customer',
'in_refund': 'supplier',
}
retval = struct(move_line=move_line,
partner_id=move_line.partner_id.id,
partner_bank_id=partner_bank_id,
reference=move_line.ref)
if move_line.invoice:
retval.invoice = move_line.invoice
retval.type = type_map[move_line.invoice.type]
else:
retval.type = 'general'
if partial:
move_line.reconcile_partial_id = reconcile_obj.create(
cursor, uid, {
'type': 'auto',
'line_partial_ids': [(4, 0, [move_line.id])]
})
else:
if move_line.reconcile_partial_id:
partial_ids = [
x.id
for x in move_line.reconcile_partial_id.line_partial_ids
]
else:
partial_ids = []
move_line.reconcile_id = reconcile_obj.create(
cursor, uid, {
'type':
'auto',
'line_id': [(4, x, False)
for x in [move_line.id] + partial_ids],
'line_partial_ids': [(3, x, False) for x in partial_ids]
})
return retval
def _get_move_info(self, cursor, uid, move_line, partner_bank_id=False,
partial=False):
reconcile_obj = self.pool.get('account.move.reconcile')
type_map = {
'out_invoice': 'customer',
'in_invoice': 'supplier',
'out_refund': 'customer',
'in_refund': 'supplier',
}
retval = struct(move_line=move_line, partner_id=move_line.partner_id.id,
partner_bank_id=partner_bank_id,
reference=move_line.ref
)
if move_line.invoice:
retval.invoice = move_line.invoice
retval.type = type_map[move_line.invoice.type]
else:
retval.type = 'general'
if partial:
move_line.reconcile_partial_id = reconcile_obj.create(
cursor, uid, {
'type': 'auto',
'line_partial_ids': [(4, 0, [move_line.id])]
}
)
else:
if move_line.reconcile_partial_id:
partial_ids = [x.id for x in
move_line.reconcile_partial_id.line_partial_ids
]
else:
partial_ids = []
move_line.reconcile_id = reconcile_obj.create(
cursor, uid, {
'type': 'auto',
'line_id': [
(4, x, False) for x in [move_line.id] + partial_ids
],
'line_partial_ids': [
(3, x, False) for x in partial_ids
]
}
)
return retval
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 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 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.
'''
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 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)
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 get_or_create_bank(pool, cursor, 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(
cursor, uid, [
('code', '=', bic[:6])
])
if not bank_ids:
bank_ids = bank_obj.search(
cursor, uid, [
('bic', 'ilike', bic + '%')
])
else:
bank_ids = bank_obj.search(
cursor, uid, [
('bic', '=', bic)
])
if bank_ids and len(bank_ids) == 1:
banks = bank_obj.browse(cursor, uid, bank_ids)
return banks[0].id, banks[0].country.id
country_obj = pool.get('res.country')
country_ids = country_obj.search(
cursor, uid, [('code', '=', bic[4:6])]
)
country_id = country_ids and country_ids[0] or False
bank_id = False
if online:
info, address = sepa.online.bank_info(bic)
if info:
bank_id = bank_obj.create(cursor, 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(cursor, uid, dict(
code = info.code or 'UNKNOW',
name = info.name or _('Unknown Bank'),
country = country_id,
bic = bic,
))
return bank_id, country_id
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.
'''
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 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,
}
