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 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 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 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 find_role(param, method): """The function will find the role according to the method specified and the given parameter. Methods include 'role_id' and 'role_name' """ # Check if the method is valid if method not in ['role_id', 'role_name']: raise ValidationError('Invalid method.') # Clean user input param = str(param).strip() # Establish db connection dao = DAO() cursor = dao.cursor() # Query db for role sql = """SELECT * FROM role WHERE """ sql += method sql += """ = %(param)s""" cursor.execute(sql, {'param': param}) result = cursor.fetchone() return result
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 get_products(method, param = ''): if method not in ['category_id', 'all']: raise ValidationError('Invalid method') # Clean the input data param = str(param).strip() # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = '' if method == 'category_id': if not param: raise ValidationError('The parameter can not be empty.') sql = """SELECT * FROM product, product_category, category WHERE product.product_id = product_category.product_id AND product_category.category_id = category.category_id AND category.category_id = %(param)s ORDER BY product.priority DESC, product.product_name ASC""" cursor.execute(sql, {'param': param}) else: sql = """SELECT * FROM product ORDER BY product.priority DESC, product.product_name ASC""" cursor.execute(sql) result = cursor.fetchall() return result
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 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_cart_items_by_user_id(user_id): """The function find all the items in the user's cart and return the info required by the front-end Keyword arguments: user_id -- the user id """ # Clean the input data user_id = str(user_id).strip() # Check for the existence of user if find_user(param=user_id, method='id') is None: raise ValidationError('Invalid user id.') # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = """SELECT cart_item.cart_item_id, cart_item.product_id, product.product_name, product.price, cart_item.amount FROM cart_item, product WHERE cart_item.product_id = product.product_id AND user_id = %(user_id)s ORDER BY created_at DESC""" cursor.execute(sql, {'user_id': user_id}) result = cursor.fetchall() return result
def find_staff(param, method): """The function finds the staff according the staff's user_id or username The return dict contains: user_id and username """ # Check if the method is valid if method not in ['user_id', 'username']: raise ValidationError('Invalid method.') # Clean user input param = str(param).strip() # Establish db connection dao = DAO() cursor = dao.cursor() # Query db for role sql = '' if method == 'user_id': sql = """WITH s_u (user_id, username) AS ( SELECT user_id, username FROM user WHERE user_id = %(param)s ) SELECT staff.user_id, staff.role_id, s_u.username FROM staff, s_u WHERE staff.user_id = s_u.user_id""" else: sql = """WITH s_u (user_id, username) AS ( SELECT user_id, username FROM user WHERE username = %(param)s ) SELECT staff.user_id, staff.role_id, s_u.username FROM staff, s_u WHERE staff.user_id = s_u.user_id""" cursor.execute(sql, {'param': param}) result = cursor.fetchone() return result
def authorization_check(user_id, permission_name): """The function verifies if the staff is authorized for the permission Parameters: user_id -- the user_id of the staff permission_name -- the name of the permission """ # Establish db connection dao = DAO() cursor = dao.cursor() # Query db for role # Check if the user is a superadmin sql = """SELECT role_name FROM staff, role WHERE staff.user_id = %(user_id)s AND staff.role_id = role.role_id AND role.role_name = 'superadmin'""" cursor.execute(sql, {'user_id': user_id, 'permission_name': permission_name}) result = cursor.fetchone() if result is not None: return True # The staff is not a superuser, check for permission sql = """SELECT permission_name FROM staff, role_permission, permission WHERE staff.user_id = %(user_id)s AND staff.role_id = role_permission.role_id AND role_permission.permission_id = permission.permission_id AND permission.permission_name = %(permission_name)s""" cursor.execute(sql, {'user_id': user_id, 'permission_name': permission_name}) result = cursor.fetchone() return False if result is None else True
def verify_credential(param, password, method='username'): # The function takes in username and password from user input # If the verification succeeded, the user's id will be returned # Otherwise, None will be returned # Check type of verification if method not in ['username', 'user_id']: raise ValidationError('Method not allowed.') # Clean the data param = str(param).strip() password = str(password).strip() # Establish db connection dao = DAO() cursor = dao.cursor() # Query the database for password and user_id sql = '' if method == 'username': sql = """SELECT user_id, password_hash FROM user WHERE username = %(param)s""" else: sql = """SELECT user_id, password_hash FROM user WHERE user_id = %(param)s""" cursor.execute(sql, {'param': param}) result = cursor.fetchone() if result is None: raise ValidationError('Invalid username') if not verify_password(password, result['password_hash']): raise ValidationError('Incorrect password') return result['user_id']
def find_coupon_and_check_validity(coupon_code): # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = """SELECT * FROM coupon WHERE coupon_code = %(coupon_code)s""" cursor.execute(sql, {'coupon_code': coupon_code}) coupon = cursor.fetchone() if coupon is None: raise ValidationError('The coupon does not exists.') # Check if the coupon is active or has expired current_time = dt.datetime.now() activate_date = coupon['activate_date'] if coupon[ 'activate_date'] is not None else dt.datetime(1970, 1, 1) expire_date = coupon['expire_date'] if coupon[ 'expire_date'] is not None else dt.datetime(9999, 12, 31) if (current_time - activate_date).total_seconds() < 0: raise ValidationError('The coupon is not activate yet.') elif (expire_date - current_time).total_seconds() < 0: raise ValidationError('The coupon has expired.') return coupon
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 get_orders(scope, limit=0, offset=0): # Verify is the scope valid if scope not in ['all', 'on_going']: raise ValidationError('Invalid scope.') # Verify parameters for pagination limit = str(limit).strip() offset = str(offset).strip() if not limit.isdecimal() or not offset.isdecimal(): raise ValidationError('Invalid pagination parameters.') # Establish db connection dao = DAO() cursor = dao.cursor() # Query database if scope == 'on_going': sql = """WITH s_order AS ( SELECT * FROM `order` WHERE status >= 2 AND status <= 4 ) SELECT user.user_id, user.username, user.first_name, user.last_name, s_order.order_id, s_order.actual_paid, s_order.status, s_order.created_at FROM s_order, user_order, user WHERE s_order.order_id = user_order.order_id AND user_order.user_id = user.user_id ORDER BY s_order.created_at ASC""" elif scope == 'all': sql = """SELECT user.user_id, user.username, user.first_name, user.last_name, `order`.order_id, `order`.actual_paid, `order`.status, `order`.created_at FROM `order`, user_order, user WHERE `order`.order_id = user_order.order_id AND user_order.user_id = user.user_id ORDER BY `order`.created_at DESC, order_id DESC""" if not int(limit) == 0: sql += ' LIMIT ' + limit + ' OFFSET ' + offset cursor.execute(sql) orders = cursor.fetchall() for order in orders: total = 0 order_items = get_order_purchased_items(order['order_id']) order['items'] = order_items for order_item in order_items: total += order_item['product_price_snapshot'] * order_item['amount'] order['total'] = total return orders
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 get_category_list(): # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = """SELECT * FROM category ORDER BY priority DESC, category_name ASC""" cursor.execute(sql) result = cursor.fetchall() return result
def count_records_length(): # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = """SELECT count(redeem_code) as len FROM redeem_card""" cursor.execute(sql) length = cursor.fetchone()['len'] return length
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 find_coupon(coupon_code): # Clean the input data coupon_code = str(coupon_code).strip() # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = """SELECT * FROM coupon WHERE coupon_code = %(coupon_code)s""" cursor.execute(sql, {'coupon_code': coupon_code}) result = cursor.fetchone() return result
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 find_order_by_id(order_id): # Clean the input data order_id = str(order_id).strip() # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = """SELECT * FROM `order` WHERE order_id = %(order_id)s""" cursor.execute(sql, {'order_id': order_id}) result = cursor.fetchone() return result
def find_cart_item_by_id(cart_item_id): # Clean the input data cart_item_id = str(cart_item_id).strip() # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = """SELECT * FROM cart_item WHERE cart_item_id = %(cart_item_id)s""" cursor.execute(sql, {'cart_item_id': cart_item_id}) result = cursor.fetchone() return result
def find_redeem_card(redeem_code): # Clean the input data param = str(redeem_code).strip() # Establish db connection dao = DAO() cursor = dao.cursor() # Query database sql = """SELECT * FROM redeem_card WHERE redeem_code = %(redeem_code)s""" cursor.execute(sql, {'redeem_code': redeem_code}) result = cursor.fetchone() return result
def get_staff_list(): """The function returns all the complete table of information for staff""" # Establish db connection dao = DAO() cursor = dao.cursor() # Query db for role sql = """SELECT * FROM staff, role, user WHERE staff.user_id = user.user_id AND staff.role_id = role.role_id""" cursor.execute(sql) result = cursor.fetchall() return result