xls.py

# 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)