c0cb09c255952d2844782dd12e2b902f78f6c9ca
1 # -*- coding: utf-8 -*-
2 ##############################################################################
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
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.
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.
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/>.
20 ##############################################################################
32 from openerp
.osv
import orm
, fields
33 from openerp
.tools
.translate
import _
36 class AccountUnicodeWriter(object):
38 A CSV writer which will write rows to CSV file "f",
39 which is encoded in the given encoding.
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
)
48 self
.encoder
= codecs
.getincrementalencoder(encoding
)()
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
)
54 encoded_row
= [c
.encode("utf-8") if isinstance(c
, unicode) else c
for c
in row
]
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
)
65 self
.queue
.truncate(0)
67 def writerows(self
, rows
):
71 class AccountCSVExport(orm
.TransientModel
):
72 _name
= 'account.csv.export'
73 _description
= 'Export Accounting'
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),
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
)
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
)
94 _defaults
= {'company_id': _get_company_default
,
95 'fiscalyear_id' : _get_fiscalyear_default
,
96 'export_filename' : 'account_export.csv'}
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()
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
)},
112 'type': 'ir.actions.act_window',
113 'res_model': 'account.csv.export',
117 'views': [(False, 'form')],
122 def _get_header_account(self
, cr
, uid
, ids
, context
=None):
130 def _get_rows_account(self
, cr
, uid
, ids
,
136 Return list to generate rows of the CSV file
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
147 {'fiscalyear_id': fiscalyear_id
, 'period_ids':tuple(period_range_ids
)}
153 rows
.append(list(line
))
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()
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
)},
170 'type': 'ir.actions.act_window',
171 'res_model': 'account.csv.export',
175 'views': [(False, 'form')],
179 def _get_header_analytic(self
, cr
, uid
, ids
, context
=None):
180 return [_(u
'ANALYTIC CODE'),
189 def _get_rows_analytic(self
, cr
, uid
, ids
,
195 Return list to generate rows of the CSV file
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
208 {'fiscalyear_id': fiscalyear_id
, 'period_ids':tuple(period_range_ids
)}
214 rows
.append(list(line
))
218 def action_manual_export_journal_entries(self
, cr
, uid
, ids
, context
=None):
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.
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.
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.
233 Tested with up to a generation of 700k entry lines
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
:
242 base64
.encode(file_data
, base64_data
)
244 cr
.execute("""UPDATE account_csv_export SET data = %s WHERE id = %s""", (base64_data
.read(), ids
[0]) )
246 'type': 'ir.actions.act_window',
247 'res_model': 'account.csv.export',
251 'views': [(False, 'form')],
256 def _get_header_journal_entries(self
, cr
, uid
, ids
, context
=None):
259 # Standard Sage export fields
268 _(u
'FULL RECONCILE'),
269 _(u
'PARTIAL RECONCILE'),
270 _(u
'ANALYTIC ACCOUNT CODE'),
276 _(u
'AMOUNT CURRENCY'),
278 _(u
'ANALYTIC ACCOUNT NAME'),
288 def _get_rows_journal_entries(self
, cr
, uid
, ids
,
294 Create a generator of rows of the CSV file
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,
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
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
339 {'period_ids': tuple(period_range_ids
), 'journal_ids': tuple(journal_ids
)}
342 # http://initd.org/psycopg/docs/cursor.html#cursor.fetchmany
343 # Set cursor.arraysize to minimize network round trips
345 rows
= cr
.fetchmany()
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')
358 period_range_ids
= [x
.id for x
in form
.periods
]
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
)
365 journal_ids
= [x
.id for x
in form
.journal_ids
]
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
,