1 # -*- encoding: utf-8 -*-
2 ##############################################################################
4 # Author: Nicolas Bessi, Guewen Baconnier
5 # Copyright Camptocamp SA 2011
6 # SQL inspired from OpenERP original code
8 # This program is free software: you can redistribute it and/or modify
9 # it under the terms of the GNU Affero General Public License as
10 # published by the Free Software Foundation, either version 3 of the
11 # License, or (at your option) any later version.
13 # This program is distributed in the hope that it will be useful,
14 # but WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU Affero General Public License for more details.
18 # You should have received a copy of the GNU Affero General Public License
19 # along with this program. If not, see <http://www.gnu.org/licenses/>.
21 ##############################################################################
22 # TODO refactor helper in order to act more like mixin
23 # By using properties we will have a more simple signature in fuctions
27 from openerp
.osv
import osv
28 from openerp
.tools
.translate
import _
29 from openerp
.addons
.account
.report
.common_report_header
import common_report_header
31 _logger
= logging
.getLogger('financial.reports.webkit')
33 MAX_MONSTER_SLICE
= 50000
34 class CommonReportHeaderWebkit(common_report_header
):
35 """Define common helper for financial report"""
37 ####################From getter helper #####################################
38 def get_start_period_br(self
, data
):
39 return self
._get
_info
(data
, 'period_from', 'account.period')
41 def get_end_period_br(self
, data
):
42 return self
._get
_info
(data
, 'period_to', 'account.period')
44 def get_fiscalyear_br(self
, data
):
45 return self
._get
_info
(data
, 'fiscalyear_id', 'account.fiscalyear')
47 def _get_chart_account_id_br(self
, data
):
48 return self
._get
_info
(data
, 'chart_account_id', 'account.account')
50 def _get_accounts_br(self
, data
):
51 return self
._get
_info
(data
, 'account_ids', 'account.account')
53 def _get_info(self
, data
, field
, model
):
54 info
= data
.get('form', {}).get(field
)
56 return self
.pool
.get(model
).browse(self
.cursor
, self
.uid
, info
)
59 def _get_journals_br(self
, data
):
60 return self
._get
_info
(data
, 'journal_ids', 'account.journal')
62 def _get_display_account(self
, data
):
63 val
= self
._get
_form
_param
('display_account', data
)
65 return _('All accounts')
66 elif val
== 'bal_mix':
67 return _('With transactions or non zero balance')
71 def _get_display_partner_account(self
, data
):
72 val
= self
._get
_form
_param
('result_selection', data
)
74 return _('Receivable Accounts')
75 elif val
== 'supplier':
76 return _('Payable Accounts')
77 elif val
== 'customer_supplier':
78 return _('Receivable and Payable Accounts')
82 def _get_display_target_move(self
, data
):
83 val
= self
._get
_form
_param
('target_move', data
)
85 return _('All Posted Entries')
87 return _('All Entries')
91 def _get_display_account_raw(self
, data
):
92 return self
._get
_form
_param
('display_account', data
)
94 def _get_filter(self
, data
):
95 return self
._get
_form
_param
('filter', data
)
97 def _get_target_move(self
, data
):
98 return self
._get
_form
_param
('target_move', data
)
100 def _get_initial_balance(self
, data
):
101 return self
._get
_form
_param
('initial_balance', data
)
103 def _get_amount_currency(self
, data
):
104 return self
._get
_form
_param
('amount_currency', data
)
106 def _get_date_from(self
, data
):
107 return self
._get
_form
_param
('date_from', data
)
109 def _get_date_to(self
, data
):
110 return self
._get
_form
_param
('date_to', data
)
112 def _get_form_param(self
, param
, data
, default
=False):
113 return data
.get('form', {}).get(param
, default
)
115 ####################Account and account line filter helper #################
117 def sort_accounts_with_structure(self
, root_account_ids
, account_ids
, context
=None):
118 """Sort accounts by code respecting their structure"""
120 def recursive_sort_by_code(accounts
, parent
):
122 # add all accounts with same parent
123 level_accounts
= [account
for account
in accounts
124 if account
['parent_id'] and account
['parent_id'][0] == parent
['id']]
125 # add consolidation children of parent, as they are logically on the same level
126 if parent
.get('child_consol_ids'):
127 level_accounts
.extend([account
for account
in accounts
128 if account
['id'] in parent
['child_consol_ids']])
129 # stop recursion if no children found
130 if not level_accounts
:
133 level_accounts
= sorted(level_accounts
, key
=lambda a
: a
['code'])
135 for level_account
in level_accounts
:
136 sorted_accounts
.append(level_account
['id'])
137 sorted_accounts
.extend(recursive_sort_by_code(accounts
, parent
=level_account
))
138 return sorted_accounts
143 accounts_data
= self
.pool
.get('account.account').read(self
.cr
, self
.uid
,
145 ['id', 'parent_id', 'level', 'code', 'child_consol_ids'],
150 root_accounts_data
= [account_data
for account_data
in accounts_data
151 if account_data
['id'] in root_account_ids
]
152 for root_account_data
in root_accounts_data
:
153 sorted_accounts
.append(root_account_data
['id'])
154 sorted_accounts
.extend(recursive_sort_by_code(accounts_data
, root_account_data
))
156 # fallback to unsorted accounts when sort failed
157 # sort fails when the levels are miscalculated by account.account
159 if len(sorted_accounts
) != len(account_ids
):
160 _logger
.warn('Webkit financial reports: Sort of accounts failed.')
161 sorted_accounts
= account_ids
163 return sorted_accounts
165 def get_all_accounts(self
, account_ids
, exclude_type
=None, only_type
=None, filter_report_type
=None, context
=None):
166 """Get all account passed in params with their childrens
168 @param exclude_type: list of types to exclude (view, receivable, payable, consolidation, other)
169 @param only_type: list of types to filter on (view, receivable, payable, consolidation, other)
170 @param filter_report_type: list of report type to filter on
172 context
= context
or {}
174 if not isinstance(account_ids
, list):
175 account_ids
= [account_ids
]
176 acc_obj
= self
.pool
.get('account.account')
177 for account_id
in account_ids
:
178 accounts
.append(account_id
)
179 accounts
+= acc_obj
._get
_children
_and
_consol
(self
.cursor
, self
.uid
, account_id
, context
=context
)
180 res_ids
= list(set(accounts
))
181 res_ids
= self
.sort_accounts_with_structure(account_ids
, res_ids
, context
=context
)
183 if exclude_type
or only_type
or filter_report_type
:
184 sql_filters
= {'ids': tuple(res_ids
)}
185 sql_select
= "SELECT a.id FROM account_account a"
187 sql_where
= "WHERE a.id IN %(ids)s"
189 sql_where
+= " AND a.type not in %(exclude_type)s"
190 sql_filters
.update({'exclude_type': tuple(exclude_type
)})
192 sql_where
+= " AND a.type IN %(only_type)s"
193 sql_filters
.update({'only_type': tuple(only_type
)})
194 if filter_report_type
:
195 sql_join
+= "INNER JOIN account_account_type t" \
196 " ON t.id = a.user_type"
197 sql_join
+= " AND t.report_type IN %(report_type)s"
198 sql_filters
.update({'report_type': tuple(filter_report_type
)})
200 sql
= ' '.join((sql_select
, sql_join
, sql_where
))
201 self
.cursor
.execute(sql
, sql_filters
)
202 fetch_only_ids
= self
.cursor
.fetchall()
203 if not fetch_only_ids
:
205 only_ids
= [only_id
[0] for only_id
in fetch_only_ids
]
206 # keep sorting but filter ids
207 res_ids
= [res_id
for res_id
in res_ids
if res_id
in only_ids
]
210 ####################Periods and fiscal years helper #######################
212 def _get_opening_periods(self
):
213 """Return the list of all journal that can be use to create opening entries
214 We actually filter on this instead of opening period as older version of OpenERP
215 did not have this notion"""
216 return self
.pool
.get('account.period').search(self
.cursor
, self
.uid
, [('special', '=', True)])
218 def exclude_opening_periods(self
, period_ids
):
219 period_obj
= self
.pool
.get('account.period')
220 return period_obj
.search(self
.cr
, self
.uid
, [['special', '=', False], ['id', 'in', period_ids
]])
222 def get_included_opening_period(self
, period
):
223 """Return the opening included in normal period we use the assumption
224 that there is only one opening period per fiscal year"""
225 period_obj
= self
.pool
.get('account.period')
226 return period_obj
.search(self
.cursor
, self
.uid
,
227 [('special', '=', True),
228 ('date_start', '>=', period
.date_start
),
229 ('date_stop', '<=', period
.date_stop
),
230 ('company_id', '=', period
.company_id
.id)],
233 def periods_contains_move_lines(self
, period_ids
):
236 mv_line_obj
= self
.pool
.get('account.move.line')
237 if isinstance(period_ids
, (int, long)):
238 period_ids
= [period_ids
]
239 return mv_line_obj
.search(self
.cursor
, self
.uid
, [('period_id', 'in', period_ids
)], limit
=1) and True or False
241 def _get_period_range_from_periods(self
, start_period
, stop_period
, mode
=None):
243 Deprecated. We have to use now the build_ctx_periods of period_obj otherwise we'll have
244 inconsistencies, because build_ctx_periods does never filter on the the special
246 period_obj
= self
.pool
.get('account.period')
247 search_period
= [('date_start', '>=', start_period
.date_start
),
248 ('date_stop', '<=', stop_period
.date_stop
)]
250 if mode
== 'exclude_opening':
251 search_period
+= [('special', '=', False)]
252 res
= period_obj
.search(self
.cursor
, self
.uid
, search_period
)
255 def _get_period_range_from_start_period(self
, start_period
, include_opening
=False,
256 fiscalyear
=False, stop_at_previous_opening
=False):
257 """We retrieve all periods before start period"""
258 opening_period_id
= False
260 period_obj
= self
.pool
.get('account.period')
261 mv_line_obj
= self
.pool
.get('account.move.line')
262 # We look for previous opening period
263 if stop_at_previous_opening
:
264 opening_search
= [('special', '=', True),
265 ('date_stop', '<', start_period
.date_start
)]
267 opening_search
.append(('fiscalyear_id', '=', fiscalyear
.id))
269 opening_periods
= period_obj
.search(self
.cursor
, self
.uid
, opening_search
,
270 order
='date_stop desc')
271 for opening_period
in opening_periods
:
272 validation_res
= mv_line_obj
.search(self
.cursor
,
274 [('period_id', '=', opening_period
)],
277 opening_period_id
= opening_period
279 if opening_period_id
:
280 #we also look for overlapping periods
281 opening_period_br
= period_obj
.browse(self
.cursor
, self
.uid
, opening_period_id
)
282 past_limit
= [('date_start', '>=', opening_period_br
.date_stop
)]
284 periods_search
= [('date_stop', '<=', start_period
.date_stop
)]
285 periods_search
+= past_limit
287 if not include_opening
:
288 periods_search
+= [('special', '=', False)]
291 periods_search
.append(('fiscalyear_id', '=', fiscalyear
.id))
292 periods
= period_obj
.search(self
.cursor
, self
.uid
, periods_search
)
293 if include_opening
and opening_period_id
:
294 periods
.append(opening_period_id
)
295 periods
= list(set(periods
))
296 if start_period
.id in periods
:
297 periods
.remove(start_period
.id)
300 def get_first_fiscalyear_period(self
, fiscalyear
):
301 return self
._get
_st
_fiscalyear
_period
(fiscalyear
)
303 def get_last_fiscalyear_period(self
, fiscalyear
):
304 return self
._get
_st
_fiscalyear
_period
(fiscalyear
, order
='DESC')
306 def _get_st_fiscalyear_period(self
, fiscalyear
, special
=False, order
='ASC'):
307 period_obj
= self
.pool
.get('account.period')
308 p_id
= period_obj
.search(self
.cursor
,
310 [('special', '=', special
),
311 ('fiscalyear_id', '=', fiscalyear
.id)],
313 order
='date_start %s' % (order
,))
315 raise osv
.except_osv(_('No period found'), '')
316 return period_obj
.browse(self
.cursor
, self
.uid
, p_id
[0])
318 ####################Initial Balance helper #################################
320 def _compute_init_balance(self
, account_id
=None, period_ids
=None, mode
='computed', default_values
=False):
321 if not isinstance(period_ids
, list):
322 period_ids
= [period_ids
]
325 if not default_values
:
326 if not account_id
or not period_ids
:
327 raise Exception('Missing account or period_ids')
329 self
.cursor
.execute("SELECT sum(debit) AS debit, "
330 " sum(credit) AS credit, "
331 " sum(debit)-sum(credit) AS balance, "
332 " sum(amount_currency) AS curr_balance"
333 " FROM account_move_line"
334 " WHERE period_id in %s"
335 " AND account_id = %s", (tuple(period_ids
), account_id
))
336 res
= self
.cursor
.dictfetchone()
338 except Exception, exc
:
339 self
.cursor
.rollback()
342 return {'debit': res
.get('debit') or 0.0,
343 'credit': res
.get('credit') or 0.0,
344 'init_balance': res
.get('balance') or 0.0,
345 'init_balance_currency': res
.get('curr_balance') or 0.0,
348 def _read_opening_balance(self
, account_ids
, start_period
):
349 """ Read opening balances from the opening balance
351 opening_period_selected
= self
.get_included_opening_period(start_period
)
352 if not opening_period_selected
:
353 raise osv
.except_osv(
355 _('No opening period found to compute the opening balances.\n'
356 'You have to configure a period on the first of January'
357 ' with the special flag.'))
360 for account_id
in account_ids
:
361 res
[account_id
] = self
._compute
_init
_balance
(account_id
, opening_period_selected
, mode
='read')
364 def _compute_initial_balances(self
, account_ids
, start_period
, fiscalyear
):
365 """We compute initial balance.
366 If form is filtered by date all initial balance are equal to 0
367 This function will sum pear and apple in currency amount if account as no secondary currency"""
368 # if opening period is included in start period we do not need to compute init balance
369 # we just read it from opening entries
371 # PNL and Balance accounts are not computed the same way look for attached doc
372 # We include opening period in pnl account in order to see if opening entries
373 # were created by error on this account
374 pnl_periods_ids
= self
._get
_period
_range
_from
_start
_period
(start_period
, fiscalyear
=fiscalyear
,
375 include_opening
=True)
376 bs_period_ids
= self
._get
_period
_range
_from
_start
_period
(start_period
, include_opening
=True,
377 stop_at_previous_opening
=True)
378 opening_period_selected
= self
.get_included_opening_period(start_period
)
380 for acc
in self
.pool
.get('account.account').browse(self
.cursor
, self
.uid
, account_ids
):
381 res
[acc
.id] = self
._compute
_init
_balance
(default_values
=True)
382 if acc
.user_type
.close_method
== 'none':
383 # we compute the initial balance for close_method == none only when we print a GL
384 # during the year, when the opening period is not included in the period selection!
385 if pnl_periods_ids
and not opening_period_selected
:
386 res
[acc
.id] = self
._compute
_init
_balance
(acc
.id, pnl_periods_ids
)
388 res
[acc
.id] = self
._compute
_init
_balance
(acc
.id, bs_period_ids
)
391 ####################Account move retrieval helper ##########################
392 def _get_move_ids_from_periods(self
, account_id
, period_start
, period_stop
, target_move
):
393 move_line_obj
= self
.pool
.get('account.move.line')
394 period_obj
= self
.pool
.get('account.period')
395 periods
= period_obj
.build_ctx_periods(self
.cursor
, self
.uid
, period_start
.id, period_stop
.id)
398 search
= [('period_id', 'in', periods
), ('account_id', '=', account_id
)]
399 if target_move
== 'posted':
400 search
+= [('move_id.state', '=', 'posted')]
401 return move_line_obj
.search(self
.cursor
, self
.uid
, search
)
403 def _get_move_ids_from_dates(self
, account_id
, date_start
, date_stop
, target_move
, mode
='include_opening'):
404 # TODO imporve perfomance by setting opening period as a property
405 move_line_obj
= self
.pool
.get('account.move.line')
406 search_period
= [('date', '>=', date_start
),
407 ('date', '<=', date_stop
),
408 ('account_id', '=', account_id
)]
410 # actually not used because OpenERP itself always include the opening when we
411 # get the periods from january to december
412 if mode
== 'exclude_opening':
413 opening
= self
._get
_opening
_periods
()
415 search_period
+= ['period_id', 'not in', opening
]
417 if target_move
== 'posted':
418 search_period
+= [('move_id.state', '=', 'posted')]
420 return move_line_obj
.search(self
.cursor
, self
.uid
, search_period
)
422 def get_move_lines_ids(self
, account_id
, main_filter
, start
, stop
, target_move
, mode
='include_opening'):
423 """Get account move lines base on form data"""
424 if mode
not in ('include_opening', 'exclude_opening'):
425 raise osv
.except_osv(_('Invalid query mode'), _('Must be in include_opening, exclude_opening'))
427 if main_filter
in ('filter_period', 'filter_no'):
428 return self
._get
_move
_ids
_from
_periods
(account_id
, start
, stop
, target_move
)
430 elif main_filter
== 'filter_date':
431 return self
._get
_move
_ids
_from
_dates
(account_id
, start
, stop
, target_move
)
433 raise osv
.except_osv(_('No valid filter'), _('Please set a valid time filter'))
435 def _get_move_line_datas(self
, move_line_ids
, order
='per.special DESC, l.date ASC, per.date_start ASC, m.name ASC'):
436 # Possible bang if move_line_ids is too long
437 # We can not slice here as we have to do the sort.
438 # If slice has to be done it means that we have to reorder in python
439 # after all is finished. That quite crapy...
440 # We have a defective desing here (mea culpa) that should be fixed
442 # TODO improve that by making a better domain or if not possible
443 # by using python sort
444 if not move_line_ids
:
446 if not isinstance(move_line_ids
, list):
447 move_line_ids
= [move_line_ids
]
458 COALESCE(l.debit, 0.0) - COALESCE(l.credit, 0.0) AS balance,
461 l.period_id AS lperiod_id,
462 per.code as period_code,
463 per.special AS peropen,
464 l.partner_id AS lpartner_id,
465 p.name AS partner_name,
467 COALESCE(partialrec.name, fullrec.name, '') AS rec_name,
468 COALESCE(partialrec.id, fullrec.id, NULL) AS rec_id,
470 c.name AS currency_code,
472 i.type AS invoice_type,
473 i.number AS invoice_number,
475 FROM account_move_line l
476 JOIN account_move m on (l.move_id=m.id)
477 LEFT JOIN res_currency c on (l.currency_id=c.id)
478 LEFT JOIN account_move_reconcile partialrec on (l.reconcile_partial_id = partialrec.id)
479 LEFT JOIN account_move_reconcile fullrec on (l.reconcile_id = fullrec.id)
480 LEFT JOIN res_partner p on (l.partner_id=p.id)
481 LEFT JOIN account_invoice i on (m.id =i.move_id)
482 LEFT JOIN account_period per on (per.id=l.period_id)
483 JOIN account_journal j on (l.journal_id=j.id)
485 monster
+= (" ORDER BY %s" % (order
,))
487 self
.cursor
.execute(monster
, (tuple(move_line_ids
),))
488 res
= self
.cursor
.dictfetchall()
489 except Exception, exc
:
490 self
.cursor
.rollback()
494 def _get_moves_counterparts(self
, move_ids
, account_id
, limit
=3):
497 if not isinstance(move_ids
, list):
498 move_ids
= [move_ids
]
500 SELECT account_move.id,
502 ARRAY(SELECT DISTINCT a.code
503 FROM account_move_line m2
504 LEFT JOIN account_account a ON (m2.account_id=a.id)
505 WHERE m2.move_id =account_move_line.move_id
506 AND m2.account_id<>%s limit %s) , ', ')
509 JOIN account_move_line on (account_move_line.move_id = account_move.id)
510 JOIN account_account on (account_move_line.account_id = account_account.id)
511 WHERE move_id in %s"""
514 self
.cursor
.execute(sql
, (account_id
, limit
, tuple(move_ids
)))
515 res
= self
.cursor
.fetchall()
516 except Exception as exc
:
517 self
.cursor
.rollback()
519 return res
and dict(res
) or {}
521 def is_initial_balance_enabled(self
, main_filter
):
522 if main_filter
not in ('filter_no', 'filter_year', 'filter_period'):
526 def _get_initial_balance_mode(self
, start_period
):
527 opening_period_selected
= self
.get_included_opening_period(start_period
)
528 opening_move_lines
= self
.periods_contains_move_lines(opening_period_selected
)
529 if opening_move_lines
:
530 return 'opening_balance'
532 return 'initial_balance'