addons/account_analytic_analysis/account_analytic_analysis.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 # Copyright (c) 2004 TINY SPRL. (http://tiny.be) All Rights Reserved.
   5 #                    Fabien Pinckaers <fp@tiny.Be>
   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 import operator
  30 from osv import osv, fields
  31
  32 class account_analytic_account(osv.osv):
  33         _name = "account.analytic.account"
  34         _inherit = "account.analytic.account"
  35
  36         def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
  37                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
  38                 acc_set = ",".join(map(str, ids2))
  39                 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 = 'sale' group by account_analytic_line.account_id" % acc_set)
  40                 res = {}
  41                 for account_id, sum in cr.fetchall():
  42                         res[account_id] = round(sum,2)
  43                 for id in ids:
  44                         res[id] = round(res.get(id, 0.0),2)
  45                 return res
  46
  47         def _ca_to_invoice_calc(self, cr, uid, ids, name, arg, context={}):
  48                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
  49                 # Amount uninvoiced hours to invoice at sale price
  50                 acc_set = ",".join(map(str, ids2))
  51                 cr.execute("""SELECT account_analytic_account.id,(sum (product_template.list_price *  account_analytic_line.unit_amount)
  52                                                                 - sum(product_template.list_price *  account_analytic_line.unit_amount * (hr_timesheet_invoice_factor.factor/100)))
  53                                                 AS ca_to_invoice
  54                                                 FROM product_template join product_product on product_template.id = product_product.product_tmpl_id
  55                                                 JOIN account_analytic_line on account_analytic_line.product_id = product_product.id
  56                                                 JOIN account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
  57                                                 JOIN account_analytic_account on account_analytic_account.id = account_analytic_line.account_id
  58                                                 JOIN hr_timesheet_invoice_factor on hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice
  59                                                 WHERE account_analytic_account.id IN (%s)
  60                                                         AND account_analytic_journal.type='general'
  61                                                         AND account_analytic_line.invoice_id is null
  62                                                 GROUP BY account_analytic_account.id;"""%acc_set)
  63                 res = {}
  64                 for account_id, sum in cr.fetchall():
  65                         res[account_id] = round(sum,2)
  66
  67                 # Expense amount and purchase invoice
  68                 acc_set = ",".join(map(str, ids2))
  69                 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)
  70                 res2 = {}
  71                 for account_id, sum in cr.fetchall():
  72                         res2[account_id] = round(sum,2)
  73                 # sum both result on account_id
  74                 for id in ids:
  75                         res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
  76                 return res
  77
  78         def _hours_qtt_non_invoiced_calc (self, cr, uid, ids, name, arg, context={}):
  79                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
  80                 acc_set = ",".join(map(str, ids2))
  81                 cr.execute("select account_analytic_line.account_id,sum(unit_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='general' and invoice_id is null GROUP BY account_analytic_line.account_id;"%acc_set)
  82                 res = {}
  83                 for account_id, sum in cr.fetchall():
  84                         res[account_id] = round(sum,2)
  85                 for id in ids:
  86                         res[id] = round(res.get(id, 0.0),2)
  87                 return res
  88
  89         def _hours_quantity_calc(self, cr, uid, ids, name, arg, context={}):
  90                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
  91                 acc_set = ",".join(map(str, ids2))
  92                 cr.execute("select account_analytic_line.account_id,sum(unit_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='general' GROUP BY account_analytic_line.account_id"%acc_set)
  93                 res = {}
  94                 for account_id, sum in cr.fetchall():
  95                         res[account_id] = round(sum,2)
  96                 for id in ids:
  97                         res[id] = round(res.get(id, 0.0),2)
  98                 return res
  99
 100         def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
 101                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 102                 acc_set = ",".join(map(str, ids2))
 103                 cr.execute("""select
 104                  account_analytic_line.account_id,sum(amount)
 105                 from
 106                  account_analytic_line join account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
 107                 where
 108                  account_analytic_line.account_id IN (%s) and amount<0
 109                 GROUP BY
 110                  account_analytic_line.account_id"""%acc_set)
 111                 res = {}
 112                 for account_id, sum in cr.fetchall():
 113                         res[account_id] = round(sum,2)
 114                 for id in ids:
 115                         res[id] = round(res.get(id, 0.0),2)
 116                 return res
 117
 118         def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
 119                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 120                 acc_set = ",".join(map(str, ids2))
 121                 # First part with expense and purchase
 122                 cr.execute("""select
 123                                                 account_analytic_line.account_id,sum(amount)
 124                                         from
 125                                                 account_analytic_line join account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
 126                                         where
 127                                                 account_analytic_line.account_id IN (%s) and account_analytic_journal.type = 'purchase'
 128                                         GROUP BY
 129                                                 account_analytic_line.account_id"""%acc_set)
 130                 res = {}
 131                 for account_id, sum in cr.fetchall():
 132                         res[account_id] = round(sum,2)
 133                 # Second part with timesheet (with invoice factor)
 134                 acc_set = ",".join(map(str, ids2))
 135                 cr.execute("""
 136                                 select
 137                                         account_analytic_line.account_id as account_id,sum((account_analytic_line.unit_amount * pt.list_price)-(account_analytic_line.unit_amount * pt.list_price*hr.factor)) as somme
 138                                 from
 139                                         account_analytic_line join account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
 140                                         join product_product pp on (account_analytic_line.product_id = pp.id)
 141                                         join product_template pt on (pp.product_tmpl_id = pt.id)
 142                                         join account_analytic_account a on (a.id=account_analytic_line.account_id)
 143                                         join hr_timesheet_invoice_factor hr on (hr.id=a.to_invoice)
 144                                 where
 145                                         account_analytic_line.account_id IN (%s) and account_analytic_journal.type='general' and a.to_invoice IS NOT NULL
 146                                 GROUP BY
 147                                         account_analytic_line.account_id"""%acc_set)
 148                 res2 = {}
 149                 for account_id, sum in cr.fetchall():
 150                         res2[account_id] = round(sum,2)
 151                 # sum both result on account_id
 152                 for id in ids:
 153                         res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
 154                 return res
 155
 156         def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
 157                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 158                 acc_set = ",".join(map(str, ids2))
 159                 cr.execute("select account_analytic_line.account_id, max(date) from account_analytic_line where account_id IN (%s) and invoice_id is null GROUP BY account_analytic_line.account_id;"%acc_set)
 160                 res = {}
 161                 for account_id, sum in cr.fetchall():
 162                         res[account_id] = sum
 163                 for id in ids:
 164                         res[id] = res.get(id, '')
 165                 return res
 166
 167         def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
 168                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 169                 acc_set = ",".join(map(str, ids2))
 170                 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) and account_analytic_line.invoice_id is not null GROUP BY account_analytic_line.account_id"%acc_set)
 171                 res = {}
 172                 for account_id, sum in cr.fetchall():
 173                         res[account_id] = sum
 174                 for id in ids:
 175                         res[id] = res.get(id, '')
 176                 return res
 177
 178         def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
 179                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 180                 acc_set = ",".join(map(str, ids2))
 181                 cr.execute("select account_analytic_line.account_id, max(date) from account_analytic_line where account_id IN (%s) and invoice_id is not null GROUP BY account_analytic_line.account_id;"%acc_set)
 182                 res = {}
 183                 for account_id, sum in cr.fetchall():
 184                         res[account_id] = sum
 185                 for id in ids:
 186                         res[id] = res.get(id, '')
 187                 return res
 188
 189         def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
 190                 res = {}
 191                 for account in self.browse(cr, uid, ids):
 192                         if account.quantity_max <> 0:
 193                                 res[account.id] = account.quantity_max - account.hours_quantity
 194                         else:
 195                                 res[account.id]=0.0
 196                 for id in ids:
 197                         res[id] = round(res.get(id, 0.0),2)
 198                 return res
 199         def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
 200                 res = {}
 201                 for account in self.browse(cr, uid, ids):
 202                         res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
 203                         if res[account.id] < 0:
 204                                 res[account.id]=0.0
 205                 for id in ids:
 206                         res[id] = round(res.get(id, 0.0),2)
 207                 return res
 208         def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context={}):
 209                 res = {}
 210                 for account in self.browse(cr, uid, ids):
 211                         if account.hours_qtt_invoiced == 0:
 212                                 res[account.id]=0.0
 213                         else:
 214                                 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
 215                 for id in ids:
 216                         res[id] = round(res.get(id, 0.0),2)
 217                 return res
 218         def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context={}):
 219                 res = {}
 220                 for account in self.browse(cr, uid, ids):
 221                         if account.ca_invoiced == 0:
 222                                 res[account.id]=0.0
 223                         else:
 224                                 res[account.id] = (account.real_margin / account.ca_invoiced) * 100
 225                 for id in ids:
 226                         res[id] = round(res.get(id, 0.0),2)
 227                 return res
 228         def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
 229                 res = {}
 230                 for account in self.browse(cr, uid, ids):
 231                         if account.amount_max <> 0:
 232                                 res[account.id] = account.amount_max - account.ca_invoiced
 233                         else:
 234                                 res[account.id]=0.0
 235                 for id in ids:
 236                         res[id] = round(res.get(id, 0.0),2)
 237                 return res
 238         def _real_margin_calc(self, cr, uid, ids, name, arg, context={}):
 239                 res = {}
 240                 for account in self.browse(cr, uid, ids):
 241                         res[account.id] = account.ca_invoiced + account.total_cost
 242                 for id in ids:
 243                         res[id] = round(res.get(id, 0.0),2)
 244                 return res
 245         def _theorical_margin_calc(self, cr, uid, ids, name, arg, context={}):
 246                 res = {}
 247                 for account in self.browse(cr, uid, ids):
 248                         res[account.id] = account.ca_theorical + account.total_cost
 249                 for id in ids:
 250                         res[id] = round(res.get(id, 0.0),2)
 251                 return res
 252
 253         _columns ={
 254                 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced amount'),
 255                 'total_cost': fields.function(_total_cost_calc, method=True, type='float', string='Total cost'),
 256                 'ca_to_invoice': fields.function(_ca_to_invoice_calc, method=True, type='float', string='Uninvoiced amount'),
 257                 'ca_theorical': fields.function(_ca_theorical_calc, method=True, type='float', string='Theorical revenue'),
 258                 'hours_quantity': fields.function(_hours_quantity_calc, method=True, type='float', string='Hours tot'),
 259                 'last_invoice_date': fields.function(_last_invoice_date_calc, method=True, type='date', string='Last invoice date'),
 260                 'last_worked_invoiced_date': fields.function(_last_worked_invoiced_date_calc, method=True, type='date', string='Last invoiced worked date'),
 261                 'last_worked_date': fields.function(_last_worked_date_calc, method=True, type='date', string='Last worked date'),
 262                 'hours_qtt_non_invoiced': fields.function(_hours_qtt_non_invoiced_calc, method=True, type='float', string='Uninvoiced hours'),
 263                 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, method=True, type='float', string='Invoiced hours'),
 264                 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining hours'),
 265                 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining revenue'),
 266                 'revenue_per_hour': fields.function(_revenue_per_hour_calc, method=True, type='float', string='Revenue per hours (real)'),
 267                 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real margin'),
 268                 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical margin'),
 269                 'real_margin_rate': fields.function(_real_margin_rate_calc, method=True, type='float', string='Real margin rate (%)'),
 270                 'month_ids': fields.one2many('account_analytic_analysis.summary.month', 'account_id', 'Month', readonly=True),
 271                 'user_ids': fields.one2many('account_analytic_analysis.summary.user', 'account_id', 'User', readonly=True),
 272         }
 273 account_analytic_account()
 274
 275 class account_analytic_account_summary_user(osv.osv):
 276         _name = "account_analytic_analysis.summary.user"
 277         _description = "Hours summary by user"
 278         _order='name'
 279         _auto = False
 280         _columns = {
 281                 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 282                 'unit_amount': fields.float('Total Time', digits=(16,2), readonly=True),
 283                 'name' : fields.many2one('res.users','User'),
 284         }
 285         def init(self, cr):
 286                 cr.execute("""
 287                         create or replace view account_analytic_analysis_summary_user as (
 288                                 select
 289                                         id,
 290                                         unit_amount,
 291                                         account_id,
 292                                         name from (
 293                                                 select
 294                                                         min(account_analytic_line.id) as id,
 295                                                         user_id as name,
 296                                                         account_id,
 297                                                         sum(unit_amount) as unit_amount
 298                                                 from
 299                                                         account_analytic_line
 300                                                 join
 301                                                         account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
 302                                                 where
 303                                                         account_analytic_journal.type = 'general'
 304                                                 group by
 305                                                         account_id, user_id
 306                                                 order by
 307                                                         user_id,account_id asc )as
 308                                         sous_account_analytic_analysis_summary_user
 309                                         order by
 310                                                 name,account_id)""")
 311 account_analytic_account_summary_user()
 312
 313 class account_analytic_account_summary_month(osv.osv):
 314         _name = "account_analytic_analysis.summary.month"
 315         _description = "Hours summary by month"
 316         _auto = False
 317         _columns = {
 318                 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 319                 'unit_amount': fields.float('Total Time', digits=(16,2), readonly=True),
 320                 'name': fields.char('Month', size=25, readonly=True),
 321         }
 322         def init(self, cr):
 323                 cr.execute("""create or replace view account_analytic_analysis_summary_month as (
 324                         select id, unit_amount,account_id, sort_month,month as name from (
 325                         select
 326                                 min(account_analytic_line.id) as id,
 327                                 date_trunc('month', date) as sort_month,
 328                                 account_id,
 329                                 to_char(date,'Mon YYYY') as month,
 330                                 sum(unit_amount) as unit_amount
 331                         from
 332                                 account_analytic_line join account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
 333                         where
 334                                 account_analytic_journal.type = 'general'
 335                         group by
 336                                 sort_month, month, account_id
 337                         order by
 338                                 sort_month,account_id asc
 339                 )as sous_account_analytic_analysis_summary_month order by sort_month,account_id)""")
 340
 341 account_analytic_account_summary_month()
 342