def getUserAccount(self):
cnxn = getConnection()
cursor = cnxn.cursor()
cursor.execute(
'EXEC AccountLookup @UserName="******" , @PassCode="{}";'.format(
self.email, self.password))
return dictfetchall(cursor)
def checkDeliveryInfo(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "SELECT * FROM Delivery WHERE DeliveryID = {};".format(
int(self.deliveryID))
cursor.execute(sql)
return (dictfetchall(cursor))
def getUserAddress(self):
cnxn = getConnection()
cursor = cnxn.cursor()
cursor.execute(
'EXEC AddressLookup @User={} , @Description="Main";'.format(
self.accountID))
return dictfetchall(cursor)
def getCartDetails(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "SELECT Quantity, SpecialInstructions FROM CartItems WHERE CartItemID = {};".format(
int(self.cartItemID))
cursor.execute(sql)
return dictfetchall(cursor)
def findInMenu(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "SELECT ItemID FROM CartItems WHERE CartItemID = {};".format(
int(self.cartItemID))
cursor.execute(sql)
return (cursor.fetchall()[0][0]) # Returns an Int
def checkStatus(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "SELECT DeliveryStatus FROM Delivery WHERE DeliveryID={};".format(
self.deliveryID)
cursor.execute(sql)
return dictfetchall(cursor)
def authenticateCustomer(self):
cnxn = getConnection()
cursor2 = cnxn.cursor()
sqlcommand = "select UserID from UserAccount where Email='{}' and Password='******';".format(
self.email, self.password)
cursor2.execute(sqlcommand)
rows = cursor2.fetchall()
user = []
for row in rows:
user.append(str(row[0]))
cursor = cnxn.cursor()
sqlcommand = "select CustomerID from Customer where UserID={} ;".format(
int(user[0]))
cursor.execute(sqlcommand)
rows2 = cursor.fetchall()
for row in rows2:
print(row)
user.append(str(row[0]))
sqlcommand = "select CONCAT(FirstName, ' ', LastName) from UserAccount where Email='{}' and Password='******';".format(
self.email, self.password)
cursor3 = cnxn.cursor()
cursor3.execute(sqlcommand)
rows3 = cursor3.fetchall()
for row in rows3:
user.append(str(row[0]))
self.userAuthenticated = True
cursor.close()
cursor2.close()
cursor3.close()
cnxn.close()
del cnxn
return user
def get_AddressDetails(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "SELECT StreetAddress,AptNum,City,StateAbbv,Zip FROM Addresses WHERE AddressID = {};".format(
int(self.deliveryAddressID))
cursor.execute(sql)
return (dictfetchall(cursor))
def getCartItems(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "SELECT CartItemID FROM OrderHistory WHERE DeliveryID = {};".format(
int(self.deliveryID))
cursor.execute(sql)
items = cursor.fetchall()
return ([item for t in items for item in t])
def searchStreetAddressOrName(self, value, zip):
cnxn = getConnection()
cursor = cnxn.cursor()
sqlcommand = 'EXEC ViewRestaurantByStreetOrNameOrZip @Value ="%{}%" , @Zip={};'.format(
value, zip)
print(sqlcommand)
cursor.execute(sqlcommand)
return dictfetchall(cursor) #return query result into dict
def getLastOrderRestaurants(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = (
"select top 2 Restaurant.RestaurantName from ((Restaurant inner join Delivery on Restaurant.RestaurantID=Delivery.RestaurantID) inner join OrderHistory on Delivery.DeliveryID =OrderHistory.DeliveryID) where OrderHistory.CustomerID = {}"
).format(int(self.customerID))
cursor.execute(sql)
restaurants = cursor.fetchall()
return ([name for t in restaurants for name in t])
def get_AddressID(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "EXEC FindAddress @User = {}, @Street='{}', @Name ='{}'".format(
self.accountID, self.street, self.addrressName)
print(sql)
cursor.execute(sql)
list = cursor.fetchall()
return list[0][0]
def viewAllergy(self):
cnxn = getConnection()
cursor = cnxn.cursor()
cursor.execute('EXEC ViewAllergy @User= {};'.format(int(self.customerID)))
rows = cursor.fetchall()
list_allergy = []
for row in rows:
list_allergy.append(str(row[0]))
return list_allergy
def viewPreferences(self):
cnxn = getConnection()
cursor = cnxn.cursor()
cursor.execute('EXEC ViewPreferences @User= {};'.format(int(self.customerID)))
rows = cursor.fetchall()
list_preferences = []
for row in rows:
list_preferences.append(str(row[0]))
return list_preferences
def searchZipCode(self, zip):
cnxn = getConnection()
cursor = cnxn.cursor()
cursor.execute(
'EXEC ViewRestaurantByZip @Zip = {}, @Street="%{}%";'.format(
int(zip), zip))
print('EXEC ViewRestaurantByZip @Zip = {}, @Street="%{}%";'.format(
int(zip), zip))
return dictfetchall(cursor) #return query result into dict
def checkEmailExists(self, email):
cnxn = getConnection()
cursor = cnxn.cursor()
cursor.execute(
"select UserID from UserAccount where Email= '{}';".format(email))
rows = cursor.fetchall()
if len(rows) == 0:
return False
else:
return True #return True if an account exsists with that email
def getPastOrders(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = ("SELECT DeliveryID From OrderHistory WHERE CustomerID = {};"
).format(int(self.customerID))
cursor.execute(sql)
orders = cursor.fetchall()
flattened = [id for t in orders for id in t]
unique = set()
return [x for x in flattened if x not in unique and not unique.add(x)]
def getOrderRestaurants(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = (
"Select RestaurantName FROM Restaurant INNER JOIN Delivery ON Restaurant.RestaurantID=Delivery.RestaurantID "
"WHERE Delivery.DeliveryTime LIKE '{}' AND Delivery.DeliveryDate LIKE '{}' AND Delivery.DeliveryAddressID = {};"
).format(self.deliveryTime, self.deliveryDate, self.deliveryAddressID)
cursor.execute(sql)
restaurants = cursor.fetchall()
return ([name for t in restaurants for name in t])
def addTip(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "UPDATE Delivery SET Tip = {} WHERE DeliveryID ={};" # FIX: Needs to be cleaned up to a stored procedure
cursor.execute(sql.format(self.tip, self.deliveryID))
cnxn.commit()
cursor.close()
cnxn.close()
del cnxn
response = "Sucesfully added a tip of ${}".format(self.tip)
return response
def getItemRestaurant(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "Select Restaurant.RestaurantID FROM ((Items inner join Menu on Items.MenuID = Menu.MenuID ) inner join Restaurant on Menu.RestaurantID = Restaurant.RestaurantID ) WHERE ItemID={}".format(self.itemID)
cursor.execute(sql)
results = cursor.fetchall()
response = results[-1][0]
cursor.close()
cnxn.close()
del cnxn
return response
def getItemPrice(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "Select Price FROM Items WHERE ItemID= {}".format(self.itemID)
cursor.execute(sql)
results = cursor.fetchall()
response = results[-1][0]
cursor.close()
cnxn.close()
del cnxn
return response
def addAllergy(self):
try:
cnxn = getConnection()
cursor = cnxn.cursor()
cursor.execute('EXEC AddAllergy @Customer= {}, @Allergy= "{}";'.format(int(self.customerID),self.allergy))
cnxn.commit()
cursor.close()
cnxn.close()
del cnxn
response = "Allergy added to account"
except:
response = "An Error Occured Adding The Allergy To The Account"
return response
def fetchLocation(self, location):
cnxn = getConnection()
cursor = cnxn.cursor() #Establish Connection to the Database
sql = "SELECT AddressID FROM Adresses WHERE StreetAddress = {};" #Find all Addresses at that location
cursor.execute(sql.format(location))
potential = cursor.fetchall()
sql2 = "SELECT RestaurantID FROM Restaurant WHERE AddressID IN {} AND RestuarantName = {};" #FIX: Clean up to stored procedure
cursor2 = cnxn.cursor(
) #Filter Adresses to the Restaurant ID Matching that Specific Restaurant
cursor2.execute(sql2.format(str(tuple(potential)),
self.restaurantName))
idNum = cursor2.fetchall()
return idNum[0]
def searchStreetAddressAndZip(self, zip, value):
cnxn = getConnection()
cursor = cnxn.cursor()
if zip in value:
sqlcommand = 'EXEC ViewRestaurantByStreetOrName @Zip={}, @Value ="%{}%";'.format(
int(zip), value)
else:
sqlcommand = 'EXEC ViewRestaurantByStreetOrName @Zip={}, @Value ="%{}%";'.format(
int(zip), value)
print(sqlcommand)
cursor.execute(sqlcommand)
return dictfetchall(cursor) #return query result into dict
def removePreference(self):
try:
cnxn = getConnection()
cursor = cnxn.cursor()
sqlCommand = 'EXEC RemovePreference @Customer= {}, @Preference= "{}";'.format(int(self.customerID),self.preference)
cursor.execute(sqlCommand)
cnxn.commit()
cursor.close()
cnxn.close()
del cnxn
response = "Preference was removed from account!"
except:
response = "An Error Occured Removing The Preference To The Account"
return response
def addCustomer(self):
try:
cnxn = getConnection()
cursor = cnxn.cursor()
print("user id before creating customer",self.accountID)
cursor.execute("INSERT INTO Customer (UserID) VALUES ({});".format(self.accountID))
cnxn.commit()
cursor.close()
cnxn.close()
del cnxn
response = "Account was created, please login"
except:
response = "An Error Ocurred While Creating Account, Please Try Again"
return response
def foodForensics(self):
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "SELECT MenuID FROM Items WHERE ItemID = {};".format(int(self.itemID))
cursor.execute(sql)
menu = cursor.fetchall()[0][0]
sql = "SELECT Restaurant.RestaurantName FROM Restaurant INNER JOIN Menu ON Restaurant.RestaurantID = Menu.RestaurantID WHERE MenuID = {};".format(int(menu))
cursor.execute(sql)
restaurantName = cursor.fetchall()[0][0]
sql = "SELECT ItemName,Price,Discount,ItemURL FROM Items WHERE ItemID = {};".format(int(self.itemID))
cursor.execute(sql)
response = dictfetchall(cursor)
response[0]["restaurantName"] = restaurantName
return response
def removeFromCart(self):
try:
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "EXEC RemoveCartItem @CartItemID={}, @CustomerID ={};"
cursor.execute(sql.format(self.cartItemID, int(self.customerID)))
cnxn.commit()
cursor.close()
cnxn.close()
del cnxn
response = "Item Succesfully Removed from Cart"
except:
response = "Error Removing Item from Cart"
return response
def deleteMenuItem(self):
try:
cnxn = getConnection()
cursor= cnxn.cursor()
sql = ""
cursor.execute(sql.format())
cnxn.commit()
cursor.close()
cnxn.close()
del cnxn
response = "Menu Item succesfully deleted"
except:
response = "Error deleting Menu Item"
return response
def addMenuItem(self):
try:
cnxn = getConnection()
cursor = cnxn.cursor()
sql = "" # SQL Query to Execute
cursor.execute(sql.format()) #Fill the Query with Class Properties
cnxn.commit()
cursor.close()
cnxn.close()
del cnxn
response = "Menu Item succefully created"
except:
response = "Error creating Menu Item"
return response
