addons/report_analytic_planning/report_analytic_planning.py

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