wizard/account_export_csv.py

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