wb4m2.py~

#!/usr/bin/python
import urllib2
import xml.etree.ElementTree as ET
import datetime
from bs4 import BeautifulSoup
import re
import csv
import time
from datetime import date
from datetime import timedelta
from datetime import datetime
import pytz
import gspread 
import MySQLdb
import MySQLdb.cursors
import string
import collections
import copy
import json
import numpy
import pdb

db= MySQLdb.connect(user='root' , passwd='andnotbut', db='wb3m4')
cursor = db.cursor()

dbd = MySQLdb.connect(user='root' , passwd='andnotbut', db='wb3m4',cursorclass=MySQLdb.cursors.DictCursor)
cursord = dbd.cursor()

def hist(s):
   d = {}
   for i in s.upper():
      if i.isalpha():
         d[i] = d[i] + 1 if i in d else 1
         for k in sorted(d.keys()):
            print k*d[k]

def getGamesForPlayer(player_id):
   n = cursord.execute('select * from info where player=%s order by date,id'%(str(player_id)))
   if n > 0:
      res = cursord.fetchall()
      return res
   else:
      return None

def printGameToCSV(file,g,first):
   fnames = ['team', 'first_name','last_name','player','date','gamekey','status','pitcherp','W','SV','ER','QS','DP','HR','SB','H','ASS','BB']   
   w = csv.DictWriter(file,fnames,extrasaction='ignore')
   if first:
      w.writer.writerow(fnames)
   w.writerow(g)

def addUpdate(d,key,val):
   if key in d:
      d[key] = d[key] + val
   else:
      d[key] = val
   return d

def getLastNameCount(t):
   players = t['batters'] + t['pitchers']
   lastlets = ''.join([x['name'][0][0] for x in players])
   return collections.Counter(lastlets).most_common()[0][1]

def getLongestStreak(x,onlyLatest=False):
   stk_n = 0
   in_stk = 0
   max_stk = 0
   for xx in x:
      if xx:
         if in_stk:
            stk_n = stk_n+1
         else:
            stk_n = 1
            in_stk = 1
      else:
         in_stk = 0
         stk_n = 0
      if stk_n > max_stk:
         max_stk = stk_n
   if (onlyLatest):
      return stk_n
   else:
      return max_stk

def teamScore(t,onlyLatest=False):
   team_w = {}
   team_sv = {}
   team_hr = {}
   team_sb = {}

   team_h = 0
   team_bb = 0
   team_ass = 0
   team_qs = 0
   team_dp = 0
   team_er = 0
   tscore = {}
   tscore['team_name'] = t['team_name']

   team_name_streak = getLastNameCount(t)

   for b in t['batters']:
      gs = getGamesForPlayer(b['id'])
      if gs:
         hs = [int(x['H']>0) for x in gs]
         bbs = [int(x['BB']>0) for x in gs]
         asss = [int(x['ASS']>0) for x in gs]
         hrs =  [int(x['HR']>0) for x in gs]
         sbss =  [int(x['SB']>0) for x in gs]
         dates =  [x['date'] for x in gs]
         team_h = team_h + getLongestStreak(hs,onlyLatest)
         team_bb = team_bb + getLongestStreak(bbs,onlyLatest)
         team_ass = team_ass + getLongestStreak(asss,onlyLatest)
         for d,hhh in zip(dates,hrs):
            team_hr = addUpdate(team_hr,d,hhh)
         for d,sss in zip(dates,sbss):
            team_sb = addUpdate(team_sb,d,sss)
   shr = [x for x in team_hr.iteritems()]
   shr.sort(key=lambda x: x[0])
   team_hr_streak = getLongestStreak([x[1] for x in shr],onlyLatest)
   ssb = [x for x in team_sb.iteritems()]
   ssb.sort(key=lambda x: x[0])
   team_sb_streak = getLongestStreak([x[1] for x in ssb],onlyLatest)
   tscore['H']=team_h
   tscore['ASS']=team_ass
   tscore['BB']=team_bb
   tscore['HR'] = team_hr_streak
   tscore['SB'] = team_sb_streak

   for b in t['pitchers']:
      gs = getGamesForPlayer(b['id'])
      if gs:
         qss = [int(x['QS']>0) for x in gs]
         dps = [int(x['DP']>0) for x in gs]
         ers = [int(x['ER']<1) for x in gs]
         ws =  [int(x['W']>0) for x in gs]
         svs =  [int(x['SV']>0) for x in gs]
         dates =  [x['date'] for x in gs]
         team_qs = team_qs + getLongestStreak(qss,onlyLatest)
         team_dp = team_dp + getLongestStreak(dps,onlyLatest)
         team_er = team_er + getLongestStreak(ers,onlyLatest)
         for d,www in zip(dates,ws):
            team_w = addUpdate(team_w,d,www)
         for d,sss in zip(dates,svs):
            team_sv = addUpdate(team_sv,d,sss)
   sw = [x for x in team_w.iteritems()]
   sw.sort(key=lambda x: x[0])
   team_w_streak = getLongestStreak([x[1] for x in sw],onlyLatest)
   ssv = [x for x in team_sv.iteritems()]
   ssv.sort(key=lambda x: x[0])
   team_sv_streak = getLongestStreak([x[1] for x in ssv],onlyLatest)
   tscore['QS'] = team_qs
   tscore['DP'] = team_dp
   tscore['ER'] = team_er
   tscore['W'] = team_w_streak
   tscore['SV'] = team_sv_streak
   tscore['team_name_streak'] = team_name_streak
   
   return tscore

