def playerBio(): if 'player_id' in session: id = session['player_id'] query = 'SELECT * FROM v_player WHERE player_id = %s' %id player = selectOne(query) user = session['player_name'] query = '''SELECT * FROM v_team_roster WHERE player_id = %s AND Year = Year(CURDATE()) ''' % id teamInfo = selectOne(query) query = '''SELECT * FROM Player WHERE player_id = %s''' % id personal = selectOne(query) if teamInfo: team = teamInfo['team_name'] else: team = None if 'error' in session: error = session.pop('error', None) action = session.pop('action', None) else: error = [] action = None return render_template('playerBio.html', player=player, user=user, error=error, action=action, team=team, personal=personal) else: return redirect(url_for('playerLogin'))
def teamBio(): if 'team_id' in session: captain = None action = None id = session['team_id'] query = 'SELECT * FROM v_team_wins WHERE team_id =%i' % id team = selectOne(query) query = (''' SELECT * FROM v_team_roster WHERE team_id=%i AND Year=Year(CURDATE())''' % id) players = selectAll(query) query = 'SELECT * FROM Team WHERE team_id=%i' %id c = selectOne(query) if c is not None: captain = c['captain'] if 'error' in session: error = session.pop('error',None) action = session.pop('action', None) else: error = [] return render_template('teamBio.html', team=team, players=players, error=error, action=action, captain=captain ) else: return redirect(url_for('teamLogin'))
def playerPicture(id): query = "SELECT * FROM Player WHERE player_id = %s" % id player = selectOne(query) imageBlob = player['picture'] if imageBlob is None: query = "SELECT * FROM Photos WHERE photo_id=1" picture = selectOne(query) imageBlob = picture['picture'] return imageBlob
def teamPicture(id): query = "SELECT * FROM Team WHERE team_id = %s" % id team = selectOne(query) imageBlob = team['team_pic'] if imageBlob is None: query = "SELECT * FROM Photos WHERE photo_id=2" picture = selectOne(query) imageBlob = picture['picture'] return imageBlob
def tree(): tree = {"name": "Player Stats"} query = ''' SELECT * FROM Team ''' teams = selectAll(query) team_list = [] for team in teams: team_dictionary = {"name":team['team_name']} query = ''' SELECT * FROM v_team_roster WHERE team_id = %s AND Year = YEAR(CURDATE())''' % team['team_id'] players = selectAll(query) player_list = [] for player in players: player_dictionary = {"name":player['player_name']} query = ''' SELECT * FROM fras2560.v_player_stats WHERE player_id = %s AND YEAR=Year(Curdate()); ''' % player['player_id'] stats = selectOne(query) if stats: player_dictionary['size'] = stats['Goals'] else: player_dictionary['size'] = 0 player_list.append(player_dictionary) if player_list == []: team_dictionary['size'] = 0 else: team_dictionary['children'] = player_list team_list.append(team_dictionary) tree['children'] = team_list return json.dumps(tree)
def find_first(cls,where,*args): ''' Find by where clause and return one result. If multiple results found, only the first one returned. If no result found, return None. ''' d = db.selectOne('select * from %s %s '%(cls.__table__, where),*args) return cls(**d) if d else None
def cutPlayer(): if 'team_id' not in session: return redirect(url_for('teamLogin')) team = session['team_id'] player = request.form['player_id'] query = ''' DELETE FROM Team_Roster WHERE player_id = %s AND team_id = %s AND year = Year(CURDATE())''' % (player, team) process(query) query = 'SELECT * FROM Player where player_id = %s' % player p = selectOne(query) player_name = p['player_name'] query = 'SELECT * FROM Team where team_id = %s' % team t = selectOne(query) team_name = t['team_name'] news = "%s has been released from the %s" %(player_name, team_name) writeNews(news) return redirect(url_for('teamRosterForm'))
def teamPage(id): captain = None query = 'SELECT * FROM v_team_wins WHERE team_id =%s' % id team = selectOne(query) query = (''' SELECT * FROM v_team_roster WHERE team_id=%s AND Year=Year(CURDATE())''' % id) players = selectAll(query) query = 'SELECT * FROM Team WHERE team_id=%i' %id c = selectOne(query) if c is not None: captain = c['captain'] return render_template('teamPage.html', team=team, players=players, captain=captain )
def addPlayer(): if 'team_id' not in session: return redirect(url_for('teamLogin')) team = session['team_id'] player = request.form['player_id'] query = ''' INSERT INTO Team_Roster (team_id, player_id, year) VALUES (%s, %s, Year(CURDATE())) ''' % (team, player) print(query) process(query) query = 'SELECT * FROM Player where player_id = %s' % player p = selectOne(query) player_name = p['player_name'] query = 'SELECT * FROM Team where team_id = %s' % team t = selectOne(query) team_name = t['team_name'] news = "%s has been added to the %s" %(player_name, team_name) writeNews(news) return redirect(url_for('teamRosterForm'))
def index(): if not session.get('name',None): return redirect('/login') fields = ['id','name','name_cn','mobile','email','role','status'] condition = 'name = "%(name)s"' % session print condition print session result = db.selectOne(fields,condition) print result user = dict((k,result[i]) for i,k in enumerate(fields)) return render_template('index.html', info = session, user = user)
def rosterGraph(): now = datetime.datetime.now() sql = '''SELECT DISTINCT year FROM Team_Roster ORDER BY year ASC ''' years_sql = selectAll(sql) years = [] for year in years_sql: years.append(year['year']) current_sql = selectOne(sql) current = current_sql['year'] return render_template('rosterGraph.html', current=current, years=years)
def playerPage(id): query = 'Select * from v_player where player_id = %s' % id player = selectOne(query) query = ''' SELECT * from v_player_stats WHERE player_id = %s ORDER BY YEAR ASC''' % id stats = selectAll(query) query = '''SELECT * FROM v_team_roster WHERE player_id = %s AND Year = Year(CURDATE()) ''' % id teamInfo = selectOne(query) query = '''SELECT * FROM Player WHERE player_id = %s''' % id personal = selectOne(query) if teamInfo: team = teamInfo['team_name'] else: team = None return render_template('playerPage.html', title='Player Page', player=player, team=team, personal=personal, stats=stats )
def teamMakeTradeSubmit(): print("submit") if request is None: print("None") return json.dumps(False) results = json.loads(request.data) home_id = results['home_id'] away_id = results['away_id'] home_players = results['home_players'] away_players = results['away_players'] now = datetime.datetime.now() date = now.strftime("%Y-%m-%d") create_trade = ''' INSERT INTO trade (team_id_1, team_id_2, date, status ) VALUES ('%s', '%s', now(), 'CREATING'); ''' % (home_id, away_id) process(create_trade) trade_query = '''SELECT * FROM trade WHERE team_id_1 = '%s' AND team_id_2 = '%s' AND status = 'CREATING' ''' % (home_id, away_id) print(date) trade_result = selectOne(trade_query) trade_id = trade_result['trade_id'] for player in home_players: trade_row = ''' INSERT INTO Trade_Player_List (player_id, trade_id, team_to) VALUES (%s, %s, %s) ''' %(player, trade_id,away_id ) process(trade_row) for player in away_players: trade_row = ''' INSERT INTO Trade_Player_List (player_id, trade_id, team_to) VALUES ('%s', '%s', '%s') ''' %(player, trade_id, home_id ) process(trade_row) query = '''SELECT * FROM tradeDetails_view''' results = selectAll(query) print(results) update_trade = '''UPDATE trade SET `status`='PENDING' WHERE `trade_id`=%s ''' % trade_id process(update_trade) return json.dumps(True)
def results_details(id): query = '''SELECT Home, Away, Year, Time_Remaining, Home_Score, Away_Score FROM v_game WHERE GameId = %s ''' % id details = selectOne(query) query = '''SELECT GoalScorer, Assist, Time FROM v_game_details WHERE Game = %s AND Team = '%s' ''' %(id, details['Home']) print(query) home = selectAll(query) query = '''SELECT GoalScorer, Assist, Time FROM v_game_details WHERE Game = %s AND Team = '%s' ''' %(id, details['Away']) away = selectAll(query) details = {'home':home, 'away':away} return json.dumps(details)
def teamJoin(): if request.form['user'] and request.form['pw']: user = request.form['user'] password = request.form['pw'] else: session['error'] = ' Missing element' return redirect(url_for('teamJoinForm')) query = 'INSERT INTO Team (team_name, password) VALUES(%s, %s)' result = processArgs(query, user, password) if result == 'FAILURE': session['error'] = ''' JOIN FAILED: CONTACT fras2560@mylaurier,ca check to see if you team name already exist''' return redirect(url_for('teamJoinForm')) query = ''' SELECT * from Team WHERE team_name like '%s' AND password = '******' ''' % (user, password) team = selectOne(query) session['teamLogin'] = True session['team_name'] = user session['team_id'] = team['team_id'] news = "%s is the new team to beat in the league" % user writeNews(news) return redirect(url_for('teamBio'))
def playerJoin(): if request.form['user'] and request.form['pw']: user = request.form['user'] password = request.form['pw'] else: session['error'] = ' Missing element' return redirect(url_for('playerJoinForm')) query = 'INSERT INTO Player (player_name, password) VALUES(%s, %s)' result = processArgs(query, user, password) if result == 'FAILURE': session['error'] = ''' JOIN FAILED: CONTACT fras2560@mylaurier,ca Check to see if your name is already entered''' return redirect(url_for('playerJoinForm')) query = ''' SELECT * from Player WHERE player_name like '%s' AND password = '******' ''' % (user, password) player = selectOne(query) session['playerLogin'] = True session['player_name'] = user session['player_id'] = player['player_id'] news = "%s has joined the league" % user writeNews(news) return redirect(url_for('playerBio'))
def login(): if request.method == 'POST': data = dict((k, v[0]) for k, v in dict(request.form).items()) if not data.get('name', None) or not data.get('password', None): return json.dumps({'code': 1, 'errmsg': 'name or password empty'}) fields = ['name', 'password', 'role'] condition = 'name = "%(name)s"' % data res = db.selectOne(fields, condition) if not res: return json.dumps({'code': 1, 'result': 'name not exist'}) user = dict((k, res[i]) for i, k in enumerate(fields)) data['password'] = hashlib.md5(data['password'] + salt).hexdigest() print data['password'] print user['password'] if data['password'] != user['password']: return json.dumps({'code': 1, 'result': 'password error'}) session['name'] = user['name'] session['role'] = user['role'] return json.dumps({'code': 0, 'result': 'login success'}) else: return render_template('login.html')
def worldmap(self, x = None, y = None, z = None): if x == None or y == None or z == None: return serve_file(voidImagePath, 'image/png') c = db.levelData.columns row = db.selectOne([c.userId, c.userName, c.userX, c.userY, c.userZ], c.userId == userId)
def get(cls,pk): """ Get by primary key. """ d = db.selectOne('select * from %s where %s = ?'%(cls.__table__, cls.__primaryKey.name),pk) return cls(**d) if d else None
def get(cls, pk): ''' Get by primary key. ''' d = db.selectOne('select * from %s where %s=?' % (cls.__table__, cls.__primary_key__.name), pk) return cls(**d) if d else None
def picture(id): query = "SELECT * FROM Photos WHERE photo_id = %s" % id player = selectOne(query) imageBlob = player['picture'] return imageBlob