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)