def __init__(self):
# self.conf_path = ConfParameters.ConfParameters().conf_path
# initialize
# # mysql
# mysql_para = ConfParameters.ConfParameters().mysql_conf
# self.db = MySQLdb.connect(mysql_para['ip'], mysql_para['users'], mysql_para['password'],
# mysql_para['stat_pay'])
#bigdata
mysqlStatInfo = {
'host': '10.66.127.43',
'port': 3306,
'user': '******',
'passwd': 'r00tr00t'
}
self.db = MySQLdb.connect(host=mysqlStatInfo['host'],
port=mysqlStatInfo['port'],
user=mysqlStatInfo['user'],
passwd=mysqlStatInfo['passwd'],
db='modernship_gf_stat_base',
charset='utf8')
self.cursor = self.db.cursor()
self.easy_mysql = EasyMysql.EasyMysql()
# # xls
self.wbk = xlwt.Workbook()
self.xls_writer = EasyXls.EasyXls()
self.style = xlwt.XFStyle()
self.style.borders = self.xls_writer.borders
def get_diamond_detail_info(self, top_list):
# initial mysql-db big_data_test(this is a special version, only acceptable for modernship cn)
easy_sql = EasyMysql.EasyMysql()
mysql_para_bd_test = ConfParameters.ConfParameters().mysql_conf_bd_test
db_bd_test = MySQLdb.connect(mysql_para_bd_test['ip'],
mysql_para_bd_test['users'],
mysql_para_bd_test['password'],
mysql_para_bd_test['stat_base'])
cursor_bd_test = db_bd_test.cursor()
top_str = easy_sql.sql_value_str(top_list)
sql_str = 'select uid,date,moneytype,sum(cash+diamond) from diamond_pay_users where uid in (' + top_str + ') group by uid,date,moneytype'
cursor_bd_test.execute(sql_str)
all_data = cursor_bd_test.fetchall()
date_list = list()
money_type_list = list()
result_dict = dict()
if all_data:
for rec in all_data:
uid = str(rec[0])
date = str(rec[1])
if date not in date_list:
date_list.append(date)
money_type = str(rec[2])
if money_type not in money_type_list:
money_type_list.append(money_type)
if uid not in result_dict.keys():
result_dict[uid] = dict()
if money_type not in result_dict[uid].keys():
result_dict[uid][money_type] = dict()
result_dict[uid][money_type][date] = int(rec[3])
date_list = sorted(date_list)
money_type_list = sorted(money_type_list, key=lambda x: int(x))
db_bd_test.close()
return [money_type_list, date_list, result_dict]
def get_ship_info(self, top_list, ship_list):
easy_sql = EasyMysql.EasyMysql()
top_str = easy_sql.sql_value_str(top_list)
ship_str = easy_sql.sql_value_str(ship_list)
trans_dict = dict()
missile_list = list()
ship_star_result = dict()
ship_grade_result = dict()
for ship_id in ship_list:
missile_id = self.__find_attribute(ship_id, 'exclusive')
ship_name = self.__find_attribute(ship_id, 'ship_name')
trans_dict[ship_id] = ship_name
trans_dict[
missile_id] = '[导]' + ship_name # missile also use the name of its corresponding ship
missile_list.append(missile_id)
missile_str = easy_sql.sql_value_str(missile_list)
x_list = ship_list + missile_list
# ship_star
sql_str = 'select ship_id,uid,max(ship_star) from user_ship_info where date = \'' + self.compute_date + '\' and uid in (' + top_str + ') and ship_id in (' + ship_str + ') group by ship_id,uid'
print sql_str
self.cursor.execute(sql_str)
all_data = self.cursor.fetchall()
if all_data:
for rec in all_data:
ship_id = str(rec[0])
uid = str(rec[1])
ship_star = int(rec[2])
if uid not in ship_star_result.keys():
ship_star_result[uid] = dict()
ship_star_result[uid][ship_id] = ship_star
# ship_grade
sql_str = 'select ship_id,uid,max(ship_grade) from user_ship_info where date = \'' + self.compute_date + '\' and uid in (' + top_str + ') and ship_id in (' + ship_str + ') group by ship_id,uid'
print sql_str
self.cursor.execute(sql_str)
all_data = self.cursor.fetchall()
if all_data:
for rec in all_data:
ship_id = str(rec[0])
uid = str(rec[1])
ship_grade = int(rec[2])
if uid not in ship_grade_result.keys():
ship_grade_result[uid] = dict()
ship_grade_result[uid][ship_id] = ship_grade
# missile_count
sql_str = 'select missile_id,uid,max(missile_count) from user_missile_info where date = \'' + self.compute_date + '\' and uid in (' + top_str + ') and missile_id in (' + missile_str + ') group by missile_id,uid'
print sql_str
self.cursor.execute(sql_str)
all_data = self.cursor.fetchall()
if all_data:
for rec in all_data:
missile_id = str(rec[0])
uid = str(rec[1])
missile_count = int(rec[2])
if uid not in ship_grade_result.keys():
ship_grade_result[uid] = dict()
if uid not in ship_star_result.keys():
ship_star_result[uid] = dict()
ship_grade_result[uid][missile_id] = missile_count
ship_star_result[uid][missile_id] = missile_count
return [ship_grade_result, ship_star_result, x_list, trans_dict]
def compute_total_sheet(self, zoneid_list, ship_list, sheet_name):
ship_str = EasyMysql.EasyMysql().sql_value_str(ship_list)
zone_str = ''
if len(zoneid_list) > 0:
zone_str = EasyMysql.EasyMysql().sql_value_str(zoneid_list)
sheet = self.xls_writer.new_sheet(sheet_name, self.wbk)
sheet.col(0).width = 256 * 20
line_num = [0]
name_list = [sheet_name]
self.xls_writer.insert_xls_style(name_list, sheet, line_num,
self.style)
line_num[0] = line_num[0] + 2
# compute users
head_name = 'VIP-船-拥有人数'
sql_str = 'select vip_level,ship_id,count(distinct uid) from user_ship_info where ship_id in (' + ship_str + ') and date=\'' + self.compute_date + '\' and uid not in (select uid from user_ban_statistic) '
if zone_str != '':
sql_str = sql_str + 'and zoneid in (' + zone_str + ')'
sql_str = sql_str + ' group by vip_level,ship_id;'
self.__compute_sql_and_load_xls(sql_str, sheet, line_num, head_name,
'ship_name')
line_num[0] = line_num[0] + 2
# compute average stars
head_name = 'VIP-船-平均星级'
sql_str = 'select vip_level,ship_id,format(sum(ship_star)/count(distinct uid),2) from user_ship_info where ship_id in (' + ship_str + ') and date=\'' + self.compute_date + '\' and uid not in (select uid from user_ban_statistic) '
if zone_str != '':
sql_str = sql_str + 'and zoneid in (' + zone_str + ')'
sql_str = sql_str + ' group by vip_level,ship_id;'
self.__compute_sql_and_load_xls(sql_str, sheet, line_num, head_name,
'ship_name')
line_num[0] = line_num[0] + 2
# compute average grades
head_name = 'VIP-船-平均阶级'
sql_str = 'select vip_level,ship_id,format(sum(ship_grade)/count(distinct uid),2) from user_ship_info where ship_id in (' + ship_str + ') and date=\'' + self.compute_date + '\' and uid not in (select uid from user_ban_statistic) '
if zone_str != '':
sql_str = sql_str + 'and zoneid in (' + zone_str + ')'
sql_str = sql_str + ' group by vip_level,ship_id;'
self.__compute_sql_and_load_xls(sql_str, sheet, line_num, head_name,
'ship_name')
# compute missile count
# head_name = 'VIP-对应导弹-数量合计'
# missile_list = list()
return 1
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 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 __init__(self):
#bigdata
mysqlStatInfo = {'host': '10.66.127.43', 'port': 3306, 'user': '******', 'passwd': 'r00tr00t'}
self.db = MySQLdb.connect(host=mysqlStatInfo['host'], port=mysqlStatInfo['port'], user=mysqlStatInfo['user'],
passwd=mysqlStatInfo['passwd'], db='modernship_gf_stat_base', charset='utf8')
self.cursor = self.db.cursor()
self.easy_mysql = EasyMysql.EasyMysql()
# # xls
self.wbk = xlwt.Workbook()
self.xls_writer = EasyXls.EasyXls()
self.style = xlwt.XFStyle()
self.style.borders = self.xls_writer.borders
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_diamond_info(self, top_list):
top_str = EasyMysql.EasyMysql().sql_value_str(top_list)
sql_str = 'select date,uid,sum(cost_diamond + cost_cash) from user_diamond where date < \'' + self.compute_date + '\' and uid in (' + top_str + ') group by date,uid'
date_list = list()
uid_diamond = dict()
print sql_str
self.cursor.execute(sql_str)
all_data = self.cursor.fetchall()
if all_data:
for rec in all_data:
date = str(rec[0])
if date not in date_list:
date_list.append(date)
uid = str(rec[1])
cost = int(rec[2])
if uid not in uid_diamond.keys():
uid_diamond[uid] = dict()
uid_diamond[uid][date] = cost
date_list = sorted(date_list)
return [uid_diamond, date_list]
def __init__(self, start_date, end_date, *channel):
# conf path
self.conf_path = ConfParameters.ConfParameters().conf_path
# initial mysql-db
self.mysql_para = ConfParameters.ConfParameters().mysql_conf
self.stat_base = self.mysql_para['stat_base']
self.db = None # such is initiate not in constructor but when actually used to ensure db get closed after connect
self.cursor = None
self.easy_sql = EasyMysql.EasyMysql()
# initial xls writer
self.wbk = xlwt.Workbook()
self.xls_writer = EasyXls.EasyXls()
self.style = xlwt.XFStyle()
self.style.borders = self.xls_writer.borders
# local parameter
self.start_date = start_date
self.end_date = end_date
self.channel = ['-1']
self.zone = ['-1']
self.period_limit = '30'
if len(channel) > 0:
self.channel = list(channel[0])
def __init__(self):
# environment
self.conf_path = ConfParameters.ConfParameters().conf_path
# initialize
# # mysql
mysql_para = ConfParameters.ConfParameters().mysql_conf
self.db = MySQLdb.connect(mysql_para['ip'], mysql_para['users'],
mysql_para['password'],
mysql_para['stat_pay'])
self.cursor = self.db.cursor()
self.easy_mysql = EasyMysql.EasyMysql()
# # xls
self.wbk = xlwt.Workbook()
self.xls_writer = EasyXls.EasyXls()
self.style = xlwt.XFStyle()
self.style.borders = self.xls_writer.borders
# # others
self.easy_date = DateList.DateList()
# local
self.cycle = 7
vip_file_name = self.conf_path + 'Vip_Money.txt'
self.vip_dict = self.__get_vip_table(vip_file_name)
def compute_certain_ship(self, ship_id, zoneid_list):
zone_str = ''
if len(zoneid_list) > 0:
zone_str = EasyMysql.EasyMysql().sql_value_str(zoneid_list)
ship_name = self.__find_attribute(ship_id, 'ship_name')
sheet_name = 'Ship_' + str(ship_id) + '_' + ship_name
sheet = self.xls_writer.new_sheet(sheet_name, self.wbk)
sheet.col(0).width = 256 * 20
line_num = [0]
# ship name
name_list = [ship_name, str(ship_id)]
self.xls_writer.insert_xls_style(name_list, sheet, line_num,
self.style)
line_num[0] = line_num[0] + 2
# start computing ship-stars
head_name = 'VIP-船星级'
sql_str = 'select vip_level,ship_star,count(distinct uid) from user_ship_info where ship_id=\'' + ship_id + '\' and date=\'' + self.compute_date + '\' and uid not in (select uid from user_ban_statistic) '
if zone_str != '':
sql_str = sql_str + 'and zoneid in (' + zone_str + ')'
sql_str = sql_str + ' group by vip_level,ship_star;'
self.__compute_sql_and_load_xls(sql_str, sheet, line_num, head_name)
line_num[0] = line_num[0] + 2
head_name = 'VIP-船阶级'
sql_str = 'select vip_level,ship_grade,count(distinct uid) from user_ship_info where ship_id=\'' + ship_id + '\' and date=\'' + self.compute_date + '\' and uid not in (select uid from user_ban_statistic) '
if zone_str != '':
sql_str = sql_str + 'and zoneid in (' + zone_str + ')'
sql_str = sql_str + ' group by vip_level,ship_grade;'
self.__compute_sql_and_load_xls(sql_str, sheet, line_num, head_name)
line_num[0] = line_num[0] + 2
head_name = 'VIP-船对应导弹数量'
missile_id = self.__find_attribute(ship_id, 'exclusive')
sql_str = 'select vip_level,missile_count,count(distinct uid) from user_missile_info where missile_id=\'' + missile_id + '\' and date=\'' + self.compute_date + '\' and uid not in (select uid from user_ban_statistic) '
if zone_str != '':
sql_str = sql_str + 'and zoneid in (' + zone_str + ')'
sql_str = sql_str + ' group by vip_level,missile_count;'
self.__compute_sql_and_load_xls(sql_str, sheet, line_num, head_name)
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_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
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