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
  37         parent_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)], context=context))
  38         accounts = self.browse(cr, uid, ids, context=context)
  39
  40         for f in fields:
  41             if f == 'user_ids':
  42                 cr.execute('SELECT MAX(id) FROM res_users')
  43                 max_user = cr.fetchone()[0]
  44                 if parent_ids:
  45                     cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
  46                                'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
  47                     result = cr.fetchall()
  48                 else:
  49                     result = []
  50                 for id in ids:
  51                     res[id][f] = [int((id * max_user) + x[0]) for x in result]
  52             elif f == 'month_ids':
  53                 if parent_ids:
  54                     cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
  55                                'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
  56                     result = cr.fetchall()
  57                 else:
  58                     result = []
  59                 for id in ids:
  60                     res[id][f] = [int(id * 1000000 + int(x[0])) for x in result]
  61             elif f == 'last_worked_invoiced_date':
  62                 for id in ids:
  63                     res[id][f] = 0.0
  64                 if parent_ids:
  65                     cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
  66                             FROM account_analytic_line \
  67                             WHERE account_id IN %s \
  68                                 AND invoice_id IS NOT NULL \
  69                             GROUP BY account_analytic_line.account_id;", (parent_ids,))
  70                     for account_id, sum in cr.fetchall():
  71                         if account_id not in res:
  72                             res[account_id] = {}
  73                         res[account_id][f] = sum
  74                 for account in accounts:
  75                     for child in account.child_ids:
  76                         if res[account.id].get(f, '') < res.get(child.id, {}).get(f, ''):
  77                             res[account.id][f] = res.get(child.id, {}).get(f, '')
  78             elif f == 'ca_to_invoice':
  79                 for id in ids:
  80                     res[id][f] = 0.0
  81                 res2 = {}
  82                 if parent_ids:
  83                     # Amount uninvoiced hours to invoice at sale price
  84                     # Warning
  85                     # This computation doesn't take care of pricelist !
  86                     # Just consider list_price
  87                     cr.execute("""SELECT account_analytic_account.id, \
  88                                 COALESCE(SUM (product_template.list_price * \
  89                                     account_analytic_line.unit_amount * \
  90                                     ((100-hr_timesheet_invoice_factor.factor)/100)), 0.0) \
  91                                     AS ca_to_invoice \
  92                             FROM product_template \
  93                             JOIN product_product \
  94                                 ON product_template.id = product_product.product_tmpl_id \
  95                             JOIN account_analytic_line \
  96                                 ON account_analytic_line.product_id = product_product.id \
  97                             JOIN account_analytic_journal \
  98                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
  99                             JOIN account_analytic_account \
 100                                 ON account_analytic_account.id = account_analytic_line.account_id \
 101                             JOIN hr_timesheet_invoice_factor \
 102                                 ON hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
 103                             WHERE account_analytic_account.id IN %s \
 104                                 AND account_analytic_line.invoice_id IS NULL \
 105                                 AND account_analytic_line.to_invoice IS NOT NULL \
 106                                 AND account_analytic_journal.type IN ('purchase','general') \
 107                             GROUP BY account_analytic_account.id;""", (parent_ids,))
 108                     for account_id, sum in cr.fetchall():
 109                         if account_id not in res:
 110                             res[account_id] = {}
 111                         res[account_id][f] = round(sum, dp)
 112
 113                 for account in accounts:
 114                     #res.setdefault(account.id, 0.0)
 115                     res2.setdefault(account.id, 0.0)
 116                     for child in account.child_ids:
 117                         if child.id != account.id:
 118                             res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
 119                             res2[account.id] += res2.get(child.id, 0.0)
 120                 # sum both result on account_id
 121                 for id in ids:
 122                     res[id][f] = round(res.get(id, {}).get(f, 0.0), dp) + round(res2.get(id, 0.0), 2)
 123             elif f == 'last_invoice_date':
 124                 for id in ids:
 125                     res[id][f] = ''
 126                 if parent_ids:
 127                     cr.execute ("SELECT account_analytic_line.account_id, \
 128                                 DATE(MAX(account_invoice.date_invoice)) \
 129                             FROM account_analytic_line \
 130                             JOIN account_invoice \
 131                                 ON account_analytic_line.invoice_id = account_invoice.id \
 132                             WHERE account_analytic_line.account_id IN %s \
 133                                 AND account_analytic_line.invoice_id IS NOT NULL \
 134                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 135                     for account_id, lid in cr.fetchall():
 136                         res[account_id][f] = lid
 137                 for account in accounts:
 138                     for child in account.child_ids:
 139                         if res[account.id][f] < res.get(child.id, {}).get(f, ''):
 140                             res[account.id][f] = res.get(child.id, {}).get(f, '')
 141             elif f == 'last_worked_date':
 142                 for id in ids:
 143                     res[id][f] = ''
 144                 if parent_ids:
 145                     cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
 146                             FROM account_analytic_line \
 147                             WHERE account_id IN %s \
 148                                 AND invoice_id IS NULL \
 149                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 150                     for account_id, lwd in cr.fetchall():
 151                         if account_id not in res:
 152                             res[account_id] = {}
 153                         res[account_id][f] = lwd
 154                 for account in accounts:
 155                     for child in account.child_ids:
 156                         if res[account.id][f] < res.get(child.id, {}).get(f, ''):
 157                             res[account.id][f] = res.get(child.id, {}).get(f, '')
 158             elif f == 'hours_qtt_non_invoiced':
 159                 for id in ids:
 160                     res[id][f] = 0.0
 161                 if parent_ids:
 162                     cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
 163                             FROM account_analytic_line \
 164                             JOIN account_analytic_journal \
 165                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
 166                             WHERE account_analytic_line.account_id IN %s \
 167                                 AND account_analytic_journal.type='general' \
 168                                 AND invoice_id IS NULL \
 169                                 AND to_invoice IS NOT NULL \
 170                             GROUP BY account_analytic_line.account_id;",(parent_ids,))
 171                     for account_id, sua in cr.fetchall():
 172                         if account_id not in res:
 173                             res[account_id] = {}
 174                         res[account_id][f] = round(sua, dp)
 175                 for account in accounts:
 176                     for child in account.child_ids:
 177                         if account.id != child.id:
 178                             res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
 179                 for id in ids:
 180                     res[id][f] = round(res[id][f], dp)
 181             elif f == 'hours_quantity':
 182                 for id in ids:
 183                     res[id][f] = 0.0
 184                 if parent_ids:
 185                     cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
 186                             FROM account_analytic_line \
 187                             JOIN account_analytic_journal \
 188                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
 189                             WHERE account_analytic_line.account_id IN %s \
 190                                 AND account_analytic_journal.type='general' \
 191                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 192                     ff =  cr.fetchall()
 193                     for account_id, hq in ff:
 194                         if account_id not in res:
 195                             res[account_id] = {}
 196                         res[account_id][f] = round(hq, dp)
 197                 for account in accounts:
 198                     for child in account.child_ids:
 199                         if account.id != child.id:
 200                             if account.id not in res:
 201                                 res[account.id] = {f: 0.0}
 202                             res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
 203                 for id in ids:
 204                     res[id][f] = round(res[id][f], dp)
 205             elif f == 'ca_theorical':
 206                 # TODO Take care of pricelist and purchase !
 207                 for id in ids:
 208                     res[id][f] = 0.0
 209                 res2 = {}
 210                 # Warning
 211                 # This computation doesn't take care of pricelist !
 212                 # Just consider list_price
 213                 if parent_ids:
 214                     cr.execute("""SELECT account_analytic_line.account_id AS account_id, \
 215                                 COALESCE(SUM((account_analytic_line.unit_amount * pt.list_price) \
 216                                     - (account_analytic_line.unit_amount * pt.list_price \
 217                                         * hr.factor)), 0.0) AS somme
 218                             FROM account_analytic_line \
 219                             LEFT JOIN account_analytic_journal \
 220                                 ON (account_analytic_line.journal_id = account_analytic_journal.id) \
 221                             JOIN product_product pp \
 222                                 ON (account_analytic_line.product_id = pp.id) \
 223                             JOIN product_template pt \
 224                                 ON (pp.product_tmpl_id = pt.id) \
 225                             JOIN account_analytic_account a \
 226                                 ON (a.id=account_analytic_line.account_id) \
 227                             JOIN hr_timesheet_invoice_factor hr \
 228                                 ON (hr.id=a.to_invoice) \
 229                         WHERE account_analytic_line.account_id IN %s \
 230                             AND a.to_invoice IS NOT NULL \
 231                             AND account_analytic_journal.type IN ('purchase', 'general')
 232                         GROUP BY account_analytic_line.account_id""",(parent_ids,))
 233                     for account_id, sum in cr.fetchall():
 234                         res2[account_id] = round(sum, dp)
 235
 236                 for account in accounts:
 237                     res2.setdefault(account.id, 0.0)
 238                     for child in account.child_ids:
 239                         if account.id != child.id:
 240                             if account.id not in res:
 241                                 res[account.id] = {f: 0.0}
 242                             res[account.id][f] += res.get(child.id, {}).get(f, 0.0)
 243                             res[account.id][f] += res2.get(child.id, 0.0)
 244
 245                 # sum both result on account_id
 246                 for id in ids:
 247                     res[id][f] = round(res[id][f], dp) + round(res2.get(id, 0.0), dp)
 248
 249         return res
 250
 251     def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 252         res = {}
 253         res_final = {}
 254         child_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)], context=context))
 255         for i in child_ids:
 256             res[i] =  {}
 257             for n in [name]:
 258                 res[i][n] = 0.0
 259         if not child_ids:
 260             return res
 261
 262         if child_ids:
 263             cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
 264                     FROM account_analytic_line \
 265                     JOIN account_analytic_journal \
 266                         ON account_analytic_line.journal_id = account_analytic_journal.id  \
 267                     WHERE account_analytic_line.account_id IN %s \
 268                         AND account_analytic_journal.type = 'sale' \
 269                     GROUP BY account_analytic_line.account_id", (child_ids,))
 270             for account_id, sum in cr.fetchall():
 271                 res[account_id][name] = round(sum,2)
 272         data = self._compute_level_tree(cr, uid, ids, child_ids, res, [name], context=context)
 273         for i in data:
 274             res_final[i] = data[i][name]
 275         return res_final
 276
 277     def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
 278         res = {}
 279         res_final = {}
 280         child_ids = tuple(self.search(cr, uid, [('parent_id', 'child_of', ids)], context=context))
 281
 282         for i in child_ids:
 283             res[i] =  {}
 284             for n in [name]:
 285                 res[i][n] = 0.0
 286         if not child_ids:
 287             return res
 288
 289         if child_ids:
 290             cr.execute("""SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
 291                     FROM account_analytic_line \
 292                     JOIN account_analytic_journal \
 293                         ON account_analytic_line.journal_id = account_analytic_journal.id \
 294                     WHERE account_analytic_line.account_id IN %s \
 295                         AND amount<0 \
 296                     GROUP BY account_analytic_line.account_id""",(child_ids,))
 297             for account_id, sum in cr.fetchall():
 298                 res[account_id][name] = round(sum,2)
 299         data = self._compute_level_tree(cr, uid, ids, child_ids, res, [name], context)
 300         for i in data:
 301             res_final[i] = data[i][name]
 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 _hours_qtt_invoiced_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] = account.hours_quantity - account.hours_qtt_non_invoiced
 319             if res[account.id] < 0:
 320                 res[account.id] = 0.0
 321         for id in ids:
 322             res[id] = round(res.get(id, 0.0),2)
 323         return res
 324
 325     def _revenue_per_hour_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.hours_qtt_invoiced == 0:
 329                 res[account.id]=0.0
 330             else:
 331                 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
 332         for id in ids:
 333             res[id] = round(res.get(id, 0.0),2)
 334         return res
 335
 336     def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
 337         res = {}
 338         for account in self.browse(cr, uid, ids, context=context):
 339             if account.ca_invoiced == 0:
 340                 res[account.id]=0.0
 341             elif account.total_cost != 0.0:
 342                 res[account.id] = -(account.real_margin / account.total_cost) * 100
 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_ca_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.amount_max != 0:
 353                 res[account.id] = account.amount_max - account.ca_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     _columns ={
 377         'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced Amount',
 378             help="Total customer invoiced amount for this account.",
 379             digits_compute=dp.get_precision('Account')),
 380         'total_cost': fields.function(_total_cost_calc, method=True, type='float', string='Total Costs',
 381             help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
 382             digits_compute=dp.get_precision('Account')),
 383         'ca_to_invoice': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
 384             help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
 385             digits_compute=dp.get_precision('Account')),
 386         'ca_theorical': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='float', string='Theorical Revenue',
 387             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.",
 388             digits_compute=dp.get_precision('Account')),
 389         'hours_quantity': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='float', string='Hours Tot',
 390             help="Number of hours you spent on the analytic account (from timesheet). It computes on all journal of type 'general'."),
 391         'last_invoice_date': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='date', string='Last Invoice Date',
 392             help="Date of the last invoice created for this analytic account."),
 393         'last_worked_invoiced_date': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
 394             help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
 395         'last_worked_date': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
 396             help="Date of the latest work done on this account."),
 397         'hours_qtt_non_invoiced': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='float', string='Uninvoiced Hours',
 398             help="Number of hours (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
 399         'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, method=True, type='float', string='Invoiced Hours',
 400             help="Number of hours that can be invoiced plus those that already have been invoiced."),
 401         'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining Hours',
 402             help="Computed using the formula: Maximum Quantity - Hours Tot."),
 403         'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining Revenue',
 404             help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
 405             digits_compute=dp.get_precision('Account')),
 406         'revenue_per_hour': fields.function(_revenue_per_hour_calc, method=True, type='float', string='Revenue per Hours (real)',
 407             help="Computed using the formula: Invoiced Amount / Hours Tot.",
 408             digits_compute=dp.get_precision('Account')),
 409         'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real Margin',
 410             help="Computed using the formula: Invoiced Amount - Total Costs.",
 411             digits_compute=dp.get_precision('Account')),
 412         'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical Margin',
 413             help="Computed using the formula: Theorial Revenue - Total Costs",
 414             digits_compute=dp.get_precision('Account')),
 415         'real_margin_rate': fields.function(_real_margin_rate_calc, method=True, type='float', string='Real Margin Rate (%)',
 416             help="Computes using the formula: (Real Margin / Total Costs) * 100.",
 417             digits_compute=dp.get_precision('Account')),
 418         'month_ids': fields.function(_analysis_all, method=True, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
 419         'user_ids': fields.function(_analysis_all, method=True, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
 420     }
 421
 422 account_analytic_account()
 423
 424 class account_analytic_account_summary_user(osv.osv):
 425     _name = "account_analytic_analysis.summary.user"
 426     _description = "Hours Summary by User"
 427     _order='user'
 428     _auto = False
 429     _rec_name = 'user'
 430
 431     def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 432         res = {}
 433         account_obj = self.pool.get('account.analytic.account')
 434         cr.execute('SELECT MAX(id) FROM res_users')
 435         max_user = cr.fetchone()[0]
 436         account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
 437         user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
 438         parent_ids = tuple(account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)], context=context))
 439         if parent_ids:
 440             cr.execute('SELECT id, unit_amount ' \
 441                     'FROM account_analytic_analysis_summary_user ' \
 442                     'WHERE account_id IN %s ' \
 443                         'AND "user" IN %s',(parent_ids, tuple(user_ids),))
 444             for sum_id, unit_amount in cr.fetchall():
 445                 res[sum_id] = unit_amount
 446         for obj_id in ids:
 447             res.setdefault(obj_id, 0.0)
 448             for child_id in account_obj.search(cr, uid,
 449                     [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
 450                 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
 451                     res[obj_id] += res.get((child_id * max_user) + obj_id -((obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)) * max_user), 0.0)
 452         for id in ids:
 453             res[id] = round(res.get(id, 0.0), 2)
 454         return res
 455
 456     _columns = {
 457         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 458         'unit_amount': fields.function(_unit_amount, method=True, type='float',
 459             string='Total Time'),
 460         'user': fields.many2one('res.users', 'User'),
 461     }
 462
 463     def init(self, cr):
 464         tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
 465         cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
 466                 'SELECT ' \
 467                     '(u.account_id * u.max_user) + u."user" AS id, ' \
 468                     'u.account_id AS account_id, ' \
 469                     'u."user" AS "user", ' \
 470                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 471                 'FROM ' \
 472                     '(SELECT ' \
 473                         'a.id AS account_id, ' \
 474                         'u1.id AS "user", ' \
 475                         'MAX(u2.id) AS max_user ' \
 476                     'FROM ' \
 477                         'res_users AS u1, ' \
 478                         'res_users AS u2, ' \
 479                         'account_analytic_account AS a ' \
 480                     'GROUP BY u1.id, a.id ' \
 481                     ') AS u ' \
 482                 'LEFT JOIN ' \
 483                     '(SELECT ' \
 484                         'l.account_id AS account_id, ' \
 485                         'l.user_id AS "user", ' \
 486                         'SUM(l.unit_amount) AS unit_amount ' \
 487                     'FROM account_analytic_line AS l, ' \
 488                         'account_analytic_journal AS j ' \
 489                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 490                     'GROUP BY l.account_id, l.user_id ' \
 491                     ') AS l '
 492                     'ON (' \
 493                         'u.account_id = l.account_id ' \
 494                         'AND u."user" = l."user"' \
 495                     ') ' \
 496                 'GROUP BY u."user", u.account_id, u.max_user' \
 497                 ')')
 498
 499     def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
 500         if context is None:
 501             context = {}
 502         if not ids:
 503             return []
 504
 505         if fields is None:
 506             fields = self._columns.keys()
 507         res_trans_obj = self.pool.get('ir.translation')
 508
 509         # construct a clause for the rules:
 510         d1, d2, tables = self.pool.get('ir.rule').domain_get(cr, user, self._name, 'read', context=context)
 511
 512         # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
 513         fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
 514         res = []
 515         cr.execute('SELECT MAX(id) FROM res_users')
 516         max_user = cr.fetchone()[0]
 517         if fields_pre:
 518             fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
 519             for i in range(0, len(ids), cr.IN_MAX):
 520                 sub_ids = ids[i:i+cr.IN_MAX]
 521                 if d1:
 522                     cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
 523                             'AND account_id IN (%s) ' \
 524                             'AND "user" IN (%s) AND %s ORDER BY %s' % \
 525                             (','.join(fields_pre2 + ['id']), self._table,
 526                                 ','.join([str(x) for x in sub_ids]),
 527                                 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
 528                                 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
 529                                 self._order),d2)
 530                     if not cr.rowcount == len({}.fromkeys(sub_ids)):
 531                         raise except_orm(_('AccessError'),
 532                                 _('You try to bypass an access rule (Document type: %s).') % self._description)
 533                 else:
 534                     cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
 535                             'AND account_id IN (%s) ' \
 536                             'AND "user" IN (%s) ORDER BY %s' % \
 537                             (','.join(fields_pre2 + ['id']), self._table,
 538                                 ','.join([str(x) for x in sub_ids]),
 539                                 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
 540                                 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
 541                                 self._order))
 542                 res.extend(cr.dictfetchall())
 543         else:
 544             res = map(lambda x: {'id': x}, ids)
 545         for f in fields_pre:
 546             if self._columns[f].translate:
 547                 ids = map(lambda x: x['id'], res)
 548                 res_trans = res_trans_obj._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
 549                 for r in res:
 550                     r[f] = res_trans.get(r['id'], False) or r[f]
 551
 552         for table in self._inherits:
 553             col = self._inherits[table]
 554             cols = intersect(self._inherit_fields.keys(), fields)
 555             if not cols:
 556                 continue
 557             res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
 558
 559             res3 = {}
 560             for r in res2:
 561                 res3[r['id']] = r
 562                 del r['id']
 563
 564             for record in res:
 565                 record.update(res3[record[col]])
 566                 if col not in fields:
 567                     del record[col]
 568
 569         # all fields which need to be post-processed by a simple function (symbol_get)
 570         fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
 571         if fields_post:
 572             # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
 573             # to get the _symbol_get in each occurence
 574             for r in res:
 575                 for f in fields_post:
 576                     r[f] = self.columns[f]._symbol_get(r[f])
 577         ids = map(lambda x: x['id'], res)
 578
 579         # all non inherited fields for which the attribute whose name is in load is False
 580         fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
 581         for f in fields_post:
 582             # get the value of that field for all records/ids
 583             res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
 584             for record in res:
 585                 record[f] = res2[record['id']]
 586
 587         return res
 588
 589 account_analytic_account_summary_user()
 590
 591 class account_analytic_account_summary_month(osv.osv):
 592     _name = "account_analytic_analysis.summary.month"
 593     _description = "Hours summary by month"
 594     _auto = False
 595     _rec_name = 'month'
 596
 597     def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 598         res = {}
 599         account_obj = self.pool.get('account.analytic.account')
 600         account_ids = [int(str(int(x))[:-6]) for x in ids]
 601         month_ids = [int(str(int(x))[-6:]) for x in ids]
 602         parent_ids = tuple(account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)], context=context))
 603         if parent_ids:
 604             cr.execute('SELECT id, unit_amount ' \
 605                     'FROM account_analytic_analysis_summary_month ' \
 606                     'WHERE account_id IN %s ' \
 607                         'AND month_id IN %s ',(parent_ids, tuple(month_ids),))
 608             for sum_id, unit_amount in cr.fetchall():
 609                 res[sum_id] = unit_amount
 610         for obj_id in ids:
 611             res.setdefault(obj_id, 0.0)
 612             for child_id in account_obj.search(cr, uid,
 613                     [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
 614                 if child_id != int(str(int(obj_id))[:-6]):
 615                     res[obj_id] += res.get(int(child_id * 1000000 + int(str(int(obj_id))[-6:])), 0.0)
 616         for id in ids:
 617             res[id] = round(res.get(id, 0.0), 2)
 618         return res
 619
 620     _columns = {
 621         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 622         'unit_amount': fields.function(_unit_amount, method=True, type='float', string='Total Time'),
 623         'month': fields.char('Month', size=32, readonly=True),
 624     }
 625
 626     def init(self, cr):
 627         tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
 628         cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
 629                 'SELECT ' \
 630                     '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id  * 1000000))::integer AS id, ' \
 631                     'd.account_id AS account_id, ' \
 632                     'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
 633                     'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
 634                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 635                 'FROM ' \
 636                     '(SELECT ' \
 637                         'd2.account_id, ' \
 638                         'd2.month ' \
 639                     'FROM ' \
 640                         '(SELECT ' \
 641                             'a.id AS account_id, ' \
 642                             'l.month AS month ' \
 643                         'FROM ' \
 644                             '(SELECT ' \
 645                                 'DATE_TRUNC(\'month\', l.date) AS month ' \
 646                             'FROM account_analytic_line AS l, ' \
 647                                 'account_analytic_journal AS j ' \
 648                             'WHERE j.type = \'general\' ' \
 649                             'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
 650                             ') AS l, ' \
 651                             'account_analytic_account AS a ' \
 652                         'GROUP BY l.month, a.id ' \
 653                         ') AS d2 ' \
 654                     'GROUP BY d2.account_id, d2.month ' \
 655                     ') AS d ' \
 656                 'LEFT JOIN ' \
 657                     '(SELECT ' \
 658                         'l.account_id AS account_id, ' \
 659                         'DATE_TRUNC(\'month\', l.date) AS month, ' \
 660                         'SUM(l.unit_amount) AS unit_amount ' \
 661                     'FROM account_analytic_line AS l, ' \
 662                         'account_analytic_journal AS j ' \
 663                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 664                     'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
 665                     ') AS l '
 666                     'ON (' \
 667                         'd.account_id = l.account_id ' \
 668                         'AND d.month = l.month' \
 669                     ') ' \
 670                 'GROUP BY d.month, d.account_id ' \
 671                 ')')
 672
 673     def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
 674         if context is None:
 675             context = {}
 676         if not ids:
 677             return []
 678
 679         if fields is None:
 680             fields = self._columns.keys()
 681         res_trans_obj = self.pool.get('ir.translation')
 682         # construct a clause for the rules:
 683         d1, d2, tables= self.pool.get('ir.rule').domain_get(cr, user, self._name)
 684
 685         # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
 686         fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
 687         res = []
 688         if fields_pre:
 689             fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
 690             for i in range(0, len(ids), cr.IN_MAX):
 691                 sub_ids = ids[i:i+cr.IN_MAX]
 692                 if d1:
 693                     cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
 694                             'AND account_id IN (%s) ' \
 695                             'AND month_id IN (%s) AND %s ORDER BY %s' % \
 696                             (','.join(fields_pre2 + ['id']), self._table,
 697                                 ','.join([str(x) for x in sub_ids]),
 698                                 ','.join([str(x)[:-6] for x in sub_ids]),
 699                                 ','.join([str(x)[-6:] for x in sub_ids]), d1,
 700                                 self._order),d2)
 701                     if not cr.rowcount == len({}.fromkeys(sub_ids)):
 702                         raise except_orm(_('AccessError'),
 703                                 _('You try to bypass an access rule (Document type: %s).') % self._description)
 704                 else:
 705                     cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
 706                             'AND account_id IN (%s) ' \
 707                             'AND month_id IN (%s) ORDER BY %s' % \
 708                             (','.join(fields_pre2 + ['id']), self._table,
 709                                 ','.join([str(x) for x in sub_ids]),
 710                                 ','.join([str(x)[:-6] for x in sub_ids]),
 711                                 ','.join([str(x)[-6:] for x in sub_ids]),
 712                                 self._order))
 713                 res.extend(cr.dictfetchall())
 714         else:
 715             res = map(lambda x: {'id': x}, ids)
 716
 717         for f in fields_pre:
 718             if self._columns[f].translate:
 719                 ids = map(lambda x: x['id'], res)
 720                 res_trans = res_trans_obj._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
 721                 for r in res:
 722                     r[f] = res_trans.get(r['id'], False) or r[f]
 723
 724         for table in self._inherits:
 725             col = self._inherits[table]
 726             cols = intersect(self._inherit_fields.keys(), fields)
 727             if not cols:
 728                 continue
 729             res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
 730
 731             res3 = {}
 732             for r in res2:
 733                 res3[r['id']] = r
 734                 del r['id']
 735
 736             for record in res:
 737                 record.update(res3[record[col]])
 738                 if col not in fields:
 739                     del record[col]
 740
 741         # all fields which need to be post-processed by a simple function (symbol_get)
 742         fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
 743         if fields_post:
 744             # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
 745             # to get the _symbol_get in each occurence
 746             for r in res:
 747                 for f in fields_post:
 748                     r[f] = self.columns[f]._symbol_get(r[f])
 749         ids = map(lambda x: x['id'], res)
 750
 751         # all non inherited fields for which the attribute whose name is in load is False
 752         fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
 753         for f in fields_post:
 754             # get the value of that field for all records/ids
 755             res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
 756             for record in res:
 757                 record[f] = res2[record['id']]
 758         return res
 759
 760 account_analytic_account_summary_month()
 761
 762 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: