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