def _csv_write_rows(self, rows, lineterminator=u'\r\n'):
"""
Write FEC rows into a file
It seems that Bercy's bureaucracy is not too happy about the
empty new line at the End Of File.
@param {list(list)} rows: the list of rows. Each row is a list of strings
@param {unicode string} [optional] lineterminator: effective line terminator
Has nothing to do with the csv writer parameter
The last line written won't be terminated with it
@return the value of the file
"""
fecfile = io.BytesIO()
writer = pycompat.csv_writer(fecfile, delimiter='|', lineterminator='')
rows_length = len(rows)
for i, row in enumerate(rows):
if not i == rows_length - 1:
row[-1] += lineterminator
writer.writerow(row)
fecvalue = fecfile.getvalue()
fecfile.close()
return fecvalue
def test_big_attachments(self):
"""
Ensure big fields (e.g. b64-encoded image data) can be imported and
we're not hitting limits of the default CSV parser config
"""
from PIL import Image
im = Image.new('RGB', (1920, 1080))
fout = io.BytesIO()
writer = pycompat.csv_writer(fout, dialect=None)
writer.writerows([
[u'name', u'db_datas'],
[u'foo', base64.b64encode(im.tobytes()).decode('ascii')]
])
import_wizard = self.env['base_import.import'].create({
'res_model': 'ir.attachment',
'file': fout.getvalue(),
'file_type': 'text/csv'
})
results = import_wizard.do(
['name', 'db_datas'],
{'headers': True, 'separator': ',', 'quoting': '"'})
self.assertFalse(results, "results should be empty on successful import")
def _makefile(self, rows):
f = io.BytesIO()
writer = pycompat.csv_writer(f, quoting=1)
writer.writerow(['name', 'counter'])
for i in range(rows):
writer.writerow(['n_%d' % i, str(i)])
return f.getvalue()
def test_newline_import(self):
"""
Ensure importing keep newlines
"""
output = io.BytesIO()
writer = pycompat.csv_writer(output, quoting=1)
data_row = [u"\tfoo\n\tbar", u" \"hello\" \n\n 'world' "]
writer.writerow([u"name", u"Some Value"])
writer.writerow(data_row)
import_wizard = self.env['base_import.import'].create({
'res_model':
'base_import.tests.models.preview',
'file':
output.getvalue(),
'file_type':
'text/csv',
})
data, _ = import_wizard._convert_import_data(['name', 'somevalue'], {
'quoting': '"',
'separator': ',',
'headers': True
})
self.assertItemsEqual(data, [data_row])
def fiscal_pos_map_to_csv(self):
writer = pycompat.csv_writer(open('account.fiscal.'
'position.tax.template-%s.csv' %
self.suffix, 'wb'))
fiscal_pos_map_iterator = self.iter_fiscal_pos_map()
keys = next(fiscal_pos_map_iterator)
writer.writerow(keys)
for row in fiscal_pos_map_iterator:
writer.writerow([pycompat.to_text(s) for s in row.values()])
def test_limit_on_lines(self):
""" The limit option should be a limit on the number of *lines*
imported at at time, not the number of *records*. This is relevant
when it comes to embedded o2m.
A big question is whether we want to round up or down (if the limit
brings us inside a record). Rounding up (aka finishing up the record
we're currently parsing) seems like a better idea:
* if the first record has so many sub-lines it hits the limit we still
want to import it (it's probably extremely rare but it can happen)
* if we have one line per record, we probably want to import <limit>
records not <limit-1>, but if we stop in the middle of the "current
record" we'd always ignore the last record (I think)
"""
f = io.BytesIO()
writer = pycompat.csv_writer(f, quoting=1)
writer.writerow(['name', 'value/value'])
for record in range(10):
writer.writerow(['record_%d' % record, '0'])
for row in range(1, 10):
writer.writerow(['', str(row)])
import_wizard = self.env['base_import.import'].create({
'res_model':
'base_import.tests.models.o2m',
'file_type':
'text/csv',
'file_name':
'things.csv',
'file':
f.getvalue(),
})
opts = {'quoting': '"', 'separator': ',', 'headers': True}
preview = import_wizard.parse_preview({**opts, 'limit': 15})
self.assertIs(preview['batch'], True)
results = import_wizard.do(['name', 'value/value'], [], {
**opts, 'limit': 5
})
self.assertFalse(results['messages'])
self.assertEqual(
len(results['ids']), 1,
"should have imported the first record in full, got %s" %
results['ids'])
self.assertEqual(results['nextrow'], 10)
results = import_wizard.do(['name', 'value/value'], [], {
**opts, 'limit': 15
})
self.assertFalse(results['messages'])
self.assertEqual(
len(results['ids']), 2,
"should have importe the first two records, got %s" %
results['ids'])
self.assertEqual(results['nextrow'], 20)
def taxes_to_csv(self):
writer = pycompat.csv_writer(
open('account.tax.template-%s.csv' % self.suffix, 'wb'))
taxes_iterator = self.iter_taxes()
keys = next(taxes_iterator)
writer.writerow(keys[3:] + ['sequence'])
seq = 100
for row in sorted(taxes_iterator, key=lambda r: r['description']):
if not _is_true(row['active']):
continue
seq += 1
if row['parent_id:id']:
cur_seq = seq + 1000
else:
cur_seq = seq
writer.writerow(
[pycompat.to_text(v)
for v in list(row.values())[3:]] + [cur_seq])
def tax_codes_to_csv(self):
writer = pycompat.csv_writer(
open('account.tax.code.template-%s.csv' % self.suffix, 'wb'))
tax_codes_iterator = self.iter_tax_codes()
keys = next(tax_codes_iterator)
writer.writerow(keys)
# write structure tax codes
tax_codes = {} # code: id
for row in tax_codes_iterator:
tax_code = row['code']
if tax_code in tax_codes:
raise RuntimeError('duplicate tax code %s' % tax_code)
tax_codes[tax_code] = row['id']
writer.writerow([pycompat.to_text(v) for v in row.values()])
# read taxes and add leaf tax codes
new_tax_codes = {} # id: parent_code
def add_new_tax_code(tax_code_id, new_name, new_parent_code):
if not tax_code_id:
return
name, parent_code = new_tax_codes.get(tax_code_id, (None, None))
if parent_code and parent_code != new_parent_code:
raise RuntimeError('tax code "%s" already exist with '
'parent %s while trying to add it with '
'parent %s' %
(tax_code_id, parent_code, new_parent_code))
else:
new_tax_codes[tax_code_id] = (new_name, new_parent_code)
taxes_iterator = self.iter_taxes()
next(taxes_iterator)
for row in taxes_iterator:
if not _is_true(row['active']):
continue
if row['child_depend'] and row['amount'] != 1:
raise RuntimeError('amount must be one if child_depend '
'for %s' % row['id'])
# base parent
base_code = row['BASE_CODE']
if not base_code or base_code == '/':
base_code = 'NA'
if base_code not in tax_codes:
raise RuntimeError('undefined tax code %s' % base_code)
if base_code != 'NA':
if row['child_depend']:
raise RuntimeError('base code specified '
'with child_depend for %s' % row['id'])
if not row['child_depend']:
# ... in lux, we have the same code for invoice and refund
if base_code != 'NA':
assert row[
'base_code_id:id'], 'missing base_code_id for %s' % row[
'id']
assert row['ref_base_code_id:id'] == row['base_code_id:id']
add_new_tax_code(row['base_code_id:id'],
'Base - ' + row['name'], base_code)
# tax parent
tax_code = row['TAX_CODE']
if not tax_code or tax_code == '/':
tax_code = 'NA'
if tax_code not in tax_codes:
raise RuntimeError('undefined tax code %s' % tax_code)
if tax_code == 'NA':
if row['amount'] and not row['child_depend']:
raise RuntimeError('TAX_CODE not specified '
'for non-zero tax %s' % row['id'])
if row['tax_code_id:id']:
raise RuntimeError('tax_code_id specified '
'for tax %s' % row['id'])
else:
if row['child_depend']:
raise RuntimeError('TAX_CODE specified '
'with child_depend for %s' % row['id'])
if not row['amount']:
raise RuntimeError('TAX_CODE specified '
'for zero tax %s' % row['id'])
if not row['tax_code_id:id']:
raise RuntimeError('tax_code_id not specified '
'for tax %s' % row['id'])
if not row['child_depend'] and row['amount']:
# ... in lux, we have the same code for invoice and refund
assert row[
'tax_code_id:id'], 'missing tax_code_id for %s' % row['id']
assert row['ref_tax_code_id:id'] == row['tax_code_id:id']
add_new_tax_code(row['tax_code_id:id'],
'Taxe - ' + row['name'], tax_code)
for tax_code_id in sorted(new_tax_codes):
name, parent_code = new_tax_codes[tax_code_id]
writer.writerow([
tax_code_id, u'lu_tct_m' + parent_code,
tax_code_id.replace('lu_tax_code_template_', u''), u'1', u'',
pycompat.to_text(name), u''
])
def generate_fec(self):
self.ensure_one()
# We choose to implement the flat file instead of the XML
# file for 2 reasons :
# 1) the XSD file impose to have the label on the account.move
# but Flectra has the label on the account.move.line, so that's a
# problem !
# 2) CSV files are easier to read/use for a regular accountant.
# So it will be easier for the accountant to check the file before
# sending it to the fiscal administration
header = [
u'JournalCode', # 0
u'JournalLib', # 1
u'EcritureNum', # 2
u'EcritureDate', # 3
u'CompteNum', # 4
u'CompteLib', # 5
u'CompAuxNum', # 6 We use partner.id
u'CompAuxLib', # 7
u'PieceRef', # 8
u'PieceDate', # 9
u'EcritureLib', # 10
u'Debit', # 11
u'Credit', # 12
u'EcritureLet', # 13
u'DateLet', # 14
u'ValidDate', # 15
u'Montantdevise', # 16
u'Idevise', # 17
]
company = self.env.user.company_id
if not company.vat:
raise Warning(
_("Missing VAT number for company %s") % company.name)
if company.vat[0:2] != 'FR':
raise Warning(_("FEC is for French companies only !"))
fecfile = io.BytesIO()
w = pycompat.csv_writer(fecfile, delimiter='|')
w.writerow(header)
# INITIAL BALANCE
unaffected_earnings_xml_ref = self.env.ref(
'account.data_unaffected_earnings')
unaffected_earnings_line = True # used to make sure that we add the unaffected earning initial balance only once
if unaffected_earnings_xml_ref:
#compute the benefit/loss of last year to add in the initial balance of the current year earnings account
unaffected_earnings_results = self.do_query_unaffected_earnings()
unaffected_earnings_line = False
sql_query = '''
SELECT
'OUV' AS JournalCode,
'Balance initiale' AS JournalLib,
'OUVERTURE/' || %s AS EcritureNum,
%s AS EcritureDate,
MIN(aa.code) AS CompteNum,
replace(replace(MIN(aa.name), '|', '/'), '\t', '') AS CompteLib,
'' AS CompAuxNum,
'' AS CompAuxLib,
'-' AS PieceRef,
%s AS PieceDate,
'/' AS EcritureLib,
replace(CASE WHEN sum(aml.balance) <= 0 THEN '0,00' ELSE to_char(SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Debit,
replace(CASE WHEN sum(aml.balance) >= 0 THEN '0,00' ELSE to_char(-SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Credit,
'' AS EcritureLet,
'' AS DateLet,
%s AS ValidDate,
'' AS Montantdevise,
'' AS Idevise,
MIN(aa.id) AS CompteID
FROM
account_move_line aml
LEFT JOIN account_move am ON am.id=aml.move_id
JOIN account_account aa ON aa.id = aml.account_id
LEFT JOIN account_account_type aat ON aa.user_type_id = aat.id
WHERE
am.date < %s
AND am.company_id = %s
AND aat.include_initial_balance = 't'
AND (aml.debit != 0 OR aml.credit != 0)
'''
# For official report: only use posted entries
if self.export_type == "official":
sql_query += '''
AND am.state = 'posted'
'''
sql_query += '''
GROUP BY aml.account_id, aat.type
HAVING sum(aml.balance) != 0
AND aat.type not in ('receivable', 'payable')
'''
formatted_date_from = self.date_from.replace('-', '')
date_from = datetime.strptime(self.date_from,
DEFAULT_SERVER_DATE_FORMAT)
formatted_date_year = date_from.year
self._cr.execute(
sql_query,
(formatted_date_year, formatted_date_from, formatted_date_from,
formatted_date_from, self.date_from, company.id))
for row in self._cr.fetchall():
listrow = list(row)
account_id = listrow.pop()
if not unaffected_earnings_line:
account = self.env['account.account'].browse(account_id)
if account.user_type_id.id == self.env.ref(
'account.data_unaffected_earnings').id:
#add the benefit/loss of previous fiscal year to the first unaffected earnings account found.
unaffected_earnings_line = True
current_amount = float(listrow[11].replace(
',', '.')) - float(listrow[12].replace(',', '.'))
unaffected_earnings_amount = float(
unaffected_earnings_results[11].replace(
',', '.')) - float(
unaffected_earnings_results[12].replace(
',', '.'))
listrow_amount = current_amount + unaffected_earnings_amount
if listrow_amount > 0:
listrow[11] = str(listrow_amount).replace('.', ',')
listrow[12] = '0,00'
else:
listrow[11] = '0,00'
listrow[12] = str(-listrow_amount).replace('.', ',')
w.writerow(listrow)
#if the unaffected earnings account wasn't in the selection yet: add it manually
if (not unaffected_earnings_line and unaffected_earnings_results
and (unaffected_earnings_results[11] != '0,00'
or unaffected_earnings_results[12] != '0,00')):
#search an unaffected earnings account
unaffected_earnings_account = self.env['account.account'].search(
[('user_type_id', '=',
self.env.ref('account.data_unaffected_earnings').id)],
limit=1)
if unaffected_earnings_account:
unaffected_earnings_results[
4] = unaffected_earnings_account.code
unaffected_earnings_results[
5] = unaffected_earnings_account.name
w.writerow(unaffected_earnings_results)
# INITIAL BALANCE - receivable/payable
sql_query = '''
SELECT
'OUV' AS JournalCode,
'Balance initiale' AS JournalLib,
'OUVERTURE/' || %s AS EcritureNum,
%s AS EcritureDate,
MIN(aa.code) AS CompteNum,
replace(MIN(aa.name), '|', '/') AS CompteLib,
CASE WHEN rp.ref IS null OR rp.ref = ''
THEN COALESCE('ID ' || rp.id, '')
ELSE replace(rp.ref, '|', '/')
END
AS CompAuxNum,
COALESCE(replace(rp.name, '|', '/'), '') AS CompAuxLib,
'-' AS PieceRef,
%s AS PieceDate,
'/' AS EcritureLib,
replace(CASE WHEN sum(aml.balance) <= 0 THEN '0,00' ELSE to_char(SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Debit,
replace(CASE WHEN sum(aml.balance) >= 0 THEN '0,00' ELSE to_char(-SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Credit,
'' AS EcritureLet,
'' AS DateLet,
%s AS ValidDate,
'' AS Montantdevise,
'' AS Idevise,
MIN(aa.id) AS CompteID
FROM
account_move_line aml
LEFT JOIN account_move am ON am.id=aml.move_id
LEFT JOIN res_partner rp ON rp.id=aml.partner_id
JOIN account_account aa ON aa.id = aml.account_id
LEFT JOIN account_account_type aat ON aa.user_type_id = aat.id
WHERE
am.date < %s
AND am.company_id = %s
AND aat.include_initial_balance = 't'
AND (aml.debit != 0 OR aml.credit != 0)
'''
# For official report: only use posted entries
if self.export_type == "official":
sql_query += '''
AND am.state = 'posted'
'''
sql_query += '''
GROUP BY aml.account_id, aat.type, rp.ref, rp.id
HAVING sum(aml.balance) != 0
AND aat.type in ('receivable', 'payable')
'''
self._cr.execute(
sql_query,
(formatted_date_year, formatted_date_from, formatted_date_from,
formatted_date_from, self.date_from, company.id))
for row in self._cr.fetchall():
listrow = list(row)
account_id = listrow.pop()
w.writerow([s.encode("utf-8") for s in listrow])
# LINES
sql_query = '''
SELECT
replace(replace(aj.code, '|', '/'), '\t', '') AS JournalCode,
replace(replace(aj.name, '|', '/'), '\t', '') AS JournalLib,
replace(replace(am.name, '|', '/'), '\t', '') AS EcritureNum,
TO_CHAR(am.date, 'YYYYMMDD') AS EcritureDate,
aa.code AS CompteNum,
replace(replace(aa.name, '|', '/'), '\t', '') AS CompteLib,
CASE WHEN rp.ref IS null OR rp.ref = ''
THEN COALESCE('ID ' || rp.id, '')
ELSE replace(rp.ref, '|', '/')
END
AS CompAuxNum,
COALESCE(replace(replace(rp.name, '|', '/'), '\t', ''), '') AS CompAuxLib,
CASE WHEN am.ref IS null OR am.ref = ''
THEN '-'
ELSE replace(replace(am.ref, '|', '/'), '\t', '')
END
AS PieceRef,
TO_CHAR(am.date, 'YYYYMMDD') AS PieceDate,
CASE WHEN aml.name IS NULL THEN '/' ELSE replace(replace(aml.name, '|', '/'), '\t', '') END AS EcritureLib,
replace(CASE WHEN aml.debit = 0 THEN '0,00' ELSE to_char(aml.debit, '000000000000000D99') END, '.', ',') AS Debit,
replace(CASE WHEN aml.credit = 0 THEN '0,00' ELSE to_char(aml.credit, '000000000000000D99') END, '.', ',') AS Credit,
CASE WHEN rec.name IS NULL THEN '' ELSE rec.name END AS EcritureLet,
CASE WHEN aml.full_reconcile_id IS NULL THEN '' ELSE TO_CHAR(rec.create_date, 'YYYYMMDD') END AS DateLet,
TO_CHAR(am.date, 'YYYYMMDD') AS ValidDate,
CASE
WHEN aml.amount_currency IS NULL OR aml.amount_currency = 0 THEN ''
ELSE replace(to_char(aml.amount_currency, '000000000000000D99'), '.', ',')
END AS Montantdevise,
CASE WHEN aml.currency_id IS NULL THEN '' ELSE rc.name END AS Idevise
FROM
account_move_line aml
LEFT JOIN account_move am ON am.id=aml.move_id
LEFT JOIN res_partner rp ON rp.id=aml.partner_id
JOIN account_journal aj ON aj.id = am.journal_id
JOIN account_account aa ON aa.id = aml.account_id
LEFT JOIN res_currency rc ON rc.id = aml.currency_id
LEFT JOIN account_full_reconcile rec ON rec.id = aml.full_reconcile_id
WHERE
am.date >= %s
AND am.date <= %s
AND am.company_id = %s
AND (aml.debit != 0 OR aml.credit != 0)
'''
# For official report: only use posted entries
if self.export_type == "official":
sql_query += '''
AND am.state = 'posted'
'''
sql_query += '''
ORDER BY
am.date,
am.name,
aml.id
'''
self._cr.execute(sql_query, (self.date_from, self.date_to, company.id))
for row in self._cr.fetchall():
w.writerow(list(row))
siren = company.vat[4:13]
end_date = self.date_to.replace('-', '')
suffix = ''
if self.export_type == "nonofficial":
suffix = '-NONOFFICIAL'
fecvalue = fecfile.getvalue()
self.write({
'fec_data': base64.encodestring(fecvalue),
# Filename = <siren>FECYYYYMMDD where YYYMMDD is the closing date
'filename': '%sFEC%s%s.csv' % (siren, end_date, suffix),
})
fecfile.close()
action = {
'name':
'FEC',
'type':
'ir.actions.act_url',
'url':
"web/content/?model=account.fr.fec&id=" + str(self.id) +
"&filename_field=filename&field=fec_data&download=true&filename=" +
self.filename,
'target':
'self',
}
return action
