1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
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.
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.
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/>.
20 ##############################################################################
22 from osv import osv, fields
23 from osv.orm import intersect, except_orm
25 from tools.translate import _
26 from decimal_precision import decimal_precision as dp
29 class account_analytic_account(osv.osv):
30 _name = "account.analytic.account"
31 _inherit = "account.analytic.account"
33 def _analysis_all(self, cr, uid, ids, fields, arg, context=None):
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)
41 cr.execute('SELECT MAX(id) FROM res_users')
42 max_user = cr.fetchone()[0]
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()
50 res[id][f] = [int((id * max_user) + x[0]) for x in result]
51 elif f == 'month_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()
59 res[id][f] = [int(id * 1000000 + int(x[0])) for x in result]
60 elif f == 'last_worked_invoiced_date':
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:
72 res[account_id][f] = sum
73 elif f == 'ca_to_invoice':
78 # Amount uninvoiced hours to invoice at sale price
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) \
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 IN ('purchase', 'general') \
102 GROUP BY account_analytic_account.id;""", (parent_ids,))
103 for account_id, sum in cr.fetchall():
104 if account_id not in res:
106 res[account_id][f] = round(sum, dp)
108 # sum both result on account_id
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':
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':
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:
137 res[account_id][f] = lwd
138 elif f == 'hours_qtt_non_invoiced':
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:
154 res[account_id][f] = round(sua, dp)
156 res[id][f] = round(res[id][f], dp)
157 elif f == 'hours_quantity':
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,))
169 for account_id, hq in ff:
170 if account_id not in res:
172 res[account_id][f] = round(hq, dp)
174 res[id][f] = round(res[id][f], dp)
175 elif f == 'ca_theorical':
176 # TODO Take care of pricelist and purchase !
180 # This computation doesn't take care of pricelist !
181 # Just consider list_price
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)
206 def _ca_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
209 child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
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)
228 def _expense_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
231 child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
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
247 def _expense_to_invoice_calc(self, cr, uid, ids, name, arg, context=None):
250 child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
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
266 def _total_cost_calc(self, cr, uid, ids, name, arg, context=None):
269 child_ids = tuple(ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
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 \
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)
287 def _remaining_hours_calc(self, cr, uid, ids, name, arg, context=None):
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
293 res[account.id] = 0.0
295 res[id] = round(res.get(id, 0.0),2)
298 def _hours_qtt_invoiced_calc(self, cr, uid, ids, name, arg, context=None):
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
305 res[id] = round(res.get(id, 0.0),2)
308 def _revenue_per_hour_calc(self, cr, uid, ids, name, arg, context=None):
310 for account in self.browse(cr, uid, ids, context=context):
311 if account.hours_qtt_invoiced == 0:
314 res[account.id] = account.ca_invoiced / account.hours_qtt_invoiced
316 res[id] = round(res.get(id, 0.0),2)
319 def _real_margin_rate_calc(self, cr, uid, ids, name, arg, context=None):
321 for account in self.browse(cr, uid, ids, context=context):
322 if account.ca_invoiced == 0:
324 elif account.total_cost != 0.0:
325 res[account.id] = -(account.real_margin / account.total_cost) * 100
327 res[account.id] = 0.0
329 res[id] = round(res.get(id, 0.0),2)
332 def _remaining_ca_calc(self, cr, uid, ids, name, arg, context=None):
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
340 res[id] = round(res.get(id, 0.0),2)
343 def _remaining_expnse_calc(self, cr, uid, ids, name, arg, context=None):
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
351 res[id] = round(res.get(id, 0.0),2)
354 def _real_margin_calc(self, cr, uid, ids, name, arg, context=None):
356 for account in self.browse(cr, uid, ids, context=context):
357 res[account.id] = account.ca_invoiced + account.total_cost
359 res[id] = round(res.get(id, 0.0),2)
362 def _theorical_margin_calc(self, cr, uid, ids, name, arg, context=None):
364 for account in self.browse(cr, uid, ids, context=context):
365 res[account.id] = account.ca_theorical + account.total_cost
367 res[id] = round(res.get(id, 0.0),2)
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)
376 result[record.id] = 0
379 def _get_analytic_account(self, cr, uid, ids, context=None):
381 for line in self.pool.get('account.analytic.line').browse(cr, uid, ids, context=context):
382 result.add(line.account_id.id)
385 def _sum_of_fields(self, cr, uid, ids, name, arg, context=None):
386 res = dict([(i, {}) for i in ids])
389 total_toinvoice = 0.0
390 total_remaining = 0.0
391 for account in self.browse(cr, uid, ids, context=context):
392 if account.fix_price_invoices:
393 total_max += account.amount_max
394 total_invoiced += account.ca_invoiced
395 total_remaining += account.remaining_ca
396 total_toinvoice += account.ca_to_invoice
397 if account.invoice_on_timesheets:
398 total_max += account.quantity_max
399 total_invoiced += account.hours_qtt_invoiced
400 total_remaining += account.remaining_hours
401 total_toinvoice += account.hours_qtt_non_invoiced
402 if account.charge_expenses:
403 total_max += account.expense_max
404 total_invoiced += account.expense_invoiced
405 total_remaining += account.remaining_expense
406 total_toinvoice += account.expense_to_invoice
407 res[account.id]['est_total'] = total_max or 0.0
408 res[account.id]['invoiced_total'] = total_invoiced or 0.0
409 res[account.id]['remaining_total'] = total_remaining or 0.0
410 res[account.id]['toinvoice_total'] = total_toinvoice or 0.0
414 'is_overdue_quantity' : fields.function(_is_overdue_quantity, method=True, type='boolean', string='Overdue Quantity',
416 'account.analytic.line' : (_get_analytic_account, None, 20),
418 'ca_invoiced': fields.function(_ca_invoiced_calc, type='float', string='Invoiced Amount',
419 help="Total customer invoiced amount for this account.",
420 digits_compute=dp.get_precision('Account')),
421 'total_cost': fields.function(_total_cost_calc, type='float', string='Total Costs',
422 help="Total of costs for this account. It includes real costs (from invoices) and indirect costs, like time spent on timesheets.",
423 digits_compute=dp.get_precision('Account')),
424 'ca_to_invoice': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Amount',
425 help="If invoice from analytic account, the remaining amount you can invoice to the customer based on the total costs.",
426 digits_compute=dp.get_precision('Account')),
427 'ca_theorical': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Theoretical Revenue',
428 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.",
429 digits_compute=dp.get_precision('Account')),
430 'hours_quantity': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Total Time',
431 help="Number of time you spent on the analytic account (from timesheet). It computes quantities on all journal of type 'general'."),
432 'last_invoice_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Last Invoice Date',
433 help="If invoice from the costs, this is the date of the latest invoiced."),
434 'last_worked_invoiced_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Invoiced Cost',
435 help="If invoice from the costs, this is the date of the latest work or cost that have been invoiced."),
436 'last_worked_date': fields.function(_analysis_all, multi='analytic_analysis', type='date', string='Date of Last Cost/Work',
437 help="Date of the latest work done on this account."),
438 'hours_qtt_non_invoiced': fields.function(_analysis_all, multi='analytic_analysis', type='float', string='Uninvoiced Time',
439 help="Number of time (hours/days) (from journal of type 'general') that can be invoiced if you invoice based on analytic account."),
440 'hours_qtt_invoiced': fields.function(_hours_qtt_invoiced_calc, type='float', string='Invoiced Time',
441 help="Number of time (hours/days) that can be invoiced plus those that already have been invoiced."),
442 'remaining_hours': fields.function(_remaining_hours_calc, type='float', string='Remaining Time',
443 help="Computed using the formula: Maximum Time - Total Time"),
444 'remaining_ca': fields.function(_remaining_ca_calc, type='float', string='Remaining Revenue',
445 help="Computed using the formula: Max Invoice Price - Invoiced Amount.",
446 digits_compute=dp.get_precision('Account')),
447 'revenue_per_hour': fields.function(_revenue_per_hour_calc, type='float', string='Revenue per Time (real)',
448 help="Computed using the formula: Invoiced Amount / Total Time",
449 digits_compute=dp.get_precision('Account')),
450 'real_margin': fields.function(_real_margin_calc, type='float', string='Real Margin',
451 help="Computed using the formula: Invoiced Amount - Total Costs.",
452 digits_compute=dp.get_precision('Account')),
453 'theorical_margin': fields.function(_theorical_margin_calc, type='float', string='Theoretical Margin',
454 help="Computed using the formula: Theorial Revenue - Total Costs",
455 digits_compute=dp.get_precision('Account')),
456 'real_margin_rate': fields.function(_real_margin_rate_calc, type='float', string='Real Margin Rate (%)',
457 help="Computes using the formula: (Real Margin / Total Costs) * 100.",
458 digits_compute=dp.get_precision('Account')),
459 'fix_price_invoices' : fields.boolean('Fix Price Invoices'),
460 'invoice_on_timesheets' : fields.boolean("Invoice On Timesheets"),
461 'charge_expenses' : fields.boolean('Charge Expenses'),
462 'month_ids': fields.function(_analysis_all, multi='analytic_analysis', type='many2many', relation='account_analytic_analysis.summary.month', string='Month'),
463 'user_ids': fields.function(_analysis_all, multi='analytic_analysis', type="many2many", relation='account_analytic_analysis.summary.user', string='User'),
464 'template_id':fields.many2one('account.analytic.account', 'Template Of Contract'),
465 'expense_invoiced' : fields.function(_expense_invoiced_calc, type="float"),
466 'expense_to_invoice' : fields.function(_expense_to_invoice_calc, type='float'),
467 'remaining_expense' : fields.function(_remaining_expnse_calc, type="float"),
468 #'fix_exp_max' : fields.float('Max. amt'),
469 #'timesheet_max': fields.float('max_timesheet'),
470 'expense_max': fields.float('expenses'),
471 'est_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
472 'invoiced_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
473 'remaining_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
474 'toinvoice_total' : fields.function(_sum_of_fields, type="float",multi="sum_of_all"),
476 def on_change_template(self, cr, uid, id, template_id):
480 template = self.browse(cr, uid, template_id)
481 if template.date_start:
482 res['value']['date_start'] = str(template.date_start)
484 res['value']['date'] = str(template.date)
485 res['value']['quantity_max'] = template.quantity_max
486 res['value']['remaining_hours'] = template.remaining_hours
487 res['value']['to_invoice'] = template.to_invoice.id
488 res['value']['pricelist_id'] = template.pricelist_id.id
489 res['value']['description'] = template.description
492 def open_hr_expense(self, cr, uid, ids, context=None):
493 account = self.browse(cr, uid, ids[0], context)
494 data_obj = self.pool.get('ir.model.data')
496 journal_id = data_obj.get_object(cr, uid, 'hr_timesheet', 'analytic_journal').id
499 line_ids = self.pool.get('hr.expense.line').search(cr,uid,[('analytic_account','=',account.id)])
500 id2 = data_obj._get_id(cr, uid, 'hr_expense', 'view_expenses_form')
501 id3 = data_obj._get_id(cr, uid, 'hr_expense', 'view_expenses_tree')
503 id2 = data_obj.browse(cr, uid, id2, context=context).res_id
505 id3 = data_obj.browse(cr, uid, id3, context=context).res_id
506 domain = [('line_ids','in',line_ids)]
508 'type': 'ir.actions.act_window',
509 'name': _('Expenses'),
511 'view_mode': 'tree,form',
512 'views': [(id3,'tree'),(id2,'form')],
514 'res_model': 'hr.expense.expense',
518 def hr_to_invoiced_expense(self, cr, uid, ids, context=None):
519 res = self.open_hr_expense(cr,uid,ids,context)
520 account = self.browse(cr, uid, ids[0], context)
521 line_ids = self.pool.get('hr.expense.line').search(cr,uid,[('analytic_account','=',account.id)])
522 res['domain'] = [('line_ids','in',line_ids),('state','=','invoiced')]
525 account_analytic_account()
527 class account_analytic_account_summary_user(osv.osv):
528 _name = "account_analytic_analysis.summary.user"
529 _description = "Hours Summary by User"
534 def _unit_amount(self, cr, uid, ids, name, arg, context=None):
536 account_obj = self.pool.get('account.analytic.account')
537 cr.execute('SELECT MAX(id) FROM res_users')
538 max_user = cr.fetchone()[0]
539 account_ids = [int(str(x/max_user - (x%max_user == 0 and 1 or 0))) for x in ids]
540 user_ids = [int(str(x-((x/max_user - (x%max_user == 0 and 1 or 0)) *max_user))) for x in ids]
541 parent_ids = tuple(account_ids) #We don't want consolidation for each of these fields because those complex computation is resource-greedy.
543 cr.execute('SELECT id, unit_amount ' \
544 'FROM account_analytic_analysis_summary_user ' \
545 'WHERE account_id IN %s ' \
546 'AND "user" IN %s',(parent_ids, tuple(user_ids),))
547 for sum_id, unit_amount in cr.fetchall():
548 res[sum_id] = unit_amount
550 res[id] = round(res.get(id, 0.0), 2)
554 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
555 'unit_amount': fields.float('Total Time'),
556 'user': fields.many2one('res.users', 'User'),
560 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_user')
561 cr.execute('CREATE OR REPLACE VIEW account_analytic_analysis_summary_user AS (' \
563 '(u.account_id * u.max_user) + u."user" AS id, ' \
564 'u.account_id AS account_id, ' \
565 'u."user" AS "user", ' \
566 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
569 'a.id AS account_id, ' \
570 'u1.id AS "user", ' \
571 'MAX(u2.id) AS max_user ' \
573 'res_users AS u1, ' \
574 'res_users AS u2, ' \
575 'account_analytic_account AS a ' \
576 'GROUP BY u1.id, a.id ' \
580 'l.account_id AS account_id, ' \
581 'l.user_id AS "user", ' \
582 'SUM(l.unit_amount) AS unit_amount ' \
583 'FROM account_analytic_line AS l, ' \
584 'account_analytic_journal AS j ' \
585 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
586 'GROUP BY l.account_id, l.user_id ' \
589 'u.account_id = l.account_id ' \
590 'AND u."user" = l."user"' \
592 'GROUP BY u."user", u.account_id, u.max_user' \
595 account_analytic_account_summary_user()
597 class account_analytic_account_summary_month(osv.osv):
598 _name = "account_analytic_analysis.summary.month"
599 _description = "Hours summary by month"
604 'account_id': fields.many2one('account.analytic.account', 'Analytic Account', readonly=True),
605 'unit_amount': fields.float('Total Time'),
606 'month': fields.char('Month', size=32, readonly=True),
610 tools.sql.drop_view_if_exists(cr, 'account_analytic_analysis_summary_month')
611 cr.execute('CREATE VIEW account_analytic_analysis_summary_month AS (' \
613 '(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + (d.account_id * 1000000::bigint))::bigint AS id, ' \
614 'd.account_id AS account_id, ' \
615 'TO_CHAR(d.month, \'Mon YYYY\') AS month, ' \
616 'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, ' \
617 'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount ' \
624 'a.id AS account_id, ' \
625 'l.month AS month ' \
628 'DATE_TRUNC(\'month\', l.date) AS month ' \
629 'FROM account_analytic_line AS l, ' \
630 'account_analytic_journal AS j ' \
631 'WHERE j.type = \'general\' ' \
632 'GROUP BY DATE_TRUNC(\'month\', l.date) ' \
634 'account_analytic_account AS a ' \
635 'GROUP BY l.month, a.id ' \
637 'GROUP BY d2.account_id, d2.month ' \
641 'l.account_id AS account_id, ' \
642 'DATE_TRUNC(\'month\', l.date) AS month, ' \
643 'SUM(l.unit_amount) AS unit_amount ' \
644 'FROM account_analytic_line AS l, ' \
645 'account_analytic_journal AS j ' \
646 'WHERE (j.type = \'general\') and (j.id=l.journal_id) ' \
647 'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) ' \
650 'd.account_id = l.account_id ' \
651 'AND d.month = l.month' \
653 'GROUP BY d.month, d.account_id ' \
657 account_analytic_account_summary_month()
658 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: