# -*- coding: utf-8 -*-
##############################################################################
#
-# Author Joel Grand-Guillaume and Vincent Renaville Copyright 2013 Camptocamp SA
-# CSV data formating inspired from http://docs.python.org/2.7/library/csv.html?highlight=csv#examples
+# Author Joel Grand-Guillaume and Vincent Renaville Copyright 2013
+# Camptocamp SA
+# CSV data formating inspired from
+# http://docs.python.org/2.7/library/csv.html?highlight=csv#examples
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as
##############################################################################
import itertools
-import time
import tempfile
import StringIO
import cStringIO
class AccountUnicodeWriter(object):
+
"""
A CSV writer which will write rows to CSV file "f",
which is encoded in the given encoding.
# Redirect output to a queue
self.queue = cStringIO.StringIO()
# created a writer with Excel formating settings
- self.writer = csv.writer(self.queue, dialect=dialect, **kwds)
+
+ self.writer = csv.writer(self.queue, dialect=dialect,
+ quoting=csv.QUOTE_ALL, **kwds)
self.stream = f
self.encoder = codecs.getincrementalencoder(encoding)()
def writerow(self, row):
- #we ensure that we do not try to encode none or bool
+ # we ensure that we do not try to encode none or bool
row = (x or u'' for x in row)
- encoded_row = [c.encode("utf-8") if isinstance(c, unicode) else c for c in row]
+ encoded_row = [
+ c.encode("utf-8") if isinstance(c, unicode) else c for c in row]
self.writer.writerow(encoded_row)
# Fetch UTF-8 output from the queue ...
for row in rows:
self.writerow(row)
+
class AccountCSVExport(orm.TransientModel):
_name = 'account.csv.export'
_description = 'Export Accounting'
_columns = {
- 'data': fields.binary('CSV',readonly=True),
- 'company_id': fields.many2one('res.company', 'Company', invisible=True),
- 'fiscalyear_id': fields.many2one('account.fiscalyear', 'Fiscalyear', required=True),
- 'periods': fields.many2many('account.period','rel_wizard_period','wizard_id','period_id','Periods',help='All periods in the fiscal year if empty'),
- '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'),
- 'fiscalyear_id': fields.many2one('account.fiscalyear', 'Fiscalyear', required=True),
+ 'data': fields.binary('CSV', readonly=True),
+ 'company_id': fields.many2one('res.company', 'Company',
+ invisible=True),
+ 'fiscalyear_id': fields.many2one('account.fiscalyear', 'Fiscalyear',
+ required=True),
+ 'periods': fields.many2many(
+ 'account.period', 'rel_wizard_period',
+ 'wizard_id', 'period_id', 'Periods',
+ help='All periods in the fiscal year if empty'),
+ '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'),
+ 'account_ids': fields.many2many(
+ 'account.account',
+ 'rel_wizard_account',
+ 'wizard_id',
+ 'account_id',
+ 'Accounts',
+ help='If empty, use all accounts, only used for journal entries'),
'export_filename': fields.char('Export CSV Filename', size=128),
}
def _get_company_default(self, cr, uid, context=None):
comp_obj = self.pool['res.company']
- return comp_obj._company_default_get(cr, uid, 'account.fiscalyear', context=context)
+ return comp_obj._company_default_get(cr, uid, 'account.fiscalyear',
+ context=context)
def _get_fiscalyear_default(self, cr, uid, context=None):
fiscalyear_obj = self.pool['account.fiscalyear']
context['company_id'] = self._get_company_default(cr, uid, context)
- return fiscalyear_obj.find(cr,uid,dt=None,exception=True, context=context)
+ return fiscalyear_obj.find(cr, uid, dt=None, exception=True,
+ context=context)
_defaults = {'company_id': _get_company_default,
- 'fiscalyear_id' : _get_fiscalyear_default,
- 'export_filename' : 'account_export.csv'}
+ 'fiscalyear_id': _get_fiscalyear_default,
+ 'export_filename': 'account_export.csv'}
def action_manual_export_account(self, cr, uid, ids, context=None):
this = self.browse(cr, uid, ids)[0]
'target': 'new',
}
-
def _get_header_account(self, cr, uid, ids, context=None):
return [_(u'CODE'),
_(u'NAME'),
]
def _get_rows_account(self, cr, uid, ids,
- fiscalyear_id,
- period_range_ids,
- journal_ids,
- context=None):
+ fiscalyear_id,
+ period_range_ids,
+ journal_ids,
+ account_ids,
+ context=None):
"""
Return list to generate rows of the CSV file
"""
cr.execute("""
- select ac.code,ac.name,
- sum(debit) as sum_debit,sum(credit) as sum_credit,sum(debit) - sum(credit) as balance
- from account_move_line as aml,account_account as ac
- where aml.account_id = ac.id
- and period_id in %(period_ids)s
- group by ac.id,ac.code,ac.name
- order by ac.code
+ select ac.code,ac.name,
+ sum(debit) as sum_debit,
+ sum(credit) as sum_credit,
+ sum(debit) - sum(credit) as balance
+ from account_move_line as aml,account_account as ac
+ where aml.account_id = ac.id
+ and period_id in %(period_ids)s
+ group by ac.id,ac.code,ac.name
+ order by ac.code
""",
- {'fiscalyear_id': fiscalyear_id, 'period_ids':tuple(period_range_ids)}
- )
+ {'fiscalyear_id': fiscalyear_id,
+ 'period_ids': tuple(period_range_ids)}
+ )
res = cr.fetchall()
rows = []
def action_manual_export_analytic(self, cr, uid, ids, context=None):
this = self.browse(cr, uid, ids)[0]
- rows = self.get_data(cr, uid, ids,"analytic", context)
+ rows = self.get_data(cr, uid, ids, "analytic", context)
file_data = StringIO.StringIO()
try:
writer = AccountUnicodeWriter(file_data)
]
def _get_rows_analytic(self, cr, uid, ids,
- fiscalyear_id,
- period_range_ids,
- journal_ids,
- context=None):
+ fiscalyear_id,
+ period_range_ids,
+ journal_ids,
+ account_ids,
+ context=None):
"""
Return list to generate rows of the CSV file
"""
- cr.execute(""" select aac.code as analytic_code,aac.name as analytic_name,ac.code,ac.name,
- sum(debit) as sum_debit,sum(credit) as sum_credit,sum(debit) - sum(credit) as balance
- from account_move_line
+ cr.execute(""" select aac.code as analytic_code,
+ aac.name as analytic_name,
+ ac.code,ac.name,
+ sum(debit) as sum_debit,
+ sum(credit) as sum_credit,
+ sum(debit) - sum(credit) as balance
+ from account_move_line
left outer join account_analytic_account as aac
on (account_move_line.analytic_account_id = aac.id)
inner join account_account as ac
group by aac.id,aac.code,aac.name,ac.id,ac.code,ac.name
order by aac.code
""",
- {'fiscalyear_id': fiscalyear_id, 'period_ids':tuple(period_range_ids)}
- )
+ {'fiscalyear_id': fiscalyear_id,
+ 'period_ids': tuple(period_range_ids)}
+ )
res = cr.fetchall()
rows = []
rows.append(list(line))
return rows
-
def action_manual_export_journal_entries(self, cr, uid, ids, context=None):
"""
- Here we use TemporaryFile to avoid full filling the OpenERP worker Memory
- We also write the data to the wizard with SQL query as write seams to use
- too much memory as well.
+ Here we use TemporaryFile to avoid full filling the OpenERP worker
+ Memory
+ We also write the data to the wizard with SQL query as write seams
+ to use too much memory as well.
- Those improvements permitted to improve the export from a 100k line to 200k lines
+ Those improvements permitted to improve the export from a 100k line to
+ 200k lines
with default `limit_memory_hard = 805306368` (768MB) with more lines,
you might encounter a MemoryError when trying to download the file even
if it has been generated.
file_data.seek(0)
base64.encode(file_data, base64_data)
base64_data.seek(0)
- cr.execute("""UPDATE account_csv_export SET data = %s WHERE id = %s""", (base64_data.read(), ids[0]) )
+ cr.execute("""
+ UPDATE account_csv_export
+ SET data = %s
+ WHERE id = %s""", (base64_data.read(), ids[0]))
return {
'type': 'ir.actions.act_window',
'res_model': 'account.csv.export',
'target': 'new',
}
-
def _get_header_journal_entries(self, cr, uid, ids, context=None):
-
- return [
- # Standard Sage export fields
- _(u'DATE'),
- _(u'JOURNAL CODE'),
- _(u'ACCOUNT CODE'),
- _(u'PARTNER NAME'),
- _(u'REF'),
- _(u'DESCRIPTION'),
- _(u'DEBIT'),
- _(u'CREDIT'),
- _(u'FULL RECONCILE'),
- _(u'PARTIAL RECONCILE'),
- _(u'ANALYTIC ACCOUNT CODE'),
-
- # Other fields
- _(u'ENTRY NUMBER'),
- _(u'ACCOUNT NAME'),
- _(u'BALANCE'),
- _(u'AMOUNT CURRENCY'),
- _(u'CURRENCY'),
- _(u'ANALYTIC ACCOUNT NAME'),
- _(u'JOURNAL'),
- _(u'MONTH'),
- _(u'FISCAL YEAR'),
- _(u'TAX CODE CODE'),
- _(u'TAX CODE NAME'),
- _(u'TAX AMOUNT'),
- ]
-
+ return [
+ # Standard Sage export fields
+ _(u'DATE'),
+ _(u'JOURNAL CODE'),
+ _(u'ACCOUNT CODE'),
+ _(u'PARTNER NAME'),
+ _(u'REF'),
+ _(u'DESCRIPTION'),
+ _(u'DEBIT'),
+ _(u'CREDIT'),
+ _(u'FULL RECONCILE'),
+ _(u'PARTIAL RECONCILE'),
+ _(u'ANALYTIC ACCOUNT CODE'),
+
+ # Other fields
+ _(u'ENTRY NUMBER'),
+ _(u'ACCOUNT NAME'),
+ _(u'BALANCE'),
+ _(u'AMOUNT CURRENCY'),
+ _(u'CURRENCY'),
+ _(u'ANALYTIC ACCOUNT NAME'),
+ _(u'JOURNAL'),
+ _(u'MONTH'),
+ _(u'FISCAL YEAR'),
+ _(u'TAX CODE CODE'),
+ _(u'TAX CODE NAME'),
+ _(u'TAX AMOUNT'),
+ _(u'BANK STATEMENT'),
+ ]
def _get_rows_journal_entries(self, cr, uid, ids,
- fiscalyear_id,
- period_range_ids,
- journal_ids,
- context=None):
+ fiscalyear_id,
+ period_range_ids,
+ journal_ids,
+ account_ids,
+ context=None):
"""
Create a generator of rows of the CSV file
"""
account_move_reconcile.name as full_reconcile,
account_move_line.reconcile_partial_id AS partial_reconcile_id,
account_analytic_account.code AS analytic_account_code,
-
account_move.name AS entry_number,
account_account.name AS account_name,
account_move_line.debit - account_move_line.credit AS balance,
account_fiscalyear.name as fiscal_year,
account_tax_code.code AS aml_tax_code_code,
account_tax_code.name AS aml_tax_code_name,
- account_move_line.tax_amount AS aml_tax_amount
+ account_move_line.tax_amount AS aml_tax_amount,
+ account_bank_statement.name AS bank_statement
FROM
public.account_move_line
- JOIN account_account on (account_account.id=account_move_line.account_id)
- JOIN account_period on (account_period.id=account_move_line.period_id)
- JOIN account_fiscalyear on (account_fiscalyear.id=account_period.fiscalyear_id)
- JOIN account_journal on (account_journal.id = account_move_line.journal_id)
- LEFT JOIN res_currency on (res_currency.id=account_move_line.currency_id)
- LEFT JOIN account_move_reconcile on (account_move_reconcile.id = account_move_line.reconcile_id)
- LEFT JOIN res_partner on (res_partner.id=account_move_line.partner_id)
- LEFT JOIN account_move on (account_move.id=account_move_line.move_id)
- LEFT JOIN account_tax on (account_tax.id=account_move_line.account_tax_id)
- LEFT JOIN account_tax_code on (account_tax_code.id=account_move_line.tax_code_id)
- LEFT JOIN account_analytic_account on (account_analytic_account.id=account_move_line.analytic_account_id)
+ JOIN account_account on
+ (account_account.id=account_move_line.account_id)
+ JOIN account_period on
+ (account_period.id=account_move_line.period_id)
+ JOIN account_fiscalyear on
+ (account_fiscalyear.id=account_period.fiscalyear_id)
+ JOIN account_journal on
+ (account_journal.id = account_move_line.journal_id)
+ LEFT JOIN res_currency on
+ (res_currency.id=account_move_line.currency_id)
+ LEFT JOIN account_move_reconcile on
+ (account_move_reconcile.id = account_move_line.reconcile_id)
+ LEFT JOIN res_partner on
+ (res_partner.id=account_move_line.partner_id)
+ LEFT JOIN account_move on
+ (account_move.id=account_move_line.move_id)
+ LEFT JOIN account_tax on
+ (account_tax.id=account_move_line.account_tax_id)
+ LEFT JOIN account_tax_code on
+ (account_tax_code.id=account_move_line.tax_code_id)
+ LEFT JOIN account_analytic_account on
+ (account_analytic_account.id=account_move_line.analytic_account_id)
+ LEFT JOIN account_bank_statement on
+ (account_bank_statement.id=account_move_line.statement_id)
WHERE account_period.id IN %(period_ids)s
AND account_journal.id IN %(journal_ids)s
+ AND account_account.id IN %(account_ids)s
ORDER BY account_move_line.date
""",
- {'period_ids': tuple(period_range_ids), 'journal_ids': tuple(journal_ids)}
- )
+ {'period_ids': tuple(period_range_ids),
+ 'journal_ids': tuple(journal_ids),
+ 'account_ids': tuple(account_ids)}
+ )
while 1:
# http://initd.org/psycopg/docs/cursor.html#cursor.fetchmany
# Set cursor.arraysize to minimize network round trips
- cr.arraysize=100
+ cr.arraysize = 100
rows = cr.fetchmany()
if not rows:
break
for row in rows:
yield row
- def get_data(self, cr, uid, ids,result_type,context=None):
- get_header_func = getattr(self,("_get_header_%s"%(result_type)), None)
- get_rows_func = getattr(self,("_get_rows_%s"%(result_type)), None)
+ def get_data(self, cr, uid, ids, result_type, context=None):
+ get_header_func = getattr(
+ self, ("_get_header_%s" % (result_type)), None)
+ get_rows_func = getattr(self, ("_get_rows_%s" % (result_type)), None)
form = self.browse(cr, uid, ids[0], context=context)
fiscalyear_id = form.fiscalyear_id.id
- user_obj = self.pool.get('res.users')
if form.periods:
period_range_ids = [x.id for x in form.periods]
else:
# If not period selected , we take all periods
p_obj = self.pool.get("account.period")
- period_range_ids = p_obj.search(cr, uid, [('fiscalyear_id','=',fiscalyear_id)], context=context)
+ period_range_ids = p_obj.search(
+ cr, uid, [('fiscalyear_id', '=', fiscalyear_id)],
+ context=context)
journal_ids = None
if form.journal_ids:
journal_ids = [x.id for x in form.journal_ids]
else:
j_obj = self.pool.get("account.journal")
journal_ids = j_obj.search(cr, uid, [], context=context)
- rows = itertools.chain((get_header_func(cr, uid, ids, context=context),),
+ account_ids = None
+ if form.account_ids:
+ account_ids = [x.id for x in form.account_ids]
+ else:
+ aa_obj = self.pool.get("account.account")
+ account_ids = aa_obj.search(cr, uid, [], context=context)
+ rows = itertools.chain((get_header_func(cr, uid, ids,
+ context=context),),
get_rows_func(cr, uid, ids,
fiscalyear_id,
period_range_ids,
journal_ids,
+ account_ids,
context=context)
)
return rows
dépôts / burette / account_export_csv.git / blobdiff