# Copyright 2019 Le Filament (<http://www.le-filament.com>)
# License AGPL-3 or later (http://www.gnu.org/licenses/agpl.html).
import csv
import re
from datetime import datetime
from io import BytesIO, StringIO
from odoo import models, fields, api, http
from odoo.addons.web.controllers.main import serialize_exception
from odoo.addons.web.controllers.main import content_disposition
from odoo.fields import Date
from odoo.http import request
from odoo.tools import pycompat
from odoo.tools.misc import xlwt
HEADER_DEFAULT = [
'date',
'journal',
'compte',
'debit',
'credit',
'libelle',
'piece',
'echeance',
'ref_piece'
]
HEADER_DEFAULT_CEGID = [
'journal',
'date',
'compte',
'auxiliaire',
'ref_piece',
'libelle',
'debit',
'credit',
]
HEADER_DEFAULT_CEGID_FILE = [
'Journal',
'Date',
'Général',
'Auxiliaire',
'Référence',
'Libellé',
'Débit',
'Crédit',
]
class AccountDatasExportWizard(models.TransientModel):
_name = "invoice.line.export"
date_start = fields.Date('Date de début', required=True)
date_end = fields.Date('Date de fin',
required=True,
default=datetime.today().strftime('%Y-%m-%d'))
export_format = fields.Selection([('csv', 'CSV'), ('xls', 'Excel')],
'Format', default="csv")
journal = fields.Selection([('ventes', 'Ventes'),
('achats', 'Achats')],
'Journal', default='ventes')
@api.multi
def get_data_file(self):
nom_outil_compta = self.env['ir.config_parameter'].sudo().get_param(
'account.nom_outil_compta')
return {
'type': 'ir.actions.act_url',
'url':
'/web/export_journal?format=%s&journal=%s&nom_outil_compta=%s'
% (self.export_format, self.journal, nom_outil_compta) +
'&date_start=%s&date_end=%s'
% (self.date_start, self.date_end),
'target': 'new',
}
class AccountDatasExport(http.Controller):
def export_csv(self, lignes_export, columns, filename_, nom_outil_compta):
fp = StringIO()
export_file = csv.writer(fp, delimiter=';', quoting=csv.QUOTE_ALL)
row = []
# Add header line only if outil = cegid
if nom_outil_compta == 'cegid':
header_file = HEADER_DEFAULT_CEGID_FILE
for head in header_file:
row.append(head)
export_file.writerow(row)
for line in lignes_export:
row = []
for column in columns:
if isinstance(line[column], str):
try:
value = line[column]
except UnicodeError:
pass
else:
if ((column == 'date') or (column == 'echeance')
and line[column]):
value = Date.to_date(line[column]).strftime('%d/%m/%Y')
elif (column == 'credit') or (column == 'debit'):
value = str(line[column]).replace('.', ',')
else:
value = line[column]
row.append(value)
export_file.writerow(row)
fp.seek(0)
data = fp.read()
fp.close()
filename = filename_ + '.csv'
csvhttpheaders = [
('Content-Type', 'text/csv;charset=utf8'),
('Content-Disposition', content_disposition(filename)),
]
return request.make_response(data, headers=csvhttpheaders)
def export_xls(self, lignes_export, columns, filename_, nom_outil_compta):
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet(filename_)
if nom_outil_compta == 'cegid':
header_file = HEADER_DEFAULT_CEGID_FILE
else:
header_file = columns
for i, fieldname in enumerate(header_file):
worksheet.write(0, i, fieldname)
worksheet.col(i).width = 8000 # around 220 pixels
base_style = xlwt.easyxf('align: wrap yes')
for row_index, line in enumerate(lignes_export):
for cell_index, column in enumerate(columns):
cell_style = base_style
if ((column == 'date') or (column == 'echeance')
and line[column]):
cell_value = Date.to_date(line[column]).strftime(
'%d/%m/%Y')
elif (column == 'credit') or (column == 'debit'):
cell_value = line[column]
elif isinstance(line[column], pycompat.string_types):
cell_value = re.sub("\r", " ", line[column])
else:
cell_value = line[column]
worksheet.write(row_index + 1, cell_index,
cell_value, cell_style)
fp = BytesIO()
workbook.save(fp)
fp.seek(0)
data = fp.read()
fp.close()
filename = filename_ + '.xls'
xlshttpheaders = [
('Content-Type', 'text/csv;charset=utf8'),
('Content-Disposition', content_disposition(filename)),
]
return request.make_response(data, headers=xlshttpheaders)
def datas_export_ventes(self, format, date_start, date_end,
nom_outil_compta):
if nom_outil_compta == 'ibiza':
columns = HEADER_DEFAULT
journal_value = 'VT'
elif nom_outil_compta == 'cegid':
columns = HEADER_DEFAULT_CEGID
journal_value = 'VEN'
else:
columns = HEADER_DEFAULT
journal_value = 'VT'
# requete
request.cr.execute("""
SELECT l.date,
%s AS journal,
a.code AS compte,
'' AS auxiliaire,
l.debit,
l.credit,
(CASE WHEN l.name = '/' OR l.name = ''
THEN p.name
ELSE CONCAT('Facture ',i.number,' - ',l.name) END) AS libelle,
i.number AS piece,
l.date_maturity AS echeance,
i.number AS ref_piece
FROM account_move_line AS l
LEFT JOIN account_invoice AS i ON l.invoice_id = i.id
LEFT JOIN account_account AS a ON l.account_id = a.id
LEFT JOIN res_partner AS p ON l.partner_id = p.id
WHERE l.journal_id = 1 AND l.date >= %s AND l.date <= %s
ORDER BY l.date, l.move_id, a.code DESC;
""", (journal_value, date_start, date_end))
lignes_export = request.cr.dictfetchall()
company_name = request.env['res.company'].search([('id', '=', 1)]).name
filename_ = (company_name.title().replace(' ', '')
+ 'JournalVentes_' + date_start.replace('-', '')
+ '_' + date_end.replace('-', ''))
if format == 'csv':
return self.export_csv(
lignes_export, columns, filename_, nom_outil_compta)
return self.export_xls(
lignes_export, columns, filename_, nom_outil_compta)
def datas_export_achats(self, format, date_start, date_end,
nom_outil_compta):
if nom_outil_compta == 'ibiza':
columns = HEADER_DEFAULT
journal_value = 'HA'
elif nom_outil_compta == 'cegid':
columns = HEADER_DEFAULT_CEGID
journal_value = 'ACH'
else:
columns = HEADER_DEFAULT
journal_value = 'HA'
request.cr.execute("""
SELECT l.date,
%s AS journal,
a.code AS compte,
'' AS auxiliaire,
l.debit,
l.credit,
(CASE WHEN l.name = '/' OR ''
THEN p.name
ELSE CONCAT('Facture ',i.number,' - ',l.name) END) AS libelle,
i.number AS piece,
i.date_due AS echeance,
i.reference AS ref_piece
FROM account_move_line AS l
LEFT JOIN account_invoice AS i ON l.invoice_id = i.id
LEFT JOIN account_account AS a ON l.account_id = a.id
LEFT JOIN res_partner AS p ON l.partner_id = p.id
WHERE l.journal_id = 2 AND i.type='in_invoice'
AND l.date >= %s AND l.date <= %s
ORDER BY l.date, l.move_id, a.code DESC;
""", (journal_value, date_start, date_end))
lignes_export = request.cr.dictfetchall()
company_name = request.env['res.company'].search([('id', '=', 1)]).name
filename_ = (company_name.title().replace(' ', '')
+ 'JournalAchats_' + date_start.replace('-', '')
+ '_' + date_end.replace('-', ''))
if format == 'csv':
return self.export_csv(
lignes_export, columns, filename_, nom_outil_compta)
return self.export_xls(
lignes_export, columns, filename_, nom_outil_compta)
@http.route('/web/export_journal/', type='http', auth="user")
@serialize_exception
def datas_export(self, format, journal, nom_outil_compta,
date_start, date_end, **kw):
if journal == 'ventes':
return self.datas_export_ventes(format, date_start, date_end,
nom_outil_compta)
elif journal == 'achats':
return self.datas_export_achats(format, date_start, date_end,
nom_outil_compta)