addons/account_analytic_analysis/account_analytic_analysis.py

   1 # -*- coding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
   6 #
   7 #    This program is free software: you can redistribute it and/or modify
   8 #    it under the terms of the GNU Affero General Public License as
   9 #    published by the Free Software Foundation, either version 3 of the
  10 #    License, or (at your option) any later version.
  11 #
  12 #    This program is distributed in the hope that it will be useful,
  13 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  14 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15 #    GNU Affero General Public License for more details.
  16 #
  17 #    You should have received a copy of the GNU Affero General Public License
  18 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  19 #
  20 ##############################################################################
  21
  22 from osv import osv, fields
  23 from osv.orm import intersect, except_orm
  24 import tools.sql
  25 from tools.translate import _
  26 from decimal_precision import decimal_precision as dp
  27
  28
  29 class account_analytic_account(osv.osv):
  30     _name = "account.analytic.account"
  31     _inherit = "account.analytic.account"
  32
  33     def _analysis_all(self, cr, uid, ids, fields, arg, context=None):
  34         dp = 2
  35         res = dict([(i, {}) for i in ids])
  36         parent_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
  37         accounts = self.browse(cr, uid, ids, context=context)
  38
  39         for f in fields:
  40             if f == 'user_ids':
  41                 cr.execute('SELECT MAX(id) FROM res_users')
  42                 max_user = cr.fetchone()[0]
  43                 if parent_ids:
  44                     cr.execute('SELECT DISTINCT("user") FROM account_analytic_analysis_summary_user ' \
  45                                'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
  46                     result = cr.fetchall()
  47                 else:
  48                     result = []
  49                 for id in ids:
  50                     res[id][f] = [int((id * max_user) + x[0]) for x in result]
  51             elif f == 'month_ids':
  52                 if parent_ids:
  53                     cr.execute('SELECT DISTINCT(month_id) FROM account_analytic_analysis_summary_month ' \
  54                                'WHERE account_id IN %s AND unit_amount <> 0.0', (parent_ids,))
  55                     result = cr.fetchall()
  56                 else:
  57                     result = []
  58                 for id in ids:
  59                     res[id][f] = [int(id * 1000000 + int(x[0])) for x in result]
  60             elif f == 'last_worked_invoiced_date':
  61                 for id in ids:
  62                     res[id][f] = False
  63                 if parent_ids:
  64                     cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
  65                             FROM account_analytic_line \
  66                             WHERE account_id IN %s \
  67                                 AND invoice_id IS NOT NULL \
  68                             GROUP BY account_analytic_line.account_id;", (parent_ids,))
  69                     for account_id, sum in cr.fetchall():
  70                         if account_id not in res:
  71                             res[account_id] = {}
  72                         res[account_id][f] = sum
  73             elif f == 'ca_to_invoice':
  74                 for id in ids:
  75                     res[id][f] = 0.0
  76                 res2 = {}
  77                 if parent_ids:
  78                     # Amount uninvoiced hours to invoice at sale price
  79                     # Warning
  80                     # This computation doesn't take care of pricelist !
  81                     # Just consider list_price
  82                     cr.execute("""SELECT account_analytic_account.id, \
  83                                 COALESCE(SUM (product_template.list_price * \
  84                                     account_analytic_line.unit_amount * \
  85                                     ((100-hr_timesheet_invoice_factor.factor)/100)), 0.0) \
  86                                     AS ca_to_invoice \
  87                             FROM product_template \
  88                             JOIN product_product \
  89                                 ON product_template.id = product_product.product_tmpl_id \
  90                             JOIN account_analytic_line \
  91                                 ON account_analytic_line.product_id = product_product.id \
  92                             JOIN account_analytic_journal \
  93                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
  94                             JOIN account_analytic_account \
  95                                 ON account_analytic_account.id = account_analytic_line.account_id \
  96                             JOIN hr_timesheet_invoice_factor \
  97                                 ON hr_timesheet_invoice_factor.id = account_analytic_account.to_invoice \
  98                             WHERE account_analytic_account.id IN %s \
  99                                 AND account_analytic_line.invoice_id IS NULL \
 100                                 AND account_analytic_line.to_invoice IS NOT NULL \
 101                                 AND account_analytic_journal.type = 'sale' \
 102                             GROUP BY account_analytic_account.id;""", (parent_ids,))
 103                     for account_id, sum in cr.fetchall():
 104                         if account_id not in res:
 105                             res[account_id] = {}
 106                         res[account_id][f] = round(sum, dp)
 107
 108                 # sum both result on account_id
 109                 for id in ids:
 110                     res[id][f] = round(res.get(id, {}).get(f, 0.0), dp) + round(res2.get(id, 0.0), 2)
 111             elif f == 'last_invoice_date':
 112                 for id in ids:
 113                     res[id][f] = False
 114                 if parent_ids:
 115                     cr.execute ("SELECT account_analytic_line.account_id, \
 116                                 DATE(MAX(account_invoice.date_invoice)) \
 117                             FROM account_analytic_line \
 118                             JOIN account_invoice \
 119                                 ON account_analytic_line.invoice_id = account_invoice.id \
 120                             WHERE account_analytic_line.account_id IN %s \
 121                                 AND account_analytic_line.invoice_id IS NOT NULL \
 122                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 123                     for account_id, lid in cr.fetchall():
 124                         res[account_id][f] = lid
 125             elif f == 'last_worked_date':
 126                 for id in ids:
 127                     res[id][f] = False
 128                 if parent_ids:
 129                     cr.execute("SELECT account_analytic_line.account_id, MAX(date) \
 130                             FROM account_analytic_line \
 131                             WHERE account_id IN %s \
 132                                 AND invoice_id IS NULL \
 133                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 134                     for account_id, lwd in cr.fetchall():
 135                         if account_id not in res:
 136                             res[account_id] = {}
 137                         res[account_id][f] = lwd
 138             elif f == 'hours_qtt_non_invoiced':
 139                 for id in ids:
 140                     res[id][f] = 0.0
 141                 if parent_ids:
 142                     cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
 143                             FROM account_analytic_line \
 144                             JOIN account_analytic_journal \
 145                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
 146                             WHERE account_analytic_line.account_id IN %s \
 147                                 AND account_analytic_journal.type='general' \
 148                                 AND invoice_id IS NULL \
 149                                 AND to_invoice IS NOT NULL \
 150                             GROUP BY account_analytic_line.account_id;",(parent_ids,))
 151                     for account_id, sua in cr.fetchall():
 152                         if account_id not in res:
 153                             res[account_id] = {}
 154                         res[account_id][f] = round(sua, dp)
 155                 for id in ids:
 156                     res[id][f] = round(res[id][f], dp)
 157             elif f == 'hours_quantity':
 158                 for id in ids:
 159                     res[id][f] = 0.0
 160                 if parent_ids:
 161                     cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(unit_amount), 0.0) \
 162                             FROM account_analytic_line \
 163                             JOIN account_analytic_journal \
 164                                 ON account_analytic_line.journal_id = account_analytic_journal.id \
 165                             WHERE account_analytic_line.account_id IN %s \
 166                                 AND account_analytic_journal.type='general' \
 167                             GROUP BY account_analytic_line.account_id",(parent_ids,))
 168                     ff =  cr.fetchall()
 169                     for account_id, hq in ff:
 170                         if account_id not in res:
 171                             res[account_id] = {}
 172                         res[account_id][f] = round(hq, dp)
 173                 for id in ids:
 174                     res[id][f] = round(res[id][f], dp)
 175             elif f == 'ca_theorical':
 176                 # TODO Take care of pricelist and purchase !
 177                 for id in ids:
 178                     res[id][f] = 0.0
 179                 # Warning
 180                 # This computation doesn't take care of pricelist !
 181                 # Just consider list_price
 182                 if parent_ids:
 183                     cr.execute("""SELECT account_analytic_line.account_id AS account_id, \
 184                                 COALESCE(SUM((account_analytic_line.unit_amount * pt.list_price) \
 185                                     - (account_analytic_line.unit_amount * pt.list_price \
 186                                         * hr.factor)), 0.0) AS somme
 187                             FROM account_analytic_line \
 188                             LEFT JOIN account_analytic_journal \
 189                                 ON (account_analytic_line.journal_id = account_analytic_journal.id) \
 190                             JOIN product_product pp \
 191                                 ON (account_analytic_line.product_id = pp.id) \
 192                             JOIN product_template pt \
 193                                 ON (pp.product_tmpl_id = pt.id) \
 194                             JOIN account_analytic_account a \
 195                                 ON (a.id=account_analytic_line.account_id) \
 196                             JOIN hr_timesheet_invoice_factor hr \
 197                                 ON (hr.id=a.to_invoice) \
 198                         WHERE account_analytic_line.account_id IN %s \
 199                             AND a.to_invoice IS NOT NULL \
 200                             AND account_analytic_journal.type IN ('purchase', 'general')
 201                         GROUP BY account_analytic_line.account_id""",(parent_ids,))
 202                     for account_id, sum in cr.fetchall():
 203                         res[account_id][f] = round(sum, dp)
 204         return res
 205
 206     def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 207         res = {}
 208         res_final = {}
 209         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 210         for i in child_ids:
 211             res[i] =  0.0
 212         if not child_ids:
 213             return res
 214
 215         if child_ids:
 216             cr.execute("SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
 217                     FROM account_analytic_line \
 218                     JOIN account_analytic_journal \
 219                         ON account_analytic_line.journal_id = account_analytic_journal.id  \
 220                     WHERE account_analytic_line.account_id IN %s \
 221                         AND account_analytic_journal.type = 'sale' \
 222                     GROUP BY account_analytic_line.account_id", (child_ids,))
 223             for account_id, sum in cr.fetchall():
 224                 res[account_id] = round(sum,2)
 225         res_final = res
 226         return res_final
 227
 228     def _expense_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 229         res = {}
 230         res_final = {}
 231         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 232         for i in child_ids:
 233             res[i] =  0.0
 234         if not child_ids:
 235             return res
 236
 237         if child_ids:
 238             cr.execute("select hel.analytic_account,SUM(hel.unit_amount*hel.unit_quantity) from hr_expense_line as hel\
 239                         LEFT JOIN hr_expense_expense as he ON he.id = hel.expense_id\
 240                         where he.state = 'paid' and hel.analytic_account IN %s \
 241                         GROUP BY hel.analytic_account",(child_ids,))
 242             for account_id, sum in cr.fetchall():
 243                 res[account_id] = sum
 244         res_final = res
 245         return res_final
 246
 247     def _expense_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
 248         res = {}
 249         res_final = {}
 250         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 251         for i in child_ids:
 252             res[i] =  0.0
 253         if not child_ids:
 254             return res
 255
 256         if child_ids:
 257             cr.execute("select hel.analytic_account, SUM(hel.unit_amount*hel.unit_quantity) from hr_expense_line as hel\
 258                         LEFT JOIN hr_expense_expense as he ON he.id = hel.expense_id\
 259                         where he.state = 'invoiced' and hel.analytic_account IN %s \
 260                         GROUP BY hel.analytic_account",(child_ids,))
 261             for account_id, sum in cr.fetchall():
 262                 res[account_id] = sum
 263         res_final = res
 264         return res_final
 265
 266     def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
 267         res = {}
 268         res_final = {}
 269         child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 270         for i in child_ids:
 271             res[i] =  0.0
 272         if not child_ids:
 273             return res
 274         if child_ids:
 275             cr.execute("""SELECT account_analytic_line.account_id, COALESCE(SUM(amount), 0.0) \
 276                     FROM account_analytic_line \
 277                     JOIN account_analytic_journal \
 278                         ON account_analytic_line.journal_id = account_analytic_journal.id \
 279                     WHERE account_analytic_line.account_id IN %s \
 280                         AND amount<0 \
 281                     GROUP BY account_analytic_line.account_id""",(child_ids,))
 282             for account_id, sum in cr.fetchall():
 283                 res[account_id] = round(sum,2)
 284         res_final = res
 285         return res_final
 286
 287     def _remaining_hours_calc(self, cr, uid, ids, name, arg, context=None):
 288         res = {}
 289         for account in self.browse(cr, uid, ids, context=context):
 290             if account.quantity_max != 0:
 291                 res[account.id] = account.quantity_max - account.hours_qtt_invoiced
 292             else:
 293                 res[account.id] = 0.0
 294         for id in ids:
 295             res[id] = round(res.get(id, 0.0),2)
 296         return res
 297
 298     def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
 299         res = {}
 300         for account in self.browse(cr, uid, ids, context=context):
 301             res[account.id] = account.hours_quantity - account.hours_qtt_non_invoiced
 302             if res[account.id] < 0:
 303                 res[account.id] = 0.0
 304         for id in ids:
 305             res[id] = round(res.get(id, 0.0),2)
 306         return res
 307
 308     def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
 309         res = {}
 310         for account in self.browse(cr, uid, ids, context=context):
 311             if account.hours_qtt_invoiced == 0:
 312                 res[account.id]=0.0
 313             else:
 314                 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
 315         for id in ids:
 316             res[id] = round(res.get(id, 0.0),2)
 317         return res
 318
 319     def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
 320         res = {}
 321         for account in self.browse(cr, uid, ids, context=context):
 322             if account.ca_invoiced == 0:
 323                 res[account.id]=0.0
 324             elif account.total_cost != 0.0:
 325                 res[account.id] = -(account.real_margin / account.total_cost) * 100
 326             else:
 327                 res[account.id] = 0.0
 328         for id in ids:
 329             res[id] = round(res.get(id, 0.0),2)
 330         return res
 331
 332     def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
 333         res = {}
 334         for account in self.browse(cr, uid, ids, context=context):
 335             if account.amount_max != 0:
 336                 res[account.id] = account.amount_max - account.ca_invoiced
 337             else:
 338                 res[account.id]=0.0
 339         for id in ids:
 340             res[id] = round(res.get(id, 0.0),2)
 341         return res
 342
 343     def _remaining_expnse_calc(self, cr, uid, ids, name, arg, context=None):
 344         res = {}
 345         for account in self.browse(cr, uid, ids, context=context):
 346             if account.expense_max != 0:
 347                 res[account.id] = account.expense_max - account.expense_invoiced
 348             else:
 349                 res[account.id]=0.0
 350         for id in ids:
 351             res[id] = round(res.get(id, 0.0),2)
 352         return res
 353
 354     def _real_margin_calc(self, cr, uid, ids, name, arg, context=None):
 355         res = {}
 356         for account in self.browse(cr, uid, ids, context=context):
 357             res[account.id] = account.ca_invoiced + account.total_cost
 358         for id in ids:
 359             res[id] = round(res.get(id, 0.0),2)
 360         return res
 361
 362     def _theorical_margin_calc(self, cr, uid, ids, name, arg, context=None):
 363         res = {}
 364         for account in self.browse(cr, uid, ids, context=context):
 365             res[account.id] = account.ca_theorical + account.total_cost
 366         for id in ids:
 367             res[id] = round(res.get(id, 0.0),2)
 368         return res
 369
 370     def _is_overdue_quantity(self, cr, uid, ids, fieldnames, args, context=None):
 371         result = dict.fromkeys(ids, 0)
 372         for record in self.browse(cr, uid, ids, context=context):
 373             if record.quantity_max > 0.0:
 374                 result[record.id] = int(record.hours_quantity >= record.quantity_max)
 375             else:
 376                 result[record.id] = 0
 377         return result
 378
 379     def _get_analytic_account(self, cr, uid, ids, context=None):
 380         result = set()
 381         for line in self.pool.get('account.analytic.line').browse(cr, uid, ids, context=context):
 382             result.add(line.account_id.id)
 383         return list(result)
 384
 385     _columns = {
 386         'is_overdue_quantity' : fields.function(_is_overdue_quantity, method=True, type='boolean', string='Overdue Quantity',
 387                                                 store={
 388                                                     'account.analytic.line' : (_get_analytic_account, None, 20),
 389                                                 }),
 390         'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
 391             help="Total customer invoiced amount for this account.",
 392             digits_compute=dp.get_precision('Account')),
 393         'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
 394             help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
 395             digits_compute=dp.get_precision('Account')),
 396         'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
 397             help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
 398             digits_compute=dp.get_precision('Account')),
 399         'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
 400             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.",
 401             digits_compute=dp.get_precision('Account')),
 402         'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Time',
 403             help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
 404         'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
 405             help="If invoice from the costs, this is the date of the latest invoiced."),
 406         'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
 407             help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
 408         'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
 409             help="Date of the latest work done on this account."),
 410         'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
 411             help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
 412         'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
 413             help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
 414         'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
 415             help="Computed using the formula: Maximum Time - Total Time"),
 416         'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
 417             help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
 418             digits_compute=dp.get_precision('Account')),
 419         'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
 420             help="Computed using the formula: Invoiced Amount / Total Time",
 421             digits_compute=dp.get_precision('Account')),
 422         'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
 423             help="Computed using the formula: Invoiced Amount - Total Costs.",
 424             digits_compute=dp.get_precision('Account')),
 425         'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
 426             help="Computed using the formula: Theorial Revenue - Total Costs",
 427             digits_compute=dp.get_precision('Account')),
 428         'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
 429             help="Computes using the formula: (Real Margin / Total Costs) * 100.",
 430             digits_compute=dp.get_precision('Account')),
 431         'fix_price_invoices' : fields.boolean('Fix Price Invoices'),
 432         'invoice_on_timesheets' : fields.boolean("Invoice On Timesheets"),
 433         'charge_expenses' : fields.boolean('Charge Expenses'),
 434         'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
 435         'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
 436         'template_id':fields.many2one('account.analytic.account', 'Template Of Contract'),
 437         'expense_invoiced' : fields.function(_expense_invoiced_calc, type="float"),
 438         'expense_to_invoice' : fields.function(_expense_to_invoice_calc, type='float'),
 439         'remaining_expense' : fields.function(_remaining_expnse_calc, type="float"),
 440         #'fix_exp_max' : fields.float('Max. amt'),
 441         #'timesheet_max': fields.float('max_timesheet'),
 442         'expense_max': fields.float('expenses'),
 443     }
 444     def on_change_template(self, cr, uid, id, template_id):
 445         if not template_id:
 446             return {}
 447         res = {'value':{}}
 448         template = self.browse(cr, uid, template_id)
 449         if template.date_start:
 450             res['value']['date_start'] = str(template.date_start)
 451         if template.date:
 452             res['value']['date'] = str(template.date)
 453         res['value']['quantity_max'] = template.quantity_max
 454         res['value']['remaining_hours'] = template.remaining_hours
 455         res['value']['to_invoice'] = template.to_invoice.id
 456         res['value']['pricelist_id'] = template.pricelist_id.id
 457         res['value']['description'] = template.description
 458         return res
 459
 460 account_analytic_account()
 461
 462 class account_analytic_account_summary_user(osv.osv):
 463     _name = "account_analytic_analysis.summary.user"
 464     _description = "Hours Summary by User"
 465     _order='user'
 466     _auto = False
 467     _rec_name = 'user'
 468
 469     def _unit_amount(self, cr, uid, ids, name, arg, context=None):
 470         res = {}
 471         account_obj = self.pool.get('account.analytic.account')
 472         cr.execute('SELECT MAX(id) FROM res_users')
 473         max_user = cr.fetchone()[0]
 474         account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
 475         user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
 476         parent_ids = tuple(account_ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
 477         if parent_ids:
 478             cr.execute('SELECT id, unit_amount ' \
 479                     'FROM account_analytic_analysis_summary_user ' \
 480                     'WHERE account_id IN %s ' \
 481                         'AND "user" IN %s',(parent_ids, tuple(user_ids),))
 482             for sum_id, unit_amount in cr.fetchall():
 483                 res[sum_id] = unit_amount
 484         for id in ids:
 485             res[id] = round(res.get(id, 0.0), 2)
 486         return res
 487
 488     _columns = {
 489         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 490         'unit_amount': fields.float('Total Time'),
 491         'user': fields.many2one('res.users', 'User'),
 492     }
 493
 494     def init(self, cr):
 495         tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
 496         cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
 497                 'SELECT ' \
 498                     '(u.account_id * u.max_user) + u."user" AS id, ' \
 499                     'u.account_id AS account_id, ' \
 500                     'u."user" AS "user", ' \
 501                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 502                 'FROM ' \
 503                     '(SELECT ' \
 504                         'a.id AS account_id, ' \
 505                         'u1.id AS "user", ' \
 506                         'MAX(u2.id) AS max_user ' \
 507                     'FROM ' \
 508                         'res_users AS u1, ' \
 509                         'res_users AS u2, ' \
 510                         'account_analytic_account AS a ' \
 511                     'GROUP BY u1.id, a.id ' \
 512                     ') AS u ' \
 513                 'LEFT JOIN ' \
 514                     '(SELECT ' \
 515                         'l.account_id AS account_id, ' \
 516                         'l.user_id AS "user", ' \
 517                         'SUM(l.unit_amount) AS unit_amount ' \
 518                     'FROM account_analytic_line AS l, ' \
 519                         'account_analytic_journal AS j ' \
 520                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 521                     'GROUP BY l.account_id, l.user_id ' \
 522                     ') AS l '
 523                     'ON (' \
 524                         'u.account_id = l.account_id ' \
 525                         'AND u."user" = l."user"' \
 526                     ') ' \
 527                 'GROUP BY u."user", u.account_id, u.max_user' \
 528                 ')')
 529
 530 account_analytic_account_summary_user()
 531
 532 class account_analytic_account_summary_month(osv.osv):
 533     _name = "account_analytic_analysis.summary.month"
 534     _description = "Hours summary by month"
 535     _auto = False
 536     _rec_name = 'month'
 537
 538     _columns = {
 539         'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
 540         'unit_amount': fields.float('Total Time'),
 541         'month': fields.char('Month', size=32, readonly=True),
 542     }
 543
 544     def init(self, cr):
 545         tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
 546         cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
 547                 'SELECT ' \
 548                     '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id  * 1000000::bigint))::bigint AS id, ' \
 549                     'd.account_id AS account_id, ' \
 550                     'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
 551                     'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
 552                     'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
 553                 'FROM ' \
 554                     '(SELECT ' \
 555                         'd2.account_id, ' \
 556                         'd2.month ' \
 557                     'FROM ' \
 558                         '(SELECT ' \
 559                             'a.id AS account_id, ' \
 560                             'l.month AS month ' \
 561                         'FROM ' \
 562                             '(SELECT ' \
 563                                 'DATE_TRUNC(\'month\', l.date) AS month ' \
 564                             'FROM account_analytic_line AS l, ' \
 565                                 'account_analytic_journal AS j ' \
 566                             'WHERE j.type = \'general\' ' \
 567                             'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
 568                             ') AS l, ' \
 569                             'account_analytic_account AS a ' \
 570                         'GROUP BY l.month, a.id ' \
 571                         ') AS d2 ' \
 572                     'GROUP BY d2.account_id, d2.month ' \
 573                     ') AS d ' \
 574                 'LEFT JOIN ' \
 575                     '(SELECT ' \
 576                         'l.account_id AS account_id, ' \
 577                         'DATE_TRUNC(\'month\', l.date) AS month, ' \
 578                         'SUM(l.unit_amount) AS unit_amount ' \
 579                     'FROM account_analytic_line AS l, ' \
 580                         'account_analytic_journal AS j ' \
 581                     'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
 582                     'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
 583                     ') AS l '
 584                     'ON (' \
 585                         'd.account_id = l.account_id ' \
 586                         'AND d.month = l.month' \
 587                     ') ' \
 588                 'GROUP BY d.month, d.account_id ' \
 589                 ')')
 590
 591
 592 account_analytic_account_summary_month()
 593 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: