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'