addons/account/project/report/analytic_balance.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 # Copyright (c) 2004-2008 TINY SPRL. (http://tiny.be) All Rights Reserved.
   5 #
   6 # $Id$
   7 #
   8 # WARNING: This program as such is intended to be used by professional
   9 # programmers who take the whole responsability of assessing all potential
  10 # consequences resulting FROM its eventual inadequacies AND bugs
  11 # End users who are looking for a ready-to-use solution with commercial
  12 # garantees AND support are strongly adviced to contract a Free Software
  13 # Service Company
  14 #
  15 # This program is Free Software; you can redistribute it AND/or
  16 # modify it under the terms of the GNU General Public License
  17 # as published by the Free Software Foundation; either version 2
  18 # of the License, or (at your option) any later version.
  19 #
  20 # This program is distributed in the hope that it will be useful,
  21 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  22 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  23 # GNU General Public License for more details.
  24 #
  25 # You should have received a copy of the GNU General Public License
  26 # along with this program; if not, write to the Free Software
  27 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  28 #
  29 ##############################################################################
  30
  31 import pooler
  32 import time
  33 from report import report_sxw
  34 import rml_parse
  35
  36 class account_analytic_balance(rml_parse.rml_parse):
  37     def __init__(self, cr, uid, name, context):
  38         super(account_analytic_balance, self).__init__(cr, uid, name, context)
  39         self.localcontext.update( {
  40             'time': time,
  41             'lines_g': self._lines_g,
  42             'move_sum_debit': self._move_sum_debit,
  43             'move_sum_credit': self._move_sum_credit,
  44             'sum_debit': self._sum_debit,
  45             'sum_credit': self._sum_credit,
  46             'sum_balance': self._sum_balance,
  47             'sum_quantity': self._sum_quantity,
  48             'move_sum_balance': self._move_sum_balance,
  49             'move_sum_quantity': self._move_sum_quantity,
  50         })
  51
  52     def _lines_g(self, account_id, date1, date2):
  53         account_analytic_obj = self.pool.get('account.analytic.account')
  54         ids = account_analytic_obj.search(self.cr, self.uid,
  55                 [('parent_id', 'child_of', [account_id])])
  56         self.cr.execute("SELECT aa.name AS name, aa.code AS code, \
  57                     sum(aal.amount) AS balance, sum(aal.unit_amount) AS quantity \
  58                 FROM account_analytic_line AS aal, account_account AS aa \
  59                 WHERE (aal.general_account_id=aa.id) \
  60                     AND (aal.account_id in (" + ','.join(map(str, ids)) + "))\
  61                     AND (date>=%s) AND (date<=%s) AND aa.active \
  62                 GROUP BY aal.general_account_id, aa.name, aa.code, aal.code \
  63                 ORDER BY aal.code", (date1, date2))
  64         res = self.cr.dictfetchall()
  65
  66         for r in res:
  67             if r['balance'] > 0:
  68                 r['debit'] = r['balance']
  69                 r['credit'] = 0.0
  70             elif r['balance'] < 0:
  71                 r['debit'] = 0.0
  72                 r['credit'] = -r['balance']
  73             else:
  74                 r['balance'] == 0
  75                 r['debit'] = 0.0
  76                 r['credit'] = 0.0
  77         return res
  78
  79
  80     def _move_sum_debit(self, account_id, date1, date2):
  81         account_analytic_obj = self.pool.get('account.analytic.account')
  82         ids = account_analytic_obj.search(self.cr, self.uid,
  83                 [('parent_id', 'child_of', [account_id])])
  84         self.cr.execute("SELECT sum(amount) \
  85                 FROM account_analytic_line \
  86                 WHERE account_id in ("+ ','.join(map(str, ids)) +") \
  87                     AND date>=%s AND date<=%s AND amount>0",
  88                 (date1, date2))
  89         return self.cr.fetchone()[0] or 0.0
  90
  91     def _move_sum_credit(self, account_id, date1, date2):
  92         account_analytic_obj = self.pool.get('account.analytic.account')
  93         ids = account_analytic_obj.search(self.cr, self.uid,
  94                 [('parent_id', 'child_of', [account_id])])
  95         self.cr.execute("SELECT -sum(amount) \
  96                 FROM account_analytic_line \
  97                 WHERE account_id in ("+ ','.join(map(str, ids)) +") \
  98                     AND date>=%s AND date<=%s AND amount<0",
  99                 (date1, date2))
 100         return self.cr.fetchone()[0] or 0.0
 101
 102     def _move_sum_balance(self, account_id, date1, date2):
 103         debit = self._move_sum_debit(account_id, date1, date2)
 104         credit = self._move_sum_credit(account_id, date1, date2)
 105         return (debit-credit)
 106
 107     def _move_sum_quantity(self, account_id, date1, date2):
 108         account_analytic_obj = self.pool.get('account.analytic.account')
 109         ids = account_analytic_obj.search(self.cr, self.uid,
 110                 [('parent_id', 'child_of', [account_id])])
 111         self.cr.execute("SELECT sum(unit_amount) \
 112                 FROM account_analytic_line \
 113                 WHERE account_id in ("+ ','.join(map(str, ids)) +") \
 114                     AND date>=%s AND date<=%s",
 115                 (date1, date2))
 116         return self.cr.fetchone()[0] or 0.0
 117
 118
 119     def _sum_debit(self, accounts, date1, date2):
 120         ids = map(lambda x: x.id, accounts)
 121         if not len(ids):
 122             return 0.0
 123         account_analytic_obj = self.pool.get('account.analytic.account')
 124         ids2 = account_analytic_obj.search(self.cr, self.uid,
 125                 [('parent_id', 'child_of', ids)])
 126         self.cr.execute("SELECT sum(amount) \
 127                 FROM account_analytic_line \
 128                 WHERE account_id IN ("+','.join(map(str, ids2))+") \
 129                     AND date>=%s AND date<=%s AND amount>0",
 130                 (date1, date2))
 131         return self.cr.fetchone()[0] or 0.0
 132
 133     def _sum_credit(self, accounts, date1, date2):
 134         ids = map(lambda x: x.id, accounts)
 135         if not len(ids):
 136             return 0.0
 137         ids = map(lambda x: x.id, accounts)
 138         account_analytic_obj = self.pool.get('account.analytic.account')
 139         ids2 = account_analytic_obj.search(self.cr, self.uid,
 140                 [('parent_id', 'child_of', ids)])
 141         self.cr.execute("SELECT -sum(amount) \
 142                 FROM account_analytic_line \
 143                 WHERE account_id IN ("+','.join(map(str, ids2))+") \
 144                     AND date>=%s AND date<=%s AND amount<0",
 145                 (date1, date2))
 146         return self.cr.fetchone()[0] or 0.0
 147
 148     def _sum_balance(self, accounts, date1, date2):
 149         debit = self._sum_debit(accounts, date1, date2) or 0.0
 150         credit = self._sum_credit(accounts, date1, date2) or 0.0
 151         return (debit-credit)
 152
 153     def _sum_quantity(self, accounts, date1, date2):
 154         ids = map(lambda x: x.id, accounts)
 155         if not len(ids):
 156             return 0.0
 157         account_analytic_obj = self.pool.get('account.analytic.account')
 158         ids2 = account_analytic_obj.search(self.cr, self.uid,
 159                 [('parent_id', 'child_of', ids)])
 160         self.cr.execute("SELECT sum(unit_amount) \
 161                 FROM account_analytic_line \
 162                 WHERE account_id IN ("+','.join(map(str, ids2))+") \
 163                     AND date>=%s AND date<=%s",
 164                 (date1, date2))
 165         return self.cr.fetchone()[0] or 0.0
 166
 167 report_sxw.report_sxw('report.account.analytic.account.balance',
 168         'account.analytic.account', 'addons/account/project/report/analytic_balance.rml',
 169         parser=account_analytic_balance, header=False)
 170
 171
 172 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 173