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 from dateutil.relativedelta import relativedelta
  22 import datetime
  23 import logging
  24 import time
  25
  26 from openerp.osv import osv, fields
  27 from openerp.osv.orm import intersect, except_orm
  28 import openerp.tools
  29 from openerp.tools.translate import _
  30
  31 from openerp.addons.decimal_precision import decimal_precision as dp
  32
  33 _logger = logging.getLogger(__name__)
  34
  35 class account_analytic_invoice_line(osv.osv):
  36     _name = "account.analytic.invoice.line"
  37
  38     def _amount_line(self, cr, uid, ids, prop, unknow_none, unknow_dict, context=None):
  39         res = {}
  40         for line in self.browse(cr, uid, ids, context=context):
  41             res[line.id] = line.quantity * line.price_unit
  42             if line.analytic_account_id.pricelist_id:
  43                 cur = line.analytic_account_id.pricelist_id.currency_id
  44                 res[line.id] = self.pool.get('res.currency').round(cr, uid, cur, res[line.id])
  45         return res
  46
  47     _columns = {
  48         'product_id': fields.many2one('product.product','Product',required=True),
  49         'analytic_account_id': fields.many2one('account.analytic.account', 'Analytic Account'),
  50         'name': fields.text('Description', required=True),
  51         'quantity': fields.float('Quantity', required=True),
  52         'uom_id': fields.many2one('product.uom', 'Unit of Measure',required=True),
  53         'price_unit': fields.float('Unit Price', required=True),
  54         'price_subtotal': fields.function(_amount_line, string='Sub Total', type="float",digits_compute= dp.get_precision('Account')),
  55     }
  56     _defaults = {
  57         'quantity' : 1,
  58     }
  59
  60     def product_id_change(self, cr, uid, ids, product, uom_id, qty=0, name='', partner_id=False, price_unit=False, pricelist_id=False, company_id=None, context=None):
  61         context = context or {}
  62         uom_obj = self.pool.get('product.uom')
  63         company_id = company_id or False
  64         context.update({'company_id': company_id, 'force_company': company_id, 'pricelist_id': pricelist_id})
  65
  66         if not product:
  67             return {'value': {'price_unit': 0.0}, 'domain':{'product_uom':[]}}
  68         if partner_id:
  69             part = self.pool.get('res.partner').browse(cr, uid, partner_id, context=context)
  70             if part.lang:
  71                 context.update({'lang': part.lang})
  72
  73         result = {}
  74         res = self.pool.get('product.product').browse(cr, uid, product, context=context)
  75         result.update({'name':res.partner_ref or False,'uom_id': uom_id or res.uom_id.id or False, 'price_unit': res.list_price or 0.0})
  76         if res.description:
  77             result['name'] += '\n'+res.description
  78
  79         res_final = {'value':result}
  80         if result['uom_id'] != res.uom_id.id:
  81             selected_uom = uom_obj.browse(cr, uid, result['uom_id'], context=context)
  82             new_price = uom_obj._compute_price(cr, uid, res.uom_id.id, res_final['value']['price_unit'], result['uom_id'])
  83             res_final['value']['price_unit'] = new_price
  84         return res_final
  85
  86
  87 class account_analytic_account(osv.osv):
  88     _name = "account.analytic.account"
  89     _inherit = "account.analytic.account"
  90
  91     def _analysis_all(self, cr, uid, ids, fields, arg, context=None):
  92         dp = 2
  93         res = dict([(i, {}) for i in ids])
  94         parent_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
  95         accounts = self.browse(cr, uid, ids, context=context)
  96
  97         for f in fields:
  98             if f == 'user_ids':
  99                 cr.execute('SELECT MAX(id) FROM res_users')
 100                 max_user = cr.fetchone()[0]
 101                 if parent_ids:
 102                     cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
 103                                'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
 104                     result = cr.fetchall()
 105                 else:
 106                     result = []
 107                 for id in ids:
 108                     res[id][f] = [int((id * max_user) + x[0]) for x in result]
 109             elif f == 'month_ids':
 110                 if parent_ids:
 111                     cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
 112                                'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
 113                     result = cr.fetchall()
 114                 else:
 115                     result = []
 116                 for id in ids:
 117                     res[id][f] = [int(id * 1000000 + int(x[0])) for x in result]
 118             elif f == 'last_worked_invoiced_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 NOT NULL \
 126                             GROUP BY account_analytic_line.account_id;", (parent_ids,))
 127                     for account_id, sum in cr.fetchall():
 128                         if account_id not in res:
 129                             res[account_id] = {}
 130                         res[account_id][f] = sum
 131             elif f == 'ca_to_invoice':
 132                 for id in ids:
 133                     res[id][f] = 0.0
 134                 res2 = {}
 135                 for account in accounts:
 136                     cr.execute("""
 137                         SELECT product_id, sum(amount), user_id, to_invoice, sum(unit_amount), product_uom_id, line.name
 138                         FROM account_analytic_line line
 139                             LEFT JOIN account_analytic_journal journal ON (journal.id = line.journal_id)
 140                         WHERE account_id = %s
 141                             AND journal.type != 'purchase'
 142                             AND invoice_id IS NULL
 143                             AND to_invoice IS NOT NULL
 144                         GROUP BY product_id, user_id, to_invoice, product_uom_id, line.name""", (account.id,))
 145
 146                     res[account.id][f] = 0.0
 147                     for product_id, price, user_id, factor_id, qty, uom, line_name in cr.fetchall():
 148                         price = -price
 149                         if product_id:
 150                             price = self.pool.get('account.analytic.line')._get_invoice_price(cr, uid, account, product_id, user_id, qty, context)
 151                         factor = self.pool.get('hr_timesheet_invoice.factor').browse(cr, uid, factor_id, context=context)
 152                         res[account.id][f] += price * qty * (100-factor.factor or 0.0) / 100.0
 153
 154                 # sum both result on account_id
 155                 for id in ids:
 156                     res[id][f] = round(res.get(id, {}).get(f, 0.0), dp) + round(res2.get(id, 0.0), 2)
 157             elif f == 'last_invoice_date':
 158                 for id in ids:
 159                     res[id][f] = False
 160                 if parent_ids:
 161                     cr.execute ("SELECT account_analytic_line.account_id, \
 162                                 DATE(MAX(account_invoice.date_invoice)) \
 163                             FROM account_analytic_line \
 164                             JOIN account_invoice \
 165                                 ON account_analytic_line.invoice_id = account_invoice.id \
 166                             WHERE account_analytic_line.account_id IN %s \
 167                                 AND account_analytic_line.invoice_id IS NOT NULL \
 168                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 169                     for account_id, lid in cr.fetchall():
 170                         res[account_id][f] = lid
 171             elif f == 'last_worked_date':
 172                 for id in ids:
 173                     res[id][f] = False
 174                 if parent_ids:
 175                     cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
 176                             FROM account_analytic_line \
 177                             WHERE account_id IN %s \
 178                                 AND invoice_id IS NULL \
 179                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 180                     for account_id, lwd in cr.fetchall():
 181                         if account_id not in res:
 182                             res[account_id] = {}
 183                         res[account_id][f] = lwd
 184             elif f == 'hours_qtt_non_invoiced':
 185                 for id in ids:
 186                     res[id][f] = 0.0
 187                 if parent_ids:
 188                     cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
 189                             FROM account_analytic_line \
 190                             JOIN account_analytic_journal \
 191                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
 192                             WHERE account_analytic_line.account_id IN %s \
 193                                 AND account_analytic_journal.type='general' \
 194                                 AND invoice_id IS NULL \
 195                                 AND to_invoice IS NOT NULL \
 196                             GROUP BY account_analytic_line.account_id;",(parent_ids,))
 197                     for account_id, sua in cr.fetchall():
 198                         if account_id not in res:
 199                             res[account_id] = {}
 200                         res[account_id][f] = round(sua, dp)
 201                 for id in ids:
 202                     res[id][f] = round(res[id][f], dp)
 203             elif f == 'hours_quantity':
 204                 for id in ids:
 205                     res[id][f] = 0.0
 206                 if parent_ids:
 207                     cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
 208                             FROM account_analytic_line \
 209                             JOIN account_analytic_journal \
 210                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
 211                             WHERE account_analytic_line.account_id IN %s \
 212                                 AND account_analytic_journal.type='general' \
 213                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 214                     ff =  cr.fetchall()
 215                     for account_id, hq in ff:
 216                         if account_id not in res:
 217                             res[account_id] = {}
 218                         res[account_id][f] = round(hq, dp)
 219                 for id in ids:
 220                     res[id][f] = round(res[id][f], dp)
 221             elif f == 'ca_theorical':
 222                 # TODO Take care of pricelist and purchase !
 223                 for id in ids:
 224                     res[id][f] = 0.0
 225                 # Warning
 226                 # This computation doesn't take care of pricelist !
 227                 # Just consider list_price
 228                 if parent_ids:
 229                     cr.execute("""SELECT account_analytic_line.account_id AS account_id, \
 230                                 COALESCE(SUM((account_analytic_line.unit_amount * pt.list_price) \
 231                                     - (account_analytic_line.unit_amount * pt.list_price \
 232                                         * hr.factor)), 0.0) AS somme
 233                             FROM account_analytic_line \
 234                             LEFT JOIN account_analytic_journal \
 235                                 ON (account_analytic_line.journal_id = account_analytic_journal.id) \
 236                             JOIN product_product pp \
 237                                 ON (account_analytic_line.product_id = pp.id) \
 238                             JOIN product_template pt \
 239                                 ON (pp.product_tmpl_id = pt.id) \
 240                             JOIN account_analytic_account a \
 241                                 ON (a.id=account_analytic_line.account_id) \
 242                             JOIN hr_timesheet_invoice_factor hr \
 243                                 ON (hr.id=a.to_invoice) \
 244                         WHERE account_analytic_line.account_id IN %s \
 245                             AND a.to_invoice IS NOT NULL \
 246                             AND account_analytic_journal.type IN ('purchase', 'general')
 247                         GROUP BY account_analytic_line.account_id""",(parent_ids,))
 248                     for account_id, sum in cr.fetchall():
 249                         res[account_id][f] = round(sum, dp)
 250         return res
 251
 252     def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 253         res = {}
 254         res_final = {}
 255         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 256         for i in child_ids:
 257             res[i] =  0.0
 258         if not child_ids:
 259             return res
 260
 261         if child_ids:
 262             #Search all invoice lines not in cancelled state that refer to this analytic account
 263             inv_line_obj = self.pool.get("account.invoice.line")
 264             inv_lines = inv_line_obj.search(cr, uid, ['&', ('account_analytic_id', 'in', child_ids), ('invoice_id.state', '!=', 'cancel')], context=context)
 265             for line in inv_line_obj.browse(cr, uid, inv_lines, context=context):
 266                 res[line.account_analytic_id.id] += line.price_subtotal
 267         for acc in self.browse(cr, uid, res.keys(), context=context):
 268             res[acc.id] = res[acc.id] - (acc.timesheet_ca_invoiced or 0.0)
 269
 270         res_final = res
 271         return res_final
 272
 273     def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
 274         res = {}
 275         res_final = {}
 276         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 277         for i in child_ids:
 278             res[i] =  0.0
 279         if not child_ids:
 280             return res
 281         if child_ids:
 282             cr.execute("""SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
 283                     FROM account_analytic_line \
 284                     JOIN account_analytic_journal \
 285                         ON account_analytic_line.journal_id = account_analytic_journal.id \
 286                     WHERE account_analytic_line.account_id IN %s \
 287                         AND amount<0 \
 288                     GROUP BY account_analytic_line.account_id""",(child_ids,))
 289             for account_id, sum in cr.fetchall():
 290                 res[account_id] = round(sum,2)
 291         res_final = res
 292         return res_final
 293
 294     def _remaining_hours_calc(self, cr, uid, ids, name, arg, context=None):
 295         res = {}
 296         for account in self.browse(cr, uid, ids, context=context):
 297             if account.quantity_max != 0:
 298                 res[account.id] = account.quantity_max - account.hours_quantity
 299             else:
 300                 res[account.id] = 0.0
 301         for id in ids:
 302             res[id] = round(res.get(id, 0.0),2)
 303         return res
 304
 305     def _remaining_hours_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
 306         res = {}
 307         for account in self.browse(cr, uid, ids, context=context):
 308             res[account.id] = max(account.hours_qtt_est - account.timesheet_ca_invoiced, account.ca_to_invoice)
 309         return res
 310
 311     def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 312         res = {}
 313         for account in self.browse(cr, uid, ids, context=context):
 314             res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
 315             if res[account.id] < 0:
 316                 res[account.id] = 0.0
 317         for id in ids:
 318             res[id] = round(res.get(id, 0.0),2)
 319         return res
 320
 321     def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
 322         res = {}
 323         for account in self.browse(cr, uid, ids, context=context):
 324             if account.hours_qtt_invoiced == 0:
 325                 res[account.id]=0.0
 326             else:
 327                 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
 328         for id in ids:
 329             res[id] = round(res.get(id, 0.0),2)
 330         return res
 331
 332     def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
 333         res = {}
 334         for account in self.browse(cr, uid, ids, context=context):
 335             if account.ca_invoiced == 0:
 336                 res[account.id]=0.0
 337             elif account.total_cost != 0.0:
 338                 res[account.id] = -(account.real_margin / account.total_cost) * 100
 339             else:
 340                 res[account.id] = 0.0
 341         for id in ids:
 342             res[id] = round(res.get(id, 0.0),2)
 343         return res
 344
 345     def _fix_price_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
 346         sale_obj = self.pool.get('sale.order')
 347         res = {}
 348         for account in self.browse(cr, uid, ids, context=context):
 349             res[account.id] = 0.0
 350             sale_ids = sale_obj.search(cr, uid, [('project_id','=', account.id), ('state', '=', 'manual')], context=context)
 351             for sale in sale_obj.browse(cr, uid, sale_ids, context=context):
 352                 res[account.id] += sale.amount_untaxed
 353                 for invoice in sale.invoice_ids:
 354                     if invoice.state != 'cancel':
 355                         res[account.id] -= invoice.amount_untaxed
 356         return res
 357
 358     def _timesheet_ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 359         lines_obj = self.pool.get('account.analytic.line')
 360         res = {}
 361         inv_ids = []
 362         for account in self.browse(cr, uid, ids, context=context):
 363             res[account.id] = 0.0
 364             line_ids = lines_obj.search(cr, uid, [('account_id','=', account.id), ('invoice_id','!=',False), ('to_invoice','!=', False), ('journal_id.type', '=', 'general')], context=context)
 365             for line in lines_obj.browse(cr, uid, line_ids, context=context):
 366                 if line.invoice_id not in inv_ids:
 367                     inv_ids.append(line.invoice_id)
 368                     res[account.id] += line.invoice_id.amount_untaxed
 369         return res
 370
 371     def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
 372         res = {}
 373         for account in self.browse(cr, uid, ids, context=context):
 374             res[account.id] = max(account.amount_max - account.ca_invoiced, account.fix_price_to_invoice)
 375         return res
 376
 377     def _real_margin_calc(self, cr, uid, ids, name, arg, context=None):
 378         res = {}
 379         for account in self.browse(cr, uid, ids, context=context):
 380             res[account.id] = account.ca_invoiced + account.total_cost
 381         for id in ids:
 382             res[id] = round(res.get(id, 0.0),2)
 383         return res
 384
 385     def _theorical_margin_calc(self, cr, uid, ids, name, arg, context=None):
 386         res = {}
 387         for account in self.browse(cr, uid, ids, context=context):
 388             res[account.id] = account.ca_theorical + account.total_cost
 389         for id in ids:
 390             res[id] = round(res.get(id, 0.0),2)
 391         return res
 392
 393     def _is_overdue_quantity(self, cr, uid, ids, fieldnames, args, context=None):
 394         result = dict.fromkeys(ids, 0)
 395         for record in self.browse(cr, uid, ids, context=context):
 396             if record.quantity_max > 0.0:
 397                 result[record.id] = int(record.hours_quantity >= record.quantity_max)
 398             else:
 399                 result[record.id] = 0
 400         return result
 401
 402     def _get_analytic_account(self, cr, uid, ids, context=None):
 403         result = set()
 404         for line in self.pool.get('account.analytic.line').browse(cr, uid, ids, context=context):
 405             result.add(line.account_id.id)
 406         return list(result)
 407
 408     def _get_total_estimation(self, account):
 409         tot_est = 0.0
 410         if account.fix_price_invoices:
 411             tot_est += account.amount_max
 412         if account.invoice_on_timesheets:
 413             tot_est += account.hours_qtt_est
 414         return tot_est
 415
 416     def _get_total_invoiced(self, account):
 417         total_invoiced = 0.0
 418         if account.fix_price_invoices:
 419             total_invoiced += account.ca_invoiced
 420         if account.invoice_on_timesheets:
 421             total_invoiced += account.timesheet_ca_invoiced
 422         return total_invoiced
 423
 424     def _get_total_remaining(self, account):
 425         total_remaining = 0.0
 426         if account.fix_price_invoices:
 427             total_remaining += account.remaining_ca
 428         if account.invoice_on_timesheets:
 429             total_remaining += account.remaining_hours_to_invoice
 430         return total_remaining
 431
 432     def _get_total_toinvoice(self, account):
 433         total_toinvoice = 0.0
 434         if account.fix_price_invoices:
 435             total_toinvoice += account.fix_price_to_invoice
 436         if account.invoice_on_timesheets:
 437             total_toinvoice += account.ca_to_invoice
 438         return total_toinvoice
 439
 440     def _sum_of_fields(self, cr, uid, ids, name, arg, context=None):
 441          res = dict([(i, {}) for i in ids])
 442          for account in self.browse(cr, uid, ids, context=context):
 443             res[account.id]['est_total'] = self._get_total_estimation(account)
 444             res[account.id]['invoiced_total'] =  self._get_total_invoiced(account)
 445             res[account.id]['remaining_total'] = self._get_total_remaining(account)
 446             res[account.id]['toinvoice_total'] =  self._get_total_toinvoice(account)
 447          return res
 448
 449     _columns = {
 450         'is_overdue_quantity' : fields.function(_is_overdue_quantity, method=True, type='boolean', string='Overdue Quantity',
 451                                                 store={
 452                                                     'account.analytic.line' : (_get_analytic_account, None, 20),
 453                                                 }),
 454         'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
 455             help="Total customer invoiced amount for this account.",
 456             digits_compute=dp.get_precision('Account')),
 457         'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
 458             help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
 459             digits_compute=dp.get_precision('Account')),
 460         'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
 461             help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
 462             digits_compute=dp.get_precision('Account')),
 463         'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
 464             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.",
 465             digits_compute=dp.get_precision('Account')),
 466         'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Worked Time',
 467             help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
 468         'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
 469             help="If invoice from the costs, this is the date of the latest invoiced."),
 470         'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
 471             help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
 472         'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
 473             help="Date of the latest work done on this account."),
 474         'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
 475             help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
 476         'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
 477             help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
 478         'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
 479             help="Computed using the formula: Maximum Time - Total Worked Time"),
 480         'remaining_hours_to_invoice': fields.function(_remaining_hours_to_invoice_calc, type='float', string='Remaining Time',
 481             help="Computed using the formula: Expected on timesheets - Total invoiced on timesheets"),
 482         'fix_price_to_invoice': fields.function(_fix_price_to_invoice_calc, type='float', string='Remaining Time',
 483             help="Sum of quotations for this contract."),
 484         'timesheet_ca_invoiced': fields.function(_timesheet_ca_invoiced_calc, type='float', string='Remaining Time',
 485             help="Sum of timesheet lines invoiced for this contract."),
 486         'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
 487             help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
 488             digits_compute=dp.get_precision('Account')),
 489         'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
 490             help="Computed using the formula: Invoiced Amount / Total Time",
 491             digits_compute=dp.get_precision('Account')),
 492         'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
 493             help="Computed using the formula: Invoiced Amount - Total Costs.",
 494             digits_compute=dp.get_precision('Account')),
 495         'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
 496             help="Computed using the formula: Theoretical Revenue - Total Costs",
 497             digits_compute=dp.get_precision('Account')),
 498         'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
 499             help="Computes using the formula: (Real Margin / Total Costs) * 100.",
 500             digits_compute=dp.get_precision('Account')),
 501         'fix_price_invoices' : fields.boolean('Fixed Price'),
 502         'invoice_on_timesheets' : fields.boolean("On Timesheets"),
 503         'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
 504         'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
 505         'hours_qtt_est': fields.float('Estimation of Hours to Invoice'),
 506         'est_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all", string="Total Estimation"),
 507         'invoiced_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all", string="Total Invoiced"),
 508         '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"),
 509         '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."),
 510         'recurring_invoice_line_ids': fields.one2many('account.analytic.invoice.line', 'analytic_account_id', 'Invoice Lines'),
 511         'recurring_invoices' : fields.boolean('Generate recurring invoices automatically'),
 512         'recurring_rule_type': fields.selection([
 513             ('daily', 'Day(s)'),
 514             ('weekly', 'Week(s)'),
 515             ('monthly', 'Month(s)'),
 516             ('yearly', 'Year(s)'),
 517             ], 'Recurrency', help="Invoice automatically repeat at specified interval"),
 518         'recurring_interval': fields.integer('Repeat Every', help="Repeat every (Days/Week/Month/Year)"),
 519         'recurring_next_date': fields.date('Date of Next Invoice'),
 520     }
 521
 522     _defaults = {
 523         'recurring_interval': 1,
 524         'recurring_next_date': lambda *a: time.strftime('%Y-%m-%d'),
 525         'recurring_rule_type':'monthly'
 526     }
 527
 528     def open_sale_order_lines(self,cr,uid,ids,context=None):
 529         if context is None:
 530             context = {}
 531         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))])
 532         names = [record.name for record in self.browse(cr, uid, ids, context=context)]
 533         name = _('Sales Order Lines to Invoice of %s') % ','.join(names)
 534         return {
 535             'type': 'ir.actions.act_window',
 536             'name': name,
 537             'view_type': 'form',
 538             'view_mode': 'tree,form',
 539             'context': context,
 540             'domain' : [('order_id','in',sale_ids)],
 541             'res_model': 'sale.order.line',
 542             'nodestroy': True,
 543         }
 544
 545     def on_change_template(self, cr, uid, ids, template_id, context=None):
 546         if not template_id:
 547             return {}
 548         obj_analytic_line = self.pool.get('account.analytic.invoice.line')
 549         res = super(account_analytic_account, self).on_change_template(cr, uid, ids, template_id, context=context)
 550
 551         template = self.browse(cr, uid, template_id, context=context)
 552         invoice_line_ids = []
 553         for x in template.recurring_invoice_line_ids:
 554             invoice_line_ids.append((0, 0, {
 555                 'product_id': x.product_id.id,
 556                 'uom_id': x.uom_id.id,
 557                 'name': x.name,
 558                 'quantity': x.quantity,
 559                 'price_unit': x.price_unit,
 560                 'analytic_account_id': x.analytic_account_id and x.analytic_account_id.id or False,
 561             }))
 562         res['value']['fix_price_invoices'] = template.fix_price_invoices
 563         res['value']['invoice_on_timesheets'] = template.invoice_on_timesheets
 564         res['value']['hours_qtt_est'] = template.hours_qtt_est
 565         res['value']['amount_max'] = template.amount_max
 566         res['value']['to_invoice'] = template.to_invoice.id
 567         res['value']['pricelist_id'] = template.pricelist_id.id
 568         res['value']['recurring_invoices'] = template.recurring_invoices
 569         res['value']['recurring_interval'] = template.recurring_interval
 570         res['value']['recurring_rule_type'] = template.recurring_rule_type
 571         res['value']['recurring_invoice_line_ids'] = invoice_line_ids
 572         return res
 573
 574     def onchange_recurring_invoices(self, cr, uid, ids, recurring_invoices, date_start=False, context=None):
 575         value = {}
 576         if date_start and recurring_invoices:
 577             value = {'value': {'recurring_next_date': date_start}}
 578         return value
 579
 580     def cron_account_analytic_account(self, cr, uid, context=None):
 581         if context is None:
 582             context = {}
 583         remind = {}
 584
 585         def fill_remind(key, domain, write_pending=False):
 586             base_domain = [
 587                 ('type', '=', 'contract'),
 588                 ('partner_id', '!=', False),
 589                 ('manager_id', '!=', False),
 590                 ('manager_id.email', '!=', False),
 591             ]
 592             base_domain.extend(domain)
 593
 594             accounts_ids = self.search(cr, uid, base_domain, context=context, order='name asc')
 595             accounts = self.browse(cr, uid, accounts_ids, context=context)
 596             for account in accounts:
 597                 if write_pending:
 598                     account.write({'state' : 'pending'}, context=context)
 599                 remind_user = remind.setdefault(account.manager_id.id, {})
 600                 remind_type = remind_user.setdefault(key, {})
 601                 remind_partner = remind_type.setdefault(account.partner_id, []).append(account)
 602
 603         # Already expired
 604         fill_remind("old", [('state', 'in', ['pending'])])
 605
 606         # Expires now
 607         fill_remind("new", [('state', 'in', ['draft', 'open']), '|', '&', ('date', '!=', False), ('date', '<=', time.strftime('%Y-%m-%d')), ('is_overdue_quantity', '=', True)], True)
 608
 609         # Expires in less than 30 days
 610         fill_remind("future", [('state', 'in', ['draft', 'open']), ('date', '!=', False), ('date', '<', (datetime.datetime.now() + datetime.timedelta(30)).strftime("%Y-%m-%d"))])
 611
 612         context['base_url'] = self.pool.get('ir.config_parameter').get_param(cr, uid, 'web.base.url')
 613         context['action_id'] = self.pool.get('ir.model.data').get_object_reference(cr, uid, 'account_analytic_analysis', 'action_account_analytic_overdue_all')[1]
 614         template_id = self.pool.get('ir.model.data').get_object_reference(cr, uid, 'account_analytic_analysis', 'account_analytic_cron_email_template')[1]
 615         for user_id, data in remind.items():
 616             context["data"] = data
 617             _logger.debug("Sending reminder to uid %s", user_id)
 618             self.pool.get('email.template').send_mail(cr, uid, template_id, user_id, force_send=True, context=context)
 619
 620         return True
 621
 622     def onchange_invoice_on_timesheets(self, cr, uid, ids, invoice_on_timesheets, context=None):
 623         if not invoice_on_timesheets:
 624             return {}
 625         result = {'value': {'use_timesheets': True}}
 626         try:
 627             to_invoice = self.pool.get('ir.model.data').get_object_reference(cr, uid, 'hr_timesheet_invoice', 'timesheet_invoice_factor1')
 628             result['value']['to_invoice'] = to_invoice[1]
 629         except ValueError:
 630             pass
 631         return result
 632
 633
 634     def hr_to_invoice_timesheets(self, cr, uid, ids, context=None):
 635         domain = [('invoice_id','=',False),('to_invoice','!=',False), ('journal_id.type', '=', 'general'), ('account_id', 'in', ids)]
 636         names = [record.name for record in self.browse(cr, uid, ids, context=context)]
 637         name = _('Timesheets to Invoice of %s') % ','.join(names)
 638         return {
 639             'type': 'ir.actions.act_window',
 640             'name': name,
 641             'view_type': 'form',
 642             'view_mode': 'tree,form',
 643             'domain' : domain,
 644             'res_model': 'account.analytic.line',
 645             'nodestroy': True,
 646         }
 647
 648     def _prepare_invoice(self, cr, uid, contract, context=None):
 649         context = context or {}
 650
 651         inv_obj = self.pool.get('account.invoice')
 652         journal_obj = self.pool.get('account.journal')
 653         fpos_obj = self.pool.get('account.fiscal.position')
 654
 655         if not contract.partner_id:
 656             raise osv.except_osv(_('No Customer Defined!'),_("You must first select a Customer for Contract %s!") % contract.name )
 657
 658         fpos = contract.partner_id.property_account_position or False
 659         journal_ids = journal_obj.search(cr, uid, [('type', '=','sale'),('company_id', '=', contract.company_id.id or False)], limit=1)
 660         if not journal_ids:
 661             raise osv.except_osv(_('Error!'),
 662             _('Please define a sale journal for the company "%s".') % (contract.company_id.name or '', ))
 663
 664         partner_payment_term = contract.partner_id.property_payment_term and contract.partner_id.property_payment_term.id or False
 665
 666
 667         inv_data = {
 668            'reference': contract.code or False,
 669            'account_id': contract.partner_id.property_account_receivable.id,
 670            'type': 'out_invoice',
 671            'partner_id': contract.partner_id.id,
 672            'currency_id': contract.partner_id.property_product_pricelist.id or False,
 673            'journal_id': len(journal_ids) and journal_ids[0] or False,
 674            'date_invoice': contract.recurring_next_date,
 675            'origin': contract.name,
 676            'fiscal_position': fpos and fpos.id,
 677            'payment_term': partner_payment_term,
 678            'company_id': contract.company_id.id or False,
 679         }
 680         invoice_id = inv_obj.create(cr, uid, inv_data, context=context)
 681
 682         for line in contract.recurring_invoice_line_ids:
 683
 684             res = line.product_id
 685             account_id = res.property_account_income.id
 686             if not account_id:
 687                 account_id = res.categ_id.property_account_income_categ.id
 688             account_id = fpos_obj.map_account(cr, uid, fpos, account_id)
 689
 690             taxes = res.taxes_id or False
 691             tax_id = fpos_obj.map_tax(cr, uid, fpos, taxes)
 692
 693             invoice_line_vals = {
 694                 'name': line.name,
 695                 'account_id': account_id,
 696                 'account_analytic_id': contract.id,
 697                 'price_unit': line.price_unit or 0.0,
 698                 'quantity': line.quantity,
 699                 'uos_id': line.uom_id.id or False,
 700                 'product_id': line.product_id.id or False,
 701                 'invoice_id' : invoice_id,
 702                 'invoice_line_tax_id': [(6, 0, tax_id)],
 703             }
 704             self.pool.get('account.invoice.line').create(cr, uid, invoice_line_vals, context=context)
 705
 706         inv_obj.button_compute(cr, uid, [invoice_id], context=context)
 707         return invoice_id
 708
 709     def recurring_create_invoice(self, cr, uid, automatic=False, context=None):
 710         context = context or {}
 711         current_date =  time.strftime('%Y-%m-%d')
 712
 713         contract_ids = self.search(cr, uid, [('recurring_next_date','<=', current_date), ('state','=', 'open'), ('recurring_invoices','=', True)])
 714         for contract in self.browse(cr, uid, contract_ids, context=context):
 715             invoice_id = self._prepare_invoice(cr, uid, contract, context=context)
 716
 717             next_date = datetime.datetime.strptime(contract.recurring_next_date or current_date, "%Y-%m-%d")
 718             interval = contract.recurring_interval
 719             if contract.recurring_rule_type == 'daily':
 720                 new_date = next_date+relativedelta(days=+interval)
 721             elif contract.recurring_rule_type == 'weekly':
 722                 new_date = next_date+relativedelta(weeks=+interval)
 723             else:
 724                 new_date = next_date+relativedelta(months=+interval)
 725             self.write(cr, uid, [contract.id], {'recurring_next_date': new_date.strftime('%Y-%m-%d')}, context=context)
 726         return True
 727
 728 class account_analytic_account_summary_user(osv.osv):
 729     _name = "account_analytic_analysis.summary.user"
 730     _description = "Hours Summary by User"
 731     _order='user'
 732     _auto = False
 733     _rec_name = 'user'
 734
 735     def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 736         res = {}
 737         account_obj = self.pool.get('account.analytic.account')
 738         cr.execute('SELECT MAX(id) FROM res_users')
 739         max_user = cr.fetchone()[0]
 740         account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
 741         user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
 742         parent_ids = tuple(account_ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 743         if parent_ids:
 744             cr.execute('SELECT id, unit_amount ' \
 745                     'FROM account_analytic_analysis_summary_user ' \
 746                     'WHERE account_id IN %s ' \
 747                         'AND "user" IN %s',(parent_ids, tuple(user_ids),))
 748             for sum_id, unit_amount in cr.fetchall():
 749                 res[sum_id] = unit_amount
 750         for id in ids:
 751             res[id] = round(res.get(id, 0.0), 2)
 752         return res
 753
 754     _columns = {
 755         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 756         'unit_amount': fields.float('Total Time'),
 757         'user': fields.many2one('res.users', 'User'),
 758     }
 759
 760     def init(self, cr):
 761         openerp.tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
 762         cr.execute('''CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (
 763             with mu as
 764                 (select max(id) as max_user from res_users)
 765             , lu AS
 766                 (SELECT
 767                  l.account_id AS account_id,
 768                  coalesce(l.user_id, 0) AS user_id,
 769                  SUM(l.unit_amount) AS unit_amount
 770              FROM account_analytic_line AS l,
 771                  account_analytic_journal AS j
 772              WHERE (j.type = 'general' ) and (j.id=l.journal_id)
 773              GROUP BY l.account_id, l.user_id
 774             )
 775             select (lu.account_id * mu.max_user) + lu.user_id as id,
 776                     lu.account_id as account_id,
 777                     lu.user_id as "user",
 778                     unit_amount
 779             from lu, mu)''')
 780
 781 class account_analytic_account_summary_month(osv.osv):
 782     _name = "account_analytic_analysis.summary.month"
 783     _description = "Hours summary by month"
 784     _auto = False
 785     _rec_name = 'month'
 786
 787     _columns = {
 788         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 789         'unit_amount': fields.float('Total Time'),
 790         'month': fields.char('Month', size=32, readonly=True),
 791     }
 792
 793     def init(self, cr):
 794         openerp.tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
 795         cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
 796                 'SELECT ' \
 797                     '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id  * 1000000::bigint))::bigint AS id, ' \
 798                     'd.account_id AS account_id, ' \
 799                     'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
 800                     'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
 801                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 802                 'FROM ' \
 803                     '(SELECT ' \
 804                         'd2.account_id, ' \
 805                         'd2.month ' \
 806                     'FROM ' \
 807                         '(SELECT ' \
 808                             'a.id AS account_id, ' \
 809                             'l.month AS month ' \
 810                         'FROM ' \
 811                             '(SELECT ' \
 812                                 'DATE_TRUNC(\'month\', l.date) AS month ' \
 813                             'FROM account_analytic_line AS l, ' \
 814                                 'account_analytic_journal AS j ' \
 815                             'WHERE j.type = \'general\' ' \
 816                             'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
 817                             ') AS l, ' \
 818                             'account_analytic_account AS a ' \
 819                         'GROUP BY l.month, a.id ' \
 820                         ') AS d2 ' \
 821                     'GROUP BY d2.account_id, d2.month ' \
 822                     ') AS d ' \
 823                 'LEFT JOIN ' \
 824                     '(SELECT ' \
 825                         'l.account_id AS account_id, ' \
 826                         'DATE_TRUNC(\'month\', l.date) AS month, ' \
 827                         'SUM(l.unit_amount) AS unit_amount ' \
 828                     'FROM account_analytic_line AS l, ' \
 829                         'account_analytic_journal AS j ' \
 830                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 831                     'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
 832                     ') AS l '
 833                     'ON (' \
 834                         'd.account_id = l.account_id ' \
 835                         'AND d.month = l.month' \
 836                     ') ' \
 837                 'GROUP BY d.month, d.account_id ' \
 838                 ')')
 839
 840 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: