1
# -*- encoding: utf-8 -*-
2
##############################################################################
4
# Copyright (c) 2005-2006 CamptoCamp
5
# Copyright (c) 2009 Zikzakmedia S.L. (http://zikzakmedia.com) All Rights Reserved.
6
# Jordi Esteve <jesteve@zikzakmedia.com>
8
# WARNING: This program as such is intended to be used by professional
9
# programmers who take the whole responsability of assessing all potential
10
# consequences resulting from its eventual inadequacies and bugs
11
# End users who are looking for a ready-to-use solution with commercial
12
# garantees and support are strongly adviced to contract a Free Software
15
# This program is Free Software; you can redistribute it and/or
16
# modify it under the terms of the GNU General Public License
17
# as published by the Free Software Foundation; either version 2
18
# of the License, or (at your option) any later version.
20
# This program is distributed in the hope that it will be useful,
21
# but WITHOUT ANY WARRANTY; without even the implied warranty of
22
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23
# GNU General Public License for more details.
25
# You should have received a copy of the GNU General Public License
26
# along with this program; if not, write to the Free Software
27
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
29
##############################################################################
33
from report import report_sxw
35
from tools import config
36
from tools.translate import _
39
class general_ledger_landscape(rml_parse.rml_parse):
40
_name = 'report.account.general.ledger.cumulative.landscape'
42
def set_context(self, objects, data, ids, report_type = None):
43
self.get_context_date_period(data['form'])
45
if (data['model'] == 'account.account'):
48
new_ids = data['form']['Account_list'][0][2]
49
objects = self.pool.get('account.account').browse(self.cr, self.uid, new_ids)
50
super(general_ledger_landscape, self).set_context(objects, data, new_ids, report_type)
53
def __init__(self, cr, uid, name, context):
54
super(general_ledger_landscape, self).__init__(cr, uid, name, context=context)
55
self.query = "" # SQL query to get account moves for given date or period
56
self.min_date = "" # Min date of the given date or period
57
self.ctx = {} # Context for given date or period
58
self.ctxfy = {} # Context from the date start or first period of the fiscal year
60
self.tot_currency = 0.0
61
self.balance_accounts = {}
62
self.localcontext.update( {
65
'sum_debit_account': self._sum_debit_account,
66
'sum_credit_account': self._sum_credit_account,
67
'sum_balance_account': self._sum_balance_account,
68
'get_children_accounts': self.get_children_accounts,
69
'sum_currency_amount_account': self._sum_currency_amount_account,
70
'get_fiscalyear':self.get_fiscalyear,
71
'get_periods':self.get_periods,
73
self.context = context
76
def get_fiscalyear(self, form):
78
if form.has_key('fiscalyear'):
79
fisc_id = form['fiscalyear']
82
self.cr.execute("SELECT name FROM account_fiscalyear WHERE id = %s" , (int(fisc_id),))
83
res=self.cr.fetchone()
84
return res and res[0] or ''
87
def get_periods(self, form):
89
if form.has_key('periods') and form['periods'][0][2]:
90
period_ids = ",".join([str(x) for x in form['periods'][0][2] if x])
91
self.cr.execute("SELECT name FROM account_period WHERE id in (%s)" % (period_ids))
92
res = self.cr.fetchall()
95
if (r == res[len_res-1]):
99
elif form.has_key('date_from') and form.has_key('date_to'):
100
result = self.formatLang(form['date_from'], date=True) + ' - ' + self.formatLang(form['date_to'], date=True) + ' '
102
fy_obj = self.pool.get('account.fiscalyear').browse(self.cr,self.uid,form['fiscalyear'])
103
res = fy_obj.period_ids
106
if r == res[len_res-1]:
111
return str(result and result[:-1]) or ''
114
def _calc_contrepartie(self, cr, uid, ids, context={}):
117
# result.setdefault(id, False)
119
for account_line in self.pool.get('account.move.line').browse(cr, uid, ids, context):
120
# For avoid long text in the field we will limit it to 5 lines
122
result[account_line.id] = ' '
123
num_id_move = str(account_line.move_id.id)
124
num_id_line = str(account_line.id)
125
account_id = str(account_line.account_id.id)
126
# search the basic account
127
# We have the account ID we will search all account move line from now until this time
128
# We are in the case of we are on the top of the account move Line
129
cr.execute("SELECT distinct(ac.code) as code_rest,ac.name as name_rest "\
130
"FROM account_account AS ac, account_move_line mv "\
131
"WHERE ac.id = mv.account_id and mv.move_id = " + num_id_move + " and mv.account_id <> " + account_id )
132
res_mv = cr.dictfetchall()
133
# we need a result more than 2 line to make the test so we will made the the on 1 because we have exclude the current line
134
if (len(res_mv) >=1):
137
for move_rest in res_mv:
138
concat = concat + move_rest['code_rest'] + '|'
139
result[account_line.id] = concat
141
# we need to stop the computing and to escape but before we will add "..."
142
result[account_line.id] = concat + '...'
148
def get_context_date_period(self, form):
149
date_min = period_min = False
151
# ctx: Context for the given date or period
152
ctx = self.context.copy()
153
ctx['state'] = form['context'].get('state','all')
154
if 'fiscalyear' in form and form['fiscalyear']:
155
ctx['fiscalyear'] = form['fiscalyear']
156
if form['state'] in ['byperiod', 'all']:
157
ctx['periods'] = form['periods'][0][2]
158
if form['state'] in ['bydate', 'all']:
159
ctx['date_from'] = form['date_from']
160
ctx['date_to'] = form['date_to']
161
if 'periods' not in ctx:
164
self.query = self.pool.get('account.move.line')._query_get(self.cr, self.uid, context=ctx)
166
# ctxfy: Context from the date start / first period of the fiscal year
168
ctxfy['periods'] = ctx['periods'][:]
170
if form['state'] in ['byperiod', 'all'] and len(ctx['periods']):
171
self.cr.execute("""SELECT id, date_start, fiscalyear_id
173
WHERE date_start = (SELECT min(date_start) FROM account_period WHERE id in (%s))"""
174
% (','.join([str(x) for x in ctx['periods']])))
175
res = self.cr.dictfetchone()
176
period_min = res['date_start']
177
self.cr.execute("""SELECT id
179
WHERE fiscalyear_id in (%s) AND date_start < '%s'"""
180
% (res['fiscalyear_id'], res['date_start']))
181
ids = filter(None, map(lambda x:x[0], self.cr.fetchall()))
182
ctxfy['periods'].extend(ids)
184
if form['state'] in ['bydate', 'all']:
185
self.cr.execute("""SELECT date_start
186
FROM account_fiscalyear
187
WHERE '%s' BETWEEN date_start AND date_stop""" % (ctx['date_from']))
188
res = self.cr.dictfetchone()
189
ctxfy['date_from'] = res['date_start']
190
date_min = form['date_from']
192
if form['state'] == 'none' or (form['state'] == 'byperiod' and not len(ctx['periods'])):
193
if 'fiscalyear' in form and form['fiscalyear']:
194
sql = """SELECT id, date_start
196
WHERE fiscalyear_id in (%s)
197
ORDER BY date_start""" % (ctx['fiscalyear'])
199
sql = """SELECT id, date_start
201
WHERE fiscalyear_id in (SELECT id FROM account_fiscalyear WHERE state='draft')
202
ORDER BY date_start"""
204
res = self.cr.dictfetchall()
205
period_min = res[0]['date_start']
206
ids = filter(None, map(lambda x:x['id'], res))
207
ctxfy['periods'] = ids
211
self.min_date = date_min
213
self.min_date = period_min
215
# If period and date are given, the maximum of the min dates is choosed
216
if period_min < date_min:
217
self.min_date = date_min
219
self.min_date = period_min
220
# print "ctx=", self.ctx
221
# print "ctxfy=", self.ctxfy
222
# print "query=", self.query
225
def get_children_accounts(self, account, form):
226
move_line_obj = self.pool.get('account.move.line')
227
account_obj = self.pool.get('account.account')
228
invoice_obj = self.pool.get('account.invoice')
229
self.child_ids = account_obj.search(self.cr, self.uid, [('parent_id', 'child_of', self.ids)])
232
ctx = self.ctx.copy()
233
if account and account.child_consol_ids: # add ids of consolidated childs also of selected account
234
ctx['consolidate_childs'] = True
235
ctx['account_id'] = account.id
236
ids_acc = account_obj.search(self.cr, self.uid,[('parent_id', 'child_of', [account.id])], context=ctx)
237
for child_id in ids_acc:
238
child_account = account_obj.browse(self.cr, self.uid, child_id)
239
balance_account = self._sum_balance_account(child_account,form)
240
self.balance_accounts[child_account.id] = balance_account
241
if form['display_account'] == 'bal_mouvement':
242
if child_account.type != 'view' \
243
and len(move_line_obj.search(self.cr, self.uid,
244
[('account_id','=',child_account.id)],
246
res.append(child_account)
247
elif form['display_account'] == 'bal_solde':
248
if child_account.type != 'view' \
249
and len(move_line_obj.search(self.cr, self.uid,
250
[('account_id','=',child_account.id)],
252
if balance_account <> 0.0:
253
res.append(child_account)
255
if child_account.type != 'view' \
256
and len(move_line_obj.search(self.cr, self.uid,
257
[('account_id','>=',child_account.id)],
259
res.append(child_account)
264
## We will now compute initial balance
266
sql_balance_init = "SELECT sum(l.debit) AS sum_debit, sum(l.credit) AS sum_credit "\
267
"FROM account_move_line l "\
268
"WHERE l.account_id = " + str(move.id) + " AND %s" % (self.query)
269
self.cr.execute(sql_balance_init)
270
resultat = self.cr.dictfetchall()
272
if resultat[0]['sum_debit'] == None:
275
sum_debit = resultat[0]['sum_debit']
276
if resultat[0]['sum_credit'] == None:
279
sum_credit = resultat[0]['sum_credit']
281
move.init_credit = sum_credit
282
move.init_debit = sum_debit
289
def lines(self, account, form):
291
'out_invoice': _('CI: '),
292
'in_invoice': _('SI: '),
293
'out_refund': _('OR: '),
294
'in_refund': _('SR: '),
297
if form['sortbydate'] == 'sort_date':
302
SELECT l.id, l.date, j.code, c.code AS currency_code, l.amount_currency, l.ref, l.name , l.debit, l.credit, l.period_id
303
FROM account_move_line as l
304
LEFT JOIN res_currency c on (l.currency_id=c.id)
305
JOIN account_journal j on (l.journal_id=j.id)
308
ORDER by %s""" % (self.query, sorttag)
309
self.cr.execute(sql % account.id)
311
res = self.cr.dictfetchall()
312
move_line_obj = self.pool.get('account.move.line')
313
account_obj = self.pool.get('account.account')
314
invoice_obj = self.pool.get('account.invoice')
316
# Balance from init fiscal year to last date given by the user
317
accounts = account_obj.read(self.cr, self.uid, [account.id], ['balance'], self.ctxfy)
318
sum = accounts[0]['balance']
320
for l in reversed(res):
321
line = move_line_obj.browse(self.cr, self.uid, l['id'])
322
l['move'] = line.move_id.name
323
self.cr.execute('Select id from account_invoice where move_id =%s'%(line.move_id.id))
324
tmpres = self.cr.dictfetchall()
326
inv = invoice_obj.browse(self.cr, self.uid, tmpres[0]['id'])
327
l['ref'] = inv_types[inv.type] + ': '+str(inv.number)
329
l['partner'] = line.partner_id.name
332
l['line_corresp'] = self._calc_contrepartie(self.cr,self.uid,[l['id']])[l['id']]
334
# Cumulative balance update
336
sum = sum - (l['debit'] or 0) + (l['credit'] or 0)
338
# Modification of currency amount
339
if (l['credit'] > 0):
340
if l['amount_currency'] != None:
341
l['amount_currency'] = abs(l['amount_currency']) * -1
342
if l['amount_currency'] != None:
343
self.tot_currency = self.tot_currency + l['amount_currency']
345
if abs(sum) > 10**-int(config['price_accuracy']) and form['initial_balance']:
347
'date': self.min_date,
348
'name': _('Initial balance'),
355
'amount_currency': '',
364
def _sum_debit_account(self, account, form):
365
self.cr.execute("SELECT sum(debit) "\
366
"FROM account_move_line l "\
367
"WHERE l.account_id = %s AND %s " % (account.id, self.query))
368
sum_debit = self.cr.fetchone()[0] or 0.0
372
def _sum_credit_account(self, account, form):
373
self.cr.execute("SELECT sum(credit) "\
374
"FROM account_move_line l "\
375
"WHERE l.account_id = %s AND %s " % (account.id, self.query))
376
sum_credit = self.cr.fetchone()[0] or 0.0
380
def _sum_balance_account(self, account, form):
381
# Balance from init fiscal year to last date given by the user
382
accounts = self.pool.get('account.account').read(self.cr, self.uid, [account.id], ['balance'], self.ctxfy)
383
sum_balance = accounts[0]['balance']
387
def _set_get_account_currency_code(self, account_id):
388
self.cr.execute("SELECT c.code as code "\
389
"FROM res_currency c, account_account as ac "\
390
"WHERE ac.id = %s AND ac.currency_id = c.id" % (account_id))
391
result = self.cr.fetchone()
393
self.account_currency = result[0]
395
self.account_currency = False
398
def _sum_currency_amount_account(self, account, form):
399
self._set_get_account_currency_code(account.id)
400
self.cr.execute("SELECT sum(l.amount_currency) "\
401
"FROM account_move_line as l, res_currency as rc "\
402
"WHERE l.currency_id = rc.id AND l.account_id= %s AND %s" % (account.id, self.query))
403
total = self.cr.fetchone()
404
if self.account_currency:
405
return_field = str(total[0]) + self.account_currency
408
currency_total = self.tot_currency = 0.0
409
return currency_total
412
report_sxw.report_sxw('report.account.general.ledger.cumulative.landscape', 'account.account', 'addons/account_financial_report/report/general_ledger_landscape.rml', parser=general_ledger_landscape, header=False)
413
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: