def add_role(role_name, permission_ids):
# Clean user input
role_name = str(role_name).strip()
if not isinstance(permission_ids, list):
raise ValidationError('Permission IDs passed incorrectly.')
# Check is the role_name withn valid length
if not is_valid_length(role_name, 1, 32):
raise ValidationError('Invalid length for role name.')
# Check if the role already exists
role = find_role(role_name, 'role_name')
if role is not None:
raise ValidationError('The role already exists.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """INSERT INTO role (role_name) VALUES (%(role_name)s)"""
cursor.execute(sql, {'role_name': role_name})
# Fetch the id of the newly inserted role
cursor.execute('SELECT LAST_INSERT_ID()')
role_id = cursor.fetchone()['LAST_INSERT_ID()']
dao.commit()
# Set the role's permission
set_role_permissions(role_id, permission_ids)
return role_id
def get_archive_index(value):
# Clean input data
value = str(value).strip()
# Verift the input data
# Archive only support string of length 1 to 255
if not is_valid_length(value, 1, 255):
raise ValidationError('Invalid length.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
search_sql = """SELECT archive_index FROM archive WHERE value = %(value)s"""
cursor.execute(search_sql, {'value': value})
result = cursor.fetchone()
if result is not None:
return result['archive_index']
# When the archive library does not exist the given value, create on
insert_sql = """INSERT INTO archive (value) VALUES (%(value)s)"""
cursor.execute(insert_sql, {'value': value})
cursor.execute(search_sql, {'value': value})
result = cursor.fetchone()
dao.commit()
return result['archive_index']
def update_role(role_id, role_name, permission_ids):
"""The function set's the role_name of the given row (by role_id)"""
# Clean user input
role_id = str(role_id).strip()
role_name = str(role_name).strip()
if not isinstance(permission_ids, list):
raise ValidationError('Permission IDs passed incorrectly.')
# Check is the role_name withn valid length
if not is_valid_length(role_name, 1, 32):
raise ValidationError('Invalid length for role name.')
# Check if the role already exists
role = find_role(role_name, 'role_name')
if role is not None:
raise ValidationError('The role already exists.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """UPDATE role SET role_name = %(role_name)s WHERE role_id = %(role_id)s"""
cursor.execute(sql, {'role_name': role_name, 'role_id': role_id})
dao.commit()
# Set the role's permission
set_role_permissions(role_id, permission_ids)
return role_id
def update_category(category_id, category_name, priority):
# Clean the input data
category_id = category_id.strip()
category_name = category_name.strip()
priority = priority.strip()
# Check is the input valid
if not category_name or not category_id or not priority.isdecimal():
raise ValidationError('Invalid input type.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
if find_category('category_id', category_id) is None:
raise ValidationError('The category does not exists.')
sql = """UPDATE category SET category_name = %(category_name)s,
priority = %(priority)s WHERE category_id = %(category_id)s"""
cursor.execute(
sql, {
'category_name': category_name,
'priority': priority,
'category_id': category_id
})
dao.commit()
def add_category(category_name, priority):
# Clean the input data
category_name = str(category_name).strip()
priority = str(priority).strip()
# Check is the input valid
if not category_name or not priority.isdecimal():
raise ValidationError('Invalid input type.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Check if the category already exists
if find_category('category_name', category_name) is not None:
raise ValidationError('The category already exists.')
sql = """INSERT INTO category (
category_name,
priority
) VALUES (
%(category_name)s,
%(priority)s
)"""
cursor.execute(sql, {'category_name': category_name, 'priority': priority})
dao.commit()
def add_redeem_cards(value, batch=1):
# Clean the input data
value = str(value).strip()
batch = str(batch).strip()
# Check is the input valid
if not is_money(value) or not batch.isdecimal():
raise ValidationError('Invalid input type.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """INSERT INTO redeem_card (
redeem_code,
value
) VALUES (
%(redeem_code)s,
%(value)s
)"""
for i in range(int(batch)):
cursor.execute(sql, {
'redeem_code': generate_random_coupon_code(),
'value': value
})
# Commit every 10 writes
if (i + 1) % 10 == 0:
dao.commit()
dao.commit()
def redeem(user_id, redeem_code):
# Clean the input data
user_id = str(user_id).strip()
redeem_code = str(redeem_code).strip()
# Find redeem card
redeem_card = find_redeem_card(redeem_code)
if redeem_card is None:
raise ValidationError('Invalid redeen code.')
# Find user
user = find_user(method='id', param=user_id)
if user is None:
raise ValidationError('user not found.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """UPDATE user SET balance = %(new_balance)s WHERE user_id = %(user_id)s"""
new_balance = user['balance'] + redeem_card['value']
cursor.execute(sql, {'new_balance': new_balance, 'user_id': user_id})
sql = """DELETE FROM redeem_card WHERE redeem_code = %(redeem_code)s"""
cursor.execute(sql, {'redeem_code': redeem_code})
dao.commit()
def populate_users():
print('Generating ' + str(NUM_USERS) + ' fake users...')
pwd = hash_password('Testpassword123')
dao = DAO()
cursor = dao.cursor()
for i in range(1, NUM_USERS + 1):
first_name = faker.first_name()
last_name = faker.last_name()
if (i % 50 == 0):
print('Current progress: ' + str(i) + '/' + str(NUM_USERS), end='\r')
try:
create_user_bypass(
username = first_name.lower() + last_name.lower() + str(random.randint(1000, 10000)),
first_name = first_name,
last_name = last_name,
email = str(random.randint(1000, 10000)) + faker.email(),
password_hash = pwd,
cursor = cursor
)
except Exception as e:
print(str(e))
if i % 1000 == 0:
dao.commit()
dao.commit()
def update_staff(user_id, role_id, first_name = '', last_name = '', gender = '', phone = ''):
# Call the add staff function
print(user_id)
from models.model_user import update_user_info
update_user_info(
user_id = user_id,
first_name = first_name,
last_name = last_name,
gender = gender,
phone = phone
)
# Clean user input
role_id = str(role_id).strip()
# Check if the staff exists
if find_staff(user_id, 'user_id') is None:
raise ValidationError('Staff not found.')
# Check if the role exists
if find_role(role_id, 'role_id') is None:
raise ValidationError('Invalid role.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """UPDATE staff SET role_id = %(role_id)s WHERE user_id = %(user_id)s"""
cursor.execute(sql, {'role_id': role_id, 'user_id': user_id})
dao.commit()
def update_cart_item_amount(cart_item_id, amount):
"""The function will set the item's amount attribute to the given paramter amount
The function only changes the amount, not the cart_item_id and product_id!!
"""
# Clean the input data
cart_item_id = str(cart_item_id).strip()
amount = str(amount).strip()
# Check is the input valid
if not amount.isdecimal():
raise ValidationError('Invalid amount.')
# If the amount less than or equal to 0, delete the cart item
if int(amount) <= 0:
return delete_cart_item(cart_item_id)
# Check for the existence of item
cart_item = find_cart_item_by_id(cart_item_id)
if cart_item is None:
raise ValidationError('The given cart item does not exists.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """UPDATE cart_item SET
amount = %(amount)s
WHERE cart_item_id = %(cart_item_id)s"""
cursor.execute(sql, {'amount': amount, 'cart_item_id': cart_item_id})
dao.commit()
def add_staff(username, email, password, role_id, first_name = '', last_name = '', gender = '', phone = ''):
# Call the add_user function in the user model
user_id = add_user(username, email, password, first_name, last_name, gender, phone)
# Clean user input
role_id = str(role_id).strip()
# Check if the role exists
if find_role(role_id, 'role_id') is None:
raise ValidationError('Invalid role.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """INSERT INTO staff (
user_id,
role_id
) VALUES (
%(user_id)s,
%(role_id)s
)"""
cursor.execute(sql, {'user_id': user_id, 'role_id': role_id})
dao.commit()
return user_id
def add_product(product_name, categories, price, priority, description = ''):
# Clean the input data
product_name = str(product_name).strip()
description = str(description).strip()
price = str(price).strip()
priority = str(priority).strip()
description = str(description).strip()
# Check is the input valid
if (not product_name) or (not description) or (not priority.isdecimal()) or (type(categories) is not list):
raise ValidationError('Invalid input type.')
if not validator.is_money(price):
raise ValidationError('Invalid pricing.')
if len(categories) == 0:
raise ValidationError('The product should belong to at least one category.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Check if the item already exists
if find_product('product_name', product_name) is not None:
raise ValidationError('The product already exists.')
sql = """INSERT INTO product (
product_name,
description,
price,
priority
) VALUES (
%(product_name)s,
%(description)s,
%(price)s,
%(priority)s
)"""
cursor.execute(sql, {'product_name': product_name,
'description': description,
'priority': priority,
'price': price
})
# Fetch the newly added order's id
cursor.execute('SELECT LAST_INSERT_ID()')
product_id = cursor.fetchone()['LAST_INSERT_ID()']
# Create relationship between product and category
sql = """INSERT INTO product_category(product_id, category_id) VALUES (
%(product_id)s,
%(category_id)s
)"""
for category_id in categories:
cursor.execute(sql, {'product_id': product_id, 'category_id': category_id})
dao.commit()
def delete_permission(permission_id):
# Clean user input
permission_id = str(permission_id).strip()
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Insert into role_permission
sql = """DELETE FROM permission WHERE permission_id = %(permission_id)s"""
cursor.execute(sql, {'permission_id': permission_id})
dao.commit()
def update_product(product_id, product_name, categories, price, priority, description=''):
# Clean the input data
product_id = str(product_id).strip()
product_name = str(product_name).strip()
description = str(description).strip()
price = str(price).strip()
priority = str(priority).strip()
description = str(description).strip()
# Check is the input valid
if (not product_id) or (not product_name) or (not description) or (not priority.isdecimal()) or (type(categories) is not list):
raise ValidationError('Invalid input type.')
if not validator.is_money(price):
raise ValidationError('Invalid pricing.')
if len(categories) == 0:
raise ValidationError('The product should belong to at least one category.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Check if the item exists
if find_product('product_name', product_name) is None:
raise ValidationError('The category does not exists.')
sql = """UPDATE product SET
product_name = %(product_name)s,
description = %(description)s,
price = %(price)s,
priority = %(priority)s
WHERE product_id = %(product_id)s
"""
cursor.execute(sql, {'product_name': product_name,
'description': description,
'priority': priority,
'price': price,
'product_id': product_id
})
# Create relationship between product and category
sql = """DELETE FROM product_category WHERE product_id = %(product_id)s"""
cursor.execute(sql, {'product_id': product_id})
sql = """INSERT INTO product_category(product_id, category_id) VALUES (
%(product_id)s,
%(category_id)s
)"""
for category_id in categories:
cursor.execute(sql, {'product_id': product_id, 'category_id': category_id})
dao.commit()
def add_coupon(coupon_code,
value,
threshold,
activate_date=None,
expire_date=None):
# Clean the input data
coupon_code = str(coupon_code).strip()
value = str(value).strip()
threshold = str(threshold).strip()
# Check is the input valid
if not is_money(value):
raise ValidationError('Invalid value.')
if not is_money(threshold):
raise ValidationError('Invalid threshold.')
# Check if the threshold is less than the value the coupon can deduct
if float(value) > float(threshold):
raise ValidationError(
'The value should be greater than the threshold.')
# Check the existence of the coupon
if find_coupon(coupon_code) is not None:
raise ValidationError('The coupon code already exists.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """INSERT INTO coupon (
coupon_code,
value,
threshold,
activate_date,
expire_date
) VALUES (
%(coupon_code)s,
%(value)s,
%(threshold)s,
%(activate_date)s,
%(expire_date)s
)"""
cursor.execute(
sql, {
'coupon_code': coupon_code,
'value': value,
'threshold': threshold,
'activate_date': activate_date,
'expire_date': expire_date
})
dao.commit()
def delete_coupon(coupon_code):
# Clean the input data
coupon_code = str(coupon_code).strip()
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Check if the coupon exists
if find_coupon(coupon_code) is None:
raise ValidationError('The coupon does not exists.')
sql = """DELETE FROM coupon WHERE coupon_code = %(coupon_code)s"""
cursor.execute(sql, {'coupon_code': coupon_code})
dao.commit()
def populate_archive():
print('Populating archive with ' + str(NUM_ARCHIVE) + ' number of archive records...')
dao = DAO()
cursor = dao.cursor()
for i in range(1, NUM_ARCHIVE + 1):
if (i % 100 == 0):
print('Current progress: ' + str(i) + '/' + str(NUM_ARCHIVE), end='\r')
get_archive_index(faker.address() + str(random.randint(10000,50000)), cursor)
if (i % 2000 == 0):
dao.commit()
dao.commit()
def delete_redeem_card(redeem_code):
# Clean the input data
redeem_code = str(redeem_code).strip()
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Check if the redeem card exists
if find_redeem_card(redeem_code) is None:
raise ValidationError('The redeem card does not exists.')
sql = """DELETE FROM redeem_card WHERE redeem_code = %(redeem_code)s"""
cursor.execute(sql, {'redeem_code': redeem_code})
dao.commit()
def remove_product(product_id):
# Clean the input data
product_id = str(product_id).strip()
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Clear from the product_category table
sql = """DELETE FROM product_category WHERE product_id = %(product_id)s"""
cursor.execute(sql, {'product_id': product_id})
# Clear from the products table
sql = """DELETE FROM product WHERE product_id = %(product_id)s"""
cursor.execute(sql, {'product_id': product_id})
dao.commit()
def delete_order(order_id):
# Clean the input data
order_id = str(order_id).strip()
# Check for existence
if find_order_by_id(order_id) is None:
raise ValidationError('Order not found.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """DELETE FROM `order` WHERE order_id = %(order_id)s"""
cursor.execute(sql, {'order_id': order_id})
dao.commit()
def update_user_info(user_id,
first_name='',
last_name='',
gender='',
phone=''):
# Clean user input
first_name = str(first_name).strip()
last_name = str(last_name).strip()
gender = str(gender).strip()
phone = str(phone).strip()
if not validator.is_valid_length(first_name, 0, 24):
raise ValidationError('Invalid first name')
if not validator.is_valid_length(last_name, 0, 24):
raise ValidationError('Invalid last name')
if gender not in ['M', 'F', '']:
raise ValidationError('Invalid gender')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Check the existence of the user
sql = """SELECT * FROM user WHERE user_id = %(user_id)s"""
cursor.execute(sql, {'user_id': user_id})
result = cursor.fetchone()
if result is None:
raise ValidationError('User not found.')
# Update information in the database
sql = """UPDATE user SET
first_name = %(first_name)s,
last_name = %(last_name)s,
gender = %(gender)s,
phone = %(phone)s
WHERE user_id = %(user_id)s"""
cursor.execute(
sql, {
'first_name': first_name,
'last_name': last_name,
'gender': gender,
'phone': phone,
'user_id': user_id
})
dao.commit()
def create_cart_item(user_id, product_id, amount=1):
"""The function creates a cart item if the cart item does not exist.
Otherwise, the cart item will be updated in an "append" manner
"""
# Clean the input data
user_id = str(user_id).strip()
product_id = str(product_id).strip()
amount = str(amount).strip()
# Check is the input valid
if not user_id or not product_id:
raise ValidationError('Invalid identifier(s).')
if not amount.isdecimal():
raise ValidationError('Invalid amount.')
# Check for the existence of user and product
if find_user(method='id', param=user_id) is None:
raise ValidationError('Invalid user id.')
if find_product(method='product_id', param=product_id) is None:
raise ValidationError('Invalid product id.')
# Check the user's cart. If the item already exists, perform update instead of insertion
item = find_cart_item_id(user_id, product_id)
if item is not None:
return update_cart_item_amount(item['cart_item_id'],
int(item['amount']) + int(amount))
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """INSERT INTO cart_item (
user_id,
product_id,
amount
) VALUES (
%(user_id)s,
%(product_id)s,
%(amount)s
)"""
cursor.execute(sql, {
'user_id': user_id,
'product_id': product_id,
'amount': amount
})
dao.commit()
def remove_category(category_id):
# Clean the input data
category_id = str(category_id).strip()
# Check is the input valid
if not category_id.isdecimal():
raise ValidationError('Invalid input type.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Check if the category exists
if find_category('category_id', category_id) is None:
raise ValidationError('The category does not exists.')
sql = """DELETE FROM category WHERE category_id = %(category_id)s"""
cursor.execute(sql, {'category_id': category_id})
dao.commit()
def add_permission(permission_name):
# Clean user input
permission_name = str(permission_name).strip()
# Check if the permission exists
if find_permission(permission_name, 'permission_name') is not None:
raise ValidationError('The permission already exists.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Insert into role_permission
sql = """INSERT INTO permission (
permission_name
) VALUES (
%(permission_name)s
)"""
cursor.execute(sql, {'permission_name': permission_name})
dao.commit()
def update_user_avatar(user_id, avatar):
# Clean user input
user_id = str(user_id).strip()
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Check the existence of the user
sql = """SELECT * FROM user WHERE user_id = %(user_id)s"""
cursor.execute(sql, {'user_id': user_id})
result = cursor.fetchone()
if result is None:
raise ValidationError('User not found.')
# Update information in the database
sql = """UPDATE user SET
avatar = %(avatar)s
WHERE user_id = %(user_id)s"""
cursor.execute(sql, {'avatar': avatar, 'user_id': user_id})
dao.commit()
def delete_cart_item(cart_item_id, relay_cursor=None):
"""The function finds a cart item according to its id"""
# Clean the input data
cart_item_id = str(cart_item_id).strip()
# Check for existence
if find_cart_item_by_id(cart_item_id) is None:
raise ValidationError('The given cart item does not exists.')
# Establish db connection
if relay_cursor is None:
dao = DAO()
cursor = dao.cursor()
else:
cursor = relay_cursor
sql = """DELETE FROM cart_item WHERE cart_item_id = %(cart_item_id)s"""
cursor.execute(sql, {'cart_item_id': cart_item_id})
# Only commit when the operation is considered atomic
if relay_cursor is None:
dao.commit()
def update_order_status(order_id, status):
# Clean the input data
order_id = str(order_id).strip()
status = str(status).strip()
if status not in ['CANC', 'PEND', 'PROC', 'REDY', 'DONE', 'REDD']:
raise ValidationError('Invalid status code.')
# Check for the existence of order
order = find_order_by_id(order_id)
if order is None:
raise ValidationError('Order not found.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """UPDATE `order` SET
status = %(status)s
WHERE order_id = %(order_id)s"""
cursor.execute(sql, {'status': status, 'order_id': order_id})
dao.commit()
def set_role_permissions(role_id, permission_ids):
"""The function sets the permissions for a given role
permissions_ids must be a list
"""
# Clean user input
role_id = str(role_id).strip()
if not isinstance(permission_ids, list):
raise ValidationError('Permission IDs passed incorrectly.')
# Check if the role exists
role = find_role(role_id, 'role_id')
if role is None:
raise ValidationError('The role does not exists.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
# Clear all permissions
sql = """DELETE FROM role_permission WHERE role_id = %(role_id)s"""
cursor.execute(sql, {'role_id': role_id})
# Insert into role_permission
sql = """INSERT INTO role_permission (
role_id,
permission_id
) VALUES (
%(role_id)s,
%(permission_id)s
)"""
for permission_id in permission_ids:
cursor.execute(sql, {
'role_id': role_id,
'permission_id': permission_id
})
dao.commit()
def change_password(user_id, password):
password = str(password).strip()
if not validator.is_valid_password(password):
raise ValidationError('Invalid password.')
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """SELECT * FROM user WHERE user_id = %(user_id)s"""
cursor.execute(sql, {'user_id': user_id})
result = cursor.fetchone()
if result is None:
raise ValidationError('User not found.')
password_hash = hash_password(password)
sql = """UPDATE user SET password_hash = %(password_hash)s
WHERE user_id = %(user_id)s"""
cursor.execute(sql, {'password_hash': password_hash, 'user_id': user_id})
dao.commit()
def place_redeem(user_id, amount):
# Clean the input data
user_id = str(user_id).strip()
amount = str(amount).strip()
# Verify the amount
if not is_money(amount):
raise ValidationError('Invalid amount.')
# Verify the user_id
if find_user(method='id', param=user_id) is None:
raise ValidationError('Invalid user id.')
# Insert into order
# Establish db connection
dao = DAO()
cursor = dao.cursor()
sql = """INSERT INTO `order` (
user_id,
total,
actual_paid,
status
) VALUES (
%(user_id)s,
%(total)s,
%(actual_paid)s,
%(status)s
)"""
# A status code of 100 means it is a desposite
cursor.execute(sql, {
'user_id': user_id,
'total': -amount,
'actual_paid': 0,
'status': 100
})
dao.commit()
