1#-*- coding:utf-8 -*- 2# Part of Odoo. See LICENSE file for full copyright and licensing details. 3 4# Copyright (C) 2013-2015 Akretion (http://www.akretion.com) 5 6import base64 7import io 8 9from odoo import api, fields, models, _ 10from odoo.exceptions import UserError 11from odoo.tools import float_is_zero, pycompat 12 13 14class AccountFrFec(models.TransientModel): 15 _name = 'account.fr.fec' 16 _description = 'Ficher Echange Informatise' 17 18 date_from = fields.Date(string='Start Date', required=True) 19 date_to = fields.Date(string='End Date', required=True) 20 fec_data = fields.Binary('FEC File', readonly=True, attachment=False) 21 filename = fields.Char(string='Filename', size=256, readonly=True) 22 test_file = fields.Boolean() 23 export_type = fields.Selection([ 24 ('official', 'Official FEC report (posted entries only)'), 25 ('nonofficial', 'Non-official FEC report (posted and unposted entries)'), 26 ], string='Export Type', required=True, default='official') 27 28 @api.onchange('test_file') 29 def _onchange_export_file(self): 30 if not self.test_file: 31 self.export_type = 'official' 32 33 def do_query_unaffected_earnings(self): 34 ''' 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. 35 This is needed because we have to display only one line for the initial balance of all expense/revenue accounts in the FEC. 36 ''' 37 38 sql_query = ''' 39 SELECT 40 'OUV' AS JournalCode, 41 'Balance initiale' AS JournalLib, 42 'OUVERTURE/' || %s AS EcritureNum, 43 %s AS EcritureDate, 44 '120/129' AS CompteNum, 45 'Benefice (perte) reporte(e)' AS CompteLib, 46 '' AS CompAuxNum, 47 '' AS CompAuxLib, 48 '-' AS PieceRef, 49 %s AS PieceDate, 50 '/' AS EcritureLib, 51 replace(CASE WHEN COALESCE(sum(aml.balance), 0) <= 0 THEN '0,00' ELSE to_char(SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Debit, 52 replace(CASE WHEN COALESCE(sum(aml.balance), 0) >= 0 THEN '0,00' ELSE to_char(-SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Credit, 53 '' AS EcritureLet, 54 '' AS DateLet, 55 %s AS ValidDate, 56 '' AS Montantdevise, 57 '' AS Idevise 58 FROM 59 account_move_line aml 60 LEFT JOIN account_move am ON am.id=aml.move_id 61 JOIN account_account aa ON aa.id = aml.account_id 62 LEFT JOIN account_account_type aat ON aa.user_type_id = aat.id 63 WHERE 64 am.date < %s 65 AND am.company_id = %s 66 AND aat.include_initial_balance IS NOT TRUE 67 AND (aml.debit != 0 OR aml.credit != 0) 68 ''' 69 # For official report: only use posted entries 70 if self.export_type == "official": 71 sql_query += ''' 72 AND am.state = 'posted' 73 ''' 74 company = self.env.company 75 formatted_date_from = fields.Date.to_string(self.date_from).replace('-', '') 76 date_from = self.date_from 77 formatted_date_year = date_from.year 78 self._cr.execute( 79 sql_query, (formatted_date_year, formatted_date_from, formatted_date_from, formatted_date_from, self.date_from, company.id)) 80 listrow = [] 81 row = self._cr.fetchone() 82 listrow = list(row) 83 return listrow 84 85 def _get_company_legal_data(self, company): 86 """ 87 Dom-Tom are excluded from the EU's fiscal territory 88 Those regions do not have SIREN 89 sources: 90 https://www.service-public.fr/professionnels-entreprises/vosdroits/F23570 91 http://www.douane.gouv.fr/articles/a11024-tva-dans-les-dom 92 """ 93 dom_tom_group = self.env.ref('l10n_fr.dom-tom') 94 is_dom_tom = company.country_id.code in dom_tom_group.country_ids.mapped('code') 95 if not is_dom_tom and not company.vat: 96 raise UserError(_("Missing VAT number for company %s", company.name)) 97 if not is_dom_tom and company.vat[0:2] != 'FR': 98 raise UserError(_("FEC is for French companies only !")) 99 100 return { 101 'siren': company.vat[4:13] if not is_dom_tom else '', 102 } 103 104 def generate_fec(self): 105 self.ensure_one() 106 # We choose to implement the flat file instead of the XML 107 # file for 2 reasons : 108 # 1) the XSD file impose to have the label on the account.move 109 # but Odoo has the label on the account.move.line, so that's a 110 # problem ! 111 # 2) CSV files are easier to read/use for a regular accountant. 112 # So it will be easier for the accountant to check the file before 113 # sending it to the fiscal administration 114 today = fields.Date.today() 115 if self.date_from > today or self.date_to > today: 116 raise UserError(_('You could not set the start date or the end date in the future.')) 117 if self.date_from >= self.date_to: 118 raise UserError(_('The start date must be inferior to the end date.')) 119 120 company = self.env.company 121 company_legal_data = self._get_company_legal_data(company) 122 123 header = [ 124 u'JournalCode', # 0 125 u'JournalLib', # 1 126 u'EcritureNum', # 2 127 u'EcritureDate', # 3 128 u'CompteNum', # 4 129 u'CompteLib', # 5 130 u'CompAuxNum', # 6 We use partner.id 131 u'CompAuxLib', # 7 132 u'PieceRef', # 8 133 u'PieceDate', # 9 134 u'EcritureLib', # 10 135 u'Debit', # 11 136 u'Credit', # 12 137 u'EcritureLet', # 13 138 u'DateLet', # 14 139 u'ValidDate', # 15 140 u'Montantdevise', # 16 141 u'Idevise', # 17 142 ] 143 144 rows_to_write = [header] 145 # INITIAL BALANCE 146 unaffected_earnings_xml_ref = self.env.ref('account.data_unaffected_earnings') 147 unaffected_earnings_line = True # used to make sure that we add the unaffected earning initial balance only once 148 if unaffected_earnings_xml_ref: 149 #compute the benefit/loss of last year to add in the initial balance of the current year earnings account 150 unaffected_earnings_results = self.do_query_unaffected_earnings() 151 unaffected_earnings_line = False 152 153 sql_query = ''' 154 SELECT 155 'OUV' AS JournalCode, 156 'Balance initiale' AS JournalLib, 157 'OUVERTURE/' || %s AS EcritureNum, 158 %s AS EcritureDate, 159 MIN(aa.code) AS CompteNum, 160 replace(replace(MIN(aa.name), '|', '/'), '\t', '') AS CompteLib, 161 '' AS CompAuxNum, 162 '' AS CompAuxLib, 163 '-' AS PieceRef, 164 %s AS PieceDate, 165 '/' AS EcritureLib, 166 replace(CASE WHEN sum(aml.balance) <= 0 THEN '0,00' ELSE to_char(SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Debit, 167 replace(CASE WHEN sum(aml.balance) >= 0 THEN '0,00' ELSE to_char(-SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Credit, 168 '' AS EcritureLet, 169 '' AS DateLet, 170 %s AS ValidDate, 171 '' AS Montantdevise, 172 '' AS Idevise, 173 MIN(aa.id) AS CompteID 174 FROM 175 account_move_line aml 176 LEFT JOIN account_move am ON am.id=aml.move_id 177 JOIN account_account aa ON aa.id = aml.account_id 178 LEFT JOIN account_account_type aat ON aa.user_type_id = aat.id 179 WHERE 180 am.date < %s 181 AND am.company_id = %s 182 AND aat.include_initial_balance = 't' 183 AND (aml.debit != 0 OR aml.credit != 0) 184 ''' 185 186 # For official report: only use posted entries 187 if self.export_type == "official": 188 sql_query += ''' 189 AND am.state = 'posted' 190 ''' 191 192 sql_query += ''' 193 GROUP BY aml.account_id, aat.type 194 HAVING round(sum(aml.balance), %s) != 0 195 AND aat.type not in ('receivable', 'payable') 196 ''' 197 formatted_date_from = fields.Date.to_string(self.date_from).replace('-', '') 198 date_from = self.date_from 199 formatted_date_year = date_from.year 200 currency_digits = 2 201 202 self._cr.execute( 203 sql_query, (formatted_date_year, formatted_date_from, formatted_date_from, formatted_date_from, self.date_from, company.id, currency_digits)) 204 205 for row in self._cr.fetchall(): 206 listrow = list(row) 207 account_id = listrow.pop() 208 if not unaffected_earnings_line: 209 account = self.env['account.account'].browse(account_id) 210 if account.user_type_id.id == self.env.ref('account.data_unaffected_earnings').id: 211 #add the benefit/loss of previous fiscal year to the first unaffected earnings account found. 212 unaffected_earnings_line = True 213 current_amount = float(listrow[11].replace(',', '.')) - float(listrow[12].replace(',', '.')) 214 unaffected_earnings_amount = float(unaffected_earnings_results[11].replace(',', '.')) - float(unaffected_earnings_results[12].replace(',', '.')) 215 listrow_amount = current_amount + unaffected_earnings_amount 216 if float_is_zero(listrow_amount, precision_digits=currency_digits): 217 continue 218 if listrow_amount > 0: 219 listrow[11] = str(listrow_amount).replace('.', ',') 220 listrow[12] = '0,00' 221 else: 222 listrow[11] = '0,00' 223 listrow[12] = str(-listrow_amount).replace('.', ',') 224 rows_to_write.append(listrow) 225 226 #if the unaffected earnings account wasn't in the selection yet: add it manually 227 if (not unaffected_earnings_line 228 and unaffected_earnings_results 229 and (unaffected_earnings_results[11] != '0,00' 230 or unaffected_earnings_results[12] != '0,00')): 231 #search an unaffected earnings account 232 unaffected_earnings_account = self.env['account.account'].search([('user_type_id', '=', self.env.ref('account.data_unaffected_earnings').id)], limit=1) 233 if unaffected_earnings_account: 234 unaffected_earnings_results[4] = unaffected_earnings_account.code 235 unaffected_earnings_results[5] = unaffected_earnings_account.name 236 rows_to_write.append(unaffected_earnings_results) 237 238 # INITIAL BALANCE - receivable/payable 239 sql_query = ''' 240 SELECT 241 'OUV' AS JournalCode, 242 'Balance initiale' AS JournalLib, 243 'OUVERTURE/' || %s AS EcritureNum, 244 %s AS EcritureDate, 245 MIN(aa.code) AS CompteNum, 246 replace(MIN(aa.name), '|', '/') AS CompteLib, 247 CASE WHEN MIN(aat.type) IN ('receivable', 'payable') 248 THEN 249 CASE WHEN rp.ref IS null OR rp.ref = '' 250 THEN rp.id::text 251 ELSE replace(rp.ref, '|', '/') 252 END 253 ELSE '' 254 END 255 AS CompAuxNum, 256 CASE WHEN aat.type IN ('receivable', 'payable') 257 THEN COALESCE(replace(rp.name, '|', '/'), '') 258 ELSE '' 259 END AS CompAuxLib, 260 '-' AS PieceRef, 261 %s AS PieceDate, 262 '/' AS EcritureLib, 263 replace(CASE WHEN sum(aml.balance) <= 0 THEN '0,00' ELSE to_char(SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Debit, 264 replace(CASE WHEN sum(aml.balance) >= 0 THEN '0,00' ELSE to_char(-SUM(aml.balance), '000000000000000D99') END, '.', ',') AS Credit, 265 '' AS EcritureLet, 266 '' AS DateLet, 267 %s AS ValidDate, 268 '' AS Montantdevise, 269 '' AS Idevise, 270 MIN(aa.id) AS CompteID 271 FROM 272 account_move_line aml 273 LEFT JOIN account_move am ON am.id=aml.move_id 274 LEFT JOIN res_partner rp ON rp.id=aml.partner_id 275 JOIN account_account aa ON aa.id = aml.account_id 276 LEFT JOIN account_account_type aat ON aa.user_type_id = aat.id 277 WHERE 278 am.date < %s 279 AND am.company_id = %s 280 AND aat.include_initial_balance = 't' 281 AND (aml.debit != 0 OR aml.credit != 0) 282 ''' 283 284 # For official report: only use posted entries 285 if self.export_type == "official": 286 sql_query += ''' 287 AND am.state = 'posted' 288 ''' 289 290 sql_query += ''' 291 GROUP BY aml.account_id, aat.type, rp.ref, rp.id 292 HAVING round(sum(aml.balance), %s) != 0 293 AND aat.type in ('receivable', 'payable') 294 ''' 295 self._cr.execute( 296 sql_query, (formatted_date_year, formatted_date_from, formatted_date_from, formatted_date_from, self.date_from, company.id, currency_digits)) 297 298 for row in self._cr.fetchall(): 299 listrow = list(row) 300 account_id = listrow.pop() 301 rows_to_write.append(listrow) 302 303 # LINES 304 sql_query = ''' 305 SELECT 306 replace(replace(aj.code, '|', '/'), '\t', '') AS JournalCode, 307 replace(replace(aj.name, '|', '/'), '\t', '') AS JournalLib, 308 replace(replace(am.name, '|', '/'), '\t', '') AS EcritureNum, 309 TO_CHAR(am.date, 'YYYYMMDD') AS EcritureDate, 310 aa.code AS CompteNum, 311 replace(replace(aa.name, '|', '/'), '\t', '') AS CompteLib, 312 CASE WHEN aat.type IN ('receivable', 'payable') 313 THEN 314 CASE WHEN rp.ref IS null OR rp.ref = '' 315 THEN rp.id::text 316 ELSE replace(rp.ref, '|', '/') 317 END 318 ELSE '' 319 END 320 AS CompAuxNum, 321 CASE WHEN aat.type IN ('receivable', 'payable') 322 THEN COALESCE(replace(replace(rp.name, '|', '/'), '\t', ''), '') 323 ELSE '' 324 END AS CompAuxLib, 325 CASE WHEN am.ref IS null OR am.ref = '' 326 THEN '-' 327 ELSE replace(replace(am.ref, '|', '/'), '\t', '') 328 END 329 AS PieceRef, 330 TO_CHAR(am.date, 'YYYYMMDD') AS PieceDate, 331 CASE WHEN aml.name IS NULL OR aml.name = '' THEN '/' 332 WHEN aml.name SIMILAR TO '[\t|\s|\n]*' THEN '/' 333 ELSE replace(replace(replace(replace(aml.name, '|', '/'), '\t', ''), '\n', ''), '\r', '') END AS EcritureLib, 334 replace(CASE WHEN aml.debit = 0 THEN '0,00' ELSE to_char(aml.debit, '000000000000000D99') END, '.', ',') AS Debit, 335 replace(CASE WHEN aml.credit = 0 THEN '0,00' ELSE to_char(aml.credit, '000000000000000D99') END, '.', ',') AS Credit, 336 CASE WHEN rec.name IS NULL THEN '' ELSE rec.name END AS EcritureLet, 337 CASE WHEN aml.full_reconcile_id IS NULL THEN '' ELSE TO_CHAR(rec.create_date, 'YYYYMMDD') END AS DateLet, 338 TO_CHAR(am.date, 'YYYYMMDD') AS ValidDate, 339 CASE 340 WHEN aml.amount_currency IS NULL OR aml.amount_currency = 0 THEN '' 341 ELSE replace(to_char(aml.amount_currency, '000000000000000D99'), '.', ',') 342 END AS Montantdevise, 343 CASE WHEN aml.currency_id IS NULL THEN '' ELSE rc.name END AS Idevise 344 FROM 345 account_move_line aml 346 LEFT JOIN account_move am ON am.id=aml.move_id 347 LEFT JOIN res_partner rp ON rp.id=aml.partner_id 348 JOIN account_journal aj ON aj.id = am.journal_id 349 JOIN account_account aa ON aa.id = aml.account_id 350 LEFT JOIN account_account_type aat ON aa.user_type_id = aat.id 351 LEFT JOIN res_currency rc ON rc.id = aml.currency_id 352 LEFT JOIN account_full_reconcile rec ON rec.id = aml.full_reconcile_id 353 WHERE 354 am.date >= %s 355 AND am.date <= %s 356 AND am.company_id = %s 357 AND (aml.debit != 0 OR aml.credit != 0) 358 ''' 359 360 # For official report: only use posted entries 361 if self.export_type == "official": 362 sql_query += ''' 363 AND am.state = 'posted' 364 ''' 365 366 sql_query += ''' 367 ORDER BY 368 am.date, 369 am.name, 370 aml.id 371 ''' 372 self._cr.execute( 373 sql_query, (self.date_from, self.date_to, company.id)) 374 375 for row in self._cr.fetchall(): 376 rows_to_write.append(list(row)) 377 378 fecvalue = self._csv_write_rows(rows_to_write) 379 end_date = fields.Date.to_string(self.date_to).replace('-', '') 380 suffix = '' 381 if self.export_type == "nonofficial": 382 suffix = '-NONOFFICIAL' 383 384 self.write({ 385 'fec_data': base64.encodebytes(fecvalue), 386 # Filename = <siren>FECYYYYMMDD where YYYMMDD is the closing date 387 'filename': '%sFEC%s%s.csv' % (company_legal_data['siren'], end_date, suffix), 388 }) 389 390 # Set fiscal year lock date to the end date (not in test) 391 fiscalyear_lock_date = self.env.company.fiscalyear_lock_date 392 if not self.test_file and (not fiscalyear_lock_date or fiscalyear_lock_date < self.date_to): 393 self.env.company.write({'fiscalyear_lock_date': self.date_to}) 394 return { 395 'name': 'FEC', 396 'type': 'ir.actions.act_url', 397 'url': "web/content/?model=account.fr.fec&id=" + str(self.id) + "&filename_field=filename&field=fec_data&download=true&filename=" + self.filename, 398 'target': 'self', 399 } 400 401 def _csv_write_rows(self, rows, lineterminator=u'\r\n'): 402 """ 403 Write FEC rows into a file 404 It seems that Bercy's bureaucracy is not too happy about the 405 empty new line at the End Of File. 406 407 @param {list(list)} rows: the list of rows. Each row is a list of strings 408 @param {unicode string} [optional] lineterminator: effective line terminator 409 Has nothing to do with the csv writer parameter 410 The last line written won't be terminated with it 411 412 @return the value of the file 413 """ 414 fecfile = io.BytesIO() 415 writer = pycompat.csv_writer(fecfile, delimiter='|', lineterminator='') 416 417 rows_length = len(rows) 418 for i, row in enumerate(rows): 419 if not i == rows_length - 1: 420 row[-1] += lineterminator 421 writer.writerow(row) 422 423 fecvalue = fecfile.getvalue() 424 fecfile.close() 425 return fecvalue 426