report/common_reports.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    Author: Nicolas Bessi, Guewen Baconnier
   5 #    Copyright Camptocamp SA 2011
   6 #    SQL inspired from OpenERP original code
   7 #
   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.
  12 #
  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.
  17 #
  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/>.
  20 #
  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
  24
  25 import logging
  26
  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
  30
  31 _logger = logging.getLogger('financial.reports.webkit')
  32
  33 MAX_MONSTER_SLICE = 50000
  34 class CommonReportHeaderWebkit(common_report_header):
  35     """Define common helper for financial report"""
  36
  37     ####################From getter helper #####################################
  38     def get_start_period_br(self, data):
  39         return self._get_info(data, 'period_from', 'account.period')
  40
  41     def get_end_period_br(self, data):
  42         return self._get_info(data, 'period_to', 'account.period')
  43
  44     def get_fiscalyear_br(self, data):
  45         return self._get_info(data, 'fiscalyear_id', 'account.fiscalyear')
  46
  47     def _get_chart_account_id_br(self, data):
  48         return self._get_info(data, 'chart_account_id', 'account.account')
  49
  50     def _get_accounts_br(self, data):
  51         return self._get_info(data, 'account_ids', 'account.account')
  52
  53     def _get_info(self, data, field, model):
  54         info = data.get('form', {}).get(field)
  55         if info:
  56             return self.pool.get(model).browse(self.cursor, self.uid, info)
  57         return False
  58
  59     def _get_journals_br(self, data):
  60         return self._get_info(data, 'journal_ids', 'account.journal')
  61
  62     def _get_display_account(self, data):
  63         val = self._get_form_param('display_account', data)
  64         if val == 'bal_all':
  65             return _('All accounts')
  66         elif val == 'bal_mix':
  67             return _('With transactions or non zero balance')
  68         else:
  69             return val
  70
  71     def _get_display_partner_account(self, data):
  72         val = self._get_form_param('result_selection', data)
  73         if val == 'customer':
  74             return _('Receivable Accounts')
  75         elif val == 'supplier':
  76             return _('Payable Accounts')
  77         elif val == 'customer_supplier':
  78             return _('Receivable and Payable Accounts')
  79         else:
  80             return val
  81
  82     def _get_display_target_move(self, data):
  83         val = self._get_form_param('target_move', data)
  84         if val == 'posted':
  85             return _('All Posted Entries')
  86         elif val == 'all':
  87             return _('All Entries')
  88         else:
  89             return val
  90
  91     def _get_display_account_raw(self, data):
  92         return self._get_form_param('display_account', data)
  93
  94     def _get_filter(self, data):
  95         return self._get_form_param('filter', data)
  96
  97     def _get_target_move(self, data):
  98         return self._get_form_param('target_move', data)
  99
 100     def _get_initial_balance(self, data):
 101         return self._get_form_param('initial_balance', data)
 102
 103     def _get_amount_currency(self, data):
 104         return self._get_form_param('amount_currency', data)
 105
 106     def _get_date_from(self, data):
 107         return self._get_form_param('date_from', data)
 108
 109     def _get_date_to(self, data):
 110         return self._get_form_param('date_to', data)
 111
 112     def _get_form_param(self, param, data, default=False):
 113         return data.get('form', {}).get(param, default)
 114
 115     ####################Account and account line filter helper #################
 116
 117     def sort_accounts_with_structure(self, root_account_ids, account_ids, context=None):
 118         """Sort accounts by code respecting their structure"""
 119
 120         def recursive_sort_by_code(accounts, parent):
 121             sorted_accounts = []
 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:
 131                 return []
 132
 133             level_accounts = sorted(level_accounts, key=lambda a: a['code'])
 134
 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
 139
 140         if not account_ids:
 141             return []
 142
 143         accounts_data = self.pool.get('account.account').read(self.cr, self.uid,
 144                                                          account_ids,
 145                                                          ['id', 'parent_id', 'level', 'code', 'child_consol_ids'],
 146                                                          context=context)
 147
 148         sorted_accounts = []
 149
 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))
 155
 156         # fallback to unsorted accounts when sort failed
 157         # sort fails when the levels are miscalculated by account.account
 158         # check lp:783670
 159         if len(sorted_accounts) != len(account_ids):
 160             _logger.warn('Webkit financial reports: Sort of accounts failed.')
 161             sorted_accounts = account_ids
 162
 163         return sorted_accounts
 164
 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
 167
 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
 171         """
 172         context = context or {}
 173         accounts = []
 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)
 182
 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"
 186             sql_join = ""
 187             sql_where = "WHERE a.id IN %(ids)s"
 188             if exclude_type:
 189                 sql_where += " AND a.type not in %(exclude_type)s"
 190                 sql_filters.update({'exclude_type': tuple(exclude_type)})
 191             if only_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)})
 199
 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:
 204                 return []
 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]
 208         return res_ids
 209
 210     ####################Periods and fiscal years  helper #######################
 211
 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)])
 217
 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]])
 221
 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)],
 231                                   limit=1)
 232
 233     def periods_contains_move_lines(self, period_ids):
 234         if not period_ids:
 235             return False
 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
 240
 241     def _get_period_range_from_periods(self, start_period, stop_period, mode=None):
 242         """
 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
 245         """
 246         period_obj = self.pool.get('account.period')
 247         search_period = [('date_start', '>=', start_period.date_start),
 248                          ('date_stop', '<=', stop_period.date_stop)]
 249
 250         if mode == 'exclude_opening':
 251             search_period += [('special', '=', False)]
 252         res = period_obj.search(self.cursor, self.uid, search_period)
 253         return res
 254
 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
 259         past_limit = []
 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)]
 266             if fiscalyear:
 267                 opening_search.append(('fiscalyear_id', '=', fiscalyear.id))
 268
 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,
 273                                                     self.uid,
 274                                                     [('period_id', '=', opening_period)],
 275                                                     limit=1)
 276                 if validation_res:
 277                     opening_period_id = opening_period
 278                     break
 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)]
 283
 284         periods_search = [('date_stop', '<=', start_period.date_stop)]
 285         periods_search += past_limit
 286
 287         if not include_opening:
 288             periods_search += [('special', '=', False)]
 289
 290         if fiscalyear:
 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)
 298         return periods
 299
 300     def get_first_fiscalyear_period(self, fiscalyear):
 301         return self._get_st_fiscalyear_period(fiscalyear)
 302
 303     def get_last_fiscalyear_period(self, fiscalyear):
 304         return self._get_st_fiscalyear_period(fiscalyear, order='DESC')
 305
 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,
 309                                  self.uid,
 310                                  [('special', '=', special),
 311                                   ('fiscalyear_id', '=', fiscalyear.id)],
 312                                  limit=1,
 313                                  order='date_start %s' % (order,))
 314         if not p_id:
 315             raise osv.except_osv(_('No period found'), '')
 316         return period_obj.browse(self.cursor, self.uid, p_id[0])
 317
 318     ####################Initial Balance helper #################################
 319
 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]
 323         res = {}
 324
 325         if not default_values:
 326             if not account_id or not period_ids:
 327                 raise Exception('Missing account or period_ids')
 328             try:
 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()
 337
 338             except Exception, exc:
 339                 self.cursor.rollback()
 340                 raise
 341
 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,
 346                 'state': mode}
 347
 348     def _read_opening_balance(self, account_ids, start_period):
 349         """ Read opening balances from the opening balance
 350         """
 351         opening_period_selected = self.get_included_opening_period(start_period)
 352         if not opening_period_selected:
 353             raise osv.except_osv(
 354                     _('Error'),
 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.'))
 358
 359         res = {}
 360         for account_id in account_ids:
 361             res[account_id] = self._compute_init_balance(account_id, opening_period_selected, mode='read')
 362         return res
 363
 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
 370         res = {}
 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)
 379
 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)
 387             else:
 388                 res[acc.id] = self._compute_init_balance(acc.id, bs_period_ids)
 389         return res
 390
 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)
 396         if not periods:
 397             return []
 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)
 402
 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)]
 409
 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()
 414             if opening:
 415                 search_period += ['period_id', 'not in', opening]
 416
 417         if target_move == 'posted':
 418             search_period += [('move_id.state', '=', 'posted')]
 419
 420         return move_line_obj.search(self.cursor, self.uid, search_period)
 421
 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'))
 426
 427         if main_filter in ('filter_period', 'filter_no'):
 428             return self._get_move_ids_from_periods(account_id, start, stop, target_move)
 429
 430         elif main_filter == 'filter_date':
 431             return self._get_move_ids_from_dates(account_id, start, stop, target_move)
 432         else:
 433             raise osv.except_osv(_('No valid filter'), _('Please set a valid time filter'))
 434
 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
 441         #
 442         # TODO improve that by making a better domain or if not possible
 443         # by using python sort
 444         if not move_line_ids:
 445             return []
 446         if not isinstance(move_line_ids, list):
 447             move_line_ids = [move_line_ids]
 448         monster = """
 449 SELECT l.id AS id,
 450             l.date AS ldate,
 451             j.code AS jcode ,
 452             j.type AS jtype,
 453             l.currency_id,
 454             l.account_id,
 455             l.amount_currency,
 456             l.ref AS lref,
 457             l.name AS lname,
 458             COALESCE(l.debit, 0.0) - COALESCE(l.credit, 0.0) AS balance,
 459             l.debit,
 460             l.credit,
 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,
 466             m.name AS move_name,
 467             COALESCE(partialrec.name, fullrec.name, '') AS rec_name,
 468             COALESCE(partialrec.id, fullrec.id, NULL) AS rec_id,
 469             m.id AS move_id,
 470             c.name AS currency_code,
 471             i.id AS invoice_id,
 472             i.type AS invoice_type,
 473             i.number AS invoice_number,
 474             l.date_maturity
 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)
 484     WHERE l.id in %s"""
 485         monster += (" ORDER BY %s" % (order,))
 486         try:
 487             self.cursor.execute(monster, (tuple(move_line_ids),))
 488             res = self.cursor.dictfetchall()
 489         except Exception, exc:
 490             self.cursor.rollback()
 491             raise
 492         return res or []
 493
 494     def _get_moves_counterparts(self, move_ids, account_id, limit=3):
 495         if not move_ids:
 496             return {}
 497         if not isinstance(move_ids, list):
 498             move_ids = [move_ids]
 499         sql = """
 500 SELECT account_move.id,
 501        array_to_string(
 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) , ', ')
 507
 508 FROM account_move
 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"""
 512
 513         try:
 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()
 518             raise
 519         return res and dict(res) or {}
 520
 521     def is_initial_balance_enabled(self, main_filter):
 522         if main_filter not in ('filter_no', 'filter_year', 'filter_period'):
 523             return False
 524         return True
 525
 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'
 531         else:
 532             return 'initial_balance'