def run_query(self, qu, jsondata): logger.debug("Running query [%s]", qu) try: query_result = {} qb = QBuilder(logger, make_db_url()) qb.loadmodels(getattr(settings, 'QBUILDER_MODELS_NAME', 'sample')) if jsondata: qb.query_parameters = jsondata query_result.update({ "query_result": qb.all_data(), "sql_query": str(qb.query), }) if "statistics" in jsondata: logger.debug("Running statistics for [%s]", qu) query_result["statistics"] = qb.statistics(jsondata.get("percentile", 100), True) except: logger.error("QBuilder error in query [%s] : %s", qu, traceback.format_exc()) qu.status = -1 # Erreur execution ou fetch qu.save() return if not query_result["query_result"]: logger.info("Query [%s] returned nothing", qu) qu.status = 2 # Aucun résultat qu.save() return query_result["keys"] = query_result["query_result"][0].keys() try: json_data = jsondump(query_result) except: logger.error("Serializaion error for results of query [%s] : %s", qu, traceback.format_exc()) qu.status = -2 # Erreur serialization qu.save() return QueryResult.objects.create(query = qu, data = json_data, date = datetime.datetime.now()) qu.status = 1 # Executé qu.save() logger.debug("Query [%s] succesfully ran", qu)
def prepare_charts(graph_data, query_id): templates = ['pie', 'bar', 'area', 'line', 'column', 'scatter', 'series', 'stackedbar', 'stackedcolumn', 'frise'] try: query_param = QueryParameters.objects.get(pk = query_id) jsondata = json.loads(query_param.data) except QueryParameters.DoesNotExist: return make_response("error.html", {"message" : _(u"No query with id %s") % (query_id,)}) except ValueError: return make_response("error.html", {"message" : _(u"Incorrect parameters for query %s") % (query_id,)}) graph_type = jsondata.get("graph_type", "line") if graph_type not in templates: return make_response("error.html", {"message" : _(u"No templates with id %s") % (query_id,)}) # On regarde si on a déjà des data pour cette requête logger.debug("Reading cache") cached_result = QueryResult.objects.filter(query = query_param).order_by('-date') if cached_result.count() != 0 and graph_data.get("cache_allowed", False): logger.debug("Using cached results") cached_result = cached_result[0] graph_data["subtitle_suffix"] = _(u"Using cached results generated on %s") % (cached_result.date.strftime("%d/%m %H:%M"),) graph_data.update(jsonload(cached_result.data)) else: graph_data.update(get_context(jsondata = jsondata)) entetes_colonnes = [] subtitle = "" if "statistics" in jsondata: logger.debug("Convert statistics to highcharts") dataHC = qbuilder_stats_to_highcharts(graph_data["statistics"], graph_type, jsondata.get("statistics",[])) if graph_data["query_result"]: entetes_colonnes = graph_data["keys"] stats_on = entetes_colonnes[1] subtitle = _("Statistics by %s") % (stats_on) elif graph_type == 'frise': logger.debug("Convert data to 'frise'") dataHC = qbuilder_temps_rendu_to_frise_tat(graph_data["query_result"], jsondata, graph_type, 1, 0) else: logger.debug("Select the right type of data conversion for highcharts") dataHC = select_function_qbuilder_data_to_highcharts(graph_data["query_result"], graph_type, 1, 0) if graph_data["query_result"]: entetes_colonnes = graph_data["keys"] subtitle = make_Z(entetes_colonnes) if subtitle: subtitle = _(u"Grouped by : %s") % (subtitle,) logger.debug("Building graph parameters") data_param = init_data_param(graph_type, jsondata, dataHC) graph_data.update(data_param) if graph_data.get("subtitle_suffix") and subtitle: graph_data["subtitle_suffix"] = u" - " + graph_data["subtitle_suffix"] if graph_type == 'frise': graph_data.update({ "title_text": query_param.name, "subtitle_text": subtitle, "description": jsondata.get("description"), }) graph_data.update(dataHC) else: # Traduction des libelles de la legende if jsondata.get("graph_type") == "pie": for d in dataHC["xValues"]: for x in d["data"]: x["name"] = jsondata.get("label_for_%s" % x["name"], x["name"]) else: for x in dataHC["xValues"]: x["name"] = jsondata.get("label_for_%s" % x["name"], x["name"]) yAxis_title_text = jsondata.get("label_for_%s" % entetes_colonnes[0], entetes_colonnes[0]) if entetes_colonnes else None xAxis_title_text = jsondata.get("label_for_%s" % entetes_colonnes[1], entetes_colonnes[1]) if entetes_colonnes else None if 'statistics' in jsondata: xAxis_title_text = jsondata.get("label_for_%s" % entetes_colonnes[2], entetes_colonnes[2]) if entetes_colonnes else None xAxis_labels_rotation = 0 if len(dataHC["xLabels"]) > 10: # A RENDRE PARAMETRABLE xAxis_labels_rotation = -90 # A RENDRE PARAMETRABLE (-90 ou -45) graph_data.update( { "title_text": query_param.name, "subtitle_text": subtitle, "xAxis_categories": jsondump([jsondata.get("label_for_%s" % x, x) for x in dataHC["xLabels"]]), "yAxis_title_text": yAxis_title_text, # A RENDRE PARAMETRABLE "xAxis_title_text": xAxis_title_text, # A RENDRE PARAMETRABLE "xAxis_labels_rotation": xAxis_labels_rotation, "seriesData": jsondump(dataHC["xValues"]), "description": jsondata.get("description"), } ) return graph_data
def init_data_param(graph_type, jsondata = {}, data = []): data_param = { "yAxis_title_align": "middle", "yAxis_stackLabels": "false", "tooltip_formatter": "this.y", "plotOptions": jsondump({}), "yAxis": { "min": None, "max": None, }, "xAxis": {} } tooltip_this_x = 'this.x'; tooltip_this_y = 'this.y'; python_type_to_js_converter_function = { datetime.datetime: lambda v: "dojo.date.locale.format(new Date(%s))" % (v,), datetime.timedelta: lambda v: """mca.timestampdisplay(%s, "S", 3)""" % (v,), } if 'colors' in jsondata: data_param['colors'] = jsondump(jsondata['colors'].split(',')) logger.error("using colors in jsondata %s", data_param['colors']) if isinstance(data, dict) and 'xType' in data: tooltip_this_x = python_type_to_js_converter_function.get(type(data['xType']), lambda v: v)(tooltip_this_x) if isinstance(data, dict) and 'yType' in data: tooltip_this_y = python_type_to_js_converter_function.get(type(data['yType']), lambda v: v)(tooltip_this_y) if graph_type == "area": data_param["tooltip_formatter"] = "''+ this.series.name +': '+ " + tooltip_this_y data_param["plotOptions"] = jsondump({"area": {"fillOpacity": 0.9},}) elif graph_type == "bar": data_param["yAxis_title_align"] = "high" data_param["tooltip_formatter"] = "''+ this.series.name +': '+ " + tooltip_this_y data_param["plotOptions"] = jsondump({"bar": {"dataLabels":{"enabled":True},},}) elif graph_type == "column": data_param["tooltip_formatter"] = "''+ this.series.name +': '+ " + tooltip_this_y data_param["plotOptions"] = jsondump({"column": {"dataLabels":{"enabled":True},},}) elif graph_type == "stackedbar": data_param["yAxis_title_align"] = "high" data_param["tooltip_formatter"] = "''+ this.series.name +': '+ " + tooltip_this_y data_param["plotOptions"] = jsondump({"series": {"stacking": "normal"}, "bar": {"dataLabels": {"enabled":True, "color": "#FFFFFF", "align": "center"},},}) elif graph_type == "stackedcolumn": data_param["yAxis_stackedLabels"] = "true" data_param["tooltip_formatter"] = "'<b>'+ " + tooltip_this_x + " +'</b><br/>'+ this.series.name +': '+ " + tooltip_this_y + " +'<br/>'" if jsondata.get('stacking', 'normal') == 'percent': data_param["tooltip_formatter"] = "'<b>'+ " + tooltip_this_x + " +'</b><br/>'+ this.series.name +': '+ Math.round(this.percentage*100)/100 +'%<br/>(' + " + tooltip_this_y + " + ')'" data_param["plotOptions"] = jsondump({"column": {"stacking": jsondata.get("stacking", "normal"), "dataLabels": {"enabled": jsondata.get("dataLabels", "1") != "off", "color": "#FFFFFF", "align": "center"},},}) elif graph_type == "pie": data_param["tooltip_formatter"] = "'<b>'+ this.point.name +'</b>: '+ " + tooltip_this_y data_param["plotOptions"] = jsondump({"pie": {"size": 280, "allowPointSelect": True, "cursor": "pointer"},}) elif graph_type == "line": data_param["tooltip_formatter"] = "'<b>'+ this.series.name +'</b><br/>'+ " + tooltip_this_x + " +': '+ " + tooltip_this_y elif graph_type == "scatter": data_param["tooltip_formatter"] = "'<b>'+ this.series.name +'</b><br/>'+Highcharts.dateFormat('%e. %b', " + tooltip_this_x + ") +': '+ " + tooltip_this_y data_param["plotOptions"] = jsondump({"scatter": {"marker":{"radius":3},},}) for key, value in jsondata.items(): for axis in ('x', 'y'): if key.startswith(axis + 'Axis_'): real_key = '_'.join(key.split('_')[1:]) data_param[axis + 'Axis'][real_key] = convert_input_values_to_HC(value, real_key) for axis in ('x', 'y'): data_param[axis + 'Axis'] = jsondump(data_param[axis + 'Axis']) return data_param