def get_games_by_search_highest_score(self, numPuzzles, Offset, searchterm,
userID):
db = get_db()
cursor = db.cursor()
games = list()
searchterm = ''.join(('%', searchterm, '%'))
cursor.execute(
"""SELECT g.game_id as GAMEID,g.name,g.type,g.description,g.authorid,g.authorname,g.difficulty,g.uri,g.created,g.plays,(SELECT count(vote_id) from vote WHERE game_id=GAMEID) as votes,(SELECT votedata from vote WHERE game_id=GAMEID and user_id=%s) as hasvoted,MIN(s.numMoves) from game g
JOIN solutions s ON g.game_id = s.gameid
WHERE g.name LIKE %s AND type='type' OR
g.authorname LIKE %s AND type='type'
GROUP BY g.game_id
ORDER BY s.numMoves DESC LIMIT %s OFFSET %s""",
(userID, searchterm, searchterm, numPuzzles, Offset))
query = cursor.fetchall()
if query is not None:
for row in query:
games.append(
Game(row[0], row[1], row[2], row[3], row[4], row[5],
row[6], '', row[7],
(row[8] - timedelta(hours=4)
).strftime('%b %d, %Y %I%p').lstrip("0").replace(
" 0",
" "), row[9], row[10], row[11]).serialize())
return games
else:
return games
def get_game(self, gameid):
cursor = get_db().cursor()
cursor.execute('SELECT * from game WHERE game_id=%s', (gameid, ))
row = cursor.fetchone()
return Game(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8],
(row[9] - timedelta(hours=4)
).strftime('%b %d, %Y %I%p').lstrip("0").replace(
" 0", " "), row[10])
def get_game_uri(self, uri):
row = GameDAO().get_game_uri(uri)
if row is None:
generated = GenDAO().get_game_uri(uri)
if generated is None:
return {'uri': ''}
return Gen(generated[0], generated[1], generated[2], generated[3],
generated[4], generated[5], generated[6]).serialize()
else:
return Game(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], '', row[10]).serialize()
def get_highscores(self, uri, metadata=True):
row = GameDAO().get_game_uri(uri)
game = Game(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7])
highscores = GameDAO().get_highscores(game.id)
if metadata:
newhighscores = list()
for highscore in highscores:
metadata = UserDAO().get_user_metadata(highscore['userid'])
newhighscores.append({**highscore, **metadata})
return newhighscores
else:
return highscores
def get_all_games(self, numGames, offset):
db = get_db()
cursor = db.cursor()
games = list()
cursor.execute(
"SELECT game_id,name,type,description,authorid,authorname,difficulty,uri,created,plays from game WHERE type='type' ORDER BY created DESC LIMIT %s OFFSET %s",
(numGames, offset))
query = cursor.fetchall()
if query is not None:
for row in query:
games.append(
Game(row[0], row[1], row[2], row[3], row[4], row[5],
row[6], '', row[7],
(row[8] - timedelta(hours=4)
).strftime('%b %d, %Y %I%p').lstrip("0").replace(
" 0", " "), row[9]).serialize())
return games
else:
return games
def get_games_by_search(self, numPuzzles, Offset, searchterm, userID):
db = get_db()
cursor = db.cursor()
games = list()
searchterm = ''.join(('%', searchterm, '%'))
cursor.execute(
"SELECT game_id as GAMEID,name,type,description,authorid,authorname,difficulty,uri,created,plays,(SELECT count(vote_id) from vote WHERE game_id=GAMEID) as votes,(SELECT votedata from vote WHERE game_id=GAMEID and user_id=%s) as hasvoted from game WHERE name LIKE %s AND type='type' OR authorname LIKE %s AND type='type' ORDER BY created DESC LIMIT %s OFFSET %s",
(userID, searchterm, searchterm, numPuzzles, Offset))
query = cursor.fetchall()
if query is not None:
for row in query:
games.append(
Game(
row[0], row[1], row[2], row[3], row[4], row[5], row[6],
'', row[7],
row[8].strftime('%b %d, %Y %I%p').lstrip("0").replace(
" 0", " "), row[9], row[10], row[11]).serialize())
return games
else:
return games
def insert_highscore(self, name, userid, authorname, solutiondata,
highscore, uri):
row = GameDAO().get_game_uri(uri)
game = Game(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8])
if checkSolution(solutiondata, game.puzzledata, highscore):
scoreList = GameDAO().get_highscores(game.id)
UpdateUserScore = False
rtnMessage = ""
userSubmitted = False
idtoupdate = None
for Solution in scoreList:
if (Solution['numMoves'] == highscore
and Solution['comment'] == name and userid == 1):
return "Duplicate highscore cannot be submitted."
if (Solution['numMoves'] >= highscore
and Solution['userid'] == userid and userid != 1):
UpdateUserScore = True
idtoupdate = Solution['id']
if (Solution['userid'] == userid):
userSubmitted = True
gameid = game.id
if (UpdateUserScore):
GameDAO().increment_plays(gameid)
return GameDAO().update_highscore(idtoupdate, gameid, name,
userid, authorname,
solutiondata, highscore)
else:
if (not userSubmitted or userid == 1):
GameDAO().increment_plays(gameid)
GameDAO().insert_highscore(gameid, name, userid,
authorname, solutiondata,
highscore)
rtnMessage = "Submitted"
return rtnMessage
else:
return 'not a higher score'
else:
return 'not a valid submission'