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] = False
  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][f] < res.get(child.id, {}).get(f):
  78                             res[account.id][f] = res.get(child.id, {}).get(f, False)
  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] = False
 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, False)
 142             elif f == 'last_worked_date':
 143                 for id in ids:
 144                     res[id][f] = False
 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, False)
 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         for record in self.browse(cr, uid, ids, context=context):
 380             if record.quantity_max > 0.0:
 381                 result[record.id] = int(record.quantity >= record.quantity_max)
 382             else:
 383                 result[record.id] = 0
 384         return result
 385
 386     def _get_analytic_account(self, cr, uid, ids, context=None):
 387         result = set()
 388         for line in self.pool.get('account.analytic.line').browse(cr, uid, ids, context=context):
 389             result.add(line.account_id.id)
 390         return list(result)
 391
 392     _columns = {
 393         'is_overdue_quantity' : fields.function(_is_overdue_quantity, method=True, type='boolean', string='Overdue Quantity',
 394                                                 store={
 395                                                     'account.analytic.line' : (_get_analytic_account, None, 20),
 396                                                 }),
 397         'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
 398             help="Total customer invoiced amount for this account.",
 399             digits_compute=dp.get_precision('Account')),
 400         'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
 401             help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
 402             digits_compute=dp.get_precision('Account')),
 403         'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
 404             help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
 405             digits_compute=dp.get_precision('Account')),
 406         'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
 407             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.",
 408             digits_compute=dp.get_precision('Account')),
 409         'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Time',
 410             help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
 411         'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
 412             help="If invoice from the costs, this is the date of the latest invoiced."),
 413         'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
 414             help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
 415         'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
 416             help="Date of the latest work done on this account."),
 417         'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
 418             help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
 419         'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
 420             help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
 421         'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
 422             help="Computed using the formula: Maximum Time - Total Time"),
 423         'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
 424             help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
 425             digits_compute=dp.get_precision('Account')),
 426         'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
 427             help="Computed using the formula: Invoiced Amount / Total Time",
 428             digits_compute=dp.get_precision('Account')),
 429         'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
 430             help="Computed using the formula: Invoiced Amount - Total Costs.",
 431             digits_compute=dp.get_precision('Account')),
 432         'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
 433             help="Computed using the formula: Theorial Revenue - Total Costs",
 434             digits_compute=dp.get_precision('Account')),
 435         'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
 436             help="Computes using the formula: (Real Margin / Total Costs) * 100.",
 437             digits_compute=dp.get_precision('Account')),
 438         'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
 439         'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
 440     }
 441
 442 account_analytic_account()
 443
 444 class account_analytic_account_summary_user(osv.osv):
 445     _name = "account_analytic_analysis.summary.user"
 446     _description = "Hours Summary by User"
 447     _order='user'
 448     _auto = False
 449     _rec_name = 'user'
 450
 451     def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 452         res = {}
 453         account_obj = self.pool.get('account.analytic.account')
 454         cr.execute('SELECT MAX(id) FROM res_users')
 455         max_user = cr.fetchone()[0]
 456         account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
 457         user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
 458         parent_ids = tuple(account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)], context=context))
 459         if parent_ids:
 460             cr.execute('SELECT id, unit_amount ' \
 461                     'FROM account_analytic_analysis_summary_user ' \
 462                     'WHERE account_id IN %s ' \
 463                         'AND "user" IN %s',(parent_ids, tuple(user_ids),))
 464             for sum_id, unit_amount in cr.fetchall():
 465                 res[sum_id] = unit_amount
 466         for obj_id in ids:
 467             res.setdefault(obj_id, 0.0)
 468             for child_id in account_obj.search(cr, uid,
 469                     [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
 470                 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
 471                     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)
 472         for id in ids:
 473             res[id] = round(res.get(id, 0.0), 2)
 474         return res
 475
 476     _columns = {
 477         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 478         'unit_amount': fields.function(_unit_amount, type='float',
 479             string='Total Time'),
 480         'user': fields.many2one('res.users', 'User'),
 481     }
 482
 483     def init(self, cr):
 484         tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
 485         cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
 486                 'SELECT ' \
 487                     '(u.account_id * u.max_user) + u."user" AS id, ' \
 488                     'u.account_id AS account_id, ' \
 489                     'u."user" AS "user", ' \
 490                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 491                 'FROM ' \
 492                     '(SELECT ' \
 493                         'a.id AS account_id, ' \
 494                         'u1.id AS "user", ' \
 495                         'MAX(u2.id) AS max_user ' \
 496                     'FROM ' \
 497                         'res_users AS u1, ' \
 498                         'res_users AS u2, ' \
 499                         'account_analytic_account AS a ' \
 500                     'GROUP BY u1.id, a.id ' \
 501                     ') AS u ' \
 502                 'LEFT JOIN ' \
 503                     '(SELECT ' \
 504                         'l.account_id AS account_id, ' \
 505                         'l.user_id AS "user", ' \
 506                         'SUM(l.unit_amount) AS unit_amount ' \
 507                     'FROM account_analytic_line AS l, ' \
 508                         'account_analytic_journal AS j ' \
 509                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 510                     'GROUP BY l.account_id, l.user_id ' \
 511                     ') AS l '
 512                     'ON (' \
 513                         'u.account_id = l.account_id ' \
 514                         'AND u."user" = l."user"' \
 515                     ') ' \
 516                 'GROUP BY u."user", u.account_id, u.max_user' \
 517                 ')')
 518
 519     def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
 520         if context is None:
 521             context = {}
 522         if not ids:
 523             return []
 524
 525         if fields is None:
 526             fields = self._columns.keys()
 527         res_trans_obj = self.pool.get('ir.translation')
 528
 529         # construct a clause for the rules:
 530         d1, d2, tables = self.pool.get('ir.rule').domain_get(cr, user, self._name, 'read', context=context)
 531
 532         # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
 533         fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
 534         res = []
 535         cr.execute('SELECT MAX(id) FROM res_users')
 536         max_user = cr.fetchone()[0]
 537         if fields_pre:
 538             fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
 539             for i in range(0, len(ids), cr.IN_MAX):
 540                 sub_ids = ids[i:i+cr.IN_MAX]
 541                 if d1:
 542                     cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
 543                             'AND account_id IN (%s) ' \
 544                             'AND "user" IN (%s) AND %s ORDER BY %s' % \
 545                             (','.join(fields_pre2 + ['id']), self._table,
 546                                 ','.join([str(x) for x in sub_ids]),
 547                                 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
 548                                 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
 549                                 self._order),d2)
 550                     if not cr.rowcount == len({}.fromkeys(sub_ids)):
 551                         raise except_orm(_('AccessError'),
 552                                 _('You try to bypass an access rule (Document type: %s).') % self._description)
 553                 else:
 554                     cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
 555                             'AND account_id IN (%s) ' \
 556                             'AND "user" IN (%s) ORDER BY %s' % \
 557                             (','.join(fields_pre2 + ['id']), self._table,
 558                                 ','.join([str(x) for x in sub_ids]),
 559                                 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
 560                                 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
 561                                 self._order))
 562                 res.extend(cr.dictfetchall())
 563         else:
 564             res = map(lambda x: {'id': x}, ids)
 565         for f in fields_pre:
 566             if self._columns[f].translate:
 567                 ids = map(lambda x: x['id'], res)
 568                 res_trans = res_trans_obj._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
 569                 for r in res:
 570                     r[f] = res_trans.get(r['id'], False) or r[f]
 571
 572         for table in self._inherits:
 573             col = self._inherits[table]
 574             cols = intersect(self._inherit_fields.keys(), fields)
 575             if not cols:
 576                 continue
 577             res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
 578
 579             res3 = {}
 580             for r in res2:
 581                 res3[r['id']] = r
 582                 del r['id']
 583
 584             for record in res:
 585                 record.update(res3[record[col]])
 586                 if col not in fields:
 587                     del record[col]
 588
 589         # all fields which need to be post-processed by a simple function (symbol_get)
 590         fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
 591         if fields_post:
 592             # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
 593             # to get the _symbol_get in each occurence
 594             for r in res:
 595                 for f in fields_post:
 596                     r[f] = self.columns[f]._symbol_get(r[f])
 597         ids = map(lambda x: x['id'], res)
 598
 599         # all non inherited fields for which the attribute whose name is in load is False
 600         fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
 601         for f in fields_post:
 602             # get the value of that field for all records/ids
 603             res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
 604             for record in res:
 605                 record[f] = res2[record['id']]
 606
 607         return res
 608
 609 account_analytic_account_summary_user()
 610
 611 class account_analytic_account_summary_month(osv.osv):
 612     _name = "account_analytic_analysis.summary.month"
 613     _description = "Hours summary by month"
 614     _auto = False
 615     _rec_name = 'month'
 616
 617     def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 618         res = {}
 619         account_obj = self.pool.get('account.analytic.account')
 620         account_ids = [int(str(int(x))[:-6]) for x in ids]
 621         month_ids = [int(str(int(x))[-6:]) for x in ids]
 622         parent_ids = tuple(account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)], context=context))
 623         if parent_ids:
 624             cr.execute('SELECT id, unit_amount ' \
 625                     'FROM account_analytic_analysis_summary_month ' \
 626                     'WHERE account_id IN %s ' \
 627                         'AND month_id IN %s ',(parent_ids, tuple(month_ids),))
 628             for sum_id, unit_amount in cr.fetchall():
 629                 res[sum_id] = unit_amount
 630         for obj_id in ids:
 631             res.setdefault(obj_id, 0.0)
 632             for child_id in account_obj.search(cr, uid,
 633                     [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
 634                 if child_id != int(str(int(obj_id))[:-6]):
 635                     res[obj_id] += res.get(int(child_id * 1000000 + int(str(int(obj_id))[-6:])), 0.0)
 636         for id in ids:
 637             res[id] = round(res.get(id, 0.0), 2)
 638         return res
 639
 640     _columns = {
 641         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 642         'unit_amount': fields.function(_unit_amount, type='float', string='Total Time'),
 643         'month': fields.char('Month', size=32, readonly=True),
 644     }
 645
 646     def init(self, cr):
 647         tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
 648         cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
 649                 'SELECT ' \
 650                     '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id  * 1000000::bigint))::bigint AS id, ' \
 651                     'd.account_id AS account_id, ' \
 652                     'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
 653                     'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
 654                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 655                 'FROM ' \
 656                     '(SELECT ' \
 657                         'd2.account_id, ' \
 658                         'd2.month ' \
 659                     'FROM ' \
 660                         '(SELECT ' \
 661                             'a.id AS account_id, ' \
 662                             'l.month AS month ' \
 663                         'FROM ' \
 664                             '(SELECT ' \
 665                                 'DATE_TRUNC(\'month\', l.date) AS month ' \
 666                             'FROM account_analytic_line AS l, ' \
 667                                 'account_analytic_journal AS j ' \
 668                             'WHERE j.type = \'general\' ' \
 669                             'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
 670                             ') AS l, ' \
 671                             'account_analytic_account AS a ' \
 672                         'GROUP BY l.month, a.id ' \
 673                         ') AS d2 ' \
 674                     'GROUP BY d2.account_id, d2.month ' \
 675                     ') AS d ' \
 676                 'LEFT JOIN ' \
 677                     '(SELECT ' \
 678                         'l.account_id AS account_id, ' \
 679                         'DATE_TRUNC(\'month\', l.date) AS month, ' \
 680                         'SUM(l.unit_amount) AS unit_amount ' \
 681                     'FROM account_analytic_line AS l, ' \
 682                         'account_analytic_journal AS j ' \
 683                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 684                     'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
 685                     ') AS l '
 686                     'ON (' \
 687                         'd.account_id = l.account_id ' \
 688                         'AND d.month = l.month' \
 689                     ') ' \
 690                 'GROUP BY d.month, d.account_id ' \
 691                 ')')
 692
 693     def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
 694         if context is None:
 695             context = {}
 696         if not ids:
 697             return []
 698
 699         if fields is None:
 700             fields = self._columns.keys()
 701         res_trans_obj = self.pool.get('ir.translation')
 702         # construct a clause for the rules:
 703         d1, d2, tables= self.pool.get('ir.rule').domain_get(cr, user, self._name)
 704
 705         # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
 706         fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
 707         res = []
 708         if fields_pre:
 709             fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
 710             for i in range(0, len(ids), cr.IN_MAX):
 711                 sub_ids = ids[i:i+cr.IN_MAX]
 712                 if d1:
 713                     cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
 714                             'AND account_id IN (%s) ' \
 715                             'AND month_id IN (%s) AND %s ORDER BY %s' % \
 716                             (','.join(fields_pre2 + ['id']), self._table,
 717                                 ','.join([str(x) for x in sub_ids]),
 718                                 ','.join([str(x)[:-6] for x in sub_ids]),
 719                                 ','.join([str(x)[-6:] for x in sub_ids]), d1,
 720                                 self._order),d2)
 721                     if not cr.rowcount == len({}.fromkeys(sub_ids)):
 722                         raise except_orm(_('AccessError'),
 723                                 _('You try to bypass an access rule (Document type: %s).') % self._description)
 724                 else:
 725                     cr.execute('SELECT %s FROM \"%s\" WHERE id IN (%s) ' \
 726                             'AND account_id IN (%s) ' \
 727                             'AND month_id IN (%s) ORDER BY %s' % \
 728                             (','.join(fields_pre2 + ['id']), self._table,
 729                                 ','.join([str(x) for x in sub_ids]),
 730                                 ','.join([str(x)[:-6] for x in sub_ids]),
 731                                 ','.join([str(x)[-6:] for x in sub_ids]),
 732                                 self._order))
 733                 res.extend(cr.dictfetchall())
 734         else:
 735             res = map(lambda x: {'id': x}, ids)
 736
 737         for f in fields_pre:
 738             if self._columns[f].translate:
 739                 ids = map(lambda x: x['id'], res)
 740                 res_trans = res_trans_obj._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
 741                 for r in res:
 742                     r[f] = res_trans.get(r['id'], False) or r[f]
 743
 744         for table in self._inherits:
 745             col = self._inherits[table]
 746             cols = intersect(self._inherit_fields.keys(), fields)
 747             if not cols:
 748                 continue
 749             res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
 750
 751             res3 = {}
 752             for r in res2:
 753                 res3[r['id']] = r
 754                 del r['id']
 755
 756             for record in res:
 757                 record.update(res3[record[col]])
 758                 if col not in fields:
 759                     del record[col]
 760
 761         # all fields which need to be post-processed by a simple function (symbol_get)
 762         fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
 763         if fields_post:
 764             # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
 765             # to get the _symbol_get in each occurence
 766             for r in res:
 767                 for f in fields_post:
 768                     r[f] = self.columns[f]._symbol_get(r[f])
 769         ids = map(lambda x: x['id'], res)
 770
 771         # all non inherited fields for which the attribute whose name is in load is False
 772         fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
 773         for f in fields_post:
 774             # get the value of that field for all records/ids
 775             res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
 776             for record in res:
 777                 record[f] = res2[record['id']]
 778         return res
 779
 780 account_analytic_account_summary_month()
 781
 782 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: