def show_table(request):
"""
execute sql and fetch results.
"""
user_id = request.user.id
data = request.POST.copy()
data.pop('timestamp')
provlist = len(data['provname'].split(",")) if data.has_key('provname') else 0
try:
view_id = data.get('view_id')
data.pop('view_id')
v = View.objects.get(id=view_id)
except:
raise Http404
# get container div, sent it back to client and put table in that container.
try:
page = data['current_page']
data.pop('current_page')
page = int(page)
except:
page = 1
container_id = data.get('container')
data.pop('container')
view_obj = ViewObj(v, request)
view_id = view_obj.obj['view_id']
t = loader.get_template('results.html')
try:
v_query = view_obj.get_query()
v_query = query_session(v_query)
u_d = get_user_dimension(user_id,view_id)
# !!! 生成sql语句
sql_sum = SQLGenerator(data, view_obj, u_d,request).get_sum().encode('utf-8') #求和
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 #求和的字段
#分页：例 第1页显示 0，30--limit 0,30 第2页显示 30，30 -- limit 30,30
#所以公式为 （页数-1）*每页显示 ， 页数*每页显示
sql_limit = "%s limit %s,%s"%(sql,(page-1)*MAX_DATA,MAX_DATA+1) #+1多取1条判断是否有下页
# !!! 双线程执行sql语句，t_sum求和 t_sql取前50条
t_sum = Mythread(args=(sql_sum,))
t_sql = Mythread(args=(sql_limit,))
t_sum.start()
t_sql.start()
t_sum.join()
t_sql.join()
try:
sql_column_sum = t_sum.v
sum_data = dict(zip(sql_sum_column,sql_column_sum[0]))
except: #某些报表没有指标！ 只有维度，跑sql语句会报错的！ 擦！
sum_data = None
res = t_sql.v
#分页
contacts = MyPaginator(len(res), page, MAX_DATA)
res = res[:MAX_DATA]
u_dimension = u_d.split(",") if len(u_d)>0 else []
res = format_table(res, view_obj,u_dimension,sum_data)
head,res = (res[0],res[1:]) if res else ("",[])
tips,u_session="",True
if country_session(u_d) and provlist<HIGHEST_AUTHORITY and v_query:
tips = "如果要看分省/市数据，请在维度设置中勾选省/市<p>如果查看全国数据，请将省条件全选"
u_session = False
#log
try:
log_id = "%s%05d" % (datetime.datetime.now().strftime("%Y%m%d%H%M%S"),random.randint(0,10000))
sql_limit_log = "%s|Query_time: %s, sql: %s"%("show_table",
t_sql.time,
sql_limit.encode("utf-8"))
tolog(request,sql_limit_log,log_id)
sql_sum_log = "%s|Query_time: %s, sql: %s"%("show_table",
t_sum.time,
sql_sum.encode("utf-8"))
tolog(request,sql_sum_log,log_id)
except:
pass
html = t.render(Context({'view_id': view_id,
'res': res,
'contacts': contacts,
'u_session': u_session,
'tips': tips,
'head': head,
'ud': u_dimension,
'container_id': container_id,
'headers': view_obj.get_headers(),
'table_name': view_obj.get_body()['dataset'].cname,
}))
json_text = simplejson.dumps({'container':container_id,'content':html})
return HttpResponse(json_text)
except:
json_text = showtable_500(t,container_id,view_obj)
return HttpResponse(json_text)
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
