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