1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # Author Joel Grand-Guillaume and Vincent Renaville Copyright 2013
6 # CSV data formating inspired from
7 # http://docs.python.org/2.7/library/csv.html?highlight=csv#examples
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.
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.
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/>.
22 ##############################################################################
33 from openerp
.osv
import orm
, fields
34 from openerp
.tools
.translate
import _
37 class AccountUnicodeWriter(object):
40 A CSV writer which will write rows to CSV file "f",
41 which is encoded in the given encoding.
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
49 self
.writer
= csv
.writer(self
.queue
, dialect
=dialect
,
50 quoting
=csv
.QUOTE_ALL
, **kwds
)
52 self
.encoder
= codecs
.getincrementalencoder(encoding
)()
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
)
59 c
.encode("utf-8") if isinstance(c
, unicode) else c
for c
in row
]
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
)
70 self
.queue
.truncate(0)
72 def writerows(self
, rows
):
77 class AccountCSVExport(orm
.TransientModel
):
78 _name
= 'account.csv.export'
79 _description
= 'Export Accounting'
82 'data': fields
.binary('CSV', readonly
=True),
83 'company_id': fields
.many2one('res.company', 'Company',
85 'fiscalyear_id': fields
.many2one('account.fiscalyear', 'Fiscalyear',
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(
97 help='If empty, use all journals, only used for journal entries'),
98 'account_ids': fields
.many2many(
100 'rel_wizard_account',
104 help='If empty, use all accounts, only used for journal entries'),
105 'export_filename': fields
.char('Export CSV Filename', size
=128),
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',
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,
119 _defaults
= {'company_id': _get_company_default
,
120 'fiscalyear_id': _get_fiscalyear_default
,
121 'export_filename': 'account_export.csv'}
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()
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
)},
137 'type': 'ir.actions.act_window',
138 'res_model': 'account.csv.export',
142 'views': [(False, 'form')],
146 def _get_header_account(self
, cr
, uid
, ids
, context
=None):
154 def _get_rows_account(self
, cr
, uid
, ids
,
161 Return list to generate rows of the CSV file
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
174 {'fiscalyear_id': fiscalyear_id
,
175 'period_ids': tuple(period_range_ids
)}
181 rows
.append(list(line
))
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()
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
)},
198 'type': 'ir.actions.act_window',
199 'res_model': 'account.csv.export',
203 'views': [(False, 'form')],
207 def _get_header_analytic(self
, cr
, uid
, ids
, context
=None):
208 return [_(u
'ANALYTIC CODE'),
217 def _get_rows_analytic(self
, cr
, uid
, ids
,
224 Return list to generate rows of the CSV file
226 cr
.execute(""" select aac.code as analytic_code,
227 aac.name as analytic_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
241 {'fiscalyear_id': fiscalyear_id
,
242 'period_ids': tuple(period_range_ids
)}
248 rows
.append(list(line
))
251 def action_manual_export_journal_entries(self
, cr
, uid
, ids
, context
=None):
253 Here we use TemporaryFile to avoid full filling the OpenERP worker
255 We also write the data to the wizard with SQL query as write seams
256 to use too much memory as well.
258 Those improvements permitted to improve the export from a 100k line to
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.
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.
268 Tested with up to a generation of 700k entry lines
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
:
277 base64
.encode(file_data
, base64_data
)
280 UPDATE account_csv_export
282 WHERE id = %s""", (base64_data
.read(), ids
[0]))
284 'type': 'ir.actions.act_window',
285 'res_model': 'account.csv.export',
289 'views': [(False, 'form')],
293 def _get_header_journal_entries(self
, cr
, uid
, ids
, context
=None):
295 # Standard Sage export fields
304 _(u
'FULL RECONCILE'),
305 _(u
'PARTIAL RECONCILE'),
306 _(u
'ANALYTIC ACCOUNT CODE'),
312 _(u
'AMOUNT CURRENCY'),
314 _(u
'ANALYTIC ACCOUNT NAME'),
321 _(u
'BANK STATEMENT'),
324 def _get_rows_journal_entries(self
, cr
, uid
, ids
,
331 Create a generator of rows of the CSV file
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
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
390 {'period_ids': tuple(period_range_ids
),
391 'journal_ids': tuple(journal_ids
),
392 'account_ids': tuple(account_ids
)}
395 # http://initd.org/psycopg/docs/cursor.html#cursor.fetchmany
396 # Set cursor.arraysize to minimize network round trips
398 rows
= cr
.fetchmany()
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
411 period_range_ids
= [x
.id for x
in form
.periods
]
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
)],
420 journal_ids
= [x
.id for x
in form
.journal_ids
]
422 j_obj
= self
.pool
.get("account.journal")
423 journal_ids
= j_obj
.search(cr
, uid
, [], context
=context
)
426 account_ids
= [x
.id for x
in form
.account_ids
]
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
,
432 get_rows_func(cr
, uid
, ids
,