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 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 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 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_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 update_contribution_request_count(tale_id, value):
DAO.update(
'''
UPDATE anaddventure.tale
SET tale_contribution_request_count = tale_contribution_request_count + (%s)
WHERE tale_id = (%s)
''', (value, tale_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_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 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 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 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 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 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 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 update_follow_count(tale_id, value):
DAO.update(
'''
UPDATE anaddventure.tale
SET tale_follow_count = tale_follow_count + (%s)
WHERE tale_id = (%s)
''', (value, tale_id))
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 update_follow_count(tale_id, value): DAO.update( ''' UPDATE anaddventure.tale SET tale_follow_count = tale_follow_count + (%s) WHERE tale_id = (%s) ''', (value, tale_id) )
def update_contribution_request_count(tale_id, value): DAO.update( ''' UPDATE anaddventure.tale SET tale_contribution_request_count = tale_contribution_request_count + (%s) WHERE tale_id = (%s) ''', (value, tale_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 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 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 update_profile(user_id, name, email, biography, is_email_visible):
DAO.update(
"""
UPDATE anaddventure.system_user SET
system_user_name = (%s),
system_user_email = (%s),
system_user_biography = (%s),
system_user_is_email_visible = (%s)
WHERE system_user_id = (%s)
""", (name, email, biography, is_email_visible, user_id))
def update_all(tale_id, title, description, category, license_id):
DAO.update(
'''
UPDATE anaddventure.tale
SET tale_title = (%s),
tale_description = (%s),
tale_category = (%s),
tale_license = (%s)
WHERE tale_id = (%s)
''', (title, description, category, license_id, tale_id))
def update_all(tale_id, title, description, category, license_id): DAO.update( ''' UPDATE anaddventure.tale SET tale_title = (%s), tale_description = (%s), tale_category = (%s), tale_license = (%s) WHERE tale_id = (%s) ''', (title, description, category, license_id, tale_id) )
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 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
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 select_viewable_by_creator_id_and_viewer_id( creator_id, viewer_id, rows = None ): return Tale._construct_tale_objects( DAO.select_by( ''' SELECT DISTINCT tale_id, tale_title, tale_description, tale_category, tale_creator, tale_license, tale_star_count, tale_follow_count, tale_contribution_request_count, tale_creation_datetime FROM anaddventure.tale INNER JOIN anaddventure.invitation ON tale_creator = (%s) AND ( tale_category = 1 OR ( invitation_creator = tale_creator AND invitation_invited = (%s) AND invitation_tale_id = tale_id ) OR tale_creator = (%s) ) ORDER BY tale_star_count DESC ''', (creator_id, viewer_id, viewer_id), rows ) )
def select_top_ten_order_by_star_count(): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_category = 1 ORDER BY tale_star_count DESC LIMIT 5", () ) )
def select_tales_other_creator(user_id, viewer_id, rows = None): return Tale._construct_tale_objects( DAO.select_by( ''' SELECT DISTINCT tale_id, tale_title, tale_description, tale_category, tale_creator, tale_license, tale_star_count, tale_follow_count, tale_contribution_request_count, tale_creation_datetime FROM anaddventure.tale INNER JOIN anaddventure.contribution_request ON contribution_request_system_user_id = (%s) AND tale_creator != contribution_request_system_user_id AND tale_id = contribution_request_tale_id AND ( tale_category = 1 OR ( ( SELECT COUNT(*) FROM anaddventure.invitation WHERE invitation_creator = tale_creator AND invitation_invited = contribution_request_system_user_id ) > 0 AND ( SELECT COUNT(*) FROM anaddventure.invitation WHERE invitation_creator = tale_creator AND invitation_invited = (%s) ) > 0 ) ) ORDER BY tale_star_count DESC ''', (user_id, viewer_id), rows ) )
def select_by_full_title(title = '', rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_title ILIKE (%s)", (title, ), rows ) )
def select_by_category(category = '', rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_category = (%s)", (category, ), rows ) )
def select_count_by_title(title = '', rows = None):
return Tale._construct_tale_objects(
DAO.select_by(
"SELECT COUNT(tale_id) FROM anaddventure.tale WHERE tale_title ILIKE (%s)",
('%' + title + '%', ),
rows
)
)
def select_by_license_id(license_id, rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_license = (%s)", (license_id, ), rows ) )
def select_by_creator_id(creator_id, rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_creator = (%s)", (creator_id, ), rows ) )
def select_by_id(id, rows = None): return License._construct_license_objects( DAO.select_by( "SELECT * FROM anaddventure.license WHERE license_id = (%s)", (id, ), rows ) )
def select_all(rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale", (), rows ) )
def select_by_previous_chapter_id(previous_chapter_id, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_previous_chapter = (%s)", (previous_chapter_id, ), rows ) )
def select_by_tale_id_and_previous_chapter_id(tale_id, previous_chapter_id, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_tale_id = (%s) AND chapter_previous_chapter = (%s)", (tale_id, previous_chapter_id), rows ) )
def select_by_title(title, rows = None):
return Chapter._construct_chapter_objects(
DAO.select_by(
"SELECT * FROM anaddventure.chapter WHERE chapter_title ILIKE (%s)",
('%' + title + '%', ),
rows
)
)
def select_by_date(date, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_datetime = (%s)", (date, ), rows ) )
def select_by_number(number, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_number = (%s)", (number, ), rows ) )
def select_by_tale_id_order_by_date(tale_id, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_tale_id = (%s) ORDER BY chapter_datetime DESC", (tale_id, ), rows ) )
def select_by_creator_id_and_full_title(creator_id, title, rows = None): return Tale._construct_tale_objects( DAO.select_by( "SELECT * FROM anaddventure.tale WHERE tale_creator = (%s) AND tale_title ILIKE (%s)", (creator_id, title), rows ) )
def select_all(rows = None): return License._construct_license_objects( DAO.select_by( "SELECT * FROM anaddventure.license", (), rows ) )
def select_all(rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter", (), rows ) )
def select_by_name(name, rows = None):
return License._construct_license_objects(
DAO.select_by(
"SELECT * FROM anaddventure.license WHERE license_name ILIKE (%s)",
('%' + name + '%', ),
rows
)
)
def select_by_user_id(user_id, rows = None): return Chapter._construct_chapter_objects( DAO.select_by( "SELECT * FROM anaddventure.chapter WHERE chapter_system_user_id = (%s)", (user_id, ), rows ) )
def select_by_tale_id(tale_id, rows = None): return Contribution_Request._construct_contribution_request_objects( DAO.select_by( "SELECT * FROM anaddventure.contribution_request WHERE contribution_request_tale_id = (%s)", (tale_id, ), rows ) )
def select_by_was_closed(was_closed, rows = None): return Contribution_Request._construct_contribution_request_objects( DAO.select_by( "SELECT * FROM anaddventure.contribution_request WHERE contribution_request_was_closed = (%s)", (was_closed, ), rows ) )
def select_by_id(id, rows = None):
return Password_Change_Requests._construct_password_change_request_objects(
DAO.select_by(
"SELECT * FROM anaddventure.password_change_requests WHERE password_change_requests_id LIKE (%s)",
(hashlib.sha256(id.encode('utf-8')).hexdigest(), ),
rows
)
)
def select_count_viewable_by_title_and_creator_id(title, creator_id, rows = None):
return DAO.select_by(
'''
SELECT COUNT(tale_id) FROM anaddventure.tale
WHERE tale_title ILIKE (%s) AND (tale_category = 1 OR tale_creator = (%s))
''',
('%' + title + '%', creator_id),
rows
)
def update_title_and_content(contribution_request_id, title, content): return DAO.update( ''' UPDATE anaddventure.contribution_request SET contribution_request_title = (%s), contribution_request_content = (%s) WHERE contribution_request_id = (%s) ''', (title, content, contribution_request_id) )
def update_was_accepted(contribution_request_id, was_accepted): return DAO.update( ''' UPDATE anaddventure.contribution_request SET contribution_request_was_accepted = (%s), contribution_request_was_closed = True WHERE contribution_request_id = (%s) ''', (was_accepted, contribution_request_id) )
