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_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_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 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 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 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