-# -*- encoding: utf-8 -*-
+# -*- coding: utf-8 -*-
##############################################################################
#
-# OpenERP, Open Source Management Solution
-# Copyright (C) 2004-2008 Tiny SPRL (<http://tiny.be>). All Rights Reserved
-# $Id$
+# OpenERP, Open Source Management Solution
+# Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
#
# This program is free software: you can redistribute it and/or modify
-# it under the terms of the GNU General Public License as published by
-# the Free Software Foundation, either version 3 of the License, or
-# (at your option) any later version.
+# it under the terms of the GNU Affero General Public License as
+# published by the Free Software Foundation, either version 3 of the
+# License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-# GNU General Public License for more details.
+# GNU Affero General Public License for more details.
#
-# You should have received a copy of the GNU General Public License
+# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################
+
import operator
from osv import osv, fields
+from osv.orm import intersect
+import tools.sql
from tools.translate import _
def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
res = {}
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
- if ids2:
- acc_set = ",".join(map(str, ids2))
- cr.execute("select account_analytic_line.account_id, sum(amount) \
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ if parent_ids:
+ cr.execute("select account_analytic_line.account_id, COALESCE(sum(amount_currency),0.0) \
from account_analytic_line \
join account_analytic_journal \
on account_analytic_line.journal_id = account_analytic_journal.id \
- where account_analytic_line.account_id IN (%s) \
+ where account_analytic_line.account_id IN %s \
and account_analytic_journal.type = 'sale' \
- group by account_analytic_line.account_id" % acc_set)
+ group by account_analytic_line.account_id" ,(parent_ids,))
for account_id, sum in cr.fetchall():
res[account_id] = round(sum,2)
- for obj_id in ids:
- res.setdefault(obj_id, 0.0)
- for child_id in self.search(cr, uid,
- [('parent_id', 'child_of', [obj_id])]):
- if child_id != obj_id:
- res[obj_id] += res.get(child_id, 0.0)
- for id in ids:
- res[id] = round(res.get(id, 0.0),2)
- return res
+
+ return self._compute_currency_for_level_tree(cr, uid, ids, parent_ids, res, context)
def _ca_to_invoice_calc(self, cr, uid, ids, name, arg, context={}):
res = {}
res2 = {}
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
- if ids2:
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ if parent_ids:
# Amount uninvoiced hours to invoice at sale price
- acc_set = ",".join(map(str, ids2))
+ # Warning
+ # This computation doesn't take care of pricelist !
+ # Just consider list_price
cr.execute("""SELECT account_analytic_account.id, \
- sum (product_template.list_price * \
+ COALESCE(sum (product_template.list_price * \
account_analytic_line.unit_amount * \
- ((100-hr_timesheet_invoice_factor.factor)/100)) \
+ ((100-hr_timesheet_invoice_factor.factor)/100)),0.0) \
AS ca_to_invoice \
FROM product_template \
join product_product \
on account_analytic_account.id = account_analytic_line.account_id \
JOIN hr_timesheet_invoice_factor \
on hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
- WHERE account_analytic_account.id IN (%s) \
+ WHERE account_analytic_account.id IN %s \
AND account_analytic_line.invoice_id is null \
AND account_analytic_line.to_invoice IS NOT NULL \
and account_analytic_journal.type in ('purchase','general') \
- GROUP BY account_analytic_account.id;"""%acc_set)
+ GROUP BY account_analytic_account.id;""",(parent_ids,))
for account_id, sum in cr.fetchall():
res[account_id] = round(sum,2)
- # Expense amount and purchase invoice
- #acc_set = ",".join(map(str, ids2))
- #cr.execute ("select account_analytic_line.account_id, sum(amount) \
- # from account_analytic_line \
- # join account_analytic_journal \
- # on account_analytic_line.journal_id = account_analytic_journal.id \
- # where account_analytic_line.account_id IN (%s) \
- # and account_analytic_journal.type = 'purchase' \
- # GROUP BY account_analytic_line.account_id;"%acc_set)
- #for account_id, sum in cr.fetchall():
- # res2[account_id] = round(sum,2)
for obj_id in ids:
res.setdefault(obj_id, 0.0)
res2.setdefault(obj_id, 0.0)
def _hours_qtt_non_invoiced_calc (self, cr, uid, ids, name, arg, context={}):
res = {}
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
- if ids2:
- acc_set = ",".join(map(str, ids2))
- cr.execute("select account_analytic_line.account_id, sum(unit_amount) \
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ if parent_ids:
+ cr.execute("select account_analytic_line.account_id, COALESCE(sum(unit_amount),0.0) \
from account_analytic_line \
join account_analytic_journal \
on account_analytic_line.journal_id = account_analytic_journal.id \
- where account_analytic_line.account_id IN (%s) \
+ where account_analytic_line.account_id IN %s \
and account_analytic_journal.type='general' \
and invoice_id is null \
AND to_invoice IS NOT NULL \
- GROUP BY account_analytic_line.account_id;"%acc_set)
+ GROUP BY account_analytic_line.account_id;",(parent_ids,))
for account_id, sum in cr.fetchall():
res[account_id] = round(sum,2)
for obj_id in ids:
def _hours_quantity_calc(self, cr, uid, ids, name, arg, context={}):
res = {}
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
- if ids2:
- acc_set = ",".join(map(str, ids2))
- cr.execute("select account_analytic_line.account_id,sum(unit_amount) \
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ if parent_ids:
+ cr.execute("select account_analytic_line.account_id,COALESCE(SUM(unit_amount),0.0) \
from account_analytic_line \
join account_analytic_journal \
on account_analytic_line.journal_id = account_analytic_journal.id \
- where account_analytic_line.account_id IN (%s) \
+ where account_analytic_line.account_id IN %s \
and account_analytic_journal.type='general' \
- GROUP BY account_analytic_line.account_id"%acc_set)
- for account_id, sum in cr.fetchall():
+ GROUP BY account_analytic_line.account_id",(parent_ids,))
+ ff = cr.fetchall()
+ for account_id, sum in ff:
res[account_id] = round(sum,2)
for obj_id in ids:
res.setdefault(obj_id, 0.0)
def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
res = {}
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
- if ids2:
- acc_set = ",".join(map(str, ids2))
- cr.execute("""select account_analytic_line.account_id,sum(amount) \
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ if parent_ids:
+ cr.execute("""select account_analytic_line.account_id,COALESCE(sum(amount_currency),0.0) \
+
from account_analytic_line \
join account_analytic_journal \
on account_analytic_line.journal_id = account_analytic_journal.id \
- where account_analytic_line.account_id IN (%s) \
+ where account_analytic_line.account_id IN %s \
and amount<0 \
- GROUP BY account_analytic_line.account_id"""%acc_set)
+ GROUP BY account_analytic_line.account_id""",(parent_ids,))
for account_id, sum in cr.fetchall():
res[account_id] = round(sum,2)
- for obj_id in ids:
- res.setdefault(obj_id, 0.0)
- for child_id in self.search(cr, uid,
- [('parent_id', 'child_of', [obj_id])]):
- if child_id != obj_id:
- res[obj_id] += res.get(child_id, 0.0)
- for id in ids:
- res[id] = round(res.get(id, 0.0),2)
- return res
-
+ return self._compute_currency_for_level_tree(cr, uid, ids, parent_ids, res, context)
+
+ # TODO Take care of pricelist and purchase !
def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
res = {}
res2 = {}
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
- if ids2:
- acc_set = ",".join(map(str, ids2))
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ # Warning
+ # This computation doesn't take care of pricelist !
+ # Just consider list_price
+ if parent_ids:
cr.execute("""select account_analytic_line.account_id as account_id, \
- sum((account_analytic_line.unit_amount * pt.list_price) \
+ COALESCE(sum((account_analytic_line.unit_amount * pt.list_price) \
- (account_analytic_line.unit_amount * pt.list_price \
- * hr.factor)) as somme
+ * hr.factor)),0.0) as somme
from account_analytic_line \
left join account_analytic_journal \
on (account_analytic_line.journal_id = account_analytic_journal.id) \
on (a.id=account_analytic_line.account_id) \
join hr_timesheet_invoice_factor hr \
on (hr.id=a.to_invoice) \
- where account_analytic_line.account_id IN (%s) \
+ where account_analytic_line.account_id IN %s \
and a.to_invoice IS NOT NULL \
and account_analytic_journal.type in ('purchase','general')
- GROUP BY account_analytic_line.account_id"""%acc_set)
+ GROUP BY account_analytic_line.account_id""",(parent_ids,))
for account_id, sum in cr.fetchall():
res2[account_id] = round(sum,2)
-
+
for obj_id in ids:
res.setdefault(obj_id, 0.0)
res2.setdefault(obj_id, 0.0)
if child_id != obj_id:
res[obj_id] += res.get(child_id, 0.0)
res[obj_id] += res2.get(child_id, 0.0)
-
+
# sum both result on account_id
for id in ids:
res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
res = {}
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
- if ids2:
- acc_set = ",".join(map(str, ids2))
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ if parent_ids:
cr.execute("select account_analytic_line.account_id, max(date) \
from account_analytic_line \
- where account_id IN (%s) \
+ where account_id IN %s \
and invoice_id is null \
- GROUP BY account_analytic_line.account_id" % acc_set)
+ GROUP BY account_analytic_line.account_id" ,(parent_ids,))
for account_id, sum in cr.fetchall():
res[account_id] = sum
for obj_id in ids:
def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
res = {}
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
- if ids2:
- acc_set = ",".join(map(str, ids2))
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ if parent_ids:
cr.execute ("select account_analytic_line.account_id, \
date(max(account_invoice.date_invoice)) \
from account_analytic_line \
join account_invoice \
on account_analytic_line.invoice_id = account_invoice.id \
- where account_analytic_line.account_id IN (%s) \
+ where account_analytic_line.account_id IN %s \
and account_analytic_line.invoice_id is not null \
- GROUP BY account_analytic_line.account_id"%acc_set)
+ GROUP BY account_analytic_line.account_id",(parent_ids,))
for account_id, sum in cr.fetchall():
res[account_id] = sum
for obj_id in ids:
def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
res = {}
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
- if ids2:
- acc_set = ",".join(map(str, ids2))
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ if parent_ids:
cr.execute("select account_analytic_line.account_id, max(date) \
from account_analytic_line \
- where account_id IN (%s) \
+ where account_id IN %s \
and invoice_id is not null \
- GROUP BY account_analytic_line.account_id;"%acc_set)
+ GROUP BY account_analytic_line.account_id;",(parent_ids,))
for account_id, sum in cr.fetchall():
res[account_id] = sum
for obj_id in ids:
def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
res = {}
for account in self.browse(cr, uid, ids):
- if account.quantity_max <> 0:
+ if account.quantity_max != 0:
res[account.id] = account.quantity_max - account.hours_quantity
else:
res[account.id]=0.0
for account in self.browse(cr, uid, ids):
if account.ca_invoiced == 0:
res[account.id]=0.0
- elif account.total_cost <> 0.0:
+ elif account.total_cost != 0.0:
res[account.id] = -(account.real_margin / account.total_cost) * 100
else:
res[account.id] = 0.0
def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
res = {}
for account in self.browse(cr, uid, ids):
- if account.amount_max <> 0:
+ if account.amount_max != 0:
res[account.id] = account.amount_max - account.ca_invoiced
else:
res[account.id]=0.0
def _month(self, cr, uid, ids, name, arg, context=None):
res = {}
for id in ids:
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
- cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
- 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
- 'AND unit_amount <> 0.0')
- res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ if parent_ids:
+ cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
+ 'WHERE account_id IN %s AND unit_amount <> 0.0',(parent_ids,))
+ res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
+ else:
+ res[id] = []
return res
def _user(self, cr, uid, ids, name, arg, context=None):
cr.execute('SELECT MAX(id) FROM res_users')
max_user = cr.fetchone()[0]
for id in ids:
- ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
- cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
- 'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
- 'AND unit_amount <> 0.0')
- res[id] = [int((id * max_user) + x[0]) for x in cr.fetchall()]
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)]))
+ if parent_ids:
+ cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
+ 'WHERE account_id IN %s AND unit_amount <> 0.0',(parent_ids,))
+ res[id] = [int((id * max_user) + x[0]) for x in cr.fetchall()]
+ else:
+ res[id] = []
return res
_columns ={
class account_analytic_account_summary_user(osv.osv):
_name = "account_analytic_analysis.summary.user"
- _description = "Hours summary by user"
+ _description = "Hours Summary by User"
_order='user'
_auto = False
_rec_name = 'user'
max_user = cr.fetchone()[0]
account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
- account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
- user_set = ','.join([str(x) for x in user_ids])
- if account_ids2:
- acc_set = ','.join([str(x) for x in account_ids2])
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', account_ids)]))
+ if parent_ids:
cr.execute('SELECT id, unit_amount ' \
'FROM account_analytic_analysis_summary_user ' \
- 'WHERE account_id in (%s) ' \
- 'AND "user" in (%s) ' % (acc_set, user_set))
+ 'WHERE account_id IN %s ' \
+ 'AND "user" IN %s',(parent_ids, user_ids,))
for sum_id, unit_amount in cr.fetchall():
res[sum_id] = unit_amount
for obj_id in ids:
'user' : fields.many2one('res.users', 'User'),
}
def init(self, cr):
+ tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
'SELECT ' \
'(u.account_id * u.max_user) + u."user" AS id, ' \
'SUM(l.unit_amount) AS unit_amount ' \
'FROM account_analytic_line AS l, ' \
'account_analytic_journal AS j ' \
- 'WHERE j.type = \'general\' ' \
+ 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
'GROUP BY l.account_id, l.user_id ' \
') AS l '
'ON (' \
_description = "Hours summary by month"
_auto = False
_rec_name = 'month'
- _order = 'month'
+# _order = 'month'
def _unit_amount(self, cr, uid, ids, name, arg, context=None):
res = {}
account_obj = self.pool.get('account.analytic.account')
account_ids = [int(str(int(x))[:-6]) for x in ids]
month_ids = [int(str(int(x))[-6:]) for x in ids]
- account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
- month_set = ','.join([str(x) for x in month_ids])
- if account_ids2:
- acc_set = ','.join([str(x) for x in account_ids2])
+ parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', account_ids)]))
+ if parent_ids:
cr.execute('SELECT id, unit_amount ' \
'FROM account_analytic_analysis_summary_month ' \
- 'WHERE account_id in (%s) ' \
- 'AND month_id in (%s) ' % \
- (acc_set, month_set))
+ 'WHERE account_id IN %s ' \
+ 'AND month_id IN %s ',(parent_ids, month_ids,))
for sum_id, unit_amount in cr.fetchall():
res[sum_id] = unit_amount
for obj_id in ids:
for child_id in account_obj.search(cr, uid,
[('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
if child_id != int(str(int(obj_id))[:-6]):
- res[obj_id] += res.get(int(child_id * 1000000 + int(obj_id)), 0.0)
+ res[obj_id] += res.get(int(child_id * 1000000 + int(str(int(obj_id))[-6:])), 0.0)
for id in ids:
res[id] = round(res.get(id, 0.0), 2)
return res
string='Total Time'),
'month': fields.char('Month', size=25, readonly=True),
}
+
def init(self, cr):
- cr.execute('DROP VIEW IF EXISTS account_analytic_analysis_summary_month')
+ tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
'SELECT ' \
'(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000))::integer AS id, ' \
'SUM(l.unit_amount) AS unit_amount ' \
'FROM account_analytic_line AS l, ' \
'account_analytic_journal AS j ' \
- 'WHERE j.type = \'general\' ' \
+ 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
') AS l '
'ON (' \