report/common_partner_balance_reports.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    Author: 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
  23 from collections import defaultdict
  24 from operator import add
  25
  26 from .common_balance_reports import CommonBalanceReportHeaderWebkit
  27 from .common_partner_reports import CommonPartnersReportHeaderWebkit
  28
  29
  30 class CommonPartnerBalanceReportHeaderWebkit(CommonBalanceReportHeaderWebkit, CommonPartnersReportHeaderWebkit):
  31     """Define common helper for balance (trial balance, P&L, BS oriented financial report"""
  32
  33     def _get_account_partners_details(self, account_by_ids, main_filter, target_move, start,
  34                                       stop, initial_balance_mode, partner_filter_ids=False):
  35         res = {}
  36         filter_from = False
  37         if main_filter in ('filter_period', 'filter_no', 'filter_opening'):
  38             filter_from = 'period'
  39         elif main_filter == 'filter_date':
  40             filter_from = 'date'
  41
  42         partners_init_balances_by_ids = {}
  43         for account_id, account_details in account_by_ids.iteritems():
  44             partners_init_balances_by_ids.update(self._get_partners_initial_balances(account_id,
  45                                                                                      start,
  46                                                                                      initial_balance_mode,
  47                                                                                      partner_filter_ids=partner_filter_ids,
  48                                                                                      exclude_reconcile=False))  # we'll never exclude reconciled entries in the legal reports
  49             opening_mode = 'exclude_opening'
  50             if main_filter == 'filter_opening':
  51                 opening_mode = 'include_opening'
  52             # get credit and debit for partner
  53             details = self._get_partners_totals_account(filter_from,
  54                                                         account_id,
  55                                                         start,
  56                                                         stop,
  57                                                         target_move,
  58                                                         partner_filter_ids=partner_filter_ids,
  59                                                         mode=opening_mode)
  60
  61             # merge initial balances in partner details
  62             if partners_init_balances_by_ids.get(account_id):
  63                 for partner_id, initial_balances in partners_init_balances_by_ids[account_id].iteritems():
  64                     if initial_balances.get('init_balance'):
  65                         details[partner_id].update({'init_balance': initial_balances['init_balance']})
  66
  67             # compute balance for the partner
  68             for partner_id, partner_details in details.iteritems():
  69                 details[partner_id]['balance'] = details[partner_id].get('init_balance', 0.0) +\
  70                                                  details[partner_id].get('debit', 0.0) -\
  71                                                  details[partner_id].get('credit', 0.0)
  72             res[account_id] = details
  73
  74         return res
  75
  76     def _get_partners_initial_balances(self, account_ids, start_period, initial_balance_mode, partner_filter_ids=None, exclude_reconcile=False):
  77         # we get the initial balance from the opening period (opening_balance) when the opening period is included in the start period and
  78         # when there is at least one entry in the opening period. Otherwise we compute it from previous periods
  79         if initial_balance_mode == 'opening_balance':
  80             opening_period_selected = self.get_included_opening_period(start_period)
  81             res = self._compute_partners_initial_balances(account_ids, start_period, partner_filter_ids, force_period_ids=opening_period_selected, exclude_reconcile=exclude_reconcile)
  82         elif initial_balance_mode == 'initial_balance':
  83             res = self._compute_partners_initial_balances(account_ids, start_period, partner_filter_ids, exclude_reconcile=exclude_reconcile)
  84         else:
  85             res = {}
  86         return res
  87
  88     def _get_partners_totals_account(self, filter_from, account_id, start, stop, target_move, partner_filter_ids=None, mode='exclude_opening'):
  89         final_res = defaultdict(dict)
  90
  91         sql_select = """
  92                  SELECT account_move_line.partner_id,
  93                         sum(account_move_line.debit) AS debit,
  94                         sum(account_move_line.credit) AS credit
  95                  FROM account_move_line"""
  96         sql_joins = ''
  97         sql_where = "WHERE account_move_line.account_id = %(account_id)s AND account_move_line.state = 'valid' "
  98         method = getattr(self, '_get_query_params_from_' + filter_from + 's')
  99         sql_conditions, search_params = method(start, stop, mode=mode)
 100         sql_where += sql_conditions
 101
 102         if partner_filter_ids:
 103             sql_where += "   AND account_move_line.partner_id in %(partner_ids)s"
 104             search_params.update({'partner_ids': tuple(partner_filter_ids)})
 105
 106         if target_move == 'posted':
 107             sql_joins += "INNER JOIN account_move ON account_move_line.move_id = account_move.id"
 108             sql_where += " AND account_move.state = %(target_move)s"
 109             search_params.update({'target_move': target_move})
 110
 111         sql_groupby = "GROUP BY account_move_line.partner_id"
 112
 113         search_params.update({'account_id': account_id})
 114         query = ' '.join((sql_select, sql_joins, sql_where, sql_groupby))
 115
 116         self.cursor.execute(query, search_params)
 117         res = self.cursor.dictfetchall()
 118         if res:
 119             for row in res:
 120                 final_res[row['partner_id']] = row
 121         return final_res
 122
 123     def _get_filter_type(self, result_selection):
 124         filter_type = ('payable', 'receivable')
 125         if result_selection == 'customer':
 126             filter_type = ('receivable',)
 127         if result_selection == 'supplier':
 128             filter_type = ('payable',)
 129         return filter_type
 130
 131     def _get_partners_comparison_details(self, data, account_ids, target_move, comparison_filter, index, partner_filter_ids=False):
 132         """
 133
 134         @param data: data of the wizard form
 135         @param account_ids: ids of the accounts to get details
 136         @param comparison_filter: selected filter on the form for the comparison (filter_no, filter_year, filter_period, filter_date)
 137         @param index: index of the fields to get (ie. comp1_fiscalyear_id where 1 is the index)
 138         @param partner_filter_ids: list of ids of partners to select
 139         @return: dict of account details (key = account id)
 140         """
 141         fiscalyear = self._get_info(data, "comp%s_fiscalyear_id" % (index,), 'account.fiscalyear')
 142         start_period = self._get_info(data, "comp%s_period_from" % (index,), 'account.period')
 143         stop_period = self._get_info(data, "comp%s_period_to" % (index,), 'account.period')
 144         start_date = self._get_form_param("comp%s_date_from" % (index,), data)
 145         stop_date = self._get_form_param("comp%s_date_to" % (index,), data)
 146         init_balance = self.is_initial_balance_enabled(comparison_filter)
 147
 148         comp_params = {}
 149         accounts_details_by_ids = defaultdict(dict)
 150         if comparison_filter != 'filter_no':
 151             start_period, stop_period, start, stop = \
 152                 self._get_start_stop_for_filter(comparison_filter, fiscalyear, start_date, stop_date, start_period, stop_period)
 153             details_filter = comparison_filter
 154             if comparison_filter == 'filter_year':
 155                 details_filter = 'filter_no'
 156
 157             initial_balance_mode = init_balance and self._get_initial_balance_mode(start) or False
 158
 159             accounts_by_ids = self._get_account_details(account_ids, target_move, fiscalyear, details_filter, start, stop, initial_balance_mode)
 160
 161             partner_details_by_ids = self._get_account_partners_details(accounts_by_ids, details_filter,
 162                                                                         target_move, start, stop, initial_balance_mode,
 163                                                                         partner_filter_ids=partner_filter_ids)
 164
 165             for account_id in account_ids:
 166                 accounts_details_by_ids[account_id]['account'] = accounts_by_ids[account_id]
 167                 accounts_details_by_ids[account_id]['partners_amounts'] = partner_details_by_ids[account_id]
 168
 169             comp_params = {
 170                 'comparison_filter': comparison_filter,
 171                 'fiscalyear': fiscalyear,
 172                 'start': start,
 173                 'stop': stop,
 174                 'initial_balance_mode': initial_balance_mode,
 175             }
 176
 177         return accounts_details_by_ids, comp_params
 178
 179     def compute_partner_balance_data(self, data, filter_report_type=None):
 180         new_ids = data['form']['account_ids'] or data['form']['chart_account_id']
 181         max_comparison = self._get_form_param('max_comparison', data, default=0)
 182         main_filter = self._get_form_param('filter', data, default='filter_no')
 183
 184         comp_filters, nb_comparisons, comparison_mode = self._comp_filters(data, max_comparison)
 185
 186         fiscalyear = self.get_fiscalyear_br(data)
 187
 188         start_period = self.get_start_period_br(data)
 189         stop_period = self.get_end_period_br(data)
 190         target_move = self._get_form_param('target_move', data, default='all')
 191         start_date = self._get_form_param('date_from', data)
 192         stop_date = self._get_form_param('date_to', data)
 193         chart_account = self._get_chart_account_id_br(data)
 194         result_selection = self._get_form_param('result_selection', data)
 195         partner_ids = self._get_form_param('partner_ids', data)
 196
 197         filter_type = self._get_filter_type(result_selection)
 198
 199         start_period, stop_period, start, stop = \
 200             self._get_start_stop_for_filter(main_filter, fiscalyear, start_date, stop_date, start_period, stop_period)
 201
 202         initial_balance = self.is_initial_balance_enabled(main_filter)
 203         initial_balance_mode = initial_balance and self._get_initial_balance_mode(start) or False
 204
 205         # Retrieving accounts
 206         account_ids = self.get_all_accounts(new_ids, only_type=filter_type,
 207                                             filter_report_type=filter_report_type)
 208
 209         # get details for each accounts, total of debit / credit / balance
 210         accounts_by_ids = self._get_account_details(account_ids, target_move, fiscalyear, main_filter, start, stop, initial_balance_mode)
 211
 212         partner_details_by_ids = self._get_account_partners_details(accounts_by_ids,
 213                                                                     main_filter,
 214                                                                     target_move,
 215                                                                     start,
 216                                                                     stop,
 217                                                                     initial_balance_mode,
 218                                                                     partner_filter_ids=partner_ids)
 219
 220         comparison_params = []
 221         comp_accounts_by_ids = []
 222         for index in range(max_comparison):
 223             if comp_filters[index] != 'filter_no':
 224                 comparison_result, comp_params = self._get_partners_comparison_details(data, account_ids,
 225                                                                                        target_move, comp_filters[index],
 226                                                                                        index, partner_filter_ids=partner_ids)
 227                 comparison_params.append(comp_params)
 228                 comp_accounts_by_ids.append(comparison_result)
 229         objects = []
 230
 231         for account in self.pool.get('account.account').browse(self.cursor, self.uid, account_ids):
 232             if not account.parent_id:  # hide top level account
 233                 continue
 234             account.debit = accounts_by_ids[account.id]['debit']
 235             account.credit = accounts_by_ids[account.id]['credit']
 236             account.balance = accounts_by_ids[account.id]['balance']
 237             account.init_balance = accounts_by_ids[account.id].get('init_balance', 0.0)
 238             account.partners_amounts = partner_details_by_ids[account.id]
 239             comp_accounts = []
 240             for comp_account_by_id in comp_accounts_by_ids:
 241                 values = comp_account_by_id.get(account.id)
 242
 243                 values['account'].update(self._get_diff(account.balance, values['account'].get('balance', 0.0)))
 244                 comp_accounts.append(values)
 245
 246                 for partner_id, partner_values in values['partners_amounts'].copy().iteritems():
 247                     base_partner_balance = account.partners_amounts[partner_id]['balance'] if \
 248                                            account.partners_amounts.get(partner_id) else 0.0
 249                     partner_values.update(self._get_diff(base_partner_balance,
 250                                                          partner_values.get('balance', 0.0)))
 251                     values['partners_amounts'][partner_id].update(partner_values)
 252
 253             account.comparisons = comp_accounts
 254
 255             all_partner_ids = reduce(add, [comp['partners_amounts'].keys() for comp in comp_accounts],
 256                                      account.partners_amounts.keys())
 257
 258             account.partners_order = self._order_partners(all_partner_ids)
 259
 260             objects.append(account)
 261
 262         context_report_values = {
 263             'fiscalyear': fiscalyear,
 264             'start_date': start_date,
 265             'stop_date': stop_date,
 266             'start_period': start_period,
 267             'stop_period': stop_period,
 268             'chart_account': chart_account,
 269             'comparison_mode': comparison_mode,
 270             'nb_comparison': nb_comparisons,
 271             'comp_params': comparison_params,
 272             'initial_balance_mode': initial_balance_mode,
 273             'compute_diff': self._get_diff,
 274         }
 275
 276         return objects, new_ids, context_report_values