def numOfGamesWon(self):
'Calculate the number of games which I have won'
sql = """SELECT player.gameID FROM player
INNER JOIN(SELECT MAX(score) AS maxScore, gameID FROM player GROUP BY gameID) AS winner
ON player.gameID=winner.gameID AND winner.maxScore=player.score
WHERE player.pupilID=%d;""" % self.pupilID
return len(getFromDatabase(sql))
def wordTable(self):
'Convert all my words into HTML table'
wordList = getFromDatabase(
"""SELECT word.word, playerWord.legitimate FROM word INNER JOIN playerWord
ON playerWord.wordID = word.wordID WHERE playerWord.pupilID=%d
AND playerWord.gameID=%d ORDER BY word.word;"""
% (self.pupilID, self.gameID))
myWords = self.allWords()
otherWords = self.otherPlayersWords()
table = "<table class='wordList'>"
for i in range(len(wordList)):
if i % 2 == 0:
table += "<tr>"
score = self.wordScore(wordList[i], wordList, otherWords)
table += "<td class='wordCell" + (
" onlyMine" if wordList[i][0] not in otherWords else
"") + "'>" + wordList[i][0] + "</td>"
table += "<td class='wordCell'>%d</td>" % score
if i % 2 == 1 or i == len(wordList) - 1:
table += "</tr>"
table += "</table>"
return table
def players(self):
'Find all players with my pupilID'
result = getFromDatabase(
"SELECT pupilID, gameID FROM player WHERE pupilID=%d;" %
self.pupilID)
playerList = [Player(row[0], row[1], True) for row in result]
return playerList
def allWords(self):
'Return all the words which I found'
wordList = getFromDatabase(
"""SELECT word.word, playerWord.legitimate FROM word INNER JOIN playerWord
ON word.wordID=playerWord.wordID WHERE playerWord.pupilID=%d
AND playerWord.gameID=%d ORDER BY word.word;"""
% (self.pupilID, self.gameID))
return wordList
def otherPlayersWords(self):
'Return all legitimate words found by other players, including ones which I also found'
otherWords = getFromDatabase(
"""SELECT word.word FROM word INNER JOIN playerWord on playerWord.wordID=word.wordID
WHERE playerWord.gameID=%d AND playerWord.pupilID!=%d
AND playerWord.legitimate='True';""" %
(self.gameID, self.pupilID))
return [item[0] for item in otherWords]
def __init__(self,
username=None,
forename=None,
surname=None,
pupilID=None):
"""Constructor for pupil. Pass pupilID or username explicitly when reading existing user from database.
Otherwise pass all parameters explicitly if creating new pupil"""
if username and forename and surname: #Create new Pupil from cgi.FieldStorage
self.waitingForGame = False
self.pupilID = pupilID #None, but added for integrity of objects
self.lastSeen = 0
super(Pupil, self).__init__(forename, surname, username)
else: #Create Pupil from database
if username:
sql = "SELECT pupilID FROM pupil WHERE username='******';" % username
if existsInDatabase(sql):
result = getFromDatabase(sql)[0][0]
self.pupilID = result
else:
raise Exception("User not found")
else:
if pupilID:
self.pupilID = pupilID
result = getFromDatabase(
"""SELECT forename, surname, username, waitingForGame
FROM pupil WHERE pupilID=%d;""" %
self.pupilID)
if len(result) > 0:
dbPupil = result[0]
self.waitingForGame = dbPupil[3] == "True"
super(Pupil, self).__init__(dbPupil[0], dbPupil[1], dbPupil[2])
else:
raise Exception("User not found")
def __init__(self,
email=None,
username=None,
forename=None,
surname=None,
teacherID=None):
"""Constructor for teacher. Pass username explicitly if reading from database.
If not, pass all parameters explicitly"""
if email and username and forename and surname: #Create new Teacher from cgi.FieldStorage
self.email = email
self.teacherID = teacherID #None, but added for integrity of objects
super(Teacher, self).__init__(forename, surname, username)
else: #Create Teacher from databse
if username:
sql = "SELECT teacherID FROM teacher WHERE username='******';" % username
if existsInDatabase(sql):
result = getFromDatabase(sql)[0][0]
self.teacherID = result
else:
raise Exception("User not found")
else:
if teacherID:
self.teacherID = teacherID
sql = """SELECT email, forename, surname, username
FROM teacher WHERE teacherID=%s;""" % self.teacherID
if existsInDatabase(sql):
result = getFromDatabase(sql)[0]
self.email = result[0]
super(Teacher, self).__init__(result[1], result[2], result[3])
else:
raise Exception("User not found")
def __init__(self, pupilID, gameID, fromDatabase=False):
"""Constructor for Player.
Pass value which evaluates to True if reading from database, otherwise pass pupilID and gameID only"""
self.pupilID = pupilID
self.gameID = gameID
if fromDatabase:
result = getFromDatabase(
"SELECT submitted, score FROM player WHERE pupilID=%d AND gameID=%d;"
% (pupilID, gameID))[0]
self.submitted = result[0]
self.score = result[1]
else:
self.submitted = False
self.score = None
def gameToHTML(self, teacherID=None):
'Represent player on the viewPupil game'
dateStarted = getFromDatabase(
"SELECT dateStarted FROM game WHERE gameID=%d;" % self.gameID)[0]
if not teacherID is None:
element = "<a href=viewGame.py?gameID=%d><h6>Game %d</h6></a>" % (
self.gameID, self.gameID)
else:
element = "<h6>Game %d</6>" % self.gameID
element += "<p>Date: %s</p>" % dateStarted
element += """<p>Score: %s</p>
<p>Words: </p>""" % (lambda s: "N/A"
if s is None else str(s))(self.score)
element += self.wordTable()
element += "</td>"
return element
cookieString = os.environ.get("HTTP_COOKIE")
cookie = Cookie.SimpleCookie()
cookie.load(cookieString)
if "teacherID" in cookie:
teacherID = int(cookie["teacherID"].value)
else:
raise Exception("A cookie expired")
else:
raise Exception("A cookie expired")
searchBy = post["pupilDataType"].value
searchData = post["pupilSearchData"].value.lower()
result = getFromDatabase("SELECT DISTINCT pupilID FROM pupil \
WHERE LOWER(%s)='%s';" % (searchBy, searchData))
pupilList = [Pupil(pupilID=row[0]) for row in result]
for i in range(len(pupilList)):
if i % 3 == 0:
resultTable += "<tr>"
resultTable += "<td class='objectContainer'>" + pupilList[
i].pupilToHTML(teacherID) + "</td>"
if i % 3 == 2 or i == len(pupilList) - 1:
resultTable += "</tr>"
print page % (teacherMenuBar(), resultTable)
except Exception as e:
#!/usr/bin/python
import cgi, cgitb, sys, MySQLdb, time
cgitb.enable()
sys.path.append("/var/www/cgi-bin")
from boggleUser import Pupil
from boggleGame import getFromDatabase
try:
post = cgi.FieldStorage()
teacherID = post["teacherID"].value
result = getFromDatabase("""SELECT pupil.pupilID FROM pupil, teaches
WHERE teaches.teacherID='{}'
AND pupil.pupilID=teaches.pupilID
AND waitingForGame='True'
AND ({} - lastSeen < 3
OR lastSeen = 0)
ORDER BY pupil.pupilID;""".
format(teacherID, time.time()))
pupils = [Pupil(pupilID=row[0]) for row in result]
pupilsTable = """<tr id='pupilTable'>
<th>Pupil ID</th>
<th>First Name</th>
<th>Surname</th>
<th>Average Score</th>
<th>Select</th>
<th>View Pupil</th>
<tr>"""
for pupil in pupils:
else:
raise Exception("A cookie expired")
else:
raise Exception("You don't have permission to view this page")
dateStarted = post["gameSearchData"].value
sql = """SELECT DISTINCT game.gameID FROM game
INNER JOIN player ON game.gameID=player.gameID
INNER JOIN pupil ON player.pupilID=pupil.pupilID
INNER JOIN teaches ON pupil.pupilID=teaches.pupilID
WHERE game.dateStarted='%s' AND teaches.teacherID=%d;"""
result = getFromDatabase(sql % (dateStarted, teacherID))
gameList = [Game(row[0]) for row in result]
for i in range(len(gameList)):
if i % 3 == 0:
resultTable += "<tr>"
resultTable += "<td class='objectContainer'>" + gameList[i].gameToHTML(
teacherID) + "<p>Players:</p>" + gameList[i].playerTable(
) + "</td>"
if i % 3 == 2 or i == len(gameList) - 1:
resultTable += "</tr>"
print page % (teacherMenuBar(), resultTable)
def findUnsubmittedPlayer(self):
'Find any players with my pupilID who are unsubmitted'
result = getFromDatabase(
"SELECT gameID FROM player WHERE pupilID=%d and submitted='False';"
% self.pupilID)
return result
def save(self):
msg = super(Pupil, self).save()
self.pupilID = getFromDatabase(
"SELECT pupilID FROM pupil WHERE username='******';".format(
self.username))[0][0]
return msg