def printTeamToCSV(t,file,first):
   tname = t['team_name']
   for b in t['batters']:
      gs = getGamesForPlayer(b['id'])
      if gs:
         for g in gs:
            g['team'] = t['team_name']
            g['first_name'] = b['name'][1]
            g['last_name'] = b['name'][0]
            printGameToCSV(file, g,first)
            first = 0
   for b in t['pitchers']:
      gs = getGamesForPlayer(b['id'])
      if gs:
         for g in gs:
            g['team'] = t['team_name']
            g['first_name'] = b['name'][1]
            g['last_name'] = b['name'][0]
            printGameToCSV(file, g,first)
            first = 0

#Use this to print out team CSV   
def printTeamsCSV(file):
   f = open(file,'wb') 
   teams = getTeams()
   first = 1
   for t in teams:
      printTeamToCSV(t,f,first)
      first = 0
   f.close()

def printTeamScores():
   teams = getTeams()
   for t in teams:
      ts = teamScore(t)
      print ts
      
def getTeamScores(onlyLatest=False):
   teams = getTeams()
   tscores = []
   for t in teams:
      dts = teamScore(t,onlyLatest)
      dts['team_name'] = t['team_name']
      tscores.append(dts)
   return tscores


def scoreVector(v):
   res = [0 for x in v]
   i=0
   for x in v:
      res[i] = 0.5+ sum( [float(x > y) for y in v]) + 0.5*sum([float(x==y) for y in v])
      i = i+1
   return res

def scoreSingle(v,i):
   x = v[i]
   res = 0.5+ sum( [float(x > y) for y in v]) + 0.5*sum([float(x==y) for y in v])
   return res

def getVictoryPoints(ts):
   #return a list of dictonaries with the season-point scores
   fnames_for_scoring = ['team','times','temps','hrs','triples','attendance','lob','total']
   tsc = copy.deepcopy(ts)
   vsc = []
   for tsii in tsc:
      tsii['total'] = 0
   for lab  in fnames_for_scoring[1:len(fnames_for_scoring)-1]:
      nteams = len(tsc)
      for tt in range(0,nteams):
         dascore = scoreSingle([x[lab] for x in ts],tt)
         tsc[tt][lab] = dascore
         tsc[tt]['total'] = tsc[tt]['total'] + dascore
   return tsc

def getID(player):
   #player is a list with [lastname,firstname]
   #print player
   #print "in getID, player is " + player[1] +','+player[0]
   db = MySQLdb.connect(user='root',passwd='andnotbut',db='gameday')
   cursor = db.cursor()
   rt = cursor.execute("select id from player where first_name='"+player[1]+"' and last_name='"+player[0]+"'")
   res = cursor.fetchone()
   try:
      return(int(res[0]))
   except:
      print "error -- can't find player " + str(player)

def makeTeamDict(tname, stadium1, stadium2, stadium3,m1):
   dd = {}
   dd['team_name'] = tname
   dd['stadiums'] = [stadium1,stadium2,stadium3]
   dd['ngames'] = 0
   dd['temps'] = 0
   dd['times'] = 0
   dd['hrs'] = 0
   dd['triples'] = 0
   dd['lob'] = 0
   dd['attendance'] = 0
   dd['m1'] = m1
   dd['mtotal'] = m1
   return dd


def getFilledTeams(date1,date2):
   ts = getTeams()
   ress = CompileRangeGames(date1,date2)
   for t in ts:
      #hr,trips,temp(av) high, 
      #lob,attendance,time(av) low
      mymean=lambda n,label:numpy.mean([x[label] for x in ress if x['venue_id'] == t['stadiums'][n]])
      t['temps'] = numpy.around(numpy.nanmean( [mymean(x,'temp') for x in range(0,3)]),2)
      t['times'] = -numpy.around(numpy.nanmean( [mymean(x,'time') for x in range(0,3)]),2)
      t['ngames'] = sum([x['ngames'] for x in ress if x['venue_id'] in t['stadiums']])
      t['hrs'] = sum([x['hrs'] for x in ress if x['venue_id'] in t['stadiums']])
      t['triples'] = sum([x['trips'] for x in ress if x['venue_id'] in t['stadiums']])
      t['lob'] = -sum([x['lob'] for x in ress if x['venue_id'] in t['stadiums']])
      t['attendance'] = -sum([x['attendance'] for x in ress if x['venue_id'] in t['stadiums']])
      t['ngames'] = sum([x['ngames'] for x in ress if x['venue_id'] in t['stadiums']])
   return ts,ress

           
