def get_date(server_id, statistic_id, begin_date, end_date, usm): the_user = usm.get_the_user() mc = memcached_util.MemcachedUtil() channel_list = [] if usm.current_userRole_is_root(): channel_list = center_cache.get_channel_list(mc) else: channel_list = center_cache.get_user_channel_list(the_user, mc) conn = connections['read'] cursor = conn.cursor() channel_id_list = [str(item.id) for item in channel_list] server_conditions = '' if 0 != server_id: server_conditions = ' AND server_id=%s ' % server_id sql = "SELECT SUM(`result`) FROM `result` WHERE statistic_id=%s AND create_time BETWEEN '%s' AND '%s' AND channel_id IN (%s) %s " % (statistic_id ,begin_date, end_date, ','.join(channel_id_list), server_conditions) cursor.execute(sql) result = cursor.fetchone()[0] if result == None: return 0 return float(result)
def get_data(server_id, statistic, usm): the_user = usm.get_the_user() mc = memcached_util.MemcachedUtil() channel_list = [] if usm.current_userRole_is_root(): channel_list = center_cache.get_channel_list(mc) else: channel_list = center_cache.get_user_channel_list(the_user, mc) conn = connections['read'] cursor = conn.cursor() channel_id_list = [str(item.id) for item in channel_list] begin_date = now.strftime('%Y-%m-%d 00:00:00') end_date = (now + datetime.timedelta(days=1)).strftime('%Y-%m-%d 00:00:00') server_conditions = '' if 0 != server_id: server_conditions = ' AND server_id=%s ' % server_id sql = "SELECT SUM(`result`) FROM `result` WHERE statistic_id=%s AND create_time BETWEEN '%s' AND '%s' AND channel_id IN (%s) %s " % ( statistic.id, begin_date, end_date, ','.join(channel_id_list), server_conditions) key = md5(sql) result = mc.get(key) if None == result: cursor.execute(sql) result = cursor.fetchone()[0] if result == None: return 0 result = float(result) mc.set(key, result) return result
def manage_question_list(request, user_id=0, status= -1): ajax = request.GET.get('ajax', False) if status == -1 : status = int(request.GET.get('status', '-1')) search_type = int(request.GET.get('search_type', '-1')) question_type = int(request.GET.get('question_type', '-1')) group_id = int(request.GET.get('group_id', -1)) kefu_name = request.GET.get('kefu_name', '') vip = -1 try: vip = int(request.GET.get('vip', -1)) except: vip = -1 the_user_id = int(request.session.get('userid', '0')) the_user = Admin.objects.using('read').get(id=the_user_id) usm = UserStateManager(request) list_group = Group.objects.using('read').all() server_id = int(request.GET.get('server_id', '0')) if server_id == 0: server_id = int(request.session.get("serverId", '0')) if user_id == 0 or user_id == '0': user_id = request.GET.get('user_id', '') status = int(status) page_num = int(request.GET.get('page_num', '1')) page_size = 20 total_page = 1 if page_num < 1: page_num = 1 kefu_list = Admin.objects.using('read').filter(role__id = 3) for kefuItem in kefu_list: kefuServerList = kefuItem.server.all() kefuServerIdList = "{\'server\':[" for kefuServerItem in kefuServerList: if kefuServerIdList == "{\'server\':[": kefuServerIdList += "{\'serverId\':'%s',\'serverName\':'%s'}" % (kefuServerItem.id, kefuServerItem.name) else: kefuServerIdList += ",{\'serverId\':'%s',\'serverName\':'%s'}" % (kefuServerItem.id, kefuServerItem.name) kefuServerIdList += "]}" #print kefuServerIdList kefuItem.kefuServerIdList = kefuServerIdList # *** 过滤 服务器列表 *** if usm.current_userRole_is_root(): list_server = center_cache.get_server_list() else: list_server = center_cache.get_user_server_list(the_user) group_server_list = [] if group_id > 0 : group = Group.objects.using('read').get(id = group_id) for item in group.server.all(): group_server_list.append(item) tmp_list = [] for item in group_server_list: if list_server.__contains__(item): tmp_list.append(item) list_server = tmp_list tmp_list = None group_server_list = None # *** 过滤 服务器列表 END *** theUserServerId = [] itemServerList = {} for item in list_server: itemServerList[item.id] = item.name if len(the_user.server.filter(id=item.id)) > 0: item.is_show = 1 theUserServerId.append(item.id) query = Q() if user_id != '': if search_type == 1: query = query & Q(post_user=user_id) elif search_type == 2: query = query & Q(question__contains = user_id) elif search_type == 3: query = query & Q(score=user_id) if -1 != vip: query = query & Q(post_user_id = vip) if -1 != question_type: query = query & Q(question_type=question_type) if server_id > 0: query = query & Q(server_id=server_id) else: if not usm.current_userRole_is_root(): server_id_list = [item.id for item in list_server] query = query & Q(server_id__in = server_id_list) if '' != kefu_name: query = query & Q(reply_user = kefu_name) if not usm.current_userRole_is_root(): channel_list = center_cache.get_user_channel_list(the_user) channel_id_list = [item.id for item in channel_list] channel_id_list.append(0) query = query & Q(channel_id__in = channel_id_list) if status != -1: query = query & Q(status=status) total_record = Question.objects.using('read').filter(query).count() list_record = Question.objects.using('read').filter(query)[(page_num - 1) * page_size:page_num * page_size] for item in list_record: if item.server_id > 0: item.serverName = itemServerList.get(item.server_id, '--') else: item.serverName = "--" if total_record > page_size: total_page = total_record / page_size if total_record % page_size > 0: total_page += 1 parg = {} parg["server_id"] = server_id parg["list_server"] = list_server parg["user_id"] = user_id parg["usm"] = usm parg["kefu_list"] = kefu_list parg["the_user_id"] = the_user_id parg["list_record"] = list_record parg["search_type"] = search_type parg["status"] = status parg["list_group"] = list_group parg["kefu_name"] = kefu_name parg["page_num"] = page_num parg["page_size"] = page_size parg["total_record"] = total_record parg["question_type"] = question_type parg["group_id"] = group_id if ajax: return render_to_response('server/question_list_block.html', parg) return render_to_response('server/question_list.html', parg)
def query_view(request, query_id=0): query_id = int(query_id) if 0 == query_id: query_id = int(request.GET.get('id', request.POST.get('id', 0))) s_list = request.POST.getlist('s') if 0 == s_list.__len__(): s_list = request.GET.getlist('s') page_num = int(request.GET.get('page_num', '1')) sdate = request.GET.get('sdate', '') edate = request.GET.get('edate', '') query_channel = request.GET.getlist('channel_id') group_id = int(request.GET.get('group_id', '0')) ajax = request.GET.get('ajax', False) exprot = int(request.GET.get('exprot', '0')) close_export = int(request.GET.get('close_export', '0')) clear_export_old_file = int(request.GET.get('clear_export_old_file', '0')) is_post_back = request.GET.get('post_back', False) exprot_file_key = request.GET.get('exprot_file_key', '') exprot_file_key = exprot_file_key.strip() more_serv_exprot = False if exprot_file_key != '': more_serv_exprot = True begin_exec_time = datetime.datetime.now() page_size = 50 mc = MemcachedUtil() if exprot > 0: page_size = 500 list_group = [] usm = UserStateManager(request) the_user = usm.get_the_user() if query_id > 0: the_query = log_cache.get_query(query_id, mc) log_define = log_cache.get_logDefine(the_query.log_type, mc) #是否在中央服务器的查询 is_centerQuery = False if log.the_log_in_center(log_define): #check_user is_centerQuery = True is_select_server = False else: is_centerQuery = False is_select_server = True has_sql = False if the_query.sql != '': has_sql = True if not has_sql or -1 != the_query.sql.find('{{server_id}}'): is_select_server = True list_query = log_cache.get_query_list_by_logType(the_query.log_type, mc) if is_select_server: list_group = center_cache.get_group_list() if group_id != 0 and usm.current_userRole_is_root(): list_server = center_cache.get_server_list(group_id, mc) else: if usm.current_userRole_is_root(): list_server = center_cache.get_server_list(mc_util=mc) else: list_server = center_cache.get_user_server_list(the_user) #取出字段定义 list_field = log_cache.get_fielddef_list_by_logType(the_query.log_type, mc) server_id = int(request.GET.get('server_id', '0')) if is_centerQuery and s_list.__len__() == 0: s_list = [str(item.id) for item in list_server] if not usm.current_userRole_is_root() and s_list.__len__() == 0: return HttpResponse(u"非法操作") elif server_id == 0 and len(list_server) > 0: server_id = list_server[0].id if server_id == 0: return HttpResponse(u"非法操作") if usm.current_userRole_is_root(): list_channel = center_cache.get_channel_list(mc) else: list_channel = center_cache.get_user_channel_list(the_user, mc) #是否在页面上显示查询channel allowChannel = False if query_channel.__len__() == 0 and not usm.current_userRole_is_root(): query_channel = [] for item in list_channel: query_channel.append(str(item.id)) if (not has_sql and list_channel.__len__() > 0) or ( -1 != the_query.sql.find('{{qchannel')): allowChannel = True for item1 in list_channel: if query_channel.__len__() > 0: if str(item1.id) in query_channel: item1.is_show = 1 else: item1.is_show = 0 field_value = request.GET.get('field_value', '') field_value = filter_inject_sql(field_value) field_id = int(request.GET.get('field_id', '0')) if has_sql: field_value = request.GET.getlist('field_value') err_msg = '' pager_str = 'limit %s,%s' % ((page_num - 1) * page_size, page_size) fields = the_query.select.split(',') query_date = '' now = datetime.datetime.now() if not is_post_back: #如果第一次进入该页面默认时间范围是昨天数据 if sdate == '': sdate = (now - datetime.timedelta(days=1)).strftime('%Y-%m-%d 00:00:00') if edate == '': edate = now.strftime('%Y-%m-%d 00:00:00') if not has_sql or the_query.sql.find('{{keyword}}') != -1: is_search = True else: is_search = False has_sdate = True has_edate = True if the_query.sql != '': if -1 == the_query.sql.find('{{sdate}}'): has_sdate = False if -1 == the_query.sql.find('{{edate}}'): has_edate = False if -1 != the_query.sql.find('{{qdate}}'): has_sdate = True has_edate = True if -1 == the_query.sql.find('{{qchannel'): allowChannel = False keywords = [] lost_param = False if has_sql: sql = the_query.sql r_keyword_name = '(@keywords.+)' keyword_name_ary = re.findall(r_keyword_name, sql, re.I) if keyword_name_ary.__len__() != 0: keyword_name = keyword_name_ary[0] names_str = keyword_name.split(':')[1] names = names_str.split(',') for i in range(names.__len__()): name = names[i] value = '' if field_value.__len__() > i: value = field_value[i] keywords.append({"name": name, "value": value}) the_query.sql = re.sub(keyword_name, '', sql, re.I) else: k_len = the_query.sql.lower().count('{{keyword}}') for i in range(k_len): value = '' if field_value.__len__() > i: value = field_value[i] if value == '': lost_param = True keywords.append({"name": '输入框%s' % i, "value": value}) #print keywords.__len__() list_data = [] total_record = 0 parg = {} parg['keywords'] = keywords parg['has_sql'] = has_sql parg['is_centerQuery'] = is_centerQuery parg['has_sdate'] = has_sdate parg['has_edate'] = has_edate parg["allowChannel"] = allowChannel parg["the_query"] = the_query parg["usm"] = usm parg["list_query"] = list_query parg["is_select_server"] = is_select_server parg["list_group"] = list_group parg["list_server"] = list_server parg["list_channel"] = list_channel parg["s_list"] = s_list parg["server_id"] = server_id parg["query_id"] = query_id parg["is_search"] = is_search parg["group_id"] = group_id parg["field_id"] = field_id parg["field_value"] = field_value parg["list_field"] = list_field parg["sdate"] = sdate parg["edate"] = edate parg["fields"] = fields parg["page_num"] = page_num parg["page_size"] = page_size parg["total_record"] = total_record if not is_post_back and not ajax: #如果没有点击查询按钮并不是ajax提交则不查询数据(第一次进入页面不查询数据) ——zhenwei 2012-10-22 return render_to_response('log/query_view.html', parg) if is_select_server and not is_centerQuery: if server_id > 0: try: conn = getConn(server_id) except: err_msg = '数据库链接出错!' else: conn = connections['read'] if err_msg != '': return render_to_response('feedback.html', locals()) try: if sdate != '': sdate = datetime.datetime.strptime( sdate, '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S') query_date = ' a.log_time>=\'%s\'' % sdate if edate != '': if query_date != '': query_date += ' and ' edate = datetime.datetime.strptime( edate, '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S') query_date += ' a.log_time<=\'%s\'' % edate except: sdate = '' edate = '' #是否有channel 查询条件 channel_condition = query_channel.__len__() > 0 query_key = '' if not has_sql: query_field = the_query.select query_where = the_query.where query_order = the_query.order query_group = the_query.group field_name = '' for field_item in list_field: query_field = query_field.replace(field_item.name, field_item.field_name) query_where = query_where.replace(field_item.name, field_item.field_name) query_order = query_order.replace(field_item.name, field_item.field_name) query_group = query_group.replace(field_item.name, field_item.field_name) if field_id == field_item.id: field_name = field_item.field_name #处理字段值查询 if field_name != '': the_values = ValueDefine.objects.filter(field_id=field_id, value=field_value) if len(the_values) > 0: field_value = the_values[0].value_id if query_where != '': query_where += ' and ' query_where += u'a.%s=\'%s\'' % (field_name, field_value) if query_date != '': if query_where != '': query_where += ' and ' query_where += query_date if channel_condition: if query_where != '': query_where += ' and ' query_where += 'a.log_channel in(%s)' % (','.join(query_channel)) if query_where != '': query_where = 'where %s' % query_where if query_order != '': query_order = ' order by a.%s' % query_order if the_query.order_type == 1: query_order += ' desc' count_sql = 'select count(0) from log_%s a %s' % (log_define.key, query_where) query_sql = 'select %s from log_%s a %s %s %s' % ( query_field, log_define.key, query_where, query_order, pager_str) query_key = md5('%s_%s' % (query_sql, server_id)) if exprot_file_key == '': exprot_file_key = md5('%s_%s' % (query_sql, server_id)) #print(query_sql) else: query_sql = the_query.sql.replace('\r\n\t', ' ').replace('\r\n', ' ') if is_centerQuery and s_list.__len__() > 0: server_list_str = ' IN (%s) ' % (','.join(s_list)) query_sql = re.sub('=[\s]*{{server_id}}', server_list_str, query_sql) elif server_id > 0: query_sql = query_sql.replace('{{server_id}}', str(server_id)) if query_date != '': query_sql = query_sql.replace("{{qdate}}", query_date) query_sql = query_sql.replace('{{sdate}}', '%s' % sdate).replace( '{{edate}}', '%s' % edate) #支持自定对非log表的日期支持 -zhenwei 2012-10-25 else: query_sql = query_sql.replace('and {{qdate}}', '').replace('where {{qdate}}', '') query_sql = query_sql.replace( '\'{{sdate}}\'', 'DATE(\'2001-01-01\')').replace( '\'{{edate}}\'', 'NOW()') #支持自定对非log表的日期支持 -zhenwei 2012-10-25 if lost_param: err_msg = '请输入查询条件' return render_to_response('feedback.html', {"err_msg": err_msg}) # if field_value != '': # query_sql = query_sql.replace("{{keyword}}", field_value) # else: # if -1 != query_sql.find('{{keyword}}'): # err_msg = '请输入查询条件' # return render_to_response('feedback.html', {"err_msg":err_msg}) if channel_condition: query_sql = query_sql.replace( '{{qchannel}}', 'log_channel in(%s)' % (','.join(query_channel))) else: query_sql = query_sql.replace('and {{qchannel}}', '').replace('where {{qchannel}}', '') if channel_condition: query_sql = query_sql.replace( '{{qchannela}}', 'a.log_channel in(%s)' % (','.join(query_channel))) else: query_sql = query_sql.replace('and {{qchannela}}', '').replace('where {{qchannela}}', '') if channel_condition: query_sql = query_sql.replace( '{{qchannelb}}', 'b.log_channel in(%s)' % (','.join(query_channel))) else: query_sql = query_sql.replace('and {{qchannelb}}', '').replace('where {{qchannelb}}', '') if channel_condition: query_sql = query_sql.replace( '{{qchannelid}}', 'channel_id in (%s)' % (','.join(query_channel))) query_sql = filter_keyword(query_sql, field_value) count_sql = 'select count(0) from (%s) newTable' % query_sql if exprot_file_key == '': exprot_file_key = md5('%s_%s' % (query_sql, server_id)) if query_sql.find('limit') == -1: query_sql = '%s %s' % (query_sql, pager_str) query_key = md5('%s_%s' % (query_sql, server_id)) parg['has_sdate'] = has_sdate parg['has_edate'] = has_edate print count_sql print query_sql #raise Exception, count_sql #desc = cursor.description cursor = conn.cursor() # update log_create_role channelKey_id_dic = {} query_memcache = mc if the_query.cache_validate != None and 0 != the_query.cache_validate: query_memcache = MemcachedUtil(valid_date=the_query.cache_validate) for item1 in list_channel: channelKey_id_dic[item1.key] = int(item1.id) try: count_query_key = md5('%s_%s' % (count_sql, server_id)) total_record = log_cache.get_query_count(count_sql, count_query_key, cursor, query_memcache) except Exception, e: raise Exception, e print('query_view error:', e) return render_to_response('feedback.html', {"err_msg": "查询数据时出错"})
def result_analyse(request, query_id=0, show_type='list'): query_id = int(query_id) base_group_id = int(request.GET.get('base_group_id', 0)) op_group_id = int(request.GET.get('op_group_id', 0)) query_statistic_item = request.GET.getlist('f') query_item = request.GET.get('query_item', 0) select_op = request.GET.get("select_op","") query_channel = request.GET.getlist('c')#channel_id usm = UserStateManager(request) the_user = usm.get_the_user() query_channel_1 = '' query_channel_2 = '' channel_1 = int(request.GET.get('channel_1', 0)) channel_2 = int(request.GET.get('channel_2', 0)) if channel_1: query_channel_1 = ' and channel_id = %d ' % channel_1 if channel_2: query_channel_2 = ' and channel_id = %d ' % channel_2 if usm.current_userRole_is_root(): list_channel = center_cache.get_channel_list() else: list_channel = center_cache.get_user_channel_list(the_user) for item1 in list_channel: if query_channel.__len__() > 0: if str(item1.id) in query_channel: item1.is_show = 1 else: item1.is_show = 1 query_server1 = request.GET.getlist('s1')#server_id query_server2 = request.GET.getlist('s2')#server_id base_server = request.GET.get('base_server','') op_server = request.GET.get('op_server','') if not base_server: if query_server1.__len__() > 0: base_server = ','.join(query_server1) elif not query_server1: query_server1 = base_server.split(',') elif query_server1: base_server = ','.join(query_server1) if not op_server: if query_server2.__len__() > 0: op_server = ','.join(query_server2) elif not query_server2: query_server2 = op_server.split(',') elif query_server2: op_server = ','.join(query_server2) the_query = QueryResult.objects.get(id=query_id) list_statistic = the_query.statistic.all() list_statistic_sort = get_statistic_in_query(query_id)#获取根据关联表ip排序的数据 join_results = [] list_statistic_name = [] exec_interval = 0 statistic_results = [] canSelectServer = True item_results = [] for item in list_statistic_sort: statistic_results.append(str(item[0])) join_results.append([str(item[0]),item[1]])#id item_results.append(int(item[0])) for item3 in join_results: if query_statistic_item.__len__() > 0: query_item = ','.join(query_statistic_item) if str(item3[0]) in query_statistic_item: item3.append(1) list_statistic_name.append(item3[1]) else: if query_item: query_statistic_item = query_item.split(',') item3.append(1) list_statistic_name.append(item3[1]) list_group = [] if usm.current_userRole_is_root(): list_group = Group.objects.all() if 0 != base_group_id: list_server_base = Group.objects.get(id = base_group_id).server.all() else: list_server_base = get_server_list() if 0 != op_group_id: list_server_op = Group.objects.get(id = op_group_id).server.all() else: list_server_op = get_server_list() else: list_server_base = the_user.server.all().order_by("id") list_server_op = the_user.server.all().order_by("id") #if canSelectServer: for serverItem in list_server_base: if len(query_server1) > 0: if str(serverItem.id) in query_server1: serverItem.is_show = 1 else: serverItem.is_show = 1 for serverItem in list_server_op: if len(query_server2) > 0: if str(serverItem.id) in query_server2: serverItem.is_show = 1 else: serverItem.is_show = 1 the_date = datetime.datetime.now() sdate = request.GET.get('sdate', the_date.strftime('%Y-%m-1')) edate = request.GET.get('edate', the_date.strftime('%Y-%m-%d')) # print(sdate,edate) query_date = '' try: if sdate != '': sdate = datetime.datetime.strptime(sdate, '%Y-%m-%d').strftime('%Y-%m-%d')# %H:%M:%S query_date = ' and result_time>=\'%s\'' % sdate if edate != '': if query_date != '': query_date += ' and ' edate = datetime.datetime.strptime(edate, '%Y-%m-%d').strftime('%Y-%m-%d') query_date += ' result_time<=\'%s\'' % edate except: sdate = '' edate = '' if query_channel.__len__() == 0 and not usm.current_userRole_is_root(): query_channel = [] for item in list_channel: query_channel.append(str(item.id)) if query_server1.__len__() == 0 and not usm.current_userRole_is_root(): query_server1 = [] for item in list_server_base: query_server1.append(str(item.id)) if query_server2.__len__() == 0 and not usm.current_userRole_is_root(): query_server2 = [] for item in list_server_op: query_server2.append(str(item.id)) # query_channel_str = '' # if query_channel.__len__() > 0 : # query_channel_str = ' and channel_id in (%s)' % (','.join(query_channel)) # query_server_str1 = '' query_server_str2 = '' if query_server1.__len__() > 0 : query_server_str1 = ' and server_id in (%s)' % (','.join(query_server1)) if query_server2.__len__() > 0 : query_server_str2 = ' and server_id in (%s)' % (','.join(query_server2)) page_size = int(request.GET.get('page_size',15)) page_num = int(request.GET.get('page_num', '1')) if page_num < 1: page_num = 1 spos = (page_num - 1) * page_size if query_statistic_item.__len__() == 1: count_statistic = ''.join(query_statistic_item) elif query_statistic_item.__len__() > 1: count_statistic = ','.join(query_statistic_item) else: count_statistic = ','.join(statistic_results) date_format = '%%Y-%%m-%%d' chart_format = '%Y-%m-%d' def_date = request.GET.get("def_date","") minTickSize = [1,"day"] if def_date == 'day': pass elif def_date == 'month': date_format = '%%Y-%%m' chart_format = '%Y-%m' minTickSize = [1,"month"] elif def_date == 'year': date_format = '%%Y' chart_format = '%Y' minTickSize = [1,"year"] cursor = connection.cursor() select_str1 = 'DATE_FORMAT(result_time,"%s") AS `date`'% date_format select_str2 = 'DATE_FORMAT(result_time,"%s") AS `date`'% date_format select_str12 = 'a.`date`' for item in query_statistic_item: select_str1 += ',sum(case when `statistic_id`=%s then result else 0 end) item%s' % (item, item) select_str2 += ',sum(case when `statistic_id`=%s then result else 0 end) item%s' % (item, item) select_str12 += ',(a.`item%s`-b.`item%s`) item%s' % (item,item,item) if select_op == '0': query_sql1 = 'select %s from result where statistic_id in(%s)%s%s group by `date` ORDER BY `date` DESC ' % (select_str1, count_statistic, query_server_str1, query_date) query_sql2 = 'select %s from result where statistic_id in(%s)%s%s group by `date` ORDER BY `date` DESC ' % (select_str2, count_statistic, query_server_str2, query_date) else: query_sql1 = 'select %s from result where statistic_id in(%s)%s%s group by `date` ORDER BY `date` DESC ' % (select_str1, count_statistic, query_channel_1, query_date) query_sql2 = 'select %s from result where statistic_id in(%s)%s%s group by `date` ORDER BY `date` DESC ' % (select_str2, count_statistic, query_channel_2, query_date) query_sql = 'SELECT %s from (%s) a join (%s) b ON a.`date` = b.`date`'%(select_str12,query_sql1,query_sql2) count_sql = 'select count(*) result from (%s) newTable WHERE 1 %s' % (query_sql, '') cursor.execute(count_sql) total_record = int(cursor.fetchone()[0]) list_record = [] if total_record > 0: query_sql = query_sql + ' ' + 'LIMIT %s,%s'%(spos,page_size) cursor.execute(query_sql) list_record = cursor.fetchall() #cursor.close() # print(total_record,query_sql) if show_type == 'list': template = 'log/result_analyse.html' else: template = 'log/result_query_chart.html' list_record_arr = {} i = 1 tmp_item = [] for item_result in join_results: if item_result[0] in query_statistic_item: tmp_item = [] for item in list_record: item = list(item) item[0] = int(time.mktime(datetime.datetime.strptime(str(item[0]),"%s"%chart_format).timetuple())) * 1000; tmp_item.append([item[0],int(item[i])]) list_record_arr[item_result[1]] = tmp_item i = i+1 for key,val in list_record_arr.items(): list_record_arr[key] = sorted(val) list_record_arr = str(list_record_arr).replace('(', '[').replace(')', ']').replace('L', '') list_record_arr = str(list_record_arr).replace('u\'', '\'') time_slot = 86400000 if def_date == 'day': d1 = datetime.datetime(int(sdate.split("-")[0]),int(sdate.split("-")[1]),int(sdate.split("-")[2])) d2 = datetime.datetime(int(edate.split("-")[0]),int(edate.split("-")[1]),int(edate.split("-")[2])) days = ( d2 - d1).days +1 time_slot = Result().cmp_time(days) elif def_date == 'month': time_slot = 86400000 * 30 elif def_date == 'year': time_slot = 86400000 * 30 * 12 #处理 导出文件 exprot = int(request.GET.get('exprot', '0')) close_export = int(request.GET.get('close_export', '0')) clear_export_old_file = int(request.GET.get('clear_export_old_file', '0')) if 0< exprot: query_exprot = QueryExprot() file_name = ''.join(query_statistic_item) file_name = file_name+'___'+sdate.replace('-','').replace(':','')+'___'+edate.replace('-','').replace(':','') #session ID session_id = request.COOKIES.get('sessionid') return query_exprot.gene_file(list_record, [u'时间']+list_statistic_name, file_name, page_num, page_size, total_record, exprot, close_export, clear_export_old_file, session_id) parg = {} parg["usm"] = usm parg["query_id"] = query_id parg["list_statistic"] = list_statistic parg["list_group"] = list_group parg["base_group_id"] = base_group_id parg["op_group_id"] = op_group_id parg["list_server_base"] = list_server_base parg["list_server_op"] = list_server_op parg["list_channel"] = list_channel parg["base_server"] = base_server parg["op_server"] = op_server parg["sdate"] = sdate parg["edate"] = edate parg["list_statistic_sort"] = list_statistic_sort parg["list_record"] = list_record parg["exec_interval"] = exec_interval parg["list_record_arr"] = list_record_arr parg["list_statistic_name"] = list_statistic_name parg["page_num"] = page_num parg["page_size"] = page_size parg["total_record"] = total_record parg["select_op"] = select_op parg["canSelectServer"] = canSelectServer parg["channel_1"] = channel_1 parg["channel_2"] = channel_2 parg["def_date"] = def_date parg["minTickSize"] = minTickSize parg["join_results"] = join_results parg["time_slot"] = time_slot parg["chart_format"] = chart_format parg["query_item"] = query_item return render_to_response(template, parg)
def result_query(request, query_id=0, show_type='list'): query_id = int(query_id) tmp_group_id = request.GET.get('group_id', 0) group_id = 0 try: group_id = int(tmp_group_id) except: pass query_channel = request.GET.getlist('c')#channel_id usm = UserStateManager(request) the_user = usm.get_the_user() if usm.current_userRole_is_root(): list_channel = center_cache.get_channel_list() else: list_channel = center_cache.get_user_channel_list(the_user) for item1 in list_channel: if query_channel.__len__() > 0: if str(item1.id) in query_channel: item1.is_show = 1 else: item1.is_show = 1 query_server = request.GET.getlist('s')#server_id the_query = QueryResult.objects.get(id=query_id) list_statistic = the_query.statistic.all() list_statistic_sort = get_statistic_in_query(query_id)#获取根据关联表ip排序的数据 join_results = [] list_statistic_name = [] exec_interval = 0 item_results = [] for item in list_statistic_sort: exec_interval = item[2]#exec_interval join_results.append(str(item[0]))#id item_results.append([item[0], item[1]]) #处理统计数据 list_group = [] if usm.current_userRole_is_root(): list_group = Group.objects.all() if 0 != group_id: list_server = Group.objects.get(id = group_id).server.all() else: list_server = get_server_list() else: list_server = the_user.server.all().order_by("id") #if canSelectServer: for serverItem in list_server: if len(query_server) > 0: if str(serverItem.id) in query_server: serverItem.is_show = 1 else: serverItem.is_show = 1 sdate = request.GET.get('sdate', '') edate = request.GET.get('edate', '') # print(sdate,edate) if query_channel.__len__() == 0 and not usm.current_userRole_is_root(): query_channel = [] for item in list_channel: query_channel.append(str(item.id)) if query_server.__len__() == 0 and not usm.current_userRole_is_root(): query_server = [] for item in list_server: query_server.append(str(item.id)) query_channel_str = '' if query_channel.__len__() > 0 : query_channel_str = ' and channel_id in (%s)' % (','.join(query_channel)) query_server_str = '' if query_server.__len__() > 0 : query_server_str = ' and server_id in (%s)' % (','.join(query_server)) page_size = 20 page_num = int(request.GET.get('page_num', '1')) if page_num < 1: page_num = 1 spos = (page_num - 1) * page_size date_format = '%%Y-%%m-%%d' chart_format = '%Y-%m-%d' time_slot = 86400000 charts_type = 'spline' def_date = request.GET.get("def_date","") now = datetime.datetime.now() if sdate and edate: d1 = datetime.datetime(int(sdate.split("-")[0]),int(sdate.split("-")[1]),int(sdate.split("-")[2])) d2 = datetime.datetime(int(edate.split("-")[0]),int(edate.split("-")[1]),int(edate.split("-")[2])) days = ( d2 - d1).days + 1 if days == 1: charts_type = 'column' time_slot = Result().cmp_time(days) else: sdate = now.strftime('%Y-%m-01') edate = now.strftime('%Y-%m-%d') if def_date == 'day': pass elif def_date == 'month': date_format = '%%Y-%%m' chart_format = '%Y-%m' time_slot = 30 * 86400000 elif def_date == 'year': date_format = '%%Y' chart_format = '%Y' time_slot = 12 * 30 * 86400000 edate = datetime.datetime.strptime(edate, '%Y-%m-%d') edate = edate + datetime.timedelta(days=1) sdate = datetime.datetime.strptime(sdate, '%Y-%m-%d') query_date = ' AND `result_time` >= \'%s\' AND `result_time` < \'%s\''%(sdate, edate) cursor = connection.cursor() count_sql = 'select count(distinct DATE_FORMAT(result_time,"%s")) result from result where statistic_id in(%s)%s%s%s' % (date_format,','.join(join_results), query_server_str, query_channel_str, query_date) cursor.execute(count_sql) total_record = int(cursor.fetchone()[0]) list_record = [] if total_record > 0: select_str = 'DATE_FORMAT(result_time,"%s") AS `date`'% date_format for item in join_results: select_str += ',sum(case when `statistic_id`=%s then result else 0 end) item%s' % (item, item) query_sql = 'select %s from result where statistic_id in(%s)%s%s%s group by `date` order by `date` DESC limit %d,%d' % (select_str, ','.join(join_results), query_server_str, query_channel_str, query_date, spos, page_size) print(query_sql) cursor.execute(query_sql) list_record = cursor.fetchall() #cursor.close() #print total_record #print '------------------' #print query_sql if show_type == 'list': template = 'log/result_query.html' else: template = 'log/result_query_chart.html' list_record_arr = {} i = 1 tmp_item = [] for item_result in item_results: tmp_item = [] for item in list_record: item = list(item) item[0] = int(time.mktime(datetime.datetime.strptime(str(item[0]),"%s"%chart_format).timetuple())) * 1000; tmp_item.append([item[0],int(item[i])]) list_record_arr[item_result[1]] = tmp_item i = i+1 for key,val in list_record_arr.items(): list_record_arr[key] = sorted(val) list_record_arr = str(list_record_arr).replace('(', '[').replace(')', ']').replace('L', '') list_record_arr = str(list_record_arr).replace('u\'', '\'') list_statistic_name = str(list_statistic_name).replace('u\'', '\'') parg = {} parg["usm"] = usm parg["query_id"] = query_id parg["list_statistic"] = list_statistic parg["list_group"] = list_group parg["group_id"] = group_id parg["list_server"] = list_server parg["list_channel"] = list_channel parg["sdate"] = sdate.strftime('%Y-%m-%d') edate = edate - datetime.timedelta(days = - 1) parg["edate"] = edate.strftime('%Y-%m-%d') parg["list_statistic_sort"] = list_statistic_sort parg["list_record"] = list_record parg["exec_interval"] = exec_interval parg["list_record_arr"] = list_record_arr parg["list_statistic_name"] = list_statistic_name if query_channel.__len__() == 1: parg["channel_id"] = int(query_channel[0]) parg["page_num"] = page_num parg["page_size"] = page_size parg["total_record"] = total_record parg["def_date"] = def_date parg["time_slot"] = time_slot parg["charts_type"] = charts_type parg["chart_format"] = chart_format return render_to_response(template, parg)
def result_list(request, statistic_id=0, show_type='list'): statistic_id = int(statistic_id) if 0 == statistic_id: statistic_id = int(request.GET.get('id', request.POST.get('id', 0))) statistic_type = 0#int(statistic_type) the_date = datetime.datetime.now() sdate = request.GET.get('sdate', the_date.strftime('%Y-%m-1')) edate = request.GET.get('edate', the_date.strftime('%Y-%m-%d')) query_channel = request.GET.getlist('c')#channel_id query_server = request.GET.getlist('s')#server_id list_record = [] usm = UserStateManager(request) the_user = usm.get_the_user() statistic = None if statistic_id > 0: statistic = Statistic.objects.get(id=statistic_id) log_def = LogDefine.objects.get(id = statistic.log_type) if statistic == None or log_def == None: return HttpResponseRedirect('/statistic/list') canSelectServer = True #if the_log_in_center(log_def): #check_user #canSelectServer = False if usm.current_userRole_is_root(): list_server = get_server_list() else: list_server = the_user.server.all().order_by("id") if canSelectServer: for serverItem in list_server: if len(query_server) > 0: if str(serverItem.id) in query_server: serverItem.is_show = 1 else: serverItem.is_show = 1 if usm.current_userRole_is_root(): list_channel = center_cache.get_channel_list() else: list_channel = center_cache.get_user_channel_list(the_user) #新增激活特殊处理, 因为新增激活没有服务器id if statistic_id != 1: if query_server.__len__() <= 0: query_server = [] for item in list_server: query_server.append(str(item.id)) channel_condition = True if query_channel.__len__() == 0: query_channel = [] for item in list_channel: query_channel.append(str(item.id)) for item1 in list_channel: if len(query_channel) > 0: if str(item1.id) in query_channel: item1.is_show = 1 else: item1.is_show = 0#取消默认全选 -zhenwei 2012-10-24 query_date = '' try: if sdate != '': sdate = datetime.datetime.strptime(sdate, '%Y-%m-%d').strftime('%Y-%m-%d') query_date = ' and result_time>=\'%s\'' % sdate if edate != '': if query_date != '': query_date += ' and ' edate = datetime.datetime.strptime(edate, '%Y-%m-%d').strftime('%Y-%m-%d')# %H:%M:%S query_date += ' result_time<=\'%s\'' % edate except: sdate = '' edate = '' query_channel_str = '' server_condition = True if (usm.user_server_count() == 0 or usm.is_Administrator(the_user)) and (list_server.__len__() == query_server.__len__()): server_condition = False if statistic_id == 1:#新增激活特殊处理,不筛选服务器 server_condition = False if (usm.user_channel_count() == 0 or usm.is_Administrator(the_user)) and (list_channel.__len__() == query_channel.__len__()): channel_condition = False if channel_condition: query_channel_str = ' and channel_id in (%s)' % (','.join(query_channel)) query_server_str = '' if server_condition: query_server_str = ' and server_id in (%s)' % (','.join(query_server)) page_size = 20 page_num = int(request.GET.get('page_num', '1')) if page_num < 1: page_num = 1 spos = (page_num - 1) * page_size statistic_types = [{'id':0, 'name':'默认统计', 'key':''}, {'id':1, 'name':'按小时', 'key':'hour'}, {'id':2, 'name':'按星期', 'key':'week'}, {'id':3, 'name':'按日数', 'key':'day'}, {'id':4, 'name':'按月数', 'key':'month'}, {'id':5, 'name':'按季度', 'key':'quarter'}]#{'id':5,'name':'按年份','key':'year'} statistic_type_str = statistic_types[statistic_type]['key'] list_statistic = Statistic.objects.using('read').filter(log_type=statistic.log_type) cursor = connection.cursor() count_sql = 'select count(distinct result_time) as result from result where statistic_id=%s%s%s%s' % (statistic_id, query_server_str, query_channel_str, query_date) cursor.execute(count_sql) total_record = int(cursor.fetchone()[0]) #raise Exception, count_sql #cursor.close() list_record_arr = {} if total_record > 0 : list_record = Result.objects.raw('select id,statistic_id,result_time,sum(result) result from result where statistic_id=%s%s%s%s group by result_time ORDER BY result_time DESC limit %d,%d' % (statistic_id, query_server_str, query_channel_str, query_date, spos, page_size)) tmp_item = [] temp = 0 for item in list_record: temp = int(time.mktime(datetime.datetime.strptime(str(item.result_time),"%s"%'%Y-%m-%d %H:%M:%S').timetuple())) * 1000; tmp_item.append([temp,int(item.result)]) list_record_arr[statistic.name] = tmp_item for key,val in list_record_arr.items(): list_record_arr[key] = sorted(val) list_record_arr = str(list_record_arr).replace('(', '[').replace(')', ']').replace('L', '') list_record_arr = str(list_record_arr).replace('u\'', '\'') if show_type == 'list': template = 'log/result_list.html' else: template = 'log/result_chart.html' parg = {} parg["statistic"] = statistic parg["usm"] = usm parg["statistic_id"] = statistic_id parg["list_statistic"] = list_statistic parg["canSelectServer"] = canSelectServer parg["list_server"] = list_server parg["list_channel"] = list_channel parg["sdate"] = sdate parg["edate"] = edate parg["statistic_type"] = statistic_type parg["list_record"] = list_record parg["page_num"] = page_num parg["page_size"] = page_size parg["total_record"] = total_record parg["list_record_arr"] = list_record_arr if query_channel.__len__() == 1: parg["channel_id"] = int(query_channel[0]) return render_to_response(template, parg)
def player_list(request, server_id=0): page_size = 30 page_num = int(request.GET.get("page_num", "1")) is_block = int(request.GET.get("block", 0)) group_id = int(request.GET.get("group_id", 0)) post_back = int(request.GET.get('post_back', '0')) list_group = center_cache.get_group_list() if (page_num < 1): page_num = 1 usm = UserStateManager(request) the_user = usm.get_the_user() list_channel = center_cache.get_channel_list() itemChannelList = {} for item in list_channel: itemChannelList[item.id] = item.name list_group_server = [] if group_id != 0: list_group_server = center_cache.get_group_server_list(group_id) if usm.current_userRole_is_root(): list_server = center_cache.get_server_list() else: list_server = center_cache.get_user_server_list(the_user) tmp_list_server = [] if 0 != list_group_server.__len__(): for item in list_group_server: if list_server.__contains__(item): tmp_list_server.append(item) list_server = tmp_list_server itemServerList = {} for item in list_server: itemServerList[item.id] = item.name player_key = request.GET.get('key', '') key_type = request.GET.get('key_type', '0') user_type = int(request.GET.get('user_type', '-1')) channel_id = int(request.session.get('channelId', '0')) server_id = int(request.GET.get("server_id", "0")) if server_id <= 0: server_id = int(request.session.get("serverId", "0")) if server_id <= 0 and len(list_server) > 0: server_id = list_server[0].id #账号状态 status_condition = 0 if is_block == 1: status_condition = -1 total_record = 0 player_list = [] player_list1 = [] if 0 != post_back and server_id > 0: conn = getConn(server_id) cursor = conn.cursor() query = [] query.append("status=%d" % status_condition) if channel_id > 0: query.append('channel_id=%d' % channel_id) if player_key != "": if key_type == '0': query.append('player_id=\'%s\'' % player_key) elif key_type == '1': query.append('player_name like \'%s%%\'' % player_key.encode('utf-8')) elif key_type == '2': query.append('link_key=\'%s\'' % player_key) elif key_type == '3': query.append('mobile_key=\'%s\'' % player_key) if user_type > -1: query.append('user_type=%d' % player_key) if not usm.current_userRole_is_root(): channel_list = center_cache.get_user_channel_list(the_user) channel_id_list_query = ' channel_id in (%s) ' % ','.join( [str(item.id) for item in channel_list]) query.append(channel_id_list_query) if len(query) > 0: sql1 = 'select count(1) from player_%d where %s' % ( server_id, ' and '.join(query)) sql2 = 'select player_id,player_name,channel_id,user_type,link_key,login_num,mobile_key,last_time,create_time,status from player_%d where %s order by id desc limit %d,%d' % ( server_id, ' and '.join(query), (page_num - 1) * page_size, page_num * page_size) else: sql1 = 'select count(1) from player_%d' % server_id sql2 = 'select player_id,player_name,channel_id,user_type,link_key,login_num,mobile_key,last_time,create_time,status from player_%d order by id desc limit %d,%d' % ( server_id, (page_num - 1) * page_size, page_num * page_size) print(sql1, sql2) cursor.execute(sql1) count_list = cursor.fetchone() total_record = int(count_list[0]) if total_record > 0: cursor.execute(sql2) player_list1 = cursor.fetchall() user_type_name = { 0: '游爱', 1: '当乐', 2: 'UC', 3: '91', 4: '云游', 5: '飞流', 6: '乐逗', 8: '小虎', 9: '4399', 10: 'facebook', 11: 'qq' } for item in player_list1: item = list(item) item[2] = itemChannelList.get(int(item[2]), item[2]) item[3] = user_type_name.get(int(item[3]), item[3]) player_list.append(item) cursor.close() parg = {} parg["server_id"] = server_id parg["list_group"] = list_group parg["list_server"] = list_server parg["player_key"] = player_key parg["server_id"] = server_id parg["player_list"] = player_list parg["is_block"] = is_block parg["usm"] = usm parg["page_num"] = page_num parg["page_size"] = page_size parg["total_record"] = total_record return render_to_response('player/player_list.html', parg)
def charts_result_pie(request, query_id=0, server_channel='server', charts_type='pie', title='饼图'): title = title query_id = int(query_id) model = ChartsDefine.objects.get(id=query_id) list_statistic_sort = get_statistic_in_query(int( model.query_result_id)) #获取根据关联表ip排序的数据 join_results = [] item_results = [] now = datetime.datetime.now() sdate = request.GET.get('sdate', '') edate = request.GET.get('edate', '') query_item = int(request.GET.get('query_item', '0')) query_type = int(request.GET.get('query_type', '0')) request_server_list = request.GET.getlist('server_id') request_channel_list = request.GET.getlist('channel_id') display_type = int(request.GET.get('d_type', 1)) if request_server_list.__len__() >= 100: return HttpResponse(u'非法请求') if request_channel_list.__len__() >= 100: return HttpResponse(u'非法请求') if not sdate and not edate: sdate = now.strftime('%Y-%m-01 00:00:00') edate = now.strftime('%Y-%m-%d 23:59:59') query_date = ' AND r.`create_time` >= \'%s\' AND r.`create_time` <= \'%s\'' % ( sdate, edate) usm = UserStateManager(request) the_user = usm.get_the_user() server_list = [] channel_list = [] if usm.current_userRole_is_root(): server_list = center_cache.get_server_list() channel_list = center_cache.get_channel_list() else: server_list = center_cache.get_user_server_list(the_user) channel_list = center_cache.get_user_channel_list(the_user) if not usm.current_userRole_is_root: if 0 == server_list.__len__(): return HttpResponse(u'没有权限') if 0 == channel_list.__len__(): return HttpResponse(u'没有权限') #限制服务器和渠道选择的数量 limit_server_count = 10 limit_channel_count = 10 tmp_index = 0 if 0 == request_server_list.__len__(): request_server_list = [] for item in server_list: if tmp_index >= limit_server_count: break request_server_list.append(str(item.id)) tmp_index = tmp_index + 1 if 0 == request_channel_list.__len__(): tmp_index = 0 for item in channel_list: if tmp_index >= limit_channel_count: break request_channel_list.append(str(item.id)) tmp_index = tmp_index + 1 has_server_count = 0 for item in server_list: for server_id in request_server_list: server_id = int(server_id) if item.id == server_id: has_server_count = has_server_count + 1 item.selected = True has_channel_count = 0 for item in channel_list: for channel_id in request_channel_list: channel_id = int(channel_id) if item.id == channel_id: has_channel_count = has_channel_count + 1 item.selected = True if (has_server_count != request_server_list.__len__() or has_server_count != request_channel_list.__len__() ) and not usm.current_userRole_is_root: return HttpResponse(u'没有权限') query_server_id = ' AND r.server_id IN (%s)' % ','.join( request_server_list) query_channel_id = ' AND r.channel_id IN (%s)' % ','.join( request_channel_list) q_str = '' limit_count = 0 for item in list_statistic_sort: join_results.append(int(item[0])) #id item_results.append([item[0], item[1]]) cursor = connection.cursor() if query_type == 0: select_str = 's.`name`' q_str = query_server_id limit_count = limit_server_count else: select_str = 'c.`name`' q_str = query_channel_id limit_count = limit_channel_count if query_item != 0: select_str += ',sum(case when r.`statistic_id`=%d then result else 0 end) item' % ( query_item) else: select_str += ',sum(case when r.`statistic_id`=%d then result else 0 end) item' % ( join_results[0]) query_item = int(join_results[0]) query_item_name = Statistic.objects.values('name').get(id=query_item) if query_type == 0: query_sql = 'select %s from result r JOIN `servers` s ON r.`server_id` = s.`id` where r.`statistic_id` = %d %s %s GROUP BY r.`server_id` ORDER BY `item` DESC LIMIT %s' % ( select_str, query_item, query_date, q_str, limit_count) else: query_sql = 'select %s from result r JOIN `channel` c ON r.`channel_id` = c.`id` where r.`statistic_id` = %d %s %s GROUP BY r.`channel_id` ORDER BY `item` DESC LIMIT %s' % ( select_str, query_item, query_date, q_str, limit_count) count_sql = 'select count(0) result from (%s) newTable WHERE 1' % ( query_sql) cursor.execute(count_sql) total_record = int(cursor.fetchone()[0]) print query_sql list_record = [] if total_record > 0: cursor.execute(query_sql) list_record = cursor.fetchall() parg = {} template = '' if 1 == display_type: template = 'charts/result_pie.html' data = [] total = 0 if total_record > 0: for val in list_record: total += int(val[1]) print 'total', total for item in list_record: item = list(item) item[0] = item[0] data.append(['%s' % item[0], float(item[1]) / total]) data = str(data).replace('(', '[').replace(')', ']').replace('L', '') data = str(data).replace('u\'', '\'') else: charts_type = 'bar' template = 'charts/result_top.html' title = "TOP10" data = [] xAxis = [] if total_record > 0: for item in list_record: item = list(item) xAxis.append('%s' % item[0]) data.append(int(item[1])) xAxis = str(xAxis).replace('(', '[').replace(')', ']').replace('L', '') xAxis = str(xAxis).replace('u\'', '\'') parg["xAxis"] = xAxis parg["server_list"] = server_list parg["channel_list"] = channel_list parg["title"] = title parg["item_results"] = item_results parg["data"] = data parg["query_item"] = query_item parg["query_item_name"] = query_item_name parg["charts_type"] = charts_type parg["query_id"] = query_id parg["sdate"] = sdate[0:10] parg["edate"] = edate[0:10] parg["query_type"] = query_type parg["d_type"] = display_type return render_to_response(template, parg)