def remove_owner(username):
sql = """
SELECT shop_name FROM Owners
WHERE username = '******'
""".format(username)
shops_array = select_command(sql)
sql = """
DELETE FROM Owners
WHERE username = '******'
""".format(username)
results = commit_command(sql)
if results is not False:
for i in range(0, len(shops_array)):
sql = """
SELECT * FROM Owners WHERE shop_name = '{}'
""".format(shops_array[i][0])
results = select_command(sql)
if len(results) == 0:
lotteries = get_lotteries_by_shop(shops_array[i][0])
for lottery in lotteries:
lottery_timer(lottery.id)
sql = """
UPDATE Shops
SET status='Permanently_closed'
WHERE name='{}'
""".format(shops_array[i][0])
if not commit_command(sql):
return False
else:
return False
return True
def get_purchased_item(item_id):
sql_query = """
SELECT *
FROM PurchasedItems
WHERE PurchasedItem = {}
""".format(item_id)
return fetch_purchased_item(select_command(sql_query))
def get_all_invisible_category(shop_name):
sql_query = """
SELECT *
FROM InvisibleDiscountsCategory
WHERE shop_name = '{}'
""".format(shop_name)
return fetch_discount_category(select_command(sql_query))
def get_review_on_shop_by_writer(shop_name, writer_id):
sql_query = """
SELECT *
FROM ReviewsOnShops
WHERE shop_name = '{}' AND writerId = '{}'
""".format(shop_name, writer_id)
return fetch_review(select_command(sql_query))
def get_lottery_customers(purchased_item):
sql_query = """
SELECT *
FROM CustomersInLotteries
WHERE lotto_id = '{}'
""".format(purchased_item)
return fetch_lottery_customers(select_command(sql_query))
def get_purchased_items_by_purchase(purchase_id):
sql_query = """
SELECT *
FROM PurchasedItems
WHERE purchaseId = '{}'
""".format(purchase_id)
return fetch_purchased_items(select_command(sql_query))
def get_all_system_managers():
sql_query = """
SELECT *
FROM SystemManagers
"""
results = select_command(sql_query)
return parse_system_managers(results)
def get_sent_system_messages():
sql_query = """
SELECT *
FROM Messages
WHERE MessageFrom = 'System'
"""
return fetch_messages(select_command(sql_query))
def get_all_shopping_policy_on_shop(shop_name):
sql_query = """
SELECT *
FROM ShoppingPolicyOnShop
WHERE shop_name='{}'
""".format(shop_name)
return fetch_shopping_policies_on_shop(select_command(sql_query))
def is_user_exists(username):
sql_query = """
SELECT *
FROM RegisteredUsers
WHERE username = '******'
""".format(username)
return len(select_command(sql_query)) == 1
def get_purchase_history(username):
sql_query = """
SELECT PurchasedItems.*
FROM PurchasedItems,Purchases
WHERE Purchases.purchaseId = PurchasedItems.purchaseId AND Purchases.username = '******'
""".format(username)
return fetch_purchased_items(select_command(sql_query))
def login(user):
sql_query = """
SELECT *
FROM RegisteredUsers
WHERE username = '******' AND password = '******'
""".format(user.username, user.password)
return len(select_command(sql_query)) == 1
def get_user(username):
sql_query = """
SELECT *
FROM RegisteredUsers
WHERE username = '******'
""".format(username)
return fetch_user(select_command(sql_query))
def get_purchased_item_by_shop_and_username(shop_name, username):
sql_query = """
SELECT PurchasedItems.*
FROM PurchasedItems, Items, Purchases
WHERE Items.shop_name = '{}' AND PurchasedItems.purchasedItem = Items.id AND Purchases.purchaseId = PurchasedItems.purchaseId AND Purchases.username='******'
""".format(shop_name, username)
return fetch_purchased_item(select_command(sql_query))
def get_purchased_item_by_user(item_id, username):
sql_query = """
SELECT PurchasedItems.*
FROM PurchasedItems,Purchases
WHERE PurchasedItems.purchasedItem = {} AND PurchasedItems.purchaseId = Purchases.purchaseId AND Purchases.username = '******'
""".format(item_id, username)
return fetch_purchased_item(select_command(sql_query))
def get_all_sent_shop_messages(shop_name):
sql_query = """
SELECT *
FROM Messages
WHERE MessageFrom = '{}'
""".format(shop_name)
return fetch_messages(select_command(sql_query))
def get_received_system_messages():
sql_query = """
SELECT *
FROM Messages
WHERE MessageTo = 'System'
"""
return fetch_messages(select_command(sql_query))
def search_items_by_keywords(item_keyword):
sql_query = """
SELECT *
FROM Items,Shops
WHERE keyWords = '{}' AND Shops.status = 'Active' AND Shops.name = Items.shop_name AND Items.kind <> 'prize'
""".format(item_keyword)
return fetch_items(select_command(sql_query))
def get_history_appointing_by_shop(shop_name):
sql_query = """
SELECT *
FROM HistoryOfAppointing
WHERE shop_name = '{}'
""".format(shop_name)
return fetch_history_appointings(select_command(sql_query))
def get_item_by_code(code):
sql_query = """
SELECT Items.*
FROM Items,InvisibleDiscounts
WHERE Items.id = InvisibleDiscounts.item_id AND InvisibleDiscounts.code = '{}'
""".format(code)
return fetch_item(select_command(sql_query))
def get_user_purchases(username):
sql_query = """
SELECT *
FROM Purchases
WHERE username = '******'
""".format(username)
return fetch_purchases(select_command(sql_query))
def get_top_five_ranked_items():
sql = """
SELECT Items.* FROM Items,Shops
WHERE Items.kind <> 'prize' AND Items.shop_name = Shops.name AND Shops.status = 'Active'
ORDER BY item_rating DESC limit 5
"""
return fetch_items(select_command(sql))
def get_lottery(lottery_id):
sql_query = """
SELECT *
FROM Lotteries
WHERE lotto_id = '{}'
""".format(lottery_id)
return fetch_lottery(select_command(sql_query))
def get_item(item_id):
sql_query = """
SELECT *
FROM Items
Where id = '{}'
""".format(item_id)
return fetch_item(select_command(sql_query))
def get_all_reviews_on_shop(shop_name):
sql_query = """
SELECT *
FROM ReviewsOnShops
WHERE shop_name = '{}'
""".format(shop_name)
return fetch_reviews(select_command(sql_query))
def search_items_in_shop(shop_name):
sql_query = """
SELECT *
FROM Items,Shops
WHERE Shops.name = Items.shop_name AND Items.shop_name = '{}' AND Items.kind <> 'prize'
""".format(shop_name)
return fetch_items(select_command(sql_query))
def get_shop_rank(shop_name):
sql_query = """
SELECT AVG(rank)
FROM ReviewsOnShops
WHERE shop_name = '{}'
""".format(shop_name)
return select_command(sql_query)[0][0]
def get(username):
sql_query = """
SELECT *
FROM UserDetails
WHERE username = '******'
""".format(username)
return parse_user_details(select_command(sql_query))
def get_all_visible_item(shop_name):
sql_query = """
SELECT *
FROM VisibleDiscounts
WHERE shop_name = '{}'
""".format(shop_name)
return fetch_discount_item(select_command(sql_query))
def get_all_messages(actor_id):
sql_query = """
SELECT *
FROM Messages
WHERE MessageTo = '{}'
""".format(actor_id)
return fetch_messages(select_command(sql_query))
