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
  22 from osv import osv, fields
  23 from osv.orm import intersect, except_orm
  24 import tools.sql
  25 from tools.translate import _
  26 from decimal_precision import decimal_precision as dp
  27
  28
  29 class account_analytic_account(osv.osv):
  30     _name = "account.analytic.account"
  31     _inherit = "account.analytic.account"
  32
  33     def _analysis_all(self, cr, uid, ids, fields, arg, context=None):
  34         dp = 2
  35         res = dict([(i, {}) for i in ids])
  36         parent_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
  37         accounts = self.browse(cr, uid, ids, context=context)
  38
  39         for f in fields:
  40             if f == 'user_ids':
  41                 cr.execute('SELECT MAX(id) FROM res_users')
  42                 max_user = cr.fetchone()[0]
  43                 if parent_ids:
  44                     cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
  45                                'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
  46                     result = cr.fetchall()
  47                 else:
  48                     result = []
  49                 for id in ids:
  50                     res[id][f] = [int((id * max_user) + x[0]) for x in result]
  51             elif f == 'month_ids':
  52                 if parent_ids:
  53                     cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
  54                                'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
  55                     result = cr.fetchall()
  56                 else:
  57                     result = []
  58                 for id in ids:
  59                     res[id][f] = [int(id * 1000000 + int(x[0])) for x in result]
  60             elif f == 'last_worked_invoiced_date':
  61                 for id in ids:
  62                     res[id][f] = False
  63                 if parent_ids:
  64                     cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
  65                             FROM account_analytic_line \
  66                             WHERE account_id IN %s \
  67                                 AND invoice_id IS NOT NULL \
  68                             GROUP BY account_analytic_line.account_id;", (parent_ids,))
  69                     for account_id, sum in cr.fetchall():
  70                         if account_id not in res:
  71                             res[account_id] = {}
  72                         res[account_id][f] = sum
  73             elif f == 'ca_to_invoice':
  74                 for id in ids:
  75                     res[id][f] = 0.0
  76                 res2 = {}
  77                 if parent_ids:
  78                     # Amount uninvoiced hours to invoice at sale price
  79                     # Warning
  80                     # This computation doesn't take care of pricelist !
  81                     # Just consider list_price
  82                     cr.execute("""SELECT account_analytic_account.id, \
  83                                 COALESCE(SUM (product_template.list_price * \
  84                                     account_analytic_line.unit_amount * \
  85                                     ((100-hr_timesheet_invoice_factor.factor)/100)), 0.0) \
  86                                     AS ca_to_invoice \
  87                             FROM product_template \
  88                             JOIN product_product \
  89                                 ON product_template.id = product_product.product_tmpl_id \
  90                             JOIN account_analytic_line \
  91                                 ON account_analytic_line.product_id = product_product.id \
  92                             JOIN account_analytic_journal \
  93                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
  94                             JOIN account_analytic_account \
  95                                 ON account_analytic_account.id = account_analytic_line.account_id \
  96                             JOIN hr_timesheet_invoice_factor \
  97                                 ON hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
  98                             WHERE account_analytic_account.id IN %s \
  99                                 AND account_analytic_line.invoice_id IS NULL \
 100                                 AND account_analytic_line.to_invoice IS NOT NULL \
 101                                 AND account_analytic_journal.type IN ('purchase', 'general') \
 102                             GROUP BY account_analytic_account.id;""", (parent_ids,))
 103                     for account_id, sum in cr.fetchall():
 104                         if account_id not in res:
 105                             res[account_id] = {}
 106                         res[account_id][f] = round(sum, dp)
 107
 108                 # sum both result on account_id
 109                 for id in ids:
 110                     res[id][f] = round(res.get(id, {}).get(f, 0.0), dp) + round(res2.get(id, 0.0), 2)
 111             elif f == 'last_invoice_date':
 112                 for id in ids:
 113                     res[id][f] = False
 114                 if parent_ids:
 115                     cr.execute ("SELECT account_analytic_line.account_id, \
 116                                 DATE(MAX(account_invoice.date_invoice)) \
 117                             FROM account_analytic_line \
 118                             JOIN account_invoice \
 119                                 ON account_analytic_line.invoice_id = account_invoice.id \
 120                             WHERE account_analytic_line.account_id IN %s \
 121                                 AND account_analytic_line.invoice_id IS NOT NULL \
 122                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 123                     for account_id, lid in cr.fetchall():
 124                         res[account_id][f] = lid
 125             elif f == 'last_worked_date':
 126                 for id in ids:
 127                     res[id][f] = False
 128                 if parent_ids:
 129                     cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
 130                             FROM account_analytic_line \
 131                             WHERE account_id IN %s \
 132                                 AND invoice_id IS NULL \
 133                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 134                     for account_id, lwd in cr.fetchall():
 135                         if account_id not in res:
 136                             res[account_id] = {}
 137                         res[account_id][f] = lwd
 138             elif f == 'hours_qtt_non_invoiced':
 139                 for id in ids:
 140                     res[id][f] = 0.0
 141                 if parent_ids:
 142                     cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
 143                             FROM account_analytic_line \
 144                             JOIN account_analytic_journal \
 145                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
 146                             WHERE account_analytic_line.account_id IN %s \
 147                                 AND account_analytic_journal.type='general' \
 148                                 AND invoice_id IS NULL \
 149                                 AND to_invoice IS NOT NULL \
 150                             GROUP BY account_analytic_line.account_id;",(parent_ids,))
 151                     for account_id, sua in cr.fetchall():
 152                         if account_id not in res:
 153                             res[account_id] = {}
 154                         res[account_id][f] = round(sua, dp)
 155                 for id in ids:
 156                     res[id][f] = round(res[id][f], dp)
 157             elif f == 'hours_quantity':
 158                 for id in ids:
 159                     res[id][f] = 0.0
 160                 if parent_ids:
 161                     cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
 162                             FROM account_analytic_line \
 163                             JOIN account_analytic_journal \
 164                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
 165                             WHERE account_analytic_line.account_id IN %s \
 166                                 AND account_analytic_journal.type='general' \
 167                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 168                     ff =  cr.fetchall()
 169                     for account_id, hq in ff:
 170                         if account_id not in res:
 171                             res[account_id] = {}
 172                         res[account_id][f] = round(hq, dp)
 173                 for id in ids:
 174                     res[id][f] = round(res[id][f], dp)
 175             elif f == 'ca_theorical':
 176                 # TODO Take care of pricelist and purchase !
 177                 for id in ids:
 178                     res[id][f] = 0.0
 179                 # Warning
 180                 # This computation doesn't take care of pricelist !
 181                 # Just consider list_price
 182                 if parent_ids:
 183                     cr.execute("""SELECT account_analytic_line.account_id AS account_id, \
 184                                 COALESCE(SUM((account_analytic_line.unit_amount * pt.list_price) \
 185                                     - (account_analytic_line.unit_amount * pt.list_price \
 186                                         * hr.factor)), 0.0) AS somme
 187                             FROM account_analytic_line \
 188                             LEFT JOIN account_analytic_journal \
 189                                 ON (account_analytic_line.journal_id = account_analytic_journal.id) \
 190                             JOIN product_product pp \
 191                                 ON (account_analytic_line.product_id = pp.id) \
 192                             JOIN product_template pt \
 193                                 ON (pp.product_tmpl_id = pt.id) \
 194                             JOIN account_analytic_account a \
 195                                 ON (a.id=account_analytic_line.account_id) \
 196                             JOIN hr_timesheet_invoice_factor hr \
 197                                 ON (hr.id=a.to_invoice) \
 198                         WHERE account_analytic_line.account_id IN %s \
 199                             AND a.to_invoice IS NOT NULL \
 200                             AND account_analytic_journal.type IN ('purchase', 'general')
 201                         GROUP BY account_analytic_line.account_id""",(parent_ids,))
 202                     for account_id, sum in cr.fetchall():
 203                         res[account_id][f] = round(sum, dp)
 204         return res
 205
 206     def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 207         res = {}
 208         res_final = {}
 209         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 210         for i in child_ids:
 211             res[i] =  0.0
 212         if not child_ids:
 213             return res
 214
 215         if child_ids:
 216             cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(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 = 'sale' \
 222                     GROUP BY account_analytic_line.account_id", (child_ids,))
 223             for account_id, sum in cr.fetchall():
 224                 res[account_id] = round(sum,2)
 225         res_final = res
 226         return res_final
 227
 228     def _expense_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 229         res = {}
 230         res_final = {}
 231         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 232         for i in child_ids:
 233             res[i] =  0.0
 234         if not child_ids:
 235             return res
 236
 237         if child_ids:
 238             cr.execute("SELECT hel.analytic_account,SUM(hel.unit_amount*hel.unit_quantity)\
 239                     FROM hr_expense_line AS hel\
 240                     LEFT JOIN hr_expense_expense AS he \
 241                         ON he.id = hel.expense_id\
 242                     WHERE he.state = 'paid' \
 243                          AND hel.analytic_account IN %s \
 244                     GROUP BY hel.analytic_account",(child_ids,))
 245             for account_id, sum in cr.fetchall():
 246                 res[account_id] = sum
 247         res_final = res
 248         return res_final
 249
 250     def _expense_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
 251         res = {}
 252         res_final = {}
 253         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 254         for i in child_ids:
 255             res[i] =  0.0
 256         if not child_ids:
 257             return res
 258
 259         if child_ids:
 260             cr.execute("SELECT hel.analytic_account, SUM(hel.unit_amount*hel.unit_quantity) \
 261                     FROM hr_expense_line AS hel\
 262                     LEFT JOIN hr_expense_expense AS he \
 263                         ON he.id = hel.expense_id\
 264                     WHERE he.state = 'invoiced' \
 265                         AND hel.analytic_account IN %s \
 266                     GROUP BY hel.analytic_account",(child_ids,))
 267             for account_id, sum in cr.fetchall():
 268                 res[account_id] = sum
 269         res_final = res
 270         return res_final
 271
 272     def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
 273         res = {}
 274         res_final = {}
 275         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 276         for i in child_ids:
 277             res[i] =  0.0
 278         if not child_ids:
 279             return res
 280         if child_ids:
 281             cr.execute("""SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
 282                     FROM account_analytic_line \
 283                     JOIN account_analytic_journal \
 284                         ON account_analytic_line.journal_id = account_analytic_journal.id \
 285                     WHERE account_analytic_line.account_id IN %s \
 286                         AND amount<0 \
 287                     GROUP BY account_analytic_line.account_id""",(child_ids,))
 288             for account_id, sum in cr.fetchall():
 289                 res[account_id] = round(sum,2)
 290         res_final = res
 291         return res_final
 292
 293     def _remaining_hours_calc(self, cr, uid, ids, name, arg, context=None):
 294         res = {}
 295         for account in self.browse(cr, uid, ids, context=context):
 296             if account.quantity_max != 0:
 297                 res[account.id] = account.quantity_max - account.hours_qtt_invoiced
 298             else:
 299                 res[account.id] = 0.0
 300         for id in ids:
 301             res[id] = round(res.get(id, 0.0),2)
 302         return res
 303
 304     def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 305         res = {}
 306         for account in self.browse(cr, uid, ids, context=context):
 307             res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
 308             if res[account.id] < 0:
 309                 res[account.id] = 0.0
 310         for id in ids:
 311             res[id] = round(res.get(id, 0.0),2)
 312         return res
 313
 314     def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
 315         res = {}
 316         for account in self.browse(cr, uid, ids, context=context):
 317             if account.hours_qtt_invoiced == 0:
 318                 res[account.id]=0.0
 319             else:
 320                 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
 321         for id in ids:
 322             res[id] = round(res.get(id, 0.0),2)
 323         return res
 324
 325     def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
 326         res = {}
 327         for account in self.browse(cr, uid, ids, context=context):
 328             if account.ca_invoiced == 0:
 329                 res[account.id]=0.0
 330             elif account.total_cost != 0.0:
 331                 res[account.id] = -(account.real_margin / account.total_cost) * 100
 332             else:
 333                 res[account.id] = 0.0
 334         for id in ids:
 335             res[id] = round(res.get(id, 0.0),2)
 336         return res
 337
 338     def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
 339         res = {}
 340         for account in self.browse(cr, uid, ids, context=context):
 341             if account.amount_max != 0:
 342                 res[account.id] = account.amount_max - account.ca_invoiced
 343             else:
 344                 res[account.id]=0.0
 345         for id in ids:
 346             res[id] = round(res.get(id, 0.0),2)
 347         return res
 348
 349     def _remaining_expnse_calc(self, cr, uid, ids, name, arg, context=None):
 350         res = {}
 351         for account in self.browse(cr, uid, ids, context=context):
 352             if account.expense_max != 0:
 353                 res[account.id] = account.expense_max - account.expense_invoiced
 354             else:
 355                 res[account.id]=0.0
 356         for id in ids:
 357             res[id] = round(res.get(id, 0.0),2)
 358         return res
 359
 360     def _real_margin_calc(self, cr, uid, ids, name, arg, context=None):
 361         res = {}
 362         for account in self.browse(cr, uid, ids, context=context):
 363             res[account.id] = account.ca_invoiced + account.total_cost
 364         for id in ids:
 365             res[id] = round(res.get(id, 0.0),2)
 366         return res
 367
 368     def _theorical_margin_calc(self, cr, uid, ids, name, arg, context=None):
 369         res = {}
 370         for account in self.browse(cr, uid, ids, context=context):
 371             res[account.id] = account.ca_theorical + account.total_cost
 372         for id in ids:
 373             res[id] = round(res.get(id, 0.0),2)
 374         return res
 375
 376     def _is_overdue_quantity(self, cr, uid, ids, fieldnames, args, context=None):
 377         result = dict.fromkeys(ids, 0)
 378         for record in self.browse(cr, uid, ids, context=context):
 379             if record.quantity_max > 0.0:
 380                 result[record.id] = int(record.hours_quantity >= record.quantity_max)
 381             else:
 382                 result[record.id] = 0
 383         return result
 384
 385     def _get_analytic_account(self, cr, uid, ids, context=None):
 386         result = set()
 387         for line in self.pool.get('account.analytic.line').browse(cr, uid, ids, context=context):
 388             result.add(line.account_id.id)
 389         return list(result)
 390
 391     def _sum_of_fields(self, cr, uid, ids, name, arg, context=None):
 392          res = dict([(i, {}) for i in ids])
 393          total_max = 0.0
 394          total_invoiced = 0.0
 395          total_toinvoice = 0.0
 396          total_remaining = 0.0
 397          for account in self.browse(cr, uid, ids, context=context):
 398             if account.fix_price_invoices:
 399                 total_max += account.amount_max
 400                 total_invoiced += account.ca_invoiced
 401                 total_remaining += account.remaining_ca
 402                 total_toinvoice += account.ca_to_invoice
 403             if account.invoice_on_timesheets:
 404                 total_max += account.quantity_max
 405                 total_invoiced += account.hours_qtt_invoiced
 406                 total_remaining += account.remaining_hours
 407                 total_toinvoice += account.hours_qtt_non_invoiced
 408             if account.charge_expenses:
 409                 total_max += account.expense_max
 410                 total_invoiced += account.expense_invoiced
 411                 total_remaining += account.remaining_expense
 412                 total_toinvoice += account.expense_to_invoice
 413             res[account.id]['est_total'] = total_max or 0.0
 414             res[account.id]['invoiced_total'] =  total_invoiced or 0.0
 415             res[account.id]['remaining_total'] = total_remaining or 0.0
 416             res[account.id]['toinvoice_total'] =  total_toinvoice or 0.0
 417          return res
 418
 419     _columns = {
 420         'is_overdue_quantity' : fields.function(_is_overdue_quantity, method=True, type='boolean', string='Overdue Quantity',
 421                                                 store={
 422                                                     'account.analytic.line' : (_get_analytic_account, None, 20),
 423                                                 }),
 424         'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
 425             help="Total customer invoiced amount for this account.",
 426             digits_compute=dp.get_precision('Account')),
 427         'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
 428             help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
 429             digits_compute=dp.get_precision('Account')),
 430         'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
 431             help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
 432             digits_compute=dp.get_precision('Account')),
 433         'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
 434             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.",
 435             digits_compute=dp.get_precision('Account')),
 436         'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Time',
 437             help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
 438         'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
 439             help="If invoice from the costs, this is the date of the latest invoiced."),
 440         'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
 441             help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
 442         'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
 443             help="Date of the latest work done on this account."),
 444         'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
 445             help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
 446         'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
 447             help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
 448         'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
 449             help="Computed using the formula: Maximum Time - Total Time"),
 450         'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
 451             help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
 452             digits_compute=dp.get_precision('Account')),
 453         'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
 454             help="Computed using the formula: Invoiced Amount / Total Time",
 455             digits_compute=dp.get_precision('Account')),
 456         'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
 457             help="Computed using the formula: Invoiced Amount - Total Costs.",
 458             digits_compute=dp.get_precision('Account')),
 459         'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
 460             help="Computed using the formula: Theorial Revenue - Total Costs",
 461             digits_compute=dp.get_precision('Account')),
 462         'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
 463             help="Computes using the formula: (Real Margin / Total Costs) * 100.",
 464             digits_compute=dp.get_precision('Account')),
 465         'fix_price_invoices' : fields.boolean('Fix Price Invoices'),
 466         'invoice_on_timesheets' : fields.boolean("Invoice On Timesheets"),
 467         'charge_expenses' : fields.boolean('Charge Expenses'),
 468         'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
 469         'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
 470         'template_id':fields.many2one('account.analytic.account', 'Template of Contract'),
 471         'expense_invoiced' : fields.function(_expense_invoiced_calc, type="float"),
 472         'expense_to_invoice' : fields.function(_expense_to_invoice_calc, type='float'),
 473         'remaining_expense' : fields.function(_remaining_expnse_calc, type="float"),
 474         #'fix_exp_max' : fields.float('Max. amt'),
 475         #'timesheet_max': fields.float('max_timesheet'),
 476         'expense_max': fields.float('expenses'),
 477         'est_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
 478         'invoiced_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
 479         'remaining_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
 480         'toinvoice_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
 481     }
 482     def on_change_template(self, cr, uid, id, template_id):
 483         if not template_id:
 484             return {}
 485         res = {'value':{}}
 486         template = self.browse(cr, uid, template_id)
 487         if template.date_start:
 488             res['value']['date_start'] = str(template.date_start)
 489         if template.date:
 490             res['value']['date'] = str(template.date)
 491         res['value']['fix_price_invoices'] = template.fix_price_invoices
 492         res['value']['invoice_on_timesheets'] = template.invoice_on_timesheets
 493         res['value']['charge_expenses'] = template.charge_expenses
 494         res['value']['quantity_max'] = template.quantity_max
 495         res['value']['remaining_hours'] = template.remaining_hours
 496         res['value']['amount_max'] = template.amount_max
 497         res['value']['expense_max'] = template.expense_max
 498         res['value']['to_invoice'] = template.to_invoice.id
 499         res['value']['pricelist_id'] = template.pricelist_id.id
 500         res['value']['description'] = template.description
 501         return res
 502
 503     def open_hr_expense(self, cr, uid, ids, context=None):
 504         account = self.browse(cr, uid, ids[0], context)
 505         data_obj = self.pool.get('ir.model.data')
 506         try:
 507             journal_id = data_obj.get_object(cr, uid, 'hr_timesheet', 'analytic_journal').id
 508         except ValueError:
 509             journal_id = False
 510         line_ids = self.pool.get('hr.expense.line').search(cr,uid,[('analytic_account','=',account.id)])
 511         id2 = data_obj._get_id(cr, uid, 'hr_expense', 'view_expenses_form')
 512         id3 = data_obj._get_id(cr, uid, 'hr_expense', 'view_expenses_tree')
 513         if id2:
 514             id2 = data_obj.browse(cr, uid, id2, context=context).res_id
 515         if id3:
 516             id3 = data_obj.browse(cr, uid, id3, context=context).res_id
 517         domain = [('line_ids','in',line_ids)]
 518         return {
 519             'type': 'ir.actions.act_window',
 520             'name': _('Expenses'),
 521             'view_type': 'form',
 522             'view_mode': 'tree,form',
 523             'views': [(id3,'tree'),(id2,'form')],
 524             'domain' : domain,
 525             'res_model': 'hr.expense.expense',
 526             'nodestroy': True,
 527         }
 528
 529     def hr_to_invoiced_expense(self, cr, uid, ids, context=None):
 530          res = self.open_hr_expense(cr,uid,ids,context)
 531          account = self.browse(cr, uid, ids[0], context)
 532          line_ids = self.pool.get('hr.expense.line').search(cr,uid,[('analytic_account','=',account.id)])
 533          res['domain'] = [('line_ids','in',line_ids),('state','=','invoiced')]
 534          return res
 535
 536 account_analytic_account()
 537
 538 class account_analytic_account_summary_user(osv.osv):
 539     _name = "account_analytic_analysis.summary.user"
 540     _description = "Hours Summary by User"
 541     _order='user'
 542     _auto = False
 543     _rec_name = 'user'
 544
 545     def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 546         res = {}
 547         account_obj = self.pool.get('account.analytic.account')
 548         cr.execute('SELECT MAX(id) FROM res_users')
 549         max_user = cr.fetchone()[0]
 550         account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
 551         user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
 552         parent_ids = tuple(account_ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 553         if parent_ids:
 554             cr.execute('SELECT id, unit_amount ' \
 555                     'FROM account_analytic_analysis_summary_user ' \
 556                     'WHERE account_id IN %s ' \
 557                         'AND "user" IN %s',(parent_ids, tuple(user_ids),))
 558             for sum_id, unit_amount in cr.fetchall():
 559                 res[sum_id] = unit_amount
 560         for id in ids:
 561             res[id] = round(res.get(id, 0.0), 2)
 562         return res
 563
 564     _columns = {
 565         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 566         'unit_amount': fields.float('Total Time'),
 567         'user': fields.many2one('res.users', 'User'),
 568     }
 569
 570     def init(self, cr):
 571         tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
 572         cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
 573                 'SELECT ' \
 574                     '(u.account_id * u.max_user) + u."user" AS id, ' \
 575                     'u.account_id AS account_id, ' \
 576                     'u."user" AS "user", ' \
 577                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 578                 'FROM ' \
 579                     '(SELECT ' \
 580                         'a.id AS account_id, ' \
 581                         'u1.id AS "user", ' \
 582                         'MAX(u2.id) AS max_user ' \
 583                     'FROM ' \
 584                         'res_users AS u1, ' \
 585                         'res_users AS u2, ' \
 586                         'account_analytic_account AS a ' \
 587                     'GROUP BY u1.id, a.id ' \
 588                     ') AS u ' \
 589                 'LEFT JOIN ' \
 590                     '(SELECT ' \
 591                         'l.account_id AS account_id, ' \
 592                         'l.user_id AS "user", ' \
 593                         'SUM(l.unit_amount) AS unit_amount ' \
 594                     'FROM account_analytic_line AS l, ' \
 595                         'account_analytic_journal AS j ' \
 596                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 597                     'GROUP BY l.account_id, l.user_id ' \
 598                     ') AS l '
 599                     'ON (' \
 600                         'u.account_id = l.account_id ' \
 601                         'AND u."user" = l."user"' \
 602                     ') ' \
 603                 'GROUP BY u."user", u.account_id, u.max_user' \
 604                 ')')
 605
 606 account_analytic_account_summary_user()
 607
 608 class account_analytic_account_summary_month(osv.osv):
 609     _name = "account_analytic_analysis.summary.month"
 610     _description = "Hours summary by month"
 611     _auto = False
 612     _rec_name = 'month'
 613
 614     _columns = {
 615         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 616         'unit_amount': fields.float('Total Time'),
 617         'month': fields.char('Month', size=32, readonly=True),
 618     }
 619
 620     def init(self, cr):
 621         tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
 622         cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
 623                 'SELECT ' \
 624                     '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id  * 1000000::bigint))::bigint AS id, ' \
 625                     'd.account_id AS account_id, ' \
 626                     'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
 627                     'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
 628                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 629                 'FROM ' \
 630                     '(SELECT ' \
 631                         'd2.account_id, ' \
 632                         'd2.month ' \
 633                     'FROM ' \
 634                         '(SELECT ' \
 635                             'a.id AS account_id, ' \
 636                             'l.month AS month ' \
 637                         'FROM ' \
 638                             '(SELECT ' \
 639                                 'DATE_TRUNC(\'month\', l.date) AS month ' \
 640                             'FROM account_analytic_line AS l, ' \
 641                                 'account_analytic_journal AS j ' \
 642                             'WHERE j.type = \'general\' ' \
 643                             'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
 644                             ') AS l, ' \
 645                             'account_analytic_account AS a ' \
 646                         'GROUP BY l.month, a.id ' \
 647                         ') AS d2 ' \
 648                     'GROUP BY d2.account_id, d2.month ' \
 649                     ') AS d ' \
 650                 'LEFT JOIN ' \
 651                     '(SELECT ' \
 652                         'l.account_id AS account_id, ' \
 653                         'DATE_TRUNC(\'month\', l.date) AS month, ' \
 654                         'SUM(l.unit_amount) AS unit_amount ' \
 655                     'FROM account_analytic_line AS l, ' \
 656                         'account_analytic_journal AS j ' \
 657                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 658                     'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
 659                     ') AS l '
 660                     'ON (' \
 661                         'd.account_id = l.account_id ' \
 662                         'AND d.month = l.month' \
 663                     ') ' \
 664                 'GROUP BY d.month, d.account_id ' \
 665                 ')')
 666
 667
 668 account_analytic_account_summary_month()
 669 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: