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