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 update_shopping_policy_on_category(policy_id, field_name, new_value):
sql = """
UPDATE ShoppingPolicyOnCategory
SET {} = '{}'
WHERE policy_id = '{}'
""".format(field_name, new_value, policy_id)
return commit_command(sql)
def edit_user_password(user):
sql_query = """
UPDATE RegisteredUsers
SET password = '******'
WHERE username = '******'
""".format(user.password, user.username)
return commit_command(sql_query)
def add_review_on_item(review):
sql_query = """
INSERT INTO ReviewsOnItems (writerId, itemId, description, rank)
VALUES ('{}', '{}', '{}', '{}');
""".format(review.writerId, review.itemId, review.description,
review.rank)
return commit_command(sql_query)
def update_code_shopping_cart(username, item_id, code):
sql = """
UPDATE ShoppingCartItem
SET code = '{}'
WHERE username = '******' AND itemId = {}
""".format(code, username, item_id)
return commit_command(sql)
def update_item_shopping_cart(username, item_id, new_quantity):
sql = """
UPDATE ShoppingCartItem
SET itemQuantity = '{}'
WHERE username = '******' AND itemId = {}
""".format(new_quantity, username, item_id)
return commit_command(sql)
def update_item(item_id, field_name, new_value):
sql = """
UPDATE Items
SET {} = '{}'
WHERE id = '{}'
""".format(field_name, new_value, item_id)
return commit_command(sql)
def close_shop_permanently(shop_name):
sql_query = """
UPDATE Shops
SET status='Permanently_closed'
WHERE name='{}'
""".format(shop_name)
return commit_command(sql_query)
def update(username, state, age, sex):
sql_query = """
UPDATE UserDetails
SET state = '{}',age = '{}', sex = '{}'
WHERE username = '******'
""".format(state, age, sex, username)
return commit_command(sql_query)
def add_lottery(lottery):
sql_query = """
INSERT INTO Lotteries(lotto_id,final_date,prize_item_id)
VALUES ('{}','{}','{}')
""".format(lottery.lotto_id, lottery.final_date,
lottery.prize_item_id)
return commit_command(sql_query)
def add_item_shopping_cart(shop_cart):
sql = """
INSERT INTO ShoppingCartItem (userName, itemId, itemQuantity)
VALUES ('{}',{},{})
""".format(shop_cart.username, shop_cart.item_id,
shop_cart.item_quantity)
return commit_command(sql)
def add_error_log(tuple_log):
sql_query = """
INSERT INTO ErrorLogs (username, time, event, additional_details)
VALUES ('{}', '{}', '{}', '{}');
""".format(tuple_log.username, tuple_log.time, tuple_log.event,
tuple_log.additional_details)
return commit_command(sql_query)
def send_message_from_shop(message):
sql_query = """
INSERT INTO Messages (MessageFrom, MessageTo, Content)
VALUES ('{}', '{}', '{}')
""".format(message.from_username, message.to_username,
message.content)
return commit_command(sql_query)
def add_security_log(tuple_log):
sql_query = """
INSERT INTO SecurityLogs (time, event, additional_details)
VALUES ('{}', '{}', '{}');
""".format(tuple_log.time, tuple_log.event,
tuple_log.additional_details)
return commit_command(sql_query)
def close_shop(shop_name):
sql_query = """
UPDATE Shops
SET status='Inactive'
WHERE name='{}'
""".format(shop_name)
return commit_command(sql_query)
def re_open_shop(shop_name):
sql_query = """
UPDATE Shops
SET status='Active'
WHERE name='{}' AND status != 'Permanently_closed'
""".format(shop_name)
return commit_command(sql_query)
def add_review_on_shop(shop_review):
sql_query = """
INSERT INTO ReviewsOnShops (writerId, shop_name, description, rank)
VALUES ('{}', '{}', '{}', '{}')
""".format(shop_review.writerId, shop_review.shop_name,
shop_review.description, shop_review.rank)
return commit_command(sql_query)
def add_visible_discount(visible_discount):
sql_query = """
INSERT INTO VisibleDiscounts (item_id, shop_name, percentage, from_date, end_date)
VALUES ('{}', '{}', '{}', '{}', '{}');
""".format(visible_discount.item_id, visible_discount.shop_name,
visible_discount.percentage,
visible_discount.from_date, visible_discount.end_date)
return commit_command(sql_query)
def update_history_appointing(appointing_user, appointed_user, shop_name,
permissions):
sql_query = """
UPDATE HistoryOfAppointing SET permissions = '{}'
WHERE appointingUser='******' AND appointedUser='******' AND shop_name='{}'
""".format(permissions, appointing_user, appointed_user,
shop_name)
return commit_command(sql_query)
def add_history_appointing(appointing_user, appointed_user, position,
shop_name, date, permissions):
sql_query = """
INSERT INTO HistoryOfAppointing (appointingUser, appointedUser, position, shop_name, date, permissions)
VALUES ('{}', '{}', '{}', '{}', '{}', '{}')
""".format(appointing_user, appointed_user, position, shop_name,
date, permissions)
return commit_command(sql_query)
def add_item_to_shop(item):
sql_query = """
INSERT INTO Items (shop_name, name, category, keyWords, price, quantity, kind, url , item_rating,
sum_of_rankings, num_of_reviews)
VALUES ('{}', '{}', '{}', '{}', {}, {}, '{}', '{}', '{}', '{}', '{}');
""".format(item.shop_name, item.name, item.category,
item.keyWords, item.price, item.quantity,
item.kind, item.url, 5, 0, 0)
return commit_command(sql_query)
def add_invisible_discount_category(invisible_discount):
sql_query = """
INSERT INTO InvisibleDiscountsCategory (category, shop_name, percentage, from_date, end_date, code)
VALUES ('{}', '{}', '{}', '{}', '{}', '{}');
""".format(invisible_discount.category,
invisible_discount.shop_name,
invisible_discount.percentage,
invisible_discount.from_date,
invisible_discount.end_date,
invisible_discount.code)
return commit_command(sql_query)
def update_permissions(store_manager):
sql_query = """
UPDATE StoreManagers
SET addItemPermission = '{}' ,removeItemPermission ='{}' ,editItemPermission ='{}',
replyMessagePermission ='{}' ,getAllMessagePermission ='{}', getPurchaseHistoryPermission='{}'
,discountPermission ='{}', setPolicyPermission='{}'
WHERE username='******' AND shop_name = '{}'
""".format(store_manager.permission_add_item,
store_manager.permission_remove_item,
store_manager.permission_edit_item,
store_manager.permission_reply_messages,
store_manager.permission_get_all_messages,
store_manager.permission_get_purchased_history,
store_manager.discount_permission,
store_manager.permission_set_policy,
store_manager.username,
store_manager.store_name)
return commit_command(sql_query)
def add_manager(store_manager):
sql = """
INSERT INTO StoreManagers (username, shop_name,
addItemPermission,
removeItemPermission,
editItemPermission,
replyMessagePermission,
getAllMessagePermission,
getPurchaseHistoryPermission,
discountPermission,
setPolicyPermission)
VALUES ('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')
""".format(store_manager.username, store_manager.store_name,
store_manager.permission_add_item,
store_manager.permission_remove_item,
store_manager.permission_edit_item,
store_manager.permission_reply_messages,
store_manager.permission_get_all_messages,
store_manager.permission_get_purchased_history,
store_manager.discount_permission,
store_manager.permission_set_policy)
return commit_command(sql)
def update_lottery_real_date(purchased_item, end_date):
sql_query = """
UPDATE Lotteries SET real_end_date = '{}'
WHERE lotto_id = {}
""".format(end_date, purchased_item)
return commit_command(sql_query)
def connect_shop_to_owner(username, shop_name):
sql_query = """
INSERT INTO Owners (username, shop_name)
VALUES ('{}', '{}')
""".format(username, shop_name)
return commit_command(sql_query)
def create_shop(shop):
sql_query = """
INSERT INTO Shops (name, status)
VALUES ('{}', '{}');
""".format(shop.name, shop.status)
return commit_command(sql_query)
def update_lottery_item(purchased_item, user_id, price, number_of_tickets):
sql_query = """
UPDATE CustomersInLotteries SET price = price + {} AND number_of_tickets = number_of_tickets + {}
WHERE lotto_id = {} AND username ={})
""".format(price, number_of_tickets, purchased_item, user_id)
return commit_command(sql_query)
def delete_invisible_category_discount(category, shop_name, from_date, code):
sql_query = """
DELETE FROM InvisibleDiscountsCategory
WHERE category='{}' AND shop_name = '{}' AND from_date='{}' AND code='{}'
""".format(category, shop_name, from_date, code)
return commit_command(sql_query)
def delete_invisible_item_discount(item_id, shop_name, from_date, code):
sql_query = """
DELETE FROM InvisibleDiscounts
WHERE item_id={} AND shop_name = '{}' AND from_date='{}' AND code='{}'
""".format(item_id, shop_name, from_date, code)
return commit_command(sql_query)