def getTeams():
   ts = [];
   team_names = ['Detroit Noojies (aka dbags)','No-Talent Ass Clowns', 'Portlandia Misfits', 'The Rube', 'Paly Players', 'Dr. Watson', 'Buena Vista Bottoms', 'Damnedest of the Nice']
   ts.append(makeTeamDict(team_names[0],'13','2392','2889',4))
   ts.append(makeTeamDict(team_names[1],'4169','3312','5',5.5))
   ts.append(makeTeamDict(team_names[2],'680','15','2680',5.5))
   ts.append(makeTeamDict(team_names[3],'14','10','17',3))
   ts.append(makeTeamDict(team_names[4],'16','2602','3313',1))
   ts.append(makeTeamDict(team_names[5],'2','7','32',7))
   ts.append(makeTeamDict(team_names[6],'4','19','2394',2))
   ts.append(makeTeamDict(team_names[7],'12','2681','3309',8))
   return ts

def getTeamsOLD():
    team_names = ['Pierzinskis Misspelled D-Bags','No-Talent Ass Clowns', 'Portlandia Misfits', 'The Rube', 'Paly Players', 'Dr. Watson', 'Buena Vista Bridesmaids', 'Damnedest of the Nice']

    pitchers = [];
    pitchers.append([['Kershaw','Clayton'], ['Rosenthal','Trevor'], ['Melancon','Mark'], ['Mejia','Jenrry'], ['Masterson','Justin'], ['Motte','Jason']] )
    pitchers.append([['Bumgarner','Madison'], ['Strasburg','Stephen'], ['Papelbon','Jonathan'], ['Porcello','Rick'], ['Perkins','Glen'], ['Lackey','John']] )
    pitchers.append([['Rodriguez','Francisco'], ['Gray','Sonny'], ['Buehrle','Mark'], ['Keuchel','Dallas'], ['Zimmermann','Jordan'], ['Bailey','Homer']] )
    pitchers.append([['Cueto','Johnny'], ['Scherzer','Max'], ['Hudson','Tim'], ['Chapman','Aroldis'], ['Cole','Gerrit'], ['Cain','Matt']] )
    pitchers.append([['Price','David'], ['Darvish','Yu'], ['Holland','Greg'], ['Lincecum','Tim'], ['Lee','Cliff'], ['Verlander','Justin']] )
    pitchers.append([['Wainwright','Adam'], ['Weaver','Jered'], ['Jansen','Kenley'], ['Wilson','C.J.'], ['Wheeler','Zack'], ['Wood','Travis']] )
    pitchers.append([['Greinke','Zack'], ['Kimbrel','Craig'], ['Garza','Matt'], ['Gonzalez','Gio'], ['Gallardo','Yovani'], ['Gonzalez','Miguel']] )
    pitchers.append([['Hernandez','Felix'], ['Tanaka','Masahiro'], ['Sale','Chris'], ['Simon','Alfredo'], ['Street','Huston'], ['Sanchez','Anibal']])

    batters = [];
    batters.append([['McCutchen','Andrew'], ['Dozier','Brian'], ['Braun','Ryan'], ['Kendrick','Howie'], ['Marte','Starling'], ['Longoria','Evan'], ['Andrus','Elvis'], ['Montero','Miguel'], ['Votto','Joey']])
    batters.append([['Hamilton','Billy'], ['Pence','Hunter'], ['Murphy','Daniel'], ['Alvarez','Pedro'], ['Posey','Buster'], ['Perez','Salvador'], ['Peralta','Jhonny'], ['Phillips','Brandon'], ['Prado','Martin']])
    batters.append([['Encarnacion','Edwin'], ['Tulowitzki','Troy'], ['Puig','Yasiel'], ['Ellsbury','Jacoby'], ['Beltre','Adrian'], ['Escobar','Alcides'], ['Bruce','Jay'], ['Molina','Yadier'], ['Bonifacio','Emilio']])
    batters.append([['Cabrera','Miguel'], ['Cano','Robinson'], ['Cabrera','Melky'], ['Cespedes','Yoenis'], ['Carpenter','Matt'], ['Cabrera','Asdrubal'], ['Choo','Shin-Soo'], ['Rosario','Wilin'], ['Harper','Bryce']])
    batters.append([['Jones','Adam'], ['Donaldson','Josh'], ['Ramirez','Hanley'], ['Rios','Alex'], ['Kemp','Matt'], ['Davis','Chris'], ['Segura','Jean'], ['Kipnis','Jason'], ['Mauer','Joe']])
    batters.append([['Trout','Mike'], ['Kinsler','Ian'], ['Bautista','Jose'], ['Gonzalez','Adrian'], ['Werth','Jayson'], ['Wright','David'], ['Walker','Neil'], ['Crawford','Brandon'], ['McCann','Brian']])
    batters.append([['Goldschmidt','Paul'], ['Rendon','Anthony'], ['Pujols','Albert'], ['Gardner','Brett'], ['Lucroy','Jonathan'], ['Pedroia','Dustin'], ['Calhoun','Kole'], ['Zobrist','Ben'], ['Cabrera','Everth']])
    batters.append([['Cruz','Nelson'], ['Stanton','Giancarlo'], ['Altuve','Jose'], ['Gordon','Dee'], ['Abreu','Jose'], ['Frazier','Todd'], ['Seager','Kyle'], ['Springer','George'], ['Santana','Carlos']]) 

    teams = []
    i=0
    for team_name in team_names:
       fbs = [];
       fps = [];
       for b in batters[i]:
          fbs.append({'name':b,'id':getID(b)})
       for p in pitchers[i]:
          fps.append({'name':p,'id':getID(p)})
       teams.append({'team_name':team_name, 'batters':fbs, 'pitchers':fps})
       i=i+1
    return teams

                                  
def SetFieldNames():
   """returns the list of fieldnames for the dictwriter"""
   return ['gameid', 'away_team', 'home_team', 'inningnum', 'score', 'away_team_runs', 'home_team_runs', 'next', 'num', 'b', 's', 'o', 'start_tfs', 'start_tfs_zulu', 'batter', 'battingteam', 'stand', 'b_height', 'pitcher', 'p_throws', 'playdes', 'esplaydes', 'event', 'des', 'des_es', 'id', 'on_1b', 'on_2b', 'on_3b', 'type', 'tfs', 'tfs_zulu', 'x', 'y', 'sv_id', 'start_speed', 'end_speed', 'sz_top', 'sz_bot', 'pfx_x', 'pfx_z', 'px', 'pz', 'x0', 'y0', 'z0', 'vx0', 'vy0', 'vz0', 'ax', 'ay', 'az', 'break_y', 'break_angle', 'break_length', 'pitch_type', 'type_confidence', 'zone', 'nasty', 'spin_dir', 'spin_rate', 'cc', 'mt']

def CreateWriter(file):
   """takes an open, writable CSV file, creates a dict writer for parse games function"""   
   fieldnames = SetFieldNames()
   return csv.DictWriter(file, delimiter = ',', fieldnames = fieldnames)  
   
def GetGameBox(gameid):
   """Takes MLB gameid string, returns an elementtree object containing all game events"""
   year, month, day = gameid[0:4], gameid[5:7], gameid[8:10] #extract date info from the gameid string
   baseURL = 'http://gd2.mlb.com/components/game/mlb/year_' #set URL beginning
   fullURL = baseURL + year + '/month_' + month + '/day_' + day + '/gid_' + gameid + '/rawboxscore.xml' #construct full URL for the game event XML file
   try:
      return ET.parse(urllib2.urlopen(fullURL)) # Tries to return the parsed game element from the XML file if the URL can be opened. If it can't 
   except:                                      # (this only is the case for some extra 2012 Marlins gameids as far as I know), returns none
      return None                               #
      
def GetGame(gameid):
   """Takes MLB gameid string, returns an elementtree object containing all game events"""
   year, month, day = gameid[0:4], gameid[5:7], gameid[8:10] #extract date info from the gameid string
   baseURL = 'http://gd2.mlb.com/components/game/mlb/year_' #set URL beginning
   fullURL = baseURL + year + '/month_' + month + '/day_' + day + '/gid_' + gameid + '/inning/inning_all.xml' #construct full URL for the game event XML file
   try:
      return ET.parse(urllib2.urlopen(fullURL)) # Tries to return the parsed game element from the XML file if the URL can be opened. If it can't 
   except:                                      # (this only is the case for some extra 2012 Marlins gameids as far as I know), returns none
      return None                               #

def GetGameEvents(gameid):
   """Takes MLB gameid string, returns an elementtree object containing all game events"""
   year, month, day = gameid[0:4], gameid[5:7], gameid[8:10] #extract date info from the gameid string
   baseURL = 'http://gd2.mlb.com/components/game/mlb/year_' #set URL beginning
   fullURL = baseURL + year + '/month_' + month + '/day_' + day + '/gid_' + gameid + '/game_events.xml' #construct full URL for the game event XML file
   try:
      return ET.parse(urllib2.urlopen(fullURL)) # Tries to return the parsed game element from the XML file if the URL can be opened. If it can't 
   except:                                      # (this only is the case for some extra 2012 Marlins gameids as far as I know), returns none
      return None                               #

#HR- gettable from boxcar json, a bit tricky...
#Triples - gettable from json, a bit tricky
#Game time, boxscore json (time) [ might be confused by multi-day games? ] 
#LOB -- can get from boxscore, a bit ugly...
#Attendance, boxscore json
# Temperature, boxscore json, weather

def GetGameBoxScoreJson(gameid):
   """Takes MLB gameid string, returns an elementtree object containing all game events"""
   year, month, day = gameid[0:4], gameid[5:7], gameid[8:10] #extract date info from the gameid string
   baseURL = 'http://gd2.mlb.com/components/game/mlb/year_' #set URL beginning
   fullURL = baseURL + year + '/month_' + month + '/day_' + day + '/gid_' + gameid + '/boxscore.json' #construct full URL for the game event XML file
   try:
      page = urllib2.urlopen(fullURL)
      data = page.read()
      jdata = json.loads(data)
      return jdata
   except:                                      # (this only is the case for some extra 2012 Marlins gameids as far as I know), returns none
      return None                               #


def ExtractTeamRecords(dd):
   """Takes json dictionary of all boxscore, returns relevant stuff"""
   retval = [];
   res = {}
   try:
      res['venue_id'] = dd['data']['boxscore']['venue_id']
      res['date'] = dd['data']['boxscore']['date']
      res['venue_name'] = dd['data']['boxscore']['venue_name']
      ginfo = dd['data']['boxscore']['game_info']
      soup = BeautifulSoup(ginfo)
      attendance = int(soup.find_all('attendance')[0].string.replace(',',''))
      res['attendance'] = attendance
      tempstrs = soup.find_all('weather')[0].string.split()
      res['temp'] = int(tempstrs[0])
      res['hrs'] = int(dd['data']['boxscore']['batting'][0]['hr']) + int(dd['data']['boxscore']['batting'][1]['hr'])

      l0soup = BeautifulSoup(dd['data']['boxscore']['batting'][0]['text_data'])
      lob0 = int(l0soup.find_all('team_lob')[0].string.split('.')[0])
      l1soup = BeautifulSoup(dd['data']['boxscore']['batting'][1]['text_data'])
      lob1 = int(l1soup.find_all('team_lob')[0].string.split('.')[0])
      res['lob'] = lob0 + lob1
      
      times = soup.find_all('time')[0].string.split(':')
      res['time'] = int(times[0])*60+int(times[1][0:2])
      res['trips'] = int(dd['data']['boxscore']['batting'][0]['t']) + int(dd['data']['boxscore']['batting'][1]['t'])
      res['ngames'] = 1
   except:
      print 'no data yet'
      print res
      res = {}
#      pdb.set_trace()
 
      
   if (len(res) > 0):
      retval.append(res)      
   return retval

      
def TodaysGames():
   """Returns a list of gameids corresponding to games on the current date"""
#   today = datetime.date.today().__str__()
   today = date.today().__str__()
   year, month, day = today[0:4], today[5:7], today[8:10]
   baseURL = 'http://gd2.mlb.com/components/game/mlb/year_'
   fullURL = baseURL + year + '/month_' + month + '/day_' + day + '/'
   return [a.text.strip()[4:-1] for a in BeautifulSoup(urllib2.urlopen(fullURL)).find_all('li',text=re.compile("gid_"))]

def DateGames(date):
   """Takes a date string yyyy-mm-dd, returns list of gameids corresponding to games on that date"""
   today = date
   year, month, day = today[0:4], today[5:7], today[8:10]
   baseURL = 'http://gd2.mlb.com/components/game/mlb/year_'
   fullURL = baseURL + year + '/month_' + month + '/day_' + day + '/'
   return [a.text.strip()[4:-1] for a in BeautifulSoup(urllib2.urlopen(fullURL)).find_all('li',text=re.compile("gid_"))]
   
def ParsePitch(pitch, GameProperties, InnProperties, ABProperties, istop, writer):
   """Takes pitch XML element, dictionaries for game, inning, and AB properties, boolean for top/bottom inning, and a dictwriter; writes all attributes + pitch attributes as CSV row"""
   if istop:
      battingTeam = {'battingteam' : InnProperties['away_team']}
   else:
      battingTeam = {'battingteam' : InnProperties['home_team']}
   PitchProperties = pitch.attrib
   try:
      PitchProperties['on_1b']
   except KeyError:
      PitchProperties['on_1b'] = ' '
   try:
      PitchProperties['on_2b']
   except KeyError:
      PitchProperties['on_2b'] = ' '
   try:
      PitchProperties['on_3b']
   except KeyError:
      PitchProperties['on_3b'] = ' '
   finaldict = dict(GameProperties.items() + InnProperties.items() + ABProperties.items() + battingTeam.items() + PitchProperties.items()) #DIE FOREVER TEXT ENCODING
   
   for key in finaldict.keys():
      finaldict[key] = finaldict[key].encode('ascii','ignore')
	  
   writer.writerow(finaldict)
   
   return 'pitch written' #not sure what this should return really

def ParseAtBat(atbat):
   """Takes an atbat XML element, returns dictionary of atbat properties"""  
   global awayteamruns
   global hometeamruns
   atbatdict = atbat.attrib
   atbatdict['playdes'] = atbatdict.pop('des')
   try:
      atbatdict['esplaydes'] = atbatdict.pop('des_es')
   except KeyError:
      atbatdict['esplaydes'] = ' '
   try:
      atbatdict['away_team_runs']
      newawayruns = int(atbatdict['away_team_runs'])
      atbatdict['away_team_runs'] = str(awayteamruns)
      awayteamruns = newawayruns
   except KeyError:
      atbatdict['away_team_runs'] = str(awayteamruns)
   try:	  
      atbatdict['home_team_runs']
      newhomeruns = int(atbatdict['home_team_runs'])
      atbatdict['home_team_runs'] = str(hometeamruns)
      hometeamruns = newhomeruns
   except KeyError:
	  atbatdict['home_team_runs'] = str(hometeamruns)
   try:
      atbatdict['score']
   except KeyError:
      atbatdict['score'] = ' '
   try:
      del atbatdict['event2']
   except KeyError:
      return atbatdict
 	  
   return atbatdict

def ParseInning(inning):
   """Takes an inning XML element, returns dictionary of inning properties"""
   inningdict = inning.attrib
   inningdict['inningnum'] = inningdict.pop('num')
   return inningdict

def GetNumberOfInnings(game):
    """Returns the # of innings"""
    maxinning = 0
    for element in game.iter():
        if element.tag == 'inning':
            foodict = element.attrib
            inum = foodict.pop('num')
            if inum > maxinning:
                maxinning = inum
    return maxinning

def AddGameToSet(game,set):
    maxinning = 0
    hometeam = '';
    awayteam = '';
    if (game != None):
        for element in game.iter():
            if element.tag == 'inning':
                foodict = element.attrib
                maxinning = maxinning+1
                hometeam = foodict['home_team']
                awayteam = foodict['away_team']
                set[hometeam] = maxinning
                set[awayteam] = maxinning
    return set

def BuildInningSet(date):
    gameids = DateGames(date)
    inningset = dict()
    for game in gameids:
       thegame = GetGame(game)
       inningset = AddGameToSet(thegame,inningset)
    return inningset

def AddBatterToDB(bb,g,date,gb):
   #Get the stuff out of bb
   global cursor
   global db
   
   date = date;
   player = int(bb.attrib['id'])
   gamekey = g
   pitcherp = 0
   status = gb.findall(".")[0].attrib['status_ind']
   W = 0
   SV = 0
   QS = 0
   DP = 0
   ER = 0
   H = bb.attrib['h']
   HR = bb.attrib['hr']
   BB = bb.attrib['bb']
   ASS = bb.attrib['a']
   SB = bb.attrib['sb']
   foo = cursor.execute("select id from info where gamekey='%s' and player=%s"%(gamekey,str(player)))
   all_res = cursor.fetchall()
   if len(all_res) > 0:
      #use alter
      id = int(all_res[0][0])
      querystr = "REPLACE into info (id, date, player,gamekey,pitcherp,status,W,SV,QS,DP,ER,H,HR,BB,ASS,SB) values (%s, '%s', %s, '%s', %s, '%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"%(str(id),date,str(player),gamekey,str(pitcherp),str(status),str(W),str(SV),str(QS),str(DP),str(ER),str(H),str(HR),str(BB),str(ASS),str(SB))
      #print querystr
      cursor.execute(querystr)
   else:
      #use insert
      querystr = "INSERT into info (date, player,gamekey,pitcherp,status,W,SV,QS,DP,ER,H,HR,BB,ASS,SB) values ('%s', %s, '%s', %s, '%s',%s,%s,%s,%s,%s,%s,%s,%s,%s, %s)"% (date,str(player),gamekey,str(pitcherp),str(status),str(W),str(SV),str(QS),str(DP),str(ER),str(H),str(HR),str(BB),str(ASS),str(SB))
      #print querystr
      #print type(querystr)
      cursor.execute(querystr)
      db.commit()

def AddPitcherToDB(pp,g,date,gb):
   #Get the stuff out of pp
   global cursor
   global db
   
   date = date;
   player = int(pp.attrib['id'])
   gamekey = g
   pitcherp = 1
   status = gb.findall(".")[0].attrib['status_ind']
   if ('win' in pp.attrib.keys()):
      W = 1
   else:
      W = 0
   if ('save' in pp.attrib.keys()):
      SV = 1
   else:
      SV = 0
   outs = pp.attrib['out']
   ER = pp.attrib['er']
   #print "outs %s, ER %s"%(str(outs),str(ER))
   #print "type of outs is " + str(type(outs))
   #print "type of ER is " + str(type(ER))
   DP = 0
   if int(outs) >= 18 and int(ER) <=3:
      QS = 1
      #print 'should be qs'
   else:
      QS = 0
      #print 'should not be qs'      
   H = 0
   HR = 0
   BB = 0
   ASS = 0
   SB = 0
   foo = cursor.execute("select id from info where gamekey='%s' and player=%s"%(gamekey,str(player)))
   all_res = cursor.fetchall()
   if len(all_res) > 0:
      #use replace
      id = int(all_res[0][0])
      querystr = "REPLACE into info (id, date, player,gamekey,pitcherp,status,W,SV,QS,DP,ER,H,HR,BB,ASS,SB) values (%s, '%s', %s, '%s', %s, '%s',%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"%(str(id),date,str(player),gamekey,str(pitcherp),str(status),str(W),str(SV),str(QS),str(DP),str(ER),str(H),str(HR),str(BB),str(ASS),str(SB))
      #print querystr
      cursor.execute(querystr)
   else:
      #use insert
      querystr = "INSERT into info (date, player,gamekey,pitcherp,status,W,SV,QS,DP,ER,H,HR,BB,ASS,SB) values ('%s', %s, '%s', %s, '%s',%s,%s,%s,%s,%s,%s,%s,%s,%s, %s)"% (date,str(player),gamekey,str(pitcherp),str(status),str(W),str(SV),str(QS),str(DP),str(ER),str(H),str(HR),str(BB),str(ASS),str(SB))
      #print querystr
      #print type(querystr)
      cursor.execute(querystr)
      db.commit()

