def check_rec(bids, rec):
# 提取新记录成绩
conn = interMysql.Connect()
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 msg_recollect(globValue, gV_Lock):
try:
conn = interMysql.Connect()
sql = '''
SELECT content FROM chat_logs
'''
csql = 'SELECT count(1) c FROM chat_logs'
num = conn.query(csql)[0]['c']
if Config.DEBUG == 1:
sql += ' limit %s,10000' % random.randint(0, int(num)-10000)
res = conn.query(sql)
if not res:
return
shuf_res = res
random.shuffle(shuf_res)
limit_cnt = Config.LIMIT_SEQ_NUM
msglist = globValue.get('msglist', set([]))
for i in range(len(msglist)):
msglist.pop()
for r in shuf_res:
if len(msglist) > limit_cnt:
break
if len(r['content']) < 30:
msglist.add(r['content'])
gV_Lock.acquire()
globValue['msglist'] = msglist
gV_Lock.release()
logging.info('词库自动更新,条数:%s' % len(msglist))
except:
traceback.print_exc()
return
def get_user_stats_today(self, uid):
conn = interMysql.Connect()
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 get_user_from_db2(self, uname):
conn = interMysql.Connect()
sql = 'SELECT * FROM user where osuname = %s'
ret = conn.query(sql, uname)
if not ret:
return None
return ret[0]
def hid_mytops(uid, groupid, hid=1, mods=-1):
# top1数量列表
conn = interMysql.Connect()
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_user_from_db3(self, oids):
conn = interMysql.Connect()
sql = 'SELECT * FROM user where osuid in (%s)'
sql = sql % (','.join(map(lambda x: '%s', oids)))
ret = conn.query(sql, oids)
if not ret:
return None
return ret
def get_usernames_by_uid(self, uids):
conn = interMysql.Connect()
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 maps_top(bids, groupid, hid=1):
conn = interMysql.Connect()
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 searchExpect(self, expect, gid):
conn = interMysql.Connect()
sql = '''
SELECT * FROM chartexpect
WHERE expect = %s and gid = %s
'''
args = [expect, gid]
ret = conn.query(sql, args)
return ret
def get_ircbind(osuname):
conn = interMysql.Connect()
sql = '''
SELECT * from ircbind where osuname = %s
'''
ret = conn.query(sql, osuname)
if not ret:
return -1
return ret[0]['groupid']
def tops_rank(groupid, hid=1):
# top榜排行
conn = interMysql.Connect()
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()
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()
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_alias(cname, rtype=1):
conn = interMysql.Connect()
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 chatlog():
try:
conn = interMysql.Connect()
sql = '''
SELECT * FROM chat_logs
'''
res = conn.query(sql)
if not res:
return []
return res
except:
traceback.print_exc()
def get_user_from_db(self, qq, gid=None):
conn = interMysql.Connect()
sql = 'SELECT * FROM user where qq = %s'
args = [qq]
if gid:
sql += ' and groupid=%s'
args.append(str(gid))
ret = conn.query(sql, args)
if not ret:
return None
self.userdbinfo = ret[0]
return self.userdbinfo
def check_user(self, uid):
'''pp估计计算'''
try:
userinfo = self.get_user_info(uid)
if not userinfo:
return 0, 0, 0
bpinfo = self.get_user_bp(uid)
pp = userinfo['pp_raw']
count_num = 0
count_pp = 0
maxpp = 0
conn = interMysql.Connect()
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:
traceback.print_exc()
return 0, 0, 0
def delalias(cname):
try:
conn = interMysql.Connect()
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 alias2db(cname, bid='', uid=''):
try:
conn = interMysql.Connect()
sql = '''
INSERT into alias(bid, uid, cname)
VALUES(%s, %s, %s)
'''
ret = conn.execute(sql, [bid, uid, cname])
conn.commit()
logging.info('alias入库记录 %s' % ret)
return ret
except:
conn.rollback()
traceback.print_exc()
return -1
def talkFromDB(groupid, nums):
try:
conn = interMysql.Connect()
sql = '''
SELECT qq, count(1) cnt
FROM chat_logs
WHERE group_number=%s GROUP BY qq
ORDER BY cnt desc limit %s'''
args = [groupid, nums]
ret = conn.query(sql, args)
if not ret:
return []
return ret
except:
traceback.print_exc()
return
def map2db(args):
try:
conn = interMysql.Connect()
sql = '''
INSERT into beatmap(bid, source, artist, title, version, creator, stars, addtime, mapjson)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
source=%s, artist=%s, title=%s, version=%s, creator=%s, stars=%s, addtime=%s, mapjson=%s
'''
ret = conn.executeMany(sql, args)
conn.commit()
logging.info('map入库记录 %s' % ret)
return ret
except:
conn.rollback()
traceback.print_exc()
def rec2db(args):
try:
conn = interMysql.Connect()
sql = '''
INSERT into recinfo(hid, bid, uid, score, maxcombo, mods, playdate, lastdate, rank, recjson)
VALUES(%s, %s, %s, %s, %s, %s, %s, now(), %s, %s)
ON DUPLICATE KEY UPDATE
score=%s, maxcombo=%s, playdate=%s, lastdate=now(), rank=%s, recjson=%s
'''
ret = conn.executeMany(sql, args)
conn.commit()
logging.info('rec入库记录 %s' % ret)
return ret
except:
conn.rollback()
traceback.print_exc()
def Chat2DB(groupid, qq, content):
try:
conn = interMysql.Connect()
now = datetime.datetime.now()
createtime = now.strftime('%Y-%m-%d %H:%M:%S')
# 插入数据
sql = '''
INSERT INTO chat_logs (group_number,qq,content,create_time)
VALUES ( %s, %s, %s, now())
'''
args = [groupid, qq, content]
conn.execute(sql, args)
conn.commit()
except:
traceback.print_exc()
conn.rollback()
return
def cmdFromDB(groupid, nums):
try:
conn = interMysql.Connect()
sql = '''
SELECT content,count(1) cnt
FROM chat_logs
WHERE group_number=%s and content like '!%%'
GROUP BY content
ORDER BY cnt desc LIMIT %s'''
args = [groupid, nums]
ret = conn.query(sql, args)
if not ret:
return []
return ret
except:
traceback.print_exc()
return
def chartexpect2DB(self, expect, gid, hid, bidjson, starttime, endtime):
try:
conn = interMysql.Connect()
sql = '''
insert into chartexpect
(expect, gid, hid, bidjson, starttime, endtime)
values
(%s,%s,%s,%s,%s,%s)
'''
args = [expect, gid, hid, bidjson, starttime, endtime]
ret = conn.execute(sql, args)
conn.commit()
return ret
except:
traceback.print_exc()
conn.rollback()
return 0
def chart2DB(self, hid, bid, gid, admin, mod, starttime, endtime, intro, resultjson, pprange, expect):
try:
conn = interMysql.Connect()
sql = '''
insert into chart
(hid, bid, gid, admin, `mod`, starttime, endtime, intro, resultjson, pprange, modifytime, expect)
values
(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,now(),%s)
'''
args = [hid, bid, gid, admin, mod, starttime, endtime, intro, resultjson, pprange, expect]
ret = conn.execute(sql, args)
conn.commit()
return ret
except:
traceback.print_exc()
conn.rollback()
return 0
def bind_group_irc(osuname, groupid, qq):
try:
conn = interMysql.Connect()
sql = '''
INSERT into ircbind(osuname, groupid, qq)
VALUES(%s, %s, %s)
ON DUPLICATE KEY UPDATE
groupid=%s, qq=%s
'''
ret = conn.execute(sql, [osuname, groupid, qq, groupid, qq])
conn.commit()
logging.info('ircbind入库记录 %s' % ret)
return ret
except:
conn.rollback()
traceback.print_exc()
return -1
def rank2db(args):
try:
# print('入库参数:%s'%args)
conn = interMysql.Connect()
sql = '''
INSERT into maprank(gid, hid, bid, uid, type, mods, lastdate, rankjson)
VALUES(%s, %s, %s, %s, %s, %s, now(), %s)
ON DUPLICATE KEY UPDATE
uid=%s, type=%s, lastdate=now(), rankjson=%s
'''
ret = conn.executeMany(sql, args)
conn.commit()
logging.info('rank入库记录 %s' % ret)
print('rank入库记录 %s' % ret)
return ret
except:
conn.rollback()
traceback.print_exc()
def choiceMap(self, uid):
'''低端推荐pp图'''
try:
conn = interMysql.Connect()
pp = self.get_user_pp(uid)
if not pp:
return 0, 0
pp = float(pp)
sql = '''
SELECT beatmap_id,count(beatmap_id) num FROM osu_user ta INNER JOIN osu_bp tb on ta.user_id = tb.user_id where ta.pp_raw BETWEEN %s and %s GROUP BY beatmap_id ORDER BY num desc limit 0,20;
'''
res = conn.query(sql, [pp, pp + 20])
if not res:
return 0, 0
ret = random.choice(res)
return ret['beatmap_id'], ret['num']
except:
traceback.print_exc()
return 0, 0
def insert2DB(self, qq, osuid, groupid, osuname, name=None):
try:
conn = interMysql.Connect()
sql = '''
insert into user
(qq, osuid, name, groupid, osuname)
values
(%s,%s,%s,%s,%s)
on duplicate key update
osuid = %s, osuname = %s, name = %s
'''
args = [qq, osuid, name, groupid, osuname, osuid, osuname, name]
ret = conn.execute(sql, args)
conn.commit()
return ret
except:
traceback.print_exc()
conn.rollback()
return 0
