addons/report_analytic_planning/report_analytic_planning.py

   1 ##############################################################################
   2 #
   3 # Copyright (c) 2004-2006 TINY SPRL. (http://tiny.be) All Rights Reserved.
   4 #
   5 # $Id: sale.py 1005 2005-07-25 08:41:42Z nicoe $
   6 #
   7 # WARNING: This program as such is intended to be used by professional
   8 # programmers who take the whole responsability of assessing all potential
   9 # consequences resulting from its eventual inadequacies and bugs
  10 # End users who are looking for a ready-to-use solution with commercial
  11 # garantees and support are strongly adviced to contract a Free Software
  12 # Service Company
  13 #
  14 # This program is Free Software; you can redistribute it and/or
  15 # modify it under the terms of the GNU General Public License
  16 # as published by the Free Software Foundation; either version 2
  17 # of the License, or (at your option) any later version.
  18 #
  19 # This program is distributed in the hope that it will be useful,
  20 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  21 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  22 # GNU General Public License for more details.
  23 #
  24 # You should have received a copy of the GNU General Public License
  25 # along with this program; if not, write to the Free Software
  26 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  27 #
  28 ##############################################################################
  29
  30 from osv import fields,osv
  31
  32 import time
  33 import mx.DateTime
  34
  35 class report_account_analytic_planning(osv.osv):
  36         _name = "report_account_analytic.planning"
  37         _description = "Planning"
  38         _columns = {
  39                 'name': fields.char('Planning Name', size=32, required=True),
  40                 'user_id': fields.many2one('res.users', 'Responsible', required=True),
  41                 'date_from':fields.date('Start date', required=True),
  42                 'date_to':fields.date('End date', required=True),
  43                 'line_ids': fields.one2many('report_account_analytic.planning.line', 'planning_id', 'Planning lines'),
  44                 'stat_ids': fields.one2many('report_account_analytic.planning.stat', 'planning_id', 'Planning analysis', readonly=True),
  45                 'stat_user_ids': fields.one2many('report_account_analytic.planning.stat.user', 'planning_id', 'Planning by user', readonly=True),
  46                 'stat_account_ids': fields.one2many('report_account_analytic.planning.stat.account', 'planning_id', 'Planning by account', readonly=True),
  47         }
  48         _defaults = {
  49                 'name': lambda *a: time.strftime('%Y-%m-%d'),
  50                 'date_from': lambda *a: time.strftime('%Y-%m-01'),
  51                 'date_to': lambda *a: (mx.DateTime.now()+mx.DateTime.RelativeDateTime(months=1,day=1,days=-1)).strftime('%Y-%m-%d'),
  52                 'user_id': lambda self,cr,uid,c: uid
  53         }
  54         _order = 'date_from desc'
  55 report_account_analytic_planning()
  56
  57 class report_account_analytic_planning_line(osv.osv):
  58         _name = "report_account_analytic.planning.line"
  59         _description = "Planning Line"
  60         _rec_name = 'user_id'
  61         _columns = {
  62                 'account_id':fields.many2one('account.analytic.account', 'Analytic account', required=True),
  63                 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning', required=True, ondelete='cascade'),
  64                 'user_id': fields.many2one('res.users', 'User'),
  65                 'amount': fields.float('Quantity', required=True),
  66                 'amount_unit':fields.many2one('product.uom', 'Qty UoM', required=True),
  67                 'note':fields.text('Note', size=64),
  68         }
  69         _order = 'user_id,account_id'
  70 report_account_analytic_planning_line()
  71
  72 class report_account_analytic_planning_stat_account(osv.osv):
  73         _name = "report_account_analytic.planning.stat.account"
  74         _description = "Planning account stat"
  75         _rec_name = 'account_id'
  76         _auto = False
  77         _log_access = False
  78         def _sum_amount_real(self, cr, uid, ids, name, args, context):
  79                 result = {}
  80                 for line in self.browse(cr, uid, ids, context):
  81                         cr.execute('select sum(unit_amount) from account_analytic_line where account_id=%d and date>=%s and date<=%s', (line.account_id.id,line.planning_id.date_from,line.planning_id.date_to))
  82                         result[line.id] = cr.fetchone()[0]
  83                 return result
  84         _columns = {
  85                 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning'),
  86                 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', required=True),
  87                 'quantity': fields.float('Planned', required=True),
  88                 'sum_amount_real': fields.function(_sum_amount_real, method=True, string='Timesheet'),
  89         }
  90         def init(self, cr):
  91                 cr.execute("""
  92                         create or replace view report_account_analytic_planning_stat_account as (
  93                                 select
  94                                         min(l.id) as id,
  95                                         l.account_id as account_id,
  96                                         sum(l.amount*u.factor) as quantity,
  97                                         l.planning_id
  98                                 from
  99                                         report_account_analytic_planning_line l
 100                                 left join
 101                                         product_uom u on (l.amount_unit = u.id)
 102                                 group by
 103                                         planning_id, account_id
 104                         )
 105                 """)
 106 report_account_analytic_planning_stat_account()
 107
 108 class report_account_analytic_planning_stat(osv.osv):
 109         _name = "report_account_analytic.planning.stat"
 110         _description = "Planning stat"
 111         _rec_name = 'user_id'
 112         _auto = False
 113         _log_access = False
 114         def _sum_amount_real(self, cr, uid, ids, name, args, context):
 115                 result = {}
 116                 for line in self.browse(cr, uid, ids, context):
 117                         if line.user_id:
 118                                 cr.execute('select sum(unit_amount) from account_analytic_line where user_id=%d and account_id=%d and date>=%s and date<=%s', (line.user_id.id,line.account_id.id,line.planning_id.date_from,line.planning_id.date_to))
 119                         else:
 120                                 cr.execute('select sum(unit_amount) from account_analytic_line where account_id=%d and date>=%s and date<=%s', (line.account_id.id,line.planning_id.date_from,line.planning_id.date_to))
 121                         result[line.id] = cr.fetchone()[0]
 122                 return result
 123         def _sum_amount_tasks(self, cr, uid, ids, name, args, context):
 124                 result = {}
 125                 for line in self.browse(cr, uid, ids, context):
 126                         where = ''
 127                         if line.user_id:
 128                                 where='user_id='+str(line.user_id.id)+' and '
 129                         cr.execute('''select
 130                                         sum(planned_hours)
 131                                 from
 132                                         project_task
 133                                 where
 134                                 '''+where+'''
 135                                         project_id in (select id from project_project where category_id=%d) and
 136                                         date_close>=%s and
 137                                         date_close<=%s''', (
 138                                 line.account_id.id,
 139                                 line.planning_id.date_from,
 140                                 line.planning_id.date_to)
 141                         )
 142                         result[line.id] = cr.fetchone()[0]
 143                 return result
 144         _columns = {
 145                 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning'),
 146                 'user_id': fields.many2one('res.users', 'User'),
 147                 'manager_id': fields.many2one('res.users', 'Manager'),
 148                 'account_id': fields.many2one('account.analytic.account', 'Account', required=True),
 149                 'sum_amount': fields.float('Planned hours', required=True),
 150                 'sum_amount_real': fields.function(_sum_amount_real, method=True, string='Timesheet'),
 151                 'sum_amount_tasks': fields.function(_sum_amount_tasks, method=True, string='Tasks'),
 152         }
 153         _order = 'planning_id,user_id'
 154         def init(self, cr):
 155                 cr.execute("""
 156                         create or replace view report_account_analytic_planning_stat as (
 157                                 select
 158                                         min(l.id) as id,
 159                                         l.user_id as user_id,
 160                                         a.user_id as manager_id,
 161                                         l.account_id as account_id,
 162                                         sum(l.amount*u.factor) as sum_amount,
 163                                         l.planning_id
 164                                 from
 165                                         report_account_analytic_planning_line l
 166                                 left join
 167                                         account_analytic_account a on (a.id = l.account_id)
 168                                 left join
 169                                         product_uom u on (l.amount_unit = u.id)
 170                                 group by
 171                                         l.planning_id, l.user_id, l.account_id, a.user_id
 172                         )
 173                 """)
 174 report_account_analytic_planning_stat()
 175
 176 class report_account_analytic_planning_stat_user(osv.osv):
 177         _name = "report_account_analytic.planning.stat.user"
 178         _description = "Planning user stat"
 179         _rec_name = 'user_id'
 180         _auto = False
 181         _log_access = False
 182         def _sum_amount_real(self, cr, uid, ids, name, args, context):
 183                 result = {}
 184                 for line in self.browse(cr, uid, ids, context):
 185                         result[line.id] = 0.0
 186                         if line.user_id:
 187                                 cr.execute('select sum(unit_amount) from account_analytic_line where user_id=%d and date>=%s and date<=%s', (line.user_id.id,line.planning_id.date_from,line.planning_id.date_to))
 188                                 result[line.id] = cr.fetchone()[0]
 189                 return result
 190         _columns = {
 191                 'planning_id': fields.many2one('report_account_analytic.planning', 'Planning', required=True),
 192                 'user_id': fields.many2one('res.users', 'User'),
 193                 'quantity': fields.float('Planned', required=True),
 194                 'sum_amount_real': fields.function(_sum_amount_real, method=True, string='Timesheet'),
 195         }
 196         def init(self, cr):
 197                 cr.execute("""
 198                         create or replace view report_account_analytic_planning_stat_user as (
 199                                 select
 200                                         min(l.id) as id,
 201                                         l.user_id as user_id,
 202                                         sum(l.amount*u.factor) as quantity,
 203                                         l.planning_id
 204                                 from
 205                                         report_account_analytic_planning_line l
 206                                 left join
 207                                         product_uom u on (l.amount_unit = u.id)
 208                                 group by
 209                                         planning_id, user_id
 210                         )
 211                 """)
 212 report_account_analytic_planning_stat_user()