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 import openerp.tools
  28 from openerp.tools.translate import _
  29
  30 from openerp.addons.decimal_precision import decimal_precision as dp
  31
  32 _logger = logging.getLogger(__name__)
  33
  34 class account_analytic_invoice_line(osv.osv):
  35     _name = "account.analytic.invoice.line"
  36
  37     def _amount_line(self, cr, uid, ids, prop, unknow_none, unknow_dict, context=None):
  38         res = {}
  39         for line in self.browse(cr, uid, ids, context=context):
  40             res[line.id] = line.quantity * line.price_unit
  41             if line.analytic_account_id.pricelist_id:
  42                 cur = line.analytic_account_id.pricelist_id.currency_id
  43                 res[line.id] = self.pool.get('res.currency').round(cr, uid, cur, res[line.id])
  44         return res
  45
  46     _columns = {
  47         'product_id': fields.many2one('product.product','Product',required=True),
  48         'analytic_account_id': fields.many2one('account.analytic.account', 'Analytic Account'),
  49         'name': fields.text('Description', required=True),
  50         'quantity': fields.float('Quantity', required=True),
  51         'uom_id': fields.many2one('product.uom', 'Unit of Measure',required=True),
  52         'price_unit': fields.float('Unit Price', required=True),
  53         'price_subtotal': fields.function(_amount_line, string='Sub Total', type="float",digits_compute= dp.get_precision('Account')),
  54     }
  55     _defaults = {
  56         'quantity' : 1,
  57     }
  58
  59     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):
  60         context = context or {}
  61         uom_obj = self.pool.get('product.uom')
  62         company_id = company_id or False
  63         local_context = dict(context, company_id=company_id, force_company=company_id, pricelist=pricelist_id)
  64
  65         if not product:
  66             return {'value': {'price_unit': 0.0}, 'domain':{'product_uom':[]}}
  67         if partner_id:
  68             part = self.pool.get('res.partner').browse(cr, uid, partner_id, context=local_context)
  69             if part.lang:
  70                 local_context.update({'lang': part.lang})
  71
  72         result = {}
  73         res = self.pool.get('product.product').browse(cr, uid, product, context=local_context)
  74         price = False
  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', 'not in', ['draft', 'cancel']), ('invoice_id.type', 'in', ['out_invoice', 'out_refund'])], context=context)
 274             for line in inv_line_obj.browse(cr, uid, inv_lines, context=context):
 275                 if line.invoice_id.type == 'out_refund':
 276                     res[line.account_analytic_id.id] -= line.price_subtotal
 277                 else:
 278                     res[line.account_analytic_id.id] += line.price_subtotal
 279
 280         for acc in self.browse(cr, uid, res.keys(), context=context):
 281             res[acc.id] = res[acc.id] - (acc.timesheet_ca_invoiced or 0.0)
 282
 283         res_final = res
 284         return res_final
 285
 286     def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
 287         res = {}
 288         res_final = {}
 289         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 290         for i in child_ids:
 291             res[i] =  0.0
 292         if not child_ids:
 293             return res
 294         if child_ids:
 295             cr.execute("""SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
 296                     FROM account_analytic_line \
 297                     JOIN account_analytic_journal \
 298                         ON account_analytic_line.journal_id = account_analytic_journal.id \
 299                     WHERE account_analytic_line.account_id IN %s \
 300                         AND amount<0 \
 301                     GROUP BY account_analytic_line.account_id""",(child_ids,))
 302             for account_id, sum in cr.fetchall():
 303                 res[account_id] = round(sum,2)
 304         res_final = res
 305         return res_final
 306
 307     def _remaining_hours_calc(self, cr, uid, ids, name, arg, context=None):
 308         res = {}
 309         for account in self.browse(cr, uid, ids, context=context):
 310             if account.quantity_max != 0:
 311                 res[account.id] = account.quantity_max - account.hours_quantity
 312             else:
 313                 res[account.id] = 0.0
 314         for id in ids:
 315             res[id] = round(res.get(id, 0.0),2)
 316         return res
 317
 318     def _remaining_hours_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
 319         res = {}
 320         for account in self.browse(cr, uid, ids, context=context):
 321             res[account.id] = max(account.hours_qtt_est - account.timesheet_ca_invoiced, account.ca_to_invoice)
 322         return res
 323
 324     def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 325         res = {}
 326         for account in self.browse(cr, uid, ids, context=context):
 327             res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
 328             if res[account.id] < 0:
 329                 res[account.id] = 0.0
 330         for id in ids:
 331             res[id] = round(res.get(id, 0.0),2)
 332         return res
 333
 334     def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
 335         res = {}
 336         for account in self.browse(cr, uid, ids, context=context):
 337             if account.hours_qtt_invoiced == 0:
 338                 res[account.id]=0.0
 339             else:
 340                 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
 341         for id in ids:
 342             res[id] = round(res.get(id, 0.0),2)
 343         return res
 344
 345     def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
 346         res = {}
 347         for account in self.browse(cr, uid, ids, context=context):
 348             if account.ca_invoiced == 0:
 349                 res[account.id]=0.0
 350             elif account.total_cost != 0.0:
 351                 res[account.id] = -(account.real_margin / account.total_cost) * 100
 352             else:
 353                 res[account.id] = 0.0
 354         for id in ids:
 355             res[id] = round(res.get(id, 0.0),2)
 356         return res
 357
 358     def _fix_price_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
 359         sale_obj = self.pool.get('sale.order')
 360         res = {}
 361         for account in self.browse(cr, uid, ids, context=context):
 362             res[account.id] = 0.0
 363             sale_ids = sale_obj.search(cr, uid, [('project_id','=', account.id), ('state', '=', 'manual')], context=context)
 364             for sale in sale_obj.browse(cr, uid, sale_ids, context=context):
 365                 res[account.id] += sale.amount_untaxed
 366                 for invoice in sale.invoice_ids:
 367                     if invoice.state != 'cancel':
 368                         res[account.id] -= invoice.amount_untaxed
 369         return res
 370
 371     def _timesheet_ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 372         lines_obj = self.pool.get('account.analytic.line')
 373         res = {}
 374         inv_ids = []
 375         for account in self.browse(cr, uid, ids, context=context):
 376             res[account.id] = 0.0
 377             line_ids = lines_obj.search(cr, uid, [('account_id','=', account.id), ('invoice_id','!=',False), ('to_invoice','!=', False), ('journal_id.type', '=', 'general'), ('invoice_id.type', 'in', ['out_invoice', 'out_refund'])], context=context)
 378             for line in lines_obj.browse(cr, uid, line_ids, context=context):
 379                 if line.invoice_id not in inv_ids:
 380                     inv_ids.append(line.invoice_id)
 381                     if line.invoice_id.type == 'out_refund':
 382                         res[account.id] -= line.invoice_id.amount_untaxed
 383                     else:
 384                         res[account.id] += line.invoice_id.amount_untaxed
 385         return res
 386
 387     def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
 388         res = {}
 389         for account in self.browse(cr, uid, ids, context=context):
 390             res[account.id] = max(account.amount_max - account.ca_invoiced, account.fix_price_to_invoice)
 391         return res
 392
 393     def _real_margin_calc(self, cr, uid, ids, name, arg, context=None):
 394         res = {}
 395         for account in self.browse(cr, uid, ids, context=context):
 396             res[account.id] = account.ca_invoiced + account.total_cost
 397         for id in ids:
 398             res[id] = round(res.get(id, 0.0),2)
 399         return res
 400
 401     def _theorical_margin_calc(self, cr, uid, ids, name, arg, context=None):
 402         res = {}
 403         for account in self.browse(cr, uid, ids, context=context):
 404             res[account.id] = account.ca_theorical + account.total_cost
 405         for id in ids:
 406             res[id] = round(res.get(id, 0.0),2)
 407         return res
 408
 409     def _is_overdue_quantity(self, cr, uid, ids, fieldnames, args, context=None):
 410         result = dict.fromkeys(ids, 0)
 411         for record in self.browse(cr, uid, ids, context=context):
 412             if record.quantity_max > 0.0:
 413                 result[record.id] = int(record.hours_quantity > record.quantity_max)
 414             else:
 415                 result[record.id] = 0
 416         return result
 417
 418     def _get_analytic_account(self, cr, uid, ids, context=None):
 419         result = set()
 420         for line in self.pool.get('account.analytic.line').browse(cr, uid, ids, context=context):
 421             result.add(line.account_id.id)
 422         return list(result)
 423
 424     def _get_total_estimation(self, account):
 425         tot_est = 0.0
 426         if account.fix_price_invoices:
 427             tot_est += account.amount_max
 428         if account.invoice_on_timesheets:
 429             tot_est += account.hours_qtt_est
 430         return tot_est
 431
 432     def _get_total_invoiced(self, account):
 433         total_invoiced = 0.0
 434         if account.fix_price_invoices:
 435             total_invoiced += account.ca_invoiced
 436         if account.invoice_on_timesheets:
 437             total_invoiced += account.timesheet_ca_invoiced
 438         return total_invoiced
 439
 440     def _get_total_remaining(self, account):
 441         total_remaining = 0.0
 442         if account.fix_price_invoices:
 443             total_remaining += account.remaining_ca
 444         if account.invoice_on_timesheets:
 445             total_remaining += account.remaining_hours_to_invoice
 446         return total_remaining
 447
 448     def _get_total_toinvoice(self, account):
 449         total_toinvoice = 0.0
 450         if account.fix_price_invoices:
 451             total_toinvoice += account.fix_price_to_invoice
 452         if account.invoice_on_timesheets:
 453             total_toinvoice += account.ca_to_invoice
 454         return total_toinvoice
 455
 456     def _sum_of_fields(self, cr, uid, ids, name, arg, context=None):
 457          res = dict([(i, {}) for i in ids])
 458          for account in self.browse(cr, uid, ids, context=context):
 459             res[account.id]['est_total'] = self._get_total_estimation(account)
 460             res[account.id]['invoiced_total'] =  self._get_total_invoiced(account)
 461             res[account.id]['remaining_total'] = self._get_total_remaining(account)
 462             res[account.id]['toinvoice_total'] =  self._get_total_toinvoice(account)
 463          return res
 464
 465     _columns = {
 466         'is_overdue_quantity' : fields.function(_is_overdue_quantity, method=True, type='boolean', string='Overdue Quantity',
 467                                                 store={
 468                                                     'account.analytic.line' : (_get_analytic_account, None, 20),
 469                                                     'account.analytic.account': (lambda self, cr, uid, ids, c=None: ids, ['quantity_max'], 10),
 470                                                 }),
 471         'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
 472             help="Total customer invoiced amount for this account.",
 473             digits_compute=dp.get_precision('Account')),
 474         'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
 475             help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
 476             digits_compute=dp.get_precision('Account')),
 477         'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
 478             help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
 479             digits_compute=dp.get_precision('Account')),
 480         'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
 481             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.",
 482             digits_compute=dp.get_precision('Account')),
 483         'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Worked Time',
 484             help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
 485         'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
 486             help="If invoice from the costs, this is the date of the latest invoiced."),
 487         'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
 488             help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
 489         'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
 490             help="Date of the latest work done on this account."),
 491         'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
 492             help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
 493         'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
 494             help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
 495         'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
 496             help="Computed using the formula: Maximum Time - Total Worked Time"),
 497         'remaining_hours_to_invoice': fields.function(_remaining_hours_to_invoice_calc, type='float', string='Remaining Time',
 498             help="Computed using the formula: Expected on timesheets - Total invoiced on timesheets"),
 499         'fix_price_to_invoice': fields.function(_fix_price_to_invoice_calc, type='float', string='Remaining Time',
 500             help="Sum of quotations for this contract."),
 501         'timesheet_ca_invoiced': fields.function(_timesheet_ca_invoiced_calc, type='float', string='Remaining Time',
 502             help="Sum of timesheet lines invoiced for this contract."),
 503         'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
 504             help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
 505             digits_compute=dp.get_precision('Account')),
 506         'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
 507             help="Computed using the formula: Invoiced Amount / Total Time",
 508             digits_compute=dp.get_precision('Account')),
 509         'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
 510             help="Computed using the formula: Invoiced Amount - Total Costs.",
 511             digits_compute=dp.get_precision('Account')),
 512         'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
 513             help="Computed using the formula: Theoretical Revenue - Total Costs",
 514             digits_compute=dp.get_precision('Account')),
 515         'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
 516             help="Computes using the formula: (Real Margin / Total Costs) * 100.",
 517             digits_compute=dp.get_precision('Account')),
 518         'fix_price_invoices' : fields.boolean('Fixed Price'),
 519         'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
 520         'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
 521         'hours_qtt_est': fields.float('Estimation of Hours to Invoice'),
 522         'est_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all", string="Total Estimation"),
 523         'invoiced_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all", string="Total Invoiced"),
 524         '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"),
 525         '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."),
 526         'recurring_invoice_line_ids': fields.one2many('account.analytic.invoice.line', 'analytic_account_id', 'Invoice Lines', copy=True),
 527         'recurring_invoices' : fields.boolean('Generate recurring invoices automatically'),
 528         'recurring_rule_type': fields.selection([
 529             ('daily', 'Day(s)'),
 530             ('weekly', 'Week(s)'),
 531             ('monthly', 'Month(s)'),
 532             ('yearly', 'Year(s)'),
 533             ], 'Recurrency', help="Invoice automatically repeat at specified interval"),
 534         'recurring_interval': fields.integer('Repeat Every', help="Repeat every (Days/Week/Month/Year)"),
 535         'recurring_next_date': fields.date('Date of Next Invoice'),
 536     }
 537
 538     _defaults = {
 539         'recurring_interval': 1,
 540         'recurring_next_date': lambda *a: time.strftime('%Y-%m-%d'),
 541         'recurring_rule_type':'monthly'
 542     }
 543
 544     def open_sale_order_lines(self,cr,uid,ids,context=None):
 545         if context is None:
 546             context = {}
 547         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))])
 548         names = [record.name for record in self.browse(cr, uid, ids, context=context)]
 549         name = _('Sales Order Lines to Invoice of %s') % ','.join(names)
 550         return {
 551             'type': 'ir.actions.act_window',
 552             'name': name,
 553             'view_type': 'form',
 554             'view_mode': 'tree,form',
 555             'context': context,
 556             'domain' : [('order_id','in',sale_ids)],
 557             'res_model': 'sale.order.line',
 558             'nodestroy': True,
 559         }
 560
 561     def on_change_template(self, cr, uid, ids, template_id, date_start=False, context=None):
 562         if not template_id:
 563             return {}
 564         res = super(account_analytic_account, self).on_change_template(cr, uid, ids, template_id, date_start=date_start, context=context)
 565
 566         template = self.browse(cr, uid, template_id, context=context)
 567
 568         if not ids:
 569             res['value']['fix_price_invoices'] = template.fix_price_invoices
 570             res['value']['amount_max'] = template.amount_max
 571         if not ids:
 572             res['value']['invoice_on_timesheets'] = template.invoice_on_timesheets
 573             res['value']['hours_qtt_est'] = template.hours_qtt_est
 574
 575         if template.to_invoice.id:
 576             res['value']['to_invoice'] = template.to_invoice.id
 577         if template.pricelist_id.id:
 578             res['value']['pricelist_id'] = template.pricelist_id.id
 579         if not ids:
 580             invoice_line_ids = []
 581             for x in template.recurring_invoice_line_ids:
 582                 invoice_line_ids.append((0, 0, {
 583                     'product_id': x.product_id.id,
 584                     'uom_id': x.uom_id.id,
 585                     'name': x.name,
 586                     'quantity': x.quantity,
 587                     'price_unit': x.price_unit,
 588                     'analytic_account_id': x.analytic_account_id and x.analytic_account_id.id or False,
 589                 }))
 590             res['value']['recurring_invoices'] = template.recurring_invoices
 591             res['value']['recurring_interval'] = template.recurring_interval
 592             res['value']['recurring_rule_type'] = template.recurring_rule_type
 593             res['value']['recurring_invoice_line_ids'] = invoice_line_ids
 594         return res
 595
 596     def onchange_recurring_invoices(self, cr, uid, ids, recurring_invoices, date_start=False, context=None):
 597         value = {}
 598         if date_start and recurring_invoices:
 599             value = {'value': {'recurring_next_date': date_start}}
 600         return value
 601
 602     def cron_account_analytic_account(self, cr, uid, context=None):
 603         context = dict(context or {})
 604         remind = {}
 605
 606         def fill_remind(key, domain, write_pending=False):
 607             base_domain = [
 608                 ('type', '=', 'contract'),
 609                 ('partner_id', '!=', False),
 610                 ('manager_id', '!=', False),
 611                 ('manager_id.email', '!=', False),
 612             ]
 613             base_domain.extend(domain)
 614
 615             accounts_ids = self.search(cr, uid, base_domain, context=context, order='name asc')
 616             accounts = self.browse(cr, uid, accounts_ids, context=context)
 617             for account in accounts:
 618                 if write_pending:
 619                     account.write({'state' : 'pending'})
 620                 remind_user = remind.setdefault(account.manager_id.id, {})
 621                 remind_type = remind_user.setdefault(key, {})
 622                 remind_partner = remind_type.setdefault(account.partner_id, []).append(account)
 623
 624         # Already expired
 625         fill_remind("old", [('state', 'in', ['pending'])])
 626
 627         # Expires now
 628         fill_remind("new", [('state', 'in', ['draft', 'open']), '|', '&', ('date', '!=', False), ('date', '<=', time.strftime('%Y-%m-%d')), ('is_overdue_quantity', '=', True)], True)
 629
 630         # Expires in less than 30 days
 631         fill_remind("future", [('state', 'in', ['draft', 'open']), ('date', '!=', False), ('date', '<', (datetime.datetime.now() + datetime.timedelta(30)).strftime("%Y-%m-%d"))])
 632
 633         context['base_url'] = self.pool.get('ir.config_parameter').get_param(cr, uid, 'web.base.url')
 634         context['action_id'] = self.pool.get('ir.model.data').get_object_reference(cr, uid, 'account_analytic_analysis', 'action_account_analytic_overdue_all')[1]
 635         template_id = self.pool.get('ir.model.data').get_object_reference(cr, uid, 'account_analytic_analysis', 'account_analytic_cron_email_template')[1]
 636         for user_id, data in remind.items():
 637             context["data"] = data
 638             _logger.debug("Sending reminder to uid %s", user_id)
 639             self.pool.get('email.template').send_mail(cr, uid, template_id, user_id, force_send=True, context=context)
 640
 641         return True
 642
 643     def hr_to_invoice_timesheets(self, cr, uid, ids, context=None):
 644         domain = [('invoice_id','=',False),('to_invoice','!=',False), ('journal_id.type', '=', 'general'), ('account_id', 'in', ids)]
 645         names = [record.name for record in self.browse(cr, uid, ids, context=context)]
 646         name = _('Timesheets to Invoice of %s') % ','.join(names)
 647         return {
 648             'type': 'ir.actions.act_window',
 649             'name': name,
 650             'view_type': 'form',
 651             'view_mode': 'tree,form',
 652             'domain' : domain,
 653             'res_model': 'account.analytic.line',
 654             'nodestroy': True,
 655         }
 656
 657     def _prepare_invoice_data(self, cr, uid, contract, context=None):
 658         context = context or {}
 659
 660         journal_obj = self.pool.get('account.journal')
 661
 662         if not contract.partner_id:
 663             raise osv.except_osv(_('No Customer Defined!'),_("You must first select a Customer for Contract %s!") % contract.name )
 664
 665         fpos = contract.partner_id.property_account_position or False
 666         journal_ids = journal_obj.search(cr, uid, [('type', '=','sale'),('company_id', '=', contract.company_id.id or False)], limit=1)
 667         if not journal_ids:
 668             raise osv.except_osv(_('Error!'),
 669             _('Please define a sale journal for the company "%s".') % (contract.company_id.name or '', ))
 670
 671         partner_payment_term = contract.partner_id.property_payment_term and contract.partner_id.property_payment_term.id or False
 672
 673         currency_id = False
 674         if contract.pricelist_id:
 675             currency_id = contract.pricelist_id.currency_id.id
 676         elif contract.partner_id.property_product_pricelist:
 677             currency_id = contract.partner_id.property_product_pricelist.currency_id.id
 678         elif contract.company_id:
 679             currency_id = contract.company_id.currency_id.id
 680
 681         invoice = {
 682            'account_id': contract.partner_id.property_account_receivable.id,
 683            'type': 'out_invoice',
 684            'partner_id': contract.partner_id.id,
 685            'currency_id': currency_id,
 686            'journal_id': len(journal_ids) and journal_ids[0] or False,
 687            'date_invoice': contract.recurring_next_date,
 688            'origin': contract.code,
 689            'fiscal_position': fpos and fpos.id,
 690            'payment_term': partner_payment_term,
 691            'company_id': contract.company_id.id or False,
 692         }
 693         return invoice
 694
 695     def _prepare_invoice_lines(self, cr, uid, contract, fiscal_position_id, context=None):
 696         fpos_obj = self.pool.get('account.fiscal.position')
 697         fiscal_position = None
 698         if fiscal_position_id:
 699             fiscal_position = fpos_obj.browse(cr, uid,  fiscal_position_id, context=context)
 700         invoice_lines = []
 701         for line in contract.recurring_invoice_line_ids:
 702
 703             res = line.product_id
 704             account_id = res.property_account_income.id
 705             if not account_id:
 706                 account_id = res.categ_id.property_account_income_categ.id
 707             account_id = fpos_obj.map_account(cr, uid, fiscal_position, account_id)
 708
 709             taxes = res.taxes_id or False
 710             tax_id = fpos_obj.map_tax(cr, uid, fiscal_position, taxes)
 711
 712             invoice_lines.append((0, 0, {
 713                 'name': line.name,
 714                 'account_id': account_id,
 715                 'account_analytic_id': contract.id,
 716                 'price_unit': line.price_unit or 0.0,
 717                 'quantity': line.quantity,
 718                 'uos_id': line.uom_id.id or False,
 719                 'product_id': line.product_id.id or False,
 720                 'invoice_line_tax_id': [(6, 0, tax_id)],
 721             }))
 722         return invoice_lines
 723
 724     def _prepare_invoice(self, cr, uid, contract, context=None):
 725         invoice = self._prepare_invoice_data(cr, uid, contract, context=context)
 726         invoice['invoice_line'] = self._prepare_invoice_lines(cr, uid, contract, invoice['fiscal_position'], context=context)
 727         return invoice
 728
 729     def recurring_create_invoice(self, cr, uid, ids, context=None):
 730         return self._recurring_create_invoice(cr, uid, ids, context=context)
 731
 732     def _cron_recurring_create_invoice(self, cr, uid, context=None):
 733         return self._recurring_create_invoice(cr, uid, [], automatic=True, context=context)
 734
 735     def _recurring_create_invoice(self, cr, uid, ids, automatic=False, context=None):
 736         context = context or {}
 737         invoice_ids = []
 738         current_date =  time.strftime('%Y-%m-%d')
 739         if ids:
 740             contract_ids = ids
 741         else:
 742             contract_ids = self.search(cr, uid, [('recurring_next_date','<=', current_date), ('state','=', 'open'), ('recurring_invoices','=', True), ('type', '=', 'contract')])
 743         if contract_ids:
 744             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),))
 745             for company_id, ids in cr.fetchall():
 746                 for contract in self.browse(cr, uid, ids, context=dict(context, company_id=company_id, force_company=company_id)):
 747                     try:
 748                         invoice_values = self._prepare_invoice(cr, uid, contract, context=context)
 749                         invoice_ids.append(self.pool['account.invoice'].create(cr, uid, invoice_values, context=context))
 750                         next_date = datetime.datetime.strptime(contract.recurring_next_date or current_date, "%Y-%m-%d")
 751                         interval = contract.recurring_interval
 752                         if contract.recurring_rule_type == 'daily':
 753                             new_date = next_date+relativedelta(days=+interval)
 754                         elif contract.recurring_rule_type == 'weekly':
 755                             new_date = next_date+relativedelta(weeks=+interval)
 756                         elif contract.recurring_rule_type == 'monthly':
 757                             new_date = next_date+relativedelta(months=+interval)
 758                         else:
 759                             new_date = next_date+relativedelta(years=+interval)
 760                         self.write(cr, uid, [contract.id], {'recurring_next_date': new_date.strftime('%Y-%m-%d')}, context=context)
 761                         if automatic:
 762                             cr.commit()
 763                     except Exception:
 764                         if automatic:
 765                             cr.rollback()
 766                             _logger.exception('Fail to create recurring invoice for contract %s', contract.code)
 767                         else:
 768                             raise
 769         return invoice_ids
 770
 771 class account_analytic_account_summary_user(osv.osv):
 772     _name = "account_analytic_analysis.summary.user"
 773     _description = "Hours Summary by User"
 774     _order='user'
 775     _auto = False
 776     _rec_name = 'user'
 777
 778     def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 779         res = {}
 780         account_obj = self.pool.get('account.analytic.account')
 781         cr.execute('SELECT MAX(id) FROM res_users')
 782         max_user = cr.fetchone()[0]
 783         account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
 784         user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
 785         parent_ids = tuple(account_ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 786         if parent_ids:
 787             cr.execute('SELECT id, unit_amount ' \
 788                     'FROM account_analytic_analysis_summary_user ' \
 789                     'WHERE account_id IN %s ' \
 790                         'AND "user" IN %s',(parent_ids, tuple(user_ids),))
 791             for sum_id, unit_amount in cr.fetchall():
 792                 res[sum_id] = unit_amount
 793         for id in ids:
 794             res[id] = round(res.get(id, 0.0), 2)
 795         return res
 796
 797     _columns = {
 798         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 799         'unit_amount': fields.float('Total Time'),
 800         'user': fields.many2one('res.users', 'User'),
 801     }
 802
 803     _depends = {
 804         'res.users': ['id'],
 805         'account.analytic.line': ['account_id', 'journal_id', 'unit_amount', 'user_id'],
 806         'account.analytic.journal': ['type'],
 807     }
 808
 809     def init(self, cr):
 810         openerp.tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
 811         cr.execute('''CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (
 812             with mu as
 813                 (select max(id) as max_user from res_users)
 814             , lu AS
 815                 (SELECT
 816                  l.account_id AS account_id,
 817                  coalesce(l.user_id, 0) AS user_id,
 818                  SUM(l.unit_amount) AS unit_amount
 819              FROM account_analytic_line AS l,
 820                  account_analytic_journal AS j
 821              WHERE (j.type = 'general' ) and (j.id=l.journal_id)
 822              GROUP BY l.account_id, l.user_id
 823             )
 824             select (lu.account_id * mu.max_user) + lu.user_id as id,
 825                     lu.account_id as account_id,
 826                     lu.user_id as "user",
 827                     unit_amount
 828             from lu, mu)''')
 829
 830 class account_analytic_account_summary_month(osv.osv):
 831     _name = "account_analytic_analysis.summary.month"
 832     _description = "Hours summary by month"
 833     _auto = False
 834     _rec_name = 'month'
 835
 836     _columns = {
 837         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 838         'unit_amount': fields.float('Total Time'),
 839         'month': fields.char('Month', size=32, readonly=True),
 840     }
 841
 842     _depends = {
 843         'account.analytic.line': ['account_id', 'date', 'journal_id', 'unit_amount'],
 844         'account.analytic.journal': ['type'],
 845     }
 846
 847     def init(self, cr):
 848         openerp.tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
 849         cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
 850                 'SELECT ' \
 851                     '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id  * 1000000::bigint))::bigint AS id, ' \
 852                     'd.account_id AS account_id, ' \
 853                     'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
 854                     'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
 855                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 856                 'FROM ' \
 857                     '(SELECT ' \
 858                         'd2.account_id, ' \
 859                         'd2.month ' \
 860                     'FROM ' \
 861                         '(SELECT ' \
 862                             'a.id AS account_id, ' \
 863                             'l.month AS month ' \
 864                         'FROM ' \
 865                             '(SELECT ' \
 866                                 'DATE_TRUNC(\'month\', l.date) AS month ' \
 867                             'FROM account_analytic_line AS l, ' \
 868                                 'account_analytic_journal AS j ' \
 869                             'WHERE j.type = \'general\' ' \
 870                             'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
 871                             ') AS l, ' \
 872                             'account_analytic_account AS a ' \
 873                         'GROUP BY l.month, a.id ' \
 874                         ') AS d2 ' \
 875                     'GROUP BY d2.account_id, d2.month ' \
 876                     ') AS d ' \
 877                 'LEFT JOIN ' \
 878                     '(SELECT ' \
 879                         'l.account_id AS account_id, ' \
 880                         'DATE_TRUNC(\'month\', l.date) AS month, ' \
 881                         'SUM(l.unit_amount) AS unit_amount ' \
 882                     'FROM account_analytic_line AS l, ' \
 883                         'account_analytic_journal AS j ' \
 884                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 885                     'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
 886                     ') AS l '
 887                     'ON (' \
 888                         'd.account_id = l.account_id ' \
 889                         'AND d.month = l.month' \
 890                     ') ' \
 891                 'GROUP BY d.month, d.account_id ' \
 892                 ')')
 893
 894 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: