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         # OK !!!
  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         # OK !!!
  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                 # Montant des heures non-facturÃ©es Ã  facturer au prix de vente :
  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                 # Montant des expense et facture d'achat :
  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' OR lower(account_analytic_journal.name) like 'expense%%') 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         # OK !!!!
  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         # OK !!!!
  90         def _hours_quantity_calc(self, cr, uid, ids, name, arg, context={}):
  91                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
  92                 acc_set = ",".join(map(str, ids2))
  93                 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)
  94                 res = {}
  95                 for account_id, sum in cr.fetchall():
  96                         res[account_id] = round(sum,2)
  97                 for id in ids:
  98                         res[id] = round(res.get(id, 0.0),2)
  99                 return res
 100
 101
 102         # OK !!!
 103         def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
 104                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 105                 acc_set = ",".join(map(str, ids2))
 106                 cr.execute("""select
 107                  account_analytic_line.account_id,sum(amount)
 108                 from
 109                  account_analytic_line join account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
 110                 where
 111                  account_analytic_line.account_id IN (%s) and amount<0
 112                 GROUP BY
 113                  account_analytic_line.account_id"""%acc_set)
 114                 res = {}
 115                 for account_id, sum in cr.fetchall():
 116                         res[account_id] = round(sum,2)
 117                 for id in ids:
 118                         res[id] = round(res.get(id, 0.0),2)
 119                 return res
 120         # OK !!!
 121         def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
 122                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 123                 acc_set = ",".join(map(str, ids2))
 124                 # First part with expense and purchase
 125                 cr.execute("""select
 126                                                 account_analytic_line.account_id,sum(amount)
 127                                         from
 128                                                 account_analytic_line join account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
 129                                         where
 130                                                 account_analytic_line.account_id IN (%s) and (lower(account_analytic_journal.name) like 'expense%%' or account_analytic_journal.type like 'purchase')
 131                                         GROUP BY
 132                                                 account_analytic_line.account_id"""%acc_set)
 133                 res = {}
 134                 for account_id, sum in cr.fetchall():
 135                         res[account_id] = round(sum,2)
 136                 # Second part with timesheet (with invoice factor)
 137                 acc_set = ",".join(map(str, ids2))
 138                 cr.execute("""
 139                                 select
 140                                         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
 141                                 from
 142                                         account_analytic_line join account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
 143                                         join product_product pp on (account_analytic_line.product_id = pp.id)
 144                                         join product_template pt on (pp.product_tmpl_id = pt.id)
 145                                         join account_analytic_account a on (a.id=account_analytic_line.account_id)
 146                                         join hr_timesheet_invoice_factor hr on (hr.id=a.to_invoice)
 147                                 where
 148                                         account_analytic_line.account_id IN (%s) and account_analytic_journal.type='general' and a.to_invoice IS NOT NULL
 149                                 GROUP BY
 150                                         account_analytic_line.account_id"""%acc_set)
 151                 res2 = {}
 152                 for account_id, sum in cr.fetchall():
 153                         res2[account_id] = round(sum,2)
 154                 # sum both result on account_id
 155                 for id in ids:
 156                         res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
 157                 return res
 158
 159         # OK !!!
 160         def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
 161                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 162                 acc_set = ",".join(map(str, ids2))
 163                 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)
 164                 res = {}
 165                 for account_id, sum in cr.fetchall():
 166                         res[account_id] = sum
 167                 for id in ids:
 168                         res[id] = res.get(id, '')
 169                 return res
 170
 171         # OK !!!
 172         def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
 173                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 174                 acc_set = ",".join(map(str, ids2))
 175                 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)
 176                 res = {}
 177                 for account_id, sum in cr.fetchall():
 178                         res[account_id] = sum
 179                 for id in ids:
 180                         res[id] = res.get(id, '')
 181                 return res
 182
 183         # OK !!!
 184         def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
 185                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 186                 acc_set = ",".join(map(str, ids2))
 187                 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)
 188                 res = {}
 189                 for account_id, sum in cr.fetchall():
 190                         res[account_id] = sum
 191                 for id in ids:
 192                         res[id] = res.get(id, '')
 193                 return res
 194
 195         def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
 196                 res = {}
 197                 for account in self.browse(cr, uid, ids):
 198                         if account.quantity_max <> 0:
 199                                 res[account.id] = account.quantity_max - account.hours_quantity
 200                         else:
 201                                 res[account.id]=0.0
 202                 for id in ids:
 203                         res[id] = round(res.get(id, 0.0),2)
 204                 return res
 205         def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
 206                 res = {}
 207                 for account in self.browse(cr, uid, ids):
 208                         res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
 209                         if res[account.id] < 0:
 210                                 res[account.id]=0.0
 211                 for id in ids:
 212                         res[id] = round(res.get(id, 0.0),2)
 213                 return res
 214         def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context={}):
 215                 res = {}
 216                 for account in self.browse(cr, uid, ids):
 217                         if account.hours_qtt_invoiced == 0:
 218                                 res[account.id]=0.0
 219                         else:
 220                                 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
 221                 for id in ids:
 222                         res[id] = round(res.get(id, 0.0),2)
 223                 return res
 224         def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context={}):
 225                 res = {}
 226                 for account in self.browse(cr, uid, ids):
 227                         if account.ca_invoiced == 0:
 228                                 res[account.id]=0.0
 229                         else:
 230                                 res[account.id] = (account.real_margin / account.ca_invoiced) * 100
 231                 for id in ids:
 232                         res[id] = round(res.get(id, 0.0),2)
 233                 return res
 234         def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
 235                 res = {}
 236                 for account in self.browse(cr, uid, ids):
 237                         if account.amount_max <> 0:
 238                                 res[account.id] = account.amount_max - account.ca_invoiced
 239                         else:
 240                                 res[account.id]=0.0
 241                 for id in ids:
 242                         res[id] = round(res.get(id, 0.0),2)
 243                 return res
 244         def _real_margin_calc(self, cr, uid, ids, name, arg, context={}):
 245                 res = {}
 246                 for account in self.browse(cr, uid, ids):
 247                         res[account.id] = account.ca_invoiced + account.total_cost
 248                 for id in ids:
 249                         res[id] = round(res.get(id, 0.0),2)
 250                 return res
 251         def _theorical_margin_calc(self, cr, uid, ids, name, arg, context={}):
 252                 res = {}
 253                 for account in self.browse(cr, uid, ids):
 254                         res[account.id] = account.ca_theorical + account.total_cost
 255                 for id in ids:
 256                         res[id] = round(res.get(id, 0.0),2)
 257                 return res
 258
 259         _columns ={
 260                 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced amount'),
 261                 'total_cost': fields.function(_total_cost_calc, method=True, type='float', string='Total cost'),
 262                 'ca_to_invoice': fields.function(_ca_to_invoice_calc, method=True, type='float', string='Uninvoiced amount'),
 263                 'ca_theorical': fields.function(_ca_theorical_calc, method=True, type='float', string='Theorical revenue'),
 264                 'hours_quantity': fields.function(_hours_quantity_calc, method=True, type='float', string='Hours tot'),
 265                 'last_invoice_date': fields.function(_last_invoice_date_calc, method=True, type='date', string='Last invoice date'),
 266                 'last_worked_invoiced_date': fields.function(_last_worked_invoiced_date_calc, method=True, type='date', string='Last invoiced worked date'),
 267                 'last_worked_date': fields.function(_last_worked_date_calc, method=True, type='date', string='Last worked date'),
 268                 'hours_qtt_non_invoiced': fields.function(_hours_qtt_non_invoiced_calc, method=True, type='float', string='Uninvoiced hours'),
 269                 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, method=True, type='float', string='Invoiced hours'),
 270                 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining hours'),
 271                 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining revenue'),
 272                 'revenue_per_hour': fields.function(_revenue_per_hour_calc, method=True, type='float', string='Revenue per hours (real)'),
 273                 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real margin'),
 274                 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical margin'),
 275                 'real_margin_rate': fields.function(_real_margin_rate_calc, method=True, type='float', string='Real margin rate (%)'),
 276                 'month_ids': fields.one2many('account_analytic_analysis.summary.month', 'account_id', 'Month', readonly=True),
 277                 'user_ids': fields.one2many('account_analytic_analysis.summary.user', 'account_id', 'User', readonly=True),
 278         }
 279 account_analytic_account()
 280
 281 class account_analytic_account_summary_user(osv.osv):
 282         _name = "account_analytic_analysis.summary.user"
 283         _description = "Hours summary by user"
 284         _order='name'
 285         _auto = False
 286         _columns = {
 287                 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 288                 'unit_amount': fields.float('Total Time', digits=(16,2), readonly=True),
 289                 'name' : fields.many2one('res.users','User'),
 290         }
 291         def init(self, cr):
 292                 cr.execute("""
 293                         create or replace view account_analytic_analysis_summary_user as (
 294                                 select
 295                                         id,
 296                                         unit_amount,
 297                                         account_id,
 298                                         name from (
 299                                                 select
 300                                                         min(account_analytic_line.id) as id,
 301                                                         user_id as name,
 302                                                         account_id,
 303                                                         sum(unit_amount) as unit_amount
 304                                                 from
 305                                                         account_analytic_line
 306                                                 join
 307                                                         account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
 308                                                 where
 309                                                         account_analytic_journal.type = 'general'
 310                                                 group by
 311                                                         account_id, user_id
 312                                                 order by
 313                                                         user_id,account_id asc )as
 314                                         sous_account_analytic_analysis_summary_user
 315                                         order by
 316                                                 name,account_id)""")
 317 account_analytic_account_summary_user()
 318
 319 class account_analytic_account_summary_month(osv.osv):
 320         _name = "account_analytic_analysis.summary.month"
 321         _description = "Hours summary by month"
 322         _auto = False
 323         _columns = {
 324                 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 325                 'unit_amount': fields.float('Total Time', digits=(16,2), readonly=True),
 326                 'name': fields.char('Month', size=25, readonly=True),
 327         }
 328         def init(self, cr):
 329                 cr.execute("""create or replace view account_analytic_analysis_summary_month as (
 330                         select id, unit_amount,account_id, sort_month,month as name from (
 331                         select
 332                                 min(account_analytic_line.id) as id,
 333                                 date_trunc('month', date) as sort_month,
 334                                 account_id,
 335                                 to_char(date,'Mon YYYY') as month,
 336                                 sum(unit_amount) as unit_amount
 337                         from
 338                                 account_analytic_line join account_analytic_journal on account_analytic_line.journal_id = account_analytic_journal.id
 339                         where
 340                                 account_analytic_journal.type = 'general'
 341                         group by
 342                                 sort_month, month, account_id
 343                         order by
 344                                 sort_month,account_id asc
 345                 )as sous_account_analytic_analysis_summary_month order by sort_month,account_id)""")
 346
 347 account_analytic_account_summary_month()
 348
 349 class analytic_account_category(osv.osv):
 350         def _check_recursion(self, cr, uid, ids):
 351                 level = 100
 352                 while len(ids):
 353                         cr.execute('select distinct parent_id from account_analytic_account_category where id in ('+','.join(map(str,ids))+')')
 354                         ids = filter(None, map(lambda x:x[0], cr.fetchall()))
 355                         if not level:
 356                                 return False
 357                         level -= 1
 358                 return True
 359         def name_get(self, cr, uid, ids, context={}):
 360                 if not len(ids):
 361                         return []
 362                 reads = self.read(cr, uid, ids, ['name','parent_id'], context)
 363                 res = []
 364                 for record in reads:
 365                         name = record['name']
 366                         if record['parent_id']:
 367                                 name = record['parent_id'][1]+' / '+name
 368                         res.append((record['id'], name))
 369                 return res
 370         def _name_get_fnc(self, cr, uid, ids, prop, unknow_none, unknow_dict):
 371                 res = self.name_get(cr, uid, ids)
 372                 return dict(res)
 373         _name = "account.analytic.account.category"
 374         _columns ={
 375                 'name': fields.char('Category Name', required=True, size=64),
 376                 'parent_id': fields.many2one('account.analytic.account.category', 'Parent Category', select=True),
 377                 'complete_name': fields.function(_name_get_fnc, method=True, type="char", string='Name'),
 378                 'child_ids': fields.one2many('account.analytic.account.category', 'parent_id', 'Childs Category'),
 379                 'active' : fields.boolean('Active'),
 380         }
 381         _constraints = [
 382                 (_check_recursion, 'Error ! You can not create recursive categories.', ['parent_id'])
 383         ]
 384         _defaults = {
 385                 'active' : lambda *a: 1,
 386         }
 387 analytic_account_category()
 388
 389