wizard/account_export_csv.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    Author Joel Grand-Guillaume and Vincent Renaville Copyright 2013
   5 #    Camptocamp SA
   6 #    CSV data formating inspired from
   7 # http://docs.python.org/2.7/library/csv.html?highlight=csv#examples
   8 #
   9 #    This program is free software: you can redistribute it and/or modify
  10 #    it under the terms of the GNU Affero General Public License as
  11 #    published by the Free Software Foundation, either version 3 of the
  12 #    License, or (at your option) any later version.
  13 #
  14 #    This program is distributed in the hope that it will be useful,
  15 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  16 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  17 #    GNU Affero General Public License for more details.
  18 #
  19 #    You should have received a copy of the GNU Affero General Public License
  20 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  21 #
  22 ##############################################################################
  23
  24 import itertools
  25 import tempfile
  26 import StringIO
  27 import cStringIO
  28 import base64
  29
  30 import csv
  31 import codecs
  32
  33 from openerp.osv import orm, fields
  34 from openerp.tools.translate import _
  35
  36
  37 class AccountUnicodeWriter(object):
  38
  39     """
  40     A CSV writer which will write rows to CSV file "f",
  41     which is encoded in the given encoding.
  42     """
  43
  44     def __init__(self, f, dialect=csv.excel, encoding="utf-8", **kwds):
  45         # Redirect output to a queue
  46         self.queue = cStringIO.StringIO()
  47         # created a writer with Excel formating settings
  48
  49         self.writer = csv.writer(self.queue, dialect=dialect,
  50                                  quoting=csv.QUOTE_ALL, **kwds)
  51         self.stream = f
  52         self.encoder = codecs.getincrementalencoder(encoding)()
  53
  54     def writerow(self, row):
  55         # we ensure that we do not try to encode none or bool
  56         row = (x or u'' for x in row)
  57
  58         encoded_row = [
  59             c.encode("utf-8") if isinstance(c, unicode) else c for c in row]
  60
  61         self.writer.writerow(encoded_row)
  62         # Fetch UTF-8 output from the queue ...
  63         data = self.queue.getvalue()
  64         data = data.decode("utf-8")
  65         # ... and reencode it into the target encoding
  66         data = self.encoder.encode(data)
  67         # write to the target stream
  68         self.stream.write(data)
  69         # empty queue
  70         self.queue.truncate(0)
  71
  72     def writerows(self, rows):
  73         for row in rows:
  74             self.writerow(row)
  75
  76
  77 class AccountCSVExport(orm.TransientModel):
  78     _name = 'account.csv.export'
  79     _description = 'Export Accounting'
  80
  81     _columns = {
  82         'data': fields.binary('CSV', readonly=True),
  83         'company_id': fields.many2one('res.company', 'Company',
  84                                       invisible=True),
  85         'fiscalyear_id': fields.many2one('account.fiscalyear', 'Fiscalyear',
  86                                          required=True),
  87         'periods': fields.many2many(
  88             'account.period', 'rel_wizard_period',
  89             'wizard_id', 'period_id', 'Periods',
  90             help='All periods in the fiscal year if empty'),
  91         'journal_ids': fields.many2many(
  92             'account.journal',
  93             'rel_wizard_journal',
  94             'wizard_id',
  95             'journal_id',
  96             'Journals',
  97             help='If empty, use all journals, only used for journal entries'),
  98         'account_ids': fields.many2many(
  99             'account.account',
 100             'rel_wizard_account',
 101             'wizard_id',
 102             'account_id',
 103             'Accounts',
 104             help='If empty, use all accounts, only used for journal entries'),
 105         'export_filename': fields.char('Export CSV Filename', size=128),
 106     }
 107
 108     def _get_company_default(self, cr, uid, context=None):
 109         comp_obj = self.pool['res.company']
 110         return comp_obj._company_default_get(cr, uid, 'account.fiscalyear',
 111                                              context=context)
 112
 113     def _get_fiscalyear_default(self, cr, uid, context=None):
 114         fiscalyear_obj = self.pool['account.fiscalyear']
 115         context['company_id'] = self._get_company_default(cr, uid, context)
 116         return fiscalyear_obj.find(cr, uid, dt=None, exception=True,
 117                                    context=context)
 118
 119     _defaults = {'company_id': _get_company_default,
 120                  'fiscalyear_id': _get_fiscalyear_default,
 121                  'export_filename': 'account_export.csv'}
 122
 123     def action_manual_export_account(self, cr, uid, ids, context=None):
 124         this = self.browse(cr, uid, ids)[0]
 125         rows = self.get_data(cr, uid, ids, "account", context)
 126         file_data = StringIO.StringIO()
 127         try:
 128             writer = AccountUnicodeWriter(file_data)
 129             writer.writerows(rows)
 130             file_value = file_data.getvalue()
 131             self.write(cr, uid, ids,
 132                        {'data': base64.encodestring(file_value)},
 133                        context=context)
 134         finally:
 135             file_data.close()
 136         return {
 137             'type': 'ir.actions.act_window',
 138             'res_model': 'account.csv.export',
 139             'view_mode': 'form',
 140             'view_type': 'form',
 141             'res_id': this.id,
 142             'views': [(False, 'form')],
 143             'target': 'new',
 144         }
 145
 146     def _get_header_account(self, cr, uid, ids, context=None):
 147         return [_(u'CODE'),
 148                 _(u'NAME'),
 149                 _(u'DEBIT'),
 150                 _(u'CREDIT'),
 151                 _(u'BALANCE'),
 152                 ]
 153
 154     def _get_rows_account(self, cr, uid, ids,
 155                           fiscalyear_id,
 156                           period_range_ids,
 157                           journal_ids,
 158                           account_ids,
 159                           context=None):
 160         """
 161         Return list to generate rows of the CSV file
 162         """
 163         cr.execute("""
 164                 select ac.code,ac.name,
 165                 sum(debit) as sum_debit,
 166                 sum(credit) as sum_credit,
 167                 sum(debit) - sum(credit) as balance
 168                 from account_move_line as aml,account_account as ac
 169                 where aml.account_id = ac.id
 170                 and period_id in %(period_ids)s
 171                 group by ac.id,ac.code,ac.name
 172                 order by ac.code
 173                    """,
 174                    {'fiscalyear_id': fiscalyear_id,
 175                        'period_ids': tuple(period_range_ids)}
 176                    )
 177         res = cr.fetchall()
 178
 179         rows = []
 180         for line in res:
 181             rows.append(list(line))
 182         return rows
 183
 184     def action_manual_export_analytic(self, cr, uid, ids, context=None):
 185         this = self.browse(cr, uid, ids)[0]
 186         rows = self.get_data(cr, uid, ids, "analytic", context)
 187         file_data = StringIO.StringIO()
 188         try:
 189             writer = AccountUnicodeWriter(file_data)
 190             writer.writerows(rows)
 191             file_value = file_data.getvalue()
 192             self.write(cr, uid, ids,
 193                        {'data': base64.encodestring(file_value)},
 194                        context=context)
 195         finally:
 196             file_data.close()
 197         return {
 198             'type': 'ir.actions.act_window',
 199             'res_model': 'account.csv.export',
 200             'view_mode': 'form',
 201             'view_type': 'form',
 202             'res_id': this.id,
 203             'views': [(False, 'form')],
 204             'target': 'new',
 205         }
 206
 207     def _get_header_analytic(self, cr, uid, ids, context=None):
 208         return [_(u'ANALYTIC CODE'),
 209                 _(u'ANALYTIC NAME'),
 210                 _(u'CODE'),
 211                 _(u'ACCOUNT NAME'),
 212                 _(u'DEBIT'),
 213                 _(u'CREDIT'),
 214                 _(u'BALANCE'),
 215                 ]
 216
 217     def _get_rows_analytic(self, cr, uid, ids,
 218                            fiscalyear_id,
 219                            period_range_ids,
 220                            journal_ids,
 221                            account_ids,
 222                            context=None):
 223         """
 224         Return list to generate rows of the CSV file
 225         """
 226         cr.execute("""  select aac.code as analytic_code,
 227                         aac.name as analytic_name,
 228                         ac.code,ac.name,
 229                         sum(debit) as sum_debit,
 230                         sum(credit) as sum_credit,
 231                         sum(debit) - sum(credit) as balance
 232                         from account_move_line
 233                         left outer join account_analytic_account as aac
 234                         on (account_move_line.analytic_account_id = aac.id)
 235                         inner join account_account as ac
 236                         on account_move_line.account_id = ac.id
 237                         and account_move_line.period_id in %(period_ids)s
 238                         group by aac.id,aac.code,aac.name,ac.id,ac.code,ac.name
 239                         order by aac.code
 240                    """,
 241                    {'fiscalyear_id': fiscalyear_id,
 242                        'period_ids': tuple(period_range_ids)}
 243                    )
 244         res = cr.fetchall()
 245
 246         rows = []
 247         for line in res:
 248             rows.append(list(line))
 249         return rows
 250
 251     def action_manual_export_journal_entries(self, cr, uid, ids, context=None):
 252         """
 253         Here we use TemporaryFile to avoid full filling the OpenERP worker
 254         Memory
 255         We also write the data to the wizard with SQL query as write seams
 256         to use too much memory as well.
 257
 258         Those improvements permitted to improve the export from a 100k line to
 259         200k lines
 260         with default `limit_memory_hard = 805306368` (768MB) with more lines,
 261         you might encounter a MemoryError when trying to download the file even
 262         if it has been generated.
 263
 264         To be able to export bigger volume of data, it is advised to set
 265         limit_memory_hard to 2097152000 (2 GB) to generate the file and let
 266         OpenERP load it in the wizard when trying to download it.
 267
 268         Tested with up to a generation of 700k entry lines
 269         """
 270         this = self.browse(cr, uid, ids)[0]
 271         rows = self.get_data(cr, uid, ids, "journal_entries", context)
 272         with tempfile.TemporaryFile() as file_data:
 273             writer = AccountUnicodeWriter(file_data)
 274             writer.writerows(rows)
 275             with tempfile.TemporaryFile() as base64_data:
 276                 file_data.seek(0)
 277                 base64.encode(file_data, base64_data)
 278                 base64_data.seek(0)
 279                 cr.execute("""
 280                 UPDATE account_csv_export
 281                 SET data = %s
 282                 WHERE id = %s""", (base64_data.read(), ids[0]))
 283         return {
 284             'type': 'ir.actions.act_window',
 285             'res_model': 'account.csv.export',
 286             'view_mode': 'form',
 287             'view_type': 'form',
 288             'res_id': this.id,
 289             'views': [(False, 'form')],
 290             'target': 'new',
 291         }
 292
 293     def _get_header_journal_entries(self, cr, uid, ids, context=None):
 294         return [
 295             # Standard Sage export fields
 296             _(u'DATE'),
 297             _(u'JOURNAL CODE'),
 298             _(u'ACCOUNT CODE'),
 299             _(u'PARTNER NAME'),
 300             _(u'REF'),
 301             _(u'DESCRIPTION'),
 302             _(u'DEBIT'),
 303             _(u'CREDIT'),
 304             _(u'FULL RECONCILE'),
 305             _(u'PARTIAL RECONCILE'),
 306             _(u'ANALYTIC ACCOUNT CODE'),
 307
 308             # Other fields
 309             _(u'ENTRY NUMBER'),
 310             _(u'ACCOUNT NAME'),
 311             _(u'BALANCE'),
 312             _(u'AMOUNT CURRENCY'),
 313             _(u'CURRENCY'),
 314             _(u'ANALYTIC ACCOUNT NAME'),
 315             _(u'JOURNAL'),
 316             _(u'MONTH'),
 317             _(u'FISCAL YEAR'),
 318             _(u'TAX CODE CODE'),
 319             _(u'TAX CODE NAME'),
 320             _(u'TAX AMOUNT'),
 321             _(u'BANK STATEMENT'),
 322         ]
 323
 324     def _get_rows_journal_entries(self, cr, uid, ids,
 325                                   fiscalyear_id,
 326                                   period_range_ids,
 327                                   journal_ids,
 328                                   account_ids,
 329                                   context=None):
 330         """
 331         Create a generator of rows of the CSV file
 332         """
 333         cr.execute("""
 334         SELECT
 335           account_move_line.date AS date,
 336           account_journal.name as journal,
 337           account_account.code AS account_code,
 338           res_partner.name AS partner_name,
 339           account_move_line.ref AS ref,
 340           account_move_line.name AS description,
 341           account_move_line.debit AS debit,
 342           account_move_line.credit AS credit,
 343           account_move_reconcile.name as full_reconcile,
 344           account_move_line.reconcile_partial_id AS partial_reconcile_id,
 345           account_analytic_account.code AS analytic_account_code,
 346           account_move.name AS entry_number,
 347           account_account.name AS account_name,
 348           account_move_line.debit - account_move_line.credit AS balance,
 349           account_move_line.amount_currency AS amount_currency,
 350           res_currency.name AS currency,
 351           account_analytic_account.name AS analytic_account_name,
 352           account_journal.name as journal,
 353           account_period.code AS month,
 354           account_fiscalyear.name as fiscal_year,
 355           account_tax_code.code AS aml_tax_code_code,
 356           account_tax_code.name AS aml_tax_code_name,
 357           account_move_line.tax_amount AS aml_tax_amount,
 358           account_bank_statement.name AS bank_statement
 359         FROM
 360           public.account_move_line
 361           JOIN account_account on
 362             (account_account.id=account_move_line.account_id)
 363           JOIN account_period on
 364             (account_period.id=account_move_line.period_id)
 365           JOIN account_fiscalyear on
 366             (account_fiscalyear.id=account_period.fiscalyear_id)
 367           JOIN account_journal on
 368             (account_journal.id = account_move_line.journal_id)
 369           LEFT JOIN res_currency on
 370             (res_currency.id=account_move_line.currency_id)
 371           LEFT JOIN account_move_reconcile on
 372             (account_move_reconcile.id = account_move_line.reconcile_id)
 373           LEFT JOIN res_partner on
 374             (res_partner.id=account_move_line.partner_id)
 375           LEFT JOIN account_move on
 376             (account_move.id=account_move_line.move_id)
 377           LEFT JOIN account_tax on
 378             (account_tax.id=account_move_line.account_tax_id)
 379           LEFT JOIN account_tax_code on
 380             (account_tax_code.id=account_move_line.tax_code_id)
 381           LEFT JOIN account_analytic_account on
 382             (account_analytic_account.id=account_move_line.analytic_account_id)
 383           LEFT JOIN account_bank_statement on
 384             (account_bank_statement.id=account_move_line.statement_id)
 385         WHERE account_period.id IN %(period_ids)s
 386         AND account_journal.id IN %(journal_ids)s
 387         AND account_account.id IN %(account_ids)s
 388         ORDER BY account_move_line.date
 389         """,
 390                    {'period_ids': tuple(period_range_ids),
 391                     'journal_ids': tuple(journal_ids),
 392                     'account_ids': tuple(account_ids)}
 393                    )
 394         while 1:
 395             # http://initd.org/psycopg/docs/cursor.html#cursor.fetchmany
 396             # Set cursor.arraysize to minimize network round trips
 397             cr.arraysize = 100
 398             rows = cr.fetchmany()
 399             if not rows:
 400                 break
 401             for row in rows:
 402                 yield row
 403
 404     def get_data(self, cr, uid, ids, result_type, context=None):
 405         get_header_func = getattr(
 406             self, ("_get_header_%s" % (result_type)), None)
 407         get_rows_func = getattr(self, ("_get_rows_%s" % (result_type)), None)
 408         form = self.browse(cr, uid, ids[0], context=context)
 409         fiscalyear_id = form.fiscalyear_id.id
 410         if form.periods:
 411             period_range_ids = [x.id for x in form.periods]
 412         else:
 413             # If not period selected , we take all periods
 414             p_obj = self.pool.get("account.period")
 415             period_range_ids = p_obj.search(
 416                 cr, uid, [('fiscalyear_id', '=', fiscalyear_id)],
 417                 context=context)
 418         journal_ids = None
 419         if form.journal_ids:
 420             journal_ids = [x.id for x in form.journal_ids]
 421         else:
 422             j_obj = self.pool.get("account.journal")
 423             journal_ids = j_obj.search(cr, uid, [], context=context)
 424         account_ids = None
 425         if form.account_ids:
 426             account_ids = [x.id for x in form.account_ids]
 427         else:
 428             aa_obj = self.pool.get("account.account")
 429             account_ids = aa_obj.search(cr, uid, [], context=context)
 430         rows = itertools.chain((get_header_func(cr, uid, ids,
 431                                                 context=context),),
 432                                get_rows_func(cr, uid, ids,
 433                                              fiscalyear_id,
 434                                              period_range_ids,
 435                                              journal_ids,
 436                                              account_ids,
 437                                              context=context)
 438                                )
 439         return rows