report/aged_partner_balance.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    Author: Nicolas Bessi
   5 #    Copyright 2014 Camptocamp SA
   6 #
   7 #    This program is free software: you can redistribute it and/or modify
   8 #    it under the terms of the GNU Affero General Public License as
   9 #    published by the Free Software Foundation, either version 3 of the
  10 #    License, or (at your option) any later version.
  11 #
  12 #    This program is distributed in the hope that it will be useful,
  13 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 #    GNU Affero General Public License for more details.
  16 #
  17 #    You should have received a copy of the GNU Affero General Public License
  18 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  19 #
  20 ##############################################################################
  21 from __future__ import division
  22 from datetime import datetime
  23
  24 from openerp import pooler
  25 from openerp.tools import DEFAULT_SERVER_DATE_FORMAT
  26 from openerp.tools.translate import _
  27 from .open_invoices import PartnersOpenInvoicesWebkit
  28 from .webkit_parser_header_fix import HeaderFooterTextWebKitParser
  29
  30
  31 def make_ranges(top, offset):
  32     """Return sorted days ranges
  33
  34     :param top: maximum overdue day
  35     :param offset: offset for ranges
  36
  37     :returns: list of sorted ranges tuples in days
  38               eg. [(-100000, 0), (0, offset), (offset, n*offset), ... (top, 100000)]
  39     """
  40     ranges = [(n, min(n + offset, top)) for n in xrange(0, top, offset)]
  41     ranges.insert(0, (-100000000000, 0))
  42     ranges.append((top, 100000000000))
  43     return ranges
  44
  45 #list of overdue ranges
  46 RANGES = make_ranges(120, 30)
  47
  48
  49 def make_ranges_titles():
  50     """Generates title to be used by mako"""
  51     titles = [_('Due')]
  52     titles += [_(u'Overdue ≤ %s d.') % x[1] for x in RANGES[1:-1]]
  53     titles.append(_('Older'))
  54     return titles
  55
  56 #list of overdue ranges title
  57 RANGES_TITLES = make_ranges_titles()
  58 #list of payable journal types
  59 REC_PAY_TYPE = ('purchase', 'sale')
  60 #list of refund payable type
  61 REFUND_TYPE = ('purchase_refund', 'sale_refund')
  62 INV_TYPE = REC_PAY_TYPE + REFUND_TYPE
  63
  64
  65 class AccountAgedTrialBalanceWebkit(PartnersOpenInvoicesWebkit):
  66     """Compute Aged Partner Balance based on result of Open Invoices"""
  67
  68     def __init__(self, cursor, uid, name, context=None):
  69         """Constructor, refer to :class:`openerp.report.report_sxw.rml_parse`"""
  70         super(AccountAgedTrialBalanceWebkit, self).__init__(cursor, uid, name,
  71                                                             context=context)
  72         self.pool = pooler.get_pool(self.cr.dbname)
  73         self.cursor = self.cr
  74         company = self.pool.get('res.users').browse(self.cr, uid, uid,
  75                                                     context=context).company_id
  76
  77         header_report_name = ' - '.join((_('Aged Partner Balance'),
  78                                          company.currency_id.name))
  79
  80         footer_date_time = self.formatLang(str(datetime.today()),
  81                                            date_time=True)
  82
  83         self.localcontext.update({
  84             'cr': cursor,
  85             'uid': uid,
  86             'company': company,
  87             'ranges': self._get_ranges(),
  88             'ranges_titles': self._get_ranges_titles(),
  89             'report_name': _('Aged Partner Balance'),
  90             'additional_args': [
  91                 ('--header-font-name', 'Helvetica'),
  92                 ('--footer-font-name', 'Helvetica'),
  93                 ('--header-font-size', '10'),
  94                 ('--footer-font-size', '6'),
  95                 ('--header-left', header_report_name),
  96                 ('--header-spacing', '2'),
  97                 ('--footer-left', footer_date_time),
  98                 ('--footer-right', ' '.join((_('Page'), '[page]', _('of'), '[topage]'))),
  99                 ('--footer-line',),
 100             ],
 101         })
 102
 103     def _get_ranges(self):
 104         """:returns: :cons:`RANGES`"""
 105         return RANGES
 106
 107     def _get_ranges_titles(self):
 108         """:returns: :cons: `RANGES_TITLES`"""
 109         return RANGES_TITLES
 110
 111     def set_context(self, objects, data, ids, report_type=None):
 112         """Populate aged_lines, aged_balance, aged_percents attributes
 113
 114         on each account browse record that will be used by mako template
 115         The browse record are store in :attr:`objects`
 116
 117         The computation are based on the ledger_lines attribute set on account
 118         contained by :attr:`objects`
 119
 120         :attr:`objects` values were previously set by parent class
 121         :class: `.open_invoices.PartnersOpenInvoicesWebkit`
 122
 123         :returns: parent :class:`.open_invoices.PartnersOpenInvoicesWebkit`
 124                   call to set_context
 125
 126         """
 127         res = super(AccountAgedTrialBalanceWebkit, self).set_context(
 128             objects,
 129             data,
 130             ids,
 131             report_type=report_type
 132         )
 133
 134         for acc in self.objects:
 135             acc.aged_lines = {}
 136             acc.agged_totals = {}
 137             acc.agged_percents = {}
 138             for part_id, partner_lines in acc.ledger_lines.items():
 139                 aged_lines = self.compute_aged_lines(part_id,
 140                                                      partner_lines,
 141                                                      data)
 142                 if aged_lines:
 143                     acc.aged_lines[part_id] = aged_lines
 144             acc.aged_totals = totals = self.compute_totals(acc.aged_lines.values())
 145             acc.aged_percents = self.compute_percents(totals)
 146         #Free some memory
 147         del(acc.ledger_lines)
 148         return res
 149
 150     def compute_aged_lines(self, partner_id, ledger_lines, data):
 151         """Add property aged_lines to accounts browse records
 152
 153         contained in :attr:`objects` for a given partner
 154
 155         :param: partner_id: current partner
 156         :param ledger_lines: generated by parent
 157                  :class:`.open_invoices.PartnersOpenInvoicesWebkit`
 158
 159         :returns: dict of computed aged lines
 160                   eg {'balance': 1000.0,
 161                        'aged_lines': {(90, 120): 0.0, ...}
 162
 163         """
 164         lines_to_age = self.filter_lines(partner_id, ledger_lines)
 165         res = {}
 166         end_date = self._get_end_date(data)
 167         aged_lines = dict.fromkeys(RANGES, 0.0)
 168         reconcile_lookup = self.get_reconcile_count_lookup(lines_to_age)
 169         res['aged_lines'] = aged_lines
 170         for line in lines_to_age:
 171             compute_method = self.get_compute_method(reconcile_lookup,
 172                                                      partner_id,
 173                                                      line)
 174             delay = compute_method(line, end_date, ledger_lines)
 175             classification = self.classify_line(partner_id, delay)
 176             aged_lines[classification] += line['debit'] - line['credit']
 177         self.compute_balance(res, aged_lines)
 178         return res
 179
 180     def _get_end_date(self, data):
 181         """Retrieve end date to be used to compute delay.
 182
 183         :param data: data dict send to report contains form dict
 184
 185         :returns: end date to be used to compute overdue delay
 186
 187         """
 188         end_date = None
 189         date_to = data['form']['date_to']
 190         period_to_id = data['form']['period_to']
 191         fiscal_to_id = data['form']['fiscalyear_id']
 192         if date_to:
 193             end_date = date_to
 194         elif period_to_id:
 195             period_to = self.pool['account.period'].browse(self.cr,
 196                                                            self.uid,
 197                                                            period_to_id)
 198             end_date = period_to.date_stop
 199         elif fiscal_to_id:
 200             fiscal_to = self.pool['account.fiscalyear'].browse(self.cr,
 201                                                                self.uid,
 202                                                                fiscal_to_id)
 203             end_date = fiscal_to.date_stop
 204         else:
 205             raise ValueError('End date and end period not available')
 206         return end_date
 207
 208     def _compute_delay_from_key(self, key, line, end_date):
 209         """Compute overdue delay delta in days for line using attribute in key
 210
 211         delta = end_date - date of key
 212
 213         :param line: current ledger line
 214         :param key: date key to be used to compute delta
 215         :param end_date: end_date computed for wizard data
 216
 217         :returns: delta in days
 218         """
 219         from_date = datetime.strptime(line[key], DEFAULT_SERVER_DATE_FORMAT)
 220         end_date = datetime.strptime(end_date, DEFAULT_SERVER_DATE_FORMAT)
 221         delta = end_date - from_date
 222         return delta.days
 223
 224     def compute_delay_from_maturity(self, line, end_date, ledger_lines):
 225         """Compute overdue delay delta in days for line using attribute in key
 226
 227         delta = end_date - maturity date
 228
 229         :param line: current ledger line
 230         :param end_date: end_date computed for wizard data
 231         :param ledger_lines: generated by parent
 232                  :class:`.open_invoices.PartnersOpenInvoicesWebkit`
 233
 234         :returns: delta in days
 235         """
 236         return self._compute_delay_from_key('date_maturity',
 237                                             line,
 238                                             end_date)
 239
 240     def compute_delay_from_date(self, line, end_date, ledger_lines):
 241         """Compute overdue delay delta in days for line using attribute in key
 242
 243         delta = end_date - date
 244
 245         :param line: current ledger line
 246         :param end_date: end_date computed for wizard data
 247         :param ledger_lines: generated by parent
 248                  :class:`.open_invoices.PartnersOpenInvoicesWebkit`
 249
 250         :returns: delta in days
 251         """
 252         return self._compute_delay_from_key('ldate',
 253                                             line,
 254                                             end_date)
 255
 256     def compute_delay_from_partial_rec(self, line, end_date, ledger_lines):
 257         """Compute overdue delay delta in days for the case where move line
 258
 259         is related to a partial reconcile with more than one reconcile line
 260
 261         :param line: current ledger line
 262         :param end_date: end_date computed for wizard data
 263         :param ledger_lines: generated by parent
 264                  :class:`.open_invoices.PartnersOpenInvoicesWebkit`
 265
 266         :returns: delta in days
 267         """
 268         sale_lines = [x for x in ledger_lines if x['jtype'] in REC_PAY_TYPE and
 269                       line['rec_id'] == x['rec_id']]
 270         refund_lines = [x for x in ledger_lines if x['jtype'] in REFUND_TYPE and
 271                         line['rec_id'] == x['rec_id']]
 272         if len(sale_lines) == 1:
 273             reference_line = sale_lines[0]
 274         elif len(refund_lines) == 1:
 275             reference_line = refund_lines[0]
 276         else:
 277             reference_line = line
 278         key = 'date_maturity' if reference_line.get('date_maturity') else 'ldate'
 279         return self._compute_delay_from_key(key,
 280                                             reference_line,
 281                                             end_date)
 282
 283     def get_compute_method(self, reconcile_lookup, partner_id, line):
 284         """Get the function that should compute the delay for a given line
 285
 286         :param reconcile_lookup: dict of reconcile group by id and count
 287                                  {rec_id: count of line related to reconcile}
 288         :param partner_id: current partner_id
 289         :param line: current ledger line generated by parent
 290                      :class:`.open_invoices.PartnersOpenInvoicesWebkit`
 291
 292         :returns: function bounded to :class:`.AccountAgedTrialBalanceWebkit`
 293
 294         """
 295         if reconcile_lookup.get(line['rec_id'], 0.0) > 1:
 296             return self.compute_delay_from_partial_rec
 297         elif line['jtype'] in INV_TYPE and line.get('date_maturity'):
 298             return self.compute_delay_from_maturity
 299         else:
 300             return self.compute_delay_from_date
 301
 302     def line_is_valid(self, partner_id, line):
 303         """Predicate hook that allows to filter line to be treated
 304
 305         :param partner_id: current partner_id
 306         :param line: current ledger line generated by parent
 307                      :class:`.open_invoices.PartnersOpenInvoicesWebkit`
 308
 309         :returns: boolean True if line is allowed
 310         """
 311         return True
 312
 313     def filter_lines(self, partner_id, lines):
 314         """Filter ledger lines that have to be treated
 315
 316         :param partner_id: current partner_id
 317         :param lines: ledger_lines related to current partner
 318                       and generated by parent
 319                       :class:`.open_invoices.PartnersOpenInvoicesWebkit`
 320
 321         :returns: list of allowed lines
 322
 323         """
 324         return [x for x in lines if self.line_is_valid(partner_id, x)]
 325
 326     def classify_line(self, partner_id, overdue_days):
 327         """Return the overdue range for a given delay
 328
 329         We loop from smaller range to higher
 330         This should be the most effective solution as generaly
 331         customer tend to have one or two month of delay
 332
 333         :param overdue_days: delay in days
 334         :param partner_id: current partner_id
 335
 336         :returns: the correct range in :const:`RANGES`
 337
 338         """
 339         for drange in RANGES:
 340             if overdue_days <= drange[1]:
 341                 return drange
 342         return drange
 343
 344     def compute_balance(self, res, aged_lines):
 345         """Compute the total balance of aged line
 346         for given account"""
 347         res['balance'] = sum(aged_lines.values())
 348
 349     def compute_totals(self, aged_lines):
 350         """Compute the totals for an account
 351
 352         :param aged_lines: dict of aged line taken from the
 353                            property added to account record
 354
 355         :returns: dict of total {'balance':1000.00, (30, 60): 3000,...}
 356
 357         """
 358         totals = {}
 359         totals['balance'] = sum(x.get('balance', 0.0) for
 360                                 x in aged_lines)
 361         aged_ranges = [x.get('aged_lines', {}) for x in aged_lines]
 362         for drange in RANGES:
 363             totals[drange] = sum(x.get(drange, 0.0) for x in aged_ranges)
 364         return totals
 365
 366     def compute_percents(self, totals):
 367         percents = {}
 368         base = totals['balance'] or 1.0
 369         for drange in RANGES:
 370             percents[drange] = (totals[drange] / base) * 100.0
 371         return percents
 372
 373     def get_reconcile_count_lookup(self, lines):
 374         """Compute an lookup dict
 375
 376         It contains has partial reconcile id as key and the count of lines
 377         related to the reconcile id
 378
 379         :param: a list of ledger lines generated by parent
 380                 :class:`.open_invoices.PartnersOpenInvoicesWebkit`
 381
 382         :retuns: lookup dict {ṛec_id: count}
 383
 384         """
 385         # possible bang if l_ids is really long.
 386         # We have the same weakness in common_report ...
 387         # but it seems not really possible for a partner
 388         # So I'll keep that option.
 389         l_ids = tuple(x['id'] for x in lines)
 390         sql = ("SELECT reconcile_partial_id, COUNT(*) FROM account_move_line"
 391                "   WHERE reconcile_partial_id IS NOT NULL"
 392                "   AND id in %s"
 393                "   GROUP BY reconcile_partial_id")
 394         self.cr.execute(sql, (l_ids,))
 395         res = self.cr.fetchall()
 396         return dict((x[0], x[1]) for x in res)
 397
 398 HeaderFooterTextWebKitParser(
 399     'report.account.account_aged_trial_balance_webkit',
 400     'account.account',
 401     'addons/account_financial_report_webkit/report/templates/aged_trial_webkit.mako',
 402     parser=AccountAgedTrialBalanceWebkit,
 403 )