def onice(self): oGid = request.args.get('gameid') exits = request.args.get('exits') onIcePlayers = {} # check if game id is real if not str(oGid).isdigit() or len(str(oGid)) != 10: return "Game ID is invalid" # parse TOI if need to cur = g.db.execute('SELECT COUNT(*) FROM shifts WHERE gameid = %s', [oGid]) results = cur.fetchone() if results[0] == 0: getTOI.getGameTOI(oGid) for e in exits.split(","): onIcePlayers[e] = [] eData = e.split(":") period = eData[0] tRemaining = eData[1] team = eData[2] location = 'h' if str(team) == '1' else 'v' # find the players for this period, time, team sql = "SELECT * FROM shifts WHERE gameid = %s AND shift_start >= %s AND shift_end < %s AND period = %s AND location = %s ORDER BY playerteamname, playernumber+0" params = [oGid, tRemaining, tRemaining, period, location] cur = g.db.execute(sql, params) pInExits = cur.fetchall() for player in pInExits: onIcePlayers[e].append( player[4] ) print "%s - %s" % (period, tRemaining) return json.dumps(onIcePlayers)
def roster(self, gameid): # not in game? parse in cur = g.db.execute('SELECT COUNT(*) FROM shifts WHERE gameid = %s', [gameid]) results = cur.fetchone() if results[0] == 0: getTOI.getGameTOI(gameid) cur = g.db.execute('SELECT playernumber, playername, location FROM shifts WHERE gameid = %s GROUP BY playername ORDER BY location, playernumber+0;', [gameid]) players = cur.fetchall() home = {} away = {} for data in players: indata = "".join(i for i in data[1] if ord(i) < 128) if data[2] == 'h': home[data[0]] = data[1] else: away[data[0]] = data[1] data = {} data['h'] = home data['v'] = away return json.dumps(data)
def gamereport(year,gameid,fetch=None): # check if there are any chances tracked, if not exit bigdata = [] # for the second/third tables gameSummaryHome = {} gameSummaryAway = {} # the fourt table data periodSummary periodSummary = [] tempPeriodSummary = [1]+[0]*12 try: cur = g.db.execute('SELECT team,period,time,comment FROM chances WHERE yearid=%s AND gameid=%s ORDER BY period, time DESC', [year,gameid]) mydata = [list(row) for row in cur.fetchall()] except Exception as e: # failed to load, quit app.logger.error('Unable to load data for game '+str(gameid)) return 'There is no saved data for this game. Did you not press save?'+str(e) # count chances if len(mydata) == 0: return 'There is no saved data for this game. Did you forget to press save?' oGameID = gameid gameid = int(str(year)[0:4] + "0" + str(gameid)[0:5]) if fetch == "true": g.db.execute('DELETE FROM shifts WHERE gameid = %s', [gameid]) return redirect(url_for('gamereport', year=year, gameid=oGameID)) # count shifts for both teams # if either is zero, delete it cur = g.db.execute('SELECT COUNT(*) as num, location FROM shifts WHERE gameid = %s GROUP BY location', [gameid]) data = cur.fetchall() if len(data) == 1: # so if we are missing data for a team, delete it all g.db.execute('DELETE FROM shifts WHERE gameid = %s', [gameid]) # check if there is any TOI data for this game. If not, parse it cur = g.db.execute('SELECT * FROM shifts WHERE gameid = %s', [gameid]) shifts = cur.fetchall() if len(shifts) == 0: getTOI.getGameTOI(gameid) for chance in mydata: timeOfChance = chance[2] m,s = divmod(timeOfChance, 60) chance[2] = "%d:%02d" % (m, s) bigdata.append(chance) sql = "SELECT playernumber FROM shifts WHERE gameid = %s AND shift_start >= %s AND shift_end < %s AND period = %s AND location = %s ORDER BY playerteamname, playernumber+0" # get all home players for this time data = [gameid, timeOfChance, timeOfChance, chance[1], 'h'] cur = g.db.execute(sql, data) homeNum = [x[0] for x in cur.fetchall()] # get all away players data[-1] = 'v' cur = g.db.execute(sql, data) awayNum = [x[0] for x in cur.fetchall()] home = len(homeNum) away = len(awayNum) # calculate the state state = str(home-1)+'v'+str(away-1) # figures out what type of chance this is for each of the players # based on state of the game and if home/away did the chance if home == away: if chance[0] == 0: cLocHome = 3 cLocAway = 4 else: cLocHome = 4 cLocAway = 3 elif home > away: if chance[0] == 0: cLocHome = 6 cLocAway = 10 else: cLocHome = 7 cLocAway = 9 elif home < away: if chance[0] == 0: cLocHome = 9 cLocAway = 7 else: cLocHome = 10 cLocAway = 6 # start adding the rows for the inividual players in home and away for num in homeNum: if num not in gameSummaryHome: gameSummaryHome[num] = [num]+[0]*10 gameSummaryHome[num][cLocHome] += 1 for num in awayNum: if num not in gameSummaryAway: gameSummaryAway[num] = [num]+[0]*10 gameSummaryAway[num][cLocAway] += 1 # adds data to the final-2 tables, sums of the totals # we know the state, we know the period, we can add to the variable if chance[1] != tempPeriodSummary[0]: # new period, reset tempPeriodSummary and append old tempPeriodSummary[1] = sum(tempPeriodSummary[3::2]) tempPeriodSummary[2] = sum(tempPeriodSummary[4::2]) # sum up totals periodSummary.append(tempPeriodSummary) tempPeriodSummary = [chance[1]] + [0]*12 # figure out which column to add to if state in ["5v5", "4v4", "3v3"]: periodPos = 3 if chance[0] == 0 else 4 elif state == "5v4": periodPos = 5 if chance[0] == 0 else 6 elif state == "5v4": periodPos = 7 if chance[0] == 0 else 8 elif state == "4v5": periodPos = 9 if chance[0] == 0 else 10 else: # it must be 3v5 periodPos = 11 if chance[0] == 0 else 12 # add to tempPeriodSummary tempPeriodSummary[periodPos] += 1 # pad out the team numbers homeNum += [' ']*(6-home) awayNum += [' ']*(6-away) if state != '-1v-1': chance += homeNum chance += awayNum chance.append(state) else: chance += "Invalid Data " # make the team readable chance[0] = "Home" if chance[0] == 0 else "Away" # display big data table getPlayerInfo = scrape.getGamePlayerStats(year, gameSummaryHome, gameSummaryAway, str(gameid)[-5:]) gameSummaryHome = [getPlayerInfo[0][x] for x in getPlayerInfo[0]] gameSummaryHome.sort(key = lambda row : int(row[0])) gameSummaryAway = [getPlayerInfo[1][x] for x in getPlayerInfo[1]] gameSummaryAway.sort(key = lambda row: int(row[0])) # update periodSummary tempPeriodSummary[1] = sum(tempPeriodSummary[3::2]) tempPeriodSummary[2] = sum(tempPeriodSummary[4::2]) periodSummary.append(tempPeriodSummary) # awayPeriodSummary awayPeriodSummary = [x[:] for x in periodSummary] for row in awayPeriodSummary: row[1],row[2] = row[2],row[1] row[3],row[4] = row[4],row[3] row[5],row[10] = row[10],row[5] row[6],row[9] = row[9],row[6] row[7],row[12] = row[12],row[7] row[8],row[11] = row[11],row[8] # swap 3<->4,5<->10,6<->9,7<->12,8<->11,1<->2 return render_template('gamereport.html', data=bigdata, homeSummary=gameSummaryHome, awaySummary=gameSummaryAway, periodSummary=periodSummary, awayPeriodSum=awayPeriodSummary)
def post(self): urlgid = None urlper = None team1 = None team1roster = [] team2 = None team2roster = [] message = None gameidForm = "" timeidForm = "20:00" gyear = request.form['gyear'] gid = request.form['gameid'] gameid = str(gyear) + '0' + str(gid) period = request.form['period'] time = request.form['time'] mins = time[:2] secs = time[3:5] # check if gid is in db cur = g.db.execute('SELECT * FROM shifts WHERE gameid=%s', [gameid]) if not gyear.isdigit() or int(gyear) not in range(2007, 2014): message = "Game year is not valid." elif not gid.isdigit() or len(gid) != 5: message = "Game ID is not valid." elif period not in ['1','2','3', '4']: message = "Period is not valid." elif not re.match(r'^\d\d:\d\d$', time): message = "Time is not in valid format" elif int(mins)*60 + int(secs) > 1200: message = "Time is too high." else: if cur.fetchone() is None: #message = "This game id does not exist in our database." getTOI.getGameTOI(gameid) # fails to grab anything? Handle this # SELECT * FROM pbp WHERE cast(timedown as integer) >= 1166 AND gid=30151 AND period = 1 ORDER BY gnumber DESC LIMIT 1; # SELECT * FROM shifts WHERE gameid = 2012020123 AND shift_start >= 1000 AND shift_end < 1000 AND period = 2 ORDER BY playerteamname, playernumber+0; sql = "SELECT * FROM shifts WHERE gameid = %s AND shift_start >= %s AND shift_end < %s AND period = %s ORDER BY playerteamname, playernumber+0" queryGameID = int(mins)*60 + int(secs) params = [int(gameid), queryGameID, queryGameID, int(period)] cur = g.db.execute(sql, params) fetchd = cur.fetchall() if fetchd != []: # turn fetchd into a list, do stuff easier here team1 = fetchd[0][5] team2 = "" for player in fetchd: if player[5] == team1: team1roster.append(player[3]) else: team2roster.append(player[3]) team2 = player[5].title() # make fancy lists team1 = team1.title() team1roster = ' <br />'.join(team1roster) team2roster = ' <br />'.join(team2roster) else: message = "Nothing found for these values" gameidForm = str(gid) timeidForm = time return render_template('toi.html', team1=team1, team2=team2, team1roster=team1roster, team2roster=team2roster, error = message, gameid=gameidForm, timerem=timeidForm)