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 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 _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 _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['messages'], "results should be empty on successful import")
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.append(lineterminator) writer.writerow(row) fecvalue = fecfile.getvalue() fecfile.close() return fecvalue
def action_csv(self): output = io.BytesIO() writer = pycompat.csv_writer(output, quoting=1) writer.writerow(['Company', 'FirstName', 'LastName', 'Email', 'Phone', 'Quantity', 'CardValue', 'ToName', 'CarrierMessage', 'FromName']) for rec in self: writer.writerow([ rec.partner_id.parent_id.name or '', rec.partner_id.name.split(' ')[0], rec.partner_id.name.split(' ', 1)[1] if len(rec.partner_id.name.split(' ')) > 1 else '', rec.partner_id.email or '', rec.partner_id.phone or '', '1', '100', rec.partner_id.name, 'Thank you for your referral and thank you for being part of the SolarSamritan movement.\n\nBest Wishes from the Team at Beyond Solar.', 'BeyondSolar' ]) attachment = self.env['ir.attachment'].create({ 'name': "DigitalCorporateOrderUpload.csv", 'datas': base64.b64encode(output.getvalue()), 'type': 'binary', 'mimetype': 'text/csv', }) return { 'name': "Referral Upload", 'type': 'ir.actions.act_url', 'url': f'/web/content/{attachment.id}/DigitalCorporateOrderUpload.csv', 'target': 'new' }
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 voicent_start_campaign(self, call_line): for rec in self: if call_line.helpdesk_ticket_stage_id.id == rec.stage_id.id: # Generate the CSV file fp = io.BytesIO() writer = pycompat.csv_writer(fp, quoting=1) headers = [] vals = [] MailTemplate = self.env['mail.template'] for field in call_line.contact_ids: head = field.name if head == 'Other': head = field.other lang = (rec.id and rec.partner_id.lang or "en_US") value = \ MailTemplate.with_context(lang=lang)._render_template( '${object.' + field.field_domain + '}', 'helpdesk.ticket', rec.id) if not value: value = field.default_value headers.append(head) vals.append(value) writer.writerow(headers) writer.writerow(vals) directory = tempfile.mkdtemp(suffix='-helpdesk.ticket') file_name = \ directory + "/" + str(rec.id) + '-' + \ str(rec.stage_id.id) + '-' + \ fields.Datetime.now().strftime('%Y-%m-%d-%H-%M-%S') + \ '.csv' write_file = open(file_name, 'wb') write_file.write(fp.getvalue()) write_file.close() # Connect to Voicent voicent_obj = voicent.Voicent(call_line.backend_id.host, str(call_line.backend_id.port), call_line.backend_id.callerid, str(call_line.backend_id.line)) res = voicent_obj.importAndRunCampaign(file_name, call_line.msgtype, call_line.msginfo) # Delete the file on the filesystem shutil.rmtree(directory) if res.get('camp_id') and res.get('status') == 'OK': rec.voicent_check_status(res.get('camp_id'), call_line) else: message = _("""Call has been sent to <b>%s</b> but failed with the following message: <b>%s</b>""" % (call_line.backend_id.name, res)) rec.message_post(body=message) raise RetryableJobError(res) else: message = _("Call has been cancelled because the stage has " "changed.") rec.message_post(body=message)
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 _csv_write_rows(rows): f = io.BytesIO() writer = pycompat.csv_writer(f, delimiter=',', quotechar='"', quoting=2, dialect='excel') rows_length = len(rows) for i, row in enumerate(rows): writer.writerow(row) fvalue = f.getvalue() f.close() return fvalue
def from_data(self, fields, rows): fp = io.BytesIO() writer = pycompat.csv_writer(fp, quoting=1) writer.writerow(fields) for data in rows: row = [] for d in data: # Spreadsheet apps tend to detect formulas on leading =, + and - if isinstance(d, str) and d.startswith(('=', '-', '+')): d = "'" + d row.append(pycompat.to_text(d)) writer.writerow(row) return fp.getvalue()
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 prepareCsv(self, mainData, invoice_type, gstToolName, gstType): attachment = False if mainData: fp = BytesIO() # writer = csv.writer(fp, quoting=csv.QUOTE_NONE,escapechar='\\') writer = pycompat.csv_writer(fp, quoting=csv.QUOTE_NONE, escapechar='\\') if invoice_type == 'b2b': columns = self.env['csv.column'].getB2BColumn(gstType) writer.writerow(columns) elif invoice_type == 'b2bur': columns = self.env['csv.column'].getB2BURColumn() writer.writerow(columns) elif invoice_type == 'b2cl': columns = self.env['csv.column'].getB2CLColumn() writer.writerow(columns) elif invoice_type == 'b2cs': columns = self.env['csv.column'].getB2CSColumn() writer.writerow(columns) elif invoice_type == 'imps': columns = self.env['csv.column'].getImpsColumn() writer.writerow(columns) elif invoice_type == 'impg': columns = self.env['csv.column'].getImpgColumn() writer.writerow(columns) elif invoice_type == 'export': columns = self.env['csv.column'].getExportColumn() writer.writerow(columns) elif invoice_type == 'hsn': columns = self.env['csv.column'].getHSNColumn() writer.writerow(columns) for lineData in mainData: _logger.info('Line Data (%s)', lineData) writer.writerow([_unescape(name) for name in lineData]) fp.seek(0) data = fp.read() fp.close() attachment = self.generateAttachment(data, invoice_type, gstToolName) return attachment
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 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 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 _get_partner_list(self, options): date_from = fields.Date.from_string( options.get('date').get('date_from')) date_to = fields.Date.from_string(options.get('date').get('date_to')) fy = self.env.user.company_id.compute_fiscalyear_dates(date_to) date_from = datetime.strftime(date_from, '%Y%m%d') date_to = datetime.strftime(date_to, '%Y%m%d') fy = datetime.strftime(fy.get('date_from'), '%Y%m%d') datev_info = self._get_datev_client_number() output = io.BytesIO() writer = pycompat.csv_writer(output, delimiter=';', quotechar='"', quoting=2) preheader = [ 'EXTF', 510, 16, 'Debitoren/Kreditoren', 4, None, None, '', '', '', datev_info[0], datev_info[1], fy, 8, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '' ] header = [ 'Konto', 'Name (AdressatentypUnternehmen)', 'Name (Adressatentypnatürl. Person)', '', '', '', 'Adressatentyp' ] move_line_ids = self.with_context(self._set_context(options), print_mode=True, aml_only=True)._get_lines(options) lines = [preheader, header] if len(move_line_ids): self.env.cr.execute( """ SELECT distinct(aml.partner_id) FROM account_move_line aml LEFT JOIN account_move m ON aml.move_id = m.id WHERE aml.id IN %s AND aml.tax_line_id IS NULL AND aml.debit != aml.credit AND aml.account_id != m.l10n_de_datev_main_account_id""", (tuple(move_line_ids), )) partners = self.env['res.partner'].browse( [p.get('partner_id') for p in self.env.cr.dictfetchall()]) for partner in partners: code = self._find_partner_account( partner.property_account_receivable_id, partner) line_value = { 'code': code, 'company_name': partner.name if partner.is_company else '', 'person_name': '' if partner.is_company else partner.name, 'natural': partner.is_company and '2' or '1' } # Idiotic program needs to have a line with 243 elements ordered in a given fashion as it # does not take into account the header and non mandatory fields array = ['' for x in range(243)] array[0] = line_value.get('code') array[1] = line_value.get('company_name') array[2] = line_value.get('person_name') array[6] = line_value.get('natural') lines.append(array) code_payable = self._find_partner_account( partner.property_account_payable_id, partner) if code_payable != code: line_value['code'] = code_payable array[0] = line_value.get('code') lines.append(array) writer.writerows(lines) return output.getvalue()
def transfert_compta(self, **kw): #s = s[ beginning : beginning + LENGTH] date_d = self.date_start[0:4] + self.date_start[5:7] + self.date_start[ 8:10] date_f = self.date_end[0:4] + self.date_end[5:7] + self.date_end[8:10] query_args = {'date_start': date_d, 'date_end': date_f} #dirpath2 = os.path.dirname(os.path.realpath(__file__)) # c:\odoo\odoo11\addons_adinfo\hubi\models # General Settings company_id = self.env['res.company']._company_default_get( 'hubi.general_settings') val_company_id = company_id.id val_name = 'General Settings' settings = self.env['hubi.general_settings'].search([ ('name', '=', val_name), ('company_id', '=', val_company_id) ]) if settings: auxiliary_account = settings.auxiliary_accounting length_account_gen = settings.length_account_general length_account_aux = settings.length_account_auxiliary complete_0_gen = settings.complete_0_account_general or False complete_0_aux = settings.complete_0_account_general or False else: auxiliary_account = False length_account_gen = 0 length_account_aux = 0 complete_0_gen = False complete_0_aux = False csv_path = self.path_account_transfer account_file = self.account_file_transfer writing_file = self.writing_file_transfer if csv_path is None: csv_path = os.environ.get( 'LOCALAPPDATA') or os.getcwd() # c:\odoo\odoo11 if account_file is None: account_file = 'comptes.txt' if writing_file is None: writing_file = 'ecritures.txt' """ csv_path = os.path.normpath(csv_path) if not os.path.exists(csv_path): os.makedirs(csv_path) os.chdir(csv_path) """ # Account File partner_file = io.BytesIO() w_p = pycompat.csv_writer(partner_file, delimiter=',') #fpc = io.open(account_file, 'w', encoding='utf-8') csv_p = "" ligne_p = "" sql_p = """SELECT distinct am.partner_id, res_partner.name, res_partner.street, res_partner.city, res_partner.zip, res_country.code as code_pays, res_country.name as country, res_partner.phone, res_partner.mobile, am.company_id from account_move as am INNER JOIN res_partner on res_partner.id = am.partner_id INNER JOIN res_country on res_country.id = res_partner.country_id WHERE am.state = 'posted' AND to_char(am.date,'YYYYMMDD') BETWEEN %s AND %s AND am.company_id = %s AND am.journal_id IN %s ORDER BY am.partner_id""" self.env.cr.execute(sql_p, ( date_d, date_f, val_company_id, tuple(self.journal_ids.ids), )) ids_p = [(r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8]) for r in self.env.cr.fetchall()] for partner_id, name, street, city, zip, code_pays, country, phone, mobile in ids_p: ret_p = self.ecrire_ligne_comptes_ebp( auxiliary_account, length_account_gen, length_account_aux, complete_0_gen, complete_0_aux, partner_id, name, street, city, zip, code_pays, country, phone, mobile) ligne_p = ret_p['ligne_p'] listrow_p = ret_p['listrow_p'] listrow_ps = ret_p['listrow_ps'] csv_p += ligne_p w_p.writerow(listrow_p) if len(listrow_ps) != 0: w_p.writerow(listrow_ps) #fpc.write(csv_p) #fpc.close() # Transfert Invoices compta_file = io.BytesIO() w = pycompat.csv_writer(compta_file, delimiter=',') #fpi = io.open(writing_file, 'w', encoding='utf-8') sql = """SELECT aml.id, am.name as move_name, account_journal.code as journal,account_account.code as compte, res_partner.name as partner, aml.name as move_line_name, to_char(am.date,'DDMMYYYY') as date_ecr, to_char(aml.date_maturity,'DDMMYYYY') as date_ech, aml.debit, aml.credit, res_currency.name as currency, aml.ref as ref, aaa.code as compte_anal, am.partner_id, aml.company_id from account_move_line as aml INNER JOIN account_move as am on am.id = aml.move_id INNER JOIN account_journal on account_journal.id = am.journal_id INNER JOIN res_currency on res_currency.id = am.currency_id INNER JOIN res_partner on res_partner.id = am.partner_id INNER JOIN account_account on account_account.id = aml.account_id LEFT JOIN account_analytic_account as aaa on aaa.id = aml.analytic_account_id WHERE am.state = 'posted' AND to_char(am.date,'YYYYMMDD') BETWEEN %s AND %s AND aml.company_id = %s AND am.journal_id IN %s AND aml.transfer_accounting is %s ORDER BY account_journal.code, am.id, account_account.code""" #AND aml.transfer_accounting is not true self.env.cr.execute(sql, ( date_d, date_f, val_company_id, tuple(self.journal_ids.ids), self.re_transfer, )) nb_lig = 0 csv = "" ligne = "" ids = [(r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8], r[9], r[10], r[11], r[12], r[13]) for r in self.env.cr.fetchall()] for line_id, move_name, journal, compte, partner_name, move_line_name, date_ecr, date_ech, debit, credit, currency, ref, compte_anal, partner_id in ids: nb_lig += 1 listrow = list() #ligne = self.ecrire_ligne_ebp(auxiliary_account,length_account_gen,length_account_aux,complete_0_gen,complete_0_aux, move_name, journal, compte, partner_name, move_line_name, date_ecr, date_ech, debit, credit, currency, ref, compte_anal, partner_id, nb_lig) ret = self.ecrire_ligne_ebp( auxiliary_account, length_account_gen, length_account_aux, complete_0_gen, complete_0_aux, move_name, journal, compte, partner_name, move_line_name, date_ecr, date_ech, debit, credit, currency, ref, compte_anal, partner_id, nb_lig) ligne = ret['ligne'] listrow = ret['listrow'] csv += ligne w.writerow(listrow) line = self.env['account.move.line'].browse(line_id) line.update({'transfer_accounting': True}) #fpi.write(csv) #fpi.close() #controller_export_csv_order_line.SaleOrderController.transfert_compta_csv_download(self,csv,writing_file) if self.mail_accounting: # send EMail #return self.send_mail_template() attachments_ids = [] comptavalue = compta_file.getvalue() partnervalue = partner_file.getvalue() if comptavalue is not None: attachment_w = { 'name': ("%s" % writing_file), 'datas_fname': writing_file, 'datas': base64.encodestring(comptavalue), 'type': 'binary' } id_w = self.env['ir.attachment'].create(attachment_w) attachments_ids.append(id_w.id) if partnervalue is not None: attachment_a = { 'name': ("%s" % account_file), 'datas_fname': account_file, 'datas': base64.encodestring(partnervalue), 'type': 'binary' } id_a = self.env['ir.attachment'].create(attachment_a) attachments_ids.append(id_a.id) email_template = self.env.ref( 'hubi.email_template_accounting_transfer') email_template.attachment_ids = False email_template.attachment_ids = attachments_ids ir_model_data = self.env['ir.model.data'] try: template_id = ir_model_data.get_object_reference( 'hubi', 'email_template_accounting_transfer')[1] except ValueError: template_id = False try: compose_form_id = ir_model_data.get_object_reference( 'mail', 'email_compose_message_wizard_form')[1] except ValueError: compose_form_id = False ctx = { 'default_model': 'wiz.transfertcompta', 'default_res_id': self.ids[0], 'default_use_template': bool(template_id), 'default_template_id': template_id, 'default_composition_mode': 'comment', 'attachment_ids': attachments_ids, 'force_email': True } return { 'type': 'ir.actions.act_window', 'view_type': 'form', 'view_mode': 'form', 'res_model': 'mail.compose.message', 'views': [(compose_form_id, 'form')], 'view_id': compose_form_id, 'target': 'new', 'context': ctx, } else: #return {'type': 'ir.actions.act_window_close'} """ view_id = self.env["ir.model.data"].get_object_reference("hubi", "wiz_transfert_compta_step2") self.message = ("%s %s %s %s") % ("Create Accounting transfer for ",nb_lig, " lines. Sur ", csv_path ) return {"type":"ir.actions.act_window", "view_mode":"form", "view_type":"form", "views":[(view_id[1], "form")], "res_id":self.id, "target":"new", "res_model":"wiz.transfertcompta" } """ comptavalue = compta_file.getvalue() partnervalue = partner_file.getvalue() self.write({ 'compta_data': base64.encodestring(comptavalue), 'filename': writing_file, 'partner_data': base64.encodestring(partnervalue), 'partner_filename': account_file, }) compta_file.close() action_writing = { 'name': 'hubi_transfert_compta', 'type': 'ir.actions.act_url', 'url': "web/content/?model=wiz.transfertcompta&id=" + str(self.id) + "&filename_field=filename&field=compta_data&download=true&filename=" + self.filename, 'target': 'self', } action_partner = { 'name': 'hubi_transfert_compta_partner', 'type': 'ir.actions.act_url', 'url': "web/content/?model=wiz.transfertcompta&id=" + str(self.id) + "&filename_field=partner_filename&field=partner_data&download=true&filename=" + self.partner_filename, 'target': 'self', } action = { 'name': 'hubi_transfert_compta', 'type': 'ir.actions.act_url', 'url': "web/content/?model=wiz.transfertcompta&id=" + str(self.id) + "&filename_field=filename&field=compta_data&download=true&filename=" + self.filename + "&filename_field=partner_filename&field=partner_data&download=true&filename=" + self.partner_filename, 'target': 'self', } return action_writing
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 get_csv(self, options): # last 2 element of preheader should be filled by "consultant number" and "client number" date_from = fields.Date.from_string( options.get('date').get('date_from')) date_to = fields.Date.from_string(options.get('date').get('date_to')) fy = self.env.user.company_id.compute_fiscalyear_dates(date_to) date_from = datetime.strftime(date_from, '%Y%m%d') date_to = datetime.strftime(date_to, '%Y%m%d') fy = datetime.strftime(fy.get('date_from'), '%Y%m%d') datev_info = self._get_datev_client_number() output = io.BytesIO() writer = pycompat.csv_writer(output, delimiter=';', quotechar='"', quoting=2) preheader = [ 'EXTF', 510, 21, 'Buchungsstapel', 7, '', '', '', '', '', datev_info[0], datev_info[1], fy, 8, date_from, date_to, '', '', '', '', 0, 'EUR', '', '', '', '', '', '', '', '', '' ] header = [ 'Umsatz (ohne Soll/Haben-Kz)', 'Soll/Haben-Kennzeichen', 'WKZ Umsatz', 'Kurs', 'Basis-Umsatz', 'WKZ Basis-Umsatz', 'Konto', 'Gegenkonto (ohne BU-Schlüssel)', 'BU-Schlüssel', 'Belegdatum', 'Belegfeld 1', 'Belegfeld 2', 'Skonto', 'Buchungstext' ] move_line_ids = self.with_context(self._set_context(options), print_mode=True, aml_only=True)._get_lines(options) lines = [preheader, header] moves = move_line_ids # find all account_move if len(move_line_ids): self.env.cr.execute( """SELECT distinct(move_id) FROM account_move_line WHERE id IN %s""", (tuple(move_line_ids), )) move_ids = [l.get('move_id') for l in self.env.cr.dictfetchall()] moves = self.env['account.move'].browse(move_ids) for m in moves: # Account-counterAccount-amount-partner move_line_set = {} for aml in m.line_ids: if aml.debit == aml.credit: # Ignore debit = credit = 0 continue # If both account and counteraccount are the same, ignore the line if aml.account_id == aml.move_id.l10n_de_datev_main_account_id: continue # If line is a tax ignore it as datev requires single line with gross amount and deduct tax itself based # on account or on the control key code if aml.tax_line_id: continue amount = abs(aml.balance) # Finding back the amount with tax included from the move can be tricky # The line with the tax contains the tax_base_amount which is the gross value # However if we have 2 product lines with the same tax, we will have a move like this # Debit Credit Tax_line_id Tax_ids Tax_base_amount Account_id # 10 0 false false 0 Receivable # 0 3 1 false 7 Tax # 0 3,5 false [1] 0 Income Account # 0 3,5 false [1] 0 Income Account # # What we want to export for his move in datev is something like this: # Account CounterAccount Amount # Income Account Receivable 10 # # So when we are on an "Income Account" line linked to a tax, we try to find back # the line representing the tax and we use as amount the tax line balance + base_amount # This means that in the case we happen to have another line with same tax and income account, # (as described above), We have to skip it. # A Problem that might happen since tax are grouped is: if we have a line # with tax 1 and 2 specified on the line, and another line with only tax 1 # specified on the line. This will result in a case where we can't easily get # back the gross amount for both lines. This case is not supported by this export # function and will result in incorrect exported lines for datev. code_correction = '' if aml.tax_ids: amount = 0 for tax in aml.tax_ids: # Find tax line in the move and get it's tax_base_amount tax_line = m.line_ids.filtered( lambda l: l.tax_line_id == tax and l.partner_id == aml.partner_id) amount += abs(tax_line.balance) + abs( tax_line.tax_base_amount) # account and counterpart account to_account_code = self._find_partner_account( aml.move_id.l10n_de_datev_main_account_id, aml.partner_id) account_code = u'{code}'.format( code=self._find_partner_account(aml.account_id, aml.partner_id)) if not aml.account_id.tax_ids and aml.tax_ids: # Take the correction code from the tax code_correction = aml.tax_ids[0].l10n_de_datev_code # If line is already to be add from this move, skip it match_key = '%s-%s-%s-%s' % (account_code, to_account_code, amount, aml.partner_id) if (move_line_set.get(match_key)): continue else: move_line_set[match_key] = True # reference receipt1 = aml.move_id.name if aml.move_id.journal_id.type == 'purchase' and aml.move_id.ref: receipt1 = aml.move_id.ref # on receivable/payable aml of sales/purchases receipt2 = '' if to_account_code == account_code and aml.date_maturity: receipt2 = aml.date currency = aml.company_id.currency_id partner_vat = aml.tax_ids and aml.move_id.partner_id.vat or '' line_value = { 'waehrung': currency.name, 'sollhaben': 's' if aml.balance >= 0 else 'h', 'amount': str(amount).replace('.', ','), 'buschluessel': code_correction, 'gegenkonto': to_account_code, 'belegfeld1': receipt1[-12:], 'belegfeld2': receipt2, 'datum': aml.move_id.date, 'konto': account_code or '', 'kurs': str(currency.rate).replace('.', ','), 'buchungstext': receipt1, } # Idiotic program needs to have a line with 116 elements ordered in a given fashion as it # does not take into account the header and non mandatory fields array = ['' for x in range(116)] array[0] = line_value.get('amount') array[1] = line_value.get('sollhaben') array[2] = line_value.get('waehrung') array[6] = line_value.get('konto') array[7] = line_value.get('gegenkonto') array[8] = line_value.get('buschluessel') array[9] = line_value.get('datum') array[10] = line_value.get('belegfeld1') array[11] = line_value.get('belegfeld2') array[13] = line_value.get('buchungstext') lines.append(array) writer.writerows(lines) return output.getvalue()
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 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, 'Balance initiale ' || MIN(aa.name) 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), '999999999999999D99') END, '.', ',') AS Debit, replace(CASE WHEN sum(aml.balance) >= 0 THEN '0,00' ELSE to_char(-SUM(aml.balance), '999999999999999D99') 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 HAVING sum(aml.balance) != 0 ''' formatted_date_from = self.date_from.replace('-', '') self._cr.execute(sql_query, (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) # 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 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, '999999999999999D99') END, '.', ',') AS Debit, replace(CASE WHEN aml.credit = 0 THEN '0,00' ELSE to_char(aml.credit, '999999999999999D99') 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, '999999999999999D99'), '.', ',') 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
def _process(format, rows, buffer): this = self[0] if format == 'txt': writer = pycompat.csv_writer(buffer, delimiter='|', quotechar='|', quoting=csv.QUOTE_MINIMAL, dialect='UNIX') if this.type == 'ventas': number_file = 1 for inv in rows: fecha = inv.date_invoice.strftime('%d/%m/%Y') writer.writerow( (str('3'), str(number_file), str(fecha), str(inv.n_factura), str(inv.n_autorizacion), str(inv.state_sin), str(inv.nit_ci or ''), str(inv.razon_social or ''), str(inv.amount_open), str(inv.amount_ice_iehd), str(inv.amount_exe), '0', str(inv.amount_open - inv.amount_ice_iehd - inv.amount_exe), str(inv.amount_des), str(inv.amount_open - inv.amount_ice_iehd - inv.amount_exe - inv.amount_des), str(inv.amount_iva), str(inv.codigo_control))) number_file = number_file + 1 elif this.type == 'compras': number_file = 1 for inv in rows: fecha = inv.date_invoice.strftime('%d/%m/%Y') writer.writerow( (str('1'), str(number_file), str(fecha), str(inv.nit_ci), str(inv.razon_social or ''), str(inv.n_factura), str(inv.n_dui or '0'), str(inv.n_autorizacion or '0'), str(inv.amount_open), str(inv.amount_exe), str(inv.amount_open - inv.amount_exe), str(inv.amount_des), str(inv.amount_open - inv.amount_exe - inv.amount_des), str(inv.amount_iva), str(inv.codigo_control or '0'), str(inv.tipo_com or ''))) number_file = number_file + 1 elif format == 'csv': writer = pycompat.csv_writer(buffer, delimiter=',', quotechar='|', quoting=csv.QUOTE_MINIMAL, dialect='UNIX') if this.type == 'ventas': writer.writerow( ("ESPECIFICACION", "NRO", "FECHA DE LA FACTURA", "N DE LA FACTURA", "N DE AUTORIZACION", "ESTADO", "NIT/CI CLIENTE", "NOMBRE O RAZON SOCIAL", "IMPORTE TOTAL DE LA VENTA", "IMPORTE ICE/IEHD/IPJ/TASAS/OTROS NO SUJETOS AL IVA", "EXPORTACIONES Y OPERACIONES EXENTAS", "VENTAS GRAVADAS A TASA CERO", "SUBTOTAL", "DESCUENTOS BONIFICACIONES Y REBAJAS SUJETAS AL IVA", "IMPORTE BASE PARA DEBITO FISCAL", "DEBITO FISCAL", "CODIGO DE CONTROL")) number_file = 1 for inv in rows: fecha = inv.date_invoice.strftime('%d/%m/%Y') writer.writerow( (str('3'), str(number_file), str(fecha), str(inv.n_factura), str(inv.n_autorizacion), str(inv.state_sin), str(inv.nit_ci or ''), str(inv.razon_social or ''), str(inv.amount_open), str(inv.amount_ice_iehd), str(inv.amount_exe), '0', str(inv.amount_open - inv.amount_ice_iehd - inv.amount_exe), str(inv.amount_des), str(inv.amount_open - inv.amount_ice_iehd - inv.amount_exe - inv.amount_des), str(inv.amount_iva), str(inv.codigo_control))) number_file = number_file + 1 elif this.type == 'compras': writer.writerow( ("ESPECIFICACION", "NRO", "FECHA DE LA FACTURA", "NIT PROVEEDOR", "NOMBRE Y APELLIDO/RAZON SOCIAL", "N° DE LA FACTURA", "N° DE DUI", "N° DE AUTORIZACIÓN", "IMPORTE TOTAL DE LA COMPRA", "IMPORTE NO SUJETO A CREDITO FISCAL", "SUBTOTAL", "DESCUENTOS BONIFICACIONES Y REBAJAS OBTENIDAS", "IMPORTE BASE PARA CRÉDITO FISCAL", "CRÉDITO FISCAL", "CODIGO DE CONTROL", "TIPO DE COMPRA")) number_file = 1 for inv in rows: fecha = inv.date_invoice.strftime('%d/%m/%Y') writer.writerow( (str('1'), str(number_file), str(fecha), str(inv.nit_ci), str(inv.razon_social or ''), str(inv.n_factura), str(inv.n_dui or '0'), str(inv.n_autorizacion or '0'), str(inv.amount_open), str(inv.amount_exe), str(inv.amount_open - inv.amount_exe), str(inv.amount_des), str(inv.amount_open - inv.amount_exe - inv.amount_des), str(inv.amount_iva), str(inv.codigo_control or '0'), str(inv.tipo_com or ''))) number_file = number_file + 1 else: raise Exception( _('Unrecognized extension: must be one of ' '.csv, .po, or .tgz (received .%s).') % format)
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 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 = 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)) 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 = fields.Date.to_string(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