def register():
# return ""
if request.method == 'POST':
db = get_db()
cur = db.cursor(dictionary=True)
username = request.form['username']
password = request.form['password']
error = None
if not username:
error = '请输入用户名.'
elif not password:
error = '请输入密码.'
else:
cur.execute(
'SELECT user_id FROM user WHERE user_name = "{}"'
.format(username)
)
if cur.fetchone() is not None:
error = '用户 {} 已存在.'.format(username)
if error is None:
cur.execute('''INSERT INTO user (user_name, password, pri)
VALUES ("{}", "{}", 10)'''.format(
username, generate_password_hash(password)))
db.commit()
return redirect(url_for('index'))
flash(error)
return render_template('auth/register.html')
def login():
if request.method == 'POST':
db = get_db()
cur = db.cursor(dictionary=True)
username = request.form['username']
password = request.form['password']
error = None
cur.execute(
'SELECT * FROM user WHERE user_name = "{}"'.format(username)
)
user = cur.fetchone()
if user is None:
error = '用户名不存在.'
elif check_password_hash(user['password'], password) is False:
error = '密码错误.'
if error is None:
session.clear()
session['user_id'] = user['user_id']
return redirect(url_for('index'))
flash(error)
return render_template('auth/login.html')
def supercup():
db = get_db()
cur = db.cursor(dictionary=True)
return render_template('background/supercup.html',
name=sidebar_items_bg['supercup']['name'],
sidebar_items=sidebar_items_bg)
def delete_schedule():
db = get_db()
cur = db.cursor(dictionary=True)
season_id = request.form['season_id']
sql = '''
update matches set team1_score=null, team2_score=null
where season_id={}
'''.format(season_id)
cur.execute(sql)
db.commit()
sql = '''
update player,team set scores=0
where player.team_id=team.team_id and season_id={}
'''.format(season_id)
cur.execute(sql)
db.commit()
sql = '''
delete from matches where season_id={}
'''.format(season_id)
cur.execute(sql)
db.commit()
return ""
def player_register():
db = get_db()
cur = db.cursor(dictionary=True)
sql = '''
SELECT team_name, team_id FROM team
WHERE user_id={} and season_id = {}
'''.format(g.user['user_id'], cfg['league_season_id'])
cur.execute(sql)
league_teams = cur.fetchall()
sql = '''
SELECT team_name, team_id FROM team
WHERE user_id={} and season_id = {}
'''.format(g.user['user_id'], cfg['cup_season_id'])
cur.execute(sql)
cup_teams = cur.fetchall()
return render_template('manage/player_register.html',
name=sidebar_items['player_register']['name'],
sidebar_items=sidebar_items,
league_teams=league_teams,
cup_teams=cup_teams
)
def add_player():
if request.method == 'POST':
db = get_db()
cur = db.cursor(dictionary=True)
if re.search(',', request.form['player_name']) is not None:
players = request.form['player_name'].split(',')
elif re.search('，', request.form['player_name']) is not None:
players = request.form['player_name'].split('，')
elif re.search(';', request.form['player_name']) is not None:
players = request.form['player_name'].split(';')
elif re.search('；', request.form['player_name']) is not None:
players = request.form['player_name'].split('；')
else:
players = [request.form['player_name']]
sql = 'insert into player (player_name,team_id,scores) values '
for player in players:
sql = sql + '("{}",{},{}),'.format(player.strip(),
request.form['team_id'], 0)
# sql = '''
# insert into player (player_name,team_id,scores) values ("{}",{},{})
# '''.format(request.form['player_name'], request.form['team_id'], 0)
cur.execute(sql[:-1])
db.commit()
return "OK"
def league():
db = get_db()
cur = db.cursor(dictionary=True)
# scoreboard
sql = '''
SELECT team_name, win, draw, lose,
goals_for, goals_against
FROM team, scoreboard
WHERE team.team_id=scoreboard.team_id and team.season_id = {}
'''.format(cfg['league_season_id'])
cur.execute(sql)
sb = cur.fetchall()
for i in range(len(sb)):
sb[i]["matches"] = sb[i]['win'] + sb[i]['draw'] + sb[i]['lose']
sb[i]["goals_diff"] = sb[i]['goals_for'] - sb[i]['goals_against']
sb[i]["score"] = sb[i]['win'] * 3 + sb[i]['draw']
# 排序
sb.sort(key=lambda x: (x['score'], x['goals_diff'], x['goals_for']),
reverse=True)
for i in range(len(sb)):
sb[i]['rank'] = i + 1
# matches
sql = '''
SELECT match_id, team1_id, team2_id,
t1.team_name as team1, t2.team_name as team2,
team1_score, team2_score
FROM matches as m, team as t1, team as t2
WHERE m.team1_id=t1.team_id and m.team2_id=t2.team_id
and m.season_id = {} and m.round_id = {}
'''.format(cfg['league_season_id'],
cfg['league_round_id'])
cur.execute(sql)
matches = cur.fetchall()
# scoring chart
sql = '''
SELECT team_name, player_name, scores
FROM player as p, team as t
WHERE t.season_id = {} and t.team_id=p.team_id and scores>0
ORDER BY scores desc
'''.format(cfg['league_season_id'])
cur.execute(sql)
scoring_chart = cur.fetchall()
return render_template('league/league.html',
name=sidebar_items['league']['name'],
season_id=cfg['league_season_id'],
sidebar_items=sidebar_items,
scoreboard=sb,
matches=matches,
scoring_chart=scoring_chart)
def supercup():
db = get_db()
cur = db.cursor(dictionary=True)
return render_template(
'league/supercup.html',
name=sidebar_items['supercup']['name'],
season_id=cfg['supercup_season_id'],
sidebar_items=sidebar_items,
)
def disable_player():
if request.method == 'POST':
db = get_db()
cur = db.cursor(dictionary=True)
sql = '''
update player set status="disable" where player_id={}
'''.format(request.form['player_id'])
cur.execute(sql)
db.commit()
return "OK"
def del_player():
if request.method == 'POST':
db = get_db()
cur = db.cursor(dictionary=True)
sql = '''
delete from player where player_id={}
'''.format(request.form['player_id'])
cur.execute(sql)
db.commit()
return "OK"
def rename_team():
if request.method == 'POST':
db = get_db()
cur = db.cursor(dictionary=True)
sql = '''
update team set team_name="{}" where team_id={}
'''.format(request.form['new_team_name'], request.form['team_id'])
cur.execute(sql)
db.commit()
return "OK"
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
db = get_db()
cur = db.cursor(dictionary=True)
cur.execute(
'SELECT user_id, user_name, pri FROM user WHERE user_id = {}'.format(
user_id))
g.user = cur.fetchone()
def get_players():
db = get_db()
cur = db.cursor(dictionary=True)
team_id = request.form['team_id']
sql = '''
select player_id, player_name, scores from player where team_id={}
and status="enable" order by scores desc
'''.format(team_id)
cur.execute(sql)
players = cur.fetchall()
return json.dumps(players)
def create_season(which):
db = get_db()
cur = db.cursor(dictionary=True)
season_name = request.form['name']
sql = '''
insert into season (season_name,season_type,season_status)
values ("{}","{}","{}")
'''.format(season_name, which, 'y')
cur.execute(sql)
db.commit()
return redirect(url_for('background.main'))
def cup_chouqian():
db = get_db()
cur = db.cursor(dictionary=True)
output = str()
sql = '''
select team_id, team_name from team where season_id={}
'''.format(cfg['cup_season_id'])
cur.execute(sql)
teams = np.array(cur.fetchall())
indeices = math.ceil(len(teams) / float(cfg['cup_group_member']))
# 删除原来的记录
sql = 'delete from matches where season_id={}'.format(cfg['cup_season_id'])
cur.execute(sql)
db.commit()
if len(teams) == 0:
return "清空赛程"
# 随机分组
np.random.shuffle(teams)
groups = np.array_split(teams, indeices)
# 生成赛程
for group, i in zip(groups, range(len(groups))):
output += "group" + chr(65 + i) + ":<br>"
# 输出分组结果
for team in group:
sql = '''update team set group_id={}
where team_id={}'''.format(i, team['team_id'])
cur.execute(sql)
db.commit()
# 小组赛程
match_gen = robin.RoundRobinScheduler(group.tolist(), meetings=1)
rounds = match_gen.generate_schedule()
for round, j in zip(rounds, range(len(rounds))):
output += " round " + str(j + 1) + "<br>"
for match in round:
if (match[0] is not None and match[1] is not None):
output += "  " + str(match[0]['team_name'])
output += " vs " + str(match[1]['team_name']) + "<br>"
sql = '''
insert into matches (season_id, round_id,
team1_id, team2_id) values
({}, {}, {}, {})
'''.format(cfg['cup_season_id'], j + 1,
match[0]['team_id'], match[1]['team_id'])
cur.execute(sql)
db.commit()
return output
def league():
db = get_db()
cur = db.cursor(dictionary=True)
sql = '''
SELECT team_name, team_id FROM team
WHERE season_id = {}
'''.format(cfg['league_season_id'])
cur.execute(sql)
league_teams = cur.fetchall()
return render_template('background/league.html',
name=sidebar_items_bg['main']['name'],
sidebar_items=sidebar_items_bg,
current_cfg=cfg,
league_teams=league_teams)
def get_schedule():
db = get_db()
cur = db.cursor(dictionary=True)
season_id = request.form['season_id']
sql = '''
SELECT match_id, round_id, t1.group_id as group_id,
t1.team_name as team1, t2.team_name as team2,
team1_score, team2_score
FROM matches as m, team as t1, team as t2
WHERE m.team1_id=t1.team_id and m.team2_id=t2.team_id
and m.season_id = {} order by round_id
'''.format(season_id)
cur.execute(sql)
rounds = cutList1(cur.fetchall(), "round_id")
return json.dumps(rounds)
def save_schedule():
db = get_db()
cur = db.cursor(dictionary=True)
season_id = request.form['season_id']
schedule = json.loads(request.form['schedule'])
for round in schedule:
for match in round:
sql = '''
insert into matches (season_id, round_id,
team1_id, team2_id) values ({},{},{},{})
'''.format(season_id, match['round_id'], match['team1_id'],
match['team2_id'])
cur.execute(sql)
db.commit()
return "OK"
def add_team(which):
if request.method == 'POST':
db = get_db()
cur = db.cursor(dictionary=True)
if which == 'league':
season_id = cfg['league_season_id']
elif which == 'cup':
season_id = cfg['cup_season_id']
elif which == 'supercup':
season_id = cfg['supercup_season_id']
sql1 = '''
insert into team (team_name, user_id, season_id)
values('{}', {}, {})
'''.format(request.form['team_name'], g.user['user_id'], season_id)
cur.execute(sql1)
db.commit()
return redirect(url_for('league.team_manage'))
def main():
db = get_db()
cur = db.cursor(dictionary=True)
league_list = dict()
cup_list = dict()
supercup_list = dict()
# 联赛
sql = '''select season_id, season_name from season
where season_type='league' and season_status='y'
'''
cur.execute(sql)
league = cur.fetchall()
for l in league:
league_list[l['season_id']] = l['season_name']
# 杯赛
sql = '''select season_id, season_name from season
where season_type='cup' and season_status='y'
'''
cur.execute(sql)
cup = cur.fetchall()
for l in cup:
cup_list[l['season_id']] = l['season_name']
# 超级杯
sql = '''select season_id, season_name from season
where season_type='supercup' and season_status='y'
'''
cur.execute(sql)
supercup = cur.fetchall()
for l in supercup:
supercup_list[l['season_id']] = l['season_name']
return render_template('background/main.html',
name=sidebar_items_bg['main']['name'],
sidebar_items=sidebar_items_bg,
current_cfg=cfg,
league_list=league_list,
cup_list=cup_list,
supercup_list=supercup_list)
def submit_match_result():
if request.method == 'POST':
db = get_db()
cur = db.cursor(dictionary=True)
sql = '''
select team1_score, team2_score from matches
where match_id={}
'''.format(request.form['match_id'])
cur.execute(sql)
match = cur.fetchone()
if match['team1_score'] is not None and match[
'team2_score'] is not None:
return "不可重复提交！"
team1_scores = json.loads(request.form['team1_scores'])
team2_scores = json.loads(request.form['team2_scores'])
team1_score = 0
team2_score = 0
for s in team1_scores:
team1_score = team1_score + int(s['scores'])
sql = '''
update player set scores=scores+{} where player_id={}
'''.format(s['scores'], s['player_id'])
cur.execute(sql)
db.commit()
for s in team2_scores:
team2_score = team2_score + int(s['scores'])
sql = '''
update player set scores=scores+{} where player_id={}
'''.format(s['scores'], s['player_id'])
cur.execute(sql)
db.commit()
sql = '''
update matches set team1_score={}, team2_score={}
where match_id={}
'''.format(team1_score, team2_score, request.form['match_id'])
cur.execute(sql)
db.commit()
return "OK!"
def generate_schedule():
db = get_db()
cur = db.cursor(dictionary=True)
season_id = request.form['season_id']
sql = '''
select season_type from season where season_id={}
'''.format(season_id)
cur.execute(sql)
season_type = cur.fetchone()['season_type']
sql = '''
select team_id, team_name from team where season_id={}
'''.format(season_id)
cur.execute(sql)
teams = np.array(cur.fetchall())
schedule = list()
if season_type == 'league':
match_gen = robin.RoundRobinScheduler(teams.tolist(), meetings=2)
rounds = match_gen.generate_schedule()
for round, j in zip(rounds, range(len(rounds))):
for match in round:
if (match[0] is not None and match[1] is not None):
schedule.append({
"round_id": j + 1,
"team1_id": match[0]['team_id'],
"team1": match[0]['team_name'],
"team2_id": match[1]['team_id'],
"team2": match[1]['team_name']
})
schedule.sort(key=lambda x: x['round_id'])
schedule = cutList1(schedule, 'round_id')
elif season_type == 'cup':
indeices = math.ceil(len(teams) / float(cfg['cup_group_member']))
# 随机分组
np.random.shuffle(teams)
groups = np.array_split(teams, indeices)
# 生成赛程
for group, i in zip(groups, range(len(groups))):
# 小组赛程
match_gen = robin.RoundRobinScheduler(group.tolist(), meetings=1)
rounds = match_gen.generate_schedule()
for round, j in zip(rounds, range(len(rounds))):
for match in round:
if (match[0] is not None and match[1] is not None):
schedule.append({
"round_id": j + 1,
"group_id": i + 1,
"team1_id": match[0]['team_id'],
"team1": match[0]['team_name'],
"team2_id": match[1]['team_id'],
"team2": match[1]['team_name']
})
schedule.sort(key=lambda x: x['round_id'])
schedule = cutList1(schedule, 'round_id')
return json.dumps(schedule)
