addons/account_analytic_analysis/account_analytic_analysis.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
   6 #
   7 #    This program is free software: you can redistribute it and/or modify
   8 #    it under the terms of the GNU Affero General Public License as
   9 #    published by the Free Software Foundation, either version 3 of the
  10 #    License, or (at your option) any later version.
  11 #
  12 #    This program is distributed in the hope that it will be useful,
  13 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 #    GNU Affero General Public License for more details.
  16 #
  17 #    You should have received a copy of the GNU Affero General Public License
  18 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  19 #
  20 ##############################################################################
  21
  22 from osv import osv, fields
  23 from osv.orm import intersect, except_orm
  24 import tools.sql
  25 from tools.translate import _
  26 from decimal_precision import decimal_precision as dp
  27
  28
  29 class account_analytic_account(osv.osv):
  30     _name = "account.analytic.account"
  31     _inherit = "account.analytic.account"
  32
  33     def _analysis_all(self, cr, uid, ids, fields, arg, context=None):
  34         dp = 2
  35         res = dict([(i, {}) for i in ids])
  36         parent_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
  37         accounts = self.browse(cr, uid, ids, context=context)
  38
  39         for f in fields:
  40             if f == 'user_ids':
  41                 cr.execute('SELECT MAX(id) FROM res_users')
  42                 max_user = cr.fetchone()[0]
  43                 if parent_ids:
  44                     cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
  45                                'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
  46                     result = cr.fetchall()
  47                 else:
  48                     result = []
  49                 for id in ids:
  50                     res[id][f] = [int((id * max_user) + x[0]) for x in result]
  51             elif f == 'month_ids':
  52                 if parent_ids:
  53                     cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
  54                                'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
  55                     result = cr.fetchall()
  56                 else:
  57                     result = []
  58                 for id in ids:
  59                     res[id][f] = [int(id * 1000000 + int(x[0])) for x in result]
  60             elif f == 'last_worked_invoiced_date':
  61                 for id in ids:
  62                     res[id][f] = False
  63                 if parent_ids:
  64                     cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
  65                             FROM account_analytic_line \
  66                             WHERE account_id IN %s \
  67                                 AND invoice_id IS NOT NULL \
  68                             GROUP BY account_analytic_line.account_id;", (parent_ids,))
  69                     for account_id, sum in cr.fetchall():
  70                         if account_id not in res:
  71                             res[account_id] = {}
  72                         res[account_id][f] = sum
  73             elif f == 'ca_to_invoice':
  74                 for id in ids:
  75                     res[id][f] = 0.0
  76                 res2 = {}
  77                 if parent_ids:
  78                     # Amount uninvoiced hours to invoice at sale price
  79                     # Warning
  80                     # This computation doesn't take care of pricelist !
  81                     # Just consider list_price
  82                     cr.execute("""SELECT account_analytic_account.id, \
  83                                 COALESCE(SUM (product_template.list_price * \
  84                                     account_analytic_line.unit_amount * \
  85                                     ((100-hr_timesheet_invoice_factor.factor)/100)), 0.0) \
  86                                     AS ca_to_invoice \
  87                             FROM product_template \
  88                             JOIN product_product \
  89                                 ON product_template.id = product_product.product_tmpl_id \
  90                             JOIN account_analytic_line \
  91                                 ON account_analytic_line.product_id = product_product.id \
  92                             JOIN account_analytic_journal \
  93                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
  94                             JOIN account_analytic_account \
  95                                 ON account_analytic_account.id = account_analytic_line.account_id \
  96                             JOIN hr_timesheet_invoice_factor \
  97                                 ON hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
  98                             WHERE account_analytic_account.id IN %s \
  99                                 AND account_analytic_line.invoice_id IS NULL \
 100                                 AND account_analytic_line.to_invoice IS NOT NULL \
 101                                 AND account_analytic_journal.type = 'general' \
 102                             GROUP BY account_analytic_account.id;""", (parent_ids,))
 103                     for account_id, sum in cr.fetchall():
 104                         if account_id not in res:
 105                             res[account_id] = {}
 106                         res[account_id][f] = round(sum, dp)
 107
 108                 # sum both result on account_id
 109                 for id in ids:
 110                     res[id][f] = round(res.get(id, {}).get(f, 0.0), dp) + round(res2.get(id, 0.0), 2)
 111             elif f == 'last_invoice_date':
 112                 for id in ids:
 113                     res[id][f] = False
 114                 if parent_ids:
 115                     cr.execute ("SELECT account_analytic_line.account_id, \
 116                                 DATE(MAX(account_invoice.date_invoice)) \
 117                             FROM account_analytic_line \
 118                             JOIN account_invoice \
 119                                 ON account_analytic_line.invoice_id = account_invoice.id \
 120                             WHERE account_analytic_line.account_id IN %s \
 121                                 AND account_analytic_line.invoice_id IS NOT NULL \
 122                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 123                     for account_id, lid in cr.fetchall():
 124                         res[account_id][f] = lid
 125             elif f == 'last_worked_date':
 126                 for id in ids:
 127                     res[id][f] = False
 128                 if parent_ids:
 129                     cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
 130                             FROM account_analytic_line \
 131                             WHERE account_id IN %s \
 132                                 AND invoice_id IS NULL \
 133                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 134                     for account_id, lwd in cr.fetchall():
 135                         if account_id not in res:
 136                             res[account_id] = {}
 137                         res[account_id][f] = lwd
 138             elif f == 'hours_qtt_non_invoiced':
 139                 for id in ids:
 140                     res[id][f] = 0.0
 141                 if parent_ids:
 142                     cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
 143                             FROM account_analytic_line \
 144                             JOIN account_analytic_journal \
 145                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
 146                             WHERE account_analytic_line.account_id IN %s \
 147                                 AND account_analytic_journal.type='general' \
 148                                 AND invoice_id IS NULL \
 149                                 AND to_invoice IS NOT NULL \
 150                             GROUP BY account_analytic_line.account_id;",(parent_ids,))
 151                     for account_id, sua in cr.fetchall():
 152                         if account_id not in res:
 153                             res[account_id] = {}
 154                         res[account_id][f] = round(sua, dp)
 155                 for id in ids:
 156                     res[id][f] = round(res[id][f], dp)
 157             elif f == 'hours_quantity':
 158                 for id in ids:
 159                     res[id][f] = 0.0
 160                 if parent_ids:
 161                     cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
 162                             FROM account_analytic_line \
 163                             JOIN account_analytic_journal \
 164                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
 165                             WHERE account_analytic_line.account_id IN %s \
 166                                 AND account_analytic_journal.type='general' \
 167                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 168                     ff =  cr.fetchall()
 169                     for account_id, hq in ff:
 170                         if account_id not in res:
 171                             res[account_id] = {}
 172                         res[account_id][f] = round(hq, dp)
 173                 for id in ids:
 174                     res[id][f] = round(res[id][f], dp)
 175             elif f == 'ca_theorical':
 176                 # TODO Take care of pricelist and purchase !
 177                 for id in ids:
 178                     res[id][f] = 0.0
 179                 # Warning
 180                 # This computation doesn't take care of pricelist !
 181                 # Just consider list_price
 182                 if parent_ids:
 183                     cr.execute("""SELECT account_analytic_line.account_id AS account_id, \
 184                                 COALESCE(SUM((account_analytic_line.unit_amount * pt.list_price) \
 185                                     - (account_analytic_line.unit_amount * pt.list_price \
 186                                         * hr.factor)), 0.0) AS somme
 187                             FROM account_analytic_line \
 188                             LEFT JOIN account_analytic_journal \
 189                                 ON (account_analytic_line.journal_id = account_analytic_journal.id) \
 190                             JOIN product_product pp \
 191                                 ON (account_analytic_line.product_id = pp.id) \
 192                             JOIN product_template pt \
 193                                 ON (pp.product_tmpl_id = pt.id) \
 194                             JOIN account_analytic_account a \
 195                                 ON (a.id=account_analytic_line.account_id) \
 196                             JOIN hr_timesheet_invoice_factor hr \
 197                                 ON (hr.id=a.to_invoice) \
 198                         WHERE account_analytic_line.account_id IN %s \
 199                             AND a.to_invoice IS NOT NULL \
 200                             AND account_analytic_journal.type IN ('purchase', 'general')
 201                         GROUP BY account_analytic_line.account_id""",(parent_ids,))
 202                     for account_id, sum in cr.fetchall():
 203                         res[account_id][f] = round(sum, dp)
 204         return res
 205
 206     def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 207         res = {}
 208         res_final = {}
 209         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 210         for i in child_ids:
 211             res[i] =  0.0
 212         if not child_ids:
 213             return res
 214
 215         if child_ids:
 216             cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
 217                     FROM account_analytic_line \
 218                     JOIN account_analytic_journal \
 219                         ON account_analytic_line.journal_id = account_analytic_journal.id  \
 220                     WHERE account_analytic_line.account_id IN %s \
 221                         AND account_analytic_journal.type = 'sale' \
 222                     GROUP BY account_analytic_line.account_id", (child_ids,))
 223             for account_id, sum in cr.fetchall():
 224                 res[account_id] = round(sum,2)
 225         res_final = res
 226         return res_final
 227
 228     def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
 229         res = {}
 230         res_final = {}
 231         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 232         for i in child_ids:
 233             res[i] =  0.0
 234         if not child_ids:
 235             return res
 236         if child_ids:
 237             cr.execute("""SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
 238                     FROM account_analytic_line \
 239                     JOIN account_analytic_journal \
 240                         ON account_analytic_line.journal_id = account_analytic_journal.id \
 241                     WHERE account_analytic_line.account_id IN %s \
 242                         AND amount<0 \
 243                     GROUP BY account_analytic_line.account_id""",(child_ids,))
 244             for account_id, sum in cr.fetchall():
 245                 res[account_id] = round(sum,2)
 246         res_final = res
 247         return res_final
 248
 249     def _remaining_hours_calc(self, cr, uid, ids, name, arg, context=None):
 250         res = {}
 251         for account in self.browse(cr, uid, ids, context=context):
 252             if account.quantity_max != 0:
 253                 res[account.id] = account.quantity_max - account.hours_quantity
 254             else:
 255                 res[account.id] = 0.0
 256         for id in ids:
 257             res[id] = round(res.get(id, 0.0),2)
 258         return res
 259
 260     def _remaining_hours_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
 261         res = {}
 262         for account in self.browse(cr, uid, ids, context=context):
 263             res[account.id] = max(account.hours_qtt_est - account.timesheet_ca_invoiced, account.ca_to_invoice)
 264         return res
 265
 266     def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 267         res = {}
 268         for account in self.browse(cr, uid, ids, context=context):
 269             res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
 270             if res[account.id] < 0:
 271                 res[account.id] = 0.0
 272         for id in ids:
 273             res[id] = round(res.get(id, 0.0),2)
 274         return res
 275
 276     def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
 277         res = {}
 278         for account in self.browse(cr, uid, ids, context=context):
 279             if account.hours_qtt_invoiced == 0:
 280                 res[account.id]=0.0
 281             else:
 282                 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
 283         for id in ids:
 284             res[id] = round(res.get(id, 0.0),2)
 285         return res
 286
 287     def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
 288         res = {}
 289         for account in self.browse(cr, uid, ids, context=context):
 290             if account.ca_invoiced == 0:
 291                 res[account.id]=0.0
 292             elif account.total_cost != 0.0:
 293                 res[account.id] = -(account.real_margin / account.total_cost) * 100
 294             else:
 295                 res[account.id] = 0.0
 296         for id in ids:
 297             res[id] = round(res.get(id, 0.0),2)
 298         return res
 299
 300     def _fix_price_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
 301         sale_obj = self.pool.get('sale.order')
 302         res = {}
 303         for account in self.browse(cr, uid, ids, context=context):
 304             res[account.id] = 0.0
 305             sale_ids = sale_obj.search(cr, uid, [('project_id','=', account.id), ('partner_id', '=', account.partner_id.id)], context=context)
 306             for sale in sale_obj.browse(cr, uid, sale_ids, context=context):
 307                 if not sale.invoiced:
 308                     res[account.id] += sale.amount_untaxed
 309                     for invoice in sale.invoice_ids:
 310                         if invoice.state not in ('draft', 'cancel'):
 311                             res[account.id] -= invoice.amount_untaxed
 312         return res
 313
 314     def _timesheet_ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 315         lines_obj = self.pool.get('account.analytic.line')
 316         res = {}
 317         for account in self.browse(cr, uid, ids, context=context):
 318             res[account.id] = 0.0
 319             line_ids = lines_obj.search(cr, uid, [('account_id','=', account.id), ('invoice_id','!=',False), ('to_invoice','!=', False), ('journal_id.type', '=', 'general')], context=context)
 320             for line in lines_obj.browse(cr, uid, line_ids, context=context):
 321                 res[account.id] += line.invoice_id.amount_untaxed
 322         return res
 323
 324     def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
 325         res = {}
 326         for account in self.browse(cr, uid, ids, context=context):
 327             res[account.id] = max(account.amount_max - account.ca_invoiced, account.fix_price_to_invoice)
 328         return res
 329
 330     def _real_margin_calc(self, cr, uid, ids, name, arg, context=None):
 331         res = {}
 332         for account in self.browse(cr, uid, ids, context=context):
 333             res[account.id] = account.ca_invoiced + account.total_cost
 334         for id in ids:
 335             res[id] = round(res.get(id, 0.0),2)
 336         return res
 337
 338     def _theorical_margin_calc(self, cr, uid, ids, name, arg, context=None):
 339         res = {}
 340         for account in self.browse(cr, uid, ids, context=context):
 341             res[account.id] = account.ca_theorical + account.total_cost
 342         for id in ids:
 343             res[id] = round(res.get(id, 0.0),2)
 344         return res
 345
 346     def _is_overdue_quantity(self, cr, uid, ids, fieldnames, args, context=None):
 347         result = dict.fromkeys(ids, 0)
 348         for record in self.browse(cr, uid, ids, context=context):
 349             if record.quantity_max > 0.0:
 350                 result[record.id] = int(record.hours_quantity >= record.quantity_max)
 351             else:
 352                 result[record.id] = 0
 353         return result
 354
 355     def _get_analytic_account(self, cr, uid, ids, context=None):
 356         result = set()
 357         for line in self.pool.get('account.analytic.line').browse(cr, uid, ids, context=context):
 358             result.add(line.account_id.id)
 359         return list(result)
 360
 361     def _get_total_estimation(self, account):
 362         tot_est = 0.0
 363         if account.fix_price_invoices:
 364             tot_est += account.amount_max
 365         if account.invoice_on_timesheets:
 366             tot_est += account.hours_qtt_est
 367         return tot_est
 368
 369     def _get_total_invoiced(self, account):
 370         total_invoiced = 0.0
 371         if account.fix_price_invoices:
 372             total_invoiced += account.ca_invoiced
 373         if account.invoice_on_timesheets:
 374             total_invoiced += account.timesheet_ca_invoiced
 375         return total_invoiced
 376
 377     def _get_total_remaining(self, account):
 378         total_remaining = 0.0
 379         if account.fix_price_invoices:
 380             total_remaining += account.remaining_ca
 381         if account.invoice_on_timesheets:
 382             total_remaining += account.remaining_hours_to_invoice
 383         return total_remaining
 384
 385     def _get_total_toinvoice(self, account):
 386         total_toinvoice = 0.0
 387         if account.fix_price_invoices:
 388             total_toinvoice += account.fix_price_to_invoice
 389         if account.invoice_on_timesheets:
 390             total_toinvoice += account.ca_to_invoice
 391         return total_toinvoice
 392
 393     def _sum_of_fields(self, cr, uid, ids, name, arg, context=None):
 394          res = dict([(i, {}) for i in ids])
 395          for account in self.browse(cr, uid, ids, context=context):
 396             res[account.id]['est_total'] = self._get_total_estimation(account)
 397             res[account.id]['invoiced_total'] =  self._get_total_invoiced(account)
 398             res[account.id]['remaining_total'] = self._get_total_remaining(account)
 399             res[account.id]['toinvoice_total'] =  self._get_total_toinvoice(account)
 400          return res
 401
 402     _columns = {
 403         'is_overdue_quantity' : fields.function(_is_overdue_quantity, method=True, type='boolean', string='Overdue Quantity',
 404                                                 store={
 405                                                     'account.analytic.line' : (_get_analytic_account, None, 20),
 406                                                 }),
 407         'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
 408             help="Total customer invoiced amount for this account.",
 409             digits_compute=dp.get_precision('Account')),
 410         'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
 411             help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
 412             digits_compute=dp.get_precision('Account')),
 413         'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
 414             help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
 415             digits_compute=dp.get_precision('Account')),
 416         'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
 417             help="Based on the costs you had on the project, what would have been the revenue if all these costs have been invoiced at the normal sale price provided by the pricelist.",
 418             digits_compute=dp.get_precision('Account')),
 419         'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Worked Time',
 420             help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
 421         'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
 422             help="If invoice from the costs, this is the date of the latest invoiced."),
 423         'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
 424             help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
 425         'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
 426             help="Date of the latest work done on this account."),
 427         'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
 428             help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
 429         'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
 430             help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
 431         'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
 432             help="Computed using the formula: Maximum Time - Total Worked Time"),
 433         'remaining_hours_to_invoice': fields.function(_remaining_hours_to_invoice_calc, type='float', string='Remaining Time',
 434             help="Computed using the formula: Maximum Time - Total Invoiced Time"),
 435         'fix_price_to_invoice': fields.function(_fix_price_to_invoice_calc, type='float', string='Remaining Time',
 436             help="Sum of quotations for this contract."),
 437         'timesheet_ca_invoiced': fields.function(_timesheet_ca_invoiced_calc, type='float', string='Remaining Time',
 438             help="Sum of timesheet lines invoiced for this contract."),
 439         'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
 440             help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
 441             digits_compute=dp.get_precision('Account')),
 442         'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
 443             help="Computed using the formula: Invoiced Amount / Total Time",
 444             digits_compute=dp.get_precision('Account')),
 445         'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
 446             help="Computed using the formula: Invoiced Amount - Total Costs.",
 447             digits_compute=dp.get_precision('Account')),
 448         'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
 449             help="Computed using the formula: Theoretical Revenue - Total Costs",
 450             digits_compute=dp.get_precision('Account')),
 451         'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
 452             help="Computes using the formula: (Real Margin / Total Costs) * 100.",
 453             digits_compute=dp.get_precision('Account')),
 454         'fix_price_invoices' : fields.boolean('Fix Price Invoices'),
 455         'invoice_on_timesheets' : fields.boolean("Invoice On Timesheets"),
 456         'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
 457         'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
 458         'hours_qtt_est': fields.float('Estimation of Hours to Invoice'),
 459         'est_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all", string="Total Estimation"),
 460         'invoiced_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all", string="Total Invoiced"),
 461         'remaining_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all", string="Total Remaining", help="Expectation of remaining income for this contract. Computed as the sum of remaining subtotals which, in turn, are computed as the maximum between '(Estimation - Invoiced)' and 'To Invoice' amounts"),
 462         'toinvoice_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all", string="Total to Invoice", help=" Sum of everything that could be invoiced for this contract."),
 463     }
 464
 465     def open_sale_order_lines(self,cr,uid,ids,context=None):
 466         if context is None:
 467             context = {}
 468         sale_ids = self.pool.get('sale.order').search(cr,uid,[('project_id','=',context.get('search_default_project_id',False)),('partner_id','=',context.get('search_default_partner_id',False))])
 469         names = [record.name for record in self.browse(cr, uid, ids, context=context)]
 470         name = _('Sale Order Lines of %s') % ','.join(names)
 471         return {
 472             'type': 'ir.actions.act_window',
 473             'name': name,
 474             'view_type': 'form',
 475             'view_mode': 'tree,form',
 476             'context': context,
 477             'domain' : [('order_id','in',sale_ids)],
 478             'res_model': 'sale.order.line',
 479             'nodestroy': True,
 480         }
 481
 482     def on_change_template(self, cr, uid, ids, template_id, context=None):
 483         if not template_id:
 484             return {}
 485         res = super(account_analytic_account, self).on_change_template(cr, uid, ids, template_id, context=context)
 486         if template_id and 'value' in res:
 487             template = self.browse(cr, uid, template_id, context=context)
 488             res['value']['fix_price_invoices'] = template.fix_price_invoices
 489             res['value']['invoice_on_timesheets'] = template.invoice_on_timesheets
 490             res['value']['hours_qtt_est'] = template.hours_qtt_est
 491             res['value']['amount_max'] = template.amount_max
 492             res['value']['to_invoice'] = template.to_invoice.id
 493             res['value']['pricelist_id'] = template.pricelist_id.id
 494         return res
 495 account_analytic_account()
 496
 497 class account_analytic_account_summary_user(osv.osv):
 498     _name = "account_analytic_analysis.summary.user"
 499     _description = "Hours Summary by User"
 500     _order='user'
 501     _auto = False
 502     _rec_name = 'user'
 503
 504     def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 505         res = {}
 506         account_obj = self.pool.get('account.analytic.account')
 507         cr.execute('SELECT MAX(id) FROM res_users')
 508         max_user = cr.fetchone()[0]
 509         account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
 510         user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
 511         parent_ids = tuple(account_ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 512         if parent_ids:
 513             cr.execute('SELECT id, unit_amount ' \
 514                     'FROM account_analytic_analysis_summary_user ' \
 515                     'WHERE account_id IN %s ' \
 516                         'AND "user" IN %s',(parent_ids, tuple(user_ids),))
 517             for sum_id, unit_amount in cr.fetchall():
 518                 res[sum_id] = unit_amount
 519         for id in ids:
 520             res[id] = round(res.get(id, 0.0), 2)
 521         return res
 522
 523     _columns = {
 524         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 525         'unit_amount': fields.float('Total Time'),
 526         'user': fields.many2one('res.users', 'User'),
 527     }
 528
 529     def init(self, cr):
 530         tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
 531         cr.execute('''CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (
 532             with mu as
 533                 (select max(id) as max_user from res_users)
 534             , lu AS
 535                 (SELECT
 536                  l.account_id AS account_id,
 537                  coalesce(l.user_id, 0) AS user_id,
 538                  SUM(l.unit_amount) AS unit_amount
 539              FROM account_analytic_line AS l,
 540                  account_analytic_journal AS j
 541              WHERE (j.type = 'general' ) and (j.id=l.journal_id)
 542              GROUP BY l.account_id, l.user_id
 543             )
 544             select (lu.account_id * mu.max_user) + lu.user_id as id,
 545                     lu.account_id as account_id,
 546                     lu.user_id as "user",
 547                     unit_amount
 548             from lu, mu)''')
 549
 550 account_analytic_account_summary_user()
 551
 552 class account_analytic_account_summary_month(osv.osv):
 553     _name = "account_analytic_analysis.summary.month"
 554     _description = "Hours summary by month"
 555     _auto = False
 556     _rec_name = 'month'
 557
 558     _columns = {
 559         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 560         'unit_amount': fields.float('Total Time'),
 561         'month': fields.char('Month', size=32, readonly=True),
 562     }
 563
 564     def init(self, cr):
 565         tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
 566         cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
 567                 'SELECT ' \
 568                     '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id  * 1000000::bigint))::bigint AS id, ' \
 569                     'd.account_id AS account_id, ' \
 570                     'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
 571                     'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
 572                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 573                 'FROM ' \
 574                     '(SELECT ' \
 575                         'd2.account_id, ' \
 576                         'd2.month ' \
 577                     'FROM ' \
 578                         '(SELECT ' \
 579                             'a.id AS account_id, ' \
 580                             'l.month AS month ' \
 581                         'FROM ' \
 582                             '(SELECT ' \
 583                                 'DATE_TRUNC(\'month\', l.date) AS month ' \
 584                             'FROM account_analytic_line AS l, ' \
 585                                 'account_analytic_journal AS j ' \
 586                             'WHERE j.type = \'general\' ' \
 587                             'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
 588                             ') AS l, ' \
 589                             'account_analytic_account AS a ' \
 590                         'GROUP BY l.month, a.id ' \
 591                         ') AS d2 ' \
 592                     'GROUP BY d2.account_id, d2.month ' \
 593                     ') AS d ' \
 594                 'LEFT JOIN ' \
 595                     '(SELECT ' \
 596                         'l.account_id AS account_id, ' \
 597                         'DATE_TRUNC(\'month\', l.date) AS month, ' \
 598                         'SUM(l.unit_amount) AS unit_amount ' \
 599                     'FROM account_analytic_line AS l, ' \
 600                         'account_analytic_journal AS j ' \
 601                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 602                     'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
 603                     ') AS l '
 604                     'ON (' \
 605                         'd.account_id = l.account_id ' \
 606                         'AND d.month = l.month' \
 607                     ') ' \
 608                 'GROUP BY d.month, d.account_id ' \
 609                 ')')
 610
 611
 612 account_analytic_account_summary_month()
 613 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: