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)
