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