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)
