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 fields, osv
23 from tools.translate import _
24 from tools import ustr
26 class report_creator(osv.osv):
30 _name = "base_report_creator.report"
31 _description = "Report"
34 # Should request only used fields
36 def export_data(self, cr, uid, ids, fields_to_export, context=None):
40 data_l = self.read(cr, uid, ids, ['sql_query'], context)
45 for key, value in i.items():
46 if key not in fields_to_export:
48 if isinstance(value, tuple):
49 datas.append(ustr(value[1]))
51 datas.append(ustr(value))
52 final_datas += [datas]
54 return {'datas': final_datas}
56 def fields_get(self, cr, user, fields=None, context=None):
59 @param cr: the current row, from the database cursor,
60 @param user: the current user’s ID for security checks,
61 @param Fields: List of field of customer reports form.
62 @return: Dictionary of Fields
67 data = context and context.get('report_id', False) or False
68 if (not context) or 'report_id' not in context:
69 return super(report_creator, self).fields_get(cr, user, fields, context)
71 report = self.browse(cr, user, data)
74 for model in report.model_ids:
75 models[model.model] = self.pool.get(model.model).fields_get(cr, user, context=context)
79 for f in report.field_ids:
81 fields['field'+str(i)] = models[f.field_id.model][f.field_id.name]
84 fields['column_count'] = {'readonly': True, 'type': 'integer', 'string': 'Count', 'size': 64, 'name': 'column_count'}
89 # Should Call self.fields_get !
91 def fields_view_get(self, cr, user, view_id=None, view_type='form', context=None, toolbar=False, submenu=False):
93 Overrides orm field_view_get.
94 @param cr: the current row, from the database cursor,
95 @param user: the current user’s ID for security checks,
96 @return: Dictionary of Fields, arch and toolbar.
101 data = context and context.get('report_id', False) or False
102 if (not context) or 'report_id' not in context:
103 return super(report_creator, self).fields_view_get(cr, user, view_id, view_type, context, toolbar=toolbar, submenu=submenu)
104 report = self.browse(cr, user, data)
106 for model in report.model_ids:
107 models[model.model] = self.pool.get(model.model).fields_get(cr, user, context=context)
110 for f in report.field_ids:
112 fields['field'+str(i)] = models[f.field_id.model][f.field_id.name]
115 fields['column_count'] = {'readonly': True, 'type': 'integer', 'string': 'Count', 'size': 64, 'name': 'column_count'}
117 arch = '<?xml version="1.0"?>'
118 if view_type == 'graph':
119 orientation_eval = {'horz':'horizontal','vert' :'vertical'}
120 orientation = eval(report.view_graph_orientation,orientation_eval)
121 arch +='<graph string="%s" type="%s" orientation="%s">' % (report.name, report.view_graph_type, orientation)
123 for val in ('x','y'):
124 for f in report.field_ids:
125 if f.graph_mode == val:
127 arch += '<field name="%s" select="1"/>' % ('field'+str(i),)
130 arch += '<field name="%s" select="1"/>' % ('column_count',)
132 elif view_type == 'calendar':
133 required_types = ['date_start', 'date_delay', 'color']
134 set_dict = {'view_type':view_type, 'string':report.name}
137 for f in report.field_ids:
138 if f.calendar_mode and f.calendar_mode in required_types:
140 field_cal = 'field'+str(i)
143 field_cal = 'column_count'
144 set_dict[f.calendar_mode] = field_cal
145 del required_types[required_types.index(f.calendar_mode)]
149 temp_list.append('''<field name = "%(name)s" select = "1"/>''' % {'name': 'field' + str(i)})
152 temp_list.append('''<field name="%(name)s" select="1"/>''' % {'name':'column_count'})
154 arch += '''<% (view_type)s string = "%(string)s" date_start = "%(date_start)s" ''' % set_dict
155 if set_dict.get('date_delay', False):
156 arch += ''' date_delay = "%(date_delay)s" ''' % set_dict
158 if set_dict.get('date_stop', False):
159 arch += ''' date_stop="%(date_stop)s" '''%set_dict
161 if set_dict.get('color', False):
162 arch += ''' color="%(color)s"'''%set_dict
164 arch += ''.join(temp_list)
166 arch += '<%s string="%s">' % (view_type, report.name)
168 for f in report.field_ids:
170 arch += '<field name="%s"/>' % ('field' + str(i),)
173 arch += '<field name="%s"/>' % ('column_count',)
174 arch += '</%s>' % (view_type,)
179 result['toolbar'] = {
186 def read(self, cr, user, ids, fields = None, context = None, load = '_classic_read'):
188 overrides orm Read method.Read List of fields for report creator.
189 @param cr: the current row, from the database cursor,
190 @param user: the current user’s ID for security checks,
191 @param ids: List of report creator's id.
192 @param fields: List of fields.
193 @return: List of Dictionary of form [{‘name_of_the_field’: value, ...}, ...]
197 data = context.get('report_id', False)
198 if (not context) or 'report_id' not in context:
199 return super(report_creator, self).read(cr, user, ids, fields, context, load)
203 if self.model_set_id:
204 wp = [self._id_get(cr, user, data, context) + (' in (%s)' % (','.join(map(lambda x: "'" + str(x) + "'", ids))))]
205 report = self._sql_query_get(cr, user, [data], 'sql_query', None, ctx, where_plus = wp)
206 sql_query = report[data]
207 cr.execute(sql_query)
208 res = cr.dictfetchall()
209 fields_get = self.fields_get(cr, user, None, context)
213 field_dict = fields_get.get(k)
214 field_type = field_dict and field_dict.get('type', False) or False
215 if field_type and field_type == 'many2one':
218 related_name = self.pool.get(field_dict.get('relation')).name_get(cr, user, [r[k]], context)[0]
223 def search(self, cr, user, args, offset=0, limit=None, order=None, context=None, count=False):
225 overrides orm search method.
226 @param cr: the current row, from the database cursor,
227 @param user: the current user’s ID for security checks,
228 @param args: list of tuples of form [(‘name_of_the_field’, ‘operator’, value), ...].
233 context_id = context.get('report_id', False)
235 if (not context) or 'report_id' not in context:
236 return super(report_creator, self).search(cr, user, args, offset, limit, order, context, count)
238 report = self.browse(cr, user, context_id)
241 for f in report.field_ids:
243 fields['field'+str(i)] = (f.field_id.model, f.field_id.name)
246 fields['column_count'] = (False, 'Count')
251 res = self.pool.get(fields[a[0]][0])._where_calc(cr, user, [[fields[a[0]][1], a[1], a[2]]], active_test = False, context = context)
255 newargs += [("count(*) " + a[1] +" " + str(a[2]))]
258 report = self._sql_query_get(cr, user, [context_id], 'sql_query', None, ctx, where_plus = newargs, limit=limit, offset=offset)
259 query = report[context_id]
260 cr.execute(query, newargs2)
261 result = cr.fetchall()
262 return map(lambda x: x[0], result)
264 def _path_get(self, cr, uid, models, filter_ids=[]):
266 @param cr: the current row, from the database cursor,
267 @param uid: the current user’s ID for security checks,
268 @param models: List of object.
270 self.model_set_id = False
276 model_dict[model.model] = self.pool.get(model.model)._table
278 model_list = model_dict.keys()
279 reference_model_dict = {}
280 for model in model_dict:
281 from_list.append(model_dict.get(model))
282 rest_list = model_dict.keys()
283 rest_list.remove(model)
284 model_pool = self.pool.get(model)
285 fields_get = model_pool.fields_get(cr, uid)
288 def _get_inherit_fields(obj):
289 pool_model = self.pool.get(obj)
290 #Adding the columns of the model itself
291 model_columns.update(pool_model._columns)
292 #Adding the columns of its _inherits
293 for record in pool_model._inherits.keys():
294 _get_inherit_fields(record)
296 _get_inherit_fields(model)
298 fields_filter = dict(filter(lambda x:x[1].get('relation', False)
299 and x[1].get('relation') in rest_list
300 and x[1].get('type') == 'many2one'
301 and not (isinstance(model_columns[x[0]], fields.function) or isinstance(model_columns[x[0]], fields.related)), fields_get.items()))
303 model in model_list and model_list.remove(model)
304 model_count = reference_model_dict.get(model, False)
306 reference_model_dict[model] = model_count +1
308 reference_model_dict[model] = 1
309 for k, v in fields_filter.items():
310 v.get('relation') in model_list and model_list.remove(v.get('relation'))
311 relation_count = reference_model_dict.get(v.get('relation'), False)
313 reference_model_dict[v.get('relation')] = relation_count+1
315 reference_model_dict[v.get('relation')]=1
316 if k in self.pool.get(model)._columns:
317 str_where = model_dict.get(model)+"."+ k + "=" + model_dict.get(v.get('relation'))+'.id'
318 where_list.append(str_where)
320 if reference_model_dict:
321 self.model_set_id = model_dict.get(reference_model_dict.keys()[reference_model_dict.values().index(min(reference_model_dict.values()))])
322 if model_list and not len(model_dict.keys()) == 1:
323 raise osv.except_osv(_('No Related Models!!'), _('These is/are model(s) (%s) in selection which is/are not related to any other model') % ','.join(model_list))
325 if filter_ids and where_list <> []:
326 filter_list.append(' and ')
327 filter_list.append(' ')
329 for filter_id in filter_ids:
330 filter_list.append(filter_id.expression)
331 filter_list.append(' ')
332 filter_list.append(filter_id.condition)
334 if len(from_list) == 1 and filter_ids:
335 from_list.append(' ')
336 ret_str = "\n where \n".join(from_list)
338 ret_str = ",\n".join(from_list)
341 where_list = list(set(where_list))
342 ret_str += "\n where \n"+" and\n".join(where_list)
343 ret_str = ret_str.strip()
345 ret_str += "\n".join(filter_list)
346 if ret_str.endswith('and'):
347 ret_str = ret_str[0:len(ret_str)-3]
348 if ret_str.endswith('or'):
349 ret_str = ret_str[0:len(ret_str)-2]
350 ret_str = ret_str.strip()
352 return ret_str % {'uid': uid}
354 def _id_get(self, cr, uid, id, context):
358 return self.model_set_id and 'min('+self.model_set_id+'.id)'
360 def _sql_query_get(self, cr, uid, ids, prop, unknow_none, context, where_plus=[], limit=None, offset=None):
362 Get sql query which return on sql_query field.
363 @return: Dictionary of sql query.
366 for obj in self.browse(cr, uid, ids):
370 for f in obj.field_ids:
371 # Allowing to use count(*)
372 if not f.field_id.model and f.group_method == 'count':
373 fields.insert(0, ('count(*) as column_count'))
375 t = self.pool.get(f.field_id.model_id.model)._table
376 if f.group_method == 'group':
377 fields.append('\t'+t+'.'+f.field_id.name+' as field'+str(i))
378 groupby.append(t+'.'+f.field_id.name)
380 fields.append('\t'+f.group_method+'('+t+'.'+f.field_id.name+')'+' as field'+str(i))
383 models = self._path_get(cr, uid, obj.model_ids, obj.filter_ids)
384 check = self._id_get(cr, uid, ids[0], context)
386 fields.insert(0, (check + ' as id'))
389 result[obj.id] = """select
393 """ % (',\n'.join(fields), models)
395 result[obj.id] += "group by\n\t"+', '.join(groupby)
397 result[obj.id] += "\nhaving \n\t"+"\n\t and ".join(where_plus)
399 result[obj.id] += " limit "+str(limit)
401 result[obj.id] += " offset "+str(offset)
403 result[obj.id] = False
407 'name': fields.char('Report Name', size=64, required=True),
408 'type': fields.selection([('list', 'Rows And Columns Report'), ], 'Report Type', required=True), #('sum','Summation Report')
409 'active': fields.boolean('Active', help="If the active field is set to true, it will allow you to hide the report without removing it."),
410 'view_type1': fields.selection([('form', 'Form'),
413 ('calendar', 'Calendar')], 'First View', required=True),
414 'view_type2': fields.selection([('', '/'),
418 ('calendar', 'Calendar')], 'Second View'),
419 'view_type3': fields.selection([('', '/'),
423 ('calendar', 'Calendar')], 'Third View'),
424 'view_graph_type': fields.selection([('pie', 'Pie Chart'),
425 ('bar', 'Bar Chart')], 'Graph Type', required=True),
426 'view_graph_orientation': fields.selection([('horz', 'Horizontal'),
427 ('vert', 'Vertical')], 'Graph Orientation', required=True),
428 'model_ids': fields.many2many('ir.model', 'base_report_creator_report_model_rel', 'report_id', 'model_id', 'Reported Objects'),
429 'field_ids': fields.one2many('base_report_creator.report.fields', 'report_id', 'Fields to Display'),
430 'filter_ids': fields.one2many('base_report_creator.report.filter', 'report_id', 'Filters'),
431 'sql_query': fields.function(_sql_query_get, method=True, type="text", string='SQL Query', store=True),
432 'group_ids': fields.many2many('res.groups', 'base_report_creator_group_rel', 'report_id', 'group_id', 'Authorized Groups'),
433 'menu_id': fields.many2one('ir.ui.menu', "Menu", readonly=True),
436 'type': lambda *args: 'list',
437 'active': lambda *args: True,
438 'view_type1': lambda *args: 'tree',
439 'view_type2': lambda *args: 'graph',
440 'view_graph_type': lambda *args: 'bar',
441 'view_graph_orientation': lambda *args: 'horz',
444 def open_report(self, cr, uid, ids, context=None):
446 This Function opens base creator report view
447 @param self: The object pointer
448 @param cr: the current row, from the database cursor,
449 @param uid: the current user’s ID for security checks,
450 @param ids: List of report open's IDs
451 @param context: A standard dictionary for contextual values
452 @return : Dictionary value for base creator report form
457 rep = self.browse(cr, uid, ids, context=context)
462 view_mode = rep.view_type1
464 view_mode += ',' + rep.view_type2
466 view_mode += ',' + rep.view_type3
470 'view_mode': view_mode,
471 'res_model': 'base_report_creator.report',
472 'type': 'ir.actions.act_window',
473 'context': "{'report_id':%d}" % (rep.id,),
479 def _function_field(self, cr, uid, ids):
481 constraints function which specify that
482 not display field which are not stored in Database.
483 @param cr: the current row, from the database cursor,
484 @param uid: the current user’s ID for security checks,
485 @param ids: List of Report creator's id.
486 @return: True if display field which are stored in database.
487 or false if display field which are not store in dtabase.
489 this_objs = self.browse(cr, uid, ids)
490 for obj in this_objs:
491 for fld in obj.field_ids:
492 # Allowing to use count(*)
493 if not fld.field_id.model and fld.group_method == 'count':
495 model_column = self.pool.get(fld.field_id.model)._columns[fld.field_id.name]
496 if (isinstance(model_column, fields.function) or isinstance(model_column, fields.related)) and not model_column.store:
500 def _aggregation_error(self, cr, uid, ids):
502 constraints function which specify that
503 aggregate function to the non calculated field..
504 @param cr: the current row, from the database cursor,
505 @param uid: the current user’s ID for security checks,
506 @param ids: List of Report creator's id.
507 @return: True if model colume type is in integer or float.
508 or false model colume type is not in integer or float.
511 aggregate_columns = ('integer', 'float')
512 apply_functions = ('sum', 'min', 'max', 'avg', 'count')
513 this_objs = self.browse(cr, uid, ids)
514 for obj in this_objs:
515 for fld in obj.field_ids:
516 # Allowing to use count(*)
517 if not fld.field_id.model and fld.group_method == 'count':
519 model_column = self.pool.get(fld.field_id.model)._columns[fld.field_id.name]
520 if model_column._type not in aggregate_columns and fld.group_method in apply_functions:
524 def _calander_view_error(self, cr, uid, ids):
526 this_objs = self.browse(cr, uid, ids)
527 for obj in this_objs:
528 if obj.view_type1 == 'calendar' or obj.view_type2 == 'calendar' or obj.view_type3 == 'calendar':
529 for fld in obj.field_ids:
530 # Allowing to use count(*)
531 if not fld.field_id.model and fld.group_method == 'count':
533 model_column = self.pool.get(fld.field_id.model)._columns[fld.field_id.name]
534 if fld.calendar_mode in ('date_start','date_end') and model_column._type not in ('date','datetime'):
536 elif fld.calendar_mode=='date_delay' and model_column._type not in ('int','float'):
539 required_types.append(fld.calendar_mode)
540 if 'date_start' not in required_types:
546 (_function_field, 'You can not display field which are not stored in Database.', ['field_ids']),
547 (_aggregation_error, 'You can apply aggregate function to the non calculated field.', ['field_ids']),
548 (_calander_view_error, "You must have to give calendar view's color,start date and delay.", ['field_ids']),
553 class report_creator_field(osv.osv):
557 _name = "base_report_creator.report.fields"
558 _description = "Display Fields"
559 _rec_name = 'field_id'
560 _order = "sequence,id"
562 'sequence': fields.integer('Sequence', help="Gives the sequence order when displaying a list of fields."),
563 'field_id': fields.many2one('ir.model.fields', 'Field'),
564 'report_id': fields.many2one('base_report_creator.report', 'Report', on_delete='cascade'),
565 'group_method': fields.selection([('group', 'Grouped'),
570 ('avg', 'Average')], 'Grouping Method', required=True),
571 'graph_mode': fields.selection([('', '/'),
573 ('y', 'Y Axis')], 'Graph Mode'),
574 'calendar_mode': fields.selection([('', '/'),
575 ('date_start', 'Starting Date'),
576 ('date_end', 'Ending Date'), ('date_delay', 'Delay'), ('date_stop', 'End Date'), ('color', 'Unique Colors')], 'Calendar Mode'),
579 'group_method': lambda *args: 'group',
580 'graph_mode': lambda *args: '',
582 report_creator_field()
585 class report_creator_filter(osv.osv):
587 Report Creator Filter
589 _name = "base_report_creator.report.filter"
590 _description = "Report Filters"
592 'name': fields.char('Filter Name', size=64, required=True),
593 'expression': fields.text('Value', required=True, help='Provide an expression for the field based on which you want to filter the records.\n e.g. res_partner.id=3'),
594 'report_id': fields.many2one('base_report_creator.report', 'Report', on_delete='cascade'),
595 'condition': fields.selection([('and', 'AND'),
596 ('or', 'OR')], 'Condition')
599 'condition': lambda *args: 'and',
601 report_creator_filter()
602 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: