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