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
