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 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 checkDeliveryInfo(self): cnxn = getConnection() cursor = cnxn.cursor() sql = "SELECT * FROM Delivery WHERE DeliveryID = {};".format( int(self.deliveryID)) cursor.execute(sql) return (dictfetchall(cursor))
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 getUserAccount(self): cnxn = getConnection() cursor = cnxn.cursor() cursor.execute( 'EXEC AccountLookup @UserName="******" , @PassCode="{}";'.format( self.email, self.password)) 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 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 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 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 findConcurrentDeliveries(self): cnxn = getConnection() cursor = cnxn.cursor() sql = "SELECT DeliveryTime,DeliveryDate From Delivery WHERE DeliveryID = {};".format( int(self.deliveryID)) cursor.execute(sql) datetime = dictfetchall(cursor) sql = "SELECT DeliveryID FROM Delivery WHERE DeliveryDate LIKE '{}' AND DeliveryTime LIKE '{}' AND DeliveryAddressID = {};".format( str(datetime[0]["DeliveryDate"]), str(datetime[0]["DeliveryTime"]), int(self.deliveryAddressID)) cursor.execute(sql) deliveries = cursor.fetchall() return ([id for t in deliveries for id in t]) # Return a List of Delivery IDs
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 getLastOrder(self): cnxn = getConnection() cursor = cnxn.cursor() sql = "exec LastOrder @Customer = {};".format(int(self.customerID)) cursor.execute(sql) return dictfetchall(cursor)
def searchStreetAddress(self, street): cnxn = getConnection() cursor = cnxn.cursor() cursor.execute( 'EXEC ViewRestaurantByStreet @Street ="%{}%";'.format(street)) return dictfetchall(cursor) #return query result into dict
def getAllOrderDetail(self): cnxn = getConnection() cursor = cnxn.cursor() sql = ("exec LastOrders @Customer={} ").format(self.customerID) cursor.execute(sql) return dictfetchall(cursor)
def searchName(self): cnxn = getConnection() cursor = cnxn.cursor() cursor.execute('EXEC ViewRestaurantByName @Name = "%{}%";'.format( self.restaurantName)) return dictfetchall(cursor) #return query result into dict
def viewItems(self): cnxn = getConnection() cursor = cnxn.cursor() sql = "EXEC ViewRestaurantsItems @Restaurant= {}".format(self.restaurantID) cursor.execute(sql) return dictfetchall(cursor)
def view_users(self): cnxn = getConnection() cursor = cnxn.cursor() cursor.execute("EXEC AllAccounts;") return dictfetchall(cursor) #return query result into dict
def view_userAddresses(self): cnxn = getConnection() cursor = cnxn.cursor() cursor.execute('EXEC LookUpUserAddress @User = {};'.format(self.accountID)) return dictfetchall(cursor) #return query result into dict
def view_AllRestaurants(self): cnxn = getConnection() cursor = cnxn.cursor() cursor.execute('EXEC ViewRestaurants;') return dictfetchall(cursor) #return query result into dict
def view_RestaurantReviews(self): cnxn = getConnection() cursor = cnxn.cursor() cursor.execute('EXEC viewRestaurantReviews @Restaurant={}'.format( int(self.restaurantID))) return dictfetchall(cursor) #return query result into dict
def viewMenuCategory(self): cnxn = getConnection() cursor = cnxn.cursor() sql = "SELECT ItemName,ItemDesc,Price FROM Items WHERE MenuID = {} AND itemType = {};".format(self.menuID,self.itemType) cursor.execute(sql) return dictfetchall(cursor)
def view_UserReviews(self): cnxn = getConnection() cursor = cnxn.cursor() cursor.execute('EXEC CustomerReviews @Customer={}'.format(int(self.customerID))) return dictfetchall(cursor) #return query result into dict
def viewMenu(self,menu): cnxn = getConnection() cursor = cnxn.cursor() sql = "SELECT ItemName,ItemDesc,Price,ItemURL FROM Items WHERE MenuID = {}".format(menu) cursor.execute(sql) return dictfetchall(cursor)