def updateTime(username):
'''def updateTime(username): updates the time of the next daily spin of wheel of fortune'''
q="SELECT time FROM user_tbl WHERE username=?"
inputs=(username,)
time=execmany(q,inputs).fetchone()[0]
if(time=="0"):
q="UPDATE user_tbl SET time=? WHERE username=?"
now=datetime.now()+timedelta(days=1)
now=str(now.strftime("%m/%d/%Y %H:%M:%S"))
inputs=(now,username)
execmany(q,inputs)
return now
time=time.split(" ")
time[1]=str(time[1]).split(":")
time[0]=str(time[0]).split("/")
prev=datetime(int(time[0][2]),int(time[0][0]),int(time[0][1]),int(time[1][0]),int(time[1][1]),int(time[1][2]))
now=datetime.now()
if(now>prev):
q="UPDATE user_tbl SET time=? WHERE username=?"
now=datetime.now()+timedelta(days=1)
now=str(now.strftime("%m/%d/%Y %H:%M:%S"))
inputs=(now,username)
execmany(q,inputs)
return now
else:
return 'NONE'
def updateQuestion(username, game_id):
#determine team number
'''def updateQuestion(username, game_id): generate the next question for a team and updates data tables accordingly'''
team = getTeamNum(username, game_id)
q = "SELECT team%s FROM game_tbl WHERE game_id=?" % team
r = "UPDATE game_tbl SET currentq%s=? WHERE game_id=?" % team
s = "UPDATE game_tbl SET team%s=? WHERE game_id=?" % team
#update question number that team is up to
inputs = (game_id, )
data = execmany(q, inputs).fetchone()[0].split(",")
number = int(data[1]) + 1
if number > 10:
completeGame(username, game_id)
return
data[1] = str(number)
data = ",".join(data)
inputs = (data, game_id)
execmany(s, inputs)
#update current question for that team
command = "SELECT * FROM question_tbl ORDER BY random() LIMIT 1"
question = exec(command).fetchone()[1]
inputs = (question, game_id)
execmany(r, inputs)
def purchaseTicket(username, type):
'''def purchaseTicket(username, type): purchase ticket of given type under given username'''
if checkPrice(username, type): #if user can afford ticket
#add to table
id = random.randrange(limit)
q = "SELECT id FROM lottery_tbl WHERE id=?"
inputs = (id, )
data = execmany(q, inputs).fetchone()
while data is not None:
id = random.randrange(limit)
data = execmany(q, inputs).fetchone()
q = "INSERT INTO lottery_tbl VALUES(?, ?, ?, ?, ?)"
id = type + str(id)
num = generateNum()
strnum = ""
for i in range(len(num)):
if i == 11: #last number
strnum += str(num[i])
else:
strnum += str(num[i]) + ","
winnings = calculatePrize(type, num)
inputs = (id, username, strnum, winnings, 0)
execmany(q, inputs)
#update user table money
if type == "A": #dummy values
price = -2000
elif type == "B":
price = -10000
else:
price = -1000
updateMoney(username, price)
return id
return -1
def getItemsFromList(list_id):
'''def getItemsFromList(list_id): return all items in a list with resolved status'''
q = "SELECT items FROM future_tbl WHERE list_id=?"
inputs = (list_id, )
items = execmany(q, inputs).fetchone()
if items is not None:
items = items[0]
if items is None:
return []
items = items.split(",")
list = []
for item in items:
info = []
item_id = int(item)
info.append(item_id)
q = "SELECT item FROM listitem_tbl WHERE item_id=?"
inputs = (item_id, )
item = execmany(q, inputs).fetchone()[0]
info.append(item)
q = "SELECT resolved FROM listitem_tbl WHERE item_id=?"
resolved = int(execmany(q, inputs).fetchone()[0]) == 1
info.append(resolved)
list.append(tuple(info))
return list
else:
return []
def updateMoney(username, amount):
'''def updateMoney(username, amount): updating data table of user in session with new amount'''
q = "UPDATE user_tbl SET money=? WHERE username=?"
money = getMoney(username)
amount += money
inputs = (amount, username)
execmany(q, inputs)
def addMulti(username, type):
'''def addMulti(username): generating a new game_id for a new multipliayer player game (team or pvp)'''
#generate random game id
game_id = type + str(random.randrange(10000000000))
command = "SELECT game_id FROM game_tbl"
data = exec(command).fetchall()
list = []
for entry in data:
list.append(entry[0])
if len(list) >= 10000000000:
return False
while game_id in list:
game_id = type + str(random.randrange(10000000000))
#add game to game table
command = "INSERT INTO game_tbl VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
inputs = (str(game_id), '0,0,' + username, '', username, '', 0, 0, 0, '',
'')
execmany(command, inputs)
#add game to user table
command = 'SELECT game_id FROM user_tbl WHERE username=?'
inputs = (username, )
games = execmany(command, inputs).fetchone()[0]
command = 'UPDATE user_tbl SET game_id=? WHERE username=?'
games += "," + game_id
inputs = (games, username)
execmany(command, inputs)
return True
def getFlag(username):
'''def getFlag(username): get flag picture url of user in session'''
q = "SELECT flag from user_tbl WHERE username=?"
inputs = (username, )
data1 = execmany(q, inputs).fetchone()[0]
q = "SELECT flag from flags_tbl WHERE country=?"
inputs = (data1, )
data = execmany(q, inputs).fetchone()[0]
return data
def myCountryboard(username):
'''def nationLeaderboard(): Place all users in the same country as user in session in order based on scores'''
q = "SELECT flag FROM user_tbl WHERE username=?"
inputs = (username, )
country = execmany(q, inputs).fetchone()[0]
q = "SELECT username,score FROM user_tbl WHERE flag=?"
inputs = (country, )
countryRank = orderDict(execmany(q, inputs).fetchall())
return countryRank
def getpfp(pic_id):
'''def getpfp(pic_id): retrieve the url of of current profile picture'''
q = "SELECT pic FROM pic_tbl WHERE category=?"
inputs = (pic_id, )
data = execmany(q, inputs).fetchone()
if (data is None):
q = "SELECT flag FROM flags_tbl WHERE country=?"
inputs = (pic_id, )
data = execmany(q, inputs).fetchone()
return data[0]
def addSleep(user_id, date, sleep):
'''adds the sleep into the sleep table if it does not already exist,
updates the sleep if there is already a value inputed for the day'''
if getSleep(user_id, date) == None:
q = "INSERT INTO sleep_tbl VALUES(?, ?, ?)"
inputs = (user_id, date, sleep)
execmany(q, inputs)
else:
q = "UPDATE sleep_tbl SET sleep = ? WHERE user_id = ? AND date = ?"
inputs = (sleep, user_id, date)
execmany(q, inputs)
def addMood(user_id, date, mood):
'''adds the mood into the mood table if it does not already exist,
updates the mood if there is already a value inputed for the day'''
if getMood(user_id, date) == ['None', '#ffffff']:
q = "INSERT INTO mood_tbl VALUES(?, ?, ?)"
inputs = (user_id, date, mood)
execmany(q, inputs)
else:
q = "UPDATE mood_tbl SET mood = ? WHERE user_id = ? AND date = ?"
inputs = (mood, user_id, date)
execmany(q, inputs)
def addUser(username, password):
'''def addUser(username, password): adding user from sign up, taking in form inputs and writing to data table'''
q = "SELECT * FROM user_tbl WHERE username=?"
inputs = (username,)
data = execmany(q, inputs).fetchone()
if (data is None):
q = "INSERT INTO user_tbl VALUES(?, ?, 50000, 0)"
inputs = (username, password)
execmany(q, inputs)
return True
return False #if username already exists
def packM(username):
'''def packM(username): gives random three cards of Mystery pack to user in session'''
q = "SELECT pic FROM pic_tbl WHERE category LIKE 'M%' ORDER BY random() LIMIT 3 "
data = exec(q).fetchall()
for pic in data:
pic = pic[0]
coll = getColl(username)
coll.append(pic)
coll = ",".join(coll)
q = "UPDATE user_tbl SET coll=? WHERE username=?"
inputs = (coll, username)
execmany(q, inputs)
def resolveItem(item_id):
'''def resolveItem(item_id): resolve a specific item'''
q = "SELECT resolved FROM listitem_tbl WHERE item_id=?"
inputs = (item_id, )
resolved = execmany(q, inputs).fetchone()[0]
if resolved == 0:
resolved = 1
else:
resolved = 0
q = "UPDATE listitem_tbl SET resolved=? WHERE item_id=?"
inputs = (resolved, item_id)
execmany(q, inputs)
def getTeamNum(username, game_id):
'''def getTeamNum(username, game_id): given an username and a game_id, return which team the user is on for that given game'''
q = "SELECT team1 FROM game_tbl WHERE game_id=?"
r = "SELECT team2 FROM game_tbl WHERE game_id=?"
inputs = (game_id, )
team1 = execmany(q, inputs).fetchone()[0].split(",")
team2 = execmany(r, inputs).fetchone()[0].split(",")
for i in range(len(team1) - 2):
i = i + 2
if team1[i] == username:
return "1"
return "2"
def sendRequest(to_user, from_user):
'''def sendRequest(to, from): send request to user from other user'''
q = "SELECT requests FROM user_tbl WHERE user_id=?"
inputs = (to_user, )
requests = execmany(q, inputs).fetchone()[0]
if requests is None:
requests = str(from_user)
else:
requests += ',' + str(from_user)
q = "UPDATE user_tbl SET requests=? WHERE user_id=?"
inputs = (requests, to_user)
execmany(q, inputs)
def getCurrentQuestion(username, game_id):
'''def getCurrentQuestion(username, game_id): get the question that the game left off on'''
team = getTeamNum(username, game_id)
q = "SELECT currentq%s FROM game_tbl WHERE game_id=?" % team
inputs = (game_id, )
data = execmany(q, inputs).fetchone()[0]
if (data != ""):
q = "SELECT * FROM question_tbl WHERE question=?"
inputs = (data, )
output = execmany(q, inputs).fetchone()
return output
return None
def claimPrize(username, id):
'''def claimPrize(username, id): allow given user to claim given lottery ticket'''
q = "SELECT owner FROM lottery_tbl WHERE id=?"
inputs = (id, )
owner = execmany(q, inputs).fetchone()[0]
if owner != username: #user doesn't own ticket
return -1
q = "SELECT winnings FROM lottery_tbl WHERE id=?"
winnings = execmany(q, inputs).fetchone()[0]
updateMoney(username, winnings) #update user's money
q = "UPDATE lottery_tbl SET claimed=1 WHERE id=?" #mark ticket as claimed
execmany(q, inputs)
return winnings
def updateEntry(username, entry, date):
'''def updateEntry(username, entry): add a new entry to the journal_tbl'''
idNum = getUserID(username)
q = "SELECT entry_id FROM journal_tbl WHERE user_id=? AND date=?"
inputs = (idNum, date)
data = execmany(q, inputs).fetchone()
if(data is None):
q = "INSERT INTO journal_tbl (user_id, date, body) VALUES(?, ?, ?)"
inputs = (idNum, date, entry)
execmany(q, inputs)
else:
q = "UPDATE journal_tbl SET body=? WHERE user_id=? AND date=?"
inputs = (entry, idNum, date)
execmany(q, inputs)
def sendMessage(user_id, friend_id, list_id):
'''def sendMessage(user_id, friend_id, list_id): send message to a friend from user'''
q = "SELECT newlists FROM user_tbl WHERE user_id=?"
inputs = (friend_id, )
newlists = execmany(q, inputs).fetchone()[0]
if newlists is None:
newlists = []
else:
newlists = newlists.split(",")
entry = str(user_id) + "/" + str(list_id)
newlists.append(entry)
newlists = ",".join(newlists)
q = "UPDATE user_tbl SET newlists=? WHERE user_id=?"
inputs = (newlists, friend_id)
execmany(q, inputs)
def getCollID(username):
'''def getCollID(username): get the ID of the pictures in user's collection'''
coll = getColl(username)
list = []
for i in range(len(coll)):
pic = coll[i]
q = "SELECT country from flags_tbl WHERE flag=?"
inputs = (pic, )
data = execmany(q, inputs).fetchone()
if (data is None):
q = "SELECT category from pic_tbl WHERE pic=?"
data = execmany(q, inputs).fetchone()[0]
else:
data = data[0]
list.append(data)
return list
def getEntry(user, date):
'''def getEntry(username, date): retrieve the body text of the user at the specified date'''
# idNum = getUserID(username)
q = "SELECT body FROM journal_tbl WHERE user_id=? AND date=?"
inputs = (user, date)
data = execmany(q, inputs).fetchone()
return data
def deleteItem(list_id, item_id):
'''def deleteItem(list_id, item_id): remove item from list'''
q = "DELETE from listitem_tbl WHERE item_id=?"
inputs = (item_id, )
execmany(q, inputs)
q = "SELECT items FROM future_tbl WHERE list_id=?"
inputs = (list_id, )
items = execmany(q, inputs).fetchone()[0]
items = items.split(",")
items.remove(str(item_id))
items = ",".join(items)
if items == "":
items = None
q = "UPDATE future_tbl SET items=? WHERE list_id=?"
inputs = (items, list_id)
execmany(q, inputs)
def updateCollaborators(list_id, collaborators):
'''def updateCollaborators(list_id, collaborators): update collaborators on specified list'''
friend_id = getOwner(list_id)[0]
old_collaborators = getCollaboratorId(list_id)
for person in old_collaborators:
user_id = int(person)
removeMessage(friend_id, user_id, list_id)
for person in collaborators:
to_id = person
sendMessage(friend_id, to_id, list_id)
collaborators = ",".join(collaborators)
if collaborators == "":
collaborators = None
q = "UPDATE future_tbl SET collaborators=? WHERE list_id=?"
inputs = (collaborators, list_id)
execmany(q, inputs)
def canEdit(list_id, user_id):
'''def canEdit(list_id, user_id): returns whether or not user is allowed to edit list'''
q = "SELECT user_id FROM future_tbl WHERE list_id=?"
inputs = (list_id, )
data = execmany(q, inputs).fetchone()[0]
if str(user_id) == str(data):
return True
q = "SELECT collaborators FROM future_tbl WHERE list_id=?"
data = execmany(q, inputs).fetchone()
if data is None:
return False
data = data[0]
if data is None:
return False
data = data.split(",")
return (str(user_id) in data)
def getMessages(user_id):
'''def getMessages(user_id): get user's messages'''
q = "SELECT newlists FROM user_tbl WHERE user_id=?"
inputs = (user_id, )
newlists = execmany(q, inputs).fetchone()[0]
if newlists is None:
return []
return newlists.split(",")
def currentNumber(username, game_id):
'''def currentNumber(username, game_id): get which number question the given user is up to'''
team = getTeamNum(username, game_id)
q = "SELECT team%s FROM game_tbl WHERE game_id=?" % team
inputs = (game_id, )
data = execmany(q, inputs).fetchone()[0].split(",")
number = int(data[1])
return number
def getFriends(user_id):
'''def getFriends(user_id): get user list of friends'''
q = "SELECT friends FROM user_tbl WHERE user_id=?"
inputs = (user_id, )
data = execmany(q, inputs).fetchone()[0]
if data is None:
return []
return data.split(",")
def updatePic(username, pic_id):
'''def updatePic(username, pic_id): update new pfp picture with the picture user in session selected'''
isOwner = ownsPic(username, pic_id)
if isOwner:
pic = getpfp(pic_id)
q = "UPDATE user_tbl SET pic=? WHERE username=?"
inputs = (pic, username)
data = execmany(q, inputs)
def addUser(username, password, flag):
'''def addUser(username, password, flag): adding user from sign up,
taking in form inputs and writing to data table'''
q = "SELECT * FROM user_tbl WHERE username=?"
inputs = (username, )
data = execmany(q, inputs).fetchone()
if (data is None):
q = "INSERT INTO user_tbl VALUES(?, ?, ?, ?, '', 200, ?, ?, 0)"
command = "SELECT flag from flags_tbl where country=?"
inputs = (flag, )
pic = execmany(command, inputs).fetchone()[0]
command = "SELECT stat FROM user_tbl WHERE username='******'"
stats = exec(command).fetchone()[0]
inputs = (username, password, pic, pic, flag, stats)
execmany(q, inputs)
return True
return False #if username already exists
