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                 and journal_id in %(journal_ids)s
 172                 group by ac.id,ac.code,ac.name
 173                 order by ac.code
 174                    """,
 175                    {'fiscalyear_id': fiscalyear_id,
 176                        'period_ids': tuple(period_range_ids),
 177                        'journal_ids': tuple(journal_ids)}
 178                    )
 179         res = cr.fetchall()
 180
 181         rows = []
 182         for line in res:
 183             rows.append(list(line))
 184         return rows
 185
 186     def action_manual_export_analytic(self, cr, uid, ids, context=None):
 187         this = self.browse(cr, uid, ids)[0]
 188         rows = self.get_data(cr, uid, ids, "analytic", context)
 189         file_data = StringIO.StringIO()
 190         try:
 191             writer = AccountUnicodeWriter(file_data)
 192             writer.writerows(rows)
 193             file_value = file_data.getvalue()
 194             self.write(cr, uid, ids,
 195                        {'data': base64.encodestring(file_value)},
 196                        context=context)
 197         finally:
 198             file_data.close()
 199         return {
 200             'type': 'ir.actions.act_window',
 201             'res_model': 'account.csv.export',
 202             'view_mode': 'form',
 203             'view_type': 'form',
 204             'res_id': this.id,
 205             'views': [(False, 'form')],
 206             'target': 'new',
 207         }
 208
 209     def _get_header_analytic(self, cr, uid, ids, context=None):
 210         return [_(u'ANALYTIC CODE'),
 211                 _(u'ANALYTIC NAME'),
 212                 _(u'CODE'),
 213                 _(u'ACCOUNT NAME'),
 214                 _(u'DEBIT'),
 215                 _(u'CREDIT'),
 216                 _(u'BALANCE'),
 217                 ]
 218
 219     def _get_rows_analytic(self, cr, uid, ids,
 220                            fiscalyear_id,
 221                            period_range_ids,
 222                            journal_ids,
 223                            account_ids,
 224                            context=None):
 225         """
 226         Return list to generate rows of the CSV file
 227         """
 228         cr.execute("""  select aac.code as analytic_code,
 229                         aac.name as analytic_name,
 230                         ac.code,ac.name,
 231                         sum(debit) as sum_debit,
 232                         sum(credit) as sum_credit,
 233                         sum(debit) - sum(credit) as balance
 234                         from account_move_line
 235                         left outer join account_analytic_account as aac
 236                         on (account_move_line.analytic_account_id = aac.id)
 237                         inner join account_account as ac
 238                         on account_move_line.account_id = ac.id
 239                         and account_move_line.period_id in %(period_ids)s
 240                         group by aac.id,aac.code,aac.name,ac.id,ac.code,ac.name
 241                         order by aac.code
 242                    """,
 243                    {'fiscalyear_id': fiscalyear_id,
 244                        'period_ids': tuple(period_range_ids)}
 245                    )
 246         res = cr.fetchall()
 247
 248         rows = []
 249         for line in res:
 250             rows.append(list(line))
 251         return rows
 252
 253     def action_manual_export_journal_entries(self, cr, uid, ids, context=None):
 254         """
 255         Here we use TemporaryFile to avoid full filling the OpenERP worker
 256         Memory
 257         We also write the data to the wizard with SQL query as write seams
 258         to use too much memory as well.
 259
 260         Those improvements permitted to improve the export from a 100k line to
 261         200k lines
 262         with default `limit_memory_hard = 805306368` (768MB) with more lines,
 263         you might encounter a MemoryError when trying to download the file even
 264         if it has been generated.
 265
 266         To be able to export bigger volume of data, it is advised to set
 267         limit_memory_hard to 2097152000 (2 GB) to generate the file and let
 268         OpenERP load it in the wizard when trying to download it.
 269
 270         Tested with up to a generation of 700k entry lines
 271         """
 272         this = self.browse(cr, uid, ids)[0]
 273         rows = self.get_data(cr, uid, ids, "journal_entries", context)
 274         with tempfile.TemporaryFile() as file_data:
 275             writer = AccountUnicodeWriter(file_data)
 276             writer.writerows(rows)
 277             with tempfile.TemporaryFile() as base64_data:
 278                 file_data.seek(0)
 279                 base64.encode(file_data, base64_data)
 280                 base64_data.seek(0)
 281                 cr.execute("""
 282                 UPDATE account_csv_export
 283                 SET data = %s
 284                 WHERE id = %s""", (base64_data.read(), ids[0]))
 285         return {
 286             'type': 'ir.actions.act_window',
 287             'res_model': 'account.csv.export',
 288             'view_mode': 'form',
 289             'view_type': 'form',
 290             'res_id': this.id,
 291             'views': [(False, 'form')],
 292             'target': 'new',
 293         }
 294
 295     def _get_header_journal_entries(self, cr, uid, ids, context=None):
 296         return [
 297             # Standard Sage export fields
 298             _(u'DATE'),
 299             _(u'JOURNAL CODE'),
 300             _(u'ACCOUNT CODE'),
 301             _(u'PARTNER NAME'),
 302             _(u'REF'),
 303             _(u'DESCRIPTION'),
 304             _(u'DEBIT'),
 305             _(u'CREDIT'),
 306             _(u'FULL RECONCILE'),
 307             _(u'PARTIAL RECONCILE'),
 308             _(u'ANALYTIC ACCOUNT CODE'),
 309
 310             # Other fields
 311             _(u'ENTRY NUMBER'),
 312             _(u'ACCOUNT NAME'),
 313             _(u'BALANCE'),
 314             _(u'AMOUNT CURRENCY'),
 315             _(u'CURRENCY'),
 316             _(u'ANALYTIC ACCOUNT NAME'),
 317             _(u'JOURNAL'),
 318             _(u'MONTH'),
 319             _(u'FISCAL YEAR'),
 320             _(u'TAX CODE CODE'),
 321             _(u'TAX CODE NAME'),
 322             _(u'TAX AMOUNT'),
 323             _(u'BANK STATEMENT'),
 324         ]
 325
 326     def _get_rows_journal_entries(self, cr, uid, ids,
 327                                   fiscalyear_id,
 328                                   period_range_ids,
 329                                   journal_ids,
 330                                   account_ids,
 331                                   context=None):
 332         """
 333         Create a generator of rows of the CSV file
 334         """
 335         cr.execute("""
 336         SELECT
 337           account_move_line.date AS date,
 338           account_journal.name as journal,
 339           account_account.code AS account_code,
 340           res_partner.name AS partner_name,
 341           account_move_line.ref AS ref,
 342           account_move_line.name AS description,
 343           account_move_line.debit AS debit,
 344           account_move_line.credit AS credit,
 345           account_move_reconcile.name as full_reconcile,
 346           account_move_line.reconcile_partial_id AS partial_reconcile_id,
 347           account_analytic_account.code AS analytic_account_code,
 348           account_move.name AS entry_number,
 349           account_account.name AS account_name,
 350           account_move_line.debit - account_move_line.credit AS balance,
 351           account_move_line.amount_currency AS amount_currency,
 352           res_currency.name AS currency,
 353           account_analytic_account.name AS analytic_account_name,
 354           account_journal.name as journal,
 355           account_period.code AS month,
 356           account_fiscalyear.name as fiscal_year,
 357           account_tax_code.code AS aml_tax_code_code,
 358           account_tax_code.name AS aml_tax_code_name,
 359           account_move_line.tax_amount AS aml_tax_amount,
 360           account_bank_statement.name AS bank_statement
 361         FROM
 362           public.account_move_line
 363           JOIN account_account on
 364             (account_account.id=account_move_line.account_id)
 365           JOIN account_period on
 366             (account_period.id=account_move_line.period_id)
 367           JOIN account_fiscalyear on
 368             (account_fiscalyear.id=account_period.fiscalyear_id)
 369           JOIN account_journal on
 370             (account_journal.id = account_move_line.journal_id)
 371           LEFT JOIN res_currency on
 372             (res_currency.id=account_move_line.currency_id)
 373           LEFT JOIN account_move_reconcile on
 374             (account_move_reconcile.id = account_move_line.reconcile_id)
 375           LEFT JOIN res_partner on
 376             (res_partner.id=account_move_line.partner_id)
 377           LEFT JOIN account_move on
 378             (account_move.id=account_move_line.move_id)
 379           LEFT JOIN account_tax on
 380             (account_tax.id=account_move_line.account_tax_id)
 381           LEFT JOIN account_tax_code on
 382             (account_tax_code.id=account_move_line.tax_code_id)
 383           LEFT JOIN account_analytic_account on
 384             (account_analytic_account.id=account_move_line.analytic_account_id)
 385           LEFT JOIN account_bank_statement on
 386             (account_bank_statement.id=account_move_line.statement_id)
 387         WHERE account_period.id IN %(period_ids)s
 388         AND account_journal.id IN %(journal_ids)s
 389         AND account_account.id IN %(account_ids)s
 390         ORDER BY account_move_line.date
 391         """,
 392                    {'period_ids': tuple(period_range_ids),
 393                     'journal_ids': tuple(journal_ids),
 394                     'account_ids': tuple(account_ids)}
 395                    )
 396         while 1:
 397             # http://initd.org/psycopg/docs/cursor.html#cursor.fetchmany
 398             # Set cursor.arraysize to minimize network round trips
 399             cr.arraysize = 100
 400             rows = cr.fetchmany()
 401             if not rows:
 402                 break
 403             for row in rows:
 404                 yield row
 405
 406     def get_data(self, cr, uid, ids, result_type, context=None):
 407         get_header_func = getattr(
 408             self, ("_get_header_%s" % (result_type)), None)
 409         get_rows_func = getattr(self, ("_get_rows_%s" % (result_type)), None)
 410         form = self.browse(cr, uid, ids[0], context=context)
 411         fiscalyear_id = form.fiscalyear_id.id
 412         if form.periods:
 413             period_range_ids = [x.id for x in form.periods]
 414         else:
 415             # If not period selected , we take all periods
 416             p_obj = self.pool.get("account.period")
 417             period_range_ids = p_obj.search(
 418                 cr, uid, [('fiscalyear_id', '=', fiscalyear_id)],
 419                 context=context)
 420         journal_ids = None
 421         if form.journal_ids:
 422             journal_ids = [x.id for x in form.journal_ids]
 423         else:
 424             j_obj = self.pool.get("account.journal")
 425             journal_ids = j_obj.search(cr, uid, [], context=context)
 426         account_ids = None
 427         if form.account_ids:
 428             account_ids = [x.id for x in form.account_ids]
 429         else:
 430             aa_obj = self.pool.get("account.account")
 431             account_ids = aa_obj.search(cr, uid, [], context=context)
 432         rows = itertools.chain((get_header_func(cr, uid, ids,
 433                                                 context=context),),
 434                                get_rows_func(cr, uid, ids,
 435                                              fiscalyear_id,
 436                                              period_range_ids,
 437                                              journal_ids,
 438                                              account_ids,
 439                                              context=context)
 440                                )
 441         return rows