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)