def combine_where_clause(self, input_dict, use_channels=True, use_zoneids=True):
# only applied for standard input dict
is_legal_input(input_dict)
where_clause = ''
if len(input_dict['channel_list']) and use_channels:
where_clause = where_clause + 'and channel in (' + self.sql_value_str(input_dict['channel_list']) + ')'
if len(input_dict['zone_list']) and use_zoneids:
where_clause = where_clause + 'and zoneid in (' + self.sql_value_str(input_dict['zone_list']) + ')'
return where_clause
def tech_avglevel(input_dict):
data_dict = dict()
for y in Y_list:
data_dict[y] = dict()
head_name = "科技人均等级"
easy_mysql = EasyMysql.EasyMysql()
is_legal_input(input_dict)
date_list = [max(input_dict['date_list'])]
date_list = sorted(date_list)
if len(date_list) == 0:
msg = 'Not date selected, len(date_list)' + str(len(date_list))
raise RuntimeError(msg)
zoneid_list = input_dict['zone_list']
channel_list = input_dict['channel_list']
cursor = input_dict['cursor']
sql_where = ' where date =' + easy_mysql.sql_value_str(date_list)
if len(zoneid_list) > 0:
sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(
zoneid_list) + ') '
if len(channel_list) > 0:
sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(
channel_list) + ') '
sql_str = 'select mvip,count(uid) from(select uid,max(level) as mlevel,max(vip_level) as mvip from user_active ' + sql_where + ' group by uid) as a group by mvip;'
cursor.execute(sql_str)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
vip = int(rec[0])
user = int(rec[1])
data_dict['UserCount'][vip] = user
# sql_str = 'select techid,mvip,mtech,count(uid),sum(mtech)/count(uid) from (select uid,techid,max(viplevel) as mvip,max(techlevel) as mtech from TechInfo'+sql_where+' group by uid,techid) as a group by techid,mvip,mtech;'
# sql_str = 'select techid,mvip,count(uid),sum(mtech)/count(uid) from (select uid,techid,max(viplevel) as mvip,max(techlevel) as mtech from TechInfo '+sql_where+' group by uid,techid) as a group by techid,mvip;'
sql_str = 'select techid,viplevel,sum(peoplenum),sum(techlevel*peoplenum)/sum(peoplenum) from TechInfoNew ' + sql_where + ' and techlevel>0 group by techid,viplevel;'
cursor.execute(sql_str)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
data_dict[rec[0]][rec[1]] = rec[3]
for key in data_dict.keys():
if X_first[0] not in data_dict.keys():
data_dict[key]['trans'] = Y_trans[key]
res_dict = {
'data_dict': data_dict,
'X_list': X_list,
'Y_list': Y_list,
'head_name': head_name,
'X_trans': X_trans,
'default_value': default_value
}
# print(res_dict)
res_dict["note"] = "统计最后日的各VIP段的激活科技的人的平均等级*"
return res_dict
def get_diamond_times(input_dict):
global X_list
global X_trans
X_list = []
data_dict = dict()
for y in Y_list:
data_dict[y] = dict()
head_name = "钻石消耗次数"
easy_mysql = EasyMysql.EasyMysql()
is_legal_input(input_dict)
date_list = input_dict['date_list']
date_list = sorted(date_list)
if len(date_list) == 0:
msg = 'Not date selected, len(date_list)' + str(len(date_list))
raise RuntimeError(msg)
zoneid_list = input_dict['zone_list']
channel_list = input_dict['channel_list']
cursor = input_dict['cursor']
sql_where = ' where date in (' + easy_mysql.sql_value_str(date_list) + ') '
if len(zoneid_list) > 0:
sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(zoneid_list) + ') '
if len(channel_list) > 0:
sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(channel_list) + ') '
sql_str = 'select mvip,count(uid) from(select uid,max(level) as mlevel,max(vip_level) as mvip from user_active '+sql_where+' group by uid) as a group by mvip;'
cursor.execute(sql_str)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
vip = int(rec[0])
user = int(rec[1])
data_dict['UserCount'][vip]=user
X_list.append(vip)
X_list = sorted(list(set(X_list)))
X_listtrans =['翻译'] + map(lambda x: 'VIP' + str(x), X_list)
X_list = X_first + X_list
X_trans = dict(zip(X_list, X_listtrans))
sqlstr='select moneytype,mvip,count(distinct(a.uid)),count(a.uid),sum(cash),sum(totalcost) from (select uid,moneytype,sum(cash) as cash,sum(diamond) as diamond,sum(cash+diamond) as totalcost from diamond_detail'+sql_where+' and keyword="CostDiamond" group by uid,moneytype) as a inner join (select uid,max(vip_level) as mvip from user_active'+sql_where+' group by uid) as b on a.uid=b.uid group by moneytype,mvip; '
cursor.execute(sqlstr)
alldata = cursor.fetchall()
if alldata:
for rec in alldata:
if int(rec[0]) not in data_dict.keys():
data_dict[int(rec[0])] = dict()
data_dict[int(rec[0])][rec[1]] = rec[3]
for key in data_dict.keys():
if X_first[0] not in data_dict.keys():
data_dict[key]['trans'] = Y_trans.setdefault(key,key)
res_dict={'data_dict':data_dict,'X_list':X_list,'Y_list':Y_list,'head_name':head_name,'X_trans':X_trans,'default_value':default_value}
# print(res_dict)
res_dict['note'] = "*起始日期-终止日期的各VIP段的钻石消耗次数*"
return res_dict
def buy_oil_times(input_dict):
data_dict = dict()
for y in Y_list:
data_dict[y] = dict()
head_name = "VIP等级购买原油次数"
easy_mysql = EasyMysql.EasyMysql()
is_legal_input(input_dict)
date_list = input_dict['date_list']
date_list = sorted(date_list)
if len(date_list) == 0:
msg = 'Not date selected, len(date_list)' + str(len(date_list))
raise RuntimeError(msg)
zoneid_list = input_dict['zone_list']
channel_list = input_dict['channel_list']
cursor = input_dict['cursor']
sql_where = ' where date in(' + easy_mysql.sql_value_str(date_list) + ')'
if len(zoneid_list) > 0:
sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(
zoneid_list) + ') '
if len(channel_list) > 0:
sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(
channel_list) + ') '
sql_str = 'select mvip,count(uid) from(select uid,max(level) as mlevel,max(vip_level) as mvip from user_active ' + sql_where + ' group by uid) as a group by mvip;'
cursor.execute(sql_str)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
vip = int(rec[0])
user = int(rec[1])
data_dict['UserCount'][vip] = user
sql_str = 'select mvip,count(uid),sum(ttimes),sum(tcost) from(select uid,max(viplevel) as mvip,sum(times) as ttimes,sum(cash+diamond) as tcost from OilBuy ' + sql_where + ' group by uid) as a group by mvip;'
cursor.execute(sql_str)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
data_dict[Y_list[1]][rec[0]] = rec[2]
for key in data_dict.keys():
if X_first[0] not in data_dict.keys():
data_dict[key]['trans'] = Y_trans[key]
res_dict = {
'data_dict': data_dict,
'X_list': X_list,
'Y_list': Y_list,
'head_name': head_name,
'X_trans': X_trans,
'default_value': default_value
}
res_dict["note"] = "*起始日期-终止日期的各VIP段的原油购买次数*"
# print(res_dict)
return res_dict
def everyday_active(input_dict, *flag):
data_dict = dict()
for y in Y_list:
data_dict[y] = dict()
head_name = "每日活跃"
easy_mysql = EasyMysql.EasyMysql()
is_legal_input(input_dict)
date_list = input_dict['date_list']
date_list = sorted(date_list)
if len(date_list) == 0:
msg = 'Not date selected, len(date_list)' + str(len(date_list))
raise RuntimeError(msg)
zoneid_list = input_dict['zone_list']
channel_list = input_dict['channel_list']
cursor = input_dict['cursor']
sql_where = ' where date in (' + easy_mysql.sql_value_str(date_list) + ') '
if len(zoneid_list) > 0:
sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(
zoneid_list) + ') '
if len(channel_list) > 0:
sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(
channel_list) + ') '
#select date,viplevel,sum(num) from DailyDiscount where rechargeType=1 group by date,viplevel;
# if len(flag)>0:
sqlstr = 'select date,vip_level,count(uid) from user_active ' + sql_where + ' group by date,vip_level;'
print(sqlstr)
cursor.execute(sqlstr)
alldata = cursor.fetchall()
if alldata:
for rec in alldata:
data_dict[str(rec[0])][rec[1]] = rec[2]
for key in data_dict.keys():
data_dict[key]['trans'] = Y_trans[key]
res_dict = {
'data_dict': data_dict,
'X_list': X_list,
'Y_list': Y_list,
'head_name': head_name,
'X_trans': X_trans,
'default_value': default_value
}
# print(res_dict)
res_dict['note'] = "每日VIP活跃"
return res_dict
def vip_level(input_dict):
data_dict = dict()
for y in Y_list:
data_dict[y] = dict()
head_name = "VIP等级分布"
easy_mysql = EasyMysql.EasyMysql()
is_legal_input(input_dict)
date_list = [max(input_dict['date_list'])]
date_list = sorted(date_list)
if len(date_list) == 0:
msg = 'Not date selected, len(date_list)' + str(len(date_list))
raise RuntimeError(msg)
zoneid_list = input_dict['zone_list']
channel_list = input_dict['channel_list']
cursor = input_dict['cursor']
sql_where = ' where date <=' + easy_mysql.sql_value_str(date_list)
if len(zoneid_list) > 0:
sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(zoneid_list) + ') '
if len(channel_list) > 0:
sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(channel_list) + ') '
sql_str = 'select mvip,count(uid) from(select uid,max(level) as mlevel,max(vip_level) as mvip from user_active '+sql_where+' group by uid) as a group by mvip;'
cursor.execute(sql_str)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
vip = int(rec[0])
user = int(rec[1])
data_dict['UserCount'][vip] = user
sql_str = 'select mlevel,mvip,count(uid) from(select uid,max(level) as mlevel,max(vip_level) as mvip from user_active '+sql_where+' group by uid) as a group by mlevel,mvip;'
cursor.execute(sql_str)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
data_dict[int(rec[0])][rec[1]] = rec[2]
# for key in data_dict.keys():
# if X_first[0] not in data_dict.keys():
# pass
# # data_dict[key]['trans'] = Y_trans[key]
res_dict={'data_dict':data_dict,'X_list':X_list,'Y_list':Y_list,'head_name':head_name,'X_trans':X_trans,'default_value':default_value}
# print(res_dict)
res_dict["note"] = "*从开服-截止终止日期的各VIP段的等级分布*"
return res_dict
def get_item_buy(input_dict):
global file_name
is_legal_input(input_dict)
data_dict = dict()
# where clause
easy_mysql = EasyMysql.EasyMysql()
is_legal_input(input_dict)
date_list = input_dict['date_list']
date_list = sorted(date_list)
if len(date_list) == 0:
msg = 'Not date selected, len(date_list)' + str(len(date_list))
raise RuntimeError(msg)
date_compute_str = easy_mysql.sql_value_str(date_list) # automatically compute the last date
zoneid_list = input_dict['zone_list']
channel_list = input_dict['channel_list']
cursor = input_dict['cursor']
sql_where = ' where date in (' + date_compute_str + ') '
if len(zoneid_list) > 0:
sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(zoneid_list) + ') '
if len(channel_list) > 0:
sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(channel_list) + ') '
# sql item buying
user_buy = dict()
vip_list = list()
item_list = list()
cmd = 'select viplevel, itemid, sum(buy_times) from (select uid,itemid,(select max(vip_level) from user_active ' + sql_where + ' and uid=a.uid ) as viplevel,count(uid) as buy_times from pay_syn_day_extend as a ' + sql_where + ' group by uid,itemid)a group by viplevel,itemid'
print(cmd)
if cursor == None:
msg = 'Cursor is None. Return empty.'
return None
cursor.execute(cmd)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
vip = str(rec[0])
item_id = str(rec[1])
uid_count = int(rec[2])
if vip not in vip_list:
vip_list.append(vip)
if item_id not in item_list:
item_list.append(item_id)
if item_id not in user_buy.keys():
user_buy[item_id] = dict()
user_buy[item_id][vip] = uid_count
vip_list = sorted(vip_list, key=lambda x: int(x))
item_list = sorted(item_list, key=lambda x: int(x))
# shop table
shop_ref_dict = __read_table(file_name)
# compute data dict
for item in user_buy.keys():
user_buy[item]['trans'] = shop_ref_dict.setdefault(item, dict()).setdefault('trans', '')
user_buy[item]['value'] = int(shop_ref_dict.setdefault(item, dict()).setdefault('value', '0'))
# compute other elements
y_list = item_list
x_list = ['trans', 'value'] + vip_list
x_trans = dict()
x_trans['trans'] = '翻译'
x_trans['value'] = '价值'
for vip in vip_list:
x_trans[vip] = 'vip' + str(vip)
# load res
res_data_raw = dict()
res_data_raw['data_dict'] = user_buy
res_data_raw['X_list'] = x_list
res_data_raw['Y_list'] = y_list
res_data_raw['X_trans'] = x_trans
res_data_raw['default_value'] = 0
res_data_raw['head_name'] = '各VIP档商品购买笔数'
res_data_raw['note'] = '*取一段时间的各VIP档购买各货品的笔数合计（VIP取时间段内单位UID的最大VIP, 按活跃表计算），翻译和价值是根据配表的，如果发现翻译或价值问题，请提示我更新配表，商品ID是实际的商品ID'
return res_data_raw
def get_module_attend_rate(input_dict):
global file_name
is_legal_input(input_dict)
data_dict = dict()
data_dict_ratio = dict()
# where clause
easy_mysql = EasyMysql.EasyMysql()
is_legal_input(input_dict)
date_list = input_dict['date_list']
date_list = sorted(date_list)
if len(date_list) == 0:
msg = 'Not date selected, len(date_list)' + str(len(date_list))
raise RuntimeError(msg)
date_compute_str = easy_mysql.sql_value_str(
date_list) # automatically compute the last date
zoneid_list = input_dict['zone_list']
channel_list = input_dict['channel_list']
cursor = input_dict['cursor']
sql_where = ' where date in (' + date_compute_str + ') '
if len(zoneid_list) > 0:
sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(
zoneid_list) + ') '
if len(channel_list) > 0:
sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(
channel_list) + ') '
# sql check dau
user_active_dict = dict()
cmd = 'select date,level,count(uid) from user_active ' + sql_where + ' group by date,level'
cursor.execute(cmd)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
date = str(rec[0])
level = int(rec[1])
uid_count = int(rec[2])
if date not in user_active_dict.keys():
user_active_dict[date] = dict()
user_active_dict[date][level] = uid_count
# sql check attending
user_attend = dict()
cmd = 'select date,keyword,sum(countuid) from behavior_template ' + sql_where + ' group by date,keyword'
cursor.execute(cmd)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
date = str(rec[0])
keyword = str(rec[1])
uid_count = int(rec[2])
if date not in user_attend.keys():
user_attend[date] = dict()
user_attend[date][keyword] = uid_count
# attending table
attend_ref_dict = __read_module_table(file_name)
key_list = attend_ref_dict.keys()
# compute data dict
for date in date_list:
if date not in data_dict.keys():
data_dict[date] = dict()
if date not in data_dict_ratio.keys():
data_dict_ratio[date] = dict()
user_active = __get_above_users(user_active_dict, 0, date)
data_dict[date]['UserActive'] = user_active
data_dict_ratio[date]['UserActive'] = user_active
for key in key_list:
if date in user_attend.keys():
attend_user = user_attend[date].setdefault(key, 0)
data_dict[date][key] = attend_user
refer_level = int(attend_ref_dict[key]['level'])
refer_user = __get_above_users(user_active_dict, refer_level,
date)
if refer_user > 0:
data_dict_ratio[date][key] = round(
float(attend_user) / refer_user, 2)
y_list = date_list
x_list = ['UserActive'] + key_list
x_trans = dict()
x_trans['UserActive'] = '活跃用户'
for key in key_list:
x_trans[key] = attend_ref_dict[key]['trans']
# load res
res_data_raw = dict()
res_data_ratio = dict()
res_data_raw['data_dict'] = data_dict
res_data_raw['X_list'] = x_list
res_data_raw['Y_list'] = y_list
res_data_raw['X_trans'] = x_trans
res_data_raw['default_value'] = 0
res_data_raw['head_name'] = '活动参与度-参与人数'
res_data_raw['note'] = '*各活动的实际参与人数'
res_data_ratio['data_dict'] = data_dict_ratio
res_data_ratio['X_list'] = x_list
res_data_ratio['Y_list'] = y_list
res_data_ratio['X_trans'] = x_trans
res_data_ratio['default_value'] = 0
res_data_ratio['head_name'] = '活动参与度-比例'
res_data_ratio['note'] = '*各活动的参与比例，注意分母是符合条件的用户，即活跃且等级达到指定等级的用户'
return [res_data_raw, res_data_ratio]
def shop_exchange(input_dict, *flag):
Y_list = []
Y_trans = dict()
data_dict = dict()
head_name = "商店" + str(flag[0])
easy_mysql = EasyMysql.EasyMysql()
is_legal_input(input_dict)
date_list = [max(input_dict['date_list'])]
date_list = sorted(date_list)
if len(date_list) == 0:
msg = 'Not date selected, len(date_list)' + str(len(date_list))
raise RuntimeError(msg)
zoneid_list = input_dict['zone_list']
channel_list = input_dict['channel_list']
cursor = input_dict['cursor']
sql_where = ' where date <=' + easy_mysql.sql_value_str(date_list)
if len(zoneid_list) > 0:
sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(
zoneid_list) + ') '
if len(channel_list) > 0:
sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(
channel_list) + ') '
if len(flag) > 0:
sqlstr = 'select mvip,itemid,sum(times) from(select uid,max(viplevel) as mvip from shopexchange' + sql_where + ' and shopid=' + str(
flag[0]
) + ' group by uid) as a left join (select uid,itemid,sum(times) as times from shopexchange' + sql_where + ' and shopid=' + str(
flag[0]
) + ' group by uid,itemid) as b on a.uid=b.uid group by mvip,itemid;'
# print(sqlstr)
cursor.execute(sqlstr)
alldata = cursor.fetchall()
if alldata:
for rec in alldata:
Y_list.append(str(rec[1]))
Y_list = sorted(list(set(Y_list)))
for y in Y_list:
data_dict[y] = dict()
if alldata:
for rec in alldata:
data_dict[str(rec[1])][int(rec[0])] = rec[2]
Y_trans_origin = ReadTable.ReadTable(
"./conf/item.txt").read_table_file_coupled([1, 1])
for i in Y_list:
Y_trans[i] = Y_trans_origin.setdefault(i, dict()).setdefault(
"游戏内名称", "-")
for key in data_dict.keys():
data_dict[key]['trans'] = Y_trans[key]
res_dict = {
'data_dict': data_dict,
'X_list': X_list,
'Y_list': Y_list,
'head_name': head_name,
'X_trans': X_trans,
'default_value': default_value
}
# print(res_dict)
res_dict['note'] = "开始-终止日的" + str(flag[0]) + "商城购买次数"
return res_dict
def get_ship_info(input_dict):
data_dict = dict()
# prepare sql
easy_mysql = EasyMysql.EasyMysql()
is_legal_input(input_dict)
date_list = input_dict['date_list']
date_list = sorted(date_list)
if len(date_list) == 0:
msg = 'Not date selected, len(date_list)' + str(len(date_list))
raise RuntimeError(msg)
date_compute = date_list[len(date_list) -
1] # automatically compute the last date
zoneid_list = input_dict['zone_list']
channel_list = input_dict['channel_list']
cursor = input_dict['cursor']
sql_where = ' where date=' + easy_mysql.sql_value_str([date_compute])
if len(zoneid_list) > 0:
sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(
zoneid_list) + ') '
if len(channel_list) > 0:
sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(
channel_list) + ') '
# ship_dict
ship_dict = __read_ship_table('ship_info_korea.txt')
for ship_id in ship_dict.keys():
if ship_id not in data_dict.keys():
data_dict[ship_id] = dict()
if 'ShipName' not in data_dict[ship_id].keys():
data_dict[ship_id]['ShipName'] = ship_dict.setdefault(
ship_id, dict()).setdefault('ship_name', ship_id)
if 'ShipRank' not in data_dict[ship_id].keys():
data_dict[ship_id]['ShipRank'] = ship_dict.setdefault(
ship_id, dict()).setdefault('ship_rank', ship_id)
# Y & X
Y_list = ['UserCount']
for key in ship_dict.keys():
Y_list.append(key)
Y_trans = {'UserCount': '用户数量'}
X_list = ['ShipName', 'ShipRank', 'TotalCount'] # + sorted(vip_list)
X_trans = {
'ShipName': '船名称',
'ShipRank': '评级',
'TotalCount': '用户总量'
} # trans of vip
# --- vip active
vip_dict = dict()
vip_list = list()
total_user = 0
sql_cmd_active = 'select vip_level, count(distinct uid) from user_active' + sql_where + ' group by vip_level'
cursor.execute(sql_cmd_active)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
vip = int(rec[0])
user = int(rec[1])
total_user += user
if vip not in vip_list:
vip_list.append(vip)
if 'UserCount' not in data_dict.keys():
data_dict['UserCount'] = dict()
data_dict['UserCount'][vip] = user
data_dict['UserCount']['TotalCount'] = total_user
data_dict['UserCount']['ShipName'] = '-'
data_dict['UserCount']['ShipRank'] = '-'
vip_list = sorted(vip_list)
X_list = X_list + vip_list
for vip in vip_list:
X_trans[vip] = 'VIP' + str(vip)
# --- ship info
sql_cmd_ship = 'select viplevel,shipid,sum(countuid) from ShipInfo ' + sql_where + ' and shipid>0 and shipstar>2 and shipgrade>2 group by viplevel,shipid'
cursor.execute(sql_cmd_ship)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
vip = int(rec[0])
ship_id = str(rec[1])
user = int(rec[2])
if ship_id not in data_dict.keys():
data_dict[ship_id] = dict()
data_dict[ship_id][vip] = user
data_dict[ship_id]['TotalCount'] = data_dict[ship_id].setdefault(
'TotalCount', 0) + user
# -- compile return value
res_dict = dict()
res_dict['data_dict'] = data_dict
res_dict['X_list'] = X_list
res_dict['Y_list'] = Y_list
res_dict['X_trans'] = X_trans
res_dict['Y_trans'] = Y_trans
res_dict['default_value'] = 0
res_dict['head_name'] = '用户阵容'
res_dict['note'] = '*取最后计算日的，各VIP用户的战役上阵用阵容，仅3星且3阶以上船被统计了'
return res_dict
def get_module_time_cost(input_dict):
global module_list
global module_trans
easy_mysql = EasyMysql.EasyMysql()
is_legal_input(input_dict)
date_list = input_dict['date_list']
date_list = sorted(date_list)
if len(date_list) == 0:
msg = 'Not date selected, len(date_list)' + str(len(date_list))
raise RuntimeError(msg)
date_compute = date_list[len(date_list) -
1] # automatically compute the last date
zoneid_list = input_dict['zone_list']
channel_list = input_dict['channel_list']
cursor = input_dict['cursor']
sql_where = ' where date=' + easy_mysql.sql_value_str([date_compute])
if len(zoneid_list) > 0:
sql_where = sql_where + ' and zoneid in (' + easy_mysql.sql_value_str(
zoneid_list) + ') '
if len(channel_list) > 0:
sql_where = sql_where + ' and channel in (' + easy_mysql.sql_value_str(
channel_list) + ') '
# vip active
vip_dict = dict()
vip_list = list()
sql_cmd = 'select vip_level, count(distinct uid) from user_active' + sql_where + ' group by vip_level'
cursor.execute(sql_cmd)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
vip = int(rec[0])
user = int(rec[1])
if vip not in vip_list:
vip_list.append(vip)
if vip not in vip_dict.keys():
vip_dict[vip] = user
vip_list = sorted(vip_list)
# module time
module_dict = dict()
sql_cmd = 'select ui_type,vip_level,sum(time)/(60*count(uid)) from user_exit_ui_time' + sql_where + ' group by ui_type,vip_level'
cursor.execute(sql_cmd)
all_data = cursor.fetchall()
if all_data:
for rec in all_data:
ui_type = str(rec[0])
vip = int(rec[1])
data = round(float(rec[2]), 1)
if ui_type not in module_dict.keys():
module_dict[ui_type] = dict()
if vip not in module_dict[ui_type].keys():
module_dict[ui_type][vip] = data
# add user_count
module_dict['UserCount'] = dict()
for vip in vip_list:
module_dict['UserCount'][vip] = vip_dict[vip]
# add trans
for module_id in module_list:
if module_id not in module_dict.keys():
module_dict[module_id] = dict()
module_dict[module_id]['trans'] = module_trans[module_id]
module_dict['UserCount']['trans'] = '用户数量'
# X_trans
X_trans = dict()
X_trans['trans'] = '翻译'
for vip in vip_list:
X_trans[vip] = 'VIP' + str(vip)
# compile return value
res_dict = dict()
res_dict['data_dict'] = module_dict
res_dict['X_list'] = ['trans'] + vip_list
res_dict['Y_list'] = ['UserCount'] + module_list
res_dict['X_trans'] = X_trans
res_dict['default_value'] = 0
res_dict['head_name'] = '模块时长'
res_dict['note'] = '*取最后计算日的，各VIP用户各项目人均游戏时长（单位：分钟）'
return res_dict
