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
