addons/account/report/account_aged_partner_balance.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
   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
  22 import time
  23 from openerp.osv import osv
  24 from openerp.report import report_sxw
  25 from common_report_header import common_report_header
  26
  27
  28 class aged_trial_report(report_sxw.rml_parse, common_report_header):
  29
  30     def __init__(self, cr, uid, name, context):
  31         super(aged_trial_report, self).__init__(cr, uid, name, context=context)
  32         self.total_account = []
  33         self.localcontext.update({
  34             'time': time,
  35             'get_lines_with_out_partner': self._get_lines_with_out_partner,
  36             'get_lines': self._get_lines,
  37             'get_total': self._get_total,
  38             'get_direction': self._get_direction,
  39             'get_for_period': self._get_for_period,
  40             'get_company': self._get_company,
  41             'get_currency': self._get_currency,
  42             'get_partners':self._get_partners,
  43             'get_account': self._get_account,
  44             'get_fiscalyear': self._get_fiscalyear,
  45             'get_target_move': self._get_target_move,
  46         })
  47
  48     def set_context(self, objects, data, ids, report_type=None):
  49         obj_move = self.pool.get('account.move.line')
  50         ctx = data['form'].get('used_context', {})
  51         ctx.update({'fiscalyear': False, 'all_fiscalyear': True})
  52         self.query = obj_move._query_get(self.cr, self.uid, obj='l', context=ctx)
  53         self.direction_selection = data['form'].get('direction_selection', 'past')
  54         self.target_move = data['form'].get('target_move', 'all')
  55         self.date_from = data['form'].get('date_from', time.strftime('%Y-%m-%d'))
  56         if (data['form']['result_selection'] == 'customer' ):
  57             self.ACCOUNT_TYPE = ['receivable']
  58         elif (data['form']['result_selection'] == 'supplier'):
  59             self.ACCOUNT_TYPE = ['payable']
  60         else:
  61             self.ACCOUNT_TYPE = ['payable','receivable']
  62         return super(aged_trial_report, self).set_context(objects, data, ids, report_type=report_type)
  63
  64     def _get_lines(self, form):
  65         res = []
  66         move_state = ['draft','posted']
  67         if self.target_move == 'posted':
  68             move_state = ['posted']
  69         self.cr.execute('SELECT DISTINCT res_partner.id AS id,\
  70                     res_partner.name AS name \
  71                 FROM res_partner,account_move_line AS l, account_account, account_move am\
  72                 WHERE (l.account_id=account_account.id) \
  73                     AND (l.move_id=am.id) \
  74                     AND (am.state IN %s)\
  75                     AND (account_account.type IN %s)\
  76                     AND account_account.active\
  77                     AND ((reconcile_id IS NULL)\
  78                        OR (reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
  79                     AND (l.partner_id=res_partner.id)\
  80                     AND (l.date <= %s)\
  81                     AND ' + self.query + ' \
  82                 ORDER BY res_partner.name', (tuple(move_state), tuple(self.ACCOUNT_TYPE), self.date_from, self.date_from,))
  83         partners = self.cr.dictfetchall()
  84         ## mise a 0 du total
  85         for i in range(7):
  86             self.total_account.append(0)
  87         #
  88         # Build a string like (1,2,3) for easy use in SQL query
  89         partner_ids = [x['id'] for x in partners]
  90         if not partner_ids:
  91             return []
  92         # This dictionary will store the debit-credit for all partners, using partner_id as key.
  93
  94         totals = {}
  95         self.cr.execute('SELECT l.partner_id, SUM(l.debit-l.credit) \
  96                     FROM account_move_line AS l, account_account, account_move am \
  97                     WHERE (l.account_id = account_account.id) AND (l.move_id=am.id) \
  98                     AND (am.state IN %s)\
  99                     AND (account_account.type IN %s)\
 100                     AND (l.partner_id IN %s)\
 101                     AND ((l.reconcile_id IS NULL)\
 102                     OR (l.reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
 103                     AND ' + self.query + '\
 104                     AND account_account.active\
 105                     AND (l.date <= %s)\
 106                     GROUP BY l.partner_id ', (tuple(move_state), tuple(self.ACCOUNT_TYPE), tuple(partner_ids), self.date_from, self.date_from,))
 107         t = self.cr.fetchall()
 108         for i in t:
 109             totals[i[0]] = i[1]
 110
 111         # This dictionary will store the future or past of all partners
 112         future_past = {}
 113         if self.direction_selection == 'future':
 114             self.cr.execute('SELECT l.partner_id, SUM(l.debit-l.credit) \
 115                         FROM account_move_line AS l, account_account, account_move am \
 116                         WHERE (l.account_id=account_account.id) AND (l.move_id=am.id) \
 117                         AND (am.state IN %s)\
 118                         AND (account_account.type IN %s)\
 119                         AND (COALESCE(l.date_maturity, l.date) < %s)\
 120                         AND (l.partner_id IN %s)\
 121                         AND ((l.reconcile_id IS NULL)\
 122                         OR (l.reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
 123                         AND '+ self.query + '\
 124                         AND account_account.active\
 125                     AND (l.date <= %s)\
 126                         GROUP BY l.partner_id', (tuple(move_state), tuple(self.ACCOUNT_TYPE), self.date_from, tuple(partner_ids),self.date_from, self.date_from,))
 127             t = self.cr.fetchall()
 128             for i in t:
 129                 future_past[i[0]] = i[1]
 130         elif self.direction_selection == 'past': # Using elif so people could extend without this breaking
 131             self.cr.execute('SELECT l.partner_id, SUM(l.debit-l.credit) \
 132                     FROM account_move_line AS l, account_account, account_move am \
 133                     WHERE (l.account_id=account_account.id) AND (l.move_id=am.id)\
 134                         AND (am.state IN %s)\
 135                         AND (account_account.type IN %s)\
 136                         AND (COALESCE(l.date_maturity,l.date) > %s)\
 137                         AND (l.partner_id IN %s)\
 138                         AND ((l.reconcile_id IS NULL)\
 139                         OR (l.reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
 140                         AND '+ self.query + '\
 141                         AND account_account.active\
 142                     AND (l.date <= %s)\
 143                         GROUP BY l.partner_id', (tuple(move_state), tuple(self.ACCOUNT_TYPE), self.date_from, tuple(partner_ids), self.date_from, self.date_from,))
 144             t = self.cr.fetchall()
 145             for i in t:
 146                 future_past[i[0]] = i[1]
 147
 148         # Use one query per period and store results in history (a list variable)
 149         # Each history will contain: history[1] = {'<partner_id>': <partner_debit-credit>}
 150         history = []
 151         for i in range(5):
 152             args_list = (tuple(move_state), tuple(self.ACCOUNT_TYPE), tuple(partner_ids),self.date_from,)
 153             dates_query = '(COALESCE(l.date_maturity,l.date)'
 154             if form[str(i)]['start'] and form[str(i)]['stop']:
 155                 dates_query += ' BETWEEN %s AND %s)'
 156                 args_list += (form[str(i)]['start'], form[str(i)]['stop'])
 157             elif form[str(i)]['start']:
 158                 dates_query += ' > %s)'
 159                 args_list += (form[str(i)]['start'],)
 160             else:
 161                 dates_query += ' < %s)'
 162                 args_list += (form[str(i)]['stop'],)
 163             args_list += (self.date_from,)
 164             self.cr.execute('''SELECT l.partner_id, SUM(l.debit-l.credit), l.reconcile_partial_id
 165                     FROM account_move_line AS l, account_account, account_move am
 166                     WHERE (l.account_id = account_account.id) AND (l.move_id=am.id)
 167                         AND (am.state IN %s)
 168                         AND (account_account.type IN %s)
 169                         AND (l.partner_id IN %s)
 170                         AND ((l.reconcile_id IS NULL)
 171                           OR (l.reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))
 172                         AND ''' + self.query + '''
 173                         AND account_account.active
 174                         AND ''' + dates_query + '''
 175                     AND (l.date <= %s)
 176                     GROUP BY l.partner_id, l.reconcile_partial_id''', args_list)
 177             partners_partial = self.cr.fetchall()
 178             partners_amount = dict((i[0],0) for i in partners_partial)
 179             for partner_info in partners_partial:
 180                 if partner_info[2]:
 181                     # in case of partial reconciliation, we want to keep the left amount in the oldest period
 182                     self.cr.execute('''SELECT MIN(COALESCE(date_maturity,date)) FROM account_move_line WHERE reconcile_partial_id = %s''', (partner_info[2],))
 183                     date = self.cr.fetchall()
 184                     if date and args_list[-3] <= date[0][0] <= args_list[-2]:
 185                         # partial reconcilation
 186                         self.cr.execute('''SELECT SUM(l.debit-l.credit)
 187                                            FROM account_move_line AS l
 188                                            WHERE l.reconcile_partial_id = %s''', (partner_info[2],))
 189                         unreconciled_amount = self.cr.fetchall()
 190                         partners_amount[partner_info[0]] += unreconciled_amount[0][0]
 191                 else:
 192                     partners_amount[partner_info[0]] += partner_info[1]
 193             history.append(partners_amount)
 194
 195         for partner in partners:
 196             values = {}
 197             ## If choise selection is in the future
 198             if self.direction_selection == 'future':
 199                 # Query here is replaced by one query which gets the all the partners their 'before' value
 200                 before = False
 201                 if future_past.has_key(partner['id']):
 202                     before = [ future_past[partner['id']] ]
 203                 self.total_account[6] = self.total_account[6] + (before and before[0] or 0.0)
 204                 values['direction'] = before and before[0] or 0.0
 205             elif self.direction_selection == 'past': # Changed this so people could in the future create new direction_selections
 206                 # Query here is replaced by one query which gets the all the partners their 'after' value
 207                 after = False
 208                 if future_past.has_key(partner['id']): # Making sure this partner actually was found by the query
 209                     after = [ future_past[partner['id']] ]
 210
 211                 self.total_account[6] = self.total_account[6] + (after and after[0] or 0.0)
 212                 values['direction'] = after and after[0] or 0.0
 213
 214             for i in range(5):
 215                 during = False
 216                 if history[i].has_key(partner['id']):
 217                     during = [ history[i][partner['id']] ]
 218                 # Ajout du compteur
 219                 self.total_account[(i)] = self.total_account[(i)] + (during and during[0] or 0)
 220                 values[str(i)] = during and during[0] or 0.0
 221             total = False
 222             if totals.has_key( partner['id'] ):
 223                 total = [ totals[partner['id']] ]
 224             values['total'] = total and total[0] or 0.0
 225             ## Add for total
 226             self.total_account[(i+1)] = self.total_account[(i+1)] + (total and total[0] or 0.0)
 227             values['name'] = partner['name']
 228
 229             res.append(values)
 230
 231         total = 0.0
 232         totals = {}
 233         for r in res:
 234             total += float(r['total'] or 0.0)
 235             for i in range(5)+['direction']:
 236                 totals.setdefault(str(i), 0.0)
 237                 totals[str(i)] += float(r[str(i)] or 0.0)
 238         return res
 239
 240     def _get_lines_with_out_partner(self, form):
 241         res = []
 242         move_state = ['draft','posted']
 243         if self.target_move == 'posted':
 244             move_state = ['posted']
 245
 246         ## mise a 0 du total
 247         for i in range(7):
 248             self.total_account.append(0)
 249         totals = {}
 250         self.cr.execute('SELECT SUM(l.debit-l.credit) \
 251                     FROM account_move_line AS l, account_account, account_move am \
 252                     WHERE (l.account_id = account_account.id) AND (l.move_id=am.id)\
 253                     AND (am.state IN %s)\
 254                     AND (l.partner_id IS NULL)\
 255                     AND (account_account.type IN %s)\
 256                     AND ((l.reconcile_id IS NULL) \
 257                     OR (l.reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
 258                     AND ' + self.query + '\
 259                     AND (l.date <= %s)\
 260                     AND account_account.active ',(tuple(move_state), tuple(self.ACCOUNT_TYPE), self.date_from, self.date_from,))
 261         t = self.cr.fetchall()
 262         for i in t:
 263             totals['Unknown Partner'] = i[0]
 264         future_past = {}
 265         if self.direction_selection == 'future':
 266             self.cr.execute('SELECT SUM(l.debit-l.credit) \
 267                         FROM account_move_line AS l, account_account, account_move am\
 268                         WHERE (l.account_id=account_account.id) AND (l.move_id=am.id)\
 269                         AND (am.state IN %s)\
 270                         AND (l.partner_id IS NULL)\
 271                         AND (account_account.type IN %s)\
 272                         AND (COALESCE(l.date_maturity, l.date) < %s)\
 273                         AND ((l.reconcile_id IS NULL)\
 274                         OR (l.reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
 275                         AND '+ self.query + '\
 276                         AND account_account.active ', (tuple(move_state), tuple(self.ACCOUNT_TYPE), self.date_from, self.date_from))
 277             t = self.cr.fetchall()
 278             for i in t:
 279                 future_past['Unknown Partner'] = i[0]
 280         elif self.direction_selection == 'past': # Using elif so people could extend without this breaking
 281             self.cr.execute('SELECT SUM(l.debit-l.credit) \
 282                     FROM account_move_line AS l, account_account, account_move am \
 283                     WHERE (l.account_id=account_account.id) AND (l.move_id=am.id)\
 284                         AND (am.state IN %s)\
 285                         AND (l.partner_id IS NULL)\
 286                         AND (account_account.type IN %s)\
 287                         AND (COALESCE(l.date_maturity,l.date) > %s)\
 288                         AND ((l.reconcile_id IS NULL)\
 289                         OR (l.reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
 290                         AND '+ self.query + '\
 291                         AND account_account.active ', (tuple(move_state), tuple(self.ACCOUNT_TYPE), self.date_from, self.date_from))
 292             t = self.cr.fetchall()
 293             for i in t:
 294                 future_past['Unknown Partner'] = i[0]
 295         history = []
 296
 297         for i in range(5):
 298             args_list = (tuple(move_state), tuple(self.ACCOUNT_TYPE), self.date_from,)
 299             dates_query = '(COALESCE(l.date_maturity,l.date)'
 300             if form[str(i)]['start'] and form[str(i)]['stop']:
 301                 dates_query += ' BETWEEN %s AND %s)'
 302                 args_list += (form[str(i)]['start'], form[str(i)]['stop'])
 303             elif form[str(i)]['start']:
 304                 dates_query += ' > %s)'
 305                 args_list += (form[str(i)]['start'],)
 306             else:
 307                 dates_query += ' < %s)'
 308                 args_list += (form[str(i)]['stop'],)
 309             args_list += (self.date_from,)
 310             self.cr.execute('SELECT SUM(l.debit-l.credit)\
 311                     FROM account_move_line AS l, account_account, account_move am \
 312                     WHERE (l.account_id = account_account.id) AND (l.move_id=am.id)\
 313                         AND (am.state IN %s)\
 314                         AND (account_account.type IN %s)\
 315                         AND (l.partner_id IS NULL)\
 316                         AND ((l.reconcile_id IS NULL)\
 317                         OR (l.reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
 318                         AND '+ self.query + '\
 319                         AND account_account.active\
 320                         AND ' + dates_query + '\
 321                     AND (l.date <= %s)\
 322                     GROUP BY l.partner_id', args_list)
 323             t = self.cr.fetchall()
 324             d = {}
 325             for i in t:
 326                 d['Unknown Partner'] = i[0]
 327             history.append(d)
 328
 329         values = {}
 330         if self.direction_selection == 'future':
 331             before = False
 332             if future_past.has_key('Unknown Partner'):
 333                 before = [ future_past['Unknown Partner'] ]
 334             self.total_account[6] = self.total_account[6] + (before and before[0] or 0.0)
 335             values['direction'] = before and before[0] or 0.0
 336         elif self.direction_selection == 'past':
 337             after = False
 338             if future_past.has_key('Unknown Partner'):
 339                 after = [ future_past['Unknown Partner'] ]
 340             self.total_account[6] = self.total_account[6] + (after and after[0] or 0.0)
 341             values['direction'] = after and after[0] or 0.0
 342
 343         for i in range(5):
 344             during = False
 345             if history[i].has_key('Unknown Partner'):
 346                 during = [ history[i]['Unknown Partner'] ]
 347             self.total_account[(i)] = self.total_account[(i)] + (during and during[0] or 0)
 348             values[str(i)] = during and during[0] or 0.0
 349
 350         total = False
 351         if totals.has_key( 'Unknown Partner' ):
 352             total = [ totals['Unknown Partner'] ]
 353         values['total'] = total and total[0] or 0.0
 354         ## Add for total
 355         self.total_account[(i+1)] = self.total_account[(i+1)] + (total and total[0] or 0.0)
 356         values['name'] = 'Unknown Partner'
 357
 358         if values['total']:
 359             res.append(values)
 360
 361         total = 0.0
 362         totals = {}
 363         for r in res:
 364             total += float(r['total'] or 0.0)
 365             for i in range(5)+['direction']:
 366                 totals.setdefault(str(i), 0.0)
 367                 totals[str(i)] += float(r[str(i)] or 0.0)
 368         return res
 369
 370     def _get_total(self,pos):
 371         period = self.total_account[int(pos)]
 372         return period or 0.0
 373
 374     def _get_direction(self,pos):
 375         period = self.total_account[int(pos)]
 376         return period or 0.0
 377
 378     def _get_for_period(self,pos):
 379         period = self.total_account[int(pos)]
 380         return period or 0.0
 381
 382     def _get_partners(self,data):
 383         # TODO: deprecated, to remove in trunk
 384         if data['form']['result_selection'] == 'customer':
 385             return self._translate('Receivable Accounts')
 386         elif data['form']['result_selection'] == 'supplier':
 387             return self._translate('Payable Accounts')
 388         elif data['form']['result_selection'] == 'customer_supplier':
 389             return self._translate('Receivable and Payable Accounts')
 390         return ''
 391
 392
 393 class report_agedpartnerbalance(osv.AbstractModel):
 394     _name = 'report.account.report_agedpartnerbalance'
 395     _inherit = 'report.abstract_report'
 396     _template = 'account.report_agedpartnerbalance'
 397     _wrapped_report_class = aged_trial_report
 398
 399 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: