addons/account_analytic_analysis/account_analytic_analysis.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 # Copyright (c) 2004 TINY SPRL. (http://tiny.be) All Rights Reserved.
   5 #                    Fabien Pinckaers <fp@tiny.Be>
   6 #
   7 # WARNING: This program as such is intended to be used by professional
   8 # programmers who take the whole responsability of assessing all potential
   9 # consequences resulting from its eventual inadequacies and bugs
  10 # End users who are looking for a ready-to-use solution with commercial
  11 # garantees and support are strongly adviced to contract a Free Software
  12 # Service Company
  13 #
  14 # This program is Free Software; you can redistribute it and/or
  15 # modify it under the terms of the GNU General Public License
  16 # as published by the Free Software Foundation; either version 2
  17 # of the License, or (at your option) any later version.
  18 #
  19 # This program is distributed in the hope that it will be useful,
  20 # but WITHOUT ANY WARRANTY; without even the implied warranty of
  21 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  22 # GNU General Public License for more details.
  23 #
  24 # You should have received a copy of the GNU General Public License
  25 # along with this program; if not, write to the Free Software
  26 # Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
  27 #
  28 ##############################################################################
  29 import operator
  30 from osv import osv, fields
  31 from osv.orm import ID_MAX
  32
  33
  34 class account_analytic_account(osv.osv):
  35         _name = "account.analytic.account"
  36         _inherit = "account.analytic.account"
  37
  38         def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
  39                 res = {}
  40                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
  41                 if ids2:
  42                         acc_set = ",".join(map(str, ids2))
  43                         cr.execute("select account_analytic_line.account_id, sum(amount) \
  44                                         from account_analytic_line \
  45                                         join account_analytic_journal \
  46                                                 on account_analytic_line.journal_id = account_analytic_journal.id  \
  47                                         where account_analytic_line.account_id IN (%s) \
  48                                                 and account_analytic_journal.type = 'sale' \
  49                                         group by account_analytic_line.account_id" % acc_set)
  50                         for account_id, sum in cr.fetchall():
  51                                 res[account_id] = round(sum,2)
  52                 for obj_id in ids:
  53                         res.setdefault(obj_id, 0.0)
  54                         for child_id in self.search(cr, uid,
  55                                         [('parent_id', 'child_of', [obj_id])]):
  56                                 if child_id != obj_id:
  57                                         res[obj_id] += res.get(child_id, 0.0)
  58                 for id in ids:
  59                         res[id] = round(res.get(id, 0.0),2)
  60                 return res
  61
  62         def _ca_to_invoice_calc(self, cr, uid, ids, name, arg, context={}):
  63                 res = {}
  64                 res2 = {}
  65                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
  66                 if ids2:
  67                         # Amount uninvoiced hours to invoice at sale price
  68                         acc_set = ",".join(map(str, ids2))
  69                         cr.execute("""SELECT account_analytic_account.id, \
  70                                                 sum (product_template.list_price * \
  71                                                         account_analytic_line.unit_amount * \
  72                                                         ((100-hr_timesheet_invoice_factor.factor)/100)) \
  73                                                         AS ca_to_invoice \
  74                                         FROM product_template \
  75                                         join product_product \
  76                                                 on product_template.id = product_product.product_tmpl_id \
  77                                         JOIN account_analytic_line \
  78                                                 on account_analytic_line.product_id = product_product.id \
  79                                         JOIN account_analytic_journal \
  80                                                 on account_analytic_line.journal_id = account_analytic_journal.id \
  81                                         JOIN account_analytic_account \
  82                                                 on account_analytic_account.id = account_analytic_line.account_id \
  83                                         JOIN hr_timesheet_invoice_factor \
  84                                                 on hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
  85                                         WHERE account_analytic_account.id IN (%s) \
  86                                                 AND account_analytic_journal.type='general' \
  87                                                 AND account_analytic_line.invoice_id is null \
  88                                                 AND account_analytic_line.to_invoice IS NOT NULL \
  89                                         GROUP BY account_analytic_account.id;"""%acc_set)
  90                         for account_id, sum in cr.fetchall():
  91                                 res[account_id] = round(sum,2)
  92
  93                         # Expense amount and purchase invoice
  94                         acc_set = ",".join(map(str, ids2))
  95                         cr.execute ("select account_analytic_line.account_id, sum(amount) \
  96                                         from account_analytic_line \
  97                                         join account_analytic_journal \
  98                                                 on account_analytic_line.journal_id = account_analytic_journal.id \
  99                                         where account_analytic_line.account_id IN (%s) \
 100                                                 and account_analytic_journal.type = 'purchase' \
 101                                         GROUP BY account_analytic_line.account_id;"%acc_set)
 102                         for account_id, sum in cr.fetchall():
 103                                 res2[account_id] = round(sum,2)
 104                 for obj_id in ids:
 105                         res.setdefault(obj_id, 0.0)
 106                         res2.setdefault(obj_id, 0.0)
 107                         for child_id in self.search(cr, uid,
 108                                         [('parent_id', 'child_of', [obj_id])]):
 109                                 if child_id != obj_id:
 110                                         res[obj_id] += res.get(child_id, 0.0)
 111                                         res2[obj_id] += res2.get(child_id, 0.0)
 112                 # sum both result on account_id
 113                 for id in ids:
 114                         res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
 115                 return res
 116
 117         def _hours_qtt_non_invoiced_calc (self, cr, uid, ids, name, arg, context={}):
 118                 res = {}
 119                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 120                 if ids2:
 121                         acc_set = ",".join(map(str, ids2))
 122                         cr.execute("select account_analytic_line.account_id, sum(unit_amount) \
 123                                         from account_analytic_line \
 124                                         join account_analytic_journal \
 125                                                 on account_analytic_line.journal_id = account_analytic_journal.id \
 126                                         where account_analytic_line.account_id IN (%s) \
 127                                                 and account_analytic_journal.type='general' \
 128                                                 and invoice_id is null \
 129                                                 AND to_invoice IS NOT NULL \
 130                                         GROUP BY account_analytic_line.account_id;"%acc_set)
 131                         for account_id, sum in cr.fetchall():
 132                                 res[account_id] = round(sum,2)
 133                 for obj_id in ids:
 134                         res.setdefault(obj_id, 0.0)
 135                         for child_id in self.search(cr, uid,
 136                                         [('parent_id', 'child_of', [obj_id])]):
 137                                 if child_id != obj_id:
 138                                         res[obj_id] += res.get(child_id, 0.0)
 139                 for id in ids:
 140                         res[id] = round(res.get(id, 0.0),2)
 141                 return res
 142
 143         def _hours_quantity_calc(self, cr, uid, ids, name, arg, context={}):
 144                 res = {}
 145                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 146                 if ids2:
 147                         acc_set = ",".join(map(str, ids2))
 148                         cr.execute("select account_analytic_line.account_id,sum(unit_amount) \
 149                                         from account_analytic_line \
 150                                         join account_analytic_journal \
 151                                                 on account_analytic_line.journal_id = account_analytic_journal.id \
 152                                         where account_analytic_line.account_id IN (%s) \
 153                                                 and account_analytic_journal.type='general' \
 154                                         GROUP BY account_analytic_line.account_id"%acc_set)
 155                         for account_id, sum in cr.fetchall():
 156                                 res[account_id] = round(sum,2)
 157                 for obj_id in ids:
 158                         res.setdefault(obj_id, 0.0)
 159                         for child_id in self.search(cr, uid,
 160                                         [('parent_id', 'child_of', [obj_id])]):
 161                                 if child_id != obj_id:
 162                                         res[obj_id] += res.get(child_id, 0.0)
 163                 for id in ids:
 164                         res[id] = round(res.get(id, 0.0),2)
 165                 return res
 166
 167         def _total_cost_calc(self, cr, uid, ids, name, arg, context={}):
 168                 res = {}
 169                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 170                 if ids2:
 171                         acc_set = ",".join(map(str, ids2))
 172                         cr.execute("""select account_analytic_line.account_id,sum(amount) \
 173                                         from account_analytic_line \
 174                                         join account_analytic_journal \
 175                                                 on account_analytic_line.journal_id = account_analytic_journal.id \
 176                                         where account_analytic_line.account_id IN (%s) \
 177                                                 and amount<0 \
 178                                         GROUP BY account_analytic_line.account_id"""%acc_set)
 179                         for account_id, sum in cr.fetchall():
 180                                 res[account_id] = round(sum,2)
 181                 for obj_id in ids:
 182                         res.setdefault(obj_id, 0.0)
 183                         for child_id in self.search(cr, uid,
 184                                         [('parent_id', 'child_of', [obj_id])]):
 185                                 if child_id != obj_id:
 186                                         res[obj_id] += res.get(child_id, 0.0)
 187                 for id in ids:
 188                         res[id] = round(res.get(id, 0.0),2)
 189                 return res
 190
 191         def _ca_theorical_calc(self, cr, uid, ids, name, arg, context={}):
 192                 res = {}
 193                 res2 = {}
 194                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 195                 if ids2:
 196                         acc_set = ",".join(map(str, ids2))
 197                         # First part with expense and purchase
 198                         cr.execute("""select account_analytic_line.account_id,sum(amount) \
 199                                         from account_analytic_line \
 200                                         join account_analytic_journal \
 201                                                 on account_analytic_line.journal_id = account_analytic_journal.id \
 202                                         where account_analytic_line.account_id IN (%s) \
 203                                                 and account_analytic_journal.type = 'purchase' \
 204                                         GROUP BY account_analytic_line.account_id"""%acc_set)
 205                         for account_id, sum in cr.fetchall():
 206                                 res[account_id] = round(sum,2)
 207
 208                         # Second part with timesheet (with invoice factor)
 209                         acc_set = ",".join(map(str, ids2))
 210                         cr.execute("""select account_analytic_line.account_id as account_id, \
 211                                                 sum((account_analytic_line.unit_amount * pt.list_price) \
 212                                                         - (account_analytic_line.unit_amount * pt.list_price \
 213                                                                 * hr.factor)) as somme
 214                                         from account_analytic_line \
 215                                         join account_analytic_journal \
 216                                                 on account_analytic_line.journal_id = account_analytic_journal.id \
 217                                         join product_product pp \
 218                                                 on (account_analytic_line.product_id = pp.id) \
 219                                         join product_template pt \
 220                                                 on (pp.product_tmpl_id = pt.id) \
 221                                         join account_analytic_account a \
 222                                                 on (a.id=account_analytic_line.account_id) \
 223                                         join hr_timesheet_invoice_factor hr \
 224                                                 on (hr.id=a.to_invoice) \
 225                                 where account_analytic_line.account_id IN (%s) \
 226                                         and account_analytic_journal.type = 'general' \
 227                                         and a.to_invoice IS NOT NULL \
 228                                 GROUP BY account_analytic_line.account_id"""%acc_set)
 229                         for account_id, sum in cr.fetchall():
 230                                 res2[account_id] = round(sum,2)
 231
 232                 for obj_id in ids:
 233                         res.setdefault(obj_id, 0.0)
 234                         res2.setdefault(obj_id, 0.0)
 235                         for child_id in self.search(cr, uid,
 236                                         [('parent_id', 'child_of', [obj_id])]):
 237                                 if child_id != obj_id:
 238                                         res[obj_id] += res.get(child_id, 0.0)
 239                                         res[obj_id] += res2.get(child_id, 0.0)
 240
 241                 # sum both result on account_id
 242                 for id in ids:
 243                         res[id] = round(res.get(id, 0.0),2) + round(res2.get(id, 0.0),2)
 244                 return res
 245
 246         def _last_worked_date_calc (self, cr, uid, ids, name, arg, context={}):
 247                 res = {}
 248                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 249                 if ids2:
 250                         acc_set = ",".join(map(str, ids2))
 251                         cr.execute("select account_analytic_line.account_id, max(date) \
 252                                         from account_analytic_line \
 253                                         where account_id IN (%s) \
 254                                                 and invoice_id is null \
 255                                         GROUP BY account_analytic_line.account_id" % acc_set)
 256                         for account_id, sum in cr.fetchall():
 257                                 res[account_id] = sum
 258                 for obj_id in ids:
 259                         res.setdefault(obj_id, '')
 260                         for child_id in self.search(cr, uid,
 261                                         [('parent_id', 'child_of', [obj_id])]):
 262                                 if res[obj_id] < res.get(child_id, ''):
 263                                         res[obj_id] = res.get(child_id, '')
 264                 for id in ids:
 265                         res[id] = res.get(id, '')
 266                 return res
 267
 268         def _last_invoice_date_calc (self, cr, uid, ids, name, arg, context={}):
 269                 res = {}
 270                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 271                 if ids2:
 272                         acc_set = ",".join(map(str, ids2))
 273                         cr.execute ("select account_analytic_line.account_id, \
 274                                                 date(max(account_invoice.date_invoice)) \
 275                                         from account_analytic_line \
 276                                         join account_invoice \
 277                                                 on account_analytic_line.invoice_id = account_invoice.id \
 278                                         where account_analytic_line.account_id IN (%s) \
 279                                                 and account_analytic_line.invoice_id is not null \
 280                                         GROUP BY account_analytic_line.account_id"%acc_set)
 281                         for account_id, sum in cr.fetchall():
 282                                 res[account_id] = sum
 283                 for obj_id in ids:
 284                         res.setdefault(obj_id, '')
 285                         for child_id in self.search(cr, uid,
 286                                         [('parent_id', 'child_of', [obj_id])]):
 287                                 if res[obj_id] < res.get(child_id, ''):
 288                                         res[obj_id] = res.get(child_id, '')
 289                 for id in ids:
 290                         res[id] = res.get(id, '')
 291                 return res
 292
 293         def _last_worked_invoiced_date_calc (self, cr, uid, ids, name, arg, context={}):
 294                 res = {}
 295                 ids2 = self.search(cr, uid, [('parent_id', 'child_of', ids)])
 296                 if ids2:
 297                         acc_set = ",".join(map(str, ids2))
 298                         cr.execute("select account_analytic_line.account_id, max(date) \
 299                                         from account_analytic_line \
 300                                         where account_id IN (%s) \
 301                                                 and invoice_id is not null \
 302                                         GROUP BY account_analytic_line.account_id;"%acc_set)
 303                         for account_id, sum in cr.fetchall():
 304                                 res[account_id] = sum
 305                 for obj_id in ids:
 306                         res.setdefault(obj_id, '')
 307                         for child_id in self.search(cr, uid,
 308                                         [('parent_id', 'child_of', [obj_id])]):
 309                                 if res[obj_id] < res.get(child_id, ''):
 310                                         res[obj_id] = res.get(child_id, '')
 311                 for id in ids:
 312                         res[id] = res.get(id, '')
 313                 return res
 314
 315         def _remaining_hours_calc(self, cr, uid, ids, name, arg, context={}):
 316                 res = {}
 317                 for account in self.browse(cr, uid, ids):
 318                         if account.quantity_max <> 0:
 319                                 res[account.id] = account.quantity_max - account.hours_quantity
 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 _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context={}):
 327                 res = {}
 328                 for account in self.browse(cr, uid, ids):
 329                         res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
 330                         if res[account.id] < 0:
 331                                 res[account.id]=0.0
 332                 for id in ids:
 333                         res[id] = round(res.get(id, 0.0),2)
 334                 return res
 335
 336         def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context={}):
 337                 res = {}
 338                 for account in self.browse(cr, uid, ids):
 339                         if account.hours_qtt_invoiced == 0:
 340                                 res[account.id]=0.0
 341                         else:
 342                                 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
 343                 for id in ids:
 344                         res[id] = round(res.get(id, 0.0),2)
 345                 return res
 346
 347         def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context={}):
 348                 res = {}
 349                 for account in self.browse(cr, uid, ids):
 350                         if account.ca_invoiced == 0:
 351                                 res[account.id]=0.0
 352                         elif account.real_margin <> 0.0:
 353                                 res[account.id] = (account.ca_invoiced / account.real_margin) * 100
 354                         else:
 355                                 res[account.id] = 0.0
 356                 for id in ids:
 357                         res[id] = round(res.get(id, 0.0),2)
 358                 return res
 359
 360         def _remaining_ca_calc(self, cr, uid, ids, name, arg, context={}):
 361                 res = {}
 362                 for account in self.browse(cr, uid, ids):
 363                         if account.amount_max <> 0:
 364                                 res[account.id] = account.amount_max - account.ca_invoiced
 365                         else:
 366                                 res[account.id]=0.0
 367                 for id in ids:
 368                         res[id] = round(res.get(id, 0.0),2)
 369                 return res
 370
 371         def _real_margin_calc(self, cr, uid, ids, name, arg, context={}):
 372                 res = {}
 373                 for account in self.browse(cr, uid, ids):
 374                         res[account.id] = account.ca_invoiced + account.total_cost
 375                 for id in ids:
 376                         res[id] = round(res.get(id, 0.0),2)
 377                 return res
 378
 379         def _theorical_margin_calc(self, cr, uid, ids, name, arg, context={}):
 380                 res = {}
 381                 for account in self.browse(cr, uid, ids):
 382                         res[account.id] = account.ca_theorical + account.total_cost
 383                 for id in ids:
 384                         res[id] = round(res.get(id, 0.0),2)
 385                 return res
 386
 387         def _month(self, cr, uid, ids, name, arg, context=None):
 388                 res = {}
 389                 for id in ids:
 390                         ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
 391                         cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
 392                                         'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
 393                                                 'AND unit_amount <> 0.0')
 394                         res[id] = [int(id * 1000000 + int(x[0])) for x in cr.fetchall()]
 395                 return res
 396
 397         def _user(self, cr, uid, ids, name, arg, context=None):
 398                 res = {}
 399                 cr.execute('SELECT MAX(id) FROM res_users')
 400                 max_user = cr.fetchone()[0]
 401                 for id in ids:
 402                         ids2 = self.search(cr, uid, [('parent_id', 'child_of', [id])])
 403                         cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
 404                                         'WHERE account_id in (' + ','.join([str(x) for x in ids2]) + ') ' \
 405                                                 'AND unit_amount <> 0.0')
 406                         res[id] = [int((id * max_user) + x[0]) for x in cr.fetchall()]
 407                 return res
 408
 409         _columns ={
 410                 'ca_invoiced': fields.function(_ca_invoiced_calc, method=True, type='float', string='Invoiced amount'),
 411                 'total_cost': fields.function(_total_cost_calc, method=True, type='float', string='Total cost'),
 412                 'ca_to_invoice': fields.function(_ca_to_invoice_calc, method=True, type='float', string='Uninvoiced amount'),
 413                 'ca_theorical': fields.function(_ca_theorical_calc, method=True, type='float', string='Theorical revenue'),
 414                 'hours_quantity': fields.function(_hours_quantity_calc, method=True, type='float', string='Hours tot'),
 415                 'last_invoice_date': fields.function(_last_invoice_date_calc, method=True, type='date', string='Last invoice date'),
 416                 'last_worked_invoiced_date': fields.function(_last_worked_invoiced_date_calc, method=True, type='date', string='Last invoiced worked date'),
 417                 'last_worked_date': fields.function(_last_worked_date_calc, method=True, type='date', string='Last worked date'),
 418                 'hours_qtt_non_invoiced': fields.function(_hours_qtt_non_invoiced_calc, method=True, type='float', string='Uninvoiced hours'),
 419                 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, method=True, type='float', string='Invoiced hours'),
 420                 'remaining_hours': fields.function(_remaining_hours_calc, method=True, type='float', string='Remaining hours'),
 421                 'remaining_ca': fields.function(_remaining_ca_calc, method=True, type='float', string='Remaining revenue'),
 422                 'revenue_per_hour': fields.function(_revenue_per_hour_calc, method=True, type='float', string='Revenue per hours (real)'),
 423                 'real_margin': fields.function(_real_margin_calc, method=True, type='float', string='Real margin'),
 424                 'theorical_margin': fields.function(_theorical_margin_calc, method=True, type='float', string='Theorical margin'),
 425                 'real_margin_rate': fields.function(_real_margin_rate_calc, method=True, type='float', string='Real margin rate (%)'),
 426                 'month_ids': fields.function(_month, method=True, type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
 427                 'user_ids': fields.function(_user, method=True, type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
 428         }
 429 account_analytic_account()
 430
 431 class account_analytic_account_summary_user(osv.osv):
 432         _name = "account_analytic_analysis.summary.user"
 433         _description = "Hours summary by user"
 434         _order='user'
 435         _auto = False
 436         _rec_name = 'user'
 437
 438         def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 439                 res = {}
 440                 account_obj = self.pool.get('account.analytic.account')
 441                 cr.execute('SELECT MAX(id) FROM res_users')
 442                 max_user = cr.fetchone()[0]
 443                 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
 444                 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
 445                 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
 446                 user_set = ','.join([str(x) for x in user_ids])
 447                 if account_ids2:
 448                         acc_set = ','.join([str(x) for x in account_ids2])
 449                         cr.execute('SELECT id, unit_amount ' \
 450                                         'FROM account_analytic_analysis_summary_user ' \
 451                                         'WHERE account_id in (%s) ' \
 452                                                 'AND "user" in (%s) ' % \
 453                                                 (acc_set, user_set))
 454                         for sum_id, unit_amount in cr.fetchall():
 455                                 res[sum_id] = unit_amount
 456                 for obj_id in ids:
 457                         res.setdefault(obj_id, 0.0)
 458                         for child_id in account_obj.search(cr, uid,
 459                                         [('parent_id', 'child_of', [int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0)))])]):
 460                                 if child_id != int(str(obj_id/max_user - (obj_id%max_user == 0 and 1 or 0))):
 461                                         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)
 462                 for id in ids:
 463                         res[id] = round(res.get(id, 0.0), 2)
 464                 return res
 465
 466         _columns = {
 467                 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 468                 'unit_amount': fields.function(_unit_amount, method=True, type='float',
 469                         string='Total Time'),
 470                 'user' : fields.many2one('res.users', 'User'),
 471         }
 472         def init(self, cr):
 473                 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
 474                                 'SELECT ' \
 475                                         '(u.account_id * u.max_user) + u.user AS id, ' \
 476                                         'u.account_id AS account_id, ' \
 477                                         'u.user AS user, ' \
 478                                         'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 479                                 'FROM ' \
 480                                         '(SELECT ' \
 481                                                 'a.id AS account_id, ' \
 482                                                 'u1.id AS user, ' \
 483                                                 'MAX(u2.id) AS max_user ' \
 484                                         'FROM ' \
 485                                                 'res_users AS u1, ' \
 486                                                 'res_users AS u2, ' \
 487                                                 'account_analytic_account AS a ' \
 488                                         'GROUP BY u1.id, a.id ' \
 489                                         ') AS u ' \
 490                                 'LEFT JOIN ' \
 491                                         '(SELECT ' \
 492                                                 'l.account_id AS account_id, ' \
 493                                                 'l.user_id AS user, ' \
 494                                                 'SUM(l.unit_amount) AS unit_amount ' \
 495                                         'FROM account_analytic_line AS l, ' \
 496                                                 'account_analytic_journal AS j ' \
 497                                         'WHERE j.type = \'general\' ' \
 498                                         'GROUP BY l.account_id, l.user_id ' \
 499                                         ') AS l '
 500                                         'ON (' \
 501                                                 'u.account_id = l.account_id ' \
 502                                                 'AND u.user = l.user' \
 503                                         ') ' \
 504                                 'GROUP BY u.user, u.account_id, u.max_user' \
 505                                 ')')
 506
 507         def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
 508                 if not context:
 509                         context={}
 510                 if not ids:
 511                         return []
 512
 513                 if fields==None:
 514                         fields = self._columns.keys()
 515
 516                 # construct a clause for the rules :
 517                 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
 518
 519                 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
 520                 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
 521
 522                 res = []
 523                 cr.execute('SELECT MAX(id) FROM res_users')
 524                 max_user = cr.fetchone()[0]
 525                 if len(fields_pre) :
 526                         fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
 527                         for i in range((len(ids) / ID_MAX) + ((len(ids) % ID_MAX) and 1 or 0)):
 528                                 sub_ids = ids[ID_MAX * i:ID_MAX * (i + 1)]
 529                                 if d1:
 530                                         cr.execute('select %s from \"%s\" where id in (%s) ' \
 531                                                         'and account_id in (%s) ' \
 532                                                         'and "user" in (%s) and %s order by %s' % \
 533                                                         (','.join(fields_pre2 + ['id']), self._table,
 534                                                                 ','.join([str(x) for x in sub_ids]),
 535                                                                 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
 536                                                                 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]), d1,
 537                                                                 self._order),d2)
 538                                         if not cr.rowcount == len({}.fromkeys(sub_ids)):
 539                                                 raise except_orm('AccessError',
 540                                                                 'You try to bypass an access rule (Document type: %s).' % self._description)
 541                                 else:
 542                                         cr.execute('select %s from \"%s\" where id in (%s) ' \
 543                                                         'and account_id in (%s) ' \
 544                                                         'and "user" in (%s) order by %s' % \
 545                                                         (','.join(fields_pre2 + ['id']), self._table,
 546                                                                 ','.join([str(x) for x in sub_ids]),
 547                                                                 ','.join([str(x/max_user - (x%max_user == 0 and 1 or 0)) for x in sub_ids]),
 548                                                                 ','.join([str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user)) for x in sub_ids]),
 549                                                                 self._order))
 550                                 res.extend(cr.dictfetchall())
 551                 else:
 552                         res = map(lambda x: {'id': x}, ids)
 553
 554                 for f in fields_pre:
 555                         if self._columns[f].translate:
 556                                 ids = map(lambda x: x['id'], res)
 557                                 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
 558                                 for r in res:
 559                                         r[f] = res_trans.get(r['id'], False) or r[f]
 560
 561                 for table in self._inherits:
 562                         col = self._inherits[table]
 563                         cols = intersect(self._inherit_fields.keys(), fields)
 564                         if not cols:
 565                                 continue
 566                         res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
 567
 568                         res3 = {}
 569                         for r in res2:
 570                                 res3[r['id']] = r
 571                                 del r['id']
 572
 573                         for record in res:
 574                                 record.update(res3[record[col]])
 575                                 if col not in fields:
 576                                         del record[col]
 577
 578                 # all fields which need to be post-processed by a simple function (symbol_get)
 579                 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
 580                 if fields_post:
 581                         # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
 582                         # to get the _symbol_get in each occurence
 583                         for r in res:
 584                                 for f in fields_post:
 585                                         r[f] = self.columns[f]._symbol_get(r[f])
 586                 ids = map(lambda x: x['id'], res)
 587
 588                 # all non inherited fields for which the attribute whose name is in load is False
 589                 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
 590                 for f in fields_post:
 591                         # get the value of that field for all records/ids
 592                         res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
 593                         for record in res:
 594                                 record[f] = res2[record['id']]
 595
 596                 return res
 597
 598 account_analytic_account_summary_user()
 599
 600 class account_analytic_account_summary_month(osv.osv):
 601         _name = "account_analytic_analysis.summary.month"
 602         _description = "Hours summary by month"
 603         _auto = False
 604         _rec_name = 'month'
 605         _order = 'month'
 606
 607         def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 608                 res = {}
 609                 account_obj = self.pool.get('account.analytic.account')
 610                 account_ids = [int(str(int(x))[:-6]) for x in ids]
 611                 month_ids = [int(str(int(x))[-6:]) for x in ids]
 612                 account_ids2 = account_obj.search(cr, uid, [('parent_id', 'child_of', account_ids)])
 613                 month_set = ','.join([str(x) for x in month_ids])
 614                 if account_ids2:
 615                         acc_set = ','.join([str(x) for x in account_ids2])
 616                         cr.execute('SELECT id, unit_amount ' \
 617                                         'FROM account_analytic_analysis_summary_month ' \
 618                                         'WHERE account_id in (%s) ' \
 619                                                 'AND month_id in (%s) ' % \
 620                                                 (acc_set, month_set))
 621                         for sum_id, unit_amount in cr.fetchall():
 622                                 res[sum_id] = unit_amount
 623                 for obj_id in ids:
 624                         res.setdefault(obj_id, 0.0)
 625                         for child_id in account_obj.search(cr, uid,
 626                                         [('parent_id', 'child_of', [int(str(int(obj_id))[:-6])])]):
 627                                 if child_id != int(str(int(obj_id))[:-6]):
 628                                         res[obj_id] += res.get(int(child_id * 1000000 + int(obj_id)), 0.0)
 629                 for id in ids:
 630                         res[id] = round(res.get(id, 0.0), 2)
 631                 return res
 632
 633         _columns = {
 634                 'account_id': fields.many2one('account.analytic.account', 'Analytic Account',
 635                         readonly=True),
 636                 'unit_amount': fields.function(_unit_amount, method=True, type='float',
 637                         string='Total Time'),
 638                 'month': fields.char('Month', size=25, readonly=True),
 639         }
 640         def init(self, cr):
 641                 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_month AS (' \
 642                                 'SELECT ' \
 643                                         '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id  * 1000000))::integer AS id, ' \
 644                                         'd.account_id AS account_id, ' \
 645                                         'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
 646                                         'TO_CHAR(d.month, \'YYYYMM\') AS month_id, ' \
 647                                         'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 648                                 'FROM ' \
 649                                         '(SELECT ' \
 650                                                 'd2.account_id, ' \
 651                                                 'd2.month ' \
 652                                         'FROM ' \
 653                                                 '(SELECT ' \
 654                                                         'a.id AS account_id, ' \
 655                                                         'l.month AS month ' \
 656                                                 'FROM ' \
 657                                                         '(SELECT ' \
 658                                                                 'DATE_TRUNC(\'month\', l.date) AS month ' \
 659                                                         'FROM account_analytic_line AS l, ' \
 660                                                                 'account_analytic_journal AS j ' \
 661                                                         'WHERE j.type = \'general\' ' \
 662                                                         'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
 663                                                         ') AS l, ' \
 664                                                         'account_analytic_account AS a ' \
 665                                                 'GROUP BY l.month, a.id ' \
 666                                                 ') AS d2 ' \
 667                                         'GROUP BY d2.account_id, d2.month ' \
 668                                         ') AS d ' \
 669                                 'LEFT JOIN ' \
 670                                         '(SELECT ' \
 671                                                 'l.account_id AS account_id, ' \
 672                                                 'DATE_TRUNC(\'month\', l.date) AS month, ' \
 673                                                 'SUM(l.unit_amount) AS unit_amount ' \
 674                                         'FROM account_analytic_line AS l, ' \
 675                                                 'account_analytic_journal AS j ' \
 676                                         'WHERE j.type = \'general\' ' \
 677                                         'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
 678                                         ') AS l '
 679                                         'ON (' \
 680                                                 'd.account_id = l.account_id ' \
 681                                                 'AND d.month = l.month' \
 682                                         ') ' \
 683                                 'GROUP BY d.month, d.account_id ' \
 684                                 ')')
 685
 686         def _read_flat(self, cr, user, ids, fields, context=None, load='_classic_read'):
 687                 if not context:
 688                         context={}
 689                 if not ids:
 690                         return []
 691
 692                 if fields==None:
 693                         fields = self._columns.keys()
 694
 695                 # construct a clause for the rules :
 696                 d1, d2 = self.pool.get('ir.rule').domain_get(cr, user, self._name)
 697
 698                 # all inherited fields + all non inherited fields for which the attribute whose name is in load is True
 699                 fields_pre = filter(lambda x: x in self._columns and getattr(self._columns[x],'_classic_write'), fields) + self._inherits.values()
 700
 701                 res = []
 702                 if len(fields_pre) :
 703                         fields_pre2 = map(lambda x: (x in ('create_date', 'write_date')) and ('date_trunc(\'second\', '+x+') as '+x) or '"'+x+'"', fields_pre)
 704                         for i in range((len(ids) / ID_MAX) + ((len(ids) % ID_MAX) and 1 or 0)):
 705                                 sub_ids = ids[ID_MAX * i:ID_MAX * (i + 1)]
 706                                 if d1:
 707                                         cr.execute('select %s from \"%s\" where id in (%s) ' \
 708                                                         'and account_id in (%s) ' \
 709                                                         'and month_id in (%s) and %s order by %s' % \
 710                                                         (','.join(fields_pre2 + ['id']), self._table,
 711                                                                 ','.join([str(x) for x in sub_ids]),
 712                                                                 ','.join([str(x)[:-6] for x in sub_ids]),
 713                                                                 ','.join([str(x)[-6:] for x in sub_ids]), d1,
 714                                                                 self._order),d2)
 715                                         if not cr.rowcount == len({}.fromkeys(sub_ids)):
 716                                                 raise except_orm('AccessError',
 717                                                                 'You try to bypass an access rule (Document type: %s).' % self._description)
 718                                 else:
 719                                         cr.execute('select %s from \"%s\" where id in (%s) ' \
 720                                                         'and account_id in (%s) ' \
 721                                                         'and month_id in (%s) order by %s' % \
 722                                                         (','.join(fields_pre2 + ['id']), self._table,
 723                                                                 ','.join([str(x) for x in sub_ids]),
 724                                                                 ','.join([str(x)[:-6] for x in sub_ids]),
 725                                                                 ','.join([str(x)[-6:] for x in sub_ids]),
 726                                                                 self._order))
 727                                 res.extend(cr.dictfetchall())
 728                 else:
 729                         res = map(lambda x: {'id': x}, ids)
 730
 731                 for f in fields_pre:
 732                         if self._columns[f].translate:
 733                                 ids = map(lambda x: x['id'], res)
 734                                 res_trans = self.pool.get('ir.translation')._get_ids(cr, user, self._name+','+f, 'model', context.get('lang', False) or 'en_US', ids)
 735                                 for r in res:
 736                                         r[f] = res_trans.get(r['id'], False) or r[f]
 737
 738                 for table in self._inherits:
 739                         col = self._inherits[table]
 740                         cols = intersect(self._inherit_fields.keys(), fields)
 741                         if not cols:
 742                                 continue
 743                         res2 = self.pool.get(table).read(cr, user, [x[col] for x in res], cols, context, load)
 744
 745                         res3 = {}
 746                         for r in res2:
 747                                 res3[r['id']] = r
 748                                 del r['id']
 749
 750                         for record in res:
 751                                 record.update(res3[record[col]])
 752                                 if col not in fields:
 753                                         del record[col]
 754
 755                 # all fields which need to be post-processed by a simple function (symbol_get)
 756                 fields_post = filter(lambda x: x in self._columns and self._columns[x]._symbol_get, fields)
 757                 if fields_post:
 758                         # maybe it would be faster to iterate on the fields then on res, so that we wouldn't need
 759                         # to get the _symbol_get in each occurence
 760                         for r in res:
 761                                 for f in fields_post:
 762                                         r[f] = self.columns[f]._symbol_get(r[f])
 763                 ids = map(lambda x: x['id'], res)
 764
 765                 # all non inherited fields for which the attribute whose name is in load is False
 766                 fields_post = filter(lambda x: x in self._columns and not getattr(self._columns[x], load), fields)
 767                 for f in fields_post:
 768                         # get the value of that field for all records/ids
 769                         res2 = self._columns[f].get(cr, self, ids, f, user, context=context, values=res)
 770                         for record in res:
 771                                 record[f] = res2[record['id']]
 772
 773                 return res
 774
 775 account_analytic_account_summary_month()
 776