report/common_partner_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 from collections import defaultdict
  26 from datetime import datetime
  27
  28 from openerp.tools import DEFAULT_SERVER_DATE_FORMAT
  29 from .common_reports import CommonReportHeaderWebkit
  30
  31
  32 class CommonPartnersReportHeaderWebkit(CommonReportHeaderWebkit):
  33     """Define common helper for partner oriented financial report"""
  34
  35     ####################Account move line retrieval helper ##########################
  36     def get_partners_move_lines_ids(self, account_id, main_filter, start, stop, target_move,
  37                                     exclude_reconcile=False,
  38                                     partner_filter=False):
  39         filter_from = False
  40         if main_filter in ('filter_period', 'filter_no'):
  41             filter_from = 'period'
  42         elif main_filter == 'filter_date':
  43             filter_from = 'date'
  44         if filter_from:
  45             return self._get_partners_move_line_ids(filter_from,
  46                                                     account_id,
  47                                                     start,
  48                                                     stop,
  49                                                     target_move,
  50                                                     exclude_reconcile=exclude_reconcile,
  51                                                     partner_filter=partner_filter)
  52
  53     def _get_first_special_period(self):
  54         """
  55         Returns the browse record of the period with the `special` flag, which
  56         is the special period of the first fiscal year used in the accounting.
  57
  58         i.e. it searches the first fiscal year with at least one journal entry,
  59         and it returns the id of the first period for which `special` is True
  60         in this fiscal year.
  61
  62         It is used for example in the partners reports, where we have to include
  63         the first, and only the first opening period.
  64
  65         :return: browse record of the first special period.
  66         """
  67         move_line_obj = self.pool.get('account.move.line')
  68         first_entry_id = move_line_obj.search(
  69             self.cr, self.uid, [], order='date ASC', limit=1)
  70         # it means there is no entry at all, that's unlikely to happen, but
  71         # it may so
  72         if not first_entry_id:
  73             return
  74         first_entry = move_line_obj.browse(self.cr, self.uid, first_entry_id[0])
  75         fiscalyear = first_entry.period_id.fiscalyear_id
  76         special_periods = [period for period in fiscalyear.period_ids if period.special]
  77         # so, we have no opening period on the first year, nothing to return
  78         if not special_periods:
  79             return
  80         return min(special_periods,
  81                    key=lambda p: datetime.strptime(p.date_start, DEFAULT_SERVER_DATE_FORMAT))
  82
  83     def _get_period_range_from_start_period(self, start_period, include_opening=False,
  84                                             fiscalyear=False,
  85                                             stop_at_previous_opening=False):
  86         """We retrieve all periods before start period"""
  87         periods = super(CommonPartnersReportHeaderWebkit, self).\
  88                 _get_period_range_from_start_period(
  89                         start_period,
  90                         include_opening=include_opening,
  91                         fiscalyear=fiscalyear,
  92                         stop_at_previous_opening=stop_at_previous_opening)
  93         first_special = self._get_first_special_period()
  94         if first_special and first_special.id not in periods:
  95             periods.append(first_special.id)
  96         return periods
  97
  98     def _get_query_params_from_periods(self, period_start, period_stop, mode='exclude_opening'):
  99         """
 100         Build the part of the sql "where clause" which filters on the selected
 101         periods.
 102
 103         :param browse_record period_start: first period of the report to print
 104         :param browse_record period_stop: last period of the report to print
 105         :param str mode: deprecated
 106         """
 107         # we do not want opening period so we exclude opening
 108         periods = self.pool.get('account.period').build_ctx_periods(
 109                 self.cr, self.uid, period_start.id, period_stop.id)
 110         if not periods:
 111             return []
 112
 113         if mode != 'include_opening':
 114             periods = self.exclude_opening_periods(periods)
 115
 116         search_params = {'period_ids': tuple(periods),
 117                          'date_stop': period_stop.date_stop}
 118
 119         sql_conditions = ""
 120         if periods:
 121             sql_conditions = "  AND account_move_line.period_id in %(period_ids)s"
 122
 123         return sql_conditions, search_params
 124
 125     def _get_query_params_from_dates(self, date_start, date_stop, **args):
 126         """
 127         Build the part of the sql where clause based on the dates to print.
 128
 129         :param str date_start: start date of the report to print
 130         :param str date_stop: end date of the report to print
 131         """
 132
 133         periods = self._get_opening_periods()
 134         if not periods:
 135             periods = (-1,)
 136
 137         search_params = {'period_ids': tuple(periods),
 138                          'date_start': date_start,
 139                          'date_stop': date_stop}
 140
 141         sql_conditions = "  AND account_move_line.period_id not in %(period_ids)s" \
 142                          "  AND account_move_line.date between date(%(date_start)s) and date((%(date_stop)s))"
 143
 144         return sql_conditions, search_params
 145
 146     def _get_partners_move_line_ids(self, filter_from, account_id, start, stop,
 147                                     target_move, opening_mode='exclude_opening',
 148                                     exclude_reconcile=False, partner_filter=None):
 149         """
 150
 151         :param str filter_from: "periods" or "dates"
 152         :param int account_id: id of the account where to search move lines
 153         :param str or browse_record start: start date or start period
 154         :param str or browse_record stop: stop date or stop period
 155         :param str target_move: 'posted' or 'all'
 156         :param opening_mode: deprecated
 157         :param boolean exclude_reconcile: wether the reconciled entries are
 158             filtred or not
 159         :param list partner_filter: list of partner ids, will filter on their
 160             move lines
 161         """
 162
 163         final_res = defaultdict(list)
 164
 165         sql_select = "SELECT account_move_line.id, account_move_line.partner_id FROM account_move_line"
 166         sql_joins = ''
 167         sql_where = " WHERE account_move_line.account_id = %(account_ids)s " \
 168                     " AND account_move_line.state = 'valid' "
 169
 170         method = getattr(self, '_get_query_params_from_' + filter_from + 's')
 171         sql_conditions, search_params = method(start, stop)
 172
 173         sql_where += sql_conditions
 174
 175         if exclude_reconcile:
 176             sql_where += ("  AND ((account_move_line.reconcile_id IS NULL)"
 177                          "   OR (account_move_line.reconcile_id IS NOT NULL AND account_move_line.last_rec_date > date(%(date_stop)s)))")
 178
 179         if partner_filter:
 180             sql_where += "   AND account_move_line.partner_id in %(partner_ids)s"
 181
 182         if target_move == 'posted':
 183             sql_joins += "INNER JOIN account_move ON account_move_line.move_id = account_move.id"
 184             sql_where += " AND account_move.state = %(target_move)s"
 185             search_params.update({'target_move': target_move})
 186
 187         search_params.update({
 188             'account_ids': account_id,
 189             'partner_ids': tuple(partner_filter),
 190         })
 191
 192         sql = ' '.join((sql_select, sql_joins, sql_where))
 193         self.cursor.execute(sql, search_params)
 194         res = self.cursor.dictfetchall()
 195         if res:
 196             for row in res:
 197                 final_res[row['partner_id']].append(row['id'])
 198         return final_res
 199
 200     def _get_clearance_move_line_ids(self, move_line_ids, date_stop, date_until):
 201         if not move_line_ids:
 202             return []
 203         move_line_obj = self.pool.get('account.move.line')
 204         # we do not use orm in order to gain perfo
 205         # In this case I have to test the effective gain over an itteration
 206         # Actually ORM does not allows distinct behavior
 207         sql = "Select distinct reconcile_id from account_move_line where id in %s"
 208         self.cursor.execute(sql, (tuple(move_line_ids),))
 209         rec_ids = self.cursor.fetchall()
 210         if rec_ids:
 211             rec_ids = [x[0] for x in rec_ids]
 212             l_ids = move_line_obj.search(self.cursor,
 213                                          self.uid,
 214                                          [('reconcile_id', 'in', rec_ids),
 215                                           ('date', '>=', date_stop),
 216                                           ('date', '<=', date_until)])
 217             return l_ids
 218         else:
 219             return []
 220
 221      ####################Initial Partner Balance helper ########################
 222
 223     def _tree_move_line_ids(self, move_lines_data, key=None):
 224         """
 225         move_lines_data must be a list of dict which contains at least keys :
 226          - account_id
 227          - partner_id
 228          - other keys with values of the line
 229          - if param key is defined, only this key will be inserted in the tree
 230          returns a tree like
 231          res[account_id.1][partner_id.1][move_line.1,
 232                                          move_line.2]
 233                           [partner_id.2][move_line.3]
 234          res[account_id.2][partner_id.1][move_line.4]
 235         """
 236         res = defaultdict(dict)
 237         for row in move_lines_data[:]:
 238             account_id = row.pop('account_id')
 239             partner_id = row.pop('partner_id')
 240             if key:
 241                 res[account_id].setdefault(partner_id, []).append(row[key])
 242             else:
 243                 res[account_id][partner_id] = row
 244         return res
 245
 246     def _partners_initial_balance_line_ids(self, account_ids, start_period, partner_filter, exclude_reconcile=False, force_period_ids=False, date_stop=None):
 247         # take ALL previous periods
 248         period_ids = force_period_ids \
 249                      if force_period_ids \
 250                      else self._get_period_range_from_start_period(start_period, fiscalyear=False, include_opening=False)
 251
 252         if not period_ids:
 253             period_ids = [-1]
 254         search_param = {
 255                 'date_start': start_period.date_start,
 256                 'period_ids': tuple(period_ids),
 257                 'account_ids': tuple(account_ids),
 258             }
 259         sql = ("SELECT ml.id, ml.account_id, ml.partner_id "
 260                "FROM account_move_line ml "
 261                "INNER JOIN account_account a "
 262                "ON a.id = ml.account_id "
 263                "WHERE ml.period_id in %(period_ids)s "
 264                "AND ml.account_id in %(account_ids)s ")
 265         if exclude_reconcile:
 266             if not date_stop:
 267                 raise Exception("Missing \"date_stop\" to compute the open invoices.")
 268             search_param.update({'date_stop': date_stop})
 269             sql += ("AND ((ml.reconcile_id IS NULL)"
 270                    "OR (ml.reconcile_id IS NOT NULL AND ml.last_rec_date > date(%(date_stop)s))) ")
 271         if partner_filter:
 272             sql += "AND ml.partner_id in %(partner_ids)s "
 273             search_param.update({'partner_ids': tuple(partner_filter)})
 274
 275         self.cursor.execute(sql, search_param)
 276         return self.cursor.dictfetchall()
 277
 278     def _compute_partners_initial_balances(self, account_ids, start_period, partner_filter=None, exclude_reconcile=False, force_period_ids=False):
 279         """We compute initial balance.
 280         If form is filtered by date all initial balance are equal to 0
 281         This function will sum pear and apple in currency amount if account as no secondary currency"""
 282         if isinstance(account_ids, (int, long)):
 283             account_ids = [account_ids]
 284         move_line_ids = self._partners_initial_balance_line_ids(account_ids, start_period, partner_filter,
 285                                                                 exclude_reconcile=exclude_reconcile,
 286                                                                 force_period_ids=force_period_ids)
 287         if not move_line_ids:
 288             move_line_ids = [{'id': -1}]
 289         sql = ("SELECT ml.account_id, ml.partner_id,"
 290                "       sum(ml.debit) as debit, sum(ml.credit) as credit,"
 291                "       sum(ml.debit-ml.credit) as init_balance,"
 292                "       CASE WHEN a.currency_id ISNULL THEN 0.0 ELSE sum(ml.amount_currency) END as init_balance_currency, "
 293                "       c.name as currency_name "
 294                "FROM account_move_line ml "
 295                "INNER JOIN account_account a "
 296                "ON a.id = ml.account_id "
 297                "LEFT JOIN res_currency c "
 298                "ON c.id = a.currency_id "
 299                "WHERE ml.id in %(move_line_ids)s "
 300                "GROUP BY ml.account_id, ml.partner_id, a.currency_id, c.name")
 301         search_param = {'move_line_ids': tuple([move_line['id'] for move_line in move_line_ids])}
 302         self.cursor.execute(sql, search_param)
 303         res = self.cursor.dictfetchall()
 304         return self._tree_move_line_ids(res)
 305
 306     ####################Partner specific helper ################################
 307     def _order_partners(self, *args):
 308         """We get the partner linked to all current accounts that are used.
 309             We also use ensure that partner are ordered by name
 310             args must be list"""
 311         res = []
 312         partner_ids = []
 313         for arg in args:
 314             if arg:
 315                 partner_ids += arg
 316         if not partner_ids:
 317             return []
 318
 319         existing_partner_ids = [partner_id for partner_id in partner_ids if partner_id]
 320         if existing_partner_ids:
 321             # We may use orm here as the performance optimization is not that big
 322             sql = ("SELECT name|| ' ' ||CASE WHEN ref IS NOT NULL THEN '('||ref||')' ELSE '' END, id, ref, name"
 323                    "  FROM res_partner WHERE id IN %s ORDER BY LOWER(name), ref")
 324             self.cursor.execute(sql, (tuple(set(existing_partner_ids)),))
 325             res = self.cursor.fetchall()
 326
 327         # move lines without partners, set None for empty partner
 328         if not all(partner_ids):
 329             res.append((None, None, None, None))
 330
 331         if not res:
 332             return []
 333         return res