addons/account/report/account_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.tools.translate import _
  25 from openerp.report import report_sxw
  26 from common_report_header import common_report_header
  27
  28
  29 class partner_balance(report_sxw.rml_parse, common_report_header):
  30
  31     def __init__(self, cr, uid, name, context=None):
  32         super(partner_balance, self).__init__(cr, uid, name, context=context)
  33         self.account_ids = []
  34         self.localcontext.update( {
  35             'time': time,
  36             'lines': self.lines,
  37             'sum_debit': self._sum_debit,
  38             'sum_credit': self._sum_credit,
  39             'sum_litige': self._sum_litige,
  40             'get_fiscalyear': self._get_fiscalyear,
  41             'get_journal': self._get_journal,
  42             'get_filter': self._get_filter,
  43             'get_account': self._get_account,
  44             'get_start_date':self._get_start_date,
  45             'get_end_date':self._get_end_date,
  46             'get_start_period': self.get_start_period,
  47             'get_end_period': self.get_end_period,
  48             'get_partners':self._get_partners,
  49             'get_target_move': self._get_target_move,
  50         })
  51
  52     def set_context(self, objects, data, ids, report_type=None):
  53         self.display_partner = data['form'].get('display_partner', 'non-zero_balance')
  54         obj_move = self.pool.get('account.move.line')
  55         self.query = obj_move._query_get(self.cr, self.uid, obj='l', context=data['form'].get('used_context', {}))
  56         self.result_selection = data['form'].get('result_selection')
  57         self.target_move = data['form'].get('target_move', 'all')
  58
  59         if (self.result_selection == 'customer' ):
  60             self.ACCOUNT_TYPE = ('receivable',)
  61         elif (self.result_selection == 'supplier'):
  62             self.ACCOUNT_TYPE = ('payable',)
  63         else:
  64             self.ACCOUNT_TYPE = ('payable', 'receivable')
  65
  66         self.cr.execute("SELECT a.id " \
  67                 "FROM account_account a " \
  68                 "LEFT JOIN account_account_type t " \
  69                     "ON (a.type = t.code) " \
  70                     "WHERE a.type IN %s " \
  71                     "AND a.active", (self.ACCOUNT_TYPE,))
  72         self.account_ids = [a for (a,) in self.cr.fetchall()]
  73         return super(partner_balance, self).set_context(objects, data, ids, report_type=report_type)
  74
  75     def lines(self):
  76         move_state = ['draft','posted']
  77         if self.target_move == 'posted':
  78             move_state = ['posted']
  79
  80         full_account = []
  81         self.cr.execute(
  82             "SELECT p.ref,l.account_id,ac.name AS account_name,ac.code AS code,p.name, sum(debit) AS debit, sum(credit) AS credit, " \
  83                     "CASE WHEN sum(debit) > sum(credit) " \
  84                         "THEN sum(debit) - sum(credit) " \
  85                         "ELSE 0 " \
  86                     "END AS sdebit, " \
  87                     "CASE WHEN sum(debit) < sum(credit) " \
  88                         "THEN sum(credit) - sum(debit) " \
  89                         "ELSE 0 " \
  90                     "END AS scredit, " \
  91                     "(SELECT sum(debit-credit) " \
  92                         "FROM account_move_line l " \
  93                         "WHERE partner_id = p.id " \
  94                             "AND " + self.query + " " \
  95                             "AND blocked = TRUE " \
  96                     ") AS enlitige " \
  97             "FROM account_move_line l LEFT JOIN res_partner p ON (l.partner_id=p.id) " \
  98             "JOIN account_account ac ON (l.account_id = ac.id)" \
  99             "JOIN account_move am ON (am.id = l.move_id)" \
 100             "WHERE ac.type IN %s " \
 101             "AND am.state IN %s " \
 102             "AND " + self.query + "" \
 103             "GROUP BY p.id, p.ref, p.name,l.account_id,ac.name,ac.code " \
 104             "ORDER BY l.account_id,p.name",
 105             (self.ACCOUNT_TYPE, tuple(move_state)))
 106         res = self.cr.dictfetchall()
 107
 108
 109         if self.display_partner == 'non-zero_balance':
 110             full_account = [r for r in res if r['sdebit'] > 0 or r['scredit'] > 0]
 111         else:
 112             full_account = [r for r in res]
 113
 114         for rec in full_account:
 115             if not rec.get('name', False):
 116                 rec.update({'name': _('Unknown Partner')})
 117
 118         ## We will now compute Total
 119         subtotal_row = self._add_subtotal(full_account)
 120         return subtotal_row
 121
 122     def _add_subtotal(self, cleanarray):
 123         i = 0
 124         completearray = []
 125         tot_debit = 0.0
 126         tot_credit = 0.0
 127         tot_scredit = 0.0
 128         tot_sdebit = 0.0
 129         tot_enlitige = 0.0
 130         for r in cleanarray:
 131             # For the first element we always add the line
 132             # type = 1 is the line is the first of the account
 133             # type = 2 is an other line of the account
 134             if i==0:
 135                 # We add the first as the header
 136                 #
 137                 ##
 138                 new_header = {}
 139                 new_header['ref'] = ''
 140                 new_header['name'] = r['account_name']
 141                 new_header['code'] = r['code']
 142                 new_header['debit'] = r['debit']
 143                 new_header['credit'] = r['credit']
 144                 new_header['scredit'] = tot_scredit
 145                 new_header['sdebit'] = tot_sdebit
 146                 new_header['enlitige'] = tot_enlitige
 147                 new_header['balance'] = r['debit'] - r['credit']
 148                 new_header['type'] = 3
 149                 ##
 150                 completearray.append(new_header)
 151                 #
 152                 r['type'] = 1
 153                 r['balance'] = float(r['sdebit']) - float(r['scredit'])
 154
 155                 completearray.append(r)
 156                 #
 157                 tot_debit = r['debit']
 158                 tot_credit = r['credit']
 159                 tot_scredit = r['scredit']
 160                 tot_sdebit = r['sdebit']
 161                 tot_enlitige = (r['enlitige'] or 0.0)
 162                 #
 163             else:
 164                 if cleanarray[i]['account_id'] <> cleanarray[i-1]['account_id']:
 165
 166                     new_header['debit'] = tot_debit
 167                     new_header['credit'] = tot_credit
 168                     new_header['scredit'] = tot_scredit
 169                     new_header['sdebit'] = tot_sdebit
 170                     new_header['enlitige'] = tot_enlitige
 171                     new_header['balance'] = float(tot_sdebit) - float(tot_scredit)
 172                     new_header['type'] = 3
 173                     # we reset the counter
 174                     tot_debit = r['debit']
 175                     tot_credit = r['credit']
 176                     tot_scredit = r['scredit']
 177                     tot_sdebit = r['sdebit']
 178                     tot_enlitige = (r['enlitige'] or 0.0)
 179                     #
 180                     ##
 181                     new_header = {}
 182                     new_header['ref'] = ''
 183                     new_header['name'] = r['account_name']
 184                     new_header['code'] = r['code']
 185                     new_header['debit'] = tot_debit
 186                     new_header['credit'] = tot_credit
 187                     new_header['scredit'] = tot_scredit
 188                     new_header['sdebit'] = tot_sdebit
 189                     new_header['enlitige'] = tot_enlitige
 190                     new_header['balance'] = float(tot_sdebit) - float(tot_scredit)
 191                     new_header['type'] = 3
 192                     ##get_fiscalyear
 193                     ##
 194
 195                     completearray.append(new_header)
 196                     ##
 197                     #
 198                     r['type'] = 1
 199                     #
 200                     r['balance'] = float(r['sdebit']) - float(r['scredit'])
 201
 202                     completearray.append(r)
 203
 204                 if cleanarray[i]['account_id'] == cleanarray[i-1]['account_id']:
 205                     # we reset the counter
 206
 207                     new_header['debit'] = tot_debit
 208                     new_header['credit'] = tot_credit
 209                     new_header['scredit'] = tot_scredit
 210                     new_header['sdebit'] = tot_sdebit
 211                     new_header['enlitige'] = tot_enlitige
 212                     new_header['balance'] = float(tot_sdebit) - float(tot_scredit)
 213                     new_header['type'] = 3
 214
 215                     tot_debit = tot_debit + r['debit']
 216                     tot_credit = tot_credit + r['credit']
 217                     tot_scredit = tot_scredit + r['scredit']
 218                     tot_sdebit = tot_sdebit + r['sdebit']
 219                     tot_enlitige = tot_enlitige + (r['enlitige'] or 0.0)
 220
 221                     new_header['debit'] = tot_debit
 222                     new_header['credit'] = tot_credit
 223                     new_header['scredit'] = tot_scredit
 224                     new_header['sdebit'] = tot_sdebit
 225                     new_header['enlitige'] = tot_enlitige
 226                     new_header['balance'] = float(tot_sdebit) - float(tot_scredit)
 227
 228                     #
 229                     r['type'] = 2
 230                     #
 231                     r['balance'] = float(r['sdebit']) - float(r['scredit'])
 232                     #
 233
 234                     completearray.append(r)
 235
 236             i = i + 1
 237         return completearray
 238
 239     def _sum_debit(self):
 240         move_state = ['draft','posted']
 241         if self.target_move == 'posted':
 242             move_state = ['posted']
 243
 244         if not self.ids:
 245             return 0.0
 246         self.cr.execute(
 247                 "SELECT sum(debit) " \
 248                 "FROM account_move_line AS l " \
 249                 "JOIN account_move am ON (am.id = l.move_id)" \
 250                 "WHERE l.account_id IN %s"  \
 251                     "AND am.state IN %s" \
 252                     "AND " + self.query + "",
 253                     (tuple(self.account_ids), tuple(move_state)))
 254         temp_res = float(self.cr.fetchone()[0] or 0.0)
 255         return temp_res
 256
 257     def _sum_credit(self):
 258         move_state = ['draft','posted']
 259         if self.target_move == 'posted':
 260             move_state = ['posted']
 261
 262         if not self.ids:
 263             return 0.0
 264         self.cr.execute(
 265                 "SELECT sum(credit) " \
 266                 "FROM account_move_line AS l " \
 267                 "JOIN account_move am ON (am.id = l.move_id)" \
 268                 "WHERE l.account_id IN %s" \
 269                     "AND am.state IN %s" \
 270                     "AND " + self.query + "",
 271                     (tuple(self.account_ids), tuple(move_state)))
 272         temp_res = float(self.cr.fetchone()[0] or 0.0)
 273         return temp_res
 274
 275     def _sum_litige(self):
 276         #gives the total of move lines with blocked boolean set to TRUE for the report selection
 277         move_state = ['draft','posted']
 278         if self.target_move == 'posted':
 279             move_state = ['posted']
 280
 281         if not self.ids:
 282             return 0.0
 283         self.cr.execute(
 284                 "SELECT sum(debit-credit) " \
 285                 "FROM account_move_line AS l " \
 286                 "JOIN account_move am ON (am.id = l.move_id)" \
 287                 "WHERE l.account_id IN %s" \
 288                     "AND am.state IN %s" \
 289                     "AND " + self.query + " " \
 290                     "AND l.blocked=TRUE ",
 291                     (tuple(self.account_ids), tuple(move_state), ))
 292         temp_res = float(self.cr.fetchone()[0] or 0.0)
 293         return temp_res
 294
 295     def _get_partners(self):
 296
 297         if self.result_selection == 'customer':
 298             return _('Receivable Accounts')
 299         elif self.result_selection == 'supplier':
 300             return _('Payable Accounts')
 301         elif self.result_selection == 'customer_supplier':
 302             return _('Receivable and Payable Accounts')
 303         return ''
 304
 305
 306 class report_partnerbalance(osv.AbstractModel):
 307     _name = 'report.account.report_partnerbalance'
 308     _inherit = 'report.abstract_report'
 309     _template = 'account.report_partnerbalance'
 310     _wrapped_report_class = partner_balance
 311
 312 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: