Django रिपोर्ट सर्वर

 class EnvirementManager(object): u'''    , render    ,    .. ''' def __init__(self, **kwargs): self._env = kwargs def render(self, text, **dict): u'''     ''' return render_string(text, self._dict_mix(dict)) def render_template_compiled(self, template, **dict): u'''     ''' return template.render(Context(self._dict_mix(dict))) def render_template(self, path, **dict): u'''  ''' return render_to_string(path, self._dict_mix(dict), context_instance=RequestContext(self._env['request'])) def render_sql(self, sql, **dict): u'''             ''' sql_rendered = self.render(sql, **dict) binds = [] for bind_token in re.findall(r'\[{2}.+\]{2}', sql_rendered): env_path = bind_token[2:-2] binds.append(attribute_by_name(self._env, env_path)) sql_rendered = sql_rendered.replace(bind_token, u'%s') return (sql_rendered, binds) def _dict_mix(self, dict): if dict: e = self._env.copy() e.update(dict) else: e = self._env return e def get(self, name, value=None): return self._env.get(name, value) def __getitem__(self, name): return self._env[name] def __setitem__(self, name, value): self._env[name] = value 

 class DatasetManager(object): u'''    ''' def __init__(self, request, dataset, env): u'''  ''' self._request = request self._dataset = dataset self._env = env self._cache = [] self._xml = None if self._dataset.xml_settings: self._xml = etree.fromstring(self._env.render(self._dataset.xml_settings)) def get_data(self): u'''        ''' if not self._cache: self._cache = [[], []] (sql, binds) = self._env.render_sql(self._dataset.sql) cursor = self._modify_cursor(connections['reports'].cursor()) cursor.execute(sql, binds) #    xml_columns = {} if self._xml not in (None, ''): for xml_column in self._xml.xpath('/xml/columns/column'): attrs = xml_column.attrib xml_columns[force_unicode(attrs['name'])] = force_unicode(attrs['alias']) #   (  ,  ) #      ,     for field in cursor.description: name_unicode = force_unicode(field.name) self._cache[0].append(xml_columns[name_unicode] if name_unicode in xml_columns else name_unicode) self._cache[1] = cursor.fetchall() return self._cache def __getitem__(self, name): u'''         ''' if name == 'sql': return self._env.render(self._dataset.sql) elif name == 'render': (sql, binds) = self._env.render_sql(self._dataset.sql) return {'sql': sql, 'binds': binds} elif name == 'data': (fields, data) = self.get_data() return [dict(zip(fields, row)) for row in data] def _modify_cursor(self, cursor): u'''    (      ), ,  Oracle    ,  number_as_string = True (    backends django) ''' return cursor 

 class FilterManager(object): u'''     ''' def __init__(self, request, filter_obj, env): u'''  ''' self._request = request self._filter_obj = filter_obj self._env = env self._form_instance = None self._changed_data = {} #    ,   POST- #    ,     ,    self._changed_data if self._request.method == 'POST' and int(self._request.POST['filter_id']) == self._filter_obj.id: self._form_instance = self._get_form_class()(self._request.POST) if self._form_instance.is_valid(): data = self._form_instance.cleaned_data for key, value in data.items(): if self._env['f'].get(key) != value: self._changed_data[key] = value def get_changed_data(self): return self._changed_data def get_form(self): u'''     ''' try: #       , ( ) #       if self._form_instance is None: self._form_instance = self._get_form_class()(initial=self._env['f']) html = self._env.render('{% load custom_filters %}' + self._filter_obj.html_layout, form=self._form_instance) return self._env.render_template('reports_filter.html', form_html=html, filter=self._filter_obj) except Exception as e: return e def __call__(self): u'''     ''' try: return self.get_form() except Exception as e: return e def _get_form_class(self): u'''       ''' form_attrs = {} filter_widgets = (DateField, ChoiceField, MultipleChoiceField, BooleanField, CharField, CharField, DateRangeField) for item in self._filter_obj.form_items.all(): kwargs = {'label': item.title, 'required': False} if item.xml_settings: xml = xml = etree.fromstring(self._env.render(item.xml_settings)) else: xml = None if item.widget_type == 0: kwargs['widget'] = forms.DateInput(attrs={'class': 'date'}) elif item.widget_type in (1, 2): choices = [] for option in xml.xpath('/xml/options/option'): choices.append((option.attrib['id'], option.attrib['value'])) sql = xml.xpath('/xml/sql') if sql: curs = connections['reports'].cursor().execute(sql[0].text) for row in curs.fetchall(): choices.append((row[0], row[1])) kwargs['choices'] = choices elif item.widget_type == 4: kwargs['max_length'] = 50 elif item.widget_type == 5: default = xml.xpath('/xml/value') kwargs['widget'] = forms.HiddenInput(attrs={'value': default[0].text}) form_attrs[item.key] = filter_widgets[item.widget_type](**kwargs) filter_form = type(str(self._filter_obj.title), (forms.Form,), form_attrs) return filter_form 

 class WidgetTableManager(object): u'''      ''' def __init__(self, request, widget_obj, dataset, env): u'''  ''' self._request = request self._widget_obj = widget_obj self._dataset = dataset self._env = env self._xml = None if widget_obj.xml_settings: self._xml = etree.fromstring(self._env.render(widget_obj.xml_settings).replace('[[', '{{').replace(']]', '}}')) def get_html(self): u'''  html-  ''' (fields, data) = self._dataset.get_data() field_settings = {} table_settings = {} if self._xml is not None: table_settings_node = xml_node(self._xml, '/xml/table') if table_settings_node is not None: table_settings = table_settings_node.attrib for xml in self._xml.xpath('/xml/fields/field'): xml_attributes = dict(xml.attrib) field_name = xml_attributes['name'] if 'lnk' in xml_attributes: xml_attributes['tpl_lnk'] = Template(force_unicode(xml_attributes['lnk'])) if 'cell_attributes' in xml_attributes: xml_attributes['tpl_cell_attributes'] = Template(force_unicode(xml_attributes['cell_attributes'])) field_settings[field_name] = xml_attributes #     fields_visible = [] for index, field_name in enumerate(fields): settings = field_settings.get(field_name, {}) if 'display' in settings and settings['display'] == '0': continue fields_visible.append((index, field_name, settings)) #       rows = [] for row in data: row_dict = dict(zip(fields, row)) row_settings = {} #    if 'field_row_style' in table_settings: row_settings['row_style'] = row_dict[table_settings['field_row_style']] if 'field_row_attributes' in table_settings: row_settings['row_attributes'] = row_dict[table_settings['field_row_attributes']] #        fields_set = [] for index, field_name, settings in fields_visible: field = {'name': field_name, 'value': row[index]} #         if 'tpl_lnk' in settings and ('lnk_enable_field' not in settings or row_dict[settings['lnk_enable_field']] not in (0, '0', '', None)): field['lnk'] = self._env.render_template_compiled(settings['tpl_lnk'], row=row_dict) #   if 'tpl_cell_attributes' in settings: field['cell_attributes'] = settings['tpl_cell_attributes'].render(Context(row_dict)) field['settings'] = settings fields_set.append(field) rows.append({'settings': row_settings, 'fields': fields_set}) return render_to_string('reports_widget_table.html', {'fields': fields_visible, 'rows': rows, 'widget_obj': self._widget_obj}) def __call__(self): u'''  ,   ''' try: return self.get_html() except Exception as e: return u'   %s: "%s"' % (self._widget_obj.title, e) 

 class WidgetChartManager(object): u'''   ''' def __init__(self, request, chart_obj, dataset, env): u'''  ''' self._request = request self._chart_obj = chart_obj self._dataset = dataset self._env = env self._xml = etree.fromstring(self._env.render(self._chart_obj.xml_settings)) print 1 def __call__(self): u'''     ''' print 2 try: return self.get_chart() except Exception as e: print unicode(e) return unicode(e) def get_chart(self): u''' html     ''' (fields, data) = self._dataset.get_data() return self._env.render_template('reports_widget_chart.html', settings=xml_to_dict(xml_node(self._xml, '/xml')), data=json.dumps([dict(zip(fields, row)) for row in data], cls=JSONEncoder), chart_obj=self._chart_obj, ) 

 class WidgetTableManager(object): u'''      ''' def __init__(self, request, widget_obj, dataset, env): u'''  ''' self._request = request self._widget_obj = widget_obj self._dataset = dataset self._env = env self._xml = None if widget_obj.xml_settings: self._xml = etree.fromstring(self._env.render(widget_obj.xml_settings).replace('[[', '{{').replace(']]', '}}')) def get_html(self): u'''  html-  ''' (fields, data) = self._dataset.get_data() field_settings = {} table_settings = {} if self._xml is not None: table_settings_node = xml_node(self._xml, '/xml/table') if table_settings_node is not None: table_settings = table_settings_node.attrib for xml in self._xml.xpath('/xml/fields/field'): xml_attributes = dict(xml.attrib) field_name = xml_attributes['name'] if 'lnk' in xml_attributes: xml_attributes['tpl_lnk'] = Template(force_unicode(xml_attributes['lnk'])) if 'cell_attributes' in xml_attributes: xml_attributes['tpl_cell_attributes'] = Template(force_unicode(xml_attributes['cell_attributes'])) field_settings[field_name] = xml_attributes #     fields_visible = [] for index, field_name in enumerate(fields): settings = field_settings.get(field_name, {}) if 'display' in settings and settings['display'] == '0': continue fields_visible.append((index, field_name, settings)) #       rows = [] for row in data: row_dict = dict(zip(fields, row)) row_settings = {} #    if 'field_row_style' in table_settings: row_settings['row_style'] = row_dict[table_settings['field_row_style']] if 'field_row_attributes' in table_settings: row_settings['row_attributes'] = row_dict[table_settings['field_row_attributes']] #        fields_set = [] for index, field_name, settings in fields_visible: field = {'name': field_name, 'value': row[index]} #         if 'tpl_lnk' in settings and ('lnk_enable_field' not in settings or row_dict[settings['lnk_enable_field']] not in (0, '0', '', None)): field['lnk'] = self._env.render_template_compiled(settings['tpl_lnk'], row=row_dict) #   if 'tpl_cell_attributes' in settings: field['cell_attributes'] = settings['tpl_cell_attributes'].render(Context(row_dict)) field['settings'] = settings fields_set.append(field) rows.append({'settings': row_settings, 'fields': fields_set}) return render_to_string('reports_widget_table.html', {'fields': fields_visible, 'rows': rows, 'widget_obj': self._widget_obj}) def __call__(self): u'''  ,   ''' try: return self.get_html() except Exception as e: return u'   %s: "%s"' % (self._widget_obj.title, e) 

 class ReportManager(object): u'''   ''' def __init__(self, request, report): self._report = report self._request = request self._user = request.user self._forms = {} self._env = EnvirementManager(request=request, user_params=self._get_user_params(), forms={}, f={}) self._datasets = {} self._widgets_table = {} self._widgets_chart = {} self._load_stored_filter_values() #   for filter_obj in self._report.forms.only('title', 'html_layout'): filter_manager = FilterManager(self._request, filter_obj, self._env) self._save_stored_filter_values(filter_manager.get_changed_data()) self._forms[filter_obj.title] = filter_manager self._env['forms'] = self._forms #    for ds in self._report.datasets.only('sql', 'title', 'xml_settings'): self._datasets[ds.title] = DatasetManager(request, ds, self._env) self._env['datasets'] = self._datasets #  - for widget_obj in self._report.widgets_table.only('title', 'dataset', 'table_header', 'xml_settings'): self._widgets_table[widget_obj.title] = WidgetTableManager(self._request, widget_obj, self._datasets[widget_obj.dataset.title], self._env) self._env['tables'] = self._widgets_table #  -  for chart_obj in self._report.widgets_chart.only('title', 'dataset', 'xml_settings'): self._widgets_chart[chart_obj.title] = WidgetChartManager(self._request, chart_obj, self._datasets[chart_obj.dataset.title], self._env) self._env['charts'] = self._widgets_chart def get_request(self): u'''   ''' response_type = self._request.REQUEST.get('response_type', 'html') if response_type == 'xls': return self._get_request_xls(self._request.REQUEST['xls']) elif response_type == 'template': return self._get_request_template(self._request.REQUEST['template']) else: return self._get_request_html() def _get_request_html(self): u'''     html     ''' context = {'favorite_reports': self._user.reports_favorited.all()} context['report_body'] = self._env.render(self._report.html_layout) context['breadcrumbs'] = (('', reverse('reports_home')), (self._report.title, None)) context['filter_presets'] = self._report.filter_presets.filter(user=self._user) context['report'] = self._report return render(self._request, 'reports_report.html', context) def _get_request_template(self): u'''     html     ''' # TODO raise NotImplementedError(u' ') def _get_request_xls(self, dataset_title): u"""     xls """ dataset = self._datasets[dataset_title] (columns, data) = dataset.get_data() w = Workbook(optimized_write=True) sheet = w.create_sheet(0) sheet.append(columns) rows_in_sheet = 0 for row in data: if rows_in_sheet > 1000000: sheet = w.create_sheet() sheet.append(columns) rows_in_sheet = 0 sheet.append(row) rows_in_sheet += 1 try: tmpFileName = os.tempnam() w.save(tmpFileName) fh = open(tmpFileName, 'rb') resp = HttpResponse(fh.read(), 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') finally: if fh: fh.close() os.unlink(tmpFileName) resp['Content-Disposition'] = 'attachment; filename=".xlsx"' return resp def _get_request_document_template(self, template_title): u"""  ,     """ pass def _save_stored_filter_values(self, values): u"""        _env    html,      ,             """ for key, value in values.items(): self._env['f'][key] = value if values.get('response_type', 'html') == 'html': (store_item, is_created) = models.WidgetFormStorage.objects.get_or_create(user=self._user, report=self._report, key=key) store_item.value = pickle.dumps(value) store_item.save() def _load_stored_filter_values(self): u"""   ,      """ for item in self._report.form_storage.all(): self._env['f'][item.key] = pickle.loads(item.value) def _get_user_params(self): u"""    """ params = {} try: param_string = models.UserAccess.objects.get(report=self._report, user=self._user).params if param_string: for pair in param_string.split(';'): (key, values_str) = pair.split('=') values = values_str.split(',') params[key] = values except Exception, e: pass return params 

 select key, value, value * 0.5 as value1 from ( select 1 as key, 2000 as value union all select 2 as key, 4000 as value union all select 3 as key, 6000 as value union all select 4 as key, 3000 as value union all select 5 as key, 2000 as value union all select 6 as key, 1000 as value ) t {% if f.doble_rows %}cross join (select 1 union all select 2) t1 {% endif %} 

 select t.key as key, t.value as value, case when key = 1 then 'background-color: #dff0d8;' end as row_style_field, case when key = 2 then 'class="error"' end as row_attribute_field {% if f.add_column %} {% for row in datasets.ds.data %} , {{row.key}} as {{row.key}} {% endfor %} {% endif %} from ({{datasets.ds.sql}}) t {% if request.GET.key %} where key = [[request.GET.key]] {% endif %} {% if f.limit %} limit [[f.limit]] {% endif %} 

 <xml> <table field_row_style='row_style_field' field_row_attributes='row_attribute_field'/> <fields> <field name='' classes='text-right'/> <field name='value' classes='text-right' lnk='?response_type=xls&xls=ds&key=[[row.]]&from=value'/> {% for row in datasets.ds.data %} <field name='{{row.key}}' classes='text-right' lnk='?response_type=xls&xls=ds1&key=[[row.]]&from={{row.key}}'/> {% endfor %} <field name='1' display='0'/> <field name='row_style_field' display='0'/> <field name='row_attribute_field' display='0'/> </fields> </xml> 

 <xml> <table field_row_style='row_style_field' field_row_attributes='row_attribute_field'/> <fields> <field name='' classes='text-right'/> <field name='value' classes='text-right' lnk='?response_type=xls&xls=ds&key=[[row.]]&from=value'/> {% for row in datasets.ds.data %} <field name='{{row.key}}' classes='text-right' lnk='?response_type=xls&xls=ds1&key=[[row.]]&from={{row.key}}'/> {% endfor %} <field name='1' display='0'/> <field name='row_style_field' display='0'/> <field name='row_attribute_field' display='0'/> </fields> </xml> 

 <xml> <chart> <categoryField>key</categoryField> <marginTop>32</marginTop> </chart> <categoryAxis> <labelsEnabled>true</labelsEnabled> <gridCount>50</gridCount> <equalSpacing>true</equalSpacing> </categoryAxis> <valueAxis> <valueAxisLeft> <stackType>regular</stackType> <gridAlpha>0.07</gridAlpha> </valueAxisLeft> </valueAxis> <cursor> <bulletsEnabled>true</bulletsEnabled> </cursor> <graphs> <graph> <type>column</type> <title></title> <valueField>value</valueField> <balloonText>[[category]] : [[value]] .</balloonText> <lineAlpha>0</lineAlpha> <fillAlphas>0.6</fillAlphas> </graph> <graph1> <type>column</type> <title> </title> <valueField>value1</valueField> <balloonText>[[category]] : [[value]] .</balloonText> <lineAlpha>0</lineAlpha> <fillAlphas>0.6</fillAlphas> </graph1> </graphs> </xml> 

 <xml> <chart> <categoryField>key</categoryField> <marginTop>32</marginTop> </chart> <categoryAxis> <labelsEnabled>true</labelsEnabled> <gridCount>50</gridCount> <equalSpacing>true</equalSpacing> </categoryAxis> <valueAxis> <valueAxisLeft> <gridAlpha>0.07</gridAlpha> </valueAxisLeft> </valueAxis> <cursor> <bulletsEnabled>true</bulletsEnabled> </cursor> <graphs> <graph> <type>column</type> <title></title> <valueField>value</valueField> <balloonText>[[category]] : [[value]] .</balloonText> <lineAlpha>0</lineAlpha> <fillAlphas>0.6</fillAlphas> </graph> <graph1> <type>column</type> <title> </title> <valueField>value1</valueField> <balloonText>[[category]] : [[value]] .</balloonText> <lineAlpha>0</lineAlpha> <fillAlphas>0.6</fillAlphas> </graph1> <graph2> <type>smoothedLine</type> <title> </title> <valueField>value1</valueField> </graph2> </graphs> </xml> 

 <h2>   </h2> <div class='well well-small'>{{forms.f}}</div> <h3> - django template</h3> <pre> sql:{{datasets.ds1.render.sql}} :{{datasets.ds1.render.binds}} </pre> <h3> </h3> {{tables.t}} <a class='btn btn-success' href='?response_type=xls&xls=ds1'> Excel</a> <h2> </h2> {{tables.t1}} <div style='height:500px;width:500px;'>{{charts.test}}</div> <div style='height:500px;width:500px;'>{{charts.test1}}</div>