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
  24 import pooler
  25 import rml_parse
  26 from report import report_sxw
  27 from common_report_header import common_report_header
  28
  29 class aged_trial_report(rml_parse.rml_parse, common_report_header):
  30
  31         def __init__(self, cr, uid, name, context):
  32                 super(aged_trial_report, self).__init__(cr, uid, name, context=context)
  33                 self.query_line = ''
  34                 self.total_account = []
  35                 self.localcontext.update({
  36                         'time': time,
  37                         'get_lines': self._get_lines,
  38                         'get_total': self._get_total,
  39                         'get_direction': self._get_direction,
  40                         'get_for_period': self._get_for_period,
  41                         'get_company': self._get_company,
  42                         'get_currency': self._get_currency,
  43                         'get_partners':self._get_partners,
  44                         'get_account': self._get_account,
  45                         'get_fiscalyear': self._get_fiscalyear,
  46                 })
  47
  48         def set_context(self, objects, data, ids, report_type=None):
  49                 self.query = data['form'].get('query_line', '')
  50                 self.direction_selection = data['form'].get('direction_selection', 'past')
  51                 self.date_from = data['form'].get('date_from', time.strftime('%Y-%m-%d'))
  52                 if (data['form']['result_selection'] == 'customer' ):
  53                         self.ACCOUNT_TYPE = ['receivable']
  54                 elif (data['form']['result_selection'] == 'supplier'):
  55                         self.ACCOUNT_TYPE = ['payable']
  56                 else:
  57                         self.ACCOUNT_TYPE = ['payable','receivable']
  58                 return super(aged_trial_report, self).set_context(objects, data, ids, report_type=report_type)
  59
  60         def _get_lines(self, form):
  61                 res = []
  62                 account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
  63                 self.cr.execute('SELECT DISTINCT res_partner.id AS id,\
  64                                         res_partner.name AS name \
  65                                 FROM res_partner,account_move_line AS l, account_account\
  66                                 WHERE (l.account_id=account_account.id)\
  67                                         AND ((reconcile_id IS NULL)\
  68                                         OR (reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
  69                                         AND (l.partner_id=res_partner.id)\
  70                                         AND ' + self.query + ' \
  71                                 ORDER BY res_partner.name' , (self.date_from,))
  72                 partners = self.cr.dictfetchall()
  73                 ## mise a 0 du total
  74                 for i in range(7):
  75                         self.total_account.append(0)
  76                 #
  77                 # Build a string like (1,2,3) for easy use in SQL query
  78                 partner_ids = [x['id'] for x in partners]
  79
  80                 # This dictionary will store the debit-credit for all partners, using partner_id as key.
  81                 totals = {}
  82                 self.cr.execute('SELECT partner_id, SUM(debit-credit) \
  83                                         FROM account_move_line AS l, account_account\
  84                                     WHERE (l.account_id = account_account.id)\
  85                                         AND (account_account.type IN %s)\
  86                                         AND (partner_id IN %s)\
  87                                         AND ((reconcile_id IS NULL)\
  88                                         OR (reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
  89                                         AND ' + self.query + '\
  90                                         AND account_account.active\
  91                                         GROUP BY partner_id ' , (tuple(self.ACCOUNT_TYPE), tuple(partner_ids), self.date_from,))
  92                 t = self.cr.fetchall()
  93                 for i in t:
  94                         totals[i[0]] = i[1]
  95
  96                 # This dictionary will store the future or past of all partners
  97                 future_past = {}
  98                 if self.direction_selection == 'future':
  99                         self.cr.execute('SELECT partner_id, SUM(debit-credit) \
 100                                                 FROM account_move_line AS l, account_account\
 101                                                 WHERE (l.account_id=account_account.id)\
 102                                                 AND (account_account.type IN %s)\
 103                                                 AND (COALESCE(date_maturity, date) < %s)\
 104                                                 AND (partner_id IN %s)\
 105                                                 AND ((reconcile_id IS NULL)\
 106                                                 OR (reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
 107                                                 AND '+ self.query + '\
 108                                                 AND account_account.active\
 109                                                 GROUP BY partner_id', (tuple(self.ACCOUNT_TYPE), self.date_from, tuple(partner_ids),self.date_from,))
 110                         t = self.cr.fetchall()
 111                         for i in t:
 112                                 future_past[i[0]] = i[1]
 113                 elif self.direction_selection == 'past': # Using elif so people could extend without this breaking
 114                         self.cr.execute('SELECT partner_id, SUM(debit-credit) \
 115                                         FROM account_move_line AS l, account_account\
 116                                         WHERE (l.account_id=account_account.id)\
 117                                                 AND (account_account.type IN %s)\
 118                                                 AND (COALESCE(date_maturity,date) > %s)\
 119                                                 AND (partner_id IN %s)\
 120                                                 AND ((reconcile_id IS NULL)\
 121                                                 OR (reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
 122                                                 AND '+ self.query + '\
 123                                                 AND account_account.active\
 124                                                 GROUP BY partner_id' , (tuple(self.ACCOUNT_TYPE), self.date_from, tuple(partner_ids), self.date_from,))
 125                         t = self.cr.fetchall()
 126                         for i in t:
 127                                 future_past[i[0]] = i[1]
 128
 129                 # Use one query per period and store results in history (a list variable)
 130                 # Each history will contain : history[1] = {'<partner_id>': <partner_debit-credit>}
 131                 history = []
 132                 for i in range(5):
 133                         self.cr.execute('SELECT partner_id, SUM(debit-credit)\
 134                                         FROM account_move_line AS l, account_account\
 135                                         WHERE (l.account_id = account_account.id)\
 136                                                 AND (account_account.type IN %s)\
 137                                                 AND (COALESCE(date_maturity,date) BETWEEN %s AND %s)\
 138                                                 AND (partner_id IN %s)\
 139                                                 AND ((reconcile_id IS NULL)\
 140                                                 OR (reconcile_id IN (SELECT recon.id FROM account_move_reconcile AS recon WHERE recon.create_date > %s )))\
 141                                                 AND '+ self.query + '\
 142                                                 AND account_account.active\
 143                                         GROUP BY partner_id' , (tuple(self.ACCOUNT_TYPE), form[str(i)]['start'], form[str(i)]['stop'], tuple(partner_ids) ,self.date_from,))
 144                         t = self.cr.fetchall()
 145                         d = {}
 146                         for i in t:
 147                                 d[i[0]] = i[1]
 148                         history.append(d)
 149
 150                 for partner in partners:
 151                         values = {}
 152                         ## If choise selection is in the future
 153                         if self.direction_selection == 'future':
 154                                 # Query here is replaced by one query which gets the all the partners their 'before' value
 155                                 before = False
 156                                 if future_past.has_key(partner['id']):
 157                                         before = [ future_past[partner['id']] ]
 158                                 self.total_account[6] = self.total_account[6] + (before and before[0] or 0.0)
 159                                 values['direction'] = before and before[0] or 0.0
 160                         elif self.direction_selection == 'past': # Changed this so people could in the future create new direction_selections
 161                                 # Query here is replaced by one query which gets the all the partners their 'after' value
 162                                 after = False
 163                                 if future_past.has_key(partner['id']): # Making sure this partner actually was found by the query
 164                                         after = [ future_past[partner['id']] ]
 165
 166                                 self.total_account[6] = self.total_account[6] + (after and after[0] or 0.0)
 167                                 values['direction'] = after and after[0] or ""
 168
 169                         for i in range(5):
 170                                 during = False
 171                                 if history[i].has_key(partner['id']):
 172                                         during = [ history[i][partner['id']] ]
 173                                 # Ajout du compteur
 174                                 self.total_account[(i)] = self.total_account[(i)] + (during and during[0] or 0)
 175                                 values[str(i)] = during and during[0] or ""
 176
 177                         total = False
 178                         if totals.has_key( partner['id'] ):
 179                                 total = [ totals[partner['id']] ]
 180                         values['total'] = total and total[0] or 0.0
 181                         ## Add for total
 182                         self.total_account[(i+1)] = self.total_account[(i+1)] + (total and total[0] or 0.0)
 183                         values['name'] = partner['name']
 184
 185                         if values['total']:
 186                                 res.append(values)
 187
 188                 total = 0.0
 189                 totals = {}
 190                 for r in res:
 191                         total += float(r['total'] or 0.0)
 192                         for i in range(5)+['direction']:
 193                                 totals.setdefault(str(i), 0.0)
 194                                 totals[str(i)] += float(r[str(i)] or 0.0)
 195                 return res
 196
 197         def _get_total(self,pos):
 198                 period = self.total_account[int(pos)]
 199                 return period
 200
 201         def _get_direction(self,pos):
 202                 period = self.total_account[int(pos)]
 203                 return period
 204
 205         def _get_for_period(self,pos):
 206                 period = self.total_account[int(pos)]
 207                 return period
 208
 209         def _get_partners(self,data):
 210                 if data['form']['result_selection'] == 'customer':
 211                     return 'Receivable Accounts'
 212                 elif data['form']['result_selection'] == 'supplier':
 213                     return 'Payable Accounts'
 214                 elif data['form']['result_selection'] == 'customer_supplier':
 215                     return 'Receivable and Payable Accounts'
 216                 return ''
 217
 218 report_sxw.report_sxw('report.account.aged_trial_balance', 'res.partner',
 219                 'addons/account/report/account_aged_partner_balance.rml',parser=aged_trial_report, header=False)
 220
 221
 222 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: