def parse(self, response):
tbl = response.css("#tbl_gameslist.ewTable.ewTableSeparate")[1]
for line in tbl.css("tbody tr"):
teamid = response.url.split("=")[-1]
info = line.css("td::text").extract()
item = PlayerItem()
item["name"] = info[0]
item["team"] = TeamDict.getnanqifull(int(teamid))
if len(info) > 1:
item["school"] = info[1]
else:
item["school"] = None
item["num"] = random.randint(0, 99)
yield item
import pymysql.cursors
import TeamDict
connection = pymysql.connect(host='localhost',
user='******',
password='******',
db='MANAN_1819',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
sql = 'SELECT * FROM Matches'
cursor.execute(sql)
matches = cursor.fetchall()
for m in matches:
if m['Stage'] == 'Group':
sql = 'UPDATE Teams SET GroupName = %s, Level = %s WHERE TeamName = %s'
cursor.execute(sql, (m['GroupName'], m['Level'],
TeamDict.getfull(m['HomeTeam'])))
cursor.execute(sql, (m['GroupName'], m['Level'],
TeamDict.getfull(m['AwayTeam'])))
connection.commit()
finally:
connection.close()
def Suspension(dbname):
connection = pymysql.connect(host='localhost',
user='******',
password='******',
db=dbname,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
#初始化
sql = 'UPDATE Players SET Suspension = 0'
cursor.execute(sql)
#额外的附加停赛
#...
sql = 'SELECT MatchID, HomeTeam, AwayTeam, Stage, MatchTime FROM Matches WHERE Valid = 1 ORDER BY MatchTime'
cursor.execute(sql) #取出所有生效比赛
matches = cursor.fetchall()
Vmatch = []
VPlayer = dict()
errstr = ''
for match in matches:
Vmatch.append(
Match(match['MatchID'], match['HomeTeam'],
match['AwayTeam'], match['Stage'],
match['MatchTime']))
for i, m in enumerate(Vmatch):
if m.stage != 'Group' and Vmatch[i -
1].stage == 'Group': #小组赛清牌
for p in VPlayer.items():
if p[1].accuyc != 0:
p[1].accuyc = 0
sql = 'SELECT * FROM Match' + str(
m.matchid) + " ORDER BY EventTime,StoppageTime"
cursor.execute(sql)
Infos = cursor.fetchall()
for info in Infos:
key = info['Team'] + str(info['KitNumber']) + info['Name']
if not key in VPlayer:
VPlayer[key] = Player(name=info['Name'],
team=info['Team'],
kitnum=info['KitNumber'])
if VPlayer[key].suspension > 0:
#停赛上场警告
es = 'Match' + str(
m.matchid
) + '\t' + m.hometeam + '\t' + m.awayteam + '\t' + str(
VPlayer[key].kitnum) + '-' + VPlayer[
key].name + '\t' + info['EventType'] + '\n'
errstr += es
print('ERROR! ', es)
else:
if info['EventType'] == '黄牌':
VPlayer[key].accuyc += 1
if info['EventType'] == '红牌':
VPlayer[key].accuyc += 2
for p in VPlayer.items():
if p[1].team in (TeamDict.getfull(m.hometeam),
TeamDict.getfull(m.awayteam)): #处理停赛的实施
if p[1].suspension > 0:
p[1].suspension -= 1
if p[1].accuyc > 1: #处理停赛的产生
sus = p[1].accuyc // 2
p[1].accuyc = 0
p[1].suspension = sus
for p in VPlayer.items():
if p[1].suspension != 0:
print('TEAM:', p[1].team, 'NAME:', p[1].name, 'SUS:',
p[1].suspension)
sql = 'UPDATE Players SET Suspension = %s WHERE Team = %s AND KitNumber = %s AND Name = %s'
cursor.execute(
sql,
(p[1].suspension, p[1].team, p[1].kitnum, p[1].name))
connection.commit()
with open('errsuspension.txt', 'w') as errfile:
errfile.write(errstr)
finally:
connection.close()
def Stat(matchname, matchid = None, add = True):
connection = pymysql.connect(host='localhost',
user='******',
password='******',
db=matchname,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM Info"
cursor.execute(sql)
matchinfo = cursor.fetchall()[0]
outtime = matchinfo['ordinarytime'] + 1
EliMatches = dict()
if os.path.exists('../html/' + matchname + '.json'):
with open('../html/' + matchname + '.json') as ef:
deli = json.loads(ef.readline())[1]
for eid,eit in deli.items():
EliMatches[int(eid)] = Match(matchid = eit['matchid'], stage = eit['stage'], hometeam = eit['hometeam'], awayteam = eit['awayteam'])
with connection.cursor() as cursor:
sql = 'SELECT MatchID,Hometeam,Awayteam,Stage,Valid FROM Matches'
if matchid != None:
sql = sql + " WHERE MatchID = %s"
cursor.execute(sql,matchid)
else:
cursor.execute(sql)
matches = cursor.fetchall() #info of all matches
Vmatch = []
for match in matches:
if match['Valid'] == 1:
#all valid matches
if not match['Stage'] == 'Group' and not match['Stage'] == 'League':
match['Hometeam'] = EliMatches[match['MatchID']].hometeam
match['Awayteam'] = EliMatches[match['MatchID']].awayteam
if re.match('^MA.+',matchname):
match['Hometeam'] = TeamDict.getfull(match['Hometeam'])
match['Awayteam'] = TeamDict.getfull(match['Awayteam'])
Vmatch.append(Match(match['MatchID'],match['Hometeam'],match['Awayteam'],match['Stage']));
#print(Vmatch)
for m in Vmatch:
hometeam=Team(m.hometeam)
awayteam=Team(m.awayteam)
sql = 'SELECT * FROM Match' + str(m.matchid) + ' ORDER BY EventTime,StoppageTime,EventType'
cursor.execute(sql)
groupbool = False
if m.stage == 'Group' or m.stage == 'League':
groupbool = True
Infos = cursor.fetchall() #all events
if (matchinfo['penalty'] == "elimination" and not groupbool) or (matchinfo['penalty'] == "always"):
ohg = 0
oag = 0
for info in Infos:
if info['EventTime'] < matchinfo['ordinarytime'] + 1:
if info['EventType'] == '进球' or info['EventType'] == '点球':
if info['Team'] == m.hometeam:
ohg += 1
elif info['Team'] == m.awayteam:
oag += 1
if info['EventType'] == '乌龙球':
if info['Team'] == m.hometeam:
oag += 1
elif info['Team'] == m.awayteam:
ohg += 1
if oag == ohg:
outtime = matchinfo['extratime'] + matchinfo['ordinarytime'] + 1
players = dict()
Habandon = False
Aabandon = False
for info in Infos:
if info['EventType'] == '弃赛':
if info['Team'] == m.hometeam:
Habandon = True
else:
Aabandon = True
elif not str(info['KitNumber']) + info['Name'] in players:
if info['EventType'] == '首发':
players[str(info['KitNumber']) + info['Name']] = Player(name = info['Name'], team = info['Team'], kitnum = info['KitNumber'], outtime = outtime)
elif info['EventType'] == '换上':
players[str(info['KitNumber']) + info['Name']] = Player(name = info['Name'], team = info['Team'], kitnum = info['KitNumber'], first = False, intime = info['EventTime'], outtime = outtime)
elif info['EventType'] == '黄牌':
players[str(info['KitNumber']) + info['Name']] = Player(name = info['Name'], team = info['Team'], kitnum = info['KitNumber'], first = False, onpitch = False, outtime = outtime)
players[str(info['KitNumber']) + info['Name']].intime.remove(1)
players[str(info['KitNumber']) + info['Name']].yc += 1
if info['Team'] == m.hometeam:
hometeam.yc += 1
else:
awayteam.yc += 1
elif info['EventType'] == '红牌':
players[str(info['KitNumber']) + info['Name']] = Player(name = info['Name'], team = info['Team'], kitnum = info['KitNumber'], first = False, onpitch = False, outtime = outtime)
players[str(info['KitNumber']) + info['Name']].intime.remove(1)
players[str(info['KitNumber']) + info['Name']].rc += 1
if info['Team'] == m.hometeam:
hometeam.rc += 1
else:
awayteam.rc += 1
else:
if info['EventType'] == '进球':
players[str(info['KitNumber']) + info['Name']].goals += 1
if info['Team'] == m.hometeam:
hometeam.goals += 1
awayteam.concede += 1
else:
hometeam.concede += 1
awayteam.goals += 1
elif info['EventType'] == '换上':
players[str(info['KitNumber']) + info['Name']].intime.append(info['EventTime'])
players[str(info['KitNumber']) + info['Name']].onpitch = True
elif info['EventType'] == '换下':
players[str(info['KitNumber']) + info['Name']].outtime.append(info['EventTime'])
elif info['EventType'] == '黄牌':
players[str(info['KitNumber']) + info['Name']].yc += 1
if players[str(info['KitNumber']) + info['Name']].yc == 2:
players[str(info['KitNumber']) + info['Name']].outtime.append(info['EventTime'])
players[str(info['KitNumber']) + info['Name']].yc = 0
players[str(info['KitNumber']) + info['Name']].rc = 1
if info['Team'] == m.hometeam:
hometeam.yc -= 1
hometeam.rc += 1
else:
awayteam.yc -= 1
awayteam.rc += 1
else:
if info['Team'] == m.hometeam:
hometeam.yc += 1
else:
awayteam.yc += 1
elif info['EventType'] == '红牌':
players[str(info['KitNumber']) + info['Name']].rc += 1
players[str(info['KitNumber']) + info['Name']].outtime.append(info['EventTime'])
if info['Team'] == m.hometeam:
hometeam.rc += 1
else:
awayteam.rc += 1
elif info['EventType'] == '点球':
players[str(info['KitNumber']) + info['Name']].penalty += 1
players[str(info['KitNumber']) + info['Name']].goals += 1
if info['Team'] == m.hometeam:
hometeam.goals += 1
hometeam.penalty += 1
awayteam.concede += 1
else:
hometeam.concede += 1
awayteam.goals += 1
awayteam.penalty += 1
elif info['EventType'] == '乌龙球':
players[str(info['KitNumber']) + info['Name']].owngoal += 1
if info['Team'] == m.hometeam:
awayteam.goals += 1
hometeam.concede += 1
hometeam.owngoal += 1
else:
awayteam.concede += 1
hometeam.goals += 1
awayteam.owngoal += 1
elif info['EventType'] == '点球罚失':
players[str(info['KitNumber']) + info['Name']].penmiss += 1
if info['Team'] == m.hometeam:
hometeam.penmiss += 1
else:
awayteam.penmiss += 1
elif info['EventType'] == '点球决胜罚进':
if info['Team'] == m.hometeam:
m.homepenalty += 1
else:
m.awaypenalty += 1
if Habandon and Aabandon:
hometeam.point = 0
awayteam.point = 0
hometeam.goals = 0
awayteam.goals = 0
hometeam.concede = 3
awayteam.concede = 3
elif Habandon and not Aabandon and awayteam.goals - hometeam.goals <= 3:
hometeam.point = 0
awayteam.point = 3
hometeam.concede = 3
awayteam.goals = 3
hometeam.goals = 0
awayteam.concede = 0
elif not Habandon and Aabandon and hometeam.goals - awayteam.goals <= 3:
hometeam.point = 3
awayteam.point = 0
hometeam.goals = 3
awayteam.goals = 0
awayteam.concede = 3
hometeam.concede = 0
elif hometeam.goals > awayteam.goals:
hometeam.point = 3
awayteam.point = 0
elif hometeam.goals < awayteam.goals:
hometeam.point = 0
awayteam.point = 3
else:
hometeam.point = 1
awayteam.point = 1
for p in players.items():
p[1].getontime()
print(p[1].team, p[1].name,'A:',p[1].onpitch,'G:',p[1].goals,'Y:',p[1].yc,'R:',p[1].rc,'P:',p[1].penalty,'PM:',p[1].penmiss,'OG:',p[1].owngoal,'MIN:',p[1].ontime)
print(hometeam.name,'G:',hometeam.goals,'C:',hometeam.concede,'P:',hometeam.penalty,'PM:',hometeam.penmiss,'Y:',hometeam.yc,'R:',hometeam.rc,'PT:',hometeam.point)
print(awayteam.name,'G:',awayteam.goals,'C:',awayteam.concede,'P:',awayteam.penalty,'PM:',awayteam.penmiss,'Y:',awayteam.yc,'R:',awayteam.rc,'PT:',awayteam.point)
#update the match
if add:
m.homegoal = hometeam.goals
m.awaygoal = awayteam.goals
m.result = hometeam.point
if Habandon and Aabandon:
m.result = -1
if m.stage != 'Group' and m.homegoal == m.awaygoal:
m.penaltyshootout = True
sql = 'UPDATE Matches SET HomeGoal = %s, AwayGoal = %s, PenaltyShootOut = %s, HomePenalty = %s, AwayPenalty = %s, Result = %s WHERE MatchID = %s'
cursor.execute(sql,(m.homegoal, m.awaygoal, m.penaltyshootout, m.homepenalty, m.awaypenalty, m.result, m.matchid))
else:
sql = 'UPDATE Matches SET HomeGoal = %s, AwayGoal = %s, Result = %s WHERE MatchID = %s'
cursor.execute(sql,(m.homegoal, m.awaygoal, m.result, m.matchid))
else:
sql = 'UPDATE Matches SET HomeGoal = %s, AwayGoal = %s, PenaltyShootOut = %s, HomePenalty = %s, AwayPenalty = %s, Result = %s WHERE MatchID = %s'
cursor.execute(sql,(m.homegoal, m.awaygoal, m.penaltyshootout, m.homepenalty, m.awaypenalty, m.result, m.matchid))
#update teams
sql = 'SELECT * FROM Teams WHERE TeamName = %s'
cursor.execute(sql,hometeam.name)
ht = cursor.fetchall()[0]
sql = 'SELECT * FROM Teams WHERE TeamName = %s'
cursor.execute(sql,awayteam.name)
at = cursor.fetchall()[0]
print(ht,at)
if add:
if hometeam.point == 3:
ht['Win'] += 1
elif hometeam.point == 1:
ht['Draw'] += 1
elif hometeam.point == 0:
ht['Lose'] += 1
if awayteam.point == 3:
at['Win'] += 1
elif awayteam.point == 1:
at['Draw'] += 1
elif awayteam.point == 0:
at['Lose'] += 1
if groupbool:
ht['Point'] += hometeam.point
at['Point'] += awayteam.point
ht['Goal'] += hometeam.goals
at['Goal'] += awayteam.goals
ht['Concede'] += hometeam.concede
at['Concede'] += awayteam.concede
ht['Penalty'] += hometeam.penalty
at['Penalty'] += awayteam.penalty
ht['Penaltymiss'] += hometeam.penmiss
at['Penaltymiss'] += awayteam.penmiss
ht['OwnGoal'] += hometeam.owngoal
at['OwnGoal'] += awayteam.owngoal
ht['YellowCard'] += hometeam.yc
at['YellowCard'] += awayteam.yc
ht['RedCard'] += hometeam.rc
at['RedCard'] += awayteam.rc
else:
if hometeam.point == 3:
ht['Win'] -= 1
elif hometeam.point == 1:
ht['Draw'] -= 1
elif hometeam.point == 0:
ht['Lose'] -= 1
if awayteam.point == 3:
at['Win'] -= 1
elif awayteam.point == 1:
at['Draw'] -= 1
elif awayteam.point == 0:
at['Lose'] -= 1
if groupbool:
ht['Point'] -= hometeam.point
at['Point'] -= awayteam.point
ht['Goal'] -= hometeam.goals
at['Goal'] -= awayteam.goals
ht['Concede'] -= hometeam.concede
at['Concede'] -= awayteam.concede
ht['Penalty'] -= hometeam.penalty
at['Penalty'] -= awayteam.penalty
ht['Penaltymiss'] -= hometeam.penmiss
at['Penaltymiss'] -= awayteam.penmiss
ht['OwnGoal'] -= hometeam.owngoal
at['OwnGoal'] -= awayteam.owngoal
ht['YellowCard'] -= hometeam.yc
at['YellowCard'] -= awayteam.yc
ht['RedCard'] -= hometeam.rc
at['RedCard'] -= awayteam.rc
print(ht,at)
sql = 'UPDATE Teams SET Win = %s, Draw = %s, Lose = %s, Goal = %s, Concede = %s, Point = %s, Penalty = %s, YellowCard = %s, RedCard = %s, Penaltymiss = %s, OwnGoal = %s WHERE TeamName = %s'
cursor.execute(sql,(ht['Win'], ht['Draw'], ht['Lose'], ht['Goal'], ht['Concede'], ht['Point'], ht['Penalty'], ht['YellowCard'], ht['RedCard'], ht['Penaltymiss'], ht['OwnGoal'], ht['TeamName']))
cursor.execute(sql,(at['Win'], at['Draw'], at['Lose'], at['Goal'], at['Concede'], at['Point'], at['Penalty'], at['YellowCard'], at['RedCard'], at['Penaltymiss'], at['OwnGoal'], at['TeamName']))
#update players
for p in players.items():
p[1].getontime()
sql = 'SELECT * FROM Players WHERE Team = %s AND Name = %s AND KitNumber = %s'
cursor.execute(sql,(p[1].team, p[1].name, p[1].kitnum))
#print(p[1].name)
playerinfo = cursor.fetchall()
playerinfo = playerinfo[0]
#print(playerinfo)
if add:
if p[1].onpitch:
playerinfo['Appearances'] += 1
playerinfo['Minutes'] += p[1].ontime
playerinfo['Goals'] += p[1].goals
playerinfo['YellowCards'] += p[1].yc
playerinfo['RedCards'] += p[1].rc
playerinfo['Penalties'] += p[1].penalty
playerinfo['Penaltymiss'] += p[1].penmiss
playerinfo['OwnGoals'] += p[1].owngoal
else:
if p[1].onpitch:
playerinfo['Appearances'] -= 1
playerinfo['Minutes'] -= p[1].ontime
playerinfo['Goals'] -= p[1].goals
playerinfo['YellowCards'] -= p[1].yc
playerinfo['RedCards'] -= p[1].rc
playerinfo['Penalties'] -= p[1].penalty
playerinfo['Penaltymiss'] -= p[1].penmiss
playerinfo['OwnGoals'] -= p[1].owngoal
#print(playerinfo)
sql = 'UPDATE Players SET Appearances = %s, Minutes = %s, Goals = %s, YellowCards = %s, RedCards = %s, Penalties = %s, Penaltymiss = %s, OwnGoals = %s WHERE Team = %s AND Name = %s AND KitNumber = %s'
cursor.execute(sql,(playerinfo['Appearances'], playerinfo['Minutes'], playerinfo['Goals'], playerinfo['YellowCards'], playerinfo['RedCards'], playerinfo['Penalties'], playerinfo['Penaltymiss'], playerinfo['OwnGoals'], playerinfo['Team'], playerinfo['Name'], playerinfo['KitNumber']))
connection.commit()
finally:
connection.close()
def Evolve(matchname):
connection = pymysql.connect(host='localhost',
user='******',
password='******',
db=matchname,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
Groups = dict()
EliMatches = dict()
if os.path.exists('../html/' + matchname + '.json'):
#Get info from file
with codecs.open('../html/' + matchname + '.json', 'r',
'utf-8') as rankfile:
line = rankfile.readline()
[dgroups, deli] = json.loads(line)
#print(deli)
for gid, git in dgroups.items():
lt = git['teams']
T = []
for t in lt:
T.append(
Team(name=t['name'],
groupname=t['groupname'],
win=t['win'],
draw=t['draw'],
lose=t['lose'],
point=t['point'],
goals=t['goals'],
concede=t['concede']))
#print(git['name'], T)
Groups[gid] = Group(name=git['name'], teams=T)
for eid, eit in deli.items():
if not eid == 'end':
EliMatches[int(eid)] = Match(matchid=eit['matchid'],
stage=eit['stage'],
hometeam=eit['hometeam'],
awayteam=eit['awayteam'],
time=eit['time'],
homegoal=eit['homegoal'],
awaygoal=eit['awaygoal'],
result=eit['result'],
homewin=eit['homewin'],
valid=eit['valid'],
todecide=eit['todecide'])
#update data
sql = 'SELECT * FROM Teams'
cursor.execute(sql)
teams = cursor.fetchall()
for t in teams:
for i, g in Groups.items():
for Ti, Te in enumerate(g.teams):
if Te.name == t['TeamName']:
Groups[i].teams[Ti].win = t['Win']
Groups[i].teams[Ti].draw = t['Draw']
Groups[i].teams[Ti].lose = t['Lose']
Groups[i].teams[Ti].point = t['Point']
Groups[i].teams[Ti].goals = t['Goal']
Groups[i].teams[Ti].concede = t['Concede']
Groups[i].teams[Ti].Getgd()
break
sql = "SELECT * FROM Matches ORDER BY MatchTime"
cursor.execute(sql)
matches = cursor.fetchall()
for m in matches:
if not m['Stage'] == 'Group' and not m['Stage'] == 'League':
resultstr = str(m['HomeGoal']) + ':' + str(
m['AwayGoal'])
if m['PenaltyShootOut']:
resultstr += '(' + str(
int(m['HomeGoal']) +
int(m['HomePenalty'])) + ':' + str(
int(m['AwayGoal']) +
int(m['AwayPenalty'])) + ')'
for Mi, Ma in EliMatches.items():
if m['MatchID'] == Ma.matchid:
EliMatches[Mi].result = resultstr
if m['Result'] == '3': #HOMEWIN
EliMatches[Mi].homewin = True
elif m['Result'] == '0': #AWAYWIN
EliMatches[Mi].homewin = False
elif m['Result'] == '1': #PENALTY
if int(m['HomePenalty']) > int(
m['AwayPenalty']):
#HOMWPENALTYWIN
EliMatches[Mi].homewin = True
else:
#AWAYPENALTYWIN
EliMatches[Mi].homewin = False
EliMatches[Mi].valid = m['Valid']
EliMatches[Mi].homegoal = m['HomeGoal']
EliMatches[Mi].awaygoal = m['AwayGoal']
if not Ma.todecide:
EliMatches[Mi].hometeam = m['HomeTeam']
EliMatches[Mi].awayteam = m['AwayTeam']
for i, g in Groups.items():
g.roughsort()
else:
sql = 'SELECT * FROM Teams'
cursor.execute(sql)
teams = cursor.fetchall()
for t in teams:
T = Team(name=t['TeamName'],
groupname=t['GroupName'],
win=t['Win'],
draw=t['Draw'],
lose=t['Lose'],
point=t['Point'],
goals=t['Goal'],
concede=t['Concede'])
for i, g in Groups.items():
if T.groupname == g.name:
Groups[i].teams.append(T)
break
else:
Groups[T.groupname] = Group(name=T.groupname,
teams=[T])
for i, g in Groups.items():
g.roughsort(gd=True)
sql = "SELECT * FROM Matches ORDER BY MatchTime"
cursor.execute(sql)
matches = cursor.fetchall()
for m in matches:
resultstr = str(m['HomeGoal']) + ':' + str(m['AwayGoal'])
if m['PenaltyShootOut']:
resultstr += '(' + str(
int(m['HomeGoal']) +
int(m['HomePenalty'])) + ':' + str(
int(m['AwayGoal']) +
int(m['AwayPenalty'])) + ')'
M = Match(matchid=m['MatchID'],
stage=m['Stage'],
hometeam=m['HomeTeam'],
awayteam=m['AwayTeam'],
time=m['MatchTime'].strftime('%Y-%m-%d %H:%M'),
homegoal=m['HomeGoal'],
awaygoal=m['AwayGoal'],
result=resultstr,
valid=m['Valid'])
if not m['Stage'] == 'Group' and not m['Stage'] == 'League':
if m['Result'] == '3': #HOMEWIN
M.homewin = True
elif m['Result'] == '0': #AWAYWIN
M.homewin = False
elif m['Result'] == '1': #PENALTY
if int(m['HomePenalty']) > int(m['AwayPenalty']):
#HOMWPENALTYWIN
M.homewin = True
else:
#AWAYPENALTYWIN
M.homewin = False
EliMatches[M.matchid] = M
for emid, em in EliMatches.items():
ht = em.hometeam
at = em.awayteam
hg, hr, hmid, hw = Tracestr(ht)
ag, ar, amid, aw = Tracestr(at)
if hg:
for m in matches:
if m['GroupName'] == hg:
if m['Valid'] == 0:
break
else:
EliMatches[emid].hometeam = Groups[hg].teams[hr].name
elif hmid:
if EliMatches[hmid].valid:
if hw:
EliMatches[emid].hometeam = EliMatches[hmid].getWL(
)
else:
EliMatches[emid].hometeam = EliMatches[hmid].getWL(
WL='L')
else:
EliMatches[emid].todecide = True
if ag:
for m in matches:
if m['GroupName'] == ag:
if m['Valid'] == 0:
break
else:
EliMatches[emid].awayteam = Groups[ag].teams[ar].name
elif amid:
if EliMatches[amid].valid:
if aw:
EliMatches[emid].awayteam = EliMatches[amid].getWL(
)
else:
EliMatches[emid].awayteam = EliMatches[amid].getWL(
WL='L')
else:
EliMatches[emid].todecide = True
if em.valid:
for i, g in Groups.items():
for ti, te in enumerate(g.teams):
if TeamDict.getfull(
em.hometeam) == TeamDict.getfull(te.name):
Groups[i].teams[ti].goals -= em.homegoal
Groups[i].teams[ti].concede -= em.awaygoal
if em.homegoal > em.awaygoal:
Groups[i].teams[ti].win -= 1
elif em.homegoal < em.awaygoal:
Groups[i].teams[ti].lose -= 1
elif em.homegoal == em.awaygoal:
Groups[i].teams[ti].draw -= 1
if TeamDict.getfull(
em.awayteam) == TeamDict.getfull(te.name):
Groups[i].teams[ti].goals -= em.awaygoal
Groups[i].teams[ti].concede -= em.homegoal
if em.homegoal > em.awaygoal:
Groups[i].teams[ti].lose -= 1
elif em.homegoal < em.awaygoal:
Groups[i].teams[ti].win -= 1
elif em.homegoal == em.awaygoal:
Groups[i].teams[ti].draw -= 1
for i, g in Groups.items():
print(g.name)
jsonstr = json.dumps([Groups, EliMatches],
default=lambda g: g.__dict__,
sort_keys=True)
#print(groupstr,elistr)
with open('../html/' + matchname + '.json', 'w') as rankfile:
rankfile.write(jsonstr)
for i, g in Groups.items():
print(g.name)
for t in g.teams:
print(t.name, t.point)
for emid, em in EliMatches.items():
print(emid, em.time, em.hometeam, em.awayteam)
finally:
connection.close()
def createmanansheet(hometeam='',awayteam='',title='2018—2019年度清华大学马约翰学生运动会',matchdb = 'MANAN_1718',subtitle = '男子足球执场单'):
filename = '/var/www/TUFA/sheettemplate.docx'
doc = Document(filename)
paras = doc.paragraphs
tables = doc.tables
styles = doc.styles
#renew the title
#for i,p in enumerate(paras):
# if len(p.runs):
# r = p.runs[0]
# print(i, r.text.encode('utf-8'))
paras[0].runs[0].text = title
for rest in paras[0].runs[1:]:
rest.text = ''
paras[12].runs[0].text = title
for rest in paras[12].runs[1:]:
rest.text = ''
paras[25].runs[0].text = title
for rest in paras[25].runs[1:]:
rest.text = ''
paras[1].runs[0].text =subtitle
for rest in paras[1].runs[1:]:
rest.text = ''
paras[13].runs[0].text = subtitle
for rest in paras[13].runs[1:]:
rest.text = ''
paras[26].runs[0].text = subtitle
for rest in paras[26].runs[1:]:
rest.text = ''
homeplayers = []
awayplayers = []
homeleaders = []
awayleaders = []
homeinfo = []
awayinfo = []
connection = pymysql.connect(host='localhost',
user='******',
password='******',
db=matchdb,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM Players WHERE Team = %s AND Valid = 1 ORDER BY KitNumber"
cursor.execute(sql,hometeam)
homeplayers = cursor.fetchall()
cursor.execute(sql,awayteam)
awayplayers = cursor.fetchall()
sql = "SELECT * FROM Leaders WHERE Team = %s"
cursor.execute(sql,hometeam)
homeleaders = cursor.fetchall()
cursor.execute(sql,awayteam)
awayleaders = cursor.fetchall()
sql = "SELECT * FROM Teams WHERE TeamName = %s"
cursor.execute(sql,hometeam)
homeinfo = cursor.fetchall()
cursor.execute(sql,awayteam)
awayinfo = cursor.fetchall()
finally:
connection.close()
#print(len(tables))
#for index,p in enumerate(tables):
# print('index:',index)
#for iq,q in enumerate(p.rows):
# print('row:',iq)
#for iw,w in enumerate(q.cells):
# print('cell',iw,":",w.text)
tables[0].rows[0].cells[0].paragraphs[0].runs[0].text = homeinfo[0]['TeamName']
tables[0].rows[0].cells[4].paragraphs[0].runs[1].text += homeinfo[0]['KitColor']
tables[16].rows[0].cells[0].paragraphs[0].runs[0].text = homeinfo[0]['TeamName']
tables[16].rows[0].cells[3].paragraphs[0].runs[1].text += homeinfo[0]['KitColor']
if len(homeleaders) > 0:
tables[0].rows[32].cells[2].paragraphs[0].runs[0].text = homeleaders[0]['Name']
tables[0].rows[32].cells[5].paragraphs[0].runs[0].text = homeleaders[1]['Name']
lenhc = len(tables[0].rows[0].cells[4].paragraphs[0].runs[0].text) + len(tables[0].rows[0].cells[4].paragraphs[0].runs[1].text)
if lenhc >= 10:
tables[0].rows[0].cells[4].paragraphs[0].runs[0].font.size = Pt(110/lenhc)
tables[0].rows[0].cells[4].paragraphs[0].runs[1].font.size = Pt(110/lenhc)
tables[16].rows[0].cells[3].paragraphs[0].runs[0].font.size = Pt(110/lenhc)
tables[16].rows[0].cells[3].paragraphs[0].runs[1].font.size = Pt(110/lenhc)
for i in range(len(homeplayers)):
tables[0].rows[i+2].cells[0].paragraphs[0].runs[0].text = str(homeplayers[i]['KitNumber'])
tables[16].rows[i+2].cells[0].paragraphs[0].runs[0].text = str(homeplayers[i]['KitNumber'])
extra = ""
if re.search('校友', homeplayers[i]['ExtraInfo']):
extra += "校友"
if re.search('教工', homeplayers[i]['ExtraInfo']):
extra += "教工"
if re.search('足特', homeplayers[i]['ExtraInfo']):
extra += "足特"
if re.search('队长', homeplayers[i]['ExtraInfo']):
extra += "C"
if not extra == "":
tables[0].rows[i+2].cells[1].paragraphs[0].runs[0].text = homeplayers[i]['Name'] + '(' + extra + ')'
tables[16].rows[i+2].cells[1].paragraphs[0].runs[0].text = homeplayers[i]['Name'] + '(' + extra + ')'
else:
tables[0].rows[i+2].cells[1].paragraphs[0].runs[0].text = homeplayers[i]['Name']
tables[16].rows[i+2].cells[1].paragraphs[0].runs[0].text = homeplayers[i]['Name']
lenname = len(tables[0].rows[i+2].cells[1].paragraphs[0].runs[0].text)
utf8_lenname = len(tables[0].rows[i+2].cells[1].paragraphs[0].runs[0].text.encode('utf-8'))
lenname = (utf8_lenname - lenname) / 2 * 1.5 + lenname
if lenname >= 16:
tables[0].rows[i+2].cells[1].paragraphs[0].runs[0].font.size = Pt(146/lenname)
tables[16].rows[i+2].cells[1].paragraphs[0].runs[0].font.size = Pt(146/lenname)
if homeplayers[i]['Suspension']:
tables[0].rows[i+2].cells[1].paragraphs[0].runs[0].font.strike = True
tables[16].rows[i+2].cells[1].paragraphs[0].runs[0].font.strike = True
tables[8].rows[0].cells[0].paragraphs[0].runs[0].text = awayinfo[0]['TeamName']
tables[8].rows[0].cells[4].paragraphs[0].runs[1].text += awayinfo[0]['KitColor']
tables[16].rows[0].cells[6].paragraphs[0].runs[0].text = awayinfo[0]['TeamName']
tables[16].rows[0].cells[9].paragraphs[0].runs[1].text += awayinfo[0]['KitColor']
if len(awayleaders) > 0:
tables[8].rows[32].cells[2].paragraphs[0].runs[0].text = awayleaders[0]['Name']
tables[8].rows[32].cells[5].paragraphs[0].runs[0].text = awayleaders[1]['Name']
lenac = len(tables[8].rows[0].cells[4].paragraphs[0].runs[0].text) + len(tables[8].rows[0].cells[4].paragraphs[0].runs[1].text)
if lenac >= 10:
tables[8].rows[0].cells[4].paragraphs[0].runs[0].font.size = Pt(110/lenac)
tables[8].rows[0].cells[4].paragraphs[0].runs[1].font.size = Pt(110/lenac)
tables[16].rows[0].cells[9].paragraphs[0].runs[0].font.size = Pt(110/lenac)
tables[16].rows[0].cells[9].paragraphs[0].runs[1].font.size = Pt(110/lenac)
for i in range(len(awayplayers)):
tables[8].rows[i+2].cells[0].paragraphs[0].runs[0].text = str(awayplayers[i]['KitNumber'])
tables[16].rows[i+2].cells[6].paragraphs[0].runs[0].text = str(awayplayers[i]['KitNumber'])
extra = ""
if re.search('校友', awayplayers[i]['ExtraInfo']):
extra += "校友"
if re.search('教工', awayplayers[i]['ExtraInfo']):
extra += "教工"
if re.search('足特', awayplayers[i]['ExtraInfo']):
extra += "足特"
if re.search('队长', awayplayers[i]['ExtraInfo']):
extra += "C"
if not extra == "":
tables[8].rows[i+2].cells[1].paragraphs[0].runs[0].text = awayplayers[i]['Name'] + '(' + extra + ')'
tables[16].rows[i+2].cells[7].paragraphs[0].runs[0].text = awayplayers[i]['Name'] + '(' + extra + ')'
else:
tables[8].rows[i+2].cells[1].paragraphs[0].runs[0].text = awayplayers[i]['Name']
tables[16].rows[i+2].cells[7].paragraphs[0].runs[0].text = awayplayers[i]['Name']
lenname = len(tables[8].rows[i+2].cells[1].paragraphs[0].runs[0].text)
utf8_lenname = len(tables[8].rows[i+2].cells[1].paragraphs[0].runs[0].text.encode('utf-8'))
lenname = (utf8_lenname - lenname) / 2 * 1.5 + lenname
if lenname >= 16:
tables[8].rows[i+2].cells[1].paragraphs[0].runs[0].font.size = Pt(146/lenname)
tables[16].rows[i+2].cells[7].paragraphs[0].runs[0].font.size = Pt(146/lenname)
if awayplayers[i]['Suspension']:
tables[8].rows[i+2].cells[1].paragraphs[0].runs[0].font.strike = True
tables[16].rows[i+2].cells[7].paragraphs[0].runs[0].font.strike = True
fname = '/var/www/TUFA/sheets/' + TeamDict.getabbr(homeinfo[0]['TeamName']) + TeamDict.getabbr(awayinfo[0]['TeamName']) + 'sheet.docx'
doc.save(fname)
return fname,fname[21:]
def main(argv):
if len(argv) == 1:
argv.append(False)
ans = TeamDict.getfull(argv[0], argv[1])
print(ans)