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)