def editUser(oldosis, osis, oldpassword, password, grade, locker, gender, combo, floor, level, type): bool = True if (userValid(oldosis, oldpassword)): if (password != ""): editUserTbl(oldosis, "password", password) if (grade != ""): editUserTbl(oldosis, "grade", grade) if (gender != ""): editUserTbl(oldosis, "gender", gender) q = "SELECT locker FROM user_tbl WHERE osis=?" inputs = (oldosis, ) oldlocker = execmany(q, inputs).fetchone()[0] if (combo != ""): editLockerTbl(oldlocker, "combo", combo, oldosis) if (floor != ""): bool = bool and editLockerTbl(oldlocker, "floor", floor, oldosis) if (level != ""): editLockerTbl(oldlocker, "level", level, oldosis) if (type != ""): editLockerTbl(oldlocker, "location", type, oldosis) # if the user wants to change their owned locker if (locker != ""): if (editLockerTbl(oldlocker, "locker", locker, oldosis)): editUserTbl(oldosis, "locker", locker) editTransTbl(oldosis, "locker", locker) else: bool = False # if the user wants to change their osis if (osis != ""): if not (findOsis(osis)): editTransTbl(oldosis, "notifs", osis) q = "UPDATE locker_tbl SET owner=? WHERE owner=?" inputs = (osis, oldosis) data = execmany(q, inputs) editUserTbl(oldosis, "osis", osis) else: bool = False return bool
def buddyRequest(to, sender): locker = getUserInfo(to) if locker == '': locker = getUserInfo(sender)[2] q = "INSERT INTO transaction_tbl VALUES (?,?,?,?,?,?)" inputs = (locker[2], to, sender, 1, "B", getLockerInfo(locker[2], to)[3]) execmany(q, inputs) return True
def putOnMarket(locker, osis): linfo = getLockerInfo(locker, osis) q = "INSERT INTO transaction_tbl VALUES (?,?,?,?,?,?)" inputs = (locker, "", linfo[1], 1, 'L', linfo[3]) execmany(q, inputs) q = "UPDATE locker_tbl SET status='TRADING' WHERE locker=" + str( locker) + " AND owner=" + str(osis) exec(q)
def deleteTrans(user, recipient, type): # if the user wants to take their locker off the market if (recipient == "" and type == 'L'): q = "UPDATE locker_tbl SET status='OWNED' WHERE owner=" + user exec(q) q = "DELETE FROM transaction_tbl WHERE sender=? AND recipient=? AND request=?" inputs = (user, recipient, type) execmany(q, inputs) return True
def ifDissolve(user): q = "SELECT * FROM transaction_tbl WHERE (recipient=? OR sender=?) AND status = ? AND request = ?" inputs = (user, user, 1, "D") data = execmany(q, inputs).fetchall() if len(data) != 0: return True return False
def findOsis(osis): q = "SELECT * FROM user_tbl WHERE osis = ?" inputs = (osis, ) data = execmany(q, inputs).fetchall() if len(data) != 0: return True return False
def getSurveyInfo(osis): q = "SELECT survey from user_tbl WHERE osis=?" inputs = (osis, ) data = execmany(q, inputs).fetchone() info = [] if (data[0] != ""): info = data[0].split("|") return info
def getDissolveInfo(user): q = "SELECT sender FROM transaction_tbl WHERE recipient=?" inputs = (user, ) data = execmany(q, inputs).fetchall() info = [] for value in data: info.append(value[0]) return info
def editLockerTbl(locker, fxn, new, osis): if fxn == "locker" or fxn == "floor": q = "SELECT locker, floor FROM locker_tbl WHERE locker = ?" inputs = (locker, ) old = execmany(q, inputs).fetchone() if fxn == "locker": q = "SELECT locker, floor FROM locker_tbl WHERE locker = ?" inputs = (new, ) data = execmany(q, inputs).fetchall() else: q = "SELECT locker, floor FROM locker_tbl WHERE floor = ?" inputs = (new, ) data = execmany(q, inputs).fetchall() if data is not None: for value in data: if value[0] == old[0] and value[1] == old[1]: return False new = "\"" + new + "\"" q = "UPDATE locker_tbl SET " + fxn + "=" + new + " WHERE owner=?" inputs = (osis, ) d = execmany(q, inputs) q = "SELECT floor FROM locker_tbl WHERE owner = ? AND locker = ?" inputs = (osis, locker) d = execmany(q, inputs).fetchone() if d is not None: q = "UPDATE transaction_tbl SET floor = ? WHERE locker=? AND floor = ?" inputs = (new, locker, d[0]) data = execmany(q, inputs) else: q = "UPDATE transaction_tbl SET floor = ? WHERE locker=?" inputs = (new, locker) data = execmany(q, inputs) return True
def getLockerInfo(locker, osis): q = "SELECT * from locker_tbl WHERE locker=? AND owner = ?" inputs = (locker, osis) data = execmany(q, inputs).fetchone() info = [] if data is not None: for value in data: info.append(value) return info
def getTransactionTo(osis): q = "SELECT sender FROM transaction_tbl WHERE recipient=? AND status=?" inputs = (osis, 1) data = execmany(q, inputs).fetchall() info = [] if data is None: return info for value in data: info.append(value[0]) return info
def getTransactionFrom(osis): q = "SELECT recipient FROM transaction_tbl WHERE sender=? AND status = ?" inputs = (osis, 1) data = execmany(q, inputs).fetchall() info = [] if data is None: return info for value in data: info.append(value[0]) filtered = [x for x in info if x != ''] return filtered
def getTransactionInfo(osis): q = "SELECT * from transaction_tbl WHERE sender=?" inputs = (osis, ) data = execmany(q, inputs).fetchall() info = [] if (data is None): return info for value in data: if len(value) > 0 and value[3] == 1: info.append( [value[0], value[1], value[2], value[3], value[4], value[5]]) return info
def userValid(osis, password): q = "SELECT password from user_tbl WHERE osis=?" inputs = (osis, ) data = execmany(q, inputs).fetchone() if (data is not None): if (data[0] == password): return True else: flash("Incorrect OSIS and password combination.", 'danger') return False flash("OSIS does not exist. Please sign up for an account.", 'danger') return False
def addUser(osis, password, grade, buddy, linfo, locker, gender): q = "SELECT * FROM user_tbl WHERE osis=?" inputs = (osis, ) data = execmany(q, inputs).fetchone() if (data is None): if (locker != ""): q = "SELECT * FROM locker_tbl WHERE locker='" + locker + "' AND floor='" + linfo[ 1] + "'" data = exec(q).fetchone() if (data is None or locker == ""): q = "INSERT INTO user_tbl VALUES(?, ?, ?, ?, ?, ?, ?)" inputs = (osis, password, locker, grade, buddy, "", gender) execmany(q, inputs) q = "INSERT INTO locker_tbl VALUES(?, ?, ?, ?, ?, ?, ?)" inputs = (locker, osis, linfo[0], linfo[1], linfo[2], linfo[3], linfo[4]) execmany(q, inputs) return "done" else: return "locker" return "user"
def tradeableLockers(): q = "SELECT * FROM transaction_tbl WHERE status=1 AND request='L' AND recipient=''" data = exec(q).fetchall() dataDict = {} for i in data: q = "SELECT * from locker_tbl WHERE locker=? AND status='TRADING'" inputs = (i[0], ) data = execmany(q, inputs).fetchone() info = [] for value in data: info.append(value) dataDict[i] = info return dataDict
def getAllNotifs(osis): q = "SELECT * FROM transaction_tbl WHERE recipient=?" inputs = (osis, ) data = execmany(q, inputs).fetchall() info = [] temp = [] if data is None: return info for value in data: for cell in value: temp.append(cell) info.append(temp) temp = [] return info
def dissolveBuddy(user, sender): q = "UPDATE transaction_tbl SET status = ? WHERE (sender = ? OR recipient = ?) AND request = ?" inputs = (0, user, user, "D") execmany(q, inputs) q = "UPDATE user_tbl SET buddy = ? WHERE osis = ?" inputs = ("", user) execmany(q, inputs) inputs = ("", sender) execmany(q, inputs) return True
def confirmB(user, recipient): q = "UPDATE transaction_tbl SET status = 0 WHERE request = ? AND (sender=? OR recipient = ? OR sender = ? OR recipient = ?)" inputs = ("B", user, user, recipient, recipient) execmany(q, inputs) q = "UPDATE user_tbl SET buddy = ? WHERE osis = ?" inputs = (user, recipient) execmany(q, inputs) q = "UPDATE user_tbl SET buddy = ? WHERE osis = ?" inputs = (recipient, user) execmany(q, inputs) return True
def editTransTbl(osis, fxn, new): if fxn == "locker": q = "UPDATE transaction_tbl SET locker = ? WHERE recipient = ?" inputs = (new, osis) execmany(q, inputs) else: q = "UPDATE transaction_tbl SET sender = ? WHERE sender = ?" inputs = (new, osis) execmany(q, inputs) q = "UPDATE transaction_tbl SET recipient = ? WHERE recipient = ?" inputs = (new, osis) execmany(q, inputs) return True
def searchLocker(searchBy, query): if (searchBy == "Locker Number"): if (len(query) != 5): return False head, space, tail = query.partition("-") q = "SELECT * FROM transaction_tbl WHERE recipient='' AND locker=" + tail + " AND floor=" + head data = exec(q).fetchall() dataDict = {} for i in data: q = "SELECT * from locker_tbl WHERE locker=? AND floor=?" inputs = (tail, head) data = execmany(q, inputs).fetchone() info = [] if data is not None: for value in data: info.append(value) dataDict[i] = info return dataDict else: if (len(query) != 9): return False q = "SELECT * FROM transaction_tbl WHERE recipient='' AND sender=" + query data = exec(q).fetchall() return getTransLock(data, query)
def getUserInfo(osis): q = "SELECT * from user_tbl WHERE osis=?" inputs = (osis, ) data = execmany(q, inputs).fetchone() info = [data[0], data[1], data[2], data[3], data[4], data[5], data[6]] return info
def breakB(user, recipient): q = "INSERT INTO transaction_tbl VALUES (?,?,?,?,?,?)" inputs = ("", recipient, user, 1, "D", "") execmany(q, inputs) return True
def updateSurvey(osis, info): q = "UPDATE user_tbl SET survey=? WHERE osis=?" inputs = (info, osis) execmany(q, inputs)
def editUserTbl(osis, fxn, new): new = "\"" + new + "\"" q = "UPDATE user_tbl SET " + fxn + "=" + new + " WHERE osis=?" inputs = (osis, ) data = execmany(q, inputs) return True
def lockerRequest(to, sender): owner = getUserInfo(to) locker = getLockerInfo(owner[2], to) q = "INSERT INTO transaction_tbl VALUES (?,?,?,?,?,?)" inputs = (owner[2], to, sender, 1, "L", locker[3]) execmany(q, inputs)