Eksempler
>FotballVM
Fotball VM
Løsningen er prinsippielt slik:
Dataflyt fotball VM
Javascript
Javascriptkoden som kjører på klienten bestiller en turneringsbeskrivelse eller et grupperesultat, tar i mot ferdig formatert tekst og plasserer denne teksten som "innerHTML". Skriptet er slik:
// relies on jquery.js
var currentYear='2006';
var scriptpath=
'http://www.it.hiof.no/~borres/cgi-bin/ajax/fifa/vmscript.py';
function getIt(targetNodeId,params)
{
$.ajax({
url:scriptpath,
data:params,
success:function(data)
{
$('#'+targetNodeId).html(data);
},
error:function(data)
{
$('#'+targetNodeId).html("Could not access content");
}
});
}
function startCountryRequest()
{
params='what=tournaments';
getIt('countrylist',params);
}
function startTournamentRequest(year)
{
currentYear=year;
params='year='+year;
getIt('tournament',params);
}
function startGroupRequest(gruppe)
{
params='gruppe='+gruppe+'&year='+currentYear;
getIt('gruppe',params);
}
Serverskriptet
Serverskriptet tolker forespørselen fra Javascriptkoden, slår opp i databasen, formaterer dataene i HTML-fragmenter og returnerer bestillingen. Skriptet er skrevet i Python og ser slik ut:
#! /usr/bin/python2.5
import MySQLdb,cgi
"""
Retrieve fotball data from MySql-base fotball
The return values ar either a complete webpage or a fragment suited for Ajax
See comments at the end of each method for adjustments
Produce result in file RESULT_FILE if we want to make a complete page
The HTML(fragments) uses some classes for styles, see fragments below
Used from: http://www.it.hiof.no/~borres/ml/fotballvm/index.html
"""
#-------------------------------------------------
# If we want to do some testing and write to a file
#-------------------------------------------------
RESULT_FILE='c:\\projects\\pydb\\dbaccessresultvm.html'
#-------------------------------------------------
# HTML-page if we want complete page return
#-------------------------------------------------
HTMLPAGE="""<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<link href="fotballstyles.css" rel="STYLESHEET" />
<title>dbaccessvm</title>
</head>
<body>
<h1>Results</h1>
%s
</body>
</html>
"""
#-------------------------------------------------
# SQL-statements
#-------------------------------------------------
SQL_ALL_COUNTRIES="""SELECT lagA,lagB FROM vwkamper;"""
SQL_ALL_COUNTRIES_IN_TOURNAMENT="""SELECT lagA,lagB
FROM vmkamper WHERE aar='%s';"""
SQL_ALL_MATCHES_IN_TOURNAMENT="""SELECT * FROM vmkamper WHERE aar='%s';"""
SQL_ALL_MATCHES_IN_TOURNAMENT_GROUP="""SELECT * FROM vmkamper
WHERE aar='%s' AND gruppe='%s';"""
SQL_ONE_TOURNAMENT="""SELECT * FROM vm WHERE aar='%s';"""
SQL_ALL_TOURNAMENTS="""SELECT aar,land FROM vm;"""
#-------------------------------------------------
# HTML-fragments serving as templates
#-------------------------------------------------
HTML_COUNTRY_LIST_FRAME="""<div class="country_list">
%s
</div>
"""
HTML_TEAM_LIST_FRAME="""<ul>
%s
</ul>
"""
HTML_TEAM_ROW="""<tr>
<td class="table_team">%s</td>
<td class="table_matchcount">%s</td>
<td class="table_matchcount">%s</td>
<td class="table_matchcount">%s</td>
<td class="table_matchcount">%s</td>
<td class="table_score">%s</td>
<td class="table_score">%s</td>
<td class="table_points">%s</td>
</tr>
"""
HTML_DESCRIPTION="""<div class="description">
%s
</div>
"""
HTML_TOURNAMENT_HEADER="""
<div>
<h3>%s - %s</h3>
<p>%s</p>
<form action="">
<div style="font-weight:bold">Velg resultater</div>
<select name="grupper" onchange="startGroupRequest(value);return false;">
<option value="lagliste">Alle lag</option>
%s
</select>
</form>
<div id="gruppe">
<!-- lagliste -->
</div>
</div>
"""
HTML_TABLE_FRAME="""<table>
%s
</table>
"""
HTML_MATCH_LINE="""<tr>
<td class="match_date">%s</td>
<td class="match_team">%s</td>
<td class="match_team">%s</td>
<td class="match_score">%s</td>
<td class="match_score">%s</td>
<td class="match_score">%s</td>
<td class="match_score">%s</td>
</tr>
"""
HTML_TOURNAMENT_SELECTOR_LINE="""
<div class="tournament"
onclick="javascript:startTournamentRequest('%s');return false;">%s %s</div>
"""
#-------------------------------------------------
# when database access goes wrong
#-------------------------------------------------
ERROR_RESULT='XXX'
ERROR_FLAG=False
def raiseErrorFlag():
global errorFlag
ERROR_FLAG=True
#-------------------------------------------------
# simply write file, for testing
#-------------------------------------------------
def storeTextFile(filename,txt):
try:
outfile=open(filename,'w')
outfile.write(txt)
outfile.close()
except:
print 'Error writing file ',filename
#-------------------------------------------------
# connect and execute a sql-request
#-------------------------------------------------
def connectAndExecute(sql):
try:
myBase=MySQLdb.connect(host='frigg.hiof.no',
user='student',
passwd='student',
db='fotball')
myTab=myBase.cursor()
myTab.execute(sql)
myBase.close()
return myTab.fetchall()
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
return None
#-------------------------------------------------
# get all available tournaments
# return as a page fragment with div-lines
#-------------------------------------------------
def makeTournaments():
resultTxt=''
result=connectAndExecute(SQL_ALL_TOURNAMENTS)
if result==None:
return 'ingen turneringer funnet'
for row in result:
resultTxt+=HTML_TOURNAMENT_SELECTOR_LINE%(row[0],row[0],row[1])
# return as fragment
return resultTxt
# returnas complete page
#return HTMLPAGE%resultTxt
#-------------------------------------------------
# get all available countries in a tournament
# return as page,fragment or line, see bottom of method
#-------------------------------------------------
def makeCountryList(Y):
resultTxt=''
result=connectAndExecute(SQL_ALL_COUNTRIES_IN_TOURNAMENT%Y)
if result==None:
return 'ingen land funnet'
reslist=[]
for row in result:
row0str=str(row[0])
row1str=str(row[1])
if not row0str in reslist:
reslist.append(row0str)
if not row1str in reslist:
reslist.append(row1str)
reslist.sort()
for r in reslist:
resultTxt+=r+' , ';
# return simple lines:
#return resultTxt[0:len(resultTxt)-1].replace(' , ','\n')
# return fragment wrapped in a div-element
resultTxt=HTML_COUNTRY_LIST_FRAME%resultTxt[0:len(resultTxt)-2]
return resultTxt
# complete page return
#return HTMLPAGE%resultTxt
#-------------------------------------------------
# get all available matches in a group in a tournament
# return as page,fragment or lines, see bottom of method
#-------------------------------------------------
def makeGroupMatches(Y,G):
tableTxt=''
resultTxt=''
tabResultTxt=''
result=connectAndExecute(SQL_ALL_MATCHES_IN_TOURNAMENT_GROUP%(Y,G))
if result==None:
return 'ingen kamper funnet'
# make result table of this groups result
if G.lower().find('gruppe')!= -1:
tableTxt=makeTable(result,Y)
for row in result:
t=str(row[3])
dag=t[8:10]+'/'+t[5:7]
lagA=str(row[5])
lagB=str(row[6])
scoreA=str(row[7])
scoreB=str(row[8])
scoreAx=str(row[9])
scoreBx=str(row[10])
scoreAp=str(row[11])
scoreBp=str(row[12])
resultTxt+=dag+' '+lagA+' - '+lagB+' '+scoreA+':'+scoreB
if (scoreAx != '0') or \
(scoreBx != '0') or \
(scoreAp != '0') or \
(scoreBp != '0'):
resultTxt+='(eo. '+scoreAx+':'+scoreBx+' )'
if (scoreAp != '0') or (scoreBp != '0'):
resultTxt+='(str. '+scoreAp+':'+scoreBp+' )'
resultTxt+='\n'
if(scoreAp != '0') or (scoreBp != '0'):
tabResultTxt+=HTML_MATCH_LINE%\
(dag,lagA,lagB,scoreA,scoreB,'(eo.'+scoreAx+':'+scoreBx+')','(st.'+scoreAp+':'+scoreBp+')')
elif(scoreAx != '0') or (scoreBx != '0'):
tabResultTxt+=HTML_MATCH_LINE%\
(dag,lagA,lagB,scoreA,scoreB,'(eo.'+scoreAx+':'+scoreBx+')','')
else:
tabResultTxt+=HTML_MATCH_LINE%\
(dag,lagA,lagB,scoreA,scoreB,'','')
# simple lines return:
#print resultTxt
#return resultTxt[0:len(resultTxt)-1]
# fragment as a table pluss result table
tabResultTxt=tableTxt+HTML_TABLE_FRAME%tabResultTxt
return tabResultTxt
# complete page return
#return HTMLPAGE%tableTxt+tabResultTxt
#-------------------------------------------------
# prepare the headerinformation for a tournament
#-------------------------------------------------
def makeTournamentHeader(Y):
resultTxt=''
result=connectAndExecute(SQL_ONE_TOURNAMENT%Y)
if result==None:
return 'ingen informasjon funnet'
year=Y
country=str(result[0][1])
description=str(result[0][2])
groups=str(result[0][3])
# do the groups
groupTxt=''
grps=groups.split(';')
for g in grps:
groupTxt+='<option value="%s">%s</option>'%(g,g)+'\n'
# do the description
descriptionTxt=HTML_DESCRIPTION%description
# merge it
resultTxt=HTML_TOURNAMENT_HEADER%\
(year,country,descriptionTxt,groupTxt)
return resultTxt;
# complete page return
#return HTMLPAGE%ResultTxt
#-------------------------------------------------
# make a table of a groups results
# input result is from database, all matches in group
# return as page or fragment, see comments at end of method
#-------------------------------------------------
def makeTable(result,year):
# points for victory
if int(year) < 1987:
wp=2
else:
wp=3
# get all results
# pick up teams
teams=[]
allList=[]
for row in result:
aTeam=str(row[5])
bTeam=str(row[6])
if not aTeam in teams:
teams.append(aTeam)
if not bTeam in teams:
teams.append(bTeam)
# calculate each teams result row
for team in teams:
ownScore=0
oppScore=0
winCount=0
drawCount=0
looseCount=0
for row in result:
hTeam=str(row[5])
aTeam=str(row[6])
hScore=int(str(row[7]))+int(str(row[9]))+int(str(row[11]))
aScore=int(str(row[8]))+int(str(row[10]))+int(str(row[12]))
if hTeam == team:
ownScore=ownScore+hScore
oppScore=oppScore+aScore
if hScore > aScore:
winCount=winCount+1
elif hScore==aScore:
drawCount=drawCount+1
else:
looseCount=looseCount+1
if aTeam == team:
ownScore=ownScore+aScore
oppScore=oppScore+hScore
if aScore > hScore:
winCount=winCount+1
elif hScore==aScore:
drawCount=drawCount+1
else:
looseCount=looseCount+1
# finish this team
allList.append([team,winCount+drawCount+looseCount,
winCount,drawCount,looseCount,
ownScore,oppScore,wp*winCount+drawCount])
# sort teamrows
allList.sort(compareResults)
# prepare table rows
resultTxt=''
for tl in allList:
resultTxt+=HTML_TEAM_ROW%\
(tl[0],tl[1],tl[2],tl[3],tl[4],tl[5],tl[6],tl[7])
resultTxt=HTML_TABLE_FRAME%resultTxt
# Only prepared table, suitable for AJAX return:
return resultTxt;
# complete page return
#return HTMLPAGE%(resultTxt)
#-------------------------------------------------
# comparison for sorting table rows in makeTable
#-------------------------------------------------
def compareResults(a,b):
# a and b are lists, three last
# entries are homegolas, awaygoals and points
# which will be criterium
if a[len(a)-1] < b[len(b)-1]:
return 1
elif a[len(a)-1] > b[len(b)-1]:
return -1
# equal points, must check goals
a_goaldiff=a[len(a)-3]-a[len(a)-2]
b_goaldiff=b[len(b)-3]-b[len(b)-2]
if a_goaldiff < b_goaldiff:
return 1
elif a_goaldiff > b_goaldiff:
return -1
# equal diff, must check scored
if a[len(a)-3] < b[len(b)-3]:
return 1
return -1
#-------------------------------------------------
# for testing off server
#-------------------------------------------------
def doit(year='1930',gruppe='Gruppe A'):
#RESULT_TXT=makeCountryList(year)
#RESULT_TXT=makeGroupMatches(year,gruppe)
RESULT_TXT=makeTournamentHeader(year)
storeTextFile(RESULT_FILE,RESULT_TXT)
#-------------------------------------------------
# what is the job ?
#-------------------------------------------------
form=cgi.FieldStorage()
print 'Content-type: text/html; charset=utf-8\n'
RESULT_TXT=''
if form.has_key('year'):
year=form['year'].value
if form.has_key('gruppe'):
gruppe=form['gruppe'].value
if gruppe.find('lagliste')!= -1:
RESULT_TXT=makeCountryList(year)
else:
RESULT_TXT=makeGroupMatches(year,gruppe)
else:
RESULT_TXT=makeTournamentHeader(year)
RESULT_TXT=RESULT_TXT.replace('<!-- lagliste -->',
makeCountryList(year))
print RESULT_TXT
elif form.has_key('what'):
RESULT_TXT=makeTournaments()
print RESULT_TXT
else:
print 'Feil: ingen kjent kommando'
Stilsett
Det er benyttet et enkelt stilsett (css):
.tournament{color:blue;cursor:pointer;margin-right:20px}
.description{margin:20px}
.country{color:blue}
.country_id{color:green}
.country_list{color:green;font-size:14px;margin:20px}
.match_date,.match_team,.match_score{font-size:13px}
.match_date{color:gray;padding-left:10px;padding-right:20px}
.match_team{color:black;padding-left:10px}
.match_score{color:red;padding-left:5px;}
.table_team,.table_matchcount,.table_score,.table_point{font-size:13px}
.table_team{padding-right:10px;color:black;font-weight:bold}
.table_matchcount{color:gray}
.table_score{color:gray}
.table_points{padding-left:10px;color:black;font-weight:bold}
