def chenckAndudpateUserToken(self, qq, groupid, access_token,
refresh_token):
"""检查加绑定操作
Returns:
-1 数据库操作异常
-2 v2信息获取失败
"""
db = interMysql.Connect('osu2')
checkSql = '''
SELECT count(1) c FROM user WHERE qq=%s and groupid=%s
'''
checkArgs = [qq, groupid]
checkRet = db.query(checkSql, checkArgs)
if not checkRet[0]["c"]:
rs = self.insertUser(qq, groupid)
if not rs:
return -1
# 取一次用户信息
b = ppyHandler.ppyHandler()
status, ret = b.autov2Req2(qq, groupid, "me", access_token,
refresh_token)
if status < 0:
return -2
osuid = ret["id"]
osuname = ret["username"]
# 批量更新操作
upRet = self.udpateUsersInfo(qq, access_token, refresh_token, osuid,
osuname)
if upRet < 0:
return -1
return 1
def check_rec(bids, rec):
# 提取新记录成绩
conn = interMysql.Connect('osu')
sql = '''
SELECT bid, score, mods from recinfo where bid in (%s)
'''
sql = sql % (','.join(map(lambda x:'%s', bids)))
ret = conn.query(sql, bids)
if not ret:
# 成绩过滤
return rec
newRec = []
ret_dict = {}
# 最大成绩录入
max_dict = {}
for r in ret:
if r['bid'] in ret_dict:
ret_dict[r['bid']][r['mods']] = r['score']
if int(r['score']) > max_dict[r['bid']]:
max_dict[r['bid']] = int(r['score'])
else:
ret_dict[r['bid']] = {r['mods']: r['score']}
max_dict[r['bid']] = int(r['score'])
# 单mod成绩比较
for r in rec:
if r['beatmap_id'] in ret_dict and int(r['enabled_mods']) in ret_dict[r['beatmap_id']]:
if int(r['score']) > int(ret_dict[r['beatmap_id']][int(r['enabled_mods'])]) or int(r['score']) > max_dict[r['beatmap_id']]:
newRec.append(r)
else:
newRec.append(r)
return newRec
def get_user_list_fromDB(self):
db = interMysql.Connect('osu2')
sql = '''
SELECT distinct(osuname) from user
'''
ret = db.query(sql)
return [r["osuname"] for r in ret]
def get_user_stats_today(self, uid):
conn = interMysql.Connect('osu')
sql = 'SELECT * FROM user2 where osuid = %s order by time desc limit 1'
ret = conn.query(sql, uid)
if not ret:
return None
return ret
def hid_mytops(uid, groupid, hid=1, mods=-1):
# top1数量列表
conn = interMysql.Connect('osu')
sql = '''
SELECT bid FROM maprank where gid=%s and hid=%s and mods=%s and uid=%s
'''
ret = conn.query(sql, [groupid, hid, mods, uid])
return ret
def get_usernames_by_uid(self, uids):
conn = interMysql.Connect('osu')
sql = 'SELECT osuid,osuname FROM user where osuid in (%s)'
sql = sql % (','.join(map(lambda x:'%s', uids)))
ret = conn.query(sql, uids)
if not ret:
return None
return ret
def getBindNum2(self, qqids):
"""检查绑定数量-暴力对比版本
"""
db = interMysql.Connect('osu2')
sql = '''
SELECT distinct(qq) qq, osuname FROM user
'''
ret = db.query(sql)
return ret
def get_best_map_rec_from_db(self, bid, uid):
db = interMysql.Connect('osu')
sql = '''
SELECT * from recinfo where uid=%s and bid=%s
'''
ret = db.query(sql, [bid, uid])
if not ret:
return None
return ret[0]
def getBindNum(self, qqids):
"""检查绑定数量-in版本
"""
db = interMysql.Connect('osu2')
sql = '''
SELECT count(distinct(qq)) n FROM user where qq in %s
'''
ret = db.query(sql, [tuple(qqids)])
return ret[0]["n"]
def get_ircbind(osuname):
conn = interMysql.Connect('osu')
sql = '''
SELECT * from ircbind where osuname = %s
'''
ret = conn.query(sql, osuname)
if not ret:
return -1
return ret[0]['groupid']
def maps_top(bids, groupid, hid=1):
conn = interMysql.Connect('osu')
sql = '''
SELECT gid,hid,bid,mods,uid,type,lastdate,rankjson from maprank where gid = %s and hid = %s and bid in (%s)
'''
sql = sql % ('%s', '%s', ','.join(map(lambda x:'%s', bids)))
args = [groupid, hid] + bids
ret = conn.query(sql, args)
return ret
def insert_user(self, *user):
try:
db = interMysql.Connect('osu')
sql = 'insert into user2(username,pp,acc,pc,rank,tth,time,osuid) values(%s,%s,%s,%s,%s,%s,now(),%s)'
ret = db.execute(sql,tuple(user))
print('插入数据结果:'+str(ret))
db.commit()
except:
db.rollback()
logging.exception('插入失败')
def getFuncDistinctNodes(self):
"""取命令列表用到的节点
"""
db = interMysql.Connect('osu2')
sql = '''
SELECT distinct(location) location
FROM cmdRef where location != '' and location is not null
'''
rs = db.query(sql)
return rs
def getFuncNodes(self):
"""取命令列表功能的节点
"""
db = interMysql.Connect('osu2')
sql = '''
SELECT location, url
FROM cmdRef where location != '' and location is not null
'''
rs = db.query(sql)
return rs
def updateUserPermission(self, qq, level):
"""用户权限设置"""
db = interMysql.Connect('osu2')
sql = 'UPDATE user SET tokenpermission=%s WHERE qq=%s'
ret = db.execute(sql, (
level,
qq,
))
db.commit()
return ret
def tops_rank(groupid, hid=1):
# top榜排行
conn = interMysql.Connect('osu')
sql = '''
SELECT uid,count(uid) num FROM maprank
WHERE gid = %s and hid = %s and mods = %s
GROUP BY uid ORDER BY num desc LIMIT 10
'''
ret = conn.query(sql, [groupid, hid, -1])
return ret
def hid_ranks(bid, groupid, hid=1, mods=-1):
# 指定式查询 -- 未扩展
conn = interMysql.Connect('osu')
sql = '''
SELECT a.bid, a.mods, a.rankjson, b.title, b.artist, b.version, b.source, b.mapjson
from maprank a INNER JOIN beatmap b ON a.bid=b.bid
where a.gid = %s and a.hid = %s and a.bid = %s and a.mods = %s
'''
ret = conn.query(sql, [groupid, hid, bid, mods])
return ret
def filter_beatmapid(bids):
conn = interMysql.Connect('osu')
sql = '''
SELECT bid from beatmap where bid in (%s)
'''
sql = sql % (','.join(map(lambda x:'%s', bids)))
ret = conn.query(sql, bids)
db_bids = set([r['bid'] for r in ret])
new_bids = set(bids) - db_bids
return list(new_bids)
def get_user_fromDB(self, username, days=0):
db = interMysql.Connect('osu')
if not days:
time = self.today_date()
else:
time = self.get_daystime(days)
sql = '''
SELECT * from user2 where username=%s and time>=%s limit 1
'''
res = db.query(sql, (username, time))
return res
def getCmdRef(self, cmd):
"""映射"""
db = interMysql.Connect('osu2')
sql = '''
SELECT cmd, url, location, reply, at, toprivate, interactive, image
FROM cmdRef WHERE cmd = %s
'''
res = db.query(sql, [cmd])
if not res:
return ''
return res[0]
def getPermission(self, cmd):
"""权限查询"""
db = interMysql.Connect('osu2')
sql = '''
SELECT ptype, cmd, groupid, gtype
FROM permission WHERE cmd = %s
'''
res = db.query(sql, [cmd])
if not res:
return []
return res
def checkUser(self, osuid, osuname):
"""预测
"""
try:
botObj = botHandler.botHandler()
apiUserInfo = botObj.getOsuInfoFromAPI(osuid)
if not apiUserInfo:
return 0, 0, 0
pp = apiUserInfo[0]['pp_raw']
bpinfo = botObj.getRecBp(osuid, limit=10)
count_num = 0
count_pp = 0
maxpp = 0
conn = interMysql.Connect('osu')
for r in bpinfo:
maxcombo1 = int(r['maxcombo']) - 10
maxcombo2 = int(r['maxcombo']) + 10
c50 = float(r['count50'])
c100 = float(r['count100'])
c300 = float(r['count300'])
cmiss = float(r['countmiss'])
acc = round((c50 * 50 + c100 * 100 + c300 * 300) /
(c50 + c100 + c300 + cmiss) / 300 * 100, 2)
acc1 = acc - 0.2
acc2 = acc + 0.2
args = [
r['beatmap_id'], r['enabled_mods'], acc1, acc2, maxcombo1,
maxcombo2
]
sql = '''
SELECT avg(u.pp_raw) a, count(1) b
FROM osu_bp b INNER JOIN osu_user u on b.user_id=u.user_id
WHERE b.beatmap_id = %s and b.mods=%s and b.acc BETWEEN %s and %s and b.maxcombo BETWEEN %s and %s
'''
res = conn.query(sql, args)
res = res[0]
if res['a'] is None:
continue
if res['a'] > maxpp:
maxpp = res['a']
if res['b'] != 1:
count_num += 1
count_pp += res['a']
if count_num == 0:
yugu_pp = pp
else:
yugu_pp = round(count_pp / count_num)
if maxpp == 0:
maxpp = float(pp)
return pp, yugu_pp, round(maxpp)
except:
logging.error(traceback.format_exc())
return 0, 0, 0
def getCmdRef(cmd):
"""映射"""
db = interMysql.Connect('osu2')
sql = '''
SELECT cmd, url, location
FROM cmdRef WHERE cmd = %s
'''
res = db.query(sql, [cmd])
if not res:
return ''
return res[0]
def get_alias(cname, rtype=1):
conn = interMysql.Connect('osu')
sql = '''
SELECT * from alias where cname = %s
'''
ret = conn.query(sql, cname)
if not ret:
return -1
if rtype == 1:
return ret[0]['bid']
elif rtype == 2:
return ret[0]['uid']
def osuBeatmapInfoFromDb(self, bid):
db = interMysql.Connect('osu')
sql = '''
SELECT mapjson
FROM beatmap WHERE bid = %s
'''
rs = db.query(sql, [bid])
if not rs:
return {}
else:
mapInfo = rs[0]
return json.loads(mapInfo['mapjson'])
def udpateUserToken(self, qq, gourpid, access_token, refresh_token):
"""绑定token
"""
db = interMysql.Connect('osu2')
sql = '''
UPDATE user
SET acesstoken = %s, refreshtoken = %s
WHERE qq = %s and groupid = %s
'''
args = [access_token, refresh_token, qq, gourpid]
ret = db.execute(sql, args)
db.commit()
return ret
def is_insert_today(self):
"""今天是否插入过
"""
db = interMysql.Connect('osu')
time = self.today_date()
sql = '''
SELECT count(1) cnt from user2 where time>=%s
'''
ret = db.query(sql, time)
if ret[0]["cnt"]:
return True
else:
return False
def get_id_cmd_num(self, osuname):
conn = interMysql.Connect('osu2')
sql = '''SELECT cmd FROM cmdRef where cmd like %s order by id desc limit 1'''
ret = conn.query(sql, (osuname+'%', ))
logging.info('ret:%s', ret)
if not ret:
return None
cmd = ret[0]['cmd']
n = cmd[len(osuname):]
if not n:
return 0
else:
return int(n)
def delalias(cname):
try:
conn = interMysql.Connect('osu')
sql = '''
DELETE from alias where cname = %s
'''
ret = conn.execute(sql, cname)
conn.commit()
logging.info('alias删除记录 %s' % ret)
return ret
except:
conn.rollback()
traceback.print_exc()
return -1
def queryRoomInfo(self, qqid, groupid):
"""读库
"""
db = interMysql.Connect('osu')
sql = '''
SELECT id, roomname, mplink, password,
uid, rtype, status, starttime,
createtime
FROM room
WHERE qq = %s and groupid = %s
'''
args = [qqid, groupid]
rs = db.query(sql, args)
return rs
