def draw_graph(request):
"""
execute sql and draw flash.
"""
if request.method == 'GET':
t = request.GET.copy()
tid = int(t['tid'])
u = Flashurl.objects.get(id=tid)
url = eval(u.url)
data={}
for key,value in url.items():
if key:
try:
data[key]=value.decode("utf-8")
except:
data[key]=value
u.delete()
try:
user_id = request.user.id
view_id = data.get('view_id')
data.pop('view_id')
v = View.objects.get(id=view_id)
except:
raise Http404
type = data.get('type')
# default chart type is bar
if not type:
type = "bar"
indicator = data['indicator'].split(",")
data.pop('indicator')
x_axis = BAR_FORMAT_FIELD if type == "bar" else DATE_FORMAT_FIELD
data.pop('type')
view_obj = ViewObj(v, request)
u_d = get_user_dimension(user_id,view_id)
u_dimension = u_d.split(",") + NON_NUMBER_FIELD
sql = SQLGenerator(data, view_obj, u_d, request,x_axis).get_sql().encode('utf-8')
res = execute_sql(sql)
chart = Chart()
chart.title.text = v.cname
chart.title.style = "{font-size: 17px; font-family: Verdana; text-align: center;}"
headers = view_obj.get_headers()
header_name = [ i['name']['value'] for i in headers]
header_cname = [ i['cname']['value'] for i in headers]
# generate x labels
indexes = []
label_keys = X_LABELS.get(type, {})
for name in label_keys:
try:
index = header_name.index(name)
except:
index = -1
indexes.append(index)
is_day_report = True if view_obj.get_body()['time_type']['name'] == 'day' else False
labels = []
for line in res:
label = []
for i, line_index in enumerate(indexes):
if line_index >= 0:
value = line[line_index]
# try:
# value = value.encode("utf-8")
# except EOFError:
# value = str(value)
# except:pass
else:
value = ''
label.append(value)
if i == len(indexes) - 1 and type != "bar":
if is_day_report:
label.pop()
label[-1] = format_date(label[-1])
else:
end_date = label.pop()
begin_date = label.pop()
date_list = []
if begin_date:
date_list.append(format_date(begin_date))
if end_date:
date_list.append(format_date(end_date))
label.append("~".join(date_list))
labels.append("\n".join(label))
if labels == ['\n']:labels=['全国\n'.decode("utf-8")]
chart.x_axis = {'labels': {"labels": labels,"size":12}}
graph_els = filter(lambda x:x not in u_dimension, header_name)
els = []
max_values = []
# add chart elements one by one.
if res:
for i, el in enumerate(graph_els):
if el not in indicator:
index = header_name.index(el)
try:
values = [int(line[index]) for line in res]
max_values.append(max(values))
except:
values = [line[index] for line in res]
graph = Chart()
graph.type = type
graph.values = values
graph.text = header_cname[index]
graph.alpha = 0.5
graph.fontsize = 13
graph.tip = '#key#<br>[#x_label#]:#val#'
graph.colour = CHART_COLOR[i]
els.append(graph)
chart.elements = els
if res:
max_value = max(max_values)
step = max_value/10
chart.y_axis = {'max': max_value, 'min': 0, 'steps': step,'labels': {"size":11}}
chart_c=chart.create()
#log
try:
log_id = "%s%05d" % (datetime.datetime.now().strftime("%Y%m%d%H%M%S"),random.randint(0,10000))
data_log = "%s-%s"%(v.cname,v.get_time_type_display())
log = "%s|%s,%s"%("draw_graph",
type.encode("utf-8"),
data_log.encode("utf-8"))
tolog(request,log,log_id)
except:
pass
return HttpResponse(chart_c)
def down_excel(request):
"""
download excel file.
"""
if request.method == 'POST':
user_id=request.user.id
data = request.POST.copy()
try:
view_id = data.get('view_id')
data.pop('view_id')
v = View.objects.get(id=view_id)
except:
raise Http404
#log
try:
log_id = "%s%05d" % (datetime.datetime.now().strftime("%Y%m%d%H%M%S"),random.randint(0,10000))
data_log = "%s-%s"%(v.cname,v.get_time_type_display())
log = "%s|%s"%("down_excel",
data_log.encode("utf-8"))
tolog(request,log,log_id)
except:
pass
view_obj = ViewObj(v, request)
u_d = get_user_dimension(user_id,view_id)
sql_sum = SQLGenerator(data, view_obj, u_d,request).get_sum().encode('utf-8') #求总量的sql语句
object_sql = SQLGenerator(data,view_obj,u_d,request)
sql = object_sql.get_sql().encode('utf-8') #查询内容的sql语句
sql_sum_column = object_sql.sum_column #求和的字段
try:
sql_column_sum = execute_sql(sql_sum)
sum_data = dict(zip(sql_sum_column,sql_column_sum[0]))
except:
sum_data = None
res = execute_sql(sql)
res = format_table(res, view_obj,u_d,sum_data)
w = Workbook()
ws = w.add_sheet('result')
ws.write_merge(0, 0, 0, len(res[0])-1, '%s:%s (%s ---- %s)'%(v.cname,v.get_time_type_display(),data['begin_date'],data['end_date']))
if not res:
response = HttpResponse("",mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=result.xls'
return response
#write to header
for k,header in enumerate(res[0]):
ws.write(1, k, header['cname']['value'])
res.pop(0)
#write to data
for i, line in enumerate(res):
for j,cell in enumerate(line):
try:
cell['value'] = cell['value'].encode("utf-8")
except:
pass
if isinstance(cell['value'],str):
new_cell = unicode(cell['value'],'utf8')
else:
new_cell = str(cell['value'])
new_cell=new_cell.replace(",","")
try:
new_cell=int(new_cell)
except:
pass
ws.write(i+2,j, new_cell)
w_save = w.save_stream()
response = HttpResponse(w_save,mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=result.xls'
return response
else:
raise Http404
