def leaderboard_team_spm(): db = getCloudSQL() cursor = db.cursor() #-------------------------------------------------- # Gets number of squares each user is on that has # points for them #-------------------------------------------------- cursor.execute("""SELECT g.team, CAST( SUM(IF(g.item is NULL, g.level, 20 * g.level)) AS UNSIGNED) AS spm FROM {0}.{1} AS g GROUP BY g.team ORDER BY spm DESC ;""".format(CLOUDSQL_DB, GRID_TABLE)) leaderboard = cursor.fetchall() cursor.close() return leaderboard
def getGrid(nw_lat, nw_lng, se_lat, se_lng): #---------------------------------------------------------------- # returns entire map within given bounds #---------------------------------------------------------------- db = getCloudSQL() cursor = db.cursor() cursor.execute( """SELECT nw_lat, nw_lng, team, level, item FROM {0}.{1} WHERE nw_lat >= %s and nw_lat < %s and nw_lng <= %s and nw_lng > %s ;""".format(CLOUDSQL_DB, GRID_TABLE), (se_lat, nw_lat + LAT_SCALE, se_lng, nw_lng - LNG_SCALE)) grid = [] for row in cursor.fetchall(): square = [] square.append(str(row[0])) square.append(str(row[1])) square.append(row[2]) if row[3] == None: square.append(0) else: square.append(row[3]) if row[4] is not None: square.append(row[4]) grid.append(square) cursor.close() return grid
def get_current_captures(user_id, team): #---------------------------------------------------------------- # Get the amount of squares the user is on that is controlled # by his team #--------------------------------------------------------------- db = getCloudSQL() cursor = db.cursor() cursor.execute("""SELECT SUM(team = %s AND (stack1 = %s or stack2 = %s or stack3 = %s or stack4 = %s)) AS c from {0}.{1};""".format(CLOUDSQL_DB, GRID_TABLE), (team, user_id, user_id, user_id, user_id)) row = cursor.fetchone() cursor.close() if row[0] == None: return 0 else: return int(row[0])
def leaderboard_spm(whitelist=None): fb_id_where = "" if whitelist is not None: for fb_id in whitelist: fb_id_where += "'{0}',".format(fb_id) fb_id_where = " WHERE u.fb_id IN (" + fb_id_where[:-1] + ")" db = getCloudSQL() cursor = db.cursor() #-------------------------------------------------- # Gets number of squares each user is on that has # points for them #-------------------------------------------------- cursor.execute("""SELECT u.name, u.fb_id, u.team, CAST( SUM( IF(g._id IS NULL, 0, IF(g.item IS NULL, 1, 20))) AS UNSIGNED) AS spm FROM {0}.{1} AS u LEFT JOIN {0}.{2} AS g ON (u._id in (g.stack1, g.stack2, g.stack3, g.stack4)) AND g.team=u.team AND g.level > 0 {3} GROUP BY u.name ORDER BY spm DESC ;""".format(CLOUDSQL_DB, USER_TABLE, GRID_TABLE, str(fb_id_where))) leaderboard = cursor.fetchall() cursor.close() return list(leaderboard)[0:100]
def is_username_taken(name): db = getCloudSQL() cursor = db.cursor() cursor.execute ("""SELECT name FROM {0}.{1} ;""".format(CLOUDSQL_DB, USER_TABLE)) for row in cursor.fetchall(): if name.lower() == row[0].lower(): cursor.close() return True return False
def createUser(name, team, email, fb_id): db = getCloudSQL() cursor = db.cursor() cursor.execute ("""INSERT INTO {0}.{1} (name, team, email, fb_id) VALUES (%s, %s, %s, %s);""".format(CLOUDSQL_DB, USER_TABLE), (name, team.lower(), email.lower(), fb_id)) db.commit() cursor.close()
def leaderboard_current_captures(whitelist=None): fb_id_where = "" if whitelist is not None: for fb_id in whitelist: fb_id_where += "'{0}',".format(fb_id) fb_id_where = " WHERE u.fb_id IN (" + fb_id_where[:-1] + ")" db = getCloudSQL() cursor = db.cursor() #-------------------------------------------------- # Gets number of squares each user is on that has # points for them #-------------------------------------------------- cursor.execute("""SELECT u.name, u.fb_id, u.team, CAST(IFNULL(COUNT(gs.stack1 OR gs.stack2 OR gs.stack3 OR gs.stack4), 0) AS UNSIGNED) AS captures FROM {0}.{1} AS u LEFT JOIN {0}.{2} AS gs ON (gs.stack1=u._id OR gs.stack2=u._id OR gs.stack3=u._id OR gs.stack4=u._id) AND gs.team=u.team AND gs.level > 0 {3} GROUP BY u.name ORDER BY captures DESC LIMIT 100 ;""".format(CLOUDSQL_DB, USER_TABLE, GRID_TABLE, str(fb_id_where))) leaderboard = cursor.fetchall() cursor.close() return leaderboard
def getUser(id_type, value): #---------------------------------------------------------------- # If lookup type is string, lookup via name. If not, then lookup # via id #---------------------------------------------------------------- db = getCloudSQL() cursor = db.cursor() cursor.execute("""SELECT u._id, u.fb_id, u.name, u.team, CAST( SUM( IF(g._id IS NULL, 0, IF(g.item IS NULL, 1, 20))) AS UNSIGNED) AS spm, u.email FROM {0}.{1} AS u LEFT JOIN {0}.{2} AS g ON (u._id in (g.stack1, g.stack2, g.stack3, g.stack4)) AND g.team=u.team AND g.level > 0 WHERE {3} = %s GROUP BY u._id ;""".format(CLOUDSQL_DB, USER_TABLE, GRID_TABLE, id_type), (str(value),)) row = cursor.fetchone() cursor.close() #---------------------------------------------------------------- # Return None if user doesn't exist #---------------------------------------------------------------- if (row is None): return None #---------------------------------------------------------------- # Turn db data into user object and return #---------------------------------------------------------------- return User(*row)
def update_local_token(fb_id, userAccessToken): #---------------------------------------------------------------- # Uodate local DB for access token for this facebook ID #---------------------------------------------------------------- db = getCloudSQL() cursor = db.cursor() cursor.execute( """REPLACE INTO {0}.{1} (fb_id, accessToken) VALUES (%s, %s);""".format(CLOUDSQL_DB, ACCESS_TOKEN_TABLE), (fb_id, userAccessToken)) cursor.close() db.commit()
def update(self): #---------------------------------------------------------------- # Updates the values of the team and level depending on the # objects current variables #---------------------------------------------------------------- db = getCloudSQL() cursor = db.cursor() cursor.execute ("""UPDATE {0}.{1} SET team = %s, email = %s, name = %s WHERE _id = %s""".format(CLOUDSQL_DB, USER_TABLE), (self.team, self.email, self.name, self._id)) #---------------------------------------------------------------- # Update items in items table if it was ever retrieved #---------------------------------------------------------------- if self.items != None: if len(self.items) > 0: values = "" for item in self.items.keys(): values += "({0}, '{1}', {2}),".format(self._id, item, self.items[item]) values = values[:-1] cursor.execute ("""REPLACE INTO {0}.{1} (user_id, item, quantity) VALUES {2} """.format(CLOUDSQL_DB, USER_ITEMS_TABLE, values)) else: cursor.execute ("""DELETE FROM {0}.{1} WHERE {1}.user_id = {2} """.format(CLOUDSQL_DB, USER_ITEMS_TABLE, self._id)) cursor.close() db.commit()
def least_populous_team(): #---------------------------------------------------------------- # Find the team which has least members # (used for new users to assign a team) #---------------------------------------------------------------- db = getCloudSQL() cursor = db.cursor() cursor.execute("""SELECT SUM(team = 'red') AS red, SUM(team = 'green') AS green, SUM(team = 'blue') AS blue from {0}.{1};""".format(CLOUDSQL_DB, USER_TABLE)) row = cursor.fetchone() cursor.close() return TEAMS[row.index(min(row))]
def get_user_items(user_id): #---------------------------------------------------------------- # Get user items from items table #---------------------------------------------------------------- db = getCloudSQL() cursor = db.cursor() cursor.execute("""SELECT item, quantity FROM {0}.{1} WHERE user_id = %s ;""".format(CLOUDSQL_DB, USER_ITEMS_TABLE), (user_id, )) items = {} for row in cursor.fetchall(): items[row[0]] = row[1] cursor.close() return items
def update(self): #---------------------------------------------------------------- # Updates the db values of the team and level depending on the # objects current variables #---------------------------------------------------------------- db = getCloudSQL() cursor = db.cursor() #---------------------------------------------------------------- # Fill rest of list with None values for database entry #---------------------------------------------------------------- s = [] for user in self.stack: s.append(user._id) while len(s) < 4: s.append(None) update_count = "" if self.changed: update_count = ", update_count = update_count + 1 " cursor.execute( """UPDATE {0}.{1} SET team = %s, level = %s, stack1 = %s, stack2 = %s, stack3 = %s, stack4 = %s, updated = NOW() {2} WHERE _id = %s ;""".format(CLOUDSQL_DB, GRID_TABLE, update_count), (self.team, self.level, s[0], s[1], s[2], s[3], self._id)) cursor.close() db.commit()
def get_local_token(fb_id): #---------------------------------------------------------------- # Check local Database to see if an accesstoken has been # kept for this Facebook ID #---------------------------------------------------------------- db = getCloudSQL() cursor = db.cursor() cursor.execute( """SELECT accessToken FROM {0}.{1} WHERE fb_id = %s ;""".format(CLOUDSQL_DB, ACCESS_TOKEN_TABLE), (fb_id, )) row = cursor.fetchone() cursor.close() if row == None: return None else: return row[0]
def leaderboard_team_score(timescale): if timescale is not None and timescale.upper() in ["HOUR", "DAY", "WEEK", "MONTH"]: t = "DATE_SUB(NOW(), INTERVAL 1 {0})".format(timescale) else: t = "'2000-01-01 00:00:00'" db = getCloudSQL() cursor = db.cursor() cursor.execute("""SELECT u.team, CAST(SUM(IF(s.time > {0}, IFNULL(s.points, 0), 0)) AS UNSIGNED) AS total FROM {1}.{2} AS s LEFT JOIN {1}.{3} AS u ON s.user_id = u._id GROUP BY u.team ORDER BY total DESC ;""".format(t, CLOUDSQL_DB, SCORE_TABLE, USER_TABLE)) leaderboard = cursor.fetchall() cursor.close() return leaderboard
def verify_and_get_user(fb_id, userAccessToken): #---------------------------------------------- # For testing purposes, not used in prod #---------------------------------------------- #if userAccessToken == "sam": # return [True, getUser("fb_id", fb_id)] #---------------------------------------------- # Check local accesstoken for match #---------------------------------------------- db = getCloudSQL() cursor = db.cursor() cursor.execute("""SELECT u._id, u.fb_id, u.name, u.team, u.email, CAST( SUM( IF(g._id IS NULL, 0, IF(g.item IS NULL, 1, 20))) AS UNSIGNED) AS spm, u.accessToken FROM (SELECT iu._id, iu.fb_id, iu.name, iu.team, iu.email, at.accessToken FROM {0}.{1} AS iu LEFT JOIN {0}.{2} AS at ON iu.fb_id = at.fb_id WHERE iu.fb_id = %s ) AS u LEFT JOIN {0}.{3} AS g ON (u._id in (g.stack1, g.stack2, g.stack3, g.stack4)) AND g.team = u.team AND g.level > 0 GROUP BY u._id ;""".format(CLOUDSQL_DB, USER_TABLE, ACCESS_TOKEN_TABLE, GRID_TABLE), (fb_id,)) row = cursor.fetchone() cursor.close() if row == None: return [verify_token_with_facebook(fb_id, userAccessToken), None] _id = row[0] fb_id = row[1] name = row[2] team = row[3] email = row[4] spm = row[5] accessToken = row[6] user = User(_id, fb_id, name, team, spm, email) #---------------------------------------------- # Check local acccess token #---------------------------------------------- if userAccessToken == accessToken: return [True, user] #---------------------------------------------- # If no match check with facebook if valid #---------------------------------------------- if verify_token_with_facebook(fb_id, userAccessToken): #---------------------------------------------- # Update local value #---------------------------------------------- update_local_token(fb_id, userAccessToken) return [True, user] return [False, user]
def leaderboard_score(timescale, whitelist=None): #-------------------------------------------------- # Get leaderboard for score #-------------------------------------------------- #-------------------------------------------------- # IF whitelist given, only query Facebook values from # this list #-------------------------------------------------- fb_id_where = "" if whitelist is not None: values = "'010101'," for fb_id in whitelist: values += "'{0}',".format(fb_id) fb_id_where = " WHERE u.fb_id IN (" + values[:-1] + ")" #-------------------------------------------------- # If timescale given, only query for score # within this time #-------------------------------------------------- if timescale is not None and timescale.upper() in ["HOUR", "DAY", "WEEK", "MONTH"]: t = "DATE_SUB(NOW(), INTERVAL 1 {0})".format(timescale) else: t = "'2000-01-01 00:00:00'" db = getCloudSQL() cursor = db.cursor() cursor.execute("""SELECT u.name, u.fb_id, u.team, CAST(SUM(IF(s.time > {3}, IFNULL(s.points, 0), 0)) AS UNSIGNED) AS total FROM {0}.{1} AS u LEFT JOIN {0}.{2} AS s ON u._id = s.user_id {4} GROUP BY u.name ORDER BY total DESC ;""".format(CLOUDSQL_DB, USER_TABLE, SCORE_TABLE, t, fb_id_where)) leaderboard = cursor.fetchall() cursor.close() return list(leaderboard)[0:100]
def getGridSquare(lat, lng): #---------------------------------------------------------------- # returns a gridsquare object that the coordinates lay within #---------------------------------------------------------------- db = getCloudSQL() cursor = db.cursor() cursor.execute( """select gs._id, gs.nw_lat, gs.nw_lng, gs.team, gs.level, gs.stack1, gs.stack2, gs.stack3, gs.stack4, gs.item, u._id, u.fb_id, u.name, u.team FROM {0}.{1} AS gs LEFT JOIN {0}.{2} AS u ON gs.stack1 = u._id OR gs.stack2 = u._id OR gs.stack3 = u._id OR gs.stack4 = u._id WHERE nw_lat >= %s and nw_lat < %s and nw_lng <= %s and nw_lng > %s ;""".format(CLOUDSQL_DB, GRID_TABLE, USER_TABLE), (lat, lat + LAT_SCALE, lng, lng - LNG_SCALE)) #---------------------------------------------------------------- # Return None if no rows found #---------------------------------------------------------------- if cursor.rowcount < 1: return None stack = [None, None, None, None] for row in cursor.fetchall(): [ grid_id, nw_lat, nw_lng, square_team, level, stack1, stack2, stack3, stack4, item, user_id, fb_id, name, user_team ] = row #---------------------------------------------------------------- # Build stack from db values #---------------------------------------------------------------- if user_id is not None: if user_id == stack1: index = 0 elif user_id == stack2: index = 1 elif user_id == stack3: index = 2 elif user_id == stack4: index = 3 stack[index] = User(user_id, fb_id, name, user_team) cursor.close() while None in stack: stack.remove(None) return GridSquare(grid_id, nw_lat, nw_lng, square_team, level, stack, item)