def get_user_purchase_info(server_id, name, start_date, end_date):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
user_info = get_user_info(server_id, name)
user_purchase_compact_info = []
if user_info:
user_id = user_info['user_id']
user_purchase_info = cursor.fetchall("SELECT * FROM user_mall_log WHERE user_id='%s' AND created_time BETWEEN '%s' AND '%s'" % (user_id, start_date, end_date))
for info in user_purchase_info:
mall_id = info['mall_id']
system_mall = cursor.fetchone("SELECT * FROM system_mall WHERE mall_id='%s'" % mall_id)
if system_mall:
num = info['num']
created_time = info['created_time']
name = system_mall['name']
data = {
'name' : name,
'num' : num,
'created_time' : created_time
}
user_purchase_compact_info.append(data)
finally:
cursor.close()
return user_purchase_compact_info
def send(server_id):
date = datetime.now() - timedelta(days=1)
date_str = date.strftime("%Y-%m-%d")
date_s = datetime.now().strftime("%Y-%m-%d")
file_name = "%s/reward_payment_double_%s_%s.sql" % (PathSettings.SCRIPT,
date_s, server)
logging.debug(file_name)
f = open(file_name, "wb")
db_config = server_business.get_server_db_connect(server_id=server_id)
connection = get_connection(db_config)
cursor = connection.cursor()
try:
sql = "SELECT sum(gold) as gold, user_id FROM payment_log where DATE_FORMAT(created_time,'%%Y-%%m-%%d') = '%s' group by user_id" % date_str
logging.debug(sql)
infos = cursor.fetchall(sql)
for info in infos:
gold = info["gold"]
user_id = info["user_id"]
sql1 = "UPDATE user SET gold_num = gold_num + %s WHERE user_id ='%s' LIMIT 1;\n" % (
gold, user_id)
sql2 = "INSERT INTO user_gold_use_log(user_id, use_type, amount, flag, created_time) VALUES('%s', 10004, %s, 1, now());\n" % (
user_id, gold)
f.write(sql1)
f.write(sql2)
finally:
f.close()
cursor.close()
def get_user_hero_info(server_id, name):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
user_info = get_user_info(server_id, name)
user_hero_compact_info = []
if user_info:
user_id = user_info['user_id']
user_hero_info = cursor.fetchall("SELECT * FROM user_hero WHERE user_id='%s' order by hero_level DESC" % user_id)
for info in user_hero_info:
system_hero_id = info['system_hero_id']
system_hero_info = cursor.fetchone("SELECT * FROM system_hero WHERE system_hero_id='%s'" % system_hero_id)
if system_hero_info:
hero_name = system_hero_info['hero_name']
data = {
'hero_name' : hero_name,
'hero_exp' : info['hero_exp'],
'hero_level' : info['hero_level'],
'system_hero': system_hero_info
}
user_hero_compact_info.append(data)
finally:
cursor.close()
return user_hero_compact_info
def update_user_level(server_id, user_id, level):
level = int(level)
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
info = cursor.fetchone("select exp from system_user_level where user_level = %s" % (level + 1))
if info:
exp = info["exp"] - 10
else:
exp = 10000 * 10000
server = server_business.get_server(server_id)
host = server.server_host
path = "/gameApi/updateUserLevel.do"
logging.debug("host:[%s], path[%s]" % (host, path))
data = {"userId": user_id, "level": level, "exp": exp}
post = Post(data, host, path, port=8088)
content = post.submit(result_keyword=None, get_content=True, debug=True)
try:
info = json.loads(content)
return info.get("rc", 3000) == 1000
except:
logging.debug("bad json format:[%s]" % content)
finally:
cursor.close()
def get_user_payment_log(server_id, name, start_time, end_time, page=1, pagesize=20):
"""获取用户充值日志"""
start = (page - 1) * pagesize
limit = pagesize
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
total, infos = 0, []
try:
user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name)
if not user_info:
user_info = cursor.fetchone("select * from user where username='******'" % name)
if user_info:
where = " user_id = '%s' " % user_info['user_id']
if start_time:
where += " and created_time >= '%s' " % start_time
if end_time:
where += " and created_time <= '%s' " % end_time
total = cursor.fetchone("select count(1) as total from payment_log where %s " % where)["total"]
if total > 0:
sql = "select * from payment_log where %s order by payment_log_id desc limit %s, %s" % (where, start, limit)
infos = cursor.fetchall(sql)
finally:
cursor.close()
return total, infos
def get_tool_name(server_id, tool_type, tool_id):
if tool_type == ToolType.TOOL_TYPE_HERO:
return u"武将卡牌"
elif tool_type == ToolType.TOOL_TYPE_EQUIP:
return u"装备"
elif tool_type == ToolType.TOOL_TYPE_EXP:
return u"经验"
elif tool_type == ToolType.TOOL_TYPE_POWER:
return u"体力"
else:
sql = "select name from system_tool where tool_id = %s" % tool_id
key = "name"
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
info = cursor.fetchone(sql)
if info:
return info[key]
else:
return "[%s][%s]" % (tool_type, tool_id)
finally:
cursor.close
def get_user_level_up_log(server_id, name, page=1, pagesize=20):
"""获取用户升级日志"""
start = (page - 1) * pagesize
limit = pagesize
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
total, infos = 0, []
try:
user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name)
if not user_info:
user_info = cursor.fetchone("select * from user where username='******'" % name)
if user_info:
user_id = user_info['user_id']
table = "user_level_up_log"
where = " user_id = '%s' " % user_id
total = cursor.fetchone("select count(1) as total from %s where %s " % (table, where))["total"]
if total > 0:
sql = "select * from %s where %s order by log_id desc limit %s, %s" % (table, where, start, limit)
infos = cursor.fetchall(sql)
infos = infos.to_list()
for info in infos:
info["username"] = user_info["username"]
finally:
cursor.close()
return total, infos
def get_user_package_info(server_id, name):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
user_info = get_user_info(server_id, name)
user_package_compact_info = []
if user_info:
user_id = user_info['user_id']
user_package_info = cursor.fetchall("SELECT * FROM user_tool WHERE user_id='%s'" % user_id)
for info in user_package_info:
tool_id = info['tool_id']
system_tool = cursor.fetchone("SELECT * FROM system_tool WHERE tool_id='%s'" % tool_id)
if system_tool:
tool_num = info['tool_num']
tool_name = system_tool['name']
data = {
'name' : tool_name,
'tool_num' : tool_num
}
user_package_compact_info.append(data)
finally:
cursor.close()
return user_package_compact_info
def get_user_hero_info(server_id, name):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
user_info = get_user_info(server_id, name)
user_hero_compact_info = []
if user_info:
user_id = user_info['user_id']
user_hero_info = cursor.fetchall(
"SELECT * FROM user_hero WHERE user_id='%s' order by hero_level DESC"
% user_id)
for info in user_hero_info:
system_hero_id = info['system_hero_id']
system_hero_info = cursor.fetchone(
"SELECT * FROM system_hero WHERE system_hero_id='%s'" %
system_hero_id)
if system_hero_info:
hero_name = system_hero_info['hero_name']
data = {
'hero_name': hero_name,
'hero_exp': info['hero_exp'],
'hero_level': info['hero_level'],
'system_hero': system_hero_info
}
user_hero_compact_info.append(data)
finally:
cursor.close()
return user_hero_compact_info
def get_tool_name(server_id, tool_type, tool_id):
if tool_type == ToolType.TOOL_TYPE_HERO:
return u"武将卡牌"
elif tool_type == ToolType.TOOL_TYPE_EQUIP:
return u"装备"
elif tool_type == ToolType.TOOL_TYPE_EXP:
return u"经验"
elif tool_type == ToolType.TOOL_TYPE_POWER:
return u"体力"
else:
sql = "select name from system_tool where tool_id = %s" % tool_id
key = "name"
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
info = cursor.fetchone(sql)
if info:
return info[key]
else:
return "[%s][%s]" % (tool_type, tool_id)
finally:
cursor.close
def get_user_reg_stat_by_pid(server_id, partner_id, start_date, end_date):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
admin_db_cursor = connection.cursor()
try:
data = {}
partner = admin_db_cursor.fetchone(
"SELECT * FROM partner WHERE partner_id='%s'" % partner_id)
reg_num = cursor.fetchone(
"SELECT COUNT(user_id) AS reg_num FROM user_mapper WHERE partner_id='%s' AND created_time BETWEEN '%s' AND '%s'"
% (partner_id, start_date, end_date))
create_num = cursor.fetchone(
"SELECT COUNT(u.user_id) AS create_num FROM `user` u LEFT JOIN user_mapper m ON u.`user_id` = m.`user_id` WHERE m.`partner_id` = '%s' AND reg_time BETWEEN '%s' AND '%s'"
% (partner_id, start_date, end_date))
data['reg_num'] = int(reg_num['reg_num'])
data['create_num'] = int(create_num['create_num'])
data['partner_name'] = partner['name']
data['ratio'] = round(data['create_num'] / float(data['reg_num']),
4) * 100
finally:
cursor.close()
return data
def update_user_level(server_id, user_id, level):
level = int(level)
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
info = cursor.fetchone(
"select exp from system_user_level where user_level = %s" %
(level + 1))
if info:
exp = info["exp"] - 10
else:
exp = 10000 * 10000
server = server_business.get_server(server_id)
host = server.server_host
path = "/gameApi/updateUserLevel.do"
logging.debug("host:[%s], path[%s]" % (host, path))
data = {"userId": user_id, "level": level, "exp": exp}
post = Post(data, host, path, port=8088)
content = post.submit(result_keyword=None,
get_content=True,
debug=True)
try:
info = json.loads(content)
return info.get("rc", 3000) == 1000
except:
logging.debug("bad json format:[%s]" % content)
finally:
cursor.close()
def run(self):
qa_db_config = server_business.get_server_db_connect(server_id="t2")
qa_conn = get_connection(qa_db_config)
qa_cursor = qa_conn.cursor()
infos = qa_cursor.fetchall("show tables")
tables = []
for info in infos:
table = info["Tables_in_ldsg_t2"]
tables.append(table)
try:
servers, _ = server_business.get_server_list(1, 30, 2)
for server in servers:
db_config = server_util.str_to_dbconfig(server.db_connect)
conn = get_connection(db_config)
cursor = conn.cursor()
try:
for table in tables:
try:
info = cursor.fetchone("select 1 from %s limit 1" % table)
except:
print "error:server:%s,table:%s" % (server.server_id, table)
finally:
cursor.close()
finally:
pass
def get_user_payment_stat(server_id, start_date, end_date):
start_date = "%s 00:00:00" % start_date
end_date = "%s 23:59:59" % end_date
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
user_payment_stat = []
try:
infos = cursor.fetchall(
"SELECT partner_id, sum(amount) AS total FROM payment_log WHERE created_time BETWEEN '%s' AND '%s' GROUP BY partner_id ORDER BY total DESC"
% (start_date, end_date))
#infos = infos.to_list()
total = 0
for info in infos:
total += info["total"]
user_payment_stat.append({
"name": info["partner_id"],
"total": info["total"]
})
user_payment_stat.append({"name": u"总计", "total": total})
finally:
cursor.close()
return user_payment_stat
def get_user_purchase_info(server_id, name, start_date, end_date):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
user_info = get_user_info(server_id, name)
user_purchase_compact_info = []
if user_info:
user_id = user_info['user_id']
user_purchase_info = cursor.fetchall(
"SELECT * FROM user_mall_log WHERE user_id='%s' AND created_time BETWEEN '%s' AND '%s'"
% (user_id, start_date, end_date))
for info in user_purchase_info:
mall_id = info['mall_id']
system_mall = cursor.fetchone(
"SELECT * FROM system_mall WHERE mall_id='%s'" % mall_id)
if system_mall:
num = info['num']
created_time = info['created_time']
name = system_mall['name']
data = {
'name': name,
'num': num,
'created_time': created_time
}
user_purchase_compact_info.append(data)
finally:
cursor.close()
return user_purchase_compact_info
def get_user_mail_list(server_id, name, source_id=0, page=1, pagesize=20):
db_config = server_business.get_server_db_connect(server_id=server_id)
total, infos = 0, []
connect = get_connection(db_config)
cursor = connect.cursor()
try:
user_info = cursor.fetchone("select user_id from user where username='******'" % name)
if not user_info:
user_info = cursor.fetchone("select user_id from user where lodo_id='%s'" % name)
if user_info:
user_id = user_info["user_id"]
table_index = table_util.get_table_index(user_id)
table = "user_mail_%s" % table_index
where = "user_id = '%s' " % user_id
if source_id:
mail_info = cursor.fetchone("select * from system_mail where source_id = %s " % source_id)
if mail_info:
where += " and system_mail_id = '%s' " % mail_info["system_mail_id"]
else:
where += " and system_mail_id = '-1' "
total = cursor.fetchone("select count(1) as total from %s where %s " % (table, where))["total"]
if total > 0:
start = (page - 1) * pagesize
infos = cursor.fetchall("select * from %s where %s order by created_time desc limit %s, %s" % (table, where, start, pagesize))
infos = infos.to_list()
for info in infos:
info["mail_info"] = cursor.fetchone("select * from system_mail where system_mail_id ='%s'" % info["system_mail_id"])
finally:
cursor.close()
return total, infos
def get_user_info(server_id, name):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
user_extinfo = None
try:
user_info = cursor.fetchone("select * from user where lodo_id='%s'" %
name)
if not user_info:
user_info = cursor.fetchone(
"select * from user where username='******'" % name)
if user_info:
user_extinfo = cursor.fetchone(
"select * from user_extinfo where user_id='%s'" %
user_info["user_id"])
finally:
cursor.close()
if user_info and user_extinfo:
user_info = user_info.to_dict()
reward_vip_level = user_extinfo["reward_vip_level"]
vip_level = user_info["vip_level"]
if vip_level < reward_vip_level:
user_info["vip_level"] = reward_vip_level
if user_info["is_banned"] and user_info["due_time"] > datetime.now():
user_info["is_banned"] = 1
else:
user_info["is_banned"] = 0
return user_info
def get_user_package_info(server_id, name):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
user_info = get_user_info(server_id, name)
user_package_compact_info = []
if user_info:
user_id = user_info['user_id']
user_package_info = cursor.fetchall(
"SELECT * FROM user_tool WHERE user_id='%s'" % user_id)
for info in user_package_info:
tool_id = info['tool_id']
system_tool = cursor.fetchone(
"SELECT * FROM system_tool WHERE tool_id='%s'" % tool_id)
if system_tool:
tool_num = info['tool_num']
tool_name = system_tool['name']
data = {'name': tool_name, 'tool_num': tool_num}
user_package_compact_info.append(data)
finally:
cursor.close()
return user_package_compact_info
def send(server_id):
date = datetime.now() - timedelta(days=1)
date_str = date.strftime("%Y-%m-%d")
date_s = datetime.now().strftime("%Y-%m-%d")
file_name = "%s/reward_payment_double_%s_%s.sql" % (PathSettings.SCRIPT, date_s, server)
logging.debug(file_name)
f = open(file_name, "wb")
db_config = server_business.get_server_db_connect(server_id=server_id)
connection = get_connection(db_config)
cursor = connection.cursor()
try:
sql = "SELECT sum(gold) as gold, user_id FROM payment_log where DATE_FORMAT(created_time,'%%Y-%%m-%%d') = '%s' group by user_id" % date_str
logging.debug(sql)
infos = cursor.fetchall(sql)
for info in infos:
gold = info["gold"]
user_id = info["user_id"]
sql1 = "UPDATE user SET gold_num = gold_num + %s WHERE user_id ='%s' LIMIT 1;\n" % (gold, user_id)
sql2 = "INSERT INTO user_gold_use_log(user_id, use_type, amount, flag, created_time) VALUES('%s', 10004, %s, 1, now());\n" % (user_id, gold)
f.write(sql1)
f.write(sql2)
finally:
f.close()
cursor.close()
def get_user_register(server_id,datetime):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor_curr = connection.cursor()
cursor = connect.cursor()
data = {}
try:
info = cursor_curr.fetchone("select 1 from stat_server_data where stat_date = '%s' and server_id = '%s' " % (datetime, server_id))
total_user = cursor.fetchone("SELECT COUNT(distinct a.user_id) as total_user FROM `user` a , user_mapper b WHERE DATE(a.reg_time) <= '%s' AND a.`user_id` = b.`user_id`" % (datetime))
data['total_user'] = int(total_user['total_user'])
if info:
temp = cursor_curr.fetchone("select create_user AS new_register,date_active from stat_server_data where stat_date = '%s' and server_id = '%s'" % (datetime, server_id))
data['new_register'] = int(temp['new_register'])
data['date_active'] = int(temp['date_active'])
else:
info = cursor.fetchone("SELECT COUNT(user_id) AS new_register FROM `user` WHERE DATE(reg_time) = '%s'" % (datetime))
data['new_register'] = int(info['new_register'])
active_user = 0
for i in range(128):
info = cursor.fetchone("select count(distinct user_id) as date_active from user_online_log_%d where date(login_time) = '%s';" % (i,datetime))
active_user = active_user+int(info['date_active'])
data['date_active'] = active_user
finally:
cursor.close()
cursor_curr.close()
data['active'] = round(data['date_active']/float(data['total_user']),5)*100
return data
def get_user_info(server_id, name):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
user_extinfo = None
try:
user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name)
if not user_info:
user_info = cursor.fetchone("select * from user where username='******'" % name)
if user_info:
user_extinfo = cursor.fetchone("select * from user_extinfo where user_id='%s'" % user_info["user_id"])
finally:
cursor.close()
if user_info and user_extinfo:
user_info = user_info.to_dict()
reward_vip_level = user_extinfo["reward_vip_level"]
vip_level = user_info["vip_level"]
if vip_level < reward_vip_level:
user_info["vip_level"] = reward_vip_level
if user_info["is_banned"] and user_info["due_time"] > datetime.now():
user_info["is_banned"] = 1
else:
user_info["is_banned"] = 0
return user_info
def get_user_draw_pk_rank(server_id):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
sql = "SELECT u.`rank`,u.`score`,u.`username` FROM user_pk_info u ORDER BY u.`rank` LIMIT 30"
return cursor.fetchall(sql)
finally:
cursor.close()
def get_user_draw_power_rank(server_id,date):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
sql = u"SELECT r.`rank_data` FROM rank_log r WHERE r.`rank_key` = 'ldsg_local_power_rank_key' AND r.`date` = '%s' " % (date)
return cursor.fetchall(sql)
finally:
cursor.close()
def get_user_draw_time_rank(server_id):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
sql = "select b.username, b.lodo_id, a.score from user_draw_time a left join user b on a.user_id = b.user_id order by a.score desc limit 50"
return cursor.fetchall(sql)
finally:
cursor.close()
def get_user_draw_time_rank(server_id):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
sql = "select b.username, b.lodo_id, a.score from user_draw_time a left join user b on a.user_id = b.user_id order by a.score desc limit 50"
return cursor.fetchall(sql)
finally:
cursor.close()
def get_user_draw_pk_rank(server_id):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
sql = "SELECT u.`rank`,u.`score`,u.`username` FROM user_pk_info u ORDER BY u.`rank` LIMIT 30"
return cursor.fetchall(sql)
finally:
cursor.close()
def get_user_draw_power_rank(server_id, date):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
sql = u"SELECT r.`rank_data` FROM rank_log r WHERE r.`rank_key` = 'ldsg_local_power_rank_key' AND r.`date` = '%s' " % (
date)
return cursor.fetchall(sql)
finally:
cursor.close()
def get_activity_list(server_id):
''' 跟住服务器ID拿活动列表'''
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
activity_list = cursor.fetchall("select * from system_activity")
finally:
cursor.close()
return activity_list
def get_system_mall_list(server_id):
''' 跟住服务器ID拿商品列表'''
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
system_mall_list = cursor.fetchall(
"select * from system_mall where tag in (1, 2)")
finally:
cursor.close()
return system_mall_list
def get_forces_list(server_id):
''' 跟住服务器ID拿关卡'''
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
forces_list = cursor.fetchall(
"select * from system_forces where forces_type = 3")
finally:
cursor.close()
return forces_list
def get_activity_list(server_id):
''' 跟住服务器ID拿活动列表'''
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
activity_list = cursor.fetchall("select * from system_activity")
finally:
cursor.close()
return activity_list
def sync_tool_table(server_id):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
cursor_loc = connection.cursor()
tool_infos = []
sqls = []
try:
colors = {0: u'白', 1: u'绿', 2: u'蓝', 3: u'紫', 4: u'橙', 5: u'鬼'}
sqls.append(u"truncate table system_tool;")
sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(0, 1, '元宝');")
sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(0, 2, '银币');")
sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(0, 6, '武将背包');")
sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(0, 7, '装备背包');")
sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(0, 8, '国战声望');")
infos = cursor.fetchall("select hero_name, hero_color, system_hero_id from system_hero")
logging.debug("sync system hero")
for info in infos:
system_hero_id = info["system_hero_id"]
hero_desc = '%s-%s' % (info["hero_name"], colors.get(info["hero_color"]))
#sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(%s, 3001, '%s');" % (system_hero_id, hero_desc))
tool_infos.append({"tool_id": system_hero_id, "tool_type": 3001, "tool_name": hero_desc})
infos = cursor.fetchall("select equip_name, color, equip_id from system_equip")
logging.debug("sync system equip")
for info in infos:
equip_id = info["equip_id"]
equip_desc = '%s-%s' % (info["equip_name"], colors.get(info["color"]))
#sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(%s, 2001, '%s');" % (equip_id, equip_desc))
tool_infos.append({"tool_id": equip_id, "tool_type": 2001, "tool_name": equip_desc})
infos = cursor.fetchall("select name, color, tool_id, type from system_tool")
logging.debug("sync system tool")
for info in infos:
tool_id = info["tool_id"]
tool_desc = '%s-%s' % (info["name"], colors.get(info["color"]))
tool_type = info["type"]
#sqls.append(u"insert into system_tool(tool_id, tool_type, tool_name) values(%s, %s, '%s');" % (tool_id, tool_type, tool_desc))
tool_infos.append({"tool_id": tool_id, "tool_type": tool_type, "tool_name": tool_desc})
for sql in sqls:
cursor_loc.execute(sql)
cursor_loc.insert(tool_infos, "system_tool")
finally:
cursor.close()
cursor_loc.close()
def get_system_mall_list(server_id):
''' 跟住服务器ID拿商品列表'''
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
system_mall_list = cursor.fetchall("select * from system_mall where tag in (1, 2)")
finally:
cursor.close()
return system_mall_list
def get_user_tool_use_log(server_id,
name,
start_time,
end_time,
use_type=0,
flag=0,
page=1,
pagesize=20,
tool_type=0,
tool_id=0):
"""获取用户道具日志"""
start = (page - 1) * pagesize
limit = pagesize
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
total, infos = 0, []
try:
user_info = cursor.fetchone("select * from user where lodo_id='%s'" %
name)
if not user_info:
user_info = cursor.fetchone(
"select * from user where username='******'" % name)
if user_info:
user_id = user_info['user_id']
table = "user_tool_use_log_%s" % table_util.get_table_index(
user_id)
where = " user_id = '%s' " % user_id
if use_type:
where += " and use_type = %s " % use_type
if flag:
where += " and flag = %s " % flag
if tool_type:
where += " and tool_type = %s " % tool_type
if tool_id:
where += " and tool_id = %s " % tool_id
if start_time:
where += " and created_time >= '%s' " % start_time
if end_time:
where += " and created_time <= '%s' " % end_time
sql = "select count(1) as total from %s where %s " % (table, where)
total = cursor.fetchone(sql)["total"]
if total > 0:
sql = "select * from %s where %s order by log_id desc limit %s, %s" % (
table, where, start, limit)
infos = cursor.fetchall(sql)
infos = infos.to_list()
finally:
cursor.close()
return total, infos
def get_user_hero_log(server_id, name, hero_name, start_time, end_time, use_type=0, flag=0, page=1, pagesize=20):
"""获取用户武将日志"""
start = (page - 1) * pagesize
limit = pagesize
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
total, infos = 0, []
try:
user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name)
if not user_info:
user_info = cursor.fetchone("select * from user where username='******'" % name)
if user_info:
user_id = user_info['user_id']
table = "user_hero_log_%s" % table_util.get_table_index(user_id)
where = " user_id = '%s' " % user_info['user_id']
if use_type:
where += " and use_type = %s " % use_type
if flag:
where += " and flag = %s " % flag
if start_time:
where += " and created_time >= '%s' " % start_time
if end_time:
where += " and created_time <= '%s' " % end_time
if hero_name:
where += " and system_hero_id in (select system_hero_id from system_hero where hero_name like '%%%s%%') " % hero_name
sql = "select count(1) as total from %s where %s " % (table, where)
total = cursor.fetchone(sql)["total"]
if total > 0:
sql = "select * from %s where %s order by log_id desc limit %s, %s" % (table, where, start, limit)
infos = cursor.fetchall(sql)
hero_info_map = {}
infos = infos.to_list()
for info in infos:
system_hero_id = info["system_hero_id"]
if system_hero_id in hero_info_map:
hero_info = hero_info_map[system_hero_id]
else:
hero_info = cursor.fetchone("select * from system_hero where system_hero_id = %s " % system_hero_id )
if hero_info:
hero_info_map[system_hero_id] = hero_info
info["hero_info"] = hero_info
finally:
cursor.close()
return total, infos
def open_all_scene(server_id, user_id):
db_config = server_business.get_server_db_connect(server_id=server_id)
table = "user_forces_%s" % table_util.get_table_index(user_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
sql = OPEN_FORCES_SQL % (table, user_id, table, user_id)
cursor.execute(sql)
finally:
cursor.close()
return True
def open_all_scene(server_id, user_id):
db_config = server_business.get_server_db_connect(server_id=server_id)
table = "user_forces_%s" % table_util.get_table_index(user_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
sql = OPEN_FORCES_SQL % (table, user_id, table, user_id)
cursor.execute(sql)
finally:
cursor.close()
return True
def main():
conn = get_connection(db_config)
cursor = conn.cursor()
f = open("data.csv", "rb")
ff = open("data_out.csv", "wb")
i = 0
for line in f:
if i == 0:
ff.write(line)
i += 1
continue
line = line.replace("\n", "")
line = line.decode("gbk").encode("utf8")
#print line
datas = line.split(",")
if len(datas) < 5:
continue
pid, partner_user_id, amount = datas[0], datas[6], datas[9]
amount = float(amount) * 100
pid = int(pid)
if pid == 1:
continue
if pid > 263:
break
sql = "select * from payment_order where partner_id = 1003 and partner_user_id = '%s' and amount = %s order by created_time desc limit 1" % (
partner_user_id, amount)
info = cursor.fetchone(sql)
if not info:
print sql
break
server_id = info["server_id"]
server_db_config = server_business.get_server_db_connect(
server_id=server_id)
server_conn = get_connection(server_db_config)
server_cursor = server_conn.cursor()
user_info = server_cursor.fetchone(
"select * from user where user_id = (select user_id from user_mapper where partner_user_id = '%s' and partner_id = 1003)"
% partner_user_id)
print server_id, user_info["lodo_id"]
datas[7] = server_id
datas[8] = "%s" % user_info["lodo_id"]
ff.write("%s\n" % ",".join(datas))
def get_payment_order_list(ucenter, name, server_id, page=1, pagesize=20):
start = (page - 1) * pagesize
limit = pagesize
ucenter_conn = get_connection(ldsg_center_db_configs[ucenter])
ucenter_cursor = ucenter_conn.cursor()
db_config = server_business.get_server_db_connect(server_id=server_id)
game_conn = get_connection(db_config)
game_cursor = game_conn.cursor()
total, infos = 0, []
try:
user_info = game_cursor.fetchone(
"select * from user where lodo_id='%s'" % name)
if not user_info:
user_info = game_cursor.fetchone(
"select * from user where username='******'" % name)
if user_info:
user_mapper_info = game_cursor.fetchone(
"select * from user_mapper where user_id = '%s'" %
user_info["user_id"])
partner_user_id = user_mapper_info["partner_user_id"]
where = " server_id = '%s' and partner_user_id = '%s'" % (
server_id, partner_user_id)
total = ucenter_cursor.fetchone(
"select count(1) as total from payment_order where %s order by created_time desc "
% where)["total"]
if total > 0:
sql = "select * from payment_order where %s order by created_time desc limit %s, %s" % (
where, start, limit)
infos = ucenter_cursor.fetchall(sql)
infos = infos.to_list()
for info in infos:
if not info["partner_order_id"]:
info["partner_order_id"] = ""
info["username"] = user_info["username"]
finally:
ucenter_cursor.close()
game_cursor.close()
return total, infos
def get_user_payment_rank(server_id, start_date, end_date, min_amount):
start_time = "%s 00:00:00" % start_date
end_time = "%s 23:59:59" % end_date
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
sql = u"select b.username, b.lodo_id, a.amount, a.count, a.max_amount from (select sum(amount) as amount, count(1) as count, max(amount) as max_amount, user_id from payment_log where created_time >= '%s' and created_time <= '%s' group by user_id)a " \
"left join user b on a.user_id = b.user_id where a.amount >= %s order by a.amount desc " % (start_time, end_time, min_amount)
logging.debug(sql)
return cursor.fetchall(sql)
finally:
cursor.close()
def get_user_payment_rank(server_id, start_date, end_date, min_amount):
start_time = "%s 00:00:00" % start_date
end_time = "%s 23:59:59" % end_date
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
try:
sql = u"select b.username, b.lodo_id, a.amount, a.count, a.max_amount from (select sum(amount) as amount, count(1) as count, max(amount) as max_amount, user_id from payment_log where created_time >= '%s' and created_time <= '%s' group by user_id)a " \
"left join user b on a.user_id = b.user_id where a.amount >= %s order by a.amount desc " % (start_time, end_time, min_amount)
logging.debug(sql)
return cursor.fetchall(sql)
finally:
cursor.close()
def main():
conn = get_connection(db_config)
cursor = conn.cursor()
f = open("data.csv", "rb")
ff = open("data_out.csv", "wb")
i = 0
for line in f:
if i == 0:
ff.write(line)
i += 1
continue
line = line.replace("\n", "")
line = line.decode("gbk").encode("utf8")
#print line
datas = line.split(",")
if len(datas) < 5:
continue
pid, partner_user_id, amount = datas[0], datas[6], datas[9]
amount = float(amount) * 100
pid = int(pid)
if pid == 1:
continue
if pid > 263:
break
sql = "select * from payment_order where partner_id = 1003 and partner_user_id = '%s' and amount = %s order by created_time desc limit 1" % (partner_user_id, amount)
info = cursor.fetchone(sql)
if not info:
print sql
break
server_id = info["server_id"]
server_db_config = server_business.get_server_db_connect(server_id=server_id)
server_conn = get_connection(server_db_config)
server_cursor = server_conn.cursor()
user_info = server_cursor.fetchone("select * from user where user_id = (select user_id from user_mapper where partner_user_id = '%s' and partner_id = 1003)" % partner_user_id)
print server_id, user_info["lodo_id"]
datas[7] = server_id
datas[8] = "%s" % user_info["lodo_id"]
ff.write("%s\n" % ",".join(datas))
def get_user_gold_use_log(server_id,
name,
start_time,
end_time,
use_type=0,
flag=0,
page=1,
pagesize=20,
amount=0):
"""获取用户金币日志"""
start = (page - 1) * pagesize
limit = pagesize
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
total, infos = 0, []
try:
user_info = cursor.fetchone("select * from user where lodo_id='%s'" %
name)
if not user_info:
user_info = cursor.fetchone(
"select * from user where username='******'" % name)
if user_info:
where = " user_id = '%s' " % user_info['user_id']
if use_type:
where += " and use_type = %s " % use_type
if flag:
where += " and flag = %s " % flag
if start_time:
where += " and created_time >= '%s' " % start_time
if end_time:
where += " and created_time <= '%s' " % end_time
if amount:
where += " and amount >= %s " % amount
total = cursor.fetchone(
"select count(1) as total from user_gold_use_log where %s " %
where)["total"]
if total > 0:
sql = "select * from user_gold_use_log where %s order by log_id desc limit %s, %s" % (
where, start, limit)
infos = cursor.fetchall(sql)
finally:
cursor.close()
return total, infos
def get_user_mail_list(server_id, name, source_id=0, page=1, pagesize=20):
db_config = server_business.get_server_db_connect(server_id=server_id)
total, infos = 0, []
connect = get_connection(db_config)
cursor = connect.cursor()
try:
user_info = cursor.fetchone(
"select user_id from user where username='******'" % name)
if not user_info:
user_info = cursor.fetchone(
"select user_id from user where lodo_id='%s'" % name)
if user_info:
user_id = user_info["user_id"]
table_index = table_util.get_table_index(user_id)
table = "user_mail_%s" % table_index
where = "user_id = '%s' " % user_id
if source_id:
mail_info = cursor.fetchone(
"select * from system_mail where source_id = %s " %
source_id)
if mail_info:
where += " and system_mail_id = '%s' " % mail_info[
"system_mail_id"]
else:
where += " and system_mail_id = '-1' "
total = cursor.fetchone(
"select count(1) as total from %s where %s " %
(table, where))["total"]
if total > 0:
start = (page - 1) * pagesize
infos = cursor.fetchall(
"select * from %s where %s order by created_time desc limit %s, %s"
% (table, where, start, pagesize))
infos = infos.to_list()
for info in infos:
info["mail_info"] = cursor.fetchone(
"select * from system_mail where system_mail_id ='%s'"
% info["system_mail_id"])
finally:
cursor.close()
return total, infos
def get_payment_order_list(ucenter, name, server_id, page=1, pagesize=20):
start = (page - 1) * pagesize
limit = pagesize
ucenter_conn = get_connection(ldsg_center_db_configs[ucenter])
ucenter_cursor = ucenter_conn.cursor()
db_config = server_business.get_server_db_connect(server_id=server_id)
game_conn = get_connection(db_config)
game_cursor = game_conn.cursor()
total, infos = 0, []
try:
user_info = game_cursor.fetchone("select * from user where lodo_id='%s'" % name)
if not user_info:
user_info = game_cursor.fetchone("select * from user where username='******'" % name)
if user_info:
user_mapper_info = game_cursor.fetchone("select * from user_mapper where user_id = '%s'" % user_info["user_id"])
partner_user_id = user_mapper_info["partner_user_id"]
where = " server_id = '%s' and partner_user_id = '%s'" % (server_id, partner_user_id)
total = ucenter_cursor.fetchone("select count(1) as total from payment_order where %s order by created_time desc " % where)["total"]
if total > 0:
sql = "select * from payment_order where %s order by created_time desc limit %s, %s" % (where, start, limit)
infos = ucenter_cursor.fetchall(sql)
infos = infos.to_list()
for info in infos:
if not info["partner_order_id"]:
info["partner_order_id"] = ""
info["username"] = user_info["username"]
finally:
ucenter_cursor.close()
game_cursor.close()
return total, infos
def get_user_payment_stat(server_id, start_date, end_date):
start_date = "%s 00:00:00" % start_date
end_date = "%s 23:59:59" % end_date
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
user_payment_stat = []
try:
infos = cursor.fetchall("SELECT partner_id, sum(amount) AS total FROM payment_log WHERE created_time BETWEEN '%s' AND '%s' GROUP BY partner_id ORDER BY total DESC" % (start_date, end_date))
#infos = infos.to_list()
total = 0
for info in infos:
total += info["total"]
user_payment_stat.append({"name": info["partner_id"], "total": info["total"]})
user_payment_stat.append({"name": u"总计", "total": total})
finally:
cursor.close()
return user_payment_stat
def get_user_tool_use_log(server_id, name, start_time, end_time, use_type=0, flag=0, page=1, pagesize=20, tool_type=0, tool_id=0):
"""获取用户道具日志"""
start = (page - 1) * pagesize
limit = pagesize
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
total, infos = 0, []
try:
user_info = cursor.fetchone("select * from user where lodo_id='%s'" % name)
if not user_info:
user_info = cursor.fetchone("select * from user where username='******'" % name)
if user_info:
user_id = user_info['user_id']
table = "user_tool_use_log_%s" % table_util.get_table_index(user_id)
where = " user_id = '%s' " % user_id
if use_type:
where += " and use_type = %s " % use_type
if flag:
where += " and flag = %s " % flag
if tool_type:
where += " and tool_type = %s " % tool_type
if tool_id:
where += " and tool_id = %s " % tool_id
if start_time:
where += " and created_time >= '%s' " % start_time
if end_time:
where += " and created_time <= '%s' " % end_time
sql = "select count(1) as total from %s where %s " % (table, where)
total = cursor.fetchone(sql)["total"]
if total > 0:
sql = "select * from %s where %s order by log_id desc limit %s, %s" % (table, where, start, limit)
infos = cursor.fetchall(sql)
infos = infos.to_list();
finally:
cursor.close()
return total, infos
def get_user_register(server_id, datetime):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor_curr = connection.cursor()
cursor = connect.cursor()
data = {}
try:
info = cursor_curr.fetchone(
"select 1 from stat_server_data where stat_date = '%s' and server_id = '%s' "
% (datetime, server_id))
total_user = cursor.fetchone(
"SELECT COUNT(distinct a.user_id) as total_user FROM `user` a , user_mapper b WHERE DATE(a.reg_time) <= '%s' AND a.`user_id` = b.`user_id`"
% (datetime))
data['total_user'] = int(total_user['total_user'])
if info:
temp = cursor_curr.fetchone(
"select create_user AS new_register,date_active from stat_server_data where stat_date = '%s' and server_id = '%s'"
% (datetime, server_id))
data['new_register'] = int(temp['new_register'])
data['date_active'] = int(temp['date_active'])
else:
info = cursor.fetchone(
"SELECT COUNT(user_id) AS new_register FROM `user` WHERE DATE(reg_time) = '%s'"
% (datetime))
data['new_register'] = int(info['new_register'])
active_user = 0
for i in range(128):
info = cursor.fetchone(
"select count(distinct user_id) as date_active from user_online_log_%d where date(login_time) = '%s';"
% (i, datetime))
active_user = active_user + int(info['date_active'])
data['date_active'] = active_user
finally:
cursor.close()
cursor_curr.close()
data['active'] = round(data['date_active'] / float(data['total_user']),
5) * 100
return data
def get_user_reg_stat_by_pid(server_id, partner_id, start_date, end_date):
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
admin_db_cursor = connection.cursor()
try:
data = {}
partner = admin_db_cursor.fetchone("SELECT * FROM partner WHERE partner_id='%s'" % partner_id)
reg_num = cursor.fetchone("SELECT COUNT(user_id) AS reg_num FROM user_mapper WHERE partner_id='%s' AND created_time BETWEEN '%s' AND '%s'" % (partner_id, start_date, end_date))
create_num = cursor.fetchone("SELECT COUNT(u.user_id) AS create_num FROM `user` u LEFT JOIN user_mapper m ON u.`user_id` = m.`user_id` WHERE m.`partner_id` = '%s' AND reg_time BETWEEN '%s' AND '%s'" % (partner_id,start_date, end_date))
data['reg_num'] = int(reg_num['reg_num'])
data['create_num']= int(create_num['create_num'])
data['partner_name'] = partner['name']
data['ratio'] = round(data['create_num']/ float(data['reg_num']), 4) * 100
finally:
cursor.close()
return data
def main():
conn = get_connection(server_business.get_server_db_connect(server_id="h26"))
cursor = conn.cursor()
maps = {}
hero_star_map = {}
try:
for i in range(128):
infos = cursor.fetchall("select * from user_hero_log_%s where use_type = 10002" % i)
for info in infos:
created_time = info["created_time"]
system_hero_id = info["system_hero_id"]
if system_hero_id in hero_star_map:
star = hero_star_map[system_hero_id]
else:
star = cursor.fetchone("select hero_star from system_hero where system_hero_id =%s " % system_hero_id)["hero_star"]
hero_star_map[system_hero_id] = star
if created_time in maps:
count_map = maps[created_time]
else:
count_map = {}
count = count_map.get(star, 0)
count += 1
count_map[star] = count
maps[created_time] = count_map
finally:
cursor.close()
for k, v in maps.iteritems():
if v.get(5, 0) >= 2:
print k, v.get(1, 0), v.get(2, 0), v.get(3, 0), v.get(4, 0), v.get(5, 0)
def get_user_level_up_log(server_id, name, page=1, pagesize=20):
"""获取用户升级日志"""
start = (page - 1) * pagesize
limit = pagesize
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
total, infos = 0, []
try:
user_info = cursor.fetchone("select * from user where lodo_id='%s'" %
name)
if not user_info:
user_info = cursor.fetchone(
"select * from user where username='******'" % name)
if user_info:
user_id = user_info['user_id']
table = "user_level_up_log"
where = " user_id = '%s' " % user_id
total = cursor.fetchone(
"select count(1) as total from %s where %s " %
(table, where))["total"]
if total > 0:
sql = "select * from %s where %s order by log_id desc limit %s, %s" % (
table, where, start, limit)
infos = cursor.fetchall(sql)
infos = infos.to_list()
for info in infos:
info["username"] = user_info["username"]
finally:
cursor.close()
return total, infos
try:
servers, _ = server_business.get_server_list(1, 30, 2)
for server in servers:
db_config = server_util.str_to_dbconfig(server.db_connect)
conn = get_connection(db_config)
cursor = conn.cursor()
try:
for table in tables:
try:
info = cursor.fetchone("select 1 from %s limit 1" % table)
except:
print "error:server:%s,table:%s" % (server.server_id, table)
finally:
cursor.close()
finally:
pass
if __name__ == "__main__":
for i in range(1, 25):
qa_db_config = server_business.get_server_db_connect(server_id="h%s" % i)
qa_conn = get_connection(qa_db_config)
qa_cursor = qa_conn.cursor()
print qa_cursor.fetchone("select count(1) as count from user_level_up_log")["count"]
def get_user_hero_log(server_id,
name,
hero_name,
start_time,
end_time,
use_type=0,
flag=0,
page=1,
pagesize=20):
"""获取用户武将日志"""
start = (page - 1) * pagesize
limit = pagesize
db_config = server_business.get_server_db_connect(server_id=server_id)
connect = get_connection(db_config)
cursor = connect.cursor()
total, infos = 0, []
try:
user_info = cursor.fetchone("select * from user where lodo_id='%s'" %
name)
if not user_info:
user_info = cursor.fetchone(
"select * from user where username='******'" % name)
if user_info:
user_id = user_info['user_id']
table = "user_hero_log_%s" % table_util.get_table_index(user_id)
where = " user_id = '%s' " % user_info['user_id']
if use_type:
where += " and use_type = %s " % use_type
if flag:
where += " and flag = %s " % flag
if start_time:
where += " and created_time >= '%s' " % start_time
if end_time:
where += " and created_time <= '%s' " % end_time
if hero_name:
where += " and system_hero_id in (select system_hero_id from system_hero where hero_name like '%%%s%%') " % hero_name
sql = "select count(1) as total from %s where %s " % (table, where)
total = cursor.fetchone(sql)["total"]
if total > 0:
sql = "select * from %s where %s order by log_id desc limit %s, %s" % (
table, where, start, limit)
infos = cursor.fetchall(sql)
hero_info_map = {}
infos = infos.to_list()
for info in infos:
system_hero_id = info["system_hero_id"]
if system_hero_id in hero_info_map:
hero_info = hero_info_map[system_hero_id]
else:
hero_info = cursor.fetchone(
"select * from system_hero where system_hero_id = %s "
% system_hero_id)
if hero_info:
hero_info_map[system_hero_id] = hero_info
info["hero_info"] = hero_info
finally:
cursor.close()
return total, infos
