def get_new_user_id_by_day(self, date, exist_tables):
new_id_map = {'google': 0, 'facebook': 0, 'dolphin': 0, 'total': 0}
date_str = get_date_str(date)
table_name = get_table_name('DolphinID_new', date)
if table_name in exist_tables:
select_str = 'select user_id,login_type from %s' % table_name
datas = self._mysql_conn.query_sql(select_str)
else:
datas = None
logger.info("the table %s do not exist" % table_name)
if datas:
for item in datas:
if item[1] == 11:
new_id_map['facebook'] += 1
elif item[1] == 10:
new_id_map['google'] += 1
elif item[1] == 0:
new_id_map['dolphin'] += 1
else:
print "not found the info %s" % item[1]
logger.info("not found the info %s" % item[1])
new_id_map['total'] = len(datas)
else:
new_id_map = {'google': 0, 'facebook': 0, 'dolphin': 0, 'total': 0}
return new_id_map
def get_operation_nginx(date):
date_str = get_date_str(date)
table_name = get_table_name("DolphinOPService_nginx", date)
data_sql_str = "select locale from %s where api_name='/api/2/provision.json'" % table_name
datas = _db_conn.query_sql(data_sql_str)
items = json.loads(datas[0][0])
data_list = []
for item in items:
data_dict = {}
count = items[item].get("count")
srv_err_count = items[item].get("srv_err_count")
ill_count = items[item].get("ill_count")
bad_count = items[item].get("bad_count")
data_dict["country"] = item
data_dict["count"] = count
data_dict["srv_err_count"] = srv_err_count
data_dict["cli_err_count"] = items[item].get("cli_err_count")
data_dict["good_count"] = items[item].get("good_count")
data_dict["ill_count"] = ill_count
data_dict["bad_count"] = bad_count
data_dict["avr_time"] = round((float(items[item].get("total_time")) / count), 3)
data_dict["max_time"] = items[item].get("max_time")
data_dict["total_time"] = items[item].get("total_time")
data_dict["nginx_avr_time"] = round((float(items[item].get("nginx_total_time", 0)) / count), 3)
data_dict["health_degree"] = round(
((count - srv_err_count * 1 - bad_count * 0.8 - ill_count * 0.2) / count * 100), 3
)
data_list.append(data_dict)
data_list = sorted(data_list, key=lambda x: x["avr_time"], reverse=True)
return data_list
def get_pushserver_cometd(date):
date_str = get_date_str(date)
table_name = get_table_name('DolphinPushServer_core_cometd', date)
sql_str = "select api_name, count, good_count, ok_count, ill_count, sick_count, bad_count,\
longest_exec_time, avr_exec_time from %s order by api_name" % table_name
datas = _db_conn.query_sql(sql_str)
return datas
def get_push_statistics(date):
date_suffix = get_date_str(date)
table_name = "DolphinPushServer_core_push_push_" + date_suffix
query_user_count = "select count(distinct user_id) from %s where src_did not like 'TRY%%'" % table_name
query_push_count = "select count(*) from %s where src_did not like 'TRY%%'" % table_name
query_userids = "select distinct user_id from %s where src_did not like 'TRY%%'" % table_name
global result_table
if _push_db_conn:
try:
user_count = _push_db_conn.query_sql(query_user_count)[0][0]
push_count = _push_db_conn.query_sql(query_push_count)[0][0]
except Exception, e:
user_count = 0
push_count = 0
result_table.setdefault(date_suffix, {})["user_count"] = user_count
current_table = result_table[date_suffix]
current_table["push_count"] = push_count
device_count = 0
user_ids = _push_db_conn.query_sql(query_userids)
try:
for user_id in user_ids:
device_count += get_user_device_count(user_id[0])
except Exception, e:
print e
pass
def get_push_active_count(date):
date_suffix = get_date_str(date)
table_name = "DolphinPushServer_core_handshake_" + date_suffix
query_count = " select count(distinct device_id) from %s" % table_name
if _push_db_conn:
try:
active_user_count = _push_db_conn.query_sql(query_count)[0][0]
except Exception, e:
active_user_count = "暂无数据"
def get_offline_push_count(date):
date_suffix = get_date_str(date)
table_name = "DolphinPushServer_core_push_offline_" + date_suffix
query_count = " select count(*) from %s" % table_name
if _push_db_conn:
try:
offline_push_count = _push_db_conn.query_sql(query_count)[0][0]
except Exception, e:
offline_push_count = "暂无数据"
def get_user_id_by_month(self, date):
month_new_id_map = {}
temp_date = date
exist_tables = self.get_exist_tables()
for i in range(28):
date_str = get_date_str(temp_date)
day_map = self.get_new_user_id_by_day(temp_date, exist_tables)
month_new_id_map[date_str] = day_map
temp_date = temp_date - datetime.timedelta(1)
items = month_new_id_map.items()
items.sort(reverse=True)
return items
def get_uwsgi_hariki(date):
date_str = get_date_str(date)
table_name = get_table_name('Uwsgi_hariki', date)
data_sql_str = "select api_name, hariki_time from %s order by hariki_time, api_name" % table_name
count_sql_str = "select count(*) from %s" % table_name
datas = _db_conn.query_sql(data_sql_str)
count = _db_conn.query_sql(count_sql_str)
data_map = {}
for name, time in datas:
time_list = re.split('[: ]', time)
if time_list[2]:
hour = time_list[3]
else:
hour = time_list[4]
if data_map.has_key(hour):
if data_map[hour].has_key(name):
data_map[hour][name] += 1
else:
data_map[hour][name] = 1
else:
data_map[hour] = {}
data_map[hour][name] = 1
return data_map, count[0][0]
def get_provision_missmatch_html(date):
html_str = ""
date_str = get_date_str(date)
datas = get_provision_missmatch(date)
html_str += get_provision_missmatch_table_str(datas, date_str)
return html_str
def get_provision_missmatch(date):
date_str = get_date_str(date)
table_name = get_table_name('Provision_locale', date)
data_sql_str = "select country,count,miss_match from %s order by (miss_match/count) desc" % table_name
datas = _db_conn.query_sql(data_sql_str)
return datas
def get_uwsgi_hariki_html(date):
html_str = ""
date_str = get_date_str(date)
data_map, count = get_uwsgi_hariki(date)
html_str += get_uwsgi_hariki_table_str(data_map, date_str, count)
return html_str
def get_operation_nginx_html(date):
html_str = ""
date_str = get_date_str(date)
datas = get_operation_nginx(date)
html_str += get_operation_nginx_table_str(datas, date_str)
return html_str
def get_pushserver_cometd_html(date):
html_str = ""
date_str = get_date_str(date)
datas = get_pushserver_cometd(date)
html_str += get_pushserver_cometd_table_str(datas, date_str)
return html_str
def get_sync_count_by_day(date):
global result_map
day_result = {"total_user": 0,
"bookmark": {"total": 0, "avg": 0.0, "median": 0},
"history": {"total": 0, "avg": 0.0, "median": 0},
"tab": {"total": 0, "avg": 0.0, "median": 0},
"desktop": {"total": 0, "avg": 0.0, "user": 0, "median": 0}}
table_name = get_table_name("DolphinPushServer_core_push_sync", date)
query_str = 'select push_type,count(distinct user_id),sum(times) from %s group by push_type' % table_name
query_total_user = '******' % table_name
query_total_count = 'select count(*) from %s group by push_type order by push_type' % table_name
if _sync_db_conn:
query_result = _sync_db_conn.query_sql(query_str)
total_count = _sync_db_conn.query_sql(query_total_count)
if query_result:
for item in query_result:
if item[0] == 1:
day_result["bookmark"]["total"] = item[2]
day_result["bookmark"]["avg"] = round(
float(item[2]) / item[1], 3)
mid_count = int(total_count[0][0] - 1) / 2
query_median = 'select times from %s where push_type=1 order by times limit %d,1' % (
table_name, mid_count)
median = _sync_db_conn.query_sql(query_median)
day_result["bookmark"]["median"] = int(median[0][0])
elif item[0] == 2:
day_result["tab"]["total"] = item[2]
day_result["tab"]["avg"] = round(
float(item[2]) / item[1], 3)
mid_count = int(total_count[1][0] - 1) / 2
query_median = 'select times from %s where push_type=2 order by times limit %d,1' % (
table_name, mid_count)
median = _sync_db_conn.query_sql(query_median)
day_result["tab"]["median"] = int(median[0][0])
elif item[0] == 4:
day_result["history"]["total"] = item[2]
day_result["history"]["avg"] = round(
float(item[2]) / item[1], 3)
mid_count = int(total_count[2][0] - 1) / 2
query_median = 'select times from %s where push_type=4 order by times limit %d,1' % (
table_name, mid_count)
median = _sync_db_conn.query_sql(query_median)
day_result["history"]["median"] = int(median[0][0])
elif item[0] == 64 or item[0] == 128:
day_result["desktop"]["total"] += item[2]
day_result["desktop"]["user"] += item[1]
mid_count = int(
total_count[3][0] + total_count[4][0] - 1) / 2
query_median = 'select times from %s where push_type=64 or push_type=128 order by times limit %d,1' % (
table_name, mid_count)
median = _sync_db_conn.query_sql(query_median)
day_result["desktop"]["median"] = int(median[0][0])
if day_result["desktop"]["user"] != 0:
day_result["desktop"]["avg"] = round(
float(day_result["desktop"]["total"]) / day_result["desktop"]["user"], 3)
query_result = _sync_db_conn.query_sql(query_total_user)
if query_result:
day_result['total_user'] = query_result[0][0]
# print day_result
date_str = get_date_str(date)
result_map[date_str] = day_result
return day_result