def cabinet():
if not session.get('name', None):
redirect('/login')
fields = ['id', 'name', 'idc_id', 'u_num', 'power']
idc_fields = ['id', 'name']
table = 'cabinet'
try:
result = db.selectAll(fields, table)
# print result
data = [
dict((k, row[i]) for i, k in enumerate(fields)) for row in result
]
print data
idcs = db.selectAll(idc_fields, 'idc')
# pdb.set_trace()
idcs = dict(idcs)
print idcs
#((7L, u'hp'), (1L, u'syq'), (8L, u'zhaowei'))
for cabinet in data:
if cabinet['idc_id'] in idcs:
cabinet['idc_id'] = idcs[cabinet['idc_id']]
print data
return render_template('cabinet/cabinet.html',
cabinets=data,
info=session)
except:
print traceback.print_exc()
return json.dumps({'code': 1, 'result': 'select cabinet failed'})
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 champs():
query = '''SELECT * FROM v_championship'''
championship = selectAll(query)
query = '''SELECT * FROM v_winning_roster'''
players = selectAll(query)
return render_template('champs.html',
players=players,
championship=championship
)
def teamMakeTrade():
if 'team_id' not in session:
return redirect(url_for('teamLogin'))
team_id = session['team_id']
sql = '''SELECT * FROM v_team_roster
WHERE team_id = %s AND Year = Year(CURDATE());
''' % team_id
home_team = selectAll(sql)
sql = '''SELECT * FROM Team WHERE team_id != %s''' % team_id
teams = selectAll(sql)
return render_template('make_trade.html',
home_team = home_team,
teams = teams,
home_id = team_id)
def teamRosterForm():
if 'team_id' not in session:
return redirect(url_for('teamLogin'))
id = session['team_id']
query = '''SELECT * FROM v_team_roster WHERE team_id = %s AND
Year = Year(Curdate())''' % id
roster = selectAll(query)
query = '''Select * from Player AS p WHERE p.player_id NOT IN
(SELECT player_id from Team_Roster where year=YEAR(CURDATE()))
ORDER BY p.player_name'''
players = selectAll(query)
return render_template('teamRosterForm.html',
roster=roster,
players=players)
def updatacab():
if session.get('name',None):
redirect('/login')
if request.method == 'POST':
data = dict(request.form)
fields = ['name','idc_id','u_num','power']
table = 'cabinet'
condition = [ "%s='%s'" % (k,v[0]) for k,v in data.items() ]
try:
db.update(condition, data['id'][0], table)
return json.dumps({'code':0, 'result': 'add cabinet success'})
except:
print traceback.print_exc()
return json.dumps({'code':1,'errmsg':'update cabinet error'})
else:
id = request.args.get('id')
print id
fields = ['id','name','idc_id','u_num','power']
table = 'cabinet'
try:
result = db.selectId(fields,id,table)
cabinet = dict((k,result[i]) for i,k in enumerate(fields))
table_idc = 'idc'
fields_idc = ['id','name']
result = db.selectAll(fields_idc,table_idc)
print result
idcs = [ dict((k,row[i]) for i,k in enumerate(fields_idc)) for row in result ]
return render_template('cabinet/updatecabinet.html',info = session, cabinet = cabinet, idcs = idcs)
except:
print traceback.print_exc()
return json.dumps({'code':1, 'result':'select cabinetinfo failed'})
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 teams():
query = 'Select * from v_team_wins ORDER BY team_name ASC'
teams = selectAll(query)
return render_template('teams.html',
title='Teams',
teams=teams
)
def addcabinet():
if session.get('name', None):
redirect('/login')
if request.method == 'POST':
data = dict((k, v[0]) for k, v in dict(request.form).items())
fields = ['name', 'idc_id', 'u_num', 'power']
table = 'cabinet'
try:
db.insert(fields, data, table)
return json.dumps({'code': 0, 'result': 'add cabinet success'})
except:
print traceback.print_exc()
return json.dumps({'code': 1, 'errmsg': 'add cabinet error'})
else:
table = 'idc'
fields = ['id', 'name']
result = db.selectAll(fields, table)
print result
data = [
dict((k, row[i]) for i, k in enumerate(fields)) for row in result
]
print data
return render_template('cabinet/addcabinet.html',
info=session,
idcs=data)
def teamConfirmTrade():
if request is None:
print("None")
return json.dumps(False)
try:
results = json.loads(request.data)
trade_id = results['trade_id']
proposing_sql = '''SELECT * FROM tradeDetails_view WHERE trade_id = %s;
''' % trade_id
results = selectAll(proposing_sql)
news_dictionary = {}
for player in results:
team = player['team_name']
if (team in news_dictionary ):
news_dictionary [team].append(player['player_name'])
else:
news_dictionary [team] = [player['player_name']]
delete_sql = '''DELETE FROM Team_Roster WHERE player_id = %s
AND year = Year(CURDATE());
''' % player['player_id']
insert_sql = '''INSERT INTO Team_Roster (team_id, player_id, year)
VALUES (%s, %s, Year(CURDATE()));
''' % (player['team_to'], player['player_id'])
process(delete_sql)
process(insert_sql)
update_trade = '''UPDATE trade
SET status='MADE'
WHERE trade_id = %s
''' % trade_id
process(update_trade)
writeTradeNews(news_dictionary)
return json.dumps(True)
except:
return json.dumps(False)
def idc():
if not session.get('name', None):
return redirect('/login')
fields = ['id', 'name', 'name_cn', 'address', 'adminer', 'phone']
table = 'idc'
result = db.selectAll(fields, table)
data = [dict((k, row[i]) for i, k in enumerate(fields)) for row in result]
return render_template('idc/idc.html', idcs=data, info=session)
def news():
user = {"nickname": "Dallas"}
events = selectAll('SELECT * FROM News ORDER BY news_id DESC')
return render_template('news.html',
title='Home',
user=user,
events=events
)
def userlist():
if not session.get('name',None):
return redirect('/login')
fields = ['id','name','name_cn','mobile','email','role','status']
table = 'users'
result = db.selectAll(fields,table)
data = [dict((k,row[i]) for i,k in enumerate(fields)) for row in result]
return render_template('user/userlist.html',users=data,info=session)
def players():
user = {"nickname": "Brother"}
players = selectAll('Select * from v_player')
return render_template('player.html',
title='Home',
user=user,
players=players
)
def teamGetRosterTrade(id):
sql = '''SELECT * FROM v_team_roster
WHERE team_id = %s AND Year = Year(CURDATE());
''' % id
team = selectAll(sql)
team_list = []
for player in team:
team_list.append([player['player_id'],player['player_name']])
return json.dumps(team_list)
def index():
user = {"nickname": "Dallas"}
events = selectAll('SELECT * FROM News ORDER BY news_id DESC Limit 10')
query = '''SELECT * FROM Photos WHERE
photo_id > 3 AND photo_id != 14 LIMIT 10'''
pictures = selectAll(query)
captions = ['Because its the cup', 'History will be made',
'Question will be answered', 'Sanway Cup',
'Where dreams come true', 'Play hard and Party Hard']
for picture in pictures:
if(len(captions) != 0):
picture['caption'] = captions.pop()
return render_template('index.html',
title='Home',
user=user,
events=events,
pictures=pictures,
captions=captions
)
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 stats():
temp = date.today()
now = temp.year
query = '''SELECT YEAR, Name, Goals, Points, Assists, Team from v_player_stats
WHERE YEAR = %s''' % now
current = selectAll(query)
query = '''SELECT Distinct YEAR FROM v_player_stats
WHERE YEAR != Year(CURDATE())'''
years = selectAll(query)
player_list = []
for year in years:
query = '''SELECT YEAR, Name, Goals, Points, Assists, Team from v_player_stats
WHERE YEAR = %s''' % year['YEAR']
players = selectAll(query)
player_list.append(players)
return render_template('stats.html',
player_list=player_list,
years=years,
now=now,
current=current)
def results():
query = '''SELECT gameId, Home, HomeId, AwayId,
Away, Home_Score, Away_Score
FROM v_game
WHERE Year = 2013
ORDER BY gameId ASC
'''
games = selectAll (query)
print(games)
return render_template('results.html',
games=games)
def teamCheckTrade():
if 'team_id' not in session:
return json.dumps(False)
team_id = session['team_id']
check = '''SELECT * FROM trade
WHERE team_id_2 = %s
AND status = '%s' ''' %(team_id, 'PENDING')
results = selectAll(check)
if len(results) > 0:
return json.dumps(True)
else:
return json.dumps(False)
def roster_tree(year):
tree = {"name": ""}
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 = %s''' % (team['team_id'], year)
players = selectAll(query)
player_list = []
for player in players:
player_dictionary = {"name":player['player_name'],
"size":randomNum(20, 40)}
player_list.append(player_dictionary)
if player_list == []:
team_dictionary['size'] = 10
else:
team_dictionary['children'] = player_list
team_list.append(team_dictionary)
tree['children'] = team_list
return json.dumps(tree)
def server():
if not session.get('name', None):
redirect('/login')
fields = [
'id', 'hostname', 'lan_ip', 'wan_ip', 'cabinet_id', 'op', 'phone'
]
table = 'server'
result = db.selectAll(fields, table)
print result
data = [dict((k, row[i]) for i, k in enumerate(fields)) for row in result]
cabinet_fields = ['id', 'name']
cabinets = db.selectAll(cabinet_fields, 'cabinet')
cabinets = dict(cabinets)
print cabinets
for server in data:
print server
print server['cabinet_id']
if server['cabinet_id'] in cabinets:
print 'True'
server['cabinet_id'] = cabinets[server['cabinet_id']]
print data
return render_template('server/server.html', info=session, servers=data)
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 leaders():
query = ''' SELECT DISTINCT YEAR FROM v_player_stats'''
years = selectAll(query)
list = []
query = '''SELECT * FROM v_player_stats WHERE Year = %s
ORDER BY Goals DESC LIMIT 5''' % 2013
goal_leaders = selectAll(query)
query = '''SELECT * FROM v_player_stats WHERE Year = %s
ORDER BY Assists DESC LIMIT 5''' % 2013
assist_leaders = selectAll(query)
query = '''SELECT * FROM v_player_stats WHERE Year = %s
ORDER BY Points DESC LIMIT 5''' % 2013
points_leaders = selectAll(query)
query = '''SELECT * FROM v_player_stats WHERE Year = %s
ORDER BY Points_per_game DESC LIMIT 5''' % 2013
ppg = selectAll(query)
return render_template('leader.html',
goal_leaders=goal_leaders,
years=years,
points_leaders=points_leaders,
assist_leaders=assist_leaders,
ppg=ppg
)
def cabinet():
if not session.get('name', None):
redirect('/login')
fields = ['id', 'name', 'idc_id', 'u_num', 'os', 'power']
table = 'cabinet'
try:
result = db.selectAll(fields, table)
print result
data = [
dict((k, row[i]) for i, k in enumerate(fields)) for row in result
]
print data
return render_template('cabinet.html', cabinets=data, info=session)
except:
print traceback.print_exc()
return json.dumps({'code': 1, 'result': 'select cabinet failed'})
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 cleanDB():
logger.debug("Purging missing files from database")
files = [ os.path.abspath(args.directory + '/' + f) for f in os.listdir(args.directory)]
deleteFiles = []
entries = db.selectAll()
while len(entries) > 0:
for nextEntry in entries:
if not nextEntry[1] in files:
logger.debug("Marking file {} for removal from database".format(nextEntry[1]))
deleteFiles.append(nextEntry[0])
entries = db.fetchNext()
db.deleteSet(deleteFiles)
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 updateser():
if request.method == 'POST':
return True
else:
table = 'cabinet'
fields = ['id', 'name']
result = db.selectAll(fields, table)
print result
# ((6L, u'002'), (5L, u'005'), (8L, u'008'), (13L, u'100'), (14L, u'111'))
print dict(result)
# {8L: u'008', 14L: u'111', 5L: u'005', 6L: u'002', 13L: u'100'}
data = [
dict((k, row[i]) for i, k in enumerate(fields)) for row in result
]
print data
# [{'id': 6L, 'name': u'002'}, {'id': 5L, 'name': u'005'}, {'id': 8L, 'name': u'008'}, {'id': 13L, 'name': u'100'}, {'id': 14L, 'name': u'111'}]
# server =
return render_template('server/updateserver.html',
info=session,
cabinets=data)
def TeamPortal():
if 'teamLogin' in session:
return redirect(url_for('teamPage'))
else:
user_name = request.form['username']
password = request.form['password']
query = ''' SELECT * from Team WHERE team_name like
'%s' AND password = '******'
''' % (user_name, password)
teams = selectAll(query)
if (len(teams) != 1):
session['error'] = 'INVALID COMBO'
return redirect(url_for('teamLogin'))
elif (teams is None):
session['error'] = 'INVALID COMBO'
return redirect(url_for('teamLogin'))
else:
session['teamLogin'] = True
session['team_name'] = user_name
session['team_id'] = teams[0]['team_id']
return redirect(url_for('teamBio'))
def playerPortal():
if 'playerLogin' in session:
return redirect(url_for('playerBio'))
else:
user_name = request.form['username']
password = request.form['password']
query = ''' SELECT * from Player WHERE player_name like
'%s' AND password = '******'
''' % (user_name, password)
players = selectAll(query)
if (len(players) != 1):
session['error'] = 'INVALID COMBO'
return redirect(url_for('playerLogin'))
elif (players is None):
session['error'] = 'INVALID COMBO'
return redirect(url_for('playerLogin'))
else:
session['playerLogin'] = True
session['player_name'] = user_name
session['player_id'] = players[0]['player_id']
return redirect(url_for('playerBio'))
def teamViewTrade():
if 'team_id' not in session:
return redirect(url_for('teamLogin'))
team_id = session['team_id']
their_trades_sql = ''' SELECT * FROM view_trade
WHERE ProposingTeamId= %s ''' % team_id
their_trades = selectAll(their_trades_sql)
Owntrades = []
for trade in their_trades:
dictionary = {"teamTo":trade['ReceivingTeam'],
'status':trade['status']}
trade_id = trade['Trade']
player_sql = '''SELECT * FROM tradeDetails_view
WHERE trade_id =%s AND team_to!=%s
''' %(trade_id, team_id)
dictionary['PlayersGivingUp'] = selectAll(player_sql)
player_sql = '''SELECT * FROM tradeDetails_view
WHERE trade_id =%s AND team_to=%s
''' %(trade_id, team_id)
dictionary['PlayersReceiving'] = selectAll(player_sql)
Owntrades.append(dictionary)
their_trades_sql = ''' SELECT * FROM view_trade
WHERE ReceivingTeamId= %s AND status='PENDING' ''' % team_id
their_trades = selectAll(their_trades_sql)
Offertrades = []
for trade in their_trades:
dictionary = {"teamTo":trade['ProposingTeam'],
'status':trade['status'],
"trade_id":trade['Trade']}
trade_id = trade['Trade']
player_sql = '''SELECT * FROM tradeDetails_view
WHERE trade_id =%s AND team_to!=%s
''' %(trade_id, team_id)
dictionary['PlayersGivingUp'] = selectAll(player_sql)
player_sql = '''SELECT * FROM tradeDetails_view
WHERE trade_id =%s AND team_to=%s
''' %(trade_id, team_id)
dictionary['PlayersReceiving'] = selectAll(player_sql)
Offertrades.append(dictionary)
if len(Offertrades)==0:
Offertrades = None
if len(Owntrades)==0:
Owntrades = None
return render_template('view_trade.html',
Offertrades=Offertrades,
Owntrades=Owntrades)
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 addserver():
if not session.get('name', None):
redirect('/login')
if request.method == 'POST':
table = 'server'
data = dict((k, v[0]) for k, v in dict(request.form).items())
fields = ['hostname', 'lan_ip', 'wan_ip', 'cabinet_id', 'op', 'phone']
try:
db.insert(fields, data, table)
return json.dumps({'code': 0, 'result': 'add server success'})
except:
print traceback.print_exc()
return json.dumps({'code': 1, 'errmsg': 'add server error'})
else:
table = 'cabinet'
fields = ['id', 'name']
result = db.selectAll(fields, table)
data = [
dict((k, row[i]) for i, k in enumerate(fields)) for row in result
]
print data
return render_template('server/addserver.html',
info=session,
cabinets=data)
