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