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                                                     'account.analytic.account': (lambda self, cr, uid, ids, c=None: ids, ['quantity_max'], 10),
 454                                                 }),
 455         'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
 456             help="Total customer invoiced amount for this account.",
 457             digits_compute=dp.get_precision('Account')),
 458         'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
 459             help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
 460             digits_compute=dp.get_precision('Account')),
 461         'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
 462             help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
 463             digits_compute=dp.get_precision('Account')),
 464         'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
 465             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.",
 466             digits_compute=dp.get_precision('Account')),
 467         'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Worked Time',
 468             help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
 469         'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
 470             help="If invoice from the costs, this is the date of the latest invoiced."),
 471         'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
 472             help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
 473         'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
 474             help="Date of the latest work done on this account."),
 475         'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
 476             help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
 477         'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
 478             help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
 479         'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
 480             help="Computed using the formula: Maximum Time - Total Worked Time"),
 481         'remaining_hours_to_invoice': fields.function(_remaining_hours_to_invoice_calc, type='float', string='Remaining Time',
 482             help="Computed using the formula: Expected on timesheets - Total invoiced on timesheets"),
 483         'fix_price_to_invoice': fields.function(_fix_price_to_invoice_calc, type='float', string='Remaining Time',
 484             help="Sum of quotations for this contract."),
 485         'timesheet_ca_invoiced': fields.function(_timesheet_ca_invoiced_calc, type='float', string='Remaining Time',
 486             help="Sum of timesheet lines invoiced for this contract."),
 487         'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
 488             help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
 489             digits_compute=dp.get_precision('Account')),
 490         'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
 491             help="Computed using the formula: Invoiced Amount / Total Time",
 492             digits_compute=dp.get_precision('Account')),
 493         'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
 494             help="Computed using the formula: Invoiced Amount - Total Costs.",
 495             digits_compute=dp.get_precision('Account')),
 496         'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
 497             help="Computed using the formula: Theoretical Revenue - Total Costs",
 498             digits_compute=dp.get_precision('Account')),
 499         'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
 500             help="Computes using the formula: (Real Margin / Total Costs) * 100.",
 501             digits_compute=dp.get_precision('Account')),
 502         'fix_price_invoices' : fields.boolean('Fixed Price'),
 503         'invoice_on_timesheets' : fields.boolean("On Timesheets"),
 504         'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
 505         'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
 506         'hours_qtt_est': fields.float('Estimation of Hours to Invoice'),
 507         'est_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all", string="Total Estimation"),
 508         'invoiced_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all", string="Total Invoiced"),
 509         '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"),
 510         '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."),
 511         'recurring_invoice_line_ids': fields.one2many('account.analytic.invoice.line', 'analytic_account_id', 'Invoice Lines'),
 512         'recurring_invoices' : fields.boolean('Generate recurring invoices automatically'),
 513         'recurring_rule_type': fields.selection([
 514             ('daily', 'Day(s)'),
 515             ('weekly', 'Week(s)'),
 516             ('monthly', 'Month(s)'),
 517             ('yearly', 'Year(s)'),
 518             ], 'Recurrency', help="Invoice automatically repeat at specified interval"),
 519         'recurring_interval': fields.integer('Repeat Every', help="Repeat every (Days/Week/Month/Year)"),
 520         'recurring_next_date': fields.date('Date of Next Invoice'),
 521     }
 522
 523     _defaults = {
 524         'recurring_interval': 1,
 525         'recurring_next_date': lambda *a: time.strftime('%Y-%m-%d'),
 526         'recurring_rule_type':'monthly'
 527     }
 528
 529     def open_sale_order_lines(self,cr,uid,ids,context=None):
 530         if context is None:
 531             context = {}
 532         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))])
 533         names = [record.name for record in self.browse(cr, uid, ids, context=context)]
 534         name = _('Sales Order Lines to Invoice of %s') % ','.join(names)
 535         return {
 536             'type': 'ir.actions.act_window',
 537             'name': name,
 538             'view_type': 'form',
 539             'view_mode': 'tree,form',
 540             'context': context,
 541             'domain' : [('order_id','in',sale_ids)],
 542             'res_model': 'sale.order.line',
 543             'nodestroy': True,
 544         }
 545
 546     def on_change_template(self, cr, uid, ids, template_id, context=None):
 547         if not template_id:
 548             return {}
 549         obj_analytic_line = self.pool.get('account.analytic.invoice.line')
 550         res = super(account_analytic_account, self).on_change_template(cr, uid, ids, template_id, context=context)
 551
 552         template = self.browse(cr, uid, template_id, context=context)
 553         invoice_line_ids = []
 554         for x in template.recurring_invoice_line_ids:
 555             invoice_line_ids.append((0, 0, {
 556                 'product_id': x.product_id.id,
 557                 'uom_id': x.uom_id.id,
 558                 'name': x.name,
 559                 'quantity': x.quantity,
 560                 'price_unit': x.price_unit,
 561                 'analytic_account_id': x.analytic_account_id and x.analytic_account_id.id or False,
 562             }))
 563         res['value']['fix_price_invoices'] = template.fix_price_invoices
 564         res['value']['invoice_on_timesheets'] = template.invoice_on_timesheets
 565         res['value']['hours_qtt_est'] = template.hours_qtt_est
 566         res['value']['amount_max'] = template.amount_max
 567         res['value']['to_invoice'] = template.to_invoice.id
 568         res['value']['pricelist_id'] = template.pricelist_id.id
 569         res['value']['recurring_invoices'] = template.recurring_invoices
 570         res['value']['recurring_interval'] = template.recurring_interval
 571         res['value']['recurring_rule_type'] = template.recurring_rule_type
 572         res['value']['recurring_invoice_line_ids'] = invoice_line_ids
 573         return res
 574
 575     def onchange_recurring_invoices(self, cr, uid, ids, recurring_invoices, date_start=False, context=None):
 576         value = {}
 577         if date_start and recurring_invoices:
 578             value = {'value': {'recurring_next_date': date_start}}
 579         return value
 580
 581     def cron_account_analytic_account(self, cr, uid, context=None):
 582         if context is None:
 583             context = {}
 584         remind = {}
 585
 586         def fill_remind(key, domain, write_pending=False):
 587             base_domain = [
 588                 ('type', '=', 'contract'),
 589                 ('partner_id', '!=', False),
 590                 ('manager_id', '!=', False),
 591                 ('manager_id.email', '!=', False),
 592             ]
 593             base_domain.extend(domain)
 594
 595             accounts_ids = self.search(cr, uid, base_domain, context=context, order='name asc')
 596             accounts = self.browse(cr, uid, accounts_ids, context=context)
 597             for account in accounts:
 598                 if write_pending:
 599                     account.write({'state' : 'pending'}, context=context)
 600                 remind_user = remind.setdefault(account.manager_id.id, {})
 601                 remind_type = remind_user.setdefault(key, {})
 602                 remind_partner = remind_type.setdefault(account.partner_id, []).append(account)
 603
 604         # Already expired
 605         fill_remind("old", [('state', 'in', ['pending'])])
 606
 607         # Expires now
 608         fill_remind("new", [('state', 'in', ['draft', 'open']), '|', '&', ('date', '!=', False), ('date', '<=', time.strftime('%Y-%m-%d')), ('is_overdue_quantity', '=', True)], True)
 609
 610         # Expires in less than 30 days
 611         fill_remind("future", [('state', 'in', ['draft', 'open']), ('date', '!=', False), ('date', '<', (datetime.datetime.now() + datetime.timedelta(30)).strftime("%Y-%m-%d"))])
 612
 613         context['base_url'] = self.pool.get('ir.config_parameter').get_param(cr, uid, 'web.base.url')
 614         context['action_id'] = self.pool.get('ir.model.data').get_object_reference(cr, uid, 'account_analytic_analysis', 'action_account_analytic_overdue_all')[1]
 615         template_id = self.pool.get('ir.model.data').get_object_reference(cr, uid, 'account_analytic_analysis', 'account_analytic_cron_email_template')[1]
 616         for user_id, data in remind.items():
 617             context["data"] = data
 618             _logger.debug("Sending reminder to uid %s", user_id)
 619             self.pool.get('email.template').send_mail(cr, uid, template_id, user_id, force_send=True, context=context)
 620
 621         return True
 622
 623     def onchange_invoice_on_timesheets(self, cr, uid, ids, invoice_on_timesheets, context=None):
 624         if not invoice_on_timesheets:
 625             return {}
 626         result = {'value': {'use_timesheets': True}}
 627         try:
 628             to_invoice = self.pool.get('ir.model.data').get_object_reference(cr, uid, 'hr_timesheet_invoice', 'timesheet_invoice_factor1')
 629             result['value']['to_invoice'] = to_invoice[1]
 630         except ValueError:
 631             pass
 632         return result
 633
 634
 635     def hr_to_invoice_timesheets(self, cr, uid, ids, context=None):
 636         domain = [('invoice_id','=',False),('to_invoice','!=',False), ('journal_id.type', '=', 'general'), ('account_id', 'in', ids)]
 637         names = [record.name for record in self.browse(cr, uid, ids, context=context)]
 638         name = _('Timesheets to Invoice of %s') % ','.join(names)
 639         return {
 640             'type': 'ir.actions.act_window',
 641             'name': name,
 642             'view_type': 'form',
 643             'view_mode': 'tree,form',
 644             'domain' : domain,
 645             'res_model': 'account.analytic.line',
 646             'nodestroy': True,
 647         }
 648
 649     def _prepare_invoice(self, cr, uid, contract, context=None):
 650         context = context or {}
 651
 652         inv_obj = self.pool.get('account.invoice')
 653         journal_obj = self.pool.get('account.journal')
 654         fpos_obj = self.pool.get('account.fiscal.position')
 655
 656         if not contract.partner_id:
 657             raise osv.except_osv(_('No Customer Defined!'),_("You must first select a Customer for Contract %s!") % contract.name )
 658
 659         fpos = contract.partner_id.property_account_position or False
 660         journal_ids = journal_obj.search(cr, uid, [('type', '=','sale'),('company_id', '=', contract.company_id.id or False)], limit=1)
 661         if not journal_ids:
 662             raise osv.except_osv(_('Error!'),
 663             _('Please define a sale journal for the company "%s".') % (contract.company_id.name or '', ))
 664
 665         partner_payment_term = contract.partner_id.property_payment_term and contract.partner_id.property_payment_term.id or False
 666
 667         currency_id = False
 668         if contract.pricelist_id:
 669             currency_id = contract.pricelist_id.currency_id.id
 670         elif contract.partner_id.property_product_pricelist:
 671             currency_id = contract.partner_id.property_product_pricelist.currency_id.id
 672         elif contract.company_id:
 673             currency_id = contract.company_id.currency_id.id
 674
 675         inv_data = {
 676            'reference': contract.code or False,
 677            'account_id': contract.partner_id.property_account_receivable.id,
 678            'type': 'out_invoice',
 679            'partner_id': contract.partner_id.id,
 680            'currency_id': currency_id,
 681            'journal_id': len(journal_ids) and journal_ids[0] or False,
 682            'date_invoice': contract.recurring_next_date,
 683            'origin': contract.name,
 684            'fiscal_position': fpos and fpos.id,
 685            'payment_term': partner_payment_term,
 686            'company_id': contract.company_id.id or False,
 687         }
 688         invoice_id = inv_obj.create(cr, uid, inv_data, context=context)
 689
 690         for line in contract.recurring_invoice_line_ids:
 691
 692             res = line.product_id
 693             account_id = res.property_account_income.id
 694             if not account_id:
 695                 account_id = res.categ_id.property_account_income_categ.id
 696             account_id = fpos_obj.map_account(cr, uid, fpos, account_id)
 697
 698             taxes = res.taxes_id or False
 699             tax_id = fpos_obj.map_tax(cr, uid, fpos, taxes)
 700
 701             invoice_line_vals = {
 702                 'name': line.name,
 703                 'account_id': account_id,
 704                 'account_analytic_id': contract.id,
 705                 'price_unit': line.price_unit or 0.0,
 706                 'quantity': line.quantity,
 707                 'uos_id': line.uom_id.id or False,
 708                 'product_id': line.product_id.id or False,
 709                 'invoice_id' : invoice_id,
 710                 'invoice_line_tax_id': [(6, 0, tax_id)],
 711             }
 712             self.pool.get('account.invoice.line').create(cr, uid, invoice_line_vals, context=context)
 713
 714         inv_obj.button_compute(cr, uid, [invoice_id], context=context)
 715         return invoice_id
 716
 717     def recurring_create_invoice(self, cr, uid, automatic=False, context=None):
 718         context = context or {}
 719         current_date =  time.strftime('%Y-%m-%d')
 720
 721         contract_ids = self.search(cr, uid, [('recurring_next_date','<=', current_date), ('state','=', 'open'), ('recurring_invoices','=', True)])
 722         for contract in self.browse(cr, uid, contract_ids, context=context):
 723             invoice_id = self._prepare_invoice(cr, uid, contract, context=context)
 724
 725             next_date = datetime.datetime.strptime(contract.recurring_next_date or current_date, "%Y-%m-%d")
 726             interval = contract.recurring_interval
 727             if contract.recurring_rule_type == 'daily':
 728                 new_date = next_date+relativedelta(days=+interval)
 729             elif contract.recurring_rule_type == 'weekly':
 730                 new_date = next_date+relativedelta(weeks=+interval)
 731             else:
 732                 new_date = next_date+relativedelta(months=+interval)
 733             self.write(cr, uid, [contract.id], {'recurring_next_date': new_date.strftime('%Y-%m-%d')}, context=context)
 734         return True
 735
 736 class account_analytic_account_summary_user(osv.osv):
 737     _name = "account_analytic_analysis.summary.user"
 738     _description = "Hours Summary by User"
 739     _order='user'
 740     _auto = False
 741     _rec_name = 'user'
 742
 743     def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 744         res = {}
 745         account_obj = self.pool.get('account.analytic.account')
 746         cr.execute('SELECT MAX(id) FROM res_users')
 747         max_user = cr.fetchone()[0]
 748         account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
 749         user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
 750         parent_ids = tuple(account_ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 751         if parent_ids:
 752             cr.execute('SELECT id, unit_amount ' \
 753                     'FROM account_analytic_analysis_summary_user ' \
 754                     'WHERE account_id IN %s ' \
 755                         'AND "user" IN %s',(parent_ids, tuple(user_ids),))
 756             for sum_id, unit_amount in cr.fetchall():
 757                 res[sum_id] = unit_amount
 758         for id in ids:
 759             res[id] = round(res.get(id, 0.0), 2)
 760         return res
 761
 762     _columns = {
 763         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 764         'unit_amount': fields.float('Total Time'),
 765         'user': fields.many2one('res.users', 'User'),
 766     }
 767
 768     def init(self, cr):
 769         openerp.tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
 770         cr.execute('''CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (
 771             with mu as
 772                 (select max(id) as max_user from res_users)
 773             , lu AS
 774                 (SELECT
 775                  l.account_id AS account_id,
 776                  coalesce(l.user_id, 0) AS user_id,
 777                  SUM(l.unit_amount) AS unit_amount
 778              FROM account_analytic_line AS l,
 779                  account_analytic_journal AS j
 780              WHERE (j.type = 'general' ) and (j.id=l.journal_id)
 781              GROUP BY l.account_id, l.user_id
 782             )
 783             select (lu.account_id * mu.max_user) + lu.user_id as id,
 784                     lu.account_id as account_id,
 785                     lu.user_id as "user",
 786                     unit_amount
 787             from lu, mu)''')
 788
 789 class account_analytic_account_summary_month(osv.osv):
 790     _name = "account_analytic_analysis.summary.month"
 791     _description = "Hours summary by month"
 792     _auto = False
 793     _rec_name = 'month'
 794
 795     _columns = {
 796         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 797         'unit_amount': fields.float('Total Time'),
 798         'month': fields.char('Month', size=32, readonly=True),
 799     }
 800
 801     def init(self, cr):
 802         openerp.tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
 803         cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
 804                 'SELECT ' \
 805                     '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id  * 1000000::bigint))::bigint AS id, ' \
 806                     'd.account_id AS account_id, ' \
 807                     'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
 808                     'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
 809                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 810                 'FROM ' \
 811                     '(SELECT ' \
 812                         'd2.account_id, ' \
 813                         'd2.month ' \
 814                     'FROM ' \
 815                         '(SELECT ' \
 816                             'a.id AS account_id, ' \
 817                             'l.month AS month ' \
 818                         'FROM ' \
 819                             '(SELECT ' \
 820                                 'DATE_TRUNC(\'month\', l.date) AS month ' \
 821                             'FROM account_analytic_line AS l, ' \
 822                                 'account_analytic_journal AS j ' \
 823                             'WHERE j.type = \'general\' ' \
 824                             'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
 825                             ') AS l, ' \
 826                             'account_analytic_account AS a ' \
 827                         'GROUP BY l.month, a.id ' \
 828                         ') AS d2 ' \
 829                     'GROUP BY d2.account_id, d2.month ' \
 830                     ') AS d ' \
 831                 'LEFT JOIN ' \
 832                     '(SELECT ' \
 833                         'l.account_id AS account_id, ' \
 834                         'DATE_TRUNC(\'month\', l.date) AS month, ' \
 835                         'SUM(l.unit_amount) AS unit_amount ' \
 836                     'FROM account_analytic_line AS l, ' \
 837                         'account_analytic_journal AS j ' \
 838                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 839                     'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
 840                     ') AS l '
 841                     'ON (' \
 842                         'd.account_id = l.account_id ' \
 843                         'AND d.month = l.month' \
 844                     ') ' \
 845                 'GROUP BY d.month, d.account_id ' \
 846                 ')')
 847
 848 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: