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