def user_pay(request): query_channel = request.POST.getlist('c') #channel_id user_id = request.POST.get("user_id", '') user_name = request.POST.get("user_name", "") sdate = request.POST.get("sdate", "") edate = request.POST.get("edate", "") query_server = request.POST.getlist('s') #server_id page_num = int(request.GET.get("page_num", "1")) if user_id == "": user_id = "0" user_id = int(user_id) page_size = 50 list_channel = center_cache.get_channel_list() 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 list_server = get_server_list() 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 query_where = " a.pay_status=4" try: if sdate != "": sdate = datetime.datetime.strptime( sdate, '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S') query_where += " and a.last_time>='%s'" % sdate if edate != "": edate = datetime.datetime.strptime( edate, '%Y-%m-%d %H:%M:%S').strftime('%Y-%m-%d %H:%M:%S') query_where += " and a.last_time<='%s'" % edate except: sdate = "" edate = "" if query_channel.__len__() > 0 and query_channel.__len__( ) != list_channel.__len__(): query_where += ' and c.id in(%s)' % (','.join(query_channel)) if query_server.__len__() > 0 and query_server.__len__( ) != list_server.__len__(): query_where += " and a.server_id in (%s)" % (','.join(query_server)) if user_id > 0: query_where += " and a.pay_user=%s" % user_id user_name = user_name.strip() if user_name != "": query_where += " and b.username='******'" % user_name # query_user_list=User.objects.filter(username=username,channel_key=channel.0key) # if len(query_user_list)>0: # query_where+=" and pay_user=%s"%query_user_list[0].id query_pagesize = " limit %s,%s" % ( (page_num - 1) * page_size, page_num * page_size) query_sql = "select a.pay_user,b.username,sum(a.pay_amount) total_amount from pay_action a,users b,channel c where a.pay_user=b.id and a.channel_id=c.id and %s group by pay_user order by total_amount desc %s" % ( query_where, query_pagesize) query_count = "select count(distinct a.pay_user) from pay_action a,users b,channel c where a.pay_user=b.id and a.channel_id=c.id and %s" % query_where print "channel_pay_rank_list:" print query_count print query_sql cursor = connection.cursor() cursor.execute(query_count) total_record = int(cursor.fetchone()[0]) list_record = [] if total_record > 0: cursor.execute(query_sql) list_record = cursor.fetchall() #cursor.close() if user_id <= 0: user_id = "" parg = {} parg["list_server"] = list_server parg["list_channel"] = list_channel parg["user_id"] = user_id parg["user_name"] = user_name parg["sdate"] = sdate parg["edate"] = edate parg["list_record"] = list_record parg["page_num"] = page_num parg["page_size"] = page_size parg["total_record"] = total_record return render_to_response('pay/user_pay.html', parg)
def update_pay_action_channel(request): sdate = request.GET.get('sdate', request.POST.get('sdate', '')) edate = request.GET.get('edate', request.POST.get('edate', '')) sid = int(request.GET.get('sid', request.POST.get('sid', 0))) eid = int(request.GET.get('eid', request.POST.get('eid', 0))) page_size = 100 if eid - sid > page_size: eid = sid + page_size where_sql = ' (channel_id = 0 OR channel_id IS NULL) AND pay_status >= 4 AND pay_amount > 0 ' if '' != sdate and '' != edate: sdate = datetime.datetime.strptime(sdate, '%Y-%m-%d').strftime('%Y-%m-%d') edate = datetime.datetime.strptime(edate, '%Y-%m-%d').strftime('%Y-%m-%d') where_sql = where_sql + " AND last_time BETWEEN '%s' AND '%s' " % ( sdate, edate) center_con = connections['write'] center_cur = center_con.cursor() pargs = {"status": 1} if 0 == sid or 0 == eid: count_sql = 'SELECT MIN(id), MAX(id) FROM pay_action WHERE %s ' % where_sql center_cur.execute(count_sql) result_item = center_cur.fetchone() min_id = 0 max_id = 0 if None != result_item: if None != result_item[0] and None != result_item[0]: min_id, max_id = int(result_item[0]), int(result_item[1]) pargs["min_id"] = min_id pargs["max_id"] = max_id pargs["page_size"] = page_size sid = min_id eid = sid + page_size sql = 'SELECT id,server_id,channel_key,pay_user FROM pay_action WHERE %s AND id BETWEEN %s AND %s ' % ( where_sql, sid, eid) center_cur.execute(sql) list_data = center_cur.fetchall() channel_list = get_channel_list() for item in list_data: item_id = item[0] server_id = item[1] channel_key = item[2] pay_user = item[3] channel_id = 0 if '' != channel_key and None != channel_key: for channel_item in channel_list: if channel_item.channel_key == channel_key: channel_id = channel_item.id if 0 == channel_id: con = None cur = None try: con = getConn(server_id) cur = con.cursor() tmp_sql = 'SELECT channel_id FROM player_%s WHERE player_id=%s' % ( server_id, pay_user) cur.execute(tmp_sql) list_player_result = cur.fetchall() if 0 < list_player_result.__len__(): channel_id = list_player_result[0][0] except: continue finally: if None != cur: cur.close() if None != con: con.close() if 0 != channel_id: update_sql = 'UPDATE pay_action SET channel_id=%s WHERE id=%s ' center_cur.execute(update_sql % (channel_id, item_id)) sid = eid + 1 eid = sid + page_size pargs["sid"] = sid pargs["eid"] = eid pargs["status"] = 0 return HttpResponse(json.dumps(pargs))
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 request.admin.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 request.admin.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 request.admin.is_root: query_channel = [] for item in list_channel: query_channel.append(str(item.id)) if query_server1.__len__() == 0 and not request.admin.is_root: query_server1 = [] for item in list_server_base: query_server1.append(str(item.id)) if query_server2.__len__() == 0 and not request.admin.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["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) g = lambda x, y: request.GET.get(x, request.POST.get(x, y)) gl = lambda x, y: request.GET.getlist(x, request.POST.getlist(x, y)) tmp_group_id = g('group_id', 0) group_id = 0 try: group_id = int(tmp_group_id) except: pass query_channel = gl('c', []) #channel_id usm = UserStateManager(request) the_user = usm.get_the_user() if request.admin.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 = gl('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 request.admin.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 = g('sdate', '') edate = g('edate', '') # print(sdate,edate) if query_channel.__len__() == 0 and not request.admin.is_root: query_channel = [] for item in list_channel: query_channel.append(str(item.id)) if query_server.__len__() == 0 and not request.admin.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 = 31 page_num = int(g('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 = g("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 = [] the_user = request.admin 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 request.admin.is_root: list_server = get_server_list() else: list_server = the_user.get_resource('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 request.admin.is_root: list_channel = center_cache.get_channel_list() else: list_channel = request.get_resource('channel') #新增激活特殊处理, 因为新增激活没有服务器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 (request.admin.get_resource('server').count() == 0 or request.admin.is_manager) and (list_server.__len__() == query_server.__len__()): server_condition = False if statistic_id == 1: #新增激活特殊处理,不筛选服务器 server_condition = False if (request.admin.get_resource('channel').count() == 0 or request.admin.is_manager) 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["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 user_list(request): channel_id = int(request.session.get('channelId', '0')) if channel_id > 0: channel = center_cache.get_channel_list() page_size = 30 page_num = int(request.GET.get('page_num', '1')) if page_num < 1: page_num = 1 user_type = int(request.GET.get('type', '-1')) user_key = request.GET.get('key', '') user_status = request.GET.get('select_status', '') model = User() query = Q() query = Q(user_type=0) if user_key != '': key_type = int(request.GET.get('key_type', '0')) if key_type == 0: try: query = query & Q(username=user_key) except: print('key_value has error') elif key_type == 2: query = query & Q(mobile_key=user_key) else: query = query & Q(username__icontains=user_key) if channel_id > 0: query = query & Q(channel_key=channel.key) if user_status: query = query & Q(status=user_status) list_record = [] if query: total_record = User.objects.using('read').filter(query).count() if total_record > 0: list_record = User.objects.using('read').filter( query)[(page_num - 1) * page_size:page_num * page_size] else: total_record = User.objects.using('read').count() if total_record > 0: list_record = User.objects.using('read').all()[(page_num - 1) * page_size:page_num * page_size] parg = {} parg["user_key"] = user_key parg["list_record"] = list_record parg["status_choices"] = User.STATUS_CHOICES parg["user_status"] = int(user_status) if user_status else 0 parg["request"] = request parg["page_num"] = page_num parg["page_size"] = page_size parg["total_record"] = total_record return render_to_response('player/user_list.html', 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 the_user = request.admin 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 request.admin.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 player_keys = [player_key] if key_type == '0' and player_key: if ',' in player_key: player_keys = [x.strip() for x in player_key.split(',') if x] if server_id <= 0: server_id = int(player_keys[0]) >> 20 #账号状态 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 in (%s)' % ','.join(player_keys)) 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 request.admin.is_root: channel_count = request.admin.get_resource('channel').count() if request > 0: channel_list = request.admin.get_resource('channel') 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_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_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["player_list"] = player_list parg["is_block"] = is_block parg['key_type'] = key_type parg["page_num"] = page_num parg["page_size"] = page_size parg["total_record"] = total_record group_servers_dict = get_group_servers_dict(request) agent_channels_dict = get_agent_channels_dict(request) return render_to_response('player/player_list.html', locals())