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