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