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)
