diff options
Diffstat (limited to 'addons/l10n_fr_fec/wizard/account_fr_fec.py')
| -rw-r--r-- | addons/l10n_fr_fec/wizard/account_fr_fec.py | 425 |
1 files changed, 425 insertions, 0 deletions
diff --git a/addons/l10n_fr_fec/wizard/account_fr_fec.py b/addons/l10n_fr_fec/wizard/account_fr_fec.py new file mode 100644 index 00000000..d80c07d5 --- /dev/null +++ b/addons/l10n_fr_fec/wizard/account_fr_fec.py @@ -0,0 +1,425 @@ +#-*- coding:utf-8 -*- +# Part of Odoo. See LICENSE file for full copyright and licensing details. + +# Copyright (C) 2013-2015 Akretion (http://www.akretion.com) + +import base64 +import io + +from odoo import api, fields, models, _ +from odoo.exceptions import UserError +from odoo.tools import float_is_zero, pycompat + + +class AccountFrFec(models.TransientModel): + _name = 'account.fr.fec' + _description = 'Ficher Echange Informatise' + + date_from = fields.Date(string='Start Date', required=True) + date_to = fields.Date(string='End Date', required=True) + fec_data = fields.Binary('FEC File', readonly=True, attachment=False) + filename = fields.Char(string='Filename', size=256, readonly=True) + test_file = fields.Boolean() + export_type = fields.Selection([ + ('official', 'Official FEC report (posted entries only)'), + ('nonofficial', 'Non-official FEC report (posted and unposted entries)'), + ], string='Export Type', required=True, default='official') + + @api.onchange('test_file') + def _onchange_export_file(self): + if not self.test_file: + self.export_type = 'official' + + def do_query_unaffected_earnings(self): + ''' Compute the sum of ending balances for all accounts that are of a type that does not bring forward the balance in new fiscal years. + This is needed because we have to display only one line for the initial balance of all expense/revenue accounts in the FEC. + ''' + + sql_query = ''' + SELECT + 'OUV' AS JournalCode, + 'Balance initiale' AS JournalLib, + 'OUVERTURE/' || %s AS EcritureNum, + %s AS EcritureDate, + '120/129' AS CompteNum, + 'Benefice (perte) reporte(e)' AS CompteLib, + '' AS CompAuxNum, + '' AS CompAuxLib, + '-' AS PieceRef, + %s AS PieceDate, + '/' AS EcritureLib, + replace(CASE WHEN COALESCE(sum(aml.balance), 0) <= 0 THEN '0,00' ELSE to_char(SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Debit, + replace(CASE WHEN COALESCE(sum(aml.balance), 0) >= 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 + 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 IS NOT TRUE + 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' + ''' + company = self.env.company + formatted_date_from = fields.Date.to_string(self.date_from).replace('-', '') + date_from = self.date_from + 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)) + listrow = [] + row = self._cr.fetchone() + listrow = list(row) + return listrow + + def _get_company_legal_data(self, company): + """ + Dom-Tom are excluded from the EU's fiscal territory + Those regions do not have SIREN + sources: + https://www.service-public.fr/professionnels-entreprises/vosdroits/F23570 + http://www.douane.gouv.fr/articles/a11024-tva-dans-les-dom + """ + dom_tom_group = self.env.ref('l10n_fr.dom-tom') + is_dom_tom = company.country_id.code in dom_tom_group.country_ids.mapped('code') + if not is_dom_tom and not company.vat: + raise UserError(_("Missing VAT number for company %s", company.name)) + if not is_dom_tom and company.vat[0:2] != 'FR': + raise UserError(_("FEC is for French companies only !")) + + return { + 'siren': company.vat[4:13] if not is_dom_tom else '', + } + + 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 Odoo 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 + today = fields.Date.today() + if self.date_from > today or self.date_to > today: + raise UserError(_('You could not set the start date or the end date in the future.')) + if self.date_from >= self.date_to: + raise UserError(_('The start date must be inferior to the end date.')) + + company = self.env.company + company_legal_data = self._get_company_legal_data(company) + + 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 + ] + + rows_to_write = [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 round(sum(aml.balance), %s) != 0 + AND aat.type not in ('receivable', 'payable') + ''' + formatted_date_from = fields.Date.to_string(self.date_from).replace('-', '') + date_from = self.date_from + formatted_date_year = date_from.year + currency_digits = 2 + + self._cr.execute( + sql_query, (formatted_date_year, formatted_date_from, formatted_date_from, formatted_date_from, self.date_from, company.id, currency_digits)) + + 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 float_is_zero(listrow_amount, precision_digits=currency_digits): + continue + 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('.', ',') + rows_to_write.append(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 + rows_to_write.append(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 MIN(aat.type) IN ('receivable', 'payable') + THEN + CASE WHEN rp.ref IS null OR rp.ref = '' + THEN rp.id::text + ELSE replace(rp.ref, '|', '/') + END + ELSE '' + END + AS CompAuxNum, + CASE WHEN aat.type IN ('receivable', 'payable') + THEN COALESCE(replace(rp.name, '|', '/'), '') + ELSE '' + END 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 round(sum(aml.balance), %s) != 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, currency_digits)) + + for row in self._cr.fetchall(): + listrow = list(row) + account_id = listrow.pop() + rows_to_write.append(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 aat.type IN ('receivable', 'payable') + THEN + CASE WHEN rp.ref IS null OR rp.ref = '' + THEN rp.id::text + ELSE replace(rp.ref, '|', '/') + END + ELSE '' + END + AS CompAuxNum, + CASE WHEN aat.type IN ('receivable', 'payable') + THEN COALESCE(replace(replace(rp.name, '|', '/'), '\t', ''), '') + ELSE '' + END 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 OR aml.name = '' THEN '/' + WHEN aml.name SIMILAR TO '[\t|\s|\n]*' THEN '/' + ELSE replace(replace(replace(replace(aml.name, '|', '/'), '\t', ''), '\n', ''), '\r', '') 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 account_account_type aat ON aa.user_type_id = aat.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(): + rows_to_write.append(list(row)) + + fecvalue = self._csv_write_rows(rows_to_write) + end_date = fields.Date.to_string(self.date_to).replace('-', '') + suffix = '' + if self.export_type == "nonofficial": + suffix = '-NONOFFICIAL' + + self.write({ + 'fec_data': base64.encodebytes(fecvalue), + # Filename = <siren>FECYYYYMMDD where YYYMMDD is the closing date + 'filename': '%sFEC%s%s.csv' % (company_legal_data['siren'], end_date, suffix), + }) + + # Set fiscal year lock date to the end date (not in test) + fiscalyear_lock_date = self.env.company.fiscalyear_lock_date + if not self.test_file and (not fiscalyear_lock_date or fiscalyear_lock_date < self.date_to): + self.env.company.write({'fiscalyear_lock_date': self.date_to}) + return { + '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', + } + + 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 |