def AddAtBatToDB(ab,g,date):
   global cursor
   global db
   pp = ab.attrib['pitcher']
   querystr = "select id,DP from info where gamekey = '%s' and player = %s"%(g,str(pp))
   #print querystr
   cursor.execute(querystr)
   ress = cursor.fetchall()
   if len(ress) < 1 or len(ress) > 1:
      print 'ERROR in atbat'
   else:
      sqlid = ress[0][0]
      DP  = ress[0][1]
      DP = DP + 1
      querystr = "update info set DP=%s where id=%s"%(str(DP),str(sqlid))
      #print querystr
      cursor.execute(querystr)
      db.commit()
      

   
def AddGameToDB(g,date):
   ge = GetGameEvents(g)
   gb = GetGameBox(g)
   if (gb != None and ge != None):
      batters = gb.findall('.//batter')
      pitchers = gb.findall('.//pitcher')
      for bb in batters:
         AddBatterToDB(bb,g,date,gb)
         for pp in pitchers:
            AddPitcherToDB(pp,g,date,gb)
         atbats = ge.findall(".//atbat[@event='Double Play']")
         w2 = ge.findall(".//atbat[@event='Grounded Into DP']")
         for a in w2:
            atbats.append(a)
         w3 = ge.findall(".//atbat[@event='Triple Play']")
         for a in w3:
            atbats.append(a)      
         for ab in atbats:
            AddAtBatToDB(ab,g,date)

def printDictList(ff, dlist, cols=None):
   if cols is None:
      cols = dlist[0].keys
   #printheader
   ff.write('<table><tr>')
   for c in cols:
      ff.write('<th>%s</th>'%(c))
   ff.write('\n</tr>')
   #now write the data
   for r in dlist:
      ff.write('<tr>')
      for c in cols:
         ff.write('<td>%s</td>'%(r[c]))
      ff.write('</tr>\n')
   ff.write('</table>')
   ff.flush()

def printDictListCSV(ff, dlist, cols=None):
   if cols is None:
      cols = dlist[0].keys()
   #printheader
   for c in cols:
      ff.write('%s,'%(c))
   ff.write('\n')
   #now write the data
   for r in dlist:
      for c in cols:
         ff.write('%s,'%(r[c]))
      ff.write('\n')
   ff.flush()


def OutputTablesToFile(filename,all_filename,ts,tsToday,ress):
   tls = ts
   vps = getVictoryPoints(ts)
   svps = sorted(vps,key=lambda k: k['total'],reverse=True)
   sts = [x for (x,y) in sorted(zip(ts,vps),key=lambda k: k[1]['total'],reverse=True)]   
   stsToday = [x for (x,y) in sorted(zip(tsToday,vps),key=lambda k: k[1]['total'],reverse=True)]

   ii = 0
   for svi in svps:
      m2s = scoreSingle([x['total'] for x in svps],ii)      
      svi['through_two'] = svi['mtotal'] + m2s
      svi['m2'] = m2s
      ii = ii+1
   ff = open(filename,'wb')
   #   ff.write('teams<br>')
   #   printDictList(ff,sts,['team_name','runs_for_team','runs_against_team','error_team'])
   #   ff.flush()
   ff.write('<BR><BR>Stats<BR>')
   printDictList(ff,sts,['team_name','times','temps','hrs','triples','attendance','lob','ngames'])
   ff.flush()
   ff.write('<BR><BR>Points<BR>')
   printDictList(ff,svps,['team_name','times','temps','hrs','triples','attendance','lob','total'])
   ff.write('<BR><BR>Today Stats<BR>')
   printDictList(ff,stsToday,['team_name','times','temps','hrs','triples','attendance','lob','ngames'])

   ff.write('<BR><BR>Season scores As of Today:<BR>')

   ssvps = sorted(svps,key=lambda k: k['through_two'],reverse=True)
   printDictList(ff,ssvps,['team_name','m1','m2','through_two'])
   ff.write('<BR><BR>')
   ff.write("<a href='all.csv'> all.csv </a><BR><BR>")

   ff.write(str(datetime.now()))

   ff.close()
   ff = open(all_filename,'wb')
   printDictListCSV(ff,ress,['venue_name','venue_id','hrs','trips','lob','attendance','temp','time','date'])
#   printDictListCSV(ff,ress)
   ff.close()

def d2s(d):
   return datetime.strftime(d,'%Y_%m_%d')

