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 ##############################################################################
25 from osv import fields,osv,orm
26 from tools.translate import _
28 #class ir_model_fields(osv.osv):
29 # _inherit = 'ir.model.fields'
30 # def _get_models(self, cr, uid, model_name, level=1):
33 # result = [model_name]
35 # for field,data in self.pool.get(model_name).fields_get(cr, uid).items():
36 # if data.get('relation', False):
37 # result += self._get_models(cr, uid, data['relation'], level-1)
39 # def search(self, cr, uid, args, offset=0, limit=None, order=None, context=None):
40 # if context and ('model_id' in context):
41 # model_name = self.pool.get("ir.model").browse(cr, uid, context['model_id'], context).model
42 # models = self._get_models(cr, uid, model_name, context.get('model_level',2))
43 # models = map(lambda x: self.pool.get('ir.model').search(cr, uid, [('model','=',x)])[0], models)
44 # args.append(('model_id','in',models))
46 # return super(ir_model_fields, self).search(cr, uid, args, offset, limit, order, context)
50 class report_creator(osv.osv):
51 _name = "base_report_creator.report"
52 _description = "Report"
55 # Should request only used fields
57 def export_data(self, cr, uid, ids, fields_to_export, context=None):
58 data_l = self.read(cr, uid, ids, ['sql_query'], context)
62 for key,value in i.items():
63 if key not in fields_to_export:
65 if isinstance(value,tuple):
66 datas.append(str(value[1]))
68 datas.append(str(value))
69 final_datas += [datas]
70 return {'datas':final_datas}
72 def fields_get(self, cr, user, fields=None, context=None):
73 if (not context) or 'report_id' not in context:
74 return super(report_creator, self).fields_get(cr, user, fields, context)
75 report = self.browse(cr, user, context['report_id'])
77 for model in report.model_ids:
78 models[model.model] = self.pool.get(model.model).fields_get(cr, user, context=context)
81 for f in report.field_ids:
83 fields['field'+str(i)] = models[f.field_id.model][f.field_id.name]
86 fields['column_count'] = {'readonly': True, 'type': 'integer', 'string': 'Count', 'size': 64, 'name': 'column_count'}
91 # Should Call self.fields_get !
93 def fields_view_get(self, cr, user, view_id=None, view_type='form', context=None, toolbar=False, submenu=False):
94 if (not context) or 'report_id' not in context:
95 return super(report_creator, self).fields_view_get(cr, user, view_id, view_type, context, toolbar=toolbar, submenu=submenu)
96 report = self.browse(cr, user, context['report_id'])
98 for model in report.model_ids:
99 models[model.model] = self.pool.get(model.model).fields_get(cr, user, context=context)
102 for f in report.field_ids:
104 fields['field'+str(i)] = models[f.field_id.model][f.field_id.name]
107 fields['column_count'] = {'readonly': True, 'type': 'integer', 'string': 'Count', 'size': 64, 'name': 'column_count'}
109 arch = '<?xml version="1.0" encoding="utf-8"?>\n'
110 if view_type=='graph':
111 arch +='<graph string="%s" type="%s" orientation="%s">' % (report.name, report.view_graph_type,report.view_graph_orientation)
112 for val in ('x','y'):
114 for f in report.field_ids:
115 if f.graph_mode==val:
117 arch += '<field name="%s" select="1"/>' % ('field'+str(i),)
120 arch += '<field name="%s" select="1"/>' % ('column_count',)
122 elif view_type=='calendar':
123 required_types = ['date_start','date_delay','color']
124 set_dict = {'view_type':view_type,'string':report.name}
127 for f in report.field_ids:
128 if f.calendar_mode and f.calendar_mode in required_types:
130 field_cal = 'field'+str(i)
133 field_cal = 'column_count'
134 set_dict[f.calendar_mode] = field_cal
135 del required_types[required_types.index(f.calendar_mode)]
139 temp_list.append('''<field name="%(name)s" select="1"/>''' % {'name':'field'+str(i)})
142 temp_list.append('''<field name="%(name)s" select="1"/>''' % {'name':'column_count'})
144 arch += '''<%(view_type)s string="%(string)s" date_start="%(date_start)s" ''' %set_dict
145 if set_dict.get('date_delay',False):
146 arch +=''' date_delay="%(date_delay)s" '''%set_dict
148 if set_dict.get('date_stop',False):
149 arch +=''' date_stop="%(date_stop)s" '''%set_dict
151 if set_dict.get('color',False):
152 arch +=''' color="%(color)s"'''%set_dict
154 arch += ''.join(temp_list)
156 arch += '<%s string="%s">\n' % (view_type, report.name)
158 for f in report.field_ids:
160 arch += '<field name="%s" select="1"/>' % ('field'+str(i),)
163 arch += '<field name="%s" select="1"/>' % ('column_count',)
164 arch += '</%s>' % (view_type,)
169 result['toolbar'] = {
176 def read(self, cr, user, ids, fields=None, context=None, load='_classic_read'):
177 if (not context) or 'report_id' not in context:
178 return super(report_creator, self).read(cr, user, ids, fields, context, load)
181 if self.model_set_id:
182 wp = [self._id_get(cr, user, context['report_id'], context)+(' in (%s)' % (','.join(map(lambda x: "'"+str(x)+"'",ids))))]
183 report = self._sql_query_get(cr, user, [context['report_id']], 'sql_query', None, ctx, where_plus = wp)
184 sql_query = report[context['report_id']]
185 cr.execute(sql_query)
186 res = cr.dictfetchall()
187 fields_get = self.fields_get(cr,user,None,context)
191 field_dict = fields_get.get(k)
192 field_type = field_dict and field_dict.get('type',False) or False
193 if field_type and field_type == 'many2one':
196 related_name = self.pool.get(field_dict.get('relation')).name_get(cr,user,[r[k]],context)[0]
200 def search(self, cr, user, args, offset=0, limit=None, order=None, context=None, count=False):
201 if (not context) or 'report_id' not in context:
202 return super(report_creator, self).search(cr, user, args, offset, limit, order, context, count)
203 report = self.browse(cr, user, context['report_id'])
206 for f in report.field_ids:
208 fields['field'+str(i)] = (f.field_id.model, f.field_id.name)
211 fields['column_count'] = (False, 'Count')
216 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)
220 newargs += [("count(*) " + a[1] +" " + str(a[2]))]
223 report = self._sql_query_get(cr, user, [context['report_id']], 'sql_query', None, ctx, where_plus=newargs, limit=limit, offset=offset)
224 query = report[context['report_id']]
225 cr.execute(query, newargs2)
226 result = cr.fetchall()
227 return map(lambda x: x[0], result)
229 def _path_get(self,cr, uid, models, filter_ids=[]):
230 # ret_str = """ sale_order_line
231 # left join sale_order on (sale_order_line.order_id=sale_order.id)
232 # left join res_partner on (res_partner.id=sale_order.partner_id)"""
234 # for filter_id in filter_ids:
235 # where_list.append(filter_id.expression)
237 # ret_str+="\nwhere\n\t"+" and\n\t".join(where_list)
238 self.model_set_id = False
244 model_dict[model.model] = self.pool.get(model.model)._table
246 model_list = model_dict.keys()
247 reference_model_dict = {}
248 for model in model_dict:
249 from_list.append(model_dict.get(model))
250 rest_list = model_dict.keys()
251 rest_list.remove(model)
252 model_pool = self.pool.get(model)
253 fields_get = model_pool.fields_get(cr,uid)
256 def _get_inherit_fields(obj):
257 pool_model = self.pool.get(obj)
258 #Adding the columns of the model itself
259 model_columns.update(pool_model._columns)
260 #Adding the columns of its _inherits
261 for record in pool_model._inherits.keys():
262 _get_inherit_fields(record)
264 _get_inherit_fields(model)
266 fields_filter = dict(filter(lambda x:x[1].get('relation',False)
267 and x[1].get('relation') in rest_list
268 and x[1].get('type')=='many2one'
269 and not (isinstance(model_columns[x[0]],fields.function) or isinstance(model_columns[x[0]],fields.related)), fields_get.items()))
271 model in model_list and model_list.remove(model)
272 model_count = reference_model_dict.get(model,False)
274 reference_model_dict[model] = model_count +1
276 reference_model_dict[model] = 1
277 for k,v in fields_filter.items():
278 v.get('relation') in model_list and model_list.remove(v.get('relation'))
279 relation_count = reference_model_dict.get(v.get('relation'),False)
281 reference_model_dict[v.get('relation')] = relation_count+1
283 reference_model_dict[v.get('relation')]=1
284 if k in self.pool.get(model)._columns:
285 str_where = model_dict.get(model)+"."+ k + "=" + model_dict.get(v.get('relation'))+'.id'
286 where_list.append(str_where)
288 if reference_model_dict:
289 self.model_set_id = model_dict.get(reference_model_dict.keys()[reference_model_dict.values().index(min(reference_model_dict.values()))])
290 if model_list and not len(model_dict.keys()) == 1:
291 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))
293 if filter_ids and where_list<>[]:
294 filter_list.append(' and ')
295 filter_list.append(' ')
297 for filter_id in filter_ids:
298 filter_list.append(filter_id.expression)
299 filter_list.append(' ')
300 filter_list.append(filter_id.condition)
302 if len(from_list) == 1 and filter_ids:
303 from_list.append(' ')
304 ret_str = "\n where \n".join(from_list)
306 ret_str = ",\n".join(from_list)
310 where_list = list(set(where_list))
311 ret_str+="\n where \n"+" and\n".join(where_list)
312 ret_str = ret_str.strip()
314 ret_str +="\n".join(filter_list)
315 if ret_str.endswith('and'):
316 ret_str = ret_str[0:len(ret_str)-3]
317 if ret_str.endswith('or'):
318 ret_str = ret_str[0:len(ret_str)-2]
319 ret_str = ret_str.strip()
320 return ret_str % {'uid' : uid}
322 def _id_get(self, cr, uid, id, context):
323 # return 'min(sale_order_line.id)'
324 return self.model_set_id and 'min('+self.model_set_id+'.id)'
326 def _sql_query_get(self, cr, uid, ids, prop, unknow_none, context, where_plus=[], limit=None, offset=None):
328 for obj in self.browse(cr, uid, ids):
332 for f in obj.field_ids:
333 # Allowing to use count(*)
334 if not f.field_id.model and f.group_method == 'count':
335 fields.insert(0,('count(*) as column_count'))
337 t = self.pool.get(f.field_id.model_id.model)._table
338 if f.group_method == 'group':
339 fields.append('\t'+t+'.'+f.field_id.name+' as field'+str(i))
341 fields.append('\t'+f.group_method+'('+t+'.'+f.field_id.name+')'+' as field'+str(i))
342 groupby.append(t+'.'+f.field_id.name)
344 models = self._path_get(cr, uid, obj.model_ids, obj.filter_ids)
345 check = self._id_get(cr, uid, ids[0], context)
347 fields.insert(0,(check+' as id'))
350 result[obj.id] = """select
354 """ % (',\n'.join(fields), models)
356 result[obj.id] += "group by\n\t"+', '.join(groupby)
358 result[obj.id] += "\nhaving \n\t"+"\n\t and ".join(where_plus)
360 result[obj.id] += " limit "+str(limit)
362 result[obj.id] += " offset "+str(offset)
364 result[obj.id] = False
368 'name': fields.char('Report Name',size=64, required=True),
369 'type': fields.selection([('list','Rows And Columns Report'),], 'Report Type',required=True),#('sum','Summation Report')
370 'active': fields.boolean('Active', help="If the active field is set to true, it will allow you to hide the report without removing it."),
371 'view_type1': fields.selection([('form','Form'),('tree','Tree'),('graph','Graph'),('calendar','Calendar')], 'First View', required=True),
372 'view_type2': fields.selection([('','/'),('form','Form'),('tree','Tree'),('graph','Graph'),('calendar','Calendar')], 'Second View'),
373 'view_type3': fields.selection([('','/'),('form','Form'),('tree','Tree'),('graph','Graph'),('calendar','Calendar')], 'Third View'),
374 'view_graph_type': fields.selection([('pie','Pie Chart'),('bar','Bar Chart')], 'Graph Type', required=True),
375 'view_graph_orientation': fields.selection([('horz','Horizontal'),('vert','Vertical')], 'Graph Orientation', required=True),
376 'model_ids': fields.many2many('ir.model', 'base_report_creator_report_model_rel', 'report_id','model_id', 'Reported Objects'),
377 'field_ids': fields.one2many('base_report_creator.report.fields', 'report_id', 'Fields to Display'),
378 'filter_ids': fields.one2many('base_report_creator.report.filter', 'report_id', 'Filters'),
379 'state': fields.selection([
382 'State', required=True,
383 help=' * The \'Draft\' state is used when a user is encoding a new and unconfirmed custom report. \
384 \n* The \'Valid\' state is used when user validates the custom report.'),
385 'sql_query': fields.function(_sql_query_get, method=True, type="text", string='SQL Query', store=True),
386 'group_ids': fields.many2many('res.groups', 'base_report_creator_group_rel','report_id','group_id','Authorized Groups'),
389 'type': lambda *args: 'list',
390 'state': lambda *args: 'draft',
391 'active': lambda *args: True,
392 'view_type1': lambda *args: 'tree',
393 'view_type2': lambda *args: 'graph',
394 'view_graph_type': lambda *args: 'bar',
395 'view_graph_orientation': lambda *args: 'horz',
397 def _function_field(self, cr, uid, ids):
398 this_objs = self.browse(cr, uid, ids)
399 for obj in this_objs:
400 for fld in obj.field_ids:
401 # Allowing to use count(*)
402 if not fld.field_id.model and fld.group_method == 'count':
404 model_column = self.pool.get(fld.field_id.model)._columns[fld.field_id.name]
405 if (isinstance(model_column,fields.function) or isinstance(model_column,fields.related)) and not model_column.store:
409 def _aggregation_error(self, cr, uid, ids):
410 aggregate_columns = ('integer','float')
411 apply_functions = ('sum','min','max','avg','count')
412 this_objs = self.browse(cr, uid, ids)
413 for obj in this_objs:
414 for fld in obj.field_ids:
415 # Allowing to use count(*)
416 if not fld.field_id.model and fld.group_method == 'count':
418 model_column = self.pool.get(fld.field_id.model)._columns[fld.field_id.name]
419 if model_column._type not in aggregate_columns and fld.group_method in apply_functions:
423 def _calander_view_error(self, cr, uid, ids):
424 # required_types = ['date_start','date_delay','color']
426 this_objs = self.browse(cr, uid, ids)
427 for obj in this_objs:
428 if obj.view_type1=='calendar' or obj.view_type2=='calendar' or obj.view_type3=='calendar':
429 for fld in obj.field_ids:
430 # Allowing to use count(*)
431 if not fld.field_id.model and fld.group_method == 'count':
433 model_column = self.pool.get(fld.field_id.model)._columns[fld.field_id.name]
434 if fld.calendar_mode in ('date_start','date_end') and model_column._type not in ('date','datetime'):
436 elif fld.calendar_mode=='date_delay' and model_column._type not in ('int','float'):
439 required_types.append(fld.calendar_mode)
440 if 'date_start' not in required_types:
445 (_function_field, 'You can not display field which are not stored in Database.', ['field_ids']),
446 (_aggregation_error, 'You can apply aggregate function to the non calculated field.', ['field_ids']),
447 (_calander_view_error, "You must have to give calendar view's color,start date and delay.", ['field_ids']),
451 class report_creator_field(osv.osv):
452 _name = "base_report_creator.report.fields"
453 _description = "Display Fields"
454 _rec_name = 'field_id'
455 _order = "sequence,id"
457 'sequence': fields.integer('Sequence', help="Gives the sequence order when displaying a list of fields."),
458 'field_id': fields.many2one('ir.model.fields', 'Field'),
459 'report_id': fields.many2one('base_report_creator.report','Report', on_delete='cascade'),
460 'group_method': fields.selection([('group','Grouped'),('sum','Sum'),('min','Minimum'),('count','Count'),('max','Maximum'),('avg','Average')], 'Grouping Method', required=True),
461 'graph_mode': fields.selection([('','/'),('x','X Axis'),('y','Y Axis')], 'Graph Mode'),
462 'calendar_mode': fields.selection([('','/'),('date_start','Starting Date'),('date_end','Ending Date'),('date_delay','Delay'),('date_stop','End Date'),('color','Unique Colors')], 'Calendar Mode'),
465 'group_method': lambda *args: 'group',
466 'graph_mode': lambda *args: '',
468 report_creator_field()
470 class report_creator_filter(osv.osv):
471 _name = "base_report_creator.report.filter"
472 _description = "Report Filters"
474 'name': fields.char('Filter Name',size=64, required=True),
475 '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'),
476 'report_id': fields.many2one('base_report_creator.report','Report', on_delete='cascade'),
477 'condition' : fields.selection([('and','AND'),('or','OR')], 'Condition')
480 'condition': lambda *args: 'and',
482 report_creator_filter()
483 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: