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