def set_goal(user_id, year, month, goal):
# SQL
conn = connect_sys_db()
query = "INSERT INTO monthly_goal VALUES(\'{user_id}\', \'{year}\', \'{month}\', \'{goal}\')".format(
user_id=user_id, year=year, month=month, goal=goal)
with mysql(conn) as cursor:
cursor.execute(query)
def get_read_history(user_id):
collection_id = Collection.get_readcollection_id(user_id)
# SQL
conn = connect_sys_db()
query = "SELECT book_id FROM collects WHERE collection_id = \'{collection_id}\'".format(
collection_id=collection_id)
db_result = read_sql(sql=query, con=conn)
json_str = db_result.to_json(orient='index')
ds = json.loads(json_str)
result = []
for index in ds:
finish_date = Collection.get_book_read_date(
user_id, ds[index]['book_id'])
# Add book's finish_time and title and cover_url to result
ds[index]['finish_time'] = finish_date
book = Book(ds[index]['book_id'])
ds[index]['book_title'] = book.get_info().title
ds[index]['book_cover_url'] = book.get_info().book_cover_url
# Reformat result need for front-end
# timeArray = time.strptime(finish_date, "%Y-%m-%d %H:%M:%S")
date = datetime.datetime.fromtimestamp(int(finish_date) / 1000)
target, finish_num, finish_flag = Collection.get_tag(
user_id, date.year, date.month)
ds[index]['tag'] = {
'target': target,
'finish_num': finish_num,
'finish_flag': finish_flag
}
result.append(ds[index])
return result
def get_read_history_by_date(user_id, year, month):
# set default start date
start_date = str(year) + "-" + str(month) + "-01 00:00:00"
# set default finish date
if month is 12:
finish_date = str(year) + "-" + str(1) + "-01 00:00:00"
else:
finish_date = str(year) + "-" + str(month + 1) + "-01 00:00:00"
# date -> timestamp
start_timestamp = int(
time.mktime(time.strptime(start_date, "%Y-%m-%d %H:%M:%S"))) * 1000
finish_timestamp = int(
time.mktime(time.strptime(finish_date,
"%Y-%m-%d %H:%M:%S"))) * 1000
collection_id = Collection.get_readcollection_id(user_id)
# SQL
conn = connect_sys_db()
query = "SELECT book_id FROM collects WHERE collection_id = \'{collection_id}\'".format(
collection_id=collection_id)
db_result = read_sql(sql=query, con=conn)
json_str = db_result.to_json(orient='index')
ds = json.loads(json_str)
result = []
for index in ds:
finish_date = Collection.get_book_read_date(
user_id, ds[index]['book_id'])
# Compare finish time to make sure whether this book is finished in this month
if start_timestamp <= finish_date < finish_timestamp:
ds[index]['finish_time'] = finish_date
book = Book(ds[index]['book_id'])
ds[index]['book_title'] = book.get_info().title
ds[index]['book_cover_url'] = book.get_info().book_cover_url
result.append(ds[index])
return result
def get_popular_book():
Book.create_view_tables()
# SQL
conn = connect_sys_db()
query = 'select distinct books.id, books.title, books.book_cover_url, view4.collect_time, view2.read_time, ' \
'view3.avg_rating, books.google_rating from books left join collects on books.id = collects.book_id ' \
'left join review_rate on books.id = review_rate.book_id left join view4 on books.id = view4.id left ' \
'join view2 on books.id = view2.id left join view3 on books.id = view3.id '
db_result = read_sql(sql=query, con=conn)
db_result = db_result.fillna(0)
# Popular grade formula
db_result.insert(
6, 'popular',
db_result.collect_time * 0.1 + db_result.read_time * 0.2 +
db_result.avg_rating * 0.3 + db_result.google_rating * 0.4)
# Sort dataframe by popular
db_result = db_result.sort_values(by=['popular'], ascending=False)
# Get first ten books in the list
db_result = db_result.head(10)
json_str = db_result.to_json(orient='index')
ds = json.loads(json_str)
result = []
for index in ds:
result.append({
'id': ds[index]['id'],
'title': ds[index]['title'],
'book_cover_url': ds[index]['book_cover_url']
})
return result
def book_search_length(input, category, rating_from, rating_to):
# SQL
conn = connect_sys_db()
query = "select id, authors, title, ISBN13, book_cover_url, description, publisher, published_date, categories, average from \
(select books.id, books.title, books.authors, books.ISBN13, books.book_cover_url, books.description, books.publisher, books.published_date, books.categories, avg(review_rate.rating) as average \
from books left join review_rate on books.id = review_rate.book_id \
where books.title like \'%{input}%\' or books.authors like \'%{input}%\' or books.isbn13 like \'%{input}%\' \
group by books.id \
order by average desc) as subquery \
where ((average >= \'{rating_from}\' and average <= \'{rating_to}\'))".format(
input=input, rating_from=rating_from, rating_to=rating_to)
# Reformat query if filter has no restriction of rating_from
if rating_from == 0:
query = query.rstrip(")")
query += ") or average is null)"
# Reformat query if filter has no restriction of category
if category is not "":
query += " and categories = \"" + category + "\""
db_result = read_sql(sql=query, con=conn)
json_str = db_result.to_json(orient='index')
ds = json.loads(json_str)
result = []
for index in ds:
result.append(ds[index])
return len(result), result
def get_username(self):
# SQL
conn = connect_sys_db()
query = "Select username FROM users WHERE id = \'{id}\'".format(
id=self._id)
db_result = read_sql(sql=query, con=conn)
return db_result.iloc[0].username
def get_num_book_collection(collection_id):
# SQL
conn = connect_sys_db()
query = "SELECT count(*) as num FROM collects WHERE collection_id = \'{collection_id}\'".format(
collection_id=collection_id)
db_result = read_sql(sql=query, con=conn)
return int(db_result.iloc[0].num)
def update_username(self, new_username):
# SQL
conn = connect_sys_db()
query = 'UPDATE users SET username = \'{new_username}\' WHERE id = \'{id}\''.format(
id=self._id, new_username=new_username)
with mysql(conn) as cursor:
cursor.execute(query)
def update_email(self, new_email):
# SQL
conn = connect_sys_db()
query = 'UPDATE users SET email = \'{new_email}\' WHERE id = \'{id}\''.format(
id=self._id, new_email=new_email)
with mysql(conn) as cursor:
cursor.execute(query)
def register_account(username, password, admin, email):
# If username already existed
if User.is_user_exists_by_username(username):
return False, 'This username has already been registered'
# SQL
conn = connect_sys_db()
query = 'SELECT email FROM users WHERE email = \'{email}\''.format(
email=email)
db_result = read_sql(sql=query, con=conn)
# If email address already been registered
if not db_result.empty:
return False, 'This email has already been registered'
# SQL
query = 'INSERT INTO users VALUES(0, \'{username}\',' \
'HEX(AES_ENCRYPT(\'{password}\', \'{key}\')), \'{admin}\', \'{email}\')' \
.format(
username=username,
password=password,
admin=admin,
email=email,
key=SECRET_KEY
)
with mysql(conn) as cursor:
cursor.execute(query)
return True, ''
def get_num_collection(user_id):
# SQL
conn = connect_sys_db()
query = "SELECT count(*) as num FROM collections WHERE (user_id = \'{user_id}\')".format(
user_id=user_id)
db_result = read_sql(sql=query, con=conn)
return int(db_result.iloc[0].num) - 1
def update_goal(user_id, year, month, goal):
# SQL
conn = connect_sys_db()
query = "UPDATE monthly_goal SET goal = \'{goal}\' WHERE (user_id = \'{user_id}\' AND year = \'{year}\' AND month = \'{month}\')".format(
user_id=user_id, year=year, month=month, goal=goal)
with mysql(conn) as cursor:
cursor.execute(query)
def get_collection_name(self):
# SQL
conn = connect_sys_db()
query = "SELECT name as collection_name FROM collections WHERE id = \'{id}\'".format(
id=self._id)
db_result = read_sql(sql=query, con=conn)
return db_result.iloc[0].collection_name
def delete_collection(collection_id):
# SQL
conn = connect_sys_db()
query = "DELETE FROM collections WHERE (id = \'{id}\')".format(
id=collection_id)
with mysql(conn) as cursor:
cursor.execute(query)
def get_num_read_collection(user_id, collection_id):
read_collection_id = Collection.get_readcollection_id(user_id)
# SQL
conn = connect_sys_db()
query = "select book_id from(select book_id from collects where collection_id = \'{collection_id}\' UNION all select book_id from collects where collection_id = \'{read_collection_id}\')a group by book_id having count(*) > 1".format(
collection_id=collection_id, read_collection_id=read_collection_id)
db_result = read_sql(sql=query, con=conn)
return int(db_result.size)
def is_book_exists_by_id(id):
# SQL
conn = connect_sys_db()
query = 'SELECT id FROM books Where id = \'{id}\''.format(id=id)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return False
else:
return True
def move_book_to_another_collection(self, new_collection_id, book_id):
# SQL
conn = connect_sys_db()
query = "UPDATE collects SET collection_id = \'{new_collection_id}\' WHERE (book_id = \'{book_id}\' AND collection_id = \'{old_collection_id}\')".format(
new_collection_id=new_collection_id,
old_collection_id=self._id,
book_id=book_id)
with mysql(conn) as cursor:
cursor.execute(query)
def is_review_exist_by_both_id(user_id, book_id):
# SQL
conn = connect_sys_db()
query = "SELECT user_id, username, book_id, rating, review_content, review_time FROM review_rate WHERE (user_id = \'{user_id}\' AND book_id = \'{book_id}\') ORDER BY review_time DESC".format(
user_id=user_id, book_id=book_id)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return False
else:
return True
def is_user_exists_by_username(username):
# SQL
conn = connect_sys_db()
query = 'SELECT username FROM users Where username = \'{username}\''.format(
username=username)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return False
else:
return True
def is_user_exists_by_email(email):
# SQL
conn = connect_sys_db()
query = 'SELECT email FROM users Where email = \'{email}\''.format(
email=email)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return False
else:
return True
def mark_as_unread(user_id, book_id):
read_collection_id = Collection.get_readcollection_id(user_id)
# SQL
conn = connect_sys_db()
query = "DELETE FROM collects WHERE (collection_id = \'{collection_id}\' AND book_id = \'{book_id}\')".format(
book_id=book_id,
collection_id=read_collection_id,
)
with mysql(conn) as cursor:
cursor.execute(query)
def is_book_exists_in_collection(collection_id, book_id):
# SQL
conn = connect_sys_db()
query = 'SELECT * FROM collects Where (book_id = \'{book_id}\' AND collection_id = \'{collection_id}\')'.format(
book_id=book_id, collection_id=collection_id)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return False
else:
return True
def delete_review(user_id, book_id):
# SQL
conn = connect_sys_db()
query = 'DELETE FROM review_rate WHERE book_id = \'{book_id}\' AND user_id = \'{user_id}\'' \
.format(
book_id=book_id,
user_id=user_id
)
with mysql(conn) as cursor:
cursor.execute(query)
def get_readcollection_id(user_id):
# SQL
conn = connect_sys_db()
query = "SELECT id FROM collections WHERE (user_id = \'{user_id}\' and name = 'read')".format(
user_id=user_id)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return None
else:
return db_result.iloc[0].id
def is_collection_exists_by_name(user_id, collection_name):
# SQL
conn = connect_sys_db()
query = "SELECT * FROM collections WHERE (user_id = \'{user_id}\' AND name = \'{collection_name}\')".format(
user_id=user_id, collection_name=collection_name)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return False
else:
return True
def is_collection_exists_by_id(collection_id):
# SQL
conn = connect_sys_db()
query = "SELECT * FROM collections WHERE id = \'{id}\'".format(
id=collection_id)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return False
else:
return True
def get_user_list():
conn = connect_sys_db()
sql = 'select id, username, admin from users'
users = read_sql(con=conn, sql=sql)
json_str = users.to_json(orient='index')
ds = json.loads(json_str)
result = []
for index in ds:
result.append(ds[index])
return {"list": result}
def delete_user(username):
if not User.is_user_exists_by_username(username):
return False
conn = connect_sys_db()
# SQL
query = 'DELETE FROM users WHERE username = \'{username}\' AND admin = \'{admin}\'' \
.format(username=username, admin=0)
with mysql(conn) as cursor:
cursor.execute(query)
return True
def is_goal_exists_by_goal(user_id, year, month, goal):
# SQL
conn = connect_sys_db()
query = "SELECT * FROM monthly_goal WHERE (user_id = \'{user_id}\' AND year = \'{year}\' AND month = \'{month}\' AND goal = \'{goal}\')".format(
user_id=user_id, year=year, month=month, goal=goal)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return False
else:
return True
def is_book_read(user_id, book_id):
collection_id = Collection.get_readcollection_id(user_id)
# SQL
conn = connect_sys_db()
query = "SELECT book_id FROM collects WHERE (book_id = \'{book_id}\' AND collection_id = \'{collection_id}\')".format(
book_id=book_id, collection_id=collection_id)
db_result = read_sql(sql=query, con=conn)
if db_result.empty:
return False
else:
return True