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