def DoTheDay():
   today = datetime.now()
   today = today.date()
   start_date = date(2015,5,1)
   end_date = date(2015,5,31)
   end_date = min(end_date,today)
   ts,ress = getFilledTeams(d2s(start_date),d2s(end_date))
   tsToday,rignore = getFilledTeams(d2s(end_date),d2s(end_date))
   OutputTablesToFile('/home/eddie7/code/wb4m2/stats_wb4m2.html','/home/eddie7/code/wb4m2/all.csv',ts,tsToday,ress)

def CompileDayGames(date):
   res = []
   gameids = DateGames(date)
   for g in gameids:
      print 'Doing game ' + g
      dinfo = ExtractTeamRecords(GetGameBoxScoreJson(g))
      if (len(dinfo) >0):
         res.extend(dinfo)
   return res

def DayGamesToDB(date):
   gameids = DateGames(date)
   for g in gameids:
      print 'Doing game ' + g
      if not(g == '2014_07_15_nasmlb_aasmlb_1'):
         AddGameToDB(g,date)
         print 'Added game ' + g

#Call RangeGamesToDB('2014_07_01','2014_07_13') to update the DB
def RangeGamesToDB(date1,date2):
   pdate1 = datetime.strptime(date1,'%Y_%m_%d').date()
   pdate2 = datetime.strptime(date2,'%Y_%m_%d').date()
   if pdate2 <= pdate1:
      raise Exception('date2 must be after date1')
   oneday = timedelta(1)
   thedate = pdate1
   while thedate <=pdate2:
      print 'Doing games for date ' + str(thedate)
      DayGamesToDB(thedate.strftime('%Y_%m_%d'))
      thedate = thedate+oneday

def CompileRangeGames(date1,date2):
   res = []
   pdate1 = datetime.strptime(date1,'%Y_%m_%d').date()
   pdate2 = datetime.strptime(date2,'%Y_%m_%d').date()
   if pdate2 < pdate1:
      raise Exception('date2 must be at or after date1')
   oneday = timedelta(1)
   thedate = pdate1
   while thedate <=pdate2:
      print 'Doing games for date ' + str(thedate)
      res.extend(CompileDayGames(thedate.strftime('%Y_%m_%d')))
      thedate = thedate+oneday
   return res

def PrintMonth():
    print 'Rangers,Reds,Rockies,Rays,Tigers,Brewers,Orioles,Indians,Giants,Braves,Pirates,BlueJays,RedSox,Mets,Cardinals,Yankees,'
    teamlist = ['tex','cin','col','tba','det','mil','bal','cle','sfn','atl','pit','tor','bos','nyn','sln','nya']
    today= date.today();
    
    for i in range(1,31):
        if  i <= today.day:
            iset = BuildInningSet('2013-08-%02d'%i)
            s = '2013-08-%02d,'%i
            for team in teamlist:
                if (team in iset):
                    if iset[team] > 9:
                        s += '%d,'%(iset[team]-9)
                    else:
                        s += '0,'
                else:
                    s += '0,'
            print s

            

def UpdateTodayInSheet():
    gs = gspread.login('dandreauto@gmail.com','841Melissa5814')
    spreadsheet = gs.open('Extra Innings')
    worksheet = spreadsheet.worksheet('AutoImport')
    teamlist = ['tex','cin','col','tba','det','mil','bal','cle','sfn','atl','pit','tor','bos','nyn','sln','nya']
    today = date.today();
    
    i = today.day
    iset = BuildInningSet('2013-08-%02d'%i)
    s = '2013-08-%02d,'%i
    col= 2
    row = i + 2
    for tt in range(0,len(teamlist)):
        team = teamlist[tt]
        if (team in iset):
            if iset[team] > 9:
                worksheet.update_cell(row,col,iset[team]-9) 
        col = col+1
    

    
def ParseGame(game, gameid, writer):
   """Takes a game element, a gameid string, and a dictwriter and adds rows to a csv file correpsonding to the game's pitches"""
   
   global awayteamruns
   global hometeamruns
   
   GameProperties = {'gameid' : gameid}  
   awayteamruns = 0
   hometeamruns = 0   
   
   for element in game.iter():
      if element.tag == 'inning':
         InnProperties = ParseInning(element)
      if element.tag == 'top':
         istop = True
      if element.tag == 'bottom':
         istop = False
      if element.tag == 'atbat':
         ABProperties = ParseAtBat(element)
      if element.tag == 'pitch':
         ParsePitch(element, GameProperties, InnProperties, ABProperties, istop, writer)
      else:
         continue

def WriteGames(gameids, filename):
   """Takes a list of game ids and a csv filename string and uses ParseGame to write each to a csv file"""
   global awayteamruns
   global hometeamruns
   outputFile = open(filename,'wb')
   outputWriter = CreateWriter(outputFile)
   outputWriter.writeheader()
   for game in gameids:
      try:
	     ParseGame(GetGame(game), game, outputWriter)
      except AttributeError:
         print "Couldn't find " + game
      print game + ' completed'
   return outputFile